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)
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. Post protected by copyright.”