Thinking Out Loud

March 2, 2022

Validating RMAN Backup For Restore

Filed under: awk_sed_grep,RMAN — mdinh @ 11:01 pm

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) $

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: