首页 > 代码库 > Oracle 11G RMAN 单实例异机恢复

Oracle 11G RMAN 单实例异机恢复

  数据备份当然是为数据恢复准备,新环境的oracle一直在进行备份,但都没有测试验证备份的有效性,所以本次测试的重要性不言而喻了!以下为WIN平台下RMAN异机恢复实例。

 


源库

目标库

操作系统

WIN SVR 2008 R2

WIN SVR 2008 R2

主机名

Ora

ORATEST

IP

192.168.18.20

192.168.18.25

数据库版本

11.2.0.1.0

11.2.0.1.0

存储方式

单实例

单实例

ORACLE_HOME

D:\app\Administrator\product\11.2.0\dbhome_1

D:\app\Administrator\product\11.2.0\dbhome_1

ORACLE_SID

HWPROD

HWPROD

 

源库备份操作:

相关备份配置及脚本如下:

RMAN> show all;

 

db_unique_name HWPROD 的数据库的 RMAN 配置参数为:

CONFIGURE RETENTION POLICY TO RECOVERYWINDOW OF 3 DAYS;  #保留备份为3

CONFIGURE BACKUP OPTIMIZATION OFF; #default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; #default

CONFIGURE CONTROLFILE AUTOBACKUP ON;  #需要打开自动备份

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO ‘e:\data_backup\

ctl_%F.bak‘;  #指定备份控制文件及参数文件备份路径格式

CONFIGURE DEVICE TYPE DISK PARALLELISM 1BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICETYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FORDEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; #default

CONFIGURE ENCRYPTION FOR DATABASE OFF; #default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128‘; #default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC‘ ASOF RELEASE ‘DEFAULT‘ OPTIMIZE FOR LOA

D TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TONONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO‘D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHO

ME_1\DATABASE\SNCFHWPROD.ORA‘; # default

 

full_backup.sql

 

run{

allocate channel d1 type disk;  

allocate channel d2 type disk;

backup as compressed backupset fulldatabase format ‘e:\data_backup\full_%d_%s_%p_%u_%t.bak‘;

sql ‘alter system archive logcurrent‘;  

backup archivelog all format‘e:\data_backup\log_%d_%s_%p_%u_%t.bak‘ delete all input; 

release channel d1;

release channel d2;

report obsolete;  

crosscheck backup; 

delete noprompt expired backup; 

delete noprompt obsolete; 

}

 

full_backup.bat

 

set oracle_sid=hwprod

set d=%date:~,4%%date:~5,2%%date:~8,2%

echo=>e:\rman_script\log\full_backup_%d%.log

 

rman target /cmdfile=e:\rman_script\full_backup.sqlmsglog=e:\rman_script\log\full_backup_%d%.log

 

 

RMAN恢复思路步骤:

 

  • 初始化数据库,安装相同环境;

  • 恢复参数文件;

  • 恢复控制文件;

  • 启动数据库到MOUNT状态,利用控制文件进行数据恢复;

  • 查看归档日志备份sequence

  • Restore –recover—alter database open ressetlogs;

  • 验证;

  • 设置监听

 

 

1、  系统安装完成后,安装ORACLE软件,选择只安装软件,并安装与源库相同路径,然后把相关备份文件拷贝到原备份路径,为避免不必要的麻烦,建议设置与源库一致。

 

2、建立相关的密码文件,服务,监听器,否则无法连接DB

C:\Users\Administrator>D:

 

D:\>cd D:\app\Administrator\product\11.2.0\dbhome_1\BIN

 

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>orapwdfile=D:\app\Administrato

r\product\11.2.0\dbhome_1\database\pwdhwprod.orapassword=oracle entries=5;

 

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>oradim-new -sid HWPROD -startm

ode m

实例已创建。

 

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>setORACLE_SID=HWPROD

 

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>

技术分享 


D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus/ as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4 21 14:19:482017

 

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

 

已连接到空闲例程。

 

SQL>

技术分享

