Data Guard
DML over Standby for Active Data Guard in 19c
Category: Database Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

DML over Standby for Active Data Guard in 19c

With the new 19c version the Data Guard received some attention and now we can do DML over the standby and it will be redirect to primary database. It is not hard to implement, but unfortunately there is no much information about that in the docs about that.
As training exercise I tested this new feature and want to share some information about that. First, the environment that I used (and the requirements too):
  • Primary and Standby databases running 19c.
  • Data Guard in Maximum Availability .
  • Active Data Guard enabled.
Remember that the idea of DML over the standby it is to use in some cases where your reporting application need to update some tables and few records (like audit logins) while processing the data in the standby. The volume of DML is (and will be) low. At this point there is no effort to allow, or create, a multiple active-active datacenters/sites for your database. If you start to execute a lot of DML in the standby side you can impact the primary database and you adding the fact that you can maximize the problems for locks and concurrency.

 

Theorical info about DDL in the standby you can find here and here.  The basic workflow you can see below:

 

 

 

To create the DG configuration you can follow the steps that are available in the internet in many sources, one good example it is here. If you want to see what I made, you can check this file that contains the output from the steps for my config “Log-Create-PHY-STB-19c.txt”.
Before continue, since here we have a lot of interaction between primary and standby, I put primary with BLUE background and the standby it is GREEN in the codes below.
The first step to configure is guarantee that everything is ok with the config for DG. The main thing is check the “Real Time Query” and ON:
 
DGMGRL> show configuration




Configuration - dg19b2




  Protection Mode: MaxAvailability

  Members:

  dg19  - Primary database

    dg19s - Physical standby database




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS   (status updated 31 seconds ago)




DGMGRL> show database dg19s




Database - dg19s




  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 0 seconds ago)

  Apply Lag:          0 seconds (computed 0 seconds ago)

  Average Apply Rate: 2.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    dg19s




Database Status:

SUCCESS




DGMGRL>




After that you enable the parameter ADG_REDIRECT_DML  in both sides. Primary:

[oracle@orcl19p ~]$ sqlplus sys/oracle@dg19 as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:50:38 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> show parameter adg_redirect_dml




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

adg_redirect_dml                     boolean     FALSE

SQL>

SQL> alter system set adg_redirect_dml = true scope = both sid = '*';




System altered.




SQL>




And in standby:

[oracle@orcl19s ~]$ sqlplus sys/oracle@dg19s as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:51:19 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> select open_mode from v$database;




OPEN_MODE

--------------------

READ ONLY WITH APPLY




SQL> show parameter adg_redirect_dml




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

adg_redirect_dml                     boolean     FALSE

SQL>

SQL> alter system set adg_redirect_dml = true scope = both sid = '*';




System altered.




SQL>

To test the DML redirection I created one table with some data in the primary database. Here I amd connected as SYS and without PDB, after I will do the same for PDB and with a normal user:

 

SQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;




