ORACLE 12C TIPS

multi-tenant Architecture

New architecture: many sub-databases (pdb) inside a single super db (cdb)


zie Oracle multitenant white paper



Available in Standard Ed. (one) en Enterprise Ed.

Standard Ed. mag maar 1 PDB hebben

Handige sql

Geef alle available services in de db

select * from v$services

of
lsnrctl service

PDB automatisch starten

Bij default worden pluggable databases niet automatisch gestart door Oracle Restart
select name, open_mode from v$pdbs

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY 
CVS                                    MOUNTED   

handmatig

handmatig starten kan op meerdere manieren
- Via context switch sqlplus / as sysdba
show con_name
alter session set container=cvs
startup

- Via commando in CDB$ROOT sqlplus / as sysdba
alter pluggable database CVS open;   (of alter pluggable database all open;) 

sluiten via
alter pluggable database cvs 1 close immediate;

 automatisch starten

v12.1.01
automatisch starten van een  pluggable database via een trigger

alter pluggable database pdb1 open;

create or replace trigger Sys.After_Startup after startup on database
begin
   execute immediate 'alter pluggable database cvs open';
end After_Startup;
/

v12.1.0.2 en hoger

SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

Open nu de PDB en en doe daarna Save state.

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

De status open wordt nu onthouden

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE
-------------------- -------------------- --------------
PDB1                 cdb1                 OPEN

saved state ongedaan maken met Discard state

ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

EM express aanpassen tbv PDB

By default EM Express is configured for a non-CDB or for a CDB depending on what you selected during a 12C installation

default URL is https://hostname:5500/em

To be able to connect to a PDB using EM Express, we need to configure another port using the DBMS_XDB_CONFIG.SETHTTPSPORT() procedure. To complete this setup, first we have to connect to the PDB and make sure it’s open and then we have to configure the https port for the PDB.

Connect / as sysdba

SQL> show user
USER is "SYS"
SQL> alter session set container = mypdb1
 2 ;

Session altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
MYPDB1 MOUNTED

SQL> alter pluggable database open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
MYPDB1 READ WRITE
SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
SQL> exec dbms_xdb_config.sethttpsport(5501);

PL/SQL procedure successfully completed.

JDBC en services

JDBC kan inloggen dmv SID in de string. Helaas is de pluggable database niet als SID maar als Service beschikbaar. Om niets te hoeven veranderen aan jdbc strings en een service als sid te benaderen kan je het volgende doen

Vi /u01/app/oracle/product/12.1.0/db1201/network/admin/listener.ora

Voeg toe   : USE_SID_AS_SERVICE_listener=on

Zie http://www.oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1.php