Fix Oracle 10g 11g Data Pump IMPDP Failed withORA-39002 ORA-06512 ORA-39070 ORA-29283: invalid file operation

Error Description:

Oracle datapump impdp fails with following error.

impdp dumpfile=exp_TAB_28122011.dmp directory=DATA_PUMP_DIR logfile=exp_TAB_28122011_imp.log job_name=EXP ignore=y

Import: Release 11.2.0.2.0 - Production on Wed Dec 28 06:19:40 2011

What are the Considerations for EXADATA Smart Scan Cell Offloading?

In below mentioned situations the exadata predicate evaluation will not offloaded.

• If you use more that 255 columns in the query
• The object which belongs to the tablespace is not stored on the Exadata
• If a virtual column is present on the query

One More Interactive Music Doodle from Google: Happy Holidays 2011

Once again goole released interactive music doodle during the 2011 holyday season. It has good graphics which represents the X-mas and holidays. Each letters of google transforms to beautiful graphics images and once you finish click on buttons of all characters, the letters starts dancing with the Jingle Bells music. Enjoy the doodle in this link. 

EXADATA Smart Scan - cell_offload_plan_display = NEVER / AUTO /ALWAYS Parameter

CELL_OFFLOAD_PLAN_DISPLAY parameter desides whether the SQL execution plan to show or not to show the offload predicates. This parameter can be dynamically modified by using ALTER SESSION or ALTER SYSTEM command. This parameter will not make any difference to the SQL execution speed.

The parameter values can be

Fix Duplicate Database failing RMAN-06136: ORA-01618: redo thread is not enabled - cannot mount

Error description: RMAN duplicate command is failing with following error.

RMAN-00571: =================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===============================================

RMAN-03002: failure of Duplicate Db command at 12/21/2011 04:43:44

RMAN-05501: aborting duplication of target database

opt_param ( 'cell_offload_processing' , ’TRUE/FALSE’ ) Hint to Enable EXADATA Smart Scan Feature: Speed Comparison with Smart Scan

opt_param( ‘cell_offload_processing’ ’TRUE’) hint is used to enable the exadata smart scan feature in sql execution process. Please see some speed comparison below with and without smart scan.

You can disable the smart scan feature in session level by using following commands

To Disable: alter session set CELL_OFFLOAD_PROCESSING = FALSE;

To Enable: alter session set CELL_OFFLOAD_PROCESSING = TRUE;

Floating Facebook Like, Share, Google Plus One +1 Button Gadget HTML Code For Blogger

Steps to Implement the code.

1. Go to Blogger Dashboard > Design > Page Elements.
2. Click Add A Gadget.
3. In Add A Gadget window, select HTML / Javascript.
4. Copy below mentioned code and paste it inside the window.
5. Click Save.

Google Language Translator Gadget HTML Code: With Small Beautiful Flags for Blogger

Steps to Implement the code.

1. Go to Blogger Dashboard > Design > Page Elements.
2. Click Add A Gadget.
3. In Add A Gadget window, select HTML / Javascript.
4. Copy below mentioned code and paste it inside the window.
5. Click Save.

Main Reasons for Delay or Not Receiving the Adsense PIN after a Period of Time

All publishers are required to enter a Personal Identification Number (PIN) to get the eligibity to get the payments from Adsense. PIN detail comes to your mailing address. PINs are necessary to verify and validate the accuracy of your contact information, mailing address. After entering the PIN, Adsense team ensure that you can receive your payments.

