Discoverer 10.1.2 with Oracle E-Business 11i & R12

Discoverer 10.1.2 with Oracle E-Business 11i & R12:

Step 1:

Edit below parameters on $CONTEXT_FILE

For 11i:

<discoinstancename oa_var="s_discoinstance">devsoadl1.singiri.com_<portno></discoinstancename>
         <disco_machine oa_var="s_disco_machine"> devsoadl1.singiri.com </disco_machine>
         <disco_port oa_var="s_disco_port" oa_type="PORT"><portno></disco_port>

        <oa_process_status oa_var="s_discostatus" osd="unix">disabled</oa_process_status>

For R12:

The Disco Url Variable Name : “ s_disco_url”

<disco_url oa_var="s_disco_url" customized="yes">http:// devsoadl1.singiri.com:6669</disco_url>

         <disco_eul_prefix oa_var="s_disco_eul_prefix">EUL</disco_eul_prefix>

Note: As Webcache is the the entry point for discoverer, we have to use the Webcache SSL or Non-SSL port in "s_disco_url". You can get the appropriate Webcache port by keying in the command "opmnctl status -l"

Step 2:

Run AutoConfig to generate all configuration files for the APPL_TOP and the ORACLE_HOMEs in its supporting technology stack:

Step 3:

Update tnsnames.ora file

Step 4:

Set Applications profile options in Oracle E-Business Suite

Log into E-Business Suite Release 12 with the System Administrator responsibility

Navigate to the Profile > System form

Query the %Discoverer% profile options.

Ensure the following ICX profile options in Oracle E-Business Suite are set to:

Profile	Description	Value
ICX: Discoverer Launcher	URL that points to Discoverer Plus Servlet.	http://<HTTP Server>:<Port> /discoverer/plus ?Connect=[APPS_SECURE]
ICX: Discoverer Viewer Launcher	URL that points to Discoverer Viewer Servlet	http://<HTTP Server>:<Port> /discoverer/viewer ?Connect=[APPS_SECURE]
ICX: Discoverer Release	Single number specifying the Discoverer release	10
ICX: Discoverer use Viewer	Specify whether Discoverer Viewer should be launched instead of Discoverer Plus (default).	Yes | No
ICX: Discoverer Default End User Layer Schema Prefix	The EUL prefix in combination with the Language code make up the EUL owner at runtime. For example EUL owner EUL_US has EUL prefix EUL.	EUL
ICX: Discoverer End User Layer Language Override	Since the End User Layer content is currently available in US English only, it is possible to override the user's general language preference for the Discoverer End User Layer using this profile option. The specified End User Layer language will be used regardless of the individual user's language preferences. This profile does not affect the National Language Support parameters passed to Discoverer. The Discoverer User Interface will still appear according to the users ICX_LANGUAGE and ICX_TERRITORY profiles. The User Interface language will only change for valid combinations of ICX_LANGUAGE and ICX_TERRITORY.	American English
ICX: Discoverer Parameters	This profile can be used to optionally pass additional URL parameters to Discoverer.	<NULL>
Discoverer DBC filename override	This profile can be used to specify the DBC Filename Discoverer should be using to connect to the E-Business Suite database. By default, this profile should remain NULL, in which case Discoverer will use the default dbc file for your E-Business Suite database.	<NULL>

Step 5:

To verify the values

#sqlplus apps/<apps password>

set linesize 140

set pagesize 132

column NAME format A40

column VALUE format A80 wrap

select  

n.user_profile_option_name NAME,

v.profile_option_value VALUE

from apps.fnd_profile_options p,

                 apps.fnd_profile_option_values v,

                apps.fnd_profile_options_tl n

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

and v.level_id=10001

AND n.LANGUAGE='US'

and upper(n.user_profile_option_name) like upper('%&profile_name%');

Enter value for profile_name: ICX: Discoverer

NAME                                     VALUE

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

ICX: Discoverer Launcher  http://devsoadl1.singiri.com:<port>/discoverer/plus?Connect=APPS_SECURE]

ICX: Discoverer Default End User Layer Schema Prefix            EUL

ICX: Discoverer Viewer Launcher  http://devsoadl1.singiri.com:<port>/discoverer/viewer?Connect=[APPS_SECURE]

