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.

How to find percentage of Fragmentation in Table

set lines 300 pages 300 ;

select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(GB)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(GB)",

to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",

trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(GB)" ,

(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"

from dba_tables a,

dba_segments b

where a.table_name = b.segment_name

and a.owner = b.owner

and a.table_name ='table_name'

order by 6

; 

Cleaning Oracle SYSAUX Tablespace Usage

If sysaux tablespace of oracle instance grew huge and filled up the complete tablespace. We were also not able to resize the tablespace to create some free space.
So here in this article we will discuss on how to create some free space in SYSAUX to continue normal database operations but before going into details we shall know few basics about SYSAUX to help understand the process better.

In Oracle SYSAUX tablespace is considered as an auxiliary tablespace to the SYSTEM tablespace. This is required by Oracle as a default tablespace for many database features and products. Previous to SYSAUX oracle use to require multiple tablespaces to support the same database features and products. Thus using the SYSAUX tablespace reduces load on SYSTEM tablespace.

Restrictions on SYSAUX tablespace

1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.

Now coming to our issue of oracle sysaux tablespace full, we need to do following to free up the space. To give a prospect of what was the size of SYSAUX before cleanup we have put a screenshot of SYSAUX before clean up below: ( 94% full)

1. Check sysaux table free size and what’s occupying SYSAUX tablespace:

Tablespace                    Used MB     Free MB    Total MB  Pct. Free

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

EXAMPLE                         1,219          42       1,261       3.33

SYSTEM                            2,438         634       3,072      20.64

SYSAUX                            765             85         850      10.28

USERS                                   2                3             5       60

METALS                             41             359         400      89.75

UNDOTBS1                       36              609         645      94.42

6 rows selected.

Run below query to know what all occupants are there in sysaux occupying all the space.

SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;

OCCUPANT_NAME             OCCUPANT_DESC                                            SPACE_USAGE_KBYTES

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

LOGMNR                    LogMiner                                                             14208

LOGSTDBY                  Logical Standby                                                       1536

SMON_SCN_TIME             Transaction Layer - SCN to TIME mapping                               3328

AUDSYS                    AUDSYS schema objects                                                 1536

PL/SCOPE                  PL/SQL Identifier Collection                                          1600

STREAMS                   Oracle Streams                                                        1024

AUDIT_TABLES              DB audit tables                                                          0

XDB                       XDB                                                                  70400

AO                        Analytical Workspace Object Table                                    41088

XSOQHIST                  OLAP API History Tables                                              41088

XSAMD                     OLAP Catalog                                                             0

SM/AWR                Server Manageability - Automatic Workload Repository                     25856

SM/ADVISOR            Server Manageability - Advisor Framework                                 10880

SM/OPTSTAT            Server Manageability - Optimizer Statistics History                      42688

SM/OTHER              Server Manageability - Other Components                                  53184

STATSPACK                 Statspack Repository                                                     0

SDO                       Oracle Spatial                                                       80640

WM                        orkspace Manager                                                     7360

ORDIM                     Oracle Multimedia ORDSYS Components                                    448

ORDIM/ORDDATA             Oracle Multimedia ORDDATA Components                                 16512

ORDIM/ORDPLUGINS          Oracle Multimedia ORDPLUGINS Components                                  0

ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components                           0

EM                        Enterprise Manager Repository                                            0

TEXT                      Oracle Text                                                           4032

ULTRASEARCH               Oracle Ultra Search                                                      0

ULTRASEARCH_DEMO_USER     Oracle Ultra Search Demo User                                            0

EXPRESSION_FILTER         Expression Filter System                                                 0

EM_MONITORING_USER        Enterprise Manager Monitoring User                                     512

TSM                       Oracle Transparent Session Migration User                                0

SQL_MANAGEMENT_BASE       SQL Management Base Schema                                            2496

AUTO_TASK                 Automated Maintenance Tasks                                            320

JOB_SCHEDULER             Unified Job Scheduler                                                 1536

32 rows selected.

We can clearly see AWR consumes the good amount of space. Lets find the AWR consumption in MB.

SQL>

col OCCUPANT_NAME form a25

col OCCUPANT_DESC form a55

select occupant_name,occupant_desc,space_usage_kbytes/1024 Space_usage_in_MB from v$sysaux_occupants where occupant_name like '%AWR%';

OCCUPANT_NAME             OCCUPANT_DESC                                           SPACE_USAGE_IN_MB

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

SM/AWR                    Server Manageability - Automatic Workload Repository              47.375

2. Check AWR Retention Period:

First is to check what is the AWR retention period in DB. For us it was set for 90 days which we did not require. You can query using below SQL:

SQL> select retention from dba_hist_wr_control;

We reduced it to 7 day which is 7*24*60 = 10080 minutes.  In this example the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes.

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period you encounter below error then check the MOVING_WINDOW_SIZE value and update it to correct value and then execute the above AWR retention query again.

execute dbms_workload_repository.modify_snapshot_settings(retention => 5760);

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SELECT moving_window_size

FROM dba_hist_baseline

WHERE baseline_type = 'MOVING_WINDOW';

3. Cleanup old AWR reports to free up space:

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SELECT

snap_id, begin_interval_time, end_interval_time

FROM

SYS.WRM$_SNAPSHOT

WHERE

snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)

UNION

SELECT

snap_id, begin_interval_time, end_interval_time

FROM

SYS.WRM$_SNAPSHOT

WHERE

snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)

/

SNAP_ID    BEGIN_INTERVAL_TIME                           END_INTERVAL_TIME

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

       161 11-SEP-18 02.30.18.274 PM                   11-SEP-18 03.30.23.151 PM

       162 11-SEP-18 03.30.23.151 PM                   11-SEP-18 04.30.27.498 PM

Execute below command to cleanup all AWR reports between snap_id 161 to 162.

BEGIN

dbms_workload_repository.drop_snapshot_range(low_snap_id => 161, high_snap_id=>162);

END;

/

If above removal process taking too much of time then you can run below two sqls as sysdba to drop the old AWR’s and rebuild the repositories. This process is very fast.

SQL> connect / as sysdba

SQL> @?/rdbms/admin/catnoawr.sql

SQL> @?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we were able to cleanup approx of 5GB space from SYSAUX tablespace.
SYSAUX Tablespace Post AWR Cleanup:

SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';

OCCUPANT_NAME   OCCUPANT_DESC                                           SPACE_USAGE_KBYTES

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

SM/AWR          Server Manageability - Automatic Workload Repository                 35072

Tablespace                    Used MB     Free MB    Total MB  Pct. Free

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

EXAMPLE                         1,219          42       1,261       3.33

SYSTEM                            2,438         634       3,072      20.64

SYSAUX                            639            211         850      24.82

USERS                                   2                3             5       60

METALS                             41             359         400      89.75

UNDOTBS1                       36              609         645      94.42

6 rows selected.

-------------------------------------------------END-------------------------------------------------------------