RMAN Backup and Recovery Scenario Point-In-Time Recovery

Backup and Restore Scenario from existing Level 0 backup
-------------------------------------------------------
steps to recover the database:-

1. Take Level 0 backup.
2. Lost SYSTEM datafile.
3. Start the instance without mounting the database.
4. Restore Controlfile.
5. Mount the database.
6. Restore database.
7. Recovery database.
8. Open the database and reset logs.

1. Take Level 0 backup:

run
{
allocate channel channel1 type disk;
allocate channel channel2 type disk;
allocate channel channel3 type disk;
backup incremental level=0 database;
backup archivelog all;
backup current controlfile;
}

2. Lost SYSTEM datafile.

Note:
----
Need controlfile, backup and parameter file for recoverying database.

3. Start the instance without mounting the database.

$sqlplus "/as sysdba"

SQL> startup nomount;
SQL> exit

4. Restore Controlfile.

$rman target /
RMAN> restore controlfile;

5. Mount the database.
RMAN> alter database mount;

6. Restore and recover & Point-In-Time Recovery of all data back to a particular date/time in the past.
run
{
set until time "to_date('2013-03-20:14:40:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recovery database;
}

7.Open the database and reset logs.

SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

NOTE:
----
As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.

http://ss64.com/ora/rman_pitr_example.html

1008 TRACE file

ISSUE:-
-----
Below error received when trying to run the code having bind variables.
But,same code works correctly in other datadases maintained at same version.

Oracle Database Version: 11.2.0.2.0 - 64 bit (SQL>SELECT BANNER FROM v$version;)
PL/SQL                 : 11.2.0.2.0
OS Version             : Linux x86_64        ($uname -ms)

OS Version
----------
$uname -ms
Linux x86_64

ERROR:
-----
ERROR at line 1:
ORA-01008:not all variables bound
ORA-06512:at line 77

Running in to Bug 14458214 (5/rdbms/partitioning) Unexpected ORA-01008 from select on composite partitioned table.

The workaround mentioned in Bug 14458214 is as follows:
Try executing this from the sqlplus session before you execute the anonymous block.

alter session set "_and_pruning_enabled"=false;
alter session set "_subquery_pruning_enabled"=false;
alter session set "_optimizer_table_expansion"=false;
--execute the anonymous PL/SQL block which fails, here

How to Generate 1008 TRACE file from Oracle Database
----------------------------------------------------
Set an 1008 ERRORSTACK event to confirm the stack.

Database Details
----------------
sqlplus "/as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

Generate 1008 TRACE file at session level
-----------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST1008';

alter session set events '1008 trace name ERRORSTACK level 4';
--execute the statement which fails, here
alter session set events '1008 trace name context off';

Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace';    -------------- 11G

INST_ID  NAME        VALUE
-------  ----------  ---------
         Diag Trace  /trace/file/location/

exit

Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST1008

10046 TRACE file

How to Generate 10046 TRACE file from Oracle Database
-----------------------------------------------------

Database Details
----------------
sqlplus "/as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

Generate 10046 TRACE file at session level
------------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST10046';

alter session set events '10046 trace name context forever, level 12';
--execute the statement which fails, here
alter session set events '10046 trace name context off';

Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace';    -------------- 11G

INST_ID  NAME        VALUE
-------  ----------  ---------
         Diag Trace  /trace/file/location/

exit

Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST10046

Locks on Database

Locks on Database
-----------------
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;


Query to find which is locking the other session:
------------------------------------------------

set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20

select sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;

SID STATUS USERNAME  SQL_TEXT FIRST_LOAD_TIME
--- ------ --------  -------- ---------------

To find the Sid and their longops:
--------------------------------

set pages 50000 lines 32767
col OPNAME form a22

select l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS from gv$session_longops l,gv$session s where s.sid=l.sid and s.serial#=l.serial#;

     SID SERIAL#    OPNAME   SOFAR      TOTALWORK  TIME_REMAINING ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- -------------- ---------------

