Create a System Trigger to Enable SQL TRACE for a session..

The following script will enable session trace for the user scott.

[oracle@canada u01]$ cat trace_trigger.sql
set echo on

spool trace_trigger.log

DROP TRIGGER SYS.trace_trigger;

CREATE OR REPLACE TRIGGER sys.trace_trigger
After logon on database
Begin
            if ( user='SCOTT') then
                execute immediate 'alter session set sql_trace=true';
                execute immediate 'alter session set timed_statistics=true';
                execute immediate 'alter session set tracefile_identifier="SCOTT"';
                execute immediate 'alter session set max_dump_file_size=unlimited';
                execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
            End if;
End;
/

spool off;


[oracle@canada u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 22 06:04:31 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @trace_trigger.sql
SQL>
SQL> spool trace_trigger.log
SQL>
SQL> DROP TRIGGER SYS.trace_trigger;
DROP TRIGGER SYS.trace_trigger
*
ERROR at line 1:
ORA-04080: trigger 'trace_trigger' does not exist


SQL>
SQL> CREATE OR REPLACE TRIGGER sys.trace_trigger
  2  After logon on database
  3  Begin
  4              if ( user='SCOTT') then
  5                  execute immediate 'alter session set sql_trace=true';
  6                  execute immediate 'alter session set timed_statistics=true';
  7                  execute immediate 'alter session set tracefile_identifier="SCOTT"';
  8                  execute immediate 'alter session set max_dump_file_size=unlimited';
  9                  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 10              End if;
 11  End;
 12  /

Trigger created.

SQL>
SQL> spool off;
SQL>


SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

To disable the trigger.

SQL> alter trigger trace_trigger disable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
DISABLED

To enable the trigger.

SQL> alter trigger trace_trigger enable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

Read more >>

RAC common wait events.

1) Global cache blocks lost- This statistic shows block losses during network
transfers. High values of this statistic indicate network problems.
The use of an unreliable IPC protocol, such as UDP, may result in the value
for lost global cache blocks being non-zero. 

High values for this statistics indicates that there is some problem with the IPC,
network or hardware.

2) Global cache blocks corrupt - This statistic shows if there is any block corruption
during interconnect transfers. High value for this statistic indicates
that there is a IPC,network or hardware problem.

Read more >>

Oracle GoldenGate Basic Architecture.

Source Database - GoldenGate extracts the information from the source database.
The source database may be from any of the following vendors.

- Oracle Database
- TimesTen
- MySQL
- IBM DB2
- Microsoft SQL Server
- Teradata
- Sybase
- Enscribe
- SQL/MX

Read more >>

Oracle RAC Instance Recovery.

1. All nodes available.

2. One or more RAC instances fail.

3. Node failure is detected by any one of the remaining instances.

4. Global Resource Directory(GRD) is reconfigured and distributed among 
   surviving nodes.

Read more >>

Split Brain Syndrome and I/O Fencing (RAC).

Split brain syndrome occurs when the instances in a RAC fails to connect or ping
to each other via the private interconnect, Although the servers are physically up
and running and the database instances on these servers is also running.

The individual nodes are running fine and can accept user connections and work
independently.

Read more >>

Oracle Clusterware Components.

Oracle clusterware is a software which facilitates multiple nodes to communicate
with each other and make them behave as a single Server.

Oracle clusterware is run by Cluster Ready Services(CRS) which in turn is dependent
on two components.

a) Oracle Cluster Registry(OCR)
b) Voting Disk

Read more >>

RAC Background Processes.

Lock Monitor(LMON) Process.

LMON is responsible for monitoring all instances in a cluster
for the detection of failed instances.

Once a failed Instance is detected it facilitates in the 
recovery of global locks held by that instance.

It is also responsible for reconfiguration of locks and other
resources when instances leave or are added to the cluster.
This dynamic reconfiguration is done in realtime.

Read more >>

Cache Fusion Scenarios (RAC).

Block Resource Modes.


Resource Mode  Identifier  Description
Null           N           Nodes which are holding the blocks
                           at this level do not have any access
                           rights to the block.

Shared         S           This level indicates that the block
                           at this level is in shared mode or
                           read mode, Multiple instances have
                           read access to this block but cannot
                           modify it.

Exclusive      X           In this level the block is held in Exclusive
                           mode and other instances cannot write to it.

Read more >>

Oracle Database Best Practices.

1) Maintain atleast three controlfiles.

2) Set CONTROL_FILE_RECORD_KEEP_TIME Large Enough.

(a) The CONTROL_FILE_RECORD_KEEP_TIME specifies the number of days
    that records are kept within the controlfile before being reused.

Read more >>

Excessive Redo Generation During Begin Backup Mode.

Excessive Redo Generation During Hot/Online backup.

During hot backup there is no excessive redo generation,
only some additional information is generated.

The process is something like following.

1) Before backup, tablespace or database is put
   into begin backup mode. 

Read more >>

Oracle Recovery Mindmap (No archivelog Mode).

Read more >>

Oracle Fuzzy Bits.

If there are FUZZY BITS in the datafile header then that means there
may have been writes on the datafile after the last checkpoint.

So, whenever a datafile is opened it is checked for fuzzy bits.
If it is fuzzy then the database checks the checkpoint_change$
column to search the SCN from which the redo logs are to be applied.

Read more >>

Types of Checkpoints in Oracle.

Checkpoint types can be divided as INCREMENTAL and COMPLETE.

Also COMPLETE CHECKPOINT can be divided further into
PARTIAL and FULL.

In Incremental Checkpoint,checkpoint information is written to the
datafile header and controlfile In the following cases:

Read more >>