Oracle Physical Data guard.

Here I have successfully implemented Oracle Data guard.Followings are the steps which  carried out.

Primary  Server  (Server1) is  OEL6.5.swt.lk
Stand By  Server (Server2 )is OEL6.5-CLONE.swt.lk

Primary  and Stand By Servers  Both  have installed  Oracle 11.2.0.4  and Primary Server only has the DB only.Other Server does not have  a DB.  

Primary Server  DB name is  TEST                       Stand By Server DB name is TEST
Primary Server  DB_UNIQUE NAME  TEST      Stand By Server DB_UNIQUE_NAMETEST_STBY


Primary Server Configuration

Step 1

Check the log mode.It should be in ARCHIVELOG Mode.


SELECT log_mode FROM v$database;

If not in ARCHIVELOG mode  follow this steps.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


Step 2 

Enable force logging  :- If any change in that database happen It will go into the redo log and be usable for recovery.

                ALTER DATABASE FORCE LOGGING;

Step 3

Check out db_name and db_unique_name in  Primary Server

show parameter db_name
show parameter db_unique_name

Step 4

log_archive_config enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs. Also specifies the service provider names (sp_name) for each database in the Data Guard configuration.

DG_CONFIG  values  could be the db_unique name.It could not be more than nine databases.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,TEST_STBY)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=test_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_STBY';


ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

%t:- Thread name
%s- Log Sequence Number
%r- Reset Log Id
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

Set Maximum number of active ARCH processes.

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

Check weather oracle looks for a password file by setting REMOTE_LOGIN_PASSWORDFILE and value is EXCLUSIVE mean this password file can be used by only one database.and contains all SYS and Users password.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

Setting Up Target DB Server
FAL-->Fetch Archive Log

ALTER SYSTEM SET FAL_SERVER=TEST_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Create tnsnames.ora file in Both Primary and StandBy Servers.

$ORACLE_HOME/network/admin/tnsnames.ora

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.5.swt.lk)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

TEST_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.5-clone.swt.lk)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_STBY)
    )
  )


I planned to do this using moving primary data files to Stand By server. For that you needed to get an RMAN backup.

OS>  rman target=/

I backed up all archive log files plus  data files both using following command.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create a control file for stand by database.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/test_stby.ctl';

Create pfile for the stand by database.

CREATE PFILE='/tmp/initTEST_stby.ora' FROM SPFILE;

Amend the pfile entries  relevant to stand by database.

*.db_unique_name='TEST_STBY'
*.fal_server='TEST'
*.log_archive_dest_2='SERVICE=test ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'


Stand By Server Configuration

#Creating required folders
mkdir -p /u01/app/oracle/oradata/TEST
mkdir -p /u01/app/oracle/fast_recovery_area/TEST
mkdir -p /u01/app/oracle/admin/TEST/adump


#Copy  Standby controlfile to all locations.
 scp oracle@oel6.5.swt.lk:/tmp/test_stby.ctl /u01/app/oracle/oradata/TEST/control01.ctl
 cp /u01/app/oracle/oradata/TEST/control01.ctl /u01/app/oracle/fast_recovery_area/TEST/control02.ctl


# Copy Archivelogs and backups
 scp -r oracle@oel6.5.swt.lk:/u01/app/oracle/fast_recovery_area/TEST/archivelog /u01/app/oracle/fast_recovery_area/TEST
 scp -r oracle@oel6.5.swt.lk:/u01/app/oracle/fast_recovery_area/TEST/backupset /u01/app/oracle/fast_recovery_area/TEST


# Copy Parameter file.
scp oracle@oel6.5.swt.lk:/tmp/initTEST_stby.ora /tmp/initTEST_stby.ora

 # Remote login password file.
scp oracle@oel6.5.swt.lk:$ORACLE_HOME/dbs/orapwTEST $ORACLE_HOME/dbs

#Add Listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TEST)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
--

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

#Start Listener
lsnrctl start

#Set the ORACLE_SID
export ORACLE_SID=TEST
sqlplus / as sysdba

#Create  the spfile from the location

SQL> CREATE SPFILE FROM PFILE='/tmp/initTEST_stby.ora';

#Set the ORACLE_SID
export ORACLE_SID=TEST
rman target=/

#Restore the Database back from
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

#Put  STANDBY_FILE_MANAGEMENT to MANUAL
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

#Add RedoLog Files groups on stand by server.
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo01.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo02.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo03.log') SIZE 10M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

# Make sure on BOTH Nodes you need to create standby LOGFILES

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo01.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo02.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo03.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo04.log') SIZE 10M;


#Redo Log Apply process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#Testing  Log Apply Process

Primary Server

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time FROM   v$archived_log ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Standby Server

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM   v$archived_log ORDER BY sequence#;


#Ultimatly Enter for automatic one  on standby server

#cancel out of the current  managed recovery
#Alter database recover managed standby database cancel;

# Real time Log apply with no time delay.

Alter database recover managed standby database using  current logfile disconnect



---------------------
Switch Over to Stand By 

For current primary database
# Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

SHUTDOWN IMMEDIATE;

# Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

For current stand by

 #Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

#Shutdown standby database
SHUTDOWN IMMEDIATE;

#Open old standby database as primary
STARTUP;

Now you are done with the SwithOver

 #Check the switch over status
 select switchover_status from v$database ;