本文介绍在Oracle数据库归档模式下，如何进行热备份并恢复丢失或损坏的数据文件。通过具体步骤演示了如何脱机、恢复及联机数据文件，特别关注users表空间内的数据文件恢复过程。
备份＆恢复之五：归档模式下丢失或损坏一个数据文件
来自piner:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在自己机器测试成功
测试环境：
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 设置成归档模式.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
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.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
4 创建表插入数据
SQL> drop table test;
Table dropped.
SQL> create table test(a int) tablespace users;
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
5 查看表test在哪个表空间
SQL> select table_name,tablespace_name from dba_tables
2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
在users表空间内
6 运行热备份脚本
[oracle@mzl BackupDatabase]$ pwd
/home/mzl/BackupDatabase
[oracle@mzl BackupDatabase]$ vi 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;
<
[oracle@mzl BackupDatabase]$ ./hotbak.sql
7 继续插入数据.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
8 关闭数据库，把users01.dbf数据文件移出
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 create.sql redo01.log risenet.dbf system01.dbf
control02.ctl example01.dbf redo02.log sqlnet.log undotbs01.dbf
control03.ctl perfstat.dbf redo03.log sysaux01.dbf users01.dbf
[oracle@mzl orcl]$ mkdir Old
[oracle@mzl orcl]$ mv users01.dbf Old
9 启动数据库
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'
其中alert_ORCL.log信息为：
ALTER DATABASE OPEN
Wed May 7 19:42:59 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3972.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
其中orcl_dbw0_3450.trc的信息为：
*** SERVICE NAME:() 2008-05-07 19:42:59.118
*** SESSION ID:(167.1) 2008-05-07 19:42:59.118
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
查看哪些文件需要恢复：
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0
10 脱机数据文件
SQL> alter database datafile 4 offline drop;
Database altered.
11 打开数据库，拷贝备份回来(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 .
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> select * from test;
A
----------
1
2
说明：
1、采用热备份，需要运行在归档模式下，可以实现数据库的完全恢复，也就是说，从备份后到数据库崩溃时的数据都不会丢失。
2、可以采用全备份数据库的方式备份，对于特殊情况，也可以只备份特定的数据文件，如只备份用户表空间（一般情况下对于某些写特别频繁的数据文件，可以单独加大备份频率）
3、如果在恢复过程中，发现损坏的是多个数据文件，即可以采用一个一个数据文件的恢复方法（第5步中需要对数据文件一一脱机，第6步中需要对数据文件分别恢复），也可以采用整个数据库的恢复方法。
4、如果是系统表空间的损坏，不能采用此方法
来自piner:
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在自己机器测试成功
测试环境：
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 设置成归档模式.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
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.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
4 创建表插入数据
SQL> drop table test;
Table dropped.
SQL> create table test(a int) tablespace users;
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
5 查看表test在哪个表空间
SQL> select table_name,tablespace_name from dba_tables
2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
在users表空间内
6 运行热备份脚本
[oracle@mzl BackupDatabase]$ pwd
/home/mzl/BackupDatabase
[oracle@mzl BackupDatabase]$ vi 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;
<
[oracle@mzl BackupDatabase]$ ./hotbak.sql
7 继续插入数据.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
8 关闭数据库，把users01.dbf数据文件移出
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 create.sql redo01.log risenet.dbf system01.dbf
control02.ctl example01.dbf redo02.log sqlnet.log undotbs01.dbf
control03.ctl perfstat.dbf redo03.log sysaux01.dbf users01.dbf
[oracle@mzl orcl]$ mkdir Old
[oracle@mzl orcl]$ mv users01.dbf Old
9 启动数据库
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'
其中alert_ORCL.log信息为：
ALTER DATABASE OPEN
Wed May 7 19:42:59 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3972.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
其中orcl_dbw0_3450.trc的信息为：
*** SERVICE NAME:() 2008-05-07 19:42:59.118
*** SESSION ID:(167.1) 2008-05-07 19:42:59.118
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
查看哪些文件需要恢复：
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0
10 脱机数据文件
SQL> alter database datafile 4 offline drop;
Database altered.
11 打开数据库，拷贝备份回来(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 .
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> select * from test;
A
----------
1
2
说明：
1、采用热备份，需要运行在归档模式下，可以实现数据库的完全恢复，也就是说，从备份后到数据库崩溃时的数据都不会丢失。
2、可以采用全备份数据库的方式备份，对于特殊情况，也可以只备份特定的数据文件，如只备份用户表空间（一般情况下对于某些写特别频繁的数据文件，可以单独加大备份频率）
3、如果在恢复过程中，发现损坏的是多个数据文件，即可以采用一个一个数据文件的恢复方法（第5步中需要对数据文件一一脱机，第6步中需要对数据文件分别恢复），也可以采用整个数据库的恢复方法。
4、如果是系统表空间的损坏，不能采用此方法
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-263079/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-263079/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
