Differences

This shows you the differences between two versions of the page.

--- oracle_guard_installation [2020/10/25 08:35] – andonovj
+++ oracle_guard_installation [2020/10/25 15:17] (current) – [Verify Replication] andonovj
@@ Line 9: / Line 9: @@
-{{ :oracledataguardinitialconfig.png?600 |}}
+{{:oracledataguardinitialconfig.png?600|}}
 So, our goal is to bring it to:
-{{ :oracledataguardgoal.png?600 |}}
+{{:oracledataguardgoal.png?600|}}
 So, let's get going
@@ Line 132: / Line 132: @@
 <Code:bash|Set Archivelog Destinations>
-SQL> alter system set
+SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=eastdb' scope=both sid='*';               <- That command should be ONE LINE, otherwise you will have issues later
-log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST
-valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=eastdb' scope=both
-sid='*';
 System altered.
-SQL> alter system set log_archive_dest_state_1='enable' scope=both
+SQL> alter system set log_archive_dest_state_1='enable' scope=both sid='*';
-sid='*';
 System altered.
-valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=westdb'
-scope=both sid='*';
+SQL> alter system set log_archive_dest_2='SERVICE=westdb ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=westdb' scope=both sid='*';               <- That command should be ONE LINE, otherwise you will have issues later
 System altered.
 SQL> alter system set log_archive_dest_state_2='DEFER' scope=both
 sid='*';
 System altered.
+SQL>
 SQL>
 SQL>
@@ Line 408: / Line 407: @@
 disconnect";
 }
------------------------------------------------------------
+....................................................................
 sql statement: alter system archive log current
 contents of Memory Script:
