暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【译】备用数据库中的活动副本

【译】备用数据库中的活动副本

原创 X丶 2022-06-30
411

通常我们从主数据库进行主动复制，但我认为如果我们从备用数据库进行主动复制，应该可以看到会发生什么。所以我做了一些测试

接下来是我的环境实际配置

DGMGRL> show configuration
Configuration - db21
  Protection Mode: MaxPerformance
  Members:
  DB21_SITE1 -Primarydatabase
    DB21_SITE2 - Physical standbydatabase
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 35 seconds ago)
DGMGRL>

本次测试是用Oracle 21c完成的，但我想它应该适用于19c

在只读模式下进行待机

我做的第一个测试是在只读模式下打开备用设备

如果没有Active Data Duard许可证，请在以只读模式打开数据库之前停止应用过程

oracle@oraadserver2:/home/oracle/[DB21 (CDB$ROOT)] DB21
********* dbi services Ltd. *********
STATUS                 : OPEN
DB_UNIQUE_NAME         : DB21_SITE2
OPEN_MODE              : READ ONLY
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : YES
FORCE_LOGGING          : YES
VERSION                : 21.0.0.0.0
CDB Enabled            : YES
List PDB(s)    MOUNTED : PDB1, PDB2, PDB3
List PDB(s)  READ ONLY : PDB$SEED
*************************************
oracle@oraadserver2:/home/oracle/[DB21 (CDB$ROOT)]

目标是将备用数据库DB21\u SITE2复制到远程服务器的DB21CLNE。我假设所有网络文件都已配置。密码文件、spfile也已配置。

从源服务器

oracle@oraadserver2:/home/oracle/[DB21 (CDB$ROOT)] tnsping db21_site2
TNS Ping Utility forLinux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:29:59
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL)))
OK (0 msec)
oracle@oraadserver2:/home/oracle/[DB21 (CDB$ROOT)] tnsping db21clne
TNS Ping Utility forLinux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:30:05
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE)))
OK (0 msec)
oracle@oraadserver2:/home/oracle/[DB21 (CDB$ROOT)]

从目标服务器

oracle@oraadserver4:/home/oracle/[DB21CLNE (CDB$ROOT)] tnsping db21_site2
TNS Ping Utility forLinux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:12
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL)))
OK (0 msec)
oracle@oraadserver4:/home/oracle/[DB21CLNE (CDB$ROOT)] tnsping db21clne
TNS Ping Utility forLinux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:16
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE)))
OK (10 msec)
oracle@oraadserver4:/home/oracle/[DB21CLNE (CDB$ROOT)]

然后创建一个简单的重复rman脚本

