Hi,
We have a server that crashed due to hardware failure and although brought back up OK Oracle DB's are no longer mounting. I am not a DBA but it looks bad to me and it seems there are no backups. Can anyone suggest a course of action? This is Oracle 11G XE running on Centos 6. I have put as much detail in as I can and could really do with some advice.
[root@627963 trace]# sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 12 23:18:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect sys as sysdba;
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2233344 bytes
Variable Size 893389824 bytes
Database Buffers 167772160 bytes
Redo Buffers 5541888 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10571
Session ID: 62 Serial number: 3
SQL> startup /nomount
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
Alert_XE.log:
Thu Sep 12 21:26:18 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =19
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
System parameters with non-default values:
sessions = 180
memory_target = 1G
control_files = "/u01/app/oracle/oradata/XE/control.dbf"
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 20G
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
shared_servers = 4
job_queue_processes = 4
audit_file_dest = "/u01/app/oracle/admin/XE/adump"
db_name = "XE"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Thu Sep 12 21:26:20 2013
PMON started with pid=2, OS id=21388
Thu Sep 12 21:26:20 2013
"alert_XE.log" 342L, 17231C
Thu Sep 12 21:26:20 2013
PSP0 started with pid=3, OS id=21390
Thu Sep 12 21:26:21 2013
VKTM started with pid=4, OS id=21392
VKTM running at (100ms) precision
Thu Sep 12 21:26:21 2013
GEN0 started with pid=5, OS id=21396
Thu Sep 12 21:26:21 2013
DIAG started with pid=6, OS id=21398
Thu Sep 12 21:26:21 2013
DBRM started with pid=7, OS id=21400
Thu Sep 12 21:26:21 2013
DIA0 started with pid=8, OS id=21402
Thu Sep 12 21:26:21 2013
MMAN started with pid=9, OS id=21404
Thu Sep 12 21:26:21 2013
DBW0 started with pid=10, OS id=21406
Thu Sep 12 21:26:21 2013
DBW1 started with pid=11, OS id=21408
Thu Sep 12 21:26:21 2013
DBW2 started with pid=12, OS id=21410
Thu Sep 12 21:26:21 2013
LGWR started with pid=13, OS id=21412
Thu Sep 12 21:26:21 2013
CKPT started with pid=14, OS id=21414
Thu Sep 12 21:26:21 2013
SMON started with pid=15, OS id=21416
Thu Sep 12 21:26:21 2013
RECO started with pid=16, OS id=21418
Thu Sep 12 21:26:21 2013
RECO started with pid=16, OS id=21418
Thu Sep 12 21:26:21 2013
MMON started with pid=17, OS id=21420
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Sep 12 21:26:21 2013
MMNL started with pid=18, OS id=21422
starting up 4 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle
Thu Sep 12 21:26:21 2013
ALTER DATABASE MOUNT
Thu Sep 12 21:26:24 2013
Sweep [inc][21794]: completed
Sweep [inc][21793]: completed
Sweep [inc2][21794]: completed
Successful mount of redo thread 1, with mount id 2707110077
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Sep 12 21:26:25 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 93 KB redo, 34 data blocks need recovery
Started redo application at
Thread 1: logseq 241, block 21190
Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_8kxhwjco_.log
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8, _wordcopy_bwd_dest_aligned()+280] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc (incident=22593):
ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []
Incident details in: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70, kcbs_dump_adv_state()+946] [flags: 0x0, count: 2]
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc (incident=22594):
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []
Incident details in: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22594/XE_ora_21440_i22594.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20130912212627], requested by (instance=1, osid=21440), summary=[incident=22593].
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []
PMON (ospid: 21388): terminating the instance due to error 397
System state dump requested by (instance=1, osid=21388 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_21398.trc
Dumping diagnostic data in directory=[cdmp_20130912212628], requested by (instance=1, osid=21388 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 21388
Thu Sep 12 21:39:03 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =19
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
System parameters with non-default values:
sessions = 180
memory_target = 1G
control_files = "/u01/app/oracle/oradata/XE/control.dbf"
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 20G
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
shared_servers = 4
job_queue_processes = 4
audit_file_dest = "/u01/app/oracle/admin/XE/adump"
db_name = "XE"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Thu Sep 12 21:39:05 2013
PMON started with pid=2, OS id=2483
Thu Sep 12 21:39:05 2013
PSP0 started with pid=3, OS id=2489
Thu Sep 12 21:39:06 2013
VKTM started with pid=4, OS id=2546
VKTM running at (100ms) precision
Thu Sep 12 21:39:06 2013
GEN0 started with pid=5, OS id=2550
Thu Sep 12 21:39:06 2013
DIAG started with pid=6, OS id=2552
Thu Sep 12 21:39:06 2013
DBRM started with pid=7, OS id=2554
Thu Sep 12 21:39:06 2013
DIA0 started with pid=8, OS id=2556
And the tracefile:
Trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_21440.trc
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe
System name: Linux
Node name: <blah>
Release: 2.6.32-358.14.1.el6.x86_64
Version: #1 SMP Tue Jul 16 23:51:20 UTC 2013
Machine: x86_64
Instance name: XE
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 21440, image: <blah>(TNS V1-V3)
*** 2013-09-12 21:26:25.858
*** SESSION ID:(62.3) 2013-09-12 21:26:25.858
*** CLIENT ID:() 2013-09-12 21:26:25.858
*** SERVICE NAME:() 2013-09-12 21:26:25.858
*** MODULE NAME:(<Blah> (TNS V1-V3)) 2013-09-12 21:26:25.858
*** ACTION NAME:() 2013-09-12 21:26:25.858
Thread 1 checkpoint: logseq 241, block 2, scn 8960531
cache-low rba: logseq 241, block 21190
on-disk rba: logseq 241, block 21377, scn 8979221
start recovery at logseq 241, block 21190, scn 0
*** 2013-09-12 21:26:25.869
Started writing zeroblks thread 1 seq 241 blocks 21377-21384
*** 2013-09-12 21:26:25.870
Completed writing zeroblks thread 1 seq 241
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 93Kb in 0.01s => 9.13 Mb/sec
Longest record: 2Kb, moves: 0/242 (0%)
Change moves: 2/26 (7%), moved: 0Mb
Longest LWN: 8Kb, moves: 0/57 (0%), moved: 0Mb
Last redo scn: 0x0000.00890313 (8979219)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 34/34 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 377/542 = 0.7
----------------------------------------------
*** 2013-09-12 21:26:25.872
KCRA: start recovery claims for 34 data blocks
*** 2013-09-12 21:26:25.873
KCRA: blocks processed = 34/34, claimed = 34, eliminated = 0
*** 2013-09-12 21:26:25.874
Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0
*** 2013-09-12 21:26:26.055
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8, _wordcopy_bwd_dest_aligned()+280] [flags: 0x0, count: 1]
Incident 22593 created, dump file: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22593/XE_ora_21440_i22593.trc
ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF8] [PC:0x319A88A1B8] [Non-existent physical address] []
Incident 22594 created, dump file: /u01/app/oracle/diag/rdbms/xe/XE/incident/incdir_22594/XE_ora_21440_i22594.trc
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+946] [SIGSEGV] [ADDR:0x7C7FD5E7FFEC] [PC:0x444FE70] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [_wordcopy_bwd_dest_aligned()+280] [SIGBUS] [ADDR:0x76FFFFF