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.

Exadata: Health Checking Exadata (Exachk)

Oracle’s exachk utility (NON-INTRUSIVE and does not change anything in the environment) is designed to perform a comprehensive health check of Exadata Database Machine. It is designed to audit important configuration settings within an Oracle Exadata Database Machine. The components examined are database servers, storage Servers, InfiniBand fabric, InfiniBand Switches, and Ethernet network.

exachk should be executed (under Oracle Software owner on DB node) after the initial Oracle Exadata Database Machine deployment, as part of the routine maintenance schedule (at least monthly), and before and after any system configuration change. You should run only one exachk instance at a time.

Exadata: Understanding key OS Processes for a cell

Exadata' s unique software runs different processes on OS to perform different functions. There are three main programs that run on the Exadata Storage Servers to facilitate cell operations cellsrv, MS and RS.  You can identify the OS processes by using ps.

Exadata: What differentiates GI on Exadata with GI on non-Exadata?

The installation,configuration, and administration of Grid Infrastructure on Exadata is identical to Grid Infrastructure on non-Exadata systems. On Exadata, Oracle has elected to store the Oracle Cluster Registry (OCR) and voting disks on Oracle ASM disk groups, mapped to Exadata storage server grid disks. Most processes in Exadata’s Oracle 11gR2 Grid infrastructure perform the same functions as non-Exadata 11gR2 installations, but one software component that plays a special role in Exadata environments is the diskmon process and associated processes

Exadata: Get Cell statistics quickly

Some time you want to get the quick information about Cell like offloading, Storage index etc. Exadata cell has a small tool named as cellsrvstat which provides the comprehensive information like below

Exadata: Replacing damaged disk is really plugNplay activity

On Exadata if any disk is failed due to some problem , replacing it will not be the hard job. It is quite simple task. On my testing environment I did the below to test it, things are quite self explanatory.

Exadata: Monitoring Active Requests, Alerts and Wait Events

Active request provides a client-centric or application-centric view of client I/O requests that are currently being processed by a cell. An active request is characterized at all levels: instance, database, ASM, and cell.

Alerts represent events of importance occurring within the storage cell, typically indicating that storage cell functionality is either compromised or in danger of failure.An alert is automatically triggered when a predefined hardware or software issue is detected, or when a metric exceeds a threshold.

Alert history entries are retained for a maximum of 100 days. If the number of alert history entries exceeds 500, then the alert history entries are only retained for 7 days. 

Stateful alerts represent observable cell states that can be subsequently retested to detect whether the state has changed, indicating that a previously observed alert condition is no longer a problem.
Stateless alerts represent point-in-time events that do not represent a persistent condition; they simply show that something has occurred.

Exadata: Monitoring Performance (Using Metrics)

Like database  in Exadata Metrics and alerts also help you monitor Oracle Exadata Storage Server Software. Metrics are associated with objects such as cells and cell disks, and can be cumulative, rate, or instantaneous.
Metrics are recorded observations of important run-time properties, retained in memory and stored on a disk for a more permanent history.

Exadata: Implementing Cell Security

Security for Exadata Cell is enforced by identifying which clients can access cells and grid disks. Clients include Oracle ASM instances, database instances, and clusters. By default Exadata allows all ASM clusters and databases in the system access to all grid disks. You can implement cell security control access to grid disks at two levels, by ASM cluster and by database

Privileges required for Debugging Oracle Procedure

The following privileges are required for debugger:
1. GRANT ALTER SESSION TO user_name;
2. GRANT CREATE SESSION TO user_name;
3. GRANT EXECUTE ON DBMS_DEBUG to user_name;


Minimum requirements to debug other than your own procedures, functions, and packages:
1. GRANT ALTER ANY PROCEDURE TO user_name; (compile)
2. GRANT CREATE ANY PROCEDURE TO user_name; (edit / save)


The following additional privileges are required for debugger inversion 10g and any version released after that:1. GRANT DEBUG ANY PROCEDURE TO user_name;
2. GRANT DEBUG CONNECT SESSION TO user_name;

Building DR site (RAC 11gR2) using EMC's SRDF/A (Windows 2008R2)

Overview:

