问题描述：主备两个库不在同一个机房，此时想从这一套库中在复制一套可读可写的新库出来。网络带宽要求比较高，需要从备库中使用备份在起一个新库，也要测试下使用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 ALLOWED SQL> SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 54 1 1120 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 55 1 2 ARCH CLOSING 1 40 1 2 ARCH CLOSING 1 48 1 2 RFS WRITING 1 56 12477 1 RFS IDLE 1 0 0 0 LNS WRITING 1 56 12476 1 MRP0 APPLYING_LOG 1 56 12476 245760 DGRD ALLOCATED 0 0 0 0 11 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=orclstd log_archive_dest_2 SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd log_archive_dest_state_1 enable log_archive_dest_state_2 enable fal_server orcl log_archive_config dg_config=(orcl,orclstd,orclstd2) log_archive_format %t_%s_%r.dbf log_archive_max_processes 4 standby_file_management AUTO remote_login_passwordfile EXCLUSIVE db_name orcl db_unique_name ORCLSTD 14 rows selected.
2.2创建pfile
create pfile='/tmp/initorclstd2.ora' from spfile;
2.3配置tnsname
[oracle@19c-dg:dbs]>$cat ../network/admin/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) ) )
2.4传输文件
传输pfile，密码文件到备库2
3. 备库2配置
3.1 pfile修改
[oracle@19c-duplicate admin]$ cat ../../dbs/initorclstd2.ora orclstd2.__data_transfer_cache_size=0 orclstd2.__db_cache_size=343932928 orclstd2.__inmemory_ext_roarea=0 orclstd2.__inmemory_ext_rwarea=0 orclstd2.__java_pool_size=79691776 orclstd2.__large_pool_size=4194304 orclstd2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orclstd2.__pga_aggregate_target=192937984 orclstd2.__sga_target=771751936 orclstd2.__shared_io_pool_size=37748736 orclstd2.__shared_pool_size=289406976 orclstd2.__streams_pool_size=0 orclstd2.__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.3 tnsname配置
[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 orclstd TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2022 20:48:05 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting 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 orclstd2 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2022 20:48:06 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting 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@orclstd2 Recovery Manager: Release 19.0.0.0.0 - Production on Wed Nov 16 18:59:06 2022 Version 19.13.0.0.0 Copyright (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:17 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK current log archived at primary database current log archived at primary database contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapworclstd2' ; } executing Memory Script Starting backup at 2022-11-16 19:03:47 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=43 device type=DISK Finished backup at 2022-11-16 19:03:48 duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { restore clone from service 'orclstd' standby controlfile; } executing Memory Script Starting restore at 2022-11-16 19:03:48 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orclstd channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 output file name=/oradata/orclstd2/control01.ctl output file name=/oradata/orclstd2/control02.ctl Finished restore at 2022-11-16 19:03:52 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 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 Script executing command: SET NEWNAME renamed tempfile 1 to /oradata/ORCLSTD2/datafile/o1_mf_temp_%u_.tmp in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 2022-11-16 19:03:56 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orclstd channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/ORCLSTD2/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orclstd channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/ORCLSTD2/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orclstd channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/ORCLSTD2/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orclstd channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/ORCLSTD2/datafile/o1_mf_users_%u_.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 2022-11-16 19:06:46 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=9 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_system_kq9jwx0b_.dbf datafile 3 switched to datafile copy input datafile copy RECID=10 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_sysaux_kq9jz8rk_.dbf datafile 4 switched to datafile copy input datafile copy RECID=11 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_undotbs1_kq9k1omp_.dbf datafile 7 switched to datafile copy input datafile copy RECID=12 STAMP=1120936006 file name=/oradata/ORCLSTD2/datafile/o1_mf_users_kq9k23xg_.dbf contents of Memory Script: { set until scn 3192644; recover standby clone database noredo delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 2022-11-16 19:06:46 using channel ORA_AUX_DISK_1 Finished recover at 2022-11-16 19:06:46 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script released channel: ORA_DISK_1 released channel: ORA_AUX_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=43 device type=DISK specification does not match any archived log in the repository Finished 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 orclstd2 SQL> 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 ALLOWED orclstd: 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 ALLOWED orcl: 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 Mode Automatic archival Enabled Archive destination /archivelog Oldest online log sequence 54 Next log sequence to archive 56 Current log sequence 56 SQL> SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /archivelog Oldest online log sequence 57 Next log sequence to archive 59 Current log sequence 59 SQL>
orclstd2:
SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 58 1 4 ARCH CLOSING 1 56 12288 1382 ARCH CLOSING 1 57 1 2 RFS IDLE 1 0 0 0 RFS IDLE 1 59 106 1 RFS IDLE 0 0 0 0 9 rows selected. [oracle@19c-duplicate admin]$ cd /home/oracle/flashdata/ORCLSTD/archivelog/ [oracle@19c-duplicate archivelog]$ ll total 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的同步情况，可以正常同步以及应用
![PGL图学习之项目实践(UniMP算法实现论文节点分类、新冠疫苗项目实战，助力疫情)[系列九]](https://s2.51cto.com/images/blog/202211/25205105_6380ba39c9f9067267.jpg?x-oss-process=image/watermark,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=)
