This was first published on https://blog.dbi-services.com/can-you-open-pdbseed-read-write (2017-05-07)
Republishing here for new followers. The content is related to the the versions available at the publication date
If you are in multitenant, you probably already felt the desire to open the PDB$SEED in READ WRITE mode.
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.
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 selectedWhen 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 NOI 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.
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.
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.sqlHere 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.
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 NOand _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
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.
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.