Oralce 10g 使用DBCA创建数据库

--===================================

--使用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

使用Uniread实现SQLplus翻页功能

Oracle 相关