ORACLE LogMiner 联机目录分析多个归档日志操作步骤

客官°小女子只卖身不卖艺 2024-04-17 15:24 76阅读 0赞

1、install LogMiner

\-bash-4.2$ sqlplus /nolog

SQL\*Plus: Release 11.2.0.4.0 Production on 星期二 8月 27 21:50:31 2019

SQL> conn / as sysdba  
Connected.  
SQL> @$ORACLE\_HOME/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL> @$ORACLE\_HOME/rdbms/admin/dbmslmd.sql

Package created.

Synonym created.

SQL>

2、add the redo log which needed the analysis

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => '/data/oracle/archive/orcl/1\_7\_1016047951.dbf'  
, options=>dbms\_logmnr.new  
);  
END;

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => '/data/oracle/archive/orcl/1\_8\_1016047951.dbf'  
, options=>dbms\_logmnr.addfile  
);  
END;

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => '/data/oracle/archive/orcl/1\_9\_1016047951.dbf'  
, options=>dbms\_logmnr.addfile  
);  
END;

3、use LogMiner to analyze the log

BEGIN  
DBMS\_LOGMNR.START\_LOGMNR(  
OPTIONS => SYS.DBMS\_LOGMNR.DICT\_FROM\_ONLINE\_CATALOG);  
END;

ALTER SESSION SET NLS\_DATE\_FORMAT = 'YYYY-MM-DD HH24:MI:SS';  
SELECT \*  
FROM V$LOGMNR\_CONTENTS order by 1;

BEGIN  
DBMS\_LOGMNR.END\_LOGMNR();  
END;

\-- 1. install LogMiner

$ORACLE\_HOME/rdbms/admin/dbmslm.sql  
$ORACLE\_HOME/rdbms/admin/dbmslmd.sql

@F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\RDBMS\\ADMIN\\dbmslm.sql  
@F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\RDBMS\\ADMIN\\dbmslmd.sql

\-- 2. create data dictionary file  
CREATE DIRECTORY utlfile AS 'F:\\app\\oracle\\oradata\\logmnr';  
ALTER SYSTEM SET utl\_file\_dir='F:\\app\\oracle\\oradata\\logmnr' SCOPE=SPFILE;

BEGIN  
dbms\_logmnr\_d.build(  
dictionary\_filename => 'dictionary.ora'  
, dictionary\_location => 'F:\\app\\oracle\\oradata\\logmnr'  
);  
END;  
/

\-- 3. add the redo log which needed the analysis

SELECT group\#, first\_time, next\_time FROM v$log;

SELECT name, first\_time, next\_time FROM v$archived\_log;

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => 'F:\\app\\oracle\\oradata\\informal\\REDO01.LOG'  
, options=>dbms\_logmnr.new  
);  
END;  
/

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => 'F:\\app\\oracle\\oradata\\informal\\REDO02.LOG'  
, options=>dbms\_logmnr.addfile  
);  
END;  
/

BEGIN  
dbms\_logmnr.add\_logfile(  
logfilename => 'F:\\app\\oracle\\oradata\\informal\\REDO03.LOG'  
, options=>dbms\_logmnr.addfile  
);  
END;  
/

\-- 4. use LogMiner to analyze the log

BEGIN  
dbms\_logmnr.start\_logmnr(  
dictfilename => 'F:\\app\\oracle\\oradata\\logmnr\\dictionary.ora'  
, startscn => 1239979  
, endscn => 1240628  
);  
END;  
/

ALTER SESSION SET NLS\_DATE\_FORMAT = 'YYYY-MM-DD HH24:MI:SS';  
SELECT \*  
FROM V$LOGMNR\_CONTENTS order by 1;

BEGIN  
DBMS\_LOGMNR.END\_LOGMNR();  
END;

timestamp\_to\_scn(to\_timestamp('2016-07-29 11:20:00','YYYY-MM-DD HH24:MI:SS'))

select timestamp\_to\_scn(to\_timestamp('2016-07-29 09:55:00','YYYY-MM-DD HH24:MI:SS')) from dual;  
select timestamp\_to\_scn(to\_timestamp('2016-07-29 10:05:00','YYYY-MM-DD HH24:MI:SS')) from dual;