Recovery scenarios using HOT/COLD/RMAN backups

Recovery scenarios using HOT/COLD/RMAN backups

Hi,

Here the several restoring and recovering scenarios of database/files/tables using HOT/COLD/RMAN backups.  I have listed all resolutions in one page as below.

Backup I had	What have I lost?	How to recover?
RMAN	system datafile	http://orcl11gdba.blogspot.in/2014/09/recovery-of-database-using-rman-backup.html
RMAN	Control file	http://orcl11gdba.blogspot.in/2014/09/recovery-using-rman-backup-when-control.html
RMAN	Users datafile	http://orcl11gdba.blogspot.in/2014/11/recovering-when-usersnon-system-data.html
COLD backup	Any of CRD files(control,redo,data files)	http://orcl11gdba.blogspot.in/2014/04/recovering-database-using-cold-backup.html
HOT backup	control file	http://orcl11gdba.blogspot.in/2014/03/recovering-database-when-control-file.html
HOT backup	system datafile	http://orcl11gdba.blogspot.in/2014/03/recovering-when-system-data-file-is-lost.html
HOT backup	sysaux datafile	http://orcl11gdba.blogspot.in/2014/03/recovering-when-sysaux-data-file-is-lost.html
HOT backup	temp datafile	http://orcl11gdba.blogspot.in/2014/03/recovering-when-temp-file-is-lost.html
HOT backup	undo datafile
HOT backup	users datafile	http://orcl11gdba.blogspot.in/2014/03/recovering-when-users-data-file-is-lost.html
HOT backup	online redo log files	http://orcl11gdba.blogspot.in/2014/03/recovering-when-redolog-file-is-lost.html
HOT backup	Tablespace	http://orcl11gdba.blogspot.in/2014/03/recovering-tablespace-was-lost.html
HOT backup	table	http://orcl11gdba.blogspot.in/2014/03/recovering-table-when-lost.html

 

Recovering when system data file is lost

Recovering when system data file is lost:

Previously we have taken the HOT backup to /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ path. Now whenever a system datafile got missed, deleted, corrupted, overridden or disk errors occurred then database will be shutdown.

In this case we need to recover the system datafile by restoring the file from /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ to /u01/oracle/app/oradata/pavan/database_1/.

1. Before proceeding with recovery operation we need to shut down the database (generally it will shut down automatically).

SQL> SHUT ABORT

2. Now copy the files from backup location to datafiles location. This is restoration of system datafile.

cp   /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/system01.dbf /u01/oracle/app/oradata/pavan/database_1/

3. Start the database in mount state

SQL>STARTUP MOUNT

4. Now we shall be doing recovery operation

SQL>RECOVER  DATAFILE 1;

In this process it will start applying all available archivelogs to the database and recovers it. We need to cancel once all archive logs are applied.

Enter cancel for cancelling the operation. Or else we can give AUTO to apply all archive logs  

5. Bringup the database in open mode .

SQL>ALTER DATABASE OPEN;

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS

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

OPEN

Recovering when sysaux data file is lost

How to Recover database when sysaux data file is lost:

Previously we have taken the HOT backup to /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ path. Now whenever a sysaux datafile got missed, deleted, corrupted, overridden or disk errors occurred then we don’t need to shut down the database. We can perform recovery by keeping database in open status only.

In this case we need to recover the sysaux datafile by restoring the file from /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ to /u01/oracle/app/oradata/pavan/database_1/.

1. Before proceeding with recovery operation we need to make the sysaux tablespace offline

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;

Tablespace altered.

2. Now copy the files from backup location to datafiles location. This is restoration of  sysaux datafile.

cp   /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/sysaux01.dbf /u01/oracle/app/oradata/pavan/database_1/

3. Now we shall be doing recovery operation

SQL>RECOVER DATAFILE 2;

or

SQL>ALTER DATABASE RECOVER DATAFILE ‘/u01/oracle/app/oradata/pavan/database_1/sysaux01.dbf’

In this process it will start applying all available archivelogs to the database and recovers it. We need to cancel once all archive logs are applied.

Enter cancel for cancelling the operation. Or else we can give AUTO to apply all archive logs automatically 

4. Check status of database;

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS

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

OPEN

5. Make tablespace online

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.