Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of deleting the archive ORA-08137 in the main library in DataGuard environment". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "How to solve the problem of deleting the archive ORA-08137 in the main database in DataGuard environment"!

1. problem description

Customer environment: 2 nodes rac, Centos 6.5, configured with a single instance physical standby, database data volume of about 200g, daily archive volume of 10g

Problem phenomenon: On November 20, 2019, the source-side backup software fully backs up the database every day. When RMAN deletes the archive three days ago, the monitoring system reports insufficient space. After inspection, the archive still retains the archives from November 12, 2019 to November 20, 2019, and does not delete the archives three days ago. After checking the backup log generated by the backup software, it is found that the error in the log is as follows:

RMAN-08137: WARNING: archived log not delivered, needed for standby or upstream capture processarchived log file name=+FRADG/honor/archivelog/2019_11_22/thread_1_seq_365.534.1024999167 thread=1 sequence=3652.

1) Analysis of causes

Through the description of the error, it is preliminarily judged that there are two reasons:

a. There is a process open holding archive

b. Archived logs are not transferred and applied to physical standby. Since physical standby is configured, by default, if archived logs are not applied to the standby database, the source database is not allowed to delete archives with RMAN to prevent gaps.

(2) Investigation

a. Check whether there is a process holding open undeleted archive

In version 11g, you can use the lsof command in the asmcmd console to view file openings on asm disks:

[root@db-oracle-node1 ~]# su - gridLast login: Thu Nov 21 20:08:26 CST 2019[grid@db-oracle-node1 ~]$ asmcmdASMCMD> lsof -G FRADG

Through inspection, it is found that there is a process that opens the archive on November 13, 2019. Through understanding, this library is not configured with OGG, SharePlex and other replication software, only DataGuard, and DG backup library is in Hefei. The network packet loss is relatively serious. At this time, it is judged that the opened archive on November 13 is DG transmission process. We go to the database and confirm this judgment through the following query statement.

select process,pid,status,thread#,sequence# from v$managed_standby;

But at the same time, by executing the following command, it is found that a part of archives can be deleted at intervals, but the wrong archive serial number is not used by the process, so it is ruled out that there is a possibility that the process holds open and causes ORA-08137.

WMI> delete archivelog until time 'sysdate-3'; or WMI> delete archivelog all completed before 'sysdate-3';

b. Check whether the archive cannot be deleted because it is not applied by DataGuard

Check the physical standby side process status by issuing the following command:

select process,pid,status,thread#,sequence# from v$managed_standby;

It was found that the archive being accepted by the RFS receiving process was indeed an archive sequence#that could not be deleted on November 13.

Check the primary side archive application by issuing the following command:

select thread#,name,max(sequence#),applied from v$archived_log where applied='YES' group by thread#,name, applied;

It is found that the physical standby terminal only applies to the log on November 13, which is 7 days behind the current one. Therefore, it is basically judged that the network packet loss is serious, resulting in slow sending and receiving, resulting in failure to archive and timely receiving by the physical terminal, resulting in failure to delete the application.

3. problem solving

By understanding with the customer, the customer basically gave up using the physical standby, so we can use the following solutions:

(1) Choose the right time to remove DG environment

a. Set primary to maximum performance mode

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

b. Reset Data Guard initialization parameters

LOG_ARCHIVE_CONFIGDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTLOG_ARCHIVE_DEST_n points to Standby Database and this parameter is valid for STANDBY_LOGFILES LOG_ARCHIVE_DEST_STATE_nSTANDBY_ARCHIVE_DESTANDBY_FILE_MANAGEMENTFAL_SERVERFAL_CLIENT

c. Delete Standby Redologies

SQL> SELECT GROUP# FROM V$STANDBY_LOG;SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP ;

d. If necessary, physical standby can be converted to a separate database for use

$ sqlplus / as sysdbaSQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP;

(2) Temporary solution to this problem can be eliminated by setting the parameter log_archive_dest_state_2 to defer and the implicit parameter_deferred_log_dest_is_valid to false to delete ORA-08137.

The_deferred_log_dest_is_valid parameter is TRUE by default, which controls whether primary archives are allowed to be deleted when they are not applied by physical standby when log_archive_dest_state_n is set to defer, so as to prevent gaps from occurring when log_archive_dest_state_n is set to enable again, resulting in physical standby being unavailable. This parameter is dynamically adjustable in version 11.2.0.4 and can be modified online.

SQL> alter system set log_archive_dest_state_2=defer scope=both;SQL> alter system set "_deferred_log_dest_is_valid"='FALSE' scope=both;

(3) Delete archive using force option

RMAN > delete force archivelog until time 'sysdate-3'; At this point, I believe that you have a deeper understanding of "how to solve the main library in DataGuard environment RMAN delete archive times ORA-08137 problem", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.