Duplicate from active database – ASM Primary to ASM Standby

In my earlier post 11g Standby Database Creation Without Any RMAN Backups I had illustrated how to create a standby database using the 11g Active Duplication feature which enabled us to create a standby database without any backup taken of the primary database. In this case it was a ASM Primary and a non ASM Standby database.

This example now illustrates how the same procedure can be extended to create an ASM Standby database from an ASM Primary database using the same active duplication method.

After following all the steps explained in the previous example, run the following command after launching RMAN and connecting to the auxiliary instance.

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
PARAMETER_VALUE_CONVERT
‘/u01/oracle/admin/test_fc’,'/u01/oracle/admin/test_js’
SET DB_UNIQUE_NAME=”test_js”
SET LOG_ARCHIVE_DEST_2=”service=test_fc LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”test_fc”
SET FAL_CLIENT=”test_js”
SET CONTROL_FILES=’+DATA/TEST_JS/controlfile/control01.dbf’
;

What I found is that if you do not include the full path name for the CONTROL_FILES parameter and only include the ‘+DATA’ part as shown below we will get a number of different errors – one of them is shown below. In another case I got an error related to RMAN-06152 which is an AUTOBACKUP related error.

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
PARAMETER_VALUE_CONVERT
‘/u01/oracle/admin/test_fc’,'/u01/oracle/admin/test_js’
SET DB_UNIQUE_NAME=”test_js”
SET LOG_ARCHIVE_DEST_2=”service=test_fc LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”test_fc”
SET FAL_CLIENT=”test_js”
SET CONTROL_FILES=’+DATA’
;

Starting backup at 08-12-2009 10:47:38
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.1/db_1/dbs/snapcf_test.f tag=TAG20091208T104738 RECID=14 STAMP=705062859
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-12-2009 10:47:42

sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/08/2009 10:47:45
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 12/08/2009 10:47:45
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-00205: error in identifying control file, check alert log for more info

11g Release 2 RMAN Backup Compression

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.

To use this option, we can run the following RMAN commands

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed by ..

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;

Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.

Compression Level ‘HIGH’

backupset size: 226.18M
time: 00:02:21

Compression Level ‘Medium’

backupset size: 293.80M
time: 00:00:30

Compression Level ‘Low’

backupset size: 352.59M
time: 00:00:20

Compression Level ‘Basic’

backupset size: 276.55M
time: 00:00:50

To summarise we can conclude:

LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

11g RMAN Virtual Private Catalog

In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog.

This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to only that user’s RMAN metadata. We can in this way create a number of multiple recovery catalog users each seeing only having access to a limited set of databases while the base recovery catalog owner has access to the entire metadata.

For example, in the RMAN catalog owned by user RMAN11D, there a a number of databases registered, but we would like to restrict access to the APEX database to a single user – RMAN_APEX.

So we need to first create a user in the database which houses the base RMAN catalog, grant that user the RECOVERY_CATALOG_OWNER role and then the ‘catalog for database …..’ privilege.

That user will then create a virtual catalog and when he connects to that catalog, we will see that he can only access the one database which he has been granted access for which is the APEX database.

The original RMAN catalog owner is RMAN11D – note the databases which are currently registered:

SQL> select name from rc_database;

NAME
--------
SID1D
SID1S
SID1A
APEX
SID1T

Create the Virtual Catalog User – RMAN_APEX

SQL> create user rman_apex identified by rman_apex
  2   default tablespace RMAN11D
  3   temporary tablespace temp
  4  quota unlimited on rman11d;

User created.

SQL>  grant recovery_catalog_owner to rman_apex;

Grant succeeded.

Connect to catalog as catalog owner and grant permissions on the one database – APEX

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:34:33 2009

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

connected to target database: EMREP (DBID=3892233981)
connected to recovery catalog database

RMAN> grant catalog for database apex to rman_apex;

Grant succeeded.

Connect now as the user RMAN_APEX and create the Virtual Private Catalog

[PROD] emrep:/u01/oracle > rman target / catalog rman_apex/rman_apex@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:39:56 2009

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

connected to target database: EMREP (DBID=3892233981)
connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN11D
created virtual catalog against base catalog owned by RMAN11D

If we connect as the original RMAN catalog owner we can see all the registered databases

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
72209   72226   APEX     1312143933       PARENT  1          07-NOV-07
72209   72210   APEX     1312143933       CURRENT 15653492933 04-AUG-09
17258   17259   SID1T    2951173163       CURRENT 1          08-OCT-09
761     762     SID1S    3097605603       CURRENT 1          08-SEP-09
2139    2140    SID1A    3639578917       CURRENT 1          16-SEP-09
1       2       SID1D    3743031640       CURRENT 1          27-AUG-09

Note that only one database is registered in this catalog when we connect as RMAN_APEX

rman target / catalog rman_apex/rman_apex@rcatd

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
72209   72226   APEX     1312143933       PARENT  1          07-NOV-07
72209   72210   APEX     1312143933       CURRENT 15653492933 04-AUG-09

11g Release 2 Tablespace Point In Time Recovery – recover from dropped tablespace

One of the good new features in 11g Release 2 is that it enables us to recover from a case of a dropped tablespace. TSPITR (tablespace point in time recovery) has been around in earlier releases, but we could not recover a dropped tablespace.

What 11gR2 does is that it peforms a fully automated RMAN managed TSPITR. It creates and starts the auxiliary instance and restores just the datafiles it requires - SYSTEM,SYSAUX,UNDO and the files pertaining to the dropped tablespace – in this case datafiles 1,2,3 and 7- in the location which we specify as the ‘Auxiliary Destination’. It will first perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database.

To illustrate this example we create a new tablespace MYEXAMPLE and create two tables MYSALES and MYCOSTS in this tablespace. We take a database backup and then drop the tablespace and then perform tablespace point in time recovery using RMAN.

Before dropping the tablespace we first note the SCN as we will use this SCN when we do the TSPITR. Note also that we are connected to a RMAN catalog as well although the documentation does state that we can perform TSPITR without a recovery catalog.

SQL> create tablespace myexample datafile '+DATA' size 200m;

Tablespace created.

SQL> create table mysales
  2  tablespace myexample
  3  as select * from sales;

Table created.

SQL> create table mycosts
  2  tablespace myexample
  3   as select * from costs;

Table created.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1043322

SQL> drop tablespace myexample including contents and datafiles;

Tablespace dropped.

RMAN> run {
2> set newname for datafile 7 to '+DATA';
3> recover tablespace myexample
4> until scn 1043322
5> auxiliary destination '+DATA';
6> }

executing command: SET NEWNAME

Starting recover at 27-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

Creating automatic instance, with SID='qyBo'

initialization parameters used for automatic instance:
db_name=TESTDB
db_unique_name=qyBo_tspitr_TESTDB
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=+DATA
log_archive_dest_1='location=+DATA'
#No auxiliary parameter file used

starting up automatic instance TESTDB

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2212736 bytes
Variable Size                100666496 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4849664 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 27-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA/testdb/backupset/2009_11_27/ncsnf0_tag20091127t104307_0.502.
704025815
channel ORA_AUX_DISK_1: piece handle=+DATA/testdb/backupset/2009_11_27/ncsnf0_tag20091127t104307_0.502.
704025815 tag=TAG20091127T104307
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/testdb/controlfile/current.506.704033345
Finished restore at 27-NOV-09

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  7 to
 "+DATA";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 7;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

Starting restore at 27-NOV-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +DATA/testdb/backupset/2009_11_27/nnndf0_tag20091127t104307 .....
channel ORA_AUX_DISK_1: piece handle=+DATA/testdb/backupset/2009_11_27/nnndf0_tag20091127t104307_0.501.
704025789 tag=TAG200 .........
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 27-NOV-09

datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=704033380 file name=+DATA/testdb/datafile/myexample.512.
704033355
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=704033380 file name=+DATA/testdb/datafile/system.511.
704033355
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=704033380 file name=+DATA/testdb/datafile/undotbs1.513.
704033355
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=704033380 file name=+DATA/testdb/datafile/sysaux.509.
704033355

contents of Memory Script:
{
# set requested point in time
set until  scn 1043322;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "MYEXAMPLE", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  7 online

Starting recover at 27-NOV-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file +DATA/testdb/archivelog/2009_11_27/.....
archived log for thread 1 with sequence 11 is already on disk as file +DATA/testdb/archivelog/2009_11_27/.....
archived log for thread 1 with sequence 12 is already on disk as file +DATA/testdb/archivelog/2009_11_27/ ......
archived log file name=+DATA/testdb/archivelog/2009_11_27/ ....
archived log file name=+DATA/testdb/archivelog/2009_11_27/....
archived log file name=+DATA/testdb/archivelog/2009_11_27/....
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-09

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  MYEXAMPLE read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+DATA''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+DATA''";
}
executing Memory Script

sql statement: alter tablespace  MYEXAMPLE read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_qyBo":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_qyBo" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_qyBo is:
   EXPDP>   +DATA/tspitr_qybo_53647.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace MYEXAMPLE:
   EXPDP>   +DATA/testdb/datafile/myexample.512.704033355
   EXPDP> Job "SYS"."TSPITR_EXP_qyBo" successfully completed at 12:51:34
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_qyBo" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_qyBo":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_qyBo" successfully completed at 12:51:49
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  MYEXAMPLE read write';
sql 'alter tablespace  MYEXAMPLE offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  MYEXAMPLE read write

sql statement: alter tablespace  MYEXAMPLE offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file +DATA/testdb/tempfile/temp.518.704033385 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_3.517.704033385 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_2.515.704033383 deleted
auxiliary instance file +DATA/testdb/onlinelog/group_1.514.704033383 deleted
auxiliary instance file +DATA/testdb/datafile/sysaux.509.704033355 deleted
auxiliary instance file +DATA/testdb/datafile/undotbs1.513.704033355 deleted
auxiliary instance file +DATA/testdb/datafile/system.511.704033355 deleted
auxiliary instance file +DATA/testdb/controlfile/current.506.704033345 deleted
Finished recover at 27-NOV-09

RMAN>

Note: After the recovery is complete, we need to bring the tablespace and datafile online as well.

SQL> alter tablespace myexample online;

Tablespace altered.

SQL> alter database datafile '+DATA/testdb/datafile/myexample.512.704033355' online;

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='MYEXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/testdb/datafile/myexample.512.704033355

SQL> select table_name from dba_tables
  2  where tablespace_name='MYEXAMPLE';

