利用 RMAN 进行异机全恢复

服务器资料信息

servername	role	sid	db_unique_name	db_name	lsn name	ip addr
node222	primary	TBDB	node222	node222	node222	192.168.17.222
node173	standby	TBDB	node173	node222	node173	192.168.17.173

node222, node173 数据文件，日志文件，归档日志存放位置一致

为实现物理standby

物理standby与primary数据库完全一模一样

同一镜像数据库，则 sid, db_name
必须一致

为标示不同数据库， db_unique_name
必须不一样

为连接不同数据库，连接器命令也必须不一样

目标

在 primary
中导入测试数据，创建 rman 进行全备，复制备份片至standby
然后导入 rman

创建数据表空间 (node173,node222)

create tablespace RECHARGE datafile '/u01/app/oracle/oradata/node222/recharge.dbf' size 500M extent management local;

创建应用连接的用户(node173,node222)

SQL> grant connect, resource to RECHARGE identified by "RECHARGE";

SQL> alter user RECHARGE default tablespace RECHARGE;

exp数据导入

注意, 数据导入过程中需要操作系统语言,
数据库语言, 客户端语言同步

查询并修改数据库语言环境

查询方法

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.AL32UTF8

修改方法

SQL> SHUTDOWN immediate;

SQL> STARTUP RESTRICT

SQL> alter database character set internal_use AL32UTF8 ;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

SQL> shutdown immediate;

SQL> startup

确保 oracle
语言环境一致后

修改系统语言环境并导入数据 (node222)

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

imp userid=system/oracle touser=RECHARGE file=day_new.dmp

创建RMAN(node173,node222)

SQL> create tablespace rman

logging datafile '/u01/app/oracle/oradata/node222/rman.dbf' size 1024M

extent management local segment space management auto;

创建 RMAN
授权(node173,node222)

create user rman identified by rman default tablespace rman;

grant connect,resource to rman;

grant recovery_catalog_owner to rman;

grant unlimited tablespace to rman;

grant sysdba to rman;

建立catalog数据库及注册数据库(node173,node222)

rman catalog rman/rman

RMAN> create catalog tablespace "RMAN";

RMAN> exit

连接 catalog(node173,node222)

rman target sys/oracle@node222 catalog rman/rman@node222

注:
需启动连接器，node222为客户端连接服务器的标识符

返回下面信息为正常连接到 RMAN

connected to target database: NODE222 (DBID=3694045568)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

创建备份目录

mkdir /u01/app/oracle/backup

cd /u01/app/oracle/backup

mkdir controlfileback dbback log logback

controlfileback 存放备份的控制文件

dbback 存放备份的数据文件

log 存放备份生成的日志，坚持查询该日志查看备份是否成功

logback 存放归档日志文件

备份数据(node222)

rman target sys/oracle@node222 catalog rman/rman@node222

注: @node222
是 listener 监听器连接（配置略）

显示一下全局配置信息

show all;

修改自动备份 control
功能

默认状态

CONFIGURE BACKUP OPTIMIZATION OFF; #
默认状态

CONFIGURE SNAPSHOT CONTROLFILE NAME TO

'/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_TBDB.f'; #
默认保存位置

修改

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO

'/u01/app/oracle/product/11.2.0/db1/dbs/cf%F';

修改备份数据最长时间

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;

如果需要删除过时备份

DELETE OBSOLETE

全库备份

RMAN> sql 'alter system archive log current';

RMAN> backup database format '/u01/app/oracle/backup/dbback/TBDB_%d_%T_%U';

备份过程信息如下

Starting backup at 28-JUN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=198 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/node222/rman.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/node222/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/node222/sysaux01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/node222/recharge.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/node222/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/node222/example01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/node222/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-JUN-13

channel ORA_DISK_1: finished piece 1 at 28-JUN-13

piece handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130628_01odb0sc_1_1 tag=TAG20130628T150651 comment=NONE

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

Finished backup at 28-JUN-13

Starting Control File and SPFILE Autobackup at 28-JUN-13

