Rem
Rem $Header: clustdb.sql 14-oct-2002.15:53:17 rxkumar Exp $
Rem
Rem
Rem clustdb.sql
Rem
Rem Copyright (c) 1999, 2002, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      clustdb.sql - Example database creation script
Rem
Rem    DESCRIPTION
Rem      Creates a RAC database on Unix
Rem
Rem    NOTES
REM ******************************************************************
REM ** UNIX clustdb.SQL Version
REM ** 
REM ** Please update this file to reflect the correct values for
REM ** 1) The init.ora file in startup nomount pfile=  
REM ** 2) The sysdba account and password if not using connect / as sysdba
REM **    (Note: The connect / as sysdba statement occurs multiple
REM **           times in the sql script below)
REM ** 3) The location of the sql scripts, to reflect your ORACLE_HOME.
REM ** 4) The raw partition names(Symbolic link name) for log and data files
REM **    Note: This script will add two additional log files for the second 
REM **          instance. If your cluster contains more nodes, you
REM **          must create and enable the additional redo thread and 
REM **          logfiles for instances on those nodes.
REM ** 5) The name of the database in the "CREATE DATABASE " statement.
REM ** 6) The character and national character sets for the databse, see
REM **    "CREATE DATABASE " statement.
REM ** 6) The size of tablespaces, if you would like to increase or 
REM **    decrease the default size.
REM **
REM ** The following options are loaded as part of this script
REM ** remove or comment out as necessary.
REM ** 1) Context (tablespace only)
REM ** 2) Advanced Replication
REM ** 3) Spatial
REM ** 4) interMedia
REM ** 5) Time Service 
REM ** 6) Visual Information Retrieval  
REM ** 7) JServer 
REM ******************************************************************
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rxkumar     10/14/02 - bug2613535
Rem    rxkumar     03/12/02 - add exit towards the end.
Rem    rxkumar     03/07/02 - 9.2 changes.
Rem    rajayar     08/06/01 - Raw device name changes
Rem    rajayar     07/25/01 - 1795029 bug fixes
Rem    rajayar     05/08/01 - Raw Device name changes.
Rem    rajayar     05/07/01 - create undo tbs for 2nd inst.
Rem    rajayar     05/07/01 - Autoextend off after database creation.
Rem    rajayar     05/04/01 - manual database script changes.
Rem    rajayar     05/04/01 - changes in manual database cretion script.
Rem    jcreight    08/25/00 - connect internal no longer supported
Rem    myechuri    07/24/00 - 8.1.7 branch
Rem    jcreight    11/19/99 - Example database creation script
Rem    jcreight    11/19/99 - Created
Rem
spool createdb.log

set echo on
connect / as sysdba
startup nomount pfile="%ORACLE_BASE%/admin/clustdb/pfile/init.ora"
CREATE DATABASE clustdb
CONTROLFILE REUSE
MAXLOGMEMBERS 3 
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 32
MAXLOGFILES 64
DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_system_400m' SIZE 380M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_temp_100m' SIZE 80M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_undotbs1_250m' SIZE 240M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/dev/vx/rdsk/oracle_dg/clustdb_raw_log11_120m') SIZE 110M REUSE,
GROUP 2 ('/dev/vx/rdsk/oracle_dg/clustdb_raw_log12_120m') SIZE 110M REUSE
CHARACTER SET US7ASCII 
NATIONAL CHARACTER SET AL16UTF16;
spool off

spool createdb1.log
set echo on

REM *** WHEN USING MANUAL UNDO MANAGEMENT, DELETE THE UNDO TABLESPACE 
REM     LINE FROM THE CREATE DATABASE COMMAND AND REPLACE IT WITH THE FOLLOWING
REM     LINE FOR RBS TABLESPACE. 
REM TABLESPACE RBS DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_rbs_580m' 
REM SIZE 560M REUSE MINIMUM EXTENT 512K;

