使用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表就找回来了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16400082/viewspace-701224/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16400082/viewspace-701224/