TABLE_NAME
------------------------------
MYSALES
MYCOSTS

SQL> select count(*) from sh.mysales;

  COUNT(*)
----------
    918843

RMAN Restore Validate Examples

We can use the RMAN RESTORE VALIDATE command to check and verify the integrity of the backups which are stored on tapes or disk as well.

A point to keep in mind is that the RESTORE DATABASE VALIDATE command will only check for the datafile backups and will not check for either the archivelog of controlfile backups. We need to issue additional RESTORE ARCHIVELOG VALIDATE as well as RESTORE CONTROLFILE VALIDATE commands.

We can also issue the RESTORE SPFILE VALIDATE command to check the backup of the server parameter file. By default, RMAN only checks for physical corruption while validating. We can also instruct RMAN to check for logical corruption via the VALIDATE CHECK LOGICAL command.

The RESTORE DATABASE VALIDATE command will check for the last level 0 or FULL tape or disk based backup, but the RESTORE ARCHIVELOG ALL command will check for all the archivelog files catalogued based on the retention policy.

So when we are validating the restore of archivelog files, it may be better to check for the existence or accessibility in the tape library or disk of all the archivelog files which have been generated since the last full or level 0 backup.

For example if we are taking daily backups, we would like to check for all archivelog file backups for the past day using the command as shown below:

RMAN> restore archivelog from time ’sysdate-1′ validate;

Some other examples of the VALIDATE command are:

RMAN> restore database validate;
RMAN> restore controlfile validate;
RMAN> restore spfile validate;
RMAN> restore tablespace users validate;
RMAN> validate backupset 922;
RMAN> validate recovery area;

An example is shown below where we are checking the validity of a database backup for a database which has a weekly level 0 backup. Note that the RESTORE DATABASE VALIDATE command will cause RMAN to check for the last level 0 backup which has been performed in this case on the 15th of November.

RMAN> restore database validate;

Starting restore at 20-NOV-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting validation of datafile backup set
channel ORA_SBT_TAPE_1: reading from backup piece vnkud0k5_1_1
channel ORA_SBT_TAPE_1: piece handle=vnkud0k5_1_1 tag=TAG20091115T060908
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:14:15
Finished restore at 20-NOV-09

RMAN> list backup of database tag TAG20091115T060908;

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3054 Incr 0 15.59G SBT_TAPE 00:39:00 15-NOV-09
BP Key: 3054 Status: AVAILABLE Compressed: NO Tag: TAG20091115T060908
Handle: vnkud0k5_1_1 Media:
List of Datafiles in backup set 3054
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/system01.dbf
2 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/undotbs01.dbf
3 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/sysaux01.dbf
4 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/users01.dbf
5 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt.dbf
6 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt_ecm_depot1.dbf
7 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/patrol01.dbf

Let us simulate the case where an archivelog is not present on disk and hence the validate will fail as shown below.

[PROD] emrep:/u05/oradata/emrep/arch > mv arch18053.1.669202302.log arch18053.1.669202302.log.old
[PROD] emrep:/u05/oradata/emrep/arch > rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Fri Nov 20 12:23:31 2009

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

connected to target database: EMREP (DBID=3892233981)

RMAN> restore archivelog from time ’sysdate-1′ validate;

Starting restore at 20-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=261 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=221 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/20/2009 12:23:37
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 18053 and starting SCN of 23679034351 found to restore

11g RMAN Substitution Variables

We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command.

Also note that in 11g we can run RMAN scripts using the ‘@’ like a SQL script

testbkp.rcv

connect target sys/oracle
run{
backup incremental level &1
database
tag &2;
}
exit;

testbkp.sh

#! /bin/ksh
export level=$1
export tag=$2_`date +%d%b%y`
rman @testbkp.rcv using $level $tag

Note the actual RMAN command that is run …….. it adds a date along with the tag name (parameter 2) and runs a level 2 backup as the first parameter was ‘2′

apex:/u01/oracle> ./testbkp.sh 2 dly_inc_bkp

Recovery Manager: Release 11.1.0.6.0 – Production on Tue Sep 22 11:28:05 2009

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

RMAN> connect target *
2> run{
3> backup incremental level 2
4> database
5> tag dly_inc_bkp_22Sep09;
6> }
7> exit;
connected to target database: APEX (DBID=1312143933)

Starting backup at 22-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=349 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
channel ORA_SBT_TAPE_1: starting incremental level 2 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/apex/undotbs01.dbf
input datafile file number=00002 name=/u02/oradata/apex/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/apex/system01.dbf
input datafile file number=00005 name=/u02/oradata/apex/monitor_data01.dbf
input datafile file number=00007 name=/u02/oradata/apex/encrypt_data01.dbf
input datafile file number=00008 name=/u02/oradata/apex/disc10_data01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 22-SEP-09
channel ORA_SBT_TAPE_1: finished piece 1 at 22-SEP-09
piece handle=a5kpsim9_1_1 tag=DLY_INC_BKP_22SEP09 comment=API Version 2.0,MMS Version 5.5.1.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-SEP-09

Starting Control File and SPFILE Autobackup at 22-SEP-09
piece handle=c-1312143933-20090922-05 comment=API Version 2.0,MMS Version 5.5.1.0
Finished Control File and SPFILE Autobackup at 22-SEP-09

Recovery Manager complete.

Duplicating a database from an ASM to a Non-ASM file system

By default, when we are using ASM with a Flash Recovery Area configured, backups to disk will go to the “backupset” subdirectory – unless we use the FORMAT clause while taking the backup to point the backups to another location on disk.

When we try to duplicate a database which is hosted on an ASM storage to a conventional O/S file system, the restore will fail as it will look for the ASM diskgroups where the backup was taken on the target node as well.

We will see an error like …..

channel ORA_AUX_DISK_1: reading from backup piece +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
ORA-19505: failed to identify file “+DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727″
ORA-17503: ksfdopn:2 Failed to open file +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
ORA-15001: diskgroup “DBATEAM” does not exist or is not mounte
failover to previous backup

In this case we will need to use the BACKUPSET clause of the BACKUP command to relocate that backup from the ASM storage to a O/S file system. We then ftp this backupset to the target node ensuring that the same directory file structure is available on the target node as compared to the source node where the ASM disk backup was restored. We can then proceed as normal with the RMAN Duplicate database procedure.

RMAN> list backup of database;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    1.40G      DISK        00:00:47     26-AUG-09
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20090826T102022
        Piece Name: +DBATEAM/bwdba/backupset/2009_08_26/nnndf0_tag20090826t102022_0.397.695902837
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/system.256.695305997
  2       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/sysaux.257.695305999
  3       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/undotbs1.258.695305999
  4       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/users.259.695305999
  5       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/example.267.695306155
  6       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/undotbs2.268.695306391

RMAN> backup device type disk format '/u01/oracle/backup/bwdba_bkp.%u'
2> backupset 14
3> ;

Starting backup at 26-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 instance=bwdba1 device type=DISK
input backup set count=15 STAMP=695902823 creation_time=26-AUG-09
channel ORA_DISK_1: starting piece 1 at 26-AUG-09
channel ORA_DISK_1: backup piece +DBATEAM/bwdba/backupset/2009_08_26/nnndf0_tag20090826t102022_0.397.695902837
piece handle=/u01/oracle/backup/bwdba_bkp.0fknl8j7 comment=NONE
channel ORA_DISK_1: finished piece 1 at 26-AUG-09
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 26-AUG-09

We will see now that the backup location has changed

RMAN> list backup of database;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    1.40G      DISK        00:00:47     26-AUG-09
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20090826T102022
        Piece Name: /u01/oracle/backup/bwdba_bkp.0fknl8j7
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/system.256.695305997
  2       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/sysaux.257.695305999
  3       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/undotbs1.258.695305999
  4       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/users.259.695305999
  5       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/example.267.695306155
  6       Full 2765425    26-AUG-09 +DBATEAM/bwdba/datafile/undotbs2.268.695306391

Using FLASHBACK to rollback a TRUNCATE

This scenario will show how we can use a combination of FLASHBACK database and also recovery to take a database back in time to undo a TRUNCATE operation and then roll forward the database after the flashback operation to bring it to the current point in time.

INSERT ROWS INTO TABLE MYOBJ – THIS WILL BE TRUNCATED

SQL> insert into scott.myobj select * from all_objects;

50496 rows created.

SQL> /

50496 rows created.

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
    100992

OBTAIN THE CURRENT SCN – FLASHBACK WILL HAPPEN TO THIS SCN

SQL> select current_scn from v$database;

          CURRENT_SCN
---------------------
          15633908021

TRUNCATE THE TABLE

SQL> truncate table scott.myobj;

Table truncated.

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
         0

AT THE SAME TIME OTHER CHANGES ARE HAPPENING IN THE DATABASE AND THESE CHANGES WILL BE RECOVERED AFTER THE FLASHBACK IS DONE

SQL> insert into scott.myobj2 select * from scott.myobj2;

356874 rows created.

SQL> /

713748 rows created.

SQL> commit;

Commit complete.

SHUTDOWN THE DATABASE AND PERFORM THE FLASHBACK TO THE SCN BEFORE THE TRUNCATE WAS DONE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  2084880 bytes
Variable Size             377491440 bytes
Database Buffers           83886080 bytes
Redo Buffers                6299648 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO SCN 15633908021;

Flashback complete.

OPEN THE DATABASE IN READ ONLY MODE AND EXPORT THE TABLE THAT WAS TRUNCATED EARLIER. THIS TABLE WILL BE IMPORTED AFTER THE RECOVERY IS DONE

SQL> alter database open read only;

Database altered.

SQL>  select count(*) from scott.myobj;

  COUNT(*)
----------
         100992

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
crashdb:/u03/oradata/crashdb/arch> exp file=scott.dmp tables=myobj

Export: Release 10.2.0.4.0 - Production on Fri Feb 6 09:53:00 2009

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

Username: scott
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          MYOBJ          100992 rows exported
Export terminated successfully without warnings.

NOW SHUTDOWN THE DATABASE,STARTUP MOUNT AND PERFORM THE RECOVERY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  696254464 bytes
Fixed Size                  2086616 bytes
Variable Size             184551720 bytes
Database Buffers          503316480 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

AS EXPECTED TABLE MYOBJ WHICH WAS TRUNCATED NOW AGAIN HAS 0 ROWS AFTER THE RECOVERY – WE CAN IMPORT THE DUMP WE TOOK AFTER THE FLASHBACK

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
         0

