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::

#

# 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命令那麼簡單。