ITPub博客

首页 > 数据库 > Oracle > Oracle 11g 高效灵活的闪回数据库功能

Oracle 11g 高效灵活的闪回数据库功能

原创 Oracle 作者:LuiseDalian 时间:2014-04-27 07:33:22 0 删除 编辑

-- 运行于归档模式且打开闪回日志的数据库,可以进行任意方向的闪回(向回或向前)

-- 查看数据库运行在归档模式

sys@TESTDB11>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive2

Oldest online log sequence     97

Next log sequence to archive   99

Current log sequence           99

 

-- 开启闪回日志

sys@TESTDB11>alter database flashback on;

 

Database altered.

 

-- 创建表

scott@TESTDB11>create table emp1 as select * from emp;

 

Table created.

 

-- 创建还原点并删除数据

scott@TESTDB11>create restore point rp_bd_analyst;

 

Restore point created.

 

-- 删除analyst

scott@TESTDB11>delete from emp1 where job = 'ANALYST';

 

2 rows deleted.

 

-- 删除clerk

scott@TESTDB11>create restore point rp_bd_clerk;

 

Restore point created.

 

scott@TESTDB11>delete from emp1 where job = 'CLERK';

 

4 rows deleted.

 

-- 删除manager

scott@TESTDB11>create restore point rp_bd_manager;

 

Restore point created.

 

scott@TESTDB11>delete from emp1 where job = 'MANAGER';

 

3 rows deleted.

 

-- 删除president

scott@TESTDB11>create restore point rp_bd_president;

 

Restore point created.

 

scott@TESTDB11>delete from emp1 where job = 'PRESIDENT';

 

1 row deleted.

 

sys@TESTDB11>shutdown immediate;

idle>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

 

-- 闪回到第2个还原点

idle>flashback database to restore point rp_bd_clerk;

 

Flashback complete.

 

idle>alter database open read only;

 

Database altered.

 

idle>conn scott/scott

Connected.

scott@TESTDB11>select * from emp1;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

12 rows selected.

 

scott@TESTDB11>select * from emp1 order by job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

 

12 rows selected.

 

scott@TESTDB11>conn / as sysdba

Connected.

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第1个还原点

sys@TESTDB11>flashback database to restore point rp_bd_analyst;

 

Flashback complete.

 

sys@TESTDB11>alter database open read only;

 

Database altered.

 

sys@TESTDB11>select * from scott.emp1 order by job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

 

14 rows selected.

 

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

 

-- 闪回到第4个还原点

sys@TESTDB11>flashback database to restore point rp_bd_president;

 

Flashback complete.

 

sys@TESTDB11>alter database open read only;

 

Database altered.

 

sys@TESTDB11>select * from scott.emp1 order by job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

 

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TESTDB11>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

-- 闪回到第3个还原点

sys@TESTDB11>flashback database to restore point rp_bd_manager;

 

Flashback complete.

 

sys@TESTDB11>alter database open read only;

 

Database altered.

 

sys@TESTDB11>select * from scott.emp1 order by job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

 

8 rows selected.

 

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--恢复到第4个还原点

 

sys@TESTDB11>startup mount;

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             645924344 bytes

Database Buffers          205520896 bytes

Redo Buffers                2306048 bytes

Database mounted.

 

-- rman中执行recover

RMAN> recover database to restore point rp_bd_president;

 

Starting recover at 25-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

 

starting media recovery

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

 

Finished recover at 25-FEB-14

 

 

sys@TESTDB11>alter database open read only;

 

Database altered.

 

sys@TESTDB11>select * from scott.emp1 order by job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

     

-- 结论: 可以向回,也可以向前来回进行闪回操作. 向前闪回等价于recover操作

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

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5569028