Friday, August 25, 2017

Using DBCA to create a standby database

In Oracle 12.2 there is a new feature added to the dbca commando. One of the new feature is creating a standby database [DBCA Standby Creation].  At the Standby server install the Oracle Software and start a listener. And start the command "dbca -silent -createDuplicateDB -createAsStandby".

This dbca command will create a standby database,and  nothing more.
Get the new standby database operational as a standby database additional steps must be performed.
The steps are conform the steps  for building a standby databases.

Lets play with the DBCA option for creating a standby database.

dbca options

dbca -help


createDuplicateD -help


Prepare the environment :


  • Setup a TNSNAMES 
  • Setup static database services 
  • Add standby logs 
  • Set database parameters


TNSNAMES.ORA
CDB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb2)
    )
  )

CDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb1)
    )
  )


LISTENER.ORA
SID_LIST_LISTENER=
   (SID_LIST=
 (SID_DESC=
          (GLOBAL_DBNAME=cdb1_dgmgrl)
          (SID_NAME=cdb1)   
          (ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/db_1)
         )
 (SID_DESC=
          (GLOBAL_DBNAME=cdb2_dgmgrl)
          (SID_NAME=cdb2)   
          (ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/db_1)
         )
        )

Database settings and configuration
On the CDB1 Primary database

alter database force logging;
alter database flashback on;
-- Activate archivelog
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
alter database force logging;
alter database flashback on;
alter database add standby logfile group 4 size 200M;
alter database add standby logfile group 5 size 200M;
alter database add standby logfile group 6 size 200M;
alter database add standby logfile group 7 size 200M;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb1,cdb2)';
alter system set FAL_SERVER=cdb2;
alter system set STANDBY_FILE_MANAGEMENT='AUTO';
alter system set DG_BROKER_START=true;

Create the standby dtabase with DBCA

dbca -silent -createDuplicateDB -gdbName cdb1 -primaryDBConnectionString oel7:1521/cdb1 -sysPassword oracle -sid cdb2 -createAsStandby -dbUniquename cdb2 -initParams FAL_SERVER=cdb1

Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
100% complete
Look at the log file "/home/oracle/app/cfgtoollogs/dbca/cdb2/cdb1.log" for further details

Check The role of the new cdb2 database
Logon to cdb2 database as sysdba

select open_mode,DATABASE_ROLE from v$database;
OPEN_MODE      DATABASE_ROLE
-------------------- ----------------
READ ONLY      PHYSICAL STANDBY

Setup Dataguard configuration

dgmgrl connect sysdg@cdb1   
CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'cdb1' CONNECT IDENTIFIER IS cdb1;
ADD DATABASE 'cdb2' AS CONNECT IDENTIFIER IS cdb2;
ENABLE CONFIGURATION;
EDIT DATABASE 'cdb1' SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE 'cdb2' SET PROPERTY 'LogXptMode'='SYNC';
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL switchover tests

dgmgrl sysdg@oel7/cdb1_CFG
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Aug 25 00:41:17 2017

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

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb2"
Connected as SYSDG.
Use "help " to see syntax for individual commands

DGMGRL> show instance verbose cdb1

Instance 'cdb1' of database 'cdb1'

  Host Name: oel7.joords.nl
  PFILE:     
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7.joords.nl)(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)'

  Log file locations:
    Alert log               : /home/oracle/app/diag/rdbms/cdb1/cdb1/trace/alert_cdb1.log
    Data Guard Broker log   : /home/oracle/app/diag/rdbms/cdb1/cdb1/trace/drccdb1.log

Instance Status:
SUCCESS

DGMGRL> SHOW CONFIGURATION WHEN PRIMARY IS cdb2

Configuration when cdb2 is primary - DRSolution

  Members:
  cdb2 - Primary database
    cdb1 - Physical standby database 

