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)

 

Oracle’s Autonomous Database (Cloud)

So yesterday I attended the “Autonomous Database GTM Roadmap Sales Workshop” at Oracle’s London office.  This training is for Oracle partners such as Version 1, which is one of Oracle’s strategic partners.

A lot of what is in this blog post is subject to Oracle’s Safe Harbour statement.

My Key Takeaways

1 . Maturity

The Autonomous Database is still very new!  It’s like back in 2008 when the first Exadata Machine (V1) was launched, it was great, it was game changer for large Data Warehouses.  But it wasn’t suited for OLTP and as with anything new it had its fair share of “teething issues”.  However, now passing its 10 year anniversary last year and on its 8th iteration the X7, it’s now a very mature product.  It’s suited for mixed workloads (since the 2nd iteration) and has had so many new features over the years that makes it now a very compelling offering if it suits your business needs.

This is the same for the Autonomous Database, at launch it was only suited for Data Warehouse just as the first Exadata Machine (however soon after another offering was available for OLTP, see further on), it’s not perfect and it has it’s fair share of “teething issues”.  However, come its 10 years anniversary and all the features that are in the road map are implemented, it will be a different story and it will be another very compelling offering from Oracle, again if it suits your business needs.

2. Makeup

The make up of the Autonomous Database in the Oracle Cloud is:

  1. Oracle’s Extreme performance platform, Exadata part of the Oracle Engineered Systems
  2. A streamlined version of 18c database soon to be 19c
  3. Oracle Cloud Automated Data Centre Operations

This is the not so “secret sauce” 🙂

3. Infrastructure Offerings

So the Oracle Autonomous Database comes in 2 offerings:

  1. Serverless Exadata Cloud Infrastructure, which just means it’s shared.  This is for non-mission critical workloads and is non-deterministic performance.  The minimum is 1 TB storage and 1 OCPU and it’s the low cost entry point.  Please Note: This is the ONLY offering at present (Jan 2019).
  2. Dedicated Exadata Cloud Infrastructure, which is as the name suggests dedicated.  This is for mission critical workloads and is deterministic performance.  To be confirmed, but envisioned to be offered like Exadata conventional sizes, i.e. quarter, half and full rack.  The minimum is 1 TB storage and 1 OCPU to all OCPU in the rack size provisioned.  It will have private networks unlike the above offering which is public.  Expecting “soon”, so could be Q2 or Q3 of 2019.

4. Workload Offerings

Once you’ve selected between shared or dedicated, then you need to decide what type of workload as there are two products that apply the autonomous optimisations:

  1. Autonomous Data Warehouse (ADW), which optimises complex SQL, stores in columnar format and creates data summaries.  This was the only offering at launch.
  2. Autonomous Transaction Processing (ATP), which optimises response time, stores in row format and creates indexes autonomously.  Now also available.

The current offering doesn’t let you change between the two, however it is on the road map to be able to converted from one to another, for example to want to test which works best for you or if you have in hindsight made the wrong selection.

5. Automatic Indexing

This one is probably a contentious yet interesting topic!  Us DBAs are used to the world of indexes and us “knowing” what’s right, however the world moved on and AI and Machine Learning is taking away laborious task from us.  The Autonomous Database in the ATP can analyse the workload and use AI and ML to see what indexes are needed over a period of time and eventually have the same elapse time of a workload, however the most interesting aspect is that it will only have indexes that are needed and have a net reduction in indexes, which can often get left behind and have little to no benefit.  There’s no denying we can know better and have a set of indexes with some redundant indexes too but how often is this reviewed to remove unused indexes, add new ones as queries change?  This Automatic Indexing takes away that headache with some volatility as it works out what is required.  I can really see the benefits here and see this being the norm just as Automatic Undo Management is, who in this day and age manages undo segments?

6. Autonomous

The Autonomous Database is:

  1. Self-Driving, performs database maintenance tasks such as tablespace space management, etc.  Automate upgrades and release updates.
  2. Self-Securing, automatically apply secure patch online.  Out the box, all data and network traffic is encrypted.
  3. Self-Repairing, can automatically detect and apply fixes data issues, i.e. resolve block corruption using Active Data Guard, ensure high availability using Real Application Clusters (RAC) and in the event of disaster, use Data Guard physical standby.

7. Is it for you?

Just talking Oracle platforms, there’s a spectrum of platforms, from most Manual to most Autonomous:

  1. Database on commodity hardware on premise
  2. Database on Engineered Systems (Exadata) on premise
  3. Database on Oracle Cloud Infrastructure (OCI)
  4. Exadata Cloud Services / Exadata Cloud @ Customer
  5. Autonomous Database Cloud Services

The more autonomous you go, the more you can focus on your business.

Anyone who’s interested in Autonomous Database, come talk to us 🙂

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)

Querying the amount of redo in Oracle Database

When a database starts to churn more redo then normal, it is handy to be able to extract the amount of redo over time, to be able to plot this on a graph.  The below query allows you to extract this info 🙂

