DB_UNIQUE_NAME, PDB, and Data Guard

When you change the parameters for the database is possible to specify the db_unique_name and allow more control where you want to apply/use it. This is very useful to limit the scope, but you need to be aware of some collateral effects. Even not present at the official doc, you can use it. But check here some details that you need to take care of.

Unplug and Plug

The environment below is a simple 19c single instance database. There, exists 3 PDB’s and you can see the db_unique_name for this CDB:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBDG                          READ WRITE NO
         5 PDBMOVE                        READ WRITE NO
SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      or19dg
SQL>

And we can connect in the PDBMOVE and change the parameters. First, as usual, normal set without any special parameter:

SQL> alter system set open_cursors = 300 scope = spfile;

System altered.

SQL>

But, we can specify the db_unique_name for an alter system. ALTER SYSTEM… DB_UNIQUE_NAME = ‘VALUE’. Here you can see that I used the same that as defined for the root level.

SQL> alter system set sessions = 100  scope = spfile db_unique_name = 'or19dg';

System altered.

SQL> alter system set cursor_sharing = FORCE scope = spfile db_unique_name = 'or19dg';

System altered.

SQL>

Detail #1 here, if you specify the DB_UNIQUE_NAME the scope can be JUST spfile. Otherwise, you will receive an error.

SQL> alter system set cursor_sharing = FORCE scope = both db_unique_name = 'or19dg';
alter system set cursor_sharing = FORCE scope = both db_unique_name = 'or19dg'
*
ERROR at line 1:
ORA-65147: DB_UNIQUE_NAME specified without SPFILE scope


SQL>

In this example, I set two parameters for PDB level and you can see at PDB_SPFILE$ inside of root cdb that values was set:

SQL> alter session set container = cdb$root;

Session altered.

SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ where PDB_UID = 2549618825;

DB_UNIQ_NAME                   NAME                           VALUE$                                      PDB_UID
------------------------------ ------------------------------ ---------------------------------------- ----------
*                              open_cursors                   300                                      2549618825
or19dg                         cursor_sharing                 'FORCE'                                  2549618825
or19dg                         sessions                       100                                      2549618825

SQL>

And after the reload of the database the values are set:

