问题描述：主备两个库不在同一个机房，此时想从这一套库中在复制一套可读可写的新库出来。网络带宽要求比较高，需要从备库中使用备份在起一个新库，也要测试下使用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的同步情况，可以正常同步以及应用
标签：主库,datafile,备库,log,orclstd2,duplicate,DISK,channel,ORA From： https://www.cnblogs.com/houzhiheng/p/16978066.html