一个数据库完全一致恢复的测试实例
来源:互联网 发布:淘宝网消保规则 编辑:程序博客网 时间:2024/05/16 00:43
一个oracle数据库的恢复测试
1,关闭数据库完全备份
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cp *.dbf backup1
$cp control01.ctl backup1
2,创建表并插入数据
SQL> startup
ORACLE instance started.
Total System Global Area 1511484856 bytes
Fixed Size 736696 bytes
Variable Size 553648128 bytes
Database Buffers 956301312 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL> create table mytable (f1 varchar2(2));
Table created.
SQL> alter system switch logfile;
System altered.
查看当前sequence和归档的sequence
SQL>select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
SEQUENCE# NAME
------------------------------------------------------------------------------------------
1 /oradata/backup/archivelogbackup/1_1.dbf
SQL> insert into mytable
2 select '01' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable
2 select '02' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
select group#,sequence#,status,first_change# from v$log;
1 1 2 INACTIVE 6612478016
2 2 3 ACTIVE 6612478685
3 3 4 CURRENT 6612480148
select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
1 5 1 /oradata/backup/archivelogbackup/1_1.dbf
2 6 2 /oradata/backup/archivelogbackup/1_2.dbf
3 7 3 /oradata/backup/archivelogbackup/1_3.dbf
SQL> insert into mytable
2 select '03' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable
2 select '04' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL>
SQL>
SQL> insert into mytable
2 select '05' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into mytable
2 select '06' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER
from v$log l,v$logfile lf
where l.GROUP# = lf.GROUP#;
1 1 8 CURRENT 6612481053 /oradata/REDO1_01.LOG
2 1 8 CURRENT 6612481053 /oradata/REDO1_02.LOG
3 2 6 INACTIVE 6612480890 /oradata/REDO2_01.LOG
4 2 6 INACTIVE 6612480890 /oradata/REDO2_02.LOG
5 3 7 ACTIVE 6612480971 /oradata/REDO3_01.LOG
6 3 7 ACTIVE 6612480971 /oradata/REDO3_02.LOG
select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
1 5 1 /oradata/backup/archivelogbackup/1_1.dbf
2 6 2 /oradata/backup/archivelogbackup/1_2.dbf
3 7 3 /oradata/backup/archivelogbackup/1_3.dbf
4 8 4 /oradata/backup/archivelogbackup/1_4.dbf
5 9 5 /oradata/backup/archivelogbackup/1_5.dbf
6 10 6 /oradata/backup/archivelogbackup/1_6.dbf
7 11 7 /oradata/backup/archivelogbackup/1_7.dbf
3,关闭数据库,并用备份的数据文件恢复
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ rm *.dbf
$ rm *.ctl
$ cd backup1
$ cp *.dbf /oradata
$ cp *.ctl /oradata
$ cd ..
$ pwd
/oradata
$ ls
CTL01.dbf REDO1_0101.LOG REDO3_02.LOG backup1 directory.out system01.dbf
FBI.dbf REDO1_0102.LOG RPTD01.dbf backup_datafile drsys01.dbf temp01.dbf
HAPPYTREE.dbf REDO1_02.LOG RPTI01.dbf bea example01.dbf tmp
HTEC.dbf REDO2_01.LOG TEMP1_01.dbf bea80 file tools01.dbf
OD01.dbf REDO2_02.LOG ant control01.ctl indx01.dbf undotbs03.dbf
REDO1_01.LOG REDO3_01.LOG backup cwmlite01.dbf lost+found users01.dbf
$ cp control01.ctl control02.ctl
$ cp control01.ctl control03.ctl
SQL> startup mount
ORACLE instance started.
Total System Global Area 1511484856 bytes
Fixed Size 736696 bytes
Variable Size 553648128 bytes
Database Buffers 956301312 bytes
Redo Buffers 798720 bytes
Database mounted.
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00338: log 1 of thread 1 is more recent than controlfile
ORA-00312: online log 1 thread 1: '/oradata/REDO1_01.LOG'
ORA-00312: online log 1 thread 1: '/oradata/REDO1_02.LOG'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 6612477360 generated at 02/26/2009 15:51:32 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_1.dbf
ORA-00280: change 6612477360 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 6612478016 generated at 02/26/2009 15:57:20 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_2.dbf
ORA-00280: change 6612478016 for thread 1 is in sequence #2
ORA-00278: log file '/oradata/backup/archivelogbackup/1_1.dbf' no longer needed
for this recovery
ORA-00279: change 6612478685 generated at 02/26/2009 16:01:38 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_3.dbf
ORA-00280: change 6612478685 for thread 1 is in sequence #3
ORA-00278: log file '/oradata/backup/archivelogbackup/1_2.dbf' no longer needed
for this recovery
ORA-00279: change 6612480148 generated at 02/26/2009 16:10:36 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_4.dbf
ORA-00280: change 6612480148 for thread 1 is in sequence #4
ORA-00278: log file '/oradata/backup/archivelogbackup/1_3.dbf' no longer needed
for this recovery
ORA-00279: change 6612480791 generated at 02/26/2009 16:14:28 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_5.dbf
ORA-00280: change 6612480791 for thread 1 is in sequence #5
ORA-00278: log file '/oradata/backup/archivelogbackup/1_4.dbf' no longer needed
for this recovery
ORA-00279: change 6612480890 generated at 02/26/2009 16:14:55 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_6.dbf
ORA-00280: change 6612480890 for thread 1 is in sequence #6
ORA-00278: log file '/oradata/backup/archivelogbackup/1_5.dbf' no longer needed
for this recovery
ORA-00279: change 6612480971 generated at 02/26/2009 16:15:20 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_7.dbf
ORA-00280: change 6612480971 for thread 1 is in sequence #7
ORA-00278: log file '/oradata/backup/archivelogbackup/1_6.dbf' no longer needed
for this recovery
ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf
ORA-00280: change 6612481053 for thread 1 is in sequence #8
ORA-00278: log file '/oradata/backup/archivelogbackup/1_7.dbf' no longer needed
for this recovery
ORA-00328: archived log ends at change 6612393963, need later change 6612481053
ORA-00334: archived log: '/oradata/backup/archivelogbackup/1_8.dbf'
提示说当前恢复到scn: 6612393963,需要更多的恢复直到scn 6612481053
SQL> select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER
from v$log l,v$logfile lf
where l.GROUP# = lf.GROUP#; 2 3
GROUP# SEQUENCE# STATUS FIRST_CHANGE#
---------- ---------- -------------------------------- -------------
MEMBER
--------------------------------------------------------------------------------
1 0 UNUSED 0
/oradata/REDO1_01.LOG
2 0 UNUSED 0
/oradata/REDO2_01.LOG
3 1 CURRENT 6612476308
/oradata/REDO3_01.LOG
3 rows selected.
我们看到当前文档为group 3,使用当前文件恢复.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf
ORA-00280: change 6612481053 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/REDO3_01.LOG
ORA-00310: archived log contains sequence 7; sequence 8 required
ORA-00334: archived log: '/oradata/REDO3_01.LOG'
提示说还差一步了,使用归档日志1恢复.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread
1
ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf
ORA-00280: change 6612481053 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/REDO1_01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from mytable;
F1
----
01
02
03
04
05
06
6 rows selected. SQL> alter database open resetlogs;
6 rows selected.
/*小结*/
在出现介质错误时,如果存在一致的完全备份和备份之后的所有归档,则可以考虑使用这种方法恢复.
恢复分为三个步骤.
1,文件覆盖;
2,recover database using backup controlfile until cancel选择auto,自动使用已经备份的东东恢复.
3,查询v$log,从status为current的日志开始(其他应该为unused,因为其他的应该已经归档,并在上一步中成功恢复).
4,使用current的下一个要归档的日志文件完全恢复过程.
- 一个数据库完全一致恢复的测试实例
- 测试数据库的恢复
- mysqlbinlog恢复的一个实例
- MediaWiki的数据库导出和恢复实例
- xtrabackup 备份mysql数据库三: innobackupex 测试一个全量和两个增量的备份恢复测试
- 比较两个表数据完全一致的sql
- DB2 数据库恢复测试
- 一个真实的数据恢复实例 分享WD800JD(2个头)一个真实的数据恢复实例
- 恢复只有一个mdf文件的数据库
- 恢复一个300G的oracle数据库
- 一个数据库恢复实验
- matlab判断一个矩阵中的元素是否完全一致
- DB2数据库跨实例恢复
- 测试一个数据库Log shipping到多SQL Server实例
- catalog损坏情况下的数据库恢复实例
- 如何用Dummy实例执行数据库的还原和恢复
- 测试数据库是否连接正确的实例
- redis数据库的使用方法以及测试实例
- Apache 的简单Rewrite
- SQLServer如何改变数据库对象所有者?
- PHP基础知识总结
- javascript实现简易计算器
- QQ等级:QQ在线等级说明
- 一个数据库完全一致恢复的测试实例
- Window Mobile 手机检测环境噪声
- 久违这咖啡的味道
- PureMVC的十个小提示
- 混沌(chaos)
- 并查集
- 超强表格grid控件
- Difference between "Primary DNS Suffix" and "Connection specific DNS Suffix"?
- 10 tips for working with PureMVC