Tuesday, 28 October 2014

How to calculate time based records in SQL

 Every 4 HOUR

/* Formatted on 10/28/2014 5:42:43 PM (QP5 v5.115.810.9015) */
SELECT   *
  FROM   PO_APPROVED_SUPPLIER_LIST
 WHERE   creation_date > (SYSDATE - INTERVAL '4' HOUR)

Every 5 HOUR




 SELECT   *
  FROM   PO_APPROVED_SUPPLIER_LIST
  WHERE CREATION_DATE > TRUNC(SYSDATE - 5 / 24, 'HH')

Monday, 27 October 2014

Monday, 20 October 2014

SUB-QUERY Using emp and dept table process

/* Formatted on 10/19/2014 10:20:33 AM (QP5 v5.115.810.9015) */
SELECT   * FROM emp

select * from dept

   Same Table:-
====================
/* Formatted on 10/19/2014 10:20:29 AM (QP5 v5.115.810.9015) */
  SELECT   MAX (SAL), ENAME
    FROM   emp
GROUP BY   ENAME

/* Formatted on 10/20/2014 8:16:18 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   sal = (SELECT   MAX (SAL) FROM emp)

/* Formatted on 10/20/2014 8:16:13 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   sal = (SELECT   MAX (SAL)
                  FROM   emp
                 WHERE   DEPTNO = 10)

    Differnt Table:-
=========================

/* Formatted on 10/19/2014 10:31:13 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno IN (SELECT deptno FROM dept)

 IN
=====
/* Formatted on 10/19/2014 10:31:49 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno IN (SELECT   deptno
                      FROM   dept
                     WHERE   deptno = 10)



   NOT IN
==============
/* Formatted on 10/19/2014 10:52:26 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno NOT IN (SELECT   deptno
                          FROM   dept
                         WHERE   deptno = 10)
                        
                        
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno in (SELECT   deptno
                      FROM   dept
                     WHERE   deptno > 10)

   Operater Value:-
=======================
 ANY
======                       
/* Formatted on 10/19/2014 11:37:50 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal > ANY (SELECT   sal
                      FROM   emp
                     WHERE   sal > 13000)

/* Formatted on 10/19/2014 11:37:57 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal > ANY (SELECT   sal
                      FROM   emp
                     WHERE   sal >= 13000)

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal >= ANY (SELECT   sal
                       FROM   emp
                      WHERE   sal >= 13000)

  ALL
=======

/* Formatted on 10/19/2014 11:51:19 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB,
         deptno
  FROM   emp
 WHERE   sal > ALL (SELECT   sal
                      FROM   emp
                     WHERE   deptno = 10)




    IN and ANY Process:-
==============================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME in (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')

  IF USING ANY AND ALL MUST ADD THE OPERATORS =,<,>,<>
===========================================================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME = any (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')


   NOT IN and ANY process:-
===============================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME not in (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')
                     
/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME !=ANY (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')
                     
  CORRELATED SUBQUERIES:-
============================

A correlated subquery is one where the inner query depends on values provided by the outer query.

/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1
 WHERE   sal = (SELECT   MAX (SAL)
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO AND DEPTNO = 10)
                
/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1
 WHERE   sal = (SELECT   SAL
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO AND DEPTNO = 10)

/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1 /*WHERE   sal = (SELECT   SAL
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO */where DEPTNO = 10
                
    Subqueries and the EXISTS operator:-
=============================================

/* Formatted on 10/20/2014 8:24:30 AM (QP5 v5.115.810.9015) */
SELECT   emp_last_name "Last Name", emp_first_name "First Name"
  FROM   employee
 WHERE   EXISTS (SELECT   *
                   FROM   dependent
                  WHERE   emp_ssn = dep_emp_ssn);
                 
