Jeyaseelan Blog

Dear Friends, The content in this blog are purely based on my own opinion ,it is not reflecting any of my current or previous employers materials or official documents. All my posts here are not warranted to be free of errors. Please use at your own risk and after thorough testing in your environment. If you feel that i am violating any of the company's policies or documents, kindly mail me at jeyaseelan.hi@gmail.com,I am happy to take out those content from this blog.

Showing posts with label Exadata. Show all posts

Friday, 15 March 2019

OPatch lsinventory command doesnot show RAC details

Please use the below command to get the remote nodes patching details

$ORACLE_HOME/OPatch/opatchauto report -format xml -type patches -remote

===============================================================
This is applicable to all releases of OPatch.
The 12.2.0.1.13 release (and later) of OPatch, which is used for all releases 12.1.0.x and later
The 11.2.0.3.18 release (and later) of OPatch, which is used for all releases 11.2.0.1 - 11.2.0.4
Beginning with these releases of OPatch, OPatch will only support patching/listing inventory for local node of a RAC cluster. There will be no propagation to other nodes in cluster.

1. In Opatch 12.2.0.1.13 and Opatch 11.2.0.3.18, OPatch command option “-all_nodes” will be no-op and existed in -help.
If opatch command is being called with option “-all_nodes”, Opatch will print out the warning on console msg as:
""OPatch was called with -all_nodes option. The -all_nodes option is being deprecated. Please remove it while calling OPatch."
2. In future release Opatch 12.2.0.1.14 (and later) and Opatch 11.2.0.3.20 (and later) , Option “-all_nodes” will be removed from -help, user will get syntax error if specify the option.
Alternative Feature of -all_node is available for Multi-Node GI/RAC :

$ORACLE_HOME/OPatch/opatchauto report -format xml -type patches -remote
(The remote command will get a list of patches from all nodes)
===========================================================

Refer:
GI/RAC/Single Instance Announcing Deprecation of OPatch Command Option "-all_nodes" (Doc ID 2331762.1)

Tuesday, 6 November 2018

Exadata - centralized management utilities

Exadata has the following utilitities which facilitates centralized management across an Oracle Exadata System Software realm
   dcli
   cellcli
   dbmcli


=> dcli    :utility runs commands on multiple compute nodes and cells in parallel threads
=> cellcli :The Cell Control Command-Line Interface (CellCLI) utility is the command-line administration tool for Oracle Exadata Storage Server.
=> dbcli    :utility is the command-line administration tool for configuring database servers,

dcli examples:
Assume dbs_group file contains compute nodes & cell_group file contains cell servers
cat dbs_group
    db1comp0101.yg.com
    db1comp0102.yg.com
    db1comp0103.yg.com
    db1comp0104.yg.com
    db1comp0105.yg.com
    db1comp0106.yg.com
    db1comp0107.yg.com
    db1comp0108.yg.com

cat cell_group
    cell1server0101.yg.com
    cell1server0102.yg.com
    cell1server0103.yg.com
    cell1server0104.yg.com
    cell1server0105.yg.com
    cell1server0106.yg.com
    cell1server0107.yg.com
    cell1server0108.yg.com
    cell1server0109.yg.com
    cell1server0110.yg.com
    cell1server0111.yg.com
    cell1server0112.yg.com
    cell1server0113.yg.com
    cell1server0114.yg.com

1. to run a command in all compute nodes
   dcli -g <group_file> -l <user> <command>
   dcli -g dbs_group -l oracle df -h

2. to run a command in all cell servers ( assume oracle has ssh key set for cellmonitor account)
   dcli -g <group_file> -l <user> <command>
   dcli -g cell_group -l cellmonitor cellcli -e list griddisk

3. to copy a file from one compute node to all computes

   dcli -g dbs_group -l oracle -f <filename> -d <target_location>
   dcli -g dbs_group -l oracle -f /tmp/p27681568_180000_Linux-x86-64.zip -d /u01/app/oracle/patch/

4. unzip the files

   dcli -l oragrid -g dbs_group unzip -oq -d <target_directory> <patch_file>
   dcli -l oragrid -g dbs_group unzip -oq -d /u01/app/oracle/patch/ /u01/app/oracle/patch/p27681568_180000_Linux-x86-64.zip

Thursday, 1 November 2018

How to onboard ZFS ( Backup appliance) into OEM EM12c/EM13c

These steps prepared based on Oracle documentation to enable Monitoring for Oracle ZFS Appliance in Oracle Enterprise Manager 12c Version 12.1.0.5.

OMS Version: 12.1.0.5
Plugin version : 12.1.0.6/12.1.0.7
ZFS plugin : 2013.06.05.7.14,1-1.1/ 8.7.14

1 . UNIX SA Task: Preparing Oracle ZFS Storage Appliance for Oracle Enterprise Manager Monitoring

Run the Configure for Oracle Enterprise Manager Monitoring workflow, entering and recording the password for the oracle_agent user. You can rerun the workflow later to reset the worksheet, re-enable data sets, or recreate the user.

When you receive the message that oracle_agent already exists and you get asked whether to recreate the user, please answer NO.

The following occurs when the workflow is run:

A restricted role user named oracle_user is created for use by the monitoring agent. The restricted access role associated with the oracle_agent user is also named oracle_agent.
An analytics worksheet is automatically created to ensure smooth communication between the Cloud Control administrator and the storage administrator tasked with administering the target Oracle ZFS Storage Appliance.

     2. Got to Configuration/USERS and edit the user oracle_agent by clicking the pencil on the very right side of the user’s entry.
      3. Ensure that the “Kiosk user” checkbox is unchecked.
      4. Press APPLY button in case you changed the user.
      5. Enable TLS V1.0 Protocol ( Configuration => HTTPS=> Edit SSL/TSL => check TLS V1.0 Protocol ) ( this step is required only if we use OEM 12.1.0.5 + ZFS plugin
         ( 12.1.0.6/12.1.0.7 , for EM13c, we can skip this step)
      6.Communicate the oracle_agent password to the OEM Administrator

2: OEM Admin Task : Discover / Add ZFS Storage Appliances to OEM 12c

Task 2.1 : Push the plugin to the target agent from OEM
    1. Login to OEM12c Enterprise Manager with an Account with OEM ADMIN privilege
    2 Select Setup → Extensibility → Plug-ins → Servers,Storage & Network → Oracle ZFS Storage Appliance
    3. Select Actions → Deploy On → Management Agent → Continue
    4. Enter the "Management Agent" ( zfsappliance.kh.com:3830) → continue → Next
    5. Once plugin installation is successful.
    6. Login to the agent host ( zfsappliance.kh.com)
    7. Create blackout & Stop agent
          . oraenv
           <AGENT/AGENT12C>
           emctl start blackout ZFS_ONBOARD -nodelevel -d 01:00
           emctl stop agent
    8. cd /data/oracle/product/agent12c/plugins/oracle.sun.oss7.agent.plugin_12.1.0.7.0/scripts/emx/sun_storage_7000
            ( <agent_home>/plugins/oracle.sun.oss7.agent.plugin_12.1.0.7.0/scripts/emx/sun_storage_7000/   )

    9. Backup current Oracle_Grid.jar as Oracle_Grid.jar.bak
         ( cp Oracle_Grid.jar Oracle_Grid.jar.bak )

    10. Download the latest Oracle_Grid.jar [ This step is required only if we use OMS 12.1.0.5 + plugin version 12.1.0.6/12.1.0.7 + ZFS image version > 8 ( 8.1.14) ]
              ( Alternatively this jar can be downloaded from Note 2384693.1 )

   11. Install latest JDK ( take backup of the current JDK)
this patch can be downloaded from MOS 1944044.1

           $AGENT_HOME/jdk/bin/java -version
         cd $AGENT_HOME
          unzip p19138237_16085_Linux-x86-64.zip

         ./jdk-6u85-linux-x64.bin

     $AGENT_HOME/jdk/bin/java -version

11. start agent & remove blackout
        emctl start agent
        emctl stop blackout ZFS_ONBOARD

Task 2.2 : Discover the target in OEM

Login to OEM12c Enterprise Manager with an Account with ADD TARGET privilege
Select Setup -> Add Target -> Add Targets Manually
Select Add Targets Declaratively by Specifying Target Monitoring Properties
As Target Type choose Oracle ZFS Storage Appliance
For Monitoring Agent click the Search Button and select as

Management Agent:zfsappliance.kh.com:3830

     6. Press Add Manually
   7. In the opening window enter the target details as follows:
        Target
           Target Name: zfsappliance.kh.com
           Monitoring Target Credentials
           UserName: oracle_agent
            Password / Confirm Password: <oracle_agent password>
       Properties            Management Port: 215
            Storage Server Name or IP Address: zfsappliance-netman.kh.com
      Global Properties
           Department: Standard Database Support
           Lifecycle Status: Production
           Location: Germany
           Contact : DB_GLOBAL_DATABASE
           Press OK

8.Verify that target has been added and appears as up and running.

9. Repeat steps 2) – 8) for the all the heads in zfs appliance

Thursday, 19 April 2018

SNAPSHOT DATABASES in EXADATA - Quick Clone & Storage Optimization

Clone multiple snapshot databases in exadata platform quickly with less additional disk space .

Traditional Method:

To create snapshot databases in a production system, we create test master and snapshot databases on a non-Exadata system, such as ZFS .In certain cases, these databases are a full copy that
consumes as much storage as its source.

We have limitation in ZFS as we most of the time use it for backup purpose with tight storage.
If the clones are a full copy of the production database, this is expensive in terms of the amount of storage consumed and the time it takes to create the clones. Imagine creating ten clones for a
multi-terabyte database and it is easy to see why this approach does not scale.
Another drawback to this approach is that Oracle Exadata Storage Server Software features such as Smart Scan, Smart Logging, and Smart Flash are not available.

Exadata Snapshots:
To solve these problems, Oracle has come out with a feature called Exadata snapshots.

Exadata snapshots are ideal for creating space-efficient read-only or read-write clones of an Oracle database that you can use for development, testing, or other non-production purposes, and when multiple clones are required because of disk space and time savings.depicts the space required for an Exadata snapshot.

An Exadata snapshot is based on a test master, which is a full clone of the source database. The test master is the only full copy of the source database. From a single test master we can create multiple Exadata snapshots with minimal additional storage and minimal effort. Each Exadata snapshot uses a small fraction of the disk space required for the test master and can be created or dropped in seconds. Each Exadata snapshot is a logical copy of the test master.

High Level Steps to create snapshot databases in Exadata

1. Create Sparse Grid Disks in the storage server
2. Create ASM Sparse DG.
3. Setup Test Master ( either full clone on a DG with ASM ACL enabled or converting an existing Full clone or Standby Database to a test master)
4.Create multiple Snapshot Databases ( it can be a PDB or full CDB or non-CDB)

Picture : Exadata Snapshot Databases in a Non-Multitenant Environment

Prerequisites for Exadata Snapshots:

Grid Disks should be spare disks. (mention virtual size while creating sparse Grid Disks)
ASM Disk Group should be on Sparse Disk Groups

Storage nodes must be X3 or later
Exadata Storage Server software 12.1.2.1.0 or later for Exadata Storage and Database Servers
Oracle Grid Infrastructure & Database 12.1.0.2.0 BP5 or later
The data files for the snapshot database and the parent database must be on the same ASM cluster.
The db_block_size must be at least 4K and be a multiple of 4K.

High Level Steps to create snapshot databases in Exadata

Detailed steps to create Snapshot databases
1. Sparse Grid Disks:

A sparse ASM disk group is composed of sparse grid disks. A sparse grid disk has a virtual size attribute as well as physical size.The maximum allowed physical size of all grid disks on a cell disk is 4 TB. The maximum allowed virtual size is 100 TB.we need to create sparse grid disks before creating a sparse ASM disk group.

For example: to create 108G grid Disk with 1000G Virtual Size.
create griddisk all harddisk prefix=DATA_SPAR, size=108G, virtualsize=1000G;

2.ASM Sparse Disk Groups:
Exadata snapshots utilize Oracle ASM sparse disk groups: sparse data files can be created only in Oracle ASM sparse disk

For example : To create ASM sparse Disk Group

create diskgroup DATA_SPARSE high redundancy disk 'o/*/DATA_SP_*' attribute
'compatible.asm'='12.1.0.2',
'compatible.rdbms'='12.1.0.2',
'au_size'='4M',
'cell.smart_scan_capable'='true',
'cell.sparse_dg'='allsparse',
'appliance.mode' = 'TRUE';

compatible.asm must be set to 12.1.0.2 or higher.
compatible.rdbms must be set to 12.1.0.2 or higher.
cell.sparse_dg must be set to "allsparse". This identifies the disk group to ASM as being made up of sparse grid disks.
appliance.mode must be set to true.

3. Setting Up the Test Master:

Creating an Exadata snapshot from the test master is as simple as recording the parent file name in the child file header, an operation that completes in seconds and requires minimal disk space.

Additional disk space is consumed only when the user of the snapshot begins to change data. Only new data is written to data blocks that are allocated to the snapshot on write. All requests for data that has not changed are serviced by the data blocks of the test master.

Multiple users can create independent snapshots from the same test master. This enables multiple development and test environments to share space while maintaining independent databases for each user

Steps to create Test Master

3.1.Build the full clone of a database using RMAN backup/restore, data pump, standby ( please not when we use standby we cannot enable log-apply & log-shiping) or any of the other methods typically used to create a full clone of a database.

3.2.Enable access control on the disk group that contains the test master's data files. The disk group must be on Exadata.
SQL> ALTER DISKGROUP DATA_SPARSE SET ATTRIBUTE 'ACCESS_CONTROL.ENABLED' = 'TRUE';

3.3. After creating the full clone, remove write permissions on all the data files to help prevent accidental overwrite.

SQL commands in ASM instance only allow to set file permissions to read only. You cannot remove write permissions in SQL.

SQL> ALTER DISKGROUP DATA_SPARSE set permission owner=read ONLY, group=read ONLY, other=none for file 'FILENAME';

3.4.Setting the Ownership of the Test Master Data Files
After you have cloned the database to create a test master database, set an operating system user as the owner of the disk group, and make the operating system user as the owner of the test master's data files.

Specify an operating system user as the owner of the disk group:
SQL> ALTER DISKGROUP DATA_SPARSE ADD USER 'perf_mon';

Make the operating system user the owner of the test master's data files:
SQL> ALTER DISKGROUP DATA_SPARSE SET OWNERSHIP OWNER='perf_mon' FOR FILE
'+DATA_SPARSE/TESTMASTER/DATAFILE/system.378.165163315';
SQL> ALTER DISKGROUP DATA_SPARSE SET OWNERSHIP OWNER='perf_mon' FOR FILE
'+DATA_SPARSE/TESTMASTER/DATAFILE/sysaux.374.165163215';
SQL> ALTER DISKGROUP DATA_SPARSE SET OWNERSHIP OWNER='perf_mon' FOR FILE
'+DATA_SPARSE/TESTMASTER/DATAFILE/sysabs.371.165163015';
SQL> ALTER DISKGROUP DATA_SPARSE SET OWNERSHIP OWNER='perf_mon' FOR FILE
'+DATA_SPARSE/TESTMASTER/DATAFILE/tbs_1.372.165161315';

4.Sparse Database and Sparse Files:
A database consists of the following files:

control files
online redo logs
temp files
data files

In a sparse database, such as an Exadata snapshot, its data files are sparse files. A sparse file contains only changes made to blocks from the parent file (the parent file remains unchanged) and maintains a pointer to the parent file for access to unchanged data.
The Exadata snapshot has its own copy of the other database files (control files, online redo logs, and temp files). These other database files will not be sparse files.

4.1) Create snapshot PDB database

4.1.1.In SQL*Plus connect to the cdb$root.

4.1.2.Close the test master PDB in all instances.
SQL> alter pluggable database PDB1TM1 close instances=all;

4.1.3.Open the test master PDB in the local instance in read only mode.
SQL> alter pluggable database PDB1TM1 open read only;

4.1.4.Create an Exadata snapshot PDB of the test master.
SQL> create pluggable database PDB1S1 from PDB1TM1 tempfile reuse
create_file_dest='+DATA_SPARSE' snapshot copy;

create_file_dest must specify the name of a sparse disk group; this ensures the files for the Exadata snapshot are created in the correct disk group. The "snapshot copy" clause creates the PDB as a snapshot rather than a full PDB clone.

4.2) Create non CDB snapshot database

   4.2.1. Create control file backup to trace
     4.2.1.1.) SQL> select value from v$diag_info where name = 'Default Trace File';
     VALUE
     --------------------------------------------------------------------------
     /u01/app/oracle/diag/rdbms/TESTMASTER/TESTMASTER1/trace/TESTMASTER1_ora_25778.trc

