--RMAN
PATH:$ORACLE_HOME/bin
1.数据库的运行状态
nomount init.ora spfile.ora
mount controlfile
open 打开数据库检查所有文件处于同一时间点,对错误进行恢复对未完成的事务做回滚,并最终可以准时用户访问
2.归档模式
数据库使用归档日志来完成rman的备份
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
for 10g
shutdown immediate
startup mount
alter database archivelog;
alter database open;
for 9i
shutdown immediate
startup mount
alter database archivelog;
alter system archive log start;(必须执行,否则会宕机)
(alter system set log_archive_start=true scope=spfile;------******------)
alter database open;
3.alter system set log_archive_dest_1="location=/opt/oracle/archivelog/";
log_archive_format string %t_%s_%r.dbf 文件保存的格式
4.rman的环境和配置
a.建产rman表空间
SQL> create tablespace rman datafile '/sda8/rman/rman.dbf'
2 size 500m extent management local segment space management auto;
Tablespace created.
b.创建用户
SQL> create user rman identified by rman default tablespace rman;
User created.
c.授权
SQL> grant connect,resource,recovery_catalog_owner to rman;
Grant succeeded.
d.登录
[oracle@pi database]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 3 14:45:14 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: HAO (DBID=1164604165)
RMAN>
5.rman基本配置
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default 1、配置备份策略
CONFIGURE BACKUP OPTIMIZATION OFF; # default 2、配置备份优化
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default 3、配置设备类型
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 4、配置控制文件自动备份
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
5、配置控制文件备份命名方式
6、配置控制文件备份路径
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
7、配置备份并行度
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
8、配置数据/归档文件的冗余数量
CONFIGURE MAXSETSIZE TO UNLIMITED; # default 9、配置备份片段最大尺寸
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default 10、配置备份是否启用加密模式
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default 11、配置备份加密算法
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default 12、配置备份压缩方式
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default 13配置归档文件的删除策略
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/data/oracle/product/11.2/db_1/dbs/snapcf_pmh.f'; # default 14、配置控制文件快照,用于控制文件的读一致性
以下为命令修改参数设置:
RMAN>configure retention policy CLEAR; RMAN配置参数重新重置为默认值
RMAN>configure retention policy to recovery WINDOW OF n days; 基于时间的保留策略.
RMAN>configure retention policy to recovery REDUNDANCY n; 基于冗余数量的保留策略(默认为1)
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; --保留三天
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
configure channel 1 device type disk format '/opt/oracle/database/temp1/rmanback_%u';
backup controlfile
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
backup format '/opt/oracle/database/temp1/ctl_%u_%c_%p.ctl' current controlfile;
release channel a1;
release channel a2;
}
sql 'alter system archive log current'; ----在RMAN里面执行SQL语句,切换归档日志
delete noprompt obsolete; ----------删除久的,不提示直接删除
run {
allocate channel a1 device type disk;
allocate channel a2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql 'alter system archive log current';
backup database format '/opt/oracle/database/temp1/dataall_%d_%T_%U';
sql 'alter system archive log current';
backup archivelog all format '/sda8/log/archive_%t_%s' delete all input;
backup format '/sda8/log/con_%s_%p' current controlfile;
crosscheck backup;
crosscheck archivelog all;
delete expired backup; 删除过期
delete noprompt obsolete;
delete noprompt backup of database completed before 'sysdate -15';
delete noprompt archivelog all;
delete noprompt backup of archivelog all completed before 'sysdate -15';
release channel a1;
release channel a2;
}
备份格式
备份文件自定义格式:
%d 数据库名称
%c 备份片的拷贝数
%D 位于该月的第几天 -----常用
%M 位于该年的第几月
%F 一个基于dbid唯一的名称,这个格式的形式比较全面c-iiiiiiiiii-yyyymmdd-qq -----常用
iiiiiiiiii:dbid yyyymmdd为日期 qq:1-256的序列号
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
1164604165 HAO
%n 数据库名称,向右补足到最大8个字符
%u 一个八个字符的名称代表备份集于创建时间
%U 一个唯一的文件名,代表%u_%p_%c -----常用
%P 该备份集的备份片号,从1开始到创建的文件数
%t 备份集的时间戳
%T yyyymmdd
查看备份文件的大小
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
--------------------
1590
SQL> select sum(bytes)/1024/1024 from dba_free_space;
SUM(BYTES)/1024/1024
--------------------
707.375
临控事务运行时间
select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_precent"
from v$session_longops
where opname like 'RMAN%%'
and opname not like '%aggregate%'
and totalwork!=0
and sofar<>totalwork;
SID SERIAL# CONTEXT SOFAR TOTALWORK %_precent
---------- ---------- ---------- ---------- ---------- ----------
141 53 1 92350 128000 72.15
144 58 1 69375 75520 91.86
断点续备
backup not backup since time 'sysdate -1'database plus archivelog;
RMAN数据恢复
A.整个数据库恢复
1.shutdown immediate
2.startup mount
3.rman target /
4.restore database;
5.recover database;
6.alter database open;
B.控制文件恢复
1.shutdown immediate
2.startup nomount
3.rman target /
4.第一种方法
RMAN> run{
2> allocate channel d1 type disk;
3> restore controlfile from '/opt/oracle/flash_recovery_area/HAO/autobackup/2009_11_05/o1_mf_s_702121736_5h4cr8kf_.bkp';
4> alter database mount;
5> recover database;
6> alter database open resetlogs;
7> }
5.第二种方法
RMAN> run {
2> allocate channel t1 type disk;
3> restore controlfile from autobackup;
4> alter database mount;
5> recover database;
6> alter database open resetlogs;
7> }
C.DBF文件被RM了
1.查看alert.log文件,查出那个文件丢失
2.在数据库查询对应DBF文件的编号
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /opt/oracle/oradata/hao/system01.dbf SYSTEM
2 /opt/oracle/oradata/hao/undotbs01.dbf ONLINE
3 /opt/oracle/oradata/hao/sysaux01.dbf ONLINE
4 /opt/oracle/oradata/hao/users01.dbf ONLINE
5 /opt/oracle/oradata/hao/example01.dbf ONLINE
6 /sda8/rman/rman.dbf ONLINE
7 /opt/oracle/database/temp1/test1.dbf ONLINE
8 /opt/oracle/database/temp1/undo1.dbf ONLINE
9 /opt/oracle/database/temp1/test12.dbf ONLINE
9 rows selected.
或者通过v$datafile,v$tablespace,v$recover_file
3.
SQL> startup force
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 285216580 bytes
Database Buffers 310378496 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/opt/oracle/database/temp1/test1.dbf'
4.run {
allocate channel t1 type disk;
sql 'alter database datafile 7 offline';
restore datafile 7;
recover datafile 7;
sql 'alter database datafile 7 online';
sql 'alter database open';
}
D.对DBF文件的存储或磁盘坏了
思路:
先找出那个DBF文件以及对应的编号,再将DBF对应的表空间offline,然后改名到其它路径
2.
run{
allocate channel t1 type disk;
sql 'alter database datafile x offline';
sql 'alter database open';
set newname for datafile x to '/opt/oracle/oradate/test1.dbf';
restore tablespace test1;
switch datafile x;
recover tablespace test1;
sql 'alter tablespace test1 online';
}
E.全备之后,新建了tt.DBF文件,所有DBF被删除
run{
allocate channel t1 type disk;
restore datafile
sql 'alter database create datafile "/opt/oracle/oradata/tt.dbf" as "/opt/oracle/oradata/tt.dbf";
recover database;
sql 'alter database open';
}
F.全库恢复
1.startup nomount
2.restore controlfile from autobackup;
3.sql 'alter database mount';
4.restore database;
5.sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss''";
6.recover database until time '2009.11.05 15:23:00';(全备之后的时间)