Madan Mohan

 SELECT

   distinct(os_username),username,userhost,timestamp

FROM

    sys.dba_audit_session where username in ('<UserName>')

 /tmp File system hit 100% after running some extraction that failed in half-way. The extracted files were deleted but the Space is not released.

Filesystem                                                               Size  Used Avail Use% Mounted on

/dev/mapper/rhel-tmp                                                      20G   20G   20K 100% /tmp

Find the runaway  os process that is not releasing the space.

************************************************

use the below command 

ll -d /proc/[1-9]*/fd/* | grep /tmp/

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/2200/fd/1 -> /tmp/OracleExtracts/nohup.out (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/2200/fd/2 -> /tmp/OracleExtracts/nohup.out (deleted)

lr-x------ 1 apps apps 64 Sep 16 05:20 /proc/2200/fd/9 -> /tmp/OracleExtracts/Oracle_Extract_Scripts_ALL.sql (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/1 -> /tmp/OracleExtracts/nohup.out (deleted)

l-wx------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/2 -> /tmp/OracleExtracts/nohup.out (deleted)

lr-x------ 1 apps apps 64 Sep 16 06:20 /proc/624/fd/9 -> /tmp/OracleExtracts/Oracle_Extract_Scripts_ALL.sql (deleted)

Kill the os process identified in above  output

************************************

kill -9 624 

kill -9 2200

After killing the os processes the system FS "/tmp" will be back to normal

Filesystem                                                               Size  Used Avail Use% Mounted on

/dev/mapper/rhel-tmp                                                      20G      34M  20G   1% /tmp

 select mp.attribute7 Fab_Code,count(*) from 

apps.oe_order_lines_all oeol,

apps.mtl_parameters mp

where to_date(oeol.schedule_ship_date) between to_date('08-09-2024 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and to_date('08-09-2024 23:59:59', 'DD-MM-YYYY HH24:MI:SS')

and mp.organization_id=oeol.ship_from_org_id

group by mp.attribute7

order by mp.attribute7;

Issue ***** Julian Calendar Error. APP-FND-01270 Error Generating Julian SYSDATE Reason For Error ***************** Server date and Timestamp changed by modifying the ntp.conf and restarting the NTPd service Solution ********* restart the Application Services. Precaution ************ Never ,Ever Change the ntp configuration on the fly. Bring down the Apps services and change the ntp configuration and restart apps services.

/bin/pkginfo -i SUNWarc SUNWbtool SUNWcsl SUNWhea SUNWi15cs SUNWi1cs SUNWi1of SUNWlibC SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWxwfnt

pkgadd -p | grep i.e 123456

showrev -p | grep i.e 123456-01

showrev -p | sort -n +2 | nawk '{printf "%s ",$2}'

Solution
---------

Increase the PROCESSES parameter in the ASM parameter file

Processes = 25 + 15 * n, where n is the number of instances on the box using ASM for their storage.

NOTE : this formula is for a basic instance and does not accomodate for

* Multiple ARCH processes
* Multiple LGWR processes

Should the ORA-0020 occur even after implementing this formula ... add additional for any multiples of these background processes


Refer NOTE 265633.1 "ASM Technical Best Practices" for more information.

Problem;
*******
AR.HZ_LOCATIONS_N15 is UNUSABLE .

SQL> select index_name,owner,status from dba_indexes where status='UNUSABLE';

INDEX_NAME OWNER STATUS
------------------------------ ------------------------------ --------
HZ_LOCATIONS_N15 AR UNUSABLE


Solution
********
a) delete user_sdo_geom_metadata
where table_name='HZ_LOCATIONS'
and COLUMN_NAME='GEOMETRY';

b) drop index AR.HZ_LOCATIONS_N15 force;

c) cd $AR_TOP/patch/115/sql
sqlplus ar/ar @ARHGEOIS.sql
sqlplus apps/apps @ARHGEOID.sql

You'll be prompted to enter a value, choose AR
Enter the value for value 1 : AR


Verify the Object by running the below query as APPS.

select index_name,status from dba_indexes where table_name='HZ_LOCATIONS' and index_name='HZ_LOCATIONS_N15';

Rgds,
Madan

Issue:
******
One of the DBMS JOB failed and recorded error in the alertlog as

From ORCL database alert log file /ORCL/ORCLDB/10.2.0/admin/ORCL_ctloraerp06/bdump/alert_ORCL.log

9:ORA-12012: error on auto execute of job 282253
10:ORA-04063: ORA-04063: package body "SYS.DBMS_STATS" has errors

Observation:
************

SYS.DBMS_STATS Package Body is Invalid.

select object_name,object_type ,owner, status from dba_objects where object_name='DBMS_STATS';

OBJECT_NAME OBJECT_TYPE OWNER STATUS
-------------------- ------------------- ------------------------------ -------
DBMS_STATS PACKAGE SYS VALID
DBMS_STATS PACKAGE BODY SYS INVALID
DBMS_STATS SYNONYM PUBLIC VALID

Solution
*********

Rebuild/ Re-compile the DBMS_STATS Objects as

connect / as sysdba

@?/rdbms/admin/dbmsstat.sql
@?/rdbms/admin/prvtstas.plb
@?/rdbms/admin/prvtstat.plb

Migrated to the new tablespaces using OATM but there are objects left behind in original tablespaces. There were no errors reported during tablespace migration.

SQL> select tablespace_name, count(1) from dba_Segments group by tablespace_name;
TABLESPACE_NAME COUNT(1)
------------------------------ ----------
APPLSYSD 1
APPLSYSX 1
COMD 26
COMX 47
CTXD 77
EDWREP 88
EDWREPX 31
PVD 1
PVX 1

SQL> select segment_name, segment_type from dba_segments
2* where tablespace_name='APPLSYSD'
SEGMENT_NA SEGMENT_TYPE
---------- ------------------
20.42 SPACE HEADER

Cause
*******
One of the circumstances under which a 'SPACE HEADER' segment gets created is if a 'dictionary managed' tablespace is migrated to 'locally managed' (see dbms_space_admin.tablespace_migrate_to_local()).

The space header segment contains the extent bitmap and is allocated during the migration of the tablespace. Since there is no reserved space after the file header (as with locally managed tablespaces) the bitmap segment will be allocated somewhere in the "data" area of the datafile. During its creation the segment will pick up some of the storage attributes (e.g. MAXEXTENTS) from the default storage clause of the tablespace. Once the segment has been created it can neither be dropped nor changed.

Fix
****
You can ignore these "left-over" objects. Please go ahead and drop old tablespaces

THE RECYCLE BIN
*****************

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.


There is no issues with DROPping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS objects

Checking the RECYCLEBIN Objects
*******************************

SELECT object_name,original_name,operation,type,dropscn,droptime FROM user_recyclebin;

SELECT owner,original_name,operation,type FROM dba_recyclebin;


Purging the Recyclebin
**************************
Subject: 10g Recyclebin Features And How To Disable it( _recyclebin )
Doc ID: Note:265253.1 Type: BULLETIN

Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.0
Information in this document applies to any platform.
Purpose:- This bulletin illustrates the new recyclebin functionality provided with the 10g database

Scope and ApplicationCan be used by Oracle Support Analyst and DBA

10g Recyclebin Features And How To Disable it( _recyclebin )ABOUT 10g RECYCLEBIN
In order to have FLASHBACK DROP functionality a recyclebin is provided to every oracle user.

SQL> desc recyclebin
Name Null? Type
----------------------------------------- -------- ------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER

The recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table.

Related recyclebin objects:

SQL> SELECT SUBSTR(object_name,1,50),object_type,owner
FROM dba_objects
WHERE object_name LIKE '%RECYCLEBIN%';
/
SUBSTR(OBJECT_NAME,1,50) OBJECT_TYPE OWNER
--------------------------- ------------------- ----------
RECYCLEBIN$ TABLE SYS
RECYCLEBIN$_OBJ INDEX SYS
RECYCLEBIN$_TS INDEX SYS
RECYCLEBIN$_OWNER INDEX SYS
USER_RECYCLEBIN VIEW SYS
USER_RECYCLEBIN SYNONYM PUBLIC
RECYCLEBIN SYNONYM PUBLIC
DBA_RECYCLEBIN VIEW SYS
DBA_RECYCLEBIN SYNONYM PUBLIC

9 rows selected.

EXAMPLE
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> sho user
USER is "BH"

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
no rows selected

SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT object_name,original_name,operation,type,dropscn,droptime
2 FROM user_recyclebin
3 /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME
------------------------------ -------------------------------- --------- ------------------------- ---------- -------------------
BIN$1Unhj5+DSHDgNAgAIKds8A==$0 T1 DROP TABLE 8.1832E+12 2004-03-10:11:03:49

SQL> sho user
USER is "SYS"

SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /

OWNER ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- ------
BH T1 DROP TABLE

We can also create a new table with the same name at this point.

@NOTE:
@Pre-10.1.0.3, the recycled objects can also be viewed in user_tables and dba_tables
@Fix for Bug 3255906 changed this behaviour to maintain compatibility with 9i



PURGING
********

In order to completely remove the table from the DB and to release the space the new PURGE command is used.

From BH user:
SQL> PURGE TABLE t1;
Table purged.

OR

SQL> PURGE TABLE "BIN$1UtrT/b1ScbgNAgAIKds8A==$0";
Table purged.

From SYSDBA user:
SQL> SELECT owner,original_name,operation,type
2 FROM dba_recyclebin
3 /
no rows selected

From BH user:
SQL> SHOW recyclebin
SQL>

There are various ways to PURGE objects:

PURGE TABLE t1;
PURGE INDEX ind1;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)

For an object, the owner or a user with SYSDBA privilege or a user with DROP ANY... system privilege for the type of object to be purged can PURGE it.


DISABLING RECYCLEBIN
**********************

We can DROP and PURGE a table with a single command

From BH user:
SQL> DROP TABLE t1 PURGE;
Table dropped.

SQL> SELECT *
2 FROM recyclebin
3 /
no rows selected

There is no need to PURGE.

On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

From SYSDBA user:
SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
ORIGINAL_NAME
--------------
T1

From SYSDBA user:
SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
no rows selected

There is no need to PURGE.

As with anyother underscore parameter, setting this parameter is not recommended unless
advised by oracle support services.

On 10gR2 recyclebin is a initialization parameter and bydefault its ON.
We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.

For Better DR (Disaster Recovery) Site setup it is important to know the required Bandwidth Link Between the Primary and DR Site.

By using the Below formula, we can estimate the required Bandwidth Based on the Peak redo rate.

Required bandwidth = ((Redo rate in bytes per second / 0.7) * 8) / 1,000,000

= bandwidth in Mbps.

How to find a Redo Rate for a Database:-
*********************************************
Redo Rate can be found out from the Statspack report. During the peak duration of your business, run a Statspack snapshot at periodic intervals. For example, you may run it three times during your peak hours, each time for a five-minute duration. The Statspack snapshot report will include a "Redo size" line under the "Load Profile" section near the beginning of the report. This line includes the "Per Second" and "Per Transaction" measurements for the redo size in bytes during the snapshot interval. Make a note of the "Per Second" value. Take the highest "Redo size" "Per Second" value of these three snapshots, and that is your peak redo generation rate. For example, this highest "Per Second" value may be 394,253 bytes or 385 KB.

Req'd Bandwidth = ((394253 / 0.7) * 8) / 1,000,000
= 4.5 Mbps

Most of the DBA's work 24 x 7 and finds difficiult and time consuming to configure the Whole List of Server settings in PUTTY. I had gone through Google hits and found one workaround to import your putty settings .


Work Around
************

1. Run the command --> regedit /e "%userprofile%\desktop\putty.reg" HKEY_CURRENT_USER\Software\SimonTatham at the command prompt.

2. Copy the Putty.exe and putty.reg onto Target Machine.

3. Right Click the putty.reg and click the option "Merge", this will import the settings to the target registry, and after that you can see all the server details which were defined by you earlier in Source Machine.

Note:- SimonTatham is the person behind the PUTTY Software.

Following are the scripts used for copying and compressing the Datafiles within the Same Server.

Scripts
*********
1. copy_process.sh ------> This File consists of all the functions which are used for copy and compress.

2. copy_file_process.sh ------> This File consists of commands used for copying , compressing , uncompressing . This file is being called by the copy_process.sh

3. worker_no ------> Define the No. of Workers (Process) for the whole process. This value can be dynamically changed by using the command , echo 4 > worker_no.


copy_process.sh
****************

#! /usr/bin/ksh
##################################################################################
# bkp_dir_path is the source (TO directory ) name
# src_path is the target (From directory ) name
# worker_pid is the worker pid file
# worker_no is the number of workers, can be adjusted while the script is running
# example, to set 3 workers, perform the following before running the script: echo 3 > worker_no
# file

function Gen_Env
{
bkp_dir_path="/tmp/to_data"
src_path="/tmp/from_data"
Log_Date=$(date +"%d%m%y")
worker_pid=/tmp/copy_process_
worker_no=/tmp/worker_no
worker_max_count=0
COPY_FILE_PROCESS=/tmp/copy_file_process.sh
File_List=$(cd $src_path; ls -l *.dbf |grep -v cntrl | awk '{print $9}')
}

function Copy_Phase
{
num_copy_workers=`cat $worker_no`
if [[ $worker_max_count -lt $num_copy_workers ]] then
worker_max_count=num_copy_workers
fi

worker=1
while [[ $worker -le $num_copy_workers ]]
do
worker_file=${worker_pid}${worker}.pid
if [[ ! -s $worker_file ]] then
echo "Busy" > $worker_file
$COPY_FILE_PROCESS $src_path $datafile $worker $worker_file $bkp_dir_path &
echo "Copy Assigned to Worker pid file: $worker"
Copy_Assigned=Yes
return
fi
let worker=$worker+1
done
sleep 5
}

function Check_Final_Copy
{
worker=1
while [[ $worker -le $worker_max_count ]]
do
worker_file=${worker_pid}${worker}.pid
if [[ -s $worker_file ]] then
echo "Background Copy is still Running... $(date)"
sleep 60
CheckFinal=No
return
else
rm -f $worker_file
fi
let worker=$worker+1
done
CheckFinal=Yes
}

function main
{
Gen_Env
echo "Total number of Datafiles in the Source Instance"
src_dbf_count=`ls $src_path|wc -l`
echo $src_dbf_count
date > $bkp_dir_path/time.log
for datafile in `echo $File_List`
do
echo "Copy $datafile : $(date)"
echo "Wait for the Next Worker ... $(date)"
Copy_Assigned=No
while [[ $Copy_Assigned == 'No' ]]
do
Copy_Phase
done
done

CheckFinal=No
while [[ $CheckFinal == 'No' ]]
do
Check_Final_Copy
done
date >> $bkp_dir_path/time.log
echo "**********************************************"
echo " Copy Process Completed Successfully"
echo "**********************************************"
echo "Number of Data files copied to the target "
target_dbf_count=`ls $bkp_dir_path|wc -l`
echo $target_dbf_count
}
main

################### End of Copy_process.sh ###########


copy_file_process.sh
**********************

#!/usr/bin/ksh
##############################################################
## Copy and compress script
## Phase - I = Copy the files from Source to Destination
## Phase - II = Compress the Destination Files.
## Phase - III = Uncompress the Destination File. (if required)
##
##
##############################################################
s_path=$1
filename=$2
worker_no=$3
statusfile=$4
d_path=$5
statuss=`cat $statusfile`
##############################################################
if [[ $statuss == 'Busy' ]] then
echo "$$" >$statusfile
## Phase I
echo "Copying $filename by worker $worker_no"
cp $s_path/$filename $d_path
sleep 5

# Phase II
echo "Zipping $filename by worker $worker_no"
/usr/bin/gzip $d_path/$filename
sleep 5

#Phase III
#echo "Unzipping $filename.gz by worker $worker_no"
#/usr/bin/gunzip $s_path/$filename.gz

>$statusfile
else
echo "Process is not Busy"
fi

echo '*********************'


################### END of copy_file_process.sh #############


Execution Syntax
*****************

1. Need to update the directory structure for the below variables within the script "copy_process.sh".

a) bkp_dir_path ---> Where to backup the datafiles.
b) src_path ---> Location of source data files.
c) COPY_FILE_PROCESS ---> Location of "copy_file_process.sh script.


Syntax
********

nohup ./Location of copy_process.sh &

The Rman Catalog Upgarde is same as normal Database upgrade and can be accomplished in two ways.

a) Updrade the Database from 9i to 10g
- Connect to rman catalog datase.
- Issue the rman command "upgrade catalog" as this upgrades the catalog database from 09.02.00 to 10.02.00.03

b) Fresh Install of 10g Database / Use the existing 10g Database.
- Create the rman user and grant create session, recovery_catalog_owner, create type to rman user.
- Export import of Rman Schema
- Issue the rman Command :upgrade catalog"


Note:- You will encounter the below warning or error message , if you have not upgraded the catalog after the database version upgrade.
connected to target database: DSSPROD (DBID=1021024992)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 09.02.00 in RCVCAT database is too old

Solution
**********
1. Connect to recovery catalog database as rman user.
2. issue the rman command " upgrade catalog" twice

Sizing an UNDO tablespace requires three pieces of data.

- (UR) UNDO_RETENTION in seconds
- (UPS) Number of undo data blocks generated per second
- (DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.


The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.

The result of the query returns the number of undo blocks per second. This value needs to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE.


The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM
v$undostat), (select block_size as DBS from dba_tablespaces where
tablespace_name= (select value from v$parameter where name
= 'undo_tablespace'));

Pre-requisites
The steps in this guide should be performed after applying the following AutoConfig patches:-

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher



How to enable/disable/change the password

To enable/disable or change the password script addlnctl.pl must be used.

DB Tier: $ORACLE_HOME/appsutil/bin/addlnctl.pl
Apps Tier: $AD_TOP/bin/addlnctl.pl

This has the following syntax:-

Valid arguments for addlnctl.pl:
help : get usage information
contextfile : provide Applications or DB Tier context file name

Set the Applications (APPSORA.env) or RBDMS ($CONTEXT_NAME.env)
Run one of the following commands

Example 1: To enable listener password

addlnctl.pl contextfile=$CONTEXT_FILE enablepassword

Example 2: To disable listener password

addlnctl.pl contextfile=$CONTEXT_FILE disablepassword

Example 3: To change existing listener password

addlnctl.pl contextfile=$CONTEXT_FILE changepassword

Further information:
This will change the contextfile variable s_enable_listener_password to "ON" or "OFF"
Update/Remove the listener.ora with a PASSWORDS_PROD entry and the encypted password value

Example

PASSWORDS_PROD=BC73ED1DD01AC862
If the listener is not running it will not start it.
If the listener is currently running it will stop and restart it.
Controlling a password protected listener .Once the password is enabled the following methods can now be used to stop/start the listener:-


Apps Tier:

Use script $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adalnctl.sh

Examples:-
$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adalnctl.sh start
$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adalnctl.sh stop

This script will check the listener.ora for the encypted password, and use this to stop/start the listener.

DB Tier:

Method A: Use script $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addlnctl.sh

Examples:-

$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addlnctl.sh start PROD
$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addlnctl.sh stop PROD

This script will check the listener.ora for the encypted password, and use this to stop/start the listener.

Method B : Use command line options in the Listener Control Utility LSNRCTL
Start the Listener Control Utility

$ lsnrctl

Enter the listener name
LSNRCTL > set current_listener PROD

Enter password for the Listener
LSNRCTL > set password or

Examples:

LSNRCTL > set password 654FA907952B15B

or:

LSNRCTL > set password
Password: apps

To determine the Status of the listener:
LSNRCTL > status

To Stop the listener:
LSNRCTL > stop

To start the listener:
LSNRCTL > start

To exit :
LSNRCTL > start

INSTALLING THE 64BIT RELEASE

1. Ensure that there is ample free size for the 64bit release installation.
Recommended free space should be 3G.

2. Start the Installer GUI.


3. On the File Locations Screen, create a new name and path for the 64bit
oracle installation under the Destination.

A typical entry would be

Name: orahome920_64b
Path: /u01/app/oracle/product/9.2.0-64b

4. Proceed with the installation. Stop at the configuration assistant
configuration screen.

5. Install the latest 64bit patch set under the new oracle installation.


CHANGING THE WORD-SIZE OF YOUR CURRENT RELEASE

The instructions in this section guide you through changing the word-size of
your current release (switching from 32-bit software to 64-bit software or vice versa).

Complete the following steps to change the word-size of your current release:

1. Start SQL*Plus.

2. Connect to the database instance AS SYSDBA.

3. Run SHUTDOWN IMMEDIATE on the database:
SQL> SHUTDOWN IMMEDIATE

4. Perform a full offline backup of the data depending on the available backup
mechanism, eg BCV, Unix file copy.

5. If the initialization parameter file eg initSID.ora, spfileSID.ora, listener.ora, sqlnet.ora resides within the old OR
ACLE_HOME, then copy it to the corresponding location of the new 64b it ORACLE_HOME. If the parameter files are symbol
ic links to another location, then the symbolic links have to be created in the new ORACLE_HOME.

Example:
Old $ORACLE_HOME/dbs
initSID.ora->/u01/app/oracle/admin/lss/initSID.ora
spfileSID.ora->/u01/app/oracle/admin/lss/spfileSID.ora
The same links have to be created in new $ORACLE_HOME/dbs.


6. Change your active Oracle instance environment to point at the new 64Bit ORACLE_HOME.
Eg
a)
Edit /var/opt/oracle/oratab if using dbhome/oraenv to set the environment.

lss:/u01/app/oracle/product/9.2.0:Y

i) set it to the new 64bit Oracle Home path

lss:/u01/app/oracle/product/9.2.0-64b:Y

b)
Change the essential environment setting eg $ORACLE_HOME,$LD_LIBRARY_PATH to use new 64bit Oracle Home Path if h
ardcoded

export ORACLE_HOME=/u01/app/oracle/product/9.2.0

Change to

export ORACLE_HOME=/u01/app/oracle/product/9.2.0-64b

7. Set AQ_TM_PROCESSES=0 if it is not.

a) If using initSID.ora to start instance, then add it to the init file.

b) If using spfileSID.ora to start instance,then the database can be startup and the parameter set by running th
e below command.

SQL> ALTER SYSTEM SET aq_tm_processes=0 SCOPE=SPFILE;

c) shutdown the database again.
9. Set _system_trig_enabled = false.

a) If using initSID.ora to start instance, then add it to the init file.
b) If using spfileSID.ora to start instance, then the database can be startup and the parameter set by running th
e below command.

SQL> ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=SPFILE;

c) shutdown the database again.


The parameter should be set to FALSE for scripts that perform dictionary operations as the objects on which the triggers d
epend may become invalid or be dropped, causing the triggers to fail and thus preventing the scripts from running successf
ully.

10. When migrating from a 32-bit Oracle version to a 64-bit Oracle version, Oracle recommends doubling the size of paramet
ers such as:

SHARED_POOL_SIZE
SHARED_POOL_RESERVED_SIZE
LARGE_POOL_SIZE

11. At a system prompt, change to the new 64bit ORACLE_HOME/rdbms/admin
directory.

12. Start SQL*Plus.

13. Connect to the database instance AS SYSDBA.

14. Run STARTUP RESTRICT:

SQL> STARTUP RESTRICT

15. Run the following script:

SQL> @?/rdbms/admin/catalog.sql

16. Check for DBMS_PACKAGE validity.

17. If invalid, run below

SQL> alter package dbms_standard compile;
18. Run the following script:

SQL> @?/rdbms/admin/catproc.sql

19. Set the system to spool results to a log file for later verification of
success.

SQL> SPOOL /tmp/catoutw.log

20. Run utlirp.sql:

SQL> @?/rdbms/admin/utlirp.sql

The utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. This script first
alters certain dictionary tables. Then, it reloads package STANDARD and DBMS_STANDARD, which are necessary for using P
L/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as packages, procedures, types, and so on.

21. 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 fil
e in Step 12; the suggested name was catoutw.log. Correct any problems you find in this file.


22. Run ALTER SYSTEM DISABLE RESTRICTED SESSION:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

23. Remove the parameter aq_tm_processes or set value back to the original value.

a) If using initSID.ora to start instance, then remove or edit it from the init file after shutting down the data
base.

b) If using spfileSID.ora to start instance, then the parameter can be change by running the below command.

SQL> ALTER SYSTEM SET aq_tm_processes= SCOPE=SPFILE;

To remove the parameter.

SQL> ALTER SYSTEM RESET aq_tm_processes SCOPE=SPFILE SID=.*.;
24. Remove the parameter _system_trig_enabled = FALSE

a) If using initSID.ora to start instance, then remove it from the init file after shutting down the database.

b) If using spfileSID.ora to start instance, then the parameter can be removed by running the below command.

SQL> ALTER SYSTEM RESET "_system_trig_enabled" SCOPE=SPFILE SID=.*.;

25. The word-size of your database is changed. The database can be shutdown and reopen for normal use.

Action Plan for Forms Patchset 18 Upgrade (4948577)


1. Take a backup of the the Printer File.

If you have customized the printer configuration file located in ORACLE_HOME/guicommon6/tk60/admin/uiprint.txt with your printer definitions, you will have to redo the configurations after applying the Developer 6i patch. Your previous uiprint.txt is backed up as $ORACLE_HOME/guicommon6/tk60/admin/uiprint.txt.PRE_P4948577

cp $ORACLE_HOME/guicommon6/tk60/admin/uiprint.txt $ORACLE_HOME/guicommon6/tk60/admin/uiprint.txt.PRE_P4948577

2. Apply the Main Patch 4948577
3. Apply the Apps Interop Patch 4888294 ----11i Patch


Apply the Additional Patches

a. Apply the Patch 4968700

To apply this patch, please follow the steps below:

1) Stop your web listeners and Forms Server.

2) Make a patch directory for this bug, and put your patch files in it:
% cd $ORACLE_HOME
% mkdir bug5034714
% cd bug5034714
% cp bug5034714.zip to $ORACLE_HOME/bug5034714
% unzip bug5034714.zip

3) If you don't have genshlib in $ORACLE_HOME/bin directory then copy this
file over there.
% cp genshlib $ORACLE_HOME/bin
% chmod ug+x $ORACLE_HOME/bin/genshlib

4) Copy your original objects in case you ever need it:
% cd $ORACLE_HOME/lib
% cp libiffw.a libiffw.a.pre5034714
% cp libiwfw.a libiwfw.a.pre5034714

5) Archive objects into the libraries:
% ar rv libiffw.a $ORACLE_HOME/bug5034714/iftm.o
% ar rv libiwfw.a $ORACLE_HOME/bug5034714/iwit.o

6) Generate the shared libraries:
% cd $ORACLE_HOME/lib
% $ORACLE_HOME/bin/genshlib iwfw 0
% $ORACLE_HOME/bin/genshlib iffw 0

7) Generate forms executables
% cd $ORACLE_HOME/forms60/lib
% make -f ins_forms60w.mk install

Note: If you are an Oracle Applications customer, please also run adrelink.
E.g. to relink f60webmx for Oracle Applications:
% adrelink.sh force=y "fnd f60webmx"

8) Restart Forms server and Web listeners.


b. Apply the Patch 4261542.

Installation instructions for Apps customers
--------------------------------------------

[Part 1] Shut down the listeners and copy the patch files

1. Stop your web listeners and Forms Server.

2. Make a patch directory within your 6i ORACLE_HOME and unzip this file
within it to create a new subdirectory. You will be able to see the
folder 4261542 which contains the actual one-off files.

%cd $ORACLE_HOME
%unzip p4261542_600_GENERIC.zip


[Part 2] Unzip the java class files and regenerate your JAR files

3.Backup the Forms class files, i.e.$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class
$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
%cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
$ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542



4. Inside folder 4261542 in step-2 has class files in oracle\forms\engine directory.
Copy this file into ORACLE_HOME/forms60/java/oracle/forms/engine
%cd $ORACLE_HOME/4261542/oracle/forms/engine
%cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
%cd $ORACLE_HOME/4261542/oracle/forms/handler
%cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class


5. Make sure that you have set up custom certificates for JAR file signing.
Run adadmin administration utility. Select the "Maintain Files" option
and then the "Generate JAR Files" sub-option.

6. Spot check that the JAR files have been generated by verifying the timestamp.

[Part 3] Restart the listeners

7. Restart the Forms Server.

8. Restart the web listener serving Forms Applets (e.g. Apache or WebDB 2.2).


c. Apply the Patch 5216496.

- Not Availbale for HP TRU64.


STEPS INVOLVED in PATCHING

STEPS Involved in patching
***************************

1. Install the Developer 6i Patch

2. Install the Additional Developer 6i Patches

3. Relink Applications Executables on UNIX Platforms


You can relink these executables by running adadmin
When the Main Menu appears select 'Maintain Applications Files Menu' and then select 'Relink Applications Program'
Answer the questions below as follows, in order to select the individual executables for relinking.

Enter list of products to link ('all' for all products)[all] : fnd
Generate specific executables for each selected product [No] ? y
Relink with debug information [No] ? n

(You will then be offered a list of executables that are available for relinking)

Enter executables to relink, or enter 'all' [all] : f60webmx ar60run ar60runb ar60rund *

* In a multi-node configuration, not all these executables exist on each node. The list of executables will show those that do exist on the node you are currently running on, and only those should be entered to avoid errors.

Refer to the Maintaining Oracle Applications manual for more information on how to use the AD Administration Utility or AD Relink Utility for relinking executables.


4. Apply the Oracle Applications 11i Interoperability Patch


5. Verify your upgrade

Verify that the fndforms.jar and fndewt.jar JAR files have been rebuilt by checking the timestamp for both files in the $OA_JAVA/oracle/apps/fnd/jar directory. If the timestamp is not current and the previous steps completed successfully, run the AD Administration Utility (adadmin), select Maintain Applications Files, then select Generate Product JAR Files. Do not force the regeneration of all JAR files. Again, verify that the fndforms.jar and fndewt.jar JAR files have been rebuilt by checking their timestamps again.
If your JAR files are still not updated, verify all the prior steps, and the Known Issues section below.
If you have applied Developer 6i Patch 6 or above, verify that the PL/SQL version installed is 8.0.6.3.x. You can run '$ORACLE_HOME/bin/f60gen help=y' to display various component versions. (If the version shown is still 8.0.6.0.x please refer to Metalink Note 191573.1 )
For Microsoft Windows platforms, look under Control Panel - Services to see if additional Forms and Reports services were added. If so, disable the new ones by pressing the Startup button, and setting the Startup Type toDisabled.


6. Start the Forms, Reports, HTTP, Concurrent Manager Servers

a. Stop the Forms Server process.

b. Add the following environment variable to the 'Oracle Forms 6.0 environment variables' section of
your '$APPL_TOP/< SID >.env' file as;

FORMS60_RESTRICT_ENTER_QUERY="TRUE"
export FORMS60_RESTRICT_ENTER_QUERY

c. Source the environment, then restart the Forms Server process

Pre-requisites
***************

Source
-------

----> For Customer Specific patch

1. Apply the Platform Migration patch 3453499 (ADX.F)
2. Make sure you have zip2.3 installed on Source Machine
3. Generate and upload the manifest of customet specific files.
- Log into source as applmgr user and source the APPL_TOP environment file.
- Generate the customer specific file manifest by executing the below command.It
generates the file adgenpsf.txt under $APPL_TOP/admin/$TWO_TASK/out

- perl $AD_TOP/bin/adgenpsf.pl
4. Go to http://updates.oracle.com/PlatformMigration and use your metalink username
and password and follow the instructions on the screen to upload the manifest
file "adgenpsf.txt" which was created in step3.

----> Foe export / import

5. Apply the AD minipack F 2141471 (conditional).
6. Apply the Applications consolidated export/import utility patch 4872830.
7. If source is on 11.5.7, then apply the materialized views patch 2447246.
8. Apply latest Applications database preparation scripts patch 4775612.
9. Identify the Global_name
- select global_name from global_name;
10. create the export parameter file "exp_parameter.dat



Target
-------
1. Run the Rapid Install to create the 9.2.0 Home withour database portion.