Symmetrix Remote Data Facility (SRDF) is a Symmetric based business continuance and disaster restart solution. In simple terms, SRDF is a configuration of multiple Symmetrix units whose purpose is to maintain real time copies of logical data volume in more than one location. The Symmetrix unit can be in the same room, in different building in the same campus or hundreds of miles apart. 

Column with NVARCHAR(4000) not appearing in Resultset using DG4MSQL

Problem:
On SQL Server side a table had a column (desc_details) with NVARCHAR(4000), DG4MSQL has been configured for communication with SQL Server. Querying to that table was not showing the column (desc_details) but all other columns were being shown correctly.

12c: Database Resident Connection Pooling

Database Resident Connection Pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios. It complements middle-tier connection pools that share connections between threads in a middle-tier process. DRCP is relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. DRCP is available to clients that use the OCI driver with C, C++, and PHP.

12c: Using DBFS

Oracle 12c introduces HTTP/HTTPS, FTP and WebDAV access to DBFS via the "/dbfs" virtual directory in the XML DB repository.

12c: Plugging unplugging Database

1- Unplugging the PDB

To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.

12c: Working with Oracle Multitenant

Working with Oracle Multitenant
Environment: Oracle Database 12c (12.1.0) Installed on Windows 7

Creating a CDB creates a service whose name is the CDB name. As a side effect of creating a PDB in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. The service has the same name as the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB.

12c: Configure EM Database Express HTTP port

In case you want to change default port of 12c EM Database Express, you need to configure the port using the dynamic protocol registration method. After the HTTP port is configured, you use it to access Enterprise Manager Express.

1- C:\Users\inam.HOME>set oracle_home=D:\app\Inam\product\12.1.0\dbhome_1

2- Verify that the listener is started by executing the lsnrctl status command.

C:\Users\inam.HOME>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2013 09:54:41

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Production
Start Date                18-JUL-2013 11:21:15
Uptime                    3 days 22 hr. 33 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Inam\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Inam\diag\tnslsnr\Inam-pc\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Inam-pc.HOME.domain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=Inam-pc.HOME.domain)(PORT=5500))(Security=(my_wallet_directory=D:\APP
admin\or12c\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "or12c.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
Service "or12cXDB.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
Service "pdbor12c.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
The command completed successfully
3- Log in to SQL*Plus as the SYSDBA user and verify that the DISPATCHERS parameter in the initialization parameter file includes the PROTOCOL=TCP attribute.
C:\Users\inam.HOME>set oracle_sid=or12c
C:\Users\inam.HOME>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 09:55:31 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameters dispatcher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=or12cX
                                                 DB)
max_dispatchers                      integer

4-  Execute the DBMS_XDB.setHTTPPort procedure to set the HTTP port for Enterprise Manager Express.

SQL> EXEC DBMS_XDB.setHTTPPort(8081);

PL/SQL procedure successfully completed.

5- D:\app\Inam\product\12.1.0\dbhome_1\BIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2013 10:32:24

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Production
Start Date                22-JUL-2013 10:28:01
Uptime                    0 days 0 hr. 4 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Inam\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Inam\diag\tnslsnr\Inam-pc\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Inam-pc.HOME.domain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Inam-pc.HOME.domain)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "or12c.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
Service "or12cXDB.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
Service "pdbor12c.HOME.domain" has 1 instance(s).
  Instance "or12c", status READY, has 1 handler(s) for this service...
The command completed successfully

5- In your Web browser enter the URL to access Enterprise Manager: http://inam-pc.HOME.domain:8081/em

Installing 12c RAC on Linux

Pre-Req
Familiarity with Oracle Virtual Machine
Understaning with Oracle RAC eg; 11gRAC. You can have the understanding by below posts.
Installing Oracle 11g RAC on Windows 2008
Installing 11gR2 RAC on Linux
Installing 11gR2 RAC on Solaris

ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

Environment: 11gR2 (11.2.0.3) , Windows 2008R2

Using ASMCDM , dropping a folder/directory gives below error. 
ASMCMD> rm moherac
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

INS-20802 Grid Infrastructure Configuration Failed - 11gR2 on Windows

Yesterday while installing Oracle RAC 11gR2 (11.2.0.3) on one of the client side got the below error.

INS-20802 Grid Infrastructure Configuration Failed

