Could not validate ASMSNMP password due to following error- "ORA-01031: insufficient privileges"

Platform: Oracle 11g R2 grid

At Fist Node's $ prompt execute

$ orapwd file= $GRID_HOME /dbs/orapw+ASM1 entries=5 password=your-password

At Fist Node's $ prompt execute

$ orapwd file=$GRID_HOME/dbs/orapw+ASM2 entries=5 password=your-password

From any of the node as 11g Grid Infrastructure owner execute:

export ORACLE_SID=+ASM1

export ORACLE_HOME=/u01/app/11.2.0/grid

[grid@node1 bin]$ sqlplus / as sysasm

SQL>create user asmsnmp identified by your-password;

SQL> grant sysdba to asmsnmp;

DBCA hangs, does not respond after clicking finish using Xmin

When I tried to create/configure database using DBCA at 11g release 2, DBCA hung or did not respond after 

clicking finish button and 

Cause:

             X Client I was using was XMING, which had a kind of bug.

Solution:

             After installing Xming complete font package, I could create database using DBCA, this problem exist with Xming at Linux or Unix enviromnets in my case it was AIX 7.

Oracle Support Note: 727290.1.

Transfer Datafile from ASM to File System

This article is about transferring database files from ASM to O/S File System, two methods are used here are:

1. DBMS_FILE_TRANSFER.copy_file.

2. 'ASMCMD cp' Command.

DBMS_FILE_TRANSFER

                            There are three procedures within the dbms_file_transfer package:

• COPY_FILE: This is useful for copying files locally on the database server.
   
• GET_FILE: This is useful when a file on a remote database is to be transferred to a local file system through the local database connection.
   
• PUT_FILE: Reads a local file and contacts a remote database to create a copy of the file in the remote file system

       Can move files to and fro between ASM and OS File System, valid from 10gR2. Here COPY_FILE procedure will be used to transfer the datafile name EXAMPLE.dbf, the complete path and name of datafile at ASM is 

        +DATA/orcl/datafile/example.267.758826135

Source directory is at ASM (+DATA/orcl/datafile)

And Destination Directory is at O/S File System (/u01/app/oracle/dbhome_1)

Log in as user with DBA role and execute the following statements:

SQL> create directory source as '+DATA/orcl/datafile/';

SQL> create directory dest as '/u01/app/oracle/dbhome_1'

SQL> grant read on directory source to dba;

SQL> grant write on directory dest to dba;

and finally run 

SQL> begin 

                  dbms_file_transfer.copy_file('source','example.267.758826135','dest','EXAMPLE.dbf'); 

          end;

          After successful run of above procedure, file existence at O/S FS can be verified using 'ls'

 

ASMCMD 'cp'

                This is the simplest way to move database file either way between ASM and File System, introduces at 11g.

Again 

Source directory is at ASM (+DATA/orcl/datafile)

And Destination Directory is at O/S File System (/u01/app/oracle/dbhome_1)

at asmcmd prompt simply run the below line and you will be done.

ASMCMD> cp +DATA/appdbtst/datafile/example.267.758826135 /u01/app/oracle/dbhome_1/example.dbf

The remaining procedures (GET_FILE and PUT_FILE) are similar in the way source and destination are specified but enable further transfer to a remote DB specified by dblink. Both source and target databases should have directories defined. Restrictions related to dbms_file_transfer package are:

• Files are supposed to be multiple of 512 bytes
• Files should be less than 2TB
• There is no character set conversion
• Oracle should have permissions to the file and after copy file are owned by oracle user.
• File transfer operation can be monitored from v$session_longops

Environment variable ORACLE_UNQNAME not defined

After successful installation of 11g release 2 grid infrastructure, when tries to start enterprise manager control, it raised error mentioned below:
                 Environment variable ORACLE_UNQNAME not defined, pleased set ORACLE_UNQNAME to database unique name.

        I simply executed following line at my linux command line with grid ouwner user i.e. oracle:

             export ORACLE_UNQNAM={DATABASE NAME}

        where DATABASE_NAME can be found via following command.

           select name from v$database;

       It simply solved my problem, and hope will help you as well.

