Troubleshooting Setting up Oracle 12c R2 and using it with Older Clients

Here are the problems I have every time I setup up Oracle.

Oracle 12c R2 on Windows Server added more complexity than even 12c R1.


1) Windows user name cannot have a space in it.  The Oracle Setup will hang up before it completes (with no explanation).


2) Turn off Password Complexity on Windows Server or Oracle Setup can't create a user for Oracle (if you are wanting it to create a user)


3) In listener.ora and tnsname.ora, change  "localhost" to your machine name or you will not be able to connect from other machines.


4) If you need older clients to connect with Oracle 12c R2, in the sqlnet.ora, you will have to add: 

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

You can use a different minimum (instead of 8), but this is the lowest.

You will get this error on other machine if this is the case:

ORA 28040: No matching authentication protocol


5) If you have already created users in Oracle before adding the entries to sqlnet.ora, you will have to reset the passwords if you want to connect from the older versions.

ALTER USER  IDENTIFIED BY 

You will get the following error until this is done (for no apparent reason):

ORA-01017 Invalid Username/Password 

----

Resources:


https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533918300346989898

https://community.microstrategy.com/s/article/ORA-28040-No-matching-authentication-protocol-when-connecting-to-Oracle-12c-or-higher-database

https://stackoverflow.com/questions/37615088/unable-to-connect-to-oracle-12c-from-client-machine

Welcome to the world of Pluggable Databases in Oracle 12c

I'm new to pluggable databases in Oracle 12c, so I thought I would put my most common problems here in hopes that the solutions might help someone else.


Everything worked fine in my database until I rebooted the machine, and then I got:


ORA-01033: ORACLE initialization or shutdown in progress
01033. 00000 -  "ORACLE initialization or shutdown in progress"
*Cause:    An attempt was made to log on while Oracle is being started up
           or shutdown.
*Action:   Wait a few minutes. Then retry the operation.
Vendor code 1033


The problem was that the pluggable databases do not automatically open by default, not that the initialization or shutdown is in progress.  It doesn't matter how long you wait (as directed by the message).

First, you must open the pluggable database to get it running again:

alter pluggable database all open;

I said "all" but you could specify a specific pluggable database.

This will get the database running until you reboot.  If you want it to automatically open, you must save the state:

alter pluggable database all save state;

Again I said "all" instead of the pluggable database name.

The following Ask Tom post goes into more detail on this topic, but it doesn't mention the misleading message you get from Oracle.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531671900346425939

Get Schema/DDL from Oracle Data Pump Export

Reposting this from the linked Stack Overflow article because this solutions works well and I want to be able to easily find it again:

impdp '/ as sysdba' dumpfile=<file> logfile=import_log.txt sqlfile=ddl_dump.txt

http://stackoverflow.com/questions/95578/how-to-determine-the-schemas-inside-an-oracle-data-pump-export-file

Adding more than one Datafile to an Oracle Tablespace

If you need to add an additional data file to an Oracle Tablespace:

ALTER TABLESPACE 
   users 
ADD DATAFILE 
   'AdditionalUsersTablespace.dbf' 
size 100m
AUTOEXTEND ON
NEXT 10M


More info from these posts:

http://www.dba-oracle.com/t_alter_tablespace_add_datafile.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3002.htm

Updating the NUM_ROWS in USER_TABLES in Oracle

Good or bad, I use the NUM_ROWS values in the USER_TABLES table to help filter lists of tables when presenting them to the user. If I have a database and only about 25% of tables have records (and there are thousands of tables), then it is a lot nicer to only sift through the tables that do have records, and knowing how many records is even better (even if it is only an estimate). My problem is that these values don't just get updated automatically for you. You might find that they are all 0 even when there is data present. You can update all of the values with something like this:

exec dbms_stats.gather_schema_stats('mySchema', cascade=>TRUE)

A lot more info on this topic can be found at:

http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm

Script to Purge All Objects for a User in Oracle

I have to purge and reload an Oracle Database dump on a monthly basis. I have been manually deleting all of the Objects with Toad before loading the new dump. I searched for a script that would delete all of the objects. Many people suggested that you should just drop the user, but if I do this I have to add back in grants that don't get picked up by the export.

This thread on the Oracle Discussion Forum has several solutions. I am pasting the script that worked the best for me below from Massimo. There is some criticism in the thread about why he did not just use the object name in one case instead of all the If/Else conditions; however, I like this approach better, because I can easily take out something if I need to:

declare
stringa varchar2(100);

cursor cur is
      select  *
      from    user_objects;

begin
  for c in cur loop
    begin
      stringa := '';
      if c.object_type = 'VIEW' then
          stringa := 'drop view ' || c.object_name;
          EXECUTE immediate stringa;
      elsif c.object_type = 'TABLE' then
          stringa := 'drop table ' || c.object_name || ' cascade constraints';
          EXECUTE immediate stringa;
      elsif c.object_type = 'SEQUENCE' then
          stringa := 'drop sequence ' || c.object_name;
          EXECUTE immediate stringa;
      elsif c.object_type = 'PACKAGE' then
          stringa := 'drop package ' || c.object_name;
          EXECUTE immediate stringa;   
      elsif c.object_type = 'TRIGGER' then
          stringa := 'drop trigger ' || c.object_name;
          EXECUTE immediate stringa;   
      elsif c.object_type = 'PROCEDURE' then
          stringa := 'drop procedure ' || c.object_name;
          EXECUTE immediate stringa;
      elsif c.object_type = 'FUNCTION' then
          stringa := 'drop function ' || c.object_name;
          EXECUTE immediate stringa;     
      elsif c.object_type = 'SYNONYM' then
          stringa := 'drop synonym ' || c.object_name;
          EXECUTE immediate stringa;
      elsif c.object_type = 'INDEX' then
          stringa := 'drop index ' || c.object_name;
          EXECUTE immediate stringa;
      elsif c.object_type = 'PACKAGE BODY' then
          stringa := 'drop PACKAGE BODY ' || c.object_name;
          EXECUTE immediate stringa;  
      elsif c.object_type = 'DATABASE LINK' then
          stringa := 'drop database link ' || c.object_name;
          EXECUTE immediate stringa;  
      end if;
  exception
        when others then
        null;
    end; 
  end loop;
  -- PURGE recyclebin