Table created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




        C1 TO_CHAR(C3,'DD/M

---------- ----------------

         1 14/04/2019 17:58




SQL>

In the standby you can see the table and do some DML over it:

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




        C1 TO_CHAR(C3,'DD/M

---------- ----------------

         1 14/04/2019 17:58




SQL> select database_role from v$database;




DATABASE_ROLE

----------------

PHYSICAL STANDBY




SQL> delete from testDML;




1 row deleted.




SQL> commit;




Commit complete.




SQL>
And in the primary, you can see that the data was deleted:

 

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




no rows selected




SQL> SELECT TO_CHAR(sysdate, 'DD/MM/RRRR HH24:MI') FROM dual;




TO_CHAR(SYSDATE,

----------------

14/04/2019 18:01




SQL>

 

Locks
But in the case of locks, how it works? Some new event? Some different? To simulate this I inserted the data in the primary again and in the standby I checked and the data appeared in the table:

 

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') as c3 FROM testDML;




        C1 C3

---------- ----------------

         1 14/04/2019 18:04




SQL>
 
In the primary I deleted all the data but without commit to lock all the table:

 

SQL> delete from testDML;




1 row deleted.




SQL>
After that I tried to update all the rows in the table. But, as expected, my session got stuck:

 

SQL> update testDML set c1 = 2;

 

At same time I went to production and check for locks and events:

 

SQL> SELECT username, final_blocking_session, final_blocking_instance, event lockwait, status, machine, service_name , sql_id FROM gv$session WHERE username IS NOT NULL;




USERNAME             FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT                                                         STATUS   MACHINE              SERVICE_NAME                             SQL_ID

-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- ---------------------------------------- -------------

SYS                                                                 OFS idle                                                         ACTIVE   orcl19p.oralocal     SYS$BACKGROUND

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS

PUBLIC                                                              SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS

SYS                                                                 SQL*Net message to client                                        ACTIVE   orcl19p.oralocal     SYS$USERS                                4dzzccufjscvp

SYS                                      86                       1 enq: TX - row lock contention                                    ACTIVE   orcl19s.oralocal     SYS$USERS                                49b39kubnb2d1




SQL>

SQL> col sql_text format a50

SQL> select sql_text from v$sql where sql_id = '49b39kubnb2d1';




SQL_TEXT

--------------------------------------------------

UPDATE "TESTDML" "A1" SET "C1" = 2




SQL>
As you can see, there is no different event. The primary database reports TX coming from one connection of standby database. And you can even see the SQL. Normal behaviour.
But, after some time in lock state, the session in the standby reported a new error. ORA-02049: timeout: distributed transaction waiting for lock and ORA-02063: preceding line from ADGREDIRECT. These exists just to avoid eternal wait in the standby.

 

SQL> update testDML set c1 = 2;

update testDML set c1 = 2

*

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02063: preceding line from ADGREDIRECT


SQL>
 
If you do in the opposite site, deleting the registry standby and not doing the commit. The session in the primary that it is waiting never receive the ORA-02049. And the same for the session in standby, it holds the lock until you commit/rollback.
Over PDB
If you want to do the same using PDB, you need to work a little with services to allow direct connections in the physical standby using the services. Basically you need to create the service and set the role (to start it) as physical_standby. The trick part here is that you need to, at least, call the start command in the primary because doing this you registry it in the service$ table. Since you are in the DG config and running the Active Data Guard (this is requirement because the redo is open with Real Time Query) you can open it in the standby too. I added this info here because with DML over standby this config start to be recurrent.
First, create and add the service in primary database:

 

[oracle@orcl19p ~]$ srvctl add service -db dg19 -service dgpdb_dml -pdb dgpdb -role physical_standby

[oracle@orcl19p ~]$ srvctl start service -db dg19 -service dgpdb_dml -pdb dgpdb

[oracle@orcl19p ~]$

[oracle@orcl19p ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 18:48:28 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> alter session set container = DGPDB;




Session altered.




SQL> set linesize 250

SQL> col NAME format a30

SQL> col NETWORK_NAME format a30

SQL> col pdb format a30

SQL> SELECT name, network_name, pdb FROM service$;




NAME                           NETWORK_NAME                   PDB

------------------------------ ------------------------------ ------------------------------

DGPDB                          DGPDB                          DGPDB

dgpdb_dml                      dgpdb_dml                      DGPDB




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19p ~]$
After that you do the same in the standby.

 

[oracle@orcl19s ~]$ srvctl add service -db dg19s -service dgpdb_dml -pdb dgpdb -role physical_standby

[oracle@orcl19s ~]$ srvctl start service -db dg19s -service dgpdb_dml -pdb dgpdb

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$ srvctl stop database -d dg19s

[oracle@orcl19s ~]$ srvctl start database -d dg19s

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$ srvctl status service -d dg19s

Service dgpdb_dml is running

[oracle@orcl19s ~]$

 

Here something to add. I don’t know why but for me just worked (the service appears as enable to connect) after I restart the standby. Since it is not focus for this post this point, you can find good info here (original post from Ivica Arsov) and here.
After that you can test the same as before (create table in PDB and populate):

 

[oracle@orcl19p ~]$ sqlplus simon/simon@orcl19p/DGPDB




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:37:36 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.




Last Successful login time: Sun Apr 14 2019 21:36:05 +02:00




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;




Table created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL>

 

After that do some DML over standby:

 

[oracle@orcl19s ~]$ sqlplus simon/simon@orcl19s/DGPDB




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:38:17 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.




Last Successful login time: Sun Apr 14 2019 21:37:36 +02:00




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL>

SQL> delete from testDML;




1 row deleted.




SQL>

 

And if you have some locks the behaviour it is the same than before. But now, with the information from user and con_id from connection:

 

SQL> select USERNAME, FINAL_BLOCKING_SESSION, FINAL_BLOCKING_INSTANCE, EVENT LOCKWAIT, STATUS, machine, service_name , sql_id, con_id from gv$session where username is not null;




USERNAME             FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT                                                         STATUS   MACHINE              SERVICE_NAME         SQL_ID            CON_ID

-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- -------------------- ------------- ----------

SYS                                                                 OFS idle                                                         ACTIVE   orcl19p.oralocal     SYS$BACKGROUND                              0

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS                                   1

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS                                   1

PUBLIC                                                              SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS                                   1

SIMON                                   102                       1 enq: TX - row lock contention                                    ACTIVE   orcl19p.oralocal     dgpdb                4sqjaugwcxar0          3

SYS                                                                 SQL*Net message to client                                        ACTIVE   orcl19p.oralocal     SYS$USERS            cssctts2u81n4          1

SIMON                                                               SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS                                   3




7 rows selected.




SQL>

SQL> select username, machine, service_name, program from gv$session where inst_id = 1 and sid = 102;




USERNAME             MACHINE              SERVICE_NAME         PROGRAM

-------------------- -------------------- -------------------- ------------------------------------------------

SIMON                orcl19s.oralocal     SYS$USERS            [email protected] (TNS V1-V3)




SQL>

 

As you can see above the connection appear in the primary as a normal connection, nothing complicate or special to handle. And in the listener log the connection (coming from standby when receiving the DML) is normal:

 

2019-04-14T22:12:57.977135+02:00

14-APR-2019 22:12:57 * (CONNECT_DATA=(SERVICE_NAME=8106653b19ca6636e053016410ac3c21)(CID=(PROGRAM=oracle)(HOST=orcl19s.oralocal)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.200.1)(PORT=36782)) * establish * 8106653b19ca6636e053016410ac3c21 * 0

2019-04-14T22:14:02.121149+02:00

14-APR-2019 22:14:02 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=dg19)(CID=(PROGRAM=oracle)(HOST=orcl19s.oralocal)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.200.1)(PORT=36786)) * establish * dg19 * 0

 

The behaviour it is so normal that you will see the cursors open in the primary (the lock system is by row):

 

SQL> create index ixtestDML on testDML(c1) tablespace users;




Index created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> INSERT INTO testDML(c1, c3) VALUES (2, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL>




    ###########

    In the sdtandby database

    ###########

    SQL> delete from testDML where c1 = 1;




    1 row deleted.




    SQL>




SQL> update testDML set c1 = 3 where c1 = 2;




1 row updated.




SQL>

SQL> select  sql_text, user_name from v$open_cursor where user_name = 'SIMON' and upper(sql_text) like '%TESTDML%';




SQL_TEXT                                                                                             USER_NAME

---------------------------------------------------------------------------------------------------- --------------------

DELETE FROM "TESTDML" "A1" WHERE "A1"."C1"=1                                                         SIMON

update testDML set c1 = 3 where c1 = 2                                                               SIMON




SQL>

 

ORA-16397
If you receive the error ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed this is linked with the way that you connected in the database. If you use “/ as sysdba” without username and password, when you try to do the DML you hit the error. To solve this, connect using username, password and database:

 

[oracle@orcl19s ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:45 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);

INSERT INTO testDML(c1, c3) VALUES (1, sysdate)

            *

ERROR at line 1:

ORA-16397: statement redirection from Oracle Active Data Guard standby database

to primary database failed







SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19s ~]$ sqlplus sys/oracle@dg19s as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:55 2019

Version 19.2.0.0.0




Copyright (c) 1982, 2018, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19s ~]$

 

