产品解决方案文档与社区权益中心定价云市场合作伙伴支持与服务了解阿里云
联系我们
4008013260
售前咨询售后服务
其他服务
我要建议我要投诉
备案控制台
探索云世界
开发者社区 云计算 文章 正文

Oracle RAC迁移到单实例DB

2019-06-09 8188
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 这篇文章记录Oracle RAC通过RMAN备份如何迁移到单机DB，文章中描述了几个容易出错的地方，如下：    ①RAC到单机redo文件位置处理    ②RAC多个UNDO处理    ③RAC到单机临时表空间处理下面开始文章正文。

这篇文章记录Oracle RAC通过RMAN备份如何迁移到单机DB，文章中描述了几个容易出错的地方，如下：
    ①RAC到单机redo文件位置处理
    ②RAC多个UNDO处理
    ③RAC到单机临时表空间处理
下面开始文章正文。

一、备份源库

1、检查数据库大小

SQL> select sum(a.bytes - b.bytes)/1024/ 1024 "sum_used MB"
  2      from (select tablespace_name, sum(bytes) bytes  
  3           from dba_data_files  
  4          group by tablespace_name) a,  
  5        (select tablespace_name, sum(bytes) bytes, max(bytes) largest  
  6           from dba_free_space  
  7          group by tablespace_name) b  
  8       where a.tablespace_name = b.tablespace_name;

2、检查备份磁盘大小

df -h

3、备份数据库

rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup incremental level 0 database format '/backup/fulldb_%u_%p_%c.bak';
sql 'alter system archive log current';
backup format '/backup/arc_%u_%p_%c' archivelog all;
backup current controlfile format '/backup/control_%U.bak';
backup spfile format '/backup/spfile_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

二、在目标端还原数据库

1、将备份文件传到目标端服务器

    目标端服务器已装完数据库软件，不创建DB，安装软件过程这里就不在叙述了。

2、创建必要目录

mkdir -p /u01/oracle/app/oracle/admin/xxx/adump    //其中xxx是数据库db_name
mkdir -p /u01/oracle/app/oracle/oradata/hisprod
mkdir -p /u01/oracle/app/oracle/oradata/arch

3、修改参数文件

将集群参数全部去除，注意如果目标端服务器配置和源端不一样，需要对应修改内存值

*.audit_file_dest='/u01/oracle/app/oracle/admin/xxx/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oracle/app/oracle/oradata/xxx/control01.ctl','/u01/oracle/app/oracle/oradata/xxx/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/s01/oracle/app/oracle/oradata/xxx'
*.db_domain=''
*.db_name='xxx'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/s01/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xxxDB)'
*.memory_target=1932735283
*.nls_territory='CHINA'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.processes=500
*.sec_case_sensitive_logon=FALSE
*.undo_tablespace='UNDOTBS1'

修改完成后数据库启动到nomount状态

