ORACLE BACKUP AND RECOVERY

WHAT IS BACKUP AND RECOVERY IN ORACLE

·        Backup Strategy

-          Whole DB backup

-          Partial DB backup

·        Backup Type

-          Full DB backup

-          Incremental DB Backup

·        Backup Mode

-          Online( Inconsistent ) DB backup

-          Offline ( Closed/Consistent ) DB Backup

Consistent and Inconsistent Backups

A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time. Unlike an inconsistent backup, a consistent whole database backup does not require recovery after it is restored.
The only way to make a consistent whole database backup is to shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options and make the backup while the database is closed.
The important point is that you can open the database after restoring a consistent whole database backup without needing recovery because the data is already consistent: no action is required to make the data in the restored datafiles correct. Hence, you can restore a year-old consistent backup of your database without performing media recovery and without Oracle performing instance recovery. Of course, when you restore a consistent whole database backup without applying redo, you lose all transactions that were made since the backup was taken.

Backup Terminology

Whole Database Backups

A whole database backup is a backup of every datafile in the database, plus the control file. Whole database backups can be taken in either ARCHIVELOG or NOARCHIVELOG mode.

Whole database backup (also known as whole backup) refers to a backup of all datafiles and the control file of the database. Whole backups can be performed when the database is closed or open. This is the most common method of backup.

The whole backup that is taken when the database is closed (after the database is shut down using the NORMAL, IMMEDIATE, or TRANSACTIONAL options) is called a consistent backup. In such a backup, all the database file headers are consistent with the control file, and when restored completely, the database can be opened without any recovery. When the database is operated in NOARCHIVELOG mode, only a consistent whole database backup is valid for restore and recovery.

When the database is open and operational, the datafile headers are not consistent with the control file unless the database is open in read-only mode. When the database is shut down with the ABORT option this inconsistency persists. Backups of the database in such a state are termed as an inconsistent backup. Inconsistent backups need recovery to bring the database into a consistent state. When databases need to be available 7 days a week, 24 hours a day, you have no option but to use an inconsistent backup, and this can be performed only on databases running in ARCHIVELOG mode

Tablespace Backup

A tablespace backup is a backup of the datafiles that make up a tablespace. Tablespace backups are valid only if the database is in ARCHIVELOG mode because redo entries will be required to make the datafiles consistent with the rest of the database. You can make tablespace backups when the tablespace is read-only or offline-normal in NOARCHIVELOG mode.

Datafile Backups

You can make backups of a single datafile if your database is in ARCHIVELOG mode. You can make backups of read-only or offline-normal datafiles in NOARCHIVELOG mode.

Control File Backups

You can configure RMAN for automatic backups of the control file after a BACKUP or COPY command is issued. The control file can also be backed up through SQL commands

BACKUP TYPES

Full Backups

A full backup is different from a whole database backup. A full datafile backup is a backup that includes every used data block in the file. RMAN copies all blocks into the backup set or image copy, skipping only datafile blocks that have never been used. For a full image copy, the entire file contents are reproduced exactly. A full backup cannot be part of an incremental backup strategy; it cannot be the parent for a subsequent incremental backup.

Incremental Backups

An incremental backup is either a level 0 backup, which includes every block in the datafiles except blocks that have never been used, or a level 1 backup, which includes only those blocks that have been changed since a previous backup was taken. A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0 backup can be used as the base for a level 1 backup, but a full backup can never be used as the base for a level 1 backup.

Incremental backups are specified through the INCREMENTAL keyword of the BACKUP command. You specify INCREMENTAL LEVEL = [0 | 1].

RMAN can create multilevel incremental backups as follows:

• Differential: Is the default type of incremental backup that backs up all blocks changed

after the most recent incremental backup at either level 1 or level 0

• Cumulative: Backs up all blocks changed after the most recent backup at level 0

1. USER MANAGED BACKUP & RECOVERY

2. RMAN BACKUP & RECOVERY

3. USING DBCONTROL TO PERFORM BACKUP &RECOVERY

Ø 1 USER MANAGED BACKUP & RECOVERY

NoArchivelogMode DEMO

DEMO-1

Consistent Whole DB Backup ( Closed DB Backup )

Scenario

Backup Mode->OFFLINE .

( Database is shutdown cleanly, Backup is to be taken for a  datafile. We are going to recover the database because the next startup will give problem due to this backed up file)

STEPS

Step1 – Shutdown the database using immediate, normal, or transactional option.

