A! Help: physical standby

Showing posts with label physical standby. Show all posts

Thursday, September 12, 2024

Restore Standby Database from Standby Backups

This post shows the steps for full restoring (controlfile + data files) for a standby database using standby database backups. The same could be achieved using restore from service (2283978.1). However, this method is useful when the database size is large and high network latencies are invovled.

1. As the first step disable log apply and transport.

DGMGRL> edit database fsfodr set state='apply-off';
Succeeded.
DGMGRL> edit database fsfopr set state='transport-off';
Succeeded.
DGMGRL>

2. Start the standby database in nomount mode and restore the standby controlfile.

$ rman target /
RMAN > startup nomount

RMAN> restore standby controlfile from '/opt/backup/fsfodr/full_c-1245564449-20230913-02.ctl';

Starting restore at 13-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2836 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/FSFODR/CONTROLFILE/current.341.1147435431
output file name=+FRA/FSFODR/CONTROLFILE/current.573.1147435431
Finished restore at 13-SEP-23

3. Mount the database.

RMAN> alter database mount;

4. Since the standby controfile was restored from a backup taken on standby database no need to catalog backup file location. The controlfile is aware of the backup locations. Run a restore and recover statements.

RMAN> run {
2> restore database;
3> recover database;
4> }

5. Clear the online logfiles on the standby database.

SQL> begin
for log_cur in ( select group# group_no from v$log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                 2635375 13-SEP-23      2635378 13-SEP-23          0
         2          1          0  104857600        512          2 YES UNUSED                 2635378 13-SEP-23      2636371 13-SEP-23          0
         5          1          0  104857600        512          2 YES UNUSED                 2636825 13-SEP-23   9.2954E+18                    0
         4          1          0  104857600        512          2 YES UNUSED                 2636470 13-SEP-23      2636825 13-SEP-23          0
         3          1          0  104857600        512          2 YES UNUSED                 2636371 13-SEP-23      2636470 13-SEP-23          0

6. Clear the standby logfiles

begin
for log_cur in ( select group# group_no from v$standby_log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/


PL/SQL procedure successfully completed.

SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME     CON_ID
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------
         6 UNASSIGNED                                        1          0  104857600        512          0 NO  UNASSIGNED                                                                                0
         7 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
         8 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
         9 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
        10 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
        11 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0

6 rows selected.

7. Enable log transport and redo apply

DGMGRL>  edit database fsfopr set state='transport-on';
Succeeded.
DGMGRL>  edit database fsfodr set state='apply-on';
Succeeded.

8. Check data guard configuration status and valdiate the standby database

DGMGRL> show configuration

Configuration - fsfo_dg

  Protection Mode: MaxAvailability
  Members:
  fsfopr - Primary database
    fsfodr - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 25 seconds ago)

DGMGRL> validate database fsfodr;

  Database Role:     Physical standby database
  Primary Database:  fsfopr

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

  Managed by Clusterware:
    fsfopr:  YES
    fsfodr:  YES

Useful metalink notes
Creating a Physical Standby database using RMAN restore database from service [ID 2283978.1]

Saturday, March 26, 2022

Initiating Switchover on a Physical Standby

It seems the best instance to intiaiate the switchover changes based on whether data guard broker is used or not. Oracle documentation states when performing switchover using SQL, the switchover process is initiated on the primary database.

Initiate the switchover on the primary database, BOSTON, by issuing the following SQL statement

On the data guard broker documentation it doesn't explicitly states where to initiate the switchover. In practice switchover command could be initiated while logged into any instance. However, executing switchover on the primary instance when data gaurd broker is used will output the following in the data guard broker log. The DG configuration is

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

Switchover command is issued while connected to the current primary, dgtest.

2022-03-21T10:41:33.122+00:00
Forwarding MON_PROPERTY operation to member dgtest2 for processing
2022-03-21T10:41:42.319+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
Switchover to physical standby database cannot be initiated from the primary database
redirecting connection to switchover target database dgtest2...
...using connect identifier: dgtest2tns
SWITCHOVER TO dgtest2
Notifying Oracle Clusterware to prepare primary database for switchover
2022-03-21T10:41:44.319+00:00
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']
2022-03-21T10:42:02.573+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest2'] executed successfully
2022-03-21T10:42:03.652+00:00
Switchover successful

From the output it seems that DG broker is creating a remote connection to the standby (new primary) using the TNS entry used when the standby was added to the data guard broker.

On the otherhand if connected to the standby and switchover command is issued no such message appears.

DGMGRL>  show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

New primary is dgtest2 and switchover command is issued from standby, dgtest. The output on DG broker log of the primary (dgtest2) shows the below

2022-03-21T10:50:27.266+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest
2022-03-21T10:50:28.682+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest']
2022-03-21T10:50:49.451+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest'] executed successfully

Similary if the switchvoer is issued from another standby that is not the future primary (in this confiugration dgtest3) then again the primary's DG broker log output will have the below output

2022-03-21T11:31:51.242+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
2022-03-21T11:31:52.601+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']

Saturday, May 22, 2021

Importing Unified Audit Data From Standby

In a primary database importing audit data involves a export job with INCLUDE=AUDIT_TRAILS parameter. However, situation is different on standby as audit records are written to file system location. In a CDB the root container audit data on standby is available in a folder path similar to $ORACLE_BASE/audit/$ORACLE_SID while the audit data for the PDB is available in a path similar to $ORACLE_BASE/audit/$ORACLE_SID/GUID where GUID is the PDB's generic unique identifier.
The audit files are not text file but binary files. The strings command could be used to get glance at the content of the audit file.

strings ora_audit_0629.bin
ANG Spillover Audit File
ORAAUDNG
oracle
ip-172-31-11-54.eu-west-1.compute.internal
pts/0
(TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=172.31.11.54)(PORT=23959))));
LOCKTEST
sqlplus@ip-172-31-11-54.eu-west-1.compute.intern
9837
ORA_LOGON_FAILURES
LOCKTEST

However, this doesn't give all the information as seen from above there's no date information.
Also same audit file would be appended with audit data for multiple sessions. For example above audit record generated for login failure for session originating in sever 172.31.11.54. However, after a while more records could be seen on the same file such as below. Now there exists another login failure for client originating from server 172.31.15.132.

strings ora_audit_0629.bin
ANG Spillover Audit File
ORAAUDNG
oracle
ip-172-31-11-54.eu-west-1.compute.internal
pts/0
(TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=172.31.11.54)(PORT=23983))));
LOCKTEST
sqlplus@ip-172-31-11-54.eu-west-1.compute.intern
10309
ORA_LOGON_FAILURES
LOCKTESTq
ORAAUDNG
oracle
ip-172-31-15-132.eu-west-1.compute.internal
pts/0
(TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=172.31.15.132)(PORT=30643))));
LOCKTEST
sqlplus@ip-172-31-15-132.eu-west-1.compute.inter
10478
ORA_LOGON_FAILURES
LOCKTEST

Therefore audit file must be queried via unified_audit_trail to get fine details of audit records.

