Rem Rem $Header: clustdb.sql 25-jul-01.12:01:45 rajayar Exp $ Rem Rem Rem clustdb.sql Rem Rem Copyright (c) Oracle Corporation 1999, 2000, 2001. All Rights Reserved. Rem Rem NAME Rem clustdb.sql - Example database creation script Rem Rem DESCRIPTION Rem Creates a RAC database on NT Rem Rem NOTES REM ****************************************************************** REM ** NT 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 ** node. If your cluster will contain more nodes(instances) you REM ** must create and enable the additional logfiles for those node. 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 rajayar 07/25/01 - UNDOTBS2 change Rem rajayar 07/11/01 - Text format conversion from ISO to DOS Rem rajayar 07/10/01 - pfile location correction Rem rajayar 07/09/01 - Symbolic link name change Rem rajayar 07/03/01 - Size for redo2 log files Rem rajayar 06/24/01 - NT Rem rajayar 06/19/01 - Rem rajayar 06/11/01 - Symbolic name changes for NT 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_HOME%/srvm/admin/initclustdb.ora" CREATE DATABASE clustdb CONTROLFILE REUSE MAXLOGMEMBERS 5 MAXLOGHISTORY 100 MAXDATAFILES 254 MAXINSTANCES 32 MAXLOGFILES 64 DATAFILE '\\.\clustdb_system1' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED UNDO TABLESPACE "undotbs1" datafile '\\.\clustdb_undotbs1' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED LOGFILE GROUP 1 ('\\.\clustdb_redo1_1') size 110m REUSE, GROUP 2 ('\\.\clustdb_redo1_2') size 110m REUSE CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16; spool off spool createdb1.log set echo on REM *** WHEN USING UNDO_MANAGEMENT=MANUAL, DELETE THE UNDO TABLESPACE ... REM LINE FROM THE CREATE DATABASE COMMAND AND UNCOMMENT THE FOLLOWING REM SQL STATEMENT FOR RBS TABLESPACE. REM CREATE TABLESPACE RBS DATAFILE 'clustdb_rbs1' REM SIZE 520M REUSE MINIMUM EXTENT 512K; REM ********** TABLESPACE FOR USER ********** CREATE TABLESPACE USERS DATAFILE '\\.\clustdb_users1' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM ********** TABLESPACE FOR TEMPORARY ********** CREATE TABLESPACE TEMP DATAFILE '\\.\clustdb_temp1' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TEMPORARY; REM ********** TABLESPACE FOR Tools ********** CREATE TABLESPACE TOOLS DATAFILE '\\.\clustdb_tools1' SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM ********** TABLESPACE FOR INDEX ********** CREATE TABLESPACE INDX DATAFILE '\\.\clustdb_indx1' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM ***********TABLESPACE FOR EXAMPLE ********* CREATE TABLESPACE EXAMPLE DATAFILE '\\.\clustdb_example1' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM ***********TABLESPACE FOR CWMLITE ********* CREATE TABLESPACE CWMLITE DATAFILE '\\.\clustdb_cwmlite1' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM ********** UNDO TABLESPACE FOR SECOND INSTANCE ********** CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '\\.\clustdb_undotbs2' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; REM **** comment out rollback_segments for automatic undo management REM **** Rollback segments for our 2 nodes *************** REM spool psrbs.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 our 2 nodes *************** alter user sys temporary tablespace TEMP; REM **** Various SQL packages *************** @%ORACLE_HOME%/rdbms/admin/catalog.sql; @%ORACLE_HOME%/rdbms/admin/catexp7.sql @%ORACLE_HOME%/rdbms/admin/catproc.sql @%ORACLE_HOME%/rdbms/admin/caths.sql connect system/manager @%ORACLE_HOME%/dbs/pupbld.sql REM **** End various SQL packages *************** REM ***** Scott's tables *************** connect / as sysdba @%ORACLE_HOME%/rdbms/admin/scott.sql REM ***** End Scott table *************** REM **** Demo support *************** connect / as sysdba @%ORACLE_HOME%/rdbms/admin/demo.sql connect / as sysdba spool off REM **** End Demo *************** REM ********** TABLESPACE FOR Context ********** spool ctxtbls.log; connect / as sysdba CREATE TABLESPACE DRSYS DATAFILE '\\.\clustdb_drsys1' SIZE 40M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; spool off REM ********** END Context tablespace *************** REM ********** Spatial, interMedia, Time Service and Visual Information Retrieval *************** spool ordinst.log; connect / as sysdba @%ORACLE_HOME%/ord/admin/ordisys.sql -- -- Steps that must be executed as ORDSYS -- connect ORDSYS/ORDSYS; @%ORACLE_HOME%/ord/admin/ordiords.sql spool off REM ********** END Spatial, interMedia, Time Service and Visual Information Retrieval *************** REM ********* Spatial Option *************** REM spool mdinst.log; REM connect / as sysdba REM @%ORACLE_HOME%/md/admin/mdinst.sql REM spool off REM ********** End Spatial Option *************** REM ********** Advanced Replication *************** spool spoolrep.log; connect / as sysdba @%ORACLE_HOME%/rdbms/admin/catrep.sql spool off REM ********** End Advanced Replication *************** REM ********** Time Series Option *************** REM spool tsinst.log; REM connect / as sysdba REM @%ORACLE_HOME%/ord/ts/admin/tsinst.sql; REM spool off REM ********** End Time Series Option *************** REM ********** interMedia Option *************** REM spool iMediainst.log; REM connect / as sysdba REM @%ORACLE_HOME%/ord/im/admin/iminst.sql; REM spool off REM ********** End interMedia Option *************** REM ********** Visual Information Retrieval Option *************** REM spool virinst.log; REM connect / as sysdba REM @%ORACLE_HOME%/ord/vir/admin/virinst.sql; REM spool off REM ********** End Visual Information Retrieval Option *************** REM ********** JServer Option *************** spool jvminst.log; connect / as sysdba @%ORACLE_HOME%/javavm/install/initjvm.sql; spool off spool initxml.log; @%ORACLE_HOME%/oracore/admin/initxml.sql spool off spool initaqjms.log; @%ORACLE_HOME%/rdbms/admin/initaqjms.sql spool off spool initsoxx.log; @%ORACLE_HOME%/admin/initsoxx.sql spool off spool initrepapi.log; @%ORACLE_HOME%/rdbms/admin/initrepapi.sql spool off REM ********** End JServer Option *************** REM **** Redo logfiles for the second instance *************** spool clustlog.log; connect / as sysdba alter database add logfile thread 2 group 3 '\\.\clustdb_redo2_1' size 110m reuse, group 4 '\\.\clustdb_redo2_2' 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 *************** connect / as sysdba alter user system default tablespace TOOLS; alter user system temporary tablespace TEMP; REM **** Auto extend is turned off ******* alter database datafile '\\.\clustdb_cwmlite1' autoextend OFF; alter database datafile '\\.\clustdb_example1' autoextend OFF; alter database datafile '\\.\clustdb_drsys1' autoextend OFF; REM For undo_management=MANUAL, uncomment next line and comment the 2 lines REM after that. REM alter database datafile '\\.\clustdb_rbs1' autoextend OFF; alter database datafile '\\.\clustdb_undotbs1' autoextend OFF; alter database datafile '\\.\clustdb_undotbs2' autoextend OFF; alter database datafile '\\.\clustdb_system1' autoextend OFF; alter database datafile '\\.\clustdb_temp1' autoextend OFF; alter database datafile '\\.\clustdb_users1' autoextend OFF; alter database datafile '\\.\clustdb_indx1' autoextend OFF; exit;