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.
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.
Hi Serhat
A remark on one of the errors:
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
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
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).