手工创建数据库及删除数据库示例

首先是应对ORACLE_SID,DB_NAME,存放数据文件、REDO日志、控制文件等的目录进行规划。 如我的实验中：ORACLE_SID=bys3，DB_NAME为bys3，数据文件、REDO日志、控制文件均存放在/u01/oradata/bys3/ 实验如下: 环境：OEL5.7,ORACLE 11.2.0.4,VBOX虚拟机--建库前OR

首先是应对ORACLE_SID,DB_NAME,存放数据文件、REDO日志、控制文件等的目录进行规划。
如我的实验中：ORACLE_SID=bys3，DB_NAME为bys3，数据文件、REDO日志、控制文件均存放在/u01/oradata/bys3/
实验如下:
环境：OEL5.7,ORACLE 11.2.0.4,VBOX虚拟机--建库前ORACLE软件要装好唉。。

1.设置ORACLE_SID

可以通过.bash_profile里查看--更改未退出会话时需要source .bash_profile使之生效，
或者直接在命令行中用：export ORACLE_SID=bys3
echo $ORACLE_SID来验证一下。

2.再次确定一下.bash_profile里的各个变量参数是否正确

cat .bash_profile 进行查看
[oracle@bys3 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
RACLE_BASE=/u01
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=bys3
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG=AMERICAN
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
ORACLE_HOSTNAME=bys3.bys.com
export ORACLE_HOSTNAME

3.创建密码文件--注意WINDOWS下和LINUX下的密码文件格式是不一样的

注意：WIN下文件夹名和LINUX下不一样。WIN下$ORACLE_HOME/database LINUX下是：$ORACLE_HOME\dbs\
如实例名为orcl,则WINDOWS下密码文件格式为：PWDorcl.ORA LINUX下为orapworcl
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwbys3 password=oracle entries=3 force=y

4.创建相应目录--根据自己计划的数据文件、TRACE等文件的位置进行调整。

可以写成类似下面的脚本进行调用
[oracle@bys3 ~]$ cat createdir.sh
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/admin/bys3/adump
mkdir -p /u01/admin/bys3/dpdump
mkdir -p /u01/admin/bys3/pfile
mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs
mkdir -p /u01/archivelog
mkdir -p /u01/cfgtoollogs/dbca/bys3
mkdir -p /u01/fast_recovery_area
mkdir -p /u01/fast_recovery_area/bys3
mkdir -p /u01/oradata/bys3
umask ${OLD_UMASK}

5.创建初始化参数文件--仅设置以下两条参数即可-db_name，controlfile

vi /home/oracle/bys.ora
db_name=bys3
control_files='/u01/oradata/bys3/control01.ctl','/u01/oradata/bys3/control02.ctl'

6.启动实例--与第1步对应，用到了ORACLE_SID

sqlplus / as sysdba
创建SPFILE文件，默认放在：WINDOWS下是：$ORACLE_HOME\database\ LINUX下是：$ORACLE_HOME\dbs\
注：从spfile 创建 pfile，需要SYSDBA权限，DBA权限发出此语句会报错。
create spfile from pfile='/home/oracle/bys.ora';
启动实例：--默认搜索顺序是：在缺省目录$ORACLE_HOME/database下搜索SPFILESID.ORA——SPFILE.ORA——PFILESID.ORA
startup nomount;

7.使用CREATE DATABASE命令创建数据库。建议使用ZHS16GBK 字符集

在SQLPLUS中使用如下命令：--也可以写入一个脚本文件，然后在SQLPLUS中调用此脚本。注意数据文件 、日志文件的创建目录位置及权限。
create database bys3
user sys identified by oraclesys
user system identified by oraclesys
logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,
group 2('/u01/oradata/bys3/redo02.log') size 50m,
group 3('/u01/oradata/bys3/redo03.log') size 50m
maxlogfiles 20
maxlogmembers 5
maxloghistory 200
maxdatafiles 100
character set AL32UTF8
national character set AL16UTF16
extent management local
datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse
sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse
default temporary tablespace temp
tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse
undo tablespace undotbs1
datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse;

以上语句成功执行后是有个类似database create complete吧，数据库此时就被置为OPEN状态--可查询select status from v$instance验证。
此时执行下面一条语句，增加USERS表空间。
create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m;
alter database default tablespace users; ---将users表空间设置为系统默认的表空间，用户新建的表不指定默认即在此表空间。

8.以上创建完成后，执行以下三个脚本：

我这里用了脚本的绝对路径,也可以使用@?/rdbms/admin/catalog.sql的方式;，
@=run ?==$ORACLE_HOME mark (?) is a SQL*Plus variable indicating the Oracle home directory
catalog.Sql创建SQL视图，
catproc.sql创建系统的存储过程...
pupbld.sql脚本主要是创建SQLPLUS_PRODUCT_PROFILE表，并在表上建立相关的视图和同义词
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql
下面一句需要使用SYSTEM用户登陆，不能用SYSDBA，会报错，见http://blog.csdn.net/q947817003/article/details/16117123
即上两个脚本执行完成后，使用conn system/oraclesys命令以SYSETM用户登陆，可以show user验证一下。
@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql
到此创建完成。默认是非归档模式。

9.删除数据库：因使用手工创建的数据库使用DBCA无法删除，此时需要使用命令来删除数据库。

使用以下三个命令即可：
SYS@bys1>startup mount exclusive
SYS@bys1>alter system enable restricted session;
SYS@bys1>drop database;

数据库相应的控制文件,日志文件，数据文件会自动删除,spfile也会删除(归档日志不会删除). 从以下官方文档的内容可以证实：Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file.
The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.

10.手工建库时的ALERT日志

[oracle@bys3 ~]$ cat alert_bys3.log
Thu Nov 14 14:17:19 2013
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 3418 MB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total System Global Area size is 146 MB. For optimal performance,
prior to the next instance restart:
1. Increase the number of unused large pages by
at least 73 (page size 2048 KB, total size 146 MB) system wide to
get 100% of the System Global Area allocated with large pages
********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Shared memory segment for instance monitoring created
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
NUMA status: non-NUMA system
cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
Grp 0:
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =22
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: bys3.bys.com
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine: i686
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora
System parameters with non-default values: ----和前面设置的初始化参数文件中参数对应
control_files = "/u01/oradata/bys3/control01.ctl"
control_files = "/u01/oradata/bys3/control02.ctl"
db_name = "bys3"
Thu Nov 14 14:17:21 2013
PMON started with pid=2, OS id=4389
Thu Nov 14 14:17:21 2013
PSP0 started with pid=3, OS id=4393
Thu Nov 14 14:17:23 2013
VKTM started with pid=4, OS id=4397 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Thu Nov 14 14:17:23 2013
GEN0 started with pid=5, OS id=4403
Thu Nov 14 14:17:23 2013
DIAG started with pid=6, OS id=4407
Thu Nov 14 14:17:23 2013
DBRM started with pid=7, OS id=4411
Thu Nov 14 14:17:23 2013
DIA0 started with pid=8, OS id=4415
Thu Nov 14 14:17:23 2013
MMAN started with pid=9, OS id=4419
Thu Nov 14 14:17:24 2013
DBW0 started with pid=10, OS id=4423
Thu Nov 14 14:17:24 2013
LGWR started with pid=11, OS id=4427
Thu Nov 14 14:17:24 2013
CKPT started with pid=12, OS id=4431
Thu Nov 14 14:17:24 2013
SMON started with pid=13, OS id=4435
Thu Nov 14 14:17:24 2013
RECO started with pid=14, OS id=4439
Thu Nov 14 14:17:24 2013
MMON started with pid=15, OS id=4443
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Thu Nov 14 14:17:25 2013
MMNL started with pid=16, OS id=4447
Thu Nov 14 14:23:19 2013
create database bys3
user sys identified by *user system identified by * logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,
group 2('/u01/oradata/bys3/redo02.log') size 50m,
group 3('/u01/oradata/bys3/redo03.log') size 50m
maxlogfiles 20
maxlogmembers 5
maxloghistory 200
maxdatafiles 100
character set AL32UTF8
national character set AL16UTF16
extent management local
datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse
sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse
default temporary tablespace temp
tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse
undo tablespace undotbs1
datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse
Database mounted in Exclusive Mode
Lost write protection disabled
Thu Nov 14 14:23:38 2013
Successful mount of redo thread 1, with mount id 3358374039
Thu Nov 14 14:23:38 2013
Database SCN compatibility initialized to 1
Assigning activation ID 3358374039 (0xc82cb897)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/oradata/bys3/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Nov 14 14:23:38 2013
SMON: enabling cache recovery
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

EXTENT MANAGEMENT LOCAL online
Thu Nov 14 14:24:23 2013
Completed: create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse

EXTENT MANAGEMENT LOCAL online
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
UNDO_SEG_CRT: Could not find usn tail
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Undo initialization finished serial:0 start:12514064 end:12514074 diff:10 (0 seconds)
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Thu Nov 14 14:24:54 2013
Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse
Thu Nov 14 14:25:12 2013
[4450] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/bys3/temp01.dbf' size 20m reuse

Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/bys3/temp01.dbf' size 20m reuse

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
processing ?/rdbms/admin/dsec.bsq
processing ?/rdbms/admin/doptim.bsq
processing ?/rdbms/admin/dobj.bsq
processing ?/rdbms/admin/djava.bsq
processing ?/rdbms/admin/dpart.bsq
processing ?/rdbms/admin/drep.bsq
processing ?/rdbms/admin/daw.bsq
processing ?/rdbms/admin/dsummgt.bsq
processing ?/rdbms/admin/dtools.bsq
processing ?/rdbms/admin/dexttab.bsq
processing ?/rdbms/admin/ddm.bsq
processing ?/rdbms/admin/dlmnr.bsq
processing ?/rdbms/admin/ddst.bsq
Thu Nov 14 14:25:21 2013
SMON: enabling tx recovery
Starting background process SMCO
Thu Nov 14 14:25:22 2013
SMCO started with pid=18, OS id=4476
Thu Nov 14 14:25:24 2013
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Nov 14 14:25:25 2013
QMNC started with pid=19, OS id=4480
Completed: create database bys3
user sys identified by *user system identified by * logfile group 1('/u01/oradata/bys3/redo01.log') size 50m,
group 2('/u01/oradata/bys3/redo02.log') size 50m,
group 3('/u01/oradata/bys3/redo03.log') size 50m
maxlogfiles 20
maxlogmembers 5
maxloghistory 200
maxdatafiles 100
character set AL32UTF8
national character set AL16UTF16
extent management local
datafile '/u01/oradata/bys3/system01.dbf' size 500m reuse
sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325m reuse
default temporary tablespace temp
tempfile '/u01/oradata/bys3/temp01.dbf' size 20m reuse
undo tablespace undotbs1
datafile '/u01/oradata/bys3/undotbs01.dbf' size 200m reuse
Thu Nov 14 14:25:57 2013
create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50m
ORA-1537 signalled during: create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50m...
Thu Nov 14 14:26:25 2013
create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m
Completed: create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50m
Thu Nov 14 14:30:31 2013
Thread 1 advanced to log sequence 2 (LGWR switch)
Current log# 2 seq# 2 mem# 0: /u01/oradata/bys3/redo02.log
Thu Nov 14 14:31:16 2013
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u01/oradata/bys3/redo03.log
Thu Nov 14 14:33:22 2013
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log# 1 seq# 4 mem# 0: /u01/oradata/bys3/redo01.log
Thu Nov 14 14:33:52 2013
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: /u01/oradata/bys3/redo02.log
Thu Nov 14 14:34:11 2013
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Thu Nov 14 14:34:28 2013
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /u01/oradata/bys3/redo03.log
Thu Nov 14 14:34:43 2013
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /u01/oradata/bys3/redo01.log
Thu Nov 14 14:35:04 2013
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /u01/oradata/bys3/redo02.log
Thu Nov 14 14:36:16 2013
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /u01/oradata/bys3/redo03.log
Thu Nov 14 14:37:43 2013
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /u01/oradata/bys3/redo01.log
Thu Nov 14 14:38:55 2013
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u01/oradata/bys3/redo02.log
Thu Nov 14 14:40:10 2013
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: /u01/oradata/bys3/redo03.log
Thu Nov 14 14:40:34 2013
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /u01/oradata/bys3/redo01.log
Thu Nov 14 14:40:46 2013
Starting background process CJQ0
Thu Nov 14 14:40:46 2013
CJQ0 started with pid=32, OS id=4598
Thu Nov 14 14:41:31 2013
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: /u01/oradata/bys3/redo02.log
Thu Nov 14 14:41:40 2013
SERVER COMPONENT id=CATPROC: timestamp=2013-11-14 14:41:40
Thu Nov 14 15:06:30 2013
Spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.