DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, October 12, 2012

Secure Configuration Guide for Oracle E-Business Suite Release 12 From Metalink



Security is very important factors for Oracle Apps DBAs.


The Metalink Doc : [ID 403537.1], Secure Configuration Guide for Oracle E-Business Suite Release 12, Shares us the Guide for R12 Security configuration and Security check script.


Login to metalink and make us of it .... Very helpful Security Guide and Scripts are very helpful too..


Below are the Extracts (Unless you have metalink access, you wont get those these).


Secure Configuration Guide for Oracle E-Business Suite Release 12


This document provides practical advice for secure configuration of the Oracle E-Business Suite Release 12.0 and 12.1.


Secure Configuration Guide for Oracle E-Business Suite Release 12 Version 1.1.1 (PDF)


Additionally, the following zip file provides a set of scripts to verify the proper setting of many of the values recommended in this document.


Oracle E-Business Suite Security Configuration Check Scripts (ZIP)


The document contains the following sections:

•Overview ◦Keep software up to date
◦Restrict network access to critical services
◦Follow the principle of least privilege
◦Monitor system activity
◦Keep up to date on latest security information

• Oracle TNS Listener Security ◦Harden operating environment

◦Add IP restrictions or enable Valid Node Checking
◦Specify connection timeout
◦Enable encryption of network traffic
◦Enable TNS Listener password (only if required)
◦Enable admin restrictions
◦Enable TNS Listener logging

• Oracle Database Security ◦Harden operating environment

◦Disable XDB
◦Review database links
◦Remove operating system trusted remote logon
◦Implement two profiles for password management
◦Change default installation passwords
◦Restrict access to SQL trace files
◦Remove operating system trusted remote roles
◦Limit file system access within PL/SQL
◦Limit dictionary access
◦Revoke unneccessary grants given to APPLSYSPUB
◦Configure the database for auditing
◦Audit database connections
◦Audit database schema changes
◦Audit other activities
◦Audit administrators and their actions
◦Review audit records
◦Maintain audit records
◦Secure audit records

• Oracle Application Tier Security ◦Harden operating environment

◦Harden Apache configuration
◦Protect administrative web pages
◦Configure logging

• Oracle E-Business Suite Security

◦Harden operating environment
◦Strike passwords from adpatch logs
◦Set Workflow notification mailer SEND_ACCESS_KEY to N
◦Set Tools environment variables
◦Restrict filetypes that may be uploaded
◦Enable Antisamy HTML filter
◦Use SSL (HTTPS) between browser and web server
◦Avoid Weak Ciphers and Protocols for SSL (HTTPS)
◦Use External Webtier if exposing any part of EBS to the internet
◦Use Terminal Services for client-server programs
◦Change passwords for seeded application user accounts
◦Switch to Hashed Passwords
◦Tighten logon and session profile options
◦Consider using Single-Sign-On
◦Create new user accounts safely
◦Create shared responsibilities instead of shared accounts
◦Configure Concurrent Manager for safe authentication
◦Configure Concurrent Manager for Start and Stop without the APPS password
◦Activate Server Security
◦Create DBC files securely
◦Review and limit Responsibilities and Permissions
◦Set other security related profile options
◦Restrict responsibilities by web server trust level
◦Set Sign-On audit level
◦Monitor system activity with OAM
◦Retrieve audit records using Reports
◦Retrieve audit records using SQL
◦Purge audit records
◦Review data tracked (no Reports available)
◦Configuring audit trail
◦Generate and identify audit trail objects
◦Choose tables to audit
◦Retrieve audit records using SQL
◦Purge audit records
◦References on Oracle E-Business Suite auditing

• Desktop Security

◦Configure browser
◦Update browser
◦Turn off AutoComplete
◦Set policy for unattended PC sessions
• Operating Environment Security ◦Cleanup file ownership and access
◦Cleanup file permissions
◦Lockdown operating system libraries and programs
◦Filter IP packets
◦Prevent spoofing
◦Eliminate telnet, rsh and ftp daemons
◦Verify network configuration
◦Monitor for attacks
◦Configure accounts securely
◦Limit root access
◦Manage user accounts
◦Secure NFS
◦Secure operating system devices
◦Secure executables
◦Secure file access

