ODC Appreciation Day : Oracle Exadata Database Machine

Those that know me well, will know about my “appreciation” of the “Oracle Exadata Database Machine“, more commonly known as “Exadata” 🙂

So this will be my contribution to ODC Appreciation Day formally known as OTN Appreciation Day, a great initiative by Tim Hall aka Oracle-Base.com.

You can see a summary of last year’s blog post here:
OTN Appreciation Day : Summary

The very first Exadata, was the V1 model, the hardware by HP and the software by Oracle.  I still remember being very excited by this in my previous employment at Auto Trader and trying very hard to convince them to get one 🙂

I of course became an instant fan of the brawn hardware with smart software, Oracle labelling as “Hardware and Software optimised together“.

Oracle’s partnership with HP only lasted a year with Oracle switching to Sun on the V2 model, when shortly after Oracle then brought Sun in 2010.  This is when Oracle switched from the V models to X models, with the initial models being the X2-2 (2 socket) and X2-8 (8 sockets).

I still remember this old video “Oracle Exadata. Are You Ready?” that I played at an internal Auto Trader conference which was about sharing knowledge, interesting new things, etc:

Exadata has come a long way since the initial release that was aimed at being a Data Warehouse to a Full On OLTP, Data Warehouse, mixed load, consolidation platform, etc with “record-breaking” IOPS and scan rate!

My favourite feature is the smart scan, the ability to off load data intensive SQL operations from the database servers directly into the storage servers, mitigating the need to pull lots of data from storage to database server.  Yes you can have very fast All Flash Storage, but the network to ship all this to the database server becomes the bottleneck and the compute to filter the data on the database server.  Exadata does this at the storage server meaning only the rows and columns that are directly relevant to a query are sent to the database servers.

Another one is storage indexes where the min and max values are stored of a column in 1Mb chunk in memory to allow for unnecessary I/O to be avoided when it’s known that block of data doesn’t meet the predicate condition.

I didn’t manage to convince Auto Trader, however I have since been very fortunate in my current employment at Version 1 to have worked on Exadata since 2014 from the Exadata X2-2 through to X5-2.  I do really appreciate these “Engineered Systems” for the Extreme Performance, Reliability and Availability.  The whole concept of being “Engineered” and the whole stack optimised, really works and the fact that all Exadatas are the same hardware makes you appreciate their supportability.  Even patching them with patchmgr is pretty much a doddle these days! 🙂

For more info, visit the following site:
www.Oracle.com/Exadata
https://en.wikipedia.org/wiki/Oracle_Exadata

Tuesday 10th October 2017

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)

Advertisements

Install Oracle’s VirtualBox

If you don’t have the luxury of having a server ready available but you want to do some research and development or training on Oracle, then Oracle’s VirtualBox is a perfect solution.

Prerequisites

To be able to use VirtualBox, you need to disable Hyper-V as it blocks all other Hyper Visors from calling VT hardware.  See my post blog on how to do this:
Disabling Microsoft’s Hyper-V to use Oracle’s VirtualBox

Download

You can download the latest VirtualBox from:
https://www.virtualbox.org/
https://www.virtualbox.org/wiki/Downloads

At the time of writing this blog, the latest is:
http://download.virtualbox.org/virtualbox/5.1.28/VirtualBox-5.1.28-117968-Win.exe

Install VirtualBox

To install, launch the VirtualBox executable i.e. VirtualBox-5.1.28-117968-Win.exe with a user with admin rights:

VirtualBox_Install_Step1

Click ‘Next‘.

VirtualBox_Install_Step2

Click ‘Next‘.

VirtualBox_Install_Step3

Change options as you wish, otherwise click ‘Next‘.

VirtualBox_Install_Step4

Accept the warning and click ‘Yes‘.

VirtualBox_Install_Step5

Click ‘Install‘.

VirtualBox_Install_Step6

You will see the progress, wait till you see the following:

VirtualBox_Install_Step7

Click ‘Install‘.

VirtualBox_Install_Step8

Once finished, click ‘Finish‘ and VirtualBox will load:

VirtualBox_Install_Step9

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)

Disabling Microsoft’s Hyper-V to use Oracle’s VirtualBox

If you want to use Oracle’s VirtualBox on Windows 10, you first need to disable Microsoft’s Hyper-V.  I’ve used VirtualBox many times in the past but on the likes of Windows 7, where Hyper-V isn’t installed by default as part of the O/S.  It seem on Windows 10 Enterprise, Hyper-V is installed by default and is started as part of the bootup.

Hyper-V blocks all other Hyper Visors like VirtualBox from calling VT hardware, therefore requires to be disabled.

To check

Run ‘bcdedit’ in Command Prompt as Admin:

C:\Users\anwarz>bcdedit

