Exadata: Shuting down/Rebooting cell without affecting ASM

Sometimes it becomes necessary to power down or reboot the cell to perform maintenance while one or more databases are running. In this situation You need to verify that taking the storage server offline will not impact Oracle ASM disk group and database availability. The ability to take Oracle Exadata Storage Server offline without affecting database availability depends on the level of Oracle ASM redundancy used on the affected disk groups, and the current status of disks in other Oracle Exadata Storage Servers that have mirror copies of data as Oracle Exadata Storage Server to be taken offline.

Exadata: Calculating Reserve Space for Cell/Disk Failure Coverage

ASM disk groups in Exadata are defined as either normal or high redundancy. Normal redundancy provides for two copies of file extents while high redundancy provides for three copies of file extents. Each disk is partnered with a set of other disks in other failure groups to ensure that file extent copies are stored in separate failure groups and the disk group can tolerate the loss of one disk (or one cell) in normal redundancy or two disks (or two cells) in high redundancy disk groups.

Exadata: Tracing Oracle I/O Requests on Exadata DB Nodes

With Oracle Exadata, database IO is handled exclusively by the cellsrv process on storage servers. The database nodes bundle IO requests into iDB messages and transmit these over the infiniband network grid to storage server. To confirm where physical database IO is performed on Oracle Exadata, we can use strace to trace system calls from an Oracle instance process. strace is a tool for tracing system calls and signals . It intercepts and records the system calls made by a running process. strace can print a record of each system call, its arguments, and its return value.

Exadata: Taking System Backup using LVM Snapshots

Recently, Oracle began shipping Exadata with the Linux LVM configured for managing file system storage on the database servers. Logical volume managers provide an abstraction layer for physical diskpartitions similar to the way ASM does for its underlying physical storage devices. 

LVM snapshots eliminate many of the typical challenges we face with simple backups using the tar command or third-party backup products. For example, depending on the amount of data in the backup set, file system backups can take quite a while to complete. These backups are not consistent to a point in time, meaning that if you must restore a file system from backup, the data in your files will represent various points in time from the beginning of the backup process to its end. Applications that continue to run during the backup cycle can hold locks on files, causing them to be skipped (not backed up). And once again, open applications will inevitably

make changes to data during the backup cycle. Even if you are able to back up these open files, you have no way of knowing if they are in any usable state unless the application is shut down before the backup is taken.

A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. You can create a temporary LVM partition if you have enough free space in the volume group.

In my test environment , I had not space space for the volume so I had to create the new volume for the example purpose. 

[root@exadb ~]# pvdisplay
--- Physical volume ---
PV Name /dev/sda2
VG Name VolGroup00
PV Size 49.90 GB / not usable 25.37 MB
Allocatable yes (but full)
PE Size (KByte) 32768
Total PE 1596
Free PE 0
Allocated PE 1596
PV UUID 6Fm5nC-4GGM-w8Ec-pqDQ-t3tn-usjP-RgnhPo
[root@exadb ~]# vgdisplay
--- Volume group ---
VG Name VolGroup00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 2
Open LV 2
Max PV 0
Cur PV 1
Act PV 1
VG Size 49.88 GB
PE Size 32.00 MB
Total PE 1596
Alloc PE / Size 1596 / 49.88 GB
Free PE / Size 0 / 0
VG UUID 6o0Qaa-sWkO-Fzpc-05Nx-cY91-IID0-zb7Ous

I added a new drive (50G) to my server and created the partition (sdb1) for it using fdisk

then verified the physical volume.

[root@exadb ~]# pvdisplay /dev/sdb1
"/dev/sdb1" is a new physical volume of "50.00 GB"
--- NEW Physical volume ---
PV Name /dev/sdb1
VG Name
PV Size 50.00 GB
Allocatable NO
PE Size (KByte) 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID SUDSzj-2qI1-7apF-a916-fG0y-mfeE-djrJhh

Having created the physical volumes the next step is to create the volume group. 

