20170315測試線上日誌與備用日誌大小不一樣
[20170315]測試線上日誌與備用日誌大小不一樣.txt
--//今天測試一下,主庫的線上日誌與備庫的備用日誌大小不一樣的情況,因為備庫日誌主要目的是接收主庫傳輸過來的日誌並應用日誌.
--//僅僅測試備用日誌小於主庫線上日誌的情況.
--//一般運維要求日誌大小線上與備用大小一致,備用日誌的數量比線上日誌數量+1,如果rac環境也就是每個例項比線上日誌多一組.
--//滿足業務高峰20-30分鐘切換一次就ok了.
--//出現不一致的情況,主要是主庫修改日誌大小,而備庫忘記修改.測試這種情況下會出現什麼情況:
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
--//備庫
SYS@bookdg> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 805 52428800 512 1 YES CURRENT 13277509688 2017-03-15 09:21:13 13277509678 2017-03-15 09:21:11
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 803 52428800 512 1 YES CLEARING 13277509678 2017-03-15 09:21:11 13277509683 2017-03-15 09:21:12
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 804 52428800 512 1 YES CLEARING 13277509683 2017-03-15 09:21:12 13277509688 2017-03-15 09:21:13
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
SYS@bookdg> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ----------- ------- --------- ----------- --------- ------------ --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
5 1337401710 1 805 52428800 512 76288 YES ACTIVE 13277509688 2017-03-15 09:21:13 13277509837 2017-03-15 09:23:42
6 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
7 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
--//當前是group#=5在接收日誌.
2.刪除備庫的備用日誌:
SYS@bookdg> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
$ oerr ora 01156
01156, 00000, "recovery or flashback in progress may need access to files"
// *Cause: Either media recovery, instance recovery, or flashback was
// in progress. The recovery or flashback in progress may need
// the files to which this operation is being applied.
// *Action: Wait for recovery or flashback to complete.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
SYS@bookdg> alter database drop standby logfile group 4;
Database altered.
SYS@bookdg> alter database drop standby logfile group 6;
Database altered.
SYS@bookdg> alter database drop standby logfile group 7;
Database altered.
SYS@bookdg> alter database drop standby logfile group 5;
alter database drop standby logfile group 5
*
ERROR at line 1:
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: '/mnt/ramdisk/book/redostb02.log'
--//在用無法drop.
--//主庫執行:
SYS@book> alter system archive log current;
System altered.
--//備庫執行:
SYS@bookdg> alter database drop standby logfile group 5;
Database altered.
--//ok現在刪除備庫全部備用日誌.現在建立新的備用日誌,大小20M
--//alter database add standby logfile ('/mnt/ramdisk/book/redostb01.log') size 50m ;
SYS@bookdg> alter database add standby logfile group 4 ('/mnt/ramdisk/book/redostb01.log') size 20m reuse;
Database altered.
alter database add standby logfile group 5 ('/mnt/ramdisk/book/redostb02.log') size 20m reuse;
alter database add standby logfile group 6 ('/mnt/ramdisk/book/redostb03.log') size 20m reuse;
alter database add standby logfile group 7 ('/mnt/ramdisk/book/redostb04.log') size 20m reuse;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ---------- ------- --------- ----------- --------- ------------ --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
5 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
6 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
--//現在全部是20M.
3.開啟日誌傳輸與應用看看:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ---------- ------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
5 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
6 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 20971520 512 0 YES UNASSIGNED
--//可以發現備用日誌並不接受日誌.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS 10079 IDLE ARCH N/A 0 0 0 0 0
RFS 10077 IDLE LGWR 2 1 806 940 1 0
ARCH 10072 CLOSING ARCH 5 1 805 1 462 0
MRP0 10163 WAIT_FOR_LOG N/A N/A 1 806 0 0 0
--//這樣並不能使用實時應用.MRP0僅僅停止在block#=0的情況.RFS PID=10077 group#=2在接收.
--//主庫執行:
SYS@book> alter system archive log current;
System altered.
--//備庫執行:
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- --------------- ---------- ---------- ---------- ----------
RFS 10079 IDLE ARCH N/A 0 0 0 0 0
RFS 10077 IDLE LGWR 3 1 807 16 1 0
ARCH 10072 CLOSING ARCH 5 1 805 1 462 0
MRP0 10163 WAIT_FOR_LOG N/A N/A 1 807 0 0 0
--//可以發現在歸檔後才應用,也就是延遲應用.
--//實際上RFS PID=10077 group#=3在接收.真的碼?
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redo03.log' validate ;
alter system dump logfile '/mnt/ramdisk/book/redo03.log' validate
*
ERROR at line 1:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/mnt/ramdisk/book/redo03.log'
--//很明顯接收的不是線上日誌,而是
$ ls -l /proc/10077/fd
total 14
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 14 -> socket:[429975916]
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 256 -> /data/ramdisk/book/control01.ctl
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 257 -> /data/ramdisk/book/control02.ctl
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 258 -> /u01/app/oracle/archivelog/book/1_807_896605872.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 4 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 5 -> /proc/10077/fd
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 6 -> /dev/zero
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 7 -> /u01/app/oracle/admin/book/adump/bookdg_ora_10077_20170315091852338409143795.aud
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 8 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trc
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 9 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trm
$ ls -l /u01/app/oracle/archivelog/book/1_807_896605872.dbf
-rw-r----- 1 oracle oinstall 52429312 2017-03-15 09:54:41 /u01/app/oracle/archivelog/book/1_807_896605872.dbf
--//很明顯接收日誌的是歸檔目的檔案/u01/app/oracle/archivelog/book/1_807_896605872.dbf.注意大小52429312/1024/1024= 50M+512位元組.
--//主庫執行:
SYS@book> alter system archive log current;
System altered.
--//備庫執行:
$ ls -l /u01/app/oracle/archivelog/book/1_807_896605872.dbf
-rw-r----- 1 oracle oinstall 409600 2017-03-15 09:56:40 /u01/app/oracle/archivelog/book/1_807_896605872.dbf
--//現在才還歸檔的檔案大小.
4.採用備用日誌檔案大小大於線上日誌.
SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database add standby logfile group 4 ('/mnt/ramdisk/book/redostb01.log') size 60m reuse;
alter database add standby logfile group 5 ('/mnt/ramdisk/book/redostb02.log') size 60m reuse;
alter database add standby logfile group 6 ('/mnt/ramdisk/book/redostb03.log') size 60m reuse;
alter database add standby logfile group 7 ('/mnt/ramdisk/book/redostb04.log') size 60m reuse;
--//再次啟動日誌傳輸與應用.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ---------- ------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 1337401710 1 809 62914560 512 1536 YES ACTIVE 1.3278E+10 2017-03-15 10:01:04 1.3278E+10 2017-03-15 10:01:05
5 UNASSIGNED 0 0 62914560 512 0 YES UNASSIGNED
6 UNASSIGNED 0 0 62914560 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 62914560 512 0 YES UNASSIGNED
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS 10079 IDLE ARCH N/A 0 0 0 0 0
RFS 10077 IDLE LGWR 2 1 809 42 1 0
ARCH 10072 CLOSING ARCH 5 1 805 1 462 0
MRP0 10232 APPLYING_LOG N/A N/A 1 809 42 122880 0
--//現在備用日誌接收並應用日誌.
--//再看看相關程式開啟檔案控制程式碼的情況.
$ ls -l /proc/10232/fd
total 14
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 2017-03-15 10:02:21 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 2017-03-15 10:02:21 10 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_mrp0_10232.trc
l-wx------ 1 oracle oinstall 64 2017-03-15 10:02:21 11 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_mrp0_10232.trm
l-wx------ 1 oracle oinstall 64 2017-03-15 10:02:21 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 2017-03-15 10:02:21 256 -> /data/ramdisk/book/control01.ctl
lrwx------ 1 oracle oinstall 64 2017-03-15 10:02:21 257 -> /data/ramdisk/book/control02.ctl
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 6 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 7 -> /proc/10232/fd
lr-x------ 1 oracle oinstall 64 2017-03-15 10:02:21 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 2017-03-15 10:02:21 9 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/hc_bookdg.dat
--//可以發現MPR0程式10232並沒有開啟相關備用日誌控制程式碼.
$ ls -l /proc/10077/fd
total 14
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 14 -> socket:[429975916]
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 256 -> /data/ramdisk/book/control01.ctl
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 257 -> /data/ramdisk/book/control02.ctl
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 258 -> /data/ramdisk/book/redostb01.log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 4 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 5 -> /proc/10077/fd
lr-x------ 1 oracle oinstall 64 2017-03-15 09:51:18 6 -> /dev/zero
lrwx------ 1 oracle oinstall 64 2017-03-15 09:51:18 7 -> /u01/app/oracle/admin/book/adump/bookdg_ora_10077_20170315091852338409143795.aud
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 8 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trc
l-wx------ 1 oracle oinstall 64 2017-03-15 09:51:18 9 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trm
--//可以發現RFS PID=10077,開啟備用日誌/data/ramdisk/book/redostb01.log的控制程式碼.
5.總結:
--維護線上日誌更改大小時注意同時備庫備用日誌的大小,一般最簡單的方式生成新的備用控制檔案.啟用新的備用控制檔案.
--最佳的方式保持線上與備用日誌大小一致.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2135433/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 離線日誌釋出測試
- 聯機重做日誌、歸檔日誌、備用重做日誌
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 從備份集恢復歸檔日誌的測試與說明
- 建立測試物理Standby日誌
- Android測試日誌檔案抓取與分析Android
- 學習日誌-----測試思維
- 日誌分析必備指令集【來自一段線上日誌的檢視的經歷】
- Oracle 線上日誌管理Oracle
- 線上日誌損壞
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試
- 18【線上日誌分析】之Spark on Yarn配置日誌Web UI(HistoryServer服務)SparkYarnWebUIServer
- oracle效能調優:管理oracle日誌之調整線上日誌檔案Oracle
- 重做日誌檔案損壞測試
- 客戶端釋出日誌測試客戶端
- Cmocka 單元測試日誌記錄Mock
- MySQL 日誌管理、備份與恢復MySql
- struts 中怎樣將系統日誌與使用者日誌分離
- 測試DML 時產生歸檔日誌和閃回日誌的比
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試（敘）
- DG保護模式與備用聯機日誌檔案模式
- 用RMAN備份歸檔日誌時檢查歸檔日誌是否存在
- 日誌傳送事務日誌備份設定
- 非線上日誌丟失
- RAC 線上日誌的管理
- PowerJob 線上日誌飽受好評的祕訣：小但實用的分散式日誌系統分散式
- MYSQL啟用日誌和檢視日誌MySql
- 自動化測試框架：日誌的分析框架
- 16【線上日誌分析】之grafana-4.1.1 Install和新建日誌分析的DashBoardGrafana
- FreeBSD系統日誌與備份(轉)
- 備份歸檔日誌
- sqlserver的日誌備份SQLServer
- 節點2線上日誌生成歸檔日誌在節點1上的初步分析
- data guard中增加與刪除主備資料庫中的聯機重做日誌與備重做日誌檔案資料庫
- 基於 kafka 的日誌資料建模測試Kafka
- 關於歸檔日誌的切換測試
- [Java/日誌] 日誌框架列印應用程式日誌程式碼的執行情況Java框架
- Data Guard備庫日誌的實時應用與非實時應用