Archives For June 2013

oracle 12c: catcon.pl

June 28, 2013 — 4 Comments

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

Advertisements

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

remote connections

June 20, 2013 — Leave a comment

this happens if you need a vm for a citrix cient to work ( and as each customer uses it’s own software to provide access I maintain a vm per customer :) ). from there you need to access the remote desktop and from there you need to connect to another remote desktop :)

sessions

life could be so easy …

recently we had support oracle on hpux. not a big deal in regards to the database. but when it comes to the os commands and files and you come from another flavor of unix you probably loose a lot of time searching for the right commands and configurations.

here is a nice comparison which makes life a little easier: unixguide

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.

here is a nice overview of the history of programming languages ( source: visual.ly ):