>  Operating System
>  VMWARE
>  Installations - Apps11i
>  R12 Topics
>  Installations - Apps R12
>  Patching
>  Cloning
>  Oracle Apps Upgrades
>  System Administration - R11/R12
>  Advance Features - Oracle Apps R12
>  Want to Become an Apps DBA ?
>  Certification - Apps DBA
>  Apps Performance
>  Oracle Apps DBA Interview Questions
>  Apps Day to Day Queries

Home

Username

Password

Remember Me







Installation
RMAN - Backup & Recovery
Replication
Convert DB Character Set
DATAPUMP
Recoveries
Oracle 11g Topics
Oracle 11g DBA course

   Installation

Oracle Recoveries Scenarios


1. Table recovery using TSPIR

2. Tablespace recovery
3. Database Recovery - No back up was taken after reset logs
4. Tablespace Recovery - using flashback database
5. Recover Controlfile
6. Recover Online RedoLog files
7. Added a datafile after backup - Recover
8. Recover datafiles
9. Complete Recovery






Created on 04/16/2009 04:42 AM by akr14feb
Updated on 10/14/2011 10:05 PM by akr14feb
 Printable Version

The comments are owned by the poster. We are not responsible for its content.
For further reference
Posted on: 2009-04-16 05:00:33   By: Anonymous
 
http://www.dbasupport.com/oracle/ora10g/TSPITR.shtml


How to recover and open the database if the archive log required for recovery is missing
Posted on: 2009-05-06 00:30:46   By: Anonymous
 
