RiCo技術農場

放颱風假(當過兵的都知道，放假為軍人的第二生命，上班族當然不例外)，小弟剛好有時間來整理自己以前的文件，由於第三章是安裝Oracle EM10 Grid Control,Oracle Agent，第四章是操作EM建立physical standby，因為文件都是貼圖亂沒內容所以直接來第五章。

 

文章均為自己見解,如有錯誤還請指教

雖然這份文件在windows平台測試，和正式環境有差異(正式環境DG建置在R6 Unix平台)，不過建置過程其實大同小異，只需注意幾個重點:

1.小弟正式環境採ASM，所以須注意路徑(+DATADG)

2.修改*.ora參數檔,control file請勿填寫由ASM自行建立

3.如使用NFS需使用Oracle規定指令才能mount成功

4.請再三確認rman backup及flash_recovery_area size，以免白花時間在備份上

 

 

確認primary and standby archive log mode

修改 archive mode

Shutdown immediate;

Startup mount;

Alter database archivelog;

Alter database open;

開啟FlashBack on(建議standby )

alter database flashback on;

開啟 forced logging

建立physics standby control file

alter database create standby controlfile as 'F:\standbybk\sdbcontrol01.ctl';

建立primary init.ora

複製該檔等等做為physics standby sdb2.ora

修改 pridg.ora

pridg.__db_cache_size=436207616

pridg.__java_pool_size=4194304

pridg.__large_pool_size=4194304

pridg.__shared_pool_size=159383552

pridg.__streams_pool_size=0

*.audit_file_dest='D:\oracle\product\10.2.0/admin/pridg/adump'

*.background_dump_dest='D:\oracle\product\10.2.0/admin/pridg/bdump'

*.compatible='10.2.0.2.0'

*.control_files='D:\oradata\pridg\control01.ctl'

*.core_dump_dest='D:\oracle\product\10.2.0/admin/pridg/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='pridg'

*.DB_UNIQUE_NAME='pridg'

*.DB_FILE_NAME_CONVERT='D:\oradata\pridg','D:\oradata\sdb2'

*.FAL_CLIENT='pridg'

*.FAL_SERVER='sdb2'

*.STANDBY_FILE_MANAGEMENT=AUTO

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pridg,sdb2)'

*.LOG_ARCHIVE_DEST_1='LOCATION=D:\oradata\priarch

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=pridg'

*.LOG_ARCHIVE_DEST_2='SERVICE=sdb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=sdb2'

*.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/pridg/udump'

 

透過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=pridg LGWR 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相同就大功告成了