Friday, October 29, 2010

Script to detect duplicate file names in different filesystems.

select substr(file_name, instr(file_name,'/',1,5)+1), count(*)
from dba_data_files
group by substr(file_name, instr(file_name,'/',1,5)+1)
having count(*) > 1;

Tuesday, October 12, 2010

Script to increment sequences

A simple script to increment Oracle sequences.

set serveroutput on
declare
x integer;
begin
x:=89796633;
while x < 89796703 loop
select mysequence.nextval into x from dual;
dbms_output.put_line(x);
end loop;
end;
/

Monday, May 10, 2010

Rebuilding unusable indexes, index partitions, and index subpartitions.

select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||';' from dba_ind_subpartitions where status='UNUSABLE';

Diagnosing RAC cluster waits.

We encountered high cluster waits on RAC, and found that it was caused by stats running on the second instance via DBMS_SCHEDULER. Some scripts to help diagnose the issue :-

# Find what programs are connected to what instance :-

select inst_id, service_name, username, program, machine, count(*)
from gv$session
where username = 'MYAPP'
group by inst_id, service_name, username, program,machine;

# find what blocks are cached on each instance in the cluster for a particular segment

select
inst_id,
s.owner owner,
object_name objname,
subobject_name subobjname,
substr(object_type,1,10) objtype,
ts.block_size / 1024 blockkb,
buffer.blocks blocks,
s.blocks totalblocks,
(buffer.blocks * ts.block_size / 1024) memkb,
(buffer.blocks/decode(s.blocks, 0, .001, s.blocks))*100 bufferpercent
from
(select inst_id, o.owner, o.object_name, o.subobject_name,
o.object_type object_type, count(*) blocks
from dba_objects o, gv$bh bh
where o.object_id = bh.objd and o.owner not in ('SYS','SYSTEM')
group by inst_id, o.owner, o.object_name, o.subobject_name, o.object_type) buffer,
dba_segments s,
dba_tablespaces ts
where s.tablespace_name = ts.tablespace_name
and s.owner = buffer.owner
and s.segment_name = buffer.object_name
and s.SEGMENT_TYPE = buffer.object_type
and (s.PARTITION_NAME = buffer.subobject_name or buffer.subobject_name is null)
and s.segment_name = 'MYTABLE'
order by bufferpercent desc;

# Find what scheduler jobs ran on the second instance.

select log_date, job_name from DBA_SCHEDULER_JOB_RUN_DETAILS where instance_id = 2 and log_date > sysdate -7 order by log_date;

Thursday, April 29, 2010

MMON automatically deletes AWR snaps if SYSAUX is full

Interesting find in an alert log indicating that MMON tries to purge old data when SYSAUX is full :-

Thu Apr 29 16:00:03 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2503051407_14753 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (14690) and older
Thu Apr 29 16:00:49 2010
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2503051407_14753 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (14705) and older

Monday, March 22, 2010

Freeing up db_recovery_file_dest reclaimable space.

I temporarily reduced db_recovery_file_dest_size to force the database to free reclaimable space from recovery_file_dest.

This reduced disk usage of ASM_ORADATA217 by over 320GB.


SYS>show parameter recovery

NAME TYPE VALUE
----------------------------------- ------------------------- ---------------------
db_recovery_file_dest string +ASM_ORADATA217
db_recovery_file_dest_size big integer 375G

SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 88.7 83.73 1991


SYS>alter system set db_recovery_file_dest_size=20G;

System altered.

SYS>alter system set db_recovery_file_dest_size=375G;

System altered.

SYS>alter system switch logfile;

System altered.

SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 5.32 .35 135

Tuesday, March 16, 2010

DBMS_SHARED_POOL.PURGE

To clear out a specific cursor from the shared pool.

SYS AS SYSDBA> alter session set events '5614566 trace name context forever';

Session altered.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;

SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
---------------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
SELECT BID, OFFER, PRICE_TIME FROM PRICE_LATEST WHERE INSTRUMENT_ID = :B1
07000001D275CE98 1404435209 1752 1 473 0
1752


SYS AS SYSDBA> exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
Enter value for address: 07000001D275CE98
Enter value for hash_value: 1404435209

PL/SQL procedure successfully completed.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;


no rows selected

Friday, March 12, 2010

DBMS_ROWID

TEST @dummy1> insert into test1 values (1);

1 row created.

TEST @dummy1> insert into test1 values (2);

1 row created.

TEST @dummy1> select rowid as therowid, id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from test1 where id <= 2;

THEROWID ID OBJID FILENUM BLOCKNUM ROWSLOT
------------------ ---------- ---------- ---------- ---------- ----------
AAAM2dAAEAAAABIAAA 1 52637 4 72 0
AAAM2dAAEAAAABIAAB 2 52637 4 72 1


TEST @dummy1> select dbms_rowid.rowid_create(1,52637,4,72,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAM2dAAEAAAABIAAA

TEST @dummy1> select * from test1 where rowid = dbms_rowid.rowid_create(1,52637,4,72,0);

ID
----------
1

Test of parameter NOSYSDBA

# create the password file with nosysdba=n
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=n

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:02:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy>

# create the password file with nosysdba=y
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=y

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:04:33 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

# test password file authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus sys/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Enter user-name:

# test dictionary authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus system/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYSTEM @dummy>

# test local OS authentication as sysdba

[dummy1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:58 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy1>

Monday, March 8, 2010

using DBMS_SESSION.SET_CONTEXT to store variables

> CREATE CONTEXT my_context using my_context_proc;

Context created.

> create or replace procedure my_context_proc as
begin
dbms_session.set_context('MY_CONTEXT', 'APP_USER', 'ROBERT');
end;
/

Procedure created.

> exec my_context_proc

PL/SQL procedure successfully completed.

> select sys_context('MY_CONTEXT','APP_USER') from dual;

SYS_CONTEXT('MY_CONTEXT','APP_USER')
-------------------------------------
ROBERT

Tuesday, March 2, 2010

Duplicate database from active

To duplicate an 11g database from active.

1. create the duplicate pfile, including db_file_name_convert, log_file_name_convert
2. startup nomount on the duplicate
3. add tnsnames.ora entries for target and duplicate on both servers
4. copy password file from target to duplicate
5. use rman to run the duplicate

rman target sys/password@target auxiliary sys/password@auxiliary log dup.log
DUPLICATE TARGET DATABASE
TO duplicate
FROM ACTIVE DATABASE
SKIP TABLESPACE 'MYTABLESPACE'
NOFILENAMECHECK;

Create a Dataguard Broker Configuration

To create a dataguard broker standby configuration for physical standby on RAC, with preferred apply instances.

dgmgrl
connect sys/
remove configuration;
create configuration 'PROD' as primary database IS 'prod' connect identifier is prod;
add database 'stby' as connect identifier is stby maintained as physical;
edit database prod set property logxptmode='ASYNC';
edit database stby set property logxptmode='ASYNC';
edit database prod set property PreferredApplyInstance='prod1';
edit database stby set property PreferredApplyInstance='stby1';
edit database prod set property MaxFailure=15;
edit database stby set property MaxFailure=15;
edit database prod set property ReopenSecs=60;
edit database stby set property ReopenSecs=60;
edit database prod set property LogArchiveMaxProcesses=10;
edit database stby set property LogArchiveMaxProcesses=10;
enable configuration;

Saturday, February 13, 2010

ORA-07445: exception encountered: core dump [ktuGetExtTxnInfo()+00f0] [SIGSEGV] [ADDR:0x6FA] [PC:0x101FCE210] [Invalid permissions for mapped object]

Get the following bug after cloning 11.1.0.7.2 ORACLE_HOME

Sat Feb 13 22:48:58 2010
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x6FA] [PC:0x101FCE210, ktuGetExtTxnInfo()+00f0]
Errors in file /opt/oracle/product/diag/rdbms/pd01dwh/pd01dwh1/trace/pd01dwh1_j000_1142998.trc (incident=90725):
ORA-07445: exception encountered: core dump [ktuGetExtTxnInfo()+00f0] [SIGSEGV] [ADDR:0x6FA] [PC:0x101FCE210] [Invalid permissions for mapped object] []
Incident details in: /opt/oracle/product/diag/rdbms/pd01dwh/pd01dwh1/incident/incdir_90725/pd01dwh1_j000_1142998_i90725.trc

It is bug 9328683 with ORACLE_HOME cloning and 11.1.0.7.2. Fixed by copying library libknlopt.a and relinking oracle.

Tuesday, February 9, 2010

ORA-28378: Wallet not open after setting the Master Key

I hit bug 7235187 today when I tried to set a TDE master key in a wallet created for OID authentication. Seems it will be fixed in 10.2.0.5 / 11.2. Workaround is to have two wallets.

# sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /opt/oracle/product/admin/xxx/wallet)))
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/product/admin/xxx/wallet)))

