4 Replies Latest reply on Aug 21, 2019 11:35 AM by Maaz Khan

    Reclaiming space from APPS_TS_QUEUES to make it reusable under ASM Diskgroup

    Maaz Khan

      Hi Experts,

       

      Platform - OEL 5.11

      Database - 12.1.0.2

      ERP - 12.1.3

       

      We would like to reclaim unused space from tablespace - APPS_TS_QUEUES. Long story short, customer was not using CRM applications however, ASO_ORDER_FEEDBACK_T queue was filling up for every sales order. This was confirmed by Oracle support and they gave below action plan which was followed successfully-

       

       

      1. Make sure there are no EBS users actively using the instance. Disable user access to the instance by switching the instance to maintenance mode.

      2. Stop ASO queues by running following SQL statements as APPS user

      execute dbms_aqadm.stop_queue('ASO.ASO_OF_Q', TRUE, TRUE);

      execute dbms_aqadm.stop_queue('ASO.ASO_OF_Q_E', FALSE,TRUE);

      execute dbms_aqadm.stop_queue('ASO.ASO_OF_EXCP_Q', TRUE, TRUE);

      execute dbms_aqadm.stop_queue('ASO.ASO_OF_EXCP_Q_E', FALSE,TRUE);

      3. Purge by running following SQL script as APPS user

      DECLARE

      po dbms_aqadm.aq$_purge_options_t;

      BEGIN

      po.block := FALSE;

      DBMS_AQADM.PURGE_QUEUE_TABLE(

      queue_table => 'ASO.ASO_ORDER_FEEDBACK_T',

      purge_condition => NULL,

      purge_options => po);

      END;

      /

      4. Shrink by running following SQL statements as APPS user

      ALTER TABLE ASO.ASO_ORDER_FEEDBACK_T DEALLOCATE UNUSED;

      ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_I DEALLOCATE UNUSED;

      ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_H DEALLOCATE UNUSED;

      ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_T DEALLOCATE UNUSED;

       

      Q) I am assuming I would want to end-date the OZF entry and then purge the table if we were to disable?

      Yes.... please remove the OZF lookup instead of disabling with below instructions

      5. Remove lookup

       

      Login to EBS using "Quoting Sales Manager" responsibility

      Navigation: Quick Codes -> ASO_ORDER_FEEDBACK_CRM_APPS

      Remove OZF lookup code entry

      Save changes

      6. start ASO queues by running following SQL statements as APPS user

      execute dbms_aqadm.start_queue('ASO.ASO_OF_Q', TRUE, TRUE);

      execute dbms_aqadm.start_queue('ASO.ASO_OF_Q_E', FALSE,TRUE);

      execute dbms_aqadm.start_queue('ASO.ASO_OF_EXCP_Q', TRUE, TRUE);

      execute dbms_aqadm.start_queue('ASO.ASO_OF_EXCP_Q_E', FALSE,TRUE);

       

       

      For more details please refer Note How To De-fragment ASO_ORDER_FEEDBACK_T without recreating? ( Doc ID 1612162.1 )

       

       

       

       

      Now we have tablespace APPS_TS_QUEUES having almost 61gb free space and we know this will not be utilized again(we will be keeping 10gb free space and would like to reclaim 50gb to be available for diskgroup and reused by other tablespaces) -

       

      We are using ASM and this is a single node implementation.

       

       

      Regards,

      Maaz

        • 1. Re: Reclaiming space from APPS_TS_QUEUES to make it reusable under ASM Diskgroup
          Kanda-Oracle

          Hi ,

           

          One approach you can test in Test instance is, to move all the objects out of this tablespace to different one. Then move it back to 'pack it tightly'. It involves time.

           

          Thanks!

          • 2. Re: Reclaiming space from APPS_TS_QUEUES to make it reusable under ASM Diskgroup
            Maaz Khan

            Hi Kanda,

             

            Agreeing to your comment that this involves time. I have been researching on this as reclaiming almost 60gb space will significantly reduce overall storage cost of customer and the same can be reused for tablespaces like APPS_TS_TX_DATA or APPS_TS_TX_IDX.

            There are 55 queue tables and moving them will require a different approach. Idea is to move them in same tablespace and then coalesce tablespace to reclaim space.

             

            I'm referring below doc id to move these objects using dbms_redefinition.

            How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

             

            ownerqueue_tabletablespace
            APPLSYSWF_JAVA_ERRORAPPS_TS_QUEUES
            ASOASO_ORDER_FEEDBACK_TAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_Q2_QTBLAPPS_TS_QUEUES
            CSCS_SERVICE_REQUEST_OQTAPPS_TS_QUEUES
            CSCS_SERVICE_REQUEST_IQTAPPS_TS_QUEUES
            APPSCZ_MESSAGE_QENTRIESAPPS_TS_QUEUES
            APPLSYSWF_ERRORAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_MP_QTBLAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_B_QTBLAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_BP_QTBLAPPS_TS_QUEUES
            APPLSYSWF_OUTAPPS_TS_QUEUES
            APPLSYSWF_DEFERREDAPPS_TS_QUEUES
            APPLSYSWF_NOTIFICATION_OUTAPPS_TS_QUEUES
            APPLSYSWF_INAPPS_TS_QUEUES
            APPSJTF_PF_LOGGING_TABLEAPPS_TS_QUEUES
            APPLSYSWF_WS_JMS_INAPPS_TS_QUEUES
            APPLSYSWF_JAVA_DEFERREDAPPS_TS_QUEUES
            APPLSYSWF_WS_JMS_OUTAPPS_TS_QUEUES
            APPLSYSWF_JMS_JMS_OUTAPPS_TS_QUEUES
            APPLSYSWF_WS_SAMPLEAPPS_TS_QUEUES
            APPLSYSWF_INBOUND_TABLEAPPS_TS_QUEUES
            APPLSYSWF_OUTBOUND_TABLEAPPS_TS_QUEUES
            AMVAMV_MATCHING_QUEUE_TBLAPPS_TS_QUEUES
            ARAR_REV_REC_QTAPPS_TS_QUEUES
            JTFJTF_EXCEP_QUEUE_TABLEAPPS_TS_QUEUES
            XDPXDP_ORDER_PROCESSOR_QTABAPPS_TS_QUEUES
            XDPXDP_PENDING_ORDER_QTABAPPS_TS_QUEUES
            CCTCCT_IBME_QUEUE_TBLAPPS_TS_QUEUES
            XNPXNP_OUT_MSG_QTABAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_Q1_QTBLAPPS_TS_QUEUES
            APPLSYSWF_REPLAY_OUTAPPS_TS_QUEUES
            IBUIBU_SUBS_TABLEAPPS_TS_QUEUES
            IEMIEMP_QUEUE_TBLAPPS_TS_QUEUES
            IEMIEMPP_QUEUE_TBLAPPS_TS_QUEUES
            XDPXDP_WF_CHANNEL_QTABAPPS_TS_QUEUES
            XDPXDP_WORKITEM_QTABAPPS_TS_QUEUES
            XNPXNP_IN_TMR_QTABAPPS_TS_QUEUES
            XNPXNP_IN_MSG_QTABAPPS_TS_QUEUES
            XNPXNP_IN_EVT_QTABAPPS_TS_QUEUES
            IEOIEO_ICSM_QUEUE_TBL_2APPS_TS_QUEUES
            IEOIEO_ICSM_QUEUE_TBL_1APPS_TS_QUEUES
            APPLSYSWF_DEFERRED_TABLE_MAPPS_TS_QUEUES
            CCTCCT_QDE_RESP_Q_TBLAPPS_TS_QUEUES
            APPSJTF_IH_BULK_QTBLAPPS_TS_QUEUES
            XDPXDP_FA_QTABAPPS_TS_QUEUES
            JTFJTF_DEF_QUEUE_TABLEAPPS_TS_QUEUES
            JTFJTF_STAGING_QUEUE_TABLEAPPS_TS_QUEUES
            OKCOKC_AQ_EV_TABAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_ER_QTBLAPPS_TS_QUEUES
            APPLSYSWF_REPLAY_INAPPS_TS_QUEUES
            APPLSYSWF_JMS_INAPPS_TS_QUEUES
            APPLSYSWF_JMS_OUTAPPS_TS_QUEUES
            APPLSYSWF_CONTROLAPPS_TS_QUEUES
            APPLSYSWF_NOTIFICATION_INAPPS_TS_QUEUES
            APPSJTF_FM_RAPID_M_QTBLAPPS_TS_QUEUES

             

             

            I would like to know further implications after moving them in same tablespace and set of test-cases to be prepared to make sure thorough testing is done in test environment.

             

            Regards,

            Maaz

            • 3. Re: Reclaiming space from APPS_TS_QUEUES to make it reusable under ASM Diskgroup
              Kanda-Oracle

              Hi

              Thanks! Its good to test in the Test Instance.

               

              I observe , most of the Queues are related to workflow related. To get better visibility..you may post here as well.

                    Core Workflow - EBS (MOSC)

               

              I see no Queue related to OPP (concurrent manager).

               

              Thanks!

              • 4. Re: Reclaiming space from APPS_TS_QUEUES to make it reusable under ASM Diskgroup
                Maaz Khan

                Hi Kanda and All,

                 

                 

                Performed below activity in a test environment -

                1. Filtered only advanced queueing tables under APPS_TS_QUEUES- around 55 tables

                select a.owner, a.queue_table, b.tablespace_name

                from dba_queue_tables a, dba_segments b where a.queue_table = b.segment_name

                and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES' order by tablespace_name;

                 

                2. Workflow related tables have additional steps to be performed, revoking enqueue/dequeue privileges to apps-

                REVOKE PRIVS-

                SQL> EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(    privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');
                SQL> EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(    privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');
                SQL> select * from dba_tab_privs where table_name like '%WF_JMS_OUT%';

                 

                MOVE TABLE-

                SQL> exec move_qt_pkg.move_queue_table('APPLSYS','WF_JMS_OUT','APPS_TS_QUEUES','APPS_TS_QUEUES')

                 

                GRANT PRIVS-

                SQL> EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(    privilege => 'ENQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

                SQL> EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(    privilege => 'DEQUEUE', queue_name => 'APPLSYS.WF_JMS_OUT',     grantee => 'APPS');

                 

                Below query gave us only those tables that needed to be worked on additional steps of revoking and granting privileges -

                select * from dba_tab_privs dbt where privilege in ('ENQUEUE','DEQUEUE') and dbt.owner||'-'||dbt.table_name

                in (

                select a.owner||'-'||a.queue_table

                from dba_queue_tables a, dba_segments b where a.queue_table = b.segment_name

                and a.owner = b.owner and b.tablespace_name='APPS_TS_QUEUES');

                 

                 

                3. Perform coalesce for APPS_TS_QUEUES -

                ALTER TABLESPACE apps_ts_queues COALESCE

                 

                4. Check datafile sizes and resize them accordingly -

                SET PAUSE ON

                SET PAUSE 'Press Return to Continue'

                SET PAGESIZE 60

                SET LINESIZE 300

                COLUMN "Tablespace Name" FORMAT A20

                COLUMN "File Name" FORMAT A80

                 

                SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",

                        Substr(df.file_name,1,80) "File Name",

                        Round(df.bytes/1024/1024,0) "Size (M)",

                        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",

                        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",

                        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"

                FROM    DBA_DATA_FILES DF,

                       (SELECT file_id,

                               sum(bytes) used_bytes

                        FROM dba_extents

                        GROUP by file_id) E,

                       (SELECT sum(bytes) free_bytes,

                               file_id

                        FROM dba_free_space

                        GROUP BY file_id) f

                WHERE    e.file_id = df.file_id

                AND      df.file_id  = f.file_id

                and  df.tablespace_name='APPS_TS_QUEUES'

                ORDER BY df.tablespace_name,

                         df.file_name

                /

                 

                 

                5. Resize datafiles -

                alter DATABASE DATAFILE '+DATA/apps_ts_queues.320.906148009.dbf' resize 100m;

                 

                 

                6. Post checks -

                select count(*) from dba_indexes where status='UNUSABLE';

                select count(*) from dba_objects where status='INVALID';

                 

                 

                 

                7. Few more reference -

                Move Queue Table Issues ORA-00955 (Doc ID 1684231.1)

                How to Move Different Database Objects to Another Tablespace (Doc ID 1915040.1)

                How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

                ORA-00990: Missing Or Invalid Privilege ORA-06512: At "AQMOVE.MOVE_QT_PKG", Line 228 (Doc ID 1677275.1)

                 

                 

                We still have to test the same one more time and then move to production. Thanks for your support.

                 

                Regards,

                Maaz