[20170206]关于Data Guard Gap Detection and Resolution3.txt
--测试看看一些参数不设置是否可以解决gap问题.相关链接:
http://blog.itpub.net/267265/viewspace-2133106/
http://blog.itpub.net/267265/viewspace-2133107/
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//节前花了一个上午安装一台dg,我一般安装dataguard,自己有一个标准的文档,dataguard实例名我一般选择主库实例名后面加dg
--//[注:如果长于8个字符,选择d1,d2.]
--//为了测试方便,使用文本参数文件,这样测试方便一些.
SYS@book> create pfile='/tmp/book.ora' from spfile ;
File created.
2.说明我一般修改如下参数:
--主库:
*.log_archive_config='DG_CONFIG=(book,bookdg)'
*.fal_client='book'
*.fal_server='bookdg'
*.standby_file_management='auto'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book'
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
*.db_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.db_unique_name='BOOK'
--备库:
*.log_archive_config='DG_CONFIG=(book,bookdg)'
*.fal_client='bookdg'
*.fal_server='book'
*.standby_file_management='auto'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bookdg'
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=book'
*.db_file_name_convert='mnt/ramdisk/book','/mnt/ramdisk/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.db_unique_name='bookdg'
*.service_names='book'
--//注:备库我一般service_names设置跟主库一样.
3.测试前修改参数文件参数:
--主库:
#*.log_archive_config='DG_CONFIG=(book,bookdg)'
#*.fal_client='book'
#*.fal_server='bookdg'
*.standby_file_management='auto'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book'
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
*.db_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
#*.db_unique_name='BOOK'
--//注:取消fal_client,fal_server,log_archive_config,db_unique_name设置,并且log_archive_dest_N保持后面的DB_UNIQUE_NAME=XXX.
--//注意log_archive_dest_2的DB_UNIQUE_NAME=bookdg在本次测试中不存在.
--备库:
#*.log_archive_config='DG_CONFIG=(book,bookdg)'
#*.fal_client='bookdg'
#*.fal_server='book'
*.standby_file_management='auto'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bookdg'
*.log_archive_dest_2='SERVICE=book LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=book'
*.db_file_name_convert='mnt/ramdisk/book','/mnt/ramdisk/book'
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
#*.db_unique_name='bookdg'
*.service_names='book'
4.测试:
A.关闭2边数据库.使用对应文本参数文件启动看看.
B.先启动主库:
SYS@book> startup pfile='/tmp/book.ora'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book> alter system archive log current ;
System altered.
/
/
/
/
/
/
SYS@book> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog/book
Oldest online log sequence 331
Next log sequence to archive 333
Current log sequence 333
C.启动备库:
SYS@bookdg> startup nomount pfile='/tmp/bookdg.ora';
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
SYS@bookdg> alter database mount standby database;
Database altered.
SYS@bookdg> show parameter unique
NAME TYPE VALUE
-------------- ------ -------
db_unique_name string book
--//???可以发现不是我想像的那样.现在2边db_unique_name是一样的.也就是与主库设置的log_archive_dest_2里面的db_unique_name不同.
--//主库执行:
SYS@book> alter system set log_archive_dest_state_2=enable ;
System altered.
--//备库查询:
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 24444 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 24442 IDLE LGWR 3 1 334 108 1 0
ARCH 24434 CLOSING ARCH 4 1 324 14336 1106 0
SYS@bookdg> @ &r/dg/dg_mess
FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
------------------------ ------------- ---------- ----------- ---------- --- ------------------- --------------------------------------------------------------------------------
Log Transport Services Informational 0 1 0 NO 2017-02-06 14:45:26 ARC0: Archival started
Log Transport Services Informational 0 2 0 NO 2017-02-06 14:45:26 ARC0: Becoming the 'no FAL' ARCH
Log Transport Services Informational 0 3 0 NO 2017-02-06 14:45:26 ARC0: Becoming the 'no SRL' ARCH
Log Transport Services Informational 0 4 0 NO 2017-02-06 14:45:26 ARC0: Becoming the heartbeat ARCH
Log Transport Services Informational 0 5 0 NO 2017-02-06 14:45:26 ARC0: Becoming the active heartbeat ARCH
Remote File Server Informational 0 6 0 NO 2017-02-06 14:46:41 RFS[1]: Assigned to RFS process 24440
Log Transport Services Control 0 7 0 YES 2017-02-06 14:46:41 ARC0: Beginning to archive thread 1 sequence 324 (13275735057-13275745795)
Log Transport Services Control 0 8 0 YES 2017-02-06 14:46:42 ARC0: Completed archiving thread 1 sequence 324 (0-0)
Remote File Server Informational 0 9 0 NO 2017-02-06 14:46:42 Primary database is in MAXIMUM PERFORMANCE mode
Remote File Server Informational 0 10 0 NO 2017-02-06 14:46:42 RFS[2]: Assigned to RFS process 24442
Remote File Server Informational 0 11 0 NO 2017-02-06 14:46:42 RFS[3]: Assigned to RFS process 24444
11 rows selected.
--alert内容:
alter database mount standby database
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from bookdg to book
ARCH: STARTING ARCH PROCESSES
Mon Feb 06 14:45:25 2017
ARC0 started with pid=19, OS id=24434
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
ARC0: Thread not mounted
Mon Feb 06 14:45:26 2017
Successful mount of redo thread 1, with mount id 1376016192
Physical Standby Database mounted.
Lost write protection disabled
ARC0: Becoming the active heartbeat ARCH
Completed: alter database mount standby database
Mon Feb 06 14:46:40 2017
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archivelog/book
RFS[1]: Assigned to RFS process 24440
RFS[1]: Selected log 4 for thread 1 sequence 324 dbid 1337401710 branch 896605872
Mon Feb 06 14:46:42 2017
Archived Log entry 159 added for thread 1 sequence 324 ID 0x4fb7d86e dest 1:
Mon Feb 06 14:46:42 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process 24442
RFS[2]: Selected log 4 for thread 1 sequence 334 dbid 1337401710 branch 896605872
Mon Feb 06 14:46:42 2017
RFS[3]: Assigned to RFS process 24444
RFS[3]: Opened log for thread 1 sequence 325 dbid 1337401710 branch 896605872
Archived Log entry 160 added for thread 1 sequence 325 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 326 dbid 1337401710 branch 896605872
Archived Log entry 161 added for thread 1 sequence 326 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 327 dbid 1337401710 branch 896605872
Archived Log entry 162 added for thread 1 sequence 327 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 328 dbid 1337401710 branch 896605872
Archived Log entry 163 added for thread 1 sequence 328 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 329 dbid 1337401710 branch 896605872
Archived Log entry 164 added for thread 1 sequence 329 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 330 dbid 1337401710 branch 896605872
Archived Log entry 165 added for thread 1 sequence 330 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 331 dbid 1337401710 branch 896605872
Archived Log entry 166 added for thread 1 sequence 331 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 332 dbid 1337401710 branch 896605872
Archived Log entry 167 added for thread 1 sequence 332 rlc 896605872 ID 0x4fb7d86e dest 2:
RFS[3]: Opened log for thread 1 sequence 333 dbid 1337401710 branch 896605872
Archived Log entry 168 added for thread 1 sequence 333 rlc 896605872 ID 0x4fb7d86e dest 2:
--//说明即使设置2边的db_unique_name一样.log_archive_dest_N中db_unique_name设置错误,gap一样能正常解决.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
Mon Feb 06 14:52:09 2017
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (bookdg)
Mon Feb 06 14:52:10 2017
MRP0 started with pid=23, OS id=24449
MRP0: Background Managed Standby Recovery process started (bookdg)
started logmerger process
Mon Feb 06 14:52:16 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database using current logfile disconnect
Media Recovery Log /u01/app/oracle/archivelog/book/1_324_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_325_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_326_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_327_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_328_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_329_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_330_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_331_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_332_896605872.dbf
Media Recovery Log /u01/app/oracle/archivelog/book/1_333_896605872.dbf
Media Recovery Waiting for thread 1 sequence 334 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 334 Reading mem 0
Mem# 0: /mnt/ramdisk/book/redostb01.log
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 24444 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 24442 IDLE LGWR 3 1 334 409 1 0
ARCH 24434 CLOSING ARCH 4 1 324 14336 1106 0
MRP0 24449 APPLYING_LOG N/A N/A 1 334 409 102400 0
SYS@bookdg> @ &r/dg/dg_mess
FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
------------------------ ------------- ---------- ----------- ---------- --- ------------------- --------------------------------------------------------------------------------
Log Transport Services Informational 0 1 0 NO 2017-02-06 14:45:26 ARC0: Archival started
Log Transport Services Informational 0 2 0 NO 2017-02-06 14:45:26 ARC0: Becoming the 'no FAL' ARCH
Log Transport Services Informational 0 3 0 NO 2017-02-06 14:45:26 ARC0: Becoming the 'no SRL' ARCH
Log Transport Services Informational 0 4 0 NO 2017-02-06 14:45:26 ARC0: Becoming the heartbeat ARCH
Log Transport Services Informational 0 5 0 NO 2017-02-06 14:45:26 ARC0: Becoming the active heartbeat ARCH
Remote File Server Informational 0 6 0 NO 2017-02-06 14:46:41 RFS[1]: Assigned to RFS process 24440
Log Transport Services Control 0 7 0 YES 2017-02-06 14:46:41 ARC0: Beginning to archive thread 1 sequence 324 (13275735057-13275745795)
Log Transport Services Control 0 8 0 YES 2017-02-06 14:46:42 ARC0: Completed archiving thread 1 sequence 324 (0-0)
Remote File Server Informational 0 9 0 NO 2017-02-06 14:46:42 Primary database is in MAXIMUM PERFORMANCE mode
Remote File Server Informational 0 10 0 NO 2017-02-06 14:46:42 RFS[2]: Assigned to RFS process 24442
Remote File Server Informational 0 11 0 NO 2017-02-06 14:46:42 RFS[3]: Assigned to RFS process 24444
Log Apply Services Control 0 12 0 YES 2017-02-06 14:52:09 Attempt to start background Managed Standby Recovery process
Log Apply Services Control 0 13 0 YES 2017-02-06 14:52:10 MRP0: Background Managed Standby Recovery process started
Log Apply Services Informational 0 14 0 NO 2017-02-06 14:52:16 Managed Standby Recovery starting Real Time Apply
Log Apply Services Informational 0 15 0 NO 2017-02-06 14:52:19 Media Recovery Log /u01/app/oracle/archivelog/book/1_324_896605872.dbf
Log Apply Services Informational 0 16 0 NO 2017-02-06 14:52:19 Media Recovery Log /u01/app/oracle/archivelog/book/1_325_896605872.dbf
Log Apply Services Informational 0 17 0 NO 2017-02-06 14:52:20 Media Recovery Log /u01/app/oracle/archivelog/book/1_326_896605872.dbf
Log Apply Services Informational 0 18 0 NO 2017-02-06 14:52:20 Media Recovery Log /u01/app/oracle/archivelog/book/1_327_896605872.dbf
Log Apply Services Informational 0 19 0 NO 2017-02-06 14:52:21 Media Recovery Log /u01/app/oracle/archivelog/book/1_328_896605872.dbf
Log Apply Services Informational 0 20 0 NO 2017-02-06 14:52:21 Media Recovery Log /u01/app/oracle/archivelog/book/1_329_896605872.dbf
Log Apply Services Informational 0 21 0 NO 2017-02-06 14:52:21 Media Recovery Log /u01/app/oracle/archivelog/book/1_330_896605872.dbf
Log Apply Services Informational 0 22 0 NO 2017-02-06 14:52:21 Media Recovery Log /u01/app/oracle/archivelog/book/1_331_896605872.dbf
Log Apply Services Informational 0 23 0 NO 2017-02-06 14:52:22 Media Recovery Log /u01/app/oracle/archivelog/book/1_332_896605872.dbf
Log Apply Services Informational 0 24 0 NO 2017-02-06 14:52:22 Media Recovery Log /u01/app/oracle/archivelog/book/1_333_896605872.dbf
Log Apply Services Warning 0 25 0 NO 2017-02-06 14:52:22 Media Recovery Waiting for thread 1 sequence 334 (in transit)
25 rows selected.