Windows Boot Manager
--------------------
identifier {bootmgr}
device partition=\Device\HarddiskVolume2
path \EFI\Microsoft\Boot\bootmgfw.efi
description Windows Boot Manager
locale en-GB
inherit {globalsettings}
badmemoryaccess Yes
isolatedcontext Yes
default {current}
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
displayorder {current}
toolsdisplayorder {memdiag}
timeout 30

Windows Boot Loader
-------------------
identifier {current}
device partition=C:
path \WINDOWS\system32\winload.efi
description Windows 10
locale en-GB
inherit {bootloadersettings}
recoverysequence {a14884aa-6117-11e7-a334-f430b9153789}
displaymessageoverride Recovery
recoveryenabled Yes
badmemoryaccess Yes
isolatedcontext Yes
allowedinmemorysettings 0x15000075
osdevice partition=C:
systemroot \WINDOWS
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
nx OptIn
bootmenupolicy Standard
hypervisorlaunchtype Auto

C:\Users\anwarz>

You’ll see it say ‘Auto‘ for hypervisorlaunchtype, this means it was enabled to load at boot.  So if this option wasn’t changed since last boot, then Hyper-V is enabled.

To Disable

Run the following command in Command Prompt as Admin:

C:\Users\anwarz>bcdedit /set hypervisorlaunchtype off
The operation completed successfully.

C:\Users\anwarz>bcdedit

Windows Boot Manager
--------------------
identifier {bootmgr}
device partition=\Device\HarddiskVolume2
path \EFI\Microsoft\Boot\bootmgfw.efi
description Windows Boot Manager
locale en-GB
inherit {globalsettings}
badmemoryaccess Yes
isolatedcontext Yes
default {current}
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
displayorder {current}
toolsdisplayorder {memdiag}
timeout 30

Windows Boot Loader
-------------------
identifier {current}
device partition=C:
path \WINDOWS\system32\winload.efi
description Windows 10
locale en-GB
inherit {bootloadersettings}
recoverysequence {a14884aa-6117-11e7-a334-f430b9153789}
displaymessageoverride Recovery
recoveryenabled Yes
badmemoryaccess Yes
isolatedcontext Yes
allowedinmemorysettings 0x15000075
osdevice partition=C:
systemroot \WINDOWS
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
nx OptIn
bootmenupolicy Standard
hypervisorlaunchtype Off

C:\Users\anwarz>

You’ll see it say ‘Off‘ for hypervisorlaunchtype, this means it is now disabled to load at boot.  However, the current boot had this enabled and therefore requires you to reboot to not have Hyper-V loaded.

To Enable

Run the following command in Command Prompt as Admin:

C:\Users\anwarz>bcdedit /set hypervisorlaunchtype auto
The operation completed successfully.

C:\Users\anwarz>bcdedit

Windows Boot Manager
--------------------
identifier {bootmgr}
device partition=\Device\HarddiskVolume2
path \EFI\Microsoft\Boot\bootmgfw.efi
description Windows Boot Manager
locale en-GB
inherit {globalsettings}
badmemoryaccess Yes
isolatedcontext Yes
default {current}
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
displayorder {current}
toolsdisplayorder {memdiag}
timeout 30

Windows Boot Loader
-------------------
identifier {current}
device partition=C:
path \WINDOWS\system32\winload.efi
description Windows 10
locale en-GB
inherit {bootloadersettings}
recoverysequence {a14884aa-6117-11e7-a334-f430b9153789}
displaymessageoverride Recovery
recoveryenabled Yes
badmemoryaccess Yes
isolatedcontext Yes
allowedinmemorysettings 0x15000075
osdevice partition=C:
systemroot \WINDOWS
resumeobject {a14884a8-6117-11e7-a334-f430b9153789}
nx OptIn
bootmenupolicy Standard
hypervisorlaunchtype Auto

C:\Users\anwarz>

You’ll see it say ‘Auto‘ for hypervisorlaunchtype, this means it is now enabled to load at boot.  However, the current boot had this disabled and therefore requires you to reboot to have Hyper-V loaded.

Just a note, I’ve not got anything against Hyper-V, we use it quite frequently, it’s stable, however in this instance, I want to use VirtualBox which I am more accustomed for certain features 🙂

Related Blog Posts

Install Oracle’s VirtualBox

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 Enable DDL Logging in the Database

If for whatever reason, you are required to log DDL, for example, I need to know why the LAST_DDL_TIME of a table was getting updated, you can do this from Oracle 11g.

To enable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

System altered.

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

To disable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=FASLE;

System altered. 

SQL> show parameter ENABLE_DDL_LOGGING 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE 

Create some DDL:

SQL> create view zeddba as select * from dual;

View created.

