This discussion is archived
8 Replies Latest reply: Dec 31, 2012 11:15 AM by 982093 RSS

Newbie needs help

982093 Newbie
Currently Being Moderated
I just started working for this company and inherited a problem with an Oracle 11g database. Trial by fire and this is what I have come up with thus far. The database abruptly quit running a couple of days ago. I have NO experience with Oracle. Through research this is what I have come up with.

Accessing the Database Control - Enterprise Manager, it shows the database instance down, the listener up, the agent connection down. No one knows the user name and password or at least doesn't know if it is correct. I first tried to start the database. I figured out that I needed to create a local admin account on the server so I could accomplish this. I start the database by clicking Startup, enter my username and password, and the next screen states:

Current status: mounted
open the database?

Are you sure you want to perform this action. I choose yes

get the following error:
Alter database open
Error at line 1
Ora-03113: end-of-file on communication channel
process ID: 4628
Session ID: 17 Serial Number: 4119

Disconnected from Oracle Database 11g

Here's what I found researching:

If I start SQLPlus /nolog and connect sys as sysdba /nolog
it states connected to an idle instance
I then type shutdown immediate;
Ora-01034: Oracle not available
ora-27101: shared memory realm does not exist

then I type startup

ORACLE instance started.

Total System Global Area 430075904 bytes
Fixed Size 2176448 bytes
Variable Size 327158336 bytes
Database Buffers 96468992 bytes
Redo Buffers 4272128 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 912
Session ID: 1 Serial number: 5

Know one knows if the system uses an init.ora or an spfile. The spfile from what I understand cannot be edited with a text editor. Here is the content of the only init.ora file I have found:

init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=gcr1.com
db_name=orcl

###########################################
# File Configuration
###########################################
control_files=("d:\oracle11gr2\oradata\orcl\control01.ctl", "d:\oracle11gr2\flash_recovery_area\orcl\control02.ctl")
db_recovery_file_dest=d:\oracle11gr2\flash_recovery_area
db_recovery_file_dest_size=4102029312

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=d:\oracle11gr2
memory_target=429916160

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Security and Auditing
###########################################
audit_file_dest=d:\oracle11gr2\admin\orcl\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1


