Skip navigation.

Pythian Group

Syndicate content
Love Your Data
Updated: 7 hours 49 min ago

Advanced Compression Option Caveat in Oracle 12c

Tue, 2015-11-24 12:18

 

Oracle 12c introduced a new capability to move a partition online, without any interruptions to DML happening at the same time. But, there’s a catch. So far we’ve been able to use basic table compression without having to worry about any extra licensing – it was just a plain EE feature.

If you are planning to use the online partition move functionality, carefully check if you’re not using basic compression anywhere. For example:

create tablespace data datafile '+DATA' size 1g
/

create user foo identified by bar
default tablespace data
quota unlimited on data
/

grant create session, create table to foo
/

connect foo/bar

create table test (x int, y varchar2(20))
partition by range (x)
(
partition p1 values less than (100) tablespace data compress,
partition p2 values less than (200) tablespace data,
partition p3 values less than (300) tablespace data
)
/

So we now have this, and our licensing is still as we know it:

select partition_name, compression, compress_for from user_tab_partitions
/
PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
P1 ENABLED BASIC
P2 DISABLED
P3 DISABLED

We can use the new feature on partition p3:

alter table test move partition p3
online
/

Or, we can use the traditional means to compress the partition p2:

alter table test move partition p2
compress
/

But as soon as we do this move “online”, we are required to purchase the Advanced Compression Option:

alter table test move partition p2
compress
online
/

And, even sneakier:
alter table test move partition p1
online
/

Notice how partition p1 – which was previously compressed – also was online moved to a compressed format:

select partition_name, compression, compress_for from user_tab_partitions
/

PARTITION_NAME COMPRESS COMPRESS_FOR
—————————— ——– ——————————
P1 ENABLED BASIC
P2 ENABLED BASIC
P3 DISABLED

 

And that, therefore, required the Advanced Compression Option.

Also note that the usage of this is not caught by dba_feature_usage_statistics (tested on 12.1.0.2):

select name, currently_used from dba_feature_usage_statistics where lower(name) like '%compress%';

NAME CURRE
—————————————————————- —–
Oracle Advanced Network Compression Service FALSE
Backup ZLIB Compression FALSE
Backup BZIP2 Compression FALSE
Backup BASIC Compression FALSE
Backup LOW Compression FALSE
Backup MEDIUM Compression FALSE
Backup HIGH Compression FALSE
Segment Maintenance Online Compress FALSE
Compression Advisor FALSE
SecureFile Compression (user) FALSE
SecureFile Compression (system) FALSE
HeapCompression FALSE
Advanced Index Compression FALSE
Hybrid Columnar Compression FALSE
Hybrid Columnar Compression Row Level Locking FALSE

15 rows selected.

I also tried to bounce the database and the data wasn’t updated in my tests. I would’ve expected this to show up under “Segment Maintenance Online Compress”, but in my tests, it did not.

This feature restriction isn’t documented anywhere in the official product documentation – at least not that I could find. The only place where I could find this information was in this Oracle document.

 

Discover more about our experience in the world of Oracle.

Categories: DBA Blogs

Implementing Fuzzy Search in SQL Server – Part 2: Levenshtein Distance

Mon, 2015-11-23 15:18

 

The Levenshtein Distance, as discussed in my last post, is a way to measure how far two strings are located from each other. There are several T-SQL implementations of this functionality, as well as many compile versions. In addition, the MDS library in SQL Server has a Similarity function which uses the Levenshtein Distance to find how similar two words are.

In this post, I’ve done a simple comparison of performance using a C# CLR implementation of Levenshtein Distance (The code is from the Wiki), and a well written T-SQL implementation from Arnold Fribble.

As many of you might expect, the C# implementation is much quicker. Needing only 2504 ms to run through dictionary table of 203,118 words. The T-SQL implementation took 42718 ms for the same work.

A comparison of two ways to implement the Levenshtein Distance

Levenshtein Distance Comparison

 

Levenshtein Distance CLR

To implement the Levenshtein Distance CLR, run this SQL Script

 

Levenshtein Distance T-SQL

To implement the Levenshtein Distance in T-SQL, run the below code. Please note that this function has a cut-off value (@d) where it simply gives up and returns -1.


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION edit_distance_within(@s nvarchar(4000), @t nvarchar(4000), @d int)
RETURNS int
AS
BEGIN
DECLARE @sl int, @tl int, @i int, @j int, @sc nchar, @c int, @c1 int,
@cv0 nvarchar(4000), @cv1 nvarchar(4000), @cmin int
SELECT @sl = LEN(@s), @tl = LEN(@t), @cv1 = ”, @j = 1, @i = 1, @c = 0
WHILE @j <= @tl
SELECT @cv1 = @cv1 + NCHAR(@j), @j = @j + 1
WHILE @i <= @sl
BEGIN
SELECT @sc = SUBSTRING(@s, @i, 1), @c1 = @i, @c = @i, @cv0 = ”, @j = 1, @cmin = 4000
WHILE @j @c1 SET @c = @c1
SET @c1 = UNICODE(SUBSTRING(@cv1, @j, 1)) + 1
IF @c > @c1 SET @c = @c1
IF @c @d BREAK
SELECT @cv1 = @cv0, @i = @i + 1
END
RETURN CASE WHEN @cmin <= @d AND @c <= @d THEN @c ELSE -1 END
END
GO

 

Discover more about our expertise in SQL Server

Categories: DBA Blogs

ORA-10173: Dynamic Sampling Time-Out Error

Mon, 2015-11-23 15:06

 

Recently on a heavily used and freshly upgraded 12.1.0.2 ware-house type database, we started seeing lots of ORA-10173 dumped into the alert log. The information out there on this error is somewhat sparse, and it is often linked to Tuning Advisor functionality. Since we’re not running that advisor on this database, a little digging was in order.

What I always do as a first step if I am confronted with an error where I am not certain why and exactly where it is raised, is to set up an error trap. In Oracle, this can be done by setting an errorstack event, like this:

alter session set events '10173 trace name errorstack level 1';

Please note: whenever possible you’d want to set this on the smallest scope possible – starting with the session level, eventually in a login trigger for multiple sessions, and only when all that fails, on the system level using the alter system statement.

Once you have trapped an occurrence or two, you can disable it again by running:
alter session set events '10173 trace name errorstack off';

Upon activating this event, the next time around when this exception is raised Oracle will write a trace file to the diagnostics repository. Two two most prevalent pieces of information in the trace file are the current SQL query:

----- Current SQL Statement for this session (sql_id=anbp9r5n8ysu6) -----
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM (SELECT "INV"."CASE_UPC" "ITEM_1","INV"."WHS_NBR" "ITEM_2",SUBSTR("INV"."CASE_UPC",3,11) "ITEM_3" FROM "XX"."XX_WHS_INV" "INV","XX"."XX_CASE_DIM" "CD2","XX"."XX_WHS_DIM" "WD" WHERE "WD"."WHS_DESC" LIKE '%PEY%' AND "CD2"."WHS_NBR"="INV"."WHS_NBR" AND "CD2"."CASE_UPC"="INV"."CASE_UPC" AND "INV"."WHS_NBR"="WD"."WHS_NBR" GROUP BY "INV"."CASE_UPC","INV"."WHS_NBR",SUBSTR("INV"."CASE_UPC",3,11)) "VW_DIS_13") innerQuery

And the stack trace, which looks something like this:

