Skip navigation.

DBA Blogs

SQL On The Edge #6 – SQL AlwaysEncrypted

Pythian Group - Thu, 2015-12-31 10:42

Security is on everyone’s mind these days in the IT (and the real) world. Either because they’re dealing with compliance, risks or mitigation, etc. at work or because they just saw on the news yet another item about some big leak/breach happening. It is said that it’s not a question of if your systems will be attacked but when. As part of the SQL product family, Microsoft has now released a new feature called AlwaysEncrypted to continue risk mitigation and strengthen the security story of the product. And I mentioned the SQL ‘product family’ instead of just SQL Server because this feature is also available on Azure SQL Database.

 

What is it?
AlwaysEncrypted is the latest in the set of features that enables encryption inside SQL Server. Let’s look at the list so far:

  • Column level encryption
    This targets specific columns in specific tables, with the encryption/decryption happening at the server.
  • Transparent Database Encryption (A.K.A TDE): This targets entire databases and is transparent to the calling application. It’s also transparent to any user with proper access to the data.
  • AlwaysEncrypted: This also targets specific columns in specific tables, with the encryption/decryption happening ON THE CLIENT.

This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.

 

How Does it Work?
This feature can be enabled through T-SQL or through a wizard in Management Studio. The actual data manipulation is done by the latest version of the ADO .NET client and during configuration, the client will read all of the data, perform the encryption and send it back to SQL Server for storage. The latest 4.6 release of the .NET framework is required. There’s a Column Master Key that will have to be stored in a Windows certificate store, Azure Key Vault or other 3rd party key storage software. During normal application operation, the ADO client will read this master key and use it to decrypt and encrypt the values.

There are two options for this type of encryption:

  1. Randomized
    This will make the same source values encrypt into DIFFERENT encrypted values. Useful for columns that could be correlated by looking at them and won’t be used for searching.
  2. Deterministic: This will make the same source values encrypt into the SAME encrypted values, thus allowing for indexing and searching.

 

For the demo, check the video below where we’ll use the SSMS Wizard to enable AlwaysEncrypted on a column and will show the decryption happening in SSIS using the ADO .NET client!

Enjoy!

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

How to Add Two Ethernet Interfaces with Azure VM

Pythian Group - Thu, 2015-12-31 09:55

 

Recently, working with my test VM on Azure, I needed a second network interface on my VM, but found no way to add one. I was using a standard A2 size Oracle Linux VM. I tried to search it in GUI interface and settings but could find no obvious way to add it. This surprised me so I continued to search and I found some blog posts on how to do it using Azure PowerShell. I discovered that there is no way to add the interface when the VM was already created and that you have to use Azure PowerShell for that. So, if you are a Mac user, as I am, and have only Azure CLI for Mac then you need to find a Windows box. I hope it will be fixed in future releases and that you will be able to manage networks from GUI or using any command line tool provided for Mac, Linux or any other platform. Thus, I will try to explain how you can create a new VM with 2 or more NIC.

 
First, we need to get a Windows machine to run Azure PowerShell commands. I created a small Windows box on Azure itself, explicitly to run the PowerShell when I need it. I’ve chosen basic A1 size Widows 2012 and installed the PowerShell there. It worked fine except you need to be careful if you use more than one monitor and rdp client for Mac. By default it was trying to use all monitors and in my case I got 1.5 screens (one was cut by half because it could not fit to my monitor). I removed check “Use all monitors” in my configuration for that connection in the rdp client. So, the first obstacle was resolved and I continued to work with the next steps.

 
Next, we will need “ImageName” to create a new machine. It can be checked using “Get-AzureVMImage” command. For Oracle linux it looks like:

PS C:\> Get-AzureVMImage | Where-Object { $_.Label -like "*Oracle Linux*" }
VERBOSE: 5:50:58 PM - Begin Operation: Get-AzureVMImage

ImageName : c290a6b031d841e09f2da759bbabe71f__Oracle-Linux-6-12-2014
OS : Linux
MediaLink :
…………………………

Using the given ImageName we can now proceed. Keep in mind that you cannot create a VM with two or more NICs for an A2 size box. For two NICs you need at least a Large (A3) for 2 interfaces or an ExtraLarge(A4) if you need 4 NICs.
Let’s set up the image name:

PS C:\> $image = Get-AzureVMImage -ImageName "c290a6b031d841e09f2da759bbabe71f__Oracle-Linux-6-12-2014"

You need to setup your subscription ID for the session in PowerShell and a storage account:

PS C:\> Set-AzureSubscription -SubscriptionId "321265e2-ffb5-66f9-9e07-96079bd7e0a6" -CurrentStorageAccount "oradb5"

Create a custom config for our VM :

PS C:\> $vm = New-AzureVMConfig -name "oradb5" -InstanceSize "Large" -Image $image.ImageName
PS C:\> Add-AzureProvisioningConfig -VM $vm -Linux -LinuxUser "otochkin" -Password "welcome1"

I’ve created a virtual network “Multi-VNet” with two subnets for my VM and named the subnets as “Public” and “Private”. The Virtual network and subnets you can create in a GUI portal or using command line. I am going to use those subnets for my NICs.
Adding the first subnet to our VM configuration:

PS C:\> Set-AzureSubnet -SubnetName "Public" -VM $vm

Setting static IP for the network:

PS C:\> Set-AzureStaticVNetIP -IPAddress 10.1.1.11 -VM $vm

Adding the second interface to the configuration.

PS C:\> Add-AzureNetworkInterfaceConfig -name "eth1" -SubnetName "Private" -StaticVNetIPAddress 10.0.2.11 -VM $vm

And we can deploy our custom VM now:

PS C:\> New-AzureVM -ServiceName "test-1" -VNetName "Multi-VNet" -VM $vm
WARNING: No deployment found in service: 'test-1'.
VERBOSE: 6:59:03 PM - Begin Operation: New-AzureVM - Create Deployment with VM oradb5

OperationDescription OperationId OperationStatus
——————– ———– —————
New-AzureVM b7fcb2de-eac7-3684-aa8b-d1e9addc4587 Succeeded
VERBOSE: 7:01:08 PM – Completed Operation: New-AzureVM – Create Deployment with VM oradb5

The VM is created and you can check and connect to it. You don’t need your Windows box anymore and can shut it down to save money:

MacBook:~ otochkin$ azure vm list -v
info: Executing command vm list
verbose: Getting virtual machines
data: Name Status Location DNS Name IP Address
data: -------- ------------------ -------- --------------------- ----------
data: winman1 ReadyRole East US winman1.cloudapp.net 10.2.0.16
data: oradb5 ReadyRole East US test-1.cloudapp.net 10.0.1.11
info: vm list command OK
MacBook:~ otochkin$

You can connect to your freshly created VM and check the network:

[root@oradb5 ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:0D:3A:11:A3:71
inet addr:10.0.1.11 Bcast:10.0.1.255 Mask:255.255.254.0
inet6 addr: fe80::20d:3aff:fe11:a371/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:337 errors:0 dropped:0 overruns:0 frame:0
TX packets:353 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:54281 (53.0 KiB) TX bytes:49802 (48.6 KiB)

eth1 Link encap:Ethernet HWaddr 00:0D:3A:11:AC:92
BROADCAST MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)

[root@oradb5 ~]#

And that’s it. In summary I can say that it is not difficult to create more than one interface on an Azure VM, but I think it can be a good addition to GUI (Azure portal). In my next blog post I will try to check other aspects of using Oracle and Linux on Microsoft Azure. Stay tuned.

 

Discover more about our expertise in the Cloud.

Categories: DBA Blogs

Good Habits & Gratitude

Pythian Group - Thu, 2015-12-31 09:40

 

One of my favourite books is The Power of Habit: Why We Do What We Do in Life and Business. With the New Year ahead and many of us focused on how we can continue to “up our game” by creating or changing our habits, it serves as a great read!

When most of us reflect on our habits, we tend to focus on our “bad” habits as opposed to the habits that are “good” or positive. I try to take a different approach by considering all the things that I do well, and incorporating those habits into my daily routine, including being grateful.

Two years ago I received the gift of a leather bound book. There were many use cases for the book, including note taking for work, collecting my favourite quotes, random sketches or jotting down ideas. I chose to use my book as a gratitude journal. At that time, a number of books about gratitude and the art of keeping a gratitude journal hit several bestseller lists. While I didn’t begin one then, I was keen on the new daily habit of documenting my gratitude.

As far as new habits go, this one was fairly easy to adopt:

  1. Find journal.
  2. Have journal in a convenient place.
  3. Pick a time of day to write in journal.
  4. Be grateful.
  5. Write it down.

My entries have covered everything from lessons I’ve learned, celebrating wins at work, special moments I’ve experienced, feelings I’ve felt, acknowledging good fortunes like health and wellness, etc. On days when I’m really pressed for time, I mindfully think about what I’m grateful for and log it in the next day. Sometimes the entries are short like a note about health, happiness, family, friends, a chocolate brownie, a great book, warm boots, etc.

This habit continues to help me remember and recognize the things that really matter to me. In times of stress or challenge my journal entries serve as a reminder that it’s important to take a few moments to find something to be grateful for. And while you don’t need a journal to be grateful, it’s wonderful to flip back and read what you were grateful for eight months ago, six weeks ago or even four days ago.