CONFIRM THAT OTHER COMMITTED CHANGES IN THE DATABASE HAVE BEEN RECOVERED

SQL> select count(*) from scott.myobj2;

  COUNT(*)
----------
  713748

READ ONLY Tablespace Restore and Recovery

Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments.

Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets.

But we need to keep in mind that we need to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups.

However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail.

Let us illustrate the same with an example where we have made the USERS tablespace read only and then simulated a media failure by deleting the three datafiles of the USERS tablespace at the OS level.

We will first attempt a normal restore and recovery and see how that fails. We follow that by using the CHECK READONLY keywords and see how the restore and recovery succeeds.

 restore database;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:10:36
Finished restore at 12-AUG-09

RMAN> recover database;

Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
.....
.......

archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:43
Finished recover at 12-AUG-09

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/12/2009 12:55:30
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/testdb/users03.dbf'

Now, the read only tableaspaces are restored as well, but we notice the recovery stage will skip these tablespaces since no recovery is required as no changes have happened on these tablespaces since the last backup of these tablespaces after they were made read only.

RMAN> restore database check readonly;

Starting restore at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/testdb/users03.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
restoring datafile 00006 to /u02/oradata/testdb/users02.dbf
restoring datafile 00009 to /u02/oradata/testdb/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 08kmb8f7_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=08kmb8f7_1_1 tag=TAG20090810T120039
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:05:25
Finished restore at 12-AUG-09

RMAN> recover database;
Starting recover at 12-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 6 not processed because file is read-only
datafile 9 not processed because file is read-only
channel ORA_SBT_TAPE_1: starting incremental datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/testdb/system01.dbf
destination for restore of datafile 00002: /u02/oradata/testdb/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/testdb/sysaux01.dbf
destination for restore of datafile 00005: /u02/oradata/testdb/example01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0akmb8uu_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0akmb8uu_1_1 tag=TAG20090810T120901
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 36 is already on disk as file /u02/oradata/testdb/arch/arch.36.1.694170424.log
archive log thread 1 sequence 37 is already on disk as file /u02/oradata/testdb/arch/arch.37.1.694170424.log
archive log thread 1 sequence 38 is already on disk as file /u02/oradata/testdb/arch/arch.38.1.694170424.log
archive log thread 1 sequence 39 is already on disk as file /u02/oradata/testdb/arch/arch.39.1.694170424.log
archive log thread 1 sequence 40 is already on disk as file /u02/oradata/testdb/arch/arch.40.1.694170424.log
archive log filename=/u02/oradata/testdb/arch/arch.36.1.694170424.log thread=1 sequence=36
archive log filename=/u02/oradata/testdb/arch/arch.37.1.694170424.log thread=1 sequence=37
archive log filename=/u02/oradata/testdb/arch/arch.38.1.694170424.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:39
Finished recover at 12-AUG-09

RMAN> alter database open;

database opened

RMAN TSPITR Limitations

While the RMAN Tablespace Point In Time Recovery (TSPITR) automates the process of creating an auxiliary instance and exporting and importing objects from the auxiliary instance to the target database, it still has many limitations and we encountered bugs which apparently have been only fixed in 11g.

Not only do we need to allocate temporary disk space for the SYSTEM, UNDO and the tablespace that we want to be recovered, it cannot be used to recover from a case of a dropped tablespace. Further, the tablespace needs to be self contained and cannot contain any objects owned by SYS or any undo or rollback segments. In case of partitioned tables spread over a number of tablespaces, all the referenced tablespaces will need to be recovered together.

While running the following command to do a TSPITR, we encountered the errors shown below

run {
recover tablespace test_tspitr
until logseq 7 thread 1
auxiliary destination ‘/u02/oradata/stage/’;
}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/04/2009 11:28:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found – aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 2
RMAN-06100: no channel to restore a backup or copy of datafile 1

Metalink Note 391785.1 indicates that this error is because the automatically configured Auxiliary SBT channel is released during the TSPITR process and not reused later on in the process. This is also seen in the log and the workaround suggested is to explicitly allocate an auxiliary channel.

released channel: ORA_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

if we change the command to allocate the channel as shown below, we are getting another error that it is not able to connect to an auxiliary instance. This means we need to startup and connect to an auxiliary instance first before running the TSPITR command. Not very good.

run {
allocate auxiliary channel c1 device type ‘SBT_TAPE’ ;
recover tablespace test_tspitr
until logseq 7 thread 1
auxiliary destination ‘/u02/oradata/stage/’;
}

RMAN-03002: failure of allocate command at 08/04/2009 12:08:48
RMAN-06174: not connected to auxiliary database

This has been documented in Bug 4768353 which is apparently fixed in 11g. The issue is not faced if backups are available on disk and default device type is disk.

RMAN Recovering a Dropped Tablespace

To recover from a case of a dropped tablespace, the Tablespace Point In Time Recovery (TSPITR) method cannot be used. When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the RMAN error RMAN-06019 – “could not translate tablespace name” as shown below.

SQL> drop tablespace rman10 including contents and datafiles;

Tablespace dropped.

testdb:/u01/oracle/diag/rdbms/apex/apex/trace> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 3 11:53:58 2009

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

connected to target database: TESTDB (DBID=2469552796)

RMAN> restore tablespace rman10;

Starting restore at 03-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=141 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/03/2009 11:54:11
RMAN-20202: tablespace not found in the recovery catalog

So to recover from a dropped tablespace, we have two options:

1) Do a point in time recovery of the whole database until the time the tablespace was dropped.
2) Create a clone of the database from a valid backup, export the required tables from the tablespace which has been dropped, recreate the tablespace and then import the tables from the clone.

The first option will require an outage of the entire database and the entire database will be rolled back in tine in order to recover the tablespace. The second option can be peformed online, but we will need to factor in the disk space requirements to create a clone of the database from which the tablespace has been dropped.

Let us examine the first option using the example shown below:

In this example, CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for the database.

With Flashback enabled, the db_recovery_file_dest will have a sub-directory ‘autobackup’ as shown below for each day

ttestdb:/u02/oradata/testdb/TESTDB/autobackup/2009_08_03> ls -lrt
total 63040
-rw-r-----    1 oracle   dba         6455296 Aug  3 10:22 o1_mf_s_693915680_57dlgcqh_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 11:49 o1_mf_s_693920955_57dqkw0j_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 13:28 o1_mf_s_693926889_57dxcbdx_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 14:18 o1_mf_s_693928526_57f094n9_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 14:20 o1_mf_s_693930026_57f0fbo2_.bkp

When we drop the tablespace we are changing the structure of the database and since controlfile autobackup has been turned on, we see another backup file has been created in the autobackup location in the flash recovery area on disk.

SQL> drop tablespace arul including contents and datafiles;

Tablespace dropped.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
testdb:/u02/oradata/testdb/TESTDB/autobackup/2009_08_03> ls -lrt
total 75648
-rw-r-----    1 oracle   dba         6455296 Aug  3 10:22 o1_mf_s_693915680_57dlgcqh_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 11:49 o1_mf_s_693920955_57dqkw0j_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 13:28 o1_mf_s_693926889_57dxcbdx_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 14:18 o1_mf_s_693928526_57f094n9_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 14:20 o1_mf_s_693930026_57f0fbo2_.bkp
-rw-r-----    1 oracle   dba         6455296 Aug  3 14:38 o1_mf_s_693931114_57f1hbmo_.bkp

We then shutdown the database, startup in nomount mode and attempt to restore the controlfile from autobackup.

The most recent controlfile autobackup has been restored, but since this has been taken after the tablespace was dropped, the tablespace which has been dropped (ARUL) is not referenced in the control file that we just restored. If we try to restore and recover the database, the dropped tablespace will not be restored.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  2083304 bytes
Variable Size             142607896 bytes
Database Buffers          113246208 bytes
Redo Buffers                6303744 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-AUG-09
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: /u02/oradata/testdb/
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: /u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693931114_57f1hbmo_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/testdb/control01.ctl
output filename=/u02/oradata/testdb/control02.ctl
output filename=/u02/oradata/testdb/control03.ctl
Finished restore at 03-AUG-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> report schema;
...

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1230     SYSTEM               ***     /u02/oradata/testdb/system01.dbf
2    1700     UNDOTBS1             ***     /u02/oradata/testdb/undotbs01.dbf
3    370      SYSAUX               ***     /u02/oradata/testdb/sysaux01.dbf
4    280      USERS                ***     /u02/oradata/backup/bkp.04klgv2b
5    131      EXAMPLE              ***     /u02/oradata/testdb/example01.dbf
6    150      USERS                ***     /u02/oradata/backup/bkp.06klgv3k
9    100      USERS                ***     /u02/oradata/backup/bkp.08klgv4i

We will need to restore a backup of the controlfile which contains records for the tablespace ARUL. We use the RESTORE CONTROLFILE FROM command to restore a specific controlfile autobackup.

RMAN>  restore controlfile from '/u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693930026_57f0fbo2_.bkp';

Starting restore at 03-AUG-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u02/oradata/testdb/control01.ctl
output filename=/u02/oradata/testdb/control02.ctl
output filename=/u02/oradata/testdb/control03.ctl
Finished restore at 03-AUG-09

RMAN> report schema;
.....

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1230     SYSTEM               ***     /u02/oradata/testdb/system01.dbf
2    1700     UNDOTBS1             ***     /u02/oradata/testdb/undotbs01.dbf
3    370      SYSAUX               ***     /u02/oradata/testdb/sysaux01.dbf
4    280      USERS                ***     /u02/oradata/backup/bkp.04klgv2b
5    131      EXAMPLE              ***     /u02/oradata/testdb/example01.dbf
6    150      USERS                ***     /u02/oradata/backup/bkp.06klgv3k
7    0        ARUL                 ***     /u02/oradata/testdb/arul01.dbf
9    100      USERS                ***     /u02/oradata/backup/bkp.08klgv4i

The alert log will also show the time when the tablespace was dropped. We can also see that a controlfile autobackup has taken place after the tablespace was dropped.

drop tablespace arul including contents and datafiles
Mon Aug  3 14:38:34 2009
Deleted file /u02/oradata/testdb/arul01.dbf
Starting control autobackup
Control autobackup written to DISK device
        handle '/u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693931114_57f1hbmo_.bkp'
Completed: drop tablespace arul including contents and datafiles

Now that we know the time the tablespace was dropped, we can do a point in time recovery of the DATABASE in order to recover the tablespace which has been dropped.

