Configure Standby Database / Data Guard Step By Step

1. Install Oracle database with same structure on two machines.

if you are installing on vmware or virtual box then prepare first machine and then clone it.

In this post machine names are machine01 (Primary DB)  and machine02 (Secondary DB).

2.  Make sure database structure  and SID is same on both machines this will help you to create and manage Data gaurd easily.
 
create SID with same name like alidb for both machines.


3.  On first machine  open $0ORACLE_HOME/network/admin/tnsnames.or file
and make two copies of your service entry and change the copied service entries to mdb01 and mdb02 as given below.

mdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )


mdb02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )

4. Now perform same on second machine  open $ORACLE_HOME/network/admin/tnsnames.or file
and make two copies of your service entry and change the copied service entries to mdb01 and mdb02 as given below.

mdb02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )



mdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = machine01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alidb)
    )
  )



5. on both machines in /etc/hosts register both systems so that both machine can access each other .
     and test connectivity of both service created earlier from both machines.
Example /etc/hosts entry on both machines

192.168.0.143    machine01   machine01.com
192.168.0.144    machine02   machine02.com



6. create standby Redolog files on primary database with formula Total Existing +1. i.e I have 3 so I need to create 4 new files as given below.

Query to check existing log files and there path.

sqlplus / as sysdba

select member from v$logfile;

===========================
Add Standby Redolog file
===========================
alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo04.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo05.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo06.log' size 50m;

alter database add standby logfile '$ORACLE_HOME/oradata/alidb/redo07.log' size 50m;

select member from v$logfile;


7. create pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora' from spfile;

8. Now open $ORACLE_BASE/admin/alidb/pfile/init.ora on your machine in text editor and
   Paste following parameters in above pfile opened in last step

###########################################
#Data Guard Parameter for db1 in pfile
###########################################
DB_UNIQUE_NAME=mdb01
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mdb01,mdb02)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/d01/apps/flash_recovery_area/arch_alidb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=mdb01'
LOG_ARCHIVE_DEST_2=
 'SERVICE=mdb02 LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=mdb02'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=Arch_%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=mdb02
FAL_CLIENT=mdb01
STANDBY_FILE_MANAGEMENT=AUTO



9. Create following arch_alidb directory in flash_recovery_area on your both machines .

mkdir   /d01/apps/flash_recovery_area/arch_alidb


10. Now mount primary DB  with new pfile create above and perform following on primary database.

sqlplus  / as sysdba


SQL> shutdown immediate;

sqlplus  / as sysdba

startup mount pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora'


Switch to archivelog mode 

archive log list;

alter database archivelog;

archive log list;


On Primary db create control file that would be used at secondary db.

alter database create standby controlfile as '$ORACLE_HOME/oradata/alidb/control_std.ctl';


Create spfile on primary database.

create spfile from pfile= '$ORACLE_BASE/admin/alidb/pfile/init.ora';


11. Shutdown  Secondary database machines.

sqlplus / as sysdba
shutdown immediate;


12 Now on machine02 and perform following

Copy control file from primary db $ORACLE_HOME/oradata/alidb/control_std.ctl ,  password file and all datafiles redologs except control files (control01,control02) to a folder and move this folder to standby db server
then rename $ORACLE_HOME/oradata/alidb/ to $ORACLE_HOME/oradata/alidb_old db  folder on standby server and  create new folder $ORACLE_HOME/oradata/alidb then move these files there.

Also rename existing control files on standby database and replace control_sdt.clt on the same location with old name.

i.e if control01.clt rename it control01_old.clt .
and paste control_std.ctl in this folder and rename it control01.ctl.

=====================================


13. add following parameters to pfile on standby
you can get pfile from Primary db '$ORACLE_BASE/admin/alidb/pfile/init.ora';

vi $ORACLE_BASE/admin/alidb/pfile/init.ora
change following line as given below



remove earlier added parameters starting from the below line as following part that we add
###########################################
#Data Guard Parameter for db1 in pfile
###########################################

Add following in the '$ORACLE_BASE/admin/alidb/pfile/init.ora' on Secondary db.


###########################################
#Data Guard Parameter db2
###########################################
DB_UNIQUE_NAME=mdb02
LOG_ARCHIVE_CONFIG='DG_CONFIG=(mdb01,mdb02)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/d01/apps/flash_recovery_area/arch_alidb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=mdb02'
LOG_ARCHIVE_DEST_2=
 'SERVICE=mdb01 LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=mDB01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=Arch%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=mDB01
FAL_CLIENT=mDB02
STANDBY_FILE_MANAGEMENT=AUTO

#======================

14. Mount secondary db using following command.

sqlplus / as sysdba

startup mount pfile='$ORACLE_BASE/admin/alidb/pfile/init.ora'

check archive log status with following command is should be enabled.

archive log list


create spfile with this command.

create spfile from pfile= '$ORACLE_BASE/admin/alidb/pfile/init.ora';

15. Open primary DB

alter database open;


16. on standby execute following For Real Time apply.
-------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;


To check the status of standby database issue the following command for applied archived logs.

select max(sequence#) from v$archived_log where applied='YES';

select sequence#,applied from v$archived_log order by 1;

===================== Stand by created ======================-

YuM on RHEL 7

RHEL7: Create a local repository.

Prerequisites

Configuration Procedure

Create a directory for the RHEL 7 distribution:

# mkdir -p /var/www/html/rhel7

Note: By creating the local repository in the /var/www/html directory, you will be ready to make it available on the network after installing an Apache webserver at a later stage.

Mount the previously downloaded Rhel 7 distribution  (called here rhel-workstation-7.1-x86_64-dvd.iso) and copy it locally:

# mount -o loop rhel-workstation-7.1-x86_64-dvd.iso /mnt
# cd /mnt
# tar cvf - . | (cd /var/www/html/rhel7; tar xvf -)
# cd /; umount /mnt

Go to the directory where the repository configuration is:

# cd /etc/yum.repo.d

Create the rhel7.repo file and type:

[rhel7]
name=rhel7
baseurl=file:///var/www/html/rhel7/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-rhel-7

Clean up the yum configuration and check it:

# yum clean all
# yum repolist all

If you regularly add new packages in your local repository, don’t forget to update it. To do that, install the createrepo package and execute it with the repository path as argument:

# yum install -y createrepo
# createrepo /var/www/html/rhel7
# yum clean all
# yum repolist all