piece handle=/u01/app/oracle/product/11.2.0/db1/dbs/cfc-3695602788-20130628-00 comment=NONE

Finished Control File and SPFILE Autobackup at 28-JUN-13

归档备份方法

sql 'alter system archive log current';

backup archivelog all format '/u01/app/oracle/backup/logback/arc_%t_%s' delete all input;

控制文件手动备份方法

backup current controlfile format '/u01/app/oracle/backup/controlfileback/cntrl_%s_%p_%s';

查询备份信息

RMAN> list backup;

备份信息显示

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

159 Full 1.56G DISK 00:01:02 27-JUN-13

BP Key: 162 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121557

Piece Name: /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1

List of Datafiles in backup set 159

File LV Type Ckp SCN Ckp Time Name

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

1 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/system01.dbf

2 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/sysaux01.dbf

3 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/undotbs01.dbf

4 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/users01.dbf

5 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/example01.dbf

6 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/recharge.dbf

7 Full 3122357 27-JUN-13 /u01/app/oracle/oradata/node222/rman.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

160 Full 9.36M DISK 00:00:02 27-JUN-13

BP Key: 163 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121557

Piece Name: /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_02od82hv_1_1

SPFILE Included: Modification time: 27-JUN-13

SPFILE db_unique_name: NODE222

Control File Included: Ckp SCN: 3122444 Ckp time: 27-JUN-13

BS Key Size Device Type Elapsed Time Completion Time

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

191 746.56M DISK 00:00:26 27-JUN-13

BP Key: 194 Status: AVAILABLE Compressed: NO Tag: TAG20130627T121920

Piece Name: /u01/app/oracle/backup/logback/arc_819202760_3

List of Archived Logs in backup set 191

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 85 2901879 25-JUN-13 2936275 25-JUN-13

1 86 2936275 25-JUN-13 2952511 25-JUN-13

1 87 2952511 25-JUN-13 2974058 26-JUN-13

1 88 2974058 26-JUN-13 3000002 26-JUN-13

1 89 3000002 26-JUN-13 3018605 26-JUN-13

1 90 3018605 26-JUN-13 3046423 26-JUN-13

1 91 3046423 26-JUN-13 3056627 27-JUN-13

1 92 3056627 27-JUN-13 3080492 27-JUN-13

1 93 3080492 27-JUN-13 3094323 27-JUN-13

1 94 3094323 27-JUN-13 3098061 27-JUN-13

1 95 3098061 27-JUN-13 3098704 27-JUN-13

1 96 3098704 27-JUN-13 3099248 27-JUN-13

1 97 3099248 27-JUN-13 3099871 27-JUN-13

1 98 3099871 27-JUN-13 3100505 27-JUN-13

1 99 3100505 27-JUN-13 3101436 27-JUN-13

1 100 3101436 27-JUN-13 3101973 27-JUN-13

1 101 3101973 27-JUN-13 3107360 27-JUN-13

1 102 3107360 27-JUN-13 3120392 27-JUN-13

1 103 3120392 27-JUN-13 3122322 27-JUN-13

1 104 3122322 27-JUN-13 3122845 27-JUN-13

1 105 3122845 27-JUN-13 3122892 27-JUN-13

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

221 Full 9.33M DISK 00:00:02 27-JUN-13

BP Key: 243 Status: AVAILABLE Compressed: NO
Tag: TAG20130627T121956

Piece Name: /u01/app/oracle/backup/controlfileback/cntrl_4_1_4

Control File Included: Ckp SCN: 3123051 Ckp time: 27-JUN-1

把 node222 RMAN
备份数据复制至 node173 然后做全库恢复

按照默认位置，把文件存放 /u01/app/oracle/backup/dbback
对应目录下

logback/arc_819202760_3 (archive log backup)

controlfileback/cntrl_4_1_4 (control file backup)

dbback/TBDB_NODE222_20130627_01od82ft_1_1 (datafile backup)

dbback/TBDB_NODE222_20130627_02od82hv_1_1 (datafile backup)

