So, with thanks to @YasirArsanukaev for the time he put in, I have found a solution which works, but which I can't really explain.
Riffing on the LOCAL_LISTENER thought, I was reading this other answer where it said:
The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according ...
It's signalling a 'ORA-214' during instance startup, which is really bad.
$ oerr ORA 214
00214, 00000, "control file '%s' version %s inconsistent with file '%s' version %s"
// *Cause: An inconsistent set of control files, datafiles/logfiles, and redo
// files was used.
// *Action: Use a consistant set of control files, datafiles/logfiles, and redo
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 Windows Server Failover Cluster (WSFC), and is used for routing traffic to the appropriate Availability Group (AG) node. However, it is ultimately just a DNS Name ...
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 AGs and listeners. The error you are getting is self explanatory
The DNS name may have been taken or have a conflict with existing name services, or ...
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 years without any issue.
Two suggestions for better manageability if you go to that route enterprise wide.
Write a collection of all CNAME pointing to listeners,...
lsnrctl status LISTENERNAME
... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
The description for the message you're seeing is:
Cause: Most of the listener administrative commands are only intended
to be issued by privileged users, for example DBAs or system
administrators. If the listener password is not set, then the listener
only accepts administrative requests from LSNRCTL running with the
same OS credentials, or ...
Run sqlplus / as sysdba then issue command startup; to start the database.
Also on Linux with this error, you can check first if Oracle background processes are up with ps aux | grep pmon. Pmon is process monitor, or check some other background process. If they are not running then the database is not running.
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=ReadOnly doesn't work properly.
Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag ...
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 application connection to connect to the secondary availability group.
"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 (Doc ID 208968.1)
To publicise the fact that from Oracle8i (8.1.5) onward, a
LISTENER.ORA is no longer required in order to start the Default
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 can force registration manually.
Move the file away:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>move listener.ora listener.foo
1 file(s) ...
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 this process at a high level in this blog post, however to quickly recap the point that needs to be made here:
The endpoint url is the address where the ...
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 offline, and is not designed for this kind of scenario.
Using a listener within an Availability Group is the way to go. This is a virtual network resource that ...
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-scan and find publicly accessible databases. Even if you run on a non-standard port, it's relatively trivial to discover the SQL Server instance that is internet-...
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 listener server as the listener will move with the owner of the resources in WSFC.
I do not have any clear explanation for this problem solution. However I used SID instead of service name in connection string and it worked.
Edit: A service name is more flexible than a SID would be. A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the ...
First, thanks for the response. I'm working on a Mac, and the instant client doesn't include the tnsping utility. I had tried to login, from the remote computer, and that is where I got the TNS:no listener error. Logging in on the server is fine.
I had tried the telnet test before, but as I did not have telnet installed and running on the server, that ...
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 listener when creating it through FCM/Powershell or SQL Server, the CNO is used as security context.
The official pre-stage way
Find the OU with the CNO in it
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 appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.
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:
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
(LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
The side-effect of creating a Custom Connection is that you will need to reestablish the ...
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 procedures themselves. There is some documentation in the application development guide.
The one environment variable that seems to be recognized by extproc itself is ...
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 set events='immediate trace name listener_registration level 0';
PMON registers to addresses defined in local_listener and remote_listener parameters. If you ...
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 sqlcmd's version, run it with -? parameter. Like so,
Microsoft (R) SQL Server Command Line Tool
Version 14.0.1000.169 NT
Copyright (C) 2017 ...
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 there is a cached entry. You can see what is in the cache by running ipconfig /displaydns. Once you run this you'll see the "Time To Live" property on each record....
To me it looks like you did not start the listener but it is started. Your problem seems to be the tcp connection and not the ipc. So the listener is running, you can not reach it using tcp. Test this using telnet gaurav 1521
If the telnet does not give a connection, a firewall is blocking you. Stop/edit the firewall.
There doesn't appear to be a problem.
The listener is a process that generally runs on the database server, not on the client machine. Doing a client-only install will not install a listener. If you want to have a listener on your machine, you'd need to do a database install (though you can do a software-only install rather than actually creating a ...
As @BalazsPapp mentioned, the oracle db can be connected locally even without a listener. In this case, the BEQ (Bequeath) protocol is used, instead of the tcp-based normal oracle communication.
Although on Balazs's link there is nothing about the details of the BEQ. On unix, it probably uses unix domain sockets. On windows, it uses probably some local rpc.
My sequence of steps for troubleshooting
Is the listener resolvable? C:\>tnsping MYDB (Confirms: the database listener is running on serverside)
Is there a firewall issue? C:\>telnet hostname 1521
Can I login? C:\>sqlplus system/pass@MYDB or C:\>sqlplus system/pass@hostname:1521/MYDB (Confirms: The database is open)
Finally, chances of having ...