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

This article mainly introduces "how Oracle11g cleans up the history log of the database". In the daily operation, I believe that many people have doubts about how to clean up the history log of the database by Oracle11g. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to clean the history log of the database by Oracle11g". Next, please follow the editor to study!

1. Create a path to the log file to be deleted in the database

It is used to store and prepare for deletion. It is assumed to be placed under the / home/oracle/delete path.

$cd / home/oracle/delete

$mkdir-p audit_file_dest background_dump_dest user_dump_dest core_dump_dest listenr_log_dest

two。 View the paths where different database log files are located

$sqlplus / as sysdba

SQL > show parameter dest

Find the corresponding value of audit_file_dest,background_dump_dest,user_dump_dest,core_dump_dest

The database audit information file is stored under the audit_file_dest path.

The trace file and alert log file of the database are stored under the background_dump_dest path.

The trace file of session after sql trace is stored under the user_dump_dest path.

Application logs are stored in the core_dump_dest path, and there is basically no information unless there is a problem with the database.

The following uses boldface audit_file_dest,background_dump_dest,user_dump_dest,core_dump_dest instead of the real path.

3. Database audit information file cleaning

Under the audit_file_dest path, move the files from 30 days ago to the folder to be deleted, and execute the command as follows:

(1) execute the following command to check the current path to determine whether it is correct

$cd audit_file_dest

$pwd

(2) execute the following command to view the audit documents from 30 days ago.

$find audit_file_dest-mtime + 30-name\ * .aud

(3) execute the following command to move the file to the folder to be deleted

$find audit_file_dest-mtime + 30-name\ * .aud-exec mv {} / home/oracle/delete/audit_file_dest\

(4) check the audit files 30 days ago under the audit directory and the files under the folder to be deleted

$find audit_file_dest-mtime + 30-name\ * .aud

$ls / home/oracle/delete/audit_file_dest

(5) after waiting for 2 days to ensure that the removal of log files has no effect on the operation of Oracle database, execute the delete command manually.

$cd / home/oracle/delete

$rm-rf audit_file_dest

4. Database trace files and alert logs clean up 4. 1. Clean up trace files

Under the background_dump_dest path, move the files from 30 days ago to the folder to be deleted, and execute the command as follows:

(1) execute the following command to check the current path to determine whether it is correct

$cd background_dump_dest

$pwd

(2) execute the following command to view the audit documents from 30 days ago.

$find background_dump_dest-mtime + 30-name\ * .tr*

(3) execute the following command to move the file to the folder to be deleted

$find background_dump_dest-mtime + 30-name\ * .tr*-exec mv {} / home/oracle/delete/background_dump_dest\

(4) check the audit files 30 days ago under the audit directory and the files under the folder to be deleted

$find background_dump_dest-mtime + 30-name\ * .aud

$ls / home/oracle/delete/background_dump_dest

(5) after waiting for 2 days to ensure that the removal of log files has no effect on the operation of Oracle database, execute the delete command manually.

$cd / home/oracle/delete

$rm-rf background_dump_dest

4.2. Clean up the alert log

Alert logs, which can directly delete alert logs (or echo cleanup) and can be generated automatically without shutting down the database (preferably if the original cp is backed up)

(1) enter the path where the log is located

$cd background_dump_dest

(2) cp backup

Cp alert_$ORACLE_SID.log alert_$ORACLE_SID.log.bak (make sure there is enough space)

(3) delete the original alert log file

Method 1:echo > alert_$ORACLE_SID.log (the original alert file is still there after this method is cleaned)

Method 2:rm alert_$ORACLE_SID.log (delete directly)

(4) the alert file deleted by test method 2 will be generated automatically.

SQL > alter system switch logfile

(5) see if a new log file is generated.

SQL > show parameter dump

SQL > exit

$cd / u01/app/oracle/diag/rdbms/db11g/db11g/trace

$ls-lrt

You can see the newly generated alert log file

5. Database sql trace file cleanup

The default user_dump_dest and background_dump_dest paths of the database are the same. If they are inconsistent, please refer to the method of 4.1for operation.

6. Database snooping log cleanup

The snooping log and alarm log of Oracle 11g are in the $ORACLE_BASE/diag/tnslsnr/hostname/listener/trace and $ORACLE_BASE/diag/tnslsnr/hostname/listener/alert directories, respectively. The previous hostname depends on the actual hostname. The following directories for snooping logs and alarm logs are referred to as the trace directory and the alert directory.

The log files generated under the trace directory are relatively large and are the main cleaning objects, while the files generated under the alert directory are relatively small and basically do not need to be cleared. The listening log in the trace directory is a file called listener.log, while the warning log in the alert directory is a file called log.xml, which is divided into a log_XXXX.xml log every 11m or so, gradually accumulating.

The method to delete the listening log is as follows:

Switch to oracle user

$su oracle

$lsnrctl set log_status off

$mv listener.log listener.log.bak (backup listening files)

$lsnrctl set log_status on (a listener.log file is automatically created at this time)

After the listening log is started, a new listener.log file is automatically created, and the backed-up files can be deleted.

If you delete the listener.log directly without executing the above command, the system will not create it.

The warning log can be moved or deleted with the following command (go to the specified directory first, and then execute this command)

Find. -mtime + 10-exec mv {} / home/oracle/delete/listenr_log_dest\

To see the warning log, you can execute adrci under the oracle user

$adrci

You can enter help in the adrci directory and see the help command. Enter help show alert and you can see the detailed usage of show alert.

Show alert

You will be prompted to select the directory of the log or exit.

Adrci > show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/db1/db1

2: diag/tnslsnr/oracle/listener

Q: to quit

Please select option: 1

Enter 1 to see the db1, enter 2 to see the listener directory, enter Q to exit.

After reading the alert log, enter: Q to exit, which is similar to the vi command.

SHOW TRACEFILE

You can see the monitoring log file

7. The adrci command uses the

11G adrci (Automatic Diagnostic Repository Command Interpreter) Clean ALERT | INCIDENT | TRACE | CDUMP | HM | Files in the UTSCDMP directory

PS: additional 11g can also be cleaned with adrci: the specific steps are as follows

$adrci

ADRCI: Release 11.2.0.4.0-Production on Fri Jun 2 18:31:34 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/ u01/app/oracle"

Adrci > show home

ADR Homes:

Diag/tnslsnr/db/listener

Diag/rdbms/cams/cams

Adrci > set homepath diag/rdbms/cams/cams

Adrci > help purge

Usage: PURGE [[- I |] |

[- age [- type ALERT | INCIDENT | TRACE | CDUMP | HM | UTSCDMP]]:

Purpose: Purge the diagnostic data in the current ADR home. If no

Option is specified, the default purging policy will be used.

Options:

[- I id1 | id1 id2]: Users can input a single incident ID, or a

Range of incidents to purge.

[- age]: Users can specify the purging policy either to all

The diagnostic data or the specified type. The data older than

Ago will be purged

[- type ALERT | INCIDENT | TRACE | CDUMP | HM | UTSCDMP]: Users can specify what type of

Data to be purged.

Examples:

Purge

Purge-I 123 456

Purge-age 60-type incident

You can use this method if you want to clean up the contents of the / u01/app/oracle/diag/rdbms/cams/cams directory.

At this point, the study on "how Oracle11g cleans up the history log of the database" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.