• Extras for Experts

◦Detect and Prevent Duplicate User Sessions
◦Customize Password Validation
◦Encrypt Credit Cards
◦Advanced Security/Networking Option (ASO/ANO)
◦Advanced Security/Transparent Data Encryption (ASO/TDE)
◦Practice Safe Cloning
◦Hardening External Procedure (EXTPROC) Services
◦EXTPROC Listener Configuration
◦EXTPROC Testing Procedure

• Appendix A: Running Web-Scanning Tools

• Appendix B: Sensitive Administrative Pages
• Appendix C: Database Schemas found in Oracle E-Business Suite
• Appendix D: Processes used by Oracle E-Business Suite
• Appendix E: Ports used by Oracle E-Business Suite
• Appendix F: Sample Linux Hardening of the Application Tier
• Appendix G: Security Check Scripts
• Appendix H: References & More Resources

Keywords: E-Business, Secure Configuration, Hardening, Best Practice, Security

Monday, October 8, 2012

AFPASSWD - New password utility in EBS 12.1.2


AFPASSWD:

In R12.1.1 and before oracle EBS Versions EBS passwords are changed using FNDCPASS.
AFPASSWD Usage and Syntax:
 -h - Displays help.
From R12.1.2 Oracle has come up with another Utility AFPASSWD for the same purpose.
Advantage is that you can run AFPASSWD utility from DB tier or from Midtier where as FNDCPASS can only be run from midtier side.

AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:
     AFPASSWD only prompts for passwords required for the current operation,
allowing separation of duties between applications administrators and database administrators.
This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.
   
     When changing a password with AFPASSWD, the user is prompted to enter the
New password twice to confirm.
  
     AFPASSWD can be run from the database tier as well as the application tier. In
contrast, FNDCPASS can only be run from the application tier.

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-f <FNDUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-o <DBUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-a]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-l <ORACLEUSER> [<TRUE>] |[<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-L [<TRUE>] | [<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-s] <APPLSYS>

These options have the following functions:

-c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the
Applications user, and/or the value of TWO_TASK. This option can be use in
combination with others. If it is not specified, default values from the environment
will be used.
Note: The password will be prompted for, and is not to be
provided in the connection string.
-f {FNDUSER} - Changes the password for an Applications user. A username that
contains spaces must be enclosed in double quotation marks; for example, "JOHN
SMITH".

-o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.
Note: This only applies to users listed in the FND_ORACLE_USERID table, not database users in general.

 -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS,APPLSYS, APPLSYSPUB) to the same password, in the same way as the ALLORACLE mode does in FNDCPASS.

 -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

 -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} =LOCK, {FALSE} = UNLOCK.

 -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user. This requires the execution of autoconfig (in ALL-TIERS) to distribute the changes on your instance \
Ie: DB-TIER and ADMIN-TIER.


Run autoconfig on db tier and application tier after changing apps password:

Saturday, October 6, 2012

FRM-92095 Oracle Jinitiator version too low


FRM-92095 Oracle Jinitiator version too low:
Disable JRE 1.7 Auto-Update for All E-Business Suite and Oracle Forms End-Users

Oracle Support advising all Forms and EBS users to urgently disable auto-update of the JRE on your end users PC. If you have Auto-Update enabled, your JRE 1.6 version will be updated to JRE 7.

Unfortunately Oracle Forms is not compatible with JRE 7 yet AND
JRE 7 has not been certified with Oracle E-Business Suite yet.

Basically all Oracle E-Business Suite functionality based on Forms — e.g. Financials —
will stop working if you upgrade to JRE 7.

Temporary Solution:

If an  end-user’s desktop has been upgraded to JRE 1.7,  and you get the error message
FRM-92095: Oracle Jnitiator version too low – please install version 1.1.8.2 or higher

Note: ID 1348436.1    and a bug exists ( not yet fixed )
 Bug:11782681 APPS6: FORMS DO NOT LAUNCH WITH BETA JRE 1.7