REM ***********TABLESPACE FOR CWMLITE *********
CREATE TABLESPACE "CWMLITE" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_cwmlite_100m' SIZE 80M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR DRSYS *********
CREATE TABLESPACE "DRSYS" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_dr_250m' SIZE 220M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR EXAMPLE *********
CREATE TABLESPACE "EXAMPLE" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_example_160m' SIZE 140M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR INDEX *********
CREATE TABLESPACE "INDX" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_indx_70m' SIZE 50M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR ODM *********
CREATE TABLESPACE "ODM" LOGGING DATAFILE 'dev/vx/rdsk/oracle_dg/clustdb_raw_odm_120m' SIZE 100M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR TOOLS *********
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_tools_20m' SIZE 16M REUSE AUTOEXTEND ON NEXT  320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR USERS *********
CREATE TABLESPACE "USERS" LOGGING DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_users_120m' SIZE 100M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

REM ***********TABLESPACE FOR XDB *********
CREATE TABLESPACE "XDB" LOGGING DATAFILE 'dev/vx/rdsk/oracle_dg/clustdb_raw_xdb_120m' SIZE 100M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;


REM ********** UNDO TABLESPACE FOR SECOND INSTANCE  **********
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/dev/vx/rdsk/oracle_dg/clustdb_raw_undotbs2_250m' SIZE 240M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

REM **** Uncomment out rollback_segments for manual undo management
REM **** Rollback segments ***************
REM spool clustdbrbs.log;
REM connect / as sysdba
REM create rollback segment rbs1_1 storage(initial 200K next 200K)
REM tablespace RBS;
REM alter rollback segment rbs1_1 online;
REM create rollback segment rbs1_2 storage(initial 200K next 200K)
REM tablespace RBS;
REM alter rollback segment rbs1_2 online;
REM create rollback segment rbs2_1 storage(initial 200K next 200K)
REM tablespace RBS;
REM alter rollback segment rbs2_1 online;
REM create rollback segment rbs2_2 storage(initial 200K next 200K)
REM tablespace RBS;
REM alter rollback segment rbs2_2 online;
REM spool off
REM **** End Rollback segments for 2 instance cluster database ****

REM **** Various SQL packages *************** 
@%ORACLE_HOME%/rdbms/admin/catalog.sql;
@%ORACLE_HOME%/rdbms/admin/catexp7.sql;
@%ORACLE_HOME%/rdbms/admin/catblock.sql;
@%ORACLE_HOME%/rdbms/admin/catproc.sql;
@%ORACLE_HOME%/rdbms/admin/catoctk.sql;
@%ORACLE_HOME%/rdbms/admin/owminst.plbl;
connect system/manager
@%ORACLE_HOME%/sqlplus/admin/pupbld.sql;
connect system/manager
@%ORACLE_HOME%/sqlplus/admin/help/hlpbld.sql helpus.sql;
REM **** End various SQL packages ***************

REM **** Demo support ***************
REM **** INSTALL HR DEMO SCHEMA ***************
connect SYSTEM/manager
@%ORACLE_HOME%/demo/schema/human_resources/hr_main.sql change_on_install EXAMPLE TEMP change_on_install %ORACLE_HOME%/assistants/dbca/logs/;

REM **** INSTALL ORDER ENTRY DEMO SCHEMA ***************
connect SYSTEM/manager
@%ORACLE_HOME%/demo/schema/order_entry/oe_main.sql change_on_install EXAMPLE TEMP change_on_install change_on_install %ORACLE_HOME%/assistants/dbca/logs/;

REM **** INSTALL PRODUCT MEDIA DEMO SCHEMA ***************
connect SYSTEM/manager
@%ORACLE_HOME%/demo/schema/product_media/pm_main.sql change_on_install EXAMPLE TEMP change_on_install change_on_install %ORACLE_HOME%/demo/schema/product_media/ %ORACLE_HOME%/assistants/dbca/logs/ %ORACLE_HOME%/demo/schema/product_media/;