[root@exadb ~]# vgcreate db_sysbkp /dev/sdb1 -v 

    Wiping cache of LVM-capable devices

    Wiping cache of LVM-capable devices

    Adding physical volume '/dev/sdb1' to volume group 'db_sysbkp'

    Archiving volume group "db_sysbkp" metadata (seqno 0).

    Creating volume group backup "/etc/lvm/backup/db_sysbkp" (seqno 1).

  Volume group "db_sysbkp" successfully created

-- verify the volume group has been created

See the attributes of newly created volume group

[root@exadb ~]# vgdisplay db_sysbkp
--- Volume group ---
VG Name db_sysbkp
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size 50.00 GB
PE Size 4.00 MB
Total PE 12799
Alloc PE / Size 0 / 0
Free PE / Size 12799 / 50.00 GB
VG UUID OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

Up to this point you have just got the new volume group which may be in your case already available. Below is the process to use the LVM snapshot once you have sufficient space in any volume group.

Process to take System Backup

1- Create destination (SAN/NAS/NFS) for the final copy of backups. You can create the logical volume in a volume group for this purpose. Determine the volume group and create the logical volume. In my case I already had the volume group "VGdb" with 50GB space. I used the command lvcreate  to create logical volume in it.

[root@exadb ~]# lvcreate -L 25G -n /dev/VGdb/dbbkp
   Logical volume "dbbkp" created

-L option allows specification of the logical volume size in Megabytes, Terabytes, Petabytes or Exabytes
-n option, which is used to specify a name for the logical volume. If this is not specified, the logical volume name defaults to the next default logical volume number.

2- After crating logical volume verify the VGdb volume group allocations

[root@exadb ~]# vgdisplay VGdb
--- Volume group ---
VG Name VGdb
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size 50.00 GB
PE Size 4.00 MB
Total PE 12799
Alloc PE / Size 6400 / 25.00 GB
Free PE / Size 6399 / 25.00 GB
VG UUID OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

