Daniel Westermann's Blog

here is a must read white paper from oracle decribing the multitenant architecture:
multitenant architecture

topics include basic concepts, upgrading, plugging/unplugging, cloning, common and local users and much more …

the previous post gave a little more information about the catcon.pl script. this time I will show how you may let work the script for you.

lets say you have a little script and you want to deploy this on two of your container databases. for this simple demonstration I created to PDBs ( dbs300_1 and dbs300_2 ):

set lines 264
col pdb_name for a30;
select PDB_ID,PDB_NAME,STATUS from dba_pdbs;

    PDB_ID PDB_NAME			  STATUS
---------- ------------------------------ -------------
	 2 PDB$SEED			  NORMAL
	 3 DBS300_1			  NORMAL
	 4 DBS300_2			  NORMAL

both pdbs have a tablespace called users:

select c.tablespace_name
     , v.name
  from v$containers v
     , cdb_tablespaces c
 where v.con_id = c.con_id
   and tablespace_name = 'USERS';

TABLESPACE_NAME 	       NAME
------------------------------ ------------------------------
USERS			       DBS300_2
USERS			       DBS300_1
USERS			       CDB$ROOT

i want a user, one table and a row for my two pdbs:

create user u1 identified by "u" container=current quota unlimited on users;
create table u1.t1 ( a number, b date);
insert into u1.t1 (a,b) values (1,sysdate);
commit;

lets pass this script to catcon.pl and see what happens:

export PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB
perl $ORACLE_HOME/rdbms/admin/catcon.pl -b mylog -d /home/oracle -l /home/oracle -e -c 'DBS300_1 DBS300_2' test_script.sql

how many logfile should be there now ? two:

ls -la mylog*
-rw-r----- 1 oracle oinstall 1511 27. Jun 03:58 mylog0.log
-rw-r----- 1 oracle oinstall 1511 27. Jun 03:58 mylog1.log

grep "ORA-" mylog*.log

looks fine. lets see if the user, table and row really got created:

SQL> show con_name

CON_NAME
------------------------------
DBS300_1
SQL> select * from u1.t1;

	 A B
---------- --------------------
	 1 27-JUN-2013 03:58:13

SQL> 

… and the second one ?

SQL> show con_name;

CON_NAME
------------------------------
DBS300_2
SQL> select * from u1.t1;

	 A B
---------- --------------------
	 1 27-JUN-2013 03:58:13

SQL> 

great…

but there is a side effect: if you want to drop the user in the pdb you’ll get:

SQL> drop user u1 cascade;
drop user u11 cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

this is a little bit strange as the test script did not create a user with “container=all”. what you need to know here is, that catcon.pl does this:

alter session set "_oracle_script"=true;

… and this somehow results in the created users to be considered administrative users although they are not. a workaround for this is:

alter session set "_oracle_script"=true;
drop user u1 cascade;

beside that catcon.pl may be a great way to deploy scripts to all or some of your pdbs, e.g. you might want to setup development pdbs which all look the same or want to do some maintenance tasks on all the pdbs.

the last post quickly mentioned a new perl script which is used to setup the catalog in 12c. first of all the script is officially document here. in summary it is used to execute sql scripts in one or more containers. it is even possible to execute scripts in parallel, e.g. if there are multiple containers to run the scripts against.

if you take a look at the script it seems there is not much in there. but if you are used to read perl scripts you’ll quickly notify that all the logic is packed into a perl module file called catcon.pm ( which is located in the same directory ). here is a simple flow diagram which rawly describes the path through the perl code:

flow of the container scripts

so, when for for example this is executed:

!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/lcsdb -b catalog /opt/oracle/product/base/12.1.0.1/rdbms/admin/catalog.sql;

… this actually executes the catalog.sql in all the containers databases as no “-c”-switch is given. this for sure is one reason why the setup of the catalog takes much more time than in 11g.

we can confirm this by looking where catconExec is called in catcon.pl:

$RetCode = catconExec(@Scripts, 0, 0, 0);

