Trace Files Purging in Oracle

Back to: Oracle DBA Tutorials

Trace Files Purging in Oracle with Examples

In this article, I am going to discuss Trace Files Purging in Oracle with Examples. Please read our previous article where we discussed Alert Log using ADRCI in Oracle with Examples. Until now we learned about the alert log files. We learned how to view the alert log file using the text editor and as well as the VI editor using the tool ADRCI.

What is Trace File in Oracle?

Trace files contain the error information besides that it also contains information that can provide guidance for tuning an application or an instance. We need to contact oracle support services if there are any internal error that occurs in the database. This is the important thing we need to know about the trace files.

Each server process and background process can write to an associated trace file. There must be a question how can we distinguish the trace file created by the server process or the trace file created using the background process? Let’s assume two files.

• Jayadb_dbw0_22120.trc
• Jayadb_ora_2243.trc

So, in this case, we have two files. The file which contains dbw0 is created from the background process. The file has a format – instance_name_dbw_number.trc. In our case, we have the instance name as jayadb and dbw indicates database writer and .trc extension. Trace files that were created using the background process are named after their processes. The trace file can be generated by job queue processes.

The second file is created using the server process. The server process file contains ora in the middle. Oracle database includes an advanced fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving problems. When a critical error occurs:

• An incident number is assigned to the error.
• Diagnostic data for the error such as trace files are immediately captured and tagged with the incident number.
• Data is stored in the ADR.

ADR files can be automatically purged with retention policy parameters. In days these files will have a huge size. By this retention policy method, the trace files will be purged.

Purging Mechanism in Oracle:

The Purging mechanism allows you to specify a retention policy stating. As the days go on there are many processes like server process, background process, and other DDL operations that will create a lot of log files in the server. This will eat up a lot of space in the server. In order to avoid this oracle needs to purge the files.

We have a long retention period is used for relatively higher-value diagnostic data such as incidents and alert logs. The default value is 365 days. The information in trace files is very important, so oracle stores the trace files for one year long. The short retention period is used for traces and core dumps. The default value is 30 days. The dump files are not that important.

Older items are deleted first. The long retention period items are typically older than any of the items in the short retention period. So, a mechanism is used in which the time periods are “scaled” so that roughly the same percentage of each period item gets deleted. Some components use these periods in slightly different ways. For example, IPS the packaging facility uses the short retention period to determine when to purge packaging metadata and the staging directory contents. However, the age of the data is based on when the package was completed, not when the package is created.

The size-based retention specifies a target size of the ADR home. When purging the old data determined by the time-based retention periods, is deleted first. If the size of the ADR home is still greater than the target size, diagnostics are automatically deleted until the target size is no longer exceeded. We can even specify the size of the ADR home as 1GB. The files will be deleted if the ADR home size crosses 1 GB.

No, let’s go ahead and open the Linux machine and open the file manager. Once the file manager is open go to the below location.

You can see the trace folder. Open the trace folder.

The first file in the folder is the alert log file. We can see there are many other files. These files are trace files. Let’s take any file from this folder and check whether the file is created using the background process or else from the server process.

From the name, we can see the name doesn’t contain ora in it. So, the file jayadb_aqpc_21733.trc is created using the background process. If the file name contains jayadb_ora then it is created using the server process. We have another file called jaydb_aqpc_16435.trm. we are not focusing on TRC files. Even the expert dba will not be able to understand all the terms in the TRM files. The most important thing is if we have any errors in the database the trace files will be moved to the incident folder. Let’s try to open the incident folder.

We can see the folder is empty. So, there are no errors in the database. The trace files are additional information for both background processes and server processes.

Examples to Understand Trace Files Purging in Oracle:

Let us do some practice sessions about the trace files.

Step 1: Open the terminal and connect to ADRCI.

Step 2: Set the home path.

Every time when we connect to ADRCI before querying about the policies we have to set the ADR home path. Below is the command for setting the home path.

Command: set HOMEPATH diag/rdbms/jayadb/jayadb

Step 3: Querying the size policy of trace files.

Qeury: select sizep_policy from adr_control_aux;

From the output, we can see there is a huge number. We don’t require this huge amount of size. Let’s try to change the size. The size is represented in bytes.

Step 4: Querying the short policy and long policy for the ADR path.

