Oracle Apps DBA stuff

The purpose of this article is to explain how to extract Hyperion Financial Management (HFM) server messages from the database either to assist Oracle Support with working on a Service request or simply to archive entries.

HFM application servers write messages to 3 locations: Windows server Application Event log, database table named HFM_Errorlog and to a text file named HsvEventlog.log located “%Hyperion_Home%\FinancialManagement\Server Working Folder” for all versions prior to 11.1.1.0.00 and %Hyperion_home%\logs\hfm in versions 11.1.1.0.00 through 11.1.1.3.00.

The application event log and HsvEventlog.log files will contain messages specific to that HFM application server while the HFM_Errorlog entries will contain messages from all HFM application servers that access the same schema. In general, the only messages that are not contained in the HFM_Errorlog table will be database connection related messages and it is this reason that Oracle Support will usually request extracts from the database anytime the HFM implementation has more than 1 HFM application server and database connection issues are not suspected.

DETAILS

Tool Location:

The utility is located in the “%Hyperion_Home%\FinancialManagement\Consultant Utilities” directory on the HFM application server as well as HFM client installations where the Consultant utilities optional component was selected during installation.

Extracting the Database entries into the log viewer:

When the utility is launched, the initial messages are populated with HFM messages parsed from the server’s application event log. Although it is not necessary to view the database messages before extracting, messages from the database can be viewed by selecting “Open an Error log from a database” from the File menu. To extract entries from the Database, select “Extract Database Entries to File(s)” from the Tools menu.

A new window is opened with multiple options that can be selected. The HFM Database UDL file should be selected. Options to control what entries will be extracted can be checked as needed.

Extracting to a Single file – This option generates 1 output file containing all entries in HFM_Errolog table.

Extracting into Multiple files - This option takes the following modifiers:

Split Files by Date – All entries in the HFM_Errorlog table will be extracted and the entries in the output files can be grouped by:
- Day
- Month – This is generally the preferred option when providing logs to Oracle support. Providing logs covering the last 2 month will generally be sufficient.
- Year
Split by Size – All entries in HFM_Errorlog table will be extracted and output size limits can be specified.

Extract for a single HFM application – Drop down list allows for extracting all entries specific to an application.

Extract for a specific data range – Allows for limiting entries extracted by date.

Output File Format - Four options of selecting the output file has been defined.

HFM Log File Format – This is default and preferred output file format when working with Oracle Support.
Comma-Delimited CSV Format
Semi-Colon Delimited CSV Format
CSV Format Custom Delimiter

Output folder - Select a location with sufficient space to hold the output files.

Output Filename or Prefix – Default prefix name can be specified or use the default.

As the title indicates, this article demonstrates the extraction of the Error Log entries from the hosting Database. Similarly, the Error log entries can be extracted from the HsvEventlog file or the application event log file

Step 1 - Clear All Credentials

Step 2 - Re-establish Bootstrap Credentials

Step 3 - Prepare Scripts for Setting Additional Passwords

Step 4 - Assign New Passwords to All Schemas Not Managed with EBS

Step 5 - Assign New Passwords to All Schemas Managed with EBS

Additional Steps

Running AutoConfig

References

APPLIES TO:

Oracle Applications Manager - Version 11.5.9 to 12 [Release 11.5 to 1.2]
Oracle Application Object Library - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

ABSTRACT

When cloning a Production database in Oracle E-Business Suite (EBS) it is a best practice to remove all Production account credentials in the cloned copy of the database. This will help to prevent retrieval of Production credentials, which could be used to compromise the security and integrity of the Production database.

It is ideal to complete this process as soon as possible after the database data files have been copied. At a minimum it should be completed before the database is turned over to any party less trusted than the Production database DBA team.

This document describes the steps required to remove the Production EBS database credentials, such as database user (schema) password hashes and encrypted passwords. Additionally information is provided about how to reestablish credentials in the cloned copy so that the clone may be used for functional, performance or patch application testing.

Steps from this paper should be incorporated into your database cloning process and procedures.

HISTORY

Author :
Create Date 14-Mar-2007
Update Date 11-JUL-2011
Expire Date

DETAILS

The steps outlined in this White Paper will:

Help to ensure that Production credentials are not retrievable from a cloned copy of an EBS Production database.
Boot strap the cloned copy with enough "clone credentials" that it may be used for testing.

The steps in this document should be integrated in your database cloning process, see the "Reference" section below for documentation on cloning EBS systems for Releases 11i and 12.

