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

This article will explain in detail what to do about the adg main library in oracle that cannot be deleted and archived through rman. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Oracle 11.2.0.4 ADG environment

The main library os space is tight, found that archived logs occupy a large proportion! But our backup script is executed every day.

CROSSCHECK ARCHIVELOG ALL

DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-31'

That is, it has been retained for 31 days, so how can the 31-day one be so big?

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oradata/ctidb/arch/

Oldest online log sequence 1319

Next log sequence to archive 1321

Current log sequence 1321

SQL > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@BJ-CTI-17 ctidb] $cd / data/oradata/ctidb/arch/

[oracle@BJ-CTI-17 arch] $ll-rt

-rw-r- 1 oracle oinstall 369649664 Nov 8 2017 1_303_950667461.dbf

-rw-r- 1 oracle oinstall 56832 Nov 8 2017 1_304_950667461.dbf

-rw-r- 1 oracle oinstall 1024 Nov 8 2017 1_305_950667461.dbf

-rw-r- 1 oracle oinstall 377382400 Nov 9 2017 1_306_950667461.dbf

-rw-r- 1 oracle oinstall 55808 Nov 9 2017 1_307_950667461.dbf

-rw-r- 1 oracle oinstall 1024 Nov 9 2017 1_308_950667461.dbf

-rw-r- 1 oracle oinstall 305280000 Aug 10 2017 1_30_950667461.dbf

-rw-r- 1 oracle oinstall 373349376 Nov 10 2017 1_309_950667461.dbf

-rw-r- 1 oracle oinstall 141824 Nov 10 2017 1_310_950667461.dbf

-rw-r- 1 oracle oinstall 1024 Nov 10 2017 1_311_950667461.dbf

Is it strange to find that there are still archived log files from a year ago?

1) View the archived file information recorded in the control file

SQL > select name, SEQUENCE# from v$archeved_log

Name SEQUENCE#

-

Accdbdg 1268

1268

Accdbdg 1269

/ caadb/oradata/arch/accdb/1_1269_943625125.dbf 1269

Accdbdg 1270

/ caadb/oradata/arch/accdb/1_1270_943625125.dbf 1270

Accdbdg 1271

2) check the archive of 31 days ago in rman and show it is empty. The control file does not record the relevant information.

RMAN > list archivelog all completed before 'sysdate-31'

3) check the archive from 30 days ago in rman, and you will have it! The note records the information of the countdown 31 days.

RMAN > list archivelog all completed before 'sysdate-30'

List of Archived Log Copies for database with db_unique_name ACCDB

=

Key Thrd Seq S Low Time

-

2536 1 1269 A 05-JUL-18

Name: / caadb/oradata/arch/accdb/1_1269_943625125.dbf

2538 1 1270 A 06-JUL-18

Name: / caadb/oradata/arch/accdb/1_1270_943625125.dbf

2540 1 1271 A 06-JUL-18

Name: / caadb/oradata/arch/accdb/1_1271_943625125.dbf

Looking at the archive files in the archive directory again, I found that I did keep the last 31 days of archiving. It turned out that there was adg when the archiving SEQUENCE# was 1268. Before that, the control files of the archived master library, including 1268, were no longer recorded! Therefore, it is impossible to delete it by executing the following command

CROSSCHECK ARCHIVELOG ALL

DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-31'

The solution is to delete the archive from the os level through the find command!

[oracle@BJ-CTI-17 arch] $find / data/oradata/ctidb/arch-name "* .dbf"-mtime + 30-exec rm-f {}\

1: about the main database archive deletion policy of adg:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

The policy corresponds to three values:

1) NONE: when set to this value, the deletion policy for archived files is not enabled. NONE is the default.

2) APPLIED ON STANDBY:

When set to this value, it is forced to check whether the log to be deleted is already in the slave apply, and only the log after apply can be deleted.

When you delete logs that are still needed by the Standby database through an additional DELETE INPUT clause, you will be prompted with a RMAN-08137 error and cannot be deleted. However, it can still be deleted manually through DELETE ARCHIVELOG.

3) SHIPPED TO ALL STANDBY:

RMAN > CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Using target database control file instead of recovery catalog

New RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

New RMAN configuration parameters are successfully stored

Note: if you set APPLIED ON STANDBY, when the archive has been normally passed to standby, it can be manually deleted through DELETE ARCHIVELOG, but not sent to standby due to network problems, so your DELETE ARCHIVELOG cannot be deleted!

Second, the method of deleting archives:

1. Delete archives that do not exist in the os directory, that is, delete the information recorded in the control file

RMAN > crosscheck archivelog all

RMAN > delete expired archivelog all

two。 Delete archives that exceed the recovery policy

RMAN > delete noprompt obsolete; # not only deletes expired backups, but also deletes related archives!

Use "delete noprompt obsolete;" in the RMAN script to delete backups that exceed the save policy.

In the past, my backup script used to deal with archived logs with "plus archivelog delete all input". After backing up the archivelog, I deleted it immediately. I always thought that "delete noprompt obsolete;" only deleted the backup set but not the archive.

When doing DataGuard, Primary needs to save the most recent archivelog so that it can be picked up when the archivelog gap appears in standby.

It was found in the experiment that it would also delete the archivelog related to obsolete backupset.

All dataguard cannot be deleted with the command delete noprompt obsolete;.

What command does it take to delete it?

Delete archivelog until time 'sysdate-7'; delete all archivelog as of the first 7 days

3. Delete an archive from n days ago

Delete archivelog all completed before 'sysdate-N'

Note:

List archivelog until time 'sysdate-1'; is based on the start time of the archive log, or fisrt_time, as the deadline.

List archivelog all completed before 'sysdate-1' is based on the completion time of the archive log, or completion_time, as the deadline.

Just imagine if you delete the archive log, you still use delete archivelog all completed before 'sysdate-N'

4. Delete the archive from 30 days ago by os command

[oracle@BJ-CTI-17 arch] $find / data/oradata/ctidb/arch-name "* .dbf"-mtime + 30-exec rm-f {}\

This is the end of the article on "what to do if the adg main library in oracle cannot be deleted and archived through rman". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.