Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  get_audit_details_script sqlplus "as sysdba" spool get_audit_details.log set timing on set echo on set serveroutput on set pagesize 10000 set linesize 4000 -- First get the current DB version details select banner from v$version; -- Get deployment type col DATABASE_ROLE for a25 select SYS_CONTEXT('USERENV', 'DATABASE_ROLE') as DATABASE_ROLE from SYS.DUAL; col CLOUD_SERVICE for a25 select SYS_CONTEXT('USERENV','CLOUD_SERVICE') as CLOUD_SERVICE from SYS.DUAL; col name format a9 col database_role format col db_unique_name format a30 col open_mode format a20 col database_role format a16 select dbid, name, created, open_mode, DATABASE_ROLE, DB_UNIQUE_NAME from v$database; col instance_name format a16 col host_name format a30 col dbversion format a17 col version_full format a17 col instance_role format a18 select instance_number, instance_name, host_name, version as dbversion, con_id, version_full, instance_role from v$instance; col name format a30...

  APPLIES TO: Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform. GOAL Provide a script which generates AUDIT commands for the current audit options as well as the NOAUDIT commands to negate them.   SOLUTION   Abstract This script generates AUDIT commands for the current audit options as well as the NOAUDIT commands to negate them. Description Since the NOAUDIT option only negates an AUDIT statement with the same syntax, it can be useful to know which AUDIT statements were issued in the past. We consider Statement and Privilege Auditing only, Object auditing is not administered in AUDIT$ but in the associated di...

   A distributed transaction modifies data related to two or more databases, it contains DML statements than span many nodes. For a distributed transaction to be succesful all or none of the database nodes involved in the transaction need to commit or rollback the whole transaction. Note the difference between a distributed and a remote transaction; a remote transaction contains one or more DML statements that are executed on the SAME remote node, Manual Resolution of In-Doubt Transactions –The in-doubt transaction has locks on critical data or undo segments. –The cause of the system, network, or software failure cannot be repaired quickly. Commit or Rollback Pending Transaction --  UNSTUCK TRANSACTION :  SQL> select local_tran_id,global_tran_id, state,mixed, commit# from dba_2pc_pending; If the state of the transaction is “prepared” and there is no inconsistency, the transaction can be forced to rollback, or maybe if the underlying problem which caused the i...

   In-Doubt Transactions   Distributed transactions perform DML on multiple databases which is a bit more complicated task because the database must coordinate the consitency in those seperate or even perhaps between different DBMSs (like Oracle - MS SQL). To ensure the transaction atomicity, Oracle implements a 2-phase commit mechanism through which the distributed transactions undergo some phases like prepare, commit, forget, etc. This phases constitute the hand-shake mechanism of the distributed transaciton.   However, sometimes things may go wrong (due to some network, system problem or even a reconfiguration of the underlying objects) and one of the phases fails while others are ok. Here, we say that the transaction becomes in-doubt. Normallly this problem should be handled by the RECO process itself, but in some cases this cannot br performed.     Why RECO cannot perform in some cases?   One of the databases involved in the distributed transactio...