My database is oracle12c(12.1.0.2.0) version. One of my database obsolete archive log files were not getting deleted and getting the below error..
I followed the temp solution to delete the old archive log files manually.
Here is the current RMAN setting...
RMAN>delete noprompt force archivelog until time 'sysdate-14';
RMAN-08137: WARNING: archived log not deleted, needed for standby or
upstream capture process
archived log file
name=+DATA/XXX_AZ/ARCHIVELOG/2016_09_28/thread_2_seq_12062.22129.923740541
thread=2 sequence=12062
RMAN>
|
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name
FRONTEND_AZ are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/u04/rmanbackup/FRONTEND1/auto_%d_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #
default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'
OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/xxx_az/controlfile/snap_XXX.ctl';
RMAN>
|
Temporary solution :
Step 1 Change the Archive log deletion policy to STANDBY.
RMAN> CONFIGURE ARCHIVELOG
DELETION POLICY TO SHIPPED TO STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archived log deletion policy
|
Step 2 Delete the obsolete archive log files.
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12858.23739.924517385 RECID=73224 STAMP=924517385
deleted archived log
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12859.23741.924518285 RECID=73230 STAMP=924518284
deleted archived log
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12860.23743.924519185 RECID=73236 STAMP=924519185
deleted archived log
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12861.23745.924520085 RECID=73242 STAMP=924520084
deleted archived log
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12862.23747.924520985 RECID=73248 STAMP=924520985
deleted archived log
archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12863.23749.924521885 RECID=73254 STAMP=924521884
Deleted 21176 objects
RMAN-08591: WARNING: invalid archived log deletion policy
RMAN>
|
Now archive log files are getting deleted successfully!!
Step 3 Change the RMAN deletion policy back to original.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
|
Step 4 Verify the RMAN settings.
RMAN configuration parameters for database with db_unique_name XXXXXXX_AZ are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u04/rmanbackup/XXXXXXX1/auto_%d_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/XXXXXXX_az/controlfile/snap_XXXXXXX.ctl';
RMAN>
|
Parmanent solution : The golden gate was implemented previously for data replication to upgrade to Oracle 12c. But Golden Gate is no more used and still there are couple of Golden Gate extract process is running and it caused the archive log deletion issue.
The currently running Golden Gate extract process stopped and archive log deletion job started deleting all the obsolete archive log files.
Here are the steps i followed to currently running extract process :
The currently running Golden Gate extract process stopped and archive log deletion job started deleting all the obsolete archive log files.
Here are the steps i followed to currently running extract process :
GGSCI (hostname) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED FEEXT 00:00:00 5234:47:30
EXTRACT RUNNING FEPMP 00:00:00 00:00:05
EXTRACT STOPPED GWEXT 00:00:02 4671:09:27
EXTRACT STOPPED GWPMP 00:00:00 4671:09:09
EXTRACT STOPPED S 00:00:01 6650:16:15
|
ggsci> delete extract FEEXT
ggsci> stop
extract FEPMP
ggsci> delete
extract FEPMP
ggsci> delete
extract GWEXt
ggsci> delete
extract GWPMP
ggsci> delete
extract S
ggsci> stop
mgr
It will prompt
(y/n)---- y and continue (press enter)
ggsci> delete
mgr
ggsci> info
all
|
