在前一篇文章中提到阅读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参数,那么建库的命令将更加简单。