参考视频:
[bbk1260]
[bbk1261]
objectives
- analyze redo logfile
- incomplete recover database
steps
- backup database
- modify spfile
- restart database
- manipulate database,switch logfile
- use logmnr
- analyze redo logfile
- inmplete recover datavase
- verify recover
modify spfile
- mkdir /logmnr
- chown -R oracle:dba /logmnr
- alter system set utl_file_dir='/logmnr' scope='spfile' side='*';
- shutdown databae;
- startup;
模拟误操作
- insert
- drop
- insert
- switch logfile
use logmnr
- create dictionary
- add logfile
- start analyze
- create table as select * from v$logmnr_contents
- end logmnr
- find exact drop time
[root@11203ora /]# mkdir logmnr [root@11203ora /]# ll total 15240 dr-xr-xr-x. 2 root root 4096 Jul 12 21:30 bin dr-xr-xr-x. 4 root root 4096 Jul 9 21:01 boot drwxr-xr-x. 2 root root 4096 Jun 25 23:04 cgroup drwxr-xr-x 16 root root 3860 Jul 26 12:38 dev drwxr-xr-x. 104 root root 12288 Jul 26 12:38 etc drwxr-xr-x. 3 root root 4096 Jul 9 22:04 home -rwxr-xr-x 1 root root 15485212 Jul 26 08:21 j2re-1_3_1_19-linux-i586.bin dr-xr-xr-x. 13 root root 4096 Jul 12 21:30 lib dr-xr-xr-x. 9 root root 12288 Jul 12 21:30 lib64 drwxr-xr-x 2 root root 4096 Jul 29 12:31 logmnr drwx------. 2 root root 16384 Jul 9 19:01 lost+found drwxr-xr-x. 2 root root 4096 Nov 1 2011 media drwxr-xr-x 2 root root 0 Jul 26 12:38 misc drwxr-xr-x. 2 root root 4096 Nov 1 2011 mnt drwxr-xr-x 2 root root 0 Jul 26 12:38 net drwxr-xr-x. 4 root root 4096 Jul 9 22:27 opt dr-xr-xr-x 125 root root 0 Jul 26 12:38 proc dr-xr-x---. 5 root root 4096 Jul 26 08:57 root dr-xr-xr-x. 2 root root 12288 Jul 12 21:30 sbin drwxr-xr-x. 2 root root 4096 Jul 9 19:02 selinux drwxr-xr-x. 2 root root 4096 Nov 1 2011 srv drwxr-xr-x 13 root root 0 Jul 26 12:38 sys drwxrwxrwt. 4 root root 4096 Jul 29 03:39 tmp drwxrwxr-x 3 oracle oinstall 4096 Jul 9 22:05 u01 drwxr-xr-x. 13 root root 4096 Jul 9 19:03 usr drwxr-xr-x. 21 root root 4096 Jul 9 19:12 var [root@11203ora /]# chown -R oracle:dba /logmnr/ [root@11203ora /]# ll total 15240 dr-xr-xr-x. 2 root root 4096 Jul 12 21:30 bin dr-xr-xr-x. 4 root root 4096 Jul 9 21:01 boot drwxr-xr-x. 2 root root 4096 Jun 25 23:04 cgroup drwxr-xr-x 16 root root 3860 Jul 26 12:38 dev drwxr-xr-x. 104 root root 12288 Jul 26 12:38 etc drwxr-xr-x. 3 root root 4096 Jul 9 22:04 home -rwxr-xr-x 1 root root 15485212 Jul 26 08:21 j2re-1_3_1_19-linux-i586.bin dr-xr-xr-x. 13 root root 4096 Jul 12 21:30 lib dr-xr-xr-x. 9 root root 12288 Jul 12 21:30 lib64 drwxr-xr-x 2 oracle dba 4096 Jul 29 12:31 logmnr drwx------. 2 root root 16384 Jul 9 19:01 lost+found drwxr-xr-x. 2 root root 4096 Nov 1 2011 media drwxr-xr-x 2 root root 0 Jul 26 12:38 misc drwxr-xr-x. 2 root root 4096 Nov 1 2011 mnt drwxr-xr-x 2 root root 0 Jul 26 12:38 net drwxr-xr-x. 4 root root 4096 Jul 9 22:27 opt dr-xr-xr-x 127 root root 0 Jul 26 12:38 proc dr-xr-x---. 5 root root 4096 Jul 26 08:57 root dr-xr-xr-x. 2 root root 12288 Jul 12 21:30 sbin drwxr-xr-x. 2 root root 4096 Jul 9 19:02 selinux drwxr-xr-x. 2 root root 4096 Nov 1 2011 srv drwxr-xr-x 13 root root 0 Jul 26 12:38 sys drwxrwxrwt. 4 root root 4096 Jul 29 03:39 tmp drwxrwxr-x 3 oracle oinstall 4096 Jul 9 22:05 u01 drwxr-xr-x. 13 root root 4096 Jul 9 19:03 usr drwxr-xr-x. 21 root root 4096 Jul 9 19:12 var [root@11203ora /]#
SQL> alter system set utl_file_dir='/logmnr' scope=spfile; System altered. SQL>
[root@11203ora /]# su - oracle 11203ora-> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 29 12:35:19 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immedaite SP2-0717: illegal SHUTDOWN option SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 430075904 bytes Fixed Size 2229064 bytes Variable Size 335547576 bytes Database Buffers 83886080 bytes Redo Buffers 8413184 bytes Database mounted. Database opened. SQL> show parameter utl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ create_stored_outlines string utl_file_dir string /logmnr SQL>
SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/logmnr'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL>
1 begin 2 dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/TESTDB/archivelog/2013_07_29/o1_mf_1_139_8zcx06o8_.arc',options=>dbms_logmnr.new); 3* end; SQL> / PL/SQL procedure successfully completed. SQL>
SQL> select * from v$logmnr_logs; LOG_ID FILENAME LOW_TIME HIGH_TIME DB_ID DB_NAME RESET_SCN RESET_SCN_TIME COMPATIBLE THREAD_ID THREAD_SQN LOW_SCN NEXT_SCN DIC DIC TYPE BLOCKSIZE FILESIZE INFO STATUS ---------- -------------------------------------------------- ------------------- ------------------- ---------- -------- ---------- ------------------- ----------------- ---------- ---------- ---------- ---------- --- --- ------- ---------- ---------- -------------------------------- ---------- 139 /u01/app/oracle/fast_recovery_area/TESTDB/archivel 2013/07/29 12:22:43 2013/07/29 12:48:06 2603690985 TESTDB 995548 2013/07/09 22:34:19 11.2.0.0.0 1 139 2688940 2690749 NO NO ARCHIVE 512 1073152 0 og/2013_07_29/o1_mf_1_139_8zcx06o8_.arc SQL>
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dictionary.ora'); PL/SQL procedure successfully completed. SQL>
SQL> select count(*) from v$logmnr_contents; COUNT(*) ---------- 1627 SQL>
SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 430075904 bytes Fixed Size 2229064 bytes Variable Size 335547576 bytes Database Buffers 83886080 bytes Redo Buffers 8413184 bytes Database mounted. SQL>
11203ora-> rman nocatalog target sys/oracle@testdb Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 29 15:10:53 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (DBID=2603690985, not open) using target database control file instead of recovery catalog RMAN> run{} RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "}": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, convert, copy, crosscheck, delete, duplicate, execute, flashback, host, mount, open, recover, release, repair, report, restore, resync, send, set, show, shutdown, sql, startup, switch, transport, validate, " RMAN-01007: at line 1 column 5 file: standard input RMAN> RMAN> run{ 2> allocate channel d1 device type disk; 3> set until scn 2690511; 4> restore database; 5> recover database; 6> } allocated channel: d1 channel d1: SID=10 device type=DISK executing command: SET until clause Starting restore at 2013/07/29 15:14:41 channel d1: starting datafile backup set restore channel d1: specifying datafile(s) to restore from backup set channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/testdb/system01.dbf channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/testdb/sysaux01.dbf channel d1: restoring datafile 00003 to /u01/app/oracle/oradata/testdb/undotbs01.dbf channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/testdb/users01.dbf channel d1: restoring datafile 00005 to /u01/app/oracle/oradata/testdb/example01.dbf channel d1: restoring datafile 00006 to /u01/app/oracle/oradata/testdb/DATACENTER01.dbf channel d1: restoring datafile 00007 to /u01/app/oracle/oradata/testdb/rman_ts01.dbf channel d1: restoring datafile 00008 to /u01/app/oracle/oradata/testdb/myundotbs01.dbf channel d1: restoring datafile 00009 to /u01/app/oracle/oradata/testdb/testtbs01.dbf channel d1: restoring datafile 00010 to /u01/app/oracle/oradata/testdb/testtbs02.dbf channel d1: restoring datafile 00011 to /u01/app/oracle/oradata/testdb/ts_perf_01.dbf channel d1: reading from backup piece /home/oracle/logmnr/rmanback/inc0_TESTDB_6eofv34u_1_1 channel d1: piece handle=/home/oracle/logmnr/rmanback/inc0_TESTDB_6eofv34u_1_1 tag=INC0 channel d1: restored backup piece 1 channel d1: restore complete, elapsed time: 00:01:45 Finished restore at 2013/07/29 15:16:27 Starting recover at 2013/07/29 15:16:27 starting media recovery media recovery complete, elapsed time: 00:00:02 Finished recover at 2013/07/29 15:16:30 released channel: d1 RMAN>
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open resetlogs; Database altered. SQL>
SQL> conn ARCER/ARCER Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- COILS_ASSETS_DEPRECIATION TABLE COILS_CODE_ASSESS TABLE COILS_COMPANY_LICENSE TABLE COILS_EMP TABLE COILS_LOG_ASSESS TABLE COILS_STORES TABLE COMPANY_ANNOUNCEMENT TABLE CRM_CUSTOMER TABLE FGPS_EMPLOYEE TABLE FGPS_FINANCEUNIT TABLE FGPS_GROUP TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FGPS_GROUP_VS_MENU TABLE FGPS_MENU TABLE FGPS_MYRECEIVER TABLE FGPS_PEOPLE TABLE FGPS_SYSTEMUSER TABLE FGPS_USER TABLE FGPS_USERGRANTACCOUNT TABLE FGPS_VOUCHER TABLE FRX_DEV_SUGGEST TABLE FRX_OA_ATTENDANCE TABLE FRX_OA_DICT TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FRX_OA_LOG_TEMPLATE TABLE FRX_OA_WORKLOG TABLE INVESTMENT_INCOME_STATEMENT TABLE NETSTORE TABLE NETSTORE_INCOMEEXPENDITURELIST TABLE RECHARGE_SUM_VW VIEW SYSTEM_GROUP TABLE SYSTEM_MENU TABLE SYS_CATEGORY TABLE SYS_GROUP_MENU_CORRESPONDENCE TABLE TEST3 TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- WAITTINGHANDLER TABLE WAITTING_HANDLER_DETAIL TABLE 35 rows selected. SQL>
SQL> select * from test3; COL1 --------------- first row second row SQL>
select scn,username,seg_name,seg_owner,seg_type_name,timestamp,operation,sql_redo,sql_undo from hr.tb_logmnr where operation = 'DDL';
Summary
- V$LOGMNR_CONTENTS是一张非常重要的表，在分析日志信息是所要使用到的. 这张表中使用到几个重要的字段
- scn
- username
- seg_name
- seg_owner
- seg_type_name
- tiemstamp
- operation
- incomplete recover database缺点:丢失时间点之后的改动.