To find the locks for the sid:
-----------------------------

set pages 50000 lines 32767
col OWNER for a20
col NAME for a20

SELECT * FROM dba_dml_locks where SESSION_ID='&sid';

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------


To find any holding sessions:
----------------------------

select * from dba_blockers;

HOLDING_SESSION
---------------
            161

To find waiters:
---------------

set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10

select * from dba_waiters;


WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
                            
Blocking details:
----------------

set pages 50000 lines 32767

select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

set pages 50000 lines 32767

col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;  2    3    4    5    6    7    8    9   10

BLOCKER SID       'ISBLOCKING' BLOCKEE SID
------- ---------- ----------  ------- --------

Find locks:
----------

set pages 50000 lines 32767

select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'|| c.object_name||' since '||round(d.last_call_et/60)||' Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||' Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0 and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and b.sid=f.sid order by d.last_call_et desc;

Another way to find locks:
-------------------------

set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;


List of blocking session:
-------------------------
set pages 9999 lines 300
select
   blocking_session,
   sid,
   serial#,
   wait_class,
   seconds_in_wait
from
   v$session
where
   blocking_session is not NULL
order by
   blocking_session;

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------


Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
    from v$session a, v$sqlarea b
    where a.sql_address=b.address
    and a.username='EODSBTCH';

Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/

Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID        format a15
col osuser    format a15
col login_time    format a14
select     username
,    osuser
,    sid || ',' || serial# "ID"
,    status
,    to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,    last_call_et
from    v$session
where    username is not null
order    by login_time
/

Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select    s.sid || ',' || s.serial# "SID/SERIAL"
,    s.username
,    s.osuser
,    p.spid "OS PID"
,    s.program
from    v$session s
,    v$process p
Where    s.paddr = p.addr
order     by to_number(p.spid)
/

Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/

Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select    s.username
,    s.sid
,    s.serial#
,    p.spid
,    last_call_et
,    status
from     V$SESSION s
,    V$PROCESS p
where    s.PADDR = p.ADDR
and    p.spid='&pid'
/

RMAN Troubleshooting

RMAN Troubleshooting Queries at Database Level

SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40

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

ARCHIVE LOG LIST;

SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;

select * from v$backup;

SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;

SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;

SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;

SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;

SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;

SELECT group#,member
FROM v$logfile;

SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;

How to run SQL scripts at DB level from OS level

$nohup sqlplus '/ as sysdba' @script_name.sql &

Longops DATAPUMP

DATAPUMP STATUS
---------------

 set pages 50000 lines 32767
col OPNAME for a10
col UNITS for a10
col TARGET_DESC for a10
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOBNAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork
/

Currently Active DataPump Operations
---------------------------------------
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Operations'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'JobName'
COL operation           FORMAT A12      HEADING 'Operation'
COL job_mode            FORMAT A12      HEADING 'JobMode'
COL state               FORMAT A12      HEADING 'State'
COL degree              FORMAT 9999     HEADING 'Degr'
COL attached_sessions   FORMAT 9999     HEADING 'Sess'

SELECT
 owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs
/

Currently Active DataPump Sessions
-------------------------------------
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Sessions'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'Job'
COL osuser              FORMAT A12      HEADING 'UserID'

SELECT
 DPS.owner_name
,DPS.job_name
,S.osuser
,S.sid
,S.serial#
,S.status
FROM
 dba_datapump_sessions DPS
,v$session S
WHERE S.saddr = DPS.saddr
/

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.
-----------------
set pages 9999 lines 300

COL owner_name FORMAT a10
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.
---------------------
set pages 9999 lines 300

COL owner_name FORMAT a10
COL job_name FORMAT a20
 
select owner_name,job_name,saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

Longops EXPORT

EXPORT STATUS
-------------
jobs
or
ps -ef| grep expdp or ps -ef| grep exp or ps -ef|grep <processid>
or
ls -lrth
tail -f nohup.out
tail -f SOURCE_TABLENAME_EXP.log

