Active RMAN duplicate clone 12c using section size and compress backupset

Overview of New PULL method

The original “push” process is based on image copies.With Oracle Database 12c, a “pull” (or restore) process is based on backup sets. A connection is first established with the source database. The auxiliary instance then retrieves the required database files from the source database as backup sets. A restore operation is performed from the auxiliary instance instance. Therefore, fewer resources are used on the source database.

Both TNS connections are required on target and auxiliary instances.Based on the DUPLICATE clauses, RMAN dynamically determines which process to use (push or pull’. This ensures that existing customized scripts continue to function.

  • When you specify USING BACKUPSET, RMAN uses the pull method.

  • When you specify SET ENCRYPTION before the DUPLICATE command, RMAN

automatically uses the pull method and creates backup sets. The backups sent to the destination are encrypted.

  • The SECTION SIZE clause divides data files into subsections that are restored in parallel across multiple channels on the auxiliary database. For an effective use of parallelization, allocate more AUXILIARY channels.

  • With the USING COMPRESSED BACKUPSET clause, the files are transferred as compressed backup sets. RMAN uses unused block compression while creating backups,thus reducing the size of backups that are transported over the network.

NOOPEN

You might duplicate a database with RMAN for various reasons. In earlier versions a recovered duplicated database was automatically opened. By default, this functionality continues with the Oracle Database 12c.
What is new is that you have an option to finish the duplication process with the database in a mounted, but not opened state. This is useful when the attempt to open the database would produce errors and in all cases when you want to modify initialization settings, which are otherwise quite difficult to modify.
For example, you may want to move the location of the database to ASM. Also when you are performing an upgrade, where the database must not be open with resetlogs, prior to running upgrade scripts.
The NOOPEN option allows the duplication to create a new database as part of an upgrade procedure and leaves the database in a state ready for opening in upgrade mode and subsequent execution of upgrade scripts.

Multi-section now is available on image copy.

 

Active duplication step

Create init parameter file from source and change relevant parameters like control_file,db_name etc.I have provided below sample of init.ora.

[oracle@rac1 dbs]$ cat initrcat.ora
*._catalog_foreign_restore=FALSE
*.audit_file_dest=’/u01/app/product/admin/rcat/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.2.0′
*.control_files=’+DATA/controlrcat.clt’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’rcat’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest=’/u01/app/product’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rcatXDB)’
*.enable_pluggable_database=true
*.open_cursors=300
*.optimizer_adaptive_features=FALSE
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=570m
*.processes=300
*.remote_login_passwordfile=’exclusive’
*.session_cached_cursors=1000
*.sga_target=1710m
*.shared_pool_size=629145600
*.undo_tablespace=’UNDOTBS1′

 

Create required audit directory in target

mkdir -p /u01/app/product/admin/rcat/adump

Add entry as static listener.ora under $GRID_HOME/network/admin

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = rcat)
(ORACLE_HOME = /u01/app/product/ora12c/12.1.0/dbhome_1)
(GLOBAL_DBNAME = rcat)
)
)

Add corresponding entry in tnsnames.ora

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/product/ora12c/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

############Source###################

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

 

#######Target#################
RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rcat)
(UR=A)
)
)

 

The (UR=A) clause for TNS connect strings was created in response to an enhancement request.
This clause can be inserted into the “(CONNECT_DATA=” section of a TNS connect string and allow a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users. This feature is introduced since Oracle 10g

If UR=A is not added,you will get following error during connection to target in nomount state in next steps:-

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Create password file

cd $ORACLE_HOME/dbs

orapwd file=orapwrcat password=oracle

Now start target database in nomount

export ORACLE_SID=rcat

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 23:20:41 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> shutdow abort;
  ORACLE instance shut down.
  SQL> startup nomount pfile='initrcat.ora';
  ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 2925456 bytes
Variable Size 805309552 bytes
Database Buffers 973078528 bytes
Redo Buffers 13848576 bytes

 

Please verify password file is working as expected in target

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
—————————— —– —– —– —– —– —– ———-
SYS TRUE TRUE FALSE FALSE FALSE FALSE 1

Register in listener

SQL>alter system register;

Now connect to target and auxiliary and start duplicate using new 12c parameters

[oracle@rac1 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@rcat

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Dec 9 23:24:40 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1489144156)
connected to auxiliary database: RCAT (not mounted)

RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 4;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> duplicate target database to rcat from active database section size 500M using compressed backupset;

Starting Duplicate Db at 09-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=35 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=36 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=37 device type=DISK
current log archived

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 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”ORCL” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”RCAT” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone from service ‘orcl’ using compressed backupset
primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”ORCL” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”RCAT” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

Starting restore at 09-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=33 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=35 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=36 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=37 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/controlrcat.clt
Finished restore at 09-DEC-17

database mounted

contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 15 to new;
set newname for clone datafile 16 to new;
set newname for clone datafile 17 to new;
restore
from service ‘orcl’ section size
500 m using compressed backupset
clone database
;
sql ‘alter system archive log current’;
}
executing 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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-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

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA/RCAT/DATAFILE/system.313.962321229
channel ORA_AUX_DISK_1: restoring section 1 of 2
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to +DATA/RCAT/DATAFILE/sysaux.325.962321231
channel ORA_AUX_DISK_2: restoring section 1 of 2
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00004 to +DATA/RCAT/DATAFILE/undotbs1.326.962321233
channel ORA_AUX_DISK_3: restoring section 1 of 1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00005 to +DATA/RCAT/DATAFILE/system.327.962321241
channel ORA_AUX_DISK_4: restoring section 1 of 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:27
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to +DATA/RCAT/DATAFILE/system.313.962321229
channel ORA_AUX_DISK_3: restoring section 2 of 2
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:25
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00003 to +DATA/RCAT/DATAFILE/sysaux.325.962321231
channel ORA_AUX_DISK_4: restoring section 2 of 2
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:02:03
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00006 to +DATA/RCAT/DATAFILE/users.328.962321353
channel ORA_AUX_DISK_2: restoring section 1 of 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:05
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00007 to +DATA/RCAT/DATAFILE/sysaux.329.962321359
channel ORA_AUX_DISK_2: restoring section 1 of 2
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:01:46
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00007 to +DATA/RCAT/DATAFILE/sysaux.329.962321359
channel ORA_AUX_DISK_3: restoring section 2 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA/RCAT/DATAFILE/undotbs2.330.962321375
channel ORA_AUX_DISK_1: restoring section 1 of 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:13
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00015 to +DATA/RCAT/DATAFILE/system.331.962321379
channel ORA_AUX_DISK_3: restoring section 1 of 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00016 to +DATA/RCAT/DATAFILE/sysaux.332.962321381
channel ORA_AUX_DISK_4: restoring section 1 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA/RCAT/DATAFILE/sysaux.332.962321381
channel ORA_AUX_DISK_1: restoring section 2 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA/RCAT/DATAFILE/my_tbs.333.962321401
channel ORA_AUX_DISK_1: restoring section 1 of 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:01:08
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:50
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:37
Finished restore at 09-DEC-17

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service ‘orcl’ using compressed backupset
archivelog from scn 3152882;
switch clone datafile all;
}
executing Memory Script