The contents of the alert log:
<txt>************************************************************************
</txt>
</msg>
<msg time='2012-12-28T11:22:15.267-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
pid='828'>
<txt>Instance terminated by USER, pid = 828
</txt>
</msg>
<msg time='2012-12-28T11:54:43.714-06:00' org_id='oracle' comp_id='rdbms'
msg_id='opistr_real:935:3971575317' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Starting ORACLE instance (normal)
</txt>
</msg>
<msg time='2012-12-28T11:54:43.792-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksunfy:14932:2937430291' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>LICENSE_MAX_SESSION = 0
</txt>
</msg>
<msg time='2012-12-28T11:54:43.807-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksunfy:14933:4207019197' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>LICENSE_SESSIONS_WARNING = 0
</txt>
</msg>
<msg time='2012-12-28T11:54:43.839-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kcsnfy:326:968333812' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Picked latch-free SCN scheme 3
</txt>
</msg>
<msg time='2012-12-28T11:54:43.839-06:00' org_id='oracle' comp_id='rdbms'
msg_id='krsd_init_sdips:2428:1211400554' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
</txt>
</msg>
<msg time='2012-12-28T11:54:44.010-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ktunfy:2209:3053135360' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Autotune of undo retention is turned on.
</txt>
</msg>
<msg time='2012-12-28T11:54:44.026-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ktinfy:1611:1526000287' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>IMODE=BR
</txt>
</msg>
<msg time='2012-12-28T11:54:44.026-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ktinfy:1619:3929296192' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>ILAT =27
</txt>
</msg>
<msg time='2012-12-28T11:54:44.073-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kzunfy:2274:2892522327' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>LICENSE_MAX_USERS = 0
</txt>
</msg>
<msg time='2012-12-28T11:54:44.073-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kzanfy:1093:1161496215' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>SYS auditing is disabled
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14782:789250895' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Starting up:
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14785:3284844642' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14854:1489803337' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>Using parameter settings in server-side spfile D:\ORACLE11GR2\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14887:144004252' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt>System parameters with non-default values:
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> processes = 150
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> memory_target = 412M
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> control_files = &quot;D:\ORACLE11GR2\ORADATA\ORCL\CONTROL01.CTL&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.120-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> control_files = &quot;D:\ORACLE11GR2\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> db_block_size = 8192
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> compatible = &quot;11.2.0.0.0&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> db_recovery_file_dest = &quot;d:\oracle11gr2\flash_recovery_area&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> db_recovery_file_dest_size= 4G
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> undo_tablespace = &quot;UNDOTBS1&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> remote_login_passwordfile= &quot;EXCLUSIVE&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> db_domain = &quot;gcr1.com&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> dispatchers = &quot;(PROTOCOL=TCP) (SERVICE=orclXDB)&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> audit_file_dest = &quot;D:\ORACLE11GR2\ADMIN\ORCL\ADUMP&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> audit_trail = &quot;DB&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> db_name = &quot;orcl&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> open_cursors = 300
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> deferred_segment_creation= FALSE
</txt>
</msg>
<msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
msg_id='kspdmp:14914:3633090201' type='NOTIFICATION' group='startup'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4820'>
<txt> diagnostic_dest = &quot;D:\ORACLE11GR2&quot;
</txt>
</msg>
<msg time='2012-12-28T11:54:44.229-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='2180'>
<txt>PMON started with pid=2, OS id=2180
</txt>
</msg>
<msg time='2012-12-28T11:54:44.245-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4588'>
<txt>VKTM started with pid=3, OS id=4588 at elevated priority
</txt>
</msg>
<msg time='2012-12-28T11:54:44.260-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='4588'>
<txt>VKTM running at (10)millisec precision with DBRM quantum (100)ms
</txt>
</msg>
<msg time='2012-12-28T11:54:44.276-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4296'>
<txt>GEN0 started with pid=4, OS id=4296
</txt>
</msg>
<msg time='2012-12-28T11:54:44.292-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='1796'>
<txt>DIAG started with pid=5, OS id=1796
</txt>
</msg>
<msg time='2012-12-28T11:54:44.307-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3920'>
<txt>DBRM started with pid=6, OS id=3920
</txt>
</msg>
<msg time='2012-12-28T11:54:44.307-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='5088'>
<txt>PSP0 started with pid=7, OS id=5088
</txt>
</msg>
<msg time='2012-12-28T11:54:44.323-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4520'>
<txt>DIA0 started with pid=8, OS id=4520
</txt>
</msg>
<msg time='2012-12-28T11:54:44.339-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3356'>
<txt>MMAN started with pid=9, OS id=3356
</txt>
</msg>
<msg time='2012-12-28T11:54:44.354-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='2664'>
<txt>DBW0 started with pid=10, OS id=2664
</txt>
</msg>
<msg time='2012-12-28T11:54:44.385-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3640'>
<txt>LGWR started with pid=11, OS id=3640
</txt>
</msg>
<msg time='2012-12-28T11:54:44.385-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3856'>
<txt>CKPT started with pid=12, OS id=3856
</txt>
</msg>
<msg time='2012-12-28T11:54:44.401-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4692'>
<txt>SMON started with pid=13, OS id=4692
</txt>
</msg>
<msg time='2012-12-28T11:54:44.417-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3208'>
<txt>RECO started with pid=14, OS id=3208
</txt>
</msg>
<msg time='2012-12-28T11:54:44.432-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='1700'>
<txt>MMON started with pid=15, OS id=1700
</txt>
</msg>
<msg time='2012-12-28T11:54:44.448-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4616'>
<txt>MMNL started with pid=16, OS id=4616
</txt>
</msg>
<msg time='2012-12-28T11:54:44.448-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='4820'>
<txt>starting up 1 dispatcher(s) for network address &apos;(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))&apos;...
</txt>
</msg>
<msg time='2012-12-28T11:54:44.464-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='4820'>
<txt>starting up 1 shared server(s) ...
</txt>
</msg>
<msg time='2012-12-28T11:54:44.479-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksu_setup_oracle_base:23688:2787919602' client_id='' type='NOTIFICATION'
group='startup' level='16' host_id='ORA-DEV-11G'
host_addr='fe80::f155:d04d:4f67:718%12' module='' pid='4820'>
<txt>ORACLE_BASE from environment = d:\oracle11gr2
</txt>
</msg>
<msg time='2012-12-28T11:57:57.622-06:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:2994:4222364190' client_id='' type='NOTIFICATION'
group='admin_ddl' level='16' host_id='ORA-DEV-11G'
host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe' pid='3232'>
<txt>ALTER DATABASE mount
</txt>
</msg>
<msg time='2012-12-28T11:58:01.888-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3640'>
<txt>Successful mount of redo thread 1, with mount id 1331034565
</txt>
</msg>
<msg time='2012-12-28T11:58:01.904-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
pid='3232'>
<txt>Database mounted in Exclusive Mode
</txt>
</msg>
<msg time='2012-12-28T11:58:01.904-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
pid='3232'>
<txt>Lost write protection disabled
</txt>
</msg>
<msg time='2012-12-28T11:58:02.201-06:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:3065:2802784106' client_id='' type='NOTIFICATION'
group='admin_ddl' level='16' host_id='ORA-DEV-11G'
host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe' pid='3232'>
<txt>Completed: ALTER DATABASE mount
</txt>
</msg>
<msg time='2012-12-28T11:58:02.216-06:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:2994:4222364190' client_id='' type='NOTIFICATION'
group='admin_ddl' level='16' host_id='ORA-DEV-11G'
host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe' pid='3232'>
<txt>ALTER DATABASE open
</txt>
</msg>
<msg time='2012-12-28T11:58:02.372-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3640'>
<txt>LGWR: STARTING ARCH PROCESSES
</txt>
</msg>
<msg time='2012-12-28T11:58:02.388-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='3588'>
<txt>ARC0 started with pid=20, OS id=3588
</txt>
</msg>
<msg time='2012-12-28T11:58:02.388-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3640'>
<txt>ARC0: Archival started
</txt>
</msg>
<msg time='2012-12-28T11:58:02.388-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3640'>
<txt>LGWR: STARTING ARCH PROCESSES COMPLETE
</txt>
</msg>
<msg time='2012-12-28T11:58:02.388-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC0: STARTING ARCH PROCESSES
</txt>
</msg>
<msg time='2012-12-28T11:58:02.404-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='5032'>
<txt>ARC1 started with pid=21, OS id=5032
</txt>
</msg>
<msg time='2012-12-28T11:58:02.419-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='4456'>
<txt>ARC2 started with pid=22, OS id=4456
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
msg_id='ksbrdp:3833:3697353022' type='NOTIFICATION' group='process start'
level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
pid='5080'>
<txt>ARC3 started with pid=23, OS id=5080
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC1: Archival started
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC2: Archival started
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC3: Archival started
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC0: STARTING ARCH PROCESSES COMPLETE
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='5032'>
<txt>ARC1: Becoming the &apos;no FAL&apos; ARCH
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='5032'>
<txt>ARC1: Becoming the &apos;no SRL&apos; ARCH
</txt>
</msg>
<msg time='2012-12-28T11:58:02.435-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module=''
pid='3588'>
<txt>ARC0: Becoming the heartbeat ARCH
</txt>
</msg>
<msg time='2012-12-28T11:58:02.919-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
pid='3232'>
<txt>Errors in file d:\oracle11gr2\diag\rdbms\orcl\orcl\trace\orcl_ora_3232.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4294967296 bytes is 100.00% used, and has 0 remaining bytes available.
</txt>
</msg>
<msg time='2012-12-28T11:58:02.919-06:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
pid='3232'>
<txt>