Query for redo generation

Query to obtain the amount of redo generation over time by hour and MB:

set pages 999 lines 400
select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB
from v$archived_log
group by trunc(first_time, 'HH24')
order by 1
/

Output

This is the output you will get from the query:

SQL> set pages 999 lines 400
SQL> select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB
2 from v$archived_log
3 group by trunc(first_time, 'HH24')
4 order by 1
5 /

DATE_BY_HOUR CHURN_IN_MB
------------------- -----------
07/12/2018 10:00:00 2
07/12/2018 11:00:00 51
07/12/2018 12:00:00 3731
07/12/2018 13:00:00 10857
07/12/2018 14:00:00 12505
07/12/2018 15:00:00 17493
07/12/2018 16:00:00 187
07/12/2018 17:00:00 173
07/12/2018 18:00:00 185
07/12/2018 19:00:00 137
07/12/2018 20:00:00 159
07/12/2018 21:00:00 155
07/12/2018 22:00:00 157
07/12/2018 23:00:00 183
08/12/2018 00:00:00 154
08/12/2018 01:00:00 184
08/12/2018 02:00:00 179
08/12/2018 03:00:00 179
08/12/2018 04:00:00 172
08/12/2018 05:00:00 177
08/12/2018 06:00:00 174
08/12/2018 07:00:00 172
08/12/2018 08:00:00 177
08/12/2018 09:00:00 175
08/12/2018 10:00:00 175
08/12/2018 11:00:00 220
08/12/2018 12:00:00 221
08/12/2018 13:00:00 218
08/12/2018 14:00:00 216
08/12/2018 15:00:00 214
08/12/2018 16:00:00 212
08/12/2018 17:00:00 208
08/12/2018 18:00:00 213
08/12/2018 19:00:00 207
08/12/2018 20:00:00 205
08/12/2018 21:00:00 205
08/12/2018 22:00:00 202
08/12/2018 23:00:00 228
09/12/2018 00:00:00 202
09/12/2018 01:00:00 238
09/12/2018 02:00:00 212
09/12/2018 03:00:00 227
09/12/2018 04:00:00 213
09/12/2018 05:00:00 206
09/12/2018 06:00:00 221
09/12/2018 07:00:00 222
09/12/2018 08:00:00 216
09/12/2018 09:00:00 220
09/12/2018 10:00:00 216
09/12/2018 11:00:00 217
09/12/2018 12:00:00 162
09/12/2018 13:00:00 163
09/12/2018 14:00:00 163
09/12/2018 15:00:00 160
09/12/2018 16:00:00 158
09/12/2018 17:00:00 159
09/12/2018 18:00:00 161
09/12/2018 19:00:00 157
09/12/2018 20:00:00 157
09/12/2018 21:00:00 153
09/12/2018 22:00:00 153
09/12/2018 23:00:00 176
10/12/2018 00:00:00 150
10/12/2018 01:00:00 174
10/12/2018 02:00:00 168
10/12/2018 03:00:00 167
10/12/2018 04:00:00 169
10/12/2018 05:00:00 162
10/12/2018 06:00:00 168
10/12/2018 07:00:00 166
10/12/2018 08:00:00 160
10/12/2018 09:00:00 162
10/12/2018 10:00:00 141
10/12/2018 11:00:00 144
10/12/2018 12:00:00 142
10/12/2018 13:00:00 141
10/12/2018 14:00:00 142
10/12/2018 15:00:00 169
10/12/2018 16:00:00 146
10/12/2018 17:00:00 173
10/12/2018 18:00:00 177
10/12/2018 19:00:00 175
10/12/2018 20:00:00 7278
10/12/2018 21:00:00 12604
10/12/2018 22:00:00 18154
10/12/2018 23:00:00 6844
11/12/2018 00:00:00 1350
11/12/2018 01:00:00 505
11/12/2018 02:00:00 1183
11/12/2018 03:00:00 508
11/12/2018 04:00:00 1488
11/12/2018 05:00:00 7071
11/12/2018 06:00:00 16453
11/12/2018 07:00:00 7076
11/12/2018 08:00:00 17310
11/12/2018 09:00:00 8063
11/12/2018 10:00:00 12681
11/12/2018 11:00:00 3678
11/12/2018 14:00:00 6026
11/12/2018 15:00:00 15569
11/12/2018 16:00:00 7069
11/12/2018 17:00:00 11772
11/12/2018 18:00:00 10167
11/12/2018 19:00:00 6159
11/12/2018 20:00:00 16450
11/12/2018 21:00:00 4106
11/12/2018 22:00:00 10115
11/12/2018 23:00:00 10355
12/12/2018 00:00:00 3203
12/12/2018 01:00:00 5160
12/12/2018 02:00:00 14468
12/12/2018 03:00:00 6591
12/12/2018 04:00:00 1376
12/12/2018 05:00:00 4053
12/12/2018 06:00:00 7947
12/12/2018 07:00:00 12433
12/12/2018 08:00:00 1434
12/12/2018 09:00:00 663
12/12/2018 10:00:00 1511
12/12/2018 11:00:00 654
12/12/2018 12:00:00 5661
12/12/2018 13:00:00 9817
12/12/2018 14:00:00 10148
12/12/2018 15:00:00 372
12/12/2018 16:00:00 1074
12/12/2018 17:00:00 672
12/12/2018 18:00:00 1094
12/12/2018 19:00:00 391
12/12/2018 20:00:00 2403
12/12/2018 21:00:00 827
12/12/2018 22:00:00 1108
12/12/2018 23:00:00 15575
13/12/2018 00:00:00 17219
13/12/2018 01:00:00 8255
13/12/2018 02:00:00 877
13/12/2018 03:00:00 180
13/12/2018 04:00:00 1782
13/12/2018 05:00:00 5284
13/12/2018 06:00:00 16191
13/12/2018 07:00:00 6251
13/12/2018 08:00:00 14533
13/12/2018 09:00:00 8138
13/12/2018 10:00:00 12629
13/12/2018 11:00:00 9701
13/12/2018 12:00:00 9869
13/12/2018 13:00:00 9554
13/12/2018 14:00:00 7106
13/12/2018 15:00:00 15094
13/12/2018 16:00:00 8622
13/12/2018 17:00:00 671
13/12/2018 18:00:00 1094
13/12/2018 19:00:00 370
13/12/2018 20:00:00 2332
13/12/2018 21:00:00 421

154 rows selected.

SQL>

The above output can then be used to create a pivot chart in Excel 🙂

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)

ODC Appreciation Day : Oracle dcli Utility

What is ODC Appreciation Day?

The Oracle Developer Community (ODC) Appreciation Day formally known as OTN Appreciation Day, is a great initiative by Tim Hall aka Oracle-Base.com.  Where we take the opportunity to say thanks to the Oracle Developer Community #ThanksODC.

More info on Oracle Developer Community can be found here:
About Oracle Developer Community

When is it?

This year, it is on Thursday 11th October 2018.

You can see my previous post here:
2017 – ODC Appreciation Day : Oracle Exadata Database Machine

You can see a summary of previous years blog posts here:
2016 – OTN Appreciation Day : Summary
2017 – ODC Appreciation Day 2017 : It’s a Wrap (#ThanksODC)

My Contribution : Oracle dcli Utility

When thinking of a subject, Oracle’s dcli Utility on Oracle Exadata Database Machine came to mind due to the frequent use 🙂

What is dcli Utility?

Distributed Command Line Interface (dcli), which it’s main purpose is to execute commands on storage cells on Exadata in parallel.  Which actually is just a Python script.  Those who don’t know Exadata, it’s an Engineered Systems which includes storage in the form of storage cells i.e. servers that have multiple disks utilised by Automatic Storage Management (ASM).  However, the smaller of offer still has 3 storage cells that can go up to 18 storage cells in a rack (elastic configuration).  More info on Exadata can be found here on the latest datasheet (at time of writing):
Exadata X7-2 Datasheet

So as you can imagine, executing commands on 3 servers is tidiuos enough, let alone 18, hence the power and usefulness of dcli!  I don’t just use it for storage cells but compute nodes (database servers), as well as the InfiniBand switches 🙂

More info on dcli can be found in the Exadata Documentation:
Exadata System Software User’s Guide -> 9 Using the dcli Utility

Example usage

Quickly see the version of Exadata Software on your Exadata Machine:

Storage Cells:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root imageinfo | grep "Kernel version\|Active image version"
v1ex1celadm01: Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
v1ex1celadm01: Active image version: 18.1.7.0.0.180821
v1ex1celadm02: Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
v1ex1celadm02: Active image version: 18.1.7.0.0.180821
v1ex1celadm03: Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
v1ex1celadm03: Active image version: 18.1.7.0.0.180821
[root@v1ex1dbadm01 ~]#

Compute Nodes:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root imageinfo | grep "Kernel version\|Image version"
v1ex1dbadm01: Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
v1ex1dbadm01: Image version: 18.1.7.0.0.180821
v1ex1dbadm02: Kernel version: 4.1.12-94.8.4.el6uek.x86_64 #2 SMP Sat May 5 16:14:51 PDT 2018 x86_64
v1ex1dbadm02: Image version: 18.1.7.0.0.180821
[root@v1ex1dbadm01 ~]#

InfiniBands:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/ib_group -l root version | grep "version"
v1ex1sw-iba01: SUN DCS 36p version: 2.2.9-3
v1ex1sw-iba01: BIOS version: SUN0R100
v1ex1sw-ibb01: SUN DCS 36p version: 2.2.9-3
v1ex1sw-ibb01: BIOS version: SUN0R100
[root@v1ex1dbadm01 ~]#

The usage are endless 🙂

When I get a chance, I will create a more in depth blog post about dcli including, how to setup, etc.  I will add the link here, for ease of reference.

Finally Happy ODC Appreciation Day! #ThanksODC #ThanksOTN 🙂

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)