当前位置： 首页 > news >正文

使用duplicate搭建备库或者级联备库

news 2025/8/15 13:26:36

使用duplicate搭建备库或者级联备库：


主库或者源端：


1. 创建pfile，更改&添加部分参数、传输到备库；
2. 主库（或者源端）的tnsnames.ora文件添加 备库的连接信息

 
备库：


1. 备库添加静态监听
2. 添加主备库信息到TNSNAMES.ORA文件中（可选）
3. 将主库的orapw$SID 文件拷贝到备库，可使用sqlplus sys/password@primary as sysdba 验证密码正确与否
4. 备库 创建initstb.ora 里面的目录，并使用此pfile启动到Nomount状态

主库或者源端：
以上都做完，在主库或者源端做rman连接，使用duplicate进行复制了

以上是主备库需要做的事情汇总，

以下是使用duplicate复制的具体步骤

主库或者源端：
创建pfile，更改&添加部分参数、传输到备库这里我没有使用主库，而是主库的ADG备库作为源端：
1. sqlplus / as sysdba登录ADG备库
create pfile='/home/oracle/initstb.ora' from spfile;

去/home/oracle找到initstb.ora文件，替换里面所有的hgxx为 hgxxdg5 ,可添加如下参数，scp传输到备库
*.db_file_name_convert='hgxxdg1','hgxxdg5','/u01/app/oracle/oradata/HGXXDG1/datafile','/u01/app/oracle/oradata/HGXXDG5/datafile'
*.log_file_name_convert='hgxxdg1','hgxxdg5'
*.standby_file_management='auto'
*.db_create_file_dest='/u01/app/oracle/oradata' 
*.log_archive_config='dg_config=(hgxx,hgxxdg5)' 


log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/HDCC/archivelog


2. 主库tnsnames.ora 添加备库信息
cat $ORACLE_HOME/network/admin/tnsnames.ora

hgxxdg5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hgxxdg5 )
    )
  )


备库：
1. 备库添加静态监听
cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER = 
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = hgxx)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = hgxxdg5)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
  )
 
2. 添加主备库信息到TNSNAMES.ORA文件中（可选）
cat $ORACLE_HOME/network/admin/tnsnames.ora
hgxx =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hgxx)
    )
  )


hgxxdg5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hgxxdg5 )
    )
  )
  
 
3. 将主库的orapw$SID 文件拷贝到备库，可使用sqlplus sys/password@primary as sysdba 验证密码正确与否

4. 备库 创建initstb.ora 里面的目录，并使用此pfile启动到Nomount状态
4.1 在备库创建initstb.ora里面的目录&数据文件的目录，目录存在的话，跳过
mkdir -p /u01/app/oracle/admin/hgxxdg1/adump
mkdir -p /u01/app/oracle/oradata/hgxxdg1/controlfile/
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/fast_recovery_area

4.2启动要duplicate的备库到nomount状态
startup nomount pfile='/home/oracle/initstb.ora';
create spfile from pfile='/home/oracle/initstb.ora';


再回到主库或者源端使用rman登录，并duplicate复制
rman target sys/j5JP8zIe@HDCC auxiliary sys/j5JP8zIe@hdccdg5

duplicate target database for standby from active database nofilenamecheck;

以下是输出结果：

[oracle@D2-CC-DB78 admin]$ rman target sys/password@primary auxiliary sys/password@standby

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 14 11:10:24 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HGXX (DBID=893348868)
connected to auxiliary database: HGXX (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 14-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=357 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhdccdg1' auxiliary format 
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhdccdg5'   ;
}
executing Memory Script

Starting backup at 14-AUG-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=305 device type=DISK
Finished backup at 14-AUG-24

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/HGXXDG5/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 14-AUG-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_hdccdg1.f tag=TAG20240814T111029 RECID=42 STAMP=1176981029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-AUG-24

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/HDCCDG5/datafile/o1_mf_temp_gclf6dvf_.tmp";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/hgxxdg1/system.256.1005835259";
  ...中间省略
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/hgxxdg5/system.256.1005835259"   datafile 
 ...中间省略
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HGXXDG5/datafile/o1_mf_temp_gclf6dvf_.tmp in control file

executing command: SET NEWNAME

...中间省略

Starting backup at 14-AUG-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/hgxxdg1/html.326.1005901859
output file name=/u01/app/oracle/oradata/hgxxdg5/html.326.1005901859 tag=TAG20240814T111036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:06:35

input datafile file number=00004 name=/u01/app/oracle/oradata/hgxxdg1/users.259.1005835259
output file name=/u01/app/oracle/oradata/hgxxdg5/users.259.1005835259 tag=TAG20240814T111036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