RMAN> run {
2> set until time "to_date('03-AUG-2009 14:38:00','DD-MON-YYYY HH24:Mi:SS')";
3> restore database;
4> recover database;
5> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 03-AUG-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=159 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy recid=14 stamp=693929215 filename=/u02/oradata/testdb/users01.dbf
destination for restore of datafile 00004: /u02/oradata/backup/bkp.04klgv2b
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/testdb/system01.dbf
restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf
restoring datafile 00005 to /u02/oradata/testdb/example01.dbf
restoring datafile 00007 to /u02/oradata/testdb/arul01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0gkloo6p_1_1
channel ORA_DISK_1: copied datafile copy of datafile 00004
output filename=/u02/oradata/backup/bkp.04klgv2b recid=21 stamp=693932732
channel ORA_DISK_1: restoring datafile 00006
input datafile copy recid=13 stamp=693929146 filename=/u02/oradata/testdb/users02.dbf
destination for restore of datafile 00006: /u02/oradata/backup/bkp.06klgv3k
channel ORA_DISK_1: copied datafile copy of datafile 00006
output filename=/u02/oradata/backup/bkp.06klgv3k recid=23 stamp=693932755
channel ORA_DISK_1: restoring datafile 00009
input datafile copy recid=10 stamp=693929108 filename=/u02/oradata/testdb/users03.dbf
destination for restore of datafile 00009: /u02/oradata/backup/bkp.08klgv4i
channel ORA_DISK_1: copied datafile copy of datafile 00009
output filename=/u02/oradata/backup/bkp.08klgv4i recid=26 stamp=693932809
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0gkloo6p_1_1 tag=TAG20090803T113241
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:40
Finished restore at 03-AUG-09

Starting recover at 03-AUG-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u02/oradata/testdb/arch/arch.8.1.693662800.log
archive log thread 1 sequence 9 is already on disk as file /u02/oradata/testdb/arch/arch.9.1.693662800.log
archive log thread 1 sequence 10 is already on disk as file /u02/oradata/testdb/arch/arch.10.1.693662800.log
archive log thread 1 sequence 1 is already on disk as file /u02/oradata/testdb/redo01.log
archive log thread 1 sequence 2 is already on disk as file /u02/oradata/testdb/redo02.log
archive log filename=/u02/oradata/testdb/arch/arch.8.1.693662800.log thread=1 sequence=8
archive log filename=/u02/oradata/testdb/arch/arch.9.1.693662800.log thread=1 sequence=9
archive log filename=/u02/oradata/testdb/arch/arch.10.1.693662800.log thread=1 sequence=10
archive log filename=/u02/oradata/testdb/redo01.log thread=1 sequence=1
archive log filename=/u02/oradata/testdb/redo02.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:06
Finished recover at 03-AUG-09

RMAN>

RMAN> alter database open resetlogs;

database opened

We can now see that the tablespace which has been dropped has been recovered

SQL> select file_name,bytes from dba_data_files where
  2  tablespace_name='ARUL';

FILE_NAME
--------------------------------------------------------------------------------
     BYTES
----------
/u02/oradata/testdb/arul01.dbf
  37748736

RMAN Recovery using the SWITCH DATABASE TO COPY command

By using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk.

In this way, we can significantly reduce the downtime related to restoring large datafiles from tape in the event of a media failure.

The downside of this is that we need to consider the disk space (and cost) of additional disks which will be required to hold the recovered incremental copies of backups on disk. Also as we will see, the SWITCH DATABASE TO COPY command will alter the names of all the datafiles and there is manual work involved in renaming them back to their original datafile names.

The following RMAN command can be used to take an optimized incremental backup to disk. Note in the first run, since no copy of any of the datafiles are found since this is the first backup, no recovery is performed.

But subsequent backups will find that the backup copies of the datafiles will undergo recovery as changes which have occurred since the last incremental backup are applied to them making them ‘current’.

 run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=143 devtype=DISK

Starting recover at 31-JUL-09
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
no copy of datafile 7 found to recover
no copy of datafile 8 found to recover
no copy of datafile 9 found to recover
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 8 found
no parent backup or copy of datafile 6 found
.......
.......

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}2> 3> 4> 5>

allocated channel: c1
channel c1: sid=140 devtype=DISK

Starting recover at 31-JUL-09
channel c1: starting incremental datafile backupset restore
channel c1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=/u02/oradata/backup/bkp.2qklgmt6
recovering datafile copy fno=00002 name=/u02/oradata/backup/bkp.2pklgmpt
recovering datafile copy fno=00003 name=/u02/oradata/backup/bkp.2rklgmvr
recovering datafile copy fno=00004 name=/u02/oradata/backup/bkp.2sklgn0u
recovering datafile copy fno=00005 name=/u02/oradata/backup/bkp.2vklgn30
recovering datafile copy fno=00006 name=/u02/oradata/backup/bkp.2uklgn2h
recovering datafile copy fno=00007 name=/u02/oradata/backup/bkp.31klgn3f
recovering datafile copy fno=00008 name=/u02/oradata/backup/bkp.2tklgn1o
recovering datafile copy fno=00009 name=/u02/oradata/backup/bkp.30klgn37
channel c1: reading from backup piece /u02/oradata/backup/bkp.33klgnda
channel c1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.33klgnda tag=TAG20090731T103002
channel c1: restore complete, elapsed time: 00:00:08
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u02/oradata/testdb/undotbs01.dbf
input datafile fno=00001 name=/u02/oradata/testdb/system01.dbf
input datafile fno=00003 name=/u02/oradata/testdb/sysaux01.dbf
input datafile fno=00004 name=/u02/oradata/testdb/users01.dbf
input datafile fno=00008 name=/u02/oradata/testdb/rman10.dbf
input datafile fno=00006 name=/u02/oradata/testdb/users02.dbf
input datafile fno=00005 name=/u02/oradata/testdb/example01.dbf
input datafile fno=00009 name=/u02/oradata/testdb/users03.dbf
......
......

Let us simulate a database failure by removing all the datafiles at the OS level.

We will then use the SWITCH DATABASE TO COPY command to point the database to the backups which are available on disk and note how the recovery is performed in a few seconds without any any backups having to be restored from tape and no archive log files also being applied.

testdb:/u02/oradata> cd /u02/oradata/testdb/
testdb:/u02/oradata/testdb> rm users*.dbf

We then shutdown and mount the database

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2083304 bytes
Variable Size 146802200 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/u02/oradata/backup/bkp.2qklgmt6"
datafile 2 switched to datafile copy "/u02/oradata/backup/bkp.2pklgmpt"
datafile 3 switched to datafile copy "/u02/oradata/backup/bkp.2rklgmvr"
datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.2sklgn0u"
datafile 5 switched to datafile copy "/u02/oradata/backup/bkp.2vklgn30"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.2uklgn2h"
datafile 7 switched to datafile copy "/u02/oradata/backup/bkp.31klgn3f"
datafile 8 switched to datafile copy "/u02/oradata/backup/bkp.2tklgn1o"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.30klgn37"

RMAN> recover database;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=156 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
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: /u02/oradata/backup/bkp.2qklgmt6
destination for restore of datafile 00002: /u02/oradata/backup/bkp.2pklgmpt
destination for restore of datafile 00003: /u02/oradata/backup/bkp.2rklgmvr
destination for restore of datafile 00004: /u02/oradata/backup/bkp.2sklgn0u
destination for restore of datafile 00005: /u02/oradata/backup/bkp.2vklgn30
destination for restore of datafile 00006: /u02/oradata/backup/bkp.2uklgn2h
destination for restore of datafile 00007: /u02/oradata/backup/bkp.31klgn3f
destination for restore of datafile 00008: /u02/oradata/backup/bkp.2tklgn1o
destination for restore of datafile 00009: /u02/oradata/backup/bkp.30klgn37
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/bkp.35klgnj6
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.35klgnj6 tag=TAG20090731T103309
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 31-JUL-09

RMAN> alter database open;

database opened

After the SWITCH command has completed, we can now see that the datafile names have changed.

I have not been able to identify an easy way to rename them back to their original names other than recreating the controlfile and renaming all the datafiles at the OS level as well. This could be an issue when we have hundreds of datafiles to rename.

 1* select name,status from v$datafile
SQL> /

NAME                                     STATUS
---------------------------------------- -------
/u02/oradata/backup/bkp.2qklgmt6         SYSTEM
/u02/oradata/backup/bkp.2pklgmpt         ONLINE
/u02/oradata/backup/bkp.2rklgmvr         ONLINE
/u02/oradata/backup/bkp.2sklgn0u         ONLINE
/u02/oradata/backup/bkp.2vklgn30         ONLINE
/u02/oradata/backup/bkp.2uklgn2h         ONLINE
/u02/oradata/backup/bkp.31klgn3f         ONLINE
/u02/oradata/backup/bkp.2tklgn1o         ONLINE
/u02/oradata/backup/bkp.30klgn37         ONLINE

Similarly, we can also use the SWITCH TABLEPACE TO COPY command to quickly recover from the loss of all datafiless at the tablespace level.

We simulate this by removing all the datafiles for the USERS tablespace at the OS level.

testdb:/u02/oradata/testdb> rm users*.dbf

SQL> conn scott/tiger
Connected.
SQL> create table emp2
2 tablespace users
3 as select * from emp;
as select * from emp
*
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u02/oradata/testdb/users01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

RMAN>  run {
2> sql 'alter tablespace users offline immediate';3>
}

sql statement: alter tablespace users offline immediate

RMAN> switch tablespace users to copy;

datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.04klgv2b"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.06klgv3k"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.08klgv4i"

RMAN> recover tablespace users;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=143 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 31-JUL-09

RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/backup/bkp.08klgv4i
/u02/oradata/backup/bkp.06klgv3k
/u02/oradata/backup/bkp.04klgv2b

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

Backup and Recovery best practices dictate that we must use a RMAN recovery catalog and also have the controlfile AUTOBACKUP enabled.

If we do not do either and we lose all the controlfiles, we cannot restore the controlfiles even if we have taken a backup to tape as shown in the case below.

We will encounter the RMAN-06563 error even if we set the DBID or explicitly alllocate a channel for a tape device.

  set dbid=693232013;

executing command: SET DBID

RMAN> run {
2> restore controlfile;
3> recover database;
4> }

Starting restore at 28-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:17:19
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN>  run {
2>  allocate channel c1 device type  sbt_tape;
3> restore controlfile;
4> alter database mount;
5> recover database;
6> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=159 devtype=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0

Starting restore at 28-JUL-09

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:19:36
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

However, if we connect to a RMAN recovery catalog, we can restore a controlfile without using the AUTOBACKUP keyword.

$ rman target / catalog rman9p/xxx@rcatp

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: ofsap (not mounted)
connected to recovery catalog database

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
3> restore controlfile;
4> release channel ch1;
5> }

