本帖最后由 lichangzai 于 2012-5-21 11:36 编辑
我们知道在dataguard环境中,适当保留归档日志和闪回日志是非常必要的,在目前的oracle11gR2版本中,都有相关参数对归档日志和闪回日志的保留参数进行设置
下面是我们一个生产环境中目前归档日志和闪回日志自动保留情况。
[oracle@hotel05 ~]$ !sql
sqlplus "/as sysdba"
--闪回保留大小为一天(24小时)
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440 --minute
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900 --second
--闪回区分配为190G
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 195000M
[oracle@hotel05 ~]$ rman target /
RMAN> show all
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
[root@hotel05 ~]# su - oracle
[oracle@hotel05 ~]$ export ORACLE_SID=+ASM
[oracle@hotel05 ~]$ export ORACLE_HOME=/u01/app/ora11g/product/11.2.0/grid
[oracle@hotel05 ~]$ asmcmd -p
ASMCMD [+] > cd fra/htdb5/archivelog
--归档自动保留了为1月9天
ASMCMD [+fra/htdb5/archivelog] > ls -lt
Type Redund Striped Time Sys Name
Y 2012_04_10/
Y 2012_04_11/
Y 2012_04_12/
Y 2012_04_13/
Y 2012_04_14/
Y 2012_04_15/
Y 2012_04_16/
Y 2012_04_17/
Y 2012_04_18/
Y 2012_04_19/
Y 2012_04_20/
Y 2012_04_21/
Y 2012_04_22/
Y 2012_04_23/
Y 2012_04_24/
Y 2012_04_25/
Y 2012_04_26/
Y 2012_04_27/
Y 2012_04_28/
Y 2012_04_29/
Y 2012_04_30/
Y 2012_05_01/
Y 2012_05_02/
Y 2012_05_03/
Y 2012_05_04/
Y 2012_05_05/
Y 2012_05_06/
Y 2012_05_07/
Y 2012_05_08/
Y 2012_05_09/
Y 2012_05_10/
Y 2012_05_11/
Y 2012_05_12/
Y 2012_05_13/
Y 2012_05_14/
Y 2012_05_15/
Y 2012_05_16/
Y 2012_05_17/
Y 2012_05_18/
ASMCMD [+fra/htdb5/archivelog] > cd 2012_04_10/
ASMCMD [+fra/htdb5/archivelog/2012_04_10] > ls -l
Type Redund Striped Time Sys Name
ARCHIVELOG UNPROT COARSE APR 10 17:00:00 Y thread_1_seq_1752.795.780256599
ARCHIVELOG UNPROT COARSE APR 10 22:00:00 Y thread_1_seq_1753.796.780271207
ARCHIVELOG UNPROT COARSE APR 10 22:00:00 Y thread_1_seq_1754.797.780272389
--闪回日志自动保留了为9天
ASMCMD [+fra/htdb5/archivelog] > cd ../flashback
ASMCMD [+fra/htdb5/flashback] > ls -lt
Type Redund Striped Time Sys Name
FLASHBACK UNPROT COARSE MAY 10 22:00:00 Y log_175.639.760502681
FLASHBACK UNPROT COARSE MAY 10 23:00:00 Y log_176.640.760502685
FLASHBACK UNPROT COARSE MAY 10 23:00:00 Y log_177.641.760502687
FLASHBACK UNPROT COARSE MAY 11 00:00:00 Y log_178.643.760502693
FLASHBACK UNPROT COARSE MAY 11 01:00:00 Y log_179.644.760502697
FLASHBACK UNPROT COARSE MAY 11 03:00:00 Y log_181.647.760502703
FLASHBACK UNPROT COARSE MAY 11 03:00:00 Y log_182.648.760502705
FLASHBACK UNPROT COARSE MAY 11 03:00:00 Y log_183.650.760502709
......
......
FLASHBACK UNPROT COARSE MAY 18 10:00:00 Y log_166.627.760502653
FLASHBACK UNPROT COARSE MAY 18 12:00:00 Y log_167.628.760502653
FLASHBACK UNPROT COARSE MAY 18 12:00:00 Y log_168.630.760502659
FLASHBACK UNPROT COARSE MAY 18 13:00:00 Y log_169.631.760502663
FLASHBACK UNPROT COARSE MAY 18 13:00:00 Y log_170.632.760502665
FLASHBACK UNPROT COARSE MAY 18 13:00:00 Y log_171.634.760502669
FLASHBACK UNPROT COARSE MAY 18 14:00:00 Y log_172.635.760502671
FLASHBACK UNPROT COARSE MAY 18 14:00:00 Y log_173.636.760502675
FLASHBACK UNPROT COARSE MAY 18 14:00:00 Y log_174.638.760502677
FLASHBACK UNPROT COARSE MAY 18 14:00:00 Y log_180.646.760502699
--ASM存储使用情况
[oracle@hotel05 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 614399 570839 0 570839 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 204797 153325 0 153325 0 N FRA/
--归档日志占用空间
ASMCMD [+fra/htdb5] > du ARCHIVELOG/
Used_MB Mirror_used_MB
37109 37109
--闪回日志占用空间
ASMCMD [+fra/htdb5] > du FLASHBACK/
Used_MB Mirror_used_MB
9009 9009
--FRA空间使用情况
SQL> Select * From V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0.01 0 1
REDO LOG 1.34 0 13
ARCHIVED LOG 18.9 18.83 280
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 4.62 3.94 273
FOREIGN ARCHIVED LOG 0 0 0
问题:
1.关于CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY的配置,归档自动保留了40天左右,当然这是根据FRA空间的使用情况自动保留的。
但是这对于这种日志自动保留策略,oracle具体根据什么进行的保留归档?目前归档日志是基本占用18.9%左右时而进行删除,这个比例是根据什么来的?
这里是oracle做的简单解释,但没有说具体方法:
In the recovery area, the database retains logs eligible for deletion as long as possible. The database deletes the oldest logs first when disk space is required. When the recovery area is under disk pressure, the database may delete archived redo log files needed by Oracle Streams.
2.同样,在dataguard备用数据库开启闪回日志后,设置db_flashback_retention_target=1440(24小时)参数,但是我们看到实际上闪回日志是基本上保留了9天,而不是1天。
占用FRA空间为4.62%。这个闪回日志的自动保留策略又是如何实现的,这个占用FRA空间比例是根据什么来的?