今天我实验了一下用RMAN来对数据库进行备份以及恢复。
详细过程如下:
环境:database
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
OS:
Redhat Enterprise Linux 6.3 x86_64
首先启动到RMAN命令模式
[oracle@ww ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jan 6 11:14:31 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1360271917)
RMAN>
进入RMAN后我们可以查看RMAN的配置属性
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default
输入show all就能查看,以下属性设置的功能及如何应用下次再来研究。
今天我们先来做一下RMAN的备份与恢复。
首先对我们当前的数据库做一个全备(包括数据文件、控制文件、初始参数文件、归档日志文件),命令很简单:
RMAN> backup database plus archivelog;
Starting backup at 2014-01-06 11:22:53
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=21 stamp=836133773
channel ORA_DISK_1: starting piece 1 at 2014-01-06 11:22:55
channel ORA_DISK_1: finished piece 1 at 2014-01-06 11:22:56
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp tag=TAG20140106T112254 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2014-01-06 11:22:56
..............
Starting Control File and SPFILE Autobackup at 2014-01-06 11:23:53
piece handle=/oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2014-01-06 11:23:55
(中间输出命令太多 这里省略部分)
备份成功~!上面在我执行了备份命令后,输出结果可以发现,备份文件全部都装入了快速恢复区。由于我在备份的时候没有指定备份文件存放位置,RMAN默认是存入快速恢复区,如果你没有开快速恢复区,那备份文件就自动存放在ORACLE_HOME/dbs目录下。
在RMAN下用以下命令可以查看当前所有的备份信息
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
21 266.50K DISK 00:00:01 2014-01-06 11:22:55
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112254
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp
List of Archived Logs in backup set 21
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 1 1469692 2014-01-06 11:01:35 1470560 2014-01-06 11:22:53
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 829.66M DISK 00:00:49 2014-01-06 11:23:45
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112256
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/system01.dbf
2 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/undotbs01.dbf
3 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/sysaux01.dbf
4 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/users01.dbf
5 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/whj.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
23 2.00K DISK 00:00:01 2014-01-06 11:23:52
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112351
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112351_9dn8g89s_.bkp
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 2 1470560 2014-01-06 11:22:53 1470586 2014-01-06 11:23:51
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24 Full 6.80M DISK 00:00:00 2014-01-06 11:23:53
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112353
Piece Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp
Control File Included: Ckp SCN: 1470592 Ckp time: 2014-01-06 11:23:53
SPFILE Included: Modification time: 2014-01-06 10:41:33
现在备份成功后我们来模拟数据文件丢失后怎么恢复
删除users01.dbf数据文件
[oracle@ww orcl]$ rm users01.dbf
[oracle@ww orcl]$ ll
total 1668300
-rw-r----- 1 oracle dba 7061504 Jan 6 11:38 control01.ctl
-rw-r----- 1 oracle dba 52429312 Jan 6 11:22 redo01.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:23 redo02.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:36 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan 6 11:23 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 6 11:23 system01.dbf
-rw-r----- 1 oracle dba 31465472 Dec 7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan 6 11:23 undotbs01.dbf
-rw-r----- 1 oracle dba 33562624 Jan 6 11:23 whj.dbf
删除后操作users表空间下的表就会报错
SQL> insert into HR values(3,'aa');
insert into HR values(3,'aa')
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/app/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
users01.dbf数据文件已经不存在
恢复:
将users01数据文件处于离线状态
SQL> alter database datafile 4 offline;
Database altered.
在RMAN命令行下输入以下恢复命令
RMAN> restore datafile 4;
Starting restore at 2014-01-06 12:15:14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp tag=TAG20140106T112256
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2014-01-06 12:15:16
恢复成功~!现在看下/oracle/app/oradata/orcl目录下有没有users.dbf文件
[oracle@ww orcl]$ ll
total 1673428
-rw-r----- 1 oracle dba 7061504 Jan 6 12:15 control01.ctl
-rw-r----- 1 oracle dba 52429312 Jan 6 11:43 redo01.log
-rw-r----- 1 oracle dba 52429312 Jan 6 12:14 redo02.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:43 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan 6 12:10 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 6 12:01 system01.dbf
-rw-r----- 1 oracle dba 31465472 Dec 7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan 6 12:10 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Jan 6 12:15 users01.dbf
-rw-r----- 1 oracle dba 33562624 Jan 6 11:49 whj.dbf
[oracle@ww orcl]$ pwd
/oracle/app/oradata/orcl
数据文件恢复出来了,最后recover一下将他的检查点与其他数据文件同步就可以了
RMAN> recover datafile 4;
Starting recover at 2014-01-06 12:17:47
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_3_9dn9lmxy_.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_4_9dn9ls3q_.arc
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 2014-01-06 12:17:50
将users.dbf数据文件启动到在线状态
SQL> alter database datafile 4 online;
Database altered.
现在执行一下刚才的insert命令就可以成功了
SQL> insert into HR values(3,'aa');
1 row created.
SQL> select * from HR;
ID NAME
---------- --------------------
1 whj
3 aa
2 qq
接下来模拟控制文件全部丢失
[oracle@ww orcl]$ rm control01.ctl
[oracle@ww ~]$ rm control02.ctl
[oracle@ww ORCL]$ rm control03.ctl
我删除了所有的控制文件
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/app/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
现在数据库都不能正常关闭,报错说找不到控制文件
SQL> shutdown abort
ORACLE instance shut down.
只能强行关闭
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 557842432 bytes
Fixed Size 2085456 bytes
Variable Size 155192752 bytes
Database Buffers 394264576 bytes
Redo Buffers 6299648 bytes
在RMAN下启动数据库到nomount状态
RMAN> restore controlfile from autobackup;
Starting restore at 2014-01-06 13:31:59
using channel ORA_DISK_1
recovery area destination: /oracle/app/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/app/oradata/orcl/control01.ctl
output filename=/home/oracle/control02.ctl
output filename=/oracle/app/flash_recovery_area/ORCL/control03.ctl
Finished restore at 2014-01-06 13:32:04
从自动 备份里恢复控制文件
然后启动到mount状态下recover数据库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database ;
Starting recover at 2014-01-06 13:34:32
Starting implicit crosscheck backup at 2014-01-06 13:34:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2014-01-06 13:34:32
Starting implicit crosscheck copy at 2014-01-06 13:34:32
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2014-01-06 13:34:32
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
File Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
archive log thread 1 sequence 6 is already on disk as file /oracle/app/oradata/orcl/redo03.log
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc thread=1 sequence=5
archive log filename=/oracle/app/oradata/orcl/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-01-06 13:34:33
最后启动数据库到open,因为是不完全恢复需要resetlogs
RMAN> alter database open resetlogs;
database opened
好了 控制文件恢复成功,可以进行数据操作了
SQL> create table whj as select * from HR;
Table created.
SQL> select * from whj;
ID NAME
---------- --------------------
1 whj
3 aa
2 qq
详细过程如下:
环境:database
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
OS:
Redhat Enterprise Linux 6.3 x86_64
首先启动到RMAN命令模式
[oracle@ww ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jan 6 11:14:31 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1360271917)
RMAN>
进入RMAN后我们可以查看RMAN的配置属性
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default
输入show all就能查看,以下属性设置的功能及如何应用下次再来研究。
今天我们先来做一下RMAN的备份与恢复。
首先对我们当前的数据库做一个全备(包括数据文件、控制文件、初始参数文件、归档日志文件),命令很简单:
RMAN> backup database plus archivelog;
Starting backup at 2014-01-06 11:22:53
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=21 stamp=836133773
channel ORA_DISK_1: starting piece 1 at 2014-01-06 11:22:55
channel ORA_DISK_1: finished piece 1 at 2014-01-06 11:22:56
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp tag=TAG20140106T112254 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2014-01-06 11:22:56
..............
Starting Control File and SPFILE Autobackup at 2014-01-06 11:23:53
piece handle=/oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2014-01-06 11:23:55
(中间输出命令太多 这里省略部分)
备份成功~!上面在我执行了备份命令后,输出结果可以发现,备份文件全部都装入了快速恢复区。由于我在备份的时候没有指定备份文件存放位置,RMAN默认是存入快速恢复区,如果你没有开快速恢复区,那备份文件就自动存放在ORACLE_HOME/dbs目录下。
在RMAN下用以下命令可以查看当前所有的备份信息
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
21 266.50K DISK 00:00:01 2014-01-06 11:22:55
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112254
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp
List of Archived Logs in backup set 21
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 1 1469692 2014-01-06 11:01:35 1470560 2014-01-06 11:22:53
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 829.66M DISK 00:00:49 2014-01-06 11:23:45
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112256
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/system01.dbf
2 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/undotbs01.dbf
3 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/sysaux01.dbf
4 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/users01.dbf
5 Full 1470565 2014-01-06 11:22:56 /oracle/app/oradata/orcl/whj.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
23 2.00K DISK 00:00:01 2014-01-06 11:23:52
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112351
Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112351_9dn8g89s_.bkp
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 2 1470560 2014-01-06 11:22:53 1470586 2014-01-06 11:23:51
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24 Full 6.80M DISK 00:00:00 2014-01-06 11:23:53
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140106T112353
Piece Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp
Control File Included: Ckp SCN: 1470592 Ckp time: 2014-01-06 11:23:53
SPFILE Included: Modification time: 2014-01-06 10:41:33
现在备份成功后我们来模拟数据文件丢失后怎么恢复
删除users01.dbf数据文件
[oracle@ww orcl]$ rm users01.dbf
[oracle@ww orcl]$ ll
total 1668300
-rw-r----- 1 oracle dba 7061504 Jan 6 11:38 control01.ctl
-rw-r----- 1 oracle dba 52429312 Jan 6 11:22 redo01.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:23 redo02.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:36 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan 6 11:23 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 6 11:23 system01.dbf
-rw-r----- 1 oracle dba 31465472 Dec 7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan 6 11:23 undotbs01.dbf
-rw-r----- 1 oracle dba 33562624 Jan 6 11:23 whj.dbf
删除后操作users表空间下的表就会报错
SQL> insert into HR values(3,'aa');
insert into HR values(3,'aa')
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/app/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
users01.dbf数据文件已经不存在
恢复:
将users01数据文件处于离线状态
SQL> alter database datafile 4 offline;
Database altered.
在RMAN命令行下输入以下恢复命令
RMAN> restore datafile 4;
Starting restore at 2014-01-06 12:15:14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp tag=TAG20140106T112256
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2014-01-06 12:15:16
恢复成功~!现在看下/oracle/app/oradata/orcl目录下有没有users.dbf文件
[oracle@ww orcl]$ ll
total 1673428
-rw-r----- 1 oracle dba 7061504 Jan 6 12:15 control01.ctl
-rw-r----- 1 oracle dba 52429312 Jan 6 11:43 redo01.log
-rw-r----- 1 oracle dba 52429312 Jan 6 12:14 redo02.log
-rw-r----- 1 oracle dba 52429312 Jan 6 11:43 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan 6 12:10 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 6 12:01 system01.dbf
-rw-r----- 1 oracle dba 31465472 Dec 7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan 6 12:10 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Jan 6 12:15 users01.dbf
-rw-r----- 1 oracle dba 33562624 Jan 6 11:49 whj.dbf
[oracle@ww orcl]$ pwd
/oracle/app/oradata/orcl
数据文件恢复出来了,最后recover一下将他的检查点与其他数据文件同步就可以了
RMAN> recover datafile 4;
Starting recover at 2014-01-06 12:17:47
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_3_9dn9lmxy_.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_4_9dn9ls3q_.arc
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 2014-01-06 12:17:50
将users.dbf数据文件启动到在线状态
SQL> alter database datafile 4 online;
Database altered.
现在执行一下刚才的insert命令就可以成功了
SQL> insert into HR values(3,'aa');
1 row created.
SQL> select * from HR;
ID NAME
---------- --------------------
1 whj
3 aa
2 qq
接下来模拟控制文件全部丢失
[oracle@ww orcl]$ rm control01.ctl
[oracle@ww ~]$ rm control02.ctl
[oracle@ww ORCL]$ rm control03.ctl
我删除了所有的控制文件
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/app/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
现在数据库都不能正常关闭,报错说找不到控制文件
SQL> shutdown abort
ORACLE instance shut down.
只能强行关闭
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 557842432 bytes
Fixed Size 2085456 bytes
Variable Size 155192752 bytes
Database Buffers 394264576 bytes
Redo Buffers 6299648 bytes
在RMAN下启动数据库到nomount状态
RMAN> restore controlfile from autobackup;
Starting restore at 2014-01-06 13:31:59
using channel ORA_DISK_1
recovery area destination: /oracle/app/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/app/oradata/orcl/control01.ctl
output filename=/home/oracle/control02.ctl
output filename=/oracle/app/flash_recovery_area/ORCL/control03.ctl
Finished restore at 2014-01-06 13:32:04
从自动 备份里恢复控制文件
然后启动到mount状态下recover数据库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database ;
Starting recover at 2014-01-06 13:34:32
Starting implicit crosscheck backup at 2014-01-06 13:34:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2014-01-06 13:34:32
Starting implicit crosscheck copy at 2014-01-06 13:34:32
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2014-01-06 13:34:32
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
File Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
archive log thread 1 sequence 6 is already on disk as file /oracle/app/oradata/orcl/redo03.log
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc thread=1 sequence=5
archive log filename=/oracle/app/oradata/orcl/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-01-06 13:34:33
最后启动数据库到open,因为是不完全恢复需要resetlogs
RMAN> alter database open resetlogs;
database opened
好了 控制文件恢复成功,可以进行数据操作了
SQL> create table whj as select * from HR;
Table created.
SQL> select * from whj;
ID NAME
---------- --------------------
1 whj
3 aa
2 qq
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28834607/viewspace-1295254/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28834607/viewspace-1295254/