ORACLE board

01 March 2011

Oracle Database System Triggers

System event list that could to be triggered:
AFTER STARTUP
BEFORE SHUTDOWN
AFTER LOGON
BEFORE LOGOFF
AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover
AFTER SUSPEND
AFTER SERVERERROR

AFTER SERVERERROR triggers fire after an Oracle error is raised, unless the error is one of
the following:
ORA-00600
Oracle internal error
ORA-01034
Oracle not available
ORA-01403
No data found
ORA-01422
Exact fetch returns more than requested number of rows
ORA-01423
Error encountered while checking for extra rows in an exact fetch
ORA-04030
Out-of-process memory when trying to allocate N bytes
In addition, the AFTER SERVERERROR trigger will not fire when an exception is raised
inside this trigger (to avoid an infinite recursive execution of the trigger).

Trace you session

Trace you session

1. Regular
Trace your own session
alter session set sql_trace=true;
or
alter session set events '10046 trace name context forever, level 4';
-- levels: 1(sql_trace=TRUE), 4, 8, 12

1.1. Regular 1
Trace your own session
exec dbms_session.set_sql_trace(TRUE);

2. Distance 1
dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);
example:
exec dbms_system.set_bool_param_in_session(10, 161, 'sql_trace', TRUE/FALSE);

3. Distance 2
dbms_system.set_sql_trace_in_session(
sid
NUMBER,
serial# NUMBER,
sql_trace BOOLEAN);
example
exec dbms_system.set_sql_trace_in_session(10, 1008, TRUE/FALSE);
or
EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

4. Distance 3 (new 11g)
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits
IN BOOLEAN
DEFAULT TRUE,
binds
IN BOOLEAN
DEFAULT FALSE,
plan_stat IN VARCHAR2
DEFAULT NULL);

Write your own message into alert log

Run under SYS
begin
dbms_system.ksdwrt(2, 'Message from DB');
end;

Original
dbms_system.ksdwrt(n, message_string);
where n is:
1 - Write to trace file.
2 - Write to alertlog.
3 - Write to both.

31 December 2010

Standby Switchover/Failover Mode

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements:

-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;


-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora

This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.

Database Failover

Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.

Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.