Do session, memory monitoring
Windows Server 2003 SP2
Oracle 10g
/PAE /3GB
RAM 8GB
instances:6
Memory Usage of Oracle.exe for current FY goes upto 2.5 GB
1) exclude database folders from virus scan
2) decrease sga_max_size for all other instances
3)Schedule to kill sniped sessions
http://systemnetworkdatabaseadministrator.blogspot.com/2010/09/idle-session-sniped-session-kill.html
Now Memory Usage of Oracle.exe for current FY goes down 2.1 GB
-------------------------
Friday, December 31, 2010
Thursday, November 11, 2010
Clear Temporary tablespace
1)
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'E:\SNSD1011\TEMP02.ORA' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
2)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3)
DROP TABLESPACE temporary INCLUDING CONTENTS AND DATAFILES;
4)
cREATE TEMPORARY TABLESPACE temporary
TEMPFILE 'E:\SNSD1011\TEMP01.ORA' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
5)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporary;
6)
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
7)
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'temporary';
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'E:\SNSD1011\TEMP02.ORA' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
2)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3)
DROP TABLESPACE temporary INCLUDING CONTENTS AND DATAFILES;
4)
cREATE TEMPORARY TABLESPACE temporary
TEMPFILE 'E:\SNSD1011\TEMP01.ORA' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
5)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporary;
6)
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
7)
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'temporary';
Wednesday, October 6, 2010
When to Rebuild an Index?
When to Rebuild an Index?
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.
When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur,creating a bottleneck in performance. Here is a sample procedure on how to identify the such indexes:
1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.
For example:
SQL> analyze index emp_empno_pk compute statistics;
Index analyzed.
2. Run the query below to find out how skewed each index is. This query checks on all indexes that are on emp table.
select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from user_indexes;
select * from dba_indexes where blevel > 4;
3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. If the BLEVEL were to be more than 4, it is recommended to rebuild the index.
Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.
4. Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table.
SQL> analyze index emp_empno_pk validate structure;
Index analyzed.
5. Run the following query to find out PCT_DELETED ratio.
SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
3 from index_stats
4 where NAME='EMP_EMPNO_PK';
The PCT_DELETED column shows the percent of leaf entries (i.e. index entries) that have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.
The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average. For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in (10000-9000) x 100 / 10000 = 10. This shows a good distribution of values. If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.
In general, indexes and tables should be rebuilt when they become too fragmented.
In practice, you probably will not have need to do it with Oracle 10g. Fragmentation occurs on tables and indexes with lots of changes to structure (adding/removing columns) and lots of data changes (insert, update, delete).
From v10, Oracle have number of automated processes that take care about database performance. One of them is "Segment advisor" that runs automatically.
-----------------------
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.
When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur,creating a bottleneck in performance. Here is a sample procedure on how to identify the such indexes:
1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.
For example:
SQL> analyze index emp_empno_pk compute statistics;
Index analyzed.
2. Run the query below to find out how skewed each index is. This query checks on all indexes that are on emp table.
select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from user_indexes;
select * from dba_indexes where blevel > 4;
3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. If the BLEVEL were to be more than 4, it is recommended to rebuild the index.
Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.
4. Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table.
SQL> analyze index emp_empno_pk validate structure;
Index analyzed.
5. Run the following query to find out PCT_DELETED ratio.
SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
3 from index_stats
4 where NAME='EMP_EMPNO_PK';
The PCT_DELETED column shows the percent of leaf entries (i.e. index entries) that have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.
The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average. For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in (10000-9000) x 100 / 10000 = 10. This shows a good distribution of values. If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.
In general, indexes and tables should be rebuilt when they become too fragmented.
In practice, you probably will not have need to do it with Oracle 10g. Fragmentation occurs on tables and indexes with lots of changes to structure (adding/removing columns) and lots of data changes (insert, update, delete).
From v10, Oracle have number of automated processes that take care about database performance. One of them is "Segment advisor" that runs automatically.
-----------------------
Friday, September 24, 2010
Corrupted OEM Recreation Error
emca -deconfig dbcontrol db -repos drop
drop taking more time and hang the server
not able to create oem because sysman already exist
emca -config dbcontrol db -repos create
use following to drop and create
emca -deconfig dbcontrol db
emca -config dbcontrol db
following is must or restart service oracledbconsoleservice
emctl stop dbconsole
emctl start dbconsole
--------------------
drop taking more time and hang the server
not able to create oem because sysman already exist
emca -config dbcontrol db -repos create
use following to drop and create
emca -deconfig dbcontrol db
emca -config dbcontrol db
following is must or restart service oracledbconsoleservice
emctl stop dbconsole
emctl start dbconsole
--------------------
Wednesday, September 22, 2010
IDLE Session > Sniped Session > Kill > Release Resource
Making Idle Session SNIPED:
An idle session can be setup to become sniped after x minutes by setting the initialization parameter resource_limit = true in the init.ora and idle_time in the user profile. You can make user session becomes sniped after 1 hours of idle time by running below command:
select * from v$parameter where name like '%resource_limit%';
select * from dba_profiles where resource_name like 'IDLE_TIME';
alter system set resource_limit=true scope=spfile;
alter profile LD_BACKOFFICE limit IDLE_TIME 30;
after 30 minutes status=inactive will become sniped
--------------------Finding Sniped Idle Session--------------------------
SELECT
p.spid "Thread ID",
s.sid "Session ID",
s.serial# "Serial Num",
b.name "Background Process",
s.sql_id "SQL ID",
s.username "Session User",
s.osuser "OS User",
s.status "Status",
s.program "Program"
FROM
v$process p,
v$bgprocess b,
v$session s
WHERE
s.status = 'SNIPED'
AND
s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name IS NULL
ORDER BY s.username ;
-----------------release resource from idle inactive / sniped / killed session-------
orakill SID SPID
-------------script---Sniped.sql--------------------------
store set c:\prevsqlsettings.sql replace
set pagesize 0
set feedback off
set trimspool on
set termout off
set verify off
spool c:\killSniped.bat
select 'orakill ' || i.instance_name || ' ' || spid from v$instance i,v$process p, v$session s,v$bgprocess b where p.addr=s.paddr AND b.paddr(+) = p.addr AND b.name IS NULL and s.status='SNIPED' and s.username != 'SYSTEM' and s.username != 'SYS' and s.username != 'DBSNMP' and s.username != 'REPADMIN' and s.username != 'WMSYS' and s.username != 'TSMSYS' and s.username != 'OUTLN' and s.username != 'ORACLE_OCM' and s.username != 'LDBO' and s.username != 'SNSEXPORT' and s.username != 'RENEXPORT' and s.username != 'CLLVL';
spool off
host c:\killSniped.bat
@c:\sqlsettings
host del c:\killSniped.bat
host del c:\prevsqlsettings.sql
exit
------------Job Schedule-----sniped.bat--------------
@echo
set oracle_sid=sns6
sqlplus ldbo/ldbo@sns1011srv @c:\sniped.sql
exit
--------------------------------------------------------Just for Knowledge-------------------------------------------
Then IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.
SQLNET.EXPIRE_TIME =10
sqlnet.expire_time
sqlnet.expire_time parameter: The database is to detect dead TCP connections, not idle client applications
if you kill an idle session, you don't have a dead connection. A dead connection occurs if you switch off or disconnect your client such that it cannot send a proper FIN to close the TCP/IP connection.
Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client, and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again. A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user (or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates. But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
------------maual kill session----------------
alter system kill session '526,67';
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
------------find sessions which are running for more than 2 hours----------------
SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC ;
----------------Kill all user sessions except me----------------
1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null
4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;
----------------------------alter kill session --job schedule-------------------
creating a procedure to kill sesssion and schedule it nightly be the best solution?
Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session
'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;
------------------------------
An idle session can be setup to become sniped after x minutes by setting the initialization parameter resource_limit = true in the init.ora and idle_time in the user profile. You can make user session becomes sniped after 1 hours of idle time by running below command:
select * from v$parameter where name like '%resource_limit%';
select * from dba_profiles where resource_name like 'IDLE_TIME';
alter system set resource_limit=true scope=spfile;
alter profile LD_BACKOFFICE limit IDLE_TIME 30;
after 30 minutes status=inactive will become sniped
--------------------Finding Sniped Idle Session--------------------------
SELECT
p.spid "Thread ID",
s.sid "Session ID",
s.serial# "Serial Num",
b.name "Background Process",
s.sql_id "SQL ID",
s.username "Session User",
s.osuser "OS User",
s.status "Status",
s.program "Program"
FROM
v$process p,
v$bgprocess b,
v$session s
WHERE
s.status = 'SNIPED'
AND
s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name IS NULL
ORDER BY s.username ;
-----------------release resource from idle inactive / sniped / killed session-------
orakill SID SPID
-------------script---Sniped.sql--------------------------
store set c:\prevsqlsettings.sql replace
set pagesize 0
set feedback off
set trimspool on
set termout off
set verify off
spool c:\killSniped.bat
select 'orakill ' || i.instance_name || ' ' || spid from v$instance i,v$process p, v$session s,v$bgprocess b where p.addr=s.paddr AND b.paddr(+) = p.addr AND b.name IS NULL and s.status='SNIPED' and s.username != 'SYSTEM' and s.username != 'SYS' and s.username != 'DBSNMP' and s.username != 'REPADMIN' and s.username != 'WMSYS' and s.username != 'TSMSYS' and s.username != 'OUTLN' and s.username != 'ORACLE_OCM' and s.username != 'LDBO' and s.username != 'SNSEXPORT' and s.username != 'RENEXPORT' and s.username != 'CLLVL';
spool off
host c:\killSniped.bat
@c:\sqlsettings
host del c:\killSniped.bat
host del c:\prevsqlsettings.sql
exit
------------Job Schedule-----sniped.bat--------------
@echo
set oracle_sid=sns6
sqlplus ldbo/ldbo@sns1011srv @c:\sniped.sql
exit
--------------------------------------------------------Just for Knowledge-------------------------------------------
Then IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.
SQLNET.EXPIRE_TIME =10
sqlnet.expire_time
sqlnet.expire_time parameter: The database is to detect dead TCP connections, not idle client applications
if you kill an idle session, you don't have a dead connection. A dead connection occurs if you switch off or disconnect your client such that it cannot send a proper FIN to close the TCP/IP connection.
Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client, and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again. A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user (or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates. But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
------------maual kill session----------------
alter system kill session '526,67';
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
------------find sessions which are running for more than 2 hours----------------
SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC ;
----------------Kill all user sessions except me----------------
1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null
4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;
----------------------------alter kill session --job schedule-------------------
creating a procedure to kill sesssion and schedule it nightly be the best solution?
Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session
'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;
------------------------------
Monday, September 20, 2010
User Session Monitoring
-------------------Sniped idle session--------------
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON_before, SID, v$session.SERIAL#, v$process.SPID OS_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
--------- Monitor Per Session UGA, PGA UGA Max, PGA Max Usage----------------
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC;
--------- Monitor Per Session UGA (User Global Area), PGA(Process Global Area) Usage----------------
SELECT
e.SID,
e.username,
e.status,
a.UGA_MEMORY,
b.PGA_MEMORY
FROM
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,
v$session e
WHERE
e.sid=a.sid
AND
e.sid=b.sid
ORDER BY
e.status,
a.UGA_MEMORY desc;
--------- Monitor Per Session UGA, PGA and Cursor Usage----------------
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
--------------------Determine PGA and Process Memory in use by Process------------------------
set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"
select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;
-------------Session I/O By User---------------------------
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
----------------CPU Usage By Session-------------------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc;
----------------Resource Usage By User-------------------------
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME;
--------------Session Stats By Session-------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc;
----------Cursor Usage By Session-------------
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls" ;
---------------------------User Hit Ratios---------------------
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS));
--------------------------------generate sql to kill a sessoin. See genDisconnect.sql
accept killme prompt 'User to Kill > '
set ver off
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' as sql_to_run from V$PROCESS p, V$SESSION s
where s.paddr = p.addr and s.username = upper ('&killme');
User to Kill > UIBADUSER
SQL_TO_RUN
---------------------------------------------------------
alter system disconnect session '296,3841' immediate;
--list old sessions to "disconnect", then generate sql to disconnect them
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
set echo off feedback off heading off
select '--run sql below to disconnect sessions above' from dual;
select 'alter system disconnect session '||chr(39)||sid||','||s.serial#||chr(39)||' immediate;' as sql_to_run from V$PROCESS p,V$SESSION s
where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
select '' from dual;
exit
OSUSER SPID USERNAME SID SERIAL# START_TIME STATUS MACHINE MODULE
------------ -------- ------------------------- ----- -------- -------------------- -------- -------------------- ----------
oracle 11911 AWILS1 1317 54857 Thu 25 Aug 07:18:55 INACTIVE banformprod5.admin.u SHACRSE
----------------------run sql below to disconnect sessions above
alter system disconnect session '1317,54857' immediate;
------------------------show current users. See showUsers.sql
set echo off feedback off heading off
set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select 'Show users ordered by logon_time, username' from dual;
select 'OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE' from dual;
select '------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------' from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;
Show users ordered by logon_time, username
OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE
------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------
submit 29273 JOBRUNNER 2 19 332 Sat 20 Aug 04:01:07 ACTIVE servername.dns.netw SFRPIPE
oracle 28413 SYS 26 452 Mon 22 Aug 15:31:55 INACTIVE servername.dns.netw sqlplus@se
johnny 14223 TLJOHN 37 710 Tue 23 Aug 09:03:36 INACTIVE TREE\USER1 TOAD 7.6.0
janedoey 22345 USER01 28 1462 Tue 23 Aug 12:44:00 INACTIVE TREE\USER1 designer.e
oracle 29986 OPS$ORACLE 18 219 Tue 23 Aug 13:01:01 ACTIVE servername.dns.netw SQL*Plus
-------------------generate SQL to disconnect blocking users.
SELECT 'alter system disconnect session '''||s.sid||','||s.serial#||'''immediate;' as "run_this_sql" FROM sys.x$kglpn p, sys.v_$session s,
(select kglhdadr, kglnaobj FROM sys.x$kglob WHERE kglhdadr in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')) k
WHERE p.kglpnuse = s.saddr AND kglpnhdl in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')
and k.kglhdadr = kglpnhdl and kglpnmod=3 order by kglpnmod;
run_this_sql
--------------------------------------------------------------------------------------
alter system disconnect session '853,44482'immediate;
------------------find the number of current commits occuring.
select command,count(command) from V$SESSION where command = '44' group by command order by 1;
USERNAME COUNT
-------------------- -----
JOBSUMITTER 37680
VISITOR 3488
WORKER 2839
TESTER 1793
DBSNMP 11
SYS 740
-----------------------show "stale" connections. showStale.sql---------------------------
set recsep off
set feedback off
set pages 1000
set wrap off
define l1='Stale Sessions (showStale.sql)'
define l2='==================================='
ttitle left l1 skip 1 left l2 skip 2
col sid_serial format a12 heading 'SID,SERIAL#'
col username format a10 heading 'User'
col osuser format a10 heading 'OS User'
col stats format a8 heading 'Status'
col program format a20 heading 'Program'
col module format a30 heading 'Module'
column last_call format 9999 heading 'Last|Call|(Days)'
select to_char(logon_time,'Dy dd Mon HH24:MI') logon_time,sid || ',' || serial# sid_serial,
username,osuser,trunc(last_call_et/60/60/24) last_call,status,program,module from V$SESSION
where type != 'BACKGROUND' and username is not null and trunc(last_call_et/60/60/24) > 1 order by 5;
exit
Last
Call
LOGON_TIME SID,SERIAL# User OS User (Days) STATUS Program Module
---------------- ------------ ---------- ---------- ------ -------- -------------------- ------------------------------
-----------------------show how users are logged on------------
select program,count(program) from V$SESSION group by program order by program;
PROGRAM COUNT(PROGRAM)
-------------------------------------------------- --------------
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON_before, SID, v$session.SERIAL#, v$process.SPID OS_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
--------- Monitor Per Session UGA, PGA UGA Max, PGA Max Usage----------------
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC;
--------- Monitor Per Session UGA (User Global Area), PGA(Process Global Area) Usage----------------
SELECT
e.SID,
e.username,
e.status,
a.UGA_MEMORY,
b.PGA_MEMORY
FROM
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,
v$session e
WHERE
e.sid=a.sid
AND
e.sid=b.sid
ORDER BY
e.status,
a.UGA_MEMORY desc;
--------- Monitor Per Session UGA, PGA and Cursor Usage----------------
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
--------------------Determine PGA and Process Memory in use by Process------------------------
set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"
select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;
-------------Session I/O By User---------------------------
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
----------------CPU Usage By Session-------------------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc;
----------------Resource Usage By User-------------------------
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME;
--------------Session Stats By Session-------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc;
----------Cursor Usage By Session-------------
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls" ;
---------------------------User Hit Ratios---------------------
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS));
--------------------------------generate sql to kill a sessoin. See genDisconnect.sql
accept killme prompt 'User to Kill > '
set ver off
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' as sql_to_run from V$PROCESS p, V$SESSION s
where s.paddr = p.addr and s.username = upper ('&killme');
User to Kill > UIBADUSER
SQL_TO_RUN
---------------------------------------------------------
alter system disconnect session '296,3841' immediate;
--list old sessions to "disconnect", then generate sql to disconnect them
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
set echo off feedback off heading off
select '--run sql below to disconnect sessions above' from dual;
select 'alter system disconnect session '||chr(39)||sid||','||s.serial#||chr(39)||' immediate;' as sql_to_run from V$PROCESS p,V$SESSION s
where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
select '' from dual;
exit
OSUSER SPID USERNAME SID SERIAL# START_TIME STATUS MACHINE MODULE
------------ -------- ------------------------- ----- -------- -------------------- -------- -------------------- ----------
oracle 11911 AWILS1 1317 54857 Thu 25 Aug 07:18:55 INACTIVE banformprod5.admin.u SHACRSE
----------------------run sql below to disconnect sessions above
alter system disconnect session '1317,54857' immediate;
------------------------show current users. See showUsers.sql
set echo off feedback off heading off
set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select 'Show users ordered by logon_time, username' from dual;
select 'OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE' from dual;
select '------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------' from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;
Show users ordered by logon_time, username
OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE
------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------
submit 29273 JOBRUNNER 2 19 332 Sat 20 Aug 04:01:07 ACTIVE servername.dns.netw SFRPIPE
oracle 28413 SYS 26 452 Mon 22 Aug 15:31:55 INACTIVE servername.dns.netw sqlplus@se
johnny 14223 TLJOHN 37 710 Tue 23 Aug 09:03:36 INACTIVE TREE\USER1 TOAD 7.6.0
janedoey 22345 USER01 28 1462 Tue 23 Aug 12:44:00 INACTIVE TREE\USER1 designer.e
oracle 29986 OPS$ORACLE 18 219 Tue 23 Aug 13:01:01 ACTIVE servername.dns.netw SQL*Plus
-------------------generate SQL to disconnect blocking users.
SELECT 'alter system disconnect session '''||s.sid||','||s.serial#||'''immediate;' as "run_this_sql" FROM sys.x$kglpn p, sys.v_$session s,
(select kglhdadr, kglnaobj FROM sys.x$kglob WHERE kglhdadr in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')) k
WHERE p.kglpnuse = s.saddr AND kglpnhdl in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')
and k.kglhdadr = kglpnhdl and kglpnmod=3 order by kglpnmod;
run_this_sql
--------------------------------------------------------------------------------------
alter system disconnect session '853,44482'immediate;
------------------find the number of current commits occuring.
select command,count(command) from V$SESSION where command = '44' group by command order by 1;
USERNAME COUNT
-------------------- -----
JOBSUMITTER 37680
VISITOR 3488
WORKER 2839
TESTER 1793
DBSNMP 11
SYS 740
-----------------------show "stale" connections. showStale.sql---------------------------
set recsep off
set feedback off
set pages 1000
set wrap off
define l1='Stale Sessions (showStale.sql)'
define l2='==================================='
ttitle left l1 skip 1 left l2 skip 2
col sid_serial format a12 heading 'SID,SERIAL#'
col username format a10 heading 'User'
col osuser format a10 heading 'OS User'
col stats format a8 heading 'Status'
col program format a20 heading 'Program'
col module format a30 heading 'Module'
column last_call format 9999 heading 'Last|Call|(Days)'
select to_char(logon_time,'Dy dd Mon HH24:MI') logon_time,sid || ',' || serial# sid_serial,
username,osuser,trunc(last_call_et/60/60/24) last_call,status,program,module from V$SESSION
where type != 'BACKGROUND' and username is not null and trunc(last_call_et/60/60/24) > 1 order by 5;
exit
Last
Call
LOGON_TIME SID,SERIAL# User OS User (Days) STATUS Program Module
---------------- ------------ ---------- ---------- ------ -------- -------------------- ------------------------------
-----------------------show how users are logged on------------
select program,count(program) from V$SESSION group by program order by program;
PROGRAM COUNT(PROGRAM)
-------------------------------------------------- --------------
Wednesday, September 8, 2010
Listener.log Cleanup
-------------------
@echo
lsnrctl stop
rename d:\oracle\product\10.2.0\db_1\network\log\listener.log listener1.log
lsnrctl start
move d:\oracle\product\10.2.0\db_1\network\log\listener1.log d:\oracle\product\10.2.0\db_1\network\log\backup
-----------------------
or manually using lsnrctl utility
c:\> LSNRCT
LSNRCTL> show log_file
LSNRCTL> set log_file listenernew.log
LSNRCTL> show log_file
LSNRCTL> exit
@echo
lsnrctl stop
rename d:\oracle\product\10.2.0\db_1\network\log\listener.log listener1.log
lsnrctl start
move d:\oracle\product\10.2.0\db_1\network\log\listener1.log d:\oracle\product\10.2.0\db_1\network\log\backup
-----------------------
or manually using lsnrctl utility
c:\> LSNRCT
LSNRCTL> show log_file
LSNRCTL> set log_file listenernew.log
LSNRCTL> show log_file
LSNRCTL> exit
LOG_ARCHIVE_MAX_PROCESSES
Oracle LOG_ARCHIVE_MAX_PROCESSES parameter specifies the number of active ARCH processes (ARC0-ARCn) initially invoked by Oracle. The value of LOG_ARCHIVE_MAX_PROCESSES parameter is evaluated at instance startup if LOG_ARCHIVE_START initialization parameter is set to true.
LOG_ARCHIVE_START=true
LOG_ARCHIVE_MAX_PROCESSES
no of log_archive_max_processes depends on active log group
select * from V$log;
If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can increase the maximum number of archiver processes with the LOG_ARCHIVE_MAX_PROCESSES initialization parameter. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes.
Sometimes it happens that we face application slow down due to heavy network traffic on the application and we tend to increase the LOG_ARCHIVE_MAX_PROCESSES parameter however this is not always the best solution. Whenever you face application slow down due to network traffic then instead of increasing the LOG _ARCHIVE_MAX_PROCESSES parameter one should first try to increase bandwidth or decrease network roundtrips or exchanged bytes.
----------------
LOG_ARCHIVE_START=true
LOG_ARCHIVE_MAX_PROCESSES
no of log_archive_max_processes depends on active log group
select * from V$log;
If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can increase the maximum number of archiver processes with the LOG_ARCHIVE_MAX_PROCESSES initialization parameter. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes.
Sometimes it happens that we face application slow down due to heavy network traffic on the application and we tend to increase the LOG_ARCHIVE_MAX_PROCESSES parameter however this is not always the best solution. Whenever you face application slow down due to network traffic then instead of increasing the LOG _ARCHIVE_MAX_PROCESSES parameter one should first try to increase bandwidth or decrease network roundtrips or exchanged bytes.
----------------
ORA-12518: TNS:listener could not hand off client connection
Connection is break again and again and showing following error
Error
ORA-12518: TNS:listener could not hand off client connection
----------------------
-----------------ALERT LOG--------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
-------------------sns6_psp0_7760.trc---------------
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:2050M/8186M, Ph+PgF:49980M/56271M, VA:24M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 3
Windows thread id: 7760, image: ORACLE.EXE (PSP0)
*** 2010-09-09 11:13:41.458
Process startup failed, error stack:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
------------------udump\sns6_ora_5440.trc-------------
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1784M/8186M, Ph+PgF:49813M/56271M, VA:32M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 270
Windows thread id: 5440, image: ORACLE.EXE (SHAD)
*** 2010-09-09 11:12:27.050
*** ACTION NAME:() 2010-09-09 11:12:27.003
*** MODULE NAME:(ld.exe) 2010-09-09 11:12:27.003
*** SERVICE NAME:(SYS$USERS) 2010-09-09 11:12:27.003
*** SESSION ID:(345.13810) 2010-09-09 11:12:27.003
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P004
kxfpg1srv
could not start local P006
kxfpg1srv
could not start local P006
-----------------LISTENER LOG--------------------
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 233: Unknown error
-------------------------
Windows 233 error means "No process is on the other end of the pipe."
-----client machine-------------
ping 10.100.0.65 -t
tnsping 10.100.0.65 1521
nslookup 10.100.0.65
--------------
lnsrctl startus
memory is efficient for process and make connnection
SGA
RAM 8 GB
virtual memory 8 GB * 1.5 = 12GB or System managed for c: will be good
D:\oracle\product\10.2.0\db_1\network\log\listener.log size
System Properties> Startup Recovery> settings>boot.ini
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /3GB /NoExecute=OptOut
----------------
exclude database files folders from McAfee on-access scan
--------------
select username,count(*) from v$session group by username having count(*) > 1 order by 2 ;
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 233 339 500 500
sessions 241 345 555 555
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
---------------------------------------------------------------- ----------
logons cumulative 89876
logons current 216
SQL> select count(*) from v$session;
COUNT(*)
----------
260
SQL> select count(*) from v$process;
COUNT(*)
----------
261
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 35
log_archive_max_processes integer 2
processes integer 500
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 555
shared_server_sessions integer
SQL> SHOW PARAMETER SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1600M
sga_target big integer 1600M
SQL> select name,display_value from v$parameter where name in
2 (
3 'job_queue_processes',
4 'open_cursors',
5 'pga_aggregate_target',
6 'processes',
7 'sessions',
8 'sga_target',
9 'sga_max_size'
10 );
NAME DISPLAY_VALUE
-------------------------------------------------------------------------------- -------------------
processes 500
sessions 555
sga_max_size 1600M
sga_target 1600M
job_queue_processes 35
open_cursors 300
pga_aggregate_target 322M
7 rows selected.
SQL> select round(sum(bytes)/1024/1024,2) total_sga,
2 round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
3 round((sum(decode(name,'free
4 memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
5 from
6 v$sgastat;
TOTAL_SGA FREE FREE_PER
--------- ---------- ----------
1627.04 80.69 0
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round(sum(reloads)/sum(pins)*100,2)
2 AS "Misses Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Misses Ratio, %
---------- ---------------------------- ---------------
159472017 3209055 2.01
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,
2 "Hit Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Hit Ratio, %
---------- ---------------------------- ------------
159472378 3209055 98.03
----------------------------------------------------------------------------------------------------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Cause
On Windows, each session creates a new thread, and by default 1MB is allocated for the session when it connects. How much memory is in the server, and is this the 32 bit or 64 bit version of Windows 2003. The server might be hitting the upper limit for maximum memory per process, which is either 2GB or 3GB depending on the settings in the server's boot.ini.
OS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.
In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On
Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of
addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini' file, this
changes the ratio of kernel memory and user memory. When the '/3GB' switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail.
Solution
To implement the solution, please execute the following steps:
Tune the application running on the 32-bit environment so that it will not consume greater than
2.7GB of virtual memory when the '/3GB' switch is set in the 'boot.ini' file.
In the case of Oracle encountering the OS resource issue, the options are:
1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user
process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only
uses a fraction of the amount of memory allocated to each user process when initial connection to
the database is established.
----------------
1.
Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory-intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts. . The only configuration change required is to ensure that the /3GB flag is used in Windows 'boot.ini file. See http://support.microsoft.com/?kbid=291988 Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory -intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts.. The only change required is to ensure Configuration that the / 3GB flag is used in Windows' Boot.ini File. See http://support.microsoft.com/?kbid=291988
2. 2.
On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS
USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory. USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.
It enables or disables the use of the extended buffer cache mechanism for 32- bit platforms that can support more than 4 GB of physical memory. See Note 342080.1 It enables or disables the use of the extended buffer cache mechanism for 32 - bit platforms that can support more than 4 GB of physical memory. See Note 342080.1
3. 3.
Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) giving memory back to the O/S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve' this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA is never increases to the value of sga_max_size, then the memory is wasted. Reserved for no reason. Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) Giving memory back to the O / S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve 'this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA i
------------------
----------------------------------------------Turn On Listener Tracing--------------------------------------------
TRACE_LEVEL_LISTENER= USER
TRACE_TIMESTAMP_listener=true
Reload
Turn on listener tracing and re-execute the operation. Verify that the
listener and database instance are properly configured for direct
handoff.
At times, when troubleshooting connectivity issues or performance issues, you might need to run listener trace (server side) and/or a sqlnet trace (client side trace). Oracle support staff will also ask for these files when you enlist their help in troubleshooting these issues. Here are the steps on how you can do that:
To enable sqlnet trace (client side trace):
Add the following to the sqlnet.ora file on the application server(s) from where the connections are being made to the Oracle database:
Trace_level_client=16
Trace_directory_client= # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
To enable listener trace (server side):
Add the following to the listener.ora file on the db server before starting the listener:
TRACE_TIMESTAMP_=on
TRACE_DIRECTORY_<>=
Here is the name of the listener. Then run lsnrctl to turn on the listener trace:
lsnrctl> set current_listener
lsnrctl> set trc_level support
You can run both trace at the same time for 15 minutes or so in order to get some meaningful data set.
To stop the sqlnet trace:
Remove the following entries from sqlnet.ora or comment them out by using the # sign:
Trace_level_client=16
Trace_directory_client= # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
To stop the listener trace:
lsnrctl> set trc_level off
TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.
i))off (equivalent to 0) provides no tracing.
ii))user (equivalent to 4) traces to identify user-induced error conditions.
iii)admin (equivalent to 6) traces to identify installation-specific problems.
iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.
------------------
LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER= USER
TRACE_FILE_LISTENER=listener.trc
TRACE_DIRECTORY_LISTENER=d:\oracle\product\10.2.0\db_1\network\trace
--------------------------
Error
ORA-12518: TNS:listener could not hand off client connection
----------------------
-----------------ALERT LOG--------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
-------------------sns6_psp0_7760.trc---------------
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:2050M/8186M, Ph+PgF:49980M/56271M, VA:24M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 3
Windows thread id: 7760, image: ORACLE.EXE (PSP0)
*** 2010-09-09 11:13:41.458
Process startup failed, error stack:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
------------------udump\sns6_ora_5440.trc-------------
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1784M/8186M, Ph+PgF:49813M/56271M, VA:32M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 270
Windows thread id: 5440, image: ORACLE.EXE (SHAD)
*** 2010-09-09 11:12:27.050
*** ACTION NAME:() 2010-09-09 11:12:27.003
*** MODULE NAME:(ld.exe) 2010-09-09 11:12:27.003
*** SERVICE NAME:(SYS$USERS) 2010-09-09 11:12:27.003
*** SESSION ID:(345.13810) 2010-09-09 11:12:27.003
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P004
kxfpg1srv
could not start local P006
kxfpg1srv
could not start local P006
-----------------LISTENER LOG--------------------
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 233: Unknown error
-------------------------
Windows 233 error means "No process is on the other end of the pipe."
-----client machine-------------
ping 10.100.0.65 -t
tnsping 10.100.0.65 1521
nslookup 10.100.0.65
--------------
lnsrctl startus
memory is efficient for process and make connnection
SGA
RAM 8 GB
virtual memory 8 GB * 1.5 = 12GB or System managed for c: will be good
D:\oracle\product\10.2.0\db_1\network\log\listener.log size
System Properties> Startup Recovery> settings>boot.ini
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /3GB /NoExecute=OptOut
----------------
exclude database files folders from McAfee on-access scan
--------------
select username,count(*) from v$session group by username having count(*) > 1 order by 2 ;
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 233 339 500 500
sessions 241 345 555 555
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
---------------------------------------------------------------- ----------
logons cumulative 89876
logons current 216
SQL> select count(*) from v$session;
COUNT(*)
----------
260
SQL> select count(*) from v$process;
COUNT(*)
----------
261
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 35
log_archive_max_processes integer 2
processes integer 500
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 555
shared_server_sessions integer
SQL> SHOW PARAMETER SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1600M
sga_target big integer 1600M
SQL> select name,display_value from v$parameter where name in
2 (
3 'job_queue_processes',
4 'open_cursors',
5 'pga_aggregate_target',
6 'processes',
7 'sessions',
8 'sga_target',
9 'sga_max_size'
10 );
NAME DISPLAY_VALUE
-------------------------------------------------------------------------------- -------------------
processes 500
sessions 555
sga_max_size 1600M
sga_target 1600M
job_queue_processes 35
open_cursors 300
pga_aggregate_target 322M
7 rows selected.
SQL> select round(sum(bytes)/1024/1024,2) total_sga,
2 round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
3 round((sum(decode(name,'free
4 memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
5 from
6 v$sgastat;
TOTAL_SGA FREE FREE_PER
--------- ---------- ----------
1627.04 80.69 0
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round(sum(reloads)/sum(pins)*100,2)
2 AS "Misses Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Misses Ratio, %
---------- ---------------------------- ---------------
159472017 3209055 2.01
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,
2 "Hit Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Hit Ratio, %
---------- ---------------------------- ------------
159472378 3209055 98.03
----------------------------------------------------------------------------------------------------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Cause
On Windows, each session creates a new thread, and by default 1MB is allocated for the session when it connects. How much memory is in the server, and is this the 32 bit or 64 bit version of Windows 2003. The server might be hitting the upper limit for maximum memory per process, which is either 2GB or 3GB depending on the settings in the server's boot.ini.
OS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.
In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On
Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of
addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini' file, this
changes the ratio of kernel memory and user memory. When the '/3GB' switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail.
Solution
To implement the solution, please execute the following steps:
Tune the application running on the 32-bit environment so that it will not consume greater than
2.7GB of virtual memory when the '/3GB' switch is set in the 'boot.ini' file.
In the case of Oracle encountering the OS resource issue, the options are:
1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user
process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only
uses a fraction of the amount of memory allocated to each user process when initial connection to
the database is established.
----------------
1.
Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory-intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts. . The only configuration change required is to ensure that the /3GB flag is used in Windows 'boot.ini file. See http://support.microsoft.com/?kbid=291988 Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory -intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts.. The only change required is to ensure Configuration that the / 3GB flag is used in Windows' Boot.ini File. See http://support.microsoft.com/?kbid=291988
2. 2.
On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS
USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory. USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.
It enables or disables the use of the extended buffer cache mechanism for 32- bit platforms that can support more than 4 GB of physical memory. See Note 342080.1 It enables or disables the use of the extended buffer cache mechanism for 32 - bit platforms that can support more than 4 GB of physical memory. See Note 342080.1
3. 3.
Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) giving memory back to the O/S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve' this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA is never increases to the value of sga_max_size, then the memory is wasted. Reserved for no reason. Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) Giving memory back to the O / S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve 'this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA i
------------------
----------------------------------------------Turn On Listener Tracing--------------------------------------------
TRACE_LEVEL_LISTENER= USER
TRACE_TIMESTAMP_listener=true
Reload
Turn on listener tracing and re-execute the operation. Verify that the
listener and database instance are properly configured for direct
handoff.
At times, when troubleshooting connectivity issues or performance issues, you might need to run listener trace (server side) and/or a sqlnet trace (client side trace). Oracle support staff will also ask for these files when you enlist their help in troubleshooting these issues. Here are the steps on how you can do that:
To enable sqlnet trace (client side trace):
Add the following to the sqlnet.ora file on the application server(s) from where the connections are being made to the Oracle database:
Trace_level_client=16
Trace_directory_client=
Trace_unique_client=on
Trace_timestamp_client=on
To enable listener trace (server side):
Add the following to the listener.ora file on the db server before starting the listener:
TRACE_TIMESTAMP_
TRACE_DIRECTORY_<>=
Here
lsnrctl> set current_listener
lsnrctl> set trc_level support
You can run both trace at the same time for 15 minutes or so in order to get some meaningful data set.
To stop the sqlnet trace:
Remove the following entries from sqlnet.ora or comment them out by using the # sign:
Trace_level_client=16
Trace_directory_client=
Trace_unique_client=on
Trace_timestamp_client=on
To stop the listener trace:
lsnrctl> set trc_level off
TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.
i))off (equivalent to 0) provides no tracing.
ii))user (equivalent to 4) traces to identify user-induced error conditions.
iii)admin (equivalent to 6) traces to identify installation-specific problems.
iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.
------------------
LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER= USER
TRACE_FILE_LISTENER=listener.trc
TRACE_DIRECTORY_LISTENER=d:\oracle\product\10.2.0\db_1\network\trace
--------------------------
Friday, September 3, 2010
MySQL with Oracle SQL Developer
Issue
Trying to connect to MySQL database using Oracle's SQL-Developer receiving the
following error message:
Status: Failure -com.mysql.jdbc.Driver
or
Status: Failure -Unable to find driver: com.mysql.jdbc.Driver
Solution
Download mysql JDBC connector from:
http://dev.mysql.com/downloads/connector/j/5.1.html
extract it on your PC and take the following file:
mysql-connector-java-5.1.5-bin.jar
... place it into sqldeveloper\sqldeveloper\extensions
then go to sqldeveloper -> Tools -> Preferences
Database -> Third Party JDBC Drivers
click "Add Entry" and select mysql-connector-java-5.1.5-bin.jar
restart sqldeveloper and you should be able to connect to mysql at this point.
-----Error---------communications link failure last packet sent successfully----
$ sudo vi /etc/mysql/hosts.allow
And add a line:
mysqld : 127.0.0.1 : allow
Trying to connect to MySQL database using Oracle's SQL-Developer receiving the
following error message:
Status: Failure -com.mysql.jdbc.Driver
or
Status: Failure -Unable to find driver: com.mysql.jdbc.Driver
Solution
Download mysql JDBC connector from:
http://dev.mysql.com/downloads/connector/j/5.1.html
extract it on your PC and take the following file:
mysql-connector-java-5.1.5-bin.jar
... place it into sqldeveloper\sqldeveloper\extensions
then go to sqldeveloper -> Tools -> Preferences
Database -> Third Party JDBC Drivers
click "Add Entry" and select mysql-connector-java-5.1.5-bin.jar
restart sqldeveloper and you should be able to connect to mysql at this point.
-----Error---------communications link failure last packet sent successfully----
$ sudo vi /etc/mysql/hosts.allow
And add a line:
mysqld : 127.0.0.1 : allow
Wednesday, September 1, 2010
Oracle User Login / Error Email Notification
connect sys as sysdba user and run two scripts for install and configure utl_mail package
SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql
Package created.
Synonym created.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;
Package body created.
No errors.
Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter
SQL> alter system set smtp_out_server = 'mail.uniconindia.in' scope=spfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
Grant EXECUTE privs to user which use utl_mail package.
SQL> grant execute on utl_mail to ldbo;
Grant succeeded.
---------------------------------Database Shutdown Notification-------------------
CREATE OR REPLACE TRIGGER LDBO_shut_notifications
before shutdown on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Problem Alert - LD Database is Down';
l_message VARCHAR2(500);
BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END LDBO_shut_notifications;
/
---------------------------------Database Startup Notification-------------------
CREATE OR REPLACE TRIGGER LDBO_start_notifications
after startup on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Recovery Alert - LD Database is Up';
l_message VARCHAR2(500);
BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END LDBO_start_notifications;
/
------------------------------------SYS User Logon-------------
CREATE OR REPLACE TRIGGER SYS_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - SYS Login';
l_message VARCHAR2(500);
BEGIN
if (USER in ('SYS')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END SYS_logon_notifications;
/
------------------------------------LDBO- DBA User Logon-------------
CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);
BEGIN
if (USER in ('LDBO')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END LDBO_logon_notifications;
/
------------------------------------Failed Logon-------------
CREATE OR REPLACE TRIGGER failed_logon_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - Failed Login';
l_message VARCHAR2(500);
BEGIN
IF ora_is_servererror( 28000 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END failed_logon_notifications;
-----------------------------------Listener Error-----
CREATE OR REPLACE TRIGGER failed_listener_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'TNS:listener could not hand off client connection';
l_message VARCHAR2(500);
BEGIN
IF ora_is_servererror( 12518 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END failed_logon_notifications;
-----------------------------Trigger Insert--------------------
CREATE OR REPLACE TRIGGER insert_ecn
before insert on ldbo.TBLDIGITALSIGNEDREPORTS
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ECN Process Done Successfully';
l_message VARCHAR2(500);
BEGIN
if (USER in ('ECNUSER')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END insert_ecn;
/
---------------Trigger if login at unofficial time---------
CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS
BEGIN
IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '20:00'
OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours');
END IF;
END secure_dml;
/
CREATE OR REPLACE TRIGGER secure_data
BEFORE INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
secure_dml(:NEW.datecol);
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
------------Trigger To Disallow Entry Of Future Dates ------
CREATE OR REPLACE TRIGGER t_date
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
bad_date EXCEPTION;
BEGIN
IF :new.datecol > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed');
END IF;
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
--------------------Holiday login-------
CREATE TABLE Company_holidays (Day DATE);
CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Non_working_hours EXCEPTION;
BEGIN
/* check for weekends: */
IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
/* check for company holidays:*/
SELECT COUNT(*) INTO Dummy FROM Company_holidays
WHERE TRUNC(Day) = TRUNC(Sysdate);
/* TRUNC gets rid of time parts of dates: */
IF dummy > 0 THEN
RAISE Not_on_holidays;
END IF;
/* Check for work hours (8am to 6pm): */
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Non_working_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'May not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'May not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'May not change '
||'Emp_tab table during non-working hours');
END;
-----------------
SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql
Package created.
Synonym created.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;
Package body created.
No errors.
Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter
SQL> alter system set smtp_out_server = 'mail.uniconindia.in' scope=spfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
Grant EXECUTE privs to user which use utl_mail package.
SQL> grant execute on utl_mail to ldbo;
Grant succeeded.
---------------------------------Database Shutdown Notification-------------------
CREATE OR REPLACE TRIGGER LDBO_shut_notifications
before shutdown on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Problem Alert - LD Database is Down';
l_message VARCHAR2(500);
BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END LDBO_shut_notifications;
/
---------------------------------Database Startup Notification-------------------
CREATE OR REPLACE TRIGGER LDBO_start_notifications
after startup on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Recovery Alert - LD Database is Up';
l_message VARCHAR2(500);
BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END LDBO_start_notifications;
/
------------------------------------SYS User Logon-------------
CREATE OR REPLACE TRIGGER SYS_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - SYS Login';
l_message VARCHAR2(500);
BEGIN
if (USER in ('SYS')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END SYS_logon_notifications;
/
------------------------------------LDBO- DBA User Logon-------------
CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);
BEGIN
if (USER in ('LDBO')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END LDBO_logon_notifications;
/
------------------------------------Failed Logon-------------
CREATE OR REPLACE TRIGGER failed_logon_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - Failed Login';
l_message VARCHAR2(500);
BEGIN
IF ora_is_servererror( 28000 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END failed_logon_notifications;
-----------------------------------Listener Error-----
CREATE OR REPLACE TRIGGER failed_listener_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'TNS:listener could not hand off client connection';
l_message VARCHAR2(500);
BEGIN
IF ora_is_servererror( 12518 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END failed_logon_notifications;
-----------------------------Trigger Insert--------------------
CREATE OR REPLACE TRIGGER insert_ecn
before insert on ldbo.TBLDIGITALSIGNEDREPORTS
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ECN Process Done Successfully';
l_message VARCHAR2(500);
BEGIN
if (USER in ('ECNUSER')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;
l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;
l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;
l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );
EXCEPTION
WHEN others THEN
RAISE;
END;
END IF;
END insert_ecn;
/
---------------Trigger if login at unofficial time---------
CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS
BEGIN
IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '20:00'
OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours');
END IF;
END secure_dml;
/
CREATE OR REPLACE TRIGGER secure_data
BEFORE INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
secure_dml(:NEW.datecol);
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
------------Trigger To Disallow Entry Of Future Dates ------
CREATE OR REPLACE TRIGGER t_date
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
bad_date EXCEPTION;
BEGIN
IF :new.datecol > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed');
END IF;
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
--------------------Holiday login-------
CREATE TABLE Company_holidays (Day DATE);
CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Non_working_hours EXCEPTION;
BEGIN
/* check for weekends: */
IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
/* check for company holidays:*/
SELECT COUNT(*) INTO Dummy FROM Company_holidays
WHERE TRUNC(Day) = TRUNC(Sysdate);
/* TRUNC gets rid of time parts of dates: */
IF dummy > 0 THEN
RAISE Not_on_holidays;
END IF;
/* Check for work hours (8am to 6pm): */
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Non_working_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'May not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'May not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'May not change '
||'Emp_tab table during non-working hours');
END;
-----------------
DPI and security Enable for IE
Do one time setting into one PC
export that setting to IE_Security_Settings.reg and Dpi.reg
copy /Y "%~dp0regdpi.dll" c:\windows\system32\
copy /Y "%~dp0IE_Security_Settings.reg" c:\windows\system32\
copy /Y "%~dp0Dpi.reg" c:\windows\system32\
regedit /S c:\windows\system32\IE_Security_Settings.reg
regedit /S c:\windows\system32\dpi.reg
regsvr32 c:\windows\system32\regdpi.dll
exit
Dpi.reg
-------------
Windows Registry Editor Version 5.00
[HKEY_CURRENT_CONFIG\Software\Fonts]
"FIXEDFON.FON"="8514fix.fon"
"FONTS.FON"="8514sys.fon"
"OEMFONT.FON"="8514oem.fon"
"LogPixels"=dword:00000078
----------------
IE_Security_Settings.reg
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones]
@=""
"SelfHealCount"=dword:00000001
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\0]
@=""
"DisplayName"="My Computer"
"Description"="Your computer"
"Icon"="explorer.exe#0100"
"CurrentLevel"=dword:00000000
"Flags"=dword:00000021
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000001
"1206"=dword:00000000
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000001
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000000
"1806"=dword:00000000
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000003
"1A00"=dword:00000000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000000
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00020000
"1E05"=dword:00030000
"2100"=dword:00000000
"2101"=dword:00000003
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000001
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000003
"2004"=dword:00000003
"PMDisplayName"="My Computer [Protected Mode]"
"LowIcon"="inetcpl.cpl#005422"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000000
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000000
"180C"=dword:00000000
"2301"=dword:00000003
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\1]
@=""
"DisplayName"="Local intranet"
"Description"="This zone contains all Web sites that are on your organization's intranet."
"Icon"="shell32.dll#0018"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00010000
"RecommendedLevel"=dword:00010500
"Flags"=dword:000001db
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000000
"1206"=dword:00000000
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000000
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000000
"1806"=dword:00000000
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000000
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00020000
"1E05"=dword:00030000
"2100"=dword:00000000
"2101"=dword:00000000
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000000
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Local intranet [Protected Mode]"
"LowIcon"="inetcpl.cpl#005423"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000003
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000000
"180C"=dword:00000000
"2301"=dword:00000000
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\2]
@=""
"DisplayName"="Trusted sites"
"Description"="This zone contains Web sites that you trust not to damage your computer or data."
"Icon"="inetcpl.cpl#00004480"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00010000
"RecommendedLevel"=dword:00010000
"Flags"=dword:00000047
"1001"=dword:00000001
"1004"=dword:00000003
"1200"=dword:00000000
"1201"=dword:00000003
"1206"=dword:00000003
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000003
"1407"=dword:00000001
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000003
"1608"=dword:00000000
"1609"=dword:00000001
"1800"=dword:00000001
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000001
"1805"=dword:00000001
"1806"=dword:00000001
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000003
"1A05"=dword:00000001
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00010000
"1E05"=dword:00020000
"2100"=dword:00000000
"2101"=dword:00000001
"2102"=dword:00000003
"2200"=dword:00000003
"2201"=dword:00000003
"2300"=dword:00000001
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Trusted sites [Protected Mode]"
"LowIcon"="inetcpl.cpl#005424"
"1208"=dword:00000000
"1209"=dword:00000003
"120A"=dword:00000003
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000003
"180C"=dword:00000000
"2301"=dword:00000000
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\3]
@=""
"DisplayName"="Internet"
"Description"="This zone contains all Web sites you haven't placed in other zones"
"Icon"="inetcpl.cpl#001313"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00011000
"RecommendedLevel"=dword:00011000
"Flags"=dword:00000001
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000000
"1206"=dword:00000000
"1207"=dword:00000003
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000000
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000001
"1806"=dword:00000001
"1807"=dword:00000001
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000001
"1A06"=dword:00000000
"1A10"=dword:00000001
"1C00"=dword:00010000
"1E05"=dword:00020000
"2100"=dword:00000000
"2101"=dword:00000000
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000000
"2000"=dword:00000000
"{AEBA21FA-782A-4A90-978D-B72164C80120}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,1e,28,1a,04,1b,0c,3b,c2,21,27,53,0d,36,05,2c,05,\
04,3d,4f,3a,4a,44,33,3a,0a,06,12,68,53,7c,20,13,35,5d,4c,10,27,01,56,7a,2d,\
3f,38,4f,79,0f,16,26,75,53,1c,31,00,56,7a,3e,32,24,4f,79,1b,00,33,71,4d,23,\
32,29,7c,6a,35,31,34,40,72,3b,01,2e,5d,4c,2a,07,15,48,72,38,12,00,56,7a,3e,\
16,3c,71,4d,24,33,35,7c,72,35,0e,3c,1a,41,44,19,0f,31,3a,56,7a,2e,3e,31,0c,\
7c,6a,10,27,0c,05,5d,4c,39,19,12,15,61,54,2e,00,33,32,40,52,03,25,1f,05,5d,\
4c,2c,0c,0a,15,61,54,1a,26,1f,05,5d,4c,10,21,1d,1b,71,4d,3b,24,3a,21,6d,72,\
24,16,3c,32,40,72,21,0f,3a,1a,41,44,1b,1e,01,01,71,4d,32,23,30,27,6d,4d,1f,\
28,10,3c,56,7a,2f,2e,32,16,7c,6a,3a,12,3b,28,75,53,0b,3f,12,01,71,4d,23,32,\
29,27,75,53,12,30,32,1e,4f,79,12,38,17,01,71,4d,30,3e,37,27,6d,72,38,12,3f,\
04,41,44,0a,0e,32,28,49,5f,1c,24,0b,1b,36,21,41,7b,5b,24,39,31,7c,6a,2b,0e,\
25,75,53,1a,2e,26,41,72,34,16,26,71,4d,30,30,3a,7c,6a,07,33,1a,56,7a,3a,00,\
33,71,4d,23,32,29,7c,6a,1a,26,1a,40,52,24,3f,1a,6d,4d,1c,22,28,75,53,13,25,\
20,41,44,0a,0e,32,75,53,08,07,20,71,4d,10,27,0d,05,5d,4c,24,1a,1e,1b,71,4d,\
3f,20,3f,21,6d,4d,10,27,0c,05,5d,4c,39,19,12,3a,56,7a,3a,20,2c,0c,7c,6a,3e,\
0c,37,07,75,53,12,30,32,3a,56,7a,25,2d,23,0c,7c,6a,2b,08,21,3a,56,7a,22,3a,\
32,3a,56,72,24,1e,26,1a,41,44,07,1f,03,1b,75,53,1c,31,01,01,71,4d,32,23,30,\
27,6d,72,34,1e,30,04,41,44,1b,1e,3b,28,49,5f,07,33,12,1b,5d,4c,35,0b,0a,1f,\
75,53,0b,00,34,28,40,72,3b,01,2d,04,41,44,01,05,34,28,40,52,22,36,04,34,48,\
72,38,12,3f,04,41,44,0a,0e,1f,01,71,4d,24,33,35,27,06,1c,68,53,49,14,21,01,\
40,52,10,27,0d,40,52,2c,29,05,6d,4d,1f,28,05,56,7a,2f,2e,32,75,53,07,33,12,\
40,52,3f,3a,19,6d,72,20,00,34,71,4d,1a,26,1a,40,52,24,3f,1a,6d,72,35,08,38,\
5d,4c,2d,01,18,48,7a,27,23,1f,56,7a,3b,2f,3f,4f,79,08,39,01,1b,71,72,33,1f,\
39,3a,56,7a,2e,3e,31,0c,7c,72,35,0e,3f,1a,41,44,0a,0a,35,3a,56,7a,3a,20,2c,\
0c,7c,6a,03,25,1f,05,5d,4c,2c,0c,0a,15,61,54,27,05,34,32,40,52,10,21,09,05,\
5d,4c,2d,01,18,15,61,54,07,37,17,05,5d,4c,1c,24,03,1b,71,4d,30,30,3b,27,6d,\
72,33,17,3f,28,40,72,34,1e,30,04,41,44,1b,1e,00,01,71,4d,2f,2c,2c,27,6d,4d,\
0b,26,3f,3c,56,7a,3a,20,23,16,7c,6a,35,05,33,28,75,53,12,30,17,01,71,4d,30,\
3e,37,27,75,53,13,25,20,1e,4f,79,1f,29,1f,01,71,4d,24,33,35,27,06,21,41,7b,\
5b,3d,24,37,7c,6a,2b,0e,25,40,72,33,1f,39,5d,72,34,1e,30,5d,4c,2a,0d,18,48,\
7a,27,12,3b,71,4d,23,32,12,56,72,20,0c,2e,5d,4c,2c,0c,0a,75,53,1a,26,1f,40,\
72,35,08,38,5d,4c,2d,01,18,75,53,0f,21,27,41,44,07,1f,3e,61,54,3d,06,22,32,\
40,52,2c,29,05,32,48,72,34,1e,05,1b,71,4d,10,27,0c,05,5d,4c,39,19,1a,1b,71,\
4d,23,32,24,21,6d,4d,03,25,1f,05,5d,4c,2c,0c,0a,3a,56,7a,25,2d,23,0c,7c,6a,\
2b,08,21,07,75,53,13,25,20,3a,56,7a,3e,3e,3b,0c,7c,6a,3f,0f,23,3a,56,7a,2f,\
2e,3d,3c,56,72,33,1f,39,04,41,44,1a,0e,05,01,75,53,1c,31,00,01,71,4d,2f,2c,\
2c,27,6d,72,20,0c,2d,04,41,44,06,18,2a,28,49,5f,1a,26,1a,1b,5d,4c,2c,0c,0f,\
1f,75,53,1c,1c,3e,28,40,72,38,12,3f,04,41,44,0a,16,3c,28,40,52,3e,39,06,34,\
21,21,41,7b,5b,23,27,3c,7c,6a,17,37,17,40,52,32,24,05,6d,4d,0e,21,2c,75,53,\
0b,31,31,75,53,08,3e,21,41,44,07,1e,3c,61,54,17,37,17,05,5d,4c,00,33,1e,1b,\
71,4d,2e,39,3b,21,6d,72,20,06,32,32,40,72,21,0f,3c,1a,41,44,1a,0e,1f,01,71,\
4d,20,2c,30,27,6d,4d,0e,21,2c,3c,56,7a,3a,2e,2d,16,7c,6a,3f,07,22,28,6e,02,\
68,4a,7c,21,09,26,5d,4c,29,1d,1f,56,7a,3f,32,38,4f,79,1e,30,01,56,7a,3a,2e,\
2d,4f,79,14,07,22,71,4d,24,30,3b,7c,6a,2a,1e,2f,07,75,53,0c,2d,26,3a,56,7a,\
31,25,3d,0c,7c,6a,3e,0e,35,3a,56,7a,3b,2f,3d,3a,56,72,34,1e,26,04,41,44,0b,\
0a,1e,01,75,53,0e,38,01,01,71,4d,23,30,2b,27,6d,72,21,0f,3c,04,28,1b,67,6b,\
5f,00,22,10,75,53,1f,21,27,41,44,0b,0a,31,75,53,0e,1d,22,71,4d,03,27,1d,40,\
52,3e,39,08,75,53,08,31,21,41,44,1a,0e,32,3a,56,7a,3f,32,38,0c,7c,6a,06,3e,\
0d,05,5d,4c,35,0d,09,15,61,54,29,07,22,32,40,52,17,37,17,1b,5d,4c,3a,19,16,\
1f,61,54,06,3e,0d,1b,5d,4c,03,27,11,01,71,4d,24,33,3b,27,06,21,41,73,41,11,\
25,1d,56,7a,2e,3e,3b,4f,79,18,12,3f,71,4d,2e,39,3b,7c,6a,3e,0e,35,40,72,21,\
0f,3c,5d,4c,36,0d,19,48,72,34,1e,1f,1b,71,4d,00,33,16,05,5d,4c,38,04,01,1b,\
71,4d,23,30,2b,21,6d,4d,1c,24,0d,05,5d,4c,29,1d,17,3c,56,7a,3f,32,38,16,7c,\
6a,39,09,25,09,75,53,0b,31,31,3c,56,7a,3b,2f,3d,16,15,39,5f,7b,42,03,38,02,\
40,20,2c,1e,4f,37,41,7b,5b,23,27,3c,7c,14,07,22,6e,14,68,4a,7c,20,13,35,5d,\
30,37,08,06,37,41,7b,5b,23,27,3c,7c,1b,39,1d,30,02,7c,50,68,3a,3b,34,4f,1b,\
1e,3b,6e,14,68,73,41,0b,22,0a,56,12,30,32,28,09,67,73,41,0b,22,2a,41,2c,0c,\
0f,21,37,41,7b,5b,23,27,3c,7c,08,1c,3e,66,0e,44,4f,56,06,13,05,61,27,23,1f,\
4f,3f,5b,53,7c,20,13,35,5d,3e,39,06,06,0a,68,53,7c,21,09,26,5d,32,12,3f,6e,\
14,68,4a,44,3e,37,02,6d,1c,24,01,4f,3f,5b,73,41,08,38,27,41,38,04,19,6e,14,\
68,4a,44,3e,37,02,6d,3e,0e,35,3b,37,41,7b,5b,24,39,31,7c,08,39,00,4f,3f,7c,\
50,68,3b,1d,3c,71,25,2d,2c,20,3a,7c,50,68,3b,25,3b,4f,01,1d,2a,6e,14,68,4a,\
44,3e,37,02,6d,10,21,09,29,1f,5e,45,67,14,30,07,49,12,16,3c,66,0e,44,73,41,\
08,38,27,41,36,0a,1b,21,3f,42,73,41,10,3b,2d,41,00,33,1e,4f,3f,5b,53,5e,2e,\
07,1d,75,21,07,22,66,0e,7c,50,68,23,24,31,4f,0d,15,01,4f,3f,5b,53,5e,2e,07,\
1d,48,0b,18,3c,6e,14,68,4a,44,26,36,0c,6d,2b,06,25,66,37,41,7b,5b,14,21,01,\
40,3a,31,24,15,37,41,7b,5b,3c,3e,3f,7c,12,38,17,4f,3f,5b,53,5e,2e,07,1d,75,\
35,08,38,36,03,56,76,74,37,08,19,40,07,37,17,29,1f,7c,50,68,23,24,31,4f,07,\
1f,3e,16,17,7c,50,68,20,3a,39,75,25,12,3f,66,0e,44,4f,56,1c,12,1d,56,1c,24,\
0d,29,37,41,7b,5b,3d,24,37,7c,1e,1d,22,66,0e,44,4f,56,1c,12,30,61,23,13,11,\
4f,3f,5b,53,5e,2f,01,15,48,10,27,0c,6e,14,68,4a,7c,36,12,38,5d,24,3f,19,6e,\
14,68,4a,44,21,2c,04,6d,35,05,34,66,0e,44,4f,56,1c,12,1d,56,1c,3b,25,28,09,\
67,6b,5f,01,2c,28,75,24,1e,26,36,37,41,7b,5b,3d,24,37,7c,14,3a,0b,30,37,41,\
7b,5b,36,0c,7c
"{A8A88C49-5EB2-4990-A1A2-0876022C854F}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,1e,28,1a,04,1b,0c,3b,c2,21,2d,53,49,07,25,0f,29,\
01,7c,50,68,3a,3b,34,4f,79,08,39,0d,49,72,33,1f,39,5d,4c,17,37,05,56,7a,2f,\
2e,32,4f,79,1f,12,3b,75,53,0b,3f,12,56,7a,3a,20,23,4f,79,12,05,33,71,4d,3a,\
31,29,7c,6a,2b,08,21,40,72,38,12,3f,5d,4c,39,1d,17,48,72,21,0f,03,56,7a,2f,\
06,22,32,40,52,2c,29,05,3a,56,7a,2e,3e,31,0c,7c,6a,2b,06,25,32,40,52,33,24,\
01,32,75,53,0b,3f,32,04,4f,79,1b,3b,1f,0c,40,72,3b,01,2d,1a,75,53,12,30,3f,\
04,4f,79,08,3f,09,0c,75,53,13,25,20,04,75,53,07,37,17,05,5d,4c,36,0a,1b,3a,\
56,72,35,0e,3c,3c,56,7a,2d,3f,38,16,7c,6a,17,37,01,1b,5d,4c,2a,0d,18,1f,61,\
54,12,12,3b,28,40,52,3f,3a,19,34,48,72,20,0c,17,01,71,4d,1a,26,1a,1b,5d,4c,\
2c,0c,17,01,71,4d,30,3e,37,27,6d,4d,1b,3b,0c,1b,5d,4c,39,1d,17,3c,56,7a,3b,\
2f,3f,16,15,39,5f,7b,42,29,1d,3c,71,4d,30,06,22,71,4d,32,23,30,7c,6a,2a,1e,\
19,75,53,1c,31,20,41,72,24,12,3b,71,4d,23,32,24,7c,6a,03,25,17,56,7a,25,05,\
33,71,4d,3a,31,29,7c,6a,10,21,09,40,52,27,2c,0b,6d,4d,0f,28,2a,75,53,08,3e,\
23,41,44,1b,1e,3c,3a,56,7a,12,34,16,05,75,53,1f,21,2d,04,4f,79,10,27,0c,05,\
5d,4c,39,19,12,15,75,53,0b,3f,32,04,4f,79,1b,00,34,32,40,52,24,3f,19,32,48,\
7a,2c,10,17,1b,71,4d,30,1c,3e,32,40,52,27,2c,0b,32,48,7a,27,16,3c,32,40,52,\
3e,07,20,3a,56,7a,2f,2e,3d,16,7c,6a,12,34,1e,01,71,4d,17,37,01,1b,5d,4c,2a,\
0d,18,3c,56,7a,3e,32,24,16,7c,6a,3e,0c,34,09,75,53,0b,3f,3f,1e,4f,79,12,38,\
12,01,71,72,3b,01,2e,3c,56,7a,2f,24,39,16,7c,72,38,12,3f,04,41,44,0a,0e,32,\
3c,56,7a,3b,2f,3f,16,15,39,7c,50,68,23,24,31,4f,79,08,39,0d,49,5f,12,34,16,\
40,52,17,37,01,40,52,22,38,0b,6d,4d,0f,34,1a,56,7a,3a,20,2c,75,53,03,25,1f,\
40,52,24,3f,19,6d,72,3b,05,34,71,4d,10,21,09,40,52,27,2c,0b,6d,72,24,1e,26,\
5d,4c,36,0a,1b,48,7a,36,13,01,1b,71,4d,32,23,30,21,6d,4d,17,37,01,3a,56,7a,\
2f,06,25,32,40,52,33,24,01,3a,56,7a,3a,20,2c,0c,7c,6a,3e,00,34,32,40,52,24,\
3f,19,32,75,53,12,30,3f,04,4f,79,08,3f,09,0c,40,72,38,12,3f,1a,75,53,0f,21,\
27,04,4f,79,14,3a,0b,0c,75,53,1c,31,21,1e,75,53,12,34,16,1b,5d,4c,29,1d,1d,\
3c,56,72,35,0e,3f,3c,56,7a,3e,32,24,16,7c,6a,03,25,1a,1b,5d,4c,35,0b,0f,1f,\
61,54,27,05,33,28,40,52,24,3f,1a,34,48,72,35,08,1d,01,71,4d,1b,3b,0c,1b,5d,\
4c,39,1d,1f,01,71,4d,24,33,35,27,06,1c,7c,50,68,20,3a,39,4f,79,08,06,22,71,\
4d,32,23,30,7c,6a,2a,1e,19,40,72,35,0e,3f,5d,72,24,1a,25,5d,4c,35,0b,0a,48,\
7a,23,00,34,71,4d,3a,31,12,56,72,3b,01,2e,5d,4c,2a,07,15,75,53,1b,3b,0c,40,\
72,24,1e,26,5d,4c,36,0a,1b,75,53,1c,31,21,04,4f,79,0a,2a,06,0c,40,72,34,1e,\
30,1a,41,44,1b,1e,3b,3a,56,7a,07,33,12,05,75,53,0b,3f,32,04,4f,79,03,25,1f,\
05,5d,4c,2c,0c,0a,15,75,53,12,30,3f,04,4f,79,08,1c,3e,32,40,52,27,2c,0b,32,\
48,7a,27,23,1f,1b,71,4d,24,07,20,32,40,52,22,38,08,34,48,7a,34,17,3f,28,40,\
52,23,16,26,3c,56,7a,2f,2e,32,16,7c,6a,07,33,1a,01,71,4d,03,25,1a,1b,5d,4c,\
35,0b,0f,3c,56,7a,25,2d,2c,16,7c,6a,35,31,37,09,75,53,1c,3b,25,1e,4f,79,13,\
35,00,01,71,72,24,1e,26,3c,56,7a,3b,2f,3f,16,15,21,41,7b,5b,23,27,3c,7c,6a,\
2a,16,3c,71,4d,20,2c,30,7c,6a,06,3e,0d,40,52,3f,38,18,6d,4d,08,27,2c,75,53,\
08,31,21,75,53,1f,21,27,04,4f,79,18,2d,06,0c,75,53,0e,38,21,04,75,53,03,27,\
1d,05,5d,4c,36,0a,19,3a,56,72,34,1e,26,3c,56,7a,3f,32,38,16,7c,6a,06,3e,0d,\
1b,5d,4c,35,0d,09,1f,61,54,29,07,22,28,29,01,5e,45,67,14,30,1f,56,7a,17,37,\
17,40,72,25,1a,39,5d,4c,38,04,01,56,7a,3a,2e,2d,4f,79,14,3a,01,56,7a,3b,2e,\
3d,4f,79,0f,16,3c,32,40,52,32,24,05,32,48,7a,18,28,01,1b,71,4d,23,06,32,32,\
40,52,3e,39,08,32,48,7a,37,16,3c,28,40,52,32,12,3f,3c,56,7a,31,25,3d,16,7c,\
6a,03,27,11,01,71,4d,1c,24,0d,1b,36,1d,56,76,74,14,21,01,40,52,23,28,02,6d,\
4d,0c,34,2b,75,53,0e,38,21,41,44,06,1e,2c,75,53,08,07,22,71,4d,1c,27,0d,40,\
52,23,28,02,3a,56,7a,3f,32,38,0c,7c,6a,39,1d,22,32,40,52,3f,38,18,32,75,53,\
08,3e,21,04,4f,79,0f,29,07,02,40,72,25,1a,39,04,75,53,0e,38,21,1e,4f,79,1b,\
39,1d,02,75,53,08,3e,21,1e,6e,02,7c,50,68,20,3a,39,4f,79,0f,16,3c,75,53,0c,\
2d,1e,56,7a,31,25,3d,4f,79,1b,06,32,71,4d,24,33,3b,7c,6a,3f,0e,25,40,72,34,\
1e,26,1a,41,44,0b,0a,31,3a,56,7a,06,3e,0d,05,75,53,0b,31,31,04,4f,79,1c,24,\
0d,05,5d,4c,29,1d,17,1f,75,53,0c,2d,26,1e,4f,79,1e,1d,22,28,40,52,3f,38,18,\
34,48,7a,22,12,01,01,66,1c,44,73,41,0b,22,2a,41,3a,19,16,21,2d,42,73,41,0b,\
22,2a,41,1c,24,01,4f,2d,5b,53,5e,35,1e,22,75,27,1d,22,66,1c,7c,50,68,3a,3b,\
34,4f,06,1e,11,4f,2d,5b,53,5e,35,1e,22,48,1c,18,2d,6e,02,68,4a,44,3f,2d,31,\
6d,35,05,33,66,21,41,7b,5b,03,38,02,40,3a,31,29,15,21,41,7b,5b,23,27,3c,7c,\
08,3f,1d,4f,2d,5b,53,5e,35,1e,22,75,24,1e,26,36,1d,56,76,74,3e,03,1c,40,1c,\
24,0b,29,01,7c,50,68,3b,25,3b,4f,0b,0a,31,16,05,7c,50,68,3b,25,3b,75,21,07,\
22,66,1c,44,4f,56,07,15,1f,56,06,3e,0d,29,21,41,7b,5b,24,39,31,7c,1b,06,32,\
66,1c,44,4f,56,07,15,32,61,36,13,00,4f,2d,5b,53,5e,36,04,17,48,1a,26,1a,6e,\
02,68,4a,7c,21,09,26,5d,24,3f,1a,6e,02,68,4a,44,3e,37,02,6d,2b,1c,3e,66,1c,\
44,4f,56,07,15,1f,56,0f,21,27,28,1b,67,6b,5f,08,21,2a,75,21,0f,3a,36,21,41,\
7b,5b,3c,3e,3f,7c,18,2d,06,30,21,41,7b,5b,3c,3e,05,56,1c,24,0d,29,01,5e,45,\
67,0c,1c,26,75,27,09,3c,6e,02,68,4a,44,26,36,0c,6d,03,27,1d,29,01,5e,45,67,\
0c,3f,31,49,3d,06,25,66,1c,44,4f,56,1f,14,38,75,3b,01,12,4f,2d,5b,73,41,10,\
3b,2d,41,2c,0c,17,4f,2d,5b,53,5e,2e,07,1d,48,10,21,09,29,01,5e,45,67,0c,1c,\
26,71,3e,3e,3b,20,28,74,4e,68,2a,29,05,56,08,3e,23,6e,02,68,4a,44,21,2c,04,\
6d,3b,1a,20,6e,02,68,4a,44,21,1a,3e,75,21,0f,3c,36,1d,56,76,74,15,3b,1d,56,\
0e,38,01,4f,2d,5b,53,5e,2f,01,15,75,20,0e,2c,36,1d,56,76,74,28,02,21,40,10,\
27,0c,29,01,5e,45,67,0d,35,1d,56,12,05,33,66,1c,7c,50,68,20,3a,39,4f,01,05,\
34,66,1c,44,4f,56,1c,12,30,75,35,08,38,36,1d,56,76,74,15,3b,09,40,2f,20,31,\
15,39,5f,7b,42,20,1a,3e,71,3b,2f,03,4f,2d,5b,53,5e,20,39,74
"180D"=dword:00000001
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Internet [Protected Mode]"
"LowIcon"="inetcpl.cpl#005425"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000000
"1408"=dword:00000003
"160A"=dword:00000000
"180A"=dword:00000003
"180C"=dword:00000003
"2301"=dword:00000000
"2103"=dword:00000003
"2104"=dword:00000003
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\4]
@=""
"DisplayName"="Restricted sites"
"Description"="This zone contains Web sites that could potentially damage your computer or data."
"Icon"="inetcpl.cpl#00004481"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00012000
"RecommendedLevel"=dword:00012000
"Flags"=dword:00000003
"1001"=dword:00000003
"1004"=dword:00000003
"1200"=dword:00000003
"1201"=dword:00000003
"1206"=dword:00000003
"1207"=dword:00000003
"1400"=dword:00000003
"1402"=dword:00000003
"1405"=dword:00000003
"1406"=dword:00000003
"1407"=dword:00000003
"1601"=dword:00000001
"1604"=dword:00000001
"1605"=dword:00000000
"1606"=dword:00000003
"1607"=dword:00000003
"1608"=dword:00000003
"1609"=dword:00000001
"1800"=dword:00000003
"1802"=dword:00000001
"1803"=dword:00000003
"1804"=dword:00000003
"1805"=dword:00000001
"1806"=dword:00000003
"1807"=dword:00000001
"1808"=dword:00000000
"1809"=dword:00000000
"180B"=dword:00000001
"1A00"=dword:00010000
"1A02"=dword:00000003
"1A03"=dword:00000003
"1A04"=dword:00000003
"1A05"=dword:00000003
"1A06"=dword:00000003
"1A10"=dword:00000003
"1C00"=dword:00000000
"1E05"=dword:00010000
"2100"=dword:00000003
"2101"=dword:00000003
"2102"=dword:00000003
"2200"=dword:00000003
"2201"=dword:00000003
"2300"=dword:00000003
"2000"=dword:00000003
"{AEBA21FA-782A-4A90-978D-B72164C80120}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,13,37,13,12,14,1a,15,39
"{A8A88C49-5EB2-4990-A1A2-0876022C854F}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,13,37,13,12,14,1a,15,39
"180D"=dword:00000001
"2001"=dword:00000003
"2004"=dword:00000003
"PMDisplayName"="Restricted sites [Protected Mode]"
"LowIcon"="inetcpl.cpl#005426"
"1208"=dword:00000003
"1209"=dword:00000003
"120A"=dword:00000003
"1408"=dword:00000003
"160A"=dword:00000003
"180A"=dword:00000003
"180C"=dword:00000003
"2301"=dword:00000000
"2103"=dword:00000003
"2104"=dword:00000003
"2105"=dword:00000003
"2400"=dword:00000003
"2401"=dword:00000003
"2402"=dword:00000003
"2600"=dword:00000003
--------------------
export that setting to IE_Security_Settings.reg and Dpi.reg
copy /Y "%~dp0regdpi.dll" c:\windows\system32\
copy /Y "%~dp0IE_Security_Settings.reg" c:\windows\system32\
copy /Y "%~dp0Dpi.reg" c:\windows\system32\
regedit /S c:\windows\system32\IE_Security_Settings.reg
regedit /S c:\windows\system32\dpi.reg
regsvr32 c:\windows\system32\regdpi.dll
exit
Dpi.reg
-------------
Windows Registry Editor Version 5.00
[HKEY_CURRENT_CONFIG\Software\Fonts]
"FIXEDFON.FON"="8514fix.fon"
"FONTS.FON"="8514sys.fon"
"OEMFONT.FON"="8514oem.fon"
"LogPixels"=dword:00000078
----------------
IE_Security_Settings.reg
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones]
@=""
"SelfHealCount"=dword:00000001
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\0]
@=""
"DisplayName"="My Computer"
"Description"="Your computer"
"Icon"="explorer.exe#0100"
"CurrentLevel"=dword:00000000
"Flags"=dword:00000021
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000001
"1206"=dword:00000000
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000001
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000000
"1806"=dword:00000000
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000003
"1A00"=dword:00000000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000000
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00020000
"1E05"=dword:00030000
"2100"=dword:00000000
"2101"=dword:00000003
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000001
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000003
"2004"=dword:00000003
"PMDisplayName"="My Computer [Protected Mode]"
"LowIcon"="inetcpl.cpl#005422"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000000
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000000
"180C"=dword:00000000
"2301"=dword:00000003
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\1]
@=""
"DisplayName"="Local intranet"
"Description"="This zone contains all Web sites that are on your organization's intranet."
"Icon"="shell32.dll#0018"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00010000
"RecommendedLevel"=dword:00010500
"Flags"=dword:000001db
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000000
"1206"=dword:00000000
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000000
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000000
"1806"=dword:00000000
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000000
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00020000
"1E05"=dword:00030000
"2100"=dword:00000000
"2101"=dword:00000000
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000000
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Local intranet [Protected Mode]"
"LowIcon"="inetcpl.cpl#005423"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000003
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000000
"180C"=dword:00000000
"2301"=dword:00000000
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\2]
@=""
"DisplayName"="Trusted sites"
"Description"="This zone contains Web sites that you trust not to damage your computer or data."
"Icon"="inetcpl.cpl#00004480"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00010000
"RecommendedLevel"=dword:00010000
"Flags"=dword:00000047
"1001"=dword:00000001
"1004"=dword:00000003
"1200"=dword:00000000
"1201"=dword:00000003
"1206"=dword:00000003
"1207"=dword:00000000
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000003
"1407"=dword:00000001
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000003
"1608"=dword:00000000
"1609"=dword:00000001
"1800"=dword:00000001
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000001
"1805"=dword:00000001
"1806"=dword:00000001
"1807"=dword:00000000
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000003
"1A05"=dword:00000001
"1A06"=dword:00000000
"1A10"=dword:00000000
"1C00"=dword:00010000
"1E05"=dword:00020000
"2100"=dword:00000000
"2101"=dword:00000001
"2102"=dword:00000003
"2200"=dword:00000003
"2201"=dword:00000003
"2300"=dword:00000001
"2000"=dword:00000000
"180D"=dword:00000000
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Trusted sites [Protected Mode]"
"LowIcon"="inetcpl.cpl#005424"
"1208"=dword:00000000
"1209"=dword:00000003
"120A"=dword:00000003
"1408"=dword:00000000
"160A"=dword:00000000
"180A"=dword:00000003
"180C"=dword:00000000
"2301"=dword:00000000
"2103"=dword:00000000
"2104"=dword:00000000
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\3]
@=""
"DisplayName"="Internet"
"Description"="This zone contains all Web sites you haven't placed in other zones"
"Icon"="inetcpl.cpl#001313"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00011000
"RecommendedLevel"=dword:00011000
"Flags"=dword:00000001
"1001"=dword:00000000
"1004"=dword:00000000
"1200"=dword:00000000
"1201"=dword:00000000
"1206"=dword:00000000
"1207"=dword:00000003
"1400"=dword:00000000
"1402"=dword:00000000
"1405"=dword:00000000
"1406"=dword:00000000
"1407"=dword:00000000
"1601"=dword:00000000
"1604"=dword:00000000
"1605"=dword:00000000
"1606"=dword:00000000
"1607"=dword:00000000
"1608"=dword:00000000
"1609"=dword:00000000
"1800"=dword:00000000
"1802"=dword:00000000
"1803"=dword:00000000
"1804"=dword:00000000
"1805"=dword:00000001
"1806"=dword:00000001
"1807"=dword:00000001
"1808"=dword:00000000
"1809"=dword:00000000
"1A00"=dword:00020000
"1A02"=dword:00000000
"1A03"=dword:00000000
"1A04"=dword:00000000
"1A05"=dword:00000001
"1A06"=dword:00000000
"1A10"=dword:00000001
"1C00"=dword:00010000
"1E05"=dword:00020000
"2100"=dword:00000000
"2101"=dword:00000000
"2102"=dword:00000000
"2200"=dword:00000000
"2201"=dword:00000000
"2300"=dword:00000000
"2000"=dword:00000000
"{AEBA21FA-782A-4A90-978D-B72164C80120}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,1e,28,1a,04,1b,0c,3b,c2,21,27,53,0d,36,05,2c,05,\
04,3d,4f,3a,4a,44,33,3a,0a,06,12,68,53,7c,20,13,35,5d,4c,10,27,01,56,7a,2d,\
3f,38,4f,79,0f,16,26,75,53,1c,31,00,56,7a,3e,32,24,4f,79,1b,00,33,71,4d,23,\
32,29,7c,6a,35,31,34,40,72,3b,01,2e,5d,4c,2a,07,15,48,72,38,12,00,56,7a,3e,\
16,3c,71,4d,24,33,35,7c,72,35,0e,3c,1a,41,44,19,0f,31,3a,56,7a,2e,3e,31,0c,\
7c,6a,10,27,0c,05,5d,4c,39,19,12,15,61,54,2e,00,33,32,40,52,03,25,1f,05,5d,\
4c,2c,0c,0a,15,61,54,1a,26,1f,05,5d,4c,10,21,1d,1b,71,4d,3b,24,3a,21,6d,72,\
24,16,3c,32,40,72,21,0f,3a,1a,41,44,1b,1e,01,01,71,4d,32,23,30,27,6d,4d,1f,\
28,10,3c,56,7a,2f,2e,32,16,7c,6a,3a,12,3b,28,75,53,0b,3f,12,01,71,4d,23,32,\
29,27,75,53,12,30,32,1e,4f,79,12,38,17,01,71,4d,30,3e,37,27,6d,72,38,12,3f,\
04,41,44,0a,0e,32,28,49,5f,1c,24,0b,1b,36,21,41,7b,5b,24,39,31,7c,6a,2b,0e,\
25,75,53,1a,2e,26,41,72,34,16,26,71,4d,30,30,3a,7c,6a,07,33,1a,56,7a,3a,00,\
33,71,4d,23,32,29,7c,6a,1a,26,1a,40,52,24,3f,1a,6d,4d,1c,22,28,75,53,13,25,\
20,41,44,0a,0e,32,75,53,08,07,20,71,4d,10,27,0d,05,5d,4c,24,1a,1e,1b,71,4d,\
3f,20,3f,21,6d,4d,10,27,0c,05,5d,4c,39,19,12,3a,56,7a,3a,20,2c,0c,7c,6a,3e,\
0c,37,07,75,53,12,30,32,3a,56,7a,25,2d,23,0c,7c,6a,2b,08,21,3a,56,7a,22,3a,\
32,3a,56,72,24,1e,26,1a,41,44,07,1f,03,1b,75,53,1c,31,01,01,71,4d,32,23,30,\
27,6d,72,34,1e,30,04,41,44,1b,1e,3b,28,49,5f,07,33,12,1b,5d,4c,35,0b,0a,1f,\
75,53,0b,00,34,28,40,72,3b,01,2d,04,41,44,01,05,34,28,40,52,22,36,04,34,48,\
72,38,12,3f,04,41,44,0a,0e,1f,01,71,4d,24,33,35,27,06,1c,68,53,49,14,21,01,\
40,52,10,27,0d,40,52,2c,29,05,6d,4d,1f,28,05,56,7a,2f,2e,32,75,53,07,33,12,\
40,52,3f,3a,19,6d,72,20,00,34,71,4d,1a,26,1a,40,52,24,3f,1a,6d,72,35,08,38,\
5d,4c,2d,01,18,48,7a,27,23,1f,56,7a,3b,2f,3f,4f,79,08,39,01,1b,71,72,33,1f,\
39,3a,56,7a,2e,3e,31,0c,7c,72,35,0e,3f,1a,41,44,0a,0a,35,3a,56,7a,3a,20,2c,\
0c,7c,6a,03,25,1f,05,5d,4c,2c,0c,0a,15,61,54,27,05,34,32,40,52,10,21,09,05,\
5d,4c,2d,01,18,15,61,54,07,37,17,05,5d,4c,1c,24,03,1b,71,4d,30,30,3b,27,6d,\
72,33,17,3f,28,40,72,34,1e,30,04,41,44,1b,1e,00,01,71,4d,2f,2c,2c,27,6d,4d,\
0b,26,3f,3c,56,7a,3a,20,23,16,7c,6a,35,05,33,28,75,53,12,30,17,01,71,4d,30,\
3e,37,27,75,53,13,25,20,1e,4f,79,1f,29,1f,01,71,4d,24,33,35,27,06,21,41,7b,\
5b,3d,24,37,7c,6a,2b,0e,25,40,72,33,1f,39,5d,72,34,1e,30,5d,4c,2a,0d,18,48,\
7a,27,12,3b,71,4d,23,32,12,56,72,20,0c,2e,5d,4c,2c,0c,0a,75,53,1a,26,1f,40,\
72,35,08,38,5d,4c,2d,01,18,75,53,0f,21,27,41,44,07,1f,3e,61,54,3d,06,22,32,\
40,52,2c,29,05,32,48,72,34,1e,05,1b,71,4d,10,27,0c,05,5d,4c,39,19,1a,1b,71,\
4d,23,32,24,21,6d,4d,03,25,1f,05,5d,4c,2c,0c,0a,3a,56,7a,25,2d,23,0c,7c,6a,\
2b,08,21,07,75,53,13,25,20,3a,56,7a,3e,3e,3b,0c,7c,6a,3f,0f,23,3a,56,7a,2f,\
2e,3d,3c,56,72,33,1f,39,04,41,44,1a,0e,05,01,75,53,1c,31,00,01,71,4d,2f,2c,\
2c,27,6d,72,20,0c,2d,04,41,44,06,18,2a,28,49,5f,1a,26,1a,1b,5d,4c,2c,0c,0f,\
1f,75,53,1c,1c,3e,28,40,72,38,12,3f,04,41,44,0a,16,3c,28,40,52,3e,39,06,34,\
21,21,41,7b,5b,23,27,3c,7c,6a,17,37,17,40,52,32,24,05,6d,4d,0e,21,2c,75,53,\
0b,31,31,75,53,08,3e,21,41,44,07,1e,3c,61,54,17,37,17,05,5d,4c,00,33,1e,1b,\
71,4d,2e,39,3b,21,6d,72,20,06,32,32,40,72,21,0f,3c,1a,41,44,1a,0e,1f,01,71,\
4d,20,2c,30,27,6d,4d,0e,21,2c,3c,56,7a,3a,2e,2d,16,7c,6a,3f,07,22,28,6e,02,\
68,4a,7c,21,09,26,5d,4c,29,1d,1f,56,7a,3f,32,38,4f,79,1e,30,01,56,7a,3a,2e,\
2d,4f,79,14,07,22,71,4d,24,30,3b,7c,6a,2a,1e,2f,07,75,53,0c,2d,26,3a,56,7a,\
31,25,3d,0c,7c,6a,3e,0e,35,3a,56,7a,3b,2f,3d,3a,56,72,34,1e,26,04,41,44,0b,\
0a,1e,01,75,53,0e,38,01,01,71,4d,23,30,2b,27,6d,72,21,0f,3c,04,28,1b,67,6b,\
5f,00,22,10,75,53,1f,21,27,41,44,0b,0a,31,75,53,0e,1d,22,71,4d,03,27,1d,40,\
52,3e,39,08,75,53,08,31,21,41,44,1a,0e,32,3a,56,7a,3f,32,38,0c,7c,6a,06,3e,\
0d,05,5d,4c,35,0d,09,15,61,54,29,07,22,32,40,52,17,37,17,1b,5d,4c,3a,19,16,\
1f,61,54,06,3e,0d,1b,5d,4c,03,27,11,01,71,4d,24,33,3b,27,06,21,41,73,41,11,\
25,1d,56,7a,2e,3e,3b,4f,79,18,12,3f,71,4d,2e,39,3b,7c,6a,3e,0e,35,40,72,21,\
0f,3c,5d,4c,36,0d,19,48,72,34,1e,1f,1b,71,4d,00,33,16,05,5d,4c,38,04,01,1b,\
71,4d,23,30,2b,21,6d,4d,1c,24,0d,05,5d,4c,29,1d,17,3c,56,7a,3f,32,38,16,7c,\
6a,39,09,25,09,75,53,0b,31,31,3c,56,7a,3b,2f,3d,16,15,39,5f,7b,42,03,38,02,\
40,20,2c,1e,4f,37,41,7b,5b,23,27,3c,7c,14,07,22,6e,14,68,4a,7c,20,13,35,5d,\
30,37,08,06,37,41,7b,5b,23,27,3c,7c,1b,39,1d,30,02,7c,50,68,3a,3b,34,4f,1b,\
1e,3b,6e,14,68,73,41,0b,22,0a,56,12,30,32,28,09,67,73,41,0b,22,2a,41,2c,0c,\
0f,21,37,41,7b,5b,23,27,3c,7c,08,1c,3e,66,0e,44,4f,56,06,13,05,61,27,23,1f,\
4f,3f,5b,53,7c,20,13,35,5d,3e,39,06,06,0a,68,53,7c,21,09,26,5d,32,12,3f,6e,\
14,68,4a,44,3e,37,02,6d,1c,24,01,4f,3f,5b,73,41,08,38,27,41,38,04,19,6e,14,\
68,4a,44,3e,37,02,6d,3e,0e,35,3b,37,41,7b,5b,24,39,31,7c,08,39,00,4f,3f,7c,\
50,68,3b,1d,3c,71,25,2d,2c,20,3a,7c,50,68,3b,25,3b,4f,01,1d,2a,6e,14,68,4a,\
44,3e,37,02,6d,10,21,09,29,1f,5e,45,67,14,30,07,49,12,16,3c,66,0e,44,73,41,\
08,38,27,41,36,0a,1b,21,3f,42,73,41,10,3b,2d,41,00,33,1e,4f,3f,5b,53,5e,2e,\
07,1d,75,21,07,22,66,0e,7c,50,68,23,24,31,4f,0d,15,01,4f,3f,5b,53,5e,2e,07,\
1d,48,0b,18,3c,6e,14,68,4a,44,26,36,0c,6d,2b,06,25,66,37,41,7b,5b,14,21,01,\
40,3a,31,24,15,37,41,7b,5b,3c,3e,3f,7c,12,38,17,4f,3f,5b,53,5e,2e,07,1d,75,\
35,08,38,36,03,56,76,74,37,08,19,40,07,37,17,29,1f,7c,50,68,23,24,31,4f,07,\
1f,3e,16,17,7c,50,68,20,3a,39,75,25,12,3f,66,0e,44,4f,56,1c,12,1d,56,1c,24,\
0d,29,37,41,7b,5b,3d,24,37,7c,1e,1d,22,66,0e,44,4f,56,1c,12,30,61,23,13,11,\
4f,3f,5b,53,5e,2f,01,15,48,10,27,0c,6e,14,68,4a,7c,36,12,38,5d,24,3f,19,6e,\
14,68,4a,44,21,2c,04,6d,35,05,34,66,0e,44,4f,56,1c,12,1d,56,1c,3b,25,28,09,\
67,6b,5f,01,2c,28,75,24,1e,26,36,37,41,7b,5b,3d,24,37,7c,14,3a,0b,30,37,41,\
7b,5b,36,0c,7c
"{A8A88C49-5EB2-4990-A1A2-0876022C854F}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,1e,28,1a,04,1b,0c,3b,c2,21,2d,53,49,07,25,0f,29,\
01,7c,50,68,3a,3b,34,4f,79,08,39,0d,49,72,33,1f,39,5d,4c,17,37,05,56,7a,2f,\
2e,32,4f,79,1f,12,3b,75,53,0b,3f,12,56,7a,3a,20,23,4f,79,12,05,33,71,4d,3a,\
31,29,7c,6a,2b,08,21,40,72,38,12,3f,5d,4c,39,1d,17,48,72,21,0f,03,56,7a,2f,\
06,22,32,40,52,2c,29,05,3a,56,7a,2e,3e,31,0c,7c,6a,2b,06,25,32,40,52,33,24,\
01,32,75,53,0b,3f,32,04,4f,79,1b,3b,1f,0c,40,72,3b,01,2d,1a,75,53,12,30,3f,\
04,4f,79,08,3f,09,0c,75,53,13,25,20,04,75,53,07,37,17,05,5d,4c,36,0a,1b,3a,\
56,72,35,0e,3c,3c,56,7a,2d,3f,38,16,7c,6a,17,37,01,1b,5d,4c,2a,0d,18,1f,61,\
54,12,12,3b,28,40,52,3f,3a,19,34,48,72,20,0c,17,01,71,4d,1a,26,1a,1b,5d,4c,\
2c,0c,17,01,71,4d,30,3e,37,27,6d,4d,1b,3b,0c,1b,5d,4c,39,1d,17,3c,56,7a,3b,\
2f,3f,16,15,39,5f,7b,42,29,1d,3c,71,4d,30,06,22,71,4d,32,23,30,7c,6a,2a,1e,\
19,75,53,1c,31,20,41,72,24,12,3b,71,4d,23,32,24,7c,6a,03,25,17,56,7a,25,05,\
33,71,4d,3a,31,29,7c,6a,10,21,09,40,52,27,2c,0b,6d,4d,0f,28,2a,75,53,08,3e,\
23,41,44,1b,1e,3c,3a,56,7a,12,34,16,05,75,53,1f,21,2d,04,4f,79,10,27,0c,05,\
5d,4c,39,19,12,15,75,53,0b,3f,32,04,4f,79,1b,00,34,32,40,52,24,3f,19,32,48,\
7a,2c,10,17,1b,71,4d,30,1c,3e,32,40,52,27,2c,0b,32,48,7a,27,16,3c,32,40,52,\
3e,07,20,3a,56,7a,2f,2e,3d,16,7c,6a,12,34,1e,01,71,4d,17,37,01,1b,5d,4c,2a,\
0d,18,3c,56,7a,3e,32,24,16,7c,6a,3e,0c,34,09,75,53,0b,3f,3f,1e,4f,79,12,38,\
12,01,71,72,3b,01,2e,3c,56,7a,2f,24,39,16,7c,72,38,12,3f,04,41,44,0a,0e,32,\
3c,56,7a,3b,2f,3f,16,15,39,7c,50,68,23,24,31,4f,79,08,39,0d,49,5f,12,34,16,\
40,52,17,37,01,40,52,22,38,0b,6d,4d,0f,34,1a,56,7a,3a,20,2c,75,53,03,25,1f,\
40,52,24,3f,19,6d,72,3b,05,34,71,4d,10,21,09,40,52,27,2c,0b,6d,72,24,1e,26,\
5d,4c,36,0a,1b,48,7a,36,13,01,1b,71,4d,32,23,30,21,6d,4d,17,37,01,3a,56,7a,\
2f,06,25,32,40,52,33,24,01,3a,56,7a,3a,20,2c,0c,7c,6a,3e,00,34,32,40,52,24,\
3f,19,32,75,53,12,30,3f,04,4f,79,08,3f,09,0c,40,72,38,12,3f,1a,75,53,0f,21,\
27,04,4f,79,14,3a,0b,0c,75,53,1c,31,21,1e,75,53,12,34,16,1b,5d,4c,29,1d,1d,\
3c,56,72,35,0e,3f,3c,56,7a,3e,32,24,16,7c,6a,03,25,1a,1b,5d,4c,35,0b,0f,1f,\
61,54,27,05,33,28,40,52,24,3f,1a,34,48,72,35,08,1d,01,71,4d,1b,3b,0c,1b,5d,\
4c,39,1d,1f,01,71,4d,24,33,35,27,06,1c,7c,50,68,20,3a,39,4f,79,08,06,22,71,\
4d,32,23,30,7c,6a,2a,1e,19,40,72,35,0e,3f,5d,72,24,1a,25,5d,4c,35,0b,0a,48,\
7a,23,00,34,71,4d,3a,31,12,56,72,3b,01,2e,5d,4c,2a,07,15,75,53,1b,3b,0c,40,\
72,24,1e,26,5d,4c,36,0a,1b,75,53,1c,31,21,04,4f,79,0a,2a,06,0c,40,72,34,1e,\
30,1a,41,44,1b,1e,3b,3a,56,7a,07,33,12,05,75,53,0b,3f,32,04,4f,79,03,25,1f,\
05,5d,4c,2c,0c,0a,15,75,53,12,30,3f,04,4f,79,08,1c,3e,32,40,52,27,2c,0b,32,\
48,7a,27,23,1f,1b,71,4d,24,07,20,32,40,52,22,38,08,34,48,7a,34,17,3f,28,40,\
52,23,16,26,3c,56,7a,2f,2e,32,16,7c,6a,07,33,1a,01,71,4d,03,25,1a,1b,5d,4c,\
35,0b,0f,3c,56,7a,25,2d,2c,16,7c,6a,35,31,37,09,75,53,1c,3b,25,1e,4f,79,13,\
35,00,01,71,72,24,1e,26,3c,56,7a,3b,2f,3f,16,15,21,41,7b,5b,23,27,3c,7c,6a,\
2a,16,3c,71,4d,20,2c,30,7c,6a,06,3e,0d,40,52,3f,38,18,6d,4d,08,27,2c,75,53,\
08,31,21,75,53,1f,21,27,04,4f,79,18,2d,06,0c,75,53,0e,38,21,04,75,53,03,27,\
1d,05,5d,4c,36,0a,19,3a,56,72,34,1e,26,3c,56,7a,3f,32,38,16,7c,6a,06,3e,0d,\
1b,5d,4c,35,0d,09,1f,61,54,29,07,22,28,29,01,5e,45,67,14,30,1f,56,7a,17,37,\
17,40,72,25,1a,39,5d,4c,38,04,01,56,7a,3a,2e,2d,4f,79,14,3a,01,56,7a,3b,2e,\
3d,4f,79,0f,16,3c,32,40,52,32,24,05,32,48,7a,18,28,01,1b,71,4d,23,06,32,32,\
40,52,3e,39,08,32,48,7a,37,16,3c,28,40,52,32,12,3f,3c,56,7a,31,25,3d,16,7c,\
6a,03,27,11,01,71,4d,1c,24,0d,1b,36,1d,56,76,74,14,21,01,40,52,23,28,02,6d,\
4d,0c,34,2b,75,53,0e,38,21,41,44,06,1e,2c,75,53,08,07,22,71,4d,1c,27,0d,40,\
52,23,28,02,3a,56,7a,3f,32,38,0c,7c,6a,39,1d,22,32,40,52,3f,38,18,32,75,53,\
08,3e,21,04,4f,79,0f,29,07,02,40,72,25,1a,39,04,75,53,0e,38,21,1e,4f,79,1b,\
39,1d,02,75,53,08,3e,21,1e,6e,02,7c,50,68,20,3a,39,4f,79,0f,16,3c,75,53,0c,\
2d,1e,56,7a,31,25,3d,4f,79,1b,06,32,71,4d,24,33,3b,7c,6a,3f,0e,25,40,72,34,\
1e,26,1a,41,44,0b,0a,31,3a,56,7a,06,3e,0d,05,75,53,0b,31,31,04,4f,79,1c,24,\
0d,05,5d,4c,29,1d,17,1f,75,53,0c,2d,26,1e,4f,79,1e,1d,22,28,40,52,3f,38,18,\
34,48,7a,22,12,01,01,66,1c,44,73,41,0b,22,2a,41,3a,19,16,21,2d,42,73,41,0b,\
22,2a,41,1c,24,01,4f,2d,5b,53,5e,35,1e,22,75,27,1d,22,66,1c,7c,50,68,3a,3b,\
34,4f,06,1e,11,4f,2d,5b,53,5e,35,1e,22,48,1c,18,2d,6e,02,68,4a,44,3f,2d,31,\
6d,35,05,33,66,21,41,7b,5b,03,38,02,40,3a,31,29,15,21,41,7b,5b,23,27,3c,7c,\
08,3f,1d,4f,2d,5b,53,5e,35,1e,22,75,24,1e,26,36,1d,56,76,74,3e,03,1c,40,1c,\
24,0b,29,01,7c,50,68,3b,25,3b,4f,0b,0a,31,16,05,7c,50,68,3b,25,3b,75,21,07,\
22,66,1c,44,4f,56,07,15,1f,56,06,3e,0d,29,21,41,7b,5b,24,39,31,7c,1b,06,32,\
66,1c,44,4f,56,07,15,32,61,36,13,00,4f,2d,5b,53,5e,36,04,17,48,1a,26,1a,6e,\
02,68,4a,7c,21,09,26,5d,24,3f,1a,6e,02,68,4a,44,3e,37,02,6d,2b,1c,3e,66,1c,\
44,4f,56,07,15,1f,56,0f,21,27,28,1b,67,6b,5f,08,21,2a,75,21,0f,3a,36,21,41,\
7b,5b,3c,3e,3f,7c,18,2d,06,30,21,41,7b,5b,3c,3e,05,56,1c,24,0d,29,01,5e,45,\
67,0c,1c,26,75,27,09,3c,6e,02,68,4a,44,26,36,0c,6d,03,27,1d,29,01,5e,45,67,\
0c,3f,31,49,3d,06,25,66,1c,44,4f,56,1f,14,38,75,3b,01,12,4f,2d,5b,73,41,10,\
3b,2d,41,2c,0c,17,4f,2d,5b,53,5e,2e,07,1d,48,10,21,09,29,01,5e,45,67,0c,1c,\
26,71,3e,3e,3b,20,28,74,4e,68,2a,29,05,56,08,3e,23,6e,02,68,4a,44,21,2c,04,\
6d,3b,1a,20,6e,02,68,4a,44,21,1a,3e,75,21,0f,3c,36,1d,56,76,74,15,3b,1d,56,\
0e,38,01,4f,2d,5b,53,5e,2f,01,15,75,20,0e,2c,36,1d,56,76,74,28,02,21,40,10,\
27,0c,29,01,5e,45,67,0d,35,1d,56,12,05,33,66,1c,7c,50,68,20,3a,39,4f,01,05,\
34,66,1c,44,4f,56,1c,12,30,75,35,08,38,36,1d,56,76,74,15,3b,09,40,2f,20,31,\
15,39,5f,7b,42,20,1a,3e,71,3b,2f,03,4f,2d,5b,53,5e,20,39,74
"180D"=dword:00000001
"2001"=dword:00000000
"2004"=dword:00000000
"PMDisplayName"="Internet [Protected Mode]"
"LowIcon"="inetcpl.cpl#005425"
"1208"=dword:00000000
"1209"=dword:00000000
"120A"=dword:00000000
"1408"=dword:00000003
"160A"=dword:00000000
"180A"=dword:00000003
"180C"=dword:00000003
"2301"=dword:00000000
"2103"=dword:00000003
"2104"=dword:00000003
"2105"=dword:00000000
"2400"=dword:00000000
"2401"=dword:00000000
"2402"=dword:00000000
"2600"=dword:00000000
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\4]
@=""
"DisplayName"="Restricted sites"
"Description"="This zone contains Web sites that could potentially damage your computer or data."
"Icon"="inetcpl.cpl#00004481"
"CurrentLevel"=dword:00000000
"MinLevel"=dword:00012000
"RecommendedLevel"=dword:00012000
"Flags"=dword:00000003
"1001"=dword:00000003
"1004"=dword:00000003
"1200"=dword:00000003
"1201"=dword:00000003
"1206"=dword:00000003
"1207"=dword:00000003
"1400"=dword:00000003
"1402"=dword:00000003
"1405"=dword:00000003
"1406"=dword:00000003
"1407"=dword:00000003
"1601"=dword:00000001
"1604"=dword:00000001
"1605"=dword:00000000
"1606"=dword:00000003
"1607"=dword:00000003
"1608"=dword:00000003
"1609"=dword:00000001
"1800"=dword:00000003
"1802"=dword:00000001
"1803"=dword:00000003
"1804"=dword:00000003
"1805"=dword:00000001
"1806"=dword:00000003
"1807"=dword:00000001
"1808"=dword:00000000
"1809"=dword:00000000
"180B"=dword:00000001
"1A00"=dword:00010000
"1A02"=dword:00000003
"1A03"=dword:00000003
"1A04"=dword:00000003
"1A05"=dword:00000003
"1A06"=dword:00000003
"1A10"=dword:00000003
"1C00"=dword:00000000
"1E05"=dword:00010000
"2100"=dword:00000003
"2101"=dword:00000003
"2102"=dword:00000003
"2200"=dword:00000003
"2201"=dword:00000003
"2300"=dword:00000003
"2000"=dword:00000003
"{AEBA21FA-782A-4A90-978D-B72164C80120}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,13,37,13,12,14,1a,15,39
"{A8A88C49-5EB2-4990-A1A2-0876022C854F}"=hex:1a,37,61,59,23,52,35,0c,7a,5f,20,\
17,2f,1e,1a,19,0e,2b,01,73,13,37,13,12,14,1a,15,39
"180D"=dword:00000001
"2001"=dword:00000003
"2004"=dword:00000003
"PMDisplayName"="Restricted sites [Protected Mode]"
"LowIcon"="inetcpl.cpl#005426"
"1208"=dword:00000003
"1209"=dword:00000003
"120A"=dword:00000003
"1408"=dword:00000003
"160A"=dword:00000003
"180A"=dword:00000003
"180C"=dword:00000003
"2301"=dword:00000000
"2103"=dword:00000003
"2104"=dword:00000003
"2105"=dword:00000003
"2400"=dword:00000003
"2401"=dword:00000003
"2402"=dword:00000003
"2600"=dword:00000003
--------------------
Friday, August 27, 2010
Delete file from date
forfiles only work in windows 2000 /2003/ vista
Folder Location C:\Test\arch
file older than 5 days
*.* all file extension (*.txt, ......)
----------------
forfiles /p C:\Test\arch /s /m *.* /d -5 /c "cmd /c echo @path"
forfiles /p C:\Test\arch /s /m *.* /d -5 /c "cmd /c del @path"
FORFILES [/P pathname] [/M searchmask] [/S] [/C command] [/D [+ | -] {MM/dd/yyyy | dd}]
----------
/p pathname Indicates the path to start searching. The default folder is the current working directory (.).
/m search mask Searches files according to a searchmask. The default searchmask is '*' .
/s Instructs forfiles to recurse into subdirectories. Like "DIR /S".
/C Indicates the command to execute for each file. Command strings should be wrapped in double quotes.
The default command is "cmd /c echo @file".
The following variables can be used in the command string:
@file - returns the name of the file. @fname - returns the file name without extension.
@ext - returns only the extension of the file.
@path - returns the full path of the file.
@relpath - returns the relative path of the file.
@isdir - returns "TRUE" if a file type is a directory, and "FALSE" for files.
@fsize - returns the size of the file in bytes.
@fdate - returns the last modified date of the file.
@ftime - returns the last modified time of the file.
/D date Selects files with a last modified date greater than or equal to (+), or less than or equal to (-), the specified date using the "MM/dd/yyyy" format; or selects files with a last modified date greater than or equal to (+) the current date plus "dd" days, or less than or equal to (-) the current date minus "dd" days. A valid "dd" number of days can be any number in the range of 0 - 32768. "+" is taken as default sign if not specified.
Examples: FORFILES /?
FORFILES
FORFILES /P C:\WINDOWS /S /M DNS*.*
FORFILES /S /M *.txt /C "cmd /c type @file | more"
FORFILES /P C:\ /S /M *.bat
FORFILES /D -30 /M *.exe /C "cmd /c echo @path 0x09 was changed 30 days ago"
FORFILES /D 01/01/2001 /C "cmd /c echo @fname is new since Jan 1st 2001"
FORFILES /D +8/19/2005 /C "cmd /c echo @fname is new today"
FORFILES /M *.exe /D +1
FORFILES /S /M *.doc /C "cmd /c echo @fsize"
FORFILES /M *.txt /C "cmd /c if @isdir==FALSE notepad.exe @file"
Folder Location C:\Test\arch
file older than 5 days
*.* all file extension (*.txt, ......)
----------------
forfiles /p C:\Test\arch /s /m *.* /d -5 /c "cmd /c echo @path"
forfiles /p C:\Test\arch /s /m *.* /d -5 /c "cmd /c del @path"
FORFILES [/P pathname] [/M searchmask] [/S] [/C command] [/D [+ | -] {MM/dd/yyyy | dd}]
----------
/p pathname Indicates the path to start searching. The default folder is the current working directory (.).
/m search mask Searches files according to a searchmask. The default searchmask is '*' .
/s Instructs forfiles to recurse into subdirectories. Like "DIR /S".
/C Indicates the command to execute for each file. Command strings should be wrapped in double quotes.
The default command is "cmd /c echo @file".
The following variables can be used in the command string:
@file - returns the name of the file. @fname - returns the file name without extension.
@ext - returns only the extension of the file.
@path - returns the full path of the file.
@relpath - returns the relative path of the file.
@isdir - returns "TRUE" if a file type is a directory, and "FALSE" for files.
@fsize - returns the size of the file in bytes.
@fdate - returns the last modified date of the file.
@ftime - returns the last modified time of the file.
/D date Selects files with a last modified date greater than or equal to (+), or less than or equal to (-), the specified date using the "MM/dd/yyyy" format; or selects files with a last modified date greater than or equal to (+) the current date plus "dd" days, or less than or equal to (-) the current date minus "dd" days. A valid "dd" number of days can be any number in the range of 0 - 32768. "+" is taken as default sign if not specified.
Examples: FORFILES /?
FORFILES
FORFILES /P C:\WINDOWS /S /M DNS*.*
FORFILES /S /M *.txt /C "cmd /c type @file | more"
FORFILES /P C:\ /S /M *.bat
FORFILES /D -30 /M *.exe /C "cmd /c echo @path 0x09 was changed 30 days ago"
FORFILES /D 01/01/2001 /C "cmd /c echo @fname is new since Jan 1st 2001"
FORFILES /D +8/19/2005 /C "cmd /c echo @fname is new today"
FORFILES /M *.exe /D +1
FORFILES /S /M *.doc /C "cmd /c echo @fsize"
FORFILES /M *.txt /C "cmd /c if @isdir==FALSE notepad.exe @file"
Thursday, August 12, 2010
Oracle Streams Replication
Set up below parameters on both databases (db1, db2)
1. Enable ARCHIVELOG MODE on both database
2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';
Database link created.
Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
Schema: ldbo
Table: ksh
NOTE: Unlock ldbo schema because in 10g ldbo schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user ldbo account unlock identified by ldbo;
User altered.
SQL> conn ldbo/ldbo@db1
Connected.
SQL> create table ksh ( no number primary key,name varchar2(20),ddate date);
Table created.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user ldbo account unlock identified by ldbo;
User altered.
SQL> conn ldbo/ldbo@db2
Connected.
SQL> create table ksh ( no number primary key,name varchar2(20),ddate date);
Table created.
7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn ldbo/ldbo@db1
Connected.
SQL> alter table ksh
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'ldbo.ksh',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'ldbo.ksh',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'ldbo.ksh',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'ldbo.ksh',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn ldbo/ldbo@db1
Connected.
SQL> --DDL operation
SQL> alter table ksh add (flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into ksh values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> conn ldbo/ldbo@db2
Connected.
SQL> --TEST DDL operation
SQL> desc ksh
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)
SQL> --TEST DML operation
SQL> select * from ksh;
NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 10-AUG-10 1
1. Enable ARCHIVELOG MODE on both database
2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';
Database link created.
Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
Schema: ldbo
Table: ksh
NOTE: Unlock ldbo schema because in 10g ldbo schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user ldbo account unlock identified by ldbo;
User altered.
SQL> conn ldbo/ldbo@db1
Connected.
SQL> create table ksh ( no number primary key,name varchar2(20),ddate date);
Table created.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user ldbo account unlock identified by ldbo;
User altered.
SQL> conn ldbo/ldbo@db2
Connected.
SQL> create table ksh ( no number primary key,name varchar2(20),ddate date);
Table created.
7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn ldbo/ldbo@db1
Connected.
SQL> alter table ksh
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'ldbo.ksh',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'ldbo.ksh',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'ldbo.ksh',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'ldbo.ksh',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn ldbo/ldbo@db1
Connected.
SQL> --DDL operation
SQL> alter table ksh add (flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into ksh values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> conn ldbo/ldbo@db2
Connected.
SQL> --TEST DDL operation
SQL> desc ksh
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)
SQL> --TEST DML operation
SQL> select * from ksh;
NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 10-AUG-10 1
Subscribe to:
Posts (Atom)