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
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.
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.
use Dba_Data_files view to check the original location.
2. SQL>STARTUP MOUNT
3. RECOVER DATAFILE 1;
4. ALTER DATABASE OPEN.
2. 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;
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;
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;
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;
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 rather 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;
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;
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;
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.
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;
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;
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;
$ 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';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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;
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
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;
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;
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
3. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Note: If you fail to specify the UNTIL clause on the RECOVER command, then you
4. ALTER DATABASE OPEN RESETLOGS;
1. STARTUP MOUNT;
2. RECOVER DATABASE UNTIL CANCEL
3. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Note: If you fail to specify the UNTIL clause on the RECOVER command, then you
4. ALTER DATABASE OPEN RESETLOGS;
Restoring/Recovering through RMAN Cold BACKUP
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 through Rman
RMAN>RESTORE SPFILE FROM AUTOBACKUP;
STARTUP NOMOUNT PFILE='C:\ORACLE1\DATABASE\INITORCL3.ORA';
2. Connect the Target database through Rman
RMAN>RESTORE SPFILE FROM AUTOBACKUP;
Restoring Controlfile:
1. STARTUP NOMOUNT PFILE='C:\ORACLE1\DATABASE\INITORCL3.ORA';
2. Connect the Target database through Rman
RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
2. Connect the Target database through 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
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
No comments:
Post a Comment