http://www.oracledba.org/EBS/install/Install_EBS_R12_on_linux.htm
file count: ls -1 | wc -l
https://community.oracle.com/thread/4027028?customTheme=otn
adcmctl.sh start apps/<psswd>
You stop the concurrent managers from the command line:
adcmctl.sh stop apps/<psswd>
Location where you find adcmctl.sh scripts is $COMMN_TOP/admin/scripts/$CONTEXT_NAME/
ps -ef|grep FNDLIBR|grep applmgr
https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=654&get_params=p_id:50&p_org_id=1080544&lang=US#tabs-1
find / -type f -name '.pls' -mtime -365 -ls
when last run this cm purge program
http://dbatalent.com/kbarticles/hot-cloning-oracle-e-business-suite-r12/
AUDIT
http://docs.oracle.com/cd/B10500_01/server.920/a96521/audit.htm#1975
Daily Check Report
http://shahiddba.blogspot.in/2012/05/dba-dailyweeklymonthly-or-quarterly.html
tar -czvf archive.tar.gz stuff
tar -xzvf archive.tar.gz -C /tmp
http://oracleajidba.blogspot.in/2010/05/how-to-clear-all-cache-using-functional.htm
shortcut key
http://appsr12help.blogspot.in/2012/12/oracle-applications-shortcut-keys.html
http://docs.oracle.com/cd/E11882_01/nav/portal_4.htm
Workflow
alter tablespace APPS_TS_TX_DATA add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_data219.dbf' size 9000m;
alter tablespace APPS_TS_TX_IDX add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_ind135.dbf' size 7000m;
alter tablespace APPS_TS_MEDIA add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_media088.dbf' size 3000m;
alter tablespace SYSTEM add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/system013.dbf ' size 5000m;
ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/oradb/oracle/product/TJTG/db/apps_st/data/undo142.dbf' size 4000m;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/oradb_temp/oradata/PROD/temp22.dbf' SIZE 4000m;
**********************************************************
select file_name, tablespace_name from dba_data_files where tablespace_name ='&a';
**********************************************************
init 0-shutdown
Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.
collect all java log file
http://haithanh8x.blogspot.in/2013/03/script-dba_22.html
https://dbasolutions.wikispaces.com/Queries
Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite (Doc ID 1057802.1)
SR 3-14640925181
Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite (Doc ID 1057802.1)
Doc ID 2087545.1
ps -fu applmgr|grep -i FND|wc -l
anydesk
update fnd_user set END_DATE='' where user_name like '%';
How To Restart The Concurrent Manager In Unix (Doc ID 147449.1)
free && sync && echo 3 > /proc/sys/vm/drop_caches && free
To free pagecache:
# echo 1 > /proc/sys/vm/drop_caches
To free dentries and inodes:
# echo 2 > /proc/sys/vm/drop_caches
To free pagecache, dentries and inodes:
# echo 3 > /proc/sys/vm/drop_caches
If you want to clear out your swap you can use the following commands.
free
Disable swapp:swapoff -a
confirm :free
Enable swap:swapon -a
Purge Logs and Closed System Alerts
update
fnd_concurrent_requests
set
phase_code = 'C',
status_code = 'E'
where
request_id ='11637530';
1st run cmclean
if cm is down (no manager)
then stop/start ./adcmctl.sh stop/start
Test.
SELECT logfile_name, outfile_name
FROM fnd_concurrent_requests
WHERE request_id = '11128248'
frmcmp_batch userid=apps/APPS module=STGI_DISPATCH.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/STGI_DISPATCH.fmx module_type=form batch=no compile_all=yes
------------------------------------------------------------------------------------------------
STICK NOTE 2
Down the Application
Run apps enviorment
Connect to sqlplus
Sql>@cmclean
Type dual to Continue
1.user deleted(Except sysadmin siva):disable user
2.shutdown email notification
3.concurrent manager data
4.Disable the schedule jobs in new clone
health check
1.Checklist workflow
2.concurrent managr
3.backup
4.rman
5.All Application/db
6.ticket logged
7.bank service
----
mail -v aminali@gmail.in
Enter Subject
Press .
----
http://oracleapplicationsfusiondba.blogspot.in/2015/05/steps-to-createmanage-pgpgpg-keys-in.html
http://docs.oracle.com/cd/E39820_01/doc.11121/gateway_docs/content/general_certificates.html
Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery
https://oracledbamasters.wordpress.com
https://oracledbamasters.wordpress.com/category/oracle-apps-dba/
Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) ( Doc ID 104282.1
logfile size
/oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/opmn/oacore_default_group_1
JAINTDSO.fmx
JAINIDMP.fmx
Severity 2 SR 3-14713151301 : Forms JAINTDSO.fmx and JAINIDMP.fmx are missing
frm-40010 cannot read form fmx
tAX SUPER USER STGI>iNDIA LOCALISATION>others>Generate TDS certificate>India localisation>setup>others>TDS Rounding Setup
SR 3-14832552571
EBS Login Page Error
Doc ID 2267420.1--PATCH GST PHASE 3
-------
tail -200f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err
tail -200f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/Apache/access_log.1495756800
tail -200f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/opmn/HTTP_Server~1.log
tail -2000f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/appl/rgf/javacache.log
tail -200f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
tail -200f /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.out
tail -200f /orahome/oracle/product/TJTG/db/adr/diag/rdbms/tjtg/TJTG/trace/alert_TJTG.log
http://www.appsdba.info/index.php?module=pagemaster&PAGE_user_op=view_page&PAGE_id=27&MMN_position=22:22
------------------------------------------------------------------------------------------------
STICK NOTE 3
select file_name,AUTOEXTENSIBLE,status from dba_data_files;
http://dba-tips.blogspot.in/2014/02/oracle-database-administration-scripts.html
http://h2hdba.blogspot.in/p/dba-shell-scripts.html
work in process>opeartion>
check log file
all cm query
http://utopiaaan.blogspot.in/2014/02/long-running-concurrent-requests.html
java apache core
oacore
oafm
for gst which patch are need to apply
SR 3-14678109331
http://appsdbaworkshop.blogspot.in/2013/02/oracle-apps-r12-cloning-online.html
dba bundle
http://dba-tips.blogspot.in/2014/02/oracle-database-administration-scripts.html
Todays Task: Write.sh script for fetching space details
http://blogs.oracle.com/EBS/
tar -cvzf MyImages-14-09-12.tar.gz /home/MyImages
tar -xvf public_html-14-09-12.tar -C /home/public_html/videos/
$APPL_TOP/admin/$SID/log/
1z0-215 AP
1Z0-216 AP
u12316543.drv
set the script alert
citi doc
patch doc-2
V6R2015X
cmclean
Purge Concurrent Request and/or Manager Data
patch
select OBJECT_NAME,CREATED,LAST_DDL_TIME from dba_objects where status='INVALID'
http://www.oracle-dba-online.com/
analyze all script
SR 3-14782501851-oaf error
Concurrent Program Details Report - XML Publisher
E-Business Suite Support Analyzer Bundle Menu Tool (Doc ID 1939637.1)
https://community.oracle.com/message/13294220#13294220
/home/uatdb/dbscripts/MENU
Database Initialization Parameters for Oracle E-Business Suite Release 12(Doc ID 396009.1)
Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite (Doc ID 1057802.1)
Click on package/table/ select error tab;
ctrl-g:Findout eror details with line no;
select USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff,action_name
from dba_audit_session
WHERE timestamp>='01-APR-2017' and timestamp<='30-APR-2017'
order by logon_time,username,timestamp,logoff_time
select count(*) from dba_audit_session WHERE timestamp>='01-MAR-2017' and timestamp<='31-MAR-2017';1066769
select * from tjtghrp.emp_detail_view;
------------
Note: 166650.1 : Working Effectively With Support
(FNDCPPUR) (Doc ID 104282.1)
May Be Purge Regularly Manually? (Doc ID 2005894.1)
purge program
refresh db
patches for st
Severity 3 SR 3-14499658341 : OFI-GST Phase- II : EAP Control SR
inactive manager
https://community.oracle.com/thread/4036035
--------------
1.DBA support for Implementation of GST project
2.DBA support for Implementation of DR system
3.Implement additional controls / processes for security in EBS / DB servers
4.Create new process document for meet the system audit report
5.Extend DBA support for Enovia project
--------------------------------
appltmp
inst_top
zip -r appstack.zip apps
----------------
or to zip a directory
zip -r squash.zip dir1
To uncompress:
unzip squash.zip
--------------
scp -r * root@172.16.2.28:/GSTBKP/apps8099
Purging Strategy for E-Business Suite (Doc ID 732713.1)
Concurrent Request "Purge Obsolete Generic File Manager Data" Methods (Doc ID 1165208.1)
/applcsf/TJTG/log
Reports:du -sh reports.log
https://anandoracle.wordpress.com/2012/03/20/cloning-r12-oracle-apps-e-business-suite-pre-clone-steps/
oracle sr call
040-66052080
http://www.petefinnigan.com/orasec.htm
Oracle Financials for India GST Diagnostics (Doc ID 2254221.1)
history -c
USER ADDITION:
SELECT USER_NAME, START_DATE, END_DATE, DESCRIPTION,
(SELECT FULL_NAME FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) EMPLOYEE_NAME,
(SELECT ATTRIBUTE1 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) COST_CENTER,
(SELECT ATTRIBUTE2 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) LOC,
(SELECT ATTRIBUTE3 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) DEPT,
(SELECT ATTRIBUTE4 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) JOINING_DATE,
(SELECT ATTRIBUTE5 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) RESIGNED_DATE,
(SELECT DESCRIPTION FROM FND_USER WHERE USER_ID = A.CREATED_BY)CREATED_BY FROM FND_USER A WHERE START_DATE >='01-Apr-2017'
ORDER BY START_DATE
USER DELETION:
SELECT USER_NAME, START_DATE, END_DATE, DESCRIPTION,
(SELECT FULL_NAME FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) EMPLOYEE_NAME,
(SELECT ATTRIBUTE1 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) COST_CENTER,
(SELECT ATTRIBUTE2 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) LOC,
(SELECT ATTRIBUTE3 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) DEPT,
(SELECT ATTRIBUTE4 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) JOINING_DATE,
(SELECT ATTRIBUTE5 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) RESIGNED_DATE
--(SELECT DESCRIPTION FROM FND_USER WHERE USER_ID = A.LAST_UPDATED_BY)LAST_UPDATED_BY
FROM FND_USER A WHERE END_DATE >='01-Apr-2017'
ORDER BY END_DATE;
/GSTDB/orahris/archivelogs
recover database until time '2018-03-28:11:00:12';
restore controlfile from '/GSTDB/orahris/bkphris/rman/TJTGHRP_Control_file971939846_16677_1';
find / -type f -name '.pls' -mtime -365 -ls
Find the File According to size:
ls -lSr
--------------------------------------------------------------------------------------------------------------------------------
/home/appluat/Optic
http://chennaioracledbatraining.in/
Use below query to cancel all scheduled concurrent programs.
===================================================
UPDATE fnd_concurrent_requests
SET phase_code = 'C',
status_code = 'X'
WHERE status_code IN ('Q', 'I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';
2.
TO cancel all running concurrent programs.
UPDATE fnd_concurrent_requests
SET phase_code = 'C',
status_code = 'X'
WHERE status_code IN ('R', 'I');
http://dbahut.blogspot.in/2013/04/cancelling-concurrent-request-from.html#!/2013/04/cancelling-concurrent-request-from.html
https://knoworacle.wordpress.com/2010/05/28/oracle-ebs-changing-main-forms-heading-site-name-profile-option/
3.
disable users
select * from fnd_user;
select * from fnd_user where user_name IN ('');
update fnd_user set END_DATE='10-feb-2018' where user_name like '%T';
update fnd_user set END_DATE='' where user_name IN ('');
3109 --
STGI_AP_PAYMENT_RECONCILIATION
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/reports/US
===========================================================================================
select * from ad_applied_patches where PATCH_NAME='27347602';
to upgrade the file : jai_tax_proc_ap.plb to version :120.0.12010000.71
BEFORE:
$Header jai_tax_proc_ap.plb 120.0.12010000.64 2017/12/12 07:45:41 dejliu noship $
AFTER:
===========================================================================================
Patch 27483028:R12.JAI.B
Before:
JAI_PA_COSTING.plb 120.4.12010000.37
After:
===========================================================================================
/home/applprod/Optic
===========================================================================================
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
select * from DBA_PRIV_AUDIT_OPTS;
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
select * from fnd_oracle_userid;
select * from apps.aud_bkp_20sept17;
select distinct(CLIENT_ID) from dba_audit_object;
702
P1503520111 _CE_ 1201 0D
Pls. add the kouban P1503520111 _CE_ 1201 0D in the common lookup XXTJ_IEXPENSE_PROJECT_LOV in the System Administrator.
This kouban is used for iExpense claims. Pls. do the needful. It’s a bit urgent.
======================
GL.sql
TB.sql
user.sql
COA.sql
GL_Sources.sql
GL_Manual_Subledger1.sql
GL_Auto_Altered.sql
Migration Steps
1. Go to $JAVA_TOP/xxtjpa/oracle/apps/pa/ and unzip the nnr.zip file
2. Run the below script (Change the production credentials)
3. Bounce OACORE and APPACHE Server.(After Month End Closure)
java \
oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxtjpa/oracle/apps/pa/nnr/webui/ \
-rootdir $JAVA_TOP/ \
-jdk13 \
-username apps \
-password apps \
-mmddir $OA_HTML/jrad \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.2.65)(PORT=1528))(CONNECT_DATA=(INSTANCE_NAME=UAT)(SERVICE_NAME=UAT)))"
/TESTAPPS/app/appluat/apps/apps_st/comn/java/classes/xxtjpa/oracle/apps/pa
========================================================
15-Feb-2018
/TESTAPPS/app/appluat/apps/apps_st/appl/admin/UAT/log/
=========================================================
19-Feb-2018
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY <PWD>
USING 'ASCPPROD';
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.81:1531/ASCPTST';
//172.16.2.81:1531/ASCPTST
select * from sys.dba_db_links@EBS_TO_ASCP.JSW_IN;
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/reports/US
APPS_TS_TX_IDX
alter tablespace APPS_TS_TX_IDX add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_ind065.dbf' size 7000m;
/oradb/oracle/product/TJTG/db/apps_st/data/
stat -c %w filename
stat -c %y STGI_GATEPASS_REPORT.rdf
%U user name of owner
%w time of file birth, human-readable; - if unknown
%W time of file birth, seconds since Epoch; 0 if unknown
%x time of last access, human-readable
%X time of last access, seconds since Epoch
%y time of last modification, human-readable
%Y time of last modification, seconds since Epoch
%z time of last change, human-readable
%Z time of last change, seconds since Epoch
stat -c %w STGI_GATEPASS_REPORT.rdf
stat -c %W STGI_GATEPASS_REPORT.rdf
stat -c %X STGI_GATEPASS_REPORT.rdf
stat -c %x STGI_GATEPASS_REPORT.rdf--Find time creation date.
stat -c %Y STGI_GATEPASS_REPORT.rdf
stat -c %y STGI_GATEPASS_REPORT.rdf
stat -c %y STGI_GATEPASS_REPORT.rdf
/etc/crontab
/etc/cron.*/*
/var/spool/cron/*
/etc/passwd
/etc/group
SELECT * FROM SYS.DBA_USERS;
===================================
------------------------------------------------------------------
select * from hr_employees
where 1 =1 -- employee_num = '15'
and attribute1 = 360124
select aa.SUBJECT,aa.CC_RECIPIENTS,
aa.* from ALR_ACTIONS_V aa
where aa.SUBJECT like '%PRODUCTION%'
and aa.ALERT_ID in (100022,100024,101028,109029);
select * from ALR_ALERTS
where ALERT_ID in (100022,100024,101028,109029)
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%ITEM%'
TJPS_ITEM_RECOVERABLE_UPDATE
SELECT * FROM jai_item_templ_dtls WHERE TEMPLATE_HDR_ID IN
(SELECT TEMPLATE_HDR_ID FROM JAI_ITEM_TEMPL_HDR WHERE INVENTORY_ITEM_ID IN
(SELECT ITEM_ID FROM PO_LINES_ALL WHERE PO_HEADER_ID IN
(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 = '40854')))
AND ATTRIBUTE_CODE = 'RECOVERABLE' AND ATTRIBUTE_VALUE = 'N'
select * from hr_employees
where 1 =1 -- employee_num = ''
and attribute1 = 360124
---------------------------------------
20-Feb-2018
FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN <New Password>
Changing password for sysadmin user
FNDCPASS apps/apps 0 Y system/tjpsebs11 USER SYSADMIN toshiba20
changing password for user
FNDCPASS apps/apps 0 Y system/tjpsebs11 USER 171092tj toshiba17
http://expertoracle.com/2014/04/19/changing-oracle-applications-users-password/
(a) DB: email communication which was communicated to your team for deactivating the DB users except for the current 11 DB users.
b) DB : List of all DB users along with created and deactivated date ( As of today)
(c) EBS: List of all EBS users with last modified date ( also provide the query screenshot)
(d) Normal User access review sample ( Please share the 2nd and 3rd quarter review evidences)
(e) Super user activity review ( here we have only one user - DBA). This users activities have to be pulled from 1st april 2017 to 31-Mar-2018 and have to perform the review on the activities performed by DBA. ----> As discussed this is your annual review and will be performed in April by your team.
(f) Point (e) will be shared by moses Sir post the review is completed and reviewed by the authorized individual.
(g) Please provide the EBS user listing along with the responsibilities of each users.
(h) 11 DB users password # values ( also provide the query screenshot).
===============
21-Feb-2018
http://oracle11ggotchas.com/articles/UsingRMANtorestoreadatabasetoanotherserverinanASMenvironment.htm
http://www.online-database.eu/index.php/recovery-manager-rman/83-clone-database-with-rman-on-another-host-option-3
===============================================
Please execute this below query regularly up to fixing the data.
UPDATE XXTJPS_FSS_DETAILS
SET START_DATE = NULL
, QCS_CHARGE_BY= NULL
, QCS_CHARGE_DATE= NULL
, QCS_CHECK_BY= NULL
, QCS_CHECK_DATE= NULL
WHERE STATUS IN ('0','1') AND START_DATE IS NOT NULL;
====================================
ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;
=====================
Fri Mar 02 08:31:24 2018
Archived Log entry 96835 added for thread 1 sequence 97071 ID 0x80704c1 dest 1:
check current log Sequence:
select max(sequence#) from v$archived_log where applied='YES';
col NAME for a25
col RESETLOGS_CHANGE# for a9
select RECID,NAME,SEQUENCE#,RESETLOGS_CHANGE#,ARCHIVED,APPLIED,STATUS from v$archived_log;
select SEQUENCE#,APPLIED from v$archived_log where NAME='/TJTGHRP/app/otjtghrp/archivelogs/1_96938_906911169.dbf';
select MIN(SEQUENCE#),MAX(SEQUENCE#) from v$archived_log where NAME='/TJTGHRP/app/otjtghrp/archivelogs/1_96938_906911169.dbf';
MIN(SEQUENCE#) MAX(SEQUENCE#)
-------------- --------------
96938 96938
col NAME for a20
select RECID,NAME,SEQUENCE# from v$archived_log;
select RECID,NAME,SEQUENCE# from v$archived_log where NAME='/TJTGHRP/app/otjtghrp/archivelogs/1_96938_906911169.dbf';
TO CHECK APPLIED ARCHIVE LOGS:
select max(sequence#) from v$archived_log where applied='YES';
-----------------------------------------------------------------
IN OARHRIS SERVER , APPLIED LOG TILL THE POINT:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
96969
IN HRIS PROD SERVER , APPLIED LOG TILL THE POINT:
MAX(SEQUENCE#)
--------------
97076
96969
---------------
00107 difference
spool Log_Sequence.txt
col NAME for a55
select NAME,SEQUENCE# from v$archived_log where SEQUENCE# >96969;
spool off;
--------------------------------
select * from v$log;
select sequence# from v$archived_log;
RMAN>RESTORE DATABASE PREVIEW
-------------------------------
cd /TJTGHRP/app/otjtghrp/archivelogs
scp -r * orahris@172.16.2.65:/GSTDB/orahris/archivelogs
96740
restore archivelog Sequence#:
run {
allocate channel demo_t1 type disk;
restore archivelog from sequence 96970 until sequence 97076 thread 1;
}
run {
allocate channel demo_t1 type disk;
restore archivelog from sequence 96970 until cancel sequence thread 1;
}
restore archivelog logseq 96969 thread 1;
RMAN-20242: specification does not match any archived log in the repository
catalog start with '/GSTDB/orahris/archivelogs/';
SELECT * FROM V$RECOVERY_FILE_DEST;
report need backup;
backup archivelog sequence between 96969 and 97076;
catalog start with '/GSTDB/orahris/bkphris/rman';
======================================
STORE DATA TILL MARCH1
https://taliphakanozturken.wordpress.com/tag/rman/
A. Time-Based incomplete recovery;
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
catalog start with '/GSTDB/orahris/bkphris/rman/';
or
catalog backuppiece '/GSTDB/orahris/bkphris/TJTGHRP_Control_file969693078_16088_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_Arch_Log969692952_16087_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_Arch_Log969692816_16084_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_Arch_Log969692816_16086_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_Arch_Log969692816_16085_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_Arch_Log969692816_16083_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_969692405_16078_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_969692405_16079_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_969692405_16080_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_969692407_16082_1';
catalog backuppiece '/GSTDB/orahris/bkphris/rman/TJTGHRP_969692405_16081_1';
RMAN>restore database until time "to_date('01/03/18 18:0:00','DD/MM/YY HH24:MI:SS')";
RMAN>recover database until time "to_date('01/03/18 18:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
E:\Neelan\2018\Mar\2
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/bin
=====================================
5-FEB-2018
(select 'set newname for datafile '||FILE_ID||' to '''||file_name||''';'from dba_data_files );
=======================================
06-Mar-2018
https://docs.oracle.com/html/E10643_07/rcmcomma005.htm
http://zahidrahimocp.blogspot.in/2015/05/upgrade-from-oracle-ebs-release-122-to.html
UPGRADATION RELEASE:
https://docs.oracle.com/cd/E18727_01/nav/technology.htm
CLONING:
Cloning Oracle Applications Release 12 with Rapid Clone (Doc ID 406982.1)
https://windows7bugs.wordpress.com/2013/10/08/oracle-r12-vision-instance-12-1-3-cloning-step-by-step-instructions/
========================================
FSS QCS:
"/home/winscp/Production_Code_07_Mar_2018
ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/oradb/oracle/product/TJTG/db/apps_st/data/undo128.dbf' size 10000m;
===========================
12-Mar-2018
To implement the solution, please execute the following steps:
1. Apply Patch 9769965: SCRIPT ALLOWS CUSTOMER TO SELECT OPTION THAT CORRUPTS FLEXFIELD DATA
2. Run the script afchrchk.sql
3. Retest the issue.
4. Migrate the solution as appropriate to other environments.
How To Run afchrchk.sql Script To Resolve FRM-40654 Error (Doc ID 960115.1)
For 12.1, Patch 10430288 R12.FND.B is available. It brings file => afchrchk.sql Version: 120.3.12010000.1
WIP_OPERATIONS
$FND_TOP/sql directory
/oraapps/oracle/product/TJTG/apps/apps_st/appl/fnd/12.0.0/sql
JzavetOVDr6LKua
sqlplus apps
afchrchk.sql
Enter value for 1: WIP_OPERATIONS
Column name (blank for all):
Enter value for 2:
Check for newline characters (Y/N)?
Enter value for 3: N
Automatically fix all errors found (Y/N)?
Enter value for 4: Y
directory pointed by DB parameter utl_file_dir.
Log file location?
Enter value for 5:
Log file Name?
Enter value for 6:
3449
Space Error:
u9769965.drv
/home/appldev/patch8006/9769965
adadmin_emode_6Mar18.log
adpatch_9769965_5Mar18.log
adadmin_dmode_6Mar18.log
/home/applprod/patch8000
/oraapps/Patches
/oraapps/Patches/9769965
p9769965_R12.FND.B_R12_GENERIC
u9769965.drv
adadmin_emode_12Mar18.log
adpatch_9769965_12Mar18.log
adadmin_dmode_12Mar18.log
AutoPatch may have written informational messages to the file
/oraapps/oracle/product/TJTG/apps/apps_st/appl/admin/TJTG/log/adpatch_9769965_12 Mar18.lgi
Errors and warnings are listed in the log file
/oraapps/oracle/product/TJTG/apps/apps_st/appl/admin/TJTG/log/adpatch_9769965_12 Mar18.log
/oraapps/oracle/product/TJTG/apps/apps_st/comn/java/classes/xxtjpa/oracle/apps/pa
java \
oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxtjpa/oracle/apps/pa/comm/webui/ \
-rootdir $JAVA_TOP/ \
-jdk13 \
-username apps \
-password apps \
-mmddir $OA_HTML/jrad \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.2.32)(PORT=1521))(CONNECT_DATA=(INSTANCE_NAME=TJTG)(SERVICE_NAME=TJTG)))"
SR 3-16456836231 : ERV has hit rounding instead of Realized Gain/Loss Account at the time application of prepayment
===============================================================
###############################################################
ORA-00020: maximum number of processes (200) exceeded
alter system set processes = 350 scope=both
https://minimalistic-oracle.blogspot.in/2014/06/how-to-deal-with-ora-00020-maximum.html
==============================================
http://nimishgarg.blogspot.in/2012/05/ora-00020-maximum-number-of-processes.html
sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown abort
sql> startup
Active call for process 15421 user 'oradev' program
oradev 15421 1 98 07:00 ? 01:59:23 oracleDEV (LOCAL=NO)
2.
how to increase sga memory in oracle
SHOW parameter sga
SHOW parameter spfile
CREATE pfile FROM spfile;
SQL> ALTER system SET sga_max_size=2048M scope=spfile;
ALTER SYSTEM SET sga_target=2048m SCOPE=spfile;
SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
SQL> startup
ORACLE https://dba.stackexchange.com/questions/148907/how-to-increase-the-memory-for-a-oracle-databaseinstance shut down.
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/XXTJSOAP.WFT
WFLOAD apps/<password> 0 Y UPGRADE XXTJSOAP.wft
WFLOAD apps/J 0 Y UPGRADE XXTJSOAP.wft
Log filename : L4099029.log
Report filename : O4099029.out
140816TJ
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ILGSTAZ.ldt CUSTOM_MODE=FORCE
/oraapps/shell_scripts/
alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
system admini>workflow>
workflow type:TJPS-EAM APPROVER WF
Type Internal Name:XEAM_APR
http://oracleappsdna.com/2012/05/wfloadhow-to-download-and-upload-a-workflow/
/home/qcs/scripts/webcontent/knn09/WEB-INF/classes/jp/co/toshiba/keihin/knn09/biz/jasperreports
/home/qcs/apache-tomcat-5.5.28/logs
SELECT * FROM XXSCB_FILE_DETAILS
WHERE 1 =1 --STATUS_REMARKS like '%%'
and PAYMENT_STATUS = 'Rejected'
SELECT PAYMENT_STATUS_CODE,STATUS_REMARKS
FROM XXSCB_FILE_DETAILS
GROUP BY PAYMENT_STATUS_CODE,STATUS_REMARKS
/GSTAPP/app/appltest/apps/apps_st/custom/TJTG/12.0.0/reports/US
2342288.1 India GST - Latest Patches Released (RUP 11 onwards) Modified 03/22/2018
2378283.1 GST: Patch 27552315 failing with Error during application
===============================
27-Mar-2018
EAM - Demo documents
Ticket No:2971
/home/applprod/Migration_plus_Deliverables_EAM_Workflow/EAM_DELIVERABLES
FNDLOAD apps/Jza O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXTJPS_ASSET_NUMBER.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
Log filename : L4195978.log
Report filename : O4195978.out
FNDLOAD apps/Jz 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXTJPSEAMWFLC_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
L4195989.log
O4195989.out
WFLOAD apps/JzavetOVDr6LKua 0 Y UPGRADE XEAM_APR.wft
Log filename : L4196026.log
-------------------------------
Oracle SR#:
3-17059267131
SR 3-17059267131 : Project Billing - India Tax Details Screen (Multiple HSN codes entry) -provision required.
India GST - Latest Patches Released (RUP 11 onwards) (Doc ID 2342288.1)
Hi Sir/Madam,
From Oracle SR reference# SR 3-17059267131 , we need to apply RUP11 patch in our testing Purpose.
This Patch is Consolidated Feb patch 27651381:R12.JAI.B .
In readme file , Pre-requisite patch details are not available.
But , according to Doc 'India GST - Latest Patches Released (RUP 11 onwards) (Doc ID 2342288.1)' , 2 pre-requisite patches are there.
Patch 27212160:R12.JAI.B This patch is consolidated patch for all the Patches released in Feb'2018 over RUP 11 patch.
Patch 27435589:R12.JAI.B This patch contains all the patches that are mentioned from Sl No. 2 to 17 in the 12.1.X Patches table
So , Our concern is Patch#27651381 already containing these two patch#27212160:R12.JAI.B & 27435589:R12.JAI.B bugs fixes also or we need to apply all these these patches one by one in which order.
Patch 27212160:R12.JAI.B This patch is consolidated patch for all the Patches released in Feb'2018 over RUP 11 patch.
Patch 27435589:R12.JAI.B This patch contains all the patches that are mentioned from Sl No. 2 to 17 in the 12.1.X Patches table
patch 27651381:R12.JAI.B .
Please provide the details .
Thanks & Regards,
Amin A
SR 3-17154869791 : RUP Patch# 27651381:R12.JAI.B
STGI_INSPECTION_PENDING
======================================
28-Mar-2018
RMAN>Backup Database;
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 67108864 bytes disk space from 10737418240 limit
http://dbaclass.com/article/ora-19809-limit-exceeded-recovery-files/
STGI_GATEPASS_PENDING
STGI_GATEPASS_REPORT
TJPS_GP_PENDING_RPT
http://tjtgb006.toshiba-tjps.in:8000/
http://oracleapps88.blogspot.in/p/scripts.html
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/
SQL> @tbsp_free_usedspace.sql
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP 3000 0 98 100
UNDOTBS1 3185 3025.75 95 5
USERS 5 4 80 20
TEMP 3000 2343 78 22
SYSAUX 7310 352.8125 5 95
TJTGHRP_DATA 9942 502.0625 5 95
SYSTEM 780 18.625 2 98
TEMP 69 0 0 100
TEMP 69 2343 -852 -3296
/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/
tbsp_free_usedspace.sql
alter tablespace APPS_TS_TX_DATA add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_data12.dbf' size 9000m;
alter tablespace APPS_TS_TX_IDX add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_ind08.dbf' size 7000m;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/undotbs02.dbf' size 4000m;
ALTER TABLESPACE TJTGHRP_DATA ADD DATAFILE '/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/tjtghrp_data02.dbf' size 4000m;
ALTER TABLESPACE SYSTEM ADD DATAFILE '/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/system02.dbf' size 4000m;
ALTER TABLESPACE SYSAUX ADD DATAFILE '/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/sysaux02.dbf' size 4000m;
ALTER TABLESPACE USERS ADD DATAFILE '/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/users02.dbf' size 400m;
orauat:8007
/ORADB/app/orauat/db/11.2.0
/ORADB/app/orauat/db/11.2.0/admin/UAT_tjpsb007/diag/rdbms/uat/UAT/trace
------------------------------------------------
SQL> startup nomount;
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 536870912
Additional information: 1
[oradev@TJPSB007 ~]$ cat /proc/sys/kernel/sem
250 32000 100 128
[oraprod@tjpsb004 ~]$ cat /proc/sys/kernel/sem
256 32000 100 142
[root@ebs121 tmp]# cat /etc/sysconfig/network
cat /etc/sysconfig/networking/profiles/default/network
/sbin/sysctl -a | grep <param-name>
"/etc/sysctl.conf
[root@ebs121 ~]# cat /etc/resolv.conf
/etc/security/limits.conf
[oraprod@tjpsb004 ~]$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=tjpsb004.ebs.in
GATEWAY=172.16.2.1
NTPSERVERARGS=iburst
[oraprod@tjpsb004 ~]$ cat /etc/resolv.conf
# Generated by NetworkManager
search ebs.in
nameserver 172.16.2.10
nameserver 172.16.2.9
[oradev@TJPSB007 ~]$ cat /etc/resolv.conf
# Generated by NetworkManager
search ebs.in
nameserver 172.16.2.9
nameserver 172.16.2.10
nameserver 172.16.2.87
# End of file
applprod hard nofile 65536
applprod soft nofile 4096
applprod hard nproc 16384
applprod soft nproc 2047
applprod soft stack 10240
oraprod hard nofile 65536
oraprod soft nofile 4096
oraprod hard nproc 16384
oraprod soft nproc 2047
oraprod soft stack 10240
otjtghrp hard nofile 65536
otjtghrp soft nofile 4096
otjtghrp hard nproc 16384
otjtghrp soft nproc 2047
otjtghrp soft stack 10240
* soft memlock 60397977
* hard memlock 60397977
getconf PAGE_SIZE
4096
Determine the system wide maximum number of shared memory pages:
4294967296
http://surachartopun.com/2009/10/shared-memory-tuning-startup-database.html
=========================
printer file
\\172.16.2.217\file_share
=========================
ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 524288
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1
ulimit -l
64
cat /proc/sys/kernel/shmall
2097152
======================
prod:
# Controls the maximum shared segment size, in bytes
##kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
####################################
kernel.sem =256 32000 100 142
##kernel.shmall =2097152
kernel.shmmax =33826908160
kernel.shmmni =4096
kernel.msgmax =8192
kernel.msgmnb =65535
kernel.msgmni =2878
fs.file-max =6815744
net.ipv4.ip_local_port_range =10000 65000
net.ipv4.tcp_tw_recycle= 0
net.core.rmem_default =262144
net.core.rmem_max =4194304
net.core.wmem_default= 262144
net.core.wmem_max =1048576
fs.aio-max-nr = 1048576
vm.nr_hugepages = 12290
8589934592
kernel.shmmax = 8589934592
/ASCPPROD_DB/ = 251G
/ASCPPROD_DB1/=1g
/ASCPPROD_DB2/=1g
/ASCPPROD_Arch=23g
SQL> select member from v$logfile;
==================================
ORA-00202: control file: '/GSTDB/oradata/TEST/cntrl01.dbf'
ORA-16038: log 6 sequence# 154 cannot be archived
ORA-00312: online log 6 thread 1: '/ORADB/oradata/TEST/log2a.dbf'
ORA-00312: online log 6 thread 1: '/ORADB/oradata/TEST/log2b.dbf'
ORA-00312: online log 6 thread 1: '/ORADB/oradata/TEST/log2c.dbf'
oratest:
Oracle_Home:/GSTDB/app/oratest/db/11.2.0
/GSTDB/app/oratest/db/11.2.0/admin/TEST_tjpsb007/diag/rdbms/test/TEST/trace
/ORADB/oradata/TEST/:1.3tb
1.SQL> shut abort;
2.
SQL> startup nomount;
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory
3.
/dev/mapper/oradb-oradb_lv01 1.6T 1.3T 276G 82% /ORADB
/dev/mapper/8025-lvol0 1.3T 237G 933G 21% /GSTDB
/dev/mapper/testdb-testdb_lv01 1.6T 1.4T 92G 95% /TESTDB
/GSTDB/oradata/TEST/cntrl01.dbf:control file and tempfile , redolog
is not available in that location.
ALTER DATABASE BACKUP CONTROLFILE TO '/GSTDB/app/oratest/db/11.2.0/dbs/control.bkp';
Error:ORA-00257: archiver error. Connect internal only, until freed.
oradev:
show parameter dump;
/TESTDB/app/oradev/db/11.2.0/admin/DEV_tjpsb007/diag/rdbms/dev/DEV/trace
archive log list;
/TESTDB/oradata/DEV/archive:55gb
select name from v$datafile;
select name from v$tempfile;
/TESTDB/oradata/DEV/:1.3T
cd $ORACLE_HOME:
/TESTDB/app/oradev/db/11.2.0/:8.2 gb
1300+55+10=1365gb
==========================================
SQL> create pfile='E:\app\roger\product\11.1.0\db_1\database\orcl.ora' from spfile;
/ORADB/oradata/TEST/
create pfile='/ORADB/oradata/TEST/initTEST1.ora' from spfile;
make changes to init file.
control file locatioin and archive destination.
SQL> startup nomount pfile='/ORADB/oradata/TEST/initTEST1.ora';
cd rman_prod_backups/
813 ls
814 sftp oraprod@172.16.2.32
815 sqlplus
816 export ORACLE_SID=TJTG
817 sqlplus
818 vi rename_datafiles.lst
819 mv rename_datafiles.lst /GSTDB/oradata/rman_prod_backups/
820 ls
808 cd archivelogs/
cd clone/bin
perl adcfgclone.pl dbconfig /GSTDB/app/oratest/db/11.2.0/appsutil/TEST_tjpsb007.xml
rman target /
840 sqlplus
841 export ORACLE_SID=TJTG
842 sqlplus
843 export ORACLE_SID=TEST
844 sqlplus
845 export ORACLE_SID=TJTG
846 cd $ORACLE_HOME/dbs
sftp oraprod@172.16.2.32
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*
select name from v$controlfile;
startup nomount spfile='/GSTDB/app/oratest/db/11.2.0/dbs/spfileTEST.ora';
/oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/appl/admin/log/
/oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/logs/ora/10.1.3/opmn/oacore_default_group_1
configFileName: /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/ora/10.1.2/forms/server/appsweb.cfg
ENOVIA
root
oracle user
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6019.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6015.trc.
Tue Apr 03 14:32:32 2018
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6011.trc.
Tue Apr 03 14:32:32 2018
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6023.trc.
Tue Apr 03 14:32:35 2018
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6211.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6011.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6015.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6023.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6019.trc.
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6211.trc.
Tue Apr 03 18:00:04 2018
============================
startup nomount pfile='/GSTDB/app/oratest/db/11.2.0/dbs/initTEST.ora';
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
disable LOG_ARCHIVE_DEST_1 in parameter file:
>
SQL> startup nomount pfile='/GSTDB/app/oratest/db/11.2.0/dbs/initTEST.ora';
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 457180040 bytes
Database Buffers 595591168 bytes
Redo Buffers 13905920 bytes
/GSTDB/oradata/TEST/cntrl01.dbf
/GSTDB/oradata/TEST/cntrl02.dbf
/GSTDB/oradata/TEST/cntrl03.dbf
547 Johnson
========================================================================
get us the Test server Oracle (10.116.8.8) DB dump for user V6R2015X_EBS.
V6R2015X
SQL> select USERNAME from dba_users where USERNAME like 'V6R2015%';
V6R2015X_EBS
V6R2015X_EBS/V6R2015X;
V6R2015X
Starting "V6R2015X_EBS"."SYS_IMPORT_FULL_01": V6R2015X_EBS/******** directory=DATA_PUMP_DIR dumpfile=TESTSERVER_31Jan2018.dmp logfile=TESTSERVER_02Feb2018_EBS.log
expdp
expdp V6R2015X_EBS/V6R2015X@orcl DUMPFILE=TESTSERVER_EBS_14June.dmp logfile=TESTSERVER_14June.log directory=data_pump_dir
expdp DUMPFILE=TESTSERVER_EBS_6Apr18.dmp logfile=TESTSERVER_6Apr18.log directory=data_pump_dir schemas=V6R2015X_EBS
/ as sysdba
grant all on directory DATA_PUMP_DIR to V6R2015X_EBS;
grant imp_full_database to V6R2015X_EBS;
grant exp_full_database to V6R2015X_EBS;
SELECT owner, directory_name, directory_path FROM all_directories
/home/app/oracle/admin/orcl/dpdump/ DATA_PUMP_DIR
/tmp/oracle_dump
CREATE OR REPLACE DIRECTORY test_dir2 AS '/tmp/oracle_dump/';
GRANT READ, WRITE ON DIRECTORY test_dir2 TO V6R2015X_EBS;
GRANT READ, WRITE ON DIRECTORY test_dir2 TO V6R2015X;
grant imp_full_database to V6R2015X;
grant exp_full_database to V6R2015X;
grant imp_full_database to V6R2015X_EBS;
grant exp_full_database to V6R2015X_EBS;
cd /tmp/oracle_dump
expdp DUMPFILE=TESTSERVER_EBS_30Apr18.dmp logfile=TESTSERVER_30Apr18.log directory=test_dir2 schemas=V6R2015X_EBS
/ as sysdba
V6R2015X oinstall
FOR V6R2015X_EBS :
expdp DUMPFILE=TEST_V6R2015XEBS_10Apr18.dmp logfile=TEST_V6R2015XEBS_10Apr18.log directory=test_dir2 schemas=V6R2015X_EBS
/ as sysdba
FOR V6R2015X:
expdp DUMPFILE=TEST_V6R2015X_10Apr18.dmp logfile=TEST_V6R2015X_10Apr18.log directory=test_dir2 schemas=V6R2015X
/ as sysdba
===
frmcmp_batch userid=apps/ module=XXINNUM.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/XXINNUM.fmx module_type=form batch=no compile_all=yes
/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US/
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/
No concurrent manager is defined to process this request, so it cannot be processed.
Contact your system administrator to define a concurrent manager to process this request or to verify that existing concurrent managers have the correct specialization rules.
http://appsdbastuff.blogspot.in/2011/03/all-concurrent-requests-have-status-of.html#!/2011/03/all-concurrent-requests-have-status-of.html
INCTM apps/ZG5927E225299B3FD8439B4974F3C25AF621E51E030F520EE839D7AEFA3E815818CFAF76913D68FCA3344 75B5C713FFB6F INV INVTMRPM N 60 t LOCK Y TJPSB005 591517
RCVOLTM apps/ZG5EF510B895AD581FD436E30C78CA3AC6C2D866CEC21E08E9107E2EFCBF040FBB9DEED1ADEBB827FC3 4475B5C713FFB6F PO RCVOLTM N 60 t LOCK Y TJPSB005 591518
POXCON apps/ZG19F79076DC68724B4FD38EC792ADE1136EC12EEC9E96F06294BD9FEE3A9DCD228B47C9DF6B8DF65234 475B5C713FFB6F PO POSB005 591519
Routine FNDSM cannot terminate the concurrent process with concurrent process ID &CPID and opera ting system process ID 53168
Shut down the concurrent managers to terminate the process or use port-specific operating system commands to termina31
This request will not be processed because there are currently no managers running that can process it.
This is an error condition. There are one or more managers defined that can process this request that should be running at this time, but do not appear to be running.
Contact your system administrator and/or check the manager log files for errors.
https://community.oracle.com/thread/584757?start=0&tstart=0
cat delete_files_from_os.sh
-----
find $APPLCSF/$APPLLOG -mtime +10 -name "*.req" -exec rm "{}" ";"
find $APPLCSF/$APPLLOG -mtime +10 -name "*.log" -exec rm "{}" ";"
find $APPLCSF/$APPLLOG -mtime +10 -name "*.mgr" -exec rm "{}" ";"
find $APPLCSF/$APPLOUT -mtime +10 -name "*.out" -exec rm "{}" ";"
find $APPLCSF/$APPLLOG -mtime +10 -name "f60web*" -exec rm "{}" ";"
--------
I will schedule delete_files_from_os.sh to run weekly after running the purge program...
find . -mtime +10 -name "*.out" -ls
find . -mtime +10 -name "*.zip" -ls
.zip
.xls
Remove Last 15 days data:
Present Date:25 Apr,2018
It will remove , 25 -15=10 Apr. Before 10 april data from system.
find $APPLCSF/$APPLOUT -mtime +15 -name "*.out" -exec rm "{}" ";"
find $APPLCSF/$APPLOUT -mtime +15 -name "*.zip" -exec rm "{}" ";"
find $APPLCSF/$APPLOUT -mtime +15 -name "*.xls" -exec rm "{}" ";"
find $APPLCSF/$APPLOUT -mtime +15 -name "*.pdf" -exec rm "{}" ";"
Find the File According to size:
ls -lSr
32gb
find $APPLCSF/$APPLLOG -mtime +10 -
GL
Optic script
audit;
E:\Optic\Optic\Optic_old_R12
Could not handle the control event -> java.lang.RuntimeException: Connection refused
$INST_TOP/appl/admin/<SID>_hostname.xml.
customer.care@sc.com
/testapp/app/appltest/apps/inst/apps/TEST_tjpsb008/ora/10.1.2/forms/server
/testapp/app/appltest/apps/apps_st/custom/TJTG/12.0.0
###Begin Customization
TJTG_TOP=/testapp/app/appltest/apps/apps_st/custom/TJTG/12.0.0
###End Customization
XXTJ_AT_MANHR_RPT
XXTJ_AT_RESOURCE_WISE
SYSTEM
APPS_UNDOTS1
APPS_TS_TX_DATA
APPS_TS_TX_IDX
alter tablespace SYSTEM add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/system013.dbf ' size 5000m;
ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/oradb/oracle/product/TJTG/db/apps_st/data/undo142.dbf' size 10000m;
alter tablespace APPS_TS_TX_DATA add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_data219.dbf' size 9000m;
alter tablespace APPS_TS_TX_IDX add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_ind133.dbf' size 7000m;
tbs_datafile_name.sql
tbsp_free_usedspace.sql
Information Center: Installing and Configuring Oracle JDeveloper & ADF 11g and 12c (Doc ID 1346677.2)
ADF 11gr1 Setup For High Availability Within an Oracle Weblogic 10.3 Cluster(Doc ID 827973.1)
How to Create a Connection to WebLogic 10.3 Server From JDeveloper 11g (Doc ID 749712.1)
Oracle E-Business Suite Release 12.1.3 Readme (Doc ID 1080973.1)
http://rafioracledba.blogspot.in/2011/05/how-to-apply-database-patches.html
http://rafioracledba.blogspot.in/
http://rafioracledba.blogspot.in/2017/12/useful-database-health-check-scripts.html
http://javeedkaleem.blogspot.in/2010/05/how-to-apply-oracle-database-patch.html#!/2010/05/how-to-apply-oracle-database-patch.html
http://rafioracledba.blogspot.in/2017/03/clonning-oracle-ebs-applications-and.html
http://rafioracledba.blogspot.in/2015/08/difference-in-location-of-log-files-in.html
XXTJ_AT_RESOURCE_WISE
XXTJ_CBR_QUERY_FORM
/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US
frmcmp_batch module=XXTJ_CBR_QUERY_FORM.fmb userid=apps/apps output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/XXTJ_CBR_QUERY_FORM.fmx
module_type=form compile_all=yes
frmcmp_batch module=XXTJ_CBR_QUERY_FORM.fmb userid=apps/JzavetOVDr6LKua output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/XXTJ_CBR_QUERY_FORM.fmx
Dear Sender,
Thank you for your mail.Please expect delay for response mail.
As i am on Personal Leave.
If anything urgent , please contact Sivaraman San
Regards,
Amin
==========================
8007
appltest:/testapp/--57gb
oratest:/OratestC/--1.5tb
8006
appldev:/TESTAPPS/--59gb
oradev:/TESTDB/--1.5tb
UAT:
orauat:/ORADB/
appluat:/
#################################
1.Datafile
select name from v$datafile
/OratestC/oradata/TESTa_txn_ind02.dbf
258 rows selected.
2.
select name from v$tempfile;
/OratestC/oradata/TEST/temp01.dbf
3.select member from v$logfile;
/OratestC/oradata/TEST/log2a.dbf
8006
appldev:/TESTAPPS/
oradev:/TESTDB/
1.Datafile
select name from v$datafile
/TESTDB/oradata/DEV/a_queue01.dbf
251 rows selected.
2.
select name from v$tempfile;
/TESTDB/oradata/DEV/temp01.dbf
3.select member from v$logfile;
/TESTDB/oradata/DEV/log2a.dbf
===========================================
ASCPTST:
DROP PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN";
CREATE PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY <PWD>
USING 'UAT';
TEST:
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY <PWD>
USING '//172.116.2.81:1531/ASCPTST';
############################################################
ASCPTST:
DROP PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN";
CREATE PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.116.2.65:1528/TEST';
TEST:
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '//172.116.2.81:1531/ASCPTST';
#############################################################
select * from DBA_AUDIT_EXISTS;
select * from DBA_AUDIT_OBJECT WHERE CLIENT_ID='SYSADMIN'
select * from DBA_AUDIT_POLICIES
select * from DBA_AUDIT_POLICY_COLUMNS;
select * from DBA_AUDIT_SESSION WHERE CLIENT_ID ='SYSADMIN' AND ACTION_NAME NOT IN ('LOGOFF','LOGOFF BY CLEANUP');
select * from DBA_AUDIT_STATEMENT;
select * from DBA_AUDIT_TRAIL where ACTION_NAME in ('CREATE TABLE','UPDATE','SESSION REC','CREATE DIRECTORY','TRUNCATE TABLE');
select * from DBA_AUDIT_TRAIL where ACTION_NAME in ('CREATE TABLE','UPDATE','SESSION REC','CREATE DIRECTORY','TRUNCATE TABLE') and CLIENT_ID ='SYSADMIN'
select * from DBA_COMMON_AUDIT_TRAIL where CLIENT_ID like 'SYSADMIN';
STATEMENT_TYPE
select distinct(STATEMENT_TYPE) from DBA_COMMON_AUDIT_TRAIL where CLIENT_ID like 'SYSADMIN' and STATEMENT_TYPE='UPDATE';
select * from DBA_COMMON_AUDIT_TRAIL where CLIENT_ID like 'SYSADMIN' and STATEMENT_TYPE='UPDATE';
3439
select * from DBA_FGA_AUDIT_TRAIL
select * from DBA_OBJ_AUDIT_OPTS
select * from DBA_PRIV_AUDIT_OPTS
select * from DBA_REPAUDIT_ATTRIBUTE
select * from DBA_REPAUDIT_COLUMN;
select * from DBA_STMT_AUDIT_OPTS;
==========================
E:\kumaresan\Apr\24
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/reports/US/
748 cat /proc/sys/kernel/sem
749 cat /etc/selinux/config
750 cat /etc/sysconfig/network
751 cat /etc/resolv.conf
752 cat /etc/security/limits.conf
753 getconf PAGE_SIZE
754 cat /proc/sys/kernel/shmall
nohup cp * /SMBBKP/ebs_db/ &
516 sftp applprod@172.16.2.33
517 sftp root@172.16.2.66
493 mount -t cifs -o domain=toshiba-jsw.in,username=,password=Secure@123
rtf file) and
4. Form (Compile fmb)
===================================================
25-Apr-2018
Log file located at /ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/admin/log/StageAppsTier_04251218.log
=================================
26-Apr-2018
SR 3-17365998645
SR 3-17365998645 : EBS Prod Frequently going down
/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US
frmcmp_batch userid=apps/J module=STGI_DISPATCH.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/STGI_DISPATCH.fmx module_type=form batch=no compile_all=yes
======================================================
1.
On 30th April request you to kindly take a DB dump from test server (10.116.8.8) for user V6R2015X_EBS.
Module Responsibility Name
Inventory TJPS Inventory Inquiry – IS
Purchasing TJPS Purchasing Inquiry - IS
Order Management TJPS Order Management Inquiry - IS
Payables TJPS Payables Inquiry - IS
Receivables TJPS Receivables Inquiry - IS
Projects TJPS Projects Inquiry - IS
Custom Application TJPS Custom Application - IS
E:\AUDIT_18\APRIL-2018
================================================================================================
[V6R2015X@ENOVIA-MCS-SRV oracle_dump]$ cat dbexport.sh
#!/bin/sh
FileName=V6R2015X_EBS_$(date +%Y%m%d%H).dmp
LogFile=V6R2015XLOGi_$(date +%Y%m%d%H).log
ORACLE_HOME=/home/app/oracle/product/11.2.0/db_2/db_1
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_SID=orcl; export ORACLE_SID
#expdp V6R2015X_EBS/V6R2015X schemas=V6R2015X_EBS DIRECTORY=backdir DUMPFILE=V6R2015X_EBS_$(date +%Y%m%d%H).dmp VERSION=10.2 LOGFILE=V6R2015XLOGi_$(date +%Y%m%d%H).log
expdp V6R2015X_EBS/V6R2015X schemas=V6R2015X_EBS DIRECTORY=backdir DUMPFILE=$FileName VERSION=10.2 LOGFILE=$LogFile
# To change the permissions of the File
#chown enoviadm.enoviadm $FileName $LogFile
#gzip -r /tmp/oracle_dump/V6R2015X_EBS_$(date +%Y%m%d%H).dmp /tmp/oracle_dump/V6R2015X_EBS_$(date +%Y%m%d%H).dmp
# can be uncommented below line
#tar -cvzf /tmp/oracle_dump/V6R2015X_EBS_$(date +%Y%m%d%H).tar.gz /tmp/oracle_dump/V6R2015X_EBS_$(date +%Y%m%d%H).dmp
#tar -cvzf /tmp/oracle_dump/$FileName.tar.gz /tmp/oracle_dump/$FileName
#cat /tmp/oracle_dump/V6R2015XLOG-$(date +%Y%m%d%H).log | mailx -s "V6R2015X Backup" -a /tmp/oracle_dump/V6R2015X-$(date +%Y%m%d%H).zip mayank.ahuja@toshiba-tjps.in
#cat /tmp/oracle_dump/V6R2015XLOGi_$(date +%Y%m%d%H).log | mailx -s "V6R2015X Backup" mayank.ahuja@toshiba-tjps.in
# for enabling email notifications
#cat /tmp/oracle_dump/$LogFile | mailx -s "V6R2015X Backup"
========================================================================================================================
98G 93G 181M 100% /ASCPPROD_Arch
Archive destination:/ASCPPROD_Arch/oracle/product/ASCPPROD/arch
/ASCPPROD_DB/oracle/product/ASCPPROD/adr/diag/rdbms/chennai/ASCPPROD/trace
Error:
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ASCPPROD - Archival Error
ORA-16014: log 8 sequence# 128667 not archived, no available destinations
ORA-00312: online log 8 thread 1: '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/log02a.dbf'
ORA-00312: online log 8 thread 1: '/ASCPPROD_DB1/oracle/product/ASCPPROD/db/apps_st/data/log02b.dbf'
ORA-00312: online log 8 thread 1: '/ASCPPROD_DB2/oracle/product/ASCPPROD/db/apps_st/data/log02c.dbf'
Wed May 02 08:55:19 2018
soln:
[root@TJTGB006 TJTGB006_BACKUP]# mkdir -p ASCPPROD_2MAY18_BKP
[root@TJTGB006 TJTGB006_BACKUP]# chown -R oascpprd:dba ASCPPROD_2MAY18_BKP
[root@TJTGB006 TJTGB006_BACKUP]# chmod -R 775 ASCPPROD_2MAY18_BKP
[root@TJTGB006 TJTGB006_BACKUP]#
[root@TJTGB006 TJTGB006_BACKUP]# pwd
/TJTGB006_BACKUP
ASCPPROD_1_868558661_128633.arc
ASCPPROD_1_868558661_128666.arc
mv ASCPPROD_1_868558661_12863*.arc /TJTGB006_BACKUP/ASCPPROD_2MAY18_BKP/
select FILE#,STATUS,CHANGE#,time from v$backup;
File status: NOT ACTIVE, ACTIVE (backup in progress), OFFLINE NORMAL, or description of an error.
NOT ACTIVE indicates that the file is not currently in backup mode (that is, an ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP statement has not been issued), whereas ACTIVE indicates that the file is currently in backup mode.
1.
https://archive.sap.com/discussions/thread/3500664
2.
SQL> show parameter LOG_ARCHIVE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/ASCPPROD_Arch/oracle
/product/ASCPPROD/arch
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service="hyderabad", LGWR ASYN
C NOAFFIRM delay=0 optional co
mpression=enable max_failure=0
max_connections=1 reopen=300
db_unique_name="hyderabad" net
_timeout=30, valid_for=(all_lo
gfiles,primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ebs db prod:
/dev/mapper/datavg-oradb 1.7T 1.4T 249G 85% /oradb
==========================================================================================
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
TEMP1 4000 19630 498 402
TEMP1 4000 0 400 500
APPS_TS_TOOLS 2000 1999.875 100 0
OWAPUB 500 499.921875 100 0
PORTAL 100 99.53125 100 0
ODM 1000 990.4375 99 1
OLAP 17.2421875 17.0625 99 1
CTXD 1000 975.289063 98 2
APPS_TS_NOLOGGING 2000 1932.625 97 3
TJTG 4000 3775.875 94 6
APPS_TS_QUEUES 2000 1837.875 92 8
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
APPS_TS_SUMMARY 5000 3623.125 72 28
SYSAUX 5000 3586 72 28
APPS_TS_INTERFACE 2026 1003.25 50 50
APPS_TS_ARCHIVE 2500 1121.25 45 55
APPS_TS_SEED 4672 1818.5 39 61
APPS_TS_TX_IDX 33000 12251.375 37 63
APPS_UNDOTS1 16500 5926.375 36 64
USERS 5000 1748 35 65
APPS_TS_MEDIA 5000 1653.75 33 67
SYSTEM 17800 5680.05469 32 68
APPS_TS_TX_DATA 66000 7835.125 12 88
22 rows selected.
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/
SELECT d.NAME, t.NAME
FROM v$datafile d, v$tablespace t
WHERE d.ts# = t.ts# AND t.NAME ='&input_tbs_name';
APPS_TS_TX_DATA
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_data13.dbf
alter tablespace APPS_TS_TX_DATA add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_data13.dbf' size 9000m;
APPS_TS_MEDIA
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_media03.dbf
alter tablespace APPS_TS_MEDIA add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_media03.dbf' size 3000m;
APPS_UNDOTS1
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/undo07.dbf
ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/undo07.dbf' size 9000m;
APPS_TS_TX_IDX
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_ind09.dbf
alter tablespace APPS_TS_TX_IDX add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_ind09.dbf' size 7000m;
USERS
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/users03.dbf
alter tablespace USERS add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/users03.dbf' size 3000m;
/dev/mapper/vg05-DBN 492G 284G 183G 61% /ASCPPROD_DB
==================================================
3-May-2018
-rw-r--r-- 1 applprod dba 1864207996 Apr 23 18:30 STGI_GLD_MAIN_14384867_1.xls 140719TJ
-rw-r--r-- 1 applprod dba 1864207999 Apr 24 18:47 STGI_GLD_MAIN_14392353_1.xls 140719TJ
-rw-r--r-- 1 applprod dba 1885462566 Apr 23 17:09 o14384867.out 140719TJ
-rw-r--r-- 1 applprod dba 1885462569 Apr 24 17:20 o14392353.out 140719TJ
-rw-r--r-- 1 applprod dba 3413119520 Apr 25 17:17 o14404132.out 150843TJ
-rw-r--r-- 1 applprod dba 3413119520 Apr 25 17:15 o14404125.out -- 150843TJ
==================================================
Display Archive Log Generation by Day
--
-- Display Archive Log Generation by Day
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COL "Generation Date" FORMAT a20
SELECT TRUNC(completion_time) "Generation Date" ,
round(SUM(blocks*block_size)/1048576,0) "Total for the Day in MB"
FROM gv$archived_log
GROUP BY TRUNC(completion_time)
ORDER BY TRUNC(completion_time)
/
Query to give size of archive generated perday in GB
============================================================
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;
===================================
/ASCPPROD_DB/oracle/product/ASCPPROD/adr/diag/rdbms/chennai/ASCPPROD/trace/
Period Close Exceptions Report (XML)
Concurrent Manager encountered an error while attempting to start your immediate concurrent program RVCTP. Routine &ROUTINE received a return code of failure.
select '************************ Getting Database Information *************' from dual;
ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%mem | head
98.0 60889 oraprod oracleTJTG (LOCAL=NO)
97.4 58022 oraprod oracleTJTG (LOCAL=NO)
92.5 61211 oraprod oracleTJTG (LOCAL=NO)
ps -p 60889 -o comm=
Ogi Kana-san’s
=========================================================================================
7-May-2018
cd $APPLCSG
cd out
ls- lSr
Large File Log
1.FIND THE concurrent requester User_name with Request ID:
SELECT *
FROM fnd_user
WHERE user_id IN (
SELECT requested_by
FROM fnd_conc_req_summary_v
WHERE request_id='&Enter_req_Id');
2.SELECT request_id, requested_by, concurrent_program_id
FROM fnd_conc_req_summary_v
WHERE request_id IN
('14433171', '10125520', '14435667', '14440801',
'14426749', '14438836', '14438834', '14438829',
'14438823'));
STGI_INSPECTION_PENDING
E:\siva\2018\May\7\Ticket NO 3480
DR SETUP:
EBS ASCPPROD;
http://tjpsb005dr.ebs.in:8000
STGI_DISPATCH.fmb
/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US
frmcmp_batch userid=apps/apps module=STGI_DISPATCH.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/STGI_DISPATCH.fmx module_type=form batch=no compile_all=yes
===============================
8-May-2018
07-MAY 19:31:33 : ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from EBS_TO_ASCP.JSW_IN
CNF/D3/47/WS 12
CNF/D3/11/WS 14
ORIGINAL:
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING 'ASCPPROD';
MODIFIED:
DR Server Setup:
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.157:1521/ASCPPROD';
############################################################
ASCPTST:
DROP PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN";
CREATE PUBLIC DATABASE LINK "ASCP_TO_EBS.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.65:1528/TEST';
TEST:
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '//172.116.2.81:1531/ASCPTST';
#############################################################
TJPS_AP_INVOICE_PENDING
E:\kumaresan\May\8
XXMZBK_MPXLS_PROG
E:\Mizuho\9 May
Check Symbolic Link:
ls -l XXMZBK_MPXLS_PROG
Unlink symbolic named as XXMZBK_MPXLS_PROG:
XXMZBK_MPXLS_PROG -> /testapp/app/appltest/apps/apps_st/appl/fnd/12.0.0/bin/fndcpesr
>unlink XXMZBK_MPXLS_PROG
Create Softlink:
ln -s $FND_TOP/bin/fndcpesr $AP_TOP/bin/XXMZBK_MPXLS_PROG
Give Permission 777 on XXMZBK_MPXLS_PROG:
chmod -R 777 XXMZBK_MPXLS_PROG
------------------
CREATE SEQUENCE XXMZBK_MPXLS_SEQ
START WITH 0000000001
INCREMENT BY 1
NOCACHE
NOCYCLE;
drop sequence XXMZBK_MPXLS_SEQ;
-----------------
/testapp/app/appltest/apps/apps_st/MIZUHO/
WHY CM IS LONG RUNNING:
http://prabhatdshine.blogspot.in/2013/10/sql-queries-to-analyze-all-concurrent.html#!/2013/10/sql-queries-to-analyze-all-concurrent.html
Note:29012.1 QREF: TKPROF Usage - Quick Reference
TKProf Interpretation (9i and below) (Doc ID 32951.1)
NOTE:29012.1 - QREF: TKPROF Usage - Quick Reference
NOTE:32895.1 - SQL Parsing Flow Diagram
Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
NOTE:7522002.8 - Bug 7522002 - "Jumps back in time" and Repeated timestamps in Cursor Steps in SQL Trace in some platforms
NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
NOTE:30366.1 - PACKAGE DBMS_APPLICATION_INFO Specification
NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:43718.1 - VIEW: "V$SESSION_WAIT" Reference Note
NOTE:43721.1 - VIEW: "V$EVENT_NAME" Reference Note
NOTE:43761.1 - VIEW: "V$SQLAREA" Reference Note
NOTE:43764.1 - VIEW: "V$SQLTEXT" Reference Note
•Alternative Trace Possibilities for Specific Scenarios
Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Document 1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
Document 160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Document 371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
Document 1102801.1 How to Get 10046 Trace for Parallel Query
Document 242374.1 Tracing PX session with a 10046 event or sql_trace
Document 258418.1 Getting 10046 Trace for Export and Import
If you are running PL/SQL procedures or packages then use of the PL/SQL profiler can be useful to determine where time goes while using PL/SQL routines.
Instructions on the use of the profiler can be found in the following article:
Document 243755.1 Implementing and Using the PL/SQL Profiler
•Trace Interpretation
Document 199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
Document 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
Document 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
Document 41634.1 - TKProf Basic Overview
Document 32951.1 - TKProf Interpretation (9i and below)
Document 760786.1 - TKProf Interpretation (9i and above)
Document 214106.1 - Using TKProf to compare actual and predicted row counts
Document 199083.1 * Master Document SQL Query Performance Overview
Document 398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions
Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)
Document 2034610.1 Things to Consider Before Upgrading to 12.1.0.2 to Avoid Poor Performance or Wrong Results
Document 1645862.1 Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results
Document 1392633.1 Things to Consider before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
Document 1320966.1 Things to Consider before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
Document 2187449.1 Recommendations for Adaptive Features in Oracle Database 12c
Document 1464274.1 Master Note for Real Application Testing Option
Document 762540.1 Consolidated Reference List Of Notes For Migration / Upgrade Service Requests
References
NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues
NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:456518.1 - How to Use SQL Plan Management (SPM) - Plan Stability Worked Example
NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:1551797.1 - Collaborate with MOS SQL Performance Community Members
NOTE:402983.1 - * Master Note: Database Performance Overview
NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions
NOTE:1361401.1 - Where to Find Information About Performance Related Features
NOTE:1226841.1 - How To: Gather Statistics for the Cost Based Optimizer
NOTE:470316.1 - Using Actual System Statistics (Collected CPU and IO information)
NOTE:29236.1 - QREF: SQL Statement HINTS
NOTE:232963.1 - How to Build a Testcase for Oracle Data Server Support to Reproduce ORA-600 and ORA-7445 Errors
NOTE:68735.1 - * Diagnostics for Query Tuning Problems
NOTE:248971.1 - Query Tuning Best Practices
NOTE:50607.1 - How to Specify an INDEX Hint
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)
NOTE:742112.1 - Troubleshooting Query Performance Degradation - Recommended Actions
NOTE:745216.1 - Troubleshooting Upgrade Related Query Performance Degradation - Recommended Actions
NOTE:235530.1 - * How to Obtain a Formatted Explain Plan - Recommended Methods
NOTE:754931.1 - Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
NOTE:67522.1 - * Diagnosing and Understanding Why a Query is Not Using an Index
NOTE:69992.1 - Why is my Hint Ignored?
NOTE:604256.1 - Why is a Particular Query Slower on One Machine (or Instance) than Another?
NOTE:223806.1 - Query with Unchanged Execution Plan is Slower than Previously
NOTE:160089.1 - Troubleshooting a Server Upgrade Resulting in Slow Query Performance
NOTE:10626.1 - Cost Based Optimizer (CBO) Overview
NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
NOTE:957993.1 - Script to Show Objects That are Missing Statistics
NOTE:46234.1 - Interpreting Explain plan
NOTE:727863.1 - How to Create a SQL-testcase Using the DBMS_SQLDIAG Package [Video]
NOTE:1386802.1 - How To Use DBMS_SQLDIAG To Diagnose Query Performance Issues
NOTE:466604.1 - How to Use DBMS_SQLDIAG to Generate a Workaround for Wrong Results Issues
NOTE:1360120.1 - How to use DBMS_SQLDIAG to Diagnose Crash or Error Issues
NOTE:179668.1 - TROUBLESHOOTING: Tuning Slow Running Queries
NOTE:33089.1 - * TROUBLESHOOTING: Possible Causes of Poor SQL Performance
NOTE:1442213.1 - Troubleshooting: Tuning Queries that Cannot Be Modified (11g and Above)
NOTE:398838.1 - FAQ: SQL Query Performance - Frequently Asked Questions
NOTE:67536.1 - Stored Outline Quick Reference
NOTE:445126.1 - HOW TO: Create a Stored Outline Based Upon an Existing Cursor
NOTE:728647.1 - How to Transfer Stored Outlines from One Database to Another (9i and above)
NOTE:730062.1 - How to Edit a Stored Outline to Use the Plan from Another Stored Outline
NOTE:1383594.1 - Collaborate with MOS Database Tuning Community Members
NOTE:1456176.1 - Oracle Database Advisor Webcast Schedule and Archive recordings
NOTE:163563.1 - * TROUBLESHOOTING: Advanced Query Tuning
NOTE:1195363.1 - Database Performance and SQL Tuning Documentation on OTN
NOTE:1337116.1 - White Papers and Blog Entries for Oracle Optimizer
/orahome/oracle/product/TJTG/db/tech_st/11.2.0/rdbms/admin
sqlplus / as sysdba
awrrpt.sql
awrrpt_1_27874_27877.html
=============================================
==============================================
10-May-2018
****************************************************
adalnctl.sh version 120.3
Checking for FNDFS executable.
Starting listener process APPS_TEST.
adalnctl.sh: exiting with status 1
adalnctl.sh: check the logfile /testapp/app/appltest/apps/inst/apps/TEST_tjpsb008/logs/appl/admin/log/adalnctl.txt for more information ...
cd $INST_TOP
cd ora/10.1.2/network/admin/
like statement team
CM RELATED QUERIES:
http://raghuook.blogspot.in/2010/08/apps-dba-trobleshoot-scripts.html
export PATH=/uatapp/app/applascp/apps_st/comn/clone/bin:$PATH
TJPS_CBM_TRANS
TJPS_CBM_TRANS_APP
TJPS_CBM_REPORT_VIEW
==================
KILL A ORACLE LOCK:
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
ALTER SYSTEM KILL SESSION '410,46207';
commit;
SELECT (SELECT username
FROM v$session
WHERE SID = a.SID) blocker, a.SID, ' is blocking ',
(SELECT username
FROM v$session
WHERE SID = b.SID) blockee, b.SID
FROM v$lock a, v$lock b
WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
http://www.dba-oracle.com/t_find_oracle_locked_objects.htm
=============================================
14-May-2018
TRansfer tmp file from applprod to 65 server:
scp -r xdo15Up8Uajh15494493084497454265.tmp oratest@172.16.2.65:/GSTDB
scp -r xdoVPwBoEDjrz6365298928612023712.tmp oratest@172.16.2.65:/GSTDB
Make a EBS clone Again for UAT Instance.
HOW TO UPGRADE FROM Lower to upper Version:
How to refresh db Duplicate method using RMAN.
Check who created EBS responsibility:
SELECT RESPONSIBILITY_NAME,CREATION_DATE FROM FND_RESPONSIBILITY_TL WHERE CREATION_DATE >= '01-MAY-18';
select * from FND_USER_RESP_GROUPS_DIRECT;
select * from FND_RESPONSIBILITY;
select * from FND_RESPONSIBILITY_TL where RESPONSIBILITY_NAME='TJPS AP Accounting Period';
ORA-01219: database not open: queries allowed on fixed tables/views only
alter system set processes=400 scope=spfile;
ORA-00020: maximum number of processes (200) exceeded
kill the db side LOCAL=NO
for a in $(ps -ef |grep FNDLIBR | grep -v grep | awk '{ print $2}'); do kill -9 $a; done
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY <PWD>
USING '172.16.2.157:1521/ASCPPROD';
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.157:1522/ASCPPROD';
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.157:1522/ASCPPROD';
ORA-01086 Savepoint 'PROCESS_ORDER' Never Established In Session Or Session Invalid
(Doc ID 1588316.1)
=======================================================================================
/orahome/oracle/product/TJTG/db/tech_st/11.2.0/network/admin/TJTG_tjpsb004
TJTG_tjpsb004_ifile.ora
ASCPPROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=tjpsb004dr.ebs.in)(PORT=1521))
(CONNECT_DATA=
(SID=ASCPPROD)
)
)
./adcmctl.sh stop
adrelink.sh force=y "fnd FNDCRM"
./adcmctl.sh start
14580028 --- TJPS QCS Print (TJPS QCS Print)
Ticket: 3480-1. In GRN Inspection Pending Report, the end user name is reflecting is as GRN Inspection Pending with end user for OSP Item.
=========================================
http://tjpspayrolldr.toshiba-jsw.in:7070/jpayroll/login.do
http://tjpshrdr.toshiba-jsw.in:9090/folklore/login.do
=================================
DISABLE ARCHIVELOG:
shut immediate
startup mount;
alter database noarchivelog;
alter database open;
login as: applprod
applprod@172.16.2.33's password:
Last login: Wed Jun 6 12:11:18 2018 from 172.16.7.39
[applprod@tjpsb005 ~]$ sftp MZHTSBAXXXXX@150.105.184.206
Connecting to 150.105.184.206...
Please login with user ID and password.MZHTSBAXXXXX@150.105.184.206's password:
6eDmbM3T
sftp> cd /././POLLABLE
sftp> pwd
Remote working directory: /MZHTSBAXXXXX/././POLLABLE
sftp> ls -alrt
-rw-r----- 1 0 0 2974 May 07 00:-1 :40 GCMS.FA.MZHTSBAXXXXX.MT940.0001.ot.00
sftp> mget GCMS.FA.MZHTSBAXXXXX.MT940.0001.ot.002597194 GCMS.FA.MZHTSBAXXXXX.MT9
Fetching /MZHTSBAXXXXX/././POLLABLE/GCMS.FA.MZHTSBAXXXXX.MT940.0001.ot.002597194
sftp>
Migration Steps.
cd /oraapps/oracle/product/TJTG/apps/apps_st/comn/java/classes/xxtjpa/oracle/apps/pa
1. Move the zip file to $JAVA_TOP/xxtjpa/oracle/apps/pa (Please take the backup for nnr.zip file).
2. Unzip the file.
3. Run the below script (Change Production Credentials)
java \
oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxtjpa/oracle/apps/pa/nnr/webui/ \
-rootdir $JAVA_TOP/ \
-jdk13 \
-username apps \
-password JzavetOVDr6LKua \
-mmddir $OA_HTML/jrad \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.2.32)(PORT=1521))(CONNECT_DATA=(INSTANCE_NAME=TJTG)(SERVICE_NAME=TJTG)))"
4. Bounce oacore and apache server.
cd /oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/admin/scripts/
./adoacorectl.sh stop
./adapcctl.sh stop
./adapcctl.sh start
./adoacorectl.sh start
http://tjpspayrolldr.toshiba-jsw.in:7070/jpayroll/login.do
http://tjpshrdr.toshiba-jsw.in:9090/folklore/login.do
LNS: Standby redo logfile selected for thread 1 sequence 68869 for destination LOG_ARCHIVE_DEST_2
RMAN> backup database format ‘/u02/oracle/backup/bkp.%U’ tag ‘retain’ keep until time ‘sysdate+2’;
==============================
18_jun-2018
RMAN BACKUP ISSUE IN ENOVIA:
http://oraware.blogspot.com/2008/05/ora-01152-file-1-was-not-restored-from.html
https://fatdba.com/2014/01/15/rman-error-ora-01152-file-1-was-not-restored-from-a-sufficiently-old-backup-ora-01110/
select LAST_DDL_TIME from dba_objects;
select count(*) from dba_objects where status='INVALID';
select count(*) from dba_objects where status='VALID';
select OBJECT_NAME from dba_objects where status='INVALID';
select OBJECT_NAME from dba_objects where status='VALID';
col OBJECT_NAME for a30
select OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where status='INVALID';
select OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where status='VALID' and created >= '31-MAY-18';
TABLESPACE;
select name from v$datafile
union
select name from v$tempfile
union
select name from v$controlfile
union
select member as name from v$logfile;
Oracle row count for all tables in schema
select 'select count(*) from '||table_name||';' from dba_tables where owner = 'V6R2015X';
A less intensive approach for estimating the total number of rows in a schema would query the num_rows column, much faster, but only accurate to the time that dbms_stats last counted the rows:
compute sum of counter on report;
select
table_name,
num_rows counter
from
dba_tables
where
owner = 'XXX'
order by
table_name;
============================
==============
tnsping FNDSM_devscl_visr12
lsnrctl status APPS_visr12
tnsping FNDSM_TJTGB006_ASCPPROD
tnsping FNDSM_TJPSB005_TJTG
Used parameter files:
/ASCPPROD_DB/oracle/product/ASCPPROD/db/tech_st/11.2.0/network/admin/ASCPPROD_tjtgb006/sqlnet_ifile.ora
TNS-03505: Failed to resolve name
select table_name,num_rows counter
from dba_tables
where owner = 'V6R2015X'
order by table_name;
lsnrctl status APPS_ASCPPROD
lsnrctl status APPS_TJTG
MXSET 264846
MXSETOBJ 245465
select count(*) from V6R2015X.MXSET;
select count(*) from V6R2015X.MXSETOBJ;
select count(*) from V6R2015X.
select table_name,num_rows counter
from dba_tables
where owner = 'V6R2015X'
order by table_name;
select count(*) from V6R2015X.MXSET WHERE
SQL> select count(*) from dba_tables;
COUNT(*)
----------
3474
select count(*) from tab;
SQL> select 'select count(*) from '||table_name||';' from dba_tables where owner = 'V6R2015X';
----------------------------------------------------
select count(*) from MXSTORE;
select count(*) from MXUSER;
select count(*) from MXASSIGN;
select count(*) from MXATTRTYPE;
select count(*) from MXRANGELIST;
select count(*) from MXFORMAT;
select count(*) from MXPROGRAM;
select count(*) from MXFUNCTION;
select count(*) from MXBUSTYPE;
select count(*) from MXRELTYPE;
select count(*) from MXDRVINFO;
----------------------------------------------------
select count(*) from MXPOLICY;
select count(*) from MXPOLINFO;
select count(*) from MXSTATEREQ;
select count(*) from MXSTATEREQUSR;
select count(*) from MXSIGREQ;
select count(*) from MXREPORT;
select count(*) from MXREPORTFIELD;
select count(*) from MXFORM;
select count(*) from MXFIELD;
select count(*) from MXTTABLE;
select count(*) from MXQUERY;
----------------------------------------------------
select count(*) from MXCUE;
select count(*) from MXMAIL;
select count(*) from MXMAILRCP;
select count(*) from MXSET;
select count(*) from MXSETOBJ;
select count(*) from MXVER6;
select count(*) from MXSERVER;
select count(*) from MXSRVLAT;
select count(*) from MXFRAME;
----------------------------------------------------
select count(*) from MXLOCATION;
select count(*) from MXPROPERTY;
select count(*) from MXADMIN;
select count(*) from MXDEFINT;
select count(*) from MXDEFREAL;
select count(*) from MXIADMIN;
select count(*) from MXWALLET;
----------------------------------------------------
select count(*) from MXOTHERADMIN;
select count(*) from MXMENU;
select count(*) from MXLIST;
select count(*) from MXINDEX;
select count(*) from MXPORTAL;
select count(*) from MXCHANNEL;
select count(*) from MXINTERFACE;
select count(*) from MXNAMEDLONG;
select count(*) from MXEXPRESSION;
select count(*) from MXWEBREPORT;
select count(*) from MXWEBREPEXT;
----------------------------------------------------
select count(*) from MXWEBREPSUM;
select count(*) from MXWRGR;
select count(*) from MXDIMENSION;
select count(*) from MXUNIT;
select count(*) from MXAPPLICATION;
select count(*) from MXPRODUCT;
select count(*) from MXPERSONPROD;
select count(*) from MXFILTER;
select count(*) from MXIDENT;
----------------------------------------------------
select count(*) from MXHIST;
select count(*) from MXPACKAGE;
select count(*) from MXCUSTOMEVENT;
select count(*) from MXFAMILY;
select count(*) from MXOBJEVENTLOG;
select count(*) from MXOBJEVENTMONITOR;
select count(*) from MXTENANT;
select count(*) from MXOV;
select count(*) from MXOWNER;
select count(*) from MXPARENT;
select count(*) from MXPATHTYPE;
select count(*) from MXUNIQUEKEY;
select count(*) from MXFILEAUX;
select count(*) from MXTNR;
----------------------------------------------------
select count(*) from LXOID_37806708;
select count(*) from LXIMG_37806708;
select count(*) from LXDESC_37806708;
select count(*) from LXBO_37806708;
select count(*) from LXRO_37806708;
select count(*) from LXSTRING_37806708;
select count(*) from LXREAL_37806708;
select count(*) from LXINT_37806708;
select count(*) from LXDATE_37806708;
select count(*) from LXBOOL_37806708;
select count(*) from LXHIST_37806708;
select count(*) from LXSTATE_37806708;
select count(*) from LXSIGN_37806708;
select count(*) from LXSET_37806708;
select count(*) from LXFILE_37806708;
select count(*) from LXINST_37806708;
----------------------------------------------------
select count(*) from LXSTRUC_37806708;
select count(*) from LXFOREIGN_37806708;
select count(*) from LXGRANT_37806708;
select count(*) from LXINTER_37806708;
select count(*) from LXWRGR_37806708;
select count(*) from LXRES_37806708;
select count(*) from LXUNIT_37806708;
select count(*) from LXVCCN_37806708;
select count(*) from LXLONG_37806708;
select count(*) from LXSLONG_37806708;
select count(*) from LXDRO_37806708;
select count(*) from LXOWNER_37806708;
----------------------------------------------------
select count(*) from LXMINT_37806708;
select count(*) from LXMSTR_37806708;
select count(*) from LXMDATE_37806708;
select count(*) from LXMREAL_37806708;
select count(*) from LXMBOOL_37806708;
select count(*) from LXPATH_37806708;
select count(*) from LXPE_37806708;
select count(*) from LXBINARY_37806708;
select count(*) from LXOID_E5C042E3;
select count(*) from LXIMG_E5C042E3;
select count(*) from LXDESC_E5C042E3;
select count(*) from LXBO_E5C042E3;
select count(*) from LXRO_E5C042E3;
select count(*) from LXSTRING_E5C042E3;
select count(*) from LXREAL_E5C042E3;
select count(*) from LXINT_E5C042E3;
select count(*) from LXDATE_E5C042E3;
----------------------------------------------------
select count(*) from LXBOOL_E5C042E3;
select count(*) from LXHIST_E5C042E3;
select count(*) from LXSTATE_E5C042E3;
select count(*) from LXSIGN_E5C042E3;
select count(*) from LXSET_E5C042E3;
select count(*) from LXFILE_E5C042E3;
select count(*) from LXINST_E5C042E3;
select count(*) from LXSTRUC_E5C042E3;
select count(*) from LXFOREIGN_E5C042E3;
select count(*) from LXGRANT_E5C042E3;
select count(*) from LXINTER_E5C042E3;
select count(*) from LXWRGR_E5C042E3;
select count(*) from LXRES_E5C042E3;
----------------------------------------------------
select count(*) from LXUNIT_E5C042E3;
select count(*) from LXVCCN_E5C042E3;
select count(*) from LXLONG_E5C042E3;
select count(*) from LXSLONG_E5C042E3;
select count(*) from LXDRO_E5C042E3;
select count(*) from LXOWNER_E5C042E3;
select count(*) from LXMINT_E5C042E3;
select count(*) from LXMSTR_E5C042E3;
select count(*) from LXMDATE_E5C042E3;
select count(*) from LXMREAL_E5C042E3;
select count(*) from LXMBOOL_E5C042E3;
select count(*) from LXPATH_E5C042E3;
select count(*) from LXPE_E5C042E3;
select count(*) from LXBINARY_E5C042E3;
----------------------------------------------------
select count(*) from LXOID_2B40FA89;
select count(*) from LXIMG_2B40FA89;
select count(*) from LXDESC_2B40FA89;
select count(*) from LXBO_2B40FA89;
select count(*) from LXRO_2B40FA89;
select count(*) from LXSTRING_2B40FA89;
select count(*) from LXREAL_2B40FA89;
select count(*) from LXINT_2B40FA89;
select count(*) from LXDATE_2B40FA89;
select count(*) from LXBOOL_2B40FA89;
select count(*) from LXHIST_2B40FA89;
select count(*) from LXSTATE_2B40FA89;
select count(*) from LXSIGN_2B40FA89;
select count(*) from LXSET_2B40FA89;
select count(*) from LXFILE_2B40FA89;
----------------------------------------------------
select count(*) from LXINST_2B40FA89;
select count(*) from LXSTRUC_2B40FA89;
select count(*) from LXFOREIGN_2B40FA89;
select count(*) from LXGRANT_2B40FA89;
select count(*) from LXINTER_2B40FA89;
select count(*) from LXWRGR_2B40FA89;
select count(*) from LXRES_2B40FA89;
select count(*) from LXUNIT_2B40FA89;
select count(*) from LXVCCN_2B40FA89;
----------------------------------------------------
select count(*) from LXLONG_2B40FA89;
select count(*) from LXSLONG_2B40FA89;
select count(*) from LXDRO_2B40FA89;
select count(*) from LXOWNER_2B40FA89;
select count(*) from LXMINT_2B40FA89;
select count(*) from LXMSTR_2B40FA89;
select count(*) from LXMDATE_2B40FA89;
select count(*) from LXMREAL_2B40FA89;
select count(*) from LXMBOOL_2B40FA89;
select count(*) from LXPATH_2B40FA89;
select count(*) from LXPE_2B40FA89;
----------------------------------------------------
select count(*) from LXBINARY_2B40FA89;
select count(*) from LXOID_8700B9BB;
select count(*) from LXIMG_8700B9BB;
select count(*) from LXDESC_8700B9BB;
select count(*) from LXBO_8700B9BB;
select count(*) from LXRO_8700B9BB;
select count(*) from LXSTRING_8700B9BB;
select count(*) from LXREAL_8700B9BB;
select count(*) from LXINT_8700B9BB;
select count(*) from LXDATE_8700B9BB;
----------------------------------------------------
select count(*) from LXBOOL_8700B9BB;
select count(*) from LXHIST_8700B9BB;
select count(*) from LXSTATE_8700B9BB;
select count(*) from LXSIGN_8700B9BB;
select count(*) from LXSET_8700B9BB;
select count(*) from LXFILE_8700B9BB;
select count(*) from LXINST_8700B9BB;
select count(*) from LXSTRUC_8700B9BB;
select count(*) from LXFOREIGN_8700B9BB;
select count(*) from LXGRANT_8700B9BB;
select count(*) from LXINTER_8700B9BB;
----------------------------------------------------
select count(*) from LXWRGR_8700B9BB;
select count(*) from LXRES_8700B9BB;
select count(*) from LXUNIT_8700B9BB;
select count(*) from LXVCCN_8700B9BB;
select count(*) from LXLONG_8700B9BB;
select count(*) from LXSLONG_8700B9BB;
select count(*) from LXDRO_8700B9BB;
select count(*) from LXOWNER_8700B9BB;
select count(*) from LXMINT_8700B9BB;
select count(*) from LXMSTR_8700B9BB;
select count(*) from LXMDATE_8700B9BB;
----------------------------------------------------
select count(*) from LXMREAL_8700B9BB;
select count(*) from LXMBOOL_8700B9BB;
select count(*) from LXPATH_8700B9BB;
select count(*) from LXPE_8700B9BB;
select count(*) from LXBINARY_8700B9BB;
select count(*) from IX65808C12_37806708;
select count(*) from IX65808C12_E5C042E3;
select count(*) from IX65808C12_2B40FA89;
select count(*) from IX65808C12_8700B9BB;
select count(*) from LXOID_8C8036A1;
----------------------------------------------------
select count(*) from LXIMG_8C8036A1;
select count(*) from LXDESC_8C8036A1;
select count(*) from LXBO_8C8036A1;
select count(*) from LXRO_8C8036A1;
select count(*) from LXSTRING_8C8036A1;
select count(*) from LXREAL_8C8036A1;
select count(*) from LXINT_8C8036A1;
select count(*) from LXDATE_8C8036A1;
select count(*) from LXBOOL_8C8036A1;
select count(*) from LXHIST_8C8036A1;
----------------------------------------------------
select count(*) from LXSTATE_8C8036A1;
select count(*) from LXSIGN_8C8036A1;
select count(*) from LXSET_8C8036A1;
select count(*) from LXFILE_8C8036A1;
select count(*) from LXINST_8C8036A1;
select count(*) from LXSTRUC_8C8036A1;
select count(*) from LXFOREIGN_8C8036A1;
select count(*) from LXGRANT_8C8036A1;
select count(*) from LXINTER_8C8036A1;
----------------------------------------------------
select count(*) from LXWRGR_8C8036A1;
select count(*) from LXRES_8C8036A1;
select count(*) from LXUNIT_8C8036A1;
select count(*) from LXVCCN_8C8036A1;
select count(*) from LXLONG_8C8036A1;
select count(*) from LXSLONG_8C8036A1;
select count(*) from LXDRO_8C8036A1;
select count(*) from LXOWNER_8C8036A1;
select count(*) from LXMINT_8C8036A1;
select count(*) from LXMSTR_8C8036A1;
----------------------------------------------------
select count(*) from LXMDATE_8C8036A1;
select count(*) from LXMREAL_8C8036A1;
select count(*) from LXMBOOL_8C8036A1;
select count(*) from LXPATH_8C8036A1;
select count(*) from LXPE_8C8036A1;
select count(*) from LXBINARY_8C8036A1;
select count(*) from IX65808C12_8C8036A1;
select count(*) from LXOID_5B8081F2;
select count(*) from LXIMG_5B8081F2;
select count(*) from LXDESC_5B8081F2;
select count(*) from LXBO_5B8081F2;
select count(*) from LXRO_5B8081F2;
select count(*) from LXSTRING_5B8081F2;
select count(*) from LXREAL_5B8081F2;
----------------------------------------------------
select count(*) from LXINT_5B8081F2;
select count(*) from LXDATE_5B8081F2;
select count(*) from LXBOOL_5B8081F2;
select count(*) from LXHIST_5B8081F2;
select count(*) from LXSTATE_5B8081F2;
select count(*) from LXSIGN_5B8081F2;
select count(*) from LXSET_5B8081F2;
select count(*) from LXFILE_5B8081F2;
select count(*) from LXINST_5B8081F2;
select count(*) from LXSTRUC_5B8081F2;
----------------------------------------------------
select count(*) from LXFOREIGN_5B8081F2;
select count(*) from LXGRANT_5B8081F2;
select count(*) from LXINTER_5B8081F2;
select count(*) from LXWRGR_5B8081F2;
select count(*) from LXRES_5B8081F2;
select count(*) from LXUNIT_5B8081F2;
select count(*) from LXVCCN_5B8081F2;
select count(*) from LXLONG_5B8081F2;
select count(*) from LXSLONG_5B8081F2;
select count(*) from LXDRO_5B8081F2;
select count(*) from LXOWNER_5B8081F2;
----------------------------------------------------
select count(*) from LXMINT_5B8081F2;
select count(*) from LXMSTR_5B8081F2;
select count(*) from LXMDATE_5B8081F2;
select count(*) from LXMREAL_5B8081F2;
select count(*) from LXMBOOL_5B8081F2;
select count(*) from LXPATH_5B8081F2;
select count(*) from LXPE_5B8081F2;
select count(*) from LXBINARY_5B8081F2;
select count(*) from IX65808C12_5B8081F2;
----------------------------------------------------
select count(*) from MXTRANSATTR;
select count(*) from MXSCHEMA;
select count(*) from MXOID;
select count(*) from MXICON;
select count(*) from MXDESC;
select count(*) from MXLATTICE;
select count(*) from MXACCESS;
select count(*) from MXTEMPOID;
select count(*) from LXTEMPSET_37806708;
select count(*) from LXTEMPSET_E5C042E3;
select count(*) from LXTEMPSET_2B40FA89;
select count(*) from LXTEMPSET_8700B9BB;
select count(*) from LXTEMPSET_8C8036A1;
select count(*) from LXTEMPSET_5B8081F2;
308 rows selected.
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY <PWD>
USING 'ASCPPROD';
DROP PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN";
CREATE PUBLIC DATABASE LINK "EBS_TO_ASCP.JSW_IN"
CONNECT TO APPS
IDENTIFIED BY apps
USING '172.16.2.34:1522/ASCPPROD';
changeonJavaMailerInstall
HOW TO COMPILE A RDF:
OPEN report builder>
click File>
Connect>
apps
apps
8007_TEST
Click ok>
Connected.
Now , Click on open Folder and browse the rdf file.
Click open.
Ctrl-K --compile.
/oraapps/oracle/product/TJTG/apps/apps_st/appl/fnd/12.0.0/patch/115/sql
wfntfqup.sql
171096TJ
How to Run Script wfmlrdbg.sql and Collect its Output? (Doc ID 1364300.1)
EBS Workflow Mailer Failing To Send Email Notifications With java.net.ConnectException: Connection refused After Environment Configuration Change
(Doc ID 1950783.1)
Workflow Mailer Not Sending Notifications java.net.ConnectException: Connection refused (errno:239)
(Doc ID 972161.1)
Problem Obtaining the HTML Content oracle.apps.fnd.wf.common.HTTPClientException (Doc ID 602971.1)
Java Notification Mailer Fails to Send Email Notifications from FormatterException with Framework Regions (Doc ID 339718.1)
==================================================
open cmd as system administrator
cd C:\Windows\System32\drivers\etc
>notepad hosts
add below entries...
172.16.2.33
171.16.2.32
171.16.2.65
171.16.2.66
171.16.2.34
171.16.2.81
172.16.2.34 TJTGB006.toshiba-tjps.in TJTGB006
ctrl-s.
A query like the following will show you current sessions generating redo
select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc;
lowing query will help you to find nummbers of archive generated per day
SELECT TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24'),
COUNT(*)
FROM V$LOGHIST
WHERE TO_CHAR(FIRST_TIME,'DD-MON-YY HH24') > '01-JUN-2018'
GROUP BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24')
ORDER BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24') ASC
Archive Daily:
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;
frmcmp_batch userid=apps/apps module=STGI_DISPATCH.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/forms/US/STGI_DISPATCH.fmx module_type=form batch=no compile_all=yes
************************************************************************************
[oraprod@tjpsb004 dbscript]$ cat tb_avg_inc_rate.sql
SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days, a.tsname, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) A
) b GROUP BY b.tsname ORDER BY b.tsname;
[oraprod@tjpsb004 dbscript]$ cat archive_daily.sql
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
[oraprod@tjpsb004 dbscript]$
[oraprod@tjpsb004 dbscript]$ cat archive_hourly.sql
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;
[oraprod@tjpsb004 dbscript]$ cat Estimate_Tablespace_Growth_In_Oracle.sql
set heading on
set linesize 5500
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
[oraprod@tjpsb004 dbscript]$ cat estimate_tbs_growth_oracle.sql
set heading on
set linesize 5500
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
[oraprod@tjpsb004 dbscript]$ cat grwth_rate_tbs.sql
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
========================
eDITED ONE
SELECT * FROM (
SELECT FU.START_DATE TESTD,fu.user_name user_id,
per.FULL_NAME user_name,
per.ATTRIBUTE2 location_of_EMP
,(SELECT DESCRIPTION FROM FND_FLEX_VALUES_TL WHERE FLEX_VALUE_ID IN
(SELECT FLEX_VALUE_ID FROM FND_FLEX_VALUES
WHERE FLEX_VALUE = per.ATTRIBUTE1 AND FLEX_VALUE_SET_ID = 1013708)) cost_center_desc,
per.ATTRIBUTE1 cost_center,
frt.responsibility_name,
(SELECT DESCRIPTION FROM FND_USER WHERE USER_ID = FURGD.CREATED_BY) RESPONSIBILITY_ADDED_BY,
furgd.start_date,
furgd.end_date
FROM fnd_user fu,
fnd_user_resp_groups_direct furgd,
fnd_responsibility_tl frt,
hr_employees per
WHERE fu.user_id = furgd.user_id
and per.EMPLOYEE_ID = fu.EMPLOYEE_ID
AND furgd.responsibility_id = frt.responsibility_id
AND TRUNC(FURGD.START_DATE) BETWEEN :F AND :T
AND TRUNC(FU.START_DATE) < TRUNC(FURGD.START_DATE)
and furgd.end_date is null and fu.END_DATE is null
order by fu.USER_NAME)
Create Necessary directories as per below:
/home/u02/app/
/home/u02/app/oracle/admin/orcl/adump'
/home/u02/app/oracle/oradata/cntrlorcl01.dbf,
/home/u02/app/oracle/flash_recovery_area/orcl/control02.dbf
/home/u02/app/diag/rdbms/orcl/ORCL/cdump
/home/u02/rmanbkp
/home/u02/archive
LOCARLIST
To download the script, go to EBS Financials for India GST Analyzer (Doc ID 2297311.1)
The Analyzer will contain all the same checks of the GST Diagnostic plus check to see what RUP level you are at as well as what one-offs you have applied from the following notes.
GST: Oracle Financials for India (OFI) RUP Patches (Doc ID 2253455.1)
GST : Phase 3 Approach to Final Release - Patch Details (Doc ID 2268465.1)
EBS Financials for India GST Analyzer (Doc ID 2297311.1)
/home/appldev
Installation process:
Running the Analyzer via SQL*Plus
Running the process via SQL*Plus can be used as an alternative method to running as a concurrent process.
1. Install the EBS Financials For India GST Analyzer by running il_gst_analyzer.sql as APPS to create the package definition.
This is required for the first time the analyzer is installed, and each time you download a new version of the analyzer.
sqlplus apps/<apps_password>
SQL> il_gst_analyzer.sql
2. Run the wrapper script il_gst_analyze.sql to submit the process as described below.
sqlplus apps/<apps_password>
SQL> il_gst_analyze.sql
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ILGSTAZ.ldt CUSTOM_MODE=FORCE
Enter the Customer Trx ID (LOCARLIST only): 199975
Files are located on Host : TJPSB007.ebs.in
Output file : /usr/tmp/ILGST_Analyzer_DEV_LOCARLIST_2018-07-06_12_32.html
Log file : /usr/tmp/ILGST_Analyzer_DEV_LOCARLIST_2018-07-06_12_32.log
STGI_PURCHASE_REGISTER
http://www.appsgeyser.com/dashboard/
amin.aiemd
https://support.google.com/googleplay/android-developer/answer/113474
http://www.appsgeyser.com/dashboard/distribute/android
00 03 * * * sh /TJPSB004_BACKUP/rman_scripts/TJTGfullbk.sh
ENOVIA_16jULY2018
###Full Rman Backup at 6 AM ######
00 06 * * * sh /u01/backup/script/backup.sh
/u01/backup/script/rmanscript.sql
run
{
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
DELETE noprompt BACKUP COMPLETED BEFORE 'SYSDATE-2';
DELETE noprompt archivelog all COMPLETED BEFORE 'SYSDATE-1';
backup as compressed backupset database format '/u01/backup/rman/Fullback_%T_%U' Plus archivelog format '/u01/backup/rman/Archive_%T_%U';
backup current controlfile format '/u01/backup/rman/Controlback_%T_%U';
release channel ch1;
}
exit
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/archive/ctrl_%F';
9841840016
43610553865
TJPS_Receipt_Invoice_Apply
https://dbatricksworld.com/oracle-11g-manual-online-hot-backup/
https://dbatricksworld.com/restore-oracle-hot-backup/
wf_advanced_worklist
tjps1048
140820TJ Sivakumar S prabhu.kuppusamy@toshiba-tjps.in
140807TJ 574
140769TJ Harish
120274TJ
expdp system/system directory=data_pump_dir dumpfile=Enovia_Full_17Jul2018.dmp logfile=Enovia_Full_17Jul2018.log full=y
expdp system/system directory=data_pump_dir dumpfile=Enovia_Full_17Jul2018.dmp logfile=Enovia_Full_17Jul2018.log full=y
as sysdba
expdp schemas=V6R2015X directory=DATA_PUMP_DIR dumpfile=V6R2015X_13AUG18.dmp logfile=V6R2015X_13AUG18.log
Username: system
system
scp -r V6R2015X_17JULY18.dmp V6R2015X@10.116.8.8:/tmp
/mntenovia/bkp_datapump_24July
expdp schemas=V6R2015X directory=DATA_PUMP_DIR dumpfile=V6R2015X_24JULY18.dmp logfile=V6R2015X_24JULY18.log
Username: system
system
expdp system/system directory=data_pump_dir dumpfile=Enovia_Full_24Jul2018.dmp logfile=Enovia_Full_24Jul2018.log full=y
as sysdba
TJPS_GP_PENDING_RPT
scp -r Enovia_Full_17Jul2018.dmp V6R2015X@10.116.8.8:/tmp
/oraapps/oracle/product/TJTG/apps/apps_st/comn/java/classes/logs
MT940:
/oraapps/oracle/product/SCBConfig/TJSWIN01/in/stmt
Please run the LOCSTATUS Note 2297311.1 in 8010 instance and send me to raise a SR with Oracle Support.
E:\siva\2018\Jul\18
select home_url from icx_parameters;
appluat
orauat
/home/appluat/il_gst_analyzer
SQL> select name, text from DBA_SOURCE
where name in UPPER('il_gst_analyzer_pkg')and text like '%$Id%'; 2
NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
IL_GST_ANALYZER_PKG
-- $Id: il_gst_analyzer.sql, 200.4 2017/10/03 08:57:57 JEFF.MCCALL@ORACLE.COM Ex
p $
sqlplus apps/<apps_password>
SQL> @il_gst_analyzer.sql
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ILGSTAZ.ldt CUSTOM_MODE=FORCE
SQL> @il_gst_analyze.sql
Files are located on Host : TJPSB007.ebs.in
Output file : /usr/tmp/ILGST_Analyzer_UAT_LOCSTATUS_2018-07-18_09_11.html
Log file : /usr/tmp/ILGST_Analyzer_UAT_LOCSTATUS_2018-07-18_09_11.log
Please compile the JAINTAXT.fmb and JAINTAXT.pll files in JA_TOP in 8010 instance and confirm
As per below oracle SR update
/GSTAPP/app/appluat/apps_st/appl/ja/12.0.0/forms/US
JAINTAXT.fmx
PROD SIDE:
cd /oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/resource/
mv JAINTAXT.plx JAINTAXT.plx_18JUL2018
cd /oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/resource/
/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/resource/JAINTAXT.pll
frmcmp_batch userid=apps/JzavetOVDr6LKua module=JAINTAXT.pll output_file=/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/resource/JAINTAXT.plx module_type=library batch=yes compile_all=special
cd /oraapps/oracle/product/TJTG/apps/apps_st/appl/ja/12.0.0/forms/US/
mv JAINTAXT.fmx JAINTAXT.fmx_18JUL2018
cd /oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US/JAINTAXT.fmb
frmcmp_batch userid=apps/JzavetOVDr6LKua module=JAINTAXT.fmb output_file=/oraapps/oracle/product/TJTG/apps/apps_st/appl/ja/12.0.0/forms/US/JAINTAXT.fmx module_type=form batch=no compile_all=yes
select to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') from dual;
Internet Expenses Audit Manager
xx_dispatch_receipt_t
E:\kumaresan\2018\July\19
/OratestC/app/oratest/db/11.2.0/dbs/initTEST.ora
SQL> startup mount pfile='/OratestC/app/oratest/db/11.2.0/dbs/initTEST.ora';
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory
startup pfile='/OratestC/app/oratest/db/11.2.0/dbs/initTEST.ora';
ORA-00205: error in identifying control file, check alert log for more info
*.control_files='/GSTDB/oradata/TEST/cntrl01.dbf','/GSTDB/oradata/TEST/cntrl02.dbf','/GSTDB/oradata/TEST/cntrl03.dbf'
/OratestC/oradata/TEST/cntrl01.dbf
/OratestC/oradata/TEST/cntrl02.dbf
/OratestC/oradata/TEST/cntrl03.dbf
.control_files='/OratestC/oradata/TEST/cntrl01.dbf','/OratestC/oradata/TEST/cntrl02.dbf','/OratestC/oradata/TEST/cntrl03.dbf'
sh /TJTGHRMS/tomcat6/bin/startup.sh
sh /TJTGHRMS/tomcat620/bin/startup.sh
sh /TJTGHRMS/tomcat6/bin/shutdown.sh
sh /TJTGHRMS/tomcat620/bin/shutdown.sh
====================================================
afterstudy.in
bridgecourse.in
studypractical.com
studywell.in
quality education:quduc.com
aliacourse.com
aliastudy.com
aliaedu.com
byzoos.com
stdwell.com
extrasubject.com
http://www.bookadsnow.com/newspaper
ReleaseMyAd
E:\kumaresan\2018\July\26
TJPS_PROJECT_OPEN_PO_EKKK
JzavetOVDr6LKua
Vels4QmeW,hUmTP
TJTG:/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/reports/US
dba_rollback_segs
col TABLESPACE_NAME for a14
col SEGMENT_NAME for a22
col STATUS for a11
select SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS from dba_rollback_segs ;
select count(*) from xx_mv_team_alert_tab
2495
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
8007:sysadmin/welcome21
E:\jp\2018\July\27
XXTJ_GRN_PRINT
How to check Database Size, Used and Free Space in a Database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
prod
Database Size Used space Free space
-------------------- -------------------- --------------------
1452 GB 996 GB 456 GB
https://support.google.com/youtube/answer/6332943?hl=en
https://support.google.com/youtube/answer/7610994
AMAZON
https://www.networkworld.com/article/2932962/cloud-storage/19-free-cloud-storage-options.html
https://www.amazon.com/b?ie=UTF8&node=16591160011
https://awstcocalculator.com/#
https://oracle.valuestoryapp.com/iaas/
https://www.namecheap.com/hosting/shared.aspx
https://www.dreamhost.com/hosting/shared/
https://www.bluehost.in/dedicated-servers.php
http://web2host.in/windows-hosting/
Please note this reference number: P02376943 dated : 01-Aug-2018 [11:34 AM IST]
===============================
Show segments that are approaching max_extents
List invalid objects
Display all datafiles, tempfiles and logfiles (and their sizes)
Whats in undo?
UPDATE MTL_MATERIAL_TRANSACTIONS MMT SET COSTED_FLAG
File I/O stats
Show the buffer cache advisory
Top ten hottest objects by access
Waits by file
Waits by segment
analyzed tables broken down by schema
Quick look at SGA
PGA usage by username
Display pool usage
to $TJTG_TOP/report/US.
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/reports/US
HRIS:
Database Size Used space Free space
-------------------- -------------------- --------------------
40 GB 22 GB 18 GB
ASCP:
SQL> @Tot_Size_DB.sql
Database Size Used space Free space
-------------------- -------------------- --------------------
232 GB 141 GB 91 GB
346499
E:\AUDIT_18\APR_MAY_JUNE_JULY_2018\Additional Responsibility
-rwxrwxr-x 1 applprod dba 188416 Jun 28 09:09 XXMZBK_MPXLS.rdf
=======================
SQL> shut immediate
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '/ASCPDB/oradata/TJTGHRT/system01.dbf'
8007:
/GSTDB/app/oratest/db/11.2.0/admin/TEST_tjpsb007/diag/rdbms/test/TEST/trace
http://tjtgb006.toshiba-tjps.in:8001/OA_HTML/AppsLogin
select fnd_message.get from dual;
APPS_TS_INTERFACE
select NAME,TS#,STATUS from v$datafile;
SQL> select * from v$tablespace where name='APPS_TS_INTERFACE';
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
384 APPS_TS_INTERFACE YES NO YES
select NAME,TS#,STATUS from v$datafile where TS#=384;
SQL> select NAME,TS#,STATUS from v$datafile where TS#=384;
NAME
--------------------------------------------------------------------------------
TS# STATUS
---------- -------
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int02.dbf
384 ONLINE
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int01.dbf
384 ONLINE
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int03.dbf
alter tablespace APPS_TS_INTERFACE add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int03.dbf' size 1000m;
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/Apache
-rw-r----- 1 aascpprd dba 207 Aug 13 07:51 error_log.1534118400
-rw-r----- 1 aascpprd dba 54334 Aug 13 08:38 access_log.1534118400
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1
-rw-r----- 1 aascpprd dba 493425 Aug 13 08:08 application.log
-rw-r----- 1 aascpprd dba 2797 Aug 12 16:39 global-application.log
-rw-r----- 1 aascpprd dba 1456 Aug 12 16:39 jms.log
-rw-r----- 1 aascpprd dba 12902 Aug 12 16:39 log.xml
-rw-r----- 1 aascpprd dba 1461 Aug 12 16:39 rmi.log
-rw-r----- 1 aascpprd dba 1909 Aug 12 16:39 server.log
-rw-r----- 1 aascpprd dba 451 Aug 12 16:39 system-application.log
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn
-rw-r----- 1 aascpprd dba 65722 Aug 13 06:53 default_group~forms~default_group~1.log
-rw-r----- 1 aascpprd dba 57357 Aug 13 03:29 default_group~oacore~default_group~1.log
-rw-r----- 1 aascpprd dba 83489 Aug 13 07:30 default_group~oafm~default_group~1.log
drwxr-x--- 2 aascpprd dba 4096 Jun 15 11:13 forms_default_group_1
-rw-r----- 1 aascpprd dba 4085 Aug 12 16:39 HTTP_Server~1.log
drwxr-x--- 2 aascpprd dba 4096 Jun 15 11:12 oacore_default_group_1
drwxr-x--- 2 aascpprd dba 4096 Jun 15 11:13 oafm_default_group_1
-rw-r----- 1 aascpprd dba 0 Jun 15 11:12 opmn.dbg
-rw-r----- 1 aascpprd dba 0 Aug 12 18:28 opmn.log
-rw-r----- 1 aascpprd dba 5720 Jun 17 11:56 opmn.log.18-06-17_11:57:06
-rw-r----- 1 aascpprd dba 3754 Jun 19 06:59 opmn.log.18-06-19_07:00:00
-rw-r----- 1 aascpprd dba 21286 Jun 19 18:07 opmn.log.18-06-20_00:00:29
-rw-r----- 1 aascpprd dba 1544 Jun 22 16:35 opmn.log.18-06-22_16:35:58
-rw-r----- 1 aascpprd dba 2210 Jun 22 20:02 opmn.log.18-06-23_00:00:15
-rw-r----- 1 aascpprd dba 958 Aug 11 18:27 opmn.log.18-08-11_18:28:00
-rw-r----- 1 aascpprd dba 6550 Aug 12 16:40 opmn.log.18-08-12_18:28:49
-rw-r----- 1 aascpprd dba 0 Jun 15 11:12 opmn.out
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn/oacore_default_group_1
18/08/12 16:39:52 Error: <connector name="OracleASjms" path="OracleASjms.rar" />
will not be bootstrapped since corresponding module declaration was not found in application.xml.
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn/oafm_default_group_1
oafmstd.out
Caused by: javax.resource.ResourceException: oracle.oc4j.sql.DataSourceException: Exception setting the property 'dbcFile' on the DataSource. Exception: java.lang.reflect.InvocationTargetException
at oracle.oc4j.sql.spi.ResourceAdapterImpl.createDataSourceConnectionPool(ResourceAdapterImpl.java:104)
at oracle.oc4j.sql.DataSourceUtils.getDataSourceConnectionPool(DataSourceUtils.java:325)
at com.evermind.server.ApplicationStateRunning.initDataSourceConnectionPool(ApplicationStateRunning.java:2121)
... 11 more
dbc:/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/appl/fnd/12.0.0/secure
/etc/init.d/vncserver-x11-serviced start
/opt/vnc_unixsrc/tightvncserver.conf
/opt/vnc_unixsrc/vncserver
/opt/vnc_unixsrc/vncserver.init
/opt/vnc_unixsrc/vncserver.man
/os/Packages/libvncserver-0.9.7-4.el6.x86_64.rpm
/vnc/vncserver
[root@tjpsb003 vnc]# ./vncserver start
vncserver: couldn't find "Xvnc" on your PATH.
/opt/vnc_unixsrc/Xvnc
PATH=$PATH:/opt/vnc_unixsrc/Xvnc:
export PATH
PATH=$PATH:/usr/X11/bin:/usr/openwin/bin
PATH=$PATH:/usr/X/bin:/usr/X11/bin
export PATH
EAM
ls -1 | wc -l
CPU (Critical Patch Update18)
3591-approved
3618-Approved in Ticket.
E:\siva\2018\Aug\21
TJPS_ROL_PRICE_UPLOAD
/oraapps/oracle/product/TJTG/apps/apps_st/custom/TJTG/12.0.0/bin
backup database plus archivelog;
/ascptest_db/htest/11.2.0/dbs/
Take backup of rman :
backup database plus archive log;
1.delete system01.dbf in test instance.
2.shut immediate . Through an error.
3.connect rman target /
4.restore datafile 1.
rman >restore datafile 1;
RMAN> restore datafile 1;
Starting restore at 21-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /ascptest_db/htest/oradata/system01.dbf
channel ORA_DISK_1: reading from backup piece /ascptest_db/htest/11.2.0/dbs/0ttb48sr_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/21/2018 14:35:53
ORA-19870: error while restoring backup piece /ascptest_db/htest/11.2.0/dbs/0ttb48sr_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
130421TJ
https://techgoeasy.com/oracle-tutorials/e-business-suite-11ir12r12-1r12-2/
appsdba questions
Mukesh Kumar Pandey (181197TJ) Mukesh.pandey@toshiba-tjps.in
enovia rman :
/home/oracle/dbscript
Memory Based Planner 64-bit Linux
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/appl/conc/log
/ASCPPROD_DB_new
udemy online tutpr:
https://preply.com/en/tutor/69104/
https://www.teacheron.com/math-tutors-in-india
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.2/network/apps_ascpprod.log
cd /ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn/forms_default_group_1
tail -900f formsstd.err
18/08/13 11:12:58 Error: <connector name="OracleASjms" path="OracleASjms.rar" /> will not be bootstrapped since corresponding module declaration was not found in application.xml.
cd /ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn/forms_default_group_1
tail -900f formsstd.out
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/ora/10.1.2/forms/server/appsweb.cfg
/ASCPPROD_APP/oracle/product/ASCPPROD/inst/apps/ASCPPROD_tjtgb006/logs/ora/10.1.3/opmn/oafm_default_group_1
tail -900f oafmstd.out
18/08/13 10:47:47 Shutting down...
18/08/13 11:13:04 WARNING: ApplicationStateRunning.loadConnectors Exception loading connector AppsSOAAdapter : Unable to find/read file META-INF/ra.xml in /ASCPPROD_APP/oracle/product/ASCPPROD/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter archive (IO Error: /ASCPPROD_APP/oracle/product/ASCPPROD/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter (No such file or directory))
18/08/13 11:13:05 INFO: SecuritySensitive.lookupException password is not available for indirect password via application context (user: ASADMIN)
18/08/13 11:13:06 SEVERE: ApplicationStateRunning.initConnector Error occurred initializing connectors. Exception is: Exception creating connection pool. Exception: oracle.oc4j.sql.DataSourceException: Exception setting the property 'dbcFile' on the DataSource. Exception: java.lang.reflect.InvocationTargetException
18/08/13 11:13:06 SEVERE: ApplicationStateRunning.initConnector Stack trace: oracle.oc4j.sql.DataSourceException: Exception creating connection pool. Exception: oracle.oc4j.sql.DataSourceException: Exception setting the property 'dbcFile' on the DataSource. Exception: java.lang.reflect.InvocationTargetException
at com.evermind.server.ApplicationStateRunning.initDataSourceConnectionPool(ApplicationStateRunning.java:2142)
at com.evermind.server.ApplicationStateRunning.initDataSourceConnector(ApplicationStateRunning.java:2085)
at com.evermind.server.ApplicationStateRunning.initConnector(ApplicationStateRunning.java:1317)
at com.evermind.server.ApplicationStateRunning.initConnectors(ApplicationStateRunning.java:366)
at com.evermind.server.ApplicationStateRunning.initializeApplication(ApplicationStateRunning.java:224)
at com.evermind.server.Application.setConfig(Application.java:460)
at com.evermind.server.Application.setConfig(Application.java:361)
at com.evermind.server.ApplicationServer.addApplication(ApplicationServer.java:1919)
at com.evermind.server.ApplicationServer.initializeDeployedApplications(ApplicationServer.java:1667)
at com.evermind.server.ApplicationServer.setConfig(ApplicationServer.java:1050)
at com.evermind.server.ApplicationServerLauncher.run(ApplicationServerLauncher.java:131)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.resource.ResourceException: oracle.oc4j.sql.DataSourceException: Exception setting the property 'dbcFile' on the DataSource. Exception: java.lang.reflect.InvocationTargetException
120212TJ KUMAR MYSORE NARASIMHANNA
STGI GATEPASS ENTRY
http://allaboutoracleapplication.blogspot.com/2014/01/oracle-ascp-standard-collection.html
SR 3-18201601511 : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
adoacorectl.sh: exiting with status 204
exiting with status 150
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*
++++++++++++++++++++++++++++++
export ORACLE_SID=ASR
export ORACLE_UNQNAME=ASR
export ORACLE_HOME=/uatdb/app/oraascp/db/11.2.0/
export PATH=$PATH:/uatdb/app/oraascp/db/11.2.0/bin/
3) Database service ASR is not registered with listener. Register the database service.
===========
emca -config dbcontrol db -repos create
==================================================================
>>>How to create SYSMAN user manually in Oracle database
Oracle Home => /eu01/app/oracle/product/11.2.0.4/dbhome_1
Username => SYSMAN
Password => sys123
Temporary tablespace name => TEMP
Verify On/Off => ON
Step 2: The script emRepository.sql actually creates SYSMAN user in the database if it does not exists..
The script is located in the below directory.
$ORACLE_HOME/sysman/admin/emdrep/sql/emreposcre.sql
emRepository.sql is one of the scripts that are normally run when you create a new Database.
DBCA puts these scripts, normally, in $ORACLE_BASE/admin/<DB>/scripts.
The script emRepository.sql calls an another Sql script emreposcre.sql by passing below 5 parameters :
cd /uatdb/app/oraascp/db/11.2.0/sysman/admin/emdrep/sql/emreposcre.sql
•Oracle Home:/uatdb/app/oraascp/db/11.2.0
•Username:SYSMAN
•Password:sys123
•Temporary tablespace name:TEMP9
•Verify On/Off:on
61
21
--
1582
welcome321
http://oracledbajourney.blogspot.com/2014/06/purge-inactive-sessions.html
E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql
select min(first_connect),count(*) from icx.icx_sessions;
SQL> select min(first_connect),count(*) from icx.icx_sessions;
MIN(FIRST_CONNE COUNT(*)
--------------- ----------
26-JUN-03 973195
/ORADB/app/orauat/11.2.0/admin/UAT_tjpsb007/diag/rdbms/uat/UAT/trace
UAT_ora_8236_120276TJ.trc
tkprof <tracefile> <outputfile> sort=fchela,exeela,prsela waits=yes sys=no
tkprof UAT_ora_8236_120276TJ.trc UAT_ora_8236_120276TJ.txt sort=fchela,exeela,prsela waits=yes sys=no
How to Generate Trace for Forms, Concurrent Processes, Java and Self Service (Doc ID 1432633.1)
select * from dba_audit_trail where trunc(TIMESTAMP) >= '01-APR-2018' and trunc(TIMESTAMP) <= '01-MAY-2018';
decide
write down
set deadline
Make list of achieve
Organise list by sequence & priority
Take Action Immediatelly
Resolve issue
Proper Prior
Planning Prevents Poor Performance."
When you make out your list the evening or the night before,
your subconscious mind works on your list all night long while you
sleep
1.
create a master list on which you write down everything
you should have a monthly list that you make up at the end
of the month for the month ahead. This may contain items
transferred from your master list.
weekly list where you plan your entire
week in advance.
One of the most important rules of personal effectiveness is the 10/90
Rule. This rule says that the first 10% of time that you spend
planning and organizing your work, before you begin, will save you
as much as 90% of the time in getting the job done once you get
started.
3
Apply the 80/20 Rule to Everything
For example, this rule says that 20% of your activities will account for
80% of your results. 20% of your customers will account for 80% of
your sales. 20% of your products or services will account for 80% of
your profits. 20% of your tasks will account for 80% of the value of
what you do, and so on.
This means that if you have a list of ten items to do, two of those
items will turn out to be worth as much or more than the other eight
items put together.
Number of Tasks versus Importance of Tasks
4.
Consider the Consequences
"Long-term thinking improves short-term decision making
There is never enough time to do everything, but
there is always enough time to do the most important thing."
Three Questions for Maximum Productivity
What are my highest value activities?"
what are the biggest frogs that you have to eat to
make the greatest contribution to your organization? To your family?
To your life in general?
This is one of the most important questions you can ask and answer.
What are your highest value activities? First, think this through for
yourself. Then, ask your boss. Ask your coworkers and subordinates.
Ask your friends and family. Like focusing the lens of a camera, you
must be crystal clear about your highest value activities before you
begin work.
"What can I and only I
do, that if done well, will make a real difference?"
This question comes from Peter Drucker, the management guru. It is
one of the best of all questions for achieving personal effectiveness.
What can you, and only you do, that if done well, can make a real
difference?
"What is the most valuable use of my
time, right now?"
What is my biggest frog of all at this moment?
"Just begin and the mind grows heated;
continue, and the task will be completed!"
5.
Practice Creative Procrastination
"Make time for getting big tasks done every day.
Plan your daily workload in advance. Single out the relatively few small jobs
that absolutely must be done immediately in the morning. Then go directly
to the big tasks and pursue them to completion."
Boardroom Reports
the action of delaying or postponing something:procrastination
Creative procrastination is one of the most effective of all personal
performance techniques. It can change your life.
Therefore, procrastinate on small tasks.
Eat the biggest and ugliest
frogs before anything else. Do the worst first!
The difference between high performers
and low performers is largely determined by what they choose to
procrastinate on.
A priority is something that you do more of and
sooner, while a posteriority is something that you do less of and later,
if at all.
"Your dance card is full."
6.
Use the ABCDE Method Continually
"The first law of success is concentration –
to bend all the energies to one point, and to go directly to that point,
looking neither to the right or to the left."
An "A" item is defined as something that is very important. This is
something that you must do. This is a task for which there can be
serious consequences if you do it or fail to do it, like visiting a key
customer or finishing a report for your boss that she needs for an
upcoming board meeting. These are the frogs of your life.
If you have more than one "A" task, you prioritize these tasks by
writing A-1, A-2, A-3, and so on in front of each item. Your A-1
task is your biggest, ugliest frog of all.
"Shoulds" versus "Musts"
A "B" item is defined as a task that you should do. But it only has mild
consequences. These are the tadpoles of your work life. This means
that someone may be unhappy or inconvenienced if you don't do it,
but it is nowhere as important as an "A" task. Returning an
unimportant telephone message or reviewing your email would be a
"B" task.
The rule is that you should never do a "B" task when there is an "A"
task left undone.
You should never be distracted by a tadpole when
there is a big frog sitting there waiting to be eaten.
A "C" task is defined as something that would be nice to do, but for
which there are no consequences at all, whether you do it or not. "C"
tasks include phoning a friend, having coffee or lunch with a
coworker or completing some personal business during work hours.
This sort of activity has no affect at all on your work life.
A "D" task is defined as something you can delegate to someone else.
The rule is that you should delegate everything that anyone else can
do so that you can free up more time for the "A" tasks that only you
can do.
An
An "E" task is defined as something that you can eliminate altogether
and it won't make any real difference.
Eat the whole frog and don’t stop until it’s
finished completely. A1 job
Your ability to think through, analyze your work list and determine
your "A-1" task is the springboard to higher levels of
accomplishment, and greater self-esteem, self-respect and personal
pride
7.
Focus On Key Result Areas
"When every physical and mental resource is focused,
one’s power to solve a problem multiplies tremendously."
As it happens, most people are not sure exactly why they are on the
payroll. But if you are not crystal clear about why it is that you are on
the payroll and what results you have been hired to accomplish, it is
very hard for you to perform at your best, get paid more and
promoted faster.
wage or a salary is a payment for a specific quality and quantity of
work that can be combined with the work of others to create a
product or service that customers are willing to pay for.
By the same token, your failure to perform in a critical
result area of your work can lead to the end of your job as well.
Planning,
Organizing, Staffing, Delegating, Supervising, Measuring and
Reporting.
The key result areas of salespeople are: Prospecting, Building
Rapport and Trust, Identifying Needs, Presenting Persuasively,
Answering Objections, Closing the Sale, and Getting Resales and
Referrals.
The starting point of high performance is for you to first of all
identify the key result areas of your work
Make a list of your most important output responsibilities and
make sure that the people above you, next to you and below you are
in agreement with it.
Rule: Your weakest key result area sets the height at which you can
use all your other skills and abilities.
Just think! You may be only one critical
skill away from top performance at your job.
"What one skill, if I developed and did it in an excellent fashion,
would have the greatest positive impact on my career?"
The good news is that all business skills are learnable. If anyone else is
excellent in that particular key result area, this is proof that you can
become excellent as well, if you decide to.
This can be as important as anything else you do in
your life or your career.
Make a habit of doing this analysis regularly for the rest of your
career. Never stop improving. This decision alone can change your
life.
8.
The Law of Three
""Do what you can with what you have right where you are."
The Quick List Method
give them a sheet of paper and then ask them, "In 30
seconds, write down your three most important goals in life, right
now."
1. What are your three most important business or career goals,
right now?
2. What are your three most important family or relationship
goals, right now?
3. What are your three most important financial goals, right now?
4. What are your three most important health goals, right now?
5. What are your three most important personal and professional
development goals, right now?
6. What are your three most important social and community
goals, right now?
7. What are your three biggest problems or concerns in life, right
now?
Rule: It is quality of time at work that counts and quantity of time at
home that matters.
9
Prepare Thoroughly Before You Begin
"No matter what the level of your ability,
you have more potential than you can ever develop in a lifetime."
James T. McKay
"the death of fear is certain."
"You miss every
shot you don’t take."
10
Take It One Oil Barrel at A Time
"Persons with comparatively moderate powers will accomplish much
if they apply themselves wholly and indefatigably to one thing at a time."
"one oil barrel at a time."
"Leap — and the net will
appear!"
A great life, a great career is built by performing one task at a time,
quickly and well, and then going on to the next task.
Financial independence is achieved by saving a little money every
single month, year after year. Health and fitness are accomplished by
just eating a little less and exercising a little more, day after day and
month after month.
11
Upgrade Your Key Skills
The only certain means of success is to render more and better service than
is expected of you, no matter what your task may be."
"If
you're not getting better, you're getting worse."
Never Stop Learning
School is never out for the professional.
Increase Your Earning Ability
Your most valuable asset, in terms of cash flow, is your "earning
ability." Your ability to work enables you to bring tens of thousands
of dollars into your life every year by simply applying your
knowledge and skills to your world. This is your ability to eat specific
frogs faster and better than others.
Identify Your Key Constraints
"Concentrate all your thoughts on the task at hand.
The sun’s rays do not burn until brought to a focus."
the purpose of a business is to create and keep a customer.
By doing this in sufficient quantities, the company makes a profit and
continues to grow and flourish.
"What is it in me that is holding me back?"
They accept
complete responsibility for their lives and look to themselves for both
the cause and cure of their problems.
Keep asking, "What sets the speed at which I get the results I want?"
14.
Put the Pressure on Yourself
The first requisite for success is to apply your physical and mental energies
to one problem incessantly without growing weary."
"your reputation with yourself."
15
Maximize Your Personal Powers
"Gather in your resources, rally all your faculties, marshal all your energies,
focus all your capacities upon mastery of at least one field of endeavor."
One of the most important requirements for being happy and
productive is for you to guard and nurture your energy levels at all
times.
Guard Your Physical Health
In addition to lots of rest, and to keep your energy levels at their
highest, be careful about what you eat. Start the day with a high
protein, low fat and low carbohydrate breakfast. Eat salads with fish
or chicken at lunch. Avoid sugar, salt, white flour products or
deserts. Avoid soft drinks and candy bars or pastries. Feed yourself
as you would feed a world class athlete before a competition, because
in many respects, that’s what you are before starting work each day.
Aim to exercise about 200 minutes each week,
1) What am I doing physically that I should do more of?
2) What am I doing that I should do less of?
3) What am I not doing that I should start doing if I want to perform
at my best?
4) What am I doing today that affects my health that I should stop
doing altogether?
Motivate Yourself into Action
"It is in the compelling zest of high adventure and of victory,
and of creative action that man finds his supreme joys."
book:
As Victor Frankl
wrote in his best selling book, Logotherapy, "The last great freedom
of mankind is the freedom to choose your attitude under any set of
external conditions."
17
CHAPTER 17
Get Out Of the Technological Time Sinks
"There is more to life than just increasing its speed."
Ghandhi
Successful salespeople set aside a specific time period each day to
phone prospects. Rather than procrastinating or delaying on a task
that they don’t particularly like, they resolve that they will phone for
one solid hour between 10:00 and 11:00 AM. They then discipline
themselves to follow through on their resolutions.
Develop A Sense of Urgency
Do not wait; the time will never be ‘just right.’ Start where you stand,
and work with whatever tools you may have at your command,
and better tools will be found as you go along."
"Do it now! Do it now! Do it now!"
Single Handle Every Task
And herein lies the secret of true power. Learn, by constant practice,
how to husband your resources, and concentrate them,
at any given moment, upon a given point."
And when you develop the habit of starting on your most
important task, before anything else, your success is assured.
1. Set the table: Decide exactly what you want. Clarity is essential.
Write out your goals and objectives before you begin;
2. Plan every day in advance: Think on paper. Every minute you
spend in planning can save you five or ten minutes in execution;
3. Apply the 80/20 Rule to everything: Twenty percent of your
activities will account for eighty percent of your results. Always
concentrate your efforts on that top twenty percent;
4. Consider the consequences: Your most important tasks and
priorities are those that can have the most serious consequences,
positive or negative, on your life or work. Focus on these above all
else;
5. Practice creative procrastination: Since you can’t do everything,
you must learn to deliberately put off those tasks that are of low
value so that you have enough time to do the few things that
really count;
6. Use the ABCDE Method continually: Before you begin work on a
list of tasks, take a few moments to organize them by value and
priority so you can be sure of working on your most important
activities:
7. Focus on key result areas: Identify and determine those results
that you absolutely, positively have to get to do your job well, and
work on them all day long;
8. The Law of Three: Identify the three things you do in your work
that account for 90% of your contribution and focus on getting
them done before anything else. You will then have more time for
your family and personal life;
9. Prepare thoroughly before you begin: have everything you need
at hand before you start. Assemble all papers, information, tools,
work materials and numbers so that you can get started and keep
going.
10. Take it one oil barrel at a time: You can accomplish the biggest
and most complicated job if you just complete it one step at a time;
11. Upgrade your key skills: The more knowledgeable and skilled
you become at your key tasks, the faster you start them and the
sooner you get them done;
12. Leverage your special talents: Determine exactly what it is that
you are very good at doing, or could be very good at, and throw
your whole heart into doing those specific things very, very well:
13. Identify your key constraints: Determine the bottlenecks or
chokepoints, internally or externally, that set the speed at which
you achieve your most important goals and focus on alleviating
them;
14. Put the pressure on yourself: Imagine that you have to leave
town for a month and work as if you had to get all your major
tasks completed before you left;
15. Maximize your personal powers: Identify your periods of highest
mental and physical energy each day and structure your most
important and demanding tasks around these times. Get lots of
rest so you can perform at your best;
16. Motivate yourself into action: Be your own cheerleader. Look for
the good in every situation. Focus on the solution rather than the
problem. Always be optimistic and constructive;
17. Get Out of The Technological Time Sinks: Use technology to
improve the quality of your communications, but do not allow
yourself to become a slave to. Learn to occasionally turn things off,
and leave them off;
18. Slice and dice the task: Break large, complex tasks down into bite
sized pieces and then just do one small part of the task to get
started;
19. Create large chunks of time: Organize your days around large
blocks of time where you can concentrate for extended periods on
your most important tasks;
20. Develop a sense of urgency: Make a habit of moving fast on your
key tasks. Become known as a person who does things quickly
and well;
21. Single handle every task: Set clear priorities, start immediately
on your most important task and then work without stopping
until the job is 100% complete. This is the real key to high
performance and maximum personal productivity.
Just do it! Eat that frog.
Patch 10389190 is there for 12.1.3+
MSCCLBAB.pls 120.86.12010000.11
$MSC_TOP
/oraapps/oracle/product/TJTG/apps/apps_st/appl/msc/12.0.0/patch/115/sql
[applprod@tjpsb005 sql]$ ll MSCCLBA*
-rwxr-xr-x 1 applprod dba 315699 Jan 10 2015 MSCCLBAB.pls
-rwxr-xr-x 1 applprod dba 26752 Jan 10 2015 MSCCLBAS.pls
[applprod@tjpsb005 sql]$ adident Header MSCCLBAB.pls
MSCCLBAB.pls:
$Header MSCCLBAB.pls 120.86.12010000.31 2013/08/26 11:30:33 beitang ship $
$Header MSCCLBAB.pls 120.86.12010000.31 2013/08/26 11:30:33 beitang ship $
ASCP:
/ASCPPROD_APP/oracle/product/ASCPPROD/apps/apps_st/appl/msc/12.0.0/patch/115/sql
[aascpprd@TJTGB006 sql]$ adident Header MSCCLBAB.pls
MSCCLBAB.pls:
$Header MSCCLBAB.pls 120.86.12010000.31 2013/08/26 11:30:33 beitang ship $
$Header MSCCLBAB.pls 120.86.12010000.31 2013/08/26 11:30:33 beitang ship $
SQL> SELECT bug_number
FROM apps.ad_bugs
WHERE bug_number LIKE '%' ||10389190 || '%'; 2 3
BUG_NUMBER
------------------------------
10389190
SQL>
SQL> SELECT patch_name
FROM apps.ad_applied_patches
WHERE patch_name LIKE '%' ||10389190 || '%' 2 3
4 ;
no rows selected
APPLIED_PATCH_ID NOT NULL NUMBER
RAPID_INSTALLED_FLAG VARCHAR2(1)
PATCH_NAME NOT NULL VARCHAR2(120)
PATCH_TYPE NOT NULL VARCHAR2(30)
MAINT_PACK_LEVEL VARCHAR2(30)
SOURCE_CODE NOT NULL VARCHAR2(3)
CREATION_DA
select PATCH_NAME from ad_applied_patches where PATCH_NAME like '1038919%';
select * from dba_audit_trail where trunc(TIMESTAMP) >= '01-APR-2018' and trunc(TIMESTAMP) <= '01-MAY-2018';
994000
select count(*) from dba_audit_trail where trunc(TIMESTAMP)>='01-APR-2018' and trunc(TIMESTAMP) <='10-APR-2018';
The start of my next career tour. To date, you are happy to be able to travel with you.
The great drug of life is "forgetfulness". This should be done in accordance with the situation.
The most powerful weapon of life is "anger". The application should be very hobby and careful.
In all life's words in all life is not "pure". Feeling this can be the gate of victory.
Exclusion without preference. Yes, next trip.
"Remembering the Sound of Love"
APPS_TS_TX_DATA
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/
/ASCPPROD_DB/oracle/product/ASCPPROD/adr/diag/rdbms/chennai/ASCPPROD/trace
alter tablespace APPS_TS_TX_DATA add datafile '/oradb/oracle/product/TJTG/db/apps_st/data/a_txn_data220.dbf' size 9000m;
alter tablespace APPS_TS_TX_DATA add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_txn_data15.dbf' size 9000m;
APPS_TS_INTERFACE 384-TS#
select FILE#,name from v$datafile where TS#=384;
/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int02.dbf
alter tablespace APPS_TS_INTERFACE add datafile '/ASCPPROD_DB/oracle/product/ASCPPROD/db/apps_st/data/a_int03.dbf' size 1000m;
Please provide the following:
1. Please download the latest version of GST Analyzer and provide the output for LOCSTATUS
Doc ID 2297311.1 : EBS Financials for India GST Analyzer
2. SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
It is available on Doc ID 215187.1
Provide the output for XTRACT method for SQL ID: 474cbr1bmrunh
E:\siva\2018\Sep
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ILGSTAZ.ldt CUSTOM_MODE=FORCE
65 server
cd /home/orauat/sqlt_10g_11g_12c_25_08_2018/sqlt/run
# sqlplus apps
SQLTXPLAIN
SQL> START sqltxtract.sql 474cbr1bmrunh SQLTXPLAIN
I found this SQL statement to be a useful place to start (sorry I can't attribute this to the original author; I found it somewhere on the internet):
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
elapsed_time,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
https://stackoverflow.com/questions/316812/top-5-time-consuming-sql-queries-in-oracle/316881#
SELECT username, sql_text, sofar, totalwork, units
FROM v$sql,v$session_longops
WHERE sql_address = address AND sql_hash_value = hash_value
ORDER BY address, hash_value, child_number;
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
/ORADB/app/orauat/11.2.0/admin/UAT_tjpsb007/diag/rdbms/uat/UAT/trace
tkprof UAT_ora_10880_120276TJ.trc UAT_ora_8236_120276TJUAT_ora_10880_120276TJ.txt sort=fchela,exeela,prsela waits=yes sys=no
This happens due to unordered start of DB Instance, DB Listener and Oracle Enterprise Manager OEM Console. Locked(TIMED) means the account has been locked because a wrong password has been entered to many time. It the password would have expired, the status would be "EXPIRED(GRACE)".
Incremental checkpoint up to RBA [0x11b26.43797.0], current log tail at RBA [0x11b26.4b4bb.0]
Tue Sep 04 16:46:33 2018
ORA-01555 caused by SQL statement below (SQL ID: 9fhhc2gu3f2b4, Query Duration=4184 sec, SCN: 0x0b 48.274b74ee):
SELECT JV_VOUCHER#, STGI_GLD_MAIN.CLEAN (JOURNAL_NAME) JOURNAL_NAME, GL_DATE, SOURCE, CATEGORY, AC COUNT, COST_CENTER, NATURAL_ACCOUNT, SUB_ACCOUNT, JV_CURRENCY, CURRENCY, EXCHANGE_DATE, EXCHANGE_R ATE, PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE, VENDOR_CODE, STGI_GLD_MAIN.CLEAN (VENDOR_NAM E) VENDOR_NAME, CUSTOMER_CODE, STGI_GLD_MAIN.CLEAN (CUSTOMER_NAME) CUSTOMER_NAME, VOUCHER#, STGI_G LD_MAIN.CLEAN (DOCUMENT#) DOCUMENT#, STGI_GLD_MAIN.CLEAN (DOCUMENT_TYPE) DOCUMENT_TYPE, STGI_GLD_M AIN.CLEAN (ITEM_CODE) ITEM_CODE, QUANTITY, NET_DEBIT_AMT, KEY_ID, KEY_DATE, STGI_GLD_MAIN.CLEAN (D ESCRIPTION) DESCRIPTION, BKEY_TYPE, BKEY_ID, BKEY_VOUCHER#, STGI_GLD_MAIN.CLEAN (BKEY_DOCUMENT#) B KEY_DOCUMENT#, BKEY_DATE, STGI_GLD_MAIN.CLEAN (BKEY_DESCRIPTION) BKEY_DESCRIPTION, FKEY_TYPE, FKEY _ID, FKEY_VOUCHER#, STGI_GLD_MAIN.CLEAN (FKEY_DOCUMENT#) FKEY_DOCUMENT#, FKEY_DATE, STGI_GLD_MAIN. CLEAN (FKEY_DESCRIPTION) FKEY_DESCRIPTION, STGI_GLD_MAIN.CLEAN (ATTRIBUTE1) ATTRIBUTE1, STGI_GLD_M AIN.CLEAN (ATTRIBUTE2) ATTRIBUTE2, STGI_GLD_
Tue Sep 04 16:53:35 2018
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P12984 (1334) VALUES LESS TH AN (TO_DATE(' 2018-09-04 06:47:22', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Tue Sep 04 17:00:57 2018
Thread 1 cannot allocate new log, sequence 72487
Private strand flush not complete
Current log# 6 seq# 72486 mem# 0: /oradb/oracle/product/TJTG/db/apps_st/data/log2a.dbf
Current log# 6 seq# 72486 mem# 1: /oradb_1/oracle/product/TJTG/db/apps_st/data/log2b.dbf
Current log# 6 seq# 72486 mem# 2: /oradb_2/oracle/product/TJTG/db/apps_st/data/log2c.dbf
Beginning log switch checkpoint up to RBA [0x11b27.2.10], SCN: 12404525068857
Thread 1 advanced to log sequence 72487 (LGWR switch)
Current log# 5 seq# 72487 mem# 0: /oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf
Current log# 5 seq# 72487 mem# 1: /oradb_1/oracle/product/TJTG/db/apps_st/data/log1b.dbf
Current log# 5 seq# 72487 mem# 2: /oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf
Tue Sep 04 17:01:01 2018
LNS: Standby redo logfile selected for thread 1 sequence 72487 for destination LOG_ARCHIVE_DEST_2
Tue Sep 04 17:01:01 2018
/orahome/oracle/product/TJTG/db/adr/diag/rdbms/ebs_chennai/TJTG/trace
Please apply patch 28433027:R12.JAI.B: GST :AUG CONSOLIDATION
/home/appluat/patch8010/28433027
E:\siva\2018\Sep
u28433027.drv
/home/appluat/patch8010/27081350
u27081350.drv
adadmin_27081350_7Sep18.log
adpatch_27081350_7Sep18.log
https://en.wikipedia.org/wiki/Venture_capital
https://en.wikipedia.org/wiki/Indian_Venture_Capital_Association
https://inc42.com/startup-101/top-37-angel-investors-india/
https://angel.co/india/investors
Please do the following for JAINTRPR.fmx compilation error
1. Apply patch 27081350 R12.JAI.B
2. using adadmin compile JAINTRPR.fmx
TJPS_RCM_MON_REGISTER
E:\jp\2018\Sep\7
chnage vnc password:
[root@TJPSB007 ~]# vncpasswd
vncserver
1) Log as root
2) su - oracle
3) enter 'xhost +'
4) enter 'export DISPLAY=:0.0'
5) ./runInstaller
ssh oracle@`hostname`
ssh oraascp@'TJPSB007'
DISPLAY=hostname:0.0 ; export DISPLAY
DISPLAY=TJPSB007:0.0 ; export DISPLAY
authentication is required to set the network proxy used for downloading software:
The solution is to create
.config/autostart/*.desktop
with:
X-GNOME-Autostart-enabled=false
https://support.citrix.com/article/CTX223602
TJPS - Non Poc Budgeting System
https://tjpsb007.ebs.in:5500/em ---UAT
sys
manager1
Chennai apps dba sir
http://oraclehub.com/oracle-e-business-suite-installation-12-2-6/
Tot_Size_DB.sql
[oraprod@tjpsb004 dbscript]$ cat Tot_Size_DB.sql
--select
--( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
--( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
--( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
--( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
--from
--dual;
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;
[oraprod@tjpsb004 dbscript]$
[oraprod@tjpsb004 dbscript]$ cat tbsp_free_usedspace.sql
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
[oraprod@tjpsb004 dbscript]$ cat tbsp_free_usedspace.sql
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
[oraprod@tjpsb004 dbscript]$ cat active_inactive_session.sql
select
s.status,
count(1),
s.username
from
v$process p,
v$session s
where
paddr(+)=addr
group by
s.status,
s.username
order by 1;
[oraprod@tjpsb004 dbscript]$ cat Active_Users_count.sql
select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
[oraprod@tjpsb004 dbscript]$ cat archive_daily.sql
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
[oraprod@tjpsb004 dbscript]$ cat archive_hourly.sql
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;
[oraprod@tjpsb004 dbscript]$ cat cm_scheduled_jobs.sql
SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
[oraprod@tjpsb004 dbscript]$ cat cm_table_count.sql
prompt FND_CONCURRENT_REQUESTS
select count(*) from FND_CONCURRENT_REQUESTS
/
prompt FND_RUN_REQUESTS
select count(*) from FND_RUN_REQUESTS
/
prompt FND_CONC_REQUEST_ARGUMENTS
select count(*) from FND_CONC_REQUEST_ARGUMENTS
/
prompt FND_DUAL
select count(*) from FND_DUAL
/
prompt FND_CONCURRENT_PROCESSES
select count(*) from FND_CONCURRENT_PROCESSES
/
prompt FND_CONC_REQ_OUTPUTS
select count(*) from FND_CONC_REQ_OUTPUTS
/
prompt FND_CONC_PP_ACTIONS
select count(*) from FND_CONC_PP_ACTIONS
/
prompt FND_RUN_REQ_PP_ACTIONS
select count(*) from FND_RUN_REQ_PP_ACTIONS
/
prompt FND_ENV_CONTEXT
select count(*) from FND_ENV_CONTEXT
/
[oraprod@tjpsb004 dbscript]$ cat CONC_REQ_CUR_RUNN.sql
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
[oraprod@tjpsb004 dbscript]$ cat connected_users_audit.sql
prompt "Enter Date format as 21 JAN 2017 "
select count(username)"No of connected Users",username
from dba_audit_session
WHERE to_char(timestamp, 'DD-MON-YYYY') >='&Enter_Date'
group by username
order by username;
[oraprod@tjpsb004 dbscript]$ cat dba_audit_inout2.sql
spool audit_user.txt
col ACTION_NAME for a12
col USERNAME for a15
select
USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff,
action_name
from dba_audit_session
WHERE to_char(timestamp, 'DD MON YYYY hh24:mi') >='27 MAR 2017 17:21'
order by logon_time,username,timestamp,logoff_time;
spool off;
/
[oraprod@tjpsb004 dbscript]$ cat dba_audit_loginout.sql
select
USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff,
action_name
from dba_audit_session
order by logon_time,username,timestamp,logoff_time;
[oraprod@tjpsb004 dbscript]$ cat Estimate_Tablespace_Growth_In_Oracle.sql
set heading on
set linesize 5500
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
[oraprod@tjpsb004 dbscript]$ cat estimate_tbs_growth_oracle.sql
set heading on
set linesize 5500
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
[oraprod@tjpsb004 dbscript]$ cat grwth_rate_tbs.sql
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
[oraprod@tjpsb004 dbscript]$ cat hc2.sql
column date_column new_value today_var
column database_column new_value sid_var
column file_type_column new_value type_var
select to_char(sysdate,'yyyy-mm-dd.HH24-MI-SS') date_column
from dual
/
select name||'_' database_column
from v$database
/
select '.txt' file_type_column
from dual
/
spool Health_Check_&sid_var&today_var&type_var
prompt
prompt Startup Time
prompt ------------
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/
prompt
prompt Total database size (including redo logs)
prompt -----------------------------------------
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
/* select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
*/
prompt
prompt Object distribution, or what is taking up all the space?
prompt ----------------------------------------------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
prompt
prompt The ten biggest things...
prompt -------------------------
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
prompt
prompt Show all National Language Set parameters
prompt -----------------------------------------
select * from nls_database_parameters
/
prompt
prompt Currently used features
prompt -----------------------
select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/
prompt
prompt Show all connected users
prompt ------------------------
set lines 100 pages 999
col ID format a15
select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc
/
prompt
prompt Time since last user activity
prompt -----------------------------
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
prompt
prompt Sessions sorted by logon time
prompt -----------------------------
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
prompt
prompt Show user info including os pid
prompt -------------------------------
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
prompt
prompt All active sql
prompt --------------
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
prompt
prompt Display any long operations
prompt ---------------------------
/* set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
*/
prompt
prompt List open cursors per user
prompt --------------------------
set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
prompt
prompt Or alternatively...
prompt -------------------
set lines 100 pages 999
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
/
prompt
prompt Show non-default parameters
prompt ---------------------------
set pages 999 lines 100
col name format a30
col value format a50
select name
, value
from v$parameter
where isdefault = 'FALSE'
and value is not null
order by name
/
prompt
prompt Show DBA_JOBS
prompt -------------
set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/
prompt
prompt Show DBMS_SCHEDULER_JOBS
prompt ------------------------
set lines 150
col owner format a12
col job_name format a30
col job_action format a70
select owner
, job_name
, job_action
from dba_scheduler_jobs
/
prompt
prompt Display all queues and queue tables
prompt -----------------------------------
-- set lines 100 pages 999
-- col owner format a15
-- select owner
-- , name
-- , queue_table
-- from dba_queues
-- /
prompt
prompt Check various stats for all queues
prompt ----------------------------------
/*set lines 100 pages 999
col queue format a50
select owner || '.' || name queue
, waiting
, ready
, expired
from dba_queues dq
, v$aq aq
where dq.qid = aq.qid
order by 2, 3, 4, 1
/*/
prompt
prompt Check DBA recycle bin
prompt ---------------------
select owner
, object_name
, original_name
from dba_recyclebin
/
prompt
prompt How full are the tablespaces?
prompt ----------------------------
/*
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
*/
prompt
prompt Tablespaces that are >=80% full, and how much to add to make them 80% again
prompt ---------------------------------------------------------------------------
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/
prompt
prompt User quotas on all tablespaces
prompt ------------------------------
col quota format a10
select username
, tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where tablespace_name not in ('TEMP')
/
prompt
prompt Show segments that are approaching max_extents
prompt ----------------------------------------------
col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/
prompt
prompt List the contents of the temporary tablespace(s)
prompt ------------------------------------------------
set pages 999 lines 100
col username format a15
col mb format 999,999
select su.username
, ses.sid
, ses.serial#
, su.tablespace
, ceil((su.blocks * dt.block_size) / 1048576) MB
from v$sort_usage su
, dba_tablespaces dt
, v$session ses
where su.tablespace = dt.tablespace_name
and su.session_addr = ses.saddr
/
prompt
prompt List invalid objects
prompt --------------------
set lines 200 pages 999
col "obj" format a40
select owner || '.' || object_name "obj",
object_type
from dba_objects
where status = 'INVALID'
/
prompt
prompt Display all datafiles, tempfiles and logfiles (and their sizes)
prompt ---------------------------------------------------------------
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#
union all
select name, 0
from v$controlfile) used
, (select sum(bytes) as p
from dba_free_space) free
/
prompt
prompt Anything left in backup mode?
prompt -----------------------------
set lines 100 pages 999
col name format a60
select df.name
, b.status
, to_char(time, 'hh24:mi:ss dd/mm/yyyy') time
from v$datafile df
, v$backup b
where df.file# = b.file#
and b.status = 'ACTIVE'
order by b.file#
/
prompt
prompt Whats in undo?
prompt ---------------
select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status
/
prompt
prompt Is anything rolling back at the moment? Look for the used_ublk value decreasing...
prompt ----------------------------------------------------------------------------------
set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/
prompt
prompt Resource intensive sql - change 8192 to match block size
prompt --------------------------------------------------------
/* select sql_text
, executions
, to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00')
as total_gets_per_exec_mb
, to_char((( disk_reads /executions) * 8192)/1048576, '9,999,999,990.00')
as disk_reads_per_exec_mb
, to_char((( buffer_gets /executions) * 8192)/1048576, '9,999,999,990.00')
as buffer_gets_per_exec_mb
, parsing_user_id
from v$sqlarea
where executions > 10
order by 6 desc
/
*/
prompt
prompt File I/O stats
prompt -------------
set lines 80 pages 999
col fname heading "File Name" format a60
col sizemb heading "Size(Mb)" format 99,999
col phyrds heading "Reads" format 999,999,999
col readtim heading "Time" format 99.999
col phywrts heading "Writes" format 9,999,999
col writetim heading "Time" format 99.999
select lower(name) fname
, (bytes / 1048576) sizemb
, phyrds
, readtim
, phywrts
, writetim
from v$datafile df
, v$filestat fs
where df.file# = fs.file#
order by 1
/
prompt
prompt Show the buffer cache advisory
prompt ------------------------------
set lines 100 pages 999
col est_mb format 99,999
col estd_physical_reads format 999,999,999,999,999
select size_for_estimate est_mb
, estd_physical_read_factor
, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT'
order by size_for_estimate
/
prompt
prompt Top ten hottest objects by access
prompt ---------------------------------
col owner format a20 trunc
col object_name format a30
col touches format 9,999,999
select *
from (
select count(*)
, sum(tch) TOUCHES
, u.name OWNER
, o.name OBJECT_NAME
from x$bh x
, obj$ o
, user$ u
where x.obj = o.obj#
and o.owner# = u.user#
group by u.name, o.name
order by 2 desc
)
where rownum < 11
/
prompt
prompt Waits by file
prompt -------------
/* col name format a60
select name
, count
from x$kcbfwait
, v$datafile
where indx + 1 = file#
order by 2
/
*/
prompt
prompt Waits by segment
prompt ----------------
set lines 150
col value format 99999999
col statistic_name format a50
select object_name
, obj#
, statistic_name
, value
from v$segment_statistics
where statistic_name like '%waits%'
and value > 0
order by statistic_name
, value desc
/
prompt ----------------------------------------------
prompt Count of analyzed tables broken down by schema
prompt ----------------------------------------------
set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
, count(*) total_tables
from dba_tables
group by owner) a
, (select owner
, count(last_analyzed) analyzed_tables
from dba_tables
where last_analyzed is not null
group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/
prompt
prompt Whats not been analyzed (at least a day ago...)
prompt ----------------------------------------------
/* select owner
, table_name
, last_analyzed
from dba_tables
where trunc(last_analyzed) < trunc(sysdate)
and owner not in ('SYS','SYSTEM','WMSYS','EXFSYS','CTXSYS','SYSMAN','OLAPSYS','MDSYS','ORDSYS','OUTLN','DMSYS','XDB','DBSNMP')
/
*/
prompt
prompt Quick look at SGA
prompt -----------------
set lines 100 pages 999
col bytes format 999,999,999
compute sum of bytes on pool
break on pool skip 1
select pool
, name
, bytes
from v$sgastat
order by pool
, name
/
prompt
prompt PGA usage by username
prompt ---------------------
select st.sid "SID",
sn.name "TYPE",
ceil(st.value / 1024 / 1024) "MB"
from v$sesstat st,
v$statname sn
where st.statistic# = sn.statistic#
and sid in (select sid
from v$session)
and upper(sn.name) like '%PGA%'
order by st.sid,
st.value desc
/
prompt
prompt Display pool usage
prompt ------------------
select name
, sum(bytes)
from v$sgastat
where pool like 'shared pool'
group by name
order by sum(bytes)
/
spool off;
exit
[oraprod@tjpsb004 dbscript]$ cat health_check_apps_db.sql
spool health_check_apps_db.out
set pages 1000
set linesize 135
col PROPERTY_NAME for a25
col PROPERTY_VALUE for a15
col DESCRIPTION for a35
col DIRECTORY_PATH for a70
col directory_name for a25
col OWNER for a10
col DB_LINK for a40
col HOST for a20
col "User_Concurrent_Queue_Name" format a50 heading 'Manager'
col "Running_Processes" for 9999 heading 'Running'
set head off
set feedback off
set echo off
break on utl_file_dir
select '--------------------------------------------------------------------------------' from dual;
select '----------------------- Database Checks ---------------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;
Prompt
select '************************ Getting Database Information *************' from dual ;
select 'Database Name..................... : '||name from v$database;
select 'Database Status................... : '||open_mode from v$database;
select 'Archiving Status.................. : '||log_mode from v$database;
select 'Global Name....................... : '||global_name from global_name;
select 'Creation Date..................... : '||to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
select 'Checking For Missing File......... : '||count(*) from v$recover_file;
select 'Checking Missing File Name ....... : '||count(*) from v$datafile where name like '%MISS%';
select 'Total SGA ........................ : '||round(sum(value)/(1024*1024))||' MB' from v$sga ;
select 'Database Version.................. : '||version from v$instance;
select 'Temporary Tablespace.............. : '||property_value from database_properties
where property_name like 'default_temp_tablespace';
select 'Apps Temp Tablespace.............. : '||temporary_tablespace from dba_users where username like '%APPS%';
select 'Temp Tablespace size.............. : '||sum(maxbytes/1024/1024/1024)||' GB' from dba_temp_files group by tablespace_name;
select 'No of Invalid Object ............. : '||count(*) from dba_objects where status = 'INVALID' ;
select 'service Name...................... : '||value from v$parameter2 where name='service_names';
select 'plsql code type................... : '||value from v$parameter2 where name='plsql_code_type';
select 'plsql subdir count................ : '||value from v$parameter2 where name='plsql_native_library_subdir_count';
select 'plsql native library dir.......... : '||value from v$parameter2 where name='plsql_native_library_dir';
select 'Shared Pool Size.........,........ : '||(value/1024/1024) ||' MB' from v$parameter where name='shared_pool_size';
select 'Log Buffer........................ : '||(value/1024/1024) ||' MB' from v$parameter where name='log_buffer';
select 'Buffer Cache...................... : '||(value/1024/1024) ||' MB' from v$parameter where name='db_cache_size';
select 'Large Pool Size................... : '||(value/1024/1024) ||' MB' from v$parameter where name='large_pool_size';
select 'Java Pool Size.................... : '||(value/1024/1024) ||' MB' from v$parameter where name='java_pool_size';
select 'utl_file_dir...................... : '||value from v$parameter2 where name='utl_file_dir';
select directory_name||'.................... : '||directory_path from all_directories where rownum < 15 ;
select '************************ Getting Apps Information *****************' from dual ;
select 'Home URL.......................... : '||home_url from apps.icx_parameters ;
select 'Session Cookie.................... : '||session_cookie from apps.icx_parameters ;
select 'Applicaiton Database ID........... : '||fnd_profile.value('apps_database_id') from dual;
select 'GSM Enabled....................... : '||fnd_profile.value('conc_gsm_enabled') from dual;
select 'Maintainance Mode................. : '||fnd_profile.value('apps_maintenance_mode') from dual;
select 'Site Name......................... : '||fnd_profile.value('Sitename')from dual;
select 'Bug Number........................ : '||bug_number from ad_bugs where bug_number='2728236';
select '************************ Doing Workflow Checks ********************' from dual ;
select 'No Open Notifications............. : '||count(*) from wf_notifications where mail_status in('MAIL','INVALID','OPEN');
select 'Name(wf_systems).................. : '||name from wf_systems;
select 'Display Name(wf_systems).......... : '||display_name from wf_systems;
select 'Address........................... : '||address from wf_agents;
select 'Workflow Mailer Status............ : '||component_status from applsys.fnd_svc_components
where component_name like 'Workflow Notification Mailer';
select 'Test Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('TEST_ADDRESS');
select 'From Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('FROM');
select 'WF Admin Role..................... : '||text from wf_resources where name = 'WF_ADMIN_ROLE' and rownum =1;
Prompt
Prompt Getting Apps Node Info
Prompt ************************
select Node_Name,'........................ : '||server_id from fnd_nodes;
select server_type||'......................: '||name from fnd_app_servers, fnd_nodes
where fnd_app_servers.node_id =fnd_nodes.node_id;
select '************************ Doing Conc Mgr Checks ********************' from dual ;
Prompt Getting Con Mgr Status
Prompt ************************
Prompt
Prompt Manager Name Hostname No of Proc Running
Prompt ~~~~~~~~~~~~ ~~~~~~~~ ~~~~~~~~~~~~~~~~~~
set lines 145
Column Target_Node Format A12
select User_Concurrent_Queue_Name,'....... : '||Target_Node||' ...... : '||Running_Processes
from fnd_concurrent_queues_vl
where Running_Processes = Max_Processes
and Running_Processes > 0;
Prompt
Prompt Getting Pending Request
Prompt ***********************
--select user_concurrent_program_name||'........ : '||request_id
-- from fnd_concurrent_requests r, fnd_concurrent_programs_vl p, fnd_lookups s, fnd_lookups ph
-- where r.concurrent_program_id = p.concurrent_program_id
-- and r.phase_code = ph.lookup_code
-- and ph.lookup_type = 'CP_PHASE_CODE'
-- and r.status_code = s.lookup_code
-- and s.lookup_type = 'CP_STATUS_CODE'
-- and ph.meaning ='Pending'
-- and rownum < 10
-- order by to_date(actual_start_date, 'dd-MON-yy hh24:mi');
--
Prompt
Prompt Getting Workflow Components Status
Prompt **********************************
set pagesize 1000
set linesize 125
col COMPONENT_STATUS for a20
col COMPONENT_NAME for a45
col STARTUP_MODE for a12
select fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs,
APPS.FND_CONCURRENT_PROCESSES fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;
select '--------------------------------------------------------------------------------' from dual;
select '----------------------- End Of Database Checks ----------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;
spool off;
[oraprod@tjpsb004 dbscript]$ cat lockdb.sql
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
[oraprod@tjpsb004 dbscript]$ cat Locks_block_session_More_Details.sql
SELECT s.inst_id, NVL (s.username, 'Internal') database_user, s.SID,
s.event, s.p1, s.serial#, p.spid, m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) lock_type,
m.TYPE
|| ' - '
|| DECODE (m.TYPE,
'BL', 'Buffer hash table instance lock',
'CF', ' Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CS', 'Control file schema global enqueue lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'Direct loader parallel index create',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FI', 'SGA open-file information lock',
'FS', 'File set lock',
'HW', 'Space management on a specific segment lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'MB', 'Master buffer hash table instance lock',
'MM', 'Mount definition gloabal enqueue lock',
'MR', 'Media recovery lock',
'PF', 'Password file lock',
'PI', 'Parallel operation lock',
'PR', 'Process startup lock',
'PS', 'Parallel operation lock',
'RE', 'USE_ROW_ENQUEUE enforcement lock',
'RT', 'Redo thread global enqueue lock',
'RW', 'Row wait enqueue lock',
'SC', 'System commit number instance lock',
'SH', 'System commit high water mark enqueue lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment lock',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TD', 'DDL enqueue lock',
'TE', 'Extend-segment enqueue lock',
'TM', 'DML enqueue lock',
'TO', 'Temporary Table Object Enqueue',
'TT', 'Temporary table enqueue lock',
'TX', 'Transaction enqueue lock',
'UL', 'User supplied lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
'WS', 'Write-atomic-log-switch global enqueue lock',
'TS', DECODE (m.id2,
0, 'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'
),
'LA', 'Library cache lock instance lock (A=namespace)',
'LB', 'Library cache lock instance lock (B=namespace)',
'LC', 'Library cache lock instance lock (C=namespace)',
'LD', 'Library cache lock instance lock (D=namespace)',
'LE', 'Library cache lock instance lock (E=namespace)',
'LF', 'Library cache lock instance lock (F=namespace)',
'LG', 'Library cache lock instance lock (G=namespace)',
'LH', 'Library cache lock instance lock (H=namespace)',
'LI', 'Library cache lock instance lock (I=namespace)',
'LJ', 'Library cache lock instance lock (J=namespace)',
'LK', 'Library cache lock instance lock (K=namespace)',
'LL', 'Library cache lock instance lock (L=namespace)',
'LM', 'Library cache lock instance lock (M=namespace)',
'LN', 'Library cache lock instance lock (N=namespace)',
'LO', 'Library cache lock instance lock (O=namespace)',
'LP', 'Library cache lock instance lock (P=namespace)',
'LS', 'Log start/log switch enqueue lock',
'PA', 'Library cache pin instance lock (A=namespace)',
'PB', 'Library cache pin instance lock (B=namespace)',
'PC', 'Library cache pin instance lock (C=namespace)',
'PD', 'Library cache pin instance lock (D=namespace)',
'PE', 'Library cache pin instance lock (E=namespace)',
'PF', 'Library cache pin instance lock (F=namespace)',
'PG', 'Library cache pin instance lock (G=namespace)',
'PH', 'Library cache pin instance lock (H=namespace)',
'PI', 'Library cache pin instance lock (I=namespace)',
'PJ', 'Library cache pin instance lock (J=namespace)',
'PL', 'Library cache pin instance lock (K=namespace)',
'PK', 'Library cache pin instance lock (L=namespace)',
'PM', 'Library cache pin instance lock (M=namespace)',
'PN', 'Library cache pin instance lock (N=namespace)',
'PO', 'Library cache pin instance lock (O=namespace)',
'PP', 'Library cache pin instance lock (P=namespace)',
'PQ', 'Library cache pin instance lock (Q=namespace)',
'PR', 'Library cache pin instance lock (R=namespace)',
'PS', 'Library cache pin instance lock (S=namespace)',
'PT', 'Library cache pin instance lock (T=namespace)',
'PU', 'Library cache pin instance lock (U=namespace)',
'PV', 'Library cache pin instance lock (V=namespace)',
'PW', 'Library cache pin instance lock (W=namespace)',
'PX', 'Library cache pin instance lock (X=namespace)',
'PY', 'Library cache pin instance lock (Y=namespace)',
'PZ', 'Library cache pin instance lock (Z=namespace)',
'QA', 'Row cache instance lock (A=cache)',
'QB', 'Row cache instance lock (B=cache)',
'QC', 'Row cache instance lock (C=cache)',
'QD', 'Row cache instance lock (D=cache)',
'QE', 'Row cache instance lock (E=cache)',
'QF', 'Row cache instance lock (F=cache)',
'QG', 'Row cache instance lock (G=cache)',
'QH', 'Row cache instance lock (H=cache)',
'QI', 'Row cache instance lock (I=cache)',
'QJ', 'Row cache instance lock (J=cache)',
'QL', 'Row cache instance lock (K=cache)',
'QK', 'Row cache instance lock (L=cache)',
'QM', 'Row cache instance lock (M=cache)',
'QN', 'Row cache instance lock (N=cache)',
'QO', 'Row cache instance lock (O=cache)',
'QP', 'Row cache instance lock (P=cache)',
'QQ', 'Row cache instance lock (Q=cache)',
'QR', 'Row cache instance lock (R=cache)',
'QS', 'Row cache instance lock (S=cache)',
'QT', 'Row cache instance lock (T=cache)',
'QU', 'Row cache instance lock (U=cache)',
'QV', 'Row cache instance lock (V=cache)',
'QW', 'Row cache instance lock (W=cache)',
'QX', 'Row cache instance lock (X=cache)',
'QY', 'Row cache instance lock (Y=cache)',
'QZ', 'Row cache instance lock (Z=cache)',
'????'
) lock_type_detail,
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) lock_request,
DECODE (command,
0, 'BACKGROUND',
1, 'Create Table',
2, 'INSERT',
3, 'SELECT',
4, 'CREATE CLUSTER',
5, 'ALTER CLUSTER',
6, 'UPDATE',
7, 'DELETE',
8, 'DROP',
9, 'CREATE INDEX',
10, 'DROP INDEX',
11, 'ALTER INDEX',
12, 'DROP TABLE',
13, 'CREATE SEQUENCE',
14, 'ALTER SEQUENCE',
15, 'ALTER TABLE',
16, 'DROP SEQUENCE',
17, 'GRANT',
18, 'REVOKE',
19, 'CREATE SYNONYM',
20, 'DROP SYNONYM',
21, 'CREATE VIEW',
22, 'DROP VIEW',
23, 'VALIDATE INDEX',
24, 'CREATE PROCEDURE',
25, 'ALTER PROCEDURE',
26, 'LOCK TABLE',
27, 'NO OPERATION',
28, 'RENAME',
29, 'COMMENT',
30, 'AUDIT',
31, 'NOAUDIT',
32, 'CREATE EXTERNAL DATABASE',
33, 'DROP EXTERNAL DATABASE',
34, 'CREATE DATABASE',
35, 'ALTER DATABASE',
36, 'CREATE ROLLBACK SEGMENT',
37, 'ALTER ROLLBACK SEGMENT',
38, 'DROP ROLLBACK SEGMENT',
39, 'CREATE TABLESPACE',
40, 'ALTER TABLESPACE',
41, 'DROP TABLESPACE',
42, 'ALTER SESSION',
43, 'ALTER USER',
44, 'COMMIT',
45, 'ROLLBACK',
46, 'SAVEPOINT',
47, 'PL/SQL EXECUTE',
48, 'SET TRANSACTION',
49, 'ALTER SYSTEM SWITCH LOG',
50, 'EXPLAIN',
51, 'CREATE USER',
52, 'CREATE ROLE',
53, 'DROP USER',
54, 'DROP ROLE',
55, 'SET ROLE',
56, 'CREATE SCHEMA',
57, 'CREATE CONTROL FILE',
58, 'ALTER TRACING',
59, 'CREATE TRIGGER',
60, 'ALTER TRIGGER',
61, 'DROP TRIGGER',
62, 'ANALYZE TABLE',
63, 'ANALYZE INDEX',
64, 'ANALYZE CLUSTER',
65, 'CREATE PROFILE',
66, 'DROP PROFILE',
67, 'ALTER PROFILE',
68, 'DROP PROCEDURE',
69, 'DROP PROCEDURE',
70, 'ALTER RESOURCE COST',
71, 'CREATE SNAPSHOT LOG',
72, 'ALTER SNAPSHOT LOG',
73, 'DROP SNAPSHOT LOG',
74, 'CREATE SNAPSHOT',
75, 'ALTER SNAPSHOT',
76, 'DROP SNAPSHOT',
79, 'ALTER ROLE',
85, 'TRUNCATE TABLE',
86, 'TRUNCATE CLUSTER',
87, '-',
88, 'ALTER VIEW',
89, '-',
90, '-',
91, 'CREATE FUNCTION',
92, 'ALTER FUNCTION',
93, 'DROP FUNCTION',
94, 'CREATE PACKAGE',
95, 'ALTER PACKAGE',
96, 'DROP PACKAGE',
97, 'CREATE PACKAGE BODY',
98, 'ALTER PACKAGE BODY',
99, 'DROP PACKAGE BODY',
command || ' - ???'
) command,
(CASE
WHEN m.TYPE = 'UL'
THEN 'None '
ELSE DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username
|| '.'
|| SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
)
END
) OBJECT,
s.machine, s.process, m.ctime, s.program, SQL.sql_text, s.sql_id,
(SELECT 'select * from '
|| owner
|| '.'
|| object_name
|| ' where rowid=dbms_rowid.rowid_create( 1, '
|| row_wait_obj#
|| ','
|| row_wait_file#
|| ','
|| row_wait_block#
|| ','
|| row_wait_row#
|| ' );'
FROM dba_objects db
WHERE db.object_id = s.row_wait_obj# AND db.object_type = 'TABLE') row_wait
FROM gv$session s,
gv$lock m,
gv$process p,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND m.inst_id = s.inst_id
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
[oraprod@tjpsb004 dbscript]$ cat Locks_block_session.sql
SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
[oraprod@tjpsb004 dbscript]$ cat tb_avg_inc_rate.sql
SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days, a.tsname, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) A
) b GROUP BY b.tsname ORDER BY b.tsname;
[oraprod@tjpsb004 dbscript]$ cat tbs_datafile_name.sql
select distinct(name) from v$tablespace;
/
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt APPS_TS_MEDIA
prompt APPS_TS_ARCHIVE
prompt APPS_TS_INTERFACE
prompt USERS
prompt APPS_TS_SUMMARY
prompt APPS_TS_QUEUES
prompt APPS_TS_SEED
prompt APPS_TS_TX_DATA
prompt APPS_UNDOTS1
prompt SYSTEM
prompt SYSAUX
prompt APPS_TS_NOLOGGING
prompt TJTG
prompt CTXD
prompt OLAP
prompt ODM
prompt APPS_RO_DATA
prompt PORTAL
prompt cOWAPUB
prompt APPS_TS_TOOLS
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT d.NAME, t.NAME
FROM v$datafile d, v$tablespace t
WHERE d.ts# = t.ts# AND t.NAME ='&input_tbs_name';
[oraprod@tjpsb004 dbscript]$ cat user_toad.sql
set linesize 250
SELECT a.SID, a.serial#, b.spid, SUBSTR (a.program, 1, 12) program,
a.username, TO_CHAR (a.logon_time, 'DD-MM HH24:MI:SS') logondate,
a.osuser, a.machine, a.status
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT NULL
AND A.PROGRAM='toad.exe'
ORDER BY status, osuser;
—-- check currently running requests
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R'
AND a.request_id in (SELECT r.request_id
FROM applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
v$session s
WHERE r.controlling_manager=p.concurrent_process_id
AND q.application_id=p.queue_application_id
AND q.concurrent_queue_id=p.concurrent_queue_id
AND qt.application_id=q.application_id
AND qt.concurrent_queue_id=q.concurrent_queue_id
AND r.phase_code='R'
AND qt.language in ('US')
AND p.session_id=s.audsid);
– Query to find runtime for a concurrent program
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name LIKE 'STGI%Drill%' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' --uncomment this for a specific date
ORDER BY rq.request_id DESC;
--Identify sessions of an user in Oracle EBS
SELECT DISTINCT * FROM (
select
usr.user_name user_name
,ses.sid||','||ses.serial# sid_serial
from
apps.icx_sessions i
,apps.fnd_logins l
,apps.fnd_appl_sessions a
,apps.fnd_user usr
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
UNION
select
usr.user_name
,ses.sid||','||ses.serial#
from
apps.fnd_logins l
,apps.fnd_login_responsibilities r
,apps.fnd_user usr
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.audsid = ses.audsid
UNION
select
usr.user_name
,ses.sid||','||ses.serial#
from
apps.fnd_logins l
,apps.fnd_login_resp_forms f
,apps.fnd_user usr
,apps.fnd_form_tl frm
,apps.fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
UNION
select
fu.user_name
,vs.SID || ',' || vs.serial#
FROM
APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
apps.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
AND vs.sid is not null
)
ORDER BY 1,2
;
Oracle Current Activity
Given below is a small query that provides the following information about current activity in Oracle database
Which user is currently logged-on?
Which SQL Query are they running?
Which computer the user is logged on from?
How long the query is running?
https://appsdba.info/2018/08/11/troubleshooting-queries/
select distinct
'========================================================='||chr(10)||
'Sid , Serial# : '||S.Sid ||' , '||S.Serial# ||Chr(10)||
'Server/Shadow : '||P.Spid ||Chr(10)||
'Client/Foreground : '||S.Process ||Chr(10)||
'Terminal / Machine: '||S.terminal||' / '||S.Machine ||Chr(10)||
'Username……..: '||S.Username ||Chr(10)||
'Osuser……….: '||S.Osuser ||Chr(10)||
'Program………: '||S.Program ||Chr(10)||
'Module……….: '||S.Module ||Chr(10)||
'Status……….: '||S.Status ||Chr(10)||
'Action……….: '||S.Action ||Chr(10)||
'Wait_time…….: '||W.Wait_time ||Chr(10)||
'State ……….: '||W.State ||Chr(10)||
'Wait Event …..: '||W.Event ||Chr(10)||
'Seconds_in_wait.: '||W.Seconds_in_wait ||Chr(10)||
'Pga_alloc….: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_used…..: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_free…..: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_max……: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None') ||Chr(10)||
'Blocking Sessi=on: '||s.blocking_session||Chr(10)||
'Blocking Session Status: '|| s.blocking_session_status||Chr(10)||
'Latch Spin…: '|| Nvl(P.Latchspin, 'None') ||Chr(10)||
'Logon Time…: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss') ||Chr(10)||
'Last Call….: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || ' -> ' || To_char(S.Last_call_et/60, '99999.0') || ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') || ' Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days' ||Chr(10)||
'Sql Address. : '||S.Sql_address ||Chr(10)||
'Sql Hash…. : '||S.Sql_hash_value ||Chr(10)||
'Prev Sql Hash: '||S.Prev_hash_value ||Chr(10)||
'Trans Status : '|| Nvl(T.Status,'None') || Chr(10)||
'Trans Active : '|| Nvl(S.Taddr, 'None')||Chr(10)||
'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'||Chr(10)||
'Changed Blocks : '||I.Block_changes||' Blocks'||Chr(10)||
'………… Current Sql Statment ……………..: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q.Sql_text,'No Current Sql Statment') ||Chr(10)||
'========================================================='||Chr(10)||
'……………. Prev Sql Statment ………………: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q2.Sql_text,'No Sql Statment') ||Chr(10)||
'========================================================='
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where p.addr=s.paddr
and s.sid=i.sid
and s.sid=nvl('&sid',s.sid)
and s.sid=w.sid
and p.spid=nvl('&spid',p.spid)
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.hash_value(+)=s.prev_hash_value
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
What is running in the database
https://ss64.com/orav/V$SQLAREA.html
No comments:
Post a Comment