Oracle 手工建库

平台：CentOS5.2+Oracle 10g

1>环境变量

export ORACLE_BASE=/u01/app/oracle
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:ORACLE_HOME/rdbms/admin
export ORACLE_SID=MANUAL
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/X11R6:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export TEMP=/tmp
export TMPDIR=/tmp
export LANG=en_US

2>建立Admin目录及相关目录

保存到/u01/manual.ora

[oracle@node2 admin]$ pwd
/u01/app/oracle/admin
[oracle@node2 admin]$ mkdir MANUAL
[oracle@node2 MANUAL]$ mkdir adump
[oracle@node2 MANUAL]$ mkdir bdump
[oracle@node2 MANUAL]$ mkdir dpdump
[oracle@node2 MANUAL]$ mkdir pfile
[oracle@node2 MANUAL]$ mkdir udump

[oracle@node2 oradata]$ pwd
/u01/app/oracle/oradata
[oracle@node2 oradata]$ mkdir MANUAL

[oracle@node2 flash_recovery_area]$ pwd
/u01/app/oracle/flash_recovery_area
[oracle@node2 flash_recovery_area]$ mkdir MANUAL

[oracle@node2 dbca]$ pwd
/u01/app/oracle/product/10.2.0/db/cfgtoollogs/dbca
[oracle@node2 dbca]$ mkdir MANUAL

3>建立初始化参数，修改某些值

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Archive
###########################################
log_archive_format=%t_%s_%r.dbf
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=MANUAL
 
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/MANUAL/bdump
core_dump_dest=/u01/app/oracle/admin/MANUAL/cdump
user_dump_dest=/u01/app/oracle/admin/MANUAL/udump
 
###########################################
# File Configuration
###########################################
db_create_file_dest=/u01/app/oracle/oradata
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
control_files=("/u01/app/oracle/oradata/MANUAL/control01.ctl", 
"/u01/app/oracle/oradata/MANUAL/control02.ctl",
"/u01/app/oracle/oradata/MANUAL/control03.ctl")
 
###########################################
# Job Queues
###########################################
job_queue_processes=10
 
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
 
###########################################
# Processes and Sessions
###########################################
processes=150
 
###########################################
# SGA Memory
###########################################
sga_target=167772160
 
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/MANUAL/adump
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Shared Server
###########################################
#dispatchers="(PROTOCOL=TCP) (SERVICE=ANKXDB)"
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=16777216
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1

4>建立密码文件

[oracle@node2 dbs]$ orapwd password=ank88ank file=orapwMANUAL force=y

5>开始手工建库


[oracle@node2 dbs]$ export ORACLE_SID=MANUAL
[oracle@node2 dbs]$ sqlplus / as sysdba
SQL> startup nomount pfile='/u01/manual.ora' 
SQL>spool /u01/createdb.log
SQL>
CREATE DATABASE "MANUAL"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 200
DATAFILE '/u01/app/oracle/oradata/MANUAL/system01.dbf' SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/MANUAL/sysaux01.dbf' SIZE 200M REUSE
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE'/u01/app/oracle/oradata/MANUAL/temp01.dbf' SIZE 200M REUSE
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE'/u01/app/oracle/oradata/MANUAL/undotbs01.dbf' SIZE 100M REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/MANUAL/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/MANUAL/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/MANUAL/redo03.log') SIZE 100M
USER SYS IDENTIFIED BY "ank88ank" USER SYSTEM IDENTIFIED BY "ank88ank";
spool off

[oracle@node2 MANUAL]$ pwd
/u01/app/oracle/oradata/MANUAL
[oracle@node2 MANUAL]$ ls -la
total 844612
drwxr-xr-x 2 oracle oinstall 4096 Dec 2 20:49 .
drwxr-x--- 5 oracle oinstall 4096 Dec 2 20:31 ..
-rw-r----- 1 oracle oinstall 8339456 Dec 2 20:50 control01.ctl
-rw-r----- 1 oracle oinstall 8339456 Dec 2 20:50 control02.ctl
-rw-r----- 1 oracle oinstall 8339456 Dec 2 20:50 control03.ctl
-rw-r----- 1 oracle oinstall 104858112 Dec 2 20:49 redo01.log
-rw-r----- 1 oracle oinstall 104858112 Dec 2 20:49 redo02.log
-rw-r----- 1 oracle oinstall 104858112 Dec 2 20:49 redo03.log
-rw-r----- 1 oracle oinstall 209723392 Dec 2 20:49 sysaux01.dbf
-rw-r----- 1 oracle oinstall 209723392 Dec 2 20:49 system01.dbf
-rw-r----- 1 oracle oinstall 209723392 Dec 2 20:49 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 2 20:49 undotbs01.dbf
[oracle@node2 MANUAL]$ 


6>运行相关脚本

[oracle@node2 admin]$ pwd
/u01/app/oracle/product/10.2.0/db/rdbms/admin
SQL>spool /u01/createdbcatalog.log 
--建立数据字典视图

SQL>@/u01/app/oracle/product/10.2.0/db/rdbms/admin/catalog.sql;
--建存储过程包

SQL>@/u01/app/oracle/product/10.2.0/db/rdbms/admin/catblock.sql;
--建锁相关的几个视图 

SQL>@/u01/app/oracle/product/10.2.0/db/rdbms/admin/catproc.sql; 
--建密码工具包dbms_crypto_toolkit

SQL>@/u01/app/oracle/product/10.2.0/db/rdbms/admin/catoctk.sql;
--建工作空间管理相关对象，如dmbs_wm

SQL>@/u01/app/oracle/product/10.2.0/db/rdbms/admin/owminst.plb; 
SQL>spool off

SQL>conect system/ank88ank
@/u01/app/oracle/product/10.2.0/db/sqlplus/admin/pupbld.sql;
connect SYSTEM/oracle
SQL>@?/sqlplus/admin/pupbld.sql
SQL>@?/sqlplus/admin/help/hlpbld.sql helpus.sql

SQL>connect / as sysdba
SQL>shutdown immediate
SQL>startup


7>建立和配置EM（可选）

SQL> @?/sysman/admin/emdrep/sql/emreposcre /u01/app/oracle/product/10.2.0/db_1 SYSMAN iamwangnc TEMP ON;
SQL> alter user SYSMAN identified by "iamwangnc" account unlock;
SQL> alter user DBSNMP identified by "iamwangnc" account unlock;
SQL> host emca -config dbcontrol db -silent -DB_UNIQUE_NAME WENDING -PORT 1521
 -EM_HOME /u01/app/oracle/product/10.2.0/db_1 -LISTENER LISTENER-SERVICE_NAME WENDING.LK -SYS_PWD 
 "iamwangnc" -SID WENDING -ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1-DBSNMP_PWD "iamwangnc"
 -HOST "vmone" -LISTENER_OH /u01/app/oracle/product/10.2.0/db_1 -LOG_FILE /orahome/emConfig.log -SYSMAN_PWD
 "iamwangnc";