DBAQuest.com
Cloning an Oracle Database
Date Monday, January 07 @ 13:50:25
Topic Oracle Administration
| 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. That much is common sense. 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. The first one that comes to mind is, of course, 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. We will be changing that.
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 (that is, do a complete recovery).
Throughout the article, I will refer to environment variables by their UNIX notation (example $ORACLE_HOME). Replace this with the Windows equivalent if you are using NT/2000 (example $ORACLE_HOME becomes %ORACLE_HOME%).
Let us use our customary server names and database names as an example. You have a production database named PROD, which resides on the machine PRODSERVER. You also have another server called TESTSERVER where you want to create a database called TEST, which is a clone of PROD. Here are the steps:
- Make sure your environment variables have been set properly on TESTSERVER.
On UNIX, assuming you're using Korn Shell,
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_SID=test
On Windows NT/2000,
set ORACLE_BASE=c:oracle
set ORACLE_HOME=c:oracleora81
set 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.
If running UNIX, create a link to the init.ora file from $ORACLE_HOME/dbs.
[oracle@testserver] $ cd $ORACLE_HOME/dbs
[oracle@testserver] $ ln -s /u01/app/oracle/admin/test/pfile/init.ora inittest.ora
If running Windows NT/2000, create a file called inittest.ora in %ORACLE_HOME%database. Edit this file using Notepad and add this single line to it -
IFILE=c:oracleadmintestpfileinit.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 MEMBER 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
The last file is the one we are looking for. Here is a sample for 8i and one for 9i.
- Copy only the CREATE CONTROLFILE command from the trace file and paste it into a file called create_control.sql. Copy this file over to the test machine, TESTSERVER. I have a directory called /home/oracle/scripts on TESTSERVER where we'll copy this file. Edit this file.
- Replace the keyword REUSE with the keyword SET. This is required since we are not reusing the controlfiles from the PROD database, but creating a new controlfile with a different database name.
- Change the database name from PROD to TEST
- Change the key word NORESETLOGS to RESETLOGS. This is because we will be doing recovery USING BACKUP CONTROLFILE (The Oracle Server thinks we have lost all our controlfiles, and are recreating them).
- Change the keyword ARCHIVELOG/NOARCHIVELOG depending on what mode you want the database TEST to run in. Here, we will run it in NOARCHIVELOG mode
- In the logfile and datafile path names, replace "prod" with "test". If the path names are different from production, then make sure you update the script with the new path. For example, system01.dbf was on the mount point /u02 on PRODSERVER. If TESTSERVER does not have enough space on /u02, you can place system01.dbf on /u03 and change the path in the script accordingly. Make sure the path names are correct and the datafiles actually exist (Redo logs should not be in the path specified in the script or the clone will fail).
Our create_control.sql script should now look like this:
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 17016
LOGFILE
GROUP 1 '/u03/oracle/oradata/test/redo01a.log' SIZE 100M,
GROUP 2 '/u04/oracle/oradata/test/redo02a.log' SIZE 100M,
GROUP 3 '/u05/oracle/oradata/test/redo03a.log' SIZE 100M
DATAFILE
'/u03/oracle/oradata/test/system01.dbf',
'/u06/oracle/oradata/test/tools01.dbf',
'/u06/oracle/oradata/test/rbs01.dbf',
'/u07/oracle/oradata/test/temp01.dbf',
'/u08/oracle/oradata/test/users01.dbf',
'/u05/oracle/oradata/test/drsys01.dbf'
CHARACTER SET US7ASCII
;
- Now we can start the cloning process on TESTSERVER. If you are running on Windows NT/2000, then you will first need to create an instance. Unix users can skip this step. In your init.ora file, if the parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED, then you will need to create a password file. This applies to both Windows and UNIX environments. You can do that using the orapwd utility. If the password file does not exist, the 'create controlfile' command will fail.
- Log into SQLPlus as SYSDBA. Start the instance using the init.ora file that we created for TEST and run our create_control.sql file.
[oracle@testserver] $ sqlplus "sys as sysdba"
SQL*Plus: Release 8.1.7.1.0 - Production on Sun Jan 6 13:06:55 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/admin/test/pfile/init.ora
ORACLE instance started.
Total System Global Area 235701300 bytes
Fixed Size 279604 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 540672 bytes
SQL> @/home/oracle/scripts/create_control.sql
Control file created.
SQL>
The controlfiles will be created in the directories specified by the init.ora parameter CONTROL_FILES. The database name has now been changed to TEST in the newly created controlfiles and it is in the mount stage. We can now recover the database using the archivelogs.
When prompted for a archivelog filename during recovery, enter the full path name of the archivelogs (in our case, it will be something like /home/oracle/backup/prod_521.arc). After applying the last archivelog, Oracle will ask for a log with the latest sequence number. Type in your current redo log file name.
The TEST database is now recovered completely since the redo entries end in the current redo log. Open the database with the RESETLOGS option. The PROD database is now successfully cloned as TEST.
SQL> recover database using backup controlfile;
ORA-00279: change 322605 generated at 01/05/2002 11:20:51 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/prod/arch/prod_521.arc
ORA-00280: change 322605 for thread 1 is in sequence #521
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/backup/prod_521.arc
Log applied.
.
.
.
ORA-00279: change 322605 generated at 01/06/2002 13:48:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/prod/arch/prod_523.arc
ORA-00280: change 322605 for thread 1 is in sequence #523
Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/backup/redo03a.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
- During the "open resetlogs" stage, new redo logs will be created with sequence# starting from 1. Check v$database, v$instance and v$log to confirm the name change and redo log creation.
- You can now add any tempfiles to the database. This command assumes that the file already exists at the operating system level
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/prod/temp01.dbf' REUSE;
| This article comes from DBAQuest.com
http://www.dbaquest.com
The URL for this story is:
http://www.dbaquest.com/modules.php?op=modload&name=News&file=article&sid=9 |
|
|