暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / RMAN恢复数据库到不同实例名(同一个主机)

RMAN恢复数据库到不同实例名(同一个主机)

刘洪 2020-05-31
2497

– RMAN恢复数据库到不同实例名(同一个主机)
– 将SID1的数据完整复制到SID2

1，配置listener.ora及tnsname.ora，添加SID2(这一步最为重要，因为无法识别动态注册，只能识别静态注册。)

异机需要安装数据库软件，配置listener.ora和tnsname.ora

listener.ora # 以下为相同主机listener的配置

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) )
)

SID_LIST_LISTENER = (SID_LIST =
(SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/db) (PROGRAM = extproc) )
(SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = /u01/app/oracle/product/db) (SID_NAME = ORCL) )
(SID_DESC = (GLOBAL_DBNAME = pdb1) (ORACLE_HOME = /u01/app/oracle/product/db) (SID_NAME = ORCL) )
(SID_DESC = (GLOBAL_DBNAME = CP) (ORACLE_HOME = /u01/app/oracle/product/db) (SID_NAME = CP) )
)

tnsnames.ora

ORCL = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) )
)

CP = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CP) )
)

pdb1 = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) )
)

2，创建SID2密码文件

orapwd file=$ORACLE_HOME/dbs/orapwCP password=oracle entries=10

3．将SID2进入到nomount状态<准备SID2目录，参数文件，并启动到mount状态>

创建恢复目录

su - oracle
mkdir -p /u01/cp1/oradata/ ; mkdir -p /u01/cp1/log/adump ; mkdir -p /u01/cp1/fra
mkdir -p /u01/cp1/dbf ; mkdir -p /u01/cp1/oradata/pdbseed ; mkdir -p /u01/cp1/oradata/pdb1/