Starting restore at 09-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

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=47
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=48
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:02
Finished restore at 09-DEC-17

datafile 1 switched to datafile copy
input datafile copy RECID=27 STAMP=962321483 file name=+DATA/RCAT/DATAFILE/system.313.962321229
datafile 3 switched to datafile copy
input datafile copy RECID=28 STAMP=962321483 file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231
datafile 4 switched to datafile copy
input datafile copy RECID=29 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/system.327.962321241
datafile 6 switched to datafile copy
input datafile copy RECID=31 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/users.328.962321353
datafile 7 switched to datafile copy
input datafile copy RECID=32 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359
datafile 8 switched to datafile copy
input datafile copy RECID=33 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375
datafile 15 switched to datafile copy
input datafile copy RECID=34 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/system.331.962321379
datafile 16 switched to datafile copy
input datafile copy RECID=35 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381
datafile 17 switched to datafile copy
input datafile copy RECID=36 STAMP=962321486 file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401

contents of Memory Script:
{
set until scn 3153106;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-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

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file +DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_47.336.962321481
archived log for thread 1 with sequence 48 is already on disk as file +DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_48.337.962321483
archived log file name=+DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_47.336.962321481 thread=1 sequence=47
archived log file name=+DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_48.337.962321483 thread=1 sequence=48
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-DEC-17
Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”RCAT” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”RCAT” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “RCAT” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
‘+DATA/RCAT/DATAFILE/system.313.962321229’,
‘+DATA/RCAT/DATAFILE/system.327.962321241’,
‘+DATA/RCAT/DATAFILE/system.331.962321379’
CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE ‘i2’
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
catalog clone datafilecopy “+DATA/RCAT/DATAFILE/sysaux.325.962321231”,
“+DATA/RCAT/DATAFILE/undotbs1.326.962321233”,
“+DATA/RCAT/DATAFILE/users.328.962321353”,
“+DATA/RCAT/DATAFILE/sysaux.329.962321359”,
“+DATA/RCAT/DATAFILE/undotbs2.330.962321375”,
“+DATA/RCAT/DATAFILE/sysaux.332.962321381”,
“+DATA/RCAT/DATAFILE/my_tbs.333.962321401”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231 RECID=1 STAMP=962321529
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233 RECID=2 STAMP=962321529
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/users.328.962321353 RECID=3 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359 RECID=4 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375 RECID=5 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381 RECID=6 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401 RECID=7 STAMP=962321530

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=962321529 file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=962321529 file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/users.328.962321353
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375
datafile 16 switched to datafile copy
input datafile copy RECID=6 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381
datafile 17 switched to datafile copy
input datafile copy RECID=7 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
sql clone “alter pluggable database all open”;
}
executing Memory Script

sql statement: alter pluggable database all open
Cannot remove created server parameter file
Finished Duplicate Db at 09-DEC-17

Create password file in ASM now

Create spfile.

Now add database in srvctl

srvctl add database -db rcat -oraclehome $ORACLE_HOME
srvctl modify database -db rcat -pwfile +DATA/pwdrcat.ora
srvctl modify database -db rcat -spfile +DATA/spfilercat.ora

Issue faced:-

RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name = ‘RCAT’ comment= ‘Modified by RMAN duplicate’ scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Please remove the database from srvctl if already added before duplicate

[oracle@rac1 dbs]$ srvctl remove database -d rcat

 

Database backup and restore after manual catalog RMAN backup

Database backup and restore after manual catalog RMAN backup

1.Please take backup of primary database ORCL

rman target / nocatalog log=/tmp/rman_bkp.log << EOF1
run
{
backup as compressed backupset database format ‘/opt/app/oratest1/bkp/ORCL_%U’;
backup as compressed backupset archivelog all format ‘/opt/app/oratest1/bkp/ORCL_ARCH_%U’;
}
exit;
EOF1

Please copy it in backup location to /opt/app/oratest1/bkp

2.Please start nomount RCATT database.

3.Please create controlfile from backup.The DB_CREATE_FILE_DEST should be set to diskgroup. Control file location should be in Diskgroup location
as control.ctl

CREATE CONTROLFILE SET DATABASE “RCATT” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘+DATA1/rcatt/onlinelog/group_1.261.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘+DATA1/rcatt/onlinelog/group_2.262.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘+DATA1/rcatt/onlinelog/group_3.263.825435445’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘+DATA1/rcatt/datafile/system.256.825435379’,
‘+DATA1/rcatt/datafile/sysaux.257.825435379’,
‘+DATA1/rcatt/datafile/undotbs1.258.825435381’,
‘+DATA1/rcatt/datafile/users.259.825696133’,
‘+DATA1/rcatt/datafile/orcl.266.825438401’
CHARACTER SET WE8MSWIN1252
;

