mysql中的rman备份与恢复_Rman备份恢复命令 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788...

一、backup操作

备机集

1.设置备份标记

backup database tag=’full_bak1′;

注：每个标记必须唯一，相同的标记可以用于多个备份只还原最新的备份。

2.设置备份集大小(一次备份的所有结果为一个备份集，要注意备份集大小)

backup database maxsetsize=100m tag=’datafile1′;

注：maxsetsize限定备份集的大小。所以必须大于数据库总数据文件的大小，否则会报错。

RMAN-06183: datafile or datafile copy larger than MAXSETSIZE: file# 1 /data/oradata/system01.dbf

3.设置备份片大小(磁带或文件系统限制)

run {

allocate channel c1 type disk maxpicecsize 100m format ‘/data/backup/full_0_%U_%T’;

backup database tag=’full_0′;

release channel c1;

}

可以在allocate子句中设定每个备份片的大小，以达到磁带或系统限制。

也可以在configure中设置备份片大小。

Configure channel device type disk maxpiecesize 100 m;

configure channel device type disk clear;

4.备份集的保存策略

backup database keep forever; –永久保留备份文件

backup database keep until time=’sysdate+30′; –保存备份30天

5.重写configure exclude命令

backup databas noexclude keep forever tag=’test backup’;

6.检查数据库错误

backup validate database;

使用RMAN来扫描数据库的物理/逻辑错误，并不执行实际备份。

7.跳过脱机，不可存取或只读文件

backup database skip readonly;

backup database skip offline;

backup database skip inaccessible;

backup database ship readonly skip offline ship inaccessible;

8.强制备份

backup database force;

9.基于上次备份时间备份数据文件

1>只备份添加的新数据文件

backup database not backed up;

2>备份”在限定时间周期内”没有被备份的数据文件

backup database not backed up since time=’sysdate-2′;

10.备份操作期间检查逻辑错误

backup check logical database;

backup validate check logical database;

11.生成备份副本

backup database copies=2;

12.备份控制文件

backup database device type disk includ current controlfile;

建立映像副本

1、建立数据文件映像副本

RMAN>backup as copy format=’c:\df_5.dbf’ datafile 5; 或者

RMAN>copy datafile 5 to ‘c:\df_5.dbf’ ;

2、建立控制文件映像副本

RMAN>backup as copy format=’c:\demo.ctl’ current controlfile; 或者

RMAN>copy current controlfile to ‘c:\demo.ctl’;

3、建立归档日志映像副本

RMAN>backup as copy format=’c:\arc10.log’ archivelog sequence 10; 或者

RMAN>copy archivelog ‘c:\demo\archive\arc00020_012345.001′ to ‘c:\arc20.log’;

4、并行化建立映像副本

RMAN>configure device type disk parallelism 3;

RMAN>backup as copy format =’c:\df_%f.dbf’ datafile 5,6,7 ;

RMAN>configure device type disk clear;

5、指定映像副本标记

RMAN>backup as copy format=’c:\df_%f.dbf’ datafile 5 tag=df_5; 或者

RMAN>copy datafile 5 to ‘c:\df_%f.dbf’ tag=df_5;

二、RMAN恢复操作

RMAN完全恢复

1、恢复数据库

1)、所有数据文件被删除

c:\rman target sys/oracel@test nocatalog

RMAN>startup force mount

RMAN> run {

restore databse ;

recover database;

sql ‘alter database open’ ;

}

2)、数据文件所在磁盘出现硬件故障

RMAN> run {

startup force mount;

set newname for datafile 1 to ‘c:\demo\system01.dbf’;

set newname for datafile 2 to ‘c:\demo\dundotbs01.dbf’;

set newname for datafile 3 to ‘c:\demo\sysaux01.dbf’;

set newname for datafile 4 to ‘c:\demo\users01.dbf’;

set newname for datafile 5 to ‘c:\demo\example01.dbf’;

set newname for datafile 6 to ‘c:\demo\test0.dbf’;

restore database;

switch datafile all;

recover database;

sql ‘alter database open ‘;

}

2、恢复SYSTEM表空间的数据文件

1)、SYSTEM表空间的数据文件被误删除

RMAN>run {

startup force mount;

restore datafile 1;

recover datafile 1;

sql ‘alter database open’ ;

}