If the unified audit table is queried on the standby DB (if it is open in read only mode) this will show records from both primary DB (available via redo apply) and from standby DB (earlier post on this causing high file waits).
To view the audit data of just the standby DB, the easiest and simplest way is to copy the required audit files (either from the CDB root or PDB location) to a different (test or temporary) database that doesn't have any audit data of its own and query the unified_audit_trail. The location where files are copied to must be similar to $ORACLE_BASE/audit/$ORACLE_SID or $ORACLE_BASE/audit/$ORACLE_SID/GUID. With audit files in above the pre-defined location, they are picked up automatically from their external location and exposed via the unified_audit_trail.
However, if the audit records must be kept in the DB then to improt the audit records into the database use the following.

EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

This will import all the audit files records in the pre-defined audit file location into the database. At the end of this the audit files are removed from the file system.

Monday, March 29, 2021

Creating a Standby From Backup of Another Standby

A previous post showed the steps for adding a physical standby to an existing data guard configuration. In it the creation of the standby was done using active database duplication. This post shows the steps for creating a standby using backups of another standby. This situation is useful if copying backups from primary to new standby location is not feasbile due to the backup size and geographical distance between the two sites.
Current DG configuration is shown below.

DGMGRL> show configuration

Configuration - dbx_dg

  Protection Mode: MaxAvailability
  Members:
  ppdbxdb1- Primary database
    ppdbxdb2 - Physical standby database
    ppdbxfs1 - Far sync instance
      ppdbxdb6 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

A new standby instance named ppdbxdb5 is added to the same region as ppdbxdb6 and redo is shipped via far sync instance ppdbxfs1. The post doesn't show the pre-reqs that need to be completed before adding the new standby. Refer the previous post for those steps.
The database duplication in this case is done connecting the auxiliary database. This method is also used before on a data guard creation.
On the existing standby database (ppdbxdb6 in this case) run following to create backups of spfile, controlfile, database and archive logs.

backup spfile format '/backup/spbackup.bkp';
backup current controlfile for standby format '/backup/stdbycontro.ctl';
backup database format '/backup/dbbackup%U' plus archivelog format '/backup/archbackup%U' delete all input;

switch few log files in the primary

sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';

and backup the archive logs on the standby

backup archivelog all format '/backup/archbkp%U' delete all input;

Copy the backup files created earlier to a location on the new standby host. Exclude any controfile backups (explicit backups or auto backups) being copied to new location except for the controlfile backup taken above. If not during the duplication, due to selecting a controlfile that has a higher checkpoint sequence following error will be thrown.

RMAN-03002: failure of Duplicate Db command at 03/18/2021 12:04:04
RMAN-05501: aborting duplication of target database
RMAN-05507: standby control file checkpoint (5381793) is more recent than duplication point-in-time (5379510)

Once backups are copied start the new standby instance with nomount option.

Connect to the standby using rman auxiliary connection and run the duplication comamnd. In this case the parameter conversion occurs between existing standby DB ppdbxdb6 and new standby ppdbxdb5. Both these databses are terminal standby as such there's no reference to other databases. Only

rman auxiliary sys@ppdbxdb5tns

run {
allocate auxiliary channel ch1 device type disk;
allocate auxiliary channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
allocate auxiliary channel ch5 device type disk;
allocate auxiliary channel ch6 device type disk;
allocate auxiliary channel ch7 device type disk;
allocate auxiliary channel ch8 device type disk;
duplicate database for standby
spfile
parameter_value_convert 'ppdbxdb6','ppdbxdb5','PPdbxDB6','PPdbxDB5'
set db_name='ppdbxdb1'
set db_unique_name='ppdbxdb5'
set log_file_name_convert='/ppdbxdb1/','/ppdbxdb5/','/ppdbxdb2/','/ppdbxdb5/'
set log_archive_max_processes='10'
set fal_server='ppdbxDB1TNS','ppdbxDB2TNS','ppdbxDB6TNS','ppdbxFS1TNS'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ppdbxdb5 NOREOPEN ALTERNATE=log_archive_dest_2' 
set local_listener='LISTENER_PPdbxDB5,DGLISTENER_PPdbxDB5'
set dg_broker_start='false'
reset log_archive_dest_3
reset log_archive_dest_4
BACKUP LOCATION '/backup' dorecover nofilenamecheck;
}

At the end of the duplication processes the new standby is ready to receive and apply redo from the primary and could be added to the data guard broker configuraiton.

show configuration

Configuration - dbx_dg

  Protection Mode: MaxAvailability
  Members:
  ppdbxdb1- Primary database
    ppdbxdb2 - Physical standby database
    ppdbxfs1 - Far sync instance
      ppdbxdb5 - Physical standby database
      ppdbxdb6 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

Useful Metalink Notes
Step by Step method to create Primary/Standby Database from Standby Backup [ID 1604251.1]

Related Posts
Adding a New Physical Standby to Existing Data Guard Setup
Oracle Data Guard on 12.2 CDB with Oracle Restart

Saturday, August 8, 2020

Removing a Failed Standby Database From a Data Guard Configuration

A previous post explained steps for removing a standby instance from a data guard configuration. This post explains steps for the same but when the standby being removed has failed and cannot be reached (or connect into).
In a standby configuration with multiple standby databases once instance is unreachable due to hardware failure. The issue is irrecoverable and only option is to rebuild the node and the standby instance. In mean time the existing standby configuration will give an error state due to the unavailability of the failed instance.

DGMGRL> show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)
  ppdb8  - Physical standby database
    Error: ORA-12170: TNS:Connect timeout occurred

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 96 seconds ago)

As the first step remove any references to the failed instance on RedoRoutes.

Then issue the remove command which will succeed with a warning.

DGMGRL> remove database ppdb8;
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed database "ppdb8" from the configuration

The warning is due to broker being unable to connect to the failed instance to execute the clean up commands. The dataguard broke log shows this.

2020-07-29T12:33:36.403+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Metadata Resync failed. Status = ORA-12170
2020-07-29T12:33:48.691+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration fc_pp_dg Warning ORA-16607: one or more members have failed
Primary Database ppdb1 Success ORA-0: normal, successful completion
Physical Standby Database ppdb2 Success ORA-0: normal, successful completion
Physical Standby Database ppdb3 Success ORA-0: normal, successful completion
Physical Standby Database ppdb4 Success ORA-0: normal, successful completion
Physical Standby Database ppdb5 Success ORA-0: normal, successful completion
Physical Standby Database ppdb6 Success ORA-0: normal, successful completion
Far Sync Instance ppfs1 Success ORA-0: normal, successful completion
Far Sync Instance ppfs2 Success ORA-0: normal, successful completion
Physical Standby Database ppdb8 Error ORA-12170: TNS:Connect timeout occurred
Physical Standby Database ppdb9 Success ORA-0: normal, successful completion
Physical Standby Database ppdb10 Success ORA-0: normal, successful completion
2020-07-29T12:34:00.979+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:05.646+00:00
REMOVE DATABASE ppdb8
2020-07-29T12:34:17.939+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Database ppdb8 (0x0a001000) could not be contacted for database removal, status = ORA-12170
2020-07-29T12:34:31.571+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:33.297+00:00
Database ppdb8 removal completed with warning ORA-16620
REMOVE DATABASE completed with warning ORA-16620

However, all the other databases that are part of the dataguard configuration would have had their log_archive_config parameter updated by removing any reference to the failed database.

NAME                           VALUE
------------------------------ -----------------------------------------
log_archive_config             dg_config=(ppdb1,ppdb2,ppdb3,ppdb4,ppfs1,
                               ppdb5,ppdb6,ppdb9,ppdb10,ppfs2)

Once the failed instance is removed the dataguard broke shows status success.