/* Formatted on 10/19/2014 10:31:49 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE  EXISTS (SELECT   *
                      FROM   dept E1
                     WHERE   deptno = E1.deptno)

Friday, 17 October 2014

CEIL and FLOOR Function in Sql

 CEIL and FLOOR:-


SELECT
     CEIL(109.19)  ceil_val,
       FLOOR(109.99) floor_val from dual;
      
      
ROUND:-
      
       ROUND( number, decimal_places [, operation ] )
      
       SELECT ROUND(125.315, -2) from DUAL;
      
       SELECT ROUND(6.2,-2) from DUAL;
      
     
      SELECT INVOICE_AMOUNT FROM AP_INVOICES_ALL
     
        SELECT INVOICE_NUM,ROUND(INVOICE_AMOUNT) FROM AP_INVOICES_ALL WHERE INVOICE_AMOUNT = '15629.33'

Step by Step information on Goverance, Risk and Compliance - GRC 8.6.4.5000 and GRCI (Intelligence) 8.6.4.5000 and its upgrade to 8.6.4.6000/7000/8000 - Non SOA implementation (Part 1)


The most important aspect for any Oracle professional to understand client needs and their environments also the challenges you may face during implementation of any Oracle products. You need to understand the dynamics of client teams , important client folks who can influence the project like Project Manager, Client DBA's, O/S and Network Administrator. Also it is very important to understnad the Change Management Process which help you to implement any product within the cost , on time and schedule.

Consider the following important points which you should always remember before you start any implementation. I may add some more points later too.

1) Project Contacts and Project Manager who is running the show.
2) Understand duration of the implementation
3) Request Project Manager for any unforseen issues may cause during product implementation and always ask for buffer time as you may face issue at their O/S Level, Network , Software Download/Upload issues, Staging issues and it could be anything which you can not expect. So the best is do a dry run and prepare you own project plan and include all requirement and analysis. When Project Manager ask you to start the project you should have all your requirement go in to your change request. This will help you avoid delays due to various reason which depends on project to project.
4) Understand Security and Compliance issues with in the project like which ports to be open and which firewall to be open and from where to where. Also do you need keep any data retention for compliance reason.
5) Check Oracle Certification and always go through Documentatoin on Installation, Upgrade ,Release Notes and mandatory MOS notes. If you get the time please re-read again and again during implementation. Before actual implementation start always download the latest updated documentation this will help you avoid any issues during your implementation. The same is applicable for Oracle MOS notes too.
6) You may use MS-Project or MS-Excel or MS-Word or a your note books. It doesn't matter what you are comfortable with but it is very important you keep you anaylysis ready for your implementation include client folks comments and their DBA/Sys administrator comments are very important to understand in-house issues.
7) Always make sure what version of software requesting to you and request them to download and upload to stage area or you have given access then do this step as atmost priority , the same is applicable to all required patches and/or any other client tools you requried during your implementation or post-implemenation.

Hope the above points are useful in your day-to-day professional life.

Today's Step by Step Oracle Product Series article is on Installation of Goverance Risk and Complaince (GRC /GRCI 8.6.4.500) and its upgrade.

I am going to cover the steps in this article and will build the Virtual environment at home and later place the screen shots for Oracle user community for their learning.

URL for GRC Suite 8.6.4.5000 Documentation: http://docs.oracle.com/cd/E39187_01/index.htm

URL for Oracle GRC Suite Software Download : edelivery.oracle.com , Select Linux 64-bit.

URL for Oracle Certification Matrix : GRC Suite Certification Matrix (Doc ID 741001.1)
Straight from Oracle Documentation :

Oracle Governance, Risk and Compliance (GRC) serves as a platform for two components — Enterprise Governance, Risk and Compliance Manager (EGRCM) and Enterprise Governance, Risk and Compliance Controls (EGRCC).

EGRCM forms a documentary record of a company’s strategy for addressing risk and complying with regulatory requirements. It enables users to define risks to the company’s business, controls to mitigate those risks, and other objects, such as business processes to which risks and controls apply.

EGRCC comprises two elements, Application Access Controls Governor (AACG) and Enterprise Transaction Controls Governor (ETCG). These enable users to create models and controls and to run them within business applications to uncover and resolve segregation of duties violations and transaction risk.

These components run as modules in the GRC platform. EGRCC runs as a Continuous Controls Monitoring (CCM) module. EGRCM provides a Financial Governance module by default, and users may create other EGRCM modules to address other areas of the company’s business.

A third component, Oracle Fusion GRC Intelligence (GRCI), provides dashboards and reports that present summary and detailed views of data generated in GRC. GRCI may be embedded in GRC, or run as a standalone application.


GRC 8.6.4.5000 High Level Installation Steps. I will includes screenshots when I get the chance.

(1)
Create a stage area /OracleSoftwareStage to stage the software. I would like to recommend here to stage all GRC Suite Software. I would like to recommend at least 100 GB to stage and unzip the files also to be used for staged backup during implementation.

Check the JDK 1.7.0_13+ is also installed.

Required Software to be staged for GRC 8.6.4.5000 Implementation are as under:
(1) Oracle Database 11.2.0.3 & Examples software
(2) Weblogic 10.3.6
(3) ADR 11.1.1.6
(4) JDK 1.7.0_13+
(5) GRC 8.6.4.5 (included rpd and Webcat files for GRCI - V36363-01.zip file).


(2)

For O/S Pre Requisites, please refer documents listed as under:

Verify Oracle RPM's, Kernel parameters (/etc/security/limits.conf),VNC,required ports to be open, including Oracle userid (oracle:dba user) and application grc user id (grcapp:dba user) for Database and GRC Installation. Sudo to root user is required for DB and GRC Application Tier installation.


a) Defining a "default RPMs" installation of the RHEL OS [ID 376183.1]
b) Master Note of Linux OS Requirements for Database Server [ID 851598.1]
c) Requirements for Installing Oracle 11gR2 RDBMS on RHEL6 or OL6 64-bit (x86-64) [ID 1441282.1]) (Must use)
d) http://docs.oracle.com/cd/E11882_01/install.112/e24321/pre_install.htm#BABCFJFG
e) Governance Risk and Compliance (GRC) Product Information [ID 1084596.1]

(3)

Verify File systems are in place and validated before the installation. Also needs to validate if the space is adequately allocated. Validate the appropriate permission for oracle user and group as dba on File system.

/u01 - For Database Binaries and Middleware Binaries.
/u02 - For Data Files , ETL & Report directories.
/opt/oracle - For Oracle Inventory location and permission should be 775 must be set for oracle and grcapp users can write to this location. Make sure both users are created on step 1 must be part of same group "dba".
archive log mount point - For Oracle Database in archive mode.

(4)

Install Oracle Database 11.2.0.3 as well as Install Oracle Examples.

(5)

Create GRC (grc_superuser and GRCI schema DA (da_superuser - will be used for GRCI implementation later).

Creation of Schema's for GRC and GRCI

In the Oracle database, create a GRC schema for use by GRC. If you intend also to install GRCI, create a second schema, known as the Data Analytics (DA) schema.
The following is a sample script that serves for the creation of either schema. You are assumed to have created tablespaces; each schema requires its own. The values you choose for tablespace name, user (schema) name, and password would be dis-tinct for each schema, and are represented here by TablespaceName, UserName, and UserPassword, respectively. Each password must contain at least six characters.

create user grc_superuser identified by mypass default tablespace GRC_TS_DATA quota unlimited on GRC_TS_DATA quota 0k on system;
grant connect, resource to grc_superuser;
grant create any view to grc_superuser;
grant create any table to grc_superuser;
grant drop any table to grc_superuser;
grant create synonym to grc_superuser;

Run the following commands as the system user:

ALTER SYSTEM SET open_cursors=5000 scope=spfile;
ALTER SYSTEM SET processes=3000 scope=spfile;
ALTER SYSTEM SET deferred_segment_creation=FALSE scope=spfile;

After running these commands, bounce the database.

GRC may display information in any of twelve languages. To use the multilingual capability, set up the database that hosts the GRC schema for UTF-8 encoding. To do so, execute this command, for which the return value should be AL32UTF8:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

Once installation is complete, users will be able to use a set of Schema Import/Export fields (available in the Properties tab of a Manage Application Configurations page) to download the GRC schema, or to upload a copy of it.


To set up this feature, create a DATA_PUMP_DIR directory, which provides temporary storage for schema-file copies.

In the following commands, use a SQL editor such as SQL*Plus; replace with the name of the admin user for the GRC application server; replace with the path to a directory that will serve as your DATA_PUMP_DIR directory.

a. Create a directory object, and grant READ and WRITE access to it.
CREATE DIRECTORY DATA_PUMP_DIR AS /u02/GRC_DATA_DUMP_DIR;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO grc_superuser;

b. Ensure that grc_superuser has EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. For a list of roles within your security domain, enter the following:

SELECT * FROM SESSION_ROLES;

c. If the grc_superuser does not have these roles, execute the following commands:

GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO grc_superuser;
GRANT CREATE SESSION TO grc_superuser;
GRANT CREATE TABLE TO grc_superuser;
GRANT UNLIMITED TABLESPACE TO grc_superuser;

d. Grant an additional permission, required for the import operation:

GRANT EXECUTE ON UTL_FILE TO grc_superuser;

Note:
Before a schema file can be imported, an empty schema must be created for it, and a tablespace must be created for the schema. The script shown above may be used to create the schema. The following is an example of a script that creates a tablespace if the original GRC schema and its copy will reside in the same database. UserName is the name of the copied schema, and UserNameTS is the name of the tablespace.
CREATE USER "grc_superuser_cp" IDENTIFIED BY "mypasscp" DEFAULT TABLESPACE GRC_TS_DATA QUOTA UNLIMITED ON GRC_TS_DATA;

Note: Please execute all the steps above for creation of grc_superuser_cp ,da_superuser and da_superuser_cp (Copy Schema). Also create seperate Index tablespace for indexes to be stored and you can use the name as "GRC_TS_INDEX". Also create two additonal tablespace for DA (GRCI Schema's to store data and index) for example DA_TS_DATA and DA_TS_INDEX.


(6)

Make sure /u02/GRC8645_ETL and /u02/GRC8645_Report directories.

(7)

Complete the Install of Weblogic 10.3.6 and ADR 11.1.1.6. (Without SOA)

(8)

Weblogic Domain Creation Steps.

== Detailed Steps (8) ==

For any installation, one template is selected automatically: “Base WebLogic Server Domain — 10.3.6.0.” Also select “Oracle Enterprise Manager — 11.1.1.0.” When you do, a third template, “Oracle JRF — 11.1.1.0,” is selected with it.
Only if you are installing GRC with SOA, select three more templates: “Oracle SOA Suite — 11.1.1.0,” “Oracle WSM Policy Manager — 11.1.1.0,” and “Oracle JRF Webservices Asynchronous Services — 11.1.1.0.”
2. Create a name for your WebLogic domain. Use any name you wish. (Throughout this document, the value represents the name you configure here.) In two other fields — Domain Location and Application Location — accept default values.
3. At a Configure Administrator Username Password prompt, create a WebLogic Server username and password.
4. At a Configure Server Start Mode and JDK prompt, select “Production Mode.” In the JDK Selection area, ensure that the correct JDK is selected. (This is the JDK instance you confirmed to be in the path to install and run WebLogic under “Initial WebLogic Installation” on page 2-3.) If necessary, use the “Other JDK” option to browse.
5. For GRC with SOA only, respond to a Configure JDBC Component Schema prompt. Enter details you’ve already established as you used RCU to create repositories (see step 2b of “Initial WebLogic Installation” on page 2-4). When you complete this step, you should see the value “Test Successful” at a Test Component Schema prompt.
6. For any installation, select “Administration Server” at a Select Optional Con-figuration prompt. Also select “Managed Servers, Clusters and Machines” if you are installing GRC with SOA, but not if you are installing GRC to run with a pre-existing SOA or without SOA.
7. At a Configure the Administration Server prompt, enter the IP address of the machine running the WebLogic Server. Also select an unused port for it.
8. If you are installing GRC to run FAACG, or GRC with SOA , a Configure Man-aged Servers prompt appears:
• For GRC with FAACG, click the Add button. In the row that appears, enter a name for the GRC server and the IP address of the machine running the WebLogic Server. Then continue at step 9.
• For GRC with SOA, confirm that the Configure Managed Servers prompt displays a row for a SOA Server. This row appears as a result of GRC-specific configuration you’ve already completed. Note the IP address and port, which you’ll need to enter later in a GRC Worklist page. Continue at step 9.
If you are installing GRC without SOA, or to run with a pre-existing SOA, you need not create a managed server. The Configure Managed Servers page and several other Configuration Wizard pages do not appear. Skip ahead to step 12.
9. Skip the Configure Clusters page.
10. In a Configure Machines page, select the Unix Machine tab. Click Add. Assign any name, and accept defaults for all other fields.
11. In the Assign Servers to Machines page, select the servers listed in the left box. Move them to machine you created in step 10, which is listed in the right box.
12. In the Summary page, select Create.

Preparing Additional Files

Complete these additional steps when the config.sh script finishes running:

1. Copy the following files from /oracle_common/modules/oracle.adf.model_11.1.1, to /user_projects/domains//lib:
• adfm.jar
• adfdt_common.jar
• adfmweb.jar
2. Copy the following files from /lib to /user_projects/domains//lib:
• groovy-all-1.6.3.jar
• xdoparser-10.1.3.4.jar
If you are installing GRC to run FAACG, invoke the WebLogic scripting tool — wlst.sh — from /oracle_common/common/bin. Use it to apply the JRF template to the GRC managed server you created in step 8 of “Creating a WebLogic Domain” ( in the following example):
applyJRF('','/user_projects/domains/')
If you do not intend to run FAACG, skip this step.
4. Create a directory called grc864 (for example, /grc864). This directory should be entirely distinct from the directory you created as you downloaded GRC files.
5. Navigate to /dist, and locate the file grc.ear. Copy it to the grc864 directory, and extract its contents there.
In addition, as you complete GRC installation procedures, you will use scripts named startWeblogic.sh and stopWeblogic.sh to start and stop the WebLogic Administration Server. First, edit the stopWeblogic.sh file as follows:
1. Open /wlserver_10.3/common/bin/stopWeblogic.sh in a text editor.

==Detailed Steps End Here==


(9)

Perform Configuration Steps in a Weblogic Server Administration Console, and modify memory and other settings to confirm to GRC requirements.


== Detailed Steps (9) ==

For any installation, use the WebLogic Server Administration Console to complete additional configuration steps:
1. Make sure you are logged in to the WebLogic Console (see step 2 of “Creating the SOA Admin User and Enabling Embedded LDAP,” page 2-13).
2. In the Change Center pane, click Lock & Edit.
3. In the Domain Structure pane, click on Deployments.
4. In the Summary of Deployments pane, select the Control tab.
5. In the Summary of Deployments pane, click on the Install button.
6. In the Path field of the Install Application Assistant pane, enter the full path to the grc864 directory you created earlier (see step 3 of “Preparing Additional Files” on page 2-5). Select “grc864 (open directory)” under Current Location.
7. In the Install Application Assistant pane, press next.
8. In the Install Application Assistant pane, choose Install this deployment as an application in the “Choose targeting style” section.
9. In the Install Application Assistant pane, press Next. Then:
• If you are installing GRC to run FAACG, select the GRC managed server you created in step 8 of “Creating a WebLogic Domain” (page 2-5).
• If you are installing GRC with SOA, select the Administration Server.
• If you are installing GRC to run without SOA or with a pre-existing SOA, you are not presented with an opportunity to select a server here. Skip to step 10.
10. In the Install Application Assistant pane, choose I will make this deployment accessible from the following location in the “Source accessibility” section. Accept all other defaults.
11. In the Install Application Assistant pane, press Next.
12. In the Install Application Assistant pane, choose Yes, take me to the deploy-ment’s configuration screen in the “Additional configuration” section.
13. In the Install Application Assistant pane, press Finish.
14. In the Install Application Assistant pane, press Save, then Activate Changes. On the Deployments screen, the state of the grc864 application will be Prepared.
15. Select the grc864 application. Click Start, select Servicing all requests, click Yes on Start Application Assistant, and wait until the application status changes to Active.

Modifying Settings
Next, modify settings in a file called setDomainEnv.sh, which is located in the /user_projects/domains//bin directory.
1. Stop the SOA Server (if you are installing EGRCM with SOA) and the Administration Server.
2. Navigate to setDomainEnv.sh and open it in a text editor.
3. In the file, locate the following lines:
# IF USER_MEM_ARGS the environment variable is set, use it to override ALL MEM_ARGS values
if [ "${USER_MEM_ARGS}" != "" ] ; then
4. Insert the following lines between those two lines:
case "${SERVER_NAME}" in "AdminServer") USER_MEM_ARGS="-Xms4g –Xmx16g" ;; "bi_server1") USER_MEM_ARGS="-Xms2g –Xmx8g" ;; "soa_server1") USER_MEM_ARGS="-Xms2g –Xmx4g" ;; "sample_server1") USER_MEM_ARGS="-Xms4g -Xmx16g" ;; "sample_server2") USER_MEM_ARGS="-Xms4g -Xmx16g" ;; *) echo "Unknown Server Detected!!. Memory set as Xms1g Xmx2g."; USER_MEM_ARGS="-Xms1g -Xmx2g" ;; esac
USER_MEM_ARGS="${USER_MEM_ARGS} -XX:PermSize=256m -XX:MaxPermSize=512m -XX:ReservedCodeCacheSize=128M -Djava.awt.headless=true -Djbo.ampool.maxpoolsize=600000"
Replace “placeholder” names (AdminServer, bi_server1, soa_server1, sample_server1, and sample_server2) with the names of your Administration Server and any managed servers included in your installation.
You may use a maximum memory setting (-Xmx) larger than 16G if your server has enough memory to support the larger value.
5. Locate the following section of the file and ensure that “-da” appears after each of two “${enableHotSwapFlag}” elements:
if [ "${debugFlag}" = "true" ] ; then
JAVA_DEBUG="-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,address=${DEBUG_PORT},server=y,suspend=n -Djava.compiler=NONE"
export JAVA_DEBUG
JAVA_OPTIONS="${JAVA_OPTIONS} ${enableHotswapFlag} -da -da:com.bea... -da:javelin... -da:weblogic... -ea:com.bea.wli... -ea:com.bea.broker... -ea:com.bea.sbconsole..."
export JAVA_OPTIONS
else
JAVA_OPTIONS="${JAVA_OPTIONS} ${enableHotswapFlag} -da"
export JAVA_OPTIONS
fi
6. Locate the EXTRA_JAVA_PROPERTIES section of the file. In it, remove the following string:
-Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger
7. Save and close the file. Start the Administration Server and (if appropriate) SOA Server.

==Detailed Steps End Here==



(10)

Perform Configuration Steps in GRC Manage Application Page.

== Detailed Steps (10) ==

GRC Configuration

Regardless of whether you use WebLogic or Tomcat, open a Manage Application Configurations page to perform GRC-specific configuration:

1. Access GRC at
http://host:port/grc
In this URL, replace host with the FQDN of your GRC server. Select one of the following values for port:
• If you use WebLogic and are installing GRC to run FAACG, enter the port number you chose for the GRC managed server as you created a WebLogic domain. (See step 8 of “Creating a WebLogic Domain” on page 2-5.)
• If you use WebLogic and are performing any other installation, enter the port number you chose for the Administration Server as you created a WebLogic domain. (See step 7 of “Creating a WebLogic Domain” on page 2-5.)
• If you use Tomcat, replace port with 8080 (if you accepted the default value when you installed Tomcat) or your configured value (if you changed the default during Tomcat installation).
2. A ConfigUI page appears. In the Installation Configuration section, type or select appropriate property values:
• User Name: Supply the user name for the GRC database.
• Password: Supply the password for the GRC database.
• Confirm Password: Re-enter the password for the GRC database.
• Port Number: Supply the port number at which the GRC database server communicates with other applications.
• Service Identifier: Supply the service identifier (SID) for the GRC database server, as configured in the tnsnames.ora file. Or, if your GRC database sup-ports RAC, enter the RAC service name configured for your RAC database.
• Server Name: Supply the FQDN of the database server. Or, if your GRC database supports RAC, enter RAC@, where is the IP address/host name of the SCAN address configured for your RAC database.
• Maximum DB Connections: Default is 50. You can edit this value.
• Report Repository Path: Supply the full path to the Report Repository directory discussed in “Creating GRC Repositories” on page 2-3.
Log Threshold: Select a value that sets the level of detail in log-file entries. From least to greatest detail, valid entries are error, warn, info, and debug.
• Transaction ETL Path: Enter the full path to the directory you created to hold ETL data used by Enterprise Transaction Controls Governor (see “Creating GRC Repositories” on page 2-3).
• App Server Library Path: Enter the full path to the library subdirectory of your web application server (for use in the upload of custom connectors for AACG). If you are installing GRC to run FAACG, set this value to /grc/WEB-INF/lib.
3. In the Language Preferences section of the ConfigUI page, select the check boxes for up to twelve languages in which you want GRC to be able to display information to its users.
4. In the Performance Configuration section of the ConfigUI page, select or clear check boxes:
• Optimize Distributed Operation: Select the check box to increase the speed at which GRC performs distributed operations such as data synchronization.
• Optimize Appliance-Based Operation: Select the check box to optimize performance if the GRC application and GRC schema reside on the same machine. Do not select this check box if the GRC application and schema do not reside on the same machine. When you select this check box, an ORACLE_HOME Path field appears. In it, enter the full, absolute path to your Oracle Home — the directory in which you have installed the Oracle data-base that houses the GRC schema.
• Enable Era-Based ETL Optimization: “Data synchronization” enables both ETCG and AACG to recognize data changes in the business applications subject to their models and controls. For ETCG only, select this check box to cause synchronization to operate only on data entered in business applica-tions after a specified date.
When you select this check box, an Analysis Start Date field appears. In it, enter a date from which you want synchronization runs to recognize data changes. When you click in the field, a pop-up calendar appears. Click left- or right-pointing arrows to select earlier or later months (and years), and then click on a date in a selected month.
• Externalize Report Engine: Select the check box to enable the reporting engine to run in its own java process, so that the generation of large reports does not affect the performance of other functionality. However, select the check box only if you have installed GRC on hardware identified as “certi-fied” in the Oracle Enterprise Governance, Risk and Compliance Certifica-tions Document; clear the check box if you use hardware identified as “sup-ported.”
• Enable Parallel Processing: Select this check box to enable multiple models and controls to be processed simultaneously. However, this feature requires, at minimum, 16 GB of RAM; 24 GB is preferred.
When you select the Enable Parallel Processing check box, two fields appear. In a Number of Cores Available for Processing field, enter the number of processor cores you wish to devote to parallel processing; one core is devoted
to each model or control selected for analysis, until as many cores as you select are in use. In a Maximum Megabytes of Physical RAM Available field, specify an amount of memory for use in parallel processing. As a rule of thumb, enter total RAM minus 8 GB; you may need to adjust this value if other processes run slowly.
• Enforce Allocated Analysis Time Per Filter: Select this check box, and enter a number in the Minutes field, to limit the time that transaction models and controls can run.
A model or control consists of filters, each of which defines some aspect of a risk and selects transactions that meet its definition. When the Allocated Analysis Time feature is enabled, each filter runs no longer than the number of minutes you specify. If time expires, the filter passes records it has selected to the next filter for analysis, but ignores records it has not yet examined. So a filter may not capture every record that meets its definition, and the model or control results are labeled “partial” in GRC job-management pages.
Once enabled here, this feature may be disabled for individual models (and for the controls developed from those models). This feature applies only to transaction models and controls, not to access models and controls, and not to EGRCM objects.
5. In the ConfigUI page, click on Actions → Save. GRC tests the values you’ve entered and, if they are valid, saves them. (If any are invalid, an error message instructs you to re-enter them.)
6. Exit the ConfigUI page.

==Detailed Steps End Here==

(11)

Run Weblogic to Complete Installation.

== Detailed Steps (11) ==

With components in place and properly configured, complete the installation, in effect by running your web application server.
1. Shut down your server — the Administration Server if you’re using WebLogic, or the Tomcat application server if you’re using Tomcat. Then restart the server.
2. In a web browser, enter the GRC URL (see step 1 of “GRC Configuration” on page 2-18).
3. Wait for a pop-up message to report, “Database upgrade and initialization process complete.” Click on its OK button.
4. You are redirected to a GRC logon page. Log on to the application. For a fresh installation, use the default logon values admin for user ID and admin for password. GRC requires you to change the password the first time you log on. If you are upgrading, you can use the password established for the previous version.
If you are installing GRC without SOA and have not set up an external OID LDAP repository to manage users, basic GRC installation is complete. (You may, however, choose to set up SSL, embed GRCI, or complete other procedures described later in this chapter and in following chapters.)

If you have installed GRC with SOA (or to run with a pre-existing SOA), or if you have set up an OID LDAP repository, complete some additional steps:
1. If you use SOA, ensure that the SOA Server is up and running. (If you have installed GRC with SOA, the SOA Server is the managed server discussed in step 8 of “Creating a WebLogic Domain,” page 2-5. If you have installed GRC to run with a pre-existing SOA instance, this is the SOA server created for that instance.)
2. In GRC, select Navigator → Setup and Administration → Setup → Manage Application Configurations.
3. If you need to configure SOA, select the Worklist tab and enter these values:
• Worklist Server User Name: Keep the default value, soaadmin.
• Worklist Server Password. Enter the password you created for the soaadmin user (see step 3 of “Creating the SOA Admin User and Enabling Embedded LDAP” on page 2-13).
• Worklist Server Confirm Password: Re-enter the Worklist Server Password.
• Worklist Server URL: http://host:port, in which host is the IP address of your SOA server, and port is its port number.
• Worklist Server Protocol: Select the communications protocol —SOAP or RMI — used by the GRC application to send and receive SOA requests.
4. If you need to configure external OID LDAP, select the User Integration tab and enter the following values:
• Enable Single Sign On: Select the check box to make use of Single Sign On, which establishes a single set of log-on credentials for each user in varying applications. (Or, clear the check box if you do not wish to use Single Sign On.)
• Enable Integration: Select the check box to permit integration with LDAP to occur.
• User Name: Supply the user name (common name) to log in to the LDAP server. This user should have admin privileges. (This is the value specified for “Principal” in step 7 of “Configuring External OID LDAP,” page 2-6.)
• Password: Enter the password for the user identified in the User Name field (established in step 7 of “Configuring External OID LDAP”).
• Confirm Password: Re-enter the password for the user identified in the User Name field.
• Port Number: Enter the port number at which the LDAP server communi-cates with other applications (established in step 7 of “Configuring External OID LDAP”).
• Server Name: Enter the host name of the LDAP server. (This is the “Host” value from step 7 of “Configuring External OID LDAP.”)
• Bind DN Suffix: Enter the “User Base DN” from step 7 of “Configuring External OID LDAP.”
• Enable SSL Authentication: Select the box to allow GRC to access the LDAP server through SSL. The LDAP server must be configured to support SSL.
2-22 Oracle Enterprise Governance, Risk and Compliance Installation Guide
• Perform LDAP Recursive Search: Select the check box to search recursively for users in subfolders along with those in the base path specified in the Bind DN Suffix field.
• Unique User Identifier: uid
5. In the Manage Application Configurations page, click on Actions → Save. Then log off of GRC.
6. Stop the GRC Deployment in the WebLogic Console:
a Log in to the WebLogic Console at
http://host:port/console
Replace host with the FQDN of your GRC server, and port with the number you selected for the WebLogic Administration Server. (See step 7 of “Creating a WebLogic Domain” on page 2-5.)
b From the Domain Structure menu, select Deployments.
c From the Deployment page, locate the GRC deployment and verify the state is Active.
d Click the checkbox next to the GRC deployment.
e From the toolbar, click Stop → Force Stop Now.
7. Start the GRC Deployment in the WebLogic Console:
a From the Domain Structure menu, select Deployments.
b From the Deployment page, locate the GRC deployment and verify the state is Prepared.
c Click the checkbox next to the GRC deployment.
d From the toolbar, click Start → Servicing All Requests.

== Detailed Steps End Here==

(12)

Skip the SSL Configuration of GRC Weblogic Admin Sever at this time. SSL should be enabled after GRCI is implemented to avoid issue with GRCI installation.


(13)

Accessing the GRC

== Detailed Steps (13) ==

After configuring SSL successfully, access the GRC application at https://host:SSLport/grc, in which host is the FQDN of your GRC server, and SSLport is the port you selected to support SSL. (If you use WebLogic, see step 7 on page 2-24. If you use Tomcat, see step 4 above.)
Because you are using a self-signed certificate, which is not signed by an official Certificate Authority, you get a security warning when you open GRC at this URL.
• For Internet Explorer, the warning reads, "There is a problem with this website’s security certificate.” Dismiss this warning by choosing “Continue to this website (not recommended).”
• For Firefox, the warning reads, “This Connection is Untrusted.” Dismiss this warning by clicking “I Understand the Risks,” and then “Add Exception.”

== Detailed Steps End Here==

GRCI 8.6.4.5000 Step by Step Installation will follow in next article.