RHEL 6.5環境Oracle 11g R2手工建庫指令碼(create database manually)

luashin發表於2016-01-21
Oracle指令碼Database
createdb.sh
******************************************************************************************************
#!/bin/sh

rm -rf /u01/app/oracle/{oradata/ORCL,admin/ORCL/{{a,dp}dump,scripts,pfile},fast_recovery_area/ORCL/archivelog}
mkdir -p /u01/app/oracle/{oradata/ORCL,admin/ORCL/{{a,dp}dump,script,pfile},fast_recovery_area/ORCL/archivelog}
export ORACLE_SID=ORCL;
export PATH=$ORACLE_HOME/bin:$PATH;
echo You should Add this entry in the /etc/oratab: ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:Y
 /u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog @createdb.sql
******************************************************************************************************

createdb.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 /u01/app/oracle/product/11.2.0/dbhome_1/bin/orapwd file=?/dbs/orapwORCL password=&&sysPassword entries=30 force=y
@CreateDB.sql
@CreateDBFiles.sql
@CreateDBCatalog.sql
@lockAccount.sql
@postDBCreation.sql
**************************************************************************************************************************************************

CreateDB.sql
******************************************************************************************************
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/cfgtoollogs/CreateDB.log
startup nomount pfile="/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora";
CREATE DATABASE "ORCL"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01.dbf') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02.dbf') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03.dbf') 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 /u01/app/oracle/cfgtoollogs/CreateDBFiles.log
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oracle/oradata/ORCL/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 /u01/app/oracle/cfgtoollogs/CreateDBCatalog.log
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/cfgtoollogs/sqlPlusHelp.log
@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off
******************************************************************************************************

lockAccount.sql
******************************************************************************************************
set echo on
spool /u01/app/oracle/cfgtoollogs/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
******************************************************************************************************

postDBCreation.sql
******************************************************************************************************
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/cfgtoollogs/postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='?/dbs/spfileORCL.ora' FROM pfile='?/dbs/initORCL.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 /u01/app/oracle/cfgtoollogs/postDBCreation.log
exit;
******************************************************************************************************

initORCL.ora
******************************************************************************************************
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=''
db_name=ORCL
 
###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/ORCL/control01.dbf","/u01/app/oracle/oradata/ORCL/control02.dbf","/u01/app/oracle/oradata/ORCL/control03.dbf")
db_recovery_file_dest=/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size=2147483648
 
###########################################
# Miscellaneous
###########################################
compatible='11.2.0'
diagnostic_dest=/u01/app/oracle/admin/ORCL
 
###########################################
# Processes and Sessions
###########################################
processes=150
sessions=115
 
###########################################
# SGA Memory
###########################################
sga_target=402653184
 
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/ORCL/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=134217728
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
******************************************************************************************************

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9034054/viewspace-1979956/，如需轉載，請註明出處，否則將追究法律責任。

相關文章