The following sequence of steps will remove production account credentials from the cloned copy of the production database and reestablish new credentials in the cloned copy. All of the new accounts on the clone target will have the password "clone".

Steps 1 through 4 are run on the database server running as the Operating System user, "oracle", using "sqlplus" connected as the "SYS" or "APPS" database user. Step 5 is run as the Operating System user "applmgr" on an application tier and uses the "FNDCPASS" command line utility. This means that steps 1 through 4 can be performed the first time the cloned database is started, i.e. before it is made accessible to the network via the database TNS listener. Step 5 is not time critical and can be performed when access to the cloned system for patch purposes is required.

All application tier processes must be stopped during this procedure.

Step 1 - Clear All Credentials

To clear all credentials on a target clone of a production database you must establish a shell environment with sufficient Oracle environment variables to successfully start "sqlplus" via the "BEQ" (bequeth) driver. If Rapid Clone has been completed successfully, then each Oracle Home should have a .env file. However, in the event you need to set the environment manually, here are the minimal environment settings:

$ export ORACLE_SID=
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin
$ unset TWO_TASK

oracle$ sqlplus '/ as sysdba'

To clear all credentials in the cloned copy of a Production database, create and execute the following 3 SQL scripts:

REM --- step1.sql

spool step1.lst

REM Start the database clone for the first time

startup restrict

REM Clear all production credentials from the cloned database

update SYS.user$ set

password = translate(password,'0123456789ABCDEF','0000000000000000')

where type#=1 and length(password) = 16

update APPLSYS.FND_ORACLE_USERID set

ENCRYPTED_ORACLE_PASSWORD='INVALID'

update APPLSYS.FND_USER set

ENCRYPTED_FOUNDATION_PASSWORD='INVALID',

ENCRYPTED_USER_PASSWORD='INVALID'

commit;

REM Shutdown the database

shutdown

exit

REM end of script

At this point, the cloned copy of the database is free from Production credentials. The database was shut down by the script in order for the unusual way of clearing the database user (schema) passwords to take effect. You will need to restart the cloned copy of the database in preparation for steps 2, 3 and 4:

oracle$ echo startup | sqlplus '/ as sysdba'

Step 2 - Re-establish Bootstrap Credentials

The database at the moment has no credentials. Now log on as "SYS" with operation system authentication. This will allow you to establish new credentials.

oracle$ sqlplus '/ as sysdba'

Here is the script for step 2, including inline comments which explains what is done.

REM --- step2.sql

spool step2.lst

REM Set a new password for a few initial database users

alter user SYS identified by CLONE;

alter user SYSTEM identified by CLONE;

alter user APPLSYSPUB identified by CLONE;

alter user APPLSYS identified by CLONE;

alter user APPS identified by CLONE;

REM Provide boot-strap info for FNDCPASS...

update APPLSYS.FND_ORACLE_USERID set

ENCRYPTED_ORACLE_PASSWORD='CLONE'

where ORACLE_USERNAME = 'APPLSYSPUB'

update APPLSYS.FND_ORACLE_USERID set

ENCRYPTED_ORACLE_PASSWORD='ZG' ||

'B27F16B88242CE980EF07605EF528F9391899B09552FD89FD' ||

'FF43E4DDFCE3972322A41FBB4DDC26DDA46A446582307D412'

where ORACLE_USERNAME = 'APPLSYS'

update APPLSYS.FND_ORACLE_USERID set

ENCRYPTED_ORACLE_PASSWORD='ZG' ||

'6CC0BB082FF7E0078859960E852F8D123C487C024C825C0F9' ||

'B1D0863422026EA41A6B2B5702E2299B4AC19E6C1C23333F0'

where ORACLE_USERNAME = 'APPS'

commit;

REM We run as SYS, now connect as APPS to run some plsql

connect APPS/CLONE

REM Every EBS database needs a GUEST user

select APPS.fnd_web_sec.change_guest_password( 'CLONE', 'CLONE' ) "RES"

from dual;

commit;

REM Set GUEST credential in site level profile option

set serveroutput on

declare

dummy boolean;

begin

dummy := APPS.FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/CLONE', 'SITE');

if not dummy then

dbms_output.put_line( 'Error setting GUEST_USER_PWD profile' );

end if;

end;

commit;

REM One more time for luck (avoid session caching of profiles)

connect APPS/CLONE

REM Set SYSADMIN password