If the deed has been done and your PC took over and upgraded your JRE
and you are unable to run Oracle Forms have no fear. just follow the steps below.

Uninstall JRE 1.7
Reinstall the latest JRE 1.6 release – Download the Java 6
http://www.java.com/en/download/manual_v6.jsp

If you want to create a silent JRE install so you can update in batch, you can checkout 
Metalink  Note: 362488.1 – How To Do A Silent Install Of Jinitiator From The Browser?

Wednesday, October 3, 2012

Oracle Standard Script to find all the Oralce Applications Product related details


One Stop Oracle Standard Script to find all the Oralce Applications Product related details

In AD Top we have script by name adutconf.sql ($AD_TOP/sql/adutconf.sql) file which gives

Product Group Information
Multi-Org enabled or not
Existing Operating Units
Multi-Currency enabled or not
Registered Applications
Registered ORACLE Schemas
Product Installation Status, Version Info and Patch Level
Product Database Configuration
Localization Module Information
Registered Data Groups
Base language and other Installed languages
NLS Settings
Replication Package Installed or not

How to run this script.

1. Login to application tier
2. source the applications env
3. cd $AD_TOP/sql
4. login to apps schema through sqlplus

sqlplus  apps @adutconf.sql

###~~~## it will prompt for apps password provide apps password.

the output will be generated in $AD_TOP/sql folder itself as adutconf.lst

Make use of it...This will be useful for most of you....

Yours
Narasimha Rao

EBusiness Suite Application Techstack component Versions


One Stop Standard Script to Oracle E-BS Application Techstack component Versions:

Really Required for Apps DBAs

MOS ID :601736.1

On Application Tier

  1. Establish the needed environment parameters by sourcing the Applications environment file as the owner of the application tier file system.
  1. Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP".
  1. Navigate to /patch/115/bin. Run the utility/command as follows :

    Operation SystemCommand Line
    Unix or Linux









    $ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl \
    -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP \
    -contextfile=$CONTEXT_FILE \
    -appspass=apps \
    -outfile=$APPLTMP/Report_App_Inventory.html

    Note:
    - Run the above command in single line
    - Remove the '\' after each parameter while you run this command in a single line
    - Supply the apps user password as is applicable for parameter "-appspass"
    Windows








    %ADPERLPRG% %FND_TOP%\patch\115\bin\TXKScript.pl
    -script=%FND_TOP%\patch\115\bin\txkInventory.pl
    -txktop=%APPLTMP% -contextfile=%CONTEXT_FILE%
    -appspass=apps
    -outfile=%APPLTMP%\Report_App_Inventory.html
    Note:
    - Run the above command in single line
    - Remove the '\' after each parameter while you run this command in a single line
    - Supply the apps user password as is applicable for parameter "-appspass"

    Where:
    txktopTemporary working directory use by Perl Modules. Should not be an empty string.
    contextfileLocation of the context file. If not passed, default is picked from the environment.
    appspassAPPS schema password. If not passed, default password is used.
    outfileLocation for the report being generated. If not passed, the default location is /TXK

    To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example:
    outfile=$APPLTMP/Report_App_Inventory.html -reporttype=text
  1. Operation SystemCommand Line
    Unix or Linux









    $ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl \
    -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP \
    -contextfile=$CONTEXT_FILE \
    -appspass=apps \
    -outfile=$APPLTMP/Report_App_Inventory.html

    Note:
    - Run the above command in single line
    - Remove the '\' after each parameter while you run this command in a single line
    - Supply the apps user password as is applicable for parameter "-appspass"
    Windows








    %ADPERLPRG% %FND_TOP%\patch\115\bin\TXKScript.pl
    -script=%FND_TOP%\patch\115\bin\txkInventory.pl
    -txktop=%APPLTMP% -contextfile=%CONTEXT_FILE%
    -appspass=apps
    -outfile=%APPLTMP%\Report_App_Inventory.html
    Note:
    - Run the above command in single line
    - Remove the '\' after each parameter while you run this command in a single line
    - Supply the apps user password as is applicable for parameter "-appspass"

  1. Where:
  1. txktopTemporary working directory use by Perl Modules. Should not be an empty string.
    contextfileLocation of the context file. If not passed, default is picked from the environment.
    appspassAPPS schema password. If not passed, default password is used.
    outfileLocation for the report being generated. If not passed, the default location is /TXK
  1. To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example:
  1. outfile=$APPLTMP/Report_App_Inventory.html -reporttype=text
  1. Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script
  1. Incase you have more than one application tier's (multi-node architecture), follow the steps 1-4 on each of the Application tiers
  1. Upload the report output file (default: $APPLTMP/Report_App_Inventory.html )to Oracle Support for review

