Oracle Interview questions - Part 2 (RAC)

What is a VIP in RAC use for?
The VIP is an alternate Virtual IP address assigned to each node in a cluster. During a node failure the VIP of the failed node moves to the surviving node and relays to the application that the node has gone down. Without VIP, the application will wait for TCP timeout and then find out that the session is no longer live due to the failure.


How do we know which database instances are part of a RAC cluster?
You can query the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster


What is a rolling upgrade?
A patch is considered a rolling if it is can be applied to the cluster binaries without having to shutting down the database in a RAC environment


What is cache fusion?
Ans:
Transferring of data between RAC instances by using private network. Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster


What is the purpose of Private Interconnect?
Ans:
Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the clustered node


What is OCR file?
Ans:
RAC configuration information repository that manages information about the cluster node list and instance-to-node mapping information.


How do you identify the OCR file location?
Ans:
check /var/opt/oracle/ocr.loc or /etc/ocr.loc
# ocrcheck


What is Voting file/disk and how many files should be there?
Voting disk is akin to the quorum disk, which helps to avoid the split-brain syndrome. Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures
Number of voting files must be odd i.e. 1, 3, 5, 7, 9


How do I identify the voting disk location?
Ans:
# crsctl query css votedisk


What is GNS?
Grid Naming service is alternative service to DNS , which will act as a sub domain in your DNS but managed by Oracle, with GNS the connection is routed to the cluster IP and manages internally

What is FAN?
Ans:
Applications can use Fast Application Notification (FAN) to enable rapid failure detection,


What is TAF and TAF policies?
Ans:
Transparent Application Failover (TAF) - A runtime failover for high availability environments, such as Real Application Clusters and Oracle Real Application Clusters Guard, TAF refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails


How to know the public IPs, private IPs, VIPs in RAC?
Ans:
# olsnodes -n -p -i
Client Side Connect-Time Load Balance:
 The client load balancing feature enables clients to randomize connection requests among the listeners.
 This is done by client Tnsnames Parameter: LOAD_BALANCE.
 The (load_balance=yes) instructs SQLNet
Server Side Listener Connection Load Balancing.

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service.
 Init parameter remote_listener should be set.
===================================================

Basic admin for Oracle 12c MultiTenant

Connecting to PDBs

SELECT name, pdb FROM   v$services ORDER BY name;

Display current container

SQL> SHOW CON_NAME

Switching Between Containers

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Startup/shutdown

SELECT name, open_mode FROM v$pdbs;

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

Preserve PDB Startup State

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

Managing Users

Multitenant environments there are two types of user.

• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Create common users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;


Create local users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

Tracking PDBs to Datafiles

Map tables to PDBs:

Checking PDB History