Step2 – Take the copy of datafile (even if system datafile) and paste it in a backup destination using OS command/action. (Suppose in “backup” folder)

Step3 – Assuming one of the datafile is corrupted delete this data file.

Step4 – Start the DB. It will give error.

Step5 – Shutdown the DB, Copy paste the corresponding file from “backup” folder and paste it in the “Datafile” folder (Assuming “datafile” is the folder where all datafile of the DB present)

Step5 – Start the DB.  ( DB will open )

CONCLUSION

Each time when a database is opened, it searches all the datafile controlfile and redologfile presence, as specified in the controlfile. If it is not available in the respective destination then oracle cannot start the DB. As the database was the clean/consistent DB, that’s why to start the database, oracle need not require the applying of any redo information. So in this scenario the redologfile backup is not required.  Because the datafile header, controlfile and redologfile header contain the same SCN and are in synchronized state. But in this demo if the “Shutdown Abort” option was used to shutdown the DB and after that if the backup was taken, then oracle need to recover the database(which is automatic) by applying the redo present in redologfile(instance recovery).

DEMO-2

Scenario

Backup Mode-> OFFLINE ,  Database is in NOARCHIVELOG mode. Cold backup of a datafile has taken.

STEPS

Step1 – Take the backup of a datafile when the DB is down.

Step2 – Start &Open the DB, Perform some DML operation to a table in a user associated to this datafile.

Note- DO NOT PERFORM LOG SWITCH. So that these change vector are present in online redologfile

Step3 – Shut down the Database.

Step4 – Keep this file in donottouch folder so that if demo fails then you can sustain. Restore the backup file to “datafile” folder (overwrite).

Step5 – Start the DB, it will give error.

RECOVERY

Step5 – In mount phase issue the following

Sql> RECOVER  DATABASE;

OR

Sql> RECOVER DATAFILE ‘D:\PRAVAT\DATAFILE\DF1.DBF’ ;

Sql> ALTER DATABASE OPEN;

CONCLUSION

Here the recovery of Database is done by applying redo from redologfile and the datafile is brought to the status to synchronize with controlfile and redologfile. Here the backup redologfile or existing redologfile is used for recovery.

DEMO-3

Scenario

Backup Mode->OFFLINE . DB is in NOARCHIVELOG MODE. This demo is same as to previous one, with a difference; here recovery will be done by oracle by applying redo from more than one/all the redologfile groups.

In step2 perform LOGSWITCH, BUT DONOT OVERIDE ANY REDOLOGFILE I.E DONOT COMPLETE A CYCLE.

Then perform the rest of steps.

CONCLUSION

Here the recovery of Database is done by applying redo from MORE THAN ONE redologfile and the datafile is brought to the status to synchronize with controlfile and redologfile.

DEMO-4

Scenario

Backup Mode->OFFLINE . DB is in NOARCHIVELOG MODE. This demo is same as to previous one, with a difference; here recovery CAN NOT BE DONE, because the redolofiles has been overwritten by performing several log switch. AS THE DATABASE IS RUNNING IN NOARCHIVELOGMODE. Here WHOLE DB BACKUP IS REQUIRED, then only we can restore it and start the DB.  By restoring only the corrupted file we can not recover. The whole DB has to be restore.  So if the database is in NOARCHIVELOGMODE and the REDOLOGFILE CONTENTS has been overwritten, then recovery is simply not possible.

DEMO-5 (CANCEL BASED RECOVERY)

Can also be performed in ARCHIVELOGMODE.

Scenario

Backup Mode-> OFFLINE. DB is in NOARCHIVELOG MODE. The next startup of DB FAILS because REDOLOGFILE gets CORRUPTED OR LOST.  (Assuming all datafiles and controfiles are OK)

STEPS

Step1 – Delete all redologfiles and Startup the DB, will give error as no redolofiles are available.

Step2 – In mount mode issue the following SQL statement.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

Type - > CANCEL

SQL> ALTER DATABSE OPEN RESETLOGS; -- This statement will create redologfiles.

--Take the whole DB backup. The base for restore and recovery

DEMO-6 (CANCEL BASED RECOVERY)

Can also be performed in ARCHIVELOGMODE.

Scenario

 Backup Mode-> OFFLINE.  DB is in NOARCHIVELOG MODE. All DATAFILES and CONTROLFILE backup are available. But no REDOLOGFILE BACKUP AVAILABLE. The next startup of DB FAILS because REDOLOGFILE gets CORRUPTED OR LOST. Also assuming any DATAFILE or CONTROLFILE is corrupted or lost. We need to restore the backup datafiles and controlfiles those are consistent and synchronized to each other. Then we will perform a CANCEL based recovery using the restored controlfile.

