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:protoc ol error") when trying a non-SQL*Net connect like "sqlplus / as sysdba" on Windows boxes usually indicates that the (Windows-) service (Usually called OracleService<S ID> ) 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%\d atabase\init<yo urSID>.ora file and set the DB_RECOVERY_FIL E_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]

tried step 3?

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


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

D:\oracle\produ ct\10.2.0\db_1\ BIN>oradim -start -sid PPROD -pfile='D:\oracl e\product\10.2. 0\db_1\database \initPPROD.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_fil e_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\produ ct\10.2.0\admin \pprod\bdump\pp rod_arc1_3468.t rc:
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\ORAC LEBACKUP\PPROD\ ARCHIVELOG\2010 _08_25\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\produ ct\10.2.0\admin \pprod\bdump\pp rod_arc1_3468.t rc:
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\ORAD ATA\PPROD\LOG1. ORA'
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORAD ATA\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\produ ct\10.2.0\admin \pprod\bdump\pp rod_arc0_3464.t rc:
ORA-16014: log 1 sequence# 8348 not archived, no available destinations
ORA-00312: online log 1 thread 1: 'F:\ORACLE\ORAD ATA\PPROD\LOG1. ORA'
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORAD ATA\PPROD\LOG1A .ORA'

[valuablevalerie]

the sql.net file says


*************** *************** *************** *************** ***********
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(A DDRESS=(PROTOCO L=BEQ)(PROGRAM= oracle)(ARGV0=o raclePPROD)(ARG S='(DESCRIPTION =(LOCAL=YES)(AD DRESS=(PROTOCOL =beq)))'))(CONN ECT_DATA=(SID=P PROD)(CID=(PROG RAM=D:\oracle\p roduct\10.2.0\c lient_1\bin\sql plus.exe)(HOST= PYRAMED1)(USER= Administrator)) ))

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\produ ct\10.2.0\db_1\ network\admin\l istener.ora
Log messages written to D:\oracle\produ ct\10.2.0\db_1\ network\log\lis tener.log
Trace information written to D:\oracle\produ ct\10.2.0\db_1\ network\trace\l istener.trc
Trace level is currently 0

Started with pid=1080
Listening on: (DESCRIPTION=(A DDRESS=(PROTOCO L=tcp)(HOST=PYR AMED1)(PORT=152 1)))
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=Adm inistrator))(CO MMAND=status)(A RGUMENTS=64)(SE RVICE=LISTENER) (VERSION=169870 080)) * status * 0
25-AUG-2010 12:23:58 * service_registe r * pprod * 0
25-AUG-2010 12:44:50 * (CONNECT_DATA=( SERVICE_NAME=pp rod)(CID=(PROGR AM=C:\Program Files\Media Highway\PyraMED \PyraMED.exe)(H OST=PYRAMED1)(U SER=Administrat or))) * (ADDRESS=(PROTO COL=tcp)(HOST=1 72.16.100.80)(P ORT=1049)) * establish * pprod * 0
25-AUG-2010 12:44:53 * (CONNECT_DATA=( SERVICE_NAME=pp rod)(CID=(PROGR AM=C:\Program Files\Media Highway\PyraMED \PyraMED.exe)(H OST=PYRAMED1)(U SER=Administrat or))) * (ADDRESS=(PROTO COL=tcp)(HOST=1 72.16.100.80)(P ORT=1050)) * establish * pprod * 0