REM **** INSTALL SALES HISTORY DEMO SCHEMA ***************
connect SYSTEM/manager
@%ORACLE_HOME%/demo/schema/sales_history/sh_main.sql change_on_install EXAMPLE TEMP change_on_install %ORACLE_HOME%/demo/schema/sales_history/ %ORACLE_HOME%/assistants/dbca/logs/;
@%ORACLE_HOME%/demo/schema/sales_history/sh_olp_c.sql;

REM **** INSTALL QUEUED SHIPPING DEMO SCHEMA ***************
connect SYSTEM/manager
@%ORACLE_HOME%/demo/schema/shipping/qs_main.sql change_on_install EXAMPLE TEMP manager change_on_install change_on_install %ORACLE_HOME%/assistants/dbca/logs/;
spool off

REM **** End Demo ***************


REM *** Spatial, interMedia, Time Service and Visual Information Retrieval ***
spool ordinst.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/ord/admin/ordinst.sql;
spool off
REM *** END Spatial, interMedia, Time Service and Visual Information Retrieval ***

REM  ********* Spatial Option ***************
spool mdinst.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/md/admin/mdinst.sql
spool off
REM ********** End Spatial Option ***************


REM ********** interMedia Option ***************
spool iMediainst.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/ord/im/admin/iminst.sql;
spool off
REM ********** End interMedia Option ***************

REM ********** JServer Option ***************
spool jvminst.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/javavm/install/initjvm.sql;
@%ORACLE_HOME%/xdk/admin/initxml.sql;
@%ORACLE_HOME%/xdk/admin/xmlja.sql;
@%ORACLE_HOME%/rdbms/admin/catjava.sql;
REM ********** End JServer Option ***************

REM ********** XML Option ***************
spool xml.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/rdbms/admin/catqm.sql change_on_install XDB TEMP;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/rdbms/admin/catxdbj.sql;
spool off
REM ********** End XML Option ***************


REM ********** ultraSearch Option ***************
spool ultraSearch.log;
connect SYS/change_on_install as SYSDBA
@%ORACLE_HOME%/ultrasearch/admin/wk0install.sql SYS change_on_install change_on_install DRSYS TEMP "" PORTAL false;
spool off
REM ********** End interMedia Option ***************


REM **** Redo logfiles for the second instance ***************
spool clustlog.log;
connect SYS/change_on_install as SYSDBA
alter database add logfile thread 2
 group 3 '/dev/vx/rdsk/oracle_dg/clustdb_raw_log21_120m' SIZE 110M  reuse,
 group 4 '/dev/vx/rdsk/oracle_dg/clustdb_raw_log22_120m' SIZE 110M  reuse;

REM **** Enable the new logfile for thread 2
alter database enable public thread 2;
spool off
REM **** End Logfiles for the second instance ***************


REM **** Cluster Database SQL support ***************
spool catclust.log;
connect / as sysdba
@%ORACLE_HOME%/rdbms/admin/catclust.sql
spool off
REM **** End Cluster Database SQL support ***************

REM **** TURN OFF THE AUTOEXTEND *******

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_cwmlite_100m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_example_160m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_dr_250m' autoextend OFF;

REM For undo_management=MANUAL, uncomment next line and comment the 2 lines 
REM after that.

REM alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_rbs_580m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_undotbs1_250m' autoextend OFF;
alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_undotbs2_250m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_system_400m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_temp_100m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_users_120m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_indx_70m' autoextend OFF;

alter database datafile 'dev/vx/rdsk/oracle_dg/clustdb_raw_odm_120m' autoextend OFF;

alter database datafile '/dev/vx/rdsk/oracle_dg/clustdb_raw_tools_20m' autoextend OFF;

alter database datafile 'dev/vx/rdsk/oracle_dg/clustdb_raw_xdb_120m' autoextend OFF;

exit;
