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

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

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

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

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

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

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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

ORA-01450: maximum key length (3215) exceeded

Hi All,

I got below 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.


Thanks,

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)

ORA-00214: control file '/u01/oradata/prod/control02.ctl' version 9240741

ORA-00214: control file '/u01/oradata/prod/control02.ctl' version 9242741
inconsistent with file '/u01/oradata/prod/control01.ctl' version 9099820
=================================================================================
Its because of the inconsistency between the mirrored copies of the control files.
All copies of the control file must have the same internal sequence number for oracle to start up the database or shut it down in normal or immediate mode.
Causes of the problem ORA-00214 includes,

1. You have restored the control file from backup, but forgot to copy it onto all of the mirrored copies of the control file as listed in the “CONTROL_FILES” parameter in the initialization parameter.
2. Improper Copy paste of the control file to a different location while the database is up and running.
4. The database or the system crashed while the mirrored copies of the control file were being updated, causing them to be out of sync.
5.  Not proper Shut down of the Oracle Machine. Like power failure, disk failure, Memory Failure

Solution:
----------------
Startup the database with single copy of the Control File. That should be a good copy.
To verify that
13:17:06 [SYS][PROD]>> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/oradata/prod/control01.ctl,
       /u01/oradata/prod/control02.ctl

1. Shut Abort
2. If you are using pfile, Edit init.ora file. Remove all the control file Except the Good one. Try One by one. Most probable highest Version Number will work.
3. If  you are using spfile;
 startup nomount. Then Set your control_file to the good one.

SQL> alter system set control_files=’/u01/oradata/prod/control01.ctl’ scope=spfile;
System altered

4. Startup restrict [If its working]. Shut down the database. Copy the good control file and Mirror it if not try with the other control file
SQL> shutdown immediate
cp /u01/oradata/prod/control02.ctl /u01/oradata/prod/control02.ctl_bkp
cp /u01/oradata/prod/control01.ctl /u01/oradata/prod/control02.ctl
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
SQL> select name,open_mode from v$database;

Startup:
-----------------
If instead you get ORA-1122, ORA-1110, and ORA-1207, go back to step 2 and try with another control file.
If you have already tried each and every one of the mirrored copies unsuccessfully, you must create a new control file for the database.
If you get ORA-1113 and ORA-1110 pointing to one of the datafiles, it means the copy of the control file you picked is good, but the referenced datafile must be recovered before the database can be opened.Then RECOVER DATBASE, apply the log it prompt and ALTER DATABASE OPEN.