Minimalistic Oracle

Tuesday, January 21, 2014

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

ksh: script that will send alert when flash recovery are is filling up

#!/usr/bin/ksh
# Vegard Kasa
# 11.07.2013
#
# Alert when FRA is filling up...
#############################################################################################################
export NOW=`date +\%d.\%m.\%y`
export NLS_DATE_FORMAT='DD.MM.YYYY'
####################################################################
# Set the script base to either $DBA_ADMIN in case of original build
# or $DBA_ADMIN_HOME in case of newer build.
####################################################################
if [ -n "${DBA_ADMIN}" ]; then
 ##############
 # Original build
 ##############
 SCRIPT_BASE=$DBA_ADMIN
elif [ -n "${DBA_ADMIN_HOME}" ]; then
 #################
 # Another build
 # uses different
 # variable names
 #################
 SCRIPT_BASE=${DBA_ADMIN_HOME}
fi


cat << EoF > ${SCRIPT_BASE}/sql/get_sum_fra.sql
set termout   off
set trimspool on
set verify    off
set feedback  off
set echo      off
set linesize  200
set heading   off
set pagesize  0
spool ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log
SELECT    LTRIM(sum(PERCENT_SPACE_USED))
FROM      V\$FLASH_RECOVERY_AREA_USAGE;
exit
EoF
sqlplus -s / as sysdba @${SCRIPT_BASE}/sql/get_sum_fra.sql
##########################################################
# Send the output via e-mail to the designated receipients
##########################################################
PRC=`cat ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log`
if [ ${PRC} -gt 80 ]; then
 echo "Warning: In database ${ORACLE_SID}, the FRA is ${PRC} percent used!"  |mailx -r monitoring@yourdomain.com -s "FRA usage report for database ${ORACLE_SID}" dba@yourdomain.com
fi
exit

Monday, January 20, 2014

Template for RMAN script, including Netbackup directives

Executed as user oracle directly on the server:


connect target /
connect catalog rmancat/password@RMANCAT
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=my_policy_name,NB_ORA_CLIENT=my_client_name-bkp.mydomain.com,NB_ORA_SCHED=my_schedule_name';

rman commands here


release channel t1;
release channel t2;
}

to execute:

$ rman cmdfile='restore_archlog.cmd' log='restore_archlog.log'

How the Archived Redo Log Deletion Policy works

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Note that *only* logs in the flash recovery area can be deleted automatically by the database.

You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.

When the Archived Redo Log Deletion Policy Is ENABLED

You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.

For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.

You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

When the Archived Redo Log Deletion Policy Is DISABLED

In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.

The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.

Source: Oracle Documentation

How does Oracle manage disk space in the Flash Recovery Area?

Files in the recovery area are permanent or transient.

Permanent files are active files used by the database instance (like control files).
All files that are not permanent are transient.

In general, Oracle Database eventually deletes transient files after they become obsolete under the backup retention policy or have been backed up to tape.

Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion.
Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose.
Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape.
When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.

The following rules govern when files become eligible for deletion from the recovery area:
• Permanent files are never eligible for deletion.
• Files that are obsolete under the retention policy are eligible for deletion.
• Transient files that have been copied to tape are eligible for deletion.
• Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements. Consumers of logs can include RMAN, standby databases, Oracle Streams databases, and the Flashback Database feature.

The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy and archived log deletion policy.
To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.

Source: Oracle Documentation