Application Modules info ( short name and id ) and identifying Patch Level apps

Use the following query to retrieve this information on your specific instance.

SELECT FND.APPLICATION_ID, APPLICATION_SHORT_NAME, PRODUCT_CODE, APPLICATION_NAME
FROM FND_APPLICATION FND , FND_APPLICATION_TL FNDTL WHERE FND.APPLICATION_ID=FNDTL.APPLICATION_ID


How to find an AD patchset level in Oracle Applications


SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;

select APP_SHORT_NAME, PATCH_LEVEL from apps.AD_PATCH_DRIVER_MINIPKS where APP_SHORT_NAME like '%PF%';

Views for DBA

V$views

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status
v$db_object_cache
v$enqueue_stat
v$event_name
v$eventmetric
v$filemetric
v$filestat
v$fixed_table
v$fixed_view_definition
v$instance
v$latch
v$librarycache
v$lock
v$locked_object
v$log
v$log_history
v$logfile
v$logmnr_contents
v$logstdby
v$managed_standby
v$mystat
v$nls_parameters
v$nls_valid_values
v$object_usage
v$open_cursor
v$option
v$parameter
v$pgastat
v$process
v$pwfile_users
v$recover_file
v$reserved_words
v$resource_limit
v$rollname
v$rollstat
v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat
v$sga
v$sga_dynamic_components
v$sga_resize_ops
v$sgastat
v$sort_segment
v$sort_usage
v$spparameter
v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines
v$standby_log
v$statname
v$sysaux_occupants
v$sysmetric
v$sysmetric_history
v$sysstat
v$system_event
v$tempfile
v$tempseg_usage
v$tempseg_usage
v$tempstat
v$thread
v$timer
v$timezone_names
v$transaction
v$transportable_platform
v$undostat
v$version
v$waitstat

Performance Tuning Factors (Application & Database)

V$ACCESS   This view shows objects in the database that are currently locked and the sessions that are accessing them

V$ACTIVE_INSTANCES  This view maps instance names to instance numbers for all instances that have the database currently mounted.

V$ARCHIVE  This view contains information on archive logs for each thread in the database system. Each row provides information for one thread.

V$BGPROCESS  This view describes the background processes.

Concurrent managers tuning

-PMON, QUEUE SIZE, SLEEP TIME
- Purging Concurrent Request

-adjusting concurrent manager cache size

-Analyzing Oracle Apps Dictionary Tables for High Performance

PMON cycle->waits between the time it checks for failed managers

QUEUE Size->no. of PMOn cycles the ICM waits to check for new CM or the disabled CM's

SLEEP Time-> no. of sec the ICM waits for the checking the request to be run

Purging concurrent request

Purging these two tables

-> FND_CONCURRENT_PROCESS
-> FND_CONCURRENT_REQUESTS
( should not exceed 50k )

Gather table stat for

FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Purge obsolete workflow runtime data as sysadmin


##gather schema stat

as sysadmin ->run-> gather schema stat%
as sysadmin ->run-> gather table stat%

form issues -->  help -> diag->trace with binds
concurrent manager issues->
profile option -> allow debugging-> set profile yes


Database Tuning

Wait Events for 11g

v$session
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v$wait_chains

Oracle 11g Concurrency and SQL Tuning

v$lock
v$sql
v$sqlarea
v$sesstat
v$mystat
v$sess_io
v$sysstat
v$statname
v$osstat
v$active_session_history
v$active_sess_pool_mth
v$session_wait
v$session_wait_class
v$system_wait_class
v$transaction
v$locked_object
v$latch
v$latch_children
v$latch_parent
v$latchname
v$latchholder
v$latch_misses
v$enqueue_lock
v$transaction_enqueue
v$sys_optimizer_env
v$ses_optimizer_env
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all

 

DBA AUDIT SIGN-ON AUDIT

• Signon Audit Concurrent Requests - Use this report to view information about who is requesting what concurrent requests and from which responsibilities and forms.

• Signon Audit Forms - Use this report to view who is navigating to what form and when they do it.

• Signon Audit Responsibilities - Use this report to view who is selecting what responsibility and when they do it.

• Signon Audit Unsuccessful Logins - Use this report to view who unsuccessfully attempted to sign on to Oracle Applications as another user. An unsuccessful login occurs when a user enters a correct username but an incorrect password. You can generate Signon Audit Unsuccessful Logins Reports for any users, regardless of whom one is auditing.

• Signon Audit Users - Use this report to view who signs on and for how long
  

Export ORACLE_HOME, PERL, JAVA, PERL

export PATH
export ORACLE_HOME=/u03/oracle/PROD/db/tech_st/11.2.0
export ORACLE_SID=XXXXX
export PERL5LIB=/u03/oracle/XXXXX/db/tech_st/11.2.0/perl/lib/5.10.0
export PATH=:$ORACLE_HOME/perl/bin:$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=/u03/oracle/TEST/db/tech_st/11.2.0/network/admin/PRODP_mind64

Commands and guide APPS DBA

 

RMAN FILE LOCATION---------------------------------------------------------

show parameter recover;

 

connect RMAN adwith
catalog---------------------------------------------------

rman TARGET / CATALOG rman/cat@catdb

rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb   // rman target /

CONFIGURE CONTROLFILE AUTOBACKUP ON

restore controlfile from autobackup --- in case u loose control file -- will
be helpful in catalog mode

 

//RMAN backup //startup mount , archive log enabled

 

rman target /

 

 //rman backup

backup  format '/u02/backup/%T%U' database plus archivelog;-   hot backup

rman target /

restore datafile 9;- to restore particular corrupted file

recover database;

//in case of full restore

startup mount

restore database;

recover database;

 

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

 

 

 enable backup mode---------------------------------------------------------

                alter database begin backup

                select * from v$backup;

                

                backup control
file------------------------------------------------------

                alter database backup control file to trace as
'/u01/backup/ctrl%U' noreset logs;

                

                database
version---------------------------------------------------------

                select * from v$version;

                

                tablespace
