10 votes
Accepted

Cannot connect database as sys as sysdba using sqlplus

If the password contains special characters such as @, enter the password between double quotes: $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 11 15:41:40 2019 Version ...
Balazs Papp's user avatar
  • 40.7k
6 votes

Listener listening without listener.ora

You don't need a listener.ora. The listener will happily start without one, but will just start without supporting any services. pmon will periodically register databases with the listener, but you ...
Philᵀᴹ's user avatar
  • 31.8k
5 votes
Accepted

Distributed Availability groups & Listeners

After a call with someone from Microsoft, I was told that there are no listeners on the Distributed availability level yet. So currently if we want to use DAG, you will have to change your DNS or ...
Stijn Wynants's user avatar
5 votes

Always On Availability Group, Always redirect user to read only instance

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=...
Cody Konior's user avatar
  • 3,508
5 votes
Accepted

Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default

The Availability Group Listener is (per the documentation), simply "a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses." It becomes a resource in the ...
AMtwo's user avatar
  • 16.1k
5 votes

Reaching SQL Server AlwaysOn HA listener from the Internet

It's generally considered a really bad idea to make your database accessible from the internet. Making the database accessible from the public internet is a HUGE security risk. It's easy to port-...
AMtwo's user avatar
  • 16.1k
5 votes

SQL Server Standard Basic Availability Group - only 10 Listeners limit?

The limitation is only number of replicas. Limit of two replicas (primary and secondary). he was able to set up 12 basic availability groups. He was able to set up 10 listeners You can create many ...
Kin Shah's user avatar
  • 62.2k
5 votes
Accepted

SQLServer - Detect who is connecting via an AG listener

[…] is there any way to verify this from the SQL side once they are connected? Unfortunately there is not, most of the client side connection string settings are not persisted or sent across the wire ...
Sean Gallardy's user avatar
5 votes
Accepted

CNAME alias for AlwaysOn Listener

Yes you can create CNAME pointing to listener name. I have been using the same for over 100 Availability group. SQL Server version 2012, 2014, 2016 and 2017. I have been using this for close to 3 ...
SqlWorldWide's user avatar
  • 13.2k
4 votes
Accepted

Listener listening without listener.ora

"As far as I know, for the listener to work, I have to have listener.ora file." Starting with 8.1.5, listener.ora is optional for a default listener. Starting the Listener without a LISTENER.ORA (...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

AG listener issues with ApplicationIntent=ReadOnly

The reason that this fails outside of the internal Rackspace location is due to the URL Endpoints being set to a value that is not able to be connected through from your local environment. I discuss ...
Sean Gallardy's user avatar
4 votes
Accepted

Do I need an Availability Group Listener if I have "Failover Partner" in my connection string?

Failover partner is for when you are using database mirroring, you should not use it for Availability Groups. While it, strictly speaking, will work, it will only do so when the primary server is ...
Nic's user avatar
  • 4,013
4 votes

Availability Group Listener - Dedicated Server?

The listener is part of the Availability Group and will reside in the resource group of the Availability Group in the Windows Server Failover Cluster. Thus it is not possible to have a specific ...
Sean Gallardy's user avatar
4 votes

nslookup returning secondary IP of a listener (different subnet)

If you’re using the default cluster settings, RegisterAllProvidersIP = 1, SQL Server will register all listener IP address in DNS, not just the active one. As such, DNS will return all registered IP ...
Brendan McCaffrey's user avatar
3 votes
Accepted

Configure SQL Server to associate a user login with a specific port

See the instructions at: Configure the Database Engine to Listen on Multiple TCP Ports To set up a new TDS endpoint a code sample is: USE master; GO CREATE ENDPOINT [CustomConnection] STATE = ...
RLF's user avatar
  • 14k
3 votes

AlwaysON - Force users connection to use Readable Secondary Nodes

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the ...
Sean Gallardy's user avatar
3 votes
Accepted

Setting up a listener for sql server alwayson availability groups without computer object rights in the AD

There is one proper way to pre-stage the listener and one way to allow the cluster to create the listener itself. Please note that YOUR account is not what is used to authorize to AD to create the ...
Sean Gallardy's user avatar
3 votes
Accepted

AlwaysOn AG with and without listener

However, one set has a listener created and the other doesn't. Well that doesn't help you application stay available! You can absolutely have an availability group without a listener, but the useful ...
Sean Gallardy's user avatar
3 votes

What is an example of an extproc.ora file?

Why do you expect that "there must be more to it"? extproc.ora simply sets environment variables for the extproc daemon, which is used to execute code for external stored procedures, and for ...
mustaccio's user avatar
  • 26k
3 votes
Accepted

How can I troubleshoot issues with dynamic registration?

Listener registration in the database can be traced with: alter system set events='immediate trace name listener_registration level 3'; Disable tracing when it is not needed anymore: alter system ...
Balazs Papp's user avatar
  • 40.7k
3 votes

Sqlcmd: 'KReadOnly': Unknown Option

One needs to use recent enough a version of sqlcmd. The parameter for application intent is -K. Note that sqlcmd's parameters are case sensitive, which is uncommon in Windows environment. To check ...
vonPryz's user avatar
  • 3,071
3 votes
Accepted

Listener IP not changing after failover

This behavior occurs due to the time to live (TTL) on the DNS records. A client does not do a DNS lookup every time it attempts to connect to a server. It first looks in its DNS cache to see if ...
Tony Hinkle's user avatar
  • 7,939
3 votes

oracle - Change host to 0.0.0.0 in listener

You have to change the files at the following locations: $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/tnsnames.ora On both these files you have to update the HOST attribute to ...
user9492428's user avatar
3 votes
Accepted

What is the correct configuration for the SQL Server listener with 2 nodes on-prem and 1 node in Azure?

For the listener connection to work in a DR scenario do I need to follow the instructions for creating the listener on the WSFC here instead? Since you only have a single node in Azure and that must ...
Sean Gallardy's user avatar
2 votes

ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

To understand the problem and try to fix it you need to check related logs: listener.log, alert log, trace file, etc possible causes and solutions: listener problem: Make sure there is no error ...
Ahmad Abuhasna's user avatar
2 votes

ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

I has this problem in Windows (with oracle 11g) because the user under which the listener service was running was not part of the administrators group... once added to the administrator group all was ...
gcr's user avatar
  • 31
2 votes

Always On Availability Group, Always redirect user to read only instance

Ste, what you are wanting is a point and click solution or a setting somewhere. Unfortunately, none of these currently exist in "off the shelf" form. It would be realllly nice if Microsoft included ...
Steve Mangiameli's user avatar
2 votes

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor on linux

Since you have the following error message that mean either hostname is not correct and/or the domain you are using. How to know if everything is working fine or not? You need to execute hostname ...
Ahmad Abuhasna's user avatar
2 votes

Oracle database not responding

I had a similar issue with an off the shelf three tier application where the app server would make a connection to the database when the user logged on. The user activity was typically to log on in ...
kevinskio's user avatar
  • 4,252
2 votes

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor on linux

Below process is solved my problem. Connect to server using sqlplus / as sysdba command check whether local listener exists or not by using SQL> show parameter local_listener command. For me ...
gprasadr8's user avatar
  • 121

Only top scored, non community-wiki answers of a minimum length are eligible