Generic DBA
Listener related questions:-
Answer Credit:-
//www.linkedin.com/in/saibal-ghosh-ccsk-prince2-%C2%AE-469b0a7/
1.Can we manually force a particular scan ip (out of the three) to relocate on a particular host (lets say – on node 5 of 5 node RAC) ?
Ans:-
srvctl relocate scan_LISTENER -i 1 -n node1
$ srvctl relocate scan -h
Usage: srvctl relocate scan -i <ordinal_number> [-n <node_name>]
-i <scan_ordinal_number> Ordinal number of IP address
-n <node_name> SCAN VIP node name
-h Print usage
2.Does scan actually performs connection load balancing?
Ans:-
Yes, it does. The LREG process of each instance registers the database service with the local listener as well as all the SCAN listeners (typically they are specified by the parameter: REMOTE_LISTENER). When a connection request gets routed to a particular SCAN listener, then the SCAN listener does a quick calculation to find out the least loaded node, and once it gets that information, it sends the address of the local listener on that node back to the client to establish the connection. That is how load balancing is done by the SCAN listeners.
For clients connecting using SCAN, three IP addresses will be received by the client by resolving the SCAN name through DNS as discussed. The client will then go through the list it receives from the DNS and try connecting through one of the IPs received. If the client receives an error, it will try the other addresses before returning an error to the user or application. This is similar to how client connection failover works in previous releases when an address list is provided in the client connection string.
When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance providing the requested service. It will then re-direct the connection request to the local listener on the node where the least loaded instance is running. Subsequently, the client will
be given the address of the local listener. The local listener will finally create the connection to the database instance.
//www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
3.Is having odd number of Scan listener a recommended practice?
Ans:-
No, it’s not a recommended practice. The only thing Oracle recommends is that the SCAN resolves to three VIP addresses, to provide high availability and scalability . If the SCAN resolves to three addresses, then three SCAN VIPs and three SCAN listeners are created. Each SCAN listener depends on its corresponding SCAN VIP. The SCAN listeners cannot start until the SCAN VIP is available on a node.
Normally 3 SCANs are capable enough to handle hundreds of new connections in a span of few seconds.
Still if you feel that default 3 SCAN listeners are not enough for your environment then you have an option to add few more scan listeners.
GNS based dynamic IP scheme still has no way to change number of SCAN listeners in your environment.
4.What is listener and why we used it?
Ans:-
Listener is a process that resides on the server whose responsibility is to listen for incoming client connection requests and manage the traffic to the server.Every time a client requests a network session with a server, a listener receives the actual request. If the client information matches the listener information, then the listener grants a connection to the server.
listener.ora file
//www.toadworld.com/platforms/oracle/b/weblog/archive/2013/08/14/the-oracle-listener-process-how-does-it-work-connecting-to-oracle
5.Difference between local listener and remote listener?
LOCAL_LISTENER on each node should point to the listener on that node. REMOTE_LISTENER should point to all listeners on all nodes if you want server side load balancing, otherwise don’t set REMOTE_LISTENER.
the purpose of REMOTE_LISTENER is to connect all instances with all listeners so the instances can propagate their load balance advisories to all listeners. if you connect to a listener, this listener uses the advisories to decide who should service your connect. if the listener decides its local instance(s) are least loaded and should service your connect it passes your connect to the local instance. if the node you connected to is overloaded,
the listener can use TNS redirect to redirect your connect a less loaded instance.
//docs.oracle.com/en/database/oracle/oracle-database/12.2/rilin/about-listener-configuration-for-an-oracle-rac-database.html#GUID-41979EBD-F3FD-4FFD-87B5-F3A33DE64397
//dbaoracledba.blogspot.in/2012/04/remote-listener-vs-local-listener.html
6.Difference between static and dynamic listeners
7.What is listener poisoning?Ans:-
The TNS Poison attack was first described in 2015 as a type of man-in-the-middle attack. Legitimate user session are hijacked and the routed to an attackers server.
Note: If you set set dynamic_registration_listener=
The Internet posts note that the TNS poison attack will be fixed in Oracle 12c but there are several workarounds for this alleged vulnerability.
These notes describe the TNS poison attack and the current workarounds:
Switch off dynamic registration:
switch off dynamic registration by setting dynamic_registration_LISTENER_
Using Class of Secure Transport on single inctance databases
Oracle recommend to set class of secure transportation to restrict instance registration to the local system. This parameter is available since Oracle 10.2.0.3 and can be implemented according to MOS Note 1453883.1
Using Class of Secure Transport in Oracle RAC
For RAC the use of COST is a bit more complex and require to configure SSL/TCPS. This is as well only possible for Oracle 10.2.0.3 and newer. It can be implemented according to MOS Note 1340831.1
Limit Network Access
Start using valid node checking to limit access to listener to certain IP addresses.
TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODE = (Comma separated list OF ALL valid, clients)
Limit Network Access on the network
As an alternative limit network access to certain listener on the network layer e.g. network segmentation, firewalls etc.
8.How to secure listener file with password
9.How to setup listeners if firewall is enabled
10.Each node have its own listener as VIP n 3 scan listener?
Ans:-
No, each node has its own local listener and there are three SCAN listeners. The three SCAN listeners may reside on any node literally. In a two node RAC cluster, one node may host one SCAN listener, while the other node may host the other two SCAN listeners, however, in a four node RAC cluster (say), the SCAN listeners may be bunched in two nodes, while the two other nodes may not be hosting any SCAN listener.
11.How scan listener decides that which node is less loaded, from where it gets the load information?
Ans:-
The Listener Registration process (LREG) provides information such as the service name, instance names, and workload information to the listeners. This feature is called service registration. That is how workload related information is transmitted to the SCAN listener.
12.Is it prerequisites to disable firewall if listeners need to work
13.Does dynamic registration can lead to security risk?
14.Should listener run on Default port 1521 or non default port. Which one is the best practice?
15.How to find out how many connections a listener refused vs how many serviced?
16.MTS on standalone database
17.What happens if listener log file grows
18.Why pmon starts first during bringing up a database?
19.How many SCAN IP need for 8 node RAC?
20.what is rate_limit ?
Ans:-
Connection rate limiter feature in the Oracle Net Listener enables to limit the number of new connections handled by the listener.
When this feature is enabled, Oracle Net Listener imposes a user-specified maximum limit on the number of new connections handled by the listener every second.
21.In which circumstance rate_limit will be implemented what are pros and cons?
Ans:-
We can set the rate_limit
Pros
=====
a) Prevent denial-of-service attacks by limiting the number of incoming connections.
b) Prevent your database being flooded by new requests like malicious attack./bugs or bad coding practices / “logon storms”
Cons
=====
Slow connect time and little tnsping responce time high.
22.What is benefit of LREG process?
The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net Listener.
When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.
23.If local listener goes down , then will the node get evicted?? As it’s not getting any connections…
In my point of view there are 2 types of cause for node evict:-network heart beat and voting disk heartbeat..Network heart beat depends on UDP/RDS ping through private interconnect..So if we have 3 nodes, and 1 node can not ping other 2,needs to evict..whereas listener works on TCP/IP protocal..so there should not be any impact of cluster node eviction.Also We need to note listener is only configured on public IP not in private ip.
24.If we have multiple version Oracle homes in 1 particular server , what should be approach to having a listener in that server?
25.What happens to connections established when listener is reatarted/reloaded?
Generally speaking, restart will terminate the service in question and restart it; reload will only reload the configuration file.
Since reload only reloads the config, it will cause less disruption to existing activities and currently open connections; users might not even notice that it was run. However, depending on what server we’re talking about, some options may not be changeable using reload; and if the server is using too much memory, etc, it may be necessary to use restart to force it to start from a clean slate.
Further, note that not all services support reload; all services support restart.
26.What is difference between session and connection?
27.My client has a 4-node RAC. Whenever the scan listener is relocated to Node-2, none of the services are getting registered. How to resolve the issue?
28.whats is COST (CLASS OF SECURE TRANSPORT)
30.How to start listener automatically on server reboot both in RAC & stand alone ?
Ans:-
Standalone : Configure the listener with oracle restart which will start the listener automatically after server reboot.
RAC : As per Note:105957.1 and Note 1397813.1,
When using an 11.2 ASM or CRS managed database (using the 11.2 Grid Infrastructure), the startup/shutdown script may no longer be used.
CRS manages automatic restarts/startups for RAC and single instance databases are managed with Oracle Restart.
31.How many methods we can configure listener file?
32.How configure listener of pdb ?
33.How can we manage listener remotely ?
34.How to make listener secure to avoid above ?
Ans:-
Add IP Restrictions / Enable Valid Node Checking
Specify Connection Timeout
Specify Class of Secure Transport for Dynamic Registration
Enable Encryption of Network Traffic
35.how to resolve Listener status BLOCKED?
Ans:-
The listener service handler for an instance that is in either nomount,mount or restricted mode will be blocked or restricted,
for connections for any non-privileged account by listener service.
use (UR=A) clause in TNSNAMES.ora – This clause is intended to work a dynamically register handler so the use of SERVICE_NAME versus SID is preferred.
36.How can I add 2nd scan listener.
37.how does listener work in shared and dedicated servers?
38.How does services or dB name or instance name get register in the listener….which process does that?
39.How do we change the scan port name if you have entered wrong port number during grid installation?
40.Is SCAN mandatory in 11g R2?
Tablsepace related questions:-
1) What is a Tablespace?
A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.
2) Why use materialized view instead of a table?
Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.
3) How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;
4) What is the use/size of temporary tablespace?
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables
5 )What is the difference between dictionary managed tablespace and locally managed tablespace?
In dictionary managed tablespace, free block information is maintained in data dictionary cache there by increases IO. In locally managed tablespace, the same information is maintained in datafile header itself there by reducing the IO. Hence LMT is better than DMT
6) How to change the segment management type for an existing tablespace?
Once defined, we cannot change segment space management for any tablespace
7) What happens when you take a tablespace/datafile offline immediate?
Any existing transactions will be stopped and datafiles of this tablespace need recovery when we make them online
8) How to identify which datafiles are modified today?
By looking at the timestamp of the files at OS level
9) What is the new feature of 11g tablespace management?
10) What is bigfile tablespace and its use?
It gives easy managebility for VLDB by providing terabytes size to a single datafile itself.
11) How will do capacity planning for your production databases?
We will take every quarter for normal databases and every week for critical and fast growing databases
12) What is OMF? What are its advantages and disadvantages?
It helps in managing files by oracle automatically. But naming convention will be the problem
13) How you will get timezone of database?
NLS_TIMESTAMP_TZ_FORMAT
14) How will you find out the current users who are using temporary tablespace segments?
By checking in v$tempseg_usage
15) How to drop a datafile without dropping a tablespace?
SQL> alter database datafile ‘path’ offline drop;
16) How to check SCN of the database?
SQL> select current_scn from v$database;
17) What is the relation between db_files and maxdatafiles?
Both will specify how many max datafiles can be there in the database
18) How do you add a datafile to a tablespace?
You can add a datafile to a tablespace by using the ALTER TABLESPACE ADD DATAFILE SIZE; statement.
19) What are the advantages of using locally managed tablespace?
Reduced contention on data dictionary tables
No rollback generated
No coalescing required
Reduced recursive space management
20) What view would you use to determine free space in a tablespace?
The DBA_FREE_SPACE view can be used to determine free space in a tablespace.
