DBA Blogs
Subscribe to business events in Fusion-based SaaS applications from Oracle Integration Cloud ...
We share our skills to maximize your revenue!
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)
Recent Blog Series on Partitioning
1. 1. Introduction (Aug-18)
2. 2. Simple Range Partitioning (Aug-18)
3. 3a. Indexes on a Partitioned Table (Aug-18)
4. 3b. More Indexes on a Partitioned Table (Aug-18)
5. 3c. Unique Index[es] on a Partitioned Table (Aug-18)
6. 3d. Partial Indexing (in 11g) (Sep-18)
7. 4. Row Movement (Sep-18)
8. 5. List Partitioning (Sep-18)
9. 6. Hash Partitioning (Sep-18)
10. 7. Interval Partitioning (Oct-18)
11. 8. Reference Partitioning (Nov-18)
12. 9. System Partitioning (Nov-18)
13. 10. Virtual Column Based Partitioning (Nov-18)
14. 11. Composite Partitioning (Dec-18)
15. 12. Data Dictionary Queries (Dec-18)
16. 13a. Relocating a Partition (Dec-18)
17. 13b. Splitting a Partition (Jan-19)
18. 13c. Merging Partitions (Mar-19)
19. 13d. TRUNCATE and DROP Partitions and Global Indexes (Mar-19)
20. 14. Converting a non-Partitioned Table to a Partitioned Table (Mar-19)
21. 15. Online Modification of Partitioning Type (Strategy) (Apr-19)
22. 16. Hybrid Partitioning (May-19)
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)
MySQL Source Installs for each RDS version
I have been doing a lot of Oracle and PeopleSoft work this year, but I am trying to continue to develop my MySQL and Amazon Web Services (AWS) knowledge at the same time. My goal is to learn some new thing about MySQL and AWS each month and then document it either on this blog or on my company’s internal web site.
This month I decided to focus on building a Linux virtual machine on VirtualBox that has the source code for each version of MySQL that we support on AWS through RDS. I already had an Oracle Linux VM with MySQL 5.7.20 installed from source code from the MySQL GitHub site. So, all I had to do was get the source code to the correct release in git and then recompile it and create a test database. Then I could save a VirtualBox snapshot for that release.
I don’t want to spend time here describing how I did the initial MySQL 5.7.20 install except to say that I followed the steps in the 5.7 reference manual section titled “2.9 Installing MySQL from Source“. The GitHub specific instructions were in the section titled “2.9.3 Installing MySQL Using a Development Source Tree“. I can’t remember why it was a problem, but I could not get Boost installed correctly for CMake to pick it up, so I pass the path to Boost to CMake using the following command:
cmake . -DWITH_BOOST=/home/bobby/boost_1_59_0
instead of what is in the manual. Otherwise I just followed the manual.
I looked at our AWS MySQL RDS databases and found 4 versions: 5.5.46, 5.6.34, 5.7.17, and 5.7.25. So, I wanted to install each of these from source. My idea is that if we hit a bug or unexpected behavior, I can try different versions and see if that behavior is version specific. We could also dive into the source if needed and have the correct version.
Here are the steps that I put together that worked for our 5.7 databases:
cd /home/bobby/mysql-server
make clean
rm CMakeCache.txt
git checkout 5.7
git pull
git checkout mysql-5.7.17
cmake . -DWITH_BOOST=/home/bobby/boost_1_59_0
make
su - root
cd /home/bobby/mysql-server
make install
cd /usr/local/mysql/data
rm -rf *
cd ..
bin/mysqld --initialize --user=mysql
bin/mysql_ssl_rsa_setup
mysqld_safe --user=mysql &
mysql -p
use default password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'My!Password1234';
mysql -p
use My!Password1234
My git repository was /home/bobby/mysql-server and my Linux username was bobby. The database is in /usr/local/mysql/data. The 5.6 and 5.5 databases had a different way to create the database and change the password:
Replace these lines:
bin/mysqld --initialize --user=mysql
bin/mysql_ssl_rsa_setup
mysqld_safe --user=mysql &
mysql -p
use default password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'My!Password1234';
with
scripts/mysql_install_db --user=mysql
./bin/mysqld_safe &
./bin/mysqladmin -u root password 'My!Password1234'
./bin/mysqladmin -u root -h mysqlsrc password 'My!Password1234'
Here are some pictures from the 5.7.17 final working install:










Time will tell if this setup really helps us during some sort of problem, but I like having the source code in case we hit a bug or unexpected behavior. The great thing about open source is that we can see the code, so why not use it?
Bobby
How to Add in Excel?
How to Manage Oracle 12c MultiTenant Database
Oracle Certification 12c Notes – DBA Track
Loading email content into oracle table
Ora-12560: TNS: protocol adapter error
Manipulate the autogenerated names for types inside packages
exporting packages,function etc. from one user to another.
SYSDATE behavior in SQL and PL/SQL
ORA-15067: command or option incompatible with diskgroup redundancy
Partner Webcast – Oracle Visual Builder Cloud Service: Web and Mobile App Development for all
We share our skills to maximize your revenue!
PLS-00436: implementation restriction error when using forall in update
Partner Webcast – Developing Internet of Things Applications with Oracle IoT Cloud Service
We share our skills to maximize your revenue!
Pages
