ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 不完全恢复(基于时间)

不完全恢复(基于时间)

原创 Linux操作系统 作者:tian1982tian 时间:2013-01-19 11:06:55 0 删除 编辑
SQL> conn usr1/usr1
Connected.
SQL> create table t (id int ,name varchar2(100));
Table created.
SQL> insert into t values (1,'yejun');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 INACTIVE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
关闭数据库后拷贝文件冷备
[oracle@oracle9ivm ora10g]$ cp * /u01/cold/
cp: omitting directory `arch'
[oracle@oracle9ivm ora10g]$ ll /u01/cold/
total 1484372
-rw-r-----  1 oracle oinstall 104865792 Jan 17 13:37 app1_01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jan 17 13:38 app2_01.dbf
-rw-r-----  1 oracle oinstall   5251072 Jan 17 13:38 app3_01.dbf
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:38 control01.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:38 control02.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:38 control03.ctl
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:38 cwmlite01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:38 drsys01.dbf
-rw-r-----  1 oracle oinstall 156639232 Jan 17 13:38 example01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jan 17 13:38 indx01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:38 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:38 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:38 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:38 redo03.log
-rw-r-----  1 oracle oinstall 534781952 Jan 17 13:38 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:39 temp01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jan 17 13:39 tools01.dbf
-rw-r-----  1 oracle oinstall 209723392 Jan 17 13:39 undotbs01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jan 17 13:39 users01.dbf
-rw-r-----  1 oracle oinstall  47194112 Jan 17 13:39 xdb01.dbf
SQL> !df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              79G  9.9G   65G  14% /
none                  252M     0  252M   0% /dev/shm
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> conn usr1/usr1
Connected.
SQL> select * from t;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 yejun
SQL> insert into t values (2,'junye');
1 row created.
SQL> conn / as sysdba
Connected.
SQL> conn usr1/usr1
Connected.
SQL> select * from t;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 yejun
         2 junye
SQL> select sysdate from dual;
SYSDATE
---------
17-JAN-10
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') t from dual;
T
-------------------
2010-01-17 13:49:55
SQL> drop table t;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
将删除表的时间记下
[oracle@oracle9ivm ora10g]$ rm -f *.dbf
[oracle@oracle9ivm ora10g]$ ll
total 314236
drwxr-xr-x  2 oracle oinstall      4096 Jan 17 13:34 arch
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control01.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control02.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control03.ctl
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:46 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:50 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:46 redo03.log
删除原目录下的数据文件,将冷备的数据文件拷回
[oracle@oracle9ivm ora10g]$ cp /u01/cold/*.dbf .
[oracle@oracle9ivm ora10g]$ ll
total 1484376
-rw-r-----  1 oracle oinstall 104865792 Jan 17 13:51 app1_01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jan 17 13:51 app2_01.dbf
-rw-r-----  1 oracle oinstall   5251072 Jan 17 13:51 app3_01.dbf
drwxr-xr-x  2 oracle oinstall      4096 Jan 17 13:34 arch
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control01.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control02.ctl
-rw-r-----  1 oracle oinstall   2285568 Jan 17 13:50 control03.ctl
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:51 cwmlite01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:51 drsys01.dbf
-rw-r-----  1 oracle oinstall 156639232 Jan 17 13:51 example01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jan 17 13:51 indx01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:51 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:46 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:50 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Jan 17 13:46 redo03.log
-rw-r-----  1 oracle oinstall 534781952 Jan 17 13:51 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Jan 17 13:51 temp01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jan 17 13:51 tools01.dbf
-rw-r-----  1 oracle oinstall 209723392 Jan 17 13:52 undotbs01.dbf
-rw-r-----  1 oracle oinstall  26222592 Jan 17 13:52 users01.dbf
-rw-r-----  1 oracle oinstall  47194112 Jan 17 13:52 xdb01.dbf
[oracle@oracle9ivm ora10g]$
SQL> startup mount
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.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@oracle9ivm ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Jan 17 13:54:41 2010
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn / as sysdba
Connected.
在mount状态查看数据文件头和控制文件里的checkpoit号,控制文件的早
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             732171
         2             732171
         3             732171
         4             732171
         5             732171
         6             732171
         7             732171
         8             732171
         9             732171
        10             732171
        11             732171
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        12             732171
        13             732171
13 rows selected.
SQL> desc v$datafile_header;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#                                              NUMBER
 STATUS                                             VARCHAR2(7)
 ERROR                                              VARCHAR2(18)
 FORMAT                                             NUMBER
 RECOVER                                            VARCHAR2(3)
 FUZZY                                              VARCHAR2(3)
 CREATION_CHANGE#                                   NUMBER
 CREATION_TIME                                      DATE
 TABLESPACE_NAME                                    VARCHAR2(30)
 TS#                                                NUMBER
 RFILE#                                             NUMBER
 RESETLOGS_CHANGE#                                  NUMBER
 RESETLOGS_TIME                                     DATE
 CHECKPOINT_CHANGE#                                 NUMBER
 CHECKPOINT_TIME                                    DATE
 CHECKPOINT_COUNT                                   NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 NAME                                               VARCHAR2(513)
SQL> select file#,checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             732170
         2             732170
         3             732170
         4             732170
         5             732170
         6             732170
         7             732170
         8             732170
         9             732170
        10             732170
        11             732170
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        12             732170
        13             732170
13 rows selected.
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             732171
         2             732171
         3             732171
         4             732171
         5             732171
         6             732171
         7             732171
         8             732171
         9             732171
        10             732171
        11             732171
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        12             732171
        13             732171
13 rows selected.
SQL> recover database until time '2010-01-17 13:49:55';
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> set linesize 300
恢复重置日志之后,日志序号将变化
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0  104857600          1 YES UNUSED                       0
         2          1          1  104857600          1 NO  CURRENT                 732740 17-JAN-10
         3          1          0  104857600          1 YES UNUSED                       0
SQL> conn usr1/usr1
selConnected.
SQL> select * from t;
SP2-0734: unknown command beginning "selselect ..." - rest of line ignored.
SQL> select * from t;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 yejun
         2 junye
SQL>
从以上可以看到两条数据回来了

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

上一篇: 好久没写博客了
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    163889