执行 node173
恢复(下面所有操作在 node173
执行)

恢复控制文件，必须以 nomount
启动

shutdown immediate;

startup nomount;

注: node222, node173当前 db_name,sid,
数据存放位置一致，否则需要重写 pfile(略)

恢复控制文件过程

rman target /

RMAN> restore controlfile from '/u01/app/oracle/backup/controlfileback/cntrl_4_1_4';

恢复过程

channel ORA_DISK_1: restoring control file

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

output file name=/u01/app/oracle/oradata/node222/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/node222/control02.ctl

Finished restore at 28-JUN-13

恢复数据，切换数据库至 mount
状态

SQL> alter database mount;

直接恢复数据库则可

RMAN> restore database;

恢复过程

Starting restore at 28-JUN-13

Starting implicit crosscheck backup at 28-JUN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 28-JUN-13

Starting implicit crosscheck copy at 28-JUN-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 28-JUN-13

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/node222/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/node222/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/node222/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/node222/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/node222/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/node222/recharge.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/node222/rman.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1

channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1 tag=TAG20130627T121557

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 28-JUN-13

修复数据库 (主要利用 archive log
进行修复)

RMAN> restore database;

修复过程

Starting restore at 28-JUN-13

using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/oradata/node222/system01.dbf

skipping datafile 2; already restored to file /u01/app/oracle/oradata/node222/sysaux01.dbf

skipping datafile 3; already restored to file /u01/app/oracle/oradata/node222/undotbs01.dbf

skipping datafile 4; already restored to file /u01/app/oracle/oradata/node222/users01.dbf

skipping datafile 5; already restored to file /u01/app/oracle/oradata/node222/example01.dbf

skipping datafile 6; already restored to file /u01/app/oracle/oradata/node222/recharge.dbf

skipping datafile 7; already restored to file /u01/app/oracle/oradata/node222/rman.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 28-JUN-13

修复完成后启动数据库

SQL> alter database open resetlogs;

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/node222/system01.dbf'

检测系统 SCN

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

3122322

检测数据文件 SCN

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

3122357

7 rows selected.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

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

3122357

7 rows selected.

结论: 系统 SCN <
数据文件 SCN 导致报错

解决方法, 重新恢复一下控制文件

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 3122357 generated at 06/27/2013 12:15:57 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/NODE173/archivelog/2013_06_28/o1_mf_1_104_%u

_.arc

ORA-00280: change 3122357 for thread 1 is in sequence #104

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

重新启动数据库

SQL> alter database open resetlogs;

Database altered.

检测 node173, node222
数据，同步成功, 建议对服务器进行全备.

时间： 2024-10-26 09:23:35

利用 RMAN 进行异机全恢复的相关文章

使用RMAN实现异机备份恢复(WIN平台)

--=================================-- 使用RMAN实现异机备份恢复(WIN平台)--================================= 在有些情况下,如数据库服务器处于磁盘空间的压力或成本控制需要将数据文件备份到异机,使用RMAN可以完成该工作.基于Windows平台所需完成的配置相对简单,仅仅是添加账户与使用新增的帐户来启动数据库服务以及设置共享路径. 下面给出具体描述. 一.配置数据库服务器与备份目的主机 1.

如何通过RMAN备份duplicate异机克隆恢复数据库

说明: 本来是想做no target目标库通过目标库备份集创建dataguard测试的,发现从原理上不能实现,但它有一个非常有用的功能,可以实现快速的rman备份异机恢复. 之前通过RMAN备份异机恢复要操作很多步骤,通过rman duplicate方式来实现数据库将非常快速.将为今后有整库迁移工作任务提供又一种方案. 本文将记录完整过程. 一.环境介绍 1. 主数据库环境操作系统版本 : OEL5.8 x64 数据库版本 : Oracle 11.2.0.3 x64 数据库名

Oracle 10g RAC RMAN备份异机单实例恢复

