Please click on link to open document:
October 22, 2020
September 20, 2020
19c New Feature DGMGRL validate database?
Not too long ago, I had blogged about When To Use dgmgrl / vs dgmgrl sys@tns
I believe this is New Feature for 19c (but not 100% certain) may resolved the question above?.
DEMO:
Connect using OS authentication from standby host.
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@ol7-112-dg2 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Sep 20 14:22:13 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> validate database hawk;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
hawk: NO
Validating static connect identifier for the primary database hawk...
ORA-01017: invalid username/password; logon denied
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
DGMGRL> validate database hawk_stby;
Database Role: Physical standby database
Primary Database: hawk
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
hawk : NO
hawk_stby: NO
Validating static connect identifier for the primary database hawk...
ORA-01017: invalid username/password; logon denied
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Log Files Cleared:
hawk Standby Redo Log Files: Cleared
hawk_stby Online Redo Log Files: Not Cleared
hawk_stby Standby Redo Log Files: Available
DGMGRL>
DEMO:
Connect to primary using tns from standby host.
DGMGRL> connect sys/oracle@hawk
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> validate database hawk;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
hawk: NO
Validating static connect identifier for the primary database hawk...
The static connect identifier allows for a connection to database "hawk".
DGMGRL> validate database hawk_stby;
Database Role: Physical standby database
Primary Database: hawk
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
hawk : NO
hawk_stby: NO
Validating static connect identifier for the primary database hawk...
The static connect identifier allows for a connection to database "hawk".
Log Files Cleared:
hawk Standby Redo Log Files: Cleared
hawk_stby Online Redo Log Files: Not Cleared
hawk_stby Standby Redo Log Files: Available
DGMGRL>
This will at least address one example for when to use TNS vs OS authentication for DGMGRL.
September 19, 2020
Monitoring LAG Using DGMGRL Is Nice And Needs Improvements
On November 2, 2017, I had blogged about Monitoring Standby – SQLPlus or DGMGRL
Since the post, I do not recall using dgmgrl much for monitoring lag.
Almost 3 years later and 19c, let’s revisit the topic.
Here is what monitoring lag looks like from SQLPlus.
Notice BLOCK# increased which mean transfer is working.
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 175 8540 0
9059 1 1 N/A MRP0 APPLYING_LOG 175 8540 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 175 8554 0
9059 1 1 N/A MRP0 APPLYING_LOG 175 8554 0
SQL>
For 19c, show configuration lag will provide info on lag and is knowing Lag is 0 seconds good enough?
[oracle@ol7-112-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Sep 19 12:50:58 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 9 seconds ago)
Apply Lag: 0 seconds (computed 9 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 20 seconds ago)
DGMGRL> show configuration lag verbose
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 12 seconds ago)
Apply Lag: 0 seconds (computed 12 seconds ago)
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = ''
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
Using SendQEntries shows LOG_SEQ but RecvQEntries does not.
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1047346434 1 175 09/19/2020 12:32:29 2984164 23586
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
DGMGRL>
Disable apply and compare differences between SQLPlus and DGMGRL.
DGMGRL> edit database hawk_stby set state=APPLY-OFF
> ;
Succeeded.
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 8 seconds ago)
Apply Lag: 0 seconds (computed 8 seconds ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
### From Primary:
*** gv$managed_standby ***
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030 1 1 LNS LNS WRITING 180 1311 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030 1 1 LNS LNS WRITING 180 1314 0
SQL>
### From Standby:
*** gv$archived_log ***
DEST_ID THREAD# APPLIED MAX_SEQ MAX_TIME DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
1 1 NO 179 19-SEP-2020 13:12:25 5 39.93333
1 1 YES 174 19-SEP-2020 12:32:29
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 180 284 0
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589 1 1 LGWR RFS RECEIVING 180 298 0
SQL>
From DGMGRL:
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 6 seconds ago)
Apply Lag: 6 minutes 48 seconds (computed 6 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 31 seconds ago)
DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1047346434 1 180 09/19/2020 13:12:25 2992416 270
DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
PARTIALLY_APPLIED 1047346434 1 175 09/19/2020 12:32:29 09/19/2020 13:12:16 2984164 2992388 24709
NOT_APPLIED 1047346434 1 176 09/19/2020 13:12:16 09/19/2020 13:12:17 2992388 2992393 1
NOT_APPLIED 1047346434 1 177 09/19/2020 13:12:17 09/19/2020 13:12:20 2992393 2992400 2
NOT_APPLIED 1047346434 1 178 09/19/2020 13:12:20 09/19/2020 13:12:20 2992400 2992403 1
NOT_APPLIED 1047346434 1 179 09/19/2020 13:12:20 09/19/2020 13:12:25 2992403 2992416 3
DGMGRL>
APPLY-ON
DGMGRL> edit database hawk_stby set state=APPLY-ON;
Succeeded.
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 3 seconds ago)
Apply Lag: 0 seconds (computed 3 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL> /
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
hawk - Primary database
hawk_stby - Physical standby database
Transport Lag: 0 seconds (computed 4 seconds ago)
Apply Lag: 0 seconds (computed 4 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 6 seconds ago)
Apply Lag: 0 seconds (computed 6 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
It would be nice if show configuration lag is able to provide some high level info frequently asked by management.
What is lag time, how many sequence is the standby behind, what is the apply rate, what is LOG_SEQ at primary and standby?
May 2, 2020
When To Use dgmgrl / vs dgmgrl sys@tns
There’s been discussion on twitter about using dgmgrl / vs dgmgrl sys@tns.
For the most part, I typically use dgmgrl sys@tns only for switch over and fail over.
Update: Should use dgmgrl sys@tns when there is an update to the database role, i.e. switch over, fail over, convert
Even Oracle’s documentation (Doc ID 278641.1) uses dgmgrl /
================================================================================
ORA-1031 Insufficient Privileges During Switchover via DGMGRL (Doc ID 740327.1)
================================================================================
CAUSE
Customer connected to database using o/s authentication from dgmgrl that resulted in ORA-01031
DGMGRL> connect /
SOLUTION
When we perform switchover using DGMGRL that requires database restarts, one must connect to dgmgrl by explicitly specifying a valid SYSDBA username/password and not using the OS Authentication.
DG Broker cannot complete a switchover using ‘connect /’ since it doesn’t have the credentials to restart the standby after shutting it down.
DGMGRL> connect sys/<password>
DGMGRL> connect sys/<pwd>@<connect string>
================================================================================
DGMGRL>CONVERT TO PHYSICAL STANDBY Fails With ORA-01031 (Doc ID 2398886.1)
================================================================================
CAUSE
The connection to the database through dgmgrl utility was not using the TNS_ALIAS
dgmgrl /
SOLUTION
Connect to the primary database through dgmgrl in order to have the convert command successfully executed:
dgmgrl sys@<primary tns_alias>
================================================================================
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)
================================================================================
1. Disable REDO Transport on Primary
1.1 Disable log shipping using DGMGRL.
If DG broker in place it is mandatory to disable log shipping via DG broker.
DGMGRL> connect /
Still do not know when dgmgrl sys@tns is absolutely necessary.
dgmgrl / works fine for monitoring, editing configuration, and modifying transport/apply.
In summary, use what makes you comfortable.
May 1, 2020
12.1 Dataguard Multiple Standby Databases Switchover Using DGMGRL
Followup from How To Create New Standby Using Active Standby Duplication From Existing Standby
There are too many contents to post to blog.
Note: 12c = 12.1 and 12.2
Access 12c Dataguard Multiple Standby Databases Switchover Using DGMGRL for more details.
Thanks to CrossMyP4th on Twitter about Password file not syncing between primary and standby. As of 12.2, this is automatic.
What can happen when password files are not in sync: 12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby
A useful enhancement in 12cR2 is that password files are automatically synchronized in a Data Guard environment: Auto Sync for Password Files in #Oracle 12.2 Data Guard
April 17, 2020
How To Create New Standby Using Active Standby Duplication From Existing Standby
Basically, my insanity is from my experience as Senior Tax Advisor and investment background.
Planning and organization will simplify processes in the long run.
I will share my thoughts for implementing Data Guard having two standby databases.
Data Guard configurations will be for the following hosts: LAX, PHX, SLC
- ORACLE_SID and db_name will be the same value.
- db_unique_name and service_names will be the same value.
- Listener will be LISTENER.
- TNS entries (alias) will be created for service_names and listener.
- Data Guard Broker is being used with Oracle Restart
Hostname: LAX (PRIMARY) : OEL6
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA dg_config=(HAWKA,HAWKB,HAWKC) fal_server='HAWKB,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/LAX Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX dg_config=(LAX,PHX,SLC) fal_server='PHX,SLC'
Hostname: PHX (CURRENT STANDBY) : OEL6
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB dg_config=(HAWKA,HAWKB,HAWKC) fal_server='HAWKA,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/PHX Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX dg_config=(LAX,PHX,SLC) fal_server='LAX,SLC'
Hostname: SLC (NEW STANDBY) : OEL7
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC dg_config=(HAWKA,HAWKB,HAWKC) fal_server='HAWKB,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/SLC Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC dg_config=(LAX,PHX,SLC) fal_server='LAX,PHX'
I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!
I did so for consistency and to avoid having to make too many changes.
As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.
Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”
How does this relate to Data Guard and creating standby databases?
Always create database having db_name different from db_unique_name.
One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”
The demonstration will be using Option #2.
One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.
CONFIGURE ARCHIVELOG DELETION TO NONE
There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51
LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';
PHX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7
Apply patch as required.
SLC : Create pfile and startup nomount database
Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.
$ cat initHAWK.ora *.db_name='HAWK' *.db_flash_cache_size='0' *.db_flash_cache_file='+DATA/flashfile.dat'
FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME
tnsping LAX; tnsping PHX; tnsping SLC tnsping LISTENER_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC
FOR ALL HOSTS : Verify connect using TNS as SYSDBA
sqlplus sys@LAX as sysdba sqlplus sys@PHX as sysdba sqlplus sys@SLC as sysdba
PHX : Perform RMAN Active DB Duplication
$ rman checksyntax @ duplicate.rman
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors
Recovery Manager complete.
$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &
PHX : Add 2nd Standby (SLC) to DG Broker Configuration
Better not to use dgmgrl / as it may be problematic.
$ dgmgrl DGMGRL for Linux: 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@PHX DGMGRL> add database SLC as connect identifier is SLC; DGMGRL> enable configuration DGMGRL> show configuration DGMGRL> show database SLC DGMGRL> exit
SLC : Move spfile to ASM
$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora SQL> create pfile='/tmp/init@.ora' from spfile; File created. SQL> ! ls -l /tmp/init* -rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora'; File created. SQL>
SLC : Add database to Oracle Oracle Restart
$ export ORACLE_SID=HAWK $ export ORACLE_UNQNAME=SLC $ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \ -spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \ -role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE $ srvctl config database -d $ORACLE_UNQNAME $ srvctl start database -d $ORACLE_UNQNAME $ srvctl status database -d $ORACLE_UNQNAME -v $ ps -ef|grep [p]mon
PHX/SLC : Review resource
crsctl stat res -t -w "TYPE = ora.database.type" crsctl stat res -t -w "TYPE = ora.service.type"
LAX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
PHX/SLC: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
REFERENCES:
What’s New in Oracle Data Guard?
New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.
Data Guard Broker 12c Release 1 (12.1.0.2)
The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:
ARCHIVE_LAG_TARGET DB_FILE_NAME_CONVERT LOG_ARCHIVE_DEST_n LOG_ARCHIVE_DEST_STATE_n LOG_ARCHIVE_FORMAT LOG_ARCHIVE_MAX_PROCESSES LOG_ARCHIVE_MIN_SUCCEED_DEST LOG_ARCHIVE_TRACE LOG_FILE_NAME_CONVERT STANDBY_FILE_MANAGEMENT
Configuring RMAN in a Data Guard Environment
CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.
New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.
CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.
UPDATES:
The only consistency with Oracle is that it’s inconsistent.
*** Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations
that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance.
*** How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)
*** Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
If “Duplicate from Active Database” is used , you must connect using ‘TNS alias’ to both, Target and AUXiliary Instance.
Therefore, the AUXiliary instance should have a static listener configured like:
$ rman target sys/@ auxiliary sys/@
To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount.
For this recommendation is to create a ‘Static Listener’ entry for the AUX instance.
The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it’s not registered with listener.
PMON process registers the instances with the listener when they are in MOUNT status, while instances in NOMOUNT status appear in listener as BLOCKED.
In addition, RMAN will shutdown the auxiliary instance several times during the execution.
Therefore, the connection may succeed initially, then fail with the error later in the execution.
*** Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)
*** Bug 11715084 : ACTIVE DUPLICATE SHOULD WORK WHEN CONNECTED TO STANDBY AS SOURCE DB
Updated : 25-Feb-2020. Fixed in Product Version 12.1
*** Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)
Q.E.D.
January 17, 2020
Data Guard Fast-Start Failover Test – Shutdown Standby Host
Data Guard Fast-Start Failover Test – Shutdown Primary Host
Review primary host and start observer:
[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:42:54 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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG1:(SYS@cdb1):PRIMARY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg1 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 13 seconds ago) DGMGRL> enable fast_start failover Enabled. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 12 seconds ago) DGMGRL> validate database cdb1 Database Role: Primary database Ready for Switchover: Yes DGMGRL> validate database cdb1_stby Database Role: Physical standby database Primary Database: cdb1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) DGMGRL> show database cdb1 Database - cdb1 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): cdb1 Database Error(s): ORA-16820: fast-start failover observer is no longer observing this database Database Status: ERROR DGMGRL> show database cdb1_stby Database - cdb1_stby 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: 2.00 KByte/s Real Time Query: ON Instance(s): cdb1 Database Error(s): ORA-16820: fast-start failover observer is no longer observing this database Database Status: ERROR DGMGRL> start observer [P001 01/17 20:46:01.38] Authentication failed. DGM-16979: Unable to log on to the primary or standby database as SYSDBA Failed. DGMGRL> connect sys@cdb1 Password: Connected as SYSDBA. DGMGRL> start observer Observer started
Restart standby host, listener, and database:
resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default poweroff (virtualbox) The VM is powered off. To restart the VM, simply run `vagrant up` resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant up Bringing machine 'default' up with 'virtualbox' provider... ==================================================================== resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default running (virtualbox) The VM is running. To stop this VM, you can run `vagrant halt` to shut it down forcefully, or you can run `vagrant suspend` to simply suspend the virtual machine. In either case, to restart it again, simply run `vagrant up`. ==================================================================== resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant ssh Last login: Fri Jan 17 20:11:35 2020 from 10.0.2.2 [vagrant@ol7-121-dg2 ~]$ sudo su - oracle Last login: Fri Jan 17 20:11:44 UTC 2020 on pts/0 [oracle@ol7-121-dg2 ~]$ . oraenv <<< cdb1 ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@ol7-121-dg2 ~]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JAN-2020 20:53:20 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 17-JAN-2020 20:53:22 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "cdb1_stby_DGMGRL" has 1 instance(s). Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@ol7-121-dg2 ~]$ cd /sf_working/sql [oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:53:38 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SYS@cdb1> startup mount; ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2924928 bytes Variable Size 520097408 bytes Database Buffers 1073741824 bytes Redo Buffers 13848576 bytes Database mounted. SYS@cdb1> @stby.sql Session altered. *** v$database *** DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB UNIQUE_NAME MODE ROLE ARCHIVE STATUS BROKER UNIQUE_NAME ----------- ---------------------- ------------------ ---------- --------------- ---------- --------------- cdb1_stby MOUNTED PHYSICAL STANDBY ENABLED NOT ALLOWED ENABLED cdb1 *** gv$archive_dest *** MOUNT THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS ID -------- -------- ------------------------- ------------ ---------------- -------- ---------- ----- 1 1 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE ARCH 0 1 32 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE RFS 0 *** gv$archive_dest_status *** DATABASE RECOVERY INST_ID DEST_ID STATUS MODE MODE GAP_STATUS ERROR -------- -------- ------------ --------------- ----------------------- --------------- -------------------------------------------------- 1 1 VALID MOUNTED-STANDBY IDLE NONE 1 32 VALID UNKNOWN IDLE NONE *** v$thread *** THREAD# CURRENT LOG SEQUENCE STATUS -------- -------------------- ------------ 1 26 OPEN *** gv$archived_log *** DEST_ID THREAD# APPLIED MAX_SEQ MAX_TIME DELTA_SEQ DETA_MIN -------- -------- --------- -------- -------------------- --------- -------- 1 1 NO 25 17-JAN-2020 20:53:53 2 41.68333 1 1 YES 23 17-JAN-2020 20:12:12 *** v$archive_gap *** no rows selected *** GAP can also be verified using RMAN from STANDBY *** RMAN1 ------------------------------------------------------------ list archivelog from sequence 24 thread 1; *** v$dataguard_stats *** NAME VALUE UNIT ------------------------- ------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval apply lag day(2) to second(0) interval *** gv$managed_standby *** no rows selected SYS@cdb1> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg2 sql]$
Screen output from observer:
DGMGRL> start observer
Observer started
[W000 01/17 20:48:58.27] The primary database has requested a transition to the UNSYNC/LAGGING state.
[W000 01/17 20:48:58.28] Permission granted to the primary database to transition to UNSYNC/LAGGING state.
[W000 01/17 20:50:01.29] The primary database has been in UNSYNC/LAGGING state for 63 seconds.
[W000 01/17 20:51:04.31] The primary database has been in UNSYNC/LAGGING state for 126 seconds.
[W000 01/17 20:52:07.33] The primary database has been in UNSYNC/LAGGING state for 189 seconds.
[W000 01/17 20:53:10.36] The primary database has been in UNSYNC/LAGGING state for 252 seconds.
[W000 01/17 20:54:13.39] The primary database has been in UNSYNC/LAGGING state for 315 seconds.
[W000 01/17 20:54:16.39] The primary database returned to SYNC/NOT LAGGING state.
Validate Data Guard configuration:
[oracle@ol7-121-dg2 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 10 seconds ago) DGMGRL> validate database cdb1 Database Role: Primary database Ready for Switchover: Yes DGMGRL> validate database cdb1_stby Database Role: Physical standby database Primary Database: cdb1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) DGMGRL> exit [oracle@ol7-121-dg2 sql]$
Open database read only:
This is required because database is not register to cluster resource.
[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 21:33: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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG2:(SYS@cdb1):PHYSICAL STANDBY> alter database open read only; Database altered. OL7-121-DG2:(SYS@cdb1):PHYSICAL STANDBY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg2 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show database cdb1_stby Database - cdb1_stby 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: 1.00 KByte/s Real Time Query: ON Instance(s): cdb1 Database Status: SUCCESS DGMGRL> exit [oracle@ol7-121-dg2 sql]$
January 16, 2020
Disable fast_start failover
Data Guard Fast-Start Failover Test
If you recalled, observer was started from ol7-121-dg1 which was standby at the time and is now primary after failover.
[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:21:28 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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg1 sql]$ dgmgrl DGMGRL for Linux: 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@cdb1_stby Password: Connected as SYSDBA. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1_stby - Primary database Warning: ORA-16819: fast-start failover observer not started cdb1 - (*) Physical standby database Warning: ORA-16819: fast-start failover observer not started Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 16 seconds ago) DGMGRL> start observer Observer started 21:27:46.27 Wednesday, January 15, 2020 Initiating Fast-Start Failover to database "cdb1"... Performing failover NOW, please wait... Failover succeeded, new primary is "cdb1" 21:27:51.99 Wednesday, January 15, 2020 21:34:56.44 Wednesday, January 15, 2020 Initiating reinstatement for database "cdb1_stby"... Reinstating database "cdb1_stby", please wait... Reinstatement of database "cdb1_stby" succeeded 21:35:15.40 Wednesday, January 15, 2020
Kill observer process from OS:
[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 16 00:32:04 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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG1:(SYS@cdb1):PRIMARY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg1 sql]$ [oracle@ol7-121-dg1 sql]$ ps -ef|grep dgmgrl oracle 10381 32397 0 00:32 pts/1 00:00:00 grep --color=auto dgmgrl oracle 31831 30778 0 Jan15 pts/0 00:00:01 dgmgrl [oracle@ol7-121-dg1 sql]$ kill -9 31831
Output from Observer session:
DGMGRL> start observer Observer started 21:27:46.27 Wednesday, January 15, 2020 Initiating Fast-Start Failover to database "cdb1"... Performing failover NOW, please wait... Failover succeeded, new primary is "cdb1" 21:27:51.99 Wednesday, January 15, 2020 21:34:56.44 Wednesday, January 15, 2020 Initiating reinstatement for database "cdb1_stby"... Reinstating database "cdb1_stby", please wait... Reinstatement of database "cdb1_stby" succeeded 21:35:15.40 Wednesday, January 15, 2020 Killed ***** [oracle@ol7-121-dg1 sql]$
Disable fast_start failover:
[oracle@ol7-121-dg1 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database Error: ORA-16820: fast-start failover observer is no longer observing this database cdb1_stby - (*) Physical standby database Error: ORA-16820: fast-start failover observer is no longer observing this database Fast-Start Failover: ENABLED Configuration Status: ERROR (status updated 31 seconds ago) DGMGRL> disable fast_start failover Disabled. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 12 seconds ago) DGMGRL> exit [oracle@ol7-121-dg1 sql]$
January 15, 2020
Data Guard Fast-Start Failover Test – Shutdown Primary Host
Note: Primary Database: cdb1_stby is because failover was previously performed.
This also demonstrate why it may not be a good idea to suffix stby for standby database.
Review Data Guard using sqlplus:
OL7-121-DG2:(SYS@cdb1):PRIMARY> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string cdb1 db_unique_name string CDB1_STBY pdb_file_name_convert string OL7-121-DG2:(SYS@cdb1):PRIMARY> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string cdb1 OL7-121-DG2:(SYS@cdb1):PRIMARY> ******************************************************************************** OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string cdb1 db_unique_name string cdb1 pdb_file_name_convert string OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string cdb1_stby OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY>
Review Data Guard configuration:
DGMGRL> show configuration verbose Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1_stby - Primary database cdb1 - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'USER' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: SUCCESS ******************************************************************************** DGMGRL> show database verbose cdb1_stby Database - cdb1_stby Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): cdb1 Properties: DGConnectIdentifier = 'cdb1_stby' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = 'cdb1' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_stby_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS ******************************************************************************** DGMGRL> show database verbose cdb1 Database - cdb1 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: 2.00 KByte/s Active Apply Rate: 0 Byte/s Maximum Apply Rate: 0 Byte/s Real Time Query: ON Instance(s): cdb1 Properties: DGConnectIdentifier = 'cdb1' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = 'cdb1_stby' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS DGMGRL>
Validate Data Guard configuration:
DGMGRL> validate database verbose cdb1_stby Database Role: Primary database Ready for Switchover: Yes Capacity Information: Database Instances Threads cdb1_stby 1 1 Temporary Tablespace File Information: cdb1_stby TEMP Files: 1 Flashback Database Status: cdb1_stby: On Data file Online Move in Progress: cdb1_stby: No Transport-Related Information: Transport On: Yes Log Files Cleared: cdb1_stby Standby Redo Log Files: Cleared Automatic Diagnostic Repository Errors: Error cdb1_stby No logging operation NO Control file corruptions NO System data file missing NO System data file corrupted NO System data file offline NO User data file missing NO User data file corrupted NO User data file offline NO Block Corruptions found NO ******************************************************************************** DGMGRL> validate database verbose cdb1 Database Role: Physical standby database Primary Database: cdb1_stby Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Capacity Information: Database Instances Threads cdb1_stby 1 1 cdb1 1 1 Temporary Tablespace File Information: cdb1_stby TEMP Files: 3 cdb1 TEMP Files: 3 Flashback Database Status: cdb1_stby: On cdb1: On Data file Online Move in Progress: cdb1_stby: No cdb1: No Standby Apply-Related Information: Apply State: Running Apply Lag: 0 seconds (computed 1 second ago) Apply Delay: 0 minutes Transport-Related Information: Transport On: Yes Gap Status: No Gap Transport Lag: 0 seconds (computed 1 second ago) Transport Status: Success Log Files Cleared: cdb1_stby Standby Redo Log Files: Cleared cdb1 Online Redo Log Files: Cleared cdb1 Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb1_stby) (cdb1) 1 3 4 Sufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb1) (cdb1_stby) 1 3 4 Sufficient SRLs Current Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (cdb1_stby) (cdb1) 1 50 MBytes 50 MBytes Future Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (cdb1) (cdb1_stby) 1 50 MBytes 50 MBytes Apply-Related Property Settings: Property cdb1_stby Value cdb1 Value DelayMins 0 0 ApplyParallel AUTO AUTO Transport-Related Property Settings: Property cdb1_stby Value cdb1 Value LogXptMode ASYNC ASYNC RedoRoutes Dependency DelayMins 0 0 Binding optional optional MaxFailure 0 0 MaxConnections 1 1 ReopenSecs 300 300 NetTimeout 30 30 RedoCompression DISABLE DISABLE LogShipping ON ON Automatic Diagnostic Repository Errors: Error cdb1_stby cdb1 No logging operation NO NO Control file corruptions NO NO SRL Group Unavailable NO NO System data file missing NO NO System data file corrupted NO NO System data file offline NO NO User data file missing NO NO User data file corrupted NO NO User data file offline NO NO Block Corruptions found NO NO DGMGRL>
Validate Data Guard connectivity from all hosts:
[oracle@ol7-121-dg2 sql]$ dgmgrl DGMGRL for Linux: 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@cdb1 Password: Connected as SYSDBA. DGMGRL> connect sys@cdb1_stby Password: Connected as SYSDBA. DGMGRL> exit [oracle@ol7-121-dg2 sql]$ ******************************************************************************** [oracle@ol7-121-dg1 sql]$ dgmgrl DGMGRL for Linux: 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@cdb1 Password: Connected as SYSDBA. DGMGRL> connect sys@cdb1_stby Password: Connected as SYSDBA. DGMGRL> exit [oracle@ol7-121-dg1 sql]$
Start Data Guard observer from standby:
Note: this is not good practice for real world and only for testing purposes only.
oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:21:28 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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG1:(SYS@cdb1):PHYSICAL STANDBY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg1 sql]$ dgmgrl DGMGRL for Linux: 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@cdb1_stby Password: Connected as SYSDBA. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1_stby - Primary database Warning: ORA-16819: fast-start failover observer not started cdb1 - (*) Physical standby database Warning: ORA-16819: fast-start failover observer not started Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 16 seconds ago) DGMGRL> start observer Observer started DGMGRL>
Shutdown primary host:
[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:26:51 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, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG2:(SYS@cdb1):PRIMARY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg2 sql]$ logout [vagrant@ol7-121-dg2 ~]$ logout Connection to 127.0.0.1 closed. resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant halt ==> default: Attempting graceful shutdown of VM... resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $
Failover succeeded:
DGMGRL> start observer
Observer started
21:27:46.27 Wednesday, January 15, 2020
Initiating Fast-Start Failover to database "cdb1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1"
21:27:51.99 Wednesday, January 15, 2020
Review Data Guard configuration:
[oracle@ol7-121-dg1 ~]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database Warning: ORA-16829: fast-start failover configuration is lagging cdb1_stby - (*) Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 41 seconds ago) DGMGRL>
Start primary host:
resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default poweroff (virtualbox) The VM is powered off. To restart the VM, simply run `vagrant up` resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant up resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default running (virtualbox) The VM is running. To stop this VM, you can run `vagrant halt` to shut it down forcefully, or you can run `vagrant suspend` to simply suspend the virtual machine. In either case, to restart it again, simply run `vagrant up`. resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $
Start listener:
[oracle@ol7-121-dg2 ~]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JAN-2020 21:33:11
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 15-JAN-2020 21:33:12
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1_stby_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-121-dg2 ~]$
Startup mount database:
[oracle@ol7-121-dg2 sql]$ ps -ef|grep pmon oracle 17762 17567 0 21:34 pts/0 00:00:00 grep --color=auto pmon [oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:34:09 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SYS@cdb1> startup mount; ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2924928 bytes Variable Size 520097408 bytes Database Buffers 1073741824 bytes Redo Buffers 13848576 bytes Database mounted. SYS@cdb1> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ol7-121-dg2 sql]$
Review Data Guard configuration:
[oracle@ol7-121-dg2 sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration ORA-16795: the standby database needs to be re-created Configuration details cannot be determined by DGMGRL DGMGRL>
Review Observer:
DGMGRL> start observer
Observer started
21:27:46.27 Wednesday, January 15, 2020
Initiating Fast-Start Failover to database "cdb1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1"
21:27:51.99 Wednesday, January 15, 2020
21:34:56.44 Wednesday, January 15, 2020
Initiating reinstatement for database "cdb1_stby"...
Reinstating database "cdb1_stby", please wait...
Reinstatement of database "cdb1_stby" succeeded
21:35:15.40 Wednesday, January 15, 2020
Review and Validate Data Guard configuration:
DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Warning: ORA-16829: fast-start failover configuration is lagging Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 54 seconds ago) DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 24 seconds ago) DGMGRL> validate database cdb1 Database Role: Primary database Ready for Switchover: Yes DGMGRL> validate database cdb1_stby Database Role: Physical standby database Primary Database: cdb1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) DGMGRL>
June 28, 2019
DGMGRL Using Help To Learn About New Validate Features
Wouldn’t be nicer and much better if Oracle would add (NF) for new features to help syntax?
DGMGRL for Linux: Release 12.2.0.1.0
[oracle@db-fs-1 bin]$ ./dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jun 28 17:49:16 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orclcdb"
Connected as SYSDG.
DGMGRL> help validate
Performs an exhaustive set of validations for a member
Syntax:
VALIDATE DATABASE [VERBOSE] <database name>;
VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
OUTPUT=<file name>;
VALIDATE DATABASE [VERBOSE] <database name> SPFILE;
VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
[WHEN PRIMARY IS <database name>];
DGMGRL>
DGMGRL for Linux: Release 18.0.0.0.0
[oracle@ADC6160274 GDS]$ dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Fri Jun 28 15:54:36 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "chi"
Connected as SYSDG.
DGMGRL> help validate
Performs an exhaustive set of validations for a member
Syntax:
VALIDATE DATABASE [VERBOSE] <database name>;
VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
OUTPUT=<file name>;
VALIDATE DATABASE [VERBOSE] <database name> SPFILE;
VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
[WHEN PRIMARY IS <database name>];
VALIDATE NETWORK CONFIGURATION FOR { ALL | <member name> }; [*** NF ***]
VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | <database name> }; [*** NF ***]
DGMGRL>
validate network configuration
DGMGRL> validate network configuration for all; Connecting to instance "sales" on database "sfo" ... Connected to "sfo" Checking connectivity from instance "sales" on database "sfo to instance "sales" on database "chi"... Succeeded. Connecting to instance "sales" on database "chi" ... Connected to "chi" Checking connectivity from instance "sales" on database "chi to instance "sales" on database "sfo"... Succeeded. Oracle Clusterware is not configured on database "sfo". Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "sfo". Oracle Clusterware is not configured on database "chi". Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "chi".
validate static connect identifier
DGMGRL> validate static connect identifier for all; Oracle Clusterware is not configured on database "sfo". Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "sfo". Oracle Clusterware is not configured on database "chi". Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "chi". DGMGRL>
