RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-12528: TNS:LISTENER: ALL APPROPRIATE INSTANCES ARE BLOCKING NEW CONNECTIONS
This can happen during a duplicate database, for example:
rman target sys/test123@PROD auxiliary sys/test123@TEST
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 12:02:57 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=835787506)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
How we know, when we are duplicating a database, firstly, we start the new duplicated database (in my case, the new standby) in nomount status (because we don’t have a controlfile yet), and so, we start the duplicate operation (where the control file is restored, and thus the database is shut down and started in mount state in order to start the database restoration).
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 13:30:44 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2253344 bytes
Variable Size 553651680 bytes
Database Buffers 905969664 bytes
Redo Buffers 7917568 bytes
SQL> exit
But, when the instance is in nomount status, the instance stay blocked. We can see it in lsnrctl status output.
oracle@Test.nizamappsdba.blogspot.com>: lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-FEB-2022 13:18:25
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.nizamappsdba.blogspot.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 08-FEB-2022 16:19:22
Uptime 0 days 20 hr. 59 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle_base/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle_base/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.nizamappsdba.blogspot.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.nizamappsdba.blogspot.com)(PORT=5500))(Security=(my_wallet_directory=/oracle_base/app/oracle/product/19.3.0/dbhome_1/admin/test/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "TEST" has 1 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Service "test.nizamappsdba.blogspot.com" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
Service "testXDB.nizamappsdba.blogspot.com" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
The command completed successfully
Case 1:
Check if LOCAL_LISTENER is properly configured in standby instance
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.
the values must be:
(ADDRESS = (PROTOCOL=TCP)(HOST=the_hostname_or_ip)(PORT=listener_port_here))
to configure:
alter system set local_listener = '(ADDRESS = (PROTOCOL=TCP)(HOST=hostname_or_ip)(PORT=listener_port))';
Case 2:
Make sure the parameters REMOTE_LOGIN_PASSWORDFILE is set as EXCLUSIVE and REMOTE_OS_AUTHENT = FALSE
to configure:
alter system set REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE' scope = spfile;
alter system set REMOTE_OS_AUTHENT = FALSE scope = spfile;
After, restart the instance.
Case 3:
Make sure you have a static listener in the new server (I mean, where will be the new standby). Also, this is very important because the duplicate stop and start the new instance remotely, start of the instance remotely is just possible when using static listener
You can use the following example in the listener.ora file. Just change the value with your environment values.
DON’T FORGOT! if your standby will be a RAC environment, you need to use the VIP address of one the servers where the instance is, not the SCAN. The restore must be made just with one instance started, after the restore you can configure (add) the another instance manually.
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.nizamappsdba.blogspot.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = TEST)
(ORACLE_HOME = /oracle_base/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = test)
)
)
NOTE: GLOBAL_DBNAME = service name
I am using SID_LIST_LISTENER because my listener name is just LISTENER. The correct is SID_LIST_YOUR_LISTENER_NAME. Also, I like to use SID_LIST_LISTENER_NAME because I can create more services (a list of fixed services). It can use multiples $ORACLE_HOME.
For example, if my server has three $ORACLE_HOME, 11.2, 12.2 and 19.3 and I am using the listener.ora in the grid_home. Could be something like it:
SID_LIST_LISTENER_NAME =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = service_one)
(ORACLE_HOME = /oracle_base/app/oracle/product/19.3.0/dbhome_1 )
(SID_NAME = inst_1)
)
(SID_DESC =
(GLOBAL_DBNAME = service_two)
(ORACLE_HOME = /oracle_base/app/oracle/product/19.3.0/dbhome_1 )
(SID_NAME = inst_2)
)
(SID_DESC =
(SID_NAME = service_three)
(ORACLE_HOME = /oracle_base/app/oracle/product/19.3.0/dbhome_1 )
(SID_NAME = inst_3)
)
)
My environment is an oracle restart, so I will use srvctl to restart the listener
srvctl stop listener
srvctl start listener
or, (without grid software) you can use it:
lsnrctl stop
lsnrctl start
lsnrctl status
oracle@Test.nizamappsdba.blogspot.com>: lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-FEB-2022 13:18:25
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.nizamappsdba.blogspot.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 08-FEB-2022 16:19:22
Uptime 0 days 20 hr. 59 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle_base/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle_base/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.nizamappsdba.blogspot.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.nizamappsdba.blogspot.com)(PORT=5500))(Security=(my_wallet_directory=/oracle_base/app/oracle/product/19.3.0/dbhome_1/admin/test/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "TEST" has 1 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Service "test.nizamappsdba.blogspot.com" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
Service "testXDB.nizamappsdba.blogspot.com" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
The command completed successfully
Now, we can connect
We still have the blocked instance, but now, the service knows it can forward the requests of connection through the service to the fixed instance in the listener.ora. Also, the UNKNOWN status is normal, is fixed, not registered dynamically like the first instance, the listener don’t know the real status. The instance can be shutdown or open, and it will remains UNKNOWN.
rman target sys/test123@PROD auxiliary sys/test123@TEST
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 12:02:57 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=835787506)
connected to auxiliary database: TEST (not mounted)
RMAN>
Other tip: don’t forget to copy or create a new password file with the same password of the duplicated database