Different Methods to Print Single Quote (') in AWK programming

This is my input file

cat dat

NO         TAG20111217T004155

YES        TAG20111217T004157

YES        TAG20111217T004157

Fix RMAN-03009 ORA-00245 RMAN-08132 control file backup operation failed

Error Description: RMAN backup is getting failed with following error.

RMAN-00571: ===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===================================================

RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_DISK_1 channel at 12/15/2011 05:24:57

ORA-00245: control file backup operation failed

Tested Tips to Increase or Improve Adsesnse CTR

General Facts on Adsense CTR

• Adsense CTR is closely related the traffic on your website. The more traffic you achieve on your website the CTR gradually increases.
• Eventhogh the revenue is related to the CTR, it more related on where the traffic or click is coming from. I.e. The clicks from certain countries like US, UK, Australia, etc pays more for each click.

Oracle 11g 10g Database SQL Script to Find Out Active Sessions Waits

This script outputs the current wait states for all active database sessions.

COLUMN dbusername FORMAT A12

COLUMN osuser FORMAT A12

COLUMN sid FORMAT 999999

COLUMN OS_pid FORMAT 999999

COLUMN serial# FORMAT 99999999

Fix DB Link Error ORA-02085: database link connects to

Problem Description:

Select from database link failing with following error

SQL> select 1 from dual@PRODDB1;

select 1 from dual@PRODDB1

                   *

ERROR at line 1:

ORA-02085: database link PRODDB1 connects to TEST3E

Oracle 10g 11g RAC ASM RMAN Database Cloning to Different Server with Different File System

Here is the Scenario:

We have 3 RMAN backup pieces from the source database (SOURCE)

Backup pieces which is already trasfered from ASM to file system :

mfmtiqi3_1_1, mhmtiqk5_1_1, SOURCE_cf_c-3981864101-20111207-00

Refer DBMS_FILE_TRANSFER

How to Rename Oracle Database Using NID command Line Utility

Requirement is rename the database name from SOURCE to TARGET

Steps:

1. Create the initTARGET.ora file with necessary parameters. Db_name parameter value must be TARGET.
2. Shutdown the SOURCE database

Oracle 10g 11G RAC Database Control File Creation Failed with ORA-01503, ORA-12720: operation requires database is in EXCLUSIVE mode

Error Description: 
Oracle RAC database control file creation failed with following error.

ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

How to Change Oracle 11g 10g RAC database to Noarchivelog mode?

1. Veryfy the current log mode using below mentioned query

SELECT log_mode FROM gv$database;

LOG_MODE

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

ARCHIVELOG

ARCHIVELOG

How to See the Oracle 10g 11g Bind Variable Values Using v$sql_bind_capture

Here is the simple query to see the bind variables from Oracle database.

SELECT     s.sql_id,   s.sql_text SQLTEXT, 

bc.name BIND_VAR_NAME, 

bc.value_string BIND_VAR_STRING

FROM       v$sql s  , v$sql_bind_capture bc

WHERE      s.sql_id = bc.sql_id

Benefits of Registering Own Custom Domains for Your Blog on Blogger or WordPress

• The blog without a domain will show as the subdomain of their (blogger/wordpress) domains. Some of the benefited services will be limited for the subdomains. The subdomain address will block your website from displaying some featured ads.  Example: buysellads.com

Reset the Password for an Oracle Expired Login or Username to Old or Previous Value

Sometimes we don’t remember the passwords for some application user/logins which we hard coded in the application. These users/logins password get expired by the aging. Even though the account password is expired the encrypted password values will be stored in the database. There are two ways to find out the encrypted values and reset it to the old value.

RMAN Reset Incarnation Failed: ORA-19910: can not change recovery target incarnation in control file

You cannot the reset of the database if it is in open or nomount state. If you try to perform the reset database to incarnation Command you will get ORA-19910 error message. You have to reset the incarnation in mount state. Now you can perform the recovery from the previous incarnation.

How to Restore Oracle RMAN Backup from Previous or Old Incarnation

Some fact about this Restoration:

• It will be a point-in-time recovery
• You must set the incarnation to the previous or required one accorind to your restoration time using RESET DATABASE command

How to Force Oracle RMAN to Delete All Archive log files

As a DBA, in many situations you get archive destination going to be filled or filled with archivelogs. Immedidately you are force to remove some or all archives. Here are the commands to remove the archivelogs.

Note: If you remove archivelogs with out backing up it is better to take a fresh level 0 backup of the database to avoid the data lose.

Oracle RAC Startup failed ORA-01102: cannot mount database in EXCLUSIVE mode

Problem Description: 
Oracle RAC database startup command failed with ORA-01102: cannot mount database in EXCLUSIVE mode.

Error Description:

The error is because the database is running or opened in EXCLUSIVE mode in one node. So you can not start the database in another node with normal startup command.

Database startup failing ORA-19815: WARNING: db_recovery_file_dest_size of bytes is 100.00% used,

Your database db_recovery_file_dest is full and it does not have any room for any more files. So you have to clean up your db_recovery_file_dest location

1. Find out your db_recovery_file_dest location from the init file and clean up some unwanted files from the location

RMAN-06059 ORA-19625 backup archivelog all delete input

Problem Description: 
RMAN archivelog all delete input is failing with following error.

Starting backup at 01-DEC-11

using channel ORA_DISK_1

using channel ORA_DISK_2

RMAN-00571:

FIX RMAN-11001 ORA-01547 "warning: RECOVER succeeded but OPEN RESETLOGS would get error below"

Error Description:

Open resetlogs fails with 01152, 00000, "file %s was not restored from a sufficiently old backup "

Solution Description:

It looks like the datafiles are not consistent state. The datafiles must be in consistent state after the recovery to open the database in open resetlogs.

Fix RMAN-08138: WARNING: archived log not deleted - must create more backups

Error description: RMAN delete archivelog all command is failing with RMAN-08138 warning.

Solution Description:

The archive log files which you are trying to delete is still needed for recovery.

Fix RMAN-04005 ORA-01031: insufficient privileges RMAN-00554 in Oracle 10g 11g RAC

Error Description: 
RMAN target database connectivity is failing with ORA-01031

$ rman catalog rman/rman@rmancat target rmanbkup/rmanbkup@prod3

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Nov 29 02:40:33 2011

Fix RMAN-20242: Specification Does Not Match Any Archive Log

Error Description:

RMAN> run

2> {

3>  backup as copy archivelog from sequence 700 until sequence 710 format '+RECO/%d/BACKUPS/PROD2_%t_%s_%r.arc';

4> }

Exadata Hybrid Columnar Compression – HCC : Comparison between Compress for Query High and Archive High

Here I am creating a test from all_objects view.  

SQL> create table object_list as select * from all_objects;

Table created.

Transfer Oracle ASM binary Files One File System to another File System Using DBMS_FILE_TRANSFER

There are 3 procedures with DBMS_FILE_TRANSFER package to help this file transfer from one ASM file system to another file system.

1. COPY_FILE

This procedure copies the file from source directory object to destination directory object.

HTML Code to Place Adsense Ads Side by Side, Left, Right, Center Aligned on Blogger Posts

It is more important that the placing of the Adsense ads in proper position of the blogger post to get more earning. This post will help you to place the ads side by side, left aligned and right aligned

How to Recover the Oracle 10g 11g Database from RMAN Cold Backup

Some facts on RMAN Cold Backup

• RMAN cold backup is taken on database mount state
• After RAMN cold backup Recovery database needs to be open with resetlogs option.

Fix ORA-09817: Write to audit file failed. SVR4 Error: 28: No space left on device

Error Descirption:

Sqlplus “/ as sysdba” failing with ORA-09817 error.

bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 14 14:21:09 2009

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

New Latest Method to Apply Ration Card Online Karnataka – Bangalore 19-Nov-11

From 19-Nov-11 onwards the people from Karnataka state can apply for the Ration cards online. You can submit the applications online through the website http://ahara.kar.nic.in/ or you can avail the ‘online service’ at taluk office or offices of the judisdictional assistant director or duputy director of the department by paying a fee of Rs.10/-

Oracle 11gR2: Generate Number Series Using Select Command with array(x) as clause? UNDEF Command to Reset && variable

It is a new feature in Oracle 11gR2; you can generate number series in single select command. See some examples below.

How to generate multiplication table using Select Command?

Multiplication table for 4:

Fix RMAN-06004 RMAN-20001: target database not found in recovery catalog

Error Description: Most of the RMAN commands are getting failed with RMAN-20001 error message.

RMAN> list backup summary;

RMAN-00571:

Add Datafile, Create tablespace to Oracle database runing on ASM or with Oracle-managed files? Fix ORA-01276

It is very simple to add a datafile or create a tablespace to a database which is running on ASM or with Oracle-managed files. You don’t need to worry about the next file name etc. You will get following error if you try to specify a file name while creation of tablespace or adding a datafile to existing tablespace.

How to Drop Datafile from Tablespace on Oracle 8i, 9i, 10g, 11g Database?

Upto Oracle 10gR1 the process of dropping a datafile is a tedious process. From 10gR2 Oracle introduced single command to drop a datafile from the tablespace.  Most of the situations in prior versions (10gR1 and below), DBAs used to reduce the size of the unwanted datafile to a smaller size and keep it with the tablespace itself because of the lengthy process.

Oracle 11g 10g RMAN Duplicate Database Command Using TAG: How to Clone a Database in same server / node

In this scenario our source database is prod9 and destination database Test9. i.e. Target is PROD9, Auxiliary (database to be cloned) is TEST9 and TAG is PROD_DB.

Please see the steps to perform the database cloning.

Fix RMAN Duplicate database failed ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower], [kdt.c], [9576], [11.2.0.2.0]

