Friday , July 21 2017
Home / Oracle DBA / ORACLE BACKUP DATABASE RECOVERY

ORACLE BACKUP DATABASE RECOVERY

BACKUP DATABASE RECOVERY

  1. Recover is of 2 types
    1. Complete recovery – recovering database till the point of failure. No data loss
    2. Incomplete recovery – recovering to a certain time or scn. Has data loss
  2. We will perform complete recovery if we lost only data files.
  3. We will perform incomplete recovery if we lost either redo log files, control files or archive log files.
  4. Recovery process involves two phases
    1. RESTORE – copying a file from backup location to original location as that file is lost now
    2. RECOVER – applying archive logs and redo logs to bring the file SCN in par with latest SCN
Note: Practically, we can do complete recovery even if we lost control files.

STEPS for recovering table space

SQL> alter table space my data offline;
SQL> !cp /u03/hotbkp/mydata01.dbf /data files/prod
SQL> recover table space my data;
SQL> alter table space my data online;

STEPS for recovering a single datafile

SQL> alter database data file ‘/data files/prod/mydata01.dbf’ offline;
SQL> !cp /u03/hotbkp/my data01.dbf /datafiles/prod
SQL> recover ‘datafile /datafiles/prod/mydata01.dbf’;
SQL> alter database datafile ‘/datafiles/prod/mydata01.dbf’ online;

STEPS for recovering system tablespace

SQL> shut immediate
SQL> !cp /u03/hotbkp/system01.dbf /datafiles/prod
SQL> startup mount
SQL> recover tablespace system;
SQL> alter database open;

STEPS for recovering database (we will perform this when we lost more than 50% of datafiles)

SQL> shut immediate
SQL> !cp /u03/hotbkp/*.dbf /datafiles/prod
SQL> startup mount
SQL> recover database;
SQL> alter database open;
Note: we can drop a single datafile using below command.
SQL> alter database datafile ‘/datafiles/prod/mydata01.dbf’ offline drop;
When we use above command, it will delete the file at OS level, but data dictionary will not be updated and never we can get back that file even if we have backup. So don’t use this in real time.
STEPS to recover datafile in a noarchivelog mode database
SQL> shutdown immediate
SQL> !cp /u03/coldbkp/*.dbf /datafiles/prod
SQL> !cp /u03/coldbkp/*.ctl /datafiles/prod
SQL> !cp /u03/coldbkp/*.log /datafiles/prod
SQL>startup
STEPS to recover redologfile in a noarchivelog mode database
SQL> shutdown immediate
SQL> !cp /u03/coldbkp/*.dbf /datafiles/prod
SQL> !cp /u03/coldbkp/*.ctl /datafiles/prod
SQL> recover database until cancel;
SQL> alter database open resetlogs;

STEPS to recover controlfile in a noarchivelog mode database

SQL> shutdown immediate
SQL> !cp /u03/coldbkp/*.ctl /datafiles/prod
SQL> startup mount
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;

STEPS to recover redolog file in archivelog mode

SQL> shutdown immediate
SQL> startup mount
SQL> recover database until scn 12345 / until time ‘2011-01-05 11:00:00’;
SQL> alter database open resetlogs;
Using RESETLOGS – When used resetlogs option to open the database, it will
  1. Create new redolog files at OS level (location and size will be taken from controlfile) if not already existing.
  2. Resets the log seq number (LSN) to 1, 2, 3 etc for the created files.
  3. Whenever database is opened with resetlogs option, we will say database entered into new incarnation. If database is in new incarnation, the backups which were taken till now are no more useful. So, whenever we perform an incomplete recovery we need to take full backup of database immediately.

STEPS to recover a datafile without backup

SQL> alter tablespace mydata offline;
SQL> alter database create datafile ‘/datafiles/prod/mydata01.dbf’ as ‘/datafiles/prod/mydata01.dbf’;
SQL> recover tablespace mydata;
SQL> alter tablespace mydata online;
Note: All the archives generated from the date of datafile creation should be available to do this.
Read More:
ORACLE BACKUP and RECOVERY INTERVIEW QUESTIONS
ORACLE NETWORKING INTERVIEW QUESTIONS
USER MANAGEMENT INTERVIEW QUESTIONS

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *