Exadata Pre-upgrade: Backing up /, /boot & /u01 mount point

During any Exadata patching and/or Grid Infrastructure/Oracle Database upgrade we always want to keep a backup of the OS & Oracle Binaries before start changing anything in the environment.

Backup of / (root) & /boot mount point can be done using DBNU utility (MOS Note # 1553103.1).
For /u01, it is possible to follow the below procedure to backup.

Exadata Compute Node: Filesystem resize issue

Exadata documentation talks about how to resize an LVM Partitions in compute node for two different scenario -

Option 1: Extending the root LVM Partition on Systems Running Oracle Exadata Storage Server Software Release 11.2.3.2.1 or Later

Option 2: Extending the root LVM Partition on Systems Running Oracle Exadata Storage Server Software Earlier than Release 11.2.3.2.1

Obviously anyone will consider to resize the filesystem ONLINE using Option 1. But if the environment came (patched/upgraded) from an older image version (e.g. from <= 11.2.3.2.0, look for imagehistory output) but current running a newer image version, lets say 11.2.3.3.x, 12.1.2.1.x or even greater, though the image is newer but still it might not possible to do the filesystem resize online. This is mainly applicable for X2, X3 (early releases).

iLOM: Exadata Compute Node, Storage Server & IB Switches

Oracle Integrated Lights Out Manager (ILOM) provides advanced service processor (SP) hardware and software that can use to manage and monitor Exadata machine components, such as compute nodes, storage server and the InfiniBand switch. ILOM's dedicated hardware and software is preinstalled on these components.

Incremental & Concurrent Statistics Collection

Incremental Statistics Collection for Partitioned Objects

Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

-- the INCREMENTAL value for the partitioned table is set to TRUE;

-- the PUBLISH value for the partitioned table is set to TRUE;

-- the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table;

If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics, which is a much more resource intensive and time-consuming operation for large tables.

InfiniBand Diagnostic Data Collection

From Database Servers:

 # /opt/oracle.SupportTools/ibdiagtools/verify-topology  
 # ibqueryerrors.pl -rR -s PortRcvSwitchRelayErrors,PortXmitDiscards,PortXmitWait,VL15Dropped  
 # ibstat  
 # ibv_devinfo -v  
 # /opt/oracle.SupportTools/CheckSWProfile.sh -I <switch1>,<switch2>,<switch3>  

GI & DB Pre-Upgrade Data Collection

As a DBA and/or Support Consultant, we frequently work with customers to upgrade their existing Oracle Databases.

Upgrading an Oracle Database is looked like very easy now a days. But the truth is, it's not that easy. Certainly the steps or activities are simple and straight forward. But after effects (Post Upgrade) of the upgrade sometimes ruin everything.

I always prefer to give effort and time for pre-upgrade activity rather during the upgrade or even post upgrade. With proper pre-upgrade analysis and correction can lead to successful & smooth upgrade of Oracle GI & Database.

For Database Upgrade activity, I always ask the customers to provide me the following information, mainly applicable for Exadata (RAC on Linux) -

Exadata Storage Server Diagnostic Data Collection

Whenever any issue occurs with Exadata Storage Server, like SMART SCAN issues, H/W Failures, SQL getting quarantined etc. It is best to collect the bellow information immediately from the environment and if any SR has been raised with Oracle Support, upload them into the SR.

It's always best to upload the below information into the SR even if the SR engineer doesn't ask for these yet. But trust me, they will ask these now or later. So why waste time?

For one Exadata Storage Server, it might take 10-20 mins on an average to collect these information. If the same situation is observed in multiple storage server, then it might be alright to collect these information from only one or two storage servers as a start and upload into the SR.

Print messages when someone accessing a particular directory

If you ever want to print a message when some accessing a particular directory like below.

 [root@pxe-yum ~]# cd /TEST.DIR/  
 WARNING! WARNING! WARNING!  
 DO NOT -  
  SAVE, REMOVE OR ALTER any files or directories in this mount point.  
 This mount point is ONLY for SYSTEM backup.  
 [root@pxe-yum TEST.DIR]# pwd  
 /TEST.DIR  

How To: Move Oracle Database Files in 12c

If it's required to change/setup db_unique_name (if not done during database creation which I always forgot to do so), then changing the db_unique_name will create another directory in ASM and start keeping all the new files into the new directory.

For Example:
-- If the db_name=DB and no db_unique_name is set: then in ASM, the database will create files like: +DATA/DB/*/
-- If the db_name=DB and db_unique_name is set, i.e. db_unique_name=DBPR, then in ASM, the database will create files like: +DATA/DBPR/*/

As a result, we will have two directory in ASM (+DATA/DB & +DATA/DBPR) which I don't want.

Install Oracle Linux 6.x with reduced set of packages for Oracle Database 12c, Grid Infrastructure 12c, EM12c etc ...

It's always best to install the operating system with a smaller footprint which automatically make the environment more secure since there are much less to consider to secure the environment.

From the My Oracle Support Note: Linux OS Installation with Reduced Set of Packages for Running Oracle Database Server (Doc ID 728346.1): Limiting the number of installed packages to only those needed provides a smaller footprint for Linux operating system installation and will help to reduce the amount of storage used. A minimal configuration will also help to avoid unnecessary processes and services and result in reduced memory usage, along with a more secure and reliable setup overall.

ORA-32017: failure in updating SPFILE, followed by: ORA-65500: could not modify DB_UNIQUE_NAME, resource exists (12c)

I was working to setup a Data Guard in my test machine and noticed the following  error when I was trying to change the db_unique_name parameter in the primary database -

 SQL> alter system set db_unique_name=TESTPR scope=spfile sid='*';  
 alter system set db_unique_name=TESTPR scope=spfile  
 *  
 ERROR at line 1:  
 ORA-32017: failure in updating SPFILE  
 ORA-65500: could not modify DB_UNIQUE_NAME, resource exists  

umount: /u01: device is busy: even when Grid Infrastructure is down (12c)

Sometimes back, I noticed that even after shutting down GI (12.1.0.2.x) in a RAC setup, I still can't unmount the filesystem where the GI binaries are located.

This is applicable for GI version 12.1.0.2.x and even in Exadata too. Reproducible every time when GI shutdown completes.

How To: mount ISO image as yum repository

This post is just a quick how to reference about how to mount an ISO image contains Oracle Linux Installation Binaries and use that as a YUM repository to install/update the operating system.

How To: iLOM SNAPSHOT using CLI

Below is just an example on how to collect iLOM snapshot using CLI.

Huge number of Oracle Database Home/Binary in a single server

Sometimes, during database consolidation planning, people tends to plan to install one ORACLE_HOME (DB Home/Binary) for each database.

Which means: Lets say, if there are 50 databases then there will be 50 ORACLE_HOME. Specially it's been seen in Exadata/SuperCluster which has capacity to consolidate a very large RDBMS environment.

Question is, is it really a good idea or crazy ?? !!!!

Monitoring TEMP Tablespace Usage

 -- The following query will return all users and their SIDs which are doing a sort  
 SELECT  b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,   
      a.username, a.osuser, a.status  
 FROM   v$session a,v$sort_usage b  
 WHERE  a.saddr = b.session_addr  
 ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;  

Oracle iLOM fault management shell

Enter into iLOM fault management shell using below command

 -- Login to ILOM CLI  
 ssh root@ilom_ip_address_of_the_host  
 -- OR, from the host operating system (provided that ipmitool is configured)  
 # ipmitool sunoem cli  

 -- Start the faultmgmt shell  
 -> start /SP/faultmgmt/shell  
 Are you sure you want to start /SP/faultmgmt/shell(y/n)? y  

ADRCI: Create Incident Package

Below is just an example on how to use ADRCI to collect incident packages for Oracle Database Diagnosis.

Oracle Exadata Documentation

Till today, I saw a lot of people wonders and look for Exadata documentation.

Exadata documentation is publicly available for sometimes and available here: Oracle Exadata Database Machine Documentation