Error Details:

RMAN Duplicate database command is failing with following errors.

RMAN-00571: ===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

Troubleshoot & Fix RMAN Duplicate database failed with ORA-12720 RMAN-06136

Error Details:

RMAN-00571: ===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===================================================

Troubleshoot & Fix RMAN Duplicate Database failed with RMAN-11003 & ORA-19906

Error Description:

RMAN-00571: ===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===================================================

Troubleshoot & Fix RMAN Duplicate database Failed RMAN-00600: internal error, arguments [8546] [1]

Error Details:

RMAN-00571: ===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

RMAN-00571: ===================================================

Troubleshoot & Fix ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

Here is one sample scenario for ORA-19811 error. I connected to target as / and tried to execute the change command. It is giving the ORA-19811 error. You should connect to recovery catalog database to execute the change command.

Also, you cannot use the KEEP for the backupset in Flash Back Recovery area (FRA). You need to copy the backupset outside of the FRA location and use the catalog backuppiece command whenever you wanted to restore it.

Change the RMAN Backup TAG Retention Policy Using Change Backup .. Keep Command

Connect to RMAN.

RMAN> list backup summary;

List of Backups

===============

Oracle 10g 11g Syntax for Lock and Unlock a User Schema Account

Syntax:

To lock an Oracle user account: alter user username account lock;

