iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 12c New Features

Online Oracle Training

LESSON 13

How to create AFTER STARTUP trigger to open all PDBs except the mypdb2_clone pluggable database

 

Login to your mycdb CDB and create AFTER STARTUP trigger.

# cd ~

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

--Just to make sure where you are

Get the container name

SQL> show con_name

 

CON_NAME

MYCDB

 

List or query the V$DATABASE view.

SQL> SELECT name, cdb, con_id FROM v$database;

 

NAME

CDB

CON_ID

MYCDB

YES

0

 

SQL> CREATE TRIGGER open_my_pdbs

   2   AFTER STARTUP ON DATABASE

   3   BEGIN

   4      EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE all EXCEPT mypdb2 OPEN;

   5   END open_my_pdbs;

   6   /

Trigger created.

 

Shutdown the CDB database

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Start up again.

SQL> STARTUP;

 

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

 

CON_ID

NAME

OPEN_MODE

DBID

2

PDB$SEED

READ ONLY

3654327692

3

MYPDB1

READ WRITE

6783241652

4

MYPDB2

MOUNTED

7549320153

5

MYPDB2_CLONE

READ WRITE

8734290212

6

PDB_MYDBS

READ WRITE

2378654330

Notice that the mypdb2 open mode is “MOUNTED.”

 

To open it

SQL> ALTER PLUGGABLE DATABAE mypdb2 OPEN;

 

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

 

CON_ID

NAME

OPEN_MODE

DBID

2

PDB$SEED

READ ONLY

3654327692

3

MYPDB1

READ WRITE

6783241652

4

MYPDB2

READ WRITE

7549320153

5

MYPDB2_CLONE

READ WRITE

8734290212

6

PDB_MYDBS

READ WRITE

2378654330

Notice that all PDBs open mode are “READ WRITE.”

 

You can also change a pluggable database to a restricted mode.

SQL> ALTER PLUGGABLE DATABAE mypdb2 CLOSE IMMEDIATE;

SQL> ALTER PLUGGABLE DATABAE mypdb2 OPEN RESTRICTED;

 

Get the list of all PDBs open mode.

SQL> SELECT con_id, name, open_mode, restricted FROM v$pdbs WHERE name = ‘MYPDB2’;

 

CON_ID

NAME

OPEN_MODE

RESTRICTED

4

MYPDB2

READ WRITE

YES

Notice that the restricted column value is “YES.”

 

Good Luck!

 

 

Google
 
Web web site