4.rman target /
RMAN> catalog start with ‘/opt/app/oratest1/bkp’;
RMAN>restore database;
RMAN>list backup; (Please identify highest SCN of archivelog)
RMAN>recover database until sequence 26;(Please specify highest SCN)

Please ignore the following error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/11/2013 17:02:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1545450

5.Please open database with resetlogs option.

SQL>alter database open resetlogs

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

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

 

 

 

RMAN clone from data guard DR to different host and different backup location

This document describe the step to clone database in another host using RMAN from DR (Data gurad) .We need not to connect to source database.Moreover we will use different location for backup.

A. Please follow below step for pre-requisites setup mentioned in below url

1.Copy the init parameter file from source .
2.In target place this init file to $ORACLE_HOME/dbs
3.Change following parameter in init file to make it compatible to single instance.
Replace original instance name to target name in pfile .For my case it should XCENTBK as target.

4.Please startup database in no mount mode.
5.Please check if sufficient space is available or not.

Please follow below url and check step 1 to step 7

RMAN cloning oracle 12c database RAC to single instance from tape backup to another host in new database name

B.Copy the source to target to your preferred directory.In my case I used NFS mount point so I did not need to copy.

Take backup from DR in some mount point.if it is NFS shared between both target and source,then no need to copy.Else you need to copy from source to target.

RMAN> run{
backup current controlfile format ‘/NFSPREPROD/XCENTPD11/rman/ctl_%t.ctl’;
BACKUP DATABASE FORMAT ‘/NFSPREPROD/XCENTPD11/rman/DB_%U’;
BACKUP archivelog all FORMAT ‘/NFSPREPROD/XCENTPD11/rman/ARCH_%U’;
}2> 3> 4> 5>

Starting backup at 10-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1282 instance=XCENTDR11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-NOV-17
channel ORA_DISK_1: finished piece 1 at 10-NOV-17
piece handle=/NFSPREPROD/XCENTPD11/rman/ctl_959667004.ctl tag=TAG20171110T061003 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-NOV-17

Starting backup at 10-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA_CRM/XCENTDR1/DATAFILE/undotbs1.319.943513509
input datafile file number=00012 name=+DATA_CRM/XCENTDR1/DATAFILE/sysaux.794.949336957
input datafile file number=00013 name=+DATA_CRM/XCENTDR1/DATAFILE/tbs_botdba.795.949336973
input datafile file number=00003 name=+DATA_CRM/XCENTDR1/DATAFILE/sysaux.317.943513509
input datafile file number=00011 name=+DATA_CRM/XCENTDR1/DATAFILE/sysaux.322.943513533
input datafile file number=00008 name=+DATA_CRM/XCENTDR1/DATAFILE/xstore_data.324.943513533
input datafile file number=00001 name=+DATA_CRM/XCENTDR1/DATAFILE/system.323.943513533
input datafile file number=00009 name=+DATA_CRM/XCENTDR1/DATAFILE/xstore_index.318.943513509
input datafile file number=00010 name=+DATA_CRM/XCENTDR1/DATAFILE/system.316.943513507
input datafile file number=00005 name=+DATA_CRM/XCENTDR1/DATAFILE/undotbs2.321.943513509
input datafile file number=00002 name=+DATA_CRM/XCENTDR1/DATAFILE/undotbs3.320.943513509
input datafile file number=00007 name=+DATA_CRM/XCENTDR1/DATAFILE/undotbs4.325.943513533
input datafile file number=00006 name=+DATA_CRM/XCENTDR1/DATAFILE/users.326.943513533
channel ORA_DISK_1: starting piece 1 at 10-NOV-17
channel ORA_DISK_1: finished piece 1 at 10-NOV-17
piece handle=/NFSPREPROD/XCENTPD11/rman/DB_rrsj6mqo_1_1 tag=TAG20171110T061032 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:35
Finished backup at 10-NOV-17

Starting backup at 10-NOV-17
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=6023 RECID=1131 STAMP=959663438
input archived log thread=1 sequence=16071 RECID=1132 STAMP=959663439
input archived log thread=4 sequence=5964 RECID=1130 STAMP=959663437
channel ORA_DISK_1: starting piece 1 at 10-NOV-17
channel ORA_DISK_1: finished piece 1 at 10-NOV-17
piece handle=/NFSPREPROD/XCENTPD11/rman/ARCH_rssj6n9n_1_1 tag=TAG20171110T061831 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 10-NOV-17

Starting Control File and SPFILE Autobackup at 10-NOV-17
piece handle=+DATA_CRM/XCENTDR1/AUTOBACKUP/2017_11_10/s_959663436.1158.959667529 comment=NONE
Finished Control File and SPFILE Autobackup at 10-NOV-17

Check backup and archivelog status