To Unlock an Oracle User Account: alter user username account unlock;

Examples:

alter user scott account lock;

user scott altered.

alter scott cep account unlock;

user scott altered.

Oracle 10g 11g Shared Database – DB Link – Features, Advantages and Disadvantages

Shared database link help you to limit the network connections between the local and remote servers. For a shared database link need 10 shared server processes in local database and also it requires upto 10 network connections to the remote server.

How to enable voice call in Microsoft Office Communicator 2007 R2 Windows 7 and XP

HKEY_CURRENT_USER-Software-Policies-Microsoft-Communicator

Add two new DWORD with

1. Valuename DisableAVConferencing and Value Data 0

2. Valuename DisablePC2PCVideo and Value Data 1

Oracle int vs number datatypes difference and features

When you create a column as int datatype in a table, the datatype will be converted to number of precision 38. You can not define any precision to the int datatype. When you create a column with number as the datatype, the datatype will be considered as number without any precision. You can insert any kind of decimal values to the table. Please see some examples below.

How to Rename Tablespaces to a New name in Oracle 9i 10g 11g ?

In Oracle 9i we need to use exp imp utility to achive this goal. Please see the example below

1. Create a new test tablespace

SQL>  create tablespace Newtab datafile '/data/oracle/oradata/prod9/newtab_01.dbf' size 50m;

Oracle SQL Script to Find Out Active Sessions Details

SET LINESIZE 500

SET PAGESIZE 1000

COLUMN username FORMAT A15

COLUMN machine FORMAT A25

COLUMN logon_time FORMAT A20

Easy Methods to Find Out Oracle 9i 10g 11g DBID

Method 1 From v$database;

SQL> select dbid from v$database;

      DBID

----------

 576655928

Different Methods to Find Out Oracle Version From the Database

Method 1: Sqlplus startup

