Tuesday, July 30, 2019

How to change java heap size EBS r12

http://madhanappsdba.blogspot.com/2016/01/how-to-change-java-heap-size-in-r12-to.html
Posted by at No comments:

Monday, July 29, 2019

SQL SERVER WAIT TYPES

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-2017#WaitTypes

Posted by at No comments:

How to create SQL SERVER email notification


EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

--AlwaysOn - Data Movement Suspended
EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Suspended',
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

--AlwaysOn - Data Movement Resumed
EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Resumed',
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO


Then create operator
SSMS> object explorer>sql server agent>operators

Posted by at No comments:

Sunday, July 28, 2019

Find longest running query in SQL SERVER

CREATE TABLE T1(Col1 int, Col2 char(3));
GO
BEGIN TRAN
INSERT INTO T1 VALUES (101, 'abc');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T1;
GO
Posted by at No comments:

Saturday, July 27, 2019

How to find stored procedure containing example SQL SERVER

I was running Top Queries Consuming CPU report the top query was
"SELECT "Tbl1002"."Model" "Col1007" FROM "eTrace"."dbo"."T_WIPHeader" "Tbl1002" WITH (NOLOCK) WHERE CONVERT(nvarchar(100),"Tbl1002"."IntSN",0)=@P"

 I first used the actual text in the like of the ROUTINE_DEFINITION so at first it looked like this.

 SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION ,
ROUTINE_SCHEMA
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_TYPE='PROCEDURE' AND
    ROUTINE_DEFINITION LIKE '%SELECT "Tbl1002"."Model" "Col1007" FROM "eTrace"."dbo"."T_WIPHeader" "Tbl1002" WITH (NOLOCK) WHERE CONVERT(nvarchar(100),"Tbl1002"."IntSN",0)=@P%'

I was getting no results and was getting frustrated. After a short nap, I had an idea of breaking down the query something like below.

SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION ,
ROUTINE_SCHEMA
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_TYPE='PROCEDURE' AND
    ROUTINE_DEFINITION LIKE '%nvarchar(100)%' AND
    ROUTINE_DEFINITION like '%T_WIPHeader%'   AND
    ROUTINE_DEFINITION like '%IntSN%' and ROUTINE_DEFINITION like '%convert%'

From there I found out that the stored proc is called sp_WIPOutATE

then I found the line that it was creating it. It was using a dynamic SQL

select @Wipid=Wipid ,@changedon =changedon,@DJ=DJ,@Model=upper(Model),@PCBA=upper(PCBA) ,@currentprocess=upper(CurrentProcess), @wipresult=result from T_wipheader with (nolock) where IntSN=@IntSerialNo

Posted by at No comments:

Wednesday, July 10, 2019

Symantec PGP Command Line Tutorial




Installation


1. Goto https://www.symantec.com/products/command-line
2. Click Free Trial
3. Fillup the information and download
4. You should get a zip file mine was "PGP_CommandLine_10.4.2_MP2.zip"
5.  Unzip then transfer the installer to the system you are going to install. Mine was a redhat
 (Hint: click to zoom)



 To install PGP Command Line into a different directory: 
1 If you have an existing version of PGP Command Line installed on the computer, uninstall it. 
2 Download the installer file called PGPCommandLine[version]Linux.tar to a known location on your system.
3 Untar the package first. You will get the following file: PGPCommandLine[version]Linux.rpm 
4 Type: rpm --prefix=/opt -ivh PGPCommandLine[version]Linux.rpm 
5 Press Enter. This command will install the application binary in the directory /opt/bin/pgp, libraries in /opt/lib, etc. 

You will need to edit the environment variable LD_LIBRARY_PATH to include the new library path for the software to function in any location other than the default.



 pgp --license-authorize --license-number "D4LVN-LH6UG-CUNWP-YLMBP-GDR59-PVA"

 CREATE KEYS 



Example: pgp --gen-key "Skull" --key-type RSA --bits 2048 --passphrase "King Kong"

Example 2: pgp --gen-key "Dexter Velasco <dexter.velasco@arttesyn.com>" --key-type DH --encryption-bits 2048 --signing-bits 2048 --passphrase "AshleyRocks"


 List key
 pgp --list-keys


 How to encrypt ?


/dbapps/applmgr/pgp-10.3.1/bin/pgp -esa $OUTGOING_TOP/mapm/processed/$DATA_FILE.$DATE -r "Bank of Amerika BA-CLEAR" -u EMRNETW10876



To encrypt