Even if the current controlfile is OK , but if any datafile is corrupted along with corrupted redologfile, we can not use the current controlfile for CANCEL based recovery, Because we need to restore the datafile from backup, which is not synchronized with the current controlfile. And to synchronize datafile we need redologfile, which is again not available to apply. So in this scenario we need to restore BOTH the backup datafile and the backup controlfile to perform CANCEL based recovery. After recovery the DB will go to the backup controlfile status.

STEPS

Step1 – Open the DB, create a table T1 and store some data in it, in a user associated to a datafile.

Step2 – Shutdown the DB

Step3 – Take the backup of all datafile and controfiles.

Step4 – startup the DB, Create another table T2 and store some data in the same user.

Step5 – Shutdown the DB.

Note -> The backup file do not have any information about the table T2 but contain info about T1 table .

Step6 – Delete that datafile and all the redologfiles assuming they were corrupted files.

Step7 – Startup the DB, will give error.

Step8 – restore all the DATAFILES AND CONTROLFILES

Note -> Now  we need to perform a CANCEL based recovery, using this restored controlfile.

RECOVERY

Step9 – Issue the following SQL statement.

SQL>  RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

Type -> CANCEL

SQL > ALTER DATABASE OPEN RESTETLOGS;  -- This statement will create the redologfiles.

Now LOGIN with that user and check table T1 only exists, table T2 cannot be recovered

User Managed Backup & Recovery in ArchivelogMode DEMO

