SQL: standby database information

This script is validated against Oracle 11g.

Run this script on the standby database to obtain basic information about the database state and the managed recovery processes:

set linesize 300
set pagesize 100
col "controlfile type" format a30
col "Unique name" format a12
col "Open Mode" format a12
col "Flashback on" format a15
col "Protection Mode" format a30
col "Current SCN" format 9999999999999
col "Database Role" format a20
col "Log Mode" format a20
col "DG broker" format a20
col "Destination name" format a20
col "Destination" format a20
col member format a70
col type format a20
col units format a20
col name format a15
set feedback off
set trimspool on
spool standby_info.log
alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:SS:MI';
Prompt
Prompt ======================================================================================================================
prompt General information:
select name,db_unique_name as "Unique name",created,open_mode as "Open Mode",flashback_on as "Flashback on",resetlogs_time as "Resetlogs time",current_scn as "Current SCN"
from v$database
/
Prompt ======================================================================================================================
prompt Standby database information, if relevant:
select database_role as "Database Role",log_mode as "Log Mode",controlfile_type as "Controlfile type",protection_mode as "Protection Mode"
from v$database
/
Prompt ======================================================================================================================
select dest_name as "Destination Name",status,type,database_mode,recovery_mode,destination,archived_seq#,applied_seq#
from v$archive_dest_status
where status <> 'INACTIVE'
/
Prompt ======================================================================================================================
prompt Is the MRP process running?
select process, status from v$managed_standby
where process like '%MRP%'
/
Prompt ======================================================================================================================
Prompt Are there standby redo logs configured?
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#
/
Prompt ======================================================================================================================
Prompt Check the recovery progress:
select START_TIME
,TYPE
,ITEM
,UNITS
,SOFAR
,TOTAL
,TIMESTAMP
from v$recovery_progress
/
Prompt ======================================================================================================================
prompt Data guard broker information, if relevant:
select dataguard_broker as "DG broker"
from v$database
/
Prompt ======================================================================================================================
exit

Example output. Note particularly the output in red. When the archived_seq# and the applied_seq# match, together with an MRP process that is in status "WAITING_FOR_LOG", your standby database is in synch with the primary.

======================================================================================================================
General information:

NAME            Unique name  CREATED             Open Mode    Flashback on    Resetlogs time         Current SCN
--------------- ------------ ------------------- ------------ --------------- ------------------- --------------
PRODDB01        STBDB01      19-01-2009 15:25:32 MOUNTED      YES             17-11-2012 13:17:40  7203583291843
======================================================================================================================
Standby database information, if relevant:

Database Role        Log Mode             Controlfile type               Protection Mode
-------------------- -------------------- ------------------------------ ------------------------------
PHYSICAL STANDBY     ARCHIVELOG           STANDBY                        MAXIMUM PERFORMANCE
======================================================================================================================

Destination Name     STATUS    TYPE                 DATABASE_MODE   RECOVERY_MODE  DESTINATION  ARCHIVED_SEQ# APPLIED_SEQ#
-------------------- --------- -------------------- --------------- -------------- ------------ ------------- ------------
LOG_ARCHIVE_DEST_2   DEFERRED  PHYSICAL             MOUNTED-STANDBY MANAGED        PRODDB01      0                0
LOG_ARCHIVE_DEST_10  VALID     PHYSICAL             MOUNTED-STANDBY MANAGED                      48610            0
STANDBY_ARCHIVE_DEST VALID     PHYSICAL             MOUNTED-STANDBY MANAGED                      48609         48609
======================================================================================================================
Is the MRP process running?

PROCESS   STATUS
--------- ------------
MRP0      WAIT_FOR_LOG
======================================================================================================================
Are there standby redo logs configured?

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                                                 TYPE
---------- ---------- ---------- --- ---------- ---------------------------------------------------------------------- --------------------
         6          1      48611 YES ACTIVE     /data/oracle/u01/STBDB01/stb_redo_6a.dbf                              STANDBY
         6          1      48611 YES ACTIVE     /data/oracle/u01/STBDB01/stb_redo_6b.dbf                              STANDBY
         7          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7a.dbf                              STANDBY
         7          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7b.dbf                              STANDBY
         8          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8a.dbf                              STANDBY
         8          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8b.dbf                              STANDBY
         9          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9a.dbf                              STANDBY
         9          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9b.dbf                              STANDBY
        10          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10a.dbf                             STANDBY
        10          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10b.dbf                             STANDBY
        11          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11a.dbf                             STANDBY
        11          1          0 NO  UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11b.dbf                             STANDBY
======================================================================================================================
Check the recovery progress:

START_TIME          TYPE                 ITEM                             UNITS                     SOFAR      TOTAL TIMESTAMP
------------------- -------------------- -------------------------------- -------------------- ---------- ---------- -------------------
15-11-2013 17:11:58 Media Recovery       Log Files                        Files                      7571          0
15-11-2013 17:11:58 Media Recovery       Active Apply Rate                KB/sec                     8118          0
15-11-2013 17:11:58 Media Recovery       Average Apply Rate               KB/sec                      110          0
15-11-2013 17:11:58 Media Recovery       Redo Applied                     Megabytes                620510          0
15-11-2013 17:11:58 Media Recovery       Last Applied Redo                SCN+Time              923136452          0 21-01-2014 12:00:08
15-11-2013 17:11:58 Media Recovery       Active Time                      Seconds                   80785          0
15-11-2013 17:11:58 Media Recovery       Apply Time per Log               Seconds                       8          0
15-11-2013 17:11:58 Media Recovery       Checkpoint Time per Log          Seconds                       1          0
15-11-2013 17:11:58 Media Recovery       Elapsed Time                     Seconds                 5767807          0
======================================================================================================================
Data guard broker information, if relevant:

DG broker
--------------------
DISABLED
======================================================================================================================

How to use the DUPLICATE ... FOR STANDBY command to create a physical standby database

run {
allocate auxiliary channel t1 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
allocate auxiliary channel t2 type sbt PARMS="BLKSIZE=1048576" maxpiecesize 32G maxopenfiles 64;
send 'NB_ORA_POLICY=mypolicy,NB_ORA_CLIENT=myclient-bkp.mydomain,NB_ORA_SCHED=my_schedule';
allocate auxiliary channel aux1 device type disk;
set until logseq=l2345 thread=1;
duplicate target database for standby dorecover;
release channel t1;
release channel t2;
}

Note that the parameters db_file_name_convert and log_file_name_convert must be set in order to use this simplified syntax.
If not, you must specify file location for all the involved files and redo log members.

How to use ssh to a Solaris 8 box - overcoming file size limitation

When transferring files to a Solaris 8 server, I have had some difficulties with files larger than 2 GB. The ssh process will simply abort after it has reached the limit.
To work around this problem, use the following syntax instead:

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# NOTE: if you are transfering dump files to a Solaris 8 box,
# you need to tar and pipe the files to the receiving server.
# Make sure you cd to the directory first, before attempting
# to tar and ssh them.
# Vegard K, 25.02.2010
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/usr/bin/bash

cd /oracle/datapump/

tar cEf - dmpfile_01.dmp | ssh prodserver1 "cd /oracle/backup/datapump; tar xf -"

exit $?

How to use RMANs RESTORE...VALIDATE command

The RESTORE ... VALIDATE and VALIDATE BACKUPSET commands test whether you can restore from your backups:

RESTORE CONTROLFILE VALIDATE;
RESTORE TABLESPACE SYSTEM VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

This example validates the restore of backup sets 56 and 57:

VALIDATE BACKUPSET 56,57;

Same thing, but for individual datafiles:

RESTORE DATAFILE 4,5,6 VALIDATE;

In addition to listing the files needed for restore and recovery, the RESTORE ... VALIDATE HEADER command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository:

RESTORE DATABASE VALIDATE HEADER;

How to use RMANs RESTORE PREVIEW command

The RESTORE PRIVIEWS: identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry
out a given restore operation, based on the information in the RMAN repository.

These commands are brilliant for planning restore and recovery operations, for example

ensuring that all required backups are available

identifying RMAN backups you'd like to avoid or use

RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;
RESTORE DATAFILE 3 PREVIEW;
RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
RESTORE DATABASE UNTIL TIME "TO_DATE('18.11.2014 18:00:00','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

Use the RESTORE... PREVIEW SUMMARY option to suppress much of the detail about specific files used and
affected by the restore process:

RESTORE DATABASE PREVIEW SUMMARY;
RESTORE DATABASE UNTIL TIME "TO_DATE('31-08-2010 18:00:00', 'DD-MM-YYYY HH24:MI:SS')" PREVIEW;
RESTORE DATABASE UNTIL TIME 'sysdate-3' PREVIEW SUMMARY;
RESTORE DATABASE UNTIL TIME 'sysdate-1/24' preview summary; -->; one hour ago
RESTORE TABLESPACE users PREVIEW SUMMARY;
RESTORE DATAFILE 3 PREVIEW SUMMARY;
RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW SUMMARY;
-- For pluggable databases, add the keyword "pluggable" to the very same statement
RESTORE PLUGGABLE DATABASE PDB2 until time "TO_DATE('31-08-2010 18:00:00', 'DD-MM-YYYY HH24:MI:SS')" PREVIEW;

To avoid the following error stack:

Starting restore at 02.12.2014 16:51:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/02/2014 16:51:25
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile n

you need to allocate the appropriate channels for maintenance first, such as

allocate channel for maintenance type 'sbt_tape';

Output example from a preview started 06.05.2015 14:18:12, checking to see what would be needed if I took the database back approximately 1,5 hours:

RMAN> restore database until time "TO_DATE('06.05.2015 12:52:00','DD.MM.YYYY HH24:MI:SS')" preview summary;

Starting restore at 06.05.2015 14:18:12

using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
using channel ORA_DISK_2


List of Backups
===============

Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---

373688959 B  0  A SBT_TAPE    30.04.2015 17:42:55 1       1       NO         20150430173000.SOLP2
373688963 B  0  A SBT_TAPE    30.04.2015 17:43:06 1       1       NO         20150430173000.SOLP2
373688965 B  0  A SBT_TAPE    30.04.2015 17:43:21 1       1       NO         20150430173000.SOLP2
373688962 B  0  A SBT_TAPE    30.04.2015 17:43:03 1       1       NO         20150430173000.SOLP2
373688958 B  0  A SBT_TAPE    30.04.2015 17:42:26 1       1       NO         20150430173000.SOLP2
373688961 B  0  A SBT_TAPE    30.04.2015 17:43:02 1       1       NO         20150430173000.SOLP2
373688956 B  0  A SBT_TAPE    30.04.2015 17:42:22 1       1       NO         20150430173000.SOLP2
373688957 B  0  A SBT_TAPE    30.04.2015 17:42:24 1       1       NO         20150430173000.SOLP2
373688960 B  0  A SBT_TAPE    30.04.2015 17:42:58 1       1       NO         20150430173000.SOLP2
373927006 B  0  A SBT_TAPE    06.05.2015 12:49:58 1       1       NO         20150506123352.SOLP2
373688966 B  0  A SBT_TAPE    30.04.2015 17:45:12 1       1       NO         20150430173000.SOLP2
373688964 B  0  A SBT_TAPE    30.04.2015 17:43:12 1       1       NO         20150430173000.SOLP2

List of Backups

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

Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---

373689024 B  A  A SBT_TAPE    30.04.2015 17:45:28 1       1       NO         TAG20150430T174527
373701547 B  A  A SBT_TAPE    30.04.2015 21:40:29 1       1       NO         TAG20150430T214020
373708776 B  A  A SBT_TAPE    30.04.2015 23:40:24 1       1       NO         TAG20150430T234018
373715185 B  A  A SBT_TAPE    01.05.2015 03:40:28 1       1       NO         TAG20150501T034020
373718630 B  A  A SBT_TAPE    01.05.2015 06:40:49 1       1       NO         TAG20150501T064020
373747390 B  A  A SBT_TAPE    01.05.2015 23:40:33 1       1       NO         TAG20150501T234022
373755172 B  A  A SBT_TAPE    02.05.2015 03:40:31 1       1       NO         TAG20150502T034022
373758977 B  A  A SBT_TAPE    02.05.2015 06:40:41 1       1       NO         TAG20150502T064016
373763033 B  A  A SBT_TAPE    02.05.2015 09:40:27 1       1       NO         TAG20150502T094020
373772933 B  A  A SBT_TAPE    02.05.2015 12:40:20 1       1       NO         TAG20150502T124018
373775169 B  A  A SBT_TAPE    02.05.2015 14:40:17 1       1       NO         TAG20150502T144016
373818387 B  A  A SBT_TAPE    04.05.2015 01:01:30 1       1       NO         TAG20150502T164016
373826167 B  A  A SBT_TAPE    04.05.2015 03:41:13 1       1       NO         TAG20150504T034021
373826168 B  A  A SBT_TAPE    04.05.2015 03:41:33 1       1       NO         TAG20150504T034021
373830219 B  A  A SBT_TAPE    04.05.2015 06:41:12 1       1       NO         TAG20150504T064022
373835638 B  A  A SBT_TAPE    04.05.2015 09:40:22 1       1       NO         TAG20150504T094018
373847467 B  A  A SBT_TAPE    04.05.2015 12:40:35 1       1       NO         TAG20150504T124024
373889376 B  A  A SBT_TAPE    05.05.2015 14:40:43 1       1       NO         TAG20150505T144022
373889377 B  A  A SBT_TAPE    05.05.2015 14:40:58 1       1       NO         TAG20150505T144022
373889378 B  A  A SBT_TAPE    05.05.2015 14:41:26 1       1       NO         TAG20150505T144022
373889379 B  A  A SBT_TAPE    05.05.2015 14:42:03 1       1       NO         TAG20150505T144022
373889380 B  A  A SBT_TAPE    05.05.2015 14:42:20 1       1       NO         TAG20150505T144022
373889381 B  A  A SBT_TAPE    05.05.2015 14:42:37 1       1       NO         TAG20150505T144022
373924623 B  A  A SBT_TAPE    06.05.2015 12:32:13 1       1       NO         TAG20150506T123133
373924624 B  A  A SBT_TAPE    06.05.2015 12:32:45 1       1       NO         TAG20150506T123133
373924625 B  A  A SBT_TAPE    06.05.2015 12:33:02 1       1       NO         TAG20150506T123133
373927099 B  A  A SBT_TAPE    06.05.2015 13:01:48 1       1       NO         TAG20150506T130147
373927100 B  A  A SBT_TAPE    06.05.2015 13:01:49 1       1       NO         TAG20150506T130147