RMAN> list backup device type disk;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353069 Full 234.67M DISK 00:00:01 14-JUL-17
BP Key: 353069 Status: AVAILABLE Compressed: NO Tag: TAG20170714T164347
Piece Name: +DATA_CRM/XCENTDR1/AUTOBACKUP/2017_07_14/s_949336366.796.949337029
SPFILE Included: Modification time: 14-JUL-17
SPFILE db_unique_name: XCENTDR1
Standby Control File Included: Ckp SCN: 11148836288 Ckp time: 14-JUL-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353070 Full 234.67M DISK 00:00:02 14-JUL-17
BP Key: 353070 Status: AVAILABLE Compressed: NO Tag: TAG20170714T165308
Piece Name: +DATA_CRM/XCENTDR1/AUTOBACKUP/2017_07_14/s_949337171.797.949337589
SPFILE Included: Modification time: 14-JUL-17
SPFILE db_unique_name: XCENTDR1
Standby Control File Included: Ckp SCN: 11148841191 Ckp time: 14-JUL-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353071 Full 358.58M DISK 00:00:04 30-AUG-17
BP Key: 353071 Status: AVAILABLE Compressed: NO Tag: TAG20170830T113548
Piece Name: +DATA_CRM/XCENTDR1/AUTOBACKUP/2017_08_30/s_953379177.1147.953379351
SPFILE Included: Modification time: 30-AUG-17
SPFILE db_unique_name: XCENTDR1
Standby Control File Included: Ckp SCN: 16866102660 Ckp time: 30-AUG-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353075 Full 358.70M DISK 00:00:03 10-NOV-17
BP Key: 353075 Status: AVAILABLE Compressed: NO Tag: TAG20171110T043840
Piece Name: +DATA_CRM/XCENTDR1/AUTOBACKUP/2017_11_10/s_959659303.1157.959661523
SPFILE Included: Modification time: 11-SEP-17
SPFILE db_unique_name: XCENTDR1
Standby Control File Included: Ckp SCN: 51270799237 Ckp time: 10-NOV-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353076 Full 358.67M DISK 00:00:18 10-NOV-17
BP Key: 353076 Status: AVAILABLE Compressed: NO Tag: TAG20171110T061003
Piece Name: /NFSPREPROD/XCENTPD11/rman/ctl_959667004.ctl
Standby Control File Included: Ckp SCN: 51270820229 Ckp time: 10-NOV-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353077 Full 12.30G DISK 00:07:19 10-NOV-17
BP Key: 353077 Status: AVAILABLE Compressed: NO Tag: TAG20171110T061032
Piece Name: /NFSPREPROD/XCENTPD11/rman/DB_rrsj6mqo_1_1
List of Datafiles in backup set 353077
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/system.323.943513533
2 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/undotbs3.320.943513509
3 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/sysaux.317.943513509
4 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/undotbs1.319.943513509
5 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/undotbs2.321.943513509
6 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/users.326.943513533
7 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/undotbs4.325.943513533
8 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/xstore_data.324.943513533
9 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/xstore_index.318.943513509
10 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/system.316.943513507
11 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/sysaux.322.943513533
12 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/sysaux.794.949336957
13 Full 51270820229 10-NOV-17 +DATA_CRM/XCENTDR1/DATAFILE/tbs_botdba.795.949336973

BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
353078 13.64M DISK 00:00:00 10-NOV-17
BP Key: 353078 Status: AVAILABLE Compressed: NO Tag: TAG20171110T061831
Piece Name: /NFSPREPROD/XCENTPD11/rman/ARCH_rssj6n9n_1_1

List of Archived Logs in backup set 353078
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 16071 51270813536 10-NOV-17 51270820248 10-NOV-17
2 6023 51270813531 10-NOV-17 51270820243 10-NOV-17
4 5964 51270813544 10-NOV-17 51270820237 10-NOV-17

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
353079 Full 358.70M DISK 00:00:02 10-NOV-17
BP Key: 353079 Status: AVAILABLE Compressed: NO Tag: TAG20171110T061846
Piece Name: +DATA_CRM/XCENTDR1/AUTOBACKUP/2017_11_10/s_959663436.1158.959667529
SPFILE Included: Modification time: 11-SEP-17
SPFILE db_unique_name: XCENTDR1
Standby Control File Included: Ckp SCN: 51270820229 Ckp time: 10-NOV-17

C.Now connect to auxiliary and run the clone command

rman auxiliary /

run {
set newname for database to ‘+DATA/XCENTBK/DATAFILE/%b’;
duplicate target database to XCENTBK
backup location ‘/NFSPREPROD/XCENTPD11/rman’
logfile
group 1 (‘+FRA/redoXCENT1.log’) size 512M,
group 2 (‘+FRA/redoXCENT2.log’) size 512M,
group 3 (‘+FRA/redoXCENT3.log’) size 512M;
}

————–Log———————–

oracle@cdv1proccmdbf01:XCENTBK[/u01/app/oracle/product/12.1.0/db_1/dbs]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 – Production on Fri Nov 10 06:24:23 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: XCENTBK (not mounted)

RMAN> run {
2> set newname for database to ‘+DATA/XCENTBK/DATAFILE/%b’;
3> duplicate target database to XCENTBK
4> backup location ‘/NFSPREPROD/XCENTPD11/rman’
5> logfile
6> group 1 (‘+FRA/redoXCENT1.log’) size 512M,
7> group 2 (‘+FRA/redoXCENT2.log’) size 512M,
8> group 3 (‘+FRA/redoXCENT3.log’) size 512M;
9> }

executing command: SET NEWNAME

Starting Duplicate Db at 2017-11-10 06:24:39

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 11811160064 bytes

Fixed Size 7655840 bytes
Variable Size 3623882336 bytes
Database Buffers 8153726976 bytes
Redo Buffers 25894912 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”XCENTPD1” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”XCENTBK” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from ‘/NFSPREPROD/XCENTPD11/rman/ctl_959667004.ctl’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”XCENTPD1” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”XCENTBK” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 11811160064 bytes

Fixed Size 7655840 bytes
Variable Size 3623882336 bytes
Database Buffers 8153726976 bytes
Redo Buffers 25894912 bytes

