ORA-12514 TNS error after restart (followed by ORA-01261: Parameter db_recovery_file_dest)

In one of my previous article I’ve managed to clean Oracle logs using ADRCI tool but after restarting my Database, I was unable to connect.

The issue was there before I was not aware about it. It’s not related to ADRCI.

The DB Oracle Service (here under Windows) started successfully but I was unable to connect and received the well known ORA-12514 TNS error.

Well, I did not change anything about the TNS…

I checked the TNS status using “LNSRCTL STATUS” in a Command Prompt. Indeed, no Listener available for my Instance.

I then noticed the Oracle DB Process was consuming less than 200Mo of memory. The database was not started successfully (indeed the service is running…).

In a Command Prompt, connect and try to start it manually:

SQL> connect / as sysdba
ConnectÚ Ó une instance inactive.
SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translat
ed
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Acc┐s impossible ┐ l'unit┐ ou au r┐pertoire indiqu┐.
O/S-Error: (OS 3) The system cannot find the path specified.

 

Ok, found the problem; the parameter “db_recovery_file_dest” was incorrect.

I managed to start the instance using an old PFILE located in ../oracle/admin/db_name/init.ora.XXXXXXXX

startup mount pfile="D:\oracle\admin\dn_name\pfile\init.ora.7720159372";

More info here: https://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm#ADMIN11140

 

I created a PFILE from the current SPFILE (file will be created under “../oracle/11.2.0/db/database”):

create pfile from spfile;

I edited the newly created PFILE to correct the parameter path and created the SPFILE from this new PFILE:

 create spfile from pfile='..\oracle\11.2.0\db\database\INITdb_name.ORA';

Problem solved.

Advertisements

Oracle – Clear Logs & Traces using ADRCI

One of my Oracle servers became full and I noticed one of my Database consumed over 40Go of logs stored in “../diag/rdbms/db_name” unless the Retention Policy was low…

Here’s how i cleaned this up manually using ADRCI tool in a Command Prompt.

C:\Users\Administrator>adrci

ADRCI: Release 11.2.0.3.0 - Production on Mer. Mars 22 09:53:24 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "D:\oracle"
adrci> show homes
ADR Homes:
diag\rdbms\db_name\db_name
adrci> set home diag\rdbms\db_name\db_name
adrci> purge

 

If needed, you can check the Retention Policy using:

adrci> show control

And change it using (Example. Note it is defined in Hours):

adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)

 

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.
SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files