Oracle11GRMAN单实例异机恢复
数据备份当然是为数据恢复准备,新环境的oracle一直在进行备份,但都没有测试验证备份的有效性,所以本次测试的重要性不言而喻了!以下为WIN平台下RMAN异机恢复实例。
10年积累的网站设计制作、成都网站设计经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站制作后付款的网站建设流程,更有开封免费网站建设让你可以放心的选择与我们合作。
源库 | 目标库 | |
操作系统 | 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>
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文件(目的是使用数据库启动到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
以此pfile启动数据库到nomount状态:
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):
5、创建flash_recovery_area、ORADATA目录,否则恢复数据时会报错ORA-01263: Name given for file destination directory is invalid 。
建立相关目录:
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
RMAN> list backup of archivelog all;
9、进行数据文件恢复:
RMAN> run { 2> set until sequence 18437; 3> restore database; 4> }
RMAN> run { 2> set until sequence 18437; 3> recover database; 4> }
10、resetlogs 打开数据库:
RMAN> alter database open resetlogs;
11、查看数据库状态:
12、添加注册表SID:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1 "ORACLE_SID"="HWPROD"
13、创建SPfile参数文件
SQL> create spfile from pfile;
可以看到相关路径已经创建新的参数文件:
14、重启数据库,是否以SPFILE启动:
SQL> select decode(count(*),1,'spfile','pfile') from v$spparameter where rownum=1 and isspecified = 'TRUE';
15、修改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 = (SERVICE_NAME = HWPROD) ) )
查看监听状态:
验证数据,OK!
2018-01-16更新
参考:http://blog.itpub.net/29119536/viewspace-1171894/
网页名称:Oracle11GRMAN单实例异机恢复
本文URL:http://myzitong.com/article/jdsigg.html