How To Gather Exadata Statistics ?

Very quick note on gathering of Exadata statistics. 

Why?

Gathering Exadata specific system statistics ensure the optimizer is aware of Exadata scan speed.

Accurately accounting for the speed of scan operations will ensure the optimizer chooses an optimal execution plan in a Exadata environment. 

Following command gathers Exadata specific system statistics

SQL>exec dbms_stats.gather_system_stats(‘EXADATA’);

Risk:

Lack of Exadata specific stats can lead to less performance optimizer plans.

Action / Repair:

To see if Exadata specific optimizer stats have been gathered, run the following query on a system with at least 11.2.0.2 BP18 or 11.2.0.2 BP8 Oracle software. 

Exadata Document Available Publicly

Its great news to all Oracle techies. Now Oracle Exadata Document is available publicly on Oracle website. No need Oracle support id to view the document.

You can get it from below link.

                                         Oracle Exadata Documentation

GoldenGate - libnnz11.so: cannot open shared object file

While login into Goldengate command line interface we faced below error. Resolution or error is not that much complicated but many time we are not able to find out what can be the issue while working in under pressure situation so thought to write this piece.

[oracle@ggtest ogg]$ ./ggsci
./ggsci: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

Only need to export library from oracle home to resolve this error. It is better to append this in .bash_profile of Oracle user.

[oracle@ggtest ogg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oracle@ggtest ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI>

What is Exacheck in Exadata and what it does?

Exacheck:

Exacheck is designed to audit configuration of Oracle Exadata Machine which includes DB Server, Cell Storage, Infiniband switch, Software, Database, and Ethernet Network.

When to execute:

Exachek report should be fetched after the initial Oracle Exadata Database Machine deployment, as a part of the routine maintenance schedule (at least monthly),before and after any system configuration change and patching activity.

Phases:

There are four phase in exachek execution.

1) Minimal Input 

2) Data Collection

3) Analysis

4) HTML Report Generation

umount: /u01 device is busy

Recently we faced this error while un-mounting /u01 file system.

You can get this error in two case either some one is logged in into the file system or some process is using this file system.

We have checked that no one is using /u01 file system now only one option was there , some process is using /u01 file system which is not allowing us to un-mount it.

We can check with below command that which processes are associated with /u01 file system.

How to disable/enable CPUs in Linux ?

It's quite interesting to know how to disable and enable CPU core in Linux in different scenario.

Thought came from Exadata architecture as Exadata 1/8th Rack comes with 16 cores but only half of the CPUs will be enabled on the DB server, that's why we have to pay Oracle licence cost for half core only.

This enabling and disabling can be controlled by resource control utility which comes with Exadata, so what we can do to enable or disable cpu core in non Exadata server?

We have performed this test on test server which has four CPU cores.

Step 1: Check Total Available CPU

There are multiple method to check it, we have checked it with TOP utility which give you better understanding. It shows 4 cpu cores are available.

What is HARD in Oracle Exadata?

Hardware Assisted Resilient Data (HARD)

• HARD will prevent data corruptions from being written to storage disks.
• Oracle database validates and add protection while sending data to the storage server.
• Whenever data will go to storage server , first it will check if there is any data corruption. If it found the corrupted data , it would stops corrupted data from being written to disk.
• Previously or in non-Exadata hardware it's not possible to prevent corrupted data to be written on to the storage and that was major reason for database corruption in many cases which Oracle has eliminated with the use of storage software which has been introduced in Exadata storage cell.
• There is nothing to set on Database level or storage level as  HARD handle corruption transparently which also included for ASM rebalance.

Linux Server Performance Analyzer Utilities

If there is any Linux performance issue we mostly go to Google and find commands to analyze the performance but multiple time it's difficult to find it when we required some specific details so, we have tried here to place all the Linux performance utility which can help you to analyze the Linux server performance.

There are multiple utilities available to analyse Linux server. Which includes CPUs, Memory, I/O, disk, network analysis.

Utility	Discription
free	Displays free and used memory.
iostat	Displays disk I/O statistics.
netstat	Reports on network statistics.
mpstat	Reports CPU statistics.
vmstat	Monitors processes, CPU, memory, or disk I/O bottlenecks.
ps	Identifies highest CPU- and memory-consuming sessions. Used to identify Oracle
sar	Displays CPU, memory, disk I/O, and network usage, both current and historical
top	Identifies sessions consuming the most resources.
watch	Periodically runs another command.
du	Displays disk usage.
df	Reports on free disk space.

                                              How to calculate IOPS in Exadata ?

