问题描述：主备两个库不在同一个机房，此时想从这一套库中在复制一套可读可写的新库出来。网络带宽要求比较高，需要从备库中使用备份在起一个新库，也要测试下使用duplicate从备库能够在复制一个新库。经过测试，使用备份和duplicate都可以从备库中恢复新库。


(资料图)

下面使用duplicate恢复新主库，通过搭建级联DG的方式，主库传输归档到备库1，备库1在传输归档到备库2。后面也可以进行拆分，通过备库1恢复的备库2，调整主库归档路径变成主库把归档直接分发给备库2，实现一主两从的改造。

1.环境介绍

利用实时备库级联DG搭建，利用duplicate在线进行新库恢复

IP

oracle版本

oracle_sid

db_unique_name

角色

192.168.163.25

19.13

orcl

orcl

主库

192.168.163.45

19.13

orclstd

orclstd

备库

192.168.163.47

19.13

orclstd2

orclstd2

新主库

2.备库1配置

--查看备库状态

SQL>  select open_mode,protection_mode,database_role,switchover_status from v$database;OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS-------------------- -------------------- ---------------- --------------------READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWEDSQL>  SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS--------- ------------ ---------- ---------- ---------- ----------ARCH      CLOSING        1      54          1       1120DGRD      ALLOCATED        0       0          0      0DGRD      ALLOCATED        0       0          0      0ARCH      CLOSING        1      55          1      2ARCH      CLOSING        1      40          1      2ARCH      CLOSING        1      48          1      2RFS      WRITING        1      56      12477      1RFS      IDLE            1       0          0      0LNS      WRITING        1      56      12476      1MRP0      APPLYING_LOG        1      56      12476     245760DGRD      ALLOCATED        0       0          0      011 rows selected.

2.1修改参数

--修改orclstd参数，这里使用dest_2或者dest_3都可以
alter system set log_archive_config="dg_config=(orcl,orclstd,orclstd2)"; alter system set log_archive_dest_3="service=orclstd2 async valid_for=(standby_logfile,standby_role) db_unique_name=orclstd2";
--查看orclstd参数配置SQL> set linesize 500 pages 0 col value for a90 col name for a50 select name,value from v$parameter where name in ("db_name","db_unique_name","log_archive_config","log_archive_dest_1","log_archive_dest_2","log_archive_dest_state_1","log_archive_dest_state_2","remote_login_passwordfile","log_archive_format","log_archiveSQL> _max_processes","fal_server","db_file_name_convert","log_file_name_convert","standby_file_management");SQL> SQL> db_file_name_convert                   /oradata/ORCL/, /oradata/orclstd/log_file_name_convert                   /oradata/ORCL/, /oradata/orclstd/log_archive_dest_1                   location=/home/oracle/flashdata/ORCLSTD/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_U                           NIQUE_NAME=orclstdlog_archive_dest_2                   SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstdlog_archive_dest_state_1               enablelog_archive_dest_state_2               enablefal_server                       orcllog_archive_config                   dg_config=(orcl,orclstd,orclstd2)log_archive_format                   %t_%s_%r.dbflog_archive_max_processes               4standby_file_management                AUTOremote_login_passwordfile               EXCLUSIVEdb_name                        orcldb_unique_name                       ORCLSTD14 rows selected.

2.2创建pfile

create pfile="/tmp/initorclstd2.ora" from spfile;

2.3配置tnsname

[oracle@19c-dg:dbs]>$cat ../network/admin/tnsnames.oraorcl =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.25)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )orclstd =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.45)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd)    )  )orclstd2 =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.47)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd2)    )  )

2.4传输文件

传输pfile，密码文件到备库2

3.备库2配置

3.1pfile修改

