ORA-12528 When Creating A Standby Database

I went through all the steps to prepare for setting up a standby database manually. Long story as to why the data guard broker is not currently being used, will be changed in the future. Sometimes during the setup dyslexia hits me and I have a typo or to despite reviewing the setup. That occurred with the INIT parameter for the log files. Not a big deal. I set aside the project until after the holidays.

Upon my return to the project I started receiving the following:
MAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/26/2012 09:47:25
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 12/26/2012 09:47:25
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-17629: Cannot connect to the remote database server

What happened? I checked the listener

lsnrctl services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.150.50.102)(PORT=1521)))
Services Summary…
Service “firlstd” has 1 instance(s).
Instance “firlstd”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:6 refused:0 state:ready
LOCAL SERVER
Service “firlstd (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2” has 1 instance(s).
Instance “firlstd (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “seclstd” has 2 instance(s).
Instance “seclstd”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Instance “seclstd”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

There’s a second database seclstd that wasn’t there when I started creating mine. Turns out one of the DBAs had started the second standby for a different production database and had modified the listener. They had placed their database under the sid_list at the top, so they hadn’t modified mine.

I tried the following:

1. lsnrctl reload — no luck

2. I checked the local_listener to ensure that it wasn’t set — not set

3. I added (UR=A) to the tnsnames to see if that would make a difference based on MOS recommendation in a note. — no luck

4. The database is in nomount, which is correct and with 11.2 you can connect to nomount via the listener.

5. There are no processes running for the database other than the background so we were not exceeding the max number of processes or connections.

6. The alert log showed nothing, which made sense as I could connect via sqlplus without going through the listener.

Had to be something within the listener.ora itself.

We weren’t using dynamic registration which is the correct way to set up the listener for a data guard configuration. So why are connections being blocked?

Upon further review the of the listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = frslstd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
)
(SID_DESC =
(SID_NAME = seclstd
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
)
)

URGH!!! There was a missing parenthesis after the sid_name in the listener.ora. How did I miss that. It would appear that if there is only one database that is a standby then dynamic registration works which is why I only experienced this issue after the second database was started.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: