Oracle 12c and SYSAUX Tablespace Growing Rapidly

Problem:

You recently upgraded to Oracle 12c and your SYSAUX tablespace continues to grow in size. This may be happening because there is a default Unified Audit policy called ORA_SECURECONFIG.

32 bit Client Install Fails on Windows Server 2012

Problem:

Oracle installer wizard fails and/or disappears on Windows 2012 r2

ORA-600 [QKAFFSINDEX5] and Oracle 12c

Problem:

You are getting ORA-00600: internal error code, arguments: [qkaffsindex5] in Oracle 12.1.0.1 or higher.

This seems like a bug in 12c and it happens because your query is using a function based index and DESC.

Roles could not be executed even after they were recreated

Problem:
Database Administrator grants privileges to a role, then grants this role to a user.  When THE user tries to execute those privileges through this role, it fails. However, user can execute privileges directly.

Error message:

ORA-00942: table or view does not exist

ORA-01031: insufficient privileges

Problem explanation:

In SYS.DBA_ROLE_PRIVS this role shows up but DEFAULT_ROLE column is set to 'N' therefore role can not be executed as it is not a default.

Oracle Error ORA-19502: write error on file

Problem:

You are getting ORA-19502 during RMAN backup or add file file/logfile command.

ORA-31693: Table data object failed to load/unload and is being skipped due

Problem:

You are getting ORA-31693 and ORA-02354 when running expdp

Error message:

ORA-31693: Table data object "HR"."EMP" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01426: numeric overflow

ORA-01000: maximum open cursors exceeded

Problem:

You are continuously getting ORA-01000 and cannot even create a table.

Solution:

Usually, this error is encountered when init.ora parameter OPEN_CURSORS is set too low.

Or developers write code that does not close cursors properly.

ORA-12557: TNS:protocol adapter not loadable

Problem:

You are getting ORA-12557 while running Oracle on Windows.

ORA-12557 is usually  related to Microsoft Windows environments and Oracle HOME PATH variable.

Flashback Data Archive (Oracle FDA)

What is FDA (Flashback Data Archive) ?

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime.A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

How to recreate TEMP Tablespace in Oracle

Problem:

You need to recreate a temporary tablespace in Oracle 9i/10g/11g/12c.

Solution:

--Create Temporary Tablespace Temp2 CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE   '/oradata/testdb/temp02.dbf' SIZE 500M; --Move Default Database temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; --Drop temp tablespace DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; --Recreate Tablespace Temp CREATE TEMPORARY TABLESPACE TEMP TEMPFILE   '/oradata/testdb/temp01.dbf' SIZE 500M; --Move Tablespace Temp, back to new temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; --Drop temporary for tablespace temp DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.

ORA-01187: cannot read from file nnn because it failed verification tests

Problem:

After RMAN duplicate/clone of an Oracle database you are getting ORA-01187:

boris@testdb> select file_name from dba_temp_files     select file_name  from dba_temp_files * ERROR at line 1:         ORA-01187: cannot read from file 1043 because it failed verification tests ORA-01110: data file 501: '/oradata/testdb/temp01.dbf'

There are no problems with the clone based on the log files and all data files seem to be ok.

RMAN-05541: no archived logs found in target database

Problem:

You are getting  RMAN-05541 error when duplicating database from a consistent (cold) RMAN backup.

Storing pictures and photos in Oracle

Problem:

You would like to be able to store and extract images and other binary large objects (BLOBs) inside your Oracle Database.

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

Problem:

BSLN_MAINTAIN_STATS_JOB fails with the following error:

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" ORA-06502: PL/SQL: numeric or value error ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1

Changing Linux password via a script

Problem:

You need to change your Oracle Linux password using a script.

Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: DBSNMP.BSLN_INTERNAL

You are seeing following errors in the database alert.log file:

ORA-12012: error on auto execute of job 41042 ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN_INTERNAL" ORA-06512: at line 1

ORA-28040: No matching authentication protocol

You are getting ORA-28040  (ORA-28040):  No matching authentication protocol.

Oracle database version: 12.1.0.1

Oracle client version: 11.2.0.3

ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

As server memory upgraded from 64GB RAM to 96GB.Even though when I am trying to do startup the database and I am getting following error:

ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

OS: Red Hat Linux

Database version: 10g and later

Error message:

SQL> startup nomount ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

How to remove unwanted components from an Oracle database

This article explains how to uninstall all optional components such as OWB, APEX, EM, OLAP, OWM, and others from an Oracle database. Oracle usually does not recommend uninstalling Oracle components manually unless you have a good reason.  Data Pump Export/Import is probably a safer alternative. Please take a full cold backup of your database before trying this approach.