While working on performance monitoring , one of my forum member Cadavre, introduced me about DMV's. These are really handy. You can extract system stats directly from dmv. Remember that dmv's scope is limited to instance. If you will restart instance , system will wipe older and collects stats after restart.
Microsoft introduced dynamic
management views (DMV) and functions (DMF) with SQL Server 2005. So what are
DMV's and DMF's? How do you use these dynamic management objects? DMVs and DMFs
are a mechanism to allow you to look at the internal workings of SQL Server
using TSQL. They allow you an easy method for monitoring what SQL Server is
doing and how it is performing. They replace the need to query the system
tables or using other awkward methods of retrieving system information that you
had to use with SQL Server 2000.
Some examples
=========================================
--–Queries taking longest elapsed time:
==========================================
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds ,
qs.total_elapsed_time / 1000000.0 AS total_seconds ,
qs.execution_count ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS individual_query ,
o.name AS object_name ,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC ;
===============================
---–Queries doing most I/O:
===============================
SELECT TOP 10
( total_logical_reads + total_logical_writes ) / qs.execution_count AS average_IO ,
( total_logical_reads + total_logical_writes ) AS total_IO ,
qs.execution_count AS execution_count ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS indivudual_query ,
o.name AS object_name ,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC ;
====================================
SELECT TOP 20
SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) ,
qs.execution_count ,
qs.total_logical_reads ,
qs.last_logical_reads ,
qs.total_logical_writes ,
qs.last_logical_writes ,
qs.total_worker_time ,
qs.last_worker_time ,
qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
qs.last_execution_time ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
--ORDER BY qs.total_logical_writes DESC -- logical writes
--ORDER BY qs.total_worker_time DESC -- CPU time
--ORDER BY total_elapsed_time_in_S DESC -- time
Friday, February 22, 2013
Thursday, February 21, 2013
Read MSSQL 2005 trace Table
while working on a major project, I was assigned to track database activity. Objective was to find long running queries. It seen easy but i was not so proficient in MSSQL profiler :(
I have created a template and applied dbname and userName filter as required.
Performance
After running trace,Now I have data in my table ,next step is to query that data.I found it is difficult for new user to extract data.So I thought why not to share it with others.
--Definitation of column used in query
--CPU: Amount of CPU time (in milliseconds) used by the event.But when store to table/file it will be
--10^3
--Duration:Amount of time (in milliseconds) taken by the event.But when store to table/file it will be
--micro second 10^6
--Writes:Number of physical disk writes performed by the server on behalf of the event.
--Reads:Number of logical disk reads performed by the server on behalf of the event.
--Event Class:Type of event class captured.
--TextData:Text value dependent on the event class captured in the trace. However, if you are tracing a parametrized query, the variables will not be displayed with data values in the TextData column.
--Query to read trace table
SELECT
COUNT(*) AS TotalExecutions ,
EventClass ,
CAST(TextData AS NVARCHAR(4000)) AS query ,
SUM(Duration) / 1000000 AS DurationTotal_Sec ,
--SUM(Duration) / 1000000/COUNT(*) AS DurationTotal_per_call_Sec ,
SUM(CPU)/ 1000 AS CPUTotal_Sec ,
SUM(Reads) AS ReadsTotal ,
SUM(Writes) AS WritesTotal
FROM tmptracetable -- table that contains the trace results
--WHERE eventclass IN ( 12 ) --batch completed
--WHERE eventclass IN ( 41 ) --stmt completed
GROUP BY EventClass ,
CAST(TextData AS NVARCHAR(4000))
--un comment required order clause as per your needs
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
--ORDER BY DurationTotal_per_call_Sec DESC
In trace table you will have event class id, if you want to know event name to filter your trace search, use the
following query
-- Query to view event class name
SELECT DISTINCT
TE.name ,
t.eventclass
FROM tmptracetable T -- table that contains the trace results
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
--WHERE t.eventclass IN (12,41)
ORDER BY t.eventclass
/*
name eventclass
SQL:BatchCompleted 12
SQL:BatchStarting 13
SQL:StmtStarting 40
SQL:StmtCompleted 41
Showplan All 97
Showplan XML 122
Performance statistics 165
*/
and rest is ongoing :)
I have created a template and applied dbname and userName filter as required.
Performance
- Performance Statistics
- ShowPlanAll
- ShowPlanXML
- SQL batch completed
- Sql batchStarting
- Sql StmtCompleted
- Sql StmtStarting
After running trace,Now I have data in my table ,next step is to query that data.I found it is difficult for new user to extract data.So I thought why not to share it with others.
--Definitation of column used in query
--CPU: Amount of CPU time (in milliseconds) used by the event.But when store to table/file it will be
--10^3
--Duration:Amount of time (in milliseconds) taken by the event.But when store to table/file it will be
--micro second 10^6
--Writes:Number of physical disk writes performed by the server on behalf of the event.
--Reads:Number of logical disk reads performed by the server on behalf of the event.
--Event Class:Type of event class captured.
--TextData:Text value dependent on the event class captured in the trace. However, if you are tracing a parametrized query, the variables will not be displayed with data values in the TextData column.
--Query to read trace table
SELECT
COUNT(*) AS TotalExecutions ,
EventClass ,
CAST(TextData AS NVARCHAR(4000)) AS query ,
SUM(Duration) / 1000000 AS DurationTotal_Sec ,
--SUM(Duration) / 1000000/COUNT(*) AS DurationTotal_per_call_Sec ,
SUM(CPU)/ 1000 AS CPUTotal_Sec ,
SUM(Reads) AS ReadsTotal ,
SUM(Writes) AS WritesTotal
FROM tmptracetable -- table that contains the trace results
--WHERE eventclass IN ( 12 ) --batch completed
--WHERE eventclass IN ( 41 ) --stmt completed
GROUP BY EventClass ,
CAST(TextData AS NVARCHAR(4000))
--un comment required order clause as per your needs
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
--ORDER BY DurationTotal_per_call_Sec DESC
In trace table you will have event class id, if you want to know event name to filter your trace search, use the
following query
-- Query to view event class name
SELECT DISTINCT
TE.name ,
t.eventclass
FROM tmptracetable T -- table that contains the trace results
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
--WHERE t.eventclass IN (12,41)
ORDER BY t.eventclass
/*
name eventclass
SQL:BatchCompleted 12
SQL:BatchStarting 13
SQL:StmtStarting 40
SQL:StmtCompleted 41
Showplan All 97
Showplan XML 122
Performance statistics 165
*/
and rest is ongoing :)
Tuesday, January 29, 2013
MSSQL onDelete trigger RollBack transaction and Log deleted data
I have a scenario to rollBack any delete on table and also log whatever was delete candidate
-- Original table
CREATE TABLE dbo.tblDomain(
DoaminId INT NULL,
Description varchar(50) NULL
);
-- Table Population
insert into dbo.tblDomain
select 1,'Desc1'
union All
select 2,'Desc2'
union All
select 3,'Desc3'
union All
select 4,'Desc4';
/*---============= Creating table to store deleted records.
table structure is same as dbo.tblDomain except last 2 columns
FromDeleteUser varchar (100) –- who initiated delete
DeleteDate datetime -- datatime of delete transaction
*/
CREATE TABLE dbo.tblDomain_tmpTrg(
DoaminId INT NULL,
Description varchar(500) NULL,
FromDeleteUser varchar (100),
DeleteDate datetime.
FromDeleteUser varchar (100),
DeleteDate datetime
);
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trg_tblDomain_DeleteLog
ON dbo.tblDomain
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
-- use table variable to store data to be logged before rolling back transaction
declare @trg table ( DoaminId INT NULL,
Description varchar(500) NULL,
FromDeleteUser varchar (100),
DeleteDate datetime
)
insert @trg select * from deleted
ROLLBACK
-- transacion rolled-back, but data is still available in @trg
-- log it into proper table
insert dbo.tblDomain_tmpTrg
select * , suser_sname(), getutcdate()
from @trg
-- re-raise the error to make it clear!
raiserror ('Delete from table dbo.dbo.tblDomain is not allowed, your action is logged!',16,1)
END
GO
--===================== Verification
--SELECT * FROM dbo.tblDomain;
--DELETE FROM dbo.tblDomain ;
--SELECT * FROM dbo.tblDomain_tmpTrg;
--=== Drop objects
--DROP TRIGGER trg_tblDomain_DeleteLog;
--DROP dbo.tblDomain_tmpTrg;
-- Original table
CREATE TABLE dbo.tblDomain(
DoaminId INT NULL,
Description varchar(50) NULL
);
-- Table Population
insert into dbo.tblDomain
select 1,'Desc1'
union All
select 2,'Desc2'
union All
select 3,'Desc3'
union All
select 4,'Desc4';
/*---============= Creating table to store deleted records.
table structure is same as dbo.tblDomain except last 2 columns
FromDeleteUser varchar (100) –- who initiated delete
DeleteDate datetime -- datatime of delete transaction
*/
CREATE TABLE dbo.tblDomain_tmpTrg(
DoaminId INT NULL,
Description varchar(500) NULL,
FromDeleteUser varchar (100),
DeleteDate datetime.
FromDeleteUser varchar (100),
DeleteDate datetime
);
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trg_tblDomain_DeleteLog
ON dbo.tblDomain
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
-- use table variable to store data to be logged before rolling back transaction
declare @trg table ( DoaminId INT NULL,
Description varchar(500) NULL,
FromDeleteUser varchar (100),
DeleteDate datetime
)
insert @trg select * from deleted
ROLLBACK
-- transacion rolled-back, but data is still available in @trg
-- log it into proper table
insert dbo.tblDomain_tmpTrg
select * , suser_sname(), getutcdate()
from @trg
-- re-raise the error to make it clear!
raiserror ('Delete from table dbo.dbo.tblDomain is not allowed, your action is logged!',16,1)
END
GO
--===================== Verification
--SELECT * FROM dbo.tblDomain;
--DELETE FROM dbo.tblDomain ;
--SELECT * FROM dbo.tblDomain_tmpTrg;
--=== Drop objects
--DROP TRIGGER trg_tblDomain_DeleteLog;
--DROP dbo.tblDomain_tmpTrg;
Thursday, January 24, 2013
SQL Server Trigger - Insert the deleted record into another table with deletetime
SQL Server Trigger - Insert the deleted record into another table with deletetime
/*
Author: Tanveer Haider Baig
Description : Trigger to fire after delete on table and populate deleted records to another table with timestamp
*/
--Create Primary table
CREATE TABLE tblTrgTest1
(
id INT,
NAME VARCHAR(10)
);
--=== Populating Table
INSERT INTO tblTrgTest1
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'d'
UNION ALL
SELECT 5,'e';
--=== Retrivening Results
SELECT * FROM tblTrgTest1;
--===== Create secondary table to host deleted values
CREATE TABLE tblTrgTest2
(
id INT,
NAME VARCHAR(10),
delDate DATETIME
);
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trgTableDeleteLog
ON tblTrgTest1
AFTER DELETE
AS
INSERT INTO tblTrgTest2
SELECT * ,GETDATE()
FROM DELETED;
---===== delete records to test trigger
DELETE FROM tblTrgTest1 WHERE id IN (1,2);
DELETE FROM tblTrgTest1 WHERE id IN (3,5);
--Verify deleted records come to secondry table with timestamp
SELECT * FROM tblTrgTest2;
--Verify delete was successfull on primary
SELECT * FROM tblTrgTest1;
--=== Drop objects
DROP TRIGGER trgTableDeleteLog;
DROP TABLE tblTrgTest1;
DROP TABLE tblTrgTest2;
/*
Author: Tanveer Haider Baig
Description : Trigger to fire after delete on table and populate deleted records to another table with timestamp
*/
--Create Primary table
CREATE TABLE tblTrgTest1
(
id INT,
NAME VARCHAR(10)
);
--=== Populating Table
INSERT INTO tblTrgTest1
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'d'
UNION ALL
SELECT 5,'e';
--=== Retrivening Results
SELECT * FROM tblTrgTest1;
--===== Create secondary table to host deleted values
CREATE TABLE tblTrgTest2
(
id INT,
NAME VARCHAR(10),
delDate DATETIME
);
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trgTableDeleteLog
ON tblTrgTest1
AFTER DELETE
AS
INSERT INTO tblTrgTest2
SELECT * ,GETDATE()
FROM DELETED;
---===== delete records to test trigger
DELETE FROM tblTrgTest1 WHERE id IN (1,2);
DELETE FROM tblTrgTest1 WHERE id IN (3,5);
--Verify deleted records come to secondry table with timestamp
SELECT * FROM tblTrgTest2;
--Verify delete was successfull on primary
SELECT * FROM tblTrgTest1;
--=== Drop objects
DROP TRIGGER trgTableDeleteLog;
DROP TABLE tblTrgTest1;
DROP TABLE tblTrgTest2;
Friday, January 18, 2013
Oracle 10g User Auditing
I start receiving oracle alerts for excessive sessions. So I decided to audit user to find source IP that request for session.
view audit status
SQL> SHOW PARAMETER AUDIT
audit_trail options:
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
The following list provides a description of each setting:
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN
SQL> STARTUP
--Auditing only single user connection
CONNECT sys/password AS SYSDBA
SQL> audit connect by MyUser;
--View audit info
SQL> select userhost, terminal, timestamp, action_name,comment_text from dba_audit_session where username=’MYUser’;
comment_text holds IP address.
You can format your results with the following query.
select count(*),action_name,audi.username,substr(audi.s1,1,instr(audi.s1,')')-1) IP from
(
select username,action_name,substr(comment_text,instr(comment_text,'HOST=')+5,100) s1
from dba_audit_trail
where username='MYUSER'
--and action_name ='LOGON'
--and action_name in ('LOGON','LOGOFF')
)audi
group by audi.username,substr(audi.s1,1,instr(audi.s1,')')-1),action_name
order by 1 desc ;
-- Fetch Audit related views in database
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
SQL>
The three main views are shown below.
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
view audit status
SQL> SHOW PARAMETER AUDIT
audit_trail options:
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
The following list provides a description of each setting:
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN
SQL> STARTUP
--Auditing only single user connection
CONNECT sys/password AS SYSDBA
SQL> audit connect by MyUser;
--View audit info
SQL> select userhost, terminal, timestamp, action_name,comment_text from dba_audit_session where username=’MYUser’;
comment_text holds IP address.
You can format your results with the following query.
select count(*),action_name,audi.username,substr(audi.s1,1,instr(audi.s1,')')-1) IP from
(
select username,action_name,substr(comment_text,instr(comment_text,'HOST=')+5,100) s1
from dba_audit_trail
where username='MYUSER'
--and action_name ='LOGON'
--and action_name in ('LOGON','LOGOFF')
)audi
group by audi.username,substr(audi.s1,1,instr(audi.s1,')')-1),action_name
order by 1 desc ;
-- Fetch Audit related views in database
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
SQL>
The three main views are shown below.
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
Oracle 10g user login Audit
you can audit user login or logoff request with triggers as well.
following needed
- Table that will hold trigger output
- Login trigger (in sys schema)
- Logoff trigger (in sys schema)
--------------------------------------------------------
-- DDL for Trigger output table
--------------------------------------------------------
CREATE TABLE "Schema"."TBLUSERCONNECTIONAUDIT"
( "DATE_CON" DATE,
"OSUSER" VARCHAR2(100 BYTE),
"PROGRAM" VARCHAR2(100 BYTE),
"MACHINE" VARCHAR2(100 BYTE),
"IP" VARCHAR2(100 BYTE),
"ACTION" VARCHAR2(30 BYTE)
)
( "DATE_CON" DATE,
"OSUSER" VARCHAR2(100 BYTE),
"PROGRAM" VARCHAR2(100 BYTE),
"MACHINE" VARCHAR2(100 BYTE),
"IP" VARCHAR2(100 BYTE),
"ACTION" VARCHAR2(30 BYTE)
)
--------------------------------------------------------
-- DDL for Trigger TRG_LOGON_AUDIT
--------------------------------------------------------
CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGON_AUDIT"
AFTER LOGON ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;
IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGON'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error
RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGON_AUDIT" ENABLE;
--------------------------------------------------------
-- DDL for Trigger TRG_LOGOFF_AUDIT
--------------------------------------------------------
CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGOFF_AUDIT"
Before LOGOFF ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;
IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGOFF'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error
RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGOFF_AUDIT" ENABLE;
Auto Start Oracle on Linux
Create the file /etc/init.d/dbora like this :
#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 <--- to be changed
ORA_OWNER=oracle
case "$1" in
'start')
# Start the Oracle databases:
echo "Starting the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
# Start the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# STOP the Oracle databases
echo "Shutting down the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
*)
echo "Usage: dbora (start|stop)"
exit 1
esac
Then, issue the commands :
chmod 755 /etc/init.d/dbora
/sbin/chkconfig dbora on
Check also in the file /etc/oratab that the database(s) are configured to start automatically (you need a Y at the end of the line for each database :
ex :
APPSPROD:/u01/app/oracle/product/11.2.0/dbhome_1:Y
That's all !
Courtesy :Michel Berger
#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 <--- to be changed
ORA_OWNER=oracle
case "$1" in
'start')
# Start the Oracle databases:
echo "Starting the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
# Start the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# STOP the Oracle databases
echo "Shutting down the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
*)
echo "Usage: dbora (start|stop)"
exit 1
esac
Then, issue the commands :
chmod 755 /etc/init.d/dbora
/sbin/chkconfig dbora on
Check also in the file /etc/oratab that the database(s) are configured to start automatically (you need a Y at the end of the line for each database :
ex :
APPSPROD:/u01/app/oracle/product/11.2.0/dbhome_1:Y
That's all !
Courtesy :Michel Berger
Subscribe to:
Posts (Atom)