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';
