Monday, January 8, 2018

Slow RMAN Performance for CROSSCHECK and DELETE OBSOLETE

I faced an issue recently where my CROSSCHECK and DELETE OBSOLETE commands were too much slow to execute, actually these were hung. The reason for this issue was IP address change of my NFS server form where a drive was mounted on database server for backups and backups were being taken on this NFS share. After NFS server’s IP address change, RMAN would go to check old IP address to search for the old/obsolete backups when we executed CROSSCHECK and DELETE OBSOLETE commands.

Although this problem was on windows, but same problem may arise on Linux, and reason and solution may be the same.

If you face same problem, you would need to remove records of all backups that were backed up on the old network path, and you need to use CHANGE…UNCATALOG command for his purpose. Otherwise RMAN would always try to search the network path that does not exist now, and RMAN would be very slow or hung. Following is the process of UNCATALOGing the backups that are no more accessible on the NFS.

Find all backups that are under old IP address. There are several ways to find out your backups, but I used REPORT OBSOLETE command as all of my backups on old NFS IP were already 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
-------------------- ------ ------------------ --------------------
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           65324  09-SEP-17
  Backup Piece       65623  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_JUSE04IA_1_1.RBF
Backup Set           65325  09-SEP-17
  Backup Piece       65624  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K1SE0CSB_1_1.RBF
Backup Set           65327  09-SEP-17
  Backup Piece       65625  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K2SE0H64_1_1.RBF
Backup Set           65328  09-SEP-17
  Backup Piece       65626  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K3SE0HNR_1_1.RBF
Backup Set           65329  09-SEP-17
  Backup Piece       65627  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K4SE0I71_1_1.RBF
Backup Set           65330  09-SEP-17
  Backup Piece       65628  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K5SE0PTL_1_1.RBF
Backup Set           65331  09-SEP-17
  Backup Piece       65629  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K6SE0ST2_1_1.RBF
Backup Set           65332  09-SEP-17
  Backup Piece       65630  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_K7SE104U_1_1.RBF
Backup Set           65334  09-SEP-17
  Backup Piece       65631  09-SEP-17          \\172.191.191.80\MYDB\ARC_KGSE21VJ_1_1.RBF
Backup Set           65343  09-SEP-17
  Backup Piece       65588  09-SEP-17          \\172.191.191.80\MYDB\ARC_KHSE21VJ_1_1.RBF
Backup Set           65340  09-SEP-17
  Backup Piece       65637  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KMSE2ASJ_1_1.RBF
Backup Set           65351  09-SEP-17
  Backup Piece       65638  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KNSE2ASJ_1_1.RBF
Backup Set           65349  09-SEP-17
  Backup Piece       65639  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KOSE2ASK_1_1.RBF
Backup Set           65348  09-SEP-17
  Backup Piece       65640  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KPSE2ASL_1_1.RBF
Backup Set           65352  09-SEP-17
  Backup Piece       65641  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KQSE2BHG_1_1.RBF
Backup Set           65353  09-SEP-17
  Backup Piece       65642  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KRSE2BIU_1_1.RBF
Backup Set           65354  09-SEP-17
  Backup Piece       65643  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KSSE2BJ5_1_1.RBF
Backup Set           65355  09-SEP-17
  Backup Piece       65644  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KTSE2BRQ_1_1.RBF
Backup Set           65356  09-SEP-17
  Backup Piece       65645  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KUSE2C5D_1_1.RBF
Backup Set           65357  09-SEP-17
  Backup Piece       65646  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_KVSE2C5V_1_1.RBF
Backup Set           65359  09-SEP-17
  Backup Piece       65647  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170909_L0SE2CAH_1_1.RBF
Backup Set           65375  09-SEP-17
  Backup Piece       65653  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LFSE4V85_1_1.RBF
Backup Set           65376  09-SEP-17
  Backup Piece       65654  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LGSE4V85_1_1.RBF
Backup Set           65374  09-SEP-17
  Backup Piece       65655  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LHSE4V86_1_1.RBF
Backup Set           65373  09-SEP-17
  Backup Piece       65656  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LISE4V86_1_1.RBF
Backup Set           65378  09-SEP-17
  Backup Piece       65657  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LJSE4VQG_1_1.RBF
Backup Set           65377  09-SEP-17
  Backup Piece       65658  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LKSE4VQH_1_1.RBF
Backup Set           65379  09-SEP-17
  Backup Piece       65659  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LLSE4VQK_1_1.RBF
Backup Set           65380  09-SEP-17
  Backup Piece       65660  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LMSE5054_1_1.RBF
Backup Set           65381  09-SEP-17
  Backup Piece       65661  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LNSE50BS_1_1.RBF
Backup Set           65382  09-SEP-17
  Backup Piece       65662  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LOSE50CM_1_1.RBF
Backup Set           65383  09-SEP-17
  Backup Piece       65663  09-SEP-17          \\172.191.191.80\MYDB\ORA_20170910_LPSE50F2_1_1.RBF

Next step is to use a text editor to create a CHANGE…UNCATALOG command for all above backup pieces. Following is the example of the commands that I created. Once you create these commands, just copy/paste the commands on RMAN prompt to execute the commands.
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_JUSE04IA_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K1SE0CSB_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K2SE0H64_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K3SE0HNR_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K4SE0I71_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K5SE0PTL_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K6SE0ST2_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_K7SE104U_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ARC_KGSE21VJ_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ARC_KHSE21VJ_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KMSE2ASJ_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KNSE2ASJ_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KOSE2ASK_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KPSE2ASL_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KQSE2BHG_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KRSE2BIU_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KSSE2BJ5_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KTSE2BRQ_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KUSE2C5D_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_KVSE2C5V_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170909_L0SE2CAH_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LFSE4V85_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LGSE4V85_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LHSE4V86_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LISE4V86_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LJSE4VQG_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LKSE4VQH_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LLSE4VQK_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LMSE5054_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LNSE50BS_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LOSE50CM_1_1.RBF' uncatalog;
change backuppiece '\\172.191.191.80\MYDB\ORA_20170910_LPSE50F2_1_1.RBF' uncatalog;

After above script/commands was executed, our CROSSCHECK and DELETE OBSOLETE commands started executing smoothly because RMAN would not go to check old NFS share.

1 comment:

  1. RMAN> allocate channel for maintenance device type sbt
    parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';

    RMAN> delete force obsolete;

    ReplyDelete

Popular Posts - All Times