阅读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；

 
1[root@OCM11g ~]# su - oracle
2OCM11g-> env | grep ORA
3ORACLE_BASE=/u01/app/oracle
4ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
5OCM11g->

2 确认环境变量，本例中选择ORACLE_SID=manualdb,ORACLE_HOME选用原有的/u01/app/oracle/product/11.2.0/db_1；

01OCM11g-> id
02uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
03OCM11g-> uname -rm
042.6.18-194.el5 x86_64
05OCM11g-> export ORACLE_SID=manualdb
06OCM11g-> env | grep ORA
07ORACLE_SID=manualdb
08ORACLE_BASE=/u01/app/oracle
09ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
10OCM11g-> 

3 创建初始化参数文件，这里将参数文件放到系统默认的路径下，即$ORACLE_HOME/dbs,命名为initmanualdb.ora,其内容如下：

 
1OCM11g-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initmanualdb.ora 
2db_name=manualdb
3memory_target=600m
4OCM11g->

简单起见，我们只在初始化参数文件中指定了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:

1	OCM11g-> mkdir -p /u02/manualdb/oradata

online log:

1OCM11g-> mkdir -p /u02/manualdb/onlinelog
2OCM11g-> ll /u02/manualdb/
3total 8
4drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 onlinelog
5drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 oradata
6OCM11g->

5 启动manualdb实例到NOMOUNT状态，其实，在该环境下，我们的控制文件尚未生成，实例至多也只能加载到NOMOUNT状态：

01OCM11g-> id
02uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
03OCM11g-> env | grep ORA
04ORACLE_SID=manualdb
05ORACLE_BASE=/u01/app/oracle
06ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
07OCM11g-> sqlplus 
08 
09SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 10:59:32 2012
10 
11Copyright (c) 1982, 2009, Oracle.  All rights reserved.
12 
13Connected to an idle instance.
14 
15SQL> startup
16ORACLE instance started.
17 
18Total System Global Area  626327552 bytes
19Fixed Size                  2215944 bytes
20Variable Size             373297144 bytes
21Database Buffers          243269632 bytes
22Redo Buffers                7544832 bytes
23ORA-00205: error in identifying control file, check alert log for more info
24 
25 
26SQL>

上述报错ORA-00205提示控制文件找不到，正是我们所预见的，因为当前控制文件还未生成，所以数据库没法MOUNT，说明数据库是NOMOUNT状态。这时，也可以看到相关的后台进程已经启动：

01SQL> !
02OCM11g-> ps -ef | grep ora_|grep -v grep
03oracle   14885     1  0 11:02 ?        00:00:00 ora_pmon_manualdb
04oracle   14887     1  0 11:02 ?        00:00:00 ora_vktm_manualdb
05oracle   14891     1  0 11:02 ?        00:00:00 ora_gen0_manualdb
06oracle   14893     1  0 11:02 ?        00:00:00 ora_diag_manualdb
07oracle   14895     1  0 11:02 ?        00:00:00 ora_dbrm_manualdb
08oracle   14897     1  0 11:02 ?        00:00:00 ora_psp0_manualdb
09oracle   14899     1  0 11:02 ?        00:00:00 ora_dia0_manualdb
10oracle   14903     1  6 11:02 ?        00:00:27 ora_mman_manualdb
11oracle   14905     1  0 11:02 ?        00:00:01 ora_dbw0_manualdb
12oracle   14907     1  0 11:02 ?        00:00:00 ora_lgwr_manualdb
13oracle   14909     1  0 11:02 ?        00:00:00 ora_ckpt_manualdb
14oracle   14911     1  0 11:02 ?        00:00:00 ora_smon_manualdb
15oracle   14913     1  0 11:03 ?        00:00:00 ora_reco_manualdb
16oracle   14915     1  0 11:03 ?        00:00:00 ora_mmon_manualdb
17oracle   14917     1  0 11:03 ?        00:00:00 ora_mmnl_manualdb
18OCM11g->

6 执行下述CREATE DATABASE的命令，开始手工创建Oracle 数据库：

01SQL> conn / as sysdba;
02Connected.
03SQL> select open_mode from v$database;
04select open_mode from v$database
05                      *
06ERROR at line 1:
07ORA-01507: database not mounted
08 
09 
10SQL> CREATE DATABASE manualdb
11  2     USER SYS IDENTIFIED BY oracle
12  3     USER SYSTEM IDENTIFIED BY oracle
13  4     LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
14  5             GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
15  6     MAXLOGFILES 5
16  7     MAXLOGMEMBERS 5
17  8     MAXLOGHISTORY 1
18  9     MAXDATAFILES 100
19 10     CHARACTER SET AL32UTF8
20 11     NATIONAL CHARACTER SET AL16UTF16
21 12     EXTENT MANAGEMENT LOCAL
22 13     DATAFILE '/u02/manualdb/oradata/system01.dbf' SIZE 300M REUSE
23   SYSAUX DATAFILE '/u02/manualdb/oradata/sysaux01.dbf' SIZE 300M REUSE
24   DEFAULT TABLESPACE users
25      DATAFILE '/u02/manualdb/oradata/users01.dbf'
26 17        SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
27 18     DEFAULT TEMPORARY TABLESPACE tempts1
28 19        TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
29 20        SIZE 20M REUSE
30 21     UNDO TABLESPACE undotbs
31 22        DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
32 23        SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
33 
34Database created.
35 
36SQL> select name,open_mode from v$database;
37 
38NAME      OPEN_MODE
39--------- --------------------
40MANUALDB  READ WRITE
41 
42SQL>

