oracle11g手工建库完整过程

操作系统版本：
[root@vmac1 ~]# lsb_release -a
LSB Version: :core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.3 (Santiago)
Release: 6.3
Codename: Santiago

数据库版本：
SQL*Plus: Release 11.2.0.3.0 Production



1. 设置环境变量
[oracle@vmac1 2015_02_28]$ cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBARY_PATH=$ORACLE_HOME:lib/lib:/usr/lib:
[oracle@vmac1 2015_02_28]$

2. 建立需要的目录
[oracle@vmac1 oracle]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@vmac1 oracle]$ mkdir -p /u01/app/oradata
[oracle@vmac1 oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area

3. 建初始化参数文件
[oracle@vmac1 dbs]$ ls /u01/app/oracle/product/11.2.0/db_1/dbs
init.ora
[oracle@vmac1 dbs]$ cat init.ora | grep -v ^$ | grep -v ^#
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=''
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
[oracle@vmac1 dbs]$ pwd
[oracle@vmac1 dbs] cat init.ora | grep -v ^$ | grep -v ^# > initorcl.ora

[root@vmac1 dbs]# more initorcl.ora #######对照上面修改参数
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/u01/app/oradata/ora_control1,/u01/app/oradata/ora_control2,/u01/app/oradata/ora_control3)
compatible ='11.2.0'

undo_management=AUTO
#background_dump_dest='/u01/app/oracle/admin/orcl/bdump' ###和10g相比 统一为diagnostic_dest
#core_dump_dest='/u01/app/oracle/admin/orcl/cdump' ###和10g相比 统一为diagnostic_dest
#user_dump_dest='/u01/app/oracle/admin/orcl/udump' ###和10g相比 统一为diagnostic_dest
#db_create_file_dest='/opt/oradata/orcl'

4. 建立密码文件
[oracle@vmac1 bin]$ orapwd file=$ORACLE_HOME/dbs/oraorcl password=oracle force=y

5. 建spfile后启动实例并开始

建库
$ sqlplus '/as sysdba'
SQL> create spfile from pfile; （会自动读取pfile：$ORACLE_HOME/dbs/init$ORACLE_SID.ora）

[root@vmac1 dbs]#
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
SQL>
SQL>
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
SQL>

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes

SQL> create database orcl CONTROLFILE REUSE
MAXINSTANCES 8 MAXDATAFILES 2000
MAXLOGHISTORY 2 MAXLOGFILES 64 MAXLOGMEMBERS 5
character set ZHS16GBK national character set AL16UTF16
logfile group 1 ('/u01/app/oradata/orcl/redo1.log') size 512M reuse,
group 2 ('/u01/app/oradata/orcl/redo2.log') size 512M reuse,
group 3 ('/u01/app/oradata/orcl/redo3.log') size 512M reuse
datafile '/u01/app/oradata/orcl/system.dbf' size 1024M reuse autoextend on next 100M maxsize unlimited extent management local
sysaux datafile '/u01/app/oradata/orcl/sysaux.dbf' size 1024M autoextend on next 100M maxsize unlimited
default temporary tablespace temp tempfile '/u01/app/oradata/orcl/temp01.dbf' size 100M reuse autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1.dbf' size 100M reuse autoextend on next 10M maxsize unlimited
USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle";

Database created.


--将以上脚本放在oracle目录下，然后执行
$cd /u01/app/oracle/
$vi createDB.sh
SQL> @/u01/app/oracle/createDB.sh

6 运行数据字典脚本，其中catalog和catproc是必需的，其它可选： (运行时间比较长)

SQL>
spool /u01/app/oracle/oradata/cat_testdb.log
@?/rdbms/admin/catalog.sql ;
@?/rdbms/admin/catproc.sql ;
@?/rdbms/admin/catblock.sql ; ）
@?/rdbms/admin/catoctk.sql ;
@?/rdbms/admin/owminst.plb ;
spool off

SQL> spool /opt/oradata/cat_testdb.log
SQL> @?/rdbms/admin/catalog.sql ; （建数据字典视图）
SQL> @?/rdbms/admin/catproc.sql ; （建存储过程包）
SQL> @?/rdbms/admin/catblock.sql ; （建锁相关的几个视图）
SQL> @?/rdbms/admin/catoctk.sql ; （建密码工具包dbms_crypto_toolkit）
SQL> @?/rdbms/admin/owminst.plb ; （建工作空间管理相关对象，如dmbs_wm）
SQL> spool off

7. 新建sqlplus属性和帮助、USERS表空间
SQL> connect system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
SQL> @?

/sqlplus/admin/help/hlpbld.sql helpus.sql
SQL> connect /as sysdba
SQL> CREATE TABLESPACE USERS LOGGING DATAFILE '/u01/app/oradata/orcl/users01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> ALTER DATABASE DEFAULT TABLESPACE USERS;

8. 最后修改为归档模式并重启(省略此步骤）
SQL> shutdown immediate;
SQL> connect /as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

9.重新编译所有失效过程：
SQL> execute utl_recomp.recomp_serial();

10、建立和配置EM（省略此步骤）：
SQL> @?/sysman/admin/emdrep/sql/emreposcre /oracle/product/10.2 SYSMAN oracle TEMP ON;
SQL> alter user SYSMAN identified by "sysman" account unlock;
SQL> alter user DBSNMP identified by "dbsnmp" account unlock;
SQL> host emca -config dbcontrol db -silent -DB_UNIQUE_NAME ypyhtb -PORT 1521 -EM_HOME /oracle/product/10.2/bin -LISTENER LISTENER -SERVICE_NAME ypythb.LK -SYS_PWD "oracle" -SID ypythb -ORACLE_HOME /oracle/product/10.2 -DBSNMP_PWD "dbsnmp" -HOST "172.19.201.184" -LISTENER_OH /oracle/product/10.2 -LOG_FILE /oradata/ypythb/emConfig.log -SYSMAN_PWD "sysman";

到此建库










SQL>