Recovery

Recovery of a metadata or Oracle system-related tablespace. Examples include:

• SYSTEM
• RBS
• PV_CFGD
• PV_CFGI
• PV_CFHD
• PV_CFHI
• PV_PROH
• PV_THRH
• PV_REPT
• PV_REPM
• PV_LOIS
• PV_VOID

To recover these tablespaces

1. Restore the most recent copy of the tablespace by using RMAN
2. Recover the tablespace by applying all archive logs generated since your backup was taken.

This scenario shows a recovery after you attempt to start your database and it reports an error similar to the following:

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/export/home2/PV/PV_PV_CFHD_0001.dbf'

Scenario 1

The following information appears in the alert log:

Errors in file /export/home/oracle/admin/PV/bdump/pv_dbw0_3051.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/export/home2/PV/PV_PV_CFHD_0001.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...

Log in to RMAN, restore and recover the tablespace, and then open the database:

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> restore tablespace PV_CFHD;
5> recover tablespace PV_CFHD;
6> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=9 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel ch1: starting datafile backupset restore
RMAN-08502: set_count=139 set_stamp=446155924 creation_time=18-NOV-01
RMAN-08089: channel ch1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00005 to /export/home2/PV/PV_PV_CFHD_0001.dbf
RMAN-08023: channel ch1: restored backup piece 1
RMAN-08511: piece handle=/export/home5/hot_backup/df_PV_1_139 tag=null
RMAN-08024: channel ch1: restore complete
RMAN-03022: compiling command: recover
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 11 is already on disk as file /export/home4/PV/
arch/PV_1_11.arch
RMAN-06050: archivelog thread 1 sequence 17 is already on disk as file /export/home4/PV/
arch/PV_1_17.arch
RMAN-03023: executing command: recover(4)
RMAN-08515: archivelog filename=/export/home4/PV/arch/PV_1_11.arch sequence=11
RMAN-08515: archivelog filename=/export/home4/PV/arch/PV_1_15.arch sequence=15
RMAN-08055: media recovery complete
RMAN-08031: released channel: ch1
RMAN> run
2> {
3> sql 'alter database open';
4> }
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database open
RMAN-03023: executing command: sql
RMAN> 

Scenario 2

Recovery of a tablespace that is placed into READ ONLY mode and backed up.

While similar to the first scenario, it differs in that you must restore the tablespace. No recovery is needed because it is in READ ONLY mode ever since it was backed up.

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> restore tablespace C01_NRAW_000_2001111500;
5> }

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: ch1
RMAN-08500: channel ch1: sid=9 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel ch1: starting datafile backupset restore
RMAN-08502: set_count=121 set_stamp=446063881 creation_time=17-NOV-01
RMAN-08089: channel ch1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00104 to /export/home3/PV/PV_C01_NRAW_000_2001111500_001.dbf
RMAN-08023: channel ch1: restored backup piece 1
RMAN-08511: piece handle=/export/home6/readonly_backup/df_PV_1_121 tag=null 
RMAN-08024: channel ch1: restore complete
RMAN-08031: released channel: ch1
RMAN> run
2> {
3> sql 'alter database open';
4> }
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database open
RMAN-03023: executing command: sql
RMAN>

Scenario 3

Recovery of a tablespace that has data loaded into it but is not backed up to permanent storage.

How you handle this scenario depends on whether you have intermediate backups:

If you have an intermediate backup, restore the tablespace and run the REVALIDATE_OBJECTS_IN_TBSP procedure in PVM_BACKUP. (Having the BOF files is important.) This procedure populates the TBSP_RELOAD_DESC table. LDR/DLDR then scans the corresponding views and reloads the associated files into the table partitions.

If you do not have an intermediate backup of a recently loaded metric tablespace and there are no newer tablespaces for the same channel and data set type, you can run the DROP_TBSP_AND_RELOAD procedure from the PVM_BACKUP package. This procedure drops the tablespace and populates the table. The LDR/DLDR then scansTBSP_RELOAD_DESC the corresponding views and determines whether the associated data is still available on disk. If the data is available, it then creates that tablespace and reloads the data. If the data is not available, then an error is written to the proviso.log. If there are newer tablespaces for the given set type, you also have the option of removing all of these tablespaces and having the LDR/DLDR reload them. Usually, this type of situation exists when the LDR/DLDR is behind and loaded multiple days of data since the last backup. You must ensure that the associated data is available for reloading. Do not assume this fact. Remember after a tablespace is successfully placed into read only mode and backed up, the LDR/DLDR is notified that it can delete the corresponding load data.

If you do want to remove newer tablespaces, you can use the following query to determine the newest tablespace for a given channel and data set type:

Select Max(Tablespace_Name)
  From DBA_TABLESPACES
 Where Tablespace_Name Like Substr( '{put_real_name_here}',1,12)||'%'
   And Substr(Tablespace_Name,14) != '0000000000'; 

Then, for each tablespace you switch the tablespace OFFLINE and then call the DROP_TBSP_AND_RELOAD procedure. You start with the most recent tablespace and work back until you reach the missing tablespace you want to reload. After all tablespaces are dropped, restart the LDR so it can reload the data.