RMAN命令下闪回数据库（十一）

在已启动闪回数据库功能的基础上进行的测试：http://space.itpub.net/685769/viewspace-749364

一、方法：

1)基于时间：

RMAN> FLASHBACK DATABASE TO TIME = “TO_DATE(’2012-08-03 14:51:13′,’YYYY-MM-DD HH24:MI:SS’)”;

2)基于SCN：

RMAN> FLASHBACK DATABASE TO SCN=23565;

3)基于：归档序号

RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;

在RMAN下基于时间、基于SCN与在sqlplus命令行基本相同，只不过语法稍有不同而已，实验步骤可参考如下：

http://space.itpub.net/685769/viewspace-749413

http://space.itpub.net/685769/viewspace-749412

二、下面测试一下基于归档序号的闪回数据库测试

1）在scott用户下创建一张测试表，表中有一条记录

SYS@ORA11GR2>create  table scott.fbdb_rman_seq tablespace users as select ‘xxf’ as name from dual;

Table created.

SYS@ORA11GR2>

2）查看归档信息，目前日志序号为1

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SYS@ORA11GR2>

SYS@ORA11GR2>select group#,sequence#,status,archived from v$log;

 GROUP#  SEQUENCE# STATUS    ARCHIVED

——- ———- ——— ——–

      1          1 CURRENT   NO

      2          0 UNUSED    YES

      3          0 UNUSED    YES

SYS@ORA11GR2>

3）手工切换一次日志

SYS@ORA11GR2>alter system switch logfile;

System altered.

SYS@ORA11GR2>

4）再次查看归档信息，此时日志序号为2，序号为1的日志已经归档

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SYS@ORA11GR2>

SYS@ORA11GR2>select group#,sequence#,status,archived from v$log;

 GROUP#  SEQUENCE# STATUS    ARCHIVED

——- ———- ——— ——–

      1          1 INACTIVE  YES

      2          2 CURRENT   NO

      3          0 UNUSED    YES

SYS@ORA11GR2>

5）向表中再次插入一条记录，此时的redo信息记录在日志序号为2的日志中

SYS@ORA11GR2>insert into scott.fbdb_rman_seq values(‘xzq’);

1 row created.

SYS@ORA11GR2>commit;

Commit complete.

SYS@ORA11GR2>

6）再次手工切换一下日志

SYS@ORA11GR2>alter system switch logfile;

System altered.

SYS@ORA11GR2>

7）再次查看归档信息，此时当前日志序号为3，日志序号为2的日志已经归档

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SYS@ORA11GR2>

SYS@ORA11GR2>select group#,sequence#,status,archived from v$log;

 GROUP#  SEQUENCE# STATUS    ARCHIVED

——- ———- ——— ——–

      1          1 INACTIVE  YES

      2          2 INACTIVE  YES

      3          3 CURRENT   NO

SYS@ORA11GR2>

8）删除测试表scott.fbdb_rman_seq purge;

SYS@ORA11GR2>drop table scott.fbdb_rman_seq purge;

Table dropped.

SYS@ORA11GR2>

9）启动到mount状态，准备闪回数据库

SYS@ORA11GR2>startup mount force;

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             641732176 bytes

Database Buffers          201326592 bytes

Redo Buffers                5132288 bytes

Database mounted.

SYS@ORA11GR2>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@ocmu ~]$

10)登陆RMAN命令行，执行基于日志序号闪回数据库（第一次基于序号2号归档闪回，线程号可写可不写，测试环境为单实例，所以线程号为1）

[oracle@ocmu ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Sat Nov 17 22:48:58 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=113563727, not open)

RMAN> flashback database to sequence=2 thread=1;

Starting flashback at 17-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device  type=DISK

starting media recovery

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

Finished flashback at 17-NOV-12

RMAN> exit

Recovery Manager complete.

[oracle@ocmu ~]$

11）闪回数据库完成，登陆sqlplus以read only打开数据库验证结果，结果为插入第二条记录以后的情况，如果我们想闪回到插入第二条记录之前的情况，那么再来一次

[oracle@ocmu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 17 22:50:00 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SYS@ORA11GR2>alter database open read only;

Database altered.

SYS@ORA11GR2>select * from scott.fbdb_rman_seq;

NAM

—

xxf

xzq

SYS@ORA11GR2>

12）启动到mount模式下

SYS@ORA11GR2>startup mount force;

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             641732176 bytes

Database Buffers          201326592 bytes

Redo Buffers                5132288 bytes

Database mounted.

SYS@ORA11GR2>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@ocmu ~]$

13）再次登陆RMAN，基于序号1日志进行闪回

[oracle@ocmu ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Sat Nov 17 22:51:48 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=113563727, not open)

RMAN> flashback database to sequence=1;

Starting flashback at 17-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

starting media recovery

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

Finished flashback at 17-NOV-12

RMAN> exit

Recovery Manager complete.

[oracle@ocmu ~]$

13）闪回完成，登陆sqlplus，以read only打开数据库验证结果

[oracle@ocmu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 17 22:52:23 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SYS@ORA11GR2>alter database open read only;

Database altered.

SYS@ORA11GR2>

14）结果是理想的，那么我们再次回到mount模式下，然后以resetlogs打开数据库，继续归档序号闪回数据库完毕

SYS@ORA11GR2>select * from scott.fbdb_rman_seq;

NAM

—

xxf

SYS@ORA11GR2>startup mount force;

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             641732176 bytes

Database Buffers          201326592 bytes

Redo Buffers                5132288 bytes

Database mounted.

SYS@ORA11GR2>alter database open resetlogs;

Database altered.

SYS@ORA11GR2>select * from scott.fbdb_rman_seq;

NAM

—

xxf

SYS@ORA11GR2>

三、小结

1、基于归档序号闪回，截止到指定归档日志结尾

2、redo日志的几种状态

   1）current:表示当前正在使用中

   2）active:表示当前活动状态（此种状态并非未归档）

   3）inactive:表示未处于活动状态

   4）unused:表示未被使用

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

转载于:http://blog.itpub.net/23009281/viewspace-758027/