已索引
文档
Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Creating a Database with DBCA / Creating a Database with the CREATE DATABASE Statement
简略步骤说明
Step 1: Specify an Instance Identifier (SID)
Step 2: Ensure That the Required Environment Variables Are Set (ORACLE_BASE, ORACLE_HOME, PATH)
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 // create spfile from pfile;
Step 8: Start the Instance to nomount
Step 9: Issue the CREATE DATABASE Statement
注意undo表空间的名称要与参数文件中的一致。所有提及到的目录都要事先创建好。
可以在另一个终端中动态观察alert日志：
$ tailf /u01/app/oracle/diag/rdbms/prod/prod/trace/alert_prod.log
另外，系统会自动在数据库创建完成后自动打开数据库
Step 10: Create Additional Tablespaces
Step 11: Run Scripts to Build Data Dictionary Views
注意：第3个脚本是以system用户身份运行的！！！！
不运行第3个脚本的话，新建的用户登陆时会异常：
SQL> create user xuw identified by xuw;
SQL> grant dba to xuw;
SQL> connect xuw/xuw;
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM Connected.
至此，手工建库完成。
详细演示过程
Step 1: Specify an Instance Identifier (SID)
DB 设计：ORACLE_SID=PROD2，db_name=PROD2, db_domain=oracle.com
vi ~/profile_PROD2
-- Add the next lines
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=PROD2
export ORACLE_UNQNAME=PROD2
export PATH=$ORACLE_HOME/bin:$PATH
-- Activate the variables
. ~/profile_PROD2
echo $ORACLE_SID
Step 2: Ensure That the Required Environment Variables Are Set
确认ORACLE_HOME和ORACLE_SID变量是否根据实际情况正确设置。
Step 3: Choose a Database Administrator Authentication Method
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD2 password=oracle entries=5
Step 4: Create the Initialization Parameter File
可以将$ORACLE_HOME/dbs/init.ora复制为$ORACLE_HOME/dbs/initPROD2.ora，然后进行修改，也可以参考文档的范例：
Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Creating a Database with the CREATE DATABASE Statement -> Sample Initialization Parameter File
-- 修改以下必改的参数，并确认参数所指定的文件夹都已经建立
db_name='PROD2' #必改
memory_target=420M #必改，这是教学环境，生产环境需要特殊考虑
processes = 150 #必改，这是教学环境，生产环境需要特殊考虑
audit_file_dest='/u01/app/oracle/admin/PROD2/adump' #必改，且需创建该目录
audit_trail ='none' # 生产一般不开审计
db_block_size=8192
db_domain='oracle.com'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' #必改
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle' #必改
dispatchers='(PROTOCOL=TCP)(SERVICE=PROD2XDB)' #必改
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='' #如果这儿指定了UNDO表空间名，则在后面的create database语句中要指定一致的undo表空间。这儿可以将值清空，这样，数据库创建完成后，以后实例在启动时会自动去搜索UNDO表空间并使用，如果没有找到UNDO表空间，则使用system表空间。
control_files = ('/u01/app/oracle/oradata/PROD2/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl') #必改
compatible ='11.2.0'
Step 5: (Windows Only) Create an Instance
Enter the following command at a Windows command prompt:
$ oradim -NEW -SID sid -STARTMODE MANUAL -PFILE pfile
Step 6: Connect to the Instance
$ sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance.
Step 7: Create a Server Parameter File
create spfile from pfile;
Step 8: Start the Instance
startup nomount
错误处理：
当spfile中参数指定的文件夹不存在时，会导致DB INSTANCE 启动时出错。可以删除错误的spfile，修改pfile文件中的错误参数或先建立好对应的文件夹，然后重新启动DB INSTANCE。
Step 9: Issue the CREATE DATABASE Statement
注意：create database 命令用到的各个目录必须提前建立。可以参考官方文档示例。
1.Create a script file to create a DB
vi /home/oracle/createDB.sql
create database PROD2
user sys identified by oracle
user system identified by oracle
logfile group 1 ('/u01/app/oracle/oradata/PROD2/redo01a.log','/u02/logs/PROD2/redo01b.log') size 100m blocksize 512 reuse,
group 2 ('/u01/app/oracle/oradata/PROD2/redo02a.log','/u02/logs/PROD2/redo02b.log') size 100m blocksize 512 reuse,
group 3 ('/u01/app/oracle/oradata/PROD2/redo03a.log','/u02/logs/PROD2/redo03b.log') size 100m blocksize 512 reuse
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 100
character set al32utf8
national character set al16utf16
extent management local
datafile '/u01/app/oracle/oradata/PROD2/system01.dbf' size 325m reuse autoextend on maxsize unlimited
sysaux datafile '/u01/app/oracle/oradata/PROD2/sysaux01.dbf' size 325m reuse autoextend on maxsize unlimited
default tablespace users datafile '/u01/app/oracle/oradata/PROD2/users01.dbf' size 500m reuse autoextend on maxsize unlimited
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/PROD2/temp01.dbf' size 20m reuse
undo tablespace undotbs datafile '/u01/app/oracle/oradata/PROD2/undotbs01.dbf' size 200m reuse autoextend on maxsize unlimited;
2.Run the script file：
@/home/oracle/createDB.sql
-- 出错检查：
如果出错，可以查看alert日志。
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- -------------------- -----------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/ocp/ocp
1 Diag Trace /u01/app/oracle/diag/rdbms/ocp/ocp/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/ocp/ocp/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/ocp/ocp/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/ocp/ocp/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/ocp/ocp/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/ocp/ocp/trace/ocp_ora_20330.trcalert日志和各进程的trace文件都放在Diag Trace指向的目录下。当前会话的trace文件，即当前会话对应的server process的trace文件为Default Trace File指向的文件。
查每个Oracle进程及其对应的trace文件：
SQL> select pid, pname, program, tracefile from v$process;
-- 错误处理：建库出错后，要删除已生成的文件，重新startup nomount，修正后再执行建库命令。
Step 10: (Optional) Create Additional Tablespaces
-- Create a tablespace for applications
create tablespace apps_tbs logging
datafile '/u01/app/oracle/oradata/PROD2/apps01.dbf'
size 100m reuse autoextend on next 1280k maxsize unlimited
extent management local;
-- Create a tablespace for indexes
create tablespace indx_tbs logging
datafile '/u01/app/oracle/oradata/PROD2/indx01.dbf'
size 100m reuse autoextend on next 1280k maxsize unlimited
extent management local;
Step 11: Run Scripts to Build Data Dictionary Views, Synonyms, and PL/SQL packages
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus.
$ sqlplus / as sysdba
@?/rdbms/admin/catalog.sql -- About 2 min
@?/rdbms/admin/catproc.sql -- About 10 min
@?/rdbms/admin/utlrp.sql -- Recompiles all PL/SQL modules in invalid state（新版的在线手册中有说要运行这个脚本，老版的手册没有，这个脚本的作用是修复无效对象）
conn system/密码
@?/sqlplus/admin/pupbld.sql -- About 20 sec
Step 12: (Optional) Run Scripts to Install Additional Options
Step 13: Backup the Database.
Step 14: (Optional) Enable Automatic Instance Startup
将手工建库注册到DBCA
vi /etc/oratab
PROD2:/u01/app/oracle/product/11.2.0/dbhome_1:N
