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;