skdstdst()+29 call kgdsdst() 7FFFA592F860 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
ksedst()+112 call skdstdst() 7FFFA592F860 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbkedDefDump()+1153 call ksedst() 000000000 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
ksedmp()+26 call dbkedDefDump() 000000001 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbkdaKsdActDriver() call ksedmp() 000000001 ? 000000000 ?
+880 7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F482FDE8618 ? 7FFFA5932830 ?
)+319 7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
... ( rest omitted for clarity and we don't need the stack for the purpose of this post )

For my case, the interesting bit was the query that caused the error to be raised. This query provides us with some hints about what’s going on:

– We can see the DS_SVC comment at the beginning of the query
– We can see lots of hints, the more interesting ones being dynamic_sampling(0) and result_cache(snapshot=3600)

A little bit of research led to a new feature introduced in 12c: Dynamic Statistics (or Adaptive Statistics). This is an enhancement to dynamic sampling, where the optimizer can automatically choose to gather more statistics as part of the parse phase. The sampling is controlled internally with a time out, and if that time out is hit, ORA-10173 is raised to the alert log.

This means that these errors are generally safe to ignore, as they are raised purely internally and your application won’t see these exceptions. Your query didn’t, and won’t fail. However, your query may well be spending more time parsing and sampling data than what makes sense. My colleague Slava has already blogged about such a case here: Performance Problems with Dynamic Statistics in Oracle 12c and there are other documented cases.

The feature can be controlled through the optimizer_dynamic_sampling parameter. If it’s set to the default value of 2, the optimizer may choose to raise that temporarily to 11 which enables these new dynamic features. You can tell by looking at an execution plan of a query using dbms_xplan.display_cursor for example, and looking at the notes section:

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

As always, to determine whether or not this feature is helpful or harmful to your applications: benchmark it. You probably don’t want to turn it off system-wide, but it may make a lot of sense to disable it for certain sessions, or certain queries in your application. The ORA-10173 you may see in the alert log however is no reason to panic, if your application is not negatively impacted. If you are mostly running queries that take minutes or longer to execute, spending a couple seconds gathering more statistics may not impact you. If on the other hand your queries are fast, and Oracle spends more time parsing and sampling than it actually takes to execute the query, or you are seeing loads and loads of ORA-10173, you may want to take action. Alternatively you may also resort to setting a _fix_control as mentioned in Slava’s post – but as always, double-check with Oracle support if setting it has the desired effect on your exact version of Oracle.

In essence, the ORA-10173 indicates wasted work, because Oracle is spending time gathering data, hits the timeout and then throws the partial data away since it’s incomplete. This certainly isn’t optimal, but again, your mileage may vary if this really impacts your applications. In our case, we are seeing thousands of them, and we have opened a case with Oracle, since obviously somewhere the optimizer keeps making the wrong choice. The bug is currently still with BDE but I shall keep you posted if anything develops.

Another thing worth noting is that Oracle stores the results of these dynamic sampling queries in the result cache, and marks them for expiration after a certain time has elapsed. This timeout that we are seeing with the result_cache(snapshot=3600) hint, comes from the hidden parameter _optimizer_ads_result_cache_life which defaults to 3600 in 12.1.0.2. This may also help in reducing the frequency of the dynamic sampling queries by increasing the lifetime for which they remain cached. But as always, before changing any hidden parameters, please consult with your local Oracle support representative!

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Implementing Fuzzy Search in SQL Server – Part 2: Levenshtein Distance

Mon, 2015-11-23 14:40

The Levenshtein Distance, as discussed in my last post, is a way to measure how far two strings are located from each other. There are several T-SQL implementations of this functionality, as well as many compiled versions. In addition, the MDS library in SQL Server has a Similarity function which uses the Levenshtein Distance to find how similar two words are.

In this post, I’ve done a simple comparison of performance using a C# CLR implementation of Levenshtein Distance (The code is from the Wiki), and a well written T-SQL implementation from Arnold Fribble.

As many of you might expect, the C# implementation is much quicker. Needing only 2504 ms to run through dictionary table of 203,118 words. The T-SQL implementation took 42718 ms for the same work.

A comparison of two ways to implement the Levenshtein Distance

Levenshtein Distance Comparison

 

Levenshtein Distance CLR

To implement the Levenshtein Distance CLR, run this SQL Script

 

Levenshtein Distance T-SQL

To implement the Levenshtein Distance in T-SQL, run the below code. Please note that this function has a cut-off value (@d) where it simply gives up and returns -1.

CREATE FUNCTION edit_distance_within(@s nvarchar(4000), @t nvarchar(4000), @d int)
RETURNS int
AS
BEGIN
  DECLARE @sl int, @tl int, @i int, @j int, @sc nchar, @c int, @c1 int,
    @cv0 nvarchar(4000), @cv1 nvarchar(4000), @cmin int
  SELECT @sl = LEN(@s), @tl = LEN(@t), @cv1 = '', @j = 1, @i = 1, @c = 0
  WHILE @j <= @tl
    SELECT @cv1 = @cv1 + NCHAR(@j), @j = @j + 1
  WHILE @i <= @sl
  BEGIN
    SELECT @sc = SUBSTRING(@s, @i, 1), @c1 = @i, @c = @i, @cv0 = '', @j = 1, @cmin = 4000
    WHILE @j <= @tl BEGIN SET @c = @c + 1 SET @c1 = @c1 - CASE WHEN @sc = SUBSTRING(@t, @j, 1) THEN 1 ELSE 0 END IF @c > @c1 SET @c = @c1
      SET @c1 = UNICODE(SUBSTRING(@cv1, @j, 1)) + 1
      IF @c > @c1 SET @c = @c1
      IF @c < @cmin SET @cmin = @c SELECT @cv0 = @cv0 + NCHAR(@c), @j = @j + 1 END IF @cmin > @d BREAK
    SELECT @cv1 = @cv0, @i = @i + 1
  END
  RETURN CASE WHEN @cmin <= @d AND @c <= @d THEN @c ELSE -1 END
END

Discover more about our expertise in SQL Server

Categories: DBA Blogs

What’s the Ratio of Your Marketing Budget to Cyber Security Budget?

Sun, 2015-11-22 19:45

This was a question asked over Twitter by @DanBarker to TalkTalk, the major British telco after they managed to lose a significant portion of its customers’ details, apparently through an SQL Injection attack by a 15 year old.

The question wasn’t answered, but the sooner companies realise that a security incident can wipe out a significant part of the brand’s goodwill, the more this ratio will adjust.

Here are three top tips to ensure you’re investing wisely in cyber security, and protecting your brand’s good name:

1. Keep everything patched and up to date – old databases have security holes that can lead to an attack. A new client of ours was running SQL Server 2002 and failed a pen-test in 30 minutes. But it doesn’t need to be that old to fail.

2. Audit and document everything. What data is where? Who or what has access? Do they need it? Are they still with the company? Not knowing what data might be lost was the major problem at the NSA post-Snowden. And within hours of the TalkTalk hack, the CEO said “I don’t know today whether all four million customers’ details have been stolen” (it was about 150,000 in the end, but by then the brand damage was done).

3. Check how employees and third party suppliers access your production environment, to make sure it’s from a safe, virus-free place. Can you see what they see? Do you know what they’re looking at?

Overall, to use Pythian’s tagline, just learn to “love your data”.

If your in-house team doesn’t have the skills and expertise to take care of these tasks, then find a company or contractor that does. The cost will be far less than a major security incident. And probably lower than your marketing budget too.

Categories: DBA Blogs

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

Fri, 2015-11-20 15:08

This Log Buffer Editions picks few blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege.
  • Lost SYSMAN password OEM CC 12gR5.
  • How Terminal Emulation Assists Easy Data Management.
  • Using EMCLI List Verb to Get Detailed Information of EM Targets.
  • How to change apex_public_user password in ORDS.

SQL Server:

  • When the connection between you and the the target host are multiple servers across the continent, the latency will drive crazy mad.
  • SQLCMD and Batch File magic.
  • Greg Larson walks through the GUI installation process for SQL Server 2016 and explore these new installation options.
  • A Single-Parameter Date Range in SQL Server Reporting Services.
  • Is SQL Server killing your application’s performance?

MySQL:

  • MariaDB Galera Cluster 10.0.22 and Connector updates.
  • Building MaxScale from source on CentOS 7.
  • Orchestrator & Pseudo-GTID for binlog reader failover.
  • InnoDB holepunch compression vs the filesystem in MariaDB 10.1.
  • Open-sourcing PinLater: An asynchronous job execution system.

 

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

Categories: DBA Blogs

Pythian Champions Gender Diversity in Tech

Wed, 2015-11-18 13:45

 

At Pythian, we embody and value diversity in every form—in our religion (or lack thereof), our geography, our culture, and of course the location we’ve chosen for our home.  We deeply believe that the best teams are diverse and we are committed to increasing our diversity in all these areas.

We’ve been doing time-zone diversity for over a decade and we’ll keep doing it—we’re actually pretty good at it. In support of time zone diversity, we’ve become an industry leader in the art of creating high-performance teams by blending people from all over the world regardless of religion, culture, country of origin or residence. But now it’s time for us to turn our attention to gender diversity.

And so, with this note and video, I am proud to announce the Pythia Program. We’re going to increase the number of brilliant women who work with us and we will take a leadership position in a very important conversation to encourage more women to enter STEM fields and the tech industry.

Inspired by Microsoft, Twitter, Pinterest and Google, Pythian is the first technology company headquartered in Canada to announce its gender stats: 35 percent of the company’s leadership team are women, 27 percent of our managers are women and only 9 percent of technical roles at Pythian are held by women. Pythian also claims a Pythia Index of 56 percent.

The Pythia Index is the ratio of people in a business or in a team that are women leaders or roll up to a woman leader, as a percentage of total headcount. At Pythian the Pythia Index is 56%, which means that 56% of Pythianites are themselves women leaders or report up the org chart to a woman. Conversely, 44% of Pythian employees have zero women leaders all the way up the org chart from them.

So how do we currently compare to industry leaders? According to the most recent published numbers, Microsoft reported that women comprise 29.1 percent of its workforce, but only 16.6 percent work in technical positions and just 23 percent hold leadership roles. Twitter said women fill 10 percent of its technical jobs, with 21 percent in leadership. And women Googlers account for 17 percent of the search giant’s tech jobs, while only 21 percent manage others. Industry averages among all surveyed major US tech firms are 15.6% of technical roles and 29.6% of leadership.*

With this announcement, I am challenging our business to surpass industry averages within no more than three years, and sooner if possible.

The Pythia Program is central to how we plan to realize the best possible talent mix. By acknowledging and strengthening our deliberate talent blending practices, we hope not only to garner interest from potential employees, but to encourage other businesses to set similar diversity goals. This is not a corporate social responsibility initiative, it’s good for business, and it’s good for the technology sector.

Under the Pythia program we will:

  • Continue to focus on eliminating any unconscious gender biases in our recruiting and management practices.
  • Actively promote Pythian as a diversity-focused company so we can get more than our fair share of the top female leaders and tech talent.
  • Help build our future pipeline of female leaders and tech talent by working with and supporting organizations who have programs to encourage more women to go into tech.

This project is very important to me personally and I’ll continue to blog on the subject in the future.

 

Discover more about the Pythia Program.

 

*Source: cnet.com

Categories: DBA Blogs

High “cursor: pin S wait on X” waits?

Tue, 2015-11-17 14:59

If your system meets any of the following criteria:

– Oracle 12.1.0.2.0 or higher
– Partitioned tables
– Parallel query heavily used
– Bind variables in use, as they should be

and, you’re seeing unusually high “cursor: pin S wait on X” waits, then you may want to know that this week, patch 21834574 was released.

To give you a little bit of background information; the issue is with a new code path introduced in Oracle version 12, which is related to some of the brand new infrastructure life-cycle management (ILM) functionality. This ILM feature – whether you are intentionally using any ILM functionality or not – causes delays when a new child cursor is created in the library cache. On a client’s system we have observed waits of up to 0.3 seconds.

In a nutshell, here’s what’s happening:

– A new parallelized query using bind variables is hard parsed.
– Oracle creates a new cursor, and adds a child cursor.
– Each of your PX slaves will then wait for the following event: “cursor: pin S wait on X”.
– The mutex being waited on is of type Cursor Pin at the location “kkslce [KKSCHLPIN2]”. This is normal and these waits can’t be fully avoided.

But what’s not normal is that the mutex is being waited on for a quarter of a second in each slave session.

Oracle has now released a patch which implements a new _fix_control which can be set to enable the fix once the patch is applied. As always, please consult with Oracle Support before applying this patch and setting this parameter, to make sure that you really are seeing this particular bug. There are others in the current versions of Oracle which share very similar symptoms, and the only way to be certain is to double-check with Oracle support or development.

Happy patching!

 

Discover more about our expertise in the world of Oracle

Categories: DBA Blogs

Comparing Schemas Between Hive Clusters

Fri, 2015-11-13 13:36

 

When running several different hive instances, we found the process of maintaining/confirming synchronization to be quite time consuming. While several tools made available by Cloudera and other frameworks do provide a visual interface for an individual instance of hive, there was no available tool for comparing across clusters. We came up with a useful way to compare hive schemas between clusters.

Our process contains 3 main steps:

  1. Fetch the schema from all hive environments, store the schema as a dictionary in a file.
  2. Create a class to perform comparisons leveraging python list, dict and pandas dataframe structures.
  3. Display the results/visualize the changes using a web interface (this approach uses python Flask).

 

Step 1- Fetch the Schema From all Environments
To fetch the schema we use the hive metastore api via the hive-thrift.py package.

First, we create a socket to connect to the hive thrift server.

transport = TSocket.TSocket(host, port)
transport = TTransport.TBufferedTransport(transport)
protocol = TBinaryProtocol.TBinaryProtocol(transport)

global metastore_client
metastore_client = ThriftHiveMetastore.Client(protocol)
transport.open()

Once that is done, it’s straightforward to get all of the tables in a db (there are lots of other great methods  that have been documented).
db = 'myDatabase'
tables = metastore_client.get_all_tables(db)

Once we get the tables, we can call the get_fields() method
for table in tables:
for field in metastore_client.get_fields(db, table):
# field is a named tuple FieldSchema(comment, type, name)
print field

Using the methods above, we retrieve all of the values and store them in a dictionary, locally in a text file, one file per environment.
{'myDatabase': {
'tbl_customer':
[
FieldSchema(comment=None, type=int, name='user_id'), FieldSchema(comment=None, type='string', name='first_name'), FieldSchema(comment=None, type='string', name='last_name')
],
'tbl_orders':
[
FieldSchema(comment=None, type=int, name='order_id'), FieldSchema(comment=None, type='string', name='item_id'), FieldSchema(comment=None, type='string', name='price')
],

etc..

Note that thrift gives us a tuple for the ddl, so for ease, we declare this named tuple locally.
FieldSchema = collections.namedtuple(‘FieldSchema’, [‘comment’,’type’, ‘name’])

 

Step 2 – Create Class to Compare Dictionaries

Now that we have a separate schema file for each hive instance, we can load these files into dictionaries and begin to compare them.

f_stage_dict = open('schema_files/stage_schema_thrift.out')
f_prod_dict = open('schema_files/prod_schema_thrift.out')
d_stage = eval(f_stage_dict.read())
d_prod = eval(f_prod_dict.read())

Create a class to compare the dictionaries. Comparisons are done leveraging a variety of nice python tools (lists, set comparison, pandas dataframes, etc..). More detail in the repo.

Class DictCompare:
def get_fields_by_table(self, db, table):...
# returns list of fields for tableA and tableB
def get_databases(self):...
# union of all dbs in all schemas A,B
def compare_fields(self, db, table)...
# returns tablediff tuple showing all field differences
def compare_dbs(self, db)...
#returns list of differences between dbs
tables_a = [table for table in self.dict_a[db]] tables_b = [table for table in self.dict_b[db]] db_diffs_a = set(tables_a).difference(set(tables_b))
db_diffs_b = set(tables_b).difference(set(tables_a))
.....

Instantiate the class by passing the path of the schema files, and the friendly names used in the config.json file.
dc = sc.DictCompare(schema_files_path,”dev”, “stage” )
dbs = dc.get_databases()

# show all of the databases..
print "DBS=",dbs

# get all diffs for a given database
compare_dict = dc.compare_dbs("my_db")

 

Step 3 Display the Results / Visualize the Changes

I used a quick and simple python flask website to display the differences. Note there is a form on the page that allows users to select the hive instances to compare.

@app.route('/home.html')
@app.route('/home')
def homepage():
form=EnvForm(csrf_enabled=False)
env_a = form.env_a.data # 'stage', for example
env_b = form.env_b.data # 'prod', for example
dc = sc.DictCompare(schema_files_path,env_a, env_b )
dbs = dc.get_databases()
return render_template("home.html", dbs=dbs, a_name =dc.a_name, b_name = dc.b_name,form =form )

Source code (open sourced under Apache2 license):

I Look forward to any comments or feedback.

 

Discover more about our expertise in Big Data and Infrastructure technologies. 

Categories: DBA Blogs

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

Fri, 2015-11-13 13:19

 

This Log Buffer Edition covers some of the niftiest blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • OBIEE 11g and Essbase – Faking Federation Using the GoURL.
  • You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration.
  • This is a short post to help out any “googlers” looking for an answer to why their 12.1.0.5 EM Cloud Control install is failing in the make phase with ins_calypso.mk.
  • A short video that Jonathan Lewis did at the OTN lounge at RMOUG a couple of years ago has just been posted on YouTube. It’s about the improvements that appear in histograms in 12c.
  • Changing the name of the server or load-balancing server handling your BI Publisher workload for OEM can be done with a single EM CLI command.

SQL Server:

  • Manoj Pandey was going through some sample Scripts provided by Microsoft SQL Server team on their site, and was checking the JSON Sample Queries procedures views and indexes.sql script file.
  • When you open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window In Power Pivot, click Get External Data, choose From Other Sources Choose Microsoft Analysis Services.
  • After the introduction of the StringStoresCompatibilityLevel property in SSAS dimension after SQL Server 2012, SSAS database designer may try to create a MOLAP dimension with more unique values than what is allowed in previous SQL Server versions.
  • After SQL Server 2012 is released, SSISDB provides stored procedures to create SSIS package executions. There is one problem though.
  • This is the fourth installment in a blog series. The previous entry is located here Based on the previous blogs in this series, you should have gotten your database hosted in WASD by now & secured access to your server.

MySQL:

  • db4free.net finally runs MySQL 5.7 which was released on October 21.
  • What Does The Universal Scalability Law Reveal About MySQL?
  • There are many dimensions by which a DBMS can be better for small data workloads: performance, efficiency, manageability, usability and availability.
  • The new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available.
  • Nginx is well-known for its ability to act as a reverse-proxy with small memory footprint. It usually sits in the front-end web tier to redirect connections to available backend services, provided these passed some health checks.

 

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

Categories: DBA Blogs

US-EU Safe Harbor Framework Invalidated – It’s time to Love Your Data!

Fri, 2015-11-13 12:43

Pythian has been closely following the developments in the EU relating to the invalidation of the US-EU Safe Harbor Framework.

On Friday, November 6, 2015, the European Commission issued guidance on the cross-border data transfer issues. This guidance spoke more to “alternative transfer tools” available to legitimize data flows to jurisdictions deemed “not adequate,” like the United States.

If you do transfer data and rely on Safe Harbor principles, we recommend you:

Keep Calm
Don’t rush to other transfer mechanisms that may turn out to be less than ideal.

Love your data!
Consider:
What personal data you are transferring outside the EU?
Where is it going?
What arrangements have you made to ensure that it is adequately protected?

Carry on and call Pythian
We can help you mitigate your risk of incidex* and prepare you for the pending EU regulation, General Data Protection Regulation (GDPR) slated to become law by end of 2015 and will mandate that all companies with EU customers follow the new rules wherever they’re based by 2018.

*Incidex: Today’s formula for total cost of ownership isn’t the traditional capex + opex + long-term expenses. Instead, there’s another more important cost component in today’s application landscape that Pythian refers to as “incidex”, or the cost of not investing in technologies that will secure and protect your revenue generating systems. If you redefine TCO as capex plus opex plus incidex, it’s a more holistic approach. And you can use the holistic TCO analysis to justify an investment in security technology.

Categories: DBA Blogs

Oracle ASM Rebalance – Turn it up. To 11?

Wed, 2015-11-11 14:01

 

If you’ve ever seen or heard of the movie This is Spinal Tap then you have likely heard the phrase Turn it up to 11.

Why bring this up?

When ASM was introduced as a method for configuring storage for Oracle, one of the features was the ability to rebalance the data across all disks when disks were added or replaced.  The value used to control how aggressively Oracle rebalances the disks is the REBALANCE POWER. And yes, the maximum value for rebalancing was 11, as an homage to the movie.

Here is an example of a command to only rebalance a disk group:

 alter diskgroup data rebalance power 11; 

That is rather straightforward, so why blog about it?

The reason is that the maximum value for REBALANCE POWER changed with Oracle 11.2.0.2, as per the documentation for the ASM_POWER_LIMIT parameter.

From 11.2.0.2, the maximum value is no longer 11, but 1024.

I’ve asked a number of DBA’s about this, and it seems that knowledge of the rebalance power limit is not really too well known.

Why does it matter?

Imagine that an 11.2.0.4 ASM diskgroup has had disks replaced, and the task took longer than expected.

Now you want to speed up the rebalance of the disk group as much as possible:

 alter diskgroup data rebalance power 11; 

Will that bit of SQL do the job?

On 10g that would be fine. But on an 11.2.0.4 database that would set the POWER limit to 1.07% of the maximum allowed value, having little effect on how aggressive Oracle would be in rebalancing the disks.

The correct SQL in this case would be:

 alter diskgroup data rebalance power 1024; 

The following is a short demonstration of REBALANCE POWER on 10.2.0.4, 11.2.0.2 and 12.1.0.2 databases.  These examples just confirm the documented maximum values for REBALANCE POWER.

 

SQL> select version from v$instance;
VERSION
-----------------
10.2.0.4.0

SQL> alter diskgroup ASM_COOKED_FS rebalance power 12;
alter diskgroup ASM_COOKED_FS rebalance power 12
                                              *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup ASM_COOKED_FS rebalance power 11;

Diskgroup altered.

################################################

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.2.0

SQL> alter diskgroup fra rebalance power 1025;
alter diskgroup fra rebalance power 1025
                                      *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup fra rebalance power 1024;

Diskgroup altered.

################################################

SQL> select version from v$instance;

VERSION
-----------------
12.1.0.2.0

SQL> alter diskgroup data rebalance power 1025;
alter diskgroup data rebalance power 1025
                                     *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup data rebalance power 1024;

Diskgroup altered.

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Oracle Upgrade Failures due to METHOD_OPT and XDBCONFIG

Wed, 2015-11-11 13:51
Background

I recently experienced a problem when upgrading an old Oracle 10.2.0.4 database to 11.2.0.4 that had no matches in a My Oracle Support (MOS) or Google search. The problem presented itself initially when upgrading as the following error was reported by the upgrade script:

ERROR at line 1:
ORA-20001: invalid column name or duplicate columns/column groups/expressions
in method_opt
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4

 

Initially, the problem was reported in the upgrade log file for the ORACLE_OCM schema which is not critical. However, it later caused the XDB component to become invalid and consequently other components that depend on XDB to also become invalid. The error reported when trying to validate XDB was:

Warning: XDB now invalid, could not find xdbconfig

 

Even if not upgrading, this error could be encountered when trying to install or re-install the XDB component in an 11g database. XDB is a mandatory component as of Oracle 12c but is optional with 11g and below. Hence, it’s possible to experience this same problem if you’re trying to add the XDB component to an 11g database that didn’t already have it.

 

“Warning: XDB now invalid, could not find xdbconfig”

Several MOS documents already exist describing the error “Warning: XDB now invalid, could not find xdbconfig”. Those include:

  • Utlrp.sql results to “Warning: XDB Now Invalid, Could Not Find Xdbconfig” (Doc ID 1631290.1)
  • XDB Invalid after Utlrp during Activation of Extended Datatypes (Doc ID 1667689.1)
  • XDB Invalid After utl32k.sql during activation of extended datatypes (Doc ID 1667684.1)

Unfortunately, none of those applied as either the cause or the solution to the problem I encountered. Either going through the XDB installation logs or simply manually running utlrp.sql shows that the xdbconfig is missing due to the “ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt” error.

For example:

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-11-09 11:36:22

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
DECLARE
*
ERROR at line 1:
ORA-20001: invalid column name or duplicate columns/column groups/expressions
in method_opt
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-11-09 11:36:23

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

Warning: XDB now invalid, could not find xdbconfig
ORDIM INVALID OBJECTS: CARTRIDGE - INVALID - PACKAGE BODY
ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - VIEW
ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - SYNONYM
ORDIM INVALID OBJECTS: SI_IMAGE_FRMT_FTRS - INVALID - SYNONYM
ORDIM INVALID OBJECTS: ORDUTIL - INVALID - PACKAGE BODY
ORDIM INVALID OBJECTS: ORDIMG_PKG - INVALID - PACKAGE BODY
ORDIM INVALID OBJECTS: ORDIMGEXTCODEC_PKG - INVALID - PACKAGE BODY
ORDIM INVALID OBJECTS: ORDX_FILE_SOURCE - INVALID - PACKAGE BODY
ORDIM INVALID OBJECTS: DICOM_IMAGE105_T - INVALID - TYPE
ORDIM INVALID OBJECTS: exifMetadata243_T - INVALID - TYPE
ORDIM INVALID OBJECTS: PATIENT_STUDY129_T - INVALID - TYPE
ORDIM INVALID OBJECTS: GENERAL_SERIES134_T - INVALID - TYPE
ORDIM INVALID OBJECTS: GENERAL_IMAGE154_T - INVALID - TYPE
ORDIM INVALID OBJECTS: TiffIfd244_T - INVALID - TYPE
ORDIM INVALID OBJECTS: ExifIfd245_T - INVALID - TYPE
ORDIM INVALID OBJECTS: GpsIfd246_T - INVALID - TYPE
ORDIM INVALID OBJECTS: CODE_SQ103_T - INVALID - TYPE
ORDIM INVALID OBJECTS: iptcMetadataType94_T - INVALID - TYPE
ORDIM INVALID OBJECTS: IMAGE_PIXEL163_T - INVALID - TYPE
ORDIM registered 0 XML schemas.
The following XML schemas are not registered:
http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0
http://xmlns.oracle.com/ord/dicom/anonymity_1_0
http://xmlns.oracle.com/ord/dicom/attributeTag_1_0
http://xmlns.oracle.com/ord/dicom/constraint_1_0
http://xmlns.oracle.com/ord/dicom/datatype_1_0
http://xmlns.oracle.com/ord/dicom/manifest_1_0
http://xmlns.oracle.com/ord/dicom/mapping_1_0
http://xmlns.oracle.com/ord/dicom/mddatatype_1_0
http://xmlns.oracle.com/ord/dicom/metadata_1_0
http://xmlns.oracle.com/ord/dicom/orddicom_1_0
http://xmlns.oracle.com/ord/dicom/preference_1_0
http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0
http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0
http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0
http://xmlns.oracle.com/ord/meta/dicomImage
http://xmlns.oracle.com/ord/meta/exif
http://xmlns.oracle.com/ord/meta/iptc
http://xmlns.oracle.com/ord/meta/ordimage
http://xmlns.oracle.com/ord/meta/xmp
Locator INVALID OBJECTS: ALL_SDO_GEOM_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: USER_SDO_INDEX_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: ALL_SDO_INDEX_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: USER_SDO_INDEX_INFO - INVALID - VIEW
Locator INVALID OBJECTS: ALL_SDO_INDEX_INFO - INVALID - VIEW
Locator INVALID OBJECTS: USER_SDO_LRS_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: SDO_LRS_TRIG_INS - INVALID - TRIGGER
Locator INVALID OBJECTS: SDO_LRS_TRIG_DEL - INVALID - TRIGGER
Locator INVALID OBJECTS: SDO_LRS_TRIG_UPD - INVALID - TRIGGER
Locator INVALID OBJECTS: USER_SDO_TOPO_INFO - INVALID - VIEW
Locator INVALID OBJECTS: ALL_SDO_TOPO_INFO - INVALID - VIEW
Locator INVALID OBJECTS: USER_SDO_TOPO_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: ALL_SDO_TOPO_METADATA - INVALID - VIEW
Locator INVALID OBJECTS: MDPRVT_IDX - INVALID - PACKAGE BODY
Locator INVALID OBJECTS: PRVT_IDX - INVALID - PACKAGE BODY
Locator INVALID OBJECTS: SDO_TPIDX - INVALID - PACKAGE BODY
Locator INVALID OBJECTS: SDO_INDEX_METHOD_10I - INVALID - TYPE BODY
Locator INVALID OBJECTS: SDO_GEOM - INVALID - PACKAGE BODY
Locator INVALID OBJECTS: SDO_3GL - INVALID - PACKAGE BODY

PL/SQL procedure successfully completed.

SQL>

 

Hence the ORA-20001 error is the true cause of the XDB problem.

 

“ORA-20001: Invalid column name or duplicate columns/column groups/expressions in method_opt”

Searching My Oracle Support (MOS) for this error leads to the following notes:

  • Gather Table Statistics Fails With ORA-20001 ORA-06512 On “invalid Column Name” (Doc ID 1668579.1).
  • 11i – 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1).
  • Gather Schema Statistics Fails With Error For APPLSYS Schema (Doc ID 1393184.1).
  • Performance Issue Noted in Trading Partner Field of Invoice Workbench (Doc ID 1343489.1).

Unfortunately, those are all related to specific tables from Oracle Applications Technology Stack, Oracle EBS, or Oracle Payables – none of those were applicable in my case. In my case the application was home grown.

Hence, MOS and Google searches returned no relevant results.

 

The Root Cause & Solution

The root cause of this problem was the METHOD_OPT parameter of DBMS_STATS.

The METHOD_OPT parameter is related to how optimizer statistic histograms are collected for columns. METHOD_OPT is set using DBMS_STATS.SET_PARAM and can be queried through DBMS_STATS.GET_PARAM or directly from the underlying base table SYS.OPTSTAT_HIST_CONTROL$.

For example:

SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> select sname, spare4 from SYS.OPTSTAT_HIST_CONTROL$ where sname = 'METHOD_OPT';

SNAME                          SPARE4
------------------------------ ----------------------------------------
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO

SQL>

 

The actual root cause of the ORA-20001 error and all of the subsequent failures and invalid components is that in the problematic database, the METHOD_OPT was set to the rarely used and outdated setting of “FOR COLUMNS ID SIZE 1”. From the database that experienced this issue:

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR COLUMNS ID SIZE 1

SQL>

 

The “FOR COLUMNS ID SIZE 1” setting was sometimes used in older versions of Oracle to prevent histogram buckets for being collected for primary keys and for plan stability through statistic changes. However, it should not be used for modern 11g or 12c databases. In fact it’s not even settable through the DBMS_STATS package after Oracle 10g.  Executing against an 11.2.0.4 database will give:

SQL> exec dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1');
BEGIN dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1'); END;

*
ERROR at line 1:
ORA-20001: method_opt should follow the syntax "[FOR ALL [INDEXED|HIDDEN]
COLUMNS [size_caluse]]" when gathering statistics on a group of tables
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at "SYS.DBMS_STATS", line 13268
ORA-06512: at "SYS.DBMS_STATS", line 13643
ORA-06512: at "SYS.DBMS_STATS", line 31462
ORA-06512: at line 1

 

Though it can still be set in 11.2.0.4 by directly updating SYS.OPTSTAT_HIST_CONTROL$, which is definitely NOT recommended.

And of course this setting can be present in an 11g database that was upgraded from an older version such as a 10g release.

Reverting this parameter to “FOR ALL COLUMNS SIZE AUTO” resolved the ORA-20001 error with UTL_RECOMP allowing the XDB component to validate and become VALID in the registry and subsequently all other components that depend on XDB.

 

Conclusion

If upgrading an older databases to 11.2.0.4 (to remain on a supported version) it is prudent to check the setting of the METHOD_OPT parameter of the DBMS_STATS package. This isn’t mentioned in any of the pre-upgrade documents or checklists and isn’t caught by even the most recent version of Oracle’s Database Pre-Upgrade Utility (MOS Doc ID 884522.1) or the DB Upgrade/Migrate Diagnostic Information (MOS Doc ID 556610.1).

The check and solution are simple and should be incorporated into your own pre-upgrade procedure:

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR COLUMNS ID SIZE 1

SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL>

 

Discover more about our expertise in the world of Oracle

Categories: DBA Blogs

Dynamic MySQL Credentials with Vault

Wed, 2015-11-11 13:20

Recently I have been looking at the Vault project as a means to manage secrets for applications and end-users. One of the use cases that immediately drew my attention was the ability to create dynamic role-based MySQL credentials.

Why Dynamic MySQL Credentials?

There are a few reasons why dynamic credentials would be beneficial, all of which can be handled by Vault, including:

  • The database environment is too large to manage individual users.
  • A need to authenticate on an external service, such as LDAP or GitHub organization.
  • Provide credentials to external resources, such as auditors or outside consultants that automatically expire.
  • Compliance requirements for strict audit logs for database access.
A High-Level Overview of Vault

Vault is a fairly new project by HashiCorp, the folks behind projects such as Vagrant and Consul. The goal is to decouple the handling of secrets from applications to enforce access control, encryption standards, and create an audit trail.

There are several components to Vault:

  • Authentication such as LDAP, GitHub or custom app-id.
  • Authorization using path-based ACL policies.
  • Encrypted storage backend using one of several options such as Consul, etcd, Zookeeper, S3, or MySQL.
  • Secret backends that define how secrets are stored or generated. Options include MySQL and AWS IAM credentials, among others.
  • Audit logging of token generation and secrets access.

To begin working with a Vault deployment, Vault must be initialized and unsealed. Unsealing Vault is a very important aspect of Vault’s security model, but is beyond the scope of this post.

After Vault is unsealed, users can begin interacting with Vault either by a CLI tool or HTTP API. Users, whether they are human or applications, are authenticated based on tokens. These tokens can be revoked at any time, be given a time-to-live (TTL) or a specific number of uses.

Authentication methods are associated with access control list (ACL) policies to define which secrets the token will have access to.

Certain secret backends can generate actual credentials upon request. This includes the MySQL secret backend, which creates users with specific grants based on their role and passes only the generated user credentials to the requesting token.

Creating Dynamic Credentials with Vault

Let’s generate a read-only MySQL credential using Vault. To follow along with this exercise, you will need to install Docker Toolbox and clone my vault repository. The docker-compose file will look like this:
View the code on Gist.

Next, we will bring up the vault and mysql containers. 
View the code on Gist.

The initiate-vault.sh script will initiate and unseal the vault, then set the environment variable ‘VAULT_TOKEN’ that we will use later. It also creates a ‘vault’ alias that allows us to interact with the Vault CLI within the docker container. The output will look like this:
View the code on Gist.

Now that Vault is unsealed, we can create the MySQL backend. Vault must be provided with the credentials of a MySQL user with GRANT OPTION privilege. For the purpose of our example, we will use the root user.
View the code on Gist.

With the MySQL backend configured, we can finally create our dynamic credentials. The generated credentials will be valid for 10 minutes, then expire.
View the code on Gist.

Final Thoughts

Overall, Vault is a promising new technology to decouple secrets from the application. Perhaps we can use it to begin to move beyond the idea that it’s OK to store credentials in environment variables.

Of course, implementing Vault does add yet another dependency that must be highly available. Care must be taken to deploy Vault in a fault-tolerant manner.

One concern I have with the current workflow of secret handling is that each GET request to /mysql/readonly creates a new user. So a busy environment could thrash the database server with CREATE USER USER commands, which will be cleaned up later with DROP USER commands.

The way I expected it to work is that if the same Vault token requested the same credential endpoint, Vault would return an unexpired credential instead of generating an entirely new user.

To work around this, the user must keep track of the ‘lease-id’ returned from the initial read request and renew the lease before it expires. The user can do this up until the lease_max period.

 

Discover more about our expertise with MySQL.

Categories: DBA Blogs

Oracle EBS News: Openworld 2015

Tue, 2015-11-10 14:41

 

Oracle OpenWorld is always an exciting and energizing experience. This year was no different! There are always a big laundry lists of new service offerings and upcoming release information during OpenWorld. This year, the major buzz is Oracle Cloud. Oracle Cloud offerings actually shadowed lot of other imported product updates at OpenWorld.

In the databases area, Oracle announced database 12c Release2 beta program. Now we can have in-memory options enabled in Active Data Guard as well. And Markus Michalewicz session “Introducing the Next Generation of Oracle Real Application Clusters [CON8769]” gave a very good insight into DB 12cR2 RAC new features. Features like Node Weighing during evictions, Domain Service Clusters and Member Clusters.

In the Middleware area, Larry announced weblogic multitenant 12cR2.

Coming to Oracle E-Business Suite, Oracle announced EBS 12.2.5 just few days before OpenWorld. And Almost all OpenWorld EBS sessions were filled with 12.2.5 updates. Cliff Godwin announced new functional updates in 12.2.5 and also confirmed that there will be 12.2.6, 12.2.7 and 12.3 in future. You can get a full list of updates from the Oracle E-Business Suite Learning Stream and this press release. On the technology side, 12.2.5 introduced new Alta UI, which is tablet friendly. 12.2.5 also introduced new tools like adopmon to monitor online patching cycle and adcfgclone dualfs=yes to parallelize patch and run fs configuration during a clone. adop validate option to validate the EBS techstack before even starting online patching. Also now oracle certified Oracle Unified Directory with EBS Release 12.2.5.

Coming to the main buzz of the event Oracle Cloud, there are two things I liked the most.

  1. Oracle Key Vault – Centralized On-Premise Key Manager for the Enterprise through which Client can control their Cloud data.
  2. Oracle Audit Vault – Audit Trails managed by the Customer using an on-premise system, which can help analyze, audit data and detect breaches. I think these two features will set apart Oracle from other Cloud providers.

For the people who are looking for OpenWorld presentation PPTs or PDFs, you can find most of them online.

 

Discover more about our expertise in the world of Oracle.

 

Categories: DBA Blogs

Implementing Fuzzy Search in SQL Server – Part 1

Tue, 2015-11-10 14:24

 

Fuzzy Search in SQL Server is not done very well. This post will cover how to implement Fuzzy Search capabilities using several approaches.

 

What is it?

Fuzzy Search is the process to locate records that are relevant to a search, even when the search criteria doesn’t match. Fuzzy Searches are used to:

  1. Suggest the correct spelling of a word (“Did you mean this…”).
  2. Find results related to your search term (“You might also like…”).
  3. Finding synonyms for search terms (Search for Dog and also get results for Puppies and Pets).
  4. Probably other things…

What we’re covering here relates to finding results after a search term has been misspelled. Related Results & Synonyms are handled quite a bit differently, and are more like geographic points on a map used to find the closest other points.

 

What Does it Fix?

As suggested above, Fuzzy Search logic is great for when you or the users don’t know the exact term they’re looking for.

For example, let’s say I want to find the latest James Bond movie, but only vaguely know its name.

An IMDB search for “Specter” brings up the James Bond “Spectre” movie as a suggestion today. If you actually search for the term, you’ll get a wide variety of increasingly weird results (And also the right answer!). I don’t have access to the IMDB code, but I believe they’re using a combination of the Levenshtein Distance, Trigrams, and maybe Double Metaphones. Coincidentally, those are exactly what we’re covering here.

Search on IMDB using Spector

An example search on IMDB

 

Fuzzy Search in SQL Server

Before implementing Fuzzy Search in SQL Server, I’m going to define what each function does. As you’ll see, they are all complementary to each other and can be used together to return a wide range of results that would be missed with traditional queries or even just one of these functions.

The three functions we’re going to look at are:

  1. Damerau-Levenshtein Distance
  2. Trigrams
  3. Double Metaphones

All of these have been written in T-SQL by very good DBAs. In future posts, I’ll be comparing the T-SQL performance to CLRs written in C# code to hopefully move the string manipulation and comparisons to a more appropriate place.

Damerau-Levenshtein Distance

The Levenshtein Distance is a calculation of how different two strings are, and it’s expressed as the number of steps required to make StringA look like StringB. The steps are counted in terms of Inserts, Updates, and Deletes of individual letters in the two words being compared.

This is good for short strings where not many differences are expected, AKA misspelled words.

A simple example is the word Car to Date. The Levenshtein Distance here is 3, and we get there by:

Step 1: UPDATE ‘C’ to ‘D’
Step 2: UPDATE ‘r’ to ‘t’
Step 3: APPEND ‘e’

So, in the IMDB example, my search for “Specter” has a Levenshtein Distance of 2 from “Spectre”. Again, we get there by:

STEP 1: UPDATE ‘r’ to ‘e’
STEP 2: UPDATE ‘e’ to ‘r’

We’re going to be looking at the Damerau-Levenshtein Distance. This is built on the Levenshtein Distance, but also accounts for transpositions of letters right next to each other. That would have returned a value of 1 with this logic:
STEP 1: Flip ‘r’ and ‘e’

Trigrams

Trigrams are used to find matching sets of words or characters in words or phrases. As the name implies, each Trigram is a set of 3 characters or words, and you simply count how many trigrams in each string match the other string’s trigrams to get a number.

These are great for comparing phrases.

An easy example is to compare a search for “SQL Server” to “SQL Sever”:

STEP 1: Break ‘SQL Server’ up into trigrams
‘SQL’, ‘QL ‘, ‘L S’, ‘ Se’, ‘Ser’, ‘erv’, ‘rve’, ‘ver’
STEP 2: Break ‘SQL Sever’ up into trigrams
‘SQL’, ‘QL ‘, ‘L S’, ‘ Se’, ‘Sev’, ‘eve’, ‘ver’
STEP 3: Count the number of trigrams that match: 5

A matching set of 5 trigrams might mean these are close enough for the application to suggest as an alternative.

Another example is comparing the phrase “Oracle” to “Relational Database Management System”.

STEP 1: Break ‘Oracle’ up into trigrams
‘Ora’, ‘rac’, ‘acl’, ‘cle’
STEP 2: Break ‘Relational Database Management System’ into trigrams
‘Rel’, ‘ela’, ‘lat’, ‘ati’, ‘ona’, ‘nal’, ‘al_’, ‘l_D’, …, ‘tem’
STEP 3: Count the number of trigrams that match between them: 0

As you can see, Oracle isn’t very close to being an RDBMS at all.

Finally, in our IMDB example, you can see that the movie Unexpected was returned. Why? I don’t actually know, but I believe it’s because there are several matching trigrams between “Specter” and “Unexpected” which pushed it into the possible suggestions.

Search on IMDB using Spector

I don’t like scrolling up.

Double Metaphone

Double Metaphones are the updated version of the SOUNDEX() function. Updated as in soundex was first patented in 1918, and double metaphones are from the 1990’s. They both work the same by breaking up consonants into what they sound like and comparing them to the closest matching values; however, soundex assumes each consonant has the same pronunciation, while metaphones allow for multiple pronunciations of the same word.

Double Metaphones are geared towards names

The first example is one where SOUNDEX and a Double Metaphone work identically. The name “Bruce” and it’s common(?) misspelling “Broos”.

STEP 1: SELECT SOUNDEX(‘Broos’) and we get B620
STEP 2: SELECT SOUNDEX(‘Bruce’) and we get B620
STEP 3: Use an online calculator to get the Metaphone values for Broos and Bruce
Both return ‘PRS’ and ‘PRS’

The benefit of the Double Metaphone here is that, because the results for both words are the same, you can have a higher level of confidence that this is a misspelling.

Another example is “Smith” and “Schmidt”.

STEP 1: SELECT SOUNDEX(‘smith’) and we get S530
STEP 2: SELECT SOUNDEX(‘schmidt’) and we get S530
STEP 3: Use an online calculator to get the Metaphone values for Smith and Schmidt
The most common pronunciation is first:
Smith yields ‘SM0’ and ‘XMT’
Schmidt yields ‘XMT’ and ‘SMT’

Using the SOUNDEX() function, you might return this “Smith” as a misspelling of “Schmidt”, which is unlikely. On the other hand, using a double metaphone function, you would know that while these two words are occasionally pronounced identically they more frequently not pronounced the same, and you could either discard the result or push it to the bottom of your suggestions.

If you do pronounce any of these words the same, here’s a website to compare the expected English pronunciations.

In my next post, I’ll implement each of these as CLRs in C# code. I’ll also point out some nice T-SQL implementations I’ve found and compare performance.

 

Discover more about our expertise in SQL Server

Categories: DBA Blogs

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

Fri, 2015-11-06 11:38

This Log Buffer is dedicated to the top quality news from the arena of Oracle, SQL Server and MySQL.

Oracle:

  • We had a question on AskTom the other day, asking us to explain what a “latch” was.
  • Jonathan Lewis thinks column groups can be amazingly useful in helping the optimizer to generate good execution plans because of the way they supply better details about cardinality.
  • Today it’s all about developing software that makes access to your product easier.
  • Steve Jobs sets a great perspective on the journey of simplicity. It starts from simple, goes through complexity and ends up in simplicity.
  • AWR period comparison is pretty easy if you have access to the two periods in the same AWR repository.

SQL Server:

  • Understanding Peer-to-Peer Transactional Replication, Part 2.
  • Knee-Jerk Wait Statistics : PAGELATCH.
  • Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes.
  • SQL Server Reporting Services General Best Practices.
  • Hello Azure: Azure IaaS – Getting Started.
  • A Skills Roadmap for DBAs in the Cloud Era.

MySQL:

  • MySQL Performance: 1M QPS on mixed OLTP_RO with MySQL 5.7 GA.
  • Deploying MongoDB, MySQL, PostgreSQL & MariaDB’s MaxScale in 40min.
  • ClusterControl Tips & Tricks: wtmp Log Rotation Settings for Sudo User.
  • Setting-up second mysql instance & replication on Linux in 10 steps.
  • s9s Tools and Resources: ‘Become a MySQL DBA’ series, ClusterControl 1.2.11 release, and more!

 

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

Categories: DBA Blogs

Nagios Authentication with Active Directory.

Wed, 2015-11-04 10:14

 

Nagios authentication with Active Directory aligns with user management consolidation policies in most organizations. This post explains how to setup Nagios authentication with Active Directory, while using Apache as web server.

mod_authz_ldap is an apache LDAP authorization module. This can be used to authorize a user based on an LDAP query.

Install mod_authz_ldap.

# yum install mod_authz_ldap

Make sure that the module is loaded in apache:

/etc/httpd/conf.d/authz_ldap.confLoadModule authz_ldap_module modules/mod_authz_ldap.so

To query LDAP, ldapsearch can be used. Install following package:

# yum install openldap-clients

Active Directory will not allow an LDAP client to operate against it anonymously, therefore a user DN and password with minimum permission is required.

For example: CN=Nagios User,CN=Users,DC=hq,DC=CORP,DC=abc,DC=org

The CN attribute corresponds to the “Display Name” of the account in Active Directory.

ldapsearch can be used to query LDAP server. In this case Active Directory.

In this example, we will look at how to enable access to all the members in ‘Pythian’ group who in turn have membership in ‘Nagios Admins’ group.

To find the members of Pythian group, run following command:

# ldapsearch -x -LLL -D ‘CN=Nagios User,CN=Users,DC=hq,DC=CORP,DC=abc,DC=org’ -W -H ldap://192.168.1.1 -b ‘CN=Pythian,OU=Internal Groups,DC=hq,DC=CORP,DC=abc,DC=org’
Enter LDAP Password:
dn: CN=Pythian,OU=Internal Security Groups,DC=hq,DC=CORP,DC=abc,DC=org
objectClass: top
objectClass: group
cn: pythian
description: General Pythian group.
member: CN=Joseph Minto,OU=Service Consultants,OU=Consultants,OU=User Accounts,DC=hq,DC=CORP,DC=abc,DC=org <—————
member: CN=Test All,OU=Service Consultants,OU=Consultants,OU=User Accounts,DC=hq,DC=CORP,DC=abc,DC=org <—————
distinguishedName: CN=pythian,OU=Internal Security Groups,DC=hq,DC=CORP,DC=abc,DC=org
instanceType: 4
whenCreated: 20120720203444.0Z
whenChanged: 20150611152516.0Z
uSNCreated: 11258263
memberOf: CN=OA Admins,OU=Internal Security Groups,DC=hq,DC=CORP,DC=abc,DC=org
uSNChanged: 128023795
name: pythian
objectGUID:: XY68X44xZU6KQckM3gckcw==
objectSid:: AQUAAAAAAAUVAAAAly2pALIyHF9ZQexpa+IAAA==
sAMAccountName: pythian
sAMAccountType: 268435456
groupType: -2147483646
objectCategory: CN=Group,CN=Schema,CN=Configuration,DC=CORP,DC=abc,DC=org
dSCorePropagationData: 20140718174533.0Z
dSCorePropagationData: 20121012140635.0Z
dSCorePropagationData: 20120823115415.0Z
dSCorePropagationData: 20120723133138.0Z
dSCorePropagationData: 16010714223649.0Z

To find the details of a user account, following command can be used:

# ldapsearch -x -LLL -D ‘CN=Nagios User,CN=Users,DC=hq,DC=CORP,DC=abc,DC=org’ -W -H ldap://192.168.1.1 -b ‘CN=Pythian,OU=Internal Groups,DC=hq,DC=CORP,DC=abc,DC=org’ -s sub “sAMAccountName=jminto”
Enter LDAP Password:
dn: CN=Joseph Minto,OU=Service Consultants,OU=Consultants,OU=User Accounts,DC= hq,DC=CORP,DC=abc,DC=org
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
cn: Joseph Minto
sn: Minto
c: US
l: Arlington
st: VA
description: 09/30/15 – Consultant – Pythian
postalCode: 22314
telephoneNumber: 1 866 – 798 – 4426
givenName: Joseph
distinguishedName: CN=Joseph Minto,OU=Service Consultants,OU=Consultants,OU=User Accounts,DC=hq,DC=CORP,DC=abc,DC=org
instanceType: 4
whenCreated: 20131203160403.0Z
whenChanged: 20150811045216.0Z
displayName: Joseph Minto
uSNCreated: 62354283
info: sponsored by: sam@abc.org
memberOf: CN=Pythian,OU=Internal Security Groups,DC=hq,DC=CORP,DC=abc,DC=org
memberOf: CN=Nagios Admins,OU=Nagios Groups,OU=AppSecurityGroups,DC=hq,DC=CORP,DC=abc,DC=org <————-
uSNChanged: 137182483
co: United States
name: Joseph Minto
objectGUID:: uh9bC/ke6Uap0/dUk9gyLw==
userAccountControl: 512
badPwdCount: 0
codePage: 0
countryCode: 840
badPasswordTime: 130360542953202075
lastLogoff: 0
lastLogon: 130844674893200195
scriptPath: callsl.bat
logonHours:: ////////////////////////////
pwdLastSet: 130305602432591455
primaryGroupID: 513
objectSid:: AQUAAAAAAAUVAAAAly2pALIyHF9ZQexphO8AAA==
adminCount: 1
accountExpires: 130881456000000000
logonCount: 116
sAMAccountName: jminto
sAMAccountType: 805306368
userPrincipalName: jminto@hq.CORP.abc.org
objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=CORP,DC=abc,DC=org
dSCorePropagationData: 20150320162428.0Z
dSCorePropagationData: 20140718174545.0Z
dSCorePropagationData: 20131203161019.0Z
dSCorePropagationData: 16010101181632.0Z
lastLogonTimestamp: 130837423368430625
mail: jo@pythian.com

Following are the ldapsearch switches used above:

-x Use simple authentication instead of SASL.
-L Search results are display in LDAP Data Interchange Format detailed in ldif(5). A single -L restricts the output to LDIFv1.
A second -L disables comments. A third -L disables printing of the LDIF version. The default is to use an extended version of LDIF.-D binddn
Use the Distinguished Name binddn to bind to the LDAP directory. For SASL binds, the server is expected to ignore this value.-W Prompt for simple authentication. This is used instead of specifying the password on the command line.-H ldapuri
Specify URI(s) referring to the ldap server(s); a list of URI, separated by whitespace or commas is expected; only the protocol/host/port fields are
allowed. As an exception, if no host/port is specified, but a DN is, the DN is used to look up the corresponding host(s) using the DNS SRV records,
according to RFC 2782. The DN must be a non-empty sequence of AVAs whose attribute type is “dc” (domain component), and must be escaped according to RFC
2396.-b searchbase
Use searchbase as the starting point for the search instead of the default.-s {base|one|sub|children}
Specify the scope of the search to be one of base, one, sub, or children to specify a base object, one-level, subtree, or children search. The default is
sub. Note: children scope requires LDAPv3 subordinate feature extension.

In the nagios configuration in apache, parameters in mod_authz_ldap can be used to validate a user like we used in ldapsearch:

# cat /etc/httpd/conf.d/nagios.conf
# SAMPLE CONFIG SNIPPETS FOR APACHE WEB SERVER
# Last Modified: 11-26-2005
#
# This file contains examples of entries that need
# to be incorporated into your Apache web server
# configuration file. Customize the paths, etc. as
# needed to fit your system.ScriptAlias /nagios/cgi-bin/ “/usr/lib64/nagios/cgi-bin/”Options ExecCGI
AllowOverride None
Order allow,deny
Allow from all
AuthName “Nagios Access”
AuthType BasicAuthzLDAPMethod ldap
AuthzLDAPServer “192.168.1.1”
AuthzLDAPBindDN “CN=Nagios User,CN=Users,DC=hq,DC=CORP,DC=abc,DC=org”
AuthzLDAPBindPassword “typepasswordhere”
AuthzLDAPUserKey sAMAccountName
AuthzLDAPUserBase “CN=Pythian,OU=Internal Groups,DC=hq,DC=CORP,DC=abc,DC=org”
AuthzLDAPUserScope subtree
AuthzLDAPGroupKey cn
AuthzLDAPMemberKey member
AuthzLDAPSetGroupAuth ldapdn
require group “Nagios Admins”Alias /nagios “/usr/share/nagios/html”Options None
AllowOverride None
Order allow,deny
Allow from all
AuthName “Nagios Access”
AuthType BasicAuthzLDAPMethod ldap
AuthzLDAPServer “192.168.1.1”
AuthzLDAPBindDN “CN=Nagios User,CN=Users,DC=hq,DC=CORP,DC=abc,DC=org”
AuthzLDAPBindPassword “typepasswordhere”
AuthzLDAPUserKey sAMAccountName
AuthzLDAPUserBase “CN=Pythian,OU=Internal Groups,DC=hq,DC=CORP,DC=abc,DC=org”
AuthzLDAPUserScope subtree
AuthzLDAPGroupKey cn
AuthzLDAPMemberKey member
AuthzLDAPSetGroupAuth ldapdn
require group “WUG Admins”

In the above configuration, mod_authz_ldap uses parameters like ldapserver, binddn, bindpassword, scope, searchbase etc to see if the supplied user credentials can be found in the Active Directory. It would also check to see if the user is a member of ‘Nagios Admins’ group.

Restarting apache would start enable Active Directory based authentication for Nagios.

 

Discover more about our expertise in Infrastructure Management.

Categories: DBA Blogs

Online Resizing of ASM Disks

Wed, 2015-11-04 07:01

 

The SAN administrator has informed us that they have extended the disks. This is the information I had from our client. The disks were labelled:

mpath_compellent_oraarch
mpath_compellent_oraarch02
mpath_compellent_oraarch03

 

The original size of the disks were 300GB and they had been extended to 600GB. These were multipath disks belonging to the disk diskgroup ARCH, which was being used to store archive logs in ASM. The database was 11.2.0.3 and was in a 2-node RAC configuration. The server was Red Hat Linux 5.9 – 2.6.18-406.el5 – 64bit.

I checked the disks using fdisk (as the root user) and got the following:

fdisk -l /dev/mpath/mpath_compellent_oraarch

Disk /dev/mpath/mpath_compellent_oraarch: 322.1 GB, 322122547200 bytes
255 heads, 63 sectors/track, 39162 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/mpath/mpath_compellent_oraarch doesn't contain a valid partition table

This confirmed that the OS was not aware of the size extension.

 

Firstly, I wanted to confirm that the correct disks had been extended. So the first place to look is in ASM:

select g.name 
     , d.path
     , d.os_mb
     , d.total_mb
from v$asm_diskgroup g
   , v$asm_disk      d
where g.group_number = d.group_number
and   g.name = 'ARCH'
/

NAME       PATH                           OS_MB   TOTAL_MB
---------- ------------------------- ---------- ----------
ARCH       ORCL:ASMDISK_NEW_ARCH03       307200     307200
ARCH       ORCL:ASMDISK_NEW_ARCH01       307200     307200
ARCH       ORCL:ASMDISK_NEW_ARCH02       307200     307200

 

Now we need to match these names to those provided by the SAN administrator.

Check the directory:

/dev/oracleasm/disks
ls -l /dev/oracleasm/disks/ASMDISK_NEW_ARCH*
brw-rw---- 1 oracle dba 253, 30 Oct  6 00:35 /dev/oracleasm/disks/ASMDISK_NEW_ARCH01
brw-rw---- 1 oracle dba 253, 29 Oct  6 00:35 /dev/oracleasm/disks/ASMDISK_NEW_ARCH02
brw-rw---- 1 oracle dba 253, 32 Oct  6 00:35 /dev/oracleasm/disks/ASMDISK_NEW_ARCH03

This gives is the major and minor numbers for the disks – major number is 253 and minor numbers are 30,29 and 32.

 

Then compare these numbers against the devices listed in:

/dev/mapper
ls -l /dev/mapper/mpath_compellent_oraarch*
brw-rw---- 1 root disk 253, 30 Oct  6 00:34 /dev/mapper/mpath_compellent_oraarch
brw-rw---- 1 root disk 253, 29 Oct  6 00:34 /dev/mapper/mpath_compellent_oraarch02
brw-rw---- 1 root disk 253, 32 Oct  6 00:34 /dev/mapper/mpath_compellent_oraarch03

The numbers match showing that they are the same devices.

 

Now we need to find the actual disks that make up the multipath devices.

multipath -l
Output truncated for brevity

mpath_compellent_oraarch03 (36000d310009aa700000000000000002b) dm-32 COMPELNT,Compellent Vol
[size=300G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 13:0:0:3  sdfm       130:128 [active][undef]
 \_ 11:0:0:3  sdgd       131:144 [active][undef]

mpath_compellent_oraarch02 (36000d310009aa700000000000000002a) dm-29 COMPELNT,Compellent Vol
[size=300G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 12:0:0:2  sdfi       130:64  [active][undef]
 \_ 14:0:0:2  sdfk       130:96  [active][undef]

mpath_compellent_oraarch (36000d310009aa7000000000000000026) dm-30 COMPELNT,Compellent Vol
[size=300G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 13:0:0:1  sdfj       130:80  [active][undef]
 \_ 11:0:0:1  sdgc       131:128 [active][undef]

From here we can see the disks:

/dev/sdfm
/dev/sdgd
/dev/sdfi
/dev/sdfk
/dev/sdfj
/dev/sdgc

We need to find this information on the other node as well, as the underlying disk names will very likely be different on the other server.

 

Now for each disk we need to rescan the disk to register the new size. To do this we need to the following for each disk on both nodes:

echo 1 &gt; /sys/block/sdfm/device/rescan

Then we can check each disk to make sure it has successfully been extended:

fdisk -l /dev/sdfm

Disk /dev/sdfm: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdfm doesn't contain a valid partition table

Looks good – once done on all nodes we can then extend the multipath devices for each device name on both nodes:

multipathd -k'resize map mpath_compellent_oraarch'
ok

Then we can check the multipath device disk size:

fdisk -l /dev/mpath/mpath_compellent_oraarch

Disk /dev/mpath/mpath_compellent_oraarch: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/mpath/mpath_compellent_oraarch doesn't contain a valid partition table

Looks good – once done on both nodes we can then resize the ASM disks within ASM:

SQL&gt; select g.name 
     , d.path
     , d.os_mb
     , d.total_mb
from v$asm_diskgroup g
   , v$asm_disk      d
where g.group_number = d.group_number
and   g.name = 'ARCH'
/

NAME       PATH                           OS_MB   TOTAL_MB
---------- ------------------------- ---------- ----------
ARCH       ORCL:ASMDISK_NEW_ARCH03       614400     307200
ARCH       ORCL:ASMDISK_NEW_ARCH01       614400     307200
ARCH       ORCL:ASMDISK_NEW_ARCH02       614400     307200

SQL&gt; alter diskgroup ARCH resize all;

Diskgroup altered.

SQL&gt; select g.name 
     , d.path
     , d.os_mb
     , d.total_mb
from v$asm_diskgroup g
   , v$asm_disk      d
where g.group_number = d.group_number
and   g.name = 'ARCH'
/

NAME       PATH                           OS_MB   TOTAL_MB
---------- ------------------------- ---------- ----------
ARCH       ORCL:ASMDISK_NEW_ARCH03       614400     614400
ARCH       ORCL:ASMDISK_NEW_ARCH01       614400     614400
ARCH       ORCL:ASMDISK_NEW_ARCH02       614400     614400

The disks and diskgroup were successfully resized.

 

Discover more about our expertise in Database Management.

Categories: DBA Blogs

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

Mon, 2015-11-02 09:34

 

This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.

Oracle:

  • An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
  • SQL Monitoring in Oracle Database 12c.
  • RMAN Full Backup vs. Level 0 Incremental.
  • Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
  • How to move OEM12c management agent to new location.

SQL Server:

  • Automate SQL Server Log Monitoring.
  • 10 New Features Worth Exploring in SQL Server 2016.
  • The ABCs of Database Creation.
  • Top 10 Most Common Database Scripts.
  • In-Memory OLTP Table Checkpoint Processes Performance Comparison.

MySQL:

  • The Client Library, Part 1: The API, the Whole API and Nothing but the API.
  • Performance of Connection Routing plugin in MySQL Router 2.0.
  • MariaDB 10.0.22 now available.
  • Semi-synchronous Replication Performance in MySQL 5.7.
  • MySQL and Trojan.Chikdos.A.

 

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

Categories: DBA Blogs