Oracle利用RMAN做异地恢复实验-针对数据库量小的情况

雅冰石

已于 2024-04-08 15:50:30 修改

阅读量2.3k

点赞数

分类专栏： 1 ORACLE 学习 # ORACLE RMAN 文章标签： oracle 数据库 sql

于 2014-10-22 11:28:35 首次发布

本文链接：https://blog.csdn.net/yabingshi_tech/article/details/40373503

版权

1 ORACLE 学习同时被 2 个专栏收录

296 篇文章 4 订阅

订阅专栏

ORACLE RMAN

20 篇文章 0 订阅

订阅专栏

一：实验介绍

把一台机器上面的某个数据库恢复到另一台机器上（相当于，建个测试环境，以便进行相关操作）

二：实验环境

三：实验准备

在目标恢复机器上建好相关目录：

3.1 创建同源库相同的数据库全备所在目录

[oracle@rac2 dbs]$ mkdir -p /home/oracle/dandan/rmanbk

3.2 创建相同的控制文件备份目录

[oracle@rac2 flash_recovery_area]$ mkdir -p /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_10_21

3.3 建数据存放目录

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl/

3.4 建bdump,cdump等几个目录

[oracle@rac2 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/bdump

[oracle@rac2 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/cdump

[oracle@rac2 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/udump

[oracle@rac2 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump

四：实验步骤

4.1 测试数据

--查看源机器下scott.t表现有数据，以便于实验完成后验证是否恢复成功

SQL> conn scott/tiger;

Connected.

SQL> select *

  2  from t;

 

ID

----------

 1

 2

 3

 4

 5

 6

 7

 8

 9

10

11

 

ID

----------

12

13

 

13 rows selected.

4.2 源机器做下数据库全备

RMAN> backup database format '/home/oracle/dandan/rmanbk/%d_%s_%T.bak';

 

Starting backup at 21-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00009 name=/u01/app/oracle/oradata/orcl/ogg01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/test01.dbf

input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/undo_test01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-OCT-14

channel ORA_DISK_1: finished piece 1 at 21-OCT-14

piece handle=/home/oracle/dandan/rmanbk/ORCL_17_20141021.bak tag=TAG20141021T045035 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

Finished backup at 21-OCT-14

 

Starting Control File and SPFILE Autobackup at 21-OCT-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_10_21/o1_mf_s_861511861_b4d7mobl_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 21-OCT-14

4.3 拷贝相关文件

4.3.1 拷贝初始化参数文件

[oracle@source_pc dbs]$ cd $ORACLE_HOME/dbs

[oracle@source_pc dbs]$ ls -l

total 7300

-rw-rw---- 1 oracle oinstall 1544 Jun 28 03:17 hc_jiao.dat

-rw-r----- 1 oracle oinstall 1544 Jun 22 01:22 hc_orcl.dat

-rw-rw---- 1 oracle oinstall 1544 Jun 22 11:43 hc_ORCL.dat

-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora

-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora

-rw-r----- 1 oracle oinstall 2472 Jun 22 11:40 initORCL.ora

-rw-rw---- 1 oracle oinstall 24 Jun 28 03:18 lkJIAO

-rw-r----- 1 oracle oinstall 24 Jun 22 01:24 lkORCL

-rw-r----- 1 oracle oinstall 1536 Jun 28 03:20 orapwjiao

-rw-r----- 1 oracle oinstall 1536 Jun 22 01:30 orapworcl

-rw-r----- 1 oracle oinstall 7389184 Oct 21 03:03 snapcf_ORCL.f

-rw-r----- 1 oracle oinstall 3584 Jun 28 03:20 spfilejiao.ora

-rw-r----- 1 oracle oinstall 2560 Jun 22 02:54 spfileorcl.ora

-rw-r----- 1 oracle oinstall 3584 Oct 21 01:58 spfileORCL.ora

[oracle@source_pc dbs]$ scp initORCL.ora 192.168.8.221:$ORACLE_HOME/dbs/

The authenticity of host '192.168.8.221 (192.168.8.221)' can't be established.

RSA key fingerprint is 1c:b2:66:d8:fc:a9:29:45:73:dd:ca:92:ca:b8:0a:20.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.8.221' (RSA) to the list of known hosts.

oracle@192.168.8.221's password:

initORCL.ora 100% 2472 2.4KB/s 00:00

4.3.2 拷贝数据库全备文件

[oracle@source_pc dbs]$ scp /home/oracle/dandan/rmanbk/ORCL_17_20141021.bak 192.168.8.221:/home/oracle/dandan/rmanbk/

oracle@192.168.8.221's password:

ORCL_17_20141021.bak 100% 715MB 13.3MB/s 00:54

4.3.3 拷贝控制文件备份

[oracle@source_pc rmanbk]# scp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_10_21/o1_mf_s_861511861_b4d7mobl_.bkp 192.168.8.221:/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_10_21/

The authenticity of host '192.168.8.221 (192.168.8.221)' can't be established.

RSA key fingerprint is 1c:b2:66:d8:fc:a9:29:45:73:dd:ca:92:ca:b8:0a:20.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.8.221' (RSA) to the list of known hosts.

oracle@192.168.8.221's password:

o1_mf_s_861505385_b4d199pq_.bkp 100% 7296KB 7.1MB/s 00:01

4.4 恢复

4.4.1 启动到nomount状态

#确保目标端db_name和源端保持一致

如果不一致，后面恢复完控制文件，将数据库启动到mount状态时会报错：

ORA-01103: 控制文件中的数据库名 ''ORCL'' 不是 ''EVS''。

修改示例：

示例：

create pfile='/home/oracle/temp.ora' from spfile;

vi /home/oracle/temp.ora

将*.db_name='ORCL'改为*.db_name='evs'

shutdown immediate;

startup nomount pfile='/home/oracle/temp.ora';

create spfile from pfile='/home/oracle/temp.ora';

show parameter name; #检查确认

#启动库到nomount状态

startup nomount;

4.4.2 恢复控制文件

--在源库查询dbid

 

SQL> select dbid

  2  from v$database;

 

      DBID

----------

1378653027

 

[oracle@rac2 dbs]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 21 07:38:27 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database: orcl (not mounted)

 

RMAN> set dbid 1378653027;

 

executing command: SET DBID

 

--对于不同数据库来说，DBID应当不同，而db_name则可能是相同的。避免在同机的时候有数据库名相同的情况下，无法识别。

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_10_21/o1_mf_s_861511861_b4d7mobl_.bkp';

 

Starting restore at 21-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 21-OCT-14

4.4.3 启动数据库到mount状态

RMAN> alter database mount;

假如报错：

ORA-00201: 控制文件版本 12.2.0.0.0 与 ORACLE 版本 12.1.0.2.0 不兼容

ORA-00202: 控制文件: ''/data/app/oracle/oradata/orcl/control01.ctl'

则修改参数文件中compatible参数，将其和源端保持一致，示例：

alter system set compatible='12.2.0' scope=spfile;

重新将库启动到mount状态，使修改生效……。
*/

4.4.4 恢复数据库

catalog start with '/backup/202404080100';

report schema;

crosscheck backup;

delete expired backup;

用restore database命令还原数据库。

如果目标端数据文件路径和源端不一致，还需要指定下新路径，示例：

run {

allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
allocate channel t7 type disk;
allocate channel t8 type disk;

allocate channel t9 type disk;
allocate channel t10 type disk;

set newname for database to '/data/app/oracle/oradata/orcl/%U';

restore database;

switch datafile all;

switch tempfile all;

}

多开几个通道，可以加速restore。

4.4.5 以resetlogs方式打开库

4.4.5.1 检查redo log所在路径在目标环境是否存在

存在的话，可忽略这一步，继续往下进行。

不存在的话，将redo log指定到一个已存在(有该目录)的路径下，再启动数据库，否则启动数据库会报错：

ORA-00349: 无法获得 '/oracle/app/oracle/oradata/orcl/redo01.log' 的块大小

ORA-27041: 无法打开文件

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

Additional information: 9

假如起点库报了上面这个错，然后指定redo log到新位置了，再启动库，还会报这个错：

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: 位于 04/05/2024 10:58:27 的 sql statement 命令失败

ORA-00392: 日志 1 (用于线程 1) 正被清除, 不允许操作

ORA-00312: 联机日志 1 线程 1: '/data/app/oracle/oradata/orcl/redo01.log'

redo log会自动生成在新指定的redo log路径下。

示例：

select member from v$logfile;

目标端没有/oracle/app/oracle/oradata/orcl这个目录。

select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'原路径','新路径')||''';' from v$logfile;

执行输出来的sql，重命名下redo log。

示例：

alter database rename file '/oracle/app/oracle/oradata/orcl/redo03.log' to '/data/app/oracle/oradata/orcl/redo03.log';

alter database rename file '/oracle/app/oracle/oradata/orcl/redo02.log' to '/data/app/oracle/oradata/orcl/redo02.log';

alter database rename file '/oracle/app/oracle/oradata/orcl/redo01.log' to '/data/app/oracle/oradata/orcl/redo01.log';

alter database rename file '/oracle/app/oracle/oradata/orcl/redo04.log' to '/data/app/oracle/oradata/orcl/redo04.log';

alter database rename file '/oracle/app/oracle/oradata/orcl/redo05.log' to '/data/app/oracle/oradata/orcl/redo05.log';

4.4.5.2 启动数据库

alter database open resetlogs;

#在指定的路径下检查redo log是否生成。

cd /data/app/oracle/oradata/orcl

ls -l | grep redo

4.5 验证是否恢复成功

SQL> select *

  2  from scott.t;

 

ID

----------

 1

 2

 3

 4

 5

 6

 7

 8

 9

10

11

 

ID

----------

12

13

 

13 rows selected.

--和备库一样，都有13条数据，说明恢复成功了。

--记得在目标库上做个全备……

雅冰石

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Oracle利用RMAN做异地恢复实验-针对数据库量小的情况

一：实验介绍把一台机器上面的某个数据库恢复到另一台机器上（相当于，建个测试环境，以便进行相关操作）二：实验环境操作系统IP数据库环境数据库实例名称主机名称source端RedHat192.168.8.225单实例ORCLsourc
复制链接

扫一扫