4.2..1.2) alter database backup controlfile to trace;
4.2.1.3 ) Edit the controlfile (The control file should be created with Exadata snapshot database name, new log file names, and the test master's data file names )

CREATE CONTROLFILE REUSE SET DATABASE SPARSETest RESETLOGS
      MAXLOGFILES 32
      MAXLOGMEMBERS 2
      MAXINSTANCES 1
      MAXLOGHISTORY 908
LOGFILE
    GROUP 1 '+DATA/SPARSETEST/t_log1.f' SIZE 100M BLOCKSIZE 512,
      GROUP 2 '+DATA/SPARSETEST/t_log2.f' SIZE 100M BLOCKSIZE 512
DATAFILE
      '+DATA_SPARSE/TESTMASTER/DATAFILE/system.257.865863315',
      '+DATA_SPARSE/TESTMASTER/DATAFILE/sysaux.258.865863317',
      '+DATA_SPARSE/TESTMASTER/DATAFILE/sysext.259.865863317',
      '+DATA_SPARSE/TESTMASTER/DATAFILE/tbs_1.256.865863315'
CHARACTER SET WE8DEC;

4.2.2. Generate dnfs files
set newpage 0
set linesize 999
set pagesize 0
set feedback off
set heading off
set echo off
set space 0
set tab off
set trimspool on
spool rename_files.sql
select 'EXECUTE dbms_dnfs.clonedb_renamefile
('||''''||name||''''||','||''''||replace(replace(replace(name,'.','_'),'TESTMASTER','JOHNTEST'),'DATA','DATA_SPARSE')||''''||');' from v$datafile;
exit

The query above builds a file called rename_files.sql that contains statements for each data file similar to the following:
EXECUTE dbms_dnfs.clonedb_renamefile (
'+DATA/TESTMASTER/DATAFILE/system.257.865863315',
'+DATA_SPARSE/SPARSETEST/DATAFILE/system_257_865863315');

　
4.2.3.Shut down the test master.
SQL> shutdown;

4.2.4. Create an init.ora file for the Exadata snapshot database.
$ cp init_TestMaster.ora snap_init.ora

Modify snap_init.ora with the new database name and new control files, for example:
db_name = SPARSETest
control_files = '+DATA_SPARSE/SPARSETEST/control1.f'

4.2.5. In the following example the script is named crt_ctlfile.sql.
SQL> @crt_ctlfile
Run the script you modified in step 4.2.1. All the files must be renamed prior to opening the Exadata snapshot database.

4.2.6 Rename files
SQL> @rename_files
This script modifies the permissions of the test master database files in ASM, marking them as READONLY.
The dbms_dnfs.clonedb_renamefile procedure, which is called by rename_files.sql, sets up the parent-child relationship between the test master database and the snapshot database, and renames the filenames in the snapshot database's control file.

4.2.7 Open the Exadata snapshot database with the RESETLOGS option:
SQL> ALTER DATABASE OPEN RESETLOGS;

Confirm that the Exadata snapshot files are child files of the test master database. Connect using SQL*Plus as sysasm to the Exadata snapshot, and run the following command:
SQL> a.select filenumber num, CLONEFILENAME child, SNAPSHOTFILENAME parent
from x$ksfdsscloneinfo;

The following is an example of the output from the query:
NUM CHILD                                        PARENT
------------------------------------------------------------------
1   +DATA_SPARSE/SPARSETEST/DATAFILE/system_257_865863315
+DATA_SPARSE/TESTMASTER/DATAFILE/system.257.865863315
2   +DATA_SPARSE/SPARSETEST/DATAFILE/sysaux_258_865863317
+DATA_SPARSE/TESTMASTER/DATAFILE/sysaux.258.865863317
3   +DATA_SPARSE/SPARSETEST/DATAFILE/sysext_259_865863317
+DATA_SPARSE/TESTMASTER/DATAFILE/sysext.259.865863317
4   +DATA_SPARSE/SPARSETEST/DATAFILE/tbs_1_256_865863315
+DATA_SPARSE/TESTMASTER/DATAFILE/tbs_1.256.865863315

4.2.8 Log in using SQL*Plus to the Exadata snapshot database, and add temp files to the TEMP tablespace. This will be a full size temp file, not sparse.
alter tablespace TEMP add tempfile '+DATA_SPARSE' size 10g;

Tuesday, 3 April 2018

How to remove all disks from a cellserver

Assume we have the following 3 Disks groups
   DATA ( Database)
   FRA ( for Archive log/FRA )
   SYSTEM ( After os partition )

Verify if you have enough usable space ( very important thing before you drop)

Before you drop the disks, you need to check the DG redundancy and the free usable space whether after the disk drop, the usable free space can accommodate the rebalance

A,1 Get the below values from the ASM instance

select sum(total_mb) from v$asm_disk where failgroup='CELLSERVER01' and lower(name) like '%data%';
select sum(total_mb) from v$asm_disk where failgroup='CELLSERVER01 and lower(name) like '%fra%';
select sum(total_mb) from v$asm_disk where failgroup='CELLSERVER01' and lower(name) like '%system%';

A.2 Depends on the redundancy divide the value by 3 or 2 ( High – 3, normal -2) )

select sum(total_mb) from v$asm_disk where failgroup='CELLSERVER01 and lower(name) like '%fra%';

SUM(TOTAL_MB)

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

344080 total MB for one cell in FRA without redundancy (with normal redundancy approx 344/2 = 172 GB free space needed) ( High – 3, normal -2)

A.3) Validate using lsdg

ASMCMD> lsdg
State    Type    Rebal Sector Block       AU   Total_MB    Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N         512   4096 4194304 298592448 124655796          3554672        60550562              0             N DATA/
MOUNTED NORMAL N         512   4096 4194304    2422560     118196            34608           41794              0             Y FRA/
MOUNTED NORMAL N         512   4096 4194304   18930240   17817060           225360         8795850              0             N SYSTEM

Note : if we don't have enough usable space to accommodate the rebalance, only option is that increase DISK_REPAIR time .We may loose data if any other failgroup goes down ( 1 for normal, 2 for high)

Drop all Disks
for a cellserver maintenenace, if we want to remove all the disks from the above DGs

alter diskgroup DATA drop disks in failgroup cellserver01rebalance power 60;
alter diskgroup FRA drop disks in failgroup cellserver01rebalance power 60;
alter diskgroup SYSTEM drop disks in failgroup cellserver01rebalance power 60;

