User Managed Recovery Scenarios And Configuration
1. Complete Closed Database Recovery. System tablespace is missing
2. Complete Open Database Recovery. Non system tablespace is missing
3. Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing
4. Recovery of a Missing Datafile that has no backups.
5. Restore and Recovery of a Datafile to a different location.
6. Control File Recovery
7. Incomplete Recovery, Until Time/Sequence/Cancel
RMAN Recovery Scenarios And Configuration
1. Complete Closed Database Recovery. System tablespace is missing
2. Complete Open Database Recovery. Non system tablespace is missing
3. Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing
4. Recovery of a Datafile that has no backups.
5. Restore and Recovery of a Datafile to a different location.
6. Control File Recovery
7. Incomplete Recovery, Until Time/Sequence/Cancel
User Managed Recovery Scenarios
User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up. Files to be copied: select name from v$datafile;
select member from v$logfile; # optional select name from v$controlfile;
Complete Closed Database Recovery. System tablespace is missing
If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed. Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie: cp -p /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;
Complete Open Database Recovery. Non system tablespace is missing
If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open. Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;
Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing
If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open. Pre requisites: A closed or open database backup and archived logs.
1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
3. alter database datafile3 offline; (tablespace cannot be used because the database is not open)
4. alter database open;
5. recover datafile 3;
6. alter tablespace <tablespace_name> online;
Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Pre requisites: All relevant archived logs.
1. alter tablespace <tablespace_name> offline immediate;
2. alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’
Restore and Recovery of a Datafile to a different location.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed. Pre requisites: All relevant archived logs.
1. Use OS commands to restore the missing or corrupted datafile to the new location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf’;
4. recover tablespace <tablespace_name>;
5. alter tablespace <tablespace_name> online;
Control File Recovery
Always multiplex your controlfiles. Controlfiles are missing, database crash. Pre requisites: A backup of your controlfile and all relevant archived logs.
1. startup; (you get ora-205, missing controlfile, instance start but database is not mounted)
2. Use OS commands to restore the missing controlfile to its original location: cp -p /user/backup/uman/control01.dbf /user/oradata/u01/dbtst/control01.dbf cp -p /user/backup/uman/control02.dbf /user/oradata/u01/dbtst/control02.dbf
3. alter database mount;
4. recover automatic database using backup controlfile;
5. alter database open resetlogs;
6. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.
Incomplete Recovery, Until Time/Sequence/Cancel
Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped. Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1. If the database is open, shutdown abort
2. Use OS commands to restore all datafiles to its original locations: cp -p /user/backup/uman/u01/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u02/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u03/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u04/*.dbf /user/oradata/u01/dbtst/ etc…
3. startup mount;
4. recover automatic database until time ‘2004-03-31:14:40:45′;
5. alter database open resetlogs;
6. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.
Alternatively you may use instead of until time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR recover database until cancel;
Rman Recovery Scenarios
Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also. Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database. Configuration and operation recommendations:
Set the parameter controlfile autobackup to ON to have with each backup a controlfile backup also:
configure controlfile autobackup on;
Set the parameter retention policy to the recovery window you want to have, ie redundancy 2 will keep the last two backups
available, after executing delete obsolete commands:
configure retention policy to redundancy 2;
Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:
backup database plus archivelog;
Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete backup;
To work with Rman and a database based catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rcuser identified by rcuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rcuser
5. exit
6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser
7. create catalog # create the catalog
8. connect target / #
Complete Closed Database Recovery. System tablespace is missing
In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without reseting the redologs.
1. rman target /
2. startup mount;
3. restore database;
4. recover database;
5. alter database open;
Complete Open Database Recovery. Non system tablespace is missing, database is up
1. rman target /
2. sql ‘alter tablespace <tablespace_name> offline immediate’;
3. restore datafile 3;
4. recover datafile 3;
5. sql ‘alter tablespace <tablespace_name> online’;
Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing
A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile ‘<datafile_name>’ offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile ‘<datafile_name>’;
9. recover datafile ‘<datafile_name>’;
10. sql ‘alter tablespace <tablespace_name> online’;
Recovery of a Datafile that has no backups (database is up).
If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace
name and put it offline. The option offline immediate is used to avoid that the update of the datafile header. Pre requisites: All relevant archived logs.
1. sqlplus ‘/ as sysdba’
2. alter tablespace <tablespace_name> offline immediate;
3. alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace <tablespace_name>;
7. sql ‘alter tablespace <tablespace_name> online’;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’
Restore and Recovery of a Datafile to a different location. Database is up.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed. Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf’;
4. rman target /
5. recover tablespace <tablespace_name>;
6. sql ‘alter tablespace <tablespace_name> online’;
Control File Recovery
Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash. Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile. It is the number following the ‘c-‘ at the start of the name.
1. rman target /
2. set dbid <dbid#>
3. startup nomount;
4. restore controlfile from autobackup;
5. alter database mount;
6. recover database;
7. alter database open resetlogs;
8. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.
Incomplete Recovery, Until Time/Sequence/Cancel
Incomplete recovery may be necessary when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing. Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it. In this case recovery needs to be performed until before the object was dropped. Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1. If the database is open, shutdown it to perform full restore.
2. rman target
3. startup mount;
4. restore database;
5. recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.
6. alter database open resetlogs;
7. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.
Alternatively you may use instead of until sequence, until time, ie: ‘2004-12-28:01:01:10′.
Up To Time Based Recovery In RMAN
Point in time recovery using RMAN (until a log sequence number)
Recovery Objective
SQL> conn scott/tiger
Connected.
SQL> select count(*) from myobjects;
COUNT(*)
———-
249410
Switch a logfile
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
Note the current log sequence number (13)
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/ORACLE/opsdba/arch
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
Simulate an application failure – WRONG Delete!!
SQL> conn scott/tiger
Connected.
SQL> delete myobjects;
249410 rows deleted.
SQL> commit;
Commit complete.
The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same.
We need to determine the log sequence we need to recover until
select sequence#,first_change#, to_char(first_time,’HH24:MI:SS’) from v$log order by 3
SQL> /
SEQUENCE# FIRST_CHANGE# TO_CHAR(
———- ————- ——–
13 2760463 07:49:36
14 2761178 08:12:47
15 2766622 08:18:49
Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13
Shutdown and mount the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2070416 bytes
Variable Size 163580016 bytes
Database Buffers 92274688 bytes
Redo Buffers 6316032 bytes
Database mounted.
RMAN> run {
2> set until sequence=14; >>> add one to the sequence number we have to recover until
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 29-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=158 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00005 to /u02/ORACLE/opsdba/users02.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf
restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf
restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
restoring datafile 00012 to /u02/ORACLE/opsdba/drtbs2.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551 tag=TAG20070129T074911
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 29-JAN-07
Starting recover at 29-JAN-07
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
starting media recovery
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577 tag=TAG20070129T074937
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u02/ORACLE/opsdba/arch/arch_1_12_613052894.dbf thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf thread=1 sequence=13
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-JAN-07
RMAN> sql ‘alter database open resetlogs’;
sql statement: alter database open resetlogs
Confirm that the recovery has worked
opsdba:/opt/tivoli/tsm/client/oracle/bin64>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.2.0 – Production on Mon Jan 29 09:43:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> select count(*) from myobjects;
COUNT(*)
———-
249410
Duplicate database using RMAN on the same host
Primary Database SID: APPLEPRD
Duplicate Database SID: APPLEDEV
RMAN Catalog SID: N/A
=============================================================
Steps
1. Identify and Backup the primary database.
2. Determine how much disk space will be required.
3. Ensuring you have enough space on your target server.
4. Making the backup available for the duplicate process.
5. Creating the init.ora & administration directories for the duplicate database.
6. Ensuring SQL*NET connections to primary database are working.
7. Prepare RMAN duplicate script.
8. Execute the RMAN script.
9. Duplicate database ready.
- Identify and Backup the primary database
:/opt/oracle>. setAPPLEPRD
APPLEPRD:/opt/oracle> sql
SQL> select dbid,name,created,log_mode,force_logging from v$database;
DBID NAME CREATED LOG_MODE FORCED LOGGING
————– ————— ————— ——————– —————————
237270920 APPLEPRD 24-OCT-06 ARCHIVELOG YES
Note the DBID and the forced logging entries after the duplication the DBID of the new database will be different and forced logging will default to NO.
Check if a backup exists
APPLEPRD:/opt/oracle/admin/APPLEDEV/rman>rman target /
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
connected to target database: APPLEPRD (DBID=237270920)
RMAN> list backup;
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
127 70M SBT_TAPE 00:00:03 05-FEB-07
BP Key: 127 Status: AVAILABLE Tag: TAG20070205T080655
Piece Name: APPLEPRD.20070205.127.1.1.613728415
List of Archived Logs in backup set 127
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 247 15132772 27-JAN-07 15173934 01-FEB-07
1 248 15173934 01-FEB-07 15215150 02-FEB-07
1 249 15215150 02-FEB-07 15256190 02-FEB-07
1 250 15256190 02-FEB-07 15297296 03-FEB-07
1 251 15297296 03-FEB-07 15338448 04-FEB-07
1 252 15338448 04-FEB-07 15379521 04-FEB-07
1 253 15379521 04-FEB-07 15420516 05-FEB-07
1 254 15420516 05-FEB-07 15423007 05-FEB-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
128 Full 537M SBT_TAPE 00:00:42 05-FEB-07
BP Key: 128 Status: AVAILABLE Tag: TAG20070205T080659
Piece Name: APPLEPRD.20070205.128.1.1.613728419
List of Datafiles in backup set 128
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/system01.dbf
2 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/undotbs01.dbf
3 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools01.dbf
4 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users01.dbf
5 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users02.dbf
6 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools02.dbf
7 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/example01.dbf
8 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools03.dbf
9 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users03.dbf
10 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users04.dbf
11 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools04.dbf
12 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/drtest1.dbf
13 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/drtest3.dbf
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
129 7K SBT_TAPE 00:00:02 05-FEB-07
BP Key: 129 Status: AVAILABLE Tag: TAG20070205T080744
Piece Name: APPLEPRD.20070205.129.1.1.613728464
List of Archived Logs in backup set 129
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 255 15423007 05-FEB-07 15423042 05-FEB-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
130 Full 3M SBT_TAPE 00:00:00 05-FEB-07
BP Key: 130 Status: AVAILABLE Tag:
Piece Name: c-237270920-20070205-00
SPFILE Included: Modification time: 27-JAN-07
As per above there exists a backup and it is available, however if no backup exists you may fire the backup as follows:
APPLEPRD:/opt/oracle/admin/APPLEDEV/rman>rman target /
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
connected to target database: APPLEPRD (DBID=237270920)
RMAN> backup device type sbt database plus archivelog;
Starting backup at 05-FEB-07
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=11 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
skipping archive log file /u01/ORACLE/APPLEPRD/arch/arch190.log; already backed up 1 time(s)
skipping archive log file /u01/ORACLE/APPLEPRD/arch/arch191.log; already backed up 1 time(s)
skipping archive log file /u01/ORACLE/APPLEPRD/arch/arch240.log; already backed up 1 time(s)
channel ORA_SBT_TAPE_1: starting archive log backupset
channel ORA_SBT_TAPE_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=241 recid=270 stamp=612980669
channel ORA_SBT_TAPE_1: starting piece 1 at 27-JAN-07
channel ORA_SBT_TAPE_1: finished piece 1 at 27-JAN-07
piece handle=APPLEPRD.20070127.113.1.1.612980669 comment=API Version 2.0,MMS Version 5.2.4.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-FEB-07
………………………………………………………………. ………………………
………………………………………………………………………………………..
Starting Control File and SPFILE Autobackup at 05-FEB-07
piece handle=c-237270920-20070127-02 comment=API Version 2.0,MMS Version 5.2.4.0
Finished Control File and SPFILE Autobackup at 05-FEB-07
RMAN>exit
- 2. Determine how much disk space will be required.
After deciding what you will be duplicating, calculate the entire space this will required on the host.
SQL> select round(sum(bytes)/1048576) ||’ M – DATA’ as DATA
from dba_data_files;
select round(sum(bytes)/1048576) ||’ M – TEMP’ as TEMP
from dba_temp_files;
select round(sum(bytes)/1048576) ||’ M – LOGS’ as LOGS
from v$log;
1230 M – DATA
100 M – TEMP
30 M – LOGS
3. Ensuring you have enough space on your target server.
Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database.
APPLEPRD:/opt/oracle>df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p8 4.9G 1.4G 3.2G 31% /
/dev/cciss/c0d0p2 25G 19G 4.4G 81% /opt
/dev/cciss/c0d0p11 14G 12G 2.1G 85% /u01
- Making the backup available for the duplicate process.
Verify the backup is available.
APPLEPRD:/opt/oracle/admin/APPLEDEV/rman>rman target /
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
connected to target database: APPLEPRD (DBID=237270920)
RMAN> list backup;
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
127 70M SBT_TAPE 00:00:03 05-FEB-07
BP Key: 127 Status: AVAILABLE Tag: TAG20070205T080655
Piece Name: APPLEPRD.20070205.127.1.1.613728415
List of Archived Logs in backup set 127
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 247 15132772 27-JAN-07 15173934 01-FEB-07
1 248 15173934 01-FEB-07 15215150 02-FEB-07
1 249 15215150 02-FEB-07 15256190 02-FEB-07
1 250 15256190 02-FEB-07 15297296 03-FEB-07
1 251 15297296 03-FEB-07 15338448 04-FEB-07
1 252 15338448 04-FEB-07 15379521 04-FEB-07
1 253 15379521 04-FEB-07 15420516 05-FEB-07
1 254 15420516 05-FEB-07 15423007 05-FEB-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
128 Full 537M SBT_TAPE 00:00:42 05-FEB-07
BP Key: 128 Status: AVAILABLE Tag: TAG20070205T080659
Piece Name: APPLEPRD.20070205.128.1.1.613728419
List of Datafiles in backup set 128
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/system01.dbf
2 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/undotbs01.dbf
3 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools01.dbf
4 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users01.dbf
5 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users02.dbf
6 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools02.dbf
7 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/example01.dbf
8 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools03.dbf
9 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users03.dbf
10 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/users04.dbf
11 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/tools04.dbf
12 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/drtest1.dbf
13 Full 15423009 05-FEB-07 /u01/ORACLE/APPLEPRD/drtest3.dbf
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
129 7K SBT_TAPE 00:00:02 05-FEB-07
BP Key: 129 Status: AVAILABLE Tag: TAG20070205T080744
Piece Name: APPLEPRD.20070205.129.1.1.613728464
List of Archived Logs in backup set 129
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 255 15423007 05-FEB-07 15423042 05-FEB-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
130 Full 3M SBT_TAPE 00:00:00 05-FEB-07
BP Key: 130 Status: AVAILABLE Tag:
Piece Name: c-237270920-20070205-00
SPFILE Included: Modification time: 27-JAN-07
5 . Creating the init.ora & administration directories for the duplicate database.
Create the directory structure of the dump destinations and of the datafiles etc.
Also create set file.
An easy way to create the dump directories is to copy the existing primary database dump directories and delete its contents
/opt/oracle/admin/> cp –rp APPLEPRD APPLEDEV
Find the filesystem directories of the database.
SQL> select distinct substr(name,1,instr(UPPER(name),’ORACLE’,1) – 1) “FILESYSTEM”
from v$datafile
UNION
select distinct substr(member,1,instr(UPPER(member),’ORACLE’,1) – 1) “FILESYSTEM”
from v$logfile
UNION
select distinct substr(name,1,instr(UPPER(name),’ORACLE’,1) – 1) “FILESYSTEM”
from v$controlfile;
FILESYSTEM
——————————————————————————–
/u01/
SQL> exit
/u01/ORACLE> mkdir APPLEDEV
Copy the original initora file of the primary database in this case initAPPLEPRD.ora and replace all APPLEPRD references to APPLEDEV and make all necessary directory changes.
*.background_dump_dest=’/opt/oracle/admin/APPLEDEV/bdump’
*.control_files=’/u01/ORACLE/APPLEDEV/control01.ctl’
*.core_dump_dest=’/opt/oracle/admin/APPLEDEV/cdump’
*.db_block_size=16384
*.db_cache_size=104857600
*.db_file_multiblock_read_count=8
*.db_file_name_convert=’/u01/ORACLE/APPLEPRD/’,’/u01/ORACLE/APPLEDEV/’
*.db_name=’APPLEDEV’
*.java_pool_size=52428800
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=’LOCATION=/u01/ORACLE/APPLEDEV/arch’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_format=’arch%s.log’
*.log_archive_start=TRUE
*.log_buffer=1048576
*.log_checkpoints_to_alert=TRUE
*.log_file_name_convert=’/u01/ORACLE/APPLEPRD/’,’/u01/ORACLE/APPLEDEV/’
*.shared_pool_size=104857600
Ensure the db_file_name_convert parameter is set appropiately. This parameter will instruct RMAN to convert the primary database filenames to the target database filenames. One can use the SET NEWNAME parameter too in the RMAN script then db_file_name_convert parameter is not needed.
Create password file for new db
:/opt/oracle>. setAPPLEDEV
APPLEDEV:/opt/oracle> cd $ORACLE_HOME/dbs
APPLEDEV:/opt/oracle/product9204/dbs>orapwd file=orapwAPPLEDEV password=oracle entries=5
APPLEDEV:/opt/oracle/product9204/dbs>
6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.
Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: You must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.
APPLEDEV:/opt/oracle/admin/APPLEDEV/rman>sqlplus ‘sys/[email protected] as sysdba’
APPLEDEV:/opt/oracle/admin/APPLEDEV/rman> sqlplus rman/[email protected] (not mandatory)
7. Prepare RMAN duplicate script.
Using RMAN connect to the primary database to retrieve the SBT Channel settings. This setting has to be included in the RMAN Duplicate script.
APPLEPRD:/opt/oracle/admin/APPLEDEV/rman>rman target /
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
connected to target database: APPLEPRD (DBID=237270920)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE ‘SBT_TAPE’ TO ‘%F’;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/ORACLE/APPLEPRD/arch/%F’;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/t
sm/client/oracle/bin64/tdpo.APPLEPRDd.opt)’ FORMAT ‘%d.%T.%s.%p.%c.%t’;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/oracle/product9204/dbs/snapcf_face.f’; # default
RMAN> exit
Note the current log sequence of the primary database APPLEPRD
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/ORACLE/fAPPLEPRD/arch
Oldest online log sequence 254
Next log sequence to archive 256
Current log sequence 256
Create a script create_APPLEDEV.rcv
Note the log sequence number and the channel parameters.
run{
allocate channel C1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.APPLEPRDd.opt)’;
allocate auxiliary channel aux1 device type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.APPLEPRDd.opt)’;
SET UNTIL SEQUENCE 256 THREAD 1;
duplicate target database to APPLEDEV;
}
Start the APPLEDEV instance in NOMOUNT mode. This will be your auxiliary instance.
:/opt/oracle> . setAPPLEDEV
APPLEDEV:/opt/oracle> sql
SQL*Plus: Release 9.2.0.4.0 – Production on Tue Feb 6 09:55:42 2007
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 287279592 bytes
Fixed Size 731624 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 1335296 bytes
SQL> exit
8. Execute the RMAN script.
Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown above. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
APPLEDEV:/opt/oracle/admin/APPLEDEV/rman>rman target sys/[email protected] APPLEPRD as sysdba auxiliary /
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: APPLEPRD (DBID=237270920)
connected to auxiliary database: APPLEDEV (not mounted)
RMAN> spool log to APPLEDEV_creation.log
RMAN> @ create_APPLEDEV.rcv
RMAN> 1 > 2 > 3 > 4 >
Open another duplicate OS session and navigate to the directory that has the log file APPLEDEV_creation.log and do a tail –f APPLEDEV_creation.log to monitor the progress of the duplication.
APPLEDEV:/opt/oracle/admin/APPLEDEV/rman> tail –f APPLEDEV_creation.log
Spooling started in log file: APPLEDEV_creation.log
Recovery Manager: Release 9.2.0.4.0 – 64bit Production
RMAN>
RMAN> run{
2> allocate channel C1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.APPLEPRDd.opt)’;
3> allocate auxiliary channel aux1 device type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.APPLEPRDd.opt)’;
4> SET UNTIL SEQUENCE 256 THREAD 1;
5> duplicate target database to APPLEDEV;
6> }
using target database controlfile instead of recovery catalog
allocated channel: C1
channel C1: sid=15 devtype=SBT_TAPE
channel C1: Data Protection for Oracle: version 5.2.4.0
allocated channel: aux1
channel aux1: sid=12 devtype=SBT_TAPE
channel aux1: Data Protection for Oracle: version 5.2.4.0
executing command: SET until clause
Starting Duplicate Db at 05-FEB-07
printing stored script: Memory Script
{
set until scn 15423042;
set newname for datafile 1 to
“/u01/ORACLE/APPLEDEV/system01.dbf”;
set newname for datafile 2 to
“/u01/ORACLE/APPLEDEV/undotbs01.dbf”;
set newname for datafile 3 to
“/u01/ORACLE/APPLEDEV/tools01.dbf”;
set newname for datafile 4 to
“/u01/ORACLE/APPLEDEV/users01.dbf”;
set newname for datafile 5 to
“/u01/ORACLE/APPLEDEV/users02.dbf”;
set newname for datafile 6 to
“/u01/ORACLE/APPLEDEV/tools02.dbf”;
set newname for datafile 7 to
“/u01/ORACLE/APPLEDEV/example01.dbf”;
set newname for datafile 8 to
“/u01/ORACLE/APPLEDEV/tools03.dbf”;
set newname for datafile 9 to
“/u01/ORACLE/APPLEDEV/users03.dbf”;
set newname for datafile 10 to
“/u01/ORACLE/APPLEDEV/users04.dbf”;
set newname for datafile 11 to
“/u01/ORACLE/APPLEDEV/tools04.dbf”;
set newname for datafile 12 to
“/u01/ORACLE/APPLEDEV/drtest1.dbf”;
set newname for datafile 13 to
“/u01/ORACLE/APPLEDEV/drtest3.dbf”;
restore
check readonly
clone database
;
}
executing script: Memory Script
executing command: SET until claus
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 05-FEB-07
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/ORACLE/APPLEDEV/system01.dbf
restoring datafile 00002 to /u01/ORACLE/APPLEDEV/undotbs01.dbf
restoring datafile 00003 to /u01/ORACLE/APPLEDEV/tools01.dbf
restoring datafile 00004 to /u01/ORACLE/APPLEDEV/users01.dbf
restoring datafile 00005 to /u01/ORACLE/APPLEDEV/users02.dbf
restoring datafile 00006 to /u01/ORACLE/APPLEDEV/tools02.dbf
restoring datafile 00007 to /u01/ORACLE/APPLEDEV/example01.dbf
restoring datafile 00008 to /u01/ORACLE/APPLEDEV/tools03.dbf
restoring datafile 00009 to /u01/ORACLE/APPLEDEV/users03.dbf
restoring datafile 00010 to /u01/ORACLE/APPLEDEV/users04.dbf
restoring datafile 00011 to /u01/ORACLE/APPLEDEV/tools04.dbf
restoring datafile 00012 to /u01/ORACLE/APPLEDEV/drtest1.dbf
restoring datafile 00013 to /u01/ORACLE/APPLEDEV/drtest3.dbf
channel aux1: restored backup piece 1
piece handle=APPLEPRD.20070205.128.1.1.613728419 tag=TAG20070205T080659 params=NULL
channel aux1: restore complete
Finished restore at 05-FEB-07
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “APPLEDEV” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( ‘/u01/ORACLE/APPLEDEV/redo01.log’ ) SIZE 10485760 REUSE,
GROUP 2 ( ‘/u01/ORACLE/APPLEDEV/redo02.log’ ) SIZE 10485760 REUSE,
GROUP 3 ( ‘/u01/ORACLE/APPLEDEV/redo03.log’ ) SIZE 10485760 REUSE
DATAFILE
‘/u01/ORACLE/APPLEDEV/system01.dbf’
CHARACTER SET US7ASCII
printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/tools01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/users01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/users02.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/tools02.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/example01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/tools03.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/users03.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/users04.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/tools04.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/drtest1.dbf
datafile 13 switched to datafile copy
input datafilecopy recid=12 stamp=613746385 filename=/u01/ORACLE/APPLEDEV/drtest3.dbf
printing stored script: Memory Script
{
set until scn 15423042;
recover
clone database
delete archivelog
;
}
executing script: Memory Script
executing command: SET until clause
Starting recover at 05-FEB-07
starting media recovery
archive log thread 1 sequence 255 is already on disk as file /u01/ORACLE/APPLEPRD/arch/arch255.log
archive log filename=/u01/ORACLE/APPLEPRD/arch/arch255.log thread=1 sequence=255
media recovery complete
Finished recover at 05-FEB-07
printing stored script: Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script: Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 354387512 bytes
Fixed Size 742968 bytes
Variable Size 234881024 bytes
Database Buffers 117440512 bytes
Redo Buffers 1323008 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “APPLEDEV” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( ‘/u01/ORACLE/APPLEDEV/redo01.log’ ) SIZE 10485760 REUSE,
GROUP 2 ( ‘/u01/ORACLE/APPLEDEV/redo02.log’ ) SIZE 10485760 REUSE,
GROUP 3 ( ‘/u01/ORACLE/APPLEDEV/redo03.log’ ) SIZE 10485760 REUSE
DATAFILE
‘/u01/ORACLE/APPLEDEV/system01.dbf’
CHARACTER SET US7ASCII
printing stored script: Memory Script
{
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/undotbs01.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/tools01.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/users01.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/users02.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/tools02.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/example01.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/tools03.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/users03.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/users04.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/tools04.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/drtest1.dbf”;
catalog clone datafilecopy “/u01/ORACLE/APPLEDEV/drtest3.dbf”;
switch clone datafile all;
}
executing script: Memory Script
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/undotbs01.dbf recid=1 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/tools01.dbf recid=2 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/users01.dbf recid=3 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/users02.dbf recid=4 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/tools02.dbf recid=5 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/example01.dbf recid=6 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/tools03.dbf recid=7 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/users03.dbf recid=8 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/users04.dbf recid=9 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/tools04.dbf recid=10 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/drtest1.dbf recid=11 stamp=613746391
cataloged datafile copy
datafile copy filename=/u01/ORACLE/APPLEDEV/drtest3.dbf recid=12 stamp=613746391
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/tools01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/users01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/users02.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/tools02.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/example01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/tools03.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/users03.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/users04.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/tools04.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/drtest1.dbf
datafile 13 switched to datafile copy
input datafilecopy recid=12 stamp=613746391 filename=/u01/ORACLE/APPLEDEV/drtest3.dbf
printing stored script: Memory Script
{
Alter clone database open resetlogs;
}
executing script: Memory Script
database opened
Finished Duplicate Db at 05-FEB-07
released channel: C1
RMAN> **end-of-file**
- 9. Duplicate database ready.
NOTE: The DBID is changed and FORCED LOGGING is set to NO. and now there are two pmon processes one for each database.
SQL> select dbid,name,created,log_mode,force_logging from v$database;
DBID NAME CREATED LOG_MODE FORCED LOGGING
—————- ——— ————— —————— —————————
338960471 APPLEDEV 05-FEB-07 ARCHIVELOG NO
SQL> exit
APPLEDEV:/opt/oracle>ps -ef | grep pmon
oracle 22385 1 0 10:39 ? 00:00:00 ora_pmon_APPLEPRD
oracle 24085 1 0 10:41 ? 00:00:00 ora_pmon_APPLEDEV
oracle 26510 17342 0 10:42 pts/2 00:00:00 grep pmon
APPLEDEV:/opt/oracle>
After registering this database in the listener. You may do a TNS promote.
Controlfile Recover
SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG)
SQL> insert into myobjects select * from myobjects;
919664 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from myobjects;
COUNT(*)
———-
1839328 >>>> need to check this record count after recovery
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
Note – current log sequence is 7 – not archived but contains the last committed changes that we made
Note – archive logs will not be found in $ARCV area, but in the flashback location
Simulate a failure
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/u01/ORACLE/testdb/control01.ctl
/u01/ORACLE/testdb/control02.ctl
/u01/ORACLE/testdb/control03.ctl
SQL> !rm /u01/ORACLE/testdb/*.ctl
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 893386752 bytes
Fixed Size 2076816 bytes
Variable Size 432017264 bytes
Database Buffers 452984832 bytes
Redo Buffers 6307840 bytes
Since we are not using a RMAN catalog we need to set the DBID
RMAN> set dbid=2415549446;
executing command: SET DBID
Restore the controlfile
RMAN> run {
2> restore controlfile from autobackup;
3> }
Starting restore at 10-JAN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/ORACLE/flash_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2010-01-10/o1_mf_s_633601094_3gynd74g_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/ORACLE/testdb/control01.ctl
output filename=/u01/ORACLE/testdb/control02.ctl
output filename=/u01/ORACLE/testdb/control03.ctl
Finished restore at 10-JAN-10
Mount and recover the database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 10-JAN-10
Starting implicit crosscheck backup at 10-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 10-JAN-10
Starting implicit crosscheck copy at 10-JAN-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-JAN-10
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2010-01-10/o1_mf_s_633601094_3gynd74g_.bkp
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
datafile 4 not processed because file is offline
starting media recovery
archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2010-01-10/o1_mf_1_6_3gyn7vnk_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log
archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2010-01-10/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6
archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7 >>>> current redo log with committed but unarchived changes applied
media recovery complete, elapsed time: 00:00:09
Finished recover at 10-JAN-10
SQL> alter database open resetlogs;
Database altered.
conn scott/tiger
Connected.
SQL> select count(*) from myobjects;
COUNT(*)
———-
1839328
RMAN Enhancements
RMAN Enhancements
In this section, we will discuss the new features of Recovery Manager (RMAN) in Oracle 10g.
Automated Channel Failover for Backup and Restore
In Oracle 10g, when multiple channels are allocated for a backup and restore operation, and one of the channels fails during the operation, RMAN continues the job on the remaining channels. RMAN does not restart the backup or restore process for the failed channel. Such a problem is typical when the media manager encounters problems with one tape drive. RMAN reports a message in v$rman_output and in the output to the terminal or log file when it encounters such problems.
Automated File Creation During Recovery
This feature enhances RMAN recovery by automatically creating and recovering datafiles that have never been backed up.
In order to recover a data file that has never been backed up, you need the archive log files from the time of the data file creation until the time you wish to stop the recovery process, and a copy of the control file with the information regarding the data file.
Figure 12.5 Automated File Creation During Recovery
Simplified Backups to Disk
In previous releases of Oracle, RMAN had two separate commands to backup data files: BACKUP and COPY. The BACKUP command backed up the data file only to backup set, which is a proprietary format recognized by RMAN only. You must use RMAN to restore a data file from a backup set. The COPY command generated image copies, which are bit-by-bit copies of data files. You do not need RMAN to restore a database from an image copy.
The BACKUP DATABASE command can backup a whole database to backup sets without specifying each individual data file. However, there is no corresponding COPY DATABASE command. Therefore, you must run the REPORT SCHEMA command to determine the file names of the data files, and then you need to specify each data files in your COPY command.
RMAN> copy current controlfile to ‘dba/backup/grid/ctlfile.cpy’,
datafile 1 to ‘dba/backup/grid/df1.cp’y,
datafile 2 to ‘dba/backup/grid/df2.cp’y,
datafile 3 to ‘dba/backup/grid/df3.cp’y,
datafile 4 to ‘dba/backup/grid/df4.cp’y,
datafile 5 to ‘dba/backup/grid/df5.cp’y,
datafile 6 to ‘dba/backup/grid/df6.cp’y;
In Oracle 10g, the COPY command is abandoned in favor of an enhanced BACKUP command that enables you to specify where RMAN should create copies or backup sets. You can use the new BACKUP AS COPY command to copy an entire database or multiple tablespaces, data files, and archived logs.
Here is an example to backup an entire database as an image copy to the recovery area.
RMAN> backup as copy
tag “weekly_backup” database;
Proxy Copy Backup of Archivelogs
The proxy functionality was first introduced in Oracle8i Release 1 (8.1.5). A proxy copy is a special type of backup, in which RMAN turns over control of the data transfer to a media manager that supports this feature. The PROXY option of the BACKUP command specifies that a backup should be a proxy copy.
For each file that you attempt to back up using the BACKUP PROXY command, RMAN queries the media manager to determine whether it can perform a proxy copy. If the media manager cannot proxy copy the file, then RMAN uses conventional backup sets to perform the backup. An exception occurs when you use the PROXY ONLY option, which causes Oracle to issue an error message when it is unable to proxy copy.
Prior to Oracle 10g, you could RMAN to perform proxy backups of data files and data file copies. However, you could not perform proxy backup of archive log files or control file copies. You can now use RMAN to perform proxy backups of archive log files. You can use the rc_proxy_archivelog dictionary view to determine the proxy backups recorded in the catalog.
RMAN> backup device type sbt proxy only
2> archivelog from logseq 35 thread 1;
Incrementally Updated Backups
In Oracle 10g, you can apply incremental backups to data file image copy backups to roll them forward to a specified point in time. This new feature provides the following benefits:
- By periodically updating image copies of data files with incremental backups, the updated image copy of the data file is moved forward to a more recent state (with more current SCN). This could potentially result in reduced recovery time.
- Avoids performing a full image copy after incremental backups.
Figure 12.6 Incrementally Updated Backups
Example
The following statement rolls forward an image copy of data file /dba/backup/grid/data01.imgcopy:
RMAN> recover datafilecopy ‘/dba/backup/grid/data15.imgcopy’;
Or, you can issue this command to perform the same operation:
RMAN> RECOVERY COPY OF DATAFILE 15;
Simplified Recovery Through Resetlogs
After you perform an incomplete (Point-in-time) recovery, you need to open the database with the RESETLOGS option:
SQL> alter database open resetlogs;
The RESETLOGS operation creates a new incarnation of the database and resets the logs. Prior to Oracle 10g, the newly generated redo log files could not be used with the backups taken in the past. Therefore, it was important to take an immediate backup, since all previous backups became invalid. In addition, if you used the RMAN catalog for future backups, you needed to issue the following command to make the RMAN catalog aware of the new incarnation of the database.
RMAN> reset database;
In Oracle 10g, you no longer have to back up your database following an incomplete recovery and OPEN RESETLOGS operations.
This new feature is also applicable for the following two scenarios:
- Performing a recovery using a backup control file and opening the database with the RESETLOGS operation.
- Reinstantiation of the old primary database following a failover (see the Flashback Reinstantiation section for details).
Benefits of Simplified Recovery Through Resetlogs
The Simplified Recovery Through Resetlogs feature provides the following benefits:
- There is no need to perform a full backup after an incomplete recovery.
- There is no need to recreate a new standby database after a failover operation.
- There is no need to change any backup scripts, as there are no changes in the recovery commands to take advantage of this functionality.
- You can take incremental backups based on full backups of a previous incarnation when you use RMAN.
- Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.
How does it work?
You may wonder how can you use the newly generated logs with an earlier incarnation of the database. Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations.
SQL> show parameter log_archive_format
NAME TYPE VALUE
—————————- ———– —————-
log_archive_format string %t_%s_%r.dbf
The format specification is %r and represents the resetlogs id. It is included in the default format for the log_archive_format initialization parameter. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and SQL*plus auto recovery mode.
During the RESETLOGS operation, the information in v$log_history and v$offline_range records are no longer cleared. In addition, two new columns have been added to indicate the incarnation the records belong to: resetlogs_change# and resetlogs_time.
Example:
SQL> select recid, thread#, sequence#, resetlogs_change#,resetlogs_time
2 from v$log_history
3 where rownum < 20;
RECID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME
———- ———- ———- —————– ——————–
1 1 1 1 Aug 12 2003 18:48:54
2 1 2 1 Aug 12 2003 18:48:54
3 1 3 1 Aug 12 2003 18:48:54
4 1 4 1 Aug 12 2003 18:48:54
5 1 5 1 Aug 12 2003 18:48:54
6 1 6 1 Aug 12 2003 18:48:54
7 1 7 1 Aug 12 2003 18:48:54
8 1 8 1 Aug 12 2003 18:48:54
9 1 9 1 Aug 12 2003 18:48:54
10 1 10 1 Aug 12 2003 18:48:54
11 1 11 1 Aug 12 2003 18:48:54
12 1 12 1 Aug 12 2003 18:48:54
13 1 13 1 Aug 12 2003 18:48:54
14 1 14 1 Aug 12 2003 18:48:54
15 1 15 1 Aug 12 2003 18:48:54
16 1 16 1 Aug 12 2003 18:48:54
17 1 17 1 Aug 12 2003 18:48:54
18 1 18 1 Aug 12 2003 18:48:54
19 1 19 1 Aug 12 2003 18:48:54
19 rows selected.
Full Database Begin Backup Command
Oracle 10g allows all of the data files in the database to be placed in online backup mode using a single command:
SQL> ALTER DATABASE BEGIN BACKUP;
You no longer need to place each tablespace in online backup mode individually:
SQL> ALTER TABLESPACE user BEGIN BACKUP;
SQL> ALTER TABLESPACE example BEGIN BACKUP;
The ALTER DATABASE BEGIN BACKUP command places all data files in the database in online backup mode. The database must be in mounted or open mode when you issue this command.
Example 1: Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace users begin backup;
alter tablespace users begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace ‘USERS’
SQL> alter database begin backup;
Database altered.
Example 2: Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode
SQL> alter tablespace example offline;
Tablespace altered.
SQL> alter tablespace example begin backup;
alter tablespace example begin backup
*
ERROR at line 1:
ORA-01128: cannot start online backup – file 5 is offline
ORA-01110: data file 5: ‘C:ORACLEORADATAGRIDEXAMPLE01.DBF’
SQL> alter database begin backup;
Database altered.
The two examples above demonstrate that when you issue the ALTER DATABASE BEGIN BACKP command, any read-only and offline data files are simply skipped, and processing continues.
Changes to the ALTER DATABASE END BACKUP command
You can run the ALTER DATABASE END BACKUP command when you have multiple tablespaces still in backup mode. You can issue the statement to take all data files currently in backup mode out of backup mode. However, you can use this statement only when the database is mounted, and not open in Oracle9i. If the database is open, you can only use ALTER TABLESPACE … END BACKUP or ALTER DATABASE DATAFILE … END BACKUP for each affected tablespace or datafile.
In Oracle 10g, you can issue the ALTER DATABASE END BACKUP command when the database is open.
If you issue the command while one of the data files is offline or in read-only mode, a warning message will return:
SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode
Change-Aware Incremental Backups
In previous releases of the Oracle database, RMAN had to examine every block in the data file to determine which blocks had been changed when performing an incremental backup. The time to perform an incremental backup was proportional to the size of the data files. Therefore, performing an incremental backup on a very large database could take some time, even if only a few blocks were changed.
In Oracle 10g, you can create a block change tracking file that records the blocks modified since the last backup. RMAN uses the tracking file to determine which blocks to include in the incremental backup. RMAN no longer needs to examine the entire data file. The time to perform an incremental backup is now proportional to the amount of content modified since the last backup.
Here are the steps RMAN will perform to do an incremental backup:
- Read the Block Change Tracking File to determine which blocks in the data file need to be read.
- Scan only the changed blocks (See Figure 12.1) in the data file and then back them up.
Figure 12.7 Incremental Backup using Block Change Tracking File
How big is the Block Change Tracking File?
The size of the block change tracking file is proportional to:
- The database size in bytes: the block change tracking file contains data representing every data file block in the database. The data is approximately 1/250000 of the total size of the database.
- The number of enabled threads: In a Real Application Cluster (RAC) environment, the instances update different areas of the tracking file without any locking or inter-node block swapping. You enable block change tracking for the entire database and not for individual instances.
- The number of old backups: The block change tracking file keeps a record of all changes between previous backups, in addition to the modification since the last backup. It retains the change history for a maximum of eight backups.
The size of the file is calculated by the following formula:
Size of the Block Change Tracking File =
( (Threads*2) + number of old backups ) * database size in bytes
250,000
The minimum size for the block change tracking file is 10 MB. Using this formula, a 2 TB database with only one thread and having five backups in the RMAN repository will require a block change tracking file of 59 MB.
Enabling, Disabling and Monitoring Block Change Tracking
By default, Oracle will not record block change information. To enable this feature, you need to issue the following command:
SQL> alter database enable block change tracking;
To disable this feature, you issue this command:
SQL> alter database disable block change tracking;
To monitor the status of block change tracking, you type:
SQL> select file, status, bytes
2 from v$block_change_tracking;
STATUS FILE BYTES
———– —————————- —————
ENABLED /dba/backup/01_mf_yzmrr7.chg 10,000,000
Automated Disk-Based Backup and Recovery
Prior to Oracle 10g, disk files that were created by the RMAN utility or ARCH process had no knowledge of one another. Furthermore, they were not aware of the sizes of the file system on which they created files. Database administrators needed to routinely clean up the old archive logs or old RMAN files.
It is better to have a unified disk storage location where you can manage all recovery-related files. Now you can achieve this in Oracle 10g by specifying a Recovery Area.
Recovery Area
The recovery area is a unified disk storage location for all recovery-related files and activities in an Oracle Database. Those files include:
- Control file
- Online redo log files
- Archived log files
- Flashback logs
- Control file autobackups
- Data file copies
- RMAN files
Setting up a Recovery Area
The recovery area is defined by setting two initialization parameters. These two parameters can be dynamically altered or disabled.
- The db_recovery_file_dest_size sets the disk limit, expressed in bytes
- The db_recovery_file_dest sets the location for the recovery area
Enabling a Recovery Area
This statement sets the disk limit for recovery area to 100 GB:
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 100G
This statement sets the recovery area destination:
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST = ‘/dba/backup/’;
Alter a Recovery Area
This statement alters the size of the recovery area:
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 200G;
Disabling a Recovery Area
This statement disables a recovery area:
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST = ‘ ’;
Recovery Area Space Management
When the recovery area is less than 90% full, Oracle does not delete eligible files immediately, to minimize the need to restore recent files from tape during recovery. The recovery area can thus serve as a kind of cache for tape.
Once the recovery area is 90% full, Oracle will issue a warning to users. The Oracle database server and RMAN will continue to create files in the recovery area until 100% of the disk limit is reached.
Once the recovery area is 100% full, the RMAN retention policy is used to indicate what files will be deleted in order to make space for newer files.
The db_flashback_retention_target parameter specifies how long Oracle will keep the flashback logs in the flashback recovery area. However, flashback logs won’t be deleted, even if they exceed the duration specified by the flashback retention period, unless more space is needed in the recovery area for other files.
New RMAN command for Recovery Area
RMAN> BACKUP RECOVERY AREA;
RMAN> BACKUP RECOVERY FILES;
New Recovery Area Dictionary View
Oracle 10g has a new dynamic performance view for monitoring the recovery area:
SQL> desc v$recovery_file_dest
Name Null? Type
—————————————- ——– ———–
NAME VARCHAR2(513)
SPACE_LIMIT NUMBER
SPACE_USED NUMBER
SPACE_RECLAIMABLE NUMBER
NUMBER_OF_FILES NUMBER
Column Description:
- NAME: Recovery area name, indicating location string.
- SPACE_LIMIT: used space by recovery area files in bytes
- SPACE_RECLAIMABILE: amount of space that can be created by deleting obsolete, redundant, and other low priority files through the space management algorithm.
- NUMBER_OF_FILE: number of files
Example:
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———— ————- ———— —————– —————-
/dba/backup 2147483648 458219520 0 49
RMAN Database Dropping and Deregistration
Oracle 10g allows a database to be dropped and its entry removed from the RMAN catalog.
The following statement drops the entire database and removes the database files:
RMAN> drop database;
The statement below drops the entire database, removes the database files, and deletes all backup copies of the database and the archive log files:
RMAN> drop database including backups;
The two statements above drop the database and delete the database files. However, they do not unregister the database from the RMAN catalog. The following statement will remove the database information from the RMAN catalog:
RMAN> unregister database grid;
Automated TSPITR Instantiation
In previous Oracle releases, you could use RMAN to perform tablespace point in time recovery (TSPITR). However, you needed to create an auxiliary instance manually.
In Oracle 10g, when you perform tablespace point in time recovery, RMAN creates an auxiliary instance automatically on the same target database server. RMAN removes the auxiliary instance after you complete the tablespace recovery.
RMAN creates the data files required by the auxiliary instance as specified by set newname, configure auxname, and db_file_name_convert. RMAN removes these files when the recovery tasks are completed.
Simplified Recovery Manager Cataloging of Backup Files
Every time RMAN backs up a database into backup sets and backup pieces, it updates the RMAN catalog to reflect the backup information. If a user removes, relocates, or renames a backup piece, you cannot make corresponding changes to the RMAN catalog in the earlier versions of the oracle database.
However, in Oracle 10g, you can use the CATALOG command to enter the new filename or location for backup pieces in the RMAN catalog. In addition, you can use the CATALOG command to enter user-managed data file copies. The UNCATALOG command is used to remove the backup pieces from the catalog.
Example 1: To catalog a backup piece
RMAN> catalog backuppiece
2> ‘/dba/backup/rman/data01.bkp’;
Example 2: To catalog a user-managed data file copy
RMAN> catalog backup
2> ‘/dba/backup/grid/data01.dbf’;
Example 3: To uncatalog a backup piece
RMAN> change backupiece
2> ‘/dba/backup/grid/data01.bkp’ uncatalog;
RMAN and the new EM (Enterprise Manager)
The new Enterprise Manger in Oracle Database 10g makes the backup a lot easier to work with the new features.