[oracle@wrtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 9 13:56:49 2019

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/pfile.ora';
ORACLE instance started.

Total System Global Area 1937457152 bytes
Fixed Size                  2254464 bytes
Variable Size            1275070848 bytes
Database Buffers          654311424 bytes
Redo Buffers                5820416 bytes
SQL> 


SQL> create spfile from pfile='/u01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/pfile.ora';

File created.

SQL> 
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount; 
ORACLE instance started.

Total System Global Area 1937457152 bytes
Fixed Size                  2254464 bytes
Variable Size            1275070848 bytes
Database Buffers          654311424 bytes
Redo Buffers                5820416 bytes
SQL>

4、恢复控制文件

从备份文件中恢复从之文件，操作如下：

RMAN> restore controlfile from '/software/bak/control_7eu36n34_1_1.bak';

Starting restore at 09-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/app/oracle/oradata/xxx/control01.ctl
output file name=/u01/oracle/app/oracle/oradata/xxx/control02.ctl
Finished restore at 09-JUN-19

RMAN>
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

5、设置归档

alter database archivelog;
alter system set log_archive_dest_1='location=/u01/oracle/app/oracle/oradata/arch';

6、还原数据文件

catalog start with '/software/bak';

run {
set newname for datafile  4 to '/u01/oracle/app/oracle/oradata/xxx/users01.dbf';
set newname for datafile  3 to '/u01/oracle/app/oracle/oradata/xxx/undotbs01.dbf';
set newname for datafile  2 to '/u01/oracle/app/oracle/oradata/xxx/sysaux.dbf';
set newname for datafile  1 to '/u01/oracle/app/oracle/oradata/xxx/system.dbf';
set newname for datafile  5 to '/u01/oracle/app/oracle/oradata/xxx/undotbs02.dbf';
set newname for datafile  6 to '/u01/oracle/app/oracle/oradata/xxx/xxx1.dbf';
set newname for datafile  7 to '/u01/oracle/app/oracle/oradata/xxx/xxx2.dbf';
set newname for datafile  8 to '/u01/oracle/app/oracle/oradata/xxx/xxx3.dbf';
set newname for datafile  9 to '/u01/oracle/app/oracle/oradata/xxx/xxx4.dbf';
set newname for tempfile 1  to '/u01/oracle/app/oracle/oradata/xxx/temp01.dbf';     
set newname for tempfile 2  to '/u01/oracle/app/oracle/oradata/xxx/xxx5.dbf';
restore database;
switch datafile all;
switch tempfile all;
}

注：在还原数据文件时，数据库默认的临时表空间在数据文件还原后是会创建数据文件，其他自己手动创建的临时表空间则不会生成，需要手动处理。

7、修改redo日志位置

select GROUP#,STATUS,TYPE,MEMBER  from v$logfile;

alter database rename file '+DATA/xxx/onlinelog/group_1.302.978617245' to '/u01/oracle/app/oracle/oradata/xxx/redo01_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_1.1215.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo01_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_2.303.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo02_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_2.866.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo02_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_3.306.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo03_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_3.1243.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo03_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_4.307.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo04_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_4.1227.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo04_2.log';

8、恢复数据库

RMAN> recover database;

Starting recover at 09-JUN-19
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7886
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9475
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9476
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7887
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9477
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7888
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7889
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9478
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9479
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7890
channel ORA_DISK_1: reading from backup piece /software/bak/arc_7pu36obs_1_1
channel ORA_DISK_1: piece handle=/software/bak/arc_7pu36obs_1_1 tag=TAG20190603T193613
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9475_978617245.dbf thread=1 sequence=9475
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7886_978617245.dbf thread=2 sequence=7886
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9476_978617245.dbf thread=1 sequence=9476
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7887_978617245.dbf thread=2 sequence=7887
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9477_978617245.dbf thread=1 sequence=9477
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7888_978617245.dbf thread=2 sequence=7888
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7889_978617245.dbf thread=2 sequence=7889
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9478_978617245.dbf thread=1 sequence=9478
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9479_978617245.dbf thread=1 sequence=9479
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7890_978617245.dbf thread=2 sequence=7890
unable to find archived log
archived log thread=2 sequence=7891
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/09/2019 14:36:05
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 7891 and starting SCN of 1730567738
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL>

9、修复临时数据文件

select user,temporary_tablespace from dba_users;
create temporary tablespace tmp1 tempfile '/u01/oracle/app/oracle/oradata/xxx/tmp1.dbf' size 2G autoextend on;
alter user user01 temporary tablespace tmp1;
drop tablespace tmpa including contents and datafiles;

10、删除多余的日志组

select b.member,a.group#,thread#,a.status from v$log a,v$logfile b where a.group#=b.group# order by a.thread#,b.group#;
alter database disable thread 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

11、清理多余undo

select name from v$tablespace where name like '%UNDO%';
drop tablespace undotbs2 including contents and datafiles;

至此，完成从RAC恢复到单机操作！

关键词：
RAC oracle
Oracle数据库一键上云rac
Oracle数据库一键上云迁移
Oracle数据库一键上云DB
Oracle数据库一键上云单实例
持续高温
目录
相关文章
机器学习Zero
|
1月前
|
存储 运维 Oracle
Oracle系列十八：Oracle RAC
Oracle系列十八：Oracle RAC
机器学习Zero
41 0
snowofsummer
|
2月前
|
Oracle 关系型数据库
oracle Hanganalyze no RAC
oracle Hanganalyze no RAC
snowofsummer
15 0
snowofsummer
|
2月前
|
Oracle 关系型数据库
oracle rac 手工安装补丁，不适用auto
oracle rac 手工安装补丁，不适用auto
snowofsummer
25 3
明哥的IT随笔
|
6月前
|
Oracle 关系型数据库 Java
分享一个 Oracle RAC 模式下客户端建立JDBC初始连接时因ONS造成应用启动时卡顿30秒问题的排查分析案例
分享一个 Oracle RAC 模式下客户端建立JDBC初始连接时因ONS造成应用启动时卡顿30秒问题的排查分析案例
明哥的IT随笔
84 0
q7s2kces74wvy
|
7月前
|
Oracle 关系型数据库 分布式数据库
如何从Oracle迁移到PolarDB(ADAM)（二）
如何从Oracle迁移到PolarDB(ADAM)（二）
q7s2kces74wvy
128 0
q7s2kces74wvy
|
7月前
|
SQL Oracle 关系型数据库
Polar DB-O （兼容 Oracle 语法版本）和Polar DB PostgreSQL 版本概述（二）
Polar DB-O （兼容 Oracle 语法版本）和Polar DB PostgreSQL 版本概述（二）
q7s2kces74wvy
678 0
姚远OracleACE
|
11月前
|
存储 Oracle 关系型数据库
Oracle的RAC环境下SYS密码修改要注意密码文件是否共享
检查数据密码文件的配置
姚远OracleACE
483 0
小小工匠
|
11月前
|
Oracle 关系型数据库 数据库
Oracle实例迁移_真实场景实操
Oracle实例迁移_真实场景实操
小小工匠
78 0
auqbllxiu
|
Oracle 关系型数据库 数据库
《ORACLE数据库和应用异构 迁移最佳实践》电子版地址
ORACLE数据库和应用异构 迁移最佳实践
auqbllxiu
50 0
《ORACLE数据库和应用异构 迁移最佳实践》电子版地址
lihongda
|
存储 Oracle 关系型数据库
异构迁移Oracle的前世今生——HuaweiCloud+UGO+DRS（三）
异构迁移Oracle的前世今生——HuaweiCloud+UGO+DRS
lihongda
119 0
异构迁移Oracle的前世今生——HuaweiCloud+UGO+DRS（三）
为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
解决方案技术解决方案
文档与社区文档开发者社区天池大赛培训与认证
权益中心免费试用高校计划企业扶持计划推荐返现计划
支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
关注阿里云
关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
阿里云APP阿里云微信
联系我们：4008013260
法律声明Cookies政策廉正举报安全举报联系我们加入我们
阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
© 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
浙公网安备 33010602009975号浙B2-20080101-4