Starting restore at 2017-11-10 06:25:32
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=58 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+FRA/XCENTBK/CONTROLFILE/control01.ctl
output file name=+FRA/XCENTBK/CONTROLFILE/control02.ctl
Finished restore at 2017-11-10 06:25:35

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=58 device type=DISK
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set until scn 51270820237;
set newname for datafile 1 to
“+DATA/XCENTBK/DATAFILE/system.323.943513533”;
set newname for datafile 2 to
“+DATA/XCENTBK/DATAFILE/undotbs3.320.943513509”;
set newname for datafile 3 to
“+DATA/XCENTBK/DATAFILE/sysaux.317.943513509”;
set newname for datafile 4 to
“+DATA/XCENTBK/DATAFILE/undotbs1.319.943513509”;
set newname for datafile 5 to
“+DATA/XCENTBK/DATAFILE/undotbs2.321.943513509”;
set newname for datafile 6 to
“+DATA/XCENTBK/DATAFILE/users.326.943513533”;
set newname for datafile 7 to
“+DATA/XCENTBK/DATAFILE/undotbs4.325.943513533”;
set newname for datafile 8 to
“+DATA/XCENTBK/DATAFILE/xstore_data.324.943513533”;
set newname for datafile 9 to
“+DATA/XCENTBK/DATAFILE/xstore_index.318.943513509”;
set newname for datafile 10 to
“+DATA/XCENTBK/DATAFILE/system.316.943513507”;
set newname for datafile 11 to
“+DATA/XCENTBK/DATAFILE/sysaux.322.943513533”;
set newname for datafile 12 to
“+DATA/XCENTBK/DATAFILE/sysaux.794.949336957”;
set newname for datafile 13 to
“+DATA/XCENTBK/DATAFILE/tbs_botdba.795.949336973”;
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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2017-11-10 06:25:46
using channel ORA_AUX_DISK_1

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 +DATA/XCENTBK/DATAFILE/system.323.943513533
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA/XCENTBK/DATAFILE/undotbs3.320.943513509
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA/XCENTBK/DATAFILE/sysaux.317.943513509
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA/XCENTBK/DATAFILE/undotbs1.319.943513509
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA/XCENTBK/DATAFILE/undotbs2.321.943513509
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA/XCENTBK/DATAFILE/users.326.943513533
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA/XCENTBK/DATAFILE/undotbs4.325.943513533
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA/XCENTBK/DATAFILE/xstore_data.324.943513533
channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA/XCENTBK/DATAFILE/xstore_index.318.943513509
channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA/XCENTBK/DATAFILE/system.316.943513507
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA/XCENTBK/DATAFILE/sysaux.322.943513533
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA/XCENTBK/DATAFILE/sysaux.794.949336957
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA/XCENTBK/DATAFILE/tbs_botdba.795.949336973
channel ORA_AUX_DISK_1: reading from backup piece /NFSPREPROD/XCENTPD11/rman/DB_rrsj6mqo_1_1
channel ORA_AUX_DISK_1: piece handle=/NFSPREPROD/XCENTPD11/rman/DB_rrsj6mqo_1_1 tag=TAG20171110T061032
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2017-11-10 06:27:02

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/system.278.959667947
datafile 2 switched to datafile copy
input datafile copy RECID=15 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/undotbs3.338.959667967
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/sysaux.277.959667947
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/undotbs1.266.959667947
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/undotbs2.339.959667965
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/users.332.959667983
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/undotbs4.337.959667981
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/xstore_data.264.959667947
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/xstore_index.336.959667947
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/system.340.959667963
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/sysaux.274.959667947
datafile 12 switched to datafile copy
input datafile copy RECID=25 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/sysaux.273.959667947
datafile 13 switched to datafile copy
input datafile copy RECID=26 STAMP=959668022 file name=+DATA/XCENTBK/DATAFILE/tbs_botdba.268.959667947

contents of Memory Script:
{
set until scn 51270820237;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2017-11-10 06:27:02
using channel ORA_AUX_DISK_1

starting media recovery

unable to find archived log
archived log thread=3 sequence=5982
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2017 06:27:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown archived log for thread 3 with sequence 5982 and starting SCN of 51270820229

D.Because of above error,If you try to open database in resetlog,you may get below error

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/10/2017 06:31:46
RMAN-06136: ORACLE error from auxiliary database: ORA-19838: Cannot use this control file to open database

E.Please take control file backup to trace and re-create the controlfile again.After that open the database.

SQL>alter database backup controlfile to trace as ‘/tmp/cntrl.ctl’;

E.Now set the DB_NAME to new name XCENTBK using below command

sql>alter system set db_name=’XCENTBK’ scope=spfile;

F.Please start the auxilairy DB in no mount again.

G.Re-create the control file after modifying DB name and set clause.Also check redolog file and datafile location point to new location of target.Now DB will open successfully after resetlog.

oracle@cdv1proccmdbf01:XCENTBK[/u01/app/oracle/product/12.1.0/db_1/dbs]$ dba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 10 06:42:23 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SYS@XCENTBK> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SYS@XCENTBK> startup nomount pfile=’initXCENTBK.ora’;
ORACLE instance started.

Total System Global Area 1.1811E+10 bytes
Fixed Size 7655840 bytes
Variable Size 3590327904 bytes
Database Buffers 8187281408 bytes
Redo Buffers 25894912 bytes
SYS@XCENTBK> CREATE CONTROLFILE SET DATABASE “XCENTBK” RESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 18688
7 LOGFILE
8 GROUP 1 ‘+FRA/redoXCENT1.log’ SIZE 1024M BLOCKSIZE 512,
9 GROUP 2 ‘+FRA/redoXCENT2.log’ SIZE 1024M BLOCKSIZE 512
10 DATAFILE
11 ‘+DATA/XCENTBK/DATAFILE/system.278.959667947’,
12 ‘+DATA/XCENTBK/DATAFILE/undotbs3.338.959667967’,
13 ‘+DATA/XCENTBK/DATAFILE/sysaux.277.959667947’,
14 ‘+DATA/XCENTBK/DATAFILE/undotbs1.266.959667947’,
15 ‘+DATA/XCENTBK/DATAFILE/undotbs2.339.959667965’,
16 ‘+DATA/XCENTBK/DATAFILE/users.332.959667983’,
17 ‘+DATA/XCENTBK/DATAFILE/undotbs4.337.959667981’,
18 ‘+DATA/XCENTBK/DATAFILE/xstore_data.264.959667947’,
19 ‘+DATA/XCENTBK/DATAFILE/xstore_index.336.959667947’,
20 ‘+DATA/XCENTBK/DATAFILE/system.340.959667963’,
21 ‘+DATA/XCENTBK/DATAFILE/sysaux.274.959667947’,
22 ‘+DATA/XCENTBK/DATAFILE/sysaux.273.959667947’,
23 ‘+DATA/XCENTBK/DATAFILE/tbs_botdba.268.959667947’
24 CHARACTER SET AL32UTF8
25 ;

Control file created.

SYS@XCENTBK> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SYS@XCENTBK> alter database open resetlogs;

RMAN cloning oracle 12c database RAC to single instance from tape backup to another host in new database name

1.Copy the init parameter file from source .

2.In target place this init file to $ORACLE_HOME/dbs

3.Change following parameter in init file to make it compatible to single instance.

Replace original instance name to target name in pfile .For my case it should EBILTST as target.

##Remove following parameters##

EBILDR12.__data_transfer_cache_size=0
EBILDR11.__data_transfer_cache_size=0
EBILDR11.__db_cache_size=5939134464
EBILDR12.__db_cache_size=3154116608
EBILDR12.__java_pool_size=234881024
EBILDR11.__java_pool_size=234881024
EBILDR12.__large_pool_size=5335154688
EBILDR11.__large_pool_size=5268045824
EBILDR11.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
EBILDR12.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
EBILDR12.__pga_aggregate_target=3456106496
EBILDR11.__pga_aggregate_target=3456106496
EBILDR12.__sga_target=17179869184
EBILDR11.__sga_target=17179869184
EBILDR12.__shared_io_pool_size=301989888
EBILDR11.__shared_io_pool_size=469762048
EBILDR11.__shared_pool_size=4127195136
EBILDR12.__shared_pool_size=7012876288
EBILDR12.__streams_pool_size=0
EBILDR11.__streams_pool_size=0

## Create the destination in target server##
*.audit_file_dest=’/u01/app/oracle/admin/EBILTST/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’12.1.0.2.0′

##Control file location needs to be changed##
*.control_files=’+REDO_BILLP/EBILTST/CONTROLFILE/current.256.926689595′
*.db_block_size=8192

##The datafile and online log destination may be changed##
*.db_create_file_dest=’+DATA_BILLP’
*.db_create_online_log_dest_1=’+REDO_BILLP’

*.db_domain=’tdeprdcl.internal’
*.db_file_multiblock_read_count=8
*.db_files=2048

##DB_NAME should be changed to target database name##
*.db_name=’EBILTST’

*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=1020054732800
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EBILTSTXDB)’

