Monday, February 9, 2015

Seed Database (pdb$seed) - Read Write OR Read Only Mode in Oracle Database 12c (12.1.0.2.0)


1. Check the instance and check the pluggable databases. 

[oracle@Server ~]$ ps -ef | grep pmon
oracle    3268     1  0 07:40 ?        00:00:00 ora_pmon_cdb1
oracle    4235  4205  0 08:00 pts/1    00:00:00 grep pmon

[oracle@Server ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1
The Oracle base has been set to /u01/app/oracle

[oracle@Server ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 10 08:00:34 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> select open_mode from v$database;

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

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                       OPEN_MODE
---------- ------------------------------ -----------------
         2 PDB$SEED                       READ ONLY
         3 PDB_AUDIT                     MOUNTED

2. Perform close instance operation in seed database (pdb$seed)

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

3. Set the parameter "_oracle_script" and close the seed database (pdb$seed)

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
--------------------
READ WRITE

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                       OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       MOUNTED
         3 PDB_AUDIT                     MOUNTED

4. Open the seed database in Read-Write Mode

SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                        OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ WRITE
         3 PDB_AUDIT                     MOUNTED

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> show con_name

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

SQL> show con_id

CON_ID
------------------------------
2

SQL> select open_mode from v$database;

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

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP

SQL> create tablespace example;
Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
EXAMPLE

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------           -----------------  ------------------
         2 PDB$SEED                                 READ WRITE NO

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------------------
1

SQL> create pluggable database cdb1_pdb1 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ -----------------  -------------------
         2 PDB$SEED                       READ WRITE NO
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED

SQL> create pluggable database cdb1_pdb2 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE   RESTRICTED
---------- ------------------------------ ------------------- ------------------
         2 PDB$SEED                       READ WRITE  NO
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED
         5 CDB1_PDB2                     MOUNTED

SQL> alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed close immediate instances=all
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed close immediate instances=all
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
*
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> show con_name

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

SQL> show con_id

CON_ID
------------------------------
2

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho con_id

CON_ID
------------------------------
1

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ -----------------  -------------------
         2 PDB$SEED                       MOUNTED
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED
         5 CDB1_PDB2                     MOUNTED

SQL> create pluggable database cdb1_pdb3 admin user user1 identified by oracle;
create pluggable database cdb1_pdb3 admin user user1 identified by oracle
*
ERROR at line 1:
ORA-65036: pluggable database PDB$SEED not open in required mode

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> show con_name

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

SQL> show con_id

CON_ID
------------------------------
2

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
*
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 open read only;
Pluggable database altered.

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------  ------------------ ----------
         2 PDB$SEED                        READ ONLY  NO
         3 PDB_AUDIT                      MOUNTED
         4 CDB1_PDB1                      MOUNTED
         5 CDB1_PDB2                      MOUNTED

SQL> create pluggable database cdb1_pdb3 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ------------------  ----------
         2 PDB$SEED                        READ ONLY   NO
         3 PDB_AUDIT                      MOUNTED
         4 CDB1_PDB1                      MOUNTED
         5 CDB1_PDB2                      MOUNTED
         6 CDB1_PDB3                      MOUNTED

2 comments:

  1. I accidentally viewed your blog and I was so amazed with your work that it touched the deepness of my heart and it made me sentimental. Thanks for posting. Visit my site too.

    n8fan.net

    www.n8fan.net

    ReplyDelete
  2. Many many thanks, really appreciated this blog, I have no word to describe

    ReplyDelete