Troubleshooting Oracle Apps DBA Scripts
12c
,
AppsDBA
,
Data Guard
,
R12.2
No comments
:
Query based export/import using datapump#########################################
Query based export/import using datapump
#########################################
Here is the scenario/requirement for export the output of the below query should be imported to another schema of Non-Prod database.
select *
from inv.mtl_mat_trans
where transaction_date >= '29-JUN-2013'
and transaction_type_id in
(
13
,26
,24
,41
,32
,24
,18
,14
,29
,31
,99
);
Steps to be performed:
[oracle@host]$ cat expdp_q.par
DIRECTORY = EXPDIR
DUMPFILE = expdp%U.dmp
FILESIZE = 5G
LOGFILE = expdp_query.log
SCHEMAS = INV
INCLUDE = TABLE:"IN ('MTL_MAT_TRANS')"
QUERY = INV.MTL_MAT_TRANS:"WHERE transaction_date >= '29-JUN-2013' and transaction_type_id in(13,26,24,41,32,24,18,14,29,31,99)"
impdp system/****
DIRECTORY=EXPDIR
FILE=expdp%U.dmp
LOGFILE=impdp_q.log
remap_schema=INV:SUPPORT REMAP_TABLESPACE=APPS_TS_TX_DATA:SUPPORT_DATA
CONTENT=data_only
ex:
expdp system/***** parfile=expdp_q.par
impdp system/***** parfile=impdp_q.par
Posted by Jagadish chandra Prasad M at 4:22 AM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Monday, March 16, 2015
TOP SEGMENTS OF A ORACLE DATABASE
####################################################
TOP 5 LARGE SEGMENT GROWTH(OBJECT GROWTH)
####################################################
Please find the below query to identify the top 5 segments in an oracle database with resepective to its size.
col SEGMENT_NAME for a30
col owner for a20
col SEGMENT_TYPE for a20
SELECT * FROM
(
select
owner,
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments order by 4 desc
) WHERE
ROWNUM <= 5;
OWNER SEGMENT_NAME SEGMENT_TYPE GB TABLESPACE_NAME
-------------------- ------------------------------ -------------------- --------------- --------------------
ABC POI_ATNL_KIT_DATA_KT TABLE 634.56091308594 USERS
ABC POS_ATNL_DATA_BKP_KIT TABLE 477.84375 USERS
ABC POS_ADDTNL_DATA_ADD_KT_A TABLE 140.8427734375 USERS
XYZ AT_DW_SALES_RPT TABLE 114.28125 USERS
YXAD P_DATA_UDTC_BKLD TABLE 103.59356689453 USERS
Posted by Jagadish chandra Prasad M at 8:28 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: Scripts
Determine the process which is locking an Oracle account with incorrect password
Here is the procedure to Determine the process which is locking an Oracle account with incorrect password, which search from the dba_audit_session view for records with a returncode equal to 1017 which indicate a failed logon.
column username format a15
column userhost format a25
column terminal format a15
column timestamp format a15
column action_name format a15
select username, userhost, terminal, timestamp, action_name from sys.dba_audit_trail where RETURNCODE='1017';
USERNAME USERHOST TERMINAL TIMESTAMP ACTION_NAME
--------------- ------------------------- ---------- ----------- -----------
appsread <HOSTNAME> 02-APR-14 LOGON
appsread <HOSTNAME> 02-APR-14 LOGON
appsread <HOSTNAME> 02-APR-14 LOGON
appsread <HOSTNAME> 02-APR-14 LOGON
appsread <HOSTNAME> 02-APR-14 LOGON
Posted by Jagadish chandra Prasad M at 8:18 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: Database
HIGH WATER USAGE (HWM USAGE)
##############################
HIGH WATER USAGE (HWM USAGE)
##############################
To resize any datafile to reclaim some space on the datafile. Need to check for the High water mark usage and based on that Water mark we may have to resize the respective datafile to get the space reclaimed.
We had an critical space issue on the dataware house environment to reclaim the space identified the datafiles using below query and resized the respective datafiles where we can get some space through this process.
Note: This is for a temporary Fix and have to plan for a better storage.
set verify off
column file_name format a60 word_wrapped
column smallest format 9999,990 heading "Smallest|Size|Poss."
column currsize format 9999,990 heading "Current|Size"
column savings format 9999,990 heading "Poss.|Savings"
set pages 100
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
SELECT FILE_NAME, CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL((NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS
where tablespace_name ='USERS' GROUP BY FILE_ID ) DBAFS
WHERE DBADF.TABLESPACE_NAME='USERS' and DBADF.FILE_ID = DBAFS.FILE_ID(+) and
(CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )) > 300;
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
------------------------------------------------------------ --------- --------- ---------
+DATA_PRD/PROD/datafile/users_data.363.825564829 31,396 31,744 348
+DATA_PRD/PROD/datafile/users_data.1042.866689707 16,076 16,512 436
---------
sum 784
Posted by Jagadish chandra Prasad M at 8:06 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: Database
How to check whether patch is applied via Hotpatch or Not in R12.2.3
#######################################################
TO CHECK WHETHER PATCH IS APPLIED VIA HOTPATCH OR NOT
########################################################
set pagesize 200;
set linesize 160;
column adop_session_id format 999999999999;
column bug_number format a15;
column status format a15;
column applied_file_system_base format a23;
column patch_file_system_base format a23;
column adpatch_options format a15;
column node_name format a15;
column end_date format a15;
column clone_status format a15;
select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
from ad_adop_session_patches where BUG_NUMBER='19677937' order by end_date desc;
ADOP_SESSION_ID BUG_NUMBER STATUS APPLIED_FILE_SYSTEM_BAS PATCH_FILE_SYSTEM_BASE ADPATCH_OPTIONS NODE_NAME END_DATE CLONE_STATUS
--------------- --------------- --------------- ----------------------- ----------------------- --------------- --------------- --------------- ---------------
32 19677937 Y /u01//app/oracle/prod/fs hotpatch <HOSTNAME> 27-MAR-14
Above information provides you that it uses session id 32 while applying as well as it applied in Hotpatch mode on so and so date.
Tha
Posted by Jagadish chandra Prasad M at 7:47 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: R12.2.3
Logfile Locations in R12.2.3
Please find the below logfiles of Admin server/oacore/forms and so on.
Admin server - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/AdminServer/logs/AdminServer.log
oacore logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.log
oacore out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.out
acore diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1-diagnostic.log
oafm logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.log
oafm outfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.out
oafm diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1-diagnostic.log
form server log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.log
form server access log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/access.log
form server out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.out
form server diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1-diagnostic.log
forms-c4ws_server1 log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.log
forms-c4ws_server1 out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.out
forms-c4ws_server1 diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1-diagnostic.log
Posted by Jagadish chandra Prasad M at 7:38 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: R12.2.3
How to find database growth on a Monthly wise
###################################
DATABASE GROWTH ON A MONTHLY WISE
##################################
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;
YEAR MO GB
---- -- ---------------
2011 09 74
2012 06 4579
2012 07 334
2012 08 523
2012 09 652
Posted by Jagadish chandra Prasad M at 7:34 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: Database
Sunday, February 16, 2014
ORA-29702: ERROR OCCURRED IN CLUSTER GROUP SERVICE OPERATION
#################################################################
ORA-29702: ERROR OCCURRED IN CLUSTER GROUP SERVICE OPERATION
#################################################################
Got the above error while starting up test instance in nomount from prod instance where as prod is a Cluster database.
-bash-3.2$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sun Feb 9 20:03:36 2014
Connected to an idle instance.
SQL> startup nomount;
ORA-29702: error occurred in Cluster Group Service operation
SQL> shut immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> exit
-bash-3.2$ cd cd $ORACLE_HOME/rdbms/lib
-bash-3.2$
-bash-3.2$ cd $ORACLE_HOME/rdbms/lib
-bash-3.2$ pwd
/u01/app/oracle/testdb/rdbms/lib
-bash-3.2$ make -f ins_rdbms.mk rac_off
rm -f /u01/app/oracle/testdb/lib/libskgxp11.so
cp /u01/app/oracle/testdb/lib//libskgxpg.so /u01/app/oracle/testdb/lib/libskgxp11.so
rm -f /u01/app/oracle/testdb/lib/libskgxn2.so
cp /u01/app/oracle/testdb/lib//libskgxns.so \
/u01/app/oracle/testdb/lib/libskgxn2.so
/usr/bin/ar d /u01/app/oracle/testdb/rdbms/lib/libknlopt.a kcsm.o
/usr/bin/ar cr /u01/app/oracle/testdb/rdbms/lib/libknlopt.a /u01/app/oracle/testdb/rdbms/lib/ksnkcs.o
-bash-3.2$ make -f ins_rdbms.mk ioracle
chmod 755 /u01/app/oracle/testdb/bin
- Linking Oracle
rm -f /u01/app/oracle/testdb/rdbms/lib/oracle
gcc -o /u01/app/oracle/testdb/rdbms/lib/oracle -m64 -L/u01/app/oracle/testdb/rdbms/lib/ -L/u01/app/oracle/testdb/lib/ -L/u01/app/oracle/testdb/lib/stubs/
-Wl,-E /u01/app/oracle/testdb/rdbms/lib/opimai.o /u01/app/oracle/testdb/rdbms/lib/ssoraed.o /u01/app/oracle/testdb/rdbms/lib/ttcsoi.o -Wl,--whole-archive
-lperfsrv11 -Wl,--no-whole-archive /u01/app/oracle/testdb/lib/nautab.o /u01/app/oracle/testdb/lib/naeet.o /u01/app/oracle/testdb/lib/naect.o /u01/app/oracl
e/testdb/lib/naedhs.o /u01/app/oracle/testdb/rdbms/lib/config.o -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcor
e11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /u01/app/orac
le/testdb/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11 -lrt -lplp11 -lserver11 -lclient11 -lvsn11 -lc
ommon11 -lgeneric11 `if [ -f /u01/app/oracle/testdb/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /u01/app/oracle/oa123t
st/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11 -lrt -lplp11 -ljavavm11 -lserver11 -lwwg `cat /u01/app/oracle/testdb/lib/ldf
lags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/testdb/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lm
m -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /u01/app/oracle/oa1
23tst/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/testdb/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz
11 -lzt11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /u01
/app/oracle/testdb/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/u01/app/oracle/testdb/ctx/lib/ -lctx
c11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskg
xn2 -lnnz11 -lzt11 -lxml11 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerge
d -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls
11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio `cat /ot0
1/app/oracle/testdb/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/testdb/lib -lm `cat /u01/app/oracle/testdb/lib/sysliblist` -ldl -lm -L/u01/app/oracle/oa1
23tst/lib
test ! -f /u01/app/oracle/testdb/bin/oracle ||\
mv -f /u01/app/oracle/testdb/bin/oracle /u01/app/oracle/testdb/bin/oracleO
mv /u01/app/oracle/testdb/rdbms/lib/oracle /u01/app/oracle/testdb/bin/oracle
chmod 6751 /u01/app/oracle/testdb/bin/oracle
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sun Feb 9 20:10:48 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 452987080 bytes
Database Buffers 1660944384 bytes
Redo Buffers 21725184 bytes
SQL> exit
Posted by Jagadish chandra Prasad M at 3:23 PM No comments:
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: Database
ORA-01450: maximum key length (3215) exceeded
error while rebuilding the index through online.
06:19:09 [SYS][TEST]>> ALTER index PRS.UK_F_HEADER rebuild online;
ALTER index PRS.UK_HEADER rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Solution:
The issue was fixed with rebuilding the index without online.
06:24:30 [SYS][TEST]>> ALTER index PRS.UK_HEADER rebuild;
Index altered.
Labels: Database
Friday, January 31, 2014
RMAN-08137 on Primary Database although Archive Destination to Standby is deferred
RMAN does not delete ArchiveLogs on Primary Database when the State for the Archive Destination to the Standby Database is set to 'defer'.
When trying to delete ArchiveLogs, this Error is raised:
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
If we defer an Archive Destination to a Standby Database, the Primary Database will still consider the
Standby Database as existing but temporary unavailable eg. for Maintenance. This can happen if you stop Log Transport
Services from the Data Guard Broker or manually defer the State for the Archive Destination.
SOLUTION:
------------------
As long as the Archive Destination (log_archive_dest_n) is still set, we consider the
Standby Database as still existing and preserve the ArchiveLogs on the Primary
Database to perform Gap Resolution when the Archive Destination is valid again.
There are Situations when this is not wanted, eg. the Standby Database was activated or
removed but you still keep the Archive Destination because you want to rebuild the Standby
Database later again. In this Case you can set the hidden Parameter "_deferred_log_dest_is_valid" to
FALSE (default TRUE) which will consider deferred Archive Destinations as completely unavailable
and will not preserve ArchiveLogs for those Destinations any more. It is a dynamic Parameter and can be set this Way:
SQL> alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;
NOTE: This Parameter has been introduced with Oracle Database 11.2.0.x. In earlier
Versions you have to unset the log_archive_dest_n-Parameter pointing to the remote Standby
Database to make the Primary Database considering it as completely unavailable. There also exists a
Patch on Top of 11.1.0.7 for some Platforms to include this Parameter in 11.1.0.7, too. This is Patch Number 8468117.
Reff: RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process (1380368,1)
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment
Note: only a member of this blog may post a comment.