Submit a ticketCall us

WebinarUpcoming Webinar: Should I Move My Database to the Cloud?

So you’ve been running an on-premises SQL Server® for a while now. Maybe you’ve moved it from bare metal to a VM, and have seen some positive benefits. But, do you want to see more? If you said “YES!”, then this session is for you, as James Serra will review the many benefits that can be gained by moving your on-prem SQL Server to an Azure® VM (IaaS). He’ll also talk about the many hybrid approaches, so you can gradually move to the cloud. If you are interested in cost savings, additional features, ease of use, quick scaling, improved reliability, and ending the days of upgrading hardware, this is the session for you.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > In an Oracle repository, disable logging for temporary data created by DPA

In an Oracle repository, disable logging for temporary data created by DPA

Updated July 18, 2018

Overview

DPA creates the following tables in the DPA repository database for each monitored database instance:

 

  • CONTSS1_<database_ID>
  • CONTSS2_<database_ID>

 

These tables store temporary data generated by DPA, which is not necessary for database recovery. When DPA uses an Oracle repository database, you can safely disable logging for the CONTSS1 and CONTSS2 tables. Disabling logging for temporary data can dramatically reduce both log file growth and overhead on the DPA repository.

Environment

  • DPA, all versions
  • Oracle repository database

Steps

Task 1: Verify that force logging and supplemental logging are not enabled at the database level

If either force logging or supplemental logging is enabled at the database level, CONTSS table data is logged even if logging is turned off for the individual tables.

 

Issue the following query to determine whether force logging or supplemental logging is enabled:

 

select name, FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
from v$database;

 

The query results for each setting should be NO. If not, refer to your Oracle documentation for information about disabling force logging or supplemental logging. 

NAME FORCE_LOGGING SUPPLEME SUP SUP SUP SUP
---- ------------- -------- --- --- --- ---
DPAT NO            NO       NO  NO  NO  NO

Task 2: Edit the DPA advanced option CONTSS_INSERT_HINT

In DPA:

  1. Go to Options > Administration > Advanced Options > System Options tab.
  2. Select Support Options.
  3. Click the CONTSS_INSERT_HINT option and enter the following value. Then click Update.

    /*+ APPEND_VALUES */

    append_values.png

Task 3: Issue the nologging command for each table

You can issue the nologging command using either of the following methods:

  • Enter a command to turn off logging for each database instance individually.
  • Enter commands to create and run a script that turns off logging for all database instances.

 

If you subsequently register new database instances to monitor, issue the "alter table" commands for the new monitored database tables, or create and run the script again.

To turn off logging for each database instance

For an Oracle repository database, complete the following steps to retrieve the monitored database instance IDs and turn off logging. 

  1. Issue the following SQL query against the DPA repository to get each monitored database ID:

    select ID from COND;
     
  2. Stop DPA.
  3. Issue the following commands against the DPA repository to turn off logging for the CONTSS1 and CONTSS2 tables for each monitored database instance:

    alter table contss1_<monitored_db_id> nologging;
    alter table contss2_
    <monitored_db_id> nologging;

    where <monitored_db_id> is the database ID retrieved by the query in step 1. 
  4. Restart DPA.

To create and run a script that turns off logging for all database instances

  1. Start SQL Command Line and log in to the DPA repository database as the repository owner. 
  2. Enter the following commands at the SQL prompt:

    SQL> spool /tmp/alt_contss.sql
    SQL> select 'alter table '||table_name||' nologging;' from user_tables where table_name like 'CONTSS1%' or table_name like 'CONTSS2%';

     
  3. After the script above creates the alter table commands, stop DPA and then run the spooled file:

    SQL> spool off
    SQL> @/tmp/alt_contss.sql
     
  4. Restart DPA.

 

 

Last modified

Tags

Classifications

Public