Improvement on JDE environment audit script

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';

9.2 release is GA

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 Objects

User 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:

• Queries - fields and QBE columns you select from a form and add conditions to in order to make the search criteria more specific.
• Grid Formats - a selection of the columns you choose to display, the sequence in which you want them displayed, and how information is displayed in your grid.
• Composite Application Framework - a user interface framework that enables JD Edwards EnterpriseOne applications to integrate with multiple third-party content and applications, as well as other EnterpriseOne applications.
• One View Reports - a Business Intelligence (BI) Publisher data model, a BI Publisher report, the report definition for the One View Report, which is the metadata for the report and resides in JD Edwards EnterpriseOne.
• One View Watchlists - a collection of items that match user-defined criteria and contain information to which users have selected to be alerted.
• EnterpriseOne Pages -the first screen end-users see when they log into EnterpriseOne. They are HTML files and can contain any HTML-enabled functionality, such as interactive process flows, URL links and web pages, company logos, and so on.

My comments: These all exist, but I think that the management has been made more consistent.  See below:

   Object Management Workbench on the Web

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!

  Security

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.

 

Security Features

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:

• Which UDO features are available in EnterpriseOne.
• Who can create UDOs for their own personal use.
• Who can request to publish (or share) UDOs with other users.
• Who can view/use shared UDOs created by other users.
• Who can modify shared UDOs created by other users.

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

2. Secure FTP (SFTP) for Media 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

3. Enhanced Database 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

 

Oracle Product Features

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.

uninstall e1local

 

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

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.

 

Tried

1. Change this file (sqlnet.ora) and set SQLNET.AUTHENTICATION_SERVICES=(NTS).
2. Restart the OEE E1Local instance.

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

 

Tried

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)

Tried

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'

What tools release can I take for my application release?

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

enter lots of table names into data selection for R98403

Remember to create a vbs script to enter all of your table names into R98403 data selection.  This is an AWESOME time saver:

set objShell = wscript.createobject("WScript.Shell")

Do until success = True 
  Success = objshell.AppActivate("List Of Values") 
  'Success = objshell.AppActivate("something.txt - notepad") 
  wscript.sleep 1000 
Loop

wscript.sleep 100 
wscript.echo "Start data pump" 
wscript.sleep 100 
Success = objshell.AppActivate("List Of Values") 
objshell.sendkeys "+{tab}+{tab}"

objshell.sendkeys "F98840~"
objshell.sendkeys "F98800~"
objshell.sendkeys "F98830~"
objshell.sendkeys "F98810~"
objshell.sendkeys "F594218~"
objshell.sendkeys "F590001~"
objshell.sendkeys "F594213~"
objshell.sendkeys "F590120~"
objshell.sendkeys "F594109~"
objshell.sendkeys "F4096~"
objshell.sendkeys "F0901D~"
objshell.sendkeys "F38111~"
objshell.sendkeys "F594111~"
objshell.sendkeys "F594110~"
objshell.sendkeys "F599312~"
objshell.sendkeys "F62UI13~"
objshell.sendkeys "F56108~"
objshell.sendkeys "F1204~"
objshell.sendkeys "F01131~"
objshell.sendkeys "F56107~"
objshell.sendkeys "F03B16~"
objshell.sendkeys "F3403~"
objshell.sendkeys "F6110~"
objshell.sendkeys "F57150~"
objshell.sendkeys "F470561~"
objshell.sendkeys "F00165~"
objshell.sendkeys "F43199~"

I’ve blogged on this previously, there are small changes that you need to make between tools releases.

Run the script

And in 1 second, you have your many values in data selection!

database link dblink without tnsnames.ora on the server

Wow, another day of learning.  I guess that it all makes sense, but if you create a database link from your client, and you reference a tnsentry that you think is going to exist on the server, then it might not.  Remember database links are going to use the servers tnsnames.ora and sqlnet.ora to try and work out where to go for the data.

create database link "jde_findeva" 
connect to JDE 
identified by myp@ss
using 'findeva’;

The link above needs the server tnsnames to know about findeva, however, this could be written with:

CREATE DATABASE LINK jde_findev
connect to JDE
identified by myp@ss
USING '(DESCRIPTION=(SDU=3000)(ADDRESS=(PROTOCOL=TCP)(HOST=myDBServer01)(PORT=1531))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=findeva.mydomain.com)))' ;

Basically, you’ve done a tnsping on a machine that does know about the database and ripped out everything from (DESCRIPTION onwards.  Removed the spaces and used this to create your database link.  Notice that this is also not a public link, make it private.

Mental note–syntax for simple left outer join on oracle

I’m too old school, I use where exists and union, but not enough left outer joins.

select table_name, simd
from all_tables LEFT OUTER JOIN ol910.f9860 ON ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where table_name like 'F98%' and owner = 'PP910';

The example above is great for CNC people (and JDE DBA’s) as it gives you a description of the extremely helpful table names in JDE.  Note that the left outer join helps when there is package build files in the mix – things that are not in object librarian.  Note that this is also fairly timely with another recent post about finding table names or counts – or something.

TABLE_NAME                     SIMD                                                         
------------------------------ ------------------------------------------------------------ 
F98745                         Smart Field Named Mappings                                   
F98750                         Forms Design Aid Text Information                            
F98751                         Forms Design Aid Specification Information                   
F98752                         Forms Design Aid/Software Versions Repository Header Info.   
F98753                         Forms Design Aid/Software Versions Repository Detail Info.   
F98760                         Report Design Aid Text Information                           
F98761                         Report Design Aid Specification Info                         
F98762                         JDEBLC - Behavior Information                                
F98950                         User Overrides Table                                         
F98950D                        User Overrides Language Table                                
F989998                        Java Persistent Object Cross Reference Table                 

This post is really for me to use next time I need to do this.