names-----------------------------------------------------------

                select * from dba_tablespace

                

                mount
----------------------------------------------------------------------

                mount xx.xx.xx.xx:/u04 /smb

                mount xx.xx.xx.xx:/root/Server /root/Server

                

                unzip .tar
file-------------------------------------------------------------

                tar -xvf archive.tar -C /location

                

                rman backup
database--------------------------------------------------------

                rman>backup database;

                

 
crontab---------------------------------------------------------------------

                crontab min hours date month_count  day of week(0-6) command
location

                

                

                manual apply log
file--------------------------------------------------------

                alter database register logfile '/location of log file';

                

                secure copy file
---------------------------------------------------------------------

                scp -r /location apps2:/target location

                scp -r /root/Server x.x.x.x:/root/Server

                account
unlock----------------------------------------------------------------------
-------

                alter user user_name account unlock;

                alter user apps account unlock;

                

                apache
restart---------------------------------------------------------------------
----------

                cd /usr/local/apache-tomcat/bin/

                ./startup.sh

                

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

                $APPL_TOP

                /u01/oracle/TEST/apps/apps_st/appl

                

                $INST_TOP

                /u01/oracle/TEST/inst/apps/TEST_training

                

                $AD_TOP

                /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0

                

                adautoconfig adcmctl.sh adapcctl.sh adalnctl.sh adstrtal.sh
adstrtal.sh adfrmctl.sh adfrmsrvctl.sh

                /u01/oracle/TEST/inst/apps/TEST_training/admin/scripts

                

                environment files .env

                adovars.env fndenv.env devenv.env appsMIND.env(consolidated
env files)

                cd $APPL_TOP/admin

                

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

                NLS parameters

                

SELECT * FROM nls_session_parameters;

Instance parameters (change with ALTER SYSTEM):

SELECT * FROM nls_instance_parameters;

Database parameters (set during database creation):

SELECT * FROM nls_database_parameters;

 

 

cd $TECH_ST

pwd cd /u01/oracle/TEST/apps/tech_st

 

//Active Session History 

select * from v$active_session_history

 

 

adadmin./////////////maintenance mode

cd $AD_TOP

 

AD ADMINISTRATION LOG FILES

cd $APPL_TOP/admin/TEST/log/adadmin.log

 

adadmin log location////

/u01/oracle/TEST/apps/apps_st/appl/admin/TEST/log (cd $APPL_TOP)

----------------------------------------------------------------------------
-------------------------patch

check  version 

adident Header (name of patch)

// apply patch (no maintenance mode enabled as well as the application is
running) 

adpatch option=hot patch

//bugs

select * from ad_bugs;

 

//version information (patches)

select * from AD_CHECKS_FILE

 

//defaults file of adadmin // non interactive mode

pwd cd $APPL_TOP/admin/<SID>/-----.txt

adadmin defaultsfile=$APPL_TOP/admin/SID/defs.txt

logfile=-----.log \

workers=5 interactive=n

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

 //DB size growth in a year

 select to_char(creation_time, 'RRRR Month') "Month",

sum(bytes)/1024/1024 "Growth in Meg"

from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

 

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

//ad controller

cd $AD_TOP/bin

./adctrl                 

 

//worker log file adctrl

cd $APPL_TOP/admin/sid/log

 

//adworker status

ps -a |grep adworker

 

// adrelink logs location

cd $APPL_TOP/admin/logs

 

//applications context file

cd $INST_TOP/appl/admin/...xml

 

 

//rollback autoconfig    (./restore.sh)

/u01/oracle/TEST/inst/apps/DB_training/admin/out/  (cd $INST_TOP)

 

//autoconfig test mode

./adchkcfg.sh

db tier-- cd $ORACLE_HOME/appsutil/bin

apps tier-- cd $AD_TOP/bin

 

//AD configuration

sqlplus apps/apps_PASSWORD

@adutconf.sql

pwd /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql

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

//find command

find . -name ("TEST3")

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

 

root@host1:/u01/app/product/grid/11.2.0/crs/install> ./rootcrs.pl -verbose
-deconfig -force

 

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

//job timing report

sqlplus apps/apps

@adtimrpt.sql

/u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql  ($AD_TOP)

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

//character set conversion

create a copy of the file 

example cp xyz.sql xyz.old

adcnv afcmstat.old xyz \ afcmstat.sql UTF8

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

//adsplice (off-cycle products)

 

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

//patch history file

applptch.txt

cd $APPL_TOP/admin/SID 

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

//version information (patches)

select * from AD_CHECKS_FILE

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

//autopatch log files

$APPL_TOP/admin/SID/log/adpatch.log

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

//patch information files

$APPL_TOP/admin/SID/log/adpatch.lgi

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

//autopath test mode

adpatch apply=n

 

// merge patch

cd $AD_TOP/bin

./admerge.pl

admrgpch <source_dir> <dest_dir>

 

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

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

//Rapid Clone 

----------------source location-------------------------------------(/*
PRECLONE STEPS */)

cd $ORACLE_HOME/appsutil/scripts/context_name

./ adpreclone.pl dbTier------------------------db tier

cd $INST_TOP/admin/scripts

./adpreclone.pl appsTier-----------------------application Tier

 

copy all the application tier file system to the target system
(appl_top,common_top,technology stack 10.1.2 ,10.1.3)

----------------target location---------------------------------------(/*
POSTCLONE STEPS */)

cd $ORACLE_HOME/appsutil/clone/bin

./adcfgclone.pl dbTier----------------------------db Tier

cd $COMMON_TOP/clone/bin

./adcfgclone.pl appsTier---------------------------application Tier

 

 

//enable archive log mode

SQL> alter database archivelog;

archive log list;

select log_mode from v$database;

 

//disable archive log

 

Shutdown edit pfile / spfile (spfile when instance is up of course) to say
log_archive_start = false.

startup mount

alter database noarchivelog;

alter database open;

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

//chmod

chmod -R 777 /u02

 

//chown 

chown -R applprod.dba /u03/oracle...

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

//add datafile

