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.