allocated channel: ch1
channel ch1: sid=8 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting restore at 27-JUN-08

channel ch1: starting datafile backupset restore
channel ch1: restoring controlfile
output filename=/u04/oradata/ofsap/control01.ctl
channel ch1: restored backup piece 1
piece handle=c-2764499561-20080627-08 tag=null params=NULL
channel ch1: restore complete
replicating controlfile
input filename=/u04/oradata/ofsap/control01.ctl
output filename=/u04/oradata/ofsap/control02.ctl
Finished restore at 27-JUN-08

released channel: ch1

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups -

This script will be run in the database, not the catalog.

Login as sysdba -

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

Change DBID of database

After a clone of a database to another machine with the same database name or if a new database name is created by recreating the controlfile, there is a need to recreate the dbid especially if the new database is to be registered in the same RMAN catalog.

This ensures especially if an RMAN catalog is used that the database can be registered in the catalog since only one dbid is allowed to be registered in a catalog – hence 2 databases with the same dbid on different machines, still can’t be registered in the same catalog.

To change dbid follow these steps -

Shutdown immediate;

startup mount;

Make sure the sys password is correct -

sqlplus “sys/password@test as sysdba” – should connect without errors.

Then command line :

cd $ORACLE_HOME/bin

oracle(DATABASE)@tmpu020:./nid target=sys/password@test
DBNEWID: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to database TEST (DBID=2764499561)

Control Files in database:
/u01/oradata/test/control01.ctl
/u02/oradata/test/control02.ctl

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2764499561 to 3112540754
Control File /u01/oradata/test/control01.ctl – modified
Control File /u02/oradata/test/control02.ctl – modified
Datafile /u02/oradata/test/system01.dbf – dbid changed
Datafile /u02/oradata/test/users01.dbf – dbid changed
Datafile /u02/oradata/test/patrol01.dbf – dbid changed
Datafile /u02/oradata/test/temp01.dbf – dbid changed
Datafile /u02/oradata/test/temp02.dbf – dbid changed
Control File /u01/oradata/test/control01.ctl – dbid changed
Control File /u02/oradata/test/control02.ctl – dbid changed

Database ID for database TEST changed to 3112540754.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

oracle(DATABASE)@tmpu020:

Shutdown immediate;

startup mount;

alter database open resetlogs;

To check new dbid-

oracle(DATABASE)@tmpu020:rman target /

connected to target database: TEST (DBID=3112540754)

RMAN>

RMAN Backup and Recovery scenarios

The following notes will illustrate the steps required to recover from a number of common failure scenarios that a DBA could possibly face in his career.

RMAN Restore on another machine with different file system- same database name

RMAN duplicate database on the same host

Loss Of All Files – Disaster Recovery

Incomplete Recovery (until point in time)

Incomplete Recovery (until log sequence)

Loss of all online redo log files

Loss of all controlfiles

Recovery when no backup exists of a datafile

ORA-19755 error using block change tracking file.

While restoring and recovering a database UNTIL TIME which uses the block change tracking file, the recovery may be interrupted.

This was the error -

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2009 09:33:29
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘/u02/oradata/flash_recovery_area/TEST/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc’
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: ‘/u02/oradata/test/ofsap_blk_change.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

This file is not backed up by RMAN and not needed for recovery – it is purely used by RMAN for speeding up the incremental backups.

Therefore to workaround this problem simply disable block_change_tracking and restart the recovery .

sql > alter database disable block change tracking ;

RESTART THE RECOVERY UNTIL TIME -

RMAN> run {
SET UNTIL TIME “TO_DATE (‘09-02-09 18:00:00′, ‘DD-MM-YY HH24:MI:SS’)”;
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
recover database;
release channel ch1 ;
}
executing command: SET until clause

allocated channel: ch1
channel ch1: sid=310 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting recover at 18-FEB-09

starting media recovery

archive log thread 1 sequence 117043 is already on disk as file /u02/oradata/flash_recovery_area/TEST/archivelog/2009_02_18/o1_mf_1_117043_4spoq75y_.arc
archive log thread 1 sequence 117044 is already on disk as thread=1 sequence=117045
channel ch1: starting archive log restore to default destination ==

Recovery continues.

RMAN – Create Catalog

CREATING NEW RMAN CATALOG

Database name to store catalog – RMAN10CAT

1. Create Tablespace for catalog owner in catalog database

Login to RMAN10CAT database as sysdba

SQL> create tablespace rman10 datafile ‘/u03/oradata/rman10cat/rman10.dbf’ size 200m autoextend on;

Create catalog owner in Catalog database

create user rman10 identified by rman default tablespace rman10 temporary tablespace temp;

Issue required grants to catalog owner

grant create session,resource to rman10;

grant recovery_catalog_owner to rman10;

alter user rman10 quota unlimited on rman10;

At this point the catalog owner exists in the database but there is no catalog created -

To test this –

SQL> conn rman10/rman
Connected.
SQL> select name from rc_database;
select name from rc_database
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> exit

This is because the RECOVERY CATALOG has not yet been created in the database.

CREATE CATALOG

Login as sysdba into database RMAN10CAT and also as catalog owner.

rman10cat:/u01/oracle> rman target / catalog rman10/rman

Recovery Manager: Release 10.2.0.4.0 – Production on Mon Jul 13 12:42:15 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2469552796)
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit

Recovery Manager complete.

rman10cat:/u01/oracle> sqlplus rman10/rman

SQL> select name from rc_database;

no rows selected

Catalog exists and the sql statement above will show results once databases are registered in this catalog .

RMAN Duplicate database on same host

This note describes in detail the procedure used to duplicate an Oracle 10g database on the same host but with a different database name using RMAN (on a Linux platform).

Download …

RMAN unregister database from catalog

1. Go to the database you wish to unregister and get its DBID:

system@HERML> select dbid,name from v$database;

DBID NAME
—————– ——————————
2082566940 HERML

2. Connect to the recovery catalog-owning schema and determine the DB_KEY of the database in the recovery catalog:

rman10@RMAN10P> select db_key,dbid,name from rc_database
2 where dbid = 2082566940;

DB_KEY DBID NAME
—————– —————– ——————————
555419843 2082566940 HERML

3. Use the dbms_rcvcat.unregisterdatabase procedure to unregister the database from the recovery catalog (this procedure takes the DB_KEY and the DB_ID that we just found as parameters)

– The syntax for the procedure is:
– dbms_rcvcat.unregisterdatabase(db_key,db_id);
In recovery catalog schema -

execute dbms.rcvcat.unregisterdatabase(555419843,2082566940);

10g RMAN restore on another machine

TASK: Restoring a tape backup onto another machine and bringing up the database.

ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26.

PRE-REQUISITES:

Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the correct library.

In ITLINUX26 ( NEW MACHINE)

rpsp:/opt/tivoli/tsm/client/oracle/bin>ls –lrt
total 1364
tdpo.opt.smp*
tdpoconf*
libobk.so*
agent.lic*
tdposync*
dsm.opt
tdpo.rpspd.opt*….should point to TDP on ITLINUX25.

1. BACKUP DATABASE PLUS ARCHIVE LOGS….ON ITLINUX25…DATABASE RPSP.

Backup script will look like this…

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
Backup database plus archivelog;
release channel ch1 ;
}

Get dbid name of rpsp….select dbid from v$database;

565953070

2. Now in ITLINUX26 machine…ALL RESTORATION AND RECOVERY COMMANDS MUST BE RUN ONLY IN ITLINUX26.

A:

SCENARIO ONE: ORIGINAL MACHINE IS AVAILABLE…

Copy setrpsp, initrpsp.ora to ITLINUX26 machine in appropriate areas and make required changes especially the new path of the controlfiles….

Create spfile from pfile….

Export ORACLE_HOME
EXPORT ORACLE_SID

sql>startup nomount;

After db is in nomount stage…exit

Then move directly to the RESTORE CONTROLFILE part…ignore next section…

B:

SCENARIO TWO: ORIGINAL MACHINE COMPLETELY LOST AND NO SPFILE AVAILABLE.

In case you do not have an spfile and the database has to restored and recovered….

Firstly startup nomount force the database….it will force an instance start without using an spfile….

This command must be run in an RMAN block.

vi nospfile.rcv

set dbid= 565953070;
run {
startup nomount force ;
}

Save file…

From command line..

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 13:37:31 2005

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

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> startup nomount force ;
4> }
5>
6>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/opt/oracle/product10g/dbs/initrpsp.ora’

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218244 bytes
Variable Size 58722620 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes

Recovery Manager complete.

Once the instance is in nomount state, restore the spfile.

vi restorespfile.rcv

set dbid= 565953070;
run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore spfile ;
release channel ch1 ;

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restorespfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 14:00:52 2005

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

connected to target database: RPSP (not mounted)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
4> restore spfile ;
5> release channel ch1 ;
6> }
7>
8>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 22-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring SPFILE
output filename=/opt/oracle/product10g/dbs/spfilerpsp.ora
channel ch1: reading from backup piece 20h4bm92_1_1
channel ch1: restored backup piece 1
piece handle=20h4bm92_1_1 tag=TAG20051122T030003
channel ch1: restore complete, elapsed time: 00:03:27
Finished restore at 22-NOV-05

released channel: ch1

Recovery Manager complete.
rpsp:/opt/oracle/scripts>

Now that the spfile is restored, create pfile from spfile…..edit pfile…make changes to point to this machine and then recreate spfile from pfile.

Next restore controlfile..

Make a restore.rcv file…TO RESTORE CONTROLFILE

Include following…

set dbid= 565953070;
run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore controlfile ;
release channel ch1 ;
}

Run at command prompt

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restore.rcv log=restore.log

tail – f restore.log

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece 1ph491rn_1_1
channel ch1: restored backup piece 1
piece handle=1ph491rn_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:46
output filename=/itlinux26db06/ORACLE/rpsp/control01.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control02.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control03.ctl
Finished restore at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Next step …since controlfile is restored…MOUNT THE DATABASE.

SQL> alter database mount;

Database altered.

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/itlinux26db06/ORACLE/rpsp/control01.ctl
/itlinux26db06/ORACLE/rpsp/control02.ctl
/itlinux26db06/ORACLE/rpsp/control03.ctl

