RMAN备份恢复小练习

 

如果一个文件被offline了，手工备份会失败，但是rman能够成功。

如果其归档都在则需要立即回复，如果归档都不在了，只能删掉这个文件。

 

 

 

1.开发商可能会将数据文件错误地创建到了本地而非存储上，此时如果本机挂掉，应用切换到了另外一台主机上面，那么这个数据文件将不可用。我们需要及时发现问题，并将数据文件迁移到它应该在的位置。

 

create tablespace testtbs02 datafile 'c:\datafile\test02.dbf' size 10M autoextend on;

 

查看：

SQL> select name from v$datafile;

select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/orcl/test01.dbf

/u01/app/oracle/product/11.2.0/db_1/dbs/c:datafiletest02.dbf

 

 

下面需要将其迁移到正确的位置：

 

1.先进入rman。然后进行copy命令：

 

copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/c:datafiletest02.dbf' to '/u01/app/oracle/oradata/orcl/test02.dbf';

 

或者直接在操作系统层面cp 数据文件到正确位置（注意裸设备只能用rman）

 

2.offline 当前数据文件

#alter database datafile 。。。 offline;

 

alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/c:datafiletest02.dbf' offline;

 

3.更新控制文件

 

#alter database rename file '' to ''

 

alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/c:datafiletest02.dbf' to '/u01/app/oracle/oradata/orcl/test02.dbf';   

 

 

4.恢复数据文件recover

 

#recover datafile 7;

 

recover datafile '/u01/app/oracle/oradata/orcl/test02.dbf';

 

 

5.online

 

#alter database datafile 7 online;

 

alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;

 

 

 

【rman 工具只能放在自己能够直接识别的包袱空间入/backup 或者磁带库。不能放在Windows上！】

 

 

Rman 备份恢复

 

全备模块：

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format '/backup/db_%d_%T_%s_%p';

}

 

 

 

压缩备份：

 

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup as compressed backupset database filesperset 1 format '/backup/full/comprss_%d_%T_%s_%p';

}

 

 

 

全备脚本：

 

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

rman target / nocatalog log /backup/full.log append <<EOF

run {

allocate channel c1 type disk;

allocate channel c2 type disk;

backup filesperset 2 database format '/backup/full/full_%d_%T_%s_%p';

sql 'alter system archive log current';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup archivelog all format '/backup/arch/arch_%d_%T_%s_%p';

backup current controlfile format '/backup/ctl/ctl_%d_%T_%s_%p';

}

EOF

 

 

 

归档备份脚本：

 

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

rman target / nocatalog log /backup/arch.log append <<EOF

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup archivelog all format '/backup/arch/arch_%d_%T_%s_%p';

backup current controlfile format '/backup/ctl/ctl_%d_%T_%s_%p';

}

EOF

 

 

 

设置计划任务：

 

$ crontab -l

2 * * * * /home/oracle/scripts/full.sh

2 * * * * /home/oracle/scripts/arch.sh

 

 

RMAN备份恢复实操：

 

1.控制文件丢失rman恢复之后，需要recover database,然后再 resetlogs 启动数据库。

 

2.系统数据文件丢失(system/sysaux/undotbs/users/test/)，restore datafile 1,recover datafile 1,open...

 

3.在线修复用户数据文件：

alter database datafile 6 offline;

run{

restore datafile 6;

recover datafile 6;

}

 

alter database datafile 6 online;

 

4.临时文件损坏直接重建即可：

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp.dbf' size 50M;

 

5.日志文件损坏

--redo 非current 丢失：

 

只能启动到mount：

SQL> startup

startup

ORACLE instance started.

 

Total System Global Area  839282688 bytes

Fixed Size                  2257880 bytes

Variable Size             545262632 bytes

Database Buffers          289406976 bytes

Redo Buffers                2355200 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 16722

Session ID: 1 Serial number: 5

 

 

清除丢失的日志文件即可：

SQL> alter database clear logfile group 1;

若是没有归档：

SQL> alter database clear unarchived logfile group 1;

 

SQL> alter database open;

 

 

 

【猜想，alter system switch logfile ;切完变为active。日志组轮一圈之后，都会变为inactive。】

 

--redo current 丢失：

1）正常关机：

 

SQL> startup

。。。

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 17141

Session ID: 1 Serial number: 5

 

清除日志文件：

SQL> alter database clear logfile group 2;

 

ERROR at line 1:

ORA-00350: log 2 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

 

日志文件还没归档：

SQL> alter database clear unarchived logfile group 2;

 

SQL> alter database open;

 

 

 

2）非正常关机：

SQL> startup force

。。。

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

此时清除日志文件无效，报错需要实例恢复：

SQL> alter database clear logfile '/u01/app/oracle/oradata/orcl/redo03.log';

alter database clear logfile '/u01/app/oracle/oradata/orcl/redo03.log';

alter database clear logfile '/u01/app/oracle/oradata/orcl/redo03.log'

*

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance orcl (thread 1)

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

 

进行不完全恢复：

RMAN> restore database;

 

RMAN> recover database until scn 1245734;

或者

SQL> recover database until cancel;

 

SQL> alter database open resetlogs;

 

 

6.误删除表，闪回

 

SQL> drop table test1;                            

drop table test1;

 

Table dropped.

 

SQL> show recyclebin;                     

show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

TEST1            BIN$N2vvsSEwRbvgUwoUqMDN6w==$0 TABLE        2016-07-12:15:44:15

SQL> flashback table test1 to before drop;

flashback table test1 to before drop;

 

Flashback complete.

 

SQL> select count(*) from test1;

select count(*) from test1;

 

  COUNT(*)

----------

       277