Thinking Out Loud

February 26, 2017

dbca does hard coding

Filed under: 11g,oracle — mdinh @ 3:12 pm

Creating Additional Data Dictionary Structures

catoctk.sql: Oracle Cryptographic Toolkit package

owminst.plb: Workspace Manager

Workspace Manager is installed by default in the seed database and in all databases created by the Database Configuration Assistant (DBCA).

However, in all other Oracle databases, such as those you create with a customized procedure, you must install Workspace Manager before you can use its features.

I know what you are thinking, “What’s the BIG deal?”.  For the most part, it’s not.

However, when a person uses dbca to create a database with different ORACLE_HOME, SID from one server versus another, it becomes inefficient to search and replace all the hard coded values.

Now I know what you are thinking, “Why are ORACLE_HOME’s different to begin with and why don’t I just use dbca to create another database?”

That’s not the scope for this post :=) but I am sure you can ascertain the answers.

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ ls -alrt

total 40
drwxr-x---. 3 oracle oinstall 4096 Feb 23 20:45 ..
-rw-r-----. 1 oracle oinstall 1948 Feb 23 20:45 init.ora
-rwxr-xr-x. 1 oracle oinstall  627 Feb 23 20:45 foo.sh
-rw-r-----. 1 oracle oinstall 1143 Feb 23 20:45 CreateDB.sql
-rw-r-----. 1 oracle oinstall  346 Feb 23 20:45 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall  768 Feb 23 20:45 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  506 Feb 23 20:45 lockAccount.sql
-rwxr-xr-x. 1 oracle oinstall  656 Feb 23 20:45 foo.sql
drwxr-x---. 2 oracle oinstall 4096 Feb 23 20:45 .
-rw-r-----. 1 oracle oinstall  966 Feb 23 20:45 postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sh

#!/bin/sh

OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/foo/adump
mkdir -p /u01/app/oracle/admin/foo/dpdump
mkdir -p /u01/app/oracle/admin/foo/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/foo
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/product/11.2.0.4/db_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=foo; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: foo:/u01/app/oracle/product/11.2.0.4/db_1:Y
/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/foo/scripts/foo.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat foo.sql

set verify off
-- DEFINE sysPassword="hacker"
-- DEFINE systemPassword="hacker"
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwfoo force=y
@/u01/app/oracle/admin/foo/scripts/CreateDB.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/foo/scripts/CreateDBCatalog.sql
host /u01/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d foo -o /u01/app/oracle/product/11.2.0.4/db_1 -n foo
@/u01/app/oracle/admin/foo/scripts/lockAccount.sql
@/u01/app/oracle/admin/foo/scripts/postDBCreation.sql

oracle@arrow1:HAWKA:/u01/app/oracle/admin/foo/scripts
$ cat CreateDBCatalog.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/foo/scripts/CreateDBCatalog.log append
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/foo/scripts/sqlPlusHelp.log append
@/u01/app/oracle/product/11.2.0.4/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off

Here’s one that I have for creating single instance DB.

Not the best as I should have labeled “pro *** Running initxml.sql ***”
with its corresponding COMP_ID or COMP_NAME versus name of the sql script.

NOTE: for production environment, you would not autoextend datafile 128M (really bad for performance).

define sysPassword="hacker"
define systemPassword="hacker"
spool crdb.log
set echo on timing on time on
host echo $ORACLE_SID
host sysresv
create spfile from pfile;
startup force nomount;
CREATE DATABASE
MAXINSTANCES 1
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 400
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE='US/Pacific'
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword"
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 513M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M
DEFAULT TABLESPACE users DATAFILE SIZE 129M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
UNDO TABLESPACE undotbs DATAFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M;
spool off
--
spool run_catalog.log
pro *** Running catalog.sql ***
@?/rdbms/admin/catalog.sql
spool off
--
spool run_catproc.log
pro *** Running catproc.sql ***
@?/rdbms/admin/catproc.sql
spool off
/* 
-- BEG Add additional components as required.
spool run_initjvm.log
pro *** Running initjvm.sql ***
@?/javavm/install/initjvm.sql
spool off
--
spool run_initxml.log
pro *** Running initxml.sql ***
@?/xdk/admin/initxml.sql
spool off
--
spool run_xmlja.log
pro *** Running initxml.sql ***
@?/xdk/admin/xmlja.sql
spool off
--
spool run_catjava.log
pro *** Running catjava.sql ***
@?/rdbms/admin/catjava.sql
spool off
--
spool run_catxdbj.log
pro *** Running catcatxdbj.sql ***
@?/rdbms/admin/catxdbj.sql
spool off
-- END Add additional components as required.
*/ 
spool crdb.log append 
exec dbms_scheduler.set_scheduler_attribute(attribute=>'default_timezone',value=>'US/Pacific'); 
connect system/"&&systemPassword" 
@?/sqlplus/admin/pupbld.sql 
exit
Advertisements

1 Comment »

  1. […] Not too long ago, I discovered: dbca does hard coding […]

    Pingback by Bad Practice from Oracle – datapatch | Thinking Out Loud — September 23, 2017 @ 1:18 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: