Archive

Archive for November, 2012

ORA-01157: cannot identify/lock data file 7 – see DBWR trace file

November 28, 2012 Leave a comment

I dropped a tablespace, its corresponding data file was missing in the system. It showed that

SQL> drop tablespace SOE;
drop tablespace SOE
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf’

SQL> !ls -l /opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf
/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf: No such file or directory <– missing
Finally, I dropped it successfully by the following procedures

1. SQL> alter database datafile 7 offline drop;

Database altered.

2. SQL> select name from v$datafile;

NAME
——————————————————————————–
+DATA1/testdb/datafile/system.504.800535271
+DATA1/testdb/datafile/sysaux.505.800535271
+DATA1/testdb/datafile/undotbs1.506.800535273
+DATA1/testdb/datafile/users.507.800535273
+DATA1/testdb/datafile/example.519.800535661
+DATA1/testdb/datafile/undotbs2.520.800536373
/opt/oracle/product/11.2.0.3/db_1/dbs/soe.dbf

7 rows selected.

3. SQL> drop tablespace soe;

Tablespace dropped.

SQL>  select name from v$datafile;

NAME
——————————————————————————–
+DATA1/testdb/datafile/system.504.800535271
+DATA1/testdb/datafile/sysaux.505.800535271
+DATA1/testdb/datafile/undotbs1.506.800535273
+DATA1/testdb/datafile/users.507.800535273
+DATA1/testdb/datafile/example.519.800535661
+DATA1/testdb/datafile/undotbs2.520.800536373

Advertisements
Categories: ORA-XXX

ORA-15260: permission denied on ASM disk group

November 28, 2012 Leave a comment

When I login 11gR2 ASM instance as sysdba to mount diskgroup, it showed permission denied on ASM disk group. After I login the ASM instance as sysasm, it is fixed.

SQL> alter diskgroup DATA1 mount;
alter diskgroup DATA1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

 

SQL> connect / as sysasm <– as sysasm
Connected.
SQL> alter diskgroup data1 mount;

Diskgroup altered.

 

 

Categories: ORA-XXX

ORA-15306: ASM password file update failed on at least one node

November 28, 2012 Leave a comment

When I created the asmsnmp account under ASM instance in 11gR2, it returned the error below

ORA-15306: ASM password file update failed on at least one node

The root cause is missing orapw+ASM file under $ORACLE_HOME/dbs (grid home). The solution is

# su – grid

# cd $ORACLE_HOME/dbs

# orapwd file=orapw+ASM1 password=123456 entries=20

( repeat the procedures above on ASM2)

 

 

Categories: ORA-XXX

Could not validate ASMSNMP Password

November 28, 2012 Leave a comment

Today, I created a new database after installation of Oracle 11gR2 by dbca, it showed the

 

 

 

 

 

The root cause is missing ASMSNMP user in ASM instance. By recreating the user “asmsnmp” under ASM instance, the problem is fixed
Procedure:

# su – grid

# sqlplus / as sysasm

# SQL> create user asmsnmp identified by <password>

Categories: ORA-XXX