http://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive-log-required-for-recovery-is-missing/#comments


Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost (Doc ID 372996.
Posted on: 2011-10-14 22:06:12   By: akr14feb
  Edited By: akr14feb
On: 2011-10-14 22:07:26
Modified Date LabelModifiedModified Date02-SEP-2010Document Type LabelTypeDocument TypeHOWTOStatusMODERATED(EXTERNAL)Priority3To Bottom
In this Document
Goal
PURPOSE
SCOPE & APPLICATION
Solution
INTRODUCTION
Restoring When All is Lost
1. Restoring the INIT.ORA File
2. Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is NOT used
2.1 Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is used
3. Query the Backup History in the Restored Controlfile
3.1 Restored controlfile does NOT contain the database backup metadata
References



--------------------------------------------------------------------------------

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.



Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.0 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
Checked for relevance on 05-AUG-2009
Goal
PURPOSE

The purpose of this note is to provide guidelines on how RMAN can be used to restore and recover a database when all that is available are the physical backuppieces on disk or tape. Such a situation might occur if:

a. in a total disaster situation not only is the database lost but also all the controlfiles, the spfile (9i and later) and the recovery catalog (if used)
b. there is a need to restore and recover the database from an old backup that has long since been deleted or aged out of the rman repository (such as a disk backup that was copied to tape prior to deletion via RMAN)



SCOPE & APPLICATION

This article is meant for database administrators and backup and recovery specialists tasked with the restore and recovery of a database when only the physical backuppieces are available. A good working knowledge of Oracle and Recovery Manager is assumed. Oracle versions 8i through to 10G are covered.

Solution
INTRODUCTION



The recovery options available if the controlfiles , init.ora/spfile and recovery catalog are irretrievably lost vary depending on the version of Oracle used.


Recovery Options Summary


Release




Spfile can be restored (from autobackup)


Controlfile can be restored (from autobackup)


Disk backuppieces must be in original backup location
Backuppieces unknown to restored controlfile can be cataloged
Manual extraction from backuppieces using PL/SQL


Primary Objective

The primary objective is to restore the spfile and controfile either from an autobackup controfile or via manual extraction from an rman backuppiece so that the instance can be mounted. The restored controlfile then becomes the RMAN repository, allowing a ‘normal’ catalog-free rman restore and recovery of the database to be made. The restored controlfile should therefore ideally also contain the metadata for the database and archivelog backups which subsequently need to be restored and recovered.


Limitations

· It is not possible at any release to restore an init.ora file from a backuppiece as init.ora files are never included in a backup.
· It is the users’ responsibility to identify the correct backuppiece(s) from which to do the restore.
· The dbid of the target database must be known.
· Unless you are using 10g onwards, all disk backuppieces must reside in the original backup location so if you need to restore to a new host, an identical backup directory must be created.

As a last resort the spfile, controlfiles and datafiles can all be manually extracted from the backuppieces with the help of Oracle Support Services. To facilitate the process should you ever need to do this, you should follow these simple recommendations:

· Keep backup logs for at least one complete backup cycle so that backupieces and the target dbid can easily be identified
· Choose a format that makes the backuppiece easily identifiable should the backup log no longer be available
· 8i only – backup the init.ora file using any non-rman means
· 8i only – make sure you backup the current controlfile after every backup of the database or archivelog backup so that you always have a controlfile backup containing the latest backup metadata
· 9i onwards - always use an spfile as this is backed up by RMAN
· 9i onwards - always run with CONTROLFILE AUTOBACKUP ON
· KNOW your rman configuration: where the backups are written , backup frequency and most importantly where the controlfile autobackups are written to (if not defaulted)





Restoring When All is Lost




1. Restoring the INIT.ORA File

This section is relevent to ALL releases where an init.ora file is used.

RMAN has to connect to a running instance.
If an init.ora file is used rather than an spfile, this should be restored from an OS backup or manually rebuilt – it is the user’s responsibility to ensure correct content.

2. Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is NOT used
This section is relevent to:

Release 8i (where controlfile AUTOBACK feature did not exist)

Release 9i onwards (where the persistent configuration parameter CONTROLFILE AUTOBACK is OFF )

a. Identify the latest controlfile backupiece

Ideally, the controlfile backup chosen should contain the metadata for the database and archivelog backups needed for restore/recovery purposes. Use past rman logs, check available backup directories on disk or interrogate your media manager catalog (tape backups only). It is the user's responsibility to identify the correct backuppiece.

b. Extract the controlfile

From release 9i onwards, you can restore the controlfile from an explicit backuppiece - see Note 403883.1: How To Restore Controlfile From A Backupset Without A Catalog Or Autobackup. The same syntax can be used to restore the spfile.

For releases prior to 9i, raise a call with Oracle Support Services to request assistance to extract the controlfile.

c. Mount the instance

Once the controlfile has been extacted you can mount the instance and then continue from Step 3 Query the Backup History in the Restored Controlfile.



2.1 Restoring the SPFILE and CONTROLFILE when CONTROLFILE AUTOBACKUP is used

This section is relevant to:

Release 9i onwards (where the persistent configuration parameter CONTROLFILE AUTOBACK is ON )

The autobackup controlfile feature makes recovery after loss of the RMAN repository very much simpler. Controlfile autobackup formats must include %F which makes them easily identifiable.



%F equates to:
c---
Release 9i :

If controlfile autobackups are written to disk, by default they are written to $ORACLE_HOME/dbs (unix) or %ORACLE_HOME%/database (windows/nt) so look for the autobackup files in one of these directories first.


Release 10G onwards:

If controlfile autobackups are written to disk, by default they are written to the FRA if one is configured otherwise the default location is the same as for 9i.

If you don’t see any controlfile autobackups then possibly they were written to a non-default directory – if you don’t know where this might be, do a search or a ‘find’ for any file with the dbid of your target database in its name. If you find it you can use ‘SET CONTROLFILE AUTOBACKUP FORMAT ’ in the script below to force rman to look in the correct location for the autobackup file.

For autobackups written to tape, no further action is required other than to allocate the appropriate channel – check the rman backup logs to see what media manager environment variables if any were specified for the channel and make sure you use a similar configuration.

The following example is for restoring from a DISK autobackup.

a. Startup NOMOUNT the instance

You must use rman – if you use sqlplus, it will fail as follows:



$ sqlplus "/ as sysdba"

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT.2.0\DB_2\DATABASE\INITTST102.ORA'

Using RMAN:

$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 21 20:49:26 2006
connected to target database (not started)

RMAN> startup nomount;
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started……



b. Restore the spfile and controlfile


RMAN> set dbid=;
run {
set controlfile autobackup format for device type disk to '\%F';
restore spfile from autobackup;
restore controlfile from autobackup;
startup force mount
}
Notes

a. Take the dbid from the name of the controfile autobackup filename or the rman backup logs
b. 'set controlfile autobackup format' is only necessary if the controlfile autobackup is in a NON default location – set accordingly. If restoring from tape use: set controlfile autobackup format for device type sbt to ‘%F’;
c. 'startup FORCE mount' is needed because the instance was originally started without an spfile. If you try to mount without restarting it will fail:
RMAN-03002: failure of alter db command at 05/28/2006 20:58:51
ORA-01103: database name 'TST102' in control file is not 'DUMMY'




3. Query the Backup History in the Restored Controlfile

This section is relevant to:



Releases 8i, 9i, 10g and 11g


Having restored the controfile and mounted the instance you need to confirm that the controlfile contains the metadata for the database and archivelog backups needed to restore and recover the database.

Use a variant of the list backup command to find the information – examples follow:




RMAN> list backup;
RMAN> list backup of database completed after ‘’;
RMAN> list backup of database completed between ‘’ and ‘’;

must conform to the format set by NLS_DATE_FORMAT.




Once you have found the database backup you need, note the checkpoint scn of the backup eg

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
98 Incr 0 7.02M DISK 00:01:25 27-jun-06 14:58:18
BP Key: 98 Status: AVAILABLE Compressed: NO Tag: TAG20060627T145650
Piece Name: D:\ORACLE\PRODUCT.2.0\FLASH_RECOVERY_AREA\TST102\BACKUPSE
T06_06_27\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP
List of Datafiles in backup set 98
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 0 Incr 1152482 27-jun-06 14:57:00 D:\ORACLE\PRODUCT.2.0\ORADATA\TST102\SYSTEM01.DBF
….etc

To check that the archivelogs needed for recovery have been backed up:





RMAN> list backup of archivelog from scn=1152482;



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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ------------------
104 322.46M DISK 00:01:13 07-jul-06 08:33:39
BP Key: 105 Status: AVAILABLE Compressed: NO Tag: TAG20060707T083222

Piece Name: D:\ORACLE\PRODUCT.2.0\DB_2\DATABASE\ARC_3BHNIL0Q_1_1

List of Archived Logs in backup set 104
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------ ---------- ---------
1 49 1152432 15-jun-06 14:55:49 1154290 15-jun-06 15:14:46
1 50 1154290 15-jun-06 15:14:46 1175092 15-jun-06 20:17:04
1 51 1175092 15-jun-06 20:17:04 1203051 16-jun-06 08:04:08
1 52 1203051 16-jun-06 08:04:08 1233232 19-jun-06 13:12:02
1 53 1233232 19-jun-06 13:12:02 1257075 19-jun-06 14:38:20
1 54 1257075 19-jun-06 14:38:20 1283386 19-jun-06 19:54:38
1 55 1283386 19-jun-06 19:54:38 1306200 20-jun-06 07:50:13
1 56 1306200 20-jun-06 07:50:13 1382381 27-jun-06 12:08:44
1 57 1382381 27-jun-06 12:08:44 1431252 28-jun-06 07:53:14

If all the required metadata needed for restore and recovery is present then you can proceed with restore and recovery of the database until say, archive log sequence 57:

RMAN> run {
set until sequence = 58 thread 1;
restore database;
recover database;
alter database open resetlogs;
}

3.1 Restored controlfile does NOT contain the database backup metadata

Release 8i and 9i :

Find a later controlfile backup and try again. Otherwise, raise a Service Request with Oracle Support Services requesting assistance to extract the database files and archivelogs as per Internal unpublished Note 60545.1 How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets.

Once the database files and archivelogs have been extracted, recovery can be done via rman eg to recover up to (and including log sequence 57):





RMAN> run{
set until sequence 58 thread 1;
recover database;
alter database open resetlogs;
}



Release 10g onwards:
Catalog the required backuppiece(s) into the restored cong backuppiece ‘X’;


For disk:

RMAN> catalog backuppiece ‘\X’;

Where 'X' is the backuppiece name.
All the information regarding backuppiece content are in the backuppiece header.
Once cataloged you can query the contents.

Example:

RMAN> catalog backuppiece 'd:\temp\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP';
cataloged backuppiece
backup piece handle=D:\TEMP\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP recid=104 stamp=595075486

Note that RMAN inserts the name in CAPITALS!

RMAN> list backuppiece 'd:\temp\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP';

RMAN-03002: failure of list command at 07/06/2006 10:45:12
RMAN-20260: backup piece not found in the recovery catalog
RMAN-06092: error while looking up backup piece

RMAN> list backuppiece 'D:\TEMP\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP';

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
104 98 1 2 AVAILABLE DISK D:\TEMP\O1_MF_NNND1_TAG20060627T145650_2B2G9KMB_.BKP




Note the BS Key: 98
RMAN> list backupset 98;

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

BS Key Type LV Size
------- ---- -- ----------
98 Incr 1 7.02M
List of Datafiles in backup set 98
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 1 Incr 1152482 27-jun-06 14:57:00 D:\ORACLE\PRODUCT.2.0\ORADATA\TS
T102\SYSTEM01.DBF
…etc..



If you are happy that you have cataloged the correct backuppiece(s), then you can proceed with restore and recovery of the database as normal.


References



Complete Recovery - 2nd solution
Posted on: 2013-02-12 19:00:13   By: akr14feb
 
RMAN> startup nomount


rman target /

restore controlfile from '/d03/oracle/OBIEE/oracle/product/11.2.0/db_1/dbs/backup/control0co1radh_1_1';

rman> alter database mount;

rman>catalog start with '/mnt/USB/rman/';

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
}

recover database;

alter database open resetlogs;