Now, let us try to query the short policy and long policy for the ADR path.

Query: select shortp_policy, longp_policy from adr_control;

From the output we can see shortp_policy as 720 and longp_policy as 8760. But before we discussed that the shortp_policy is 30 days and the longp_policy is 365 days. Because in the ADRCI the shortp_policy and longp_policy are measured in hours. If we divide 720/24 then it will be 30 days. So, no need to worry about the numbers in ADRCI. Those are measured in hours.

So, we have 30 days for the short policy and 365 days for the long policy. Remember that the long policy is for higher diagnostic data such as incidents and alert logs and the short policy is for traces and core dumps.

Step 5: Estimation of size policy.

We have to mention the sizep_policy with some size in the bytes and the ADRCI tool will give us the output of whether we can use that size or not. Let’s try to choose sizep_policy as 200MB. Then the numeric value in bytes will be 200000000 bytes. If we provide this size then the Oracle will display the shortp_policy and longp_policy that we can use to change the size of ADR.

Query: estimate (sizep_policy = 200000000)

We are getting the output as unknown due to lack of information but the output would be similar to the below.

Output:
Estimate
Short policy hours: 6452
Long policy hours: 78509
Size Policy Bytes: 200000000

So, in order to use the size as 200MB then we have to use the shortp_policy as 6452 hours and longp_policy as 78509 hours. This helps us to specify the value of the ADR path.

Let’s try to estimate the size using shortp_policy and longp_policy. We are estimating the size policy for 8 days. After calculating 8 days into hours we will be getting 192 hours. So, let’s try to use the short policy and long policy as 192 hours.

Query: estimate (shortp_policy = 192, longp_policy = 192)

We are getting size policy bytes as 0 due to an internal error but the size of the policy would be 8559020 bytes.

Output:
Estimate
Short policy hours: 192
Long Policy hours: 192
Size policy bytes: 8559020.

Step 6: Update the size policy

Now, that we have understood the size policy. Let’s update the size policy for 8 days. So, if we consider 8 days then the command to set the policy will be as below.

Query:
Set control (SHORTP_POLICY = 192);
Set control (LONGP_POLICY = 192);
Set control (SIZEP_POLICY = 8559020);

These are the three commands we used to specify the size of the size policy. Let’s run this command in the ADRCI tool.

So, we have set the sizep_policy. Let’s try to check the current size of the sizse_policy in ADRCI. We can use the previous query to check the sizep_policy.

Query: select shortp_policy, longp_policy from adr_control;

We have set the ADR sizep_policy for 8 days. So, any files which are not 8 days longer will be deleted. We have set the size policy, let’s try to purge the files.

Step 7: Purging the files manually.

Exit out from the ADRCI and check the current working directory.

So, we are currently in the /u01/app/oracle/diag/rdbms/jayadb/jayadb/ path. Let us check the size of the current directory using the command “du -hs”.

From the output, we can see the size of the current directory is 22M. Suppose, we have to reduce the size of the directory it is better to go ahead and delete the files manually. To delete the files manually we will be using the purging concepts. We will be purging the files in the alert folder. To Purge the data, connect to the ADRCI tool.

To purge the data let’s check the term purge using the command “help purge”. You can view a lot more information about the purge command.

If you scroll up a little bit you can see more information about the purge command and how it is used.

We can use “purge -I 123 456“, which is the incidents range created in the incidents folder, and “purge -age 60 -type incident”, which deletes the incident files which are present for more than 60 days. And “purge -size 10000000” which indicates purging the data to 10MB. The purge is a huge topic. We have 22MB in our directory. Let’s try to reduce the size to 6MB.

Command: purge -size 6000000

This purging will happen according to the retention period in the ADR directory. This directory tries to delete old files and keeps the new files for the last 8 days. This purging will take some time and check the space in the directory.

Command: du -hs

We can see the size of the path had reduced from 22M to 10M. We couldn’t reduce more than 10M because there might some files which are large and couldn’t delete the files due to the retention policy. This is the way we reduce the space in the ADR path manually, but most of the time we do not use to prefer deleting the files manually.

In the next article, I am going to discuss Enable DDL Logging in Oracle with Examples. Here, in this article, I try to explain Trace Files Purging in Oracle with Examples and I hope you enjoy this Trace Files Purging in Oracle with Example article.