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

Advertisements

RMAN Image Copy & Demo

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)

RMAN Full Backup & Demo

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)

Oracle Database File Placement Best Practice & Create Database Demo

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

Introduction

In order for an Oracle Database to be resilient to physical storage failures, i.e. disk failure, it is imperative that the database files are placed according to Best Practice, so to avoid loss of database in case of a storage failure.  For example, don’t have your database and backups on the same disk, because if the disk fails, you have no means to resolve the failure.

This blog post will advise which files need to be protected and how, followed by a demo of creating a new database which shows how Oracle conforms to the best practice when setting a few parameters.

What files comprise a database?

An Oracle database comprises of the following files:

  • spfile
  • password file
  • control files*
  • datafiles including temp files
  • redo logs*

The files marked with * should be multiplexed over a minimum of 2 locations:

Automatic Storage Management (ASM): +DATA and +FRA

  • If HIGH redundancy, Oracle say to use 1 control file and log member to reduce the control file and log file writes.  However this is Availability vs Performance, if Data Guard is in place then this is a more viable recommendation to have 1, but I recommend still to multiple.

Oracle Managed Files: /oradata and /fast_recovery_area

  • Can be prefixed with the Oracle default of /u01/app/oracle as long as the oradata and fast_recovery_area are physically different, i.e. mounts to different disks or mounts to SAN/NAS.

Once the above is conformed to, you will be able to resolve any issues from any scenario, assuming you take backups 🙂

Create Database Demo

Below we create a database called ZEDDBA, using my createZEDDBA.sh script:

[oracle@dc1sbxdb001 scripts]$ ./createZEDDBA.sh
------------------------------
Step 1: Add Database to oratab
------------------------------

Press Enter to continue

adding ZEDDBA to oratab...

Databases in oratab:
ZEDDBA:/u01/app/oracle/product/12.2.0/dbhome_1:N

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 script adds the line:

ZEDDBA:/u01/app/oracle/product/12.2.0/dbhome_1:N

To the oratab and then sets the environment by using .oraenv in slient mode.

Next the pfile is created from the content shown below:

--------------------
Step 2: Create pfile
--------------------

Press Enter to continue

Content of pfile just created:

db_name='ZEDDBA'
memory_target=2816M
processes = 150
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=10G
diagnostic_dest='u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ZEDDBAXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='12.2.0.1'

Press Enter to continue

--------------------------------
Step 3: Create spfile from pfile
--------------------------------

Press Enter to continue

Calling 'sqlplus / as sysdba'
And running 'create spfile from pfile;'

File created.

Press Enter to continue

PLEASE NOTE: The lines highlighted in red, are the minimum parameters required for Oracle to use Oracle Managed Files (OMF) and multiplex critical files.  This example shows both paths under ‘/u01‘ as an example, which is fine as long as the ‘oradata‘ and ‘fast_recovery_area‘ are physically different as explained earlier on.  They could also be under ‘/u02/oradata‘ for oradata and ‘/u03/fast_recovery_area‘ for fast_recovery_area, which is my personal preference, leaving ‘/u01‘ for oracle binaries.

Next the instance is started in nomount, so to spawn the processes and create the memory structure but not attempt to access controlfiles or datafiles as they do not exist as yet:

-------------------------------------
Step 4: Start the instance in nomount
-------------------------------------

Press Enter to continue

Calling 'sqlplus / as sysdba'
And running 'startup nomount;'

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

Press Enter to continue

Next the database is created using the create database statement:

-------------------------------------------------------
Step 5: Create database using create database statement
-------------------------------------------------------

Press Enter to continue

Content of createZEDDBA.sql just created:

CREATE DATABASE ZEDDBA
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE tempts1
UNDO TABLESPACE undotbs1;

Press Enter to continue

Calling 'sqlplus / as sysdba'
And running '@?/dbs/createZEDDBA.sql'

Database created.

Press Enter to continue

Please Note: I haven’t specified any control files, datafiles or redo logs.  Oracle will automatically create them based on the settings mentioned earlier.

Next we show the database layout:

----------------------------
Step 6: Show database layout
----------------------------

Press Enter to continue

Calling 'sqlplus / as sysdba'
And running '@?/dbs/showfiles.sql'