cat SOURCE_TABLENAME_EXP.log
(OR)

EXPORT STATUS
-------------

$expdp attach=<JOB_NAME>
username: / as sysdba
EXPDP>STATUS

If required, to kill EXPORT job
CTRL+C
EXPDP>ATTACH=SOURCE_TABLE_EXPORT
EXPDP>STATUS
EXPDP>STOP_JOB=IMMEDIATE (OR) KILL_JOB


(OR)
EXPORT STATUS
-------------
set pages 50000 lines 32767
col UNITS for a5
col TARGET_DESC for a11
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOB_NAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,username,opname,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork and target_desc='EXPORT'
/
(OR)

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
 
select owner_name,job_name,s.saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

EXP
---
nohup exp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME &

Longops IMPORT

IMPORT STATUS
------------
jobs
or
ps -ef| grep impdp or ps -ef| grep imp or ps -ef|grep <processid>
or
ls -lrth
tail -f nohup.out
tail -f IMPORT_SOURCE_TABLENAME_EXP.log

cat  IMPORT_SOURCE_TABLENAME_EXP.log

(OR)

IMPORT STATUS
------------
 $impdp attach=<JOB_NAME>
username: / as sysdba
IMPDP>STATUS

If required, to kill IMPORT job

CTRL+C
IMPDP>ATTACH=SOURCE_TABLE_IMPORT
IMPDP>STATUS
IMPDP>STOP_JOB=IMMEDIATE (OR) KILL_JOB


IMPORT STATUS
------------
 set pages 50000 lines 32767
col OPNAME for a10
col UNITS for a5
col TARGET_DESC for a10
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOB_NAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork
/

IMPORT SPEED
------------

set pages 50000 lines 32767
col TABLE_NAME for a80

SELECT SUBSTR(sql_text, INSTR(sql_text,'INTO "'),120) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE sql_text like 'insert %into "%' AND command_type = 2 AND open_versions > 0
/

(OR)

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

set pages 50000 lines 32767

COL owner_name FORMAT a10
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
 
select owner_name,job_name,saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

IMP
---
nohup imp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=IMPORT_SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME IGNORE=y FROMUSER=OWNER TOUSER=OWNER &

Longops - RMAN

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

Longops - RMAN
------------------------
set pages 50000 lines 32767

col USERNAME for a10
col OPNAME for a10
col TARGET_DESC for a10
col CONTEXT for a10
col MESSAGE for a30

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",
TIME_REMAINING/60 REMAINING_MINS 
FROM GV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK
/
Longops - RMAN
------------------------
set pages 9999 lines 300
col status for a10
col object_type for a10

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select SID,STATUS,START_TIME,END_TIME,OBJECT_TYPE,OUTPUT_DEVICE_TYPE
from v$rman_status order by START_TIME desc;

select max(START_TIME) "START_TIME", END_TIME,
ELAPSED_SECONDS/60 "ELAPSED_MINS",
OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS
from v$rman_backup_job_details
group by START_TIME,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS
having max(START_TIME) > sysdate-1
/

Longops Query using v$session_longops

Longops.sql
-----------
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0
/

Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.

Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id and b.sid='&sid'
/

set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10

select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv$session s,gv$process p where s.addr=p.addr and s.sid='&sid'
/

$ps -ef | grep <spid>

set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
 set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10 
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/

OR - Use the below Query

Longops.sql
-----------
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS", round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE", l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0
/

How to Drop SCHEMA Objects in Oracle

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_obj.sql

select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by  object_type,object_name
/

spool off;
set head on

@ target_schemaname_drop_obj.sql
exit

DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_other_obj.sql

select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/

spool off;
set head on

@target_schemaname_drop_other_obj.sql
exit

ls -lrth target_schemaname_drop*.sql

vi target_schemaname_drop_obj.sql  /  vi target_schemaname_drop_other_obj.sql

:wq

sqlplus "/as sysdba"

@target_schemaname_drop_obj.sql

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

exit