Sure it helps you....please comment.

Yours,
Narasimha Rao

Recovering Block Corruption when the block does not belong to any segment


Let me Share one of my recent incident , where im suppose to recover the block which was marked as corrupted and the same was found belongs to free extent [ or does not belong to any segment].

Got an message from alert logfile that the sysaux datafile got some block corrupted.

When i ran the dbverify , got the below message:

DBVERIFY: Release 10.2.0.4.0 - Production on Mon Oct 1 07:06:50 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/uat/uatdata/sysaux01.dbf

DBVERIFY - Verification complete
Total Pages Examined         : 1091072
Total Pages Processed (Data) : 370292
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 682135
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 18266
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 20379
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 2431509128 (1389.2431509128)

When i checked database_block_corruption view , it returned no rows selected.

SQL> Select * from v$database_block_corruption;

no rows selected

Then i followed MOS ID : 819533.1 and found that The Corrupted block belongs to a free extent.

SQL> select dbms_utility.data_block_address_file(&&rdba) RFN,dbms_utility.data_block_address_block(&&rdba) BL from dual;
Enter value for rdba: 80451227
old 1: select dbms_utility.data_block_address_file(&&rdba) RFN,dbms_utility.data_block_address_block(&&rdba) BL from dual
new 1: select dbms_utility.data_block_address_file(80451227) RFN,dbms_utility.data_block_address_block(80451227) BL from dual

RFN BL
---------- ----------
19 759451

##### You will get RDBA , when you run dbverify.

SQL> select file_id AFN, relative_fno, tablespace_name from dba_data_files where relative_fno=&RFN; 
Enter value for rfn: 19
old 3: where relative_fno=&RFN
new 3: where relative_fno=19

AFN RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------
19 19 SYSAUX

SQL> select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1; 

Enter value for afn: 19
old 3: where file_id = &AFN
new 3: where file_id = 19
Enter value for bl: 759451
old 4: and &BL between block_id AND block_id + blocks - 1
new 4: and 759451 between block_id AND block_id + blocks - 1

no rows selected

SQL> select owner, segment_name, segment_type, partition_name from dba_segments where header_file = &AFN and header_block = &BL; 
Enter value for afn: 19
old 3: where header_file = &AFN
new 3: where header_file = 19
Enter value for bl: 759451
old 4: and header_block = &BL
new 4: and header_block = 759451

no rows selected

SQL> select * from dba_free_space where file_id = &AFN and &BL between block_id AND block_id + blocks - 1; 
Enter value for afn: 19
old 3: where file_id = &AFN
new 3: where file_id = 19
Enter value for bl: 759451
old 4: and &BL between block_id AND block_id + blocks - 1
new 4: and 759451 between block_id AND block_id + blocks - 1

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------------------------------------------------------------------------------------------------------------------
SYSAUX 19 749065 246415360 30080 19
Then I Followed the MOS ID : 336133.1 , and got the corrupted block recovered.

conn apps
Password:

SQL> create table s (
n number,
c varchar2(4000)
) nologging tablespace sysaux;

SQL> CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END


SQL> SELECT BLOCK_ID,FILE_ID,BYTES FROM DBA_FREE_SPACE WHERE FILE_ID = 19 AND 759451 BETWEEN BLOCK_ID and BLOCK_ID + BLOCKS - 1;

 
BLOCK_ID FILE_ID BYTES
---------- ---------- ----------
749065 19 246415360


SQL> alter table s allocate extent (DATAFILE '/u01/oracle/uat/uatdata/sysaux01.dbf' size 235m);


Table Altered

