Cloning Database
Database cloning is a procedure by which you can create an exact copy of an Oracle database without doing a full export/import. This method is used frequently by DBAs to update test or development environments from production.
As a rule, testing and development should not be done on your production database. Depending on the resources available to companies, some have separate test and development databases and many roll testing and development into a single database. Now, the developers need up-to-date data for testing purposes, and it is the DBA who provides this to them. There are a few ways to do this. one way is by export and import. But this is cumbersome if you have to do the data refresh frequently and import takes a long time if your database is of good size.
You can of course, resort to exporting and importing tablespaces if they are self contained and you are running version 8i or greater, enterprise edition. A faster alternative is to clone the database.
Clone means an exact copy. That is, you will copy the very datafiles from your production database and use it to create your test database. So your test database will be a mirror image of the production in all respects, except of course, for the database name. The method used here is actually a backup/recovery of the production database on the test server. We just recreate the controlfile on the test machine using a new database name and do a recovery. The datafiles from the production database can be from a hot backup, a cold backup or an RMAN backup. If the database was open during backup (hot or RMAN), you will need all the archivelogs since the time the backup started to recover the database to its present state. The environment variables are used by their UNIX notation (example $ORACLE_HOME).
A production database named PROD, which resides on the machine PRODSERVER.
Another server called TESTSERVER where you want to create a database called TEST, which is a clone of PROD.
The steps:
* Make sure your environment variables have been set properly on TESTSERVER.
export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_SID=test
* On TESTSERVER, create a directory called test under $ORACLE_BASE/admin. Create the following sub-directories for the TEST database under $ORACLE_BASE/admin/test -
--> pfile (This is where your init.ora file will reside) --> bdump (This is the directory that the init.ora parameter, BACKGROUND_DUMP_DEST, points to) --> udump (This is the directory that the init.ora parameter, USER_DUMP_DEST, points to) --> cdump (This is the directory that the init.ora parameter, CORE_DUMP_DEST, points to)
* Copy your initprod.ora file to from the production machine to the directory $ORACLE_BASE/admin/test/pfile and rename it to init.ora. Make sure you copy any IFILE referenced in your init.ora file.
Create a link to the init.ora file from $ORACLE_HOME/dbs.
[] $ cd $ORACLE_HOME/dbs [oracle@testserver] $ ln -s /u01/app/oracle/admin/test/pfile/init.ora inittest.ora
* Change the following parameters in the inittest.ora file (or the init.ora file for the TEST database).
- DB_NAME - Change from PROD to TEST - SERVICE_NAMES - If defined (by default, it is DB_NAME.DB_DOMAIN). For example, from PROD.WORLD to TEST.WORLD - CONTROL_FILES - Change it to an appropriate directory on TESTSERVER. You can leave the actual filenames as they are. - LOG_ARCHIVE_DEST_n - If you want to run TEST in archivelog mode - BACKGROUND_DUMP_DEST, USER_DUMP_DEST, CORE_DUMP_DEST - Change them to the new directories that you just created under $ORACLE_BASE/admin/test - Resize SHARED_POOL, DB_BLOCK_BUFFERS (or DB_CACHE_SIZE in 9i) to fit your TESTSERVER environment
Your init.ora file is now ready for use.
* Copy all the datafiles from your production database to the test server. These files may be from a hot, cold or RMAN backup. You can place the datafiles wherever you want.They do not have to be in the same directory structure as on the production server.
Also copy all the archivelogs from the production database to the test server to some backup directory (say /home/oracle/backup). DO NOT copy the controlfiles and the redo logfiles. These will be newly created.
If you have tempfiles (check v$tempfile), you can copy the tempfiles to the TESTSERVER although they will not be used duing recovery. After recovery, we can make them a part of our new database. You will need the current redo log, though, since your current transactions are stored in this logfile and Oracle will need it to recover completely. Even if you archive the current redo log and try to use it, Oracle will ask you for the next logfile during recovery (unless you do an incomplete recovery UNTIL CANCEL, UNTIL TIME or UNTIL SCN). Find your current redo log with this query -
SQL> select f.group# "GROUP", f.member, g.status
group_status,
2 g.sequence#, g.archived arc
3 from v$logfile f, v$log g where
f.group#=g.group#;
GROUP_STATUS SEQUENCE# ARC
---------- ----------------------------------------1 /u03/oracle/oradata/prod/redo01a.log
INACTIVE 521 YES
2 /u04/oracle/oradata/prod/redo02a.log
INACTIVE 522 YES
3 /u05/oracle/oradata/prod/redo03a.log
CURRENT 523 NO
Copy the CURRENT logfile (redo03a.log in our case) from PRODSERVER into the backup directory containing the archivelogs on TESTSERVER. We will use this during the last step of our recovery .
* On your production machine, PRODSERVER, log in to the PROD database and create a text copy of the controlfile. We will use this to recreate the controlfile on the test machine. The following ALTER DATABASE command will create a SQL script with the CREATE CONTROLFILE statement in your USER_DUMP_DEST directory. [oracle@prodserver] $ sqlplus "sys as sysdba"
SQL> alter database backup controlfile to trace;
[oracle@prodserver] $ cd $ORACLE_BASE/admin/prod/udump
[oracle@prodserver] $ ls -lrt
-rw-r----- 1 oracle dba 831484 Sep 8
13:01 ora_4463.trc
-rw-r----- 1 oracle dba 410412 Dec 29
08:58 ora_32183.trc
-rw-r----- 1 oracle dba 410450 Dec 29
08:58 ora_32182.trc
-rw-r----- 1 oracle dba 410384 Dec 29
08:58 ora_32179.trc
-rw-r----- 1 oracle dba 2331 Jan 6
11:02 ora_32108.trc