oracle闪回技术总结之闪回数据库

页面导航:首页 > 数据库 > oracle > oracle闪回技术总结之闪回数据库

oracle闪回技术总结之闪回数据库

来源: 作者: 时间:2016-01-20 09:40 【

[实施步骤]步骤一、设置如下的初始化参数:Sqlalter system set db_recovery_file_dest_size = 2G scope=both;System altered.Sqlalter systemset db_recovery_file_dest=#39;/u01/apps/oracle/flash...
[实施步骤]
步骤一、设置如下的初始化参数:
 
Sql>alter system set db_recovery_file_dest_size = 2G  scope=both;
System altered.
 
Sql>alter systemset db_recovery_file_dest='/u01/apps/oracle/flash_recovery_area'scope=both;
System altered.
步骤二 设置重现保持目标为24小时:以分钟为单位
SQL> alter system setdb_flashback_retention_target = 1440 scope=both;
Systemaltered.
步骤三  正常关闭,启动到mount状态,激活闪回功能后打开数据库 
19:27:38SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
19:28:19SQL> startup mount;
ORACLEinstance started.
TotalSystem Global Area 2415919104 bytes
FixedSize                  2022768 bytes
VariableSize             553648784 bytes
DatabaseBuffers         1845493760 bytes
RedoBuffers               14753792 bytes
Databasemounted.
SQL> alter database archivelog;
           Database altered.
SQL>!mkdir /u01/apps/archive_log
SQL> alter system setlog_archive_dest_1='location=/u01/apps/archive_log';
Systemaltered.
SQL> alter database flashback on;
Databasealtered.
SQL> alter database open;
  Databasealtered.
SQL> select flashback_on from v$database;
  FLASHBACK_ON
  ------------------
  YES
步骤四  监控FRA信息
SQL>col name for a55
SQL> select name,space_limit,space_used, space_reclaimable fromv$recovery_file_dest;
 
NAME                                                        SPACE_LIMITSPACE_USED SPACE_RECLAIMABLE
------------------------------------------------------------------ ---------- -----------------
/u01/apps/oracle/flash_recovery_area                        2147483648    8192000                       0
 
1 rowselected.
步骤五   开启日志挖掘功能,日志挖掘logmnr ,scn和时间的挖掘
Sql>Alterdatabase   add  supplemental  log data;
创建数据字典,疑问(数据字典什么时间点开始建,)
SQL> execdbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs)
PL/SQL proceduresuccessfully completed.
查看数据字典创作在哪个日志里
SQL> selectname,sequence#,first_change#,dictionary_begin,dictionary_end fromv$archived_log;
NAME                                               SEQUENCE# FIRST_CHANGE# DIC DIC
/arch2/1_2_822244495.dbf                                    2        646310 YES YES
步骤六   开始有事务操作
SQL> conn scott/tiger
SQL>create table test as select * from emp;
insertinto test select * from emp;
 insert into test select * from emp;
 insert into test select * from emp;
 commit;
 update test set ename ='helei';
 rollback;
 delete from test where deptno =20;
 commit;
 
 
SQL> create table emp2 as select * from emp;
Tablecreated.
步骤七  此时不小心误删除了一张表,直到发现表被误删除
SQL> drop table emp2 purge;
Tabledropped.
SQL> set time on;
21:52:14SQL>
表被删除时间大概在21:52分左右
此时事务还在继续运行
22点00分左右发现表被删除
切了几个归档
22:00:38SQL> alter system switch logfile;
System altered.
 
步骤八  开始日志挖掘出表变删除时的SCN及时间戳
再次查看归档日志信息及创建的数据字典信息
17:53:39 SQL> select name,sequence#,first_change#,to_char(first_time,'yyyy-mm-ddhh24:mi:ss'),dictionary_begin,dictionary_end from v$archived_log;
22:01:25SQL>  select name,sequence#,first_change#,to_char(first_time,'yyyy-mm-ddhh24:mi:ss'),dictionary_begin,dictionary_end from v$archived_log;
 