select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES"

from dual;

commit;

exit

The expected output from step 2 is as follows:

User altered.

1 row updated.

Commit complete.

Connected.

RES

------

Commit complete.

PL/SQL procedure successfully completed.

Commit complete.

Connected.

RES

------

Commit complete.

It is important to verify that no errors are reported and that the 2 returned "RES" values are both "Y", which indicates success.

ATTENTION :

It has been identified, that some Customers running into an error for the SQL PLus command

select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES" from dual;

In this case, please check Note 1350776.1 for the solution, before your are going ahead with the next steps !

Now we have completed establishing a set of bootstrap EBS credentials in the database.

Step 3 - Prepare Scripts for Setting Additional Passwords

In this step scripts are prepared to assign passwords to the other database users which were disabled in Step 1. Dynamically generated scripts are used to accomplish this because the set of database users may differ between instances of EBS. Create the script below and run it as the Operating System user "oracle":

$ sqlplus '/ as sysdba'

The comments in script below explains what is done in step 3.

REM --- step3.sql

REM Prepare SQL and SHELL scripts to set more passwords later

spool step3.lst

REM Generate a sql script to set password for db users not managed with EBS

select 'alter user "'|| USERNAME ||'" identified by CLONE; '

from SYS.DBA_USERS

where USERNAME not in (select ORACLE_USERNAME from APPLSYS.FND_ORACLE_USERID)

and USERNAME not in ('SYS','SYSTEM');

REM Generate a shell script to set password for all base product schemas

select 'FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone' from dual;

REM Generate a shell script to set password for non-EBS db users managed with EBS

select 'FNDCPASS apps/clone 0 Y system/clone ORACLE "' ||

replace(ORACLE_USERNAME,'$','\$') || '" clone'

from APPLSYS.FND_ORACLE_USERID

where READ_ONLY_FLAG = 'X'

and ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS);

REM Generate a shell script to set password for APPS/APPLSYS/APPM_mrc db users

select 'FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone' from dual;

REM Generate scripts for steps 4 & 5

spool off

HOST grep '^alter user ' step3.lst > dbusers4.sql

HOST grep '^FNDCPASS ' step3.lst > dbusers5.sh

exit

REM End of Script

NOTE: The script above calls the UNIX command "grep" to extract 2 sets of lines from the step3.lst spool file. If you are running Windows, the shell redirection will fail when attempted from within sqlplus. You can perform the failed step by going to a command prompt (using the HOST command from sqlplus). If you have your MKS environment set, then you can use the "grep" syntax or alternatively you can use the below syntax from a Windows command (cmd.exe) prompt.

# alternative commands for extracting sql and shell commands from step3.lst

C:\ORACLE\Clone> findstr "^alter user " step3.lst > dbusers4.sql

C:\ORACLE\Clone> findstr "^FNDCPASS " step3.lst > dbusers5.cmd

Step 4 - Assign New Passwords to All Schemas Not Managed with EBS

This Step runs the SQL script, "dbusers4.sql", generated in Step 3.

Sample content of "dbusers4.sql" listed below for illustration purposes only, you must run the one you generated on your system.

NOTE: "dbusers4.sql", for example purposes only!

alter user "OLAPSYS" identified by CLONE;

...

alter user "MDSYS" identified by CLONE;

alter user "ORDPLUGINS" identified by CLONE;

alter user "ORDSYS" identified by CLONE;

alter user "DBSNMP" identified by CLONE;

alter user "OUTLN" identified by CLONE;

alter user "AD_MONITOR" identified by CLONE;

alter user "EM_MONITOR" identified by CLONE;

Note: Prior to running your script, you should review the contents of the script for any obvious problems or syntax errors- this is good advice for any dynamically-created SQL scripts.

Connect as "SYSDBA":

$ sqlplus "/ as sysdba"

Now run the "dbusers4.sql" file:

SQL> spool step4.lst
SQL> start dbusers4.sql
SQL> exit

The output spool file should show many output lines stating "User altered.". No error messages (ORA-nnnnn) should appear.

At this point, the database should be started and running. Stop and restart the database at this time. To ensure that the application tier code can access the database for Step 5, you must also ensure that the database TNS-listener service is running.

$ echo shutdown | sqlplus "/ as sysdba"

$ echo startup | sqlplus "/ as sysdba"

$ lsnrctl start

Step 5 - Assign New Passwords to All Schemas Managed with EBS