DGMGRL> VALIDATE DATABASE verbose cdb1

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    cdb1:  On

  Capacity Information:
    Database  Instances        Threads        
    cdb1      1                1              

  Managed by Clusterware:
    cdb1:  NO             
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    cdb1 TEMP Files:  1

  Data file Online Move in Progress:
    cdb1:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    cdb1 Standby Redo Log Files:  Cleared

  Automatic Diagnostic Repository Errors:
    Error                       cdb1   
    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 cdb2

  Database Role:     Physical standby database
  Primary Database:  cdb1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    cdb1:  On
    cdb2:  Off

  Capacity Information:
    Database  Instances        Threads        
    cdb1      1                1              
    cdb2      1                1              

  Managed by Clusterware:
    cdb1:  NO             
    cdb2:  NO             
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    cdb1 TEMP Files:  1
    cdb2 TEMP Files:  1

  Data file Online Move in Progress:
    cdb1:  No
    cdb2:  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 Standby Redo Log Files:  Cleared
    cdb2 Online Redo Log Files:   Cleared
    cdb2 Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (cdb1)                  (cdb2)                               
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (cdb2)                  (cdb1)                               
    1         3                       2                       Insufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (cdb1)                    (cdb2)                   
    1          200 MBytes                200 MBytes               

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (cdb2)                    (cdb1)                   
    1          200 MBytes                200 MBytes               

  Apply-Related Property Settings:
    Property                        cdb1 Value               cdb2 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        cdb1 Value               cdb2 Value
    LogXptMode                      SYNC                     SYNC
    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     cdb2   
    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> SWITCHOVER TO cdb2
Performing switchover NOW, please wait...
New primary database "cdb2" is opening...
Operation requires start up of instance "cdb1" on database "cdb1"
Starting instance "cdb1"...
ORACLE instance started.
Database mounted.
Connected to "cdb1"
Switchover succeeded, new primary is "cdb2"
DGMGRL> show configuration verbose 

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb2 - 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'
    ConfigurationWideServiceName    = 'cdb1_CFG'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> VALIDATE DATABASE verbose cdb2

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    cdb2:  Off

  Capacity Information:
    Database  Instances        Threads        
    cdb2      1                1              

  Managed by Clusterware:
    cdb2:  NO             
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    cdb2 TEMP Files:  1

  Data file Online Move in Progress:
    cdb2:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    cdb2 Standby Redo Log Files:  Cleared

  Automatic Diagnostic Repository Errors:
    Error                       cdb2   
    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:  cdb2

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    cdb2:  Off
    cdb1:  On

  Capacity Information:
    Database  Instances        Threads        
    cdb2      1                1              
    cdb1      1                1              

  Managed by Clusterware:
    cdb2:  NO             
    cdb1:  NO             
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    cdb2 TEMP Files:  1
    cdb1 TEMP Files:  1

  Data file Online Move in Progress:
    cdb2:  No
    cdb1:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds (computed 0 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    cdb2 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       
              (cdb2)                  (cdb1)                               
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (cdb1)                  (cdb2)                               
    1         3                       2                       Insufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (cdb2)                    (cdb1)                   
    1          200 MBytes                200 MBytes               

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo    
               Log File Size             Log File Size            
               (cdb1)                    (cdb2)                   
    1          200 MBytes                200 MBytes               

  Apply-Related Property Settings:
    Property                        cdb2 Value               cdb1 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        cdb2 Value               cdb1 Value
    LogXptMode                      SYNC                     SYNC
    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                       cdb2     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> switchover to cdb1
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb1"
Connecting ...
Connected to "cdb1"
Connected as SYSDG.
New primary database "cdb1" is opening...
Operation requires start up of instance "cdb2" on database "cdb2"
Starting instance "cdb2"...
ORACLE instance started.
Database mounted.
Connected to "cdb2"
Switchover succeeded, new primary is "cdb1"
DGMGRL> show configuration verbose

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1 - Primary database
    cdb2 - Physical standby database 
      Warning: ORA-16854: apply lag could not be determined

  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    = 'cdb1_CFG'

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show configuration verbose

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  cdb1 - Primary database
    cdb2 - 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'
    ConfigurationWideServiceName    = 'cdb1_CFG'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 

DBCA  Standby Creation on a mulitenant database configuration

Using the dbca standby creation on a multitenant database result for me in a error:
[FATAL] [DBT-16057] Specified primary database is a container database (CDB).
   CAUSE: Duplicate database operation is supported only for non container databases.

Starting with Oracle 12.2.0.1 the Database Configuration Assistant (DBCA) can also be used as a simple command-line method to create an Oracle Data Guard physical standby database. The DBCA duplicate command in this release can only create a single instance standby database from single instance primary database. It does not support RAC or multitenant configurations at present.