I had a client which 5 instances on the same box were crashed because of disk failure.They did recover databases more than once and each time they found out that DBs were not recovered to right time.
They called me and asked me to recover database to June 7th which was the time that all DBs were opened with RESETLOGS.
As I checked, I found that DBs were opened with resetlogs 4 times.
June 3rd
June 7th <==== Requested recovery time.
June 9th
June 10th
Recovery seems to be straight forward with RMAN.Just need to find right incarnation and reset database to this incarnation and recover database.
This was my first thought but it did not work.
Why ? Reason is very simple. Since RMAN had no backup in June 7th right after resrlogs, database could not be recovered to that SCN.Database should be recovered to SCN in June 7th which RMAN has backup for all datafiles.
Therefore, the scenario was changed to this way :
- Connect to recovery catalog.
- List incarnation; <== Find incarnation for June 7th, sassume 1234.
- RESET database to incarnation 1234; <=== Incarnation from previous step.
- List backup of database; <==== Find the max SCN in June 7th which all datafiles have been backed up. (For example 28234442198 )
- run {
allocate channel ch1 device type disk;
set UNTIL SCN 28234442198 ;
restore controlfile ;
sql 'alter database mount';
restore database;
recover database;
}
=========
Example :
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
136421 136465 ALTP 2638891487 NO 190578 30-SEP-06
136421 136422 ALTP 2638891487 YES 28209020897 07-JUN-07
136421 141144 ALTP 2638891487 NO 28235483091 10-JUN-07
RESET DATABASE TO INCARNATION 136422;
List backup of database ;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136888 Full 161M DISK 00:00:08 07-JUN-07
BP Key: 136903 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662601_S1666_P1
List of Datafiles in backup set 136888
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\CWMLITE01.DBF
6 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\INDX01.DBF
13 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\CONVDATA.DBF
18 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\ININDX.DBF
28 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SQINDX.DBF
29 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SYDATA.DBF
30 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SYINDX.DBF
38 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\WOINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136889 Full 348M DISK 00:00:10 07-JUN-07
BP Key: 136904 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662616_S1667_P1
List of Datafiles in backup set 136889
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\DRSYS01.DBF
5 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\EXAMPLE01.DBF
11 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\APPDDATA.DBF
14 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\CUSTDATA.DBF
19 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\INVCDATA.DBF
24 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\RFQDATA.DBF
35 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\T_DATA.DBF
37 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\WODATA.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136890 Full 200M DISK 00:00:09 07-JUN-07
BP Key: 136905 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662631_S1668_P1
List of Datafiles in backup set 136890
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\ODM01.DBF
9 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\USERS01.DBF
10 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\XDB01.DBF
16 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\EQDATA.DBF
17 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\EQINDX.DBF
23 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\PRCHDATA.DBF
41 Full 28234451214 07-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHDATA.DBF
42 Full 28234451214 07-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136891 Full 365M DISK 00:00:11 07-JUN-07
BP Key: 136906 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662646_S1669_P1
List of Datafiles in backup set 136891
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\SYSTEM01.DBF
8 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\TOOLS01.DBF
12 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\APPDINDX.DBF
15 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\CUSTINDX.DBF
20 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\INVCINDX.DBF
22 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\MSPROJ.DBF
26 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\BIGROLL1.DBF
32 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\YKTPTOOL.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136892 Full 330M DISK 00:00:11 07-JUN-07
BP Key: 136907 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662662_S1670_P1
List of Datafiles in backup set 136892
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
21 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\IN_ITEM_XREF.DBF
25 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\RFQINDX.DBF
27 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\SQDATA.DBF
31 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\INDATA.ORA
33 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\TPDATA.DBF
34 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\TPINDX.DBF
36 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\YKTPUSER.DBF
40 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\PRCHINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136893 Full 1000M DISK 00:00:40 07-JUN-07
BP Key: 136908 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662677_S1671_P1
List of Datafiles in backup set 136893
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 28234451491 07-JUN-07 E:\ORADATA\YKTP\UNDOTBS01.DBF
39 Full 28234451491 07-JUN-07 C:\ORACLE\ORADATA\YKTP\COPYROL1.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141796 Full 161M DISK 00:00:08 11-JUN-07
BP Key: 141802 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998729_S1681_P1
List of Datafiles in backup set 141796
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\CWMLITE01.DBF
6 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\INDX01.DBF
13 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\CONVDATA.DBF
18 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\ININDX.DBF
28 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SQINDX.DBF
29 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SYDATA.DBF
30 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SYINDX.DBF
38 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\WOINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141797 Full 349M DISK 00:00:09 11-JUN-07
BP Key: 141803 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998745_S1682_P1
List of Datafiles in backup set 141797
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\DRSYS01.DBF
5 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\EXAMPLE01.DBF
11 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\APPDDATA.DBF
14 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\CUSTDATA.DBF
19 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\INVCDATA.DBF
24 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\RFQDATA.DBF
35 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\T_DATA.DBF
37 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\WODATA.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141798 Full 201M DISK 00:00:08 11-JUN-07
BP Key: 141804 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998760_S1683_P1
List of Datafiles in backup set 141798
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\ODM01.DBF
9 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\USERS01.DBF
10 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\XDB01.DBF
16 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\EQDATA.DBF
17 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\EQINDX.DBF
23 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\PRCHDATA.DBF
41 Full 28239402297 11-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHDATA.DBF
42 Full 28239402297 11-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141799 Full 365M DISK 00:00:10 11-JUN-07
BP Key: 141805 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998775_S1684_P1
List of Datafiles in backup set 141799
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\SYSTEM01.DBF
8 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\TOOLS01.DBF
12 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\APPDINDX.DBF
15 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\CUSTINDX.DBF
20 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\INVCINDX.DBF
22 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\MSPROJ.DBF
26 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\BIGROLL1.DBF
32 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\YKTPTOOL.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141800 Full 330M DISK 00:00:10 11-JUN-07
BP Key: 141806 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998790_S1685_P1
List of Datafiles in backup set 141800
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
21 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\IN_ITEM_XREF.DBF
25 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\RFQINDX.DBF
27 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\SQDATA.DBF
31 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\INDATA.ORA
33 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\TPDATA.DBF
34 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\TPINDX.DBF
36 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\YKTPUSER.DBF
40 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\PRCHINDX.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141801 Full 1000M DISK 00:00:37 11-JUN-07
BP Key: 141807 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998806_S1686_P1
List of Datafiles in backup set 141801
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 28239402393 11-JUN-07 E:\ORADATA\YKTP\UNDOTBS01.DBF
39 Full 28239402393 11-JUN-07 C:\ORACLE\ORADATA\YKTP\COPYROL1.DBF
After finding proper SCN, run the following to recover database before resetlogs.
run {
allocate channel ch1 device type disk;
set UNTIL SCN 28234451320 ;
restore controlfile ;
sql 'alter database mount';
restore database;
recover database;
}
TIP # 48 : Reading 10046 trace.
As I mentioned in TIP #38 post, there are ways to reading and formatting 10046 trace files.However, in some situation DBAs may choose to read details in trace file by themselves.Besides this may be more true if translator software has some bugs.
The followings is a guideline about the meaning of different section in 10046 trace file.
----------------------------------------------------------------------------
APPNAME mod='%s' mh=%lu act='%s' ah=%lu
---------------------------------------------------------------------------- APPNAME Application name setting. This only applies to Oracle 7.2
and above. This can be set by using the DBMS_APPLICATION_INFO
package. See Note 30366.1.
mod Module name.
mh Module hash value.
act Action.
ah Action hash value.
----------------------------------------------------------------------------
PARSING IN CURSOR # len=X dep=X uid=X oct=X lid=X tim=X hv=X ad='X'
END OF STMT
---------------------------------------------------------------------------- Cursor number.
len Length of SQL statement.
dep Recursive depth of the cursor.
uid Schema user id of parsing user.
oct Oracle command type.
lid Privilege user id.
tim Timestamp.
Pre-Oracle9i, the times recorded by Oracle only have a resolution
of 1/100th of a second (10mS). As of Oracle9i some times are
available to microsecond accuracy (1/1,000,000th of a second).
The timestamp can be used to determine times between points
in the trace file.
The value is the value in V$TIMER when the line was written.
If there are TIMESTAMPS in the file you can use the difference
between 'tim' values to determine an absolute time.
hv Hash id.
ad SQLTEXT address (see and ).
The actual SQL statement being parsed.
----------------------------------------------------------------------------
PARSE ERROR #%d:len=%ld dep=%d uid=%ld oct=%d lid=%ld tim=%lu err=%d
...
----------------------------------------------------------------------------
PARSE ERROR In Oracle 7.2+ parse errors are reported to the trace file.
len Length of SQL statement.
dep Recursive depth of the statement
uid User id.
oct Oracle command type (if known).
lid Privilege user id.
tim Timestamp.
err Oracle error code (e.g. ORA-XXXXX) reported
The SQL statement that errored. If this contains a password,
the statement is truncated as indicated by '...' at the end.
----------------------------------------------------------------------------
PARSE #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
FETCH #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
UNMAP #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
---------------------------------------------------------------------------- - OPERATIONS:
PARSE Parse a statement.
EXEC Execute a pre-parsed statement.
FETCH Fetch rows from a cursor.
UNMAP If the cursor uses a temporary table, when the cursor is
closed you see an UNMAP when we free up the temporary table
locks.(Ie: free the lock, delete the state object, free the
temp segment)
In tkprof, UNMAP stats get added to the EXECUTE statistics.
SORT UNMAP
As above, but for OS file sorts or TEMP table segments.
c CPU time (100th's of a second in Oracle7 ,8 and 9).
e Elapsed time (100th's of a second Oracle7, 8
Microseconds in Oracle 9 onwards).
p Number of physical reads.
cr Number of buffers retrieved for CR reads.
cu Number of buffers retrieved in current mode.
mis Cursor missed in the cache.
r Number of rows processed.
dep Recursive call depth (0 = user SQL, >0 = recursive).
og Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
tim Timestamp (large number in 100ths of a second). Use this to
determine the time between any 2 operations.
----------------------------------------------------------------------------
ERROR #%d:err=%d tim=%lu
---------------------------------------------------------------------------- SQL Error shown after an execution or fetch error.
err Oracle error code (e.g. ORA-XXXXX) at the top of the stack.
tim Timestamp.
----------------------------------------------------------------------------
STAT # id=N cnt=0 [pid=0 pos=0 obj=0 op='SORT AGGREGATE ']
----------------------------------------------------------------------------
STAT Lines report explain plan statistics for the numbered.
Cursor which the statistics apply to.
id Line of the explain plan which the row count applies to (starts
at line 1). This is effectively the row source row count
for all row sources in the execution tree.
cnt Number of rows for this row source.
As of 7.3.3 the items in '[...]' are also reported:
pid Parent id of this row source.
pos Position in explain plan.
obj Object id of row source (if this is a base object).
op='...' The row source access operation.
These let you know the 'run time' explain plan.
----------------------------------------------------------------------------
XCTEND rlbk=%d rd_only=%d
---------------------------------------------------------------------------- XCTEND A transaction end marker.
rlbk 1 if a rollback was performed, 0 if no rollback (commit).
rd_only 1 if transaction was read only, 0 if changes occurred.
----------------------------------------------------------------------------
======================================================================
The items below are only output if WAITS or BINDS are being traced.
These can be enabled with the DBMS_SUPPORT package.
======================================================================
----------------------------------------------------------------------------
BINDS #%d:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0
bfp=02fedb44 bln=22 avl=00 flg=05
value=10
----------------------------------------------------------------------------
BIND Variables bound to a cursor.
bind N The bind position being bound.
dty Data type (see).
mxl Maximum length of the bind variable (private max len in paren).
mal Array length.
scl Scale.
pre Precision.
oacflg Special flag indicating bind options
oacflg2 Continuation of oacflg
size Amount of memory to be allocated for this chunk
offset Offset into this chunk for this bind buffer
bfp Bind address.
bln Bind buffer length.
avl Actual value length (array length too).
flg Special flag indicating bind status
value The actual value of the bind variable.
Numbers show the numeric value, strings show the string etc...
It is also possible to see "bind 6: (No oacdef for this bind)", if no
separate bind buffer exists.
----------------------------------------------------------------------------
WAIT #: nam="" ela=0 p1=0 p2=0 p3=0
----------------------------------------------------------------------------
WAIT An event that we waited for.
nam What was being waited for .
The wait events here are the same as are seen in
. For any Oracle release a full list of
wait events and the values in P1, P2 and P3 below can be seen
in
ela Elapsed time for the operation.
p1 P1 for the given wait event.
p2 P2 for the given wait event.
p3 P3 for the given wait event.
Example (Full Table Scan):
WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25
WAITing under CURSOR no 1
for "db file scattered read"
We waited 0.05 seconds
For a read of: File 4, start block 1435, for 25 Oracle blocks
Example (Index Scan):
WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1
WAITing under CURSOR no 1
for "db file sequential read"
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224
The followings is a guideline about the meaning of different section in 10046 trace file.
----------------------------------------------------------------------------
APPNAME mod='%s' mh=%lu act='%s' ah=%lu
---------------------------------------------------------------------------- APPNAME Application name setting. This only applies to Oracle 7.2
and above. This can be set by using the DBMS_APPLICATION_INFO
package. See Note 30366.1.
mod Module name.
mh Module hash value.
act Action.
ah Action hash value.
----------------------------------------------------------------------------
PARSING IN CURSOR #
END OF STMT
----------------------------------------------------------------------------
len Length of SQL statement.
dep Recursive depth of the cursor.
uid Schema user id of parsing user.
oct Oracle command type.
lid Privilege user id.
tim Timestamp.
Pre-Oracle9i, the times recorded by Oracle only have a resolution
of 1/100th of a second (10mS). As of Oracle9i some times are
available to microsecond accuracy (1/1,000,000th of a second).
The timestamp can be used to determine times between points
in the trace file.
The value is the value in V$TIMER when the line was written.
If there are TIMESTAMPS in the file you can use the difference
between 'tim' values to determine an absolute time.
hv Hash id.
ad SQLTEXT address (see
----------------------------------------------------------------------------
PARSE ERROR #%d:len=%ld dep=%d uid=%ld oct=%d lid=%ld tim=%lu err=%d
----------------------------------------------------------------------------
PARSE ERROR In Oracle 7.2+ parse errors are reported to the trace file.
len Length of SQL statement.
dep Recursive depth of the statement
uid User id.
oct Oracle command type (if known).
lid Privilege user id.
tim Timestamp.
err Oracle error code (e.g. ORA-XXXXX) reported
the statement is truncated as indicated by '...' at the end.
----------------------------------------------------------------------------
PARSE #
EXEC #
FETCH #
UNMAP #
----------------------------------------------------------------------------
PARSE Parse a statement.
EXEC Execute a pre-parsed statement.
FETCH Fetch rows from a cursor.
UNMAP If the cursor uses a temporary table, when the cursor is
closed you see an UNMAP when we free up the temporary table
locks.(Ie: free the lock, delete the state object, free the
temp segment)
In tkprof, UNMAP stats get added to the EXECUTE statistics.
SORT UNMAP
As above, but for OS file sorts or TEMP table segments.
c CPU time (100th's of a second in Oracle7 ,8 and 9).
e Elapsed time (100th's of a second Oracle7, 8
Microseconds in Oracle 9 onwards).
p Number of physical reads.
cr Number of buffers retrieved for CR reads.
cu Number of buffers retrieved in current mode.
mis Cursor missed in the cache.
r Number of rows processed.
dep Recursive call depth (0 = user SQL, >0 = recursive).
og Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
tim Timestamp (large number in 100ths of a second). Use this to
determine the time between any 2 operations.
----------------------------------------------------------------------------
ERROR #%d:err=%d tim=%lu
---------------------------------------------------------------------------- SQL Error shown after an execution or fetch error.
err Oracle error code (e.g. ORA-XXXXX) at the top of the stack.
tim Timestamp.
----------------------------------------------------------------------------
STAT #
----------------------------------------------------------------------------
STAT Lines report explain plan statistics for the numbered
id Line of the explain plan which the row count applies to (starts
at line 1). This is effectively the row source row count
for all row sources in the execution tree.
cnt Number of rows for this row source.
As of 7.3.3 the items in '[...]' are also reported:
pid Parent id of this row source.
pos Position in explain plan.
obj Object id of row source (if this is a base object).
op='...' The row source access operation.
These let you know the 'run time' explain plan.
----------------------------------------------------------------------------
XCTEND rlbk=%d rd_only=%d
---------------------------------------------------------------------------- XCTEND A transaction end marker.
rlbk 1 if a rollback was performed, 0 if no rollback (commit).
rd_only 1 if transaction was read only, 0 if changes occurred.
----------------------------------------------------------------------------
======================================================================
The items below are only output if WAITS or BINDS are being traced.
These can be enabled with the DBMS_SUPPORT package.
======================================================================
----------------------------------------------------------------------------
BINDS #%d:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0
bfp=02fedb44 bln=22 avl=00 flg=05
value=10
----------------------------------------------------------------------------
BIND Variables bound to a cursor.
bind N The bind position being bound.
dty Data type (see
mxl Maximum length of the bind variable (private max len in paren).
mal Array length.
scl Scale.
pre Precision.
oacflg Special flag indicating bind options
oacflg2 Continuation of oacflg
size Amount of memory to be allocated for this chunk
offset Offset into this chunk for this bind buffer
bfp Bind address.
bln Bind buffer length.
avl Actual value length (array length too).
flg Special flag indicating bind status
value The actual value of the bind variable.
Numbers show the numeric value, strings show the string etc...
It is also possible to see "bind 6: (No oacdef for this bind)", if no
separate bind buffer exists.
----------------------------------------------------------------------------
WAIT #
----------------------------------------------------------------------------
WAIT An event that we waited for.
nam What was being waited for .
The wait events here are the same as are seen in
wait events and the values in P1, P2 and P3 below can be seen
in
ela Elapsed time for the operation.
p1 P1 for the given wait event.
p2 P2 for the given wait event.
p3 P3 for the given wait event.
Example (Full Table Scan):
WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25
WAITing under CURSOR no 1
for "db file scattered read"
We waited 0.05 seconds
For a read of: File 4, start block 1435, for 25 Oracle blocks
Example (Index Scan):
WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1
WAITing under CURSOR no 1
for "db file sequential read"
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224
TIP # 47 :Consideration in granting SELECT to public
I posted this log because of the situation that I faced with for a client last week.I created a user in 10g database (10gR2) and granted only connect privilege.I was suprised when I Logged in with a new user and checked out all tables which are accessible by user (select * from user_tables) . Users had privileges to select from some tables in other schema while user had only connect privilege.
After digging up, I found out that these privileges are because of select privileges which have been granted to PUBLIC user. The lesson is that any privilege which is granted to public, is granted to all users.As the result, in order to prevent security hole, granting privileges to PUBLIC should be restricted.
Above all, a known bug or it is better to say a big security hole may exists in DBs which select privilege has been granted to PUBLIC. In Database without latest CPU patch, users in database can run any DML commands on tables which only select provilege has been granted to public . (Security Hole !!!) .
The following shows the sample :
create user test identified by test;
create user test1 identified by test1;
grant resource,connect to test;
grant connect to test1;
--------- Create a table in test user and grant select to publi
cconnect test/test
create table test_sec (id number primary key,id2 number);
grant select on test_sec to public;
--------- Connect to second user .
connect test1/test1
desc test.test_sec
Name Null? Type
---------------------------
ID NOT NULL NUMBER
ID2 NUMBER
select * from test.test_sec; <===== user can select because select was granted to user
insert into test.test_sec values(1,1);
ORA-01031: insufficient privileges <======= User can not insert because insert was not granted
===========Bug ============== test1 can insert into test.test_sec if using the following syntax :
insert into (select a.id,a.id2 from (select * from test.test_sec)a inner join (select * from test.test_sec )b on (a.id=b.id) ) values (1,2);
1 row inserted. <========== User can insert while it has not any privilege to do that
select * from test.test_sec;
ID ID2
------------------
1 2
Bottom line is that avoid granting privileges to public users or have a monitoring of granted privileges to public users to prevent from unexpected permission.
After digging up, I found out that these privileges are because of select privileges which have been granted to PUBLIC user. The lesson is that any privilege which is granted to public, is granted to all users.As the result, in order to prevent security hole, granting privileges to PUBLIC should be restricted.
Above all, a known bug or it is better to say a big security hole may exists in DBs which select privilege has been granted to PUBLIC. In Database without latest CPU patch, users in database can run any DML commands on tables which only select provilege has been granted to public . (Security Hole !!!) .
The following shows the sample :
create user test identified by test;
create user test1 identified by test1;
grant resource,connect to test;
grant connect to test1;
--------- Create a table in test user and grant select to publi
cconnect test/test
create table test_sec (id number primary key,id2 number);
grant select on test_sec to public;
--------- Connect to second user .
connect test1/test1
desc test.test_sec
Name Null? Type
---------------------------
ID NOT NULL NUMBER
ID2 NUMBER
select * from test.test_sec; <===== user can select because select was granted to user
insert into test.test_sec values(1,1);
ORA-01031: insufficient privileges <======= User can not insert because insert was not granted
===========Bug ============== test1 can insert into test.test_sec if using the following syntax :
insert into (select a.id,a.id2 from (select * from test.test_sec)a inner join (select * from test.test_sec )b on (a.id=b.id) ) values (1,2);
1 row inserted. <========== User can insert while it has not any privilege to do that
select * from test.test_sec;
ID ID2
------------------
1 2
Bottom line is that avoid granting privileges to public users or have a monitoring of granted privileges to public users to prevent from unexpected permission.
Subscribe to:
Posts (Atom)