Although all the cluvfy verification passed successful already, above error occurred after the 84% of installation on the step of Grid Infrastructure Configuration. Before this error remote operations went smooth and all GI folders copied on the remote host successfuly. I did not cancel the installation and decided to investigate first to know the cause.

ORA-00214: control file version inconsistent with file version

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             624954336 bytes
Database Buffers          436207616 bytes
Redo Buffers                5513216 bytes
ORA-00214: control file '+DGDUP/control01.ctl' version 1960 inconsistent with file '+DGDUP/control02.ctl' version 1956

ORA-01624: log %s needed for crash recovery of instance %s (thread %s)

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance migdb (thread 1)
ORA-00312: online log 3 thread 1: 'D:\APP\INAM\ORADATA\MIGDB\REDO03.LOG'

Migrating non-ASM database to ASM - 11gR2

Requirement: Migrate a database from Non-ASM to ASM.
Environment: Oracle 11gR2 (11.2.0.3) , non-ASM Database: migdb (in Archivelog mode), Diskgroup on ASM: +DGDUP

Change RAC database Name using NID

Scenario: A RAC database "HOMEDB" name is needed to be changed to "HOMEATT"
Environment: 2 node Oracle RAC 11gR2 11.2.0.3 (Windows 2008R2)

Restore RMAN RAC database backup to other RAC enviornment

Scenerio:
We have a disk backup of a 2 nodes RAC database (homedb) and want to restore it to other RAC environment 2 nodes.
Environment:
Source: Oracle RAC 11gR2 11.2.0.3 (Windows 2008R2) , Diskgroups:  +HOMEDBDATA & +HOMEDBFLASH, Instances: homedb1,homedb2

Destination:  Oracle RAC 11gR2 11.2.0.3 (Windows 2008R2) only software installed. No database existing. Diskgroups: +DBDATA & +DBFLASH

FRM-40733 PL/SQL built in DBMS_error_code failed

On client side developer faced the below issue (11gR2 database) and requested for the investigations.

I got the below after research

ORA-02114 - Undocumented error

One of the developer on client side got the error, ORA-02114.

after research I found below.

Snapshot Standby Database - 11gR2

Prerequsite:
Configuring Oracle 11gR2 Data Guard - Physical Standby (without DG Broker) 

Brief:

Snapshot Standby is a new feature introduced in 11g that allows the standby database to be opened in read-write mode for real time testing. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature. Using the Flashback Database technology restore point is guaranteed to which the database can be later flashed back to.

Setting up active dataguard - Oracle 11g

Prerequsite:

Configuring Oracle 11gR2 Data Guard - Physical Standby (without DG Broker)

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

Converting a Failed Primary Into a Standby Database Using Flashback Database

Brief: After a failover occurs, the original primary database can no longer participate in the Data Guard configuration until it is repaired and established as a standby database in the new configuration. To do this, you can use the Flashback Database feature to recover the failed primary database to a point in time before the failover occurred, and then convert it into a physical or logical standby database in the new configuration.

Configuring Oracle 11gR2 Data Guard - Physical Standby

Brief: Data Guard is an Oracle feature that primarily provides database redundancy. This is done by having a standby (physical copy) database, preferably in another location and on separate disk. This standby database is maintained by applying the changes from the primary database to it. Standby databases can be maintained with either Redo (Physical standby) or SQL (Logical standby).

Restrict development tools on Production

Environment: Oracle 11gR2 Database on Windows 2008R2
Purpose: Implementing business rules to restrict development tools on production. Developers may work on production through these development tools  on-demand.

Keep history of Oracle Source code in DB

You can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger. This will allow you to easily revert to previous code if required.

Big Data: Working with Oracle NoSQL (KVLite)

KVLite is a single-node, single Replication Group store. It usually runs in a single process and is used to develop and test client applications. KVLite is installed when you install Oracle NoSQL Database.

Big Data: Oracle NoSQL Database - Intro

"A DBA walks into a NOSQL bar, but turns and leaves because he couldn't find a table"

Oracle NoSQL Database provides multi-terabyte distributed key/value pair storage that offers scalable throughput and performance.

Big Data: A Brief Intro

Definition

"Big data" is a term applied to data sets whose size is beyond the ability of commonly used software tools to capture, manage, and process the data within a tolerable elapsed time. Big data sizes are a constantly moving target, as of 2012 ranging from a few dozen terabytes to many petabytes of data in a single data set.

