Oracle 11g Data Guard Broker Configuration

Oracle 11g Data Guard Broker Configuration

Oracle Server - Enterprise Edition - Version 11.2.0.2

Goal

Step By Step How to create Dataguard Broker Configuration.

Fix

Step 1. Start broker in both of primary and standby (in all instances in case of RAC):

SQL>ALTER SYSTEM SET DG_BROKER_START=TRUE;

Step 2. Connect to DGMGRL on primary: (from instance one in case of RAC) 

$dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> CONNECT sys/<password>;
Connected.

Step 3. Create configuration on primary:

DGMGRL> CREATE CONFIGURATION 'MY_CONFIGURATION' AS PRIMARY DATABASE IS 'PRIMARY' CONNECT IDENTIFIER IS primary;

Configuration "MY_CONFIGURATION" created with primary database "PRIMARY"

Step 4. Add standby in the configuration:

DGMGRL> ADD DATABASE 'STANDBY' AS CONNECT IDENTIFIER IS STANDBY MAINTAINED AS PHYSICAL;

Database "STANDBY" added

Step 5. Enable Configuration:

DGMGRL> ENABLE CONFIGURATION;
Enabled.

Use the SHOW command to verify that the configuration and its databases were successfully enabled and brought online:

DGMGRL> SHOW CONFIGURATION;

Configuration
Name: MY_CONFIGURATION

Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED

Databases:
PRIMARY - Primary database
STANDBY - Physical standby database

Current status for "MY_CONFIGURATION"
SUCCESS

Oracle 11g Active Data Guard using (RMAN)

Oracle Database 11g Active Standby Database Creation using RMAN

PRIMARY SITE: (Information)

Machine IP: 192.168.1.1

Machine Name: Linux1

Database name (db_name): db

Database Unique Name (db_unique_name): primary

TNS Service Name: standby (Through this service, the primary machine will be connected to STANDBY machine)

STANDBY SITE: (Information)

Machine IP: 192.168.1.2

Machine Name: Linux2

Database name (db_name): db

Database Unique Name (db_unique_name): standby

TNS Service Name: primary (Through this service, the standby machine will be connected to PRIMARY machine)

CONFIGURATION ON PRIMARY SITE: -

Step1:

Create pfile from spfile on the primary database:

SQL>create pfile= ‘/backup/mypfile.ora’ from spfile;

Step2:

Add following settings in the pfile (/backup/mypfile.ora) on the PRIMARY Machine.

DB_UNIQUE_NAME='primary'

FAL_Client='primary'

FAL_Server='standby'

LOG_ARCHIVE_CONFIG=

 'DG_CONFIG= (primary, standby)'

standby_file_management=auto

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=

 'LOCATION=USE_DB_RECOVERY_FILE_DEST

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_2=

 'SERVICE=standby LGWR ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

Service_names='primary'

Step3:

Create password file using ‘Shell prompt’.

[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl’ password=oracle entries=5 force=y ignorecase=y

Step4:

SQL>startup mount;

Step5:

Take the Primary database to Archive Log Mode.

SQL>alter database archiveLog;

Enable Force Logging.

SQL>alter database force logging;

Now shutdown the database:

SQL>shutdown immediate;
Now startup the database:
SQL>startup;

Step6:

Now add standby Redo Log group to the PRIMARY site.

SQL>Alter database add standby logfile (‘/u01/app/oracle/oradata/ORCL/standbyredo.log’) size 100m;

Step7:

Startup the database from pfile.
SQL>startup pfile='/backup/mypfile.ora';
Create spfile from pfile to startup the database with spfile.

It is necessary for primary database to use spfile in a dataguard environment.

SQL>Create spfile from pfile=‘/backup/mypfile.ora’;

Now bounce the DB to take affect of new parameter from pfile.

Step8:

Restart the database

SQL> shut immediate;

SQL> startup;

Step9:

Configure tnsnames.ora on both servers to hold entries for both databases.

Now on PRIMARY site create following services in TNSnames.ora file. This file will be use on standby site also.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

(SERVER = DEDICATED)

      (SID = db)

    )

  )

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

(SERVER = DEDICATED)

      (SID = db)

    )

  )

Note: This tnsnames.ora will be used on both side of dataguard env.

Step10:

Listener.ora for Primary Site.

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = db)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = db)

    )

  )

LISTENER =

  (DESCRIPTION =

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

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step11:

Listener.ora for Standby Site.

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = db)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = db)

    )

  )

LISTENER =

  (DESCRIPTION =

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

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step12:

Also configure net service name for primary database on primary site and standby service name on standby site.

[oracle@linux1~]$ netmgr

[oracle@linux1~]$ Lsnrctl status

[oracle@linux1~]$ Lsnrctl stop

[oracle@linux1~]$ Lsnrctl start

CONFIGURATION ON STANDBY SITE:-

Step1:

Create following directories on target standby

mkdir -p /u01/app/oracle/flash_recovery_area/db/archivelog
mkdir -p /u01/app/oracle/flash_recovery_area/db/onlinelog
mkdir -p /u01/app/oracle/flash_recovery_area/db/flashback
mkdir -p /u01/app/oracle/admin/db/dpdump
mkdir -p /u01/app/oracle/admin/db/adump

mkdir -p /u01/app/oracle/admin/db/scripts

mkdir -p /u01/app/oracle/admin/db/pfile

mkdir -p /u01/app/oracle/cfgtoollogs/oraclce/db

mkdir -p /u01/app/oracle/cfgtoollogs/emca

mkdir -p /u01/app/oracle/cfgtoollogs/netca

mkdir -p /u01/app/oracle/oradata/db     

mkdir -p /u01/app/oracle/admin/db/adump

Step2:

Create pfile.

[oracle@linux2~]$ vi /backup/pfile.ora

Make following entry in pfile

Db_name=db

Step3:

Start database using above created pfile

SQL>startup nomount pfile=’/backup/pfile.ora’

Step4:

Create password file using ‘Shell prompt’.

[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapwdb’ password=oracle entries=5 force=y ignorecase=y

Step5:

Use this command to connect to rman

rman target=sys/oracle@primary auxiliary=sys/oracle@standby

Step6:

Run following script in rman prompt to create standby database using rman

run{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database

DORECOVER

spfile

parameter_value_convert 'db','db'

set db_file_name_convert='/u01/app/oracle/oradata/db/','/u01/app/oracle/oradata/db/'

set

log_file_name_convert='/u01/app/oracle/oradata/db/','/u01/app/oracle/oradata/db/'

set 'db_unique_name'='standby'

set

control_files='/u01/app/oracle/oradata/db/control01.ctl','/u01/app/orac

e/flash_recovery_area/db/control02.ctl'

set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

set DB_RECOVERY_FILE_DEST_SIZE='52000'

nofilenamecheck;

}

Check Standby Archive Destination

Run following command at primary site to check archive dest_1 for standby .

select status, error from v$archive_dest where dest_id=2;

On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.

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

Start Apply Process

Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

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;

Check the new archived redo log has arrived at the standby server and been applied.

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#;

Protection Mode

There are three protection modes for the primary database:

• Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
• Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
• Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE

--------------------

MAXIMUM PERFORMANCE

SQL>

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.

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

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

ALTER DATABASE OPEN;

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Step1:

Cancel the manager recovery.

SQL> recover managed standby database cancel;
Media recovery complete.

Step2:

Open the database in read-only mode.

SQL> alter database open read only;
Database altered.

Step3:

Restarts the Redo apply.

SQL> recover managed standby database disconnect using current logfile;
Media recovery complete.

-------------------------------------------------------------------------------------------------------------------------------
###############################################################################