NAME                                                        SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME, DIC DIC
----------------------------------------------------------------- ------------- ------------------- --- ---
/u01/apps/archive_log/1_5_896643434.dbf                        5         1011817 2015-11-24 19:38:27NO  NO
/u01/apps/archive_log/1_6_896643434.dbf                        6         1018525 2015-11-24 21:39:09YES YES
/u01/apps/archive_log/1_7_896643434.dbf                        7         1019401 2015-11-24 21:39:11NO  NO
/u01/apps/archive_log/1_8_896643434.dbf                        8         1020367 2015-11-24 22:00:36NO  NO
 
 
开始正式日志挖掘:
22:03:13SQL> exec dbms_logmnr.add_logfile(logfilename =>'/u01/apps/archive_log/1_6_896643434.dbf',options=>dbms_logmnr.new)---添加包含数据字典的日志
PL/SQL proceduresuccessfully completed.
 
由于表被drop的时间大概是0点29分左右,drop可能包含sequence 7,8对其进行挖掘
22:04:15SQL> execdbms_logmnr.add_logfile(logfilename=>'/u01/apps/archive_log/1_7_896643434.dbf',options=>dbms_logmnr.addfile)
PL/SQL proceduresuccessfully completed.
22:05:31SQL> execdbms_logmnr.add_logfile(logfilename=>'/u01/apps/archive_log/1_8_896643434.dbf',options=>dbms_logmnr.addfile)
PL/SQL proceduresuccessfully completed.
22:06:05 SQL> exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_redo_logs);
PL/SQLprocedure successfully completed.
22:07:01 SQL> alter session set nls_date_format='yyyy_mm_ddhh24:mi:ss';
Sessionaltered.
18:08:38 SQL> col sql_redo for a40
挖掘完成,查看成果,找到被DROP时的确切信息
22:07:05SQL>  select  scn,timestamp,sql_redo   from v$logmnr_contents  Where  seg_owner='SCOTT'  and  seg_name ='EMP2';
 
       SCNTIMESTAMP               SQL_REDO
----------------------------- ----------------------------------------
   1019443 2015_11_24 21:40:33 create tableemp2 as select * from emp;
   10198002015_11_24 21:52:11 drop table emp2 purge;
Sql>execdbms_logmnr.end_logmnr; 
步骤九  开始闪回数据库
正常关闭数据库,保持数据文件的一致性
18:42:03 SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
18:43:17 SQL> startup mount;
ORACLEinstance started.
TotalSystem Global Area 2415919104 bytes
FixedSize                  2022768 bytes
VariableSize             553648784 bytes
DatabaseBuffers         1845493760 bytes
RedoBuffers               14753792 bytes
Databasemounted.
22:08:43SQL> flashback database to scn 1019800;
Flashback complete.
 
或者flashback database to timestamp to_timestamp('2015-11-25 21:52:10','yyyy-mm-ddhh24:mi:ss');
Flashbackcomplete.
[[email protected] ~]$  tail–f  /u01/app/oracle/admin/test/bdump/alert_test.log
[[email protected]~]$ tail -f /u01/apps/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
FlashbackRestore Start
FlashbackRestore Complete
FlashbackMedia Recovery Start
SerialMedia Recovery started
FlashbackMedia Recovery Log /u01/apps/archive_log/1_5_896643434.dbf
FlashbackMedia Recovery Log /u01/apps/archive_log/1_6_896643434.dbf
FlashbackMedia Recovery Log /u01/apps/archive_log/1_7_896643434.dbf
IncompleteRecovery applied until change 1019801 time 11/24/2015 21:52:11
FlashbackMedia Recovery Complete
Completed:flashback database to scn 1019800
闪回成功,查看新数据文件和控制文件中的检查点信息
18:49:04 SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
----------------------------
         1             1019801
         2             1019801
         3             1019801
         4             1019801
         5             1019801
18:49:57 SQL> select file#,checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
----------------------------
         1             1019801
         2             1019801
         3             1019801
         4             1019801
         5             1019801
步骤十  以redo  only 方式打开数据库
22:11:42 SQL> alter database open read only;
Databasealtered.
22:11:49 SQL> conn scott/tiger
Connected.
22:11:54 SQL> select * from tab;
TNAME                              TABTYPE        CLUSTERID
------------------------------------- ----------
BONUS                               TABLE
DEPT                               TABLE
EMP                               TABLE
EMP2                               TABLE
SALGRADE                       TABLE
TEST                               TABLE
6 rowsselected.
 
