How to deal with the rapid growth of oracle archive logs, oracle archive logs

How to deal with the rapid growth of oracle archive logs, oracle archive logs

Oracle archive logs are generally generated by dml statements, so the increase speed should be too frequent by dml.

First, query the generation of the following daily archives:

Select trunc (FIRST_TIME) "TIME ",
SUM (BLOCK_SIZE * BLOCKS)/1024/1024/1024 "SIZE (GB )"
From v $ ARCHIVED_LOG
Group by trunc (FIRST_TIME );

In this way, the exception occurred when archiving started during that time period.

You can use either of the following methods to analyze archiving logs:

Method 1: Use logminer

1. Use scripts to create related packages.

@ $ ORACLE_HOME/rdbms/admin/dbmslm. SQL
@ $ ORACLE_HOME/rdbms/admin/dbmslmd. SQL;
The first script is used to create the DBMS_LOGMNR package, which is used to analyze log files.
The second script is used to create the DBMS_LOGMNR_D package, which is used to create a data dictionary file.

2. Specify the log file to be analyzed
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node/410771348_78810967000.arc', options => dbms_logmnr.new );

3. Use a local online data dictionary to analyze archived logs
Exec sys. dbms_logmnr.start_logmnr (options => sys. dbms_logmnr.dict_from_online_catalog );

4. query the archived log content, such as the Schema that calculates the maximum modification volume.
Select seg_owner, count (*) from v $ logmnr_contents group by seg_owner;
Select count (1), substr (SQL _redo,) from v $ logmnr_contents group by substr (SQL _redo,) order by count (1) desc;

5. Add other log files
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node/415773333_78810967000.arc ');
Exec sys. dbms_logmnr.add_logfile (logfilename => '/archivelog/node1/415773334_788000067000.arc ');
6. End Analysis of archived logs
Exec sys. dbms_logmnr.end_logmnr;

Method 2: Use toad to view log information

Find logminer in the database and put the logs to be analyzed. The detailed execution records of the database are displayed, and the archived statements are displayed intuitively.

After finding out the statement, I will discuss with the developer whether it can reduce the execution frequency of some dml statements.

How to solve the "archive log full problem"

The oracle 10g database operation interface is opened by the oem. the following error message is displayed: "The Archiving program cannot archive and redo logs because the output device is full or unavailable ." The solution is summarized as follows: Method 1: to increase the size of the archive log space, you can adjust the size of the system's flash recovery zone: first, shut down the database: link to oracle as sys, execute> shutdown immediate; start the database to the mount status:> startup mount to view the size and storage target of the flash recovery zone:> show parameter db_recovery_file_dest modify the size of the flash recovery zone> alter system set db_recovery_file_dest_size = 4G (default: 2G, you can adjust the size according to the actual situation) finally open the database:> alter database open; OK. Solve the problem. Restore the database. Method 2: Enter the oracle clearing log information and release the space to start the database to the mount state:> sqlplus "/as sysdba"> startup mount a new terminal, run the rman command> rman target/(only one oracle10g Database is installed)> crosscheck archivelog all; (list archived log information) command> delete expired archivelog all; (delete the archived logs listed above) command> exit; at this time, you 'd better back up the database and change the database's mount status to open status> alter database open; OK. solve the problem and use the database. Misunderstanding: the log information in the system to clear the archive directory (that is, the archived log is deleted physically, or the archived log is transferred elsewhere) is not available. Although the OS is deleted, however, the oracle system cannot identify that logs have been cleared. It can only enter oracle to clear the log information and release the space (method 2); or set a larger archive space (method 1 ). We recommend that you use both methods to reduce the workload and avoid frequent database suspension. At the same time, it regularly backs up the database completely or other important data.

How to solve the "archive log full problem"

Problem description: the database used is oracle 10 Gb, the archive mode is used, and the default archive space of the oracle system is 2 GB. Because there are too many logs and the space is full, the redo files of the database cannot be archived, the oracle database is suspended. When the oracle 10g database operation interface is opened by the oem, the following error message is displayed: the archiving program cannot archive and redo logs because the output device is full or unavailable. The solution is summarized as follows: Method 1: to increase the size of the archive log space, you can adjust the size of the system's flash recovery zone: first, shut down the database: link to oracle as sys and run shutdown immediate. Start the database to the mount status: startup mount to view the size and storage destination of the flash recovery zone: show parameter db_recovery_file_dest modify the size of the flash recovery area alter system set db_recovery_file_dest_size = 4G (default: 2G, you can adjust the size according to the actual situation) and finally open the database: alter database open; OK, solve the problem. Restore the database. Method 2: Enter oracle to clear the log information and release the space to start the database to the mount status: sqlplus/as sysdbastartup mount a new terminal, run the rman command rman target/(only one oracle10g Database is installed) command crosscheck archivelog all; (list archived log information) command delete expired archivelog all; (delete the archived logs listed above) command exit; at this time it is best to back up the database again and change the database's mount status to the open state alter database open; OK. solve the problem and use the database. Misunderstanding: the log information in the system to clear the archive directory (that is, the archived log is deleted physically, or the archived log is transferred elsewhere) is not available. Although the OS is deleted, however, the oracle system cannot identify that logs have been cleared. It can only enter oracle to clear the log information and release the space (method 2); or set a larger archive space (method 1 ). We recommend that you use both methods to reduce the workload and avoid frequent database suspension. At the same time, it regularly backs up the database completely or other important data.