{"id":73281,"date":"2012-09-27T16:21:52","date_gmt":"2012-09-27T16:21:52","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/data-guard-physical-standby-database-best-practices-part-ii\/"},"modified":"2021-07-14T13:07:50","modified_gmt":"2021-07-14T13:07:50","slug":"data-guard-physical-standby-database-best-practices-part-ii","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/data-guard-physical-standby-database-best-practices-part-ii\/","title":{"rendered":"Data Guard Physical Standby Database Best Practices \u2013 Part II"},"content":{"rendered":"

Read Data Guard Physical Standby Database Best Practices \u2013 Part I<\/a><\/p>\n

In this second part we will setup the standby database and enable Fast Start Fail Over.<\/p>\n

Standby Database Creation<\/h2>\n

Start the standby database instance in NOMOUNT<\/strong> start up mode:<\/p>\n

SQL> startup nomount pfile=initdg2.ora;\r\n<\/pre>\n
Now that the configuration of the standby server is complete, let\u2019s perform the duplication from the primary site.<\/p>\n
Primary Server<\/strong><\/p>\n
Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
\n Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:<\/p>\n
RMAN> connect target sys\r\n\r\ntarget database Password: \r\nconnected to target database: DG1 (DBID=1753913301)\r\n\r\nRMAN> connect auxiliary sys@dg2\r\n\r\nauxiliary database Password: \r\nconnected to auxiliary database: DG2 (not mounted)\r\n\r\nRMAN> run{\r\nallocate channel prmy1 type disk;\r\nallocate channel prmy2 type disk;\r\nallocate auxiliary channel stby type disk;\r\nduplicate target database for standby from active database\r\nspfile\r\nparameter_value_convert 'dg1','dg2'\r\nset db_unique_name='dg2'\r\nset db_file_name_convert='\/dg1\/','\/dg2\/'\r\nset log_file_name_convert='\/dg1\/','\/dg2\/'\r\nset control_files='\/opt\/oradata\/dg2\/dg2.ctl'\r\nset log_archive_max_processes='5'\r\nset fal_client='dg2'\r\nset fal_server='dg1'\r\nset standby_file_management='AUTO'\r\nset log_archive_config='dg_config=(dg1,dg2)'\r\nset log_archive_dest_2='service=dg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg1'\r\n;\r\n}\r\n<\/pre>\n
The RMAN duplication process is finished without errors. After the database duplication, we will force the log switch on the primary database to archive the current redo log group.<\/p>\n
SQL> alter system switch logfile;\r\n<\/pre>\n
Standby Server<\/strong><\/p>\n
On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.The DISCONNECT option means the redo apply will run in background session.<\/p>\n
SQL> alter database recover managed standby database \r\n    using current logfile disconnect;\r\n<\/pre>\n
Note that the current log sequence number on the standby is 10.<\/p>\n
SQL> select sequence#, first_time, applied\r\n  from v$archived_log\r\n  order by sequence#;\r\n\r\n SEQUENCE# FIRST_TIM APPLIED\r\n---------- --------- ---------\r\n         9 16-JAN-12 YES\r\n        10 16-JAN-12 IN-MEMORY\r\n<\/pre>\n
Primary Server<\/strong><\/p>\n
Let\u2019s perform 3 additional log switches on the primary database. Then we will query the standby database to verify that the log files are applied to the standby.<\/p>\n
SQL> alter system switch logfile;\r\n<\/pre>\n
Standby Server<\/strong><\/p>\n
We query the standby database. The logs were successfully transported and applied.<\/p>\n
SQL> select sequence#, first_time, applied\r\n    from v$archived_log\r\n   order by sequence#;\r\n\r\n SEQUENCE# FIRST_TIM APPLIED\r\n---------- --------- ---------\r\n         9 16-JAN-12 YES\r\n        10 16-JAN-12 YES\r\n        11 16-JAN-12 YES\r\n        12 16-JAN-12 YES\r\n        13 16-JAN-12 IN-MEMORY\r\n<\/pre>\n
Broker Configuration<\/h2>\n
Configuring the broker is recommended because it simplifies data guard operations.<\/p>\n
The DG_BROKER_START parameter must be set to TRUE.<\/p>\n
Standby Server<\/strong><\/p>\n
SQL> alter system set dg_broker_start=TRUE;\r\n<\/pre>\n
The Oracle Net listener must be also configured with an additional static service identifier. The value of the GLOBAL_DBNAME attribute must be set to a concatenation of _DGMGRL.<\/p>\n
[oracle@dg2 dbs]$ cat $ORACLE_HOME\/network\/admin\/listener.ora\r\nLISTENER =\r\n  (ADDRESS_LIST=\r\n       (ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521))\r\n       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   \r\nSID_LIST_LISTENER=\r\n   (SID_LIST=\r\n       (SID_DESC=\r\n          (GLOBAL_DBNAME=dg2)\r\n          (SID_NAME=dg2)                      \r\n          (ORACLE_HOME=\/u01\/app\/oracle\/product\/11.2.0\/db_1)\r\n    )\r\n   (SID_DESC=\r\n          (GLOBAL_DBNAME=dg2_DGMGRL)\r\n          (SID_NAME=dg2)\r\n          (ORACLE_HOME=\/u01\/app\/oracle\/product\/11.2.0\/db_1)\r\n    )\r\n )\r\n       \r\n[oracle@dg2 dbs]$ lsnrctl status\r\n\r\n(Entry truncated)\r\n (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)))\r\n  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))\r\nServices Summary...\r\nService \"dg2\" has 1 instance(s).\r\n  Instance \"dg2\", status UNKNOWN, has 1 handler(s) for this service...\r\nService \"dg2_DGMGRL\" has 1 instance(s).\r\n  Instance \"dg2\", status UNKNOWN, has 1 handler(s) for this service...\r\nThe command completed successfully\r\n<\/pre>\n
Primary Server<\/strong><\/p>\n
SQL> alter system set dg_broker_start=TRUE;\r\n<\/pre>\n
The Oracle Net listener on the primary database should have static service definitions:<\/p>\n
[oracle@dg1 ~]$ cat $ORACLE_HOME\/network\/admin\/listener.ora\r\n\r\nLISTENER =\r\n  (DESCRIPTION_LIST =\r\n    (DESCRIPTION =\r\n      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))\r\n    )\r\n  )\r\n\r\nSID_LIST_LISTENER=\r\n   (SID_LIST=\r\n       (SID_DESC=\r\n          (GLOBAL_DBNAME=dg1)\r\n          (SID_NAME=dg1)\r\n          (ORACLE_HOME=\/u01\/app\/oracle\/product\/11.2.0\/db_1)\r\n    )\r\n   (SID_DESC=\r\n          (GLOBAL_DBNAME=dg1_DGMGRL)\r\n          (SID_NAME=dg1)\r\n          (ORACLE_HOME=\/u01\/app\/oracle\/product\/11.2.0\/db_1)\r\n    )\r\n )\r\n\r\nADR_BASE_LISTENER = \/u01\/app\/oracle\r\n\r\n[oracle@dg1 ~]$ lsnrctl start\r\n\r\n(Entry truncated)\r\nServices Summary...\r\nService \"dg1\" has 1 instance(s).\r\n  Instance \"dg1\", status UNKNOWN, has 1 handler(s) for this service...\r\nService \"dg1_DGMGRL\" has 1 instance(s).\r\n  Instance \"dg1\", status UNKNOWN, has 1 handler(s) for this service...\r\nThe command completed successfully\r\n\r\n<\/pre>\n
Let\u2019s create a broker configuration and identify the primary database.<\/p>\n
[oracle@dg1 ~]$ dgmgrl\r\nDGMGRL> connect sys\r\nPassword:\r\nConnected.\r\nDGMGRL> create configuration 'DGConfig1' as primary database is 'dg1'\r\n> connect identifier is dg1;\r\nConfiguration \"DGConfig1\" created with primary database \"dg1\"\r\nDGMGRL> add database 'dg2' as connect identifier is dg2;\r\nDatabase \"dg2\" added\r\nDGMGRL> enable configuration\r\nEnabled.\r\nDGMGRL> show configuration\r\n\r\nConfiguration - DGConfig1\r\n\r\n  Protection Mode: MaxPerformance\r\n  Databases:\r\n    dg1 - Primary database\r\n    dg2 - Physical standby database\r\n\r\nFast-Start Failover: DISABLED\r\n\r\nConfiguration Status:\r\nSUCCESS\r\n<\/pre>\n
Perform a switchover test:<\/strong><\/p>\n
DGMGRL> switchover to dg2;\r\nPerforming switchover NOW, please wait...\r\nNew primary database \"dg2\" is opening...\r\nOperation requires shutdown of instance \"dg1\" on database \"dg1\"\r\nShutting down instance \"dg1\"...\r\nORA-01109: database not open\r\n\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nOperation requires startup of instance \"dg1\" on database \"dg1\"\r\nStarting instance \"dg1\"...\r\nORACLE instance started.\r\nDatabase mounted.\r\nSwitchover succeeded, new primary is \"dg2\"\r\nDGMGRL> show configuration;\r\n\r\nConfiguration - DGConfig1\r\n\r\n  Protection Mode: MaxPerformance\r\n  Databases:\r\n    dg2 - Primary database\r\n    dg1 - Physical standby database\r\n\r\nFast-Start Failover: DISABLED\r\n\r\nConfiguration Status:\r\nSUCCESS\r\n<\/pre>\n
Standby Server<\/strong><\/p>\n
Confirm of the role switch:<\/p>\n
SQL> select database_role from v$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPRIMARY\r\n\r\n<\/pre>\n
Primary Server<\/strong><\/p>\n
The former primary database is now the new physical standby database:<\/p>\n
SQL> select database_role from v$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPHYSICAL STANDBY\r\n<\/pre>\n
Switch over to the former primary database:<\/p>\n
DGMGRL> switchover to dg1;\r\nPerforming switchover NOW, please wait...\r\nNew primary database \"dg1\" is opening...\r\nOperation requires shutdown of instance \"dg2\" on database \"dg2\"\r\nShutting down instance \"dg2\"...\r\nORA-01109: database not open\r\n\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\nOperation requires startup of instance \"dg2\" on database \"dg2\"\r\nStarting instance \"dg2\"...\r\nORACLE instance started.\r\nDatabase mounted.\r\nSwitchover succeeded, new primary is \"dg1\"\r\nDGMGRL> show configuration\r\n\r\nConfiguration - DGConfig1\r\n\r\n  Protection Mode: MaxPerformance\r\n  Databases:\r\n    dg1 - Primary database\r\n    dg2 - Physical standby database\r\n\r\nFast-Start Failover: DISABLED\r\n\r\nConfiguration Status:\r\nSUCCESS\r\n\r\nDGMGRL> show database dg2\r\n\r\nDatabase - dg2\r\n\r\n  Role:            PHYSICAL STANDBY\r\n  Intended State:  APPLY-ON\r\n  Transport Lag:   5 minutes 2 seconds\r\n  Apply Lag:       5 minutes 51 seconds\r\n  Real Time Query: OFF\r\n  Instance(s):\r\n    dg2\r\n\r\nDatabase Status:\r\nSUCCESS\r\n<\/pre>\n
Enable Fast Start Fail Over<\/h2>\n
The actual configuration is running in Max Performance mode and Fast Start Fail Over is currently disabled.<\/p>\n
Primary Server<\/strong><\/p>\n
To configure FSFO, you must first enable flashback database on both the primary and standby databases. (Further reading: Introduction to Oracle Flashback Technology<\/a>)<\/p>\n
SQL> alter database flashback on;\r\n<\/pre>\n
Redo apply must be stopped to enable flashback database on the standby database:<\/p>\n
DGMGRL> connect sys   \r\nPassword:\r\nConnected.\r\nDGMGRL> edit database 'dg2' set state='apply-off';\r\nSucceeded.\r\n<\/pre>\n
Standby Server<\/strong><\/p>\n
SQL> alter database flashback on;\r\n\r\nDatabase altered.\r\n\r\nSQL> select flashback_on from v$database;\r\n\r\nFLASHBACK_ON\r\n------------------\r\nYES\r\n<\/pre>\n
Primary Server<\/strong><\/p>\n
Restart the Redo apply:<\/p>\n
DGMGRL>  edit database 'dg2' set state='apply-on';\r\nSucceeded.\r\n<\/pre>\n
The Observer<\/h2>\n
The server dg3 will act as the observer in the Fast-Start Fail Over configuration. The Oracle client binaries have been installed with administrator option. Confirm the connectivity with both the primary and the standby databases:<\/p>\n
[oracle@dg3 ~]$ tnsping dg1\r\n\r\n(Entry truncated)\r\nUsed EZCONNECT adapter to resolve the alias\r\nAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521)))\r\nOK (140 msec)\r\n[oracle@dg3 ~]$ tnsping dg2\r\n\r\n(Entry truncated)\r\nAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521)))\r\nOK (50 msec)\r\n[oracle@dg3 ~]$\r\n<\/pre>\n
The prerequisites for FSFO have been met. So FSFO can be configured, enabled and started. The FSFO observer process will be started using the DGMGRL session and will be logged to a file named observer.log:<\/p>\n
[oracle@dg3 admin]$ dgmgrl -logfile .\/observer.log\r\nDGMGRL for Linux: Version 11.2.0.1.0 - Production\r\n\r\nCopyright (c) 2000, 2009, Oracle. All rights reserved.\r\n\r\nWelcome to DGMGRL, type \"help\" for information.\r\nDGMGRL> connect sys@dg1\r\nPassword:\r\nConnected. \r\nDGMGRL> show fast_start failover\r\n\r\nFast-Start Failover: DISABLED\r\n\r\n  Threshold:        30 seconds\r\n  Target:           (none)\r\n  Observer:         (none)\r\n  Lag Limit:        30 seconds\r\n  Shutdown Primary: TRUE\r\n  Auto-reinstate:   TRUE\r\n\r\nConfigurable Failover Conditions\r\n  Health Conditions:\r\n    Corrupted Controlfile          YES\r\n    Corrupted Dictionary           YES\r\n    Inaccessible Logfile            NO\r\n    Stuck Archiver                  NO\r\n    Datafile Offline               YES\r\n\r\n  Oracle Error Conditions:\r\n    (none)\r\n \r\nDGMGRL> edit configuration set property FastStartFailoverLagLimit=60;\r\nProperty \"faststartfailoverlaglimit\" updated\r\nDGMGRL> enable fast_start failover;\r\nEnabled.\r\nDGMGRL> start observer;\r\n<\/pre>\n
Primary Server<\/strong><\/p>\n
DGMGRL> show configuration verbose\r\n\r\nConfiguration - DGConfig1\r\n\r\n  Protection Mode: MaxPerformance\r\n  Databases:\r\n    dg1 - Primary database\r\n    dg2 - (*) Physical standby database\r\n\r\n  (*) Fast-Start Failover target\r\n\r\nFast-Start Failover: ENABLED\r\n\r\n  Threshold:        30 seconds\r\n  Target:           dg2\r\n  Observer:         dg3.localdomain\r\n  Lag Limit:        60 seconds\r\n  Shutdown Primary: TRUE\r\n  Auto-reinstate:   TRUE\r\n\r\nConfiguration Status:\r\nSUCCESS\r\n<\/pre>\n
Create a shell script and run the shell script in the background; create observer.sh (MOS ID: 1084681.1)<\/p>\n
#!\/bin\/ksh\r\ndgmgrl -echo -logfile \/home\/oracle\/dgmgrl.log << EOF\r\nconnect sys\/wissem@dg3\r\nstart observer\r\nEOF\r\nchmod +x observer.sh\r\n.\/observer.sh &\r\n<\/pre>\n
Where do you place the Observer? <\/strong><\/p>\n
That all depends on your requirements, the observer can be placed in a third site separated from primary and standby sites with a HA observer; this means 2 hosts; one observer process running on the first host and the other on standby (not running) on the second host. Only one observer can run at any one time.<\/p>\n
References<\/strong><\/p>\n
https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41134\/toc.htm<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In this second part we will setup the standby database and enable Fast Start Fail Over.…<\/p>\n","protected":false},"author":316200,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[48572],"class_list":["post-73281","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316200"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73281"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73281\/revisions"}],"predecessor-version":[{"id":73662,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73281\/revisions\/73662"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73281"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}