11G新特性-控制檔案延遲自動備份

perfychi發表於2013-08-09
From:http://blog.chinaunix.net/uid-22948773-id-3347991.html
在10G中，ORACLE引入了控制檔案的自動備份，如果資料庫的結構發生了變化，那麼ORACLE
會自動將控制檔案備份到指定目錄中。
前提是資料庫處於歸檔模式，並且CONTROLFILE AUTOBACKUP設定為ON。
首先看一下10G的情況：
 

點選(此處)摺疊或開啟

  1. SQL> select * from v$version;

  3. BANNER
  4. --------------------------------------------------------------------------------

  6. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
  7. PL/SQL Release 10.2.0.5.0 - Production
  8. CORE 10.2.0.5.0 Production
  9. TNS for Linux: Version 10.2.0.5.0 - Production
  10. NLSRTL Version 10.2.0.5.0 - Production

  12. SQL> conn / as sysdba
  13. Connected.
  14. SQL> archive log list
  15. Database log mode Archive Mode
  16. Automatic archival Enabled
  17. Archive destination USE_DB_RECOVERY_FILE_DEST
  18. Oldest online log sequence 121
  19. Next log sequence to archive 124
  20. Current log sequence 124

  22. [oracle@dbserver ~]$ rman target /

  24. Recovery Manager: Release 10.2.0.5.0 - Production on Fri Sep 14 11:00:13 2012

  26. Copyright (c) 1982, 2007, Oracle. All rights reserved.

  28. connected to target database: REPORTDB (DBID=3119636991)

  30. RMAN> show controlfile autobackup;

  32. using target database control file instead of recovery catalog
  33. RMAN configuration parameters are:
  34. CONFIGURE CONTROLFILE AUTOBACKUP ON;

  36. RMAN>
這是一個10.2.0.5的資料庫，資料庫處於歸檔模式，並且controlfile autobackup設定為ON。

點選(此處)摺疊或開啟

  1. SQL> create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m;

  3. Tablespace created.

  5. SQL> alter database drop logfile group 5;

  7. Database altered.

日誌檔案中將會看到控制檔案的自動備份情況。

點選(此處)摺疊或開啟

  1. Fri Sep 14 10:32:12 CST 2012
  2.  create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m
  3. Fri Sep 14 10:32:13 CST 2012
  4. Starting control autobackup
  5. Control autobackup written to DISK device
  6.         handle '/u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/o1_mf_s_793967533_8555sgkh_.bkp'
  7. Completed: create tablespace test datafile '/u01/app/oracle/oradata/reportdb/test01.dbf' size 2m
  8. Fri Sep 14 10:32:33 CST 2012
  9.  alter database drop logfile group 5
  10. Fri Sep 14 10:32:33 CST 2012
  11. Starting control autobackup
  12. Control autobackup written to DISK device
  13.         handle '/u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/o1_mf_s_793967553_8555t1q6_.bkp'
  14. Completed: alter database drop logfile group 5
ls -ltr 也可以看出檔案的建立時間可以看出備份檔案被立馬建立。
 
[oracle@dbserver REPORTDB]$ ls -ltr /u01/app/oracle/flash_recovery_area/REPORTDB/autobackup/2012_09_14/
total 36380
-rw-r----- 1 oracle oinstall 7438336 Sep 14 10:32 o1_mf_s_793967533_8555sgkh_.bkp
-rw-r----- 1 oracle oinstall 7438336 Sep 14 10:32 o1_mf_s_793967553_8555t1q6_.bkp
 
 

從ORACLE DATABASE 11GR2開始，引入了控制檔案自動備份延遲建立的特性。
即使你設定了控制檔案的自動備份，在資料庫結構發現變化的時候不會立即看到控制檔案的備份，而是過一段時間才會看到。
這是ORACLE為了改變效能而引入的，防止你在一個指令碼中多次對資料庫結構的變化而建立多個控制檔案備份。
譬如CREATE TABLEPACE,DROP LOGFILE,ADD LOGEILE等，11G只會備份一個控制檔案，而不是多個。
對於10G只要對資料庫結構改變，就會自動備份一個。
上面的例子我建立了一個表空間，刪除了一個日誌檔案組，後臺自動生成了2個控制檔案備份。
下面看一下11G的情況：

點選(此處)摺疊或開啟

  1. [oracle@db2server ~]$ rman target /

  3. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 11 22:35:09 2012

  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: HUATENG (DBID=2134565240)

  9. RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archivelog/autobackup/%F';

  11. new RMAN configuration parameters:
  12. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archivelog/autobackup/%F';
  13. new RMAN configuration parameters are successfully stored

  15. RMAN> SHOW CONTROLFILE AUTOBACKUP ;

  17. RMAN configuration parameters for database with db_unique_name HUATENG are:
  18. CONFIGURE CONTROLFILE AUTOBACKUP ON;

  20. RMAN>


  23. SQL> archive log list
  24. Database log mode Archive Mode
  25. Automatic archival Enabled
  26. Archive destination /archivelog
  27. Oldest online log sequence 7
  28. Next log sequence to archive 9
  29. Current log sequence 9

  31. SQL> create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m;

  33. Tablespace created.


  36. Incremental checkpoint up to RBA [0x9.346f.0], current log tail at RBA [0x9.346f.0]
  37. Tue Sep 11 22:38:02 2012
  38. create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m
  39. Completed: create tablespace test datafile '/u01/app/oracle/oradata/huateng/test01.dbf' size 2m
  40. Tue Sep 11 22:38:20 2012
  41. Incremental checkpoint up to RBA [0x9.346f.0], current log tail at RBA [0x9.3501.0]


日誌檔案中僅僅顯示了表空間建立成功的資訊，並沒有控制檔案的自動備份資訊。
而且備份目錄下也沒有發現任何的控制檔案備份檔案。
[oracle@db2server autobackup]$ pwd
/archivelog/autobackup
[oracle@db2server autobackup]$ ls -ltr
total 0
[oracle@db2server autobackup]$

MOS [ID 1068182.1]對這個問題進行了如下闡述：
In this release, the controlfile autobackups are created by MMON slaves after few minutes of the structural changes,
 which increases performance.
So, It's  the expected behaviour to get the controlfile autobackup several minutes after the structural change on the database
and it's also expected that no message about controlfile autobackup creation will appear in the alert.log. 
There will be generated one MMON slave trace file with the controlfile creation information, that will be a file named: 
  SID__m000_.trc
 
 
在經過6分鐘後，終於看到備份檔案了：
[oracle@db2server autobackup]$ ls -ltr
total 9616
-rw-r----- 1 oracle oinstall 9830400 Sep 11 22:44 c-2134565240-20120911-00
oracle後臺程式m000的TRACE檔案中記錄此次備份資訊，ALERT檔案中不再記錄了。
 

點選(此處)摺疊或開啟

  1. [oracle@db2server trace]$ more huateng_m000_8971.trc
  2. Trace file /u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_m000_8971.trc
  3. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  5. ORACLE_HOME = /u01/app/oracle/product/db11gr2
  6. System name: Linux
  7. Node name: db2server
  8. Release: 2.6.18-92.el5
  9. Version: #1 SMP Tue Jun 10 18:49:47 EDT 2008
  10. Machine: i686
  11. Instance name: huateng
  12. Redo thread mounted by this instance: 1
  13. Oracle process number: 30
  14. Unix process pid: 8971, image: oracle@db2server (M000)


  17. *** 2012-09-11 22:44:03.648
  18. *** SESSION ID:(50.5) 2012-09-11 22:44:03.648
  19. *** CLIENT ID:() 2012-09-11 22:44:03.648
  20. *** SERVICE NAME:(SYS$BACKGROUND) 2012-09-11 22:44:03.648
  21. *** MODULE NAME:(MMON_SLAVE) 2012-09-11 22:44:03.648
  22. *** ACTION NAME:(Autobackup Control File) 2012-09-11 22:44:03.648

  24. Starting control autobackup

  26. *** 2012-09-11 22:44:06.515
  27. Control autobackup written to DISK device
  28.         handle '/archivelog/autobackup/c-2134565240-20120911-00'

ORACLE透過隱含引數controlfile_autobackup_delay來控制這種行為，預設是300秒。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27042095/viewspace-768145/，如需轉載，請註明出處，否則將追究法律責任。

相關文章