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

ORATOP - Top process for Oracle databases

Key	description
t	display tablespace information.
a	displays the database's ASM information
s	toggle the fourth section between showing session information, the default, and showing SQL statements
x	lets the database administrator see the Explain Plan for a given SQL identifier.
h	help
m	Module/process
k	File#, block#
f	Long format
i	Interval display
d	Real time processing

RED

Values flagged and highlighted in red color are merely a warning or to emphasize importance

MySQL - How to perform basic admin

MySQL - How to perform basic admin

How to display output

display in vertical : -E, --vertical

 

variables : --print-defaults

 

save output : -tee= save html : -H

 

How to check MySQL server is alive?

alive : mysqladmin -p ping

 

How to Find out current Status of MySQL server?

mysqladmin -u root -p extended-status mysqladmin -u root -p status

How to check MySQL version?

mysqladmin -u root -p version

How to stop MYSQL?

mysqladmin -p shutdown

How to set root password?

mysqladmin -u root password YOURNEWPASSWORD

How to check all the running Process of MySQL server?

mysqladmin -u root -p processlist

How to kill a process

>

mysql> SHOW PROCESSLIST; or mysql> SELECT * FROM information_schema.processlist ORDER BY id; mysql> Kill thread_id or mysqladmin kill id

How to create a job

mysql > CREATE EVENT IF NOT EXISTS test_event_01 ON SCHEDULE AT CURRENT_TIMESTAMP DO … mysql> SHOW EVENTS FROM …

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

MySQL Backup and Recovery

This article provides a quick guide to performing backup and recovery of MySQL databases

Logical Backup (mysqldump)

Backup database

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Backup multiple databases

mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

 

Restore a database


In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore

mysql -u root -ptmppassword mysql> create database sugarcrm; mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Exadata - tools for administration

Purpose	Command
Conduct a comprehensive Exadata health check on your Exadata Database Machine to validate your hardware, firmware,	./exachk -a
Collecting RAID Storage Information	/opt/MegaRAID/MegaCli/MegaCli64 -PDList –aALL
Administering the Storage Cell Network	ipconf –verify
Diagnosing Your InfiniBand Network	/usr/bin/ibdiagnet /usr/sbin/ibqueryerrors.pl
InfiniBand network performance	opt/oracle.SupportTools/ibdiagtools/infinicheck
Exadata version	imageinfo imagehistory
oswatcher	exawatcher

 

Exadata - the key software components and their location

Directory/Executable/File	Purpose
/opt/oracle	Top level directory containing Oracle storage server software
/opt/oracle.cellos	Directory containing Exadata cell software and utilities
/opt/oracle.cellos/cell.conf	Cell configuration file
/opt/oracle.cellos/CheckHWnFWProfile	Utility to validate hardware profile
/opt/oracle.cellos/ExadataDiagCollector.sh	Utility to collect cell diagnostics data, valuable for SRs
/opt/oracle.cellos/functions_cellos	Contains various Cell OS function calls
/opt/oracle.cellos/imageinfo	Shows current image information
/opt/oracle.cellos/imagehistory	Shows image history
/opt/oracle.cellos/ipconf[.pl]	Displays or configures cell network environment
/opt/oracle.cellos/iso	Contains kernel ISO images
/opt/oracle.cellos/make_cellboot_usb.sh	Creates a USB rescue image
/opt/oracle.cellos/MegaCli64	MegaCLI—also in /opt/MegaRAID/MegaCli
/opt/oracle.cellos/patch	Directory for staged patches
/opt/oracle.cellos/restore_cellboot.sh	Restores from USB rescue image
/opt/oracle.cellos/validations_cell	Directory containing output from cell server validations
/opt/oracle.cellos/vldconfig	Configures cell validation
/opt/oracle.cellos/vldrun	Runs cell validation scripts and logs to /opt/oracle.cellos/validations_cellw
/opt/oracle/cell	Symlink to /opt/oracle.cell[VERSION]
/opt/oracle/cell[VERSION]	Directory containing current cell software. For example, /opt/oracle/cell11.2.2.4.2_LINUX.X64_111221
/opt/oracle/cell[VERSION]/cellsrv	Directory containing cellsrv software
/opt/oracle/cell[VERSION]/cellsrv/deploy/config	Configuration deployment files for active cell image
/opt/oracle/cell[VERSION]/cellsrv/deploy/config/cellinit.ora	Cell initialization parameter file
/var/log/oracle	Directory containing cell server log files, alerts, and trace files
/var/log/oracle/cellos	Directory containing log and trace files for Cell Services utilities, validation framework, and cell server startup/shutdown events
/var/log/oracle/diag/asm	Directory containing log and trace files for cell storage-related events in your cell

Hardware sizing for Oracle database

1. Dell white paper for Oracle database sizing

5 key features that empowers Oracle Exadata

5 key features that empowers this engineered system:

