How to change SQL prompt to show connected user and database name in 12c +

In order to get the Database schema & Database name with include Time on the prompt need to change on the below Original file.

 

pwd

/u01/app/oracle/TEST/12.1.0/sqlplus/admin

[oracle@server1 admin]$ ls -lrt glogin.sql
-rw-r--r--. 1 oracle dba 342 Jan 12  2006 glogin.sql

[oracle@us04dbqa01 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
[oracle@server1 admin]$

 

 

 

Now Connect as oracle Database OS user and add the following line at the end of the  $ORACLE_HOME/sqlplus/admin/glogin.sql

 

 

[oracle@server1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set editfile "/tmp/a447553_1.buf"
SET FEEDBACK OFF
set head off
set linesize 600
SET TERMOUT OFF
define _editor = vi

VARIABLE v_database  VARCHAR2(10)

BEGIN
  SELECT upper(INSTANCE_NAME)
  INTO   :v_database
  FROM   v$instance;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :v_database := 'SQL';
END;
/

SPOOL uid_temp.sql

SELECT 'SET SQLPROMPT "' || '['||upper(User) || ']' ||'['|| :v_database || ']>> "' FROM   v$database;
SELECT 'SET numw 15' from DUAL;
SELECT 'SET time on ' from DUAL;
SELECT 'SET timing on trimspool on ' from DUAL;
SELECT 'SET pages 1000' from DUAL;
SELECT 'col OWNER format a16
SELECT 'col program format a30' from DUAL;
SELECT 'col machine format a10' from DUAL;
SELECT 'col partition_name format a20' from DUAL;
SELECT 'col table_name format a33' from DUAL;
SELECT 'col tablespace_name format a20' from DUAL;
SELECT 'col TBSP_NAME format a35' from DUAL;
SPOOL OFF

@uid_temp.sql
SET TERMOUT ON
--set sqlprompt " [ _DATE _USER _CONNECT_IDENTIFIER ] >> "
SET FEEDBACK ON
set head on

 

 

 

[oracle@server1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 9 09:22:24 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

09:22:24 [SYS][TEST1]>> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST   READ WRITE

1 row selected.

Elapsed: 00:00:00.03

09:22:55 [SYS][TEST1]>> show user;
USER is "SYS"
09:22:56 [SYS][TEST1]>>

 

 

 

How To Stabilize the Execution plan using SQL PLAN Baseline

How To Stabilize the Execution plan using SQL PLAN Baseline

 

Each SQL associated with SQL_ID and based on the DBMS_STATS, the Optimizer chooses a execution plan identified with PLAN_HASH_VALUE.   Hence, SQL_ID with multiple PLAN_HASH_VALUE often causes inconsistent performance.

How to fix this?

 

To start with, need to identify the optimal PLAN_HASH_VALUE for the SQL_ID.

 

Below Query give the history of the SQL_ID with PLAN_HASH_VALUE and execution time lines to choose the optimal PLAN_HASH_VALUE.  Make a note of the instance number in case of RAC for the optimal PLAN_HASH_VALUE.

 

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','XXXXXXXXX')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3

/

Before creating PLAN_BASELINE, check if the SQL_ID with optimal PLAN_HASH_VALUE is in cursor cache.

 

Need to query v$SQL :  select inst_id ,SQL_ID,PLAN_HASH_VALUE from gv$SQL;

 

If the same optimal PLAN_HASH_VALUE identified from the above query, you can create Plan Baseline from the Cursor Cache using below Steps:-

 

Loading SQL Plan Baseline from CURSOR Cache:

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘&SQL_ID’,PLAN_HASH_VALUE=>’&PLAN_HASH_VALUE’,fixed=>’YES’);

END;

/

commit;

 

Need to query dba_sql_plan_baselines, to check if the plan baseline created.

 

If the Optimal PLAN_HASH_VALUE does not exist in cursor cache, we need load it from AWR.

 

Loading SQL Plan Baseline from AWR

 

Step1:- Need to create SQL tuning set name, I am choosing the name as TUNING_SET1 in this document.

 

exec  dbms_sqltune.create_sqlset(sqlset_name => 'TUNING_SET1',description => 'sqlset descriptions');

 

Step2:- Load the SQL from AWR to above Tuning SET name TUNING_SET1

 

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for

select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(' TUNING_SET1', baseline_ref_cur);

end;

/

COMMIT;

 

Note:- Input the SQL_ID and snapshotIDs identified from the first step .Run from the appropriate instance , where optimal PLAN_HASH_VALUE identified.

 

Ensure the SQL_SET:-

 

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='TUNING_SET1';

 

Note:- statement_count value in the above query should be 1

 

To ensure right PLAN_HASH_VALUE is there in Tuning set:-

 

set long 2000000

select * from table(dbms_xplan.display_sqlset('TUNING_SET1','&sql_id'));

 

Step3 :- Load SQL Plan baseline from above tuning set.

declare

my_int pls_integer;

begin

my_int := dbms_spm.load_plans_from_sqlset (

sqlset_name => 'TUNING_SET1',

sqlset_owner => 'SYS',

fixed => 'YES',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_int);

end;

 /

commit;

 

Query dba_sql_plan_baselines  , to check if the plan baseline created.

 

 

 

 

 

 

 

 

 

 

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 

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.

Location of Different Logfiles in Exadata Environment

On the cell nodes

================

1. Cell alert.log file

/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/alert.log

or

if the CELLTRACE parameter is set just do cd $CELLTRACE

2. MS logfile

/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/ms-odl.log.
or

if the CELLTRACE parameter is set just do cd $CELLTRACE

3. OS watcher output data

/opt/oracle.oswatcher/osw/archive/

To get OS watcher data of specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} \; 

where 12- year 01- Month 13-day

4. Os message logfile

/var/log/messages

5. VM Core files

/var/log/oracle/crashfiles 

More details can be found in the following note:

Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

6. SunDiag output files.

/tmp/sundiag_.tar.bz2

7. Cell patching issues related logfiles:   

/var/log/cellos

The major logfile of patch application output you will find in the db node from where you are patching in the location /tmp/<cell version>/patchmgr.stdout and patchmgr.err

8. Disk controller firmware logs:

/opt/MegaRAID/MegaCli/MegaCli64 -fwtermlog -dsply -a0

On the Database nodes

=====================

1. Database alert.log

$ORACLE_BASE/diag/rdbms/{DBNAME}/{sid}/trace/alert_{sid}.log
Ex: /u01/app/oracle/diag/rdbms/dbfs/DBFS2/trace/alert_DBFS2.log

2. ASM alert.log

$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM {instance number}.log
Ex: /u01/app/oracle/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log

3. Clusterware CRS alert.log

$GRID_HOME/log/{node name}/alert{node name}.log

Ex: /u01/app/11.2.0/grid/log/dmorldb02/alertdmorldb02.log

4. Diskmon logfiles

$GRID_HOME/log/{node name}/diskmon/diskmon.lo*
Ex: /u01/app/11.2.0/grid/log/dmorldb02/diskmon/diskmon.log

5. OS Watcher output files

/opt/oracle.oswatcher/osw/archive/

To get OS watcher data of specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} \; 

