Senthil Rajendran's Blog: June 2020

Tuesday, June 30, 2020

Oracle Enterprise Manager - Find all datafiles which are Auto-Extendable or NOT

Oracle Enterprise Manager- Find all datafiles which are Auto-Extendable or NOT

here is a super simple SQL that can help you to find data files that are no Auto-Extendable in your database farm using Oracle Enterprise Manager SQL.

SQL Data

SELECT

table1.CM_TARGET_NAME ,

table1.FILE_NAME ,

table1.AUTOEXTENSIBLE ,

table1.TABLESPACE_NAME

FROM

CM$MGMT_DB_DATAFILES_ECM table1

WHERE

table1.CM_TARGET_TYPE ='oracle_database'

AND table1.CM_SNAPSHOT_TYPE='oracle_dbconfig'

AND

(

table1.TABLESPACE_NAME IS NOT NULL

OR table1.FILE_NAME IS NOT NULL

)

AND

(

NLS_UPPER(table1.AUTOEXTENSIBLE) = NLS_UPPER('NO')

)

CM_TARGET_NAME	FILE_NAME	AUTOEXTENSIBLE	TABLESPACE_NAME
DEMO1	+DATA/DEMO1/datafile/system.495.788236081	NO	SYSTEM
DEMO1	+DATA/DEMO1/datafile/sysaux.496.788236083	NO	SYSAUX
DEMO1	+DATA/DEMO1/tempfile/temp.498.788236091	NO	TEMP
DEMO2	/u01/oradata/data01/psdefault.dbf	NO	PSDEFAULT
DEMO3	/DEMO3/oradata/data02/pstemp01.dbf	NO	PSTEMP
DEMO3	/DEMO3/oradata/data02/pstemp02.dbf	NO	PSTEMP
DEMO3	/DEMO3/oradata/data02/tde_tbs_temp01.dbf	NO	TDE_TBS_TEMP
DEMO4	/u01/oradata/data02/system03.dbf	NO	SYSTEM
DEMO4	/u01/oradata/data02/xxah_08.dbf	NO	XXAH
DEMO4	/u01/oradata/data03/cists08.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists09.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists10.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists11.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists12.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists13.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists14.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists15.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists33.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists36.dbf	NO	CISTS_01
DEMO4	/u01/oradata/data03/cists37.dbf	NO	CISTS_01
DEMO5	/u01/oradata/data02/temp07.dbf	NO	PSTEMP
DEMO5	/u01/oradata/data02/temp1.dbf	NO	AHGTT
DEMO5	/u01/oradata/data02/temp2.dbf	NO	PSGTT01
DEMO5	/u01/oradata/data02/temp3.dbf	NO	PSTEMP
DEMO5	/u01/oradata/data02/temp4.dbf	NO	TEMP
DEMO5	/u01/oradata/data02/temp5.dbf	NO	PSTEMP
DEMO5	/u01/oradata/data02/temp6.dbf	NO	PSTEMP

PI Graph

SELECT

NLS_UPPER(table1.AUTOEXTENSIBLE),

COUNT(*) CNT

FROM

CM$MGMT_DB_DATAFILES_ECM table1

WHERE

table1.CM_TARGET_TYPE ='oracle_database'

AND table1.CM_SNAPSHOT_TYPE='oracle_dbconfig'

AND

(

table1.TABLESPACE_NAME IS NOT NULL

OR table1.FILE_NAME IS NOT NULL

)

GROUP BY

NLS_UPPER(table1.AUTOEXTENSIBLE)

Hope this helps you to find out the datafiles that are not auto extendable, and now you know what to do 😀

Sunday, June 28, 2020

EBS 12.2 adpreclone appsTier is Hung

Let me start telling a story , "Once upon a time there lived a great ♚EBS 12.2 instance , it was very kind to the DBAs and one fine day adpreclone appsTier got hung for unknown reason" interesting ???

$ perl adpreclone appsTier

....

hung 🙈🙉🙊

[oracle@kingdom <RUN> ]$ pwd