[oracle@19c-duplicate admin]$ cat ../../dbs/initorclstd2.ora orclstd2.__data_transfer_cache_size=0orclstd2.__db_cache_size=343932928orclstd2.__inmemory_ext_roarea=0orclstd2.__inmemory_ext_rwarea=0orclstd2.__java_pool_size=79691776orclstd2.__large_pool_size=4194304orclstd2.__oracle_base="/u01/app/oracle"#ORACLE_BASE set from environmentorclstd2.__pga_aggregate_target=192937984orclstd2.__sga_target=771751936orclstd2.__shared_io_pool_size=37748736orclstd2.__shared_pool_size=289406976orclstd2.__streams_pool_size=0orclstd2.__unified_pga_pool_size=0*._optimizer_cartesian_enabled=FALSE*.audit_file_dest="/u01/app/oracle/admin/orclstd2/adump"*.audit_trail="NONE"*.compatible="19.0.0"*.control_files="/oradata/orclstd2/control01.ctl","/oradata/orclstd2/control02.ctl"*.db_block_size=8192*.db_create_file_dest="/oradata"*.db_name="orcl"*.db_recovery_file_dest_size=16106127360*.db_recovery_file_dest="/home/oracle/flashdata"*.db_unique_name="ORCLSTD2"*.deferred_segment_creation=FALSE*.diagnostic_dest="/u01/app/oracle"*.dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"*.event="10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90"*.fal_client="orclstd2"*.fal_server="orclstd"*.log_archive_config="dg_config=(orcl,orclstd,orclstd2)"*.log_archive_dest_1="location=/home/oracle/flashdata/ORCLSTD/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd2"*.log_archive_dest_2="SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd"*.log_archive_format="%t_%s_%r.dbf"*.nls_language="AMERICAN"*.nls_territory="AMERICA"*.open_cursors=300*.pga_aggregate_target=184m*.processes=300*.remote_login_passwordfile="EXCLUSIVE"*.result_cache_max_size=0*.sga_target=735m*.standby_file_management="AUTO"*.undo_tablespace="UNDOTBS1"

3.2监听配置

[oracle@19c-duplicate admin]$ cat listener.ora LISTENER = (DESCRIPTION_LIST =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.47)(PORT = 1521))    )  )SID_LIST_LISTENER = (SID_LIST =   (SID_DESC =     (GLOBAL_DBNAME = orclstd2)     (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)     (SID_NAME = orclstd2)    )   )ADR_BASE_LISTENER = /u01/app/oracle--打开监听lsnrctl start

3.3tnsname配置

[oracle@19c-duplicate admin]$ cat tnsnames.ora orcl =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.25)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )orclstd =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.45)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd)    )  )orclstd2 =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.47)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd2)    )  )

tnsname验证

备库1和备库2互相验证

[oracle@19c-dg:dbs]>$tnsping orclstdTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2022 20:48:05Copyright (c) 1997, 2021, Oracle.  All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.45)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orclstd)))OK (30 msec)[oracle@19c-dg:dbs]>$tnsping orclstd2TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2022 20:48:06Copyright (c) 1997, 2021, Oracle.  All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.47)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orclstd2)))OK (0 msec)

3.4创建目录

mkdir -p相关目录/home/oracle/flashdata/ORCLSTD/archivelog/oradata/orclstd2/u01/app/oracle/admin/orclstd2/adump/home/oracle/flashdata

3.5rman在线创建二级备库