3- Create a file system, now we can create a new file system on the dbbkp partition created in step 1.
[root@exadb sbin]# mkfs.ext3 -m 0 -b 4096 /dev/VGdb/dbbkp
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3276800 inodes, 6553600 blocks
0 blocks (0.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
200 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or

180 days, whichever comes first.  Use tune2fs -c or -i to override.

-b block-size: Specifies the size of blocks in bytes. Valid block size vales are 1024, 2048 and 4096 bytes per block.
-m reserved-blocks-percentage: Specifies the percentage of the file system blocks reserved for the super-user.

4- Create the target directory and mount the new file system where backup will be placed.

[root@exadb dev]# mkdir /mnt/databasebkp

[root@exadb dev]# mount /dev/VGdb/dbbkp /mnt/databasebkp

[root@exadb dev]# df -k /mnt/databasebkp

Filesystem           1K-blocks      Used Available Use% Mounted on

/dev/mapper/VGdb-dbbkp

                      25803068    176200  25626868   1% /mnt/databasebkp

[root@exadb dev]# 

[root@exadb dev]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       44G   13G   29G  30% /

/dev/sda1              99M   23M   71M  25% /boot

tmpfs                 1.5G  966M  505M  66% /dev/shm

VOBXSHARED            368G  259G  109G  71% /media/sf_VOBXSHARED

/dev/sr0               94K   94K     0 100% /media/20131031_1914

/dev/mapper/VGdb-dbbkp

                       25G  173M   25G   1% /mnt/databasebkp

At this point , now you have the place (/mnt/databasebkp) to stage your backup images. 

5- Know your logical volumes for which, you want to take the backup. In my case I've the volume group "db_sysbkp" (50G) with logical volume "sysbkp" (25G) already . If you don't have logical volume for a volume group you could create like below.

[root@exadb ~]# lvcreate -L 25G -n /dev/db_sysbkp/sysbkp

  Logical volume "sysbkp" created

after crating logical volume you could verify the volume group "db_sysbkp" attributes

[root@exadb ~]# vgdisplay db_sysbkp

--- Volume group ---

  VG Name               db_sysbkp

  System ID             

  Format                lvm2

  Metadata Areas        1

  Metadata Sequence No  2

  VG Access             read/write

  VG Status             resizable

  MAX LV                0

  Cur LV                1

  Open LV               0

  Max PV                0

  Cur PV                1

  Act PV                1

  VG Size               50.00 GB

  PE Size               4.00 MB

  Total PE              12799

  Alloc PE / Size       6400 / 25.00 GB

  Free  PE / Size       6399 / 25.00 GB

  VG UUID               OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

6- Once you know the logical volumes to be backed up (eg; sysbkp) , you can create LVM snapshot.

[root@exadb dev]# lvcreate -L5G -s -n syssnap1 /dev/db_sysbkp/sysbkp

  Logical volume "syssnap1" created

-L parameter determines the size of the snapshot volume

When the datablocks are modified or deleted after the snapshot is created , the original copy

of the block is written to the snapshot. It is important to size the snapshot sufficiently to store 

an original copy of all chaneged blocks. If snapshot runs out of space , it will be deactivated.

From the operating system point of view snapshot will be a block device containing the information from /dev/db_sysbkp/syssnap1 from the moment the snapshot was taken.

[root@exadb dev]# ls d*

dm-0  dm-1  dsp  dvd  dvd-sr0

db_sysbkp:

sysbkp  syssnap1

disk:

by-id  by-label  by-path  by-uuid

[root@exadb dev]# 

See the LVM snapshot info

[root@exadb dev]# lvdisplay /dev/db_sysbkp/syssnap1

  --- Logical volume ---

  LV Name                /dev/db_sysbkp/syssnap1

  VG Name                db_sysbkp

  LV UUID                rOXrZh-OH6B-Cviy-QuNb-4UTJ-mPxQ-1ddC0J

  LV Write Access        read/write

  LV snapshot status     active destination for /dev/db_sysbkp/sysbkp

  LV Status              available

  # open                 0

  LV Size                25.00 GB

  Current LE             6400

  COW-table size         5.00 GB

  COW-table LE           1280

  Allocated to snapshot  0.00% 

  Snapshot chunk size    4.00 KB

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           253:3

7- Mount the snapshot as you would any other file system. First we will create a directory to use as mount point for our snapshot. Then we will mount it.

[root@exadb dev]# mkdir /mnt/snap

[root@exadb ~]# mount /dev/db_sysbkp/syssnap1 /mnt/snap

[root@exadb dev]# mount

/dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

/dev/sda1 on /boot type ext3 (rw)

tmpfs on /dev/shm type tmpfs (rw)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)

VOBXSHARED on /media/sf_VOBXSHARED type vboxsf (gid=500,rw)

/dev/sr0 on /media/20131031_1914 type iso9660 (ro,nosuid,nodev,uid=0)

/dev/mapper/db_sysbkp-sysbkp on /mnt/sysbkp type ext3 (rw)

/dev/mapper/db_sysbkp-syssnap1 on /mnt/snap type ext3 (rw)

[root@exadb dev]# 

[root@exadb /]# lvscan

  ACTIVE   Original '/dev/db_sysbkp/sysbkp' [25.00 GB] inherit

  ACTIVE   Snapshot '/dev/db_sysbkp/syssnap1' [5.00 GB] inherit

  ACTIVE            '/dev/VolGroup00/LogVol00' [45.03 GB] inherit

  ACTIVE            '/dev/VolGroup00/LogVol01' [4.84 GB] inherit

Once the snapshot is mounted , we can browse just like any other filesystem. They look and feel

just like the original file system, with one exception that it will not show the files and contents created/modified after the snapshot creation. 

8- Test and verify. I created the a text file in the volume (Original) for the test purpose before the snapshot creation with two lines like below. (You should do it just before step 6 )

