Oracle DBA Day to Day Scripts: Step by Step dataguard implementation

How to Setup Active DataGuard on Exadata (Doc ID 1580796.1)

Purpose of this article is too provide step by step instructions for deploying Active DataGuard Configuration within an Exadata Environment.

Transport methods within an Exadata Environment depending upon how the environment is architected can utilize one of 3 transport methods -->

1). Standard Client Network which would be used by SCAN/VIP Network, typically 1gE
2). IB/SDP Protocol Deployment, this is only applicable if the Primary/Standby are connected via Spine Switch's. Located in the same data center physically connected to each other.

3). High Speed Transport using 10gE Interface SFP Module over LAN or WAN

Article assumes that Primary and Standby sites are both running on Exadata Hardware.
This does not need to be exact Exadata Image release however following best practices ideally we would be running the exact same image.

With respect too Oracle Enterprise Linux release this can differ again though not ideal.

Pre-requisite is that Grid Insfrastructure is the same version and the Oracle RDBMS must be the same version and release at both primary/standby sites.

Software is already installed at both Primary/Standby environments with the same Exadata Bundle Patch's applied to both GRID & RDBMS Oracle along with any required overlay patch's.

Verify this at both ends with 'opatch lsinventory'

SOLUTION

SETUP CONFIGURATION

Primary is 2 Nodes --->
Host Names # exadbmel01.au.oracle.com exadbmel02.au.oracle.com
SID Names # AM1, AM2
DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1

$ srvctl status database -d AM
Instance AM1 is running on node exadbmel01
Instance AM2 is running on node exadbmel02

Standby is 2 Nodes --->
Host Names # exadbmel03.au.oracle.com exadbmel04.au.oracle.com
SID Names # AMADG1, AMADG2
DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1

Here no DB is configured yet, all we have is the s/ware installed.

On the primary Database

1). Enable force logging..

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

2). Create standby redo logs

You will need to create sufficient Standby Redo Logs to cater for redo apply.
This needs to equal the same group and member amount of REDO logs you have configured on your primary.

SQL> alter database add standby logfile thread 1
  2  group 9 ('+DATA_EXA','+DBFS_DG')  size 50M,
  3  group 10 ('+DATA_EXA','+DBFS_DG')  size 50M;

Database altered.

SQL> alter database add standby logfile thread 2
  2  group 11 ('+DATA_EXA','+DBFS_DG')  size 50M,
  3  group 12 ('+DATA_EXA','+DBFS_DG')  size 50M;

Database altered.

On the primary & standby Database

3). Setup Entries in tnsnames.ora

Pre-requisite here is that a listener exists on all nodes in Primary/Standby hosts
Before defining the below sql*net entries use netca to create the require listeners.

$ cat tnsnames.ora

AM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = AM)
    )
  )

AMADG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = AMADG)
         (UR=A)
    )
  )

On the Primary Database Node#1

4). Copy password file from Primary -> Standby

$ cd $ORACLE_HOME/dbs
$ cp orapwAM1 oracle@exadbmel03:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG1
$ cp orapwAM1 oracle@exadbmel04:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG2

On the standby Database

5). Setup a dummy pfile in $ORACLE_HOME/dbs on node #1 and node #2

Name the file as initAMADG.ora
You will later create a initAMADG1.ora & initAMADG2.ora accordingly with an spfile pointer.

initAMADG.ora
~~~~~~~~~~~~~~
*.db_cache_size=2684354560
*.java_pool_size=16777216
*.large_pool_size=16777216
AMADG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
AMADG2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.pga_aggregate_target=1157627904
*.sga_target=3472883712
*.shared_io_pool_size=0
*.shared_pool_size=721420288
*.streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/AMADG/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.2.0'
*.control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl'
*.db_block_size=8192
*.db_cache_size=2684354560
*.db_create_file_dest='+DATA_EXA'
*.db_domain=''
*.db_file_name_convert='AM','AMADG'
*.db_name='AM'
*.db_recovery_file_dest='+DBFS_DG'
*.db_recovery_file_dest_size=10737418240
*.db_unique_name='AMADG'
*.dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat'
*.dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AMADGXDB)'
*.fal_server='am'
AMADG1.instance_number=1
AMADG2.instance_number=2
*.java_pool_size=16777216
*.large_pool_size=16777216
*.log_archive_config='dg_config=(AMADG,am)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_format='%t_%s_%r.dbf'
AMADG2.log_archive_format='%t_%s_%r.dbf'
AMADG1.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
AMADG2.log_archive_trace=0
AMADG1.log_archive_trace=0
*.log_file_name_convert='AM','AMADG'
*.open_cursors=300
*.processes=150
*.remote_listener='scan-exa:1521'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=721420288
*.standby_file_management='AUTO'
*.streams_pool_size=0
AMADG3.thread=3
AMADG1.thread=1
AMADG2.thread=2
AMADG1.undo_tablespace='UNDOTBS1'
AMADG2.undo_tablespace='UNDOTBS2'

