Exadata Smart Flash Caching

Smart Flash Cache:

• Primary task of smart flash cache is to hold frequently accessed data in flash cache so next time if same data required than physical read can be avoided by reading the data from flash cache.

• Exadata caching mechanism focuses on caching frequently accessed data and index block with performance critical information like control file and file headers. 

• Also we can influence caching priorities by using CELL_FLASH_CACHE storage attribute for specific database objects

What will be cached ?

• Frequently accessed data
• Index Block
• File header reads and writes
• Control file reads and writes
• DBA can set the caching priority

What will be not cached?

• Backup related I/O
• Database pump I/O
• Data file formatting
• I/O to mirror copies
• Table scan don't control the cache

Exadata Certification Sample Questions & Answers (1Z0-536)

I have given some sample questions for Exadata Certification. I hope it will help you to prepare for certification. 


When do you specify the type of interleaving for a grid disk?

A. When you create the cell disk
B. When you create the grid disk
C. Through ASM
D. When you create a tablespace
Answer: B

Why would you reduce the default disk_repair_time parameter?

A. To extend the availability of your ASM disk groups.
B. To reduce the need for high redundancy
C. To reduce the amount of data collected for a fast resynch
D. To increase performance of anExadata Storage Server.
Answer: C


What benefit is provided by column filtering?

A. The Exadata Storage Server can select rows based on column values listed in a SQL predicate
B. Storage indexes are built on columns for use in filtering
C. Only necessary columns are returned to the database server
D. Column filtering is a marketing term, not a real benefit
Answer: C

Which types of data are most likely to be cached In the Exadata SmartFlash Cache?

A. Results of random reads.
B. Results of table scans.
C. Write to a mirror
D. Redo data
E. All data is cached In the Flash Cache
Answer: A

What does the role attribute of a DB plan Indicate?

A. The role specified for the category
B. The role specified for the user
C. The role specified In a Data Guard environment
D. The role specified for the application
Answer: A

Your customer has designated a number of database objects to be kept persistently in the Exadata Smart Flash Cache. What happens if the total size of these objects is greater than 80% of the size of the available Exadata Smart Flash Cache?

A. Nothing
B. Exadata Smart Flash Cache expands the allocation to hold the objects.
C. Not all objects will be stored in Exadata Smart Flash Cache.
D. Overall performance is increased as more objects fit into Exadata Smart Flash Cache.
Answer: C

Which three attributes are likely to result in data being cached in the Exadata Smart Flash Cache?

A. CELL_FLASH_CACHE attribute on the data object
B. CACHE hint In the SQL statement
C. Data from a table scan
D. Small data less than 128 KBs
E. Control file I/Os
Answer: A,C,E

How many IP addresses you need to assign for each Exadata storage cell?

Exadata X4 series launched

                      http://www.oracle.com/us/corporate/press/2079925

Cell Server Connectivity and Port Details

Following image shows back end layout of cell storage server.

Short description for each port :

ILOM -- Useful to manage the Hardware remotely

eth0 -- Use to manage the server from OS level

eth1 , eth2 & eth3 -- Not connected as there is no public network required for it.

IB0 & IB1 -- Infiniband network ports (Bonded)

KVM -- For kvm connectivity.

Database Node Connectivity and Port Details

Following image shows the back end connectivity of database server.

Short description for each port:

ILOM -- Useful to manage the Hardware remotely

eth0 -- Use to manage the server from OS level

eth1 & eth2 -- Useful for public network (Bonded)

eth3 -- This port is useful for additional network if required.

eth4 & eth5 -- 10Gig N/W port (Bonded)

IB0 & IB1 -- Infiniband network ports for storage connectivity (Bonded)

KVM -- For kvm connectivity.

How Exadata Cell Offloading Works ?

First let's know what is offloading and benefit of cell offloading in Exadata?

What is Cell Offloading :-

It refers to the fact that part of the traditional SQL processing done by the database can be “offloaded” from the database layer to the storage layer.

Benefit: The primary benefit of Offloading is the reduction in the volume of data that must be returned to the database server. This is one of the major bottlenecks of most  large databases.

Below is the example of cell offloading.

We have created table cell_offload with 20 lac records.

#### Checking the cell offloading process in DB parameter  ####

SQL> show parameter cell_offload_processing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cell_offload_processing              boolean     TRUE

# Flush buffer_cache and shared_pool to get exact result of offloading scenario #

