Oralce 10g 使用DBCA創建數據庫

語言: CN / TW / HK

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

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

[[email protected] 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即可完成建庫

[[email protected] 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

[[email protected] 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

[[email protected] 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

[[email protected] 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

[[email protected] 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

[[email protected] 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

[[email protected] 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

[[email protected] scripts]$ cat customScripts.sql

set echo on

spool /u01/app/oracle/admin/Test/scripts/customScripts.log

spool off

 

--創建完畢後,缺省的情況下,剛剛創建的實例已經啟動

[[email protected] 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命令那麼簡單。
————————————————