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

Useful Oracle Exadata Metalink Notes

Thought to list down some Exadata Document metalink which you can keep as a reference for lifetime.

Doc ID	Document Description
888828.1	Exadata Database Machine and Exadata Storage Server Supported Versions
1270094.1	Exadata Critical Issues
1353073.1	Exadata Diagnostics Collection Guide
1187674.1	Master Note for Oracle Exadata Database Machine and Oracle Exadata Stororage Server
1483344.1	Exadata Platinum Customer Outage Classifications and Restoration Action Plans
1571965.1	Maximizing Availability with Engineered Systems - Exadata
1262380.1	Exadata Testing Practices and Patching Explained
1306814.1	Oracle Software Patching with OPLAN
1110675.1	Oracle Exadata Database Machine Monitoring
1070954.1	Database Machine Healthcheck (Exachk)
1094934.1	Best Practices for Data Warehousing on the Database Machine
1269706.1	Best Practices for OLTP Applications on the Database Machine
1071221.1	Oracle Sun Database Machine Backup and Recovery Best Practices
1054431.1	Configuring DBFS on Oracle Exadata Database Machine
1084360.1	Bare Metal Restore Procedure for Compute Nodes on an Exadata Environment (Linux)
1339769.1	Master Note for Oracle Database Resource Manager
960510.1	Data Guard Transport Considerations on Oracle Database Machine
1551288.1	Understanding ASM Capacity and Reservation of Free Space in Exadata
401749.1	Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration
1009715.1	Integrated Lights Out Manager (ILOM) CLI Quick Reference
1070954.1	Oracle Exadata Database Machine exachk or HealthCheck
1317159.1	Changing IP addresses on Exadata Database Machine
1244344.1	Exadata Starter Kit
1537407.1	Requirements and restrictions when using Oracle Database 12c on Exadata Database Machine
1551288.1	Understanding ASM Capacity and Reservation of Free Space in Exadata
1459611.1	How to Calculate Usable_FILE_MB / REQUIRED_MIRROR_FREE_MB
361468.1	HugePages on Oracle Linux 64-bit
761868.1	Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues
10386736	Documentation for Exadata 11.2 & 12.1

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

Study Guide for 1Z0-027 Certification

Exadata Database Machine Overview

• Identify the benefits of using Database Machine for different application classes
• Describe the integration of the Database Machine with Oracle Database Clusterware and ASM
• Describe Exadata Storage Server and the different Database Machine configurations
• Describe the key capacity and performance specifications for Database Machine
• Describe the key benefits associated with Database Machine

Exadata Database Machine Architecture

• Describe the Database Machine network architecture
• Describe the Database Machine software architecture
• Describe the Exadata Storage Server storage entities and their relationships
• Describe how multiple Database Machines can be interconnected
• Describe site planning requirements for Database Machine
• Describe network requirements for Database Machine

Key Capabilities of Exadata Database Machine

• Describe the key capabilities of Exadata Database Machine
• Describe the Exadata Smart Scan capabilities
• Describe the capabilities of hybrid columnar compression
• Describe the capabilities and uses of the Smart Flash Cache
• Describe the capabilities of the Smart Flash Log
• Describe the purpose and benefits of Storage Indexes
• Describe the capabilities and uses of Exadata Secure Erase

Exadata Database Machine Initial Configuration

Exadata Documentation Download

You can find all the Exadata documentation on Exadata storage server itself on below location.

/usr/share/doc/oracle/Exadata

In case you don’t have Exadata access or you don’t have Exadata in your organization than you can download Exadata documentation from metalink.

Download patch 10386736 from metalink. All the latest versions are available on this patch.

click on image to enlarge

Remove Exadata Flash Grid disk and configure as normal Flash

In previous blog we have created Grid Disk on Flash Disk. Here we are removing Flash Grid Disk and configuring normal Flash Cache.

Step-1
Remove or move database or any contents to some another ASM Disk group

Step-2
Drop ASM disk group which was create on flash disk

Step-3
Drop all the flash grid disk

CellCLI> drop griddisk all prefix=flashdisk force
GridDisk FlashDisk_FD_00_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_01_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_02_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_03_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_04_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_05_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_06_exa1cel01 successfully dropped
GridDisk FlashDisk_FD_07_exa1cel01 successfully dropped

Step-4

Configure Exadata Flash Disk as Grid Disk

We can use Flash Cache as Grid Disk to create the ASM disk group and can keep the database on flash disk but this is not reccomended solution as flash cache performs better to cache frequently access data rather than creating database on it.

If you want put your database on Flash cache than below are the steps to configure FLASH CACHE as ASM disk.

Steps:

1. Drop existing flash cache

2. Create flashcache with required size

3. Create grid disk from Flash Cache

4. Create ASM disk group on top of the Flash Griddisk

5. Put your database on ASM disk group which has been configured from Flash grid disk

Step-1

CellCLI>drop flashcache

If it gives error like below

CELL-02769: Cannot complete flash cache drop on: FD_03_ex01cceladm03. 

Received error: CELL-02732: There may be data not synchronized to the grid disks (dirty data) in flash cache.

Use ALTER FLASHCACHE to flush the dirty data and to stop caching, and then retry the command.

This error comes because flash cache mode has been configured as writeback hence some of the dirty data are still not written to the grid disks.

To overcome from this error flush all the flashcache

CellCLI>  alter flashcache all flush

Flash cache exa1cel01_FLASHCACHE altered successfully

CellCLI> drop flashcache 

Flash cache exa1cel01_FLASHCACHE successfully dropped 

Step-2

How to move a datafile from one ASM disk group to another

Explaining how to move datafile from one ASM DG to another.

Note: Database must be in archivelog mode before doing this activity.

1. Find the name of the datafile that you're going to migrate

SQL> SELECT file_name FROM dba_data_files;

FILE_NAME

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

+DATA/mydb/datafile/system.312.825938943

+DATA/mydb/datafile/sysaux.313.825938957

+DATA/mydb/datafile/undotbs1.314.825938971

+DATA/mydb/datafile/undotbs2.315.825938991

+DATA/mydb/datafile/users.317.825939005

+DATA/mydb/datafile/mydb01.dbf

+RECO/mydb/datafile/mydb_tbs.7665.836338453 <Moving this file from +RECO to +DATA

7 rows selected.

2. Take the datafile offline

SQL> Alter database datafile '+RECO/mydb/datafile/mydb_tbs.7665.836338453' offline;

Database altered.

3. Copy the file using RMAN:

Command to read the file contents of tar and zip file in Unix

#unzip -l read.zip

#tar -tvf read.tar

#tar -ztvf read.tar.gz

#tar -jtvf read.tar.bz2

Oracle Clusterware Log Files (11.2)

Below are a list of Clusterware log files and their associated process that can be used to diagnose/resolve errors.

For the below log file names server hostname is "dbserver01" and Grid Infrastructure is installed under /u01/app/11.2.0/grid/ (UNIX).

Summary of all Clusterware events on this host

$GI_HOME/log/dbserver01/alertdbserver01.log

Oracle High Availability Services Daemon OUT

$GI_HOME/log/dbserver01/ohasd/ohasdOUT.log

Oracle High Availability Services Daemon

$GI_HOME/log/dbserver01/ohasd/ohasd.log 

Cluster Ready Services OUT

$GI_HOME/log/dbserver01/crsd/crsdOUT.log

Cluster Ready Services Daemon

$GI_HOME/log/dbserver01/crsd/crsd.log

Cluster Synchronisation Service OUT

$GI_HOME/log/dbserver01/cssd/cssdOUT.log

Cluster Synchronisation Service Daemon

Error while loading shared libraries: libclntsh.so.11.1

Error:

error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

Solution:

Export Library path and try again. It will work.

$LD_LIBRARY_PATH=$ORACLE_HOME/lib

$export LD_LIBRARY_PATH

What is Total Cost of Ownership? (TCO)

Total cost of ownership (TCO) is a financial estimate intended to help buyers and owners determine the direct and indirect costs of a product or system.

Total Cost of Ownership is the combined hard and soft costs of owning networked information assets. 'Hard' costs include items such as the purchase price of the asset, implementation fees, upgrades, maintenance contracts, support contracts, and disposal costs. These costs are considered 'hard costs' because they are tangible and easily accounted for. Even more significant in most environments however, are the 'soft' costs related to management, support, training, hidden costs, and downtime. Because they don't occur at acquisition time, they are often overlooked in budgets, often leading to unexpected increases or worse, a transfer of management and support responsibility to end users.

Although many companies factor TCO into the purchasing equation, they often underestimate the hidden costs of a new technology solution, which can result in negative consequences. For example, if don’t have the resources you need to adequately maintain a solution, you may skip upgrades and patches required to keep the solution running securely and at peak performance. Or, if you misjudge the time and expense needed to train employees on a new product or service, they may never use it productively.
While TCO helps you to determine hidden costs of a new technology solution, return on investment (ROI) analysis helps to illuminate benefits that may not be readily apparent, such as improved employee productivity or increased customer satisfaction. ROI assessments can be more subjective in nature than TCO, because these indirect benefits are usually harder to measure than direct costs.