ORA-16000
If you disable the ADG_REDIRECT_DML you will receive the error ORA-16000: database or pluggable database open for read-only access when you try to execute the DML:

 

SQL> alter system set ADG_REDIRECT_DML=false scope = both sid = '*';




System altered.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);

INSERT INTO testDML(c1, c3) VALUES (1, sysdate)

            *

ERROR at line 1:

ORA-16000: database or pluggable database open for read-only access







SQL>

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”


Generating an export from a Data Guard database using NETWORK_LINK.
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Generating an export from a Data Guard database using NETWORK_LINK

This article explains how we can export Data Guard using NETWORK_LINK.
 
The Physical Standby database must be opened in “READ ONLY” mode.

Steps to execute to export from Physical Standby Database

Physical Standby Database

— Connect to Physical Standby database and check its status

 

 

[oracle@vm1 admin] sqlplus / as sysdba


SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
PHYSTBY MOUNTED

-- Cancel managed recovery and open database in "READ ONLY" mode.


SQL> alter database recover managed standby database cancel;



SQL> alter database open read only;




-- Verify database status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
VTABOLDG OPEN


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY


 

“Non Standby” Database           

On the same machine I created a new instance just to serve as a bridge to perform the procedure.

-- create DB Link, Oracle Directory.


[oracle@vm1 admin] sqlplus / as sysdba


SQL> create database link expd connect to system identified by oracle using ‘DG_VTABOL’;

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
--------------------
DP

