Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 6 March 2012

Media Recovery in Oracle Database

Media Recovery:
Media recovery is a process of restoring the physical backups and making all the restored datafiles consistent with each other by having same SCN’s in their header’s.
Types of media recovery:
  1. Complete recovery
  2. Incomplete recovery
Though it is complete recovery or incomplete recovery, the recovery types classified based on what type of failure we are going to recover.
·        Full database recovery
·        Tablespace recovery
·        Datafile recovery
Complete media recovery:
Complete media recovery is process of restoring full database, a tablespace or a datafile from backup based upon media failure and applying the redo log files to the most current time without loosing any data.
Type of Complete Media Recovery:
·        Closed Database Recovery
·        Open Database Recovery
You can perform complete full database recovery either operating system command along with Sqlplus recovery command or RMAN utility.
Closed Database Recovery:
§         If the database is open, shut it down using Shutdown Abort option.
§         If you're recovering from a media error, correct it if possible.
§         If files are permanently damaged, restore the most recent backup files (taken as part of a full or partial backup) of only the datafiles damaged by the media failure.
§         Connect to Oracle with administrator privileges.
§         Start a new instance and mount, but do not open the database.
§         If one or more damaged datafiles were restored to alternative locations in Step 3, the new location of these files must be indicated to the control file of the associated database.
§         All datafiles you want to recover must be online during complete media recovery. To get the datafile names, query with V$DATAFILE. Then, issue the ALTER DATABASE command with the DATAFILE ONLINE option to ensure that all datafiles of the database are online.
§         To start closed database recovery of all damaged datafiles in one step, use either the Server Manager Apply Recovery Archive dialog box, or an equivalent RECOVER DATABASE statement.
§         To start closed database recovery of an individual damaged datafile, use the RECOVER DATAFILE statement.
§         Now Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the application of files is automated, Oracle prompts you for each required redo log file.
§         Oracle continues until all required archived redo log files have been applied to the restored datafiles. The online redo log files are then automatically applied to the restored datafiles and notifies you when media recovery is complete. If no archived redo log files are required for complete media recovery, Oracle does not prompt for any. Instead, all necessary online redo log files are applied, and media recovery is complete.
§         After performing closed database recovery, the database is recovered up to the moment that media failure occurred. You can then open the database using the SQL command ALTER DATABASE with the OPEN option.
Open-Database, Offline-Tablespace Recovery:
§         At this point, an open database has experienced a media failure, and the database remains open while the undamaged datafiles remain online and available for use. The damaged datafiles are automatically taken off-line by Oracle.
§         The starting point for this recovery operation can vary, depending on whether you left the database open after the media failure occurred.
§         If the database was shut down, start a new instance, and mount and open the database. After the database is open, take all tablespaces that contain damaged datafiles offline.
§         If the database is still open and only damaged datafiles of the database are offline, take all tablespaces containing damaged datafiles offline. Oracle identifies damaged datafiles via error messages. If possible, take the damaged tablespaces offline with temporary priority (to minimize the amount of recovery).
§         Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, you can proceed with database recovery by restoring damaged files to an alternative storage device.
§         If files are permanently damaged, restore the most recent backup files (taken as part of a full or partial backup) of only the datafiles damaged by the media failure.
§         If one or more damaged datafiles were restored to alternative locations (Step 3), indicate the new locations of these files to the control file of the associated database.
§         After connecting with administrator privileges, use the RECOVER TABLESPACE statement.
§         Oracle begins the roll forward phase of media recovery by applying the necessary redo log files (archived and online) to reconstruct the restored datafiles. Unless the applying of files is automated, Oracle prompts for each required redo log file.
§         Oracle continues until all required archived redo log files have been applied to the restored datafiles. The online redo log files are then automatically applied to the restored datafiles to complete media recovery. If no archived redo log files are required for complete media recovery, Oracle does not prompt for any. Instead, all necessary online redo log files are applied, and media recovery is complete.
§         The damaged tablespaces of the open database are now recovered up to the moment that media failure occurred.
Steps:
·        Mount the database
·        Restore all the datafiles from the backup and make all datafiles online.
·        Apply online redo log files or archived redo log files or both.
·        Open the database with sql>alter database open;
You can perform complete Tablespace/datafile recovery to both operating system along with Sqlplus RECOVERY command or RMAN utility method.
Steps:
·        During database open take tablespace or datafile which need recovery offline.
·        Restore the backup tablespace or datafile.
·        Apply the online redo log files or archive redo log files or both.
·        Open the database with sql>alter database open;
Incomplete Recovery:
Incomplete recovery in oracle database are done using following option when applying archived redo log files and online redo log files.
·        Time based recovery
·        Cancel based recovery
·        Change based recovery
·        Log sequence recovery
After using any one option open the database with sql>alter database open resetlogs;
The above statement reset the log sequence to 0 and this command is must used when we perform incomplete recovery.
Types of Media recovery Method in oracle
Methods of Media recovery
Using OS commands and Sqlplus
RMAN (Recovery Manager)
This old traditional method of physical backup and recovery. From oracle 9i onwards oracle recommends to use RMAN which overcomes the disadvantages in backup and recovery using OS commands. Since backup taken with OS can be used with RMAN utility. But backup taken with RMAN cannot use with OS commands for media recovery directly.
RMAN is physical backup and recovery utility comes along with oracle database software. This makes oracle database server itself to take backup and recovery it during failure. The backup information are stored in target database control file or catalog database scheme (optional one if it is used).
RECOVER DATABASE
RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. If all instances were cleanly shutdown, and no backups were restored, RECOVER DATABASE indicates a no recovery required error. It also fails if any instances have the database open (since they have the datafile locks). To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.
RECOVER TABLESPACE
RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. To translate the tablespace names into datafile names, the database must be mounted and open. The tablespaces must be offline to perform the recovery. An error is indicated if none of the files require recovery.
RECOVER DATAFILE
RECOVER DATAFILE lists the datafiles to be recovered. The database can be open or closed, provided the media recovery locks can be acquired. If the database is open in any instance, then datafile recovery can only recover off-line files.
To check corruption (Need Recovery)
SQL>SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
    r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r,V$Datafile d, V$Tablespace t      WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
