oracle 时间点,Oracle基于时间点的恢复

最新推荐文章于 2023-12-01 10:01:03 发布

王日山

最新推荐文章于 2023-12-01 10:01:03 发布

阅读量511

点赞数

文章标签： oracle 时间点

Oracle基于时间点的恢复，在数据库日常维护中，对于处理用户误删除操作等有很好的恢复作用，本实验就是对数据库基于时间点恢复的测试。

[oracle@node0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 15:35:33 2014

Connected to an idle instance.

SQL> show parameter instance_name;

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 511708752 bytes

Database Buffers 331350016 bytes

Redo Buffers 5132288 bytes

Database mounted.

Database opened.

首先对数据库进行一个0级备份

RMAN> list backup;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN> run

2> {

3> allocate channel d1 type disk;

4> allocate channel d2 type disk;

5> backup incremental level=0 database include current controlfile plus archivelog all;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "all": expecting one of: "auxiliary, channel, delete, diskratio, filesperset, force, format, from, include, keep, maxsetsize, noexclude, nokeep, not, pool, reuse, section, skip readonly, skip, tag, to, ;"

RMAN-01007: at line 5 column 81 file: standard input

RMAN> run

2> {

3> allocate channel d1 type disk;

4> allocate channel d2 type disk;

5> backup incremental level=0 database include current controlfile plus archivelog;

6> release channel d1;

7> release channel d2;

8> }

allocated channel: d1

channel d1: SID=44 device type=DISK

allocated channel: d2

channel d2: SID=47 device type=DISK

Starting backup at 08-JUL-14

current log archived

channel d1: starting archived log backup set

channel d1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=852391155

channel d1: starting piece 1 at 08-JUL-14

channel d2: starting archived log backup set

channel d2: specifying archived log(s) in backup set

input archived log thread=1 sequence=4 RECID=2 STAMP=852391353

channel d2: starting piece 1 at 08-JUL-14

channel d1: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152233_9vq71tfm_.bkp tag=TAG20140708T152233 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:04

channel d2: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152233_9vq71thd_.bkp tag=TAG20140708T152233 comment=NONE

channel d2: backup set complete, elapsed time: 00:00:04

Finished backup at 08-JUL-14

Starting backup at 08-JUL-14

channel d1: starting incremental level 0 datafile backup set

channel d1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/hnjy/system01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/hnjy/users01.dbf

channel d1: starting piece 1 at 08-JUL-14

channel d2: starting incremental level 0 datafile backup set

channel d2: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/hnjy/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/hnjy/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/hnjy/undotbs01.dbf

channel d2: starting piece 1 at 08-JUL-14

channel d1: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238 comment=NONE

channel d1: backup set complete, elapsed time: 00:02:41

channel d1: starting incremental level 0 datafile backup set

channel d1: specifying datafile(s) in backup set

including current control file in backup set

channel d1: starting piece 1 at 08-JUL-14

channel d2: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238 comment=NONE

channel d2: backup set complete, elapsed time: 00:02:58

channel d2: starting incremental level 0 datafile backup set

channel d2: specifying datafile(s) in backup set

including current SPFILE in backup set

channel d2: starting piece 1 at 08-JUL-14

channel d1: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_ncnn0_TAG20140708T152238_9vq77jxb_.bkp tag=TAG20140708T152238 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:03

channel d2: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnsn0_TAG20140708T152238_9vq77mcs_.bkp tag=TAG20140708T152238 comment=NONE

channel d2: backup set complete, elapsed time: 00:00:00

Finished backup at 08-JUL-14

Starting backup at 08-JUL-14

current log archived

channel d1: starting archived log backup set

channel d1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=3 STAMP=852391540

channel d1: starting piece 1 at 08-JUL-14

channel d1: finished piece 1 at 08-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152540_9vq77n7h_.bkp tag=TAG20140708T152540 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-JUL-14

released channel: d1

released channel: d2

记录下在开始操作之前的时间SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME

-------------------

2014-07-08 15:37:59

SQL> conn scott/tiger

Connected.

查看scott用户下面的表

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

SALGRADE USERS

BONUS USERS

EMP USERS

DEPT USERS

创建一个test表进行测试

SQL> create table test(name char);

Table created.

插入一条数据

SQL> insert into test values('a');

1 row created.

SQL> select * from test;

SQL> commit;

Commit complete.

记录下当前的时间

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME

-------------------

2014-07-08 15:41:43

SQL> drop table test;

Table dropped.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME

-------------------

2014-07-08 15:43:13

删除scott用户

SQL> conn /as sysdba

Connected.

SQL> drop user scott cascade;

User dropped.

[oracle@node0 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 8 15:44:23 2014

connected to target database: HNJY (DBID=1408835946)

查看此时数据库的状态

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 HNJY 1408835946 PARENT 1 13-AUG-09

2 2 HNJY 1408835946 CURRENT 754488 08-JUL-14

关闭数据库

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

启动到mount状态

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 511708752 bytes

Database Buffers 331350016 bytes

Redo Buffers 5132288 bytes

首先恢复scott用户

RMAN> run

2> {

3> allocate channel d1 type disk;

4> allocate channel d2 type disk;

5> set until time "to_date('2014-07-08 15:43:13','yyyy-mm-dd hh24:mi:ss')";

6> restore database;

7> recover database;

8> }

allocated channel: d1

channel d1: SID=18 device type=DISK

allocated channel: d2

channel d2: SID=21 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14

channel d1: starting datafile backup set restore

channel d1: specifying datafile(s) to restore from backup set

channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/hnjy/system01.dbf

channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/hnjy/users01.dbf

channel d1: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp

channel d2: starting datafile backup set restore

channel d2: specifying datafile(s) to restore from backup set

channel d2: restoring datafile 00002 to /u01/app/oracle/oradata/hnjy/sysaux01.dbf

channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/hnjy/undotbs01.dbf

channel d2: restoring datafile 00005 to /u01/app/oracle/oradata/hnjy/example01.dbf

channel d2: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp

channel d2: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238

channel d2: restored backup piece 1

channel d2: restore complete, elapsed time: 00:03:16

channel d1: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238

channel d1: restored backup piece 1

channel d1: restore complete, elapsed time: 00:03:29

Finished restore at 08-JUL-14

Starting recover at 08-JUL-14

starting media recovery

media recovery complete, elapsed time: 00:00:13

Finished recover at 08-JUL-14

released channel: d1

released channel: d2

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

[oracle@node0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 15:53:32 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BIN$/am9RJGdPMngQKjAb3IRGg==$0 TABLE

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

可以看到scott用户已经恢复，但是test表还没有恢复

下面开始恢复test表

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@node0 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 8 15:54:04 2014

connected to target database: HNJY (DBID=1408835946)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 511708752 bytes

Database Buffers 331350016 bytes

Redo Buffers 5132288 bytes

RMAN> run

2> {

3> allocate channel d1 type disk;

4> allocate channel d2 type disk;

5> set until time "to_date('2014-07-08 15:41:43','yyyy-mm-dd hh24:mi:ss')"; 指定删除表之前的时间

6> restore database;

7> recover database;

8> }

allocated channel: d1

channel d1: SID=18 device type=DISK

allocated channel: d2

channel d2: SID=19 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14

released channel: d1

released channel: d2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/08/2014 15:56:56

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

提示报错，查看此时数据库的状态DB Key由2变为3，解决办法就是重置数据库到状态2，然后直接恢复到删除EMP表之前的时间点即可。 RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 HNJY 1408835946 PARENT 1 13-AUG-09

2 2 HNJY 1408835946 PARENT 754488 08-JUL-14

3 3 HNJY 1408835946 CURRENT 823876 08-JUL-14

RMAN> reset database to incarnation 2; 重置数据库的状态

database reset to incarnation 2

RMAN> run

2> {

3> allocate channel d1 type disk;

4> allocate channel d2 type disk;

5> set until time "to_date('2014-07-08 15:41:43','yyyy-mm-dd hh24:mi:ss')";

6> restore database;

7> recover database;

8> }

allocated channel: d1

channel d1: SID=18 device type=DISK

allocated channel: d2

channel d2: SID=19 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14

channel d1: starting datafile backup set restore

channel d1: specifying datafile(s) to restore from backup set

channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/hnjy/system01.dbf

channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/hnjy/users01.dbf

channel d1: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp

channel d2: starting datafile backup set restore

channel d2: specifying datafile(s) to restore from backup set

channel d2: restoring datafile 00002 to /u01/app/oracle/oradata/hnjy/sysaux01.dbf

channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/hnjy/undotbs01.dbf

channel d2: restoring datafile 00005 to /u01/app/oracle/oradata/hnjy/example01.dbf

channel d2: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp

channel d2: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238

channel d2: restored backup piece 1

channel d2: restore complete, elapsed time: 00:02:18

channel d1: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238

channel d1: restored backup piece 1

channel d1: restore complete, elapsed time: 00:02:39

Finished restore at 08-JUL-14

Starting recover at 08-JUL-14

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/archive/1_5_852390318.dbf

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/archive/1_6_852390318.dbf

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/archive/1_7_852390318.dbf

archived log file name=/u01/app/oracle/archive/1_5_852390318.dbf thread=1 sequence=5

archived log file name=/u01/app/oracle/archive/1_6_852390318.dbf thread=1 sequence=6

archived log file name=/u01/app/oracle/archive/1_7_852390318.dbf thread=1 sequence=7

media recovery complete, elapsed time: 00:00:11