Monday, July 15, 2013

Multitenant - How to change PDB$SEED mode as read write or mount?

How to open PDB$SEED as read write mode? That's question, but why you have to do right that? You might want to fix something in PDB$SEED. I recommend you should learn about "catcon.pl" script.
[oracle@test12c admin]$ perl catcon.pl

  Usage: catcon  [-u username[/password]] [-U username[/password]]
                 [-d directory] [-l directory]
                 [{-c|-C} container] [-p degree-of-parallelism]
                 [-e] [-s]
                 [-E { ON | errorlogging-table-other-than-SPERRORLOG } ]
                 [-g]
                 -b log-file-name-base
                 --
                 { sqlplus-script [arguments] | --x<SQL-statement> } ...

   Optional:
     -u username (optional /password; otherwise prompts for password)
        used to connect to the database to run user-supplied scripts or
        SQL statements
        defaults to "/ as sysdba"
     -U username (optional /password; otherwise prompts for password)
        used to connect to the database to perform internal tasks
        defaults to "/ as sysdba"
     -d directory containing the file to be run
     -l directory to use for spool log files
     -c container(s) in which to run sqlplus scripts, i.e. skip all
        Containers not named here; for example,
          -c 'PDB1 PDB2',
     -C container(s) in which NOT to run sqlplus scripts, i.e. skip all
        Containers named here; for example,
          -C 'CDB PDB3'

       NOTE: -c and -C are mutually exclusive

     -p expected number of concurrent invocations of this script on a given
        host

       NOTE: this parameter rarely needs to be specified

     -e sets echo on while running sqlplus scripts
     -s output of running every script will be spooled into a file whose name
        will be
          <log-file-name-base>_<script_name_without_extension>_[<container_name_if_any>].<default_extension>
     -E sets errorlogging on; if ON is specified, default error logging table
        will be used, otherwise, specified error logging table (which must
        have been created in every Container) will be used
     -g turns on production of debugging info while running this script

   Mandatory:
     -b base name (e.g. catcon_test) for log and spool file names

     sqlplus-script - sqlplus script to run OR
     SQL-statement  - a statement to execute

   NOTES:
     - if --x<SQL-statement> is the first non-option string, it needs to be
       preceeded with -- to avoid confusing module parsing options into
       assuming that '-' is an option which that module is not expecting and
       about which it will complain
     - command line parameters to SQL scripts can be introduced using --p
       interactive (or secret) parameters to SQL scripts can be introduced
       using --P

     For example,
       perl catcon.pl ... x.sql '--pJohn' '--PEnter Password for John:' ...
 You might ask... why I had to post. when I recommend to use"catcon.pl". Ok! just play to change PDB$SEED mode.
SQL> select cdb from v$database;

CDB
---
YES

SQL> conn / as sysdba
Connected.
SQL> alter session set container=PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY
SQL>  alter pluggable database pdb$seed close;
 alter pluggable database pdb$seed close
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL>  select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

Pluggable database altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB$SEED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

 --- Fixed something. Not recommend! ---

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;
Pluggable database altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB$SEED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter session set "_oracle_script"=FALSE;

Session altered.
Easy! It's all about "_oracle_script" parameter.

No comments: