crdb2intra.sql

 

---

 REM * This script takes care off all commands necessary to create 
 REM * an OFA compliant database after the CREATE DATABASE command has
 REM * succeeded.
 
 REM * Set terminal output and command echoing on; log output of this script.
 REM *
 set termout on
 set echo on
 spool /opt/oracle/admin/intra/create/crdb2intra.lst
 
 REM * The database should already be started up at this point with:
 REM * pfile=$ORACLE_BASE/admin/create/SID/initSID.ora0
 
 connect internal
 
 REM # install data dictionary views:
 @$ORACLE_HOME/rdbms/admin/catalog.sql
 
 REM * Create additional rollback segment in SYSTEM before creating tablespace.
 REM *
 connect internal
 create rollback segment r0 tablespace system
 storage (initial 16k next 16k minextents 2 maxextents 20);
 
 
 
 REM * Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online without shutting
 REM * down and restarting the database.
 REM *
 alter rollback segment r0 online;	
 
 REM * Create a tablespace for rollback segments.
 REM * Rollback segment configuration guidelines:
 REM *   1 rollback segments for every 4 concurrent xactions.
 REM *   No more than 50 rollback segments.
 REM *   All rollback segments the same size.
 REM *   Between 2 and 4 homogeneously-sized extents per rollback segment.
 REM * Attempt to keep rollback segments to 4 extents.
 REM *
 create tablespace rbs_a datafile
 	'/oradata1/intra/intra_rbs_a1.dbf'	size   50M
 default storage (
 	initial		 512k
 	next		 512k
 	pctincrease	   0
 	minextents	   20
 	maxextents	   100
 );
 
 create tablespace rbs_b datafile
 	'/oradata2/intra/intra_rbs_b1.dbf'	size   50M
 default storage (
 	initial		 512k
 	next		 512k
 	pctincrease	   0
 	minextents	   20
 	maxextents	   100
 );
 
 REM * Create a tablespace for temporary segments.
 REM * Temporary tablespace configuration guidelines:
 REM *   Initial and next extent sizes = k * SORT_AREA_SIZE, k in {1,2,3,...}.
 REM *
 create tablespace temp datafile
 	'/oradata2/intra/intra_temp1.dbf'	size   100M
 default storage (
 	initial      512k
 	next         512k
         pctincrease  0
 ) temporary;
 
 create tablespace tools datafile
 	'/oradata2/intra/intra_tools1.dbf'	size   300M
 default storage (
 	initial      512k
 	next         512k
         pctincrease  0
 );
 
 create tablespace users datafile
 	'/oradata2/intra/intra_users1.dbf'	size   100M
 default storage (
 	initial      512k
 	next         512k
         pctincrease  0
 );
 
 REM * Create rollback segments.
 REM * Create rollback segments.
 REM *
 create rollback segment r01 tablespace rbs_a storage (minextents 20 optimal 10m);
 create rollback segment r02 tablespace rbs_b storage (minextents 20 optimal 10m);
 create rollback segment r03 tablespace rbs_a storage (minextents 20 optimal 10m);
 create rollback segment r04 tablespace rbs_b storage (minextents 20 optimal 10m);
 
 REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online 
 REM * without shutting down and restarting the database.  Only put one
 REM * of the rollback segments online at this time so that it will always
 REM * be the one used.  When the user shuts down the database and starts
 REM * it up with initSID.ora, all four will be brought online.
 REM *
 alter rollback segment r01 online;
 alter rollback segment r02 online;
 alter rollback segment r03 online;
 alter rollback segment r04 online;
 
 REM * Check for all non-system rollback segment are inline:
 
 select segment_name, tablespace_name, initial_extent,status 
  from dba_rollback_segs;
 
 REM * Since we've created and brought online 2 more rollback segments,
 REM * we no longer need the second rollback segment in the SYSTEM tablespace.
 alter rollback segment r0 offline;
 drop rollback segment r0;
 
 REM * Alter SYS and SYSTEM users.
 REM *
 alter user sys temporary tablespace temp;
 revoke resource from system;
 revoke resource on system from system;
 -- grant resource on tools to system;
 alter user system default tablespace tools temporary tablespace temp;
 alter tablespace system default storage (pctincrease 0);
 
 REM
 REM * If procedural option, run as SYS
 REM do follow this order to create dictionary!!!
 -- @$ORACLE_HOME/rdbms/admin/catalog.sql
 @$ORACLE_HOME/rdbms/admin/catproc.sql
 @$ORACLE_HOME/rdbms/admin/catparr.sql
 
 
 
 REM * For each DBA user, run DBA synonyms SQL script.  Don't forget that EACH
 REM * DBA USER created in the future needs dba_syn.sql run from its account.
 REM *
 connect system/manager
 @$ORACLE_HOME/rdbms/admin/catdbsyn.sql
 @$ORACLE_HOME/sqlplus/admin/pupbld.sql
 
 
 REM Create Data Tablespace
 create tablespace data datafile
 	'/oradata2/intra/intra_data1.dbf'	size   400M
 default storage (
 	initial      512k
 	next         512k
         pctincrease  0
 );
 
 
 
 
 REM Create Index Tablespace
 
 create tablespace indx datafile
 	'/oradata2/intra/intra_indx1.dbf'	size   200M
 default storage (
 	initial      512k
 	next         512k
         pctincrease  0
 );
 
 
 
 
 REM Create DBMS_SHARED_POOL package
 connect as sys
 @$ORACLE_HOME/rdbms/admin/dbmspool.sql
 @$ORACLE_HOME/rdbms/admin/prvtpool.plb
 drop public synonym dbms_shared_pool;
 create public synonym dbms_shared_pool for dbms_shared_pool;
 
 REM Create AQ related Objects
 connect as sys
 @$ORACLE_HOME/rdbms/admin/catqueue.sql
 
 -- Create user jboss
 grant aq_user_role to jboss;
 grant aq_administrator_role to jboss;
 
 REM Create JVM
 connect as sys
 @$ORACLE_HOME/javavm/install/initjvm.sql
 
 spool off

---