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