请注意,本文编写于 582 天前,最后修改于 582 天前,其中某些信息可能已经过时。
问题现象:
近期公司做数据库冗余测试,停掉RAC其中一个节点后,发现本地自动备份和清理归档的脚本执行失败。
脚本类似如下:
run {
allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
******
}
报错如下:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel ch1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
原因分析:
经测试,此多通道写法无法实现故障转移。
测试过程如下:
[oracle@rac2 cjc]$ rman target /
RMAN> run {
allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
show all;
}2> 3> 4> 5>
allocated channel: ch1
channel ch1: SID=716 instance=oradb1 device type=DISK
allocated channel: ch2
channel ch2: SID=730 instance=oradb2 device type=DISK
RMAN configuration parameters for database with db_unique_name ORADB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/db/product/19.0.0/dbs/snapcf_oradb1.f'; # default
released channel: ch1
released channel: ch2
停掉节点1实例后:
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 9 18:51:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shutdown immediate
再次连接rman,报错
oracle@rac2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun May 9 18:56:39 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2810081861)
RMAN> run {
allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
show all;
}2> 3> 4> 5>
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel ch1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
是否和通道顺序有关呢?将ch2写到前面,测试报错依旧,看来和顺序无关
RMAN> run {
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
show all;
}2> 3> 4> 5>
allocated channel: ch2
channel ch2: SID=25 instance=oradb2 device type=DISK
released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel ch1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
把停掉的节点通道注释掉以后可以正常执行命令
run {
#allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
show all;
}
[oracle@rac2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun May 9 18:59:03 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2810081861)
RMAN> run {
#allocate channel ch1 type disk connect sys/oracle@rac1:1521/oradb;
allocate channel ch2 type disk connect sys/oracle@rac2:1521/oradb;
show all;
}2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: ch2
channel ch2: SID=479 instance=oradb2 device type=DISK
RMAN configuration parameters for database with db_unique_name ORADB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/db/product/19.0.0/dbs/snapcf_oradb2.f'; # default
released channel: ch2
如何在RAC两个节点都保持正常的情况下,可以使用多个节点并发通道提高速度,在某个节点出现故障,通道自动转移到另一个节点呢?
可以尝试使用10g连接RAC的指定多个vip的方式。
将如下内容加入到tnsnames.ora文件中。
vip1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
#(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
vip2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
#(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
测试在不使用LOAD_BALANCE = yes情况下,优先连接第一个地址,当第一个地址连不通了,会自动去连接第二个地址,实现故障转移。
[oracle@rac2 admin]$ sqlplus system/oracle@vip1
show parameter name
[oracle@rac2 admin]$ sqlplus system/oracle@vip2
show parameter name
修改RMAN脚本
run {
allocate channel ch1 type disk connect sys/oracle@vip1;
allocate channel ch2 type disk connect sys/oracle@vip2;
show all;
release channel ch1;
release channel ch2;
}
在节点1实例不启动的情况下,可以正常执行命令了。
[oracle@rac2 admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun May 9 19:36:14 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2810081861)
RMAN> run {
allocate channel ch1 type disk connect sys/oracle@vip1;
allocate channel ch2 type disk connect sys/oracle@vip2;
show all;
release channel ch1;
release channel ch2;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=717 instance=oradb1 device type=DISK
allocated channel: ch2
channel ch2: SID=489 instance=oradb2 device type=DISK
RMAN configuration parameters for database with db_unique_name ORADB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/db/product/19.0.0/dbs/snapcf_oradb2.f'; # default
released channel: ch1
released channel: ch2
作者:chenoracle