Today I’m grateful for the free speech that allows me to write this blog post, the winter tires on my car, the collective talents of the Pythian HR team, for the amazing 400+ colleagues in 36 countries who we get to work with every day and the opportunities that lay ahead for all of us in 2016.

What new habit(s) will you form in 2016?

Categories: DBA Blogs

Happy New Year 2016!

The Oracle Instructor - Thu, 2015-12-31 02:08

Another year has passed. I take the opportunity to thank you for visiting and to wish you a Happy New Year 2016!

Happy New Year 2016!

In case you didn’t recognize: That is supposed to look like fireworks, The Oracle Instructor style ;-)

2015 was a great year for uhesse.com with 345,000+ views and the crossing of the one million hits threshold. Top countries with more than 4,000 views in 2015 were

Visitors 2015

Visitors came from 202 countries, even China is on the list this year with 1,500+ views.

Hope to see all of you again here in 2016 :-)


Categories: DBA Blogs

Log Buffer #455: A Carnival of the Vanities for DBAs

Pythian Group - Wed, 2015-12-30 13:45

What better to do during the holiday season than to read the Log Buffer? This log buffer edition is here to add some sparkle to Oracle, MySQL and SQL Server on your days off.

Oracle:

  • Ops Center version 12.3.1 has just been released. There are a number of enhancements here.
  • Oracle R Enterprise (ORE) 1.5 is now available for download on all supported platforms with Oracle R Distribution 3.2.0 / R-3.2.0. ORE 1.5 introduces parallel distributed implementations of Random Forest, Singular Value Decomposition (SVD), and Principal Component Analysis (PCA) that operate on ore.frame objects.
  • Create a SOA Application in JDeveloper 12c Using Maven SOA Plug-In by Daniel Rodriguez.
  • How reliable are the memory advisors?
  • Oracle Enterprise Manager offers a complete cloud solution including self-service provisioning balanced against centralized, policy-based resource management, integrated chargeback and capacity planning and complete visibility of the physical and virtual environments from applications to disk.

SQL Server:

  • SQL Server Data Tools (SSDT) and Database References.
  • Stairway to SQL Server Extended Events Level 1: From SQL Trace to Extended Events.
  • Advanced Mathematical Formulas using the M Language.
  • Liberating the DBA from SQL Authentication with AD Groups.
  • Enterprise Edition customers enjoy the manageability and performance benefits offered by table partitioning, but this feature is not available in Standard Edition.

MySQL:

  • Is MySQL X faster than MySQL Y? – Ask query profiler.
  • Usually when one says “SSL” or “TLS” it means not a specific protocol but a family of protocols.
  • The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.10, MariaDB Galera Cluster 5.5.47, and MariaDB Galera Cluster 10.0.23.
  • EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery.
  • Use MySQL to store data from Amazon’s API via Perl scripts.

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Upgrade a Pluggable Database in #Oracle 12c

The Oracle Instructor - Tue, 2015-12-29 09:27

This is how an upgrade with pluggable databases looks conceptually:
You have two multitenant databases from different versions in place. Preferably they share the same storage, which allows to do the upgrade without having to move any datafiles
Initial state

You unplug the pluggable database from the first multitenant database, then you drop it. That is a fast logical operation that does not delete any files

unplug drop

Next step is to plug in the pluggable database into the multitenant database from the higher version

plug in

So far the operations were very fast (seconds). Next step takes longer, when you upgrade the pluggable database in its new destination

catupgrade.sql

Now let’s see that with details:

 

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/CDB1/system01.dbf
/oradata/CDB1/pdbseed/system01.dbf
/oradata/CDB1/sysaux01.dbf
/oradata/CDB1/pdbseed/sysaux01.dbf
/oradata/CDB1/undotbs01.dbf
/oradata/CDB1/users01.dbf

6 rows selected.

SQL> host mkdir /oradata/PDB1

SQL> create pluggable database PDB1 admin user adm identified by oracle
  2  file_name_convert=('/oradata/CDB1/pdbseed/','/oradata/PDB1/');

Pluggable database created.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=PDB1;

Session altered.

SQL> create tablespace users datafile '/oradata/PDB1/users01.dbf' size 100m;

Tablespace created.

SQL> alter pluggable database default tablespace users;

Pluggable database altered.

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> create table adam.t as select * from dual;

Table created.

The PDB should have its own subfolder underneath /oradata respectively in the DATA diskgroup IMHO. Makes not much sense to have the PDB subfolder underneath the CDBs subfolder because it may get plugged into other CDBs. Your PDB names should be unique across the enterprise anyway, also because of the PDB service that is named after the PDB.

I’m about to upgrade PDB1, so I run the pre upgrade script that comes with the new version

SQL> connect / as sysdba
Connected.

SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in CDB$ROOT...
***************************************************************************


      ************************************************************

                 ====>> ERRORS FOUND for CDB$ROOT <<==== The following are *** ERROR LEVEL CONDITIONS *** 
that must be addressed prior to attempting your upgrade. Failure to do so will result in a failed upgrade. 
You MUST resolve the above errors prior to upgrade 
************************************************************ 
************************************************************ 
====>> PRE-UPGRADE RESULTS for CDB$ROOT <<==== ACTIONS REQUIRED: 
1. Review results of the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade.log 
2. Execute in the SOURCE environment BEFORE upgrade: /u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql 
3. Execute in the NEW environment AFTER upgrade: /u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups.sql 
************************************************************ 
*************************************************************************** 
Pre-Upgrade Checks in CDB$ROOT Completed. 
*************************************************************************** 
*************************************************************************** 
*************************************************************************** 
SQL> @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups
Pre-Upgrade Fixup Script Generated on 2015-12-29 07:02:21  Version: 12.1.0.2 Build: 010
Beginning Pre-Upgrade Fixups...
Executing in container CDB$ROOT

**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

No fixup routines were executed.

           **************************************************
**************** Pre-Upgrade Fixup Script Complete *********************
SQL> EXECUTE dbms_stats.gather_dictionary_stats

Not much to fix in this case. I’m now ready to unplug and drop the PBD

SQL> alter pluggable database PDB1 close immediate;
SQL> alter pluggable database PDB1 unplug into '/home/oracle/PDB1.xml';
SQL> drop pluggable database PDB1;

PDB1.xml contains a brief description of the PDB and needs to be available for the destination CDB. Keep in mind that no files have been deleted

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@localhost:~$ . oraenv
ORACLE_SID = [CDB1] ? CDB2
The Oracle base remains unchanged with value /u01/app/oracle
oracle@localhost:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 07:11:16 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/CDB2/system01.dbf
/oradata/CDB2/pdbseed/system01.dbf
/oradata/CDB2/sysaux01.dbf
/oradata/CDB2/pdbseed/sysaux01.dbf
/oradata/CDB2/undotbs01.dbf
/oradata/CDB2/users01.dbf

6 rows selected.

The destination CDB is on 12.1.0.2 and shares the storage with the source CDB running on 12.1.0.1. Actually, they are both running on the same server. Now I will check if there are any potential problems with the plug in

SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/PDB1.xml',
pdb_name => 'PDB1')
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11
NO

PL/SQL procedure successfully completed.

SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';

MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PDB's version does not match CDB's version: PDB's version 12.1.0.0.0. CDB's vers
ion 12.1.0.2.0.
PENDING

Now that was to be expected: The PDB is coming from a lower version. Will fix that after the plug in

SQL> create pluggable database PDB1 using '/home/oracle/PDB1.xml' nocopy;

Pluggable database created.

SQL> alter pluggable database PDB1 open upgrade;

Warning: PDB altered with errors.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

We saw the first three phases so far and everything was quite fast. Not so with the next step

oracle@localhost:~$ cd $ORACLE_HOME/rdbms/admin
oracle@localhost:/u01/app/oracle/product/12.1.0.2/rdbms/admin$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'PDB1' catupgrd.sql

Argument list for [catctl.pl]
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = 0
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = PDB1
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle

Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_17942.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 2
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count     = 0
New SQL Process Count = 2

[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1] Exclusion:[]

Start processing of PDB1
[/u01/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c 'PDB1' -I -i pdb1 -n 2 catupgrd.sql]

Argument list for [catctl.pl]
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = 0
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = pdb1
Run in                c = PDB1
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 1

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle

Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrdpdb1_catcon_18184.lst
catcon: See catupgrdpdb1*.log files for output generated by scripts
catcon: See catupgrdpdb1_*.lst files for spool files, if any
Number of Cpus        = 2
SQL PDB Process Count = 2
SQL Process Count     = 2

[CONTAINER NAMES]

CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1] Exclusion:[]

