ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用logmnr分析归档日志恢复被drop掉的数据表

使用logmnr分析归档日志恢复被drop掉的数据表

原创 Linux操作系统 作者:tian1982tian 时间:2011-07-03 09:55:50 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    165265