Validating RMAN Backup For Restore

Backup is only good if it can be used to restore.

Lately, I have been performing a lot of RMAN backup and validation.

In summary:

Restore validate completed in 0:24:17 (h:m:s) 
comprising of 39 ARCH, 1 LEVEL0, 3 LEVEL1, and 2 TAG20220302T121110 (control files backup).

--- The only reason I am providing host info is because grep -A does not work!
Host: AIX dbhost01 1 7 00C7DE504B00

--- RMAN restore script:
restore_validate.rman
spool log to restore_validate.log
set echo on
connect target;
show all;
restore spfile validate;
restore controlfile validate;
restore database until time "SYSDATE" check logical validate;
restore archivelog from time "SYSDATE-1" check logical validate;
report schema;
exit

--- RMAN configuration:
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 2 15:59:34 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BANANA (DBID=2937483440)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name BANANA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/home/oracle/idpa_ddbea.config)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/11.2.0/11.2.0.4/dbs/snapcf_BANANA.f'; # default

RMAN>

--- Run RMAN restore_validate:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1 &
[1] 2359590

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ jobs
[1]+  Running                 nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1 &

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $
[1]+  Done                    nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1

--- Check policy:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -i "policy" restore_validate.log
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

--- Check restore timing:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep "restore at" restore_validate.log
Starting restore at 2022-MAR-02 14:57:58
Finished restore at 2022-MAR-02 14:58:01
Starting restore at 2022-MAR-02 14:58:01
Finished restore at 2022-MAR-02 14:58:04
Starting restore at 2022-MAR-02 14:58:04
Finished restore at 2022-MAR-02 15:21:11
Starting restore at 2022-MAR-02 15:21:11
Finished restore at 2022-MAR-02 15:22:15

--- Check number of backup piece:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c "piece handle" restore_validate.log
45

--- Backup tag=LEVEL0, tag=LEVEL1, tag=ARCH
--- Check number of backup piece type:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ awk -F 'tag=' '{print $2}' restore_validate.log|sort|uniq -c
 275
  39 ARCH
   1 LEVEL0
   3 LEVEL1
   2 TAG20220302T121110

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c TAG20220302T121110 restore_validate.log
2

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'LEVEL0$' restore_validate.log
1

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'LEVEL1$' restore_validate.log
3

oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'ARCH$' restore_validate.log
39

--- If not using end with ('ARCH$'), will have incorrect results.
grep 'ARCH' restore_validate.log|head
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

--- This is a replacement for grep -A which is not available for AIX.
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ awk 'c-->0;$0~s{if(b)for(c=b+1;c>1;c--)print r[(NR-c+1)%b];print;c=a}b{r[NR%b]=$0}' b=0 a=30 s="schema for database with db_unique_name" restore_validate.log

Report of database schema for database with db_unique_name BANANA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    2565     SYSTEM               ***     /oradata/BANANA/datafile/system_01.dbf
2    4667     SYSAUX               ***     /oradata/BANANA/datafile/sysaux_01.dbf
3    1300     UNDOTBS1             ***     /oradata/BANANA/datafile/undotbs1_01.dbf
4    50       EMSPROD_TS           ***     /oradata/BANANA/datafile/emsprod_ts_01.dbf
5    1650     MODPROD_TS           ***     /oradata/BANANA/datafile/modprod_ts_01.dbf
6    2039     AVAIL                ***     /oradata/BANANA/datafile/avail_01.dbf
7    32767    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_05.dbf
8    2548     AUDIT_TBS            ***     /oradata/BANANA/datafile/audit_tbs_01.dbf
9    512      USERS                ***     /oradata/BANANA/datafile/users_01.dbf
10   30720    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_04.dbf
11   30720    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_03.dbf
12   32767    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_02.dbf
13   30720    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_01.dbf
14   25536    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_06.dbf
15   25472    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_07.dbf
16   25920    PROD001_TS           ***     /oradata/BANANA/datafile/PROD001_ts_08.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    4807     TEMP                 30720       /oradata/BANANA/datafile/temp_01.db

Recovery Manager complete.
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $

Advertisement

COMPLICATED COMMINGLED DATABASE ENVIRONMENT

