Various ORA-* errors and solutions&workarounds

Hello everyone,

I wanted to share some ORA-* errors which I’ve already faced, and provide solutions or workarounds for them. The list can be expanded with other ORA- error codes.

I hope it helps you with your projects.

• ORA-01756  CLEAR_OLD_REPORTS

I have faced this error during installation of support packages. I’ve updated my tp executable according to the note 2006658.

• ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

Table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP

———- —————- ———– ——————————– — – —————- ———

1123416650 ATEST 0 EAD774574D3C7D22D4ADF220356092C5 NX Y ACTIVE 27-FEB-12

1123416650 TEST 0 CEF1782AC11ED02FAB1E1D121B19FC3F HX Y ACTIVE 27-FEB-12

Delete the inconsistent entry

SQL> delete from DBSNMP.BSLN_BASELINES where INSTANCE_NAME=’ATEST’;

1 row deleted.

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP

———- —————- ———– ——————————– — – —————- ———

1123416650 ATEST 0 EAD774574D3C7D22D4ADF220356092C5 NX Y ACTIVE 27-FEB-12

• ORA-01157 PSAPTEMP

1) SQL> select NAME,bytes from v$tempfile;

NAME

——————————————————————————–

/oracle/TST/sapdata1/temp_1/temp.data1

/oracle/TST/sapdata2/temp_2/temp.data2

2) select file_name from dba_temp_files;

Determine which files are not consistent.

ERROR at line 1:

ORA-01157: cannot identify/lock data file 256 – see DBWR trace file

ORA-01110: data file 256: ‘/oracle/TST/sapdata2/temp_2/temp.data2’

3) First create new files instead the inconsistent ones, then drop the old ones.

mkdir /oracle/TST/sapdata1/temp_3

alter tablespace PSAPTEMP add tempfile ‘/oracle/TST/sapdata1/temp_3/temp.data3’ size 10480M autoextend off;

alter database tempfile ‘/oracle/TST/sapdata2/temp_2/temp.data2’ drop;

alter database tempfile ‘/oracle/TST/sapdata2/temp_2/temp.data2’ drop;

• ORA-00059: maximum number of DB_FILES exceeded

There is no .dbf files enough in your database.

alter system set db_files=512 scope=spfile ;

• ORA-28003

After a system copy I’ve faced this error.

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL ;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL ;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED ;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_SAP

• ORA-01017: invalid username/password; logon denied
• ORA-28000: the account is locked

alter user <USERNAME> identified by <PASSWORD> account unlock;

• ORA-00392  log 8 of thread 1 is being cleared, operation not allowed

I’ve faced this error during a system copy.

alter database clear unarchived logfile group 3;

alter database open resetlogs;

• ORA-01452 cannot create unique index. Duplicate keys found.

        For more information see 11369 – ORA-1452 during import after export from other system.

Try to create manually not a unique but non-unique index. I had this error during a migration. I fetched the SQL which has been tried to be executed and created it as non-unique as a workaround.

CREATE INDEX SAPSR3.”SMSCMAID~A01″ ON SAPSR3.”SMSCMAID” (“MANDT”,

    “SCHEDULERID”)

    PCTFREE 10

    INITRANS 002

    TABLESPACE PSAPSR3

    NOCOMPRESS

    STORAGE (INITIAL 0000000016 K

    NEXT 0000000080 K

   MINEXTENTS 0000000001

   MAXEXTENTS UNLIMITED

   PCTINCREASE 0000

   FREELISTS 001);

• ORA-00054 During index building

Note 869521 – Oracle <= 10g: TM locks with REBUILD ONLINE / CREATE ONLINE

• ORA-01173:

Data dictionary indicates missing data file from system tablespace

During restore for a system copy.

https://docs.oracle.com

Cause:

Either the database has been recovered to a point in time in the future of the control file or a datafile from the system tablespace was omitted from the create control file command previously issued.

Action:

For the former problem you need to recover the database from a more recent control file. For the latter problem, simply recreate the control file checking to be sure that you include all the datafiles in the system tablespace.