Monthly Archives: July 2005

Cloning Multi Node To Single Node

Posted by on July 28, 2005 No comments

This document briefly outlines the procedure for cloning an 11i multi node instance to a single node instance.

Assumptions
1. SOURCE Instance is split on 2 NODES:
SOURCE DB NODE – Both APPS and DB services installed on 1st Server
Application tier services are shut down
SOURCE APPS NODE – Both APPS and DB services installed on 2nd Server
Application tier services are started
2. TARGET instance is to be a single node (may be a new machine or one of the above)

Summary

Shut down the apps server
Start all services on db server
Run autoconfig to convert to instance to a single node
Run the preclone perl scripts
Copy files to new location
Convert source to 2 nodes by running autoconfig
Run clone scripts on Target

Cloning SOURCE Instance

1. Refer to the latest version of the cloning documentation on Metalink (Metalink Doc ID: 230672.1) to determine which patches to apply before cloning.

2. Shutdown the application server by running the adstpall.sh script:
cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finsvr/
./adstpall.sh apps/apps

Log off source APPS node
Log on source DB node

Make multi node a single node to begin cloning.
1. cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finsvr

2. Start all application services on database Source DB Node using adstrtal.sh:
cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finsvr
./adstrtal.sh apps/apps

Even though the listener and concurrent manager are the only services started on PROD, you will need to ensure that you start ALL services. Verify that the Concurrent Manager is up.

3. Run autoconfig:
cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finsvr
./adautocfg.sh

Read prompt carefully, choosing default responses where possible.

Migrating to AutoConfig on the Source DB Node (necessary only if a new rapid clone patch is applied).

4. cd /oraprod/oracle/prodappl/ad/11.5.0/bin (that is, $AD_TOP/bin)

Create appsutil.zip file:
perl admkappsutil.pl

appsutil.zip created in $APPL_TOP/admin/out

On the database tier (finmgr), copy or FTP the appsutil.zip file to the RDBMS_ORACLE_HOME.

