How to properly delete archived logs when Oracle archive logs are full

When the Oracle archive log is full, you will not be able to log in to Oracle properly, and you need to delete a subset of the archived logs to log in to Oracle properly.

First, delete the archive log physical files, archive logs are generally located in the archive directory, AIX system file format is "1_17884_667758186.DBF", it is recommended to backup the database before operation, delete at least the last few days of the log for database recovery.

Second, after the physical file of the archived log is deleted, we can log into Oracle normally, but the archive log is not completely deleted, and Oracle's controlfile still records these archivelog information. In Oracle's OEM manager there is a visual log showing that when we manually clear the files in the archive directory, these records are not erased from the controlfile, and then we have to do this job.

We use Rman to delete operations, the following steps: (Window client system for example)

1. Specifying a DB instance

C:/Documents and Settings/administrator>set Oracle_sid =ORCL

2. Connect to the database

C:/Documents and Settings/administrator>rman TARGET Sys/[email protected]

3. View the status of archived logs

rman> list Archivelog all;

4. Manually delete archived log files

Rman> DELETE ARCHIVELOG all completed before ' SYSDATE-7 ';

Description
SYSDATA-7, indicates that the current system time 7 days ago, the Before keyword represents the archive log 7 days ago, and if the flashback function is used, the flashback data is also deleted.
In the same way, you can delete all logs from 7 days ago to the present, but this command should be considered clearly, after this deletion, it is best to make full backup database immediately
DELETE ARCHIVELOG from time ' SYSDATE-7 '; Delete all logs from 7 days ago to the present, with caution
Unix/linux can also find the archived data 7 days ago using the EXEC sub-operation to delete
Find/oraarchive-xdev-mtime +7-name "*.dbf"-exec rm-f {};
Doing so will still leave an undocumented archive in Rman
You still need to execute the following 2 commands in Rman
Crosscheck Archivelog All;
Delete Expired Archivelog all;
So it's not as good as the method above, but the advantage of using find is that you can do a lot of things on the condition, and on the Exec subkey, to achieve more complex functions

5. Exiting Rman

Rman> exit

Description: RMAN is a backup recovery tool that comes with Oracle database software , a command-line approach that is similar to Dos and is operated by a keyboard.

Connecting the local database via Rman is very simple, take the W indows platform as an example and go to the command prompt interface:

C:/Documents and settings/junsansi> SET oracle_sid =jssbook

C:/Documents and settings/junsansi> RMAN TARGET/

Recovery manager:release 10.2.0.1.0-production on Tue Mar 17 21:12:17 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to target Database:jssbook (dbid=1415261003)

rman>

As shown above, before using Rman to connect to a local database, you must first set the operating system environment variable: ORACLE_SID, and specify that the value equals the instance name of the target database. If there is only one instance of the local library and the ORACLE_SID environment variable has been set, you do not need to specify ORACLE_SID. Rman automatically connects to the default instance.

Of course, you can also start Rman first, and then connect to the target database via the Connect command, as shown here:

C:/Documents and Settings/junsansi> RMAN

Recovery manager:release 10.2.0.1.0-production on Tue Mar 17 21:12:36 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Rman> Connect Target/

connected to target Database:jssbook (dbid=1415261003)

The local connection allows the use of operating system authentication, so the above example does not require a user name and password, but if operating system authentication is not enabled locally, you must specify a user name and password with the appropriate permissions.

It is important to note that the Export command should be used for setting operating system environment variables in Linux/unix environment, and ORACLE_SID must be uppercase. For example:

[Email protected] ~]$ export oracle_sid=test08

[Email protected] ~]$ Rman target/

Recovery manager:release 10.2.0.3.0-production on Tue Mar 17 16:08:32 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to target database:test08 (dbid=3812548755)

rman>

Connecting to a remote database

If the target database to which you are connecting is a remote database, you must specify a valid network service name (NET services name) when you establish the connection, and the local Tnsname.ora file must have the correct configuration for that network service name. The connection examples are as follows:

C:/Documents and settings/junsansi> RMAN TARGET Sys/[email protected]

Recovery manager:release 10.2.0.1.0-production on Tue Mar 17 16:12:32 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to target Database:testdb (dbid=2220262593)

rman>

How to properly delete archived logs when Oracle archive logs are full