1 PACKAGE dbms_logmnr IS
2
3 --------------------
4 -- OVERVIEW
5 --
6 -- This package contains the procedures used by LogMiner ad-hoc query
7 -- interface that allows for redo log stream analysis.
8 -- There are three procedures and two functions available to the user:
9 -- dbms_logmnr.add_logfile() : to register logfiles to be analyzed
10 -- dbms_logmnr.remove_logfile() : to remove logfiles from being analyzed
11 -- dbms_logmnr.start_logmnr() : to provide window of analysis and
12 -- meta-data information
13 -- dbms_logmnr.end_logmnr() : to end the analysis session
14 -- dbms_logmnr.column_present() : whether a particular column value
15 -- is presnet in a redo record
16 -- dbms_logmnr.mine_value() : extract data value from a redo record
17 --
18
19 ---------------------------
20 -- PROCEDURE INFORMATION:
21 -- #1 dbms_logmnr.add_logfile():
22 -- DESCRIPTION:
23 -- Registers a redo log file with LogMiner. Multiple redo logs can be
24 -- registered by calling the procedure repeatedly. The redo logs
25 -- do not need to be registered in any particular order.
26 -- Both archived and online redo logs can be mined. If a successful
27 -- call to the procedure is made a call to start_logmnr() must be
28 -- made before selecting from v$logmnr_contents.
29 --
30 -- CONSTANTS:
31 -- dbms_logmnr.NEW: Starts a new list. Any file registered prior to
32 -- this call is discarded.
33 -- dbms_logmnr.ADDFILE: Adds the redo log to the existing
34 -- list. LogMiner checks to make sure that the redo log is from
35 -- the same database (DB_ID and RESETLOGS_SCN) incarnation as the
36 -- ones previously added.
37 --
38 -- EXCEPTIONS:
39 -- ORA: 1284 Redo log file specified can not be opened.
40 -- ORA: 1285 Error reading the header of the redo log file
41 -- ORA: 1286 Redo log file specified is from a database with a
42 -- different DB_ID
43 -- ORA: 1287 Redo log file specified is from a database with
44 -- different incarnation
45 -- ORA: 1289 Redo log file specified is a duplicate of a previously
46 -- specified redo log. LogMiner matches redo logs by the
47 -- log sequence number. Thus two redo logs with different
48 -- names but with the same log sequence# (for instance
49 -- the online counterpart of an archived redo log has
50 -- a different name, but attempting to register it with
51 -- LogMiner after registering the archived counterpart
52 -- will return this exception).
53 --
54 -- #2 dbms_logmnr.remove_logfile()
55 -- DESCRIPTION:
56 -- Unregisters a redo log file from LogMiner. Multiple redo logs can be
57 -- unregistered by calling the procedure repeatedly. The redo logs
58 -- do not need to be unregistered in any particular order. If a
59 -- successful call to the procedure is made a call to start_logmnr()
60 -- must be made before selecting from v$logmnr_contents.
61 --
62 -- EXCEPTIONS:
63 -- ORA: 1290 Attempt was made to remove a redo log that has not been
64 -- registered with LogMiner
65 --
66 -- #3 dbms_logmnr.start_logmnr()
67 -- DESCRIPTION:
68 -- Loads the data dictionary used by LogMiner to translate internal
69 -- schema object identifiers to names. The redo stream does not
70 -- contain names of schema objects and columns. The data dictionary
71 -- extract can be provided in three ways:
72 -- (i) use Oracle's online catalog. This is only valid when the
73 -- mining of redo logs is done in the same system that generated
74 -- them.
75 -- (ii) use data dictionary extract taken to a flat file.
76 -- (See description of dbms_logmnr_d.build())
77 -- (iii) use data dictionary extracted in the redo stream. This
78 -- option can ONLY be used when LogMiner is being run on an open
79 -- database, and the source and the mining database instances are
80 -- Oracle9i or higher.
81 -- The user can also restrict the analysis inside an SCN range or a
82 -- time range. If both SCN range and time range are specified, only
83 -- the SCN range is used.
84 -- The user needs to be mindful of the fact that use of time range
85 -- can be imprecise. If a start_time or start_scn is specified, it
86 -- must be contained in a redo log added by a previous call to
87 -- dbms_logmnr.add_logfile(). If a start_time and start_scn is not
88 -- specified, LogMiner will set it based on the earliest added redo
89 -- log. If a end_time or end_scn is specified and it is beyond the
90 -- latest added redo log, LogMiner will overwrite the end_time and
91 -- and end_scn with information from the latest added redo log. When
92 -- the CONTINOUS_MINE option is in use the semantics of
93 -- start and end time/scn ranges may be different.
94 -- See additional documentation below.
95 --
96 -- CONSTANTS (used in options parameter)
97 -- dbms_logmnr.NO_DICT_RESET_ONSELECT: (will be deprecated soon)
98 -- dbms_logmnr.COMMITED_DATA_ONLY: Groups DMLs belonging to the
99 -- same transaction. Transactions are shown in their commit order.
100 -- Internal redo records (those related to index operations, space
101 -- management etc) are filtered out. So are rolled back
102 -- transactions, rollback to savepoints and in-flight transactions.
103 -- dbms_logmnr.SKIP_CORRUPTION: Usually LogMiner returns an error
104 -- on encountering corrupt redo records. With this option set
105 -- LogMiner will skip the corrupted redo records and continue
106 -- mining. LogMiner can not handle a redo log that has a corrupt
107 -- header.
108 -- dbms_logmnr.DDL_DICT_TRACKING: LogMiner will apply the DDL
109 -- statements encountered in the redo stream to its internal
110 -- dictionary. Only available with Oracle9i redo logs and later.
111 -- Mining database needs to be open.
112 -- dbms_logmnr.DICT_FROM_ONLINE_CATALOG: Use the online data
113 -- dictionary for SQL reconstruction. Mining database must be the
114 -- same one that generated the redo logs. User should expect to
115 -- see "Dictionary Version Mismatch" in SQL_REDO if the current
116 -- object version is newer than the ones encountered in the redo
117 -- stream.
118 -- dbms_logmnr.DICT_FROM_REDO_LOGS: Use the dictionary extract logged
119 -- in the redo stream.
120 -- dbms_logmnr.NO_SQL_DELIMITER: By default, the SQL_REDO and SQL_UNDO
121 -- statements are delimited with a ';'. However, this is
122 -- inconvenient for applications that want to open a cursor and
123 -- execute the reconstructed statements. With this option set,
124 -- the SQL_DELIMITER is not placed at the end of reconstructed
125 -- statements.
126 -- dbms_logmnr.NO_ROWID_IN_STMT: By default, the SQL_REDO and SQL_UNDO
127 -- statements for UPDATE and DELETE operations contain a 'ROWID = '
128 -- in the where clause. However, this is inconvenient for
129 -- applications that want to re-execute the SQL statement. With
130 -- this option set, 'ROWID' is not placed at the end of reconstructed
131 -- statements. Note: The onus is on the user to ensure that
132 -- supplemental logging was enabled in the source database at the
133 -- appropriate level and that no duplicate rows exist in tables of
134 -- interest. LogMiner Adhoc Query does NOT make any quarantee
135 -- regarding uniqueness of logical row identifiers.
136 -- dbms_logmnr.PRINT_PRETTY_SQL: Useful for basic report for
137 -- analysis. With large number of columns the reconstructed
138 -- SQL statements become visually busy. With this option set
139 -- LogMiner formats the reconstructed SQL statements for ease
140 -- of reading. The reconstructed SQL statements look as follow:
141 -- insert into "SCOTT"."EMP" values
142 -- EMPNO: 101010,
143 -- ENAME: "Valued Employee",
144 -- SAL: 101010,
145 -- DEPT: NULL;
146 -- update "SCOTT"."EMP"
147 -- set
148 -- "EMPNO" = 101011 and
149 -- "SAL" = 101011
150 -- where
151 -- "EMPNO" = 101010 and
152 -- "SAL" = 101010 and
153 -- ROWID = AABBCEXFGHA;
154 -- dbms_logmnr.CONTINUOUS_MINE: Need to mine in the same instance
155 -- that is generating the redo logs. The user needs to register
156 -- only one archived log file. LogMiner will automatically add
157 -- and mine subsequent archived redo logs, and eventually
158 -- mine online logfiles.
159 -- dbms_logmnr.STRING_LITERALS_IN_STMT: By default, the SQL_REDO and
160 -- SQL_UNDO statements honor a database session's NLS setting
161 -- (e.g. NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, etc.) when
162 -- formating the reconstructed SQL statements. With this option,
163 -- the reconstructed SQL statements will be formatted using ANSI/ISO
164 -- string literal formats.
165 --
166 -- EXCEPTIONS:
167 -- ORA: 1281 startScn or endSCN parameter specified is not a valid
168 -- SCN or endScn is greater then startScn
169 -- ORA: 1282 startTime parameter is greater than year 2110 or
170 -- endTime parameter is greater than year 2110 or
171 -- startTime parameter is less then year 1988
172 -- ORA: 1283 The value specified in the Options parameter is not a
173 -- NUMBER or is not a known LogMiner Adhoc option
174 -- ORA: 1284 The dictionary file specified in the DictFileName
175 -- parameter has a full path length greater then 256 or
176 -- cannot be opened
177 -- ORA: 1285 DictFileName parameter is not a valid VARCHAR2
178 -- ORA: 1291 Redo files are missing which are needed to satisfy
179 -- the user's requested SCN/time range.
180 -- The user can specify ALLOW_MISSING_LOGS option.
181 -- Missing logs are not allowed under any circumstance
182 -- when DDL tracking is in use
183 -- ORA: 1292 No log file has been registered with LogMiner
184 -- ORA: 1293 Mounted database required for options specified
185 -- (CONTINIOUS_MINE)
186 -- ORA: 1294 Error while processing the data dictionary extract
187 -- ORA: 1295 DB_ID of the data dictionary does not match that of
188 -- the redo logs
189 -- ORA: 1296 Character set specified in the data dictionary does
190 -- not match (and is incompatible with) that of the
191 -- mining database
192 -- ORA: 1297 Redo version mismatch between the dictionary and
193 -- the registered redo logs
194 -- ORA: 1298 More than one dictionary source was specified or
195 -- DDL_DICT_TRACKING was requested with
196 -- DICT_FROM_ONLINE_CATALOG
197 -- ORA: 1299 Dictionary is from a different database incarnation
198 -- ORA: 1300 Writable database required for options specified
199 -- (DDL_DICT_TRACKING, DICT_FROM_REDO_LOGS,
200 -- DICT_FROM_ONLINE_CATALOG)
201 -- ORA: 1371 A logfile containing the dictionary dump to redo logs
202 -- is missing
203 -- ORA: 1286 Options specified require start time or start SCN
204 --
205 -- #4 dbms_logmnr.end_logmnr()
206 -- DESCRIPTION:
207 -- Ends the LogMiner session. Releases all PGA memory allocated
208 -- to stage internal data structures etc.
209 --
210 -- EXCEPTIONS:
211 -- ORA: 1307 No LogMiner session is currently active.
212 -- Attempt to end_logmnr() without calling
213 -- add_logfile() or start_logmnr()
214 --
215 -- #5 dbms_logmnr.mine_value()
216 -- DESCRIPTION:
217 -- This facilitates query by data value. For instance, the user
218 -- can formulate a query that says "Show me all updates to
219 -- SCOTT.EMP where the SAL column is updated to twice its
220 -- original value"
221 -- select sql_redo from v$logmnr_contents where
222 -- operation = 'UPDATE" and
223 -- owner_name = 'SCOTT' and seg_name = 'EMP' and
224 -- dbms_logmnr.mine_value(redo_value, 'SCOTT.EMP.SAL') >
225 -- 2* dbms_logmnr.mine_value(undo_value, 'SCOTT.EMP.SAL');
226 -- The function returns NULL if the column does not exist in
227 -- the redo record or if the column value is actually null.
228 -- To decipher between the two different null possibilities
229 -- use dbms_logmnr.column_present() function.
230 --
231 -- PARAMETERS:
232 -- sql_redo_undo: which column in v$logmnr_contents to
233 -- extract data value from
234 -- column_name: fully qualified column name of the
235 -- column that needs to be extracted
236 --
237 -- EXCEPTIONS:
238 -- ORA 1302: Specified table or column does not exist
239 --
240 -- #6 dbms_logmnr.column_present()
241 -- DESCRIPTION:
242 -- Can be used to decipher null returns from mine_value function
243 -- The query described above can be rewritten to filter out
244 -- redo records that do not contain update to the 'SAL'
245 -- columns
246 -- select sql_redo from v$logmnr_contents where
247 -- operation = 'UPDATE"
248 -- owner_name = 'SCOTT' and seg_name = 'EMP' and
249 -- dbms_logmnr.mine_value(redo_value, 'SCOTT.EMP.SAL') >
250 -- 2* dbms_logmnr.mine_value(undo_value, 'SCOTT.EMP.SAL') and
251 -- dbms_logmnr.column_present(redo_value, 'SCOTT.EMP.SAL');
252 --
253 -- PARAMETERS:
254 -- sql_redo_undo: which column in v$logmnr_contents to
255 -- extract data value from
256 -- column_name: fully qualified column name of the
257 -- column that needs to be extracted
258 --
259 -- EXCEPTIONS:
260 -- ORA 1302: Specified table or column does not exist
261 --
262 ---------------------------------
263
264 -----------------------------------
265 -- SUBTYPES and related CONSTANTS
266 --
267
268 --
269 -- Constants for add_archivelog options flag
270
271 NEW CONSTANT BINARY_INTEGER := 1;
272 REMOVEFILE CONSTANT BINARY_INTEGER := 2;
273 ADDFILE CONSTANT BINARY_INTEGER := 3;
274
275 --
276 -- Constants for start_logmnr options flag
277 NO_DICT_RESET_ONSELECT CONSTANT BINARY_INTEGER := 1;
278 COMMITTED_DATA_ONLY CONSTANT BINARY_INTEGER := 2;
279 SKIP_CORRUPTION CONSTANT BINARY_INTEGER := 4;
280 DDL_DICT_TRACKING CONSTANT BINARY_INTEGER := 8;
281 DICT_FROM_ONLINE_CATALOG CONSTANT BINARY_INTEGER := 16;
282 DICT_FROM_REDO_LOGS CONSTANT BINARY_INTEGER := 32;
283 NO_SQL_DELIMITER CONSTANT BINARY_INTEGER := 64;
284 PRINT_PRETTY_SQL CONSTANT BINARY_INTEGER := 512;
285 CONTINUOUS_MINE CONSTANT BINARY_INTEGER := 1024;
286 NO_ROWID_IN_STMT CONSTANT BINARY_INTEGER := 2048;
287 STRING_LITERALS_IN_STMT CONSTANT BINARY_INTEGER := 4096;
288 --
289 SUBTYPE Length IS BINARY_INTEGER;
290 SUBTYPE ThreadId IS BINARY_INTEGER;
291
292 --
296 VALID_SQL CONSTANT BINARY_INTEGER := 0;
293 -- Constants for STATUS column of v$logmnr_contents
294 -- NOTE: Make sure that new ones match the values defined
295 -- in the krvfsri struct in krv0.h
297 INVALID_SQL CONSTANT BINARY_INTEGER := 2;
298 UNGUARANTEED_SQL CONSTANT BINARY_INTEGER := 3;
299 CORRUPTED_BLK_IN_REDO CONSTANT BINARY_INTEGER := 4;
300 ASSEMBLY_REQUIRED_SQL CONSTANT BINARY_INTEGER := 5;
301 HOLE_IN_LOGSTREAM CONSTANT BINARY_INTEGER := 1291;
302
303 -- Workaround for the lack of constrained subtypes
304
305 LogFileNameTemplate VARCHAR2(256);
306 SUBTYPE LogFileName IS LogFileNameTemplate%TYPE;
307 LogFileDescTemplate VARCHAR2(256);
308 SUBTYPE LogFileDescription IS LogFileDescTemplate%TYPE;
309
310
311 -------------
312 -- PROCEDURES
313 --
314
315 ---------------------------------------------------------------------------
316 ---------------------------------------------------------------------------
317 -- Initialize LOGMINER
318 --
319 -- Supplies LOGMINER with the list of filenames and SCNs required
320 -- to initialize the tool. Once this procedure completes, the server is ready
321 -- to process selects against the v$logmnr_contents fixed view.
322 --
323 ---------------------------------------------------------------------------
324
325 PROCEDURE start_logmnr(
326 startScn IN NUMBER default 0 ,
327 endScn IN NUMBER default 0,
328 startTime IN DATE default '',
329 endTime IN DATE default '',
330 DictFileName IN VARCHAR2 default '',
331 Options IN BINARY_INTEGER default 0 );
332
333 PROCEDURE add_logfile(
334 LogFileName IN VARCHAR2,
335 Options IN BINARY_INTEGER default ADDFILE );
336
337 PROCEDURE end_logmnr;
338
339 FUNCTION column_present(
340 sql_redo_undo IN NUMBER default 0,
341 column_name IN VARCHAR2 default '') RETURN BINARY_INTEGER;
342
343 FUNCTION mine_value(
344 sql_redo_undo IN NUMBER default 0,
345 column_name IN VARCHAR2 default '') RETURN VARCHAR2;
346
347 PROCEDURE remove_logfile(
348 LogFileName IN VARCHAR2);
349
350 ---------------------------------------------------------------------------
351
352 pragma TIMESTAMP('1998-05-05:11:25:00');
353
354 END;
[Home] [Help]