prepare phase hangs
Posted by satish on May 11, 2023; 11:10am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/prepare-phase-hangs-tp11865.html
Dear Erman,
Our environment R12.2.5 2 node shared appltop
2 node RAC
adop.log shows:
[EVENT] Checking AD-TXK codelevel.
[PROCEDURE] [START 2023/05/11 15:47:38] Looking for ad/txk codelevel change
AD-TXK codelevel has not changed.
[PROCEDURE] [END 2023/05/11 15:47:38] Looking for ad/txk codelevel change
[EVENT] Checking for pending cleanup actions.
[PROCEDURE] [START 2023/05/11 15:47:38] Checking for pending cleanup actions
[EVENT] No pending cleanup actions found.
[PROCEDURE] [END 2023/05/11 15:47:38] Checking for pending cleanup actions
[EVENT] Validating database is ready to prepare.
[PROCEDURE] [START 2023/05/11 15:47:38] Running database checks
[STATEMENT] Deleting rows from table AD_PATCHES_TABLE
[STATEMENT] Performing database sanity checks.
[STATEMENT] Checking if edition-enabled users exist.
[PROCEDURE] [END 2023/05/11 15:47:38] Running database checks
[EVENT] Generating report for key tablespaces.
[PROCEDURE] [START 2023/05/11 15:47:38] Generating tablespaces report
[PROCEDURE] [START 2023/05/11 15:47:39] Generating report to display the status of important tablespaces. (ie. free space)
[STATEMENT] Report: /u01/UATAPPS/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWTS.sql---->looks like stuck here.
No blocking session
Backend session status:
SQL> select sid,serial#,last_call_et/60,event,ROW_WAIT_OBJ# from gv$session where sql_id='8w2c6js39gc35';
616 48293 180.8333333 db file sequential read -1
SQL> select sql_text from gv$sqltext where sql_id='8w2c6js39gc35' order by piece;
SQL_TEXT
----------------------------------------------------------------
SELECT df.tablespace_name , round((df.total_space/(1024*10
24*1024)),2) total_space , round(((df.total_space - fs.free_sp
ace)/(1024*1024*1024)),2) used_space , round((fs.free_space/(1
024*1024*1024)),2) free_space , round(((df.total_space-fs.free
_space) / df.total_space)*100, 2) PCT_USED FROM ( SELECT tab
lespace_name, SUM(bytes) TOTAL_SPACE FROM dba_data_files
GROUP BY tablespace_name) df , ( SELECT tablespace_name,
SUM(bytes) FREE_SPACE FROM dba_free_space GROUP BY t
ablespace_name) fs WHERE df.tablespace_name = fs.tablespac
e_name(+) and df.tablespace_name in ('SYSTEM', 'APPS_T
S_SEED', 'APPS_TS_TX_DATA', 'APPS_TS_TX_IDX') ORDER BY fs.tables
pace_name
12 rows selected.
and we veried the script,it spools with a file name adzdshowts.out,below we can see it is in 0 size.
No errors in logs:
===================
[appluat@erpuatapp01 erpuatapp01]$ pwd
/u01/UATAPPS/fs_ne/EBSapps/log/adop/118/20230511_1542929
[appluat@erpuatapp01 20230511_154229]$ cd prepare/
[appluat@erpuatapp01 prepare]$ ls -lrt
total 12
drwxr-xr-x 4 appluat dba 3864 May 11 15:46 validate
drwxr-xr-x 6 appluat dba 3864 May 11 15:47 erpuatapp01
drwxr-xr-x 2 appluat dba 3864 May 11 15:48 erpuatapp02
[appluat@erpuatapp01 prepare]$ cd erpuatapp01
[appluat@erpuatapp01 erpuatapp01]$ ls -lrt
total 48
drwxr-xr-x 3 appluat dba 3864 May 11 15:44 TXK_EVAL_prepare_Thu_May_11_15_44_02_2023
drwxr-xr-x 3 appluat dba 3864 May 11 15:44 TXK_EVAL_prepare_Thu_May_11_15_44_56_2023
drwxr-xr-x 3 appluat dba 3864 May 11 15:46 TXK_EVAL_prepare_Thu_May_11_15_46_28_2023
drwxr-xr-x 2 appluat dba 3864 May 11 15:47 TXK_SANITY_filesystemcheck
-rw-r--r-- 1 appluat dba 5237 May 11 15:47 adConfigChanges.log
-rw-r--r-- 1 appluat dba 12122 May 11 15:47 adop.log
-rw-r--r-- 1 appluat dba 0 May 11 15:47 adzdshowts.out-------------------------------------------------->This one
[appluat@erpuatapp01 erpuatapp01]$ pwd
/u01/UATAPPS/fs_ne/EBSapps/log/adop/118/20230511_154229/prepare/erpuatapp01
[appluat@erpuatapp01 erpuatapp01]$
When we run the tablespace query,it runs immediately. Not sure why prepare phase stops at this stage. Any suggestions please? Any connection issue?
Thanks,
Satish