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;

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_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.

3 Comments

You must be Logged on to comment or reply to a post.

Fabian Schoen March 12, 2015 at 1:30 pm

Hi Serhat

A remark on one of the errors:

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

Probably it is worth to check why there are duplicate entries in the table instead of just create the index non-unique.

If the model expect that the index should be created as unique index then there is a reason for it. I guess 🙂

regards

like (0)
1. Former Member Post authorMarch 12, 2015 at 1:44 pm
  
  Hi Fabian,
  
  You’re right somehow! : ) Actually it’s some kind of workaround. Please see that note 11369 – ORA-1452 during import after export from other system
  
  Kind regards,
  
  Serhat
  
  like (0)
  1. Former Member March 12, 2015 at 1:58 pm
    
    Serhat Yüceant wrote:
    
    Hi Fabian,
    
    You’re right somehow! : )
    
    Unfortunately it is not “somehow”
    
    SAP uses unique indexes to be sure that the “primary” key is not duplicated. If the unique index does not exist, then there is a high chance of inserting a duplicate value (situation that already happens)
    
    creating a index not unique is a workaround for the wrong issue, as the real issue is why there are duplicated keys (probably because the unique index was missing) why the unique index was missing in the first place and what keys have to be “deleted” to allow the unique index to be created (application issue, not a DB one as people like to think).
    
    like (0)