Oracle手动建库的步骤

Oracle除了用DBCA可以建库外，也可以手动来建立数据库，手动建库能够更了解数据库的一些启动过程和运作机制，对深入学习oracle 很有帮助，本文演示了10g下手动建库的流程和可能碰到的问题。

第一步：建立参数文件，这个文件可以从其他地方已有的数据库复制过来修改，也可以从init.ora的基础上修改，主要内容如下：

*.audit_file_dest='/u01/app/oracle/admin/erpwh/adump'
*.background_dump_dest='/u01/app/oracle/admin/erpwh/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DGWH/erpwh/control01.ctl','+DGWH/erpwh/control02.ctl','+DGWH/erpwh/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/erpwh/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='erpwh'
*.db_recovery_file_dest='+DGWH'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpoptmXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/erpwh/udump'

可以看到参数文件主要是指定了数据库名、跟踪文件的位置、控制文件的位置、数据块大小、内存参数等。Linux系统中，该文件位置应在$ORACLE_HOME/dbs目录下。

第二步：建立密码文件，该文件用于sys用户以sysdba身份远程管理登录，这里的密码可以和建立数据库脚本理的sys密码不同，如果是远程以sysdba身份登录则需要使用这里的密码。密码文件和参数文件在相同的目录下：

[oracle@erpdevdb dbs]$ orapwdfile=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapwerpwhpassword=erpwh entries=5

第三步：建立跟踪、日志等文件的路径：

[oracle@erpdevdb dbs]$ cd /u01/app/oracle/admin/

[oracle@erpdevdb admin]$ mkdir erpwh
[oracle@erpdevdb admin]$ cd erpwh
[oracle@erpdevdb erpwh]$ ll
total 0
[oracle@erpdevdb erpwh]$ mkdir adump bdump udump cdump
[oracle@erpdevdb erpwh]$ ll
total 32
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 adump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 bdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 cdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 10 15:16 udump

第四步：启动数据库到nomount状态：

[oracle@erpdevdb dbs]$ export ORACLE_SID=erpwh
[oracle@erpdevdb dbs]$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 10 15:18:09 2010

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

Connected to an idle instance.

SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

如果出现以上错误，是操作系统参数设置的内存不够，参见这篇文章修改即可：

[root@erpdevdb ~]# vi /etc/sysctl.conf

kernel.shmall = 4718592

或者调小参数文件里的内存参数也可以，取决于系统资源情况。

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 486540544 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14753792 bytes

第五步，运行建立数据库的脚本

CREATE DATABASE erpwh
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY sys
LOGFILE GROUP 1 ('+DGWH/erpwh/redo01.log') SIZE 100M,
GROUP 2 ('+DGWH/erpwh/redo02.log') SIZE 100M,
GROUP 3 ('+DGWH/erpwh/redo03.log') SIZE 100M
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+DGWH/erpwh/system01.dbf' SIZE 2048M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '+DGWH/erpwh/sysaux01.dbf' SIZE 1024M REUSE
DEFAULT TEMPORARY TABLESPACE temptbs1
TEMPFILE '+DGWH/erpwh/temp01.dbf'
SIZE 2048M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '+DGWH/erpwh/undotbs01.dbf'
SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

脚本主要是指定sys和system的密码，redo文件，字符集，数据文件，临时表空间和回滚段等信息。

但有时候在建库脚本里指定默认表空间DEFAULT TABLESPACE erp会报错：

SQL> CREATE DATABASE erpwh
2 USER SYS IDENTIFIED BY sys
3 USER SYSTEM IDENTIFIED BY sys
4 LOGFILE GROUP 1 ('+DGWH/erpwh/redo01.log') SIZE 100M,
5 GROUP 2 ('+DGWH/erpwh/redo02.log') SIZE 100M,
6 GROUP 3 ('+DGWH/erpwh/redo03.log') SIZE 100M
7 MAXLOGFILES 16
8 MAXLOGMEMBERS 5
9 MAXDATAFILES 100
10 MAXINSTANCES 8
11 MAXLOGHISTORY 29200
12 CHARACTER SET ZHS16GBK
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE '+DGWH/erpwh/system01.dbf' SIZE 2048M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE '+DGWH/erpwh/sysaux01.dbf' SIZE 1024M REUSE
17 DEFAULT TABLESPACE erp
18 DEFAULT TEMPORARY TABLESPACE temptbs1
19 TEMPFILE '+DGWH/erpwh/temp01.dbf'
20 SIZE 2048M REUSE
21 UNDO TABLESPACE undotbs1
22 DATAFILE '+DGWH/erpwh/undotbs01.dbf'
23 SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE DATABASE erpwh
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

这种情况一般是建库脚本哪里写的不对，需要检查。从asm存储来看各种数据文件都建立了，但是数据库并没有创建成功，这时把已经建立的数据文件删掉，并删除建库脚本理的默认表空间这行就可以了：

SQL> CREATE DATABASE erpwh
2 USER SYS IDENTIFIED BY sys
3 USER SYSTEM IDENTIFIED BY sys
4 LOGFILE GROUP 1 ('+DGWH/erpwh/redo01.log') SIZE 100M,
5 GROUP 2 ('+DGWH/erpwh/redo02.log') SIZE 100M,
6 GROUP 3 ('+DGWH/erpwh/redo03.log') SIZE 100M
7 MAXLOGFILES 16
8 MAXLOGMEMBERS 5
9 MAXDATAFILES 100
10 MAXINSTANCES 8
11 MAXLOGHISTORY 2
12 CHARACTER SET ZHS16GBK
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE '+DGWH/erpwh/system01.dbf' SIZE 2048M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE '+DGWH/erpwh/sysaux01.dbf' SIZE 1024M REUSE
17 DEFAULT TEMPORARY TABLESPACE temptbs1
18 TEMPFILE '+DGWH/erpwh/temp01.dbf'
19 SIZE 2048M REUSE
20 UNDO TABLESPACE undotbs1
21 DATAFILE '+DGWH/erpwh/undotbs01.dbf'
22 SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

第六步，建立其他表空间和数据字典：

SQL> create tablespace erp datafile '+DGWH' size30720m;

Tablespace created.

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

......

PL/SQL procedure successfully completed.

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

......

PL/SQL procedure successfully completed.

第七步，建立spfile：

SQL> create spfile='+DGWH/ERPWH/spfileerpwh.ora' frompfile;

File created.

要注意的是，Oracle下次启动的时候不不会默认使用这种方式建立的spfile，

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ -----------------------------------------
spfile string

不指定spfile路径的时候就可以了：

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2022144 bytes
Variable Size 486540544 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ -----------------------------------------
spfile string/u01/app/oracle/oracle/product
/10.2.0/db_1/dbs/spfileerpwh.o
ra

第八步，建立用户

SQL> create user erp identified by erp defaulttablespace ERP temporary tablespace temptbs1 profile DEFAULT;

User created.

SQL> grant dba to erp;

Grant succeeded.

SQL> connect erp/erp
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

出现这个错误提示并不会影响数据库的使用，手动建库一般会有这个警告出现。PRODUCT_USER_PROFILE是SYSTEM用户的一个表，存储客户端程序执行命令方面的限制信息，可以根据提示用system用户执行脚本来消除：

SQL> connect system/sys
Connected.
SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

.......

SQL> connect erp/erp
Connected.
SQL>
SQL>
SQL> show user
USER is "ERP"

第九步，配置网络服务：

[oracle@erpdevdb admin]$ vi tnsnames.ora

ERPWH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdevdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erpwh)
)
)

至此手动建立数据库完成，客户端可以连接上来使用了。