the header of catconExec explains the parameters:

  # catconExec - run specified sqlplus script(s) or SQL statements
  #
  # If connected to a non-Consolidated DB, each script will be executed
  # using one of the processes connected to the DB.
  #
  # If connected to a Consolidated DB and the caller requested that all
  # scripts and SQL statements be run against the Root (possibly in addition
  # to other Containers), each script and statement will be executed in the
  # Root using one of the processes connected to the Root.
  #
  # If connected to a Consolidated DB and were asked to run scripts and SQL
  # statements in one or more Containers besides the Root, all scripts and
  # statements will be run against those PDBs in parallel
  #
  # Parameters:
  #   - reference to an array of sqlplus script name(s) or SQL statement(s);
  #     script names are expected to be prefixed with @
  #   - an indicator of whether scripts need to be run in order
  #       TRUE => run in order
  #   - an indicator of whether scripts or SQL statements need to be run only
  #     in the Root if operating on a CDB (temporarily overriding whatever
  #     was set by catconInit)
  #       TRUE => if operating on a CDB, run in Root only
  #   - an indicator of whether per process initialization/completion
  #     statements need to be issued
  #     TRUE => init/comletion statements, if specified, will be issued

in short: run all the scripts referenced in the first parameter ( which is a perl array ), do not run it in order, do not run it only in the root container and do not issue per process initialization/completion.

at the end a little hardcoding in catconInit: “(default sys/knl_test7 AS SYSDBA)”. i wonder if this is a default password used at oracle development :) :

find . -type f | xargs grep "knl_test7"
./admin/catcon.pm:#     set connect string to 'sys/knl_test7 AS SYSDBA'
./admin/catcon.pm:    $connect = "sys/knl_test7"; 
./admin/catcon.pm:  #     undefined (default sys/knl_test7 AS SYSDBA)

update 2013-JUN-29: knl_test7 is also referenced in some older documentations:
10g Release 1 (10.1)
Oracle 8i Data Cartridge Developer’s Guide

as usual when oracle releases a new version of its database I start the dbca and generate the scripts to see what happens in the background. there are quite some interesting changes:

• the create database statement contains new parameters: “enable pluggable database” and “seed file_name_convert”
• there is a perl wrapper script to setup the database catalog (catcon.pl)
• xdb is installed by catproc
• a hidden parameter “_oracle_script” is used while setting up the catalog
• there is a new parameter for the init.ora: enable_pluggable_database=true
• files for the seed container get created, so you’ll need more space

I am sure there are much more changes but these are the ones that catched my eyes while looking at the scripts. here is the complete sample setup ( you’ll need to be patient, this will take much more time than you are used to in 11g ):

umask 0027
mkdir -p /oradata/dbs300/admin/adump
mkdir -p /oradata/dbs300/admin/dpdump
mkdir -p /oradata/dbs300/admin/audit
mkdir -p /oradata/dbs300/admin/pfile
mkdir -p /oradata/dbs300/appl
mkdir -p /oradata/dbs300/dbf
mkdir -p /oradata/dbs300/rdo1
mkdir -p /oradata/dbs300/rdo2
mkdir -p /oradata/dbs300/pdbseed
mkdir -p /oradata/dbs300/fast_recovery_area

orapwd file=/oradata/dbs300/admin/pfile/orapwdbs300 force=y format=12 password=oracle

echo "*.db_block_size=8192
*.control_file_record_keep_time=35
*.filesystemio_options='setall'
*.disk_asynch_io=TRUE
*.db_block_checksum=TYPICAL
*.db_block_checking=MEDIUM
*.db_cache_advice='ON'
*.db_keep_cache_size=0m
*.db_files=1024
*.db_recycle_cache_size=0m
*.fast_start_mttr_target=500
*.instance_name='dbs300'
*.open_cursors=300
*.db_domain='local'
*.db_name="dbs300"
*.log_archive_dest_1='location=/oradata/dbs300/fast_recovery_area'
*.log_archive_format='dummy_%r_%t_%S.arc'
*.job_queue_processes=2
*.control_files=('/oradata/dbs300/rdo1/control01.ctl', '/oradata/dbs300/rdo2/control02.ctl')
*.db_recovery_file_dest='/oradata/dbs300/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.compatible=12.1.0.0.0
*.diagnostic_dest=/oradata/dbs300/admin/
*.enable_pluggable_database=true
*.processes=300
*.sga_target=256m
*.audit_file_dest='/oradata/dbs300/admin/adump/'
*.audit_trail=db
*.remote_login_passwordfile=EXCLUSIVE
*.pga_aggregate_target=80m
*.parallel_max_servers=5
*.query_rewrite_enabled='FALSE'
*.recovery_parallelism=4
*.star_transformation_enabled='FALSE'
*.statistics_level='typical'
*.timed_statistics=TRUE
*.recyclebin='OFF'
*.undo_management='auto'
*.undo_retention=10800
*.workarea_size_policy='auto'
*.optimizer_capture_sql_plan_baselines='FALSE'
*.optimizer_use_sql_plan_baselines='TRUE'
*.optimizer_dynamic_sampling=2
*.max_dump_file_size=50m
*.remote_login_passwordfile=EXCLUSIVE
*.undo_tablespace=UNDOTBS1" > /oradata/dbs300/admin/pfile/initdbs300.ora

PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB

sqlplus / as sysdba
startup force nomount pfile='/oradata/dbs300/admin/pfile/initdbs300.ora';
create spfile='/oradata/dbs300/admin/pfile/spfiledbs300.ora' from pfile='/oradata/dbs300/admin/pfile/initdbs300.ora';
!ln -s /oradata/dbs300/admin/pfile/spfiledbs300.ora $ORACLE_HOME/dbs/
startup force nomount

CREATE DATABASE "dbs300"
MAXINSTANCES 8
MAXLOGHISTORY 5
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 1024
DATAFILE '/oradata/dbs300/dbf/system_01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/dbs300/dbf/sysaux_01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g 
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/dbs300/dbf/temp_01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g
UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/oradata/dbs300/dbf/undotbs_01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g 
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oradata/dbs300/rdo1/redo01_1.log', '/oradata/dbs300/rdo2/redo01_2.log') SIZE 64m,
        GROUP 2 ('/oradata/dbs300/rdo1/redo02_1.log', '/oradata/dbs300/rdo2/redo02_2.log') SIZE 64m,
        GROUP 3 ('/oradata/dbs300/rdo1/redo03_1.log', '/oradata/dbs300/rdo2/redo03_2.log') SIZE 64m
USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"
enable pluggable database
seed file_name_convert=('/oradata/dbs300/dbf/system_01.dbf', '/oradata/dbs300/pdbseed/system_01.dbf'
                       ,'/oradata/dbs300/dbf/sysaux_01.dbf', '/oradata/dbs300/pdbseed/sysaux_01.dbf'
                       ,'/oradata/dbs300/dbf/temp_01.dbf', '/oradata/dbs300/pdbseed/temp_01.dbf'
                       ,'/oradata/dbs300/dbf/undotbs_01.dbf', '/oradata/dbs300/pdbseed/undotbs_01.dbf');

CREATE TABLESPACE "USERS" LOGGING DATAFILE '/oradata/dbs300/dbf/users_01.dbf' SIZE 16m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";

alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;

!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catalog /opt/oracle/product/base/12.1.0.1/rdbms/admin/catalog.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catblock /opt/oracle/product/base/12.1.0.1/rdbms/admin/catblock.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catproc /opt/oracle/product/base/12.1.0.1/rdbms/admin/catproc.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catoctk /opt/oracle/product/base/12.1.0.1/rdbms/admin/catoctk.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b pupbld -u SYSTEM/oracle /opt/oracle/product/base/12.1.0.1/sqlplus/admin/pupbld.sql;
connect system/oracle
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b hlpbld -u SYSTEM/oracle -a 1  /opt/oracle/product/base/12.1.0.1/sqlplus/admin/help/hlpbld.sql 1helpus.sql;

conn / as sysdba
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catclust /opt/oracle/product/base/12.1.0.1/rdbms/admin/catclust.sql;
grant sysdg to sysdg;
grant sysbackup to sysbackup;
grant syskm to syskm;

@?/rdbms/admin/utlrp

-- for java
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /opt/oracle/product/base/12.1.0.1/javavm/install/initjvm.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /opt/oracle/product/base/12.1.0.1/xdk/admin/initxml.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /opt/oracle/product/base/12.1.0.1/xdk/admin/xmlja.sql;
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /opt/oracle/product/base/12.1.0.1/rdbms/admin/catjava.sql;
connect "SYS"/oracle as SYSDBA
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catxdbj /opt/oracle/product/base/12.1.0.1/rdbms/admin/catxdbj.sql;

-- for oracle text
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catctx -a 1  /opt/oracle/product/base/12.1.0.1/ctx/admin/catctx.sql 1change_on_install 1SYSAUX 1TEMP 1LOCK;
alter user CTXSYS account unlock identified by "CTXSYS";
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b dr0defin -u CTXSYS/CTXSYS -a 1  /opt/oracle/product/base/12.1.0.1/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
connect "SYS"/oracle as SYSDBA
alter user CTXSYS password expire account lock;

--for ord
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b ordinst -a 1  /opt/oracle/product/base/12.1.0.1/ord/admin/ordinst.sql 1SYSAUX 1SYSAUX;

-- for intermedia
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b iminst /opt/oracle/product/base/12.1.0.1/ord/im/admin/iminst.sql;

-- for olap
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b olap -a 1  /opt/oracle/product/base/12.1.0.1/olap/admin/olap.sql 1SYSAUX 1TEMP;

-- for spatial
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b mdinst /opt/oracle/product/base/12.1.0.1/md/admin/mdinst.sql;

-- for label security
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catols /opt/oracle/product/base/12.1.0.1/rdbms/admin/catols.sql;

-- for apex
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catapx -a 1  /opt/oracle/product/base/12.1.0.1/apex/catapx.sql 1change_on_install 1SYSAUX 1SYSAUX 1TEMP 1/i/ 1NONE;

-- for datavault
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catmac -a 1  /opt/oracle/product/base/12.1.0.1/rdbms/admin/catmac.sql 1SYSAUX 1TEMP 1oracle;

-- psu
!perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catbundle -a 1  /opt/oracle/product/base/12.1.0.1/rdbms/admin/catbundle.sql 1psu 1apply;

select COMP_NAME,VERSION,STATUS from dba_registry

COMP_NAME				 VERSION			STATUS
---------------------------------------- ------------------------------ ------------------------------
Oracle XML Database			 12.1.0.1.0			VALID
Oracle Database Catalog Views		 12.1.0.1.0			VALID
Oracle Database Packages and Types	 12.1.0.1.0			VALID
Oracle Real Application Clusters	 12.1.0.1.0			OPTION OFF
shutdown immediate;
-- add to gi
!/opt/oracle/product/base/12.1.0.1/bin/srvctl add database -d dbs300 -o /opt/oracle/product/base/12.1.0.1 -n dbs300
!/opt/oracle/product/base/12.1.0.1/bin/srvctl start database -d dbs300;

more to come …

if you are used to use silent installation for the oracle database software you need to provide additional parameters: oracle.install.db.BACKUPDBA_GROUP, oracle.install.db.DGDBA_GROUP and oracle.install.db.KMDBA_GROUP

./runInstaller oracle.install.option=INSTALL_DB_SWONLY \
    ORACLE_BASE=/opt/oracle/product/base \
    ORACLE_HOME=/opt/oracle/product/base/12.1.0.1 \
    UNIX_GROUP_NAME=oinstall \
    oracle.install.db.DBA_GROUP=dba \
    oracle.install.db.OPER_GROUP=oper \
    oracle.install.db.BACKUPDBA_GROUP=dba \
    oracle.install.db.DGDBA_GROUP=dba \
    oracle.install.db.KMDBA_GROUP=dba \
    FROM_LOCATION=../stage/products.xml \
    INVENTORY_LOCATION=/opt/oracle/oraIventory \
    SELECTED_LANGUAGES=en \
    oracle.install.db.InstallEdition=EE \
    DECLINE_SECURITY_UPDATES=true  -silent -ignoreSysPrereqs -ignorePrereq -waitForCompletion

otherwise your installation will fail with:

[FATAL] [INS-35341] The installation user is not a member of the following groups: [null, null, null]
   CAUSE: The installation user account must be a member of all groups required for installation.
   ACTION: Ensure that the installation user is a member of the system privileges operating system groups you selected.

these are used to further improve seperation of duties: documentation

to be honest, I never was aware of this. there is a create schema command in oracle which can be used to populate a schema in a singe transaction:

create user u identified by u;
create user a identified by a;
grant create session, create table, create view to u;
alter user u quota 1m on users;
connect u/u
create schema authorization u
   create table t1 
      (a number, b varchar2(10))
   create view v1
      as select * from t1 where a = 1
   grant select on v1 to a;

this can be very handy if you want a script ( which creates some tables and views ) to be atomic. if there is an error in your create schema script like:

create schema authorization u
   create table t1
      (a number, b varchar2(10))
   create view v1
      as select **** from t1 where a = 1
   grant select on v1 to a;

… nothing will be created at all. you may fix the script and start over again ( without dropping anything as you would need to do it if you use single create commands ).

this is documented here.