SQL> select db_unique_name from v$database@exp;

DB_UNIQUE_NAME
--------------------
VTABOL

SQL> create directory datapump as ‘/tmp’;

 


Use NETWORK_LINK to database link above to connect to the Physical Standby database:

 

[oracle@vm1 admin] expdp DIRECTORY=DATAPUMP network_link=EXP DUMPFILE=DG_EXPDP.dmp SCHEMAS=DBA_DISCOVER,ITAMARATI,VENDASEXTERNAS,VTABOL,ABSTRATOS logfile=expdp.log PARALLEL=4



Export: Release 12.1.0.2.0 - Production on Fri Sep 6 17:32:54 2019

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_SCHEMA_02":  sys/******** AS SYSDBA DIRECTORY=DATAPUMP network_link=EXP DUMPFILE=AWS.dmp SCHEMAS=DBA_DISCOVER,ITAMARATI,VENDASEXTERNAS,VTABOL,ABSTRATOS logfile=expdp.log PARALLEL=4

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 84.29 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/JOB

Processing object type SCHEMA_EXPORT/REFRESH_GROUP

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ




Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:

  /tmp/DG_EXPDP.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_02" completed with 0 error(s) at Fri Sep 6 19:55:35 2019 elapsed 0 02:22:29


 

On Physical Standby Database

[oracle@vm1 admin] sqlplus / as sysdba


SQL> shutdown immediate



SQL> startup mount




SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;




SQL> SELECT
        ARCH.THREAD# "Thread",
        ARCH.SEQUENCE# "Last Sequence Received",
        APPL.SEQUENCE# "Last Sequence Applied",
        (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
        (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
        (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
        (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
        (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
   WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

  2    3    4    5    6    7    8    9   10   11

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

         1                  72153                 72153          0

         2                  67021                 67021          0




SQL>

 

I hope this helps you!!!

 

Andre Luiz Dutra Ontalba 
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”


Observer, Quorum
Category: Database Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

Observer, Quorum

This article closes the series for DG and Fast-Start Failover that I covered with more details the case of isolation can leverage the shutdown of your healthy/running primary database. The “ORA-16830: primary isolated from fast-start failover partners”.
In the first article, I wrote about how one simple detail that impacts dramatically the reliability of your MAA environment. Where you put your Observer in DG environment (when Fast-Start Failover is in use) have a core figure in case of outages, and you can face Primary isolation and shutdown. Besides that, there is no clear documentation to base yourself about “pros and cons” to define the correct place for Observer. You read more in my article here.
In the second article, I wrote about one new feature that can help to have more protected and cover more scenarios for Fast-Start Failover/DG. Using Multiple Observers you can remove the single point of failure and allow you to put one Observer in each side of your environment (primary, standby and a third one). You can read more in my article here.
In this last article I discuss how, even using all the features, there is no               perfect solution. Another point is discussing here is how (maybe) Oracle can improve that. Below I will show more details that even multiple observers continue to shutdown a healthy primary database. Unfortunately, it is a lot of tech info and is a log thread output. But you can jump directly to the end to see the discussion about how this can be improved.

 

Fast-Start Failover and Multiple Observers

Because the design of Fast-Start Failover, Broker and DG even using multiple observers, we continue to have the decision (to failover or no the database) based in just one observer report. The others are just backup from the master, but what they saw not count when the failover scenario hit the environment. Even if the Primary Database can receive connections from other two observers, but not receive the connection master (and standby), it decides to shutdown because it is “isolated”.
Look the example below where I have three observers (one in each site and a third one in the “cloud”):

 

 

The image above can be translated in this config for Broker where the Master Observer it is “dbobss” (that resides in standby datacenter):

 

DGMGRL> show fast_start failover;




Fast-Start Failover: ENABLED




  Threshold:          240 seconds

  Target:             orcls

  Observers:      (*) dbobss

                      dbobsp

                      dbobst

  Lag Limit:          30 seconds (not in use)

  Shutdown Primary:   TRUE

  Auto-reinstate:     TRUE

  Observer Reconnect: 10 seconds

  Observer Override:  FALSE




Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Write Errors          YES




  Oracle Error Conditions:

    (none)




DGMGRL>

 

Check that in this case I set the threshold for fast-start failover as 240 seconds just to have more time to show the logs. But be aware that this parameter defines the time that your system waits/freeze until proceeding with the failover in case of system isolation or lost the primary.
Here, to simulate side isolation and same behavior for the first article, I shutdown the network from standby (that talk with primary database), for the Master observer (just network that talks with primary database), and for others observers the network for standby communication. The image below reflects this:

 

 

After that, the log from Broker in the primary start to report a lot of information and I will discuss bellow. You can click here to check the full text for this. From this log you can see that the primary detected that lost communication with standby at 20:14:06.504 and in 4 minutes (240 seconds) will trigger the failover. But the most important part is marked below:

 

2019-05-05 20:14:36.506                      LGWR: still awaiting FSFO ack after 30 seconds

2019-05-05 20:14:41.968                      DMON: Creating process FSFP

2019-05-05 20:14:44.976                      FSFP: Process started

2019-05-05 20:14:44.976                      FSFP: The ping from current master observer (No. 1) to primary database timed out.

2019-05-05 20:14:44.976                      FSFP: Preparing to switch master observer from observer 1 to observer 2

2019-05-05 20:14:44.976                      FSFP: FSFO SetState(st=43 "SET SWOB INPRG", fl=0x0 "", ob=0x0, tgt=0, v=0)

2019-05-05 20:14:44.976                      DMON: FSFP successfully started

2019-05-05 20:14:44.977                      FSFP: persisting FSFO state flags=0x10040001, version=128, enver=123,target=2, lad=, obid=0x32cc2adc (852241116), threshold=240, laglim=30, obslim=30

2019-05-05 20:14:44.979                      FSFP: Data Guard Broker initiated a master observer switch since the current master observer cannot reach the primary database

2019-05-05 20:14:44.980                      FSFP: NET Alias translated: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbstb_dg)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls) (UR=A)))

2019-05-05 20:14:44.980                      FSFP: Net link using RFIUPI_DB_CDESC, site=2, inst=0

2019-05-05 20:14:44.981                      FSFP: Connect to member orcls using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbstb_dg)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(UR=A)(SERVICE_NAME=orcls_DGB)))