rm /u01/cp1/oradata/* /u01/cp1/log/adump/* /u01/cp1/fra/* /u01/cp1/dbf/* /u01/cp1/log/diag/* -rf
mkdir -p /u01/cp1/oradata/pdbseed ; mkdir -p /u01/cp1/oradata/pdb1/
find /u01/cp1/

备份控制文件SID1

create pfile=’/u01/orcl/pfile.ora’ from spfile;
create pfile=’/u01/cp1/pfile.ora’ from spfile; – 创建源库参数文件，修改参数配置，创建spfile
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/u01/cp1/controlfile_tace.ora’;

修改参数文件用于SID2，修改dest,db_name

vi /u01/cp1/pfile.ora

*.audit_file_dest=’/u01/cp1/log/adump’
*.audit_trail=‘db’
*.compatible=‘12.1.0.2.0’
*.control_files=’/u01/cp1/oradata/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/cp1/dbf’
*.db_domain=’’
*.db_name=‘CP’
*.db_recovery_file_dest=’/u01/cp1/fra’
*.db_recovery_file_dest_size=31457280
*.diagnostic_dest=’/u01/cp1/log’
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=384m
*.processes=300
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_target=1152m
*.undo_tablespace=‘UNDOTBS1’

– SID2使用修改之后的参数文件启动到nomount状态
export ORACLE_SID=CP
sqlplus / as sysdba
create spfile from pfile=’/u01/cp1/pfile.ora’;
startup nomount

report schema;重命名数据文件并

SET NEWNAME FOR DATABASE TO ‘/oradata/%U’;

4，使用RMAN恢复数据库
export ORACLE_SID=CP
rman target sys/oracle@ORCL auxiliary sys/oracle@CP
run{
set newname for datafile 1 to ‘/u01/cp1/oradata/system01.dbf’;
set newname for datafile 3 to ‘/u01/cp1/oradata/sysaux01.dbf’;
set newname for datafile 4 to ‘/u01/cp1/oradata/undotbs01.dbf’;
set newname for datafile 5 to ‘/u01/cp1/oradata/pdbseed/system01.dbf’;
set newname for datafile 6 to ‘/u01/cp1/oradata/users01.dbf’;
set newname for datafile 7 to ‘/u01/cp1/oradata/pdbseed/sysaux01.dbf’;
set newname for datafile 15 to ‘/u01/cp1/oradata/pdb1/o1_mf_system_hf24bonn_.dbf’;
set newname for datafile 16 to ‘/u01/cp1/oradata/pdb1/o1_mf_sysaux_hf24bonp_.dbf’;
set newname for datafile 17 to ‘/u01/cp1/oradata/pdb1/users01.dbf’;
set newname for tempfile 1 to ‘/u01/cp1/oradata/temp01.dbf’;
set newname for tempfile 2 to ‘/u01/cp1/oradata/pdbseed/pdbseed_temp01.dbf’;
set newname for tempfile 3 to ‘/u01/cp1/oradata/pdb1/o1_mf_temp_hf24bonp_.dbf’;
DUPLICATE TARGET DATABASE TO CP
LOGFILE
GROUP 1 (’/u01/cp1/oradata/redo01.dbf’) SIZE 10M REUSE,
GROUP 2 (’/u01/cp1/oradata/redo02.dbf’) SIZE 10M REUSE;
}

#########################################################################

删除新建的数据库SID2

export ORACLE_SID=CP
sqlplus / as sysdba
startup mount exclusive restrict;
drop database;

#########################################################################
################ DUPLICATE TARGET DATABASE TO CP ########################
################ 日志如下 ########################

– 1 contents of Memory Script: 设置参数文件，设置db_name,db_unique_name,restore控制文件，启动到mount
{
sql clone “alter system set db_name = ‘‘ORCL’’ comment= ‘‘Modified by RMAN duplicate’’ scope=spfile”;
sql clone “alter system set db_unique_name = ‘‘CP’’ comment= ‘‘Modified by RMAN duplicate’’ scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}

– 2 contents of Memory Script: 重命名控制文件中的数据文件路径及名称，存储到新的路径
{
set until scn 2734708;
set newname for datafile 1 to “/u01/cp1/oradata/system01.dbf”;
set newname for datafile 3 to “/u01/cp1/oradata/sysaux01.dbf”;
set newname for datafile 4 to “/u01/cp1/oradata/undotbs01.dbf”;
set newname for datafile 5 to “/u01/cp1/oradata/pdbseed/system01.dbf”;
set newname for datafile 6 to “/u01/cp1/oradata/users01.dbf”;
set newname for datafile 7 to “/u01/cp1/oradata/pdbseed/sysaux01.dbf”;
set newname for datafile 15 to “/u01/cp1/oradata/pdb1/o1_mf_system_hf24bonn_.dbf”;
set newname for datafile 16 to “/u01/cp1/oradata/pdb1/o1_mf_sysaux_hf24bonp_.dbf”;
set newname for datafile 17 to “/u01/cp1/oradata/pdb1/users01.dbf”;
restore clone database
;
}

– 3 contents of Memory Script: – 切换数据文件到最近的COPY
{
switch clone datafile all;
}

– 4 contents of Memory Script: – 恢复数据库
{
set until scn 2734708;
recover clone database delete archivelog
;
}

– 5 contents of Memory Script: 修改db_name=CP，取消db_unique_name设置
{
sql clone “alter system set db_name = ‘‘CP’’ comment= ‘‘Reset to original value by RMAN’’ scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}

– 6 启动数据库，重建控制文件
Oracle instance started
Total System Global Area 1207959552 bytes
Fixed Size 2923776 bytes
Variable Size 335545088 bytes
Database Buffers 855638016 bytes
Redo Buffers 13852672 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CP” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u01/cp1/oradata/redo01.dbf’ ) SIZE 10 M REUSE,
GROUP 2 ( ‘/u01/cp1/oradata/redo02.dbf’ ) SIZE 10 M REUSE
DATAFILE
‘/u01/cp1/oradata/system01.dbf’,
‘/u01/cp1/oradata/pdbseed/system01.dbf’,
‘/u01/cp1/dbf/CP/datafile/o1_mf_system_hf6govn3_.dbf’
CHARACTER SET AL32UTF8

– 7 contents of Memory Script: 设置临时文件，切换临时文件，注册数据文件Copy，切换数据文件
{
set newname for tempfile 1 to “/u01/cp1/oradata/temp01.dbf”;
set newname for tempfile 2 to “/u01/cp1/oradata/pdbseed/pdbseed_temp01.dbf”;
set newname for tempfile 3 to “/u01/cp1/oradata/pdb1o1_mf_temp_hf24bonp_.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u01/cp1/oradata/sysaux01.dbf”, “/u01/cp1/oradata/undotbs01.dbf”, “/u01/cp1/oradata/users01.dbf”,
“/u01/cp1/oradata/pdbseed/sysaux01.dbf”, “/u01/cp1/dbf/CP/datafile/o1_mf_sysaux_hf6432p1_.dbf”, “/u01/cp1/oradata/pdb1/users01.dbf”;
switch clone datafile all;
}

– 8 contents of Memory Script: --重设日志打开数据库
{
Alter clone database open resetlogs;
}

– 9 contents of Memory Script: 打开PDB
{
sql clone “alter pluggable database all open”;
}

oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
663次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
626次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
535次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
480次阅读
2025-04-22 00:20:37
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
480次阅读
2025-04-17 17:02:24
一页概览：Oracle GoldenGate
甲骨文云技术
462次阅读
2025-04-30 12:17:56
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
451次阅读
2025-04-22 00:13:51
火焰图--分析复杂SQL执行计划的利器
听见风的声音
407次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
371次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
348次阅读
2025-05-05 19:28:36
三
三分恶
关注
51
文章
0
粉丝
20K+
浏览量
暂无图片
获得了2次收藏
热门文章
Oracle password 密码策略 密码复杂度
2020-06-24 9743浏览
python paramiko 设置远程ssh执行命令超时限制
2021-06-22 9720浏览
library cache lock 原因及解决方案
2021-05-10 7457浏览
linux Oracle修改ORACLE_SID、修改DB_NAME，修改实例名，服务名
2020-05-31 6124浏览
使用SecureCRT开启图形化界面功能，在Windown显示Linux服务器图形化界面
2020-06-20 5879浏览
在线实训环境入口
Oracle 18C 在线实训环境
查看详情
最新文章
人大金仓 安装报错ArrayIndexOutOfBoundsException
2025-02-07 74浏览
CRS-42216: No interfaces are configured on the local node for interface definition ib1(:.*)?:10.10.1
2024-08-02 179浏览
opengauss数据库初始化报错：[GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details.
2024-03-05 652浏览
“Oracle_tns_listener远程投毒”故障诊断
2023-11-30 855浏览
ORA-01873 故障处理
2023-11-30 2251浏览
目录
  • 1，配置listener.ora及tnsname.ora，添加SID2(这一步最为重要，因为无法识别动态注册，只能识别静态注册。)
  • 异机需要安装数据库软件，配置listener.ora和tnsname.ora
  • listener.ora # 以下为相同主机listener的配置
  • tnsnames.ora
    • 2，创建SID2密码文件
    • 3．将SID2进入到nomount状态<准备SID2目录，参数文件，并启动到mount状态>
  • 创建恢复目录
  • 备份控制文件SID1
  • 修改参数文件用于SID2，修改dest,db_name
  • vi /u01/cp1/pfile.ora
  • report schema;重命名数据文件并
  • SET NEWNAME FOR DATABASE TO ‘/oradata/%U’;
  • 删除新建的数据库SID2