alter tablespace system add datafile
'/u01/oracle/oradata/prodtest1/system02.dbf' size 50G;

 

//add log file to existing group

sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO
GROUP 2;

 

//add log file to the new group

alter database add logfile group 5
('/u01/oracle/oradata/prod/redologt1.dbf','/u01/oracle/oradata/prod/logt2.db
f') size 50m;

 

//control file creation 

"CREATE CONTROLFILE

 SET DATABASE prod

   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',"

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

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

 // Data dump

 

 CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS
'/u01/app/oracle/oradata/';---------------------create a directory where the
dump file will be placed

GRANT READ, WRITE ON DIRECTORY test_dir TO scott; 

 

Table Exports/Imports

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log---export 

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log---import

 

(/*  IN THIS CASE THE TARGET DATABASE IS SAME BUT SUPPOSE WE NEED TO
TRANSFER IT TO DIFFERENT DATABASE , WE HAVE TO CREATE A SEPARATE DIRECTORY
THERE AND MOVE THE DUMP FILE TO THAT LOCATION) */

 

Schema Exports/Imports

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp
logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp
logfile=impdpSCOTT.log

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

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

//kill process

pkill -u applprod

 

 

RMAN FILE LOCATION---------------------------------------------------------

show parameter recover;

 

connect RMAN with catalog---------------------------------------------------

rman TARGET / CATALOG rman/cat@catdb

rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb   // rman target /

 

//RMAN backup

rman target /

 

 //rman backup

backup  format '/u01/backup/%U' database plus archivelog;

 

 

 restore controlfile from autobackup

rman 

restore database

 

 

 enable backup mode---------------------------------------------------------

                alter database begin backup

                select * from v$backup;

                

                backup control
file------------------------------------------------------

                alter database backup control file to trace as
'/u01/backup/ctrl%U' noreset logs;

                

                linux version

                cat /proc/version

                uname -m  or a

                

                //oracle ebs version

                select release_name from apps.fnd_product_groups;

                

                

                database
version---------------------------------------------------------

                select * from v$version;

                

                tablespace
names-----------------------------------------------------------

                select * from dba_tablespace

                

                mount
----------------------------------------------------------------------

                mount X.X.X.X:/u04 /smb

                mount X.X.X.X:/root/Server /root/Server

                

                unzip .tar
file-------------------------------------------------------------

                tar -xvf archive.tar -C /location

                

                rman backup
database--------------------------------------------------------

                rman>backup database;

                

 
crontab---------------------------------------------------------------------

                crontab min hours date month_count  day of week(0-6) command
location

                

                

                manual apply log
file--------------------------------------------------------

                alter database register logfile '/location of log file';

                

                secure copy file
---------------------------------------------------------------------

                scp -rfv /location apps2:/target location

                

                account
unlock----------------------------------------------------------------------
-------

                alter user user_name account unlock;

                alter user apps account unlock;

                

                apache
restart---------------------------------------------------------------------
----------

                cd /usr/local/apache-tomcat/bin/

                ./startup.sh

                

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

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

                

                ///          web service listener in oracle apps

                

                

A. Script to start/stop OPMN in R12 - $ADMIN_SCRIPTS_HOME/ adopmnctl.sh

B. Script to start/stop HTTP Server in R12 - $ADMIN_SCRIPTS_HOME/
adapcctl.sh

C. Script to start/stop oacore OC4J in R12 - $ADMIN_SCRIPTS_HOME/
adoacorectl.sh

D. Script to start/stop oafm OC4J in R12 - $ADMIN_SCRIPTS_HOME/ adoafmctl.sh

E. Script to start/stop forms OC4J in R12 - $ADMIN_SCRIPTS_HOME/
adformsctl.sh

 

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

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

                $APPL_TOP

                /u01/oracle/TEST/apps/apps_st/appl

                

                $INST_TOP

                /u01/oracle/TEST/inst/apps/TEST_training

                

                $AD_TOP

                /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0

                

                adautoconfig adcmctl.sh adapcctl.sh adalnctl.sh adstrtal.sh
adstrtal.sh adfrmctl.sh adfrmsrvctl.sh

                /u01/oracle/TEST/inst/apps/TEST_training/admin/scripts

                

                environment files .env

                adovars.env fndenv.env devenv.env appsMIND.env(consolidated
env files)

                cd $APPL_TOP/admin

                

                //concurrent manager restart

                cd $INST_TOP/admin/scripts

                adcmctl.sh start apps/appsPWD

                

                //oracle server process opmnctl

                cd $INST_TOP/admin/scripts

                ./adopmnctl.sh apps/appsPWD

                

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

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

                NLS parameters

                

SELECT * FROM nls_session_parameters;

Instance parameters (change with ALTER SYSTEM):

SELECT * FROM nls_instance_parameters;

Database parameters (set during database creation):

SELECT * FROM nls_database_parameters;

 

 

//Active Session History 

select * from v$active_session_history

 

 

adadmin./////////////maintenance mode

cd $AD_TOP

 

AD ADMINISTRATION LOG FILES

cd $APPL_TOP/admin/TEST/log/adadmin.log

 

adadmin log location////

/u01/oracle/TEST/apps/apps_st/appl/admin/MIND/log (cd $APPL_TOP)

 

check  version 

adident Header (name of patch)

// apply patch (no maintenance mode enabled as well as the application is
running) 

adpatch option=hotpatch

//bugs

select * from ad_bugs;

 

//defaults file of adadmin // non interactive mode

pwd cd $APPL_TOP/admin/<SID>/-----.txt

adadmin defaultsfile=$APPL_TOP/admin/SID/defs.txt

logfile=-----.log \

workers=5 interactive=n

 

 //DB size growth in a year

 select to_char(creation_time, 'RRRR Month') "Month",

sum(bytes)/1024/1024 "Growth in Meg"

from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

 

 

//ad controller

cd $AD_TOP/bin

./adctrl                 

 

//worker log file adctrl

cd $APPL_TOP/admin/sid/log

 

//adworker status

ps -a |grep adworker

 

// adrelink logs location

cd $APPL_TOP/admin/logs

 

//applications context file

cd $INST_TOP/appl/admin/...xml

 

 

//rollback autoconfig    (./restore.sh)

/u01/oracle/TEST/inst/apps/TEST_training/admin/out/  (cd $INST_TOP)

 

//autoconfig test mode

./adchkcfg.sh

db tier-- cd $ORACLE_HOME/appsutil/bin
//logs-appsutil/log/date/adconfig.log

apps tier-- cd $AD_TOP/bin              //logs-INST_TOP/sid/admin/log

//job timing report

sqlplus apps/apps

@adtimrpt.sql

/u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql  ($AD_TOP)

 

//character set conversion

create a copy of the file 

example cp xyz.sql xyz.old

adcnv afcmstat.old xyz \ afcmstat.sql UTF8

 

//adsplice (off-cycle products)

 

 

//patch history file

applptch.txt

cd $APPL_TOP/admin/SID 

 

//version information (patches)

select * from AD_CHECKS_FILE

 

//autopatch log files

$APPL_TOP/admin/SID/log/adpatch.log

 

//patch information files

$APPL_TOP/admin/SID/log/adpatch.lgi

 

//autopath test mode

adpatch apply=n

 

// merge patch

cd $AD_TOP/bin

./admerge.pl

admrgpch <source_dir> <dest_dir>

 

 

//Rapid Clone 

----------------source location-------------------------------------(/*
PRECLONE STEPS */)

cd $ORACLE_HOME/appsutil/scripts/context_name

./ adpreclone.pl dbTier------------------------db tier

cd $INST_TOP/admin/scripts

./adpreclone.pl appsTier-----------------------application Tier

 

copy all the application tier file system to the target system
(appl_top,common_top,technology stack 10.1.2 ,10.1.3)

----------------target location---------------------------------------(/*
POSTCLONE STEPS */)

cd $ORACLE_HOME/appsutil/clone/bin

./adcfgclone.pl dbTier----------------------------db Tier

cd $COMMON_TOP/clone/bin

./adcfgclone.pl appsTier---------------------------application Tier

 

 

//enable archive log mode

SQL> alter database archivelog;

 

//chmod

chmod -R 777 /u02

 

//chown 

chown -R applprod.dba /u03/oracle...

 

//add datafile

alter tablespace system add datafile
'/u01/oracle/oradata/prodtest1/system02.dbf' size 50G;

 

//add log file to existing group

sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO
GROUP 2;

 

//add log file to the new group

alter database add logfile group 5
('/u01/oracle/oradata/prod/redologt1.dbf','/u01/oracle/oradata/prod/logt2.db
f') size 50m;

 

//control file creation 

"CREATE CONTROLFILE

 SET DATABASE prod

   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',"

   

 // Data Pump

 

 CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS
'/u01/app/oracle/oradata/';---------------------create a directory where the
dump file will be placed

GRANT READ, WRITE ON DIRECTORY test_dir TO scott; 

 

Table Exports/Imports

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log---export 

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log---import

 

(/*  IN THIS CASE THE TARGET DATABASE IS SAME BUT SUPPOSE WE NEED TO
TRANSFER IT TO DIFFERENT DATABASE , WE HAVE TO CREATE A SEPARATE DIRECTORY
THERE AND MOVE THE DUMP FILE TO THAT LOCATION) */

 

Schema Exports/Imports

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp
logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp
logfile=impdpSCOTT.log

 

//kill process

pkill -u applprod

 

//concurrent manager logs//

$FND_TOP/log

 

//port change

OAM ->autoconfig->oa ports

change the values and run autoconfig (with application down)

 

//FNDCPASS

 

SYSTEM ,It is used to change APPS/APPLSYS password

 USER , It is used to change oracle applications user like SYSADMIN

 ORACLE, It is used to change the password of oracle schema like GL,AP

ALLORACLE , It is used to change all passwords of all schema.

                

FNDCPASS apps/APPS_PWD 0 Y system/manager SYSTEM APPLSYS apps

update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE = 'R12  INSTANCE' where PROFILE_OPTION_ID = '125' ;

update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE = 'RED' where
PROFILE_OPTION_ID = '5785' ;

 

/red hat release

vi /etc/redhat-release

 

frd

profile-> ICX forms launcher

 

//last refresh of database created date

cd $INST_TOP/appl/admin

ls -ltr

 

select created, sysdate from v$database;

 

//create user directory

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';

 

//bash profile

export ORACLE_HOME=/u02/oracle/11.2.0

export ORACLE_SID=database2 

export PATH=$PATH:$ORACLE_HOME/bin

 


//environment file

/u01/oracle/TEST/apps/apps_st/appl/APPSTEST_training.env

 

//ad controller

as a applprod user first run the environment file then run adctrl 

 

 

//block change tracking

SELECT filename, status, bytes

FROM v$block_change_tracking;---- status

 

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE 'c:\oracle\product
lash_recovery_area\ORABASE\bctf01.log'; ----- enable

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; --- disable

 

 

// listener.ora tnsname.ora location

cd $ORACLE_HOME/network/admin/sid/

 

//gather schema statistics

run the rquest as 

%GATHER SCHEMA STATISTICS%

 

//multi organization

select multi_org_flag from fnd_product_groups;

 

//top ten process using memory

 

ps aux|head -1;ps aux|sort -m

 

//file system

cat /etc/fstab

 

 

 

//users in linux

cat /etc/passwd

//mount cd writer

 mount /dev/cdwriter /media/

 

 //restart X server from gui

 ctrl+alt+f7

 

 //edit .sh file substitute

 :%s/target1 /target2 /g

 

 //user add

 useradd -g dba oraprod

 //group add

 groupadd dba

 

 alter system switch logfile

archive log list;

export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin

 

 

 \\remove and keep last 5 days log files

 find . -mtime +10  -exec rm -f {} \; -print

 after this alter system switch logfile

 

 \\lock and unlock

 alter table myTable read only;

 alter table myTable read write;

 

revoke the INSERT, UPDATE, DELETE grants from that table.

SQL> revoke UNLIMITED TABLESPACE from KAMRAN;

 

lock table <table_name> in exclusive mode

modes <ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE,
NOWAIT and WAIT>

 

\\DBlink

create database link arobase connect to "mm@oracle" identified by
"mypassword" using 'dg112odbc';

 

CREATE Public Database Link SS64

Connect To scott Identified By demo

Using test;

 

select * from dba_db_links;

 

\\SGA (DDLJ - SR)

select * from v$sgainfo

    Data buffer cache - cache data and index blocks for faster access.

    Shared pool - cache parsed SQL and PL/SQL statements.

    Dictionary Cache - information about data dictionary objects.

    Redo Log Buffer - committed transactions that are not yet written to the
redo log files.

    JAVA pool - caching parsed Java programs.

    Streams pool - cache Oracle Streams objects.

    Large pool - used for backups, UGAs, etc. 

 

                \\audit

                sign-on in oracle ebiz suite

                

sign on audit users

sign on audit forms

sign on audit resposibilities

sign on concuurent

 

performance

\\invalid objects

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

\\recompile

alter procedure procedur_name compile;

alter view view_name compile;

 

dbTechStack

 cd /u03/oracle/PROD/db/tech_st/11.2.0/appsutil/clone/bin/

 

\\unusable indexes

 

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

SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables
with unusable indexes or index partitions.

 

skip unusable indexes

alter system set SKIP_UNUSABLE_INDEXES=FALSE;

 

 

rman target sys/PWD@prod auxiliary sys/PWD@TEST

 

 run

{

DUPLICATE TARGET DATABASE TO 'TEST' FROM ACTIVE DATABASE nofilenamecheck;

}

exit

 

 

 rman target sys/PWD@prod auxiliary  / LOG $outputfile <<EOF

 run

{

DUPLICATE TARGET DATABASE TO 'TESTR' FROM ACTIVE DATABASE;

}

exit

 

clearcache

#!/bin/sh

sync; echo 3 > /proc/sys/vm/drop_caches

 

//DR setup

httphttp://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php

 

 

 

space check

SELECT a.tablespace_name, b.size_mb, a.free_mb, Trunc((a.free_mb/b.size_mb)
* 100) "FREE_%"

FROM   (SELECT tablespace_name, Trunc(Sum(bytes)/(1024*1024)) FREE_mb 

            FROM   dba_free_space  GROUP BY tablespace_name) a, 

        (SELECT tablespace_name,

            Trunc(Sum(bytes)/(1024*1024)) SIZE_mb FROM   dba_data_files
GROUP BY tablespace_name ) b

WHERE  a.tablespace_name = b.tablespace_name

 

//Flash Recovery area

rman backups

control file

logs

                online redo logs

                archived logs

                flashback logs

                

//recycle bin oracle (to recover drop tables)

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge
as "PUR", droptime

  2  from recyclebin 

  //to recover data from recycle bin

  SQL> flashback table tst to before drop;

                Flashback complete.

                

AFTER THIS PURGE TABLE FROM RECYCLE BIN

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

 

 

//clear cache in oracle apps

 

either from functional administrator responsibility (core
services->framework)

or

bring down the apache services

 clearing file $COMMON_TOP/_pages will clear cache and recompile all jsp
files.

 In R12 if you clear cache $COMMON_TOP/_pages then you will have to
recompile the jsp files manually

 

 perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2 

 

 

 // ash (active session history) for last 5 days

 SELECT NVL(a.event, 'ON CPU') AS event,

       COUNT(*) AS total_wait_time

FROM   v$active_session_history a

WHERE  a.sample_time > SYSDATE - 5/(24*60) -- 5 mins

GROUP BY a.event

ORDER BY total_wait_time DESC;

 

 

select * from FND_LOGS; -- details of all application user login as apps

 

///////////////////alter the archive_dest_2 enable and disable

 

ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;

ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;

 

/////node error on oracle apps

 

SQL> conn apps/apps_PWD

Connected.

 

 exec fnd_net_services.remove_system ('DB_NAME');

 

SQL> commit;

 

exec fnd_conc_clone.setup_clean ; 

 

 

$ cd <RDBMS ORACLE_HOME>/appsutil/scripts/<context>

$ ./adautocfg.sh 

 

 

//alert file location

/u03/oracle/TEST/db/tech_st/11.2.0/admin/PROD_appstest1/diag/rdbms/prods/PRO
D/trace

 

select * fron FND_LOGS; -- details of all appl user login as apps

 

///////////////////////DATAGUARD PHYSICAL STANDBY
CONFIGURATION/////////////////////////////

 

http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php

 

 

//AUTOMATIC WORKLOAD REPOSITORY

 

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled
every second.

V$METRIC - Displays metric information.

V$METRICNAME - Displays the metrics associated with each metric group.

V$METRIC_HISTORY - Displays historical metrics.

V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active
session history.

DBA_HIST_BASELINE - Displays baseline information.

DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

DBA_HIST_SNAPSHOT - Displays snapshot information.

DBA_HIST_SQL_PLAN - Displays SQL execution plans.

DBA_HIST_WR_CONTROL - Displays AWR settings

 

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

 

//ADDM : automatic database diagnostic monitor

 

analyzes data in the Automatic Workload Repository (AWR) to identify
potential performance bottlenecks.and we use it for the following cases :

 

         CPU bottlenecks

         Undersized memory structures

         I/O capacity issues

         High load SQL statements

         RAC specific issues

         Database configuration issues

         Also provides recommendations on hardware changes, database
configuration & schema changes.

 

Generate ADDM  :

 

    Login to SQL

    @$ORACLE_HOME/rdbms/admin/addmrpt.sql

 

                enter system password when you asked for .

    Specify a begin_snap from the list and press Enter.

    Specify the end_snap from the list and press Enter.

     Report Name

 

////////////        ASH : Active Session History 

 

statistics from the in-memory performance monitoring tables also used to
track session activity and simplify performance tuning.

 

ASH reports Give the following information :

 

    Top User Events (frequent wait events)

    Details to the wait events

    Top Queries

    Top Sessions

    Top Blocking Sessions

    Top DB Object.

    Activity Over Time

 

 Generate ASH reports :

 

The Best way to do that using OEM.

 

oraInventory

all the oracle software products installed on all ORACLE_HOME

other non - oracle products such as JRE

 

select * from v$standby_log

 

//tape backup commands

 

backup                 tar -czf /dev/st0 *

List         tar -tzf /dev/st0

Erase the tape    mt -f /dev/st0 erase

Rewind Tape     mt -f /dev/st0 rewind

 

 

//logs location

 

$INST_TOP-- apache logs , start and stop, concurrent manager, forms report .
cd $INST_TOP/logs/  appl, ora 

[appltest@TEST PROD]$ cd
inst/apps/TESTR_TEST/logs/appl/admin/log/--------------------------------
----- cd $INST_TOP

 

adalnctl.txt     adcmctl.txt      adoacorectl.txt  adopmnctl.txt
adstrtal.log     jtffmctl.txt

adapcctl.txt     adformsctl.txt   adoafmctl.txt    adstpall.log
javacache.log

[appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/appl/admin/log/

 

[appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/ora/10.1.2
------------- forms reports network

[appltest@TEST PROD]$ cd
inst/apps/TESTR_TEST/logs/ora/10.1.3-----------apache http

 

 

autoconfig

cd $INST_TOP/apps/TESTR/admin/log

 

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

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

 $APPL_TOP

 adpatch logs , adadmin logs

 /u06/oracle/PROD/apps/apps_st/appl/admin/TESTR/log

 adadmin.log  adpatch.log    adwork002.log  adwork004.log  adwork006.log
adwork008.log

adpatch.lgi  adwork001.log  adwork003.log  adwork005.log  adwork007.log

 

____________________________________________________________________________
_________________________________________________________________

 

database autoconfig

 

appsutil/logs

The log file for this session is located at:
/u06/oracle/PROD/db/tech_st/11.2.0/appsutil/log/TESTR_TEST/02120254/adcon
fig.log

 

opatch logs (before applying OPatch , you need to export the path  export
PATH=$PATH:$ORACLE_HOME/OPatch)

cd $ORACLE_HOME/cfgtools/logs

 

database trace file location

/u06/oracle/PROD/db/tech_st/11.2.0/admin/TESTR_TEST/diag/rdbms/testr/TEST
R/trace

 

//.dbc file

/u03/oracle/PROD/inst/apps/PRODR_TEST/appl/fnd/12.0.0/secure

will hold all the database connect parameters

 

 

//adbuild in R12

 

Generating  a new Context file on the database tier

perl $ORACLE_HOME/appsutil/bin/adbldxml.pl template=[contextfile_template]
out=[contextfile_name]

 

(Where [contextfile_template] is the location of the context template
(default is $ORACLE_HOME/appsutil/template/adxdbctx.tmp) and
[contextfile_name] is the absolute Path of the context file to be generated
(the default is $ORACLE_HOME/appsutil/[context_name].xml).)

 

Generating  a new Context file on the Applications - Tier :

run the adclonectx.pl script from.

perl [COMMON_TOP]/clone/bin/adclonectx

 

To retrieve the applications tier context file ( if it is lost or deleted
accidentally) , execute the following command on the applications tier :

perl [COMMON_TOP]/clone/bin/adclonectx.pl retrieve

 

//concurrent enable trace file

profile->system->concurrent:allow debugging

Concurrent: Allow Debuggin

enable trace

 

location trace

SQL> SELECT oracle_process_id

     FROM fnd_concurrent_requests fcr

    WHERE fcr.request_id = &req_id;

 

                //tkprof

                The TKPROF program converts Oracle trace files into a more
readable form

                tkprof orcl102_ora_3064.trc output.prf EXPLAIN=scott/tiger
SYS=NO

                TKPROF <trace-file> <output-file>
explain=user/password@service table=sys.plan_table

                

                

                //concurrent programs running
http://onlineappsdba.com/index.php/2008/11/20/troubleshoot-long-running-conc
urrent-request-in-apps-11ir12/

                 

 
http://onlineappsdba.com/index.php/2008/11/20/troubleshoot-long-running-conc
urrent-request-in-apps-11ir12/

                oradebug setospid spid

                

                //locate trace file

                //locate trace file

                sql>oradebug tracefile_name

                

                

 

//You can see the request id and other relevant information from the result.

 

Based on the SPID associated to each running request, query the v$session or
v$session_longops table to see what is the request id doing in the backend.

 

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state,
c.sql_text,b.logon_time

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = '11696'

ORDER BY a.spid, c.piece

 

(Replace v$session with gv$session if the database is running on RAC
environment. Enable or set trace if you wish to know more details on the
session. 

 

//Concurrent manager status for a given sid?

 

col MODULE for a20

col OSUSER for a10

col USERNAME for a10

set num 10

col MACHINE for a20

set lines 200

col SCHEMANAME for a10

select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username,
s.MACHINE,s.MODULE,

s.SCHEMANAME,

s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid =
'&oracle_sid';

 

 

AD Utilities

AD Administration	adadmin Oracle Application Manager	Performs maintenance tasks on the Oracle Applications file system and database.
AutoPatch	adpatch	Applies patches to the Oracle Applications and Oracle database (also you can add new languages and products as a patch).
AD Controller	adctrl	Which this utility you can restart, see the status of parallel workers in AutoPatch, AD Administration and AutoUpgrade.
AutoUpgrade	adaimgr	Upgrades your database code and objects from an old version to the last 11i version. This is a time consuming step.
AD Relink	adrelink	Used to re-link apps executables with product libraries. This must be done if some library definition is changed or an executable is corrupted.
AD Merge Patch	admrgpch	Used to merge multiple patches into a single one. This is useful when we have to apply many patches to reduce the patching time.
AD Splicer	adsplice	Used to register the off-cycle products (a new oracle released product which is not delivered with the initial package).
File Character Set Converter	adncnv	Used to convert a file from one character set to another.
AD Configuration	$AD_TOP/sql/adutconf.sql	Shows information about the installed configuration of the APPS.
AD Job Timing Report	$AD_TOP/admin/sql/adtimrpt.sql	Shows timing statistics about the parallel workers.
AD File Identification	adident	Shows the header file of one Oracle Application file. In the header we can see the version and the timestamp of the file.
License Manager	Oracle Application Manager	Licenses products, country-specific functionalities or languages.
Patch Wizard	Oracle Application Manager	Determines which patches was applied and which need to be applied.
Applied Patches	Oracle Application Manager	Keeps a history of the patches which have been applied.

FNDLOAD

To download

On source: FNDLOAD apps/xxxxxx O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct GL_SUPERUSER.ldt MENU MENU_NAME="GL_SUPERUSER"


To upload

On Target: FNDLOAD apps/xxxxxx O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct GL_SUPERUSER.ldt MENU MENU_NAME="GL_SUPERUSER"

SQL Queries for Parameter description

SQL> show parameter background_dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u0x/oracle/PROD/db/tech_st/11
                                                 .2.0/admin/XXXX/diag/
                                                 rdbms/XXX/XXX/trace


SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u06/oracle/proddata/cntrl01.d
                                                 bf, /u06/oracle/proddata/cntrl
                                                 02.dbf, /u06/oracle/proddata/c
                                                 ntrl03.dbf
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> show parameter log

will display all the parameters info about logs

SQL> show parameter

will display all the parameters info

How to delete archived log files past one week in RMAN

BACKUP ARCHIVELOG ALL;
DELETE COPY OF ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

LOGS location oracle Ebs

Log file location for Oracle E-Business Suite R12

Below list of  log file locations could be helpful for your issue:

 Apache, OC4J and OPMN:
$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/j2ee
$LOG_HOME/ora/10.1.3/opmn

 Startup/Shutdown Log files:
$INST_TOP/logs/appl/admin/log

 Patch log:
$APPL_TOP/admin/$SID/log/

 Autoconfig log file:

Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/<MMDDHHMM>/adconfig.log
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/<MMDDHHMM>/NetServiceHandler.log

 Concurrent log:
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

 Clone log:
Preclone log files in source instance

Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
Clone log files in target instance

Apps :
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_<time>.log
Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_<time>.log

 Alert Log File:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Reference:
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12 [ID 804603.1]
Oracle Application Server Diagnostic Tools and Log Files in Applications Release 12 [ID 454178.1]
Oracle Applications System Administrator’s Guide

PMON vs SMON

SMON -> performs recovery, when instance fails and,
PMON -> performs recovery, when user process fails.

PMON

• Rolls back the user’s current transaction
• Releases all the locks that are held on tables or rows
• Frees other resources used by the users
• Restarts the dead dispatcher


SMON

• Rolling forward changes that are made in the online redo log files but not in the data files. Since all the committed transactions are written to the online redo log files, these are successfully recovered as result of rolling forward changes from the online redo log files to the data files.
• Opening the database. After the database is opened, users can log on and access any data that is not locked by un-recovered transaction.
• Rolling back all the uncommitted transactions.
 

Performance tuning Concurrent Managers

Factors which will help us in improving performance

Sleep Seconds

Increase the cache size (number of requests cached) to at least twice the number of target processes.

specialized concurrent managers to dedicate certain process

For maximum throughput consider reducing the sleep time of the Conflict Resolution Manager

purge the old concurrent logs ( Purge Concurrent Request FNDCPPUR) $APPLCSF/$APPLLOG

Set profile option 'Concurrent: PCP Instance Check' to 'OFF' if instance-sensitive failover is not required

Tuning Output Post Processor (OPP) NOTE:1399454.1 Tuning Output Post Processor (OPP) to Improve Performance     

Issues Faced

Responsibility not reflecting after changes are done to the user


Run this request after you make the changes.

Workflow Directory Services User/Role Validation

Performance Tuning AWR ADDM

AWR contains the  vital elapsed-time information on what happened during particular snapshot range.

Main sections of AWR report:

Report Summary
Cache Sizes
Load Profile
Instance Efficiency Percentages
Shared Pool Statistics
Top 5 Timed Events
Wait Events
Time Model Statistics
Operating System Statistics
Service Statistics
SQL Section
I/O Section
Buffer Wait Statistics            
SGA Memory Summary 

The script you can see at 

SQL> $ORACLE_HOME/rdbms/admin/awrrpt.sql


ADDM

ADDM analyzes the AWR report and generate the report and further action to be taken.

The types of problems that ADDM considers

CPU bottlenecks
Undersized Memory Structures
I/O capacity issues
High load SQL statements  
High load PL/SQL 
Database configuration issues 
Concurrency issues
Hot objects and top SQL

It also shows the information on:

• Hardware changes - Adding CPUs or changing the I/O subsystem configuration
  
• Database configuration - Changing initialization parameter settings
  
• Schema changes - Hash partitioning a table or index, or using automatic segment-space management (ASSM)
  
• Application changes - Using the cache option for sequences or using bind variables
  
• Using other advisors - Running the SQL Tuning Advisor on high load SQL or running the Segment Advisor on hot objects
  

 

Responsiblity R12

For Oracle Responsibility, means set of privileges assigned to user.
Responsibilities are generally defined by System Administrator.

For defining the responsibility, you need set of components described below:

1. Data Group
It defines the mapping between Oracle Application and oracle username assigned to it.Each application in data group has a oracle user assigned to it
For defining the data group, the following details should be assigned.

->Name
->Application
->Menu
->Request Group

2.Menu
It's a hierarchical arrangement of application forms that displays in the navigation.It defines the range of application functionality available for a responsibility.

3.Application
Name of the application to which responsibility is given access to.

4. Responsibility key
Internal key for the responsibility

5. Responsibility name 
Name of the responsibility

To define a new responsibility:

System Administrator >> Security >> Responsibility >> Define

Refer metalink for detailed explanation 1508400.1

Program was terminated by signal 25 concurrent manager

This issue occurs when your reports.log in $APPLCSF/$APPLLOG  exceeds the size by more than 2GB.

To check the size

$ cd $APPLCSF/$APPLLOG
$  ls -l reports.log

Solution

Backup your reports.log file rename it to reports.log.bk
Create a new reports.log file

$ touch reports.log

RMAN Duplicate Database

RMAN duplicate database is a new feature added to 11g database.
With this feature you can duplicate your target database without bringing down any services on it.

This feature is basically used for creating up the Standby Databases.



rman target sys/XXX@SID auxiliary sys/XXX@SID

 run
{
DUPLICATE TARGET DATABASE TO 'XXXX' FROM ACTIVE DATABASE nofilenamecheck;
}
exit

OPATCH Utility (Database Patch)

Opatch is a patching utility that ensures your Oracle software is into healthy and secure condition.

The below image will help you in understanding the workflow for applying the database patch.





OPATCH options

    opatch -help
   opatch apply -help
   opatch compare 
   opatch lsinventory -help
   opatch lspatches -help
   opatch napply -help
   opatch nrollback -help
   opatch rollback -help
   opatch prereq -help
   opatch util -help

Application Patching (ADPATCH)

Patch is a program to fix a particular problem or enhance/add a particular feature in existing environment.

To apply the patch, download the patch from metalink with specific OS versions.

Unzip the patch and read the pre-requisite.
vi README.txt

To check whether the patch is already applied.

*sqlplus apps/<apps password>@<tnsalias>   then
select * from AD_BUGS where bug_number=’<patch number>’


Maintenance Mode is mode of operation in which the oracle application system is ready for patching activities. It improves performance and availability by minimizing downtime.

Run the 'adadmin' from APPL_TOP
 
1.Generate applications file menu.
2.Maintain applications file menu.
3.Compile/Reload Applications Database Entities Menu.
4.Maintain Applications Database Entities Menu.
5.Change Maintenance Mode.
6.Exit ad Administration

Select the option '5' to change the maintenance mode.
1.Enable Maintenance mode.
2.Disable Maintenance mode.
3.Return to Main Menu.


If you wish to apply patch without putting applications in maintenance mode use options=hotpatch with adpatch.

adpatch options=hotpatch

RMAN BACKUP INCREMENTAL LEVEL 1

RMAN BACKUP INCREMENTAL LEVEL 1

Rman target / catalog rman/rman@rman(running it from the production, and the catalog is in backup server(10.0.xx.x and SID of rman is rman ))

RMAN> #

2> connect catalog *

3> connect target *

4>

5> set snapshot controlfile name to '/tmp/PROD_snapshot.ctl';

6>

7>

8> CONFIGURE DEVICE TYPE disk PARALLELISM 1;

9> CONFIGURE CONTROLFILE AUTOBACKUP ON;

10> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rmanbackup/PROD/1_xxxxxx/CTRL_BK_%F';

11>

12> run

13> {

14> allocate channel ch1 type disk;

15> allocate channel ch2 type disk;

16> backup incremental level 1 format '/rmanbackup/PROD/1_xxxxxxxx/DB_FULL_1_%U_%T' database ;

17> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

18> backup incremental level 1 format '/rmanbackup/PROD/1_xxxxxxxx/ARC_FULL_1_%U_%T' archivelog all;

19> release channel ch1;

20> release channel ch2;

21> }

22> crosscheck backup;

23> exit

cloning

Rapid Clone
Cloning an Oracle E-Business Suite Release 12 system can be accomplished by running the Rapid Clone tool. This tool can be employed with Oracle E-Business Suite Release 12, or any AutoConfig-enabled earlier releases.

(/* PRECLONE STEPS */)
----------------source location-------------------------------------
cd $ORACLE_HOME/appsutil/scripts/context_name
./ adpreclone.pl dbTier      -----------------------db tier

cd $INST_TOP/admin/scripts
./adpreclone.pl appsTier   ---------------------application Tier

copy all the application tier file system to the target system (appl_top,common_top,technology stack 10.1.2 ,10.1.3)



(/* POSTCLONE STEPS */)
cd $ORACLE_HOME/appsutil/clone/bin
./adcfgclone.pl dbTier        ----------------------------db Tier
cd $COMMON_TOP/clone/bin
./adcfgclone.pl appsTier     ---------------------------application Tier

Scripts For DBA

RMAN FILE LOCATION---------------------------------------------------------
show parameter recover;

database version---------------------------------------------------------
    select * from v$version;

tablespace names-----------------------------------------------------------
    select * from dba_tablespace

account unlock-----------------------------------------------------------------------------
    alter user user_name account unlock;
    alter user apps account unlock;

apache restart-------------------------------------------------------------------------------
    cd /usr/local/apache-tomcat/bin/
    ./startup.sh

NLS parameters

SELECT * FROM nls_session_parameters;
Instance parameters (change with ALTER SYSTEM):
SELECT * FROM nls_instance_parameters;
Database parameters (set during database creation):
SELECT * FROM nls_database_parameters;

check patch version 
adident Header (name of patch)

 Permission

chmod
chmod -R 777 /u02

user add
useradd -g dba oraprod

groupadd dba

chown
chown -R applprod.dba /u03/oracle...

Adding up datafile

select * from v$tablespace;
select * from v$datafile;
add datafile
alter tablespace tablespace_name add datafile '/u01/oracle/oradata/prodtest1/system02.dbf' size 50G;

add log file to existing group
sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2;

add log file to the new group
alter database add logfile group 5 ('/u01/oracle/oradata/prod/redologt1.dbf','/u01/oracle/oradata/prod/logt2.dbf') size 50m;

 control file creation
"CREATE CONTROLFILE
 SET DATABASE prod
   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',"


archive log find old files

find . -mtime +5  -exec rm -f {} \; -print         - delete 

find . -mtime +5  {} \; -print
find /u02/oracle/archive/ -mtime +5;

About Me

Hello Everyone,

I Aman Singh graduated as a Computer Science Engineer.
Certified OCA 10g.

Working as Oracle Apps DBA.