手工建立、刪除11gR2資料庫

it_newbalance發表於2013-05-29
資料庫
閱讀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；

 
1[root@OCM11g ~]# su - oracle
2OCM11g-> env | grep ORA
3ORACLE_BASE=/u01/app/oracle
4ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
5OCM11g->

2 確認環境變數，本例中選擇ORACLE_SID=manualdb,ORACLE_HOME選用原有的/u01/app/oracle/product/11.2.0/db_1；        

01OCM11g-> id
02uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
03OCM11g-> uname -rm
042.6.18-194.el5 x86_64
05OCM11g-> export ORACLE_SID=manualdb
06OCM11g-> env | grep ORA
07ORACLE_SID=manualdb
08ORACLE_BASE=/u01/app/oracle
09ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
10OCM11g->

3 建立初始化引數檔案，這裡將引數檔案放到系統預設的路徑下，即$ORACLE_HOME/dbs,命名為initmanualdb.ora,其內容如下：

 
1OCM11g-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initmanualdb.ora
2db_name=manualdb
3memory_target=600m
4OCM11g->

簡單起見，我們只在初始化引數檔案中指定了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:

1OCM11g-> mkdir -p /u02/manualdb/oradata

online log:

1OCM11g-> mkdir -p /u02/manualdb/onlinelog
2OCM11g-> ll /u02/manualdb/
3total 8
4drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 onlinelog
5drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 oradata
6OCM11g->

5 啟動manualdb例項到NOMOUNT狀態，其實，在該環境下，我們的控制檔案尚未生成，例項至多也只能載入到NOMOUNT狀態：

01OCM11g-> id
02uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
03OCM11g-> env | grep ORA
04ORACLE_SID=manualdb
05ORACLE_BASE=/u01/app/oracle
06ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
07OCM11g-> sqlplus
08
09SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 10:59:32 2012
10
11Copyright (c) 1982, 2009, Oracle. All rights reserved.
12
13Connected to an idle instance.
14
15SQL> startup
16ORACLE instance started.
17
18Total System Global Area 626327552 bytes
19Fixed Size 2215944 bytes
20Variable Size 373297144 bytes
21Database Buffers 243269632 bytes
22Redo Buffers 7544832 bytes
23ORA-00205: error in identifying control file, check alert log for more info
24
25
26SQL>

上述報錯ORA-00205提示控制檔案找不到，正是我們所預見的，因為當前控制檔案還未生成，所以資料庫沒法MOUNT，說明資料庫是NOMOUNT狀態。這時，也可以看到相關的後臺程式已經啟動：

01SQL> !
02OCM11g-> ps -ef | grep ora_|grep -v grep
03oracle 14885 1 0 11:02 ? 00:00:00 ora_pmon_manualdb
04oracle 14887 1 0 11:02 ? 00:00:00 ora_vktm_manualdb
05oracle 14891 1 0 11:02 ? 00:00:00 ora_gen0_manualdb
06oracle 14893 1 0 11:02 ? 00:00:00 ora_diag_manualdb
07oracle 14895 1 0 11:02 ? 00:00:00 ora_dbrm_manualdb
08oracle 14897 1 0 11:02 ? 00:00:00 ora_psp0_manualdb
09oracle 14899 1 0 11:02 ? 00:00:00 ora_dia0_manualdb
10oracle 14903 1 6 11:02 ? 00:00:27 ora_mman_manualdb
11oracle 14905 1 0 11:02 ? 00:00:01 ora_dbw0_manualdb
12oracle 14907 1 0 11:02 ? 00:00:00 ora_lgwr_manualdb
13oracle 14909 1 0 11:02 ? 00:00:00 ora_ckpt_manualdb
14oracle 14911 1 0 11:02 ? 00:00:00 ora_smon_manualdb
15oracle 14913 1 0 11:03 ? 00:00:00 ora_reco_manualdb
16oracle 14915 1 0 11:03 ? 00:00:00 ora_mmon_manualdb
17oracle 14917 1 0 11:03 ? 00:00:00 ora_mmnl_manualdb
18OCM11g->

6 執行下述CREATE DATABASE的命令，開始手工建立Oracle 資料庫：

01SQL> conn / as sysdba;
02Connected.
03SQL> select open_mode from v$database;
04select open_mode from v$database
05 *
06ERROR at line 1:
07ORA-01507: database not mounted
08
09
10SQL> CREATE DATABASE manualdb
11 2 USER SYS IDENTIFIED BY oracle
12 3 USER SYSTEM IDENTIFIED BY oracle
13 4 LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
14 5 GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
15 6 MAXLOGFILES 5
16 7 MAXLOGMEMBERS 5
17 8 MAXLOGHISTORY 1
18 9 MAXDATAFILES 100
19 10 CHARACTER SET AL32UTF8
20 11 NATIONAL CHARACTER SET AL16UTF16
21 12 EXTENT MANAGEMENT LOCAL
22 13 DATAFILE '/u02/manualdb/oradata/system01.dbf' SIZE 300M REUSE
23 SYSAUX DATAFILE '/u02/manualdb/oradata/sysaux01.dbf' SIZE 300M REUSE
24 DEFAULT TABLESPACE users
25 DATAFILE '/u02/manualdb/oradata/users01.dbf'
26 17 SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
27 18 DEFAULT TEMPORARY TABLESPACE tempts1
28 19 TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
29 20 SIZE 20M REUSE
30 21 UNDO TABLESPACE undotbs
31 22 DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
32 23 SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
33
34Database created.
35
36SQL> select name,open_mode from v$database;
37
38NAME OPEN_MODE
39--------- --------------------
40MANUALDB READ WRITE
41
42SQL>

