Orphan ASM File Cleanup Script

Hi all,

So I got asked by a client to perform a checking on ASM for orphan files, as they have some frequent create/drop database on this environment, as being a development env.

Also, lots of databases shared the same data diskgroup, so I had to work this out for all databases and also for possible inexistent databases.

Some basic approaches I raised:

1) Locating uncatalloged files in ASM per database.
– Source: https://oraganism.wordpress.com/2012/09/09/orphaned-files-in-asm/
– This approach assumes the files on ASM uncatalogged to any database are the Orphaned ones. Which is a fair assumption.
– But I understand that files can be catalloged and unmonted, which would brake this approach.

2) Listing files in ASM but not in database (v$datafile, v$datafile_copy, v$controlfile, v$tempfile, v$logfile) by database.
– Source: https://oracledba.blogspot.com/2018/11/orphaned-files-in-asm.html
– This seems a fair assumption. Would need to be ran from each database.
– There is not guarantee if this is working properly or not.
– Not clear also if PDB files are included.
– There is another similar one: https://dbaliveblog.wordpress.com/asm-orphaned-file-identification-script/
– Also this one: https://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

3) MOS: Query That Can Be Used to Find Orphaned Datafiles on a 12c ASM Instance (Doc ID 2228573.1)
– From MOS, seems the most recommended approach.
– Attention point: PDB$SEED may not be shown as per: PDB$SEED Datafiles Not Appear In CDB_DATA_FILES (Doc ID 1940806.1)
— On 12.1.0.2, recommended to use “EXCLUDE_SEED_CDB_VIEW”. To check if it can be done on session level.

I downloaded and ran referred script on MOS Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1) on the environment.
But the results didn’t sound correct.

After a while, I ended up building my own script based on all mentioned approaches and it worked very fine.

After approved I dropped all the listed files, freed a several TBs of space and no database affected. So I’d assume it as correct and would really recommend it for you.

So what did I used:

SQL to Check ASM Space per Database:

set pages 350 timing on
col gname form a10
col dbname form a10
col file_type form a16
break on gname skip 2 on dbname skip 1
compute sum label total_db of gb on dbname
compute sum label total_diskg of gb on gname  
SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
    gname,
    dbname,
    file_type
/

Expected Output:

SQL> @asm_sizebydb

GNAME	   DBNAME     FILE_TYPE 	       MB	  GB	 #FILES
---------- ---------- ---------------- ---------- ---------- ----------
DATAC1	   DATABSE1   CONTROLFILE	     2316	   2	      1
		      DATAFILE		  7620756	7442	     49
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	    82536	  81	     14
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					7525

	   DATABSE2   CONTROLFILE	      492	   0	      1
		      DATAFILE		  3081604	3009	     47
		      ONLINELOG 	      416	   0	      4
		      PARAMETERFILE	       16	   0	      2
		      PASSWORD			0	   0	      2
		      TEMPFILE		    83372	  81	      3
	   **********				  ----------
	   total_db					3090

	   DATABSE3   CONTROLFILE	      588	   1	      1
		      DATAFILE		  1430712	1397	      8
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	   147816	 144	     18
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					1542
[...]

**********					  ----------
total_disk					       76868

SQL To list Orphan files per Database:

SET VERIFY OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000

DECLARE
   cmd   CLOB;
