Duplicate Database

Duplicate database until Point in Time recover, using backup location from RAC to single instance

Posted on by debasis maity

Prepare by:  Nurullah Sharif

Scope: Point in Time Recovery

 

Duplicate database until Point in Time recover, using backup location.

#PointInTimeRecovery #DuplicateDatabase #RestoreDatabaseUsingBackupLocation

 

We are using full backup of 28-11-2017 and archivelog backup of 29-11-2017

 

Step 1: Take full backup and archive log backup.

On target database :

Rman target /

run
{
backup as compressed BACKUPSET incremental level 0 tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/full_db_%t_set%s_piece%p_dbid%I.rman’ database;
backup current controlfile tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/ctl_%t_dbid%I.rman’;
backup as compressed BACKUPSET archivelog all tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/arc_%t_set%s_piece%p_dbid%I.rman’ ;
}

Transfer the backup file to target Environment. To create duplicate database.
Step 2: create pfile –

Source database is running in RAC, you must remove all the parameter which are related to Cluster which require to your configuration of single instance database.

SOURCE’s PFILE FILE-

SSPRODDB1.__db_cache_size=1006632960
SSPRODDB2.__db_cache_size=1174405120
SSPRODDB1.__java_pool_size=16777216
SSPRODDB2.__java_pool_size=16777216
SSPRODDB1.__large_pool_size=16777216
SSPRODDB2.__large_pool_size=16777216
SSPRODDB1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
SSPRODDB1.__pga_aggregate_target=1342177280
SSPRODDB2.__pga_aggregate_target=1342177280
SSPRODDB1.__sga_target=4026531840
SSPRODDB2.__sga_target=4026531840
SSPRODDB1.__shared_io_pool_size=536870912
SSPRODDB2.__shared_io_pool_size=536870912
SSPRODDB1.__shared_pool_size=2382364672
SSPRODDB2.__shared_pool_size=2214592512
SSPRODDB1.__streams_pool_size=33554432
SSPRODDB2.__streams_pool_size=33554432
*.audit_file_dest=’/u01/app/oracle/admin/SSPRODDB/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/ssproddb/controlfile/current.285.916959529′,’+DATA/ssproddb/controlfile/current.284.916959529′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’SSPRODDB’
*.db_recovery_file_dest_size=1099511627776
*.db_recovery_file_dest=’+FRA’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SSPRODDBXDB)’
SSPRODDB2.instance_number=2
SSPRODDB1.instance_number=1
*.log_archive_dest_1=’location=+FRA’
*.log_archive_format=’ARCH_%t_%s_%r.arc’
*.open_cursors=300
*.pga_aggregate_target=1342177280
*.processes=150
*.remote_listener=’scan:1521′
*.remote_login_passwordfile=’exclusive’
*.sga_target=4026531840
SSPRODDB2.thread=2
SSPRODDB1.thread=1
SSPRODDB2.undo_tablespace=’UNDOTBS2′
SSPRODDB1.undo_tablespace=’UNDOTBS1′

 

TARGET’s PFILE –

 

SSP.__db_cache_size=201326592
SSP.__java_pool_size=16777216
SSP.__large_pool_size=33554432
SSP.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
SSP.__pga_aggregate_target=738197504
SSP.__sga_target=1409286144
SSP.__shared_io_pool_size=0
SSP.__shared_pool_size=1107296256
SSP.__streams_pool_size=33554432
*.audit_file_dest=’/oradata1/SSP/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/oradata1/SSP/control01.ctl’,’/oradata1/SSP/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’SSP’
*.diagnostic_dest=’/oradata1/SSP’
*.memory_target=2048M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.undo_tablespace=’UNDOTBS2′
*.undo_tablespace=’UNDOTBS1′

 

 

Target Database name will SSP
Step 3: start database in nomount state and create spfile.

TARGET’s PFILE –

SQL> startup nomount pfile=’/oradata1/SSP/ssppfile.ora’;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

SQL> create spfile from pfile=’/oradata1/SSP/ssppfile.ora’;
File created.

SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
—————- ————
SSP STARTED

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
—————- ————
SSP STARTED

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/dbhome
_1/dbs/spfileSSP.ora

Database is start with newly created spfile

 

Note: we need to recover database until  29-11-2017 11am, to make sure, we need some specific information from Source database,

We have to find the nearest time of 11am , on which archive been switched.

To find out, run below command in Source database.

 

select sequence#,to_char(completion_time, ‘dd-mm-yyyy hh24:mi:ss’), to_char(next_time,’dd-mm-yyyy hh24:mi:ss’) from v$archived_log order by 2;

We found sequence 2281 were been recorded in time which is near to our required time.
Step 4: Connect RMAN auxiliary and run duplicate command with point in time recovery using until time argument.

rman auxiliary /

