Zed DBA's Oracle Blog

Subscribe to  Zed DBA's Oracle Blog feed  Zed DBA's Oracle Blog
The Oracle DBA Specialist - OCM, OCP, OCE, OCS, ACE
Updated: 2 weeks 6 days ago

RMAN Incremental & Demo Part 2 (Level 1)

Tue, 2019-05-28 11:07

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. 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.
  2. 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.
Incremental Level 1 Demo

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 Scripts
  1. 6_incremental_level_1.sh
  2. 6_incremental_level_1.cmd

To download all 2 in one zip: 6_incremental_level_1.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. 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)

Categories: DBA Blogs

RMAN Incremental & Demo Part 1 (Level 0)

Mon, 2019-05-27 07:15

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. 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.
  2. 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.
Incremental Level 0 Demo

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 Scripts
  1. 5_incremental_level_0.sh
  2. 5_incremental_level_0.cmd

To download all 2 in one zip: 5_incremental_level_0.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. 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)

Categories: DBA Blogs

RMAN Image Copy & Demo

Fri, 2019-05-24 09:38

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Image Copy

An 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 Demo

We 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 Scripts
  1. 4_image_copy.sh
  2. 4_image_copy.cmd

To download all 2 in one zip: 4_image_copy.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. 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)

Categories: DBA Blogs

RMAN Full Backup & Demo

Thu, 2019-05-23 09:13

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Full Backup

A 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 Mode

When 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 Mode

Before 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. 1_enable_archive_log_mode.sh
  2. 1_enable_archive_log_mode.sql
  3. 1_create_demo_table.sql
  4. 2_full_backup.sh
  5. 2_full_backup.cmd
  6. 3_full_backup_plus_archivelogs.sh
  7. 3_full_backup_plus_archivelogs.cmd

To download all 7 in one zip: 1_full_backup.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. 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)

Categories: DBA Blogs

How to create an sosreport on Oracle Linux

Sun, 2019-03-10 13:18

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 use

To 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
More info, can be found in the following MOS note:
  • 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)
Related Posts

 

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)

Categories: DBA Blogs

Moving file location of an Oracle database whilst preserving file permissions on Windows

Mon, 2019-02-04 12:16

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:

https://support.microsoft.com/en-gb/help/323007/how-to-copy-a-folder-to-another-folder-and-retain-its-permissions

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)

 

Categories: DBA Blogs

Resolving Slow Performance, Skipped Checks and Timeouts on Exa Check (exachk)

Wed, 2018-10-03 05:14
Background

For more information with regards to Exa Check, please read the following post:
How to use Oracle Exadata Database Machine Exa Check (exachk)

Slow Performance, Skipped Checks and Timeouts

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)

Categories: DBA Blogs

Extending the root LVM Partition on Exadata

Tue, 2018-09-11 10:09

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:

Extending the root LVM Partition on Systems Running Oracle Exadata System Software Earlier than Release 11.2.3.2.1

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)

Categories: DBA Blogs

Index Monitoring in Oracle Database

Tue, 2018-07-17 10:34

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 Monitoring

To 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/11g
SELECT * 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 Monitoring

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

Categories: DBA Blogs

Oracle Exadata Smart Flash Logging

Tue, 2018-02-06 11:56
What is 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)

Categories: DBA Blogs

How to check if Exadata Write-Back Flash Cache is Enabled

Mon, 2018-01-22 06:14
What is Exadata Write-Back Flash Cache?

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)

How to check if Exadata Write-Back Flash Cache is Enabled?

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)

Categories: DBA Blogs

How to use the Oracle Exadata Diagnostics Collection Tool (sundiag.sh)

Wed, 2018-01-17 09:16
What is 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)

How to run the Diagnostics Collection Tool

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)

Categories: DBA Blogs