Configure Oracle Dataguard with Broker Service

Configure Oracle Dataguard with Broker Service

Steps to Configure the Primary and standby database for creating PHYSICAL STANDBY DATABASE

Create standby database involves more than one node. The hardware structure and file system must be same for primary database and standby database.

On primary node

1) Create a password file.
Check a password file already exists or not, run the following command:

SQL> select * from v$pwfile_users;
–If it doesn’t exist, use the following command to create:
$Orapwd file=pwdtestpr.ora password=xxxxxxxx force=y

Note: This Password file exist in dbs directory linux platform under $ORACLE_HOME/dbs
 
2) Configure a Standby Redo log.
Note:-The size of the standby redoes log files should match the size of the current Primary database online redo log files

–To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
———-
52428800
52428800
52428800

—Create standby Redo log groups.
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

–To verify the results of the standby redo log:
SQL>select * from v$standby_log;

 
3) Check on primary database whether the archiving is enable.

–If not enable the archiving:
Sql>startup mount
Sql>alter database archivelog;
Sql>alter database open;

–Ensure the primary database in ARCHIVELOG mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6

Note:- In oracle 11g R2, we don’t have need to mention in spfile.
LOG_ARCHIVE_START=TRUE
Parameter is deprecated in 11g:
ORA-32004: obsolete and/or deprecated parameter(s) specified
 
4) Enable database in FORCE LOGGING mode.

SQL> alter database force logging;
Database altered

 
5) Set Primary Database Initialization Parameters.

— Create a pfile from spfile
Create pfile from spfile for the primary database:
SQL>create pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initTESTPR.ora’ from spfile;

— Edit pfile initTESTPR.ora to add the new parameters for setup dataguard environment
— General parameter pfile
TESTPR.__db_cache_size=113246208
TESTPR.__java_pool_size=4194304
TESTPR.__large_pool_size=4194304
TESTPR.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
TESTPR.__pga_aggregate_target=209715200
TESTPR.__sga_target=289406976
TESTPR.__shared_io_pool_size=0
TESTPR.__shared_pool_size=159383552
TESTPR.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/TESTPR/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/TESTPR/control01.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’TESTPR’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTPRXDB)’
*.log_archive_max_processes=30
*.log_archive_min_succeed_dest=1
TESTPR.log_archive_trace=0
*.memory_target=498073600
*.open_cursors=300
*.processes=150
*.sga_max_size=488073600
*.undo_tablespace=’UNDOTBS1′
*.archive_lag_target=0
— Adding following for configure Dataguard
*.db_unique_name=’TESTPR’
*.fal_client=’TESTPR’
*.fal_server=’testdr’
*.log_archive_config=’dg_config=(TESTPR,testdr)’
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TESTPR’
*.log_archive_dest_2=’service=”testdr”‘,’LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”testdr” net_timeout=30′,’valid_for=(all_logfiles,primary_role)’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.log_file_name_convert=’u01/app/oracle/TESTDR’,’/u01/app/oracle/TESTPR’
*.db_file_name_convert=’/u01/app/oracle/TESTDR’,’/u01/app/oracle/TESTPR’
*.standby_file_management=’AUTO’
*.remote_login_passwordfile=’EXCLUSIVE’
— For configure DG Broker Service
*.dg_broker_config_file1=’/u01/app/oracle/admin/dgbroker1/dg1TESTPR.dat’
*.dg_broker_config_file2=’/u01/app/oracle/admin/dgbroker2/dg2TESTPR.dat’
*.dg_broker_start=true

 
6) Now starts the database with new pfile.

sql>startup pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initTESTPR.ora’ nomount;
Create spfile and start the database with new spfile:
sql>create spfile from pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initTESTPR.ora’;
sql>shutdown immediate
sql>startup ;

 
7) On Primary DB, create a control file for the standby.

SQL>alter database create standby controlfile as ‘/u01/app/oracle/stand01.ctl’;
Then scp standby control file at standby database

 
8) Copy the primary database to standby Server.

–shutdown primary database
Shutdown immediate;
Then scp standby control file and database file to standby database.
$cd /u01/app/oracle/TESTPR
$ scp * 192.168.1.101: /u01/app/oracle/TESTDR
Startup

 
9) Copy the pfile, password file to standby Server.
 
10) Edit the tnsname.ora file and add entry for Standby Server on Primary.

TESTPR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testpr)
)
)
TESTDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dataguard2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdr)
)
)

 
11) Edit the listener.ora on primary server.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTPR_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTPR)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTPR)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

 
12) copy the listener and tnsnames.ora to Standby Server Network folder.

On Standby Server

1) Install the oracle software only on Standby Server.
$ ./runInstaller
 
2) Used already Copied all the datafiles, password file, initpfile to standby server.
 
