Nologging操作對standby的影響 (zt)
一、實驗環境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> select force_logging from v$database;
FOR
---
NO
1.在primary上執行
SQL> create table test nologging tablespace test as select * from all_objects where rownum<1001;
Table created.
2.歸檔當前日誌
SQL> alter system archive log current;
System altered.
3.在備庫,等剛才傳過來的日誌應用後,啟動到read onlySQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database open read only;
Database altered.
4.執行查詢
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 10)
ORA-01110: data file 8: 'D:ORACLEORADATATESTTEST01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
可以看到,主庫的nologging操作不會無法傳遞到備庫來,這樣就會造成主備庫的資料不一致,丟失了資料。
三、處理方法
在主庫上,將受nologging操作影響的datafile做個熱備,然後copy到備庫覆蓋原來的,再重新應用日誌
1.查詢受nologging操作影響的datafile
SQL> select a.file_name,a.tablespace_name,b.unrecoverable_change# from dba_data_
files a,v$datafile b where a.file_id=b.file#;
FILE_NAME TABLESPACE UNRECOVERABLE_CHANGE#
---------------------------------------- ---------- ---------------------
D:ORACLEORADATANINGSYSTEM01.DBF SYSTEM 0
D:ORACLEORADATANINGUNDOTBS01.DBF UNDOTBS1 0
D:ORACLEORADATANINGCWMLITE01.DBF CWMLITE 0
D:ORACLEORADATANINGEXAMPLE01.DBF EXAMPLE 0
D:ORACLEORADATANINGINDX01.DBF INDX 0
D:ORACLEORADATANINGTOOLS01.DBF TOOLS 0
D:ORACLEORADATANINGUSERS01.DBF USERS 0
D:ORACLEORADATANINGTEST01.DBF TEST 738934
8 rows selected.
2.關閉備庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3.從主庫備份受影響的test01.dbf到備庫SQL> alter tablespace test begin backup;
Tablespace altered.
在作業系統中複製檔案
SQL> alter tablespace test end backup;
Tablespace altered.
4.歸檔當前日誌
SQL> alter system archive log current;
System altered.
5.啟動備庫並恢復注意將上一不歸檔的日誌全部copy到備庫的歸檔目的地,需要end backup操作的日誌才能將資料庫恢復到一致狀態
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01195: online backup of file 8 needs more recovery to be consistent
ORA-01110: data file 8: 'D:ORACLEORADATATESTTEST01.DBF'
SQL> recover standby database;
ORA-00279: change 738991 generated at 11/22/2006 10:46:44 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHTESTARC00100.001
ORA-00280: change 738991 for thread 1 is in sequence #100
Specify log: {
auto
ORA-00279: change 739327 generated at 11/22/2006 10:53:30 needed for thread 1
ORA-00289: suggestion : D:ORACLEARCHTESTARC00101.001
ORA-00280: change 739327 for thread 1 is in sequence #101
ORA-00278: log file 'D:ORACLEARCHTESTARC00100.001' no longer needed for
this recovery
ORA-00308: cannot open archived log 'D:ORACLEARCHTESTARC00101.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database open read only;
Database altered.
6.再在備庫執行查詢SQL> select count(1) from test;
COUNT(1)
----------
1000
四、將主庫置於force logging模式
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1404132
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-1006444/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- CONTEXT索引對COMMIT操作的影響 (ZT)Context索引MIT
- nologging對備庫的影響
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- STANDBY中NOLOGGING操作的監控
- 關於drop操作對role的影響
- [zt] 影響SQL效能的原因SQL
- [zt] segment size(strip size)對磁碟陣列效能的影響陣列
- reverse index 對於 MAX/MIN操作的影響Index
- 分割槽表的不同操作對索引的影響索引
- oracle點陣圖索引對DML操作的影響Oracle索引
- mysql刪除和更新操作對效能的影響MySql
- DDL,DML操作對結果快取的影響快取
- 影響flashback table的操作!
- mysql的DDL操作對業務產生影響測試MySql
- 操作分割槽表對global和local索引的影響索引
- zt_parallel_execution_message_size 對 rman recover恢復速度的影響Parallel
- 對列進行連線操作會影響索引的使用索引
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- 表的nologging和logging屬性對資料庫redo資料生成的影響資料庫
- Oracle DML(非select) 操作不commit 對select的影響OracleMIT
- Sql Server之旅——第十站 看看DML操作對索引的影響SQLServer索引
- nologging與append ztAPP
- 批操作效能影響診斷
- unusable index對DML/QUERY的影響Index
- Arraysize 對consistent get的影響
- mysql event對主從的影響MySql
- 新增欄位對SQL的影響SQL
- 語言對思維的影響
- “影響者研究”系列(一):社會網路化時代影響者對營銷創新的影響
- 絕對定位對margin外邊距的影響
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- 遊戲暗示對於遊戲玩家的影響遊戲
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- 物聯網對企業的影響
- Web-Scale IT:對企業的影響Web