DEMO-1 (Recovering a datafile from ARCHIVED REDOLOGFILE AND REDOLOGFILE

Scenario

DB is in Archive log Mode. The next startup of the DB fails because of a corrupted datafile. The corrupted datafile will be recovered from archived redologfile and redologfile. Backup Mode->OFFLINE

STEPS

Step1 – Take the backup of a datafile, when DB is down.

Step2 – Startup the DB, Create a table T1 and store some data in it, for a user associated with this datafile.

Step3 – Issue the following statement SEVERAL TIMES to bring all change vectors into archived log file.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step4 – Again perform some DML but do not switch log file i.e. the change vectors (redo)           are present in ONLINE REDOLOGFILE.

Step5 – Shutdown the DB

Step6 – Delete that file and keep in DONOTTOUCH folder, assuming that the file gets corrupted.

Step7 – Startup the DB, will give error.

Step8 – Restore the file from backup, this file do not have any info about the table T1 and its data.

Step8 – In mount phase issue the following statement.

SQL>  RECOVER DATABASE;

OR

SQL> RECOVER DATAFILE ‘D:\PRAVAT\DATAFILE\... ”;

Checks -> The table along with the data available. These operation done  by oracle during recovery by collecting data from archived redologfile and online redologfile.

DEMO-2 (Open Database Recovery When the DB is initially OPEN)

Scenario

Backup Mode-> ONLINE. Both backup and recovery will be done ONLINE.  First we will take the BACKUP ONLINE, after that while the database is running it was found that some users are unable to perform operation and some of them are not able to login the database. Assuming one of the non system datafile has corrupted, we are going to perform ONLINE recovery of that datafile. (Note: media failure containing non system TS does not not affect system TS.)

Suppose a TS is available with the name TBS1 and a datafile is associated with it, with the name TBS1_DF1.DBF. A user is associated with this datafile of name USER1.

STEPS

Step1 – Login as a user USER1 and create a table T1.

Step2 – Now we are going to take backup of the tablespace TBS1.

SQL> ALTER TABLESPACE TBS1 BEGIN BACKUP;

è This command will freeze the header of the datafileTBS1_DF1.DBF. In this scenario also, the user USER1 associated to this datafile( tablespace TBS1) can perform DML operation.

è SQL> SELECT * FROM V$BACKUP;

è Check the backup file STATUS will be ACTIVE.

Step3 – Copy the datafile TBS1_DF1.DBF to the backup destination.

Step4 – Once backup is over issue  the following statement.

SQL> ALTER TABLESPACE TBS1 END BACKUP;

SQL> ALTER SYSTEM ARCHIVE   LOG CURRENT;

è Archive the un archived redo entries generated during backup periods, so that the redo required to recover the TS TBS1 is archived.

---- The BACKUP OF DATAFILE  WHILE DB IS Open IS OVER ------

Step5 – Create a table T2 in USER1 schema.

Step6 – Now assume the datafile TBS1_DF1.DBF gets corrupted.

RESTORE

Step7 – Now write the following statement to check if you need to take the tablespace TBS1 offline for restoring purpose.

SQL>        SELECT D.FILE#, D.NAME, D.STATUS, H.STATUS

                  FROM        V$BACKUP D, V$DATAFILE_HEADER H

                  WHERE D.FILE#=H.FILE#;

If it is not offline then write the following command.

SQL> ALTER TABLESPACE TBS1 OFFLINE;

Step8 – After making the TS TBS1 OFFLINE restore the datafile.

I.e. COPY AND PASTE

NOTE :-  Suppose U are not making the TS offline and suppose started and completed the restoring of datafile TBS1_DB1.DBF ( which is possible i.e you can overwrite a datafile even the TS is ONLINE), then you cannot recover the datafile, as it is ONLINE and currently used in the database. In that case you can not even shutdown the database consistently. To overcome this problem you need to shutdown the database with ABORT option. Then mount the database and perform recovery by applying all the archived redolog files which it will demand.

RECOVERY

STEP9 – To recover the datafile issue

SQL> RECOVER DATABASE;

OR

SQL> RECOVER DATAFILE ‘D:\PRAVAT\DATAFILE\TBS1_DF1.DBF’;

--- Give appropriate option, YOU WILL GET Media recovery complete.

Step 10 – Issue the below statement.

SQL> ALTER TABLESPACE TBS1 ONLINE;

Now query the USER1 schema and check that the table T2 is available.

DEMO-3 (HOW TO OVERCOME FAILURE DURING ONLINE TABLESPACE BACKUP)

Scenario

During online TS backup, the system may crash, power failure may occur. In that scenario the backup file may unusable, because OS copy operation was not completed due to failure. The file under backup is not synchronized with the database, because the header was freeze when backup starts. The database will not start because oracle assumes that the existing freezed data file has been restored from backup.

 STEPS

Step1 – Start the DB

Step2 – Issue the below statement to take backup.

SQL> ALTER TABLESPACE TBS1 BEGIN BACKUP;

- Use OS copy command to copy this file

-  During copy operation switch off the machine OR issue SHUTDOWN ABORT;

Step3 – Start the DB.

- Error

Step4 – In mount phase, issue the below statement.

SQL> ALTER TABLESPACE TBS1 END BACKUP;

                 

SQL> RECOVER DATABASE;

Step5 – Issue the below statement to open the DB

SQL> ALTER DATABASE OPEN;

DEMO-3 (OPEN DATABASE RECOVERY WHEN THE DATABASE INITIALLY CLOSED)

Scenario

The database initially closed. At the time of starting the database it is not opening because a datafile (non system non undo but can be sysaux) failure occurred.

You need recovery. But suppose you cannot afford the DB to down during this restore and recovery period (as it is a 24/7 database). So it is decided to start the DB partially without facilitating the users to connect to the DB, those are associated to the corrupted datafile.  After opening the database by making the datafile (note: TS associated to this datafile cannot be made offline here, as the TS is not opened) OFFLINE, later we can restore and recover this datafile, then make ONLINE. Then intimating the users associated to this datafile for connection to DB.

STEPS

Step1 – Take the cold backup of the file TBS1_F1.DBF.

Step2 – Start and open the database and perform some DML operation in a schema associated to this datafile.

Step3 – Shutdown the DB

Now assume the datafile TBS1_F1.DBF has been corrupted, so delete this file.

Step4 – Start the DB, it will show error

As we cannot afford to perform the restore and recovery operation at this stage, as it is a 24/7 DB, management decided to open the database without using this corrupted datafile, as a result the users associated to the TS having this datafile cannot connect to the DB till we recover it.

Collect information about this datafile from V$DATAFILE.

SQL> SELECT NAME, STATUS FROM V$DATAFILE;

Step5 – Issue the below statement to make the corrupted datafile offline.

SQL> ALTER DATABASE DATAFILE ‘D:\PRAVAT\DATAFILE\TBS1_DF1.DBF’ OFFLINE;

Verify the status again by querying V$DATAFILE.

(Note: You cannot OFFLINE the tablespace associated to this datafile, as the TS is not yet opened. Also remember this demo will not work for system and undo datafiles, because we cannot open the database by making the system or current undo tablespace offline)

Step6 – Issue the below statement to open the DB

SQL> ALTER DATABASE OPEN;

Tell the other users those are not associated to this datafile to connect to the DB. ( Check by connecting as a user associated to this DB also, and see what happens ?)

Step7 – Restore the datafile from backup.

Step8 – Recover the datafile by issuing the below statement.

SQL> RECOVER DATAFILE ‘D: \PRAVAT\DATAFILE\TBS1_DF1.DBF’;

Step9 – Make the datafile ONLINE

SQL> ALTER DATABASE DATAFILE ‘D: \PRAVAT\DATAFILE\TBS1_DF1.DBF’ ONLINE;

Intimate the user associated to this datafile to connect to the DB.

************************************************************************

DEMO-4 (RECOVERY OF A NON SYSTEM, NON UNDO DATAFILE(S) WITHOUT HAVING A BACKUP

Scenario

The backup of a datafile was never been taken. Suppose this datafile damaged, in this scenario we can re create this datafile if certain pre-requisite achieved.

-  All redo must be available since the datafile was created.

- This datafile should not associate to system or undo tablespace.

- The controlfile should not be re created after the creation of this datafile.

For demonstration purpose to achieve all these pre-requisite, create new TS and use it’s file.

STEPS

IF DATABASE IS IN CLOSED STATE

Step1 – Mount the DB

Step2 – Make the damaged file offline whose backup is not available.

SQL> ALTER DATABASE DATAFILE ‘D:\PRAVAT\DATAFILE\TBS1_F1.DBF’ OFFLINE;

Step3 – Open the DB.

SQL> ALTER DATABASE OPEN;

IF DATABASE IS IN OPEN STATE

Instead of issuing the above three steps issue

SQL> ALTER TABLESPACE TBS1 OFFLINE IMMEDIATE;

-          The immediate option must be issued to avoid immediate write of DBWn to a file which is not exists/corrupted.

Step4 – Query the V$RECOVER_FILE to get information.

SQL> SELECT * FROM V$RECOVER_FILE;

Step5 – Issue the following statement to re-create the file.

SQL> ALTER DATABASE CREATE DATAFILE ‘D: \PRAVAT\DATAFILE\TBS1_F1.DBF’ AS ‘D:\PRAVAT\DATAFILE\TBS1_F1.DBF’;

Step6 –recover the DB and make online.

SQL> RECOVER TABLESPACE TBS1;

SQL> ALTER TABLESPACE TBS1 ONLINE;

BACKUP & RECOVERY USING RMAN

RMAN> SHOW ALL;

RMAN> TARGET  /   LOG D:\PRAVAT\RMAN_LOG.LOG   APPEND;

--- Any operation performed in RMAN the report will generated in the file instead of displaying it in the screen.

DEMO-1 (FULL DATABASE BACKUP USING RMAN)

Scenario

The full DB backup is taken and the database is recovered using RMAN while DB is DOWN.

Steps

Step1 – If database not opened, mount it.

Step2 – Open another command prompt and issue the following,

C:\ > RMAN

RMAN> CONNECT   TARGET  /

RMAN> BACKUP FORMAT  ‘ D: \PRAVAT\BACKUP\BACK_%d%s%p.BAK ’ DATABASE;

  --- This command will create a backup set (file) with the name specified according to the format.

 ‘d’ --  Database Name

 ‘s’  --  Backup set number

 ‘P’ – Backup piece Number

You can give any name and extension.

OR

RMAN> BACKUP  DATABASE;

--- This command will create a file in Flash_recovery_area.

RMAN> EXIT;

C:\ > EXIT;

Step2 – Open the Database.

SQL> ALTER DATABSE OPEN;

Step3 – Perform some operation in DB (i.e. create a table in a schema and put data into it), and shutdown the database.

Step4 – Delete all the datafiles

RESTORE & RECOVERY

Step5 – Start the DB in mount phase

Step6 – Go to RMAN session

C:\ > RMAN TARGET /

RMAN> RESTORE DATABASE;

              ---- It will create all the datafiles.

RMAN> RECOVER DATABASE;

Step7 – Go to database window and issue the below statement to open the DB.

SQL> ALTER DATABASE OPEN;

Step8 – Login as a user and check, the operation done after taking the backup is available or not.

DEMO-2 (RMAN BACKUP OF A TABLESPACE AND RECOVERING IT WHILE DATABASE IS DOWN)

Scenario

Taking backup and recovering the database when DB is down using RMAN.

STEPS

Step1 – Mount the DB

Step2 – In another prompt connect to RMAN.

C:\ > RMAN CONNECT TARGET SYS/ORACLE

RMAN> BACKUP FORMAT ‘ D: \PRAVAT\BACKUP\TBS1_BACK1.BAK ’ TABLESPACE TBS1;

OR

RMAN> RUN

{

              ALLOCATE CHANNEL C1 TYPE DISK/SBT

              FORMAT ‘D:\PRAVAT\BACKUP\TBS1_BAC.BAK ’;

              BACKUP TABLESPACE TBS1;

}

OR

RMAN> BACKUP TABLESPACE TBS1;

  ----- This command will create backup in flash_recovery_area with a default naming convention.

Step3 – Go to database window and open the DB.

SQL> ALTER DATABASE OPEN;

Step4 – Create a table and put some data in that tablespace whose backup you have taken.

Step5 – Shutdown the database

Step6 – Cut the TBS1_DF1.DBF and put it in donottouch folder.

Step7 – Start the DB, it will give error. Start the DB in mount phase.

Step8 – Go to RMAN session.

C:\> RMAN CONNECT TARGET SYS/ORACLE

Step9 – Restore and recover the database

RMAN> RESTORE TABLESPACE TBS1;

RMAN> RECOVER TABLESPACE TBS1;

Step10 – Go to database window, open the DB.

SQL> ALTER DATABASE OPEN;

Connect to the user and check tables created after the backup is available.

DEMO-3 (BACKUP & RECOVERY OF A TABLESPACE  WHILE  DATABASE IS RUNNING, USING RMAN)

Scenario

Backup of a tablespace will be taken online. Later  we will recover this tablespace ONLINE assuming that it has corrupted.

STEPS

Step1 – The DB is open we need to take backup online. Open a command prompt and create RMAN session.

C :\> RMAN CONNECT TARGET /

RMAN>

RUN

{

ALLOCATE CHANNEL C1 TYPE DISK/SBT

FORMAT ‘D: \PRAVAT\BACKUP\USERS_BACK.BAK’;

BACKUP TABLESPACE USERS;

}

Step2 – Create a table in USERS tablespace and put some data in it, by connecting as a user.

Step3 – Now suppose the datafile associated to users tablespace gets corrupted.

Here in demo delete that file, but to delete the file you need to make the tablespace offline.

SQL> ALTER TABLESPACE USERS OFFLINE;

------- Delete the datafile of USERS tablespace.

Step3 – Go to RMAN session.

------- Take care that at the time of performing RESTORE and RECOVERY operation you must connect to SYS schema.

RMAN>

RUN

{

Sql “ALTER TABLESPACE USERS OFFLINE IMMEDIATE “; -- Specify here or above, but once.

ALLOCATE CHANNEL C1 TYPE DISK;

RESORE TABLESPACE USERS;

RECOVER TABLESPACE USERS;

Sql “ALTER TABLESPACE USERS ONLINE”;

}

Step4 – Check the tables you have created after taking the backup is available.

DEMO-4 (USING RMAN RELOCATE A TABLESPACE)

Scenario

This demo can be performed while the database is open or close. If the database is OPEN take the tablespace OFFLINE, whose datafile was corrupted. Then perform the RESTORE & RECOVERY.

If the database is closed then mount the database & perform the RESTORE & RECOVERY operation.

STEPS

Step1 – Open RMAN session.

Step2 – Backup the tablespace.

SQL>

RUN

{

ALLOCATE CHANNEL C1 TYPE DISK/SBT

FORMAT ‘D: \PRAVAT\BACKUP\USERS_BACK.BAK’;

BACKUP TABLESPACE USERS;

}

Step3 – Perform some operations, create some table and put some data in that tablespace.

Step4 – Assume that the datafile USERS1_DF1.DBF has been corrupted.

RECOVERY OF TABLESPACE BY RELOCATING TO A NEW LOCATION

Step5 – Connect to RMAN.

RMAN>

RUN

{

Sql “ALTER TABLESPACE TBS1 OFFLINE “;

SET NEWNAME   FOR  DATAFILE  ‘ D:\PRAVAT\DATAFILE\USER1_DF1.DBF ‘  TO  D:\PRAVAT\USER1_DF1.DBF ‘ ;

RESTORE (TABLESPACE USERS);

SWITCH   DATAFILE   3; ---- 3 is the fileno. Of this file, otherwise you can specify the complete path.

RECOVER   TABLESPACE   USERS;

Sql “ALTER TABLESPACE USERS ONLINE”;

}

Step6 – Check the table created after taking the backup is present.

INCOMPLETE RECOVERY

Situations Requiring Incomplete Recovery

•      Missing Archive

–     When a complete recovery operation fails because an archived log is lost.

•      Missing of redo log

–     Redo logs were not mirrored(archived) and you lost redo log before archive.

•      User error

–     An important table was dropped.

–     Invalid data was committed in a table.

•      Loss of control file

–     Current control file is lost and you don’t know the structure of database, but you have backup of an old control file.

Types of Incomplete Recovery

There are different types of incomplete recovery:

–     Time-based recovery

–     Cancel-based recovery

–     Change-based recovery(SCN)

–     Using backup of the control file

To performing incomplete recovery, you need:

•         A valid offline or online backup of all of the datafiles made before the recovery point

•         All archived logs from the backup until the specified time of recovery

USER MANAGED INCOMPLETE RECOVERY

Note:-  You need to restore & recover the DB while the DB is down.

DEMO1-Time based Recovery

Scenario :- The objective of this demo is an important table has been dropped, we need to recover this table by performing incomplete recovery.

Steps-  

Step1- Shutdown the database, take the backup of all the datafiles.

(To avoid confusion, Delete all the archived files from archived destination. These are not required as you have taken the recent cold backup.)

Step2- 

Start the DB and issue the following statements and commands

SQL> Alter session set NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

SQL> select * from v$log;

SQL> select * from v$logfile;

SQL> select * from v$log_history;

(Get the information about archived log files)

Step3- Perform some operation (create some tables, ie EMPLOYEES  ) and do switch logfile each time.

Step4- Check the current log sequence number and first_time column value from v$log.

(Note down the time, i.e. ‘2007-02-26 08:43:45’)

Step5- Drop the table “Employee” . And switch logfile several times.

Step6- Perform some operation (create some tables) and do log switching each time.

Step7- Shutdown the DB, restore the backup datafiles assuming the DB is corrupted.

Step8- Mount the database.

 Issue the below statement to recover the DB

SQL> RECOVER DATABASE UNTIL TIME ‘2007-02-26 08:43:45’;

              à Press ENTER KEY

              à Media recovery Complete

Step9- Issue the following statement to create new redologfile and log sequence number starting with 1.

SQL> ALTER DATABASE OPEN RESETLOGS;

Verify the table employee is exist.

Take the whole DB backup to have a reference point for future recovery.

             

DEMO2-Cancel Based Recovery

Scenario

The situation demands cancel based incomplete recovery because of an archived redo log file missing

Steps-  

Step1- Shutdown the database, Take whole DB backup.

Step2- Start the DB, Create a table, and perform log switches to cover this table and to generate its archive. Note this archive log sequence number.

Step3- Perform several log switches by doing some more operation. in each log switch, assuming that database operation is going on.

Step3- Drop that table and archive it. Note down this archive files log sequence number.

Step4- Keep on doing log switches several times by performing changes ( create tables) to the database.

Step5- Now you come to know an archive logfile is missing or corrupted.

              (Cut an archived file  of the position specified in bold letter log sequence number and paste in the donottouch folder)

Step6- Shutdown DB cleanly

Step7- Restore the datafiles from backup taken before the point of archived file loss.

Step8- Recover the DB upto that lost archived file.

SQL> RECOVER DATABASE UNTIL CANCEL;

Media recovery cancelled

SQL> ALTER DATABASE OPEN RESTLOGS;

(Check that tables created before the lost archive file only presents, other tables created after the lost archived file is missing)

DEMO3- Using Backup Controlfile During Recovery

Scenario- All datafiles and controlfile has been taken backup (datafile creation must be older than backup control file, so that the control file can have information about the data files). Now suppose a TS is dropped, then to recover that TS we will restore all these datafiles and control files and will perform recovery to just before the drop event of the TS, by applying archived and redolog file. Also this demo is useful if the current control file is corrupted or lost, using the old backup controlfile, recovery can be done to that instant which is recorded in the controlfile.

Steps-

Step1- Shutdown the DB, and take the whole DB backup (on Sunday)

Step2- Start and open the DB.

Step3- Create a controlfile from existing configuration of the DB. (On Tuesday)

SQL> ALTER DATABASE BACKUP CONTROLFILE TO ‘D:\CONTROL\CONTROL.BKP’;

This controlfile contain information of the database i.e. the tablespace TBS1 information.

Step4- Create a table in TBS1 tablespace and perform several log switches.

Step5- Today( Friday) at 11:45am

SQL> DROP TABLESPACE TBS1 INCLUDING CONTENTS AND DATAFILES;

Now at 12 Noon, You need to recover that tablespace TBS1.

Step6- Check some information

SQL> SELECT * FROM V$LOG;

Note the exact time of TS drop from alert log file.

Shutdown the DB.

(Backup the current control file for safer reason)

Step6- Restore all the datafiles from Sundays backup.

Step7- Restore Tuesday’s backup controlfile.

(Here Paste thrice depending upon number of controfiles present in that control file)

Step8- Start the DB, Error. Go to mount phase.

(Check any datafile is offline, if it is then make online, otherwise these OFFLINE files may unrecoverable after “recovery using controlfile”)

SQL> SELECT * FROM V$RECOVER_FILE;

If OFFLINE then

SQL> ALTER DATABASE DATAFILE 4 ONLINE;

Step9- Perform recovery

SQL> RECOVER DATABASE UNTIL TIME ‘2007-02-26 11:44:00’ USING BACKUP CONTROLFILE;

Step10- To synchronize the datafiles, control files, redologs and to start with new sequence number from 1 issue

SQL> ALTER DATABASE OPEN RESETLOGS;

(VERIFY THE TABLESPACE AVAILABILITY.)

DEMO4- Incomplete Recovery in case of loss of REDOLOG files( Group)

Scenario

REFER TO CANCEL BASED RECOVERY IN NOARCHIVELOG MODE DISCUSSED EARLIER.

RMAN INCOMPLETE RECOVERY

Be careful to set NLS_LANG & NLS_DATE_FORMAT parameter in each command prompt i.e. SYS and RMAN session by writing

C :\> SET NLS_DATE_FORMAT=YYYY-MM-DD HH24: MI: SS

(Note: Do not provide single quote (‘), it is required in linux only)

DEMO1 ( RMAN INCOMPLETE RECOVERY USING A SPECIFIC TIME)

Scenario

Suppose last night full backup was taken using RMAN.

Today at 12noon it was reported that an important table has been dropped (at 11:45am).

Now it is required to take back the DB to 11:44am status. (so that the table can be recovered)

Steps-

BACKUP

Step1- Open the database.

Step2- Create a RMAN session

Step3-

RMAN> BACKUP FORMAT ‘D:\PRAVAT\BACKUP\DB.DBF’ DATABASE;

OR

RMAN> RUN

            {

            ALLOCATE CHANNEL C1 TYPE DISK;

            BACKUP FORMAT ‘D:\PRAVAT\BACKUP\DB.DBF’ DATABASE;

            }

Step4- Create a table employee, put some data and perform several log switches.

Note down the time e.g.  ‘2007-08-26 11:05:00’

Step5- Drop employee table, note down the time e.g.  ‘2007-08-26 11:45:00’. Perform several log switches.

Step6- Shutdown the database.

Step7- Start the DB in mount phase. Also create a RMAN session.

RESTORE AND RECOVERY

RMAN> RUN

              {

              ALLOCATE CHANNEL C1 TYPE DISK;

              ALLOCATE CHANNEL C2 TYPE DISK;

              SET UNTIL TIME=’2007-08-26 23:44:00’;

              RESTORE DATABASE;

              RECOVER DATABASE;

              }

Converted to hh24 format and 1 minute before drop of the employee table

 

Step8- ALTER DATABASE OPEN RESETLOGS;

(Check the table employees is available)

DEMO2 (RMAN INCOMPLETE RECOVERY USING A LOG SEQUENCE NUMBER)

Scenario

Steps-

Step1- Take the RMAN full backup of the DATABASE

Step2- Check the current log sequence number.

SQL> SELECT * FROM V$LOG;

PERFORM SEVERAL LOG SWITCHES

Note the current log sequence number suppose 120.

Step3- DROP AN EXISTING TABLE.  Perform several log switches.

THE TASK IS TO RECOVER THE DROPPED TABLE.

RESTORE AND RECOVERY

Step4- Shutdown the database.

Step5- Start the DB in mount phase.

Step6- Create a RMAN session.

Step7- Issue the following

RMAN> RUN

            {

            SET UNTIL SEQUENCE 120 THREAD 1;

            RESTORE DATABASE;

            RECOVER DATABASE;

            }

(RMAN recovers up to 120-1=119 log sequence number.)

Check the table exists.