@@ Line 449: / Line 448: @@
-=====Setup=====
+=====Completing the Standby Configuration=====
+Now that we have a standby database, we have to integrate it in the Grid Infrastructure, so we can take advantage of all these nice features. However before that, we have to enable it and check if it works correctly. So in this part we will:
+  * Enable the log_archive_dest_2 & Check archivelog apply
+  * Add the Clusterware entries
+====Enable The archivelog destination & Check archivelog apply ====
+Remember, that we set the second archivelog destination on the primary as DEFER :) Well, I sure do, that means that Oracle won't send the archivelog on the service. We have to change that
+<Code:bash|On Primary>
+SQL> col destination format a10
+SQL> col error format a10
+SQL> select dest_id, status, destination, archiver, error, valid_now
+     from v$archive_dest where dest_id = 2;
+DEST_ID STATUS DESTINATIO ARCHIVER ERROR VALID_NOW
+------- --------- ---------- --------- ---------- --------------
+DEFERRED westdb LGWR ORA-12514 UNKNOWN
+SQL>
+SQL> alter system set log_archive_dest_state_2=ENABLE;
+System altered.
+SQL> select dest_id, status, destination, archiver, error, valid_now
+from v$archive_dest where dest_id = 2;
+DEST_ID STATUS DESTINATIO ARCHIVER E VALID_NOW
+---------- --------- ---------- ---------- - ----------------
+VALID westdb LGWR YES
+SQL>
+</Code>
+After that, we have to verify the application
+<Code:bash|Verify Application>
+--On Primary
+[oracle@enode01 ~]$ sqlplus / as sysdba
+SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;
+THREAD# MAX(SEQUENCE#)
+---------- --------------
+11
+2
+--On Standby
+SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY THREAD#;
+THREAD# SEQUENCE# APPLIED
+---------- ---------- ---------
+11 YES
+2 YES
+SQL>
+</Code>
+Now that we checked and verified that the archivelogs are being applied, we have to re-create the spfile. Remember that the spfile was re-created during the duplicate procedure and it contains data referring to the primary.
+<Code:bash|Modify the pfile>
+SQL> create pfile='/tmp/init.ora' from spfile;
+File created.
+SQL> exit
+[oracle@wnode03 ~]$
+[oracle@wnode03 ~]$ vi /tmp/init.ora
+:g/westdb1._/d
+fewer lines
+:%s/eastdb._/westdb._/g
+substitutions on 10 lines
+:%s/eastdb1/westdb1/g
+substitutions on 13 lines
+:%s/eastdb2/westdb2/g
+substitutions on 13 lines
+--Be sure the following values are SET:
+*.cluster_database=TRUE
+*.dispatchers='(PROTOCOL=TCP) (SERVICE=westdbXDB)'
+*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=westdb'
+*.log_archive_dest_2='SERVICE=eastdb ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=eastdb'
+*.log_archive_dest_state_2='enable'
+:wq!
+[oracle@wnode03 ~]$ sqlplus / as sysdba
+SQL> create spfile='+DATA/westdb/spfilewestdb.ora' from
+pfile='/tmp/init.ora';
+File created.
+SQL> exit
+[oracle@wnode03 ~]$
+[oracle@wnode03 ~]$ vi $ORACLE_HOME/dbs/initwestdb1.ora
+### And Add the following entry
+spfile='+DATA/westdb/spfilewestdb.ora'
+:wq!
+[oracle@wnode03 ~]$ scp $ORACLE_HOME/dbs/initwestdb1.ora
+wnode04:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initwestdb2.ora
+initwestdb1.ora 100% 39 0.0KB/s 00:00
+[oracle@wnode03 ~]$
+</Code>
+Phew, that was something, now that we are done with the modifications, we have to shutdown the standby, prior adding it to the grid.
+<Code:bash|Shutdown the Standby>
+[oracle@wnode03 ~]$ sqlplus / as sysdba
+SQL> shutdown immediate
+ORA-01109: database not open
+Database dismounted.
+ORACLE instance shut down.
+SQL> exit
+[oracle@wnode03 ~]$
+</Code>
+====Add Clusterware entries for the Standby====
+Adding entries to the clusterware registeries is rather simple:
+<Code:bash|On standby>
+[oracle@wnode03 ~]$ srvctl add database -db westdb -oraclehome
+/u01/app/oracle/product/12.1.0/dbhome_1 -dbtype RAC -spfile
+'+DATA/westdb/spfilewestdb.ora' -role physical_standby -diskgroup
+"DATA,FRA" -dbname eastdb -domain example.com
+[oracle@wnode03 ~]$ srvctl add instance -db westdb -instance westdb1 -
+node wnode03
+[oracle@wnode03 ~]$ srvctl add instance -db westdb -instance westdb2 -
+node wnode04
+[oracle@wnode03 ~]$
+</Code>
+It is important they to be added as the owner of the resource. Otherwise, you might not be able to start it later, due to profile restrictions.
+Finally, we can start and verify the standby:
+<Code:bash|On Standby>
+[oracle@wnode03 ~]$ srvctl start database -db westdb -startoption mount
+[oracle@wnode03 ~]$ srvctl status database -db westdb -verbose
+Instance westdb1 is running on node wnode03. Instance status: Mounted
+(Closed).
+Instance westdb2 is running on node wnode04. Instance status: Mounted
+(Closed).
+[oracle@wnode03 ~]$
+[oracle@wnode03 ~]$ sqlplus / as sysdba
+SQL> alter database recover managed standby database disconnect;
+Database altered.
+SQL> exit
+SQL>
+[oracle@wnode03 ~]$ srvctl config database -db westdb
+Database unique name: westdb
+Database name: eastdb
+Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
+Oracle user: oracle
+Spfile: +DATA/westdb/spfilewestdb.ora
+Password file:
+Domain: example.com
+Start options: open
+Stop options: immediate
+Database role: PHYSICAL_STANDBY                 <- Physical Standby
+Management policy: AUTOMATIC
+Server pools:
+Disk Groups: DATA,FRA
+Mount point paths:
+Services:
+Type: RAC                                       <- RAC Database
+Start concurrency:
+Stop concurrency:
+Database is enabled
+Database is individually enabled on nodes:
+Database is individually disabled on nodes:
+OSDBA group: dba
+OSOPER group: oper
+Database instances: westdb1,westdb2
+Configured nodes: wnode03,wnode04
+Database is administrator managed
+[oracle@wnode03 ~]$
+</Code>
+====Verify Replication====
+To verify replication, we can simply check 2 things. Is the MRP process on the Standby Running, is the archivelogs being applied. Don't forget that the Data Guard in general can be either:
+  * Physical Guard (up to Read Only - Active Data Guard)
+  * Logical Guard (up to Read Write)
+{{:oracledataguard.jpg?600|}}
+----
+Archiver Process –  The archiver process (ARCn or ARCH) is responsible for archiving online redo logs. The archival destination could be a local destination or a remote standby database site. In the case of a Data Guard configuration, one of the archival destinations must be a standby database.  The archiver process of the primary database writes the redo log file.
+Log Writer (LGWR) – The log writer process on the primary database writes entries from the redo log buffer to the online redo log file. When the current online redo log file is full, it triggers the archiver process to start the archiving activity.
+Remote File Server (RFS) Process – The RFS process runs on the standby database and is responsible for communication between the primary and the standby database. For the log transport service, the RFS on the standby database receives the redo records from the archiver or the log writer process of the primary database over Oracle Net and writes to filesystem on the standby site.
+Fetch Archive Log (FAL) – The FAL process has two components: FAL Client and FAL Server. Both processes are used for archive gap resolution. If the Managed Recovery Process (MRP) on the standby database site detects an archive gap sequence, it initiates a fetch request to the FAL client on the standby site. This action, in turn, requests the FAL server process on the primary database to re-transmit the archived log files to resolve the gap sequence. Archive gap sequences will be discussed later in this chapter.
+Once the log transport service completes the transmission of redo records to the standby site, the log apply service starts applying the changes to the standby database. The log apply service operates solely on the standby database. The following processes on the standby site facilitate the log apply operations:
+Managed Recovery Process (MRP) – The MRP  applies the redo entries from the archived redo logs onto the physical standby database.
+Logical Standby Process (LSP) – The LSP applies the redo records from archived redo logs to the logical standby database. The Oracle database log miner engine is used by the logical standby process for the SQL apply operations. Using the log miner engine, the LSP process recreates the SQL statements from redo logs that have been executed on the primary database. These statements are then applied to the standby database to keep it current with the primary database.
+Because of that there are limitation of the Logical Guard. But we will address that in the Logical Guard section, enough side ways :)
+----
+So, to verify the application we can do the following:
+<Code:bash|Verify Apply>
+--On Standby
+[oracle@wnode03 ~]$ pgrep -lf mrp
+ora_mrp0_westdb1
+[oracle@wnode03 dbs]$
+[oracle@wnode03 ~]$ sqlplus / as sysdba
+SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY
+THREAD#;
+THREAD# SEQUENCE# APPLIED
+---------- ---------- ---------
+12 YES
+13 YES
+11 YES
+4 IN-MEMORY
+3 YES
+2 YES
+rows selected.
+SQL>
+--on Primary:
+SQL> alter system archive log current;
+System altered.
+SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM GV$ARCHIVED_LOG WHERE
+RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM
+GV$ARCHIVED_LOG) GROUP BY THREAD#;
+THREAD# MAX(SEQUENCE#)
+---------- --------------
+16
+7
+SQL>
+--On Standby
+SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY
+THREAD#;
+THREAD# SEQUENCE# APPLIED
+---------- ---------- ---------
+16 YES
+15 YES
+13 YES
+11 YES
+14 YES
+12 YES
+6 YES
+5 YES
+7 IN-MEMORY
+3 YES
+2 YES
+THREAD# SEQUENCE# APPLIED
+---------- ---------- ---------
+4 YES
+rows selected.
+SQL>
+SQL> select inst_id, process, status, sequence#, thread# from
+gv$managed_standby;
+INST_ID PROCESS STATUS SEQUENCE# THREAD#
+---------- --------- ------------ ---------- ----------
+ARCH CLOSING 13 1
+ARCH CONNECTED 0 0
+ARCH CLOSING 7 2
+ARCH CLOSING 5 2
+RFS IDLE 8 2
+RFS IDLE 0 0
+RFS IDLE 0 0
+MRP0 APPLYING_LOG 8 2
+ARCH CLOSING 16 1
+ARCH CONNECTED 0 0
+ARCH CLOSING 14 1
+INST_ID PROCESS STATUS SEQUENCE# THREAD#
+---------- --------- ------------ ---------- ----------
+ARCH CLOSING 15 1
+RFS IDLE 0 0
+RFS IDLE 0 0
+RFS IDLE 17 1
+rows selected.
+</Code>