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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mohammad.

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

Last updated: July 07, 2022 - 4:32 am UTC

Version: 12.1.0.2

Viewed 100K+ 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 Chris 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

  (11 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.

Phil J

Phil J, April 26, 2022 - 4:49 pm UTC

Can we auto-open PDBs in a preferred order, e.g. after instance startup auto-open PDB2 before PDB1 (maybe needed due to an apps dependency)?

Of course we can do this using a 12.1.0.1 after startup trigger, just open them one at a time in the preferred sequence. But can we do it using some flavour of 12.1.0.2 SAVE STATE ? My understanding is that Oracle tries to open them all at the same time via parallelism.

What I'm looking for is to get the same effect as:

ALTER PLUGGABLE DATABASE PDB2 SAVE STATE BEFORE PDB1;
  ALTER PLUGGABLE DATABASE PDB1 SAVE STATE AFTER PDB2;


(ok not using that syntax because it doesn't exist, but I hope you can see what I'm looking for).

Thanks, Phil
Connor McDonald
April 27, 2022 - 3:17 am UTC

Correct - there is not a declarative way of doing this.

You'll need a script that either does the open in the sequence you want, or similar using srvctl if you're using grid etc.

Note - there are plans to set PDB priority coming in a future release or patchset. I don't have any more timing details than that

Siddhartha, May 14, 2022 - 7:30 am UTC

i tried to open pdb using the following command and got the error(I'm using db 19.3)

SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL
Connor McDonald
May 16, 2022 - 2:13 am UTC

Yeah that doesn't look good :-)

Check your alert log - it should contain more info about what happened, and its probably created an incident trace as well.

It might be this:

Reference: Alter Pluggable Database Open Returns ORA-01405 (Doc ID 2193378.1)


Open PDB in read only mode automatically after DB or server restart

Fasi Mohammed, June 30, 2022 - 2:17 pm UTC

Is there any solution to open pbd in read only mode on active dataguard ?? It comes up in MOUNTED mode after server or database restart.

SQL> select inst_id,INSTANCE_NAME,host_name,status,DATABASE_STATUS from gv$instance;

INST_ID INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS
---------- --------------- ---------------------- ------------ -----------------
1 CDB_NAME hostname OPEN ACTIVE

SQL> select inst_id,NAME,DATABASE_ROLE,OPEN_MODE,DB_UNIQUE_NAME from gv$database;

INST_ID NAME DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME
---------- --------------- ---------------- -------------------- ------------------------------
1 CDB_NAME PHYSICAL STANDBY READ ONLY WITH APPLY XXXXXX

SQL> select inst_id,con_id,NAME,OPEN_MODE from gv$pdbs where name not like '%SEED%';

INST_ID CON_ID NAME OPEN_MODE
---------- ---------- --------------- ----------
1 3 PDB_NAME MOUNTED

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME INSTANCE_NAME STATE
--------------- --------------- --------------
PDB_NAME CDB_NAME OPEN
Connor McDonald
July 07, 2022 - 4:32 am UTC

We look at the CDB *future* state and try to match the PDBs.

So if your CDB is defined to open as mounted as default (even if you then later come along and open it yourself), then at restart time, the PDBs are also going to default to open as mounted (not open).

So double check that (eg srvctl config database -d XXX) and if you still are observing that discrepancy, then its time to log a call with Support

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