[oracle@orcl19p ~]$ srvctl stop database -d or19dg
[oracle@orcl19p ~]$ srvctl start database -d or19dg
[oracle@orcl19p ~]$ 
[oracle@orcl19p ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 1 17:33:29 2020
Version 19.5.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBDG                          READ WRITE NO
         5 PDBMOVE                        READ WRITE NO
SQL> alter session set container = PDBMOVE;

Session altered.

SQL> show parameter cursor_sharing;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           FORCE
SQL>

UNPLUG

But if I need to unplug the PDB, the values will be exported too, right? Yes, but no as you imagine.

So, doing a simple unplug to XML:

SQL> alter pluggable database PDBMOVE close immediate;

Pluggable database altered.

SQL> alter pluggable database PDBMOVE unplug into '/tmp/pdbmove_par.xml';

Pluggable database altered.

SQL> drop pluggable database PDBMOVE keep datafiles;

Pluggable database dropped.

SQL>

And checking the file for these parameters at generated XML:

[oracle@orcl19p ~]$ cat /tmp/pdbmove_par.xml |grep open_cursors
      <spfile>*.open_cursors=300#HWM:300,</spfile>
[oracle@orcl19p ~]$ cat /tmp/pdbmove_par.xml |grep cursor_sharing
      <spfile>*.cursor_sharing='FORCE'</spfile>
[oracle@orcl19p ~]$ cat /tmp/pdbmove_par.xml |grep sessions
      <spfile>*.sessions=100#HWM:100,flag:1,</spfile>
[oracle@orcl19p ~]$

As you can see here, the parameters were exported, but the parameter changed to “*”. The definition of specific db_unique_name was cleared.

And if we try to plug again the same PDB, we can see that parameter was loaded as “*”:

SQL> create  pluggable database PDBMOVE USING '/tmp/pdbmove_par.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> col name format a30
SQL> col VALUE$ format a40
SQL> set linesize 250
SQL> select CON_ID,DBID,CON_UID,GUID,NAME from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME
---------- ---------- ---------- -------------------------------- ------------------------------
         2  826464235  826464235 9B151A78B6DB533AE0533205A00AFC30 PDB$SEED
         3 1340021208 1340021208 9B155010BEAC661BE0533205A00AF21B PDBDG
         4 2549618825 3364812106 A7082B6C610C5E1DE0533205A00AF7FE PDBMOVE

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBDG                          READ WRITE NO
         4 PDBMOVE                        MOUNTED
SQL> alter pluggable database PDBMOVE open;

Pluggable database altered.

SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ where PDB_UID = 3364812106;

DB_UNIQ_NAME                   NAME                           VALUE$                                      PDB_UID
------------------------------ ------------------------------ ---------------------------------------- ----------
*                              cursor_sharing                 'FORCE'                                  3364812106
*                              open_cursors                   300                                      3364812106
*                              sessions                       100                                      3364812106

SQL>

DATAGUARD

As you can imagine, using the db_unique_name for an alter system can affect the dataguard environments. If you know, the db_unique_name is different between primary and standby. So, if you define some parameter, depending on the way that you made, after you, switchover/failover can occur that parameter returns to the default value.

It is not the case that this is wrong, maybe you need to set some parameters for one server/side in specific. Maybe because of less memory, less CPU power, or whatever the reason.

In this example, I have gold19c as primary, and golds19c as standby. And I have the GOLD19P as used PDB here:

[oracle@goldpn1 ~]$ dgmgrl sys/oracle@gold19c
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 1 19:13:39 2020
Version 19.7.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "GOLD19C"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - gold19c

  Protection Mode: MaxAvailability
  Members:
  gold19c  - Primary database
    golds19c - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 72 seconds ago)

