已登入: Logged in as: 登入
搜尋:
Page last modified 14:34, 30 May 2018 by alang

Create/Drop Database

    版本為 12:59, 30 Nov 2024

    到這個版本。

    返回到 版本存檔.

    查閱目前版本

    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='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
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
    createDB.sql:
    reference to undefined name 'syntax' Exception of type 'MindTouch.Deki.Script.Runtime.DekiScriptUndefinedNameException' was thrown. (click for details)

    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

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

    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

     

    如果有 Apply PatchSet (PSU) 時

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

    最後做一次 recompile

    @?/rdbms/admin/utlrp.sq

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

    Powered by MindTouch Core