12C+ Super Instance Multi Tenant

1] Oracle 12C+ CDB Management

Goal	CDB , PDB Mutli-Tenant Management
Version	12.1.0.2
Views	dba_data_files cdb_data_files
Log as	SYS

1.1 Oracle 12c start pdb at instance startup

• Previous solution

alter pluggable database all open;

create or replace trigger Sys.After_Startup after startup on database

 begin

    execute immediate 'alter pluggable database all open';

 end;

 /

• Current

alter pluggable database all save state;

1.2 Oracle 12C Create Users

To create a common user, you must be connected to the root. You can optionally specify CONTAINER = ALL, which is the default when you are connected to the root.

To create a local user, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT, which is the default when you are connected to a PDB.

• Create a COMMON User, that can connect to any PDB, to CHECK CONNECTION TIME

Create the Common User, connected as « / as sysdba » to the root

CREATE USER C##APP_SVC IDENTIFIED BY <psw> DEFAULT TABLESPACE USERS QUOTA 10M ON USERS TEMPORARY TABLESPACE TEMP container=all ;

ORA-65048: error encountered when processing the current DDL statement in pluggable database GAMIQ

ORA-00959: tablespace 'USERS' does not exist

PROXT srv-appbd-1:/data/proxt > sqlplus / as sysdba

CREATE USER APP_SVC IDENTIFIED BY <psw> DEFAULT TABLESPACE USERS QUOTA 10M ON USERS TEMPORARY TABLESPACE TEMP container=current;

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

CREATE USER C##APP_SVC IDENTIFIED BY <psw> DEFAULT TABLESPACE SYSAUX QUOTA 10M ON SYSAUX TEMPORARY TABLESPACE TEMP container=all ;

grant create session to C##APP_SVC ;

grant select_catalog_role to C##APP_SVC ;

Connect to a PDB and get PDB name using SQLPLUS

[ oracle 12C how to get the current pdb with a select ]

$ sqlplus C##APP_SVC/<psw>@GAM_PRO

SQL> sho con_name ;

CON_NAME

------------------------------

GAM

SQL> select name from v$PDBS ;

NAME

------------------------------

GAM

• Create a COMMON User to GET VOLUME information : sum up every datafiles accross PDBs

cdb_data_files return different results depending on the connected user

CDB_ROOT srv-appbdiqr-1:/home/oracle > sqlplus / as sysdba

SQL> CREATE USER C##SCH IDENTIFIED BY tzghwx DEFAULT TABLESPACE SYSAUX QUOTA 10M ON SYSAUX TEMPORARY TABLESPACE TEMP container=all ;

User created.

SQL> grant create session , select_catalog_role to C##SCH ;

Grant succeeded.

$ sqlplus C##SCH/<psw>@CDB_ROOT

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 7 15:34:48 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select ceil(sum(bytes)/1024/1024/1024) total_DBF_GB from cdb_data_files ;

TOTAL_DBF_GB

------------

           4

SQL> -- there, we got only the PDB result -- COMMON USER

SQL> conn / as sysdba

Connected.

SQL> select ceil(sum(bytes)/1024/1024/1024) total_DBF_GB from cdb_data_files ;

TOTAL_DBF_GB

------------

          20

SQL> -- now, we have every PDBs – SYS USER

Let’s give the needed right for the Common User to see every PDBs volume information, still connected as SYS to the root :

SQL> alter user C##SCH set container_data=all for cdb_data_files container = current ;

User altered.

SQL> connect C##SCH/tzghwx@CDB_ROOT

Connected.

SQL> select ceil(sum(bytes)/1024/1024/1024) total_DBF_GB from cdb_data_files ;

TOTAL_DBF_GB

------------

          20

SQL> -- now, we have every PDBs – COMMON USER

1.3 Oracle 12C Navigate through PDBs and Profile

-- Find which user is using a Non-Default Profile ?

select NAME PDB , username , profile from CDB_USERS a , v$containers b where profile != 'DEFAULT' and a.CON_ID=b.CON_ID order by a.con_id ;

-- Get DEFAULT Profile Value in each PDBs

select NAME PDB , profile , resource_name , LIMIT  from cdb_profiles a , v$containers b -- CDB_PDBS b

where a.CON_ID=b.CON_ID and resource_name = 'PASSWORD_LIFE_TIME' and profile = 'DEFAULT' order by a.con_id ;

-- Show USER Account status and Profile in each PDBs

select NAME , username, account_status , expiry_date , a.profile , resource_name

from CDB_USERS a , v$containers b , cdb_profiles c

where -- USERNAME = 'APP_OIM' and

a.CON_ID=b.CON_ID and a.PROFILE=c.PROFILE and a.CON_ID=c.CON_ID

and resource_name = 'PASSWORD_LIFE_TIME'

-- 07/07/2014 backuped db date to be restored by rman using dbca

and trunc(expiry_date,'DAY') != '07/07/2014'

order by a.con_id , expiry_date

-- if needed, reset psw using :

