Show recovery details, e.g. archive logs etc.
Parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
V$INSTANCE
INSTANCE_NAME HOST_NAME VERSION ARCHIVE STARTUP_TIME STATUS
---------------- -------------------- ----------------- ------- ------------------- ------------
DEV011 linux0001 11.2.0.3.0 STARTED 02/10/2015 00:12:36 OPEN
V$DATABASE
NAME FLASHBACK_ON PLATFORM_NAME LOG_MODE SWITCHOVER_STATUS DATAGUAR GUARD_S CURRENT_SCN DB_UNIQUE_NAME
---------- ------------------ ------------------------------ ------------ -------------------- -------- ------- ---------------- ------------------------------
DEV01 NO Linux x86 64-bit ARCHIVELOG NOT ALLOWED DISABLED NONE 12746494795204 DEV01
V$FLASH_RECOVERY_AREA_USAGE
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE .01 0 1
REDO LOG .66 0 5
ARCHIVED LOG .22 0 3
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
V$RECOVERY_FILE_DEST
NAME Limit (MB) Used (MB) Reclaimable (MB) No. files
----------------------------------- ---------------- ---------------- ---------------- ----------
+DGFRA 486,400 4,283 0 9
V$ARCHIVE_DEST
DEST_ID DEST_NAME STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE DESTINATION LOG_SEQUENCE REOPEN_SECS DELAY_MINS MAX_CONNECTIONS NET_TIMEOUT PROCESS REG FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE ERROR ALTERNATE DEPENDENCY REMOTE_TEMPLATE QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME VER COMPRES APPLIED_SCN
---------- ------------------------------ --------- --------- ------- ------- ---------- -------- -------------------------------------------------- ------------ ----------- ---------- --------------- ----------- ---------- --- ------------------- ------------- ---------- ------------- ----------- -------------------- ---------- ---------- -------------------- ---------- ---------- ---------- ------------ ------------ --- ------- ---------------- --------------- ------------ ------------------------------ --- ------- -----------
1 LOG_ARCHIVE_DEST_1 VALID MANDATORY SYSTEM PRIMARY ARCH ACTIVE USE_DB_RECOVERY_FILE_DEST 35263 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC YES ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
2 LOG_ARCHIVE_DEST_2 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
3 LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
4 LOG_ARCHIVE_DEST_4 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
5 LOG_ARCHIVE_DEST_5 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
6 LOG_ARCHIVE_DEST_6 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
7 LOG_ARCHIVE_DEST_7 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
8 LOG_ARCHIVE_DEST_8 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
9 LOG_ARCHIVE_DEST_9 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
10 LOG_ARCHIVE_DEST_10 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
11 LOG_ARCHIVE_DEST_11 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
12 LOG_ARCHIVE_DEST_12 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
13 LOG_ARCHIVE_DEST_13 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
14 LOG_ARCHIVE_DEST_14 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
15 LOG_ARCHIVE_DEST_15 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
16 LOG_ARCHIVE_DEST_16 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
17 LOG_ARCHIVE_DEST_17 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
18 LOG_ARCHIVE_DEST_18 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
19 LOG_ARCHIVE_DEST_19 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
20 LOG_ARCHIVE_DEST_20 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
21 LOG_ARCHIVE_DEST_21 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
22 LOG_ARCHIVE_DEST_22 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
23 LOG_ARCHIVE_DEST_23 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
24 LOG_ARCHIVE_DEST_24 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
25 LOG_ARCHIVE_DEST_25 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
26 LOG_ARCHIVE_DEST_26 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
27 LOG_ARCHIVE_DEST_27 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
28 LOG_ARCHIVE_DEST_28 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
29 LOG_ARCHIVE_DEST_29 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
30 LOG_ARCHIVE_DEST_30 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
31 LOG_ARCHIVE_DEST_31 INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 1 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE 0
V$ARCHIVED_LOG (last 24 hours)
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APPLIED DEL S COMPLETION_TIME DIC DIC END BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED BAC
---------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------------- ------------------- ------------ ------------- ------------------- ------------ ------------------- ---------- ---------- ------- ------- --- --- --------- --- - ------------------- --- --- --- ------------ ---------------- ----------- --- --- --- ---------- ---
123783 892839980 1 2 84506 4.5856E+12 11/02/2013 12:18:13 807106693 1.2746E+13 11/10/2015 09:00:28 1.2746E+13 11/10/2015 19:06:18 133911 512 ARCH ARCH NO YES NO YES D 11/10/2015 19:06:20 NO NO NO 1 2 181980983 YES NO NO NO
123784 892839991 1 1 35262 4.5856E+12 11/02/2013 12:18:13 807106693 1.2746E+13 11/10/2015 02:28:12 1.2746E+13 11/10/2015 19:06:20 1045483 512 FGRD FGRD NO YES NO YES D 11/10/2015 19:06:31 NO NO NO 1 1 181980983 YES NO NO NO
123785 892862130 +DGFRA/DEV01/archivelog/2015_10_12/thread_2_seq_84507.1427.892862129 1 2 84507 4.5856E+12 11/02/2013 12:18:13 807106693 1.2746E+13 11/10/2015 19:06:18 1.2746E+13 12/10/2015 01:15:28 165485 512 ARCH ARCH NO YES NO NO A 12/10/2015 01:15:30 NO NO NO 0 2 181980983 YES NO NO NO
123786 892862192 +DGFRA/DEV01/archivelog/2015_10_12/thread_2_seq_84508.1242.892862193 1 2 84508 4.5856E+12 11/02/2013 12:18:13 807106693 1.2746E+13 12/10/2015 01:15:28 1.2746E+13 12/10/2015 01:16:31 160899 512 ARCH ARCH NO YES NO NO A 12/10/2015 01:16:32 NO NO NO 0 2 181980983 YES NO NO NO
123787 892866594 +DGFRA/DEV01/archivelog/2015_10_12/thread_1_seq_35263.1021.892866579 1 1 35263 4.5856E+12 11/02/2013 12:18:13 807106693 1.2746E+13 11/10/2015 19:06:20 1.2746E+13 12/10/2015 02:29:38 1822732 512 ARCH ARCH NO YES NO NO A 12/10/2015 02:29:54 NO NO NO 0 1 181980983 YES NO NO NO
V$LOG
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 2 84509 104857600 512 2 NO CURRENT 1.2746E+13 12/10/2015 01:16:31 2.8147E+14
2 2 84508 104857600 512 2 YES INACTIVE 1.2746E+13 12/10/2015 01:15:28 1.2746E+13 12/10/2015 01:16:31
5 1 35262 1048576000 512 2 YES INACTIVE 1.2746E+13 11/10/2015 02:28:12 1.2746E+13 11/10/2015 19:06:20
6 1 35264 1048576000 512 2 NO CURRENT 1.2746E+13 12/10/2015 02:29:38 2.8147E+14
7 1 35263 1048576000 512 2 YES INACTIVE 1.2746E+13 11/10/2015 19:06:20 1.2746E+13 12/10/2015 02:29:38
V$LOGFILE
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------- ---
1 ONLINE +DGFRA/DEV01/onlinelog/group_1.261.807106695 YES
1 ONLINE +DGDATA/DEV01/onlinelog/group_1.276.807106695 NO
2 ONLINE +DGDATA/DEV01/onlinelog/group_2.268.807106697 NO
2 ONLINE +DGFRA/DEV01/onlinelog/group_2.262.807106697 YES
5 ONLINE +DGDATA/DEV01/onlinelog/group_5.266.807106699 NO
5 ONLINE +DGFRA/DEV01/onlinelog/group_5.257.807106703 YES
6 ONLINE +DGDATA/DEV01/onlinelog/group_6.279.807106709 NO
6 ONLINE +DGFRA/DEV01/onlinelog/group_6.264.807106713 YES
7 ONLINE +DGFRA/DEV01/onlinelog/group_7.263.807106723 YES
7 ONLINE +DGDATA/DEV01/onlinelog/group_7.261.807106719 NO
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set nls_timestamp_format='dd/mm/yyyy hh24:mi:ss.ff';
set echo off
set verify off
set lines 1000
set pages 999
set head on
set feed on
set serveroutput on size 1000000 format wrap
set trimspool on
set termout on
set long 10000
set feed off
spool recov.rpt
prompt
col VALUE_COL_PLUS_SHOW_PARAM for a30
prompt Parameter db_flashback_retention_target
sho parameter db_flashback_retention_target
prompt
prompt V$INSTANCE
select instance_name, host_name, version, archiver, startup_time, status from v$instance;
prompt
prompt V$DATABASE
col name for a10
col platform_name for a30
col current_scn for 999999999999999
select name,
flashback_on,
platform_name,
log_mode,
switchover_status,
dataguard_broker,
guard_status,
current_scn,
db_unique_name
from v$database;
prompt
prompt V$FLASH_RECOVERY_AREA_USAGE
select * from v$flash_recovery_area_usage;
prompt
prompt V$RECOVERY_FILE_DEST
col "Limit (MB)" for 999,999,999,999
col "Used (MB)" for 999,999,999,999
col "Reclaimable (MB)" for 999,999,999,999
col name for a35
select name,
space_limit/(1024*1024) "Limit (MB)",
space_used/(1024*1024) "Used (MB)",
space_reclaimable/(1024*1024) "Reclaimable (MB)",
number_of_files "No. files"
from v$recovery_file_dest;
prompt
prompt V$ARCHIVE_DEST
col dependency for a10
col alternate for a10
col destination for a50
col dest_name for a30
col error for a20
col remote_template for a20
select * from v$archive_dest;
prompt
prompt V$ARCHIVED_LOG (last 24 hours)
col name for a70
select * from v$archived_log
where completion_time > sysdate - 1
order by completion_time;
prompt
prompt
prompt V$LOG
select * from v$log;
prompt V$LOGFILE
column member for a75
select * from v$logfile order by group#;
spool off
set feed on