Monday, January 25, 2010

ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],

Received the following in 11.1.0.7.2 on AIX 5.3. SR raised ...

Cause was trying to duplicate a RAC DB with one thread down. Workaround was to start the second thread.


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],
[], [], [], [], []

Wednesday, January 20, 2010

Allow 11g to access UTL_SMTP

SELECT owner, name, referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_smtp.xml',
description => 'utl_smtp',
principal => 'ORADBA',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
commit;
end;
/


begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => 'USER2',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
end;
/

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => 'mysmtpserver',
lower_port => 1,
upper_port => 1024);
commit;
end;
/

Oracle 11.1.0.7.2 DB cannot write to 11.1.0.7.1 ASM.

In the case below 11.1.0.7.2 DB could not write to 11.1.0.7.1 ASM.

If you see any of the following errors, check to confirm if DB ORACLE_HOME is at exactly the same patch level as ASM ORACLE_HOME.

SQL> create tablespace asm_test;
create tablespace asm_test
*
ERROR at line 1:
ORA-19510: failed to set size of 12800 blocks for file
"+ASM_ORADATA41/t01dwh/datafile/asm_test.2558.708719699" (block size=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 12800 blocks
ORA-15061: ASM operation not supported [41]


SQL> create spfile='+ASM_ORADATA41/t01dwh/spfilet01dwh.ora_1263913779030' FROM pfile='/opt/oracle/product/11.1.0/db_1/dbs/inittl01dwh1.ora';
create spfile='+ASM_ORADATA41/t01dwh/spfilet01dwh.ora_1263913779030' FROM pfile='/opt/oracle/product/11.1.0/db_1/dbs/inittl01dwh1.ora'
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
ORA-17505: ksfdrsz:1 Failed to resize file to size 5 blocks
ORA-15061: ASM operation not supported [41]


SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4292558848 bytes
Fixed Size 2160600 bytes
Variable Size 1593837608 bytes
Database Buffers 2684354560 bytes
Redo Buffers 12206080 bytes
ORA-00600: internal error code, arguments: [kccgtt_1], [3], [], [], [], [], [], [], [], [], [], []


ORA-00202: control file: '+ASM_ORADATA42/control02.ctl'
ORA-17505: ksfdrsz:1 Failed to resize file to size 2114 blocks
ORA-15061: ASM operation not supported [41]
Control file not expanded from 1982 to 2114 blocks for upgrade:denied by OS
Errors in file /opt/oracle/diag/rdbms/t01dwh/tl01dwh1/trace/tl01dwh1_ora_119470.trc (incident=18204):
ORA-00600: internal error code, arguments: [kccgtt_1], [3], [], [], [], [], [], [], [], [], [], []

Tuesday, January 19, 2010

ASM diagnostics Report

# From Metalink Note 470211.1

SPOOL ASM_FIRST.HTML
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
SELECT * FROM V$ASM_DISKGROUP;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM V$VERSION;
SHOW PARAMETER ASM
SHOW PARAMETER CLUSTER
SHOW PARAMETER INSTANCE_TYPE
SHOW PARAMETER INSTANCE_NAME
SHOW PARAMETER SPFILE
SPOOL OFF
EXIT

Friday, January 15, 2010

Auditing feature usage in Oracle

select name, detected_usages from dba_feature_usage_statistics where dbid = (select dbid from v$database) and version = '10.2.0.4.0';

select name, highwater from dba_high_water_mark_statistics where dbid = (select dbid from v$database) and version = '10.2.0.4.0';

Friday, January 8, 2010

Using PLSQL to load images into a BLOB

create or replace directory temp as '/home/oracle';

CREATE OR REPLACE PROCEDURE load_file (dirname VARCHAR2, filename VARCHAR2) IS

src_file BFILE;
dst_file BLOB;

BEGIN
dbms_lob.createtemporary(dst_file,true);
src_file := bfilename(dirname, filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
dbms_lob.loadfromfile(dst_file, src_file, dbms_lob.getlength(src_file));

UPDATE mytable
SET image_data = dst_file
WHERE image_id=741;

dbms_lob.fileclose(src_file);
END;
/

exec load_file('TEMP','testimage.png');