(转) Oracle 11G RMAN 单实例异机恢复

https://blog.csdn.net/u013115078/article/details/86150694
https://blog.51cto.com/pimg2005/1920337
Oracle 11G RMAN 单实例异机恢复
pimg2005关注0人评论14495人阅读2017-04-28 10:48:15
数据备份当然是为数据恢复准备，新环境的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>orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\pwdhwprod.ora password=oracle entries=5;

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>oradim -new -sid HWPROD -startmode m
实例已创建。

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

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

wKioL1kCqc7QOc1BAABHhlprgvs295.png-wh_50

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 © 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL>

wKioL1kCqhnStZLCAAAw976cjSY886.png-wh_50

此时可以连接到DB了。

3、手动建立一个pfile文件（目的是使用数据库启动到nomount状态），放到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

wKiom1kCqlPzsnnhAAAochqfGro724.png-wh_50

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

wKioL1kCqqWCHAsNAABGZ5PeOjE703.png-wh_50

SQL> startuppfile=‘D:\app\Administrator\product\11.2.0\dbhome_1\database\inithwprod.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）：

wKiom1kCqteRcIgHAABW61dRT94467.png-wh_50

wKioL1kCqtfDWV4hAABUz4YCS2Y719.png-wh_50

5、创建flash_recovery_area、ORADATA目录，否则恢复数据时会报错ORA-01263: Name given for file destination directory is invalid 。

建立相关目录：

wKioL1kCq0iDxfTIAACM8_VbfkI826.png-wh_50

6、恢复spfile文件：

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

RMAN> restore spfile from’e:\data_backup\CTL_C-3279461817-20180116-00.BAK’;

恢复参数文件完成后，shutdown 数据库，退出RMAN，找到恢复的参数文件把相关参数copy并修改到inithwprod.ora文件里(这里应该注意，源库可能空间较大，如果设置不当，启动数据库到nomount时会报ORA-27102 out of memory 等错误，所以关于sga,pga等酌情根据目标库修改)

以下为修改后的inithwprod.ora文件内容：

hwprod.__java_pool_size=33554432
hwprod.__large_pool_size=33554432
hwprod.__oracle_base=‘d:\app\Administrator’#ORACLE_BASE set from environment
hwprod.__pga_aggregate_target=536870912
hwprod.__sga_target=536870912
hwprod.__shared_io_pool_size=0
hwprod.__shared_pool_size=536870912
hwprod._streams_pool_size=16777216
*.audit_file_dest=‘d:\app\Administrator\admin\hwprod\adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=‘d:\app\Administrator\oradata\hwprod\control01.ctl’,‘d:\app\Administrator\flash_recovery_area\hwprod\control02.ctl’
*.db_block_size=8192
*.db_domain=‘hwprod_test’
*.db_name=‘hwprod’
*.db_recovery_file_dest=‘d:\app\Administrator\flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=‘d:\app\Administrator’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=hwprodXDB)’
*.log_archive_format='ARC%S%R.%T’
*.memory_target=1073741824
*.nls_language=‘SIMPLIFIED CHINESE’
*.nls_territory=‘CHINA’
*.open_cursors=100
*.processes=500
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’
修改完成后再进入RMAN，并把数据库startup nomount 状态

SQL> startup nomount pfile=‘D:\app\Administrator\product\11.2.0\dbhome_1\database\inithwprod.ora’ ;

7、此时可以进行恢复控制文件，找到源控制文件备份相关文件进行恢复：

RMAN> restore controlfile from’E:\data_backup\CTL_C-3279461817-20170427-01’;
控制文件恢复完成后，接下来把数据库修改为MOUNT状态：

8、查看归档日志备份的状态，其最大sequence为18437

wKioL1kCq9iD2_ZfAAByIFANEqg849.png-wh_50

RMAN> list backup of archivelog all;

9、进行数据文件恢复：

wKiom1kCq_-yIw2SAABhdEBsLc4501.png-wh_50

RMAN> run {
2> set until sequence 18437;
3> restore database;
4> }
wKiom1kCrCvjWLAZAABkEfhAIx0638.png-wh_50

RMAN> run {
2> set until sequence 18437;
3> recover database;
4> }

10、resetlogs 打开数据库：

wKioL1kCrEvg7uDAAABilW97oUs894.png-wh_50

RMAN> alter database open resetlogs;

11、查看数据库状态：

wKioL1kCrG6A8jsfAAAn1Dw1hOQ985.png-wh_50

12、添加注册表SID：

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1
“ORACLE_SID”=“HWPROD”
wKioL1kCrInyb453AACszi3b0lU105.png-wh_50

13、创建SPfile参数文件

SQL> create spfile from pfile;
可以看到相关路径已经创建新的参数文件：

无标题.png

14、重启数据库，是否以SPFILE启动：

SQL> select decode(count(*),1,‘spfile’,‘pfile’) from v$spparameter where rownum=1 and isspecified = ‘TRUE’;
wKioL1kCrKmTaOwvAABBARpmX7g209.png-wh_50

15、修改listener.ora,tnsnames.ora

wKiom1kCrMGRhv7SAABW38TJ9Lo572.png-wh_50

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 =
(SERVICE_NAME = HWPROD)
)
)

查看监听状态：

wKioL1kCrOLyBvRrAABjwHoBGzU386.png-wh_50

验证数据，OK！

2018-01-16更新

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