validation succeeded for backup piece
Media recovery start SCN is 169070460360
Recovery must be done beyond SCN 169092024179 to clear datafile fuzziness
validation succeeded for backup piece
Finished restore at 06.05.2015 14:18:16

How to use the DBMS_FILE_TRANSFER.PUT_FILE procedure

BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => 'myfile.txt',
   destination_directory_object => 'REMOTE_DPDUMP',
   destination_file_name        => 'myfile.txt',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/

Used in a script:

export DMP_NAME=`echo $1 | tr '[a-z]' '[A-Z]'`
export DPDIR=`echo $4 | tr '[a-z]' '[A-Z]'`

#####################################################
# Transfer to remote server using DBMS_FILE_TRANSFER
#####################################################

cat << EoF > ${DBA_ADMIN}/sql/copy_file.sql
set trimspool on
spool copy_file.log
Prompt Transferring Dumpfiles;
define file_name=&1


BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => '&file_name',
   destination_directory_object => '${DPDIR}',
   destination_file_name        => '&file_name',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/
exit
EoF

for dmpfile in $(ls /oracle/datapump/${DMP_NAME}_*.dmp); do
 file_name=`echo $dmpfile | cut -d / -f 7`

sqlplus -s / as sysdba @${DBA_ADMIN}/sql/copy_file.sql ${file_name} > ${BATCHDIR}/file_name.log 2> ${BATCHDIR}/file_name.err &
done
wait

rm -f  ${DBA_ADMIN}/sql/copy_file.sql

How to use RMANs LIST command to find individual archivelogs or a sequence of archivelogs

To list an individual backup, any of the three alternatives bellow would be valid query using rman:

RMAN> list backup of archivelog logseq=120316;
RMAN> list backup of archivelog logseq 120316;
RMAN> list backup of archivelog sequence 120316;

To view backups of archivelog between two sequences:

RMAN> list backup of archivelog sequence between 120316 and 120317;
RMAN> list backup of archivelog from logseq 412593 until logseq 412656;

Use the SUMMARY directive to view only the backupsets affected:

RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary;
RMAN> list backup of archivelog sequence between 120316 and 120317 summary;

To view backups completed before a specific date:

RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list archivelog all completed before 'sysdate-2';:

To list archivelogs recognized by the controlfile between two sequnce numbers:

RMAN> list archivelog sequence between 110880 and 110881;

To list archivelogs recognized by the controlfile up until yesterday:

RMAN> list archivelog until time 'sysdate-1';

To list archivelogs recognized by the controlfile from two days ago, up until yesterday:

RMAN> list archivelog from time 'sysdate-2' until time 'sysdate-1';

To limit the list of archivelogs to the ones taken on a specific date, in this case, the last of January 2022:

RMAN> list archivelog from time '31.01.2022' until time '01.02.2022';

If you remove the "until time" clause, RMAN will list all the archivelogs from 31.01.2022 up until the latest ones:

RMAN> list archivelog from time '31.01.2022';

Remember, the value of sysdate is the time right now, sysdate-1 is 24hrs ago, sysdate-2 is 48 hours ago, etc.

 select sysdate, sysdate-1, sysdate-2 from dual;

SYSDATE             SYSDATE-1           SYSDATE-2
------------------- ------------------- -------------------
03.02.2022 12:24:12 02.02.2022 12:24:12 01.02.2022 12:24:12