- - Monitor v$asm_operation

Add the Disks Back

( why we use two ip-addreses here? from X4 onwards infiniband is in active -active mode )

To add all disks from once cell server in a single command
       ALTER DISKGROUP DATA ADD DISK 'o/10.10.10.1;10.10.2/DATA_DG_CD*' rebalance power 60 NOWAIT;
       ALTER DISKGROUP FRA ADD DISK 'o/10.10.10.1;10.10.2/FRA_DG_CD*' rebalance power 60 NOWAIT;
       ALTER DISKGROUP SYSTEM ADD DISK 'o/10.10.10.1;10.10.2/SYSTEM_DG_CD*' rebalance power 60 NOWAIT;

- Monitor v$asm_operation

Friday, 12 April 2013

SPARC SUPER CLUSTER a General Purpose Engineered System recently unveiled by Oracle Corporation which is a combination of Exadata Storage Server and Exalogic Application server.

You may want to explore in the Internet as there are lots of documents available on this like DataSheet/ Comparison between exadata etc...

Oracle SPARC SUPER CLUSTER

Oracle Unveiled the World's First General Purpose Engineered System

It is designed, tested and delivered by Oracle

The SPARC SuperCluster T4-4 utilizes high performance technologies from Oracle Exadata
Storage Servers and Oracle Exalogic Elastic Cloud combined with SPARC T4-4 servers, ZFS Storage Appliance, and InfiniBand technology, and Oracle Solaris 11. With the addition of the SPARC SuperCluster, Oracle continues to set the standard for engineered systems: maximizing customer value with leading performance in a complete and tested
package

Interesting Super Cluster Configuration

I.SPARC T4-4 Compute Node ( Full RACK 4 Nodes)

Each SPARC T4-4 compute node configured with:

4 x eight-core SPARC T4 Processors (3.0GHz)
1 TB Memory
6 x 600 GB 10,000 RPM SAS-2 Disks
2 x 300 GB solid state disks
4 x dual-port InfiniBand QDR
4 x dual-port 10 Gb Ethernet

II.Exadata Storage Server X2-2 ( Full RACK 6 Nodes)

Each Exadata Storage Server is configured with:

Either 12 x 600 GB 15,000 RPM High Performance SAS disks or 12 x 3TB 7,200 RPM High Capacity SAS disks
12 CPU cores for SQL processing
384 GB Exadata Smart Flash Cache

III.Sun ZFS Storage 7320 Appliance ( full rack 1 )

7320 Dual Controller, each with:

2 x four-core 2.4GHz Intel® Xeon® Processors
24 GB Memory
1 x dual-port InfiniBand HCA
2 x 500 GB SATA HDD
4 x 512 GB read optimized SSD

7320 Disk Shelf with the following:

20 x 3 TB using high capacity SAS-2 7,200 RPM disks
4 x 73 GB write-optimized SSDs

IV.Sun Datacenter InfiniBand Switch 36 ( Full rac 3 )

V.Additional Hardware Components
Additional Hardware Components Included:

Ethernet management switch
42U rack packaging
2 x Redundant Power Distributions Units (PDUs)
InfiniBand cables
10G cables

Spares Included:

1 x 600 GB High Performance SAS disk or 1 x 3 TB High Capacity SAS disk
1 x 3 TB SAS disk
1 x 96 GB Exadata Smart Flash Cache card

Wednesday, 10 August 2011

Diskmon cannot handle more than 13 instances - Exadata

Diskmon process is one of the background process in exadata environment and is a part of Oracle Clusterware ready services.This takes care of the I/O Fencing.

In 11.2.0.1/11.2.0.2 version, Diskmon can handle upto 13instances. Each instance has one diskmon(ora_dskm_<sid>) process.Oracle can fork upto 13 diskmon slaves.This is a limitation in these versions.The CRS startup will hang if the exadata server has more than 13 instances. We could find the below messages in the instance's alert logfile which are failed to start.

ORA-00600: internal error code, arguments: [2116], [900], [], [], [], [], [], [], [], [], [], [] In the alert log and DISKMON logs show dskm_slave_thrd_creat1: no more slave table slots left!

Oracle has provided a patch 9790947 to support upto 62 instances and later introduced another patch 9842238 to completely remove this limitation.Exadata Patch Bundle 4 has covered the latest patch.

Monday, 1 August 2011

Why My query is not availing the offloading feature

Some of the area we need to check before we go to oracle to check if there is any known bug ?

1. Verify STORAGE clause is included in your query plan.

2. Verify if your function is offloloadabe using v$sqlfn_metadata