SQL> alter table s allocate extent (DATAFILE '/u01/oracle/uat/uatdata/sysaux01.dbf' size 235m);

Table Altered

SQL> alter table s allocate extent (DATAFILE '/u01/oracle/uat/uatdata/sysaux01.dbf' size 235m);

Table Altered

SQL> SELECT BYTES FROM DBA_FREE_SPACE WHERE FILE_ID = 19 AND 759451 BETWEEN BLOCK_ID and BLOCK_ID + BLOCKS - 1;
no rows selected

SQL> Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;
2 3 4 5 6 7 8 9
10 /
Begin
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "APPS.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'APPS.CORRUPT_TRIGGER'
ORA-06512: at line 4


########## The above will take time based on where the corrupted block got placed ###

[orauat@dbdata12 10.2]$ dbv file=/u01/oracle/uat/uatdata/sysaux01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Mon Oct 1 12:07:33 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/uat/uatdata/sysaux01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 1091072
Total Pages Processed (Data) : 510461
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 542090
Total Pages Failing (Index): 0
Total Pages Processed (Other): 18861
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 19660
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2432979198 (1389.2432979198)


sqlplus "apps"


SQL> drop table s;

SQL>Alter system switch logfile ;

SQL>Alter system checkpoint ;############################################


How is it ? Please comment.

Yours,
Narasimha Rao

Monday, October 1, 2012

Purging/Archiving Partial Data from Very Huge Table



Real challange for DBA's when they archive table , is when they perform partial data archiving from a very huge table.

This is my case : I was pushed into a bull ring, where i need to purge a workflow table with 42 GB size , which has 512203845 rows

Customer asked me to retain only 6 months .

Initially, as a regular DBAs, i tried to purge through delete statement with nologging and parallel, OMG~!  it took the complete day and additionally it ate all my archivelog space.

then i tried using expdp and impdp using the query option after switching off the archivelog mode, where it goes like this.

expdp myuser/mypass tables='WORKFLOW_TABLE' query='"where created_time < sysdate-180"'

It started well without any issues, but it took more than 13 hours.
Customer will kill me if i am asking for 13 hours downtime.

Finally i worked the very good solution, which took me only 01 hours 30 mins to complete retaining 343888371 records gaining 26GB.

Following is the technical solution:
1.  Extract the metadata of the table which you are going to purge,
2.  Extract the metadata of the index of the table which you are going to purge.
3.  Extract the dependent triggers,grant , synonyms

we can use

select dbms_metadata.get_ddl('TABLE','WORKFLOW_TABLE','MYUSER') FROM DUAL;

select dbms_metadata.get_ddl('INDEX','WORKFLOW_TABLE_N1','MYUSER') FROM DUAL;

 select dbms_metadata.get_ddl('TRIGGER','WORKFLOW_TABLE_TRG1','MYUSER') FROM DUAL;


4. create custom tablespace to accommodate the workflow partial data. [ in my case 30G ]

5. create the table with [as select * from ... where 1=2 ] clause , so that it will take the data structure including the constraints associated.

SQL> create table WORKFLOW_TABLE_R1" as select * from "WORKFLOW_TABLE" where 1=2 ;

6. Move the backup table to the custom tablespace;
SQL> alter table "WORKFLOW_TABLE_R1" move tablespace ev_data_purge ;

Table altered.

7. For safety reason, enforce nologging for workflow table

SQL> alter table "WORKFLOW_TABLE_R1" NOLOGGING ;

Table altered.

8. insert partial data into backup table

SQL> insert into "WORKFLOW_TABLE_R1" select * from "WORKFLOW_TABLE" where created_time < sysdate-180 ;

343888371 rows created.

6. Drop the original table
 
drop table "WORKFLOW_TABLE";

7. Rename the backup table to original table name

Alter table workflow_TABLE_r1 rename to workflow_TABLE;

8. create indexes for the workflow table.[with the metadata]

9. Create triggers ,grants and synonyms for the workflow table..[with the metadata]

10. Drop tablespace custom tablespace and enforce force logging for database and for the table.
 
 
How is it ??? Post your comments.
 
Yours,
Narasimha Rao.K