Showing 'select name from v$controlfile;' Press Enter to continue

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ZEDDBA/controlfile/o1_mf_gftkqxjg_.ctl
/u01/app/oracle/fast_recovery_area/ZEDDBA/controlfile/o1_mf_gftkqxof_.ctl

Showing 'select GROUP#, TYPE, MEMBER, IS_RECOVERY_DEST_FILE from v$logfile;' Press Enter to continue

GROUP# TYPE MEMBER IS_
---------- ------- -------------------------------------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/ZEDDBA/onlinelog/o1_mf_1_gftkqy26_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/ZEDDBA/onlinelog/o1_mf_1_gftkqzk3_.log YES
2 ONLINE /u01/app/oracle/oradata/ZEDDBA/onlinelog/o1_mf_2_gftkqztn_.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/ZEDDBA/onlinelog/o1_mf_2_gftkr1x7_.log YES

Showing 'select name from v$datafile;' Press Enter to continue

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

Showing 'select name from v$tempfile;' Press Enter to continue

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_tempts1_gftkr9c7_.tmp

Press Enter to exit sqlplus

Press Enter to continue

As you can see the control files and redo logs have automatically multiplexed over ‘oradata‘ and ‘fast_recovery_area‘, where as the rest are in ‘oradata‘.  When backups are taken, they will be in ‘fast_recovery_area‘ which allows for control file and datafiles to be recovered in case of lost of ‘oradata‘ once the physical issue is resolved.

Finally run the rest of the script to run the catelog.sql, cateproc.sql, datapatch, etc to complete the database creation:

----------------------------------------------------
Step 7: run catalog.sql, catproc.sql, datapatch, etc
----------------------------------------------------

Press Enter to continue

...
...
...

Session altered.

SQL Patching tool version 12.2.0.1.0 Production on Thu May 16 12:50:03 2019
Copyright (c) 2012, 2017, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10329_2019_05_16_12_50_03/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 25811364 (OJVM RELEASE UPDATE: 12.2.0.1.170718 (25811364)):
Installed in the binary registry only
Bundle series DBRU:
ID 170814 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
25811364 (OJVM RELEASE UPDATE: 12.2.0.1.170718 (25811364))
26609817 (DATABASE RELEASE UPDATE 12.2.0.1.170814)

Installing patches...
Patch installation complete. Total patches installed: 2

Validating logfiles...
Patch 25811364 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/25811364/21188920/25811364_apply_ZEDDBA_2019May16_12_50_21.log (no errors)
Patch 26609817 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26609817/21483023/26609817_apply_ZEDDBA_2019May16_12_50_21.log (no errors)
SQL Patching tool complete on Thu May 16 12:50:38 2019
Start Time: 16-05-2019_12_20
En Time: 16-05-2019_12_50

Press Enter to exit shell script

[oracle@dc1sbxdb001 scripts]$

Reference

Scripts

  1. createZEDDBA.sh
  2. initZEDDBA.ora
  3. createZEDDBA.sql
  4. showfiles.sql
  5. catalog_catproc.sql

To download all 5 in one zip: createZEDDBA.zip

The above demo is based on Oracle Database 12cR2, installed under “/u01/app/oracle/product/12.2.0/dbhome_1“.  Scripts under “/media/sf_Software/scripts/” which I am running Oracle Linux 7.4 on VirtualBox.  Some modification may be required to work in your environment.

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)

RMAN Back to Basics Series

Back in October 2017, I presented “RMAN Back to Basics” at the UKOUG Database SIG.  The intention was to go over RMAN basics but ended up more like a master class 🙂

I was asked if I could share my demos from the presentation, thus the “RMAN Back to Basics Series” was born!  Albeit a year and a half later 🙂

Enjoy!

RMAN Back to Basics 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)

How to create an sosreport on Oracle Linux

When creating a SR for an issues on Oracle Linux, for example in an Exadata environment, you are quite often enough asked to run an sosreport.

What is sosreport?

“The “sosreport” is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.

Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

Why support needs sosreport?

“The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.

Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.

The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.

The sosreport helps the support to identify configuration errors and make proactive recommendations too.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

How to 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)

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

You may want to move an Oracle database from one location to another on a Windows Server, for example one drive to another.  But it’s important to keep all the file permissions preserved, so Oracle can access.

To do this, is simple using xcopy with certain switches as explained in this Microsoft article:

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)