DBA & APPSDBA ANSWERS

TOP 100 SQL's for DBA Day-To-Day Tasks

2010-12-22T14:48:00.001-08:00

2010-02-15T11:20:00.000-08:00

PL/SQL API to Generate ReOrg SQL

2010-02-15T09:02:00.000-08:00

/*
This script needs to be run as system schema
*/
CREATE OR REPLACE PACKAGE info_gen_movescript_api
AS
PROCEDURE info_gen_movescript_api(source_tablespace_name in varchar2,target_tablespace_name in varchar2);
END info_gen_movescript_api;
/
CREATE OR REPLACE PACKAGE BODY info_gen_movescript_api
AS
PROCEDURE info_gen_movescript_api(source_tablespace_name in varchar2,target_tablespace_name in varchar2)
IS
CURSOR main IS select table_name from dba_tables where tablespace_name=source_tablespace_name;
CURSOR info_table(h_table_name varchar2) IS
SELECT 'alter table '||table_name||' move tablespace '||target_tablespace_name||'
storage (
initial '||initial_extent||'
next '||next_extent||'
minextents '||min_extents||'
maxextents '||max_extents||'
pctincrease '||pct_increase||'
)
pctfree '||pct_free||'
pctused '||pct_used||';' table_name
FROM dba_tables
WHERE table_name=h_table_name;
CURSOR info_index(c_table_name varchar2) IS
SELECT 'alter index '||index_name||' rebuild tablespace '||target_tablespace_name||'
storage (
initial '||initial_extent||'
next '||next_extent||'
minextents '||min_extents||'
maxextents '||max_extents||'
pctincrease '||pct_increase||'
)
pctfree '||pct_free||';' index_name
FROM user_indexes
WHERE table_name=c_table_name;
BEGIN
FOR for_main IN main
LOOP
FOR for_info_table IN info_table(for_main.table_name)
LOOP
INSERT INTO info_gen_move_tbl
(TEXT)
VALUES
(for_info_table.table_name);
END LOOP;
FOR for_info_index IN info_index(for_main.table_name)
LOOP
INSERT INTO info_gen_move_tbl
(TEXT)
VALUES
(for_info_index.index_name);
END LOOP;
END LOOP;
END info_gen_movescript_api;
END info_gen_movescript_api;
/
　
　

Generate ASM Config Info

2010-02-15T08:59:00.000-08:00

--Preared by Vijay.Cherukuri
--To get the full asm end-to-end info
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON
column path format a50
column name format a20
spool gen_fullasm_details.lst
prompt
prompt
prompt ***INSTANCE PARAMETERS***
select name||'===='||value from v$parameter where isdefault='FALSE';
prompt
prompt
prompt ***DISK GROUP INFO***
select name, state, type, total_mb, free_mb from v$asm_diskgroup;
prompt
prompt
prompt ***DISK INFO***
select name, path, mode_status, state, disk_number from v$asm_disk;
prompt
prompt
prompt ***DISK GROUP AND DISK INFO***
select a.group_number,a.mount_status,
a.redundancy,a.total_mb,a.free_mb,b.name,a.failgroup,
b.state,b.type,a.path
from
v$asm_disk a,
v$asm_diskgroup b
where
a.group_number=b.group_number
/
prompt
prompt
prompt ***ASM CLIENT INFO***
select INSTANCE_NAME,DB_NAME,STATUS,SOFTWARE_VERSION,COMPATIBLE_VERSION from v$asm_client;
prompt
prompt
prompt ***ASM FILE HIERARCHY INFO***
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;
exit;
　

Release 11i Customer Data Conversion

2010-02-15T08:54:00.000-08:00

Following API contains some of the pl/sql features such as cursors, cursor variables, exception handling, loop, error handling etc.

　
　
DROP TABLE spc_cust_error_table
/
CREATE TABLE spc_cust_error_table
(source_transaction_id varchar2(1000),
ERROR_TYPE varchar2(1000),
error_message varchar2(1000),
table_name varchar2(1000),
column_name varchar2(1000))
/
　
　
CREATE OR REPLACE PACKAGE spc_ar_cust_conv
AS
PROCEDURE spc_arcust_conv;
END spc_ar_cust_conv;
/
　
CREATE OR REPLACE PACKAGE BODY spc_ar_cust_conv
AS
spc_ccid NUMBER;
PROCEDURE spc_arcust_conv
IS
spc_error_msg_ex varchar2(1000) :=NULL;
spc_insert_update_flag varchar2(10) := 'I';
v_err_msg VARCHAR2(100) := NULL;
spc_created_by NUMBER := 1085;
spc_last_update_date DATE := SYSDATE;
spc_creation_date DATE := SYSDATE;
spc_last_updated_by NUMBER := 1085;
spc_last_update_login NUMBER := NULL;
spc_custhead_record_status VARCHAR2(1000) := NULL;
spc_custprofile_record_status VARCHAR2(1000) := NULL;
spc_err_msg VARCHAR2(1000) := NULL;
spc_cust_profile_class_name VARCHAR2(1000) := NULL;
spc_dunning_letter_set_name VARCHAR2(1000) := NULL;
spc_grouping_rule_name VARCHAR2(1000) := NULL;
spc_standard_term_name VARCHAR2(1000) := NULL;
spc_statement_cycle_name VARCHAR2(1000) := NULL;
spc_autocash_hierarchy_name varchar2(1000) := NULL;
spc_collector_name varchar2(1000) := NULL;
spc_customer_class_code VARCHAR2(1000) := NULL;
spc_cust_tax_code VARCHAR2(1000) := NULL;
spc_site_use_tax_code VARCHAR2(1000) := NULL;
spc_cust_ship_via_code VARCHAR2(1000) := NULL;
spc_site_ship_via_code VARCHAR2(1000) := NULL;
spc_site_use_code VARCHAR2(1000) := NULL;
spc_orig_system_customer_ref VARCHAR2(1000) := NULL;
spc_orig_system_address_ref VARCHAR2(1000) := NULL;
spc_orig_system_contact_ref VARCHAR2(1000) := NULL;
spc_orig_system_telephone_ref VARCHAR2(1000) := NULL;
spc_charge_flag VARCHAR2(1000) := NULL;
spc_cust_class VARCHAR2(1000) := NULL;
spc_orig_system_parent_ref VARCHAR2(1000) := NULL;
spc_bill_to_site_ref VARCHAR2(1000) := NULL;
v_rule0 number := NULL;
v_rule1 number := NULL;
v_rule2 number := NULL;
v_rule3 number := NULL;
v_rule4 number := NULL;
v_rule5 number := NULL;
v_rule6 number := NULL;
v_rule7 number := NULL;
spc_tax_org_id NUMBER := NULL;
/***************************************************************************************************************************
First cursor for driving the second,third,fourth,fifth cursors
**************************************************************************************************************************/
CURSOR spc_cust_main
IS
SELECT customer_id,status FROM ra_customers@TRAIN.WORLD;
/***************************************************************************************************************************
Second cursor for converting the customers,customer addresses and the addresses business purposes.This cursor is dependent on First Cursor
Skip customers created 3 months back or earlier and not used on any converted Sales Order or Invoice
**************************************************************************************************************************/
　
　
CURSOR spc_custhead_cur(spc_header_customer_id number)
IS
SELECT
a.customer_id orig_system_customer_ref ,
b.address_id orig_system_address_ref ,
c.site_use_code ,
a.customer_number ,
a.customer_name ,
a.status customer_status ,
a.customer_type ,
c.location ,
b.address_id ,
b.address1 ,
b.address2 ,
b.address3 ,
b.address4 ,
b.city ,
b.state ,
b.province ,
b.county ,
b.country ,
b.postal_code ,
b.org_id ,
a.customer_category_code ,
a.customer_class_code ,
a.tax_code cust_tax_code ,
a.tax_reference cust_tax_reference ,
c.tax_code site_use_tax_code ,
c.tax_reference site_use_tax_reference ,
c.demand_class_code ,
a.ship_via cust_ship_via_code ,
c.ship_via site_ship_via_code ,
c.primary_flag primary_site_use_flag ,
a.last_update_login ,
a.last_updated_by ,
a.last_update_date ,
a.created_by ,
a.creation_date ,
a.customer_id ,
a.customer_name translated_customer_name ,
a.attribute1 customer_attribute1 ,
a.attribute2 customer_attribute2 ,
a.attribute3 customer_attribute3 ,
a.attribute4 customer_attribute4 ,
a.attribute5 customer_attribute5 ,
a.attribute6 customer_attribute6 ,
a.attribute7 customer_attribute7 ,
a.attribute8 customer_attribute8 ,
a.attribute9 customer_attribute9 ,
a.attribute10 customer_attribute10 ,
a.attribute11 customer_attribute11 ,
a.attribute12 customer_attribute12 ,
a.attribute13 customer_attribute13 ,
a.attribute14 customer_attribute14 ,
a.attribute15 customer_attribute15 ,
b.attribute1 address_attribute1 ,
b.attribute2 address_attribute2 ,
b.attribute3 address_attribute3 ,
b.attribute4 address_attribute4 ,
b.attribute5 address_attribute5 ,
b.attribute6 address_attribute6 ,
b.attribute7 address_attribute7 ,
b.attribute8 address_attribute8 ,
b.attribute9 address_attribute9 ,
b.attribute10 address_attribute10 ,
b.attribute11 address_attribute11 ,
b.attribute12 address_attribute12 ,
b.attribute13 address_attribute13 ,
b.attribute14 address_attribute14 ,
b.attribute15 address_attribute15 ,
b.attribute_category address_attribute_category ,
a.attribute_category customer_attribute_category ,
c.bill_to_site_use_id bill_to_orig_address_ref ,
c.site_use_id old_site_use_id ,
b.status address_status
FROM
ra_customers@TRAIN.WORLD a,
ra_addresses_all@TRAIN.WORLD b,
ra_site_uses_all@TRAIN.WORLD c
WHERE a.customer_id= b.customer_id(+)
AND b.address_id= c.address_id(+)
AND a.customer_id=spc_header_customer_id;
　
　
/***************************************************************************************************************************
Third cursor for converting the customer profiles information and profile amounts info.
This cursor is dependent on First Cursor
**************************************************************************************************************************/
　
CURSOR spc_custprofile_cur(spc_profile_customer_id number) IS
SELECT
a.account_status ,
a.customer_id ,
a.auto_rec_incl_disputed_flag ,
a.autocash_hierarchy_id ,
b.auto_rec_min_receipt_amount ,
nvl(a.charge_on_finance_charge_flag,'N') charge_on_finance_charge_flag ,
a.clearing_days ,
a.collector_id ,
a.cons_inv_flag ,
a.cons_inv_type ,
a.credit_balance_statements ,
a.credit_hold ,
a.credit_rating ,
b.currency_code ,
a.customer_profile_id ,
a.discount_terms ,
a.credit_checking ,
a.dunning_letters ,
a.interest_charges ,
a.statements ,
a.discount_grace_days ,
a.dunning_letter_set_id ,
a.grouping_rule_id ,
a.interest_period_days ,
b.interest_rate ,
b.max_interest_charge ,
b.min_dunning_amount ,
b.min_dunning_invoice_amount ,
b.min_fc_balance_amount ,
b.min_fc_invoice_amount ,
b.min_statement_amount ,
a.customer_id orig_system_customer_ref ,
b.overall_credit_limit ,
a.override_terms ,
a.payment_grace_days ,
a.percent_collectable ,
a.risk_code ,
a.standard_terms ,
a.statement_cycle_id ,
a.tax_printing_option ,
a.tolerance ,
b.trx_credit_limit ,
c.name cust_profile_class_name ,
d.name dunning_letter_set_name ,
e.name grouping_rule_name ,
f.name standard_term_name ,
g.name statement_cycle_name ,
h.hierarchy_name autocash_hierarchy_name ,
i.name collector_name ,
j.address_id orig_system_address_ref ,
j.site_use_id
FROM
ar_customer_profiles@TRAIN.WORLD a,
ar_customer_profile_amounts@TRAIN.WORLD b,
ar_customer_profile_classes@TRAIN.WORLD c,
ar_dunning_letter_sets@TRAIN.WORLD d,
ra_grouping_rules@TRAIN.WORLD e,
ra_terms@TRAIN.WORLD f,
ar_statement_cycles@TRAIN.WORLD g,
ar_autocash_hierarchies@TRAIN.WORLD h,
ar_collectors@TRAIN.WORLD i,
ra_site_uses_all@TRAIN.WORLD j
WHERE
a.site_use_id =j.site_use_id(+)
AND
a.customer_profile_id =b.customer_profile_id(+)
AND
a.customer_profile_class_id =c.customer_profile_class_id(+)
AND
a.dunning_letter_set_id =d.dunning_letter_set_id(+)
AND
a.grouping_rule_id =e.grouping_rule_id(+)
AND
a.standard_terms =f.term_id(+)
AND
a.statement_cycle_id =g.statement_cycle_id(+)
AND
a.autocash_hierarchy_id =h.autocash_hierarchy_id(+)
AND
a.collector_id =i.collector_id(+)
AND
a.customer_id =spc_profile_customer_id ;
　
/***************************************************************************************************************************
Fourth cursor for converting the contacts for the customer,contacts for the customer address,contact phone numbers for the customer contact,contact phone numbers for the customer address contact.This cursor is dependent on First Cursor
**************************************************************************************************************************/
CURSOR spc_cust_contact_cur (spc_contact_customer_id NUMBER)
IS
SELECT a.customer_id orig_system_customer_ref ,
a.address_id orig_system_address_ref ,
a.orig_system_reference orig_system_contact_ref ,
b.orig_system_reference orig_system_telephone_ref ,
a.first_name contact_first_name ,
a.last_name contact_last_name ,
a.title contact_title ,
a.job_title contact_job_title ,
a.sex_code ,
a.salutation ,
a.email_address ,
a.mail_stop ,
a.contact_key ,
b.phone_number telephone ,
b.extension telephone_extension ,
b.phone_type telephone_type ,
b.area_code telephone_area_code ,
a.last_update_date ,
a.last_updated_by ,
a.creation_date ,
a.created_by ,
a.last_update_login ,
a.contact_id
FROM
ra_contacts@TRAIN.WORLD a,
ra_phones@TRAIN.WORLD b
WHERE
a.customer_id = spc_contact_customer_id
AND a.contact_id is not null
AND a.contact_id = b.contact_id(+);
　
/***************************************************************************************************************************
Fifth cursor for converting the customer phone numbers at customer level.This cursor is dependent on First Cursor
**************************************************************************************************************************/
　
CURSOR spc_cust_phone_cur (spc_phone_customer_id NUMBER)
IS
SELECT a.orig_system_reference orig_system_telephone_ref ,
a.customer_id orig_system_customer_Ref ,
a.address_id orig_system_address_ref ,
a.phone_number telephone ,
a.extension telephone_extension ,
a.phone_type telephone_type ,
a.area_code telephone_area_code ,
a.last_update_date ,
a.last_updated_by ,
a.creation_date ,
a.created_by ,
a.last_update_login ,
a.contact_id
FROM
ra_phones@TRAIN.WORLD a
WHERE a.customer_id = spc_phone_customer_id
AND a.contact_id is null;
/***************************************************************************************************************************
DECLARE SECTION IS OVER . BEGIN SECTION STARTS HERE.OPENING THE FIRST CURSOR
**************************************************************************************************************************/
　
BEGIN
FOR spc_for_cust_main IN spc_cust_main
LOOP
v_rule0 :=NULL;
v_rule1 :=NULL;
v_rule2 :=NULL;
v_rule3 :=NULL;
v_rule4 :=NULL;
spc_custhead_record_status :=NULL;
--convert all active customers which are created in last three months
SELECT count(*) into v_rule0
FROM ra_customers@train.world
WHERE status='A' AND TRUNC(CREATION_DATE) > ADD_MONTHS(TRUNC(SYSDATE),-3)
and customer_id=spc_for_cust_main.customer_id;
--convert active customers which are required for invoices
select count(*) into v_rule1
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and bill_to_customer_id = spc_for_cust_main.customer_id
and spc_for_cust_main.status='A';
--convert active customers which are required for salesorders
SELECT count(*) into v_rule2
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and customer_id=spc_for_cust_main.customer_id
and spc_for_cust_main.status='A';
--convert inactive customers which are required for open invoices
--dont convert the customer invoices for the inactive customers
select count(*) into v_rule3
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and STATUS_TRX='OP'
and bill_to_customer_id = spc_for_cust_main.customer_id
and spc_for_cust_main.status='I';
--convert inactive customers which are required for open salesorders
--dont convert the customer sales orders for the inactive customers
SELECT count(*) into v_rule4
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and open_flag='Y'
and customer_id=spc_for_cust_main.customer_id
and spc_for_cust_main.status='I';
　
IF v_rule0<1 AND v_rule1<1 AND v_rule2<1 AND v_rule3<1 AND v_rule4<1 THEN
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_main.customer_id,'customer rule fired',NULL,NULL,NULL);
GOTO END_LOOP1;
END IF;
　
　
　
　
/********************************************************************************************************** PROCESSING THE SECOND CURSOR
**********************************************************************************************************/
FOR spc_for_custhead_cur IN spc_custhead_cur(spc_for_cust_main.customer_id)
LOOP
v_rule5 :=NULL;
v_rule6 :=NULL;
v_rule7 :=NULL;
--convert inactive customer addresses which are required for open salesorders
--dont convert the customer sales orders for the inactive addresses
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.address_status='I' THEN
SELECT count(*) into v_rule5
from so_headers_all@train.world where
to_char(creation_date,'RRRR') >2000
and open_flag='Y'
and invoice_to_site_use_id=spc_for_custhead_cur.old_site_use_id;
END IF;
--convert inactive customer addresses which are required for open invoices
--dont convert the customer invoices for the inactive addresses
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.address_status='I' THEN
SELECT count(*) into v_rule6
from ra_customer_trx_all@train.world where
to_char(creation_date,'RRRR') >2000
and STATUS_TRX='OP'
and bill_to_site_use_id=spc_for_custhead_cur.old_site_use_id;
END IF;
--dont convert the customer addresses without business purposes
IF spc_for_custhead_cur.address_id is not null and spc_for_custhead_cur.site_use_code is null THEN
v_rule7:=0;
END IF;
IF v_rule5<1 AND v_rule6<1 OR v_rule7<1 THEN
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_main.customer_id,'address rule fired',NULL,NULL,NULL);
GOTO END_LOOP2;
END IF;
　
/**********************************************************************************************************
Validating customer tax code
**********************************************************************************************************/
spc_tax_org_id:=NULL;
IF spc_for_custhead_cur.org_id=106 then spc_tax_org_id:=83;
ELSIF spc_for_custhead_cur.org_id=104 then spc_tax_org_id:=84;
END IF;
BEGIN
spc_cust_tax_code :=NULL;
IF spc_for_custhead_cur.cust_tax_code IS NOT NULL THEN
SELECT tax_code INTO
spc_cust_tax_code
FROM ar_vat_tax_all
where tax_code=spc_for_custhead_cur.cust_tax_code
AND org_id=spc_tax_org_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','spc_for_custhead_cur.cust_tax_code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'cust use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
END;
　
　
BEGIN
spc_site_use_tax_code :=NULL;
IF spc_for_custhead_cur.site_use_tax_code IS NOT NULL THEN
SELECT tax_code INTO
spc_site_use_tax_code
FROM ar_vat_tax_all
WHERE tax_code=spc_for_custhead_cur.site_use_tax_code
AND org_id=spc_tax_org_id
AND ROWNUM<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL',spc_for_custhead_cur.site_use_tax_code);
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_use_tax_code,'site use tax code',spc_err_msg,'AR_VAT_TAX_ALL','TAX_CODE');
END;
　
　
　
/**********************************************************************************************************
Validating customer ship via code
**********************************************************************************************************/
　
BEGIN
spc_cust_ship_via_code :=NULL;
IF spc_for_custhead_cur.cust_ship_via_code IS NOT NULL THEN
SELECT freight_code INTO
spc_cust_ship_via_code
FROM wsh_carriers
WHERE
freight_code=spc_for_custhead_cur.cust_ship_via_code and rownum<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,' cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,'cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.cust_ship_via_code,'cust ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
　
　
　
BEGIN
spc_site_ship_via_code :=NULL;
IF spc_for_custhead_cur.site_ship_via_code IS NOT NULL THEN
SELECT freight_code INTO
spc_site_ship_via_code
FROM wsh_carriers
WHERE
freight_code=spc_for_custhead_cur.site_ship_via_code and rownum<2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.site_ship_via_code,'site ship via code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
　
　
/**********************************************************************************************************
Validating site use code
**********************************************************************************************************/
BEGIN
spc_site_use_code :=NULL;
IF spc_for_custhead_cur.site_use_code IS NOT NULL THEN
SELECT lookup_code INTO
spc_site_use_code
FROM fnd_lookup_values_vl
WHERE
lookup_type='SITE_USE_CODE'
AND LOOKUP_CODE=spc_for_custhead_cur.site_use_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'site use code',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
　
BEGIN
spc_cust_class :=NULL;
IF spc_for_custhead_cur.customer_class_code IS NOT NULL THEN
SELECT lookup_code INTO
spc_cust_class
FROM fnd_lookup_values_vl
WHERE
lookup_type='CUSTOMER CLASS'
AND LOOKUP_CODE=spc_for_custhead_cur.customer_class_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
　
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'customer class',spc_err_msg,'FND_LOOKUP_VALUES_VL','lookup code');
END;
　
　
　
BEGIN
spc_orig_system_parent_ref :=NULL;
IF spc_for_custhead_cur.bill_to_orig_address_ref IS NOT NULL THEN
SELECT a.customer_id INTO spc_orig_system_parent_ref
from
ra_addresses_all@train.world a,
ra_site_uses_all@train.world b
where
a.address_id=b.address_id
and
b.site_use_id=spc_for_custhead_cur.bill_to_orig_address_ref;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
END;
　
　
　
　
　
BEGIN
spc_bill_to_site_ref :=NULL;
IF spc_for_custhead_cur.bill_to_orig_address_ref IS NOT NULL THEN
SELECT a.address_id INTO spc_bill_to_site_ref
from
ra_addresses_all@train.world a,
ra_site_uses_all@train.world b
where
a.address_id=b.address_id
and
b.site_use_id=spc_for_custhead_cur.bill_to_orig_address_ref;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN TOO_MANY_ROWS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
WHEN OTHERS
THEN
spc_custhead_record_status:=spc_custhead_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custhead_cur.customer_number,'parent ref',spc_err_msg,'RA_SITE_USES_ALL','bill_to_site_use_id');
END;
　
　
IF spc_custhead_record_status is null then
BEGIN
INSERT INTO ra_customers_interface_all(
org_id ,
customer_class_code ,
address1 ,
address2 ,
address3 ,
address4 ,
orig_system_parent_ref ,
orig_system_address_ref ,
orig_system_customer_ref ,
bill_to_orig_address_ref ,
city ,
country ,
county ,
postal_code ,
province ,
state ,
primary_site_use_flag ,
site_use_code ,
site_use_tax_code ,
site_use_tax_reference ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
customer_category_code ,
customer_name ,
customer_number ,
customer_status ,
customer_type ,
cust_ship_via_code ,
cust_tax_code ,
cust_tax_reference ,
insert_update_flag ,
location ,
translated_customer_name ,
site_ship_via_code ,
--language ,
--terrritory ,
--demand_class_code ,
--cust_tax_exempt_num ,
--warning_text ,
--validated_flag ,
--jgzz_fiscal_code ,
--site_use_tax_exempt_num ,
--location_ccid ,
--message_text ,
--org_id ,
--party_number ,
--person_first_name ,
--person_flag ,
--person_last_name ,
--request_id ,
--customer_key ,
--customer_name_phonetic ,
--customer_prospect_code ,
--bill_to_orig_address_ref ,
--orig_system_parent_ref ,
--orig_system_party_ref ,
--gl_id_clearing ,
--gl_id_factor ,
--gl_id_freight ,
--gl_id_rec ,
--gl_id_remittance ,
--gl_id_rev ,
--gl_id_tax ,
--gl_id_unbilled ,
--gl_id_unearned ,
--gl_id_unpaid_rec ,
--address_key ,
--address_lines_phonetic ,
customer_attribute1 ,
customer_attribute10 ,
customer_attribute11 ,
customer_attribute12 ,
customer_attribute13 ,
customer_attribute14 ,
customer_attribute15 ,
customer_attribute2 ,
customer_attribute3 ,
customer_attribute4 ,
customer_attribute5 ,
customer_attribute6 ,
customer_attribute7 ,
customer_attribute8 ,
customer_attribute9 ,
customer_attribute_category ,
address_attribute1 ,
address_attribute10 ,
address_attribute11 ,
address_attribute12 ,
address_attribute13 ,
address_attribute14 ,
address_attribute15 ,
address_attribute2 ,
address_attribute3 ,
address_attribute4 ,
address_attribute5 ,
address_attribute6 ,
address_attribute7 ,
address_attribute8 ,
address_attribute9 ,
address_attribute_category ,
--site_ship_via_code ,
--site_use_attribute1 ,
--site_use_attribute10 ,
--site_use_attribute11 ,
--site_use_attribute12 ,
--site_use_attribute13 ,
site_use_attribute14
--site_use_attribute15 ,
--site_use_attribute16 ,
--site_use_attribute17 ,
--site_use_attribute18 ,
--site_use_attribute19 ,
--site_use_attribute2 ,
--site_use_attribute20 ,
--site_use_attribute21 ,
--site_use_attribute22 ,
--site_use_attribute23 ,
--site_use_attribute24 ,
--site_use_attribute25 ,
--site_use_attribute3 ,
--site_use_attribute4 ,
--site_use_attribute5 ,
--site_use_attribute6 ,
--site_use_attribute7 ,
--site_use_attribute8 ,
--site_use_attribute9 ,
--site_use_attribute_category ,
--global_attribute1 ,
--global_attribute10 ,
--global_attribute11 ,
--global_attribute12 ,
--global_attribute13 ,
--global_attribute14 ,
--global_attribute15 ,
--global_attribute16 ,
--global_attribute17 ,
--global_attribute18 ,
--global_attribute19 ,
--global_attribute2 ,
--global_attribute20 ,
--global_attribute3 ,
--global_attribute4 ,
--global_attribute5 ,
--global_attribute6 ,
--global_attribute7 ,
--global_attribute8 ,
--global_attribute9 ,
--global_attribute_category ,
--gdf_address_attribute1 ,
--gdf_address_attribute10 ,
--gdf_address_attribute11 ,
--gdf_address_attribute12 ,
--gdf_address_attribute13 ,
--gdf_address_attribute14 ,
--gdf_address_attribute15 ,
--gdf_address_attribute16 ,
--gdf_address_attribute17 ,
--gdf_address_attribute18 ,
--gdf_address_attribute19 ,
--gdf_address_attribute2 ,
--gdf_address_attribute20 ,
--gdf_address_attribute3 ,
--gdf_address_attribute4 ,
--gdf_address_attribute5 ,
--gdf_address_attribute6 ,
--gdf_address_attribute7 ,
--gdf_address_attribute8 ,
--gdf_address_attribute9 ,
--gdf_address_attr_cat ,
--gdf_site_use_attribute1 ,
--gdf_site_use_attribute10 ,
--gdf_site_use_attribute11 ,
--gdf_site_use_attribute12 ,
--gdf_site_use_attribute13 ,
--gdf_site_use_attribute14 ,
--gdf_site_use_attribute15 ,
--gdf_site_use_attribute16 ,
--gdf_site_use_attribute17 ,
--gdf_site_use_attribute18 ,
--gdf_site_use_attribute19 ,
--gdf_site_use_attribute2 ,
--gdf_site_use_attribute20 ,
--gdf_site_use_attribute3 ,
--gdf_site_use_attribute4 ,
--gdf_site_use_attribute5 ,
--gdf_site_use_attribute6 ,
--gdf_site_use_attribute7 ,
--gdf_site_use_attribute8 ,
--gdf_site_use_attribute9 ,
--gdf_site_use_attr_cat ,
--interface_status
)
VALUES
( spc_for_custhead_cur.org_id ,
spc_cust_class ,
spc_for_custhead_cur.address1 ,
spc_for_custhead_cur.address2 ,
spc_for_custhead_cur.address3 ,
spc_for_custhead_cur.address4 ,
spc_orig_system_parent_ref ,
spc_for_custhead_cur.orig_system_address_ref ,
spc_for_custhead_cur.orig_system_customer_ref ,
spc_bill_to_site_ref ,
spc_for_custhead_cur.city ,
spc_for_custhead_cur.country ,
spc_for_custhead_cur.county ,
spc_for_custhead_cur.postal_code ,
spc_for_custhead_cur.province ,
spc_for_custhead_cur.state ,
spc_for_custhead_cur.primary_site_use_flag ,
spc_for_custhead_cur.site_use_code ,
spc_site_use_tax_code ,
spc_for_custhead_cur.site_use_tax_reference ,
spc_created_by ,
spc_creation_date ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_custhead_cur.customer_category_code ,
spc_for_custhead_cur.customer_name ,
spc_for_custhead_cur.customer_number ,
spc_for_custhead_cur.customer_status ,
spc_for_custhead_cur.customer_type ,
spc_cust_ship_via_code ,
spc_cust_tax_code ,
spc_for_custhead_cur.cust_tax_reference ,
spc_insert_update_flag ,
spc_for_custhead_cur.location ,
spc_for_custhead_cur.translated_customer_name ,
spc_site_ship_via_code ,
--language ,
--terrritory ,
--demand_class_code ,
--cust_tax_exempt_num ,
--warning_text ,
--validated_flag ,
--jgzz_fiscal_code ,
--site_use_tax_exempt_num ,
--location_ccid ,
--message_text ,
--org_id ,
--party_number ,
--person_first_name ,
--person_flag ,
--person_last_name ,
--request_id ,
--customer_key ,
--customer_name_phonetic ,
--customer_prospect_code ,
--bill_to_orig_address_ref ,
--orig_system_parent_ref ,
--orig_system_party_ref ,
--gl_id_clearing ,
--gl_id_factor ,
--gl_id_freight ,
--gl_id_rec ,
--gl_id_remittance ,
--gl_id_rev ,
--gl_id_tax ,
--gl_id_unbilled ,
--gl_id_unearned ,
--gl_id_unpaid_rec ,
--address_key ,
--address_lines_phonetic ,
spc_for_custhead_cur.customer_attribute1 ,
spc_for_custhead_cur.customer_attribute10 ,
spc_for_custhead_cur.customer_attribute11 ,
spc_for_custhead_cur.customer_attribute12 ,
spc_for_custhead_cur.customer_attribute13 ,
spc_for_custhead_cur.orig_system_customer_ref ,
spc_for_custhead_cur.customer_attribute15 ,
spc_for_custhead_cur.customer_attribute2 ,
spc_for_custhead_cur.customer_attribute3 ,
spc_for_custhead_cur.customer_attribute4 ,
spc_for_custhead_cur.customer_attribute5 ,
spc_for_custhead_cur.customer_attribute6 ,
spc_for_custhead_cur.customer_attribute7 ,
spc_for_custhead_cur.customer_attribute8 ,
spc_for_custhead_cur.customer_attribute9 ,
spc_for_custhead_cur.customer_attribute_category ,
spc_for_custhead_cur.address_attribute1 ,
spc_for_custhead_cur.address_attribute10 ,
spc_for_custhead_cur.address_attribute11 ,
spc_for_custhead_cur.address_attribute12 ,
spc_for_custhead_cur.address_attribute13 ,
spc_for_custhead_cur.orig_system_address_ref ,
spc_for_custhead_cur.address_attribute15 ,
spc_for_custhead_cur.address_attribute2 ,
spc_for_custhead_cur.address_attribute3 ,
spc_for_custhead_cur.address_attribute4 ,
spc_for_custhead_cur.address_attribute5 ,
spc_for_custhead_cur.address_attribute6 ,
spc_for_custhead_cur.address_attribute7 ,
spc_for_custhead_cur.address_attribute8 ,
spc_for_custhead_cur.address_attribute9 ,
spc_for_custhead_cur.address_attribute_category ,
--site_ship_via_code ,
--site_use_attribute1 ,
--site_use_attribute10 ,
--site_use_attribute11 ,
--site_use_attribute12 ,
--site_use_attribute13 ,
spc_for_custhead_cur.old_site_use_id
--site_use_attribute15 ,
--site_use_attribute16 ,
--site_use_attribute17 ,
--site_use_attribute18 ,
--site_use_attribute19 ,
--site_use_attribute2 ,
--site_use_attribute20 ,
--site_use_attribute21 ,
--site_use_attribute22 ,
--site_use_attribute23 ,
--site_use_attribute24 ,
--site_use_attribute25 ,
--site_use_attribute3 ,
--site_use_attribute4 ,
--site_use_attribute5 ,
--site_use_attribute6 ,
--site_use_attribute7 ,
--site_use_attribute8 ,
--site_use_attribute9 ,
--site_use_attribute_category ,
--global_attribute1 ,
--global_attribute10 ,
--global_attribute11 ,
--global_attribute12 ,
--global_attribute13 ,
--global_attribute14 ,
--global_attribute15 ,
--global_attribute16 ,
--global_attribute17 ,
--global_attribute18 ,
--global_attribute19 ,
--global_attribute2 ,
--global_attribute20 ,
--global_attribute3 ,
--global_attribute4 ,
--global_attribute5 ,
--global_attribute6 ,
--global_attribute7 ,
--global_attribute8 ,
--global_attribute9 ,
--global_attribute_category ,
--gdf_address_attribute1 ,
--gdf_address_attribute10 ,
--gdf_address_attribute11 ,
--gdf_address_attribute12 ,
--gdf_address_attribute13 ,
--gdf_address_attribute14 ,
--gdf_address_attribute15 ,
--gdf_address_attribute16 ,
--gdf_address_attribute17 ,
--gdf_address_attribute18 ,
--gdf_address_attribute19 ,
--gdf_address_attribute2 ,
--gdf_address_attribute20 ,
--gdf_address_attribute3 ,
--gdf_address_attribute4 ,
--gdf_address_attribute5 ,
--gdf_address_attribute6 ,
--gdf_address_attribute7 ,
--gdf_address_attribute8 ,
--gdf_address_attribute9 ,
--gdf_address_attr_cat ,
--gdf_site_use_attribute1 ,
--gdf_site_use_attribute10 ,
--gdf_site_use_attribute11 ,
--gdf_site_use_attribute12 ,
--gdf_site_use_attribute13 ,
--gdf_site_use_attribute14 ,
--gdf_site_use_attribute15 ,
--gdf_site_use_attribute16 ,
--gdf_site_use_attribute17 ,
--gdf_site_use_attribute18 ,
--gdf_site_use_attribute19 ,
--gdf_site_use_attribute2 ,
--gdf_site_use_attribute20 ,
--gdf_site_use_attribute3 ,
--gdf_site_use_attribute4 ,
--gdf_site_use_attribute5 ,
--gdf_site_use_attribute6 ,
--gdf_site_use_attribute7 ,
--gdf_site_use_attribute8 ,
--gdf_site_use_attribute9 ,
--gdf_site_use_attr_cat ,
--interface_status
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_custhead_cur.customer_number,'other error for header insert',spc_error_msg_ex,'customer number',NULL);
END;
END IF;
<>
NULL;
END LOOP;
/**********************************************************************************************************
PROCESSING THE THIRD CURSOR
**********************************************************************************************************/
FOR spc_for_custprofile_cur IN spc_custprofile_cur(spc_for_cust_main.customer_id)
LOOP
spc_custprofile_record_status:=NULL;
/**********************************************************************************************************
validating customer profile classes;
**********************************************************************************************************/
BEGIN
spc_cust_profile_class_name :=NULL;
IF spc_for_custprofile_cur.cust_profile_class_name IS NOT NULL THEN
SELECT name INTO
spc_cust_profile_class_name
FROM ar_customer_profile_classes where name=spc_for_custprofile_cur.cust_profile_class_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'cust_profile_class_name ',spc_err_msg,'customer_profile_classes','name');
END;
/********************************************************************************************************** validating dunning letters
**********************************************************************************************************/
BEGIN
spc_dunning_letter_set_name :=NULL;
IF spc_for_custprofile_cur.dunning_letter_set_name IS NOT NULL THEN
SELECT name INTO
spc_dunning_letter_set_name
FROM ar_dunning_letter_sets where name=spc_for_custprofile_cur.dunning_letter_set_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'dunning_letter_set_name ',spc_err_msg,'ar_dunning_letter_sets','name');
END;
/********************************************************************************************************** validating grouping rules
**********************************************************************************************************/
BEGIN
spc_grouping_rule_name :=NULL;
IF spc_for_custprofile_cur.grouping_rule_name IS NOT NULL THEN
SELECT name INTO
spc_grouping_rule_name
FROM ra_grouping_rules where name=spc_for_custprofile_cur.grouping_rule_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'grouping_rule_name ',spc_err_msg,'ra_grouping_rules','name');
END;
/********************************************************************************************************** validating standard terms
**********************************************************************************************************/
BEGIN
spc_standard_term_name :=NULL;
IF spc_for_custprofile_cur.standard_term_name IS NOT NULL THEN
SELECT name INTO
spc_standard_term_name
FROM ra_terms_vl where name=spc_for_custprofile_cur.standard_term_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'standard_term_name ',spc_err_msg,'ra_terms_vl','name');
END;
/**********************************************************************************************************
validating statement cycles
**********************************************************************************************************/
BEGIN
spc_statement_cycle_name :=NULL;
IF spc_for_custprofile_cur.statement_cycle_name IS NOT NULL THEN
SELECT name INTO
spc_statement_cycle_name
FROM ar_statement_cycles where name=spc_for_custprofile_cur.statement_cycle_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles',spc_for_custprofile_cur.statement_cycle_name);
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles','name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'statement_cycle_name ',spc_err_msg,'ar_statement_cycles','name');
END;
/********************************************************************************************************** validating autocash hierarchies
**********************************************************************************************************/
BEGIN
spc_autocash_hierarchy_name :=NULL;
IF spc_for_custprofile_cur.autocash_hierarchy_name IS NOT NULL THEN
SELECT hierarchy_name INTO
spc_autocash_hierarchy_name
FROM ar_autocash_hierarchies where hierarchy_name=spc_for_custprofile_cur.autocash_hierarchy_name ;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name ',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'autocash_hierarchy_name ',spc_err_msg,'ar_autocash_hierarchies','hirearchy_name');
END;
/********************************************************************************************************** validating collectors
**********************************************************************************************************/
BEGIN
spc_collector_name :=NULL;
IF spc_for_custprofile_cur.collector_name IS NOT NULL THEN
SELECT name INTO
spc_collector_name
FROM ar_collectors
WHERE name=spc_for_custprofile_cur.collector_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
WHEN TOO_MANY_ROWS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES (spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
　
WHEN OTHERS
THEN
spc_custprofile_record_status:=spc_custprofile_record_status||' '||'Error';
spc_err_msg:=substr(SQLERRM,1,30);
INSERT INTO spc_cust_error_table
(source_transaction_id,ERROR_TYPE, error_message, table_name,column_name)VALUES
(spc_for_custprofile_cur.customer_id,'collector ',spc_err_msg,'AR_COLLECTORS','NAME');
END;
　
IF spc_for_custprofile_cur.interest_charges is null or spc_for_custprofile_cur.interest_charges='N' THEN
spc_charge_flag:=NULL;
ELSE
spc_charge_flag:=spc_for_custprofile_cur.charge_on_finance_charge_flag;
END IF;
　
　
　
IF spc_custprofile_record_status is null then
BEGIN
INSERT INTO ra_customer_profiles_int_all(
created_by ,
creation_date ,
autocash_hierarchy_name ,
auto_rec_incl_disputed_flag ,
auto_rec_min_receipt_amount ,
charge_on_finance_charge_flag ,
clearing_days ,
collector_name ,
cons_inv_flag ,
cons_inv_type ,
credit_balance_statements ,
credit_checking ,
credit_hold ,
credit_rating ,
currency_code ,
customer_profile_class_name ,
discount_grace_days ,
discount_terms ,
dunning_letters ,
dunning_letter_set_name ,
account_status ,
grouping_rule_name ,
insert_update_flag ,
interest_charges ,
interest_period_days ,
interest_rate ,
last_updated_by ,
last_update_date ,
last_update_login ,
max_interest_charge ,
min_dunning_amount ,
min_dunning_invoice_amount ,
min_fc_balance_amount ,
min_fc_invoice_amount ,
min_statement_amount ,
orig_system_address_ref ,
orig_system_customer_ref ,
overall_credit_limit ,
override_terms ,
payment_grace_days ,
percent_collectable ,
risk_code ,
standard_term_name ,
statements ,
statement_cycle_name ,
tax_printing_option ,
tolerance ,
trx_credit_limit ,
--interface_status ,
--request_id ,
--validated_flag ,
--org_id ,
--validated_flag ,
--amount_attribute1 ,
--amount_attribute10 ,
--amount_attribute11 ,
--amount_attribute12 ,
--amount_attribute13 ,
--amount_attribute14 ,
--amount_attribute15 ,
--amount_attribute2 ,
--amount_attribute3 ,
--amount_attribute4 ,
--amount_attribute5 ,
--amount_attribute6 ,
--amount_attribute7 ,
--amount_attribute8 ,
--amount_attribute9 ,
--amount_attribute_category ,
attribute1 ,
attribute2
--attribute11 ,
--attribute12 ,
--attribute13 ,
--attribute14 ,
--attribute15 ,
--attribute2 ,
--attribute3 ,
--attribute4 ,
--attribute5 ,
--attribute6 ,
--attribute7 ,
--attribute8 ,
--attribute9 ,
--attribute_category ,
--gdf_cust_prof_attribute1 ,
--att_cust_prof_attribute10 ,
--att_cust_prof_attribute11 ,
--att_cust_prof_attribute12 ,
--att_cust_prof_attribute13 ,
--att_cust_prof_attribute14 ,
--att_cust_prof_attribute15 ,
--att_cust_prof_attribute16 ,
--att_cust_prof_attribute17 ,
--att_cust_prof_attribute18 ,
--att_cust_prof_attribute19 ,
--att_cust_prof_attribute2 ,
--att_cust_prof_attribute20 ,
--att_cust_prof_attribute3 ,
--att_cust_prof_attribute4 ,
--att_cust_prof_attribute5 ,
--att_cust_prof_attribute6 ,
--att_cust_prof_attribute7 ,
--att_cust_prof_attribute8 ,
--att_cust_prof_attribute9 ,
--att_cust_prof_attr_cat ,
--attbal_attribute1 ,
--attbal_attribute10 ,
--attbal_attribute11 ,
--attbal_attribute12 ,
--attbal_attribute13 ,
--attbal_attribute14 ,
--attbal_attribute15 ,
--attbal_attribute16 ,
--attbal_attribute17 ,
--attbal_attribute18 ,
--attbal_attribute19 ,
--attbal_attribute2 ,
--attbal_attribute20 ,
--attbal_attribute3 ,
--attbal_attribute4 ,
--attbal_attribute5 ,
--attbal_attribute6 ,
--attbal_attribute7 ,
--attbal_attribute8 ,
--attbal_attribute9 ,
--attbal_attribute_category ,
)VALUES(
spc_created_by ,
spc_creation_date ,
spc_autocash_hierarchy_name ,
spc_for_custprofile_cur.auto_rec_incl_disputed_flag ,
spc_for_custprofile_cur.auto_rec_min_receipt_amount ,
spc_charge_flag ,
spc_for_custprofile_cur.clearing_days ,
spc_collector_name ,
spc_for_custprofile_cur.cons_inv_flag ,
spc_for_custprofile_cur.cons_inv_type ,
spc_for_custprofile_cur.credit_balance_statements ,
spc_for_custprofile_cur.credit_checking ,
spc_for_custprofile_cur.credit_hold ,
spc_for_custprofile_cur.credit_rating ,
spc_for_custprofile_cur.currency_code ,
spc_cust_profile_class_name ,
spc_for_custprofile_cur.discount_grace_days ,
spc_for_custprofile_cur.discount_terms ,
spc_for_custprofile_cur.dunning_letters ,
spc_dunning_letter_set_name ,
spc_for_custprofile_cur.account_status ,
spc_grouping_rule_name ,
spc_insert_update_flag ,
spc_for_custprofile_cur.interest_charges ,
spc_for_custprofile_cur.interest_period_days ,
spc_for_custprofile_cur.interest_rate ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_custprofile_cur.max_interest_charge ,
spc_for_custprofile_cur.min_dunning_amount ,
spc_for_custprofile_cur.min_dunning_invoice_amount ,
spc_for_custprofile_cur.min_fc_balance_amount ,
spc_for_custprofile_cur.min_fc_invoice_amount ,
spc_for_custprofile_cur.min_statement_amount ,
spc_for_custprofile_cur.orig_system_address_ref ,
spc_for_custprofile_cur.orig_system_customer_ref ,
spc_for_custprofile_cur.overall_credit_limit ,
spc_for_custprofile_cur.override_terms ,
spc_for_custprofile_cur.payment_grace_days ,
spc_for_custprofile_cur.percent_collectable ,
spc_for_custprofile_cur.risk_code ,
spc_standard_term_name ,
spc_for_custprofile_cur.statements ,
spc_statement_cycle_name ,
spc_for_custprofile_cur.tax_printing_option ,
spc_for_custprofile_cur.tolerance ,
spc_for_custprofile_cur.trx_credit_limit ,
--interface_status ,
--request_id ,
--validated_flag ,
--org_id ,
--validated_flag ,
--amount_attribute1 ,
--amount_attribute10 ,
--amount_attribute11 ,
--amount_attribute12 ,
--amount_attribute13 ,
--amount_attribute14 ,
--amount_attribute15 ,
--amount_attribute2 ,
--amount_attribute3 ,
--amount_attribute4 ,
--amount_attribute5 ,
--amount_attribute6 ,
--amount_attribute7 ,
--amount_attribute8 ,
--amount_attribute9 ,
--amount_attribute_category ,
spc_for_custprofile_cur.overall_credit_limit ,
spc_for_custprofile_cur.trx_credit_limit
--attribute2
--attribute11 ,
--attribute12 ,
--attribute13 ,
--attribute14 ,
--attribute15 ,
--attribute2 ,
--attribute3 ,
--attribute4 ,
--attribute5 ,
--attribute6 ,
--attribute7 ,
--attribute8 ,
--attribute9 ,
--attribute_category ,
--gdf_cust_prof_attribute1 ,
--att_cust_prof_attribute10 ,
--att_cust_prof_attribute11 ,
--att_cust_prof_attribute12 ,
--att_cust_prof_attribute13 ,
--att_cust_prof_attribute14 ,
--att_cust_prof_attribute15 ,
--att_cust_prof_attribute16 ,
--att_cust_prof_attribute17 ,
--att_cust_prof_attribute18 ,
--att_cust_prof_attribute19 ,
--att_cust_prof_attribute2 ,
--att_cust_prof_attribute20 ,
--att_cust_prof_attribute3 ,
--att_cust_prof_attribute4 ,
--att_cust_prof_attribute5 ,
--att_cust_prof_attribute6 ,
--att_cust_prof_attribute7 ,
--att_cust_prof_attribute8 ,
--att_cust_prof_attribute9 ,
--att_cust_prof_attr_cat ,
--attbal_attribute1 ,
--attbal_attribute10 ,
--attbal_attribute11 ,
--attbal_attribute12 ,
--attbal_attribute13 ,
--attbal_attribute14 ,
--attbal_attribute15 ,
--attbal_attribute16 ,
--attbal_attribute17 ,
--attbal_attribute18 ,
--attbal_attribute19 ,
--attbal_attribute2 ,
--attbal_attribute20 ,
--attbal_attribute3 ,
--attbal_attribute4 ,
--attbal_attribute5 ,
--attbal_attribute6 ,
--attbal_attribute7 ,
--attbal_attribute8 ,
--attbal_attribute9 ,
--attbal_attribute_category ,
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
( spc_for_custprofile_cur.orig_system_customer_ref,'other error for profile',spc_error_msg_ex,'customerid',NULL);
END;
END IF;
END LOOP;
/********************************************************************************************************** PROCESSING THE FOURTH CURSOR
**********************************************************************************************************/
　
FOR spc_for_cust_contact_cur IN spc_cust_contact_cur (spc_for_cust_main.customer_id)
LOOP
　
BEGIN
INSERT INTO ra_contact_phones_int_all(
contact_attribute14 ,
contact_first_name ,
contact_job_title ,
contact_last_name ,
contact_title ,
created_by ,
creation_date ,
email_address ,
insert_update_flag ,
last_updated_by ,
last_update_date ,
last_update_login ,
mail_stop ,
orig_system_address_ref ,
orig_system_contact_ref ,
orig_system_customer_ref ,
orig_system_telephone_ref ,
salutation ,
sex_code ,
telephone ,
telephone_area_code ,
telephone_extension ,
telephone_type
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--org_id
--phone_attribute9 ,
--phone_attribute_category
)VALUES
( spc_for_cust_contact_cur.contact_id ,
spc_for_cust_contact_cur.contact_first_name ,
spc_for_cust_contact_cur.contact_job_title ,
spc_for_cust_contact_cur.contact_last_name ,
spc_for_cust_contact_cur.contact_title ,
spc_created_by ,
spc_creation_date ,
spc_for_cust_contact_cur.email_address ,
spc_insert_update_flag ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_cust_contact_cur.mail_stop ,
spc_for_cust_contact_cur.orig_system_address_ref ,
spc_for_cust_contact_cur.orig_system_contact_ref ,
spc_for_cust_contact_cur.orig_system_customer_ref ,
spc_for_cust_contact_cur.orig_system_telephone_ref ,
spc_for_cust_contact_cur.salutation ,
spc_for_cust_contact_cur.sex_code ,
spc_for_cust_contact_cur.telephone ,
spc_for_cust_contact_cur.telephone_area_code ,
spc_for_cust_contact_cur.telephone_extension ,
spc_for_cust_contact_cur.telephone_type
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--spc_for_cust_contact_cur.org_id
--phone_attribute9 ,
--phone_attribute_category
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex:=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_contact_cur.orig_system_customer_ref,'other error for contact',spc_error_msg_ex,'customerid',NULL);
END;
END LOOP;
/********************************************************************************************************** PROCESSING THE FIFTH CURSOR
**********************************************************************************************************/
FOR spc_for_cust_phone_cur IN spc_cust_phone_cur (spc_for_cust_main.customer_id)
LOOP
　
BEGIN
INSERT INTO ra_contact_phones_int_all(
created_by ,
creation_date ,
insert_update_flag ,
last_updated_by ,
last_update_date ,
last_update_login ,
orig_system_address_ref ,
orig_system_customer_ref ,
orig_system_telephone_ref ,
telephone ,
telephone_area_code ,
telephone_extension ,
telephone_type
--contact_first_name ,
--contact_job_title ,
--contact_last_name ,
--contact_title ,
--salutation ,
--sex_code ,
--email_address ,
--mail_stop ,
--orig_system_contact_ref ,
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--org_id ,
--phone_attribute9 ,
--phone_attribute_category
)VALUES
(
spc_created_by ,
spc_creation_date ,
spc_insert_update_flag ,
spc_last_updated_by ,
spc_last_update_date ,
spc_last_update_login ,
spc_for_cust_phone_cur.orig_system_address_ref ,
spc_for_cust_phone_cur.orig_system_customer_ref ,
spc_for_cust_phone_cur.orig_system_telephone_ref ,
spc_for_cust_phone_cur.telephone ,
spc_for_cust_phone_cur.telephone_area_code ,
spc_for_cust_phone_cur.telephone_extension ,
spc_for_cust_phone_cur.telephone_type
--contact_first_name ,
--contact_job_title ,
--contact_last_name ,
--contact_title ,
--salutation ,
--sex_code ,
--email_address ,
--mail_stop ,
--orig_system_contact_ref ,
--request_id ,
--contact_key ,
--interface_status ,
--validated_flag ,
--contact_attribute1 ,
--contact_attribute10 ,
--contact_attribute11 ,
--contact_attribute12 ,
--contact_attribute13 ,
--contact_attribute14 ,
--contact_attribute15 ,
--contact_attribute16 ,
--contact_attribute17 ,
--contact_attribute18 ,
--contact_attribute19 ,
--contact_attribute2 ,
--contact_attribute20 ,
--contact_attribute21 ,
--contact_attribute22 ,
--contact_attribute23 ,
--contact_attribute24 ,
--contact_attribute25 ,
--contact_attribute3 ,
--contact_attribute4 ,
--contact_attribute5 ,
--contact_attribute6 ,
--contact_attribute7 ,
--contact_attribute8 ,
--contact_attribute9 ,
--contact_attribute_category ,
--phone_attribute1 ,
--phone_attribute10 ,
--phone_attribute11 ,
--phone_attribute12 ,
--phone_attribute13 ,
--phone_attribute14 ,
--phone_attribute15 ,
--phone_attribute2 ,
--phone_attribute3 ,
--phone_attribute4 ,
--phone_attribute5 ,
--phone_attribute6 ,
--phone_attribute7 ,
--phone_attribute8 ,
--spc_for_cust_phone_cur.org_id ,
--phone_attribute9 ,
--phone_attribute_category
);
EXCEPTION
WHEN OTHERS THEN
spc_error_msg_ex :=SUBSTR(SQLERRM,1,30);
INSERT INTO spc_cust_error_table VALUES
(spc_for_cust_phone_cur.orig_system_customer_ref,'other error for contact phone',spc_error_msg_ex,'customerid',NULL);
END;
END LOOP;
<>
NULL;
END LOOP;
　
END spc_arcust_conv;
END spc_ar_cust_conv;
/
　
　
　

NoLogging & Parallel Queries Example

2010-02-15T08:51:00.000-08:00

create or replace procedure erp_crs_base_populate as
begin
insert /*+ append parallel (n,4) */ into apps.erp_crs_6mth
(select /*+ parallel (x,4) */ * from apps.oe_order_lines_all where CREATION_DATE > TO_DATE('15-OCT-2003','DD-MON-RRRR'));
commit;
end;

Create and Test Java Stored Procedures

2010-02-15T08:50:00.000-08:00

/*************************************************************************/
save the following as vijayDemo.java
/*************************************************************************/
　
　
import java.io.*;
import java.util.*;
public class vijayDemo
{
static public String[] runCommand(String cmd) throws IOException
{
// set up list to capture command output lines
ArrayList list = new ArrayList();
// start command running
Process proc = Runtime.getRuntime().exec(cmd);
// get command's output stream and
// put a buffered reader input stream on it
InputStream istr = proc.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(istr));
// read output lines from command
String str;
while ((str = br.readLine()) != null)
list.add(str);
// wait for command to terminate
try
{
proc.waitFor();
}
catch (InterruptedException e)
{
System.err.println("process was interrupted");
}
// check its exit value
if (proc.exitValue() != 0)
System.err.println("exit value was non-zero");
// close stream
br.close();
// return list of strings to caller
return (String[])list.toArray(new String[0]);
}
public static void main(String args[]) throws IOException
{
try
{
// run a command
String outlist[] = runCommand("/bin/vmstat 2 20");
//String outlist[] = runCommand("/u02/home/usupport/ashehade/java/test");
// uncomment this line an comment above if you want to execut
// a c executable called test.
// display its output
for (int i = 0; i < outlist.length; i++)
System.out.println(outlist[i]);
}
catch (IOException e)
{
System.err.println(e);
}
}
}
/*************************************************************************/
Load the code into datbase
/*************************************************************************/
loadjava -u apps/apps vijaydemo.java
/*************************************************************************/
create the procedure for stored java code
/*************************************************************************/
create or replace procedure lsfromjava as
language java name 'vijaydemo.main(java.lang.String[])';
/
Test it
SQL> set serverout on
SQL call dbms_java.set_output(2000);
SQL> execute lsfromjava

PL/SQL Function to Fetch CC ID

2010-02-15T08:43:00.000-08:00

PL/SQL Function to fetch the new cc id based on the old cc id

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

1) Create following mapping table based on the data given by the accounting department

　
Name Null? Type
------------------------------- -------- ----
OLD_SEGMENT1 VARCHAR2(10)
OLD_SEGMENT2 VARCHAR2(10)
OLD_SEGMENT3 VARCHAR2(10)
NEW_SEGMENT1 VARCHAR2(10)
NEW_SEGMENT2 VARCHAR2(10)
NEW_SEGMENT3 VARCHAR2(10)
NEW_SEGMENT4 VARCHAR2(10)
NEW_SEGMENT5 VARCHAR2(10)
NEW_SEGMENT6 VARCHAR2(10)
NEW_SEGMENT7 VARCHAR2(10)
NEW_SEGMENT8 VARCHAR2(10)
OLD_CCID NUMBER(15)
NEW_CCID NUMBER(15)
DETAIL_POSTING_ALLOWED_FLAG VARCHAR2(1)
DETAIL_BUDGETING_ALLOWED_FLAG VARCHAR2(1)
OLD_ACCOUNT_TYPE VARCHAR2(1)
OLD_ENABLED_FLAG VARCHAR2(1)
OLD_SUMMARY_FLAG VARCHAR2(1)
NEW_ACCOUNT_TYPE VARCHAR2(1)
CREATED_BY NUMBER
CREATION_DATE DATE
LAST_UPDATE_DATE DATE