2019-05-05 20:14:44.981                      FSFP: 0 x 10 seconds network retry READY

2019-05-05 20:14:51.506                      LGWR: still awaiting FSFO ack after 45 seconds

2019-05-05 20:14:55.120                      FSFP: Failed to connect to remote database orcls. Error is ORA-12543

2019-05-05 20:14:55.120                      FSFP: FSFO SetState(st=44 "CLR SWOB INPRG", fl=0x0 "", ob=0x0, tgt=0, v=0)

2019-05-05 20:14:55.120                      FSFP: persisting FSFO state flags=0x40001, version=128, enver=123,target=2, lad=, obid=0x32cc2adc (852241116), threshold=240, laglim=30, obslim=30

2019-05-05 20:15:06.507                      LGWR: still awaiting FSFO ack after 60 seconds

2019-05-05 20:15:10.124                      FSFP: The ping from current master observer (No. 1) to primary database timed out.

2019-05-05 20:15:10.124                      FSFP: Preparing to switch master observer from observer 1 to observer 2

2019-05-05 20:15:10.124                      FSFP: FSFO SetState(st=43 "SET SWOB INPRG", fl=0x0 "", ob=0x0, tgt=0, v=0)

2019-05-05 20:15:10.124                      FSFP: persisting FSFO state flags=0x10040001, version=128, enver=123,target=2, lad=, obid=0x32cc2adc (852241116), threshold=240, laglim=30, obslim=30

2019-05-05 20:15:10.128                      FSFP: Data Guard Broker initiated a master observer switch since the current master observer cannot reach the primary database

2019-05-05 20:15:10.128                      FSFP: NET Alias translated: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbstb_dg)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls) (UR=A)))

2019-05-05 20:15:10.128                      FSFP: Net link using RFIUPI_DB_CDESC, site=2, inst=0

2019-05-05 20:15:10.128                      FSFP: Connect to member orcls using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbstb_dg)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(UR=A)(SERVICE_NAME=orcls_DGB)))

2019-05-05 20:15:10.128                      FSFP: 0 x 10 seconds network retry READY

2019-05-05 20:15:14.979                      DMON: A Fast-Start Failover target switch is necessary because the primary cannot reach the Fast-Start Failover target standby database

2019-05-05 20:15:14.980                      DMON: A target switch was not attempted because the observer has not pinging primary recently.

2019-05-05 20:15:21.246                      FSFP: Failed to connect to remote database orcls. Error is ORA-12543

2019-05-05 20:15:21.247                      FSFP: FSFO SetState(st=44 "CLR SWOB INPRG", fl=0x0 "", ob=0x0, tgt=0, v=0)

2019-05-05 20:15:21.247                      FSFP: persisting FSFO state flags=0x40001, version=128, enver=123,target=2, lad=, obid=0x32cc2adc (852241116), threshold=240, laglim=30, obslim=30

 