此时可以连接到DB了。

 

3、手动建立一个pfile文件,放到D:\app\Administrator\product\11.2.0\dbhome_1\database目录下,inithwprod.ora内容如下:

db_name=HWPROD

java_pool_size=4194304

large_pool_size=4194304

shared_pool_size=96468992

技术分享

以此pfile启动数据库到nomount状态:

技术分享

SQL> startuppfile=‘D:\app\Administrator\product\11.2.0\dbhome_1\database\inithw

prod.ora‘ nomount;

ORACLE 例程已经启动。

 

Total System Global Area  162873344 bytes

Fixed Size                  2173800 bytes

Variable Size             104858776 bytes

Database Buffers           50331648 bytes

Redo Buffers                5509120 bytes

 

4、切换到RMAN下,并SETDBID=3279461817(源库DBID),恢复spfile文件:

技术分享

技术分享


 

Restore SPfile文件,找到源spfile备份相关文件进行恢复:

RMAN> restore spfile from‘e:\data_backup\CTL_C-3279461817-20170427-00‘;

 

启动 restore 27-4 -17

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: SID=135 设备类型=DISK

 

通道 ORA_DISK_1: 正在从 AUTOBACKUP e:\data_backup\CTL_C-3279461817-20170427-00

还原 spfile

通道 ORA_DISK_1: AUTOBACKUP 还原 SPFILE 已完成

完成 restore 27-4 -17

技术分享

 

 

 

5SPfile恢复完成,关闭数据库,用刚恢复的spfile启动数据库到nomount状态(注意需要创建flash_recovery_areaORADATA目录,否则会报错ORA-01263: Name given for file destination directory is invalid  ,同时需注意修改还原过来的SPfile文件,这里应该注意,源库可能空间较大,关于sga,pga等酌情根据目标库修改),否则会报OSD-00026超出内存等错误,不能正常nomount

技术分享 


 

建立相关目录:

技术分享

再启动:

技术分享

 

6restore恢复controlfile

技术分享

RMAN> restore controlfile from‘E:\data_backup\CTL_C-3279461817-20170427-01‘;

 

接下来把数据库修改为MOUNT状态:

技术分享

 

7、查看归档日志备份的状态,其最大sequence18437

技术分享

RMAN> list backup of archivelog all;

 

8、进行数据文件恢复:

技术分享

RMAN> run {

2> set until sequence 18437;

3> restore database;

4> }

技术分享

RMAN> run {

2> set until sequence 18437;

3> recover database;

4> }

 

9resetlogs 打开数据库:

技术分享

RMAN> alter database open resetlogs;

 

10、查看数据库状态:

技术分享

 

11、添加注册表SID

技术分享

 

12、创建SPfile

SQL> create spfile from pfile;

 

13、重启数据库,是否以SPFILE启动:

SQL> select decode(count(*),1,‘spfile‘,‘pfile‘) from v$spparameter whererownum

=1 and isspecified = ‘TRUE‘;

技术分享

14、修改listener.ora,tnsnames.ora

技术分享

 

listener.ora

 

# listener.ora Network Configuration File:D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = HWPROD)

     (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)

     (SID_NAME = HWPROD)

    )

  )

 

LISTENER =

 (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = ORATEST)(PORT = 1521))

  )

 

ADR_BASE_LISTENER =D:\app\Administrator\product\11.2.0\dbhome_1\log

 

tnsnames.ora

 

# tnsnames.ora Network Configuration File:D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.

 

HWPROD =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.25)(PORT = 1521))

    )

   (CONNECT_DATA =http://www.mamicode.com/

     (SERVICE_NAME = HWPROD)

    )

  )

 

查看监听状态:

技术分享

 

验证数据,OK!


参考:http://blog.itpub.net/29119536/viewspace-1171894/


本文出自 “MG05 學習筆記~” 博客,请务必保留此出处http://pimg2005.blog.51cto.com/842469/1920337

Oracle 11G RMAN 单实例异机恢复