[oracle@19c-duplicate archivelog]$ rman target sys/oracle@orclstd auxiliary sys/oracle@orclstd2Recovery Manager: Release 19.0.0.0.0 - Production on Wed Nov 16 18:59:06 2022Version 19.13.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1646277430)connected to auxiliary database: ORCL (not mounted)RMAN> RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;Starting Duplicate Db at 2022-11-16 18:59:17using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=20 device type=DISKcurrent log archived at primary databasecurrent log archived at primary databasecontents of Memory Script:{   backup as copy reuse   passwordfile auxiliary format  "/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapworclstd2"   ;}executing Memory ScriptStarting backup at 2022-11-16 19:03:47allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=43 device type=DISKFinished backup at 2022-11-16 19:03:48duplicating Online logs to Oracle Managed File (OMF) locationduplicating Datafiles to Oracle Managed File (OMF) locationcontents of Memory Script:{   restore clone from service  "orclstd" standby controlfile;}executing Memory ScriptStarting restore at 2022-11-16 19:03:48using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service orclstdchannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=/oradata/orclstd2/control01.ctloutput file name=/oradata/orclstd2/control02.ctlFinished restore at 2022-11-16 19:03:52contents of Memory Script:{   sql clone "alter database mount standby database";}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{   set newname for clone tempfile  1 to new;   switch clone tempfile all;   set newname for clone datafile  1 to new;   set newname for clone datafile  3 to new;   set newname for clone datafile  4 to new;   set newname for clone datafile  7 to new;   restore   from  nonsparse   from service  "orclstd"   clone database   ;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /oradata/ORCLSTD2/datafile/o1_mf_temp_%u_.tmp in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 2022-11-16 19:03:56using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service orclstdchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/ORCLSTD2/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service orclstdchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/ORCLSTD2/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service orclstdchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/ORCLSTD2/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: using network backup set from service orclstdchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/ORCLSTD2/datafile/o1_mf_users_%u_.dbfchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 2022-11-16 19:06:46contents of Memory Script:{   switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=9 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_system_kq9jwx0b_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=10 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_sysaux_kq9jz8rk_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=11 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_undotbs1_kq9k1omp_.dbfdatafile 7 switched to datafile copyinput datafile copy RECID=12 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_users_kq9k23xg_.dbfcontents of Memory Script:{   set until scn  3192644;   recover   standby   clone database   noredo    delete archivelog   ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 2022-11-16 19:06:46using channel ORA_AUX_DISK_1Finished recover at 2022-11-16 19:06:46contents of Memory Script:{   delete clone force archivelog all;}executing Memory Scriptreleased channel: ORA_DISK_1released channel: ORA_AUX_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=43 device type=DISKspecification does not match any archived log in the repositoryFinished Duplicate Db at 2022-11-16 19:07:34

3.6打开数据库验证

此时数据库状态，从主库把归档发送到备库1，备库1把归档在传回备库2上，备库不开启实时应用，只传输归档，不应用归档

orclstd2:SQL> alter database open;SQL> select status,instance_name from v$instance;STATUS         INSTANCE_NAME------------ ----------------OPEN         orclstd2SQL> select open_mode,protection_mode,database_role,switchover_status from v$database;OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS-------------------- -------------------- ---------------- --------------------READ ONLY         MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWEDorclstd:SQL> select open_mode,protection_mode,database_role,switchover_status from v$database;OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS-------------------- -------------------- ---------------- --------------------READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWEDorcl:SQL> select open_mode,protection_mode,database_role,switchover_status from v$database;OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS-------------------- -------------------- ---------------- --------------------READ WRITE         MAXIMUM PERFORMANCE  PRIMARY       TO STANDBY

orcl切换归档

SQL> archive log list;Database log mode           Archive ModeAutomatic archival           EnabledArchive destination           /archivelogOldest online log sequence     54Next log sequence to archive   56Current log sequence           56SQL> SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> SQL> archive log list;Database log mode           Archive ModeAutomatic archival           EnabledArchive destination           /archivelogOldest online log sequence     57Next log sequence to archive   59Current log sequence           59SQL>

orclstd2:

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS--------- ------------ ---------- ---------- ---------- ----------ARCH      CONNECTED        0       0          0      0DGRD      ALLOCATED        0       0          0      0DGRD      ALLOCATED        0       0          0      0ARCH      CLOSING        1      58          1      4ARCH      CLOSING        1      56      12288       1382ARCH      CLOSING        1      57          1      2RFS      IDLE            1       0          0      0RFS      IDLE            1      59        106      1RFS      IDLE            0       0          0      09 rows selected.[oracle@19c-duplicate admin]$ cd /home/oracle/flashdata/ORCLSTD/archivelog/[oracle@19c-duplicate archivelog]$ lltotal 10984-rw-r-----. 1 oracle oinstall 3655680 Nov 16 19:09 1_53_1118496696.dbf-rw-r-----. 1 oracle oinstall  573952 Nov 16 19:18 1_54_1118496696.dbf-rw-r-----. 1 oracle oinstall    1536 Nov 16 19:18 1_55_1118496696.dbf-rw-r-----. 1 oracle oinstall 6999040 Nov 16 20:55 1_56_1118496696.dbf-rw-r-----. 1 oracle oinstall    1536 Nov 16 20:55 1_57_1118496696.dbf-rw-r-----. 1 oracle oinstall    2560 Nov 16 20:55 1_58_1118496696.dbf

3.7 开启实时同步

SQL> alter database recover  managed standby database using current logfile disconnect from session;此时主库相当于两个备库

4.测试主库到备库2

关闭备库1，主库的归档就传不到备库2了。如果在主库直接添加远程传输路径到备库2，备库2是不是还是可以正常接收归档以及应用呢

orcl：

alter system set log_archive_config="dg_config=(orcl,orclstd,orclstd2)";alter system set log_archive_dest_3="SERVICE=orclstd2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd2";alter system set log_archive_dest_state_3=enable;

添加tnsname

orcl =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.25)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )orclstd =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.45)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd)    )  )orclstd2 =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.47)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orclstd2)    )  )

orclstd2添加tns

关闭备库1，查看主库到备库2的同步情况，可以正常同步以及应用

推荐内容