Above you can see that the broker in the primary detected that master observer is down and tries to realize the switch to another observer. Unfortunately, since it was not possible to connect with the standby database was impossible to change it. Look the event “SET SWOB INPRG” was triggered but the “CLR SWOB INPRG” was impossible because “Failed to connect to remote database orcls. Error is ORA-12543”. If you compare with the log from the previous article (when I changed the master observer manually) here we don’t see the “SET OBID” and even “OBSERVED” events.
So, basically, because the standby database was incommunicable, the primary database can’t swap the master observer (even if receive connection with them). This behavior does not change even if you set big values for “FastStartFailoverThreshold”, “OperationTimeout”, “CommunicationTimeout” parameters.

Quorum

In one scenario for DG with Fast-Start Failover enabled you can hit a shutdown from your healthy primary database because it thinks that it is isolated when lost communication from standby and observer. Even when you add multiple observers the behavior does not change.
By the actual design for DG, this is 100% correct, it is impossible for each side to know if it is isolated or no. The primary, when lost the connection from Master Observer and Standby, shutdown because can’t guarantee the transactions. And the standby (if alive and have the connection for the Master Observer) failover to be the next primary.  The problem is that even using multiple observers, where you can have odd votes, you still face isolation if the minimal part (standby + master observer) vote itself (even if it is isolated). As discussed in the first article, where you put your observer it is very important, but you need to check the pros and cons for your decision.
Going deeper, when you use the Fast-Start Failover your DG start to operate in sync (even in Max Availability), and in the first sign of communication failure the primary database freeze and don’t accept more transactions. No records are stored, and this is why, even with multiple observers, the primary can’t switch to another one. The database itself is blocked to store this change.
One option that can improve this gap it is Broker start to use Quorum (or voting system) to control if it is ok to proceed with the failover or no. In the scenario that I showed before, where the primary still has a connection from others two observers, the shutdown of the healthy primary not occur because have more votes/connections from observers (2) compared with standby (that have just one). Unfortunately, there is not a perfect solution, if one outage occurs in the primary side, and you lost connection with most part of observers, the standby can’t failover because don’t know if have votes to do that or no.
This can be a future and additional option for Fast-Start Failover environments. But the key and fundamental part is still there, even quorum will not work correctly if you still put all the observers in just one side. And become even more critical when, now, you can have hybrid clouds with one for databases and other for applications.
The idea about writing these articles was to show the problem and try to fill the gap about the place to put your observer. There is no clear doc to search the “pros and cons” for each side option. Unfortunately, even with the new features, still exists a gap that needs to be covered to improve the reliability for some scenarios. There is no perfect solution, but it is important to know what you can do to reach MAA at the highest level.
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for community.”

 


Monitoring Data Guard operation
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Monitoring Data Guard operation

As we saw in the last article on installing DataGuard in Oracle 19C, we will now see how to monitor Data Guard.
 
 
— This script is to be run on the Standby of a Data Guard Physical Standby Site

 

set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
— Output the results to this file
spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
— Get the current Date
set feedback on
select systimestamp from dual;

-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on

col db_unique_name format a15
col flashb_on format a10

select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
— Current SCN – this value on the primary and standby sites where real time apply is in place should be nearly the same

 

select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;

-- Incarnation Information
--

set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--

column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999

select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;

-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs

set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
— Data Guard Parameters

set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
— Managed Recovery State

 

set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on

select systimestamp from dual;

column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on

column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';
— If there is a lag remove the comment for the select below

 

-- SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on

select * from v$archive_gap;

set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on

select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
spool off

 



Hope this helps. See you !!!
 
André  Ontalba  – www.dbadutra.com
 
 
 
 
 
 

 Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

 


Data Guard Physical Setup using Data Broker in Oracle Database 19c
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

 

 Data Guard Physical Setup using Data Broker in Oracle Database 19c

 
 
 

Environments

  • You have two servers (VMs or physical) with an operating system and Oracle installed on them. My environment I’ve used Oracle Linux 7.6 and Oracle Database 19c.
  • The primary server (duts-dg1) has a running instance.
  • The standby server (duts-dg2) has a software only installation.
  • There is nothing blocking communication between the machines over the listener ports.

 

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE

————

NOARCHIVELOG

SQL>

If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

— Make sure at least one logfile is present.

ALTER SYSTEM SWITCH LOGFILE;

Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.

— If Oracle Managed Files (OMF) is not used.

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo01.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo02.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo03.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo04.log’) SIZE 100M;

— If Oracle Managed Files (OMF) is used.

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. I always use it in my environments.