SQL> alter system flush buffer_cache;

System altered.

SQL>  alter system flush SHARED_POOL;

System altered.

## Disabling Cell_offloading_process and checking the query result ##

Exadata Installation Activities

Implementation of Exadata done by Oracle ACS team so many time we are curious to know that what exactly they are doing so here we have listed down the list of activities which can be done by Oracle in various phases.

1.  Configuration worksheet

2.  Pre-delivery Survey

(ACS Team Activity)

3.  Generic Config Files based on Configuration Worksheet

4.  Run checkip.sh

(Oracle H/W Team Activity)

5.  Power on and validate all the components

6.  Configure KVM

7.  Configure Infiniband Swithces

8.  Configure Cisco Switch

9.  Configure IP to PDUs

10. Storage Cell validation

11. Compute Node validation

(ACS Team Activity)

12. Transfer configuration files from USB to DB node

13. Execute firstboot and applyconfig.sh

14. Stage Oracle Software on Node 1

15. Run OneCommand

                                           ASR Installation

Location of Log files in Exadata

On the cell nodes

============

1. Cell alert.log file

/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/alert.logorif the CELLTRACE parameter is set just do cd $CELLTRACE

2. MS logfile

/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/ms-odl.log.

orif the CELLTRACE parameter is set just do cd $CELLTRACE

3. OS watcher output data

/opt/oracle.oswatcher/osw/archive/

To get OS watcher data of specific date :

#cd /opt/oracle.oswatcher/osw/archive

#find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} ;

where 12- year 01- Month 13-day

4. Os message logfile

/var/log/messages

5. VM Core files

/var/log/oracle/crashfiles

More details can be found in the following note:

Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

6. SunDiag output files.

/tmp/sundiag_.tar.bz2

7. Cell patching issues related logfiles:

/var/log/cellos

The major logfile of patch application output you will find in the db node from where you are patching in the location /tmp/<cell version>/patchmgr.stdout and patchmgr.err

8. Disk controller firmware logs:

/opt/MegaRAID/MegaCli/Megacli64 -fwtermlog -dsply -a0

Download Free Oracle Exadata Certification Exam Dumps

On the Database Nodes

================

1. Database alert.log

$ORACLE_BASE/diag/rdbms/{DBNAME}/{sid}/trace/alert_{sid}.log

Ex: /u01/app/oracle/diag/rdbms/dbfs/DBFS2/trace/alert_DBFS2.log

2. ASM alert.log

$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM {instance number}.log

Ex: /u01/app/oracle/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log

3. Clusterware CRS alert.log

$GRID_HOME/log/{node name}/alert{node name}.log

Ex: /u01/app/11.2.0/grid/log/dmorldb02/alertdmorldb02.log

4. Diskmon logfiles

$GRID_HOME/log/{node name}/diskmon/diskmon.lo*

Ex: /u01/app/11.2.0/grid/log/dmorldb02/diskmon/diskmon.log

5. OS Watcher output files

/opt/oracle.oswatcher/osw/archive/

To get OS watcher data of specific date :

#cd /opt/oracle.oswatcher/osw/archive

#find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} ;

where 12- year 01- Month 13-day

6. Os message logfile

/var/log/messages

7. VM Core files for Linux

/u01/crashfiles

More details can be found in the following note:Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

8. Disk controller firmware logs:

/opt/MegaRAID/MegaCli/Megacli64 -fwtermlog -dsply -a0


What are the Exadata Installation Activities ?

ASR Installation and Registration procedure with Oracle

Plateforms:

RHEL 3 or latter x86
SUSE Linux 9 or latter x86
OEL 4 or latter x86

Software Requirement (08/10/2013) :

1. Oracle Automated Service Manager (OSAM) Package (Patch Number p17270592_150_Linux-x86-64.zip)
2. Oracle Automated Service Request (ASR) Package (Patch Number p17199302_45_Linux-x86-64.zip)
3. Service Tools Bundle (STB) (Patch Number p12757884_10000_Generic.zip)

Here we have taken rpm and patch for testing purpose, it's always best practice to use latest RPMs and patch.

All the installation must be done by super User

