2

We discovered that our Oracle 12c database was taking up a large amount of disk space with archive logs, so I figured out how to delete the files, but I'm unable to disable archive logging. We don't have an in-house DBA, so I'm trying to figure this out myself. From lots of Googling, it's my understanding that I need to do the following:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

I've got two problems though. I can't do the "sqlplus / as sysdba", it complains about "ORA-12560: TNS:protocol adapter error" unless I specify the SID, even though it the environment variable is defined. Also, when I do the shutdown, I get "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" and it disconnects me from Oracle.

C:\Users\administrator.MYDOMAIN>set oracle_sid=orcm

C:\Users\administrator.MYDOMAIN>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 5 11:11:33 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error


SQL> connect sys/password@orcm as sysdba
Connected.
SQL>
SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\oracle\product\12.1.0\dbhome_1\RDBMS
Oldest online log sequence     6869
Next log sequence to archive   6871
Current log sequence           6871
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>

Any assistance would be greatly appreciated.

  • You really need to get a DBA. If you did succeed in turning off archive log, then you would give up the ability to do point in time recovery. Did your last DBA create a backup job in Windows scheduler? Do you know where the backups are being written? Do you know when the last successful backup completed? If you are stuck I have worked with Oracle on Windows for over 15 years, I would be happy to help you. just send me a private message. – Gandolf989 Feb 6 '18 at 13:58
1

ORA-12560: TNS:protocol adapter error on Windows is typically caused by incorrent ORACLE_HOME and ORACLE_SID environment variables.

Find the database service:

enter image description here

Find the path and name of the instance:

enter image description here

Based on that:

set ORACLE_HOME=O:\oracle\product\12.2.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=RTG

Then try again:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.