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

This article mainly introduces the method of cleaning up the database history log of Oracle 11g, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

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 sysdbaSQL > 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 log cleanup

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 dumpSQL > exit$ cd / u01ActionActionoracleCharageDiagram rdbmsUniteddb11gOnOnDb11gActionTrace$ 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 (back up the listening file) $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 alertChoose the alert log from the following homes to view:1: diag/rdbms/db1/db12: diag/tnslsnr/oracle/listenerQ: to quitPlease 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

AdrciADRCI: Release 11.2.0.4.0-Production on Fri Jun 2 18:31:34 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.ADR base = "/ u01/app/oracle" adrci > show homeADR Homes:diag/tnslsnr/db/listenerdiag/rdbms/cams/camsadrci > set homepath diag/rdbms/cams/camsadrci > 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.

Thank you for reading this article carefully. I hope the article "Oracle 11g how to clean up the database history log" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you 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.