手工建庫與dbca建庫

pwz1688發表於2014-04-09
一、10g上手工建庫
建庫口絕分四步：
1、建立目錄，賦予許可權；
2、建立初始化檔案；
3、啟動資料庫至nomount狀態，執行建庫指令碼；
4、執行三個指令碼，建立使用者預設表空間；
先看第一步（即:建立目錄，賦予許可權）
--連線oracle使用者，進入$ORACLE_BASE/oradata目錄下：
[root@gc1 ~]# su - oracle
[oracle@gc1 ~]$ cd $ORACLE_BASE/oradata
[oracle@gc1 oradata]$ pwd
/u01/app/oracle/oradata
[oracle@gc1 oradata]$ ls
PROD yj
--在$ORACLE_BASE/oradata目錄下建立BOB目錄，並賦予755許可權及分配相關使用者組（oracle:oinstall）
[oracle@gc1 oradata]$ mkdir BOB
[oracle@gc1 oradata]$ ls -lt
total 12
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:10 BOB
drwxr-xr-x 10 oracle oinstall 4096 Sep 19 2012 PROD
drwxr-x--- 2 oracle oinstall 4096 Jul 6 2012 yj
--假若BOB目錄以上許可權和所在使用者及組不對，可用下面命令進行相關修改，另外建庫需要755許可權
[oracle@gc1 oradata]$ chown -R oracle:oinstall /u01/app/oracle/oradata/BOB
[oracle@gc1 oradata]$ chmod -R 755 BOB
[oracle@gc1 oradata]$ ls -lt
total 12
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:10 BOB
drwxr-xr-x 10 oracle oinstall 4096 Sep 19 2012 PROD
drwxr-x--- 2 oracle oinstall 4096 Jul 6 2012 yj
--在BOB目錄下建立5個disk目錄及3個dump目錄
[oracle@gc1 oradata]$ cd BOB
[oracle@gc1 BOB]$ mkdir disk1 disk2 disk3 disk4 disk5 bdump cdump udump
[oracle@gc1 BOB]$ ls -lt
total 32
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 bdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 cdump
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 disk1
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 disk2
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 disk3
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 disk4
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 disk5
drwxr-xr-x 2 oracle oinstall 4096 Mar 31 22:12 udump
第二步：建立初始化引數檔案（pfile檔案）
口絕：山的車又又又被（當鋪）吃
--進入初始化引數檔案存放路徑，建立initBOB.ora初始化引數檔案
[oracle@gc1 BOB]$ cd $ORACLE_HOME/dbs
[oracle@gc1 dbs]$ ls
??7?.txt ha hc_PROD.dat hc_yj.dat initdw.ora init.ora initPROD.ora lkPROD lkYJ orapwPROD orapwyj spfileyj.ora
--vi編輯初始化引數檔案（注意初始化引數檔案命名為init+oracle_sid.ora）
[oracle@gc1 dbs]$ vi initBOB.ora
control_files='/u01/app/oracle/oradata/BOB/disk1/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk2/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk3/control01.ctl'
undo_management=auto
undo_tablespace=undotbs
user_dump_dest=/u01/app/oracle/oradata/BOB/udump
background_dump_dest=/u01/app/oracle/oradata/BOB/bdump
core_dump_dest=/u01/app/oracle/oradata/BOB/cdump

initBOB.ora [+] 22,0-1 40%
:wq
:
:~
:~
:~
:~
command-line 2,2 All
"initBOB.ora" [New] 47L, 429C written 
--檢視剛新建的初始化引數檔案（注意檢查有沒錯誤）
[oracle@gc1 dbs]$ more initBOB.ora
sga_target=400M
db_name=BOB
control_files='/u01/app/oracle/oradata/BOB/disk1/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk2/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk3/control01.ctl'
undo_management=auto
undo_tablespace=undotbs
user_dump_dest=/u01/app/oracle/oradata/BOB/udump
background_dump_dest=/u01/app/oracle/oradata/BOB/bdump
core_dump_dest=/u01/app/oracle/oradata/BOB/cdump