I have been reviewing RMAN RAC backup for environment having a total of 15 non-production and production databases on the same host excluding APX and MGMTDB.

That’s not a big deal, as I have once had to managed 28 databases residing on the same host, right?

I am just too lazy and too tedious to change RMAN configuration one database at a time.

Luckily, there is a convention where non-production instances ends with T1 and production instances ends with P1.

This allows me to make the same changes to non-production and production in 2 steps.

Goal is to configure RMAN PARALLELISM 2 for NON-PROD and PARALLELISM 4 for PROD and consistent RECOVERY WINDOW OF 14 DAYS.

### Current configuration is inconsistent across databases:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

====================
### NON-PROD: 
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort
DB01T1
DB02T1
DB03T1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

====================
### PROD:
====================

--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
show all;
exit

--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort
DB01P1
DB02P1
DB03P1

--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;

HOW TO LOAD BALANCE RMAN RAC DATABASE BACKUP

First, I will share the incorrect method since it is hard coded.

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';

The goal is to configure RMAN backup with parallel 4 and load balance.

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT 'sys/***@DB_UNIQUE_NAME';

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 10 17:24:15 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB_NAME (DBID=453022715)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB_UNIQUE_NAME are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backups/DB_NAME/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT '*';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DB_NAME_DATA/DB_UNIQUE_NAME/controlfile/snapcf_DB_NAME.f';

RMAN>

It’s that easy. Changing parallelism will automatically load balance across all nodes.

Here is an example where parallelism is configured and backup is not load balance.

All the channels are allocated to node1.

