I don’t pretend to be an Oracle DBA but I can fuddle my way through most of it without too much of an issue, but there are some really odd things with how Oracle works that drive me crazy.  One of the things that drives me crazy is cloning.  In SQL Server I can clone a database very easily and usually very quickly.  Oracle NOT so much.  I have written a procedure to follow for cloning in the past that is missing a very critical element so I am updated that post and also writing it here for future reference.

When I clone the database I simply copy my data directory with houses my temp and perm tablespaces, and the recreate statement I use puts all the permanent tablespaces back in, but the temporary ones need to be re-added as well.  This can be done with the command:

ALTER tablespace {TableSpaceName} add TEMPFILE ‘{Directory Path/TableSpace.dbf}’ reuse;

If you look in the dump file that is mentioned in the cloning post, at the bottom you should find the TEMPFILE reuse statements for the database.  Just copy them and change the paths and add that to the create script or run the steps manually in sqlplus and you will be good to go!

Okay now this is going to be relatively uncommon for most people, but often times in recent years PeopleSoft requires that we install 32 bit & 64 bit versions of the Oracle Client in order to make everything work as PeopleSoft has been transitioning from 32 bit Windows to 64 bit Windows.

So if you are flying through the installation of a new machine and you put the 32 bit version on without a problem and then try to install the 64 bit version you might run into an error. There are a few reasons this might happen but for me the problem was with a service called:  “OracleRemExecService”.  This service gets created during installation of 32 bit and 64 bit clients, but for some reason doesn’t remove itself cleanly in all situations.  If you stop the service it should stop cleanly and then delete itself.

Continue with your other installation!

Oracle Database: Oracle Performance Secret

When working with a new Oracle Database on a Windows 2012R2 server running Oracle 11.2.0.4.  The PeopleTools Upgrade process was on the last step which is to do a Final Alter Audit (FNLALTAUD).  This Upgrade Step looks at all the tables within the system and determines if the metadata is in sync with what is actually in the database.

14 hours into the process and has to be done, but as per usual it is next to impossible to tell with PeopleSoft.  Since this really should not take anymore than an hour or two, the process is killed and the following Oracle Performance Secret is executed in SQLPlus:

analyze table sys.CDEF$ delete statistics;
analyze table sys.CON$ delete statistics;
analyze table sys.USER$ delete statistics;

Rerunning the Upgrade Task after apply the Oracle Performance Secret and it is now running in:  29 minutes.  That is much more acceptable.

What is the SYS Schema?

The SYS schema stores all of the base tables and views for the Oracle Database’s Data Dictionary.  The Data Dictionary is critical to the operation of Oracle Database.  It is imperative to maintain the integrity of the data dictionary, so manipulation of the Data Dictionary should only be done by Oracle itself.

Where did this fix come from?

During upgrades of environments there has been some reported cases of very poor performance using Oracle PeopleSoft Data Mover to import data into the new environment.  We have experienced this problem while upgrading environments specifically the Final Table Audit (or Initial Audit) Upgrade Tasks.  PeopleSoft has a very high number of constraints (Finance v9.2, I just queried had 2.7 million rows), which are stored on the CDEF$ & CON$ Data Dictionary Tables. If the Cost Based Optimizer (COB) has bad statistics to work from it is often seen that the optimization plan will come up with Full Table Scans which are extremely poor performing.  Patch #4618615 & #5103126 from Oracle apparently address this issue, and so the problem should be limited to Oracle Database 10.2 to 11.0.  However, often times when poor performance is effecting an environment we apply this Oracle Performance Secret and performance typically is greatly improved.

I have a little test site setup for Oracle PeopleSoft and I ran into an issue the other day where my sysadm account was expired. I typically have never ran across Oracle Database expiring my administrative user accounts. A little google search later and I found Amit Rath’s blog on fixing this problem.  It turned out that I had a default profile that had a time limit of 180 days for the password.

SQL> select profile from dba_users;

All of my system administrative users had the profile:  DEFAULT

SQL> select * from dba_profiles where profile=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;

Here is a nice clean way to clone an Oracle database.

STEP 1:

Generate a control file trace which will allow you to create a new database. On the current database server, go into SQL*Plus as sysdba: enter the command:

alter database backup controlfile to trace as ‘/filepath/filename.sql’;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 8
MAXLOGMEMBERS 4
MAXDATAFILES 1021
MAXINSTANCES 1
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ‘/oracle/db/olddb/redo/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/oracle/db/olddb/redo/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/oracle/db/olddb/redo/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/oracle/db/olddb/data/system01.dbf’,
‘/oracle/db/olddb/data/sysaux01.dbf’,
‘/oracle/db/olddb/data/psundots01.dbf’,
‘/oracle/db/olddb/data/psdefault.dbf’,
‘/oracle/db/olddb/data/aaapp.dbf’,
…..All the PeopleSoft Datafiles will be listed here…….
‘/oracle/db/olddb/data/aalarge.dbf’,
‘/oracle/db/olddb/data/psimage2.dbf’
CHARACTER SET WE8ISO8859P15
;

STEP 2:

Shutdown the OLDDB database

STEP 3:

Copy all of the associated files with the database into a new directory or to a new server. Make sure you end up with the same structure as before just with the NEWDB name. So in my case I did: cp -rf /oracle/db/olddb /oracle/db/newdb, make sure you create the bdump, udump and cdump directories

STEP 4:

Using the script from Step 1, change the controlfile creation script:

Original: CREATE CONTROLFILE REUSE DATABASE “OLDDB” NORESETLOGS
Modified: CREATE CONTROLFILE SET DATABASE “NEWDB” RESETLOGS

STEP 4a:

Identify the TEMPFILE’s reuse statements in the script and make sure to keep them as part of the script (Should be at the end of the trace file)

STEP 5:

Remove any other instructions from the trace file and save it.

STEP 6:

Change the olddb references in the datafile and controlfile sections to the NEWDB location.

Original: DATAFILE ‘/oracle/db/olddb/data/system01.dbf’,
Modified: DATAFILE ‘/oracle/db/newdb/data/system01.dbf’,

STEP 6a:

Change the paths for the TEMPFILE to the new database location:

ALTER tablespace {TableSpaceName} add TEMPFILE ‘/oracle/db/{newdb}/data/psgtt01.dbf’ reuse;

STEP 7:

Save as newdb_create.sql.

STEP 8:

Copy the old initOLDDB.ora file to initNEWDB.ora
Modify the initNEWDB.ora and make the changes from the OLDDB to the NEWDB.

STEP 9:

Add entries in the tnsnames.ora, listener.ora and oratab for the NEWDB.

STEP 10:

Change to the NEWDB and STARTUP NOMOUNT and run the @newdb_create.sql

STEP 11:

ALTER DATABASE OPEN RESETLOGS;

STEP 12:

For a PeopleSoft Environment, you will want to update the PS.PSDBOWNER table to reflect the NEWDB.