------------------------------------------------------
Phases [0-73]         Start Time:[2015_12_29 07:19:01]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------
Serial   Phase #: 0    PDB1 Files: 1     Time: 14s
Serial   Phase #: 1    PDB1 Files: 5     Time: 46s
Restart  Phase #: 2    PDB1 Files: 1     Time: 0s
Parallel Phase #: 3    PDB1 Files: 18    Time: 17s
Restart  Phase #: 4    PDB1 Files: 1     Time: 0s
Serial   Phase #: 5    PDB1 Files: 5     Time: 17s
Serial   Phase #: 6    PDB1 Files: 1     Time: 10s
Serial   Phase #: 7    PDB1 Files: 4     Time: 6s
Restart  Phase #: 8    PDB1 Files: 1     Time: 0s
Parallel Phase #: 9    PDB1 Files: 62    Time: 68s
Restart  Phase #:10    PDB1 Files: 1     Time: 0s
Serial   Phase #:11    PDB1 Files: 1     Time: 13s
Restart  Phase #:12    PDB1 Files: 1     Time: 0s
Parallel Phase #:13    PDB1 Files: 91    Time: 6s
Restart  Phase #:14    PDB1 Files: 1     Time: 0s
Parallel Phase #:15    PDB1 Files: 111   Time: 13s
Restart  Phase #:16    PDB1 Files: 1     Time: 0s
Serial   Phase #:17    PDB1 Files: 3     Time: 1s
Restart  Phase #:18    PDB1 Files: 1     Time: 0s
Parallel Phase #:19    PDB1 Files: 32    Time: 26s
Restart  Phase #:20    PDB1 Files: 1     Time: 0s
Serial   Phase #:21    PDB1 Files: 3     Time: 7s
Restart  Phase #:22    PDB1 Files: 1     Time: 0s
Parallel Phase #:23    PDB1 Files: 23    Time: 104s
Restart  Phase #:24    PDB1 Files: 1     Time: 0s
Parallel Phase #:25    PDB1 Files: 11    Time: 40s
Restart  Phase #:26    PDB1 Files: 1     Time: 0s
Serial   Phase #:27    PDB1 Files: 1     Time: 1s
Restart  Phase #:28    PDB1 Files: 1     Time: 0s
Serial   Phase #:30    PDB1 Files: 1     Time: 0s
Serial   Phase #:31    PDB1 Files: 257   Time: 23s
Serial   Phase #:32    PDB1 Files: 1     Time: 0s
Restart  Phase #:33    PDB1 Files: 1     Time: 1s
Serial   Phase #:34    PDB1 Files: 1     Time: 2s
Restart  Phase #:35    PDB1 Files: 1     Time: 0s
Restart  Phase #:36    PDB1 Files: 1     Time: 1s
Serial   Phase #:37    PDB1 Files: 4     Time: 44s
Restart  Phase #:38    PDB1 Files: 1     Time: 0s
Parallel Phase #:39    PDB1 Files: 13    Time: 67s
Restart  Phase #:40    PDB1 Files: 1     Time: 0s
Parallel Phase #:41    PDB1 Files: 10    Time: 6s
Restart  Phase #:42    PDB1 Files: 1     Time: 0s
Serial   Phase #:43    PDB1 Files: 1     Time: 6s
Restart  Phase #:44    PDB1 Files: 1     Time: 0s
Serial   Phase #:45    PDB1 Files: 1     Time: 1s
Serial   Phase #:46    PDB1 Files: 1     Time: 0s
Restart  Phase #:47    PDB1 Files: 1     Time: 0s
Serial   Phase #:48    PDB1 Files: 1     Time: 140s
Restart  Phase #:49    PDB1 Files: 1     Time: 0s
Serial   Phase #:50    PDB1 Files: 1     Time: 33s
Restart  Phase #:51    PDB1 Files: 1     Time: 0s
Serial   Phase #:52    PDB1 Files: 1     Time: 0s
Restart  Phase #:53    PDB1 Files: 1     Time: 0s
Serial   Phase #:54    PDB1 Files: 1     Time: 38s
Restart  Phase #:55    PDB1 Files: 1     Time: 0s
Serial   Phase #:56    PDB1 Files: 1     Time: 12s
Restart  Phase #:57    PDB1 Files: 1     Time: 0s
Serial   Phase #:58    PDB1 Files: 1     Time: 0s
Restart  Phase #:59    PDB1 Files: 1     Time: 0s
Serial   Phase #:60    PDB1 Files: 1     Time: 0s
Restart  Phase #:61    PDB1 Files: 1     Time: 0s
Serial   Phase #:62    PDB1 Files: 1     Time: 1s
Restart  Phase #:63    PDB1 Files: 1     Time: 0s
Serial   Phase #:64    PDB1 Files: 1     Time: 1s
Serial   Phase #:65    PDB1 Files: 1 Calling sqlpatch [...] Time: 42s
Serial   Phase #:66    PDB1 Files: 1     Time: 1s
Serial   Phase #:68    PDB1 Files: 1     Time: 8s
Serial   Phase #:69    PDB1 Files: 1 Calling sqlpatch [...] Time: 53s
Serial   Phase #:70    PDB1 Files: 1     Time: 91s
Serial   Phase #:71    PDB1 Files: 1     Time: 0s
Serial   Phase #:72    PDB1 Files: 1     Time: 5s
Serial   Phase #:73    PDB1 Files: 1     Time: 0s

------------------------------------------------------
Phases [0-73]         End Time:[2015_12_29 07:35:06]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 966s PDB1

LOG FILES: (catupgrdpdb1*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/cfgtoollogs/CDB2/upgrade/upg_summary.log

Total Upgrade Time:          [0d:0h:16m:6s]

     Time: 969s For PDB(s)

Grand Total Time: 969s

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time:    [0d:0h:16m:9s]

Even this tiny PDB with very few objects in it took 16 minutes. I have seen this step taking more than 45 minutes on other occasions.

oracle@localhost:/u01/app/oracle/product/12.1.0.2/rdbms/admin$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 12:45:36 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups
Post Upgrade Fixup Script Generated on 2015-12-29 07:02:21  Version: 12.1.0.2 Build: 010
Beginning Post-Upgrade Fixups...

**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

No fixup routines were executed.

           **************************************************
*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

PL/SQL procedure successfully completed.

Done! I was using the excellent Pre-Built Virtualbox VM prepared by Roy Swonger, Mike Dietrich and The Database Upgrade Team for this demonstration. Great job guys, thank you for that!
In other words: You can easily test it yourself without having to believe it :-)


Tagged: 12c New Features, Multitenant, upgrade
Categories: DBA Blogs

Links for 2015-12-23 [del.icio.us]

Categories: DBA Blogs

LEVERAGE GEOGRAPHICALLY-DISTRIBUTED DEVELOPMENT