[root@exadb sysbkp]# echo "First Line" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# echo "Second Line" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# cat /mnt/sysbkp/f1.txt

First Line

Second Line

After creating and mounting the LVM snapshot (Step 6,7)  I modified the contents of the file like below

[root@exadb sysbkp]# echo "Third Line - added" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# cat /mnt/sysbkp/f1.txt

First Line

Second Line

Third Line - added

You can see that "Third Line - added" appears in the file (Observe this file is on original volume)

Now check the same file on the LVM snapshot volume

[root@exadb mnt]# cd snap

[root@exadb snap]# ls

f1.txt  lost+found

[root@exadb snap]# cat f1.txt

First Line

Second Line

You don't see the "Third Line - added"  (Observe this the snapshot volume)

9- After your testing is done and you are comfortable with the results, finally take the backup using tar command.

[root@exadb snap]# cd /mnt/snap

[root@exadb snap]# tar -pjcvf /mnt/sysbkp/exadb_system_backup.tar.bz2 * /boot --exclude /mnt/sysbkp/exadb_system_backup.tar.bz2 >/tmp/exadata_system_backup.stdout 2>/tmp/exadata_system_backup.stderr

Note that /boot file system does not use the LVM for storage. This file system must be backed up using the tar command. That is not the problem, because /boot file system is fairly small and static so I'm not concerned with these files being modified, locked or open during the backup cycle.

10- Unmount the filesystem for snapshot and remove it.

[root@exadb ~]# umount /mnt/snap

[root@exadb ~]# lvremove /dev/db_sysbkp/syssnap1

Do you really want to remove active logical volume syssnap1? [y/n]: y

  Logical volume "syssnap1" successfully removed

Note: I've used the example to take the backup for the non-Oracle volume but you can take the Oracle related volumes int he same way. 

Avoiding Logon Storm (DoS) on Oracle Database

A Logon storm is a sudden spike in the number of client connection requests. Logon storms can occur due to a variety of factors. They could be malicious like a DoS attack. Or they could occur due to administrative actions – such as a middle tier coming online.

Configuring Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers. A pooled server is the equivalent of a server foreground process and a database session combined.

Clusterware resource ora.cvu

ora.cvu is a new resource introduced with Grid Infrastructure 11.2.0.2. The purpose of this resource is to invoke clusterware health checks at regular intervals. It is a singleton resource with cardinality of 1 and invokes the cluster verification utility. It executes the following command in the background.

Configuring Resource Manager for Multiple Workloads

Resource Manager can be configured to manage workloads (OLTP,DSS etc) differently by configuring consumer groups and resource plans.

Consumer Group:

A consumer group is a collection of sessions that are managed as a unit. You can define consumer groups for each application in your database. Or you can define consumer groups for each type of workload, e.g. OLTP, reports, maintenance, etc.

Resource Manager and Instance Caging

Brief: Excessive CPU load can destabilize the server and expose operating system bugs and can also prevent critical Oracle background processes from running in a timely manner,  resulting in failures such as database instance evictions on a RAC database.

Using Oracle Database Resource Manager, you can ensure that your database’s CPU  load is always healthy, thus avoiding all of these problems.

Recovering/Opening database for which archive log is missing

Some times you don't have the missing archivelogs and database cannot be opened due to it.

After incomplete recover we tried to open database with resetlogs but it failed, one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) can be used to open database even though it’s not properly recovered.

Exadata: Knowing a bit Exadata administrative utilities

MegaCLI: This Utility (run as root on cell)  generate diagnostics or configuration information about your MegaRAID-controlled disk devices on an Exadata Storage Server or Compute Server. There are various command line options for this utility:

Exadata: Diagnostics using sundiag/deaddisk

For Sun Oracle Exadata Environments
On each Exadata compute and storage cell nodes, Oracle delivers a utility called sundiag.sh . Bydefault sundiag.sh script isinstalled in /opt/oracle.SupportTools.When logging Oracle Service Requests, it is common for Oracle Support to request the output of the sundiag.sh utility.