2)、SYSTEM表空间数据文件所在的磁盘出现故障

RMAN> run {

startup force mount;

set newname for datafile 1 to ‘c:\demo\system01.dbf’;

restore datafile 1;

switch datafile 1;

recover datafile 1;

sql ‘alter database open’;

}

3、在OPEN状态下恢复关闭后意外丢失数据文件

1)、数据文件被删除

RMAN>run {

startup force mount;

sql ‘alter database datafile 4 offline’;

sql ‘alter database open ‘;

restore datafile 4;

recover datafile 4;

sql ‘alter database datafile 4 online’;

}

2)、数据文件所在的磁盘出现损坏

RMAN>run {

startup force mount;

sql ‘alter database datafile 4 offline’;

sql ‘alter database open ‘;

set newname for datafile 4 to ‘c:\demo\user01.dbf’;

restore datafile 4;

switch datafile 4;

recover datafile 4;

sql ‘alter database datafile 4 online’;

}

4、在OPEN状态下恢复打开时意外丢失的数据文件

1)、数据文件被误删除

RMAN>run {

sql ‘alter database datafile 4 offline’;

restore datafile 4;

recover datafile 4;

sql ‘alter database datafile 4 online’;

}

2)、数据文件所在磁盘出现故障

RMAN>run {

sql ‘alter database datafile 4 offline’;

set newname for datafile 4 to ‘c:\demo\user01.dbf’;

restore datafile 4;

switch datafile 4 ;

recover datafile 4;

sql ‘alter database datafile 4 online’;

}

5、在OPEN状态下恢复未备份的数据文件(创造新数据文件后没有进行过备份)

1)、数据文件被误

RMAN>run {

startup force mount;

sql ‘alter database datafile 7 offline ‘;

sql ‘alter database open’;

restore datafile 7;

recover datafile 7;

sql ‘alter database datafile 7 online’;

}

2)、数据文件所在磁盘出现故障

RMAN>run {

startup force mount;

sql ‘alter database datafile 7 offline ‘;

sql ‘alter database open’;

set newname for datafile 7 to ‘c:\demo\user04.dbf’;

restore datafile 7;

switch datafile 7;

recover datafile 7;

sql ‘alter database datafile 7 online’;

}

6、恢复表空间

1)、表空间被删除

RMAN>run {

sql ‘alter tablespace users offline for recover’;

restore tablespace users;

recover tablespace users;

sql ‘alter tablespace users online’;

}

2)、表空间的数据文件所在磁盘出现故障

RMAN>run {

sql ‘alter tablespace users offline for recover’;

set newname for datafile 4 to ‘c:\demo\user01.dbf’;

restore tablespace users;

switch tablespace users;

recover tablespace users;

sql ‘alter tablespace users online’;

}

7、数据块介质恢复

RMAN>blockrecover device type disk datafile 5 block 20,21,100 ;

RMAN不完全恢复

1、基本时间恢复

c:\set nls_date_format=yyyy-dd-mm hh24:mi:ss

c:\rman target sys/dddddd@train nocatalog

RMAN>run{

startup force mount;

set until time=’2010-09-09 13:00:00′;

restore database;

recover database;

sql ‘alter database open resetlogs’;

}

2、基于SCN恢复

RMAN>run {

startup force mount;

set until scn=123456;

restore database;

recover database;

sql ‘alter database open resetlogs’;

}

3、基于日志序列号恢复

RMAN>run {

startup force mount;

set until sequence=58;

restore database;

recover database;

sql ‘alter database open resetlogs’;

}

4、基于备份控制文件恢复

c:\set nls_date_format=yyyy-dd-mm hh24:mi:ss

c:\rman target sys/oracle@test nocatalog

RMAN>startup force nomount;

RMAN>set dbid=1113606269;

RMAN>restore controlfile from autobackup maxseq 6;

RMAN>alter database mount;

RMAN>run {

set until time=’2010-09-5 12:00:08′;

restore database;

recover database;

sql ‘alter database open resetlogs;

}

当执行了上述4种不完全恢复之后，建议删除早期的所有备份，并重新备份数据库

RMAN>run {

delete noprompt backup;

delete noprompt copy;

backup database format=’c:\backup\%d_%s.bak’;

sql ‘alter system archive log current’;

}