PURGING TRACE AND DUMP FILES WITH 11G ADRCI

In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

1. SHORTP_POLICY:  Which is used for automatically purging short-lived files, i.e. core dump files and traces, expressed in hours and defaults to 30 days.

2.    LONGP_POLICY: Which is used for automatically purging long-lived files, i.e. incidents and health monitor warnings, expressed in hours and defaults to 1 year (365 days).

The ADRCI command show control will show us what the current purge settings are as shown below.

C:\Users\ashwanik\Desktop\MY_SCRIPT>show control

'show' is not recognized as an internal or external command,

operable program or batch file.

C:\Users\ashwanik\Desktop\MY_SCRIPT>adrci

ADRCI: Release 11.2.0.4.0 - Production on Tue Dec 11 15:28:30 2018

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

ADR base = "c:\app\ashwanik"

adrci> show control

DIA-48448: This command does not support multiple ADR homes

adrci>  show homes

ADR Homes:

diag\clients\user_ashwani\host_2731541237_76

diag\clients\user_ashwani\host_2731541237_80

diag\clients\user_ashwani\host_2731541237_82

diag\clients\user_SYSTEM\host_2731541237_76

diag\clients\user_SYSTEM\host_2731541237_80

diag\clients\user_SYSTEM\host_2731541237_82

diag\rdbms\db11g\db11g

diag\rdbms\db11gdev\db11gdev

diag\rdbms\db11gdev\orcl

diag\rdbms\metalsdb\metalsdb

diag\rdbms\orcl\orcl

diag\rdbms\testdb\testdb

diag\rdbms\testdev\testdev

diag\tnslsnr\2242ASHWAN1525D\listener

diag\tnslsnr\2242ASHWAN1525D\listener1

diag\tnslsnr\2242ASHWAN1525D\listener12c

diag\tnslsnr\2242ASHWAN1525D\listener2

diag\tnslsnr\2242ASHWAN1525D\listener_12c

diag\tnslsnr\2242ASHWAN1525D\listener_db11gdr

diag\tnslsnr\2242ASHWAN1525D\listner1

diag\tnslsnr\2242ASHWAN1525D\services

diag\tnslsnr\2242ASHWAN1525D\status

I have multiple different path for different instance. Let’s choose the path for which instance (db11gdev) we need to remove the trace files as below:

adrci> set homepath diag\rdbms\db11gdev\db11gdev

adrci> show control

ADR Home = c:\app\ashwani\diag\rdbms\db11gdev\db11gdev:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

4201279328           720                  8760                 2017-10-23 16:17:50.212000 +05:30        2018-12-09 04:46:22.558000 +05:30        2018-12-11 15:20:21.338000 +05:30        1                    2                    80                   1                    2017-10-23 16:17:50.212000 +05:30

1 rows fetched

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category.

adrci> show control

ADR Home = c:\app\ashwanik\diag\rdbms\db11gdev\db11gdev:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

4201279328           720                  8760                 2017-10-23 16:17:50.212000 +05:30        2018-12-09 04:46:22.558000 +05:30        2018-12-11 15:20:21.338000 +05:30        1                    2                    80                   1                    2017-10-23 16:17:50.212000 +05:30

1 rows fetched

We can change this by using the ADRCI command ‘set control’

adrci> set control (SHORTP_POLICY =360)

adrci> show control

ADR Home = c:\app\ashwanik\diag\rdbms\db11gdev\db11gdev:

*************************************************************************

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

4201279328           360                  8760                 2018-12-11 15:42:08.147000 +05:30        2018-12-09 04:46:22.558000 +05:30        2018-12-11 15:40:56.995000 +05:30        1                    2                    80                   1                    2017-10-23 16:17:50.212000 +05:30

1 rows fetched

In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)

In this example we are purging all trace files older than 6 days. We see that the LAST_MANUPRG_TIME column is now populated.

adrci> purge -age 8640 -type TRACE 

HOW TO CONNECT TO OTHER USER AND CREATE OBJECTS WITH OUT KMOWING THE PASSWORD OF OTHERS USER

Suppose a user ASHWANI wants to connect to SCOTT user and create a table and we don’t know the password of SCOTT.

