Performing Tablespace-Level Recovery

In this post I want to explain how to Tablespace-Level Recovery when we have lost all the datafiles contained in one tablespace.

For performing Tablespace-Level Recovery you should have the following conditions:

1.       We have online redo log files.
2.       We have a healthy backup.
3.       We have all archived redo logfiles.

Also we have two solution. First solution is while database open and second is while database not open.

1.     Recover While database Not open:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore tablespace <TABLESPACE NAME>;

RMAN> recover tablespace <TABLESPACE NAME>;

RMAN> alter database open;

2.       Recover While database open:

RMAN> connect target /

RMAN> sql ‘alter tablespace <TABLESPACE NAME> offline immediate’;

RMAN> restore tablespace <TABLESPACE NAME>;

RMAN> recover tablespace <TABLESPACE NAME>;

RMAN> sql ‘alter tablespace <TABLESPACE NAME> online’;

This method of taking tablespace offline and then restore , recover and then back to online would work for every tablespace except SYSTEM and UNDO tablespace.

Performing Database point-in-time recovery (DBPITR) - Time-Based Recovery

In this post I want to explain how to Time-Based recovery to perform Incomplete recovry.

STEP 1:- Start the database in mount mode.

RMAN> connect target /

RMAN> startup mount;

STEP 2:- Use the RMAN Set Until Time clause and set the time to the point in Time till where you want to restore recover the database.  RMAN will restore and recover the database up to, but not including, the specified time.

RMAN> set until time “to_date(’05-aug-2015 14:32:00′, ‘dd-mon-rrrr hh24:mi:ss’)”;

STEP 3:- Restore the Database

RMAN> restore database;

STEP 4:- Recover the Database

RMAN> recover database;

STEP 5:- Open the database with resetlogs.

RMAN> alter database open resetlogs;

Use the following script:-

connect target /

startup mount;

run{

set until time “to_date(’05-oct-2015 13:00:00′, ‘dd-mon-rrrr hh24:mi:ss’)”;

restore database;

recover database;

}

alter database open resetlogs;

Performing Database-Level Recovery

In this post I want to explain how to do complete database level recovery when we have lost all the datafiles.

For performing Database level recovery you should have the following conditions:

1.       We have online redo log files.
2.       We have a healthy backup.
3.       We have all archived redo logfiles.

Now we do recovery steps:

1.     Start database in nomount state:

Sql> shut  immediate;

Sql> startup nomount;

2.     Restore control file :

RMAN> restore controlfile from autobackup;

3.     Mount database:

RMAN>alter database mount;

4.     Restore and Recover database:

RMAN>run

{

Restore database;

Recover database;

}

5.     Open database with resetlogs option:

SQL> alter database open resetlogs;

All Steps :

RMAN> connect target /

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

Performing Database point-in-time recovery (DBPITR) - SCN-Based Recovery

In this post I want to explain how to do SCN-Based recovery to a particular database SCN. In this scenario we want to recover database to specify SCN. For example we’ll create a table and drop it .

And then we recover dropped table .

DBPITR is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database.

SQL> Create table  TEST_SCN

    AS

    SELECT * FROM HR.EMPLOYEES;

Table created.

SQL> SELECT COUNT(*) FROM TEST_SCN;

 COUNT(*)

----------

      3424

SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE# FROM V$LOG;

    GROUP#  SEQUENCE#   STATUS           ARC    FIRST_CHANGE#

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

         1          0                        UNUSED            YES       0

         2          0                        UNUSED            YES       0

         3          1                        CURRENT          NO         678956

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE#

    FROM V$LOG;

    GROUP#  SEQUENCE# STATUS           ARC          FIRST_CHANGE#

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

         1          2 ACTIVE                                YES            689996

         2          3 CURRENT                             NO             689998

         3          1 ACTIVE                                YES            678956

Copy SCN  689998  because we need it for recovery.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG ALL;

SQL> DROP TABLE HR.TEST_SCN PURGE;

 SQL> SHUT IMMEDIATE;

SQL> STARTUP MOUNT;

REMEMBER WE HAVE COPIED THE CURRENT SCN NUMBER

RMAN> RESTORE DATABASE UNTIL SCN 689998;

RMAN> RECOVER DATABASE UNTIL SCN 689998;

RMAN> ALTER DATABASE OPEN RESETLOGS;

SQL> SELECT COUNT(*) FROM TEST_SCN;

  COUNT(*)

----------

      3424

Also If you have set the initialization parameter log_checkpoints_to_alert to TRUE, then every time a log switch occurs, Oracle will write the SCN of each checkpoint to your target database alert.log file. This feature can be handy when trying to determine historical checkpoint SCN activity.

database point-in-time recovery (DBPITR) - Log Sequence–Based Recovery

In this post I want to explain how to do Log sequence-Based recovery. In this case we want to recover whole database from last good backup but, we don’t have all the archivelog files and also we don’t have backup of all archivelog files. Hence Rman allows you to apply redo up specific archive redo log file by specifying sequence number.