Now create new .rcv for restoration of DATAFILES……

Use this script to generate the files to be renamed…

select ’set newname for datafile ‘||file_id||’ to ”’||’/'||
decode(substr(file_name,instr(file_name,’/',1)+ 1,instr(file_name,’/',1,2) – 2),
‘patriot7′,’itsun103ekcl07′)||’/'||substr(file_name,(instr(file_name,’ORACLE’)))||”’;’
from dba_data_files;

vi restoredb.rcv

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
set newname for datafile 4 to ‘/itlinux26db06/ORACLE/rpsp/users01.dbf’;
set newname for datafile 3 to ‘/itlinux26db06/ORACLE/rpsp/sysaux01.dbf’;
set newname for datafile 2 to ‘/itlinux26db06/ORACLE/rpsp/undotbs01.dbf’;
set newname for datafile 1 to ‘/itlinux26db06/ORACLE/rpsp/system01.dbf’;
set newname for datafile 5 to ‘/itlinux26db06/ORACLE/rpsp/example01.dbf’;
set newname for datafile 6 to ‘/itlinux26db06/ORACLE/rpsp/perfstat01.dbf’;
set newname for datafile 7 to ‘/itlinux26db06/ORACLE/rpsp/tools01.dbf’;
set newname for datafile 8 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf’;
set newname for datafile 9 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf’;
set newname for datafile 10 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf’;
set newname for datafile 11 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf’;
set newname for datafile 12 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf’;
set newname for datafile 13 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf’;
set newname for datafile 14 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf’;
set newname for datafile 15 to ‘/itlinux26db06/ORACLE/rpsp/undotbs02.dbf’;
restore database ;
switch datafile all;
release channel ch1 ;
}
SAVE THE FILE.

From command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restoredb.rcv log=restoredb.log

:/opt/oracle/scripts>tail -f restoredb.log

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 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /itlinux26db06/ORACLE/rpsp/system01.dbf
restoring datafile 00002 to /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
restoring datafile 00003 to /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
restoring datafile 00004 to /itlinux26db06/ORACLE/rpsp/users01.dbf
restoring datafile 00005 to /itlinux26db06/ORACLE/rpsp/example01.dbf
restoring datafile 00006 to /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
restoring datafile 00007 to /itlinux26db06/ORACLE/rpsp/tools01.dbf
restoring datafile 00008 to /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
restoring datafile 00009 to /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
restoring datafile 00010 to /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
restoring datafile 00011 to /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
restoring datafile 00012 to /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
restoring datafile 00013 to /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
restoring datafile 00014 to /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1gh47mvc_1_1

Now RECOVER DATABASE USING ANY INCREMENTAL BACKUP AND ARCHIVE LOGS…RMAN WILL DO THE NEEDFUL.

To get list of archive logs backed up by RMAN…

Sqlplus rman10rg2/rman10gr2@rmanp

select sequence# from rc_backup_redolog where db_name=’RPSP’;

Last seq is 466.

vi recoverdb.rcv

run {
allocate channel ch1 type ’sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
recover database until logseq=466 ;
release channel ch1 ;
}

Save the file…

Command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=rescover.rcv log=recover.log

RMAN does a recovery until log archive log sequence number 466…

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting recover at 21-NOV-05
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /itlinux26db06/ORACLE/rpsp/system01.dbf
destination for restore of datafile 00002: /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
destination for restore of datafile 00003: /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
destination for restore of datafile 00004: /itlinux26db06/ORACLE/rpsp/users01.dbf
destination for restore of datafile 00005: /itlinux26db06/ORACLE/rpsp/example01.dbf
destination for restore of datafile 00006: /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
destination for restore of datafile 00007: /itlinux26db06/ORACLE/rpsp/tools01.dbf
destination for restore of datafile 00008: /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
destination for restore of datafile 00009: /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
destination for restore of datafile 00010: /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
destination for restore of datafile 00011: /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
destination for restore of datafile 00012: /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
destination for restore of datafile 00013: /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
destination for restore of datafile 00014: /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: restored backup piece 1
piece handle=1nh491pl_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:26
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00015: /itlinux26db06/ORACLE/rpsp/undotbs02.dbf
channel ch1: reading from backup piece 1oh491rm_1_1
channel ch1: restored backup piece 1
piece handle=1oh491rm_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:17:57

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=456
channel ch1: reading from backup piece 1qh495a4_1_1
channel ch1: restored backup piece 1
piece handle=1qh495a4_1_1 tag=TAG20051121T040004
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.456.1.556053936.log thread=1 sequence=456
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=457
channel ch1: restoring archive log
archive log thread=1 sequence=458
channel ch1: restoring archive log
archive log thread=1 sequence=459
channel ch1: restoring archive log
archive log thread=1 sequence=460
channel ch1: restoring archive log
archive log thread=1 sequence=461
channel ch1: reading from backup piece 1rh49jc6_1_1
channel ch1: restored backup piece 1
piece handle=1rh49jc6_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:01:06
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.457.1.556053936.log thread=1 sequence=457
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.458.1.556053936.log thread=1 sequence=458
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.459.1.556053936.log thread=1 sequence=459
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.460.1.556053936.log thread=1 sequence=460
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.461.1.556053936.log thread=1 sequence=461
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=462
channel ch1: restoring archive log
archive log thread=1 sequence=463
channel ch1: reading from backup piece 1sh49jdk_1_1
channel ch1: restored backup piece 1
piece handle=1sh49jdk_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:02:56
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.462.1.556053936.log thread=1 sequence=462
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.463.1.556053936.log thread=1 sequence=463
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=464
channel ch1: restoring archive log
archive log thread=1 sequence=465
channel ch1: reading from backup piece 1th4a1e6_1_1
channel ch1: restored backup piece 1
piece handle=1th4a1e6_1_1 tag=TAG20051121T120006
channel ch1: restore complete, elapsed time: 00:00:36
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.464.1.556053936.log thread=1 sequence=464
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.465.1.556053936.log thread=1 sequence=465
media recovery complete, elapsed time: 00:00:26
Finished recover at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Now rename redolog files to point to this machine…

Now select member from v$logfile;…..rename the redo log files to area in ITLINUX26 machine

Alter database rename file ‘/….’ to ‘/itlinux26db06/ORACLE/rpsp/redo…..

Alter database open resetlogs…(IF THIS FAILS DUE TO THE PRESENCE OF A BLOCK CHANGE TRACKING FILE….

alter database disable block change tracking;

Database altered.

SQL> alter database open;

Database altered.

RMAN recovery from loss of all control files

SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG)

To simulate this scenario, we will create and populate a table with some records,
recover the database from a loss of all control files and then perform an incomplete
recovery until the point of failure.

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

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 18-SEP-07
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/
2007_09_18/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 18-SEP-07

Mount and recover the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07
Starting implicit crosscheck backup at 18-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-SEP-07

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/
2007_09_18/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/2007_09_18/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/2007_09_18/
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 18-SEP-07

SQL> alter database open resetlogs;

Database altered.

 conn scott/tiger
Connected.
SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1839328

RMAN recovery from loss of all online redo log files

MAA best practices dictates that to avoid such scenarios, we should be multiplexing the online
redo log files. Each group should have at least 2 members and each member should be located
on a different physical disk.

Loss of a single current online redo log file will require us to restore the entire database and
do an incomplete recovery.

We can simulate this scenario by deleting all the online redo log files at the OS level.

SQL> select member from v$Logfile;

MEMBER
-------------------------------
/u02/ORACLE/opsdba/redo03.log
/u02/ORACLE/opsdba/redo02.log
/u02/ORACLE/opsdba/redo01.log

opsdba:/u02/ORACLE/opsdba>rm redo*.log

If the current online redo log file is lost,the database hangs and in the alert log file
we can see the following error message:

Tue Jan 30 00:47:19 2007
ARC1: Failed to archive thread 1 sequence 93 (0)
Tue Jan 30 00:47:24 2007
Errors in file /opt/oracle/admin/opsdba/bdump/opsdba_arc0_32722.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u02/ORACLE/opsdba/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Using RMAN we can recover from this error by restoring the database from the backup and
recovering to the last available archived redo logfile. 

From the alert log we can obtain the last archived file in our case it is sequence
92 as the error shows that it fails to archive the log file sequence 93.

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         95   52428800          1 NO  CURRENT                3203078 30-JAN-07
         2          1         93   52428800          1 NO  INACTIVE               3202983 30-JAN-07
         3          1         94   52428800          1 NO  INACTIVE               3203074 30-JAN-07

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/ORACLE/opsdba/arch
Oldest online log sequence     92
Next log sequence to archive   93
Current log sequence           93

opsdba: cd /u02/ORACLE/opsdba/arc
opsdba:/u02/ORACLE/opsdba/arch> ls –lrt
total 54824
-rw-r-----    1 oracle   dba        714240 Jan 29 16:02 arch_1_90_613129285.dbf
-rw-r-----    1 oracle   dba      46281216 Jan 30 00:37 arch_1_91_613129285.dbf
-rw-r-----    1 oracle   dba         11264 Jan 30 00:41 arch_1_92_613129285.dbf

Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Mount the database

SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2069680 bytes
Variable Size              92277584 bytes
Database Buffers           67108864 bytes
Redo Buffers                6316032 bytes
Database mounted.

Use RMAN connect to the target database:

opsdba:/u02/ORACLE/opsdba>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 00:53:21 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: OPSDBA (DBID=1493612009, not open)

