手工创建oracle数据库

[qq_40608182]

求手工创建oracle数据库的方法，不甚感激！

[碧水幽幽泉]

手工创建oracle数据库，具体方法和步骤如下：

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

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