Scheduling Jobs with Oracle Scheduler

You operate Oracle Scheduler by creating and managing a set of Scheduler objects. Each Scheduler object is a complete database schema object of the form[schema.]name

Job
A job is the combination of a schedule and a program, along with any additional arguments required by the program.

Managing Automated Maintenance Tasks

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. These tasks run automatically by the database and are executed during the "maintenance window" which is a contiguous time interval during which automated maintenance tasks are run under.

Kill The Running Job in Oracle

Some times it becomes necessary to kill the ongoing running Oracle job. I faced a situation when there was "enq: TX - row lock contention" and job was continuously running.

Oracle Proxy User

A proxy user is a user that is allowed to "connect on behalf of another user"

Backup and Restore using AVAMAR

Brief:
The Avamar Plug-in for Oracle works with Oracle and Oracle Recovery Manager (RMAN) to
back up an Oracle database, a tablespace, or datafiles to an Avamar server.

Creating duplicate database using rman backup 11gR2 (Single instnace)

Scenerio:
Duplication required for a single instance database (11gR2) on the same server. OS environment Windows 64bit.

Restoring RMAN backup to new server

Scenerio:
RMAN backup has been taken on the production server and now it is required to restore it on the new fresh server. OS environment is Windows 64bit. Source system was on RAC 11gR2 and Destination was 11gR2 Single instance.

Scheduling RMAN Batch job for backup

Scenerio:

To take the RMAN backup by OS scheduled job (on Windows)

Deleting obsolete rman backup information from controlfile

Scenario:
We took database backup disk including controlfile from production server to refresh a staging server for application testing purpose. On production server TAPE (Netbackup) already configured. After copying the RMAN backup files to Stage DB Server, upon restore when we checked database backups it was showing the TAPE backups also. So we deleted the backups.

ORA-01031 insufficient privileges

Developer was getting "ORA-01031: insufficient privileges" error while updating a table even though the user had proper privileges already. Complete scenario is given below 

A Quick Intro to GIS - Part I

What is GIS
GIS stands for geographic information system. It is an integrated system used to display, store, manage and analyze data about objects on earth. It is used to perform a variety of functions on geographic information.

ORA-01152: file 1 was not restored from a sufficiently old backup

Scenerio: After restring and recovering database got ORA-01152 while opening the database with resetlogs. Backup was taken without taking the archive logs. Perform the following:

RMAN: Taking Cold backup using RMAN and restore it

Cold backup is particular useful when you plan to test some changes on the database and in case something goes wrong you can always fall back to this Cold backup.

Cold backup is a consistent backup when the database has been shutdown immediate or Shutdown Normal.If the database is shutdown with abort option then its not a consistent backup.

Cold backup can be taken by RMAN in mount stage after database has been shutdown immediate.

RMAN: Recovery of missing datafile that is never backed up

Environment:
Oracle Database 11gR2 on Windows 7
DB in archivelog mode

Example:
Assuming that already a tablespace "TS1" is existing with one datafile, if not you can use the statements below.
 create tablespace ts1 datafile 'C:\app\Inam\oradata\orcl\ts01.dbf' size 10m reuse;

Using Transportable Tablespaces

Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases. Using transportable tablespaces, Oracle data files (containing table data, indexes, and almost every other Oracle database object) can be directly transported from one database to another. Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data.

RMAN: Recover A Dropped Tablespace Using TSPITR (11gR2)

RMAN automatic Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

ORA-00245 control file backup failed

Environment: Two node Oracle RAC 11gR2 on Windows 2008R2

Symptoms

RMAN backups report errors like :

ORA-00245: control file backup operation failed

ORA-12528: TNS: listener: all appropriate instances are blocking new connections

Cause: All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

Installing 11gR2 RAC on Solaris

Environment: OS  Sun Solaris 10 update 10 x86 64bit on OVM, Oracle 11gR2 (11.2.0.1) 64bit
See the overview Note:

Read the overview above for Grid infrastructure concepts. As overview is related to Windows OS, difference is given below with respect to Solaris.
ADVM (ASM dynamic volume manager) and ACFS (ASM cluster file system) are currently not available forSolaris. 

11gR2 on Solaris: Create Database

11gR2 on Solaris: RDBMS Software Install

