I’m not too sure whether anyone out there (listening to this) is going to oracle open world, but I’m lucky enough to be attending. Reach out to me if you’d like a demo of google analytics for JDE or perhaps a look at our enterprise mobility solutions. We could also just talk about how cool 9.2 is until the wee hours of the morning…
I really look forward to see what is out there in the oracle space for the next year. I’ll be sure to blog anything interesting that I learn.
http://e1ovr.myriad-it.com:9704/xmlpserver/io/zip/read-resource-cache 0.112 10-20 00:19:23 0.109 Warning Invalid HTTP response code: 500 Internal Server Error , for Url: http://e1ovr.myriad-it.com:9704/xmlpserver/io/zip/read-resource-cache
http://e1ovr.myriad-it.com:9704/xmlpserver/viewer.jsp? _xpt=1& _xdo=%2F%7Ebip%2Fruntime%2FMqDWksFPrgpvkky7wmj7qxKx95pXQkzn0vG1RJRQQgln79SKL%2F0640f55c252b4603baa1aa3dfd779e3a.xdo _xt=Inventory+Valuation+by+Branch+Plant _xl=en _xf=analyze _xtoken=B06BE2613C8F251D3044D54EB3A10C6A
_xf=analyze&_xtoken=(.+?)'
http.solve("xtoken", "_xf=analyze&_xtoken=(.+?)'", "", false,
Source.Html, 0, EncodeOptions.None)
This is becoming a little frustrating.
Because weblogic redeploys the application after EVERY restart from the owl_deployment directories under jde_home – if you have any directories locked or explorer open on any of these directories accidently – the application cannot redeploy and will not start.
If you goto http://servername:7001/console and look at the application, you’ll see that it’s running!
You’ll see that it’s running in WLS, but JDE states it’s not.
Remove all of the locks on the runtime dirs, oracle\middleware\user_projects…
Then stop application from wls console
Then start from SM
This is for me when I need to do this again in a week…
I keep forgetting
c:\windows\syswow64\wscript.exe //e:vbscript d:\myriad\scripts\CheckForCorruptVersionsInProd.vbs
The contents of the script:
The script is totally RAD, it does a query (nice), but also emails results and uses some basic password encryption for prying eyes.
'Username, password and URL settings
'Modify what is required to get this running, username, password (create read only innocuous users)
FromEmailAddress="jde@au.myriad.com"
Const strSmartHost = "smtpserver.com.au"
Company="myriad"
RecipientNames = ARRAY("smoir@au.myriad.com", "shannon.moir@myriad-it.com")
'
'The password below has been encrypted with a top secret encryption key
'If the JDE password changes, then you'll need to reencrypt the new password
'This can be done by calling EncryptPasswordToFile fCrypt("newPAsswordText","Something99"),"passwordFile"
EncryptedPassword="ThingsGoHere"
PasswordString="Password=" & fCrypt(EncryptedPassword, "Something99") & ";"
'EncryptPasswordToFile encrypted,"passwordFile"
Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset
set recset = wscript.createobject("ADODB.Recordset")
Dim cmd
set cmd = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=e1sys;" & _
"User ID=jde;" & _
PasswordString
Oracon.Open
Set cmd.ActiveConnection = Oracon
'cmd.CommandText = "Select count(*) from dvfindta.f0101"
cmd.CommandText = "SELECT vrpid, vrvers, vrjd, vrved FROM pd910.f983051 where vrvers not like 'XJDE%' and vrvers not like 'ZJDE%' and vrpodata is null and vrdstnm > ' '"
Set recset = cmd.Execute
i=0
'Note that often this is not supported by the DB, so... Need to use a variable
if recset.recordcount = -1 then
counter=500
else
counter=recset.recordcount
end if
emailBody=chr(10) & chr(13) & "*** This Script contains a list of versions that should not have NULL PO's (500 max) ***" & chr(10) & chr(13)
emailBody=emailBody & encrypted & chr(10) & chr(13)
emailBody=emailBody & cmd.CommandText & chr(10) & chr(13)
emailBody = emailBody & "vrpid, vrvers, vrjd, vrved" & chr(10) & chr(13)
while ((i < counter) and (recset.EOF <> true))
for each field in recset.fields
tuple=tuple & """" & field.value & ""","
next
i=i+1
recset.MoveNext
emailBody=emailBody & chr(10) & chr(13) & tuple
tuple=" "
wend
'wscript.echo emailBody
i=sendMail("WeeklySummary of NULL POs", emailBody,"")
set recset = nothing
set cmd = nothing
set oracon = nothing
'#####################################################################
'FUNCTION:sendMail
'#####################################################################
function sendMail(subjectText, emailBody, attachment)
on error resume next
Dim iMsg
Dim iConf
Dim Flds
Const cdoSendUsingPort = 2
for each name in RecipientNames
'Create the message object
Set iMsg = CreateObject("CDO.Message")
'Create the configuration object
Set iConf = iMsg.Configuration
'Set the fields of the configuration object to send using SMTP via port 25.
With iConf.Fields
.item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmartHost
.Update
End With
'Set the message to,from,subject,body properties.
if strlen(attachment) > 1 then
With iMsg
.AddAttachment attachment
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = chr(1) & " " & emailBody
.Send
End With
else
With iMsg
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = emailBody
.Send
End With
end if
set iMsg = Nothing
next
end function
Function fCrypt(sPlainText, sPassword)
'This function will encrypt or decrypt a string using the RSA's RC4 algorithm.
Dim aBox(255), aKey(255), sTemp, a, b, c, i, j, k, iCipherBy, sTempswap, iLength, sO
i = 0:j = 0:b = 0
iLength = Len(sPassword)
For a = 0 To 255
aKey(a) = Asc(Mid(sPassword, (a Mod iLength)+1, 1))
aBox(a) = a
Next
For a = 0 To 255
b = (b + aBox(a) + aKey(a)) Mod 256
sTempswap = aBox(a)
aBox(a) = aBox(b)
aBox(b) = sTempswap
Next
For c = 1 To Len(sPlainText)
i = (i + 1) Mod 256
j = (j + aBox(i)) Mod 256
sTemp = aBox(i)
aBox(i) = aBox(j)
aBox(j) = sTemp
k = aBox((aBox(i) + aBox(j)) Mod 256)
iCipherBy = Asc(Mid(sPlainText, c, 1)) Xor k
sO = sO & Chr(iCipherBy)
Next
fCrypt = sO
End Function
Function EncryptPasswordToFile(szPassword, filename)
Set wshShell = CreateObject( "WScript.Shell" )
tmpDirectory = wshShell.ExpandEnvironmentStrings( "%TMP%" )
set wshShell = Nothing
Const ForAppending = 8
logfile=tmpDirectory & "\" & filename & ".txt"
'Tempates
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)
objTextFile.WriteLine(szPassword)
objTextFile.Close
end function
Quick way to duplicate a pathcode:
Central objects copy, takes about 40 mins. Honestly, forget import and export and security and indexes and constraints… Just do this:
select 'TRUNCATE TABLE PP910.' || table_name || ';' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;
select 'INSERT INTO PP910.' || table_name || ' SELECT * FROM PD910.' || table_name || '@jde_e1sys ;' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;
update pp910.f983051 set vrenhv = 'PP910' ;
Fix object librarian
create table jde.f9861srm as select * from ol910.f9861 where sipathcd = 'PD910' ;
delete from ol910.f9861 where sipathcd = 'PP910' ;
commit;
update jde.f9861srm set sipathcd = 'PP910' ;
insert into ol910.f9861 select * from jde.f9861srm ;
commit;
Full build and deploy.
I’ve put a number of iterations of this on my blog, but hopefully they keep getting better. I’m using the current one to devise a script that keeps a couple of “Systems” in sync. On a nightly basis I want to replicate a number of data changes from one system to the other.
This uses a left outer join, so that you get all of the JDE tables that are in OL910, but also all of the copies and full package table names – which is really handy.
I use this to identify the tables that have data and then use this for my synchronisation logic.
DROP TABLE SY910.DATACOUNTS ;
cREATE TABLE "SY910"."DATACOUNTS"
( "TABLE_NAME" NVARCHAR2(20),
"TABLE_OWNER" NVARCHAR2(20),
"ORACLE_COUNT_E1SYS" NUMBER,
"ORACLE_COUNT_E1SYS2" NUMBER,
"TABLE_DESCRIPTION" NVARCHAR2(256)
)
TABLESPACE "SY910T" ;
--I use the below statement to get me a likely list of JDE owners that have JDE tables. This is using the F% criteria, note that it essentially builds the “IN” clause for you.
SELECT '''' || OWNER || ''',', count(1) FROM ALL_TABLES WHERE TABLE_NAME LIKE 'F%' group by owner;
grant all on SY910.DATACOUNTS to public ;
delete from sy910.datacounts ;
insert into SY910.datacounts select table_name,owner,0,0,simd
from all_tables left outer join ol910.f9860 on ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where owner in ('SVMDV01',
'JDE',
'SVMAP01',
'PD910',
'DD910',
'OL910',
'SVMAP02',
'SY910',
'DVDD910');
commit;
select 'update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts
where table_owner in ('SVMDV01',
'JDE',
'SVMAP01',
'PD910',
'DD910',
'OL910',
'SVMAP02',
'SY910',
'DVDD910')
order by table_name desc ;
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.SHAE ) WHERE table_name = 'SHAE' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F99410 ) WHERE table_name = 'F99410' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F98TMPL ) WHERE table_name = 'F98TMPL' and table_owner = 'SY910';
The next major release of JD Edwards is GA
Oct. 6, 2015: JD Edwards EnterpriseOne Announces Release of Applications and Tools Release 9.2!
Please see the announcement here.
The Oracle Support Document 2062671.1 (JD Edwards EnterpriseOne 9.2 Certifications) can be found here.
The Oracle Support Document 2054020.1 (Required Components for JD Edwards EnterpriseOne Tools 9.2) can be found here.
NOTE: Tools Release Maintenance Pack 9.2.0.1 is the first Tools Release for JD Edwards EnterpriseOne Application and Tools Release 9.2.
Cutting through the marketing, go here
https://apexapps.oracle.com/pls/apex/f?p=44785:141:0::::P141_PAGE_ID,P141_SECTION_ID:343,2576
So you get the following snazzy screen:
The tools enhancements are categorised under 4 main headings:
User Defined ObjectsUser Defined Objects (UDO) are web objects created using the JD Edwards EnterpriseOne web interface. You can create UDOs for your own use, view UDOs others have created, or share your UDOs so others can view and use them. Your ability to perform any of these tasks depends on the permissions you and others have been granted by your administrator.
There are six UDOs that you create from the EnterpriseOne interface:
My comments: These all exist, but I think that the management has been made more consistent. See below:
Object Management Workbench (OMW) is a system used to manage the development of JD Edwards EnterpriseOne objects on the development client. Available with Tools 9.2 is the new Web OMW which is available on the web client and is used to manage user defined objects (UDO). UDOs are objects created from the web client. For a list of these objects, look above
Both OMW systems are defined using the Object Management Configuration application.
This is totally awesome, having a consistent management framework for promoting these objects is great. Please include things like security in the next release… Please!
Oracle's JD Edwards EnterpriseOne Tools provides security applications, reports, and features to help you protect your company's sensitive application data. EnterpriseOne authentication security ensures that only authenticated users can sign in to EnterpriseOne. Authorization security ensures that EnterpriseOne users have access to only the applications and features that they are authorized to use.
In addition, EnterpriseOne enables you to set up security for developers who use Object Management Workbench (OMW) to add and modify objects for custom applications. Setting up developer security ensures that developers can only perform certain actions in OMW based on pre-defined responsibilities.
EnterpriseOne also includes reports that you can use for security auditing purposes, as well as auditing features for supporting the 21 CFR Part 11 auditing regulations.
With Tools Release 9.2, JD Edwards has removed the 10 character restriction for user ID and password to support more robust security authentication processes. This enhancement enables security officers to define complex password rules that align with their security and compliance requirements. User IDs have also been expanded to improve the end-user experience when signing into JD Edwards.
WOW – this is awesome. Not so great for myLunch – but great!!!
The following content describes changes to security implemented with Tools Release 9.2.
1. User Defined Object Security
Tools 9.1: Not applicable
Tools 9.2: Administrators can enable or disable the user defined object (UDO) features, as well as set up security for viewing, creating, and modifying individual UDOs.
EnterpriseOne provides UDO security features in the Security Workbench to enable administrators to control:
Action: Enable access to the user defined object security and administration applications, then set up security for user defined object features and user defined objects.
Documentation: JD Edwards EnterpriseOne Security Administration Guide
Managing Security for User Defined Objects
Tools 9.1: Only non-secure FTP is available.
Tools 9.2: Secure FTP (SFTP) for media object access on the HTML Server and Business Services Server is configurable using Server Manager.
Action: Oracle recommends using SSH file transfer protocol, otherwise referred to as Secure FTP (SFTP), for accessing media objects as a more secure alternative to FTP. When EnterpriseOne is configured to use SFTP for media objects, users can securely upload, download, and delete media objects.
Documentation: JD Edwards EnterpriseOne Security Administration Guide (for HTML Server)
JD Edwards EnterpriseOne HTML Server Security
Business Services Server Security
Tools 9.1: All tables are created and shipped with PUBLIC ALL authority.
Tools 9.2: For an installation, all tables delivered by the Platform Pack installer are locked down.
Action: To enable you to maintain the security on the tables, you must set up the security definition using P986117 (signed into JDEPLAN) before running the Installation Workbench. Any new tables that the Workbenches create (for example the TC Workbench or the Environment Workbench) will be created according to the rules in F986117.
Documentation: JD Edwards EnterpriseOne Security Administration Guide
Managing Data Source Security
Access the Oracle Product Features tool using your Oracle Technology Network login to explore a comprehensive list of features and enhancements introduced with each release of the JD Edwards EnterpriseOne and JD Edwards World products. View information by release or product area. Use the Compare Releases option to review product changes introduced since your last update.
okay, I’m having a mare with the e1local database on a fat client. Things should not take this long.
Sure, I know it’s my fault – hitting shift delete on the c:\E910 dir after stopping the local oracle database. I know I should not have done it…
Tried using reconfigureMSDEDatabase to get the password – NO
Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...
ERROR: Could not change the SYSTEM user's password. The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details. Will not attempt to change the SYS user's password.
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...
ERROR: Could not change the SYSTEM user's password. The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details. Will not attempt to change the SYS user's password.
Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...
ERROR: Could not change the SYSTEM user's password. The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details. Will not attempt to change the SYS user's password.
No – nothing
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 6 17:11:30 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Changing the path so that e1local was in front of my local client install – NO
tnsping also told me everything was right:
C:\Windows\system32>tnsping e1local
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-OCT-2015 17:13:34
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
c:\Oracle\E1Local\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = E1Local)))
OK (10 msec)
Tied of tried to be honest…
Went to delete and install the local engine – look how helpful OUI is! it’s funny!
Tells me to run a command at the command line.
C:\Oracle\E1Local\deinstall\deinstall.bat
Which runs
reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_E1Local /f
@echo off
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem deinstall.bat - script that calls deinstall_E1Local.bat.
Rem
Rem DESCRIPTION
Rem This script calls the deinstall_db.bat script, passing in the
Rem correct parameters.
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem mchaney 04/04/06 - Created Windows version.
Rem
set ORACLE_HOME=%~dp0\..
net stop OracleE1LocalTNSListener
net stop OracleMTSRecoveryService
net stop OracleServiceE1LOCAL
%ORACLE_HOME%\deinstall\deinstall_E1Local.bat -silent -paramfile %ORACLE_HOME%\deinstall\response\deinstall_E1Local.rsp
Then OF COURSE:
C:\Oracle\E1Local\deinstall>reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_
E1Local /f
The operation completed successfully.
The OracleE1LocalTNSListener service is stopping.
The OracleE1LocalTNSListener service was stopped successfully.
The OracleMTSRecoveryService service is stopping.
The OracleMTSRecoveryService service was stopped successfully.
The OracleServiceE1LOCAL service is stopping...
The OracleServiceE1LOCAL service was stopped successfully.
Checking for required files and bootstrapping ...
Please wait ...
ERROR: Cannot find the file bin\AgentStatus.pm in the ORACLE_HOME c:\Oracle\E1Lo
cal. You must download the standalone version of the deinstall tool from OTN to
deconfigure/deinstall this Oracle Home.
cannot remove directory for C:\Users\smoir\AppData\Local\Temp\OraDeinstall2015-0
6-10_17-18-54: Permission denied at c:\Oracle\E1Local\deinstall\bootstrap.pl lin
e 179
1 file(s) copied.
1 file(s) copied.
The system cannot find the path specified.
1 dir(s) moved.
Will this ever end… shift delete is coming out again!
Remember that if you are having issues with local oracle, logs are:
C:\Oracle\E1Local\database\oradim.log
Mon Oct 05 12:44:20 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 12:44:28 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'
Mon Oct 05 13:14:38 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 13:14:57 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'
I always get confused on what tools release you can take based upon your current application release, so this table is great. This information changes all of the time, so probably best you go to https://support.oracle.com for the gospel.
| JDEdwards | OneWorld | EnterpriseOne Application Releases | |||||
|---|---|---|---|---|---|---|---|
| Application Release | XE/ERP8 | 8.10 | 8.11 | 8.11 SP1 | 8.12 | 9.0 | 9.1 |
| Compatible Tools Release | SP23/24 | X | X | X | X | X | X |
| 8.93 | X | X | X | X | X | ||
| 8.94 | 8.94 | X | X | X | X | ||
| 8.95 | 8.95 | 8.95 | X | X | X | ||
| 8.9 | 8.96 | 8.96 | 8.96 | X | X | ||
| 8.97 | 8.97 | 8.97 | 8.97 | X | X | ||
| 8.98 | 8.98 | 8.98 | 8.98 | 8.98 | X | ||
| 8.98.1 | 8.98.1 | 8.98.1 | 8.98.1 | 8.98.1 | X | ||
| X | 8.98.2 | 8.98.2 | 8.98.2 | 8.98.2 | X | ||
| X | 8.98.3 | 8.98.3 | 8.98.3 | 8.98.3 | X | ||
| X | 8.98.4 (8.98.4.11 or earlier) | 8.98.4 (8.98.4.11 or earlier) | 8.98.4 (8.98.4.14 Terminal Release) | 8.98.4 (8.98.4.14 Terminal Release) | X | ||
| X | X | X | X | 9.1.0.0 | 9.1.0.0 | ||
| X | X | X | X | 9.1.2.0 | 9.1.2.0 | ||
| X | X | X | X | 9.1.3.0 | 9.1.3.0 | ||
| X | X | X | X | 9.1.4.0 | 9.1.4.0 | ||
| X | X | X | X | 9.1.5.0 | 9.1.5.0 | ||
