Enable APEX in Oracle 11.2

cd $ORACLE_HOME/apex
sqlplus / as sysdba


SQL> @apxconf.sql

PORT
----------
8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.


Enter a password for the ADMIN user []
Enter a port for the XDB HTTP listener [ 8080]
...changing HTTP Port

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.


Commit complete.

SQL> alter user anonymous account unlock;

User altered.

# now access by http://server:8080/apex/apex_admin (login admin/password entered above)

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;

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;
/

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;

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

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