Infrastructure at your Service

Franck Pachot

Active Data Guard services in Multitenant

By September 16, 2017 Oracle No Comments

By Franck Pachot

.
A database (or the CDB in multitenant) registers its name as the default service. When a standby database is on the same server, or same cluster, you have no problem because this database name is the db_unique_name which is different between the primary and the standby(s).

In multitenant, in addition to that, each PDB registers its name as a service. But the PDB name is the same in the primary and the standby database. This means that we have the same service name registered for the PDB in primary and standby:


Service "pdb1" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...

We cannot change that, and then it is strongly recommended to create different services for the PDB in primary and standby.

The PDB default service name

Here is what we want to avoid.
I’ve a container database (db_name=CDB2) with its primary (db_unique_name=CDB2A) and standby (db_unique_name=CDB2B) on the same server, registered to the same listener:


Service "59408d6bed2c1c8ee0536a4ea8c0cfa9" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2A" has 1 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2AXDB" has 1 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2A_DGB" has 1 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
Service "CDB2A_DGMGRL" has 1 instance(s).
  Instance "CDB2A", status UNKNOWN, has 1 handler(s) for this service...
Service "CDB2B" has 1 instance(s).
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2BXDB" has 1 instance(s).
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2B_DGB" has 1 instance(s).
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "CDB2B_DGMGRL" has 1 instance(s).
  Instance "CDB2B", status UNKNOWN, has 1 handler(s) for this service...
Service "CDB2_CFG" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...

The PDB1 service is registered from both instances, and then when I use it in my connection string I’m connected at random to the primary or the standby:


22:27:46 SQL> connect sys/[email protected]//localhost:1522/pdb1 as sysdba
Connected.
22:27:51 SQL> select * from v$instance;
 
INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME  VERSION     STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER  LOG_SWITCH_WAIT  LOGINS   SHUTDOWN_PENDING  DATABASE_STATUS  INSTANCE_ROLE     ACTIVE_STATE  BLOCKED  CON_ID  INSTANCE_MODE  EDITION  FAMILY  DATABASE_TYPE
---------------  -------------  ---------  -------     ------------  ------  --------  -------  --------  ---------------  ------   ----------------  ---------------  -------------     ------------  -------  ------  -------------  -------  ------  -------------
1                CDB2B          VM106      12.2.0.1.0  15-SEP-17     OPEN    NO        1        STARTED                    ALLOWED  NO                ACTIVE           PRIMARY_INSTANCE  NORMAL        NO       0       REGULAR        EE               SINGLE
 
22:28:00 SQL> connect sys/[email protected]//localhost:1522/pdb1 as sysdba
Connected.
22:28:06 SQL> /
 
INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME  VERSION     STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER  LOG_SWITCH_WAIT  LOGINS   SHUTDOWN_PENDING  DATABASE_STATUS  INSTANCE_ROLE     ACTIVE_STATE  BLOCKED  CON_ID  INSTANCE_MODE  EDITION  FAMILY  DATABASE_TYPE
---------------  -------------  ---------  -------     ------------  ------  --------  -------  --------  ---------------  ------   ----------------  ---------------  -------------     ------------  -------  ------  -------------  -------  ------  -------------
1                CDB2A          VM106      12.2.0.1.0  15-SEP-17     OPEN    NO        1        STARTED                    ALLOWED  NO                ACTIVE           PRIMARY_INSTANCE  NORMAL        NO       0       REGULAR        EE               SINGLE
 
22:28:07 SQL> connect sys/[email protected]//localhost:1522/pdb1 as sysdba
Connected.
22:28:10 SQL> /
 
INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME  VERSION     STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER  LOG_SWITCH_WAIT  LOGINS   SHUTDOWN_PENDING  DATABASE_STATUS  INSTANCE_ROLE     ACTIVE_STATE  BLOCKED  CON_ID  INSTANCE_MODE  EDITION  FAMILY  DATABASE_TYPE
---------------  -------------  ---------  -------     ------------  ------  --------  -------  --------  ---------------  ------   ----------------  ---------------  -------------     ------------  -------  ------  -------------  -------  ------  -------------
1                CDB2B          VM106      12.2.0.1.0  15-SEP-17     OPEN    NO        1        STARTED                    ALLOWED  NO                ACTIVE           PRIMARY_INSTANCE  NORMAL        NO       0       REGULAR        EE               SINGLE
 
22:28:11 SQL> connect sys/[email protected]//localhost:1522/pdb1 as sysdba
Connected.
22:28:13 SQL> /
 