When two solutions provide roughly equivalent benefits over the solution lifecycle, but have different types of costs associated with acquisition, maintenance and operation, a TCO comparison gives you a framework to better evaluate competing solutions to a problem, and avoid getting stuck with hidden costs and unwanted surprises.
For instance, a cloud or software-as-a-service (SaaS) customer management solution may provide business benefits very similar to what an in-house customer management solution would provide. However, TCO over a given time period may vary greatly. That’s because the very different business and delivery models and the cost and pricing structures for cloud computing and on-premise solution significantly affect TCO.

For example, on-site solutions usually require significant upfront capital expenditures for hardware, software and application software, along with IT resources to install and configure these components. As a result, first-year costs for on-site solutions are often much higher than those associated with SaaS or cloud computing solutions, and total costs to maintain and manage on-site infrastructure and solutions continue to be a factor over time. On the flip side, TCO analysis may actually favor on-site solutions as the number of users rises and the total time period factored into the calculation increases.

What to Consider

Think about your business and how long you expect to be using a particular solution. In the case of a core business solution, such as accounting or financial, many companies look at a TCO a period of four or five years (generally thought of as the useful life of hardware and software without the need for major replacements).
In less core or strategic areas — which will vary from business to business — you may want to look at TCO over a shorter time period. Regardless, TCO calculations usually include several categories and components, such as:

• Planning and selection: How long will it take to evaluate the solution, the vendor and service level agreements (if applicable)? Consider whether you can try the product for free and/or if you need to invest money or resources to set up a test environment.

• IT infrastructure requirements: For on-site solutions, do you need to buy hardware and software upfront to run the solution? What associated expenses will you have for space, power and cooling? Consider if you will you need to add, shift or outsource IT personnel to manage and maintain the infrastructure, and how much this will cost. For a SaaS or cloud solution, do you need to upgrade or add networking capabilities or bandwidth?

• Application subscription or license costs: What is the per user charge for the license (on-site) solution, or the per user subscription fee (cloud or SaaS solution)? Are ongoing maintenance costs for patches, bug-fixes, upgrades, etc. included in this price or billed separately?

• Application design, configuration and implementation: What resources (internal and/or external) will it take to design and configure the solution so it fits your business needs? Factor in relevant data migration, integration and customization costs, and any system testing necessary.

• Administration and maintenance: For an on-site solution, what is required to transition daily system administration to your internal staff? How much time, resources and money will you need to invest to manage, upgrade, trouble-shoot, patch, etc. over the solution lifecycle?

• Training costs: What IT administrative training and/or end-user costs are involved to get everyone on board and productive in using the solution.

While TCO is very important for most companies, you should also consider other factors — including contract terms, service level agreements, data security requirements and customization and integration needs — just to name a few. Many companies under-invest when it comes to thoroughly evaluating IT solution requirements and options.

You can get TCO comparision between Oracle Exadata and IBM Power system HERE...

Start and Stop Cell Server Processes

Cell services automatically starts with cell boot. If manually start and stop required than we can follow below procedure.

1. Start and stop through OS command

Stop:

Login as a root or celladmin and execute below command to stop services. 

[root@excell01 ~]# service celld stop

Stopping the RS, CELLSRV, and MS services...
The SHUTDOWN of services was successful.

Start:

[root@excell01 ~]# service celld start

Starting the RS, CELLSRV, and MS services...
Getting the state of RS services...
running
Starting CELLSRV services...
The STARTUP of CELLSRV services was successful.
Starting MS services...
The STARTUP of MS services was successful.

2. We can also manage this services with cellcli command utility as given below

Stop:

[root@excell01 ~]# cellcli -e alter cell shutdown services all

Start:

[root@excell01 ~]# cellcli -e alter cell startup services all


3. We can shutdown all services on all the cell servers with the use of dcli utility on DB node as below:

Stop:

[root@exdb01]#dcli -g cell_group -l root "cellcli -e alter cell shutdown services all"

Start:

[root@exdb01]#dcli -g cell_group -l root "cellcli -e alter cell startup services all"

Here cell_group is the list of cell server which contains IP of all the cell.

Verify Hardware and Firmware configuration on Exadata

Database Node

#/opt/oracle.SupportTools/CheckHWnFWProfile

Output would be as below:

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

If any result other than "SUCCESS" then investigate and correct the condition.

Verify on Cell Server

CellCLI> alter cell validate configuration

The output will be like :  Cell RanDomcel08 successfully altered

If any result other than "successfully altered" then investigate and correct the condition.

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 ##

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