创建oracle实例9i

最新推荐文章于 2024-11-16 12:08:16 发布

lipengbo10054444

最新推荐文章于 2024-11-16 12:08:16 发布

阅读量792

点赞数

文章标签： oracle character database system archive list

本文链接：https://blog.csdn.net/lipengbo10054444/article/details/5725212

版权

#!/bin/ksh

# shell script for setup 'zxin' database
# date: 2001/06/20

prepdbfilename_fs()
{
    oraf_root=/zxindata
    if [ `uname` = "Linux" ]
    then
        ora_arch=/zxinbak/arch
    else
        ora_arch=$oraf_root/zxinbak/arch
    fi

    _controlfile01=$oraf_root/oracle/control/control01.ctl
    _controlfile02=$oraf_root/oracle/control/control02.ctl
    _controlfile03=$oraf_root/oracle/control/control03.ctl

    _system_tbf=$oraf_root/oracle/system/system.dbf      # size = 512M
    _temp_tbf=$oraf_root/oracle/system/temp.dbf
    _undo_tbf=$oraf_root/oracle/system/undo.dbf

    if [ `uname` = "AIX" ]
    then
    _rredolog01=/dev/roraredo01
    _rredolog02=/dev/roraredo02
    _rredolog03=/dev/roraredo03
    elif [ `uname` = "HP-UX" ]
    then
    _rredolog01=/dev/zxinvg/roraredo01
    _rredolog02=/dev/zxinvg/roraredo02
    _rredolog03=/dev/zxinvg/roraredo03
    else
    _rredolog01=/dev/roraredo01
    _rredolog02=/dev/roraredo02
    _rredolog03=/dev/roraredo03
    fi

    _redolog01=$oraf_root/oracle/redolog/redo01.log       # size = 250M
    _redolog02=$oraf_root/oracle/redolog/redo02.log
    _redolog03=$oraf_root/oracle/redolog/redo03.log

    [ -d $oraf_root/oracle/redolog ] || mkdir $oraf_root/oracle/redolog
    if [ -c $_rredolog01 ]
    then
        chown oracle:dba $_rredolog01
    [ -h $_redolog01 ] || ln -s $_rredolog01 $_redolog01
    else
    _redolog01=$oraf_root/oracle/redolog/redo01.dbf
    fi
    if [ -c $_rredolog02 ]
    then
    chown oracle:dba $_rredolog02
    [ -h $_redolog02 ] || ln -s $_rredolog02 $_redolog02
    else
    _redolog02=$oraf_root/oracle/redolog/redo02.dbf
    fi
    if [ -c $_rredolog03 ]
    then
    chown oracle:dba $_rredolog03
    [ -h $_redolog03 ] || ln -s $_rredolog03 $_redolog03
    else
    _redolog03=$oraf_root/oracle/redolog/redo03.dbf
    fi

    [ ! -d ${ORACLE_BASE}/zxin ] && mkdir ${ORACLE_BASE}/zxin
    for f in $oraf_root $oraf_root/oracle $ora_arch
    do
        [ -d $f ] || mkdir -p $f
    done

chmod -R 775 $ora_arch

    for f in $_controlfile01 $_controlfile02 $_controlfile03 /
        $_system_tbf $_undo_tbf
    do
        f=`dirname $f`
        [ ! -d $f -a ! -z $f ] && mkdir $f
    done

}

objsize()
{
    _the_size=0
    case $1 in
        control)_the_size=128M
            ;;
        system)_the_size=512M
            ;;
        temp)_the_size=512M
            ;;
        undo)_the_size=2000M
            ;;
        redo|redolog)_the_size=250M
            ;;
    esac

echo $_the_size

}

memsize()
{
    unset LANG
    _memsize_=0
    case `uname` in
        AIX)
            for _memb_ in `lsdev -Ccmemory|awk '$NF~/Memory/&&$2~/Available/{print $1}'`
            do
                _memb_size=`lsattr -El $_memb_ -a size|awk '{print $2}'`
                _memsize_=`expr $_memsize_ + $_memb_size`
            done
            ;;
        HP-UX)
            hpux_release=`uname -r |awk -F"." '{print $3}'`
            if [ $hpux_release -lt 23 ]
            then
                _memsize_=`echo "memory_installed_in_machine/D" | adb -k /stand/vmunix /dev/kmem |tail -1 | awk '$2 > 0 { print $2 / 256 }'`
            else
                _memsize_=`echo "phys_mem_pages/2d" |adb /stand/vmunix /dev/kmem |tail -1 | awk '$2 > 0 { print $2 / 256 }'`
                _memsize_=`echo "$_memsize_" |awk -F"." '{print $1}'`
            fi
            ;;
        Linux)
            _memsize_=`cat /proc/meminfo |grep MemTotal |awk '{print $2}'`
            _memsize_=`expr $_memsize_ / 1024`
            ;;
        SunOS)
            _memsize_=`prtconf |grep "Memory size"|awk '{print $3}'`
            ;;
        *)
            echo "Not supported operating system, exit!"
            exit
            ;;
    esac

    if [ -z "$_memsize_" ]
    then
        echo "failed to get memory size, exit!"
        exit
    elif [ "$_memsize_" = "0" ]
    then
        echo "failed to get memory size, exit!"
        exit
    fi
    return $_memsize_
}