Kubilay Çilkara - Wed, 2015-12-23 14:44
As technology advances at warp speed, there are certain tech methodologies that will go by the wayside to make room for more advanced and efficient versions; and how development projects are managed is a case, in point.  Companies in every industrialized nation of the world are embracing Geographically-Distributed Development or GDD, which has embedded itself an impressive and proof-positive IT strategy model.  Outdated procedures that have been utilized for the administration of virtually any type of development project have been limited to one or several building sites.  That was then; this is now.
Let’s take a look at the advantages that GDD offers:decreased labor expenses increased availability to skilled resourcesreduced time-to-market, with round-the-clock flexible staffingThe beauty of GDD is that is allows enterprises, regardless of location, to respond to changes in business circumstances as they happen.  Any feedback can be presented, instantaneously, within a global framework.
In order for GDD to achieve its vast benefit potential, major barriers that might impede an enterprise’s successes must be reduced to a minimum or entirely eliminated within the GDD strategy.   It is crucial that increased expenses associated with communication and coordination logistics that occur on an international level within a globally-distributed market, be uncovered and targeted.  If communication and coordination-specific expenses are allowed to flourish, the very benefits of GDD can be sorely compromised.  Various challenges must be reckoned with:  1) cultural variances 2) language differences and 3) inaccessibility to time-sensitive information.  These can all jeopardize the progress of distributed projects.
GDD is oblivious to location. it is an IT strategy model without borders.  This allows development team-members to work collectively and cohesively within a city, across state lines or beyond continents.  A site or sites might be engaged with one particular software-development venture while one or more outsourcing companies work, simultaneously, towards the projected goal.   Outsourcing companies would contribute their efforts and expertise, like a fine-tuned engine, within the software’s project-development cycle.  Optimized efficiency and cost savings, via structured and coordinated local or global team-work, becomes refreshingly realized.
With GDD, thorough and clear communication is established between all team members and project coordination.  Business demands incorporate global-sourcing, service-oriented architecture, new compliance regulations, new development methodologies, reduced release cycles and broadened application lifetimes.  Because of this, highly-effective, unencumbered communication is mission-critical; and a necessity arises that begs for a solution that has the power to:Provide management visibility of all change activities among distributed development teams  Integrate and automate current change processes and best practices within the enterpriseOrganize the distribution of dependent change components among platforms and teamsProtect intellectual property
Track and authenticate Service Level Agreements (SLAs)Engaging an organization to efficiently manage and significantly optimize communication among all stakeholders in the change process is a priceless component of an Application Lifecycle Management (ALM) solution.  Multiple GDD locales present inherent challenges:  language and cultural divides, varying software-development methods, change-management protocol, security employment, adherence to industry mandates and client business requisites.  The good news is the ALM solution tackles these hurdles with ease!
Provide Management Visibility of all Change Activities among Distributed Development Teams
When a centralized repository allows for the viewing of all the activities, communications and artifacts that could be impacted by the change process, you have beauty in motion; and this is what ALM does.  Via ALM, users have the luxury of effortlessly viewing project endeavors by each developer, development group or project team--irrespective of location, platform and development setting.  This type of amenity becomes especially striking when one begins to compare this model-type with other distributed environments where work-in-progress is not visible across teams due to niche teams employing their own code repositories.
ALM provides the opportunity for development managers to not only track, but validate a project’s standing.  A project’s status can be verified which helps to guarantee the completion of tasks.  User-friendly dashboards will alert management if vital processes indicate signs of sluggishness or inefficiency.
ALM ensures that the overall development objectives will be met on a consistent basis.  This is accomplished through the seamless coordination between both remote and local development activities.  The ALM-accumulated data plays a crucial role with boosting project management, status tracking, traceability, and resource distribution.  Development procedures can be continually improved upon, thanks to generated reports that allow for process metrics to be collected and assessed.  Also, ALM allows regulatory and best-practices compliance to be effortlessly monitored and evaluated.  Compliance deals with structuring the applicable processes and creating the necessary reports.  ALM executes compliance strategy and offers visibility to the needed historical information, regardless of users’ geographic locations.
Integrate and Automate Current Change Processes and Best Practices within the Enterprise
In a perfect world, each and every facet of a company’s application development would be super easy; and with ALM it is.  By way of ALM, companies can establish the defined, repeatable, measureable and traceable processes based on best practices, with absolute perfection.  User-friendly point-and-click set-up functions enable one to create a collection of authorized processes that automate task assignments and movement of application artifacts.
ALM permits the streamlining of change management by means of its simplicity when dealing with changes and necessary proceedings.  This in turn means changes can be analyzed and prioritized.  The approval management functions demand that official authorizations must be secured before any changes are permitted to go forth.  The ATM’s automated logging functions totally un-complicate the tracking of software changes.  This is huge since changes can be tracked from the time a request is received up to the time when a solution is submitted to production.
Every member that is part of the global development team would be duly notified regarding any required assignments as well as any happenings that would have an impact on their efforts.
Organize the Distribution of Dependent Change Components among Teams and Platforms
It’s no secret that when there are changes within just one system of a cohesive enterprise, those changes can impact other systems.  ALM offers multi-platform support which ensures that modifications made on disparate platforms, by way of geographically-dispersed teams can be navigated through the application lifecycle jointly.  A Bill of Materials Process, or BOMP, serves as an on-board feature that permits users to create file portfolios that incorporate characteristics from various platforms.  This means those portfolios can travel through the lifecycle as a unit.  Additionally, some ALM solutions absolutely ensure that the parts within the assemblies are positioned with the suitable platforms at each state of the lifecycle. 
Protect Intellectual Property
An ALM solution is the perfect component that allows for access and function control over all managed artifacts.  Managers are in a position to easily determine and authorize any access to vital intellectual property due to ALM functioning on a role-based control system.  The role-based structure means administrative operations are streamlined which permits any system administrator to bypass assigning individual rights to a single user.  Additionally, a role-based system delivers a clearly-defined synopsis of access rights between groups of individuals.
Track and Authenticate Service Level Agreements
The overall project plan, absolutely, must remain on schedule while administering accountability for established deliveries; and this can be easily realized through ALM’s ability to track and authenticate tasks and processes.  The ALM solution caters to satisfying Service Level Agreement (SLA) requirements within an outsourcing contract.  As a result, project management is enhanced by ensuring performance of specific tasks.  Optimizing the user’s ability to track emphasized achievements is made possible due to the consistency between tasks that have been assigned to developers and tasks that are part of the project plan.   Invaluable ALM-generated reports will track response and resolution times; and service-level workflows automate service processes and offer flexibility.  This translates into an acceleration of processes to the respective resources to meet project deadlines.  The ability to track performance against service-level agreements is made possible due to the availability of reports and dashboards that are at one’s fingertips.
Enhance Your Geographically-Distributed Development
As stated, ALM is beauty in motion; and aside from promoting perfected-levels of communication and coordination, it utilizes management strategies designed to plow through any obstructions that have the potential to compromise success.   ALM’s centralized repository is purposed to present multiple ideas, designs, dialogue, requirements, tasks and much more to team-members who would require or desire instant access to data.  Development procedures and tasks can be precisely and efficiently automated and managed due to ALM’s cohesive workflow capabilities.  Vital intellectual property, all of it is embedded and safeguarded in a central repository.  Due to this caliber of reinforced protection, loss and unauthorized access is null and void.  When remote software development is in-sync with local development, project management becomes seamless, highly-coordinated and error-free.  Integration of the monitoring, tracking and auditing of reports and dashboards means management can successfully satisfy project deadlines.  It would behoove any enterprise who wishes to reap the rewards of GDD to fully embrace ALM as its solution, it is truly mission-critical.
Application Lifecycle Management Solutions
Application lifecycle management programs are able to easily deliver a plethora of enterprise software modifications and configuration management facilities.  ALM solutions have the ability to support the needs of multiple groups of geographically-distributed developers.  Business process automation services, designated to automate and enforce on-going service delivery processes throughout enterprise organizations, is a vital component of ALM solutions.  Within  those groups of geographically-distributed developers, the product continues to reveal the magnitude of its talents since it:   targets permission-based assignment and enforcement services, caters to role-based interfaces which allows support for developers, software engineers, project managers, IT specialists, etc, delivers enterprise application inventory-management services, oversees and coordinates  large software inventories and configurations, guards user access, manages differing versions of application code,  supports the existence of concurrent development projects, coordinates a diversity of release management facilities. 
Mike Miranda is a writer concerning topics ranging from Legacy modernization to Application life cycle management, data management, big data and more


Categories: DBA Blogs

Why the Python Data Model Could be for You

Pythian Group - Wed, 2015-12-23 08:31

 

A great feature of Python is its simplicity. This simplicity allows developers to start coding immediately with very little formality. Python is also very good at doing ‘quick and dirty’ tasks. The result of this ability to get up and running with Python so quickly leads to many programmers using Python without necessitating a deeper understanding of the language. In many cases this is fine, but a deeper dive has some nice rewards.

Named Tuples
Named Tuples are a nice lightweight construct that makes things easier when dealing with data that might otherwise be stored in a regular tuple or dict.

In a recent project we encountered named tuples when returning table schema from Hive Thrift API:

# Define the fieldschema tuple
FieldSchema = collections.namedtuple('FieldSchema', ['comment','type', 'name'])


# Create some fields for a table schema
customer_table=[] customer_name = FieldSchema('customer name', 'varchar(100)','name')
customer_addr = FieldSchema('address', 'varchar(100)','addr1')
customer_dob = FieldSchema('birthdate ', 'int','dob')
last_updated = FieldSchema('date last updated', 'datetime','last_updated')


# create two customer tables that have slightly different schemas
customer_table_a=[customer_name, customer_addr, customer_dob] customer_table_b = [customer_name, customer_addr, customer_dob, last_updated]

The Python Data Model
In Python, everything is an object and each object has an identity, a type and a value. Built in methods like id(obj) returns the object’s identity and type(obj) returns the object’s type. The identity of an object can never change. Objects can be compared with the “is” keyword. Example: X is Y.

Special or “dunder” methods.
Special or ‘Magic’ method names are always written with leading and trailing double underscores (i.e., __getitem__). So when you use the syntax myobj[key] it is really calling the __getitem__ special method behind the scenes. So the interpreter calls obj.__getitem(key) in order to evaluate myobj[key]. Because of the leading and trailing double underscores, these methods are sometimes referred to as ‘dunder’ (double-underscore) methods. These ‘dunder’ methods are invoked by special syntax. For example using the index [] on a collections object invokes the __getitem__(key) special method. Example:

my_list = ['a','b','c'] print my_list[2] print my_list.__getitem__(2)
> c
> c

Generally, these ‘dunder’ methods are meant to be used by the interpreter, and not by you. But the advantage of knowing something about special methods is that it makes class design more fluid. Here’s an example where we override the __sub__ method (subtract) so we can compare dataframes by subtracting one from another:

import pandas as pd
class HiveTable:
def __init__(self,tabledef):
self.df = pd.DataFrame(tabledef)

def __sub__(self, other):
oj = other.df.merge(self.df, on=”name”, how=”outer”)
df_diff = oj[(pd.isnull(oj[“type_x”])) | (pd.isnull(oj[“type_y”]))] return df_diff

# Create Hive Tables
t = HiveTable([customer_name, customer_addr, customer_dob])
t2 = HiveTable([customer_name, customer_addr, customer_dob, last_updated])

# Compare them by ‘subracting’ them using the “-” operator.
t-t2

>>

comment_x
type_x
name
comment_y
type_y
3
date customer last updated
datetime
last_updated
NaN
NaN

 

Conclusion
While Python is easy to get started with, it is also ‘deep all the way down’, continues to be interesting and reward with a deeper understanding. By implementing special methods in your own classes, your objects can behave more like the built in ones, allowing for ‘pythonic’ coding for your own classes.

refs:
https://docs.python.org/2/reference/datamodel.html,
Fluent Python by Luciano Ramalho

 

 

Categories: DBA Blogs

Github Repository

Bobby Durrett's DBA Blog - Tue, 2015-12-22 10:33

I am experimenting with Github. I have created a repository for my Oracle database related scripts. Here is my Github URL: https://github.com/bobbydurrett/dba.git

