Coping a Database. 1

[SOL9MAN]

Could somebody provide me with a set of instructions which is the best way of cloning a database on Unix.

thanks

 

[alexhu]

Same server or different ?

Alex

 

[sybaseguru]

The best way of cloning the database in my opinion is as follows. If you want to create both databases on the same host you need to be aware of total RAM etc you have. You can easily have two instances each with an SGA of 2GB on a host with say 6GB of max shared memory. On Solaris you can find out the max shared memory by looking at file /etc/system. The parameter Set shmsys:shminfo_shmmax will give the total amount of shared memory in bytes. On Linux you can do ipcs -lm to get the max seg size in KB. For example

oracle@linux:/u001/oracle/admin/mydb/udump% ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 2097151
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

the line you are interested is 'max seg size (kbytes)'

Either way on the same host or different hosts you can clone the database as follows:
Decide on the name (SID) of the new database and create an associated parameter file to define the Oracle instance. Remember, the parameter file MUST reside 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 "/ as sysdba"
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

 

[sem]

Just insignificant remark: name and location of parameter file may be ARBITRARY, but in this case you should specify full name in PFILE clause.

Regards, Dima