ICX: Discoverer use Viewer                                                     Y

ICX: Discoverer Release                                                          10

ICX: Discoverer End User Layer LanguageOverride               US

Step 6:

Copy Database Connection file

$FND_SECURE/<two_task>.dbc

$FND_SECURE/<database host name>_<two_task>.dbc

make a copy of your dbc file on your OracleBI Discoverer node $FND_SECURE directory and rename the file to match above default naming convention. Also make sure that the dbc file is saved in lower case only.

 for  EUL_US version :

SQL> select ver_min_code_ver from EUL_US.eul5_versions;

VER_MIN_CODE_VER

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

10.1.2.45.20

DISCOVER Node: Linux users Only:

Replace the line LD_ASSUME_KERNEL= 2.4.19 with following lines in $ORACLE_HOME/discoverer/discwb.sh before Import/Refreshing the EUL.

+++++++++++++++++++++++++++++++++++++++++++++

Note:   

ID 373634.1 , 762870.1

DB Duplicate From PROD To TEST:

DB Duplicate From PROD To TEST:

=========================

 Step 1:

Create password file on PROD , then copy same file to TEST

orapwd file=orapw$ORACLE_SID password=welcome

Step 2:

Prod db should be archive log mode…

Step 3:

Before firing disk backup script, check the backup location is available or not -à /BACKUP/rman_db_backup/PROD

[oracle@prodebsdl1 scripts]$ cat rman_hot_backup_disk.sh

rman target /

spool log to '/home/oracle/rman_db_backup/logs/PROD_disk_hot_backup.log' append;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'CF_snapshot_PROD_May06.bak';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/rman_db_backup/PROD/CF_%F_PROD_May06.bak';

Run{

sql 'alter system archive log current';

sql 'alter system switch logfile';

backup FORMAT  '/BACKUP/rman_db_backup/PROD/DF_%t_%u_%s_%p_%N_HRPROD_May06.bak' database;

sql 'alter system archive log current';

backup FORMAT '/BACKUP/rman_db_backup/PROD/AF_%e_%t_%u_%s_%p_afterbu_PROD_May06.bak' archivelog all;

sql 'alter system archive log current';

backup FORMAT '/BACKUP/rman_db_backup/PROD/AF_%e_%t_%u_%s_%p_afterbu_PROD_May06.bak' archivelog all not backed up;

sql 'alter database backup controlfile to trace';

}

spool log off;

quit;

EOF

TESTDB:

Step 1:

Please check backup location is mounted,

Step:

Remove all the datafiles

Step3:

DB should be  nomont

# Start the instance for duplicate database

sqlplus / as sysdba << END_SQL

startup nomount

END_SQL

Step 4: Connect to Target

rman target sys/welcome@PROD auxiliary /

Step 5:

Run the bellow script on Rman prompt

[oracle@devebsdl1 scripts]$ cat TEST.sql

RUN

{

ALLOCATE AUXILIARY CHANNEL disk1 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL disk2 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL disk3 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL disk4 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL disk5 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL disk6 DEVICE TYPE disk;

DUPLICATE DATABASE TO HRTST BACKUP LOCATION '/BACKUP/rman_db_backup/PROD/' NOFILENAMECHECK

DB_FILE_NAME_CONVERT=('PROD','TEST')

LOGFILE

  GROUP 1 (

    '/u01/oracle/TEST/db/apps_st/data/log01a.dbf',

    '/u01/oracle/TEST/db/apps_st/data/log01b.dbf'

  ) SIZE 1000M,

  GROUP 2 (

    '/u01/oracle/TEST/db/apps_st/data/log02a.dbf',

    '/u01/oracle/TEST/db/apps_st/data/log02b.dbf'

  ) SIZE 1000M;

RELEASE CHANNEL disk1;

RELEASE CHANNEL disk2;

RELEASE CHANNEL disk3;

RELEASE CHANNEL disk4;

RELEASE CHANNEL disk5;

RELEASE CHANNEL disk6;

}

++++++++++++++++++++++++++++++++++++++++++++++

How to Run adcfgclone with dbTechStack :

[oracle@svrqatebsdl1 bin]$cd /d02/oracle/qatdb/11.2.0.3/appsutil/clone/bin

