Thinking Out Loud

October 22, 2020

Validate And Perform 19c Data Guard Switchover

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 6:37 pm

Please click on link to open document:

Validate And Perform 19c Data Guard Switchover

September 20, 2020

19c New Feature DGMGRL validate database?

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 2:35 pm

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

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 1:53 pm

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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 3:41 pm

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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 2:28 am

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

12.1 LOG_ARCHIVE_TRACE

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

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)

12c: Data Guard Physical Standby – Managing password files in a RAC Physical Standby (Doc ID 1984091.1)

April 17, 2020

How To Create New Standby Using Active Standby Duplication From Existing Standby

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 8:46 pm

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

  1. ORACLE_SID and db_name will be the same value.
  2. db_unique_name and service_names will be the same value.
  3. Listener will be LISTENER.
  4. TNS entries (alias) will be created for service_names and listener.
  5. 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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 9:41 pm

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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 12:54 am

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&gt; 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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 10:37 pm

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

Filed under: 18c,Dataguard,dgmgrl — mdinh @ 3:57 pm

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>
Next Page »

Blog at WordPress.com.