Information Technology Writeups: Data Guard/Broker Configuration Oracle 10g

PRIMARY SITE: (Information)

Machine IP: 192.168.1.10
Database name (db_name): ORCL
Database Unique Name (db_unique_name): primary
TNS Service Name: to_standby (Through this service, the primary machine will be connected to STANDBY machine)

STANDBY SITE: (Information)

Machine IP: 192.168.1.20
Database name (db_name): ORCL
Database Unique Name (db_unique_name): standby
TNS Service Name: to_primary (Through this service, the standby machine will be connected to PRIMARY machine)

CONFIGURATION ON PRIMARY

Create pfile from spfile on the primary database:

SQL> Create pfile=’C:\oracle\product\10.2.0\db_1\dbs\spfilePrimary.ora’ from spfile;

initPrimary.ora file setting on the PRIMARY Machine.

db_unique_name=’PRIMARY’

FAL_Client=’to_primary’

FAL_Server=’to_standby’

Log_archive_config=’DG_CONFIG=(primary,standby)’

Log_archive_dest_1=’Location=c:\oracle\backup

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

db_unique_name=primary’

Log_archive_dest_2=’Service=to_standby 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’

Create password file using ‘cmd’.

C:\> orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDOrcl.ora password=oracle entries=5 [force=y].

Force option is used to replace an existing password file. Now startup the PRIMARY database to MOUNT stage.

• SQL> startup mount;

Make the following changes: Take the database to Archive Mode.

• SQL> Alter database ArchiveLog;

§ Enable Force Logging.

• SQL> Alter database Force Logging;

On the PRIMARY site, create standby log file for
and data broker setting

• SQL>Alter database add standby logfile

(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo.log’) size 150m;

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.

• SQL> startup mount;

Now create a standby controlfile on the PRIMARY site.

• SQL> Alter database create standby controlfile as ‘c:\oracle\backup\standcontrol.ctl’;

Now copy the created standby control file to the standby datafile location.

Create spFile from pfile.

• SQL> Create spfile from pfile;

Restart the primary database.

on PRIMARY machine create an entry in TNSnames.ora file through which the PRIMARY site will be connected to the Standby.

TO_STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

Also check the connectivity from the SQL Prompt.

• SQL> connect sys/oracle@to_standby as sysdba

Connected.

> Lsnrctl stop

> Lsnrctl start

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

CONFIGURATION ON STANDBY

Check the mode of Archiving by following command:

• SQL> Archive Log List

Then create pfile from spfile on the standby database:

SQL> Create pfile from spfile=’C:\oracle\product\10.2.0\db_1\dbs\spfileStandby.ora’;

initStandby.ora file on the STANDBY Machine.

db_unique_name=’STANDBY’

FAL_Client=’to_standby’

FAL_Server=’to_primary’

Log_archive_config=’DG_CONFIG=(primary,standby)’

Log_archive_dest_1=’Location=c:\oracle\backup

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

db_unique_name=standby’

Log_archive_dest_2=’Service=to_primary

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’

db_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'

log_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'

lock_name_space=standby

Create password file using ‘cmd’.

C:\> orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDOrcl.ora password=oracle entries=5 [force=y].

Force option is used to replace an existing password file.

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

TO_PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

Check the connectivity from the SQL Prompt.

• SQL> connect sys/oracle@to_primary as sysdba

Connected.

> Lsnrctl stop

> Lsnrctl start

Create spfile from pfile.

• SQL> Create spfile from pfile;

Restart the database. Now startup the STANDBY database to mount stage.

• SQL> startup mount

Enable Force Logging.

• SQL> Alter database Force Logging;

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

LOG SHIPPING

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;

System Altered.

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 returns 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 Recovery Process (MRP). This command is executed on Mount stage.

• SQL> Alter Database Recover Managed Standby Database;

Database Altered.

By executing the above command your session will be taken by oracle and become in hang state . To avoid this hanging, you can execute the following command with DISCONNECT option.

• SQL> Alter Database Recover Managed Standby Database Disconnect;

Database Altered.

Now the session will be available to you and MRP (media recovery process )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.

Manual Switchover:

On the PRIMARY Database: (Open stage)

