Clone Oracle 9i database on AIX box

[MCubitt]

I am attempting to clone a 9i database which exists on an AIX server.

The destination database is a test version of the source, eg. LIVEDB and TESTDB.

Effectively I want to copy LIVEDB to TESTDB on a monthly basis.

I know to copy the files, no worries.

However, I would like to obtain the controlfile, change LIVEDB to TESTDB and then carry on my merry way.

I'd use: alter database backup controlfile to trace;
but I need to be able to change (grep?) ocurrences of LIVEDB to TESTDB within it - so I need to know it's location and file name.

Any suggestions?

I tried alter database backup controlfile to '/oracle/clone/' but the file is not ASCII.

 

[alexhu]

The default path to the file generated by 'alter database backup controlfile to trace;' is the 'user_dump_dest'.

check your parameter file for this value.

or select * from v$parameter where name like 'back%';

If you are using Oracle Flexible Architecture, then the location is $ORACLE_BASE/admin/<SID>/udump

Alex

 

[MCubitt]

I am aware of this, but I would like to specify the output on the command.

I do not understand why specifying the location causes a binary file yet using &quot;trace&quot; creates an ASCII one.

Alternatively, I guess one could use a few commands to obtain the name of the trace file. No idea how though, I've just started scripting!

 

[MCubitt]

Actually, I think:
ls -ltr *.trc|tail -1

would give the name of the file created, right?

So to change LIVEDB to TESTDB is this the kind of thing?

ls -ltr *.trc|tail -1|
and a sed in tehre somewhere?!

sed &quot;s/LIVEDB/TESTDB/g&quot; somewhere too!

 

[alexhu]

I think you have many trace files in the location ?

In that case you are right, to find the last one created use ls -lrt and it will be at the bottom of the screen.

you will have to edit the file as it looks like this;

Dump file /u05/app/oracle/admin/pkms/udump/ora_135002_pkms.trc
Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
PL/SQL Release 8.0.5.1.0 - Production
ORACLE_HOME = /u05/app/oracle/product/8.0.5
System name: AIX
Node name: rs6000
Release: 3
Version: 4
Machine: 004003234C00
Instance name: pkms
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 135002, image:

*** SESSION ID123.11040) 2003.09.11.18.00.01.000
*** 2003.09.11.18.00.01.000
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE &quot;PKMS&quot; NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 72
MAXINSTANCES 1
MAXLOGHISTORY 5899
LOGFILE
GROUP 1 (
'/u06/oradata/pkms/pkms_log1.ora',
'/u07/oradata/pkms/pkms_log1.ora'
) SIZE 100M,
GROUP 2 (
'/u06/oradata/pkms/pkms_log2.ora',
'/u07/oradata/pkms/pkms_log2.ora'
) SIZE 100M,
GROUP 3 (
'/u06/oradata/pkms/pkms_log3.ora',
'/u07/oradata/pkms/pkms_log3.ora'
) SIZE 100M
DATAFILE
'/u03/oradata/pkms/syspkms_01.ora',
'/u09/oradata/pkms/large_tables_03.dbf',
'/u10/oradata/pkms/temp_04.dbf',
'/u10/oradata/pkms/large_tables_04.dbf',
'/u08/oradata/pkms/phpick00_data_01.dbf',
'/u10/oradata/pkms/phpick00_index_01.dbf',
'/u09/oradata/pkms/large_tables_01.dbf',
'/u10/oradata/pkms/idcase00_data_01.dbf',
'/u03/oradata/pkms/users_01.dbf',
'/u09/oradata/pkms/pkms_data_01.dbf',
'/u06/oradata/pkms/rbs_01.dbf',
'/u09/oradata/pkms/pkms_index_01.dbf',
'/u09/oradata/pkms/pdpick00_data_01.dbf',
'/u10/oradata/pkms/pdpick00_index_01.dbf',
'/u10/oradata/pkms/pipick00_data_01.dbf', etc, etc, etc....

As you can see the top 20 or so lines are redundant

There are also lines at the end you may like to dlete and perform manually

Alex

 

[MCubitt]

Alex,

This would be an automated process, so that is why I need to do a find/replace automatically.

 

[sybaseguru]

I have answered this question many times. But a general approach would be and by the way you can use UNIX &quot;sed&quot; command for replacing the lines in the sql file easiliy

