A] DRP Restore Whole DB from scratch : DBPITR
A.i Perform Incomplete Recovery From ONLINE Backup
|
Goal
|
DRP - Restoration from Zero
SPFILE+CONTROLFILE+DATABASE
|
|
Version
|
10gR2
|
|
Context
|
Media DISK
NO CATALOG
|
|
Log as
|
/ AS SYSDBA
|
1] Pre-requisite on target server
- Instance
OFF
- Same
Oracle Version
- Filepath
already created
- Same
Path to RMAN Dump files
- DBID
found in controlfile autobackup filename : c-<DBID>-<DATE>-00
- ORACLE_SID=<DB_NAME>
2] Scripts
2.1 Restore SPFILE+Controlfile
|
No
|
Operation
|
Scope
|
Recovery
Catalog
|
Information
needed
|
Archivelog
|
|
1.
|
Cross-Restore
|
Spfile
|
No
|
DBID
|
NA
|
|
No
|
Operation
|
Scope
|
Recovery
Catalog
|
Information
needed
|
Archivelog
|
|
2.
|
Cross-Restore
|
Controlfile
|
No
|
DBID
|
NA
|
Note 1 : a « dummy » instance is
started to allow the SPFILE restoration.
Note 2 : the instance is bounced to read
newly restored init file.
RMAN script rma-1_rsc_<DB_NAME>_noc_disk.scr
$ cat
rma-1_rsc_<DB_NAME>_noc_disk.scr
connect
target /
run {
startup
nomount;
}
set
dbid=179121004 ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/rman/<db_name>/%F' ;
restore spfile from autobackup ;
}
shutdown
immediate ;
startup
nomount ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/rman/<db_name>/%F' ;
restore
controlfile from autobackup ; }
alter
database mount ;
2.2 Restore Database , Recover , Open
|
No
|
Operation
|
Scope
|
Recovery
Catalog
|
Information
needed
|
Archivelog
|
|
3.
|
Restore
Recover
Open
|
DB
|
No
|
Restore
endpoint
|
Yes
|
2.2.1 Find Log Sequence # to Recover Until
The LOG Sequence at which Recover will end
may be found in the Backup Log or with the
LIST BACKUP command as follow :
$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on
Wed Oct 19 18:26:22 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: <DB_NAME>
(DBID=179121004, not open)
RMAN> list backup of archivelog all completed after
'sysdate-1' ;
using target database control file instead of recovery
catalog
List of Backup Sets
===================
BS Key
Size Device Type Elapsed
Time Completion Time
------- ---------- ----------- ------------
--------------------
723
1.06G DISK 00:05:26 OCT 19 2011 13:27:55
BP Key:
723 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/<db_name>/ar_<DB_NAME>_s862_Uqumpgasl_1_1_20111019
…
List of
Archived Logs in backup set 732
Thrd Seq Low SCN
Low Time Next SCN Next Time
---- -------
---------- -------------------- ---------- ---------
1 319096 9556041564 OCT 19 2011 14:17:39 9556057679
OCT 19 2011 14:21:11
2.2.2 Preview the needed Dump files
RMAN script rma-2_rdb_<DB_NAME>_noc_disk_preview.scr
$ cat
rma-2_rdb_<DB_NAME>_noc_disk_preview.scr
run {
#set
until time "to_date('Oct 18 2010 16:40:00','MON DD YYYY HH24:MI:SS')"
;
set
until logseq 319097 ; # logseq max + 1
RESTORE
DATABASE PREVIEW;
}
$ rman target /
@rma-2_rdb_<DB_NAME>_noc_disk_preview.scr |grep 'Piece Name'
Piece
Name: /rman/<db_name>/db_F_<DB_NAME>_s864_Ur0mpgb7n_1_1_20111019
Piece Name:
/rman/<db_name>/db_F_<DB_NAME>_s863_Uqvmpgb7n_1_1_20111019
Piece
Name: /rman/<db_name>/db_I_<DB_NAME>_s869_Ur5mpge4e_1_1_20111019
Piece
Name: /rman/<db_name>/db_I_<DB_NAME>_s868_Ur4mpge4e_1_1_20111019
Piece
Name: /rman/<db_name>/ar_<DB_NAME>_s870_Ur6mpgeao_1_1_20111019
Check with
« xargs »
$ rman target / @rma-2_rdb_<DB_NAME>_noc_disk_preview.scr|grep
'Piece Name' |awk '{ print $3}' | xargs ls -l
-rw-r----- 1 oracle
oracle 847360 jun 5 02:33 /rman/<db_name>/ar_I_<DB_NAME>_s2953_Us9obd048_1_1_20130605
-rw-r----- 1 oracle
oracle 12288 jun 5 02:33 /rman/<db_name>/ar_I_<DB_NAME>_s2954_Usaobd048_1_1_20130605
-rw-r----- 1 oracle oracle
1251164160 jun 2 22:46 /rman/<db_name>/db_F_<DB_NAME>_s2921_Ur9ob79nm_1_1_20130602
-rw-r----- 1 oracle
oracle 860897280 jun 2 22:44 /rman/<db_name>/db_F_<DB_NAME>_s2922_Uraob79nm_1_1_20130602
-rw-r----- 1 oracle oracle
2470969344 jun 2 22:49 /rman/<db_name>/db_F_<DB_NAME>_s2923_Urbob79nm_1_1_20130602
-rw-r----- 1 oracle
oracle 427393024 jun 2 22:43 /rman/<db_name>/db_F_<DB_NAME>_s2924_Urcob79nn_1_1_20130602
-rw-r----- 1 oracle oracle
2060812288 jun 5 02:33 /rman/<db_name>/db_I_<DB_NAME>_s2949_Us5obcvhq_1_1_20130605
-rw-r----- 1 oracle
oracle 685768704 jun 5 02:28 /rman/<db_name>/db_I_<DB_NAME>_s2950_Us6obcvhq_1_1_20130605
-rw-r----- 1 oracle oracle
2412306432 jun 5 02:33 /rman/<db_name>/db_I_<DB_NAME>_s2951_Us7obcvhq_1_1_20130605
-rw-r----- 1 oracle
oracle 562012160 jun 5 02:28 /rman/<db_name>/db_I_<DB_NAME>_s2952_Us8obcvhr_1_1_20130605
You should ensure that you have those RMAN
dumps available.
RMAN script rma-2_rdb_<DB_NAME>_noc_disk.scr
$ cat
rma-2_rdb_<DB_NAME>_noc_disk.scr
connect
target /
run {
set until logseq 319097 thread 1 ; # applies
redo indluding logseq 319096
restore database ;
RECOVER DATABASE ;
ALTER DATABASE OPEN RESETLOGS ;
}
3] Run the RMAN scripts
FULL and
Incremental Backups are restored.
$ rman
@rma-1_rsc_<DB_NAME>_noc_disk.scr
connected to target database (not started)
startup failed: ORA-01078: failure in processing
system parameters
LRM-00109: could not open parameter file
'/oracle/10GDB/dbs/init<DB_NAME>.ora'
starting Oracle instance without parameter file for
retrival of spfile
/etc/profile[51]: termdef: not found.
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2094840 bytes
Variable Size 67111176 bytes
Database Buffers 83886080 bytes
Redo Buffers 6291456 bytes
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery
catalog
Starting restore at 19/10/2011
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day:
20111019
channel ORA_DISK_1: autobackup found:
/rman/<db_name>/c-179121004-20111019-01
channel ORA_DISK_1: SPFILE restore from autobackup
complete
Finished restore at 19/10/2011
Oracle instance shut down
connected to target database (not started)
/etc/profile[51]: termdef: not found.
Oracle instance started
Total System Global Area 3422552064 bytes
Fixed Size 2100240 bytes
Variable Size 2801796080 bytes
Database Buffers 603979776 bytes
Redo Buffers 14675968 bytes
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 19/10/2011
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day:
20111019
channel ORA_DISK_1: autobackup found:
/rman/<db_name>/c-179121004-20111019-01
channel ORA_DISK_1: control file restore from
autobackup complete
output
filename=/data/<db_name>/ctrl1<DB_NAME>.ctl
output filename=/data/<db_name>/ctrl2<DB_NAME>.ctl
Finished restore at 19/10/2011
database mounted
released channel: ORA_DISK_1
Recovery Manager complete.
$ export NLS_DATE_FORMAT='MON DD
YYYY HH24:MI:SS'
$ rman
@rma-2_rdb_<DB_NAME>_noc_disk.scr |tee
rma-2_rdb_<DB_NAME>_noc_disk.log
connected to target database: <DB_NAME>
(DBID=179121004, not open)
executing command: SET until clause
using target database control file instead of recovery
catalog
Starting restore at OCT 19 2011 18:41:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=540 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=542 devtype=DISK
channel ORA_DISK_2: starting datafile backupset
restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
restoring datafile 00002 to /data/<db_name>/undo.dbf
restoring datafile 00003 to
/data/<db_name>/users.dbf
…
channel ORA_DISK_1: reading from backup piece
/rman/<db_name>/db_F_<DB_NAME>_s864_Ur0mpgb7n_1_1_20111019
channel ORA_DISK_1: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s864_Ur0mpgb7n_1_1_20111019
tag=TAG20111019T132822
channel ORA_DISK_1: restore complete, elapsed time:
00:13:05
channel ORA_DISK_2: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s863_Uqvmpgb7n_1_1_20111019
tag=TAG20111019T132822
channel ORA_DISK_2: restore complete, elapsed time:
00:13:30
Finished restore at OCT 19 2011 18:55:02
Starting recover at OCT 19 2011 18:55:02
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile
backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00001:
/data/<db_name>/syst<DB_NAME>.dbf
…
destination for restore of datafile 00035:
/data/<db_name>/iqd_dat.dbf
channel ORA_DISK_1: reading from backup piece
/rman/<db_name>/db_I_<DB_NAME>_s869_Ur5mpge4e_1_1_20111019
channel ORA_DISK_2: starting incremental datafile
backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00002: /data/<db_name>/undo.dbf
destination for restore of datafile 00003:
/data/<db_name>/users.dbf
…
channel ORA_DISK_2: reading from backup piece
/rman/<db_name>/db_I_<DB_NAME>_s868_Ur4mpge4e_1_1_20111019
channel ORA_DISK_1: restored backup piece 1
piece handle=/rman/<db_name>/db_I_<DB_NAME>_s869_Ur5mpge4e_1_1_20111019
tag=TAG20111019T141749
channel ORA_DISK_1: restore complete, elapsed time:
00:00:03
channel ORA_DISK_2: restored backup piece 1
piece
handle=/rman/<db_name>/db_I_<DB_NAME>_s868_Ur4mpge4e_1_1_20111019
tag=TAG20111019T141749
channel ORA_DISK_2: restore complete, elapsed time:
00:00:04
starting media recovery
channel ORA_DISK_1: starting archive log restore to
default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=319096
channel ORA_DISK_1: reading from backup piece
/rman/<db_name>/ar_<DB_NAME>_s870_Ur6mpgeao_1_1_20111019
channel ORA_DISK_1: restored backup piece 1
piece
handle=/rman/<db_name>/ar_<DB_NAME>_s870_Ur6mpgeao_1_1_20111019
tag=I
channel ORA_DISK_1: restore complete, elapsed time:
00:00:02
archive log
filename=/arch/<db_name>/A3190961606572332.arch thread=1 sequence=319096
media recovery complete, elapsed time: 00:00:00
Finished recover at OCT 19 2011 18:55:12
database opened
Recovery Manager complete.
$
4] Restore Files to a new location
If the files are to be restored to a
different location:
ð Only
when cloning with DUPLICATE command, use instead :
Before RESTORE
SET
NEWNAME FOR DATAFILE <file_number> TO '/<new_path>/<file_name>.dbf';
May be generated at source instance :
select 'SET NEWNAME FOR DATAFILE
'||file_id||' TO '''||replace(file_name,'<old_path>','<new_path>')||'''
;' from dba_data_files ;
Before RECOVER
switch
datafile all;
Before OPEN RESETLOGS
SQL
"ALTER DATABASE RENAME FILE ''/<your_path>/log_01_1.rdo'' TO ''/<new_path>/log_01_1.rdo''
";
Ex.
Restore/recover/open script
connect target /
run {
SET NEWNAME FOR DATAFILE 1 TO '/data/<db_2>/system_01.dbf';
SET NEWNAME FOR DATAFILE 56 TO '/data/<db_2>/tbs_dat.dbf';
# set until logseq 319097 thread 1 ; # applies redo indluding logseq 319096
set until time "to_date('Oct 03
2016 21:30:00','MON DD YYYY HH24:MI:SS')" ;
restore database ;
switch datafile all;
RECOVER DATABASE ;
SQL "ALTER DATABASE RENAME
FILE ''/rdo1/<db_1>/log_01_1.rdo''
TO ''/rdo1/<db_2>/log_01_1.rdo'' ";
SQL "ALTER DATABASE RENAME
FILE ''/rdo1/<db_1>/log_02_1.rdo''
TO ''/rdo1/<db_2>/log_02_1.rdo'' ";
SQL "ALTER DATABASE RENAME
FILE ''/rdo1/<db_1>/log_03_1.rdo''
TO ''/rdo1/<db_2>/log_03_1.rdo'' ";
ALTER DATABASE OPEN RESETLOGS ;
}
Take care of
tempfiles :
$ sqlplus / as sysdba
SQL>
select NAME from v$tempfile;
/data/<base>/temp01.dbf
SQL>
alter database tempfile '/data/<base>/temp01.dbf' drop;
Database
altered.
SQL>
alter tablespace TEMP add tempfile '/data/<base>/temp01.dbf' size 512M
AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED ;
Tablespace
altered.
A.ii Perform Incomplete Recovery From a CLOSED Backup
A.ii.1 [10G 11G]
DB in
Noarchivelog Mode.
Same
prerequisite and method as in previous chapter, except the Restore/recover
part.
Without any
information, RMAN brings back LAST BACKUP.
- RMAN script rma_rcb_<DB_NAME>_noc_disk.scr
connect
target /
run {
startup
nomount;
}
set
dbid=2336449865 ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F'
;
restore spfile from autobackup maxdays 15 ;
}
shutdown
immediate ;
startup
nomount ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<db_name>/%F'
;
restore
controlfile from autobackup maxdays 15 ; }
alter
database mount ;
run {
# By
default, RMAN does not restore read-only files when you issue the RESTORE
DATABASE command.
RESTORE DATABASE CHECK READONLY;
#
Suppresses the application of redo logs during recovery. Only incremental
backups are applied.
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS ;
}
- Incident
Termination issued to instance processes. Waiting for
the processes to exit
sqlplus / as sysdba
SQL> shut abort
- Available backups in the disk
area :
$ ls
-l /rman/<db_name>
- Run the script
$ rman @rma_rcb_<DB_NAME>_noc_disk.scr |tee
rma_rcb_<DB_NAME>_noc_disk.log
connected to target database (not started)
startup failed: ORA-01078: failure in processing
system parameters
ORA-01565: error in identifying file '/work/oracle/<db_name>/pfile/spfile<DB_NAME>.ora'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or
directory
Additional information: 3
starting Oracle instance without parameter file for
retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size
2094840 bytes
Variable Size 67111176 bytes
Database Buffers 83886080 bytes
Redo Buffers 6291456 bytes
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery
catalog
Starting restore at 03/09/2012
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day:
20120903
channel ORA_DISK_1: autobackup found: /rman/<db_name>/c-2336449865-20120903-01
channel ORA_DISK_1: SPFILE restore from autobackup
complete
Finished restore at 03/09/2012
Oracle instance shut down
connected to target database (not started)
Oracle instance started
Total System Global Area 201326592 bytes
Fixed Size 2095160 bytes
Variable Size 134219720 bytes
Database Buffers 58720256 bytes
Redo Buffers 6291456 bytes
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 03/09/2012
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=499 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day:
20120903
channel ORA_DISK_1: autobackup found:
/rman/<db_name>/c-2336449865-20120903-01
channel ORA_DISK_1: control file restore from
autobackup complete
output filename=/rdo1/<db_name>/ctl/cntl_1.ctl
output filename=/rdo2/<db_name>/ctl/cntl_2.ctl
Finished restore at 03/09/2012
database mounted
released channel: ORA_DISK_1
Starting restore at 03/09/2012
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=499 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=497 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=496 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=495 devtype=DISK
channel ORA_DISK_1: starting datafile backupset
restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
restoring datafile 00004 to
/data/<db_name>/users_01.dbf
restoring datafile 00005 to
/data/<db_name>/xdb_01.dbf
channel ORA_DISK_1: reading from backup piece
/rman/<db_name>/db_F_<DB_NAME>_s27_U0rnk9d5q_1_1_20120903
channel ORA_DISK_2: starting datafile backupset
restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
restoring datafile 00002 to
/data/<db_name>/undo_01.dbf
restoring datafile 00007 to
/data/<db_name>/its_dat.dbf
channel ORA_DISK_2: reading from backup piece
/rman/<db_name>/db_F_<DB_NAME>_s25_U0pnk9d5p_1_1_20120903
channel ORA_DISK_3: starting datafile backupset
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
restoring datafile 00003 to
/data/<db_name>/sysaux_01.dbf
restoring datafile 00006 to
/data/<db_name>/adb_dat.dbf
channel ORA_DISK_3: reading from backup piece
/rman/<db_name>/db_F_<DB_NAME>_s26_U0qnk9d5q_1_1_20120903
channel ORA_DISK_4: starting datafile backupset
restore
channel ORA_DISK_4: specifying datafile(s) to restore
from backup set
restoring datafile 00001 to
/data/<db_name>/system_01.dbf
channel ORA_DISK_4: reading from backup piece
/rman/<db_name>/db_F_<DB_NAME>_s24_U0onk9d5p_1_1_20120903
channel ORA_DISK_1: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s27_U0rnk9d5q_1_1_20120903
tag=F
channel ORA_DISK_1: restore complete, elapsed time:
00:00:03
channel ORA_DISK_2: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s25_U0pnk9d5p_1_1_20120903
tag=F
channel ORA_DISK_2: restore complete, elapsed time:
00:00:10
channel ORA_DISK_3: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s26_U0qnk9d5q_1_1_20120903
tag=F
channel ORA_DISK_3: restore complete, elapsed time:
00:00:13
channel ORA_DISK_4: restored backup piece 1
piece
handle=/rman/<db_name>/db_F_<DB_NAME>_s24_U0onk9d5p_1_1_20120903
tag=F
channel ORA_DISK_4: restore complete, elapsed time:
00:00:28
Finished restore at 03/09/2012
Starting recover at 03/09/2012
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting incremental datafile
backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00004:
/data/<db_name>/users_01.dbf
destination for restore of datafile 00005:
/data/<db_name>/xdb_01.dbf
channel ORA_DISK_1: reading from backup piece /rman/<db_name>/db_I_<DB_NAME>_s32_U10nk9dgt_1_1_20120903
channel ORA_DISK_2: starting incremental datafile
backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00001:
/data/<db_name>/system_01.dbf
channel ORA_DISK_2: reading from backup piece
/rman/<db_name>/db_I_<DB_NAME>_s29_U0tnk9dgt_1_1_20120903
channel ORA_DISK_3: starting incremental datafile
backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00003:
/data/<db_name>/sysaux_01.dbf
destination for restore of datafile 00006:
/data/<db_name>/adb_dat.dbf
channel ORA_DISK_3: reading from backup piece
/rman/<db_name>/db_I_<DB_NAME>_s31_U0vnk9dgt_1_1_20120903
channel ORA_DISK_4: starting incremental datafile
backupset restore
channel ORA_DISK_4: specifying datafile(s) to restore
from backup set
destination for restore of datafile 00002:
/data/<db_name>/undo_01.dbf
destination for restore of datafile 00007:
/data/<db_name>/its_dat.dbf
channel ORA_DISK_4: reading from backup piece
/rman/<db_name>/db_I_<DB_NAME>_s30_U0unk9dgt_1_1_20120903
channel ORA_DISK_1: restored backup piece 1
piece
handle=/rman/<db_name>/db_I_<DB_NAME>_s32_U10nk9dgt_1_1_20120903
tag=TAG20120903T144340
channel ORA_DISK_1: restore complete, elapsed time:
00:00:00
channel ORA_DISK_2: restored backup piece 1
piece
handle=/rman/<db_name>/db_I_<DB_NAME>_s29_U0tnk9dgt_1_1_20120903
tag=TAG20120903T144340
channel ORA_DISK_2: restore complete, elapsed time:
00:00:00
channel ORA_DISK_3: restored backup piece 1
piece
handle=/rman/<db_name>/db_I_<DB_NAME>_s31_U0vnk9dgt_1_1_20120903
tag=TAG20120903T144340
channel ORA_DISK_3: restore complete, elapsed time:
00:00:00
channel ORA_DISK_4: restored backup piece 1
piece handle=/rman/<db_name>/db_I_<DB_NAME>_s30_U0unk9dgt_1_1_20120903
tag=TAG20120903T144340
channel ORA_DISK_4: restore complete, elapsed time:
00:00:01
Finished recover at 03/09/2012
database opened
Recovery Manager complete.
A.ii.2 [12C] version : how to restore
a whole CDB with RMAN ?
We’re about
to restore to a previous time from a cold backup a whole CDB : Root-PDBseed-PDB1-PDB2-PDB3
1. Verify every Backup set are available with VALIDATE HEADER
When planning your restore and recovery
operation, use RESTORE ... PREVIEW or RESTORE ... VALIDATE HEADER to ensure that all required backups are available or to identify
situations in which you may want to direct RMAN to use or avoid specific
backups.
$ rman @rman_restor_validate_head_dsk_noc_PRO12.scr
|tee rman_restor_validate_head_dsk_noc_PRO12.log
Recovery Manager: Release 12.1.0.2.0 - Production on
Wed Jul 22 15:30:45 2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
RMAN> connect
target *
2>
3> run {
4>
5> set until
time "to_date('Jul 20 2015 10:00:00','MON DD YYYY HH24:MI:SS')" ;
6>
7> RESTORE
DATABASE VALIDATE HEADER ;
8>
9> }
10>
11>
12>
13>
connected to target database: PRO12 (DBID=3979658611)
Starting restore at 22-JUL-15
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=593 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=66 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=219 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=459 device type=DISK
List of Backup Sets
===================
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
73
Full 210.44M DISK
00:00:33 20-JUL-15
BP Key:
73 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s77_U2dqcgobv_1_1_20150720
List of
Datafiles in backup set 73
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
1 Full 43392921 20-JUL-15 /data/pro12/system01.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
74 Full 210.91M
DISK 00:00:40 20-JUL-15
BP Key:
74 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s73_U29qcgobo_1_1_20150720
List of
Datafiles in backup set 74
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
3 Full 43392921 20-JUL-15 /data/pro12/sysaux01.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
78 Full
4.08M DISK 00:00:01 20-JUL-15
BP Key:
78 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s85_U2lqcgodd_1_1_20150720
List of
Datafiles in backup set 78
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
4 Full 43392921 20-JUL-15 /data/pro12/undotbs01.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
80
Full 51.66M DISK
00:00:09 20-JUL-15
BP Key:
80 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s86_U2mqcgode_1_1_20150720
List of
Datafiles in backup set 80
Container ID: 2,
PDB Name: PDB$SEED
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
5 Full 1752282 05-JUN-15 /data/pro12/pdbseed/system01.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
84
Full 1.05M DISK 00:00:00 20-JUL-15
BP Key:
84 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s88_U2oqcgodp_1_1_20150720
List of Datafiles in backup set 84
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
6 Full 43392921 20-JUL-15 /data/pro12/users01.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
83
Full 105.45M DISK
00:00:19 20-JUL-15
BP Key:
83 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s82_U2iqcgod5_1_1_20150720
List of
Datafiles in backup set 83
Container ID:
2, PDB Name: PDB$SEED
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
7 Full 1752282 05-JUN-15 /data/pro12/pdbseed/sysaux01.dbf
BS Key Type LV
Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------
---------------
79
Full 77.00M DISK
00:00:13 20-JUL-15
BP Key:
79 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s80_U2gqcgod5_1_1_20150720
List of
Datafiles in backup set 79
Container ID:
3, PDB Name: PDB1
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
10 Full 43392482 20-JUL-15 /data/pdb1/system01.dbf
16 Full 43392482 20-JUL-15 /data/pdb1/PDB1_soainfra.dbf
19 Full 43392482 20-JUL-15 /data/pdb1/PDB1_wlsservices.dbf
24 Full 43392482 20-JUL-15 /data/pdb1/GBM_mds.dbf
. . .
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
82
Full 10.68M DISK
00:00:02 20-JUL-15
BP Key:
82 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s87_U2nqcgodm_1_1_20150720
List of
Datafiles in backup set 82
Container ID:
5, PDB Name: PDB3
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
48 Full 43392900 20-JUL-15 /data/pdb3/PDB3_oam.dbf
51 Full 43392900 20-JUL-15 /data/pdb3/PDB3_mds.dbf
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
69
Full 33.00M DISK
00:00:06 20-JUL-15
BP Key: 69 Status: AVAILABLE Compressed: YES Tag: F
Piece
Name: /rman/pro12/full/db_F_PRO12_s74_U2aqcgobo_1_1_20150720
List of
Datafiles in backup set 69
Container ID:
4, PDB Name: PDB2
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
53 Full 43392741 20-JUL-15 /data/pdb2/PDB2_oim_arch_data.dbf
55 Full 43392741 20-JUL-15 /data/pdb2/PDB2_mds.dbf
56 Full 43392741 20-JUL-15 /data/pdb2/PDB2_ias_opss.dbf
validation succeeded for backup piece
Finished restore at 22-JUL-15
Recovery Manager complete.
-
Check PDB status
COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED FROM V$PDBS;
NAME
OPEN_MODE RESTRICTED
--------------- ---------- ----------
PDB$SEED
READ ONLY NO
PDB1
READ WRITE NO
PDB2
READ WRITE NO
PDB3
READ WRITE NO
2. Restore a whole CDB from an RMAN Closed Backup
- RMAN script rman_restor_cdb_PRO12_noc_disk.scr
$ cat
rman_restor_cdb_PRO12_noc_disk.scr
connect target /
shutdown immediate
startup nomount
set
dbid=3979658611 ;
RUN {
set until time "to_date('Jul 20 2015
10:00:00','MON DD YYYY HH24:MI:SS')" ;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/rman/pro12/ctl/%F' ;
restore controlfile from autobackup maxdays
15 ; }
alter database mount;
RUN {
set until time "to_date('Jul 20 2015
10:00:00','MON DD YYYY HH24:MI:SS')" ;
RESTORE DATABASE;
# Suppresses the
application of redo logs during recovery. Only incremental backups are applied.
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS ;
}
- Exécution
$ export NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'
$ rman @rman_restor_cdb_PRO12_noc_disk.scr |tee
rman_restor_cdb_PRO12_noc_disk_4.log
Recovery Manager: Release 12.1.0.2.0 - Production on
Tue Jul 28 17:02:31 2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
RMAN> connect target *
2>
3> shutdown immediate
4> startup nomount
5>
6> set dbid=3979658611 ;
7>
8> RUN {
9> set
until time "to_date('Jul 20 2015 10:00:00','MON DD YYYY HH24:MI:SS')"
;
10> SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/pro12/ctl/%F' ;
11> restore
controlfile from autobackup maxdays 15 ; }
12>
13> alter
database mount;
14>
15> RUN {
16> set
until time "to_date('Jul 20 2015 10:00:00','MON DD YYYY HH24:MI:SS')"
;
17> RESTORE
DATABASE;
18> # Suppresses the application of redo logs
during recovery. Only incremental backups are applied.
19> RECOVER
DATABASE NOREDO;
20> ALTER
DATABASE OPEN RESETLOGS ;
21> }
22>
23>
connected to target database: PRO12 (DBID=3979658611)
using target database control file instead of recovery
catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
Total System Global Area 6442450944 bytes
Fixed Size 2938792 bytes
Variable Size 3238004824 bytes
Database Buffers 3187671040 bytes
Redo Buffers 13836288 bytes
executing command: SET DBID
executing command: SET until clause
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 28-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=580 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day:
20150720
channel ORA_DISK_1: AUTOBACKUP found: /rman/pro12/ctl/c-3979658611-20150720-00
channel ORA_DISK_1: restoring control file from
AUTOBACKUP /rman/pro12/ctl/c-3979658611-20150720-00
channel ORA_DISK_1: control file restore from
AUTOBACKUP complete
output file name=/rdo1/pro12/ctl/control01.ctl
output file name=/rdo2/pro12/ctl/control02.ctl
Finished restore at 28-JUL-15
Statement processed
released channel: ORA_DISK_1
executing command: SET until clause
Starting restore at 28-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=393 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=582 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=6 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=201 device type=DISK
skipping datafile 5; already restored to file /data/pro12/pdbseed/system01.dbf
skipping datafile 7; already restored to file /data/pro12/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting datafile backup set
restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_DISK_1: restoring datafile 00053 to /data/pdb2/PDB2_oim_arch_data.dbf
channel ORA_DISK_1: restoring datafile 00055 to /data/pdb2/PDB2_mds.dbf
channel ORA_DISK_1: restoring datafile 00056 to /data/pdb2/PDB2_ias_opss.dbf
channel ORA_DISK_1: reading from backup piece /rman/pro12/full/db_F_PRO12_s74_U2aqcgobo_1_1_20150720
channel ORA_DISK_2: starting datafile backup set
restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
channel ORA_DISK_2: restoring datafile 00012 to /data/pdb2/system01.dbf
channel ORA_DISK_2: restoring datafile 00057 to /data/pdb2/PDB2_oimlob.dbf
channel ORA_DISK_2: restoring datafile 00058 to /data/pdb2/PDB2_iassdpm.dbf
channel ORA_DISK_2: reading from backup piece /rman/pro12/full/db_F_PRO12_s76_U2cqcgobo_1_1_20150720
channel ORA_DISK_3: starting datafile backup set
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
channel ORA_DISK_3: restoring datafile 00013 to /data/pdb2/sysaux01.dbf
channel ORA_DISK_3: restoring datafile 00052 to /data/pdb2/PDB2_soainfra.dbf
channel ORA_DISK_3: restoring datafile 00054 to /data/pdb2/PDB2_oim.dbf
channel ORA_DISK_3: reading from backup piece /rman/pro12/full/db_F_PRO12_s75_U2bqcgobo_1_1_20150720
channel ORA_DISK_4: starting datafile backup set
restore
channel ORA_DISK_4: specifying datafile(s) to restore
from backup set
channel ORA_DISK_4: restoring datafile 00011 to /data/pdb1/sysaux01.dbf
channel ORA_DISK_4: restoring datafile 00022 to /data/pdb1/PDB1_ias_opss.dbf
channel ORA_DISK_4: restoring datafile 00025 to /data/pdb1/GBM_ias_iau.dbf
channel ORA_DISK_4: restoring datafile 00028 to /data/pdb1/GBM_wlsservices.dbf
channel ORA_DISK_4: reading from backup piece /rman/pro12/full/db_F_PRO12_s78_U2eqcgoc6_1_1_20150720
channel ORA_DISK_1: piece handle=/rman/pro12/full/db_F_PRO12_s74_U2aqcgobo_1_1_20150720
tag=F
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time:
00:00:15
channel ORA_DISK_1: starting datafile backup set
restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/pro12/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman/pro12/full/db_F_PRO12_s77_U2dqcgobv_1_1_20150720
channel ORA_DISK_2: piece handle=/rman/pro12/full/db_F_PRO12_s76_U2cqcgobo_1_1_20150720
tag=F
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time:
00:00:15
channel ORA_DISK_2: starting datafile backup set
restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
channel ORA_DISK_2: restoring datafile 00003 to /data/pro12/sysaux01.dbf
channel ORA_DISK_2: reading from backup piece /rman/pro12/full/db_F_PRO12_s73_U29qcgobo_1_1_20150720
channel ORA_DISK_3: piece handle=/rman/pro12/full/db_F_PRO12_s75_U2bqcgobo_1_1_20150720
tag=F
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time:
00:00:30
channel ORA_DISK_3: starting datafile backup set
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
channel ORA_DISK_3: restoring datafile 00020 to /data/pdb1/PDB1_UMS.dbf
channel ORA_DISK_3: restoring datafile 00021 to /data/pdb1/PDB1_ias_iau.dbf
channel ORA_DISK_3: restoring datafile 00023 to /data/pdb1/GBM_soainfra.dbf
channel ORA_DISK_3: restoring datafile 00029 to /data/pdb1/GBM_svctbl.dbf
channel ORA_DISK_3: reading from backup piece /rman/pro12/full/db_F_PRO12_s81_U2hqcgod5_1_1_20150720
channel ORA_DISK_4: piece handle=/rman/pro12/full/db_F_PRO12_s78_U2eqcgoc6_1_1_20150720
tag=F
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time:
00:00:30
channel ORA_DISK_4: starting datafile backup set
restore
channel ORA_DISK_4: specifying datafile(s) to restore
from backup set
channel ORA_DISK_4: restoring datafile 00015 to /data/pdb3/sysaux01.dbf
channel ORA_DISK_4: restoring datafile 00050 to /data/pdb3/PDB3_ias_iau.dbf
channel ORA_DISK_4: reading from backup piece /rman/pro12/full/db_F_PRO12_s79_U2fqcgocm_1_1_20150720
channel ORA_DISK_3: piece handle=/rman/pro12/full/db_F_PRO12_s81_U2hqcgod5_1_1_20150720
tag=F
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time:
00:00:03
channel ORA_DISK_3: starting datafile backup set
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
channel ORA_DISK_3: restoring datafile 00017 to /data/pdb1/PDB1_mds.dbf
channel ORA_DISK_3: restoring datafile 00018 to /data/pdb1/PDB1_svctbl.dbf
channel ORA_DISK_3: restoring datafile 00026 to /data/pdb1/GBM_ias_opss.dbf
channel ORA_DISK_3: restoring datafile 00027 to /data/pdb1/GBM_UMS.dbf
channel ORA_DISK_3: reading from backup piece /rman/pro12/full/db_F_PRO12_s83_U2jqcgod6_1_1_20150720
channel ORA_DISK_3: piece handle=/rman/pro12/full/db_F_PRO12_s83_U2jqcgod6_1_1_20150720
tag=F
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time:
00:00:07
channel ORA_DISK_3: starting datafile backup set
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
channel ORA_DISK_3: restoring datafile 00004 to /data/pro12/undotbs01.dbf
channel ORA_DISK_3: reading from backup piece /rman/pro12/full/db_F_PRO12_s85_U2lqcgodd_1_1_20150720
channel ORA_DISK_3: piece handle=/rman/pro12/full/db_F_PRO12_s85_U2lqcgodd_1_1_20150720
tag=F
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time:
00:00:03
channel ORA_DISK_3: starting datafile backup set
restore
channel ORA_DISK_3: specifying datafile(s) to restore
from backup set
channel ORA_DISK_3: restoring datafile 00010 to /data/pdb1/system01.dbf
channel ORA_DISK_3: restoring datafile 00016 to /data/pdb1/PDB1_soainfra.dbf
channel ORA_DISK_3: restoring datafile 00019 to /data/pdb1/PDB1_wlsservices.dbf
channel ORA_DISK_3: restoring datafile 00024 to /data/pdb1/GBM_mds.dbf
channel ORA_DISK_3: reading from backup piece /rman/pro12/full/db_F_PRO12_s80_U2gqcgod5_1_1_20150720
channel ORA_DISK_1: piece handle=/rman/pro12/full/db_F_PRO12_s77_U2dqcgobv_1_1_20150720
tag=F
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time:
00:00:44
channel ORA_DISK_1: starting datafile backup set
restore
channel ORA_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_DISK_1: restoring datafile 00014 to /data/pdb3/system01.dbf
channel ORA_DISK_1: restoring datafile 00049 to /data/pdb3/PDB3_ias_opss.dbf
channel ORA_DISK_1: reading from backup piece /rman/pro12/full/db_F_PRO12_s84_U2kqcgodd_1_1_20150720
channel ORA_DISK_2: piece handle=/rman/pro12/full/db_F_PRO12_s73_U29qcgobo_1_1_20150720
tag=F
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time:
00:00:44
channel ORA_DISK_2: starting datafile backup set
restore
channel ORA_DISK_2: specifying datafile(s) to restore
from backup set
channel ORA_DISK_2: restoring datafile 00048 to /data/pdb3/PDB3_oam.dbf
channel ORA_DISK_2: restoring datafile 00051 to /data/pdb3/PDB3_mds.dbf
channel ORA_DISK_2: reading from backup piece /rman/pro12/full/db_F_PRO12_s87_U2nqcgodm_1_1_20150720
channel ORA_DISK_4: piece handle=/rman/pro12/full/db_F_PRO12_s79_U2fqcgocm_1_1_20150720
tag=F
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time:
00:00:29
channel ORA_DISK_4: starting datafile backup set
restore
channel ORA_DISK_4: specifying datafile(s) to restore
from backup set
channel ORA_DISK_4: restoring datafile 00006 to /data/pro12/users01.dbf
channel ORA_DISK_4: reading from backup piece /rman/pro12/full/db_F_PRO12_s88_U2oqcgodp_1_1_20150720
channel ORA_DISK_3: piece handle=/rman/pro12/full/db_F_PRO12_s80_U2gqcgod5_1_1_20150720
tag=F
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time:
00:00:16
channel ORA_DISK_4: piece handle=/rman/pro12/full/db_F_PRO12_s88_U2oqcgodp_1_1_20150720
tag=F
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time:
00:00:01
channel ORA_DISK_2: piece handle=/rman/pro12/full/db_F_PRO12_s87_U2nqcgodm_1_1_20150720
tag=F
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time:
00:00:03
channel ORA_DISK_1: piece handle=/rman/pro12/full/db_F_PRO12_s84_U2kqcgodd_1_1_20150720
tag=F
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time:
00:00:15
Finished restore at 28-JUL-15
Starting recover at 28-JUL-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished recover at 28-JUL-15
Statement processed
Recovery Manager complete.
PRO12 srv-appbd-1:/work/oracle/pro12/rman/scr/restore
>
- Check
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 28
17:06:05 2015
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, OLAP, Advanced Analytics and
Real Application Testing options
SQL> SELECT NAME, OPEN_MODE, RESTRICTED FROM
V$PDBS;
NAME OPEN_MODE RES
------------------------------ ---------- ---
PDB$SEED READ ONLY NO
PDB1 READ WRITE NO
PDB2 READ WRITE NO
PDB3 READ WRITE NO
B] Restore a DB Subset with SKIP TABLESPACE
If the issue
comes from « Drop User » or « Drop Objects other than
tables » ( Procedure , sequences … ), we need to use RMAN to restore a DB
subset ( TSPITR is not from any help ).
1] Prepare the Restore Factory
On a different
server
- we create the DB directories
- then, we restore an init file ( as seen in A] ) or we copy an init file from source environment ( since we are not in a DRP situation )
- the directories written in init should be created too
- we start the instance : startup nomount;
2] Run the restore
2.1 Restore Controlfile ( DBID still needed )
$ cat
rma-1_rsc_<BASE>_noc_disk.scr
set
dbid=1504614898;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<base>/%F' ;
restore
controlfile from autobackup ; }
alter
database mount ;
Note :
Possible ERRORS
- I got Rman Duplication Failed
with ORA-00059 [ID 1339185.1] because my init file had a smaller
‘db_files’ parameter value than source DB.
- RMAN-06172: no autobackup found
or specified handle is not a valid copy or piece
Because dump
file was on NFS filesystem, move it or set the event :
$ cat rma-1_rsc_<BASE>_noc_disk.scr
set dbid=2299686464;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
TO '/rman/<base>/%F' ;
restore controlfile from AUTOBACKUP ; }
alter database mount ;
$ cat rma-1_rsc_<BASE>_bup_disk.scr
shutdown abort
startup nomount
run {
restore
controlfile from '/oracle/10GDB/dbs/c-2299686464-20141105-00' ; }
alter database mount ;
or
event="10298
trace name context forever, level 32"
2.2 Restore/Recover DB UNTIL before the incident
Get SKIP
Tablespace from
select ''||tablespace_name||',' from
dba_tablespaces where
tablespace_name not in ('SYSTEM','SYSAUX','UNDO','<APPLICATION_TBS>','XDB','USERS') ;
$ cat rma-2_rdb_<BASE>_noc_disk.scr
connect target /
run {
set until time
"to_date('Mar 5 2012 01:00:00','MON DD YYYY HH24:MI:SS')" ;
restore
database skip forever tablespace TEMP,DRSYS,BAL_DAT,CGP_DAT,CTXSYS,ISR_DAT,HST60_DAT,DPC_DAT,
… KPA_IDXSP,KTO_IDXSP ;
RECOVER
database skip forever tablespace
TEMP,DRSYS,BAL_DAT,CGP_DAT,CTXSYS,ISR_DAT,HST60_DAT,DPC_DAT,…
,KPA_IDXSP,KTO_IDXSP ;
}
$ export
NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'
$ rman
@rma-2_rdb_<BASE>_noc_disk.scr |tee rma-2_rdb_<BASE>_noc_disk.log
2.3 Open resetlogs
SQL>
alter database open resetlogs ;
Database
altered.
When one
needs to expdp some schemas, TEMP tablespace should be created :
CREATE
TEMPORARY TABLESPACE TEMP_AUC TEMPFILE '/data/<base>/temp_auc.dbf' SIZE
128M AUTOEXTEND ON NEXT 64M MAXSIZE 1024M TABLESPACE GROUP '' EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 1M;
alter
database default temporary tablespace temp_auc ;
2.4 From a Cold Backup script
$ cat
rma_rcf_<BASE>_noc_disk.scr
connect
target /
set
dbid=1645003607 ;
startup
nomount ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/<base>/ctl/%F'
;
restore
controlfile from autobackup maxdays 15 ; }
alter
database mount ;
$ cat
rma_rcb_<BASE>_noc_disk.scr
connect
target /
run {
# By
default, RMAN does not restore read-only files when you issue the RESTORE
DATABASE command.
RESTORE DATABASE skip forever tablespace
TS1_DAT,
TS2_DAT
CHECK READONLY;
#
Suppresses the application of redo logs during recovery. Only incremental
backups are applied.
RECOVER DATABASE skip forever tablespace
TS1_DAT,
TS2_DAT
NOREDO;
# ALTER DATABASE OPEN RESETLOGS ;
}
C] Duplicate
0] [10G] RMAN Duplicate
0.1 Pre Requisite
§ Target Database
PRD DB is accessed during RMAN Duplicate to
get information about DB physical structure, SCN …
Caution : it is very
dangerous to use commands like ‘shutdown’ in RMAN script as it would operate on
PRD DB !!!
We will use the keyword CLONE to inform RMAN
that we’re acting on Auxiliary instance.
§ Auxiliary Database
Clone DB
§ RMAN Catalog
Gives information about last full backup (
mandatory for Backup on Flash-copy or Standby ) :
Until_Time =
last full backup completion time + 3 seconds
sqlplus -s ${rma_rcat_str} << ! | tail -1 > ${unt_time_rma}
set head off feed off
alter session set nls_date_format='Mon DD YYYY HH24:MI:SS' ;
select MAX_T + 3/(24*60*60) from (
select distinct max(completion_time) over ( partition by name ) MAX_T
from rc_backup_set a, rc_database b
where incremental_level = '0' and a.db_id = b.dbid and name =
'${ora_sid_src}' ) ;
!
§ To
prepare the auxiliary instance used in RMAN DUPLICATE DATABASE, carry out the
following tasks:
- Connection to Clone instance will use OS /
authentification.
- Modify Initialization Parameter File for the Auxiliary
Instance
*.audit_file_dest
*.background_dump_dest
*.control_files
*.core_dump_dest
*.db_name
*.db_unique_name
*.instance_name
*.log_archive_dest_1
*.log_archive_format
*.user_dump_dest=
the convert parameters will inform RMAN of the new path.
db_file_name_convert=('/data/<old_path>','/data/<new_path>')
log_file_name_convert=('/rdo1/<old_path>','/rdo1/<new_path>','/rdo2/<old_path>','/rdo2/<new_path>')
Copy INIT FILE to
$ORACLE_HOME/dbs
0.2 RMAN script
Duplicate script to restore an
RMAN closed backup ( SET UNTIL not needed ).
#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+
#
#
RMAN duplicate script
#
#
Environment: PRD
#
Target: Primary DB
#
Auxiliary: Clone DB
#
Recovery catalog: Y
#
Restore type: offline
#
Mode: duplicate
#
Restore element: database
#
Target instance state:mount / open
#
controlfile state: current
#
Oracle version: 9i - 10g
#
Media: TSM
#
connect target / AUXILIARY
/ catalog rcma_prd
#
#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+
connect
target SYS/<psw>@PRD_BD
connect
catalog IRC/<psw>@RCAT
connect
AUXILIARY /
# on
auxiliary
startup
clone nomount pfile = '/work/oracle/<new_base>/pfile/init<new_base>.ora'
;
run {
# set until time
"to_date('%%UNTIL_TIME%%', 'Mon DD YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO <NEW_NAME>
skip forever tablespace TBS1_DAT , TBS2_DAT ;
}
Duplicate script to restore an
RMAN Online backup :
# on
auxiliary
startup
clone nomount ;
run {
#
UNTIL Jul 17 04:13
set until time "to_date('Jul 17 2017
04:13:00', 'Mon DD YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO PPR12 skip
tablespace TBS1 , … , TBSN ;
}
1] [11G] Targetless Duplicate
Since
Oracle 11g Release2 there is the option to use the BACKUP LOCATION-clause for
the socalled Targetless Duplication.
See note
874352.1 RMAN 11GR2 : DUPLICATE WITHOUT CONNECTING TO TARGET DATABASE
This is
an Oracle 11g Release 2 feature called 'backup-based duplication' and is only
connecting to the Auxiliary instance, while prior versions required a
connection to the TARGET and optional to the RMAN-catalog.
Clone a Database With RMAN Without Connecting To
Target Database [ID 732624.1]
We have the advantage to easily clone with RMAN without the drawback of the
connection to Target instance.
Moreover, we can set the dump files to a different location than the backup
place.
|
Goal
|
RMAN Targetless Duplicate
<SOURCE_DB> to
<CLONE_DB>
|
|
Version
|
11gR2 11.2.0.3 Linux
|
|
Log as
|
SYS AS SYSDBA
|
|
Until
|
2013 avr 9 01:56
|
1.1 Run backup & copy
dumps to new server
FULL & INCR Backups , here is INCR last execution :
$./rman_backup_db.ksh <SOURCE_DB> DSK FULL ONLINE
[<SOURCE_DB>] Remove old CTL autobackup
[<SOURCE_DB>] Backup RMAN DB FULL mode ONLINE on
dsk
Start - 121126_14:36:51
RMAN script = /work/oracle/<SOURCE_DB>/rman/scr/rman_backup_db_online_full_dsk_noc_<SOURCE_DB>.scr
RMAN log =
/work/oracle/<SOURCE_DB>/rman/log/rman_backup_db_INCR_ONLINE_DSK_noc_<SOURCE_DB>_130409_01:52:34.log
RMAN> 2> 3> 4> 5> 6> 7> 8>
9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19>
20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30>
31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41>
42> 43> 44> 45> 46> 47> 48> 49> 50> 51>
End - 112612_14:55:37
<SOURCE_DB> srv-app-ora-1:/work/oracle/<SOURCE_DB>/rman/log
> ls -ltr /rman/<SOURCE_DB>
total 20579636
-rw-r----- 1 oracle oracle 22888448 mai 19 2012 snapcf_<SOURCE_DB>.f
-rw-r----- 1 oracle oracle 22970368 mar 22 02:03
c-1218103723-20130322-00
-rw-r----- 1 oracle oracle 22970368 mar 23 02:09
c-1218103723-20130323-00
-rw-r----- 1 oracle oracle 22970368 mar 24 21:55
c-1218103723-20130324-00
-rw-r----- 1 oracle oracle 22970368 mar 25 02:01
c-1218103723-20130325-00
-rw-r----- 1 oracle oracle 22970368 mar 26 02:08
c-1218103723-20130326-00
-rw-r----- 1 oracle oracle 22970368 mar 27 02:04
c-1218103723-20130327-00
-rw-r----- 1 oracle oracle 22970368 mar 28 02:03
c-1218103723-20130328-00
-rw-r----- 1 oracle oracle 22970368 mar 29 02:08
c-1218103723-20130329-00
-rw-r----- 1 oracle oracle 22970368 mar 30 02:08
c-1218103723-20130330-00
-rw-r----- 1 oracle oracle 341566976 mar 31 21:41
ar_F_<SOURCE_DB>_s2774_Ummo5ue6f_1_1_20130331
-rw-r----- 1 oracle oracle 419731968 mar 31 21:41
ar_F_<SOURCE_DB>_s2773_Umlo5ue6f_1_1_20130331
-rw-r----- 1 oracle oracle 414293504 mar 31 21:41
ar_F_<SOURCE_DB>_s2771_Umjo5ue6e_1_1_20130331
-rw-r----- 1 oracle oracle 448378368 mar 31 21:41
ar_F_<SOURCE_DB>_s2772_Umko5ue6e_1_1_20130331
-rw-r----- 1 oracle oracle 833429504 mar 31 21:51
db_F_<SOURCE_DB>_s2778_Umqo5ue9k_1_1_20130331
-rw-r----- 1 oracle oracle 1172119552 mar 31 21:53
db_F_<SOURCE_DB>_s2776_Umoo5ue9k_1_1_20130331
-rw-r----- 1 oracle oracle 1864474624 mar 31 21:54
db_F_<SOURCE_DB>_s2775_Umno5ue9k_1_1_20130331
-rw-r----- 1 oracle oracle 2922635264 mar 31 21:57
db_F_<SOURCE_DB>_s2777_Umpo5ue9k_1_1_20130331
-rw-r----- 1 oracle oracle 1280000 mar 31 21:57
ar_F_<SOURCE_DB>_s2779_Umro5uf6k_1_1_20130331
-rw-r----- 1 oracle oracle 22970368 mar 31 21:57
c-1218103723-20130331-00
-rw-r----- 1 oracle oracle 22970368 avr
1 02:04 c-1218103723-20130401-00
-rw-r----- 1 oracle oracle 22970368 avr
2 02:05 c-1218103723-20130402-00
-rw-r----- 1 oracle oracle 22970368 avr
3 02:07 c-1218103723-20130403-00
-rw-r----- 1 oracle oracle 22970368 avr
4 02:12 c-1218103723-20130404-00
-rw-r----- 1 oracle oracle 22970368 avr
5 01:55 c-1218103723-20130405-00
-rw-r----- 1 oracle oracle 22970368 avr
6 01:58 c-1218103723-20130406-00
-rw-r----- 1 oracle oracle 604426240 avr
7 21:42 ar_F_<SOURCE_DB>_s2843_Uoro6gsqf_1_1_20130407
-rw-r----- 1 oracle oracle 688830464 avr
7 21:42 ar_F_<SOURCE_DB>_s2842_Uoqo6gsqe_1_1_20130407
-rw-r----- 1 oracle oracle 708943872 avr
7 21:42 ar_F_<SOURCE_DB>_s2841_Uopo6gsqe_1_1_20130407
-rw-r----- 1 oracle oracle 1321863680 avr 7 21:44
ar_F_<SOURCE_DB>_s2840_Uooo6gsqe_1_1_20130407
-rw-r----- 1 oracle oracle 847339520 avr
7 21:53 db_F_<SOURCE_DB>_s2846_Uouo6gt2b_1_1_20130407
-rw-r----- 1 oracle oracle 1214341120 avr 7 21:55
db_F_<SOURCE_DB>_s2845_Uoto6gt2a_1_1_20130407
-rw-r----- 1 oracle oracle 1884307456 avr 7 21:56
db_F_<SOURCE_DB>_s2844_Uoso6gt2a_1_1_20130407
-rw-r----- 1 oracle oracle 2945507328 avr 7 21:58
db_F_<SOURCE_DB>_s2847_Uovo6gt2b_1_1_20130407
-rw-r----- 1 oracle oracle 1327104 avr
7 21:58 ar_F_<SOURCE_DB>_s2848_Up0o6gtsj_1_1_20130407
-rw-r----- 1 oracle oracle 22970368 avr
7 21:58 c-1218103723-20130407-00
-rw-r----- 1 oracle oracle 26046464 avr
8 01:51 ar_I_<SOURCE_DB>_s2853_Up5o6hbhq_1_1_20130408
-rw-r----- 1 oracle oracle 53195264 avr
8 01:52 ar_I_<SOURCE_DB>_s2852_Up4o6hbhq_1_1_20130408
-rw-r----- 1 oracle oracle 58794496 avr
8 01:52 ar_I_<SOURCE_DB>_s2851_Up3o6hbhq_1_1_20130408
-rw-r----- 1 oracle oracle 62351872 avr
8 01:52 ar_I_<SOURCE_DB>_s2850_Up2o6hbhq_1_1_20130408
-rw-r----- 1 oracle oracle 6651904 avr
8 01:52 db_I_<SOURCE_DB>_s2855_Up7o6hbic_1_1_20130408
-rw-r----- 1 oracle oracle 17678336 avr
8 01:52 db_I_<SOURCE_DB>_s2854_Up6o6hbic_1_1_20130408
-rw-r----- 1 oracle oracle 4571136 avr
8 01:52 db_I_<SOURCE_DB>_s2857_Up9o6hbid_1_1_20130408
-rw-r----- 1 oracle oracle 59129856 avr
8 01:53 db_I_<SOURCE_DB>_s2856_Up8o6hbid_1_1_20130408
-rw-r----- 1 oracle oracle 658432 avr
8 01:53 ar_I_<SOURCE_DB>_s2858_Upao6hbkg_1_1_20130408
-rw-r----- 1 oracle oracle 22970368 avr
8 01:53 c-1218103723-20130408-00
-rw-r----- 1 oracle oracle 284784128 avr
9 01:53 ar_I_<SOURCE_DB>_s2865_Upho6jvvc_1_1_20130409
-rw-r----- 1 oracle oracle 333074432 avr
9 01:53 ar_I_<SOURCE_DB>_s2862_Upeo6jvvc_1_1_20130409
-rw-r----- 1 oracle oracle 362637824 avr
9 01:53 ar_I_<SOURCE_DB>_s2864_Upgo6jvvc_1_1_20130409
-rw-r----- 1 oracle oracle 365964800 avr
9 01:53 ar_I_<SOURCE_DB>_s2863_Upfo6jvvc_1_1_20130409
-rw-r----- 1 oracle oracle 23928832 avr
9 01:54 db_I_<SOURCE_DB>_s2867_Upjo6k01i_1_1_20130409
-rw-r----- 1 oracle oracle 82501632 avr
9 01:55 db_I_<SOURCE_DB>_s2866_Upio6k01i_1_1_20130409
-rw-r----- 1 oracle oracle 28385280 avr
9 01:55 db_I_<SOURCE_DB>_s2869_Uplo6k01j_1_1_20130409
-rw-r----- 1 oracle oracle 164012032 avr
9 01:56 db_I_<SOURCE_DB>_s2868_Upko6k01j_1_1_20130409
-rw-r----- 1 oracle oracle 1230336 avr
9 01:56 ar_I_<SOURCE_DB>_s2870_Upmo6k067_1_1_20130409
-rw-r----- 1 oracle oracle 22888448 avr
9 01:56 snapcf_<SOURCE_DB>f
-rw-r----- 1 oracle oracle 22970368 avr
9 01:56 c-1218103723-20130409-00
[Clone Server] Copy Dump files & BCTracking file if any [
Bug 10185641 : RMAN'S DB_FILE_NAME_CONVERT OF DUPLICATE NOT APPLIED TO CHANGE
TRACKING FILE ]
$ scp srv-app-ora-1:/rman/<SOURCE_DB>/* .
$ mkdir -p /work/oracle/<SOURCE_DB>/rman
$ scp srv-app-ora-1:/work/oracle/<SOURCE_DB>/rman/<SOURCE_DB>_bct.trk
/work/oracle/<SOURCE_DB>/rman
1.2 Prepare Auxiliary
Environment
- [Source Server] Prepare Initialization file for
the duplicate database
SQL> create pfile =
'/work/oracle/<CLONE_DB>/init<CLONE_DB>.ora' from spfile ;
File created.
- [Clone Server] Create Password file and
Directories
<CLONE_DB> db_srv-1:/oracle/11GDB/dbs >
orapwd file=orapw${ORACLE_SID} password=sys_psw
mkdir /work/oracle/<CLONE_DB>/noarc
mkdir /rdo1/<CLONE_DB>/ctl /rdo2/<CLONE_DB>/ctl
mkdir -p /work/oracle/<CLONE_DB>/adump
mkdir -p /work/oracle/<CLONE_DB>/pfile
mkdir -p /work/oracle/<CLONE_DB>/exp
mkdir -p /work/oracle/<CLONE_DB>/dpdump
mkdir -p /work/oracle/<CLONE_DB>/tnsadmin
mkdir -p /work/oracle/<CLONE_DB>/rman
- [Clone
Server] Adapt Init file
If the files are to be restored to a
different location:
db_file_name_convert=('/data/<SOURCE_DB>' ,
'/data/<CLONE_DB>')
log_file_name_convert=('/rdo1/<SOURCE_DB>' ,
'/rdo1/<CLONE_DB>' , /rdo2/<SOURCE_DB>' , '/rdo2/<CLONE_DB>')
<CLONE_DB>
db_srv-1:/work/oracle/<CLONE_DB>/pfile > diff init<CLONE_DB>.ora
init<DB_NAME>.ora
1c1,10
<
*.audit_file_dest='/work/oracle/<CLONE_DB>/adump'
---
> <DB_NAME>.__db_cache_size=163577856
> <DB_NAME>.__java_pool_size=4194304
> <DB_NAME>.__large_pool_size=4194304
> <DB_NAME>.__oracle_base='/oracle/11GDB'#ORACLE_BASE
set from environment
> <DB_NAME>.__pga_aggregate_target=281018368
> <DB_NAME>.__sga_target=524288000
> <DB_NAME>.__shared_io_pool_size=0
> <DB_NAME>.__shared_pool_size=331350016
> <DB_NAME>.__streams_pool_size=8388608
>
*.audit_file_dest='/work/oracle/<db_name>/adump'
4c13
<
*.control_files='/rdo1/<CLONE_DB>/ctl/cntl_1.ctl','/rdo2/<CLONE_DB>/ctl/cntl_2.ctl'
---
>
*.control_files='/rdo1/<db_name>/ctl/cntl_1.ctl','/rdo2/<db_name>/ctl/cntl_2.ctl'
7,8c16,17
<
*.db_name='<CLONE_DB>'
<
*.diagnostic_dest='/work/oracle/<CLONE_DB>'
---
>
*.db_name='<DB_NAME>'
>
*.diagnostic_dest='/work/oracle/<db_name>'
10c19
<
*.dispatchers='(PROTOCOL=TCP) (SERVICE=<CLONE_DB>XDB)'
---
>
*.dispatchers='(PROTOCOL=TCP) (SERVICE=<DB_NAME>XDB)'
12,13c21,22
<
*.log_archive_dest_1='LOCATION=/arch/<CLONE_DB>/'
<
*.log_archive_format='<CLONE_DB>%t_%s_%r.arc'
---
>
*.log_archive_dest_1='LOCATION=/arch/<db_name>/'
>
*.log_archive_format='<DB_NAME>%t_%s_%r.arc'
27,28d35
<
*.db_file_name_convert=('/data/<SOURCE_DB>' ,
'/data/<CLONE_DB>')
<
*.log_file_name_convert=('/rdo1/<SOURCE_DB>' , '/rdo1/<CLONE_DB>' ,
'/rdo2/<SOURCE_DB>' , '/rdo2/<CLONE_DB>')
Complete file
<CLONE_DB>
db_srv-1:/work/oracle/<CLONE_DB>/pfile > cat init<CLONE_DB>.ora
*.audit_file_dest='/work/oracle/<CLONE_DB>/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/rdo1/<CLONE_DB>/ctl/cntl_1.ctl','/rdo2/<CLONE_DB>/ctl/cntl_2.ctl'
*.db_block_size=8192
*.db_domain='corp.fr'
*.db_name='<CLONE_DB>'
*.diagnostic_dest='/work/oracle/<CLONE_DB>'
*.disk_asynch_io=true
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=<CLONE_DB>XDB)'
*.filesystemio_options='SetAll'
*.log_archive_dest_1='LOCATION=/arch/<CLONE_DB>/'
*.log_archive_format='<CLONE_DB>%t_%s_%r.arc'
*.max_shared_servers=8
*.memory_target=805306368
*.open_cursors=1000
*.os_authent_prefix=''
*.processes=300
*.query_rewrite_enabled='TRUE'
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.service_names='TBI'
*.shared_servers=4
*.sql_trace=FALSE
*.undo_tablespace='UNDOTBS1'
*.
db_file_name_convert=('/data/<SOURCE_DB>' , '/data/<CLONE_DB>')
*.log_file_name_convert=('/rdo1/<SOURCE_DB>'
, '/rdo1/<CLONE_DB>' , '/rdo2/<SOURCE_DB>' , '/rdo2/<CLONE_DB>')
SQL> create spfile = '/data/<CLONE_DB>/spfile<CLONE_DB>.ora'
from pfile = '/work/oracle/<CLONE_DB>/pfile/init<CLONE_DB>.ora' ;
- [Clone
Server] start the auxiliary instance
<CLONE_DB> db_srv-1:/oracle/11GDB/dbs > cat
> init<CLONE_DB>.ora
SPFILE='/data/<CLONE_DB>/spfile<CLONE_DB>.ora'
<CLONE_DB> db_srv-1:/home/oracle > sqlplus /
as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 9
14:05:32 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s)
specified for RDBMS instance
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 348129480 bytes
Database Buffers 176160768 bytes
Redo Buffers 7942144 bytes
SQL>
Note
REMOTE_OS_AUTHENT
specifies whether remote clients will be authenticated over insecure
connections, this parameter is now deprecated. It is retained for backward
compatibility only. Stepping up 'out-of-the-box' security Oracle has decided to
deprecate this potentially dangerous parameter in the 11g releases.
1.3 Run Duplicate
To restore a closed backup, no
need to use “set until” in RMAN script.
FULL & INCR Backups are
restored.
Recovery
Manager: Release 11.2.0.3.0 - Production on Tue Apr 9 15:39:12 2013
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to auxiliary database: <CLONE_DB> (not mounted)
RMAN>
run {
2> set
until time "to_date('Apr 09 2013 01:56:00','MON DD YYYY HH24:MI:SS')"
;
3>
DUPLICATE DATABASE TO <CLONE_DB> BACKUP LOCATION '/rman/<db_name>/<SOURCE_DB>'
;
4> }
5>
executing
command: SET until clause
<CLONE_DB>
db_srv-1:/work/oracle/<CLONE_DB>/rman > rman auxiliary /
@scr/duplicate_tipr_to_<CLONE_DB>.scr |tee log/duplicate_tipr_to_<CLONE_DB>_4.log
Recovery
Manager: Release 11.2.0.3.0 - Production on Tue Apr 9 16:17:02 2013
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to auxiliary database: <CLONE_DB> (not mounted)
RMAN>
run {
2>
set until time "to_date('Apr 09 2013 01:56:00','MON DD YYYY
HH24:MI:SS')" ;
3>
DUPLICATE DATABASE TO <CLONE_DB> BACKUP LOCATION '/rman/<db_name>/<SOURCE_DB>'
;
4>
}
5>
executing
command: SET until clause
Starting
Duplicate Db at APR 09 2013 16:17:02
contents
of Memory Script:
{
sql clone "alter system set db_name =
''<SOURCE_DB>'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system set db_unique_name =
''<CLONE_DB>'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/rman/<db_name>/<SOURCE_DB>/c-1218103723-20130408-00';
alter clone database mount;
}
executing
Memory Script
sql
statement: alter system set db_name
= ''<SOURCE_DB>'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql
statement: alter system set
db_unique_name = ''<CLONE_DB>''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle
instance shut down
Oracle
instance started
Total
System Global Area 534462464 bytes
Fixed
Size 2230072 bytes
Variable
Size 352323784 bytes
Database
Buffers 171966464 bytes
Redo
Buffers 7942144 bytes
Starting
restore at APR 09 2013 16:17:11
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=5 device type=DISK
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output
file name=/rdo1/<CLONE_DB>/ctl/cntl_1.ctl
output
file name=/rdo2/<CLONE_DB>/ctl/cntl_2.ctl
Finished
restore at APR 09 2013 16:17:13
database
mounted
released
channel: ORA_AUX_DISK_1
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=5 device type=DISK
allocated
channel: ORA_AUX_DISK_2
channel
ORA_AUX_DISK_2: SID=122 device type=DISK
allocated
channel: ORA_AUX_DISK_3
channel
ORA_AUX_DISK_3: SID=242 device type=DISK
allocated
channel: ORA_AUX_DISK_4
channel
ORA_AUX_DISK_4: SID=361 device type=DISK
contents
of Memory Script:
{
set until scn 436865130;
set newname for datafile 1 to
"/data/<CLONE_DB>/system01.dbf";
set newname for datafile 2 to
"/data/<CLONE_DB>/sysaux01.dbf";
set newname for datafile 3 to
"/data/<CLONE_DB>/undotbs01.dbf";
set newname for datafile 4 to
"/data/<CLONE_DB>/users01.dbf";
set newname for datafile 5 to
"/data/<CLONE_DB>/adb_dat.dbf";
set newname for datafile 6 to
"/data/<CLONE_DB>/commun_dat.dbf";
set
newname for datafile 7 to
"/data/<CLONE_DB>/load_dat.dbf";
set newname for datafile 8 to
"/data/<CLONE_DB>/TBS_LOG_PRX_01.DBF";
set newname for datafile 9 to
"/data/<CLONE_DB>/TBS_HLO_PRX_01.DBF";
set newname for datafile 10 to
"/data/<CLONE_DB>/TBS_LOG_TBS_01.DBF";
set newname for datafile 11 to
"/data/<CLONE_DB>/TBS_DAT_TBS_01.DBF";
set newname for datafile 12 to
"/data/<CLONE_DB>/TBS_DAT_BRI_01.DBF";
set newname for datafile 13 to
"/data/<CLONE_DB>/TBS_IDX_TBS_01.DBF";
set newname for datafile 14 to
"/data/<CLONE_DB>/TBS_IDX_BOB_01.DBF";
set newname for datafile 15 to
"/data/<CLONE_DB>/TBS_DAT_IBO_01.DBF";
set newname for datafile 16 to
"/data/<CLONE_DB>/TBS_IDX_IBO_01.DBF";
set newname for datafile 17 to
"/data/<CLONE_DB>/TBS_IDX_LGT_01.DBF";
set newname for datafile 18 to
"/data/<CLONE_DB>/TBS_DAT_HPV_01.DBF";
set newname for datafile 19 to
"/data/<CLONE_DB>/TBS_DAT_BLB_01.DBF";
set newname for datafile 20 to
"/data/<CLONE_DB>/TBS_IDX_BLB_01.DBF";
set newname for datafile 21 to
"/data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF";
restore
clone database
;
}
executing
Memory Script
executing
command: SET until clause
executing
command: SET NEWNAME
…
executing
command: SET NEWNAME
Starting
restore at APR 09 2013 16:17:23
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: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00001 to /data/<CLONE_DB>/system01.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00005 to /data/<CLONE_DB>/adb_dat.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00006 to /data/<CLONE_DB>/commun_dat.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00017 to /data/<CLONE_DB>/TBS_IDX_LGT_01.DBF
channel
ORA_AUX_DISK_1: restoring datafile 00020 to /data/<CLONE_DB>/TBS_IDX_BLB_01.DBF
channel
ORA_AUX_DISK_1: restoring datafile 00021 to /data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF
channel
ORA_AUX_DISK_1: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2845_Uoto6gt2a_1_1_20130407
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 /data/<CLONE_DB>/sysaux01.dbf
channel
ORA_AUX_DISK_2: restoring datafile 00004 to /data/<CLONE_DB>/users01.dbf
channel
ORA_AUX_DISK_2: restoring datafile 00011 to /data/<CLONE_DB>/TBS_DAT_TBS_01.DBF
channel
ORA_AUX_DISK_2: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2844_Uoso6gt2a_1_1_20130407
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
/data/<CLONE_DB>/undotbs01.dbf
channel
ORA_AUX_DISK_3: restoring datafile 00007 to /data/<CLONE_DB>/load_dat.dbf
channel
ORA_AUX_DISK_3: restoring datafile 00009 to /data/<CLONE_DB>/TBS_HLO_PRX_01.DBF
channel
ORA_AUX_DISK_3: restoring datafile 00013 to /data/<CLONE_DB>/TBS_IDX_TBS_01.DBF
channel
ORA_AUX_DISK_3: restoring datafile 00015 to /data/<CLONE_DB>/TBS_DAT_IBO_01.DBF
channel
ORA_AUX_DISK_3: restoring datafile 00018 to /data/<CLONE_DB>/TBS_DAT_HPV_01.DBF
channel
ORA_AUX_DISK_3: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2846_Uouo6gt2b_1_1_20130407
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 00008 to /data/<CLONE_DB>/TBS_LOG_PRX_01.DBF
channel
ORA_AUX_DISK_4: restoring datafile 00010 to /data/<CLONE_DB>/TBS_LOG_TBS_01.DBF
channel
ORA_AUX_DISK_4: restoring datafile 00012 to /data/<CLONE_DB>/TBS_DAT_BRI_01.DBF
channel
ORA_AUX_DISK_4: restoring datafile 00014 to /data/<CLONE_DB>/TBS_IDX_BOB_01.DBF
channel
ORA_AUX_DISK_4: restoring datafile 00016 to /data/<CLONE_DB>/TBS_IDX_IBO_01.DBF
channel
ORA_AUX_DISK_4: restoring datafile 00019 to /data/<CLONE_DB>/TBS_DAT_BLB_01.DBF
channel
ORA_AUX_DISK_4: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2847_Uovo6gt2b_1_1_20130407
channel
ORA_AUX_DISK_3: piece handle=/rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2846_Uouo6gt2b_1_1_20130407
tag=TAG20130407T214442
channel
ORA_AUX_DISK_3: restored backup piece 1
channel
ORA_AUX_DISK_3: restore complete, elapsed time: 00:06:55
channel
ORA_AUX_DISK_1: piece handle=/rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2845_Uoto6gt2a_1_1_20130407
tag=TAG20130407T214442
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:26
channel
ORA_AUX_DISK_2: piece handle=/rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2844_Uoso6gt2a_1_1_20130407
tag=TAG20130407T214442
channel
ORA_AUX_DISK_2: restored backup piece 1
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:10:26
channel
ORA_AUX_DISK_4: piece handle=/rman/<db_name>/<SOURCE_DB>/db_F_<SOURCE_DB>_s2847_Uovo6gt2b_1_1_20130407
tag=TAG20130407T214442
channel
ORA_AUX_DISK_4: restored backup piece 1
channel
ORA_AUX_DISK_4: restore complete, elapsed time: 00:11:06
Finished
restore at APR 09 2013 16:28:30
contents
of Memory Script:
{
switch clone datafile all;
}
executing
Memory Script
datafile
1 switched to datafile copy
input
datafile copy RECID=24 STAMP=812305710 file
name=/data/<CLONE_DB>/system01.dbf
…
datafile
21 switched to datafile copy
input
datafile copy RECID=44 STAMP=812305711 file name=/data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF
contents
of Memory Script:
{
set until time "to_date('APR 09 2013 01:56:00', 'MON DD
YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing
Memory Script
executing
command: SET until clause
Starting
recover at APR 09 2013 16:28:31
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 incremental datafile backup set restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00003: /data/<CLONE_DB>/undotbs01.dbf
destination
for restore of datafile 00007: /data/<CLONE_DB>/load_dat.dbf
destination
for restore of datafile 00009: /data/<CLONE_DB>/TBS_HLO_PRX_01.DBF
destination
for restore of datafile 00013: /data/<CLONE_DB>/TBS_IDX_TBS_01.DBF
destination
for restore of datafile 00015: /data/<CLONE_DB>/TBS_DAT_IBO_01.DBF
destination
for restore of datafile 00018: /data/<CLONE_DB>/TBS_DAT_HPV_01.DBF
channel
ORA_AUX_DISK_1: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2868_Upko6k01j_1_1_20130409
channel
ORA_AUX_DISK_2: starting incremental datafile backup set restore
channel
ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00002: /data/<CLONE_DB>/sysaux01.dbf
destination
for restore of datafile 00004: /data/<CLONE_DB>/users01.dbf
destination
for restore of datafile 00011: /data/<CLONE_DB>/TBS_DAT_TBS_01.DBF
channel
ORA_AUX_DISK_2: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2866_Upio6k01i_1_1_20130409
channel
ORA_AUX_DISK_3: starting incremental datafile backup set restore
channel
ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00008: /data/<CLONE_DB>/TBS_LOG_PRX_01.DBF
destination
for restore of datafile 00010: /data/<CLONE_DB>/TBS_LOG_TBS_01.DBF
destination
for restore of datafile 00012: /data/<CLONE_DB>/TBS_DAT_BRI_01.DBF
destination
for restore of datafile 00014: /data/<CLONE_DB>/TBS_IDX_BOB_01.DBF
destination
for restore of datafile 00016: /data/<CLONE_DB>/TBS_IDX_IBO_01.DBF
destination
for restore of datafile 00019: /data/<CLONE_DB>/TBS_DAT_BLB_01.DBF
channel
ORA_AUX_DISK_3: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2869_Uplo6k01j_1_1_20130409
channel
ORA_AUX_DISK_4: starting incremental datafile backup set restore
channel
ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00001: /data/<CLONE_DB>/system01.dbf
destination
for restore of datafile 00005: /data/<CLONE_DB>/adb_dat.dbf
destination
for restore of datafile 00006: /data/<CLONE_DB>/commun_dat.dbf
destination
for restore of datafile 00017: /data/<CLONE_DB>/TBS_IDX_LGT_01.DBF
destination
for restore of datafile 00020: /data/<CLONE_DB>/TBS_IDX_BLB_01.DBF
destination
for restore of datafile 00021: /data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF
channel
ORA_AUX_DISK_4: reading from backup piece /rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2867_Upjo6k01i_1_1_20130409
channel
ORA_AUX_DISK_3: piece handle=/rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2869_Uplo6k01j_1_1_20130409
tag=TAG20130409T015353
channel
ORA_AUX_DISK_3: restored backup piece 1
channel
ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:26
channel
ORA_AUX_DISK_4: piece handle=/rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2867_Upjo6k01i_1_1_20130409
tag=TAG20130409T015353
channel
ORA_AUX_DISK_4: restored backup piece 1
channel
ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:26
channel
ORA_AUX_DISK_2: piece handle=/rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2866_Upio6k01i_1_1_20130409
tag=TAG20130409T015353
channel
ORA_AUX_DISK_2: restored backup piece 1
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:56
channel
ORA_AUX_DISK_1: piece handle=/rman/<db_name>/<SOURCE_DB>/db_I_<SOURCE_DB>_s2868_Upko6k01j_1_1_20130409
tag=TAG20130409T015353
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16
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=2772
channel
ORA_AUX_DISK_1: restoring archived log
archived
log thread=1 sequence=2773
channel
ORA_AUX_DISK_1: reading from backup piece /rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2862_Upeo6jvvc_1_1_20130409
channel
ORA_AUX_DISK_2: starting archived log restore to default destination
channel
ORA_AUX_DISK_2: restoring archived log
archived
log thread=1 sequence=2774
channel
ORA_AUX_DISK_2: restoring archived log
archived
log thread=1 sequence=2775
…
channel
ORA_AUX_DISK_2: reading from backup piece /rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2863_Upfo6jvvc_1_1_20130409
channel
ORA_AUX_DISK_3: starting archived log restore to default destination
channel
ORA_AUX_DISK_3: restoring archived log
archived
log thread=1 sequence=2780
…
channel
ORA_AUX_DISK_3: restoring archived log
archived
log thread=1 sequence=2785
channel
ORA_AUX_DISK_3: reading from backup piece /rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2864_Upgo6jvvc_1_1_20130409
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=2786
…
channel
ORA_AUX_DISK_4: reading from backup piece /rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2865_Upho6jvvc_1_1_20130409
channel
ORA_AUX_DISK_4: piece handle=/rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2865_Upho6jvvc_1_1_20130409
tag=I
channel
ORA_AUX_DISK_4: restored backup piece 1
channel
ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:46
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=2791
channel
ORA_AUX_DISK_4: reading from backup piece /rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2870_Upmo6k067_1_1_20130409
channel
ORA_AUX_DISK_4: piece handle=/rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2870_Upmo6k067_1_1_20130409
tag=I
channel
ORA_AUX_DISK_4: restored backup piece 1
channel
ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:01
channel
ORA_AUX_DISK_1: piece handle=/rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2862_Upeo6jvvc_1_1_20130409
tag=I
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:02
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2768_788544330.arc
thread=1 sequence=2768
channel
clone_default: deleting archived log(s)
…
channel
clone_default: deleting archived log(s)
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2773_788544330.arc
RECID=8 STAMP=812305914
channel
ORA_AUX_DISK_2: piece handle=/rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2863_Upfo6jvvc_1_1_20130409
tag=I
channel
ORA_AUX_DISK_2: restored backup piece 1
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:02:14
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2774_788544330.arc
thread=1 sequence=2774
…
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2779_788544330.arc
thread=1 sequence=2779
channel
clone_default: deleting archived log(s)
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2779_788544330.arc
RECID=15 STAMP=812305916
channel
ORA_AUX_DISK_3: piece handle=/rman/<db_name>/<SOURCE_DB>/ar_I_<SOURCE_DB>_s2864_Upgo6jvvc_1_1_20130409
tag=I
channel
ORA_AUX_DISK_3: restored backup piece 1
channel
ORA_AUX_DISK_3: restore complete, elapsed time: 00:02:27
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2780_788544330.arc
thread=1 sequence=2780
channel
clone_default: deleting archived log(s)
…
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2791_788544330.arc
thread=1 sequence=2791
channel
clone_default: deleting archived log(s)
archived
log file name=/arch/<CLONE_DB>/<CLONE_DB>1_2791_788544330.arc
RECID=6 STAMP=812305900
media
recovery complete, elapsed time: 00:00:19
Finished
recover at APR 09 2013 16:32:40
Oracle
instance started
Total
System Global Area 534462464 bytes
Fixed
Size 2230072 bytes
Variable
Size 369101000 bytes
Database
Buffers 155189248 bytes
Redo
Buffers 7942144 bytes
contents
of Memory Script:
{
sql clone "alter system set db_name =
''<CLONE_DB>'' 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
= ''<CLONE_DB>'' 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 534462464 bytes
Fixed
Size 2230072 bytes
Variable
Size 369101000 bytes
Database
Buffers 155189248 bytes
Redo
Buffers 7942144 bytes
sql
statement: CREATE CONTROLFILE REUSE SET DATABASE "<CLONE_DB>"
RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 1024
MAXINSTANCES 4
MAXLOGHISTORY 4382
LOGFILE
GROUP
1 ( '/rdo1/<CLONE_DB>/log_01_1.rdo',
'/rdo2/<CLONE_DB>/log_01_2.rdo' ) SIZE 256 M REUSE,
GROUP
2 ( '/rdo1/<CLONE_DB>/log_02_1.rdo', '/rdo2/<CLONE_DB>/log_02_2.rdo'
) SIZE 256 M REUSE,
GROUP
3 ( '/rdo1/<CLONE_DB>/log_03_1.rdo',
'/rdo2/<CLONE_DB>/log_03_2.rdo' ) SIZE 256 M REUSE
DATAFILE
'/data/<CLONE_DB>/system01.dbf'
CHARACTER SET AL32UTF8
contents
of Memory Script:
{
set newname for tempfile 1 to
"/data/<CLONE_DB>/temp01.dbf";
set newname for tempfile 2 to
"/data/<CLONE_DB>/TBS_TMP_TBS_01.DBF";
switch clone tempfile all;
catalog clone datafilecopy
"/data/<CLONE_DB>/sysaux01.dbf",
"/data/<CLONE_DB>/undotbs01.dbf",
"/data/<CLONE_DB>/users01.dbf",
"/data/<CLONE_DB>/adb_dat.dbf",
"/data/<CLONE_DB>/commun_dat.dbf",
"/data/<CLONE_DB>/load_dat.dbf",
"/data/<CLONE_DB>/TBS_LOG_PRX_01.DBF",
"/data/<CLONE_DB>/TBS_HLO_PRX_01.DBF",
"/data/<CLONE_DB>/TBS_LOG_TBS_01.DBF",
"/data/<CLONE_DB>/TBS_DAT_TBS_01.DBF",
"/data/<CLONE_DB>/TBS_DAT_BRI_01.DBF",
"/data/<CLONE_DB>/TBS_IDX_TBS_01.DBF",
"/data/<CLONE_DB>/TBS_IDX_BOB_01.DBF",
"/data/<CLONE_DB>/TBS_DAT_IBO_01.DBF",
"/data/<CLONE_DB>/TBS_IDX_IBO_01.DBF",
"/data/<CLONE_DB>/TBS_IDX_LGT_01.DBF",
"/data/<CLONE_DB>/TBS_DAT_HPV_01.DBF",
"/data/<CLONE_DB>/TBS_DAT_BLB_01.DBF",
"/data/<CLONE_DB>/TBS_IDX_BLB_01.DBF",
"/data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF";
switch clone datafile all;
}
executing
Memory Script
executing
command: SET NEWNAME
executing
command: SET NEWNAME
renamed
tempfile 1 to /data/<CLONE_DB>/temp01.dbf in control file
renamed
tempfile 2 to /data/<CLONE_DB>/TBS_TMP_TBS_01.DBF in control file
cataloged
datafile copy
datafile
copy file name=/data/<CLONE_DB>/sysaux01.dbf RECID=1 STAMP=812305974
…
cataloged
datafile copy
datafile
copy file name=/data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF RECID=20
STAMP=812305974
datafile
2 switched to datafile copy
input
datafile copy RECID=1 STAMP=812305974 file name=/data/<CLONE_DB>/sysaux01.dbf
…
datafile
21 switched to datafile copy
input
datafile copy RECID=20 STAMP=812305974 file name=/data/<CLONE_DB>/DBATBS_DAT_TBS_01.DBF
Reenabling
controlfile options for auxiliary database
Executing:
alter database enable block change tracking using file
'/work/oracle/<SOURCE_DB>/rman/<SOURCE_DB>_bct.trk'
ORACLE
error from auxiliary database: ORA-19751: could not create the change tracking
file
ORA-19750:
change tracking file:
'/work/oracle/<SOURCE_DB>/rman/<SOURCE_DB>_bct.trk'
ORA-27038:
created file already exists
Additional
information: 1
Ignoring
error, reattempt command after duplicate finishes
contents
of Memory Script:
{
Alter clone database open resetlogs;
}
executing
Memory Script
database
opened
Finished
Duplicate Db at APR 09 2013 16:33:37
Recovery
Manager complete.
