Oracle提供了DBCA来创建数据库，对于初学者来说使用DBCA创建数据库简化了很多工作和设置，直接在交互界面即可实现所有的功能。然而对于实际的生产数据库来说，使用DBCA来创建不切实际，建议按实际需求规划来创建数据库。Oracle数据库的创建不像SQL server，直接使用CREATE DATABASE DB_NAME(仅作临时，演示用)一条语句即可实现。不管是SQL还是Oracle，对于创建生产型数据库都需要进行需求分析、规划、创建等步骤。
一、规划数据库
1.创建数据库的目的(高可用性、并发性、数据装载)
2.数据库的应用类型(OLAP,OLTP)
3.数据库存储结构的设计
4.数据库的名称、字符集
5.db_block 块的大小
6.数据库容量的初始大小及增幅
二、建库前需要确认的问题(创建后不可修改)
1.数据库字符集(建议使用AL32UTF8,该字符集支持XML)
2.数据库的名称(SID)
3.数据块的大小
以下是可调整的设置，建议提前确定
1.SGA大小sga_max_size
2.日志缓冲区大小log_buffer
3.最大允许进程数
其他注意事项
1.文件存储方式(文件系统/RAW/ASM)
2.数据文件、日志文件大小、存储位置
3.表空间的构成
三、建库的几种方法
1.通过OUI安装软件后自动调用DBCA来创建
2.手动调用DBCA创建(图型化界面，跨平台)
3.手动执行命令创建
四、查看数据库是否已经创建
方式一：以下方式可以查看使用DBCA已创建的数据库
[oracle@robinson isqlplus]$ more /etc/oratab
/*
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/u01/app/oracle/10g:N
Test:/u01/app/oracle/10g:N
*/
方式二：以下方式查看手动创建的数据库
[oracle@robinson isqlplus]$ ls $ORACLE_BASE/admin /*查看没有使用DBCA创建的数据库*/
Test orcl
[oracle@robinson isqlplus]$ ls /u01/app/oracle/oradata/Test /*如存在文件则表示已安装该库*/
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
五、使用DBCA创建数据库
1.打开Linux中的命令行模式
2.在root帐户下执行xhost +
3.切换到oracle 帐户，执行DBCA
4.按提示操作完毕
六、查看相关信息
--1.查看操作系统中的模板数据库文件所在的位置
[oracle@robinson admin]$ ll /u01/app/oracle/10g/assistants/dbca/templates
total 112120
-rw-r----- 1 oracle oinstall 5728 Jun 30 2005 Data_Warehouse.dbc
-rw-r----- 1 oracle oinstall 5608 Jun 30 2005 General_Purpose.dbc
-rw-r----- 1 oracle oinstall 12050 May 16 2005 New_Database.dbt
-r-xr-xr-x 1 oracle oinstall 7061504 Jul 2 2005 Seed_Database.ctl
-r-xr-xr-x 1 oracle oinstall 93569024 Jul 2 2005 Seed_Database.dfb
-rw-r----- 1 oracle oinstall 5665 Jun 30 2005 Transaction_Processing.dbc
-r-xr-xr-x 1 oracle oinstall 991232 Jul 2 2005 example.dmp
-r-xr-xr-x 1 oracle oinstall 13017088 Jul 2 2005 example01.dfb
--2.查看创建数据库期间保存的脚本文件位置及脚本文件
[oracle@robinson scripts]$ pwd
/u01/app/oracle/admin/Test/scripts
[oracle@robinson scripts]$ ls
CloneRmanRestore.sql cloneDBCreation.sql initTestTemp.ora rmanRestoreDatafiles.sql
Test.sh customScripts.sql postDBCreation.sql
Test.sql init.ora postScripts.sql
--刚刚创建的被保存为Test.sh和Test.sql，下次可以直接运行Test.sh即可完成建库
[oracle@robinson scripts]$ cat Test.sh
#!/bin/sh
--建立相关文件目录
mkdir -p /u01/app/oracle/10g/cfgtoollogs/dbca/Test
mkdir -p /u01/app/oracle/10g/dbs
mkdir -p /u01/app/oracle/admin/Test/adump
mkdir -p /u01/app/oracle/admin/Test/bdump
mkdir -p /u01/app/oracle/admin/Test/cdump
mkdir -p /u01/app/oracle/admin/Test/dpdump
mkdir -p /u01/app/oracle/admin/Test/pfile
mkdir -p /u01/app/oracle/admin/Test/udump
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/oradata/Test
--设置Oracle_SID
ORACLE_SID=Test; export ORACLE_SID
--修改oratab文件
echo You should Add this entry in the /etc/oratab: Test:/u01/app/oracle/10g:Y
--使用nolog登陆并执行Test.sql
/u01/app/oracle/10g/bin/sqlplus /nolog @/u01/app/oracle/admin/Test/scripts/Test.sql
-------------------------------------------------------------------------------------
--查看Test.sql
[oracle@robinson scripts]$ more Test.sql
--设定密码
set verify off
PROMPT specify a password for sys as parameter 1;
DEFINE sysPassword = &1
PROMPT specify a password for system as parameter 2;
DEFINE systemPassword = &2
PROMPT specify a password for sysman as parameter 3;
DEFINE sysmanPassword = &3
PROMPT specify a password for dbsnmp as parameter 4;
DEFINE dbsnmpPassword = &4
--使用orapwd命令生成密码文件
host /u01/app/oracle/10g/bin/orapwd file=/u01/app/oracle/10g/dbs/orapwTest password=&&sysPassword force=y
--执行下列脚本
@/u01/app/oracle/admin/Test/scripts/CloneRmanRestore.sql
@/u01/app/oracle/admin/Test/scripts/cloneDBCreation.sql
@/u01/app/oracle/admin/Test/scripts/postScripts.sql
@/u01/app/oracle/admin/Test/scripts/postDBCreation.sql
@/u01/app/oracle/admin/Test/scripts/customScripts.sql
------------------------------------------------------------------------------------------
--查看CloneRmanRestore.sql
[oracle@robinson scripts]$ cat CloneRmanRestore.sql
--使用sys帐户登陆
connect "SYS"/"&&sysPassword" as SYSDBA
set echo ON
--输入日志文件
spool /u01/app/oracle/admin/Test/scripts/CloneRmanRestore.LOG
--使用静态参数文件启动数据库
startup nomount pfile="/u01/app/oracle/admin/Test/scripts/init.ora";
@/u01/app/oracle/admin/Test/scripts/rmanRestoreDatafiles.sql;
------------------------------------------------------------------------------------------
--查看rmanRestoreDatafiles.sql
[oracle@robinson scripts]$ cat rmanRestoreDatafiles.sql
--从rman备份文件中恢复创建数据库所必须的基本数据文件
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/u01/app/oracle/oradata/Test/system01.dbf', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/Test/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/Test/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/Test/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/10g/assistants/dbca/templates/Seed_Database.dfb', done);
if done then
dbms_output.put_line(' Restore done.');
else
dbms_output.put_line(' ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
--------------------------------------------------------------------------------------------
--查看cloneDBCreation.sql
[oracle@robinson scripts]$ cat cloneDBCreation.sql
--使用sys帐户登陆
connect "SYS"/"&&sysPassword" as SYSDBA
set echo ON
--输出日志文件
spool /u01/app/oracle/admin/Test/scripts/cloneDBCreation.LOG
--创建数据文件、控制文件、日志文件等
Create controlfile reuse set database "Test"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/Test/system01.dbf',
'/u01/app/oracle/oradata/Test/undotbs01.dbf',
'/u01/app/oracle/oradata/Test/sysaux01.dbf',
'/u01/app/oracle/oradata/Test/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/Test/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/Test/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/Test/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
--使用参数文件initTestTemp.ora启动数据库到nomount状态并修改数据文件、控制文件、日志文件等
startup nomount pfile="/u01/app/oracle/admin/Test/scripts/initTestTemp.ora";
Create controlfile reuse set database "Test"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/Test/system01.dbf',
'/u01/app/oracle/oradata/Test/undotbs01.dbf',
'/u01/app/oracle/oradata/Test/sysaux01.dbf',
'/u01/app/oracle/oradata/Test/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/Test/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/Test/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/Test/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "Test" open resetlogs;
alter database rename global_name to "Test";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/Test/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND
ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
alter system disable restricted session;
connect "SYS"/"&&sysPassword" as SYSDBA
@/u01/app/oracle/10g/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install
change_on_install change_on_install change_on_install
/u01/app/oracle/10g/assistants/dbca/templates/example.dmp
/u01/app/oracle/10g/assistants/dbca/templates/example01.dfb
/u01/app/oracle/oradata/Test/example01.dbf
/u01/app/oracle/admin/Test/scripts/ "/'SYS/&&sysPassword as SYSDBA/'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
startup pfile="/u01/app/oracle/admin/Test/scripts/initTestTemp.ora";
alter system enable restricted session;
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT AL16UTF16;
alter user sys identified by "&&sysPassword";
alter user system identified by "&&systemPassword";
alter system disable restricted session;
------------------------------------------------------------------------------------------
--查看postScripts.sql
[oracle@robinson scripts]$ cat postScripts.sql
--该脚本主要是导入样本数据
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/Test/scripts/postScripts.LOG
--执行dbmssml.sql脚本及过程
@/u01/app/oracle/10g/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set current_schema=ORDSYS;
--执行ordlib.sql脚本及过程
@/u01/app/oracle/10g/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"&&sysPassword" as SYSDBA
connect "SYS"/"&&sysPassword" as SYSDBA
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
spool OFF
---------------------------------------------------------------------------------------
--查看postDBCreation.sql
[oracle@robinson scripts]$ cat postDBCreation.sql
--该脚本主要生成spfile及验证帐户信息，配置OEM
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/Test/scripts/postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
--为Test数据库生成spfile
create spfile='/u01/app/oracle/10g/dbs/spfileTest.ora' FROM pfile='/u01/app/oracle/admin/Test/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
alter user SYSMAN identified by "&&sysmanPassword" account unlock;
alter user DBSNMP identified by "&&dbsnmpPassword" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
--下面是配置OEM
host /u01/app/oracle/10g/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME
Test -PORT 1521 -EM_HOME /u01/app/oracle/10g -LISTENER LISTENER -SERVICE_NAME
Test -SYS_PWD &&sysPassword -SID Test -ORACLE_HOME /u01/app/oracle/10g -DBSNMP_PWD
&&dbsnmpPassword -HOST robinson.com -LISTENER_OH /u01/app/oracle/10g -LOG_FILE
/u01/app/oracle/admin/Test/scripts/emConfig.log
-SYSMAN_PWD &&sysmanPassword;
spool /u01/app/oracle/admin/Test/scripts/postDBCreation.LOG
------------------------------------------------------------------------------------------
--查看customScripts.sql
[oracle@robinson scripts]$ cat customScripts.sql
set echo on
spool /u01/app/oracle/admin/Test/scripts/customScripts.log
spool off
--创建完毕后，缺省的情况下，刚刚创建的实例已经启动
[oracle@robinson scripts]$ ps -ef |grep Test
oracle 18054 1 0 11:45 ? 00:00:00 ora_pmon_Test
oracle 18056 1 0 11:45 ? 00:00:00 ora_psp0_Test
oracle 18058 1 0 11:45 ? 00:00:00 ora_mman_Test
oracle 18060 1 0 11:45 ? 00:00:00 ora_dbw0_Test
oracle 18062 1 0 11:45 ? 00:00:00 ora_lgwr_Test
oracle 18064 1 0 11:45 ? 00:00:02 ora_ckpt_Test
oracle 18066 1 0 11:45 ? 00:00:01 ora_smon_Test
oracle 18068 1 0 11:45 ? 00:00:00 ora_reco_Test
oracle 18070 1 0 11:45 ? 00:00:00 ora_mmon_Test
oracle 18072 1 0 11:45 ? 00:00:00 ora_mmnl_Test
oracle 18074 1 0 11:45 ? 00:00:00 ora_d000_Test
oracle 18076 1 0 11:45 ? 00:00:00 ora_s000_Test
oracle 18080 1 0 11:45 ? 00:00:00 ora_qmnc_Test
oracle 18121 1 0 11:45 ? 00:00:00 ora_q001_Test
oracle 18598 1 0 12:03 ? 00:00:00 ora_cjq0_Test
oracle 26455 1 0 16:14 ? 00:00:00 ora_q000_Test
oracle 27046 1 0 16:32 ? 00:00:00 ora_q003_Test
oracle 27063 30407 0 16:33 pts/2 00:00:00 grep Test
--总结：由此可见，创建Oralce Database的工作量可见一般，绝非SQL server 的一个Create database命令那么简单。
