On this post we are going to review possible errors we can face while creating a Standby DB with RMAN using an active DB.
These are just errors I faced. For some I skipped steps on purpose so we can see the error we would face. You might face these, or others ( or none…)
1:- RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
This error is generated because STATIC REGISTRATION on listener was not done. To fix this, we need to add the following on listener.ora:
[oracle@standbyracnode ~]$ ls -tlr $ORACLE_HOME/network/admin/listener.ora
-rw-r--r--. 1 oracle oinstall 1470 Aug 13 17:47 /u01/app/19c/grid/network/admin/listener.ora
[oracle@standbyracnode ~]$
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTSTB)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1/)
(SID_NAME = TESTSTB)
)
)
2:- RMAN-04006: error from auxiliary database: ORA-12537: TNS:connection closed
This error was generated by missing the (UR = A) on tnsnames, so fix, we need to add:
TEST19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RACnode-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test19c)
(UR = A) <<----------------
)
)
TESTSTB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyracnode)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = teststb)
(UR = A) <<----------------
)
)
3:- RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied
RMAN> connect target sys/Pas5w0rd@TEST19C
connected to target database: TEST19C (DBID=2463024481)
RMAN> connect auxiliary sys/Pas5w0rd@TESTSTB
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied
RMAN>
This error is because password file was not copied to standby node, to fix:
[oracle@RACnode1 ~]$ srvctl config db -d test19c | grep -i pass
Password file: +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357
[oracle@RACnode1 ~]$
ASMCMD> cp +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357 /tmp/TEST19C_pass
copying +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357 -> /tmp/TEST19C_pass
ASMCMD>
[oracle@RACnode1 ~]$ scp -p /tmp/TEST19C_pass standbyracnode:/u01/app/oracle/product/19c/db_1/dbs/orapwTESTSTB
oracle@standbyracnode's password:
TEST19C_pass 100% 2048 146.1KB/s 00:00
[oracle@RACnode1 ~]$
4:- RMAN-04017: startup error description: ORA-00439: feature not enabled: Real Application Clusters
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/30/2021 23:32:44
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-01078: failure in processing system parameters
RMAN-04017: startup error description: ORA-00439: feature not enabled: Real Application Clusters
This error is because primary is a 2 node RAC and standby is a single node. To fix this, we need to add “set CLUSTER_DATABASE=’FALSE'” in our run block:
)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel a1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'TEST19C','TESTSTB'
set db_unique_name='TESTSTB'
set db_name="TEST19C"
set CLUSTER_DATABASE='FALSE'
set control_files='+DATA/TESTSTB/CONTROLFILE/control01.ctl','+RECO/TESTSTB/CONTROLFILE/control02.ctl'
set db_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set log_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(TEST19C,TESTSTB)'
set db_recovery_file_dest='+RECO';
release channel p1;
release channel p2;
}
5:- ORA-17629: Cannot connect to the remote database server
Starting backup at 30-OCT-21
RMAN-03009: failure of backup command on p1 channel at 10/30/2021 23:44:42
ORA-17627: ORA-12541: TNS:no listener
ORA-17629: Cannot connect to the remote database server
continuing other job steps, job failed will not be re-run
released channel: p1
released channel: p2
released channel: a1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/30/2021 23:44:43
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on p2 channel at 10/30/2021 23:44:42
ORA-17627: ORA-12541: TNS:no listener
ORA-17629: Cannot connect to the remote database server
RMAN>
This error is because we could not do tnsping from all primary nodes and this was due to a missing IP on node 2 on /etc/hosts (ping was resolving with other ip):
[oracle@RACnode2 ~]$ tnsping TESTSTB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 31-OCT-2021 00:04:13
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbyracnode)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = teststb) (UR = A)))
TNS-12541: TNS:no listener
[oracle@RACnode2 ~]$
[oracle@RACnode1 ~]$ ping standbyracnode
PING standbyracnode.localdomain (192.168.24.3) 56(84) bytes of data.
64 bytes from standbyracnode.localdomain (192.168.24.3): icmp_seq=1 ttl=64 time=1.22 ms
64 bytes from standbyracnode.localdomain (192.168.24.3): icmp_seq=2 ttl=64 time=0.800 ms
^C
--- standbyracnode.localdomain ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 0.800/1.014/1.229/0.216 ms
[oracle@RACnode1 ~]$
[oracle@RACnode2 ~]$ ping standbyracnode
PING standbyracnode (192.168.56.105) 56(84) bytes of data.
64 bytes from standbyracnode (192.168.56.105): icmp_seq=1 ttl=64 time=0.996 ms
64 bytes from standbyracnode (192.168.56.105): icmp_seq=2 ttl=64 time=1.72 ms
^C
--- standbyracnode ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 0.996/1.358/1.720/0.362 ms
[oracle@RACnode2 ~]$
[oracle@RACnode1 ~]$ grep standbyracnode /etc/hosts
192.168.24.3 standbyracnode.localdomain standbyracnode
192.168.24.44 standbyracnode-scan.localdomain standbyracnode-scan
192.168.24.45 standbyracnode-scan.localdomain standbyracnode-scan
192.168.24.46 standbyracnode-scan.localdomain standbyracnode-scan
[oracle@RACnode1 ~]$
[oracle@RACnode2 ~]$ grep standbyracnode /etc/hosts
[oracle@RACnode2 ~]$
Issue was fixed adding the IP’s for the standbynode on node 2.
6:- RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/31/2021 00:19:49
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
RMAN>
Path did exist however script was using the primary path, so we need to add on our run block:
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel a1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'TEST19C','TESTSTB'
set db_unique_name='TESTSTB'
set db_name="TEST19C"
set CLUSTER_DATABASE='FALSE'
set control_files='+DATA/TESTSTB/CONTROLFILE/control01.ctl','+RECO/TESTSTB/CONTROLFILE/control02.ctl'
set db_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set log_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(TEST19C,TESTSTB)'
set db_recovery_file_dest='+RECO'
set audit_file_dest='/u01/app/oracle/admin/teststb/adump';
release channel p1;
release channel p2;
}
Comments
wow, great, after searching for 30 minutes the last line helped me: “Path did exist however script was using the primary path, so we need to add on our run block:”