Oracle 11 g data guard configuration for windows server

We will discuss here oracle 11g data guard configuration for windows server. Primary and Standby server information is given below

Primary Server:

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

IP: 192.168.1.100

db_name=db11g

db_unique_name=db11g

Stand by Server:

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

IP: 192.168.1.103

db_name=db11g

db_unique_name=db11g_stby

Primary Server Setup:

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

We already installed RDBMS software with one database in primary server.

First check the primary database is in archivelog mode.

SQL>select log_mode from v$database;

if database is in noarchivelog mode, performe below step to switch database in archive mode

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL> alter database open;

Check database is force logging mode using below command

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

if database is not force logging mode change it using below command

SQL>ALTER DATABASE FORCE LOGGING;

Verify DB_NAME and DB_UNIQUE_NAME using below command and both query output will be db11g

SQL> show parameter db_name

SQL> show parameter db_unique_name

Now change archive log configuration using below command here db11g_stby is Standby database unique name

SQL> alter system set log_archive_config=’DG_CONFIG=(db11g,db11g_stby)’; 

Note: both server db_name will be same that is db11g

Set suitable remote archive log destinations.Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

We need to set the following parameters.

SQL> ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

SERVICE SETUP

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

both server (Primary & Standby) tns file have same entry that are given below

DB11G =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = db11g)

  )

 )

DB11G_STBY =

 (DESCRIPTION =

  (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))

  )

  (CONNECT_DATA =

   (SERVICE_NAME = db11g_stby)

Backup Primary Database using rman 

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

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE using following command

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\db11g_stby.ctl';

SQL>CREATE PFILE='D:\initDB11G_stby.ora' FROM SPFILE;

Standby Server Configuration

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

Standby server installed RDBMS software only and create listener and tns file conatain same as primary server

copy RMAN backup, control file and pfile to Standby server

Amend following paramiter to pfile for the standby database.

*.db_unique_name='DB11G_STBY'

*.fal_server='DB11G'

*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

Standby server create same direcotry as primary server and copy files from primary server to standby server. Server's directory are same which are given below

Control file: D:\app\Masud\oradata\db11g 

RMAN Backup: D:\app\Masud\product\11.2.0\dbhome_1\database

Tns file: D:\app\Masud\product\11.2.0\dbhome_1\NETWORK\ADMIN

pfile: D:\app\Masud\product\11.2.0\dbhome_1\dbs

Restore Backup in Standby Server

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

START Listener

SQL> sqlplus / as sysdba

SQL>CREATE spfile from pfile='D:\app\Masud\product\11.2.0\dbhome_1\dbs\initDB11G_stby.ora';

Restore backup using rman

$rman target=/

RMAN> STARTUP MOUNT;

RMAN> RESTORE DATABASE;

Create Redo Logs

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

Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo01.log') SIZE 50M;

SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo02.log') SIZE 50M;

SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo03.log') SIZE 50M;

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). 

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('D:\app\Masud\oradata\db11g\standby_redo01.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('D:\app\Masud\oradata\db11g\standby_redo02.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('D:\app\Masud\oradata\db11g\standby_redo03.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('D:\app\Masud\oradata\db11g\standby_redo04.log') SIZE 50M;

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo01.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo02.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo03.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo04.log') SIZE 50M;

Start Apply Process

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

# Background redo apply. Control is returned to the session once the apply process is started.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

check the sequence detail using below command to primary server

SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Now go to the standby server and check the applied log sequence

SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Steps to configure read only STANDBY

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

SQL>Shu immediate

SQL>startup mount;

SQL>alter database open read only;