第三步：啟庫至nomount狀態，執行建庫指令碼
--啟動資料庫至nomount狀態，檢視告警日誌檔案存放路徑
[oracle@gc1 dbs]$ export ORACLE_SID=BOB
[oracle@gc1 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 31 22:33:55 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/oradata/BOB/bd
                                                 ump
core_dump_dest string /u01/app/oracle/oradata/BOB/cd
                                                 ump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/oradata/BOB/ud
                                                 ump
--新開一連線視窗，檢視告警日誌資訊如下：
[root@gc1 ~]# su - oracle
[oracle@gc1 ~]$ cd $ORACLE_BASE/oradata
[oracle@gc1 oradata]$ ls
BOB PROD yj
[oracle@gc1 oradata]$ cd BOB
[oracle@gc1 BOB]$ ls
bdump cdump disk1 disk2 disk3 disk4 disk5 udump
[oracle@gc1 BOB]$ pwd
/u01/app/oracle/oradata/BOB
[oracle@gc1 BOB]$ cd /u01/app/oracle/oradata/BOB/bdump
[oracle@gc1 bdump]$ ls
alert_BOB.log
[oracle@gc1 bdump]$ tail -f alert_BOB.log
PMON started with pid=2, OS id=4383
PSP0 started with pid=3, OS id=4385
MMAN started with pid=4, OS id=4387
DBW0 started with pid=5, OS id=4389
LGWR started with pid=6, OS id=4391
CKPT started with pid=7, OS id=4393
SMON started with pid=8, OS id=4395
RECO started with pid=9, OS id=4397
MMON started with pid=10, OS id=4399
MMNL started with pid=11, OS id=4401
--檢視官方文件（），copy並修改建庫指令碼
官方文件查詢方式如下：
手工建庫與dbca建庫
點進去後，搜尋 create a如下：
手工建庫與dbca建庫
再找到
手工建庫與dbca建庫
最後點進去，找到下面這段sql:
手工建庫與dbca建庫
然後copy這段sql,並做以下一些修改：
1、刪除2個user、5個max及default tablespace部分內容；
2、修改資料庫名及對應的日誌檔案（注意日誌檔案一般最少存二份）與資料檔案存放路徑；
3、修改字符集（一般用zhs16gbk）；
4、修改undo tablespace表空間名，注意建庫中undo表空間名，一定要與之前initBOB.ora初始化引數檔案中定義的undo_tablespace名保持一致，否則會建庫失敗。
按以上方式修改後的建庫指令碼如下：
CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
--執行建庫指令碼
SQL> CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
  2 3 4 5 6 GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
  7 '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
  8 CHARACTER SET zhs16gbk
  9 NATIONAL CHARACTER SET AL16UTF16
 10 DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
 11 EXTENT MANAGEMENT LOCAL
 12 SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
 13 DEFAULT TEMPORARY TABLESPACE tempts1
 14 TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
 15 SIZE 20M REUSE
 16 UNDO TABLESPACE undotbs
 17 DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
 18 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
--執行建庫sql後告警日誌資訊如下：
CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Tue Apr 1 00:13:27 2014
Database mounted in Exclusive Mode
Tue Apr 1 00:16:16 2014
Successful mount of redo thread 1, with mount id 25537186
Assigning activation ID 25537186 (0x185aaa2)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/BOB/disk1/redo01.log
  Current log# 1 seq# 1 mem# 1: /u01/app/oracle/oradata/BOB/disk2/redo01.log
Successful open of redo thread 1
Tue Apr 1 00:16:17 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 1 00:16:17 2014
SMON: enabling cache recovery
Tue Apr 1 00:16:17 2014
create tablespace SYSTEM datafile '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL online
Tue Apr 1 00:16:52 2014
Completed: create tablespace SYSTEM datafile '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL online
Tue Apr 1 00:16:52 2014
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Tue Apr 1 00:17:02 2014
CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Successfully onlined Undo Tablespace 1.
Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Tue Apr 1 00:17:17 2014
create tablespace SYSAUX datafile '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Tue Apr 1 00:17:43 2014
Completed: create tablespace SYSAUX datafile '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Tue Apr 1 00:17:43 2014
CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
      SIZE 20M REUSE
   
Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
      SIZE 20M REUSE
   
Tue Apr 1 00:17:44 2014
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Tue Apr 1 00:17:44 2014
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Tue Apr 1 00:17:47 2014
SMON: enabling tx recovery
Tue Apr 1 00:17:51 2014
Threshold validation cannot be done before catproc is loaded.
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=13, OS id=13663
Tue Apr 1 00:17:54 2014
Completed: CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk5/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
第四步：執行三個指令碼，建立使用者預設表空間
sysdba使用者執行 $ORACLE_HOME/rdbms/admin目錄下的catalog.sql（建立資料字典）與catproc.sql（建立儲存過程）指令碼，
system使用者執行$ORACLE_HOME/sqlplus/admin目錄下的pupbld.sql指令碼（讓其它使用者如scott可登入系統）
--執行catalog.sql指令碼
SQL> @?/rdbms/admin/catalog.sql
--執行catproc.sql指令碼
SQL> @?/rdbms/admin/catproc.sql
--執行pupbld.sql指令碼（必須用system使用者，且非sysdba許可權）
SQL> conn system/manager
Connected.
SQL> @?/sqlplus/admin/pupbld.sql
--檢視資料庫當前連線狀態及表空間與資料檔案
SQL> conn / as sysdba
Connected.
SQL> select instance_name,status,host_name from v$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
BOB OPEN gc1
1 row selected.
SQL> select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
UNDOTBS UNDO ONLINE
SYSAUX PERMANENT ONLINE
TEMPTS1 TEMPORARY ONLINE
4 rows selected.
SQL> col name for a50;
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/BOB/disk3/system01.dbf
         2 /u01/app/oracle/oradata/BOB/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf
3 rows selected.
--建立users表空間，並將其設為預設表空間
SQL> create tablespace users datafile '/u01/app/oracle/oradata/BOB/disk3/users01.dbf' size
  2 50m autoextend on next 10m maxsize unlimited;
Tablespace created.
SQL> alter database default tablespace users;
Database altered.

到此為止，手工建庫已完成。
二、建庫失敗，要重新建庫怎麼辦？
1、shutdown abort資料庫；
2、刪除oradata下該資料庫名（BOB）的資料夾及其檔案；
3、修改初始化引數檔案中的undo_tablespace名（不可於建庫失敗那次的重名）及相應的建庫指令碼中的undo tablespace名稱（與初始化引數檔案保持一致）；
4、修改建庫指令碼中的TEMPORARY TABLESPACE名（不可於建庫失敗那次的重名）。
三、11g、12C手工建庫與10g區別
11g、12C建庫方式一樣，與10g的區別在於初始化引數檔案的建立及建庫指令碼不同。下面分別附上11g初始化引數檔案與建庫指令碼
--11g初始化引數檔案內容如下（紅色字型為10g不同部分）：
[oracle@localhost dbs]$ more initBOB.ora
memory_target=800M
db_name=BOB
control_files='/u01/app/oracle/oradata/BOB/disk1/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk2/control01.ctl',
'/u01/app/oracle/oradata/BOB/disk3/control01.ctl'
undo_management=auto
undo_tablespace=undotbs
diagnostic_dest=/u01/app/oracle/oradata/BOB/dump
即10g的sga_target改為mermory_target，且11g需設為800M，另外10g中三個dump目錄，在11g中只需設一個，且名為diagnostic_dest。
--11g官方建庫指令碼 
CREATE DATABASE mynewdb
   USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
修改方式與10g一樣，只是可以保留default tablespace users部分內容（10g中因為沒有設定users表空間的datafiles，所以有bug，需刪掉這部分內容），修改後內容如下：
CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/BOB/disk5/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk4/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
--檢視告警日誌資訊
SQL> select instance_name,status,host_name from v$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
BOB STARTED localhost.localdomain
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/oradata/BOB/du
                                                 mp/diag/rdbms/bob/BOB/trace
core_dump_dest string /u01/app/oracle/oradata/BOB/du
                                                 mp/diag/rdbms/bob/BOB/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/oradata/BOB/du
                                                 mp/diag/rdbms/bob/BOB/trace

[oracle@localhost rdbms]$ cd /u01/app/oracle/oradata/BOB/dump/diag/rdbms/bob/BOB/trace
[oracle@localhost trace]$ ls
alert_BOB.log BOB_mman_3241.trc BOB_mman_3241.trm BOB_ora_3183.trc BOB_ora_3183.trm BOB_vktm_3227.trc BOB_vktm_3227.trm
[oracle@localhost trace]$ tail -f alert_BOB.log
SMON started with pid=13, OS id=3249
Mon Feb 24 19:17:40 2014
MMON started with pid=15, OS id=3253
Mon Feb 24 19:17:40 2014
MMNL started with pid=16, OS id=3255
Mon Feb 24 19:17:40 2014
RECO started with pid=14, OS id=3251
Mon Feb 24 19:17:40 2014
LGWR started with pid=11, OS id=3245
ORACLE_BASE from environment = /u01/app/oracle
Mon Feb 24 19:31:55 2014
CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/BOB/disk5/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk4/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Database mounted in Exclusive Mode
Lost write protection disabled
Mon Feb 24 19:32:55 2014
Successful mount of redo thread 1, with mount id 22260907
Assigning activation ID 22260907 (0x153acab)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/BOB/disk1/redo01.log
  Current log# 1 seq# 1 mem# 1: /u01/app/oracle/oradata/BOB/disk2/redo01.log
Successful open of redo thread 1
Mon Feb 24 19:32:56 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Feb 24 19:32:56 2014
SMON: enabling cache recovery
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL online
Mon Feb 24 19:33:18 2014
Completed: create tablespace SYSTEM datafile '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL online
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Mon Feb 24 19:33:52 2014
Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/oradata/BOB/disk4/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 UNDOTBS DATAFILE '/u01/app/oracle/oradata/BOB/disk3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/oradata/BOB/disk3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/BOB/disk4/temp01.dbf'
      SIZE 20M REUSE
   
Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/BOB/disk4/temp01.dbf'
      SIZE 20M REUSE
   
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/BOB/disk5/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    SEGMENT SPACE MANAGEMENT MANUAL
Mon Feb 24 19:34:28 2014
Completed: CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/BOB/disk5/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    SEGMENT SPACE MANAGEMENT MANUAL
ALTER DATABASE DEFAULT TABLESPACE USERS
Completed: ALTER DATABASE DEFAULT TABLESPACE USERS
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
Mon Feb 24 19:34:41 2014
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
Mon Feb 24 19:34:45 2014
SMON: enabling tx recovery
Starting background process SMCO
Mon Feb 24 19:34:48 2014
SMCO started with pid=18, OS id=3438
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Feb 24 19:34:52 2014
QMNC started with pid=19, OS id=3440
Completed: CREATE DATABASE BOB
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk1/redo01.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/BOB/disk1/redo02.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/BOB/disk1/redo03.log',
                    '/u01/app/oracle/oradata/BOB/disk2/redo03.log') SIZE 100M
   CHARACTER SET zhs16gbk
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/BOB/disk3/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk4/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/BOB/disk5/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/BOB/disk4/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/BOB/disk3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
四、dbca建庫（10g）
--root使用者開啟圖形畫介面，oracle使用者執行dbca
手工建庫與dbca建庫
--進入圖形畫介面後，共有13步，比較簡單，在此不詳述，注意字符集設定為zhs16gbk。
另外，說下dbca刪庫
手工建立的資料庫，在執行dbca刪庫時，圖形介面不會顯示出來，
可通過編輯/etc/oratab檔案，加上一段程式碼（紅色內容），如下：
[oracle@gc1 etc]$ vi oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
yj:/u01/app/oracle/product/10.2.0/db_1:N
ZHEN:/u01/app/oracle/product/10.2.0/db_1:N
BOB:/u01/app/oracle/product/10.2.0/db_1:N
儲存後，再次執行dbca刪庫，就可以看到了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1139017/，如需轉載，請註明出處，否則將追究法律責任。

相關文章