/u01/install/APPS/fs1/inst/apps/KING_kingdom/admin/log/clone

[oracle@kingdom <RUN> ]$ ls -lrt

total 26

drwxr-xr-x. 2 oracle oinstall 4 Jun 26 11:04 wlsT2PStage

drwxr-xr-x. 2 oracle oinstall 2 Jun 26 11:04 txkSetOHSConfig_06261104

drwxr-xr-x. 2 oracle oinstall 4 Jun 26 11:04 ohsT2PStage

-rw-r--r--. 1 oracle oinstall 20445 Jun 26 11:04 StageAppsTier_06261009.log

drwxr-xr-x. 2 oracle oinstall 2 Jun 29 01:17 fmwT2PStage

[oracle@kingdom <RUN> ]$

looking at StageAppsTier_datetime.log the below copyBinary was running

/u01/install/APPS/fs1/FMW_Home/oracle_common/bin/copyBinary.sh -javaHome /u01/install/APPS/fs1/EBSapps/comn/clone/FMW/t2pjdk -al /u01/install/APPS/fs1/EBSapps/comn/clone/FMW/FMW_Home1234.jar -smw /u01/install/APPS/fs1/FMW_Home -ldl /u01/install/APPS/fs1/inst/apps/KING_kingdom/admin/log/clone/fmwT2PStage -invPtrLoc /etc/oraInst.loc -silent true -debug true

getting deeper into fmwT2PStage found the below

FINE : Jun 26, 2020 10:55:25 - CLONE-22065 The statistics of files whose permission will be handled:

FINE : Jun 26, 2020 10:55:25 - CLONE-22066 The number of files with root privilege=0.

FINE : Jun 26, 2020 10:55:25 - CLONE-22067 The number of symbolic link files=12.

...

INFO : Jun 26, 2020 10:55:25 - CLONE-21118 Adding symbolic link files detail to the archive started ...

FINE : Jun 26, 2020 10:55:25 - CLONE-22209 Number of entries in the file, which will contain all valid symbolic link files detail=12.

FINE : Jun 26, 2020 10:55:25 - [FileUtil:deleteDir] /u01/install/APPS/fs1/FMW_Home/webtier/clone/CloningService_OH_All_Simlink_file path does not exist, so it will not be deleted.

FINE : Jun 26, 2020 10:55:25 - [JarUtilArchiveForOracleHome:addAllFileNamesFromSimlinkListToJarStream] Archiving file which contains simlink file information...

FINE : Jun 26, 2020 10:55:25 - CLONE-22061 The archive entry point=oraclehomes/ORACLEHOME_WEBTIER_CLONE20200626101420859-oraclehome2@webtier/clone/.

FINE : Jun 26, 2020 10:55:25 - [JarUtilArchiveForOracleHome:addAllFileNamesFromSimlinkListToJarStream] Archive of file which contains no read access file information completed.

FINE : Jun 26, 2020 10:55:25 - [FileUtil:deleteDir] Deleting /u01/install/APPS/fs1/inst/apps/KING_kingdom/logs/appl/rgf/TXK/CLONINGCLIENT-8495823447040830722/1593186925232_allsimlinkFilePath/CloningService_OH_All_Simlink_file

FINE : Jun 26, 2020 10:55:25 - [FileUtil:deleteDir] Deleting /u01/install/APPS/fs1/inst/apps/KING_kingdom/logs/appl/rgf/TXK/CLONINGCLIENT-8495823447040830722/1593186925232_allsimlinkFilePath

INFO : Jun 26, 2020 10:55:25 - CLONE-21119 Adding symbolic link files detail to the archive finished. Operation complete in "23" milliseconds.

It got stuck with the symbolic link processing. Analyzing the logs found that there are 12 files but there were more files under the FMW_HOME. After checking all the symlinks found few strange links that were created as root user.

[oracle@kingdom <RUN> ]$ ls -lR . | grep ^l

The above command will list down all the symlinks , carefully review them to find the problematic one. So in my case there was a symlink that is created as root. Recreated the symlink as oracle user and there was another broken symlink , deleted it.

Restarted the adpreclone tool