在上一篇介紹了使用EM建立physical standby，相信大家以愛上EM，但我還是得說明如何使用SQL來建置physical standby，畢竟這樣才能真正了解整個作業流程。
承接上篇

透過pfile建立spfile

create spfile from pfile='F:\standbybk\testdg.ora';

複製Datafile和修改過的Control file(該controlfile應為standby controlfile)以及修改過init.ora to standby

(由於測試DB大小5GB，所以利用COPY Datafile來完成，當然正式DB大小800GB可沒這麼多時間慢慢複製，所以小弟採rman duplicate+NFS，真的省了很多時間)

Add standby redo log

Alter database add standby logfile group 4('d:\oradata\pridg\STANDBYRD04.log') size 50M;(大小建議和online redo相同)

Physics Standby 設定

oradim 建立新的Oracle service(如已有instance或linux,unix平台可以跳過)
oradim -NEW -SID sdb2 -STARTMODE auto -PFILE "F:\standbybk\dgini.ora”
建立pwdfile(如已有相同sys密碼可以跳過)
orapwd file=D:\oracle\product\10.2.0\db_1\database\PWsdb2.ora password=vlifedba entries=30

修改standby db init.ora

sdb2.__db_cache_size=436207616
sdb2.__java_pool_size=4194304
sdb2.__large_pool_size=4194304
sdb2.__shared_pool_size=159383552
sdb2.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/sdb2/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/sdb2/bdump'
*.compatible='10.2.0.2.0'
*.control_files='D:\oradata\sdb2\SBCONTROL01.CTL'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/sdb2/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='pridg'
*.DB_UNIQUE_NAME='sdb2'
*.DB_FILE_NAME_CONVERT='D:\oradata\pridg','D:\oradata\sdb2'
*.FAL_CLIENT='sdb2'
*.FAL_SERVER='pridg'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pridg,sdb2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\oradata\sdb2arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=sdb2'

*.LOG_ARCHIVE_DEST_2='SERVICE=pridgLGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=pridg'

*.standby_archive_dest='D:\oradata\sdb2arch'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='D:\oradata\priarch','D:\oradata\sdb2arch'
*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pridgXDB)'
*.job_queue_processes=10
*.nls_language='TRADITIONAL CHINESE'
*.nls_length_semantics='CHAR'
*.nls_territory='TAIWAN'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/sdb2/udump'

建立spfile

create spfile from pfile='D:\backup\inittestdg.ora';
Add standby redo log
Alter database add standby logfile group 4('d:\oradata\sdb2\STANDBYRD04.log') size 50M;(大小建議和online redo相同)

開啟redo apply

alter database recover managed standby database disconnect from session;

如有錯誤(先註冊redo log)

alter database recover managed standby database finish;

再開啟redo apply

暫停redo apply語法

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

check sync
alter system switch logfile;
select max(sequence#) from v$archived_log;

當switch logfile後primary和standby archived_log sequence相同就大功告成了

雖然沒有簡單明瞭的UI介面，但卻可以讓你DG的基礎更穩固，所以還是要學好使用SQL來建置。