How to query oracle archive logs

In Oracle, you can use the select statement with "v$recovery_file_dest" to query the Oracle archive log. The select statement is used to select data from the database, and the syntax is "select * from v$recovery_file_dest".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query the oracle archive log

Check the size and usage of the archive log

select * from v$recovery_file_dest

The example is as follows:

SQL> select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u03/fast_recovery_area
 8589934592 6551817216 
 
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE                          0                         0
              0
REDO LOG                           1.83                         0
              3
ARCHIVED LOG                      74.44                         0
            159

If the ARCHIVED LOG exceeds 90%, Oracle will have it at any time The danger of downtime

Increase the archive log space

SQL> alter system set db_recovery_file_dest_size=20G;
System altered.

Delete the archive log

1. Look at how many archive logs are used now (by percentage):

 select * from v$flash_recovery_area_usage;

If it exceeds 90%, there is a risk of downtime at any time

2. After deleting the archive file, you need to use the RMAN tool to release the space occupied by the archive. First connect the RMAN tool

rman target sys/pass

3 .Check the archive log

crosscheck archivelog all;

4. If you think it is useless, get rid of it. If you want to keep today’s log, you can also use the following statement

delete expired archivelog all;(全部删除)
 
delete archivelog until time 'sysdate - 1';（保留当天删除，删除之前所有）

: $ORACLE_BASE/flash_recovery_area/yours Under the path of instance name/archivelog/, find the archive log file and delete the useless ones

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query oracle archive logs. For more information, please follow other related articles on the PHP Chinese website!