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

• Database Administrator’s Guide -> 2.4 Creating a Database with the CREATE DATABASE Statement

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)