This step uses the "FNDCPASS" command to set the passwords for all the EBS managed schemas and all the base product schemas. The "FNDCPASS" must be run from an application tier node.(Any node with an APPL_TOP file system.)

You will need to locate and copy the "dbusers5.sh" script from the directory where it was created in Step 3. Again, as with any dynamcially generated scripts that you run on your system, you should review the contents of the file before running it.

Note for Windows users: In the unlikely event that any of the usernames contain the dollar sign "$" it has been escaped by prefixing it by a backslash "\"; on Windows the backslash should be removed.

To run "FNDCPASS" you also need a number of environment variables set, at a minimum ensure that:

"FNDCPASS" is in the "$PATH" ("$ which FNDCPASS" will tell you if it is.)
The "ORACLE_HOME" environment variable points to the "Tools" ORACLE_HOME (8.0.6 on 11i, 10.1.2 on R12)
The "TWO_TASK" environment variable is set to a value that can be resolved via the "$TNS_ADMIN/tnsnames.ora file", in order to access the clone target database.

# Verify that the Oracle client environment is set to correct database (as "applmgr" OS user)

applmgr$ sqlplus -s apps/clone <

select SYSDATE,NAME from v\$DATABASE;

EOF

SYSDATE NAME

--------- ---------

25-JUL-07 PRD12

applmgr$ mkdir ~/s5 ; cd ~/s5 # create new directory to hold output files

applmgr$ sh dbusers5.sh # Run the FNDCPASS shell script

The following is sample content of a "dbusers5.sh" file is listed below for illustration purposes only, run the one you generated on your system.

NOTE: This "dbusers5.sh" is for example only!

FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone

FNDCPASS apps/clone 0 Y system/clone ORACLE "OWAPUB" clone

FNDCPASS apps/clone 0 Y system/clone ORACLE "ODM" clone

FNDCPASS apps/clone 0 Y system/clone ORACLE "CTXSYS" clone

FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone

Each run of "FNDCPASS" will generate output an output/log file in the current working directory, you should review these log files (example "L2763902.log") for errors.

NOTE: If your version of the "FNDCPASS" utility does not support the "ALLORACLE" mode, see "Q5" in the "Discussion" section below.

To verify that you have assigned passwords to all the database users, run the following query and ensure that it does not return any rows:

SQL> select USERNAME,PASSWORD from DBA_USERS where PASSWORD='0000000000000000';

This concludes the clearing and re-establishment of account credentials from a cloned database. Please see the following 2 steps "Additional Steps" and "Running Autoconfig" before attempting to use the system.

Additional Steps

What remains to be done is to set new passwords for additional applications users or the creation of new test users, depending on your needs. Changing passwords for applications users can be done using the "Define User" form (logged on as "SYSADMIN/CLONE") or by running "FNDCPASS" with the below syntax from an "applmgr" applications shell environment.

applmgr$ FNDCPASS apps/clone 0 Y system/clone USER

You may also wish to change the passwords to something other than "clone". You can use modified versions of the scripts in this note and you should reference the security best practices document for advice on changing passwords for an E-Business Suite system, see the References section below.

Running AutoConfig

Before you can actually start and access the cloned EBS system from the Application, a number of other configuration items, such as system Profile Options, most likely need to be changed in the cloned environment. Items to change typically include:

IP addresses, hostnames and port numbers
Profiles containing hostnames and port numbers

Web interface URLs
Hostnames of external services (mail, print, SSO)

The cloning notes, listed in the "Reference" section below, will provide you with information on how to run AutoConfig. Running AutoConfig is a requirement and it must be run on all tiers of the cloned system to propagate password changes and other changed settings into Autoconfig-managed files.

Prior to running AutoConfig ensure that the AutoConfig Context file contains the new "GUEST" password (Context variable "s_guest_pass") and the new password for "APPLSYSPUB" (Context variable "s_gwyuid_pass").

Password for	Context Variable	New Value
APPLSYSPUB	s_gwyuid_pass	CLONE
GUEST	s_guest_pass	CLONE

Oracle Apps DBA stuff

Tuesday, February 3, 2015

How to Extract Hyperion Financial Management (HFM) ErrorLog Entries from Database Using HFMErrorLogViewer.exe

DETAILS

Thursday, January 29, 2015

Removing Credentials from a Cloned EBS Production Database

Wednesday, January 28, 2015

Registry-Cleanup utility and the XML which drives it!

Search This Blog

Blog Archive