Oracle add datafile on oracle standby

Oracle add datafile
standby add datafile



col FILE_NAME format a55
select FILE_NAME,TABLESPACE_NAME from dba_data_files
where TABLESPACE_NAME='DM_xxxPROD1_DOCBASE';



FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase01.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase02.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase03.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase04.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase05.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase06.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase07.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase08.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase09.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase10.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase11.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase12.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase13.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase14.dbf DM_xxxPROD1_DOCBASE



alter tablespace add datafile '/data/oracle/xxx/oradata/dev9r2/questaims2.dbf' size 100M maxsize 2000M autoextend on;

SQL> ALTER TABLESPACE DM_xxxPROD1_DOCBASE ADD DATAFILE '/opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf' size 2G ;

Tablespace altered.




SQL> !ls -ltr /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:10 /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf



SQL> alter system switch logfile; ---- this statement will create datafile on standby

System altered.


binary format:
SQL> alter database backup controlfile to '/xxxxx/xxxxx/file_name';



then dump the controlfile on human readable format


SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.

SQL> !ls -ltr /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt
-rw-r--r-- 1 oracle dba 9990 Aug 17 13:27 /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt




Now time to check on standby .


ssh on that box


xxx-ora-01dr# ls -ltr /opt/oracle/oradata1/xxxdb/
total 8294416
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:34 dm_xxxprod1_docbase15.dbf <<<<<<<<<<<<<<<<---
-rw-r----- 1 oracle dba 2097160192 Aug 17 13:34 dm_xxxprod1_ind13.dbf




then dump the controlfile on human readable format on standby also


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.

Oracle Archive Gaps in Standby Database

Oracle Standby Gap info

Oracle Standby log gap sql

Standby Gap

On the Both 

set pagesize 200
set linesize 200
select
  substr(local.name,1,70)  "Archive Name",
  case when remote.sequence# is null then 'NOT TRANSMITTED' 
                                     else 'transmitted'
  end,
  local.sequence#,
  local.thread#
from
  (select * from v$archived_log where dest_id = 1) local 
                                    left join 
  (select * from v$archived_log where dest_id = 2) remote
  on local.sequence# = remote.sequence# and
     local.thread#   = remote.thread#
  order by local.sequence#;



Archive Name                                                           CASEWHENREMOTE.  SEQUENCE#    THREAD#
---------------------------------------------------------------------- --------------- ---------- ----------
/u01/oracle/Archive/arch_1_0000000025_780268156.arc                    transmitted             25          1
/u01/oracle/Archive/arch_1_0000000026_780268156.arc                    transmitted             26          1
/u01/oracle/Archive/arch_1_0000000027_780268156.arc                    transmitted             27          1
/u01/oracle/Archive/arch_1_0000000028_780268156.arc                    transmitted             28          1
/u01/oracle/Archive/arch_1_0000000029_780268156.arc                    transmitted             29          1
/u01/oracle/Archive/arch_1_0000000030_780268156.arc                    transmitted             30          1
/u01/oracle/Archive/arch_1_0000000031_780268156.arc                    transmitted             31          1
/u01/oracle/Archive/arch_1_0000000032_780268156.arc                    transmitted             32          1





On Standby

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#);   

   THREAD#  SEQUENCE#
---------- ----------
         1         49
         1         50
         1         51
         1         52
         1         53
         1         54
         1         59
         1         60

8 rows selected.


On Standby

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; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0





SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM  V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         59          1       1381
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1         60        791     102400
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         60        791          1

9 rows selected.




on Primary

column DEST_NAME format A19
column DB_UNIQUE_NAME format A15
column VALID_NOW format A9
column VALID_TYPE format A15
column VALID_ROLE format A15
select DEST_NAME,DB_UNIQUE_NAME,VALID_NOW ,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST WHERE DB_UNIQUE_NAME <> 'NONE';

DEST_NAME           DB_UNIQUE_NAME  VALID_NOW VALID_TYPE      VALID_ROLE
------------------- --------------- --------- --------------- ---------------
LOG_ARCHIVE_DEST_2  STANDBY         YES       ONLINE_LOGFILE  PRIMARY_ROLE





col archive_log_seq format 9999999
col dest format a10
select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#),'2' dest from v$archived_log where DEST_ID=2


or