You can clone this repository locally if you have git installed using this command:

git clone https://github.com/bobbydurrett/dba.git

I’ve had challenges before when I write a blog post about a script and then revise the script later.  It seems weird to update the post with links to the new version.  So, I’m thinking of using github to expose the scripts that I want to share with the Oracle community and then I can update them over time and the version history will be visible.

Let me know if you have any questions or suggestions.  This is just my first attempt at using Github for this purpose.

Bobby

Categories: DBA Blogs

Log Buffer #454: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-12-18 07:39

As the festive season of holidays draws near, there is a spring in the blogosphere. This Log Buffer edition anticipates that and picks some lively blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.
  • It should be rare to see many TNS listener processes running on 1 OS. Some listeners are even Global Data Services listener.
  • The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g.
  • Monitoring is a critical and necessary function to ensure that systems and process are running properly. Good monitoring practice can also be proactive in identifying and resolving potential problems before they occur.
  • Creating Multiple Tables in a Single Transaction.

SQL Server:

  • Execute these stored procedures to backup all your database daily and your transaction logs several time daily.
  • At some point you are going to experience issues with database performance.  You need to have a process to follow and certain tasks that you should perform.
  • PowerShell is like any computer language: you must understand the paradigms, the constructs, and the way it is designed to work to get the most value from it.
  • With the introduction of SQL Server 2016 you now have a new way to encrypt columns called Always Encrypted.
  • Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure.

MySQL:

  • Ahead of Oracle’s OpenWorld conference in 2013, the company first began to talk about a major new release of its open-source MySQL database. Now two years later, development on MySQL 5.7 is compete and general availability is set for October 26.
  • POWER8 Accelerated CRC32 merged in MariaDB 10.1
  • used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used.
  • MySQL 5.8 Planning: C++11 and Native Partitioning.
  • Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host.  But what about when you need visibility into privileges from the other direction – which accounts can access specific data?

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Issues with Triggers in Cloudera Manager

Pythian Group - Thu, 2015-12-17 09:47

 

Triggers in Cloudera Manager is a great feature. You may want to set them up in order to monitor tons of available metrics using tsquery language. But the documentation says:

Important: Because triggers are a new and evolving feature, backward compatibility between releases is not guaranteed at this time.

That’s happened to me when I tried to setup a new trigger to monitor dfs_capacity_used_non_hdfs metric in HDFS in CDH5.4.2 (verified that issue is still there for CDH5.5.0).

I used the Create Trigger button on the status page of HDFS service to create a new trigger, changed the default name, entered a metric, a value and changed the action to Mark as bad. It then showed that everything is ok and trigger was not fired. So I pressed Create Trigger and was now sure that it would send me an alert once non-dfs usage goes to high.

Well everything need to be tested. So I created another trigger with a lower value that should make this trigger fire. But I realised that noting happened. Having checked various things I figured out that the issue was that CM had created a trigger with a variable name $SERVICENAME instead of actual value. If you ever see the issue in the servicemonitor logs, it would be something like “Could not parse trigger expression: …”

The fix seems to be simple: replace it with HDFS and save. If you just do that it will complain about a manually changed expression for the trigger that was created in the editor. To prevent that you may want to remove expressionEditorConfig section. But the more consistent way is to remove the trigger before using the documented way from the Edit Trigger page. I prefer to do so as we can’t be sure that CM doesn’t keep any metadata somewhere else.

Another issue however is that you don’t have a link to this page in CM. It would usually appear at Health tests, if it was created without an initial issue. To get this Edit Trigger page you may use your browser history or build it manually. Just go to any health test from the service and replace the tail of the URL with healthTestName=alarm%3A<trigger name>. If you used spaces in <trigger name> replace them with plus sign.

The triggers are awesome but you should create them manually before the editor is fixed. Also if you have created triggers using editor, you may want to review if they are actually working. You should see them in the Health tests list. You shouldn’t see any parsing errors in the servicemonitor logs.

 

Discover more about our expertise in Big Data & Hadoop.

Categories: DBA Blogs

Is Oracle Smart Flash Cache a “SPOF”?

Pythian Group - Thu, 2015-12-17 09:28

 

Oracle Smart Flash Cache (OSFC) is a nice feature that was introduced in Oracle 11g Release 2. As only recently I had a real use case for it, I looked into it with the main goal of determining if adding this additional caching layer would not introduce a new Single Point Of Failure (SPOF). This was a concern, because the solid-state cards/disks used for caching would normally have no redundancy to maximize the available space, and I couldn’t find what happens if any of the devices fail by looking in the documentation or My Oracle Support, so my decision was to test it!
The idea behind the OSFC is to provide a second level of “buffer cache” on solid-state devices that would have better response times compared to re-reading data blocks from spinning disks. When buffer cache runs out of space clean blocks (not “dirty”) would be evicted from it and written to the OSFC. The dirty blocks would be written by DBWR to the data files first, and only then would be copied to OSFC and evicted from the buffer cache. You can read more about what it is, how it works and how to configure OSFC in Oracle Database Administrator’s Guide for 11.2 and 12.1 and in this Oracle white paper “Oracle Database Smart Flash Cache“.

In my case the OSFC was considered for a database running on an Amazon AWS EC2 instance. We used EBS volumes for ASM disks for data files, and as EBS volumes are basically attached by networks behind the scenes, we wanted to remove that little bit of I/O latency by using the instance store (ephemeral SSDs) for the Smart Flash Cache. The additional benefit from using this would be reduction of IOPS done on the EBS volumes, and that’s a big deal, as it’s not that difficult to reach the IOPS thresholds on EBS volumes.

 

Configuration

I did the testing on my VirtualBox VM, which ran Oracle Linux 7.2 and Oracle Database 12.1.0.2 EE. In my case I simply added another VirtualBox disk, that I used for OSFC (reminder, not looking for performance testing here). The device was presented to the database via a separate ASM disk group named “FLASH”. Enabling the OCFS was done by setting the following parameters in the parameter file:

  • db_flash_cache_file=’+FLASH/flash.dat’
  • db_flash_cache_size=’8G’

The 1st surprise came when I bounced the database to enable the new settings, the DB didn’t start and an error was presented “ORA-00439: feature not enabled: Server Flash Cache”. Luckily, I found a known issue in a MOS note “Database Startup Failing With ORA-00439 After Enabling Flash Cache (Doc ID 1550735.1)”, and after forcefully installing two RPMs from OL5 (enterprise-release and redhat-release-5Server), the database came up.

 

Testing

The test I chose was a really simple. These are the preparation steps I did:

  • Reduced the buffer cache of the DB to approximately 700Mb.
  • Created table T1 of size ~1598Mb.
  • Set parameter _serial_direct_read=NEVER (to avoid direct path reads when scanning large tables. I really want to cache everything this time).

The next step was Full-scanning the table by running “select count(*) from T1”, and as I was also tracing the operation to see what was happening:

    • During the 1st execution I observed the following wait events (all multi-block reads from data files, as expected), however, I new the buffer cache was too small to fit all blocks, so a large volume of the blocks would end up in OSFC when they were flushed out from the buffer cache:
      WAIT #140182517664832: nam='db file scattered read' ela= 6057 file#=10 block#=90244 blocks=128 obj#=92736 tim=19152107066
      WAIT #140182517664832: nam='db file scattered read' ela= 4674 file#=10 block#=90372 blocks=128 obj#=92736 tim=19152113919
      WAIT #140182517664832: nam='db file scattered read' ela= 5486 file#=10 block#=90500 blocks=128 obj#=92736 tim=19152121510
      WAIT #140182517664832: nam='db file scattered read' ela= 4888 file#=10 block#=90628 blocks=128 obj#=92736 tim=19152129096
      WAIT #140182517664832: nam='db file scattered read' ela= 3754 file#=10 block#=90756 blocks=128 obj#=92736 tim=19152133997
      WAIT #140182517664832: nam='db file scattered read' ela= 8515 file#=10 block#=90884 blocks=124 obj#=92736 tim=19152143891
      WAIT #140182517664832: nam='db file scattered read' ela= 7177 file#=10 block#=91012 blocks=128 obj#=92736 tim=19152152344
      WAIT #140182517664832: nam='db file scattered read' ela= 6173 file#=10 block#=91140 blocks=128 obj#=92736 tim=19152161837
      
    • The 2nd execution of the query confirmed the reads from the OSFC:
      WAIT #140182517664832: nam='db flash cache single block physical read' ela= 989 p1=0 p2=0 p3=0 obj#=92736 tim=19288463835
      WAIT #140182517664832: nam='db file scattered read' ela= 931 file#=10 block#=176987 blocks=3 obj#=92736 tim=19288465203
      WAIT #140182517664832: nam='db flash cache single block physical read' ela= 589 p1=0 p2=0 p3=0 obj#=92736 tim=19288466044
      WAIT #140182517664832: nam='db file scattered read' ela= 2895 file#=10 block#=176991 blocks=3 obj#=92736 tim=19288469577
      WAIT #140182517664832: nam='db flash cache single block physical read' ela= 1582 p1=0 p2=0 p3=0 obj#=92736 tim=19288471506
      WAIT #140182517664832: nam='db file scattered read' ela= 1877 file#=10 block#=176995 blocks=3 obj#=92736 tim=19288473665
      WAIT #140182517664832: nam='db flash cache single block physical read' ela= 687 p1=0 p2=0 p3=0 obj#=92736 tim=19288474615
      

 