# Try to create the key
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "password";
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "password"
*
ERROR at line 1:
ORA-28378: Wallet not open after setting the Master Key

Tuesday, February 2, 2010

DBA_FEATURE_USAGE_STATISTICS

# Did some research into view DBA_FEATURE_USAGE_STATISTICS. It is based on the following joins.

create or replace view dba_feature_usage_statistics as
select samp.dbid, fu.name, samp.version, detected_usages, total_samples,
decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'),
NULL, 'FALSE',
to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE',
'FALSE')
currently_used, first_usage_date, last_usage_date, aux_count,
feature_info, last_sample_date, last_sample_period,
sample_interval, mt.description
from wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu,
wri$_dbu_feature_metadata mt
where
samp.dbid = fu.dbid and
samp.version = fu.version and
fu.name = mt.name and
fu.name not like '_DBFUS_TEST%' and /* filter out test features */
bitand(mt.usg_det_method, 4) != 4 /* filter out disabled features */


Tables are initialised by procedure DBMS_FEATURE_REGISTER_ALLFEAT which calls the DBMS_FEATURE_USAGE package.

E.g

/**********************
* Audit Options
**********************/

declare
DBFUS_AUDIT_STR CONSTANT VARCHAR2(1000) :=
'select count(*), NULL, NULL from audit$ where exists ' ||
'(select 1 from v$parameter where name = ''audit_trail'' and ' ||
'upper(value) != ''FALSE'' and upper(value) != ''NONE'')';
begin
dbms_feature_usage.register_db_feature
('Audit Options',
dbms_feature_usage.DBU_INST_ALWAYS_INSTALLED,
NULL,
dbms_feature_usage.DBU_DETECT_BY_SQL,
DBFUS_AUDIT_STR,
'Audit options in use.');
end;

#Looks like feature usage collection is triggered by procedure DBMS_FEATURE_WCR_CAPTURE based on details in wri$_dbu_usage_sample, so updating that table can trigger collection.

update wri$_dbu_usage_sample set last_sample_date = last_sample_date -7, last_sample_date_num = last_sample_date_num - sample_interval
where dbid=1336168478 and version = '10.2.0.4.0';

Monday, February 1, 2010

An open database is not really open ?

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2155464 bytes
Variable Size 637537336 bytes
Database Buffers 1476395008 bytes
Redo Buffers 21798912 bytes
Database mounted.
Database opened.
SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
BEGIN dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE'); END;

*
ERROR at line 1:
ORA-01033: ORACLE initialization or shutdown in progress
ORA-06512: at "SYS.DBMS_ISCHED", line 3598
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2881
ORA-06512: at line 1
Process ID: 6652282
Session ID: 1655 Serial number: 5


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');

PL/SQL procedure successfully completed.

SQL>

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');