本文通过将Oracle 10g RAC RMAN的完整的备份进行异机恢复的过程,可以对在恢复的过程中可以发现备份时的一些问题.比如规档日志的冗余,控制文件与参数文件的自动备份的利用等,本示例是拿了rman的备份集进行备份的,所以在最后的启动数据库的过程中出现了问题,提示控制文件过旧等问题,所以备份需要经过详细高可用的设计,才能在恢复过程中降低风险. Oracle 11g R2 RAC on OEL5.8 x64安装笔记 http://koumm.blog.51cto.com/703525/128

MS-SQL异机备份恢复并最小化停机时间

本文采用备份加增量日志的恢复方法,恢复源库到异机,增量日志恢复保证停机切换时间最小. 一. SQL Server数据库有三种恢复模式:简单恢复模式.完整恢复模式和大容量日志恢复模式: 1.Simple 简单恢复模式, Simple模式的旧称叫"Checkpoint with truncate log",其实这个名字更形象,在Simple模式下,SQL Server会在每次checkpoint或backup之后自动截断log,也就是丢弃所有的inactive log records,

如何使用nbu异机备份恢复oracle

1.1 Nbu备份oracle NBU备份oracle步骤 1.在oracle数据库所在服务器上安装NBU客户端. 2.修改oracle的备份数据库脚本 9.1.1修改备份脚本安装好客户端后生成的默认脚本路径: 备份脚本在nbu的安装目录里,oracle数据一般默认位置为:C:\Program Files\Veritas\NetBackup\DbExt\Oracle\Samples\rman 将脚本拷贝到c:\scripts目录下并重命名为hot_database_backup,修改一下几项

RMAN冷备份异机还原

1:环境准备在新的服务器上安装ORACLE实例,安装过程中需要注意源服务器与目标服务器的ORACLE_SID一致,另外确保安装路径与源路径一致(不仅是安装目录,甚至包括数据文件.控制文件目录.联机重做日志文件所在目录都要注意).如果不一致相当麻烦,需要修改spfile. 2:RMAN做冷备份使用cold_backup.sh将数据库备份到/u04/migration目录下面 mkdir -p /u04/migration mkdir -p /u04/migration/log cat

基于RMAN从活动数据库异机克隆(rman duplicate from active DB)

Oracle 11g RMAN能够实现基于活动数据库进行异机克隆,从而省去需要先备份再ftp到辅助服务器的过程.这一切可以全部交给Oracle来搞定.在克隆期间,Oracle会读取Target DB的所有数据结构,无须人为的干预(不同的磁盘系统,文件路径与文件名要指定转换方式).尽管如此,对于生产环境数据库在整个克隆期间性能会下降,其次在Target Server与Auxiliary Server中会产生较大的网络流量.如果这些可以接受的话,从活动数据库进行异机克隆是一个不错的选择.

基于RMAN实现坏块介质恢复(blockrecover)

对于物理损坏的数据块,我们可以通过RMAN块介质恢复(BLOCK MEDIA RECOVERY)功能来完成受损块的恢复,而不需要恢复整个数据库或所有文件来修复这些少量受损的数据块.恢复整个数据库或数据文件那不是大炮用来打蚊子,有点不值得!但前提条件是你得有一个可用的RMAN备份存在,因此,无论何时备份就是一切.本文演示了产生坏块即使用RMAN实现坏块恢复的全过程. 1.创建演示环境 SQL> select * from v$version where rownum<2; BAN

使用RMAN迁移数据库到异机

迁移数据库的方法有多种,较为常用的则是使用RMAN来迁移.使用RMAN迁移数据库属于数据库的物理备份与恢复范畴,整个过程中数据库的相关信息是完整地镜像.因此,基于此种方式还原恢复的数据库用于测试会使得与真实的生产环境差异相对较小.本文描述了使用RMAN来还原Oracle 10g数据库的过程. 一.主要步骤 1.备份数据库 2.ftp备份到目的服务器 3.为目标数据库创建项目目录 4.为目标数据库创建pfile或spfile(使用RMAN还原或复