Crashing it?

Once the OSFC was in use I decided to “pull out the SSD” by removing the device /dev/asm-disk03-flash that I created using udev rules and that the FLASH disk group consisted of.
Once I did it, nothing happened, so I executed the query against the T1 table again, as it would access the data in OSFC. This is what I saw:

      1. The query didn’t fail, it completed normally. The OSFC was not used, and the query transparently fell back to the normal disk IOs.
      2. I/O errors for the removed disk were logged in the alert log, followed by messages about disabling of the Flash Cache. It didn’t crash the instance!
        Tue Dec 15 17:07:49 2015
        Errors in file /u01/app/oracle/diag/rdbms/lab12c/LAB12c/trace/LAB12c_ora_24987.trc:
        ORA-15025: could not open disk "/dev/asm-disk03-flash"
        ORA-27041: unable to open file
        Linux-x86_64 Error: 2: No such file or directory
        Additional information: 3
        Tue Dec 15 17:07:49 2015
        WARNING: Read Failed. group:2 disk:0 AU:8243 offset:1040384 size:8192
        path:Unknown disk
                 incarnation:0x0 synchronous result:'I/O error'
                 subsys:Unknown library krq:0x7f7ec93eaac8 bufp:0x8a366000 osderr1:0x0 osderr2:0x0
                 IO elapsed time: 0 usec Time waited on I/O: 0 usec
        WARNING: failed to read mirror side 1 of virtual extent 8191 logical extent 0 of file 256 in group [2.3848896167] from disk FLASH_0000  allocation unit 8243 reason error; if possible, will try another mirror side
        Tue Dec 15 17:07:49 2015
        Errors in file /u01/app/oracle/diag/rdbms/lab12c/LAB12c/trace/LAB12c_ora_24987.trc:
        ORA-15025: could not open disk "/dev/asm-disk03-flash"
        ORA-27041: unable to open file
        Linux-x86_64 Error: 2: No such file or directory
        Additional information: 3
        ORA-15081: failed to submit an I/O operation to a disk
        WARNING: Read Failed. group:2 disk:0 AU:8243 offset:1040384 size:8192
        path:Unknown disk
                 incarnation:0x0 synchronous result:'I/O error'
                 subsys:Unknown library krq:0x7f7ec93eaac8 bufp:0x8a366000 osderr1:0x0 osderr2:0x0
                 IO elapsed time: 0 usec Time waited on I/O: 0 usec
        WARNING: failed to read mirror side 1 of virtual extent 8191 logical extent 0 of file 256 in group [2.3848896167] from disk FLASH_0000  allocation unit 8243 reason error; if possible, will try another mirror side
        Tue Dec 15 17:07:49 2015
        Errors in file /u01/app/oracle/diag/rdbms/lab12c/LAB12c/trace/LAB12c_ora_24987.trc:
        ORA-15025: could not open disk "/dev/asm-disk03-flash"
        ORA-27041: unable to open file
        Linux-x86_64 Error: 2: No such file or directory
        Additional information: 3
        ORA-15081: failed to submit an I/O operation to a disk
        ORA-15081: failed to submit an I/O operation to a disk
        WARNING: Read Failed. group:2 disk:0 AU:8243 offset:1040384 size:8192
        path:Unknown disk
                 incarnation:0x0 synchronous result:'I/O error'
                 subsys:Unknown library krq:0x7f7ec93eaac8 bufp:0x8a366000 osderr1:0x0 osderr2:0x0
                 IO elapsed time: 0 usec Time waited on I/O: 0 usec
        WARNING: failed to read mirror side 1 of virtual extent 8191 logical extent 0 of file 256 in group [2.3848896167] from disk FLASH_0000  allocation unit 8243 reason error; if possible, will try another mirror side
        Tue Dec 15 17:07:49 2015
        Errors in file /u01/app/oracle/diag/rdbms/lab12c/LAB12c/trace/LAB12c_ora_24987.trc:
        ORA-15081: failed to submit an I/O operation to a disk
        ORA-15081: failed to submit an I/O operation to a disk
        ORA-15081: failed to submit an I/O operation to a disk
        Encounter unknown issue while accessing Flash Cache. Potentially a hardware issue
        Flash Cache: disabling started for file
        0
        
        Flash cache: future write-issues disabled
        Start disabling flash cache writes..
        Tue Dec 15 17:07:49 2015
        Flash cache: DBW0 stopping flash writes...
        Flash cache: DBW0 garbage-collecting for issued writes..
        Flash cache: DBW0 invalidating existing flash buffers..
        Flash cache: DBW0 done with write disabling. Checking other DBWs..
        Flash Cache file +FLASH/flash.dat (3, 0) closed by dbwr 0
        

 

Re-enabling the OSFC

Once the OSFC was automatically disabled I wanted to know if it can be re-enabled without bouncing the database. I added back the missing ASM disk, but it didn’t trigger the re-enabling of the OSFC automatically.
I had to set the db_flash_cache_size=’8G’ parameter again, and then the cache was re-enabled, which was also confirmed by a message in the alert log:

Tue Dec 15 17:09:46 2015
Dynamically re-enabling db_flash_cache_file 0
Tue Dec 15 17:09:46 2015
ALTER SYSTEM SET db_flash_cache_size=8G SCOPE=MEMORY;
Conclusions

Good news! It appears to be safe (and also logical) to configure Oracle Smart Flash Cache on non-redundant solid-state devices, as their failures don’t affect the availability of the database. However, you may experience a performance impact at the time the OSFC is disabled. I did the testing on 12.1.0.2 only, so this may behave differently in order versions.

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Recursion in Hive – Part 1

Pythian Group - Wed, 2015-12-16 15:12

 

I am going to start this new series of blog posts talking about code migration use cases. We will talk about migration from RDBMS to Hive keeping the simplicity and flexibility of a SQL approach.

The first case is about recursive SQL. In most of the situations for RDBMS it covered by recursive queries by using a “with” clause. Though, unfortunately it’s not yet supported in Hive;.

Let’s consider the following scenario. We have PRODUCTs and STATEs. STATEs make the forest of trees structure. The facts are combinations of PRODUCTs and STATEs which may have some data. Here are the simplified DDLs:

create table t_product (
  product string);

create table t_state (
  state string,
  next_state string);

create table t_big_data (
  product string,
  state string,
  data string);

The task is: for an input set of pairs (PRODUCT, STATE) try to find the next available STATE with data in fact table or return NULL.

The input data is stored in t_input table:

create table t_input (
  product string,
  state string);

We need to populate t_output table:

create table t_output (
  product string,
  state string,
  found_state string,
  data string);

Here are various methods to solve this: procedural approach with recursive functions, recursive SQL, multi-joins (in case we know the max depth).

The most reasonable for the modern RDBMS supporting recursive queries would be something like this:

insert into t_output(product, state, found_state, data)
with rec (product, state1, state2, data) as (
  select i.product, i.state, i.state, d.data
  from t_input i
  left join t_big_data d
    on d.product = i.product and d.state = i.state
  union all
  select r.product, r.state1, s.next_state, b.data
  from rec r
  join t_state s
    on s.state = r.state2
  left join t_big_data d
    on d.product = r.product and d.state = s.next_state
  where r.data is null
)
select product, state1 as state, state2 as found_state, data
from rec
where data is not null
   or state2 is null;

RDBMS can make a good execution plan for such queries especially if there are correct indexes on t_big_data table. We could do a multi-join approach in Hive but the cost for each big table scan it too high.

The trick we will use here is based on an observation that a tree structure is usually relatively small in comparison with a data table. So that we can easily “expand” a tree into flat denormalized structure: for each STATE from the initial table, we keep all STATES and path length on the way to root. For example, for the following simple tree:

STATE NEXT_STATE
----- ----------
S1    NULL
S2    S1
S3    S1
S4    S2

We would have:

STATE1 STATE2 LEVEL
------ ------ -----
S1     S1     0
S2     S2     0
S2     S1     1
S3     S3     0
S3     S1     1
S4     S4     0
S4     S2     1
S4     S1     2

Using the RDBMS recursive queries we would create this table as:

create table t_expand_state (
  state1 string,
  state2 string,
  lvl integer);

insert into t_expand_state (state1, state2, lvl)
with rec (state1, state2, lvl) as (
  select state, state, 0
  from t_state
  union all
  select r.state1, s.next_state, r.lvl + 1
  from rec r
  join t_state s
    on r.state2 = s.state
  where s.next_state is not null
)
select * from rec;

For Oracle DB we could do this with “connect by”:

select connect_by_root state state1, state as state2, level-1 lvl
from t_state
connect by prior next_state = state;

Having this t_expand_state table we can rewrite out query as:

insert into t_output(product, state, found_state, data)
select t.product, t.state,
       case when t.min_lvl_with_data is not null then t.state2 end,
       t.data
from (
  select i.product, i.state, s.state2, s.lvl, d.data,
         min(case when d.data is not null then lvl end)
           over(partition by i.product_id, i.state) min_lvl_with_data
  from t_input i
  join t_expand_state s
    on s.state1 = i.state
  left join t_big_data d
    on d.product = i.product and d.state = s.state2) t
  where t.lvl = t.min_lvl_with_data
     or (t.lvl = 0 and t.min_lvl_with_data is null);

