Find Last & DML activity

select
          (select to_char(last_ddl_time,'DD-MM-YYYY HH24:MI:SS') from dba_objects where object_name='Table_name' and owner='owner') "DDL Time",
           decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
           from
          (select nvl(max(ora_rowscn),0) maxscn from owner.table_name);

Fragmentation Script

To Calculate Fragmentation for Schema

select a.owner, a.TABLE_NAME, b.SIZE_GB, ((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024)) as ACTUAL_GB,
(b.SIZE_GB-((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024))) Savings,
a.TABLESPACE_NAME, b.SEGMENT_TYPE TABLE_TYPE, a.last_analyzed
from
dba_tables a,
(select * from
 (select owner, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, sum(bytes/1024/1024/1024) Size_GB
 from
 dba_segments
 where owner='&schema_name' and segment_type like '%TABLE%'
 group by segment_name, owner, SEGMENT_TYPE, TABLESPACE_NAME
 order by 5 desc) ) b
where a.TABLE_NAME=b.SEGMENT_NAME and a.owner=b.owner
order by SIZE_GB desc;


Index Fragmentation:

select e.*, d.owner index_owner,d.index_name, d.index_type, sum(g.bytes/1024/1024/1024) index_size_gb, d.TABLESPACE_NAME index_tbs from dba_segments g, dba_indexes d
right outer join
 (select * from (
select a.owner tabown, a.TABLE_NAME, b.SIZE_GB, ((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024)) as ACTUAL_GB,
(b.SIZE_GB-((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024))) Savings_GB,
a.TABLESPACE_NAME tab_tbs, b.SEGMENT_TYPE TABLE_TYPE, a.last_analyzed
from
dba_tables a,
(select * from
 (select owner tabown, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, sum(bytes/1024/1024/1024) Size_GB
 from
 dba_segments
 where owner='&schema_name' and segment_type like '%TABLE%'
 group by segment_name, owner, SEGMENT_TYPE, TABLESPACE_NAME
 order by 5 desc)
where rownum<11 b="" br="">where a.TABLE_NAME=b.SEGMENT_NAME and a.owner=b.tabown
order by SIZE_GB desc))
e on e.tabown=d.table_owner and e.table_name=d.table_name
where g.segment_name=d.index_name and d.owner=g.owner
group by
e.tabown, e.table_name, size_gb, actual_gb, e.savings_GB, e.tab_tbs, e.table_type, e.last_analyzed, d.owner, d.index_name, d.index_type, d.tablespace_name
order by size_gb desc
;

Resolving Gaps in DataGuard Physical Standby Using RMAN Incremental Backup

1.Defer log_archive_dest_state_2 in primary database

alter system set log_archive_dest_state_2='DEFER';

2.check Current_scn in primary and compare with Standby

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    3436343

3.alter database recover managed standby database cancel -->in standby
 rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 29 13:49:26 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: RMAN (DBID=1700890588)

RMAN>
run {
allocate channel c1 type disk format '/backup/%U.bkp';
backup incremental from scn 3436343 database;
}

4.Transfer the backup pieces to Standby location

5.Backup control file in Primary Database location and transfer to standby location

alter database create standby controlfile as '/tmp/control_standby.ctl';

6.Startup mount physical standby database with latest control file
alter database mount standby database;
rman target /
catalog start with '/backup/%U.bkp';

>recover database;

In Standby Database

SQL> alter database recover managed standby database disconnect from session;

Enable below command in primary

alter system set log_archive_dest_state_2='ENABLE'; 

Archive log GAP detection

Archive log GAP detection:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;



Determining Which Log Files Were Not Received by the Standby Site.


SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

Archive log difference:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

 SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;


set lines 200
col name format a40
col value format a20
 select * from v$dataguard_stats;


  set lines 200
  col type format a30
  col ITEM format a20
  col comments format a20
  select * from v$recovery_progress;
 
 
  SET PAGESIZE 9999
  col day format a15
  SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$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 gv$log ) B;



Check MRP Rate:

set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);


Delete Archive log in standby location:

#!/usr/bin/ksh
ARCH_DIR=/archive/orcl
#take the log number to be applied
LogNo=`tail -30000 /oracle/app/oracle/product/10.2.0/admin/orcl/bdump/alert* | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 5 | tail -1 `
echo "Oracle applied LogNo is $LogNo"
#extract 10
let SecLogNo=${LogNo}-10
echo "new backlog log No: $SecLogNo"
#delete small numbers from this in arch dir
cd $ARCH_DIR
for i in `ls *.arc`
do
Newi=`echo $i | cut -d "_" -f 4`
if [ $Newi -lt $SecLogNo ] ; then
echo "$i to be deleted..."
rm $i
fi
done

TSPITR

Tablespace point time recovery:

run
{
recover tablespace users until time "to_date('13-oct-14 11:00:00','dd-mon-rr hh24:mi:ss')" auxiliary destination '/home/oracle/auxdest';
}


Error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/13/2014 12:20:11
RMAN-03015: error occurred in stored script Memory Script
RMAN-06034: at least 1 channel must be allocated to execute this command

workaround:

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt parms='SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u05)';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u05)';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

How to run APListh Oracle apps?

How to run APListh Oracle apps?

Standalone APList - Payables Data Collection Test [ID 148388.1]

OWB Component not upgraded to 11.2.0.4 from 11.2.0.2

OWB Component not upgraded to 11.2.0.4 from 11.2.0.2

The Database has been upgraded from 11.2.0.2 to 11.2.0.4. Upgrade completed successfully but the OWB component is not upgraded and showing version 11.2.0.4

Solution:

Login as sysdba and execute below scripts

1. execute the script  $ORACLE_HOME\owb\UnifiedRepos\clean_owbsys.sql
2. execute the script  $ORACLE_HOME\owb\UnifiedRepos\cat_owb.sql