在归档方式下损坏或丢失一个数据文件,如果存在相应的备份与该备份以来的归档日志,恢复还是比较简单的,可以作到尽量少的Down机时间,并能作到数据库的完全恢复。
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 数据库的名字:orcl
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
4 查看数据库是否在归档模式下
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
5 在users表空间创建表
SQL> create table testdata(a int) tablespace users;
Table created.
6 插入数据
SQL> insert into testdata values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testdata;
A
----------
1
7 在/home/mzl/BackupDatabase目录下有个hotbak.sql文件,内容如下:
[oracle@mzl BackupDatabase]$ cat ./hotbak.sql
#rem script.:hotbak.sql
#rem creater:mengzhaoliang
#rem data:2008/2/4
#rem desc:backup all database datafile in archive
#enter database
$ORACLE_HOME/bin/sqlplus "/as sysdba" <alter system archive log current;
#start
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/system01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/users01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/example01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
#backup control file
#binary
alter database backup controlfile to '/home/mzl/BackupDatabase/controlbinarybackup';
#ascii
alter database backup controlfile to trace;
alter system archive log current;
<
8 执行hotbak.sql文件
[oracle@mzl BackupDatabase]$ ./hotbak.sql
9 继续插入数据
SQL> insert into testdata values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testdata;
A
----------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
10 关闭数据库,模拟丢失数据
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
[oracle@mzl orcl]$ rm users01.dbf
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf
模拟媒体毁坏
11 启动数据库错误,脱机该数据文件
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
还可以查看报警文件(见上一个恢复案例)或动态视图v$recover_file
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 ONLINE ONLINE
FILE NOT FOUND 0
脱机数据文件
SQL> alter database datafile 4 offline drop;
Database altered.
12 打开数据库,拷贝备份回来(restore),恢复(recover)该数据文件,并联机
SQL> alter database open;
Database altered.
拷贝备份从备份处
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ cp /home/mzl/BackupDatabase/users01.dbf .
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
恢复该数据文件
SQL> recover datafile 4;
Media recovery complete.
恢复成功,联机该数据文件
SQL> alter database datafile 4 online;
13 检查数据库的数据(完全恢复)
SQL> select * from testdata;
A
----------
1
2
说明:
1、采用热备份,需要运行在归档模式下,可以实现数据库的完全恢复,也就是说,从备份后到数据库崩溃时的数据都不会丢失。
2、可以采用全备份数据库的方式备份,对于特殊情况,也可以只备份特定的数据文件,如只备份用户表空间(一般情况下对于某些写特别频繁的数据文件,可以单独加大备份频率)
3、如果在恢复过程中,发现损坏的是多个数据文件,即可以采用一个一个数据文件的恢复方法(第5步中需要对数据文件一一脱机,第6步中需要对数据文件分别恢复),也可以采用整个数据库的恢复方法。
4、如果是系统表空间的损坏,不能采用此方法
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D1%26amp%3Bfilter%3Ddigest&page=1
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 数据库的名字:orcl
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
4 查看数据库是否在归档模式下
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
5 在users表空间创建表
SQL> create table testdata(a int) tablespace users;
Table created.
6 插入数据
SQL> insert into testdata values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testdata;
A
----------
1
7 在/home/mzl/BackupDatabase目录下有个hotbak.sql文件,内容如下:
[oracle@mzl BackupDatabase]$ cat ./hotbak.sql
#rem script.:hotbak.sql
#rem creater:mengzhaoliang
#rem data:2008/2/4
#rem desc:backup all database datafile in archive
#enter database
$ORACLE_HOME/bin/sqlplus "/as sysdba" <alter system archive log current;
#start
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/system01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/users01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/orcl/example01.dbf /home/mzl/BackupDatabase/;
alter tablespace system end backup;
#backup control file
#binary
alter database backup controlfile to '/home/mzl/BackupDatabase/controlbinarybackup';
#ascii
alter database backup controlfile to trace;
alter system archive log current;
<
8 执行hotbak.sql文件
[oracle@mzl BackupDatabase]$ ./hotbak.sql
9 继续插入数据
SQL> insert into testdata values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testdata;
A
----------
1
2
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
10 关闭数据库,模拟丢失数据
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
[oracle@mzl orcl]$ rm users01.dbf
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf
模拟媒体毁坏
11 启动数据库错误,脱机该数据文件
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
还可以查看报警文件(见上一个恢复案例)或动态视图v$recover_file
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 ONLINE ONLINE
FILE NOT FOUND 0
脱机数据文件
SQL> alter database datafile 4 offline drop;
Database altered.
12 打开数据库,拷贝备份回来(restore),恢复(recover)该数据文件,并联机
SQL> alter database open;
Database altered.
拷贝备份从备份处
[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ cp /home/mzl/BackupDatabase/users01.dbf .
[oracle@mzl orcl]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
恢复该数据文件
SQL> recover datafile 4;
Media recovery complete.
恢复成功,联机该数据文件
SQL> alter database datafile 4 online;
13 检查数据库的数据(完全恢复)
SQL> select * from testdata;
A
----------
1
2
说明:
1、采用热备份,需要运行在归档模式下,可以实现数据库的完全恢复,也就是说,从备份后到数据库崩溃时的数据都不会丢失。
2、可以采用全备份数据库的方式备份,对于特殊情况,也可以只备份特定的数据文件,如只备份用户表空间(一般情况下对于某些写特别频繁的数据文件,可以单独加大备份频率)
3、如果在恢复过程中,发现损坏的是多个数据文件,即可以采用一个一个数据文件的恢复方法(第5步中需要对数据文件一一脱机,第6步中需要对数据文件分别恢复),也可以采用整个数据库的恢复方法。
4、如果是系统表空间的损坏,不能采用此方法
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D1%26amp%3Bfilter%3Ddigest&page=1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-170147/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-170147/