Skip to main content

An Alternative Method for Oracle Physical Standby

Physical Standby Server can be used to recover data from disaster is one of the best methods. But the installation and management may be a little bit difficult. There is an another way.

First i should specify properties of the primary and recovery databases.
Primary server name: primsrv
Instance name: orcl
Primary server: ORACLE_BASE: /u01/app/oracle
Primary server: ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
Primary server datafile location: /database/oradata/orcl
Primary server redolog file location: /database/oradata/orcl
Primary server controlfile location: /database/oradata/orcl
Primary server backup location (will be mounted by recovery server via nfs with the same folder name): /backup/rman_bck
Primary server archivelog destination (will be mounted by recovery server via nfs with the same folder name): /fra/arch/orcl
Recovery server name: recovsrv
And other properties of the recovery server database will be same of the primary server.

Now i can install recovery server.
First i should install an operating system same as the primary server and prepare folder structure where the datafiles, redologs and controlfiles exist. In this example it is /database/oradata/orcl folder. Then i should install same version of oracle software only as the primary server has. After installing oracle software i should create database instance exactly same as the primary server’s instance using dbca tool. I should be careful for specifying the right locations for datafiles, redologs and controlfiles. I also should be careful for specifying the right parameters of the database like character set, log_archive_dest_1, log_archive_format.

Now i have two oracle databases have the same structures. But how can i recover primary server to the recovery server? First i need to provide recovery server to see primary server’s /backup/rman_bck and /fra/arch/orcl folders. I can do it using nfs. With nfs sharing, recovery server can see primary server’s folders.

Recovery server’s /etc/fstab configuration should be look like this.
primsrv:/backup              /backup               nfs …..
primsrv:/fra/arch/orcl      /fra/arch/orcl    nfs ……

At recovery server shutdown database and remove datafiles and controlfiles.
recovsrv SQL> shutdown immediate
recovsrv #> cd /database/oradata/orcl
recovsrv #> rm –f *.dbf
recovsrv #> rm –f *.ctl

At primary server take backup of controlfile and initialization file then send it to the recovery server
primsrv SQL> alter database backup controlfile to '/home/oracle/primorcl.ctl';
primsrv SQL> create pfile='/home/oracle/initprim.ora' from spfile;
primsrv #> scp /home/oracle/primsrv.ctl oracle@recovsrv:/database/oradata/orcl
primsrv #> scp /home/oracle/initprim.ora oracle@recovsrv:/home/oracle

At recovery server make copies of the backup controlfile and create spfile from pfile. Then startup database at mount state, restore and recover database.
recovsrv #> cd /database/oradata/orcl
recovsrv #> mv primorcl.ctl control01.ctl
recovsrv #> cp control01.ctl control02.ctl
recovsrv #> cp control01.ctl control03.ctl
recovsrv SQL> create spfile from pfile='/home/oracle/initprim.ora';
recovsrv SQL> startup mount
recovsrv RMAN> restore database
recovsrv SQL> recover database using backup controlfile until cancel

There is a parameter named archive_lag_target used to switch archivelog files periodically. I can set this parameter to apply most recent changes in the primary server to recovery server. At primary server set archive_lag_target parameter to 900 second.
primsrv SQL> alter system set archive_lag_target=900 scope both;

So I can setup a cron job that executes recovery process every 15 minutes. At recovery server
recovsrv #> crontab -l
*/15 * * * * /home/oracle/scripts/

recovsrv #> more /home/oracle/scripts/
export PATH
sqlplus "/ as sysdba" @/home/oracle/scripts/recov.sql

recovsrv #> more /home/oracle/scripts/recov.sql
set autorecovery on
spool /home/oracle/scripts/recov.lst
recover database using backup controlfile until cancel;

At recovery server when cron job runs, i can tail output
recovsrv #> tail -f /home/oracle/scripts/recov.lst
tail: recov.lst: file truncated

ORA-00279: change 137823348 generated at 12/23/2010 18:02:56 needed for thread
ORA-00289: suggestion : /fra/arch/orcl/arch_728088008_1_8238.arc
ORA-00280: change 137823348 for thread 1 is in sequence #8238
ORA-00278: log file ‘/fra/arch/orcl/arch_728088008_1_8237.arc’ no longer
needed for this recovery

ORA-00279: change 137828288 generated at 12/23/2010 18:17:57 needed for thread
ORA-00289: suggestion : /fra/arch/orcl/arch_728088008_1_8239.arc
ORA-00280: change 137828288 for thread 1 is in sequence #8239
ORA-00278: log file ‘/fra/arch/orcl/arch_728088008_1_8238.arc’ no longer
needed for this recovery

ORA-00308: cannot open archived log ‘/fra/arch/orcl/arch_728088008_1_8239.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

As you see the output, archivelogs will be applied every 15 minutes on the recovery server.

Important note: If i would like to open recovery server for transactions, i have to execute alter database open resetlogs command. In case of a failure, i have a ready database for business continuity.


Popular posts from this blog