例項恢復擴充套件案例-手工產生髒塊

--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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1151258/，如需轉載，請註明出處，否則將追究法律責任。

例項恢復擴充套件案例-手工產生髒塊

相關文章