SQLs for DBA

Tablespace Usage:

SELECT d.tablespace_name,
  ROUND (NVL (a.bytes / 1024 / 1024, 0), 2) size_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes, 0) / 1024 / 1024, NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024), 2) usage_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes / a.bytes * 100, 0), NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0)), 2) usage_perc,
  a.autoext,
  ROUND (DECODE (d.contents, 'UNDO', NVL (a.bytes - NVL (u.bytes, 0), 0) / 1024 / 1024, NVL (f.bytes, 0) / 1024 / 1024), 2) free_in_mb,
  d.status, a.num_of_file, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name,
      SUM (bytes) bytes, COUNT (file_id) num_of_file,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
    FROM dba_data_files
    GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name, SUM (bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f,
  (
    SELECT tablespace_name, SUM (bytes) bytes
    FROM ( 
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'ACTIVE'
      GROUP BY tablespace_name, status
     
      UNION ALL
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'UNEXPIRED'
      GROUP BY tablespace_name, status
    )
    GROUP BY tablespace_name
  ) u
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.tablespace_name = u.tablespace_name(+)
  AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')

UNION ALL
SELECT d.tablespace_name,
  NVL (a.bytes / 1024 / 1024, 0) size_in_MB,
  ROUND (NVL (t.bytes, 0) / 1024 / 1024, 2) usage_in_MB,
  ROUND (NVL (t.bytes / a.bytes * 100, 0), 2) usage_perc,
  a.autoext,
  ROUND ((NVL (a.bytes, 0) / 1024 / 1024 - NVL (t.bytes, 0) / 1024 / 1024), 2) free_in_MB,
  d.status, a.COUNT, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
    FROM dba_temp_files
    GROUP BY tablespace_name
  ) a,
  (
    SELECT ss.tablespace_name, SUM ( (ss.used_blocks * ts.blocksize)) bytes
    FROM gv$sort_segment ss, sys.ts$ ts
    WHERE ss.tablespace_name = ts.name
    GROUP BY ss.tablespace_name
  ) t
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY'
ORDER BY 1 desc

Configuration files and Log files for Oracle Business Intelligence

Log files (diagnostics)

<OBI_LOGS> = /u01/fmw/instances/instance1/diagnostics/logs

Log Name (.log)	Location
NQQuery	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQServer	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQSAdminTool	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
NQSUDMLExec	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
RPD migration utility	<OBI_LOGS>/OracleBIServerComponent/coreapplication_obis1
SAW log	<OBI_LOGS>/OracleBIPresentationServicesComponent/coreapplication_obips1
JavaHost	<OBI_LOGS>/OracleBIJavaHostComponent/coreapplication_obijh1
NQScheduler	<OBI_LOGS>/OracleBISchedulerComponent/coreapplication_obisch1
NQCluster	<OBI_LOGS>/OracleBIClusterControllerComponent/coreapplication_obiccs1
ODBC	<OBI_LOGS>/OracleBIODBCComponent/coreapplication_obips1
OPMN system	<OBI_LOGS>/OPMN/opmn/

Configuration files

BI Component	Configuration File	File Location
Oracle BI Server	NQSConfig.INI logconfig.xml	ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obisn For example:   \instances\instance1\config\OracleBIServerComponent\coreapplication_obis1 Note: Although DBFeatures.ini is also located in this directory, do not edit this file directly. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about how to edit features for a database.
Oracle BI Presentation Services	instanceconfig.xml	ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn For example:   \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 Note: Do not add elements to the instanceconfig.xml file unless you are overriding the stated default values. Override only those settings that are necessary for configuring the system to meet the needs of your organization.
Cluster Controller	ClusterConfig.xml ccslogging.xml	ORACLE_INSTANCE\config\OracleBIApplication\coreapplication For example:   \instances\instance1\config\OracleBIApplication\coreapplication
Oracle BI Scheduler	instanceconfig.xml ccslogging.xml (for Cluster Controller)	ORACLE_INSTANCE\config\OracleBISchedulerComponent\coreapplication_obischn For example:   \instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1
JavaHost	config.xml logging_config.xml	ORACLE_INSTANCE\config\OracleBIJavaHostComponent\coreapplication_obijhn For example:   \instances\instance1\config\OracleBIJavaHostComponent\coreapplication_obijh1
Oracle BI Presentation Services Plug-in	bridgeconfig.properties	MW_HOME\user_projects\domains\domain_name\config\fmwconfig\biinstances\coreapplication For example:   mw_home\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication

Linux swappiness parameter

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Because disks are much slower than RAM, this can lead to slower response times for system and applications if processes are too aggressively moved out of memory.

• swappiness can have a value of between 0 and 100
• swappiness=0 tells the kernel to avoid swapping processes out of physical memory for as long as possible
• swappiness=100 tells the kernel to aggressively swap processes out of physical memory and move them to swap cache

The default setting in RedHat is swappiness=60. Reducing the default value of swappiness will probably improve overall performance. A value of swappiness=10 is recommended, but feel free to experiment.
To check the current swappiness value:

cat /proc/sys/vm/swappiness

To change the swappiness value A temporary change (lost on reboot) with a swappiness value of 10 can be made with

sysctl vm.swappiness=10

To make a change permanent, edit the configuration file /etc/sysctl.conf with your favorite editor. Search for vm.swappiness and change its value as desired. If vm.swappiness does not exist, add it to the end of the file like so:

vm.swappiness=10

Save the file and run the this command to apply your change:

sysctl -p

you can also clear your swap by running swapoff -a and then vm.swapon -a as root instead of rebooting to achieve the same effect.

http://askubuntu.com/questions/103915/how-do-i-configure-swappiness/103916#103916

Audit Trail Purging (11g Release 2 Only)

As you know Oracle Audit write log to AUD$, FGA_LOG$ tables and OS files, XML files. Day by day these tables and directorys grow in large size. In pre 11g Release 2 you must manual delete or truncate tables and delete OS files, XML files periodically.
In 11g Release 2 you have 1 build-in package to do this easily.
First you must initialize purge process:

begin     sys.dbms_audit_mgmt.init_cleanup(         audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_all,         default_cleanup_interval    => 30*24     ); end;

You can see your purge process by below query:

select * from DBA_AUDIT_MGMT_CLEANUP_JOBS

Next, to perform the actual purge:

begin   dbms_audit_mgmt.clean_audit_trail(     audit_trail_type =>  dbms_audit_mgmt.audit_trail_all   ); end;

When the initial purge is complete, you may want to set up an automated process to execute this periodically. You will need to create a DBMS Scheduler Job as shown below:

begin   dbms_audit_mgmt.create_purge_job (     audit_trail_type    => dbms_audit_mgmt.audit_trail_all,     audit_trail_purge_interval  => 7*24,     audit_trail_purge_name => 'all_audit_trails_job'   ); end;

Since the above SQL performs a DELETE operation, it is not very conducive to a large delete operation. You may want to break it into chunks:

For Database Audit trails begin  dbms_audit_mgmt.set_audit_trail_property(   audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,   audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,   audit_trail_property_value => 100000); end; For FGA begin  dbms_audit_mgmt.set_audit_trail_property(   audit_trail_type           => dbms_audit_mgmt.audit_trail_fga_std,   audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,   audit_trail_property_value => 100000); end;

You can recheck your config:

select * from dba_audit_mgmt_config_params;

Then using a special package you will move the AUD$ table to the new tablespace:

begin   dbms_audit_mgmt.set_audit_trail_location(     audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,     audit_trail_location_value  => 'AUDIT_TS'   ); end;

To move the FGA_LOG$ table to this tablespace:

begin   dbms_audit_mgmt.set_audit_trail_location(     audit_trail_type  => dbms_audit_mgmt.audit_trail_fga_std,     audit_trail_location_value  => 'AUDIT_TS'   ); end;

List of values for audit_trail_type parameter:

Parameter	Purpose: to remove:
dbms_audit_mgmt.audit_trail_aud_std	The normal AUD$ audit train the database
dbms_audit_mgmt.audit_trail_fga_std	The FGA_LOG$ table, for Fine Grained Auditing
dbms_audit_mgmt.audit_trail_db_std	Both normal and FGA audit trails
dbms_audit_mgmt.audit_trail_os	The OS file audit trails
dbms_audit_mgmt.audit_trail_xml	The XML audit trail files
dbms_audit_mgmt.audit_trail_files	Both the OS and XML audit trails
dbms_audit_mgmt.audit_trail_all	All of the above