where 12- year 01- Month 13-day

6. Os message logfile

/var/log/messages

7. VM Core files for Linux

/u01/crashfiles  

More details can be found in the following note:

Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

8. Disk controller firmware logs:
    

/opt/MegaRAID/MegaCli/MegaCli64 -fwtermlog -dsply -a0

Workflow Directory Services User/Role Validation

This is often experienced in Oracle Applications 11i/R12 that user has been assigned a responsibility but not able to see it while accessing it. However , everything seems fine with responsibility ,it is not end dated and looks good. To overcome this issue , there is a concurrent request that does sync up of such users and responsibility in 11i version.

Login as System Administrator --> Submit Concurrent Program ""Workflow Directory Services User/Role Validation' with parameters 10000, yes, yes

This request would check all users and assigned responsibilities and should sync up users with attached responsibilities , users should be able to view assigned responsibility now.

p_BatchSize – 10000 (Default Value 10000)
p_Check_Dangling – Yes (Default value No)
Add missing user/role assignments – Yes (Default Value No)
Update WHO columns in WF tables – No (Default Value No)

Form process is not getting cleared and occupying /tmp space

LSOF:

lsof is a command meaning "list open files", which is used in many Unix-like systems to report a list of all open files and the processes that opened them. This open source utility was developed and supported by Victor A. Abell, the retired Associate Director of the Purdue University Computing Center. It works in and supports several Unix flavors.

Open files in the system include disk files, pipes, network sockets and devices opened by all processes. One use for this command is when a disk cannot be unmounted because (unspecified) files are in use. The listing of open files can be consulted (suitably filtered if necessary) to identify the process that is using the files.


# lsof /var
COMMAND     PID     USER   FD   TYPE DEVICE SIZE/OFF   NODE NAME
syslogd     350     root    5w  VREG  222,5        0 440818 /var/adm/messages
syslogd     350     root    6w  VREG  222,5   339098   6248 /var/log/syslog
cron        353     root  cwd   VDIR  222,5      512 254550 /var -- atjobs

To view the port associated with a daemon:

# lsof -i -n -P | grep sendmail
sendmail  31649    root    4u  IPv4 521738       TCP *:25 (LISTEN)

From the above one can see that "sendmail" is listening on its standard port of "25".
-i Lists IP sockets.
-n Do not resolve hostnames (no DNS).
-P Do not resolve port names (list port number instead of its name).
One can also list Unix Sockets by using lsof -U.

The /tmp directory keeps filling up but the space reported with user space tools ("du" for example) is next to nothing.

According to the standard system tools:


