Create/Drop Database

Create a Database

NOTE：

• 將下文包含 SID 的文字改成實際的資料庫名稱。
• ORACLE_BASE=/opt/oracle
• ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
   

建立 pfileSID.ora：
Oracle DB 10g)

*.audit_file_dest='/opt/oracle/admin/SID/adump'
*.background_dump_dest='/opt/oracle/admin/SID/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/SID/control01.ctl','/opt/oracle/oradata/SID/control02.ctl','/opt/oracle/oradata/SID/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/SID/cdump'
*.db_block_size=8192
*.db_files=1000
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SID'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SIDXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/opt/oracle/admin/SID/udump'

Oracle DB 11g)

*._optimizer_use_feedback=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/opt/oracle/admin/SID/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/opt/oracle/oradata/SID/control01.ctl','/opt/oracle/fast_recovery_area/SID/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='SID'
*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/opt/oracle'
*.open_cursors=300
*.optimizer_dynamic_sampling=0
*.pga_aggregate_target=1073741824
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=3221225472
*.undo_tablespace='UNDOTBS1'

* 修改 SID 成實際的名稱

* 調整 sga_target (4GB, 4294967296 bytes / 3GB, 3221225472 bytes)

* 調整 db_block_size (8192/16384/32768)

* 調整 db_files

轉換成 spfile

cp pfileSID.ora $ORACLE_HOME/dbs

export ORACLE_SID=SID
sqlplus /nolog

SQL> connect / as sysdba
SQL> create spfile='/opt/oracle/product/10.2.0/db_1/dbs/spfileSID.ora' from pfile='/opt/oracle/product/10.2.0/db_1/dbs/pfileSID.ora';
SQL> exit  

建立目錄及 oratab

su - oracle
mkdir -p /opt/oracle/admin/sid/{adump,bdump,cdump,udump}
mkdir -p /opt/oracle/oradata/sid
mkdir -p /opt/oracle/flash_recovery_area/SID   <== 目錄名要大寫

編輯 /etc/oratab：

SID:/opt/oracle/product/10.2.0/db_1:N

建立 createDB.sql：

• 修改 SYS 密碼
• 修改檔案路徑
• 修改 redolog size

CREATE DATABASE SID
   USER SYS IDENTIFIED BY mypass
   USER SYSTEM IDENTIFIED BY mypass
   LOGFILE GROUP 1 ('/opt/oracle/oradata/SID/redo01.log') SIZE 100M,
           GROUP 2 ('/opt/oracle/oradata/SID/redo02.log') SIZE 100M,
           GROUP 3 ('/opt/oracle/oradata/SID/redo03.log') SIZE 100M
   MAXLOGFILES 10
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/opt/oracle/oradata/SID/system01.dbf' 
      SIZE 325M REUSE AUTOEXTEND ON MAXSIZE 32767M
   SYSAUX DATAFILE '/opt/oracle/oradata/SID/sysaux01.dbf' 
      SIZE 325M REUSE AUTOEXTEND ON MAXSIZE 10240M
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/opt/oracle/oradata/SID/temp01.dbf' 
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE 2048M
   UNDO TABLESPACE undotbs 
      DATAFILE '/opt/oracle/oradata/SID/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

TIPs:

redo LOG 在正式環境，建議可用 2G x 4

開始建立

確認環境變數
$ echo $ORACLE_SID
win
$ echo $ORACLE_HOME
/opt/oracle/product/10.2.0/db_1

$ sqlplus /nolog

SQL> connect / as sysdba
SQL> startup nomount
SQL> @createDB.sql

DB 建立完後，還需要執行幾個 SQL

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

SQL> conn system/pass
SQL> @?/sqlplus/admin/pupbld.sql  

PUPBLD.SQL       Create user profiles，必須用 SYSTEM 執行，執行過程中會出現一些錯誤是正常的；如果沒有執行，一般用戶會無法連線 DB。

更多其他的 SQL 可參閱 http://docs.oracle.com/cd/B14117_01/...s.htm#REFRN005

 

Drop a Database：

$ export ORACLE_SID=<my_SID>
$ sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive restrict
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2095672 bytes
Variable Size             109053384 bytes
Database Buffers          163577856 bytes
Redo Buffers                6291456 bytes
Database mounted.

SQL> drop database;

Database dropped.

手動刪除相關目錄

cd $ORACLE_BASE/oradata
rm -rf <my_SID>/

cd $ORACLE_BASE/admin
rm -rf <my_SID>/ 

編輯 /etc/oratab

<my_SID>:/opt/oracle/product/10.2.0/db_1:N    將這行移除