7 建立資料字典檢視，分別執行下述指令碼來完成：

1SQL> @?/rdbms/admin/catalog.sql
2......
3......
4SQL> @?/rdbms/admin/catproc.sql
5......
6......
7SQL> @?/sqlplus/admin/pupbld.sql
8......
9......

上述3個指令碼的說明見下表：

ScriptDescription
The Scripts and descriptions
CATALOG.SQLCreates the views of the data dictionary tables, the dynamic performance views, and public sy nonyms for many of the views.Grants PUBLIC access to the synonyms.
CATPROC.SQLRuns all scripts required for or used with PL/SQL.
PUPBLD.SQLRequired for SQL*Plus. Enables SQL*Plus to disable commands by user.

8 至此，我們完成了手工建立Oracle 資料庫。

01SQL> select * from v$version;
02
03BANNER
04--------------------------------------------------------------------------------
05Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
06PL/SQL Release 11.2.0.1.0 - Production
07CORE 11.2.0.1.0 Production
08TNS for Linux: Version 11.2.0.1.0 - Production
09NLSRTL Version 11.2.0.1.0 - Production
10
115 rows selected.
12
13SQL> select name,open_mode from v$database;
14
15NAME OPEN_MODE
16--------- --------------------
17MANUALDB READ WRITE
18
191 row selected.
20
21SQL> select name from v$datafile
22 2 union
23 3 select name from v$controlfile
24 4 union
25 5 select member from v$logfile;
26
27NAME
28--------------------------------------------------------------------------------
29/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlmanualdb.dbf
30/u02/manualdb/onlinelog/redo01a.log
31/u02/manualdb/onlinelog/redo02a.log
32/u02/manualdb/oradata/sysaux01.dbf
33/u02/manualdb/oradata/system01.dbf
34/u02/manualdb/oradata/undotbs01.dbf
35/u02/manualdb/oradata/users01.dbf
36
377 rows selected.
38
39SQL>

9 最後，如果該資料庫不需要的話。我們可以執行手工刪除資料庫，當然必須要將資料庫啟動到MOUNT RESTRICT狀態來刪除：

如何確認例項是否是RESTRICTED MODE：

01SQL> select instance_name,status,startup_time,logins from v$instance;
02
03INSTANCE_NAME STATUS STARTUP_TIME LOGINS
04--------------- ------------ ------------------- ----------
05manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED
06
071 row selected.
08
09SQL> alter system enable restricted session;
10
11System altered.
12
13SQL> select instance_name,status,startup_time,logins from v$instance;
14
15INSTANCE_NAME STATUS STARTUP_TIME LOGINS
16--------------- ------------ ------------------- ----------
17manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED
18
191 row selected.
20
21SQL> alter system disable restricted session;
22
23System altered.
24
25SQL> select instance_name,status,startup_time,logins from v$instance;
26
27INSTANCE_NAME STATUS STARTUP_TIME LOGINS
28--------------- ------------ ------------------- ----------
29manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED
30
311 row selected.
32
33SQL>

通過檢視V$INSTANCE動態效能檢視的LOGINS欄位，如果該值為ALLOWED，說明例項是正常啟動，並未進入RESTRICTED MODE，普通使用者可以正常訪問；如果該值為RESTRICTED說明是RESTRICTED MODE，即需要具有RESTRICTED SESSION系統許可權的使用者方可訪問；

手工刪除資料庫：

01SQL> select instance_name,status,startup_time,logins from v$instance;
02
03INSTANCE_NAME STATUS STARTUP_TIME LOGINS
04--------------- ------------ ------------------- ----------
05manualdb MOUNTED 2012/03/30 13:38:52 ALLOWED
06
071 row selected.
08
09SQL> drop database;
10drop database
11*
12ERROR at line 1:
13ORA-12719: operation requires database is in RESTRICTED mode
14
15
16SQL> alter system enable restricted session;
17
18System altered.
19
20SQL> select instance_name,status,startup_time,logins from v$instance;
21
22INSTANCE_NAME STATUS STARTUP_TIME LOGINS
23--------------- ------------ ------------------- ----------
24manualdb MOUNTED 2012/03/30 13:38:52 RESTRICTED
25
261 row selected.
27
28SQL> drop database;
29
30Database dropped.
31
32Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
33With the Partitioning, OLAP, Data Mining and Real Application Testing options
34SQL>

此時，Oracle自動刪除該資料庫的所有資料檔案、聯機重做日誌檔案、控制檔案以及初始化引數檔案。從alert日誌檔案裡可以看到下述資訊：

01Fri Mar 30 13:45:33 2012
02drop database
03Deleted file /u02/manualdb/oradata/system01.dbf
04Deleted file /u02/manualdb/oradata/sysaux01.dbf
05Deleted file /u02/manualdb/oradata/undotbs01.dbf
06Deleted file /u02/manualdb/oradata/users01.dbf
07Deleted file /u02/manualdb/onlinelog/redo01a.log
08Deleted file /u02/manualdb/onlinelog/redo02a.log
09Deleted file /u02/manualdb/oradata/temp01.dbf
10Deleted file /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_manualdb.f

10 簡單總結，可見手工建立、刪除資料庫也很簡單。當然，在第6步中執行手工建庫的命令中還有諸多其它選項，比如選擇OMF，這時，只要在引數檔案中指定DB_CREATE_FILE_DEST引數，那麼建庫的命令將更加簡單。

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

相關文章