-e encrypt
-s sign
-a armor
-r recipient
-u user
--passphrase

Example : pgp -esa open.txt -r "Skull" -u Skull --passphrase "King Kong"

To decrypt

pgp --decrypt open.txt.asc

pgp --decrypt open.txt.asc --passphrase "King Kong"

Theory how to
Export key
Import key





How  to Export Keys



Public key

pgp --export "Dexter Velasco <dexter.velasco@arttesyn.com>" --output DAV

Private key
pgp --export-key-pair "Dexter Velasco <dexter.velasco@arttesyn.com>" --output DA.PRV


Scenario
A message can be encrypted by Skull but decrypted by DAV

  1. pgp -esa missile.txt -r "Dexter Velasco <dexter.velasco@arttesyn.com>" -u Skull --passphrase "King Kong"
  2. pgp --decrypt missile.txt.asc --passphrase "AshleyRocks"

  1. Encrypt missile.txt with Dexter
  2. Delete key Dexter
  3. Import Private key Dexter
  4. Decrypt missile.txt


  1. Import Public key Dexter
  2. Encrypt missile.txt
  3. Decrypt
  4. Import private key dexter
  5. Decrype

Remove key

pgp --remove-key-pair "Dexter Velasco <dexter.velasco@artesyn.com>" --force

How to import key

pgp --import "DAV"  (this contains the public)
 pgp --import "DA.V"

Posted by at 1 comment:

Monday, July 8, 2019

Can't reach this page + EBS

to see if something connects
use   $IAS_ORACLE_HOME/instances/ EBS_web_<instance>_OHS<no>/diagnostics/logs/OHS/EBS_web_<instance> acces_log
Posted by at No comments:

How To Stop Concurrent Managers on EBS

Concurrent manager can be stopped by running 

adcmctl.sh stop  <appsusername/password>

We can check if the Concurrent processes are still running by 

ps -ef |grep FNDLIBR 

If there are processes running the we can manually kill the processes.

kill -9 `ps -ef|grep FNDLIBR|awk '{print $2}'`

or

ps -ef|grep FNDLIBR|awk '{print $2}'|xargs kill -9
Posted by at No comments:

Sunday, July 7, 2019

How to run Autoconfig on EBS

Database Tier:


Execute Autoconfig on the DB Tier. ($ORACLE_HOME/appsutil/bin/adconfig.sh)


Application Tier:

Execute  apollo:/dbapps/applmgr/HRD2/inst/apps/HRD2_apollo/admin/scripts
Posted by at No comments:
ADOP Fails With Error Unable to Find Appltop_id (Doc ID 1907419.1)

 There are duplicates on the table use the following to verify
Query
select node_name from FND_OAM_CONTEXT_FILES
where NAME not in ('TEMPLATE','METADATA','config.txt') and
CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'run'
and EXTRACTVALUE(XMLType(TEXT),'//oa_service_group_status[@oa_var=''s_web_admin_status'']')='enabled'

and EXTRACTVALUE(XMLType(TEXT),'//oa_service_list/oa_service[@type=''admin_server'']/oa_service_status')='enabled';



SQL> conn apps
Enter password:
Connected.
SQL> select node_name from FND_OAM_CONTEXT_FILES
where NAME not in ('TEMPLATE','METADATA','config.txt') and
  2    3  CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'run'
and EXTRACTVALUE(XMLType(TEXT),'//oa_service_group_status[@oa_var=''s_web_admin_status'']')='enabled'
and EXTRACTVALUE(XMLType(TEXT),'//oa_service_list/oa_service[@type=''admin_server'']/oa_service_status')='enabled';  4    5    6

NODE_NAME
------------------------------
rend
rend


SQL>

Posted by at No comments:

SEVERE : Jul 7, 2019 8:11:12 PM - ERROR - CLONE-20275 Insufficient space to create /tmp/CLONINGCLIENT-1225975372382435825.

Running Rapid Clone with command:
Running:
perl /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/bin/adclone.pl java=/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/bin/../jre mode=apply stage=/upgrade/R12_R12T1/fs1/EBSapps/comn/clone component=appsTier method=CUSTOM appctxtg=/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/appl/admin/R12T1_crcdevportal.xml showProgress contextValidated=true



FMW Pre-requisite check log file location : /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/FMW/logs/prereqcheck.log