ALTER DATABASE FLASHBACK ON;

 

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “duts” on the primary database.

SQL> show parameter db_name

NAME                                TYPE       VALUE

———————————— ———– ——————————

db_name                             string     duts

SQL> show parameter db_unique_name

NAME                                TYPE       VALUE

———————————— ———– ——————————

db_unique_name                      string     duts

SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example, the standby database will have the value “duts_stby”.

Make sure the STANDBY_FILE_MANAGEMENT parameter is set.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

 

Service Setup

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers.

You can create these using the Network Configuration Utility (netca) or manually.

The following entries were used during this setup. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.

duts =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = duts)

    )

  )

duts_stby =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = duts)

    )

  )

The “$ORACLE_HOME/network/admin/listener.ora” file on the primary server contains the following configuration.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = duts_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)

      (SID_NAME = duts)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

The “$ORACLE_HOME/network/admin/listener.ora” file on the standby server contains the following configuration.

Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = duts_stby_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)

      (SID_NAME = duts)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Once the listener.ora changes are in place, restart the listener on both servers.

lsnrctl stop

lsnrctl start

 

Standby Server Setup

Prepare for Duplicate

Create a parameter file for the standby database called “/tmp/initduts_stby.ora” with the following contents.

*.db_name=’duts’

Create the necessary directories on the standby server.

mkdir -p /u02/data/duts/pdbseed

mkdir -p /u02/data/duts/pdb1

mkdir -p /u02/app/oracle/fast_recovery_area/duts

mkdir -p /u02/app/oracle/admin/duts/adump

Create a password file, with the SYS password matching that of the primary database.

$ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwduts password=oracle entries=10

 

 

Create Standby Using DUPLICATE

Start the auxiliary instance on the standby server by starting it using the temporary “init.ora” file.

$ export ORACLE_SID=duts

$ sqlplus / as sysdba

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.

$ rman TARGET sys/oracle@duts AUXILIARY sys/oracle@duts_stby

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts_stby’ COMMENT ‘Is standby 19c’

  NOFILENAMECHECK;

If you need to convert file locations, or alter any initialization parameters, you can do this during the DUPLICATE using the SET command.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts_stby’ COMMENT ‘Is standby 19c’

    SET db_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’

    SET log_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’

    SET job_queue_processes=’0′

  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.
  •  

Once the command is complete, we can start using the broker.

 

Enable Broker

At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command.

ALTER SYSTEM SET dg_broker_start=true;

On the primary server, issue the following command to register the primary server with the broker.

$ dgmgrl sys/oracle@duts

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:39:33 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS duts CONNECT IDENTIFIER IS duts;

Configuration “dg_config” created with primary database “duts”

DGMGRL>

Now add the standby database.

DGMGRL> ADD DATABASE duts_stby AS CONNECT IDENTIFIER IS duts_stby MAINTAINED AS PHYSICAL;

Database “duts_stby” added

DGMGRL>

Now we enable the new configuration.

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL>

The following commands show how to check the configuration and status of the databases from the broker.

DGMGRL> SHOW CONFIGURATION;

Configuration – dg_config

Protection Mode: MaxPerformance

  Members:

  duts      – Primary database

    duts_stby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 26 seconds ago)

DGMGRL> SHOW DATABASE duts;

Database – duts

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    duts

Database Status:

SUCCESS

DGMGRL> SHOW DATABASE duts_stby;

Database – duts_stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 15.00 KByte/s

  Real Time Query:    OFF

  Instance(s):

    duts

Database Status:

SUCCESS

DGMGRL>

 

 

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (duts) and switchover to the standby database (duts_stby).

$ dgmgrl sys/oracle@duts

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:55:33 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> SWITCHOVER TO duts_stby;

Performing switchover NOW, please wait…

Operation requires a connection to instance “duts” on database “duts_stby”

Connecting to instance “duts”…

Connected as SYSDBA.

New primary database “duts_stby” is opening…

Operation requires start up of instance “duts” on database “duts”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “duts_stby”

DGMGRL>

Let’s switch back to the original primary. Connect to the new primary (duts_stby) and switchover to the new standby database (duts).

$ dgmgrl sys/oracle@duts_stby

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:57:20 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> SWITCHOVER TO duts;

Performing switchover NOW, please wait…

Operation requires a connection to instance “duts” on database “duts”

Connecting to instance “duts”…

Connected as SYSDBA.

New primary database “duts” is opening…

Operation requires start up of instance “duts” on database “duts_stby”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “duts”

DGMGRL>

 

Database Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (duts_stby) and failover.