1. Install STB on ASR Manager Server

          #rpm -i sun-hardware-reg-1.0.0-1.i386.rpm
          #rpm -i sun-servicetag-1.1.5-1.i386.rpm

   2. Verify created service tag execute below command

         #/opt/sun/servicetag/bin/stclient -x

  3. Install OSAM on ASR Manager Server

        #rpm -i SUNWsasm-1.5.0-112.rpm

  4. Install ASR Package on ASR Manager Server

       #rpm -ivh SUNWswasr-4.5-20130703104534.rpm

  5. Add asr path in .bash_profile file:

      PATH=$PATH:/opt/SUNWswasr/bin/asr
      export PATH

Register ASR Manager :

Error while mounting NFS file system on Exadata -- reason given by server: Permission denied

One of our colleague faced below issue while mounting NFS file system on Exadata.

[root@nfsclient /]# mount -t nfs 192.168.100.11:/nfs_server /nfs_client
mount: 192.168.100.11:/nfs_server failed, reason given by server: Permission denied

Solution:

NFS_Server IP - 192.168.100.11
NFS_Client IP - 192.168.100.10

Go to NFS Server. In our case NFS server is 192.168.100.11

ssh 192.168.100.11

Check the /etc/exports file

#cat /etc/exports

/nfs_server 192.168.100.*(*)

Edit /etc/exports file

#vi /etc/exports

/nfs_server 192.168.100.10 (rw,sync)

Restart the NFS services.

#service nfs restart

Now check on NFS Client server which is 192.168.100.10

ssh 192.168.100.10

#Service nfs restart
#Service portmap restart

#showmount -e nfsclient ----Here nfsclient is the server name

output:/nfs_server 192.168.100.10      

Now mount the nfs filesystem

[root@nfsclient /]# mount -t nfs 192.168.100.11:/nfs_server /nfs_client

Now it has been mounted successfully.

Pre-login Message / Banner Configuration in Linux

Banner is used to display  welcome message while login to the particular system which may includes warning for unauthorized users and agreement for the user who used to login to the system.

Create a text file with banner description

#vi /etc/ssh/ssh-banner-message.txt

WARNING: Unauthorized access to this system is forbidden and will be prosecuted by law. By accessing this system, you agree that your actions may be monitored if unauthorized usage is suspected

# edit below line in /etc/ssh/sshd-config file

Banner /etc/ssh/ssh-banner-message.txt

#service sshd restart

SSH Login with any user :

Now it will show the pre-login message.

DBFS on Oracle Exadata

Follow below steps to create DBFS

Create the mount point for DBFS

[root@db01 ]# mkdir /dbfs_dir

Login with Oracle user

[root@db1 ]# su - oracle

Set the Oracle Database environment variable to connect the your database.

Connect Database as sysdba

[oracle@db01 ~]$ sqlplus  / as sysdba

Create tablespace on ASM disk group

SQL> create tablespace dbfsts datafile '+DATA' size 1G;

SQL> create user dbfs_usr identified by Dbfs_pwd1 default tablespace dbfsts quota unlimited on dbfsts;

SQL> grant create session, create table, create procedure, dbfs_role to dbfs_usr;

You can use below syntax to create bigfile tablespace.

create bigfile tablespace dbfsts datafile '+DATA' size 2048g autoextend off nologging extent management local;

Go to admin directory of oracle binaryand run the below procedure

$cd $ORACLE_HOME/rdbms/admin

$sqlplus dbfs_usr/Dbfs_pwd1

SQL> start dbfs_create_filesystem_advanced dbfsts FS nocompress nodeduplicate noencrypt non-partition

Above procedure should run without errors.

TEST DBFS SETUP

[oracle@db01 ~]$ echo Dbfs_pwd1 > pwd.txt

[oracle@db01 ~]$ nohup $ORACLE_HOME/bin/dbfs_client dbfs_usr@test /dbfs_dir < pwd.txt &

- 'test' is database name

- '&' to run the process in background

- '/dbfs_dir' is a directory on which DBFS will be mounted

LOGIN to Oracle USER account from another terminal and check the DBFS file system

[oracle@db01 ~]$ cd /dbfs_dir/FS

[oracle@db01 ~]$ touch test.txt

[oracle@db01 ~]$ ls -ltr test.txt

LOGIN to root USER account from another terminal and check whether you can get acces of the DBFS file system

[root@db01 ]# cd /dbfs_dir/FS

Permission Denied

To unmount the file system issue the following command from the "root" OS user.

[root@db01 ]# fusermount -u /dbfs_dir

To allow other users ( example root to access the filesystem )

[oracle@dbs1 ]$ su - root

Password :

[root@db01 ]# echo user_allow_other > /etc/fuse.conf

