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.

When starting the install of Oracle Database Server there always seems to be a few items that either you are just suppose to know or you spend a day banging your head against the wall trying to figure out what you need to do to get the install up and running.

First item, is creating a database user and group for the install. Do this as the root user:

# groupadd -g 1001 oinstall
# groupadd -g 1002 dba
# useradd -u 1002 -g oinstall -G dba oracle
# passwd oracle

Next, you need to setup X11 forwarding. Modify the /etc/ssh/ssh_config file to allow forwarding: ForwardX11 yes. I personally use BitVise SSH Client and Xming in order to access my SSH environments. In the Xming client, I change the offset to 10 from the default of 0. In the SSH Client, enable X11 Forwarding and set it to 127.0.0.1:10. On the Unix box, install the xauth rpm using yum install xauth. Connect as the oracle user (or psoft) and create the xauthority file, command: xauth add 127.0.0.1:10 . 12345678900987654321123456789009 – you should be able to validate that x11 works by running xclock – if you see a clock, you are golden! If you see the error: Warning: Cannot convert string “” to type XftFont, do a yum install xorg-x11-fonts*

Firewalls can be a bit of a problem, and you will want to make sure that the iptables firewall allows the database to talk to machines that will be connecting to the database. In this example 10.100.120.10 is my application server connecting to the database server 10.100.120.20:

-A INPUT -s 10.100.120.10 -p tcp -m state –state NEW,ESTABLISHED -m tcp –dport 1521 -j ACCEPT
-A OUTPUT -s 10.100.120.20 -p tcp -m state –state ESTABLISHED -m tcp –dport 1024:65535 –sport 1521 -j ACCEPT

RPMs need to installed in order to make the install work correctly, you can manually do everything or you can automate the application by:

# cd /etc/yum.repos.d
# wget https://public-yum.oracle.com/public-yum-ol6.repo –no-check-certificate
# yum install oracle-rdbms-server-11gR2-preinstall
wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle –no-check-certificate

RPM Libraries need to be installed in order to make the OUI work:
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel 4.1.2
make-3.81
sysstat-7.0.2

Oracle decided to integrate PeopleSoft patching into their main database for patch fix, and in the process the commonly used Update ID, has been change to be associated with what I call the Bug Incident, but they are referring to as the Patchset in ARU.

The ARU is Oracle’s Automated Release Update system and has gone by various other names.  Since I maintain a few patch lists I have some serious updating to do, but at least they made some nice documents to show the old and new numbers.  The new system appears to be clunky and difficult to to use like all new patch systems in the past.  Hopefully it will get easier to use and understand with some more use.  Good luck!

Here is the list for the Campus Solutions 8.9 & 9.0 Campus Bundles:

Campus Solutions 9.0 Bundles

Campus Solutions 8.9 Bundles

I found this video that shows you have to create a database when ASM is installed.

YouTube Video

I found that when I manually created the Oracle Database, that because the ASM dynamically names everything, I simply removed the directory & file name reference and replaced it with the +ASM group where I wanted the datafile to be located.  If you look at the ptddl script for a PeopleTools database or the hcddl script for an HR database you will see how it will say the directory+file name:

‘/oracle/oradata/dbfiles/tablespace1.dbf’ is replaced with:

‘+ASMHRORPTGROUP1’ depending on what you named it.

This little SQL is a sweet little piece of code I found somewhere in my travels.  It will find duplicate rows of data based on a keyset, however, this only works in Oracle.  I will try to find a similar piece for SQL Server, but for now here we go:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);