ORA-1110, ORA-1157 starting a RAC database

Error description : Starting a RAC database throws ORA-1157, ORA1110

[oracle@grac41 SCRIPTS]$ srvctl start database -d grac4 
PRCR-1079 : Failed to start resource ora.grac4.db
CRS-5017: The resource action "ora.grac4.db start" encountered the following error: 
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+TEST/grac4/datafile/test_ts.256.852905863'
. For details refer to "(:CLSN00107:)" in "/u01/app/11204/grid/log/grac41/agent/crsd/oraagent_oracle/oraagent_oracle.log"
--> From above error we know that DG TEST with tablespace TEST_TS has problems
    As we have lost the DG we can only drop this DG/Tablespace and recreate it later

Fix the problem

SQL> alter database  datafile '+TEST/grac4/datafile/test_ts.256.852905863' offline drop;
Database altered.
SQL> alter database open;
Database altered.

Verify datafile status 
SQL> SELECT     t.NAME "Tablespace", f.NAME "Datafile",  f.status "DBFile STAT"
        FROM     V$TABLESPACE t, V$DATAFILE f WHERE    t.TS# = f.TS# ORDER BY t.NAME;
Tablespace               Datafile                                      DBFile STAT
------------------------------ --------------------------------------- ------------
SYSAUX                   +DATA/grac4/datafile/sysaux.257.826111425      ONLINE
SYSTEM                   +DATA/grac4/datafile/system.256.826111425      SYSTEM
TEST_TS                  +TEST/grac4/datafile/test_ts.256.852905863     RECOVER
UNDOTBS1                 +DATA/grac4/datafile/undotbs1.258.826111425    ONLINE
UNDOTBS2                 +DATA/grac4/datafile/undotbs2.264.826111755    ONLINE
UNDOTBS3                 +DATA/grac4/datafile/undotbs3.268.826220083    ONLINE
USERS                    +DATA/grac4/datafile/users.259.826111427       ONLINE

Drop tablespace including content
SQL> drop tablespace TEST_TS including contents;
Tablespace dropped.

SQL> SELECT     t.NAME "Tablespace", f.NAME "Datafile",  f.status "DBFile STAT"
        FROM     V$TABLESPACE t, V$DATAFILE f WHERE    t.TS# = f.TS# ORDER BY t.NAME;
Tablespace               Datafile                                        DBFile STAT
------------------------------ ----------------------------------------- ------------
SYSAUX                   +DATA/grac4/datafile/sysaux.257.826111425       ONLINE
SYSTEM                   +DATA/grac4/datafile/system.256.826111425       SYSTEM
UNDOTBS1                 +DATA/grac4/datafile/undotbs1.258.826111425     ONLINE
UNDOTBS2                 +DATA/grac4/datafile/undotbs2.264.826111755     ONLINE
UNDOTBS3                 +DATA/grac4/datafile/undotbs3.268.826220083     ONLINE
USERS                    +DATA/grac4/datafile/users.259.826111427        ONLINE
--> Datafile is dropped now 

Verify that datafile was deleted from ASM 
[grid@grac41 SCRIPTS]$ asmcmd ls 
DATA/
FRA2/
OCR/
TEST_NEW/
--> No reference to DG TEST anymore 

Reference

  • ORA-1157/ORA-1110 Trying To Open The Database (Doc ID 212053.1)

Leave a Reply

Your email address will not be published. Required fields are marked *