1.RMAN 整库备份与恢复
1.1备份全库
--对整个数据库进行全备份
backup database;
--备份全库及控制文件、服务器参数文件与所有归档的重做日志，并删除旧的归档日志
backup database plus archivelog delete input;
1.2全库恢复
rm -rf /oracle/u01/app/oracle/oradata/orcl/*
[oracle@localhost orcl]$ ls
archivelog.bak datafile leo01.dbf.bak system01.dbf.bak undotbs01.dbf.bak
control01.ctl.bak example01.dbf.bak sysaux01.dbf.bak temp01.dbf.bak users01.dbf.bak
[oracle@localhost orcl]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 00:00:08 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/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.
SQL> startup
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 889193600 bytes
Database Buffers 146800640 bytes
Redo Buffers 5632000 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 4 00:01:04 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/oradata/backup/control/cf_c-1468135053-20170503-07';
Starting restore at 04-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 04-MAY-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 04-MAY-17
Starting implicit crosscheck backup at 04-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 28 objects
Finished implicit crosscheck backup at 04-MAY-17
Starting implicit crosscheck copy at 04-MAY-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 04-MAY-17
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
datafile 4 not processed because file is offline
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_10s3bnr6_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_10s3bnr6_1_1 tag=TAG20170503T233814
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 04-MAY-17
RMAN> recover database;
Starting recover at 04-MAY-17
using channel ORA_DISK_1
datafile 4 not processed because file is offline
starting media recovery
archived log for thread 1 with sequence 87 is already on disk as file /u01/archive/1_87_940354317.arc
archived log for thread 1 with sequence 89 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_2_dg6f7w2x_.log
archived log for thread 1 with sequence 90 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_3_dg6f7xhp_.log
archived log for thread 1 with sequence 91 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_1_dg6f7tf5_.log
archived log file name=/u01/archive/1_87_940354317.arc thread=1 sequence=87
archived log file name=/u01/archive/1_88_940354317.arc thread=1 sequence=88
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_2_dg6f7w2x_.log thread=1 sequence=89
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_3_dg6f7xhp_.log thread=1 sequence=90
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL_ST/onlinelog/o1_mf_1_dg6f7tf5_.log thread=1 sequence=91
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-MAY-17
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@localhost orcl]$ ls
archivelog datafile leo01.dbf sysaux01.dbf.bak temp01.dbf undotbs01.dbf.bak
control01.ctl example01.dbf leo01.dbf.bak system01.dbf temp01.dbf.bak users01.dbf.bak
control01.ctl.bak example01.dbf.bak sysaux01.dbf system01.dbf.bak undotbs01.dbf
[oracle@localhost orcl]$
SQL> select count(*) from leo;
COUNT(*)
----------
68317
2.RMAN 表空间备份与恢复
2.1备份表空间
备份指定表空间及归档的重做日志，并删除旧的归档日志
backup tablespace leo plus archivelog delete input;
2.2恢复表空间
[root@localhost orcl]# pwd
/u01/app/oracle/oradata/orcl
[root@localhost orcl]# rm -rf leo01.dbf
run{sql sql "alter tablespace users offline";//如果文件不存在，则用 sql "alter tablespace users offline immeidate";
restore tablespace leo;
recover tablespace leo; //与online redolog file 信息一致
sql "alter tablespace leo online";}
RMAN> run{sql "alter tablespace leo offline immediate";
restore tablespace leo;
recover tablespace leo;
4> sql "alter tablespace leo online";}
sql statement: alter tablespace leo offline immediate
Starting restore at 04-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_10s3bnr6_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_10s3bnr6_1_1 tag=TAG20170503T233814
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 04-MAY-17
Starting recover at 04-MAY-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 87 is already on disk as file /u01/archive/1_87_940354317.arc
archived log for thread 1 with sequence 88 is already on disk as file /u01/archive/1_88_940354317.arc
archived log for thread 1 with sequence 89 is already on disk as file /u01/archive/1_89_940354317.arc
archived log for thread 1 with sequence 90 is already on disk as file /u01/archive/1_90_940354317.arc
archived log for thread 1 with sequence 91 is already on disk as file /u01/archive/1_91_940354317.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/archive/1_1_943056305.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/archive/1_2_943056305.arc
archived log file name=/u01/archive/1_87_940354317.arc thread=1 sequence=87
archived log file name=/u01/archive/1_88_940354317.arc thread=1 sequence=88
archived log file name=/u01/archive/1_89_940354317.arc thread=1 sequence=89
archived log file name=/u01/archive/1_90_940354317.arc thread=1 sequence=90
archived log file name=/u01/archive/1_91_940354317.arc thread=1 sequence=91
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-17
sql statement: alter tablespace leo online
SQL> select count(*) from leo;
COUNT(*)
----------
68317
3 RMAN 数据文件级备份与恢复
3.1备份数据文件
backup datafile '/u01/app/oracle/oradata/orcl/leo01.dbf';
3.2恢复数据文件
[root@localhost orcl]# pwd
/u01/app/oracle/oradata/orcl
[root@localhost orcl]# rm -rf leo01.dbf
run{sql "alter database datafile 6 offline";
restore datafile 6;
recover datafile 6;
sql "alter database datafile 6 online";}
RMAN>run{sql "alter database datafile 6 offline";
restore datafile 6;
recover datafile 6;
4> sql "alter database datafile 6 online";}
sql statement: alter database datafile 6 offline
Starting restore at 04-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_10s3bnr6_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_10s3bnr6_1_1 tag=TAG20170503T233814
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 04-MAY-17
Starting recover at 04-MAY-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 87 is already on disk as file /u01/archive/1_87_940354317.arc
archived log for thread 1 with sequence 88 is already on disk as file /u01/archive/1_88_940354317.arc
archived log for thread 1 with sequence 89 is already on disk as file /u01/archive/1_89_940354317.arc
archived log for thread 1 with sequence 90 is already on disk as file /u01/archive/1_90_940354317.arc
archived log for thread 1 with sequence 91 is already on disk as file /u01/archive/1_91_940354317.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/archive/1_1_943056305.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/archive/1_2_943056305.arc
archived log file name=/u01/archive/1_87_940354317.arc thread=1 sequence=87
archived log file name=/u01/archive/1_88_940354317.arc thread=1 sequence=88
archived log file name=/u01/archive/1_89_940354317.arc thread=1 sequence=89
archived log file name=/u01/archive/1_90_940354317.arc thread=1 sequence=90
archived log file name=/u01/archive/1_91_940354317.arc thread=1 sequence=91
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-17
sql statement: alter database datafile 6 online
4 备份归档日志
backup archivelog all delete input;
5 RMAN 的完全恢复与不完全恢复
5.1 基于时间点的恢复
1)第一种方式
run{
set until time "to_date(07/01/02 15:00:00','mm/dd/yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
2)第二种方式
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
1.startup mount;
2.restore database until time "to_date('2009-7-19 13:19:00','YYYY-MM-DD HH24:MI:SS')";
3.recover database until time "to_date('2009-7-19 13:19:00','YYYY-MM-DD HH24:MI:SS')";
4.alter database open resetlogs;
--实验脚本
run{
set until time "to_date('05/04/2017 03:26:23','mm/dd/yyyy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
--注意事项
这种恢复类型允许用户将数据库恢复到与指定时间一致的状态。 当然，如果不存在能将数据库还原到用户请求的时间的有效备份或归档重做日志，Oracle 就会报RMAN-03002 和 RMAN-20207的错误。
必须具备在我们指定的恢复时间之前生成的数据库备份，此外还需要所有归档的重做日志。
--全库备份
RMAN> backup database;
Starting backup at 04-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/leo01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-MAY-17
channel ORA_DISK_1: finished piece 1 at 04-MAY-17
piece handle=/u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1 tag=TAG20170504T032222 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 04-MAY-17
Starting Control File and SPFILE Autobackup at 04-MAY-17
piece handle=/u01/app/oracle/oradata/backup/control/cf_c-1468135053-20170504-06 comment=NONE
Finished Control File and SPFILE Autobackup at 04-MAY-17
RMAN> exit
Recovery Manager complete.
--打开显示完整时间
SQL> conn leo/leo
Connected.
SQL> set time on;
--创建表
03:24:42 SQL> create table leo1 (id number(10));
Table created.
--插入数据并提交， 为了保持数据一致性，最好使用sys用户手动切换日志（ALTER SYSTEM SWITCHLOGFILE）和触发CKPT（ALTERSYSTEM CHECKPOINT）
03:24:52 SQL> insert into leo1 values(10);
1 row created.
03:25:08 SQL> commit;
Commit complete.
03:25:38 SQL> select * from leo1;
ID
----------
10
03:25:50 SQL> conn /as sysdba
Connected.
03:25:56 SQL> alter system switch logfile;
System altered.
03:26:17 SQL> conn leo/leo
Connected.
03:26:23 SQL> drop table leo1 purge;
Table dropped.
03:26:41 SQL> shutdown immediate
ORA-01031: insufficient privileges
03:26:47 SQL> conn /as sysdba
Connected.
03:26:55 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
03:27:05 SQL> !rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 4 03:28:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 889193600 bytes
Database Buffers 146800640 bytes
Redo Buffers 5632000 bytes
RMAN中查看当前INCARNATION号，注意：如果有其他的操作，以后恢复时应该选取当前查看到的INCARNATION号，使用RESET DATABASE TO INCARNATION <INCARNATION号>命令
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1468135053 PARENT 1 24-AUG-13
2 2 ORCL 1468135053 PARENT 925702 03-APR-17
3 3 ORCL 1468135053 PARENT 1440351 04-MAY-17
4 4 ORCL 1468135053 CURRENT 1456377 04-MAY-17
编辑基于时间点恢复的脚本，注意时间点的选取，此处选取为删除表的时间点（03:26:23），注意时间点尤其重要
run{
set until time "to_date('05/04/2017 03:26:23','mm/dd/yyyy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
executing command: SET until clause
Starting restore at 04-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1 tag=TAG20170504T032222
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 04-MAY-17
Starting recover at 04-MAY-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-17
database opened
RMAN> exit
Recovery Manager complete.
03:32:22 SQL> conn leo/leo
Connected.
--验证被删掉的表的数据
03:32:25 SQL> select * from leo1;
ID
----------
10
5.2 基于 SCN的恢复
实验脚本
run {
set until scn=1463992;
restore database;
recover database;
alter database open resetlogs;}
也可以用下面步骤来执行
1.startup mount;
2.restore database until scn 10000;
3.recover database until scn 10000;
4.alter database open resetlogs;
SQL> conn /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1463992
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/leo01.dbf
6 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls
archivelog datafile leo01.dbf sysaux01.dbf.bak temp01.dbf undotbs01.dbf.bak
control01.ctl example01.dbf leo01.dbf.bak system01.dbf temp01.dbf.bak users01.dbf
control01.ctl.bak example01.dbf.bak sysaux01.dbf system01.dbf.bak undotbs01.dbf
[oracle@localhost orcl]$ rm -rf leo01.dbf
[oracle@localhost orcl]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 04:13:48 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 889193600 bytes
Database Buffers 146800640 bytes
Redo Buffers 5632000 bytes
Database mounted.
SQL> !rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 4 04:21:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1468135053, not open)
--rman scn 恢复数据库
run {
set until scn=1463992;
restore database;
recover database;
5> alter database open resetlogs;}
executing command: SET until clause
Starting restore at 04-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1 tag=TAG20170504T032222
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 04-MAY-17
Starting recover at 04-MAY-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/archive/1_9_943066955.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/archive/1_10_943066955.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/archive/1_1_943068610.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/archive/1_2_943068610.arc
archived log file name=/u01/archive/1_9_943066955.arc thread=1 sequence=9
archived log file name=/u01/archive/1_10_943066955.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-MAY-17
database opened
RMAN> exit
Recovery Manager complete.
--数据库打开，表已恢复
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> conn leo/leo
Connected.
SQL> select * from leo1;
ID
----------
10
5.3 基于日志序列的恢复
--脚本
run {
set until sequence=3;
restore database;
recover database;
alter database open resetlogs;}
也可以分开执行
1.startup mount;
2.restore database until SEQUENCE 100 thread 1; //100是日志序列
3.recover database until SEQUENCE 100 thread 1;
4.alter database open resetlogs;
SQL> conn /as sysdba
Connected.
SQL> set lines 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 YES INACTIVE 1463993 04-MAY-17 1463996 04-MAY-17
2 1 2 52428800 512 1 YES INACTIVE 1463996 04-MAY-17 1464311 04-MAY-17
3 1 3 52428800 512 1 NO CURRENT 1464311 04-MAY-17 2.8147E+14
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/leo01.dbf
6 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost orcl]$ ls
archivelog datafile leo01.dbf sysaux01.dbf.bak temp01.dbf undotbs01.dbf.bak
control01.ctl example01.dbf leo01.dbf.bak system01.dbf temp01.dbf.bak users01.dbf
control01.ctl.bak example01.dbf.bak sysaux01.dbf system01.dbf.bak undotbs01.dbf
[oracle@localhost orcl]$ rm -rf leo01.dbf
[oracle@localhost orcl]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 04:30:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 889193600 bytes
Database Buffers 146800640 bytes
Redo Buffers 5632000 bytes
Database mounted.
SQL> ! rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 4 04:31:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1468135053, not open)
--基于日志序列号的恢复
run {
set until sequence=3;
restore database;
recover database;
5> alter database open resetlogs;}
executing command: SET until clause
Starting restore at 04-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/leo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/backup/db_1cs3c4ve_1_1 tag=TAG20170504T032222
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 04-MAY-17
Starting recover at 04-MAY-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/archive/1_9_943066955.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/archive/1_10_943066955.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/archive/1_1_943068610.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/archive/1_2_943068610.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/archive/1_3_943068610.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/archive/1_1_943071751.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/archive/1_2_943071751.arc
archived log file name=/u01/archive/1_9_943066955.arc thread=1 sequence=9
archived log file name=/u01/archive/1_10_943066955.arc thread=1 sequence=10
archived log file name=/u01/archive/1_1_943068610.arc thread=1 sequence=1
archived log file name=/u01/archive/1_2_943068610.arc thread=1 sequence=2
archived log file name=/u01/archive/1_3_943068610.arc thread=1 sequence=3
archived log file name=/u01/archive/1_1_943071751.arc thread=1 sequence=1
archived log file name=/u01/archive/1_2_943071751.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 04-MAY-17
database opened
RMAN> exit
Recovery Manager complete.
SQL> conn leo/leo
Connected.
SQL> select * from leo1;
ID
----------
10
6 RMAN 修复坏块
对于坏块，可以选择跳过坏块，如果有RMAN 备份的话，可以直接利用RMAN 来修复坏块。
先用RMAN 备份DB. 坏块可以利用BBED进行标记。
SQL> conn leo/leo
Connected.
select * from (
SELECT DBMS_ROWID.rowid_relative_fno(ROWID) rel_fno,
DBMS_ROWID.rowid_block_number(ROWID) blockno,
DBMS_ROWID.rowid_row_number(ROWID) rowno
5 FROM leo) where rownum<10;
REL_FNO BLOCKNO ROWNO
---------- ---------- ----------
6 131 0
6 131 1
6 131 2
6 131 3
6 131 4
6 131 5
6 131 6
6 131 7
6 131 8
9 rows selected.
BBED> corrupt dba 6,131
BBED> verify dba 6,131
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 4 20:54:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1468135053)
RMAN> blockrecover datafile 6 block 131;
Starting recover at 04-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-MAY-17
RMAN> exit
Recovery Manager complete.
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 20:55:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> conn leo/leo
Connected.
SQL> select count(*) from leo;
COUNT(*)
----------
68317
7 修改RMAN 的参数，如备份保留策略
可以显示出RMAN 配置参数为:
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL_PD 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 '/u01/app/oracle/oradata/backup/control/cf_%F';
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 CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/backup/db_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE EXCLUDE FOR TABLESPACE 'LEO';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default
查询RMAN设置中非默认值:
SQL> select name,value from v$rman_configuration;
NAME VALUE
----------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------
CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/backup/db_%U'
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/oradata/backup/control/cf_%F'
CONTROLFILE AUTOBACKUP ON
常用的configure选项
--保留策略 retention policy
configure retention policy to recovery window of 7 days;
configure retention policy to redundancy 5;
configure retention policy clear;
CONFIGURE RETENTION POLICY TO NONE;
第一种recover window是保持所有足够的备份，可以将数据库系统恢复到最近七天内的任意时刻。任何超过最近七天的数据库备份将被标记为obsolete。
第二种redundancy 是为了保持可以恢复的最新的5份数据库备份，任何超过最新5份的备份都将被标记为redundancy。它的默认值是1份。
第三四：NONE 可以把使备份保持策略失效，Clear 将恢复默认的保持策略
一般最安全的方法是采用第二种保持策略。
--备份优化 backup optimization
configure backup optimization on;
configure backup optimization off;
configure backup optimization clear;
默认值为关闭，如果打开，rman将对备份的数据文件及归档等文件进行一种优化的算法。
--默认设备 default device type
configure default device type to disk;
configure default device type to stb;
configure default device type clear;
是指定所有I/O操作的设备类型是硬盘或者磁带，默认值是硬盘
磁带的设置是CONFIGURE DEFAULT DEVICE TYPE TO SBT;
--控制文件 controlfile
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/cfs01/backup/conf/conf_%F';
configure controlfile autobackup clear;
configrue controlfile autobackup format for device type disk clear;
--并行数(通道数) device type disk|stb pallelism n;
configure device type disk|stb parallelism 2;
configure device type disk|stb clear; --用于清除上面的信道配置
configure channel device type disk format 'e/:rmanback_%U';
configure channel device type disk maxpiecesize 100m
configure channel device type disk rate 1200K
configure channel 1 device type disk format 'e/:rmanback_%U';
configure channel 2 device type disk format 'e/:rmanback_%U';
configure channel 1 device type disk maxpiecesize 100m
配置数据库设备类型的并行度。
--生成备份副本 datafile|archivelog backup copies
configure datafile backup copies for device type disk|stb to 3;
configure archivelog backup copies for device type disk|stb to 3;
--是设置数据库的归档日志的存放设备类型
configure datafile|archivelog backup copies for device type disk|stb clear
是配置数据库的每次备份的copy数量，oracle的每一次备份都可以有多份完全相同的拷贝。
--排除选项 exclude
configure exclude for tablespace 'LEO';
configrue exclude clear;
此命令用于将指定的表空间不备份到备份集中， 此命令对只读表空间是非常有用的。
--备份集大小 maxsetsize
configure maxsetsize to 1G|1000M|1000000K|unlimited;
configure maxsetsize clear;
--其它选项 auxiliary
CONFIGURE AUXNAME FOR DATAFILE 1 TO '/u01/backup/auxfiles/aux_1.f';
CONFIGURE AUXNAME FOR DATAFILE 2 TO '/u01/backup/auxfiles/aux_2.f';
CONFIGURE AUXNAME FOR DATAFILE 3 TO '/u01/backup/auxfiles/aux_3.f';
CONFIGURE AUXNAME FOR DATAFILE 4 TO '/u01/backup/auxfiles/aux_4.f';
-
CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 3 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 4 CLEAR;
Rman的format格式中的%
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为
日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)