Simple Backup and Recovery Example / Cloning a database (Oracle10g)

I will discuss very simple but most crucial activity performed by every DBA as his / her routine. This scenario will be applied when your database is crashed and your physical machine is not available. Definitely you need to arrange same database on another machine with up-to-date database.

Following per-requisites must be met before going further with this exercise.

1.    Live database will be called as primary database here.

2.    Target database / server will be one where you want to recover your backed up database.

3.    Primary database is installed with autobackup. The full backup is performed every night.

4.    Same software version (Oracle 10g) is installed both the primary and target database / machine.

5.    Same directory structure is available on both the primary and target database.

6.    Database is running in archive log mode

Step 1: Backup the Live database

If your database is already configured to perform full backup every night then please proceed to next section.

Create a backup directory

$ ORACLE_HOME=/data/app/oracle/product/10.2.0/db_1;export ORACLE_HOME

$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE

$ PATH=$ORACLE_HOME/bin:$PATH;export PATH

$ ORACLE_SID='orcl';export ORACLE_SID

$ cd /data/app/oracle/product/10.2.0/db_1/dbs/
$ mkdir backup
$ chmod 775 backup

$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {

         allocate channel d01 device type disk
         format '/data/app/oracle/product/10.2.0/db_1/dbs/backup/%d_%t_%s_%U.dbf';
         set limit channel d01 kbytes 1024000;
         backup as compressed backupset database include current controlfile plus archivelog;
     }
using target database control file instead of recovery catalog
allocated channel: d01
channel d01: sid=136 devtype=DISK

Starting backup at 08-OCT-10
current log archived
channel d01: starting compressed archive log backupset
channel d01: specifying archive log(s) in backup set
..
..
..
tag=TAG20101008T221834 comment=NONE
channel d01: backup set complete, elapsed time: 00:00:02
Finished backup at 08-OCT-10
released channel: d01

RMAN>exit

Note: Assume this backup was taken at 01:00 A.M,


Before restoring backup to target server we assume that our database was crashed in following patters

·         Nightly full database backup taken at 01:00 A.M

·         Users performed some transactions on database at 11:00 A.M

Note : To prove the recovery upto last committed transation, please do the following

 on live database after step 1.

SQL>  Create table test_backup_tr (transaction_dttm date);

SQL> Insert into test_backup_tr values(sysdate);

SQL> Commit;

·         Database crashed at 11:35 A.M

Step 2: Recover database on another machine.

Once you got full backup of the database, you can restore it on any other machine which has suitable/ required hardware installed in hand. It is important that the target / recovery server has same version of oracle software installed alongwith the same directory structure (for simplicity).

The recovery process requires following files to be moved to Recovery Server

1.    A good copy of control file.

Run following query to find location(s) of control file(s).

SQL> select name from v$controlfile;

2.    Latest copy redo log files

Run following query to find location(s) of redo log files.

SQL> select member from v$logfile;

3.    Archive logs.

Run following statement at sql prompt to find location(s) of Archive Logs.

SQL> show parameter log_archive_dest

log_archive_dest                     string      /data/oradata/ORCLLIV_ARC

4.    Full database backup files

Remember we did mention backup location as following.

/data/app/oracle/product/10.2.0/db_1/dbs/backup/

5.    Latest copy of spfile

In 10g following is the default location of spfile

/data/app/oracle/product/10.2.0/db_1/dbs/

Please make sure you have good copies of all the above mentioned files to recover the  target / recovery server. If any of those files are missing and you think you have physically lost them, learn to maintain them on multiple places (multiplexing) and find alternative ways to recover database with old backups. In case of any missing files, this process will not guarantee the last committed transactions to be recovered.

Step 3: Restore database on target / recovery server

Setup environment for the database instance

$ ORACLE_HOME=/data/app/oracle/product/10.2.0/db_1;export ORACLE_HOME

$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE

$ PATH=$ORACLE_HOME/bin:$PATH;export PATH

$ ORACLE_SID='orcl';export ORACLE_SID

First shutdown recovery instance (if running) with abort option.

$ sqlplus / as sysdba

SQL> Shutdown abort;

SQL> exit

Now delete any existing datafile / controlfiles from database location

$ cd /data/oradata/controlfile/

$ rm *

$

$ cd /data/oradata/datafile/

$ rm *

$

Copy the recovery files taken from live database and move them to there same locations on recovery / target database.

Please note that directory structure may be different on your database, in this case please make necessary changes in the directories mentioned below

Move the new controlfile copy(s) to $ /data/oradata/controlfile/

Move the new redo logfiles to to $ /data/oradata/redo/

Move archive log files to /data/oradata/ORCLLIV_ARC

Move spfile to /data/app/oracle/product/10.2.0/db_1/dbs/

Move database backup piece files to /data/app/oracle/product/10.2.0/db_1/dbs/backup/

Connect to sqlplus to startup instance in mount position.

SQL> Startup mount;

SQL> exit

Finally, connect to rman target and recover the database.

$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run    {

restore database;

Recover database;

 }

RMAN>exit
$
$ sqlplus / as sysdba
SQL>Alter database open;


Check the last committed transactions to verify restore operation.

SQL> select * from test_backup_tr;

DATE_IN
---------
09-OCT-10
09-OCT-10
09-OCT-10

SQL>