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.
I have faced this error during installation of support packages. I've updated my tp executable according to the note 2006658.
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
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;
There is no .dbf files enough in your database.
alter system set db_files=512 scope=spfile ;
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
alter user <USERNAME> identified by <PASSWORD> account unlock;
I've faced this error during a system copy.
alter database clear unarchived logfile group 3;
alter database open resetlogs;
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);
Note 869521 - Oracle <= 10g: TM locks with REBUILD ONLINE / CREATE ONLINE
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 |