清理RMAN备份数据中的过期数据

好几天没开VM学习ORACLE

今天启动出现下面错误：

[root@web1 bdump]# pwd
/opt/oracle/admin/testdb2/bdump
[root@web1 bdump]# cat alert_oradb.log
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=testdb2XDB)
  job_queue_processes      = 10
  background_dump_dest     = /opt/oracle/admin/testdb2/bdump
  user_dump_dest           = /opt/oracle/admin/testdb2/udump
  core_dump_dest           = /opt/oracle/admin/testdb2/cdump
  audit_file_dest          = /opt/oracle/admin/testdb2/adump
  db_name                  = testdb2
  open_cursors             = 300
  pga_aggregate_target     = 16777216
MMAN started with pid=4, OS id=3760
PSP0 started with pid=3, OS id=3758
PMON started with pid=2, OS id=3756
LGWR started with pid=6, OS id=3764
DBW0 started with pid=5, OS id=3762
CKPT started with pid=7, OS id=3766
SMON started with pid=8, OS id=3768
RECO started with pid=9, OS id=3770
CJQ0 started with pid=10, OS id=3772
MMON started with pid=11, OS id=3774
Fri Aug 21 07:51:00 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=3776
Fri Aug 21 07:51:00 2009
starting up 1 shared server(s) ...
Fri Aug 21 07:51:01 2009
ALTER DATABASE   MOUNT
Fri Aug 21 07:51:05 2009
Setting recovery target incarnation to 5
Fri Aug 21 07:51:05 2009
Successful mount of redo thread 1, with mount id 1017141349
Fri Aug 21 07:51:05 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Fri Aug 21 07:51:05 2009
ALTER DATABASE OPEN
Fri Aug 21 07:51:06 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=3784
Fri Aug 21 07:51:06 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=3786
Fri Aug 21 07:51:06 2009
ARC0: STARTING ARCH PROCESSES
Fri Aug 21 07:51:06 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Aug 21 07:51:06 2009
Thread 1 opened at log sequence 14
  Current log# 1 seq# 14 mem# 0: /oradata/testdb2/redo01.log
Successful open of redo thread 1
Fri Aug 21 07:51:06 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 21 07:51:06 2009
SMON: enabling cache recovery
Fri Aug 21 07:51:06 2009
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=3788
Fri Aug 21 07:51:08 2009
Successfully onlined Undo Tablespace 1.
Fri Aug 21 07:51:08 2009
SMON: enabling tx recovery
Fri Aug 21 07:51:08 2009
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=3790
Fri Aug 21 07:51:16 2009
Completed: ALTER DATABASE OPEN
Fri Aug 21 07:51:17 2009
Errors in file /opt/oracle/admin/testdb2/bdump/oradb_mmon_3774.trc:
ORA-19815: Message 19815 not found; No message file for product=RDBMS, facility=ORA; arguments: [db_recovery_file_dest_size] [2147483648] [91.93] [173236224]
Fri Aug 21 07:51:17 2009
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************
Fri Aug 21 07:57:06 2009
Shutting down archive processes
Fri Aug 21 07:57:11 2009
ARCH shutting down
ARC2: Archival stopped
Fri Aug 21 08:06:01 2009
alter database backup controlfile to trace
Fri Aug 21 08:06:02 2009
Completed: alter database backup controlfile to trace

处理过程：

1、查看flash_recovery_area目录空间

SQL> show parameter db_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
db_domain                            string
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     200
db_flashback_retention_target        integer     1440
db_keep_cache_size                   big integer 0
db_name                              string      testdb2
db_recovery_file_dest                string      /oradata/oraRecovery
db_recovery_file_dest_size           big integer 2G
db_recycle_cache_size                big integer 0
db_unique_name                       string      testdb2
db_writer_processes                  integer     1
dbwr_io_slaves                       integer     0
rdbms_server_dn                      string
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>

2、检查 $ORACLE_BASE/flash_recovery_area  是否这个目录满了，排除！！

3、处理过期的备份

[oracle@web1 ~]$ rman catalog rman/rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 21 08:38:07 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: TESTDB2 (DBID=1004146945)
connected to recovery catalog database

 

report obsolete命令报告过期备份

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           208    13-JUL-09        
  Backup Piece       219    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692096947_55o29nc7_.bkp
Backup Set           212    13-JUL-09        
  Backup Piece       223    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097592_55o2xrny_.bkp
