How to find the Full Table Scans in the Database

 SELECT sp.sql_id ,sp.object_owner ,sp.object_name ,sa.sql_text as sqltext

,sa.sql_fulltext AS sql_fulltext ,sa.executions as no_of_full_scans ,tbl.row_num

,tbl.blocks ,tbl.buff_pool FROM v$sql_plan sp -- LEFT JOIN v$sqlarea sa ON

sa.address = sp.address AND sa.hash_value = sp.hash_value -- JOIN (SELECT

table_name ,owner ,num_rows AS row_num ,blocks ,buffer_pool AS buff_pool FROM

dba_tables WHERE 1 = 1) tbl ON tbl.table_name = sp.object_name AND tbl.owner =

sp.object_owner -- WHERE 1=1 AND operation = 'TABLE ACCESS' AND options = 'FULL'

AND object_owner = 'ORACLE_WORLD' -- for a particular user (or comment out to

get all) AND tbl.row_num >= 100000 -- limit the table size to filter out small

tables -- ORDER BY no_of_full_scans DESC

How to restore a sequence of archivelogs back to their original location

connect catalog RMAN//*********@RMANCAT

connect target
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=ora_dbserver4506zone1,NB_ORA_CLIENT=dbserver4505-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
restore archivelog from logseq 32567 until logseq 32569;
release channel t1;
release channel t2;
}

How to find the SQL script which executed more than 5 hrs in database

set wrap off
col elapsed_time_delta format 9999999999
col plan_hash_value    format 9999999999
col seconds            format 99999
col executions_total   format 99999999

select
   stat.sql_id,
   plan_hash_value,
   rpad(parsing_schema_name,10) "schema",elapsed_time_total/1000000 "seconds",  
   elapsed_time_delta,disk_reads_delta,
   stat.executions_total,
   to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "endtime",
   rpad(sql_text,40) text,ss.snap_id
from
   dba_hist_sqlstat  stat,
   dba_hist_sqltext  txt,
   dba_hist_snapshot ss
where
   stat.sql_id = txt.sql_id
and
   stat.dbid = txt.dbid
and
   ss.dbid = stat.dbid
and
   ss.instance_number = stat.instance_number
and
   stat.snap_id = ss.snap_id
and
   parsing_schema_name not like 'sys%'
and
   ss.begin_interval_time >= sysdate-40 ----you can change it accordingly
and
   stat.elapsed_time_total/1000000 > 18000 ----you can change it accordingly
order by
elapsed_time_total desc;

how to restore the database upto until time( point in time recovery) using RMAN

Let Say you got a request from a application team that , their application upgrade has failed and they wants to restore back the database to back date.

Application team asked you to restore the database to specific date in our case they wants to restore the database to 2017-05-18 22:00 CET

1. We can check by the RMAN preview command if we have available backups to restore or recover the database or not , it will not do the actual restore and recovery , also it will not check that backups are valid to restore or not, we have to use RMAN VALIDATE command in order to check the validity if the backups.

run {
set command id to 'rman restore until time';
set until time "to_date('2017-05-18:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
allocate channel t1 type 'sbt_tape' parms
                'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_sfadb.opt)';
restore database preview;
recover database preview;
}

2. After the successful completion of the above command we can restore/recover the database actually:

run {
set command id to 'rman restore until time';
        set until time "to_date('2017-05-18:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
        shutdown abort;
        startup nomount;
        allocate channel t1 type 'sbt_tape' parms
                'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_sfadb.opt)';
        alter database mount;
        restore database;
        recover database;
        release channel t1;
        sql 'alter database open resetlogs';
} 

Script to find the status of the RMAN backup from the command prompt

SQL> select start_time,end_time, status, input_type from v$rman_backup_job_details order by start_time;

START_TIM END_TIME  STATUS                  INPUT_TYPE
--------- --------- ----------------------- -------------
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               DB FULL
12-MAY-17 12-MAY-17 COMPLETED               DB FULL

START_TIM END_TIME  STATUS                  INPUT_TYPE
--------- --------- ----------------------- -------------
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               DB FULL
13-MAY-17 13-MAY-17 COMPLETED               DB FULL
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA

START_TIM END_TIME  STATUS                  INPUT_TYPE
--------- --------- ----------------------- -------------
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               DB FULL
14-MAY-17 14-MAY-17 COMPLETED               DB FULL
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA

START_TIM END_TIME  STATUS                  INPUT_TYPE
--------- --------- ----------------------- -------------
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED               RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED               RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED               RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED               DB FULL
15-MAY-17 15-MAY-17 COMPLETED               DB FULL
15-MAY-17 15-MAY-17 COMPLETED               RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED               RECVR AREA

How to find out upto what time we can flashback our database

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COLUMN oldest_flash_scn FOR 999,999,999

ALTER SESSION
   SET nls_date_format='DD MON YYYY hh24:mi:ss'
/

SELECT
   oldest_flashback_scn,
   oldest_flashback_time
FROM
   v$flashback_database_log
