手工建立資料庫及刪除資料庫示例--附建庫時alert日誌

首先是應對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.