SQL> desc v$sqlfn_metadata
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FUNC_ID                                            NUMBER
NAME                                               VARCHAR2(30)
MINARGS                                            NUMBER
MAXARGS                                            NUMBER
DATATYPE                                           VARCHAR2(8)
VERSION                                            VARCHAR2(12)
ANALYTIC                                           VARCHAR2(3)
AGGREGATE                                          VARCHAR2(3)
OFFLOADABLE                                        VARCHAR2(3)
DISP_TYPE                                          VARCHAR2(13)
USAGE                                              VARCHAR2(30)
DESCR                                              VARCHAR2(4000)

3. Verify if the below init.ora parameters are matching
            cell_offload_processing = TRUE
            _kcfis_storageidx_disabled =FALSE ( Storage index is enabled)
             _kcfis_cell_passthru_enabled = TRUE ( perform smart IO filtering on the cell )

4. Verify if 'cell.smart_scan_capable'='TRUE' is enabled for all the disk

Friday, 29 July 2011

Why gridisks are not automatically created after replacing a diskdrive in a cellserver?

Why gridisks are not automatically created after replacing a diskdrive in a cellserver?

   if the firmware version and cellserver's image versions are different.then automatic creation of celldisk and griddisk will get failed.   because of that we may not be able to add the disks to the asm diskgroups.

1.1 How to check if the firmware is same across all the disks
   cellcli -e list physicaldisk detail => will hep us to check if any of the disk's firmware differet from others.
          name:                   27:0
         deviceId:               15
         diskType:               HardDisk
         enclosureDeviceId:      27
         errMediaCount:          0
         errOtherCount:          0
         foreignState:           false
         id:                     E0QXXB
         luns:                   0_0
         makeModel:              "SEAGATE ST360057SSUN600G"
         physicalFirmware:       0705         physicalInsertTime:     2010-06-30T15:42:20-04:00
         physicalInterface:      sas
         physicalSerial:         E0QXXB
         physicalSize:           558.9109999993816G
         slotNumber:             0
         status:                 normal
         name:                   27:1
         deviceId:               16
         diskType:               HardDisk
         enclosureDeviceId:      27
         errMediaCount:          0
         errOtherCount:          0
         foreignState:           false
         id:                     E0QYFR
         luns:                   0_1
         makeModel:              "SEAGATE ST360057SSUN600G"
         physicalFirmware:       0705         physicalInsertTime:     2010-06-30T15:42:21-04:00
         physicalInterface:      sas
         physicalSerial:         E0QYFR
         physicalSize:           558.9109999993816G
         slotNumber:             1
         status:                 normal
         name:                   27:2
         deviceId:               29
         diskType:               HardDisk
         enclosureDeviceId:      27
         errMediaCount:          0
         errOtherCount:          0
         foreignState:           false
         id:                     E0NAK9
         makeModel:              "SEAGATE ST360057SSUN600G"
         physicalFirmware:       0805         physicalInterface:      sas
         physicalSerial:         E0NAK9
         physicalSize:           558.9109999993816G
         slotNumber:             2
         status:                 normal
         name:                   27:2
         deviceId:               17
         diskType:               HardDisk
         enclosureDeviceId:      27
         errMediaCount:          47
         errOtherCount:          1
         foreignState:           false
         id:                     E0QQGD
         luns:                   0_2
         makeModel:              "SEAGATE ST360057SSUN600G"
         physicalFirmware:       0705
         physicalInsertTime:     2010-06-30T15:42:22-04:00
         physicalInterface:      sas
    2 How to reconfigure the disk which was not configured properly due to firmware mismatch.

2.1 Run any of the below command to find the problematic disk drive. The firmware state shows "unconfigured(good)"
       ( MegaCli64 is available under /opt/MegaRAID/MegaCli folder)
       MegaCli64 -CfgDsply -a0|grep 'Firmware state:'
Firmware state: Unconfigured(good), Spun Up
  or
MegaCli64 -Pdlist -aAll | grep "Slot\|Firmware"
Slot Number: 0
Firmware state: Online
Slot Number: 1
Firmware state: Online
Slot Number: 2
Firmware state: Unconfigured(good), Spun Up Slot Number: 3
Firmware state: Online
Slot Number: 4
Firmware state: Online
Slot Number: 5
Firmware state: Online
Slot Number: 6
Firmware state: Online
Slot Number: 7
Firmware state: Online
Slot Number: 8
Firmware state: Online
Slot Number: 9
Firmware state: Online
Slot Number: 10
Firmware state: Online
Slot Number: 11
Firmware state: Online
            of