/

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM

-------------------- --------------------

           787294429 22 MAY 2017 06:35:11

How to find the Lost logfile of RMAN?

How to find the Lost logfile of RMAN?

If we lost the RMAN logfile and want to regenerate the logfile, we can find it out from the store of  database. As it is stored in the database and if database went down all data will lost.

We can find it out by using below query:

col STATUS format a9
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi:ss') start_time
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time
/
set pages 0
set lines 130
set trims on
set feedback off
set ver off
define sess_key=&session_key
spool rman_&sess_key..output
select output
from v$rman_output
where session_key = &sess_key
order by recid;
/
spool off
prompt Your outputfile is called rman_&sess_key..output
exit

==================================
output:

SQL> @saurabh.sql
SESSION_KEY INPUT_TYPE    STATUS    START_TIME
----------- ------------- --------- -----------------
         71 CONTROLFILE   COMPLETED 06/08/12 23:00:46
         76 DB FULL       COMPLETED 06/09/12 18:00:09
         81 DB FULL       COMPLETED 06/10/12 18:00:09
         86 DB FULL       COMPLETED 06/11/12 18:00:08
         91 DB FULL       COMPLETED 06/12/12 18:00:09
         96 DB FULL       COMPLETED 06/13/12 18:00:07
        101 DB FULL       COMPLETED 06/14/12 18:00:07
        106 DB FULL       COMPLETED 06/15/12 18:00:09
        111 DB FULL       COMPLETED 06/16/12 18:00:09
        116 DB FULL       COMPLETED 06/17/12 18:00:08
        121 DB FULL       COMPLETED 06/18/12 18:00:07
SESSION_KEY INPUT_TYPE    STATUS    START_TIME
----------- ------------- --------- -----------------
        126 DB FULL       COMPLETED 06/19/12 18:00:07
        131 DB FULL       COMPLETED 06/20/12 18:00:07
        136 DB FULL       COMPLETED 06/21/12 18:00:08
        141 DB FULL       COMPLETED 06/22/12 18:00:08
        146 DB FULL       COMPLETED 06/23/12 18:00:09
        151 DB FULL       COMPLETED 06/24/12 18:00:15
        156 DB FULL       COMPLETED 06/25/12 18:00:08
        161 DB FULL       COMPLETED 06/26/12 18:00:08
        166 DB FULL       COMPLETED 06/27/12 18:00:08
        171 DB FULL       COMPLETED 06/28/12 18:00:07
        176 DB FULL       COMPLETED 06/29/12 18:00:09
SESSION_KEY INPUT_TYPE    STATUS    START_TIME
----------- ------------- --------- -----------------
        181 DB FULL       COMPLETED 06/30/12 18:00:08
        186 DB FULL       COMPLETED 07/01/12 18:00:08
        191 DB FULL       COMPLETED 07/02/12 18:00:12
        196 DB FULL       COMPLETED 07/03/12 18:00:09
        201 DB FULL       COMPLETED 07/04/12 18:00:08
        206 DB FULL       COMPLETED 07/05/12 18:00:09
        211 DB FULL       COMPLETED 07/06/12 18:00:11
29 rows selected.
Enter value for session_key: 211

------
-----

hannel d2: backup set complete, elapsed time: 00:02:55
Finished backup at 06-JUL-12
released channel: d2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
List of Archived Log Copies for database with db_unique_name GFSDEV
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
165     1    165     A 04-JUL-12
        Name: /u01/apps/gfsdevdb/11.2.0/dbs/arch1_165_784089575.dbf
166     1    166     A 04-JUL-12
        Name: /u01/apps/gfsdevdb/11.2.0/dbs/arch1_166_784089575.dbf
167     1    167     A 04-JUL-12
        Name: /u01/apps/gfsdevdb/11.2.0/dbs/arch1_167_784089575.dbf
168     1    168     A 04-JUL-12
        Name: /u01/apps/gfsdevdb/11.2.0/dbs/arch1_168_784089575.dbf
deleted archived log
archived log file name=/u01/apps/gfsdevdb/11.2.0/dbs/arch1_165_784089575.dbf RECID=165 STAMP=787770523
deleted archived log
archived log file name=/u01/apps/gfsdevdb/11.2.0/dbs/arch1_166_784089575.dbf RECID=166 STAMP=787791323
deleted archived log
archived log file name=/u01/apps/gfsdevdb/11.2.0/dbs/arch1_167_784089575.dbf RECID=167 STAMP=787791325
deleted archived log
archived log file name=/u01/apps/gfsdevdb/11.2.0/dbs/arch1_168_784089575.dbf RECID=168 STAMP=787829361
Deleted 4 objects

Starting backup at 06-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-JUL-12
channel ORA_DISK_1: finished piece 1 at 06-JUL-12
piece handle=/backup/GFSDEV_backup/database/GFSDEV_20120706_i7nfeodq_1_1.ctl.bkp tag=TAG20120706T225906 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 06-JUL-12


Your outputfile is called rman_211.output