Skip navigation.

DBA Blogs

database switch over using dgmgrl

Learn DB Concepts with me... - Thu, 2016-02-25 09:59
Perform a switch over test:


ON PRIMARY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCL      PRIM

ON STANDBY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCLSTB1      PHYSICAL STANDBY


LETS CONNECT TO DGMGRL AND SWITCH OVER ROLES

DGMGRL> switchover to 'ORCLSTB1';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCLSTB1" on database "ORCLSTB1"
Connecting to instance "ORCLSTB1"...
Connected.
New primary database "ORCLSTB1" is opening...
Operation requires startup of instance "ORCLPRIM" on database "ORCL"
Starting instance "ORCLPRIM"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "ORCLPRIM" of database "ORCL"


ON OLD PRIMARY DB SERVER (ORCL) :

I have to start it manually coz dgmgrl was unable to connect to lsnr after role transfer.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size    2258320 bytes
Variable Size  566233712 bytes
Database Buffers  310378496 bytes
Redo Buffers    6340608 bytes
Database mounted

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCL      PHYSICAL STANDBY


LETS CONFIRM THE DB_ROLE BY QUERYING STANDBY DATABASE

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCLSTB1      PRIMARY

Categories: DBA Blogs

Links for 2016-02-23 [del.icio.us]

Categories: DBA Blogs

Partner Webcast - Oracle Digital Transformation: We know why, let’s talk about how

Digital Transformation is being talked about everywhere. It is the hot topic not only in IT Industry but across Industries! Why? Because those companies who master the transformation … digital...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Security in the age of cloud computing: How IT leaders are mitigating risks

Pythian Group - Tue, 2016-02-23 13:37

 

As the world becomes more connected, and more systems are moving to the cloud, security and data privacy are major considerations for IT leaders. But with the ever-present possibility of security threats, vulnerabilities and privacy exposures, how are today’s CIOs and IT leaders securing their systems?

 

According to a recent survey by the Cloud Security Alliance, despite concerns about the security of corporate data moving to the cloud, just 35.0 percent of IT leaders said that cloud-based systems of record are less secure than their on-premises counterparts. And 64.9 percent said that the cloud is either more secure than on-premises software or equally secure.

 

This is not to say that these same IT leaders don’t see security as a major concern as they evolve their systems onto the cloud. While businesses have to focus on innovation and agility to gain competitive advantage, the question of security has to be addressed alongside innovation. But the key is to address security without slowing the business down.

 

So what are IT leaders doing to secure their systems as they move forward with business and IT innovations? I had the opportunity to discuss this with IT leaders from RMS and Mozilla, during Pythian’s CIO panel discussion in November 2015.

 

Cory Isaacson, CTO at RMS has been working on bringing big data and scalable systems together to create a new cloud-based platform, and says his customers — some of the world’s largest insurance companies — are constantly concerned about threats to their data. This is an industry rife with concerns over privacy exposures because of the nature of data being handled. RMS runs catastrophe models for their customers, like scenarios that will tell insurance companies what a disaster like an earthquake or hurricane might cost them.

 

One of the biggest fears on the minds of Isaacson’s customers is about the security of their data. “The best idea is to not have anything that’s worth stealing. We’re looking at techniques that will keep the data encrypted from the user’s browser all the way into the database. If we can solve the data security issue simply by not having anything worth stealing, then that’s much better and much safer. Just take all the confidential and proprietary information and encrypt it end-to-end, and work with it on an encrypted basis,” he said.

 

RMS is betting on this encryption strategy for the longer term. But, it’s not an easy one to implement. Isaacson admits that it’s going to take some doing, and he hopes that after following ISO standards, going through quality gates and adhering to all of the industry prescribed protections and processes, that he will have some robust security in place.

 

Sean Rich, director of IT at Mozilla, is leading their application services group, and is facing the question of how to automate security within their day to day processes.  “Just like agile development found ways to build quality assurance into the process and DevOps found ways to build operations into the process, we now need a way to build greater security into the process. The definition of working software has evolved to include all three: quality, runtime and security,” said Rich.

 

Aaron Lee, the Chief Data Officer at Pythian, believes that we all need to think about automating security, just as we do with things like QA.  “When it comes to security, the cost of inaction is super high and the risk of inaction is super high,”  Lee said.

 

According to Lee, many IT leaders think the idea of automating security is not feasible. “I think the idea of depending on humans to do the same thing over and over again is nuts,” he said, referring to the manual effort that goes into security.

 

“The idea that a static security design can secure a dynamic engineering environment is an incredible mismatch,” he added.

 

Lee’s team at Pythian spends a lot of time with clients trying to figure out how to parse the regulatory requirements to automate as much as possible.

 

And Lee asserted that companies don’t always know what they’re getting into when they host their data with a third party.

 

“My favorite example is single tenant versus multi tenant. Single tenant sounds safer because all your data is in one place. But it’s all multi tenant in the end, you’re all sharing the building, you’re all sharing the earth so it’s hard to make that distinction. For a certain part of the SaaS industry, this is an important marketing distinction. But in reality, it’s meaningless. A data isolation strategy that might be implemented hypothetically could involve my business being a tenant on some multi tenant infrastructure in which I have a set of keys that I control and that are specific to my business. My third party doesn’t have access to those keys, and they are not shared by any other tenant. But in reality, are we all sharing the same set of keys?  And how are those keys and secrets managed? Are they sitting on a high risk security module somewhere on somebody’s laptop? Is it some combination of all that? When you start looking at what vendors promise from a security standpoint and what the industry talks about, it doesn’t always match reality,” he said.

 

These are the issues Pythian faces every day when assessing the vulnerability of a company’s IT systems.

 

Companies that are serious about security aren’t just checking compliance boxes to make auditors happy, according to Lee.  They’re getting experts like Pythian to address their issues in a constructive way.

 

“My observation is that the enterprise world at large has gone from needing to have people in place to check boxes to being at true risk of fundamental intellectual property leakage and for that matter, business continuity problems,” Lee said.

 

But most companies aren’t putting resources on automating. They’re hiring. And according to the Cloud Security Alliance survey, IT leaders see the biggest barrier to mitigating threats to their systems and data is not a limitation in security technology — it’s a human resource limitation. The survey results suggested that companies just can’t find enough security professionals to fill their hiring needs.

 

In keeping with that trend, The Wall Street Journal recently reported that JPMorgan expected to spend $500 million on cyber security in 2015, double its 2014 budget of $250 million.

 

