Creating a Physical standby Database

A physical Standy is a database which acts like a backup server for the Production. Incase, if the Production Server gets crashed then we can open(Failover or Swithchover) the standby database and then start using it as a Production Database.

On the Source Machine:

1) Create a directory named standby.(to copy the parameter,password,control files and RMAN backup)


[oracle@ccc_dba_sys03 ~]$ cd $ORACLE_HOME/dbs
[oracle@ccc_dba_sys03 dbs]$ cp init<SID>.ora /standby/ Example : cp initabc.ora /standby/
[oracle@ccc_dba_sys03 dbs]$ cp orapw<SID> /standby/ cp orapwabc /standby/


2) Take a complete RMAN backup of the database and place it in standby directory

[oracle@ccc_dba_sys03 ~]$ rman target /
RMAN> backup as compressed backupset database format'/standby/%U' include current controlfile;

3) After completing RMAN backup we have to create a standby control file.


[oracle@ccc_dba_sys03 ~]sqlplus / as sysdba
SQL> alter database create standby controlfile as '/standby/standby.ctl';


On the Target System :


1) Install Linux on the system with proper mount points


2) Configure the /etc/yum.repos.d/abc.repo file and Install all the required rpm's.


(http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/install_overview.htm)

For 32-bit Linux


[abc.repo]
name=abc Standby Server Repository
baseurl=ftp://ip address/pub/ (For the ip, please consult your sys-admin)
enabled=1
gpgcheck=0


For 64-bit Linux


[abc.repo]
name=abc Standby Server Repository
baseurl=ftp://ip address/pub/oel5.5_64/ (For the ip, please consult your sys-admin)
enabled=1
gpgcheck=0


The following packages (or later versions) must be installed:


On Linux x86:

binutils-2.17.50.0.6-2.el5
compat-libstdc++-33-3.2.3-61
elfutils-libelf-0.125-3.el5
elfutils-libelf-devel-0.125
gcc-4.1.1-52
gcc-c++-4.1.1-52
glibc-2.5-12
glibc-common-2.5-12
glibc-devel-2.5-12
glibc-headers-2.5-12
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.1-52
libstdc++-4.1.1
libstdc++-devel-4.1.1-52.e15
make-3.81-1.1
numactl-devel-0.9.8.i386
sysstat-7.0.0

On Linux x86-64:


binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.1
gcc-c++-4.1.1
glibc-2.5-12
glibc-2.5-12 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5-12 (32 bit)
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libgcc-4.1.1
libgcc-4.1.1 (32 bit)
libstdc++-4.1.1
libstdc++-4.1.1 (32 bit)
libstdc++-devel 4.1.1
make-3.81
numactl-devel-0.9.8.x86_64
sysstat-7.0.0


3) Create the proper Group(dba) and Users(oracle) and give permissions to the user.


[root@ccc_dba_sys03 ~]# adduser oracle
[root@ccc_dba_sys03 ~]# passwd oracle
[root@ccc_dba_sys03 ~]# groupadd dba
[root@ccc_dba_sys03 ~]# usermod -g dba oracle
[root@ccc_dba_sys03 ~]# mkdir /oracle
[root@ccc_dba_sys03 ~]# chown -R oracle:dba /oracle
[root@ccc_dba_sys03 ~]# chmod -R 775 /oracle
[root@ccc_dba_sys03 ~]#


4) Login as the user(Oracle) created and Create the environment file(Example : abcstby.env)


[oracle@ccc_dba_sys03 ~]# vi abcstby.env


export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=abcstby
:wq (save and quit)


5) Run the enironment file(abcstby.env)


[oracle@ccc_dba_sys03 ~]# . abcstby.env


5) Install the Oracle software(No database) only by giving the proper paths for the installation of the s/w.


6) Now copy/move the standby directory from the source machine to target machine.


7) Now copy the parameter and password file to $ORACLE_HOME/dbs location and rename those file with the new oracle_sid value.
Ex: Standby] mv initabc.ora $ORACLE_HOME/dbs/initabcstby.ora
Standby] mv orapwabc $ORACLE_HOME/dbs orapwabcstby


8) Now edit the parameter file and change the paths/locations according to the standby server and also add one parameter called db_unique_name and specify the new oracle_sid value to this parameter. We will not change the db_name value.


