--===================================
-- 使用 DBCA 创建数据库
--===================================
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 命令那么简单。
七 . 更多
VmWare6.5.2 下安装 RHEL 5.4 （配置 Oracle 安装环境）
Linux (RHEL 5.4) 下安装 Oracle 10g R2