INSTANCE_NUMBER  INSTANCE_NAME  HOST_NAME  VERSION     STARTUP_TIME  STATUS  PARALLEL  THREAD#  ARCHIVER  LOG_SWITCH_WAIT  LOGINS   SHUTDOWN_PENDING  DATABASE_STATUS  INSTANCE_ROLE     ACTIVE_STATE  BLOCKED  CON_ID  INSTANCE_MODE  EDITION  FAMILY  DATABASE_TYPE
---------------  -------------  ---------  -------     ------------  ------  --------  -------  --------  ---------------  ------   ----------------  ---------------  -------------     ------------  -------  ------  -------------  -------  ------  -------------
1                CDB2A          VM106      12.2.0.1.0  15-SEP-17     OPEN    NO        1        STARTED                    ALLOWED  NO                ACTIVE           PRIMARY_INSTANCE  NORMAL        NO       0       REGULAR        EE               SINGLE

I don’t want to use a service that connects at random and then I need to create different services.

Read-Only service for the Active Data Guard standby

I’m in Oracle Restart and I create the service with srvctl (but you can also create it with dbms_service when not running with Grid Infrastructure):


srvctl   add service -db cdb2b -service pdb1_ro -pdb pdb1 -role physical_standby

This creates the service for the standby database (CDB2B) to be started when in physical standby role, and the service connects to the pluggable database PDB1.
But I cannot start it:


srvctl start service -db cdb2b -service pdb1_ro -pdb pdb1
 
 
PRCD-1084 : Failed to start service pdb1_ro
PRCR-1079 : Failed to start resource ora.cdb2b.pdb1_ro.svc
CRS-5017: The resource action "ora.cdb2b.pdb1_ro.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 "/u01/app/12.2/diag/crs/vm106/crs/trace/ohasd_oraagent_oracle.trc".
 
CRS-2674: Start of 'ora.cdb2b.pdb1_ro.svc' on 'vm106' failed

The reason is that the service information must be stored in the dictionary, SYS.SERVICE$ table, and you cannot do that on a read-only database.

This has been explained a long time ago by Ivica Arsov on his blog: https://iarsov.com/oracle/data-guard/active-services-on-physical-standby-database/ and nothing has changed. You need to create the service on the primary so that the update of SYS.SERVICE$ is propagated to the standby database through log shipping:


srvctl   add service -db cdb2a -service pdb1_ro -pdb pdb1 -role physical_standby

This is not sufficient because the insert in SYS.SERVICE$ does not occur yet:


SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> show pdbs
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---------   ----------
3       PDB1      READ WRITE  NO
 
SQL> select * from service$;
 
SERVICE_ID  NAME  NAME_HASH   NETWORK_NAME  CREATION_DATE  CREATION_DATE_HASH  DELETION_DATE  FAILOVER_METHOD  FAILOVER_TYPE  FAILOVER_RETRIES  FAILOVER_DELAY  MIN_CARDINALITY  MAX_CARDINALITY  GOAL  FLAGS  EDITION  PDB   RETENTION_TIMEOUT  REPLAY_INITIATION_TIMEOUT  SESSION_STATE_CONSISTENCY  SQL_TRANSLATION_PROFILE  MAX_LAG_TIME  GSM_FLAGS  PQ_SVC  STOP_OPTION  FAILOVER_RESTORE  DRAIN_TIMEOUT
----------  ----  ---------   ------------  -------------  ------------------  -------------  ---------------  -------------  ----------------  --------------  ---------------  ---------------  ----  -----  -------  ---   -----------------  -------------------------  -------------------------  -----------------------  ------------  ---------  ------  -----------  ----------------  -------------
14          pdb1  1888881990  pdb1          15-SEP-17      1332716667                                                                                                                                   136             PDB1  

As explained by Ivica in his blog post, we need to start the service once to have the row inserted in SERVICE$:


srvctl start service -db cdb2a -service pdb1_ro -pdb pdb1
srvctl stop service -db cdb2a -service pdb1_ro

Now the service information is persistent in the dictionary:


SQL> alter session set container=PDB1;
Session altered.
 
SQL> show pdbs
 
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----   ----------
3       PDB1      READ WRITE  NO
 
SQL> select * from service$;
 