2) Create the function to fetch the new CC id

create or replace
function GET_CCID (CCID in number) return number
is
NEW_CCID number;
Begin
select new_ccid into NEW_CCID
from mscs_code_combinations_mapping
where
old_ccid = ccid
and rownum < 2;
return( NEW_CCID );
exception
when no_data_found
then
return(null);
when others
then
return(null);
end GET_CCID;

Sr Applications DBA A-Z Most Wanted Metalink Bookmarks

2008-05-23T07:33:00.000-07:00

Features/Release Content Documents

Note 210326.1, Oracle Applications DBA 11i+ Features Matrix
Note 163400.1, Release Content/New Features Documents for Patch Sets, Family Packs & New Releases
Note 289972.1 , 11.5.10 Release Information
Note 294932.1, Recommendations & Release Notes to Install Oracle Applications 11i

Cloning/Autoconfig/Shared APPL_TOP

Note 165195.1, Using AutoConfig with Oracle Applications 11i
Note 218089.1, Frequently Asked Questions About Using AutoConfig With Oracle Applications Release 11i
Note 217368.1, Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
Note 230672.1, Cloning Oracle Applications Release 11i with Rapid Clone
Note 216664.1, Frequently Asked Questions About Cloning Oracle Applications 11i
Note 18070.1, How to Make a Copy of a Database on the Same UNIX Machine
Note 233428.1, Sharing the Application Tier File System in Oracle E-Business Suite11i

Patching

Note 181665.1, Release 11i Adpatch Basics
Note 457566.1, Oracle Applications Patching FAQ for Release 11i
Note 60766.1, Patch Installation Frequently Asked Questions
Note 224875.1, Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note 308916.1, How To Install Interim Patches with Opatch
Note 242480.1, Using a Staged Applications System to Reduce Patching Downtime
Note 225165.1, Oracle Applications 11i Patching Best Practices and Reducing Downtime
Note 259484.1, Release 11.5.10 Maintenance Pack Installation Instructions

Installations & Upgrades

Note 240744.1, Technology Validation Utility for Oracle Applications Release 11.5.9
Note 403311.1, Daylight Saving Time (DST) Compliance for Oracle E-Business Suite
Note 458452.1, Complying with DST and Time Zone Rule Changes in E-Business Suite 11i
Note 465915.1, Using Oracle VM with Oracle E-Business Suite Release 11i or Release 12
Note 216550.1, Interoperability Notes Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note 282038.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database10.1.0
Note 362202.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database 10g (10.1.0.4)
Note 362203.1, Interoperability Notes Oracle Applications Release 11i with Oracle 10g Release 2 10.2.0)
Note 452783.1, Interoperability Notes - Oracle E-Business Suite Release 11i with Oracle Database 11.1.0
Note 125767.1, Upgrading Developer 6i with Oracle Applications 11i
Note 164317.1, Upgrading Oracle JDBC Drivers with Oracle E-Business Suite 11i
Note 124606.1, Upgrading Oracle JInitiator with Oracle Applications 11i
Note 201340.1, Using Forms Listener Servlet with Oracle Applications 11i
Note 181244.1, Configuring an X Display Server for Applications on Unix Platforms
Note 146468.1, Installing Oracle9i Application Server with Oracle Applications 11i

Troubleshooting Oracle Applications

Note 295606.1, 10gAS with Oracle E-Business Suite Release 11i Troubleshooting Guide
Note 342332.1, Troubleshooting Login Problems in Oracle Applications 11i (11.5.2 – 11.5.10+)
Note 169935.1, Troubleshooting Oracle Applications Performance Issues
Note 380487.1, Oracle Application Server 10g with Release 12 Troubleshooting Guide
Note 226265.1, Troubleshooting Guide for AutoAccounting
Note 365529.1, Troubleshooting FRM-92XXX Errors in Oracle Applications
Note 139863.1, Troubleshooting Self Service Framework with Oracle Applications 11i
Note 216208.1, Oracle9i Application Server (9iAS) with Oracle E-Business Suite Release 11i Troubleshooting

Application Server/Java

Note 300482.1, Overview of Using Java with Oracle E-Business Suite Release 11i
Note 285218.1, Recommended Browsers for Oracle Applications 11i
Note 290807.1, Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i

Database and High Availability/RAC & ASM

Note 362135.1, Configuring Oracle E-Business Suite Release 11i with 10g Release2 RAC and ASM
Note 312731.1, Configuring Oracle Applications Release 11i with 10g Real Application Clusters and 10g ASM
Note 279956.1, Oracle E-Business Suite Release 11i with 9i RAC
Note 351646.1, Configuring Release 11i with 10g Database on Raw Devices
Note 341437.1, Business Continuity for Oracle Applications Release 11i Using RAC & Data Guard
Note 294652.1, Configuration Oracle E-Business Suite 11i RAC Environments (Load Balancing )

Application Server/Portal/Web Cache

Note 186981.1, Oracle Application Server with Oracle E-Business Suite Release 11i FAQ
Note 223927.1, Oracle Application Server Integration with Oracle E-Business Suite: Statement of Direction
Note 254618.1, HTTP Server Patches Certified to be used with E-Business Suite 11i External Integrations
Note 233436.1, Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
Note 305918.1, Using Oracle Portal 10g with Oracle E-Business Suite 11i
Note 306653.1, Installing and Configuring 10gAS Web Cache with Oracle E-Business Suite 11i
Note 146469.1, Configuring Oracle Applications with Oracle Portal

Security/Encryption/SSL/OID/DMZ/SSO

Note 189367.1, Best Practices for Securing Oracle E-Business Suite
Note 287176.1, Oracle E-Business Suite 11i Configuration in a DMZ
Note 123718.1, A Guide to Understanding and Implementing SSL with Oracle Applications 11i
Note 340178.1, Enabling SSL with Oracle Application Server 10g and the E-Business Suite
Note 234599.1, Enabling Oracle Label Security in Oracle E-Business Suite
Note 391248.1, Encrypting EBS 11i Network Traffic using Advanced Security Option / Advanced Networking
Note 403294.1, Using Transparent Data Encryption with the E-Business Suite
Note 428503.1, Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.
Note 150832.1, Implementing Oracle Applications Synchronization with LDAP
Note 261914.1, Integrating Oracle E-Business Suite Release 11i with OID and Oracle Single Sign-On

Database

Note 285267.1, Oracle E-Business Suite Release 11i and Database FAQ
Note 216205.1, Database Initialization Parameters and Configuration for Oracle Applications 11i
Note 343917.1, Frequently Asked Questions: Using Oracle Applications with an x86-64 Database Server",
Note 311717.1, Frequently Asked Questions: Using Oracle Applications with an Itanium Database Server
Note 332977.1, Using Oracle Applications with an HP OpenVMS Alpha Database Server

Export & Import

Note 204015.1, Export/Import Process for Release 11i Database Instances Using Oracle8i Enterprise Edition
Note 230627.1, Export/Import Process for Release 11i Database Instances Using Oracle9i Enterprise Edition
Note 331221.1, Export/Import Process for Release 11i Database Instances Using Oracle10g Enterprise Edition
Note 362205.1, Export/Import Process for Release 11i Database Instances Using Oracle Database 10g R2

Split Configurations

Note 304489.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle9i Release 2
Note 356839.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 1
Note 369693.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 2

Discoverer

Note 257798.1, Using Discoverer 10g (9.0.4) with Oracle Applications 11i,
Note 139516.1, Discoverer 4i with Oracle Applications 11i,
Note 313418.1, Using Discoverer 10g with Oracle Applications 11i

RMAN Duplicate

2007-12-31T08:30:00.001-08:00

Primary Database SID: PRD
Duplicate Database SID: PRDAUX
Rman Catalog SID: RCAT

1) Below step is required when the backup of the database is performed using OS level commands
rman target / catalog rman/rman@RCAT
startup
catalog datafilecopy '/home/oracle/PROD/data/backup/sys01.dbf';
catalog datafilecopy '/home/oracle/PROD/data/backup/undotbs.dbf';
catalog datafilecopy '/home/oracle/PROD/data/backup/users_9i.dbf';

2) Now create a pfile for the PRDAUX instance

audit_file_dest =/oradata/PRDAUX/adump
background_dump_dest =/oradata/PRDAUX/bdump
core_dump_dest =/oradata/PRDAUX/cdump
user_dump_dest =/oradata/PRDAUX/udump
db_name ="PRDAUX"
instance_name =PRDAUX
control_files ='/home/oracle/PRDAUX/c1.ctl'
log_file_name_convert=('/home/oracle/PROD/redo/','/home/oracle/PRDAUX/')
db_file_name_convert=('/home/oracle/PROD/data/','/home/oracle/PRDAUX/')

3) Now connect to target,catalog and auxiliary instances from Rman
$ rman target sys/sys@PRD catalog rman/rman@RCAT auxiliary / Recovery
sql 'alter system switch logfile';
duplicate target database to PRDAUX1;

Standby Using RMAN Duplicate

2007-12-31T08:17:00.000-08:00

Primary Database Name: PROD Standby Name: DRPROD

Step 1: Backup the primary database physical structures(datafiles, archive logs) using RMAN

rman target /
RMAN> run
{
allocate channel c1 type disk;
backup database plus archivelog;
}

Step 2: Create controlfile for DRPROD using RMAN

RMAN> run
{
allocate channel c1 type disk;
backup current controlfile for DRPROD;
}

Step 3: Copy the backups to the DRPROD server using scp

Step 4: Make proper changes in the parameter files of both primary and DRPROD database

Add the below parameter in primary database parameter file :

db_name = PROD
instance_name = PROD
fal_client = PROD
fal_server= DRPROD
log_archive_dest_1 = 'LOCATION=/oradata/PROD/arch'
log_archive_dest_2 = 'SERVICE=DRPROD LGWR ASYNC NOAFFIRM'
log_archive_format = %t_%s.dbf
log_archive_start = true
remote_login_passwordfile = EXCLUSIVE
service_names = PROD

Step 5: Add the below parameters in DRPROD database parameter file :

db_name = "PROD" -- Must match the production database.
instance_name = DRPROD -- Instance identifier
lock_name_space = DRPROD -- Required when both PROD & DRPROD exist in same server
service_names = DRPROD -- Specifies the service names supported by the instance.
fal_client = DRPROD
fal_server = PROD
db_file_name_convert = ('/oradata/PROD/','/oradata/DRPROD/')
log_file_name_convert = ('/oradata/PROD/','/oradata/DRPROD/')

Step 6: Restore and Recover DRPROD Database

export ORACLE_SID=DRPROD
rman target /
connect auxiliary sys/welcome@DRPROD
run {
set until sequence = 100 thread=1;
duplicate target database for standby
dorecover;}

Step7:Enable Managed Media Recovery

sqlplus "/ as sysdba"
alter database recover managed standby database disconnect from session;

Database altered.

Standby Using RMAN Backups

2007-12-31T08:13:00.000-08:00

Primary Database Name: PROD Standby Name: DRPROD

Step 1: Backup the primary database physical structures(datafiles, archive logs) using RMAN

rman target /
RMAN> run
{
allocate channel c1 type disk;
backup database plus archivelog;
}

Step 2: Create controlfile for DRPROD using RMAN

RMAN> run
{
allocate channel c1 type disk;
backup current controlfile for DRPROD;
}

Step 3: Copy the backups to the DRPROD server using scp

Step 4: Make proper changes in the parameter files of both primary and DRPROD database

Add the below parameter in primary database parameter file :

db_name = PROD
instance_name = PROD
fal_client = PROD
fal_server= DRPROD
log_archive_dest_1 = 'LOCATION=/oradata/PROD/arch'
log_archive_dest_2 = 'SERVICE=DRPROD LGWR ASYNC NOAFFIRM'
log_archive_format = %t_%s.dbf
log_archive_start = true
remote_login_passwordfile = EXCLUSIVE
service_names = PROD

Step 5: Add the below parameters in DRPROD database parameter file :

db_name = "PROD" -- Must match the production database.
instance_name = DRPROD -- Instance identifier
lock_name_space = DRPROD -- Required when both PROD & DRPROD exist in same server
service_names = DRPROD -- Specifies the service names supported by the instance.
fal_client = DRPROD
fal_server = PROD
db_file_name_convert = ('/oradata/PROD/','/oradata/DRPROD/')
log_file_name_convert = ('/oradata/PROD/','/oradata/DRPROD/')

Step 6: Restore and Recover DRPROD Database

export ORACLE_SID=DRPROD
rman target /
startup nomount;
set dbid=3959250272;
restore DRPROD controlfile from '/oradata/PROD/flash_recovery_area/ORA10G/backupset/2007_12_09/o1_mf_ncnnf_TAG20071209T150009_3oqff2fb_.bkp';
sql 'alter database mount DRPROD database';
restore database;
list backup of archivelog all;
recover database until sequence 37;

Step7:Enable Managed Media Recovery

sqlplus "/ as sysdba"
alter database recover managed standby database disconnect from session;

Database altered.

RAC & ASM how to procedures

2007-12-06T11:14:00.000-08:00

CRS Start Stop Procedure. Run this as root user

crsctl stop crs

crsctl start crs

Enable auto startup of crs during boot process. Run this as root user

crsctl enable crs

Verify if all services are up using crs_stat

$ crs_stat -v -t
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora....C1.inst application 0/5 0/0 ONLINE ONLINE node1
ora....C2.inst application 0/5 0/0 ONLINE ONLINE node2
ora.PRODDB.db application 0/1 0/1 ONLINE ONLINE node1
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE node1
ora....1.lsnr application 0/5 0/0 ONLINE ONLINE node1
ora.node1.gsd application 0/5 0/0 ONLINE ONLINE node1
ora.node1.ons application 0/3 0/0 ONLINE ONLINE node1
ora.node1.vip application 0/0 0/0 ONLINE ONLINE node1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE node2
ora....2.lsnr application 0/5 0/0 ONLINE OFFLINE
ora.node2.gsd application 0/5 0/0 ONLINE ONLINE node2
ora.node2.ons application 0/3 0/0 ONLINE ONLINE node2
ora.node2.vip application 0/0 0/0 ONLINE ONLINE node2

Check/validate cluster registry

$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 762628
Used space (kbytes) : 3848
Available space (kbytes) : 758780
ID : 1551687088
Device/File Name : /dev/rdsk/c3t500A09832D81B9Cd20s3
Device/File integrity check succeeded
Device/File Name : /dev/rdsk/c3t500A09832D81B9Cd21s3
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Startup Sequence for each node

srvctl start nodeapps -n node1
srvctl start asm -n node1
srvctl start instance -d PRODDB -i "PRODDB1" -c "system/welcome as sysdba"
srvctl start nodeapps -n node2
srvctl start asm -n node2
srvctl start instance -d PRODDB -i "PRODDB2" -c "system/welcome as sysdba"

Shutdown Sequence for each node

srvctl stop instance -d PRODDB -i "PRODDB2” –o immediate –c “system/welcome as sysdba”
srvctl stop asm –n dlel8
srvctl stop nodeapps –n node2

srvctl stop instance –d PRODDB –i “PRODDB1” –o immediate –c “system/welcome as sysdba”
srvctl stop asm –n node1
srvctl stop nodeapps –n node1

Check statuses of all instances using srvctl

$ pwd
/u01/10gRAC
$ srvctl status database -d PRODDB
Instance PRODDB1 is running on node node1
Instance PRODDB2 is running on node node2

Check status of a specific RAC instance

$ srvctl status instance –d PRODDB –I PRODDB2
Instance PRODDB2 is running on node node2

Check status of a node applications on a specific node

$ srvctl status nodeapps -n node1
VIP is running on node: node1
GSD is running on node: node1
Listener is running on node: node1
ONS daemon is running on node: node1
$ srvctl status nodeapps -n node2
VIP is running on node: node2
GSD is running on node: node2
Listener is running on node: node2
ONS daemon is running on node: node2

Check status of ASM using srvctl

$ srvctl status asm -n node1
ASM instance +ASM1 is running on node node1.
$ srvctl status asm -n node2
ASM instance +ASM2 is running on node node2.

Syntax for identifying configured RAC DATABASES

$ srvctl config database
PRODDB

Display the configuration for the database

$ srvctl config database -d PRODDB
node1 PRODDB1 /u01/10gRAC/10gdb
node2 PRODDB2 /u01/10gRAC/10gdb

Display all services for the specified cluster database

$ srvctl config service -d PRODDB
PRODDB_taf PREF: PRODDB2 PRODDB1 AVAIL:

Display the configuration for node applications - (VIP, GSD, ONS, Listener)

$ srvctl config nodeapps -n node1 -a -g -s -l
VIP exists.: /node1-vip/157.170.142.129/255.255.255.0/eri0:ce2
GSD exists.
ONS daemon exists.
Listener exists.

Display the configuration for the ASM instance(s)

$ srvctl config asm -n node1
+ASM1 /u01/10gRAC/10gdb

Start all RAC instances with one command

./srvctl start database –d PRODDB -c “system/welcome as sysdba”

Stop all RAC instances with one command

./srvctl stop database –d PRODDB -c “system/welcome as sysdba”

Verify how many instances currently running using SQL

$ . ./ASMDB.env
$ sqlplus "/as sysdba"

SQL>
SELECT * FROM V$ACTIVE_INSTANCES;SQL>

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 node1:+ASM1
2 node2:+ASM2
$ . ./RACDB.env
$ sqlplus "/as sysdba"

SQL>
SELECT * FROM V$ACTIVE_INSTANCES;SQL>

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
1 node1:PRODDB1
3 node2:PRODDB2
set linesize 300
SELECT
inst_id
, instance_number inst_no
, instance_name inst_name
, parallel
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id;

INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST
---------- ---------- ---------------- --- ------------ ----------------- --------- ----------------------------------------------------------------
1 1 PRODDB1 YES OPEN ACTIVE NORMAL node1
2 2 PRODDB2 YES OPEN ACTIVE NORMAL node2

Various important init parameter values which should have same setting In both instances

SQL> select distinct name,value from gv$parameter where upper(name) in(
2 'ACTIVE_INSTANCE_COUNT',
3 'ARCHIVE_LAG_TARGET',
4 'CLUSTER_DATABASE',
5 'CLUSTER_DATABASE_INSTANCES',
6 'CONTROL_FILES',
7 'DB_BLOCK_SIZE',
8 'DB_DOMAIN',
9 'DB_FILES',
10 'DB_NAME',
11 'DB_RECOVERY_FILE_DEST',
12 'DB_RECOVERY_FILE_DEST_SIZE',
13 'DB_UNIQUE_NAME',
14 'UNDO_MANAGEMENT',
15 'CLUSTER_INTERCONNECTS',
16 'SERVICE_NAMES',
17 'LOCAL_LISTENER',
18 'REMOTE_LISTENER');
control_files +DG_DATA/PRODDB/controlfile/current.260.
636137965

cluster_database TRUE
cluster_database_instances 2
undo_management AUTO
db_name PRODDB
active_instance_count
db_files 200
cluster_interconnects
db_recovery_file_dest_size 0
db_block_size 8192
local_listener
remote_listener LISTENERS_PRODDB
db_recovery_file_dest
db_domain
service_names PRODDB
db_unique_name PRODDB
archive_lag_target 0

17 rows selected.

Various important init parameter values which should have different setting In both instances

SQL>
SQL> select name,value from gv$parameter where upper(name) in(
'INSTANCE_NUMBER',
'THREAD',
'UNDO_TABLESPACE',
'INSTANCE_NAME');
thread 3
instance_number 3
undo_tablespace UNDOTBS3
instance_name PRODDB2
thread 1
instance_number 1
undo_tablespace UNDOTBS1
instance_name PRODDB1

8 rows selected.
crsd log files $ pwd
/u01/10gRAC/10gcrs/log/node1/crsd
$ ls -ltr
total 5200
-rw-r--r-- 1 root root 2650250 Oct 30 16:14 crsd.log

Oracle Cluster Registry (OCR) log files & Cluster Synchronization Services (CSS) log files
$ pwd
/u01/10gRAC/10gcrs/log/node1/client
$ ls -lt more
total 224
-rw-r--r-- 1 oracle dba 12516 Oct 30 16:14 clsc.log
-rw-r--r-- 1 root root 718 Oct 30 15:23 css.log
-rw-r--r-- 1 oracle dba 17415 Oct 22 05:11 oifcfg.log
-rw-r----- 1 oracle dba 168 Oct 16 17:41 ocr_11947_3.log

Event Manager (EVM) log files
$ pwd
/u01/10gRAC/10gcrs/log/node1/evmd
$ ls -ltr
total 544
-rw-r--r-- 1 oracle dba 23997 Oct 30 15:29 evmd.log
-rw-r--r-- 1 oracle dba 239194 Oct 30 16:16 evmdOUT.log

RAC trace files
$ pwd
/u01/10gRAC/10gcrs/log/node1/racg
$ ls -ltr
total 26
drwxrwxrwt 2 oracle dba 512 Oct 16 13:50 racgmain
drwxrwxrwt 2 oracle dba 512 Oct 16 13:50 racgeut
drwxrwxrwt 2 oracle dba 512 Oct 16 13:50 racgevtf
-rw-r--r-- 1 root root 1142 Oct 29 15:46 ora.node2.vip.log
-rw-r--r-- 1 root root 1715 Oct 29 16:26 ora.node1.vip.log
-rw-r--r-- 1 oracle dba 32 Oct 29 16:26 evtf.log
-rw-r--r-- 1 oracle dba 928 Oct 29 16:26 ora.PRODDB.db.log
-rw-r--r-- 1 oracle dba 3372 Oct 30 15:26 ora.node1.ons.log

Query ASM disk groups, disks & clients instances
$ pwd
/u01/10gRAC
$
$ . ./ASM1.env
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 20 12:32:49 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SELECT NAME,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
DG_DATA NORMAL 42976 39432
DG_ARCH EXTERN 21488 21393

SQL> SELECT INSTANCE_NAME FROM V$ASM_CLIENT;

PRODDB1

SQL>
SQL> l
1* SELECT NAME,PATH FROM V$ASM_DISK
SQL> /

NAME PATH
------------------------------ --------------------------------------------------------------------------------
/dev/rdsk/c12t60A9800042E54716434453530707569d0s3
/dev/rdsk/c12t60A9800042E5471643444757A554167d0s3
/dev/rdsk/c12t60A9800042E5471643444757A547451d0s3
/dev/rdsk/c12t60A9800042E5471643444757A545879d0s3
DG_ARCH_0000 /dev/rdsk/c12t60A9800042E5471643444757A594441d0s3
DG_DATA_0001 /dev/rdsk/c12t60A9800042E5471643444757A58726Ed0s3
DG_DATA_0000 /dev/rdsk/c12t60A9800042E5471643444757A584B76d0s3

7 rows selected.

SELECT gname,sum(space) used_mb
FROM
(
SELECT space,gname
FROM (
SELECT g.name gname, a.parent_index pindex, a.name aname, vf.space,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g, v$asm_file vf
WHERE a.group_number = g.group_number
and a.file_number=vf.file_number(+)
and a.group_number=vf.group_number(+)
)
START WITH (MOD(pindex, POWER(2,24))) = 0
CONNECT BY PRIOR rindex = pindex
) GROUP BY gname;

GNAME USED_MB
------------------------------ ----------
DG_ARCH 2097152
DG_DATA 3546284032

SELECT CONCAT('+'gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc

FULL_PATH D S
---------------------------------------------------------------------- - -
+DG_ARCH/PRODDB Y Y
+DG_ARCH/PRODDB/PARAMETERFILE Y Y
+DG_DATA/PRODDB Y Y
+DG_DATA/PRODDB/CONTROLFILE Y Y
+DG_DATA/PRODDB/DATAFILE Y Y
+DG_DATA/PRODDB/ONLINELOG Y Y
+DG_DATA/PRODDB/TEMPFILE Y Y
+DG_ARCH/PRODDB/PARAMETERFILE/spfile.256.639080871 N Y
+DG_ARCH/PRODDB/spfilePRODDB.ora N N
+DG_DATA/PRODDB/CONTROLFILE/Current.260.639080591 N Y
+DG_DATA/PRODDB/DATAFILE/EXAMPLE.264.639080611 N Y

FULL_PATH D S
---------------------------------------------------------------------- - -
+DG_DATA/PRODDB/DATAFILE/SYSAUX.257.639080475 N Y
+DG_DATA/PRODDB/DATAFILE/SYSTEM.256.639080475 N Y
+DG_DATA/PRODDB/DATAFILE/UNDOTBS1.258.639080477 N Y
+DG_DATA/PRODDB/DATAFILE/UNDOTBS2.265.639080809 N Y
+DG_DATA/PRODDB/DATAFILE/USERS.259.639080477 N Y
+DG_DATA/PRODDB/DATAFILE/USERS.268.639139571 N Y
+DG_DATA/PRODDB/ONLINELOG/group_1.261.639080593 N Y
+DG_DATA/PRODDB/ONLINELOG/group_2.262.639080595 N Y
+DG_DATA/PRODDB/ONLINELOG/group_3.266.63908027 N Y
+DG_DATA/PRODDB/ONLINELOG/group_4.267.63908029 N Y
+DG_DATA/PRODDB/TEMPFILE/TEMP.263.639080603 N Y

Purge archive files in ASM environment
select 'alter diskgroup DSKGRP1 drop file
''//ARCHIVELOG/' to_char(b.creation_date,'YYYY_MM_DD') '/' a.name''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;
'ALTERDISKGROUPDSKGRP1DROPFILE''//ARCHIVELOG/'TO_CHAR(B.CRE
--------------------------------------------------------------------------------
alter diskgroup DSKGRP1 drop file
'//ARCHIVELOG/2007_12_05/thread_1_seq_96.257.64051781';

alter diskgroup DSKGRP1 drop file
'//ARCHIVELOG/2007_12_05/thread_1_seq_97.259.640517899';

alter diskgroup DSKGRP1 drop file
'//ARCHIVELOG/2007_12_05/thread_2_seq_29.258.640517897';

What are the different types of files which can placed in ASM managed raw devices
SQL> select distinct type from v$asm_file;

TYPE
----------------------------------------------------------------
ARCHIVELOG
CONTROLFILE
DATAFILE
ONLINELOG
PARAMETERFILE
TEMPFILE

6 rows selected.

Important ASM dictionary views

v$asm_diskgroup
v$asm_disk
v$asm_file
v$asm_template
v$asm_alias
v$asm_operation
List tablespaces in RAC ASM environment column file_name format a40
select tablespace_name, file_name,bytes/(1024*1024)
from dba_data_files
union
select tablespace_name, file_name,bytes/(1024*1024)
from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024)
------------------------------ -------------------------------------------------- -----------------
EXAMPLE +DG_DATA/PRODDB/datafile/example.264.639080611 100
SYSAUX +DG_DATA/PRODDB/datafile/sysaux.257.639080475 260
SYSTEM +DG_DATA/PRODDB/datafile/system.256.639080475 480
TEMP +DG_DATA/PRODDB/tempfile/temp.263.639080603 20
UNDOTBS1 +DG_DATA/PRODDB/datafile/undotbs1.258.639080477 50
UNDOTBS2 +DG_DATA/PRODDB/datafile/undotbs2.265.639080809 25
USERS +DG_DATA/PRODDB/datafile/users.259.639080477 5
Resize the datafile in ASM environment alter database datafile '+DG_DATA/PRODDB/datafile/example.264.639080611' resize 150m;
Add a datafile to a tablespace in ASM environment alter tablespace users add datafile '+DG_DATA' size 50m autoextend off;
Resize the tempfile in ASM environment alter database tempfile '+DG_DATA/PRODDB/tempfile/temp.263.639080603' resize 400m;

Verify if TAF is configured and working

Step 1: In node1 Verify the TNS entry. If the below doesnt exist add it

PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODDB)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD =BASIC)
(RETRIES =180)
(DELAY = 5)
)
)
)

In node 1 connect to the database PRODDB1 and verify the below

column name format a30
column value format a30
select name,value from v$parameter where name in('service_names','local_listener','remote_listener','db_name','instance_name');

SQL> SQL> SQL>
NAME VALUE
------------------------------ ------------------------------
instance_name PRODDB1
service_names PRODDB
local_listener LISTENER_node1
remote_listener LISTENERS_PRODDB
db_name PRODDB

In node1 verify the tns entries LISTENERS_PRODDB & LISTENER_node1 exist. If not add those.

LISTENERS_PRODDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
)

LISTENER_node1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODDB)
(INSTANCE_NAME = PRODDB1)
)
)

Perform the same steps in node 2 and all sub-sequent nodes

sqlplus scott/welcome@PRODDB. Verify that session is connected to the PRODDB1 database

SELECT
instance_name
, host_name
, NULL AS failover_type
, NULL AS failover_method
, NULL AS failed_over
FROM v$instance
UNION
SELECT
NULL
, NULL
, failover_type
, failover_method
, failed_over
FROM v$session
WHERE username = 'SCOTT’;

INSTANCE_NAME HOST_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------- ------------- --------------- -----------
PRODDB1 node1

#Now stop the PRODDB1 database

$ srvctl stop instance -d PRODDB -i PRODDB1 -o abort

$ srvctl status database -d PRODDB
Instance PRODDB1 is not running on node node1
Instance PRODDB2 is running on node node2

Now let's go back to our SQL session and rerun the SQL statement in the buffer:
COLUMN instance_name FORMAT a13
COLUMN host_name FORMAT a9
COLUMN failover_method FORMAT a15
COLUMN failed_over FORMAT a11

SELECT
instance_name
, host_name
, NULL AS failover_type
, NULL AS failover_method
, NULL AS failed_over
FROM v$instance
UNION
SELECT
NULL
, NULL
, failover_type
, failover_method
, failed_over
FROM v$session
WHERE username = 'SCOTT';

INSTANCE_NAME HOST_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------- ------------- --------------- -----------
PRODDB2 node2
SELECT BASIC NO

Load Distribution Across interconnects

We need minimum two interconnects then we can set the init parameter
CLUSTER_INTERCONNECTS = ip1:ip2:...:ipn. Setting this parameter does not provide interconnect failover interconnect failover is operating-system dependent.

Changing init parameters

alter system set open_cursors =502 scope=spfile sid='PRODDB1';
alter system set open_cursors =502 scope=spfile sid='PRODDB2';
alter system set job_queue_processes=10 scope=spfile sid='*' ;

Upgrading the RAC database from 10.2.0.1 to 10.2.0.3
Download patch set
Shutdown crs, database, asm, liseteners
Update crs home
Update oracle home
Start asm on node1
Start up nomount racdb on node1
Restart racdb on node1
Disable cluster_database init parameter setting
Restart racdb on node1
Upgrade database using startup upgrade catupgrd.sql & utlrp.sql
Enable cluster_database init parameter setting
Restart racdb on node1 and node2

Renaming & Relocating datafiles

SQL> ALTER DATABASE DATAFILE '+DG_DATA/PRODDB/datafile/users.268.639139571' OFFLINE;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
$ rman

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Dec 5 09:40:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (DBID=4038713422)

RMAN> COPY DATAFILE '+DG_DATA/PRODDB/datafile/users.268.639139571' TO '+DG_ARCH';

Starting backup at 05-DEC-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=491 instance=PRODDB1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DG_DATA/PRODDB/datafile/users.268.639139571
output filename=+DG_ARCH/PRODDB/datafile/users.260.640518077 tag=TAG20071205T094115 recid=2 stamp=640518077
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 05-DEC-07

RMAN> exit

Recovery Manager complete.
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 5 09:44:56 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>
ALTER DATABASE RENAME FILE '+DG_DATA/PRODDB/datafile/users.268.639139571' TO '+DG_ARCH/PRODDB/datafile/users.260.640518077'
SQL> 2
SQL> /

Database altered.

SQL> exit;

$ rman

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Dec 5 09:46:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (DBID=4038713422)

RMAN>

SWITCH DATAFILE '+DG_ARCH/PRODDB/datafile/users.260.640518077' TO COPY;
RMAN>
RMAN>

using target database control file instead of recovery catalog
datafile 7 switched to datafile copy "+DG_ARCH/PRODDB/datafile/users.260.640518077"

RMAN> exit

Recovery Manager complete.
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 5 09:46:33 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>
RECOVER DATAFILE '+DG_ARCH/PRODDB/datafile/users.260.640518077';SQL>
Media recovery complete.
SQL>
ALTER DATABASE DATAFILE '+DG_ARCH/PRODDB/datafile/users.260.640518077' ONLINE;
SQL>
Database altered.

Steps to change the RAC database from no-archive mode to archive mode

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/10gRAC/10gdb/dbs/arch
Oldest online log sequence 95
Current log sequence 96
SQL> alter system set log_archive_dest_state_1=enable scope=spfile sid='*';

System altered.
SQL> alter system set log_archive_dest_1='LOCATION=+DG_ARCH' scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size 2226904 bytes
Variable Size 916194600 bytes
Database Buffers 603979776 bytes
Redo Buffers 4325376 bytes
Database mounted.

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
+DG_ARCH/PRODDB/archivelog/2007_12_05/thread_1_seq_96.257.64051781
+DG_ARCH/PRODDB/archivelog/2007_12_05/thread_2_seq_29.258.640517897
+DG_ARCH/PRODDB/archivelog/2007_12_05/thread_1_seq_97.259.640517899
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DG_ARCH
Oldest online log sequence 97
Next log sequence to archive 98
Current log sequence 98
Multiplex Controlfiles SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_DATA/PRODDB/controlfile/cu
rrent.260.639080591

$ rman

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Dec 5 10:15:53 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (not mounted)

RMAN>

restore controlfile to '+DG_DATA/PRODDB/controlfile/control2.ctl' from '+DG_DATA/PRODDB/controlfile/current.260.639080591';

RMAN>
RMAN>
Starting restore at 05-DEC-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=373 instance=PRODDB1 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 05-DEC-07

RMAN>
restore controlfile to '+DG_DATA/PRODDB/controlfile/control3.ctl' from '+DG_DATA/PRODDB/controlfile/current.260.639080591';

RMAN>
Starting restore at 05-DEC-07
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 05-DEC-07

RMAN>

SQL>
alter system set control_files='+DG_DATA/PRODDB/controlfile/current.260.639080591','+DG_DATA/PRODDB/controlfile/control2.ctl','+DG_DATA/PRODDB/controlfile/control3.ctl' scope=spfile sid='*';
SQL>
System altered.

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_DATA/PRODDB/controlfile/cu
rrent.260.639080591
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size 2226904 bytes
Variable Size 916194600 bytes
Database Buffers 603979776 bytes
Redo Buffers 4325376 bytes
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_DATA/PRODDB/controlfile/cu
rrent.260.639080591, +DG_DATA/
PRODDB/controlfile/control2.ct
l, +DG_DATA/PRODDB/controlfile
/control3.ctl
SQL> alter database mount;

Database altered.

SQL> alter database open;

Trasportable tablespaces

2007-12-06T11:01:00.000-08:00

==Main advantages is plugin option
==Perform the below steps in the source tablespaces
==Pre-Req is we need to identify all the users who has objects in the source tablespace from dba_segments and create those users in the target.
==select owner from dba_segments where tablespace_name='APPS_OMO'

create tablespace apps_omo
datafile '/db2/FDEV/oradata/apps_omo.dbf'
size 200 M
autoextend off
segment space management auto
default storage (initial 128 K
next 128 K
pctincrease 0 minextents 1
) online ;

create user vijay identified by vijay default tablespace apps_omo temporary tablespace temp;
grant connect,resource,dba to vijay;
connect vijay/vijay
create table vijay_user as select * from apps.fnd_user;
alter tablespace apps_omo read only;

exp userid=\'sys/isupp0rt as sysdba\' transport_tablespace=y tablespaces=apps_omo file='/db2/FDEV/oradata/apps_omo.dmp' triggers=y constraints=n grants=n

==Perform the below steps in the target tablespace

create user vijay identified by vijay default tablespace system temporary tablespace temp;

cp /db2/FDEV/oradata/apps_omo.dbf /db1/FTEST/oradata/apps_omo.dbf

imp userid=\'sys/isupp0rt as sysdba\' transport_tablespace=y file='/db2/FDEV/oradata/apps_omo.dmp' datafiles='/db1/FTEST/oradata/apps_omo.dbf' \
tablespaces=apps_omo tts_owners=vijay fromuser=vijay touser=vijay

alter user vijay default tablespace apps_omo;
grant connect,resource,dba to vijay;

Step By Step 10g Physical Standby

2007-12-06T10:11:00.001-08:00

1) Verify Primary Database

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

SQL> select force_logging from v$database;

FOR
---
YES

2) Connect to the primary database and create the standby control file:

SQL> alter database create standby controlfile as
'/u01/oradata/backup/standby.ctl';

Database altered.

3) Copy all primary database data files to the Standby host using hotbackup :

Copy the backup datafiles, standby controlfile, all available archived redo logs,
and online redo logs from the primary site to the standby site. Copying of the
online redo logs is necessary to facilitate switchover.

4) Configure networking components:

On the Primary host create a net service name that the Primary database can
use to connect to the Standby database. On the Primary host create a net
service name that Standby, when running on the Primary host, can use to
connect to the Primary, when it is running on the Standby host.
You must also configure a listener on the standby database. If you plan to
manage this standby database using the Data Guard broker, you must configure
the listener to use the TCP/IP protocol and statically register the standby
database service using its SID.