RMAN> run {
2> set until sequence 93; (Note: set this number to one higher than the last archived log available)
3> restore database;
4> recover database;
5>  alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 30-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
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

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_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
restoring datafile 00013 to /tmp/undonew.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 5mi8ornj_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=5mi8ornj_1_1 tag=TAG20070130T004019
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:06
Finished restore at 30-JAN-07

Starting recover at 30-JAN-07
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1

starting media recovery

archive log thread 1 sequence 92 is already on disk as file 
/u02/ORACLE/opsdba/arch/arch_1_92_613129285.dbf
archive log filename=/u02/ORACLE/opsdba/arch/arch_1_92_613129285.dbf thread=1 sequence=92
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-JAN-07

database opened

RMAN>exit
The recovery process creates the online redo logfiles at the operating system level also.

opsdba:/u02/ORACLE/opsdba>ls -lrt redo*
-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo03.log
-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo02.log
-rw-r-----    1 oracle   dba      52429312 Jan 30 01:00 redo01.log

Since we have done an incomplete recover with open resetlogs, we should take a fresh
complete backup of the database.

Recovery From Loss Of Datafile For Which No Backup Is Available

SCENARIO – 10g Database Loss of datafile which has not been backed up.

CREATE NEW TABLESPACE TEST

SQL> create tablespace test datafile ‘/u02/oradata/testdb/test.dbf’ size 10m;

Tablespace created.

TAKE A BACKUP OF THE DATABASE AT THIS POINT >>>>>>>>>

ADD DATAFILE TO TEST TABLESPACE

SQL> alter tablespace test add datafile ‘/u02/oradata/testdb/test01.dbf’ size 10m;

Tablespace altered.

SIMULATE FAILURE BY REMOVING DATAFILES FOR TEST TABLESPACE FROM DISK

Note: The tablespace TEST has two datafiles, but only one has been backed up at this point in time

testdb:/u02/oradata/testdb> rm test*
testdb:/u02/oradata/testdb> sql

SQL> alter tablespace test offline immediate;

Tablespace altered.

RESTORE DATAFILE 5; – The datafile which was backed up.

testdb:/u02/oradata/testdb> rman target / catalog rman11p/xxx@rcatp

Recovery Manager: Release 11.1.0.6.0 – Production on Thu May 14 09:19:28 2009

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

connected to target database: TESTDB (DBID=2469983515)
connected to recovery catalog database

RMAN> restore datafile 5;

Starting restore at 14/MAY/09
starting full resync of recovery catalog
full resync complete
Finished restore at 14/MAY/09

RMAN> restore tablespace test;

Starting restore at 14/MAY/09
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1

creating datafile file number=6 name=/u02/oradata/testdb/test01.dbf >>> In 10g, Oracle creates the missing datafile .
skipping datafile 5; already restored to file /u02/oradata/testdb/test.dbf
Finished restore at 14/MAY/09

RECOVER TABLESPACE TEST

SQL> recover tablespace test;
Media recovery complete.

SQL> alter tablespace test online;
Tablespace altered.

Enable block change tracking

BLOCK CHANGE TRACKING

From version 10.2 onwards, Oracle have provided a new tool which is very useful to reduce the time of RMAN incremental backups.

Prior to 10.2, all incremental backups had to read every single block in the database, and if the block has changed, it was backed up. This meant the RMAN backup job took nearly as long as a normal full backup because every block had to be read regardless.

To avoid this, Oracle introduced the BLOCK CHANGE TRACKING file – if this was enabled, then a file called the block change tracking file kept information of all changes to blocks since the last backup. This file was read instead of all the blocks in the database to arrive at changed blocks and then these blocks were backed up.

This reduced the backup time considerably – sometimes, especially in the case of Data Warehouse type databases, since changes happened infrequently, incremental backups hardly took minutes to complete as opposed to many hours.

To enable block change tracking – this can be run with the database open ( as sysdba ).

alter database enable block change tracking using file ‘/hqlinux01db05/ORACLE/test/block_change.dbf’;

Database altered.

select filename,status from v$block_change_tracking;

FILENAME
——————————————————————————–
STATUS
———-
/hqlinux01db05/ORACLE/test/block_change.dbf
ENABLED

FLASHBACK DATABASE

FLASHBACK DATABASE

Flashback database allows you to rollback database to a time in the past.

Useful if you have :

1. Dropped user
2. Truncated table
3. Batch job:Partial changes.

PRE-REQUISITE: DATABASE MUST BE IN ARCHIVELOG MODE AND YOU MUST SET FLASHBACK ON( BY DEFAULT IT IS OFF)

FLASHBACK LOGS ARE CREATED IN YOUR FLASH_RECOVERY_AREA SPECIFIED BY THE INIT.ORA PARAMETER – DB_RECOVERY_FILE_DEST

To set FLASHBACK ON in your 10g database.

shutdown immediate;

startup mount exclusive;….Must be mounted exclusive.

alter database flashback on;

alter database open;

To Flashback database…

Flashback database can be issued with 3 different conditions:

1. TO_TIME
2. TO SCN
3. TO SEQUENCE( LOG ARCHIVE SEQ)

FLASHBACK TO_TIME

PRESENT TIME IS 2009-06-29 07:40:00

drop user arjun cascade;

user dropped.

shutdown immediate;

startup mount;

flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:38:00′,’YYYY-MM-DD HH24:MI:SS’);

alter database open resetlogs;

select username from dba_users;

SQL> select username from dba_users;

USERNAME
——————————
SYS
SYSTEM
DBSNMP
ARJUN
OUTLN

After this is done you cannot flashback the database to a time before the original flashback…

Shutdown immediate;

Startup mount;

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’);
flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’)
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

But you can flashback the tablespace to a time after the original flashback time of 07:38:00

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:45:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

alter database open resetlogs;

FOR FLASHBACK TO SCN….

Alter database mount;

Flashback database to scn=12355;

To get current_scn: select current_scn from v$database;

TO FLASHBACK TO SEQUENCE NUMBER…

Alter database mount;

Flashback database to sequence=223 thread=1;

Recovery from a backup taken before a RESETLOGS

Simulate loss of redo log files 

