不完全恢复(基于时间)

最新推荐文章于 2021-04-06 01:28:03 发布

转载最新推荐文章于 2021-04-06 01:28:03 发布 · 123 阅读

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

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#
---------- ------------------
        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/，如需转载，请注明出处，否则将追究法律责任。

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