Connect to SYS user

SQL> conn / as sysdba

Connected.

Grant below privilege

SQL> alter user Scott grant connect through ashwani;

User altered.

Connect as below and provide the ashwani user password.

SQL> conn ashwani[scott]

Enter password:         => Provide the password of ashwani user

Connected.

SQL> sho user

USER is "SCOTT"

Create table

SQL> create table emp_bkp as select * from emp;

Table created.

CONNECT TO SYS USER AND CHECK THE DETAILS OF THE OBJECT CREATED.

SQL> SELECT OBJECT_NAME,OBJECT_TYPE,OWNER,CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME='EMP_BKP';

OBJECT_NAME          OBJECT_TYPE         OWNER           CREATED

-------------------- ------------------- --------------- ---------

EMP_BKP              TABLE               SCOTT           10-DEC-18

HOW TO STOP GENERATION OF ARCHIVES DURING HEAVY INSERT

LOGGING is a keyword that used on creating the index, table or tablespace. If we use LOGGING when creating the object then DML operations on the objects are logged in redo log file. If we use NOLOGGING when creating the object, in some cases DML operations on the objects are not logged in redo log file.

The following scenario is run against a database running in ARCHIVELOG mode

and table is created with nologging option.

SQL> conn / as sysdba

16:11:52 SQL> create table finance.invoice nologging as select * from finance.material_records;

Table created.

16:16:10 SQL> select table_name,LOGGING FROM DBA_tables WHERE table_name='INVOICE' AND OWNER='FINANCE';

TABLE_NAME                     LOG

------------------------------ ---

INVOICE                        NO

We can see table has been created with NOLOGGING.

Now insert rows to INVOICE table.

SET AUTOTRACE ON STATISTICS

SQL>  select count(*) from  finance.invoice;

  COUNT(*)

----------

     90860

15:24:14 SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     474

Next log sequence to archive   476

Current log sequence           476

15:24:23 SQL>

Check the current sequence before inserting the bulk load. We have current sequence of 476.

Let’s insert bulk data to check and see how much archives are generated:

15:24:23 SQL> insert into finance.invoice select * from finance. material_records;

90860 rows created.

Statistics

---------------------------------------------------

         75  recursive calls

     304088  db block gets

      93827  consistent gets

      34311  physical reads

  231903788  redo size

        843  bytes sent via SQL*Net to client

        819  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

      90860  rows processed

Now we can check the current sequence again.

15:32:29 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     490

Next log sequence to archive   492

Current log sequence           492

We can see number of archive generated during the bulk inserts (492-476=16). It means archive logs generated with normal INSERT statement even we have NOLOGGING table. Now we will use HINT to insert the data

and see if archives are generating or not.

Again check the current sequence after manually switching the archives(3 times if you are using 3 redolog file). And then INSERT the data using “APPEND” Hint.

15:39:44 SQL> alter system switch logfile;

System altered.

15:39:59 SQL> /

System altered.

15:40:09 SQL> /

System altered.

15:36:15 SQL>  archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     495

Next log sequence to archive   497

Current log sequence           497

15:36:19 SQL>

16:42:33 SQL> INSERT /*+ APPEND */ INTO finance.invoice select * from finance.material_records;

90860 rows created.

Statistics

----------------------------------------------------------

         83  recursive calls

      41547  db block gets

      35927  consistent gets

      34311  physical reads

    9962248  redo size

        827  bytes sent via SQL*Net to client

        832  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      90860  rows processed

15:43:53 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     495

Next log sequence to archive   497

Current log sequence           497=è No CHANGE

15:46:41 SQL>

Commit;

We can see no archive generated during INSERT operation by using APPEND hint.

NOTE: In ARCHIVELOG mode, the APPEND hint will not reduce redo generation only when the table is set to NOLOGGING. Our table is set to nologging this is the reason archives are not generated during insert.

With the database running on NOARCHIVELOG mode, the addition of the APPEND hint will reduce the amount of redo generated even if the table is created with normal logging option. Same APPEND hint will generate redo if the database is in ARCHIVELOG mode if the table is created with normal logging option.