[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log

Check alert log 

Errors in file /u02/ORACLE/opsdba/bdump/opsdba_arc1_29898.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ORACLE/opsdba/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Jun 28 12:50:59 2006
ARC1: Failed to archive thread 1 sequence 14 (0)

RECOVER DATABASE UNTIL LAST LOG SEQUENCE (13) 

RMAN> run {
2> set until logseq=14 thread=1;  >>>> Note add one to the max applied log sequence
3> restore database;
4> recover database;
5> }


executing command: SET until clause

Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

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 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 28-JUN-06

Starting recover at 28-JUN-06
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_05hmonkt
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_05hmonkt tag=TAG20060628T123748
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:05
Finished recover at 28-JUN-06

RMAN> exit
Recovery Manager complete.

[oracle@itlinuxdevblade08 opsdba]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 12:57:34 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> alter database open resetlogs;
Database altered.

SQL> conn scott/tigerConnected.
SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1271227

RECOVERY COMPLETE !

GENERATE SOME MORE ARCHIVE LOGS 

SQL> exec load_data;

PL/SQL procedure successfully completed.

SQL> select count(*) from myobjects;
  COUNT(*)
----------
   1678207

NOTE: NO BACKUP HAS BEEN TAKEN AFTER RESETLOGS - AGAIN SIMULATE FAILURE

[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log

[oracle@itlinuxdevblade08 opsdba]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:11:35 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  2020512 bytes
Variable Size             171969376 bytes
Database Buffers          146800640 bytes
Redo Buffers                6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/ORACLE/opsdba/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN> list incarnation of database;  ; 

NOTE - incarnation has not changed after resetlogs

new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
185729689 185729700 OPSDBA   1480400868       PARENT  525876     23-JUN-06
185729689 185729690 OPSDBA   1480400868       PARENT  654443     27-JUN-06
185729689 185817798 OPSDBA   1480400868       CURRENT 685999     28-JUN-06

RECOVER AGAIN - NOTICE HOW ALL ARCHIVE LOG FILES ARE APPLIED –

BOTH BEFORE RESETLOGS AS WELL AS AFTER RESETLOGS

Note:After RESETLOGS log sequence 8 is the last log sequence applied -
so we recover until sequence 9


RMAN> run {
2> set until sequence=9 thread =1;
3> restore database;
4> recover database;
5> }


executing command: SET until clause

Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

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 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 28-JUN-06

Starting recover at 28-JUN-06
using channel ORA_DISK_1

starting media recovery

NOTE: Applying archived log files BEFORE resetlogs

archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594244455.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log

NOTE: Applying archived log files AFTER resetlogs

archive log thread 1 sequence 1 is already on disk as file /u02/ORACLE/opsdba/arch/arch.1.1.594305864.log
archive log thread 1 sequence 2 is already on disk as file /u02/ORACLE/opsdba/arch/arch.2.1.594305864.log
archive log thread 1 sequence 3 is already on disk as file /u02/ORACLE/opsdba/arch/arch.3.1.594305864.log
archive log thread 1 sequence 4 is already on disk as file /u02/ORACLE/opsdba/arch/arch.4.1.594305864.log
archive log thread 1 sequence 5 is already on disk as file /u02/ORACLE/opsdba/arch/arch.5.1.594305864.log
archive log thread 1 sequence 6 is already on disk as file /u02/ORACLE/opsdba/arch/arch.6.1.594305864.log
archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594305864.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594305864.log
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
archive log filename=/u02/ORACLE/opsdba/arch/arch.1.1.594305864.log thread=1 sequence=1
archive log filename=/u02/ORACLE/opsdba/arch/arch.2.1.594305864.log thread=1 sequence=2
archive log filename=/u02/ORACLE/opsdba/arch/arch.3.1.594305864.log thread=1 sequence=3
archive log filename=/u02/ORACLE/opsdba/arch/arch.4.1.594305864.log thread=1 sequence=4
archive log filename=/u02/ORACLE/opsdba/arch/arch.5.1.594305864.log thread=1 sequence=5
archive log filename=/u02/ORACLE/opsdba/arch/arch.6.1.594305864.log thread=1 sequence=6
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594305864.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594305864.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:09
Finished recover at 28-JUN-06

RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit

ALL DATA RECOVERED UNTIL POINT OF FAILURE 

[oracle@itlinuxdevblade08 opsdba]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:34:51 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1678207

Using FLASHBACK after a Data Guard Failover

Original Data Guard Environment

HQLINUX05 - PRIMARY
ITLINUX03 - STANDBY.

Perform FAILOVER TO ITLINUX03 (now the new Primary)

Next in ITLINUX03 .....

Step 1: On the new primary, run the following query to determine at what SCN it became
the new primary.

bozo:/u01/ORACLE/bozo> sql

SQL*Plus: Release 10.1.0.2.0 - Production on Mon May 9 14:48:57 2005

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
385053

Step2: Bring the old primary database to the mount state and flashback to SCN retrieved in Step 1.

Go to original primary machine (HQLINUX05)…

SQL>
 shutdown immediate;
 startup mount;
 flashback database to scn 387774;

Flashback complete.

Step3:
SQL> alter database flashback off;  - since flashback logs are no more valid.

Step4: 

On the old primary(hqlinux05), create a standby controlfile

SQL>alter database create standby controlfile as '/tmp/standby1.ctl';

Step 5:

SQL>Shutdown immediate;

Copy the standby control file to the control_files location in the init.ora(hqlinux05)
which is the original primary.

rename the standby controlfile standby1.ctl to match the names of the control files
as defined in the control_files parameter in the init.ora.

Step 6:

SQL> Startup mount;

SQL> Alter database flashback on;

On the new primary(itlinux03) enable log shipping…

SQL> alter system set log_archive_dest_state_2=ENABLE  scope=both;

On the new standby/old primary start managed recovery - (hqlinux05)

SQL> Alter database recover managed standby database disconnect;

RMAN Block Recovery

From Oracle 9i onwards you can use RMAN to recover only blocks while database is up and
running.

This could possibly save hours and hours of recovery time as a full database restore
 is not necessary.

Error reported by user pointing to block corruption.

 POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted
(file # 48, block # 142713)
ORA-01110: data file 48: '/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf

Check first if the there is only one(few) blocks corrupted or most of the blocks are
corrupted.

macsl:/opt/oracle/admin/macsl/bdump>

Issue command below at UNIX prompt.

dbv file=/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf BLOCKSIZE=8192
LOGFILE=test.log

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBV-00200: Block, dba 201469305, already marked corrupted

macsl:/opt/oracle/admin/macsl/bdump> vi test.log
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Oct 4 14:28:37 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 262144
Total Pages Processed (Data) : 218615
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 22422
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 21107
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 2550111754 (1040.2550111754) 

You can get the list of corrupted blocks from  v$database_block_corruption

SQL> Select * from v$database_block_corruption;

You will get block number corrupt.
Ex: block 142713.

After that LOGIN TO RMAN.

macsl:/opt/oracle/admin/macsl/bdump> rman target / catalog rman10/rman10@rman10p
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 4 14:33:26 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: MACSL (DBID=1125502194)
connected to recovery catalog database

RMAN> BLOCKRECOVER DATAFILE 48 BLOCK 142713;;

Starting blockrecover at 04-OCT-06
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=119 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00048
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.1.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
piece handle=MACSL.20061004.7379.1.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: reading from backup piece MACSL.20061004.7379.2.1.602899473
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 2
piece handle=MACSL.20061004.7379.2.1.602899473 tag=TAG20061003T222108
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:04:01

starting media recovery
media recovery complete, elapsed time: 00:00:46

Finished blockrecover at 04-OCT-06

Additional information:

V$database_block_corruption is the view to check the list of corrupted blocks.

If you have multiple block list as corrupt, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;

RMAN – Clear Saved Configuration

To clear the saved configuration in rman – example the configuration for Channel 1

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;

starting full resync of recovery catalog
full resync complete
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/ora_export/backup/fin/%U’ MAXPIECESIZE 16 G;
old RMAN configuration parameters are successfully deleted
starting full resync of recovery catalog
full resync complete

11g RMAN REPAIR ADVISOR

USING RMAN REPAIR ADVISOR:

Export ORACLE_SID=test1

sqlplus “sys as sysdba”

Shutdown immediate.

Go to c:\app\bb16872\test1 and rename SYSTEM01.DBF to SYSTEM01.old

Next try to startup database…will fail with error that SYSTEM01.DBF is missing.

Y:\>rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Wed Apr 30 10:51:03 2008

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

connected to target database: TEST1 (DBID=1030433487, not open)

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08 System datafile 1: ‘C:\APP\BB16872\T
EST1\SYSTEM01.DBF’ is missing

RMAN> list failure detail;
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened

RMAN>

RMAN> advise failure;
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
122 CRITICAL OPEN 30-APR-08
System datafile 1: ‘C:\APP\BB16872\TEST1\SYSTEM01.DBF’ is missing
Impact: Database cannot be opened

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file C:\APP\BB16872\TEST1\SYSTEM01.DBF was unintentionally renamed or
moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;

RMAN> repair failure noprompt;
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;

executing repair script

Starting restore at 30-APR-08
using channel ORA_DISK_1

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 C:\APP\BB16872\TEST1\SYSTEM01.DB
F
channel ORA_DISK_1: reading from backup piece C:\APP\BB16872\BACKUP\1_TEST1_6533
95264
channel ORA_DISK_1: piece handle=C:\APP\BB16872\BACKUP\1_TEST1_653395264 tag=TAG
20080430T104104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 30-APR-08

Starting recover at 30-APR-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 30-APR-08
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened.

In case of loss of controlfile….
Follow same steps above –remove controlfile and then try to startup.

RMAN> repair failure noprompt;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_3514023340.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘C:\APP\BB16872\TEST1\CONTROL03.CTL’;
sql ‘alter database mount’;
executing repair script

Starting restore at 30-APR-08
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=C:\APP\BB16872\TEST1\CONTROL01.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL02.CTL
output file name=C:\APP\BB16872\TEST1\CONTROL03.CTL
Finished restore at 30-APR-08

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete
database opened

RMAN> exit

Recovery Manager complete.

Y:\>sqlplus “sys as sysdba”

SQL*Plus: Release 11.1.0.6.0 – Production on Wed Apr 30 11:17:04 2008

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_Data_Files;
FILE_NAME
——————————————————————————–

C:\APP\BB16872\TEST1\SYSTEM01.DBF
C:\APP\BB16872\TEST1\SYSAUX01.DBF
C:\APP\BB16872\TEST1\UNDOTBS01.DBF
C:\APP\BB16872\TEST1\USERS01.DBF

SQL>

11g Standby Database creation without a backup

11g New Feature – Standby Database creation from active database without having to take a backup ….

Note the command output – APEXDG is created by copying the datafiles directly from an open database APEX without having to restore the backup from tape

RMAN> run {

allocate channel c1 type disk;

allocate auxiliary channel c2 type disk;

duplicate target database for standby from active database

dorecover

nofilenamecheck;

}

10g optimized incremental backups to disk

 By using a combination of Oracle 10g Block Change Tracking and RMAN Optimised Incremental Backups we can significantly reduce backup durations and more importantly restore and recover time.

The optimised incremental backup will ensure that subsequent incremental backups after a full backup only contain blocks changed since the last backup and will also apply archive logs to the backup on disk so that the backupset on disk can be used to recover the database without applying any archivelog files.

Also, since the backup on disk is actually the image of the database with all recent changes applied to it, we can issue a SWITCH DATABASE TO COPY command in the event of a database crash and bring up the database directly from the backup on disk in a few minutes without having to restore any file from tape.

Note the first run of the RMAN backup command and compare the same with the second run …….. the backup of the database includes an internal recovery operation as well.

 RMAN> run {

allocate channel c1 device type disk format ‘/u02/oradata/backup/bkp.%u’;

recover copy of database with tag ‘LEVEL0_BKP’;

backup incremental level 1 for recover of copy with tag ‘LEVEL0_BKP’ database;

}

  using target database control file instead of recovery catalog

 

allocated channel: c1

channel c1: sid=147 devtype=DISK

Starting recover at 11-MAR-09

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

no copy of datafile 5 found to recover

no copy of datafile 6 found to recover

no copy of datafile 8 found to recover

Finished recover at 11-MAR-09

Starting backup at 11-MAR-09

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u02/oradata/rcatd/system01.dbf

input datafile fno=00003 name=/u02/oradata/rcatd/sysaux01.dbf

input datafile fno=00006 name=/u02/oradata/rcatd/rman10d01.dbf

input datafile fno=00002 name=/u02/oradata/rcatd/undotbs01.dbf

input datafile fno=00004 name=/u02/oradata/rcatd/users01.dbf

input datafile fno=00005 name=/u02/oradata/rcatd/rman9d01.dbf

input datafile fno=00008 name=/u02/oradata/rcatd/patrol01.dbf

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.09k9l940 tag=TAG20090311T124136 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0ak9l941 tag=TAG20090311T124136 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:02

Finished backup at 11-MAR-09

released channel: c1

 RMAN> run {

allocate channel c1 device type disk format ‘/u02/oradata/backup/bkp.%u’;

recover copy of database with tag ‘LEVEL0_BKP’;

backup incremental level 1 for recover of copy with tag ‘LEVEL0_BKP’ database;

}

  using target database control file instead of recovery catalog

 

channel c1: sid=141 devtype=DISK

Starting recover at 11-MAR-09

channel c1: starting incremental datafile backupset restore

channel c1: specifying datafile copies to recover

recovering datafile copy fno=00001 name=/u02/oradata/backup/bkp.01k9l8o8

recovering datafile copy fno=00002 name=/u02/oradata/backup/bkp.04k9l8s7

recovering datafile copy fno=00003 name=/u02/oradata/backup/bkp.02k9l8q0

recovering datafile copy fno=00004 name=/u02/oradata/backup/bkp.05k9l8sm

recovering datafile copy fno=00005 name=/u02/oradata/backup/bkp.06k9l8st

recovering datafile copy fno=00006 name=/u02/oradata/backup/bkp.03k9l8re

recovering datafile copy fno=00008 name=/u02/oradata/backup/bkp.07k9l8t1

channel c1: reading from backup piece /u02/oradata/backup/bkp.09k9l940

channel c1: restored backup piece 1

piece handle=/u02/oradata/backup/bkp.09k9l940 tag=TAG20090311T124136

channel c1: restore complete, elapsed time: 00:00:02

Finished recover at 11-MAR-09

Starting backup at 11-MAR-09

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u02/oradata/rcatd/system01.dbf

input datafile fno=00003 name=/u02/oradata/rcatd/sysaux01.dbf

input datafile fno=00006 name=/u02/oradata/rcatd/rman10d01.dbf

input datafile fno=00002 name=/u02/oradata/rcatd/undotbs01.dbf

input datafile fno=00004 name=/u02/oradata/rcatd/users01.dbf

input datafile fno=00005 name=/u02/oradata/rcatd/rman9d01.dbf

input datafile fno=00008 name=/u02/oradata/rcatd/patrol01.dbf

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0bk9lak7 tag=TAG20090311T130719 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:07

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel c1: starting piece 1 at 11-MAR-09

channel c1: finished piece 1 at 11-MAR-09

piece handle=/u02/oradata/backup/bkp.0ck9lake tag=TAG20090311T130719 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 11-MAR-09

released channel: c1

Using GLOBAL Scripts in RMAN

CREATE GLOBAL SCRIPT full_backup

{

BACKUP DATABASE PLUS ARCHIVELOG;

DELETE FORCE NOPROMPT OBSOLETE;

}

 Script can also be created from existing files as well ……

 CREATE GLOBAL SCRIPT full_backup FROM FILE ‘backup_db.rcv’;

 For each registered database then :

RUN { EXECUTE GLOBAL SCRIPT full_backup; }