...中间省略

Finished backup at 14-AUG-24

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=42 STAMP=1176985399 file name=/u01/app/oracle/oradata/hgxxdg5/system.256.1005835259

...中间省略

Finished Duplicate Db at 14-AUG-24

主库或者源库修改如下参数，也可以直接写在Init文件中：
alter system set log_archive_config='DG_CONFIG=(hgxxdg1,hgxxdg5)' scope=both sid='*';

alter system set log_archive_dest_5 ='SERVICE=hgxxdg5 LGWR ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=hgxxdg5' scope=both sid='*';
alter system set log_archive_dest_state_5=defer scope=both sid='*';
alter system set log_archive_dest_state_5=enable scope=both sid='*';


备库参数修改如下：
alter system set log_archive_dest_3 ='SERVICE=standby78 LGWR ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=hgxxdg1' scope=both sid='*';
alter system set log_archive_dest_state_3=defer scope=both sid='*';
alter system set log_archive_dest_state_3=enable scope=both sid='*';


备库开启恢复进程
alter database recover managed standby database using current logfile disconnect from session;

查看恢复进程是否开启以及传输日志进程
set linesize 400;
select process,pid,status,thread#,sequence# from v$managed_standby;
PROCESS                                 PID STATUS                                       THREAD#       SEQUENCE#
--------------------------- --------------- ------------------------------------ --------------- ---------------
ARCH                                  31943 CONNECTED                                          0               0
ARCH                                  31946 CONNECTED                                          0               0
ARCH                                  31948 CONNECTED                                          0               0
ARCH                                  31950 CONNECTED                                          0               0
MRP0                                  15347 WAIT_FOR_LOG                                       1           19717
RFS                                   18007 IDLE                                               0               0
RFS                                   15797 IDLE                                               0               0

查看备库alert日志，正在恢复数据了，这个是自动传输到备库的，并且开始恢复
Wed Aug 14 13:50:51 2024
Media Recovery Waiting for thread 1 sequence 19717

或者执行以下语句，查看备库执行过的log，跟主库或源端进行对比
SYS>select thread#,max(sequence#) from v$log_history group by thread#;

        THREAD#  MAX(SEQUENCE#)
--------------- ---------------
              1           19716
              2           15537

开启数据库为只读状态

SYS>alter database open;

Database altered.

SYS>alter database recover managed standby database using current logfile disconnect from session;

Database altered.

查看备库的状态

SYS>select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

以下其他数据字典表也可以作为参考：

查看应用日志延迟时间：

SQL> select value from v$dataguard_stats where name='apply lag';

查看接收日志延迟时间：

SQL> select value from v$dataguard_stats where name='transport lag';

查看接受日志情况：

SQL> select max(sequence#) from v$archived_log where applied='YES';

         
              

http://www.lryc.cn/news/426473.html

相关文章：

  • 【存储学习笔记】4：快照（Snapshot）技术的实现方式
  • 数根（字符串数根公式）
  • C语言之文件操作上卷（二十一）（逆行人生-2024）
  • 【微服务架构实战】结合实际案例进行微服务架构的设计与实现
  • 为什么要有二级指针
  • 如何保证数据不丢失？（死信队列）
  • 树莓派开发笔记01-树莓派的系统烧录以及初次开机配置
  • 微信答题小程序产品研发-后端开发
  • 回溯算法——LeetCode37 解数独
  • 【CPP】继承语法详解与菱形继承
  • 数据结构(6.2_1)——领接矩阵法
  • 诈骗未成功是否构成犯罪?
  • 网络协议栈应用层的意义（内含思维导图和解析图通俗易懂超易理解）
  • 【NXP-MCXA153】i2c驱动移植
  • C++(11)类语法分析(2)
  • 数字验证每日十问--（3）
  • 22.给定 n 对括号，实现一个算法生成所有可能的正确匹配的括号组合
  • 检测到目标URL存在http host头攻击漏洞
  • C++奇迹之旅：手写vector模拟实现与你探索vector 容器的核心机制与使用技巧
  • 018、钩子函数 mounted和beforeDestroy、父组件向子组件传递参数 props 的使用
  • xlnt在Windows中的dll,lib生成
  • 【网络】私有IP和公网IP的转换——NAT技术
  • java 面试 PDF 资料整理
  • 初步认识Linux系统
  • JavaScript AI 编程助手
  • 达梦数据库的系统视图v$datafile
  • Triton/window安装: triton-2.0.0-cp310-cp310-win_amd64.whl文件
  • 应急响应-DDOS-典型案例
  • JAVA学习之知识补充（下）
  • qt生成一幅纯马赛克图像