##Instance_number should be removed##
EBILDR11.instance_number=1
EBILDR12.instance_number=2

*.job_queue_processes=8
*.log_archive_dest_1=’LOCATION=+ARCH_BILLP’
*.log_archive_format=’log_%d_%t_%s_%r.arc’
*.log_buffer=2147483648
*.memory_max_target=0
*.memory_target=0
*.open_cursors=500
*.open_links=10
*.open_links_per_instance=100
*.processes=3000

##Remote listener should be removed##
*.remote_listener=’BSCSprd1-scan.tdeprdcl.internal:1591′

*.remote_login_passwordfile=’exclusive’
*.sessions=2272
*.sga_target=17179869184
*.standby_file_management=’AUTO’

##Thread parameter must be removed##
EBILDR12.thread=2
EBILDR11.thread=1

*.undo_retention=3600

##Remove undo tablespace for 2nd instance parameter##
EBILDR12.undo_tablespace=’UNDOTBS2′

EBILTST.undo_tablespace=’UNDOTBS1′

##This parameter is only useful if huge pages is setup##
*.use_large_pages=’ONLY’

4.Please startup database in no mount mode.

5.Please create following redo log directory in ASM

+FRA/EBILTST

6.Please provide tns entry of source database in target tnsnames.ora

7.Please check if sufficient space is available or not.

8.Now you can execute following command for database duplicate.Please note if it is big database,run it in nohup.

A.Nohup step:-

Please add more process for auxiliary channel if you need more parallelism for big database.

vi rman_restore.cmd

run {
set until time = “TO_DATE(’11/02/2017 18:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)’;
4> DUPLICATE target DATABASE TO EBILTST
logfile
5> 6> group 1 (‘+FRA/EBILTST/redolog1a.log’) size 512M,
7> group 2 (‘+FRA/EBILTST/redolog2a.log’) size 512M,
8> group 3 (‘+FRA/EBILTST/redolog3a.log’) size 512M;
9> }

Export Instance name of  target database

export ORACLE_SID=EBILTST

vi rman_restore.sh
rman catalog BACKUP/p4ssw0rd@ERMANP01 auxiliary / target sys/eb4i3ll5p@EBILPD1 auxiliary / msglog /home/oracle/rman_restore.log cmdfile=/home/oracle/rman_restore.cmd

nohup rman_restore.sh &

keep checking /home/oracle/rman_restore.log

B.Direct from terminal:-

[oracle@cdv1pradbakdbv01 dbs]$ rman catalog BACKUP/p4ssw0rd@ERMANP01 auxiliary / target sys/eb4i3ll5p@EBILPD1

Recovery Manager: Release 12.1.0.2.0 – Production on Fri Nov 3 07:41:09 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: EBILPD1 (DBID=831846396)
connected to recovery catalog database
connected to auxiliary database: EBILTST (not mounted)

RMAN> run {
set until time = “TO_DATE(’11/02/2017 18:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)’;
4> DUPLICATE target DATABASE TO EBILTST
logfile
5> 6> group 1 (‘+FRA/EBILTST/redolog1a.log’) size 512M,
7> group 2 (‘+FRA/EBILTST/redolog2a.log’) size 512M,
8> group 3 (‘+FRA/EBILTST/redolog3a.log’) size 512M;
9> }

executing command: SET until clause

old RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)’;
new RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OCRMPD1,OB2BARLIST=CDV-PRD-DB-OCRMPD1_MENSUAL)’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 03-NOV-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=737 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=2 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

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 5184159744 bytes

Fixed Size 5294472 bytes
Variable Size 1358956152 bytes
Database Buffers 3808428032 bytes
Redo Buffers 11481088 bytes

