合 Oracle 10g和11g手动建库过程
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 10G和11g手动建库（重点）
② 各种组件安装
③ 创建Sample Schemas数据
④ 手动建库中常用脚本的解释
⑤ sqlplus中的帮助命令
本文简介
上一篇（http://blog.itpub.net/26736162/viewspace-2121930/）中最后差了手动建库的部分，今天把这个部分的内容加上。本来手动建库很早就学习过了，只是一直没有时间来整理发布，今天就趁这个机会正好整理一下，分享给大家。
小麦苗学习手动建库的动力源于之前帮网友采用dbca建库的时候报错，由于java环境的问题，dbca一直没有办法使用，无论界面还是静默都用到java，折腾了2个小时还是把java没有修复好，dbca不能用，最后想到了create database手动建库，虽然工作中很少采用但还是有一定的用途的。
手动建库简介
有时候因为环境的缘故不能使用图形界面或者不能使用dbca的静默方式来创建一个新库，那么这个时候可以考虑使用CREATE DATABASE SQL命令行来创建数据库，该方式是一种手动建库方式，使用此种命令行手动创建数据库的优点是：可以用脚本来创建数据库。 另外OCM的考试中要求我们用CREATE DATABASE来创建数据库。当然在使用脚本创建数据库时，在建立数据字典视图和安装标准的PL/SQL程序包时，必须先建立一个可以操作的数据库。
手动建库基本步骤
官方文档的步骤：
Step 1: Specify an Instance Identifier (SID)
Step 2: Ensure That the Required Environment Variables Are Set
Step 3: Choose a Database Administrator Authentication Method
Step 4: Create the Initialization Parameter File
Step 5: (Windows Only) Create an Instance
Step 6: Connect to the Instance
Step 7: Create a Server Parameter File
Step 9: Issue the CREATE DATABASE Statement
Step 10: Create Additional Tablespaces
Step 11: Run Scripts to Build Data Dictionary Views
Step 12: (Optional) Run Scripts to Install Additional Options
Step 13: Back Up the Database.
Step 14: (Optional) Enable Automatic Instance Startup
具体可以参考： http://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm
我的blog：http://blog.itpub.net/26736162/viewspace-2098211/
直接给出脚本
我们直接给出手动建库用到的脚本，至于过程小麦苗就不演示了。
11G
ORACLE用户执行 数据文件在文件系统 单实例DB
------------ 1、 确保环境变量正确
export ORACLE_SID=lhrdb
env|grep ORACLE
ORACLE_SID=lhrdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
------------ 2、 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwlhrdb password=lhr force=y
------------ 3、 创建初始化参数文件和相关路径
$ORACLE_HOME/dbs/initlhrdb.ora
db_name='lhrdb'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/lhrdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='/u01/app/oracle/oradata'
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=MYNEWDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/oradata/lhrdb/control01.ctl','/u01/app/oracle/flash_recovery_area/lhrdb/control02.ctl'
compatible ='11.2.0'
mkdir -p /u01/app/oracle/admin/lhrdb/adump
mkdir -p /u01/app/oracle/flash_recovery_area/lhrdb/
mkdir -p /u01/app/oracle/oradata/lhrdb/
------------ 4、 创建spfile，启动到nomount状态
sqlplus / as sysdba
create spfile from pfile;
startup nomount
! ps -ef|grep lhrdb
------------ 5、 创建DB
CREATE DATABASE lhrdb
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('/u01/app/oracle/oradata/lhrdb/redo01a.log','/u01/app/oracle/oradata/lhrdb/redo01b.log') SIZE 50M BLOCKSIZE 512,
GROUP 2('/u01/app/oracle/oradata/lhrdb/redo02a.log','/u01/app/oracle/oradata/lhrdb/redo02b.log') SIZE 50M blocksize 512,
GROUP 3('/u01/app/oracle/oradata/lhrdb/redo03a.log','/u01/app/oracle/oradata/lhrdb/redo03b.log') SIZE 50M BLOCKSIZE 512
DATAFILE '/u01/app/oracle/oradata/lhrdb/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '/u01/app/oracle/oradata/lhrdb/sysaux01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/lhrdb/temp01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/lhrdb/undotbs01.dbf'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/lhrdb/users01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
/
SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off
------ 单实例数据库添加到srvctl中
srvctl add database -d lhrdb -c single -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilelhrdb.ora' -r primary -n lhrdb -x ZFXDESKDB2
srvctl config database -d lhrdb -a
srvctl status database -d lhrdb
srvctl start database -d lhrdb
crsctl stat res -t
------ drop database
alter database close;
alter system enable restricted session;
drop database;
ORACLE用户执行 数据文件在ASM中 单实例DB
------------ 1、 确保环境变量正确
export ORACLE_SID=lhrasm
env|grep ORACLE
ORACLE_SID=lhrasm
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
------------ 2、 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwlhrasm password=lhr force=y
------------ 3、 创建初始化参数文件和相关路径
$ORACLE_HOME/dbs/initlhrasm.ora
db_name='lhrasm'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/lhrasm/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='+DATA'
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=MYNEWDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '+DATA/lhrasm/controlfile/control01.ctl','+FRA/lhrasm/controlfile/control02.ctl'
compatible ='11.2.0'
mkdir -p /u01/app/oracle/admin/lhrasm/adump
mkdir -p /u01/app/oracle/flash_recovery_area/lhrasm/
mkdir -p /u01/app/oracle/oradata/lhrasm/
------------ 4、 创建spfile，启动到nomount状态
sqlplus / as sysdba
create spfile from pfile;
startup nomount
! ps -ef|grep lhrasm
------------ 5、 创建DB
CREATE DATABASE lhrasm
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('+DATA','+DATA') SIZE 50M BLOCKSIZE 512,
GROUP 2('+DATA','+DATA') SIZE 50M blocksize 512,
GROUP 3('+DATA','+DATA') SIZE 50M BLOCKSIZE 512
DATAFILE '+DATA' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '+DATA' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SET DEFAULT bigfile TABLESPACE
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
;
/* --------- BIGFILE
CREATE DATABASE lhrasm
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
EXTENT MANAGEMENT LOCAL
SET DEFAULT bigfile TABLESPACE
DEFAULT TEMPORARY TABLESPACE TEMP
UNDO TABLESPACE UNDOTBS1
DEFAULT TABLESPACE USERS
;
*/
SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off
------ 单实例数据库添加到srvctl中
srvctl add database -d lhrasm -c single -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilelhrasm.ora' -r primary -n lhrasm -x ZFXDESKDB2
srvctl config database -d lhrasm -a
srvctl status database -d lhrasm
srvctl start database -d lhrasm
crsctl stat res -t
------ drop database
alter database close;
alter system enable restricted session;
drop database;
11G rac asm
---- 思路：先创建单实例DB然后再转换为RAC DB
export ORACLE_SID=raclhr1
env|grep ORACLE
ORACLE_SID=raclhr
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
------------ 2、 2个节点都 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwraclhr1 password=lhr force=y
orapwd file=$ORACLE_HOME/dbs/orapwraclhr2 password=lhr force=y
------------ 3、 创建初始化参数文件和相关路径
--- 节点一配置：
$ORACLE_HOME/dbs/initraclhr1.ora
*.db_name='raclhr'
*.memory_target=400437056
*.processes = 150
*.open_cursors=300
*.audit_file_dest='/u01/app/oracle/admin/raclhr/adump'
*.audit_trail ='db'
*.db_block_size=8192
*.db_domain=''
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYNEWDBXDB)'
*.control_files = '+DATA/raclhr/controlfile/control01.ctl','+FRA/raclhr/controlfile/control02.ctl'
*.remote_login_passwordfile='EXCLUSIVE'
---2个节点都创建路径
mkdir -p /u01/app/oracle/admin/raclhr/adump
mkdir -p /u01/app/oracle/flash_recovery_area/raclhr/
mkdir -p /u01/app/oracle/oradata/raclhr/
--- 节点一执行
su - grid
asmcmd
cd +DATA
mkdir raclhr
cd raclhr
mkdir PARAMETERFILE
su - oracle
sqlplus / as sysdba
create spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora' from pfile;
---2个节点都执行 创建初始化参数文件执行ASM磁盘里的SPFILE
cp $ORACLE_HOME/dbs/initraclhr1.ora $ORACLE_HOME/dbs/initraclhr1.ora_bk
echo "spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora'" > $ORACLE_HOME/dbs/initraclhr1.ora
echo "spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora'" > $ORACLE_HOME/dbs/initraclhr2.ora
------------ 4、节点一启动到nomount状态
startup nomount
! ps -ef|grep raclhr
show parameter spfile
------------ 5、 创建DB
CREATE DATABASE raclhr
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('+DATA','+DATA') SIZE 50M BLOCKSIZE 512,
GROUP 2('+DATA','+DATA') SIZE 50M blocksize 512,
GROUP 3('+DATA','+DATA') SIZE 50M BLOCKSIZE 512
DATAFILE '+DATA' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '+DATA' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SET DEFAULT bigfile TABLESPACE
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
;
/* --------- BIGFILE