Log out from "grid" user and enter as "oracle" and run installer

11gR2 on Solaris: Oracle Grid Infrastructure Install

Start the RACSOL1 and RACSOL2 virtual machines, login to RACSOL1 as the grid user and start the Oracle GI installer.

11gR2 on Solaris: Prepare the shared storage/Prereq

After installing the basic installation of Solaris there are certain requirements which need to be fulfilled for successful installation of RAC 11g.

11gR2 on Solaris: Installing/configuring vboxguestAddition tool

After installing the OS, you can install the "VBoxGuestAdditions" to get the additional benefits from Oracle Virtual Machine like resolution/full screen capabilities etc. VBoxGuestAdditions' .iso image is found in the installation folder of OVM eg; C:\Program Files\Oracle\VirtualBox.

11gR2 on Solaris: Installing OS (Solaris)

You can get the Solaris media from edelivery. After getting the media attach the .iso image as CDROM to OVM and start the virtual machine. Installation wizard will be started. Here are the images for your reference.

ORA-28112: failed to execute policy function

On some client side, today a developer got the errorORA-28112: failed to execute policy function, while running the few Oracle forms.

Install Oracle Database 11gR2 on Solaris 10

Purpose: Installation of Oracle Database 11g Release 2 (11.2.0.2) on Solaris 10 (x86-64)

Environment: Solaris 10 on VMWare, 11gR2 Database

Step 1:  Install Solaris 10

Step2:  Oracle Database 11g Downloads

Step3:  Prepare Solaris for  Oracle Database 11gR2

Create/Work with an Oracle TimesTen 11.2.2 Database - Windows

In order to create and work with TimesTen database we will perform the below tasks.

1- Define Datasource (DSN)

2- Specify DSN, data source path & name, the size of database and the database character set.

3-Verify that database daemon is running
4-Use ttisql to connect and create/work the database

Oracle Database 12c: Are you ready?

What is 12c Database?

Oracle Database 12c is the next release of Oracle’s flagship database product, expected to be launched in calendar Q1, 2013. Because it is  not yet officially launched to general availability , information about it, is limited. following are the information I could manage to gather from various sources, 100% authenticity is not guaranteed until the product is released officially.

ORA 1775 looping chain of synonyms

There can be multiple reasons for this error.

1- Through a series of CREATE synonym statements, a synonym was defined that referred
to itself.

Recompiling Invalid Objects

Operations such as upgrades, patches and DDL changes can invalidate schema objects. 
These objects are re-validated by on-demand automatic recompilation if they don't have compilation errors. But it may take a significant time, so it is always better to make them compiled before they are called.

Import fails with IMP0098: Checking integrity of dump file

After the export is finished one should always check if the export dump file is corrupted on thesource machine using imp with "show=y". If getting the errors then the file is corrupted on the source machine.

RMAN: Incomplete Recovery from TAPE to Single Instance

Environment: Windows 2008R2 64bit, backup located at Netbackup (Tape media), DB is RAC one with two nodes on ASM.

Installing TimesTen 11gR2 - Windows

On UNIX, you can install multiple instances of TimesTen. On Windows, you can
install only one instance of any major TimesTen release where a major release is indicated by the first three parts of the release number, such as 11.2.2. For example, you can install both 11.2.1.9.0 and 11.2.2.3.0 on the same Windows computer, but you cannot install both 11.2.2.0.0 and 11.2.2.3.0.

Oracle TimesTen - Intro

TimesTen - What is it? 
 TimesTen is a memory-optimized, relational database management system with persistence and recover ability. Unlike traditional disk-optimized relational databases, all data within a TimesTen database is located in physical memory (RAM), which means no disk I/O is required for any data operation.

Subqueries: Nested & Corelated

Nested Subquery: A subquery is a query with in a query. It is nested in the where or having clause of another query. Subquery is executed first and then its results are provided to the main clause of the main query. 

Installing 11gR2 RAC on Linux

Environment: OS Oracle Linux 5.4 32bit, Oracle 11gR2 (11.2.0.1) 32bit
See the overview

All the installation process already explained in the overview as mentioned above. Here all JPGs are being provided for the Linux which are self explanatory. Wherever any additional information would be required , it will be provided.

11gR2 on Linux: Create Database

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: RDBMS Software Install

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Oracle Grid Infrastructure Install

