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

11/12/2018 7:17 PM

11/12/2018 7:17 PM

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

Database Performance Analyzer

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 */
   
   

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.