Tuesday, July 21, 2020

Dataguard Broker Configuration In Oracle 12c

Dataguard Broker Configuration In Oracle 12c


Primary side:

oracle@mqm-testdb1:~$ export ORACLE_SID=QPROD
oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 12:30:27 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Check the dgbroker config files:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1QPROD.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2QPROD.dat

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb1:~$

DGMGRL> exit
oracle@mqm-testdb1:~$ ps -ef|grep pmon
    grid 12699     1   0   Oct 22 ?          35:04 asm_pmon_+ASM1
    grid 23378     1   0   Oct 22 ?          28:37 mdb_pmon_-MGMTDB
  oracle 28481     1   0   Jan 07 ?          23:02 ora_pmon_OGGSRC1
  oracle 14556     1   0   Feb 18 ?           9:15 ora_pmon_QPROD1
  oracle 24956 11452   0 12:34:49 pts/13      0:00 grep pmon

De-activate the log shipping:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:10:15 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='defer' SCOPE=BOTH sid='*';
System altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
QPROD1

SQL> select password from sys.user$ where name like 'SYS';

PASSWORD
--------------------------------------------------------------------------------
27889DA827C33694

Check the passwordfile parameters:

SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      mqm-testscan.local:1521
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
SQL>

Change the sys password from sql level:

SQL> alter user sys identified by sys_1234;
User altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Check the passwordfile status:

oracle@mqm-testdb1:~$ srvctl config database -d QPROD
Database unique name: QPROD
Database name: QPROD
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/QPROD/spfilerac2.ora
Password file: +DATA/QPROD/PASSWORD/pwdprQPROD/
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: QUATRAC,ANSQ
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: QPROD1,QPROD2
Configured nodes: mqm-testdb1,mqm-testdb2
Database is administrator managed
oracle@mqm-testdb1:~$
oracle@mqm-testdb1:~$
oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:19:37 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Create the passwordfile using orapwd utility:

oracle@mqm-testdb1:~$ orapwd file='+DATA/QPROD/PASSWORD/pwdQPROD' dbuniquename='QPROD' password=sys_1234 entries=10

Add the passwordfile using srvctl:

oracle@mqm-testdb1:~$ srvctl modify database -d QPROD -pwfile '+DATA/QPROD/PASSWORD/pwdQPROD'

oracle@mqm-testdb1:~$ srvctl config database -d QPROD
Database unique name: QPROD
Database name: QPROD
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/QPROD/spfilerac2.ora
Password file: +DATA/QPROD/PASSWORD/pwdQPROD
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: QUATRAC,ANSQ
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: QPROD1,QPROD2
Configured nodes: mqm-testdb1,mqm-testdb2
Database is administrator managed

Test the new passwordfile:

oracle@mqm-testdb1:~$ sqlplus sys/sys_1234@QPROD as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:25:36 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> exit

Activate the log shipping:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:40:07 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='enable' SCOPE=BOTH sid='*';
System altered.

Check the config file status:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:49:21 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1QPROD.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2QPROD.dat

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
QPROD1

Move the config files to ASM:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:49:21 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter system set dg_broker_start=FALSE scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='+DATA/QPROD/DGBROKERCONFIGFILE/dr1QPROD.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='+DATA/QPROD/DGBROKERCONFIGFILE/dr2QPROD.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;
System altered.

Create the configuration:

oracle@mqm-testdb1:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPROD
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION UATRAC_DG AS PRIMARY DATABASE IS QPROD CONNECT IDENTIFIER IS QPROD;
Configuration "UATRAC_DG" created with primary database "QPROD"

DGMGRL> ADD DATABASE QPRODN AS CONNECT IDENTIFIER IS QPRODN MAINTAINED AS PHYSICAL;
Database "QPRODN" added

Enable the configuration:

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> show configuration;

Configuration - UATRAC_DG

  Protection Mode: MaxPerformance
  Members:
  QPROD  - Primary database
    QPRODn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)


Check the status:

oracle@mqm-testdb1:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPROD
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - UATRAC_DG

  Protection Mode: MaxPerformance
  Members:
  QPROD  - Primary database
    QPRODn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 47 seconds ago)

DGMGRL> show database QPROD

Database - QPROD

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    QPROD1
    QPROD2

Database Status:
SUCCESS

DGMGRL> show database QPRODN

Database - QPRODn

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 66.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    QPRODN1 (apply instance)
    QPRODN2

Database Status:
SUCCESS


No comments:

Post a Comment