2.2 identify the device id and slot number for the problematic drive
MegaCli64 -Pdlist -aAll | more
Enclosure Device ID: 27
Slot Number: 2
Device Id: 29
Sequence Number: 1
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
PD Type: SAS
Raw Size: 558.911 GB [0x45dd2fb0 Sectors]
Non Coerced Size: 558.411 GB [0x45cd2fb0 Sectors]
Coerced Size: 557.861 GB [0x45bb9000 Sectors]
Firmware state: Unconfigured(good), Spun UpSAS Address(0): 0x5000c50039e700f1
SAS Address(1): 0x0
Connected Port Number: 0(path0)
Inquiry Data: SEAGATE ST360057SSUN600G0805    E0NAK9
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Foreign State: None
Device Speed: 6.0Gb/s
Link Speed: 3.0Gb/s
Media Type: Hard Disk Device
Drive: Not Certified

2.3 Configure the diskdrive
./MegaCli64 -CfgLdAdd -R0 [27:2] WB NORA Direct NoCachedBadBBU -strpsz1024 -a0
Adapter 0: Created VD 11
Adapter 0: Configured the Adapter!!
Exit Code: 0x00

2.4 listout the status
MegaCli64 -Pdlist -aAll | grep "Slot\|Firmware"
Slot Number: 0
Firmware state: Online
Slot Number: 1
Firmware state: Online
Slot Number: 2
Firmware state: Online
Slot Number: 3
Firmware state: Online
Slot Number: 4
Firmware state: Online
Slot Number: 5
Firmware state: Online
Slot Number: 6
Firmware state: Online
Slot Number: 7
Firmware state: Online
Slot Number: 8
Firmware state: Online
Slot Number: 9
Firmware state: Online
Slot Number: 10
Firmware state: Online
Slot Number: 11
Firmware state: Online

2.5 drop the celldisk.
cellcli -e drop celldisk CD_02_nyjdvop02cel04 force

2.6 list the lun
cellcli -e list lun

2.7 Verify the size of the exisiting disks.so that we can make use of it for new griddisk creation

cellcli -e list griddisk CELL_DISK_NAME detail
         name:                   CELL_DISK_NAME
         availableTo:
         cellDisk:               CELL_DISK_NAME
         comment:
         creationTime:           2010-08-12T11:40:16-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     0000012a-66f5-73f6-0000-000000000000
         offset:                 32M
         size:                   220G
         status:                 active
2.8 Drop the problematic celldisk
cellcli -e drop celldisk CELL_DISK_NAME force

2.9 create new celldisk
cellcli -e create celldisk CELL_DISK_NMAE lun=0_2

2.10 Create grid disk
cellcli -e create griddisk GRID_DISK_NAME celldisk=CELL_DISK_NAME, size=220G

2.11 Add the disks to the DG
      verify if the disks are candidate
       select name,path,header_status,mount_status from v$asm_disk where path like '%griddisk%';
       alter diskgroup diskgroupname add disk '0/xxx/xxxxx';

2.12 ALL done

Monday, 25 July 2011

asmmodestatus of griddisk shows as UNUSED

When asmmodestatus of griddisk shows as UNUSED, the disk is never attached to any asm disks or never being used. Here are the steps to find out the cause and fix the issue.

1. cellcli -e LIST GRIDDISK attributes name, size, asmmodestatus
   -- result of the above command shows that   asmmodestatus of a disk is in UNUSED status

2. cellcli -e list alerthistory
     -- result of the above command shows that the disk was replaced recently
    *******critical "Hard disk removed. Status : NOT PRESENT*******
   **********"Hard disk replaced.****************

3. looks the replaced disk was not added to the ASM diskgroup and then it was showing the asmmodestatus as unused.

4. identify the candidate disks from the v$asm_disks and added the disks to the respective diskgroup using

alter diskgroup diskgroupname add disk '0/xxx/xxxxx';

Note: The messages in this sites are very general and not representing/referering any company's product.

Saturday, 9 July 2011

The What, Why, and When of Oracle Exadata

http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21interview-302468.html

Evolution of Exadata

Oracle Exadata
　
　Exadata was announced by Larry Ellison at the 2008 Oracle OpenWorld conference in San Francisco for immediate delivery. The main headline was that Oracle was entering the hardware business with a pre-built Database Machine, engineered by Oracle. The hardware at this time was manufactured, delivered and supported by HP. Since the acquisition of Sun Microsystems by Oracle circa January 2010, Exadata hardware utilises Sun based hardware. Oracle claims that it is the fastest database server on the planet.

Exadata is a database appliance with support for both OLTP and OLAP workloads. It was initially designed in collaboration between Oracle Corporation and Hewlett Packard where Oracle designed the database, operating system and storage software whereas HP designed the hardware for it. With Oracle's acquisition of Sun Microsystems, Oracle announced the release of Exadata Version two with improved performance and usage of Sun Microsystems Storage Systems technologies.[1]