Running: /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/FMW/t2pjdk/bin/java -classpath /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/engine.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraPrereq.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraPrereqChecks.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraInstaller.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraInstallerNet.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/srvm.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl2.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl-log4j.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/xmlparserv2.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/prereq/webtier/oui/jlib/share.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/java oracle.apps.ad.clone.util.FMWOracleHomePreReqCheck -prereqCheckFMW -e /upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/appl/admin/R12T1_crcdevportal.xml -stage /upgrade/R12_R12T1/fs1/EBSapps/comn/clone -log /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/FMW/logs/prereqcheck.log

Beginning application tier Apply - Sun Jul  7 20:09:44 2019

/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/oui -classpath /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/xmlparserv2.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/ojdbc6.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/java:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/oui/OraInstaller.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/oui/ewt3.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/oui/share.jar:/upgrade/R12_R12T1/fs1/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/ojmisc.jar:/upgrade/R12_R12T1/fs1/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/upgrade/R12_R12T1/fs1/EBSapps/comn/clone/jlib/obfuscatepassword.jar  oracle.apps.ad.clone.ApplyAppsTier -e /upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/appl/admin/R12T1_crcdevportal.xml -stage /upgrade/R12_R12T1/fs1/EBSapps/comn/clone    -showProgress -nopromptmsg
Log file located at /upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone/ApplyAppsTier_07072009.log
  |      0% completed
ERROR while running Apply...
Sun Jul  7 20:11:13 2019

 ERROR: Failed to execute /upgrade/R12_R12T1/fs1/EBSapps/comn/clone/bin/adclone.pl

 Please check logfile.



We then check

crcdevportal:/usr/applmgr> cd /upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log> ls
07072009  clone
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log> cd clone/
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone> cd ..
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log> cd clone/
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone> ls
ApplyAppsTier_07072009.log  fmwT2PApply
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone> cd fmwT2PApply/
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone/fmwT2PApply> ls
CLONE2019-07-07_08-09-46PM.error  CLONE2019-07-07_08-09-46PM.log
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone/fmwT2PApply> cat CLONE2019-07-07_08-09-46PM.error
SEVERE : Jul 7, 2019 8:11:12 PM - ERROR - CLONE-20275   Insufficient space to create /tmp/CLONINGCLIENT-1225975372382435825.
SEVERE : Jul 7, 2019 8:11:12 PM - CAUSE - CLONE-20275   Minimum required space was at least "3,350" MB, but only "3,003" MB was available for use.
SEVERE : Jul 7, 2019 8:11:12 PM - ACTION - CLONE-20275   Make sure that the minimum required space is available for use.
SEVERE : Jul 7, 2019 8:11:12 PM - ERROR - CLONE-20233   Restoring Middleware home for sourceid "mwhome1@FMW_Home" has failed.
SEVERE : Jul 7, 2019 8:11:12 PM - CAUSE - CLONE-20233   An internal operation failed.
SEVERE : Jul 7, 2019 8:11:12 PM - ACTION - CLONE-20233   Check the clone log and error file for more details.
SEVERE : Jul 7, 2019 8:11:12 PM - ERROR - CLONE-20237   Restoring the sourceid "mwhome1@FMW_Home" has failed.
SEVERE : Jul 7, 2019 8:11:12 PM - CAUSE - CLONE-20237   An internal operation failed.
SEVERE : Jul 7, 2019 8:11:12 PM - ACTION - CLONE-20237   Check the clone log and error file for more details.
SEVERE : Jul 7, 2019 8:11:13 PM - ERROR - CLONE-20218   Cloning is not successful.
SEVERE : Jul 7, 2019 8:11:13 PM - CAUSE - CLONE-20218   An internal operation failed.
SEVERE : Jul 7, 2019 8:11:13 PM - ACTION - CLONE-20218   Provide the clone log and error file for investigation.
! crcdevportal:/upgrade/R12_R12T1/fs1/inst/apps/R12T1_crcdevportal/admin/log/clone/fmwT2PApply> cd /tmp



SOLUTION:


CAUSE

The default /tmp directory doesn't have enough free space available to run the copyBinary.sh script and create the temporary files like CLONINGCLIENT202653551162129417.

Customer can't increase the space in the default /tmp directory.
 

SOLUTION

1) Create a new temporary directory in a location of your choice.
  Just make sure that:

  a) Oracle user has the right privileges to write on this directory and,
  b) There is plenty of free space, at least more than 5Gb free.

2) You can create a script to execute the cloning command or set the environment variable at command line.
  For example:
  $ export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/home/Oracle/testcase/tmp"


On my case

export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/upgrade/skullone/tmp"

It works


Posted by at No comments:
Subscribe to: Posts (Atom)