In my VM i have a database called TESTDB & i want to clone it to tstdbdup on a different host.
We will clone the DB with the same name first then later we will change the name
So i had followed the link "http://www.dbatutor.com/2010/12/rman-restore-rman-backup-to-different.html" & here are the screen shot of the work i did.
[oracle@localhost ~]$ ps -fe|grep pmon
grid 3931 1 0 14:54 ? 00:00:00 asm_pmon_+ASM
oracle 5414 1 0 17:06 ? 00:00:00 ora_pmon_testDB
oracle 5620 5231 0 17:24 pts/2 00:00:00 grep pmon
Step 1:Take a backup of the source database(TESTDB) as below.
RMAN> backup as compressed backupset database format '/u02/backup/rman_testDB_%s_%d_%T.bus' plus archivelog format '/u01/backup/arch_testDB_%s_%d_%T.arc';
Starting backup at 04-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=44 RECID=40 STAMP=781885927
input archived log thread=1 sequence=45 RECID=41 STAMP=781886354
input archived log thread=1 sequence=46 RECID=42 STAMP=782234385
input archived log thread=1 sequence=47 RECID=43 STAMP=782418077
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u01/backup/arch_testDB_26_TESTDB_20120504.arc tag=TAG20120504T182117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 04-MAY-12
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/testdb/datafile/system.691.781606239
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.692.781606241
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.693.781606241
input datafile file number=00004 name=+DATA/testdb/datafile/users.694.781606241
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u02/backup/rman_testDB_27_TESTDB_20120504.bus tag=TAG20120504T182134 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 04-MAY-12
Starting backup at 04-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=48 RECID=44 STAMP=782418250
channel ORA_DISK_1: starting piece 1 at 04-MAY-12
channel ORA_DISK_1: finished piece 1 at 04-MAY-12
piece handle=/u01/backup/arch_testDB_28_TESTDB_20120504.arc tag=TAG20120504T182411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
Starting Control File and SPFILE Autobackup at 04-MAY-12
piece handle=/u02/backup/c-2564934505-20120504-00 comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-12
RMAN> list backup
2> ;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
25 31.25M DISK 00:00:13 04-MAY-12
BP Key: 25 Status: AVAILABLE Compressed: YES Tag: TAG20120504T182117
Piece Name: /u01/backup/arch_testDB_26_TESTDB_20120504.arc
List of Archived Logs in backup set 25
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 44 2513198 29-APR-12 2538899 29-APR-12
1 45 2538899 29-APR-12 2558433 29-APR-12
1 46 2558433 29-APR-12 2586068 02-MAY-12
1 47 2586068 02-MAY-12 2590564 04-MAY-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 256.04M DISK 00:02:28 04-MAY-12
BP Key: 26 Status: AVAILABLE Compressed: YES Tag: TAG20120504T182134
Piece Name: /u02/backup/rman_testDB_27_TESTDB_20120504.bus
List of Datafiles in backup set 26
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2590572 04-MAY-12 +DATA/testdb/datafile/system.691.781606239
2 Full 2590572 04-MAY-12 +DATA/testdb/datafile/sysaux.692.781606241
3 Full 2590572 04-MAY-12 +DATA/testdb/datafile/undotbs1.693.781606241
4 Full 2590572 04-MAY-12 +DATA/testdb/datafile/users.694.781606241
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
27 108.00K DISK 00:00:01 04-MAY-12
BP Key: 27 Status: AVAILABLE Compressed: YES Tag: TAG20120504T182411
Piece Name: /u01/backup/arch_testDB_28_TESTDB_20120504.arc
List of Archived Logs in backup set 27
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 48 2590564 04-MAY-12 2590660 04-MAY-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 9.42M DISK 00:00:03 04-MAY-12
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20120504T182412
Piece Name: /u02/backup/c-2564934505-20120504-00
SPFILE Included: Modification time: 04-MAY-12
SPFILE db_unique_name: TESTDB
Control File Included: Ckp SCN: 2590669 Ckp time: 04-MAY-12
<here in step 1 we have to note the control file backupset & the last sequence of the archivelog>
Step 2: First do some sanity check on the database TESTDB to find the log files,temp files & datafiles location , even i had also taken a trace file of the
control file of the source database. & stored the details in a notepad for later use.
Location of logfiles
=====================
SQL> select GROUP#,MEMBER from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
+DATA/testdb/onlinelog/group_3.698.781606385
2
+DATA/testdb/onlinelog/group_2.697.781606383
1
+DATA/testdb/onlinelog/group_1.696.781606381
Location of tempfiles
=====================
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
+DATA/testdb/tempfile/temp.699.781606405
1 TEMP
Location of Datafiles
=============================
SQL> select file#,NAME from v$datafile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
1
+DATA/testdb/datafile/system.691.781606239
2
+DATA/testdb/datafile/sysaux.692.781606241
3
+DATA/testdb/datafile/undotbs1.693.781606241
FILE#
----------
NAME
--------------------------------------------------------------------------------
4
+DATA/testdb/datafile/users.694.781606241
To find generic location of all datafiles,tempfiles,logfiles
==============================================================
SQL> select distinct substr(name,1,instr(name,'/',-1)) dbf_con_str from v$tempfile;
DBF_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/tempfile/
SQL> select distinct substr(name,1,instr(name,'/',-1)) dbf_con_str from v$datafile;
DBF_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/datafile/
SQL> select distinct substr(member,1,instr(member,'/',-1)) logfile_con_str from v$logfile where type='ONLINE';
LOGFILE_CON_STR
--------------------------------------------------------------------------------
+DATA/testdb/onlinelog/
trace control file from source<testdb> database(from bdump location as *.trc file)
=============================================================================
SQL> alter database backup controlfile to trace
2 ;
Database altered.
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/tes
tdb/testDB/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/tes
tdb/testDB/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/tes
tdb/testDB/trace
[oracle@localhost ~]$ cd /u01/app/oracle/diag/rdbms/testdb/testDB/trace
[oracle@localhost trace]$ ls -ltr|grep control
[oracle@localhost trace]$ ls -ltr
total 2240
-rw-r----- 1 oracle asmadmin 7852 May 4 19:07 testDB_ora_6090.trc
-rw-r----- 1 oracle asmadmin 126399 May 4 19:07 alert_testDB.log
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/testdb/onlinelog/group_1.696.781606381' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/testdb/onlinelog/group_2.697.781606383' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/testdb/onlinelog/group_3.698.781606385' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/testdb/datafile/system.691.781606239',
'+DATA/testdb/datafile/sysaux.692.781606241',
'+DATA/testdb/datafile/undotbs1.693.781606241',
'+DATA/testdb/datafile/users.694.781606241'
CHARACTER SET AL32UTF8
;
Step 3: Modify the pfile from source database(testdb) , change it as below but keep the db_name unchanged<important>
*.audit_file_dest='/u01/app/oracle/admin/testDBDUP/adump'
*.audit_trail='db'
*.blank_trimming=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/testdbdup/controlfile/current.695.781606377'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='testDB'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='+DATA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testDBDUPXDB)'
*.open_cursors=300
*.pga_aggregate_target=158334976
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=476053504
*.undo_tablespace='UNDOTBS1'
Step 4: Start(nomount) the instance using the modified pfile
[oracle@localhost tmp]$ export ORACLE_SID=testDB
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 19:34:30 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/inittestdb.ora';
ORACLE instance started.
Total System Global Area 477073408 bytes
Fixed Size 1337324 bytes
Variable Size 146802708 bytes
Database Buffers 322961408 bytes
Redo Buffers 5971968 bytes
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost tmp]$ ps -fe|grep pmon
grid 3931 1 0 14:54 ? 00:00:00 asm_pmon_+ASM
oracle 6502 1 0 19:36 ? 00:00:00 ora_pmon_testDB
oracle 6562 6287 0 19:37 pts/1 00:00:00 grep pmon
Step 5: Now from RMAN connect to the database as below & restore the control file < make sure u have created the ASM disk path i.e.
+DATA/testdbdup/controlfile/ > and mount the database
[oracle@localhost rdbms]$ echo $ORACLE_SID
testDB
[oracle@localhost rdbms]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 4 19:54:21 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from '/u02/backup/c-2564934505-20120504-00';
Starting restore at 04-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+DATA/testdbdup/controlfile/control01.ctl
Finished restore at 04-MAY-12
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
Step 6: Now connect to the database mounted in sqlplus & rename the logfiles and tempfiles
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB MOUNTED PRIMARY
SQL>
SQL>
SQL> alter database rename file '+DATA/testdb/onlinelog/group_3.698.781606385' to '+DATA/testdbdup/onlinelog/log01.dbf';
Database altered.
SQL> alter database rename file '+DATA/testdb/onlinelog/group_2.697.781606383' to '+DATA/testdbdup/onlinelog/log02.dbf';
Database altered.
SQL> alter database rename file '+DATA/testdb/onlinelog/group_1.696.781606381' to '+DATA/testdbdup/onlinelog/log03.dbf';
Database altered.
SQL> alter database rename file '+DATA/testdb/tempfile/temp.699.781606405' to '+DATA/testdbdup/tempfile/tmp01.dbf';
Database altered.
Step 7: Do the set new name for the datafiles as in below rman run script & execute as below . set until clause as sequence number+1
RMAN> run{
set until sequence 49;
set newname for datafile 1 to '+DATA/testdbdup/datafile/system01.dbf';
set newname for datafile 2 to '+DATA/testdbdup/datafile/sysaux01.dbf';
set newname for datafile 3 to '+DATA/testdbdup/datafile/undotbs1.dbf';
set newname for datafile 4 to '+DATA/testdbdup/datafile/users01.dbf';
restore database;
switch datafile all;
recover database;
}14> 15> 16> 17> 18>
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 04-MAY-12
Starting implicit crosscheck backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 04-MAY-12
Starting implicit crosscheck copy at 04-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-MAY-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/TESTDB/CONTROLFILE/current.742.782422753
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/testdbdup/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/testdbdup/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/testdbdup/datafile/undotbs1.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DATA/testdbdup/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/rman_testDB_27_TESTDB_20120504.bus
channel ORA_DISK_1: piece handle=/u02/backup/rman_testDB_27_TESTDB_20120504.bus tag=TAG20120504T182134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 04-MAY-12
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=782425582 file name=+DATA/testdbdup/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=782425582 file name=+DATA/testdbdup/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=782425582 file name=+DATA/testdbdup/datafile/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=782425582 file name=+DATA/testdbdup/datafile/users01.dbf
Starting recover at 04-MAY-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 48 is already on disk as file +DATA/testdb/archivelog/2012_05_04/thread_1_seq_48.743.782418251
archived log file name=+DATA/testdb/archivelog/2012_05_04/thread_1_seq_48.743.782418251 thread=1 sequence=48
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-MAY-12
Step 8: Now take a trace of the control file & modify the database name(db_name) in it and save this file as .sql file
SQL> alter database backup controlfile to trace;
Database altered.
CREATE CONTROLFILE SET DATABASE "TSTDBDUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/testdbdup/onlinelog/log03.dbf' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/testdbdup/onlinelog/log02.dbf' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/testdbdup/onlinelog/log01.dbf' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/testdbdup/datafile/system01.dbf',
'+DATA/testdbdup/datafile/sysaux01.dbf',
'+DATA/testdbdup/datafile/undotbs1.dbf',
'+DATA/testdbdup/datafile/users01.dbf'
CHARACTER SET AL32UTF8
;
Step 9: Now shutdown the database and make changes for db_name in init.ora file from testDB to tstdbdup.
<note: the database name shouldnot exceed more than 8 characters can see the error message below>
**********************************************************
[oracle@localhost tmp]$ echo $ORACLE_SID
testdbdup
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 20:51:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/inittestdb.ora'
ORA-01127: database name 'TESTDBDUP' exceeds size limit of 8 characters
SQL> exit
Disconnected
***********************************************************
[oracle@localhost tmp]$ export ORACLE_SID=tstdbdup
[oracle@localhost tmp]$ vi control_testdbdup.sql
[oracle@localhost tmp]$ echo $ORACLE_SID
tstdbdup
[oracle@localhost tmp]$
[oracle@localhost tmp]$
[oracle@localhost tmp]$
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 4 20:54:11 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/inittestdb.ora'
ORACLE instance started.
Total System Global Area 477073408 bytes
Fixed Size 1337324 bytes
Variable Size 146802708 bytes
Database Buffers 322961408 bytes
Redo Buffers 5971968 bytes
SQL> @/tmp/control_testdbdup.sql
CREATE CONTROLFILE SET DATABASE "TSTDBDUP" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '+DATA/testdbdup/controlfile/control01.ctl'
ORA-17502: ksfdcre:4 Failed to create file
+DATA/testdbdup/controlfile/control01.ctl
ORA-15005: name "testdbdup/controlfile/control01.ctl" is already used by an
existing alias
<Note: remove the existing control file from ASM location and again execute the command>
SQL> @/tmp/control_testdbdup.sql
Control file created.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TSTDBDUP READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost tmp]$
No comments:
Post a Comment