Create/Drop Database

Create a Database

NOTE：

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

建立 pfileSID.ora：
for 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'
*.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'

for 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/oradata/SID/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='SID'
*.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='UNDOTBS'

* 修改 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

// for 10g
mkdir -p /opt/oracle/admin/sid/{adump,bdump,cdump,udump}
// for 11g
mkdir -p /opt/oracle/admin/sid/adump

mkdir -p /opt/oracle/oradata/sid

編輯 /etc/oratab：

// for 10g
SID:/opt/oracle/product/10.2.0/db_1:N

// for 11g
SID:/opt/oracle/product/11.2.4/db_1:N 

建立 createDB.sql：

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

Callstack:
    at User:Alang/IT_專案工作/Oracle_DBA_筆記/Create//Drop_Database

MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException: reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown.
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptVar expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptVar.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Evaluate (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state, Boolean evaluateProperties) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptAccess expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptAccess.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptCall expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptCall.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptSequence expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Expr.DekiScriptSequence.VisitWith[DekiScriptExpressionEvaluationState,Range] (IDekiScriptExpressionVisitor`2 visitor, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 
  at MindTouch.Deki.Script.Compiler.DekiScriptExpressionEvaluation.Visit (MindTouch.Deki.Script.Expr.DekiScriptReturnScope expr, DekiScriptExpressionEvaluationState state) [0x00000] in <filename unknown>:0 

確認環境變數
$ 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

確認結果
SQL> select instance_name, status from v$instance; 

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

SQL> @?/rdbms/admin/catbundle.sql psu  apply

SQL> @?/rdbms/admin/utlrp.sql

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

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    將這行移除