[oracle@svrqatebsdl1 bin]$ perl adcfgclone.pl dbTechStack

Enter the APPS password [APPS]:

apps

First Creating a new context file for the cloned system.

The program is going to ask you for information about the new system:

Provide the values required for creation of the new Database Context file.

Do you want to use a virtual hostname for the target node (y/n) [n] ?:n

Target instance is a Real Application Cluster (RAC) instance (y/n) [n]: n

Target System database name [PROD]:QAT

Target system RDBMS ORACLE_HOME directory [/d02/oracle/proddb/11.2.0.3]:/d02/oracle/qatdb/11.2.0.3

Target system utl_file accessible directories list [/usr/tmp]:

Number of DATA_TOP's on the target system [3]:5

Target system DATA_TOP 1:/u01/oradata/QAT

Target system DATA_TOP 2:/u02/oradata/QAT

Target system DATA_TOP 3:/u03/oradata/QAT

Target system DATA_TOP 4:/u04/oradata/QAT

Target system DATA_TOP 5:/u04/oradata/QAT

Do you want to preserve the Display set to svrprdebsdl1:0.0 (y/n) [y] ?:n

Target system Display [svrqatebsdl1:0.0]:

Perl executable location is set to:

/usr/bin/perl

Do you want to preserve the port values from the source system on the target system (y/n) [y] ?:n

Clone Context uses the same port pool mechanism as the Rapid Install

Once you choose a port pool, Clone Context will validate the port availability.

Enter the port pool number [0-99]:

2

Checking the port pool 2

done: Port Pool 2 is free

Database port is 1523

Creating the new Database Context file from :

  /d02/oracle/qatdb/11.2.0.3/appsutil/template/adxdbctx.tmp

The new database context file has been created :

  /d02/oracle/qatdb/11.2.0.3/appsutil/QAT_svrqatebsdl1.xml

Log file located at /tmp/CloneContext_1028043505.log

Running Rapid Clone with command:

        perl /d02/oracle/qatdb/11.2.0.3/appsutil/clone/bin/adclone.pl java=/d02/oracle/qatdb/11.2.0.3/appsutil/clone/bin/../jre/jre mode=apply stage=/d02/oracle/qatdb/11.2.0.3/appsutil/clone/bin/.. component=dbTechStack method=CUSTOM dbctxtg=/d02/oracle/qatdb/11.2.0.3/appsutil/QAT_svrqatebsdl1.xml  showProgress contextValidated=true

Beginning rdbms home Apply - Sun Oct 28 04:38:25 2012

Log file located at /d02/oracle/qatdb/11.2.0.3/appsutil/log/QAT_svrqatebsdl1/ApplyDBTechStack_10280438.log

                                          

Completed Apply...

Sun Oct 28 04:39:15 2012

Beginning APPSDB_QAT registration to central inventory...

ORACLE_HOME NAME   : APPSDB_QAT

ORACLE_HOME PATH   : /d02/oracle/qatdb/11.2.0.3

Using Inventory location in /etc/oraInst.loc

Log file located at /home/oracle/oraInventory/logs/OracleHomeCloner_10280439.log

ERROR: Registration Failed... Please check log file.

You can rerun this registration with the following script:

        /d02/oracle/qatdb/11.2.0.3/appsutil/out/QAT_svrqatebsdl1/regOUI_APPSDB_QAT.sh

Starting database listener for QAT:

Running:

/d02/oracle/qatdb/11.2.0.3/appsutil/scripts/QAT_svrqatebsdl1/addlnctl.sh start QAT

You are running addlnctl.sh version 115.7

Logfile: /d02/oracle/qatdb/11.2.0.3/appsutil/log/QAT_svrqatebsdl1/addlnctl.txt

Starting listener process QAT ...

addlnctl.sh: exiting with status 0

[oracle@svrqatebsdl1 bin]$

Note:

ERROR: Registration Failed... ... ...

Soluton:

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/d02/oracle/qatdb/11.2.0.3" ORACLE_HOME_NAME="oracleHome" CLUSTER_NODES="{}"

 If already registered, we can ignore this error ......

++++++++++++++++++++++++++++++++++++++