This solution has its specific edge cases of inefficiency:
— big t_state that produce abnormal t_expand_state table;
— dense t_big_data table: so that during the query execution it has to keep a lot of extra-rows with “data” for states we don’t need;
— big t_input: joining it by all “next states” would inflate dataset.
But for practical use t_input is usually relatively small and there isn’t much overhead for getting extra-data for the next states. Another advantage is that we scan t_big_data only once.

To reach our goal the only task left is: how to build t_expand_state in Hive without recursion? Well we surely may consider multi-joins once again, but my choice is to use: UDTF.

In order to make recursion more natural I implemented this function using Scala. In the ExpandTreeUDTF we store tree structure in a mutable map during the “process” method call. After that it expands this map using memoization.

class ExpandTree2UDTF extends GenericUDTF {
  var inputOIs: Array[PrimitiveObjectInspector] = null
  val tree: collection.mutable.Map[String,Option[String]] = collection.mutable.Map()

  override def initialize(args: Array[ObjectInspector]): StructObjectInspector = {
    inputOIs = args.map{_.asInstanceOf[PrimitiveObjectInspector]}
    val fieldNames = java.util.Arrays.asList("id", "ancestor", "level")
    val fieldOI = primitive.PrimitiveObjectInspectorFactory.javaStringObjectInspector.asInstanceOf[ObjectInspector]
    val fieldOIs = java.util.Arrays.asList(fieldOI, fieldOI, fieldOI)
    ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
  }

  def process(record: Array[Object]) {
    val id = inputOIs(0).getPrimitiveJavaObject(record(0)).asInstanceOf[String]
    val parent = Option(inputOIs(1).getPrimitiveJavaObject(record(1)).asInstanceOf[String])
    tree += ( id -> parent )
  }

  def close {
    val expandTree = collection.mutable.Map[String,List[String]]()
    def calculateAncestors(id: String): List[String] =
      tree(id) match { case Some(parent) => id :: getAncestors(parent) ; case None => List(id) }
    def getAncestors(id: String) = expandTree.getOrElseUpdate(id, calculateAncestors(id))
    tree.keys.foreach{ id => getAncestors(id).zipWithIndex.foreach{ case(ancestor,level) => forward(Array(id, ancestor, level)) } }
  }
}

Having this we may compile it to jar, add it to Hive, create function and use it to build t_expand_state table.

create function expand_tree as 'com.pythian.nikotin.scala.ExpandTreeUDTF';

insert ovewrite table t_expand_state (state1, state2, lvl)
select expand_tree(state, next_state) from t_state;

 

 

Categories: DBA Blogs

ASM Internals: Tracking Down Failed ASM Reads

Pythian Group - Wed, 2015-12-16 14:50

On a live customer system, we’ve encountered repeated incidents of errors such as the following:

WARNING: Read Failed. group:1 disk:3 AU:86753 offset:524288 size:262144

Since Oracle doesn’t tell us what exactly is failing here, some research was in order. There’s a few posts out there about mapping ASM allocation units (AU) to database extents. But I felt that some of them weren’t entirely clear on what is being done, how and why. This prompted me to do some digging of my own.

This is our starting point. We know that:

  • The error happened on RAC instance 1 (since it was logged in the alert log of said instance).
  • The ASM disk group number is 1.
  • The ASM disk number is 3.
  • The AU number is 86753.
  • We can’t read that AU.
  • Database version is 11.2.0.4 on Linux.
  • ASM disk group redundancy is external.

We can further tell, that the failed read was at byte offset 524288 (which is 512KB) into the AU, and it was a multi-block read of 32 blocks (262144 / 8192). Thus it was likely a full table scan.

Disclaimer: what follows next is undocumented, and the usual disclaimers apply: check with Oracle support before running any of this against your production system.

In an ASM instance, Oracle exposes the ASM AU map in the fixed table X$KFFXP. We can query that to get some additional details, using the information we already have:

select inst_id, group_kffxp, number_kffxp, pxn_kffxp  
  from x$kffxp 
 where group_kffxp=1 
   and disk_kffxp=3 
   and au_kffxp=86753;

   INST_ID GROUP_KFFXP NUMBER_KFFXP  PXN_KFFXP
---------- ----------- ------------ ----------
         1           1          287       5526

 


Note: you have to run this in an ASM instance. On a database instance, the table doesn’t contain any rows (on the current version that I tested this on).

The columns in this table aren’t officially documented, but my own testing confirms that the information that can be found on google is fairly reliable in the current versions. What we used here is:

  • GROUP_KFFXP – the ASM disk group number, 1 in our case.
  • DISK_KFFXP – the ASM disk number, 3.
  • AU_KFFXP – the AU number, 86753.

The view now tells us the first two pieces of the puzzle that we need to know:

  • NUMBER_KFFXP – the ASM file number (not to be confused with the Oracle data file number).
  • PXN_KFFXP – the physical extent number in that file.

Armed with this information, we can now determine the details of the file that’s experiencing read errors:

set lines 200 pages 999
col dg for a12
col name for a20
col fname for a40
select t.name,
       substr(f.name, instr(f.name,'/',-1) + 1) as fname, 
       a.file_number, f.file# as DBFILE#,
       f.bytes/1024/1024 as file_mb
  from v$datafile f, v$tablespace t, v$asm_diskgroup g, 
       v$asm_alias a, v$asm_file af
 where g.name(+) = substr(f.name,2,instr(f.name,'/')-2)
   and a.name(+) = upper(substr(f.name, instr(f.name,'/',-1) + 1))
   and a.file_number = af.file_number
   and a.group_number = af.group_number
   and f.ts# = t.ts#
   and af.file_number = 287
/

NAME                 FNAME               FILE_NUMBER DBFILE#    FILE_MB
-------------------- ------------------- ----------- ---------- ----------
USERS                users.287.795706011         287          4      11895

We can see that the file is a part of the USERS table-space, and has a data file ID of 4.

Let’s double check our environment:

select allocation_unit_size from v$asm_diskgroup where group_number=1;

ALLOCATION_UNIT_SIZE
--------------------
             1048576

select block_size from dba_tablespaces where tablespace_name='USERS';

BLOCK_SIZE
----------
      8192

Now we have all that we need to get the final piece of our puzzle. We can use the following formula to calculate the position of the extent in the file, and from there, hit DBA_EXTENTS to see what that is.


[ AU_SIZE ] * [ PXN ] / [ BLOCK_SIZE ]

In our case, that becomes the following query:

select owner, segment_name, segment_type 
  from dba_extents 
 where file_id = 4 
   and 1048576 * 5526 / 8192 between block_id and block_id + blocks -1;

OWNER                          SEGMENT_NAME                    SEGMENT_TYPE
------------------------------ ------------------------------- ------------------
FOO                            CUSTOMER_OLD                    TABLE

We can also confirm that our result is correct by attempting to read it (note we are forcing a full scan to make sure we’re actually reading the table segment):

select /*+ full(t) */ count(*) from FOO.CUSTOMER_OLD t
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 741
Session ID: 695 Serial number: 40797

And sure enough, we see our familiar error message in the alert log instantly:

Thu Dec 10 04:24:23 2015
WARNING: Read Failed. group:1 disk:3 AU:86753 offset:524288 size:262144
ERROR: unrecoverable error ORA-15188 raised in ASM I/O path; terminating process 741 

We found the affected segment, and can now proceed with the usual recovery scenarios that are available to us. In this particular case, the table can likely be dropped as it was a backup.

Nonetheless, it is quite clear that the underlying disk (disk number 3 in group number 1) is faulty and must be replaced. There is one more thing, though, that we need to be mindful of. In order to replace the disk, Oracle has to be able to read all the allocated AUs on that disk as part of the re-balance process that is triggered when dropping/adding disks.

How do we tell if there aren’t any other segments that can’t be read? We’d have to be able to retrieve a list of all extents that are located on the disk in question. Of course, we can simply go for it, and let the drop/re-balance operation fail, which would also tell us that there are additional areas with problems on that disk. Since this is production, I prefer to be in the know instead of running something blindly. Additionally, you may hit one error during the re-balance, correct that, re-try and then hit another one. Rinse and repeat. Doesn’t sound too comforting, does it? So let’s see how we can get that information together.

There is but one problem we need to solve first. The data that we need is not available in the same place:

  1. X$KFFXP is only available on an ASM instance.
  2. DBA_EXTENTS is only available on a database instance.

I opted to go for the external table approach, and pull the data out of ASM first by creating the file /tmp/asm_map.sql with these contents:

set echo off
set feedback off
set termout off
set pages 0
spool /tmp/asm_map.txt
select x.number_kffxp || ',' || x.pxn_kffxp as data
  from x$kffxp x
 where x.group_kffxp=1
   and x.disk_kffxp=3
   and x.number_kffxp &gt; 255
/
spool off

Again, we are specifying the group number from our error message (GROUP_KFFXP=1) and the problematic disk (DISK_KFFXP=3).