7 创建数据字典视图，分别执行下述脚本来完成：

1SQL> @?/rdbms/admin/catalog.sql
2......
3......
4SQL> @?/rdbms/admin/catproc.sql
5......
6......
7SQL> @?/sqlplus/admin/pupbld.sql
8......
9......

上述3个脚本的说明见下表：

The Scripts and descriptions
Script	Description
CATALOG.SQL	Creates the views of the data dictionary tables, the dynamic performance views, and public sy nonyms 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 SQLPlus. Enables SQLPlus to disable commands by user.

8 至此，我们完成了手工创建Oracle 数据库。

01SQL> select * from v$version;
02 
03BANNER
04--------------------------------------------------------------------------------
05Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
06PL/SQL Release 11.2.0.1.0 - Production
07CORE    11.2.0.1.0      Production
08TNS for Linux: Version 11.2.0.1.0 - Production
09NLSRTL Version 11.2.0.1.0 - Production
10 
115 rows selected.
12 
13SQL> select name,open_mode from v$database;
14 
15NAME      OPEN_MODE
16--------- --------------------
17MANUALDB  READ WRITE
18 
191 row selected.
20 
21SQL> select name from v$datafile
22  2  union
23  3  select name from v$controlfile
24  4  union
25  5  select member from v$logfile;
26 
27NAME
28--------------------------------------------------------------------------------
29/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlmanualdb.dbf
30/u02/manualdb/onlinelog/redo01a.log
31/u02/manualdb/onlinelog/redo02a.log
32/u02/manualdb/oradata/sysaux01.dbf
33/u02/manualdb/oradata/system01.dbf
34/u02/manualdb/oradata/undotbs01.dbf
35/u02/manualdb/oradata/users01.dbf
36 
377 rows selected.
38 
39SQL>

9 最后，如果该数据库不需要的话。我们可以执行手工删除数据库，当然必须要将数据库启动到MOUNT RESTRICT状态来删除：

如何确认实例是否是RESTRICTED MODE：

01SQL> select instance_name,status,startup_time,logins from v$instance;
02 
03INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
04--------------- ------------ ------------------- ----------
05manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED
06 
071 row selected.
08 
09SQL> alter system enable restricted session;
10 
11System altered.
12 
13SQL> select instance_name,status,startup_time,logins from v$instance;
14 
15INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
16--------------- ------------ ------------------- ----------
17manualdb        MOUNTED      2012/03/30 13:38:52 RESTRICTED
18 
191 row selected.
20 
21SQL> alter system disable restricted session;
22 
23System altered.
24 
25SQL> select instance_name,status,startup_time,logins from v$instance;
26 
27INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
28--------------- ------------ ------------------- ----------
29manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED
30 
311 row selected.
32 
33SQL>

通过查看V$INSTANCE动态性能视图的LOGINS字段，如果该值为ALLOWED，说明实例是正常启动，并未进入RESTRICTED MODE，普通用户可以正常访问；如果该值为RESTRICTED说明是RESTRICTED MODE，即需要具有RESTRICTED SESSION系统权限的用户方可访问；

手工删除数据库：

01SQL> select instance_name,status,startup_time,logins from v$instance;
02 
03INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
04--------------- ------------ ------------------- ----------
05manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED
06 
071 row selected.
08 
09SQL> drop database;
10drop database
11*
12ERROR at line 1:
13ORA-12719: operation requires database is in RESTRICTED mode
14 
15 
16SQL> alter system enable restricted session;
17 
18System altered.
19 
20SQL> select instance_name,status,startup_time,logins from v$instance;
21 
22INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
23--------------- ------------ ------------------- ----------
24manualdb        MOUNTED      2012/03/30 13:38:52 RESTRICTED
25 
261 row selected.
27 
28SQL> drop database;
29 
30Database dropped.
31 
32Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
33With the Partitioning, OLAP, Data Mining and Real Application Testing options
34SQL> 

此时，Oracle自动删除该数据库的所有数据文件、联机重做日志文件、控制文件以及初始化参数文件。从alert日志文件里可以看到下述信息：

01Fri Mar 30 13:45:33 2012
02drop database
03Deleted file /u02/manualdb/oradata/system01.dbf
04Deleted file /u02/manualdb/oradata/sysaux01.dbf
05Deleted file /u02/manualdb/oradata/undotbs01.dbf
06Deleted file /u02/manualdb/oradata/users01.dbf
07Deleted file /u02/manualdb/onlinelog/redo01a.log
08Deleted file /u02/manualdb/onlinelog/redo02a.log
09Deleted file /u02/manualdb/oradata/temp01.dbf
10Deleted file /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_manualdb.f

10 简单总结，可见手工创建、删除数据库也很简单。当然，在第6步中执行手工建库的命令中还有诸多其它选项，比如选择OMF，这时，只要在参数文件中指定DB_CREATE_FILE_DEST参数，那么建库的命令将更加简单。

ITPub博客

手工创建、删除11gR2数据库

最新文章