Skip to Main Content
  • Questions
  • Pluggable Database not open automatically

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mohammad.

Asked: September 02, 2016 - 8:27 am UTC

Last updated: April 23, 2020 - 8:04 am UTC

Version: 12.1.0.2

Viewed 50K+ times! This question is

You Asked

Some Days before I have Install Oracle Database New version 12.1.0.2 with no error or warnings.
Created 2 pluggable databases in the DB container.

SELECT name, open_mode from v$pdbs; 
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        READ WRITE
SPL2                           READ WRITE

Restart the computer
SELECT name, open_mode from v$pdbs; 
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        MOUNT
SPL2                           MOUNT

alter pluggable database all open;

SELECT name, open_mode from v$pdbs; 
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        READ WRITE
SPL2                           READ WRITE

Again restart the computer
SELECT name, open_mode from v$pdbs; 
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        MOUNT
SPL2                           MOUNT


My question is.
1. Is it some kind of default functionality of database that I have to Open it manually every time after restarting the computer.
2. Is there any error in my installation/configuration.

I have googled regarding this and found common problem. Almost every one give the solution to put a Database Trigger.

CREATE or REPLACE trigger OPEN_ALL_PLUGGABLES 
   after startup 
   on  database 
BEGIN 
   execute immediate 'alter pluggable database all open'; 
END open_all_pdbs;


Do i have to use this trigger or there is any other solution available which oracle recommends.

Thanks

and we said...

You can save the state of a PDB once it's open. To do this, issue a:

alter pluggable database pdb_name save state;


Oracle will then preserve the mode when you restart the CDB. You can find the current saved state by querying dba_pdb_saved_states.

If you have several and want them all to open on restart, you can do:

alter pluggable database all save state;


You can use this to keep the state of all but a few with the except clause:

alter pluggable database all except pdb_name1, pdb_name2 save state;


Note saving state is only available from 12.1.0.2. If you're on 12.1.0.1 then the trigger is the way to go.

Further reading:

http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN14251
https://oracle-base.com/articles/12c/multitenant-startup-and-shutdown-cdb-and-pdb-12cr1#pdb-automatic-startup

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Mohammad Kashif, September 02, 2016 - 9:57 am UTC

This worked for me.
Thanks a lot for quick response.

what about active dataguard?

yannS, January 24, 2017 - 3:52 pm UTC

Hola!
I try to install an active dataguard on oracle 12.1.0.2. When I start database on read only mode, the pdb is only mounted.

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED     READ ONLY  NO
  3 PESC01     MOUNTED    nul


I can't save the state, cause I open pdb in read only mode.

SQL> alter pluggable database pesc01 open read only;

Base de donnees pluggable modifiee.

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED     READ ONLY  NO
  3 PESC01     READ ONLY  NO

SQL> alter pluggable database pesc01 save state
  2  /
alter pluggable database pesc01 save state
*
ERREUR a la ligne 1 :
ORA-16000: base de donnees ou base de donnees pluggable ouverte pour acces en
lecture seule


Is there any solution to open pbd in read only mode on active dataguard?
Connor McDonald
January 25, 2017 - 1:49 am UTC

Have you tried a save state command on the *primary* ?

RAC database

Arun Gupta, June 08, 2017 - 5:54 pm UTC

I noticed that for RAC database (12.2), I have to save the state of PDB for each instance separately. Is that correct? If yes, then is there an easier way so I don't have to connect to each instance?
Thanks,
Arun

Connor McDonald
June 09, 2017 - 2:31 am UTC

Have you tried the "instances" clause.


From the docs:

For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB's open mode is preserved in the following ways:

List one or more instances in the instances clause in the following form:

INSTANCES = ('instance_name' [,'instance_name'] … )
Specify ALL in the instances clause to modify the PDB in all running instances, as in the following example:

INSTANCES = ALL
Specify ALL EXCEPT in the instances clause to modify the PDB in all of the instances, except for the instances listed, in the following form:

INSTANCES = ALL EXCEPT('instance_name' [,'instance_name'] … )

Shutdown

Harry, November 28, 2018 - 8:57 am UTC

I refere following link.
https://docs.oracle.com/database/121/SQLRF/statements_2008.htm#SQLRF55667

But I have question how to shutdown specific pluggable database?
Chris Saxon
November 28, 2018 - 2:04 pm UTC

alter pluggable database pdb_to_close close


You need to be connected to the container database to do this.

ez!

A reader, January 20, 2019 - 1:13 pm UTC

This is what i looking for.
ez 2 use & so simple.
Connor McDonald
January 21, 2019 - 1:04 am UTC

glad we could help

thanks

junaid.mcsd, April 02, 2019 - 5:04 pm UTC

it's working

Work for Oracle 19c

Luis, December 10, 2019 - 7:18 am UTC

It works like a charm thanks a lot.

SQL - 12 c error in identifying control file-ORA-00205

Mak, April 22, 2020 - 9:05 pm UTC

Hi there!

I have encountered this problem on Oracle 12 c after installation. I can connect using sysdba and password on sqlplus and sql developer but it shows error in alert log file as such:

Errors in file C:\app\Me\virtual\product\12.2.0\dbhome_1\log\diag\rdbms\orcl\orcl\trace\orcl_m000_8724.trc:
ORA-00202: control file: 'C:\APP\ME\VIRTUAL\ORADATA\ORCL\CONTROL02.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'C:\APP\ME\VIRTUAL\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
Chris Saxon
April 23, 2020 - 8:04 am UTC

Uh-oh! There's a problem reading your control file!

Check the file exists and the OS permissions are correct. If it's missing you'll need to restore it from a backup or re-recreate it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library