3) Edit the pfile for the Standby SERVER.

— General pfile parameter
TESTDR.__db_cache_size=130023424
TESTDR.__java_pool_size=4194304
TESTDR.__large_pool_size=4194304
TESTDR.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
TESTDR.__pga_aggregate_target=209715200
TESTDR.__sga_target=289406976
TESTDR.__shared_io_pool_size=0
TESTDR.__shared_pool_size=142606336
TESTDR.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/TESTDR/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_file_record_keep_time=30
*.control_files=’/u01/app/oracle/TESTDR/stand01.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/TESTDR/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_name=’TESTPR’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTPRXDB)’
*.log_archive_max_processes=30
*.log_archive_min_succeed_dest=1
TESTDR.log_archive_trace=0
*.max_dispatchers=6
*.max_shared_servers=1
*.memory_target=498073600
*.open_cursors=300
*.processes=150
*.sec_case_sensitive_logon=FALSE
*.sessions=322
*.shared_servers=1
*.undo_tablespace=’UNDOTBS1′
— edit for standby server
*.fal_client=’TESTDR’
*.fal_server=’testpr’
*.log_archive_config=’dg_config=(TESTDR,testpr)’
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TESTDR’
*.log_archive_dest_2=’service=”testpr”‘,’LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”testpr” net_timeout=30′,’valid_for=(all_logfiles,primary_role)’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.log_file_name_convert=’u01/app/oracle/TESTPR’,’/u01/app/oracle/TESTDR’
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.archive_lag_target=0
*.db_file_name_convert=’/u01/app/oracle/TESTPR’,’/u01/app/oracle/TESTDR’
*.db_unique_name=’TESTDR’
— edit for db broker server
*.dg_broker_config_file1=’/u01/app/oracle/admin/dgbroker1/dg1TESTDR.dat’,’/u01/app/oracle/admin/dgbroker2/dg2TESTDR.dat’
*.dg_broker_start=TRUE

 
4) Edit the listener.ora files

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDR_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDR)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDR)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard2)(PORT = 1521)(IP=FIRST))
)

ADR_BASE_LISTENER = /u01/app/oracle

 
5)Copy the tnsnames.ora and check the tnsping from both server.

tnsping testdr
tnsping testpr

 
6) On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOMEand ORACLE_SID.
 
7) Start up the standby database at nomount and generate a spfile.

sql>startup nomount pfile=’$ORACLE_HOME/dbs/initTESTDR.ora’
sql>create spfile from pfile=’$ORACLE_HOME/dbs/initTESTDR.ora’
— Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

 
8) Start Redo apply on Standby.

–On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
–If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;

 
9) Verify the standby database is performing properly.

–On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
–On Primary, force a logfile switch:
SQL>alter system switch logfile;

 
10) Verify the archived redo log files were applied.

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

Conversion from Physical standby to Active Data Guard

We can convert the physical standby into active Data Guard standby (readyonly standby server).
Following are the steps:

–Stop Apply Services
SQL> alter database recover managed standby database cancel ;

— Follow this steps
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Note: It open standby database in readonly mode for reporting access to avoid load from primary database.
 

Verify that Active Data Guard is Enabled or Not

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE CONTROLFILE
——————– ————
READ ONLY WITH APPLY STANDBY

Creating A Dataguard Broker Configuration Using DGMGRL

On Prepare Primary Site
1. Configure parameter db_broker Init.ora Changes.

*.dg_broker_config_file1=’/u01/app/oracle/admin/dgbroker1/dg1TESTPR.dat’
*.dg_broker_config_file2=’/u01/app/oracle/admin/dgbroker2/dg2TESTPR.dat’
*.dg_broker_start=true

Note: we already added in pfile in above steps
 
2. Listener.ora file Changes on primary.
GLOBAL_DBNAME attribute value should be set as shown in the following listener.ora configuration on primary site otherwise you will get TNS-12154 error during switch over configuration.
A service with name _DGMGRL will be started when the listener is started.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTPR_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTPR)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = TESTPR)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard1)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

 

On Prepare Standby site
3. Change the Init.ora on Standby Server.

*.dg_broker_config_file1=’/u01/app/oracle/admin/dgbroker1/dg1TESTDR.dat’
*.dg_broker_config_file1=’/u01/app/oracle/admin/dgbroker2/dg2TESTDR.dat’
*.dg_broker_start=true

 
4. Listener.ora Changes on Standy Server.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDR_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDR)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDR)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dataguard2)(PORT = 1521)(IP=FIRST))
)

ADR_BASE_LISTENER = /u01/app/oracle

 
5. On primary Server, Create Dataguard configuration.
On the primary Database Server connect to database using DGMGRL utility

$DGMGRL
DGMGRL> CONNECT sys/sys123@testpr(service name)
Connected