change permission of /etc/fuse.conf to 644

[root@db01 ]#chmod 644 /etc/fuse.conf

[root@db01 ]# su - oracle

[oracle@db01 ~]$ nohup $ORACLE_HOME/bin/dbfs_client dbfs_usr@test -o direct_io,allow_root /dbfs_dir < pwd.txt &

You can check nohup file for any error logs.

Login to root user account from another terminal and check the accessibility of DBFS

[root@db01 ]# cd /dbfs_dir/FS

[root@db01 ]# ls -lt test.txt

To unmount the file system issue the following command from the "root" OS user.

# fusermount -u /dbfs_dir

User Equivalence between DB Node and Storage Cell

In configuration of user equivalence is quite simple as it has been managed by executable script which doesn't required manual actions which generally we do in normal server setup.

Explained user equivalence setup procedure for Exadata servers and storage.

Checking the hostname on storage server from DB node with dcli utility where SSH equivalence has not been configured.

[oracle@ex01dbm01 ~]$ dcli -c ex01cel01 -l oracle hostname

celladmin@ex01cel01's password:

ex01cel01: ex01cel01@example.com

Now pushing SSH key to storage cell with below command

[oracle@ex01dbm01 ~]$ dcli -g ./cell_group -k

celladmin@ex01cel02's password:

celladmin@ex01cel01's password:

celladmin@ex01cel03's password:

ex01cel01: ssh key added

ex01cel02: ssh key added

ex01cel03: ssh key added

Here cell_group is the list of cell storage server name .

Now executing dcli on storage cell to check whether user equivalence has been configured properly or not.

[oracle@ex01dbm01 ~]$ dcli -c ex01cel01 -l oracle hostname

ex01cel01: ex01cel01@example.com

Above command has not asked any password to login into the cel01 that means equivalence is working fine.

                            Oracle User Equivalence between two DB servers

Cheeers..!!!

Exadata Monitoring Tools

Many requests came that how we can monitoring Exadata and what are the monitoring tolls available so listed known Exadata monitoring tools as well as configuration check tool.

Exacheck -- To check whether Exadata configuration has been done as per best practice or not.

OSWatcher -- It monitors server resources and gives past resource consumption details if required.

OEM (Oracle Enterprise Manager) -- It used to monitor Exadata end to end. With OEM we can monitor each and every components of Exadata. It is useful to monitor Hardware as well as software.

ASR (Auto Service Request) -- It is used to monitor Exadata hardware. Automatically it creates service request in oracle support.

We will explain each tool with setup & configuration in separate post.

                               Exadata Machine Monitoring Commands

How to Verify Hardware and Firmware on Exadata Components (Storage & Server) ?

If the hardware and firmware are not validated, inconsistencies between database and storage servers can lead to problems and outages.

With below command we can verify hardware and firmware on storage and server.

# /opt/oracle.cellos/CheckHWnFWProfile

[SUCCESS] The hardware and firmware profile matches one of the supported profile

If any result other than "SUCCESS" is returned, investigate and correct the condition.

Hot to Verify InfiniBand Fabric Topology ?

Verifying infiniband topology whether it is configured current or not. If it not gives the desired output as per below then it will cause the InfiniBand network to operate at degraded efficiency, intermittently, or fail to operate.

# /opt/oracle.SupportTools/ibdiagtools/verify-topology -t fattree

[ DB Machine InfiniBand Cabling Topology Verification Tool ]
Is every external switch connected to every internal switch..........[SUCCESS]
Are any external switches connected to each other....................[SUCCESS]
Are any hosts connected to spine switch..............................[SUCCESS]
Check if all hosts have 2 CAs to different switches..................[SUCCESS]
Leaf switch check: cardinality and even distribution.................[SUCCESS]
Check if each rack has an valid internal ring........................[SUCCESS]

If anything other than "SUCCESS" is reported, investigate and correct the condition.

Exadata Hybrid Columnar Compression (EHCC)

Let's see how EHCC help us to reduce storage space with performance database improvement.

Here we have created three tables with same data as per below.

1. Test  with no compression

2. Test_hcc_archive with archive high compression

3. Test_hcc_query with query high compression

#### List of tables with compression types ####

SQL> select table_name,compression,compress_for

  2    from user_tables

  3   where table_name in ('TEST','TEST_HCC_QUERY','TEST_HCC_ARCHIVE');

