| How to locate SCN/Time of DROP TABLE using LogMiner [ID 93370.1] | |||||
|
| |||||
| Modified 17-APR-2009 Type BULLETIN Status PUBLISHED | |||||
PURPOSE This article discusses and demonstrates how to locate and analyze a DROP TABLE statement in online and archived redo logs using LogMiner. This article is not intended to present LogMiner basics - refer to the Oracle8i Administrator's Guide and Supplied Packages Reference for LogMiner introduction and general instructions. The procedure outlined in this article results in information (SCN/Time) to assist Point-in-Time Recovery (PITR) for an unintentional dropped table. Refer to Note:96197.1 for a step-by-step procedure to recover a dropped table from a full database backup. SCOPE & APPLICATION This article assumes the reader has basic understanding of fixed tables and LogMiner. RELATED DOCUMENTS Note:74988.1 How to build an Oracle 8.0 LogMiner Dictionary Note:96197.1 Recovering a Dropped Table from a Full Database Backup Oracle 8.1.5 Administrator's Guide - Managing Archived Redo Logs Chapter Oracle 8.1.5 Supplied Packages Reference: dbms_logmnr_d, dbms_logmnr How to locate SCN/Time of DROP TABLE using LogMiner: ==================================================== LogMiner Overview LogMiner (packages dbms_logmnr_d and dbms_logmnr) provides a SQL interface to review information contained in Oracle8 online and archived redo logs. The LogMiner dictionary allows queries against v$logmnr_contents to be easily understood. This dictionary provides schema, object, column and datatype translations. Note: v$logmnr_contents.seg_name is only populated if a LogMiner dictionary is used! Example 1 : LogMiner analysis using a dictionary file. SQL> select sql_redo from v$logmnr_contents; SQL_REDO -------------------------------------------------------------------------------- set transaction read write; insert into TEST.TAB1(C1) values (1); commit; Example 2: LogMiner analysis without a dictionary file. SQL> select sql_redo from v$logmnr_contents; SQL_REDO -------------------------------------------------------------------------------- set transaction read write; insert into UNKNOWN.Objn:13079(Col[1]) values (HEXTORAW('c102')); commit; Only Data Manipulation Language (DML) and transaction control generates redo/undo that is translated by LogMiner. Data Definition Language (DDL), such as DROP TABLE, actually performs DML on dictionary objects (i.e. SYS.TAB$, SYS.OBJ$), but DML on most dictionary objects do not have translations in Oracle 8.1.5, therefore analysis may require hex conversion. Note: SYS.OBJ$ is translated if a LogMiner dictionary is specified in dbms_logmnr.start_logmnr but SYS.TAB$ is not translated regardless of dictionary specification. Although the LogMiner dictionary file assists in locating a DDL for a specfic object - the actual dictionary file will also be used since SELECTs are not allowed against the temporary objects built when the dictionary is loaded (dbms_logmnr.logmnr_start). As an alternative method, it is possible to convert the dictionary file into a SQL script which will build normal database objects. Refer to the comments in the header of the dictionary file created by dbms_logmnr_d.build for instructions. This procedure will not be demonstrated in this article. Assumptions: It is assumed that a dictionary file exists from a time when the dropped table existed. A dictionary file MUST be specified in dbms_logmnr.start_logmnr in order to populate v$logmnr_contents.seg_name. If a dictionary file is not specified v$logmnr_contents.seg_name will be NULL for every redo/undo entry. The SCN/Time of a DROP TABLE can be determined even if a LogMiner dictionary does not contain the dropped table. It is also assumed that the exact on-line and/or archived redo log file spanning the time of the DROP table is known. The larger the DROP time estimate - the more redo log files will require analysis. Either 1) multiple redo log files can be loaded into LogMiner (dbms_logmnr.add_logfile) for a single LogMiner analysis session or 2) a LogMiner analysis session can be run individually for each redo log file - starting with the 'best guess'. *** Locating DROP TABLE Overview: 1. Locate on-line and/or archived redo logs for time corresponding with DROP TABLE. 2. If possible, locate the dictionary file that was created while table existed, otherwise generate a LogMiner dictionary file for the database. 3. Load the redo logs into LogMiner (dbms_logmnr.add_logfile) 4. Load dictionary objects supporting LogMiner (dbms_logmnr.start_logmnr) 5. Process redo logs and extract information (v$logmnr_contents). 5a. (*) CREATE TABLE AS SELECT (CTAS) to move the rows of v$logmnr_contents into a temporary table. Since each SELECT of v$logmnr_contents performs physical file read of the loaded redo logs - this step can be omitted if the redo log is small, but is a time savings if processing a lot of redo. A temporary table will also facilitate analysis beyond a single session. 6. Locate the DROP TABLE by locating associated DML on fixed tables. (*) Optional Locating DROP TABLE Example: For ease of discussion, SQL statements are intentionally isolated into very small redo log files using ALTER SYSTEM SWITCH LOGFILE. Without question - this is not a 'real-world' example for redo log files are typically quite large for performance reasons. *** --------------------------------------------------------------------------- *** Setup: Create table and build LogMiner dictionary. *** --------------------------------------------------------------------------- SQL> create table tab1 ( 2 c1 integer, 3 c2 integer, 4 c3 integer); Table created. SQL> connect internal Connected. SQL> select value from v$parameter where name = 'utl_file_dir'; VALUE -------------------------------------------------------------------------------- /database/V815/extproc SQL> begin 2 sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', 3 dictionary_location => '/database/V815/extproc'); 4 end; 5 / PL/SQL procedure successfully completed. *** --------------------------------------------------------------------------- *** Setup: Isolate DROP TABLE into an archive log *** --------------------------------------------------------------------------- SQL> alter system switch logfile; System altered. SQL> select sequence# from v$log where status = 'CURRENT'; SEQUENCE# ---------- 22 SQL> drop table tab1; Table dropped. SQL> alter system switch logfile; System altered. *** --------------------------------------------------------------------------- *** Analysis *** --------------------------------------------------------------------------- SQL> -- Load archive log into LogMiner. SQL> begin 2 dbms_logmnr.add_logfile ( 3 LogFileName => 4 '/database/V815/archivelogs/V815_ArchiveLog_1_0000000022.dbf', 5 Options => dbms_logmnr.new); 6 end; 7 / PL/SQL procedure successfully completed. SQL> -- Prepare LogMiner for analysis. SQL> begin 2 dbms_logmnr.start_logmnr ( 3 dictFileName => '/database/V815/extproc/dictionary.ora'); 4 end; 5 / PL/SQL procedure successfully completed. SQL> -- DROP TABLE will generate DML on fixed tables SQL> -- Notes: SQL> -- 1. The goal is to locate a single transaction (SCN) the performs a SQL> -- DELETE operation on SYS.COL$ (columns), SYS.OBJ$ (objects) and SQL> -- SYS.TAB$ (tables) for this identifies a DROP TABLE statement. SQL> -- 2. There will be one DELETE operation on SYS.COL$ for each column. SQL> -- 3. DML on SYS.SEG$ (segments), SYS.UET$ (used space), SYS.FET$ (free SQL> -- space) and SYS.TSQ$ (tablespace quotas) perform segment and space SQL> -- cleanup. DML on these fixed tables do not necessarily indicate SQL> -- DROP TABLE. SQL> -- 4. Refer to $ORACLE_HOME/rdbms/admin/sql.bsq for additional information SQL> -- regarding fixed tables. SQL> -- Summarize contents of archive log. Not advised for large redo files. SQL> -- v$logmnr_contents.operation = 'INTERNAL' implies sql_redo is NULL. SQL> -- A LogMiner dictionary must be specified for v$logmnr_contents.seg_name SQL> -- population - which is essential for this procedure. SQL> column seg_name format a15 trunc SQL> select seg_name, operation, scn, count(*) from v$logmnr_contents 2 where operation != 'INTERNAL' 3 group by seg_name, operation, scn 4 order by scn; SEG_NAME OPERATION SCN COUNT(*) --------------- -------------------------------- ---------- ---------- COMMIT 5012064 1 COL$ DELETE 5012065 3 OBJ$ DELETE 5012065 1 TAB$ DELETE 5012065 1 START 5012065 1 SEG$ UPDATE 5012065 1 COMMIT 5012066 1 SEG$ DELETE 5012067 1 UET$ DELETE 5012067 1 FET$ INSERT 5012067 1 START 5012067 1 TSQ$ UPDATE 5012067 1 COMMIT 5012068 1 13 rows selected. SQL> -- DROP TABLE will generate DELETE operations on COL$, OBJ$ and TAB$ SQL> -- Since SELECT on v$logmnr_contents physically reads the redo files, SQL> -- CREATE TABLE AS SELECT (CTAS) can be used to move associated data from SQL> -- v$logmnr_contents into a temporary table. The temporary table may SQL> -- contain DML associated with DML other than DROP TABLE including DROP SQL> -- COLUMN, DROP INDEX, etc. Moving the relevant contents of SQL> -- v$logmnr_contents into a temporary table will also facilitate SQL> -- analysis spanning sessions. SQL> create table temp 2 as select * from v$logmnr_contents 3 where operation = 'DELETE' 4 and seg_name in ('COL$','OBJ$','TAB$'); Table created. SQL> -- The remainder of the example will reference TEMP but all SQL could SQL> -- be modified to use v$logmnr_contents. SQL> -- Verify that DROP TABLE exists in analyzed redo files. If there does not SQL> -- exist a single SCN that performs a DELETE operation on COL$, OBJ$ and SQL> -- TAB$ then more redo files must be loaded into LogMiner for analysis. SQL> -- At some point, the time spent performing redo analysis must be weighted SQL> -- against the total time to object recovery, for processing redo with SQL> -- LogMiner can be a time consuming task. SQL> select seg_name, operation, scn, count(*) from temp 2 where operation != 'INTERNAL' 3 group by seg_name, operation, scn 4 order by scn; SEG_NAME OPERATION SCN COUNT(*) --------------- -------------------------------- ---------- ---------- COL$ DELETE 5012065 3 OBJ$ DELETE 5012065 1 TAB$ DELETE 5012065 1 SQL> -- Locate SCNs of all DROP TABLE statement(s). SQL> -- This is only necessary if the resulting rows from the previous query SQL> -- is to large to easily inspect. Clearly this is not necessary SQL> -- for this example, for the redo file only contains DML associated with SQL> -- a single DROP TABLE statement. SQL> select scn from v$logmnr_contents 2 where operation = 'DELETE' and seg_name = 'COL$' 3 intersect 4 select scn from v$logmnr_contents 5 where operation = 'DELETE' and seg_name = 'OBJ$' 6 intersect 7 select scn from v$logmnr_contents 8 where operation = 'DELETE' and seg_name = 'TAB$'; SCN ---------- 5012065 SQL> -- At this point we have collected a list of SCN(s) associated with DROP SQL> -- TABLE. Using LogMiner dictionary if available, analyze sql_redo to SQL> -- determine dropped tables. SQL> select sql_redo from temp 2 where scn = 5012065 and seg_name = 'TAB$'; SQL_REDO -------------------------------------------------------------------------------- delete from UNKNOWN.Objn:4 where Col[1] = HEXTORAW('c3021f50') and Col[2] = HEXT ORAW('c102') and Col[3] = HEXTORAW('c103') and Col[4] = HEXTORAW('c2034e') and C ol[5] IS NULL and Col[6] IS NULL and Col[7] = HEXTORAW('c104') and Col[8] IS NUL L and Col[9] = HEXTORAW('c10b') and Col[10] = HEXTORAW('c129') and Col[11] = HEX TORAW('c102') and Col[12] = HEXTORAW('c20338') and Col[13] = HEXTORAW('c102') an d Col[14] = HEXTORAW('2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d 2d2d2d') and Col[15] IS NULL and Col[16] IS NULL and Col[17] IS NULL and Col[18] IS NULL and Col[19] IS NULL and Col[20] IS NULL and Col[21] IS NULL and Col[22] IS NULL and Col[23] IS NULL and Col[24] IS NULL and Col[25] IS NULL and Col[26] IS NULL and Col[27] = HEXTORAW('c104') and Col[28] = HEXTORAW('c104') and Col[2 9] = HEXTORAW('80') and Col[30] = HEXTORAW('80') and Col[31] = HEXTORAW('c20441' ) and Col[32] IS NULL and Col[33] = HEXTORAW('80') and ROWID = 'AAAAACAABAAAH1ZA AA'; SQL> -- Col[1] corresponds to tab$.obj# SQL> -- Hex to raw conversion of data results in object id (Oracle internal value) SQL> -- Using LogMiner dictionary, locate INSERT_INTO OBJ$_TABLE for the SQL> -- object id deleted from TAB$. The 4th column in OBJ$ is the object name. SQL> select distinct timestamp from temp where scn = 5012065; TIMESTAMP -------------------- 2000-JAN-18 12:36:23 NOTE: This timestamp is for scn 5012065, when the drop table statement was submitted. Extract from /database/V815/extproc/dictionary.ora: INSERT_INTO OBJ$_TABLE VALUES (13079,13079,38,'TAB1',1,'',2,to_date('01/18/2000 12:25:28', 'MM/DD/YYYY HH24:MI:SS'),to_date('01/18/2000 12:25:28', 'MM/DD/YYYY H H24:MI:SS'),to_date('01/18/2000 12:25:28', 'MM/DD/YYYY HH24:MI:SS'),1,'','',0,,, ,,'','', ); SQL> -- Note that the same information can be found from the SQL redo of the SQL> -- DELETE operation on OBJ$ for the SCN without using the dictionary: SQL> -- OBJ$.OBJ# = 13709, OBJ$.NAME = 'TAB1'. SQL> select sql_redo from temp 2 where scn = 5012065 and seg_name = 'OBJ$'; SQL_REDO -------------------------------------------------------------------------------- delete from SYS.OBJ$ where OBJ# = 13079 and DATAOBJ# = 13079 and OWNER# = 38 and NAME = 'TAB1' and NAMESPACE = 1 and SUBNAME IS NULL and TYPE# = 2 and CTIME = T O_DATE('18-JAN-2000 12:25:28', 'DD-MON-YYYY HH24:MI:SS') and MTIME = TO_DATE('18 -JAN-2000 12:25:28', 'DD-MON-YYYY HH24:MI:SS') and STIME = TO_DATE('18-JAN-2000 12:25:28', 'DD-MON-YYYY HH24:MI:SS') and STATUS = 1 and REMOTEOWNER IS NULL and LINKNAME IS NULL and FLAGS = 0 and ROWID = 'AAAAASAABAAAGXkAAA'; NOTE: ctime = timestamp when the table was created mtime = timestamp for last DDL against the table Do not confuse the timestamp associated with the scn for the drop table with the timestamps in obj$ for object create and modification. SQL> select username from dba_users where user_id = 38; USERNAME ------------------------------ TEST Summary: ======== At this point we know that table TEST.TAB1 (3 columns) was dropped at SCN 5012065 and time 2000-JAN-18 12:36:23. This information can be used for point-in-time recovery (PITR) of an unintentionally dropped object following the steps in Note:96197.1. Search Words: ============= Log Miner
| Related Products
|
173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
