Long Operation Process

COL MINUTES FORMAT 999,990.99
COL HOURS FORMAT 999,990.99
COL MESSAGE FORMAT A22
COL OPNAME FORMAT A20 HEA "O P E R A T I O N"
COL PCT_COMPLETE FORMAT A04 HEA "PCT |DONE" TRUNC JUST RIGHT
COL SERIAL# FORMAT 99999
COL SID FORMAT 9999999
COL STARTED FORMAT A20 HEA "S T A R T T I M E"
COL TARGET FORMAT A16
COL TARGET_DESC FORMAT A16
COL TIME_REMAINING FORMAT 99,999,999
COL UNITS FORMAT A10
COL USERNAME FORMAT A08
SET ECHO OFF
SET ECHO ON
SELECT SID
,SERIAL#
,USERNAME
,OPNAME
,TO_CHAR(START_TIME,'DD-MON-YYYY HH24 MI SS') STARTED
,SOFAR/TOTALWORK*100||'%' PCT_COMPLETE
,ELAPSED_SECONDS/60 MINUTES
,TOTALWORK
,UNITS
,TIME_REMAINING/60 REMAINING
FROM V$SESSION_LONGOPS
WHERE USERNAME != 'PATROL'
AND USERNAME != 'SYS'
and sofar != totalwork
ORDER BY ELAPSED_SECONDS DESC
;

How to Trace Oracle Sessions

How-to trace oracle sessions?


There are lots of methods to trace oracle sessions.

1. alter session set events ‘10046 trace name context forever,level 12?;

alter session set tracefile_identifier=’10046?;
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

Start trace:

alter session set events ‘10046 trace name context forever,level 12?;

Stop trace:

alter session set events ‘10046 trace name context off’;

Note: level can be 4,8,12, only trace your own session

2. Using package DBMS_MONITOR

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,waits => TRUE, binds => TRUE);

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);

Trace a module:

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
service_name=>'vasont.world', module_name=>'VasontU.exe',
action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE,
binds=>TRUE,instance_name=>NULL);

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
service_name=>'vasont.world',module_name=>'VasontU.exe');

Note: not available before 10g, can trace any session


3. Using package DBMS_SESSION

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE,
binds => TRUE);

EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();

Note: only trace your own session


4. Using package DBMS_SYSTEM

execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
serial#=>4957,sql_trace=>TRUE);

execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507,
serial#=>4957,sql_trace=>FALSE);

Note: available in 8i/9i/10g, can trace any session, package is wrapped.


5. oradebug

Trace session sid=58;select p.PID,p.SPID from v$process p,v$session s where s.paddr = p.addr and s.sid = 58;

PID SPID
———- ———
32 12943
connect / as sysdba
oradebug setospid 12943 //or oradebug setorapid 32
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

Stop trace:
oradebug event 10046 trace name context off
Note: available in 8i/9i/10g, can trace any session


6. Using DBMS_SUPPORT

exec DBMS_SUPPORT.START_TRACE_IN_SESSION(&SID,
waits=>true, binds=>true );

exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null );

NOTE: need to install before use

SQL> connect / AS SYSDBA
SQL> @?\rdbms\admin\dbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;


7. Using trcsess

trcsess [output=output_file_name]
[session=session_id]
[clientid=client_id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]

Trace a module:
trcsess service=vasont.world module=VasontU.exe trc.log


8. Tracing whole system

alter system set events '10046 trace name context forever,level 12';
or
event="10046 trace name context forever,level 12"

Stop system wider trace:

alter system set events '10046 trace name context off';

9. Using trigger to start traces

There may be some situations where it is necessary to trace
the activity of a specific user. In this case a logon trigger
could be used.

An example is provided below:

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace

name context forever, level 12''';
END set_trace;
/



Last step, after trace processing, using tkprof

tkprof vasont_ora_22103.trc vasont_ora_22103.trc.log sys=no

waits=yes sort=(prscnt, execnt)

How To Open The Standby Database When The Primary Is Lost

Startup Mount:

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 4 19:51:12 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> STARTUP MOUNT

ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1260984 bytes
Variable Size 184549960 bytes
Database Buffers 29360128 bytes
Redo Buffers 2932736 bytes
Database mounted.


Check Status:

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY


Recover if you have logs to apply:

In this example the primary is lost and I don't have more archived logs to apply:
SQL> RECOVER STANDBY DATABASE;

ORA-00279: change 794348 generated at 12/29/2008 12:23:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf
ORA-00280: change 794348 for thread 1 is in sequence #49
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> !ls -l /u01/app/oracle/oradata/dgfdb/archive/
total 31072
-rw-r----- 1 oracle oinstall 1158656 Dec 29 11:31 1_37_633452428.dbf
-rw-r----- 1 oracle oinstall 7385600 Dec 29 11:31 1_38_633452428.dbf
-rw-r----- 1 oracle oinstall 4941824 Dec 29 11:31 1_39_633452428.dbf
-rw-r----- 1 oracle oinstall 13739008 Dec 29 11:31 1_40_633452428.dbf
-rw-r----- 1 oracle oinstall 2272256 Dec 29 11:50 1_41_633452428.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 29 11:51 1_42_633452428.dbf
-rw-r----- 1 oracle oinstall 89088 Dec 29 11:51 1_43_633452428.dbf
-rw-r----- 1 oracle oinstall 1847296 Dec 29 12:18 1_44_633452428.dbf
-rw-r----- 1 oracle oinstall 135680 Dec 29 12:18 1_45_633452428.dbf
-rw-r----- 1 oracle oinstall 67584 Dec 29 12:19 1_46_633452428.dbf
-rw-r----- 1 oracle oinstall 34816 Dec 29 12:22 1_47_633452428.dbf
-rw-r----- 1 oracle oinstall 2048 Dec 29 12:22 1_48_633452428.dbf


Finish the Recovery process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.


Activate the Standby Database:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.


Check the new status:

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE PRIMARY


Open the Database:

SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE
---------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE PRIMARY

How to Install Oracle Client 10g on Windows 7

Checking operating system requirements ...
Expected result: One of 5.0,5.1,5.2,6.0
Actual Result: 6.1
Check complete. The overall result of this check is: Failed <<<< Problem: Oracle Database
10g is not certified on the current operating system. Recommendation: Make sure you are
installing the software on the correct platform.
========================================================
To workaround this problem I've decided to edit refhost.xml file adding entry for Windows 7.

Location of this file on my system:

c:\Install_Location...\client\stage\prereq\client\refhost.xml

This is excerpt from my newly edited refhost.xml file:

























New lines are added at the end of this excerpt after . Notice
version value=6.1.

Flush the Data Buffer Cache

In Oracle 9i and beyond you can flush the buffer cache with this command:

alter session set events ‘immediate trace name flush_cache’;


In Oracle 10g and beyond, this command will flush the buffer cache:

alter system flush buffer_cache;

Hidden Parameters

COLUMN parameter FORMAT a37
COLUMN description FORMAT a30 WORD_WRAPPED
COLUMN "Session Value" FORMAT a10
COLUMN "Instance Value" FORMAT a10
SET LINES 100
SET PAGES 0
SPOOL undoc.lis



SELECT
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
order by a.ksppinm
/

SPOOL OFF
SET LINES 80 PAGES 20
CLEAR COLUMNS

Formula for SGA

Approximating size calculation of the SGA

In 8.0.X :Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + log_buffers) + 1MB

In 8.1.X : Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

In 9.X : To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb


SGA Usage:

select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
from
v$sgastat
/


I have a doubt regarding SHARED POOL Memory in SGA.
The information stored in ORACLE SGA Memory is
(i) Database Buffer
(ii) Redo Log Buffer
(iii) Shared Pool memory


SELECT NVL(NAME,'TOTAL_SIZE') AS NAME,sum(ROUND(((VALUE/1024)/1024),2)) AS SIZE_MB
FROM V$SGA
GROUP BY ROLLUP(NAME)
/

NAME SIZE_MB
------------------------------ ----------
Database Buffers 420
Fixed Size 1.19
Redo Buffers 6.8
Variable Size 156
TOTAL_SIZE 583.99


From the abouve Query
a) Total SGA size is 583.99 MB
b) Database Buffer Size in SGA is 420 MB
c) Redo Buffer size in SGA is 6.8 MB



Questions
1) What does the VARIABLE SIZE & FIXED SIZE Values refers?
2) How to find the Size of SHARED POOL memory?


Fixed SGA
The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is "compiled" into the Oracle binary itself at installation time (hence the name "fixed"). The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a `bootstrap¿ section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.





the variable part is everything else that isn't the buffer cache, the fixed size, the redo buffers - it includes things like the shared pool, large pool, java pool, etc.

select pool, sum(bytes) from v$sgastat where pool is not null group by pool
order by pool;

POOL SUM(BYTES)
------------ ----------
java pool 16777216
large pool 16777216
shared pool 268440148