Execute that script while connected to your ASM instance. Beware, if you have huge LUNs, this may write a lot of data. You may want to relocate the file to an alternate location. Again, please verify with Oracle support before running this against your production database, as with anything that involves underscore parameters, or x$ tables.

Next, switch to the database instance, and run the following:

create directory tmp_asm as '/tmp'
/
create table asm_map
(
  asm_file number, 
  asm_pxn number
)
organization external 
(
  type oracle_loader 
  default directory tmp_asm
  access parameters
  (
    records delimited by newline 
    fields terminated by ','
  )
  location ( 'asm_map.txt' )
)
/

Ensure that the data is properly readable:

select * from asm_map where rownum &lt; 10
/
  ASM_FILE    ASM_PXN
---------- ----------
       256          4
       256          7
       256         21
       256         28
       256         35
       256         49
       256         52
       256         75
       256         88

9 rows selected.

Now we can join into v$datafile and dba_extents to get the actual data we’re after. Let’s first build a list of table spaces and data files that are stored on this disk:

col ts_name for a30
col fname for a50
set lines 200 pages 999
select unique t.name as TS_NAME,
       substr(f.name, instr(f.name,'/',-1) + 1) as fname, a.file_number, f.file# as DBFILE#
  from v$datafile f, v$tablespace t, v$asm_diskgroup g, v$asm_alias a, v$asm_file af, ( select distinct asm_file from asm_map ) m
 where g.name(+) = substr(f.name,2,instr(f.name,'/')-2)
   and a.name(+) = upper(substr(f.name, instr(f.name,'/',-1) + 1))
   and a.file_number = af.file_number
   and a.group_number = af.group_number
   and f.ts# = t.ts#
   and af.file_number = m.asm_file
 order by 1,2
/

Now let’s expand that to also include dba_extents. I am creating a copy of the contents of dba_extents, which is known to often not perform in an optimal fashion, particularly on large databases. Otherwise the query may take an extremely long time. This extra step is particularly helpful and yields more benefit if you want to repeatedly query the data in dba_extents, which an exercise like this is a good example of.

create table tmp_extents
tablespace users
as 
select * from dba_extents
/

And now we’re ready to get the list of all segments that would be affected by problems on this one disk. This query gives us a list of everything stored on that disk:

col ts_name for a30
col obj for a100
set lines 200 pages 999
col segment_type for a18
set lines 200 pages 999
select unique t.name as TS_NAME, e.owner || '.' || e.segment_name as obj, e.segment_type,
       a.file_number, f.file# as DBFILE#
  from v$datafile f, v$tablespace t, v$asm_diskgroup g, v$asm_alias a, v$asm_file af, asm_map m, tmp_extents e
 where g.name(+) = substr(f.name,2,instr(f.name,'/')-2)
   and a.name(+) = upper(substr(f.name, instr(f.name,'/',-1) + 1))
   and a.file_number = af.file_number
   and a.group_number = af.group_number
   and f.ts# = t.ts#
   and af.file_number = m.asm_file
   and f.file# = e.file_id
   and t.name = e.tablespace_name
   and g.allocation_unit_size * m.asm_pxn / f.block_size between e.block_id and e.block_id + e.blocks -1
 order by 1,3,2
/

Now with this information, you can proceed to verify if any other segments exist which are unreadable and located on defective sectors:

  • Tables can be full scanned.
  • Indexes can either be rebuilt online, or also read with a fast full scan plan.
  • Lobs can be read with a small PL/SQL block.
  • Clusters should be okay as well if the contained tables are scanned. as that will read the respective blocks.
  • Partitioned tables and indexes can be treated analogous to their non-partitioned counterparts.
  • If undo segments are affected and can’t be read, you may want to involve Oracle support at this point.

By doing that, you can ensure that any potential problems can be detected before the applications or end users are affected by it, and if you don’t detect any other problems, you can feel fairly safe when swapping out the disk that you won’t be hit by any unexpected errors.

Once we are done, let’s not forget to clean up:

drop table tmp_extents
/
drop table asm_map
/
drop directory tmp_asm
/

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

What is Big Data and Do You Really Need it?

Pythian Group - Wed, 2015-12-16 12:09

Enhancing efficiency and cost-effectiveness for any company on a data-driven path is imperative to survival in the modern business world. For those who are willing to push the envelope and evolve from a defensive operation into a more transformative competitor, understanding what Big Data is, and if you really need it, is essential.

In the following on-demand webinar, Pythian CTO, Alex Gorbachev, provides an in-depth guide to help you better understand what exactly big data is, it’s benefits and use cases, and how to determine whether or not your company needs to implement a big data project.

This webinar is essential if you’re planning or thinking about completing a big data initiative in 2016 or simply trying to answer the question, “what is big data”? Download this webinar to watch now!

Categories: DBA Blogs

Trace Files -- 10b : More DML Tracing

Hemant K Chitale - Tue, 2015-12-15 08:30
In the previous post, I demonstrated what a trace of UPDATE or DELETE would show (or not show).  For example, although Index blocks need to be updated, we cannot identify how many Index blocks were updated.

Let's now look at an INSERT on an empty table and then gradually populate it with single row inserts.

SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table small_insert (id_column number, data_column varchar2(25)) tablespace users;

Table created.

SQL> select blocks from user_segments where segment_name = 'SMALL_INSERT';

BLOCKS
----------
8

SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

SQL> insert into small_insert select 1, 'Row ONE' from dual;

1 row created.

SQL>


SQL ID: fphypk50c6rtw Plan Hash: 1388734953

insert into small_insert select 1, 'Row ONE' from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.15 0 1 0 0
Execute 1 0.00 0.00 0 2 30 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.15 0 3 30 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=0 pw=0 time=592 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=6 us cost=2 size=0 card=1)


I started with a table with a precreated segment (else the first row INSERT would have had to create the segment).  That count of 30 blocks in current mode is a suprise, isn't it ?  Row Source Operations statistics do not tell us why 30 blocks or which 30 blocks (actually buffers in the buffer cache).
This is from the trace file (not the "cu=30" for the EXEC) :
PARSING IN CURSOR #139944607436728 len=54 dep=0 uid=87 oct=2 lid=87 tim=1450187439148096 hv=1086545724 ad='7f927750' sqlid='fphypk50c6rtw'
insert into small_insert select 1, 'Row ONE' from dual
END OF STMT
PARSE #139944607436728:c=27996,e=331306,p=0,cr=427,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1450187439148091
EXEC #139944607436728:c=1000,e=628,p=0,cr=2,cu=30,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1450187439148770
STAT #139944607436728 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=2 pr=0 pw=0 time=592 us)'
STAT #139944607436728 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=6 us cost=2 size=0 card=1)'
WAIT #139944607436728: nam='log file sync' ela= 1652 buffer#=1260 sync scn=4725627 p3=0 obj#=-1 tim=1450187439150552
WAIT #139944607436728: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1450187439150617

Could the 30 blocks be some maintenance operations ?  I leave it to you to conduct your own experiments.  Maybe you could post your findings and observations.
Remember : I have used aggregate=NO and sys=NO in my tkprof calls.
Note that there was no disk read.  The initial table creation with a segment seems to have created the first extent with blocks in the buffer cache (when I do a block dump later, I can confirm that 5 blocks after the segment header are formatted).

Let me do another INSERT.

SQL> insert into small_insert select 2, 'Row TWO' from dual;

1 row created.

SQL>


SQL ID: 9r6zkjh90yvfy Plan Hash: 1388734953

insert into small_insert select 2, 'Row TWO' from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=118 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=6 us cost=2 size=0 card=1)


I still see 3 blocks in current get mode.  It is quite likely that this second row went into the same block as the first row yet the same block has to be 're-got' in current mode.  Also, an Undo segment block has to be updated.  Note, however, the consistent get also that had to be done.  If we could enable more detailed tracing (can we ?) we might be able to identify which block (buffer in the buffer cache) was in consistent get and which were in current get.

What if I have an index on the table ?

SQL> create index small_insert_ndx on small_insert(id_column);

Index created.

SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

SQL> insert into small_insert select 3, 'Row THREE' from dual;

1 row created.

SQL>


SQL ID: cx3ubh9c2r80m Plan Hash: 1388734953

insert into small_insert select 3, 'Row THREE' from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 6 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=223 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=7 us cost=2 size=0 card=1)

Now we have 6 blocks in current mode.

SQL> insert into small_insert select 4, 'Row FOUR' from dual;

1 row created.

SQL>

insert into small_insert select 4, 'Row FOUR' from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 5 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=206 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=9 us cost=2 size=0 card=1)

So, with an index present, the current get overhead is greater. Obviously, Oracle has to expend more effort to retrieve and update the index  block.

Here, this table with 4 rows has only 1 table block and 1 index (root) block being used.  The same block is updated for each fresh INSERT, and each call requires a fresh current get.  This means that there will be CPU overhead in repeatedly reading the (same !) block for each fresh INSERT.
Caveat : All 4 rows inserted are inserted by the same session, so they went into the same block.  Had I used different sessions for the 4 rows they may have gone into 2 or more blocks because of the way in which ASSM splits INSERT calls across different blocks.
(Note : A GATHER_TABLE_STATS would show a block count of 5 (not 1, not 8) because the first 5 blocks are formatted, as I can confirm with a block dump).
.
.
.


Categories: DBA Blogs