calcpunum()
{
if [ `uname` = "Linux" ]
then
         cpunum=`cat /proc/cpuinfo |grep processor |wc |awk '{print $1}'`
elif [ `uname` = "HP-UX" ]
then
         cpunum=`ioscan -k |grep -n processor |wc -l`
elif [ `uname` = "AIX" ]
then
         cpunum=`unset LANG;lsdev -C |grep proc |grep Available|wc|awk '{print $1}'`
elif [ `uname` = "SunOS" ]
then
         cpunum=`mpstat |grep -v CPU |wc |awk '{print $1}'`
fi
[ -z "$cpunum" ] && cpunum=1
[ $cpunum -ge 8 ] && cpunum=8
}

## use this to replace "expr" in 32-bit OS -- when the result is more than 2^32
calculate()
{
bc << @@
${1}
@@
return
}

prepdbinitfile()
{
[ -d ${ORACLE_BASE}/zxin/define ] || mkdir ${ORACLE_BASE}/zxin/define

    calcpunum
    memsize;
    sga_total=`calculate $_memsize_*85/100/2`
    if [ `uname` = "Linux" ]
    then
        [ ${sga_total} -gt 1600 ] && sga_total=1600
    else
        [ ${sga_total} -gt 2000 ] && sga_total=2000
    fi
    pga_aggregate_target=`calculate ${sga_total}*15/85`

    ## total memory (sga+pga) = 1/2 physical memory
    ## pga:sga = 15%:85%
    ## db_cache:shared_pool:large_pool:java_pool = 7:2:0.5:0.5

    db_cache_size=`calculate ${sga_total}*7/10`
    shared_pool_size=`calculate ${sga_total}*2/10`
    large_pool_size=`calculate ${sga_total}/20`
    [ $large_pool_size -gt 64 ] && large_pool_size=64
    java_pool_size=$large_pool_size

cat << EOF > ${ORACLE_BASE}/zxin/define/initzxin.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# Database Identification
###########################################
db_domain=""
db_name=zxin

###########################################
# Instance Identification
###########################################
instance_name=zxin

###########################################
# File Configuration
###########################################
control_files=("${_controlfile01}", "${_controlfile02}", "${_controlfile03}")

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

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=${db_cache_size}M
db_file_multiblock_read_count=16

###########################################
# Pools
###########################################
java_pool_size=${java_pool_size}M
large_pool_size=${large_pool_size}M
shared_pool_size=${shared_pool_size}M

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=${pga_aggregate_target}M
sort_area_size=524288

###########################################
# Processes and Sessions
###########################################
processes=150
db_writer_processes = $cpunum

###########################################
# Archive
###########################################
log_archive_dest_1='location=${ora_arch} reopen=30'
log_archive_format=%t_%s.dbf
log_archive_start=false

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=1800
undo_tablespace=UNDOTBS1

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=${ORACLE_BASE}/admin/zxin/bdump
core_dump_dest=${ORACLE_BASE}/admin/zxin/cdump
timed_statistics=TRUE
user_dump_dest=${ORACLE_BASE}/admin/zxin/udump
max_dump_file_size=100M

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=NONE
os_authent_prefix = ""

###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0

UTL_FILE_DIR = "$HOME"

EOF
[ -f ${ORACLE_BASE}/zxin/define/initzxin.ora ] && rm ${ORACLE_HOME}/dbs/initzxin.ora >/dev/null 2>&1
ln -s ${ORACLE_BASE}/zxin/define/initzxin.ora ${ORACLE_HOME}/dbs/initzxin.ora
}

choose_character_set()
{
    echo "======================================================"
    echo "Now two types of database character set are supported:"
    echo "1: ZHS16GBK (commonly used to support Chinese Language)"
    echo "2: UTF8 (commonly used to support Non-Chinese Language)"
    echo "9: other character set(you can customize character set)"
    echo "======================================================"
    echo "Which one do you prefer, input your choice: [1]"

    while read user_answer
    do
        case $user_answer in
            1|"") choosed_character_set=ZHS16GBK
                break
                ;;
            2) choosed_character_set=UTF8
                break
                ;;
            9) echo "please input your database character set:"
                read charset
                choosed_character_set=$charset
                break
                ;;
            *) echo "Invalid arguments, exit!"
                exit
                ;;
        esac
    done
    echo "You have choosed ${choosed_character_set} as database character set, continue(y/n)?"
    read user_answer
    case $user_answer in
        y|Y) echo "begin to create database..."
             ;;
        *)   echo "exit the installation!"
             exit
             ;;
    esac
}

#
# step 0

if [ $LOGNAME != "oracle" -o "X$ORACLE_HOME" = "x" -o "X$ORACLE_BASE" = "x" ]
then
    echo "MUST as user/group 'oracle/dba' and set ORACLE_HOME, ORACLE_BASE !"
    exit 1