RMAN> run {
set newname for database to ‘/oradata1/SSP/%b’;
duplicate target database to SSP nofilenamecheck backup location ‘/oradata1/RMAN/SSPROD’
UNTIL TIME “TO_DATE(‘2017-11-29 11:26:28’, ‘YYYY-MM-DD HH24:MI:SS’)”
logfile
group 1 (‘/oradata1/SSP/redo01.log’) size 50M,
group 2 (‘/oradata1/SSP/redo02.log’) size 50M,
group 3 (‘/oradata1/SSP/redo03.log’) size 50M;
} 3> 4> 5> 6> 7> 8> 9>

executing command: SET NEWNAME
Starting Duplicate Db at 04-DEC-17
contents of Memory Script:
{
sql clone “alter system set db_name =
”SSPRODDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”SSP” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from ‘/oradata1/RMAN/SSPROD/ctl_961288234_dbid196682280.rman’;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”SSPRODDB” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”SSP” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

Starting restore at 04-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata1/SSP/control01.ctl
output file name=/oradata1/SSP/control02.ctl
Finished restore at 04-DEC-17

database mounted

released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=127 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=156 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=189 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=221 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=3 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=34 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=66 device type=DISK
allocated channel: ORA_AUX_DISK_9
channel ORA_AUX_DISK_9: SID=97 device type=DISK
allocated channel: ORA_AUX_DISK_10
channel ORA_AUX_DISK_10: SID=128 device type=DISK
allocated channel: ORA_AUX_DISK_11
channel ORA_AUX_DISK_11: SID=159 device type=DISK
allocated channel: ORA_AUX_DISK_12
channel ORA_AUX_DISK_12: SID=190 device type=DISK
contents of Memory Script:
{
set until scn 126463226;
set newname for datafile 1 to
“/oradata1/SSP/system.280.916959463”;
set newname for datafile 2 to
“/oradata1/SSP/sysaux.281.916959463”;
set newname for datafile 3 to
“/oradata1/SSP/undotbs1.282.916959463”;
set newname for datafile 4 to
“/oradata1/SSP/users.283.916959463”;
set newname for datafile 5 to
“/oradata1/SSP/undotbs2.291.916959571”;
set newname for datafile 6 to
“/oradata1/SSP/users.294.940601841”;
set newname for datafile 7 to
“/oradata1/SSP/system.289.942830555”;
set newname for datafile 8 to
“/oradata1/SSP/sysaux.288.944822531”;
restore
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

Starting restore at 04-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
using channel ORA_AUX_DISK_11
using channel ORA_AUX_DISK_12

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata1/SSP/system.280.916959463
channel ORA_AUX_DISK_1: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8943_piece1_dbid196682280.rman
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00002 to /oradata1/SSP/sysaux.281.916959463
channel ORA_AUX_DISK_2: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8942_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /oradata1/SSP/undotbs1.282.916959463
channel ORA_AUX_DISK_3: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8946_piece1_dbid196682280.rman
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00004 to /oradata1/SSP/users.283.916959463
channel ORA_AUX_DISK_4: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8947_piece1_dbid196682280.rman
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00005 to /oradata1/SSP/undotbs2.291.916959571
channel ORA_AUX_DISK_5: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8948_piece1_dbid196682280.rman
channel ORA_AUX_DISK_6: starting datafile backup set restore
channel ORA_AUX_DISK_6: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_6: restoring datafile 00006 to /oradata1/SSP/users.294.940601841
channel ORA_AUX_DISK_6: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8949_piece1_dbid196682280.rman
channel ORA_AUX_DISK_7: starting datafile backup set restore
channel ORA_AUX_DISK_7: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_7: restoring datafile 00007 to /oradata1/SSP/system.289.942830555
channel ORA_AUX_DISK_7: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8944_piece1_dbid196682280.rman
channel ORA_AUX_DISK_8: starting datafile backup set restore
channel ORA_AUX_DISK_8: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_8: restoring datafile 00008 to /oradata1/SSP/sysaux.288.944822531
channel ORA_AUX_DISK_8: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8945_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8946_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_5: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8948_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_5: restored backup piece 1
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_6: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8949_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_6: restored backup piece 1
channel ORA_AUX_DISK_6: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_7: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8944_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_7: restored backup piece 1
channel ORA_AUX_DISK_7: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_8: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8945_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_8: restored backup piece 1
channel ORA_AUX_DISK_8: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_4: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8947_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8943_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_2: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8942_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:35

Finished restore at 04-DEC-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=961823188 file name=/oradata1/SSP/system.280.916959463
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=961823188 file name=/oradata1/SSP/sysaux.281.916959463
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=961823188 file name=/oradata1/SSP/undotbs1.282.916959463
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=961823188 file name=/oradata1/SSP/users.283.916959463
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=961823188 file name=/oradata1/SSP/undotbs2.291.916959571
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=961823188 file name=/oradata1/SSP/users.294.940601841
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=961823188 file name=/oradata1/SSP/system.289.942830555
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=961823188 file name=/oradata1/SSP/sysaux.288.944822531

contents of Memory Script:
{
set until time “to_date(‘NOV 29 2017 11:26:28’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
delete archivelog
;
}

executing Memory Script

executing command: SET until clause
Starting recover at 04-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
using channel ORA_AUX_DISK_11
using channel ORA_AUX_DISK_12

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2278
channel ORA_AUX_DISK_1: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8959_piece1_dbid196682280.rman
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=2 sequence=1706
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=2279
channel ORA_AUX_DISK_2: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8960_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=2 sequence=1707
channel ORA_AUX_DISK_3: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8961_piece1_dbid196682280.rman
channel ORA_AUX_DISK_4: starting archived log restore to default destination
channel ORA_AUX_DISK_4: restoring archived log
archived log thread=1 sequence=2280
channel ORA_AUX_DISK_4: reading from backup piece /oradata1/RMAN/SSPROD/arc_961374649_set8977_piece1_dbid196682280.rman
channel ORA_AUX_DISK_5: starting archived log restore to default destination
channel ORA_AUX_DISK_5: restoring archived log
archived log thread=2 sequence=1708
channel ORA_AUX_DISK_5: restoring archived log
archived log thread=1 sequence=2281
channel ORA_AUX_DISK_5: reading from backup piece /oradata1/RMAN/SSPROD/arc_961374649_set8978_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8961_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:00
channel ORA_AUX_DISK_2: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8960_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_4: piece handle=/oradata1/RMAN/SSPROD/arc_961374649_set8977_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8959_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2278_916930730.dbf thread=1 sequence=2278
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1706_916930730.dbf thread=2 sequence=1706
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2278_916930730.dbf RECID=5 STAMP=961823193
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2279_916930730.dbf thread=1 sequence=2279
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1706_916930730.dbf RECID=4 STAMP=961823192
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1707_916930730.dbf thread=2 sequence=1707
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2279_916930730.dbf RECID=2 STAMP=961823189
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2280_916930730.dbf thread=1 sequence=2280
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1707_916930730.dbf RECID=1 STAMP=961823189
channel ORA_AUX_DISK_5: piece handle=/oradata1/RMAN/SSPROD/arc_961374649_set8978_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_5: restored backup piece 1
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:08
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1708_916930730.dbf thread=2 sequence=1708
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2280_916930730.dbf RECID=3 STAMP=961823191
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2281_916930730.dbf thread=1 sequence=2281
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2281_916930730.dbf RECID=6 STAMP=961823196
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1708_916930730.dbf RECID=7 STAMP=961823196
media recovery complete, elapsed time: 00:00:05
Finished recover at 04-DEC-17
Oracle instance started

Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”SSP” 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 = ”SSP” 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 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “SSP” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/oradata1/SSP/redo01.log’ ) SIZE 50 M ,
GROUP 2 ( ‘/oradata1/SSP/redo02.log’ ) SIZE 50 M ,
GROUP 3 ( ‘/oradata1/SSP/redo03.log’ ) SIZE 50 M
DATAFILE
‘/oradata1/SSP/system.280.916959463’
CHARACTER SET AL32UTF8

contents of Memory Script:

{
set newname for tempfile 1 to
“/oradata1/SSP/temp.290.916959535”;
switch clone tempfile all;
catalog clone datafilecopy “/oradata1/SSP/sysaux.281.916959463”,
“/oradata1/SSP/undotbs1.282.916959463”,
“/oradata1/SSP/users.283.916959463”,
“/oradata1/SSP/undotbs2.291.916959571”,
“/oradata1/SSP/users.294.940601841”,
“/oradata1/SSP/system.289.942830555”,
“/oradata1/SSP/sysaux.288.944822531”;
switch clone datafile all;
}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata1/SSP/temp.290.916959535 in control file

cataloged datafile copy
datafile copy file name=/oradata1/SSP/sysaux.281.916959463 RECID=1 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/undotbs1.282.916959463 RECID=2 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/users.283.916959463 RECID=3 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/undotbs2.291.916959571 RECID=4 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/users.294.940601841 RECID=5 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/system.289.942830555 RECID=6 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/sysaux.288.944822531 RECID=7 STAMP=961823218

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=961823218 file name=/oradata1/SSP/sysaux.281.916959463
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=961823218 file name=/oradata1/SSP/undotbs1.282.916959463
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=961823218 file name=/oradata1/SSP/users.283.916959463
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=961823218 file name=/oradata1/SSP/undotbs2.291.916959571
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=961823218 file name=/oradata1/SSP/users.294.940601841
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=961823218 file name=/oradata1/SSP/system.289.942830555
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=961823218 file name=/oradata1/SSP/sysaux.288.944822531

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 12/04/2017 05:06:59
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

SQL> alter database open resetlogs;
alter database open resetlogs

*

ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
Please refer to
RMAN Duplicate from RAC backup fails ORA-38856 (Doc ID 334899.1)

SQL> alter system set “_no_recovery_through_resetlogs”=true scope=spfile;
system altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL> select name, open_mode from v$database;
NAME OPEN_MODE
——— ——————–
SSP READ WRITE