BEGIN
   FOR c IN (SELECT name Diskgroup
               FROM V$ASM_DISKGROUP)
   LOOP
      FOR l
         IN (SELECT 'rm ' || files files
               FROM
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                        (
                                           SYS_CONNECT_BY_PATH (aa.name, '/')
                                        )
                                           files
                                      , aa.reference_index
                                      , b.TYPE
                                   FROM (SELECT file_number
                                              , alias_directory
                                              , name
                                              , reference_index
                                              , parent_index
                                           FROM v$asm_alias) aa
                                      , (SELECT parent_index
                                           FROM (SELECT parent_index
                                                   FROM v$asm_alias
                                                  WHERE     group_number =
                                                               (SELECT group_number
                                                                  FROM v$asm_diskgroup
                                                                 WHERE name =
                                                                          c.Diskgroup)
                                                        AND alias_index = 0)) a
                                      , (SELECT file_number, TYPE
                                           FROM (SELECT file_number, TYPE
                                                   FROM v$asm_file
                                                  WHERE group_number =
                                                           (SELECT group_number
                                                              FROM v$asm_diskgroup
                                                             WHERE name =
                                                                      c.Diskgroup)))
                                        b
                                  WHERE     aa.file_number = b.file_number(+)
                                        AND aa.alias_directory = 'N'
                                        AND b.TYPE IN
                                               ('DATAFILE'
                                              , 'ONLINELOG'
                                              , 'CONTROLFILE'
                                              , 'TEMPFILE')
                             START WITH aa.PARENT_INDEX = a.parent_index
                             CONNECT BY PRIOR aa.reference_index =
                                           aa.parent_index)
                      WHERE SUBSTR
                            (
                               files
                             , INSTR (files, '/', 1, 1)
                             ,   INSTR (files, '/', 1, 2)
                               - INSTR (files, '/', 1, 1)
                               + 1
                            ) =
                               (SELECT dbname
                                  FROM (SELECT    '/'
                                               || UPPER (db_unique_name)
                                               || '/'
                                                  dbname
                                          FROM v$database))
                     MINUS
                     (SELECT UPPER (name) files, 'DATAFILE' TYPE
                        FROM v$datafile
                      UNION ALL
                      SELECT UPPER (name) files, 'TEMPFILE' TYPE
                        FROM v$tempfile
                      UNION ALL
                      SELECT UPPER (name) files, 'CONTROLFILE' TYPE
                        FROM v$controlfile
                       WHERE name LIKE '+' || c.Diskgroup || '%'
                      UNION ALL
                      SELECT UPPER (name), 'CONTROLFILE' TYPE
                        FROM v$datafile_copy
                       WHERE deleted = 'NO'
                      UNION ALL
                      SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
                        FROM v$logfile
                       WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
      LOOP
         DBMS_OUTPUT.put_line (l.files);
      END LOOP;
   END LOOP;
END;
/

Expected Output:

rm +DATA/XPTODB/CONTROLFILE/CURRENT.4928.955985765
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4934.955986589
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4962.955998825
rm +DATA/XPTODB/CONTROLFILE/CURRENT.5063.956480113
rm +DATA/XPTODB/CONTROLFILE/CURRENT.6374.955984145
rm +DATA/XPTODB/CONTROLFILE/CURRENT.7547.955968953
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4936.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4966.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7540.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7574.955984177
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4937.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4967.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7542.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7558.955984177
rm +DATA/XPTODB/DATAFILE/SYSAUX.4935.955986599
rm +DATA/XPTODB/DATAFILE/SYSAUX.4963.955998847
rm +DATA/XPTODB/DATAFILE/SYSAUX.6286.955984161
rm +DATA/XPTODB/DATAFILE/SYSAUX.7544.955968963
rm +DATA/XPTODB/DATAFILE/SYSTEM.4930.955986599
rm +DATA/XPTODB/DATAFILE/SYSTEM.4964.955998847
rm +DATA/XPTODB/DATAFILE/SYSTEM.7536.955968965

To run this for all databases on server (RAC Databases):

export ORAENV_ASK=NO
for DBSID in `ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'` 
do
echo "######" ${DBSID}
export ORACLE_SID=${DBSID}
. oraenv
sqlplus / as sysdba
@script.sql
exit
done

Hope it helps you!

AWS: ALTER SYSTEM and Managing SYS Objects in RDS

I’m very often managing services over EC2 and there are a few actions clients are often getting some issues to perform in RDS. So I decided to list here 5 of them:

Kill sessions:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial,
method => 'IMMEDIATE');
end;
/

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
exec rdsadmin.rdsadmin_util.flush_buffer_cache;