If you are using the sqlplus from server it will show the version of the Oracle and if you are trying it from the client machine which will show you the Oracle client installed on the machine.

How to Use Drop Database Command in Oracle 11gR2?

Post Update on 23-Nov-2013 with screenshot.
Dropping a database will remove all corresponding datafile, redo log files, control files and init parameter files. Obviously it removes all the data in the database. The database must be mounted in exclusive and restricted mode.

How to Change Oracle 9i 10g 11g Database Time_Zone? Fix ORA-02231 & ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns

You can check the database time_zone by using following query

SQL> select dbtimezone from dual;

DBTIME

------

How to encrypt UNIX (solaris AIX Linux) files using vi editior? Protect your UNIX files with password

vi editor has –x option to encrypt a unix file. You can use this option as a password protection for your UNIX files. The –x option will ask you for a passcode or encryption key which will be used as the key password for the file.

Soft Link Vs Hard Link in Solaris & AIX Unix OS – Difference and Features

Hard Links

• Hard links have actual content
• Hard link is the replica of the original file
• All hard links have the same inode number

How to Find Out Last Modified Time of a Row in an Oracle 10g 11g Table Using ORA_ROWSCN and SCN_TO_TIMESTAMP Functions?

ora_rowscn pseudo column and SCN_TO_TIMESTAMP function

Oracle 11g database can link the system change number and corresponding timestamp for certain amount of time period. This short period of time depended on the auto-tuned undo retention period.

How to Get Current SCN for Oracle 10g 11g Database? DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER and V$database

There are two ways to get the current scn (System Change Number) for an oracle database.

Method 1

Get Timestamp from SCN in Oracle 10g 11g? Convert SCN to Timestamp Using scn_to_timestamp and timestamp_to_scn

There are 2 functions to perform these operations.

For converting SCN to Time Stamp you can use scn_to_timestamp function and for converting time stamp to SCN use timestamp_to_scn.

How to get Aadhaar UID application enrollment centers information for Maharashtra & Orissa

STATE NAME	ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
ORISSA	Central Bank of India, Rasulgad Branch	Rasulgad Square

How to Rename an Unnamed datafile (UNNAMED00004) to Original Name in Oracle 9i 10g 11g Standby Database?

Please find the steps to rename an unnamed datafile to original name in Standby Database.

Step1. Find out the exact name for the datafile which is in unnamed format from V$DATAFILE table.

How to get Aadhaar UID application enrollment centers information for Uttrakhand, Tripura, Uttar Pradesh, West Bengal?

STATE NAME	ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
TRIPURA	Bagabasa	Bagabasa Panchyat Office
TRIPURA	Chandanmura	Chandanmura Panchyat Office

How to get Aadhaar UID application enrollment centers information for Tamil Nadu, Puducherry, Rajasthan

STATE NAME	ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
TAMIL NADU	EPPL_1057	-
TAMIL NADU	IOB Main Branch	#763,Annai Salai
TAMIL NADU	Chromepet	10,Bhashyam Street, Radha Nagar,Chennai

How to get Aadhaar UID application enrollment centers information for Himachal Pradesh, Madhya Pradesh, Haryana?

STATE NAME	ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
HIMACHAL PRADESH	Mandi, Sadar	-
HIMACHAL PRADESH	Naggar	BDO Office Patikul

How to get Aadhaar UID application enrollment centers information for Goa & Gujarat?

STATE NAME	ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
GOA	-	Curchrom
GOA	-	Margoa H O
GOA	-	Mormugoa
GOA	-	Navelim

How to get Aadhaar UID application enrollment centers information for Delhi?

ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
Society for Promotion of Youth and Masses, South	-
Community Outreach Department, St. Stephen’s Hospital, Tis Hazari, Delhi	-
-	-

How to get Aadhaar UID application enrollment centers information for Chandigarh?

These are the enrollment Aadhaar application enrollment centers for Chandigarh state.

ENROLLMENT CENTER NAME/LOCATION	ENROLLMENT CENTER ADDRESS
-	Central Bank Of India, SCO68-69, Sec 17B, Bank Square, Chd
-	ALANKIT ASSIGNMENTS LTD, First floor, SCO 196-197, SECTOR 34A, Chd