1. Smart Flash Cache
The Exadata Storage Server layer includes some flash storage as a hardware component, which has been implemented as a set of PCI flash cards. The main benefit of that is of course faster access than standard, disk-based access. In some cases you can order Exadata to KEEP whole table in Smart Flash Cache which speeds up database layer access with Full Table Scans. On the other hand in Write-Back mode you can empower DBWR or LGWR performance by putting written data on flash cache first, and afterwards sync it with regular hard drives, which of course is transparent to database engine.


2. Storage Indexes
It is absolutely unique to Exadata. Storage Indexes are an intelligent storage implementation. Generally speaking classic database index by it definition is created to efficiently provide a location of a certain data key. To be honest Storage Indexes in Exadata are focused on eliminating areas on storage as possible place where data might exist. Online data maps which are completely transparent for database layer are stored on the Flash Cache of Exadata Storage Servers. To make the story short, when Exadata Storage Server scans through the Storage Index and identifies the regions where predicate value falls within the MIN/MAX for the region, only for the identified regions physical I/O occurs. And as a consequence, even Full Table Scan has been planned by CBO on Database Server layer, limited I/O operations use to be proceed.


3. Smart Scans and Cell Offloading
Offloading use to be called as secret sauce of Oracle Exadata. The main concept of Offloading is to move processing from DB Nodes (Database Servers) to the intelligent storage layer. What is even more important Offloading means the reduction in the volume of data that returns to database server, which is one of the major bottlenecks in terabytes or even more bigger databases. To eliminate the time spent on transferring completely unnecessary data between storage and the database tier is the main issue that Oracle Exadata has been built to solve. Keep in mind that Offloading and Smart Scan terms could be used somewhat interchangeably.


4. Hybrid Columnar Compression
Also known as HCC, Hybrid Columnar Compression is one of the key features of Oracle Exadata, and it is only available on this engineered system. HCC format of compression will be used only when data arrives with direct path loads. There are four levels of compression QUERY LOW (LZO, 4x), QUERY HIGH (ZLIB, 6x), ARCHIVE LOW (ZLIB, 7x) and ARCHIVE HIGH (Bzip2, 12x). Keep in mind that HCC is not a good option for OLTP systems. In case of HCC mechanics, HCC store data in nontraditional format. Even data resides in Oracle blocks, with block header for every block, in HCC data storage has been organized in logical structures called compression units (CUs). Each CU consists of multiple Oracle blocks.


5. IORM
I/O Resource Manager (IORM) is a Oracle Exadata features which enriches Oracle Resource Manager from Database layer. IORM only actively manages I/O requests when needed and when Storage Server is not fully utilized, it provide data immediately. But when a disk is under heavy utilzation, Storage Server software redirects the I/O requests to the appropriate IORM queue and schedules I/O from there according to the policies defined in your IORM plans. Generally IORM policies open the way to prioritize databases on intelligent storage layer, which enable workload optimization.

Oracle Database Backup To Cloud (to Amazon S3)

Oracle Database Backup To Cloud:
Amazon Simple Storage Service (S3)

Amazon Web Services (AWS) is the first Cloud vendor that Oracle has partnered with to enable database backup in the Cloud.

The process is:

1. Create an AWS S3 account and setup the necessary credentials.
2. Install an AWS specific Oracle Secure Backup library into your Oracle Home.
3. Run an RMAN backup using the SBT_TAPE device type.

Creating an AWS S3 Account

Go to -> aws.amazon.com

Under IAM (Identify & Access Management) , select user -> add user

 

Installing the Oracle Secure Backup Cloud Module Jar file

The Oracle Secure Backup (OSB) Cloud Module enables an Oracle Database to send its backups to Amazon S3. It is compatible with Oracle Database versions 9i Release 2 and above, and it requires a network connection to the Internet




From OTN download an installer Java JAR file and copy and extract the zip to your database server. When run, the installer will determine the proper database version and OS platform, as well as download the appropriate library file to your Oracle home or other specified directory.

$ java -jar osbws_install.jar \
>    -AWSID AKI***************QA \
>    -AWSKey no/MD*******************************upxK \
>    -otnUser vincent.ng@xxxx.com \
>    -walletDir $ORACLE_HOME/dbs/osbws_wallet \
>    -libDir $ORACLE_HOME/lib

RMAN> run {
allocate channel aws_s3 type sbt
parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/12.1.0/dbhome_2/dbs/osbwsCDB121.ora)';
backup tablespace users;
}

Getting X11 forwarding through ssh working after running su

Getting X11 forwarding through ssh working after running su

$ xauth list $DISPLAY
You'll get something like

somehost.somedomain:10 mit-magic-cookie-1 4d22408a71a55b41ccd1657d377923ae

Then, after having done su, tell the new user what the cookie is:

$ xauth add somehost.somedomain:10 MIT-MAGIC-COOKIE-1 4d22408a71a55b41ccd1657d377923ae

Oracle Clusterware Main Log Files

Oracle Clusterware Main Log Files

Oracle Clusterware uses a unified log directory structure to consolidate the Oracle Clusterware
component log files. This consolidated structure simplifies diagnostic information collection and
assists during data retrieval and problem analysis.