DGMGRL>  show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

Monday, July 6, 2020

Configuring Statspack for Standby Database

Statspack provide separate set of scripts for setting it up for a standby database in a data guard configuration. If enterprise edition is used with diagnostic pack then Remote Management Framework could be configured to get AWR reports of the standby. Depending on the data guard configuration (multiple standbys, snapshot data needed only from read only instances etc) the statspack setup would result in less effort and complexity than setting up RMF for AWR.
RMF has database link from both primary to standby and vice versa. Before a role transition additional work must be done by way of creating database link between future primary and future standby. In contrast the statspack only create one database link between primary and standby. As long as the TNS entry used for creating the DB link is available in the future primary then the statspack would continue to function. This is especially useful in a configuration where only subset of instances are candidate to become primary and all others are part of a reader farm.
In a multi-tenant architecture the data guard works on CDB level. As such the standby statspack is installed on the root container. This will create two users perfstat and stdbyperf. To accomplish this the statspack and standby statspack must be installed using catcon.pl script (Refer 2020285.1). The setup of standby consists of running spcreate.sql (normal statspack) and sbcreate.sql (standby statspack) scripts.
A script defining the statspack setup parameters could be used to automate the statspack creation. Output below shows content of a such script, which defines tablespace to create the statspack related tables and the password for perfstat user among other things.

cat sp.sql
define default_tablespace='STATSPACKTBS'
define temporary_tablespace='TEMP'
define perfstat_password='asanga123'
@?/rdbms/admin/spcreate.sql;
@/home/oracle/statpack/chng.sql;

The chng.sql contains the additional work must be done after setting up statspcak. This include issues mentioned in 382993.1 and 2437142.1. Content of this files is shown below.

cat chng.sql
alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;
create index perfstat.STATS$MUTEX_SLEEP_PK on STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
insert into stats$idle_event select name from v$event_name where wait_class='Idle' 
minus select event from stats$idle_event;

If perl binary out of ORACLE_HOME is not in the path then following error will occur when running catcon.pl

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spcreate sp.sql
Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/) at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin//catcon.pm line 497.
BEGIN failed--compilation aborted at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin//catcon.pm line 497.
Compilation failed in require at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/catcon.pl line 165.
BEGIN failed--compilation aborted at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/catcon.pl line 165.

To rectify this issue put perl binary out of OH into the path and run the catcon.pl specifying sp.sql script.

export PATH=$ORACLE_HOME/perl/bin:$PATH
$ which perl
/opt/cx/app/oracle/product/19.x.0/dbhome_1/perl/bin/perl

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spcreate sp.sql

Next step is to create the standby statspack. A similar script could be created for that as well. The most important parameter in this case is the TNS alias which will be used to create the database link that will connect to the standby.

cat sb.sql
define default_tablespace='STATSPACKTBS'
define temporary_tablespace='TEMP'
define stdbyuser_password='asanga321'
define perfstat_password='asanga123'
define key='y'
define tns_alias='ppdbxdb5tns'
@?/rdbms/admin/sbcreate.sql;
@/home/oracle/statpack/sbchng.sql;

The sbchng.sql has additional work must be done on standby statspack. As stdbyperf user doesn't have access to v$event_name the idle event related workaround must be done after the setup. Also the STATS$MUTEX_SLEEP on standby statspack doesn't have DBID or instance number columns.

SQL> desc stdbyperf.stats$mutex_sleep
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DB_UNIQUE_NAME                            NOT NULL VARCHAR2(30)
 INSTANCE_NAME                             NOT NULL VARCHAR2(16)
 MUTEX_TYPE                                NOT NULL VARCHAR2(32)
 LOCATION                                  NOT NULL VARCHAR2(40)
 SLEEPS                                             NUMBER
 WAIT_TIME                                          NUMBER

Instead db_unique_name and instance_name must be used

cat sbchng.sql
alter table stdbyperf.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;
CREATE INDEX stdbyperf.STATS$MUTEX_SLEEP_PK ON stdbyperf.STATS$MUTEX_SLEEP(SNAP_ID,DB_UNIQUE_NAME,INSTANCE_NAME,MUTEX_TYPE, LOCATION);

Run the standby statspack creation with

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbcreate sb.sql

Once standby statspack is created run as sys the following to update the idle events in stdbyperf user schema.

insert into stdbyperf.stats$idle_event select event from perfstat.stats$idle_event minus select event from stdbyperf.stats$idle_event;

Additional standby instaces could be added to the standby statspack by using sbaddins.sql. This must be run using stdbyperf and must specify the TNS entry to the standby db. A script similar to below could be used for accomplishing this. As all the standby database instnaces use same set of tables no need to do the additional work on the subsequent standby instances.

cat sbaddin.sql
connect stdbyperf/asanga321
define key='y'
define tns_alias='ppdbxdb6tns'
define perfstat_password='asanga123'
@?/rdbms/admin/sbaddins.sql;

perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbaddins sbaddin.sql

Login as stdbyperf and run the below to view the current statspack standby configuration. This only shows the standby instances added to statspack. Not the actual data guard configuation.

select * from stats$standby_config ;

DB_UNIQUE_NAME                 INST_NAME        DB_LINK                          PACKAGE_NAME
------------------------------ ---------------- -------------------------------- ----------------------------
ppdbxdb5                       ppdbxdb5         STDBY_LINK_ppdbxdb5tns           STATSPACK_ppdbxdb5_ppdbxdb5
ppdbxdb6                       ppdbxdb6         STDBY_LINK_ppdbxdb6tns           STATSPACK_ppdbxdb6_ppdbxdb6

As mentioned earlie the standby statspack uses common set of tables to store data from all the standby instances. Only unique objects created for each standby instance are the database link and PL/SQL package which has the name STATSPACK_<db_unique_name>_<instance_name>. The corresponding package for the particular standby instance must be used in order to work with the instance. This inlcuding taking snapshots, changing statspack parameters and etc. For example in orde to change the snap level on ppdbxdb5 should run the STATSPACK_PPdbxDB5_PPdbxDB5 package. Similarly to change snap level on ppdbxdb6 should use STATSPACK_PPdbxDB6_PPdbxDB6. Below output shows chaning snap level on ppdbxdb6.

exec STATSPACK_PPdbxDB6_PPdbxDB6.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter=>'true');

select DB_UNIQUE_NAME,INSTANCE_NAME,snap_level from stats$statspack_parameter;

DB_UNIQUE_NAME                 INSTANCE_NAME    SNAP_LEVEL
------------------------------ ---------------- ----------
ppdbxdb5                       ppdbxdb5                  7
ppdbxdb6                       ppdbxdb6                  7

Standby statspack use a common sequence to create snap id. As such when multiple instances takes snapshots there will be gaps between two consecutive snap ids.
Manual snapshots could be taken by running STATSPACK_<db_unique_name>_<instance_name>.snap. Snapshot taking is not automated and has to be done manually. Scheduling it in a dbms_scheduler job would allow the snapshot taking to work even after swichover in a reader farm configuration mentioned at the begining of the post.
The user stdbyperf doesn't have permission to create scheduler job. Grant create job and execute on dbms_scheduler (if revoked from public) to stdbyperf. Could create one scheduler job for each standby or have a single wrapper procedure with snap call for each standby. Below shows two separate scheduler job for automating snapshot taking.

--ppdbxdb5                       

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STANDBY_STATSPACK_PPdbxDB5',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_PPdbxDB5_PPdbxDB5.snap',
start_date => TO_TIMESTAMP('00' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/

--ppdbxdb6
                       
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STANDBY_STATSPACK_PPdbxDB6',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_PPdbxDB6_PPdbxDB6.snap',
start_date => TO_TIMESTAMP('01' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/

When having multiple scheduler jobs are invoked at the same time it is possible to run into the following error.

2020-06-29T18:02:49.768983+00:00
Errors in file /opt/cx/app/oracle/diag/rdbms/ppdbxdb1/ppdbxdb1/trace/ppdbxdb1_j001_119494.trc:
ORA-12012: error on auto execute of job "STDBYPERF"."STANDBY_STATSPACK_PPdbxDB5"
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 3909
ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 5486
ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 101
ORA-06512: at line 1
2020-06-29T18:06:57.404823+00:00

To eliminate this have a time gap between two schedulers. Above schedulers are scheduled 1 minute apart.
Create standby statspack report by calling the sbreport. This will prompt to select the db_unique_name and instance_name and prompt the range of snap ids to create the statspack report.

SQL> @?/rdbms/admin/sbreport

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name                 Instance Name
------------------------------ ----------------
ppdbxdb5                       ppdbxdb5
ppdbxdb6                       ppdbxdb6

Enter the DATABASE UNIQUE NAME of the standby database to report
Enter value for db_unique_name: ppdbxdb5
You entered: ppdbxdb5

Enter the INSTANCE NAME of the standby database instance to report
Enter value for inst_name: ppdbxdb5
You entered: ppdbxdb5


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots
                                          Snap
Instance       Snap Id   Snap Started    Level Comment
------------ --------- ----------------- ----- --------------------
ppdbxdb5            14 30 Jun 2020 00:00     7
                    16 30 Jun 2020 01:00     7
                    18 30 Jun 2020 02:00     7
                    20 30 Jun 2020 03:00     7
                    22 30 Jun 2020 04:00     7

Standby statspack report will have a separate section on standby recovery

Recovery Progress Stats  DB/Inst: ppdbxdb5/ppdbxdb5  End Snap: 32
-> End Snapshot Time: 30-Jun-20 09:00:04
-> ordered by Recovery Start Time desc, Units, Item asc

Recovery Start Time Item                       Sofar Units   Redo Timestamp
------------------- ----------------- -------------- ------- ------------------
15-Jun-20 15:18:07  Log Files                 11,360 Files
15-Jun-20 15:18:07  Active Apply Rate            659 KB/sec
15-Jun-20 15:18:07  Average Apply Rat             12 KB/sec
15-Jun-20 15:18:07  Maximum Apply Rat         13,280 KB/sec
15-Jun-20 15:18:07  Redo Applied              14,936 Megabyt
15-Jun-20 15:18:07  Recovery ID                    0 RCVID
15-Jun-20 15:18:07  Last Applied Redo              0 SCN+Tim 30-Jun-20 09:00:15
15-Jun-20 15:18:07  Active Time               29,277 Seconds
15-Jun-20 15:18:07  Apply Time per Lo              1 Seconds
15-Jun-20 15:18:07  Checkpoint Time p              0 Seconds
15-Jun-20 15:18:07  Elapsed Time           1,273,511 Seconds
15-Jun-20 15:18:07  Standby Apply Lag              2 Seconds
          -------------------------------------------------------------

Purging of snapshots must also be done calling the purge procedure of the relavent standby instance package. Following procedure delete snapshots after 32 days of creation.

create table deleted_snaps(delete_time timestamp,
min_snapid number, 
max_snapid number, 
DB_UNIQUE_NAME VARCHAR2(30), 
INSTANCE_NAME VARCHAR2(16), primary key (DB_UNIQUE_NAME,INSTANCE_NAME,delete_time));

create or replace
Procedure Delete_Snaps As
min_snap_id stats$snapshot.snap_id%type;
max_snap_id stats$snapshot.snap_id%type;
db_unq_name stats$snapshot.DB_UNIQUE_NAME%type;
ins_name stats$snapshot.INSTANCE_NAME%type;
snapshots_purged pls_integer;
stm varchar2(500);
cursor delsnaps is select DB_UNIQUE_NAME,INSTANCE_NAME,min(snap_id) as min,
max(snap_id) as max from stats$snapshot 
where to_char(snap_time,'YYYY-MM-DD') = to_char(sysdate-32,'YYYY-MM-DD') 
group by DB_UNIQUE_NAME,INSTANCE_NAME;
begin
open delsnaps;
loop
fetch delsnaps into db_unq_name,ins_name,min_snap_id,max_snap_id;
Exit When Delsnaps%Notfound;
stm := 'begin STATSPACK_'||db_unq_name||'_'||ins_name||'.purge(i_begin_snap =>'||min_snap_id||', i_end_snap =>'|| max_snap_id||', i_snap_range  => true, i_extended_purge  => true, I_DB_UNIQUE_NAME  => '''||db_unq_name||''', I_INSTANCE_NAME => '''||ins_name||'''); end;';
dbms_output.put_line(stm);
execute immediate stm;
commit;
Insert Into Deleted_Snaps values(Systimestamp,Min_Snap_Id,Max_Snap_Id,db_unq_name,ins_name);  
end loop;
Close Delsnaps;
commit;
End;
/

Schedule the deletion of snapshots.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'Delete_Snaps_job',
job_type => 'STORED_PROCEDURE',
job_action => 'Delete_Snaps',
start_date => trunc(SYSDATE + 1,'DD') + 15/1444,
repeat_interval => 'FREQ=DAILY',
enabled => TRUE);
END;
/

To remove a standby instance from statspack configuration use $ORACLE_HOME/rdbms/admin/sbdelins.sql.

To drop standby statspack and statspack from the database use catcon.pl.

#remove standby statspack
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbdrop  $ORACLE_HOME/rdbms/admin/sbdrop.sql

# remove statspack
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spdrop  $ORACLE_HOME/rdbms/admin/spdrop.sql

Useful metalink notes
STATSPACK:Idle Wait Events Missing in STATS$IDLE_EVENT [ID 2673657.1]
STATSPACK REPORTS SHOW "IDLE" DATA GUARD WAIT EVENTS IN TOP 5 IN 12.2 [ID 2305287.1]
12.2 or later STATSPACK: Idle Wait Event Such as 'Data Guard: Timer' is Erroneously Included in Top 5 Timed Events [ID 2437142.1]
New Idle Events are Erroneously Listed in STATSPACK Report [ID 1998538.1]
STATSPACK:Idle Wait Events Missing in STATS$IDLE_EVENT [ID 2673657.1]

Related Posts
Statspack setup in Brief
AWR Reports on Standby when Active Data Guard is Used

Monday, December 3, 2018

AWR Reports on Standby when Active Data Guard is Used

Oracle introduced Remote Management Framework (RMF) in 12.2 which allows creating AWR reports on standby database when active data guard is in use. This post list the steps for setting up the RMF so AWR reports could be generated on standby. The post use the data guard configuration set up on 18.3, which is mentioned in a previous post. Current data guard setup and standby open mode is as follows.

DGMGRL> show configuration

Configuration - paas_iaas_dg

  Protection Mode: MaxAvailability
  Members:
  fradb_fra1kk - Primary database
    londb        - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL> show database londb

Database - londb

  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):
    lonDB

Database Status:
SUCCESS

Oracle provide a pre-created user sys$umf (locked by default) which has all the necessary privileges to carry out the RMF related work. Unlock this user from primary DB and set a password.

alter user sys$umf identified by rmfuser account unlock;

Next create two database link that sys$umf user will use to connect to and from the standby DB. The TNS entries used here were created in the previous post. Before creating database link take a note of the global_names parameter. If this is set to true, then DB links are expected to be the same name as the DB they connect to. If not ORA-02085 error could be encountered when trying to use the DB links.

create database link fra_to_lon CONNECT TO sys$umf IDENTIFIED BY rmfuser  using 'LONDBTNS';
create database link lon_to_fra CONNECT TO sys$umf IDENTIFIED BY rmfuser  using 'FRADBTNS';

Check the DB links are working by querying remote instance using them. Run both on primary and standby.

SQL> select instance_name from v$instance@fra_to_lon;

INSTANCE_NAME
----------------
lonDB

SQL>  select instance_name from v$instance@lon_to_fra;

INSTANCE_NAME
----------------
fraDB

On primary run the following to configure the primary node with RMF. Configure node require a unique name for each node configured. If none is provided, the db_unique_name will be used instead.

SQL> exec dbms_umf.configure_node ('fraDB');

PL/SQL procedure successfully completed.

On standby run the following to configure the standby with RMF. In this case a unique name for standby and the db link name from standby to primary is given as inputs.

SQL> exec dbms_umf.configure_node ('lonDB','lon_to_fra');

PL/SQL procedure successfully completed.

Create the RMF topology by running following on primary.

SQL> exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');

PL/SQL procedure successfully completed.

SQL> select * from dba_umf_topology;

TOPOLOGY_NAME         TARGET_ID TOPOLOGY_VERSION TOPOLOGY
-------------------- ---------- ---------------- --------
FRA_LON_TOPOLOGY     1423735874                1 ACTIVE

View the registered nodes. Only primary is registered so far.

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME        NODE_NAME     NODE_ID  NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- --------------------
FRA_LON_TOPOLOGY     fraDB      1423735874          0 FALSE FALSE OK

Register the standby with the topology. The meaning of the input parameters could be found here. Execute the following on primary.

exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'lonDB', 'fra_to_lon', 'lon_to_fra', 'FALSE', 'FALSE');

Check both nodes are registered.

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME        NODE_NAME     NODE_ID  NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- -----
FRA_LON_TOPOLOGY     fraDB      1423735874          0 FALSE FALSE OK
FRA_LON_TOPOLOGY     lonDB      4041047630          0 FALSE FALSE OK

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'lonDB');

PL/SQL procedure successfully completed.

Verify AWR service is active on the remote node

SQL> select * from dba_umf_service;

TOPOLOGY_NAME           NODE_ID SERVICE
-------------------- ---------- -------
FRA_LON_TOPOLOGY     4041047630 AWR

To generate AWR report create two snapshots on the remote database.

SQL> exec dbms_workload_repository.create_remote_snapshot('lonDB');

PL/SQL procedure successfully completed.

Once a snapshot is created the standby DB is listed in the AWR with the DB ID that is equal to the node ID (highlighted above) in the UMF registration.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        lonDB        lonvm
  4041047630     1      FRADB        lonDB        lonvm
* 1042410484     1      FRADB        fraDB        fravm

Enter value for dbid: 4041047630

The AWR instance report generated would list the role as physical standby.

The AWR control view list the standby database as well.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- ----------
1042410484 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 1042410484
4041047630 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT               1042410484 lonDB

The remote snapshots will be automatically taken according to snapshot internal.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        lonDB        lonvm
  4041047630     1      FRADB        lonDB        lonvm
* 1042410484     1      FRADB        fraDB        fravm

Enter value for dbid: 4041047630
Using 4041047630 for database Id
Enter value for inst_num: 1
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

lonDB        FRADB                1  29 Nov 2018 13:11    1
                                  2  29 Nov 2018 13:59    1
                                  3  29 Nov 2018 14:58    1
                                  4  29 Nov 2018 15:58    1

However, after a switchover, when roles changes the automatic snapshot taking will stop, both on new primary (old standby) and new standby (old primary). In order to automatic AWR snapshot to continue do the following after a switchover. As the first step unregistered the new primary (old standby) as a remote database.

SQL> exec DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE('lonDB','FRA_LON_TOPOLOGY',false);

PL/SQL procedure successfully completed.

Drop the topology

exec DBMS_UMF.drop_topology('FRA_LON_TOPOLOGY');

PL/SQL procedure successfully completed.

Run un-configure procedure on each node

SQL>  exec DBMS_UMF.UNCONFIGURE_NODE;

PL/SQL procedure successfully completed.

Re-create the topology again with new primary and standby. On new primary

SQL> exec dbms_umf.configure_node ('lonDB');

PL/SQL procedure successfully completed.

On new standby

SQL> exec dbms_umf.configure_node ('fraDB','fra_to_lon');

PL/SQL procedure successfully completed.

Same topology name is used

exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');

exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'fraDB', 'lon_to_fra', 'fra_to_lon', 'FALSE', 'FALSE');

Register remote DB for AWR by specifying the node ID of the DB shown in dba_umf_registration earlier. If the remote DB registration was to be done using DB name as before then "ORA-13516: AWR Operation failed: ORA-13516: AWR Operation failed: Remote source not registered for AWR" would be encountered when snapshots are taken. Doing log switches as suggested by 2409808.1 did not resolve this issue.

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(1423735874);

PL/SQL procedure successfully completed.

Once remote DB is registered remote snapshots could be taken

SQL> exec dbms_workload_repository.create_remote_snapshot('fraDB');

PL/SQL procedure successfully completed.

After the role reversal, automatic snapshots will continue on the snapshot interval. Run AWR instance reports same as before.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1042410484     1      FRADB        fraDB        fravm
  1423735874     1      FRADB        fraDB        fravm
* 1042410484     1      FRADB        lonDB        lonvm

Enter value for dbid: 1423735874

The new primary (old standby) will be reflected on the AWR report taken against it.

Useful Metalink Note
How to Generate AWRs in Active Data Guard Standby Databases [ID 2409808.1]

Related Posts
Enabling Automatic AWR Snapshots on PDB

Update on 2020-07-13
Oracle documentation now has a separate section on "Managing ADG Role Transition". The doc shows the use of DBMS_UMF.SWITCH_DESTINATION for role reversal scenarios.

Monday, October 1, 2018

Converting a Physical Standby to a Snapshot Standby

Converting a physical standby to a snapshot standby allows it to be open for read/write access. This could enable various types of testing to be carried out on the snapshot standby. This post list steps for converting a physical standby to snapshot standby and then convert it back to a physical standby.
1. Current data guard configuration is as follows.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2. It is not necessary to have flashback enabled for snapshot to work. As per Oracle documentation "if Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required". However, a fast recovery area must be configured on the physical standby. According to Oracle documentation "this is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area". For this post the flashback feature was deliberately turned off to verify that snapshot conversion can go through without it. However, it's good practice to have flashback enabled in a DG configuration. Turning off flashback is not part of converting to snapshot standby and no need to do the below steps.
On primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

On standby

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

3. Conversion to snapshot standby is done using the DGMGRL.

DGMGRL> convert database stdby to snapshot standby;
Converting database "stdby" to a Snapshot Standby database, please wait...
Database "stdby" converted successfully

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Use of DGMGRL automate lot of the steps needed for conversion such as stopping the redo apply and initializing DG after the conversion. These steps could be identified by looking at the standby alert log.

Wed Sep 19 16:22:13 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_2301.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 16269612
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery process shutdown (stdby)
Managed Standby Recovery Canceled (stdby)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Starting background process RVWR
Wed Sep 19 16:22:14 2018
RVWR started with pid=34, OS id=2429
Wed Sep 19 16:22:15 2018
Archived Log entry 4995 added for thread 1 sequence 399 ID 0x172868bb dest 1:
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/19/2018 16:22:14
Killing 1 processes with pids 2334 (all RFS) in order to disallow current and fu                                                                                                              ture RFS connections. Requested by OS process 2293
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 16269612
Resetting resetlogs activation ID 388524219 (0x172868bb)
Online log +DATA/stdby/onlinelog/group_1.264.964883451: Thread 1 Group 1 was previously cleared
Online log +FRA/stdby/onlinelog/group_1.258.964883451: Thread 1 Group 1 was previously cleared
...
Standby became primary SCN: 16269610
Wed Sep 19 16:22:18 2018
Setting recovery target incarnation to 6
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete

4. After physical standby is converted to snapshot standby it will not carry out redo apply. However, it will continue to receive redo from primary thus protecting the primary in the event of failure. This could be verified by querying managed standby view. On standby run

SQL> select status,sequence#,block# from v$managed_standby where client_process='LGWR';

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                402        409

Make a note of block and sequence number. Execute a log switch on primary and query the standby again, which would show change in sequence# and/or block#. This indicate redo shipping is working as expected.

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                403          4

5. At this point the snapshot standby is open for read/write. Carry out any testing needed. Once done with testing convert back to physical standby. Any changes done on snapshot standby DB will be lost once converted back to physical standby.

6. Connect to DGMGRL from primary and execute the convert command.

DGMGRL> convert database stdby to physical standby;
Converting database "stdby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Continuing to convert database "stdby" ...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Database "stdby" converted successfully

7. Finally verify the DG status and redo apply has begun.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stdby

Database - stdby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      95.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    stdby

Database Status:
SUCCESS

Wednesday, August 1, 2018

Adding a New Physical Standby to Existing Data Guard Setup

This post shows the steps for adding a physical standby to an existing data guard setup. The existing data guard setup consists of a single primary and single physical standby with Oracle restart. To this DG setup a new physical standby would be added followed by DG broker update with the new standby. The summary of hostnames, DB names is shown in the table below.

Item	Existing Data Guard Setup		New Standby
Item	Primary	Standby	New Standby
Host name	city7	city7s	city7s2
Database Name	prodcdb	stbycdb	stby2cdb
Diskgroup Names	DATA FRA	DATA FRA	DATA FRA
TNS Entry Name	PRODCDBTNS	STBYTNS	STBY2TNS

The steps here are of the configuration of the data guard portion only. It's assumed relevant software is installed and ASM disk configuration is done on the new standby server.

1. On the new standby create the adump directory.

cd $ORACLE_BASE/admin
mkdir stby2cdb
cd stby2cdb
mkdir adump  dpdump  hdump  pfile

2. Add the static listner entries to the listener.ora file. In this case entries for both standard listener entry and DG broker specific entry (with DGMGRL) is added the same time.

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = stby2cdb)
                (SID_NAME = stby2cdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
        (SID_DESC =
                (GLOBAL_DBNAME = stby2cdb_DGMGRL)
                (SID_NAME = stby2cdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )

)

Start the listener on the new standby and verify static services are up

Service "stby2cdb" has 1 instance(s).
  Instance "stby2cdb", status UNKNOWN, has 1 handler(s) for this service...
Service "stby2cdb_DGMGRL" has 1 instance(s).
  Instance "stby2cdb", status UNKNOWN, has 1 handler(s) for this service...

3. Create TNS entry on all servers (both on existing and new standby) pointing to the standby instance that would be running on the new standby server.

STBY2TNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = city7s2)(PORT = 1581))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby2cdb)
    )
  )

4. Copy the Oracle password file to new standby server and rename the file to reflect the new standby instance name.

scp orapwprodcdb city7s2:$ORACLE_HOME/dbs/orapwstby2cdb

5. Create init file on new standby with just the db_name entry and start the standby instance in nomount mode

more initstby2cdb.ora
db_name=stby2cdb

6. On the primary modify the log_archive_config parameter by including the new standby DB. Also add a new log_archive_dest value pointing to the new standby instance. Update the fal_server list to include the TNS entry for the new standby. Update both DB and Log file name convert parameter considering how the file name conversion must happen if and when each of the standby databases become primary.

alter system set log_archive_config='dg_config=(prodcdb,stbycdb,stby2cdb)' scope=both ;
alter system set log_archive_dest_3='service=STBY2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby2cdb' scope=both;
alter system set fal_server='STBYTNS','STBY2TNS' scope=both;
alter system set db_file_name_convert='/stbycdb/','/prodcdb/','/stby2cdb/','/prodcdb/' scope=spfile;
alter system set log_file_name_convert='/stbycdb/','/prodcdb/','/stby2cdb/','/prodcdb/' scope=spfile;

7. Same set of parameters should be updated on the existing standby as well.

alter system set log_archive_config='dg_config=(prodcdb,stbycdb,stby2cdb)' scope=both ;
alter system set log_archive_dest_3='service=STBY2TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby2cdb' scope=both;
alter system set fal_server='PRODCDBTNS','STBY2TNS' scope=both;
alter system set db_file_name_convert='/prodcdb/','/stbycdb/','/stby2cdb/','/stbycdb/' scope=spfile;
alter system set log_file_name_convert='/prodcdb/','/stbycdb/','/stby2cdb/','/stbycdb/' scope=spfile;

8. Finally run the rman command to create the new standby. Earlier post showed multiple ways of creating the standby DB. In this instance the standby is created using active database option. Following rman command is run from the current primary.

rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stby2tns
duplicate target database for standby from active database
spfile
parameter_value_convert 'prodcdb','stby2cdb','PRODCDB','STBY2CDB','stbycdb','stby2cdb','STBYCDB','STBY2CDB'
set db_name='prodcdb'
set db_unique_name='stby2cdb'
set db_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
set log_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS','STBYTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_3='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb';

Full output of the duplication is shown below

rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stby2tns

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jul 16 14:41:23 2018

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

connected to target database: PRODCDB (DBID=2963914998)
connected to auxiliary database: STBY2CDB (not mounted)

RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'prodcdb','stby2cdb','PRODCDB','STBY2CDB','stbycdb','stby2cdb','STBYCDB','STBY2CDB'
4> set db_name='prodcdb'
5> set db_unique_name='stby2cdb'
set db_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
6> 7> set log_file_name_convert='/prodcdb/','/stby2cdb/','/stbycdb/','/stby2cdb/'
8> set log_archive_max_processes='10'
9> set fal_server='PRODCDBTNS','STBYTNS'
10> reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
11> 12> set log_archive_dest_3='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'
13> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb';

Starting Duplicate Db at 16-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstby2cdb'   targetfile
 '+DATA/prodcdb/spfileprodcdb.ora' auxiliary format
 '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora'   ;
   sql clone "alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora''";
}
executing Memory Script

Starting backup at 16-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Finished backup at 16-JUL-18

sql statement: alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/opt/app/oracle/admin/stby2cdb/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA/STBY2CDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBY2CDB/CONTROLFILE/current.256.965841019'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=stby2cdbXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name =
 ''prodcdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''stby2cdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''PRODCDBTNS'', ''STBYTNS'' comment=
 '''' scope=spfile";
   sql clone "alter system reset  local_listener scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_3 =
 ''service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/opt/app/oracle/admin/stby2cdb/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/STBY2CDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBY2CDB/CONTROLFILE/current.256.965841019'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stby2cdbXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''prodcdb'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stby2cdb'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/prodcdb/'', ''/stby2cdb/'', ''/stbycdb/'', ''/stby2cdb/'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  10 comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''PRODCDBTNS'', ''STBYTNS'' comment= '''' scope=spfile

sql statement: alter system reset  local_listener scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_3 =  ''service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby2cdb'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1191182336 bytes

Fixed Size                     8792104 bytes
Variable Size                452986840 bytes
Database Buffers             721420288 bytes
Redo Buffers                   7983104 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/STBY2CDB/CONTROLFILE/current.264.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.318.981652083'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DATA/STBY2CDB/CONTROLFILE/current.290.981652083';
   restore clone primary controlfile to  '+FRA/STBY2CDB/CONTROLFILE/current.276.981652083' from
 '+DATA/STBY2CDB/CONTROLFILE/current.290.981652083';
   sql clone "alter system set  control_files =
  ''+DATA/STBY2CDB/CONTROLFILE/current.290.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.276.981652083'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/STBY2CDB/CONTROLFILE/current.264.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.318.981652083'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_prodcdb.f tag=TAG20180716T144207
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-18

Starting restore at 16-JUL-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=137 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 16-JUL-18

sql statement: alter system set  control_files =   ''+DATA/STBY2CDB/CONTROLFILE/current.290.981652083'', ''+FRA/STBY2CDB/CONTROLFILE/current.276.981652083'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1191182336 bytes

Fixed Size                     8792104 bytes
Variable Size                452986840 bytes
Database Buffers             721420288 bytes
Redo Buffers                   7983104 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA";
   set newname for tempfile  2 to
 "+DATA";
   set newname for tempfile  3 to
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA";
   set newname for datafile  2 to
 "+DATA";
   set newname for datafile  3 to
 "+DATA";
   set newname for datafile  4 to
 "+DATA";
   set newname for datafile  5 to
 "+DATA";
   set newname for datafile  6 to
 "+DATA";
   set newname for datafile  7 to
 "+DATA";
   set newname for datafile  33 to
 "+DATA";
   set newname for datafile  34 to
 "+DATA";
   set newname for datafile  35 to
 "+DATA";
   set newname for datafile  121 to
 "+DATA";
   backup as copy reuse
   datafile  1 auxiliary format
 "+DATA"   datafile
 2 auxiliary format
 "+DATA"   datafile
 3 auxiliary format
 "+DATA"   datafile
 4 auxiliary format
 "+DATA"   datafile
 5 auxiliary format
 "+DATA"   datafile
 6 auxiliary format
 "+DATA"   datafile
 7 auxiliary format
 "+DATA"   datafile
 33 auxiliary format
 "+DATA"   datafile
 34 auxiliary format
 "+DATA"   datafile
 35 auxiliary format
 "+DATA"   datafile
 121 auxiliary format
 "+DATA"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/PRODCDB/DATAFILE/sysaux.265.965841035
output file name=+DATA/STBY2CDB/DATAFILE/sysaux.296.981652125 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/PRODCDB/DATAFILE/system.259.965841027
output file name=+DATA/STBY2CDB/DATAFILE/system.298.981652161 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/PRODCDB/DATAFILE/undotbs1.262.965841045
output file name=+DATA/STBY2CDB/DATAFILE/undotbs1.289.981652175 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.263.965841045
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.285.981652183 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00034 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.277.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.306.981652199 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.258.965841031
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.275.981652213 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/PRODCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.261.965841047
output file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.282.981652221 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00033 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.276.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.268.981652227 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00035 name=+DATA/PRODCDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.275.979213603
output file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.262.981652235 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00121 name=+DATA/PRODCDB/DATAFILE/test.303.969113983
output file name=+DATA/STBY2CDB/DATAFILE/test.283.981652243 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/PRODCDB/DATAFILE/users.269.965841065
output file name=+DATA/STBY2CDB/DATAFILE/users.311.981652243 tag=TAG20180716T144248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-18

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=981652244 file name=+DATA/STBY2CDB/DATAFILE/system.298.981652161
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=981652244 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.275.981652213
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/sysaux.296.981652125
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=981652245 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.285.981652183
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/undotbs1.289.981652175
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=981652245 file name=+DATA/STBY2CDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.282.981652221
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/users.311.981652243
datafile 33 switched to datafile copy
input datafile copy RECID=8 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/system.268.981652227
datafile 34 switched to datafile copy
input datafile copy RECID=9 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/sysaux.306.981652199
datafile 35 switched to datafile copy
input datafile copy RECID=10 STAMP=981652245 file name=+DATA/STBY2CDB/6EFD65D00A9340E9E0535500A8C09D38/DATAFILE/undotbs1.262.981652235
datafile 121 switched to datafile copy
input datafile copy RECID=11 STAMP=981652245 file name=+DATA/STBY2CDB/DATAFILE/test.283.981652243
Finished Duplicate Db at 16-JUL-18

9. Start the managed recovery on the new standby and verify applying of the archived logs

alter database recover managed standby database using current logfile disconnect;

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------
       497 YES
       498 YES
       499 IN-MEMORY

10. Next step is to add the new standby to the Oracle restart. Update the oracle binary permission on the Oracle home of the new standby to avoid the ORA-27303 issue. Once done add the new standby to Oracle restart configuration. Run the following as Oracle user

srvctl add database -db stby2cdb -oraclehome $ORACLE_HOME  -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"

srvctl  config database -db stby2cdb
Database unique name: stby2cdb
Database name:
Oracle home: /opt/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: /opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestby2cdb.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group:
OSOPER group:
Database instance: stby2cdb

srvctl  stop database -db stby2cdb
srvctl  start database -db stby2cdb

11. Final step is to add the new standby to the existing data guard broker configuration. On the new standby cancel the managed recovery and set dg_broker_start to true. It is assumed that dg_broker_config_file* parameter will be set to default value.

alter database recover managed standby database cancel;

alter system set dg_broker_start=true scope=both sid='*';

12. To avoid the ORA-16575, clear the log_archive_dest values added during the earlier steps. On all databases, including the new standby, run the following to clear the new log_archive_dest value

alter system reset log_archive_dest_3 scope=both;

Additionally on the new standby run the following to clear the second log archive dest as well.

alter system reset log_archive_dest_2 scope=both;

13. Once the log archive dest are cleared add the new standby to the existing data guard configuration and enable it.

DGMGRL> add database stby2cdb as connect identifier is stby2tns;
Database "stby2cdb" added

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    Warning: ORA-16792: configurable property value is inconsistent with member setting

    stbycdb  - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

    stby2cdb - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 39 seconds ago)

DGMGRL> enable database stby2cdb;
Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    Warning: ORA-16792: configurable property value is inconsistent with member setting

    stbycdb  - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 36 seconds ago)

14. The warning on the existing databases is down to mismatch of db/log file name convert values. The Oracle doc states the following (only db_file_name convert text is quoted here) "when a database is added to the configuration, the broker sets the initial value of this property to the in-memory value of the DB_FILE_NAME_CONVERT initialization parameter. It is possible that the in-memory value and server parameter file (SPFILE) value of this parameter will differ. If you want to use the parameter's in-memory value, then enable the database and the broker will ensure that the SPFILE value of the parameter is set to the in-memory value. If you want to use the SPFILE value, then set the property value to be the parameter's value stored in the SPFILE. Then enable the database". Listing inconsistent properties for each database list these values

DGMGRL> show database prodcdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         prodcdb    DbFileNameConvert /stbycdb/, /prodcdb/ /stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/ /stbycdb/, /prodcdb/
         prodcdb   LogFileNameConvert /stbycdb/, /prodcdb/ /stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/ /stbycdb/, /prodcdb/

DGMGRL> show database stbycdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         stbycdb    DbFileNameConvert /prodcdb/, /stbycdb/ /prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/ /prodcdb/, /stbycdb/
         stbycdb   LogFileNameConvert /prodcdb/, /stbycdb/ /prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/ /prodcdb/, /stbycdb/

15. To fix this manually update each property for each database that report inconsistency. Following is executed on current production

DGMGRL>  edit database prodcdb set property 'DbFileNameConvert'='/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

show database prodcdb DbFileNameConvert
  DbFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

edit database prodcdb set property 'LogFileNameConvert'='/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

show database prodcdb  LogFileNameConvert
  LogFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

Following is executed on the existing standby

DGMGRL>  edit database stbycdb set property 'DbFileNameConvert'='/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/' ;
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

DGMGRL>  edit database stbycdb set property 'LogFileNameConvert'='/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/' ;
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

DGMGRL> show database stbycdb LogFileNameConvert
  LogFileNameConvert = '/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/'
DGMGRL> show database stbycdb DbFileNameConvert
  DbFileNameConvert = '/prodcdb/,/stbycdb/,/stby2cdb/,/stbycdb/'

16. As highlighted above, for this change to take effect restart of the databases, both primary and exiting standby is required incurring down time. Configuration status after restart of the existing standby (stbycdb)

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    Warning: ORA-16809: multiple warnings detected for the member

    stbycdb  - Physical standby database (disabled)
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 16 seconds ago)

DGMGRL> enable database stbycdb
Enabled.
DGMGRL>  show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    Warning: ORA-16792: configurable property value is inconsistent with member setting

    stbycdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 1 second ago)

17.After the restart of primary

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    stbycdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

18. The existing DG configuration has the protection mode set to maximum availability. However the new standby was added with log transport mode of ASYNC. Change this to SYNC to keep the same protection level even if one standby goes down.

DGMGRL> show database stby2cdb LogXptMode
  LogXptMode = 'ASYNC'
DGMGRL> edit database stby2cdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> show database stby2cdb LogXptMode
  LogXptMode = 'SYNC'
DGMGRL>  show database stbycdb LogXptMode
  LogXptMode = 'SYNC'

19. If possible switchover to the newly added standby and verify DG configuration is working as expected.

DGMGRL> switchover to stby2cdb
Performing switchover NOW, please wait...
New primary database "stby2cdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stby2cdb"
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  stby2cdb - Primary database
    prodcdb  - Physical standby database
    stbycdb  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

20. Rotate the primary among all databases to verify file name conversion and log transport is working as expected.

DGMGRL> switchover to prodcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Oracle Clusterware is restarting database "stby2cdb" ...
Switchover succeeded, new primary is "prodcdb"
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    stbycdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 61 seconds ago)

DGMGRL> switchover to stbycdb
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  stbycdb  - Primary database
    prodcdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 65 seconds ago)

This concludes the adding of new physical standby to an existing data guard configuration.

Related Posts
Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB
Oracle Data Guard on 12.2 CDB with Oracle Restart
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
Data Guard Broker for 11gR2 RAC
11gR2 Standalone Data Guard (with ASM and Role Separation)
Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync
Enable Active Dataguard on 11gR2 RAC Standby

Monday, July 23, 2018

Start of Service Fails with ORA-16000 on Physical Standby Open for Read Only

Start of database service failed with ora-16000 on physical standby where both CDB and PDB are open read only mode. The DG setup is same one mentioned in earlier post Data Guard on 12.2 CDB.

SQL> show con_name

CON_NAME
----------
CDB$ROOT

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBAPP1                        READ ONLY  NO

srvctl add service -db stbycdb -pdb pdbapp1 -service abc -role PHYSICAL_STANDBY -notification TRUE -failovertype NONE -failovermethod NONE -failoverdelay 0 -failoverretry 0
srvctl start service -db stbycdb -s abc
PRCD-1084 : Failed to start service abc
PRCR-1079 : Failed to start resource ora.stbycdb.abc.svc
CRS-5017: The resource action "ora.stbycdb.abc.svc start" encountered the following error:
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "SYS.DBMS_SERVICE", line 5
ORA-06512: at "SYS.DBMS_SERVICE", line 288
ORA-06512: at line 1
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/diag/crs/city7s/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.stbycdb.abc.svc' on 'city7s' failed

Reason seems to be creating the service with physical standby role is trying to add some rows to the read only database. To fix problem first add the service to the primary with role as physical standby and start the service. It may seems odd to start a database service that it is defined for a physical standby role but that's what needed to resolve this. Once the service is started stop it on the primary before the next steps.

srvctl add service -db prodcdb -pdb pdbapp1 -service abc -role PHYSICAL_STANDBY -notification TRUE -failovertype NONE -failovermethod NONE -failoverdelay 0 -failoverretry 0
srvctl start service -db prodcdb -s abc

lsnrctl status

...
  Instance "prodcdb", status READY, has 1 handler(s) for this service...
Service "prodcdb_DGB" has 1 instance(s).
  Instance "prodcdb", status READY, has 1 handler(s) for this service...
Service "prodcdb_DGMGRL" has 1 instance(s).
  Instance "prodcdb", status UNKNOWN, has 1 handler(s) for this service...
Service "abc" has 1 instance(s).
  Instance "prodcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

srvctl stop service -db prodcdb -s abc

Do few log switches and wait until these logs are applied on the standby. Once logs are applied on standby create and start the service on standby.

srvctl add service -db stbycdb -pdb pdbapp1 -service abc -role PHYSICAL_STANDBY -notification TRUE -failovertype NONE -failovermethod NONE -failoverdelay 0 -failoverretry 0
srvctl start service -db stbycdb -s abc

lsnrctl status

...
  Instance "stbycdb", status READY, has 1 handler(s) for this service...
Service "stbycdb_DGB" has 1 instance(s).
  Instance "stbycdb", status READY, has 1 handler(s) for this service...
Service "stbycdb_DGMGRL" has 1 instance(s).
  Instance "stbycdb", status UNKNOWN, has 1 handler(s) for this service...
Service "abc" has 1 instance(s).
  Instance "stbycdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Useful metalink notes
ORA-16000 Cannot Enable Auto Open of PDB On Physical Standby [ID 2377174.1]
How to create a RAC Database Service With Physical Standby Role Option? [ID 1129143.1]

A! Help

Labels