SERVICE_ID  NAME     NAME_HASH   NETWORK_NAME  CREATION_DATE  CREATION_DATE_HASH  DELETION_DATE  FAILOVER_METHOD  FAILOVER_TYPE  FAILOVER_RETRIES  FAILOVER_DELAY  MIN_CARDINALITY  MAX_CARDINALITY  GOAL  FLAGS  EDITION  PDB   RETENTION_TIMEOUT  REPLAY_INITIATION_TIMEOUT  SESSION_STATE_CONSISTENCY  SQL_TRANSLATION_PROFILE  MAX_LAG_TIME  GSM_FLAGS  PQ_SVC  STOP_OPTION  FAILOVER_RESTORE  DRAIN_TIMEOUT
----------  ----     ---------   ------------  -------------  ------------------  -------------  ---------------  -------------  ----------------  --------------  ---------------  ---------------  ----  -----  -------  ---   -----------------  -------------------------  -------------------------  -----------------------  ------------  ---------  ------  -----------  ----------------  -------------
14          pdb1     1888881990  pdb1          15-SEP-17      1332716667                                                                                                                                   136             PDB1                                                                                                                                                             
1           pdb1_ro  1562179816  pdb1_ro       15-SEP-17      1301388390                                                         0                 0                                                 0     8               PDB1  86400              300                        DYNAMIC                                             ANY           0                  0            0                 0

This is from the primary, but after the redo has been transported and applied, I have the same on the standby. Now I can start the service I’ve created for the standby:


srvctl start service -db cdb2b -service pdb1_ro -pdb pdb1

Here is the new service registered on the listener, which I can use to connect to the read-only PDB1 on the Active Data Guard standby:


Service "pdb1" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_ro" has 1 instance(s).
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_rw" has 1 instance(s).

Read-Write service for the primary

You can see above that in order to select from SERVICE$ I connected to CDB$ROOT and switched to PDB1 with ‘set container’. There’s no other choice because using the service name directs me at random to any instance. Then, I need a service to connect to the primary only, and I’ll call it PDB1_RW as it is opened in Read Write there.


srvctl   add service -db cdb2a -service pdb1_rw -pdb pdb1 -role primary
srvctl start service -db cdb2a -service pdb1_rw

Finally, here are the services registered from the listener:


Service "pdb1" has 2 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_ro" has 1 instance(s).
  Instance "CDB2B", status READY, has 1 handler(s) for this service...
Service "pdb1_rw" has 1 instance(s).
  Instance "CDB2A", status READY, has 1 handler(s) for this service...

I’ll probably never use the ‘PDB1’ service because I want to know where I connect to.

In case of switchover, I also create the Read Write service in for the standby:


srvctl   add service -db cdb2b -service pdb1_rw -pdb pdb1 -role primary

Here are the resources when CDB2A is the primary:


$ crsctl stat resource -t -w "TYPE = ora.service.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb2a.pdb1_ro.svc
      1        OFFLINE OFFLINE                               STABLE
ora.cdb2a.pdb1_rw.svc
      1        ONLINE  ONLINE       vm106                    STABLE
ora.cdb2b.pdb1_ro.svc
      1        ONLINE  ONLINE       vm106                    STABLE
ora.cdb2b.pdb1_rw.svc
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------

I test as switchover to CDB2B:


$ dgmgrl sys/oracle
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Sep 15 23:41:26 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "CDB2B"
Connected as SYSDG.
DGMGRL> switchover to cdb2b;
Performing switchover NOW, please wait...
New primary database "cdb2b" is opening...
Oracle Clusterware is restarting database "cdb2a" ...
Switchover succeeded, new primary is "cdb2b"

Here are the services:


[[email protected] blogs]$ crsctl stat resource -t -w "TYPE = ora.service.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb2a.pdb1_ro.svc
      1        ONLINE  ONLINE       vm106                    STABLE
ora.cdb2a.pdb1_rw.svc
      1        OFFLINE OFFLINE                               STABLE
ora.cdb2b.pdb1_ro.svc
      1        OFFLINE OFFLINE                               STABLE
ora.cdb2b.pdb1_rw.svc
      1        ONLINE  ONLINE       vm106                    STABLE
--------------------------------------------------------------------------------

So what?

The recommendations are not new here:

  • Always do the same on the primary and the standby. Create services on both sites, then have started them depending on the role
  • Always use one or several application services rather than the default one, in order to have better control and flexibility on where you connect

In multitenant, because services are mandatory to connect to a container with a local user, all the recommendations about services are even more important than before. If you follow them, you will see that multitenant is not difficult at all.

This case may seem improbable, because you probably don’t put the standby on the same server or cluster as the primary. But you may have several standby databases on the same server. About the service registered from the PDB name, just don’t use it. I’m more concerned by the GUID service name (here 59408d6bed2c1c8ee0536a4ea8c0cfa9) which is also declared by both databases. If you plan to use online PDB relocate in a Data Guard configuration then be careful with that. I’ve not tested it, but it is probably better to keep the standby PDB closed, or at least do not register it on the same listener.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director
Oracle Database OCM 12c certified
AWS Database Specialty certified
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod