RMAN本地备份和异机恢复
一、 本地环境
1) env|grep ORA
ORACLE_SID=sdzy
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
查看参数文件
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/10.2.0/db_
1/dbs/spfilesdzy.ora
将参数文件转换成文本
SQL> create pfile='/opt/oracle/sdzy_pfile.ora' from spfile;
File created.
>cat /opt/oracle/sdzy_pfile.ora
sdzy.__db_cache_size=155189248
sdzy.__java_pool_size=4194304
sdzy.__large_pool_size=4194304
sdzy.__shared_pool_size=113246208
sdzy.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/sdzy/adump'
*.background_dump_dest='/opt/oracle/admin/sdzy/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/oracle/oradata/sdzy/control01.ctl','/opt/oracle/oradata/sdzy/control02.ctl','/opt/oracle/oradata/sdzy/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/sdzy/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='sdzy'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sdzyXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=279969792
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/sdzy/udump'
SQL> selectfile_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/opt/oracle/oradata/sdzy/system01.dbf SYSTEM
/opt/oracle/oradata/sdzy/undotbs01.dbf UNDOTBS1
/opt/oracle/oradata/sdzy/sysaux01.dbf SYSAUX
/opt/oracle/oradata/sdzy/users01.dbf USERS
/opt/oracle/oradata/sdzy/testtbs.dbf USERS
/opt/oracle/oradata/sdzy/hellotbs01.dbf USERS
6 rows selected.
查看控制文件
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/sdzy/control01.ctl
/opt/oracle/oradata/sdzy/control02.ctl
/opt/oracle/oradata/sdzy/control03.ctl
查看日志文件
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /opt/oracle/oradata/sdzy/redo01.log NO
2 ONLINE /opt/oracle/oradata/sdzy/redo02.log NO
3 ONLINE /opt/oracle/oradata/sdzy/redo03.log NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 31 52428800 1 NO CURRENT 358072 15-MAR-11
2 1 29 52428800 1 YES ACTIVE 357061 15-MAR-11
3 1 30 52428800 1 YES ACTIVE 358070 15-MAR-11
2) 执行测试数据生成SQL语句
CREATE TABLE departments(
department_id NUMBER(4),
department_nameVARCHAR2(30),
manager_id NUMBER(6),
location_id NUMBER(4))
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE users;
>vi /opt/oracle/insert.sql
declare
i integer;
begin
i := 1;
loop
insert into departments
values
(100 + i,
'a',
i,
1000);
i := i + 1;
exit when i > 2000;
end loop;
commit;
end;
SQL> @/opt/oracle/insert.sql;
17 /
SQL> select count(*) from departments;
COUNT(*)
----------
2000
3) 创建RMAN全备脚本
>vi /opt/oracle/rman_sdzy_full.sh
#!/bin/sh
#数据库全备，同时备份控制文件及归档日志文件，备份文件保存至/opt/oracle/sdzy/rmanbak目录下，并在完成归档日志文件备份后，自动删除已备份的归档日志
#RMAN配置了自动备份控制文件，另外全备也做了控制文件备份，不冲突
#备份保留7天，过期则自动删除
#保留操作日志备查到/opt/oracle/sdzy/rmanbak/rman_sdzy_`date +%Y%m%d-%H%M`.log
#事先创建好/opt/oracle/sdzy/rmanbak目录
ORACLE_SID=sdzy
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID ORACLE_BASE ORACLE_HOME
export PATH=$ORACLE_HOME/bin:${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
RMAN=$ORACLE_HOME/bin/rman
$RMAN target / msglog=/opt/oracle/sdzy/rmanbak/rman_sdzy_`date +%Y%m%d-%H%M`.log<
run
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
ALLOCATE CHANNEL t1 TYPE DISK;
ALLOCATE CHANNEL t2 TYPE DISK;
ALLOCATE CHANNEL t3 TYPE DISK;
backup full database format '/opt/oracle/sdzy/rmanbak/%d_full_%u_%s_%p' tag='full_db' include current controlfile;
sql 'alter system archive log current';
backup format '/opt/oracle/sdzy/rmanbak/%d_arch_%T_%t_%U' archivelog all delete all input;
release channel t1;
release channel t2;
release channel t3;
}
4) 执行备份脚本
>sh/opt/oracle/rman_sdzy_full.sh
5) 查看备份情况
oracle@linux-o154:~/sdzy/rmanbak>ll
total 875220
-rw-r----- 1 oracle oinstall 7143424 2011-03-15 21:04 c-1338037274-20110315-00
-rw-r----- 1 oracle oinstall 7143424 2011-03-15 21:04 c-1338037274-20110315-01
-rw-r--r-- 1 oracle oinstall 5555 2011-03-15 21:04 rman_sdzy_20110315-2102.log
-rw-r----- 1 oracle oinstall 30449152 2011-03-15 21:04 SDZY_arch_20110315_745880668_06m7af2s_1_1
-rw-r----- 1 oracle oinstall 150016 2011-03-15 21:04 SDZY_arch_20110315_745880668_07m7af2s_1_1
-rw-r----- 1 oracle oinstall 10651648 2011-03-15 21:04 SDZY_arch_20110315_745880668_08m7af2s_1_1
-rw-r----- 1 oracle oinstall 2457600 2011-03-15 21:03 SDZY_full_01m7af06_1_1
-rw-r----- 1 oracle oinstall 368779264 2011-03-15 21:03 SDZY_full_02m7af06_2_1
-rw-r----- 1 oracle oinstall 461455360 2011-03-15 21:04 SDZY_full_03m7af06_3_1
-rw-r----- 1 oracle oinstall 7110656 2011-03-15 21:04 SDZY_full_04m7af0f_4_1
查看库备份情况：
RMAN> list backup of database;
//之前备份的数据文件是6个，这里查询的文件也是6个
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.34M DISK 00:00:04 15-MAR-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
4 Full 359516 15-MAR-11 /opt/oracle/oradata/sdzy/users01.dbf
5 Full 331018 28-FEB-11 /opt/oracle/oradata/sdzy/testtbs.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 351.69M DISK 00:01:01 15-MAR-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1
List of Datafiles in backup set 2
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
1 Full 359517 15-MAR-11 /opt/oracle/oradata/sdzy/system01.dbf
6 Full 359517 15-MAR-11 /opt/oracle/oradata/sdzy/hellotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 440.07M DISK 00:01:07 15-MAR-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1
List of Datafiles in backup set 4
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
2 Full 359518 15-MAR-11 /opt/oracle/oradata/sdzy/undotbs01.dbf
3 Full 359518 15-MAR-11 /opt/oracle/oradata/sdzy/sysaux01.dbf
查看控制文件备份情况：
RMAN> list backup of controlfile;
//之前查询的控制文件是3个，这里自动备份的控制文件是2个，全备自动备一个
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 6.77M DISK 00:00:58 15-MAR-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_04m7af0f_4_1//全备including
Control File Included: Ckp SCN: 359526 Ckp time: 15-MAR-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 6.80M DISK 00:00:01 15-MAR-11
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210419
Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-00//自动备份
Control File Included: Ckp SCN: 359556 Ckp time: 15-MAR-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 6.80M DISK 00:00:00 15-MAR-11
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210433
Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-01//自动备份
Control File Included: Ckp SCN: 359580 Ckp time: 15-MAR-11
查看归档文件备份
RMAN> list backup of archivelog all;
//之前查询的日志文件是3个，这里备份后的备份结果也是3个
List of Backup Sets
===================
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 146.00K DISK 00:00:02 15-MAR-11
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1
List of Archived Logs in backup set 6
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 29 357061 15-MAR-11 358070 15-MAR-11
1 30 358070 15-MAR-11 358072 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 10.16M DISK 00:00:03 15-MAR-11
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1
List of Archived Logs in backup set 7
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 31 358072 15-MAR-11 359568 15-MAR-11
1 32 359568 15-MAR-11 359573 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 29.04M DISK 00:00:03 15-MAR-11
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1
List of Archived Logs in backup set 8
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 28 315840 30-OCT-10 357061 15-MAR-11
二、 异机环境
1. 准备一台同等机器环境，安装相同的OS和数据库软件
oracle@linux-o154:~>env|grep ORA
ORACLE_SID=sdzy
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
>mkdir -p /opt/oracle/sdzy/rmanbak
2. 将备份文件拷贝到异机环境中
>cd /opt/oracle/sdzy/rmanbak/
>scp * 192.168.219.55:/opt/oracle/sdzy/rmanbak
Password:
c-1338037274-20110315-00 100% 6976KB 6.8MB/s 00:00
c-1338037274-20110315-01 100% 6976KB 6.8MB/s 00:01
rman_sdzy_20110315-2102.log 100% 5555 5.4KB/s 00:00
SDZY_arch_20110315_745880668_06m7af2s_1_1 100% 29MB 9.7MB/s 00:03
SDZY_arch_20110315_745880668_07m7af2s_1_1 100% 147KB 146.5KB/s 00:00
SDZY_arch_20110315_745880668_08m7af2s_1_1 100% 10MB 10.2MB/s 00:01
SDZY_full_01m7af06_1_1 100% 2400KB 2.3MB/s 00:00
SDZY_full_02m7af06_2_1 100% 352MB 9.5MB/s 00:37
SDZY_full_03m7af06_3_1 100% 440MB 9.8MB/s 00:45
SDZY_full_04m7af0f_4_1 100% 6944KB 6.8MB/s 00:01
3. 创建口令文件
口令文件一般是在$ORACLE_HOME/dbs/目录下
作用：主要进行DBA权限的身份认证
DBA用户：具有sysdba，sysoper权限的用户被称为dba用户。默认情况下sysdba角色中存在sys用户，sysoper角色中存在system用户
>orapwd file=$ORACLE_HOME/dbs/orapwsdzy password=oracle
4. 将参数文件拷贝过来
>scpsdzy_pfile.ora 192.168.219.55:/opt/oracle
Password:
sdzy_pfile.ora 100% 2560 2.5KB/s 00:00
5. 依照参数文件在目标机上创建目录
mkdir -p /opt/oracle/admin/sdzy/adump
mkdir -p /opt/oracle/admin/sdzy/bdump
mkdir -p /opt/oracle/oradata/sdzy
mkdir -p /opt/oracle/admin/sdzy/cdump
mkdir -p /opt/oracle/flash_recovery_area
mkdir -p /opt/oracle/admin/sdzy/udump
6. 进入RMAN
oracle@linux-o154:~>rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 14 22:45:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
7. 恢复参数文件
RMAN> set DBID=1338037274;
RMAN> startup nomountpfile=/opt/oracle/sdzy_pfile.ora;
Oracle instance started
Total System Global Area 281018368 bytes
Fixed Size 1261348 bytes
Variable Size 113246428 bytes
Database Buffers 163577856 bytes
Redo Buffers 2932736 bytes
8. 恢复控制文件
RMAN>restore controlfile from '/opt/oracle/sdzy/rmanbak/c-1338037274-20110315-01';//以之前查询到的Ckp SCN: 359580最大值恢复，否则可能出现部分数据文件和日志文件查询不到的情况
Starting restore at 15-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/opt/oracle/oradata/sdzy/control01.ctl
output filename=/opt/oracle/oradata/sdzy/control02.ctl
output filename=/opt/oracle/oradata/sdzy/control03.ctl
Finished restore at 15-MAR-11
9. 将数据库启动到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
10. 查看备份日志
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 2.34M DISK 00:00:04 15-MAR-11
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
4 Full 359516 15-MAR-11 /opt/oracle/oradata/sdzy/users01.dbf
5 Full 331018 28-FEB-11 /opt/oracle/oradata/sdzy/testtbs.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 351.69M DISK 00:01:01 15-MAR-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1
List of Datafiles in backup set 2
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
1 Full 359517 15-MAR-11 /opt/oracle/oradata/sdzy/system01.dbf
6 Full 359517 15-MAR-11 /opt/oracle/oradata/sdzy/hellotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 440.07M DISK 00:01:07 15-MAR-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1
List of Datafiles in backup set 4
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
2 Full 359518 15-MAR-11 /opt/oracle/oradata/sdzy/undotbs01.dbf
3 Full 359518 15-MAR-11 /opt/oracle/oradata/sdzy/sysaux01.dbf
查看归档日志和控制文件：
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 146.00K DISK 00:00:02 15-MAR-11
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1
List of Archived Logs in backup set 6
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 29 357061 15-MAR-11 358070 15-MAR-11
1 30 358070 15-MAR-11 358072 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 10.16M DISK 00:00:03 15-MAR-11
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1
List of Archived Logs in backup set 7
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 31 358072 15-MAR-11 359568 15-MAR-11
1 32 359568 15-MAR-11 359573 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 29.04M DISK 00:00:03 15-MAR-11
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1
List of Archived Logs in backup set 8
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 28 315840 30-OCT-10 357061 15-MAR-11
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 6.77M DISK 00:00:58 15-MAR-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL_DB
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_04m7af0f_4_1
Control File Included: Ckp SCN: 359526 Ckp time: 15-MAR-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 6.80M DISK 00:00:01 15-MAR-11
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210419
Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-00
Control File Included: Ckp SCN: 359556 Ckp time: 15-MAR-11
11. 恢复数据文件
RMAN> restore database;
Starting restore at 15-MAR-11
Starting implicit crosscheck backup at 15-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 15-MAR-11
Starting implicit crosscheck copy at 15-MAR-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-MAR-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafilebackupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00004 to /opt/oracle/oradata/sdzy/users01.dbf
restoringdatafile 00005 to /opt/oracle/oradata/sdzy/testtbs.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1 tag=FULL_DB
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafilebackupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00001 to /opt/oracle/oradata/sdzy/system01.dbf
restoringdatafile 00006 to /opt/oracle/oradata/sdzy/hellotbs01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1 tag=FULL_DB
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafilebackupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00002 to /opt/oracle/oradata/sdzy/undotbs01.dbf
restoringdatafile 00003 to /opt/oracle/oradata/sdzy/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1 tag=FULL_DB
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 15-MAR-11
查看恢复数据文件情况：
# ls -l
total 3245662
-rw-r----- 1 oracle oinstall 7061504 Mar 15 05:39 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 15 05:39 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 15 05:39 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 15 05:37 hellotbs01.dbf
-rw-r----- 1 oracle oinstall 251666432 Mar 15 05:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Mar 15 05:37 system01.dbf
-rw-r----- 1 oracle oinstall 2097160192 Mar 15 05:37 testtbs.dbf
-rw-r----- 1 oracle oinstall 309338112 Mar 15 05:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall 32776192 Mar 15 05:37 users01.dbf
12. 恢复数据库
RMAN> recover database;
//会报错，但不要紧，我们只需要得到thread和sequence号就行
Starting recover at 15-MAR-11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=29
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1 tag=TAG20110315T210427
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=31
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=32
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1 tag=TAG20110315T210427
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=28
channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1 tag=TAG20110315T210427
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_28_6qyf0jh7_.arc thread=1 sequence=28
channel default: deleting archive log(s)
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_28_6qyf0jh7_.arc recid=10 stamp=745825233
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_29_6qyf0gcr_.arc thread=1 sequence=29
channel default: deleting archive log(s)
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_29_6qyf0gcr_.arc recid=6 stamp=745825230
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_30_6qyf0gks_.arc thread=1 sequence=30
channel default: deleting archive log(s)
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_30_6qyf0gks_.arc recid=7 stamp=745825230
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_31_6qyf0hf5_.arc thread=1 sequence=31
channel default: deleting archive log(s)
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_31_6qyf0hf5_.arc recid=9 stamp=745825231
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_32_6qyf0hhd_.arc thread=1 sequence=32
channel default: deleting archive log(s)
archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_32_6qyf0hhd_.arc recid=8 stamp=745825231
unable to find archive log
archive log thread=1 sequence=33
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/15/2011 05:40:35
RMAN-06054: media recovery requesting unknown log: thread 1 seq 33 lowscn 359573
//找到最接近seq 33 lowscn 359573的归档日志，是 32 359568
查询归档文件
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 146.00K DISK 00:00:02 15-MAR-11
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1
List of Archived Logs in backup set 6
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 29 357061 15-MAR-11 358070 15-MAR-11
1 30 358070 15-MAR-11 358072 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 10.16M DISK 00:00:03 15-MAR-11
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1
List of Archived Logs in backup set 7
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 31 358072 15-MAR-11 359568 15-MAR-11
1 32 359568 15-MAR-11 359573 15-MAR-11
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 29.04M DISK 00:00:03 15-MAR-11
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20110315T210427
Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1
List of Archived Logs in backup set 8
ThrdSeq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 28 315840 30-OCT-10 357061 15-MAR-11
再执行恢复
RMAN> recover database until scn 359568;
Starting recover at 15-MAR-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/15/2011 05:49:24
RMAN-06556: datafile 1 must be restored from backup older than scn 359568
报RMAN-06556: datafile 1 must be restored from backup older than scn 359568
13. 可以直接 alter database open resetlogs命令打开数据库
RMAN> alter database open resetlogs;
database opened
14. 检查恢复情况
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/opt/oracle/oradata/sdzy/system01.dbf SYSTEM
/opt/oracle/oradata/sdzy/undotbs01.dbf UNDOTBS1
/opt/oracle/oradata/sdzy/sysaux01.dbf SYSAUX
/opt/oracle/oradata/sdzy/users01.dbf USERS
/opt/oracle/oradata/sdzy/testtbs.dbf USERS
/opt/oracle/oradata/sdzy/hellotbs01.dbf USERS
6 rows selected.
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/sdzy/control01.ctl
/opt/oracle/oradata/sdzy/control02.ctl
/opt/oracle/oradata/sdzy/control03.ctl
SQL> /
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
1 ONLINE /opt/oracle/oradata/sdzy/redo01.log NO
2 ONLINE /opt/oracle/oradata/sdzy/redo02.log NO
3 ONLINE /opt/oracle/oradata/sdzy/redo03.log NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 359574 15-MAR-11
SQL> select count(*) from departments;
COUNT(*)
----------
2000
//测试表数据得到恢复
SQL> create spfile from pfile='/opt/oracle/sdzy_pfile.ora';
File created.
//否则以后启动的时候会找PFILE文件
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1261348 bytes
Variable Size 125829340 bytes
Database Buffers 150994944 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/10.2.0/db_
1/dbs/spfilesdzy.ora
15. 恢复完成后，做一次全备