RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

-

 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..


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>


I followed the temp solution to delete the old archive log files manually.

Here is the current RMAN setting...


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.

 RMAN>delete noprompt force archivelog until time 'sysdate-14';


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> show all;

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 :

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 

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more