Main Post: Installing 11gR2 RAC on Linux

Start the RAC1 and RAC2 virtual machines, login to RAC1 as the oracle user and start the Oracle installer.

11gR2 on Linux: Preparing second VM

Main Post: Installing 11gR2 RAC on Linux 

After fulfilling all the pre-reqs now we will shutdown the node1 (RAC1) and make its clone for Node2 (RAC2)

11gR2 on Linux: Prepare the shared storage/Prereq

Main Post: Installing 11gR2 RAC on Linux 

After installing the basic installation of linux there are certain requirments which need to be fulfilled for successful installation of RAC 11g.

11gR2 on Linux: Installing/configuring vmware tools

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Installing OS (Linux)

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Prepare the Virtual Machines

Main Post: Installing 11gR2 RAC on Linux

Two virtual machines will be prepared, first VM "RAC1" will be prepared , OS will be installed on it and then its copy ("RAC2")will be made. After copy the first VM , necessary modification for parameters will be made.

ORA-29270 Too Many Open HTTP Requests

Today one of the developer got the below error, i thought to write the details for him.

Incomplete Recovery by RMAN from TAPE (ASM) to Single Instance.

-- Incomplete Recovery by RMAN from TAPE (ASM) to Single Instance.
Env. Windows 2008R2 64bit, backup is located at Netbackup (Tape media)
1-  first set ORACLE_SID and the DBID of the source DB
C:\> set ORACLE_SID=HOMEDB
RMAN> set dbid=1547250382

executing command: SET DBID

Adding service to single instance database

Database services (services) are logical abstractions for managing workloads in Oracle Database. Each service represents a workload with common attributes, service-level thresholds, and priorities.

In Real Application Clusters (RAC), a service can span one or more instances and facilitate real workload balancing based on real transaction performance.   RAC also enables you to manage a number of service features with Enterprise Manager, the DBCA, and the Server Control utility (SRVCTL).

ORA-00304: requested INSTANCE_NUMBER is busy

Environment: 2 nodes Oracle RAC 11gR2, Linux 5
Scenario: Node1 has a single instance database named testdb, same testdb was restored to the Node2 (as single also) , while starting the instance on node2 for testdb (single instance) gave the error below:
ORA-00304: requested INSTANCE_NUMBER is busy

Setting up an NFS share

NFS (Network File System) is a protocol used by UNIX/Linux computers to share disks across a network. Similar to the Common Internet File Services (CIFS) protocol used by Windows, NFS is older and more light-weight, and performs much more efficiently on UNIX and Linux systems.

Adding vip resource (11gR2 Linux)

Enviroment: Oracle Linux 5 update 5 64bit, Oracle RAC 11gR2

While installing  RDBMS software on Oracle 11gR2 cluster following error was encountered.

ORA-12631: Username retrieval failed

Case:
While attempting to connect to the database the following error occurs.

c:\temp\dig>sqlplus /@qanew as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 2 09:09:07 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12631: Username retrieval failed
 

Configuring Transparent Application Failover

Transparent Application Failover (TAF) is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side.

RAC on Windows: Oracle Clusterware Node Evictions a.k.a. Why do we get a Blue Screen (BSOD) Caused By Orafencedrv.sys? [ID 337784.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Microsoft Windows Itanium (64-bit)
Microsoft Windows x64 (64-bit)
Microsoft Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003 (64-bit Itanium)Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (32-bit)
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7

Symptoms

While running RAC a blue screen is shown and a reboot takes place. Windows creates a coredump that shows that orafencedrv.sys is involved.

Configuring Ignite for Oracle Connections

For Ignite to connect to Oracle databases that are specified by a name (tnsnames or LDAP), the following Oracle Network configuration (.ora)  files are required
    tnsnames.ora (required for name TNSName resolution)
    ldap.ora (required for LDAP name resolution)
    sqlnet.ora (optional)

Oracle Data Guard Configuration (DGMGRL) (11gR2 Windows 2008R2)

Brief:

Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. DG broker does not have the ability to create standby and is used for managing the dataguard configuration.

Task:  Create  physical standby database for an existing primary database. Both primary and standby would be on same physical machine.

PRM = primary db 192.168.26.11

STBL = local Standby 192.168.26.11