TABLE_NAME                 COMPRESS COMPRESS_FOR                            

------------------------------ -------- ------------                            

TEST                           DISABLED                                         

TEST_HCC_ARCHIVE      ENABLED    ARCHIVE HIGH                            

TEST_HCC_QUERY         ENABLED    QUERY HIGH                              

#### Each Tables are with 100000 row data ####

SQL> select count(*) from test;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.11

SQL> select count(*) from test_hcc_query;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.10

SQL> select count(*) from test_hcc_archive;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.10

#### Checking the size of data for each tables ####

Exadata Interview Questions Part -2

Get an e-book of Exadata Interview Questions and Answers to stay miles ahead of your competition..!!!

• What is cell disk ?
• What is grid disk ?
• What is IORM ?
• What are the ways to migrate on Exadata ?
• Procedure to drop cell disk on Exadata.
• What is cellsrv and how it works ?
• Use of cellsrv service on Exadata storage cell.
• What is iDB protocol ?
• What is the purpose to use Exacheck tool ?
• What is the oswatcher and how it works ?
• What is RS services and how it works ?
• How to enable HCC on database table ?
• How many networks are available on Exadata ?
• How we can integrate OEM12c with Exadata ?
• What are the ways to install OEM12c agent on Exadata ?
• Which cell services are running on Exadata storage cell ?
• Purpose of MS(management service) on storage cell.
• How cell storage software backup happens ?
• Which OS supports by Exadata ?
• How to power off Exadata Machine ?
• How to do maintainance on Cell Storage and DB server?
• What is writet-through caching mode ?
• What is Write-back caching mode ?
• Explain the procedure to drop the Cell storage ?
• How to replace physical storage disk on cell storage ?

Get Questions and Answers HERE.....

You can follow our Exadata Certification Question Bank to achieve maximum success in your Exadata certification.

How to find and delete older files with bigger size files in Linux

To find out files size more than 5MB

#find . -size +5000  -exec ls -ltr {} \; 

 To **Remove** files size more than 5MB

#find . -size +5000k  -exec rm -rf {} \;

To find out files older than 30days

#find . -mtime +30 -exec ls -ltr {} \;

To find **Remove** files older than 30days

#find . -mtime +30  -exec rm -rf {} \; &

                                  Linux Server Performance Analyzer Tools

OEM11g / OMS Auto Start and Stop Script for Linux

After installation OEM 11g mostly we required is to start the OEM services even after server reboot so here you will see how to make a OEM start-up script and add into the OS start-up sequence.

This is purely to avoid manual operation of OEM start-up if in any case we reboot the server.

1. Create dbora (service name) file as below: 

 vi /etc/init.d/dbora

#Run-level Startup script for the Oracle Instance and Listener

#

# chkconfig: 345 91 19

# description: Startup/Shutdown Oracle listener and instance

ORACLE_HOME="/oem/oracle/app/oracle/product/11.2.0/dbhome_1/"

ORACLE_OWNR="oracle"

OMS_ORACLE_HOME="/oem/Middleware/oms11g/"

OEM_AGENT_HOME="/oem/Middleware/agent11g/"

# if the executables do not exist -- display error

Power Off Sequence for Exadata Stack

Poweroff Database Servers

For single server power off /Restart

1. First stop the oracle cluster with below command

#$GRID_HOME/grid/bin/crsctl stop cluster

2. If it fails to stop the cluster then use -f option to stop it forcefully.
Shutdown the server with below command

#shutdown -h -y now

To restart the server execute below command

#shutdown -r -y now      or      #reboot

3. Remove power cable from the server

For multiple servers poweroff/restart with dcli utility

1. First stop the oracle cluster with below command

#$GRID_HOME/grid/bin/crsctl stop cluster -all

2. Shutdown all the servers with dcli utility

#dcli -g dbs_group -l root shutdown -h -y now

---dbs_group is the file which contains the list of all the exadata servers.

To restart the server execute below command

#dcli -g dbs_group -l root shutdown -r -y now    or  #dcli -g dbs_group -l root reboot

----dbs_group is the file which contains the list of all the exadata servers.

3. Remove power cables from the servers

Poweroff Storage Servers

Reat the same procedure of server to power off the exadata storage servers except to stopping the crsctl.

Note: We have to use cell_group file instead of dbs_group file to shutdown the storage servers.

Poweroff Network Equipments

We can directly remove power cables from the network switches as network equipments don't have power switches.