Database 12c: Pluggable database autostart like HAS


Today I discussed with a college the problem of a simple autostart trigger like I posted here.

After some brainstorming I’vh written a new example that acts like oracle HAS (High Availability Service). The pluggable database should have the same state as after a reboot sequence.

Here the coding example:

First create a table in the CDB:

create table pdbs_autostart (
 con_id      number,
 dbid        number,
 name        varchar2(30),
 open_mode   varchar2(10),
 restricted  varchar2(3),
 locked      varchar2(3),
 error       varchar2(200)
);

Further create a startup and shutdown trigger:
Startup trigger:

CREATE OR REPLACE TRIGGER start_pdbs
   AFTER STARTUP ON DATABASE
DECLARE
  opencmd varchar2(100);
  v_errm  varchar2(200);
BEGIN
  for db in (select * from pdbs_autostart) loop
    if db.locked != 'YES' and db.name !='PDB$SEED' and db.open_mode != 'MOUNTED' then
      opencmd := 'alter pluggable database '||db.name||' OPEN '||db.open_mode;
      if db.restricted = 'YES' then
        opencmd := opencmd||' RESTRICTED';
      end if;

      BEGIN
        execute immediate opencmd;
        update pdbs_autostart set error='NO ERROR' where name = db.name;
      EXCEPTION WHEN OTHERS THEN
        v_errm := SUBSTR(SQLERRM, 1 , 200);
        update pdbs_autostart set error=v_errm where name = db.name;
      END;
    else
      update pdbs_autostart set error='Skipped' where name = db.name;
    end if;
  end loop;
  commit;
END;
/

Shutdown tigger:

CREATE OR REPLACE TRIGGER shutdown_pdbs
  BEFORE SHUTDOWN ON DATABASE
BEGIN 
  merge into  pdbs_autostart a
  using       v$pdbs         b
  on          (a.dbid = b.dbid)
  when matched then
    update set a.con_id = b.con_id,a.name = b.name,a.open_mode=b.open_mode,a.restricted=nvl(b.restricted,'NO'),error='' where a.locked = 'NO'
    delete where b.dbid not in (select dbid from v$pdbs )
  when not matched then
    insert values (b.con_id,b.dbid,b.name,b.open_mode,b.restricted,'NO','')
  ;  
  commit;
END; 
/

At every shutdown the state of the database will be written into the table pdbs_autostart. After startup of database the trigger starts all database as saved in table.

I’vh also added an LOCKED column to prevent state change at shutdown. This is useful if you want to permanent save your configuration. Alternativ you can disable the shutdown trigger.

If an error occurs during startup, it will be written in the error column.

Here the construction in action:

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           MOUNTED
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

Check the actual state, then start PDB3 and PDB5.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> alter pluggable database pdb5 open;

Pluggable database altered.

Check configuration. PDB3 state should be changed at shutdown and should startup at next restart. PDB5 ist LOCKED and should not be overruled at next startup. It will not be started at next time.

SQL> select * from pdbs_autostart;

    CON_ID       DBID NAME                           OPEN_MODE  RES LOCKED ERROR
---------- ---------- ------------------------------ ---------- --- ------ -----------------------------------------------------------------
         3 3328312323 PDB3                           MOUNTED    NO  NO  
         2 4062846046 PDB$SEED                       READ ONLY  NO  NO  
         4 3887199318 PDB2                           READ ONLY  NO  NO  
         7 3881397024 PDB5                           MOUNTED        YES 
         6 3831633646 PDB4                           READ WRITE NO  NO  
         5 3328633065 PDB1                           READ WRITE NO  NO  

6 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1970864128 bytes
Fixed Size                  2289928 bytes
Variable Size             587206392 bytes
Database Buffers         1375731712 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB3                           READ WRITE
PDB2                           READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE
PDB5                           MOUNTED

6 rows selected.

SQL>

It works! Have fun!

One thought on “Database 12c: Pluggable database autostart like HAS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s