使用logmnr分析歸檔日誌恢復被drop掉的資料表

1、全備資料庫

 [oracle@oracle9idemo ~]$ rman target / nocatalog

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: DENVER (DBID=4041114247)
using target database controlfile instead of recovery catalog

RMAN> list backup;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5       59M        DISK        00:00:18     16-JUN-11     
        BP Key: 5   Status: AVAILABLE   Tag: TAG20110616T152006
        Piece Name: /u01/ubackup/archive_06mf1m57_1_1

  List of Archived Logs in backup set 5
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1073816811 16-JUN-11 1073889017 16-JUN-11

RMAN> delete backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=21 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5       5       1   1   AVAILABLE   DISK        /u01/ubackup/archive_06mf1m57_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/ubackup/archive_06mf1m57_1_1 recid=5 stamp=753981608
Deleted 1 objects

RMAN> run
2> {
3>  backup database format '/u01/ubackup/hot/wdb_i_%U';
4> }

Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/u01/oradata/denver/system01.dbf
input datafile fno=00002 name=/u01/oradata/denver/undotbs01.dbf
input datafile fno=00005 name=/u01/oradata/denver/example01.dbf
input datafile fno=00011 name=/u01/oradata/denver/app1_01.dbf
input datafile fno=00010 name=/u01/oradata/denver/xdb01.dbf
input datafile fno=00006 name=/u01/oradata/denver/indx01.dbf
input datafile fno=00009 name=/u01/oradata/denver/users01.dbf
input datafile fno=00003 name=/u01/oradata/denver/cwmlite01.dbf
input datafile fno=00004 name=/u01/oradata/denver/drsys01.dbf
input datafile fno=00007 name=/u01/oradata/denver/odm01.dbf
input datafile fno=00008 name=/u01/oradata/denver/tools01.dbf
input datafile fno=00012 name=/u01/oradata/denver/app2_01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-11
channel ORA_DISK_1: finished piece 1 at 27-JUN-11
piece handle=/u01/ubackup/hot/wdb_i_07mfuqvk_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:02
Finished backup at 27-JUN-11

RMAN> quit

Recovery Manager complete.
[oracle@oracle9idemo ~]$

 

2、建立logmnr目錄

[oracle@oracle9idemo ~]$ mkdir /u01/oracle/logmnr

 

3、修改該spfile引數：utl_file_dir

SQL> alter system set utl_file_dir='/u01/oracle/logmnr' scope=spfile;

System altered.

SQL> ! cat  /u01/oracle/dbs/spfiledenver.ora
Žímÿ,*._allow_resetlogs_corruption=TRUE
*.aq_tm_processes=1
*.audit_trail='DB'
*.background_dump_dest='/u01/admin/denver/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/oradata/denver/control01.ctl','/u01/oradata/denver/control02.ctl','/u01/oradata/denver/control03.ctl'
*.core_dump_dest='/u01/admin/denver/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='denver'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=denverXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='denver'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/u01/oradata/denver/archive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/denver/udump'
*.utl_file_dir='/u01/oracle/logmnr'

SQL>

spfile裡的utl_file_dir的路徑已經設定成功，等下次重新啟動才能生效

 

4、發生誤操作

SQL> SELECT * FROM t;

        ID NAME
---------- ----------
         0 xiaowang
         1 xiaowang1
         2 xiaowang2

SQL> DROP TABLE t;

Table dropped.

SQL>

 

5、做日誌切換，產生歸檔檔案

SQL> alter system switch logfile;

System altered.

SQL>

[oracle@oracle9idemo archive]$ ll
total 654160
-rw-r-----  1 oracle oinstall   1173504 Jun 27 14:43 1_10.dbf
-rw-r-----  1 oracle oinstall   1160704 Jun 27 15:08 1_1.dbf
-rw-r-----  1 oracle oinstall   6386176 Jun 27 16:50 1_2.dbf
-rw-r-----  1 oracle oinstall 104857088 Jun 25 14:17 1_3.dbf
-rw-r-----  1 oracle oinstall 104857088 Jun 25 14:18 1_4.dbf
-rw-r-----  1 oracle oinstall 104857088 Jun 25 14:18 1_5.dbf
-rw-r-----  1 oracle oinstall 104857088 Jun 25 14:46 1_6.dbf
-rw-r-----  1 oracle oinstall 104857088 Jun 25 14:47 1_7.dbf
-rw-r-----  1 oracle oinstall  89633792 Jun 26 13:14 1_8.dbf
-rw-r-----  1 oracle oinstall  46505984 Jun 27 14:24 1_9.dbf