·         Recover Steps:

To get the Sequence Number you can use the below query with time:-

SQL>select sequence#, first_change#, first_time from v$log_history order by first_time;

RMAN> rman target /

RMAN> startup mount;

RMAN> run{

2> set until sequence <SEQUENCE NUMBER> thread <THREAD NUMBER>;

3> restore database;

4> recover database;

5> }

RMAN> alter database open resetlogs;

Performing Datafile-Level Recovery

In this post I want to explain how to do datafile level recovery when we have lost  some datafiles.

For performing Datafile  level recovery you should have the following conditions:

1.       We have online redo log files.
2.       We have a healthy backup.
3.       We have all archived redo logfiles.

Also we have two solution. First solution is while database open and second is while database not open.

1.     Recover While database Not open:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore datafile ‘<Datafile NAME or Number>’;

RMAN> recover datafile ‘<Datafile NAME or Number>’;

RMAN> alter database open;

2.       Recover While database open:

RMAN> connect target /

RMAN> sql “alter database datafile ‘<Datafile NAME or Number>’ offline”;

RMAN> restore datafile ‘<Datafile NAME or Number>’;

RMAN> recover datafile ‘<Datafile NAME or Number>’;

RMAN> sql “alter database datafile ‘<Datafile NAME or Number>’ online”;

Performing Database-Level Recovery

In this post I want to explain how to do complete database level recovery when we have lost all the datafiles.

For performing Database level recovery you should have the following conditions:

1.       We have online redo log files.
2.       We have a healthy backup.
3.       We have all archived redo logfiles.

Now we do recovery steps:

1.     Start database in nomount state:

Sql> shut  immediate;

Sql> startup nomount;

2.     Restore control file :

RMAN> restore controlfile from autobackup;

3.     Mount database:

RMAN>alter database mount;

4.     Restore and Recover database:

RMAN>run

{

Restore database;

Recover database;

}

5.     Open database with resetlogs option:

SQL> alter database open resetlogs;

All Steps :

RMAN> connect target /

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

Performing Cancel-Based Recovery

A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command. The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command. This type of recovery is used when the DBA manually wants to look at the logfile name and location and then decide to apply it.

·         Recovery Steps:

1.       Connect to Rman and mount database:

RMAN> connect target /

RMAN> startup mount;

2.       Restore database:

RMAN> restore database;

3.       Connect to sql plus and doing cancel base recovery:

SQL> connect / as sysdba

SQL> recover database until cancel;

When you run above command you’ll give prompt for manually apply each archivelog files:

Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

SQL>SELECT NAME FROM V$ARCHIVED_LOG;

NAME

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

/oracle/arc_dest/arcr_1_667.arc

/oracle/arc_dest/arcr_1_668.arc

/oracle/arc_dest/arcr_1_669.arc

4.       And at the end:

SQL> alter database open resetlogs;

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

Performing Rman Block-Level Recovery

In this post I want to explain how to do Block level recovery when we have a corrupt block in a datafile.

For performing Block recovery you should have the following conditions:

1.       We have online redo log files.
2.       We have a healthy backup.
3.       We have all archived redo logfiles.

Now we do recovery steps:

    1.       Identifying corrupt blocks:

you can see error in alert logfile and v$DATABASE_BLOCK_CORRUPTION

SQL> SELECT FILE#, BLOCK# FROM v$DATABASE_BLOCK_CORRUPTION;

FILE#    BLOCK#

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

16           2532

2.       Recover the corrupt block:

RMAN> recover datafile 16 block 2532;

Or

RMAN>recover corruption list;

Block level corruption is uncommon and caused by some I/O error and It’s helpful when we have block corruption in few number of block are corrupt in a datafile.

With Backup Validate command Rman detect corruption in the blocks.

To validate the tablespace, use the following command: 

RMAN> backup validate tablespace tbs_test;

To validate a specific datafile, use the following command:

RMAN> backup validate datafile 8;

RMAN> VALIDATE DATAFILE 1 BLOCK 10;

To check the whole database, use the following command:

RMAN> backup validate database;

To check all archived redo logfiles, use the following command:

RMAN> backup validate archivelog all;

To check all archived redo log files and database:

RMAN>BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

To check for logical corruptions in addition to physical corruptions:

RMAN>BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

To check the spfile, use the following command:

RMAN> backup validate spfile;

To check the current control file, use the following command:

RMAN> backup validate current control file;

And also to check if a backup is valid and available for a restore operation we can use of following commands:

To check backup of the control file, use the following command:

RMAN> restore control file validate;

To check backup of the spfile, use the following command:

RMAN> restore spfile validate;

To check backup of tablespace users, use the following command:

RMAN> restore tablespace users validate;

To check backup of a datafile, use the following command:

RMAN> restore datafile 4 validate;

To check backup of all archived redo log files, use the following command:

RMAN> restore archivelog all validate;

To check backup of the whole database, use the following command:

RMAN> restore database validate;