COMPLETE CLOSED DATABASE RECOVERY (When Missing System Tablespace)
1. Use OS command to restore the missing datafile to its original location.
   use Dba_Data_files view to check the original location.
2. SQL>STARTUP MOUNT
3. RECOVER DATAFILE 1;
4. ALTER DATABASE OPEN.
COMPLETE OPEN DATABASE RECOVERY (When Non-system Tablespace Missing)
1. Use OS command to restore the missing datafile to its original location.
   use Dba_Data_files view to check the original location.
2. SQL>ALTER TABLESPACE <TABLESPACE_NAME> OFFLINE IMMEDIATE;‎
‎3. SQL>RECOVER TABLESPACE <TABLESPACE_NAME>;‎
‎4. SQL>ALTER TABLESPACE <TABLESPACE_NAME> ONLINE;‎
COMPLETE OPEN DATABASE RECOVERY (When Non-system Tablespace is missing and database is initially closed)
1. STARTUP; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will ‎remain mounted)‎
2. Use OS command to restore the missing datafile to its original location.
‎3. SQL>ALTER DATABASE DATAFILE 3 OFFLINE; (tablespace cannot be used because ‎the database is not open)‎
4. SQL>ALTER DATABASE OPEN;‎
5. SQL>RECOVER DATAFILE 3;‎
6. SQL>ALTER TABLESPACE <TABLESPACE_NAME> ONLINE;‎
RESTORE AND RECOVERY DATAFILE (Different Location rather than original location)
1. Use OS command to restore the missing datafile to its original location.
‎2. ALTER TABLESPACE <TABLESPACE_NAME> OFFLINE IMMEDIATE;‎
‎3. ALTER TABLESPACE <TABLESPACE_NAME> RENAME DATAFILE ‎‎'C:\Oracle1\Oradata\USER01.DBF' TO ‎‎'C:\Oracle1\Oradata\USER01.DBF';‎
‎4. RECOVER TABLESPACE <TABLESPACE_NAME>;‎
‎5. ALTER TABLESPACE <TABLESPACE_NAME> ONLINE;‎
MISSING DATAFILE RECOVERY  HAS NO BACKUPS (When database is open and all the archive since creation of database is available)
‎1. ALTER TABLESPACE <DBTST> OFFLINE IMMEDIATE;‎
‎2. ALTER DATABASE CREATE DATAFILE ‎‎'C:\oracle1\oradata\dbtst\NEWDATA01.DBF';‎
‎3. RECOVER TABLESPACE <dbtst>;‎
‎4. ALTER TABLESPACE <dbtst> ONLINE;‎
Note: If Create datafile needs to be executed the different location rqather than original then use:
ALTER DATABASE CREATE DATAFILE 'C:\oracle1\oradata\dbtst\NEWDATA01.DBF' ‎AS 'D:\oracle1\oradata\dbtst\NEWDATA01.DBF'‎
MEDIA RECOVERY: Tablespace Recovery
SQL> ALTER TABLESPACE MAIN_DBF OFFLINE IMMEDIATE;‎
CMD> RMAN TARGET SYS/ORACLE@ORCL3;‎
RMAN> RESTORE TABLESPACE MAIN_DBF;‎
RMAN> RECOVER TABLESPACE MAIN_DBF;‎
SQL> ALTER TABLESPACE MAIN_DBF ONLINE;‎
MEDIA RECOVERY: Datafile Recovery
SQL> ALTER DATABASE DATAFILE 9 OFFLINE;‎
we can check datafile details from dba_data_files or v$datafile view
CMD>Connect the Rman to the target database.
RMAN> RESTORE DATAFILE 9;‎
RMAN> RECOVER DATAFILE 9;‎
SQL> ALTER DATABASE DATAFILE 9 ONLINE;‎
MEDIA RECOVERY: Datafile Recovery with specified backup
connect the rman target
rman>startup mount;
rman> restore datafile 11 from tag='weekly_full_backup';
rman> recover datafile 11 from tag='weekly_full_backup';
rman> alter database open;
MEDIA RECOVERY: Controlfile Recovery
SQL>STARTUP NOMOUNT; Start the database in nomount phase (No CRD file is open ‎only Archive, pfile)‎
RMAN> RMAN TARGET SYS/ORACLE@ORCL CATALOG RMAN/RMAN@RMAN
RMAN> SET DBID=691421794‎
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; Must when taking backup ‎controlfile autobackup parameter should be on;‎
SQL> ALTER DATABASE MOUNT;‎
RMAN>RECOVER DATABASE;‎
SQL> ALTER DATABASE OPEN RESETLOGS;  OR ALTER DATABASE OPEN‎
SQL> ALTER DATABASE OPEN RESETLOGS; This will update all current datafiles and ‎online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and ‎time stamp.‎
Note: As soon as you have done a resetlogs run a full backup, this is important as ‎should you suffer
a second failure you will not be able to perform a second recovery ‎because after resetting the logs the SCN numbers will no longer match any older ‎backup files.‎
Database Recovery (when lost datafile but having control and redo file‎)
SQL>STARTUP MOUNT;‎
RMAN> RMAN TARGET SYS/ORACLE@ORCL3‎
RMAN> RESTORE DATABASE;‎
RMAN> RECOVER DATABASE;‎
SQL> ALTER DATABASE OPEN;‎
Database Recovery (when lost Datafile, control and redo file‎)
SQL>STARTUP NOMOUNT; ‎
RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;‎
SQL>ALTER DATABASE MOUNT;‎
RMAN> RESTORE DATABASE;‎
RMAN> RECOVER DATABASE;‎
SQL> ALTER DATABASE OPEN RESETLOGS;‎
DATABASE RECOVERY (When lost Redofiles)‎
In this scenario, it is assumed that your control files are backed up. You have a backup, ‎done for example with backup database plus archivelog;‎
‎$ sqlplus "/ as sysdba"
SQL> SHUTDOWN ABORT;
SQL> STARTUP NOMOUNT;
‎rman target sys/oracle@orcl3 catalog rman/rman@shaan‎
RMAN> set dbid = 691421794;
RMAN> RESTORE CONTROLFILE;
SQL> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAM> RECOVER DATABASE;
IF the Error Occurs then
Since the online logs were lost, complete recovery is not possible. Open the database ‎with resetlogs to continue.‎
RMAN> alter database open resetlogs;‎
Recovering Archived Logs only
In the event that you want to recover the database archived redo logs until a desired ‎time, you can use the following commands:‎
SQL> Startup Mount;‎
C:\ rman target sys/oracle@orcl3 catalog rman/rman@shaan
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';‎
or
RMAN> restore ARCHIVELOG FROM TIME "to_date('09/26/11 00:00:01','MM/DD/YY ‎HH24:MI:SS')” UNTIL TIME 'SYSDATE';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INCOMPLETE Recovery with RMAN
POINT IN TIME RECOVERY (Recovery of Particular time Backup)
C:\> SET ORACLE_SID=LIVE
C:\> RMAN TARGET SYS/ORACLE@SHAAN NOCATALOG    -- set the target database
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN
{
ALLOCATE CHANNEL DEV1 TYPE DISK;
SET UNTIL TIME "TO_DATE('2011-13-10:10:12:00', 'YYYY-DD-MM:HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}
SQL> ALTER DATABASE OPEN RESETLOGS;
SEQUENCE BASED Recovery (Recovery through Archivelog sequence Number)
1. SQL> STARTUP MOUNT;
2. Examine the Alert.log to find the SCN of event and recover to prior that event. Alternatively you can determine log SCN
   select * from V$LOG_HISTORY order by first_time desc;
3. RMAN> restore database until sequence 91
4. RMAN> recover database until sequence 91
5. SQL> ALTER DATABASE OPEN RESETLOGS;
Note: After resetlogs must take fresh backup
SCN Based Recovery
SQL> STARTUP MOUNT;
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000
SQL> alter database open resetlogs;
How to find Exact SCN number for Recovery
1. Query with V$backup_datafile
select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
(select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date('JUN-20-2010','MON-DD-YYYY')
and file# <> 0
order by completion_time desc
       );
2. Edit list_backup.log and search for checkpoint change# (chkpnt#) you need.
select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,
 first_change#,
 to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time,
 next_change# from v$archived_log
where completion_time between to_date('JUN-22-2010','MON-DD-YYYY') and Sysdate;
3) Or, if the EXACT SCN prior to crash, or logical errors is needed, use LogMiner.
BEGIN
 DBMS_LOGMNR.start_logmnr (
   starttime => to_date('10-MAR-2010 18:50:00','DD-MON-YYYY HH24:MI:SS'),
   endtime => to_date('10-MAR-2010 19:05:00','DD-MON-YYYY HH24:MI:SS'),
   options => Dbms_Logmnr.DDL_Dict_Tracking);