contents of Memory Script:
{
set until scn 44821971954;
sql clone “alter system set control_files =
”+DATA/EBILTST/CONTROLFILE/current.498.959067807” comment=
”Set by RMAN” scope=spfile”;
sql clone “alter system set db_name =
”EBILPD1” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”EBILTST” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set control_files = ”+DATA/EBILTST/CONTROLFILE/current.498.959067807” comment= ”Set by RMAN” scope=spfile

sql statement: alter system set db_name = ”EBILPD1” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”EBILTST” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 5184159744 bytes

Fixed Size 5294472 bytes
Variable Size 1358956152 bytes
Database Buffers 3808428032 bytes
Redo Buffers 11481088 bytes

Starting restore at 03-NOV-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=51 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

channel ORA_AUX_DISK_1: restoring control file
ORA-19625: error identifying file /NFSPREPROD/CF_HPDP/ctrl_EBILPD1_44288.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is control file (/NFSPREPROD/CF_HPDP/ctrl_EBILPD1_44288.dbf)
ORA-19601: output file is control file (+DATA/EBILTST/CONTROLFILE/current.498.959067807)

failover to previous backup

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-831846396-20171102-00
channel ORA_AUX_SBT_TAPE_1: piece handle=c-831846396-20171102-00 tag=TAG20171102T143147
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
output file name=+DATA/EBILTST/CONTROLFILE/current.498.959067807
Finished restore at 03-NOV-17

database mounted

contents of Memory Script:
{
set until scn 44821971954;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
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

Starting restore at 03-NOV-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36833:958948289:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36833:958948289:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:31
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36834:958948289:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36834:958948289:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:01
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36832:958948289:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36832:958948289:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00006 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36837:958948315:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36837:958948315:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36836:958948315:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36836:958948315:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to +DATA
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36835:958948289:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_MENSUAL<EBILPD1_36835:958948289:1>.dbf tag=TAG20171101T223128
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:25
Finished restore at 03-NOV-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=942 STAMP=959068298 file name=+DATA/EBILTST/DATAFILE/system.462.959068193
datafile 2 switched to datafile copy
input datafile copy RECID=943 STAMP=959068299 file name=+DATA/EBILTST/DATAFILE/undotbs2.470.959067947
datafile 3 switched to datafile copy
input datafile copy RECID=944 STAMP=959068300 file name=+DATA/EBILTST/DATAFILE/sysaux.461.959068249
datafile 4 switched to datafile copy
input datafile copy RECID=945 STAMP=959068301 file name=+DATA/EBILTST/DATAFILE/undotbs1.487.959068031
datafile 5 switched to datafile copy
input datafile copy RECID=946 STAMP=959068302 file name=+DATA/EBILTST/DATAFILE/tbs_botdba.464.959068085
datafile 6 switched to datafile copy
input datafile copy RECID=947 STAMP=959068303 file name=+DATA/EBILTST/DATAFILE/users.463.959068149

contents of Memory Script:
{
set until time “to_date(‘NOV 02 2017 18:59:00’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 03-NOV-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

starting media recovery

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2172
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36854:959005893:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36854:959005893:1>.dbf tag=TAG20171102T143053
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=2112
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36852:959005892:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36852:959005892:1>.dbf tag=TAG20171102T143053
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2112.370.959068393 thread=2 sequence=2112
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2172.371.959068347 thread=1 sequence=2172
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2172.371.959068347 RECID=9361 STAMP=959068349
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2173
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36855:959005893:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36855:959005893:1>.dbf tag=TAG20171102T143053
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2173.371.959068439 thread=1 sequence=2173
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2112.370.959068393 RECID=9362 STAMP=959068393
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=2113
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36856:959005896:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36856:959005896:1>.dbf tag=TAG20171102T143053
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2113.370.959068483 thread=2 sequence=2113
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2173.371.959068439 RECID=9363 STAMP=959068439
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2174
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36859:959023854:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36859:959023854:1>.dbf tag=TAG20171102T193054
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:55
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2174.371.959068529 thread=1 sequence=2174
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2113.370.959068483 RECID=9364 STAMP=959068484
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=2114
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36858:959023854:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36858:959023854:1>.dbf tag=TAG20171102T193054
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2114.370.959068585 thread=2 sequence=2114
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2174.371.959068529 RECID=9365 STAMP=959068540
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2175
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36860:959023854:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36860:959023854:1>.dbf tag=TAG20171102T193054
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2175.371.959068629 thread=1 sequence=2175
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2114.370.959068585 RECID=9366 STAMP=959068585
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=2115
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36861:959023855:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-ARCHIVE-EBILLPD1<EBILPD1_36861:959023855:1>.dbf tag=TAG20171102T193054
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2115.370.959068675 thread=2 sequence=2115
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2115.370.959068675 RECID=9368 STAMP=959068676
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=2 sequence=2116
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2176
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CDV-PRD-DB-EBILLPD1_<EBILPD1_36874:959034744:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=CDV-PRD-DB-EBILLPD1_<EBILPD1_36874:959034744:1>.dbf tag=TAG20171102T223224
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2116.370.959068721 thread=2 sequence=2116
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2175.371.959068629 RECID=9367 STAMP=959068629
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2176.369.959068721 thread=1 sequence=2176
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_1_seq_2176.369.959068721 RECID=9369 STAMP=959068723
channel clone_default: deleting archived log(s)
archived log file name=+ARCHIVE/EBILTST/ARCHIVELOG/2017_11_03/thread_2_seq_2116.370.959068721 RECID=9370 STAMP=959068724
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-NOV-17
Oracle instance started

Total System Global Area 5184159744 bytes

Fixed Size 5294472 bytes
Variable Size 1358956152 bytes
Database Buffers 3808428032 bytes
Redo Buffers 11481088 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”EBILTST” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”EBILTST” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 5184159744 bytes

Fixed Size 5294472 bytes
Variable Size 1358956152 bytes
Database Buffers 3808428032 bytes
Redo Buffers 11481088 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “EBILTST” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘+FRA/EBILTST/redolog1a.log’ ) SIZE 512 M ,
GROUP 2 ( ‘+FRA/EBILTST/redolog2a.log’ ) SIZE 512 M ,
GROUP 3 ( ‘+FRA/EBILTST/redolog3a.log’ ) SIZE 512 M
DATAFILE
‘+DATA/EBILTST/DATAFILE/system.462.959068193’
CHARACTER SET WE8ISO8859P15

 

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “+DATA/EBILTST/DATAFILE/undotbs2.470.959067947”,
“+DATA/EBILTST/DATAFILE/sysaux.461.959068249”,
“+DATA/EBILTST/DATAFILE/undotbs1.487.959068031”,
“+DATA/EBILTST/DATAFILE/tbs_botdba.464.959068085”,
“+DATA/EBILTST/DATAFILE/users.463.959068149”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/EBILTST/DATAFILE/undotbs2.470.959067947 RECID=1 STAMP=959068758
cataloged datafile copy
datafile copy file name=+DATA/EBILTST/DATAFILE/sysaux.461.959068249 RECID=2 STAMP=959068758
cataloged datafile copy
datafile copy file name=+DATA/EBILTST/DATAFILE/undotbs1.487.959068031 RECID=3 STAMP=959068758
cataloged datafile copy
datafile copy file name=+DATA/EBILTST/DATAFILE/tbs_botdba.464.959068085 RECID=4 STAMP=959068758
cataloged datafile copy
datafile copy file name=+DATA/EBILTST/DATAFILE/users.463.959068149 RECID=5 STAMP=959068758

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=959068758 file name=+DATA/EBILTST/DATAFILE/undotbs2.470.959067947
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=959068758 file name=+DATA/EBILTST/DATAFILE/sysaux.461.959068249
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=959068758 file name=+DATA/EBILTST/DATAFILE/undotbs1.487.959068031
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=959068758 file name=+DATA/EBILTST/DATAFILE/tbs_botdba.464.959068085
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=959068758 file name=+DATA/EBILTST/DATAFILE/users.463.959068149
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Executing: alter database enable block change tracking

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 03-NOV-17

RMAN>

Clone point in time Recovery using Dataprotector and RMAN for single instance to single instance

1.Login to source database

2. Create temporary pfile from spfile of source database

 

2.1. create temporary pfile from spfile

sqlplus / as sysdba

SQL>create pfile=’/tmp/inittest.ora’ from spfile;

 

 

2.2. Change parameters in init parameter to fit target database

 

Please change all source database name to target database name and create audit_file_dest and control_files loction

 

BKPTSTC.__data_transfer_cache_size=0

BKPTSTC.__db_cache_size=939524096

BKPTSTC.__inmemory_ext_roarea=0

BKPTSTC.__inmemory_ext_rwarea=0

BKPTSTC.__java_pool_size=16777216

BKPTSTC.__large_pool_size=33554432

BKPTSTC.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

BKPTSTC.__pga_aggregate_target=469762048

BKPTSTC.__sga_target=1375731712

BKPTSTC.__shared_io_pool_size=67108864

BKPTSTC.__shared_pool_size=301989888

BKPTSTC.__streams_pool_size=0

*.audit_file_dest=’/u01/app/oracle/admin/BKPTSTC/adump’

*.audit_trail=’db’

*.compatible=’12.2.0′

*.control_files=’/u01/app/oracle/oradata/BKPTSTC/control01.ctl’,’/u01/app/oracle/oradata/BKPTSTC/control02.ctl’

*.db_block_size=8192

*.db_create_file_dest=’/u01/app/oracle/oradata’

*.db_name=’BKPTSTC’

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=BKPTSTCXDB)’

*.local_listener=’LISTENER_BKPTSTC’

*.nls_language=’AMERICAN’

*.nls_territory=’AMERICA’

*.open_cursors=300

*.pga_aggregate_target=436m

*.processes=300

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=1305m

*.undo_tablespace=’UNDOTBS1′

3.  Startup no mount the database using temporary parameter file.

 

[oracle@xxx tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 26 20:07:11 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile=’/tmp/inittest.ora’;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
SQL> exit

 

4.Clone the database after connecting to source, catalog and auxiliary

 

[oracle@xxx tmp]$ rman target sys/oracle@bkptst catalog rco/rco@botdbadb auxiliary /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 20:09:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)
connected to recovery catalog database
connected to auxiliary database: BKPTSTC (not mounted)

RMAN> run {
set until time = “TO_DATE(’06/26/2017 11:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> DUPLICATE TARGET DATABASE TO BKPTSTC
5> logfile
6> group 1 (‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’) size 512M,
7> group 2 (‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’) size 512M,
8> group 3 (‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’) size 512M;
9> }

executing command: SET until clause

new RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=36 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

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 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
set until scn 1732640;
sql clone “alter system set db_name =
”BKPTST” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”BKPTSTC” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ”BKPTST” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”BKPTSTC” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

Starting restore at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=35 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2013020542-20170626-00
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2013020542-20170626-00 tag=TAG20170626T113217
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/BKPTSTC/control01.ctl
output file name=/u01/app/oracle/oradata/BKPTSTC/control02.ctl
Finished restore at 26-JUN-17

database mounted
datafile 7 not processed because file is offline

contents of Memory Script:
{
set until scn 1732640;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
restore
clone database
skip forever tablespace “USERS” ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=21 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k)
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy RECID=20 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n)
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy RECID=19 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r)
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy RECID=17 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q
destination for restore of datafile 00005: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q)
failover to previous backup

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=37 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
set until time “to_date(‘JUN 26 2017 11:59:00’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35

Executing: alter database datafile 7 offline drop
starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf thread=1 sequence=1
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:28
Finished recover at 26-JUN-17
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”BKPTSTC” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”BKPTSTC” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “BKPTSTC” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’ ) SIZE 512 M ,
GROUP 2 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’ ) SIZE 512 M ,
GROUP 3 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’ ) SIZE 512 M
DATAFILE
‘/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf RECID=1 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf RECID=2 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf RECID=3 STAMP=947708374

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened