中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Fast Recovery Area空間用滿后的自動清理機制

發布時間：2020-08-10 23:55:18 來源：ITPUB博客 閱讀：347 作者：thinkiger 欄目：關系型數據庫
使用Fast Recovery Area最大的好處在于oracle能夠對于其中存放的備份恢復相關的對象進行自動管理，特別是在Fast Recovery Area空間利用率達到100%時能夠按照保留策略對其中的transient files進行自動清理，及時騰挪出可用空間，很大程度上減少了數據庫Hang的發生。下面模擬了FRA空間用滿的場景，之后通過不同的處理方式使得FRA又能騰出可用的空間，從中體會一下在FRA用滿的情況下oracle是如何進行自動清理的


準備數據：
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;


backup database;
backup database;
backup database;


list backup of database by summary;
RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23      B  F  A DISK        20150506 15:10:28 1       1       NO         TAG20150506T150958
25      B  F  A DISK        20150506 15:11:29 1       1       NO         TAG20150506T151059
27      B  F  A DISK        20150506 15:13:24 1       1       NO         TAG20150506T151252


SYS@tstdb1-SQL> select * from v$recovery_area_usage;


FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE  NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE                          0                         0                0
REDO LOG                              0                         0                0
ARCHIVED LOG                       4.07                      3.38               56
BACKUP PIECE                      24.22                         0                6
IMAGE COPY                            0                         0                0
FLASHBACK LOG                       .78                         0                2
FOREIGN ARCHIVED LOG                  0                         0                0


SYS@tstdb1-SQL> select * from v$recovery_file_dest;


NAME                      SPACE_LIMIT       SPACE_USED SPACE_RECLAIMABLE  NUMBER_OF_FILES
-------------------- ---------------- ---------------- ----------------- ----------------
/oradata06/fra            34359738368       9990663168        1162465280               64


alter system set db_recovery_file_dest_size=10G scope=memory;


create table t0506_3 tablespace TS0422_1 as select * from dba_objects;
insert into t0506_3 select * from t0506_3;  ---執行若干次
commit;
create table t0506_tpl as select * from t0506_3;


---循環delete->insert生成Archivelog填充FRA
declare
begin
while ( true ) loop
delete t0506_3;
commit;
insert into t0506_3 select * from t0506_tpl;
commit;
end loop;
end;
/


---不久FRA達到100%使用率：
SYS@tstdb1-SQL> select * from v$recovery_area_usage;


FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      13.01                       .12              35
BACKUP PIECE                      77.52                         0               6
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      9.14                         0               8
FOREIGN ARCHIVED LOG                  0                         0               0


---alert.log里不斷有提示FRA滿的信息輸出
************************************************************************
ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_63_%u_.arc'
Errors in file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_arc0_42205562.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.


###1、測試超出retention policy規定的backup是否在空間用滿的時候會被刪除
RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23      B  F  A DISK        20150506 15:10:28 1       1       NO         TAG20150506T150958
25      B  F  A DISK        20150506 15:11:29 1       1       NO         TAG20150506T151059
27      B  F  A DISK        20150506 15:13:24 1       1       NO         TAG20150506T151252


RMAN> show RETENTION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;


---將redundancy 3改成redundancy 2，看下有否一個版本的backup會被刪除
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;


ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_%u_.arc'
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_3_1jQKKtsWK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQKKtmNK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQKKtU5p_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQIqfg-3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQIqfl7C_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T150958_1jVEZMOAo_.bkp   <---果然被刪除
Archived Log entry 67 added for thread 1 sequence 14 ID 0x79f955eb dest 1:
Archiver process freed from errors. No longer stopped
Archived Log entry 68 added for thread 1 sequence 13 ID 0x79f955eb dest 1:
Wed May 06 15:32:11 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: /oradata06/testaaaaa/redo01a.log
  Current log# 1 seq# 16 mem# 1: /oradata06/testaaaaa/redo01b.log
Archived Log entry 69 added for thread 1 sequence 15 ID 0x79f955eb dest 1:


RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25      B  F  A DISK        20150506 15:11:29 1       1       NO         TAG20150506T151059
27      B  F  A DISK        20150506 15:13:24 1       1       NO         TAG20150506T151252


從上面的結果觀察到BS Key=23的backupset被清理掉了，但沒過不久上面的存儲過程不斷生成的archivelog又將FRA空間占滿了。

###2、測試將backupset backup到FRA以外的區域后是否FRA里的backupset會被刪除
---我們接下來把剩下的25、27中的25備份到FRA以外的路徑下，觀察一下是否25備份之后會被清理掉
backup backupset 25 format '/oradata06/vlib/%U';
Starting backup at 20150506 15:44:50
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=3, stamp=879001859, piece=1
channel ORA_DISK_1: starting piece 1 at 20150506 15:44:51
channel ORA_DISK_1: backup piece /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp
piece handle=/oradata06/vlib/03q69083_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 20150506 15:45:26
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35
Finished backup at 20150506 15:45:26


Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_ncsnf_TAG20150506T150958_1jVEbWU3n_.bkp  <---原BS key=24里包含controlfile&spfile的backup piece
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp   <---原BS key=25里的backup piece


RMAN> list backup of database summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25      B  F  A DISK        20150506 15:11:29 1       1       NO         TAG20150506T151059
27      B  F  A DISK        20150506 15:13:24 1       1       NO         TAG20150506T151252


RMAN> list backup of database;




List of Backup Sets
===================




BS Key  Type LV Size       Device Type Elapsed Time Completion Time  
------- ---- -- ---------- ----------- ------------ -----------------
25      Full    2.57G      DISK        00:00:30     20150506 15:11:29
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20150506T151059
        Piece Name: /oradata06/vlib/03q69083_1_2                                                       <----指向了FRA以外的路徑
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/system01.dbf
  2       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/users01.dbf
  5       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0329_1.dbf
  6       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/xdbts1.dbf
  7       Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
  8       Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
  9       Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0422_1.dbf
  10      Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0505_1.dbf


BS Key  Type LV Size       Device Type Elapsed Time Completion Time  
------- ---- -- ---------- ----------- ------------ -----------------
27      Full    2.57G      DISK        00:00:32     20150506 15:13:24
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20150506T151252
        Piece Name: /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151252_1jVEjjcey_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/system01.dbf
  2       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/users01.dbf
  5       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0329_1.dbf
  6       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/xdbts1.dbf
  7       Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
  8       Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
  9       Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0422_1.dbf
  10      Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0505_1.dbf


從以上結果可以看出在backup backupset 25 執行之后，原先FRA里的backup piece:/oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp已經被刪除query代之的是/oradata06/vlib/03q69083_1_2這個不在FRA里的piece，同時看出retention policy 2的條件依然滿足，因為我們做的只是將FRA里的backuppiece挪到了非FRA的目錄/oradata06/vlib/下
RMAN> show RETENTION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;


RMAN> report need backup;    <---沒有輸出表示REDUNDANCY 2的條件依然滿足


RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------


RMAN> list backup of datafile 2 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25      B  F  A DISK        20150506 15:11:29 1       1       NO         TAG20150506T151059
27      B  F  A DISK        20150506 15:13:24 1       1       NO         TAG20150506T151252


沒過一會兒，FRA目錄又撐滿了，這次我們嘗試將sequence 1~10的archivedlog備份到/oradata06/vlib/目錄下，看看這部分空間能否騰出來

###3、測試ARCHIVELOG DELETION POLICY TO NONE的情況下FRA里的archivelog是否會被刪除
backup archivelog sequence between 1 and 10 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:10:30
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=1 RECID=38 STAMP=879000498
input archived log thread=1 sequence=2 RECID=2 STAMP=878999036
input archived log thread=1 sequence=3 RECID=57 STAMP=879002329
input archived log thread=1 sequence=4 RECID=58 STAMP=879002333
input archived log thread=1 sequence=5 RECID=59 STAMP=879002472
input archived log thread=1 sequence=6 RECID=60 STAMP=879002478
input archived log thread=1 sequence=7 RECID=61 STAMP=879002481
input archived log thread=1 sequence=8 RECID=62 STAMP=879002818
input archived log thread=1 sequence=9 RECID=63 STAMP=879002828
input archived log thread=1 sequence=10 RECID=64 STAMP=879002837
channel ORA_DISK_1: starting piece 1 at 20150506 16:10:30
channel ORA_DISK_1: finished piece 1 at 20150506 16:10:45
piece handle=/oradata06/vlib/arc_08q693nm_1_1 tag=TAG20150506T161030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:10:45


---alert.log里觀察到1~10的archivelog被刪除了
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jVB-byQo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_2_1jVB-hUR3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jV0O3oPw_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_3_1jVF2uK7X_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_4_1jVF38mNH_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_5_1jVFBN_2Y_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_6_1jVFBlUyY_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_7_1jVFB_ZRX_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_8_1jVFW46iI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_9_1jVFWfjwo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_10_1jVFXC84I_.arc


---盡管我們為archivelog配置的策略是永久不刪除，但是在FRA滿的時候archivelog仍然會被刪除
RMAN> show ARCHIVELOG DELETION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default


過不久archivelog再次撐滿，這次我們將archivelog的delete policy配置成至少備份兩次到disk


###4、測試ARCHIVELOG DELETION POLICY TO backed up X times 的情況下FRA里的archivelog在何種條件下才會被刪除
CONFIGURE ARCHIVELOG DELETION POLICY TO backed up 2 times to device type disk;


RMAN> show ARCHIVELOG DELETION POLICY;


RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;


---先備份一次sequence: 11~20的archivelog到/oradata06/vlib/，看這些archivelog會否直接被刪除
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';


Starting backup at 20150506 16:24:07
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=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:24:08
channel ORA_DISK_1: finished piece 1 at 20150506 16:24:23
piece handle=/oradata06/vlib/arc_09q694h8_1_1 tag=TAG20150506T162408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:24:23


---備份完一次后我們沒有看到alert.log里11~20這些archivelog被刪除的信息，再備份一次
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:25:41
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=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:25:42
channel ORA_DISK_1: finished piece 1 at 20150506 16:25:57
piece handle=/oradata06/vlib/arc_0aq694k6_1_1 tag=TAG20150506T162541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:25:57


--alert.log顯示11~20被刪除
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_11_1jVFXkYDn_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_12_1jVFYGwBc_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_1jVFofwXZ_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_13_1jVFojazT_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_15_1jVFonFOt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_16_1jVFpJLXI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_17_1jVFpl8Vt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_18_1jVFq1Lso_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_19_1jVFqOM2x_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_20_1jVFqxB1o_.arc



以上測試驗證了在FRA滿的情況下：
對于超出retention policy的backup會被自動清理;
把backupset備份到FRA以外的區域時，FRA里的backupset會被自動清理；
對于ARCHIVELOG DELETION POLICY設置為none的情況，只要FRA里的archivelog已經進行過了備份，FRA里的archivelog就會被清理
對于ARCHIVELOG DELETION POLICY設置為BACKED UP N TIMES TO DISK的情況，"至少備份N次到disk"這個前提必須被滿足，FRA里的archivelog才會被清理

向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
汝州市| 文山县| 鄂托克旗| 芦溪县| 长汀县| 会昌县| 囊谦县| 信宜市| 广宗县| 远安县| 淳安县| 公安县| 庆阳市| 乌兰县| 武川县| 无为县| 赣榆县| 茌平县| 临朐县| 天峻县| 苍溪县| 蒲城县| 泰州市| 绥宁县| 东至县| 九龙城区| 西青区| 山东| 梓潼县| 德保县| 宝应县| 工布江达县| 叙永县| 淮南市| 柳江县| 土默特右旗| 中西区| 浮梁县| 百色市| 涪陵区| 奇台县|