Query V$DATABASE to check the role of Primary Database.

• SQL> Select Database_role

from v$Database;

It will return “PRIMARY”,

Now check the Switchover Status of the Primary Database.

• SQL> Select switchover_status

from v$Database;

It will return “SESSIONS ACTIVE”.

Now you are ready to perform a manual switchover. Execute the following command using “WITH SESSION SHUTDOWN” option.

• SQL> Alter Database Commit to Switchover to Physical Standby with session Shutdown;

Database Altered.

Now your PRIMARY Database has become Physical Standby. Before To verify this change, You must Shutdown the database and again Start it to mount stage. Again query the Database_role column of V$DATABASE. Now it will return “PHYSICAL STANDBY”.

On the PHYSICAL STANDBY Database: (Mount stage) Query V$DATABASE to check the role of Standby Database.

• SQL> Select Database_role

from v$Database;

It will return “PHYSICAL STANDBY”,

Now check the Switchover Status of the Standby Database.

• SQL> Select switchover_status

from v$Database;

It will return “NOT ALLOWED”

Now check the Switchover Status of the Standby Database.

• SQL> Alter database commit to switch over to primary;

It will change the role of standby then shutdown immediate the standby and start it again as you start your primary and start the log apply services as mentioned above.

Revert back the standby database to Primary and vice versa

Now cancel the MRP which is running in the background of the Standby Database. Execute the following command:

• SQL> Alter database Recover Managed Standby Database Cancel;

Database Altered.

Now you are ready to perform a manual switchover from Physical Standby to Primary. Execute the following command using “WITH SESSION SHUTDOWN” option.

• SQL> Alter Database Commit to Switchover to PRIMARY with session Shutdown;

Database Altered.

Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.

Shutdown the database and again Start it to Open stage.

DATAGUARD BROKER :

• Creating and enabling Data Guard configurations, including setting up log transport services and log apply services.

• Managing an entire Data Guard configuration from any system in the configuration.

• Managing and monitoring Data Guard configurations that contain Real Application Clusters primary or standby databases.

BROKER CONFIGURATION:

On both Primary and Standby sites, change the initialization parameter in the spfile to enable the Data guard broker.

•SQL> Alter system set dg_broker_start=True scope=both;

System Altered.

On the PRIMARY site, open the ‘cmd’ and start Command Line Interface (CLI) of the Dataguard Broker (DGMGRL).

C:\> dgmgrl

DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL>_

Now connect to the database through the service you made previously.

DGMGRL> connect sys/oracle@to_primary

Connected.

Create broker configuration.

DGMGRL> create configuration ‘broker1’ as

> primary database is ‘primary’

> connect identifier is to_primary;

(‘to_primary’ in Connect identifier is the service name through which the broker is connected to the PRIMARY database)

Add Standby Database to the above configuration.

DGMGRL> Add database ‘standby’ as

> connect identifier is to_standby

> maintained as physical;

(‘to_standby’ in Connect identifier is the service name through which the broker is connected to the STANDBY database)

Now the configuration has been set up but it is still disabled. You can view the configuration by executing:

DGMGRL> show configuration

Configuration

Name: broker1

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLE

Databases:

primary - Physical standby database

standby - Primary database

Current status for "broker1":

DISABLE

The next step is to ENABLE the configuration ‘broker1’.

DGMGRL> enable configuration;

Enabled

Again view the configuration.

DGMGRL> show configuration

Configuration

Name: broker1

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLE

Databases:

primary - Physical standby database

standby - Primary database

Current status for "broker1":

SUCCESS

Now we are ready to switch over the PRIMARY database Role to STANDBY database Role.

DGMGRL> switchover to ‘Standby’;

…..

Primary Database Successfully converted to Physical Standby. You can again switch over the Standby Database to Primary by executing following command.

DGMGRL> switchover to ‘Primary’;

…..

Standby Database is successfully converted to Primary Database.

Failover:

Failover can be done through the same configuration without any alteration. You simply need to execute following command:

DGMGRL> failover to ‘Standby’;

….

Failover to standby succeeded.

And also…

DGMGRL> failover to ‘Primary’;

….

Failover to primary succeeded.