ORA-14400: inserted partition key does not map to any partition
insert into ldbo.TBLSNPRISKDETAILS (CFIRMNUMBER, CCLIENTCODE, DSAUDADATE, NPRODUCTCODE, NSEGMENTTYPE, NFINANCEAMOUNT, NMARGINAMOUNT, NBILLAMOUNT, NCOLLATERAL, NCOLLATERALBHCUT, NDEMATSTOCK, NDEMATSTOCKBHCUT, NOVERALLPOSITION, NOVERALLPOSITIONBHCUT, NNETMARGIN, NNETMARGINBHCUT, NFOEXPOSURE, NDERIVATIVESNOTIONAL, NOSSALES, NMARGINPERCENTAGE, NMARPERBHCUT, NPOASTOCK, NPOASTOCKBHCUT, NUNREALIZEDAMOUNT, NBHLEVEL, NBHLEVELBHCUT, NMFDEMATSTOCK, NMFDEMATSTOCKBHCUT, NMFPOASTOCK, NMFPOASTOCKBHCUT, NTILLDATEFINANCE, NTILLDATEMARGIN, CSCHEMETYPE, CBRANCHCODE, CCTCLBRANCHCODE, NMFNOTIONAL, CCOMPANYNAME, CCLIENTCATEGORY, NPENDINGACCEPTED, NADHOCCHARGE, NACCRUEDINTEREST, CNEXTRADEDATE, CBDRCODE, NPISAMOUNT, NGLOBALMARGIN, NTOTALSTOCK, NDPCHARGES, NTOTALILLBENSTOCK)
values ('FOR-000001', 'N124349 ', to_date('31-05-2017', 'dd-mm-yyyy'), 0, 0, 267.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 58630.70, 64229.30, 58363.70, 63962.30, 0.00, 0.00, 0.00, 99.54, 99.58, 58630.70, 64229.30, 0.00, 0, 0, 0.00, 0.00, 0.00, 0.00, 267.00, 0.00, 'NORMAL ', '8002', 'HO ', 0.00, 'RSL_SELF_RACE_ONLINE', ' ', 0.00, 0.00, 0.00, '02/04/2014', ' ', 0.00, 51034.05, 64229.30, 0.00, 0);
ORA-14400: inserted partition key does not map to any partition
STORAGE(INITIAL 10000M NEXT 1000M)
TABLESPACE "USR"
PARTITION BY RANGE ("DSAUDADATE")
(
PARTITION SAUDA_APR values LESS THAN (TO_DATE('01-MAY-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAY values LESS THAN (TO_DATE('01-JUN-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUN values LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUL values LESS THAN (TO_DATE('01-AUG-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_AUG values LESS THAN (TO_DATE('01-SEP-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_SEP values LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_OCT values LESS THAN (TO_DATE('01-NOV-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_NOV values LESS THAN (TO_DATE('01-DEC-2015','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_DEC values LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JAN values LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_FEB values LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAR values LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
add following
PARTITION SAUDA_MAX values LESS THAN (maxvalue) TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M )
Wednesday, January 27, 2016
tnsping no listener
tns-12541 no listener nslookup dns timeout
etc/hosts
network issue right
nslookup DB-serverIP
If timeout contact to IT Admin
ipconfig /registerdns
TNS-03505: Failed to resolve name
This shows that the configuration is incorrect and the SID/service name that you are attempting to connect to cannot be resolved. Check the TNSNames entry is there for your database you are attempting to connect to. If it is, check that the IP address/DNS entry are correct as previously mentioned above when regarding configuring the TNSNames.ora file. Then, check that you can ping the IP address from the server you are making the connection from. If you can’t, that is your problem. If you are using DNS you can try using NSLOOKUP to resolve the name. For example, NSLOOKUP TEST will attempt to look up and show you the IP address for the TEST name in your DNS. The problem will be in this area somewhere. - See more at: http://www.ora00600.com/wordpress/articles/listener-tnsnames-configuration/#sthash.lsuFuJ4S.dpuf
etc/hosts
network issue right
nslookup DB-serverIP
If timeout contact to IT Admin
ipconfig /registerdns
TNS-03505: Failed to resolve name
This shows that the configuration is incorrect and the SID/service name that you are attempting to connect to cannot be resolved. Check the TNSNames entry is there for your database you are attempting to connect to. If it is, check that the IP address/DNS entry are correct as previously mentioned above when regarding configuring the TNSNames.ora file. Then, check that you can ping the IP address from the server you are making the connection from. If you can’t, that is your problem. If you are using DNS you can try using NSLOOKUP to resolve the name. For example, NSLOOKUP TEST will attempt to look up and show you the IP address for the TEST name in your DNS. The problem will be in this area somewhere. - See more at: http://www.ora00600.com/wordpress/articles/listener-tnsnames-configuration/#sthash.lsuFuJ4S.dpuf
ora-31626 ora-31687 ora-31688
If your init parameter AQ_TM_PROCESSES parameter has a value of zero, remove it.
Ensure that your streams_pool_size is configured with a minimum value of 50MB and 128MB would be better then retry the job.
SQL> SHOW PARAMETER streams_pool_size
TO SET THE PARAMETER streams_pool_size
SQL> ALTER SYSTEM SET streams_pool_size=128M
SHUT DOWN & RESTART THE DATABASE
Ensure that your streams_pool_size is configured with a minimum value of 50MB and 128MB would be better then retry the job.
SQL> SHOW PARAMETER streams_pool_size
TO SET THE PARAMETER streams_pool_size
SQL> ALTER SYSTEM SET streams_pool_size=128M
SHUT DOWN & RESTART THE DATABASE
Friday, January 22, 2016
EXPDP ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'
ALTER USER SYSTEM QUOTA unlimited ON SYSTEM;
ALTER USER SYSTEM QUOTA unlimited ON USR;
ALTER USER SYSTEM QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO SYSTEM;
ALTER USER LDBO QUOTA unlimited ON SYSTEM;
ALTER USER LDBO QUOTA unlimited ON USR;
ALTER USER LDBO QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO LDBO ;
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'
ALTER USER SYSTEM QUOTA unlimited ON SYSTEM;
ALTER USER SYSTEM QUOTA unlimited ON USR;
ALTER USER SYSTEM QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO SYSTEM;
ALTER USER LDBO QUOTA unlimited ON SYSTEM;
ALTER USER LDBO QUOTA unlimited ON USR;
ALTER USER LDBO QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO LDBO ;
Handler PageHandlerFactory-Integrated has a bad module ManagedPipelineHandler in its module list
HTTP Error 500.21 - Internal Server Error
Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i
Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i
ora-01536 space quota exceed
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
grant unlimited tablespace TO LDBO;
Select Tablespace_Name, Username, Bytes / 1024 / 1024/ 1024 "Used GB", Max_Bytes / 1024 / 1024/ 1024 As "Max GB" from dba_ts_quotas;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB",Increment_By from dba_data_files;
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile from dba_users where username='LDBO';
impdp hang statement suspended, wait error to be cleared
space issue or add more datafiles
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB" from dba_data_files;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB" from dba_temp_files
ORA-01652 unable to extend temp segment by 64 in tablespace TEMPORARY
ALTER TABLESPACE temporary ADD TEMPFILE 'E:\NBSD1314\TEMP02.ORA' SIZE 1000M AUTOEXTEND ON;
ALTER database TEMPFILE 'E:\NBSD1314\TEMP02.ORA' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
change file location as per client DB folder
alter tablespace
temporary
add tempfile
'D:\APXD1516\TEMP02.ORA'
size
1000m
autoextend on
next
100m
maxsize
unlimited;
ALTER database TEMPFILE 'E:\NBSD1314\TEMP02.ORA' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
change file location as per client DB folder
alter tablespace
temporary
add tempfile
'D:\APXD1516\TEMP02.ORA'
size
1000m
autoextend on
next
100m
maxsize
unlimited;
ORA-00600 internal error code, arguments [FILEjsks.c LINE2388 IDOCIKCallPush] ORA-28031 maximum of 148 enabled roles exceeded
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388 ID:OCIKCallPush] ORA-28031: maximum of 148 enabled roles exceeded
Error starting at line 1 in command:
begin
dbms_scheduler.run_job('JobImportClientMaster8');
end;
Error report:
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388 ID:OCIKCallPush], [], [], [], [], [], [], [], [], [], [], []
ORA-28031: maximum of 148 enabled roles exceeded
https://dbamohsin.wordpress.com/tag/ora-600/
SR 3-8664869401 : ORA 600-[FILE:jsks.c LINE:2388 FUNCTION:jsksStartOCICall() ID:OCIKCallPus]
Caused by Bug 8895202 : ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN
The solution was to set the following dynamic parameter on both the primary and standby database:
ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=BOTH;
This parameter is designed to heal blocks having invalid dependent scn’s on switchover operations.
From the traces provided to oracle, I was told that the affected object for this issue was ID 12152331.
Running the following SQL determines the object:
select owner,object_name,object_type,subobject_name,object_id,data_object_id
from dba_objects
where object_id in (12152331)
or data_object_id in (12152331);
The affected blocks were on index SYS.I_SCHEDULER_JOB_RUN_DETAILS on table SCHEDULER$_JOB_RUN_DETAILS.
As well as the dynamic parameter, I also did the following:
ANALYZE TABLE SCHEDULER$_JOB_RUN_DETAILS VALIDATE STRUCTURE online;
ALTER INDEX SYS.I_SCHEDULER_JOB_RUN_DETAILS REBUILD online;
Error starting at line 1 in command:
begin
dbms_scheduler.run_job('JobImportClientMaster8');
end;
Error report:
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388 ID:OCIKCallPush], [], [], [], [], [], [], [], [], [], [], []
ORA-28031: maximum of 148 enabled roles exceeded
https://dbamohsin.wordpress.com/tag/ora-600/
SR 3-8664869401 : ORA 600-[FILE:jsks.c LINE:2388 FUNCTION:jsksStartOCICall() ID:OCIKCallPus]
Caused by Bug 8895202 : ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN
The solution was to set the following dynamic parameter on both the primary and standby database:
ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=BOTH;
This parameter is designed to heal blocks having invalid dependent scn’s on switchover operations.
From the traces provided to oracle, I was told that the affected object for this issue was ID 12152331.
Running the following SQL determines the object:
select owner,object_name,object_type,subobject_name,object_id,data_object_id
from dba_objects
where object_id in (12152331)
or data_object_id in (12152331);
The affected blocks were on index SYS.I_SCHEDULER_JOB_RUN_DETAILS on table SCHEDULER$_JOB_RUN_DETAILS.
As well as the dynamic parameter, I also did the following:
ANALYZE TABLE SCHEDULER$_JOB_RUN_DETAILS VALIDATE STRUCTURE online;
ALTER INDEX SYS.I_SCHEDULER_JOB_RUN_DETAILS REBUILD online;
PLS-00565 STR_ARRAY must be completed as a potential REF target (object type)
create or replace TYPE "STR_ARRAY" is table of varchar2(3999);
/
ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 0, column 0:
PLS-00565: STR_ARRAY must be completed as a potential REF target (object type)
DROP TYPE "STR_ARRAY" ;
create or replace TYPE "STR_ARRAY" is table of varchar2(3999);
/
/
ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 0, column 0:
PLS-00565: STR_ARRAY must be completed as a potential REF target (object type)
DROP TYPE "STR_ARRAY" ;
create or replace TYPE "STR_ARRAY" is table of varchar2(3999);
/
ORA-01658
select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024;
select file_name,tablespace_name,bytes/1024/1024/1024 "Size GB",status,autoextensible,maxbytes/1024/1024/1024 "MaxSize GB",increment_by from dba_data_files;
select tablespace_name,bigfile from dba_tablespaces;
alter database default tablespace SYSTEM;
ALTER DATABASE DATAFILE 'E:\MCS0809\USERs01.ORA' AUTOEXTEND ON MAXSIZE UNLIMITED;
Hard disk space is not equal to the database space
tablespace is full, and then hang it a few more data files
Reply:
Hard disk space is sufficient enough table space does not mean
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024;
select file_name,tablespace_name,bytes/1024/1024/1024 "Size GB",status,autoextensible,maxbytes/1024/1024/1024 "MaxSize GB",increment_by from dba_data_files;
select tablespace_name,bigfile from dba_tablespaces;
alter database default tablespace SYSTEM;
ALTER DATABASE DATAFILE 'E:\MCS0809\USERs01.ORA' AUTOEXTEND ON MAXSIZE UNLIMITED;
Hard disk space is not equal to the database space
tablespace is full, and then hang it a few more data files
Reply:
Hard disk space is sufficient enough table space does not mean
ora-16038 ora-19502 ora-00312
ora-16038 ora-19502 ora-00312
ORA-16038: log 4 sequence# 10840 cannot be archived,ORA-19502: write error on file "", blockno (blocksize=,ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a',ORA-16014: log 4 seque
Just check the space in your archive log destination.
Make sure that it is not full.
shut immediate
startup mount
recover database until cancel;
alter database open resetlogs;
1. Increase the ARCHIVE DESTINATION size
2. Take backup of the archivelogs to different location.
3. Change archivelogs destination to some other mountpoint.
4. Delete archivelogs to make more space. ( should be the last option) and in case of standby database make sure those logs are already applied to standby.
ORA-16038: log 4 sequence# 10840 cannot be archived,ORA-19502: write error on file "", blockno (blocksize=,ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a',ORA-16014: log 4 seque
Just check the space in your archive log destination.
Make sure that it is not full.
shut immediate
startup mount
recover database until cancel;
alter database open resetlogs;
1. Increase the ARCHIVE DESTINATION size
2. Take backup of the archivelogs to different location.
3. Change archivelogs destination to some other mountpoint.
4. Delete archivelogs to make more space. ( should be the last option) and in case of standby database make sure those logs are already applied to standby.
ORA-00600 internal error code, arguments FILE jsks.c LINE 2388ID OCIKCallPush
SQL> exec DBMS_SCHEDULER.run_job ('ANALYZE_FULL');
BEGIN DBMS_SCHEDULER.run_job ('ANALYZE_FULL'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388ID:OCIKCallPush], [], [], [], [], [], [], [], [], [], [], []
ORA-28031: maximum of 148 enabled roles exceeded
ORA-28031: maximum of 148 enabled roles exceeded
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
spool c:\revokeldbo.sql
select 'REVOKE ' || GRANTED_ROLE || ' from sys;' from dba_role_privs where grantee='SYS' and granted_role not in ('CONNECT','DBA');
spool off
@c:\revokeldbo.sql
BEGIN DBMS_SCHEDULER.run_job ('ANALYZE_FULL'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388ID:OCIKCallPush], [], [], [], [], [], [], [], [], [], [], []
ORA-28031: maximum of 148 enabled roles exceeded
ORA-28031: maximum of 148 enabled roles exceeded
ORA-28031: maximum of 148 enabled roles exceeded
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
spool c:\revokeldbo.sql
select 'REVOKE ' || GRANTED_ROLE || ' from sys;' from dba_role_privs where grantee='SYS' and granted_role not in ('CONNECT','DBA');
spool off
@c:\revokeldbo.sql
utl_http.request ORA-12541 TNSno listener
select utl_http.request('http://172.168.1.7') from dual;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-12541: TNS:no listener
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
select utl_http.request('http://172.168.1.7:91') from dual;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-12541: TNS:no listener
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
select utl_http.request('http://172.168.1.7:91') from dual;
ORA-00600 [13011], [104], [4216981]
drop procedure ldbo.sp_mvmfrm;
drop materialized view ldbo.mv_rkmfrm;
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_ora_25880.trc (incident=2513464):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [13011], [104], [4216981], [185], [4216981], [17], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\incident\incdir_2513464\rakshak_ora_25880_i2513464.trc
Tue Jun 16 13:07:24 2015
Dumping diagnostic data in directory=[cdmp_20150616130724], requested by (instance=1, osid=25880), summary=[incident=2513464].
opiodr aborting process unknown ospid (25880) as a result of ORA-603
Tue Jun 16 13:07:27 2015
Sweep [inc][2513464]: completed
Sweep [inc2][2513464]: completed
Tue Jun 16 13:07:28 2015
SMON: Parallel transaction recovery tried
Tue Jun 16 13:07:30 2015
ORA-00600 [13011], [104], [4216981], [239], [4216981], [17], [], [], [], [], [], []
Tue Jun 16 11:24:47 2015
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_j002_27844.trc:
ORA-00600: internal error code, arguments: [13011], [104], [4216981], [239], [4216981], [17], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at "LDBO.SP_MVCLIENTFOLLOWUP", line 4
ORA-06512: at line 1
select object_name,object_type,owner from dba_objects where data_object_id=104;
analyze table DEPENDENCY$ validate structure cascade;
SELECT
ss.program "SOFTWARE",last_call_et,
substr(sqa.sql_text, 1, 50) "SQL"
,'alter system disconnect session ''' || ss.sid || ',' || ss.serial# || ''' immediate;'
fROM v$process pr, v$session ss, v$sqlarea sqa
WHERE pr.addr = ss.paddr
AND ss.username is not null
AND ss.sql_address = sqa.address(+)
AND ss.sql_hash_value = sqa.hash_value(+)
AND ss.status = 'ACTIVE'
ORDER BY last_call_et desc;
ANALYZE INDEX I_DEPENDENCY1 VALIDATE STRUCTURE;
ANALYZE INDEX I_DEPENDENCY2 VALIDATE STRUCTURE;
alter INDEX I_DEPENDENCY1 rebuild;
alter INDEX I_DEPENDENCY2 rebuild;
DROP INDEX "SYS"."I_DEPENDENCY1";
DROP INDEX "SYS"."I_DEPENDENCY2";
CREATE UNIQUE INDEX "SYS"."I_DEPENDENCY1" ON "SYS"."DEPENDENCY$" ("D_OBJ#", "D_TIMESTAMP", "ORDER#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 32768 NEXT 114688 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
CREATE INDEX "SYS"."I_DEPENDENCY2" ON "SYS"."DEPENDENCY$" ("P_OBJ#", "P_TIMESTAMP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 32768 NEXT 114688 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
SQL> DROP INDEX "SYS"."I_DEPENDENCY1";
DROP INDEX "SYS"."I_DEPENDENCY1"
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlGetCkyName1], [106], [], [],
[], [], [], [], [], [], [], []
SQL>
SQL> analyze table DEPENDENCY$ validate structure cascade;
analyze table DEPENDENCY$ validate structure cascade
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
drop materialized view ldbo.mv_rkmfrm;
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_ora_25880.trc (incident=2513464):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [13011], [104], [4216981], [185], [4216981], [17], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\incident\incdir_2513464\rakshak_ora_25880_i2513464.trc
Tue Jun 16 13:07:24 2015
Dumping diagnostic data in directory=[cdmp_20150616130724], requested by (instance=1, osid=25880), summary=[incident=2513464].
opiodr aborting process unknown ospid (25880) as a result of ORA-603
Tue Jun 16 13:07:27 2015
Sweep [inc][2513464]: completed
Sweep [inc2][2513464]: completed
Tue Jun 16 13:07:28 2015
SMON: Parallel transaction recovery tried
Tue Jun 16 13:07:30 2015
ORA-00600 [13011], [104], [4216981], [239], [4216981], [17], [], [], [], [], [], []
Tue Jun 16 11:24:47 2015
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_j002_27844.trc:
ORA-00600: internal error code, arguments: [13011], [104], [4216981], [239], [4216981], [17], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at "LDBO.SP_MVCLIENTFOLLOWUP", line 4
ORA-06512: at line 1
select object_name,object_type,owner from dba_objects where data_object_id=104;
analyze table DEPENDENCY$ validate structure cascade;
SELECT
ss.program "SOFTWARE",last_call_et,
substr(sqa.sql_text, 1, 50) "SQL"
,'alter system disconnect session ''' || ss.sid || ',' || ss.serial# || ''' immediate;'
fROM v$process pr, v$session ss, v$sqlarea sqa
WHERE pr.addr = ss.paddr
AND ss.username is not null
AND ss.sql_address = sqa.address(+)
AND ss.sql_hash_value = sqa.hash_value(+)
AND ss.status = 'ACTIVE'
ORDER BY last_call_et desc;
ANALYZE INDEX I_DEPENDENCY1 VALIDATE STRUCTURE;
ANALYZE INDEX I_DEPENDENCY2 VALIDATE STRUCTURE;
alter INDEX I_DEPENDENCY1 rebuild;
alter INDEX I_DEPENDENCY2 rebuild;
DROP INDEX "SYS"."I_DEPENDENCY1";
DROP INDEX "SYS"."I_DEPENDENCY2";
CREATE UNIQUE INDEX "SYS"."I_DEPENDENCY1" ON "SYS"."DEPENDENCY$" ("D_OBJ#", "D_TIMESTAMP", "ORDER#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 32768 NEXT 114688 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
CREATE INDEX "SYS"."I_DEPENDENCY2" ON "SYS"."DEPENDENCY$" ("P_OBJ#", "P_TIMESTAMP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 32768 NEXT 114688 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
SQL> DROP INDEX "SYS"."I_DEPENDENCY1";
DROP INDEX "SYS"."I_DEPENDENCY1"
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkdlGetCkyName1], [106], [], [],
[], [], [], [], [], [], [], []
SQL>
SQL> analyze table DEPENDENCY$ validate structure cascade;
analyze table DEPENDENCY$ validate structure cascade
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
ORA-00064 object is too large to allocate on this OS (1,10085160)
process should be less 500
create pfile from spfile;
rename spfile
open pfile and change processes to 200
and restart services
create pfile from spfile;
rename spfile
open pfile and change processes to 200
and restart services
Oracle Installation Issue Please ensure that this directory is writable and has at least 45MB of disk space
Virus
AntiVirus
Rights
Space in temp
Cross verify the registry using 'regedit' and check it doesn't have any unclean services of previos oracle installation (if any).
a) Insufficient free space in the temporary directory.
b) Insufficient access rights on the temporary directory.
c) Not using a local console.
d) Corrupted software set.
e) Wrong Unzip utility is used.
For Database: %Oracle_extracted_media%\database\install\oraparam.ini
Edit value: BOOTSTRAP=TRUE
New value: BOOTSTRAP=FALSE << this allows OUI creates less files /tmp and avoids creation of autorun.inf file @ %tmp%
2) Then run the setup.exe -debug from the media (where the file praparam.ini edited ).
Example: run setup.exe -debug from required media location
cd %Oracle_extracted_media%\database\setup.exe
create autorun.inf file in temp folder, try to save this
unload officescan
PLS-00920 parameter plsql_native_library_dir is not set
sho parameter plsql
plsql_code_type string INTERPRETED
plsql_compiler_flags string INTERPRETED, NON_DEBUG
10g
alter system set plsql_code_type ='INTERPRETED';
plsql_code_type string INTERPRETED
plsql_compiler_flags string INTERPRETED, NON_DEBUG
10g
alter system set plsql_code_type ='INTERPRETED';
The specified DSN contains an architecture mismatch between the Driver and Application
64 bit m/c
Reason
odbc at both location
c:\windows\system32\odbcad32.exe
c:\windows\sysWOW64\odbcad32.exe
remove odbc connection from c:\windows\system32\odbcad32.exe
You will get this error if you attempt to do the following on a 64-bit Windows machine:
Connect a 64-bit application to a data source for a 32-bit ODBC driver.
Connect a 32-bit application to a data source for a 64-bit ODBC driver.
If you are using a 64-bit application, it will be linked against the 64-bit version of the Microsoft ODBC Driver Manager, which can only load a 64-bit ODBC driver. You can only connect to data sources for ODBC drivers that are listed in the 64-bit ODBC Data Source Administrator (accessible from Administrative tools in Control Panel).
Similarly, if you are using a 32-bit application, it will be linked against the 32-bit version of the Microsoft ODBC Driver Manager, which can only load a 32-bit ODBC driver. You can only connect to data sources for ODBC drivers that are listed in the 32-bit ODBC Data Source Administrator (accessible by running %windir%\syswow64\odbcad32.exe in the Windows Run dialog box).
If an ODBC driver is listed in both versions of the ODBC Data Source Administrator, both a 32-bit and a 64-bit version of that driver are installed.
Reason
odbc at both location
c:\windows\system32\odbcad32.exe
c:\windows\sysWOW64\odbcad32.exe
remove odbc connection from c:\windows\system32\odbcad32.exe
You will get this error if you attempt to do the following on a 64-bit Windows machine:
Connect a 64-bit application to a data source for a 32-bit ODBC driver.
Connect a 32-bit application to a data source for a 64-bit ODBC driver.
If you are using a 64-bit application, it will be linked against the 64-bit version of the Microsoft ODBC Driver Manager, which can only load a 64-bit ODBC driver. You can only connect to data sources for ODBC drivers that are listed in the 64-bit ODBC Data Source Administrator (accessible from Administrative tools in Control Panel).
Similarly, if you are using a 32-bit application, it will be linked against the 32-bit version of the Microsoft ODBC Driver Manager, which can only load a 32-bit ODBC driver. You can only connect to data sources for ODBC drivers that are listed in the 32-bit ODBC Data Source Administrator (accessible by running %windir%\syswow64\odbcad32.exe in the Windows Run dialog box).
If an ODBC driver is listed in both versions of the ODBC Data Source Administrator, both a 32-bit and a 64-bit version of that driver are installed.
ORA-00344 unable to re-create online log ORA-27040 file create error unable to create file
http://blog.dbvisit.com/activating-standby-database-failover/
ORA-00344 unable to re-create online log ORA-27040 file create error unable to create file
SQL> alter database rename file '/oracle/oradata/dbvisitp/redo01.log'
to '/oracleX/oradata/dbvisitp/redo01.log';
Database altered.
SQL> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/oracleX/oradata/dbvisitp/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
The database is still a standby database as the activation failed.
We cannot manually create the redo logs, as this is an Oracle internal operation.
The only thing we can do is to give the correct location for the redo log files.
Rename the redo log to the correct location:
SQL> alter database rename file '/oracleX/oradata/dbvisitp/redo01.log' to
'/oracle/oradata/dbvisitp/redo01.log';
Database altered.
SQL> alter database activate standby database;
Database altered.
SQL>
Note: all previous backups of this database are now invalid and cannot be used to restore this database. This is because of the RESETLOGS command which resets the archive sequence number (SEQUENCE#) and invalidates all previous archive logs. The SCN number of the database is not reset.
ORA-00344 unable to re-create online log ORA-27040 file create error unable to create file
SQL> alter database rename file '/oracle/oradata/dbvisitp/redo01.log'
to '/oracleX/oradata/dbvisitp/redo01.log';
Database altered.
SQL> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/oracleX/oradata/dbvisitp/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
The database is still a standby database as the activation failed.
We cannot manually create the redo logs, as this is an Oracle internal operation.
The only thing we can do is to give the correct location for the redo log files.
Rename the redo log to the correct location:
SQL> alter database rename file '/oracleX/oradata/dbvisitp/redo01.log' to
'/oracle/oradata/dbvisitp/redo01.log';
Database altered.
SQL> alter database activate standby database;
Database altered.
SQL>
Note: all previous backups of this database are now invalid and cannot be used to restore this database. This is because of the RESETLOGS command which resets the archive sequence number (SEQUENCE#) and invalidates all previous archive logs. The SCN number of the database is not reset.
Could not load file or assembly 'ChilkatDotNet4' or one of its dependencies. An attempt was made to load a program with an incorrect format.
Common Error #1
The Chilkat .NET assembly is a mixed-mode assembly. It provides a managed API, but the inner core contains native code. A process must load the correct assembly at runtime. A 32-bit process must load the 32-bit ChilkatDotNet4.dll, and a 64-bit process must load the 64-bit ChilkatDotNet4.dll. When a process tries to load the incorrect format, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4' or one of its dependencies. An attempt was made to load a program with an incorrect format.
See Incorrect Format for more information.
check application pool (32 bit enable or not)
check ChilkatDotNet4 version 9.3.1
Microsoft Visual C++ 2010 SP1 Redistributable 32 bit / 64 bit
Microsoft Visual C++ 2012 SP1 Redistributable 32 bit/ 64 bit
The Chilkat .NET assembly is a mixed-mode assembly. It provides a managed API, but the inner core contains native code. A process must load the correct assembly at runtime. A 32-bit process must load the 32-bit ChilkatDotNet4.dll, and a 64-bit process must load the 64-bit ChilkatDotNet4.dll. When a process tries to load the incorrect format, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4' or one of its dependencies. An attempt was made to load a program with an incorrect format.
See Incorrect Format for more information.
check application pool (32 bit enable or not)
check ChilkatDotNet4 version 9.3.1
Microsoft Visual C++ 2010 SP1 Redistributable 32 bit / 64 bit
Microsoft Visual C++ 2012 SP1 Redistributable 32 bit/ 64 bit
Could not load file or assembly 'ChilkatDotNet4.dll' or one of its dependencies. The specified module could not be found.
install
vcredist_x64
vcredist_x86
vcredist_x64_2010
vcredist_x86_2010
vcredist_x64_2012
vcredist_x86_2012
Common Error #2
The Chilkat .NET assembly requires the VC++ runtime to be installed on any computer where your application runs. Most computers will already have it installed. Your development computer will have it because Visual Studio has been installed. However, if deploying to a computer where the VC++ runtime is not available, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4.dll' or one of its dependencies. The specified module could not be found.
http://www.microsoft.com/en-us/download/details.aspx?id=5555
Common Error #1
The Chilkat .NET assembly is a mixed-mode assembly. It provides a managed API, but the inner core contains native code. A process must load the correct assembly at runtime. A 32-bit process must load the 32-bit ChilkatDotNet4.dll, and a 64-bit process must load the 64-bit ChilkatDotNet4.dll. When a process tries to load the incorrect format, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4' or one of its dependencies. An attempt was made to load a program with an incorrect format.
See Incorrect Format for more information.
vcredist_x64
vcredist_x86
vcredist_x64_2010
vcredist_x86_2010
vcredist_x64_2012
vcredist_x86_2012
Common Error #2
The Chilkat .NET assembly requires the VC++ runtime to be installed on any computer where your application runs. Most computers will already have it installed. Your development computer will have it because Visual Studio has been installed. However, if deploying to a computer where the VC++ runtime is not available, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4.dll' or one of its dependencies. The specified module could not be found.
http://www.microsoft.com/en-us/download/details.aspx?id=5555
Common Error #1
The Chilkat .NET assembly is a mixed-mode assembly. It provides a managed API, but the inner core contains native code. A process must load the correct assembly at runtime. A 32-bit process must load the 32-bit ChilkatDotNet4.dll, and a 64-bit process must load the 64-bit ChilkatDotNet4.dll. When a process tries to load the incorrect format, the following error will occur:
Could not load file or assembly 'ChilkatDotNet4' or one of its dependencies. An attempt was made to load a program with an incorrect format.
See Incorrect Format for more information.
PLS-00123 program too large (Diana nodes)
in 10g, plsql is not taking such size.
better way to use large procedures and functions should always be defined within packages
microsoft excel cannot open or save any more documents because there is not enough available memory
at Control Panel \ All Control Panel Items \ Default Programs \ Set Default Programs \ Set Program Associations
In Excel, go to File/Options/Trust Center/Trust Center Settings/Protected View. Untick the "Enable Protected View for Outlook Attachments" - and all will be well.
no mapping between account name and security ids was done server 2008 service installation
grant rights of folder
If you do choose the SYSPREP route though here is what you need to do:
From the Start Menu select Run
Enter C:\Windows\System32\sysprep\sysprep.exe in the box and click OK
Be sure that Enter System Out-of-Box Experience (OOBE) is selected
Check the box next to Generalize (If this is not select the SID won’t get changed)
Click OK and follow the prompts when the system reboots.
C:\Windows\System32\sysprep\sysprep.exe
If you do choose the SYSPREP route though here is what you need to do:
From the Start Menu select Run
Enter C:\Windows\System32\sysprep\sysprep.exe in the box and click OK
Be sure that Enter System Out-of-Box Experience (OOBE) is selected
Check the box next to Generalize (If this is not select the SID won’t get changed)
Click OK and follow the prompts when the system reboots.
C:\Windows\System32\sysprep\sysprep.exe
Silverlight out of browser export to excel download issue require Administrator to create the file
at time of package build, in properties there is a tick for out of browser
LD Ledger Client Code not search
Insert into tblsaudatable select distinct Firmnumber,Oowncode,'C','N' from sauda minus
Select distinct cFirmnumber,cclientcode,'C','N' from tblsaudatable Where cSegment='C' ;
Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'C' from sauda minus
Select distinct cFirmnumber,Cclientcode,cscripcode,'C' from Tblsaudascriptable Where cSegment='C' ;
Insert into tblsaudatable select distinct Firmnumber,Oowncode,'D','N' from fosauda minus
Select distinct cFirmnumber,CClientcode,'D','N' from tblsaudatable Where cSegment='D' ;
Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'D' from fosauda minus
Select distinct cFirmnumber,Cclientcode,Cscripcode,'D' from Tblsaudascriptable Where cSegment='D' ;
Insert into Tblfintable select distinct firmnumber,oowncode,branchcode,'N' from ledger minus
select distinct cfirmnumber,Cclientcode,Cbranchcode,'N' from Tblfintable ;
Select distinct cFirmnumber,cclientcode,'C','N' from tblsaudatable Where cSegment='C' ;
Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'C' from sauda minus
Select distinct cFirmnumber,Cclientcode,cscripcode,'C' from Tblsaudascriptable Where cSegment='C' ;
Insert into tblsaudatable select distinct Firmnumber,Oowncode,'D','N' from fosauda minus
Select distinct cFirmnumber,CClientcode,'D','N' from tblsaudatable Where cSegment='D' ;
Insert into Tblsaudascriptable select distinct Firmnumber,Oowncode,Compcode,'D' from fosauda minus
Select distinct cFirmnumber,Cclientcode,Cscripcode,'D' from Tblsaudascriptable Where cSegment='D' ;
Insert into Tblfintable select distinct firmnumber,oowncode,branchcode,'N' from ledger minus
select distinct cfirmnumber,Cclientcode,Cbranchcode,'N' from Tblfintable ;
ORA-29861 domain index is marked LOADINGFAILEDUNUSABLE
Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','DPCDSL',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal => 'DPCDSL',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_http_access.xml', '*',NULL,NULL);
Commit ;
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 252
ORA-06512: at line 1
select owner,index_name from all_indexes where domidx_status != 'VALID' or domidx_opstatus !='VALID';
alter index xdb.XDB$ACL_XIDX rebuild;
"ORA-28547: Connection to Server Failed, Probable Oracle Net Admin Error"
Edit the SQLNET.ora file in a text editor such as Notepad.
Change this line to what is shown below:
#SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.AUTHENTICATION_SERVICES = (NONE)
Check Path at Env Var
Listener rights issue
Restart Listener
Standby Database not automatically startup issue
What is the value of REMOTE_LOGIN_PASSWORDFILE parameter?
Please change the owner of the Oracle process
Start->Settings->Control Panel->Services
Locate and highlight the "OracleServiceSID"
Click on the "Startup" button
In the "Log On As", choose "This Account"
Use the "..." button to browse to the "OracleAdmin" or Administrator user
Choose in "List Names From" your host
Provide the password of this user and confirm it
Repeat for the "OracleStartSID", "OracleTNSListener" and any
other Oracleservice you are using.
Be sure that the service startup is "Automatic"
ORA_<SID>_AUTOSTART =
ORA_<SID>_PFILE =
ORA_<SID>_SHUTDOWN =
ORA_<SID>_SHUTDOWNTYPE =
ORA_<SID>_SHUTDOWN_TIMEOUT =
Research:
=========
WIN: Automatic Startup of the Database when Using O/S Authentication ( Note 116979.1 )
How to configure Database Control to Start Automatically on Server Reboot / Shutdown Note 1282530.1
Windows Service Not Starting Automatically At Server reboot ( Doc ID 1264404.1 )
LD Login Error Table Definitions have undergone a change
select table_name from dba_tables where owner='LDBO' and temporary = 'Y' and table_name='TBLTEMPOPERATIONSTATISTICS';
DRop TABLE "LDBO"."TBLTEMPOPERATIONSTATISTICS" ;
CREATE GLOBAL TEMPORARY TABLE "LDBO"."TBLTEMPOPERATIONSTATISTICS"
( "NOPERCODE" NUMBER(3,0),
"NSUBCODE" NUMBER(3,0),
"COPERATIONNAME" VARCHAR2(100 BYTE),
"CSTARTTIME" DATE,
"CENDTIME" DATE,
"NTOTALROWS" NUMBER(12,0),
"NINITIALROWS" NUMBER(12,0),
"NFETCHROWS" NUMBER(12,0)
) ON COMMIT PRESERVE ROWS ;
ORA-00322 ORA-00312
ORA-00322 ORA-00312
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_ora_10232.trc:
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'E:\RKDATABASE\REDO03.LOG'
recover database using backup controlfile;
E:\RKDATABASE\REDO03.LOG
ALTER DATABASE OPEN RESETLOGS;
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\rakshak\rakshak\trace\rakshak_ora_10232.trc:
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'E:\RKDATABASE\REDO03.LOG'
recover database using backup controlfile;
E:\RKDATABASE\REDO03.LOG
ALTER DATABASE OPEN RESETLOGS;
EXPDP Privileges ORA-31631 ORA-39161
create user expdpuser identified by expdpuser default tablespace usr temporary tablespace temporary;
grant connect, resource, exp_full_database,IMP_FULL_DATABASE to expdpuser;
grant all on directory to expdpuser;
grant connect, resource, exp_full_database to ldbo;
ORA-00980: synonym translation is no longer valid
select * from dba_synonyms s
where table_owner not in('SYSTEM','SYS')
and db_link is null
and not exists
(select 1
from dba_objects o
where s.table_owner=o.owner
and s.table_name=o.object_name);
ORA-01172 ORA-01151
ORA-01172: recovery of thread 1 stuck at block 45 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
shut immediate
startup mount
recover database;
alter database open;
ORA-01151: use media recovery to recover block, restore backup if needed
shut immediate
startup mount
recover database;
alter database open;
Please Check Connection Or Data is huge
There is some issue in one of the services so browse all services one by one
1) some one restart IIS at time of report generation
2) Less memory at server
if there is occur only particular machine then check the same another browser like firefox
LD ..Addon..API....Login Issue..... Unable to fetch IP Address
$-------------------------------------- Begin ---------------------------------------------$
Client code :
Title : INIT CONNECTION
Message :
StackTrace : 01~Unable to Fetch IP Address.
Computer : ANIL-PC
Processor : 4
OS Version : Microsoft Windows NT 6.1.7600.0
Memory : 168865792
Shut down : N
Username : DefaultAppPool
Date/Time : 11/9/2015 3:09:40 PM
Connection : Closed
===========================================================================================
Client code :
Title : INIT CONNECTION
Message :
StackTrace : 01~Unable to Fetch IP Address.
Computer : ANIL-PC
Processor : 4
OS Version : Microsoft Windows NT 6.1.7600.0
Memory : 168865792
Shut down : N
Username : DefaultAppPool
Date/Time : 11/9/2015 3:09:40 PM
Connection : Closed
===========================================================================================
New Splogin
SELECT UTL_INADDR.get_host_address from dual;
host file
SELECT TERMINAL FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID') AND AUDSID != 0 AND ROWNUM = 1;
SELECT PROGRAM,MODULE FROM SYS.V_$SESSION
WHERE AUDSID = USERENV('SESSIONID')
AND AUDSID != 0
AND ROWNUM = 1
SELECT TBLTEMPOPERATIONSTATISTICS.COPERATIONNAME LCIPADDRESS FROM TBLTEMPOPERATIONSTATISTICS TBLTEMPOPERATIONSTATISTICS WHERE TBLTEMPOPERATIONSTATISTICS.NOPERCODE=-1 ;
LDADDON users are not visible in Menu Level Security
select d.user_id , d.username , d.password , d.profile , d.account_status , d.account_status from dba_users d where d.default_tablespace='USR' order by d.user_id ;
alter user <username> default tablespace USR;
ORA-06502 PLSQL numeric or value error character to number conversion error ORA-06512 at LDBO.SP_SERSEQ
SELECT voucher FROM LEDGER WHERE FIRMNUMBER='APXC-00001' and voucher like '%JV%' and Regexp_Like((SUBSTR(VOUCHER,-7,7)), '[[:alpha:]]');
SELECT MAX(SUBSTR(VOUCHER,-7,7)) FROM LEDGER WHERE FIRMNUMBER='APXC-00001' and voucher like '%JV%' and Regexp_Like((SUBSTR(VOUCHER,-7,7)), '[[:alpha:]]');
result should be numeric
SELECT MAX(SUBSTR(VOUCHER,-7,7)) FROM LEDGER WHERE FIRMNUMBER='APXC-00001' and voucher like '%JV%' and Regexp_Like((SUBSTR(VOUCHER,-7,7)), '[[:alpha:]]');
result should be numeric
ora-14450 ora-14452 global temporary table attempt to access a transactional temp table already in use
SELECT
distinct
ss.program "SOFTWARE",
SS.TERMINAL,
ss.username "USER",
'alter system disconnect session ''' || ss.sid || ',' || ss.serial# || ',@' || ss.inst_id || ''' immediate;'
fROM gv$process pr, gv$session ss, gv$sqlarea sqa,
(select sid
from gv$lock
where id1 = (
select object_id
from dba_objects
where owner = 'LDBO'
and object_name = 'TBLTEMPTRADESTATUS'))tbllock
WHERE pr.addr = ss.paddr
AND ss.username is not null
AND ss.sql_address = sqa.address(+)
AND ss.sql_hash_value = sqa.hash_value(+)
and ss.inst_id=pr.inst_id
and ss.sid = tbllock.sid
;
distinct
ss.program "SOFTWARE",
SS.TERMINAL,
ss.username "USER",
'alter system disconnect session ''' || ss.sid || ',' || ss.serial# || ',@' || ss.inst_id || ''' immediate;'
fROM gv$process pr, gv$session ss, gv$sqlarea sqa,
(select sid
from gv$lock
where id1 = (
select object_id
from dba_objects
where owner = 'LDBO'
and object_name = 'TBLTEMPTRADESTATUS'))tbllock
WHERE pr.addr = ss.paddr
AND ss.username is not null
AND ss.sql_address = sqa.address(+)
AND ss.sql_hash_value = sqa.hash_value(+)
and ss.inst_id=pr.inst_id
and ss.sid = tbllock.sid
;
ORA-00904 invalid identifier
column is missing
but column is there
query is run
but in procedure it gives error
Solution
some one add column from plsql
may be case issue
so drop and recreate column
but column is there
query is run
but in procedure it gives error
Solution
some one add column from plsql
may be case issue
so drop and recreate column
ORA-29913 ORA-29400 KUP-04080
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object XMLDIR not found
cllvl@
select * from ldbo.TBLFTXTERNTEMP_DEALER
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object XMLDIR not found
grant read, write on directory xmldir to cllvl;
ORA-29400: data cartridge error
KUP-04080: directory object XMLDIR not found
cllvl@
select * from ldbo.TBLFTXTERNTEMP_DEALER
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object XMLDIR not found
grant read, write on directory xmldir to cllvl;
Error while trying to retrieve text for error ORA-01019
Error while trying to retrieve text for error ORA-01019
VFP connection issue
check oracle path in Env Var
remove 11g path keep 10g path in starting
VFP connection issue
check oracle path in Env Var
remove 11g path keep 10g path in starting
tns-03505 failed to resolve name
remove entry from sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES= (NONE)
#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
HTTP Error 404.3 - Not Found
enter "cd c:\windows\Microsoft.Net\Framework\v3.0\Windows Communication Foundation\" and press Enter.
Enter "ServiceModelReg -i" and press Enter.
WCF will now be installed:
Enter "ServiceModelReg -i" and press Enter.
WCF will now be installed:
ORA-02022 remote statement has unoptimized view with remote object sqlserver merge command issue
This kind of statement is not supported by the Database Gateway.
Please review the Gateway documentation (http://docs.oracle.com/database/121/GMSWN/ch3.htm#GMSWN200) :
----------------------------------------------------------------------------------------------------------------
Callback Support
SQL statements that require the gateway to callback to Oracle database would not be supported.
The following categories of SQL statements will result in a callback:
Any DML with a sub-select, which refers to a table in Oracle database. For example:
INSERT INTO emp@non_oracle SELECT * FROM oracle_emp;
ORA-02022 remote statement has unoptimized view with remote object
following is not supported in oracle
insert into tblDematDetails@Lnk_Datum (Oowncode) select Clientocode from demat where vallan=2015062 and compcode=17140
;
Please review the Gateway documentation (http://docs.oracle.com/database/121/GMSWN/ch3.htm#GMSWN200) :
----------------------------------------------------------------------------------------------------------------
Callback Support
SQL statements that require the gateway to callback to Oracle database would not be supported.
The following categories of SQL statements will result in a callback:
Any DML with a sub-select, which refers to a table in Oracle database. For example:
INSERT INTO emp@non_oracle SELECT * FROM oracle_emp;
ORA-02022 remote statement has unoptimized view with remote object
following is not supported in oracle
insert into tblDematDetails@Lnk_Datum (Oowncode) select Clientocode from demat where vallan=2015062 and compcode=17140
;
oracle sql server datetime overflow
use to_char
select to_char(Markethistory.Dtoftran,'DD-MM-YYYY') from Markethistory;
ORA-02070 error insert data into SQL Server
insert into vc@odbc ("c1") values (current_timestamp);
ORA-02070: database ODBC does not support operator 293 in this context
insert into vc@odbc ("c1") values (sysdate);
ORA-02070: database ODBC does not support special functions in this context
We worked around this issue in the following way:
We created this SQL Server table:
create table vc ( c0 int IDENTITY(1,1) NOT NULL, c1 datetime, primary key (c0))
Next, we tried to run one of the INSERT statements shown above but got the ORA-02070 error. To work around this, we passed the contents of the function into a data store and then passed the data store to the INSERT statement. For example:
DECLARE
d1 date;
BEGIN
select sysdate into d1 from dual;
INSERT INTO vc@odbc ("c1") values (d1);
END;/
PL/SQL procedure successfully completed.
SQL> select * from vc@odbc;
c0 c1
---------- ---------
1 03-MAR-14
ORA-06508 PLSQL could not find program unit being called
select object_name from user_objects where object_type='PACKAGE'
MINUS
select object_name from user_objects where object_type='PACKAGE BODY';
MINUS
select object_name from user_objects where object_type='PACKAGE BODY';
ora-01502 index unusable state
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';
Saturday, July 27, 2013
Grant role access to all objects in a schema to another user
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('PROCEDURE','PACKAGE','FUNCTION') )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('TABLE') )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('SEQUENCE') )
LOOP
EXECUTE IMMEDIATE 'SELECT ON '||i.object_name ||' TO kshitij';
END LOOP;
END;
/
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('PROCEDURE','PACKAGE','FUNCTION') )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('TABLE') )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||i.object_name||' TO kshitij';
END LOOP;
END;
/
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type in ('SEQUENCE') )
LOOP
EXECUTE IMMEDIATE 'SELECT ON '||i.object_name ||' TO kshitij';
END LOOP;
END;
/
Sessions Hang On Wait Event WAIT FOR EMON PROCESS NTFNS ID 1287435.1
Sessions Hang On Wait Event "WAIT FOR EMON PROCESS NTFNS" [ID 1287435.1]
Symptoms
RDBMS 10.2.0.4.0
OCI based DB change notification
EMON spin with high CPU usage
EMN0 locks affected multiple applications for a few hours.
Performance suffers until DBCN Database Change Notification is disabled.
Cause
Bug 7282408: EMN STUCK - OTHER SESSIONS HANG ON "WAIT FOR EMON TO PROCESS NTFNS"
Filename = awr_report_29315_29317.html
See the following error:
In Top 5 Timed Events, "wait for EMON to process ntfns " wait event is listing
Matches Bug 7282408 filed against 10.2.0.4.0 and fix is included in 11.1.0.6.0 RDBMS
WORKAROUND:
SQLNET.SEND_TIMEOUT=3
Values are in seconds. However the send timeout is a tunable parameter and there is no standard value which has been prescribed. The ideal value for this parameter would be such that this does not affect the normal working clients.
Solution
1) Set SQLNET.SEND_TIMEOUT=10
It was found that after the SEND_TIMEOUT parameter was adjusted and resolved the EMN process to stop spinning and de-register the subscribers; if customer tried to re-register, the subscribers were removed as soon as new changes took place and could not re-register successfully until the EMN process was killed.
Continued problem resolution found that unpublished Bug 6750296 'Incorrect handling of Connection Failures' was needed to address the incorrect handling of connection failure issues.
Symptoms
RDBMS 10.2.0.4.0
OCI based DB change notification
EMON spin with high CPU usage
EMN0 locks affected multiple applications for a few hours.
Performance suffers until DBCN Database Change Notification is disabled.
Cause
Bug 7282408: EMN STUCK - OTHER SESSIONS HANG ON "WAIT FOR EMON TO PROCESS NTFNS"
Filename = awr_report_29315_29317.html
See the following error:
In Top 5 Timed Events, "wait for EMON to process ntfns " wait event is listing
Matches Bug 7282408 filed against 10.2.0.4.0 and fix is included in 11.1.0.6.0 RDBMS
WORKAROUND:
SQLNET.SEND_TIMEOUT=3
Values are in seconds. However the send timeout is a tunable parameter and there is no standard value which has been prescribed. The ideal value for this parameter would be such that this does not affect the normal working clients.
Solution
1) Set SQLNET.SEND_TIMEOUT=10
It was found that after the SEND_TIMEOUT parameter was adjusted and resolved the EMN process to stop spinning and de-register the subscribers; if customer tried to re-register, the subscribers were removed as soon as new changes took place and could not re-register successfully until the EMN process was killed.
Continued problem resolution found that unpublished Bug 6750296 'Incorrect handling of Connection Failures' was needed to address the incorrect handling of connection failure issues.
Bug 8605337 - Deadlock between gather stats job and concurrent DDL [ID 8605337.8]
Bug 8605337 Deadlock between gather stats job and concurrent DDL
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
11.2.0.1
11.1.0.7
11.1.0.6
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
Symptoms:
Related To:
Deadlock
ORA-60
Optimizer
PL/SQL (DBMS Packages)
DBMS_STATS
Description
ORA-60 can occur when gathering stats and some other concurrent DDL
is happening on the same table(s).
The deadlocking sessions will wait on following wait events:
- 'enq: TX - row lock contention' on tab$ table
- 'library cache lock' on a sys owned object
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
11.2.0.1
11.1.0.7
11.1.0.6
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
Symptoms:
Related To:
Deadlock
ORA-60
Optimizer
PL/SQL (DBMS Packages)
DBMS_STATS
Description
ORA-60 can occur when gathering stats and some other concurrent DDL
is happening on the same table(s).
The deadlocking sessions will wait on following wait events:
- 'enq: TX - row lock contention' on tab$ table
- 'library cache lock' on a sys owned object
ORA-00439 feature not enabled Deferred Segment Creation 11g enterprise to standard edition downgrade
1293326.1
http://lifeandoracle.blogspot.in/2012/07/ora-00439-feature-not-enabled-deferred.html
http://ocpdba.wordpress.com/2009/10/09/deferred-segment-creation-segmentless-tables-11gr2-new-feature-and-dumb-questions/
http://asanga-pradeep.blogspot.in/2011/01/segment-creation-behavior-change.html
http://www.donotcommit.net/?p=51
------------------
1. Init.ora Parameter "DEFERRED_SEGMENT_CREATION" Reference Note [ID 1216282.1]
2. IMP-00003, ORA-00959 While Importing Data Into Existing Table Of 11gR2 Using Traditional Import [ID 1180873.1]
3. Bug 8795792 - DBMS_METADATA.get_ddl generates wrong keywords for compressed indexes in 11.2 [ID 8795792.8]
4. Exporting Schema In 11.2 Database With 10g Client Gives ORA-1455 [ID 1083330.1]
1293326.1
-----------------
then you must know you have just hit one of the new features of 11gR2. Unfortunately this feature makes the life of the casual DBA a little bit more complicated than necessary.
I found an interesting debate about whether this must be considered a feature or a bug in the OTN forums but I very much appreciated the last comment where Michiel provided a working solution without delving into philosophical matters. I remember doing the same thing when dealing with another EXPDP/IMPDP problem between versions 11.1.0.6 and 11.1.0.7.
---------------------------Solution 1--------------
-- on the source instance
EXPDP ldbo/ldbo@apx1314srv full=Y dumpfile=expdp1314.dmp directory=DATA_DUMP_DIR logfile=expdp1314.log version=10.2
-- on the target instance
IMPDP ldbo/ldbo@apx1314srv full=Y dumpfile=expdp1314.dmp directory=DATA_DUMP_DIR logfile=expdp1314.log version=10.2
---------------------------Solution 2--------------
For 11.2.0.1 one way to avoid the issue is to allocate segment manually before the export. This could be done with
alter table allocate extent;
select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';
set autocommit on;
set line 124;
select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';
---------------Solution 3--------
SQL> alter system set deferred_segment_creation=false;
===================
From 11g Release 2 all tables created without rows, do not create any segments (by default). If you want to change this behaviour, tweak the spfile parameter “deferred_segment_creation” to FALSE. Anyway the people installing Peoplesoft, SAP, Siebel and other thousand-table data models, really do thank for this new feature.
How does it work?
If the INITIAL extent of a table is 15 MegaBytes in size, but the table is empty, the table won’t ask for that 15 MB of space. As soon as the first row of data is inserted (even a few bytes), the Oracle space engine will run the storage space settings of the table, before inserting that line, making the table actually owner of that space. You can create a 100GB table onto a 100MB tablespace as long as the table is empty, because the space will only be reclaimed by the table when it receives the first row.
========================
In 11.2 the initialization parameter DEFERRED_SEGMENT_CREATION controls whether segment space is allocated at the time of the segment creation. This is set to true by default both on enterprise edition and standard edition. But on standard edition this parameter has no effect as this feature is not enabled. Initialization parameter behavior could be override at table with
SQL> CREATE TABLE x(a number) SEGMENT CREATION IMMEDIATE;
or
SQL> CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
But on standard edition this would still give an error.
SQL> CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
CREATE TABLE x(a number) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-00439: feature not enabled: Deferred Segment Creation
Problem happens when exporting some empty tables from an enterprise edition database to a standard edition database. According to the default behavior on enterprise edition when tables have no rows there won't be any segments. But default behavior on standard edition is that, tables are always created with segments.
In 11.2.0.1 when importing tables from enterprise to standard edition tables with no segments throws an error while in 11.2.0.2 tables get created with segments without an error.
========================
Oracle 11g2 does NOT export empty tables when using exp.exe
http://tsells.wordpress.com/2011/02/10/oracle-11g2-does-not-export-empty-tables-when-using-exp-exe/
Out of habit I have been using the older data import / export functions in lieu of using the Data Pump commands that Oracle recommends. As of 11g2 I can no longer do this. The exp.exe no longer exports tables that do not have an entry in DBA_SEGMENTS. This is due to a new feature named “deferred segment creation”.
The syntax for the new Data Pump commands are different and require some additional setup / maneuvering on the Oracle Server itself to complete. The files when importing now must be placed in the Data Pump Directory. This can be found by running the following query against the database server.
select owner, directory_name, directory_path from dba_directories
================
The cause is a new feature of Oracle 11.2: deferred segment creation.
All newly created tables that has no rows in it (ever!), has no corresponding row in DBA_SEGMENTS, so somewhy won't get exported with (deprecated) exp utility. Use expdp instead.
================
select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';
set autocommit on;
set line 124;
select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';
================
Don't forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.
================Export was always failing due to the empty tables. Here is my dynamic sql======
DECLARE
CURSOR cur IS
SELECT 'ALTER TABLE ' || table_name || ' ALLOCATE EXTENT ' vSQL
FROM user_tables
where SEGMENT_CREATED = 'NO';
BEGIN
FOR c IN cur LOOP
BEGIN
EXECUTE IMMEDIATE c.vSQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));
END;
END LOOP;
END;
/
Out of habit I have been using the older data import / export functions in lieu of using the Data Pump commands that Oracle recommends. As of 11g2 I can no longer do this. The exp.exe no longer exports tables that do not have an entry in DBA_SEGMENTS. This is due to a new feature named “deferred segment creation”.
The syntax for the new Data Pump commands are different and require some additional setup / maneuvering on the Oracle Server itself to complete. The files when importing now must be placed in the Data Pump Directory. This can be found by running the following query against the database server.
select owner, directory_name, directory_path from dba_directories
================
The cause is a new feature of Oracle 11.2: deferred segment creation.
All newly created tables that has no rows in it (ever!), has no corresponding row in DBA_SEGMENTS, so somewhy won't get exported with (deprecated) exp utility. Use expdp instead.
================
select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';
set autocommit on;
set line 124;
select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';
================
Don't forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.
================Export was always failing due to the empty tables. Here is my dynamic sql======
DECLARE
CURSOR cur IS
SELECT 'ALTER TABLE ' || table_name || ' ALLOCATE EXTENT ' vSQL
FROM user_tables
where SEGMENT_CREATED = 'NO';
BEGIN
FOR c IN cur LOOP
BEGIN
EXECUTE IMMEDIATE c.vSQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));
END;
END LOOP;
END;
/
ORA-24330 OCI INTERNAL ERROR
Solution: alter system set client_result_cache_size=0 scope=spfile;
Effect of client_result_cache_size On Client Applications Or OCI Based Applications [ID 1300727.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
- Receiving error 'ORA-24330: OCI INTERNAL ERROR' while running back end Procedures from SQL*Plus and also from the Application connected to the Database - Oracle 11g 11.2.0.2.
- Unable to log in through Application.
Changes
- Database was upgraded from Oracle 11g Release 2 - 11.2.0.1 to 11.2.0.2. The client_result_cache_size was set to 1GB.
- Parameter client_result_cache_size is not on by default and the recommended value is 0.
Cause
client_result_cache_size is supposed to reset once the client process terminates but in cases like transactions with millions of rows the cache gets full very soon and does not reset itself while the client process is still on and fails during that time with errors like 'ORA-24330: OCI INTERNAL ERROR'.
If client_result_cache_size is set to a non zero value, technically it would mean that you have turned on the setting client_result_cache_size and it does have a limit active until that client session that started is active. For example; if the value is set to 1GB and the process that started client process is still actively processing million of rows then it will for sure get 'ORA-24330: OCI INTERNAL ERROR' when it reaches 1GB cache which is the set hard limit value.
So for higher volume of transactions the recommended value for client_result_cache_size is zero to avoid all such issues.
BUG 8480915 - ORA-24330: OCI INTERNAL ERROR IN ODP.NET STRESS WHEN CLIENT RESULT CACHE ENABLED
Base BUG 8418934 - KPOQGINV: CLIENT CACHING GOT DISABLED AFTER BADREGID ERROR
Solution
'client_result_cache_size' is an internal integer parameter and to change settings, please do:
1. Setting client_result_cache_size = 0 in the initialization (init.ora) parameter files followed by instance restart is the most reliable way.
client_result_cache_size=0 need to be set at the init.ora and bouncing the DB should effectively set that value.
If client_result_cache_size is set to 0 and it would mean that NO client_result_cache_size is on and nothing will be in cache.
(OR)
Client caching can be totally removed by commenting out the parameter setting client_result_cache_size from init.ora (or) by totally removing that entry.
Once the DB comes back on, you can check the value from SQL*Plus by doing:
SQL> show parameter CLIENT_RESULT_CACHE_SIZE;
2. Alternatively;
If spfile is used;
i. connect / as sysdba to database
ii. alter system set client_result_cache_size=64000 scope=SPFILE; -- value set to 64KB, please note that this is just a sample value.
iii. shutdown the database
iv. startup the database
Once the DB comes back on, you can check the value from SQL*Plus by doing:
SQL> show parameter CLIENT_RESULT_CACHE_SIZE;
'client_result_cache_size' is stored on the Client side. Once that limit have reached to the maximum then all other subsequent connections will fail. That is why it is recommended to set to 0 for high volume of transactions in which case there will be no caching. Even if there's a limit it gets reset only when the Database is bounced.
References
Effect of client_result_cache_size On Client Applications Or OCI Based Applications [ID 1300727.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
- Receiving error 'ORA-24330: OCI INTERNAL ERROR' while running back end Procedures from SQL*Plus and also from the Application connected to the Database - Oracle 11g 11.2.0.2.
- Unable to log in through Application.
Changes
- Database was upgraded from Oracle 11g Release 2 - 11.2.0.1 to 11.2.0.2. The client_result_cache_size was set to 1GB.
- Parameter client_result_cache_size is not on by default and the recommended value is 0.
Cause
client_result_cache_size is supposed to reset once the client process terminates but in cases like transactions with millions of rows the cache gets full very soon and does not reset itself while the client process is still on and fails during that time with errors like 'ORA-24330: OCI INTERNAL ERROR'.
If client_result_cache_size is set to a non zero value, technically it would mean that you have turned on the setting client_result_cache_size and it does have a limit active until that client session that started is active. For example; if the value is set to 1GB and the process that started client process is still actively processing million of rows then it will for sure get 'ORA-24330: OCI INTERNAL ERROR' when it reaches 1GB cache which is the set hard limit value.
So for higher volume of transactions the recommended value for client_result_cache_size is zero to avoid all such issues.
BUG 8480915 - ORA-24330: OCI INTERNAL ERROR IN ODP.NET STRESS WHEN CLIENT RESULT CACHE ENABLED
Base BUG 8418934 - KPOQGINV: CLIENT CACHING GOT DISABLED AFTER BADREGID ERROR
Solution
'client_result_cache_size' is an internal integer parameter and to change settings, please do:
1. Setting client_result_cache_size = 0 in the initialization (init.ora) parameter files followed by instance restart is the most reliable way.
client_result_cache_size=0 need to be set at the init.ora and bouncing the DB should effectively set that value.
If client_result_cache_size is set to 0 and it would mean that NO client_result_cache_size is on and nothing will be in cache.
(OR)
Client caching can be totally removed by commenting out the parameter setting client_result_cache_size from init.ora (or) by totally removing that entry.
Once the DB comes back on, you can check the value from SQL*Plus by doing:
SQL> show parameter CLIENT_RESULT_CACHE_SIZE;
2. Alternatively;
If spfile is used;
i. connect / as sysdba to database
ii. alter system set client_result_cache_size=64000 scope=SPFILE; -- value set to 64KB, please note that this is just a sample value.
iii. shutdown the database
iv. startup the database
Once the DB comes back on, you can check the value from SQL*Plus by doing:
SQL> show parameter CLIENT_RESULT_CACHE_SIZE;
'client_result_cache_size' is stored on the Client side. Once that limit have reached to the maximum then all other subsequent connections will fail. That is why it is recommended to set to 0 for high volume of transactions in which case there will be no caching. Even if there's a limit it gets reset only when the Database is bounced.
References
Windows 7 64 bit (unable to make connect to sql data source error# -1)
Error in odbc.dbf : Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener
Solution:
I have changed scb1213srv with scb1213 or someother name in directy.dbf, odbcad32, tnsnames.ora Now it is working
Cause:
Client IT team create ODBC using registry batch file, may scb1213srv is present some where.
ODBC is not points to listener address properly
Steps
Check your PATH and ORACLE_HOME environment variables
They should point to the location you installed the client and under which your tnsnames.ora file is located.
Solution:
I have changed scb1213srv with scb1213 or someother name in directy.dbf, odbcad32, tnsnames.ora Now it is working
Cause:
Client IT team create ODBC using registry batch file, may scb1213srv is present some where.
ODBC is not points to listener address properly
Steps
Check your PATH and ORACLE_HOME environment variables
They should point to the location you installed the client and under which your tnsnames.ora file is located.
LD error money obligation cexist ORA-01422 exact fetch returns more than one requested number of rows
That error occurs in a SELECT ... INTO statement, when your query returns more than one row. Check your where clause, and make the necessary changes in order to ensure just 1 row is returned. There is a predefined exception for that error. It is: too_many_rows. If you know your query could return more than 1 row in some cases, you should put an exception handler, something like this:
<pre>Begin
SELECT xx into l_xx
FROM table_x;
Exception
when too_many_rows then
' do something
End;</pre>
select distinct oowncode,stkcode
from compexist
where firmnumber = 'FGSB-00001'
and stkcode in (select stkcode
from (select distinct stkcode, oowncode
from compexist
where firmnumber = 'FGSB-00001'
and exchange = 'BSE ')
group by stkcode
having count(*) > 1)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-06512: at "LDBO.PK_NSEBI
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651
duplicacy of oowncode in accountaddressdetails
SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;
<pre>Begin
SELECT xx into l_xx
FROM table_x;
Exception
when too_many_rows then
' do something
End;</pre>
select distinct oowncode,stkcode
from compexist
where firmnumber = 'FGSB-00001'
and stkcode in (select stkcode
from (select distinct stkcode, oowncode
from compexist
where firmnumber = 'FGSB-00001'
and exchange = 'BSE ')
group by stkcode
having count(*) > 1)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-06512: at "LDBO.PK_NSEBI
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651
duplicacy of oowncode in accountaddressdetails
SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;
Nse Bill Data Generation cannot take place. ~-1422~ORA-01422 exact fetch returns more than requested number of rows~ORA-06512 at LDBO.PK_NSEBI
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-06512: at "LDBO.PK_NSEBI
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651
SPLOGIN IS PRESENT IN LDBO AND SYS USER
duplicacy of oowncode in accountaddressdetails
SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;
primary key is from
FIRMNUMBER
CODE
but can be dupicate oowncode
Connectivity error: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: Nse Bill Data Generation cannot take place. ~-1422~ORA-01422: exact fetch returns more than requested number of rows~ORA-0651
SPLOGIN IS PRESENT IN LDBO AND SYS USER
duplicacy of oowncode in accountaddressdetails
SELECT COUNT(*) INTO LNTOTALRECORDS FROM ACCOUNTADDRESSDETAIL ACCOUNTADDRESSDETAIL
WHERE ACCOUNTADDRESSDETAIL.FIRMNUMBER=ICFIRMNUMBER AND ACCOUNTADDRESSDETAIL.OOWNCODE=CLEXTRA.OOWNCODE ;
primary key is from
FIRMNUMBER
CODE
but can be dupicate oowncode
table alias uncommitted changes
http://www.mofeel.net/110-microsoft-public-fox-programmer-exchange/5596.aspx
http://support.microsoft.com/KB/249836
Table buffer for alias "name" contains uncommitted changes. (1545)
The problem manifests itself when I load data, modify it and load the next record. When that happens it throws Error #1545 Table buffer for alias "My_Cursor" contains uncommitted changes. I have been able to solve the problem by closing the cursor before I reload the new data. I missed this required step before.
---solution
create index on that vfp table
---------CHECK FOXPRO VIEW
MAIN.DBC
MODI CONNECTION
MODI VIEW
alter system flush buffer_cache;
alter system flush shared_pool;
----------
seems like you have the table set to Buffering = 5 Table. So when you skip
it will not save the record. either change the buffering to a 3 Record, or
do an implicit Tableupdate(). The record is not being saved, that's all
SET MULTILOCKS ON
=CURSORSETPROP("Buffering",3)
-------------
Appending a record to a view with
buffered changes is allowed and would
just add that buffered, depending on the
buffermode.
Have you SET MULTILOCKS ON?
What buffermode do you use?
If you are using remote views only
buffermode 3 and 5 are allowed, so
only optimistic buffering is possible.
---------------------
When I fill a row/column in the grid ( DETAILBILL Table), display error :
CursorSetProp("buffering",THIS.oldBuffering,aTablesUsed([m.i,1]) && optimistic table buffering
Error: 1545
Table buffer for alias "DETAILBILL" contains uncommitted changes
Line 67
My code in form destroy event :
SELECT DETAILBILL
TABLEUPDATE(.T.)
You cannot change the buffering mode when there are uncommitted changes in the buffer. These changes are saved to the disk when you issue TABLEUPDATE() but your TABLEUPDATE() is issued after the CursorSetProp probably. To save changes made to your data in Destroy event is too late.
In versions prior to Visual FoxPro 9, using a SQL SELECT statement meant that the results were always pulled from disk. This meant that if you wanted to query uncommitted changes from a buffered table, you were forced to use procedural commands. Now it's possible to specify for each table in a SELECT statement whether to read from the disk or from the local buffer using SET SQLBUFFERING and SELECT ... WITH (Buffering = <lexpr>).
Some examples of how to use WITH (BUFFERING ...) include:
SELECT * FROM Customer WITH (BUFFERING = .t.)
SELECT * FROM Orders WITH (BUFFERING = lUseBuffer)
SELECT DISTINCT c.city, o.shipcity ;
FROM customers C WITH (BUFFERING=.T.) ;
JOIN orders O WITH (BUFFERING=.T.) ;
ON c.customerID = o.customerID
http://support.microsoft.com/KB/249836
Table buffer for alias "name" contains uncommitted changes. (1545)
The problem manifests itself when I load data, modify it and load the next record. When that happens it throws Error #1545 Table buffer for alias "My_Cursor" contains uncommitted changes. I have been able to solve the problem by closing the cursor before I reload the new data. I missed this required step before.
---solution
create index on that vfp table
---------CHECK FOXPRO VIEW
MAIN.DBC
MODI CONNECTION
MODI VIEW
alter system flush buffer_cache;
alter system flush shared_pool;
----------
seems like you have the table set to Buffering = 5 Table. So when you skip
it will not save the record. either change the buffering to a 3 Record, or
do an implicit Tableupdate(). The record is not being saved, that's all
SET MULTILOCKS ON
=CURSORSETPROP("Buffering",3)
-------------
Appending a record to a view with
buffered changes is allowed and would
just add that buffered, depending on the
buffermode.
Have you SET MULTILOCKS ON?
What buffermode do you use?
If you are using remote views only
buffermode 3 and 5 are allowed, so
only optimistic buffering is possible.
---------------------
When I fill a row/column in the grid ( DETAILBILL Table), display error :
CursorSetProp("buffering",THIS.oldBuffering,aTablesUsed([m.i,1]) && optimistic table buffering
Error: 1545
Table buffer for alias "DETAILBILL" contains uncommitted changes
Line 67
My code in form destroy event :
SELECT DETAILBILL
TABLEUPDATE(.T.)
You cannot change the buffering mode when there are uncommitted changes in the buffer. These changes are saved to the disk when you issue TABLEUPDATE() but your TABLEUPDATE() is issued after the CursorSetProp probably. To save changes made to your data in Destroy event is too late.
In versions prior to Visual FoxPro 9, using a SQL SELECT statement meant that the results were always pulled from disk. This meant that if you wanted to query uncommitted changes from a buffered table, you were forced to use procedural commands. Now it's possible to specify for each table in a SELECT statement whether to read from the disk or from the local buffer using SET SQLBUFFERING and SELECT ... WITH (Buffering = <lexpr>).
Some examples of how to use WITH (BUFFERING ...) include:
SELECT * FROM Customer WITH (BUFFERING = .t.)
SELECT * FROM Orders WITH (BUFFERING = lUseBuffer)
SELECT DISTINCT c.city, o.shipcity ;
FROM customers C WITH (BUFFERING=.T.) ;
JOIN orders O WITH (BUFFERING=.T.) ;
ON c.customerID = o.customerID
ORA-22288 File Or LOB Operation FILEOPEN Failed On Windows2008 R2 OS SAN Attached Drive [ID 1504521.1]
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Microsoft Windows x64 (64-bit)
Symptoms
Error
ORA-22288: file or LOB operation FILEOPEN failed on Windows2008 R2 OS SAN attached drive
Directory object refers to a directory which is a LUN on the netapp.
Re-pointing the Directory object to a folder on C:\ drive of the server with same permissions as the directory on the
LUN, the problem is resolved
Cause
Same symptoms described in
Bug 12667656 - DBMS_LOB.FILEOPEN() FAILS WITH ORA-22288 ON WINDOWS 2008 R2 LUN
which is closed as not a BUG
The implementation of a LUN may take place by junctions/symlinks in Windows.
It is disabled to use symlink/junctions with BFILE for security reason.
This has been introduced in unpublished Bug 2262685.
Solution
1. Use the new "disable_directory_link_check" access parameter, present in 11.2.0.3 and above versions. This
access parameter will disable additional checks for junctions.
For this, upgrade to the 11.2.0.2 latest bundle patch or 11.2.0.3 to get the new feature before creating an
external table with "disable_directory_link_check" access parameter.
-- OR --
2. Disable the check and allow links by turning on the hidden internal parameter "_kolfuseslf" in a DBA (SYS)
session, i.e. run
6/20/13 Document Display
https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_adf.ctrl-state=6no1qqwc6_69 2/2
SQL> alter session set "_kolfuseslf" = true;
before executing the previously failing code/scripts
alter session set "_kolfuseslf" = true;
alter system set "_kolfuseslf" = true;
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Microsoft Windows x64 (64-bit)
Symptoms
Error
ORA-22288: file or LOB operation FILEOPEN failed on Windows2008 R2 OS SAN attached drive
Directory object refers to a directory which is a LUN on the netapp.
Re-pointing the Directory object to a folder on C:\ drive of the server with same permissions as the directory on the
LUN, the problem is resolved
Cause
Same symptoms described in
Bug 12667656 - DBMS_LOB.FILEOPEN() FAILS WITH ORA-22288 ON WINDOWS 2008 R2 LUN
which is closed as not a BUG
The implementation of a LUN may take place by junctions/symlinks in Windows.
It is disabled to use symlink/junctions with BFILE for security reason.
This has been introduced in unpublished Bug 2262685.
Solution
1. Use the new "disable_directory_link_check" access parameter, present in 11.2.0.3 and above versions. This
access parameter will disable additional checks for junctions.
For this, upgrade to the 11.2.0.2 latest bundle patch or 11.2.0.3 to get the new feature before creating an
external table with "disable_directory_link_check" access parameter.
-- OR --
2. Disable the check and allow links by turning on the hidden internal parameter "_kolfuseslf" in a DBA (SYS)
session, i.e. run
6/20/13 Document Display
https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_adf.ctrl-state=6no1qqwc6_69 2/2
SQL> alter session set "_kolfuseslf" = true;
before executing the previously failing code/scripts
alter session set "_kolfuseslf" = true;
alter system set "_kolfuseslf" = true;
ora-600 [16513] [1403]
http://www.sql.ru/forum/377566/vopros-pro-shutdown-abort
ora-600 [16513] [1403] [20]
SQL > Select count (*) from $ obj;
COUNT (*)
----------
6296
SQL > delete from $ obj;
6296 rows deleted.
SQL > shutdown abort
ORACLE instance shut down.
SQL > Startup;
ORACLE instance started.
Total System Global Area 135760720 bytes
Fixed Size 731984 bytes
Variable Size 104857600 bytes
Database Buffers 29360128 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA- 01092 : ORACLE instance terminated. Disconnection forced
after all objects are created from the bootstrap $, when the first call
=====================
PARSING IN CURSOR # 5 len = 52 dep = 1 uid = 0 oct = 3 lid = 0 tim = 2701521679320 hv = 782031521 ad = '84756cb8 '
Select ctime, mtime, stime from obj $ where obj # =: 1
END OF STMT
PARSE # 5 : c = 0 , e = 966 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 1 , og = 0 , tim = 2701521679306
BINDS # 5 :
bind 0 : dty = 2 mxl = 22 ( 22 ) mal = 00 scl = 00 pre = 00 oacflg = 08 oacfl2 = 1 Size = 24 offset = 0
bfp = ffffffff7cc6a138 bln = 22 avl = 02 flg = 05
value = 4
EXEC # 5 : c = 10000 , e = 1,340 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 0 , dep = 1 , og = 4 , tim = 2701521681024
WAIT # 5 : Nam = 'file db Sequential Read ' ela = 109 p1 = 1 p2 = 94 p3 = 1
WAIT # 5 : Nam = 'db file Sequential Read' ela = 78 p1 = 1 p2 = 1291 p3 = 1
Begin Cleaning out Block ...
Block header dump: 0x0040050b
Object ID on Block? Y
seg / obj: 0x24 csc: 0x00. 2452 itc: 2 flg: O typ: 2 - INDEX
fsl: 2 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004 0x008000ba. 0002 . 01 CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378 0x00800025. 0075 .0 F ---- 298 fsc 0x0fd9.00000000
- Poskipal -
----- End of leaf dump Block -----
Found All committed transactions
- Poskipal
Block cleanout Record , SCN: 0x0000.001b3c72 ver: opt 0x01: 0x01, entries follow ...
itli: 2 flg: 2 scn: 0x0000. 00000001
Block header dump: 0x0040050b
Object ID on Block? Y
seg / obj: 0x24 csc: 0x00.1b3c72 itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004 0x008000ba. 0002 . 01 CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378 0x00800025. 0075 .0 F C --- 0 scn 0x0000.00000001
- T. E he just commits, no checks, as a result of
... clean out dump
Internal or Fatal error
ORA- 00600 : internal error code, arguments: [ 16513 ], [ 1403 ], [ 4 ], [], [], [], []
- Ora1403 - no data found
ora-600 [16513] [1403] [20]
SQL > Select count (*) from $ obj;
COUNT (*)
----------
6296
SQL > delete from $ obj;
6296 rows deleted.
SQL > shutdown abort
ORACLE instance shut down.
SQL > Startup;
ORACLE instance started.
Total System Global Area 135760720 bytes
Fixed Size 731984 bytes
Variable Size 104857600 bytes
Database Buffers 29360128 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA- 01092 : ORACLE instance terminated. Disconnection forced
after all objects are created from the bootstrap $, when the first call
=====================
PARSING IN CURSOR # 5 len = 52 dep = 1 uid = 0 oct = 3 lid = 0 tim = 2701521679320 hv = 782031521 ad = '84756cb8 '
Select ctime, mtime, stime from obj $ where obj # =: 1
END OF STMT
PARSE # 5 : c = 0 , e = 966 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 1 , og = 0 , tim = 2701521679306
BINDS # 5 :
bind 0 : dty = 2 mxl = 22 ( 22 ) mal = 00 scl = 00 pre = 00 oacflg = 08 oacfl2 = 1 Size = 24 offset = 0
bfp = ffffffff7cc6a138 bln = 22 avl = 02 flg = 05
value = 4
EXEC # 5 : c = 10000 , e = 1,340 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 0 , dep = 1 , og = 4 , tim = 2701521681024
WAIT # 5 : Nam = 'file db Sequential Read ' ela = 109 p1 = 1 p2 = 94 p3 = 1
WAIT # 5 : Nam = 'db file Sequential Read' ela = 78 p1 = 1 p2 = 1291 p3 = 1
Begin Cleaning out Block ...
Block header dump: 0x0040050b
Object ID on Block? Y
seg / obj: 0x24 csc: 0x00. 2452 itc: 2 flg: O typ: 2 - INDEX
fsl: 2 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004 0x008000ba. 0002 . 01 CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378 0x00800025. 0075 .0 F ---- 298 fsc 0x0fd9.00000000
- Poskipal -
----- End of leaf dump Block -----
Found All committed transactions
- Poskipal
Block cleanout Record , SCN: 0x0000.001b3c72 ver: opt 0x01: 0x01, entries follow ...
itli: 2 flg: 2 scn: 0x0000. 00000001
Block header dump: 0x0040050b
Object ID on Block? Y
seg / obj: 0x24 csc: 0x00.1b3c72 itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn / Fsc
0x01 0x000a.00a. 00000004 0x008000ba. 0002 . 01 CB - 0 SCN 0x0000.000021c0
0x02 0x0002. 013 . 00000378 0x00800025. 0075 .0 F C --- 0 scn 0x0000.00000001
- T. E he just commits, no checks, as a result of
... clean out dump
Internal or Fatal error
ORA- 00600 : internal error code, arguments: [ 16513 ], [ 1403 ], [ 4 ], [], [], [], []
- Ora1403 - no data found
ORA-00350 log 3 of instance beb1314 (thread 1) needs to be archived ORA-00312 online log 3 thread 1 'EBEBD1314REDO03.LOG'
ORA-00350 ORA-00312
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
Database altered.
alter database open;
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
Database altered.
alter database open;
ORA-16014 ORA-00312
----------ORA-16014 ORA-00312
Archive process error: ORA-16014: log 3 sequence# 29808 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1112\REDO03.LOG'
That would happen if the destination filesystem is full (i.e. at 100%) and Oracle cannot write the archivelog to that filesystem.
The general solution for this error is
- check the Disk space
- check where the archive location exists
- Try Clearing the Unarchived log group through the SQL :
alter database clear unarchived logfile group <<No>>;
- Bounce the Db
- Bounce the Server
---------------------------Archive Log Creation Fails With Errors ORA-16014, ORA-00312 [ID 1373314.1]
Symptoms
When attempting to run "alter system switch logfile;", the session hangs and following errors are reported in the alert log and trace file:
Alert log :
Sun Oct 30 03:01:55 2011
ARC1: Failed to archive log 3 thread 3 sequence 122674
Sun Oct 30 03:01:55 2011
Errors in file /apps/oracle/9.2.0/admin/cwld/bdump/cwld_arc1_889040.trc:
ORA-16014: log 3 sequence# 122674 not archived, no available destinations
ORA-00312: online log 3 thread 3: '/data/oradata/cwld/redo3.log'
Changes
In our example, the database was moved (to new SAN).
Cause
In this case, the issue is caused by an inaccessible/incorrect archiving directory.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
-------------------- ------- ------------------------------
log_archive_dest_1 string location=/data3/oradata/cwld/archive/
However, that location does not exist and the archive log cannot be created.
Solution
Correct the archive path to match the new location, for example;
SQL> alter system set log_archive_dest_1 = 'location=/data1/oradata/archive/';
Or
Remove the old directory path on the operating system and create the new directory path to match the Oracle archiving location.
------------------------------------------------------------------------------------------
Archive process error: ORA-16014: log 3 sequence# 53538 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
alter system set db_recovery_file_dest_size=200m;
------------------------------
select Group#,Member from V$logfile;
select group#, status,bytes/1024/1024 from v$log;
alter database add logfile group 4 ('E:\BEBD1314\REDO04.LOG') size 100m;
alter database add logfile group 5 ('E:\BEBD1314\REDO05.LOG') size 100m;
alter database add logfile group 6 ('E:\BEBD1314\REDO06.LOG') size 100m;
ALTER SYSTEM CHECKPOINT GLOBAL;
select group#, status from v$log;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter system switch logfile;
alter system switch logfile;
select group#, status,bytes/1024/1024 from v$log;
Note: Can not drop current logfile should be inactive state;
ORA-00350 ORA-00312
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
Database altered.
alter database open;
Archive process error: ORA-16014: log 3 sequence# 29808 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1112\REDO03.LOG'
That would happen if the destination filesystem is full (i.e. at 100%) and Oracle cannot write the archivelog to that filesystem.
The general solution for this error is
- check the Disk space
- check where the archive location exists
- Try Clearing the Unarchived log group through the SQL :
alter database clear unarchived logfile group <<No>>;
- Bounce the Db
- Bounce the Server
---------------------------Archive Log Creation Fails With Errors ORA-16014, ORA-00312 [ID 1373314.1]
Symptoms
When attempting to run "alter system switch logfile;", the session hangs and following errors are reported in the alert log and trace file:
Alert log :
Sun Oct 30 03:01:55 2011
ARC1: Failed to archive log 3 thread 3 sequence 122674
Sun Oct 30 03:01:55 2011
Errors in file /apps/oracle/9.2.0/admin/cwld/bdump/cwld_arc1_889040.trc:
ORA-16014: log 3 sequence# 122674 not archived, no available destinations
ORA-00312: online log 3 thread 3: '/data/oradata/cwld/redo3.log'
Changes
In our example, the database was moved (to new SAN).
Cause
In this case, the issue is caused by an inaccessible/incorrect archiving directory.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
-------------------- ------- ------------------------------
log_archive_dest_1 string location=/data3/oradata/cwld/archive/
However, that location does not exist and the archive log cannot be created.
Solution
Correct the archive path to match the new location, for example;
SQL> alter system set log_archive_dest_1 = 'location=/data1/oradata/archive/';
Or
Remove the old directory path on the operating system and create the new directory path to match the Oracle archiving location.
------------------------------------------------------------------------------------------
Archive process error: ORA-16014: log 3 sequence# 53538 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
alter system set db_recovery_file_dest_size=200m;
------------------------------
select Group#,Member from V$logfile;
select group#, status,bytes/1024/1024 from v$log;
alter database add logfile group 4 ('E:\BEBD1314\REDO04.LOG') size 100m;
alter database add logfile group 5 ('E:\BEBD1314\REDO05.LOG') size 100m;
alter database add logfile group 6 ('E:\BEBD1314\REDO06.LOG') size 100m;
ALTER SYSTEM CHECKPOINT GLOBAL;
select group#, status from v$log;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter system switch logfile;
alter system switch logfile;
select group#, status,bytes/1024/1024 from v$log;
Note: Can not drop current logfile should be inactive state;
ORA-00350 ORA-00312
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance beb1314 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'E:\BEBD1314\REDO03.LOG'
16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
Database altered.
alter database open;
ORA-00257Archiver Error, Connect Internal Only Until Freed and ORA-16014 and ORA-00312
ORA-00257Archiver Error, Connect Internal Only Until Freed and ORA-16014 and ORA-00312 [ID 376923.1]
I have stop archivelog to stop error
Symptoms
Users cannot connect to the database :
0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 231 not archived, no available destinations
ORA-00312:online log 2 thread 1:'/u02/oradata/wlbranch/redo02.log'
Cause
The flash_recovery_area is full.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive Destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 231
Next log sequence to archive 231
Current log sequence 233
SQL> select group#,status archived from v$log;
GROUP# ARCHIVED
------------ ------------------------
1 INVALIDATED
2 INACTIVE
3 INACTIVE
SQL> archive log all;
ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEEDED_DEST
The only one destination allowed (log_archive_min_succeed_dest= 1) is not able to perform the archiving.
Solution
1. Either allow more space in the DB_RECOVERY_FILE_DEST with the DB_RECOVERY_FILE_DEST_SIZE parameter :
SQL> alter system set db_recovery_file_dest_size=3G ;
2. Or to avoid the situation once the 3Gb is full , set the following parameters so that when the dest1 is full, archiving is automatically performed to the alternate dest2 :
log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/product/10.1.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2G
I have stop archivelog to stop error
Symptoms
Users cannot connect to the database :
0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 231 not archived, no available destinations
ORA-00312:online log 2 thread 1:'/u02/oradata/wlbranch/redo02.log'
Cause
The flash_recovery_area is full.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive Destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 231
Next log sequence to archive 231
Current log sequence 233
SQL> select group#,status archived from v$log;
GROUP# ARCHIVED
------------ ------------------------
1 INVALIDATED
2 INACTIVE
3 INACTIVE
SQL> archive log all;
ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEEDED_DEST
The only one destination allowed (log_archive_min_succeed_dest= 1) is not able to perform the archiving.
Solution
1. Either allow more space in the DB_RECOVERY_FILE_DEST with the DB_RECOVERY_FILE_DEST_SIZE parameter :
SQL> alter system set db_recovery_file_dest_size=3G ;
2. Or to avoid the situation once the 3Gb is full , set the following parameters so that when the dest1 is full, archiving is automatically performed to the alternate dest2 :
log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/product/10.1.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2G
Problem while archiving the redo Log
https://forums.oracle.com/thread/977515
Hi all,
I m having few issues in my server...
I get the following error in the alert log of oracle..
There are many errors
1) No space left on device
2) ARC0: I/O error 19502 archiving log 1 to
'/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
3) ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*
Also please find the v$log file query
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 405 1073741824 1 NO CURRENT
60275455 20-OCT-09
2 1 403 1073741824 1 NO INACTIVE
59987366 19-OCT-09
3 1 404 1073741824 1 NO INACTIVE
60125083 20-OCT-09
check the mount point ... is it full ?
if yes then
change the destination .....
alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;
or
delete the archive logfile
or
But not a very good idea ....
SQL> ALTER SYSTEM ARCHIVE LOG STOP;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG start;
Hi all,
I m having few issues in my server...
I get the following error in the alert log of oracle..
There are many errors
1) No space left on device
2) ARC0: I/O error 19502 archiving log 1 to
'/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
3) ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*
Also please find the v$log file query
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 405 1073741824 1 NO CURRENT
60275455 20-OCT-09
2 1 403 1073741824 1 NO INACTIVE
59987366 19-OCT-09
3 1 404 1073741824 1 NO INACTIVE
60125083 20-OCT-09
check the mount point ... is it full ?
if yes then
change the destination .....
alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;
or
delete the archive logfile
or
But not a very good idea ....
SQL> ALTER SYSTEM ARCHIVE LOG STOP;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG start;
ALTER SYSTEM SWITCH LOGFILE Statement hangs
ALTER SYSTEM SWITCH LOGFILE Statement hangs
ALTER SYSTEM SWITCH LOGFILE hangs:
Step 1: Login as sysdba.
Step 2: Enable log_archive_dest_state_1 Parameter.
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
ALTER SYSTEM SWITCH LOGFILE hangs:
Step 1: Login as sysdba.
Step 2: Enable log_archive_dest_state_1 Parameter.
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
CreateFile error 32 when trying set file time oracle 11g windows 7
Today I am going to install Oracle 10g Client and ODAC11gR2 (Oracle Data Provider .Net) in Microsoft Windows XP Professional Service Pack 2. I have got following error message in console.
CreateFile error 32 when trying set file time
After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message
CreateFile error 32 when trying set file time
After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message
ORA-00333 redo log read error block
PFILE
_ALLOW_RESETLOGS_CORRUPTION = true
UNDO_MANAGEMENT = MANUAL
RENAME SPFILE
recover database until cancel;
or
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
alter database open resetlogs;
--------------------UNDO RECREATION
now undo is corrupted
ORA-00600: internal error code, arguments: [4194]
drop corrupted undo tablespace and create a new undo tablespace.
but database is not able to open due to undo corruption
add following in pfile
undo_management = manual
event = '10513 trace name context forever, level 2'
startup restrict pfile=<initsid.ora>
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\rkdatabase\UNDOTBS02.ORA' SIZE 500M REUSE AUTOEXTEND ON;
shut immediate
change undo_tablespace=UNDOTBS2 into parameter file
startup
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
REMOVE ADD PARAMETER FROM PFILE
Dynamic change of CPU_COUNT causes ORA-600 [KsrpubwaitDeadlock] ORA-2097 Instance crash
Dynamic change of CPU_COUNT causes ORA-600 [Ksrpubwait:Deadlock] ORA-2097 Instance crash
Problem Description
Dynamic change of CPU_COUNT parameter causes oracle bug ORA-600 [ksrpubwait:deadlock]. For example, after issuing command "alter system set cpu_count=5;" it fails with following errors.
ORA-600 [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel]
ORA-2097: parameter cannot be modified because specified value is invalid
And then instance crashes.
If you look at stack trace you will see,
ksrpubwait ksrpublish kspbcast kspset0 kxfpCPUCountUpdate ksbcpcb
Cause of the Problem
This errors are caused by Oracle Bug 7535429
Solution of the Problem
Solution 01: Bug 7535429 is fixed in version 11.2. So upgrade your oracle database to 11gR2 will solve the problem.
There is one-off patch which also help you to solve the problem. Check for the availability of one-off Patch 7535429 for your platform on MetaLink.
Solution 02: Explicitely setting CPU_COUNT to new value.
Solution 03: If you start the instance from pfile instead of using spfile then it will also solve the problem.
Solution 04: Set explicitly PARALLEL_MAX_SERVERS to the current value instead of relying default value.
Problem Description
Dynamic change of CPU_COUNT parameter causes oracle bug ORA-600 [ksrpubwait:deadlock]. For example, after issuing command "alter system set cpu_count=5;" it fails with following errors.
ORA-600 [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel]
ORA-2097: parameter cannot be modified because specified value is invalid
And then instance crashes.
If you look at stack trace you will see,
ksrpubwait ksrpublish kspbcast kspset0 kxfpCPUCountUpdate ksbcpcb
Cause of the Problem
This errors are caused by Oracle Bug 7535429
Solution of the Problem
Solution 01: Bug 7535429 is fixed in version 11.2. So upgrade your oracle database to 11gR2 will solve the problem.
There is one-off patch which also help you to solve the problem. Check for the availability of one-off Patch 7535429 for your platform on MetaLink.
Solution 02: Explicitely setting CPU_COUNT to new value.
Solution 03: If you start the instance from pfile instead of using spfile then it will also solve the problem.
Solution 04: Set explicitly PARALLEL_MAX_SERVERS to the current value instead of relying default value.
ORA-600 [6006] ORA-600 [6856]
While startup, instance terminated after ORA-600 [6006] ORA-600 [6856]
Problem Description
While starting up the instance it fails with following errors:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []
and then SMON abnormally crashes the database instance.
Cause of the Problem
These are oracle bugs and these ORA-600 [6006] and ORA-600 [6856] bug occurred whenever undo segments are trying to rollback a failed transaction and cannot.
For error "ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []" ,
Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
For error "ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []" , SMON is trying to recover a dead transaction. But the undo application runs into an internal error (trying to delete a row that is already deleted).
Solution of the Problem
1) Review the trace files and look for the object(s) involved. If the trace file does not have a SQL statement, search on the following: "block dump header"
2) In the block header there will be a seg/obj = hex value. Convert the hex to dec and this will give you the data_object_id.
3) The alert.log may also show the affected object, for example:
ORACLE Instance ORCL (pid = 8) - Error 600 encountered while recovering transaction (9999, 36) on object 45879.
SQL>select owner, object_name from dba_objects where data_object_id = object#;
This will be the object you need to work with.
To implement the solution:
1. shutdown the oracle instance.
SQL> shut immediate
2. set event - event="10513 trace name context forever, level 2" (this event and setting disables transaction recovery which was initiated by SMON). Open the pfile and add the following line inside pfile.
event="10513 trace name context forever, level 2"
3. startup instance
SQL> startup
4.
- If the object is an index - drop and recreate.
- If the object is a table - drop / export / Create Table as Select (CTAS) to change the object ID
5. shutdown the instance
SQL> shut immediate
6. remove the event
Open the pfile and remove the event line that was added.
7. startup the instance
SQL> startup
Problem Description
While starting up the instance it fails with following errors:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []
and then SMON abnormally crashes the database instance.
Cause of the Problem
These are oracle bugs and these ORA-600 [6006] and ORA-600 [6856] bug occurred whenever undo segments are trying to rollback a failed transaction and cannot.
For error "ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []" ,
Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
For error "ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []" , SMON is trying to recover a dead transaction. But the undo application runs into an internal error (trying to delete a row that is already deleted).
Solution of the Problem
1) Review the trace files and look for the object(s) involved. If the trace file does not have a SQL statement, search on the following: "block dump header"
2) In the block header there will be a seg/obj = hex value. Convert the hex to dec and this will give you the data_object_id.
3) The alert.log may also show the affected object, for example:
ORACLE Instance ORCL (pid = 8) - Error 600 encountered while recovering transaction (9999, 36) on object 45879.
SQL>select owner, object_name from dba_objects where data_object_id = object#;
This will be the object you need to work with.
To implement the solution:
1. shutdown the oracle instance.
SQL> shut immediate
2. set event - event="10513 trace name context forever, level 2" (this event and setting disables transaction recovery which was initiated by SMON). Open the pfile and add the following line inside pfile.
event="10513 trace name context forever, level 2"
3. startup instance
SQL> startup
4.
- If the object is an index - drop and recreate.
- If the object is a table - drop / export / Create Table as Select (CTAS) to change the object ID
5. shutdown the instance
SQL> shut immediate
6. remove the event
Open the pfile and remove the event line that was added.
7. startup the instance
SQL> startup
ORA-600 [17147] ORA-600 [Kghlkrem1] ORA-7445 kghlkremf() database crash
ORA-600 [17147] ORA-600 [Kghlkrem1] ORA-7445 kghlkremf() database crash
During normal database activity, in alert log the following ORA-00600 error occurred and it causes database crashed.
ORA-00600: internal error code, arguments: [17147], [0xC0000003E4CE10A5], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0xC0000003E4CE10B0], [], [], [], []
ORA-7445: exception encountered: core dump [kghlkremf()+33] [SIGSEGV]
If you look at the trace file you no longer will see any sql that causes this bug.
The call stack is as follows:
kghfrmrg kghfre qsmqktcc ktcdso ktcrcm ktdcmt k2lcom k2send xctctl xctcom_with_options opicom
or
k2send xctctl xctcom_with_options opicom opiodr
or
kghalo ktcccadd kwqidracbk kwqidcpmc kwqidafm0 kwqididqx kpoaqdq
or
kghadd_reserved_extent kghget_reserved_extent kghgex kghfnd
Cause of the Problem
The problem occurred due to oracle bug. The bug involves abnormal termination of queries involving bloom filters. This manifests itself as memory corruptions. The memory was freed, reallocated to some other client, and corrupted but slaves were still writing into it.
Solution of the Problem
Solution 01:
This bug is fixed in oracle database version 10.2.0.4. So apply 10.2.0.4 patchset to solve this problem.
Solution 02:
Workaround, you can set the oracle hidden parameter _bloom_filter_enabled to false.
This parameter can be set dynamically:
If you are using spfile then just set as,
SQL> connect / as sysdba
SQL> alter system set "_bloom_filter_enabled"=false scope=both;
If you use pfile to startup your database remove the scope parameter that is use,
SQL> alter system set "_bloom_filter_enabled"=false;
During normal database activity, in alert log the following ORA-00600 error occurred and it causes database crashed.
ORA-00600: internal error code, arguments: [17147], [0xC0000003E4CE10A5], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0xC0000003E4CE10B0], [], [], [], []
ORA-7445: exception encountered: core dump [kghlkremf()+33] [SIGSEGV]
If you look at the trace file you no longer will see any sql that causes this bug.
The call stack is as follows:
kghfrmrg kghfre qsmqktcc ktcdso ktcrcm ktdcmt k2lcom k2send xctctl xctcom_with_options opicom
or
k2send xctctl xctcom_with_options opicom opiodr
or
kghalo ktcccadd kwqidracbk kwqidcpmc kwqidafm0 kwqididqx kpoaqdq
or
kghadd_reserved_extent kghget_reserved_extent kghgex kghfnd
Cause of the Problem
The problem occurred due to oracle bug. The bug involves abnormal termination of queries involving bloom filters. This manifests itself as memory corruptions. The memory was freed, reallocated to some other client, and corrupted but slaves were still writing into it.
Solution of the Problem
Solution 01:
This bug is fixed in oracle database version 10.2.0.4. So apply 10.2.0.4 patchset to solve this problem.
Solution 02:
Workaround, you can set the oracle hidden parameter _bloom_filter_enabled to false.
This parameter can be set dynamically:
If you are using spfile then just set as,
SQL> connect / as sysdba
SQL> alter system set "_bloom_filter_enabled"=false scope=both;
If you use pfile to startup your database remove the scope parameter that is use,
SQL> alter system set "_bloom_filter_enabled"=false;
ORA-00600 [730] [SPACE LEAK] OR ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] during shutdown
ORA-00600 [730] [SPACE LEAK] OR ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] during shutdown
Problem Description
While shutting down oracle database the following error occurred.
In oracle 10g and 11g,
ORA-00600: internal error code, arguments: [730], [4100736], [space leak], [], [], [], [], [], [], [], [], []
If you observe stack trace it will look like,
ksesic2 <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- main <- start
In oracle 10g the error looks like,
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [] ,[], [], [], [], []
If you observe stack trace it will look like, kglshu <- kqlnfy <- kscnfy <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
Cause of the Problem
The error occurred due to oracle bug. Oracle named this bug as BUG 7572335. This happens during database shutdown when child cursors had been previously marked as kept in the shared pool. Note that this is a non-corruptive error.
Solution of the Problem
Solution 01: This bug is solved in Oracle database 11gR2. So upgrade to Oracle database 11.2 or higher is one solution.
Solution 02: If you are using Oracle database 10gR2 then up to patchset version 10.2.0.4 this bug remained and it is solved in version 10.2.0.5. You can upgrade to oracle version 10.2.0.5 by applying the Patchset, Patch 8202632.
Solution 03: If available for your platform and version, you can apply one off patch 7572335. Download and apply the fix, Patch 7572335
Problem Description
While shutting down oracle database the following error occurred.
In oracle 10g and 11g,
ORA-00600: internal error code, arguments: [730], [4100736], [space leak], [], [], [], [], [], [], [], [], []
If you observe stack trace it will look like,
ksesic2 <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- main <- start
In oracle 10g the error looks like,
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [] ,[], [], [], [], []
If you observe stack trace it will look like, kglshu <- kqlnfy <- kscnfy <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
Cause of the Problem
The error occurred due to oracle bug. Oracle named this bug as BUG 7572335. This happens during database shutdown when child cursors had been previously marked as kept in the shared pool. Note that this is a non-corruptive error.
Solution of the Problem
Solution 01: This bug is solved in Oracle database 11gR2. So upgrade to Oracle database 11.2 or higher is one solution.
Solution 02: If you are using Oracle database 10gR2 then up to patchset version 10.2.0.4 this bug remained and it is solved in version 10.2.0.5. You can upgrade to oracle version 10.2.0.5 by applying the Patchset, Patch 8202632.
Solution 03: If available for your platform and version, you can apply one off patch 7572335. Download and apply the fix, Patch 7572335
ORA-00600 internal error code, arguments [4819]
ORA-00600: internal error code, arguments: [4819]
Problem Description
While reviewing database alert log files it was report an ORA-600 [4819] error followed by ORA-00308 and ORA-27037 errors like below.
ORA-00600: internal error code, arguments: [4819], [], [], [], [], [], [], []
ORA-00308: cannot open archived log '/home/oracle/archive/arc_ARJU_001.arc'
ORA-27037: unable to obtain file status
This problem started to happen because recently database has been switched into NOARCHIVELOG mode from ARCHIVELOG mode.
Cause of the Problem
The problem arises because the LOG_ARCHIVE_DEST_n parameter is still set to point to an archive log destination and the default value for the LOG_ARCHIVE_DEST_STATE_n parameter is 'ENABLE'. Based on these two settings database still attempts to open the required archive log file during transaction recovery.
Solution of the Problem
To resolve this problem, mark the destination as deferred and null out the archive destination, That is
1) Login as sysdba.
SQL> connect / as sysdba
2) Set the archive_dest_state_1 to defer.
SQL> alter system set log_archive_dest_state_1 = defer scope=both;
3) Set log_archive_dest_1 to null.
SQL> alter system set log_archive_dest_1 = '' scope=both;
Problem Description
While reviewing database alert log files it was report an ORA-600 [4819] error followed by ORA-00308 and ORA-27037 errors like below.
ORA-00600: internal error code, arguments: [4819], [], [], [], [], [], [], []
ORA-00308: cannot open archived log '/home/oracle/archive/arc_ARJU_001.arc'
ORA-27037: unable to obtain file status
This problem started to happen because recently database has been switched into NOARCHIVELOG mode from ARCHIVELOG mode.
Cause of the Problem
The problem arises because the LOG_ARCHIVE_DEST_n parameter is still set to point to an archive log destination and the default value for the LOG_ARCHIVE_DEST_STATE_n parameter is 'ENABLE'. Based on these two settings database still attempts to open the required archive log file during transaction recovery.
Solution of the Problem
To resolve this problem, mark the destination as deferred and null out the archive destination, That is
1) Login as sysdba.
SQL> connect / as sysdba
2) Set the archive_dest_state_1 to defer.
SQL> alter system set log_archive_dest_state_1 = defer scope=both;
3) Set log_archive_dest_1 to null.
SQL> alter system set log_archive_dest_1 = '' scope=both;
ORA-00600 internal error code, arguments [13013] [5001]
http://oraclemamukutti.blogspot.in/2011/06/ora-00600-13013-5001.html
ORA-600 [13013] [a] [b] [c] [d] [e] [f]
This format relates to Oracle Server 8.0.3 to 10.1
Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code
ORA-00600: internal error code, arguments: [13013], [5001]
ORA-00600: internal error code, arguments: [13013], [5001], [268], [8447338], [5], [8447338], [17], [], [], [], [], []
Select object_name,object_type,owner from dba_objects where data_object_id=268;
select * from dba_objects where object_name='SMON_SCN_TIME';
select * frOm dba_indexes where table_name='SMON_SCN_TIME';
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') from dual;
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') from dual;
drop index "SYS"."SMON_SCN_TIME_TIM_IDX";
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ;
drop index "SYS"."SMON_SCN_TIME_SCN_IDX";
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ;
analyze table SYS.SMON_SCN_TIME validate structure cascade online;
======================================================================================================
SQL>Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argment b>
Once the Object is identified run the following :
The below command check if the table has corruption or not .
SQL> Analyze table <owner>.<table name> validate structure online ;
If this goes fine table doesnot have corruption. For next command.
If the above command fails with ORA-1498 go to Section 3
The below command check if table/index has corruption or not
SQL>Analyze table <owner>.<table name> validate structure cascade online ;
If the above command errors out with ora-1499 it indicates a corruption in index.
Go to section 2 for resolution
Run dbverify on the datafile reported in the error
Arg [c] in the ora-0600[13013] would give the Relative DBA
For example
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],[155254965], [17], []
Arg [c] --> rdba-->155254965
Use this value and find the file and block number for this dba
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
You an run dbveirfy on datafile with rfile#=37
SQL>Select name from v$datafile where rfile#=37
dbv file=<location of datafile> blocksize=<db_block_size>
Section 2 >How to resolve if a Index is corrupted
You would need to drop and recreate the index
Ensure before dropping the Index
SQL>Spool /tmp/createindex.sql
SQL>Set long 100000000
SQL>Select dbms_metadata.get_ddl('INDEX','<Index name>',<'user name>') from dual
SQL>Spool off
Refer the Following note to Identify the index
Note 563070.1
Title: ORA-1499. Table/Index row count mismatch
Please note if there is just one index in the table then you can use dbms_metadata.get_ddl to get the script. of the index and drop and recreate it.
Section 3> How to resolve if table is corrupted
Option a> Backup is available
Ora-1498 would be reported on the table.
The trace file from Ora-1498 would contain following information
Example
Block Checking:DBA = 1066265208,Block Type = KTB-managed data block --->
file 254,block 911992
data header at 0xc00000010118e07c
kdbchk: avsp(816) > tosp(812)
Block header dump: 0x3f8dea78
Object id on Block? Y
seg/obj: 0x155b452 csc: 0x05.7b2b4ee6 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3f8dde0c ver: 0x01
inc: 0 exflg: 0
Note the DBA value reported in the trace file DBA = 1066265208
Convert this to find the file number and block number having issue
Sql>Select dbms_utility.data_block_address_file('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE('1066265208')
--------------------------------------------------
254
Sql>Select dbms_utility.data_block_address_block('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK('1066265208')
---------------------------------------------------
911992
Run dbverify on the datafile containing the table
dbv file=<location of datafile> blocksize=<db_block_size>
Corruption would be reported on the block.
If you have an Rman backup do a Rman block recovery (Take the file number and block number affected from the trace file)
Rman> Blockrecover datafile <no> block <block number>
Or
If you have a user managed backup you can do an restore and recovery from good copy of the problematic datafile
Option b> Backup is not available
If no backups are available then use event 10231 at session level to create a salvage table
SQL>alter session set events '10231 trace name context forever, level 10'
SQL> Create table <owner>.salvage_table as select * from <Corrupted table> ;
Rename the Original table to old.
Rename salvage table to Original table name
or
You can use dbms_repair script. to mark the block soft corrupt.
Note 556733.1
DBMS_REPAIR SCRIPT.
How to resolve when Smon terminates the instance due to Ora-00600[13013]
If Smon is terminating the instance then.
Set event 10513 and startup the database
event="10513 trace name context forever, level 2"
SQL>Startup mount ;
SQL>Show parameter event
SQL>Alter datatabase open ;
Identify the object involved using information from Section 1.
How to resolve the issue if the object involved belongs to system tablespace
System objects are very important.
Please open a Service request with Oracle support if system tables are involved.
Identifying the row having issue when the table is having corruption.
1>Once the error occurs, using the ORA-600 arguments, gather the following information :
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[155254965], [17], []
arg b : [57353] - it is the OBJECT_ID
arg c : [155254965] - it is the block address in Decimal
arg d : [261] - is it the slot number
2. Translate the the block address in Decimal to a file# and block #
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
The Relative file is 37
The block number is 65717
Find the data_object_id for this object
Argument b is Object_id--> 57353
SQL>Select data_object_id ,object_name,owner from dba_objects where object_id=57353 ;
3. Create the rowid using dbms_rowid.rowid_create(1,DATA_OBJECT_ID,FILE#,BLOCK#,SLOT#)
In this case :
select dbms_rowid.rowid_create(1,57353,37,65717,261) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAOAJAAlAAAQC1AEF
4. You can select from table and identify the record causing the issue
SQL> Select * from <owner>.<table name> where rowid='AAAOAJAAlAAAQC1AEF';
Are there any Known Bug
There is a internal bug number 5085288
fixed in 11.1
Details
ORA-600 [13013] [5001] error can occur on a MERGE command if the DELETE pass encounters a consistent read (CR) error due to the update pass having updated the same
row and column previously.
Check for availability of one off patch usingpatch 5085288
Bug 4549673
Abstract: ORA-30926 / OERI:13030 during update
Fixed-Releases: 9208 A204 B106
Details:
ORA-30926 (in Oracle 9i) or ORA-600 [13030] (in Oracle10g) can occur
during an update DML. This can occur if an internal ORA-1551 error
occurs and is trapped (1551 errors are not visible to client
code and are trapped and handled internally)
Fixed In Ver: 11.0
Check for availability of one off patch usingpatch 5085288
ORA-600 [13013] [a] [b] [c] [d] [e] [f]
This format relates to Oracle Server 8.0.3 to 10.1
Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code
ORA-00600: internal error code, arguments: [13013], [5001]
ORA-00600: internal error code, arguments: [13013], [5001], [268], [8447338], [5], [8447338], [17], [], [], [], [], []
Select object_name,object_type,owner from dba_objects where data_object_id=268;
select * from dba_objects where object_name='SMON_SCN_TIME';
select * frOm dba_indexes where table_name='SMON_SCN_TIME';
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') from dual;
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') from dual;
drop index "SYS"."SMON_SCN_TIME_TIM_IDX";
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ;
drop index "SYS"."SMON_SCN_TIME_SCN_IDX";
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ;
analyze table SYS.SMON_SCN_TIME validate structure cascade online;
======================================================================================================
SQL>Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argment b>
Once the Object is identified run the following :
The below command check if the table has corruption or not .
SQL> Analyze table <owner>.<table name> validate structure online ;
If this goes fine table doesnot have corruption. For next command.
If the above command fails with ORA-1498 go to Section 3
The below command check if table/index has corruption or not
SQL>Analyze table <owner>.<table name> validate structure cascade online ;
If the above command errors out with ora-1499 it indicates a corruption in index.
Go to section 2 for resolution
Run dbverify on the datafile reported in the error
Arg [c] in the ora-0600[13013] would give the Relative DBA
For example
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],[155254965], [17], []
Arg [c] --> rdba-->155254965
Use this value and find the file and block number for this dba
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
You an run dbveirfy on datafile with rfile#=37
SQL>Select name from v$datafile where rfile#=37
dbv file=<location of datafile> blocksize=<db_block_size>
Section 2 >How to resolve if a Index is corrupted
You would need to drop and recreate the index
Ensure before dropping the Index
SQL>Spool /tmp/createindex.sql
SQL>Set long 100000000
SQL>Select dbms_metadata.get_ddl('INDEX','<Index name>',<'user name>') from dual
SQL>Spool off
Refer the Following note to Identify the index
Note 563070.1
Title: ORA-1499. Table/Index row count mismatch
Please note if there is just one index in the table then you can use dbms_metadata.get_ddl to get the script. of the index and drop and recreate it.
Section 3> How to resolve if table is corrupted
Option a> Backup is available
Ora-1498 would be reported on the table.
The trace file from Ora-1498 would contain following information
Example
Block Checking:DBA = 1066265208,Block Type = KTB-managed data block --->
file 254,block 911992
data header at 0xc00000010118e07c
kdbchk: avsp(816) > tosp(812)
Block header dump: 0x3f8dea78
Object id on Block? Y
seg/obj: 0x155b452 csc: 0x05.7b2b4ee6 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3f8dde0c ver: 0x01
inc: 0 exflg: 0
Note the DBA value reported in the trace file DBA = 1066265208
Convert this to find the file number and block number having issue
Sql>Select dbms_utility.data_block_address_file('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE('1066265208')
--------------------------------------------------
254
Sql>Select dbms_utility.data_block_address_block('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK('1066265208')
---------------------------------------------------
911992
Run dbverify on the datafile containing the table
dbv file=<location of datafile> blocksize=<db_block_size>
Corruption would be reported on the block.
If you have an Rman backup do a Rman block recovery (Take the file number and block number affected from the trace file)
Rman> Blockrecover datafile <no> block <block number>
Or
If you have a user managed backup you can do an restore and recovery from good copy of the problematic datafile
Option b> Backup is not available
If no backups are available then use event 10231 at session level to create a salvage table
SQL>alter session set events '10231 trace name context forever, level 10'
SQL> Create table <owner>.salvage_table as select * from <Corrupted table> ;
Rename the Original table to old.
Rename salvage table to Original table name
or
You can use dbms_repair script. to mark the block soft corrupt.
Note 556733.1
DBMS_REPAIR SCRIPT.
How to resolve when Smon terminates the instance due to Ora-00600[13013]
If Smon is terminating the instance then.
Set event 10513 and startup the database
event="10513 trace name context forever, level 2"
SQL>Startup mount ;
SQL>Show parameter event
SQL>Alter datatabase open ;
Identify the object involved using information from Section 1.
How to resolve the issue if the object involved belongs to system tablespace
System objects are very important.
Please open a Service request with Oracle support if system tables are involved.
Identifying the row having issue when the table is having corruption.
1>Once the error occurs, using the ORA-600 arguments, gather the following information :
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[155254965], [17], []
arg b : [57353] - it is the OBJECT_ID
arg c : [155254965] - it is the block address in Decimal
arg d : [261] - is it the slot number
2. Translate the the block address in Decimal to a file# and block #
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
The Relative file is 37
The block number is 65717
Find the data_object_id for this object
Argument b is Object_id--> 57353
SQL>Select data_object_id ,object_name,owner from dba_objects where object_id=57353 ;
3. Create the rowid using dbms_rowid.rowid_create(1,DATA_OBJECT_ID,FILE#,BLOCK#,SLOT#)
In this case :
select dbms_rowid.rowid_create(1,57353,37,65717,261) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAOAJAAlAAAQC1AEF
4. You can select from table and identify the record causing the issue
SQL> Select * from <owner>.<table name> where rowid='AAAOAJAAlAAAQC1AEF';
Are there any Known Bug
There is a internal bug number 5085288
fixed in 11.1
Details
ORA-600 [13013] [5001] error can occur on a MERGE command if the DELETE pass encounters a consistent read (CR) error due to the update pass having updated the same
row and column previously.
Check for availability of one off patch usingpatch 5085288
Bug 4549673
Abstract: ORA-30926 / OERI:13030 during update
Fixed-Releases: 9208 A204 B106
Details:
ORA-30926 (in Oracle 9i) or ORA-600 [13030] (in Oracle10g) can occur
during an update DML. This can occur if an internal ORA-1551 error
occurs and is trapped (1551 errors are not visible to client
code and are trapped and handled internally)
Fixed In Ver: 11.0
Check for availability of one off patch usingpatch 5085288
ORA-00600 internal error code, arguments [kdsgrp1], [], [], [], [], [], [],[]
ORA-00600: internal error code, arguments: [kdsgrp1] , This error may occur on a RAC database when (re)building index online.
The fixes for this bug are in Metalink Note : 285586.1
As a workaround please try to rebuild the index either offline or online with as little as possible activity on the affected table.
If error continues check the table for chained rows.
---------------------------------
If this error comes up, you have to do the following:
use adrci to package the incident. How to do that is described here.
adrci
show incidents
ips pack incident … to …
afterwards check into the trace file in the package.
<the package zip>\diag\rdbms\$ORACLE_SID\$ORACLE_SID\incident\incdir_<some kind of id>look into the trc files searching right in the beginning for an sql statement.
If you find a select with bind variables, check the trace furthermore for "bind" or "variable" to get the variable which has been used in that specific statement.
Find out, which tables are involved in the specific SQL and analyze each by using followin syntax
ANALYZE TABLE <SCHEMA.TABLE_NAME> VALIDATE STRUCTURE CASCADE;
You should get an error like, this:
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
go to the tracefile, that just popped up in the background dump destination.
In that file, look for the string "mismatch".
You should now arrive at a line, which explains what exactly is wrong.
In my case, I found a mismatch between an index of the table and the table itself.
The tracefile with the mismatch pointed out the object_id of the specific index, which was corrupt.
You should be able to identify it by
select owner, object_name, object_type from dba_objects where object_id=<OBJECT_ID>;
If you rebuild/recreate indexes, the analyze table should run through without any error message.
To rebuild an index, use
alter index SCHEMA.INDEX_NAME rebuild online;
or get the metadata and recreate it using
set long 999999
set lines 160 pages 9999
col text for a150
select dbms_metadata.get_ddl('INDEX','<INDEX_NAME>','<INDEX_OWNER>') as text from dual;
replace the "create index" with "create or replace index"
and run it.
To check the status of all other tables, an idea is to extract the validate.sql script out of
Note:100419.1 SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace
Then, log as sys user and run this script:
sql> @validate
This will create the "ValidateStructure" package.
After this, open a spool file and enable SERVEROUT
sql> spool myoutput.log
sql> execute dbms_output.enable(1000000);
sql> set serveroutput on
and run:
sql> execute ValidateStructure.TS('TABLESPACE_NAME', TRUE);
This will run until all requested items are scanned.
Errors from the ANALYZE commands are output to DBMS_OUTPUT and so any failing objects are listed when all TABLES / CLUSTERS have been analyzed.
More detailed output from failing ANALYZE commands will be written to the user trace file in USER_DUMP_DEST
Although, initially written for 10.2, this should still work fine against 11g databases.
Subscribe to:
Posts (Atom)