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