Create 2nd new DB instance with new SID

必要的條件

• 已安裝 Oracle Database Server
• 已經可以使用 SYS/SYSTEM 登入 DB
• 有足夠的 RAM 可用
• 有足夠的儲存空見可用

決定新 SID

su - oracle
export ORACLE_SID=win_new
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 

TIPs:

查詢 NLS_LANG
SQL> select userenv('language') from dual;

建立 pfile

使用原有 instance 的 pfile 作為範本

pfilewin.ora（原有的 Instance - win）

win.__db_cache_size=88080384
win.__java_pool_size=4194304
win.__large_pool_size=4194304
win.__shared_pool_size=171966464
win.__streams_pool_size=4194304
*.audit_file_dest='/opt/oracle/admin/win/adump'
*.background_dump_dest='/opt/oracle/admin/win/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/win/control01.ctl','/opt/oracle/oradata/win/control02.ctl','/opt/oracle/oradata/win/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/win/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='win'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=winXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=281018368
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/win/udump'

新的 pfile 改成這樣，並取名為 pfilewin_new.ora

• 將前面幾行 win.xxxx 刪除
• 將每項有包含 win 的改成 win_new
• db_block_size 視 DB 的規模調整，預設是 8192
• db_name 必須是新的 SID
• db_files 自行調整（預設是 200）
• sga_target 視記憶體使用狀況調整（2G = 2147483648）

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

建立相關的目錄及權限

su - oracle
mkdir -p /opt/oracle/admin/win_new/adump
mkdir -p /opt/oracle/admin/win_new/bdump
mkdir -p /opt/oracle/admin/win_new/cdump
mkdir -p /opt/oracle/admin/win_new/udump
mkdir /opt/oracle/oradata/win_new
mkdir /opt/oracle/flash_recovery_area/WIN_NEW 

編輯 oratab

/etc/oratab
必須使用 root

win:/opt/oracle/product/10.2.0/db_1:N
win_new:/opt/oracle/product/10.2.0/db_1:N  <== 加這一行

TIP:

格式為 <ORACLE_SID>:<ORACLE_HOME>:N

建立 spfile

NOTE: spfile 的檔案路徑：$ORACLE_HOME/dbs/spfile<SID>.ora

export ORACLE_SID=win_new
sqlplus /nolog

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

啟動 Instance

export ORACLE_SID=win_new
sqlplus /nolog

SQL> connect / as sysdba
SQL> startup nomount 
SQL> shutdown

建立資料庫

新增 createDB.sql

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

TIPs:

如何查詢原有 Database 的 CHARACTER 設定
執行 select * from nls_database_parameters;
- NLS_CHARACTERSET  WE8ISO8859P1
- NLS_NCHAR_CHARACTERSET  AL16UTF16

UNDO TABLESPACE name 必須是 undotbs1，因為 spfile 的參數

TEMPORARY TABLESPACE temp 視需要調整 MAXSIZE

redo LOG 建議可用 2G x 4

執行 createDB.sql

export ORACLE_SID=win_new
sqlplus /nolog

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

TIPs:

如果建立 DB 失敗，可以檢查 alert_xxx.log 及 trace log

如果需要重新建立 DB 可以參閱 Create/Drop Database

建立 catalog

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

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

TIPs:

CATALOG.SQL     Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.

CATPROC.SQL     Runs all scripts required for or used with PL/SQL.

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

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

Verify the Dictionary views created

SQL> select name from v$database;

重啟 DB

SQL> conn / as sysdba
SQL> shutdown
SQL> startup 

重啟 Listener

lsnrctl stop
lsnrctl start