厦门创意网站建设/seo零基础视频教程

使用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';