步骤十一  导出scott用户下的emp2表
[[email protected] bin]$ exp scott/tigerfile=/u01/apps/scott.ep2 log=scottep2.log indexes=n  tables=emp2
Export:Release 11.2.0.1.0 - Production on Tue Nov 24 22:13:48 2015
Copyright(c) 1982, 2009, and/or its affiliates. All rights reserved.
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Exportdone in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note:indexes on tables will not be exported
About toexport specified tables via Conventional Path ...
. .exporting table                          EMP2         14 rows exported
Exportterminated successfully without warnings.
 
步骤十二  正常关闭数据库,启动到mount状态对数据库进行完全恢复
22:14:43 SQL> shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
22:14:48 SQL> startup mount;
ORACLEinstance started.
 
TotalSystem Global Area  780824576 bytes
FixedSize                    2217424 bytes
VariableSize                  465570352 bytes
DatabaseBuffers          310378496 bytes
RedoBuffers                    2658304 bytes
Databasemounted.
22:14:56 SQL> recover database;
ORA-00279:change 1019801 generated at 11/24/2015 21:39:11 needed for thread 1
ORA-00289:suggestion : /u01/apps/archive_log/1_7_896643434.dbf
ORA-00280:change 1019801 for thread 1 is in sequence #7
 
 
22:15:32Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279:change 1020367 generated at 11/24/2015 22:00:36 needed for thread 1
ORA-00289:suggestion : /u01/apps/archive_log/1_8_896643434.dbf
ORA-00280:change 1020367 for thread 1 is in sequence #8
 
 
Logapplied.
Mediarecovery complete.
跟踪日志中的信息
[[email protected]]$ tail -f /u01/apps/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
ALTERDATABASE RECOVER    CONTINUE DEFAULT 
MediaRecovery Log /u01/apps/archive_log/1_8_896643434.dbf
Recoveryof Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0:/u01/apps/oracle/oradata/orcl/redo03.log
Recoveryof Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0:/u01/apps/oracle/oradata/orcl/redo01.log
Recoveryof Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
  Mem# 0:/u01/apps/oracle/oradata/orcl/redo02.log
MediaRecovery Complete (orcl)
Completed:ALTER DATABASE RECOVER    CONTINUEDEFAULT
步骤十三  打开数据库,数据库正常开启
Mediarecovery complete.
22:15:52 SQL> alter database open;
 
Databasealtered.
 
22:16:57 SQL> conn scott/tiger
Connected.
22:18:42 SQL> select * from tab;
 
TNAME                              TABTYPE        CLUSTERID
------------------------------------- ----------
BONUS                               TABLE
DEPT                               TABLE
EMP                               TABLE
SALGRADE                       TABLE
TEST        
    查看scott用户下的表,emp2不存在,正常,因为在归档中记录了emp2被drop掉,恢复的时候使用归档完全恢复中,emp2还是会被drop掉
22:19:26 SQL> select file#,checkpoint_change#from v$datafile;
 
     FILE# CHECKPOINT_CHANGE#
----------------------------
 1              1033196
 2              1033196
 3              1033196
 4              1033196
 5              1033196
22:19:27 SQL> select file#,checkpoint_change#from v$datafile_header;
 
     FILE# CHECKPOINT_CHANGE#
----------------------------
 1              1033196
 2              1033196
 3              1033196
 4              1033196
 5              1033196
步骤十四  导入scott下emp2表
[[email protected] bin]$imp scott/tigerfile=/u01/apps/scott.ep2 tables=emp2 fromuser=scott touser=scott ignore=y
 
Import:Release 11.2.0.1.0 - Production on Tue Nov 24 22:24:30 2015
 
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 
 
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
With thePartitioning, OLAP, Data Mining and Real Application Testing options
 
Exportfile created by EXPORT:V11.02.00 via conventional path
importdone in ZHS16GBK character set and AL16UTF16 NCHAR character set
.importing SCOTT's objects into SCOTT
. .importing table                        "EMP2"         14 rowsimported
Import terminated successfully without warnings.
 
查询
22:26:08 SQL> select * from tab;
 
TNAME                              TABTYPE        CLUSTERID
------------------------------------- ----------
BONUS                               TABLE
DEPT                               TABLE
EMP                               TABLE
EMP2                               TABLE
SALGRADE                       TABLE
TEST                               TABLE
 
6 rowsselected.
基于SCN和时间戳的flash database 成功完成
 
