邏輯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