Data … as usual

All things about data by Laurent Leturgez

Category Archives: Administration

ORA-01031: Insufficient Privileges while creating a 11.2.0.2 Database with ASM

3 Comments Posted by on May 4, 2011

I recently encountered this strange error during a database creation process on my RAC :

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

I engaged the search mode, and I found a message in my ASM instance alert.log :

ORA-01918: user 'ASMSNMP' does not exist

By a mysterious way, the ASMSNMP user hasn’t been created during the Grid Infrastructure installation process.

So, to resolve this problem,  I have executed the steps above to create the ASMSNMP user:

1- Create or replace the existing password file for the ASM instance

[grid@rac1 ~]$ orapwd file=/u01/app/11.2.0/grid/dbs/orapw+ASM password=oracle

2- Copy this password file on every node of your cluster

[grid@rac1 dbs]$ scp orapw+ASM grid@rac2:/u01/app/11.2.0/grid/dbs
orapw+ASM                                     100% 1536     1.5KB/s   00:00

3- Create the ASMSNMP user in the ASM instance, and grant it the SYSDBA Privilege.

[grid@rac1 dbs]$ sqlplus / as sysasm
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user asmsnmp identified by oracle ;

User created.
SQL> grant sysdba to asmsnmp;

Grant succeeded.

Don’t forget … the ASM instance parameter “REMOTE_LOGIN_PASSWORDFILE” has to be set to EXCLUSIVE or you will raise an ORA-01999 error.

When the ASMSNMP user has been created, I have been able to create my clustered database and to connect to ASM instance.

Administration, ASM / ADVM, RAC

What’s your Oracle cluster name ?

Leave a comment Posted by on March 17, 2011

If, like me, you have installed an oracle cluster few months ago, and now you have to reinstall your database control … you must know your cluster name.

To retrieve it, you can use the “cemutlo” command.

For example, in a Windows Grid Infrastructure installation :

D:\>d:\grid\11.2.0\BIN\cemutlo.exe -n
RAC_PRD
Administration, Oracle, RAC

Oracle 11g md_restore and compatible.rdbms

Leave a comment Posted by on November 19, 2009

I have recently made a restore test of my diskgroup metadata with the new version of oracle server : 11.2.0.1.

Initially, I have created a TEST_DG diskgroup with the new asmca interface.
As I had a 10g database on my laptop, I have set the compatibility.rdbms parameter on 10.2.0.0.
After a rapid md_backup command to save my diskgroup metadatas, I have tried to restore it with the md_restore command.

What a surprise when I saw this error message:

ASMCMD [+] > md_restore md_backup.sav -G TEST_DG
Current Diskgroup metadata being restored: TEST_DG
ASMCMD-09352: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.7.0 or higher (DBD ERROR: OCIStmtExecute)

After a short research, I realize that the CREATE DISKGROUP command is generated like this:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10'name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' , 'au_size' = '1048576', 'sector_size' = '512
', 'cell.smart_scan_capable' = 'FALSE';

And this command uses properties which have been introduced in Oracle 11g release (for example: sector_size). Next command works fine:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10' name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' ;

So, if like me, you have some Oracle 10g databases that run on an ASM 11g release, you have to restore your diskgroup metadata in an SQL file (by using -S option of the md_restore command) and adapt the DDLs for your needs.

Not really easy ! 😉

Administration, ASM / ADVM, Linux, Oracle

Delete ADR "problems"

Leave a comment Posted by on November 5, 2009

When you are using adrci to interface adr you can easily purge incidents, health monitor reports etc…

… but you can’t purge problems:

adrci> help purge
Usage: PURGE [[-i  |  ] |
[-age  [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:
Purpose: Purge the diagnostic data in the current ADR home. If no
option is specified, the default purging policy will be used.
Options:
[-i id1 | id1 id2]: Users can input a single incident ID, or a
range of incidents to purge.
[-age ]: Users can specify the purging policy either to all
the diagnostic data or the specified type. The data older than
ago will be purged
[-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
data to be purged.
Examples:
purge
purge -i 123 456
purge -age 60 -type incident

No trace of problem purge 😦

If you want to delete those problems, you have to know about a SQL interface directly in adrci. (The “tables” represents XML files mappings, these files are located in the repository).

So, I paste above a little example of a DELETE in adrci interface:

adrci> delete from problem;
 3 Rows Deleted
 adrci> commit;
 Transaction Committed
 adrci> show problem
  
 ADR Home = /u01/app/oracle/diag/rdbms/racdb/racdb1:
 ***********************************************************
 0 rows fetched

Be careful, if you delete problems, you will loose the problem memory of your database.

Administration, Oracle

Newer posts →