Dear Legends,
Environment: EBS 11.5.10.2 DB - 9.2.0.6 32bit OS RHEL
I performed a Restore and Recovery yesterday for my TEST Instance. After a Incomplete recovery upto Feb 28 archive has been applied to the Full Hot backup database. After giving "alter database open resetlogs" in RMAN prompt the database opened but when I came today morning and checked for a stop and start I couldn't stop the Database via either ./addbctl stop immediate or shutdown immediate in sqlplus.
Before shutting down I tried to create a TEMP file it was also hanging for the whole night to create 500 MB but when I checked in alert log it seems to be created but sqlplus prompt is hanging.
In the mean while I tried my surfing "Hemant's Oracle DBA Blog: OPEN RESETLOGS without really doing a Recovery"
I did a recover database in sqlplus using until cancel and it errored out. Then I couldn't open database using "alter database open resetlogs" so I did "alter database open noresetlogs"
Then database status in open
SQL> select status from v$instance;
STATUS
------------
OPEN
$ ./addbctl.sh stop immediate
You are running addbctl.sh version 115.8
Shutting down database MASK ...
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 6 10:17:56 2015
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
Last 100 lines of Alert log
Dictionary check beginning
File #11 is offline, but is part of an online tablespace.
data file 11: '/paydata/data/pay_tx_data01.dbf'
Dictionary check complete
Thu Mar 5 18:05:08 2015
SMON: enabling tx recovery
Thu Mar 5 18:05:08 2015
Database Characterset is US7ASCII
Thu Mar 5 18:05:21 2015
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open resetlogs
Thu Mar 5 18:06:00 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j001_15804.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 18:08:07 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j000_15880.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 18:12:12 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j000_16185.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 18:20:17 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j000_16791.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 18:36:22 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j000_17806.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 18:45:17 2015
Beginning log switch checkpoint up to RBA [0x2.2.10], SCN: 0x056e.2def7f46
Thread 1 advanced to log sequence 2
Current log# 2 seq# 2 mem# 0: /ERP/oramasknew/maskdata1/log02a.dbf
Current log# 2 seq# 2 mem# 1: /ERP/oramasknew/maskdata1/log02b.dbf
Thu Mar 5 19:05:37 2015
Completed checkpoint up to RBA [0x2.2.10], SCN: 0x056e.2def7f46
Thu Mar 5 19:08:28 2015
Errors in file /ERP/oramasknew/maskdb/9.2.0/admin/MASK_oramask/bdump/mask_j000_19828.trc:
ORA-12012: error on auto execute of job 414
ORA-12008: error in materialized view refresh path
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu Mar 5 19:40:51 2015
ORACLE Instance MASK - Can not allocate log, archival required
Thu Mar 5 19:40:51 2015
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 3
All online logs needed archiving
Current log# 2 seq# 2 mem# 0: /ERP/oramasknew/maskdata1/log02a.dbf
Current log# 2 seq# 2 mem# 1: /ERP/oramasknew/maskdata1/log02b.dbf
Thu Mar 5 20:41:37 2015
alter tablespace temp ADD TEMPFILE '/ERP/oramasknew/maskdata1/temp01.dbf' size 500M autoextend on NEXT 64K
Thu Mar 5 20:41:37 2015
Completed: alter tablespace temp ADD TEMPFILE '/ERP/oramaskne
Fri Mar 6 08:42:18 2015
alter tablespace temp ADD TEMPFILE '/ERP/oramasknew/maskdata1/temp04.dbf' REUSE
Fri Mar 6 08:42:18 2015
Completed: alter tablespace temp ADD TEMPFILE '/ERP/oramaskne
Fri Mar 6 08:42:56 2015
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 9
Fri Mar 6 08:42:59 2015
ALTER DATABASE CLOSE NORMAL
Any kind of help would be much appreciated.
Thanks,
Karthik