select 'alter user '||NAME||' identified by values '''||PASSWORD||''' ; ' from USER$ where NAME = 'APP_OIM' ;

1.4 Oracle 12C Other Information retrieval through PDB

1.4.1 Count Session per PDB

select NAME PDB , count(NAME) from v$session a , v$containers b

where a.CON_ID=b.CON_ID

group by NAME

order by 1 ;

PDB	Sessions
27/11/2015
CDB$ROOT	3
GAMIQ	4
APPIQ	340
GOSIQ	55
IORIQ	30

1.4.2 Get Tablespace Name per PDB

select TABLESPACE_NAME , NAME PDB from cdb_tablespaces a , v$containers b where a.CON_ID=b.CON_ID order by a.con_id ;

1.5 Oracle 12c CDB Restart encounters ORA-01154

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

dbstop()

{

sqlplus "/ as sysdba" <<FIN

shutdown abort;

startup restrict;

!sleep 5 <= Solution

shutdown immediate;

exit

FIN

}

2] Oracle 12C+ PDB Management

Goal	PDB Mutli-Tenant Management
Version	12.1.0.2
Views	v$containers cdb_tablespaces
Log as	SYS

2.1         How to Move a PDB Back to a Non-CDB Using TRANSPORTABLE=ALWAYS

Oracle Doc ID 2027352.1

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

expdp system/<password>@<pdbname> directory=TC_DIR dumpfile=fullexp.dmp logfile=fullexp.log full=y transportable=always;

select * from cdb_tablespaces

select * from v$containers

select PDB_NAME , tablespace_name , b.status from cdb_tablespaces a , DBA_PDBS b where a.con_id = b.con_id order by 2

select NAME PDB , tablespace_name , status from cdb_tablespaces a , v$containers b where a.con_id = b.con_id order by 1,2

Source - Put the PDB's user-defined tablespace(s) in read-only mode

select 'alter tablespace '||tablespace_name||' read only ; ' from cdb_tablespaces a , v$containers b where a.con_id = b.con_id and NAME = 'GOR' and tablespace_name not in ('SYSAUX','SYSTEM','TEMP','IAS_TEMP') order by 1

sqlplus / as sysdba

SQL> alter session set container=GOR ;

Source - Export

$ expdp system/DB1@GOR_CAL directory=BAT_DP_DIR dumpfile=CDB_db1_fullexp.dmp logfile=db1_fullexp.log full=y transportable=always

ORA-31626: job does not exist

ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT", line 810

ORA-39244: Event to disable dropping null bit image header during relational select

Correct the error :

$ sqlplus / as sysdba

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB_1', force => TRUE, auto_commit => TRUE);

$ expdp system/DB1@GOR_CAL directory=BAT_DP_DIR dumpfile=CDB_db1_fullexp.dmp logfile=db1_fullexp.log full=y transportable=always

...

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

  /DPUMP_TMP/oracle/CDB_db1_fullexp.dmp

******************************************************************************

Datafiles required for transportable tablespace APEX_2232223278371978:

  /data/db1/gor/APEX_2232223278371978.dbf

Datafiles required for transportable tablespace APEX_5760478225708625:

  /data/db1/gor/APEX_5760478225708625.dbf

Datafiles required for transportable tablespace DAT:

  /data/db1/gor/DAT.DBF

Datafiles required for transportable tablespace ODI_USER:

  /data/db1/gor/ODI_USER.dbf

Datafiles required for transportable tablespace REF_W_02_DAT:

  /data/db1/gor/REF_W_02_DAT.dbf

Datafiles required for transportable tablespace SYS:

  /data/db1/gor/sys.dbf

Datafiles required for transportable tablespace APP_DAT:

  /data/db1/gor/app_dat.dbf

Datafiles required for transportable tablespace APP_FILE_DAT:

  /data/db1/gor/app_file_dat.dbf

Datafiles required for transportable tablespace IXT_DAT:

  /data/db1/gor/IXT_DAT.dbf

Datafiles required for transportable tablespace ODI_DAT:

  /data/db1/gor/ODI_DAT

Datafiles required for transportable tablespace RAC_DAT:

  /data/db1/gor/RAC_DAT.dbf

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Jun 18 15:21:44 2018 elapsed 0 00:07:28

Source - Copy (and endian convert, if necessary) the datafiles to the desired location on the target.

cd /data/db1/gor

for i in APEX_2232223278371978.dbf  APEX_5760478225708625.dbf  DAT.DBF  ODI_USER.dbf  REF_W_02_DAT.dbf  sys.dbf  app_dat.dbf  app_file_dat.dbf  IXT_DAT.dbf  ODI_DAT  RAC_DAT.dbf ; do

scp -p $i SR-ORAIQR-1:/data/db1

done

Target - Create the directory pointing to the location of the metadata dumpfile (named TC_DIR in the example).

Target - Import as normal for a regular TTS import, but add the full=y parameter.

impdp system/$psw directory=BAT_DP_DIR dumpfile=CDB_db1_fullexp.dmp logfile=i_CDB_db1_fullexp.log full=y \

transport_datafiles='/data/db1/APEX_2232223278371978.dbf','/data/db1/APEX_5760478225708625.dbf','/data/db1/DAT.DBF','/data/db1/ODI_USER.dbf','/data/db1/REF_W_02_DAT.dbf','/data/db1/sys.dbf','/data/db1/app_dat.dbf','/data/db1/app_file_dat.dbf','/data/db1/IXT_DAT.dbf','/data/db1/ODI_DAT','/data/db1/RAC_DAT.dbf'

ORA-31625: Schema APEX_050000 is needed to import this object, but is unaccessible

BEGIN

 execute immediate 'delete from xdb.xdb$import_tt_info'; COMMIT; END;

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

ORA-39360: Table "HM"."EBA_UT_CHART_PROJECTS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.

…

ORA-39360: Table "DI"."APEX$TEAM_DEV_FILES" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.