select * from (select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#) archive_log_seq ,'2' dest from v$archived_log where DEST_ID=2 )

ARCHIVE_LOG_SEQ DEST
--------------- ----------
             60 1
             60 2


 select pri , stand , pri - stand "Diff" from ( select max(SEQUENCE#) pri from v$archived_log where DEST_ID=1) , (select max(b.SEQUENCE#) stand from v$archived_log b where b.DEST_ID=2 );

       PRI      STAND       Diff
---------- ---------- ----------
        60         60          0





On standby

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS ;

or

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS where ARCHIVED_THREAD# !=0 ;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               1            60               0            0
               1            60               1           60




on Stnadby

alter session set nls_date_format='dd-mon-yy hh24:mi:ss'
 
Session altered.


select app_thread, seq_app, tm_applied,
nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd
from (select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread
from v$archived_log where applied = 'YES'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'YES'
group by thread# ) ),(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread
from v$archived_log where applied = 'NO'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'NO'
group by thread# )
)
where rcvd_thread(+)= app_thread
/



APP_THREAD    SEQ_APP TM_APPLIED           SEQ_RCVD TM_RCVD
---------- ---------- ------------------ ---------- ------------------
         1         60 19-jan-13 07:04:29         60 19-jan-13 07:04:29





On primary

select (select name from V$DATABASE) database ,(select max(sequence#) from v$archived_log where dest_id=1) Current_primary_seq,
( select max(sequence#) from v$archived_log where next_time > sysdate - 1
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v$archived_log
where next_time > sysdate - 1 and dest_id=2 and applied='NO'),0) from dual) "To be applied",
((select max(sequence#) from v$archived_log
where dest_id=1) - (select max(sequence#) from v$archived_log where dest_id=2)) "To be Shipped" from dual 
/

DATABASE  CURRENT_PRIMARY_SEQ   MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
VIHAAN                     60         60             0             0




On both The site Primary and Standby 

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,(select thread# thrd, max(sequence#) lhmax from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd 

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                60               60


 
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

    Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



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;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0




On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 
   Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



On Physical Standby

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;


    Thread Last Sequence Generated
---------- -----------------------
         1                      60

set pagesize 0
select 
'Thread                     : ' || THREAD   		||chr(10)||
'Prod - Archived            : ' ||PR_Archived 		||chr(10)||
'STBY - Archived            : ' ||STBY_ARCHIVED 	||chr(10)||
'STBY - Applied             : ' ||STBY_APPLIED 		||chr(10)||
'Shipping GAP (PR -> STBY)  : '	||SHIPPING_GAP_STBY ||chr(10)||
'Applied GAP (STBY -> STBY) : '	||APPLIED_GAP_STBY_STBY
from (
select
t1 THREAD,
pricre "PR_ARCHIVED",
stdcre "STBY_ARCHIVED",
stdnapp "STBY_APPLIED",
pricre-stdcre "SHIPPING_GAP_STBY",
stdcre-stdnapp "APPLIED_GAP_STBY_STBY"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
;
set pagesize 80



Thread                     : 1
Prod - Archived            : 7105
STBY - Archived            : 7105
STBY - Applied             : 7104
Shipping GAP (PR -> STBY)  : 0
Applied GAP (STBY -> STBY) : 1





set linesize 200 pagesize 200
alter session set nls_date_format='dd-mm-YYYY hh24:mi'; 
col destination for a15
select /*+ parallel(8) */ sysdate, ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq   from v$archived_log val, v$database vdb
           where val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)   from v$archived_log val, v$database vdb
              where  val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),  0)   "applied_gap",
         (SELECT MAX (sequence#) highiest_seq    from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                     AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
              where val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'), 0)     "received_gap",
       NVL (
          (SELECT MAX (sequence#)   from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'), 0) "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),  0)   "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar;



SYSDATE             inst_id    dest_id dest_stat destination     applied_gap received_gap last_received_seq last_applied_seq
---------------- ---------- ---------- --------- --------------- ----------- ------------ ----------------- ----------------
17-08-2023 14:58          1          2 VALID     *********_****5              1          0       7105             7104






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;




SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES')
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log );



LOGS             TIME                           THREAD#  SEQUENCE#
---------------- --------------------------- ---------- ----------
Last Applied :   11-JUN-23:08:35:19                   1      35567
Last Received :  11-JUN-23:08:44:26                   1      35568



-- Check that Archive Logs are being Shipped
-- This query needs to be run on the Primary database

SET PAGESIZE 300
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A20
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);








set linesize 200 pagesize 200
col destination for a15
select /*+ parallel(8) */ ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq   from v$archived_log val, v$database vdb
           where val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)   from v$archived_log val, v$database vdb
              where  val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),  0)   "applied_gap",
         (SELECT MAX (sequence#) highiest_seq    from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                     AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
              where val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'), 0)     "received_gap",
       NVL (
          (SELECT MAX (sequence#)   from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'), 0) "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),  0)   "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar;
	



set echo on feed on term on
set linesize 200
col PRIMARY_TIME format a20
col STANDBY_COMPLETION_TIME format a23
col LAG_MINUTES for 99999.99
col db_name for a15

SELECT
sys_context('USERENV','DB_NAME') db_name,
prim.thread# thread,
prim.seq primary_seq,
to_char(prim.tm, 'DD-MON-YYYY HH24:MI:SS') primary_time,
tgt.thread# standby_thread,
tgt.seq standby_seq,
to_char(tgt.tm, 'DD-MON-YYYY HH24:MI:SS') standby_completion_time,prim.seq - tgt.seq seq_gap,( prim.tm - tgt.tm ) * 24 * 60 lag_minutes
FROM
(
SELECT thread#,MAX(sequence#) seq,MAX(completion_time) tm FROM v$archived_log GROUP BY thread#
) prim,
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM v$archived_log
WHERE dest_id IN (SELECT dest_id FROM v$archive_dest
WHERE target = 'STANDBY'
)
AND applied = 'YES'
GROUP BY thread#
) tgt
WHERE prim.thread# = tgt.thread#;





select *
from (select TIMESTAMP,
completion_time "ArchTime",
SEQUENCE#,
round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60,1) "Diff(sec)",
round((blocks * block_size) / 1024 /decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP) OVER(order by TIMESTAMP)) * 24 * 60 * 60),0,1,(TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60),1) "KB/sec",round((blocks * block_size) / (1024 * 1024) /decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60),0,1,(TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP) OVER(order by TIMESTAMP)) * 24 * 60 * 60),3) "MB/sec",
round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) - completion_time) * 24 * 60 * 60,1) "Lag(sec)"
from v$archived_log a, v$dataguard_status dgs
where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc)
where rownum < 10;

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

Oracle  DataGaurd on Standard Edition !!!!!!!!!!!!!!!!!!!
on standby !!!! SE !!!!!! dbvisit

--Prod !!

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from gv$archived_log where  THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2 MAX_SEQ_ARCH1 MAX_SEQ_ARCH2
------------- ------------- ------------- -------------
       524064        470606        524064        470606



---on standby !!!! SE !!!!!! dbvisit
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2
--,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from  gv$archived_log where THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2
------------- -------------
       524064        470605





Physical Standby
set linesize 300
alter session set nls_date_format = 'dd-Mon-yyyy hh24:mi:ss';
select * from ( select sysdate,count(*) To_Be_Restored from x$kcvfh where fhrba_seq=0),
              ( select count(*) restored,max(fhrba_seq) max_sequence,min(fhrba_seq) min_sequence,max(fhscn) max_scn  from x$kcvfh where fhrba_seq!=0),
			  ( select count(*) total,min(fhscn) min_scn,min(fhrba_seq) min_seq from x$kcvfh)

On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 

On Physical Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
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;

-- Standard edition 

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2

--,almax,almax2

 from (select  max(sequence#) almax   from v$archived_log   where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=1)) al1,

      (select  max(sequence#) almax2   from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=2)) al2,

      (select max(sequence#) maxSCN1   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=1)) lh,

  (select max(sequence#) maxSCN2   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=2)) ;

Oracle DBMS_SCHEDULER example

The DBMS_JOB package is replaced by the DBMS_SCHEDULER package in oracle 10g
( oracle job )


login as sys or system

sqlplus / as sysdba

sqlplus>


this job will run daily 4 a.m


begin
dbms_scheduler.create_job
(
job_name => 'RMAN_FULL_F',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
enabled => false,
comments => 'oracle backup RMAN '
);
end;
/

PL/SQL procedure successfully completed.

(start_date =>sysdate+ 1/288 this will start job after 5min )

Notice that the JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE.
The REPEAT_INTERVAL
can be HOURLY, DAILY, MINUTELY, YEARLY or BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOUND.
For example, FREQ=BYWEEKNO=4,7,52 or BYDAY=MON, etc.




exec dbms_scheduler.set_job_argument_value(job_name=>'RMAN_FULL_F',argument_pos
ition=>1 ,argument_value=>'/aptus/oracle/admin/aptdb/script/rman_oracle.sh') ;

PL/SQL procedure successfully completed.

(rman_oracle.sh is the novagenesis Rman backup file )

to eable the job

SQL> exec dbms_scheduler.enable(name=>'RMAN_FULL_F');

PL/SQL procedure successfully completed.



to see the detail of job
select status,run_duration,actual_start_date,additional_info from dba_scheduler_job_run_details
where job_name='RMAN_FULL_F'

oracle dictionary info dict.sql

dict comment
dict info
dictionary comments
@dict.sql
dict.sql


break on sort_key skip1 nodup
col sort_key noprint
col comments format a78 word

def table = &&1

WITH dict_view AS
( SELECT CASE
WHEN SUBSTR(table_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(table_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(table_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, table_name
, comments
FROM dictionary
WHERE table_name LIKE UPPER('%&&1%')
UNION
SELECT CASE
WHEN SUBSTR(v.view_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(v.view_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(v.view_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, NVL(s.synonym_name,v.view_name)
, NVL(d.comments,c.comments)
FROM dba_views v
LEFT OUTER JOIN dictionary d
ON d.table_name = v.view_name
LEFT OUTER JOIN all_synonyms s
ON s.table_name = v.view_name
AND s.table_owner = v.owner
AND s.owner = 'PUBLIC'
LEFT OUTER JOIN dba_tab_comments c
ON c.table_name = v.view_name
AND c.owner = v.owner
WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
AND d.table_name IS NULL )
SELECT sort_key, table_name, comments
FROM ( SELECT sort_key, table_name, comments , ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY table_name NULLS LAST) AS seq
FROM dict_view )
WHERE seq = 1
ORDER BY sort_key, table_name
/
undefine 1



SQL> @dict
Enter value for 1: file
old 11: WHERE table_name LIKE UPPER('%&&1%')
new 11: WHERE table_name LIKE UPPER('%file%')
old 31: WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
new 31: WHERE ( v.view_name LIKE UPPER('V\_$%file%') ESCAPE '\'
old 32: OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
new 32: OR v.view_name LIKE UPPER('GV\_$file%') ESCAPE '\'
old 33: OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
new 33: OR v.view_name LIKE UPPER('DBA\_$file%') ESCAPE '\' )

TABLE_NAME COMMENTS
------------------------------ ------------------------------------------------------------------------------
USER_DBFS_HS_FILES
USER_FILE_GROUPS Details about file groups
USER_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
USER_FILE_GROUP_FILES Details about file group files
USER_FILE_GROUP_TABLES Details about the tables in the file group repository
USER_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
USER_FILE_GROUP_VERSIONS Details about file group versions
USER_SCHEDULER_FILE_WATCHERS Scheduler file watch requests owned by the current user

ALL_FILE_GROUPS Details about file groups
ALL_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
ALL_FILE_GROUP_FILES Details about file group files
ALL_FILE_GROUP_TABLES Details about the tables in the file group repository
ALL_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
ALL_FILE_GROUP_VERSIONS Details about file group versions
ALL_SCHEDULER_FILE_WATCHERS Scheduler file watch requests visible to the current user

DBA_DATA_FILES Information about database data files
DBA_EXP_FILES Description of export files
DBA_FILE_GROUPS Details about file groups
DBA_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
DBA_FILE_GROUP_FILES Details about file group files
DBA_FILE_GROUP_TABLES Details about the tables in the file group repository
DBA_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
DBA_FILE_GROUP_VERSIONS Details about file group versions
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_FILEMETRIC_HISTORY File Metrics History
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_IOSTAT_FILETYPE Historical I/O statistics by file type
DBA_HIST_IOSTAT_FILETYPE_NAME File type names for historical I/O statistics
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_PROFILES Display all profiles and their limits
DBA_SCHEDULER_FILE_WATCHERS All scheduler file watch requests in the database
DBA_SQL_PROFILES set of sql profiles
DBA_TEMP_FILES Information about database temp files

GV$ASM_FILE Synonym for GV_$ASM_FILE
GV$ASM_FILESYSTEM Synonym for GV_$ASM_FILESYSTEM
GV$BACKUP_DATAFILE Synonym for GV_$BACKUP_DATAFILE
GV$BACKUP_SPFILE Synonym for GV_$BACKUP_SPFILE
GV$CONTROLFILE Synonym for GV_$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION Synonym for GV_$CONTROLFILE_RECORD_SECTION

Oracle List of constraints owned by the current user

constraint list
List of constraints owned by the current user
List constraints for a table owned by the current user




SQL> CREATE OR REPLACE TYPE db_constraint_ot AS OBJECT
( owner VARCHAR2(30)
, constraint_name VARCHAR2(30)
, constraint_type VARCHAR2(1)
, table_name VARCHAR2(30)
, search_condition VARCHAR2(32767)
, r_owner VARCHAR2(30)
, r_constraint_name VARCHAR2(30)
, delete_rule VARCHAR2(9)
, status VARCHAR2(8)
, deferrable VARCHAR2(14)
, deferred VARCHAR2(9)
, validated VARCHAR2(13)
, generated VARCHAR2(14)
, bad VARCHAR2(3)
, rely VARCHAR2(4)
, last_change DATE
, index_owner VARCHAR2(30)
, index_name VARCHAR2(30)
, invalid VARCHAR2(7)
, view_related VARCHAR2(14) )
/

Type created.

SQL> CREATE OR REPLACE TYPE db_constraint_tt AS TABLE OF db_constraint_ot;
2 /

Type created.

SQL> CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000) ;
2 /

Type created.







def table = &1
set term off
store set sqlplus_settings.sql replace
TTITLE OFF
var tabname VARCHAR2(30)
var results REFCURSOR
exec :tabname := UPPER('&table')
col table new_value table
SELECT UPPER(:tabname) AS "TABLE" FROM dual;
set def on term on autoprint off feed off lines 110

col type hea "Type"
col constraint_name hea "Constraint name"
col table_name hea "Table name"
col table_owner format a20 hea "Owner"
col status hea "Status"
col deferrable hea "Deferrable?"
col deferred hea "Deferred?"

col search_condition format a60 word hea "Definition"

DECLARE
v_table_exists VARCHAR2(1) := 'Y';
v_constraints DB_CONSTRAINT_TT := DB_CONSTRAINT_TT();

CURSOR c_constraints (cp_tablename all_constraints.table_name%TYPE)
IS
SELECT c.owner
, CASE
WHEN c.generated = 'GENERATED NAME' AND c.constraint_name LIKE 'SYS\_%' ESCAPE '_'
THEN '[' || c.constraint_name || ']'
ELSE c.constraint_name
END AS constraint_name
, c.constraint_type
, c.table_name
, c.search_condition
, TO_CHAR(NULL) AS column_list
, DECODE(c.constraint_type,
'R', 'Foreign key (%COLS%) to ' || r.table_name
|| ' (' || c.r_constraint_name
|| ')'
|| DECODE(c.delete_rule, 'CASCADE', ', ' || c.delete_rule),
'P', 'Primary key (%COLS%)',
'U', 'Unique key (%COLS%)'
) key_description
, CAST
( MULTISET
( SELECT column_name
FROM all_cons_columns
WHERE owner = c.owner
AND constraint_name = c.constraint_name
ORDER BY position
) AS VARCHAR2_TT ) key_columns
, c.r_owner
, c.r_constraint_name
, c.delete_rule
, c.status
, c.deferrable
, c.deferred
, c.validated
, c.generated
, c.bad
, c.rely
, c.last_change
, c.index_owner
, c.index_name
, c.invalid
, c.view_related
FROM all_constraints c
, all_constraints r
WHERE c.table_name = cp_tablename
AND c.owner = USER
AND r.constraint_name (+)= c.r_constraint_name
AND r.owner (+)= c.r_owner;
BEGIN
FOR r IN c_constraints(:tabname)
LOOP
v_constraints.EXTEND;

IF r.key_columns.COUNT > 0 THEN
FOR i IN r.key_columns.FIRST..r.key_columns.LAST LOOP
r.column_list := r.column_list || r.key_columns(i) || ', ';
END LOOP;
r.column_list := RTRIM(r.column_list,', ');
END IF;

r.key_description := REPLACE(r.key_description,'%COLS%', r.column_list);

v_constraints(c_constraints%ROWCOUNT) :=
DB_CONSTRAINT_OT
( r.owner
, r.constraint_name
, r.constraint_type
, r.table_name
, NVL(r.search_condition,r.key_description)
, r.r_owner
, r.r_constraint_name
, r.delete_rule
, r.status
, r.deferrable
, r.deferred
, r.validated
, r.generated
, r.bad
, r.rely
, r.last_change
, r.index_owner
, r.index_name
, r.invalid
, r.view_related );
END LOOP;

IF v_constraints.COUNT = 0 THEN
-- Nothing in ALL_CONSTRAINTS - check table exists:
SELECT MIN('N') INTO v_table_exists
FROM dual
WHERE NOT EXISTS
( SELECT 1 FROM all_tables
WHERE table_name = :tabname
AND owner = USER );

IF v_table_exists = 'N' THEN
DBMS_OUTPUT.PUT_LINE('No such table "' || :tabname || '"');
END IF;
END IF;

OPEN :results FOR
SELECT CASE
WHEN search_condition LIKE '"%" IS NOT NULL' THEN 'Not Null'
WHEN constraint_type = 'C' THEN 'Check'
WHEN constraint_type = 'U' THEN 'Unique'
WHEN constraint_type = 'P' THEN 'Primary'
WHEN constraint_type = 'R' THEN 'FK'
ELSE constraint_type
END AS type
, constraint_name
, search_condition
, status
FROM TABLE(v_constraints)
ORDER BY
CASE
WHEN constraint_type = 'P' THEN '1'
WHEN constraint_type = 'U' THEN '2'
WHEN constraint_type = 'R' THEN '3'
WHEN constraint_type LIKE '"%" IS NOT NULL' THEN '4'
WHEN constraint_type = 'C' THEN '5'
ELSE constraint_type
END
, constraint_name;
END;
/

set term on

print :results





TTITLE ON LEFT 'Tables referencing &table:' SKIP1

SELECT -- r_owner AS table_owner
table_name
, constraint_name
, status
, deferrable
, deferred
FROM all_constraints
WHERE constraint_type = 'R'
AND r_owner = USER
AND r_constraint_name IN
( SELECT constraint_name
FROM user_constraints
WHERE table_name = :tabname
AND constraint_type IN ('P','U') );

prompt
TTITLE ""
TTITLE OFF
@sqlplus_settings.sql




SQL> create table anuj1( x number primary key ) ;


test this script


SQL> @cons
Enter value for 1: ANUJ1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED





SQL> create table anuj2( n number, CONSTRAINT anuj2_fk FOREIGN KEY(n) REFERENCES anuj1(x) );

SQL> desc anuj2
Name Null? Type
----------------------------------------------------------- -------- N NUMBER


SQL> @cons
Enter value for 1: anuj1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED

Tables referencing ANUJ1:
Table name Constraint name Status Deferrable? Deferred?
------------------------------ ------------------------------ -------- -------------- ---------
ANUJ2 ANUJ2_FK ENABLED NOT DEFERRABLE IMMEDIATE





save_sqlplus_settings.sql
set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set termout on
set define "&"




restore_sqlplus_settings.sql
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
set termout on
============================


or


SQL> col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/


TABLE_NAME CONSTRAINT_NAME COLUMNS
-------------------------------- ------------------------------ ----------------------------------------
EMP FK_DEPTNO DEPTNO

How to read oracle Release No ?

read oracle version
Oracle Release Numbers




Release Number Format

Oracle database server labeled "Release 10.2.0.1.0." The significance of each number

10 is the version number
2 is the new features release number
0 is the maintenance release number
1 is the generic patch set number
0 is the platform specific patch set number




Version Number : This is the most general identifier. It represents a major new edition (or version) of the
software and contains significant new functionality.
New Features Release Number : This number represents a new features release level.
Maintenance Release Number : This number represents a maintenance release level. A few new features may also be included.
Generic Patch Set Number : This number identifies a generic patch set. The patch set is applicable across all operating system and
hardware platforms.


SELECT * FROM PRODUCT_COMPONENT_VERSION;

oracle recover option

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

Until time
Performs a incomplete recovery (=Point in time recovery).
The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


recover automatic database until time '2005-02-14:15:45:00';



recover database UNTIL TIME '2010-05-08:08:42:00'


recover database UNTIL TIME '2010-05-08:08:42:00';
recover database until time '2004-03-21:22:59:04'