5) Set the initialization parameters for the primary database & Standby

Primary

log_archive_dest_1='LOCATION=/arch'
log_archive_dest_2='SERVICE=PROD_AV2 reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=%t_%s.dbf
log_archive_start=true

.

Standby
log_archive_dest_1='LOCATION=/arch'
log_archive_dest_state_1=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
standby_archive_dest=/arch
standby_file_management=auto
fal_server=PROD_AV2
fal_client=PROD_AV1
remote_arhive_enable=true
db_file_name_convert='/u01/oradata','/oradata'
log_file_name_convert ='/u01/oradata','/oradata'

6) Start the standby instance and mount the standby database.

oracle@hasunclu2:/export/home/oracle/temp/oracle> sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.0 - Production on Thu Mar 14 18:00:57 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initDGD.ora
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

7) Create standby redo log files, if necessary for maximum protection mode or real-time apply:

SQL> alter database add standby logfile
2 '/oradata/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/oradata/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/oradata/srl_3.dbf' size 20m;

Database altered.

SQL>

8) Verify that automatic archiving is occurring:

On the Primary database switch a log and verfy that it has been shipped
properly using the v$archive_dest view.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -------------------------------------------------------
VALID

SQL>

9) Optionally place Standby database in managed recovery:

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> exit

Step By Step 8i Physical Standby

2007-12-06T09:58:00.001-08:00

============================================================================================
Step 1 Prepare the stand by environment file
============================================================================================

# Usage= . STDBY.env
#The following created by vijay cherkuri
#STDBY is using /stdby/db_tier/product/817 techstack
#This database is the standby database for PRODDB
TERM=vt220
DOMAIN=oracle.com
HOST=leda.oracle.com
USER=oracle
PWD=/usr/users/oracle
HOME=/usr/users/oracle
SHELL=/bin/ksh
PATH=/usr/users/oracle/bin:/bin:/usr/bin/X11:/usr/local/bin/zip:/usr/local/bin:/usr/bin:/usr/sbin:.:/usr/local/bin:/stdby/db_tier/product/817/bin
ORACLE_HOME=/stdby/db_tier/product/817
ORACLE_SID=STDBY
ORAENV_ASK=YES
TNS_ADMIN=/stdby/db_tier/product/817/network/admin

============================================================================================
Step 2 Configure the primary database listner.ora and tnsnames.ora
============================================================================================

leda-oracle > cd $ORACLE_HOME/network/admin
leda-oracle > ls
stbu.lsr listener.ora tnsnames.ora
leda-oracle > tail -20 listener.ora
IFILE= /stdby/db_tier/product/817/network/admin/stby.lsr

leda-oracle > cat stby.lsr
PRODDB =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= PRODDB))
(ADDRESS= (PROTOCOL= TCP)(Host= leda.oracle.com)(Port= 1575))
)

SID_LIST_PRODDB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /stdby/db_tier/product/817)
(SID_NAME = PRODDB)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /stdby/db_tier/product/817)
(PROGRAM = extproc)
)
)

STARTUP_WAIT_TIME_PRODDB = 0
CONNECT_TIMEOUT_PRODDB = 15
TRACE_LEVEL_PRODDB = OFF

LOG_DIRECTORY_PRODDB = /stdby/db_tier/product/817/network/admin
LOG_FILE_PRODDB = PRODDB
TRACE_DIRECTORY_PRODDB = /stdby/db_tier/product/817/network/admin
TRACE_FILE_PRODDB = PRODDB

leda-oracle > cat tnsnames.ora

IFILE = /stdby/db_tier/product/817/network/admin/stby.tsr

leda-oracle > cat stby.tsr
PRODDB = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=leda.oracle.com)(PORT=1575))
(CONNECT_DATA=(SID=PRODDB))

============================================================================================
Step 3 Create the stand by controlfile
============================================================================================

leda-oracle > . PRODDB.env
leda-oracle > env grep SID
ORACLE_SID=PRODDB

leda-oracle > svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.4.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 131056612 bytes
Fixed Size 103396 bytes
Variable Size 77688832 bytes
Database Buffers 52428800 bytes
Redo Buffers 835584 bytes
Database mounted.
Database opened.
SVRMGR>

SVRMGR> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ha01/PRODDB/archive/
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13

SVRMGR> select name from v$database;
NAME
---------
PRODDB
1 row selected.
SVRMGR>

SVRMGR> alter database create standby controlfile as '/tmp/stbycf.f'
Statement processed.

SVRMGR> exit
Server Manager complete.

============================================================================================
Step 4 Create the listener in standby database so that it can accept the requests(archive logs)
which are coming from the primary database in tcp mode.
============================================================================================
leda-oracle > vi listener.ora

IFILE= /stdby/db_tier/product/817/network/admin/stby.lsr

STDBY =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= STDBY))
(ADDRESS= (PROTOCOL= TCP)(Host= leda.oracle.com)(Port= 1576))
)

SID_LIST_STDBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /stdby/db_tier/product/817)
(SID_NAME = STDBY)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /stdby/db_tier/product/817)
(PROGRAM = extproc)
)
)

STARTUP_WAIT_TIME_STDBY = 0
CONNECT_TIMEOUT_STDBY = 15
TRACE_LEVEL_STDBY = OFF

LOG_DIRECTORY_STDBY = /stdby/db_tier/product/817/network/admin
LOG_FILE_STDBY = STDBY
TRACE_DIRECTORY_STDBY = /stdby/db_tier/product/817/network/admin
TRACE_FILE_STDBY = STDBY

============================================================================================
Step 5 Add the tns entry in primary database network configuration file(tnsnames.ora)
so that primary database can transfer the logs to the standby database in tcp mode.
============================================================================================

leda-oracle > cat tnsnames.ora

IFILE = /stdby/db_tier/product/817/network/admin/stby.tsr

leda-oracle > cat stby.tsr
PRODDB = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=leda.oracle.com)(PORT=1575))
(CONNECT_DATA=(SID=PRODDB))

STDBY = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=leda.oracle.com)(PORT=1576))
(CONNECT_DATA=(SID=STDBY))

============================================================================================
Step 6 Configure the primary database init file
============================================================================================

control_files = /ha01/PRODDB/control/cntrl01.ctl,/ha01/PRODDB/control/cntrl02.ctl
background_dump_dest = /dev03/PRODDB/admin/bdump
core_dump_dest = /dev03/PRODDB/admin/cdump
user_dump_dest = /dev03/PRODDB/admin/udump
db_name = PRODDB
instance_name = PRODDB
db_domain = ".WORLD"
optimizer_mode = choose
compatible = 8.1.7
db_files = 200
db_block_size = 8192
db_file_multiblock_read_count = 16
db_block_buffers = 6400
shared_pool_size = 50000000
shared_pool_reserved_size = 3000000
aq_tm_processes = 1
job_queue_interval = 300
distributed_transactions = 60
db_block_lru_latches = 16
session_cached_cursors = 100
log_checkpoint_interval = 10000000
processes = 100
dml_locks = 4000
open_cursors = 10000
max_rollback_segments = 105
row_locking = always
rollback_segments = RBS1,RBS2,RBS3,RBS4
enqueue_resources = 1000
timed_statistics = True
log_checkpoints_to_alert = True
oracle_trace_collection_size = 104857600
max_dump_file_size = 104857600
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary
nls_language = american
nls_territory = america
utl_file_dir = /sqlcom/inbound,/sqlcom/outbound,/sqlcom/log,/sqlcom/out,/usr/tmp
os_authent_prefix = "OPS$"
remote_os_authent = True
_optimizer_undo_changes = True
_trace_files_public = True
job_queue_processes = 1 # set to 0 during upgrade
_push_join_union_view = True # (Outerbay)
dbwr_io_slaves = 2
_TRU64_DIRECTIO_DISABLED = True
parallel_max_servers = 0
sort_area_size = 12288000
sort_area_retained_size = 4096000
remote_login_passwordfile = None
log_archive_dest_1 = 'LOCATION=/ha01/PRODDB/archive/'
log_archive_dest_2 = 'SERVICE=STDBY MANDATORY REOPEN=10'
log_archive_dest_state_2 = ENABLE
log_archive_dest_state_1 = ENABLE #use defer value in case if we want to stop
log_archive_min_succeed_dest = 2
log_archive_trace = 8
log_archive_start = True
log_archive_format = PRODDB%S.ARC
log_buffer = 819200

============================================================================================
Step 7 Configure the standby database init file
============================================================================================
db_file_name_convert ='/ha01/PRODDB/db_files/','/ha01/STDBY/db_files/'
log_file_name_convert ='/ha01/PRODDB/onlinelogs','/ha01/STDBY/onlinelogs/'
log_archive_dest = /ha01/STDBY/archive
standby_archive_dest = /ha01/STDBY/archive
lock_name_space = STDBY #So that share memory will have different oracle_sid pointer
log_archive_trace = 8
log_archive_start = True
log_archive_format = STDBY%S.ARC
log_buffer = 819200
control_files = /ha01/STDBY/control/stbycf.f
background_dump_dest = /dev03/STDBY/admin/bdump
core_dump_dest = /dev03/STDBY/admin/cdump
user_dump_dest = /dev03/STDBY/admin/udump
db_name = PRODDB #db name should be always same
instance_name = STDBY #This should be always different
db_domain = ".WORLD"
optimizer_mode = choose
compatible = 8.1.7
db_files = 200
db_block_size = 8192
db_file_multiblock_read_count = 16
db_block_buffers = 6400
shared_pool_size = 50000000
shared_pool_reserved_size = 3000000
aq_tm_processes = 1
job_queue_interval = 300
distributed_transactions = 60
db_block_lru_latches = 16
session_cached_cursors = 100
log_checkpoint_interval = 10000000
processes = 100
dml_locks = 4000
open_cursors = 10000
max_rollback_segments = 105
row_locking = always
rollback_segments = RBS1,RBS2,RBS3,RBS4
enqueue_resources = 1000
timed_statistics = True
log_checkpoints_to_alert = True
oracle_trace_collection_size = 104857600
max_dump_file_size = 104857600
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary
nls_language = american
nls_territory = america
utl_file_dir = /sqlcom/inbound,/sqlcom/outbound,/sqlcom/log,/sqlcom/out,/usr/tmp
os_authent_prefix = "OPS$"
remote_os_authent = True
_optimizer_undo_changes = True
_trace_files_public = True
job_queue_processes = 4 # set to 0 during upgrade
_push_join_union_view = True # (Outerbay)
event ="10929 trace name context forever"
event ="10932 trace name context level 2"
dbwr_io_slaves = 1
_TRU64_DIRECTIO_DISABLED = True
parallel_max_servers = 0
sort_area_size = 12288000
sort_area_retained_size = 4096000
remote_login_passwordfile = None

============================================================================================
Step 8 Mount the stand by database
============================================================================================

leda-oracle > env grep STDBY
ORACLE_SID=STDBY

leda-oracle > svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.4.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 130434020 bytes
Fixed Size 103396 bytes
Variable Size 77066240 bytes
Database Buffers 52428800 bytes
Redo Buffers 835584 bytes
ORA-01666: controlfile is for a standby database

Above error will come if you use the normal mount command.

SVRMGR> alter database mount standby database;
Statement processed.
SVRMGR>

============================================================================================
Step 9 Test the archive destinations and automatic archiving process
============================================================================================

Connect to the primary database switch the logs to create the archive files

SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 130434020 bytes
Fixed Size 103396 bytes
Variable Size 77066240 bytes
Database Buffers 52428800 bytes
Redo Buffers 835584 bytes
Database mounted.
Database opened.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Here sys went into hang-state due to archiving failed in one destination.

Mon Feb 9 10:31:44 2004
ARC0: Beginning to archive log# 3 seq# 13
ARC0: Archiving not possible: No available destinations
ARC0: Failed to archive log# 3 seq# 13
Mon Feb 9 10:35:53 2004
ARC0: Beginning to archive log# 3 seq# 13
ARC0: Error 12154 connecting to standby host 'STDBY'
ARC0: Error 12154 creating archivelog file 'STDBY'
ARC0: Archiving not possible: error count exceeded
ARC0: Failed to archive log# 3 seq# 13

Connected to sys in the primary database and run the following query and noticed that
some problem exist for the tns entry STDBY

SQL> select error,destination from v$archive_dest;

ERROR DESTINATION
-------------------------------------------------------------------------------------------

/ha01/PRODDB/archive/ ORA-12154: TNS:could not resolve service name STDBY

Now run the following and noticed that

leda-oracle > tnsping STDBY

TNS Ping Utility for DEC OSF/1 AXP: Version 8.1.7.4.0 - Production on 09-FEB-200
4 10:58:38

(c) Copyright 1997 Oracle Corporation. All rights reserved.

TNS-03506: Failed to create address binding
leda-oracle > tnsping PRODDB

Noticed that ")" parenthesis is missing in the tnsnames.ora in the primary database and corrected
it.Now archive files are getting created in the stand by destination.

SVRMGR> alter system switch logfile;
Statement processed.
Statement processed.
SVRMGR>

============================================================================================
Step 10 configure the stand by database for the managed recovery mode.
============================================================================================

Run the following query to make sure that there is no gap sequence.

SVRMGR> select instance_name from v$instance;
INSTANCE_NAME
----------------
STDBY
1 row selected.