Exadata Dictionary Views

Listed down some Exadata views which can help you to get Exadata related statistics

Get Cell Definition

V$CELL 

GV$CELL 

Exadata cell effectiveness and statistics

V$SYSSTAT 

V$SEGMENT_STATISTICS 

V$SQL 

Historical view of the types and frequencies of the requests being run by a cell.

V$CELL_REQUEST_TOTALS 

Cell Performance Statistics

V$CELL_STATE 

Display Exadata Cell Threads

V$CELL_THREAD_HISTORY

Backup Related View

V$BACKUP_DATAFILE 

Useful Files in Solaris

Message log:  /var/adm/messages

Tape driver configuration file:  /kernel/drv/st.conf

Disk driver configuration file:  /kernel/drv/sd.conf

File defining filesystems to mount:  /etc/vfstab

Kernel configuration parameters:  /etc/system

Names and IP addr of machines:  /etc/hosts

Default Port Numbers:  /etc/services

                                    Analyze Linux Server Performance

How to check that database is running on Exadata?

All the DBA don't get chance to work on Exadata as only DMA would have access to the Exadata so , if you are a DBA and want to know that whether your database is running on Exadata or not than you can check with below query.

If this query gives the output >0 than database is running on Exadata.

SQL>select count(*) from (select distinct cell_name from gv$cell_state);

COUNT(*)

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

         3

It means database is running on 1/8th Exadata as 3 tells you the number of cells available on database machine.

By executing "select distinct cell_name from gv$cell_state" you will get cell IP details.

Oracle Exadata vs SAP HANA

We have provided some major difference between SAP HANA and Oracle Exadata. 

Even Exadata is not comparable with HANA than also we have tried to conclude it based on materials and documentation available in the market.

Oracle Exadata	SAP HANA
SAP HANA is an in-Memory Appliance.	SAP HANA is an in-Memory Appliance.
Oracle Exadata is based on Oracle servers, storage, networking, and software– all engineered and optimized to work together. It’s meant for Mixed workloads, i.e OLTP,DW	SAP HANA and partner solutions powered by the SAP HANA in-memory software. There are only a limited number of specialized SAP applications that run on HANA
We can use existing Oracle licences with Exadata	If we move to HANA, it's required to buy new licences as existing licences would not be reused
Oracle Exadata is delivered pre-configured, pre-tested and pre-optimized, with all necessary software, storage, hardware,and interconnect to lower total cost of ownership and provide quick time to value. Oracle Exadata is sold, delivered, and supported as a single-vendor solution with quick time-to-resolution.	SAP HANA relies on third-party compute, network,and storage vendors. Multiple points of support lead to longer downtimes and higher total cost of ownership. No single vendor is accountable for the solution's service-level agreements and due to its complexity, HANA deployments typically require a significant amount of effort.
Exadata can be used for any type of workload for any application which is running with Oracle Database	HANA is limited to SAP BW only. It doesn't support any other application
Exadata is capable for all type of workload so customer can consolidate multiple database on single machine.	SAP HANA runs only single application. It doesn't support multiple workload as well non sap DBs
Exadata comes with built in security	HANA don't have any encryption, auditing , compliance monitoring or any security certification
Oracle Exadata is enough smart to use RAM, Flash and Storage	HANA puts everything on memory
Exadata is single vendor optimized machine	HANA comes as an assemble hardware
Exadata is pre configured with HA and single point of hardware failure	HANA has limited capabilities in backup and recovery area. Also there is no failover solution as well disaster recovery solution
We can use IORM and instance caging in Exadata to prioritize the I/Os.	There is no features available to prioritize I/O in HANA
TCO is less compare to HANA	TCO is high compare to Exadata
Only Exadata DBA is required to manage Exadata	Multiple resource required to manage HANA

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

Data Migration using Oracle Transportable Tablespace

Procedure to restore or migrate tablespace with the use of transportable tablespace.

There are two ways to moving tablespace/data to target system.

• Same platform data migration - Move data to same OS or endian, big to big endian or little to little endian operating system
• Cross platform data migration - Move data from little to big endian or big to little endian

If we are moving tablespace from little to big endian or big to little endian than we have to convert it to target endian platform which has been showed here in step-7.

If we are moving data to same endian platform operating system even to different OS than it's not required to use convert option.

List of operating system with endian format.