oracle@oraadserver4: [DB21CLNE (CDB$ROOT)] catduplicate_from_standby.rcv
connect target sys/******@db21_site2
connect auxiliary  sys/****@db21clne
run {
duplicate target database to DB21CLNE from active database nofilenamecheck;
}

在NOMOUNT状态下启动目标数据库DB21CLNE后，我运行脚本来创建DB21CLNE

oracle@oraadserver4: [DB21CLNE (CDB$ROOT)] nohuprman cmdfile=duplicate_from_standby.rcv log=duplicate_standby_`date+"%Y-%m-%d_%H%M%S"`.log &

几分钟后，副本恢复正常（输出被截断）

Recovery Manager: Release 21.0.0.0.0 - Production onWed Apr 6 14:51:56 2022
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle and/orits affiliates.  Allrights reserved.
RMAN> connecttarget *
2> connectauxiliary *
3> run {
4> duplicate target databasetoDB21CLNEfromactivedatabasenofilenamecheck;
5> }
6> 
connected totarget database: DB21 (DBID=1137202071)
connected toauxiliary database: DB21CLNE (notmounted)
Starting Duplicate Db at06-APR-2022 14:51:57
using target databasecontrol file insteadofrecovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK
currentlog archivedatprimarydatabase
currentlog archivedatprimarydatabase
duplicating Online logs toOracle Managed File (OMF) location
duplicating Datafiles toOracle Managed File (OMF) location
contents ofMemory Script:
{
   sql clone"alter system set  control_files = 
  ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone"alter system set  db_name = 
 ''DB21'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone"alter system set  db_unique_name = 
 ''DB21CLNE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup cloneforcenomount
   restore clonefromservice'db21_site2'primarycontrolfile;
   alterclone databasemount;
}
executing Memory Script
sql statement: altersystem setcontrol_files =   ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl''comment= ''SetbyRMAN''scope=spfile
sql statement: altersystem setdb_name =  ''DB21''comment= ''Modified byRMAN duplicate''scope=spfile
sql statement: altersystem setdb_unique_name =  ''DB21CLNE''comment= ''Modified byRMAN duplicate''scope=spfile
Oracle instance shut down
Oracle instance started
Total System GlobalArea    1577057624 bytes
Fixed Size9686360 bytes
Variable Size385875968 bytes
DatabaseBuffers             889192448 bytes
Redo Buffers                 292302848 bytes
Starting restore at06-APR-2022 14:56:46
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=333 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup setrestore
channel ORA_AUX_DISK_1: using network backup setfromservice db21_site2
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
outputfilename=/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl
outputfilename=/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl
Finished restore at06-APR-2022 14:56:49
databasemounted
…
…
Executing: alterdatabaseforcelogging
contents ofMemory Script:
{
   Alterclone databaseopenresetlogs;
}
executing Memory Script
databaseopened
Executing: alterdatabaseflashbackon
contents ofMemory Script:
{
   sql clone"alter pluggable database all open";
}
executing Memory Script
sql statement: alterpluggable databaseallopen
Finished Duplicate Db at06-APR-2022 15:02:41
Recovery Manager complete.

DB21CLNE已创建并以读写方式打开并且可以使用

oracle@oraadserver4:/u01/app/oracle/admin/DB21CLNE/create/ [DB21CLNE (CDB$ROOT)] DB21CLNE
********* dbi services Ltd. *********
STATUS                 : OPEN
DB_UNIQUE_NAME         : DB21CLNE
OPEN_MODE              : READWRITE
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PRIMARY
FLASHBACK_ON           : YES
FORCE_LOGGING          : YES
VERSION                : 21.0.0.0.0
CDB Enabled            : YES
List PDB(s)  READONLY: PDB$SEED
List PDB(s) READWRITE : PDB1, PDB2, PDB3
*************************************

装载模式下待机

我决定执行相同的测试，但源备用数据库处于装载状态

oracle@oraadserver2:/home/oracle/ [DB21 (CDB$ROOT)] DB21
********* dbi services Ltd. *********
STATUS                 : MOUNTED
DB_UNIQUE_NAME         : DB21_SITE2
OPEN_MODE              : MOUNTED
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : YES
FORCE_LOGGING          : YES
CDB Enabled            : YES
List PDB(s)    MOUNTED : PDB$SEED, PDB1, PDB2, PDB3
*************************************
oracle@oraadserver2:/home/oracle/ [DB21 (CDB$ROOT)]

在这种情况下，恢复过程可以正常启动，但恢复过程要求存档，因此无法完成。没有返回错误，但副本正在询问，询问丢失的存档

RMAN> connecttarget *
2> connectauxiliary *
3> run {
4> duplicate target databasetoDB21CLNEfromactivedatabasenofilenamecheck;
5> }
6>
connected totarget database: DB21 (DBID=1137202071, notopen)
connected toauxiliary database: DB21CLNE (notmounted)
Starting Duplicate Db at06-APR-2022 14:12:28
using target databasecontrol file insteadofrecovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
currentlog archivedatprimarydatabase
currentlog archivedatprimarydatabase
duplicating Online logs toOracle Managed File (OMF) location
duplicating Datafiles toOracle Managed File (OMF) location
contents ofMemory Script:
{
   sql clone"alter system set  control_files =
  ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v15kz7_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v15l04_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone"alter system set  db_name =
 ''DB21'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone"alter system set  db_unique_name =
 ''DB21CLNE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup cloneforcenomount
   restore clonefromservice'db21_site2'primarycontrolfile;
   alterclone databasemount;
}
executing Memory Script
…
…
starting media recovery
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
...
...
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence=35
….

作为备用服务器的解决方法，我手动将请求的存档复制到远程源服务器

oracle@oraadserver2 ] scp o1_mf_1_35_k4v156k8_.arc oraadserver4:/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06

复制过程进展顺利

…
…
contents ofMemory Script:
{
   Alterclone databaseopenresetlogs;
}
executing Memory Script
databaseopened
Executing: alterdatabaseflashbackon
contents ofMemory Script:
{
   sql clone"alter pluggable database all open";
}
executing Memory Script
sql statement: alterpluggable databaseallopen
Finished Duplicate Db at06-APR-2022 14:34:39
Recovery Manager complete.

所以我们可以看到，可以从备用数据库（如主数据库）中进行复制。在装载备用设备时，您可能需要手动复制一些已存档的文件。

请注意，我在装载数据库时第二次重复了复制操作，并且我有相同的行为，我必须手动复制丢失的存档日志。

墨力翻译
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论