（一）在Unix下Oracle 11g 建库脚本 bsb

zjycy 2017-03-09

展开全文

全部分类数据库大数据开发技术操作系统网络技术信息化 IT人生

（一）在Unix下Oracle 11g 建库脚本 bsb_oracle_create_db.sh

750阅读　0评论2015-02-07　fjzcau
分类：Oracle

<>
<>

使用该建库脚本前,请理解脚本内容.

echo '#==========================Create DB===============================#'
date
echo '#==========================Create DB===============================#'
#------------------------------------------------------#
# 手工建库
# 以oracle用户执行该脚本
#------------------------------------------------------#
ORACLE_SID=bjtc
export ORACLE_UNQNAME=bjtc
export ORACLE_HOSTNAME=BJTC
ORACLE_BASE=/oracle/product
ORACLE_HOME=$ORACLE_BASE/11.2.3
#--修改.profile
cd ~
cat >> ~/.profile EOF
#-----------------------------------------#
# add for oracle
export ORACLE_SID=bjtc
export ORACLE_UNQNAME=bjtc
export ORACLE_HOSTNAME=BJTC
export ORACLE_BASE=/oracle/product
export ORACLE_HOME=$ORACLE_BASE/11.2.3
export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT='yy-mm-dd HH24:MI:SS'
export NLS_LANG=American_America.ZHS16GBK
#-----------------------------------------#
set -o vi
alias ll='ls -l'
EOF
. .profile
#------------------------------------------------------#
#--生成建库配置文件 dbca.rsp
#------------------------------------------------------#
cat > /oracle/dbca.rsp EOF
[GENERAL]
RESPONSEFILE_VERSION = '11.2.0'
OPERATION_TYPE = 'createDatabase'
[CREATEDATABASE]
GDBNAME = '$ORACLE_SID'
SID = '$ORACLE_SID'
TEMPLATENAME = 'General_Purpose.dbc'
SYSPASSWORD = 'Bsbora12#'
SYSTEMPASSWORD = 'Bsbora12#'
EMCONFIGURATION = 'NONE'
SYSMANPASSWORD = 'Bsbora12#'
DBSNMPPASSWORD = 'Bsbora12#'
DATAFILEDESTINATION = /oradata
#STORAGETYPE=FS
CHARACTERSET = 'ZHS16GBK'
NATIONALCHARACTERSET= 'AL16UTF16'
LISTENERS = 'listener'
MEMORYPERCENTAGE = '40'
DATABASETYPE = 'MULTIPURPOSE'
AUTOMATICMEMORYMANAGEMENT = 'TRUE'
#TOTALMEMORY = '800'
EOF
cd $ORACLE_HOME/bin
./dbca -silent -createdatabase -responseFile /oracle/dbca.rsp
sleep 5
#------------------------------------------------------#
# 手工建库后需要执行的语句
#------------------------------------------------------#
echo 'col file_name for a50' >> $ORACLE_HOME/sqlplus/admin/glogin.sql
echo 'col member for a50' >> $ORACLE_HOME/sqlplus/admin/glogin.sql
echo 'set lines 200' >> $ORACLE_HOME/sqlplus/admin/glogin.sql
echo 'set pages 100' >> $ORACLE_HOME/sqlplus/admin/glogin.sql
mkdir /oraredo1/$ORACLE_SID
mkdir /oraredo2/$ORACLE_SID
sqlplus / as sysdba EOF
shutdown immediate;
EOF
#--spfile放在存储
echo 'spfile=/oradata/$ORACLE_SID/spfile$ORACLE_SID.ora' > $ORACLE_HOME/dbs/init$ORACLE_SID.ora
mv $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora /oradata/$ORACLE_SID
sqlplus / as sysdba EOF
startup mount;
alter database archivelog;
alter system set log_archive_dest_1='location=/archivelog';
alter database open;
alter profile default limit password_life_time unlimited;
alter system set processes=1500 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set db_recovery_file_dest='';
alter database datafile 1 resize 4096m;
alter database datafile 2 resize 4096m;
alter database datafile 3 resize 20480m;
alter database datafile 4 resize 1024m;
alter database tempfile 1 resize 20480m;
alter database add logfile group 4 ( '/oraredo1/$ORACLE_SID/redo041.log','/oraredo2/$ORACLE_SID/redo042.log') size 256M;
alter database add logfile group 5 ( '/oraredo1/$ORACLE_SID/redo051.log','/oraredo2/$ORACLE_SID/redo052.log') size 256M;
alter database add logfile group 6 ( '/oraredo1/$ORACLE_SID/redo061.log','/oraredo2/$ORACLE_SID/redo062.log') size 256M;
alter database add logfile group 7 ( '/oraredo1/$ORACLE_SID/redo071.log','/oraredo2/$ORACLE_SID/redo072.log') size 256M;
alter database add logfile group 8 ( '/oraredo1/$ORACLE_SID/redo081.log','/oraredo2/$ORACLE_SID/redo082.log') size 256M;
alter database add logfile group 9 ( '/oraredo1/$ORACLE_SID/redo091.log','/oraredo2/$ORACLE_SID/redo092.log') size 256M;
alter database add logfile group 10 ( '/oraredo1/$ORACLE_SID/redo1001.log','/oraredo2/$ORACLE_SID/redo1002.log') size 256M;
alter system switch logfile;
!sleep 3
alter system switch logfile;
!sleep 3
alter system switch logfile;
!sleep 3
alter system switch logfile;
!sleep 3
alter system switch logfile;
!sleep 3
alter system switch logfile;
!sleep 3
alter system switch logfile;
select group#,status,BYTES from v\$log;
select group#,status,member from v\$logfile;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database add logfile group 1 ( '/oraredo1/$ORACLE_SID/redo011.log','/oraredo2/$ORACLE_SID/redo012.log') size 256M;
alter database add logfile group 2 ( '/oraredo1/$ORACLE_SID/redo021.log','/oraredo2/$ORACLE_SID/redo022.log') size 256M;
alter database add logfile group 3 ( '/oraredo1/$ORACLE_SID/redo031.log','/oraredo2/$ORACLE_SID/redo032.log') size 256M;
alter system set control_files='/oradata/$ORACLE_SID/control01.ctl','/oradata/$ORACLE_SID/control02.ctl' scope=spfile;
shutdown immediate;
! cp /oradata/$ORACLE_SID/control01.ctl /oradata/$ORACLE_SID/control02.ctl
startup;
alter user sysman account unlock;
EOF
echo '#==========================End Create DB===============================#'
date
echo '#==========================End Create DB===============================#'
cat > $ORACLE_HOME/network/admin/listener.ora EOF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/11.2.3)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = $ORACLE_SID )
(ORACLE_HOME = /oracle/product/11.2.3)
(GLOBAL_DBNAME = $ORACLE_UNQNAME )
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = $ORACLE_HOSTNAME )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
EOF