SQL> select * from zeddba;

D 
- 
X 

SQL> drop view zeddba;

View dropped.

Oracle 12c

Now if you look in the following text file:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

You will see:

Mon Sep 11 15:52:59 2017
diag_adl:create view zahid as select * from dual
diag_adl:drop view zahid

There is also a XML version:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml

<msg time='2017-09-11T15:41:35.000+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'
 version='1'>
 <txt>create view zeddba as select * from dual
 </txt>
</msg>
<msg time='2017-09-11T15:41:45.942+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'>
 <txt>drop view zeddba
 </txt>
</msg>

Oracle 11g

DDL statements are written to the alert log in: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

License

Oracle Database Lifecycle Management Pack for Oracle Database

Licensed Parameters

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.  When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl. The default setting is FALSE.”

More info

Database Reference: ENABLE_DDL_LOGGING

See MOS Note:
How To Enable DDL Logging in Database (Doc ID 2207341.1)

“When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the alert log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER
ALTER/CREATE/DROP FUNCTION
ALTER/CREATE/DROP INDEX
ALTER/CREATE/DROP OUTLINE
ALTER/CREATE/DROP PACKAGE
ALTER/CREATE/DROP PACKAGE BODY
ALTER/CREATE/DROP PROCEDURE
ALTER/CREATE/DROP PROFILE
ALTER/CREATE/DROP SEQUENCE
CREATE/DROP SYNONYM
ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
ALTER/CREATE/DROP TRIGGER
ALTER/CREATE/DROP TYPE
ALTER/CREATE/DROP TYPE BODY
DROP USER
ALTER/CREATE/DROP VIEW

Earlier, RENAME was not logged and a bug was reported for that and the same is fixed in 11.2.0.4.
Document 12938609.8 – ENABLE_DDL_LOGGING does not log RENAME table statements, this is fixed in 11.2.0.4

However, the feature does not log DDLs of some DBMS_STATS operations like:
set_column_stats
set_index_stats
create_extended_stats
drop_extended_stats
set_*_prefs (table/schema/global etc)
delete_pending_stats
publish_pending_stats
export_pending_stats
create_stat_table 

There is an enhancement raised with development to add more operations to this mechanism and would get fixed in 12.2.

Unpublished Bug 22368778 : PERF_DIAG: ENABLE_DDL_LOGGING NEEDS TO LOG MORE DDLS”

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 update OPatch

When applying patches, such as PSUs or one-offs, you may need to update OPatch to meet the minimum OPatch version.  It is also recommended to update OPatch when applying any patch.

To see your current OPatch version:

[oracle@v1ex1dbadm01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@v1ex1dbadm01 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
[oracle@v1ex1dbadm01 ~]$

Backup existing OPatch:

[oracle@v1ex1dbadm01 ~]$ cd $ORACLE_HOME
[oracle@v1ex1dbadm01 dbhome_1]$ tar -cvf OPatch_backup.tar OPatch/*
OPatch/datapatch
OPatch/datapatch.bat
OPatch/docs/
...
OPatch/oplan/README.txt
OPatch/oplan/README.html
OPatch/oplan/oplan
[oracle@v1ex1dbadm01 dbhome_1]$

Check the backup of OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ ls -lh | grep OPatch_backup.tar
-rw-rw-r--. 1 oracle oracle 6.7M Aug 29 11:51 OPatch_backup.tar
[oracle@v1ex1dbadm01 dbhome_1]$

Remove the existing OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ rm -rf OPatch

Unzip the latest OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ unzip -d $ORACLE_HOME ~/sw/p6880880_122010_Linux-x86-64.zip
Archive: /home/oracle/sw/p6880880_122010_Linux-x86-64.zip
creating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/datapatch
...
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/cversion.txt
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/FAQ
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch.bat
[oracle@v1ex1dbadm01 dbhome_1]$

Which can be found here:
OPatch – Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)
OPATCH PLACEHOLDER Patch 6880880

To see your newOPatch version:

[oracle@v1ex1dbadm01 dbhome_1]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.9

OPatch succeeded.
[oracle@v1ex1dbadm01 dbhome_1]$

 

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 obtain the Historical Database Total Used and Allocated Size from OEM Repository

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also good to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date
ORDER BY Database, Month_Date
/

Output:

DATABASE   MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB
---------- --------- ------------ -----------------
VERS       01-SEP-15        1.198             1.554
VERS       01-OCT-15        1.209             1.652
VERS       01-NOV-15          1.3             1.805
...
VERS       01-MAY-17        6.526             7.226
VERS       01-JUN-17        7.085             8.528
VERS       01-JUL-17        7.136             7.569

23 rows selected.

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

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 obtain the Database Total Used, Allocated and Max Size

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

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)