Farrukh Salman.

Using _ALLOW_RESETLOGS_CORRUPTION in case of Archivelog unavailibility or corruption

Summary 

                Recently I had to recover my database using an old backup set for which I used the normal steps as under: 

1. startup the database at nomount state.
2. Connect to RMAN catalog
3. Run a script which allocates some tape channels, restore the controlfile, mount the database, restore database, recover applying the archives and redologs.
4. At the end I hade to open database with resetlogs;

STARTUP NOMOUNT;
rman TARGET / CATALOG rman/****@rman
RUN {
ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
restore CONTROLFILE;
alter database mount;
restore database;
recover database;
release channel c1;
}
alter database open resetlogs;
exit;

While recovering the database you will be asked for some archives. A typical message like this occurs:

ORA-00279: change 9867098396261 generated at 08/21/2011 16:44 needed for
thread 1
ORA-00289: suggestion : /u01/oracle1/**********.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #****

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 10 needs more recovery to be consistent
ORA-01110: data file 10: �Archivelog location and name�
ORA-01112: media recovery not started

At the archivelogs apply step as you notice i pressed CANCEL

When i tried to open the database with resetlogs i got the error.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 10 needs more recovery to be consistent
ORA-01110: data file 10: �/oracle/oradata/MIS/data_01.dbf�

There is a hidden parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE which will allow us to open database even though it�s not properly recovered.

ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

Tip: Also change the undo_management to “Manual”

After the two changes in the spfile you can open the database with:

sqlplus "/ as sysdba"
startup force

How to find all the corrupted objects in your Database
Partially restore database

Reviews

Unable to create database at 11g release 2 grid, scan IP was not running on both nodes

After successful installation of Oracle 11g release2 patchset1 grid and database software, I tried to create a new 11g RAC database using DBCA, but it stuck at an error 'database creation requires scan IP to be running, can not create database', on further investigation I found SCAN IP was not configured at my grid infrastructure by default, so I follwed the below sequence of commands to create and start the SCAN

On node1
========
srvctl add vip -n testnode01 -k 1 -A testnode01-vip/255.255.255.0/en0

srvctl add scan -n <scan-name>  in my case scan name is 'testnode-scan'

srvctl add scan_listener

srvctl start vip -n testnode01
srvctl start scan
srvctl start scan_listener

On node2
========
srvctl add vip -n testnode02 -k 1 -A testnode02-vip/255.255.255.0/en0

srvctl start vip -n testnode02
srvctl start scan
srvctl start scan_listener

       After completing above steps I am able to create a new database.

Regards,

Farrukh Salman.

Unable to connect ASM instance using sqlplus/asmcmd at 11gR2 'Connected To An Idle Instance'

Just after installing 11gR2 patch set 1, I checked all of my cluster services every thing was up and running then I tried to connect ASM instance which is now builtin from 11g onward, I got an error 'Connected To An Idle Instance', I checked all my services related to ASM it were all up and running i.e. ora.asm, pmon, smon etc., then I got an Oracle support document saying I have to remove forward slash '/' from ORACLE_HOME environment variable in my .profile file setting.

Incorrect value:
                       ORACLE_HOME=/u01/app/11.2.0/grid/

Correct value:
                      ORACLE_HOMe=/u01/app/11.2.0/grid

                 After this I can connect to ASM instance properly :).

Regards,

Farrukh Salman,

Reference Link:

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(viewingMode=1143&bmDocID=1179825.1&bmDocDsrc=KB&bmDocType=PROBLEM&bmDocTitle=Unable%20To%20Connect%20To%20ASM%20Due%20To%20SQL*Plus%20Shows%20%E2%80%9CConnected%20To%20An%20Idle%20Instance.&from=BOOKMARK))