oracle rman備份歸檔日誌需要先切換日誌嗎
我們在備份Oracle資料庫的時候習慣性的在備份之前,會執行下ALTER SYSTEM SWITCH LOGFILE或者ALTER SYSTEM ARCHIVE LOG CURRENT,那麼這兩個命令到底是做什麼的,Oracle11.2.0.4版本中什麼情況下需要執行,什麼情況下不需要執行呢?
一:首先說下ALTER SYSTEM SWITCH LOGFILE和ALTER SYSTEM ARCHIVE LOG CURRENT
rman不會備份online redo log和standby redo log ,所以這就是為啥在做Oracle備份的時候,需要執行alter system archive log current; 或者alter system switch logfile這類的操作,這樣把當前在redo log檔案中的最新redo及時進行歸檔,
ALTER SYSTEM SWITCH LOGFILE 對單例項資料庫或RAC中的當前例項執行日誌切換,立刻返回結果,除非切換的時候現在所有的redo logfile group都沒有歸檔完成,此時會等待下一個redo logfile group歸檔完成後,才可以切換成current的redo logfile;
ALTER SYSTEM ARCHIVE LOG CURRENT 會對資料庫中的所有例項執行日誌切換,並且歸檔下當前current狀態的redo logfile日誌,會等待當前current狀態的redo logfile日誌歸檔完畢才會返回結果;
由此可見:當你想備份到完整的歸檔日誌的時候,資料庫應該執行ALTER SYSTEM ARCHIVE LOG CURRENT操作,或者應該執行多次ALTER SYSTEM SWITCH LOGFILE(有幾組online redo group就執行幾次切換),才能保證歸檔日誌中包含完整的備份資料檔案期間的所有redo!
官方文件關於這兩個命令的解釋:
ALTER SYSTEM SWITCH LOGFILE ;
SWITCH LOGFILE Clause
The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.
ALTER SYSTEM ARCHIVE LOG CURRENT ;
CURRENT Clause
Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, then Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify CURRENT only when the database is open.
二、Oracle 11g備份歸檔的方式:
1、備份歸檔日誌方式:
1)單獨備份歸檔日誌:backup archivelog all
2)在執行備庫時一起備份歸檔日誌:backup database plus archivelog;
2、這兩種方式有什麼區別呢?
1)執行backup archivelog all 命令時執行的步驟:
1.alter system archive log current; 歸檔當前日誌
2.backup archivelog all ; 備份所有歸檔日誌
2)而執行backup database plus archivelog,的執行步驟是:
1.alter system archive log current; 歸檔當前日誌
2.backup archivelog all; 備份所有歸檔日誌
3.backup database; 備份資料庫
4.alter system archive log current; 歸檔當前日誌
5.backup archivelog recently generated ; 備份剛生成的歸檔日誌
具體驗證如下:
1)首先 backup archivelog all,你在主庫備份的時候,可以看到如下的關鍵字current log archived,表明在備份歸檔的時候,會自動執行alter system archive log current;
RMAN> backup archivelog all format '/home/backup/liuwenhe/archivelog%U.bak';
Starting backup at 02-JUL-22
current log archived
----此處表明備份開始的時候執行了alter system archive log current;
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=866 RECID=1722 STAMP=1108998018
input archived log thread=1 sequence=867 RECID=1724 STAMP=1109027795
input archived log thread=1 sequence=868 RECID=1725 STAMP=1109028305
input archived log thread=1 sequence=869 RECID=1727 STAMP=1109028364
input archived log thread=1 sequence=870 RECID=1729 STAMP=1109028396
input archived log thread=1 sequence=871 RECID=1731 STAMP=1109028591
input archived log thread=1 sequence=872 RECID=1733 STAMP=1109029397
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/archivelogq611ksgl_1_1.bak tag=TAG20220702T234317 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-22
2)當你backup archivelog all,在dg庫備份的時候,發現報錯如下,說明當你在dg庫嘗試備份的時候會嘗試去主庫執行alter system archive log current;
RMAN> backup archivelog all format '/home/backup/liuwenhe/archivelog%U.bak';
Starting backup at 02-JUL-22
RMAN-06820: WARNING: failed to archive current log at primary database
----在dg庫執行備份歸
檔的操作,會嘗試去主庫執行alter system archive log current;--------------
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=857 RECID=850 STAMP=1108688404
input archived log thread=1 sequence=858 RECID=851 STAMP=1108739104
input archived log thread=1 sequence=859 RECID=852 STAMP=1108740245
input archived log thread=1 sequence=860 RECID=853 STAMP=1108740951
input archived log thread=1 sequence=861 RECID=854 STAMP=1108861301
input archived log thread=1 sequence=862 RECID=855 STAMP=1108861301
input archived log thread=1 sequence=863 RECID=856 STAMP=1108915207
input archived log thread=1 sequence=864 RECID=857 STAMP=1108947602
input archived log thread=1 sequence=865 RECID=858 STAMP=1108947603
input archived log thread=1 sequence=866 RECID=859 STAMP=1108998019
input archived log thread=1 sequence=867 RECID=860 STAMP=1109027795
input archived log thread=1 sequence=868 RECID=861 STAMP=1109028305
input archived log thread=1 sequence=869 RECID=862 STAMP=1109028364
input archived log thread=1 sequence=870 RECID=863 STAMP=1109028397
input archived log thread=1 sequence=871 RECID=864 STAMP=1109028591
input archived log thread=1 sequence=872 RECID=865 STAMP=1109029397
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/archivelog4k11kskp_1_1.bak tag=TAG20220702T234529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 02-JUL-22
3)在主庫執行backup database的時候加上plus archivelog關鍵字,如下所示
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/orcldg/backup/rman/data/db%U" plus archivelog;
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/liuwenhe/_db_%U" plus archivelog;
Starting backup at 02-JUL-22
current log archived-------
此處表明備份開始的時候執行了alter system archive log current;
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set --開始備份歸檔日誌
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=866 RECID=1722 STAMP=1108998018
input archived log thread=1 sequence=867 RECID=1724 STAMP=1109027795
input archived log thread=1 sequence=868 RECID=1725 STAMP=1109028305
input archived log thread=1 sequence=869 RECID=1727 STAMP=1109028364
input archived log thread=1 sequence=870 RECID=1729 STAMP=1109028396
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_07_02/o1_mf_annnn_TAG20220702T232636_kd0rxdyb_.bkp tag=TAG20220702T232636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 02-JUL-22
Starting backup at 02-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set ---開始備份資料檔案
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oradata/orcl/otc01.dbf
input datafile file number=00006 name=/home/oradata/orcl/htsl.dbf
input datafile file number=00002 name=/home/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oradata/orcl/system01.dbf
input datafile file number=00005 name=/home/oradata/orcl/example01.dbf
input datafile file number=00003 name=/home/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/home/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/_db_q011krhk_1_1 tag=TAG20220702T232644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/_db_q111krnd_1_1 tag=TAG20220702T232644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-22
Starting backup at 02-JUL-22
current log archived ----此處表明在備份完資料檔案後,又執行了一次alter system archive log current
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set---備份最新的歸檔
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=871 RECID=1731 STAMP=1109028591
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_07_02/o1_mf_annnn_TAG20220702T232951_kd0s3h9m_.bkp tag=TAG20220702T232951 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-22
4)在主庫不加plus archivelog引數備份資料庫,具體如下所示,發現沒有執行alter system archive log current;相關操作!
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/liuwenhe/_db_%U";
Starting backup at 02-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oradata/orcl/otc01.dbf
input datafile file number=00006 name=/home/oradata/orcl/htsl.dbf
input datafile file number=00002 name=/home/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oradata/orcl/system01.dbf
input datafile file number=00005 name=/home/oradata/orcl/example01.dbf
input datafile file number=00003 name=/home/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/home/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/_db_q411ks59_1_1 tag=TAG20220702T233713 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/liuwenhe/_db_q511ksb2_1_1 tag=TAG20220702T233713 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-22
5)同樣在dg庫執行plus archivelog方式的備份時,也會自動嘗試去主庫執行alter system archive log current操作:
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/orcldg/backup/rman/data/db%U" plus archivelog;
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/orcldg/backup/rman/data/db%U" plus archivelog;
Starting backup at 02-JUL-22
RMAN-06820: WARNING: failed to archive current log at primary database
---嘗試去主庫執行alter system archive log current
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=857 RECID=850 STAMP=1108688404
input archived log thread=1 sequence=858 RECID=851 STAMP=1108739104
input archived log thread=1 sequence=859 RECID=852 STAMP=1108740245
input archived log thread=1 sequence=860 RECID=853 STAMP=1108740951
input archived log thread=1 sequence=861 RECID=854 STAMP=1108861301
input archived log thread=1 sequence=862 RECID=855 STAMP=1108861301
input archived log thread=1 sequence=863 RECID=856 STAMP=1108915207
input archived log thread=1 sequence=864 RECID=857 STAMP=1108947602
input archived log thread=1 sequence=865 RECID=858 STAMP=1108947603
input archived log thread=1 sequence=866 RECID=859 STAMP=1108998019
input archived log thread=1 sequence=867 RECID=860 STAMP=1109027795
input archived log thread=1 sequence=868 RECID=861 STAMP=1109028305
input archived log thread=1 sequence=869 RECID=862 STAMP=1109028364
input archived log thread=1 sequence=870 RECID=863 STAMP=1109028397
input archived log thread=1 sequence=871 RECID=864 STAMP=1109028591
input archived log thread=1 sequence=872 RECID=865 STAMP=1109029397
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/u01/app/oracle/fast_recovery_area/ORCLDG/backupset/2022_07_02/o1_mf_annnn_TAG20220702T235004_kd0t9d87_.bkp tag=TAG20220702T235004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 02-JUL-22
Starting backup at 02-JUL-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oradata/ORCLDG/datafile/o1_mf_otc_jmrwnook_.dbf
input datafile file number=00006 name=/home/oradata/orcldg/htsl.dbf
input datafile file number=00002 name=/home/oradata/orcldg/sysaux01.dbf
input datafile file number=00001 name=/home/oradata/orcldg/system01.dbf
input datafile file number=00005 name=/home/oradata/orcldg/example01.dbf
input datafile file number=00003 name=/home/oradata/orcldg/undotbs01.dbf
input datafile file number=00004 name=/home/oradata/orcldg/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/orcldg/backup/rman/data/db4n11kstr_1_1 tag=TAG20220702T235019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-JUL-22
channel ORA_DISK_1: finished piece 1 at 02-JUL-22
piece handle=/home/backup/orcldg/backup/rman/data/db4o11kt3k_1_1 tag=TAG20220702T235019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUL-22
Starting backup at 02-JUL-22
RMAN-06820: WARNING: failed to archive current log at primary database
---再次嘗試去主庫執行alter system archive log current
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist
using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 02-JUL-22
綜上所示:
1、在Oracle11.2.0.4中,在RMAN備份指令碼中有 plus archivelog 引數就無需在備份歸檔日誌之前執行: sql 'alter system archive log current'; 同時如果你單獨備份歸檔的時候,也不需要提前執行 sql 'alter system archive log current';了,因為都會自動執行alter system archive log current'操作;
2、建議備份的時候遵從如下原則:
1)加上plus archivelog
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT "/home/backup/orcldg/backup/rman/data/db%U" plus archivelog;
2)不加plus archivelog,建議最後再單獨備份下歸檔日誌;
backup database;
backup archivelog all;
3、Oracle 11.2.0.4不加plus archivelog,也沒有在最後再備份下歸檔日誌,那麼你得到的備份結果集可能不一致,無法恢復的風險,具體報錯:即便是你執行備份前執行了alter system archive log current,也無濟於事!原因是你備份的歸檔日誌檔案的scn號可能小於你的某個資料檔案的scn號,導致recover database的時候不能實現一致性!
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/orcl/system01.dbf'
所以Oracle11.2.0.4備份任務中,沒必要執行手動執行ALTER SYSTEM SWITCH LOGFILE和ALTER SYSTEM ARCHIVE LOG CURRENT這倆操作了!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-2903990/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle設定多個歸檔路徑生成多份歸檔日誌,Rman備份時也只備份其中的一份歸檔日誌Oracle
- RMAN備份整庫和歸檔日誌的方法
- oracle歸檔日誌Oracle
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- Oracle歸檔日誌清理Oracle
- 歸檔oracle alert日誌Oracle
- rman 還原歸檔日誌(restore archivelogRESTHive
- 【REDO】Oracle 日誌挖掘,分析歸檔日誌線上日誌主要步驟Oracle
- [20221121]rman刪除歸檔日誌問題.txt
- oracle11G歸檔日誌管理Oracle
- PostgreSQL 歸檔日誌SQL
- 歸檔日誌挖掘
- 通過RMAN設定standby接收日誌後主庫歸檔日誌才可刪除
- Oracle歸檔日誌暴增排查優化Oracle優化
- 【SQL】Oracle 歸檔日誌暴增原因分析SQLOracle
- oracle dg 歸檔日誌恢復情況Oracle
- oracle 刪除過期的歸檔日誌Oracle
- 14. 日誌歸檔
- 檢視Oracle的redo日誌切換頻率Oracle
- RMAN-ERROR:因為找不到過期和丟失的歸檔日誌而備份失敗Error
- 如何快速找到備份過最近、最大序號的歸檔日誌
- [重慶思莊每日技術分享]-備庫使用RMAN獲得歸檔日誌
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- oracle資料庫歸檔日誌量陡增分析Oracle資料庫
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- RMAN刪除歸檔日誌出現RMAN-0813錯誤的處理
- 批量註冊歸檔日誌
- 檢視oracle的redo日誌組切換頻率Oracle
- SpringBoot切換預設日誌框架Spring Boot框架
- 配置rman來自動刪除應用過的歸檔日誌
- Oracle歸檔日誌所在目錄時間不對&&Oracle叢集日誌時間顯示錯誤Oracle
- ArgoWorkflow教程(四)---Workflow & 日誌歸檔Go
- logminer異機挖掘歸檔日誌
- DG歸檔日誌缺失恢復
- 在Oracle中,如何定時刪除歸檔日誌檔案?Oracle
- ORACLE NBU調取oracle rman指令碼備份歸檔不自動刪除歸檔Oracle指令碼