4.1非归档模式下的备份与恢复
备份方案:采用OS冷备份
1.连接数据库并创建测试表
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete
2.备份数据库
SQL> @coldbak.sql 或在DOS下 svrmgrl @coldbak.sql
3.再插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
4.关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5.毁坏一个或多个数据文件,如删除user01.dbf
C:\>del D:\ORACLE\ORADATA\TEST\USERS01.DBF
模拟媒体毁坏
6.重新启动数据库,会发现如下错误
SQL> startup
ORACLE instance started.
Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'
在报警文件中,会有更详细的信息
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
7.拷贝备份复原到原来位置(restore过程)
C:\>xcopy d:\database\*.* d:\oracle\oradata\test/H/R/S
8.打开数据库,检查数据
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
这里可以发现,数据库恢复成功,但在备份之后与崩溃之前的数据丢失了。
说明:
1、非归档模式下的恢复方案可选性很小,一般情况下只能有一种恢复方式,就是数据库的冷备份的完全恢复,仅仅需要拷贝原来的备份就可以(restore),不需要recover。
2、这种情况下的恢复,可以完全恢复到备份的点上,但是可能是丢失数据的,在备份之后与崩溃之前的数据将全部丢失。
3、不管毁坏了多少数据文件或是联机日志或是控制文件,都可以通过这个办法恢复,因为这个恢复过程是Restore所有的冷备份文件,而这个备份点上的所有文件是一致的,与最新的数据库没有关系,就好比把数据库又放到了一个以前的“点”上。
4、对于非归档模式下,最好的办法就是采用OS的冷备份,建议不要用RMAN来作冷备份,效果不好,因为RMAN不备份联机日志,restore不能根本解决问题。
5、如果没有备份联机日志,如RMAN的备份,就需要利用不完全恢复(until cancel)的方法来重新创建联机日志文件
备份方案:采用OS冷备份
1.连接数据库并创建测试表
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete
2.备份数据库
SQL> @coldbak.sql 或在DOS下 svrmgrl @coldbak.sql
3.再插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
4.关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5.毁坏一个或多个数据文件,如删除user01.dbf
C:\>del D:\ORACLE\ORADATA\TEST\USERS01.DBF
模拟媒体毁坏
6.重新启动数据库,会发现如下错误
SQL> startup
ORACLE instance started.
Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'
在报警文件中,会有更详细的信息
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
7.拷贝备份复原到原来位置(restore过程)
C:\>xcopy d:\database\*.* d:\oracle\oradata\test/H/R/S
8.打开数据库,检查数据
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
这里可以发现,数据库恢复成功,但在备份之后与崩溃之前的数据丢失了。
说明:
1、非归档模式下的恢复方案可选性很小,一般情况下只能有一种恢复方式,就是数据库的冷备份的完全恢复,仅仅需要拷贝原来的备份就可以(restore),不需要recover。
2、这种情况下的恢复,可以完全恢复到备份的点上,但是可能是丢失数据的,在备份之后与崩溃之前的数据将全部丢失。
3、不管毁坏了多少数据文件或是联机日志或是控制文件,都可以通过这个办法恢复,因为这个恢复过程是Restore所有的冷备份文件,而这个备份点上的所有文件是一致的,与最新的数据库没有关系,就好比把数据库又放到了一个以前的“点”上。
4、对于非归档模式下,最好的办法就是采用OS的冷备份,建议不要用RMAN来作冷备份,效果不好,因为RMAN不备份联机日志,restore不能根本解决问题。
5、如果没有备份联机日志,如RMAN的备份,就需要利用不完全恢复(until cancel)的方法来重新创建联机日志文件
解决方案 »
- 请教:OracleDBConsole关闭以后就不能启动了
- oracle中如何批量更新某列数据
- orapwd和alter user SYS identified by "123"修改密码有何不同
- 一个包中有多个需要返回结果集的存储过程的输出参数是引用光标类型的问题
- 请教一下~~2个sql语句的区别
- 求一sql语句(想了三天的问题)
- 本人想往DBA方面发展,有人能给指点指点吗?
- [200分]在该存储过程传递的字符参数如何处理,我的处理老是出错!(急急急急急急急急急急急急急)
- 这句话如何理解:数据库日志模式及非存档模式?
- plsql创建自动导出表的任务如果写啊,谢谢
- 怎么看ORA-xxxxxx的具体错误信息?
- Imp如何实现增量导入?只导入数据。
4.2.1 OS备份方案
在归档方式下损坏或丢失一个数据文件,如果存在相应的备份与该备份以来的归档日志,恢复还是比较简单的,可以作到尽量少的Down机时间,并能作到数据库的完全恢复。
1、连接数据库,创建测试表并插入记录
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int) tablespace users;
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete2、备份数据库
SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql3、继续在测试表中插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.4、关闭数据库,模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:\>del D:\ORACLE\ORADATA\TEST\USERS01.DBF
模拟媒体毁坏5、启动数据库错误,脱机该数据文件
SQL> startup
ORACLE instance started.Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'
还可以查看报警文件(见上一个恢复案例)或动态视图v$recover_file
如SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- -----------
3 ONLINE 1013500 2003-05-07脱机数据文件
SQL> alter database datafile 3 offline drop;
Database altered.6、打开数据库,拷贝备份回来(restore),恢复(recover)该数据文件,并联机
SQL> alter database open;
Database altered.
拷贝备份从备份处
copy d:\databak\ users01.dbf d:\oracle\oradata\test;
恢复该数据文件
SQL> recover datafile 3;
ORA-00279: change 1053698 generated at 05/07/2003 17:51:26 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00304.ARC
ORA-00280: change 1053698 for thread 1 is in sequence #304Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1053701 generated at 05/07/2003 17:51:39 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00305.ARC
ORA-00280: change 1053701 for thread 1 is in sequence #305
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00304.ARC' no
longer needed for this recoveryLog applied.
Media recovery complete.
恢复成功,联机该数据文件
SQL> alter database datafile 3 online;
Database altered.7、检查数据库的数据(完全恢复)
SQL> select * from test;
A
---------------------------------------
1
2
说明:
1、采用热备份,需要运行在归档模式下,可以实现数据库的完全恢复,也就是说,从备份后到数据库崩溃时的数据都不会丢失。
2、可以采用全备份数据库的方式备份,对于特殊情况,也可以只备份特定的数据文件,如只备份用户表空间(一般情况下对于某些写特别频繁的数据文件,可以单独加大备份频率)
3、如果在恢复过程中,发现损坏的是多个数据文件,即可以采用一个一个数据文件的恢复方法(第5步中需要对数据文件一一脱机,第6步中需要对数据文件分别恢复),也可以采用整个数据库的恢复方法。
4、如果是系统表空间的损坏,不能采用此方法
4.3.1 OS备份方案
OS备份归档模式下损坏(丢失)多个数据文件,进行整个数据库的恢复
1、连接数据库,创建测试表并插入记录
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected.SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete2、备份数据库,备份除临时数据文件后的所数据文件
SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql3、继续在测试表中插入记录
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.4、关闭数据库,模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut downC:\>del D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\INDX01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\TOOLS01.DBF
C:\>del D:\ORACLE\ORADATA\TEST\RBS01.DBF
模拟媒体毁坏(这里删除多个数据文件)5、启动数据库,检查错误
SQL> STARTUP
ORACLE instance started.
Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'详细信息可以查看报警文件
ORA-1157 signalled during: ALTER DATABASE OPEN...
Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\TEST\RBS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\TEST\TOOLS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。Thu May 08 09:39:36 2003
Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\TEST\INDX01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。通过查询v$recover_file可以看到
SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- -----------
1 ONLINE FILE NOT FOUND 0
2 ONLINE FILE NOT FOUND 0
5 ONLINE FILE NOT FOUND 0
6 ONLINE FILE NOT FOUND 0
有四个数据文件需要恢复6、拷贝备份回到原地点(restore),开始恢复数据库(recover)
restore过程:
C:\>copy D:\DATABAK\SYSTEM01.DBF D:\ORACLE\ORADATA\TESTC:\>copy D:\DATABAK\TEST\INDX01.DBF D:\ORACLE\ORADATA\TESTC:\>copy D:\DATABAK\TEST\TOOLS01.DBF D:\ORACLE\ORADATA\TESTC:\>copy D:\DATABAK\TEST\RBS01.DBF.DBF D:\ORACLE\ORADATA\TEST
Recover过程:
SQL> recover database;
ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC
ORA-00280: change 1073849 for thread 1 is in sequence #311Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC
ORA-00280: change 1073856 for thread 1 is in sequence #312
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC' no
longer needed for this recoveryORA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC
ORA-00280: change 1073858 for thread 1 is in sequence #313
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC' no
longer needed for this recoveryORA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00314.ARC
ORA-00280: change 1073870 for thread 1 is in sequence #314
ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC' no
longer needed for this recoveryLog applied.
Media recovery complete.7、打开数据库,检查数据库的数据(完全恢复)
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
2说明:
1、只要有备份与归档存在,就可以实现数据库的完全恢复(不丢失数据)
2、适合于丢失大量数据文件,或包含系统数据文件在内的数据库的恢复
3、恢复过程在mount下进行,如果恢复成功,再打开数据库,down机时间可能比较长一些。
ORA-01110: ????9?'D:\DATA\IMAGE\IMAG01.DBF'
ORA-01203: ???????? - ?? SCN ??这三个代码组合在一起我根本找不到头绪了
shutdown
conn / as sysdba
startup mount
recover database
alter database open
2.备份数据库
SQL> @coldbak.sql 或在DOS下 svrmgrl @coldbak.sql谢谢!!