逻辑dg删除主库过期归档日志

主库上查看一下standby 的状态

SQL> select process,client_process,sequence#,status from v$managed_standby;


PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH             25 CLOSING
ARCH      ARCH             24 CLOSING
LGWR      LGWR             26 WRITING
ARCH      ARCH              0 CONNECTED


检查归档文件路径和创建的信息：


SQL> select name,creator,sequence#,applied,completion_time from v$archived_log;


NAME                                          CREATOR  SEQUENCE# APP COMPLETIO
--------------------------------------------- ------- ---------- --- ---------
                                              ARCH            12 NO  08-MAR-14
orcl_s_tns                                    ARCH            12 NO  08-MAR-14
/u01/log/arch_location/1_13_841597045.dbf     ARCH            13 NO  08-MAR-14
orcl_s_tns                                    ARCH            13 YES 08-MAR-14
/u01/log/arch_location/1_14_841597045.dbf     ARCH            14 NO  08-MAR-14
orcl_s_tns                                    ARCH            14 YES 08-MAR-14
/u01/log/arch_location/1_15_841597045.dbf     ARCH            15 NO  18-MAY-14
orcl_s_tns                                    ARCH            15 NO  18-MAY-14
/u01/log/arch_location/1_16_841597045.dbf     ARCH            16 NO  18-MAY-14
orcl_s_tns                                    ARCH            16 NO  18-MAY-14
orcl_s_tns                                    LGWR            17 NO  18-MAY-14


主要看applied列，显示为YES的归档日志已经被应用，可以尝试删除（删除前最好有该归档日志的备份，方便以后恢复）


查看standby数据库未接收的日志文件（切记未接收完成的文件不可删除）
SQL> select local.thread#,local.sequence# from
  2  (select thread#,sequence# from v$archived_log where dest_id=1) local
  3  where local.sequence# not in
  4  (select sequence# from v$archived_log where dest_id=2 and
  5  thread# = local.thread#);


no rows selected


物理删除旧的归档日志：（在此之前，最好去看一下你的备份集中日志的备份信息）
[oracle@localhost admin]$ cd /u01/log/arch_location/
[oracle@localhost arch_location]$ ll
total 49724
-rw-r----- 1 oracle oinstall   145408 Mar  8 19:36 1_13_841597045.dbf
-rw-r----- 1 oracle oinstall    22016 Mar  8 19:38 1_14_841597045.dbf
-rw-r----- 1 oracle oinstall 47468544 May 18 04:49 1_15_841597045.dbf
-rw-r----- 1 oracle oinstall   278528 May 18 04:52 1_16_841597045.dbf
-rw-r----- 1 oracle oinstall  2769920 May 18 05:01 1_17_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:02 1_18_841597045.dbf
-rw-r----- 1 oracle oinstall   125952 May 18 05:04 1_19_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:05 1_20_841597045.dbf
-rw-r----- 1 oracle oinstall     2560 May 18 05:05 1_21_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:05 1_22_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:05 1_23_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:05 1_24_841597045.dbf
-rw-r----- 1 oracle oinstall     1024 May 18 05:05 1_25_841597045.dbf


[oracle@localhost arch_location]$ rm -rf 1_1*
[oracle@localhost arch_location]$ ll
total 24
-rw-r----- 1 oracle oinstall 1024 May 18 05:05 1_20_841597045.dbf
-rw-r----- 1 oracle oinstall 2560 May 18 05:05 1_21_841597045.dbf
-rw-r----- 1 oracle oinstall 1024 May 18 05:05 1_22_841597045.dbf
-rw-r----- 1 oracle oinstall 1024 May 18 05:05 1_23_841597045.dbf
-rw-r----- 1 oracle oinstall 1024 May 18 05:05 1_24_841597045.dbf
-rw-r----- 1 oracle oinstall 1024 May 18 05:05 1_25_841597045.dbf


登入rman将废弃的归档日志清除：


RMAN> crosscheck archivelog all;


using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
validation failed for archived log
archive log filename=/u01/log/arch_location/1_13_841597045.dbf recid=24 stamp=841692969
validation failed for archived log
archive log filename=/u01/log/arch_location/1_14_841597045.dbf recid=26 stamp=841693096
validation failed for archived log
archive log filename=/u01/log/arch_location/1_15_841597045.dbf recid=28 stamp=847860548
validation failed for archived log
archive log filename=/u01/log/arch_location/1_16_841597045.dbf recid=30 stamp=847860745
validation failed for archived log
archive log filename=/u01/log/arch_location/1_17_841597045.dbf recid=33 stamp=847861298
validation failed for archived log
archive log filename=/u01/log/arch_location/1_18_841597045.dbf recid=34 stamp=847861326
validation failed for archived log
archive log filename=/u01/log/arch_location/1_19_841597045.dbf recid=37 stamp=847861499
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_20_841597045.dbf recid=39 stamp=847861502
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_21_841597045.dbf recid=41 stamp=847861507
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_22_841597045.dbf recid=43 stamp=847861509
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_23_841597045.dbf recid=45 stamp=847861511
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_24_841597045.dbf recid=47 stamp=847861518
validation succeeded for archived log
archive log filename=/u01/log/arch_location/1_25_841597045.dbf recid=49 stamp=847861519
Crosschecked 13 objects




删除废弃的归档日志，由于是dg环境，此时report obsolete是看不到信息的
RMAN> delete expired archivelog all;


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
24      1    13      X 08-MAR-14 /u01/log/arch_location/1_13_841597045.dbf
26      1    14      X 08-MAR-14 /u01/log/arch_location/1_14_841597045.dbf
28      1    15      X 08-MAR-14 /u01/log/arch_location/1_15_841597045.dbf
30      1    16      X 18-MAY-14 /u01/log/arch_location/1_16_841597045.dbf
33      1    17      X 18-MAY-14 /u01/log/arch_location/1_17_841597045.dbf
34      1    18      X 18-MAY-14 /u01/log/arch_location/1_18_841597045.dbf
37      1    19      X 18-MAY-14 /u01/log/arch_location/1_19_841597045.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=/u01/log/arch_location/1_13_841597045.dbf recid=24 stamp=841692969
deleted archive log
archive log filename=/u01/log/arch_location/1_14_841597045.dbf recid=26 stamp=841693096
deleted archive log
archive log filename=/u01/log/arch_location/1_15_841597045.dbf recid=28 stamp=847860548
deleted archive log
archive log filename=/u01/log/arch_location/1_16_841597045.dbf recid=30 stamp=847860745
deleted archive log
archive log filename=/u01/log/arch_location/1_17_841597045.dbf recid=33 stamp=847861298
deleted archive log
archive log filename=/u01/log/arch_location/1_18_841597045.dbf recid=34 stamp=847861326
deleted archive log
archive log filename=/u01/log/arch_location/1_19_841597045.dbf recid=37 stamp=847861499
Deleted 7 EXPIRED objects




重启主数据库查看数据库的健壮情况（生产库就不用了）：


RMAN> startup


connected to target database (not started)
Oracle instance started
database mounted
database opened


Total System Global Area     167772160 bytes


Fixed Size                     1218316 bytes
Variable Size                 71305460 bytes
Database Buffers              92274688 bytes
Redo Buffers                   2973696 bytes




SQL> select status from v$instance;


STATUS
------------
OPEN

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29320885/viewspace-1163695/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-1163695/