fi
if [ ! -d $ORACLE_HOME ]
then
    echo "ORACLE_HOME=$ORACLE_HOME does not exist !"
    exit 1
fi
if [ ! -d $ORACLE_BASE ]
then
    echo "ORACLE_BASE=$ORACLE_BASE does not exist !"
    exit 1
fi
if [ ! -d $ORACLE_BASE/zxin ]
then
    mkdir $ORACLE_BASE/zxin
fi

##2007.06.22 choose database character set
choose_character_set

for f in admin admin/zxin admin/zxin/adhoc admin/zxin/arch admin/zxin/bdump/
admin/zxin/cdump admin/zxin/create admin/zxin/exp admin/zxin/pfile admin/zxin/udump
do
[ -d ${ORACLE_BASE}/$f ] || mkdir ${ORACLE_BASE}/$f
done

chmod -R 775 ${ORACLE_BASE}

prepdbfilename_fs;
x=`hostname`
_svc_name_=${x%[0-9]}
_svc_name_=${_svc_name_%[_]}

if [ `uname` = "SunOS" ]
then
    UNIXGREP=/usr/xpg4/bin/grep
    UNIXAWK=/usr/xpg4/bin/awk
else
    UNIXGREP=grep
    UNIXAWK=awk
fi

# step 1
g_logname=`pwd`/__crdblog
ORACLE_SID=${ORACLE_SID:=zxin}

prepdbinitfile;

[ -f $ORACLE_HOME/network/admin/tnsnames.ora ] && cp $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.zxinbak
cat << EOF > $ORACLE_HOME/network/admin/tnsnames.ora
# Generated by ZTE zxin10 product
# for oracle - tnsnames.ora
# date: `date`

ZXIN =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = $_svc_name_)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zxin)
    )
)

zx`$UNIXAWK -v y=${_svc_name_} '$1!~/^#/{if($2==y) print $1}' /etc/hosts|sed -e 's//./_/g'`=
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = $_svc_name_)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER       = DEDICATED)
      (SERVICE_NAME = zxin)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

EOF

[ -f $ORACLE_HOME/network/admin/listener.ora ] && cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.zxinbak
cat << EOF > $ORACLE_HOME/network/admin/listener.ora
# Generated by ZTE zxin10 product
# for oracle - listener.ora
# date: `date`

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = $_svc_name_)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = $_svc_name_)(PORT = 1522))
      )
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = ${ORACLE_HOME})
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = zxin)
      (ORACLE_HOME = ${ORACLE_HOME})
      (SID_NAME = zxin)
    )
)

EOF

# step 2
$ORACLE_HOME/bin/sqlplus /nolog << EOF > ${g_logname}
spool $ORACLE_BASE/zxin/define/crdb1.log
connect / as sysdba
shutdown abort

connect / as sysdba
startup nomount pfile = $ORACLE_BASE/zxin/define/initzxin.ora
CREATE DATABASE "zxin"
CONTROLFILE REUSE
MAXDATAFILES 254
MAXINSTANCES 8
MAXLOGHISTORY 100
MAXLOGMEMBERS 3
MAXLOGFILES 32
logfile '${_redolog01}' SIZE `objsize redolog` reuse,
    '${_redolog02}' SIZE `objsize redolog` reuse,
    '${_redolog03}' SIZE `objsize redolog` reuse
character set ${choosed_character_set}
DATAFILE '${_system_tbf}' SIZE `objsize system`
REUSE AUTOEXTEND OFF
extent management local
default temporary tablespace TEMP
   tempfile '${_temp_tbf}' size `objsize temp` reuse
   autoextend off
   extent management local uniform size 256K
undo tablespace UNDOTBS1
   datafile '${_undo_tbf}' size `objsize undo` reuse
   autoextend off
noarchivelog;
disconnect
spool off
exit

EOF

if grep "ORA-[0-9][0-9]*" ${g_logname}
then
echo "maybe error occured, exit."
return
fi

# step 3

$ORACLE_HOME/bin/sqlplus /nolog << EOF >> ${g_logname}
spool $ORACLE_BASE/zxin/define/crdb2.log
connect / as sysdba
@ $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.plb
disconnect
spool off
exit

EOF

$ORACLE_HOME/bin/sqlplus /nolog << EOF >> ${g_logname}
spool $ORACLE_BASE/zxin/define/crdb3.log
connect system/manager
@ $ORACLE_HOME/sqlplus/admin/pupbld.sql
disconnect
spool off
exit

EOF

[ $HOME/oratool.sql ] &&
$ORACLE_HOME/bin/sqlplus /nolog << EOF >> ${g_logname}
spool $ORACLE_BASE/zxin/define/crdb4.log
connect / as sysdba
@ $HOME/oratool.sql
disconnect
spool off
exit
EOF

if [ "${choosed_character_set}" != "ZHS16GBK" ]
then
echo "Because you have choosen another charset, so remember to change NLS_LANG in oracle/zxin10's profile!"
fi
echo "Creating database finished."