5. Copy file appsutil.zip to /oraprod/oracle/proddb/9.2.0/*
unzip -o appsutil.zip

Generate and Apply AutoConfig Configuration files
This step performs the conversion to AutoConfig. Once completed, the previous configuration will not be available. The database server and the database listener must remain available during the AutoConfig run. All other database tier services should be shut down.

Warning: Running AutoConfig on the database node will update the RDBMS network listener file. Save the listener file before you run autoconfig.

7. Change directory to RDBMS_ORACLE_HOME/appsutil/bin
cd /oraprod/oracle/proddb/9.2.0/appsutil/bin

adconfig.sh contextfile=PROD_finmgr.xml appspass=apps*

8. Prepare the source system database tier for cloning
cd /oraprod/oracle/proddb/9.2.0/appsutil/scripts/PROD_finmgr (that is, $RBDMS_ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl adpreclone.pl db Tier

9. Prepare the source system application tier for cloning
cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finmgr (that is, $COMMON_TOP/admin/scripts/$CONTEXT_NAME)
perl adpreclone appsTier

10. Stop all services (DB and APPS)
cd /oraprod/oracle/prodcomn/admin/scripts/PROD_finmgr/
./adstpall.sh apps/apps
Shutdown database listener (addlnctl.sh stop apps/apps)
Shutdown database (addbctl.sh stop apps/apps)
Or run the stopDB script in /oraprod/scripts (doing so will attempt to shut the services again)

11. Copy files
Create the respective folders on target node:
clondb
clondata
clonora
clonappl
cloncomn

Copy the files from Source DB Node to Target Node. As root user, mount source node on target Node by editing /etc/exports on target node and adding the line where the source files are located (i.e. /oraprod/oracle).

Then restart NFS on the target server by entering the following command at the command prompt (as root user):
service nfs restart

12. On the target node (that is, where the clone is being created), do the following:
a. su – applmgr
b. cd /orapatch (new clone top directory)
c. mkdir prodfiles (where we want the files copied to)
d. su – root
e. Mount SOURCE directory onto TARGET directory
f. Copy the files using the operating system utilities

May need to ensure that the owner name and group of target directories are applmgr and dba respectively and not nfsmount.

To convert from single node to multi node:

Run autoconfig on Source Apps Node to make Source Instance 2 Node.

When a report is run on the target instance we get an error: “File server could not verify its initialization parameters when viewing output”. Patch 4244610 was applied, but this did not resolve the issue.

Removed spaces from listener.ora file in /orapatch/oracle/clonora/8.0.6/network/admin/clon_newtest/listener.ora. Removed all spaces near the APPLFSTT parameter. Removed everything and left reference to database alone. Bounce listener.

To change color and name, go to SysAdmin/ Security/Profile/System, under profile %Site%, change Site Name to new name. To change color, navigate to profile %Color% JavaColorScheme and Service:Visual Attributes for Settings where the colour can also be changed.

Setting Up Oracle Alerts To Send Email Notifications

Posted by on July 27, 2005 No comments

This document shows how to set up Oracle Alerts to send email notifications.

1. Navigate to Alerts and enter the following information:

Application: Application Object Library
Description: Send email Notification
Name: Send Email Notification

Under Periodic > Periodic Details
Frequency: Every N Calendar Days
Days: 1
Start Time: 07:00:00
End Time: 18:00:00
Check Interval: 00:30:00

Keep: 2 Days
End Date:

Select Statement:

select w.notification_id,
w. group_id,
w.message_type,
w.message_name,
rtrim(f.email_address),
substr(f.description,1,30) name,
w.to_user,
substr(w.subject,1,100) message_text
INTO
&notification_id,
&group_id,
&message_type,
&message_name,
&email_address,
&name,
&to_user,
&message_text
from
wf_notifications w,
fnd_user f
where
w.recipient_role = f.user_name and
w.status=’OPEN’ and
trunc(begin_date) = trunc(sysdate)

2. Click the Actions button.

3. Enter the following information:

Actions – Send Email Notification
Action Name: Email User
Description: Send User an email
Action Level: Detail

Action Details – Email User

To: &EMAIL_ADDRESS
Subject: PROD NOTIFICATION:&NOTIFICATION_ID – &MESSAGE_TYPE – &MESSAGE_NAME

Select Text.

Enter the following text into the textbox:

&NAME

Please log in to Oracle Applications and check your Notifications. &MESSAGE_TEXT

Link to Oracle:
http://appsserver.serverdomain.com:8000/OA_HTML/US/ICXINDEX.htm

This is an unattended messaging system. Please do NOT respond to this email.

Oracle Applications

4. Action Sets – Send Email Notification

Seq: 1
Action Set Name: Email Notification
Suppress Duplicates:
Enable:

5. Alert Details – Send Email Notification

Under the Outputs tab:

Output Name: NOTIFICATION_ID
Description: NOTIFICATION_ID
Detail:
Summary:

Output Name: GROUP_ID
Description: GROUP_ID
Detail:
Summary:

Output Name: MESSAGE_TYPE
Description: MESSAGE_TYPE
Detail:
Summary:

Output Name: MESSAGE_NAME
Description: MESSAGE_NAME
Detail:
Summary:

Output Name: EMAIL_ADDRESS
Description: EMAIL_ADDRESS
Detail:
Summary:

Output Name: NAME
Description: NAME
Detail: 40
Summary: 40

Output Name: TO_USER
Description: TO_USER
Detail:
Summary:

Output Name: MESSAGE_TEXT
Description: MESSAGE_TEXT
Detail: 150
Summary: 150

Setting Up Cash Management Open Interface

Posted by on July 27, 2005 No comments

In SQL*Plus, execute script ce_999_int.sql as user apps to create the interface table.
Ensure that the view was not changed before running script.
After running script, ensure that there are no invalid objects for owner CE.

#ce_999_int.sql
 create table ce.CE_999_INTERFACE
 (
 TRX_ID Number(15) unique,
 BANK_ACCOUNT_ID NUMBER(15) not null,
 TRX_TYPE VARCHAR2(20) not null,
 TRX_TYPE_DSP VARCHAR2(80) not null,
 TRX_DATE DATE not null,
 CURRENCY_CODE VARCHAR2(15) not null,
 STATUS VARCHAR2(15) not null,
 STATUS_DSP VARCHAR2(80) not null,
 AMOUNT NUMBER(17,2) not null,
 GL_DATE DATE not null,
 CREATION_DATE DATE not null,
 CREATED_BY NUMBER(15) not null,
 LAST_UPDATE_DATE DATE not null,
 LAST_UPDATED_BY NUMBER(15) not null,
 TRX_NUMBER VARCHAR2(15),
 EXCHANGE_RATE_TYPE VARCHAR2(30),
 EXCHANGE_RATE_DATE DATE,
 EXCHANGE_RATE NUMBER(17,2),
 CLEARED_AMOUNT NUMBER(17,2),
 CHARGES_AMOUNT NUMBER(17,2),
 ERROR_AMOUNT NUMBER(17,2),
 ACCTD_AMOUNT NUMBER(17,2),
 ACCTD_CLEARED_AMOUNT NUMBER(17,2),
 ACCTD_CHARGES_AMOUNT NUMBER(17,2),
 ACCTD_ERROR_AMOUNT NUMBER(17,2),
 CLEARED_DATE DATE
 )
 /
create or replace view apps.CE_999_INTERFACE_V
 as select
 ROWID ROW_ID,
 TRX_ID,
 BANK_ACCOUNT_ID,
 TRX_TYPE,
 TRX_TYPE_DSP,
 TRX_NUMBER,
 TRX_DATE,
 CURRENCY_CODE,
 STATUS,
 STATUS_DSP,
 AMOUNT,
 GL_DATE,
 CREATION_DATE,
 CREATED_BY,
 LAST_UPDATE_DATE,
 LAST_UPDATED_BY,
 EXCHANGE_RATE_TYPE,
 EXCHANGE_RATE_DATE,
 EXCHANGE_RATE,
 CLEARED_AMOUNT,
 CHARGES_AMOUNT,
 ERROR_AMOUNT,
 ACCTD_AMOUNT,
 ACCTD_CLEARED_AMOUNT,
 ACCTD_CHARGES_AMOUNT,
 ACCTD_ERROR_AMOUNT,
 CLEARED_DATE
 from CE.CE_999_INTERFACE
 /
 accept database char prompt 'Enter database to connect to: '
 connect apps/apps@&database
 rem compile all invalid database objects
 alter view CE_999_RECONCILED_V compile
 /
 alter view CE_999_TRANSACTIONS_V compile
 /
 alter view CE_AVAILABLE_TRANSACTIONS_V compile
 /
 alter view CE_RECONCILED_TRANSACTIONS_V compile
 /
 alter package CE_AUTO_BANK_CLEAR compile body
 /
 alter package CE_AUTO_BANK_MATCH compile body
 /

2. Start Enterprise Manager
On the database server, change directory to db/9.2.0
Source the database environment
run $oemapp dbastudio

Select the APPS schema
Select the CE_999_PKG body
Enable the clear and unclear procedures
Compile procedure bodies

Bank Statement SQL Loader Scripts

Posted by on July 27, 2005 No comments

Before uploading bank statement file (SQL Loader data file), ensure that the data file exactly maps to the lines.ctl and the headers.ctl.

Sample control files (Files may need to altered to best match statement)
 # HEADER.CTL
LOAD DATA
APPEND INTO TABLE ce_statement_headers_int_all
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX',
#ADD BANK ACCT NUMBER ORG_ID CONSTANT '23',
#DETERMINE ORG ID STATEMENT_NUMBER,
STATEMENT_DATE "TO_DATE(:STATEMENT_DATE,'YYYYMMDD')",
DUMMY_COLUMN FILLER,
DUMMY_COLUMN2 FILLER,
DUMMY_COLUMN3 FILLER,
DUMMY_COLUMN4 FILLER,
DUMMY_COLUMN5 FILLER,
DUMMY_COLUMN6 FILLER,
DUMMY_COLUMN7 FILLER,
DUMMY_COLUMN8 FILLER,
DUMMY_COLUMN9 FILLER,
DUMMY_COLUMN10 FILLER,
CURRENCY_CODE CONSTANT 'TTD' )

# LINES.CTL
load data
append into table ce_statement_lines_interface
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( STATEMENT_NUMBER,
TRX_DATE "TO_DATE(:TRX_DATE, 'YYYYMMDD')",
BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX',
#ADD BANK ACCT NUMBER LINE_NUMBER SEQUENCE(COUNT),
currency_code constant 'TTD',
dummy_filler2 filler,
dummy_filler3 filler,
dummy_filler4 filler,
dummy_filler1 filler,
TRX_CODE,
TRX_TEXT,
AMOUNT,
BANK_TRX_NUMBER,
dummy_filler5 filler,
dummy_filler6 filler )

Save the data file as a .csv file.

Note:

To determine org_id, issue the following command in SQL*Plus:

Select ORGANIZATION_ID, NAME From HR_ALL_ORGANIZATION_UNITS;