While companies continue to spend their security budgets on hiring, Mozilla’s Sean Rich agrees that there’s a need for automation to deal with vulnerability.

 

“This need has driven transformation. We’ve moved from maintaining compliance to actually dealing with practical threats and with real consequences, looking at every single attack vector and how to mitigate them,” said Rich.

 

“Finding the answer to mitigating IT risks won’t be easy, and will continue to be costly,” said Pythian’s Aaron Lee.

 

“The best, most sophisticated zero day vulnerability attacks are utterly widespread in a month, so we all eventually get to feel the enormity of those attacks. Security is the biggest expense and it’s rising. It’s only going to continue to get worse because there are a lot of people who make a lot of money by hacking into systems, and they keep getting better at it.”

Categories: DBA Blogs

Oracle ADF - Getting started with Maven

As a developer you will find yourself working on commercial project which requires you to use build tools. If you want to have Continuous Integrated Delivery of your application on Oracle Cloud...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Links for 2016-02-22 [del.icio.us]

Categories: DBA Blogs

Google Cloud Dataproc in ETL pipeline – Part 1 (Logging)

Pythian Group - Mon, 2016-02-22 11:16

 

Google Cloud Dataproc, now generally available, provides access to fully managed Hadoop and Apache Spark clusters, and leverages open source data tools for querying, batch/stream processing, and at-scale machine learning. To get more technical information on the specifics of the platform, refer to Google’s original blog post and product home page.

 

Having access to fully managed Hadoop/Spark based technology and powerful Machine Learning Library (MLlib) as part of Google Cloud Platform makes perfect sense as it allows you to reuse existing code and helps many to overcome the fear of being “locked into” one specific vendor while taking a step into big data processing in the cloud. That said, I would still recommend evaluating Google Cloud Dataflow first while implementing new projects and processes for its efficiency, simplicity and semantic-rich analytics capabilities, especially around stream processing.

 

When Cloud Dataproc was first released to the public, it received positive reviews. Many blogs were written on the subject with few taking it through some “tough” challenges on its promise to deliver cluster startup in “less than 90 seconds”. In general the product was well received, with the overall consensus that it is well positioned against the AWS EMR offering.

 

Being able, in a matter of minutes, to start Spark Cluster without any knowledge of the Hadoop ecosystem and having access to a powerful interactive shell such as Jupyter or Zeppelin is no doubt a Data Scientist’s dream. But with extremely fast startup/shutdown, “by the minute” billing and widely adopted technology stack, it also appears to be a perfect candidate for a processing block in bigger ETL pipelines. Orchestration, workflow engine, and logging are all crucial aspects of such solutions and I am planning to publish a few blog entries as I go through evaluation of each of these areas starting with Logging in this blog.

 

Cloud Dataproc Logging

Cluster’s system and daemon logs are accessible through cluster UIs as well as through SSH-ing to the cluster, but there is a much better way to do this. By default these logs are also pushed to Google Cloud Logging consolidating all logs in one place with flexible Log Viewer UI and filtering. One can even create custom log-based metrics and use these for baselining and/or alerting purposes. All cluster logs are aggregated under a “dataproc-hadoop” tag but “structPayload.filename” field can be used as a filter for specific log file.

 

In addition to relying on Logs Viewer UI, there is a way to integrate specific log messages into Cloud Storage or BigQuery for analysis. Just to get an idea on what logs are available by default, I have exported all Cloud Dataproc messages into BigQuery and queried new table with the following query:

 

SELECT

structPayload.filename AS file_name,

count(*) AS cnt

FROM

[dataproc_logs.dataproc_hadoop_20160217]

WHERE

metadata.labels.key=’dataproc.googleapis.com/cluster_id’

AND metadata.labels.value = ‘cluster-2:205c03ea-6bea-4c80-bdca-beb6b9ffb0d6’

GROUP BY

file_name

 

  • hadoop-hdfs-namenode-cluster-2-m.log
  • yarn-yarn-nodemanager-cluster-2-w-0.log
  • container_1455740844290_0001_01_000004.stderr
  • hadoop-hdfs-secondarynamenode-cluster-2-m.log
  • hive-metastore.log
  • hadoop-hdfs-datanode-cluster-2-w-1.log
  • hive-server2.log
  • container_1455740844290_0001_01_000001.stderr
  • container_1455740844290_0001_01_000002.stderr
  • hadoop-hdfs-datanode-cluster-2-w-0.log
  • yarn-yarn-nodemanager-cluster-2-w-1.log
  • yarn-yarn-resourcemanager-cluster-2-m.log
  • container_1455740844290_0001_01_000003.stderr
  • mapred-mapred-historyserver-cluster-2-m.log

 

Google Cloud Logging is a customized version of fluentd – an open source data collector for unified logging layer. In addition to system logs and its own logs, fluentd is configured (refer to /etc/google-fluentd/google-fluentd.conf on master node) to tail hadoop, hive, and spark message logs as well as yarn application logs and pushes them under “dataproc-hadoop” tag into Google Cloud Logging.

Application Logging

You can submit a job to the cluster using Cloud Console, Cloud SDK or REST API. Cloud Dataproc automatically gathers driver (console) output from all the workers, and makes it available through Cloud Console. Logs from the job are also uploaded to the staging bucket specified when starting a cluster and can be accessed from there.

 

Note: One thing I found confusing is that when referencing driver output directory in Cloud Dataproc staging bucket you need Cluster ID (dataproc-cluster-uuid), however it is not yet listed on Cloud Dataproc Console. Having this ID or a direct link to the directory available from the Cluster Overview page is especially critical when starting/stopping many clusters as part of scheduled jobs. One way to get dataproc-cluster-uuid and a few other useful references is to navigate from Cluster “Overview” section to “VM Instances” and then to click on Master or any worker node and scroll down to “Custom metadata” section. Indeed, you can also get it using “gcloud beta dataproc clusters describe <CLUSTER_NAME> |grep clusterUuid” command but it would be nice to have it available through the console in a first place.

 

The job (driver) output however is currently dumped into console ONLY (refer to  /etc/spark/conf/log4j.properties on master node) and although accessible through Dataproc Job interface, it is not currently available in Cloud Logging.

 

The easiest way around this issue, which can be easily implemented as part of Cluster initialization actions, is to modify /etc/spark/conf/log4j.properties by replacing  “log4j.rootCategory=INFO, console” with “log4j.rootCategory=INFO, console, file” and add the following appender:

 

# Adding file appender

log4j.appender.file=org.apache.log4j.RollingFileAppender

log4j.appender.file.File=/var/log/spark/spark-log4j.log