2、基于restorepoint  还原点的闪回数据库
步骤一、创建restorepoint
先创建一张dept1表
SQL> createtable dept1 as select * from dept;                                    
Table created.
查看当前信息
SQL> select *from tab;                                                           
TNAME                          TABTYPE  CLUSTERID
------------------------------------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT1                          TABLE
EMP                            TABLE
EMP2                           TABLE
SALGRADE                       TABLE
6 rows selected.
创建restorepoint
SQL>  create restore point rs_pt;                                                
Restore point created.
在创建一张dept2表
SQL> connscott/tiger
SQL> createtable dept2 as select * from dept;                                    
Table created.
步骤二、不小心drop掉dept1
SQL> drop tabledept1 purge;                                                      
Table dropped.
SQL> select *from tab;                                                           
TNAME                          TABTYPE  CLUSTERID
------------------------------------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT2                          TABLE
EMP                            TABLE
EMP2                           TABLE
SALGRADE                       TABLE
6 rows selected.
步骤三、开始基于restorepoint闪回数据库
SQL> col name fora20                                                          
SQL>col time for a35                                                            
SQL> selectname,scn,time from v$restore_point;                                   
NAME                            SCN TIME
------------------------------ -----------------------------------
RS_PT                        103371424-NOV-15 10.27.58.000000000 PM
SQL> shutdownimmediate
SQL>startupmount
SQL>flashbackdatabase to restore point rs_pt;
#flashback databaseto timestamp sysdate-15/1440; (当前的时间之前的 15 分钟)
 
                                 
Flashback complete.
步骤四、以readonly 方式打开数据库
SQL> alterdatabase open read only;                                               
Database altered.
                               
SQL> connscott/tiger                                                     
Connected.
SQL> select *from tab;                                                           
TNAME                          TABTYPE  CLUSTERID
------------------------------------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT1                          TABLE
EMP                            TABLE
EMP2                           TABLE
SALGRADE                       TABLE
6 rows selected.
步骤五、导出dept1表
[[email protected] bin]$exp scott/tiger file=/u01/apps/scott.dp1 log=scottdp1.logindexes=n  tables=dept1
Export:Release 11.2.0.1.0 - Production on Tue Nov 24 22:33:53 2015
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Exportdone in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note:indexes on tables will not be exported
About toexport specified tables via Conventional Path ...
. .exporting table                         DEPT1          4 rows exported
Exportterminated successfully without warnings.
22:34:51 SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
 
22:35:13 SQL> startup mount;
ORACLEinstance started.
 
TotalSystem Global Area  780824576 bytes
FixedSize                    2217424 bytes
VariableSize                  465570352 bytes
DatabaseBuffers          310378496 bytes
RedoBuffers                    2658304 bytes
Databasemounted.
步骤六、恢复数据库recoverdatabase
22:35:37 SQL>recover database;
Mediarecovery complete.
步骤七、打开数据库
22:35:40 SQL>alterdatabase open;
 
Databasealtered.
 
22:35:47 SQL> conn scott/tiger
Connected.
22:36:01 SQL> select *from tab;
 
TNAME                              TABTYPE        CLUSTERID
------------------------------------- ----------
BONUS                               TABLE
DEPT                               TABLE
DEPT2                               TABLE
EMP                               TABLE
EMP2                               TABLE
SALGRADE                       TABLE
TEST                               TABLE
 
7 rowsselected.
 
 
步骤八、导入dept1表
[[email protected] bin]$impscott/tiger file=/u01/apps/scott.dp1 tables=dept1 fromuser=scott touser=scottignore=y
Import:Release 11.2.0.1.0 - Production on Tue Nov 24 22:39:06 2015
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Exportfile created by EXPORT:V11.02.00 via conventional path
importdone in ZHS16GBK character set and AL16UTF16 NCHAR character set
.importing SCOTT's objects into SCOTT
. .importing table                       "DEPT1"          4 rowsimported
Importterminated successfully without warnings.
 
22:36:05 SQL> select * from tab;
 
TNAME                              TABTYPE        CLUSTERID
------------------------------------- ----------
BONUS                               TABLE
DEPT                               TABLE
DEPT1                               TABLE
DEPT2                               TABLE
EMP                               TABLE
EMP2                               TABLE
SALGRADE                       TABLE
TEST                               TABLE
 
8 rowsselected.
闪回数据库完成
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<