Grant Privileges to SYS Objects

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

 

Hope it Helps!

Restore table statistics using dbms_stats

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida

Oracle: Explicitly Lock a Table

Hello all,
So I client had a process that kept need big chunks locks on a table. Problem is that due application use this lock occasionally failed (lock timeout) and the whole process fail. So how to guarantee this?

Using explicit locks for the table before starting the procedure, with command below:

LOCK TABLE GREPORA.GREP_TABLE IN SHARE MODE;

This lock is released as soon as the session commit or rollback transaction in the current session. Killing session means an implicit rollback, as usual.

The SHARE permits concurrent queries but prohibits updates to the locked table.

Nice right?
Hope it helps you too!

OEM Report: Last 6 month Database Space Usage and Growth

Hello All!
So I had worked in some very useful reports to have in OEM. In next weeks I’ll share some code you may like… 🙂

To create it? Enterprise -> Reports -> Information Reports. There are several nice default reports there you me like.

Now, let’s go to the first Report, as per title:

1. First you Select the Database:

Select_DB.png

2. Then you see the report:

Report3

That’s nice, right?
Here is a report for another database with actual 640GB average growth per month (its expected, once DBSize is over 60TB):

Growth

So, Mat, can you share the queries? Of course:

Continue reading

Oracle Trigger Follows Clause: Simultaneous Ordered Triggers

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So here is something you may not be aware and is really cool:

Follows Clause

Oracle allows more than one trigger to be created for the same timing point, but up to version 11g is not possible to establish the execution order of execution. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.

The example below establish that second_trigger is executed after first_trigger before inserting each row in table_example.

CREATE OR REPLACE TRIGGER second_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
FOLLOWS first_trigger
BEGIN NULL; END;
/

Nice, han?
Cheers!

Regular Expressions with SUBEXPR

Hi All!

Did you know since 11g the REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern?

Also, a new function, REGEXP_COUNT, returns the number of times the search pattern appears in source string.

Below a simple example of each one:

SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual;

REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3)
---------------------------------------------------------
                                                        5

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual;

RE
--
56

SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual;

REGEXP_COUNT('123123123123','123',1,'I')
----------------------------------------
                                       4

Cheers!

Oracle: Create Disabled Triggers

Hey,
I believe you may know about this already, but in case its something new to you: Yes, this is possible since 11g!

Prior to 11g, it was only possible to create triggers in the enabled state, and after creating to disable them. Since 11g they can be explicitly enabled or disabled at creation time using clause ENABLE or DISABLE. As per example:

CREATE OR REPLACE TRIGGER disabled_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
DISABLE
BEGIN NULL; END;
/

If didn’t know it yet. Np! You know it now! 😀

Cheers!

Oracle SQL Join Factorization Transformation (JFT)

Hi All!
Following the line of last week post, let’s talk today about the JFT…

Oracle SQL Join Factorization Transformation (JFT)

The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. Oracle 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.

The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Original Query:

select t1.c1, t2.c2     
    from t1, t2, t3     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2    
union all     
    select t1.c1, t2.c2     
    from t1, t2, t4     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Re-Written Query:

select t1.c1, VW_JF_1.item_2
     from t1, (select t2.c1 item_1, t2.c2 item_2
                    from t2, t3
                    where t2.c2 = t3.c2 and t2.c2 = 2
               union all
                    select t2.c1 item_1, t2.c2 item_2
                    from t2, t4
                    where t2.c3 = t4.c3) VW_JF_1
     where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

Nice, right?
Cheers!

Oracle: “Invisible” Locks!

Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”

By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.

How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.

How to check if this is your case?

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'
4 /

KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
31 07-APR-2018 16:48:53 17705 ACTIVE
48 07-APR-2018 16:48:53 108856 ACTIVE

Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:

SQL> show parameters roll

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;

System altered.

Hope it helps!
Cheers!