相关文章链接：
《解析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语法参见：http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm#SQLRF01204

参考文章：http://www.dbaglobe.com/2011/05/manual-create-database-in-11gr2-11202.html

ITPub博客

11gR2 手动创建数据库(11.2.0.3)

最新文章