DGMGRL> CREATE CONFIGURATION ross AS PRIMARY DATABASE IS testpr CONNECT IDENTIFIER IS testpr;
Configuration “ross” created with primary database testpr

DGMGRL> show configuration;
Configuration – ross
Protection Mode: MaxPerformanceDatabases:
testpr – Primary database
Fast-Start Failover: DISABLED
Configuration Status:DISABLED
DGMGRL>

Note: In the created configuration command,
ross= It is the name of the configuration. And it can be anything that your choice.
Primary Database is testpr = Here testpr is primary database db_unique_name value
Connect Identifier is ‘testpr’ = Here ‘testpr’ is alias name defined in tnsnames.ora to connect to primary database.
 
6. Add the standby to the configuration and check it.

DGMGRL> ADD DATABASE testdr AS CONNECT IDENTIFIER IS testdr MAINTAINED AS PHYSICAL;
Database “testdr” added

DGMGRL> show configuration;
Configuration – ross
Protection Mode: MaxPerformance Databases:
testpr – Primary database
testdr – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:DISABLED

 
7. Verbose command is used to check the all configuration of database.

DGMGRL> SHOW DATABASE VERBOSE testdr;

DGMGRL> show database verbose testpr;

 
8. Enabling the configuration and databases.

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
DGMGRL> enable database testpr;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE testpr;
DGMGRL> enable database testdr;

 
9. Specify the FastStartFailoverTarget property.

DGMGRL> EDIT DATABASE testpr SET PROPERTY FastStartFailoverTarget=’testdr’;
Property “faststartfailovertarget” updated
DGMGRL> EDIT DATABASE testdr SET PROPERTY FastStartFailoverTarget=’testpr’;

 
10. Enable Flashback Database on the Primary and Standby Databases.
To enter the standby into Flashback mode you must shutdown the both databases, then while the primary is down execute the following commands on the standby:

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
System altered.
SQL> startup mount;
SQL> ALTER DATABASE FLASHBACK ON;

 
11. EXECUTE THE SWITCHOVER with dataguard brocker service.

DGMGRL> SWITCHOVER TO testdr;
Performing switch over NOW, please wait…
Operation requires shutdown of instance “testpr” on database “testpr”
Shutting down instance “testpr”…
ORA-01109: database not open Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “testdr” on database “testdr”
Shutting down instance “testdr”…
ORA-01109: database not open Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “testpr” on database “testpr”
Starting instance “testpr”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “testdr” on database “testdr”
Starting instance “testdr”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “testdr”

 
12. Verify the switchover with following commands.

DGMGRL> show configuration verbose

DGMGRL> show database testdr
Database
Name: testdr
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s): testdr
Current status for “testdr”:
SUCCESS

DGMGRL> show database testpr
Database
Name: testpr
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s): testpr
Current status for “testpr”:
SUCCESS

 
13. Failover the database to standby.

DGMGRL> failover to testdr
Performing failover NOW, please wait…
Failover succeeded, new primary is “testdr”

Note:- should connect to the target database
 
14. Confirm Data Guard switchover or failover has completed.

–Example shows what the display would look like after a switchover.
[oracle@dataguard2 ~]$ dgmgrl
DGMGRL> connect sys/sys123@testdr
DGMGRL> show configuration
Configuration
Name: DRSolution
Enabled: YES
Protection Mode: MaxAvailability
Databases:
testdr – Primary database
testpr – Physical standby database
Fast-Start Failover: DISABLED
Current status for “ross”: SUCCESS

–Example of failover, the physical standby database will show as “disabled” as below.

DGMGRL> show configuration
Configuration
Name: ross
Enabled: YES
Protection Mode: MaxAvailability
Databases:
testdr – Primary database
testpr – Physical standby database (disabled)
Fast-Start Failover: DISABLED
Current status for “ross”:
Warning: ORA-16608: one or more databases have warnings

Note: Physical standby database testdr running fully as your primary database server.After a failover, the physical standby database will not be present in your current setup.
 
15. Reinstate the Failover Server with data broker service.
In the case that the failed database could be brought back, issue the following command in Data Guard Command-Line Interface to reinstate the failed database as a physical standby database.

DGMGRL>Connect sys/sys123@testdr
connected

DGMGRL> reinstate database testpr
Reinstating database “testpr”, please wait…
Operation requires shutdown of instance “orclsid” on database “testpr”
Shutting down instance “orclsid”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “orclsid” on database “testpr”
Starting instance “orclsid”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “testpr” …
Operation requires shutdown of instance “orclsid” on database “testpr”
Shutting down instance “orclsid”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “orclsid” on database “testpr”
Starting instance “orclsid”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “testpr” …
Reinstatement of database “testpr” succeeded

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s