Ex: db_unique_name='abcstby'


9) Create required directories as per parameter file and also copy the control file from standby directory to the specified location in parameter file.


10) Now connect to SQL prompt,
[oracle@ccc_dba_sys03 ~] sqlplus / as sysdba
SQL>


11) Startup the database in nomount stage and then alter the database to mount stage.


SQL>startup nomount


ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1336820 bytes
Variable Size 352324108 bytes
Database Buffers 1241513984 bytes
Redo Buffers 11833344 bytes


SQL>alter database mount;


Database altered.


SQL>


12) Now check few options using the following queries,


SQL> select open_mode from v$database;


open_mode
MOUNTED


SQL> select database_role from v$database;


database_role
PHYSICAL STANDBY


SQL>


13) Now check the location ie., on which path previously the database is storing its datafile and redo log file.


13-a) SQL> select name from v$datafile; Ex: /oradata/abc/datafiles/system.dbf


13-b) SQL> select member from v$logfile; Ex: /oradata/abc/redologs/redo01.log


14) Now shutdown the database.


SQL> shut immediate


Database closed.
Database dismounted.
ORACLE instance shut down.


15) Now open the parameter file to change the locations of datafiles and redo log files. Add two parameters in parameter file.

db_file_name_convert='Primary database datafiles path which is show step 13-a','standby database datafile path ie where we want to store the datafiles'


log_file_name_convert='Primary database log files path which is show step 13-b','standby database redo log files path ie where we want to store the redo log files'


Ex: db_file_name_convert='/oradata/abc/datafiles/','/data/abcstby/datafiles/'
log_file_name_convert='/oradata/abc/redologs/','/data/abcstby/redologs/'


Now save and quit the file.

16) Now start the database in mount stage and check for the new locations give in parameter file.


SQL> startup mount
    

ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1336820 bytes
Variable Size 352324108 bytes
Database Buffers 1241513984 bytes
Redo Buffers 11833344 bytes
Database mounted.


SQL> select name from v$datafile; Ex: /data/abcstby/datafiles/system.dbf


SQL> select member from v$logfile; Ex: /data/abcstby/redologs/redo01.log


Now both the above queries should show the standby db path instead of primary db path.

17) Now exit from SQL prompt(do not shutdown the database) and connect to RMAN prompt.


SQL> exit


[oracle@ccc_dba_sys03 ~]rman target /


RMAN>


18) Now we have to catalog all the RMAN backup pieces of primary database which we have copied from primary db to standby severs standby directory.


RMAN> catalog backuppiece 'path were we have stored that backup pieces temporarily in standby server';


Ex : RMAN> catalog backuppiece '/data/standby/UE45fse2/';


To catalog a complete folder, then use the following command


RMAN> catalog start with '/path of the folder where the rman pieces are loacted/';


Ex : RMAN> catalog start with '/data/standby/';


19) Now list all the backup sets available,


RMAN> list backupset summary;


20) Now crosscheck the backup sets to list out the expired backups.
    

RMAN> crosscheck backupset;


21) Now delete the expired backup sets.


RMAN> delete expired backup;


22) Now we have to restore the database.


RMAN>restore database;


23) After restoring the database, come out of RMAN prompt and connect to SQL prompt and take the database into recover mode


RMAN> exit


[oracle@ccc_dba_sys03 ~] sqlplus / as sysdba


SQL> alter database recover managed standby database disconnect from session;


Database altered.


SQL>


24) Now we have to check the log sequence number.


SQL> select sequence#,process,status,block# from v$managed_standby;


In the output it will show wait_for_log and we have to copy the archives from that sequence number to the present running log sequence number in primary db to standby server.


25) Copy the pending archives from primary server to standby server and paste it in the archive directory which we have specified in parameter file.


26) Now we have to apply the new archives using RMAN,


RMAN> catalog start with 'path where we have copied the new archives';


And give yes to start cataloging


27) After applying the pending archives we have to again check the new log sequence number.


SQL> select sequence#,process,status,block# from v$managed_standby;


Now it will show apply_log and after applying all logs it will show wait_for_log.


And now we have to check the applied log sequence number with the primary db log sequence number. And both the number must be same.


28) Everyday we have to copy the new archives from the primary server to standby sever by checking the log sequence number in standby server and primary server.