A Minimal Database Creating Script Generated By Dbca
  • init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="ENGLISH"
nls_territory="AMERICA"

###########################################
# Miscellaneous
###########################################
compatible=11.1.0.0.0
diagnostic_dest=C:\oracle
memory_target=314572800

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Cache and I/O
###########################################
db_block_size=2048

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\oracle\admin\dbcamin\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Database Identification
###########################################
db_domain=""
db_name=dbcamin

###########################################
# File Configuration
###########################################
control_files=("C:\oracle\oradata\dbcamin\control01.ctl", "C:\oracle\oradata\dbcamin\control02.ctl")
db_recovery_file_dest=C:\oracle\flash_recovery_area
db_recovery_file_dest_size=2147483648

###########################################
# Processes and Sessions
###########################################
processes=60
sessions=71
  • dbcamin.bat
mkdir C:\oracle\admin\dbcamin\adump
mkdir C:\oracle\admin\dbcamin\dpdump
mkdir C:\oracle\admin\dbcamin\pfile
mkdir C:\oracle\cfgtoollogs\dbca\dbcamin
mkdir C:\oracle\flash_recovery_area
mkdir C:\oracle\oradata\dbcamin
mkdir C:\oracle\product\11.1.0\db_1\database
set ORACLE_SID=dbcamin
set PATH=%ORACLE_HOME%\bin;%PATH%
C:\oracle\product\11.1.0\db_1\bin\oradim.exe -new -sid DBCAMIN -startmode manual -spfile 
C:\oracle\product\11.1.0\db_1\bin\oradim.exe -edit -sid DBCAMIN -startmode auto -srvcstart system 
C:\oracle\product\11.1.0\db_1\bin\sqlplus /nolog @C:\oracle\admin\dbcamin\scripts\dbcamin.sql
  • dbcamin.sql
set verify off
PROMPT specify a password for sys as parameter 1;
DEFINE sysPassword = &1
PROMPT specify a password for system as parameter 2;
DEFINE systemPassword = &2
host C:\oracle\product\11.1.0\db_1\bin\orapwd.exe file=C:\oracle\product\11.1.0\db_1\database\PWDdbcamin.ora password=&&sysPassword force=y
@C:\oracle\admin\dbcamin\scripts\CreateDB.sql
@C:\oracle\admin\dbcamin\scripts\CreateDBFiles.sql
@C:\oracle\admin\dbcamin\scripts\CreateDBCatalog.sql
@C:\oracle\admin\dbcamin\scripts\lockAccount.sql
@C:\oracle\admin\dbcamin\scripts\postDBCreation.sql
  • CreateDB.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\oracle\admin\dbcamin\scripts\CreateDB.log
startup nomount pfile="C:\oracle\admin\dbcamin\scripts\init.ora";
CREATE DATABASE "dbcamin"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\oradata\dbcamin\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'C:\oracle\oradata\dbcamin\sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\dbcamin\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\oradata\dbcamin\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('C:\oracle\oradata\dbcamin\redo01.log') SIZE 51200K,
GROUP 2 ('C:\oracle\oradata\dbcamin\redo02.log') SIZE 51200K,
GROUP 3 ('C:\oracle\oradata\dbcamin\redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off
  • CreateDBFiles.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\oracle\admin\dbcamin\scripts\CreateDBFiles.log
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE 'C:\oracle\oradata\dbcamin\users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
spool off
  • CreateDBCatalog.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\oracle\admin\dbcamin\scripts\CreateDBCatalog.log
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catalog.sql;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catblock.sql;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catproc.sql;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catoctk.sql;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@C:\oracle\product\11.1.0\db_1\sqlplus\admin\pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool C:\oracle\admin\dbcamin\scripts\sqlPlusHelp.log
@C:\oracle\product\11.1.0\db_1\sqlplus\admin\help\hlpbld.sql helpus.sql;
spool off
spool off
  • lockAccount.sql
set echo on
 
spool C:\oracle\admin\dbcamin\scripts\lockAccount.log
 
BEGIN 
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ( 
'SYS','SYSTEM') ) 
 LOOP 
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); 
  execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
 END LOOP;
END;
/
 
spool off
  • postCreation.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\oracle\admin\dbcamin\scripts\postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='C:\oracle\product\11.1.0\db_1\database\spfiledbcamin.ora' FROM pfile='C:\oracle\admin\dbcamin\scripts\init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
connect "SYS"/"&&sysPassword" as SYSDBA
spool C:\oracle\admin\dbcamin\scripts\postDBCreation.log