I see that a trc file is full but have no idea how to clean it. I tried a few commands with RMAN but obviously do not know the correct syntax.

any help would be appreciated.
  • 1. Re: Newbie needs help
    EdStevens Guru
    Currently Being Moderated
    >

    See embedded comments:
    I just started working for this company and inherited a problem with an Oracle 11g database. Trial by fire and this is what I have come up with thus far. The database abruptly quit running a couple of days ago. I have NO experience with Oracle. Through research this is what I have come up with.

    Accessing the Database Control - Enterprise Manager, it shows the database instance down, the listener up, the agent connection down. No one knows the user name and password or at least doesn't know if it is correct. I first tried to start the database. I figured out that I needed to create a local admin account on the server so I could accomplish this. I start the database by clicking Startup, enter my username and password, and the next screen states:

    Current status: mounted
    open the database?

    Are you sure you want to perform this action. I choose yes

    get the following error:
    Alter database open
    Error at line 1
    Ora-03113: end-of-file on communication channel
    process ID: 4628
    Session ID: 17 Serial Number: 4119

    Disconnected from Oracle Database 11g

    Here's what I found researching:

    If I start SQLPlus /nolog and connect sys as sysdba /nolog
    it states connected to an idle instance
    I then type shutdown immediate;
    Ora-01034: Oracle not available
    ora-27101: shared memory realm does not exist

    then I type startup

    ORACLE instance started.

    Total System Global Area 430075904 bytes
    Fixed Size 2176448 bytes
    Variable Size 327158336 bytes
    Database Buffers 96468992 bytes
    Redo Buffers 4272128 bytes
    Database mounted.
    ORA-03113: end-of-file on communication channel
    Process ID: 912
    Session ID: 1 Serial number: 5

    Know one knows if the system uses an init.ora or an spfile. The spfile from what I understand cannot be edited with a text editor. Here is the content of the only init.ora file I have found:
    <snip unused init.ora file - I'll explain further down>

    BTW, there is a non-xml version located at $ORACLE_BASE/diag/rdbms/<oracle_sid>/<oracle_sid>/trace. It works much better for posting to the forum.
    The contents of the alert log:
    <txt>************************************************************************
    </txt>
    </msg>
    <msg time='2012-12-28T11:22:15.267-06:00' org_id='oracle' comp_id='rdbms'
    client_id='' type='UNKNOWN' level='16'
    host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
    pid='828'>
    <txt>Instance terminated by USER, pid = 828
    </txt>
    </msg>
    <msg time='2012-12-28T11:54:43.714-06:00' org_id='oracle' comp_id='rdbms'
    msg_id='opistr_real:935:3971575317' type='NOTIFICATION' group='startup'
    level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
    pid='4820'>
    <txt>Starting ORACLE instance (normal)
    Ok, your database is starting
    </txt>
    <snip>
    <txt>Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
    archive log files are written to the location specified by RECOVERY_FILE_DEST. This will be key.

    </txt>
    <snip>
    <txt>Using parameter settings in server-side spfile D:\ORACLE11GR2\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
    now we know you are using an spfile - D:\ORACLE11GR2\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
    </txt>
    <snip>
    <txt> control_files = "D:\ORACLE11GR2\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL"
    </txt>
    and now we know you have two control files. good.

    <snip>
    <txt> db_recovery_file_dest = "d:\oracle11gr2\flash_recovery_area"
    and here is the location of the recovery file dest. your archivelog files are written to a subdirectory of that.
    </txt>
    </msg>
    <msg time='2012-12-28T11:54:44.135-06:00' org_id='oracle' comp_id='rdbms'
    msg_id='kspdmp:14923:527288951' type='NOTIFICATION' group='startup'
    level='16' host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12'
    pid='4820'>
    <txt> db_recovery_file_dest_size= 4G
    and you have told oracle that it can only use 4g of d:\oracle11gr2\flash_recovery_area. Regardless of how much space the OS says you have there.
    </txt>
    <snip>
    <txt> diagnostic_dest = "D:\ORACLE11GR2"
    Ok, your non-xml version a=of the alert log will be found in a subdirectory of D:\ORACLE11GR2
    </txt>
    <snip>
    <txt>ALTER DATABASE mount
    Initialization complete, now we start the 'mount' phase of opening the control files.
    </txt>
    <snip>
    <txt>Completed: ALTER DATABASE mount
    And we successfully opened the control file.
    </txt>
    </msg>
    <msg time='2012-12-28T11:58:02.216-06:00' org_id='oracle' comp_id='rdbms'
    msg_id='opiexe:2994:4222364190' client_id='' type='NOTIFICATION'
    group='admin_ddl' level='16' host_id='ORA-DEV-11G'
    host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe' pid='3232'>
    <txt>ALTER DATABASE open
    Now we will open the database data files, etc.
    </txt>
    <snip>
    <txt>Errors in file d:\oracle11gr2\diag\rdbms\orcl\orcl\trace\orcl_ora_3232.trc:
    ORA-19815: WARNING: db_recovery_file_dest_size of 4294967296 bytes is 100.00% used, and has 0 remaining bytes available.
    </txt>
    OOPS!! That 4gb FRA is full. Oracle can't write any more to it, so cannot continue.
    </msg>
    <msg time='2012-12-28T11:58:02.919-06:00' org_id='oracle' comp_id='rdbms'
    client_id='' type='UNKNOWN' level='16'
    host_id='ORA-DEV-11G' host_addr='fe80::f155:d04d:4f67:718%12' module='sqlplus.exe'
    pid='3232'>
    <txt>


    I see that a trc file is full but have no idea how to clean it. I tried a few commands with RMAN but obviously do not know the correct syntax.

    any help would be appreciated.
    You probably have not done any housekeeping of the FRA, allowing it to get full of archivelog files. If this is just a test ssystem, I'd

    1 - delete the archivelog files, using OS level commans
    2 - let oracle know about this:
    c:> set ORACLE_SID=orcl
    c:> rman target /
    rman> crosscheck archivelog all;
    rman> delete nomprompt expired archivelog;
    This will allow you to get your database started. Then you need to implement a regular schedule of rman backup, to include a backup of the archivelogs. Since you are using the FRA for that, it should self-manage itself once you start taking backups, which will allow it to delete old backups and archivelogs when they are no longer needed to protect your recovery window. I'd also increase the size of the FRA, 4gb is pretty tiny. Also, if this is a test db, you could consider not running it in archivelog mode.
  • 2. Re: Newbie needs help
    EdStevens Guru
    Currently Being Moderated
    979090 wrote:
    I just started working for this company and inherited a problem with an Oracle 11g database. Trial by fire and this is what I have come up with thus far. The database abruptly quit running a couple of days ago. I have NO experience with Oracle. Through research this is what I have come up with.
    <snip>
    In addition to my technical comments in the other post, this leaves me wonder why, if you have no experience with Oracle, you got roped into having to fix this.... What you revealed from your research so far is pretty impressive for someone with no experience. (there are people who have been on this board for years that couldn't have done what you've done so far.)
  • 3. Re: Newbie needs help
    marksmithusa Journeyer
    Currently Being Moderated
    Indeed. Fair play to you for at least trying. Most people tend to throw their hands up in the air (sometimes, saying AYO'...). Good luck in trying to figure Oracle out with no-one at your company to lean on. It's tough!
  • 4. Re: Newbie needs help
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Welcome to OTN

    Did you try to search for the error
    Ora-03113: end-of-file on communication channel
    Search in your alert log
  • 5. Re: Newbie needs help
    982093 Newbie
    Currently Being Moderated
    I think I am a glutton for punishment, is how I got roped into this. :)

    If it is not a "test" environment, can I still run your recommended commands to clear the log, so I can get the database up?
  • 6. Re: Newbie needs help
    EdStevens Guru
    Currently Being Moderated
    979090 wrote:
    I think I am a glutton for punishment, is how I got roped into this. :)

    If it is not a "test" environment, can I still run your recommended commands to clear the log, so I can get the database up?
    Yes, but you run a risk of creating a situation where you have an 'unrecoverable' period of time should something go wrong immediately or down the line. So for a productin system you'd want to add a preliminary step to take an OS level backup of all files to be deleted, before you delete them.

    Just to give you a quick understanding of the risk ...
    Suppose you take a full (Inc 0) backup of the database at 0200 on Sunday.
    And you take an incremental (inc 1) backup at 0200 the other 6 days.
    Of course, you are also in archivelog mode, so as your redo logs are filled the are copied to archive redo logs then reused.

    Now, suppose your DB goes up in flames at 1400 on Tuesday. First, rman will restore all files from the Sunday Inc0 backup. Then it will apply all block changes from the Inc 1 backups taken on Monday and Tuesday. Then it will start applying redo from those archivelog files. So, what happens if you have deleted those archivelog files?

    And a little more explanation about the FRA. You set the size and location with the parameters I mentioned in an earlier post. However, this does NOT actually reserve space on the disk. It simply gives oracle a 'budget' to work with. Oracle doesn't really know how much space is actually on the disk. It only knows that you've told it it can have 4gb. So it keeps track of what it writes to it and deletes from it. If you delete files from the FRA by use of OS commands, oracle won't know the space has been freed up until you issue an rman 'crosscheck' command. Likewise, if you allow other files to consume space on the same mount point where you defined the FRA, oracle has no way of knowing this and will, by its own bookkeeping, think there is more space available than actually exists.

    Your current problem is probably due to NOT regularly backing up and deleting (within rman) your archivelog files. Thus, it just keeps writing them to the FRA until such time as it has used all of the allocated space. Even if you increase the FRA allocation, you will eventually fill it up again without a regular backup/delete operation. THat should also be in conjunction with regular database backups. I have two regular rman jobs.

    The first takes a full (inc 0) backup and runs once a week.
    run {
      backup incremental level 0 database ;
      backup archivelog all not backed up 1 times ;
      delete noprompt archivelog all backed up 1 times to device type disk;
    }
    run {
    delete noprompt obsolete;
    crosscheck backup;
    delete noprompt expired backup;
    crosscheck archivelog all;
    delete noprompt expired archivelog all;
    }
    list backup;
    the second runs the other 6 days and is identical except instead of "backup incremental level 0 " it has "backup incremental level 1 "

    Edited by: EdStevens on Dec 30, 2012 5:24 PM
  • 7. Re: Newbie needs help
    rp0428 Guru
    Currently Being Moderated
    >
    If it is not a "test" environment, can I still run your recommended commands to clear the log, so I can get the database up?
    >
    NO! STOP IMMEDIATELY!

    Don't do ANYTHING without first taking a cold backup of your entire database. That cold backup should include any and all configuration files such as your spfile.

    In your situation I suggest that you don't take ANY action that can't be undone somehow.

    You also want to document all current configuration settings at both the OS level and the database level.

    Another important thing to do is to create a PFILE from that SPFILE that you are using. You can do that without starting the database. This file will include all of the non-default parameters being used.

    For that see CREATE PFILE in the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6008.htm
    >
    Creating a Parameter File: Example The following example creates a text parameter file my_init.ora from a binary server parameter file s_params.ora:
    CREATE PFILE = 'my_init.ora' FROM SPFILE = 's_params.ora';
    >
    This new PFILE should also specify the DB_RECOVERY_FILE_DEST_SIZE and you can confirm that 4G is the size that is set.
    Save a copy of this original PFILE and then you can modify that parameter to 6 or 8GB so you don't run out of space.

    RESTRICTED MODE STARTUP

    When you do startup the database be sure to start it in restricted mod. This will prevent normal users from connecting until you can confirm the state of the database.

    See Restricting Access to an Instance at Startup in the DBA Guide
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm
    >
    You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

    •Perform an export or import of data
    •Perform a data load (with SQL*Loader)
    •Temporarily prevent typical users from using data
    •Perform certain migration or upgrade operations

    Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

    The following command starts an instance (and mounts and opens the database) in restricted mode:
    STARTUP RESTRICT

    You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.
    >
    And when you do startup the database specify this new modified PFILE so it will use your new recovery size. Note: you should have already saved the original SPFILE when you did the cold backup.


    See Setting the Flash Recovery Area Location and Initial Size in the Oracle® Database Backup and Recovery User's Guide
    http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm#BRADV89422
    >
    •Shut down the database and set the DB_RECOVERY_FILE_DEST_SIZE parameter in the initialization parameter file of the database, as shown in the following example:
    DB_RECOVERY_FILE_DEST_SIZE = 10G
  • 8. Re: Newbie needs help
    982093 Newbie
    Currently Being Moderated
    Thanks! It worked. Database is back up and running.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points