在前一篇文章中提到阅读Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，简单描述了DDL_LOCK_TIMEOUT参数。在Oracle 11gR2 Administrator‘s Guide文档的chapter 2 Creating and Configuring an Oracle Database章节时，提到手工建库，本文简单记录一下，如何在OEL 5.5 X86_64位环境下，手工创建及删除11.2.0.1.0的数据库，将文件放到文件系统上存放。当然，利用DBCA来建库、删库比较简单，就不再赘述了。具体操作步骤如下：
1 前提条件，操作系统上已经安装好Oracle 11gR2 的软件，其中环境变量ORACLE_BASE=/u01/app/oracle，ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1；
[root@OCM11g ~]# su - oracle OCM11g-> env | grep ORA ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g->
2 确认环境变量，本例中选择ORACLE_SID=manualdb,ORACLE_HOME选用原有的/u01/app/oracle/product/11.2.0/db_1；
OCM11g-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper) OCM11g-> uname -rm 2.6.18-194.el5 x86_64 OCM11g-> export ORACLE_SID=manualdb OCM11g-> env | grep ORA ORACLE_SID=manualdb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g->
3 创建初始化参数文件，这里将参数文件放到系统默认的路径下，即$ORACLE_HOME/dbs,命名为initmanualdb.ora,其内容如下：
OCM11g-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initmanualdb.ora db_name=manualdb memory_target=600m OCM11g->
简单起见，我们只在初始化参数文件中指定了db_name,memory_target这两个参数。我们知道，启动数据库实例的最少参数只需要一个db_name即可，这里选择db_name=manualdb,附加一个memory_target=600m,这个是11g的新参数，用于控制SGA+PGA的总大小。当然，也可以在参数文件中指定control_files,如果该参数被忽略的话，那么Oracle会自动在$ORACLE_HOME/dbs路径下创建一个名为cntrl$ORACLE_SID.dbf的控制文件。
4 准备将来存放数据文件、日志文件的路径。这两类文件的路径如下：
datafile:
OCM11g-> mkdir -p /u02/manualdb/oradata
online log:
OCM11g-> mkdir -p /u02/manualdb/onlinelog OCM11g-> ll /u02/manualdb/ total 8 drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 onlinelog drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 oradata OCM11g->
5 启动manualdb实例到NOMOUNT状态，其实，在该环境下，我们的控制文件尚未生成，实例至多也只能加载到NOMOUNT状态：
OCM11g-> id uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper) OCM11g-> env | grep ORA ORACLE_SID=manualdb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 OCM11g-> sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 10:59:32 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 373297144 bytes Database Buffers 243269632 bytes Redo Buffers 7544832 bytes ORA-00205: error in identifying control file, check alert log for more info SQL>
上述报错ORA-00205提示控制文件找不到，正是我们所预见的，因为当前控制文件还未生成，所以数据库没法MOUNT，说明数据库是NOMOUNT状态。这时，也可以看到相关的后台进程已经启动：
SQL> ! OCM11g-> ps -ef | grep ora_|grep -v grep oracle 14885 1 0 11:02 ? 00:00:00 ora_pmon_manualdb oracle 14887 1 0 11:02 ? 00:00:00 ora_vktm_manualdb oracle 14891 1 0 11:02 ? 00:00:00 ora_gen0_manualdb oracle 14893 1 0 11:02 ? 00:00:00 ora_diag_manualdb oracle 14895 1 0 11:02 ? 00:00:00 ora_dbrm_manualdb oracle 14897 1 0 11:02 ? 00:00:00 ora_psp0_manualdb oracle 14899 1 0 11:02 ? 00:00:00 ora_dia0_manualdb oracle 14903 1 6 11:02 ? 00:00:27 ora_mman_manualdb oracle 14905 1 0 11:02 ? 00:00:01 ora_dbw0_manualdb oracle 14907 1 0 11:02 ? 00:00:00 ora_lgwr_manualdb oracle 14909 1 0 11:02 ? 00:00:00 ora_ckpt_manualdb oracle 14911 1 0 11:02 ? 00:00:00 ora_smon_manualdb oracle 14913 1 0 11:03 ? 00:00:00 ora_reco_manualdb oracle 14915 1 0 11:03 ? 00:00:00 ora_mmon_manualdb oracle 14917 1 0 11:03 ? 00:00:00 ora_mmnl_manualdb OCM11g->
6 执行下述CREATE DATABASE的命令，开始手工创建Oracle 数据库：
SQL> conn / as sysdba;
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> CREATE DATABASE manualdb
2 USER SYS IDENTIFIED BY oracle
3 USER SYSTEM IDENTIFIED BY oracle
4 LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
5 GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
6 MAXLOGFILES 5
7 MAXLOGMEMBERS 5
8 MAXLOGHISTORY 1
9 MAXDATAFILES 100
10 CHARACTER SET AL32UTF8
11 NATIONAL CHARACTER SET AL16UTF16
12 EXTENT MANAGEMENT LOCAL
13 DATAFILE '/u02/manualdb/oradata/system01.dbf' SIZE 300M REUSE
SYSAUX DATAFILE '/u02/manualdb/oradata/sysaux01.dbf' SIZE 300M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u02/manualdb/oradata/users01.dbf'
17 SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
18 DEFAULT TEMPORARY TABLESPACE tempts1
19 TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
20 SIZE 20M REUSE
21 UNDO TABLESPACE undotbs
22 DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
23 SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
MANUALDB READ WRITE
SQL>
7 创建数据字典视图，分别执行下述脚本来完成：
SQL> @?/rdbms/admin/catalog.sql ...... ...... SQL> @?/rdbms/admin/catproc.sql ...... ...... SQL> @?/sqlplus/admin/pupbld.sql ...... ......
上述3个脚本的说明见下表：
The Scripts and descriptions
|
Script |
Description |
|
CATALOG.SQL |
Creates the views of the data dictionary tables, the dynamic performance views,and public synonyms for many of the views.Grants PUBLIC access to the synonyms. |
|
CATPROC.SQL |
Runs all scripts required for or used with PL/SQL. |
|
PUPBLD.SQL |
Required for SQL*Plus. Enables SQL*Plus to disable commands by user. |
8 至此，我们完成了手工创建Oracle 数据库。
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production 5 rows selected. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- MANUALDB READ WRITE 1 row selected. SQL> select name from v$datafile 2 union 3 select name from v$controlfile 4 union 5 select member from v$logfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/product/11.2.0/db_1/dbs/cntrlmanualdb.dbf /u02/manualdb/onlinelog/redo01a.log /u02/manualdb/onlinelog/redo02a.log /u02/manualdb/oradata/sysaux01.dbf /u02/manualdb/oradata/system01.dbf /u02/manualdb/oradata/undotbs01.dbf /u02/manualdb/oradata/users01.dbf 7 rows selected. SQL>
9 最后，如果该数据库不需要的话。我们可以执行手工删除数据库，当然必须要将数据库启动到MOUNT RESTRICT状态来删除：
如何确认实例是否是RESTRICTED MODE：
SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL> alter system enable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED 1 row selected. SQL> alter system disable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL>
通过查看V$INSTANCE动态性能视图的LOGINS字段，如果该值为ALLOWED，说明实例是正常启动，并未进入RESTRICTED MODE，普通用户可以正常访问；如果该值为RESTRICTED说明是RESTRICTED MODE，即需要具有RESTRICTED SESSION系统权限的用户方可访问；
手工删除数据库：
SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED 1 row selected. SQL> drop database; drop database * ERROR at line 1: ORA-12719: operation requires database is in RESTRICTED mode SQL> alter system enable restricted session; System altered. SQL> select instance_name,status,startup_time,logins from v$instance; INSTANCE_NAME STATUS STARTUP_TIME LOGINS --------------- ------------ ------------------- ---------- manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED 1 row selected. SQL> drop database; Database dropped. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
此时，Oracle自动删除该数据库的所有数据文件、联机重做日志文件、控制文件以及初始化参数文件。从alert日志文件里可以看到下述信息：
Fri Mar 30 13:45:33 2012 drop database Deleted file /u02/manualdb/oradata/system01.dbf Deleted file /u02/manualdb/oradata/sysaux01.dbf Deleted file /u02/manualdb/oradata/undotbs01.dbf Deleted file /u02/manualdb/oradata/users01.dbf Deleted file /u02/manualdb/onlinelog/redo01a.log Deleted file /u02/manualdb/onlinelog/redo02a.log Deleted file /u02/manualdb/oradata/temp01.dbf Deleted file /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_manualdb.f
10 简单总结，可见手工创建、删除数据库也很简单。当然，在第6步中执行手工建库的命令中还有诸多其它选项，比如选择OMF，这时，只要在参数文件中指定DB_CREATE_FILE_DEST参数，那么建库的命令将更加简单。
