【转载】Oracle 11g 手工建库

2015-07-09 17:11 AlfredZhao 阅读(2303) 评论(0) 编辑收藏举报

原文地址：http://www.oracleonlinux.cn/2012/03/11gr2-manual-create-database/
作者:Asher | 分类:Data Dictionary, Oracle | Tag:Oracle, oracle 11g | 评论:1

在前一篇文章中提到阅读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

手工建库脚本：

CREATE DATABASE manualdb
     USER SYS IDENTIFIED BY oracle
     USER SYSTEM IDENTIFIED BY oracle
     LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
             GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
     MAXLOGFILES 5
     MAXLOGMEMBERS 5
     MAXLOGHISTORY 1
     MAXDATAFILES 100
     CHARACTER SET AL32UTF8
     NATIONAL CHARACTER SET AL16UTF16
     EXTENT MANAGEMENT LOCAL
     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'
        SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
     DEFAULT TEMPORARY TABLESPACE tempts1
        TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
        SIZE 20M REUSE
     UNDO TABLESPACE undotbs
        DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
        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 SQLPlus. Enables SQLPlus 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参数，那么建库的命令将更加简单。

刷新页面返回顶部

Alfred Zhao 从Oracle起航，领略精彩的IT技术。