相關文章連結：
《解析dbca圖形化工具建立資料庫》http://space.itpub.net/?uid-23135684-action-viewspace-itemid-742834

這篇文章討論在Oracle Database 11.2.0.3的軟體環境下，手動建立單機資料庫的過程。

1.初始化ASM例項及建立磁碟組。

[grid@redhat6 dbs]$cd $GRID_HOME/dbs

[grid@redhat6 dbs]$touch init+ASM.ora

[grid@redhat6 dbs]$ vi init+ASM.ora

加入

instance_type=asm

[grid@redhat6 dbs]$ echo $ORACLE_SID

+ASM

[grid@redhat6 dbs]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 05:26:44 2012

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ORA-15110: no diskgroups mounted

SQL> show parameter sga

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

lock_sga boolean

FALSE

sga_max_size big integer

272M

sga_target big integer

0

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

ORA-15100: invalid or missing diskgroup name

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ORA-15110: no diskgroups mounted

SQL> create diskgroup data external redundancy

2 disk '/dev/raw/raw6' name data01;

Diskgroup created.

SQL> create diskgroup arc external redundancy

2 disk '/dev/raw/raw7' name arc01;

Diskgroup created.

SQL> show parameter disk

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

asm_diskgroups string

DATA, ARC

asm_diskstring string

SQL> alter system set asm_diskstring='/dev/raw/raw*';

System altered.

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2227664 bytes

Variable Size 256537136 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL> select name,state from v$asm_diskgroup;

NAME

------------------------------------------------------------

STATE

----------------------

ARC

MOUNTED

DATA

MOUNTED

2.建立Database資料庫。

[oracle@redhat6 /]$ cd $ORACLE_HOME/dbs

[oracle@redhat6 dbs]$ echo $ORACLE_SID

orcl

[oracle@redhat6 dbs]$ ls

init.ora

[oracle@redhat6 dbs]$ cp init.ora initorcl.ora

[oracle@redhat6 dbs]$ vi initorcl.ora

[oracle@redhat6 dbs]$ tail -20 initorcl.ora

# install time)

db_name='ORCL'

memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

# devices

control_files = ('+DATA','+ARC')

compatible ='11.2.0'

[oracle@redhat6 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle123

[oracle@redhat6 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump

[oracle@redhat6 dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area

[oracle@redhat6 dbs]$ mkdir -p /u01/app/oracle

確保tmpfs檔案系統要比grid和oracle使用者使用的memory_target加起來還大，否則在啟動資料庫例項的時候可能收到ORA-00845: MEMORY_TARGET not supported on this system的報錯，ORA-00845報錯參考文章：http://yangtingkun.itpub.net/post/468/408235，有關/dev/shm的介紹參考文章http://dbua.iteye.com/blog/1271574。另外，在/etc/fstab配置檔案中指定/dev/shm大小之後重啟伺服器，/dev/shm的配置可能不會生效，必須手動UMOUNT/MOUNT之後才生效，解決辦法參考文章： http://space.itpub.net/23135684/viewspace-742888

[oracle@redhat6 dbs]$ df -ahT /dev/shm

Filesystem Type Size Used Avail Use% Mounted on

tmpfs tmpfs 2.0G 212M 1.8G 11% /dev/shm

[oracle@redhat6 dbs]$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 08:44:16 2012

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes

Variable Size 616563896 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

SQL> CREATE DATABASE orcl

USER SYS IDENTIFIED BY oracle123

USER SYSTEM IDENTIFIED BY oracle123

CONTROLFILE REUSE

LOGFILE

GROUP 1 ('+DATA', '+ARC') SIZE 200M,

GROUP 2 ('+DATA', '+ARC') SIZE 200M,

GROUP 3 ('+DATA', '+ARC') SIZE 200M

MAXLOGFILES 5

MAXLOGHISTORY 100

MAXDATAFILES 10

MAXINSTANCES 2

ARCHIVELOG

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

SYSAUX DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA' SIZE 4G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

SET TIME_ZONE = '+08:00';

Database created.

執行CREATE DATABASE命令是在NOMOUNT下完成的，而使用dbca建立資料庫（實際是使用dbms_backup_restore恢復模板）是在MOUNT模式下完成的。

3.執行資料庫字典初始化指令碼。

SQL> @?/rdbms/admin/catalog.sql
SQL>  @?/rdbms/admin/catproc.sql
SQL>  conn system/oracle123
SQL>  @?/sqlplus/admin/pupbld.sql

4.修改控制檔案及驗證資料庫。

SQL> show parameter control_file

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string +DATA/orcl/controlfile/current

.266.787999629, +ARC/orcl/cont

rolfile/current.256.787999629

[oracle@redhat6 dbs]$ tail -20 initorcl.ora

# install time)

db_name='ORCL'

memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

# devices

control_files = ('+DATA/orcl/controlfile/current.266.787999629','+ARC/orcl/controlfile/current.256.787999629')

compatible ='11.2.0'

[oracle@redhat6 dbs]$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 09:56:02 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> create spfile from pfile;

File created.

在執行CREATE DATABASE命令之前建立並使用SPFILE是個好習慣，之後的建庫操作SPFILE中的control_files引數會被自動的修改，這樣就不需要在建庫完成之後再來調整SPFILE。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes

Variable Size 616563896 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/db_1/dbs/spfileorcl.ora

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select name ,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

ORCL READ WRITE

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME

--------------------------------------------------------------------------------

VERSION STATUS

------------------------------ ----------------------

Oracle Database Catalog Views

11.2.0.3.0 VALID

Oracle Database Packages and Types

11.2.0.3.0 VALID

5.DROP資料庫。

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount exclusive restrict

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes

Variable Size 616563896 bytes

Database Buffers 444596224 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> drop database;

Database dropped.

DROP Database操作完成之後，由於ASM遵循OMF管理方式，磁碟組中的控制檔案、資料檔案和日誌檔案都會被刪除。

CREATE DATABASE語法參見：

參考文章：

11gR2 手動建立資料庫(11.2.0.3)

相關文章