Email: [email protected] 7 x 24 online support!
Oracle Database Dead?
Hello. I'm not very knowledgeable when it comes to DBA work, but I've hit a snag and would appreciate a little advice. I have an Oracle XE install on my home computer for keeping track of business receipts and such. Recently I couldn't connect to APEX and started looking into it. When I tried to connect with SQL+, I would get the message:
ERROR:
OR-01033: ORACLE initialization or shutdown in progress
So I stopped and started the DB, checked to see the listener service was going, and still have the issue. I checked my alert_xe.log file, and it's... huge. Attached at the bottom, I included a fragment of my log file that consists of the last recent timestamp.
I do have a script that runs on my computer that does an EXP every night on my schema, but if I try an IMP, I get the same message (which makes sense since my DB apparently isn't running).
What would be your suggestions? I wanted some advice before I uninstalled and re-installed then imported my dmp backups.
Thanks to any necromancers who can help me bring this back from the dead!
Dump file e:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log
Thu Oct 29 20:40:40 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:211M/511M, Ph+PgF:1012M/1247M, VA:1945M/2047M
Thu Oct 29 20:40:40 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Thu Oct 29 20:40:52 2009
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
sessions = 49
__shared_pool_size = 104857600
__large_pool_size = 8388608
__java_pool_size = 4194304
__streams_pool_size = 0
spfile = E:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\SPFILEXE.ORA
sga_target = 146800640
control_files = E:\ORACLEXE\ORADATA\XE\CONTROL.DBF
__db_cache_size = 25165824
compatible = 10.2.0.1.0
db_recovery_file_dest = E:\oraclexe\app\oracle\flash_recovery_area
db_recovery_file_dest_size= 10737418240
undo_management = AUTO
undo_tablespace = UNDO
remote_login_passwordfile= EXCLUSIVE
dispatchers = (PROTOCOL=TCP) (SERVICE=XEXDB)
shared_servers = 4
job_queue_processes = 4
audit_file_dest = E:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP
background_dump_dest = E:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP
user_dump_dest = E:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
core_dump_dest = E:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP
db_name = XE
open_cursors = 300
os_authent_prefix =
pga_aggregate_target = 41943040
PSP0 started with pid=3, OS id=2988
MMAN started with pid=4, OS id=2992
PMON started with pid=2, OS id=2984
DBW0 started with pid=5, OS id=3004
LGWR started with pid=6, OS id=3008
CKPT started with pid=7, OS id=3012
SMON started with pid=8, OS id=3016
RECO started with pid=9, OS id=3020
CJQ0 started with pid=10, OS id=3024
MMON started with pid=11, OS id=3028
MMNL started with pid=12, OS id=3032
Thu Oct 29 20:40:55 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
Thu Oct 29 20:41:00 2009
alter database mount exclusive
Thu Oct 29 20:41:05 2009
Setting recovery target incarnation to 2
Thu Oct 29 20:41:05 2009
Successful mount of redo thread 1, with mount id 2582644764
Thu Oct 29 20:41:05 2009
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Thu Oct 29 20:41:05 2009
alter database open
Thu Oct 29 20:41:06 2009
Beginning crash recovery of 1 threads
Thu Oct 29 20:41:06 2009
Started redo scan
Thu Oct 29 20:41:07 2009
Completed redo scan
9180 redo blocks read, 514 data blocks need recovery
Thu Oct 29 20:41:07 2009
Started redo application at
Thread 1: logseq 797, block 5520
Thu Oct 29 20:41:09 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 797 Reading mem 0
Mem# 0 errs 0: E:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_2MXYQN2G_.LOG
RECOVERY OF THREAD 1 STUCK AT BLOCK 28 OF FILE 2
Thu Oct 29 20:41:11 2009
Aborting crash recovery due to error 1172
Thu Oct 29 20:41:11 2009
Errors in file e:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3080.trc:
ORA-01172: recovery of thread 1 stuck at block 28 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: alter database open...
Thu Oct 29 20:55:05 2009
db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Oct 29 22:50:17 2009
WARNING: inbound connection timed out (ORA-3136)
While I would generally wholeheartedly concur with that, I believe that XE only allows you to install one instance per server. I haven't tried to see whether that is a licensing restriction or a technical restriction that is checked by the installer, but I'm not sure that you can get a second XE database running on the server.
And while there are tools that can recover data from old data files, given that the free version of Oracle is being used, those tools probably aren't an option-- I don't think Oracle's DUL is available unless you have a support contract which isn't available on XE and the competitors are pretty darned pricey. If the data isn't important enough to need a commercial version of the database with support, patches, etc., it's probably not important enough to license one of these tools.
You can also try PRM-DUL : http://www.parnassusdata.com/en/oracle-dul