PLATFORM_ID	PLATFORM_NAME	ENDIAN_FORMAT
1	Solaris[tm] OE (32-bit)	Big
2	Solaris[tm] OE (64-bit)	Big
3	HP-UX (64-bit)	Big
4	HP-UX IA (64-bit)	Big
5	HP Tru64 UNIX	Little
6	AIX-Based Systems (64-bit)	Big
7	Microsoft Windows IA (32-bit)	Little
8	Microsoft Windows IA (64-bit)	Little
9	IBM zSeries Based Linux	Big
10	Linux IA (32-bit)	Little
11	Linux IA (64-bit)	Little
12	Microsoft Windows 64-bit for AMD	Little
13	Linux 64-bit for AMD	Little
14	HP Open VMS	Little
15	Apple Mac OS	Big

Now let's see the demo to migrate tablespace. Here we have created "ttest" tablespace to show the demo.

How to calculate IOPS in Exadata ?

When we think to calculate IOPS , it seems very difficult task but it’s very easy to calculate in Exadata Storage. Let’s understand the procedure to calculate IOPS.

Well, first of all, execute the calibrate command on all available cell storage.

We are doing testing on Exadata Database Machine X3-2 1/8^th rack. Same procedure can be followed on any configuration of Exadata.

Cell Server 1

CellCLI> calibrate force

Calibration will take a few minutes...

Aggregate random read throughput across all hard disk LUNs: 1521 MBPS

Aggregate random read throughput across all flash disk LUNs: 8048.46 MBPS

Aggregate random read IOs per second (IOPS) across all hard disk LUNs: 2380

Aggregate random read IOs per second (IOPS) across all flash disk LUNs: 113116

Controller read throughput: 1974.08 MBPS

Oracle ASR Logfile Location

Location of ASR log on ASR server

#/var/opt/SUNWsasm/log

#/var/opt/SUNWsasm/log/auditlog

Integrate Oracle Support with OEM12c

Procedure to integrate Oracle Support with OEM12c

1. First set the proxy if any, otherwise you can keep as it is like no proxy

Go to


Setup -> Proxy Settings -> My Oracle Support


Click to enlarge

Provide proxy settings:

Test the proxy setting:

Apply Proxy:

2. Apply your oracle credentials which you want to integrarte with EM

Oracle Block Change Tracking (BCT)

Block change tracking records the modified blocks since last backup and stores this logs in block change tracking file.

During backups,  RMAN uses this log file to identify the specific blocks (changed block) that must be backed up.

Benifits: BCT improves RMAN backup performance as it would not required to scan whole datafiles to detect changed blocks. It will only see the changed block through block change tracking files and take the back up that clock.

Whenever data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. 

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer.

During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the block change-tracking file.

To see the status of BCT Status

How to change OEM 12c Agent Port ?

Whenever we install EM agent, it will use default port at time of installation on target host. If we need to modify the port then we can follow below steps to change the port number.

Stop the EM Agent
$ $AGENT_HOME/bin/emctl stop agent

Modify the $AGENT_HOME/sysman/config/emd.properties file in the EM Agent home directory:

For example:
EMD_URL=http://example.com:3872/emd/main

Modify the <port number> in the EMD_URL property so the EM Agent uses a new unused port on the target host.

EMD_URL=http://example.com:3883/emd/main

Start the EM Agent.

$ $AGENT_HOME/bin/emctl start agent

Check the EM status

Exadata Patching Strategy

Let's have an overview of Exadata Machine patching.

1. Check latest patches available on Oracle note ID - 888828.1 
2. Download the required patches
3. Review the patch README file
4. Make step by step plan to apply patch for all the component
5. Run Exachk utility before the patch application, analyse the report and correct wherever it's required.
6. Automate the patch application process based on rolling or non-rolling method
7. Apply the patch

Resource Control in Exadata

We can check enabled resources (CPUs, RAM) with below command on exadata.

Sample output from one storage cell of Exadata 1/8th Rack

[root@cell01 ~]# /opt/oracle.SupportTools/resourcecontrol -show

[INFO] Validated hardware and OS. Proceed.

[SHOW] Number of cores active per socket: 3

[SHOW] Number of harddisks active - 6.

[SHOW] Number of flashdisks active - 8.

1/8th Exadata Rack comes with same hardware as quarter rack, but only half of the resources would be enabled on each 1/8th storage cell So, if in future if we need to upgrade 1/8th rack to quarter rack then we can use this utility to enable rest all the resources.

Above example shows all the enabled resources on one storage cell.