Zed DBA's Oracle Blog
RMAN Incremental Updating Backup & Demo Part 1
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Incremental Updating BackupAn incremental updating backup is a level 0 incremental backup which is recovered using level 1 incremental backups. This avoids the overhead of taking level 0 incremental backups, for example in a backup strategy where a level 0 is taken every Sunday and all other days level 1.
An simple example would be, an incremental level 0 backup is taken once and would be recovered daily. For example everyday an incremental level 1 backup is taken and then the incremental level 1 backup taken yesterday would be used to recover the incremental level 0 backup. Thus the incremental level 0 backup would fluctuate between 1 to 2 days behind current, i.e. at the point when the backup script is about to run, the incremental level 1 backup would be as of approximately 48 hours ago and when ran would be as of approximately 24 hours ago.
Incremental Updating Backup DemoWe take an incremental updating backup using my script 9_incremental_updated_backup.sh:
[oracle@dc1sbxdb001 demo]$ ./9_incremental_updated_backup.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, next we recover an incremental level 0 backup followed by taking an incremental level 1 backup, which we do three times as shown in the content of the 9_incremental_updated_backup.cmd file:
--------------------------------------- Step 2: Take Incremental Updated Backup --------------------------------------- Clearing all backups... Content of 9_incremental_updated_backup.cmd file: RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; HOST 'read Press Enter'; BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; HOST 'read Press Enter'; RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; HOST 'read Press Enter'; BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; HOST 'read Press Enter'; RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; HOST 'read Press Enter'; BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; Press Enter to continue
Next we recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’“:
Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/9_incremental_updated_backup.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 30 19:31:09 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; Starting recover at 30-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=152 device type=DISK no copy of datafile 1 found to recover no copy of datafile 2 found to recover no copy of datafile 3 found to recover no copy of datafile 4 found to recover Finished recover at 30-MAY-19 RMAN> HOST 'read Press Enter'; host command complete
As you can see RMAN states there is no copy of the datafiles found to recover. This is because we don’t yet have an incremental level 0 backup.
Next we take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; Starting backup at 30-MAY-19 using channel ORA_DISK_1 no parent backup or copy of datafile 1 found no parent backup or copy of datafile 2 found no parent backup or copy of datafile 3 found no parent backup or copy of datafile 4 found channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf tag=INC UPDATE RECID=5 STAMP=1009654288 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf tag=INC UPDATE RECID=6 STAMP=1009654291 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf tag=INC UPDATE RECID=7 STAMP=1009654293 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf tag=INC UPDATE RECID=8 STAMP=1009654296 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 30-MAY-19 Starting Control File and SPFILE Autobackup at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654296_gh08d93p_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-19 RMAN> HOST 'read Press Enter'; host command complete
As you can see RMAN still states there no parent backup or copy of the datafiles found, so instead takes an incremental level 0 backup (copy) for you.
Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:
RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; Starting recover at 30-MAY-19 using channel ORA_DISK_1 no copy of datafile 1 found to recover no copy of datafile 2 found to recover no copy of datafile 3 found to recover no copy of datafile 4 found to recover Finished recover at 30-MAY-19 RMAN> HOST 'read Press Enter'; host command complete
As you can see RMAN states there still no copy of the datafiles found to recover. This is not exactly true as an incremental level 0 backup was taken above, however there is no incremental level 1 backup to use to recover hence the message.
Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; Starting backup at 30-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 30-MAY-19 channel ORA_DISK_1: finished piece 1 at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 30-MAY-19 Starting Control File and SPFILE Autobackup at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654317_gh08dx4z_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-19 RMAN> HOST 'read Press Enter'; host command complete
As you can see RMAN now is aware of the incremental level 0 backup and thus takes an incremental level 1 backup as requested.
Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:
RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE'; Starting recover at 30-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile copies to recover recovering datafile copy file number=00001 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf recovering datafile copy file number=00002 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf recovering datafile copy file number=00003 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf recovering datafile copy file number=00004 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 30-MAY-19 Starting Control File and SPFILE Autobackup at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654322_gh08f35f_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-19 RMAN> HOST 'read Press Enter'; host command complete
As there is now an incremental level 1 backup that has the tag ‘INC UPDATE‘ it is used to recover the incremental level 0 backup to the point when the incremental level 1 backup was taken.
Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE; Starting backup at 30-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 30-MAY-19 channel ORA_DISK_1: finished piece 1 at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08ffh1_.bkp tag=INC UPDATE comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-MAY-19 Starting Control File and SPFILE Autobackup at 30-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654334_gh08fgqj_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-19 Recovery Manager complete. Press Enter to continue
As you can see RMAN takes incremental level 1 backup as requested, just as before and will continue to do so till there is no incremental level 0 backup.
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup 16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs 17-MAY-19 04.55.12.000000 PM Image Copy 20-MAY-19 05.13.44.000000 PM Incremental Level 0 22-MAY-19 12.09.17.000000 PM Incremental Level 1 22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT 24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative 30-MAY-19 07.32.24.000000 PM Incremental Updated Backup 9 rows selected. Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Reference
- 8.8 About RMAN Incremental Backups
- 9.8 Making and Updating RMAN Incremental Backups
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
To download all 2 in one zip: 9_incremental_updated_backup.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
RMAN Incremental Differential vs Cumulative & Demo
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Differential Incremental Backups (default)A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.
RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the blocks changed after the last level 0 backup are backed up. If no level 0 backup exist, then a level 0 backup is taken.
The diagram below shows how the level 1 differential incremental backups are just backing up from the last level 1:
A cumulative incremental backup, backs up all the blocks that have changed after the most recent incremental backup at level 0 only.
This reduces the work needed when recovering after a restore as only the desired level 1 is required to be applied instead of several level 1 in sequence. The trade off is as every level 1 backup needs to backup all the blocks changed from the last level 0, it is repeated the work done by the previous level 1, thus require more space and more time.
The diagram below shows how the level 1 cumulative incremental backup are backing up from the last level 0:
We take an cumulative incremental level 1 backup using my script 8_incremental_level_1_cumulative.sh:
[oracle@dc1sbxdb001 demo]$ ./8_incremental_level_1_cumulative.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, next we will take an differential backup twice, followed by cumulative incremental backup twice:
-------------------------------------------------
Step 2: Take Incremental Level 1 Cumlative Backup
-------------------------------------------------
Content of 8_incremental_level_1_cumulative.cmd file:
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;
Press Enter to continue
Next a differential incremental backup is taken:
Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/8_incremental_level_1_cumulative.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 24 18:27:49 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; Starting backup at 24-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=157 device type=DISK channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp tag=INCR LEVEL 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next another differential incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; Starting backup at 24-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp tag=INCR LEVEL 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next a cumulative incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM'; Starting backup at 24-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp tag=INCR LEVEL 1 CUM comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next another cumulative incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM'; Starting backup at 24-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp tag=INCR LEVEL 1 CUM comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next we list the backup within RMAN using ‘LIST BACKUP‘:
RMAN> HOST 'read Press Enter to LIST BACKUP'; host command complete RMAN> LIST BACKUP; List of Backup Sets =================== ... ... ... BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 25 Incr 1 38.23M DISK 00:00:02 24-MAY-19 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp List of Datafiles in backup set 25 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 26 Full 8.31M DISK 00:00:00 24-MAY-19 BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182756 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603541 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 27 Incr 1 64.00K DISK 00:00:03 24-MAY-19 BP Key: 27 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp List of Datafiles in backup set 27 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 28 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182801 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603567 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 29 Incr 1 38.23M DISK 00:00:01 24-MAY-19 BP Key: 29 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp List of Datafiles in backup set 29 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 30 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182804 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603592 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 31 Incr 1 38.23M DISK 00:00:01 24-MAY-19 BP Key: 31 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp List of Datafiles in backup set 31 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 32 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182807 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603617 Ckp time: 24-MAY-19 Recovery Manager complete. Press Enter to continue
Next we can see the first differential incremental backup backed up 39M of changed blocks, however the second differential incremental backup only backed up 72K as only backed up the blocks changed from the last level 1 backup. Where as the cumulative incremental on both the first and second backup, backed up 39M of changed blocks:
Files size on disk:
...
...
...
/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24:
total 115M
-rw-r-----. 1 oracle oinstall 39M May 24 18:27 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
-rw-r-----. 1 oracle oinstall 72K May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp
Press Enter to continue
Finally, we update the demo log table:
-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log
1 row created.
Commit complete.
WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative
8 rows selected.
Press Enter to exit shell script
[oracle@dc1sbxdb001 demo]$
Reference
- 8.8 About RMAN Incremental Backups
- 8.8.1.1 About Differential Incremental Backups
- 8.8.1.2 About Cumulative Incremental Backups
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
To download all 2 in one zip: 8_incremental_level_1_cumulative.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
RMAN Incremental & Demo Part 2 (Level 1)
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Incremental BackupAn incremental backup only backup up those data blocks that have changed since the last backup.
Types of Incremental BackupsThere are 2 types of Incremental Backups:
- Level 0 are a base for subsequent backups. Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included in an incremental strategy. Level 0 can be backup sets or image copies.
- Level 1 are subsequent backups of a level 0, backing up by default all blocks changed after the most recent level 0 or 1, known as differential incremental backup. More on different types of level 1 backups is discuss in detail here.
We take an incremental level 1 backup using my script 6_incremental_level_1.sh:
[oracle@dc1sbxdb001 demo]$ ./6_incremental_level_1.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next the incremental backup (Level 1) is taken:
---------------------------------------- Step 2: Take Incremental Level 1 Backup ---------------------------------------- Cotent of 6_incremental_level_1.cmd file: BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; HOST 'read Press Enter to LIST BACKUP'; LIST BACKUP; Press Enter to continue Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/6_incremental_level_1.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Wed May 22 12:08:27 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; 2> HOST 'read Press Enter to LIST BACKUP'; 3> LIST BACKUP; 4> Starting backup at 22-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=153 device type=DISK channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 22-MAY-19 channel ORA_DISK_1: finished piece 1 at 22-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp tag=INCR LEVEL 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 22-MAY-19 Starting Control File and SPFILE Autobackup at 22-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008936514_ggbcflon_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 22-MAY-19 host command complete
We use host within RMAN just to wait for input before moving on for demo purposes. Next we list the backup within RMAN using ‘LIST BACKUP‘:
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 498.93M DISK 00:00:08 16-MAY-19 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 3 77.83M DISK 00:00:01 16-MAY-19 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp List of Archived Logs in backup set 3 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 332298 16-MAY-19 354044 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4 Full 498.94M DISK 00:00:06 16-MAY-19 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 5 3.50K DISK 00:00:00 16-MAY-19 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp List of Archived Logs in backup set 5 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 354044 16-MAY-19 354066 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7 Full 8.31M DISK 00:00:00 17-MAY-19 BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190517T165453 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp SPFILE Included: Modification time: 17-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 458035 Ckp time: 17-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Incr 0 511.64M DISK 00:00:05 20-MAY-19 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp List of Datafiles in backup set 8 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Full 8.31M DISK 00:00:00 20-MAY-19 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190520T171324 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp SPFILE Included: Modification time: 20-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 571056 Ckp time: 20-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Incr 1 11.22M DISK 00:00:02 22-MAY-19 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 11 Full 8.31M DISK 00:00:00 22-MAY-19 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20190522T120834 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008936514_ggbcflon_.bkp SPFILE Included: Modification time: 22-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 575181 Ckp time: 22-MAY-19 Recovery Manager complete. Press Enter to continue
Next we look at the file size of the backup piece and we can see the level 1 was just 12M compared to the level 0 which was 512M:
Files size on disk: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16: total 1.1G -rw-r-----. 1 oracle oinstall 499M May 16 17:39 o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp -rw-r-----. 1 oracle oinstall 78M May 16 17:46 o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp -rw-r-----. 1 oracle oinstall 499M May 16 17:46 o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp -rw-r-----. 1 oracle oinstall 4.0K May 16 17:46 o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20: total 512M -rw-r-----. 1 oracle oinstall 512M May 20 17:13 o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22: total 12M -rw-r-----. 1 oracle oinstall 12M May 22 12:08 o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp Press Enter to continue
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup 16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs 17-MAY-19 04.55.12.000000 PM Image Copy 20-MAY-19 05.13.44.000000 PM Incremental Level 0 22-MAY-19 12.09.17.000000 PM Incremental Level 1 6 rows selected. Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Reference
- 8.8 About RMAN Incremental Backups
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
To download all 2 in one zip: 6_incremental_level_1.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
RMAN Incremental & Demo Part 1 (Level 0)
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Incremental BackupAn incremental backup only backup up those data blocks that have changed since the last backup.
Types of Incremental BackupsThere are 2 types of Incremental Backups:
- Level 0 are a base for subsequent backups. Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included in an incremental strategy. Level 0 can be backup sets or image copies.
- Level 1 are subsequent backups of a level 0, backing up by default all blocks changed after the most recent level 0 or 1, known as differential incremental backup. More on different types of level 1 backups is discuss in detail here.
We take an incremental level 0 backup using my script 5_incremental_level_0.sh:
[oracle@dc1sbxdb001 demo]$ ./5_incremental_level_0.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next the incremental backup (Level 0) is taken:
--------------------------------------- Step 2: Take Incremental Level 0 Backup --------------------------------------- Cotent of 5_incremental_level_0.cmd file: BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0'; HOST 'read Press Enter to LIST BACKUP'; LIST BACKUP; Press Enter to continue Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/5_incremental_level_0.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Mon May 20 17:13:13 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0'; 2> HOST 'read Press Enter to LIST BACKUP'; 3> LIST BACKUP; 4> Starting backup at 20-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=15 device type=DISK channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 20-MAY-19 channel ORA_DISK_1: finished piece 1 at 20-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp tag=INCR LEVEL 0 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 20-MAY-19 Starting Control File and SPFILE Autobackup at 20-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-MAY-19 host command complete
We use host within RMAN just to wait for input before moving on for demo purposes. Next we list the backup within RMAN using ‘LIST BACKUP‘:
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 498.93M DISK 00:00:08 16-MAY-19 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 3 77.83M DISK 00:00:01 16-MAY-19 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp List of Archived Logs in backup set 3 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 332298 16-MAY-19 354044 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4 Full 498.94M DISK 00:00:06 16-MAY-19 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 5 3.50K DISK 00:00:00 16-MAY-19 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp List of Archived Logs in backup set 5 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 354044 16-MAY-19 354066 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 7 Full 8.31M DISK 00:00:00 17-MAY-19 BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190517T165453 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp SPFILE Included: Modification time: 17-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 458035 Ckp time: 17-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Incr 0 511.64M DISK 00:00:05 20-MAY-19 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp List of Datafiles in backup set 8 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 9 Full 8.31M DISK 00:00:00 20-MAY-19 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190520T171324 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp SPFILE Included: Modification time: 20-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 571056 Ckp time: 20-MAY-19 Recovery Manager complete. Press Enter to continue
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup 16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs 17-MAY-19 04.55.12.000000 PM Image Copy 20-MAY-19 05.13.44.000000 PM Incremental Level 0 Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Reference
- 8.8 About RMAN Incremental Backups
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
To download all 2 in one zip: 5_incremental_level_0.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
RMAN Image Copy & Demo
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Image CopyAn Image copy backup are exact copies of the datafiles including the free space. They are not stored in RMAN backup pieces but as actual datafiles, therefore are a bit-for-bit copy.
Image Copy DemoWe take an image copy backup using my script 4_image_copy.sh:
[oracle@dc1sbxdb001 demo]$ ./4_image_copy.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next the image copy is taken:
------------------------------ Step 2: Take Image Copy Backup ------------------------------ Cotent of 4_image_copy.cmd file: BACKUP AS COPY DATABASE TAG 'IMAGE COPY'; HOST 'read Press Enter to LIST BACKUP'; LIST COPY; Press Enter to continue Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/4_image_copy.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 17 16:54:31 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP AS COPY DATABASE TAG 'IMAGE COPY'; 2> HOST 'read Press Enter to LIST BACKUP'; 3> LIST COPY; 4> Starting backup at 17-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=32 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gfxp9txc_.dbf tag=IMAGE COPY RECID=1 STAMP=1008521678 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gfxpb246_.dbf tag=IMAGE COPY RECID=2 STAMP=1008521685 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gfxpb9b0_.dbf tag=IMAGE COPY RECID=3 STAMP=1008521691 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gfxpbdgo_.dbf tag=IMAGE COPY RECID=4 STAMP=1008521693 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 17-MAY-19 Starting Control File and SPFILE Autobackup at 17-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 17-MAY-19 host command complete
We use host within RMAN just to wait for input before moving on for demo purposes. Next we list the backup within RMAN using ‘LIST COPY‘:
specification does not match any control file copy in the repository List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 1 1 A 17-MAY-19 458020 17-MAY-19 NO Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gfxp9txc_.dbf Tag: IMAGE COPY 2 2 A 17-MAY-19 458023 17-MAY-19 NO Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gfxpb246_.dbf Tag: IMAGE COPY 3 3 A 17-MAY-19 458027 17-MAY-19 NO Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gfxpb9b0_.dbf Tag: IMAGE COPY 4 4 A 17-MAY-19 458029 17-MAY-19 NO Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gfxpbdgo_.dbf Tag: IMAGE COPY List of Archived Log Copies for database with db_unique_name ZEDDBA ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 3 1 15 A 16-MAY-19 Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_17/o1_mf_1_15_gfxp940y_.arc Recovery Manager complete. Press Enter to continue
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup 16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs 17-MAY-19 04.55.12.000000 PM Image Copy Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Reference
- 8.4 About RMAN Image Copies
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
To download all 2 in one zip: 4_image_copy.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
RMAN Full Backup & Demo
This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Full BackupA full backup is a backup of all the database’s data which excludes free space and stores in RMAN backup pieces. For example, if you have a newly created database with datafiles added that initial size of 10GB, the 10GB from each datafiles is mainly just free space and hence RMAN does not backup the free space, thus space efficient.
Archive Log ModeWhen DML (Data Manipulation Language) is ran against a database, this is stored in online redo logs, so Oracle can reply DML in case of crash recovery to ensure database is consistent and that all committed data is present. Online redo logs fill up and Oracle move onto the next group till reaches the last one and then go back to the first one, but in order to maintain the redo, Oracle “archive” the online redo log to archive logs, hence the word “archive”.
Archive redo logs along with online redo logs are required to recovery a database after a restored, because if the database is open it’s a moving target and the DML will need to be replayed so the the first and last datafile that were backups are consistent i.e. at the same point (SCN) if doing incomplete recovery. Complete recovery is when all the archive redo logs and then online redo logs are applied, taking the database to the current SCN before the restore was done i.e. no data loss.
Demos Enable Archive Log ModeBefore we can take a full backup we need to enable archive log mode using my script 1_enable_archive_log_mode.sh:
[oracle@dc1sbxdb001 demo]$ ./1_enable_archive_log_mode.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base has been set to /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next a table table is created to log each demo as they run in the table:
----------------------------- Step 2: Create demo log table ----------------------------- Content of 1_create_demo_table.sql file: create table demo_log (when timestamp, comments varchar2(200)); exit Press Enter to continue Calling 'sqlplus / as sysdba @1_create_demo_table.sql' Table created. Press Enter to continue
Next we enable archive log mode:
------------------------------- Step 3: Enable Archive Log Mode ------------------------------- Content of 1_enable_archive_log_mode.sql file: alter system set db_recovery_file_dest_size = 15G; shutdown immediate; startup mount; alter database archivelog; alter database open; insert into demo_log values (sysdate, 'Enable Archive Log Mode'); commit; @/media/sf_Software/scripts/demo/demo_log.sql exit Press Enter to continue Calling 'sqlplus / as sysdba @1_enable_archive_log_mode.sql' System altered. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2952790016 bytes Fixed Size 8625080 bytes Variable Size 1677722696 bytes Database Buffers 1258291200 bytes Redo Buffers 8151040 bytes Database mounted. Database altered. Database altered. 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Full Database Backup
We take a full backup using my script 2_full_backup.sh:
[oracle@dc1sbxdb001 demo]$ ./2_full_backup.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base has been set to /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next the full backup is taken:
------------------------ Step 2: Take Full Backup ------------------------ Cotent of 2_full_backup.cmd file: BACKUP DATABASE TAG 'FULL BACKUP'; HOST 'read Press Enter to LIST BACKUP'; LIST BACKUP; Press Enter to continue Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/2_full_backup.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 16 17:38:52 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP DATABASE TAG 'FULL BACKUP'; 2> HOST 'read Press Enter to LIST BACKUP'; 3> LIST BACKUP; 4> Starting backup at 16-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=149 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 16-MAY-19 channel ORA_DISK_1: finished piece 1 at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp tag=FULL BACKUP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 16-MAY-19 Starting Control File and SPFILE Autobackup at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-MAY-19 host command complete
We use host within RMAN just to wait for input before moving on. Next we list the backup within RMAN using ‘LIST BACKUP‘:
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 498.93M DISK 00:00:08 16-MAY-19 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19 Recovery Manager complete. Press Enter to continue
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Full Database Backup with Archive Logs
We take a full backup with archive logs using my script 3_full_backup_plus_archivelogs.sh:
[oracle@dc1sbxdb001 demo]$ ./3_full_backup_plus_archivelogs.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base has been set to /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, then next the full backup with archive logs is taken:
---------------------------------------- Step 2: Take Full Backup plus archivelog ---------------------------------------- Content of 3_full_backup_plus_archivelogs.cmd file: BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG'; HOST 'read Press Enter to LIST BACKUP'; LIST BACKUP; Press Enter to continue Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/3_full_backup_plus_archivelogs.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 16 17:45:54 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG'; 2> HOST 'read Press Enter to LIST BACKUP'; 3> LIST BACKUP; 4> Starting backup at 16-MAY-19 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=158 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=13 RECID=1 STAMP=1008438357 channel ORA_DISK_1: starting piece 1 at 16-MAY-19 channel ORA_DISK_1: finished piece 1 at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp tag=FULL BACKUP PLUS ARCHIVELOG comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_16/o1_mf_1_13_gfv4y4mk_.arc RECID=1 STAMP=1008438357 Finished backup at 16-MAY-19 Starting backup at 16-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 16-MAY-19 channel ORA_DISK_1: finished piece 1 at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp tag=TAG20190516T174603 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 16-MAY-19 Starting backup at 16-MAY-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=14 RECID=2 STAMP=1008438370 channel ORA_DISK_1: starting piece 1 at 16-MAY-19 channel ORA_DISK_1: finished piece 1 at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp tag=FULL BACKUP PLUS ARCHIVELOG comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_16/o1_mf_1_14_gfv4ylo5_.arc RECID=2 STAMP=1008438370 Finished backup at 16-MAY-19 Starting Control File and SPFILE Autobackup at 16-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-MAY-19 host command complete
We use host within RMAN just to wait for input before moving on for demo purposes. Next we list the backup within RMAN using ‘LIST BACKUP‘:
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 498.93M DISK 00:00:08 16-MAY-19 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 3 77.83M DISK 00:00:01 16-MAY-19 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp List of Archived Logs in backup set 3 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 332298 16-MAY-19 354044 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 4 Full 498.94M DISK 00:00:06 16-MAY-19 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 5 3.50K DISK 00:00:00 16-MAY-19 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp List of Archived Logs in backup set 5 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 354044 16-MAY-19 354066 16-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Full 8.31M DISK 00:00:00 16-MAY-19 BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp SPFILE Included: Modification time: 16-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19 Recovery Manager complete. Press Enter to continue
Finally, we update the demo log table:
------------------------------------- Step 3: Updating and viewing demo log ------------------------------------- Calling 'sqlplus / as sysdba' To updated and view demo log 1 row created. Commit complete. WHEN COMMENTS ------------------------------ -------------------------------------------------- 16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode 16-MAY-19 05.40.23.000000 PM Full Backup 16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs Press Enter to exit shell script [oracle@dc1sbxdb001 demo]$Reference Scripts
- 1_enable_archive_log_mode.sh
- 1_enable_archive_log_mode.sql
- 1_create_demo_table.sql
- 2_full_backup.sh
- 2_full_backup.cmd
- 3_full_backup_plus_archivelogs.sh
- 3_full_backup_plus_archivelogs.cmd
To download all 7 in one zip: 1_full_backup.zip
The rest of the series- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
How to create an sosreport on Oracle Linux
When creating a SR for an issues on Oracle Linux, for example in an Exadata environment, you are quite often enough asked to run an sosreport.
What is sosreport?“The “sosreport” is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.
Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services
It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.”
How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)
Why support needs sosreport?“The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.
Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.
The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.
The sosreport helps the support to identify configuration errors and make proactive recommendations too.”
How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)
How to useTo use, simple run “sosreport”:
[root@v1ex1dbadm01 ~]# sosreport sosreport (version 3.2) This command will collect diagnostic and configuration information from this Oracle Linux system and installed applications. An archive containing the collected information will be generated in /tmp/sos.9gvK0N and may be provided to a Oracle USA support representative. Any information provided to Oracle USA will be treated in accordance with the published support policies at: http://linux.oracle.com/ The generated archive may contain data considered sensitive and its content should be reviewed by the originating organization before being passed to any third party. No changes will be made to system configuration. Press ENTER to continue, or CTRL-C to quit. Please enter your first initial and last name [v1ex1dbadm01.v1.com]: Z Anwar Please enter the case id that you are generating this report for []: 3-XXXXXXX1234 Setting up archive ... Setting up plugins ... Running plugins. Please wait ... Running 70/70: xfs... Creating compressed archive... Your sosreport has been generated and saved in: /tmp/sosreport-ZAnwar.3-XXXXXXX1234-20181004103417.tar.xz The checksum is: 04d1a2b728216ba79df6cc38f801de6d Please send this file to your support representative. [root@v1ex1dbadm01 ~]#
You will then have a tar file at the end, which you can upload to your SR for your support engineer to analysis.
If you don’t have sosreport installed, then install the sos package:
[root@v1ex1dbadm01 ~]# yum install sos
References
- How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)
- SRDC – How To Collect Sosreport on Oracle Linux and Oracle VM (Doc ID 1928183.1)
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Moving file location of an Oracle database whilst preserving file permissions on Windows
You may want to move an Oracle database from one location to another on a Windows Server, for example one drive to another. But it’s important to keep all the file permissions preserved, so Oracle can access.
To do this, is simple using xcopy with certain switches as explained in this Microsoft article:
Below is where I needed to move oradata from u: drive to g: drive:
Microsoft Windows [Version 6.3.9600] (c) 2013 Microsoft Corporation. All rights reserved. C:\Windows\system32>xcopy u:\oradata g:\oradata /O /X /E /H /K Does G:\oradata specify a file name or directory name on the target (F = file, D = directory)? d U:\oradata\PEPM\CONTROLFILE\O1_MF_FD9ZRZBP_.CTL U:\oradata\PEPM\DATAFILE\O1_MF_PEPM_ST_FDW1GL8P_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_PEPM_TS_FDW1GX6J_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_PERFSTAT_FDWHDK7L_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_SYSAUX_FD9ZHRHO_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_SYSTEM_FD9ZL3SK_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_TEMP_FD9ZST99_.TMP U:\oradata\PEPM\DATAFILE\O1_MF_UNDOTBS1_FD9ZO4DD_.DBF U:\oradata\PEPM\DATAFILE\O1_MF_USERS_FD9ZO361_.DBF U:\oradata\PEPM\ONLINELOG\O1_MF_1_FD9ZS2RH_.LOG U:\oradata\PEPM\ONLINELOG\O1_MF_2_FD9ZS9P3_.LOG U:\oradata\PEPM\ONLINELOG\O1_MF_3_FD9ZSJDT_.LOG 12 File(s) copied C:\Windows\system32>
And fast_recovery_area from v: drive to i: drive:
Microsoft Windows [Version 6.3.9600] (c) 2013 Microsoft Corporation. All rights reserved. C:\Windows\system32>xcopy v:\fast_recovery_area i:\fast_recovery_area /O /X /E /H /K Does I:\fast_recovery_area specify a file name or directory name on the target (F = file, D = directory)? d V:\fast_recovery_area\PEPM\CONTROLFILE\O1_MF_FD9ZRZGM_.CTL V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_1_FD9ZS67G_.LOG V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_2_FD9ZSDYD_.LOG V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_3_FD9ZSMS6_.LOG 4 File(s) copied C:\Windows\system32>
Without using this method, you would have to manually set the permissions, which is time consuming and error prone.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Resolving Slow Performance, Skipped Checks and Timeouts on Exa Check (exachk)
For more information with regards to Exa Check, please read the following post:
How to use Oracle Exadata Database Machine Exa Check (exachk)
When running the latest exachk (at time of writing, version 18.3.0_20180808), you may notice it takes a long time to run compared to the past. This is due to the vast amount of additional checks carried out by the tool. Due to this, you may also notice you get timeout issues reported in the report:
Killed Processes
exachk found that below commands were killed during the run, so some checks might have failed to execute properly. Refer to the “Slow Performance, Skipped Checks, and Timeouts” section of the user guide for corrective actions.
Killed check Manage ASM Audit File Directory Growth with cron (CHECK-ID: 9DEBED7B8DAB583DE040E50A1EC01BA0) at v1ex2dbadm01 because it timed out Killed check Manage ASM Audit File Directory Growth with cron (CHECK-ID: 9DEBED7B8DAB583DE040E50A1EC01BA0) at v1ex2dbadm02 because it timed out
If you refer to the documentation “Slow Performance, Skipped Checks, and Timeouts“, you’ll see there are various parameters you can set in your environment to increase the default timeouts, which I have done below:
[root@v1ex2dbadm01 exachk]# export RAT_TIMEOUT=300 [root@v1ex2dbadm01 exachk]# export RAT_ROOT_TIMEOUT=900 [root@v1ex2dbadm01 exachk]# export RAT_PASSWORDCHECK_TIMEOUT=10 [root@v1ex2dbadm01 exachk]# export RAT_PROMPT_TIMEOUT=30 [root@v1ex2dbadm01 exachk]# export RAT_PROMPT_WAIT_TIMEOUT=60 [root@v1ex2dbadm01 exachk]# export RAT_REMOTE_RUN_TIMEOUT=10800 [root@v1ex2dbadm01 exachk]# [root@v1ex2dbadm01 exachk]# env | grep RAT RAT_ROOT_TIMEOUT=900 RAT_PROMPT_TIMEOUT=30 RAT_TIMEOUT=300 RAT_REMOTE_RUN_TIMEOUT=10800 RAT_PASSWORDCHECK_TIMEOUT=10 RAT_PROMPT_WAIT_TIMEOUT=60 [root@v1ex2dbadm01 exachk]#
Now when you run exachk, it will wait longer before killing processes.
In addition, if you run the “-dbparallelmax” option, you will increase the number of slave processes for database checks:
[root@v1ex2dbadm01 exachk]# ./exachk -dbparallelmax
PLEASE NOTE: This will consume more resources but will run quicker, so use with caution. Alternatively you can run with “-dbparallel” with a acceptable number of processes and increase as per your requirements.
Now you should not have any timeouts and if you still do, then you will need to review the parameters above and increase again. Alternatively raise an Support Request with Oracle Support.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Extending the root LVM Partition on Exadata
On an Oracle Exadata Database Machine, the ‘/’ (root) is defaulted to a size of 30Gb, which can easily fill up. Luckily this is just a Logical Volume and there’s normally lots of space available on the Logical Volume Group which is usually untapped.
Extending ‘/’Identify how much space is used and free on ‘/’ using df:
[root@v1ex1dbadm01 ~]# df -h / Filesystem Size Used Avail Use% Mounted on /dev/mapper/VGExaDb-LVDbSys1 30G 22G 6.2G 79% / [root@v1ex1dbadm01 ~]#
Display the current logical volume configuration using the lvs command:
[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size LV Path VG LSize LVDbOra1 /dev/VGExaDb/LVDbOra1 VGExaDb 200.00g LVDbSwap1 /dev/VGExaDb/LVDbSwap1 VGExaDb 24.00g LVDbSys1 /dev/VGExaDb/LVDbSys1 VGExaDb 30.00g LVDbSys2 /dev/VGExaDb/LVDbSys2 VGExaDb 30.00g LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb 1.00g [root@v1ex1dbadm01 ~]#
PLEASE NOTE: On Exadata there are 2 SYS volumes, of which one is active and the other inactive. These are used when patching the compute node, as one is a backup of the current and is used for rollback purposes.
Check the online resize option is available using the tune2fs command:
[root@v1ex1dbadm01 ~]# tune2fs -l /dev/VGExaDb/LVDbSys1 | grep resize_inode Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize [root@v1ex1dbadm01 ~]# tune2fs -l /dev/VGExaDb/LVDbSys2 | grep resize_inode Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize [root@v1ex1dbadm01 ~]#
If not available then the file system needs to be unmounted before resizing. Refer to documentation:
Verify there’s space available in the Logical Volume Group using vgdisplay command:
[root@v1ex1dbadm01 ~]# vgdisplay -s "VGExaDb" 1.63 TiB [285.00 GiB used / 1.36 TiB free] [root@v1ex1dbadm01 ~]#
Finally if there’s enough space, then extend the Logical Volumes using lvextend command:
[root@v1ex1dbadm01 ~]# lvextend -L +70G /dev/VGExaDb/LVDbSys1 Size of logical volume VGExaDb/LVDbSys1 changed from 30.00 GiB (7680 extents) to 100.00 GiB (25600 extents). Logical volume LVDbSys1 successfully resized. [root@v1ex1dbadm01 ~]# [root@v1ex1dbadm01 ~]# lvextend -L +70G /dev/VGExaDb/LVDbSys2 Size of logical volume VGExaDb/LVDbSys2 changed from 30.00 GiB (7680 extents) to 100.00 GiB (25600 extents). Logical volume LVDbSys2 successfully resized. [root@v1ex1dbadm01 ~]#
Followed by a resize of the file system using resize2fs command:
[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys1 resize2fs 1.43-WIP (20-Jun-2013) Filesystem at /dev/VGExaDb/LVDbSys1 is mounted on /; on-line resizing required old_desc_blocks = 2, new_desc_blocks = 7 Performing an on-line resize of /dev/VGExaDb/LVDbSys1 to 26214400 (4k) blocks. The filesystem on /dev/VGExaDb/LVDbSys1 is now 26214400 blocks long. [root@v1ex1dbadm01 ~]#
The inactive SYS volume may give you errors as shown below:
[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys2 resize2fs 1.43-WIP (20-Jun-2013) Please run 'e2fsck -f /dev/VGExaDb/LVDbSys2' first. [root@v1ex1dbadm01 ~]#
In which case, you just need to run the command to check the file system for error that may have occurred with journal-ling after unclear shutdown:
[root@v1ex1dbadm01 ~]# e2fsck -f /dev/VGExaDb/LVDbSys2 e2fsck 1.43-WIP (20-Jun-2013) Pass 1: Checking inodes, blocks, and sizes Pass 2: Checking directory structure Pass 3: Checking directory connectivity Pass 4: Checking reference counts Pass 5: Checking group summary information /dev/VGExaDb/LVDbSys2: 111629/1966080 files (0.1% non-contiguous), 5031185/7864320 blocks [root@v1ex1dbadm01 ~]#
Now re-run the resize of the file system using resize2fs command:
[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys2 resize2fs 1.43-WIP (20-Jun-2013) Resizing the filesystem on /dev/VGExaDb/LVDbSys2 to 26214400 (4k) blocks. The filesystem on /dev/VGExaDb/LVDbSys2 is now 26214400 blocks long. [root@v1ex1dbadm01 ~]#
You should now see ‘/’ with additional 70Gb less formatting:
[root@v1ex1dbadm01 ~]# df -h / Filesystem Size Used Avail Use% Mounted on /dev/mapper/VGExaDb-LVDbSys1 99G 22G 72G 24% / [root@v1ex1dbadm01 ~]#
Also see the Logical Volumes are now 100Gb from 30Gb:
[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size LV Path VG LSize LVDbOra1 /dev/VGExaDb/LVDbOra1 VGExaDb 200.00g LVDbSwap1 /dev/VGExaDb/LVDbSwap1 VGExaDb 24.00g LVDbSys1 /dev/VGExaDb/LVDbSys1 VGExaDb 100.00g LVDbSys2 /dev/VGExaDb/LVDbSys2 VGExaDb 100.00g LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb 1.00g [root@v1ex1dbadm01 ~]#
Documentation for reference:
Extending the root LVM Partition on Systems Running Oracle Exadata System Software Release 11.2.3.2.1 or Later
Related Post:
Extending a Non-root LVM Partition on Exadata
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Index Monitoring in Oracle Database
Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced. A new blog post of this feature will be posted in due course.
Since Oracle 10g, you can monitor indexes to see if they are being used or not. Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used. Therefore, it’s recommended to monitor indexes and any unused indexes can be dropped, not only freeing vital space but CPU and I/O, resulting in better throughput.
It recommended to enable index monitoring for an appropriate period that is representative of your full workload cycle. For example if you run a report every week, month or even year, just to catch all those activities.
Please Note: It’s important that foreign key constraints have associated indexes to avoid any table level lock on DML. These indirect uses of indexes will not be detected by index monitoring. So be mindful when dropping indexes that are associated with foreign key constraint.
Enable Index MonitoringTo Enable Index Monitoring, you alter the index as shown below:
ALTER INDEX . MONITORING USAGE;
Checking Index Usage
To check if the index has been used, will depends on the version of Oracle:
Oracle 10g/11gSELECT * FROM V$OBJECT_USAGE;
Database Reference 10g – V$OBJECT_USAGE
Database Reference 11g – V$OBJECT_USAGE
Please Note: Only shows the indexes belonging to the login user.
To see all indexes in the database regardless of the logged on user:
select do.owner, t.name table_name, io.name index_name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring, decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used, ou.start_monitoring, ou.end_monitoring from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_objects do where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo# and i.obj# = do.object_id order by 1, 2, 3;
DISCLAIMER: As accessing data dictionary objects directly, this query is not guaranteed to work.
Oracle 12cR1“The V$OBJECT_USAGE view is deprecated in Oracle Database 12c Release 1 (12.1) and maintained for backward compatibility. Support for this view may be removed in a future release. Oracle recommends that you use the USER_OBJECT_USAGE view instead of the V$OBJECT_USAGE view.”
Database Reference 12cR1 – V$OBJECT_USAGE
For the current logon user:
SELECT * FROM USER_OBJECT_USAGE;
Database Reference 12cR1 – USER_OBJECT_USAGE
For all users:
SELECT * FROM DBA_OBJECT_USAGE;
Database Reference 12cR1 – DBA_OBJECT_USAGE
Dis-enable Index MonitoringTo Dis-enable Index Monitoring, you alter the index as shown below:
ALTER INDEX . NOMONITORING USAGE;
Demo of Index Monitoring
The content of the index_monitoring_demo.sql is below:
------------------------------------------------------------- -- -- Index Monitoring Demo -- -- File Name: index_monitoring_demo.sql (blog.zeddba.com) -- Created: Zahid Anwar (ZedDBA) -- -- Date: 16/07/2018 -- Version: 1.0 -- -- History -- --1.0 16/07/2018 ZA Initial Script -- ------------------------------------------------------------- set echo on set pages 999 lines 400 CREATE TABLE "EMP" ( "ID" NUMBER, "FIRST_NAME" VARCHAR2(50), "LAST_NAME" VARCHAR2(50) ); pause Press Enter INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar'); INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger'); INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs'); commit; pause Press Enter set autotrace on col id format 99 col first_name format a50 col last_Name format a50 select * from emp; pause Press Enter create index emp_id on emp (id); pause Press Enter select * from emp where id = 1; pause Press Enter set autotrace off col INDEX_NAME format a20 col TABLE_NAME format a20 col MONITORING format a15 col USED format a10 select * from USER_OBJECT_USAGE; pause Press Enter alter index emp_id monitoring usage; pause Press Enter select * from USER_OBJECT_USAGE; pause Press Enter set autotrace on select * from emp; pause Press Enter set autotrace off select * from USER_OBJECT_USAGE; pause Press Enter set autotrace on select * from emp where id = 1; pause Press Enter set autotrace off select * from USER_OBJECT_USAGE; pause Press Enter alter index emp_id nomonitoring usage; pause Press Enter select * from USER_OBJECT_USAGE; pause Press Enter DROP TABLE "EMP" PURGE;
Below is the output of the script index_monitoring_demo.sql:
SQL> @index_monitoring_demo.sql
SQL> set pages 999 lines 400
SQL>
SQL> CREATE TABLE "EMP"
2 ( "ID" NUMBER,
3 "FIRST_NAME" VARCHAR2(50),
4 "LAST_NAME" VARCHAR2(50)
5 );
Table created.
SQL>
SQL> pause Press Enter
Press Enter
Table EMP is created in my schema ZEDDBA, please ensure you have appropriate schema with correct privileges. Next we insert 3 rows into the table EMP:
SQL> INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');
1 row created.
SQL> INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');
1 row created.
SQL> INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> pause Press Enter
Press Enter
With the 3 rows inserted and committed, we select from the EMP table with auto trace on, so we can see the execution plan:
SQL> set autotrace on
SQL> col id format 99
SQL> col first_name format a50
SQL> col last_Name format a50
SQL>
SQL> select * from emp;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Please Note: This database is an Exadata Machine, hence the “TABLE ACCESS STORAGE FULL“, otherwise would be “TABLE ACCESS FULL“.
As we can see the Execution Plan was a Full Table Scan (FTS) as this was the only access method available. Next, we create an index on the emp_id:
SQL> create index emp_id on emp (id);
Index created.
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with a predicate on emp_id:
SQL> select * from emp where id = 1;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
Execution Plan
----------------------------------------------------------
Plan hash value: 458854468
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Now, we can see the Execution Plan used the newly created index. Next, we check if the index was used:
SQL> set autotrace off
SQL>
SQL> col INDEX_NAME format a20
SQL> col TABLE_NAME format a20
SQL> col MONITORING format a15
SQL> col USED format a10
SQL> select * from USER_OBJECT_USAGE;
no rows selected
SQL>
SQL> pause Press Enter
Press Enter
We get no rows back as we didn’t turn on index monitoring, which isn’t on by default. So next, we enable index monitoring:
SQL> alter index emp_id monitoring usage;
Index altered.
SQL>
SQL> pause Press Enter
Press Enter
Next, we check the index usage and we can see it monitoring but not used:
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES NO 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with no predicate to do a FTS:
SQL> set autotrace on
SQL>
SQL> select * from emp;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
51 recursive calls
0 db block gets
54 consistent gets
0 physical reads
132 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Next, we check if the index monitoring reflects the usage:
SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES NO 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we query the table with a predicate to do Index Range Scan:
SQL> set autotrace on
SQL>
SQL> select * from emp where id = 1;
ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
Execution Plan
----------------------------------------------------------
Plan hash value: 458854468
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
3 db block gets
24 consistent gets
0 physical reads
876 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> pause Press Enter
Press Enter
Next, we check if the index monitoring reflects the usage:
SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP YES YES 07/16/2018 17:53:59
SQL>
SQL> pause Press Enter
Press Enter
Next, we now turn off index monitoring, as we know it’s been used:
SQL> alter index emp_id nomonitoring usage;
Index altered.
SQL>
SQL> pause Press Enter
Press Enter
Next, we check to see if monitoring is now disabled:
SQL> select * from USER_OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID EMP NO YES 07/16/2018 17:53:59 07/16/2018 17:54:09
SQL>
SQL> pause Press Enter
Press Enter
Finally, we drop the demo EMP table:
SQL> DROP TABLE "EMP" PURGE;
Table dropped.
SQL>
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Oracle Exadata Smart Flash Logging
In an OLTP environment, it is crucial to have fast response times to redo log writes i.e. low latency. When multiplexing redo logs for high availability i.e. to protect against hardware failure, redo log writes are only acknowledged when redo is written to all redo log members i.e when the slowest disk completes the write. By this nature, whenever a disk slows down even if for a moment it can have impact on redo log performance and throughput.
Flash alone can’t resolve this issue as flash can also momentarily slow down due to issues in erase cycles or wear leveling and remember the acknowledgement is only given when the redo is written to all redo log members.
Exadata Smart Flash Logging, is the feature that writes to both hard disk and flash with the acknowledgement given as soon as either completes the write, thus improving response time and throughput. So if a write is slow to hard disk the flash will give a quicker acknowledgement but when flash is experiencing a slow down due to erase cycles or wear leveling then the hard disk will acknowledge, smoothing out response times.
The Exadata Smart Flash Cache isn’t permanent but a temporary store to provide fast response times by storing redo until it’s safely written to disk.
No changes are required to redo log configuration and is transparent to database and recovery.
How to enable Smart Flash Logging?It’s enabled out the box or for older systems it’s enabled when applying cell patch version 11.2.2.4 and also requires Database 11.2.0.2 Bundle Patch 11 or higher.
How to disable Smart Flash Logging?This shouldn’t be done unless instructed to do so by Oracle Support or Development.
How much flash is used by Smart Flash Logging?By default just 512Mb is used per cell, which should be sufficient for most situations. It’s a small investment for huge performance benefit. Statistics record the number of successful write and unsuccessful writes due to the temporary space filled. In which case the size may need to be increased. Also I/O Resource Manager (IORM) can be used to disable Smart Flash Logging for none critical databases.
Do standby redo logs use Smart Flash Logging?Yes, standby redo logs benefit from Smart Flash Logging just as redo logs as long as cell patch 11.2.2.4 or higher is applied and Database 11.2.0.2 Bundle Patch 11 or higher is applied.
How to check that Smart Flash Logging is configured?Using CellCLI run “LIST FLASHLOG DETAIL” and if output is returned as shown below with the details, then this means that Smart Flash Logging is configured:
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list flashlog detail"
v1ex1celadm01: name: v1ex1celadm01_FLASHLOG
v1ex1celadm01: cellDisk: FD_00_v1ex1celadm01,FD_01_v1ex1celadm01
v1ex1celadm01: creationTime: 2015-06-28T17:52:43+01:00
v1ex1celadm01: degradedCelldisks:
v1ex1celadm01: effectiveSize: 512M
v1ex1celadm01: efficiency: 100.0
v1ex1celadm01: id: 366421ec-bf77-499e-870f-f0cf5390343e
v1ex1celadm01: size: 512M
v1ex1celadm01: status: normal
v1ex1celadm02: name: v1ex1celadm02_FLASHLOG
v1ex1celadm02: cellDisk: FD_01_v1ex1celadm02,FD_00_v1ex1celadm02
v1ex1celadm02: creationTime: 2015-06-28T17:52:44+01:00
v1ex1celadm02: degradedCelldisks:
v1ex1celadm02: effectiveSize: 512M
v1ex1celadm02: efficiency: 100.0
v1ex1celadm02: id: 9f670843-c9cc-4156-a32e-8d23fa79cdb8
v1ex1celadm02: size: 512M
v1ex1celadm02: status: normal
v1ex1celadm03: name: v1ex1celadm03_FLASHLOG
v1ex1celadm03: cellDisk: FD_01_v1ex1celadm03,FD_00_v1ex1celadm03
v1ex1celadm03: creationTime: 2015-06-28T17:52:33+01:00
v1ex1celadm03: degradedCelldisks:
v1ex1celadm03: effectiveSize: 512M
v1ex1celadm03: efficiency: 100.0
v1ex1celadm03: id: 749bada6-8ae2-4c51-8410-97622f9a9532
v1ex1celadm03: size: 512M
v1ex1celadm03: status: normal
[root@v1ex1dbadm01 ~]#
For more info:
Exadata Smart Flash Logging FAQ (Doc ID 1372894.1)
Oracle Exadata Whitepaper: Exadata Smart Flash Cache Features and the Oracle Exadata Database Machine
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
How to check if Exadata Write-Back Flash Cache is Enabled
Exadata Write-Back Flash Cache provides the ability to cache not only read I/Os but write I/O to the Exadata’s PCI flash on the storage cells. Exadata storage software 11.2.3.2.1 or higher and Grid Infrastructure and Database software 11.2.0.3.9 or higher is required to use Exadata Write-Back Flash Cache, which is persistent across storage cell restarts.
The default since April 2017 for the Oracle Exadata Deployment Assistant (OEDA) is Write-Back Flash Cache when DATA diskgroup is HIGH redundancy and Grid Infrastructure and Database software are:
- 11.2.0.4.1 or higher
- 12.1.0.2 or higher
- 12.2.0.2 or higher
PLEASE NOTE: This option is only applicable to High Capacity as Extreme Flash doesn’t have Hard Disks and therefore Write-Back Flash Cache is explicitly enabled and can’t be disabled.
What are the Performance Benefit of Exadata Write-Back Flash Cache?Write-Back Flash Cache can significantly improve write intensive operations because writing to Flash Cache is significantly faster than writing to Hard Disks. Depending on the workload, write performance (IOPS) can be improved by 10x on older generations of Exadata Machines V2 and X2 and 20x on newer generations X3 onwards (correct at time of writing).
If you are experiencing high write I/O times on storage cells from AWR Reports or Storage Cell metrics, then you should consider enabling Write-Back Flash Cache to alleviate write operations on Hard Disks and move to Flash Cache.
See the following My Oracle Support (MOS) Note for more info:
Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)
To check if Exadata Write-Back Flash Cache is enabled, run “list cell attributes flashcachemode” on the storage cell using CellCLI as shown below:
[root@v1ex2celadm01 ~]# cellcli CellCLI: Release 12.1.2.3.5 - Production on Wed Jan 17 10:09:51 GMT 2018 Copyright (c) 2007, 2016, Oracle. All rights reserved. CellCLI> list cell attributes flashcachemode WriteThrough CellCLI> exit quitting [root@v1ex2celadm01 ~]#
If “WriteThrough” then Write-Back Flash Cache is disabled (writes go straight to hard disk and then can be placed in flash for caching reads if required), otherwise if “WriteBack” then Write-Back Flash Cache is enabled as the name suggests (writes go straight to flash and then can be moved to hard disk if aged or not required for read caching).
You can also run “list cell detail” using CellCLI as shown below:
[root@v1ex2celadm01 ~]# cellcli CellCLI: Release 12.1.2.3.5 - Production on Wed Jan 17 10:10:22 GMT 2018 Copyright (c) 2007, 2016, Oracle. All rights reserved. CellCLI> list cell detail name: v1ex2celadm01 accessLevelPerm: remoteLoginEnabled bbuStatus: normal cellVersion: OSS_12.1.2.3.5_LINUX.X64_170418 cpuCount: 16/32 diagHistoryDays: 7 eighthRack: TRUE fanCount: 8/8 fanStatus: normal flashCacheMode: WriteThrough id: xxxxxxxxxx interconnectCount: 2 interconnect1: ib0 interconnect2: ib1 iormBoost: 6.4 ipaddress1: 10.1.11.14/22 ipaddress2: 10.1.11.15/22 kernelVersion: 2.6.39-400.294.4.el6uek.x86_64 locatorLEDStatus: off makeModel: Oracle Corporation ORACLE SERVER X5-2L High Capacity memoryGB: 95 metricHistoryDays: 7 notificationMethod: snmp notificationPolicy: critical,warning,clear offloadGroupEvents: powerCount: 2/2 powerStatus: normal releaseImageStatus: success releaseVersion: 12.1.2.3.5.170418 rpmVersion: cell-12.1.2.3.5_LINUX.X64_170418-1.x86_64 releaseTrackingBug: 25509078 rollbackVersion: 12.1.2.3.4.170111 securityCert: PrivateKey OK Certificate: Subject CN=v1ex2celadm01.v1.com,OU=Oracle Exadata,O=Oracle Corporation,L=Redwood City,ST=California,C=US Issuer CN=v1ex2celadm01.v1.com,OU=Oracle Exadata,O=Oracle Corporation,L=Redwood City,ST=California,C=US snmpSubscriber: host=v1ex2dbadm02.v1.com,port=1830,community=public host=v1ex2dbadm01.v1.com,port=1830,community=public host=v1ex2dbadm01.v1.com,port=3872,community=public host=v1ex2dbadm02.v1.com,port=3872,community=public status: online temperatureReading: 24.0 temperatureStatus: normal upTime: 105 days, 7:35 usbStatus: normal cellsrvStatus: running msStatus: running rsStatus: running CellCLI> exit quitting [root@v1ex2celadm01 ~]#
However, the simpler way to check is via dcli, especially when you have lots of storage cells as shown below:
[root@v1ex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list cell attributes flashcachemode" v1ex2celadm01: WriteThrough v1ex2celadm02: WriteThrough v1ex2celadm03: WriteThrough
Related Posts:
How to Enable Exadata Write-Back Flash Cache
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
How to use the Oracle Exadata Diagnostics Collection Tool (sundiag.sh)
Very often when creating a Support Request (SR) for an issue on an Oracle Exadata Database Machine, you’ll need to run the script “sundiag.sh“. Which is the “Oracle Exadata Database Machine – Diagnostics Collection Tool“.
The tool collects a lot of diagnostics information that assist the support analyst in diagnosing your problem, such as failed hardware like a failed disk, etc.
More information can be found on My Oracle Support (MOS) Note:
SRDC – EEST Sundiag (Doc ID 1683842.1)
Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues (Doc ID 761868.1)
To run “sundiag.sh“, is very simple as shown below:
[root@v1ex1dbadm01 ~]# /opt/oracle.SupportTools/sundiag.sh
Oracle Exadata Database Machine - Diagnostics Collection Tool
Gathering Linux information
Skipping collection of OSWatcher/ExaWatcher logs, Cell Metrics and Traces
Skipping ILOM collection. Use the ilom or snapshot options, or login to ILOM
over the network and run Snapshot separately if necessary.
/var/log/exadatatmp/sundiag_v1ex1dbadm01_xxxxxxxxxx_2018_01_17_13_49
Gathering dbms information
Generating diagnostics tarball and removing temp directory
==============================================================================
Done. The report files are bzip2 compressed in /var/log/exadatatmp/sundiag_v1ex1dbadm01_xxxxxxxxxx_2018_01_17_13_49.tar.bz2
==============================================================================
[root@v1ex1dbadm01 ~]#
For more advanced collections, use the option switches to override default behaviour as shown in the help:
[root@v1ex1dbadm01 ~]# /opt/oracle.SupportTools/sundiag.sh -h
Oracle Exadata Database Machine - Diagnostics Collection Tool
Version: 12.1.2.3.3.161109
By default sundiag will collect OSWatcher/ExaWatcher, Cell Metrics and traces,
if there was an alert in the last 7 days. If there is more than one alert, latest
alert is chosen to set the time range for data collection.
Time range is 8hrs prior to and 1hr after the latest alert, for the total of 9 hrs
e.g: latest alert timestamp = 2014-03-29T01:20:04-05:00
echo Time range = 2014-03-28_16:00:00 and 2014-03-29_01:00:00
User can also specify time ranges (as explained in usage below), which takes
precedence over default behavior of checking for alerts
Usage: /opt/oracle.SupportTools/sundiag.sh [ilom | snapshot] [osw <time ranges>]
osw - This argument when used expects value of one or more comma separated
time ranges. OSWatcher/ExaWatcher, cell metrics and traces will be gathered
in those time ranges.
The format for time range(s) is <from>-<to>,<from>-<to> and so on without spaces
where <from> and <to> format is <date>_<time>
<date> and <time> format should be any valid format that can be recognized by
'date' command. The command 'date -d <date>' or 'date -d <time>' should be valid
e.g: /opt/oracle.SupportTools/sundiag.sh osw 2014/03/31_15:00:00-2014/03/31_18:00:00
Note: Total time range should not exceed 9 hrs. Only the time ranges that
fall within this limit are considered for the collection of above data
ilom - User level ILOM data gathering option via ipmitool, in place of
separately using root login to get ILOM snapshot over the network.
snapshot - Collects node ILOM snapshot- requires host root password for ILOM
to send snapshot data over the network.
[root@v1ex1dbadm01 ~]#
Then just upload the bzip2 file to your SR on MOS.
I tend to run this as part of my SR creation and upload to save time.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)