手工创建oracle模板,手工创建oracle数据库

下面过程演示手工创建数据库实例的过程：

(b14231 第75-80页)

1、设置oracle数据库的sidora_test@oracle[/home/oracle]> export ORACLE_SID=cnhtm[@more@]

2、确定数据库的认证方式

如果需要在其他客户端以sysdba方式连接这个实例，按如下方法创建口令文件

ora_test@oracle[/home/oracle]> orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y

3、编辑参数文件

创建参数文件，文件名为 $ORACLE_HOME/dbs/init$ORACLE_SID.ora，其内容如下

ora_test@oracle[/home/oracle]> cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora

control_files = (+DATA/cnhtm/controlfile/control01.dbf,

+DATA/cnhtm/controlfile/control02.dbf,

+DATA/cnhtm/controlfile/control03.dbf)

db_name = cnhtm

log_archive_dest_1 = "LOCATION=+DATA/cnhtm/arch"

log_archive_dest_state_1 = enable

db_block_size = 8192

pga_aggregate_target = 100M

processes = 1000

sessions = 1200

open_cursors = 1024

undo_management = AUTO

shared_servers = 3

#下面这一行的undo表空间名要与步骤7的UNDO TABLESPACE语句中的undo表空间名一致，否则建库失败，

#在警告日志中报ORA-30012: undo tablespace 'undotbs1' does not exist or of wrong type

undo_tablespace = undotbs1

compatible = 10.2.0

sga_target = 200M

nls_language = AMERICAN

nls_territory = AMERICA

4、连接数据库实例

ora_test@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 9 11:19:08 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

5、创建spfile

idle> create spfile from pfile;

File created.

6、启动数据库实例到nomount状态

idle> startup nomount

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1218532 bytes

Variable Size 109053980 bytes

Database Buffers 92274688 bytes

Redo Buffers 7168000 bytes

7、执行create database语句

idle> CREATE DATABASE cnhtm

2 USER SYS IDENTIFIED BY oracle

3 USER SYSTEM IDENTIFIED BY oracle

4 LOGFILE

5 GROUP 1 ('+DATA/cnhtm/onlinelog/redo01.log') SIZE 100M,

6 GROUP 2 ('+DATA/cnhtm/onlinelog/redo02.log') SIZE 100M,

7 GROUP 3 ('+DATA/cnhtm/onlinelog/redo03.log') SIZE 100M

8 MAXLOGFILES 5

9 MAXLOGMEMBERS 5

10 MAXLOGHISTORY 1

11 MAXDATAFILES 100

12 MAXINSTANCES 1

13 CHARACTER SET ZHS16GBK

14 NATIONAL CHARACTER SET AL16UTF16

15 DATAFILE

16 '+DATA/cnhtm/datafile/system01.dbf' SIZE 325M REUSE

17 EXTENT MANAGEMENT LOCAL

18 SYSAUX DATAFILE '+DATA/cnhtm/datafile/sysaux01.dbf'

19 SIZE 325M REUSE

20 DEFAULT TABLESPACE tbs_1

--下面红色的这三行是需要加上的,否则会建库失败，alert log中报ORA-02236: invalid file name

21 DATAFILE '+DATA/cnhtm/datafile/tbs_101.dbf'

22 SIZE 100M REUSE

23 AUTOEXTEND ON MAXSIZE UNLIMITED

24 DEFAULT TEMPORARY TABLESPACE tempts1

25 TEMPFILE '+DATA/cnhtm/tempfile/temp01.dbf'

26 SIZE 20M REUSE

--下面这一行的undo表空间名要与步骤3的undo_tablespace一致，否则建库失败，

--在警告日志中报ORA-30012: undo tablespace 'undotbs1' does not exist or of wrong type

27 UNDO TABLESPACE undotbs1

28 DATAFILE '+DATA/cnhtm/datafile/undotbs01.dbf'

29 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

8、创建额外的表空间

idle> CREATE TABLESPACE users LOGGING

2 DATAFILE '+data/cnhtm/datafile/users01.dbf'

3 SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL;

4

Tablespace created.

idle>

idle> CREATE TABLESPACE indx LOGGING

2 DATAFILE '+data/cnhtm/datafile/indx01.dbf'

3 SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

4 EXTENT MANAGEMENT LOCAL;

Tablespace created.

9、创建数据字典视图

idle> @?/rdbms/admin/catalog.sql

idle> @?/rdbms/admin/catproc.sql

--end--