By Franck Pachot

.
If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode.

  • Can you open PDB$SEED read write yourseld? Yes and No.
  • Should you open PDB$SEED read write yourself? Yes and No.
  • How to run upgrade scripts that need to write to PDB$SEED? catcon.pl


In 12.1 you have no reason to open the seed read write yourself. In 12.2 there is one reason when you are in LOCAL UNDO mode, because you may want to customize the UNDO tablespace.

12c in local undo

I am in 12.1 or in 12.2 in shared undo mode:

SYS@CDB$ROOT SQL> select * from database_properties where property_name like '%UNDO%';
 
no rows selected

When the CDB is opened, the PDB$SEED is opened in read only mode.

SYS@CDB$ROOT SQL> show pdbs
&nsbp;
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----   ----------
2       PDB$SEED  READ ONLY   NO
3       PDB01     READ WRITE  NO

I try to open the PDB$SEED in read write mode (FORCE is a shortcut to avoid to close it before)

SYS@CDB$ROOT SQL> alter pluggable database pdb$seed open force;
&nsbp;
Error starting at line : 1 in command -
alter pluggable database pdb$seed open force
Error report -
ORA-65017: seed pluggable database may not be dropped or altered
65017. 00000 -  "seed pluggable database may not be dropped or altered"
*Cause:    User attempted to drop or alter the Seed pluggable database which is not allowed.
*Action:   Specify a legal pluggable database name.
SYS@CDB$ROOT SQL>

Obviously, this is impossible and clearly documented. PDB$SEED is not a legal pluggable database for this operation.

Oracle Script

There is an exception to that: internal Oracle scripts need to run statements in the PDB$SEED. They run with “_oracle_script”=true where this operation is possible:

SYS@CDB$ROOT SQL> alter session set "_oracle_script"=true;
Session altered.
 
SYS@CDB$ROOT SQL> alter pluggable database pdb$seed open read write force;
Pluggable database PDB$SEED altered.

catcon.pl

Of course, when upgrading, there are phases where you need the seed opened read-write. But you don’t to that yourself. The scripts to run in each container are called through catcon.pl which, by default, opens the seed read-write and ensures that the initial open mode is restored at the end even in case of error.


   -m mode in which PDB$SEED should be opened; one of the following values
        may be specified:
        - UNCHANGED - leave PDB$SEED in whatever mode it is already open
        - READ WRITE (default)
        - READ ONLY
        - UPGRADE
        - DOWNGRADE

I have the following “/tmp/show_open_mode.sql” script


column name format a10
select name,open_mode,current_timestamp-open_time from v$containers;

I call it with catcon to run in PDB$SEED:


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -c 'PDB$SEED' -n 1 -d /tmp -l /tmp -b tmp -show_open_mode.sql

Here is the output in /tmp/tmp0.log


CATCON_STATEMENT
--------------------------------------
catconExec(): @/tmp/show_open_mode.sql
SQL> SQL> column name format a10
SQL> select name,open_mode,current_timestamp-open_time from v$containers;
NAME       OPEN_MODE  CURRENT_TIMESTAMP-OPEN_TIME
---------- ---------- ---------------------------------------------------------------------------
PDB$SEED   READ WRITE +000000000 00:00:00.471398
SQL>
END_RUNNING
------------------------------------------------------------------------------------------------------------------------
==== @/tmp/show_open_mode.sql Container:PDB$SEED Id:2 17-05-07 05:02:06 Proc:0 ====
SQL>
END_RUNNING
------------------------------------------------------------------------------------------------------------------------
==== @/tmp/show_open_mode.sql Container:PDB$SEED Id:2 17-05-07 05:02:06 Proc:0 ====

The PDB$SEED was opened READ WRITE to run the statements.

We can see that in alert.log:


alter pluggable database pdb$seed close immediate instances=all
ALTER SYSTEM: Flushing buffer cache inst=0 container=2 local
Pluggable database PDB$SEED closed
Completed: alter pluggable database pdb$seed close immediate instances=all
alter pluggable database pdb$seed OPEN READ WRITE
Database Characterset for PDB$SEED is WE8MSWIN1252
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Pluggable database PDB$SEED opened read write
Completed: alter pluggable database pdb$seed OPEN READ WRITE
alter pluggable database pdb$seed close immediate instances=all
ALTER SYSTEM: Flushing buffer cache inst=0 container=2 local
Pluggable database PDB$SEED closed
Completed: alter pluggable database pdb$seed close immediate instances=all
alter pluggable database pdb$seed OPEN READ ONLY instances=all
Database Characterset for PDB$SEED is WE8MSWIN1252
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Pluggable database PDB$SEED opened read only
Completed: alter pluggable database pdb$seed OPEN READ ONLY instances=all