Backup Set           214    13-JUL-09        
  Backup Piece       225    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097670_55o3077g_.bkp
Backup Set           215    14-JUL-09        
  Backup Piece       226    14-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692174017_55qfl2w6_.bkp
Backup Set           318    14-JUL-09        
  Backup Piece       325    14-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692175069_55qgly5h_.bkp
Backup Set           368    15-JUL-09        
  Backup Piece       370    15-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_15/o1_mf_n_692250636_55srdf9y_.bkp

report obsolete命令删除过期备份
RMAN> delete obsolete;                        

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           208    13-JUL-09        
  Backup Piece       219    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692096947_55o29nc7_.bkp
Backup Set           212    13-JUL-09        
  Backup Piece       223    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097592_55o2xrny_.bkp
Backup Set           214    13-JUL-09        
  Backup Piece       225    13-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097670_55o3077g_.bkp
Backup Set           215    14-JUL-09        
  Backup Piece       226    14-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692174017_55qfl2w6_.bkp
Backup Set           318    14-JUL-09        
  Backup Piece       325    14-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692175069_55qgly5h_.bkp
Backup Set           368    15-JUL-09        
  Backup Piece       370    15-JUL-09          /oradata/oraRecovery/TESTDB2/autobackup/2009_07_15/o1_mf_n_692250636_55srdf9y_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692096947_55o29nc7_.bkp recid=46 stamp=692096948
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097592_55o2xrny_.bkp recid=50 stamp=692097592
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_13/o1_mf_n_692097670_55o3077g_.bkp recid=52 stamp=692097671
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692174017_55qfl2w6_.bkp recid=53 stamp=692174018
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_14/o1_mf_n_692175069_55qgly5h_.bkp recid=55 stamp=692175070
deleted backup piece
backup piece handle=/oradata/oraRecovery/TESTDB2/autobackup/2009_07_15/o1_mf_n_692250636_55srdf9y_.bkp recid=56 stamp=692250637
Deleted 6 objects

RMAN>

list copy命令查看备份
RMAN> list copy;

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
366     1    10      A 13-JUL-09 /oradata/oraRecovery/TESTDB2/archivelog/2009_07_14/o1_mf_1_10_55s074g6_.arc
367     1    11      A 14-JUL-09 /oradata/oraRecovery/TESTDB2/archivelog/2009_07_15/o1_mf_1_11_55smqvtv_.arc
387     1    12      A 15-JUL-09 /oradata/oraRecovery/TESTDB2/archivelog/2009_07_16/o1_mf_1_12_55y7p3yv_.arc
388     1    13      A 16-JUL-09 /oradata/oraRecovery/TESTDB2/archivelog/2009_07_18/o1_mf_1_13_5632zmmk_.arc

 

归档日志备份处理：
执行RMAN CROSSCHECK and DELETE EXPIRED commands.
因为操作系统虽然认为这个目录下有空间，但RMAN中的catalog库或控制文件的记录认为这个空间是满的，
如果 不与实际的操作系统空间同步的话，RMAN是不会感觉到有剩余空间的！

当手工删除了归档日志以后，Rman备份会检测到日志缺失，从而无法进一步继续执行。
所以此时需要手工执行crosscheck过程，之后Rman备份可以恢复正常。
Crosscheck日志

同步控制文件的信息和实际物理文件的信息：
RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
validation succeeded for archived log
archive log filename=/oradata/oraRecovery/TESTDB2/archivelog/2009_07_14/o1_mf_1_10_55s074g6_.arc recid=46 stamp=692225895
validation succeeded for archived log
archive log filename=/oradata/oraRecovery/TESTDB2/archivelog/2009_07_15/o1_mf_1_11_55smqvtv_.arc recid=47 stamp=692245885
validation succeeded for archived log
archive log filename=/oradata/oraRecovery/TESTDB2/archivelog/2009_07_16/o1_mf_1_12_55y7p3yv_.arc recid=48 stamp=692397380
validation succeeded for archived log
archive log filename=/oradata/oraRecovery/TESTDB2/archivelog/2009_07_18/o1_mf_1_13_5632zmmk_.arc recid=49 stamp=692556407
Crosschecked 4 objects

注意：以上crosscheck的2种提示英文对照为：

对归档日志的验证成功-----》validation failed for archived log
对归档日志的验证失败-----> validation succeeded for archived log

delete expired archivelog all 命令删除所有过期归档日志:
RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN>

到此清理任务结束！！！
定期清理备份的RMAN归档日志和过期备份。