log4j.appender.file.layout=org.apache.log4j.PatternLayout

log4j.appender.file.layout.conversionPattern=%d{yy/MM/dd HH:mm:ss} %p %c: %m%n

 

Existing Cloud Dataproc fluentd configuration will automatically tail through all files under /var/log/spark directory adding events into Cloud Logging and should automatically pick up messages going into /var/log/spark/spark-log4j.log.

 

You can verify that logs from the job started to appear in Cloud Logging by firing up one of the examples provided with Cloud Dataproc and filtering Logs Viewer using the following rule:

node.metadata.serviceName=”dataproc.googleapis.com”

structPayload.filename=”spark-log4j.log”

 

If after this change messages are still not appearing in Cloud Logging, try restarting fluentd daemon by running “/etc/init.d/google-fluentd restart” command on master node.

Once changes are implemented and output is verified you can declare logger in your process as:

import pyspark

sc = pyspark.SparkContext()

logger = sc._jvm.org.apache.log4j.Logger.getLogger(__name__)

and submit the job redefining logging level (INFO by default) using “–driver-log-levels”.

Learn more here.

Categories: DBA Blogs

PRKO-2002 : Small Things, Large Shadows

Pythian Group - Mon, 2016-02-22 09:28

This quick and short blog post is a reminder to remember the syntax. I was just trying to bring down local listeners at half rack of Exadata (11gR2) to perform some maintenance. The best way to shutdown resources in RAC is to use srvctl so was trying to run following after setting Grid home as Oracle home, from where the local listeners were running:

[oracle@mytestexa1:~]$ srvctl stop listener LISTENER
PRKO-2002 : Invalid command line option: LISTENER

