http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/CLHB/Default.aspx http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564 https://sites.google.com/a/dbaref.com/www/clone-oracle-database/creatingaduplicatedatabaseonanewhostusingrmanduplicatefeature http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10734/rcmrecov.htm http://gavinsoorma.com/2010/03/create-standby-database-using-11g-duplicate-from-active-database/ http://www.oraclemasters.in/?p=1198 http://www.dbapundits.com/blog/step-by-step/oracle-11g-active-database-duplication/ http://www.dba-oracle.com/t_active_database_duplication_add.htm http://www.orafaq.com/node/2383 RMAN Progress monitor SQL. SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK 1. Change DBNAME not DBID (in NID command) so that your old backup works after the RMAN duplicate. If you are changing DBID then you need to re-register DB in RMAN catalog. 2. To avoid ORA-25153: Temporary Tablespace is Empty after RMAN duplicate check for tempfiles and re-add to TEMP tablespace after duplicate If the database uses locally-managed temporary tablespaces, then add new tempfiles to these tablespaces. For example: SQL>ALTER TABLESPACE temp ADD TEMPFILE '/u03/PRODDB/oradata/temp01.dbf' REUSE"; SQL>ALTER TABLESPACE temp ADD TEMPFILE '/u03/PRODDB/oradata/temp02.dbf' REUSE"; SQL> alter tablespace temp add TEMPFILE '/mnt/u03/RPTDB/oradata/temp02.dbf' size 500M; Tablespace altered. SQL> alter tablespace temp add TEMPFILE '/mnt/u03/RPTDB/oradata/temp01.dbf' size 500M; Tablespace altered. SQL> |
Clone Oracle Database
Temp file after duplicate db
SQL> select tablespace_name from dba_temp_files; TABLESPACE_NAME ------------------------------ TEMP TEMP SQL> select temporary_tablespace from dba_users where username='ADMN'; TEMPORARY_TABLESPACE ------------------------------ TEMP SQL> select count(*) from dba_temp_files; COUNT(*) ---------- 2 SQL> desc dba_temp_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(257) FILE_ID NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS CHAR(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files; FILE_NAME ------------------------------ TABLESPACE_NAME ----------------------------- /u03/PRODDB/oradata/temp02.dbf TEMP /u03/PRODDB/oradata/temp01.dbf TEMP |
clone/duplicating an 11g database on the same machine using the new active database duplication
Here
is a complete example of duplicating an 11g database on the same
machine (Linux) using the new, aforementioned active database
duplication: 1. Create any needed directories mkdir /u03/app/oracle/oradata/duptest2. Create hard-coded network entries for your new, auxiliary database Addition to SID_LIST_LISTENER in listener.ora: (SID_DESC =Addition to tnsames.ora: DUPTEST =3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET) cd $ORACLE_HOME/dbs 4. Create 'dummy' parameter file for auxiliary instance [/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ more initduptest.ora5. Add relevant entry to oratab on non-Windows environments [/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ grep duptest /etc/oratab6. 'Nomount' the auxiliary instance in preparation for duplication [/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ export ORACLE_SID=duptest7. Duplicate the database using RMAN rman target=sys/password@nf auxiliary=sys/password@duptest If successful, you should see output similar to the following: Starting Duplicate Db at 14-JAN-11 |
Duplicate an active database without using backups in Oracle 11g
oracle 11g provides a new feature, without RMAN database backup we can clone the database. I have tested the active database RMAN cloning in my test server. Environment Details: Operating system: Windows XP service pack 2 How its work? In an active duplication process, target database online image copies
and archived redo log files were copied through the auxiliary instance
service name. So we no need the target database backup. 1.Generate the unique DBID for auxiliary database. ACTIVE Database RMAN cloning in same server. 1.Configure the network files. Listener configuration: SID_LIST_LISTENER11G= TNS configuration: TROY = CLONEDB = 2.Create the instance using oradim utility ( applicable for windows platform) C:\>oradim -new -sid clonedb 3.Create a password file for clonedb database using orapwd utility C:\>orapwd file=D:\Oracle\app\product\11.1.0\db_1\dbs\orapwCLONEDB password=clonedbadmin entries=20 4.Create the init. ora file for clonedb database. Troy database: We create the pfile for clonedb from troy database & edit the parameter. SQL> show parameter spfile NAME TYPE VALUE SQL> create pfile='D:\Oracle\app\product\admin\clonedb\pfile\initclonedb.ora' File created. Init parameter change “clonedb” instead of troy & use to start the Clonedb. clonedb.__db_cache_size=130023424 5.Create the appropriate folder for clonedb database (admin/oradata folders etc..) 6.Startup the clone database in mount stage C:\>set oracle_sid=clonedb C:\>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 28 14:21:08 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Enter user-name: / as sysdba SQL> startup nomount Total System Global Area 535662592 bytes 7.Connect the target & auxiliary database using RMAN C:\>rman target sys/troyadmin@troy Recovery Manager: Release 11.1.0.7.0 - Production on Wed Apr 28 14:26:18 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TROY (DBID=3876493159) RMAN> connect auxiliary sys/clonedbadmin@clonedb connected to auxiliary database: CLONEDB (not mounted) RMAN> duplicate target database to clonedb from active database nofilenamecheck; Starting Duplicate Db at 28-APR-10 contents of Memory Script: executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 28-APR-10 sql statement: alter system archive log current contents of Memory Script: Starting backup at 28-APR-10 cataloged archived log datafile 2 switched to datafile copy contents of Memory Script: executing command: SET until clause Starting recover at 28-APR-10 starting media recovery archived log for thread 1 with sequence 25 is already on disk as file D:\ORACLE\APP\PRODUCT\ARCHIVE\ contents of Memory Script: database dismounted connected to auxiliary database (not started) Total System Global Area 535662592 bytes Fixed Size 1348508 bytes contents of Memory Script: executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP01.DBF in control file cataloged datafile copy cataloged datafile copy cataloged datafile copy cataloged datafile copy cataloged datafile copy datafile 2 switched to datafile copy contents of Memory Script: database opened 8.Verify the clonedb database. SQL> select name,open_mode from v$database; NAME OPEN_MODE SQL> archive log list Active database duplication is better way to clone the database, since don’t you have any prior backup. |
How do I clone a database from one machine to another - when the filesystems are different ?
Cloning a database is normally done with the following sequence:
This is all quite straightforward even if you have to rename the database, and any of the files in the database. The secret is in step (2). On the source database, you can run SQL> alter database backup controlfile to trace at which point you will get a full "create controlfile" command dumped out to the user_dump_dest directory. It will look something like CREATE CONTROLFILE REUSE DATABASE "SOURCEDB" NORESETLOGS ARCHIVELOG For cloning this database, you will alter
For each file listed under the DATAFILE clause, if you will be renaming the file as part of the cloning process, then you simply update the file name to the new name in the CREATE CONTROLFILE command. |
Clone database using cold backup
A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database. STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this: STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 240 MAXINSTANCES 1 MAXLOGHISTORY 113 LOGFILE GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf', '/u03/oradata/olslsq/log1b.dbf') SIZE 30M, GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf', '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M DATAFILE '/u01/oradata/oldlsq/system01.dbf', '/u01/oradata/oldlsq/mydatabase.dbf' ; # Recovery is required if any of the datafiles are restored # backups, or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN; STEP 2: Shutdown the old database STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server. rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: Old: CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS New: CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS STEP 5: Remove the “recover database” and “alter database open” syntax # Recovery is required if any of the datafiles are restored # backups, or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN; STEP 6: Re-names of the data files names that have changed. Save as db_create_controlfile.sql. Old: DATAFILE '/u01/oradata/oldlsq/system01.dbf', '/u01/oradata/oldlsq/mydatabase.dbf' New: DATAFILE '/u01/oradata/newlsq/system01.dbf', '/u01/oradata/newlsq/mydatabase.dbf' STEP 7: Create the bdump, udump and cdump directories cd $DBA/admin mkdir newlsq cd newlsq mkdir bdump mkdir udump mkdir cdump mkdir pfile STEP 8: Copy-over the old init.ora file rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile STEP 9: Start the new database @db_create_controlfile.sql STEP 10: Place the new database in archivelog mode |
Clone a Database Using Hot Backup
Make a clone of your database with RMAN (on the same server as the target database) - windows
Before execution of the clone script I 1. Created the directories for the datafiles, controlfiles, redologfiles for the new clone database 2. I opened the target database with db_name = UTF8. UTF8 is the database which I would like to clone to a database with db_name = CLONE 3. I copied and renamed the passwordfile 4. I created the windows service oracleserviceclone ( with oradim ) 5. I made the pfile / spfile for the new database with db_name = CLONE. Please note I had to take away the OMF entry for the controlfiles. There are still some issues left with OMF redolog files and OMF controlfiles. C:\> SET ORACLE_SID=CLONE RUN { set newname for datafile 1 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF'; set newname for datafile 2 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF'; set newname for datafile 3 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF'; set newname for datafile 4 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF'; set newname for datafile 5 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF'; set newname for datafile 6 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF'; set newname for datafile 7 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF'; set newname for datafile 8 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF'; set newname for datafile 9 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF'; set newname for tempfile 1 to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP'; DUPLICATE TARGET DATABASE TO "CLONE" LOGFILE GROUP 1 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO') SIZE 10M, -- I made an error in the path name GROUP 2 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO') SIZE 10M, -- I made an error in the path name GROUP 3 ('C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO') SIZE 10M; -- I made an error in the path name } This is the output of the script. Please note that only at the final end I had do some manual work, since I made a mistake at the level of the path for the online redo logs. executing command: SET NEWNAME using target database control file instead of recovery catalog executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting Duplicate Db at 12-FEB-06 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=157 devtype=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: sid=156 devtype=DISK allocated channel: ORA_AUX_DISK_3 channel ORA_AUX_DISK_3: sid=155 devtype=DISK allocated channel: ORA_AUX_DISK_4 channel ORA_AUX_DISK_4: sid=154 devtype=DISK contents of Memory Script: { set until scn 10616265; set newname for datafile 1 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF"; set newname for datafile 2 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF"; set newname for datafile 3 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF"; set newname for datafile 4 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF"; set newname for datafile 5 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF"; set newname for datafile 6 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF"; set newname for datafile 7 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF"; set newname for datafile 8 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF"; set newname for datafile 9 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF"; restore check readonly clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 12-FEB-06 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00006 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF restoring datafile 00008 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86P37_.BKP channel ORA_AUX_DISK_2: starting datafile backupset restore channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF restoring datafile 00003 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF channel ORA_AUX_DISK_2: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87YMF_.BKP channel ORA_AUX_DISK_3: starting datafile backupset restore channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set restoring datafile 00005 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF restoring datafile 00007 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF channel ORA_AUX_DISK_3: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87FFC_.BKP channel ORA_AUX_DISK_4: starting datafile backupset restore channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set restoring datafile 00002 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF channel ORA_AUX_DISK_4: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS8GYHF_.BKP channel ORA_AUX_DISK_4: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS8GYHF_.BKP tag=TAG20060210T151853 channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:09:08 channel ORA_AUX_DISK_4: starting datafile backupset restore channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set restoring datafile 00004 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF restoring datafile 00009 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF channel ORA_AUX_DISK_4: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86JRL_.BKP channel ORA_AUX_DISK_2: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87YMF_.BKP tag=TAG20060210T151853 channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:11:24 channel ORA_AUX_DISK_3: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS87FFC_.BKP tag=TAG20060210T151853 channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:11:31 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86P37_.BKP tag=TAG20060210T151853 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:12:17 channel ORA_AUX_DISK_4: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_NNNDF_TAG20060210T151853_1YS86JRL_.BKP tag=TAG20060210T151853 channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:07:04 Finished restore at 12-FEB-06 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 4090 LOGFILE GROUP 1 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' ) SIZE 10 M , GROUP 2 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' ) SIZE 10 M , GROUP 3 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' ) SIZE 10 M DATAFILE 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF' CHARACTER SET AL32UTF8 contents of Memory Script: { switch clone datafile all; } executing Memory Script released channel: ORA_AUX_DISK_1 released channel: ORA_AUX_DISK_2 released channel: ORA_AUX_DISK_3 released channel: ORA_AUX_DISK_4 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF datafile 3 switched to datafile copy input datafile copy recid=2 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF datafile 4 switched to datafile copy input datafile copy recid=3 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF datafile 5 switched to datafile copy input datafile copy recid=4 stamp=582216382 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF datafile 6 switched to datafile copy input datafile copy recid=5 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF datafile 7 switched to datafile copy input datafile copy recid=6 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF datafile 8 switched to datafile copy input datafile copy recid=7 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF datafile 9 switched to datafile copy input datafile copy recid=8 stamp=582216383 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF contents of Memory Script: { set until scn 10616265; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 12-FEB-06 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=158 devtype=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: sid=154 devtype=DISK allocated channel: ORA_AUX_DISK_3 channel ORA_AUX_DISK_3: sid=155 devtype=DISK allocated channel: ORA_AUX_DISK_4 channel ORA_AUX_DISK_4: sid=156 devtype=DISK starting media recovery channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=4407 channel ORA_AUX_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_ANNNN_TAG20060210T153431_1YS9F8S9_.BKP channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\BACKUPSET\2006_02_10\O1_MF_ANNNN_TAG20060210T153431_1YS9F8S9_.BKP tag=TAG20060210T153431 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:27 archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\ARCHIVELOG\2006_02_12\O1_MF_1_4407_1YYH1YVL_.ARC thread=1 sequence=4407 channel clone_default: deleting archive log(s) archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\ARCHIVELOG\2006_02_12\O1_MF_1_4407_1YYH1YVL_.ARC recid=1 stamp=582216415 media recovery complete, elapsed time: 00:00:08 Finished recover at 12-FEB-06 contents of Memory Script: { shutdown clone; startup clone nomount ; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 524288000 bytes Fixed Size 1249968 bytes Variable Size 473959760 bytes Database Buffers 46137344 bytes Redo Buffers 2940928 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 4090 LOGFILE GROUP 1 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' ) SIZE 10 M , GROUP 2 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' ) SIZE 10 M , GROUP 3 ( 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' ) SIZE 10 M DATAFILE 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF' CHARACTER SET AL32UTF8 contents of Memory Script: { set newname for tempfile 1 to "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP"; switch clone tempfile all; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF"; catalog clone datafilecopy "C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1P47MLKR_.TMP in control file cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF recid=1 stamp=582216453 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF recid=2 stamp=582216454 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF recid=3 stamp=582216455 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF recid=4 stamp=582216455 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF recid=5 stamp=582216456 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF recid=6 stamp=582216457 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF recid=7 stamp=582216458 cataloged datafile copy datafile copy filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF recid=8 stamp=582216459 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=582216453 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF datafile 3 switched to datafile copy input datafile copy recid=2 stamp=582216454 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF datafile 4 switched to datafile copy input datafile copy recid=3 stamp=582216455 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF datafile 5 switched to datafile copy input datafile copy recid=4 stamp=582216455 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF datafile 6 switched to datafile copy input datafile copy recid=5 stamp=582216456 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF datafile 7 switched to datafile copy input datafile copy recid=6 stamp=582216457 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF datafile 8 switched to datafile copy input datafile copy recid=7 stamp=582216458 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF datafile 9 switched to datafile copy input datafile copy recid=8 stamp=582216459 filename=C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 02/12/2006 14:47:44 RMAN-03015: error occurred in stored script Memory Script RMAN-06136: ORACLE error from auxiliary database: ORA-00344: unable to re-create online log 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' ORA-27040: file create error, unable to create file OSD-04002: unable to open file O/S-Error: (OS 3) The system cannot find the path specified. At the end, something went wrong , I made an error in the directory paths of the online redo logs as such they couldn' t have been created by the alter database open resetlogs. Let us take a look and let us correct in a traditional way SQL> select member from v$logfile; MEMBER -------------------------------------------------- C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO03.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO03.RDO'; Database altered. SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO01.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO01.RDO'; Database altered. SQL> alter database rename file 'C:\ORACLE\ORADATA\UTF\CLONE\DUPLICAT\REDO02.RDO' to 'C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO02.RDO'; Database altered. SQL> alter database open resetlogs; Database altered. My database with db_name UTF8 has been copied, cloned on the same server, other directories, other db_name and offcourse other dbid, let us explore some well known v$-views SQL> select dbid,name from v$database; DBID NAME ---------- --------- 781032125 CLONE SQL> select name from v$datafile; NAME ------------------------------------------------------------------------- C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSTEM_1YYG2RXX_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_16_1YYG2S9N_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_SYSAUX_1YYG2T33_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS_1YYGMYBV_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_UNDOTBS2_1YYG2SHW_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_D_1YYG2RVQ_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_CW4ORA_I_1YYG2S9Y_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USERS2_1YYG2T8Q_.DBF C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_USER_LOB_1YYGMZ6K_.DBF 9 rows selected. SQL> select name from v$controlfile; NAME ------------------------------------------------------------------------- C:\ORACLE\ORADATA\CLONE\DUPLICAT\CONTROLFILE\O1_MF_1YYH15D2_.CTL C:\ORACLE\FLASH_RECOVERY_AREA\DUPLICAT\CONTROLFILE\O1_MF_1YYH16VG_.CTL SQL> select member from v$logfile; MEMBER ------------------------------------------------------------------------- C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO03.RDO C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO02.RDO C:\ORACLE\ORADATA\CLONE\DUPLICAT\ONLINELOG\REDO01.RDO SQL> select name from v$tempfile; NAME ------------------------------------------------------------------------- C:\ORACLE\ORADATA\CLONE\DUPLICAT\DATAFILE\O1_MF_TEMP_1YYHPXV3_.TMP |
Duplicate Oracle 9i database - same server
1. Create password file va-ibill01:SVPRD1B:cd /u01/SVPRD1B/app/oracle/product/92/dbs va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs $orapwd file=/u01/SVPRD1B/app/oracle/product/92/dbs/orapwDUP password=password entries=10 $ls orapwDUP orapwDUP 1. Update listener. ora file and restart the listener $cd $ORACLE_HOME/network/admin va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin $ls libnk59.exp libnk59.imp libnrad9.exp libnrad9.imp listener.ora samples shrept.lst snmp_ro.ora snmp_rw.ora sqlnet.ora tnsnames.ora $vi listener.ora "listener.ora" 16 lines, 430 characters # LISTENER.ORA Network Configuration File: /u01/SVPRD1B/app/oracle/product/92/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = va-ibill01)(PORT = 1521)) ) ) ) DUP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = va-ibill01)(PORT = 1521)) ) (CONNECT_DATA = (ORACLE_SID = DUP) ) ) INBOUND_CONNECT_TIMEOUT_LISTENER = 0 Save file. va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin $lsnrctl reload LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production on 16-MAR-2011 21:08:16 Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) The command completed successfully va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin $cd /u01/SVPRD1B/app/oracle/product/92/dbs va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs $ls autocf_SVPRD1B_c-3443740097-20100902-0d.f initSVPRF1B.ora orapwSVPRD1B snapcf_SVPRD1B.f autocf_SVPRD1B_c-3443740097-20110228-02.f initdw.ora reorg418.sql spfileSVPRD1B.ora init.ora lkSVPRD1B reorg418.sql.orginal initSVPRD1B.ora orapwDUP reorg421.sql 3. Create new init.ora file for DUP database. va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs $cp initSVPRD1B.ora initDUP.ora va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs Update the initDUP.ora file. a) Updatelocation of new control files. b) get location of the data files from prod and use DB_FILE_NAME_CONVERT to point new location c)Get location of all log files from prod and use LOG_FILE_NAME_CONVERT to point new location SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /mnt/u02/SVPRD1B/oradata/system01.dbf /mnt/u04/SVPRD1B/oradata/undotbs01.dbf /mnt/u02/SVPRD1B/oradata/drsys01.dbf /mnt/u08/SVPRD1B/oradata/tools01.dbf /mnt/u08/SVPRD1B/oradata/users01.dbf /mnt/u03/SVPRD1B/oradata/xdb01.dbf /mnt/u09/SVPRD1B/oradata/archived_ne_idx01.dbf /mnt/u08/SVPRD1B/oradata/archived_ne_data02.dbf /mnt/u08/SVPRD1B/oradata/archived_ne_data03.dbf /mnt/u08/SVPRD1B/oradata/ata_data01.dbf /mnt/u08/SVPRD1B/oradata/ata_data_charge01.dbf NAME -------------------------------------------------------------------------------- /mnt/u08/SVPRD1B/oradata/ata_data_ne01.dbf /mnt/u08/SVPRD1B/oradata/ata_ic_lob01.dbf /mnt/u09/SVPRD1B/oradata/ata_index01.dbf /mnt/u09/SVPRD1B/oradata/ata_index_charge01.dbf /mnt/u09/SVPRD1B/oradata/ata_index_ne01.dbf /mnt/u04/SVPRD1B/oradata/perfstat01.dbf /mnt/u08/SVPRD1B/oradata/tpsysadm_data01.dbf /mnt/u09/SVPRD1B/oradata/tpsysadm_index01.dbf /mnt/u08/SVPRD1B/oradata/forecasting.dbf /mnt/u08/SVPRD1B/oradata/archived_charge_data.dbf /mnt/u09/SVPRD1B/oradata/archived_charge_idx01.dbf NAME -------------------------------------------------------------------------------- /mnt/u08/SVPRD1B/oradata/archived_charge_data02.dbf /mnt/u08/SVPRD1B/oradata/archived_ne_data01.dbf 24 rows selected. SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /mnt/u02/SVPRD1B/oradata/control01.ctl /mnt/u03/SVPRD1B/oradata/control02.ctl /mnt/u04/SVPRD1B/oradata/control03.ctl SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /mnt/u05/SVPRD1B/oradata/redo05a.log /mnt/u06/SVPRD1B/oradata/redo05b.log /mnt/u05/SVPRD1B/oradata/redo04a.log /mnt/u06/SVPRD1B/oradata/redo04b.log /mnt/u05/SVPRD1B/oradata/redo03a.log /mnt/u06/SVPRD1B/oradata/redo03b.log /mnt/u05/SVPRD1B/oradata/redo02a.log /mnt/u06/SVPRD1B/oradata/redo02b.log /mnt/u05/SVPRD1B/oradata/redo01a.log /mnt/u06/SVPRD1B/oradata/redo01b.log 10 rows selected. SQL> Minimum required parameter in init.ora file. *.compatible='9.2.0.8.0' *.control_files='/mnt/u02/SVPRD1B/oradata/control01.ctl','/mnt/u03/SVPRD1B/oradata/control02.ctl','/mnt/u04/SVPRD1B/oradata/control03.ctl' # Convert file names to allow for different directory structure. DB_FILE_NAME_CONVERT= ('/u01/SVPRD1B/','/mnt/u01/SVPRD1B/',' /u02/SVPRD1B/','/mnt/u02/SVPRD1B/', '/u03/SVPRD1B/','/mnt/u03/SVPRD1B/', '/u04/SVPRD1B/','/mnt/u04/SVPRD1B/', '/u05/SVPRD1B/','/mnt/u05/SVPRD1B/', '/u06/SVPRD1B/','/mnt/u06/SVPRD1B/', '/u07/SVPRD1B/','/mnt/u07/SVPRD1B/', '/u08/SVPRD1B/','/mnt/u08/SVPRD1B/', '/u09/SVPRD1B/','/mnt/u09/SVPRD1B/') LOG_FILE_NAME_CONVERT=('/u05/SVPRD1B/oradata/','/mnt/u05/SVPRD1B/oradata/','/u06/SVPRD1B/oradata/','/mnt/u06/SVPRD1B/oradata/') *.db_block_size=8192 *.db_name='DUP' Save the file va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs $ORACLE_SID=DUP; export ORACLE_SID va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs $sqlplus /nolog SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 21:42:01 2011 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> conn / as sysdba Connected to an idle instance. SQL> create spfile from pfile='/u01/SVPRD1B/app/oracle/product/92/dbs/initDUP.ora'; File created. SQL>STARTUP FORCE NOMOUNT; SQL>EXIT $ORACLE_SID=DUP; export ORACLE_SID $rman TARGET sys/duck_p6nts@SVPRD1B CATALOG rman/rman@rman AUXILIARY / RUN { ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE sbt; ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt; DUPLICATE TARGET DATABASE TO DUP; } Open another window and monitor the progress. va-ibill01:SVPRD1B:/mnt/u02/SVPRD1B/oradata $export ORACLE_SID=DUP; va-ibill01:DUP:/mnt/u02/SVPRD1B/oradata $sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 23:53:25 2011 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK 2 3 4 5 6 7 ; SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 11 1 1 6196962 8038400 77.09 SQL> |
Creating Duplicate Database from Rman Using the Backups done at OS level
Creating Duplicate database from Rman using the backups that are done at OS level . The backup that is used in the example is a cold backup of the database Below the procedure with an example: Assumed database names: Primary Database SID: PROD Duplicate Database SID: AUX Rman Catalog SID: RMAN 1) Perform a cold backup of the primary database - SQL> Shutdown immediate; [oracle@test-br data]$ cp /home/oracle/PROD/data/sys01.dbf /home/oracle/PROD/data/backup/sys01.dbf [oracle@test-br data]$ cp /home/oracle/PROD/data/undotbs.dbf /home/oracle/PROD/data/backup/undotbs.dbf [oracle@test-br data]$ cp /home/oracle/PROD/data/users_9i.dbf /home/oracle/PROD/data/backup/users_9i.dbf [oracle@test-br data]$ cp /home/oracle/PROD/data/test01.dbf /home/oracle/PROD/data/backup/test01.dbf 2) Connect to target database and recovery catalog from rman % Rman target / catalog rman/rman@con_catalog 3) Startup the database RMAN> startup 4) Catalog the datafile copies RMAN>catalog datafilecopy '/home/oracle/PROD/data/backup/sys01.dbf'; cataloged datafile copy datafile copy filename=/home/oracle/PROD/data/backup/sys01.dbf recid=9 stamp=617219529 RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/undotbs.dbf'; cataloged datafile copy datafile copy filename=/home/oracle/PROD/data/backup/undotbs.dbf recid=10 stamp=617219556 RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/users_9i.dbf'; cataloged datafile copy datafile copy filename=/home/oracle/PROD/data/backup/users_9i.dbf recid=11 stamp=617219574 RMAN> catalog datafilecopy '/home/oracle/PROD/data/backup/test01.dbf'; cataloged datafile copy datafile copy filename=/home/oracle/PROD/data/backup/test01.dbf recid=12 stamp=617219589 5) Now create a pfile for the duplicate instance # +----------------------------------------+ # | FILE : initAUX.ora | # | DATABASE NAME : AUX | # +----------------------------------------+ # Set the below to location of the clone Duplicate database / name of # clone database. audit_file_dest =/oradata/aux/adump background_dump_dest =/oradata/aux/bdump core_dump_dest =/oradata/aux/cdump user_dump_dest =/oradata/aux/udump db_name ="AUX" instance_name =AUX # Set the below to the location of the duplicate clone control file. control_files ='/home/oracle/aux/c1.ctl' # Set the below for the from and to location for all data files / redo # logs to be cloned. log_file_name_convert=('/home/oracle/PROD/redo/','/home/oracle/aux/') db_file_name_convert=('/home/oracle/PROD/data/','/home/oracle/aux/') #Set the below to the same as the production target undo_management =AUTO undo_retention =10800 undo_tablespace =UNDOTBS1 db_block_size = < > compatible = 9.2.0 6) Startup nomount the auxiliary database $ export ORACLE_SID=aux1 $ sqlplus "/as sysdba" SQL> Startup nomount 7) Now connect to target,catalog and auxiliary instances from Rman $ rman target sys/sys@con_tar catalog rman/rman@con_cat auxiliary / Recovery Manager: Release 9.2.0.7.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: PROD (DBID=4282295901) connected to recovery catalog database connected to auxiliary database: aux1 (not mounted) 8) Perform a log switch RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile 9) Perform the duplicate + RMAN> duplicate target database to aux1; |
Creating a Duplicate Database on a New Host using RMAN duplicate feature
Duplicate database from server A to server B (Non ASM) Assumed database names: Primary Database SID: PROD Duplicate Database SID: AUX RMAN Catalog SID: RMAN ==================================================================================== Steps 1. Backup the primary database. 2. Determine how much disk space will be required. 3. Ensuring you have enough space on your target server. 4. Making the backup available for the duplicate process. 5. Creating the init.ora & administration directories for the duplicate database. 6. Ensuring SQL*NET connections to primary database and RMAN catalog are working. 7. Prepare RMAN duplicate script. 8. Execute the RMAN script. ===================================================================================== 1. Backup of the primary database. Host A (Target) Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile (Figure 1a). If you are planning to duplicate a subset of the database refer to (Figure 1b) this illustrates the RMAN command to backing up certain tablespaces. [oracle@linux] export ORACLE_SID=PROD [oracle@linux] rman target=/ catalog=rman/rman@RMAN RMAN> run { allocate channel d1 type disk; backup format '/backups/PROD/df_t%t_s%s_p%p' database; sql 'alter system archive log current'; backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all; release channel d1; } Figure 1a - This command will perform a full database backup including archivelogs and the current controlfile. [oracle@linux] export ORACLE_SID=PROD [oracle@linux] rman target=/ catalog=rman/rman@RMAN RMAN> run { allocate channel d1 type disk; backup format '/backups/PROD/df_t%t_s%s_p%p' tablespace SYSTEM, SYSAUX, UNDO, USERS; sql 'alter system archive log current'; backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all; release channel d1; } Figure 1b- This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile. 2. Determine how much disk space will be required. Host A(Target) - After deciding what you will be duplicating, calculate the entire space this will require on the new host. The full database calculation (Figure 2a) will calculate the entire space required whereas (figure 2b) allows you to enter the tablespace names in order to calculate the space required. Figure 2c provides a sample output. Note: sql valid for version 10g only. SQL> select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL; Figure 2a - Calculate total space for all datafiles within database. SQL> select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL; Figure 2b - Calculate space for list of tablespaces within primary database. DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb ---------------- ---------------- -------------------- ------------- 900 150 20.34375 1070.34375 Figure 2c - Sample output of space calculation. 3. Ensuring you have enough space on your target server. Host B (Aux) Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database. [oracle@linux] df -kh Filesystem Size Used Avail Use% Mounted on /dev/vg01/root 9.9G 2.8G 6.6G 30% / /dev/sda1 145M 16M 122M 12% /boot none 999M 0 999M 0% /dev/shm /dev/vg01/tmp 2.0G 383M 1.5G 20% /tmp /dev/vg01/u01 20G 12G 7.0G 62% /u01 /dev/vg01/u02 4.9G 1010M 3.6G 22% /u02 /dev/vg01/backups 5.9G 1.2G 4.4G 22% /backups /dev/vg01/oradata 15G 13G 2.0G 87% /oradata Figure 3a - Sample output showing the space available on your filesystem. Compare the results received from this query with the output from 2a or 2b and ensure you have enough diskspace for your duplicate database. 4. Making the backup available for the duplicate process. If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below (figure 4a) the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B. Furthermore also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery. RMAN> list backup; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 22 Full 529M DISK 00:00:51 2006/05/16 11:12:54 BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203 Piece Name: /backups/PROD/df_t590584323_s23_p1 List of Datafiles in backup set 22 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf 2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf 3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf 4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 24 48M DISK 00:00:06 2006/05/16 11:13:07 BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301 Piece Name: /backups/PROD/al_t590584381_s25_p1 List of Archived Logs in backup set 24 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19 1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20 1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11 1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59 1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05 1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00 1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00 1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00 Figure 4a - A list backup showing the backup pieces that need to be copied across Host B 5. Creating the init.ora & administration directories for the duplicate database. Host B(AUX) Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database. An example is shown below (figure 5a) with bare minimum settings. Note you may require to set parameters which are the same as your production database (refer to you primary init.ora) # +----------------------------------------+ # | FILE : initAUX.ora | # | DATABASE NAME : AUX | # +----------------------------------------+ # Set the below to location of the clone Duplicate database / name of # clone database. audit_file_dest =/oradata/AUX/adump background_dump_dest =/oradata/AUX/bdump core_dump_dest =/oradata/AUX/cdump user_dump_dest =/oradata/AUX/udump db_name ="AUX" instance_name =AUX cluster_database=false (in case the production is a rac environment) # Set the below to the location of the duplicate clone control file. control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl') # Set the below for the from and to location for all data files / redo # logs to be cloned. db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/") log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/") #Set the below to the same as the production target undo_management =AUTO undo_retention =10800 undo_tablespace =UNDOTBS1 db_block_size = 8192 compatible = 10.2.0.1.0 Figure 5a - Sample initAUX.ora with minimal settings Following the creation of the initAUX.ora startup nomount the auxiliary instance. [oracle@linux]export ORACLE_SID=AUX [oracle@linux] sqlplus '/as sysdba' SQLPLUS> startup nomount; Figure 5b - startup nomount the AUX instance. 6. Ensuring SQL*NET connections to primary database and RMAN catalog are working. Host B(AUX) Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step. [oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’ [oracle@linux]% sqlplus rman/rman@RMAN (not mandatory) Figure 6a - SQL*NET connections 7. Prepare RMAN duplicate script. In a working directory on Host B create an RMAN script file duplicate.rcv. The example below (figure 7a) shows the command for a complete duplicate (figure 7b) skips the tablespaces which are not required in the duplicate and (figure 7b) provide the syntax required for a point in time duplicate. run { allocate auxiliary channel C1 device type disk; duplicate target database to AUX; } Figure 7a - Sample duplicate command. run { allocate auxiliary channel C1 device type disk; duplicate target database to AUX skip tablespace ABC, XYZ; } Figure 7b- Sample duplicate script omitting optional tablespaces; run { set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')"; allocate auxiliary channel C1 device type disk; duplicate target database to AUX; } Figure 7c- Sample duplicate script to a point in time. 8. Execute the RMAN script. Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database. [oracle@linux] export ORACLE_SID=AUX [oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary / RMAN> @duplicate.rcv |
1-10 of 15