Oracle Database 11g Data Guard Configuration on Linux

Basic Information of Machines


1. Primary DB:

Database Name: ORCL
Unique Name: Primary
Machine IP: 192.168.0.1
TNS Service Name: toStandby

2. Standby DB:

Database Name: ORCL
Unique Name: Standby
Machine IP: 192.168.0.2
TNS Service Name: toPrimary


Configuring For Remote Installation


To configure Secure Shell:

1. Create the public and private keys on all nodes:

[PrimaryDB]$ /usr/bin/ssh-keygen -t dsa
[StandbyDB]$ /usr/bin/ssh-keygen -t dsa

2. Concatenate id_dsa.pub for all nodes into the authorized_keys file on the first node:

[PrimaryDB]$ ssh 192.168.0.1 "cat ~/.ssh/id_dsa.pub" >> ~/.ssh/authorized_keys
[PrimaryDB]$ ssh 192.168.0.2 "cat ~/.ssh/id_dsa.pub" >> ~/.ssh/authorized_keys

3. Copy the authorized_keys file to the other nodes:

[PrimaryDB]$ scp ~/.ssh/authorized_keys 192.168.0.2:/home/oracle/.ssh/

4. Copy ~/.ssh folder on all nodes.


CONFIGURATION ON PRIMARY DATABASE

Step # 1.

SQL> Shutdown Immediate;
SQL> Startup Mount;
SQL> Alter Database ArchiveLog;

Step # 2.

Now Create a standby controlfile on the PRIMARY site.

SQL> Alter Database Create Standby Controlfile as ‘/opt/oracle/backup/standcontrol.ctl’;

Now copy this new created standby control file to the standby site where other database file like
datafiles, logfiles and control files are located. Rename this file to Control01.ctl, Control02.ctl, Control03.ctl.

Step # 3.

Create pfile from spfile on the primary database:

SQL> Create pfile from spfile=’/opt/oracle/OraDB11g/dbs/spfileORCL.ora’;

Step # 4.
Then make/add following settings in the initORCL.ora file on the PRIMARY Machine.

db_unique_name=’PRIMARY’
FAL_Client=’toPrimary’
FAL_Server=’toStandby’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=/opt/oracle/backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary’
Log_archive_dest_2=’Service=toStandby lgwr async VALID_FOR = (ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=DEFER
Service_names=’primary’
Standby_File_Management=’AUTO’

Step # 5
Create password file using ‘bash’.

$ orapwd file=/opt/oracle/OraDB11g/dbs/orapwORCL password=oracle entries=5 ignorecase=y force=y

Force option is used to replace an existing password file.

Step # 6.
Now startup the PRIMARY database to MOUNT stage.

SQL> Shutdown Immediate;
SQL> Startup Mount;
SQL> Alter Database Force Logging;

Step # 7
On the PRIMARY site, also create standby redo logfile for the Standby database. This Standby redo logfile will be used for Datagurad Observer later on. If you don’t want to use DG broker, then there is no need to created standby redo logfile.

SQL> Alter Database Add Standby Logfile (‘/opt/oracle/oradata/ORCL/StandbyRedo.log’) size 150m;

Step # 8

Now shutdown the primary database

SQL> Shutdown Immediate;

Copy all the datafiles and standby redo logfile from PRIMARY site to the same location on the STANDBY site. Then again startup the PRIMARY database to mount stage.

Step # 9
Now shutdown the primary database

SQL> Shutdown Immediate;

Create spFile from pFile.

SQL> Create spfile from pfile;

Restart the Primary database.

Step # 10

SQL> Startup;

Step # 11

Now on PRIMARY site create a service in TNSnames.ora file through which the PRIMARY site will be connected to Standby machine.

TOSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP) (HOST = 192.168.0.2) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Standby)
)
)

Step # 12
Register the Primary Database in the Listener.ora file. Then stop and start the listener.

> lsnrctl stop
> lsnrctl start

Step # 13

Query the DATABASE_ROLE column from V$DATABASE to new the role of Primary database. It should return ‘PRIMARY’.

SQL> select database_role from v$database;
DATABASE_ROLE
-------------------------
PRIMARY

Step # 14
Also check the connectivity from the SQL prompt.
SQL> connect sys/oracle@toStandby as sysdba;
Connected.

Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there.

CONFIGURATION ON STANDBY DATABASE

Step # 1

Check the mode of archiving by following command:

SQL> Archive Log List;

Step # 2

Then create pfile from spfile on the standby database:

SQL> Create pfile from spfile=’/opt/oracle/OraDB11g/dbs/spfileStandby.ora’;

Step # 3

Then make/add following settings in the initORCL.ora file on the STANDBY Machine.

db_unique_name=’STANDBY’
FAL_Client=’toStandby’
FAL_Server=’toPrimary’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=/opt/oracle/backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby’
Log_archive_dest_2=’Service=toprimary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names=’standby’
Standby_File_Management=’AUTO’

Step # 4

Create password file using ‘bash’.

$ orapwd file=/opt/oracle/OraDB11g/dbs/orapwORCL password=oracle entries=5 ignorecase=y force=y

Force option is used to replace an existing password file.

Step # 5

Now on STANDBY site create a service in TNSnames.ora file through which the STANDBY site will be connected to PRIMARY machine.

TOPRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP) (HOST = 192.168.0.1) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

Step # 6

Check the connectivity from the SQL prompt.

SQL> Connect sys/oracle@toPrimary as sysdba;
Connected.

Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there.

Step # 7
Register the Primary Database in the Listener.ora file. Then stop and start the listener.

> lsnrctl stop
> lsnrctl start

Step # 8

Now shutdown the primary database
SQL> Shutdown Immediate;

Create spFile from pFile.

SQL> Create spfile from pfile;



Restart the Primary database.




Step # 9



Now startup the STANDBY database to mount stage.



SQL> Startup NoMount;
SQL> Alter Database Mount Standby Database;



Enable Force logging.



SQL> Alter Database Force Logging;



Step # 10



Query the DATABASE_ROLE column from V$DATABASE to new the role of Standby database. It should return ‘PHYSICAL STANDBY’.



SQL> Select Database_role from v$Database;




DATABASE_ROLE
-------------------------
PHYSICAL STANDBY






LOG SHIPPING




On PRIMARY site enable log_archive_dest_state_2 to start shipping archive redo logs.



SQL> Alter system set Log_Archvie_Dest_State_2=ENABLE scope=both;



Check the sequence # and the archiving mode by executing following command.



SQL> Archive Log List;



Then switch the logfile.



SQL> Alter System Switch Logfile;



System Altered.



Now on the primary site check the status of Standby Archiving destination.



SQL> Select Status, Error from v$Archive_dest where dest_id=2;



The STATUS should return – VALID. If it return Error, then check the connectivity between the Primary and standby machines.






START PHYSICAL LOG APPLY SERVICE





On the STANDBY database execute the following command to start Managed Recover Process (MRP). This command is executed on mount stage.


SQL> Shutdown Immediate;
SQL> Startup Mount;


SQL> Alter Database Recover Managed Standby Database;

Database Altered.



By executing the above command the current session will become hanged because MRP is a foreground recovery process. It waits for the logs to come and apply them. To avoid this hanging, you can execute the following command with DISCONNECT option.



SQL> Alter Database Recover Managed Standby Database Disconnect;



Now the session will be available to you and MRP will work as a background process and apply the redo logs.



You can check whether the log is applied or not by querying v$archived_log



SQL> Select Name, Applied, Archived from v$Archived_Log;



This query will return the name of archived files and their status of being archived and applied.




Oracle 11g Data Guard Configuration Done.