RMAN duplicate database using backup location and skip oracle tablespace option:(PROD to TEST)
----------------------------------------------------------------------------------------------
1) create pfile from prodution database
2) modify the pfile according to test environment
db_name
admup dest
control_fiiles
db_file_name_conver
log_file_name_convert
3) create the required directories for controlfiles,datafiles,archivelogs,audit files etc
4) start the auxiliary database in nomount
startup nomount
4) connect to rman no need to connect to target database as we are using backup location.
rman auxiliary /
RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}
5) we can ignore below errors
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace
6) check the TEST database
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 12 11:16:21 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL> archive log lsit
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oracle/archive/PROD/TEST/
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
Rman log file:
=================
rman auxiliary /
RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3847 device type=DISK
Starting Duplicate Db at 12-NOV-15
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1056965528 bytes
Database Buffers 1073741824 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3847 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/data/oracle/archive/PROD/PRODBKP/PROD_control_file_c-4235066977-20151108-03.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1056965528 bytes
Database Buffers 1073741824 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK
Starting restore at 12-NOV-15
channel aux2: skipped, AUTOBACKUP already found
channel aux3: skipped, AUTOBACKUP already found
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:08
output file name=/data/oracle/PROD/TEST/control01.ctl
output file name=/data/oracle/PROD/TEST/control02.ctl
Finished restore at 12-NOV-15
database mounted
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 4 skipped by request
Datafile 5 skipped by request
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 13642258978044;
set newname for datafile 1 to
"/data/oracle/PROD/TEST/system01.dbf";
set newname for datafile 2 to
"/data/oracle/PROD/TEST/sysaux01.dbf";
set newname for datafile 3 to
"/data/oracle/PROD/TEST/undotbs01.dbf";
set newname for datafile 10 to
"/data/oracle/PROD/TEST/MISC_01.dbf";
set newname for datafile 11 to
"/data/oracle/PROD/TEST/MISC_02.dbf";
set newname for datafile 12 to
"/data/oracle/PROD/TEST/MISC_03.dbf";
set newname for datafile 30 to
"/data/oracle/PROD/TEST/undotbs02.dbf";
set newname for datafile 31 to
"/data/oracle/PROD/TEST/MISC_04.dbf";
set newname for datafile 33 to
"/data/oracle/PROD/TEST/MISC_05.dbf";
restore
clone database
skip forever tablespace "USERS",
"HIST_IDX",
"HIST_DETAIL" ;
}
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-NOV-15
channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to /data/oracle/PROD/TEST/system01.dbf
channel aux1: restoring datafile 00010 to /data/oracle/PROD/TEST/MISC_01.dbf
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00002 to /data/oracle/PROD/TEST/sysaux01.dbf
channel aux2: restoring datafile 00031 to /data/oracle/PROD/TEST/MISC_04.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak
channel aux3: starting datafile backup set restore
channel aux3: specifying datafile(s) to restore from backup set
channel aux3: restoring datafile 00003 to /data/oracle/PROD/TEST/undotbs01.dbf
channel aux3: restoring datafile 00012 to /data/oracle/PROD/TEST/MISC_03.dbf
channel aux3: restoring datafile 00033 to /data/oracle/PROD/TEST/MISC_05.dbf
channel aux3: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:08:36
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00011 to /data/oracle/PROD/TEST/MISC_02.dbf
channel aux2: restoring datafile 00030 to /data/oracle/PROD/TEST/undotbs02.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak
channel aux3: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak tag=HOT_DB_BK_LEVEL0
channel aux3: restored backup piece 1
channel aux3: restore complete, elapsed time: 00:15:21
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak tag=HOT_DB_BK_LEVEL0
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:18:51
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:13:25
Finished restore at 12-NOV-15
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=895576491 file name=/data/oracle/PROD/TEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=895576492 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=15 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=16 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=17 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=18 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_05.dbf
contents of Memory Script:
{
set until scn 13642258978044;
recover
clone database
skip forever tablespace "USERS",
"HIST_IDX",
"HIST_DETAIL" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 12-NOV-15
Executing: alter database datafile 4 offline drop
Executing: alter database datafile 5 offline drop
Executing: alter database datafile 6 offline drop
starting media recovery
channel aux1: starting archived log restore to default destination
channel aux1: restoring archived log
archived log thread=1 sequence=50993
channel aux1: restoring archived log
archived log thread=1 sequence=50994
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak tag=TAG20151108T015456
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:00:25
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc thread=1 sequence=50993
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc RECID=2 STAMP=895576512
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc thread=1 sequence=50994
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc RECID=1 STAMP=895576493
media recovery complete, elapsed time: 00:00:04
Finished recover at 12-NOV-15
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1073742744 bytes
Database Buffers 1056964608 bytes
Redo Buffers 4956160 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TEST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''TEST'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1073742744 bytes
Database Buffers 1056964608 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 ( '/data/oracle/redo/PROD/redo01a.log', '/data/oracle/redo/PROD/redo01b.log' ) SIZE 200 M REUSE,
GROUP 2 ( '/data/oracle/redo/PROD/redo02a.log', '/data/oracle/redo/PROD/redo02b.log' ) SIZE 200 M REUSE,
GROUP 3 ( '/data/oracle/redo/PROD/redo03b.log', '/data/oracle/redo/PROD/redo03a.log' ) SIZE 200 M REUSE,
GROUP 4 ( '/data/oracle/redo/PROD/redo04a.log', '/data/oracle/redo/PROD/redo04b.log' ) SIZE 200 M REUSE,
GROUP 5 ( '/data/oracle/redo/PROD/redo05b.log', '/data/oracle/redo/PROD/redo05a.log' ) SIZE 200 M REUSE
DATAFILE
'/data/oracle/PROD/TEST/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/oracle/PROD/TEST/temp01.dbf";
set newname for tempfile 2 to
"/data/oracle/PROD/TEST/temp02.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/data/oracle/PROD/TEST/sysaux01.dbf",
"/data/oracle/PROD/TEST/undotbs01.dbf",
"/data/oracle/PROD/TEST/MISC_01.dbf",
"/data/oracle/PROD/TEST/MISC_02.dbf",
"/data/oracle/PROD/TEST/MISC_03.dbf",
"/data/oracle/PROD/TEST/undotbs02.dbf",
"/data/oracle/PROD/TEST/MISC_04.dbf",
"/data/oracle/PROD/TEST/MISC_05.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /data/oracle/PROD/TEST/temp01.dbf in control file
renamed tempfile 2 to /data/oracle/PROD/TEST/temp02.dbf in control file
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/sysaux01.dbf RECID=1 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs01.dbf RECID=2 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_01.dbf RECID=3 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_02.dbf RECID=4 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_03.dbf RECID=5 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs02.dbf RECID=6 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_04.dbf RECID=7 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_05.dbf RECID=8 STAMP=895576536
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=895576536 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=3 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=4 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=5 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=6 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=7 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=8 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_05.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace
RMAN> exit
----------------------------------------------------------------------------------------------
1) create pfile from prodution database
2) modify the pfile according to test environment
db_name
admup dest
control_fiiles
db_file_name_conver
log_file_name_convert
3) create the required directories for controlfiles,datafiles,archivelogs,audit files etc
4) start the auxiliary database in nomount
startup nomount
4) connect to rman no need to connect to target database as we are using backup location.
rman auxiliary /
RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}
5) we can ignore below errors
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace
6) check the TEST database
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 12 11:16:21 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL> archive log lsit
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oracle/archive/PROD/TEST/
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
Rman log file:
=================
rman auxiliary /
RMAN> run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
duplicate database to 'TEST' backup location '/data/oracle/archive/PROD/PRODBKP/' nofilenamecheck skip tablespace USERS,HIST_DETAIL,HIST_IDX;
}
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3847 device type=DISK
Starting Duplicate Db at 12-NOV-15
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1056965528 bytes
Database Buffers 1073741824 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3847 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/data/oracle/archive/PROD/PRODBKP/PROD_control_file_c-4235066977-20151108-03.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1056965528 bytes
Database Buffers 1073741824 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK
Starting restore at 12-NOV-15
channel aux2: skipped, AUTOBACKUP already found
channel aux3: skipped, AUTOBACKUP already found
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:08
output file name=/data/oracle/PROD/TEST/control01.ctl
output file name=/data/oracle/PROD/TEST/control02.ctl
Finished restore at 12-NOV-15
database mounted
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 4 skipped by request
Datafile 5 skipped by request
Datafile 6 skipped by request
contents of Memory Script:
{
set until scn 13642258978044;
set newname for datafile 1 to
"/data/oracle/PROD/TEST/system01.dbf";
set newname for datafile 2 to
"/data/oracle/PROD/TEST/sysaux01.dbf";
set newname for datafile 3 to
"/data/oracle/PROD/TEST/undotbs01.dbf";
set newname for datafile 10 to
"/data/oracle/PROD/TEST/MISC_01.dbf";
set newname for datafile 11 to
"/data/oracle/PROD/TEST/MISC_02.dbf";
set newname for datafile 12 to
"/data/oracle/PROD/TEST/MISC_03.dbf";
set newname for datafile 30 to
"/data/oracle/PROD/TEST/undotbs02.dbf";
set newname for datafile 31 to
"/data/oracle/PROD/TEST/MISC_04.dbf";
set newname for datafile 33 to
"/data/oracle/PROD/TEST/MISC_05.dbf";
restore
clone database
skip forever tablespace "USERS",
"HIST_IDX",
"HIST_DETAIL" ;
}
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-NOV-15
channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to /data/oracle/PROD/TEST/system01.dbf
channel aux1: restoring datafile 00010 to /data/oracle/PROD/TEST/MISC_01.dbf
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00002 to /data/oracle/PROD/TEST/sysaux01.dbf
channel aux2: restoring datafile 00031 to /data/oracle/PROD/TEST/MISC_04.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak
channel aux3: starting datafile backup set restore
channel aux3: specifying datafile(s) to restore from backup set
channel aux3: restoring datafile 00003 to /data/oracle/PROD/TEST/undotbs01.dbf
channel aux3: restoring datafile 00012 to /data/oracle/PROD/TEST/MISC_03.dbf
channel aux3: restoring datafile 00033 to /data/oracle/PROD/TEST/MISC_05.dbf
channel aux3: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80873_1_895195794.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:08:36
channel aux2: starting datafile backup set restore
channel aux2: specifying datafile(s) to restore from backup set
channel aux2: restoring datafile 00011 to /data/oracle/PROD/TEST/MISC_02.dbf
channel aux2: restoring datafile 00030 to /data/oracle/PROD/TEST/undotbs02.dbf
channel aux2: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak
channel aux3: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80874_1_895196270.bak tag=HOT_DB_BK_LEVEL0
channel aux3: restored backup piece 1
channel aux3: restore complete, elapsed time: 00:15:21
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80875_1_895196715.bak tag=HOT_DB_BK_LEVEL0
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:18:51
channel aux2: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_db_80872_1_895195349.bak tag=HOT_DB_BK_LEVEL0
channel aux2: restored backup piece 1
channel aux2: restore complete, elapsed time: 00:13:25
Finished restore at 12-NOV-15
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=895576491 file name=/data/oracle/PROD/TEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=895576492 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=15 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=16 STAMP=895576492 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=17 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=18 STAMP=895576492 file name=/data/oracle/PROD/TEST/MISC_05.dbf
contents of Memory Script:
{
set until scn 13642258978044;
recover
clone database
skip forever tablespace "USERS",
"HIST_IDX",
"HIST_DETAIL" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 12-NOV-15
Executing: alter database datafile 4 offline drop
Executing: alter database datafile 5 offline drop
Executing: alter database datafile 6 offline drop
starting media recovery
channel aux1: starting archived log restore to default destination
channel aux1: restoring archived log
archived log thread=1 sequence=50993
channel aux1: restoring archived log
archived log thread=1 sequence=50994
channel aux1: reading from backup piece /data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak
channel aux1: piece handle=/data/oracle/archive/PROD/PRODBKP/PROD_arc_80877_1_895197296.bak tag=TAG20151108T015456
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:00:25
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc thread=1 sequence=50993
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50993_811696399.arc RECID=2 STAMP=895576512
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc thread=1 sequence=50994
channel clone_default: deleting archived log(s)
archived log file name=/data/oracle/archive/PROD/TEST/TEST_1_50994_811696399.arc RECID=1 STAMP=895576493
media recovery complete, elapsed time: 00:00:04
Finished recover at 12-NOV-15
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1073742744 bytes
Database Buffers 1056964608 bytes
Redo Buffers 4956160 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TEST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''TEST'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2223208 bytes
Variable Size 1073742744 bytes
Database Buffers 1056964608 bytes
Redo Buffers 4956160 bytes
allocated channel: aux1
channel aux1: SID=1284 device type=DISK
allocated channel: aux2
channel aux2: SID=2565 device type=DISK
allocated channel: aux3
channel aux3: SID=3846 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 ( '/data/oracle/redo/PROD/redo01a.log', '/data/oracle/redo/PROD/redo01b.log' ) SIZE 200 M REUSE,
GROUP 2 ( '/data/oracle/redo/PROD/redo02a.log', '/data/oracle/redo/PROD/redo02b.log' ) SIZE 200 M REUSE,
GROUP 3 ( '/data/oracle/redo/PROD/redo03b.log', '/data/oracle/redo/PROD/redo03a.log' ) SIZE 200 M REUSE,
GROUP 4 ( '/data/oracle/redo/PROD/redo04a.log', '/data/oracle/redo/PROD/redo04b.log' ) SIZE 200 M REUSE,
GROUP 5 ( '/data/oracle/redo/PROD/redo05b.log', '/data/oracle/redo/PROD/redo05a.log' ) SIZE 200 M REUSE
DATAFILE
'/data/oracle/PROD/TEST/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/oracle/PROD/TEST/temp01.dbf";
set newname for tempfile 2 to
"/data/oracle/PROD/TEST/temp02.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/data/oracle/PROD/TEST/sysaux01.dbf",
"/data/oracle/PROD/TEST/undotbs01.dbf",
"/data/oracle/PROD/TEST/MISC_01.dbf",
"/data/oracle/PROD/TEST/MISC_02.dbf",
"/data/oracle/PROD/TEST/MISC_03.dbf",
"/data/oracle/PROD/TEST/undotbs02.dbf",
"/data/oracle/PROD/TEST/MISC_04.dbf",
"/data/oracle/PROD/TEST/MISC_05.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /data/oracle/PROD/TEST/temp01.dbf in control file
renamed tempfile 2 to /data/oracle/PROD/TEST/temp02.dbf in control file
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/sysaux01.dbf RECID=1 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs01.dbf RECID=2 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_01.dbf RECID=3 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_02.dbf RECID=4 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_03.dbf RECID=5 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/undotbs02.dbf RECID=6 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_04.dbf RECID=7 STAMP=895576536
cataloged datafile copy
datafile copy file name=/data/oracle/PROD/TEST/MISC_05.dbf RECID=8 STAMP=895576536
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=895576536 file name=/data/oracle/PROD/TEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=3 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=4 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_02.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=5 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_03.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=6 STAMP=895576536 file name=/data/oracle/PROD/TEST/undotbs02.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=7 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_04.dbf
datafile 33 switched to datafile copy
input datafile copy RECID=8 STAMP=895576536 file name=/data/oracle/PROD/TEST/MISC_05.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace
RMAN> exit
No comments:
Post a Comment