如上：產生的歸檔檔案為-rw-r-----  1 oracle oinstall   6386176 Jun 27 16:50 1_2.dbf

 

6、使用logmnr(建立字典檔案存放位置，否則無法讀懂日誌檔案)

SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/u01/oracle/logmnr');
BEGIN dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/u01/oracle/logmnr'); END;

*
ERROR at line 1:
ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 931
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2016
ORA-06512: at line 1

SQL>

在這裡需要重啟資料庫：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/u01/oracle/logmnr');

PL/SQL procedure successfully completed.

SQL>

7、新增歸檔日誌(誤操作時的歸檔日誌)

SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/u01/oradata/denver/archive/1_2.dbf',Options=>dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

SQL>

 

8、檢視v$logmnr_logs記錄(檢視記錄是否被新增)

SQL> SELECT filename,db_name,info ,status FROM v$logmnr_logs;

FILENAME                       DB_NAME  INFO                     STATUS
------------------------------ -------- -------------------- ----------
/u01/oradata/denver/archive/1_ DENVER                                 8
2.dbf

SQL> col filename format a40
SQL> /

FILENAME                                 DB_NAME  INFO                     STATUS
---------------------------------------- -------- -------------------- ----------
/u01/oradata/denver/archive/1_2.dbf      DENVER                                 8

9、分析歸檔日誌

SQL> execute dbms_logmnr.start_logmnr(DictFileName => '/u01/oracle/logmnr/dictionary.ora');

PL/SQL procedure successfully completed.

SQL>

 

10、檢視v$logmnr_contents記錄

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
     30476

 

11、建立一張表來儲存v$logmnr_contents中的資料

SQL> Create table tb_logmnr as select * from v$logmnr_contents;

Table created.
SQL>

 

12、使用Toad或Plsql developer 檢視tb_logmnr，找出scn號

select * from tb_logmnr where peration='DDL';

找到一個sql欄位：drop table t;

其scn號為：            1074413334

 

13、使用rman 做不完全恢復：

先關閉資料庫：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

再用rman恢復：

[oracle@oracle9idemo ~]$ rman target / nocatalog

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: denver (not mounted)
using target database controlfile instead of recovery catalog

RMAN> run
2> {
3> startup force mount;         
4> allocate channel c1 device type disk;
5> set until scn 1074413334;
6> restore database;
7> recover database;
8> alter database open resetlogs;
9> }

Oracle instance started
database mounted

Total System Global Area     236000356 bytes

Fixed Size                      451684 bytes
Variable Size                201326592 bytes
Database Buffers              33554432 bytes
Redo Buffers                    667648 bytes

allocated channel: c1
channel c1: sid=13 devtype=DISK

executing command: SET until clause

Starting restore at 27-JUN-11

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/denver/system01.dbf
restoring datafile 00002 to /u01/oradata/denver/undotbs01.dbf
restoring datafile 00003 to /u01/oradata/denver/cwmlite01.dbf
restoring datafile 00004 to /u01/oradata/denver/drsys01.dbf
restoring datafile 00005 to /u01/oradata/denver/example01.dbf
restoring datafile 00006 to /u01/oradata/denver/indx01.dbf
restoring datafile 00007 to /u01/oradata/denver/odm01.dbf
restoring datafile 00008 to /u01/oradata/denver/tools01.dbf
restoring datafile 00009 to /u01/oradata/denver/users01.dbf
restoring datafile 00010 to /u01/oradata/denver/xdb01.dbf
restoring datafile 00011 to /u01/oradata/denver/app1_01.dbf
restoring datafile 00012 to /u01/oradata/denver/app2_01.dbf
channel c1: restored backup piece 1
piece handle=/u01/ubackup/hot/wdb_i_07mfuqvk_1_1 tag=TAG20110627T164020 params=NULL
channel c1: restore complete
Finished restore at 27-JUN-11

Starting recover at 27-JUN-11

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/oradata/denver/archive/1_2.dbf
archive log filename=/u01/oradata/denver/archive/1_2.dbf thread=1 sequence=2
media recovery complete
Finished recover at 27-JUN-11

database opened
released channel: c1

RMAN>

 

14、用usr1使用者進去查詢表t是否找回來了

SQL> conn usr1/usr1
Connected.
SQL> SELECT * FROM T;

        ID NAME
---------- ----------
         0 xiaowang
         1 xiaowang1
         2 xiaowang2

SQL>

 

至此t表就找回來了