END;
/
SELECT scn, to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') timest, operation, sql_redo FROM   v$logmnr_contents
where scn between  21822207692 and 21822211410
order by scn;
CANCEL Based Recovery
In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.
1. STARTUP MOUNT;
2. RECOVER DATABASE UNTIL CANCEL
If you are using a backup controlfile with this incomplete recovery then use this command:
3. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Note: If you fail to specify the UNTIL clause on the RECOVER command, then you will not be able to open the database until a complete recovery is done.
4. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
CANCEL
5. Oracle returns a message indicating whether recovery is successful.
Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file.You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.
6. ALTER DATABASE OPEN RESETLOGS;
A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.
Restoring/Recovering through RMAN Cold BACKUP
Restoring SPFILE
1.Start the database with minimum paramters just set the DB_name in a text file: INITORCL3.ORA then
STARTUP NOMOUNT PFILE='C:\ORACLE1\DATABASE\INITORCL3.ORA';
2. Connect the Target database thruogh Rman
RMAN>RESTORE SPFILE FROM AUTOBACKUP;
Restoring Controlfile:
1. STARTUP NOMOUNT PFILE='C:\ORACLE1\DATABASE\INITORCL3.ORA';
2. Connect the Target database thruogh Rman
RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
Restoring and Recovering the Database
1. STARTUP MOUNT;
If the Archive and Redo log is not available then step2 otherwise step3.
2.Connect rman with target database.
   RMAN>RECOVER DATABASE NOREDO;
3. RMAN>RESTORE DATABASE;
4. RMAN>RECOVER DATABASE

1 comments:

  1. Are you monetizing your exclusive shared links?
    Did you know that ShareCash will pay you an average of $500 per 1,000 file downloads?

    ReplyDelete