oracle介质恢复的概念
http://space.itpub.net/9240380/viewspace-757008
如何控制文件检查点scn和数据文件头及数据文件检查点scn不致,则需要介质恢复
测试思路：
1,开启归档
2,备份21号文件
3,查看备份后的各个scn
4,关库
5,用备份21数据文件替换当前的21数据文件
6,启动库到mount状态
7,查看各个scn
8,查看alert是否发生介质恢复（我们知道介质恢复要人工操作）
9,手工进行介质恢复
10,查看open后的各个scn
/************开始测试****************/
1,开启归档
SQL> startup mount
ORA-32004: obsolete or deprecated parameter
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
/*********配置归档目录,不然即使开启归档无地可归档***********/
SQL> alter system set log_archive_dest='d:\archive';
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 825 52428800 512 1 YES INACTIVE 10592541 2013/3/25 1 10620531 2013/3/26 9
4 1 826 209715200 512 1 YES ACTIVE 10620531 2013/3/26 9 10628796 2013/3/26 1
5 1 827 209715200 512 1 NO CURRENT 10628796 2013/3/26 1 281474976710
6 1 820 209715200 512 1 YES INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 YES INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 YES INACTIVE 10561202 2013/3/25 1 10588922 2013/3/25 1
9 1 824 209715200 512 1 YES INACTIVE 10591778 2013/3/25 1 10592541 2013/3/25 1
10 1 823 20971520 512 1 YES INACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
/********active日志已归档************/
SQL> select * from v$archived_log;
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
1 811078878 D:\ARCHIVE\ARC0000000826_0803485505.0001 1 1 826 1 2012/12/31 14: 803485505 10620531 2013/3/26 9 10628796 2013/3/26 1 27049 512 ARCH ARCH NO YES NO NO A 2013/3/26 11:41 NO NO NO 0 1 1331289025 NO NO NO NO
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10628796
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10628796
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10628796
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10628796
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10628796
6 C:\TBS_1.DBF 10628796
7 C:\TBS_1_OTHER.DBF 10628796
8 C:\TBS_2.DBF 10628796
9 C:\TBS_2_OTHER.DBF 10628796
10 C:\TBS_HANG1.DBF 10628796
11 D:\TBS_AUTO.DBF 10628796
12 C:\TBS_NON_STANDARD.DBF 10628796
13 C:\1.DBF 10628796
14 C:\2.DBF 10628796
15 C:\3.DBF 10628796
16 C:\5.DBF 10628796
17 C:\9.DBF 10628796
18 C:\22.DBF 10628796
19 C:\19.DBF 10628796
20 C:\222.DBF 10628796
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10628796
21 rows selected
2,备份21号文件
/**********手工备份后对数据文件进行dml*************/
SQL> create table tbs_16(a int) tablespace tbs_16k;
Table created
SQL> insert into tbs_16 values(1);
1 row inserted
SQL> commit;
Commit complete
/****强制手工检查点*************/
SQL> alter system checkpoint;
System altered
3,查看备份后的各个scn
SQL> select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629687
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629687
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629687
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629687
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629687
6 C:\TBS_1.DBF 10629687
7 C:\TBS_1_OTHER.DBF 10629687
8 C:\TBS_2.DBF 10629687
9 C:\TBS_2_OTHER.DBF 10629687
10 C:\TBS_HANG1.DBF 10629687
11 D:\TBS_AUTO.DBF 10629687
12 C:\TBS_NON_STANDARD.DBF 10629687
13 C:\1.DBF 10629687
14 C:\2.DBF 10629687
15 C:\3.DBF 10629687
16 C:\5.DBF 10629687
17 C:\9.DBF 10629687
18 C:\22.DBF 10629687
19 C:\19.DBF 10629687
20 C:\222.DBF 10629687
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629687
21 rows selected
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629687
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629687
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629687
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629687
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629687
6 C:\TBS_1.DBF 10629687
7 C:\TBS_1_OTHER.DBF 10629687
8 C:\TBS_2.DBF 10629687
9 C:\TBS_2_OTHER.DBF 10629687
10 C:\TBS_HANG1.DBF 10629687
11 D:\TBS_AUTO.DBF 10629687
12 C:\TBS_NON_STANDARD.DBF 10629687
13 C:\1.DBF 10629687
14 C:\2.DBF 10629687
15 C:\3.DBF 10629687
16 C:\5.DBF 10629687
17 C:\9.DBF 10629687
18 C:\22.DBF 10629687
19 C:\19.DBF 10629687
20 C:\222.DBF 10629687
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629687
21 rows selected
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629687
/*********检查点后的日志文件及归档日志信息**********/
SQL> select * from v$archived_log;
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
1 811078878 D:\ARCHIVE\ARC0000000826_0803485505.0001 1 1 826 1 2012/12/31 14: 803485505 10620531 2013/3/26 9 10628796 2013/3/26 1 27049 512 ARCH ARCH NO YES NO NO A 2013/3/26 11:41 NO NO NO 0 1 1331289025 NO NO NO NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 825 52428800 512 1 YES INACTIVE 10592541 2013/3/25 1 10620531 2013/3/26 9
4 1 826 209715200 512 1 YES INACTIVE 10620531 2013/3/26 9 10628796 2013/3/26 1
5 1 827 209715200 512 1 NO CURRENT 10628796 2013/3/26 1 281474976710
6 1 820 209715200 512 1 YES INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 YES INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 YES INACTIVE 10561202 2013/3/25 1 10588922 2013/3/25 1
9 1 824 209715200 512 1 YES INACTIVE 10591778 2013/3/25 1 10592541 2013/3/25 1
10 1 823 20971520 512 1 YES INACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
4,关库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
5,用备份21数据文件替换当前的21数据文件
6,启动库到mount状态
SQL> startup mount
ORA-32004: obsolete or deprecated paramete
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
7,查看各个scn,发现数据文件头的检查点scn与其它不一样
select file#,name,checkpoint_change# from v$datafile_header
FILE# NAME CHECKPOINT_CHANGE#
----- -------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629758
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629758
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629758
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629758
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629758
6 C:\TBS_1.DBF 10629758
7 C:\TBS_1_OTHER.DBF 10629758
8 C:\TBS_2.DBF 10629758
9 C:\TBS_2_OTHER.DBF 10629758
10 C:\TBS_HANG1.DBF 10629758
11 D:\TBS_AUTO.DBF 10629758
FILE# NAME CHECKPOINT_CHANGE#
----- -------------------------------------------------- ------------------
12 C:\TBS_NON_STANDARD.DBF 10629758
13 C:\1.DBF 10629758
14 C:\2.DBF 10629758
15 C:\3.DBF 10629758
16 C:\5.DBF 10629758
17 C:\9.DBF 10629758
18 C:\22.DBF 10629758
19 C:\19.DBF 10629758
20 C:\222.DBF 10629758
21 C:\TBS16K_1.DBF 10628796 --明显看到这个scn小于其它的
21 rows selected.
/**你发现即使用了备份的数据文件,21数据文件的检查点scn与其它是一致,所以v$datafile与v$datafile_header是不一样的检查点****/
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629758
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629758
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629758
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629758
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629758
6 C:\TBS_1.DBF 10629758
7 C:\TBS_1_OTHER.DBF 10629758
8 C:\TBS_2.DBF 10629758
9 C:\TBS_2_OTHER.DBF 10629758
10 C:\TBS_HANG1.DBF 10629758
11 D:\TBS_AUTO.DBF 10629758
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
12 C:\TBS_NON_STANDARD.DBF 10629758
13 C:\1.DBF 10629758
14 C:\2.DBF 10629758
15 C:\3.DBF 10629758
16 C:\5.DBF 10629758
17 C:\9.DBF 10629758
18 C:\22.DBF 10629758
19 C:\19.DBF 10629758
20 C:\222.DBF 10629758
21 C:\TBS16K_1.DBF 10629758
21 rows selected.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629758
8,查看alert是否发生介质恢复（我们知道介质恢复要人工操作）
/*******提示要介质恢复******/
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'C:\TBS16K_1.DBF'
9,手工进行介质恢复
/****** 手工介质恢复*********/
SQL> recover datafile 21;
Media recovery complete.
SQL>
/******查看alert看介质恢复的内容*****/
ALTER DATABASE RECOVER datafile 21
Media Recovery Start --介质恢复开始
Serial Media Recovery started
WARNING! Recovering data file 21 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command. ---提示很明确因为我是在线用os 备份的21数据文件
Recovery of Online Redo Log: Thread 1 Group 5 Seq 827 Reading mem 0 --介质恢复应用了827号日志,827号日志是介质恢复之前current的日志
Mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO05.LOG
Completed: ALTER DATABASE RECOVER datafile 21 --介质恢复结束
10,查看open后的各个scn,发现介质恢复后各个scn又达到一致，数据库又回归到了正常状态
SQL> alter database open;
Database altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629761
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629761
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629761
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629761
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629761
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629761
6 C:\TBS_1.DBF 10629761
7 C:\TBS_1_OTHER.DBF 10629761
8 C:\TBS_2.DBF 10629761
9 C:\TBS_2_OTHER.DBF 10629761
10 C:\TBS_HANG1.DBF 10629761
11 D:\TBS_AUTO.DBF 10629761
12 C:\TBS_NON_STANDARD.DBF 10629761
13 C:\1.DBF 10629761
14 C:\2.DBF 10629761
15 C:\3.DBF 10629761
16 C:\5.DBF 10629761
17 C:\9.DBF 10629761
18 C:\22.DBF 10629761
19 C:\19.DBF 10629761
20 C:\222.DBF 10629761
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629761
21 rows selected
SQL> select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629761
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629761
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629761
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629761
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629761
6 C:\TBS_1.DBF 10629761
7 C:\TBS_1_OTHER.DBF 10629761
8 C:\TBS_2.DBF 10629761
9 C:\TBS_2_OTHER.DBF 10629761
10 C:\TBS_HANG1.DBF 10629761
11 D:\TBS_AUTO.DBF 10629761
12 C:\TBS_NON_STANDARD.DBF 10629761
13 C:\1.DBF 10629761
14 C:\2.DBF 10629761
15 C:\3.DBF 10629761
16 C:\5.DBF 10629761
17 C:\9.DBF 10629761
18 C:\22.DBF 10629761
19 C:\19.DBF 10629761
20 C:\222.DBF 10629761
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629761
21 rows selected
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-757153/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-757153/
95
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