Decide on the name (SID) of the new database and create an associated parameter file to define the Oracle instance. Remember, the parameter file by default resides in $ORACLE_HOME/dbs and have an init<$ORACLE_SID>.ora filename. For example, if the name of the new database is to be NEWDB, then the associated parameter file must be called initNEWDB.ora for the database. When you create the parameter file, be sure to change the parameter DB_NAME to contain the new name of the database, and change CONTROL_FILES to contain the location of the new associated Oracle controlfiles. This is very important
1. Using SQL*Plus or anything else connect to the source database and issue the following command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command creates a text-based copy of the Database controlfile in the Database trace files.
2. Cleanly shutdown the source database as below:


Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Now that you have shutdown the source instance, go and find the text-based copy of your controlfile which will be saved in the trace directory. This directory will be contained in the directory pointed to by USER_DUMP_DEST in the database initialization parameter file. 
Once you are in the directory do the following:

ls -t *.trc | head -1

and this will identify the trace file you want. 

You'll have to edit this file and perform the following changes 

(a) Delete all lines at the top of the trace file up to but NOT INCLUDING STARTUP NOMOUNT 
(b) Delete all lines that start with '#', which are comments. Just do 


cat xxxx_ora_3426.trc | grep -v '^#' > clone.sql


(c) On the line that starts with CREATE CONTROLFILE, change 'REUSE' to 'SET' Change the old database name to the new one, and change 'NORESETLOGS' to 'RESETLOGS' 
(d) Change the locations of all logfiles and datafiles to the location of the datafiles and logfiles for the new database. 
(e) Delete the line 'RECOVER DATABASE' 
(f) Change the line 'ALTER DATABASE OPEN' to 'ALTER DATABASE OPEN RESETLOGS' 

Now you have the clone.sql ready 

4. At this point, the source database should still be shut down. Before you start it back up, copy all logfiles + datafiles associated with this database to the new location (local or on another host). These files will eventually comprise your destination database.
5. Once you have copied all the associated datafiles, you are ready to create the new database. The actual cloning process simply creates a new set of Oracle controlfiles for the new database. You have all the Oracle environment variables set in the new host etc. Set your $ORACLE_SID to point to the name of the new database.
6. Use SQL*Plus to connect to an idle instance as follows


sqlplus &quot;/ as sysdba&quot;
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initNEWDB.ora
SQL> show sga
SQL> @clone.sql 
Statement processed.

7. If you have no errors, you now have another copy of your database. As a general rule, you may want to shutdown/startup this new database just to make sure there are no problems.
8. You can now go back and restart the original database.

Hope this helps and good luck

 

[alexhu]

Ah! I see. A little more complex then. Perhaps a good idea would be to post the contents of the trace file in the AIX forum and get one of the 'awk' experts to propose a solution.

Alex

 

[MCubitt]

I think
ls -ltr *.trc|tail -1|awk '{ print $9 }'
provides the name of the file?

So..

ls -ltr *.trc|tail -1|awk '{ print $9 }'|sed 's/LIVEDB/TESTDB/g'

is right?

 

[MCubitt]

sybase: Thanks, but I need it automated. Manual is so much easier tho cumbersome.

alex: I think my safest way is to copy the trace file to a known location then sed that.

Now.. how do I copy that file?

cp <tracefile> /mydata/mytrace.trc

obviously, substituting <tracefile> with the result of ls -ltr *.trc|tail -1|awk '{ print $9 }'

 

[sybaseguru]

MCubitt,

No sweat mate. You should be able to write a shell script to do the automated replacement for you for everything on the copy of generated trace file. I will try to have a go at it later on and post it.

 

[MCubitt]

sybase, thanks..

I am sure I can, with a little help from my Tek-tip friends.

I am learning, definately learning.. trouble with UNIX is, trial and error can be costly

 

[MCubitt]

Hurrah!

I discovered how to copy the file!

$ cp `ls -ltr *.trc|tail -1|awk '{ print $9 }'` /mystuff/mytrc.trc

 

[MCubitt]

This worked! I am nearly proud of myself!

cp `ls -ltr /oracle/app/oracle/admin/LIVEDB/udump/*.trc|tail -1|awk '{ print $9 }'` /mystuff/controlLIVEDB.sql

sed &quot;s/LIVEDB/TESTDB/g&quot; /mystuff/controlTESTDB.sql > /mystuff/controlTESTDB.sql

It took the trace file and copied it to controlLIVEDB.sql. The contents gets changed LIVEDB to TESTDB and then output as controlTESTDB.sql.

 

[MCubitt]

err.. that should be
sed &quot;s/LIVEDB/TESTDB/g&quot; /mystuff/controlLIVEDB.sql > /mystuff/controlTESTDB.sql

;o)

 

[MCubitt]

Script finished. Testing now! But thanks guys for your help and support.

 

[MCubitt]

I managed to complete my scripts and refresh the test DB from live. Thanks guys!