$ dgmgrl sys/oracle@duts_stby

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 15:00:20 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> FAILOVER TO duts_stby;

Performing failover NOW, please wait…

Failover succeeded, new primary is “duts_stby”

DGMGRL>

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.

DGMGRL> REINSTATE DATABASE duts;

Reinstating database “duts”, please wait…

Operation requires shut down of instance “duts” on database “duts”

Shutting down instance “duts”…

ORACLE instance shut down.

Operation requires start up of instance “duts” on database “duts”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “duts” …

Reinstatement of database “duts” succeeded

DGMGRL>

If flashback database is not enabled, you would have to manually recreate duts as a standby. The basic process is the reverse of what you did previously.

# 1) Cleanup the old instance.

sqlplus / as sysdba <<EOF

SHUTDOWN IMMEDIATE;

EXIT;

EOF

rm -Rf /u01/data/duts/*

rm -Rf /u01/app/oracle/fast_recovery_area/duts

rm -Rf /u01/app/oracle/fast_recovery_area/duts_stby

rm -Rf /u01/app/oracle/admin/duts

mkdir -p /u01/app/oracle/fast_recovery_area/duts

mkdir -p /u01/app/oracle/admin/duts/adump

mkdir -p /u01/data/duts/pdbseed

mkdir -p /u01/data/duts/pdb1

rm $ORACLE_HOME/dbs/spfileduts.ora

export ORACLE_SID=duts

sqlplus / as sysdba <<EOF

STARTUP NOMOUNT PFILE=’/tmp/initduts_stby.ora’;

EXIT;

EOF

# 2) Connect to RMAN.

$ rman TARGET sys/oracle@duts_stby AUXILIARY sys/oracle@duts

# 3) Duplicate the database.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts’ COMMENT ‘Is standby 19c’

    SET db_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’

    SET log_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’

    SET job_queue_processes=’0′

  NOFILENAMECHECK;

# 4) Connect to DGMDRL on the current primary.

$ dgmgrl sys/oracle@duts_stby

# 5) Enable the new standby.

DGMGRL> ENABLE DATABASE duts;

 

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database  once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database, as shown above.

 

 

Creation of application services

To facilitate the administration of client connections, and to make SWITCHOVER operations more transparent for clients, it is recommended to create database SERVICES.

Example, service definition « DUTSS » :

begin DBMS_SERVICE.CREATE_SERVICE ( service_name => ‘DUTSS’,

                                      network_name => ‘ DUTSS ‘,

                                      failover_method => ‘BASIC’,

                                      failover_type => ‘SELECT’,

                                      failover_retries => 180,

                                      failover_delay => 1);

end;

/

In this case, there are 180 retries and a delay of 1 second (so basically 3 minutes before switching).  This should be adapted depending on your needs and requirements.

These are the services that should be used by client application connections.

 

Creating the Startup trigger

To manage the automatic start of the services, in particular in the event of a role transition, the following TRIGGER must be created (example for the DUTSS service). The trigger must be created under SYS:

Connect SYS as SYSDBA

CREATE OR REPLACE TRIGGER manage_app_services

   AFTER STARTUP

   ON DATABASE

DECLARE

   role   VARCHAR (30);

BEGIN

   SELECT   DATABASE_ROLE INTO role FROM V$DATABASE;

   IF role = ‘PRIMARY’

   THEN

      DBMS_SERVICE.START_SERVICE (‘DUTSS’);

   END IF;

END;

/

Then we restart the PRIMARY database to check that the service is started:

sqlplus / as sysdba

shutdown immediate ;

startup

 

Client connections

To make the role transitions (as a result of a SWITCHOVER or FAILOVER)   transparent to users the client database connection string needs to be configured with a failover connection string.

This can be configured at the TNSNAMES.ORA file level by configuring two addresses or two descriptions for the same alias.

Example of an alias defined for DUTS:

DUTS =

  (DESCRIPTION =

    (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

        )

    (CONNECT_DATA = (SERVICE_NAME = DUTSS))

  )    

With this method, clients will attempt to first connect to the first address (corresponding to the primary server and database). If it is operational (which should be the case in a nominal way), the connection is established with this base.

If this first address does not respond (primary server unavailable or services stopped), then the client tries to connect to the second address (pointing to the standby server). If it is operational (which will be the case only after a SWITCHOVER or a FAILOVER), then the client will connect to the emergency base transparently and automatically.

Hope this helps. See you !!!
André  Ontalba  – http://www.dbadutra.com


LUXOUG – LUXEMBOURG ORACLE USERS GROUP
© 2024 LUXOUG – LUXEMBOURG ORACLE USERS GROUP