On the standby DB

6). Using the dummy parameter file startup the instance's

Standby Node1, placing the file as follows in $OH/dbs location

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initAMADG.ora

ORACLE instance started.

Total System Global Area 3457368064 bytes
Fixed Size                  2233272 bytes
Variable Size             754977864 bytes
Database Buffers         2684354560 bytes
Redo Buffers               15802368 bytes
SQL>

Repeat on node 2 and start the instance in nomount as well.

On the primary & standby Database

7). Test Connections

On both Primary and Standby Test Sql*Net Connections

$ sqlplus sys/welcome1@AMADG as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 29 15:10:47 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

Repeat this connection across all nodes for the Primary AM connect string and the standby AMADG connect string.

On the Primary Database

8). Perform an RMAN Duplicate

From Primary --> Connect to RMAN
------------------------------------

[oracle@exadbmel01 dbhome_2]$ rman target sys@AM auxiliary sys@AMADG

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 1 09:46:27 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: AM (DBID=939288056)
auxiliary database Password:
connected to auxiliary database: AM (not mounted)

RMAN>


RMAN> duplicate target database for standby from active database
spfile
parameter_value_convert 'AM','AMADG'
set db_unique_name='AMADG'
set db_file_name_convert='AM','AMADG'
set log_file_name_convert='AM','AMADG'
set control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl'
set standby_file_management='AUTO'
set db_recovery_file_dest='+DBFS_DG'
set audit_file_dest='/u01/app/oracle/admin/AMADG/adump'

On the Standby Database

9). Validate the Database Role on either AMADG1 or AMADG2

SQL> select name, database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
AM        PHYSICAL STANDBY

On the Standby Database

10). Create spfile from the test pfile created

SQL>  create spfile='+DATA_EXA' from pfile='/u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/initAMADG1.ora';

Then connected to 'ASMCMD'
copied spfile location +DATA_EXA/AMADG/PARAMETERFILE too +DATA_EXA/AMADG/spfileamadg.ora

Then made sure init.ora was updated to have correct SPFILE pointer entry..

$ cat $ORACLE_HOME/dbs/initAMADG1.ora
+DATA_EXA/amadg/spfileamadg.ora

$ cat $ORACLE_HOME/dbs/initAMADG2.ora
+DATA_EXA/amadg/spfileamadg.ora

On the Standby Database

11). Verify both Standby Instances can start in mount mode.

Its at this point we verify group services registration with the underlying CSSD daemon.
Please note you'll need to do this with sqlplus as we are yet to register the standby resource with
Oracle Clusterware.

Once you have started them both make sure you see both instances

SQL> select instance_number, instance_name from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 AMADG1
              2 AMADG2

On the Primary & Standby Database

12). Define Log Transport Method to be used.

In Step 3 we used the example of creating the DataGuard configuration on the SCAN Client Network.
You now have the option to change this if architecturally intended to use the 10gE or IB network.

Verify the current deployment is on the scan, which in my example would be bondeth0.

E.g.

HOST String used in tnsnames.ora is 'SCAN-EXA'

$ srvctl config scan
SCAN name: scan-exa, Network: 1/10.187.80.0/255.255.254.0/bondeth0
SCAN VIP name: scan1, IP: /scan-exa/10.187.80.174
SCAN VIP name: scan2, IP: /scan-exa/10.187.80.34
SCAN VIP name: scan3, IP: /scan-exa/10.187.80.35



% cat /proc/net/bonding/bondeth0 |grep eth
Currently Active Slave: eth1
Slave Interface: eth1
Slave Interface: eth2

Lets Proceed with transport method options -->

a). Using the Scan Client Network (default as above)
b). Using 10gE Network Interface SFP Module over WAN or LAN
c). Using TCPoIB on the Infiniband Network LAN

If your choosing Option "A" then please proceed to Step 13 as their is no need to change the transport method.
If your choosing Option "B" then proceed to step 12a
If your choosing Option "C" then proceed to step 12b

12.a) Defining Log Transport on 10gE

