--1.改变检查点

sys@TESTDB11>alter system checkpoint;

System altered.

-- 查看日志组状态，当前组为3，序号为93

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

        91          1 INACTIVE

        92          2 INACTIVE

        93          3 CURRENT

--

scott@TESTDB11>select * from emp;

     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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      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

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--手工产生脏志

scott@TESTDB11>update emp set sal = sal * 2 where ename = 'MILLER';

1 row updated.

--这个改变进行提交

scott@TESTDB11>commit;

Commit complete.

--切换日志

sys@TESTDB11>alter system switch logfile;

System altered.

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

        94          1 CURRENT

        92          2 INACTIVE

        93          3 ACTIVE

sys@TESTDB11>select sequence#, group#, status, first_change# from v$log;

 SEQUENCE#     GROUP# STATUS           FIRST_CHANGE#

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

        94          1 CURRENT                2583163

        92          2 INACTIVE               2581128

        93          3 ACTIVE                 2581689

sys@TESTDB11>grant select any dictionary to scott;

Grant succeeded.

--确定当前使用的是94号日志

scott@TESTDB11>select current_scn from v$database;

CURRENT_SCN

-----------

    2583205

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--删除数据但不提交

scott@TESTDB11>delete from dept where deptno = 40;

1 row deleted.

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO  

--切换日志，查看有2个active日志

sys@TESTDB11>alter system switch logfile;

System altered.

sys@TESTDB11>select sequence#, group#, status, first_change# from v$log;

 SEQUENCE#     GROUP# STATUS           FIRST_CHANGE#

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

        94          1 ACTIVE                 2583163

        95          2 CURRENT                2583270

        93          3 ACTIVE                 2581689

--检查点对应94号日志       

sys@TESTDB11>col name for a50

sys@TESTDB11>select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#

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

/u01/app/oracle/oradata/TestDB11/system01.dbf                 2583163

/u01/app/oracle/oradata/TestDB11/sysaux01.dbf                 2583163

/u01/app/oracle/oradata/TestDB11/undotbs01.dbf                2583163

/u01/app/oracle/oradata/TestDB11/users01.dbf                  2583163

/u01/app/oracle/oradata/TestDB11/example01.dbf                2583163

/u01/app/oracle/oradata/TestDB11/rotbs01.dbf                  2583163

6 rows selected.       

sys@TESTDB11>select name, checkpoint_change#, last_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE# LAST_CHANGE#

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

/u01/app/oracle/oradata/TestDB11/system01.dbf                 2583163

/u01/app/oracle/oradata/TestDB11/sysaux01.dbf                 2583163

/u01/app/oracle/oradata/TestDB11/undotbs01.dbf                2583163

/u01/app/oracle/oradata/TestDB11/users01.dbf                  2583163

/u01/app/oracle/oradata/TestDB11/example01.dbf                2583163

/u01/app/oracle/oradata/TestDB11/rotbs01.dbf                  2583163

6 rows selected.

--关库

sys@TESTDB11>shutdown abort;

ORACLE instance shut down.

--启动到mount状态

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

--此时数据文件中miller的工资仍为原来的. 40部门仍然存在。

sys@TESTDB11>alter database open;

Database altered.

--查看报告，确定进行实例恢复时只使用了需要的94号与95号日志

Completed: ALTER DATABASE   MOUNT

Thu Aug 08 21:41:38 2013

alter database open

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

 read 59 KB redo, 50 data blocks need recovery

Started redo application at

 Thread 1: logseq 94, block 27

Recovery of Online Redo Log: Thread 1 Group 1 Seq 94 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/TestDB11/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 95 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/TestDB11/redo02.log

Completed redo application of 0.02MB

Completed crash recovery at

 Thread 1: logseq 95, block 84, scn 2603383

 50 data blocks read, 50 data blocks written, 59 redo k-bytes read

Thu Aug 08 21:41:38 2013

LGWR: STARTING ARCH PROCESSES

Thu Aug 08 21:41:38 2013

ARC0 started with pid=19, OS id=3453

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thread 1 advanced to log sequence 96 (thread open)

Thread 1 opened at log sequence 96

  Current log# 3 seq# 96 mem# 0: /u01/app/oracle/oradata/TestDB11/redo03.log

Successful open of redo thread 1

Thu Aug 08 21:41:40 2013

SMON: enabling cache recovery

Thu Aug 08 21:41:40 2013

ARC1 started with pid=21, OS id=3455

Thu Aug 08 21:41:40 2013

ARC2 started with pid=22, OS id=3457

Thu Aug 08 21:41:40 2013

ARC3 started with pid=23, OS id=3459

ARC1: Archival started

ARC2: Archival started

ARC3: Archival started

ARC3: Becoming the 'no FAL' ARCH

ARC3: Becoming the 'no SRL' ARCH

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

Archived Log entry 7 added for thread 1 sequence 95 ID 0x99b61782 dest 1:

[3451] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:26344105 end:26344522 diff:417 (4 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Aug 08 21:41:43 2013

QMNC started with pid=24, OS id=3461

Completed: alter database open

Thu Aug 08 21:41:45 2013

db_recovery_file_dest_size of 4122 MB is 0.34% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Thu Aug 08 21:41:45 2013

Starting background process CJQ0

Thu Aug 08 21:41:45 2013

CJQ0 started with pid=26, OS id=3475

--确认数据

scott@TESTDB11>select * from emp;

     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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      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

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       2600                    10

14 rows selected.

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--对恢复的优化

sys@TESTDB11>show parameter mttr;

NAME                                 TYPE        VALUE

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

fast_start_mttr_target               integer     600

--在恢复时肯定是串行的，只有在读文件内容的时候才可以并行

sys@TESTDB11>show parameter recovery_parallelism;

NAME                                 TYPE        VALUE

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

recovery_parallelism                 integer     0

sys@TESTDB11>show parameter rollback

NAME                                 TYPE        VALUE

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

fast_start_parallel_rollback         string      LOW

rollback_segments                    string

transactions_per_rollback_segment    integer     5