DGMGRL> exit
[oracle@goldpn1 ~]$
[oracle@goldpn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 1 19:16:50 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      gold19c
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      GOLD19C
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GOLD19P                        READ WRITE NO
SQL> col name format a30
SQL> col VALUE$ format a40
SQL> set linesize 250
SQL> select CON_ID,DBID,CON_UID,GUID,NAME from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME
---------- ---------- ---------- -------------------------------- ------------------------------
         2 2661745537 2661745537 A6B13C68753F63D3E0530A07A00A6303 PDB$SEED
         3 2141275600 2141275600 A6B19FBF38306073E0530C07A00ACE20 GOLD19P

SQL> 

And to set the parameters I used the same than before, with and without db_unique_name:

SQL> alter session set container = GOLD19P;

Session altered.

SQL> alter system set open_cursors = 300 scope = spfile;

System altered.

SQL> alter system set cursor_sharing = FORCE scope = spfile db_unique_name = 'GOLD19C';

System altered.

SQL>

And as you can see, the parameter was stored.

SQL> alter session set container = cdb$root;

Session altered.

SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ where PDB_UID = 2141275600;

DB_UNIQ_NAME                   NAME                           VALUE$                                      PDB_UID
------------------------------ ------------------------------ ---------------------------------------- ----------
*                              open_cursors                   300                                      2141275600
GOLD19C                        cursor_sharing                 'FORCE'                                  2141275600

SQL>

But if I made the switchover:

[oracle@goldpn1 ~]$ dgmgrl sys/oracle@gold19c
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 1 19:24:15 2020
Version 19.7.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "GOLD19C"
Connected as SYSDBA.
DGMGRL> switchover to golds19c;
Performing switchover NOW, please wait...
Operation requires a connection to database "golds19c"
Connecting ...
Connected to "GOLDS19C"
Connected as SYSDBA.
New primary database "golds19c" is opening...
Oracle Clusterware is restarting database "gold19c" ...
Connected to "GOLD19C"
Connected to "GOLD19C"
Switchover succeeded, new primary is "golds19c"
DGMGRL>

You can see that now, the value for the parameter cursor_sharing now have the default value because the db_unique_name does not hit what was defined:

[oracle@goldsn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 1 19:35:01 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      gold19c
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      GOLDS19C
SQL>
SQL> alter session set container = GOLD19P;

Session altered.

SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
SQL>

But you can see that the parameter is defined at PDB_SPFILE$:

SQL> alter session set container = cdb$root;

Session altered.

SQL>
SQL> col name format a30
SQL> col VALUE$ format a40
SQL> set linesize 250
SQL> select CON_ID,DBID,CON_UID,GUID,NAME from v$pdbs;

    CON_ID       DBID    CON_UID GUID                             NAME
---------- ---------- ---------- -------------------------------- ------------------------------
         2 2661745537 2661745537 A6B13C68753F63D3E0530A07A00A6303 PDB$SEED
         3 2141275600 2141275600 A6B19FBF38306073E0530C07A00ACE20 GOLD19P

SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ where PDB_UID = 2141275600;

DB_UNIQ_NAME                   NAME                           VALUE$                                      PDB_UID
------------------------------ ------------------------------ ---------------------------------------- ----------
*                              open_cursors                   300                                      2141275600
GOLD19C                        cursor_sharing                 'FORCE'                                  2141275600

SQL>

As showed, the value at standby is not defied, but as told before this can be expected behavior. Or no. If you want to set the parameter and it became a value in both, primary and standby, you don’t need to specify the db_unique_name, otherwise when the switchover/failover occurs the value will not be there. And, this can lead to some unexpected behaviors (at application side as an example) and need to troubleshoot (and until finding this about set parameter, can be a long path).

Others points

The db_unique_name option does not check what you specify, so, take care to set the correct value. As you can see below I defined it as SIMON, and was accepted and saved:

SQL> alter session set container = GOLD19P;

Session altered.

SQL> alter system set cursor_sharing = FORCE scope = spfile db_unique_name = 'SIMON';

System altered.

SQL> alter session set container = cdb$root;

Session altered.

SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ where PDB_UID = 2141275600;

DB_UNIQ_NAME                   NAME                           VALUE$                                      PDB_UID
------------------------------ ------------------------------ ---------------------------------------- ----------
*                              open_cursors                   300                                      2141275600
GOLD19C                        cursor_sharing                 'FORCE'                                  2141275600
SIMON                          cursor_sharing                 'FORCE'                                  2141275600

SQL>

And to reset, the process is similar. As you see below, you can specify the db_unique_name to delete specific. But if you specify nothing, the reset will be done for all.

SQL> alter system reset cursor_sharing scope = spfile db_unique_name = 'GOLD19C';

System altered.

SQL> alter system reset cursor_sharing scope = spfile db_unique_name = 'GOLD19C';
alter system reset cursor_sharing scope = spfile db_unique_name = 'GOLD19C'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE


SQL> alter system set cursor_sharing = FORCE scope = spfile db_unique_name = 'GOLD19C';

System altered.

SQL> alter system reset cursor_sharing scope = spfile;

System altered.

SQL> alter system reset cursor_sharing scope = spfile db_unique_name = 'GOLD19C';
alter system reset cursor_sharing scope = spfile db_unique_name = 'GOLD19C'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE


SQL>

Conclusion

Sometimes a simple definition can lead to some strange behaviors. Think that your application is running ok and after a switchover change completely the way how it works (cursor_sharing as above). And until you dig the solution can take time.

Db_unique_name for set parameter needs to be used carefully to avoid these cases. It is not the case that you never need to use it, sometimes this definition needs to be used. You can use it to prepare everything BEFORE the switchover to avoid some error or to tune the database since the beginning.

References

Initialization parameters in a Multitenant database – FAQ and Examples (Doc ID 2101638.1)

Initialization parameters in a Multitenant database – Facts and additional information (Doc ID 2101596.1)

2.7 Managing Initialization Parameters Using a Server Parameter File

 

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.”

Leave a Reply

Your email address will not be published. Required fields are marked *