[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201010300_Sat.log

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK

[oracle@host01 log]$

Here is the correct way and let the database determine the node.

[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201101400_Mon.log

channel ORA_DISK_1: SID=199 instance=HAWK2 device type=DISK
channel ORA_DISK_2: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1139 instance=HAWK2 device type=DISK

channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK

channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK

[oracle@host01 log]$

Review RMAN Backup for All Databases On Host

There are 15+ databases on host.

Here’s a nice and easy method to run any SQL for all databases.

[oracle@xxxxxxxxxx dinh]$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;
Database check DEBUGX1
ORACLE_SID = [xxxxxxxxxx] ? The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
DEBUGX    DEBUGX                         PRIMARY


DATAFILE_CT
-----------
         66


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-20 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          902          691
2021-DEC-20 COMPLETED                          1 DB INCR       DISK                     1              1       17,563           41
2021-DEC-21 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          970          730
2021-DEC-21 COMPLETED                          1 DB INCR       DISK                     1              1       17,527           36
2021-DEC-22 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          977          732
2021-DEC-22 COMPLETED                          1 DB INCR       DISK                     1              1       17,537           38
2021-DEC-23 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          934          719
2021-DEC-23 COMPLETED                          1 DB INCR       DISK                     1              1       17,523           38
2021-DEC-24 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          937          720
2021-DEC-24 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-25 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,024          746
2021-DEC-25 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-26 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,040          750
2021-DEC-26 COMPLETED                          9 DB INCR       DISK                     1              1       20,617        3,515
2021-DEC-27 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          906          692
2021-DEC-27 COMPLETED                          2 DB INCR       DISK                     1              1       17,528           39
2021-DEC-28 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          967          729
2021-DEC-28 COMPLETED                          1 DB INCR       DISK                     1              1       17,544           39
2021-DEC-29 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          988          736
2021-DEC-29 COMPLETED                          1 DB INCR       DISK                     1              1       17,546           38
2021-DEC-30 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          921          716
2021-DEC-30 COMPLETED                          1 DB INCR       DISK                     1              1       17,578           48
2021-DEC-31 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          945          723
2021-DEC-31 COMPLETED                          1 DB INCR       DISK                     1              1       17,517           36
2022-JAN-01 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,016          744
2022-JAN-01 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           38
2022-JAN-02 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,009          742
2022-JAN-02 COMPLETED                          9 DB INCR       DISK                     1              1       20,627        3,514
2022-JAN-03 COMPLETED                          8 ARCHIVELOG    DISK                    22             22          903          691
2022-JAN-03 COMPLETED                          1 DB INCR       DISK                     1              1       17,567           42
2022-JAN-04 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          958          727
2022-JAN-04 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           36
2022-JAN-05 COMPLETED                          5 ARCHIVELOG    DISK                    14             14          511          421
2022-JAN-05 COMPLETED                          1 DB INCR       DISK                     1              1       17,511           37
                                                                                                         ------------ ------------
sum                                                                                                           320,167       19,616

34 rows selected.

Database check RHPNEWDB1
ORACLE_SID = [XXXXXXXX] ? ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
RHPNEWDB  RHPNEWDB_XXXXXXX               PRIMARY


DATAFILE_CT
-----------
         33


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-26 COMPLETED WITH WARNINGS           17 ARCHIVELOG    DISK                     5              5       18,692        6,516
2021-DEC-26 COMPLETED WITH WARNINGS          414 DB INCR       DISK                     1              1    1,183,591      182,525
2021-DEC-27 COMPLETED WITH WARNINGS           59 ARCHIVELOG    DISK                    17             17       67,941       23,384
2021-DEC-27 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,048        3,537
2021-DEC-28 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,189       23,623
2021-DEC-28 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,403        3,826
2021-DEC-29 COMPLETED WITH WARNINGS           65 ARCHIVELOG    DISK                    22             22       68,835       23,807
2021-DEC-29 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,801        3,934
2021-DEC-30 COMPLETED WITH WARNINGS           61 ARCHIVELOG    DISK                    22             22       68,416       23,680
2021-DEC-30 COMPLETED WITH WARNINGS           11 DB INCR       DISK                     1              1       22,870        4,739
2021-DEC-31 COMPLETED WITH WARNINGS           58 ARCHIVELOG    DISK                    22             22       64,633       22,392
2021-DEC-31 COMPLETED WITH WARNINGS           10 DB INCR       DISK                     1              1       20,545        4,125
2022-JAN-01 COMPLETED WITH WARNINGS           57 ARCHIVELOG    DISK                    22             22       61,385       21,477
2022-JAN-01 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       17,463        3,689
2022-JAN-02 COMPLETED WITH WARNINGS           52 ARCHIVELOG    DISK                    21             21       55,319       19,407
2022-JAN-02 COMPLETED WITH WARNINGS          419 DB INCR       DISK                     1              1    1,193,071      185,202
2022-JAN-03 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    17             17       68,181       23,433
2022-JAN-03 COMPLETED WITH WARNINGS           12 DB INCR       DISK                     1              1       23,149        5,028
2022-JAN-04 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,714       23,797
2022-JAN-04 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,222        3,418
2022-JAN-05 COMPLETED WITH WARNINGS           38 ARCHIVELOG    DISK                    16             16       41,959       14,496
                                                                                                         ------------ ------------
sum                                                                                                         3,188,427      626,035

21 rows selected.

[oracle@xxxxxxxxxx dinh]$

----- COMPLETED WITH WARNINGS
Check RMAN log file to find
RMAN-08120: warning: archived log not deleted, not yet applied by standby

for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;

--- backup_review.sql
col input_mb  format 999,999,999
col output_mb format 999,999,999
col output_type for a12
col status for a25
set lines 200 pages 5000
break on report
compute SUM of input_mb on report
compute SUM of output_mb on report
break on report
alter session set nls_date_format = 'YYYY-MON-DD';
set echo on
select name, db_unique_name, database_role from v$database;
select count(*) datafile_ct from v$datafile;
select
TRUNC(start_time) start_date,
status,
round(sum(elapsed_seconds)/60) minutes,
input_type,
output_device_type output_type,
count(input_type) input_type_ct,
count(output_device_type) output_type_ct,
round(sum(input_bytes)/1024/1024) input_mb,
round(sum(output_bytes)/1024/1024) output_mb
from V$RMAN_BACKUP_JOB_DETAILS
where TRUNC(start_time)>=TRUNC(sysdate)-16
group by TRUNC(start_time), input_type, output_device_type, status
order by 1 asc, input_type asc, 2
;
/* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
exit

How To Change RMAN Config For Standby DB

Here is the typical error when changing RMAN configuration for standby database.

[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> configure retention policy to recovery window of 7 days;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

There are suggestions found from MOS. Yuck!

Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)

Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)

Thanks to teammate for the easy method.

[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
HAWK      PHYSICAL STANDBY

SQL> select * from v$rman_configuration;

no rows selected

SQL> set serveroutput on
SQL> !vi t.sql

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 1

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME
---------- -----------------------------------------------------------------
VALUE                                                  CON_ID
-------------------------------------------------- ----------
         1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS                                0


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg2 ~]$


[oracle@ol7-112-dg2 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$

IMPORTANT: Delete existing configuration before updating with another.

Here is a demonstration of what happens when configuration is not deleted.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0

SQL> @t.sql
SQL> DECLARE
  2     x NUMBER;
  3    BEGIN
  4      x := dbms_backup_restore.setconfig('RETENTION POLICY','TO REDUNDANCY 1');
  5      dbms_output.put_line('setconfig returned ' || x);
  6    END;
  7  /
setconfig returned 2

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         1 RETENTION POLICY     TO RECOVERY WINDOW OF 7 DAYS                                0
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>

--------------------------------------------------

SQL> exec DBMS_BACKUP_RESTORE.DELETECONFIG(1);

PL/SQL procedure successfully completed.

SQL> select * from v$rman_configuration;

     CONF# NAME                 VALUE                                                  CON_ID
---------- -------------------- -------------------------------------------------- ----------
         2 RETENTION POLICY     TO REDUNDANCY 1                                             0

SQL>

--------------------------------------------------

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default

RMAN>

Find Tablespace Info Using RMAN report schema

A quick and dirty post. This also works for PDBs.

[oracle@ol7-112-dg1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1040     SYSTEM               YES     /u01/oradata/hawk/system01.dbf
2    890      SYSAUX               NO      /u01/oradata/hawk/sysaux01.dbf
3    1025     UNDOTBS1             YES     /u01/oradata/hawk/undotbs01.dbf
4    10       USERS                NO      /u01/oradata/hawk/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    37       TEMP                 32767       /u01/oradata/hawk/temp01.dbf

RMAN> exit


Recovery Manager complete.

[oracle@ol7-112-dg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:44 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter tablespace users add datafile size 1m;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@ol7-112-dg1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:41:23 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3331620895)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1040     SYSTEM               YES     /u01/oradata/hawk/system01.dbf
2    890      SYSAUX               NO      /u01/oradata/hawk/sysaux01.dbf
3    1025     UNDOTBS1             YES     /u01/oradata/hawk/undotbs01.dbf
4    10       USERS                NO      /u01/oradata/hawk/users01.dbf
5    1        USERS                NO      /u01/oradata/HAWK/datafile/o1_mf_users_jldgmgxd_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    37       TEMP                 32767       /u01/oradata/hawk/temp01.dbf

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

RMAN BACKUP RECOVERY AREA

The current backup solution is Disk-To-Disk-To-Tape (D2D2T).

Here are the backups scheduled from cron.

$ crontab -l|grep backup
30 13 * * 0 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level0
30 13 * * 1,2,3,4,5,6 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level1
50 * * * * /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t arch
30 15 * * 6 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level0
10 15 * * 0,1,2,3,4,5 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level1

For small databases, it’s not a big deal; however, when the database size is terabytes it might not be an optimal solution to punish the database twice for each backup type.

Backing Up from the Fast Recovery Area (FRA) to Oracle Database Backup Cloud Service

The solution selected was – BACKUP DEVICE TYPE sbt RECOVERY AREA and here is a snippet of the shell script.

Please don’t ask me why a function is created for simple RMAN backup. It’s a loosing battle.

backup_database_inc() {
        RECOVERY_WINDOW=`echo "show retention policy;"| $RMAN_BIN target / | awk '/CONFIGURE RETENTION POLICY TO/ {print $(NF-1);}'`
        RMAN_CMDSTRING="
        $RMAN_CONNECT_TARGET_CMD
show all;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
set encryption off;
run {
    set command id to '$SCRIPT_NAME:LEVEL${BACKUP_LEVEL}:$BACKUP_TAG';
    crosscheck archivelog all;
    delete noprompt expired archivelog all;
    backup device type disk  tag '${BACKUP_TAG}'
    incremental level ${BACKUP_LEVEL}
    database
    plus archivelog not backed up 1 times;
    delete noprompt obsolete recovery window of 14 days device type disk;
}
set encryption on;
run {
    set command id to '$SCRIPT_NAME:ARCH:$BACKUP_TAG';
    backup device type sbt recovery area;
    delete noprompt archivelog all backed up 1 times to disk;
    delete noprompt backup device type sbt_tape completed before 'sysdate - 20';
}
exit;
"

Next, let’s review the backup from V$RMAN_BACKUP_JOB_DETAILS.

Notice INPUT_TYPE = RECVR AREA

$ sqlplus / as sysdba @ new_backup_review.sql 

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 19 19:28:30 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> select name, database_role from v$database
  2  ;

NAME      DATABASE_ROLE
--------- ----------------
XXXXXXX   PRIMARY

SQL> alter session set nls_date_format = 'YYYY-MON-DD';

Session altered.

SQL> select
  2  TRUNC(start_time) start_date,
  3  input_type,
  4  output_device_type output_type,
  5  status,
  6  round(sum(elapsed_seconds)/60) minutes,
  7  round(sum(input_bytes)/1024/1024) input_mb,
  8  round(sum(output_bytes)/1024/1024) output_mb
  9  from V$RMAN_BACKUP_JOB_DETAILS
 10  group by TRUNC(start_time), input_type, output_device_type, status
 11  order by start_date asc, input_type asc, 2
 12  ;

START_DATE  INPUT_TYPE    OUTPUT_TYPE       STATUS          MINUTES     INPUT_MB    OUTPUT_MB
----------- ------------- ----------------- ------------ ---------- ------------ ------------
2021-JUL-25 ARCHIVELOG    *                 COMPLETED             4        3,362          408
2021-JUL-26 ARCHIVELOG    *                 COMPLETED            13       11,214        1,375
2021-JUL-26 DB INCR       *                 COMPLETED             2        2,766          841
2021-JUL-26 DB INCR       SBT_TAPE          COMPLETED             1          316           30
2021-JUL-27 ARCHIVELOG    *                 COMPLETED            14       11,013        1,222
2021-JUL-27 DB INCR       *                 COMPLETED             2        3,375          953
2021-JUL-27 DB INCR       SBT_TAPE          COMPLETED             1          237           25
2021-JUL-28 ARCHIVELOG    *                 COMPLETED            15       10,827        1,118
2021-JUL-28 DB INCR       SBT_TAPE          COMPLETED             1          238           24
2021-JUL-28 DB INCR       *                 COMPLETED             2        3,011          874
2021-JUL-29 ARCHIVELOG    *                 COMPLETED            15       10,713        1,092
2021-JUL-29 DB INCR       *                 COMPLETED             2        2,901          841
2021-JUL-29 DB INCR       SBT_TAPE          COMPLETED             1          236           23
2021-JUL-30 ARCHIVELOG    *                 COMPLETED            15       10,601        1,011
2021-JUL-30 DB INCR       *                 COMPLETED             2        2,783          813
2021-JUL-30 DB INCR       SBT_TAPE          COMPLETED             1          227           21
2021-JUL-31 ARCHIVELOG    *                 COMPLETED            14       10,230          955
2021-JUL-31 DB INCR       SBT_TAPE          COMPLETED             3       26,311        3,828
2021-JUL-31 DB INCR       *                 COMPLETED             2        3,004          819
2021-AUG-01 ARCHIVELOG    *                 COMPLETED            14       10,937        1,197
2021-AUG-01 DB INCR       SBT_TAPE          COMPLETED             1          257           29
2021-AUG-01 DB INCR       *                 COMPLETED             6      105,712       16,038
2021-AUG-02 ARCHIVELOG    *                 COMPLETED            13       10,488          982
2021-AUG-02 DB INCR       *                 COMPLETED             2        2,257          632
2021-AUG-02 DB INCR       SBT_TAPE          COMPLETED             1          236           23
2021-AUG-03 ARCHIVELOG    *                 COMPLETED            14       10,675        1,074
2021-AUG-03 DB INCR       SBT_TAPE          COMPLETED             1          223           19
2021-AUG-03 DB INCR       *                 COMPLETED             2        2,750          796
2021-AUG-04 ARCHIVELOG    *                 COMPLETED            16       10,750        1,109
2021-AUG-04 DB INCR       *                 COMPLETED             2        2,741          775
2021-AUG-04 DB INCR       SBT_TAPE          COMPLETED             1          237           23
2021-AUG-05 ARCHIVELOG    *                 COMPLETED            17       10,493          980
2021-AUG-05 DB INCR       *                 COMPLETED             3        2,738          788
2021-AUG-05 DB INCR       SBT_TAPE          COMPLETED             1          222           19
2021-AUG-06 ARCHIVELOG    *                 COMPLETED            15       10,466          962
2021-AUG-06 DB INCR       SBT_TAPE          COMPLETED             1          222           19
2021-AUG-06 DB INCR       *                 COMPLETED             2        2,604          750
2021-AUG-07 ARCHIVELOG    *                 COMPLETED            15       10,915        1,195
2021-AUG-07 DB INCR       *                 COMPLETED             2        2,784          780
2021-AUG-07 DB INCR       SBT_TAPE          COMPLETED             1       27,377        4,201
2021-AUG-08 ARCHIVELOG    *                 COMPLETED            15       10,872        1,190
2021-AUG-08 DB INCR       SBT_TAPE          COMPLETED             1          252           27
2021-AUG-08 DB INCR       *                 COMPLETED             7      104,131       15,833
2021-AUG-09 ARCHIVELOG    *                 COMPLETED            13       10,472          967
2021-AUG-09 DB INCR       *                 COMPLETED             2        2,313          657
2021-AUG-09 DB INCR       SBT_TAPE          COMPLETED             1          236           22
2021-AUG-10 ARCHIVELOG    *                 COMPLETED            14       10,724        1,089
2021-AUG-10 DB INCR       *                 COMPLETED             2        2,755          786
2021-AUG-10 DB INCR       SBT_TAPE          COMPLETED             1          223           19
2021-AUG-11 ARCHIVELOG    *                 COMPLETED            16       10,753        1,094
2021-AUG-11 DB INCR       SBT_TAPE          COMPLETED             1          223           20
2021-AUG-11 DB INCR       *                 COMPLETED             2        2,813          806
2021-AUG-12 ARCHIVELOG    *                 COMPLETED            15       10,527          993
2021-AUG-12 DB INCR       *                 COMPLETED             2        2,755          794
2021-AUG-12 DB INCR       SBT_TAPE          COMPLETED             1          224           20
2021-AUG-13 ARCHIVELOG    *                 COMPLETED            15       10,541          997
2021-AUG-13 DB INCR       *                 COMPLETED             2        2,625          774
2021-AUG-13 DB INCR       SBT_TAPE          COMPLETED             1          236           24
2021-AUG-14 ARCHIVELOG    *                 COMPLETED            15       10,799        1,131
2021-AUG-14 DB INCR       SBT_TAPE          COMPLETED             1       25,945        3,837
2021-AUG-14 DB INCR       *                 COMPLETED             2        2,953          851
2021-AUG-15 ARCHIVELOG    *                 COMPLETED            14       10,832        1,151
2021-AUG-15 DB INCR       SBT_TAPE          COMPLETED             1          238           24
2021-AUG-15 DB INCR       *                 COMPLETED             6      104,284       15,951
2021-AUG-16 ARCHIVELOG    *                 COMPLETED            14       10,396          914
2021-AUG-16 DB INCR       *                 COMPLETED             2        2,362          683
2021-AUG-16 DB INCR       SBT_TAPE          COMPLETED             1          224           20
2021-AUG-17 ARCHIVELOG    *                 COMPLETED            19       10,678        1,059
2021-AUG-17 DB INCR       *                 COMPLETED             2        2,577          721
2021-AUG-17 DB INCR       SBT_TAPE          COMPLETED             1          241           25
2021-AUG-17 RECVR AREA    *                 COMPLETED            63       50,162       49,461
2021-AUG-18 ARCHIVELOG    *                 COMPLETED             3        1,981           98
2021-AUG-18 ARCHIVELOG    DISK              COMPLETED             0          205           17
2021-AUG-18 RECVR AREA                      COMPLETED             0            0            0
2021-AUG-18 RECVR AREA    *                 COMPLETED           125      146,534       76,160
2021-AUG-18 RECVR AREA    SBT_TAPE          COMPLETED            14        2,462        1,530
2021-AUG-19 RECVR AREA    *                 COMPLETED            42       23,975        5,028

77 rows selected.

SQL> /* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

Validating RMAN Tape Backup

Lately, I have been validating a lot of database backups to tape.

The validation is made easy because channels are configured in RMAN.

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS 'SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/$ORACLE_SID/opc/libopc.so, ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/$ORACLE_SID/opc/opc$ORACLE_SID.ora)' CONNECT '*';

CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT '*';

Here is the generic RMAN script which has been used successfully for all databases so far.

RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS is for all databases (luckily)

[oracle@rac01 dinh]$ rman checksyntax @restore_validate.rman

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Aug 13 11:18:20 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN> set echo on
2> spool log to restore_validate.log
3> connect target;
4> show all;
5> restore spfile validate device type=SBT_TAPE;
6> restore controlfile validate device type=SBT_TAPE;
7> restore database until time "SYSDATE" validate device type=SBT_TAPE;
8> restore archivelog from time="SYSDATE-14" validate device type=SBT_TAPE;
9> exit
[oracle@rac01 dinh]$

The database size is 2.4 TB.
There are 1,495 backup pieces.
Restore validate completed in ~1 hr.

--- Verify retention policy.
[oracle@rac01 dinh]$ grep -i "policy" restore_validate.log
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
[oracle@rac01 dinh]$ 

--- Check restore timing.
[oracle@rac01 dinh]$ grep "restore at" restore_validate.log
Starting restore at 2021-AUG-13 11:20:04
Finished restore at 2021-AUG-13 11:20:21
Starting restore at 2021-AUG-13 11:20:21
Finished restore at 2021-AUG-13 11:20:35
Starting restore at 2021-AUG-13 11:20:36
Finished restore at 2021-AUG-13 11:34:01
Starting restore at 2021-AUG-13 11:34:02
Finished restore at 2021-AUG-13 12:13:51
[oracle@rac01 dinh]$

--- Still unclear why archived logs from DISK are scanned.
[oracle@rac01 dinh]$ grep "scanning archived log" restore_validate.log|grep ORA
channel ORA_SBT_TAPE_1: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68216.70749.1080469891
channel ORA_SBT_TAPE_4: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68217.64086.1080473423
channel ORA_SBT_TAPE_2: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68218.10749.1080473497
channel ORA_SBT_TAPE_3: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_2_seq_70946.42152.1080473423
channel ORA_SBT_TAPE_2: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_2_seq_70947.12624.1080473499
[oracle@rac01 dinh]$

--- Verify All backup pieces are from TAPE.
[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|grep -v TAPE

--- There are 1495 backup pieces - Needs Improvements.
[oracle@rac01 dinh]$ grep -c "piece handle" restore_validate.log
1495
[oracle@rac01 dinh]$

--- Examples of backup pieces.
[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|head
channel ORA_SBT_TAPE_1: piece handle=c-2010814236-20210813-16 tag=TAG20210813T103149
channel ORA_SBT_TAPE_1: piece handle=c-2010814236-20210813-16 tag=TAG20210813T103149
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_sa05a48k_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_iu05si7j_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_j105si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_sr05a496_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_j305si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_j205si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_s905a48k_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_j505si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
[oracle@rac01 dinh]$

[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|tail
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5206cvrd_1_1 tag=BKP_$ORACLE_SID1_202108130730
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5n06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_5e06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_5d06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_6106dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_5c06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5m06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_5v06dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_5l06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_5u06dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
[oracle@rac01 dinh]$

RMAN Backup Tuning – MAXOPENFILES

Currently, database backup is performed using RMAN from shell script.

The shell script has has 2,347 lines. Does anyone KISS anymore?

Anyhow, I recalled very early in my career when someone coined the phrased, “Doing It The Dinh’s Way”.

Basically, it’s not effective nor efficient to debug and troubleshoot shell script.

Client provided the following info and off I went.

Database backup changed from filesperset 4 to filesperset 1.

Rollback change and still facing performance issues.

Level 1 increment backup with filesperset = 4 still running > 24 hours – killed.

Can you try a few things not using the rman script in crontab, but using your own rman command?

Request was to disable block change tracking – DONE.

Database size is 272G with the following datafiles.

SQL> select file_id, file_name, round(bytes/1024/1024/1024) gb from dba_data_files order by 1 asc;

 FILE_ID FILE_NAME                                                          GB
-------- ------------------------------------------------------------ --------
       1 +DATA_XXXDWP01/XXXdwp01/datafile/system.256.964878417               1
       2 +DATA_XXXDWP01/XXXdwp01/datafile/sysaux.257.964878419               4
       3 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs1.258.964878421            21
       4 +DATA_XXXDWP01/XXXdwp01/datafile/gg_tbs.261.964878453               5
       5 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs2.262.964878457            19
       6 +DATA_XXXDWP01/XXXdwp01/datafile/users.264.965655143               31
       7 +DATA_XXXDWP01/XXXdwp01/datafile/users.267.1020339295              32
       8 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.268.1027263011           0
       9 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.269.1027263013           0
      10 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.270.1027263013           0
      11 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.271.1027263013           0
      12 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.272.1027263015           0
      13 +DATA_XXXDWP01/XXXdwp01/datafile/users.274.1046508647              32
      14 +DATA_XXXDWP01/XXXdwp01/datafile/users.275.1056870315              32
      15 +DATA_XXXDWP01/XXXdwp01/datafile/users.276.1066387693              32
      16 +DATA_XXXDWP01/XXXdwp01/datafile/users.277.1066387695              32
      17 +DATA_XXXDWP01/XXXdwp01/datafile/users.278.1066387697              32

17 rows selected.

SQL>

Backup completed in 3.25 hours using MAXPIECESIZE 2G MAXOPENFILES 1.

Believe further improvements by increasing MAXPIECESIZE to 4G; however, client wanted to leave as is.

Backupset size is 24G.

I believe performance may be related to Pure Storage Deduplication; however, there’s no more time nor different destination to test with.

MAXOPENFILES ≤ 4
Each buffer = 1MB, total buffer size for channel is up to 16MB

MAXOPENFILES = 1 => 16 buffers/file, 1 MB/buffer = 16 MB/file
Optimal for ASM or striped system

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 16 11:08:18 2021 Version 19.8.0.0.0

RMAN> connect target;
2> set echo on
3> spool log to backup.log
4> show all;
5> run {
6> allocate channel c1 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
7> allocate channel c2 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
8> allocate channel c3 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
9> allocate channel c4 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
10> backup incremental level 1 filesperset 1 database;
11> }
12> exit

The Horror Of Restore Validate For SBT_TAPE Backups

For the database environment, there are database backups to disk and tape.

I wanted to validate tape backup by using validate preview summary device type=SBT_TAPE.

I hit a brick wall as the restore was reading backups from both DISK and SBT_TAPE.

Here is the command used:

restore database until time "TRUNC(sysdate)" validate preview summary device type=SBT_TAPE;

Oracle support came through with the following info:

Reading from Disk because of the preview command.

The command is now replaced with:

restore database   until time "TRUNC(sysdate)" validate device type=SBT_TAPE;
restore archivelog until time "TRUNC(sysdate)" validate device type=SBT_TAPE;

Another option is to use RETENTION POLICY.

RMAN> show RETENTION POLICY;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> restore archivelog from time='SYSDATE-7' validate device type=SBT_TAPE;

Verify missing archivelog backup.

--- list backup of archivelog sequence 89961 thread 2;

RMAN-06025: no backup of archived log for thread 2 with sequence 3712 and starting SCN of 11284417423066 found to restore

How to Check/Validate that RMAN Backups Are Good (Doc ID 466221.1)

--- Report of backups that are used to do the restore and recover:
restore database preview;

--- Check the backup pieces are good:
restore database validate;

Works beautifully and learned something new today.