SELECT high.thread#,"LowGap#","HighGap#"
FROM
(SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
(
SELECT A.thread#,a.sequence#
FROM
(
SELECT *
FROM v$archived_log
) a,

( SELECT thread#, MAX(next_change#) gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread#=b.thread#
AND a.next_change# > gap1
)
GROUP BY thread#
) high,
(
SELECT thread#, MIN(sequence#) "LowGap#"
FROM
(
SELECT thread#,sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <=next_change# AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#;

THREAD# LowGap# HighGap#
---------- ---------- ----------
0 rows selected.
SVRMGR>
SVRMGR> recover automatic standby database;
ORA-00279: change 7035549840061 generated at 02/09/2004 11:15:50 needed for thre
ad 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000018.ARC
ORA-00280: change 7035549840061 for thread 1 is in sequence #18
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000018.ARC' no longer needed for
this recovery
ORA-00308: cannot open archived log '/ha01/STDBY/archive/STDBY0000000018.ARC'
ORA-27037: unable to obtain file status
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested filename AUTO CANCEL}
CANCEL;
Media recovery cancelled.

Now enable the managed media recovery(automatic)

SVRMGR> recover managed standby database;

============================================================================================
Step 11 Test the managed recovery mode.(standby backlog)
============================================================================================

Connect to primary database.Issue log switches

SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> select max(sequence#) from v$log_history;
MAX(SEQUEN
----------
21
1 row selected.
SVRMGR> exit
Server Manager complete.
leda-oracle > cd
leda-oracle > . STDBY.env
leda-oracle > svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.4.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> select max(sequence#) from v$log_history;
MAX(SEQUEN
----------
21
1 row selected.
SVRMGR>

This confirms that automatic media recover is working fine and stand by is upto date.

============================================================================================
Step 12 Testing the regular maintenance activity.
Test 1 Add a tablespace in primary database
============================================================================================

leda-oracle > tail -f alert_STDBY.log
Media Recovery Log /ha01/STDBY/archive/STDBY0000000021.ARC
Media Recovery Waiting for thread 1 seq# 22
Tue Feb 10 12:49:32 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000022.ARC
Media Recovery Waiting for thread 1 seq# 23
Tue Feb 10 12:49:49 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000023.ARC
Media Recovery Log /ha01/STDBY/archive/STDBY0000000024.ARC
Media Recovery Log /ha01/STDBY/archive/STDBY0000000025.ARC
Media Recovery Waiting for thread 1 seq# 26

SVRMGR> select instance_name from v$instance;
INSTANCE_NAME
----------------
PRODDB
1 row selected.
SVRMGR> create tablespace test1 datafile
2> '/ha01/PRODDB/db_files/test1.dbf' size 20 M;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR>

Now check the STDBY alertfile

leda-oracle > tail -f alert_STDBY.log
Media Recovery Log /ha01/STDBY/archive/STDBY0000000021.ARC
Media Recovery Waiting for thread 1 seq# 22
Tue Feb 10 12:49:32 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000022.ARC
Media Recovery Waiting for thread 1 seq# 23
Tue Feb 10 12:49:49 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000023.ARC
Media Recovery Log /ha01/STDBY/archive/STDBY0000000024.ARC
Media Recovery Log /ha01/STDBY/archive/STDBY0000000025.ARC
Media Recovery Waiting for thread 1 seq# 26
Tue Feb 10 13:11:20 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000026.ARC
Tue Feb 10 13:11:20 2004
Errors in file /dev03/STDBY/admin/bdump/dbw0_1685828.trc:
ORA-01157: cannot identify/lock data file 79 - see DBWR trace file
ORA-01110: data file 79: '/ha01/STDBY/db_files/test1.dbf'
ORA-27037: unable to obtain file status
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
Media Recovery failed with error 1670
ORA-283 signalled during: ALTER DATABASE RECOVER managed standby database ...

From the above we can see that recovery got cancelled by oracle server.
Now run the following.

SVRMGR> select instance_name from v$instance;
INSTANCE_NAME
----------------
STDBY

SVRMGR> select name from v$datafile where status='RECOVER';
NAME
------------------------------------------------------------------------
/ha01/STDBY/db_files/test1.dbf
1 row selected.

SVRMGR> alter database create datafile '/ha01/STDBY/db_files/test1.dbf' as
'/ha01/STDBY/db_files/test1.dbf';
Statement processed.

create the standby controlfile again in primary database
copy this to the stand by database
shutdown standby database
mount the standby again with this new controlfile

SVRMGR> recover managed standby database;

Now if we check the alert file for STDBY we can say that media recovery completed
for 26 and STDBY is waiting for log27.

Tue Feb 10 13:17:39 2004
Media Recovery Start: Managed Standby Recovery
Media Recovery Log
Media Recovery Log /ha01/STDBY/archive/STDBY0000000026.ARC
Media Recovery Waiting for thread 1 seq# 27

Now run the following query

SVRMGR> select name from v$datafile where status='RECOVER';
NAME
---------------------------------------------------------------------------
0 rows selected.

============================================================================================
Step 13 Testing the regular maintenance activity.
Test 2 Adding,Dropping,renaming,clearing,resetting logfiles
============================================================================================

SVRMGR> select instance_name from v$instance;
INSTANCE_NAME
----------------
PRODDB
1 row selected.

SVRMGR> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE

SVRMGR> select group#,member from v$logfile;

GROUP# MEMBER
---------- --------------------------------------------
3 /ha01/PRODDB/onlinelogs/log_3_01.dbf

3 /ha01/PRODDB/onlinelogs/log_3_02.dbf

2 /ha01/PRODDB/onlinelogs/log_2_01.dbf

2 /ha01/PRODDB/onlinelogs/log_2_02.dbf

1 /ha01/PRODDB/onlinelogs/log_1_01.dbf

1 /ha01/PRODDB/onlinelogs/log_1_02.dbf

SVRMGR> ALTER DATABASE DROP LOGFILE MEMBER '/ha01/PRODDB/onlinelogs/log_3_01.dbf';
Statement processed.

SVRMGR> host rm /ha01/PRODDB/onlinelogs/log_3_01.dbf

SVRMGR> alter database add logfile member '/ha01/PRODDB/onlinelogs/log_3_01.dbf' to group 3;
Statement processed.

SVRMGR> ALTER DATABASE ADD LOGFILE GROUP 4 ('/ha01/PRODDB/onlinelogs/log_4_01.dbf',
'/ha01/PRODDB/onlinelogs/log_4_02.dbf') SIZE 0K;
Statement processed.
SVRMGR> ALTER DATABASE DROP LOGFILE GROUP 4;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.

Now go and check the alert file in the stand by database.

Tue Feb 10 13:40:24 2004
Media Recovery Log /ha01/STDBY/archive/STDBY0000000027.ARC
Media Recovery Log /ha01/STDBY/archive/STDBY0000000028.ARC
Media Recovery Waiting for thread 1 seq# 29

Renaming the logfiles also doesnt require any maintenance.

Note: If you use clear aunarchived logfile or resetlogs,create controlfile commands in the primary database
then you have to re-create the stand by database.

============================================================================================
Step 14 Testing the regular maintenance activity.
Test 3 Unrecoverable and Nologging Operations
============================================================================================

Took a backup of the primary database at 6 am on 02/10/2004

create table amspro.test nologging STORAGE (
INITIAL 64K
MINEXTENTS 1
NEXT 100 M
MAXEXTENTS 2147483645
FREELISTS 1
FREELIST GROUPS 1 ) as select * from dba_users;

SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> select unrecoverable_change#,
to_char(unrecoverable_time, 'mm-dd-yyyy hh24:mi:ss'),
name from v$datafile
where unrecoverable_change#!=0;

UNRECOVERA TO_CHAR(UNRECOVERAB NAME

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

7.0355E+12 02-10-2004 13:58:56 /ha01/PRODDB/db_files/users01.dbf

Above time is greater than the backup time.So we need the backup for this tablespace
now run the same query in stand by database and see the unrecoverable change.If stby
unrecoverable change is less than primary change then we need the following.

to confirm that the standby database is invalid we run the following.

SVRMGR> recover managed standby database cancel;
Media recovery complete.
SVRMGR> alter database open read only;
Statement processed.

SVRMGR> SELECT COUNT(*) FROM AMSPRO.TEST;
COUNT(*)
----------
ORA-01578: ORACLE data block corrupted (file # 82, block # 682546)
ORA-01110: data file 82: '/ha01/STDBY/db_files/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

For solving the above problem you can do the following in the primary database.

SVRMGR> alter tablespace begin backup;
SVRMGR> host cp /ha01/PRODDB/db_files/users01.dbf /ha01/STDBY/db_files/users01_new.dbf
SVRMGR> alter tablespace end backup;
SVRMGR> alter database create standby controlfile as '/ha01/STDBY/control/new_stby.ctl';
Shutdown the standby database.
move the above users01_new.dbf as users01.dbf in standby database.
modify the standby init parameter file to point to the new stand by database controlfile.
mount the standby database.
run the following command

SVRMGR> alter database open read only;
alter database open read only
*
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/ha01/STDBY/db_files/system01.dbf'

This is because scn in system file and the newly copied file is different.
So an incomplete media recovery is required.Go the primary database issue some log switches
and Do the following

SVRMGR> recover automatic standby database;
ORA-00279: change 7035550013584 generated at 02/17/2004 16:02:18 needed for thread 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000046.ARC
ORA-00280: change 7035550013584 for thread 1 is in sequence #46
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000046.ARC' no longer needed for
this recovery
ORA-00308: cannot open archived log '/ha01/STDBY/archive/STDBY0000000046.ARC'
ORA-27037: unable to obtain file status
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested filename AUTO CANCEL}
/ha01/STDBY/archive/STDBY0000000046.ARC
Log applied.
ORA-00279: change 7035550013681 generated at 02/17/2004 16:09:11 needed for thre
ad 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000047.ARC
ORA-00280: change 7035550013681 for thread 1 is in sequence #47
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000046.ARC' no longer needed for
this recovery
Specify log: {=suggested filename AUTO CANCEL}
CANCEL
Media recovery cancelled.
SVRMGR> alter database open read only;
Statement processed.
SVRMGR> SELECT COUNT(*) FROM AMSPRO.TEST;
COUNT(*)
----------
822
1 row selected.

So we are fine now.

============================================================================================
Step 15 Testing the regular maintenance activity.
Test 4 resize the data file in primary database.
============================================================================================
Resizing the datafile in primary database.

SVRMGR> alter database datafile '/ha01/PRODDB/db_files/users01.dbf' autoextend
on maxsize 4000 M;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.

Now check the alert file in stand by database.No errors.So we are fine

============================================================================================
Step 16 Testing the regular maintenance activity.
Test 5 Add the data file in primary database.
============================================================================================

Add datafile in primary database.

SVRMGR> alter tablespace users add datafile '/ha01/PRODDB/db_files/users07.dbf' size 10 M;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.

Now check the alert file in stand by database

Tue Feb 10 14:20:57 2004
Errors in file /dev03/STDBY/admin/bdump/dbw0_1685828.trc:
ORA-01157: cannot identify/lock data file 83 - see DBWR trace file
ORA-01110: data file 83: '/ha01/STDBY/db_files/users07.dbf'
ORA-27037: unable to obtain file status
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
Media Recovery failed with error 1670
ORA-283 signalled during: ALTER DATABASE RECOVER managed standby database ...
Tue Feb 10 14:20:57 2004
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL...

Run the following in stand by database.

SVRMGR> alter database create datafile '/ha01/STDBY/db_files/users07.dbf' as
'/ha01/STDBY/db_files/users07.dbf';
Statement processed.

SVRMGR> select name from v$datafile where status='RECOVER';
NAME
---------------------------------------------------------------------------
/ha01/STDBY/db_files/users07.dbf
1 rows selected.

This is because still the controlfile is not updated with the new information.

So for the resolution do the following.

Create the standby controlfile again in primary database
copy this to the stand by database
shutdown standby database
start the standby again with this new file

Now run the following query

SVRMGR> select name from v$datafile where status='RECOVER';
NAME
---------------------------------------------------------------------------
0 rows selected.

SVRMGR> alter database open read only;
alter database open read only
*
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/ha01/STDBY/db_files/system01.dbf'
SVRMGR> select max(sequence#) from v$log_history;
MAX(SEQUEN
----------
41
1 row selected.
SVRMGR> recover automatic standby database;
ORA-00279: change 7035549864186 generated at 02/10/2004 14:53:13 needed for thre
ad 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000042.ARC
ORA-00280: change 7035549864186 for thread 1 is in sequence #42
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000042.ARC' no longer needed for
this recovery
ORA-00308: cannot open archived log '/ha01/STDBY/archive/STDBY0000000042.ARC'
ORA-27037: unable to obtain file status
Compaq Tru64 UNIX Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested filename AUTO CANCEL}
/ha01/STDBY/archive/STDBY0000000042.ARC
Log applied.
ORA-00279: change 7035550013574 generated at 02/17/2004 16:01:35 needed for thre
ad 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000043.ARC
ORA-00280: change 7035550013574 for thread 1 is in sequence #43
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000042.ARC' no longer needed for
this recovery
Specify log: {=suggested filename AUTO CANCEL}
/ha01/STDBY/archive/STDBY0000000043.ARC
Log applied.
ORA-00279: change 7035550013575 generated at 02/17/2004 16:01:40 needed for thre
ad 1
ORA-00289: suggestion : /ha01/STDBY/archive/STDBY0000000044.ARC
ORA-00280: change 7035550013575 for thread 1 is in sequence #44
ORA-00278: log file '/ha01/STDBY/archive/STDBY0000000043.ARC' no longer needed for
this recovery
Specify log: {=suggested filename AUTO CANCEL}
CANCEL
Media recovery cancelled.
SVRMGR> alter database open read only;
Statement processed.

============================================================================================
Step 17 Make standby as primary database and primary as standby-Failover testing
============================================================================================

Connected.
SVRMGR> startup mount standby database;
MGR-00127: invalid combination of STARTUP options
SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area 130434020 bytes
Fixed Size 103396 bytes
Variable Size 77066240 bytes
Database Buffers 52428800 bytes
Redo Buffers 835584 bytes
SVRMGR> alter database mount standby database;
Statement processed.
SVRMGR> alter database activate standby database;
Statement processed.
SVRMGR> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 130434020 bytes
Fixed Size 103396 bytes
Variable Size 77066240 bytes
Database Buffers 52428800 bytes
Redo Buffers 835584 bytes
Database mounted.
SVRMGR> alter database open read write;
Statement processed.

Note:Once actiavates standby will have the new log sequence number.

SVRMGR> select sequence# from v$log;
SEQUENCE#
----------
0
0
1
3 rows selected.

============================================================================================
Step 18 Recover the original primary database with the new logs which got created
in standby database.
============================================================================================

SVRMGR> select sequence# from v$log;
SEQUENCE#
----------
0
0
1
3 rows selected.

SVRMGR> select count(*) from amspro.test;
COUNT(*)
----------
822
1 row selected.
SVRMGR> delete from amspro.test where rownum<200;> commit;
Statement processed.
SVRMGR> alter system switch logfile
2> /
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> select sequence# from v$log;
SEQUENCE#
----------
5
6
7
3 rows selected.

SVRMGR> alter tablespace system begin backup;
Statement processed.
SVRMGR>
SVRMGR> host cp /ha01/STDBY/db_files/system01.dbf /ha01/PRODDB/db_files/system_new01.dbf
SVRMGR> alter tablespace system end backup;
Statement processed.

RAID Concepts Explained

2007-12-06T09:50:00.001-08:00

1. Overview of RAID configurations and Oracle
---------------------------------------------

RAID-0:
-------

RAID-0 offers pure disk striping. The striping allows a large file to be spread
across multiple disks/controllers, providing concurrent access to data because
all the controllers are working in parallel. It does not provide either data
redundancy or parity protection. In fact, RAID-0 is the only RAID level focusing
solely on performance. Some vendors, such as EMC, do not consider level 0 as
true RAID and do not offer solutions based on it. Pure RAID-0 significantly
lowers MTBF, since it is highly prone to downtime. If any disk in the array
(across which Oracle files are striped) fails, the database goes down.

RAID-1:
-------

With RAID-1, all data is written onto two independent disks (a "disk pair") for
complete data protection and redundancy. RAID-1 is also referred to as disk
mirroring or disk shadowing. Data is written simultaneously to both disks to
ensure that writes are almost as fast as to a single disk. During reads, the
disk that is the least busy is utilized. RAID-1 is the most secure and reliable
of all levels due to full 100-percent redundancy. However, the main disadvantage
from a performance perspective is that every write has to be duplicated.
Nevertheless, read performance is enhanced, as the read can come from either
disk. RAID-1 demands a significant monetary investment to duplicate each disk;
however, it provides a very high Mean time between failures (MTBF). Combining
RAID levels 0 and 1 (RAID-0+1) allows data to be striped across an array,
in addition to mirroring each disk in the array.

RAID-0 & RAID-1:
----------------

If RAID/0 is then combined with RAID/1 (mirroring) this then provides the resilience,
but at a cost of having to double the number of disk drives in the configuration.
There is another benefit in some RAID/1 software implementations in that the requested
data is always returned from the least busy device.
This can account for a further increase in performance of over 85% compared
to the striped, non-mirrored configuration.

Write performance on the other hand has to go to both pieces of the software
mirror. If this second mirror piece is on a second controller (as would
normally be recommended for controller resilience), this degradation can be as
low as 4 percent.

RAID-3:
-------

In a RAID 3 configuration, a single drive is dedicated to storing error correction
or parity data. Information is striped across the remaining drives.
RAID/3 dramatically reduces the level of concurrency that the disk
subsystem can support (I/O's per second) to a comparable software mirrored
solution . The worst case for a system using RAID/3, would be an OLTP
environment, where the number of rapid transactions is numerous and response
time is critical.

So to put it simply, if the environment is mainly read only (Eg Decision
Support) RAID/3 provides disk redundancy with read performance slightly
improved, but at the cost of write performance. Unfortunately, even decision
support databases still do a significant amount of disk writing since complex
joins, unique searches etc still do temporary work, thus involving disk
writing.

RAID-5:
-------

Instead of total disk mirroring, RAID-5 computes and writes parity for every
write operation. The parity disks avoid the cost of full duplication of the
disk drives of RAID-1. If a disk fails, parity is used to reconstruct data
without system loss. Both data and parity are spread across all the disks in
the array, thus reducing disk bottleneck problems. Read performance is improved,
but every write has to incur the additional overhead of reading old parity,
computing new parity, writing new parity, and then writing the actual data, with
the last two operations happening while two disk drives are simultaneously
locked. This overhead is notorious as the RAID-5 write penalty. This write
penalty can make writes significantly slower. Also, if a disk fails in a RAID-5
configuration, the I/O penalty incurred during the disk rebuild is extremely
high. Read-intensive applications (DSS, data warehousing) can use RAID-5 without
major real-time performance degradation (the write penalty would still be
incurred during batch load operations in DSS applications). In terms of storage,
however, parity constitutes a mere 20-percent overhead, compared to the
100-percent overhead in RAID-1 and 0+1.
Initially, when RAID-5 technology was introduced, it was labeled as the
cost-effective panacea for combining high availability and performance.
Gradually, users realized the truth, and until about a couple of years ago,
RAID-5 was being regarded as the villain in most OLTP shops. Many sites
contemplated getting rid of RAID-5 and started looking at alternative solutions.
RAID 0+1 gained prominence as the best OLTP solution for people who could afford
it. Over the last two years, RAID-5 is making a comeback either as hardware-based
RAID-5 or as enhanced RAID-7 or RAID-S implementations. However, RAID-5 evokes
bad memories for too many OLTP database architects.

RAID-S:
-------

RAID S is EMC's implementation of RAID-5. However, it differs from pure RAID-5
in two main aspects:
(1) It stripes the parity, but it does not stripe the data.
(2) It incorporates an asynchronous hardware environment with a write cache.
This cache is primarily a mechanism to defer writes, so that the overhead of
calculating and writing parity information can be done by the system, while it
is relatively less busy (and less likely to exasperate the user!). Many users of
RAID-S imagine that since RAID-S is supposedly an enhanced version of RAID-5,
data striping is automatic. They often wonder how they are experiencing I/O
bottlenecks, in spite of all that striping. It is vital to remember that in
RAID-S, striping of data is not automatic and has to be done manually via
third-party disk-management software.

RAID-7:
-------
RAID-7 also implements a cache, controlled by a sophisticated built-in real-time
operating system. Here, however, data is striped and parity is not. Instead,
parity is held on one or more dedicated drives. RAID-7 is a patented architecture
of Storage Computer Corporation.

2. Pro's and Cons of Implementing RAID technology
-------------------------------------------------

There are benefits and disadvantages to using RAID, and those depend on the
RAID level under consideration and the specific system in question.

In general, RAID level 1 is most useful for systems where complete redundancy
of data is a must and disk space is not an issue. For large datafiles or
systems with less disk space, this RAID level may not be feasible. Writes
under this level of RAID are no faster and no slower than 'usual'.

For all other levels of RAID, writes will tend to be slower and reads will be
faster than under 'normal' file systems. Writes will be slower the more
frequently ECC's are calculated and the more complex those ECC's are.
Depending on the ratio of reads to writes in your system, I/O speed may have a
net increase or a net decrease. RAID can improve performance by distributing
I/O, however, since the RAID controller spreads data over several physical
drives and therefore no single drive is overburdened.

The striping of data across physical drives has several consequences besides
balancing I/O. One additional advantage is that logical files may be created
which are larger that the maximum size usually supported by an operating
system. There are disadvantages, as well, however. Striping means that it is
no longer possible to locate a single datafile on a specific physical drive.
This may cause the loss of some application tuning capabilities. Also, in
Oracle's case, it can cause database recovery to be more time-consuming. If a
single physical disk in a RAID array needs recovery, all the disks which are
part of that logical RAID device must be involved in the recovery.

One additional note is that the storage of ECC's may require up to 20%
more disk space than would storage of data alone, so there is some disk
overhead involved with usage of RAID.

3. RAID and Oracle
------------------

The usage of RAID is transparent to Oracle. All the features specific to
RAID configuration are handled by the operating system and go on behind-
the-scenes as far as Oracle is concerned. Different Oracle file-types
are suited differently for RAID devices. Datafiles and archive logs can be
placed on RAID devices, since they are accessed randomly. Redo logs should be
not be put on RAID devices, since they are accessed sequentially and
performance is enhanced in their case by having the disk drive head near the
last write location. However, mirroring of redo log files is strongly
recommended by Oracle.

In terms of administration, RAID is far simple than using Oracle
techniques for data placement and striping.

Recommendations:

In general, RAID usually impacts write operations more than read operation.
This is specially true where parity need to be calculated (RAID 3, RAID 5, etc).
Online or archived redo log files can be put on RAID 1 devices.
You should not use RAID 5. 'TEMP' tablespace data files should also go on
RAID1 instead of RAID5 as well. The reason for this is that streamed
write performance of distributed parity (RAID5) isn't as good as that of
simple mirroring (RAID1).

Swap space can be used on RAID devices without affecting Oracle.

====================================================================================
RAID Type of RAID Control Database Redo Log Archive Log
File File File File
====================================================================================
0 Striping Avoid* OK* Avoid* Avoid*
------------------------------------------------------------------------------------
1 Shadowing OK OK Recommended Recommended
------------------------------------------------------------------------------------
0+1 Striping + OK Recommended Avoid Avoid
Shadowing (1)
------------------------------------------------------------------------------------
3 Striping with OK Avoid Avoid Avoid
Static Parity (2)
------------------------------------------------------------------------------------
5 Striping with OK Avoid Avoid Avoid
Rotating Parity (2)
------------------------------------------------------------------------------------

* RAID 0 does not provide any protection against failures. It requires a strong backup
strategy.
(1) RAID 0+1 is recommended for database files because this avoids hot spots and gives
the best possible performance during a disk failure. The disadvantage of RAID 0+1
is that it is a costly configuration.
(2) When heavy write operation involves this datafile

Step By Step 11.5.8.->11.5.10 Apps Upgrade

2007-12-06T09:48:00.000-08:00

Apply the autoconfig patch as applmgr in concurrent & app tiers.

adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3817226.log \
patchtop=/staging/patches/3817226 driver=u3817226.drv \
workers=6 interactive=yes options=novalidate

run the following to validate the technstack in app-tier as applmgr
/staging/patches/3594604/fnd/patch/115/bin/applxkValidateRollup.sh appspass=appdi01s

Apply the latest autoconfig template patch as applmgr in concurrent & app tiers.
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3594604.log \
patchtop=/staging/patches/3594604 driver=u3594604.drv \
workers=6 interactive=yes options=novalidate

Now build the autoconfig xml file file as applmgr in concurrent & app tiers.
adbldxml.sh tier=apps appsuser=APPS appspasswd=appdi01s

Now re-instantiate the the configuration files in the app tier with the autoconfig as applmgr
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/PROJ10_projdemo1.xml appspass=appdi01s

Now re-instantiate the configuration files in the conc and admin tier with the autoconfig as applmgr
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/PROJ10_projdemo2.xml appspass=appdi01s

Generate the autoconfig appsutil.zip for the RDBMS ORACLE_HOME as applmgr in concurrent tier
perl $AD_TOP/bin/admkappsutil.pl

Move and unzip the appsutil to RDBMS ORACLE_HOME as oracle user in db tier
cp /appl7/u01/oraclep/PROJ10appl/admin/out/appsutil.zip /appl7/u01/oraclep/PROJ10db/9.2.0/
unzip appsutil.zip

Enable the autoconfig in the database tier as oracle user in db tier
$ORACLE_HOME/appsutil/bin/adbldxml.sh tier=db appsuser=apps appspasswd=appdi01s

Run the autoconfig in the database tier as oracle user in db tier
$ORACLE_HOME/appsutil/bin/adconfig.sh contextfile=$ORACLE_HOME/appsutil/PROJ10_projdemo2.xml appspass=appdi01s

Run the required sys grants script as oracle user in db tier
cd $ORACLE_HOME
mkdir admin
cp /appl7/u01/oraclep/PROJ10appl/admin/adgrants.sql .
sqlplus /as sysdba
@adgrants.sql applsys;

Run adadmin and create a named snapshot as applmgr in db tier & app-tiers.

Apply the AD mini-pack I as applmgr
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3180816.log \
patchtop=/staging/patches/3180816 driver=u3180816.drv \
workers=6 interactive=yes options=novalidate

Apply the technology stack validation utility
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3971265.log \
patchtop=/staging/patches/3971265 driver=u3971265.drv \
workers=3 interactive=yes options=novalidate

Following developer upgrade takes 30 mins. We have to do this upgrade simuntatenously in projdemo1 and projdemo2 servers.
Upgrade the developer6i to patch set 16 in db and apptiers & apply the latest developer6i patches
cd $ORACLE_HOME
unzip /staging/patches/p3596539_600_AIX.zip
cd $ORACLE_HOME/developer6i_patch16
check $ORACLE_HOME/network/jre11/lib/aix/native_threads is in $LIBPATH and $LD_LIBRARY_PATH
./patch_install.sh 2>&1 tee patch_install_p16.log
cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install
cp $ORACLE_HOME/developer6i_patch16/bin/genshlib $ORACLE_HOME/bin
mkdir $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116
unzip /staging/patches/p3941497*
unzip /staging/patches/p3309083*
unzip /staging/patches/p3333818*
unzip /staging/patches/p2632838*
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/Bug3531106
cp genshlib $ORACLE_HOME/bin
chmod ug+x $ORACLE_HOME/bin/genshlib
cd $ORACLE_HOME/lib
cp libiffw.a libiffw.a.PRE_Bug3531106
cp libiwfw.a libiwfw.a.PRE_Bug3531106
cp libiffw.so.0 libiffw.so.0.PRE_Bug3531106
cp libiwfw.so.0 libiwfw.so.0.PRE_Bug3531106
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/Bug3531106
ar vt $ORACLE_HOME/lib/libiffw.a iftm.o
ar vt $ORACLE_HOME/lib/libiwfw.a iwbaeh.o
ar r $ORACLE_HOME/lib/libiffw.a iftm.o
ar r $ORACLE_HOME/lib/libiwfw.a iwbaeh.o
ar vt $ORACLE_HOME/lib/libiffw.a iftm.o
ar vt $ORACLE_HOME/lib/libiwfw.a iwbaeh.o
cd $ORACLE_HOME/lib
$ORACLE_HOME/bin/genshlib iffw 0
$ORACLE_HOME/bin/genshlib iwfw 0
cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk install
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/Bug3534091
cp $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/Bug3534091/genshlib $ORACLE_HOME/bin/
cd $ORACLE_HOME/lib
cp libiwfw.a libiwfw.a.ORIG
cp libiwfw.so.0 libiwfw.so.0.ORIG
cp libixw.a libixw.a.ORIG
cp libixw.so.0 libixw.so.0.ORIG
cp libiffw.a libiffw.a.ORIG
cp libiffw.so.0 libiffw.so.0.ORIG
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/Bug3534091
ar vt $ORACLE_HOME/lib/libiwfw.a iwalx.o
ar vt $ORACLE_HOME/lib/libixw.a ixal.o
ar vt $ORACLE_HOME/lib/libiffw.a iftdlg.o
ar r $ORACLE_HOME/lib/libiwfw.a iwalx.o
ar r $ORACLE_HOME/lib/libixw.a ixal.o
ar r $ORACLE_HOME/lib/libiffw.a iftdlg.o
ar vt $ORACLE_HOME/lib/libiffw.a iftdlg.o
ar vt $ORACLE_HOME/lib/libiwfw.a iwalx.o
ar vt $ORACLE_HOME/lib/libixw.a ixal.o
cd $ORACLE_HOME/lib
$ORACLE_HOME/bin/genshlib iwfw 0
$ORACLE_HOME/bin/genshlib ixw 0
$ORACLE_HOME/bin/genshlib iffw 0
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/bug3941497
cd $ORACLE_HOME/forms60/java
cp -r oracle/forms oracle/forms.pre3941497
jar xvf $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/bug3941497/bug3941497.jar
ls -la oracle/forms/handler/ComponentItem.class
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/bug3530728
mkdir bug3530728cs bug3530728web
cp cs/icgc.o bug3530728cs
cp web/icgc.o bug3530728web
cp cs/iqf.o bug3530728cs
cp web/iqf.o bug3530728web
cp cs/ictse.o bug3530728cs
cp web/ictse.o bug3530728web
cp cs/iqfsc.o bug3530728cs
cp web/iqfsc.o bug3530728web
cp genshlib $ORACLE_HOME/bin
cd $ORACLE_HOME/lib
cp libic.a libic.a.ORI
cp libic.so libic.so.ORI
cp libic.so.0 libic.so.0.ORI
cp libicw.a libicw.a.ORI
cp libicw.so libicw.so.ORI
cp libicw.so.0 libicw.so.0.ORI
cp libiq.a libiq.a.ORI
cp libiq.so libiq.so.ORI
cp libiq.so.0 libiq.so.0.ORI
cp libiqw.a libiqw.a.ORI
cp libiqw.so libiqw.so.ORI
cp libiqw.so.0 libiqw.so.0.ORI
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/bug3530728/cs
ar rv $ORACLE_HOME/lib/libic.a icgc.o
ar rv $ORACLE_HOME/lib/libic.a ictse.o
ar rv $ORACLE_HOME/lib/libiq.a iqf.o
ar rv $ORACLE_HOME/lib/libiq.a iqfsc.o
cd $ORACLE_HOME/developer6i_patch16/patches_on_top_of_patch116/bug3530728/web
ar rv $ORACLE_HOME/lib/libicw.a icgc.o
ar rv $ORACLE_HOME/lib/libicw.a ictse.o
ar rv $ORACLE_HOME/lib/libiqw.a iqf.o
ar rv $ORACLE_HOME/lib/libiqw.a iqfsc.o
cd $ORACLE_HOME/lib
$ORACLE_HOME/bin/genshlib ic 0
$ORACLE_HOME/bin/genshlib icw 0
$ORACLE_HOME/bin/genshlib iq 0
$ORACLE_HOME/bin/genshlib iqw 0
cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk install

Upgrade the J-Intiator to 1.3.x
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3930041.log \
patchtop=/staging/patches/3930041 driver=u3930041.drv \
workers=3 interactive=yes options=novalidate
cd /appl7/u01/oraclep/PROJ10comn/util/jinitiator
unzip /staging/patches/p3516136_131_GENERIC.zip
/staging/patches/3930041/fnd/patch/115/bin/jinit.sh no_jinit skippatch

Apply the forms6i i/o patch in database and app tiers with the following commands simuntaniously
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3392966.log \
patchtop=/staging/patches/3392966 driver=c3392966.drv \
workers=3 interactive=yes options=novalidate

Relink the following executables and force the regeneration of jarfiles from adadmin
f60webmx ar60run ar60runb ar60rund
Now check the time stamp of the following files.

increase the system tablespace to 9.5GB and setup the new tablespaces for 11.5.10
sqlplus system/manager /staging/patches/3180164/ad/patch/115/sql/adruncts.sql 15 min

apply the shipping pre-req patch
adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3649470.log \
patchtop=/staging/patches/3649470 driver=u3649470.drv \
workers=3 interactive=yes options=novalidate

run the payables upgrade steps

prepare opm for update

apply the maintenance pack in the db-tier & app tiers.

adpatch defaultsfile=$APPL_TOP/admin/PROJ10/defaults.txt logfile=3140000.log \
patchtop=/staging/patches/3140000 driver=u3140000.drv \
workers=12 \
interactive=yes \
flags=hidepw \
options=novalidate,norevcache

Step-By-Step 8174->9205 DB Upgrade

2007-12-06T09:40:00.000-08:00

Description
9i Software can be located under /staging/patches/9iDisk1,/staging/patches/9iDisk2,/staging/patches/9iDisk3,/staging/patches/9iDisk4
Please note that this is a 64bit software.If o/s is running on 32bit then we need to install 32bit 9i software.In general 32bit oracle can run
on 64bit operating system.(some times an oracle patch is required to do this). But 64bit oracle can't run on a 32bit operating system.
Since we have 64 bit processors and hardware 64 bit O/S we can use the 9i 64 bit software.
Steps for 64 bit upgrade were mentioned under the work sheet o/s upgrade
Login as root into erp2 create a directory called
erp2:/u017/u01/oraclep/PROJ10db>mkdir /u017/u01/oraclep/PROJ10db/9.2.0
erp2:/u017/u01/oraclep/PROJ10db>chown -R orat10:t10dbap /u017/u01/oraclep/PROJ10db/9.2.0
Edit /etc/oraInst.loc file and identify the inventory directory path.
erp2:/u017/u01/oraclep/PROJ10db/9.2.0 >cat /etc/oraInst.loc
#9isoftware install logs can be found in the below directory.Edited by vijay.cherukuri 11/11/2004
inventory_loc=/u017/u01/oraclep/oraInventory
inst_group=t7grp
Now change the permissions on the inventory directory
erp2:/u017/u01/oraclep/PROJ10db/9.2.0 >chown -R orat10:t10dbap /u017/u01/oraclep/oraInventory

Now run the script /staging/patches/9iDisk1/rootpre.sh as root.
Edit the /etc/oratab file to make sure that automatic startup is diabled.
erp2:/u017/u01/oraclep/PROJ10db/9.2.0 >cat /etc/oratab
#databases will not startup automatically after the reboot.Edited by vijay.cherukuri 11/11/2004
PROJ8:/u018/u01/oraclep/PROJ8db/8.1.7:N
*:/u018/u01/oraclep/PROJ8ora/iAS:N
*:/u018/u01/oraclep/PROJ8ora/8.0.6:N
*:/u017/u01/oraclep/PROJ10db/9.2.0:N
Now login as orat10 to my client PC and use the exceed client wizard to start a xterm session. Make sure that DISPLAY is SET.
Run the script /staging/patches/9iDisk1/runInstaller

Now choose the new oracle_home name and new oracle_home directory to point to 9.2.0 directory.

Now start the installation and choose with no database option(we are installing only the software).

Please make sure that you select the /usr/java131 directory

Finish the installation.Now create the new environment file for 9.2.0 oracle_home

Now source the 9.2.0/PROJ10.env and apply patch 3567364 to apply the OUI 2.2.0.19 fix the inventory directories

erp2:/u017/u01/oraclep/PROJ10db/8.1.7 >unzip -od /u017/u01/oraclep/PROJ10db/9.2.0 /staging/patches/p3890564_11i_AIX64.zip

erp2:/u017/u01/oraclep/PROJ10db/9.2.0/appsoui/setup >perl OUIsetup.pl

Now install the 9.2.0.5 patch set. Download patch p3501955_9205_AIX64-5L.zip from metalink. Unzip the patch.Uncompress the file using the following command . cpio -idcv < path="/staging/patches/OPatch:$PATH"> select sum(bytes)/(1024*1024) from dba_data_files where tablespace_name='SYSTEM';
SUM(BYTES)
----------
6750
1 row selected.

SVRMGR> select sum(bytes)/(1024*1024) from dba_data_files where tablespace_name='RBS';
SUM(BYTES)
----------
20500

19.Now setup the new big rollback segments in the old 8.1.7 database.Make sure that these are offline. You are going to use this only for upgrade

CREATE ROLLBACK SEGMENT rbs1
TABLESPACE rbs
STORAGE
( INITIAL 400 M
NEXT 100 M
MAXEXTENTS 50 MINEXTENTS 2 OPTIMAL 2000 M);

CREATE ROLLBACK SEGMENT rbs2
TABLESPACE rbs
STORAGE
( INITIAL 400 M
NEXT 100 M
MAXEXTENTS 50 MINEXTENTS 2 OPTIMAL 2000 M);
Now create the new initPROJ10.ora for the 9.2.0 database with the following parameters

db_name = PROJ10
control_files = /u017/u02/oraclep/PROJ10data/cntrl01.dbf, /u017/u05/oraclep/PROJ10cntrl2/cntrl02.dbf, /u017/u06/oraclep/PROJ10cntrl3/cntrl03.dbf
db_block_size = 8192
compatible = 8.1.7
_system_trig_enabled = TRUE
row_locking = always
nls_date_format = DD-MON-RR # Is this still true with 11.5 ?
nls_numeric_characters = .,
nls_sort = binary
nls_language = american
nls_territory = america
max_enabled_roles = 40
user_dump_dest = /u017/u01/oraclep/PROJ10db/8.1.7/admin/PROJ10/udump
background_dump_dest = /u017/u01/oraclep/PROJ10db/8.1.7/admin/PROJ10/bdump
core_dump_dest = /u017/u01/oraclep/PROJ10db/8.1.7/admin/PROJ10/cdump
max_dump_file_size = 10240 # trace file size
timed_statistics = true
_trace_files_public = TRUE
db_files = 500
dml_locks = 500
open_cursors = 700
session_cached_cursors = 100
enqueue_resources = 5000
db_block_buffers = 7000
log_checkpoint_timeout = 72000
log_checkpoint_interval = 100000
log_buffer = 5242880
log_checkpoints_to_alert = TRUE
rollback_segments = rbs1,rbs2
sort_area_size = 256000
shared_pool_reserved_size = 40000000
_shared_pool_reserved_min_alloc = 4100
shared_pool_size = 800000000
utl_file_dir = /u017/u01/oraclep/PROJ10comn/u01emp/APPLPTMP
utl_file_dir =/u017/u01/oraclep/PROJ10appl/wms/11.5.0/reports/US/labels
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxwlk/11.5.0/edi/inbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxwlk/11.5.0/edi/outbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxdii/11.5.0/edi/inbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxdii/11.5.0/edi/outbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxske/11.5.0/edi/inbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxske/11.5.0/edi/outbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxsol/11.5.0/edi/inbound
utl_file_dir =/u017/u01/oraclep/PROJ10appl/xxsol/11.5.0/edi/outbound
aq_tm_processes = 1
job_queue_processes = 2
job_queue_interval = 90
log_archive_start = true # if you want automatic archiving
log_archive_dest_1 = 'location=/u017/u03/oraclep/PROJ10arc1/'
log_archive_dest_2 = 'location=/u017/u04/oraclep/PROJ10arc2/'
log_archive_format = proddb_PROJ10_%s.arc
parallel_query_servers = 8
parallel_min_servers = 0
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
connect /as sysdba
SPOOL upgrade.log
STARTUP MIGRATE
@u0900010.sql

SQL> SELECT comp_name, status, version FROM dba_registry;

COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.5.0
Oracle9i Packages and Types VALID 9.2.0.5.0
JServer JAVA Virtual Machine LOADED 8.1.7
Java Packages LOADED 8.1.7
Oracle XDK for Java LOADED 8.1.7
Oracle interMedia Text LOADED 8.1.7
Real Application Clusters LOADED
Oracle interMedia LOADED 8.1.6.0.0
Oracle Spatial LOADED 8.1.6.0.0

Upgrade the components

SQL> @cmpdbmig.sql

SQL> SELECT comp_name, status, version FROM dba_registry;

The following is an example of the output you will see when issuing this query:

COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.5.0
Oracle9i Packages and Types VALID 9.2.0.5.0
JServer JAVA Virtual Machine VALID 9.2.0.5.0
Oracle9i Java Packages VALID 9.2.0.5.0
Oracle XDK for Java UPGRADED 9.2.0.2.0
Oracle interMedia Text LOADED 8.1.7
Oracle9i Real Application Clusters INVALID 9.2.0.5.0
Oracle interMedia LOADED 8.1.6.0.0
Oracle Spatial LOADED 8.1.6.0.0

12 rows selected.

Upgrade Intermedia Text

SQL> @$ORACLE_HOME/ord/im/admin/imdbma.sql
ord/im/admin/u0800060.sql

PL/SQL procedure successfully completed.

SQL>@$ORACLE_HOME/ord/im/admin/u0800060.sql

Upgrade Visual Information retreival

SQL> @$ORACLE_HOME/ord/vir/admin/virdbma.sql
NOT INSTALLED

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area 936020264 bytes
Fixed Size 743720 bytes
Variable Size 872415232 bytes
Database Buffers 57344000 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
Upgrade the oracle text

SQL>@$ORACLE_HOME/ctx/admin/s0902000.sql
cSQL>onnect ctxsys/ctxsys
SQL>@$ORACLE_HOME/ctx/admin/u0900010.sql
SQL>connect /as sysdba
SQL>@$ORACLE_HOME/ctx/admin/s0902000.sql
SQL>connect ctxsys/ctxsys
SQL>@$ORACLE_HOME/ctx/admin/u0902000.sql

Upgrade the spatial
SQL>connect /as sysdba
SQL>@$ORACLE_HOME/md/admin/mdprivs.sql
SQL>connect mdsys/mdsys
SQL>@$ORACLE_HOME/md/admin/c81Xu9X.sql

Run utl_recomp.recomp_parallel as sysdba with 4 parallel slaves
Run the adadmin to re-create the grants and synonyms in the apps schema
COMP_NAME STATUS VERSION
------------------------------ ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.5.0
Oracle9i Packages and Types VALID 9.2.0.5.0
JServer JAVA Virtual Machine VALID 9.2.0.5.0
Oracle9i Java Packages VALID 9.2.0.5.0
Oracle XDK for Java VALID 9.2.0.2.0
Oracle Text VALID 9.2.0.5.0
Oracle9i Real Application Clus INVALID 9.2.0.5.0
ters

Oracle interMedia VALID 9.2.0.5.0
Spatial VALID 9.2.0.5.0

use this if adadmin fails to star after the upgrade
connect applsys/pwd@PROJ10
grant all on ad_invoker_tasks to SYSTEM with grant option;
grant all on ad_timestamps to SYSTEM with grant option;
grant select on fnd_application to SYSTEM;
grant select on fnd_oracle_userid to SYSTEM;
grant select on fnd_product_installations to SYSTEM;
sqlplus apps/pwd@PROJ10
@adinvsps.pls
@adinvspB.pls
@adaprs.pls
@adaprb.pls
@admlss.pls
@admlbb.pls
connect system/manager@PROJ10
alter package AD_DDL compile;
alter package AD_INST compile;
alter package AD_MCURR compile;
alter package AD_MLS compile;
alter package AD_DDL compile body;
alter package AD_INST compile body;
alter package AD_MCURR compile body;
alter package AD_MLS compile body;
begin
ad_compile.compile_apps_ddl;
end;
/
now relink the adadmin
now start the adadmin and run the option
re-create grants and synonyms from adadmin

Step-By-Step 8174->9203 Upgrade

2007-12-06T09:39:00.001-08:00

Database Upgrade from 8.1.7.4 to 9.2.0.3
========================================

1.HOW TO UPGRADE E-BUSINESS SUITE 11.5.8 ON 8i(8.1.7.4) TO 9i(9.2.0.3).
----------------------------------------------------

Verify Certification Matrix.

Oracle applications 11.5.8 is certified to use with Oracle 9i Release 2.(9.2.0).
Also we are on latest version of Developer 6i patch 13.
Patch set 9 or later of Oracle developer 6i is certified for use with the Applications
using Oracle 9i Release 2(9.2.0) . Certified patch set : 9.2.0.2 Required.

2. Upgrading an Oracle Applications Release 11i database server from prior Oracle RDBMS
releases to Oracle9i Release 2 (9.2.0.3).

2.1 Prepare to create the 9.2.0 Oracle home - Decide where you want to install the 9.2.0 Oracle home
on the database server node. It must be in a different directory than the current Oracle home.
On the database server node, as the owner of the Oracle RDBMS file system and database instance,
unzip and extract the patch set file appropriate for your platform.

2.2 Create the 9.2.0 Oracle home.

(i) Log in to the database server node as the owner of the Oracle RDBMS file system
and database instance. Ensure that environment settings such as ORACLE_HOME are set
for the new Oracle home you are about to create, and not for any existing Oracle homes
on the database server node.In the available products window ,select American English
as the language.

(ii) In the installation types window, select Enterprise Edition, then make sure to
select software only database configuration option.The following components are required
by the Applications and must be installed:
- Oracle9I
- Oracle9i Enterprise Edition Options -> Oracle Spatial, Oracle Partitioning
- Oracle Net Services -> Oracle Net Listener, Oracle Connection Manager
- Oracle 9i Development Kit -> Oracle C++ Call Interface, Oracle CallInterface (OCI), Oracle Programmer, Oracle XML Developer's Kit
- Oracle HTTP Server -> BC4J Runtime for Database, Oracle Mod PL/SQL Gateway

(iii) In the Component Locations window, set the JRE 1.3.1 directory t to ORACLE_HOME/jre/1.3.1,
JRE 1.1.8 directory to ORACLE_HOME/jre/1.1.8, and OUI to ORACLE_HOME/appsoui.
This is to make the directory structure consistent with Rapid Install.

(iii)When the Oracle Net Configuration Assistant window appears, use it to configure the Oracle Net listener for the Applications database instance as well as external procedures.

2.3 Install the 9.2.0.3 patch set files.

On the database server node, as the owner of the Oracle RDBMS file system and database instance,
perform only the installation portion of the "Installing the Patch Set steps while observing the
following:
(i)The ORACLE_HOME environment setting must point to the new 9.2.0 Oracle home,
not to any other existing Oracle homes on the database server node.
The ORACLE_SID environment setting must not be set to the Applications database SID,
nor to any other .It is not necessary to shut down the Applications database instance
before applying the patch set, because the patch set is being applied to the
new 9.2.0 Oracle home, which the database instance is not using yet.

(ii)Do not perform the "Post Install Actions" section yet.

2.4 Apply RDBMS patches for 9.2.0.3.

Apply the following RDBMS patches to the 9.2.0 Oracle home:

For all UNIX/Linux platforms:
2646480
2782919
2805382
2840233
For all platforms:
2757441
2805491
2949941

Attention: More information about fixes in Advanced Queues maybe found in OracleMetaLink alert 231074.1.

2.5 Shut down Applications server processes.

Shut down all Applications server processes except for the database instance.
The Applications will be unavailable to users until all tasks in this section are completed.

2.6 Upgrade the database instance.

Before running the upgrade, ensure that the following parameter, if it exists,
commented out: event="38004 trace name context forever, level 1"Otherwise it will cause deadlocks during the upgrade.

3 UPGRADE DATABASE FROM 8.1.7.4 TO 9i
========================================

3.1 32-bit/64-bit Information:
Starting with Oracle9i, the Server is always in 64-bit mode and ships with both a 32-bit and 64-bit client. If the Server product is 64-bit then 64-bit hardware (processors) is required.

3.2 Hardware Information:
Systems that are capable of more than 4 CPUs are certified only for the Enterprise Edition.

OS Product CertifiedWith Version Status Addtl.Info Components Other Install Issues

4.3.3 9.2 64 bit N/A N/A Certified Shown below None

5.2 - - -

Certification Matrix: Oracle Server - Enterprise Edition on IBM AIX -Based Systems

Note---For OS 5.2 Metalink is not showing any information. But both OS 4.3.3 and 5.2 are certifified for E-business Suite 11.5.8.

1.1 Upgrade path for Oracle 8.1.7.4 to Oracle 9.2.0.3.

If you are on version 8.1.7, you can directly upgrade your database to Oracle 9.2.

i. Verify all necessary OS patches are installed.
ii.Verifying version and options.
Select * from v$version;
Select * from v$option;

PERFORM a Full cold backup!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

iii. Avoid running out of space during the migration. If you run out of space in one of these tablespaces during the upgrade, you will need to perform the upgrade again.

(a) Prepare the system rollback segment:

Alter rollback segment system storage (maxextents 121 next 1M);

(b) Ensure plenty of free space in the SYSTEM tablespace. A minimum of 500 Mb additional free space:

Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';

(c) Ensure plenty of free space in the ROLLBACK tablespace with a minimumof 750 MB of free rollback segment space, with MAXEXTENTS set to at least 512. Ensure that you have at least 1 rollback segment of 70 Mb if the number of objects in the database exceeds 5000:

Select count(*) from dba_objects;

iv. List all objects that are not VALID before the upgrade.This list of fatal objects.

Select substr(owner,1,12) owner, substr(object_name,1,30) object,Substr(object_type,1,30) type,status from dba_objects where status <>'VALID';

v. To create a script to compile all invalid objects, before upgrading, run the script called utlrp.sql in the $ORACLE_HOME/rdbms/admin directory. This script recompiles all invalid PL/SQL in the database including views.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus sys/ as sysdba
SQL> @utlrp.sql

a) Run the script and than rerun the query to get invalid objects.

spool invalid_pre.lst
Select substr(owner,1,12) owner, Substr(object_name,1,30) object, Substr(object_type,1,30) type, status from dba_objects where status <>'VALID';
spool off

b) This last query will return a list of all objects that cannot be recompiled before the upgrade in the file 'invalid_pre.lst'

vi. Ensure ORACLE_SID is set to instance you want to upgrade.

echo $ORACLE_SID
echo $ORACLE_HOME

vii. As of Oracle 9i the sql NCHAR datatypes will be limited to the Unicode character set encoding only (UTF8 and AL16UTF16). Any other NCHAR datatype will no longer be supported. When upgrading to 9i the value of the NCHAR is based on the NCHAR datatype used in the Oracle8 version. If the old National character set is UTF8, the new will be UTF8. Otherwise the national character set is changed to AL16UTF16.

viii. Verify character set of the database:

sqlplus SYS/
select name, substrb(value$,1,40) value from props$;

ix. Check for corruption in the dictionary, use the following commands in sqlplus connected as sys:

set verify off
set space 0
set heading off
set feedback off
set pages 1000
spool analyze.sql
Select 'Analyze 'object_type' 'object_name' validate structure;' from dba_objects where owner='SYS' and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
This script (analyze.sql) should not return any errors.

x. Ensure that all Snapshot refreshes are successfully completed. And replication is stopped.

$ Sqlplus SYS/
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

xi. Stop the listener for the database.

$ lsnrctl
lsnrctl> stop

xii. Ensure no files need media recovery:

$ sqlplus SYS/
Select * from v$recover_file;

xiii. Ensure no files are in backup mode:

Select * from v$backup where status!='NOT ACTIVE';
This should return no rows.

xiv. Resolve any outstanding unresolved distributed transaction:

Select * from dba_2pc_pending;

If this returns rows you should do the following:

Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('');
commit;

xv. Disable all batch and cron jobs.

xvi. Ensure the users sys and system have 'system' as their default tablespace.

Select username, default_tablespace from dba_users where username in ('SYS','SYSTEM');
To modify use:

Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;

xvii. Optionally ensure the aud$ is in the system tablespace when auditing is enabled.

Select tablespace_name from dba_tables where table_name='AUD$';

xviii. Note down where all control files are located.

Select * from v$controlfile;

xix. Note down all sysdba users.

Select * from v$pwfile_users;

xx. If a passwordfile is used copy it to the new location. On unix the default is $ORACLE_HOME/dbs/orapw.

xxi. Shutdown the database.

$ sqlplus SYS/
SQL> Shutdown immediate

xxii. Change the init.ora file:

(a) Make a backup of the init.ora file.
(b) Verify that the parameter DB_DOMAIN is set properly.
(c) Ensure there is a value for DB_BLOCK_SIZE
(d) Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this explicitly to zero, during the upgrade
(e) Comment out the AQ_TM_PROCESSES parameter, put in a new and set this explicitly to zero, during the upgrade
(f) If archiving is enabled set LOG_ARCHIVE_START=TRUE
(g) Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST are set to an explicit directory
(h) Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade
(i) Set the parameter OPTIMIZER_MODE to CHOOSE during the upgrade
(j) Comment out obsoleted parameters(list in appendix A).
(k) Comment out SNAPSHOT_REFRESH_? parameters
(l) Either leave COMPATIBLE unset in your initialization parameter file or set COMPATIBLE to 8.1.x. Setting this parameter a lower or a higher value than 8.1.X results in an error during the upgrade.
(m) If you are using a passwordfile set the parameter REMOTE_LOGIN_PASSWORDFIcLE=NONE
(n) If you have set the parameter NLS_LENGTH_SEMANTICS to CHAR, change the value to BYTE during the upgrade.
(o) If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade.
(p) If the parameter RESOURCE_MANAGER_PLAN is set, comment it out as a workaround for bug 2610769

xxiii. Check for adequate freespace on archive log destination file systems.

xxiv. Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG

xxv. If needed copy the listener.ora and the tnsnames.ora to the new location (when no TNS_ADMIN env. Parameter is used)

cp $ORACLE_HOME/network/admin /network/admin

xxvi. If needed copy the init.ora file to the new oracle_home or Create a link to the init.ora.
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora
OR
ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora

xxvii. Also check 'ifile' parameters in the init.ora, to be set to the correct file. if an IFILE is used, verify the above mentioned parameter for the init.ora and copy this to the correct location. Change the IFILE entry in the init.ora file when this file changes from location.

xxviii. Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:
::N

xxix. Update the environment variables like ORACLE_HOME and PATH.
$ . oraenv
xxx. Make sure the following enviroment variables point to the new Release directories:
(a) ORACLE_HOME
(b) PATH
(c) ORA_NLS33
(d) ORACLE_BASE
(e) LD_LIBRARY_PATH
(f) ORACLE_PATH

xxxi. Run the upgrade script:

(a) $ cd $ORACLE_HOME/rdbms/admin
(b) Sqlplus /nolog
(c) SQL> Connect sys/passwd_for_sys as sysdba
(d) Use Startup MIGRATE when you are upgrading to Oracle 9.2:
(e) SQL> Startup Migrate
(f) Spool the output so you can take a look at possible errors after the upgrade:
(g) SQL> Spool Upgrade.log
(h) Run the appropriate script for your version.
(i) From To: Only Script to Run
(j) 8.1.7 9.2 u0801070.sql

Each of these scripts is a direct upgrade path from the version you are on to 9i. You do not need to run catalog.sql and catproc.sql as these two scripts are called from within the upgrade script.

xxxii. Display the contents of the component registry to determine which components need to be upgraded:
SQL> Select comp_name, version, status from dba_registry;

xxxiii. Run the script cmpdbmig.sql to upgrade the components which can be upgrade with the SYSDBA privilege (This step is only valid for upgrades towards 9.2):
SQL> @cmpdbmig.sql
(a) The components upgraded by this script are: Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data Mining, OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway, Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.
(b) Display the components which were upgraded:
SQL> Select comp_name, version, status from dba_registry;

End the spool of the upgrade:
SQL> Spool Off

xxxiv.Following components require manual upgrade

Upgrading Oracle Spatial --

Upgrading from Spatial Release 8.1.7
If you are upgrading from Spatial release 8.1.7 to Spatial 9i release 2 (9.2.0), and if you have not chosen the automatic upgrade option, perform the following steps to upgrade to Spatial release 9.2.0.

Note:

The following steps are not necessary if you chose the Oracle Installer option for an automatic upgrade.
If you have linear referencing system (LRS) data, you must perform the steps in Section A.5 regardless of whether or not you chose an automatic upgrade.

Make sure that the Oracle RDBMS is upgraded to release 9.2.0.

Connect to the database instance specifying AS SYSDBA.

Grant the required privileges to the MDSYS user by running the following procedure:

SQL> @$ORACLE_HOME/md/admin/mdprivs.sql

Connect as MDSYS.

Perform the migration by running the following procedure:

SQL> @$ORACLE_HOME/md/admin/c81Xu9X.sql

Upgrading Oracle interMedia -----

Upgrade instructions for Oracle interMedia can be found in ORACLE_HOME/ord/im/admin/README.txt on UNIX platforms

Upgrading Oracle Text----

If the Oracle system has Oracle Text installed, then complete the following steps:

Log in to the system as the owner of the Oracle home directory of the new release.
At a system prompt, change to the ORACLE_HOME/ctx/admin directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
If the instance is running, shut it down using SHUTDOWN IMMEDIATE:
SQL> SHUTDOWN IMMEDIATE

Start up the instance in RESTRICT mode:
SQL> STARTUP RESTRICT

You may need to use the PFILE option to specify the location of your initialization parameter file.

Set the system to spool results to a log file for later verification of success:
SQL> SPOOL text_upgrade.log

If you are upgrading from release 8.1.7, then complete the following steps. Skip to Step 9 if you are upgrading from release 9.0.1.
Run s0900010.sql:
SQL> @s0900010.sql

This script grants new, required database privileges to user CTXSYS.

Connect to the database instance as user CTXSYS.
Run u0900010.sql:
SQL> @u0900010.sql

Connect to the database instance as a user with SYSDBA privileges.
If you are upgrading from release 8.1.7 or release 9.0.1, then complete the following steps.
Run s0902000.sql:
SQL> @s0902000.sql

This script grants new, required database privileges to user CTXSYS.

Connect to the database instance as user CTXSYS.
Run u0902000.sql:
SQL> @u0902000.sql

This script upgrades the CTXSYS schema to release 9.2.

Connect to the database instance as a user with SYSDBA privileges.
Check for any invalid CTXSYS objects and alter compile as needed.
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF

Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 7; the suggested name was text_upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary.

Shut down the instance:
SQL> SHUTDOWN IMMEDIATE

Exit SQL*Plus.
Oracle Text is upgraded to the new release.

Adjust Your Parameter File for the New Release.Refer to the document 216205.1.

The AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES parameters were required to set to zero during the upgrade, but now you must set them to the recommended nonzero values.
Do not set the UNDO_% parameters yet; you will be directed to do so as part of the System Managed Undo (SMU) instructions in the next paragraph.

Follow the instructions in the "Enabling System Managed Undo (SMU)" and "Temporary Tablespace Setup" sections of the aforementioned document to convert from rollback segment usage to SMU, and to confirm that the temporary tablespace is properly defined.

xxxv. Restart the database:
SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL> Startup restrict
Executing this clean shutdown flushes all caches, clears buffers and performs
other database housekeeping tasks. Which is needed if you want to upgrade
specific components.

xxxvi. Run script to recompile invalid pl/sql modules:

SQL> @utlrp
If there are still objects which are not valid after running the script run the following:

spool invalid_post.lst
Select substr(owner,1,12) owner, Substr(object_name,1,30) object, Substr(object_type,1,30) type, status from dba_objects where status <>'VALID';
spool off

Now compare the invalid objects in the file 'invalid_post.lst' with the invalid objects in the file 'invalid_pre.lst' you create in step 5.

xxxvii. Edit init.ora file:

(a) Leave the parameter JOB_QUEUE_PROCESSES=0.
(b) Leave the parameter AQ_TM_PROCESSES=0.
(c) Remove the parameter _system_trig_enabled from the init.ora file. This parameter was explicitly set to false during the upgrade.
(d) Modify the log_archive_dest parameter specify only the path, but make sure it ends with a '/'. (remove the format).
(e) e.g. log_archive_dest=/path/arch into log_archive_dest=/path/
(f) Modify the parameter log_archive_format and add the format previously removed from the log_archive_dest.
(g) E.g log_archive_format=arch%t_SID_%s.log
(h) If you are using a password file set the REMOTE_LOGIN_PASSWORDFILE parameter to the value used before migration.
xxxviii. Shutdown the database and startup the database.
$ sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Shutdown
SQL> Startup restrict

xxxix. In Oracle9i, the National Character Set (for NCHAR, NVARCHAR2, and NCLOB datatypes) will be limited to the Unicode character sets AL16UTF16 and UTF8 only. When upgrading to Oracle9i, the National Character Set will be chosen based on the previous National Character Set. If your previous National Character Set was UTF8, then it will remain UTF8 in Oracle9i. All other National Character Sets will be converted to AL16UTF16.
To upgrade user tables with NCHAR datatype columns run the script utlnchar.sql:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> @utlnchar.sql
You can override the default upgrade selection of the National Character Set. That is, a version 8 UTF8 National Character Set can be converted to an Oracle9i AL16UTF16 National Character Set by running the script n_switch.sql:
SQL> @n_switch.sql
After running utlnchar.sql and alternatively n_switch.sql. Shut down the database cleanly.
SQL> shutdown immediate

xl. Now edit the init.ora:
(a) Put back the old value for the JOB_QUEUE_PROCESSES parameter.
(b) Put back the old value for the AQ_TM_PROCESSES parameter.
(c) If you change the value for NLS_LENGTH_SEMANTICS prior to the upgrade put the value back to CHAR.
(d) If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE.

xli. Create a server parameter file with a initialization parameter file
SQL> Create spfile from pfile;
This will create a spfile as a copy of the init.ora file located in the $ORACLE_HOME/dbs directory.
xlii. Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME.

xliii. Start the listener
$ lsnrctl
LSNRCTL> start

xliv. Enable cron and batch jobs
Change oratab entry to use automatic startup SID:ORACLE_HOME:Y
xlv. To use the new features in 9i change the compatible parameter to the new release.
When everything is well tested, update the compatible parameter in the init.ora and restart to the new release number.
COMPATIBLE=9.0.X where x is the release number

xlvi. Restart the database:
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict
----------------------------------------------------------------------------
Appendix A: Obsolete parameters in 9i:
--------------------------------------
_average_dirties_half_life
_lm_statistics
allow_partial_sn_results
always_anti_join
always_semi_join
arch_io_slaves
b_tree_bitmap_plans
backup_disk_io_slaves
cache_size_threshold
cleanup_rollback_entries
close_cached_open_cursors
compatible_no_recovery
complex_view_merging
cpu_count
db_block_checkpoint_batch
db_block_lru_extended_statisti
db_block_lru_latches
db_block_lru_statistics
db_block_max_dirty_target
db_file_simultaneous_writes
delayed_logging_block_cleanout
discrete_transactions_enabled
distributed_lock_timeout
distributed_recovery_connectio
distributed_transactions
fast_full_scan_enabled
freeze_DB_for_fast_instance_re
gc_defer_time
gc_latches
gc_lck_procs
gc_releasable_locks
gc_rollback_locks
hash_multiblock_io_count
instance_nodeset
job_queue_interval
job_queue_keep_connections
large_pool_min_alloc
lgwr_io_slaves
lm_locks
lm_procs
lm_procs
lm_ress
lock_sga_areas
log_block_checksum
log_files
log_simultaneous_copies
log_small_entry_max_size
ogms_home
ops_admin_group
ops_interconnects
optimizer_percent_parallel
optimizer_search_limit
parallel_default_max_instances
parallel_min_message_pool
parallel_server_idle_time
parallel_transaction_resource_
push_join_predicate
row_cache_cursors
sequence_cache_entries
sequence_cache_hash_buckets
shared_pool_reserved_min_alloc
snapshot_refresh_interval
snapshot_refresh_keep_connecti
snapshot_refresh_processes
sort_direct_writes
sort_multiblock_read_count
sort_read_fac
sort_spacemap_size
sort_write_buffer_size
sort_write_buffers
spin_count
temporary_table_locks
text_enable
use_ism

3.3 Shut down and restart the database instance once these changes have been made.

3.4 Perform 9.2.0.3 Post Install Actions.

Compile invalid objects using utlrp.sql or utlrcmp.sql.After completion of the post install steps, connect as SYSDBA and run the following: SQL> @?/javavm/install/jvmsec3.sql SQL> @?/javavm/install/jvmsec5.sql
Attention: When you run jvmsec3.sql and jvmsec5.sql, ignore the following failures:

ORA-29532: Java call terminated by uncaught Java exception:java.lang.SecurityException: policy table update SYS:java.lang.RuntimePermission, loadLibrary.oraawt

ORA-29532: Java call terminated by uncaught Java exception:java.lang.SecurityException: policy
table update SYS:java.lang.RuntimePermission, loadLibrary.oraioser

ORA-29532:Javacall terminated by uncaught Java exception: oracle.aurora.vm.IdNotFoundException: EJBCLIENT is not a user or role.

After going through all the necessary steps, if you find invalid JAVA CLASS or UNDEFINED objects, you may recompile them individually. Generally, Snapshots and Materialized Views are marked as UNDEFINED objects. We recommend compiling UNDEFINED objects treating them as snapshots.

Attention:After completion of the database upgrade, package body JIS817_U_PKGS in the SYSTEM schema and the materialized views BIM_R_FDSP_SUMFUND_MV and BIM_R_FDSP_SUMMARY_MV in the APPS schema may have a status of invalid. These objects are not needed and can be ignored or deleted.

3.41 Grant dictionary privileges to the SYSTEM, APPS, and MRC schemas.

On the administration server node, use SQL*Plus to connect to the database as APPS and run the $AD_TOP/patch/115/sql/adgrn9i.sql
script with the following parameters:
$ sqlplus apps/ @$AD_TOP/patch/115/sql/adgrn9i.sql

3.42 Apply Oracle Applications Object Library (FND) patch - Apply patch 2838093 to the administration server node. This fixes the package body DBMS_XMLQUERY.

3.43 Apply Oracle Contracts Core patch (conditional) - If Oracle Contracts Core (OKC) is licensed, apply patch 2652080 to the administrationserver node. This fixes the view OKC_QUE_RULES_V.

3.44 Re-create grants and synonyms - Oracle9i Release 2 (9.2.0) contains new functionality regarding grants and synonyms that Oracle8i and Oracle9i Release 1 (9.0.1) did not have. As a result, you must re-create the grants and synonyms in the APPS schema(s). On the administration server node, as the owner of the Applications file system, run AD Administration and select the "Recreate grants and synonyms for APPS schema(s)" task from the Maintain Applications Database Objects menu.

3.45 Restart Applications server processes - Restart all the Applications server processes that you shut down before upgrading the database instance. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 9.2.0 Oracle home from now on. Users may return to the system.

Monitor all ORA-errors through serverside trigger

2007-12-06T09:33:00.000-08:00

drop sequence log_errors_seq;

create sequence log_errors_seq
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
nocycle;

drop table log_errors_tab;

create table log_errors_tab (
log_date DATE,
usr VARCHAR2(30),
os_usr VARCHAR2(30),
machine VARCHAR2(64),
process VARCHAR2(9),
program VARCHAR2(64),
module varchar2(64),
err_msg VARCHAR2(2000));

create or replace trigger log_errors_trig
after servererror on database
declare
cursor c_session is
select osuser, machine, process, program,module
from v$session
where audsid = userenv('SESSIONID');
begin
for r_session in c_session loop
if r_session.machine not in('oraprddb') and r_session.module='T.O.A.D' then
insert into log_errors_tab values (sysdate, ora_login_user,
r_session.osuser, r_session.machine, r_session.process,
r_session.program,r_session.module, DBMS_UTILITY.FORMAT_ERROR_STACK);
end if;
end loop;
end log_errors_trig;
/
show errors

Auditing through Server Side Triggers

2007-12-06T09:30:00.000-08:00

Use the below procedure to let the users login to the system and to maintain audit information

CREATE TABLE logonaudittable
(
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
timestamp DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64),
module VARCHAR2(64),
program VARCHAR2(64)) tablespace AOL
/

alter table logonaudittable storage(next 1 m maxextents unlimited);

CREATE OR REPLACE TRIGGER logonauditing
after LOGON ON database
DECLARE
CURSOR c1 IS
SELECT distinct sid, serial#, osuser, machine,module,program
FROM v$session WHERE
username='APPS' and module in('SQL*Plus','SQLNav4.exe','T.O.A.D.','TOAD Freeware 8.5.0.50')
and osuser not in('applmgr','oracle','oraems','oradisco','oraias','tibco','webmethods') and audsid = USERENV( 'SESSIONID' );
BEGIN
for r1 in c1 loop
INSERT INTO logonaudittable VALUES ( 'LOGON', r1.sid, r1.serial#, sysdate,user, r1.osuser, r1.machine,r1.module,r1.program);
end loop;
END;
/

CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
CURSOR c1 IS
SELECT distinct sid, serial#, osuser, machine,module,program
FROM v$session WHERE
username='APPS' and module in('SQL*Plus','SQLNav4.exe','T.O.A.D.','TOAD Freeware 8.5.0.50')
and osuser not in('applmgr','oracle','oraems','oradisco','oraias','tibco','webmethods') and audsid = USERENV( 'SESSIONID' );
BEGIN
for r1 in c1 loop
INSERT INTO logonaudittable VALUES ( 'LOG-OFF', r1.sid, r1.serial#, sysdate,user, r1.osuser, r1.machine,r1.module,r1.program);
end loop;
END;
/

Use the below procedure to raise an error and to restrict back-end SQL access

CREATE OR REPLACE TRIGGER logonauditing
--Developed by Vijay.Cherukuri on 03/03/06 as per the KPMG sox request
AFTER LOGON ON DATABASE
DECLARE
CURSOR c1 IS
SELECT distinct STATUS, username,sid, serial#, osuser, machine,module,program,client_info
FROM v$session
--Audit all XEROX and Rapidgm client machines
WHERE (machine like'%XEROX%' or machine like'%RAPID%')
--Audit only apps schema
AND username='APPS'
--Audit only the client side os accounts. Not the server side accounts
osuser not in('appluat','orauat','oraems','oradisco','oraias','tibco','webmethods')
--Audit only the active new sessions
and status not in('KILLED')
--Audit only the SQL & Toad Sessions.Dont audit the discoverer sessions
and client_info is null;
c1_rec c1%rowtype;
l_cnt number;
BEGIN
OPEN C1;
FETCh c1 INTO c1_rec;
IF c1_rec.username='APPS'
then
RAISE_APPLICATION_ERROR(-20001, 'You are not authorized to connect to apps schema.Use APPSR schema.
If you need promote a change contact DBA through approved CRF so dba can promote the change from the server.
Please note that your failure attempt to access apps schema is reported to the management');
END IF;
CLOSE C1;
END;

OS utilities & Commands

2007-12-06T09:20:00.001-08:00

Non-Interactive ftp script.

You can use the below script or by creating a .netrc file (security issue) and with macro programming we can do the non-interactive ftp script

#! /bin/csh -f
set host='machine_name'
set user='username'
set pass='password'
ftp -ni ${host} << Eof verbose quote user ${user} quote pass ${pass} cd destdir get somefile.log quit Eof exit

Sending email from windows system using mapisend

for /f
c:\winnt\system32\findstr /in "ORA-" o:\orant\bin\test.lst >o:\orant\bin\vijay.txt
do
mapisend -u "vijayc" -p welcome -r dba@advmkt.com -s "sandman alert" -m testing pause -c

Syntax for creating and restoring tar files

Syntax to backup & restore files to the same location
tar cvf /oraspace/patches/tarfile /export/home/oratest/*.*
tar tvf /oraspace/patches/tarfile
tar xvf /oraspace/patches/tarfile

Syntax to backup & restoring files to a different file systems
cd /export/home/appltest
tar cvf /oraspace/patches/tarfile *
cd /oradata/patches
tar xvf /oraspace/patches/tarfile

Compressing a tar file

tar -cvf - *.ps compress - > booga.tar.Z

Syntax for scp or rcp.

Trasffering file to a different server scp /tmp/tarfile oracle@dev01:/u01/

Getting file from different server scp oracle@dev01:/u02/tarfile /tmp/

DR Scripts & Rsync Scripts

2007-12-06T09:15:00.000-08:00

SQL Query to Identify LOG GAP in 10g standby environment

select * from v$archive_gap;

Use the below syntax a automated shell script from crontab to synch the technology stack in the DR server

PATH=/usr/local/bin:$PATH
export PATH
rsync -a --delete -e ssh /app_form/prdappl applmgr@prodapp:/u01/app_form/
rsync -a --delete -e ssh /app_form/prdcomn applmgr@prodapp:/u01/app_form/
rsync -a --delete -e ssh /app_form/prdora applmgr@prodapp:/u01/app_form/

Use the below sql from a automated shell script from crontab to monitor the DR health hourly.

connect /as sysdba
set pages 2000
set lines 120
startup nomount
alter database mount standby database;
set echo off
set feedback off
set heading off
set pagesize 0
set termout off
spool /home/oracle/dba/scripts/dr_report.txt
select 'The latest log applied in DR is 'max(sequence#)'' from v$loghist;
select 'DR Sync Failed.The latest log applied in DR was created in PROD at 'to_char(first_time,'DD-MON-RRRR-HH24-MI')''
from v$loghist where sequence# in (select max(sequence#) from v$loghist) and first_time

Use the below sql from a automated shell script from crontab to monitor the DR health hourly.

connect /as sysdba
set pages 2000
set lines 120
startup nomount
alter database mount standby database;
set echo off
set feedback off
set heading off
set pagesize 0
set termout off
spool /home/oracle/dba/scripts/dr_report.txt
select 'The latest log applied in DR is 'max(sequence#)'' from v$loghist;
select 'DR Sync Failed.The latest log applied in DR was created in PROD at 'to_char(first_time,'DD-MON-RRRR-HH24-MI')''
from v$archived_log where sequence# in (select min(sequence#) from v$archived_log where standby_dest!='NO' and applied='NO') and first_time

Call the below sql from a automated shell script from crontab to transfer the archive files to DR server. This is required when service entry cannot be added to primary init file due to DB LISTENER PORT open restrictions.

connect /as sysdba
set pages 2000
set lines 120
set echo off
set feedback off
set heading off
set pagesize 0
set termout off
spool /home/oracle/dba/scripts/dr_synch_secure_copy.sh
select 'scp 'name' proddb:/u01/oradata/arch/' from v$archived_log where name like'/arch2%' and completion_time>sysdate-1/24;
spool off
host sh /home/oracle/dba/scripts/dr_synch_secure_copy.sh > /export/home/oracle/dba/scripts/dr_synch_secure_copy.report
host mailx -s "Secure Copy of Archive Logs from oraprddb to proddb completed" vijay@infolob.com< /home/oracle/dba/scripts/dr_synch_secure_copy.report host rm home/oracle/dba/scripts/dr_synch_secure_copy.sh exit

External Tables Example

2007-12-06T09:13:00.000-08:00

SQL> create directory vj_load_external_tables as '/u2/app/oracle/oradata/external_tables/';

Directory created.

CREATE TABLE vj_external_table_stg (USER_ID number,user_name VARCHAR2(200))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY vj_load_external_tables
ACCESS PARAMETERS (FIELDS TERMINATED BY ",")
LOCATION ('vj_external_table1.dat'));

Table created.

SQL> insert into vj_actual_table(select user_id,user_name from vj_external_table_stg);

7387 rows created.

SQL> commit;

Commit complete.

Background Process Health-Check Script

2007-12-05T09:28:00.000-08:00

. /u01/oraclep/prodappl/APPSORA.env #this script developed by vijay for prod #Monitor the client connections sqlplus apps/apps@prod < /opt/applprod/sa/logs/prod_alert1.lst EOF ERRFILE=/opt/applprod/sa/logs/prod_alert2.lst export ERRFILE strings -a /opt/applprod/sa/logs/prod_alert1.lst grep 'ORA' > $ERRFILE ERRORLINES=`cat $ERRFILE wc -l` if [ "$ERRORLINES" -gt "0" ] then echo "prod is not accepting client connections" > /opt/applprod/sa/logs/prod_alert3.lst /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_alert3.lst rm /opt/applprod/sa/logs/prod_alert1.lst rm /opt/applprod/sa/logs/prod_alert2.lst rm /opt/applprod/sa/logs/prod_alert3.lst else rm /opt/applprod/sa/logs/prod_alert1.lst rm /opt/applprod/sa/logs/prod_alert2.lst fi #Monitor the database ps -ef grep ora_pmon_prod grep oraprod > /opt/applprod/sa/logs/prod_dblog if [ $? -ne 0 ] then echo " prod Database is Down " > /opt/applprod/sa/logs/prod_dblog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_dblog rm prod_dblog fi #Monitor the db listener ps -ef grep tns grep oraprod > /opt/applprod/sa/logs/prod_listenerlog if [ $? -ne 0 ] then echo " prod DB Listener is Down " > /opt/applprod/sa/logs/prod_listenerlog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_listenerlog rm prod_listenerlog fi #Monitor the rpc listener for reports review agent ps -ef grep tns grep applprod grep -v pts > /opt/applprod/sa/logs/prod_rpclog if [ $? -ne 0 ] then echo " prod RPC listener is Down " > /opt/applprod/sa/logs/prod_rpclog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_rpclog rm prod_rpclog fi #Monitor the reports server ps -ef grep rwmts60 grep applprod grep -v pts > /opt/applprod/sa/logs/prod_reportslog if [ $? -ne 0 ] then echo " prod Web Reports Server is Down " > /opt/applprod/sa/logs/prod_reportslog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_reportslog rm prod_reportslog fi #Monitor the discoverer server ps -elf grep locator grep applprod grep -v pts > /opt/applprod/sa/logs/prod_disclog if [ $? -ne 0 ] then echo " prod Discoverer Server is Down " > /opt/applprod/sa/logs/prod_disclog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_disclog rm prod_disclog fi #Monitor the forms server rsh prodapp ps -ef grep f60srvm > /opt/applprod/sa/logs/prod_formslog if [ $? -ne 0 ] then echo " prod Forms Server is Down " > /opt/applprod/sa/logs/prod_formslog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_formslog rm prod_formslog fi #Monitor the web server rsh prodapp ps -ef grep httpd > /opt/applprod/sa/logs/prod_weblog if [ $? -ne 0 ] then echo " prod web Server is Down " > /opt/applprod/sa/logs/prod_weblog /usr/lib/sendmail -v vijay@infolob.com < /opt/applprod/sa/logs/prod_weblog rm prod_weblog fi

Rotate Alert & Other Log Files

2007-12-05T09:27:00.000-08:00

#!/bin/ksh
# makes a dated copy of the alert log and clears out the original
date=`date +"%m%d%y"`
cp /oradata02/PROD/admin/PROD/bdump/alert_PROD.log /oradata02/PROD/admin/PROD/bdump/alert_PROD.log.$date
rm /oradata02/PROD/admin/PROD/bdump/alert_PROD.log
touch /oradata02/PROD/admin/PROD/bdump/alert_PROD.log

Monitor Disk Usage

2007-12-05T09:24:00.000-08:00

#Script to check the size of /u01 and notify if greater than 90%

df -k /u01 > /home/applprod/sa/logs/u01_df.log1

tail -1 /home/applprod/sa/logs/u01_df.log1 > /home/applprod/sa/logs/u01_df.log2

awk '{print $4}' /home/applprod/sa/logs/u01_df.log2 cut -c1,2,3 > /home/applprod/sa/logs/u01_df.log3

current=`sed "s/\%//" /home/applprod/sa/logs/u01_df.log3`

max=60

if [[ $current > $max ]]

then echo "/u01 in prod is Greater than 95%" mail vijay@infolob.com

#else

#do nothing
fi

Monitor Alert Log

2007-12-05T09:20:00.000-08:00

# alert_log_check
# This will look at the database alert file
# for any messages that may be errrors since the
# last check. The errors will be mailed to a list
# of people.

#
# set environment variables, oracle home, etc..
ALERTDIR=/opt/oracle/admin/PROD/bdump
export ALERTDIR

LOGDIR=/opt//oradba/logs/PROD
export LOGDIR

ERRORFILE=$LOGDIR/errorfile
export ERRORFILE

# Test if line counter file exists, create if needed.
if [ -f $LOGDIR/$ORACLE_SID.lastline ]
then
continue
else
echo 0 > $LOGDIR/$ORACLE_SID.lastline
fi

# determine the start and end lines to check for this run
STARTLINE=`cat $LOGDIR/$ORACLE_SID.lastline`;
export STARTLINE
ENDLINE=`cat $ALERTDIR/alert_PROD.log wc -l`;
export ENDLINE

# Reset the startlines if the alert file has been edited.
if [ $STARTLINE -gt $ENDLINE ]
then
STARTLINE=0
fi

TOTLINES=`expr $ENDLINE - $STARTLINE`
echo "total lines to check is $TOTLINES"

# Read lines from the last time the program ran and
# search for any errors.
tail +$STARTLINE $ALERTDIR/alert_PROD.log grep -i -e "ORA-" -e "cannot allocate" -e "alter" -e "OER-" > $ERRORFILE

ERRORLINES=`cat $ERRORFILE wc -l`
export ERRORLINES

echo "error lines is $ERRORLINES"

# Mail the error messages to a list of userids
# if there are any errors
if [ "$ERRORLINES" -gt "0" ]
then
/usr/sbin.sendmail -v vijay@infolob.com< $ERRORFILE fi # set the new startline amount for the next run echo $ENDLINE > $LOGDIR/$ORACLE_SID.lastline

Database Heartbeat Check

2007-12-05T09:13:00.000-08:00

Schedule the below job to run every minute. This will alert the DBA if the database is not accepting client connections. . /usr/local/bin/oraenv PROD . /usr/users/oracle/.passwd#!/bin/ksh #this script developed by vijay on 12/22/03 for monitoring #proddb.proddb monitoring code starts here. sqlplus system/$SYSPWD@PROD < errfile="proddb_alert2.lst"> $ERRFILE ERRORLINES=`cat $ERRFILE wc -l` if [ "$ERRORLINES" -gt "0" ] then echo "proddb is not accepting connections" > proddb_alert3.lst /usr/lib/sendmail -v vijay@infolob.com < proddb_alert3.lst
rm proddb_alert1.lst proddb_alert2.lst proddb_alert3.lst
else
rm proddb_alert1.lst proddb_alert2.lst
fi
#proddb monitoring code completes here.

Purge Old Trace Files

2007-12-05T09:10:00.000-08:00

16 18 * * * /usr/bin/find /oradata_102/ARC1/admin/udump -mtime +14 -exec rm -f {} \;

16 18 * * * /usr/bin/find /oradata_102/ARC1/admin/bdump -mtime +14 -exec rm -f {} \;

16 18 * * * /usr/bin/find /oradata_102/ARC1/admin/cdump -mtime +14 -exec rm -f {} \;

Troubleshooting Oracle Applications

2007-11-14T13:53:00.000-08:00

What are the log and config files for Apache in release 11i ?

There are totally 4 log files where Apache write the log. They are:
1. error_log
2. access_log
3. mod_jserv.log
4. jserv.log

How do you check a release 11 OWS version 3.0 web server is up ?

ps –ef grep –i ows

How do you verify if jserv is up ?

http://host.domain:port/servlets/IsItWorking

or
http://host.domain:port/OA_HTML/jsp/fnd/aoljtest.jsp

How do you check if concurrent managers are up or not ?

ps –fu grep FNDLIBR grep –v grep or this can be also done from SYSADMIN responsibility.

If just the ICM is down how do you start it without affecting other managers ?

$FND_TOP/bin/startmgr sysmgr="APPS/APPS PASSWORD" mgrname=$TWO_TASK

How do you very if the reports server is down are up ?

ps –fu apps_owner grep rwmts60

Where do you find the discoverer related log files for discoverer troubleshooting ?

In discoverer server go to

$ORACLE_HOME/6iserver/discwb4/util/oad.log
$ORACLE_HOME/6iserver/discwb4/util/osagent.log
$ORACLE_HOME/6iserver/discwb4/util/locator.log
$ORACLE_HOME/6iserver/discwb4/util/gatekeeper.log

How do you verify the MWA server is up in Mobile Web Apps Environment ?

ps -ef grep mwa

Concurrent Managers

2007-11-14T13:39:00.000-08:00

How do you shutdown concurrent managers using CONCSUB ?

CONCSUB apps/fnd SYSADMIN 'System Administrator' sysadmin WAIT=Y CONCURRENT FND ABORT

How do you stop/start/verify MFG environment concurrent managers ?

Login to Oracle Applications.
Switch to System Administrator Responsibility.
Navigate to Administer Concurrent Manager Screen.
Concurrent->
Manager->
Administer->

Query the below managers.
1) MRP Manager.
2) INV Remote Procedure Manager.
3) Inventory Manager.
If any one of the above managers is inactive, activate them.
Make sure that Actual=Target. If actual <>
Transactions->
Interface Managers->
Make sure that status for material transaction manager is “Active”. If the status says “Inactive” Launch "Material Transaction Manager" from Inventory setup module. This will schedule a concurrent program called Process transactions interface (INCTCM) every 5 minutes. This manager (concurrent program) reads records from MTL_TRANSACTIONS_INTERFACE validates them and moves the successful transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Inventory Transaction Worker (INCTCW) which then processes these records through inventory. This process consists of data derivation, validation, and transfer of records from MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE into their respective TEMP (temporary) tables from where the transactions processor processes them. The worker rows should be 200(seeded setting), which means each time manager launches the worker, the worker processes 200 records. This seeded setting is more than adequate for current transaction volume. Leave the process interval as 5 minutes which is also a seeded setting.

Make sure that “Cost Manager” status is “Active”. If the status says “Inactive” Launch "Cost Manager" from Inventory setup module. This schedules a concurrent program called Cost Manager (CMCTCM) every 5 minutes. Cost transaction manager costs material transactions in Oracle Inventory and Oracle Work in Process in the background.

Make sure that “Move transaction Manager” status is “Active”. If the status says “Inactive” Launch "Move transaction Manager" from Inventory setup module. This will schedule a concurrent program called WIP Move Transactions Manager (WICTMS) every 5 minutes. The WIP move transaction manager processes move transactions in Oracle Work in Process and move transactions you import from devices such as portable bar code readers or your custom data entry forms using the Open Move Transaction Interface.

Login to Oracle Applications.
Switch to Material Planner Responsibility.
Navigate to Planning Manager Screen.
Setup->
Planning Manager->

If the planning manager is down start the Planning Manager. Choose the Schedule this for every 4 hours. The Planning Manager runs under the MRP Manager, which has only one process (the Planning Manager). The Planning Manager kicks off a Planning Manager worker which runs under the Standard Manager.

What are the profiles which controls the concurrent managers for MFG modules ?

Login to Oracle Applications
Navigate to System Profile Values Screen.
Profiles->
System->
Query the profile name TP: INV Transaction processing.
Make sure that value=Background Processing.
With this option, Oracle Inventory processes transactions on a periodic basis via the Inventory Transaction Manager. With this option Returns control immediately to users. This is the recommended setting.

MRP: Planning Manager Max Workers=0MRP: Planning Manager Batch Size=10000
MRP: Interface Table History Days=5
MRP: Snapshot Workers=0
MRP: Use Direct Load Option=No

How do you enable de-bugging for inventory manager ?

Login to Oracle Applications
Navigate to System Profile Values Screen.
Profiles->
System->
Query the profile name
INV:Debug Trace = Yes (Default value is No)
INV:Debug file= file_name_with_fullpath (Default value is null)
INV:Debug Level= 10 (Default value is 0)

How do I register concurrent program sql ?

Using FND_PROGRAM API

FND & Workflow Loaders

2007-11-14T13:31:00.000-08:00

(1) how to download the concurrent program definition for a specific program ???????

FNDLOAD apps/msc123 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /oraspace/patches/vjafcpprog.ldt PROGRAM
CONCURRENT_PROGRAM_NAME='MSCSC_NOTES_ORDER_HEADERS'

(2) how to download the concurrent program definitions for all the custom concurrent programs ???????

select 'FNDLOAD apps/msc123 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct PROGRAM
CONCURRENT_PROGRAM_NAME=555'CONCURRENT_PROGRAM_NAME'556' from fnd_concurrent_programs where last_updated_by not in(1);

(3) how to download the concurrent program definition for a specific application ???????

FNDLOAD apps/msc123 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /oraspace/patches/vjafcpprog.ldt
PROGRAM APPLICATION_SHORT_NAME='MSCAR'

(4) how to download the concurrent programs which are registered under all custom applications ??????????

select 'FNDLOAD apps/msc123 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /oraspace/patches/vjafcpprog.ldt PROGRAM
APPLICATION_SHORT_NAME=555'APPLICATION_SHORT_NAME'556' from fnd_APPLICATION WHERE last_updated_by not in(1);

(5) how to download the concurrent executable definition for a specific executable ???????????

FNDLOAD apps/msc123 0 Y DOWNLOAD afcpprog.lct /oraspace/patches/vjafcpprog2.ldt EXECUTABLE
EXECUTABLE_NAME='MSCSC_NOTES_ORDER_HEADERS'

Once the files were downloaded successfully we can upload the files using the following commands

FNDLOAD apps/msc123 0 Y UPLOAD afcpprog.lct /oraspace/patches/vjafcpprog.ldt
FNDLOAD apps/msc123 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct vjafcpprogmsccus.ldt

How to upload workflow definition into the database from unix ?

$FND_TOP/bin/WFLOAD apps/apps 0 Y FORCE $FA_TOP/patch/115/import/US/faflxgrp.wft

Oracle Apps SYSADMIN Activities

2007-11-14T13:06:00.000-08:00

How do I add a responsibility from sql ?

exec fnd_user_pkg.addresp('EHARRINGTON','CSI','INSTALLED_BASE_USER','STANDARD',NULL,SYSDATE,NULL);

Steps for registering a shell script as Host Based concurrent program in apps ?

step 1:
=======
Place the .prog script under the bin directory for your
applications top directory.

For example, call the script CUSTOM.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called CUSTOM.prog use this:

ln -s $FND_TOP/bin/fndcpesr CUSTOM

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
'Host'. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:

Use CUSTOM

step 4:
=======
Your script will be passed at least 4 parameters, between $1
and $4.

These are:
orauser/pwd,
userid,
username,
and request_id

Any other parameters you define will be passed in $5 and higher.
Make sure your script returns an exit status also.

Which profile can be used to turn on the trace for a self-service session ?

Initialization SQL Statement - Custom

Sytax for generating a FMB form ?

FORMS60_PATH=$AU_TOP/resource:$AU_TOP/forms/US:$AU_TOP/forms/US
export FORMS60_PATH
f60gen module=OEXOEORD.fmb userid=APPS/aesc output_file=OEXOEORD.fmx module_type=form batch=yes compile_all=special

Sytax for generating the PLL file ?

FORMS60_PATH=$AU_TOP/resource:$AU_TOP/forms/US:$AU_TOP/forms/US
export FORMS60_PATH
f60gen module=CUSTOM.pll apps/apps1598 module_type=library compile_all=y

Syntax for generating FRD trace ?

http://pythia.corp.yahoo.com:8008/dev60cgi/f60cgi?play=&record=collect&log=/tmp/form1.frd

Syntax for loading jar file into the database ?

loadjava -user apps/ -r .jar

PL/SQL AP's for loading value sets into the database ?

fnd_flex_val_api.create_valueset_table

list all site level profiles

COLUMN PROFILE_OPTION_VALUE FORMAT A70
COLUMN USER_PROFILE_OPTION_NAME FORMAT A70
SELECT
A.USER_PROFILE_OPTION_NAME,
B.PROFILE_OPTION_VALUE
FROM
FND_PROFILE_OPTIONS_VL A,
FND_PROFILE_OPTION_VALUES B
WHERE
A.PROFILE_OPTION_ID=B.PROFILE_OPTION_ID
AND
B.LEVEL_ID=10001

list All the sysadmin profiles

COLUMN PROFILE_OPTION_VALUE FORMAT A70
COLUMN USER_PROFILE_OPTION_NAME FORMAT A70
SELECT
A.USER_PROFILE_OPTION_NAME,
B.PROFILE_OPTION_VALUE
FROM
FND_PROFILE_OPTIONS_VL A,
FND_PROFILE_OPTION_VALUES B
WHERE
A.PROFILE_OPTION_ID=B.PROFILE_OPTION_ID
AND
B.LEVEL_ID=10004
AND
B.LEVEL_VALUE=0;

PL/SQL API for adding form using SQL ?

FND_FORM_PKG

Oracle Applications Printing Conscepts

2007-11-14T12:57:00.000-08:00

How do you test a initialization string from os level ?

FNDPRESC "/eE/e&l0O/e&l1E" > Test.out
cat o73151.out >> Test.out
lp -d qms_513 Test.out

How do I verify profile option values for printer profiles ?

SELECT
A.USER_PROFILE_OPTION_NAME,
c.user_name,
B.PROFILE_OPTION_VALUE
FROM
FND_PROFILE_OPTIONS_VL A,
FND_PROFILE_OPTION_VALUES B,
fnd_user c
WHERE
A.PROFILE_OPTION_ID=B.PROFILE_OPTION_ID
AND
b.profile_option_value like'print%'
and
b.level_value=c.user_id;

How does the printing works in oracle applications ?

Make sure that the following check boxes are enabled for the
concurrent program definition

Use In SRS
Enabled
Save
Print
Style Required

Define rows,columns in the program definition form.You should
know about the output-layout of your report...

For landscape

rows= 45
columns =132

For Portrait

rows=66
columns 80

Based on the rows,columns definition of a concurrent program
you can assign a print style from the LOV in the concurrent program definitions form..

Based on the style you assigned, it will check the print style definition and identify the
srw driver which was assigned in the print styles form and will check for srw driver
physical file availability in $FND_TOP/reports/

If it cannot find the physical srw driver file there, system will return the error while running the report.

If SRW.prt in this dir is not compatiable with the report request returns error.

If it is available and compatible request will complete normal...

If output is ok run the report again with re-print option with copies=1 .

If you choose copies=1 then based on the style you have choosen system will look for the associated
printer driver definition and will read the arguments..and issues the o/s command which was written in
arguments field.Along with the o/s command it will also send the initialization string to the printer
if initialization string is available.

While priting the system will use the escape codes from the srw driver file which was available
in $FND_TOP/reports and initialization string from the printer driver definition
which was asscociated with the style..

What ever the Printer you use Three common srw driver files you can use from $FND_TOP are

HPL For landscape
HPP For Portrait
HPW For Landwide

These three driver files contains the escape codes .This is escape codes were developed by HP
and also known AS Printer Command Language (PCL).Any printer can understand this language...

But some times to change font,space,margins etc we use initialization strings..These initialization
strings we can define in sysadmin printer driver definition form...

How do you setup a post-script printing ?

Configure uiprint.txt

Welcome

2007-11-14T07:22:00.000-08:00

White Papers/Case Studies

2007-11-14T07:01:00.000-08:00

MetaLink Recognition Report for August 2007

About This Report :-

This report is provided by the Oracle USA Knowledge Management Team to recognize your contribution and sharing of knowledge on MetaLink. Thank you for participating in the Customer Knowledge Exchange Program! Your contribution is highly valued by both customers, partners, and employees and is viewed by over 300,000+ total users worldwide.

Note ID
Title
Date Published
432838.1
A code snippet I use for converting around 2 million invoice records from 10.7 system to release 11i
15-MAY-07
432845.1
My experience with Oracle Streams Replication configuration with Oracle RDBMS database 9.2.0.7 and Oracle ERP 11.5.
15-MAY-07
432851.1
A query I developed extract the email center data from email center database
15-MAY-07
430136.1
How I set up a database function in E-Business environment for the code combinations mapping
15-MAY-07
432853.1
A query I use to extract all the discoverer end user report names
15-MAY-07
432846.1
My experience with Oracle Logical Standby configuration with Oracle RDBMS database 9.2.0.7 and Oracle ERP 11.5.7.
15-MAY-07
430870.1
Html Code I C! reated to Generate on-demand html Pages When Required
11-MAY-07
429902.1
How I Generate a Daily Audit report about the AOL changes
04-MAY-07
429207.1
Oracle RDBMS Backups Using Veritas Checkpoints
30-APR-07
429011.1
My Experience With Oracle Applications Forms Personalization's Feature
26-APR-07
427365.1
Case ! Study: Backup & Recovery Using NetApp
16-APR-07

Proactive Monitoring

2007-11-13T13:02:00.000-08:00

PL/SQL wrapper API to send email from database
PL/SQL API for proactive database monitoring

Script to Identify index candidates for rebuild

2007-11-13T12:26:00.000-08:00

select name as IndexName,
height,
lf_rows,
del_lf_rows
from index_stats;
Here are the results of that query:

INDEXNAME HEIGHT LF_ROWS DEL_LF_ROWS
------------------------- ---------- ---------- ---------------
CUSTOMER_LASTNAME_IND 2 5237 2130

height refers to the maximum number of levels encountered within the index.

lf_rows refers to the total number of leafs nodes in the index.
del_lf_rows refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.
In this example, del_lf_rows equals 2130, lf_rows equals 5237; the ratio is:

(2130*100) / 5237 = 40.7 percent
This index is simply "begging" for rebuilding!

Identify a blocking Session

2007-11-13T12:23:00.000-08:00

select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Trace the Optimizer Features

2007-11-13T12:22:00.000-08:00

alter session set events '10053 trace name context forever, level 1';

Trace the wait events

2007-11-13T12:21:00.000-08:00

EXEC DBMS_SYSTEM.SET_EV(29,12153,10046,12,'');

EXEC DBMS_SYSTEM.SET_EV(&&sid,&&serial,10046,0,'');

Identify the SID for oracle apps user

2007-11-13T12:20:00.001-08:00

set linesize 400
column user_name format a30
column user_form_name format a30
column responsibilty_name format a30
select c.sid,c.serial#, a.user_name,a.TIME,a.user_form_name,a.responsibility_name,b.pid
from
apps.FND_SIGNON_AUDIT_VIEW a,
v$process b,
v$session c
where
a.pid=b.pid
and
b.addr=c.paddr
and
A.USERNAME='&NAME';

Identify the front-end apps session details for a back-end process.

2007-11-13T12:19:00.000-08:00

--Identify the front-end apps session details for a back-end process.
set linesize 400
column user_name format a30
column user_form_name format a30
column responsibilty_name format a30
select user_name,TIME,user_form_name,responsibility_name from apps.FND_SIGNON_AUDIT_VIEW where pid in
(select pid from v$process where spid=&SPID;

SQL Script to Troubleshoot a long-running concurrent request

2007-11-13T12:17:00.000-08:00

set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst

Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';

Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');

prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';

prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;

prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;

prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';

prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;

prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';

prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;

prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;

prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;

prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;

spool off
clear columns
clear breaks

Oracle APPS Patches Questions

2007-11-13T12:11:00.000-08:00

How do I apply the patch in non-interactive mode for uni-driver patch ?

adpatch defaultsfile=$APPL_TOP/admin/DEV2/defaults.txt \

logfile=g2551295.log \

patchtop=/export/home/oracle/autorefresh/DEV2/2551295 \

driver=/export/home/oracle/autorefresh/DEV2/2551295/g2551295.drv \

flags=hidepw \

workers=8

How do I apply the patch in non-interactive mode for c,d,g drivers patch ?

adpatch defaultsfile=$APPL_TOP/admin/DEV2/defaults.txt \
logfile=g2551295.log \
patchtop=/export/home/oracle/autorefresh/DEV2/2551295 \
flags=hidepw \
workers=8

How do I apply the patch in no-parallel mode ?

adpatch defaultsfile=$APPL_TOP/admin/DEV2/defaults.txt \

logfile=g2551295.log \

patchtop=/export/home/oracle/autorefresh/DEV2/2551295 \

options=noparallel

flags=hidepw \workers=8

How do I skip one specific portion from the uni-driver file ?

adpatch defaultsfile=$APPL_TOP/admin/DEV2/defaults.txt \
logfile=g2551295.log \

options=nocopyportion,nocompilejsp \
patchtop=/export/home/oracle/autorefresh/DEV2/2551295 \
driver=/export/home/oracle/autorefresh/DEV2/2551295/u2551295.drv \
flags=hidepw \
workers=8

How do I merge patches ?

Using admrgpch utility

RDBMS Patches Questions

2007-11-13T12:08:00.000-08:00

How do I apply a RDBMS Patch ?

PERL5LIB=$ORACLE_HOME/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB$ORACLE_HOME/Apache/perl/bin/perl /dba-repo/bin/OPatch/opatch.pl apply /dba-repo/patch/3247926

PERL5LIB=$ORACLE_HOME/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB$ORACLE_HOME/Apache/perl/bin/perl /dba-repo/opatch/OPatch/opatch.pl lsinventory

Is OS patches are BIT specific ?

Yes. use file oracle command to verify if it is 32 or 64 then apply corresponding patch.

Is Oracle Apps patches are BIT specific ?

No oracle apps is 32 bit only.

How do I list the RDBMS patches applied ?

PERL5LIB=$ORACLE_HOME/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB$ORACLE_HOME/Apache/perl/bin/perl /dba-repo/opatch/OPatch/opatch.pl lsinventory

Release 11i Cloning Steps

2007-11-13T12:04:00.000-08:00

Section 1 – Copy Files From Source

Copy 9i Oracle Home Binaries to desired location. Change ownership to appropriate unix account. Rename appropriate directories to point to Target SID. (e.g. Rename directory: /app/proddb to /app/)
Copy database files and archive logs to desired location. Change ownership to appropriate unix account.

Copy applications tech stack to desired location. Change ownership to appropriate unix account. Rename appropriate directories to point to Target SID. (e.g. Rename directories: /app/prodappl to /app/appl). Only copy the following tech stack directories:
8.0.6 Home
APPL_TOP
IAS1022 Home
OA_HTML
OA_JAVA
Common_top/util
Common_top/clone

Note :
All the backup tar files of applications and s/w technology stack are stored in /u01/app directory tar file names are (these files are created between 00:00 to 02:30 Hours PST daily) :

- tarfiledb : For Oracle 9i binaries
- tarfileappl : For APPL_TOP files
- tarfilecomn : For COMMON_TOP files
- tarfileora : For 8.0.6 oracle binaries

Section 2 – Start Cloning Process
Grant appropriate permissions to /var/opt/oracle/oratab and /var/opt/oracle/oraInst.loc. Vi oraInst.loc and make sure orainventory directory points to correct location for Target Instance.
Login as respective oracle unix account on Target Node and go to directory: $ORACLE_HOME/appsutil/clone/bin
Execute script: perl ./adcfgclone.pl dbTechStack. Script will prompt series of questions related to the Target Instance and environment. Make sure script runs to completion with no errors.
Source environment file that was created under $ORACLE_HOME/_.env
Manually create control file and open database. Ensure database opens with no errors in alert log.
Add tempfile(s).
Execute script: perl ./adcfgclone.pl database (e.g. perl ./adcfgclone.pl database /app//9.2.0/appsutil/_.xml)
Bounce database to verify if there are any errors.
Login as respective applmgr unix account on Target Node and go to directory: $COMMON_TOP/clone/bin
Execute script: perl ./adcfgclone.pl appsTier. Script will prompt series of questions related to the Target Instance and environment. Make sure script runs to completion with no errors.
Bounce application services using start/stop scripts. Grep for respective applmgr account processes such as “FNDLIBR”, “tns”, etc.
Make sure correct environment file is sourced in $HOME/.profile for respective oracle and applmgr accounts.

Section 3 – Post Cloning Tasks

Verify SI_DBA_CLONE_API.TARGET_UPDATES was run
Archive log should be disabled
utl_file_dir values and $APPLPTMP $APPLTMP should be same
TEMP tablespace should be minimum 1GB
Use FNDCPASS to change APPS password to apps.
Use FNDCPASS to change sysadmin password to sysadmin.
Verify that all oracle application schema usernames and passwords are same.
Verify sys and system passwords were set to same as listed in /usr/local/bin/passwd
Verify that Backups are enabled
Verify /var/opt/oraenv is updated with the list of instance names
Verify PHP,CRM, CGI, discoverer, logins are working fine.
Verify Concurrrent request are getting completed successfully.
Verify SYSADMIN password is set to sysadmin.
Modify wdbsvr.app , change apps password in this file.
Change no. of concurrent process
Inv manager standard manager should be reset to 4 process
Shipping queues should be reset to 4 process
RCVTM should be reset to 2 process
Disable Alerts
CX:Session Timeout to NULL (Field should not have any value)
Disable password profiles
Complete SSO integration steps to point to DEV OID database
Re-Create ASCP Partitions in corresponding ASCP database
Re-Create database links
Disable pending workflow emails
Re-configure workflow mailer to point to DEV exchange account
Disable all scheduled jobs
Schedule the required jobs
Clean up workflow and concurrent request tables
Verify that required responsibilities (full access) is granted to the appropriate IT team members
Complete health-check using aoljtest.jsp
Verify if graphical reports are working
Test a workflow email, RRA
Completed discoverer post install steps
Complete email center post install steps such as iemcpcfg.sql
Update the team-site URL with the newly refreshed date
Send an email to IT Team about the completion of the refresh

PL/SQL API for Proactive Database Monitoring

2007-11-13T11:40:00.000-08:00

CREATE OR REPLACE
PACKAGE XXL_DBA_MONITORING_API
--Created by vijay to monitor the production databases at Infolob
AS
--Following Procedure will alert the dba's every 30 minutes about the concurrent programs which are failed in the last 30 mins.
PROCEDURE XXL_30MINS_ERRCONALERT_PROC;
--Following Procedure will alert the dba's every 30 minutes about the concurrent programs which are running for more than 30 mins.
PROCEDURE XXL_30MINS_RUNCONCALERT_PROC;
--Following Procedure will alert the dba's every 1 hour,If the tablespace is 90% Full.
PROCEDURE XXL_30mins_TBLSPC_PROC;
--Following Procedure will alert the dba's every 30 minutes,If the next extent can't fit into tablespace.
PROCEDURE XXL_30MINS_EXTCHECK_PROC;
--Following Procedure will alert the dba's every 10 minutes if there is a blocking lock exist in the database for more than 30 mins.
--Following Procedure will alert the dba's every 30 minutes if the extents are reaching the max limit.
PROCEDURE XXL_30MINS_MAXEXTCHECK_PROC;
PROCEDURE XXL_10MINS_LOCKCHECK_PROC;
--Following Procedure will alert the dba's every 10 minutes about the concurrent programs which are pending for more than 10 mins.
PROCEDURE XXL_10MINS_PENCONCALERT_PROC;
--Following Procedure will alert the dba's every 1 hour,If the redo generation in the past one hour exceeds 500 mb.
PROCEDURE XXL_1Hour_REDOGEN_PROC;
--Following Procedure Will alert the managers if any one make the changes in the "Define User Form" as sysadmin
PROCEDURE XXL_HOURLY_SYSADMIN_USAGE_PROC;
--Following Procedure Will update tne password expiration to 90 days where it is not equal to 90 days
PROCEDURE XXL_HOURLY_PASSWORD_CHECK_PROC ;
--Following Procedure Will alert the managers about the failed login attempts.The site level profile is not working in 11.5.9
PROCEDURE XXL_HOURLY_FAILED_LOGINS_PROC;
--Following Procedure Will populate the database growth details table every day
PROCEDURE XXL_DAILY_SPACE_MONITOR_PROC;
--Following Procedure Will alert the dba's if there are 10 workflow mails are pending in the que in the last one hour.
PROCEDURE XXL_HOURLY_WORKFLOW_MONITOR;
--Following Procedure Will alert the dba's every minute if the time in oraprddb is different from apsun02
PROCEDURE XXL_1min_timecheck_alert;
--Following will monitor the PROD for max sessions
PROCEDURE XXL_PRODMONITOR_PROC;
--Following procedure will monitor the Remedy Email Engine
PROCEDURE XXL_10min_remedyemail_check;

END XXL_DBA_MONITORING_API;
/

-- End of DDL script for XXL_DBA_MONITORING_API

-- Start of DDL script for XXL_DBA_MONITORING_API
-- Generated 13-Jun-05 11:46:11 am
-- from prod-SYS:1

-- Package body XXL_DBA_MONITORING_API

CREATE OR REPLACE
PACKAGE BODY XXL_DBA_MONITORING_API
--Created by vijay to monitor the production databases at Infolob
AS
PROCEDURE XXL_30MINS_ERRCONALERT_PROC
IS

CURSOR one IS
SELECT
concurrent_program_id,
REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH24:MI:SS') strttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH24:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime_in_minutes,
PROGRAM,
REQUESTOR
FROM apps.fnd_conc_req_summary_v
WHERE
status_code='E' AND PROGRAM not like'%Pick%'
AND
ACTUAL_START_DATE > sysdate-1/24/2 order by 5 desc;
BEGIN

FOR C1 IN ONE LOOP
IF C1.REQUEST_ID IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com,vijay@infolob.com','Alert From DBA***Some Concurrent Programs Failed In The Past 30 minutes',
'Concurrent program 'c1.program' Submitted by 'c1.requestor' failed at 'c1.endtime'
Request_id='c1.request_id'');
end;
END IF;
END LOOP;
END XXL_30MINS_ERRCONALERT_PROC;
PROCEDURE XXL_30MINS_RUNCONCALERT_PROC
IS

CURSOR one IS
SELECT
REQUEST_ID,
requestor,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH24:MI:SS') strttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH24:MI:SS') endtime,
ROUND((SYSDATE - ACTUAL_START_DATE)*(60*24),2) rtime,
SUBSTR(completion_text,1,20) compl_txt,
PROGRAM
FROM apps.fnd_conc_req_summary_v
WHERE status_code='R'
AND
TO_NUMBER(TO_CHAR(SYSDATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) > 1800;

BEGIN
FOR C1 IN ONE LOOP
IF C1.REQUEST_ID IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com,vijay@infolob.com','Alert From DBA***Some Jobs Are Taking More Than 30 Minutes to Complete',
'Concurrent program 'c1.program' Submitted by 'c1.requestor' running from last 'c1.rtime' Seconds
Request_id='c1.request_id'');
END;
END IF;
END LOOP;
END XXL_30MINS_RUNCONCALERT_PROC;

PROCEDURE XXL_PRODMONITOR_PROC
IS
cursor one is
SELECT
count(*) sescount from v$session@prod;

BEGIN
FOR C1 IN ONE LOOP
IF C1.sescount>270 THEN
BEGIN
XXL_SEND_MAIL('2484705806@messaging.sprintpcs.com,'Alert From DBA***PROD max sessions threshold reached.Only 30 sessions left',
'Please inform Vijay');
END;
END IF;
END LOOP;
END XXL_PRODMONITOR_PROC;

PROCEDURE XXL_30MINS_EXTCHECK_PROC
IS

CURSOR one IS
select /* + RULE */ s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s where tablespace_name in(select tablespace_name from XXL_dba_mon_growth where size_in_mb>100
and to_char(monitor_date,'DD-MON-RRRR')='14-MAR-2005')
and s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);
BEGIN
FOR C1 IN ONE LOOP
IF c1.segment_name IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Space Issue.Next Extent Cant Fit',
'Object 'c1.segment_name' with Object_type 'c1.segment_type' cant fit into next extent in tablespace 'c1.tablespace_name'');
END;
END IF;
END LOOP;
END XXL_30MINS_EXTCHECK_PROC;

PROCEDURE XXL_30MINS_MAXEXTCHECK_PROC
IS

CURSOR one IS
select segment_name,max_extents,extents,max_extents-extents left from dba_segments
where
max_extents-extents<10>50;

BEGIN
FOR C1 IN ONE LOOP
IF c1.segment_name IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Space Issue.Max Extents Are Reaching','
'c1.segment_name' has only 'c1.left' extents left');
END;
END IF;
END LOOP;
END XXL_30MINS_MAXEXTCHECK_PROC;

PROCEDURE XXL_30mins_TBLSPC_PROC
IS
CURSOR one IS
select /* + RULE */ a.tablespace_name,
to_char((free_space/total_space)*100,'999.99') free_pct
from
(select tablespace_name, sum(bytes)/1024/1024 total_space
from dba_data_files where tablespace_name in(select tablespace_name from XXL_dba_mon_growth where size_in_mb>100
and to_char(monitor_date,'DD-MON-RRRR')='14-MAR-2005')
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 free_space
from dba_free_space where tablespace_name in(select tablespace_name from XXL_dba_mon_growth where size_in_mb>100
and to_char(monitor_date,'DD-MON-RRRR')='14-MAR-2005')
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
and to_char((free_space/total_space)*100,'999.99') < wait_time =" 0" event =" 'enqueue'" ksqrsid1 =" w.p2" ksqrsid2 =" w.p3" ksqrsidt =" chr(bitand(p1,-16777216)/16777215)" block =" 1" saddr =" s.addr" raddr =" r.addr" inst_id =" userenv('Instance')">600;
BEGIN
FOR C1 IN ONE LOOP
IF c1.waiting_session IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Some Users Are Blocking Others',
'Session 'c1.waiting_session' is waiting on session 'c1.holding_session' for lock 'c1.lock_type' and mode 'c1.mode_held'
This lock was held from last 'C1.seconds_in_wait' seconds ');
END;
END IF;
END LOOP;
END XXL_10MINS_LOCKCHECK_PROC;
PROCEDURE XXL_10MINS_PENCONCALERT_PROC
IS

CURSOR one IS
SELECT
REQUEST_ID,
TO_CHAR(REQUESTED_START_DATE,'MM/DD/YY HH24:MI:SS') SCH_TO_START_AT,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH24:MI:SS') strttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH24:MI:SS') endtime,
ROUND((SYSDATE - REQUESTED_START_DATE)*(60*24),2) rtime,
SUBSTR(completion_text,1,20) compl_txt,
TO_NUMBER(TO_CHAR(SYSDATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(REQUESTED_START_DATE,'JSSSSS')) ptime,
PROGRAM,
REQUESTOR
FROM apps.fnd_conc_req_summary_v
WHERE
TO_NUMBER(TO_CHAR(SYSDATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(REQUESTED_START_DATE,'JSSSSS')) > 600
AND
PHASE_CODE='P';
BEGIN
FOR C1 IN ONE LOOP
IF C1.REQUEST_ID IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Some Concurrent Jobs are pending in the queue',
'Concurrent program 'c1.program' Submitted by 'c1.requestor' in pending in the queue for the last 'c1.ptime' minutes
Request_id='c1.request_id'');
END;
END IF;
END LOOP;
END XXL_10MINS_PENCONCALERT_PROC;
PROCEDURE XXL_1Hour_REDOGEN_PROC
IS
CURSOR one IS

select size_in_mb from
(SELECT /* + RULE */
count(sequence#)*50 size_in_mb from v$loghist where first_time>sysdate-1/24) where size_in_mb>500;

BEGIN
FOR C1 IN ONE LOOP
IF C1.size_in_mb>500 THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Lot of archivelogs are generating in the database',
'Total 'c1.size_in_mb' Megabytes of archive logs Generated in the past 1 hour ');
END;

END IF;
END LOOP;
END XXL_1Hour_REDOGEN_PROC;

PROCEDURE XXL_HOURLY_SYSADMIN_USAGE_PROC
IS
CURSOR one IS
select CREATED_BY,LAST_UPDATED_BY,last_update_date FROM apps.FND_USER WHERE (CREATED_BY=0 OR LAST_UPDATED_BY=0) AND
(CREATION_DATE>SYSDATE-1/24 OR LAST_UPDATE_DATE>SYSDATE-1/24)
UNION
select CREATED_BY,LAST_UPDATED_BY,last_update_date FROM apps.FND_USER_RESP_GROUPS WHERE (CREATED_BY=0 OR LAST_UPDATED_BY=0) AND
(CREATION_DATE>SYSDATE-1/24 OR LAST_UPDATE_DATE>SYSDATE-1/24);
BEGIN
FOR C1 IN ONE LOOP
IF c1.CREATED_BY IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Sysadmin Security Message',
'Some one logged in as sysadmin make the changes to the user records in the define user form at 'c1.last_update_date' ');
END;
END IF;
END LOOP;
END XXL_HOURLY_SYSADMIN_USAGE_PROC;

PROCEDURE XXL_HOURLY_PASSWORD_CHECK_PROC
IS
CURSOR one IS
SELECT USER_NAME,PASSWORD_LIFESPAN_DAYS FROM apps.FND_USER
WHERE (END_DATE IS NULL OR END_DATE>SYSDATE)
AND
(PASSWORD_LIFESPAN_DAYS!=90 OR PASSWORD_LIFESPAN_DAYS IS NULL)
AND USER_NAME NOT IN('GUEST','SYSADMIN','AUTOINSTALL') ;

BEGIN
FOR C1 IN ONE LOOP
IF c1.USER_NAME IS NOT NULL THEN
UPDATE apps.FND_USER SET PASSWORD_LIFESPAN_DAYS=90 WHERE USER_NAME=C1.USER_NAME;
END IF;
END LOOP;
END XXL_HOURLY_PASSWORD_CHECK_PROC;

PROCEDURE XXL_HOURLY_FAILED_LOGINS_PROC
IS
cursor one is SELECT u.user_name,l.attempt_time
from
apps.fnd_unsuccessful_logins l,
apps.fnd_user u
where l.user_id = u.user_id
and
(attempt_time + (10/1440)) > SYSDATE
and (select count(*) from apps.fnd_unsuccessful_logins l1, apps.fnd_user u1 where l1.user_id=u1.USER_ID and u1.user_id = u.user_id) >=3;
BEGIN
FOR C1 IN ONE LOOP
IF c1.user_name IS NOT NULL THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Failed Login Attempt Message',
'Some one tried to login as 'c1.user_name' at 'c1.attempt_time' ');
END;
END IF;
END LOOP;
END XXL_HOURLY_FAILED_LOGINS_PROC;

PROCEDURE XXL_DAILY_SPACE_MONITOR_PROC
IS
BEGIN
insert into /*+ append parallel (n,4) */ XXL_dba_mon_growth(select /* + RULE */ /*+ parallel (x,4) */ sum(bytes)/(1024*1024),tablespace_name,sysdate from dba_extents group by tablespace_name);
commit;
END XXL_DAILY_SPACE_MONITOR_PROC;

PROCEDURE XXL_HOURLY_WORKFLOW_MONITOR
IS
cursor one is SELECT COUNT(*) COUNT from APPS.wf_notifications where mail_status='MAIL'
and begin_date>sysdate-1/24;

BEGIN
FOR C1 IN ONE LOOP
IF c1.COUNT>10 THEN
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Workflow Mailer Issues',
'There are 'c1.count' workflow mails are pending in the queue');
END;
END IF;
END LOOP;
END XXL_HOURLY_WORKFLOW_MONITOR;

PROCEDURE XXL_1min_timecheck_alert
IS
cursor one is select to_char(sysdate,'DD-MON-RRRR-HH24-MI') TIME FROM dual@WEBAPP_FCST
MINUS
select to_char(sysdate,'DD-MON-RRRR-HH24-MI') TIME FROM dual;

BEGIN
FOR C1 IN ONE LOOP
IF c1.time is not null then
BEGIN
XXL_SEND_MAIL('vijay@infolob.com','Alert From DBA***Production db-server sysdate is wrong',
'Production db-server sysdate is wrong and matching to apsun02 sysdate');
END;
END IF;
END LOOP;
END XXL_1min_timecheck_alert;

PROCEDURE XXL_10min_remedyemail_check
IS
cursor one is SELECT A.EMAIL_ID INCOMING_EMAILID,B.EMAIL_ID OUTGOING_EMAILID FROM
(SELECT MAX(EMAIL_ID) EMAIL_ID FROM ARADMIN.AR_SYSTEM_EMAIL_MESSAGES@prod WHERE MESSAGE_TYPE=0 and date_received is not null) A,
(SELECT MAX(EMAIL_ID) EMAIL_ID FROM ARADMIN.AR_SYSTEM_EMAIL_MESSAGES@prod WHERE MESSAGE_TYPE=1 and date_sent is not null) B;

BEGIN
FOR C1 IN ONE LOOP
IF C1.INCOMING_EMAILID-C1.OUTGOING_EMAILID>10 THEN
BEGIN
XXL_SEND_MAIL('2484705806@messaging.sprintpcs.com','Remedy Email Engine is Down',
'Remedy Email Engine is Down.Contact Vijay to start this ASAP');
END;
END IF;
END LOOP;
END XXL_10min_remedyemail_check;

END XXL_DBA_MONITORING_API;
/

-- End of DDL script for XXL_DBA_MONITORING_API

PL/SQL Wrapper For Sending Emails from Database

2007-11-13T11:36:00.000-08:00

CREATE OR REPLACE PROCEDURE XXL_SEND_MAIL
(recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'mail.INFOLOB.com';
mail_conn utl_smtp.connection;
sender VARCHAR2(50);
multi_recipt VARCHAR2(2000);
t_recipt VARCHAR2(2000);
mesg VARCHAR2( 4000 );
crlf VARCHAR2( 2 ):= CHR( 13 ) CHR( 10 );
today Date := sysdate;

BEGIN
/*
* The schema name is the sender of the mail. For e.g. if the mail is send
* using database account
*/
SELECT 'vijay@infolob.com' INTO sender FROM DUAL;
/*
* Open a Connection to an SMTP(Mail) Server.
*/
mail_conn := utl_smtp.open_connection('mail.infolob.com, 25);
/*
* Perform initial handshaking with SMTP server after connecting.
*/
utl_smtp.helo(mail_conn, mailhost);
/*
* Initiate a mail transaction with the server. The destination is a mailbox.
*/
utl_smtp.mail(mail_conn, sender);
/*
* Specify the recipent of an email message.
* To send a message to multiple recipients, call this routine mutiple times.
*/
multi_recipt := recipient;
dbms_output.put_line(TO_CHAR(INSTR(multi_recipt,',')) );
/*
* Loop only in case there are multiple recipients .
*/
WHILE (INSTR(multi_recipt,',') > 0 )
LOOP
/*
* Extract the first recipient from the list.
*/
t_recipt := SUBSTR(multi_recipt,1,INSTR(multi_recipt,',')-1);
/*
* Specify the recipient of an email message.
*/
utl_smtp.rcpt(mail_conn,t_recipt);

dbms_output.put_line(t_recipt);

/*
* Delete the first recipient from the list.
*/
IF (INSTR(SUBSTR(multi_recipt,LENGTH(t_recipt) + 2,LENGTH(multi_recipt) - LENGTH(t_recipt)+2),',') > 0 ) THEN
multi_recipt := SUBSTR(multi_recipt,LENGTH(t_recipt)+2,LENGTH(multi_recipt) - LENGTH(t_recipt)+2);
ELSE
multi_recipt := SUBSTR(multi_recipt,LENGTH(t_recipt)+2,LENGTH(multi_recipt) - LENGTH(t_recipt)+1);
END IF;
END LOOP;

dbms_output.put_line(multi_recipt);
/*
* Specify the recipient of an email message.
*/
utl_smtp.rcpt(mail_conn, multi_recipt);
/*
* Specify the body of an email message.
*/

mesg:= 'Date: 'to_char(today+5/24,' dd mon yy hh24:mi:ss') crlf
'From:'' <'Sender'>' crlf
'Subject: 'subject crlf
'To: 'recipient crlf
'' crlf;

/* mesg:= 'Date: 'to_char(today+5/24,' dd mon yy hh24:mi:ss') crlf
'From:' '"Infolob" <'Sender'>' crlf
'Subject: 'subject crlf
'To: 'recipient crlf
'' crlf; */

mesg := mesgmessage;

utl_smtp.data( mail_conn, mesg );

/*
* Terminate an SMTP session and disconnect from the server
*/
utl_smtp.quit(mail_conn);
EXCEPTION
/*
* Raise error
*/
WHEN OTHERS THEN
dbms_output.put_line('ERror is ' TO_CHAR(SQLCODE) SQLERRM);
RAISE_APPLICATION_ERROR (-20001,'Can NOT send Mail.');
END;
/

Sample Email Format System Outage Announcement

2007-11-13T11:35:00.000-08:00

As we are performing system maintenance this weekend (Saturday, May 28 – Monday, May 30), PROD instance will be unavailable during this period.

We regret any inconvenience this may cause.

Thank you

Vijay.Cherukuri

PSP PL/SQL API for Customizations Tracking

2007-11-13T11:34:00.001-08:00

CREATE OR REPLACE
PROCEDURE SI_dba_customizations_mon_api IS
cursor one is
select 'Custom Concurrent Programs Defined Under 'b.application_name' = 'count(*)' ' mesg from
(SELECT application_id
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE
APPLICATION_ID>20000
union all
SELECT application_id
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE
(APPLICATION_ID<20000>7)) a,
fnd_application_vl b
where
a.application_id=b.application_id
group by b.application_name
union
select 'Custom Concurrent Programs with execution method 'execution_method_code' = 'count(*)' ' mesg from(
SELECT DECODE(EXECUTION_METHOD_CODE,'Q','SQLPLUS','P','Oracle Reports','I','PL/SQL Stored Procedure','L','SQLLOADER','H','HOST','A','SPAWNED','J','JAVA','R','SQLREPORT','X','Flexrpt','S','IMMEDIATE') EXECUTION_METHOD_CODE
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE
APPLICATION_ID>20000
union all
SELECT DECODE(EXECUTION_METHOD_CODE,'Q','SQLPLUS','P','Oracle Reports','I','PL/SQL Stored Procedure','L','SQLLOADER','H','HOST','A','SPAWNED','J','JAVA','R','SQLREPORT','X','Flexrpt','S','IMMEDIATE')
EXECUTION_METHOD_CODE
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE
(APPLICATION_ID<20000>7)) group by execution_method_code
union all
select 'Custom Forms Defined = 'count(*)' ' mesg
from
FND_FORM_FUNCTIONS_VL a,
fnd_form_vl b
where
a.created_by>7
and
a.form_id not in(51614, 20589)
and
a.form_id=b.form_id
union all
select 'Custom Database Objects With Object Type 'object_type' = 'count(*)' ' mesg from dba_objects where object_name like'SI%' GROUP BY OBJECT_TYPE;

cursor two is
select
a.user_function_name form_name,
b.form_name form_os_file_name,
c.basepath directory,
c.application_name
from
FND_FORM_FUNCTIONS_VL a,
fnd_form_vl b,
fnd_application_vl c
where
a.created_by>7
and
a.form_id not in(51614, 20589)
and
a.form_id=b.form_id
and
c.application_id=b.application_id order by basepath;

cursor three is
select object_NAME,OBJECT_TYPE,OWNER from dba_objects where object_name like'SI%' ORDER BY OBJECT_TYPE;

cursor four is
select a.user_concurrent_program_name,b.application_name,c.user_executable_name filename,
DECODE(c.EXECUTION_METHOD_CODE,'Q','SQLPLUS','P','Oracle Reports','I','PL/SQL Stored Procedure','L','SQLLOADER','H','HOST','A','SPAWNED','J','JAVA','R','SQLREPORT','X','Flexrpt','S','IMMEDIATE') type_of_program,
b.basepath directory_name
from
(SELECT USER_CONCURRENT_PROGRAM_NAME,executable_id,application_id
FROM
FND_CONCURRENT_PROGRAMS_VL
WHERE
APPLICATION_ID>20000
union all
SELECT USER_CONCURRENT_PROGRAM_NAME,executable_id,application_id
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE
(APPLICATION_ID<20000>7)) a,
fnd_application_vl b,
fnd_executables_vl c
where
a.application_id=b.application_id
and
a.executable_id=c.executable_id;

BEGIN
HTP.htmlopen;
HTP.headopen;
HTP.title ('Q-Logic ERP Customizations Report');
HTP.headclose;

HTP.bodyopen;
HTP.P(' ');
HTP.P(' ');
HTP.P('

Customizations Summary Break-Down

');
HTP.tableopen ('border=1 cellpadding=3 width="100%"');
HTP.tableheader ('Customizations Count');
FOR ones in one
LOOP
HTP.tablerowopen;
HTP.tabledata (ones.mesg, 'Left');
HTP.tablerowclose;
END LOOP;
HTP.tableclose;
HTP.P('

');
HTP.bodyopen;
HTP.P(' ');
HTP.P(' ');
HTP.P('

Detailed List of Custom Forms

');
HTP.tableopen ('border=1 cellpadding=3 width="100%"'); HTP.tableheader ('FORM NAME ');
HTP.tableheader ('FILE NAME');
HTP.tableheader ('DIRECTORY NAME');
HTP.tableheader ('APPLICATION NAME');

FOR twos in two
LOOP
HTP.tablerowopen;
HTP.tabledata (twos.form_name, 'Left');
HTP.tabledata (twos.form_os_file_name, 'Left'); HTP.tabledata (twos.directory, 'Left');
HTP.tabledata (twos.application_name, 'Left');
HTP.tablerowclose;
END LOOP;
HTP.tableclose;
HTP.P('

');

HTP.bodyopen;
HTP.P(' ');
HTP.P(' ');
HTP.P('

Detailed List of Custom Database Objects

');
HTP.tableopen ('border=1 cellpadding=3 width="100%"'); HTP.tableheader ('OBJECT NAME ');
HTP.tableheader ('OBJECT TYPE');
HTP.tableheader ('OWNER');
FOR threes in three
LOOP
HTP.tablerowopen;
HTP.tabledata (threes.object_name, 'Left');
HTP.tabledata (threes.object_type, 'Left'); HTP.tabledata (threes.owner, 'Left');
HTP.tablerowclose;
END LOOP;
HTP.tableclose;
HTP.P('

');

HTP.bodyopen;
HTP.P(' ');
HTP.P(' ');
HTP.P('

Detailed List of Custom Concurrent Programs

');
HTP.tableopen ('border=1 cellpadding=3 width="100%"'); HTP.tableheader ('CONCURRENT PROGRAM NAME ');
HTP.tableheader ('APPLICATION NAME');
HTP.tableheader ('FILENAME');
HTP.tableheader ('DIRECTORY');

FOR fours in four
LOOP
HTP.tablerowopen;
HTP.tabledata (fours.USER_CONCURRENT_PROGRAM_NAME, 'CONCURRET PROGRAM NAME');
HTP.tabledata (fours.APPLICATION_NAME, 'Left'); HTP.tabledata (fours.FILENAME, 'Left');
HTP.tabledata (fours.type_of_program, 'Left');
HTP.tablerowclose;
END LOOP;
HTP.tableclose;
HTP.P('

');

HTP.bodyclose;
HTP.htmlclose;
HTP.bodyclose;
HTP.htmlclose;
EXCEPTION
WHEN OTHERS
THEN
HTP.p ('ERROR - Please contact VIJAY CHERUKURI or
the Q-Logic IT Helpdesk - 8*000-9111');
HTP.bodyclose;
HTP.htmlclose;
END SI_dba_customizations_mon_api;
/

-- End of DDL script for SI_DBA_CUSTOMIZATIONS_MON_API

PL/SQL API for Post-Clone Database Updates

2007-11-13T11:20:00.000-08:00

CREATE OR REPLACE PACKAGE SI_DBA_CLONE_API
--Developed By Vijay.Cherukuri on 02/28/2005 to simplify the post cloning steps at Infolob
AS
PROCEDURE SI_DBA_PSTCLONE_TARGET_UPDATES(p_target_node in fnd_profile_option_values.profile_option_value%type,
p_target_port in fnd_profile_option_values.profile_option_value%type,
p_target_sid in fnd_profile_option_values.profile_option_value%type,
p_target_osuser in fnd_svc_comp_param_vals.parameter_value%type);
PROCEDURE SI_DBA_PSTCLONE_TARGET_CLEAN;
END SI_DBA_CLONE_API;
/

CREATE OR REPLACE PACKAGE BODY SI_DBA_CLONE_API
--Developed By Vijay.Cherukuri on 02/28/2005 to simplify the post cloning steps at Infolob
AS
PROCEDURE SI_DBA_PSTCLONE_TARGET_UPDATES(p_target_node in fnd_profile_option_values.profile_option_value%type,
p_target_port in fnd_profile_option_values.profile_option_value%type,
p_target_sid in fnd_profile_option_values.profile_option_value%type,
p_target_osuser in fnd_svc_comp_param_vals.parameter_value%type)
IS
CURSOR profiles is
select replace(replace(replace(profile_option_value, 'oraprdapp',p_target_node),'8080',p_target_port),'PROD',p_target_sid) profile_option_value,profile_option_id
from fnd_profile_option_values WHERE PROFILE_OPTION_ID
in(select profile_option_id from fnd_profile_option_values where profile_option_value like'%http%' or profile_option_value like'%PROD%'
OR profile_option_value like'%infolob%');
--CURSOR workflow1 is
--select replace(replace(replace(text_value,'oraprdapp',p_target_node),'8080',p_target_port),'PROD',p_target_sid) wf_text,text_value
--from wf_item_attribute_values where text_value like'%oraprddb%';
--CURSOR workflow2 is
--select replace(replace(replace(text_value,'oraprdapp',p_target_node),'8080',p_target_port),'PROD',p_target_sid) wf_text,text_value
--from wf_notification_attributes where text_value like'%oraprddb%';
CURSOR workflow3 is
select replace(replace(parameter_value,'oraprdapp','p_target_node'),'applmgr',p_target_osuser) parameter_value,component_parameter_id
from fnd_svc_comp_param_vals where component_id=10006 and parameter_value
like'%oraprdapp%' or parameter_value like'%applmgr%';
cursor site_name is
select ''name' Instance Cloned from PROD backup on 'created'' mesg from v$database;

BEGIN
for for_profiles in profiles LOOP
update fnd_profile_option_values set profile_option_value=for_profiles.profile_option_value where profile_option_id=for_profiles.profile_option_id;
END LOOP;
--for for_workflow in workflow1 LOOP
--update wf_item_attribute_values set text_value=for_workflow.wf_text where text_value=for_workflow.text_value;
--END LOOP;
--for for_workflow in workflow2 LOOP
--update wf_notification_attributes set text_value=for_workflow.wf_text where text_value=for_workflow.text_value;
--END LOOP;
for for_workflow in workflow3 LOOP
update fnd_svc_comp_param_vals set parameter_value=for_workflow.parameter_value where component_parameter_id=for_workflow.component_parameter_id;
END LOOP;
update wf_systems set name=p_target_sid where name='PROD';
update wf_systems set guid=p_target_sid where name='PROD';
update wf_notifications set mail_status='SENT' where mail_status is not null;
--update wf_roles set mailto:email_address= where name='SYSADMIN';
update fnd_user set employee_id=null where user_name='SYSADMIN';
update wf_resources set text='VCHERUKURI' where name='WF_ADMIN_ROLE';
update fnd_user_resp_groups set end_date=null where user_id in(select user_id from fnd_user where user_name in('VCHERUKURI'));
for site_name_for in site_name LOOP
update fnd_profile_option_values set profile_option_value=site_name_for.mesg where profile_option_id=125;
end loop;
END SI_DBA_PSTCLONE_TARGET_UPDATES;

PROCEDURE SI_DBA_PSTCLONE_TARGET_CLEAN IS
BEGIN
--delete from fnd_concurrent_requests where phase_code not in('C');
--execute immediate 'TRUNCATE TABLE APPLSYS.fnd_concurrent_processes';
--execute immediate 'TRUNCATE TABLE APPLSYS.FND_LOGINS';
--execute immediate 'TRUNCATE TABLE APPLSYS.FND_LOGIN_RESPONSIBILITIES';
--execute immediate 'TRUNCATE TABLE APPLSYS.FND_LOGIN_RESP_FORMS';
--execute immediate 'TRUNCATE TABLE APPLSYS.FND_UNSUCCESSFUL_LOGINS';
--execute immediate 'TRUNCATE TABLE APPLSYS.FND_ENV_CONTEXT';

Delete From fnd_Concurrent_Queue_Size
where concurrent_queue_id in
(Select concurrent_queue_id
from fnd_concurrent_queues
where manager_type in (2,6));

Delete from fnd_concurrent_queue_size
where concurrent_queue_id in
(select concurrent_queue_id
from fnd_concurrent_queues
where manager_type in
( select service_id
from fnd_cp_services
where upper(service_handle) in
('FORMSL', 'FORMSMS', 'FORMSMC',
'REPSERV', 'TCF', 'APACHE',
'JSERV', 'OAMGCS')));
Delete From fnd_Concurrent_Queues_tl
where concurrent_queue_id in
(Select concurrent_queue_id
from fnd_concurrent_queues
where manager_type in (2,6));

Delete from fnd_concurrent_queues_tl
where concurrent_queue_id in
(select concurrent_queue_id
from fnd_concurrent_queues
where manager_type in
(select service_id
from fnd_cp_services
where upper(service_handle) in
('FORMSL', 'FORMSMS', 'FORMSMC',
'REPSERV', 'TCF', 'APACHE',
'JSERV', 'OAMGCS')));

Delete from fnd_concurrent_queues
where manager_type in (2,6);
Delete from fnd_concurrent_queues
where manager_type in
(select service_id
from fnd_cp_services
where upper(service_handle) in
('FORMSL', 'FORMSMS', 'FORMSMC',
'REPSERV', 'TCF', 'APACHE',
'JSERV', 'OAMGCS'));
END SI_DBA_PSTCLONE_TARGET_CLEAN;
END SI_DBA_CLONE_API;
/

Index Page

2007-11-13T09:11:00.000-08:00

Step-By-Step-Procedures

2007-11-13T09:07:00.000-08:00

RAC Installation Checks/Concepts

2007-11-13T08:26:00.000-08:00

This article purpose is to help you to configure the RAC cluster. This article assumes a basic understanding of oracle database & operating system.

1) RAC Database Storage & Cluster File System Requirements

All datafiles, control files, redo log files, and the server parameter file (SPFILE) in Oracle RAC environments must reside on shared storage that is accessible by all the instances.There are several solutions available for storage

Vendor Cluster File System such as VCFS on solaris or General Parallel File System (GPFS) on IBM platforms
Shared Storage such as NetApp Filers
Oracle Cluster File System for Microsoft Windows and Linux
Raw Devices with ASM

Minimum no of raw devices required:-

Three raw devices will be required for voting disk. Manages cluster membership and arbitrates cluster ownership between the nodes in case of network failures. Voting disk is a configuration file.

Two raw devices will be required for OCR disk. This will contain cluster configuration such as which database instances run on which nodes and which services run on which databases.

Four raw devices will be required for database storage in which database will reside.

2) RAC ORACLE_HOME Software Requirements

There will be two seperate ORACLE_HOMES will be installed in each node of the cluster.

CRS_HOME in which CRS Software will be installed.
ORACLE_HOME from which ORACLE RDBMS & ASM instances will be running.

3) Cluster Ware Requirements

Since oracle CRS comes free with RAC license, third party clusterware such as veritas VCS or IBM HACMP is not required for 10g RAC

4) NIC Card Requirements

Each node needs at least two network interface cards, or network adapters. One adapter is for the public network and the other adapter is for the private network used by the interconnect. You should install additional network adapters on a node if that node uses SAN or NAS storage. The private interconnect is a separate network that you configure between cluster nodeswill serve as communication path between the nodes in the cluster. RAC uses this to transmit the data blocks that are shared between the instances. This interconnect should be a private interconnect, meaning it is not be accessible by nodes that are not members of the cluster.

5) IP Addresses & Node Names

Three IP addresses and node names are required for each node.

One Public IP address is for public node name. For the public node name, use the primary host name of each node. Use the output displayed by the hostname command.(for example node1-pub)

One private IP address is for private host name for private interface.(for example: 10.*.*.* or 192.168.*.*).Oracle Database uses private IP addresses for instance-to-instance Cache Fusion traffic (example private node name would be node1-priv)

One Virtual IP address with an associated network name(virtual host name) with same subnet as your public interface.The virtual host name for each node should be registered with your DNS. If you do not have an available DNS, then record the virtual host name and IP address in the system hosts file, /etc/hosts

Public interface names must be the same for all nodes. If the public interface on one node uses the network adapter eth0, then you must configure eth0 as the public interface on all nodes.Private interface names must be the same for all nodes. If eth1 is the private interface name for the first node, then eth1 should be the private interface name for your second node.For the private network, the end points of all designated interconnect interfaces must be completely reachable on the network. There should be no node that is not accessible by other nodes in the cluster using the private network.

6) Network Configuration Checks for RAC

When you complete the network configuration, the IP address and network interface configuration should be similar to what is shown in the following table (your node names and IP addresses might be different):

Node Node Name Type IP Address Registered in

devrac1 devrac1 Public 143.46.43.100 DNS (if available, else the hosts file)

devrac1 devrac1-vip Virtual 143.46.43.104 DNS (if available, else the hosts file)

devrac1 devrac1-priv Private 10.10.10.11 Hosts file

devrac2 devrac2 Public 143.46.43.101 DNS (if available, else the hosts file)

devrac2 devrac2-vip Virtual 143.46.43.105 DNS (if available, else the hosts file)

devrac2 devrac2-priv Private 10.10.10.12

Entries in Hosts file

127.0.0.1 localhost.localdomain localhost

143.46.43.100 devrac1.mycompany.com devrac1

143.46.43.104 devrac1-vip.mycompany.com devrac1-vip

10.10.10.11 devrac1-priv

143.46.43.101 devrac2.mycompany.com devrac2

143.46.43.105 devrac2-vip.mycompany.com devrac2-vip

10.10.10.12 devrac2-priv

As the root user, verify the network configuration by using the ping command to test the connection from devrac1 from devrac2 and the reverse. As the root user, run the following commands on each node:

# ping -c 3 devrac1.mycompany.com

# ping -c 3 devrac1

# ping -c 3 devrac1-priv

# ping -c 3 devrac2.mycompany.com

# ping -c 3 devrac2

# ping -c 3 devrac2-priv

You will not be able to discover the nodes using the ping command for the virtual IPs (devrac1-vip, devrac2-vip) until after Oracle Clusterware is installed and running. If the ping commands for the public or private addresses fail, resolve the issue before you proceed.Ensure that you can access the default gateway with a ping command.

7) Other Pre-Requisities

SSH trust should be established
RSH is also required during installation of software
Login banner should be disabled
User equlancy should be present
raw devices should be initialized and owned by the oracle software owner
xterm tools such as exceed is required for installation.All pre-req os patches & kernel settings. Please refer to install guide.
Run cluster verify utility using /staging_area/clusterware/cluvfy/runcluvfy.sh stage -pre crsinst -n node1,node -verbose.

APPSDBA Scripts/Code Snippets

2007-06-14T18:28:00.000-07:00

Identify Failed Adpatch Jobs Sqlscript
Idetify Patch Jobs Run By adpatch Sqlscript
Idenitfy FND UserLoad Sqlscript
Register AOL Tables Sqlscript
Concurrent Manager Info Sqlscript
Concurrent Request Load Sqlscript
Concurrent Manager HealthCheck Sqlscript
Merged Patch Details Sqlscript
SQL script to troubleshoot long running concurrent request
Identifying APPS front-end username for a given sid
Identifying SID for a given front-end username
Tracing Wait Events
Tracing Optimizer
Identify blocking locks
Identify indexes which requires rebuild