RMAN备份恢复小练习

最新推荐文章于 2025-05-27 14:14:42 发布

Felix_Hu_

最新推荐文章于 2025-05-27 14:14:42 发布

阅读量449

点赞数

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/qq_23967911/article/details/52090993

版权

Oracle 专栏收录该内容

5 篇文章

订阅专栏

如果一个文件被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';

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';

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