Digging deeper with "lsof" we see the following:


[root@mail tmp]# lsof | grep "/tmp"
bash       1970    root  cwd       DIR        9,3       4096          2 /tmp
screen    13507   jgray    3r     FIFO        9,3                 15747 /tmp/uscreens/S-jgray/13507.pts-0.mail
perl      19932  zimbra    1w      REG        9,3      71523         18 /tmp/logswatch.out (deleted)
perl      19932  zimbra    2w      REG        9,3      71523         18 /tmp/logswatch.out (deleted)
zmlogger  19937  zimbra    1w      REG        9,3      71523         18 /tmp/logswatch.out (deleted)
zmlogger  19937  zimbra    2w      REG        9,3      71523         18 /tmp/logswatch.out (deleted)
zmlogger  19937  zimbra    4w      REG        9,3 1884374605         26 /tmp/zmlogger.out (deleted)
mysqld_sa 21321  zimbra    1w      REG        9,3         70         15 /tmp/zmcontrol.out.20738 (deleted)
mysqld_sa 21321  zimbra    2w      REG        9,3         70         15 /tmp/zmcontrol.out.20738 (deleted)
logswatch 21391  zimbra    1w      REG        9,3         82         19 /tmp/logswatch.out
logswatch 21391  zimbra    2w      REG        9,3         82         19 /tmp/logswatch.out
mysqld    21402  zimbra    5u      REG        9,3          0         20 /tmp/ibD4jO0l (deleted)
mysqld    21402  zimbra    6u      REG        9,3          0         21 /tmp/ibzy1fOB (deleted)
mysqld    21402  zimbra    7u      REG        9,3          0         22 /tmp/ibXukIBR (deleted)
mysqld    21402  zimbra    8u      REG        9,3          0         23 /tmp/ibJj1dq7 (deleted)
mysqld    21402  zimbra   12u      REG        9,3          0         25 /tmp/iblBE0Vn (deleted)
perl      21423  zimbra    1w      REG        9,3         82         19 /tmp/logswatch.out
perl      21423  zimbra    2w      REG        9,3         82         19 /tmp/logswatch.out

Regards,
Jaagadish.

Purging AUDIT TRAIL RECORDS (AUD$ Table)

If in your production database configured audit, DBA should maintain audit tables.
Because audit records may grows up to undesired size.

One of the most significant aspects of database security involves setting up auditing to record user activities.

When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.

These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed: 

1. Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
2. Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt. 

Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.

The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).

In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails. 

Oracle 11g Release 1 turned on auditng by default for the first time. Oracle 11g Release 2 now allows better management of the audit trail using theDBMS_AUDIT_MGMT package.

The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

• DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.

Automated Purge with retention of 90 days.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

 Manual Purging only aud$ table with retention of 120 days.

col owner for a20
select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER                TABLE_NAME                       NUM_ROWS
--------------- -------------------- ------------------------- ---------------
14-OCT-07       SYS                  AUD$                                    0

20:03:01 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.65
20:03:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER      TABLE_NAME                       NUM_ROWS
--------------- ---------- ------------------------- ---------------
24-MAY-13       SYS        AUD$                              1982500

00:36:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
28-AUG-10 11.58.44.054379 PM


02:45:16 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
10-JAN-12 11.56.07.081514 PM

Elapsed: 00:00:01.51


02:46:13 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -250);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
16-SEP-12 11.58.46.296859 PM

Elapsed: 00:00:01.55


02:59:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
24-JAN-13 11.59.17.319628 PM


03:04:53 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000);

795713 rows deleted.

Elapsed: 00:01:02.47


03:06:08 [SYS]>> commit;

Commit complete.

Elapsed: 00:00:00.02


03:06:38 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500);

660309 rows deleted.

Elapsed: 00:00:56.64


03:07:58 [SYS]>> commit;

Commit complete.

Elapsed: 00:00:00.01


03:08:08 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120);

396701 rows deleted.

Elapsed: 00:00:46.57


03:09:07 [SYS]>> commit;

Commit complete.



Rebuilding Indexes
----------------------
03:25:23 [SYS]>> select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name='AUD$' and owner='SYS';

OWNER        INDEX_NAME                               INDEX_TYPE   LAST_ANALYZED
------------ ---------------------------------------- ------------ ---------------
SYS          SYS_IL0000000375C00040$$                 LOB
SYS          SYS_IL0000000375C00041$$                 LOB
SYS          I_AUD1                                   NORMAL       25-MAY-13


-->If any Indexes listed above needs to rebuild indexes.

03:27:50 [SYS]>> alter index SYS.I_AUD1 rebuild online;

Index altered.

Elapsed: 00:00:05.39


03:35:59 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

PL/SQL procedure successfully completed.


03:37:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER        TABLE_NAME                  NUM_ROWS
--------------- ------------ -------------------- ---------------
25-MAY-13       SYS          AUD$                          123510