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

1 comment:

  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