When the pre-upgrade and post-upgrade scripts are run from DBUA you can see the following in the logs:

exec_DB_script: opened Reader and Writer
exec_DB_script: executed connect / AS SYSDBA
exec_DB_script: executed alter session set "_oracle_script"=TRUE
/
exec_DB_script: executed alter pluggable database pdb$seed close immediate instances=all
/
exec_DB_script: executed alter pluggable database pdb$seed OPEN READ WRITE
/

This is displayed because DBUA runs catcon.pl in debug mode and you can do the same by adding ‘-g’ to the catcon.pl arguments.

12cR2 in local undo

In 12.2 there is a case where you can make a change to the PDB$SEED to customize the UNDO tablespace template. Here I am changing to LOCAL UNDO:


SYS@CDB$ROOT SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
&nsbp;
SYS@CDB$ROOT SQL> startup upgrade;
ORACLE instance started.
&nsbp;
Total System Global Area   1107296256 bytes
Fixed Size                    8791864 bytes
Variable Size               939526344 bytes
Database Buffers            150994944 bytes
Redo Buffers                  7983104 bytes
Database mounted.
Database opened.
&nsbp;
SYS@CDB$ROOT SQL> alter database local undo on;
Database altered.
&nsbp;
SYS@CDB$ROOT SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SYS@CDB$ROOT SQL> select * from database_properties where property_name like '%UNDO%';
 
PROPERTY_NAME       PROPERTY_VALUE  DESCRIPTION
-------------       --------------  -----------
LOCAL_UNDO_ENABLED  TRUE            true if local undo is enabled

PDB$SEED is read only:


SYS@CDB$ROOT SQL> show pdbs
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----   ----------
2       PDB$SEED  READ ONLY   NO
3       PDB01     READ WRITE  NO

and _oracle_script is not set:


SYS@CDB$ROOT SQL> show parameter script
 
NAME TYPE VALUE
---- ---- -----
 

I get no error now and can open the seed in read-write mode:


SYS@CDB$ROOT SQL> alter pluggable database PDB$SEED open force;
Pluggable database PDB$SEED altered.
 
SYS@CDB$ROOT SQL> show pdbs
 
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----   ----------
2       PDB$SEED  READ WRITE  NO
3       PDB01     READ WRITE  NO

Customize UNDO seed

Once you open read write an undo tablespace is created. If you want to customize it, you can create another one and drop the previous one. This requires changing the undo_tablespace parameter:


SYS@CDB$ROOT SQL> show parameter undo
NAME              TYPE    VALUE
----------------- ------- ------
undo_tablespace   string  UNDO_1
 
SYS@CDB$ROOT SQL> create undo tablespace UNDO;
Tablespace UNDO created.
 
SYS@CDB$ROOT SQL> alter system set undo_tablespace=UNDO;
System SET altered.
 
SYS@CDB$ROOT SQL> drop tablespace UNDO_1 including contents and datafiles;
Tablespace UNDO_1 dropped.
 
SYS@CDB$ROOT SQL> shutdown immediate
Pluggable Database closed

You can leave it like this, just close and re-open read only. If you want to keep the same undo tablespace name as before, you need to play with create and drop, and change undo_tablespace again.

So what?

Don’t forget that you should not modify or drop PDB$SEED. If you want a customized template for your PDB creations, then you should create your PDB template to clone. You can clone remotely, so this is possible in single-tenant as well. Being able to open the PDB$SEED in read write is possible only for the exception of creating the UNDO tablespace in PDB$SEED when you move to local undo mode. This is not required, and then an UNDO tablespace will be created when you open a PDB with no undo_tablespace.
When running pre-upgrade and post-upgrade scripts, then don’t worry: catcon.pl is there to help run scripts in containers and handles that for you.

Post Views: 4,649
Thumbnail [60x60]
by
Oracle Team