end;
/

Importing a Datapump export into Oracle with IMPDP

I never had to remember the command-line options for the old imp utility (because it prompted you for the info it needed), but with impdp, you need to know what to specify. Here is my simple example that works for me to import a Datapump export file:

impdp user/password schemas=mySchema dumpfile=myfile.dpdmp logfile=mylog.log

The schema parameter specifies which schema from the file to import.

The dumpfile parameter specifies the name of the datapump export file to import. The catch here is that the dump file location defaults to a default directory on the server (something like this):

c:\oracle\Administrator\admin\orcl\dpdump

You can change this path, but you have to create the directory in Oracle and specify it with a Directory Parameter. You have to create a directory in Oracle with something like this:

create directory MyExportDir as 'c:\exports';

Here is the detailed info for impdp on the Oracle site.

Creating a Tablespace in Oracle with SQL

I never can seem to remember the SQL to create a tablespace in Oracle. I am posting my example that I use to I can easily find it again in the future:

create tablespace MyTableSpaceName
logging
datafile 'MyTableSpaceFile.dbf'
size 32m
autoextend on
next 32m maxsize unlimited
extent management local;

How to use SQL to retrieve the Vertex values in an SDO_GEOMETRY Attribute

It turns out to be more complicated than you might think to simply retrieve the Vertices of an SDO_GEOMETRY attribute in Oracle Spatial using just SQL. The main problem is that this is an array of an unknown size, so the solution below is just for retrieving the Ordinate values for 1 record identified by a key and each record returned is one Ordinate in the list.

SELECT Round(COLUMN_VALUE,8) as pt
FROM TABLE( SELECT a.MyGeomAttr.SDO_ORDINATES
                    FROM MyTable a
                    WHERE MyKey=24974)

which will retrieve something like this (my example uses a 3D geometry so there are 3 value per vertex with Z being 0 in all of them):

I will go through why I did the things I did to make this work like I wanted below.

If you are using ODP.NET and you have Oracle Number values in the Ordinates that are larger than a .NET Decimal value (this is common for me), you must use Round (or truncate the value in someway) so that you don't get an Arithmetic Overflow when the Ordinate Value is loaded into a .NET decimal value. I am using the Round function to simply get 8 decimal points of precision). It turns out that the data I have uses Oriented Points and the computed vector coordinates use a large number of decimal places that .NET Decimals can't handle.

To get the Ordinate Values back as the Query results, you must use the TABLE function to convert the SDO_ORDINATES Array to records. See here for more info on the TABLE function. The key thing here is that the COLUMN_VALUE is not just me making up a name for my example, it is a keyword that lets you refer to the value the Table function returns (otherwise you can't run the results of the Table function through the Round function).

The alias "a" seems to be required or you get an error in the query.

Getting the Version of an OleDB Provider

There may a much simpler way of determining the version of an OleDb provider (like OraOledb.Oracle.1) than I have shown below, but I have yet to find one.

The OleDbConnection object will tell you the version of the Server through its ServerVersion property:

Dim myConn As New OleDbConnection(g_gtech.GetConnectionString)
myConn.Open()
Dim versionStr as String = myConn.ServerVersion.ToString
myConn.Dispose()

But, I often want to the know the version of the Provider itself. The only way I have found to do this is to sift through the registry and read the ProductVersion off the provider DLL itself.

For example:

Dim versionStr as String = _
GetProviderVersion("OraOleDb.Oracle.1")

Here is the supporting code:


Public Function GetProviderVersion(ByVal providerName As String) As String

Try
Dim clsid As String = GetRegistryValue(Registry.LocalMachine, _
"SOFTWARE\Classes\" + providerName + "\clsid", "")

If clsid.Trim = "" Then Return "Not Installed"
Dim path As String = _
GetRegistryValue(Registry.LocalMachine, "SOFTWARE\Classes\CLSID\" + clsid + "\InprocServer32", "")

Dim Info As FileVersionInfo
Info = FileVersionInfo.GetVersionInfo(path)
Return Info.ProductVersion.ToString
Catch ex As Exception

Return "Unable to get Version"

End Try

End Function

Public Function GetRegistryValue(ByVal regKey As RegistryKey, _
ByVal subKey As String, ByVal valueName As String) As String

Dim value As String = ""
Dim registryKey As RegistryKey = regKey
Dim registrySubKey As RegistryKey
registrySubKey = registryKey.OpenSubKey(subKey)
If registrySubKey IsNot Nothing Then
Try
value = registrySubKey.GetValue(valueName).ToString
Catch ex As Exception
value = ""
End Try
registrySubKey.Close()
End If
Return value
End Function

If there is a simple or more elegant way to find the Provider version, please comment.