The 10GigE interfaces on an X2-2 or X3-2 are identified as eth4 or eth5, while on an X2-8 they are identified as eth8 through eth15. Use the ethtool command to probe each 10GigE interface.

E.g.
# ethtool eth8 |grep -i speed
        Speed: 10000Mb/s

Once you've identified the correct interface and confirmed connectivity you can now setup additional
network for TCP Support over 10gE.

Please refer to article -->

How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure (Doc ID 1063571.1)

Once you have defined the 10gE net2 resource, setup connect strings as we did in step 3 but you'll need
to change the HOST address to be the 10gE hostnames defined.

Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba "

12.b). Defining Log Transport on IB using TCPoIB Communication

Please refer to article -->
Setup Listener on Infiniband Network using both SDP and TCP Protocol (Doc ID 1580584.1)

You will need to implement the requires steps outlined in 1580584.1 on both Primary and Standby Environments once this complete you can then add desired sql*Net connect strings to be used.

E.g.

AMIB =
  (DESCRIPTION =
        (LOAD_BALANCE=on)
        (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel01-ib)(PORT = 1522))
        (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel02-ib)(PORT = 1522))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = AM)
        ))

AMADGIB =
  (DESCRIPTION =
        (LOAD_BALANCE=on)
        (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel03-ib)(PORT = 1522))
        (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel04-ib)(PORT = 1522))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = AMADG)
        ))

Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba "

Finally once you have defined either transport methods ensure you change 'log_archive_dest_2'
parameter too point to the correct service name for either of the options in 12a or 12b.

E.g.

SQL>  alter system set LOG_ARCHIVE_DEST_2='SERVICE=AMADGIB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=AMADG' scope=both;

On the Primary & Standby Database

13>. Enable Data Guard Broker

On Primary, and Standby

SQL> alter system set dg_broker_start=true scope=both sid='*';

System altered.

ON PRIMARY,   Node #1

SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AM.dat' scope=spfile;

System altered.

SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AM.dat' scope=spfile;

System altered.

ON STANDBY,  Node #1

SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat' scope=spfile;

System altered.

SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat' scope=spfile;

System altered.

Then Copy the Broker Files connecting as GRID s/ware owner using ASMCMD in GRID Home When first enabling the broker it creates config files locally (file system) we can place these on shared storage in this case +ASM diskgroup.

Primary DB on Node 1

$ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat +DATA_EXA/AM/BROKER/dr1AM.dat
copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat -> +DATA_EXA/AM/BROKER/dr1AM.dat

$ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat +DATA_EXA/AM/BROKER/dr2AM.dat
copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat -> +DATA_EXA/AM/BROKER/dr2AM.dat


Standby DB on Node 1

ASMCMD> cd +DATA_EXA/AMADG/BROKER
ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat .
copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr1AMADG.dat

ASMCMD> cd +DATA_EXA/AMADG/BROKER
ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat .
copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr2AMADG.dat

Restart the broker for changes to take effect.

On Primary, and Standby

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL> alter system set dg_broker_start=true scope=both sid='*';

System altered.

Primary DB on Node 1

DGMGRL> create configuration AM_Active_DG as primary database is AM connect identifier is AM;
Configuration "am_active_dg" created with primary database "am"

DGMGRL> add database AMADG as connect identifier is AMADG maintained as physical;
Database "amadg" added

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - am_active_dg

  Protection Mode: MaxPerformance
  Databases:
    am    - Primary database
    amadg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

On the Standby Database

14). Register Standby Database Resources with Clusterware

As the oracle software owner

$ srvctl add database -d AMADG -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATA_EXA/amadg/spfileamadg.ora -r physical_standby
$ srvctl add instance -d AMADG -i AMADG1 -n exadbmel03
$ srvctl add instance -d AMADG -i AMADG2 -n exadbmel04

Steps are complete! Restart of the Standby Resource using srvctl will automatically start MRP with one of the active instance threads.

15. Additional Action to define static listener registration so that the Data Guard Broker can successfully restart the instances.

Please refer to article --> Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

NOTE:1063571.1 - How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure
NOTE:1580584.1 - Setup Listener on Infiniband Network using both SDP and TCP Protocol
NOTE:1387859.1 - Oracle Data Guard Broker and Static Service Registration

Oracle DBA Day to Day Scripts

Friday, January 22, 2021

Step by Step dataguard implementation

SOLUTION

No comments:

Post a Comment

How to Trouble shoot Logfile_sync wait event

Translate

Followers