The main directories used by Oracle Clusterware to store its log files:

• CRS logs are in $ORA_CRS_HOME/log//crsd/. The crsd.log file is archived every 10 MB (crsd.l01, crsd.l02, …).
• CSS logs are in $ORA_CRS_HOME/log//cssd/. The cssd.log file isarchived every 20 MB (cssd.l01, cssd.l02, …).
• EVM logs are in $ORA_CRS_HOME/log//evmd.
• Depending on the resource, specific logs are in $ORA_CRS_HOME/log//racg and in $ORACLE_HOME/log//racg. In the last directory, imon_.log is archived every 10 MB for each service. Each RACG executable has a subdirectory assigned exclusively for that executable. The name of the RACG executable subdirectory is the same as the name of the executable.
• SRVM (srvctl) and OCR (ocrdump, ocrconfig, ocrcheck) logs are in $ORA_CRS_HOME/log//client/ and in $ORACLE_HOME/log//client/.
• Important Oracle Clusterware alerts can be found in alert.log in the $ORA_CRS_HOME/log/ directory.

Oracle dbstart/dbshut does not do anything

Steps

1. at etc/oratab have the last char as Y and not N
2. Eg

3. orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

   
   change it to this :
   
   
   

   orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

Basic Oracle Exadata administration

Basic Oracle Exadata Machine administration

Using dcli

1. connect as oracle user
2. create a file named mycells
   
   

   cat <<> mycells dbserver01 dbserver02 dbserver03 dbserver04 END

1. Generate a ssh key pair

2. ssh-keygen -t rsa



3. You have completed. Now try it.
   
   

   dcli -g mycells "cat /proc/meminfo | grep Mem"

How to check Exadata version?

[root@remote_dbacell1 ~]# imageinfo [root@remote_dbacell1 ~]# imagehistory

How to clean exawatcher logs?

(Doc ID 1617454.1)


One-time cleanup for ExaWatcher logs:

1.  Stop ExaWatcher
   
   

   ./ExaWatcher.sh --stop

2. Adjust on the command line for a one-time cleanup operation ...  will remove all older ExaWatcher logs over 2GB quotaOutput showing ExaWatcherCleanup.sh execution will be written to the console.  When completed you will see something similar to:"Current size of the result directory /opt/oracle.ExaWatcher/archive/ is 2097152 after clean up. Please see /opt/oracle.ExaWatcher/log/OldFilesDeleted.log to look for the files deleted." - Use 'control C' to exit from cleanup script
   

   ./ExaWatcher.sh --spacelimit "spacelimit" (in MB)ex) ./ExaWatcher.sh  --spacelimit 2048

3. 
   
4. Restart ExaWatcher
   
   

   /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher

5. 
   

How to change OS user password for Cell Node, Database Node , ILOM, KVM , Infiniband Switch , GigaBit Ethernet Switch and PDU on Exadata Database Machine

(Doc ID 1291766.1)

1. set /SP/users/root password

   > dcdli -g dbs_cell_group_ilom -| root " ipmitool sunoem cli 'set /SP/users/root password=newpassword' oldpassword"

   
   
   
   

How to stop database nodes?

1. /grid/bin/crsctl stop crs –f
2. Shutdown oswatcher
   
   

   cd /opt/oracle/oswatcher/osw/ /stopOSW.sh

How to power on/off sequence?

Power off sequence

1. On Database server
   1. crsctl stop cluster
   2. shutdown –h –u now
2. On Storage server
   1. Shutdown –h –y now
3. Turn off Rack, network switches

Power on sequence

1. Rack, network switches
2. Storage server
3. Database server

How to Shutdown Exadata storage Server?

Shutdown sequence

cellcli> LIST GRIDDISK WHERE asmdeactivationoutcome != ‘Yes’ cellcli> ALTER GRIDDISK ALL INACTIVE cellcli> LIST GRIDDISK WHERE STATUS !=’INACTIVE’

Startup sequence

cellcli> ALTER GRIDDISK ALL ACTIVE cellcli> LIST GRIDDISK ATTRIBUTES name,attributes

1. 
   

How to replacing a Damaged Physical disk?

1. cellcli> LIST ALERTHISTORY WHERE ALERTMESSAGE LIKE “Logical drive lost.*” DETAIL
2. Replace the physical disk
   1. ALTER PHYSICALDISK
3. Monitor the ASM to confirm the re-addition of the disk

Replacing a damaged Flash Card

1. 
   
   

   cellcli> LIST PHYSICALDISK DETAIL

   
   
2. power off cell
3. replace the damanged card
4. power on the cell

Moving all disk from one cell to another

1. 
   
   

   cellcli> ALTER GRIDDISK ALL INACTIVE

2. Backup OS config
3. Move the disk,flashcard, disk controller and CELLBOOT to new cell
4. Boot the new cell
5. Restart cell services
   
   

   Cellcli> ALTER CELL RESTART SERVICES ALL

   
   
6. Activate the gird disks
   
   

   Cellcli> ALTER GRDDISK ALL ACTIVE

   
   
7.