i inherited an windows oracle box. when i try to login to database, i get 12560, etc

[valuablevalerie]

i inherited an windows oracle box. when i try to login to database, i get 12560, 1033, 19815, 3113, 16014, 19809, 16038. when i log on using [connect sys/{password}@{SID} as sysdba], i get ora 1033. when i log on using [connect / as sysdba], i get ora 12560. the other errors are in the background. i know that i have to increase the size of the recovery file, but i cannot even log in. My tnsnames and listener files are in order. The database services are currently running.

Please help!!
Thanks

[magicwand]

The database services are currently running

I doubt that.

You are documenting a couple of mutually exclusive error messages:

ORA-12560 ("TNS:protocol error") when trying a non-SQL*Net connect like "sqlplus / as sysdba" on Windows boxes usually indicates that the (Windows-) service (Usually called OracleService<SID> ) is not started.

ORA-1033 ("ORACLE initialization or shutdown in progress") seems to point to the fact that someone (?) has issued a "shutdown" command, and for whatever reason, the db is not going down

Since nobody will be able to connect to the db (because of the ORA-1033) and you have to bring down the db anyway, I'd suggest the following procedure:

1.) go to windows services and stop the database service, the listener service and (if running) the dbconsole service and the scheduler service.

This should be equal to a "shutdown abort".

2.) edit your %ORACLE_HOME%\database\init<yourSID>.ora file and set the DB_RECOVERY_FILE_DEST_SIZE parameter accordingly (make sure there's enough space on disk for the new value)

3.) in a DOS window type

oradim -start -sid <yourSID> -pfile=<ORACLE_HOME>\database\init<yourSID>.ora
 
and 
 
lsnrctl start
 

5.) take a full backup.

6.) issue the command

create spfile from pfile;

6.) in sqlplus:

shutdown immediate;
startup

P.S.: As always, when no version is mentioned, I assume the latest Version (11.2.0.1.) is used.

[valuablevalerie]

I apologize for the omission. This is version 10.2.0. when I searched the directory for the init{sid}.ora file, it wasn't there. Do I create it. There is a init.ora file in a different directory. Is this the same file

[magicwand]

If you don't find your init<SID>.ora, you can produce it after Step 1.):

startup nomount;
create pfile from spfile;
 

[valuablevalerie]

when I type in the oradim command, this is what I get


D:\oracle\product\10.2.0\db_1\BIN>oradim -start -sid PPROD -pfile=D:\oracle\product\10.2.0\db_1\database\initP PROD.ora
DIM-00003: An argument is missing for the parameter.

D:\oracle\product\10.2.0\db_1\BIN>oradim -start -sid PPROD -pfile='D:\oracle\product\10.2.0\db_1\database\init PPROD.ora'
DIM-00003: An argument is missing for the parameter.

[valuablevalerie]

Now I get ORA 12560 and no longer get the 03301 error. In the alert_pprod.log file I get the following information.


Wed Aug 25 12:10:38 2010
************************************************** **********************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************** **********************
Wed Aug 25 12:10:39 2010
Errors in file d:\oracle\product\10.2.0\admin\pprod\bdump\pprod_a rc1_3468.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 12816896 bytes disk space from 107374182400 limit

ARC1: Error 19809 Creating archive log file to 'F:\ORACLE\ORACLEBACKUP\PPROD\ARCHIVELOG\2010_08_2 5\O1_MF_1_8348_U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Aug 25 12:10:39 2010
Errors in file d:\oracle\product\10.2.0\admin\pprod\bdump\pprod_a rc1_3468.trc:
ORA-16038: log 1 sequence# 8348 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'F:\ORACLE\ORADATA\PPROD\LOG1.ORA'
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\PPROD\LOG1A.ORA'

Wed Aug 25 12:11:38 2010
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Aug 25 12:11:38 2010
Errors in file d:\oracle\product\10.2.0\admin\pprod\bdump\pprod_a rc0_3464.trc:
ORA-16014: log 1 sequence# 8348 not archived, no available destinations
ORA-00312: online log 1 thread 1: 'F:\ORACLE\ORADATA\PPROD\LOG1.ORA'
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\PPROD\LOG1A.ORA'

[valuablevalerie]

the sql.net file says


************************************************** *********************
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracl e)(ARGV0=oraclePPROD)(ARGS='(DESCRIPTION=(LOCAL=YE S)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=P PROD)(CID=(PROGRAM=D:\oracle\product\10.2.0\client _1\bin\sqlplus.exe)(HOST=PYRAMED1)(USER=Administra tor))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
Time: 25-AUG-2010 12:26:22
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 126
nt OS err code: 0

[valuablevalerie]

my listener.log file says

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production on 25-AUG-2010 12:23:22

Copyright (c) 1991, 2006, Oracle. All rights reserved.

System parameter file is D:\oracle\product\10.2.0\db_1\network\admin\listen er.ora
Log messages written to D:\oracle\product\10.2.0\db_1\network\log\listener .log
Trace information written to D:\oracle\product\10.2.0\db_1\network\trace\listen er.trc
Trace level is currently 0

Started with pid=1080
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PYRAMED1 )(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
25-AUG-2010 12:23:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administ rator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LIST ENER)(VERSION=169870080)) * status * 0
25-AUG-2010 12:23:58 * service_register * pprod * 0
25-AUG-2010 12:44:50 * (CONNECT_DATA=(SERVICE_NAME=pprod)(CID=(PROGRAM=C: \Program Files\Media Highway\PyraMED\PyraMED.exe)(HOST=PYRAMED1)(USER=A dministrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.100.80)(PORT=1 049)) * establish * pprod * 0
25-AUG-2010 12:44:53 * (CONNECT_DATA=(SERVICE_NAME=pprod)(CID=(PROGRAM=C: \Program Files\Media Highway\PyraMED\PyraMED.exe)(HOST=PYRAMED1)(USER=A dministrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.100.80)(PORT=1 050)) * establish * pprod * 0