[oracle@mytestexa1:~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mytestexa2,mytestexa4,mytestexa1,mytestexa3

Hmmmm. ok, then I tried this:

[oracle@mytestexa1:~]$ srvctl stop listener LISTENER -n mytestexa1
PRKO-2002 : Invalid command line option: LISTENER

Well, ummm ok, then I tried this one:

[oracle@mytestexa1:~]$ srvctl stop listener -n mytestexa1
[oracle@mytestexa1:~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mytestexa2,mytestexa4,mytestexa3

And it worked. Yes, if the default listener name is not in use, then -l listener_name should be used.

So need to remember the syntax :) . Small things, but in emergencies they cast large shadows for a while.

Categories: DBA Blogs

A View From Down Under: What IT Innovation and Agility Mean to IT Pros in Australia

Pythian Group - Mon, 2016-02-22 06:38

The impact of today’s disruptive technologies on how we do business led Pythian to introduce Velocity of Innovation, a series of panel discussions for senior IT professionals. These are Pythian’s exclusive thought-leadership events where a moderator-led panel engages in lively conversations around today’s emerging technologies.

We recently took our Velocity of Innovation event series to Sydney, Australia to find out what’s on the minds of CIOs and other IT leaders in that region. Our moderator for this event was Tom McCann, senior customer experience analyst with Forrester. You may be thinking: Why would a customer experience analyst be moderating a CIO panel? But here’s why we chose to put a customer experience expert at the centre of this discussion. With the commoditization of products and services, delivering a quality customer experience is one of the few ways that companies now have of truly differentiating themselves from their competitors. From attracting prospects, to acquiring customers, to onboarding and ongoing support, companies need to have the right tools and processes in place to ensure products or services perform as promised, and that every interaction customers have with them is satisfying. They also need to be able to use data to respond to customer needs and market trends. IT plays a critical role in of all of these areas.

The panel consisted of IT leaders with a range of backgrounds and experiences:

  • Francisco Alvarez, vice president, APAC at Pythian
  • Chris Mendes, executive consultant big data and analytics from Industrie IT
  • Tim Sheedy, principal analyst with Forrester Research

As we do to start off many of these events, we began the Sydney discussion by asking this question: Innovation and agility are very common buzzwords that seem to describe what everyone wants. What have you explicitly done to get better at innovating or to make your organization or your customer’s more agile? How has this push for agility impacted your enterprise architecture?

Here’s an excerpt from the discussion that followed.

Chris: I actually don’t think there’s enough innovation in Australia, in particular. There’s a lot of talk about it, people are actually doing a lot of experiments and there are some companies who’ve set up business purely giving them the tool sets to use their data to innovate. You’ve got a few things that seem to be working against that at the moment and I think one of the things working against it is that it doesn’t stand on its own.

It doesn’t matter what the technology is, and in fact what ends up happening is it all comes down to the “Why?” Because you really need to have the technologists interacting with business people so that when they come up with an idea they get immediate feedback. I’ve found that a lot of businesses don’t have time for innovation. They run pretty lean, they are very focused on day-to-day operations and don’t have time to come up with new ideas or to use their data to actually innovate. There are a lot of roadblocks there. I don’t see it as a technology problem, I actually see one of the roadblocks as a management issue. The other one is sort of the culture of no failure, and I think we’ve got a big issue with that in Australia.

Tim: As you are saying, I’ve presented a lot on innovation and Forrester actually has a really good innovation model. We spoke to about 130 companies around the globe who did bits of innovation well and we took all those bits and made them into an end-to-end process that can be used by our clients. We learned a lot from putting this together. We spoke to Telstra in Australia and did the piece on deciding what to innovate with, and where to invest. Other companies told us about how they come up with ideas. I speak to a lot of clients who told us that they had an innovation program in place where they gathered ideas from staff. But now those employees don’t trust their companies anymore because they never got back to them on how they would implement their ideas.

Chris: I think the other thing is I think there’s also a very big misunderstanding at board levels about innovation because boards are there to actually stop you changing your business. The fundamental tenant is: “We’ve got a great business model here, it’s running well, we’ve got to make sure that any change to it doesn’t damage that.” There’s a natural caution at board levels and it’s totally understandable.

Audience: One of the issues I guess that you just raised is that the boards are risk adverse, however if their business is failing, then there is obviously a need for them to do something about it.

Tim: But that’s a bad time to be innovating, right? When everything is going wrong, “We should try to do things differently.” The market’s probably left you behind by that point.

Francisco: The main problem that most of you have hit upon is that innovations equal risk. But take a step back and look at the companies that are really doing well in the market. They’re doing really well because of one factor that differentiates them: they were not afraid to try to innovate. And because of that innovation they are getting their share of the market and gaining ground. Just look at the financial market. CBA was considered crazy a few years ago for all the investment they were doing in technology, social media, apps and so on. They got ahead. And now everybody is trying to do the same.The problem is they need to have a balance. Even with choosing vendors, companies will avoid risk. The will keep going with IBM because they don’t want to take a risk. Or they keep Oracle and let Oracle do everything for them because there might be a risk with moving to another vendor. If they already own a technology, it doesn’t matter if they are not getting good service. They think that for risk capacity, there is insurance. Sometimes you’re not saving money or not improving, but you don’t want to innovate simply because you don’t want to increase the risk to the business.

Tim: Innovation is risk management, this is it. The discussion went further on this topic, and explored areas like the future of IT, security and more.

Interested in being a part of a discussion like this one? Velocity of Innovation is a series of thought-leadership events for senior IT management hosted by Pythian. Pythian invites leading IT innovators to participate in discussions about today’s disruptive technologies: big data, cloud, advanced analytics, DevOps, and more. These events are by invitation only.

If you are interested in attending an upcoming Velocity of Innovation event in a city near you, please contact events@pythian.com. To view our schedule of upcoming events visit out Velocity of Innovation page.

Categories: DBA Blogs

Compression -- 1 : BASIC Table Compression

Hemant K Chitale - Sun, 2016-02-21 08:30
Now, starting a new series of blog posts on Compression.

Beginning with  BASIC Table Compression.

Basic Table Compression was introduced in 9i Release 2 (9.2.0).  It is free with the Enterprise Edition.  Basic Compression works only with Direct Path (Bulk Load) INSERTs.  It does not apply to "normal" INSERT and UPDATE operations.
Basic Compression is actually "de-duplication".  It identifies values that are repeated within the same database block and replaces them with tokens mapped to the list of values.   Note the section that I have underlined.  De-duplication does not span database blocks.

Here is a first demo of Basic Compression using INSERT /*+ APPEND */  (for Direct Path Insert).

I start with a table that is a multiplied copy of DBA_OBJECTS, named as SOURCE_DATA.

PDB1@ORCL> show user
USER is "HEMANT"
PDB1@ORCL> select count(*) from source_data;

COUNT(*)
----------
364496

PDB1@ORCL>
PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'SOURCE_DATA';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL>


I then create table to hold compressed data.  Note (let me repeat again : Basic Compression works only with DIRECT PATH INSERT).

PDB1@ORCL> create table compressed_1 tablespace hemant compress as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_1
2 select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> col segment_name format a30
PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_Segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 12
SOURCE_DATA TABLE 49

PDB1@ORCL>


So, a 49MB table is compressed down to 12MB.  What if I UPDATE these rows ?

PDB1@ORCL> update compressed_1
2 set owner = owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


Updating only a single column without changing the length of the data in that column has increased the size of the table.

Let me continue the UPDATE experiment further, without increasing the length of data in any columns.

PDB1@ORCL> update compressed_1
2 set owner = owner, object_name = object_name, subobject_name = subobject_name, object_id = object_id,
3 data_object_id = data_object_id, object_type = object_type, created = created
4 /

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


No further degradation ? (unless my UPDATE expands the size of data in one or more column(s)).

Note a difference between the two tables :

PDB1@ORCL> select table_name, compression, compress_for, pct_free
2 from user_tables;

TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
------------------------------ -------- ------------------------------ ----------
COMPRESSED_1 ENABLED BASIC 0
SOURCE_DATA DISABLED 10

PDB1@ORCL>


The Compressed table is created with PCT_FREE=10.  PCT_FREE=0  (Note that this can also have an impact when you issue UPDATEs subsequently, UPDATEs that increase the size of data in one or more columns).

UPDATE : In 12c the syntax for BASIC Compression is "ROW STORE COMPRESS BASIC". Simply specifying "COMPRESS" or "COMPRESS BASIC" is supported for backward compatibility.

.
.
.

Categories: DBA Blogs

Links for 2016-02-19 [del.icio.us]

Categories: DBA Blogs

Check duplicates for combination of multiple columns

Learn DB Concepts with me... - Fri, 2016-02-19 16:41
If you have combination of multiple columns that you want to check duplicates. 

For example : Check duplicates for combination of

 AGE,NAME,SEX,DOB,CITY

Sql will be :

select AGE,NAME,SEX,DOB,CITY,count(1) 
from Employees 
group by AGE,NAME,SEX,DOB,CITY 
having count(1) >1;

Categories: DBA Blogs

RMAN : Unused Block Compression and Null Block Compression

Hemant K Chitale - Fri, 2016-02-19 08:56
To quote the 11.2 documentation,  "Although it is referred to as block compression, it might be helpful to think of block compression as block skipping".  11.2 differentiates between Unused Block Compression and Null Block Compression.
UPDATE 28-Feb-16 : Please note the list of 5 pre-requisites for Unused Block Compression.

In response to a community thread, here is a quick demo to see both Null Block Compression and Unused Block Compression.

I start with a 100MB tablespace.

[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:37:47 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace hemant;

Tablespace created.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> select file_name, bytes/1048576 from dba_data_files
2 where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
100


SQL>


First I take a backup of this empty tablespace.

RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp tag=TAG20160219T224255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904257776_cdgbhkcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>

This 100MB datafile backed up to 1.03MB is Null Blocm compression.

I next load data into it and  then take a backup.

[oracle@ora11204 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:45:12 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hkc_all_obj tablespace hemant
2 as select * from dba_objects;

Table created.

SQL> insert into hkc_all_obj select * from hkc_all_obj;

28105 rows created.

SQL> /

56210 rows created.

SQL> /

112420 rows created.

SQL> /

224840 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HKC_ALL_OBJ
49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp tag=TAG20160219T224721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258049_cdgbr179_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


So, I now have 49.67MB backup of the datafile in the tablespace. What happens if I drop all objects (in this case only the 1 table) in that tablespace, with RECYLEBIN ?

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> drop table hkc_all_obj;

Table dropped.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp tag=TAG20160219T225023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258224_cdgbxjg4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


The latest backup (BackupSet 29) is still 49.67MB although, technically, the tablespace has no Tables/Indexes.  It does have something in the RECYCLEBIN.

Let me purge the RECYCLEBIN and then run a fresh backup.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL> purge hkc_all_obj;
purge hkc_all_obj
*
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge table hkc_all_obj;

Table purged.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

no rows selected

SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp tag=TAG20160219T225323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258404_cdgc351n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 1.03M DISK 00:00:00 19-FEB-16
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225323
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4808966 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>
[oracle@ora11204 Desktop]$ ls -l /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
-rw-rw----. 1 oracle oracle 104865792 Feb 19 22:53 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
[oracle@ora11204 Desktop]$


Aha ! BackupSet 31 is now 1.03MB only.  So, Unused Block Compression has kicked in !  The blocks that belonged to that table are yet formatted and in prior versions that did not have Unused Block Compression would still be backed up even though the table has been dropped.
(Note : I did not shrink the datafile, it is still 100MB).


UPDATE 28-Feb-16 :  Testing for TRUNCATE

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ 49

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp tag=TAG20160228T190655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022445_cf5o7lyq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>
SQL> show parameter recyclebin;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> truncate table hkc_all_obj;

Table truncated.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ .0625

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp tag=TAG20160228T190946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022589_cf5oczwq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.53M DISK 00:00:01 28-FEB-16
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190946
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776656 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>


The TRUNCATE did reduce the size of the table and the backup !
.
.
.

Categories: DBA Blogs

The Value of Pythian’s Internship Program

Pythian Group - Fri, 2016-02-19 07:35

I have been working as a software development intern at Pythian for more than three months. As a second year student in electrical engineering, it is both unsurprising and somewhat expected that I would be working in the software industry during my first co-op term. I’ve heard complaints from aspiring electrical engineers that most of the available co-op positions are for software. This is especially true for first or second year students, because the knowledge base required for advanced electronics projects has not yet been attained.

I would discourage students from adhering to this viewpoint. Knowledge of programming and software development in the 21st century is invaluable to any kind of engineering. Furthermore, with the high level of involvement and learning involved in working as a software developer at Pythian, I am confident that someone with a thirst for knowledge would find this to be a worthwhile experience.

It began at the interview. As soon as I began talking to my current manager and the human resources representative, I could tell that the position was one I’d be very interested in. They both struck me as very kind, understanding, and most importantly, knowledgeable individuals. It was also very clear to me that the goal of the interview was to determine certain key traits, other than the necessary technical requirements. They were seeking someone who had a drive to participate and learn, as well as someone who was sociable, and who could fit with the team dynamic. Needless to say, after learning what kind of work I would be doing on the team and receiving the job offer, I quickly accepted the position.

After two weeks of orientation, I was immediately tasked with a very interesting project. The project involved creating a script to upgrade the database used by the core code base, which would eventually be used on the actual product that is “Adminiscope”. It was very fulfilling to know that my work, at the beginning of my work term, would be used on a production system to apply new features.

After this project, I was assigned more interesting projects, which required even more learning. At this point of my co-op term, I have touched almost all aspects of the Adminiscope software, and my abilities in software development have grown to a significant degree. It is very important to note, that learning all these new skills would have been much more difficult if not for the constant guidance of the friendly, knowledgeable, and experienced people I have had the pleasure to work with.

If what I have already stated is not enough to encourage you to work at Pythian, then their unique corporate culture should convince you. While working here, I never once felt that my work or input was less valuable then others just because I was a co-op student. Participation is actively encouraged. Learning more about the company as a whole is also supported through “BORG” meetings with various senior level staff. I have had a few conversions with the CEO himself, and have learned something of significance every time. It is also a very good feeling to be told by someone of such status in the company that the work you are doing is meaningful, appreciated, and most of all, why it’s important.

I have absolutely no regrets, and I am grateful that I have had the opportunity to work in such a stimulating environment. I can say without doubt that in my three and a half months at Pythian, I have learned more about programming and software development than I have in my entire academic career. It is truly a worthwhile learning experience for those seeking a challenge. I hope my story will encourage you to apply for Pythian’s internship program, and I wish you luck in getting the position.

Categories: DBA Blogs

Data Encryption at Rest

Pythian Group - Thu, 2016-02-18 14:31

This blog post was co-authored by Peter Sylvester and Valerie Parham-Thompson

Introduced in version 10.1.3 (and with substantial changes in 10.1.4), the MariaDB data encryption at rest feature allows for transparent encryption at the tablespace level for various storage engines, including InnoDB and Aria.

Before now, there have been only two widely accepted encryption methods for MySQL/MariaDB: encryption at the file system level, or encryption at the column level. For comparison, we’ll do a brief overview of how these work, as well as the pros and cons typically associated with each option.

File System Encryption

This is performed by setting a file system to be encrypted at the block level within the operating system itself, and then specifying that the encrypted volume should be the location of the data directory for MySQL/MariaDB. You can also use encrypted volumes to store MariaDB binary logs.

Pros
  • One-time setup and then no additional management required.
Cons
  • There is a large degree of overhead at the CPU level. Every time an InnoDB page/extent is retrieved and stored in the InnoDB buffer pool, the data has to be decrypted. The same issue occurs when dirty pages are flushed back to persistent storage, be it data or a log file.
Column Encryption

You can encrypt data at the column level by using a binary data type (varbinary/BLOB) and then encrypt the data as it goes into or out of the the page at the application or code level. Typically this is done using the AES_ENCRYPT and AES_DECRYPT functions in MySQL/MariaDB.

Pros
  • You’re only encrypting the data that needs to be secured. All other data has no encryption-related overhead.
  • This provides a higher degree of security then file system encryption. If the data is encrypted at the file system or by the data encryption at rest feature, if you can get into the running MariaDB instance you can still see the unencrypted version of the data. With column-level encryption, the data is stored in a secure fashion and you need to supply the encryption key every time it is accessed by the MariaDB instance.
Cons
  • The crypt key needs to be stored somewhere that allows the application to easily provide it when running queries against MariaDB.
  • You may be able to see the crypt key in statement-based binary logging, or in the process list.
  • Data that is encrypted should not be used for reverse lookups. For example, if you are encrypting a column that stores a name, and you need to search that column for a specific name, you have to specify the search using the AES_DECRYPT function, which will force all the table records to be scanned, decrypted, and compared as part of the “where” operation of the query.
MariaDB Data Encryption at Rest

This solution sits somewhere between the aforementioned file system level and column level encryption, allowing you to encrypt data at the table level. This allows for encryption that is easier to manage and work with, while also allowing for a narrower focus so you are encrypting only the data or logs that you wish to encrypt. Although, it should be noted that like file system encryption, if you can get to the launched MariaDB instance, you can get access to the encrypted data.

Now let’s walk through a test of the functionality of the MariaDB data encryption at rest feature.

Prep

Preparation included cloning a Centos7 base VM in VirtualBox, adjusting the IP and hostname, and installing MariaDB 10.1.11 using their repository with instructions here.

Create Keys

The first step was to create keys. The output of the openssl command below (with example output) was used to edit a new file /var/lib/mysql/keys.txt.

The command was:
openssl enc -aes-256-cbc -P -md sha1
enter aes-256-cbc encryption password:
Verifying - enter aes-256-cbc encryption password:

Sample output:

...
key=AD2F01FD1D496F6A054E3D19B79815D0F6DE82C49E105D63E1F467912E2F0B95
iv =C6A3F3625D420BD19AF04CEB9DA2D89B

Sample contents of keys.txt using that output:

1;C6A3F3625D420BD19AF04CEB9DA2D89B;AD2F01FD1D496F6A054E3D19B79815D0F6DE82C49E105D63E1F467912E2F0B95

(You can take the additional step of encrypting the keys, but that was not done here.)

Don’t lose the key file, or you won’t be able to start the server:

2016-02-13 20:37:49 140334031026304 [ERROR] mysqld: File '/var/lib/mysql/keys.txt' not found (Errcode: 2 "No such file or directory") 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'file_key_management' init function returned error. 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'file_key_management' registration as a ENCRYPTION failed. 
2016-02-13 20:37:49 140334031026304 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'InnoDB' init function returned error. 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 
2016-02-13 20:37:49 140334031026304 [Note] Plugin 'FEEDBACK' is disabled. 
2016-02-13 20:37:49 140334031026304 [ERROR] Unknown/unsupported storage engine: InnoDB 
2016-02-13 20:37:49 140334031026304 [ERROR] Aborting

You can of course remove the relevant configs and restart successfully, but we have found at least two issues when trying to remove this configuration after it has been put in place on the MariaDB instance.

  1. If you encrypt the InnoDB log files (redo, not binary logs), then remove the encryption configuration and restart MariaDB, it will not be able to start until you re-enable the data at rest encryption feature.
  2. If you enable default encryption by putting innodb-encrypt-tables in the my.cnf, and then create a table, remove the feature, and restart MariaDB, the server will crash irrecoverably when selecting data from the table (bug filed as https://mariadb.atlassian.net/browse/MDEV-9559).
Install Plugin

Next step was to install the plugin and use this file. The clearest path to doing this is to add the following two lines in /etc/my.cnf within the [mysqld] section:

plugin-load-add=file_key_management.so
file-key-management-filename = /var/lib/mysql/keys.txt</pre>

Restart MariaDB, and confirm the plugin is installed. The file_key_management plugin should display as “active.”

show all_plugins like '%file%';
Testing Encrypted Tables

As the documentation indicates, you can encrypt all tables when they are created (specify innodb-encrypt-tables in the my.cnf) or individual tables (by adding the settings to a create or alter table statement). (See further below for result of using the third option, innodb-encrypt-tables=force.)

Here are the results if you encrypt a single table.

First, create a table:

mysqlslap --concurrency=5 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-write-number=1000 --no-drop

And encrypt it:

alter table mysqlslap.t1 encrypted=yes encryption_key_id=1;

Here’s the table definition after encrypting:

show create table mysqlslap.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `encryption_key_id`=1

Looking at the .ibd file directly via xxd, you can see some text data before encryption:

0084570: 0001 8000 0002 7661 6c65 7269 6570 6172 ......valeriepar
0084580: 6861 6d74 686f 6d70 736f 6e00 0000 0000 hamthompson.....

And after encryption:

0085470: fdf4 7c27 d9cb 5d33 59b1 824d 4656 b211 ..|'..]3Y..MFV..
0085480: 7243 9ce0 1794 7052 9adf 39a1 b4af c2fd rC....pR..9.....

Once that table was encrypted, to test moving encrypted tablespaces, the files were copied from the source to a destination server as follows. The destination server had no encryption plugin, configs, or key installed.

The following process is typical for moving tablespaces: create a similar empty table on the destination server, without encryption. (It throws an error on that unencrypted server if you try it with `encrypted`=yes `encryption_key_id`=1.)

create database mysqlslap;

use mysqlslap

CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then start the move process. First, discard the tablespace on the destination server. This leaves you with just the .frm file there.

-rw-rw----. 1 mysql mysql 65 Feb 13 13:15 db.opt
-rw-rw----. 1 mysql mysql 932 Feb 13 13:15 t1.frm
ALTER TABLE mysqlslap.t1 DISCARD TABLESPACE;

Prepare the table on the source server:

flush tables t1 for export;

Now you have a .cfg file on the source server:

-rw-rw----. 1 mysql mysql 65 Feb 13 13:13 db.opt
-rw-rw----. 1 mysql mysql 620 Feb 13 13:16 t1.cfg
-rw-rw----. 1 mysql mysql 976 Feb 13 13:14 t1.frm
-rw-rw----. 1 mysql mysql 557056 Feb 13 13:14 t1.ibd

Send the .cfg and .ibd files from the source to the destination server:

scp /var/lib/mysql/mysqlslap/t1.cfg root@192.168.56.69:/var/lib/mysql/mysqlslap/t1.cfg
scp /var/lib/mysql/mysqlslap/t1.ibd root@192.168.56.69:/var/lib/mysql/mysqlslap/t1.ibd

Free to unlock on the source server now:

unlock tables;

You’ll get an error on import if you don’t make them usable by mysql:

chown mysql:mysql /var/lib/mysql/mysqlslap/t1*

With the .cfg and .ibd files in place on the destination server, import the tablespace there:

alter table t1 import tablespace;

As intended, the encryption prevents importing the table:

MariaDB [mysqlslap]&gt; alter table t1 import tablespace;
ERROR 1296 (HY000): Got error 192 'Table encrypted but decryption failed. This could be because correct encryption management plugin is not loaded, used encryption key is not available or encryption method does not match.' from InnoDB
innodb-encrypt-tables=force

If you set innodb-encrypt-tables=force in /etc/my.cnf, attempting to create a table with encryption=no fails:

create table t3 ( 
`intcol1` int(32) DEFAULT NULL, 
`intcol2` int(32) DEFAULT NULL, 
`charcol1` varchar(128) DEFAULT NULL, 
`charcol2` varchar(128) DEFAULT NULL, 
`charcol3` varchar(128) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encryption_key_id`=1 `encrypted`=no;
ERROR 1005 (HY000): Can't create table `mysqlslap`.`t3` (errno: 140 "Wrong create options")

The error message could be more clear, but the setting would save future create statements from undoing desired encryption set up by a DBA.

Encrypted Binlogs

Binlogs can also be encrypted.

Start by adding this to the my.cnf, and restart server.

encrypt_binlog

Before encryption, the binlogs look like this:

008dfb0: 494e 5345 5254 2049 4e54 4f20 7431 2056 I NSERT INTO t1 V
008dfc0: 414c 5545 5320 2832 3132 3635 3538 3138 ALUES (212655818
008dfd0: 352c 3737 3332 3733 3731 382c 2759 3838 5,773273718,'Y88
008dfe0: 4e30 3774 6f30 3333 6d32 5845 497a 487a N07to033m2XEIzHz
008dff0: 4d4a 7348 7558 544c 3247 6543 6865 4334 MJsHuXTL2GeCheC4
008e000: 574a 7149 436c 4471 6f6c 3479 634d 7071 WJqIClDqol4ycMpq
008e010: 5a68 374b 3463 5a79 7442 4251 684e 4d42 Zh7K4cZytBBQhNMB
008e020: 6234 4c6e 7161 6457 425a 5366 7649 544c b4LnqadWBZSfvITL
008e030: 7a64 5a77 3536 7571 4835 4771 5466 7477 zdZw56uqH5GqTftw
008e040: 6a36 6a5a 5943 336b 6c4f 4e5a 616c 6d50 j6jZYC3klONZalmP
008e050: 454a 4c4a 5047 4161 4c49 4f27 2c27 6970 EJLJPGAaLIO','ip

After restarting the server with encryption, newly generated binlog files look like this:

011b860: 69c5 cc00 5cb0 1581 0217 2d3f 728c 77ff i...\.....-?r.w.
011b870: a6ca e6e3 a041 0f26 ee39 c398 eecd 4df9 .....A.&amp;.9....M.
011b880: 5bef 53e0 bf0a 96bd 7b61 bfcc c074 6151 [.S.....{a...taQ
011b890: 208b 63fc 4efd ee91 b2bc 0a90 1009 76a1 .c.N.........v.
011b8a0: bf18 84e3 f444 82a1 e674 b44b 7754 2cc9 .....D...t.KwT,.
011b8b0: b63f 946c 821d 222a ae57 a251 451c 8332 .?.l.."*.W.QE..2
011b8c0: d030 1c5f 3997 db77 96f1 4da5 a03e 55a9 .0._9..w..M..&gt;U.
011b8d0: a882 3980 f81f 9fa9 7b45 27c1 2f51 34ad ..9.....{E'./Q4.
011b8e0: b8bf e5e6 4b1e 6732 11a1 1b00 0000 c049 ....K.g2.......I
011b8f0: b2a9 ad08 ed95 4c5c 5541 05b4 a256 14d3 ......L\UA...V..
011b900: 045b e74f 2526 0000 009f 921c 1482 d621 .[.O%&amp;.........!

Note that also you can’t use mysqlbinlog on encrypted binlogs:

mysqlbinlog /var/lib/mysql/maria101-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160213 10:49:27 server id 1 end_log_pos 249 Start: binlog v 4, server v 10.1.11-MariaDB-log created 160213 10:49:27
BINLOG '
h1C/Vg8BAAAA9QAAAPkAAAAAAAQAMTAuMS4xMS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3QAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAAGbdjEE=
'/*!*/;
# at 249
# Encryption scheme: 1, key_version: 1, nonce: 4caf0fe45894f796a234a764
# The rest of the binlog is encrypted!
# at 285
/*!50521 SET skip_replication=1*//*!*/;
#620308 22:02:57 server id 3337593713 end_log_pos 2396907567 Ignorable
# Ignorable event type 118 (Unknown)
# at 324
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 42, event_type: 204
ERROR: Could not read entry at offset 366: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

As a test of replication, encrypted binlogs were sent from an encrypted master to an unencrypted slave. The master had encrypted binlogs, but the slave had no encryption plugin, configs, or keys.

Nothing special in the replication setup, and replication did not break. No issues were detected in multi-master replication with replication filtering. Also, if the slave is set up for encryption, the encryption key in use on the slave does not need to be identical to that of the key that is in use on the master.

Of special note for security, while the master’s binlogs were encrypted, the slave’s relay logs were not. Change statements on an unencrypted slave are easily viewed at the file level or using mysqlbinlog on the relay logs. Watch those user permissions! Relay logs on the slave can be encrypted using the ‘encrypt-binlog’ setting on the slave having the plugin installed.

Conclusions
  1. Binlog encryption prevents viewing change statements in raw format or via mysqlbinlog.
  2. Replication from an encrypted master to a nonencrypted slave works. Note that the relay logs on the nonencrypted slave make the change statements visible.
  3. Encrypting a table prevents copying the tablespace to another server.
  4. Once implemented, the steps to unencrypt multiple tables in your schema require careful planning. It is recommended you test this feature carefully before implementing in production.
Categories: DBA Blogs

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

Pythian Group - Thu, 2016-02-18 14:08

This Log Buffer browses through Oracle, SQL Server and MySQL spaces and brings forth some of the useful blog posts for this week.

Oracle:

Conner throws it out of the park. As we all (hopefully) know, we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

Pythian’s Gleb talks about Azure on RAC. Microsoft Azure provides an acceptable and affordable platform for a training environment.

There are some performance improvements that require physical storage options to be set on tables or indexes. One particular technique that I will take as an example for this article is index compression.

To call Oracle Reports from Oracle Forms application, the most secure approach is to use the RUN_REPORT_OBJECT built-in.

Introducing Asset Physical Inventory with PeopleSoft Asset Tracking

SQL Server:

Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

SSIS: Case sensitivity may expose issues with change deployment

Foreign Key Indexes and Binding

In SQL Server 2016 we have new compression functions to compress and uncompress specific data.

Database Continuous Integration

MySQL:

VividCortex is all about optimizing your queries. Many of our users asked us to analyse their queries, searching for common errors and mistakes.

RocksDB vs the world for loading Linkbench tables

Measuring Docker IO overhead

MariaDB 5.5.48 and Connector/J 1.3.5 now available

EXPLAIN FORMAT=JSON: buffer_result is not hidden!

Categories: DBA Blogs

Partner Webcast – Oracle PaaS: Exadata Database on Cloud

Cloud computing is known for many things: cost effectiveness, flexibility, scalability. IT of our generation needs to adapt to cloud exactly for the above reasons. But extreme performance has not...

We share our skills to maximize your revenue!
Categories: DBA Blogs

The Little Big Company With The Huge Heart

Pythian Group - Wed, 2016-02-17 10:26

February is a drag. Maybe like me you have already experienced profound failure on following-up with New Year’s Resolutions. Instead of “taking yoga” I ended up “severely injuring my back”. Ah, c’est la vie. Sometimes it’s harder than other times to be positive, and when it’s hard to be positive it’s even harder to be grateful (another one of my New Year’s Resolutions). The good news (or bad news if you’re making excuses for your negativity) is you can practice gratitude from wherever you are, in whichever state of mind or body you’re in.

Pythian exists because of the people who work here. This isn’t a commentary on the Company’s success, the Company’s Brand or Culture, but rather the actual employees who make this place a great place to be, by doing one thing: practicing kindness.

This building is full of people who are going out of their way on a daily basis to:

a) be generous with their time (and their baking)

b) offer support to a colleague in need

c) tell a joke, take a joke, share a laugh

d) provide you with directions when you inevitably become confused and disoriented on the 5th or 6th floor.

Thankfully I have the life and work experience to know that this is not the norm. Something special is happening here. People are supporting each other, providing personal and professional insights, and I must say that while technical genius is the grease that allows the wheels to spin, there is a ton of emotional intelligence being exchanged by a ton of people, all the time.

If you wouldn’t characterize your experience here in the same light I encourage you to wander around. Get lost looking for finance and smile with the Accountant who keeps chocolate in his desk. Tell someone in L&D about a new hobby you’ve taken up and she might just tell you she’s proud of you. Stumble into the IT room and ask an inane question about your new cell phone that you can barely turn on, it will be met with patience. Tell our CRO a joke, the driest you’ve got, or a dad-joke, he’ll love it.

Talk to our CEO about heavy metal, art, poetry, books (highly recommended points of interest if like me you started here not ever having heard of “Unix”). Ask our VP of Transformation where the printer is, seriously he told me three times and I still don’t know so please also let me know when you know. Tell one of the DBAs that you’re learning French and then avoid them for the next month or so because you’re too nervous to say anything beyond “je ne comprends pas”.

I’ve been given hugs, have given out hugs, one of my colleagues actually made me lunch a few times when I was moving places and my life was in disarray. There are a lot of smiles here and they are contagious.

Happy to be here with all of you. Thanks for being kind.

Categories: DBA Blogs

How to build a Multi-domain cluster with AlwaysOn Availability Groups – SQL Server 2016

Pythian Group - Wed, 2016-02-17 09:30

SQL Server 2016 is making lots of news, and promises to be a revolutionary SQL Server version. In talking about AlwaysOn Availability Groups, a very good number of improvements were already announced as part of the Basic Availability Groups. By the way, Hemantgiri, my friend and former Data Platform MVP, already talked about this on his post.

One of the improvements that got my attention was the ability to set up the AlwaysOn Availability Groups sit in a multi-domain cluster, or even a domain-detached cluster. I tested both and indeed this works very well, but the maintenance is slightly different (read: more difficult).

We need to consider the following points:

  • The cluster manager is not supporting this, everything should be done by using PowerShell.
  • To perform the setup, you need to be connected as a local Administrator.
    • The “Administrator” user password should be the same on all involved nodes.
  • For some tasks, like SQL Server upgrade/update, you will need to run the setup using the local administrator account.
    • You don’t need use the Administrator account to log in. Use your regular account, select the Run as different user menu item, and press the SHIFT key while you right-click the file.
      sql_server_screenshot
  • You must be using Windows Server 2016 and SQL Server 2016.

The new capability is a group effort between SQL Server and the Windows development team and is, in my opinion, a very good option. I see the multi-domain capability as a better option than a detached-domain, and I already have customers who can benefit very well from this option.

The following approach is the same; it doesn’t matter if you are making a multi-domain setup or domain-detached cluster; however, there are some particular details:

For Domain-detached cluster

In this particular case, the Failover Cluster is created without any associated computer objects, and for this reason, each involved node needs to have a defined primary DNS suffix.

2

For Multi-domain cluster

Make sure that you can reach all the nodes, using IP, server name, and FQDN. Take care with firewall rules as this may influence the cluster creation. Make sure that the trust relationship between the domains is well defined.

 

Creating the cluster

In my case, I created a multi-domain cluster, which was also a multi subnet one. The following command is the base to create the cluster:

New-Cluster –Name <Cluster Name> -Node <Nodes to Cluster> -StaticAddress <Cluster IP> -AdministrativeAccessPoint DNS

The catch here is the -AdministrativeAccessPoint DNS. Check the PowerShell New-Cluster command for more options.

To perform this command, open the PowerShell console as Administrator (you can either log in as local Administrator or open the console using the method that I explained earlier in this article), and execute the command. That’s it!

In my test laboratory, I created a cluster containing three nodes:

  • SQLCAFE.local (192.100.100.17)
  • LISBON.local (192.100.200.18)
  • SQLCAFE.local (192.100.100.19)

I executed the following command:

New-Cluster –Name W2016CLT01 -Node W2016SRV06.SQLCAFE.local , W2016SRV07.LISBON.local , W2016SRV08.SQLCAFE.local  -StaticAddress 192.100.100.52,192.100.200.52 -NoStorage -AdministrativeAccessPoint Dns

 

After the cluster is created, use PowerShell to perform all the administrative tasks. The following commands are useful:

  • Get-Cluster
  • Get-ClusterNode
  • Get-ClusterGroup
  • Get-ClusterGroup <Group Name> | Get-ClusterResource

3

Creating an Availability Group

The process of creating the availability group is the same as mentioned previously. Actually, we are lucky, because the SQL Server Management Studio has all we need to manage the AG, without the need to touch PowerShell. For basic management, SSMS is enough.

After the cluster is created, you can go ahead and enable the AlwaysOn Availability Groups feature. To perform this, just go the SLQ Server Configuration manager and access the SQL Server engine properties. In the AlwaysOn High Availability tab, select the Enable AlwaysOn Availability Groups check box. Click OK and restart the SQL Server engine.

4

After enabling the feature, just follow the usual procedure and use the wizard in the SSMS, T-SQL, or PowerShell, to create the Availability Group.

 

More Details

As this is a complex configuration, a careful DNS and Quorum configuration is recommended to ensure the highest service availability. As a result, a DNS replication might be needed. For the quorum, the new Cloud Witness (supported from Windows Server 2016) is recommended.

 

Summary

When installed on Windows Server 2016, more flexibility is given to SQL Server 2016, including DTC support for AG, and Cloud Witness. Multi-domain and domain-detached cluster increases the architecture options when using SQL Server. This is just one of the improvements coming with SQL Server 2016. Stay tuned for more news!

 

Categories: DBA Blogs