Skip navigation.

Yann Neuhaus

Syndicate content
All blog entries from http://www.dbi-services.com/
Updated: 8 hours 15 min ago

The SQL Server DBA's essential toolkit list

Mon, 2014-09-22 02:01

This week, I attended the SQLSaturday 2014 in Paris. During the Pre-Conference on Thursday, I followed Isabelle Van Campenhoudt for her SQL Server Performances Audit session. This conference took the form of an experience sharing between attendees. Indeed, we tried to list together the most important software, tools, features or scripts which will help an SQL Server DBA during his work. In this blog, I want to share our final list with you.

 

Windows Server Level: Hardware & Applications


CrystalDiskMark

CrystalDiskMark is a free disk benchmark software. It can be downloaded here.

 

SQLIO

SQLIO is another free disk benchmark software. It can be downloaded here.

 

Windows Performance Monitor (PerfMon)

PerfMon is a Windows native tool which collects log data in real time in order to examine how programs running on the computer affect the performance.

PerfMon provides a lot of counters which measure the system state or the activity.

You can learn more on TechNet.

You can find the most important counters for SQL Server here.

 

Performance Analysis of Logs (PAL)

PAL is an Open Source tool based on the top of PerfMon. It reads and analyses the main counters looking for known thresholds.

PAL generates an HTML report which alerts when thresholds are reached.

PAL tool can be downloaded on CodePlex.

 

Microsoft Assessment and Planning (MAP)

MAP is a Microsoft toolkit which provides hardware and software information and recommendations for deployment or migration process for several Microsoft technologies (such as SQL Server or Windows Server).

MAP toolkit can be downloaded on TechNet.

 

SQL Server Level: Configuration & Tuning

 

Dynamic Management Views and Functions (DMV)

DMV are native views and functions of SQL Server which returns server state information of a SQL Server instance.

You can learn more on TechNet.

 

sp_Blitz (from Brent Ozar)

It is a free script which checks SQL Server configuration and highlights common issues.

sp_Blitz can be found on Brent Ozar website.

 

Glenn Berry's SQL Server Performance

It provides scripts to diagnostic your SQL Server since SQL Server 2005.

These scripts can be downloaded here.

 

Enterprise Policy Management (EPM) Framework

EPM Framework is based on Policy-Based Management. It is a reporting solution which tracks SQL Server states which do not meet the specified requirements. It works on all instances of SQL Server since SQL Server 2000.

You can learn more on CodePlex.

 

SQL Server Level: Monitoring & Troubleshooting

 

SQL Profiler

SQL Profiler is a rich interface integrated in SQL Server, which allows to create and manage traces to monitor and troubleshoot an SQL Server instance.

You can learn more on TechNet.

 

Data Collector

Data Collector is a SQL Server feature introduced in SQL Server 2008, and available in all versions.

It gathers performance information from multiple instances for performance monitoring and tuning.

You can learn more on TechNet.

 

Extended Events

Extended Events is a monitoring system integrated in SQL Server. It helps for troubleshooting or identifying a performance problem.

You can learn more on TechNet.

 

SQL Nexus

SQL Nexus is an Open Source tool that helps you for identifying the root cause of SQL Server performance issues.

It can be downloaded on CodePlex.

 

SQL Server Level: Maintenance

 

SQL Server Maintenance Solution

It a set of scripts for running backups, integrity checks, and index statistics maintenance on all editions of Microsoft SQL Server since SQL Server 2005.

This solution can be downloaded on Ola Hallengren's website.

 

 

Conclusion

This blog does not pretend to make a complete list of DBA needs, but it tries to cover most parts. You will notice that all softwares are free and recognized by the DBA community as reliable and powerful tools.

I hope this will help you.

For information, you can learn how to use these tools in our SQL Server DBA Essentials workshop.

Documentum upgrade project: D2-Client, facets and xPlore

Sun, 2014-09-21 19:57

To enhance the search capability we had to configure xPlore to use the new customer attributes as facets and configure D2 to use the default and new facets.

  Configuring xPlore to use facets with the customer attributes
  • Stop the Index Agent and Server
  • Update indexserverconfig.xml by adding the following line (e. g.):

 

 xml-code

 

  • Keep only the indexserverconfig.xml file in $DSSEARCH_HOME/config
  • Remove $DSSEARCH_HOME/data/*
  • Start index and agent server
  • Start a full reindexing
  • Once all is indexed, set index to normal mode

 

Necessary tests

You should do two tests before configuring the D2-Client.

 

1. On the content server:

 

java com.emc.d2.api.config.search.D2FacetTest -docbase_name test67 -user_name admin -password xxxx -full_text -facet_names dbi_events

 

2. On the xPlore server:

  • Check if the new lines have been validated by executing $DSEARCH_HOME/dsearch/xhive/admin/XHAdmin
  • Navigate to xhivedb/root-library/dsearch/data/default
  • Under the Indexes Tab, click the "Add Subpaths" button to open the "Add sub-paths to index" window where you can see in the Path column the added customer attributes

 

Configure the search in D2-Config
  • Launch D2-Config
  • Select Interface and then the Search sub-menu
  • Tick  "Enable Facets" and enter a value for "Maximun of result by Facet"

 

D2-Config

 

Once this is done, you are able to use the facets with the D2-Client.

Improving your SharePoint performance using SQL Server settings (part 2)

Sun, 2014-09-21 17:36

Last week, I attended the SQLSaturday 2014 in Paris and participated in a session on SQL Server optimization for Sharepoint by Serge Luca. This session tried to list the best pratices and recommendations for Database Administrators in order to increase the SharePoint performance. This blog post is based on this session and is meant as a sequel to my previous post on Improving your SharePoint performance using SQL Server settings (part 1).

 

SQL Server instance

It is highly recommended to use a dedicated SQL Server instance for a SharePoint farm and to set LATIN1_GENERAL_CI_AS_KS_WS as the instance collation.

 

Setup Account permissions

You should give the Setup Account the following permissions in your SQL Server instance:

  • securityadmin server role

  • dbcreator server role

  • dbo_owner for databases used by the Setup Account

 

Alias DNS

It is recommended to use Alias DNS to connect to the SQL Server instance with your SharePoint server. It simplifies the maintenance and makes it easier to move SharePoint databases to another server.

 

Disk Priority

When you plan to allocate your SharePoint databases accross different databases, you might wonder how to maximize the performance of your system.

This is a possible disk organization (from faster to lower):

  • Tempdb data and transaction log files

  • Content database transaction log files

  • Search database data files (except Admin database)

  • Content database data files

 

Datafiles policy

You should use several datafiles for Content and Search databases, as follows:

  • distribute equally-sized data files accross separate disks

  • the number of data files should be lower than the number of processors

Multiple data files are not supported for other SharePoint databases.

 

Content databases size

You should avoid databases bigger than 200 GB. Databases bigger than 4 TB are not supported by Microsoft.

 

Conclusion

SharePoint is quite abstract for SQL Server DBAs because it requires specific configurations.

As a result, you cannot guess the answer: you have to learn on the subject.

Oracle OEM Cloud Control 12.1.0.4 - the new features

Thu, 2014-09-18 17:01

This document describes the main new features of Cloud Control 12.1.0.4. A lot of new features are coming with the 12.1.0.4 version I will describe the most important ones, but you can also refer to this Oracle documen: http://docs.oracle.com/cd/E24628_01/doc.121/e25353/whats_new.htm#CEGFFGBI

 

New management services repository page

There is a new management services repository page providing details about the management repository:

 

cc1

 

In the Setup Menu—> Manager Cloud Control, select health overview:

 

cc2

 

You have access to a new performance page:

 

cc3

 

This new Enterprise Manager Performance Page is providing precious performance informations in order to help administrators to check the overall performance of their Enterprise Manager infrastructure.

 

cc4

 

Oracle BI Publisher

Oracle BI Publisher 11g is now installed by default with Enterprise Manager Cloud Control 12.1.0.4, but it is not configured by default. A post installation configuration step has to be done in order to configure the BI Publisher server.

 

New Security Console

A new Security Console allows the administrators to have a single entry point where they can view, analyze, or optimize the security for their environment.

In the Setup menu, select Security, then Security Console:

 

cc5

cc6


This new security console displays your Enterprise Manager security configuration and allows you to view, analyze and optimize the security for your environment.

The categories are:

  • Pluggable authentication (LDAP authentication, Oracle access manager, Oracle SSO based authentication...)
  • Fine-grained Access Control (target and resource privilege, list of super administrator...)
  • Secure Communication (Https and public key infrastructure, Oms secure configuration, Database Encryption configuration)
  • Credentials Management
  • Comprehensive Auditing (Current auditing configuration, Specific Audit operations, Audit Usage Statistics)
  • Active User Session Count (Session settings, active sessions)
  • Best Practices Analysis (quick view of Enterprise Manager Security configuration)

 

cc7

 

Apply privilege delegation template

Enterprise Manager 12.1.0.4 allows to apply a default privilege delegation template setting to one newly discovered host, or many already discovered hosts.

This new feature is very interesting for administrators when a lot of new host targets have been added to Enterprise Manager Cloud Control. We can also use emcli with the set_default_privilege_delegation verb to apply those privileges to hosts.

 

cc8

 

In the Setup Menu à Security, we select Privilege Delegation:

 

cc9

 

We can display the templates:

 

cc10

 

We apply the template to multiple hosts:

 

cc11

 

The SUDO_TEMPLATE has been successfully applied to mutliple hosts:

 

Emcli

New emcli verbs are available in the 12.1.0.4 version. The command emcli help will show you the new features.

The following ones are especially interesting:

  • get_not_updatable_agents: displays agents not updatable
  • get_updatable_agents: displays updatable Agents
  • update_agents:  performs Agent Update Prereqs and submits Agent Update Job
  • delete_incident_record:   deletes incidents based on the provided IDs, up to a maximum of 20 incidents.
  • resume_job: resumes a job
  • suspend_job:  suspends a job
  • clear_default_privilege_delegation_setting: clears the default privilege delegation settings for a platform.
  • set_default_privilege_delegation_setting: sets the default privilege delegation setting for one or more platforms
  • test_privilege_delegation_setting: tests Privilege Delegation Setting on a host

 

Plugin management

We can deploy multiple plugins from the Cloud Control Console in one operation. This new feature will help administrators to reduce the number of OMS restarts during the fastidious plugins deployment:

 

cc12

 

Metric alert message customization

Metric alert messages can be customized in order to be more understandable or to be compliant with the data centers' wording convention. As you can see in the following screenshot, checking the Edit Alert Message allows the Enterprise Manager Administrator to modify the error message:

 

cc13

 

Metric collection schedule enhancement

We now have the possibility to enter a starting time for a metric collection, if the schedule frequency is defined by days, by weeks, weekly or monthly. This new feature might be very interesting for administrators when the metric is time sensitive.

 

cc14

 

Advanced Threshold Management

With Enterprise Manager 12.1.0.4, the Advanced Threshold Management new feature allows us to compute adaptive thresholds (self-adjusting) or time-based thresholds.

Now, in the Metric and collections settings, you can display different kinds of metrics:

 

cc15

 

Adaptive thresholds

Enterprise Manager 12.1.0.4 has improved the alerting mechanism with the adaptive thresholds. Generally the normal expected set metrics values are depending on the workload of the target, so the threshold value is too low or too high. The adaptive thresholds are calculated about a target’s baseline value.

For example (see below), you can define a warning or a critical threshold to high (95 %), very high (99 %), severe (99,9 %) and extreme value (99.99 %).

Select Adaptive Threshold:

 

cc16

 

cc17

 

Select Quick Configuration:

 

cc18

 

Choose Primary OLTP, for example:

 

cc19

 

Select Finish:

 

cc20

 

Then you can edit and modify the thresholds values:

 

cc21

 

Time based statics thresholds

As database activity is quite different during the day where a lot of users are conected, and the night where the main activity is concerning the batch jobs, this new feature allows the administrators to define higher thresholds value for a metric during the night.

For example in the Metric and Collection Settings:

 

cc22

 

We select Advanced Threshold Management in the Related Links:

 

cc23

 

By selecting the Threshold Change Frequency, you can adapt the warning and critical values, depending of the time of the day (or week):

cc24

 

Day is 7 AM to 7 PM in target timezone, night means 7 PM to 7 AM in target timezone, weekdays means Monday to Friday, and weekend means Sunday and Saturday.

 

Incident rule set simulator

The new Rule Set Simulator in the Incident Rule screen displays you the rules to which the event will apply. By this way administrators can test its rules without executing the actins specified in the rules like emailing or opening tickets.

 

cc25

cc26

 

Incident manager

There are some new features in the incident screen manager screen.

When looking at an incident, the related event tab displays recent configuration changes, helping administrators to solve the problem:


cc27

 

The notification tab displays now all the notifications sent for the event or the incident (email, SNMP traps …).

 

SNMP V3

The new SNMP version 3 protocols offer more security sending information from Enterprise Manager 12.1.0.4 and third party management systems. SNMPv3 includes three important services: authentication, privacy, and access control.

 

cc28

 

Faster target Down notifications

The target down detection (concerning hosts, database instance, WebLogic Server, Management Agent) has been improved in terms of quickness of detection. Oracle documentation says the target down monitoring is detected within seconds, the tests I made showed me it was true, the target down incident has been generated in some seconds.

 

Enhanced agent and host down detection

Every Enterprise Administrator has encountered problems with agents going down or not communicating anymore with the OMS. The new Enterprise Manager 12.1.0.4 version has added a sub status icon allowing the administrator to discover the reason why the agent is in an unreachable state.

 

cc29

 

When an agent goes down unexpectedly, in the Manager Cloud Control agent page you can select the symptom analysis icon which may help you to determine the root cause of the problem:

 

cc30

 

When an agent goes down unexpectedly, you can select the symptom analysis icon in the Manager Cloud Control agent page which may help you to determine the root cause of the problem.

 

Role and target property when adding a target

When adding a target database to a host managed by an agent 12.1.0.4, we have the possibility to specify the global target properties:

 

cc31

 

We can also explicitly specify a group for the targets:

 

cc32

 

New Job progress screen

 

oracle@vmtestoraem12c:/home/oracle/ [oms12c] emctl set property -name oracle.sysman.core.jobs.ui.useAdfExecutionUi -value true

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.

All rights reserved.

SYSMAN password:

Property oracle.sysman.core.jobs.ui.useAdfExecutionUi has been set to value true for all Management Servers

OMS restart is not required to reflect the new property value

 

Before setting this property to true, when selecting a job we only could view the following screen:

 

cc33

 

After setting the property to true:

 

cc34

 

Conclusion

A lot of new interesting features are present in the 12.1.0.4 version. I would particularly mention the Advanced Threshold Management and the new Security Console, which will help administrators to be more and more proactive in their job.

Thinking about downgrading from Oracle Enterprise to Standard Edition?

Tue, 2014-09-16 01:01

You are using an Oracle Enterprise Edition and thinking about downgrading to the Standard Edition? In this case, you must be sure that your applications are compatible. It's not something easy to check. Here are a few ideas.

 

Why?

Why do you want to downgrade to the Standard Edition? For licensing costs, of course. Today, it is difficult to find a server with only a few cores. And Oracle Enterprise Edition is licenced per number of cores which are physically in the machine. You change your hardware and you will find that you cannot have a machine with the same number of cores. Even if the performance is fine, you will need to buy more software licenses because of those new multicore processors.

Another reason is virtualization. You want to consolidate your servers, but you don't want to pay database software licenses for all your datacenter capacity.

So the Standard Edition is a good alternative: besides the fact that they are chaper, the licenses are counted per socket and not per core.

Oracle Standard Edition doesn't have all features, but you can accept that. The reduction in the cost of licenses can compensate several days of development, tuning or administration, as well as the acquisition of third party tools to compensate what is missing on SE (for example dbvisit standby for high availability).

But you need to identify those features that you are using and that come with Enterprise Edition only

 

1. Read feature availability

The features available only in Enterprise Edition are listed in the documentation which shows which ones are available in Standard Edition or Enterprise Edition.

So the first thing to be done is to read that list and mark what you know you are using.

But there are two problems:

  • It's sometimes difficult to understand. For example, do you see clearly that you can't send e-mails for Enterprise Manager notifications when you don't have diagnostic Pack?
  • You probably don't know all what you (or your developers, your application) use.

 

2. Query feature usage

Oracle comes with a nice view about feature usage. DBA_FEATURE_USAGE_STATISTICS. It's nice because you have information about what you used, with comments, dates, etc. And it's also exposed in Cloud Control.

But did you ever try to match that with the documentation from the link above? That's difficult:

  • some Enterprise Edition features are not checked. For example, the usage of materialized view is shown, but without the distinction about those using query rewrite (which is an EE feature)
  • some subset of features triggers usage even when they should not (for example the Locator part of Spatial do not need Spatial option)

 

3. Import to standard

One important thing to do is to import into a Standard Edition and check what fails with an 'ORA-00439: feature not enabled' error. Because what is nice is that when you install Standard Edition the features not available are supposed to be disabled at link time.

One tip: you probably need to import metadata only so you want to import it in a small database. But when you do that you will see that your datafiles are increasing because of the initial segment size. This is because the 'deferred segment creation' is an Enterprise Edition feature. So the tip is:

 

impdp ... content=metadata_only transform="storage:n"

 

The big advantage when testing the import is that you are already testing the migration procedure, because it's the only way to migrate from Enterprise Edition to Standard Edition.

The problem is that it warns you only about static feature - those in your data model. Not about the usage. For example you will know that you can't create bitmap indexes. But you will not know that you will not be able to do bitmap plan conversion from regular indexes.

Testing the import guarantees that the migration can be done, but you should test the application on a SE database with data in order to validate usage and performance.

 

4. Try and test

After having checked everything, from the obvious which is documented, to the little things we know by experience, I usually advise the customer to test. Install a test database in Standard Edition. Test the application, test the monitoring, test the administration procedures (no online operation, no flashback database,...). If you plan to migrate with minimum downtime with a replication tool (such as dbvisit replicate) you can start to replicate to a Standard Edition database. Then you will be able to test the read-only use cases, such as reporting, which may suffer from the lack of some optimizer features (adaptive plans, result cache,...)

 

5. Decide

Then you will decide if you are ready to downgrade to Oracle Standard Edition. Of course, it will no be transparent. You will have to find some workarounds. The decision is just a balance between the cost reduction and the time you can spend to do manually what was automatic in EE.

SQL Saturday 323: SQL Server AlwaysOn and availability groups session slides

Sun, 2014-09-14 23:50

This SQL Saturday’s edition in Paris is now over. It was a great event with a lot of French and international speakers. There were also many attendees indicating that this event is a great place to share about SQL Server technologies. Maybe the Montparnasse tower in Paris played a role here with its panoramic view over Paris from the 40th floor! Smile


blog_16_landscape_from_spuinfo

blog_16_badge_sqlsaturdays


For those who didn’t attend on Saturday, you will find our SQL Server AlwaysOn and availability groups session slides here: SQLSaturday-323-Paris-2014---AlwaysOn-session.pptx

Don’t forget the next big event of the SQL Server community in Paris (1-2 december): Journées SQL Server

We will probably be there and of course we will enjoy to meet you!

Documentum upgrade project - ActiveX and D2-Client 3.1Sp1

Sun, 2014-09-14 19:31

This is another blog posting an our Documentum upgrade project. This time, the following issue occured: the ActiveX could not be installed using the D2-Client. We had to access the D2-Config url to have it installed. For a normal user, this could not be used.

Analyzes

The workstation had the ActiveX for D2 3.0 installed, the version before the upgrade. Under C:\\Windows\\Downloaded Program Files, we had:  

ctx
ctx.ocx  
D2UIHelper.dll

On my workstation where I could install (using D2-Config) the D2 3.1.1 ActiveX, I also had C:\\Windows\\Downloaded Program Files\\CONFLICT.* folders containing D2UIHelper.dll and ctx.inf

By checking the content of ctx.inf of this new cab I saw that we had the wrong version (see FileVersion) of the

 [ctx.ocx]  
file-win32-x86=thiscab  
RegisterServer=yes  
clsid={8C55AA13-D7D9-4539-8B20-78BC4A795681}  
DestDir=  
FileVersion=3,0,0,2

By checking the "ctx.cab" file in "D2-Client/install" and "D2-Config/install" on the application server I found that we did not have the same version, both ctx-cab had the same date and size but the digital signature was different:  

D2-Config ctx-cab: &8206;17 &8206;September &8206;2013 10:56:11,  
D2-Client: 19 &8206;April &8206;2013 17:03:08

 

Solution

To solve the issue I copied the ctx.cab" from "D2-Config/install" path to "D2-Client/install/". Once this was done the activeX could be installed using the D2-Client url.

It was confirmed by the vendor that this is a bug in the delivered package

Kerberos SSO with Liferay 6.1

Sun, 2014-09-14 02:22

In my previous blog, I described the process to install a Kerberos Client and how to Kerberized Alfresco. In this blog, I will continue in the same way and present another application that could be configured to use the Kerberos MIT KDC: Liferay. Liferay is a very popular and a leader in Open Source solution for enterprise web platform (Intranet/Extranet/Internet web sites). Liferay could be bundled with several application servers like Tomcat, JBoss, Glassfish, but it could also be installed from scratch (deployment of a war file) with a lot of existing application servers.

 

For this blog, I will need the following properties/variables:

  • example.com = the DNS Domain
  • EXAMPLE.COM = the KDC REALM
  • kdc01oel.example.com = the FQDN of the KDC
  • document.write(['mpatou','EXAMPLE.COM'].join('@')) = the principal of a test user
  • lif01.example.com = the FQDN of the Liferay host server
  • otrs01.example.com = the FQDN of the OTRS host server

 

Please be aware that some configurations below may not be appropriate for production environment. For example, I don't configure Apache to run as a different user like "www" or "apache", I don't specify the installation directory for Apache or Kerberos, aso...

Actual test configuration:

  • OS: Oracle Enterprise Linux 6
  • Liferay: Liferay Community Edition 6.1.1 GA2 - installed on /opt/liferay-6.1.1
  • Application Server: Tomcat 7.0.27 - listening on port 8080

 

This version of Liferay doesn't have a default connection to a Linux KDC so everything should be done from scratch. The first thing to do is to add an Apache httpd in front of Liferay, if there is not already one, to process Kerberos requests. This part is described very quickly without extensive explanations because we don't need all the functionalities of Apache. Of course you can, if you want, add some other configurations to the Apache httpd to manage for example an SSL certificate, the security of your application or other very important features of Apache... So first let's check that the Tomcat used by Liferay is well configured for Kerberos with an Apache front-end:

  • The HTTP port should be 8080 for this configuration
  • The maxHttpHeaderSize must be increased to avoid authentication errors because an http header with a Kerberos ticket is much more bigger than a standard http header
  • The AJP port should be 8009 for this configuration
  • The tomcatAuthentication must be disabled to delegate the authentication to Apache

 

To verify that, just take a look at the file server.xml:

[root ~]# vi /opt/liferay-6.1.1/tomcat-7.0.27/conf/server.xml
1.png

 

Then download Apache httpd from the Apache web site (or use yum/apt-get), extract the downloaded file and go inside of the extracted folder to install this Apache httpd with some default parameters:

[root ~]# cd /opt
[root opt]# wget http://mirror.switch.ch/mirror/apache/dist//httpd/httpd-2.4.10.tar.gz
[root opt]# tar -xvf httpd-2.4.10.tar.gz
[root opt]# cd httpd-2.4.10
[root httpd-2.4.10]# ./configure
[root httpd-2.4.10]# make
[root httpd-2.4.10]# make install

 

This will install Apache httpd 2.4.10 under /usr/local/apache2. There could be some errors during the execution of "./configure" or "make" or "make install" but these kind of issues are generally well known and so the solutions to these issues could be found everywhere on Internet. An installation with the command apt-get will put the configuration file (named apache2.conf not httpd.conf) under /etc/apache2/ so please adapt the description below to your environment.

 

Once Apache httpd is installed, it must be configured to understand and use Kerberos for all incoming requests:

[root httpd-2.4.10]# vi /usr/local/apache2/conf/httpd.conf
# Add at the end of the file
Include /opt/liferay-6.1.1/tomcat-7.0.27/conf/mod_jk.conf
    Include /usr/local/apache2/conf/mod_kerb.conf

[root httpd-2.4.10]# vi /usr/local/apache2/conf/mod_kerb.conf
# New file for the configuration of the module "mod_auth_kerb" and Kerberos
    ServerAdmin root@localhost
    # The FQDN of the host server
    ServerName lif01.example.com:80

# Of course, find the location of the mod_auth_kerb and replace it there if
# it's not the same
    LoadModule auth_kerb_module /usr/local/apache2/modules/mod_auth_kerb.so

‹Location /›
    AuthName "EXAMPLE.COM"
        AuthType Kerberos
        Krb5Keytab /etc/krb5lif.keytab
        KrbAuthRealms EXAMPLE.COM
        KrbMethodNegotiate On
        KrbMethodK5Passwd On
        require valid-user
    ‹/Location›

 

The next step is to build the mod_auth_kerb and mod_jk. The build of mod_auth_kerb requires an already installed Kerberos client in this Liferay server. As seen below, my Kerberos client on this server is under /usr/local. Moreover, the buid of mod_jk may requires to specify the apxs binary used by Apache, that's why there is the "--with-apxs" parameter:

[root httpd-2.4.10]# cd ..
[root opt]# wget http://sourceforge.net/projects/modauthkerb/files/mod_auth_kerb/mod_auth_kerb-5.4/mod_auth_kerb-5.4.tar.gz/download
[root opt]# tar -xvf mod_auth_kerb-5.4.tar.gz
[root opt]# cd mod_auth_kerb-5.4
[root mod_auth_kerb-5.4]# ./configure --with-krb4=no --with-krb5=/usr/local --with-apache=/usr/local/apache2
[root mod_auth_kerb-5.4]# make
[root mod_auth_kerb-5.4]# make install

[root mod_auth_kerb-5.4]# cd ..
[root opt]# wget http://mirror.switch.ch/mirror/apache/dist/tomcat/tomcat-connectors/jk/tomcat-connectors-1.2.40-src.tar.gz
[root opt]# tar -xvf tomcat-connectors-1.2.40-src.tar.gz
[root opt]# cd tomcat-connectors-1.2.40-src/native
[root native]# ./configure --with-apxs=/usr/local/apache2/bin/apxs --enable-api-compatibility
[root native]# make
[root native]# make install

 

The module auth_mod_kerb doesn't need extra configuration but it's not the case of the mod_jk for which we will need to define several elements like log file and level, JkMount parameters which defines http requests that should be sent to the AJP connector, aso:

[root native]# cd ../..
[root opt]# vi /opt/liferay-6.1.1/tomcat/conf/mod_jk.conf
LoadModule jk_module /usr/local/apache2/modules/mod_jk.so
    JkWorkersFile /opt/liferay-6.1.1/tomcat-7.0.27/conf/workers.properties
    JkLogFile /usr/local/apache2/logs/mod_jk.log
    JkLogLevel debug
    JkLogStampFormat "[%a %b %d %H:%M:%S %Y]"
    # JkOptions indicate to send SSL KEY SIZE,
    JkOptions +ForwardKeySize +ForwardURICompat -ForwardDirectories
    # JkRequestLogFormat set the request format
    JkRequestLogFormat "%w %V %T"
    JkMount / ajp13
    JkMount /* ajp13

[root opt]# vi /opt/liferay-6.1.1/tomcat/conf/workers.properties
    # Define 1 real worker named ajp13
    worker.list=ajp13
    worker.ajp13.type=ajp13
    worker.ajp13.host=localhost
    worker.ajp13.port=8009
    worker.ajp13.lbfactor=50
    worker.ajp13.cachesize=10
    worker.ajp13.cache_timeout=600
    worker.ajp13.socket_keepalive=1
    worker.ajp13.socket_timeout=300

 

Finally, the last configuration for Apache httpd is to configure a krb5.conf file for the Kerberos client to know where the KDC is located:

[root opt]# vi /etc/krb5.conf
    [libdefaults]
        default_realm = EXAMPLE.COM

    [realms]
        EXAMPLE.COM = {
            kdc = kdc01oel.example.com:88
            admin_server = kdc01oel.example.com:749
            default_domain = example.com
        }

    [domain_realm]
        .example.com = EXAMPLE.COM
        example.com = EXAMPLE.COM

 

Once this is done, there is one step to execute on the KDC side for the configuration of Kerberos. Indeed, there is a configuration above in the file mod_kerb.conf that shows a keytab file named krb5lif.keytab. By default, this file doesn't exist so we must create it! From the KDC host server, execute the following commands to create a new service account for Liferay and then create the keytab for this service account:

[root opt]# kadmin
Authenticating as principal root/document.write(['admin','EXAMPLE.COM'].join('@')) with password.
Password for root/document.write(['admin','EXAMPLE.COM'].join('@')):  ##Enter here the root admin password##

kadmin:  addprinc HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@'))
WARNING: no policy specified for HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@')); defaulting to no policy
Enter password for principal "HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@'))":  ##Enter a new password for this service account##
Re-enter password for principal "HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@'))":  ##Enter a new password for this service account##
Principal "HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@'))" created.

kadmin:  ktadd -k /etc/krb5lif.keytab HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@'))
Entry for principal HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@')) with kvno 2, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:/etc/krb5lif.keytab.
Entry for principal HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@')) with kvno 2, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:/etc/krb5lif.keytab.
Entry for principal HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@')) with kvno 2, encryption type des3-cbc-sha1 added to keytab WRFILE:/etc/krb5lif.keytab.
Entry for principal HTTP/document.write(['lif01.example.com','EXAMPLE.COM'].join('@')) with kvno 2, encryption type arcfour-hmac added to keytab WRFILE:/etc/krb5lif.keytab.

kadmin:  exit

[root opt]# scp /etc/krb5lif.keytab document.write(['root','lif01.example.com'].join('@')):/etc/
document.write(['root','lif01.example.com'].join('@'))'s password:
krb5lif.keytab [====================================›] 100% 406 0.4KB/s 00:00
[root opt]# exit

 

From now on, all configurations required by Apache & Tomcat to handle Kerberos tickets are done. The only remaining step and certainly the most complicated is to configure Liferay to understand and use this kind of authentication. For that purpose, a Liferay Hook must be created (in eclipse using the Liferay Plugin for example). Let's name this Liferay Project created with the liferay-plugins-sdk-6.1.1: "custom-hook". For the configuration below, I will suppose that this project is at the following location: "C:/liferay-plugins-sdk-6.1.1/hooks/custom-hook/" and this location is abbreviated to %CUSTOM_HOOK%. You will find at the bottom of this blog a link to download the files that should be in this custom-hook. Feel free to use it!

 

To create a new authentication method, the first step is to create and edit the file %CUSTOM_HOOK%/docroot/WEB-INF/liferay-hook.xml as follow:

liferay-hook.png

 

Then, create and insert in the file %CUSTOM_HOOK%/docroot/WEB-INF/src/portal.properties the following lines:

    # This line defines the new auto login authentication used by Liferay
    auto.login.hooks=com.liferay.portal.security.auth.KerberosAutoLogin

 

And finally, the last step is to create the Java Class %CUSTOM_HOOK%/docroot/WEB-INF/src/com/liferay/portal/security/auth/KerberosAutoLogin with the following content. This class is used to retrieve the Kerberos principal from the Kerberos Ticket received by Apache and then transforms this principal to log the user in Liferay. Please be aware that this code can probably not be used as such because it's specific to our company: the screenName used in Liferay is equal to the principal used in the KDC. That's why there is some logger.info in the code: to help you to find the good relation between the Liferay screenName and the KDC principal.

AutoLogin.png

 

After that, just build your hook and deploy it using the liferay deploy folder (/opt/liferay-6.1.1/deploy/). If necessary, restart Apache and Liferay using the services or the control scripts:

[root opt]# /opt/liferay-6.1.1/tomcat-7.0.27/bin/shutdown.sh
[root opt]# /opt/liferay-6.1.1/tomcat-7.0.27/bin/startup.sh
[root opt]# /usr/local/apache2/bin/apachectl -k stop
[root opt]# /usr/local/apache2/bin/apachectl -f /usr/local/apache2/conf/httpd.conf

 

Wait for Liferay to start and that's it, you should be able to obtain a Kerberos Ticket from the KDC, access to Liferay (through Apache on port 80) and you should be logged in automatically. That's MAGIC!

Thanks for reading and I hope you will be able to work with Kerberos for a long long time =).

 

Custom hook download link: custom-hook.zip

MySQL high availability management with ClusterControl

Sat, 2014-09-13 03:03

Installing and managing a highly available MySQL infrastructure can be really tedious. Solutions to facilitate database and system administrator’s task exist, but few of these cover the complete database lifecycle and address all the database infrastructure management requirements. Severalnines’ product ClusterControl is probably the only solution that covers the full infrastructure lifecycle and is also able to provide a full set of functionalities required by database cluster architectures. In this article, I will show how to install, monitor and administrate a database cluster with ClusterControl.


Introduction

Severalnines is a Swedish company mostly composed of ex-MySQL AB staff. Severalnines provides automation and management software for database clusters. Severalnines’ ClusterControl perfectly fits this objective by providing a full “deploy, manage, monitor, and scale” solution. ClusterControl supports several database cluster technologies such as: Galera Cluster for MySQL, Percona XtraDB Cluster, MariaDB Galera Cluster, MySQL Cluster and MySQL Replication. However ClusterControl does not only support MySQL based cluster but also MongoDB clusters such as MongoDB Sharded Cluster, MongoDB Replica Set and TokuMX. In this article we will use Percona XtraDB Cluster to demonstrate ClusterControl functionalities.

 There are two different editions of ClusterControl: the community edition that provides basic functionalities and the enterprise edition that provides a full set of features and a really reactive support. All the details about the features of both editions can be found on the Severalnines website (http://www.severalnines.com/ClusterControl). In this article, we will detail four main global functionalities that are covered by ClusterControl:

 

1. The cluster deployment

2. The cluster management

3. The cluster monitoring

4. The scalability functionalities

 

The cluster architecture that we chose for the purpose of this article is represented in Figure 1. This cluster is composed by three Percona XtraDB nodes (green), two HAProxy nodes (red) and one ClusterControl (blue).

 

clustercontrol001.png

Figure 1: Percona XtraDB Cluster architecture


1. Cluster Deployment

As stated in the introduction, ClusterControl can manage several kinds of MySQL clusters or MongoDB clusters. The cluster deployment starts on Severalnines website on http://www.severalnines.com/configurator by choosing the kind of cluster we want to install. Once we have selected Percona XtraDB Cluster (Galera), we can select on which infrastructure we want to deploy the cluster. We can choose between on-premise, Amazon EC2 or Rackspace. Since we want to install this cluster on our own infrastructure, our choice here is “on-premise”.

Then we simply have to fill in the general settings forms by specifying parameters such as operating system, platform, number of cluster nodes, ports number, OS user, MySQL password, system memory, database size, etc., as presented in Figure 1.

 

clustercontrolsetup.png

Figure 2: General Settings


Once the general settings forms are filled in, we have to specify the nodes that belong to the Percona XtraDB cluster as well as the storage details.

The first settings are related to the ClusterControl server, the ClusterControl address and memory. There are also the details regarding the Apache settings, since the web interface is based on an Apache web server:

 

clustercontrolsetup002.png

Figure 3: ClusterControl settings


Now you can fill in the parameters related to the Percona XtraDB data nodes.

 

clustercontrolsetup003.png

Figure 4: Percona XtraDB nodes settings


Once all settings are entered, a deployment package can be automatically generated through the “Generate Deployment Script” button. We simply have to execute it on the ClusterControl server in order to deploy the cluster. Of course, it is still possible to edit the configuration parameters by editing the my.cnf file located in s9s-galera-1.0.0-/mysql/config/my.cnf.

 

[root@ClusterControl severalnines]# tar xvzf s9s-galera-percona-2.8.0-rpm.tar.gz

[root@ClusterControl severalnines]# cd s9s-galera-percona-2.8.0-rpm/mysql/scripts/install/

[root@ClusterControl install]# bash ./deploy.sh 2>&1|tee cc.log

 

The deployment package will download and install Percona XtraDB Cluster on the database hosts, as well as the ClusterControl components to manage the cluster. When the installation is successfully finalized, we can access the ClusterControl web interface via http://ClusterControl

Once logged in to ClusterControl we are able to view all database systems that are managed and monitored by ClusterControl. This means that you can have several differing cluster installations, all managed from one ClusterControl web interface.

 

clustercontrolsetup004.png

Figure 5: ClusterControl Database Clusters


Now the Percona XtraDB cluster is deployed and provides data high availability by using three data nodes. We still have to implement the service high availability and service scalability. In order to do that, we have to setup two HAProxy nodes in the frontend. Adding an HAProxy node with ClusterControl is a straightforward procedure. We would use a one-page wizard to specify the nodes to be included in the load balancing set and the node that will act as the load balancer, as presented in Figure 6.

 

clustercontrolsetup005.png

Figure 6 : Load balancer installation, using HAProxy


To avoid having a Single Point Of Failure (SPOF), it is strongly advised to add a second HAProxy node by following the same procedure as for adding the first HAProxy node. Then simply add a Virtual IP, using the “Install Keepalived” menu as presented in Figure 7.

 

clustercontrolsetup0x1.png 

 Figure 7: Virtual IP configuration using KeepAlived


2. Cluster Management 

ClusterControl offers numbers of administration features such as: Online backup scheduling, configuration management, database node failover and recovery, schema management, manual start/stop of nodes, process management, automated recovery, database user management, database upgrades/downgrades, adding and removing nodes online, cloning (for galera clusters), configuration management (independently for each MySQL node) and comparing status of different cluster nodes.

Unfortunately, presenting all these great management functionalities is not possible in the context of this article. Therefore, we will focus on backup scheduling and user, schema, and configuration management.

 

a. Backup Scheduling

As far as I remember, MySQL backup has always been a hot topic. ClusterControl offers three backup possibilities for MySQL databases: mysqldump, Percona Xtrabackup (full) and Percona Xtrabackup (incremental). Xtrabackup is a hot backup facility that does not lock the database during the backup. Scheduling the backups and having a look on performed backups is really easy with ClusterControl. It is also possible to immediately start a backup from the backup schedules’ interface. The Figure 7 presents the backup scheduling screen.

 

clustercontrolsetup007.png

Figure 8: Backup scheduling screen (retouched image for the purpose of this article)

You do not have to make a purge script to remove old backups anymore: ClusterControl is able to purge the backups after the definition of the retention period (from 0 to 365 days).

Unfortunately the restore procedure has to be managed manually since ClusterControl does not provide any graphical interface to restore a backup.

 

b. User, schema, and configuration management 

We can manage the database schemas, upload dumpfiles, and manage user privileges through the ClusterControl web interface.

 

clustercontrolsetup008.png

Figure 9: MySQL user privileges management

 

You can also change the my.cnf configuration file, apply the configuration changes across the entire cluster, and orchestrate a rolling restart – if required. Every configuration change is version-controlled.

 

clustercontrolsetup009.png

 Figure 10: MySQL Configuration management

 

New versions of the database software can be uploaded to ClusterControl, which then automates rolling software upgrades.

 

clustercontrolsetup010.png

Figure 11: Rolling upgrade through ClusterControl interface


A production cluster can easily be cloned, with a full copy of the production data, e.g. for testing purposes.

 

clustercontrolsetup011.png

Figure 12: Cloning Cluster screen


3. Cluster monitoring

With ClusterControl, you are not only able to build a cluster from scratch or get a full set of cluster management functionalities. It is also a great monitoring tool that provides you with a number of graphs and indicators, such as the list of top queries (by execution time or Occurrence), the running queries, the query histogram, CPU/Disk/Swap/RAM/Network usage, Tables/Databases growth, health check, and schema analyzer (showing tables without primary keys or redundant indexes). Furthermore, ClusterControl can record up to 48 different MySQL counters (such as opened tables, connected threads, aborted clients, etc.), present all these counters in charts, and many other helpful things that a database administrator will surely appreciate.

 

clustercontrolsetup012.png

Figure 13: Database performance graphics with time range and zoom functionalities (retouched image for the purpose of this article)


ClusterControl provides some interesting information regarding database growth for data and indexes. Figure 14 presents a chart showing the database growth since the last 26 days.

 

clustercontrolsetup013.png

Figure 14: Database growth since the last 26 days

 

ClusterControl is also able to send e-mail notifications when alerts are raised or even create custom expressions. The database administrator can also setup its own warning as well as critical thresholds for CPU, RAM, disk space, and MySQL memory usage. The following figure represents the resource usage for a given node.

 

clustercontrolsetup014.png

Figure 15: Resources usage for a Master node


Power users can set up custom KPIs, and get alerts in case of threshold breaches.

 

clustercontrolsetup015.png

 Figure 16: Custom KPIs definition

 

Health Report consists of a number of performance advisors that automatically examine the configuration and performance of the database servers, and alert in case of deviations from best practice rules.

 

clustercontrolsetup0xx.png

Figure 17: Health report with performance advisors

 

4. Scalability functionalities

Sooner or later it will be necessary to add or remove either a data node or a HAProxy node to the cluster for scalability or maintenance reasons. With ClusterControl, adding a new node is as easy as selecting the new host and giving it the role we want in the cluster. ClusterControl will automatically install the package needed for this new node and make the appropriate configuration in order to integrate it in the cluster. Of course, removing a node is just as easy.

 

clustercontrolsetup017.png

 Figure 18: New node addition and "add master" screens

 

Conclusion

With ClusterControl, Severalnines did a great job! For those who ever tried to build and administrate a highly available MySQL architecture using disparate clustering components such as heartbeat, DRBD (Data Replication Block Device), MySQL replication or any other high availability component, I am sure that you often wished to have a solution that provides a complete package. Deploying multiple clustering technologies can become a nightmare. Of course there are solutions such as MMM (Multi-Master replication Management for MySQL), but there is no solution covering the whole cluster lifecycle and offering such an amazing set of features via a nice web interface.

In addition to the great set of functionalities provided by ClusterControl, there is the Severalnines support: Their support team is amazingly efficient and reactive. The reaction time presented on the Severalnines website indicates 1 day but I never waited more than 1 hour before getting a first answer.

As stated in the introduction, there are two editions: The community edition with a limited set of functionalities is free, whereas the enterprise edition is available under a commercial license and support subscription agreement. This subscription includes ClusterControl software, upgrades, and 12 incidents per year. It is also interesting to notice that Severalnines and Percona are partners starting from this year.

 

The summary of my ClusterControl experience is presented in the table below:

 

Advantages

Drawbacks / limitation

+ Covers the whole cluster lifecycle from installation, upgrade as well as the management and monitoring phases


+ Much easier to use than many other tools that do not even provide half of the ClusterControl functionalities


+ Each operation includes a new job subscription – all operation are therefore logged


+ Amazingly reactive support!

- Does not provide backup restore functionalities


- It is not possible to acknowledge alerts or blackout targets

 

Additional information can be found on http://www.severalnines.com/blog. Since dbi services is Severalnines partner and has installed this solution at several customer sites, feel free to contact us if you have any additional question regarding ClusterControl.

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehouse

Thu, 2014-09-11 20:42

This post explains how to configure and use the new AWR warehouse functionality present in Enterprise Manager Cloud Control 12.1.0.4. This new feature offers the possibility to store date from the AWR of your main databases in the EM AWR warehouse.

As OEM AWR warehouse automatically extracts the AWR data of the database you have selected, there is no impact on your production databases. The main advantage is that it allows to keep AWR historical data beyond the retention period of the target databases. Another benefit is a performance improvement and a space gain of the target databases because AWR data are uploaded in a centralized AWR warehouse.

We have to apply a patch to get the AWR warehouse functionality available in the menu Database Performance. For Linux x86-64, you have to apply the patch 19176910 which contains the EM DB plugin bundle patch 12.1.0.6.2. Before applying of the patch the screen appears as follows:

 

aw1

 

We have to use the latest Opatchversion available for 11.1 release.

Watch out OPatch 11.2.0.x.0 version is not supported with Oracle Management Service (OMS) 12.1.0.3 and 12.1.0.4

 

oracle@vmtestoraem12c:/u01/app/oracle/MiddleWare_12cR4/oms/OPatch/ [oms12c] opatch version

OPatch Version: 11.1.0.11.0

OPatch succeeded.

 

Then we can run:

 

oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] opatchauto apply -analyze

OPatch Automation Tool

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

OPatchauto version : 11.1.0.11.0

OUI version       : 11.1.0.12.0

Running from       : /u01/app/oracle/MiddleWare_12cR4/oms

Log file location : /u01/app/oracle/MiddleWare_12cR4/oms/cfgtoollogs/opatch/opatch2014-08-25_16-45-18PM_1.log

OPatchauto log file: /u01/app/oracle/MiddleWare_12cR4/oms/cfgtoollogs/opatchauto/19176910/opatch_oms_2014-08-25_16-45-20PM_analyze.log

[Aug 25, 2014 4:46:33 PM]   Prerequisites analysis summary:

                             -------------------------------

                             The following sub-patch(es) are applicable:

                             oracle_sysman_db91:19051532

                             oracle_sysman_vt91:19060193

                             oracle_sysman_mos91:18873245

                             oracle_sysman_emas91:19051543

                             oracle_sysman_ssa91:19051528

 


We can check if any patches are installed in the OMS:


oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] opatchauto lspatches

OPatch Automation Tool

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

There are no patches installed in the OMS system.

 

Then we stop the OMS:

 

oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

 

Then we run under the patch directory : opatchauto apply

Once the patch is applied you can start the oms with the classical command : emctl start oms

Finally we have the AWR warehouse menu:

 

aw2


We have first to configure the AWR warehouse repository:

 

aw3

 

 

You select the configure button:

 

aw4

 

You enter the database and host credentials:

 

aw4

 

You configure the snapshot management for a one year retention period, you can also define the snapshot upload interval from 1 hour to 24 hours:

 

aw6

 

The caw_load_setup job_%id is successful.

Now we have access to the AWR warehouse screen where we can add databases:

 

aw7

 

When you select add, you have to define preferred credentials to the database you add and add some grants:

 

aw8

 

We have to allocate the execute privilege on dbms_swrf_internal to system:

 

SYS> grant execute on sys.dbms_swrf_internal to system;

Grant succeeded.


For the NMO error, I forgot to run the root.sh agent 12c script:


[root@vmtestoradg1 ~]# cd /u00/app/oracle/agent12c/core/12.1.0.4.0

[root@vmtestoradg1 12.1.0.4.0]# ./root.sh

Finished product-specific root actions.

/etc exist

 

Finally the databases are added:

 

aw9

 

We have to grant the access to the databases added, we select a target database and we choose Privileges:

 

aw10

 

We select the sysman administrator account:

 

aw11

 

As we have configured the snapshot upload interval to 24 hours, we will have data every day:

 

aw12

 

From this dashboard you can make ADDM comparaisons, view ADDM reports, ASH analytics or directly go to the performance page of the database you have selected.

The AWR warehouse feature requires the diagnostic pack license; This new feature seems very interesting due to the central and consolidated AWR warehouse. I will test this exciting feature deeper in the next weeks.

Documentum upgrade project - Documentum Administrator and ACS

Thu, 2014-09-11 19:15

After having installed the Documentum Administrator, one of our test was to open a document. When I opened one for the first time the content was empty although I knew it contained text. But the correct content was well displayed when I opened it a second time.

In the ucf trace file I had:

 

FINE: sun.net.www.MessageHeader@108565d6 pairs: {null: HTTP/1.1 500 Internal Server Error}{Server: Apache-Coyote/1.1}{Content-Type: text/html;charset=utf-8}{Content-Length: 3437}{Date: Mon, 27 Jan 2014 12:55:47 GMT}{Connection: close} Jan 27, 2014 1:55:47 PM com.documentum.ucf.client.logging.impl.UCFLogger error
SEVERE: Connection to ACS failed with response status : 0 Jan 27, 2014 1:55:47 PM com.documentum.ucf.client.logging.impl.UCFLogger debug
FINE: Failed processing segment java.io.IOException: Connection to the ACS failed - status : 0  at com.documentum.ucf.common.transport.impl.HTTPRemoteContentNativeConnector.checkConnectStatus(HTTPRemoteContentNativeConnector.java:460)  at com.documentum.ucf.common.transport.impl.HTTPRemoteContentNativeConnector.prepareInputStream(HTTPRemoteContentNativeConnector.java:122)
....
....
at com.documentum.ucf.common.transport.parallel.impl.ParallelDownloadWorker.run(ParallelDownloadWorker.java:48)  at java.lang.Thread.run(Unknown Source)

 

Solution

To solve the issue, I had to replace in acs.properties:

 

java.security.provider.class.name=sun.security.provider.Sun
to
java.security.provider.class.name=com.ibm.jsse2.IBMJSSEProvider

 

and

 

java.security.provider.name=SUN
to
java.security.provider.name=IBMJSSE2

 

EMC validated my workaround, logged an incident report, and reported a regression bug to be solved in future versions.

Documentum upgrade project - D2EventSenderMailMethod & bug with Patch 12

Wed, 2014-09-10 18:55

We started the Documentum upgrade in the wintertime and our jobs ran successfully by following the defined schedule. Once we moved to the summertime we hit an issue: A job that was scheduled for instance at 4:00 AM was executed at 4:00 AM, but also started every 2 minutes until 5:00 AM. We had this issue on all our 6.7SP2P009 repositories - on upgraded as well as on new repositories.

Before opening an SR in powerlink, I first checked the date and time with the following query.

On the content server using idql:

 

1> select date(now) as date_now from dm_docbase_config
2> go
date_now          
------------------------
4/9/2014 17:47:55       
(1 row affected)
1>

 

The date and time was correct, EMC confirmed a bug and asked us to install the Patch 12 which solved the issue.

  Patch 12 and D2EventSenderMailMethod

Unfortunately the patch 12 introduced a bug on D2EventSenderMailMethod which does not work anymore. The mail could not be sent out. D2EventSenderMailMethod is a requirement for D2. It is used by D2 mails but also for some workflow functionalities. By default, if the event is not managed by D2 (ie : configured) the default Documentum mail method is executed, EMC said.

To test the mail issue, I used the dm_ContentWarning job by setting the -percent_full parameter to 5 (lower than the value displayed by df -k).

In $DOCUMENTUM/dba/log//MethodServer/test67.log thefollowing error was displayed:

 

Wrong number of arguments (31) passed to entry point 'Mail'.

 

And by setting the trace flag for the dm_event_sender method we saw:


2014-05-08T12:53:45.504165      7260[7260]      0100007b8000c978        TRACE LAUNCH [MethodServer]: ./dmbasic -f./dm_event_sender.ebs -eMail  --   "test67"  "xxx May 08 12:53:25 2014"  "DM_SYSADMIN"  "Take a look at /dev/mapper/vg00-lvpkgs--it's 81% full!!!"  "ContentWarning"  "0900007b8000aeb3"  "nulldate"  "10"  "dm_null_id"  " "  "dmsd"  "test67"  "event"  " "  "test67" ""  "undefined"  "dmsd"  "1b00007b80003110"  "5/8/2014 12:53:28"  "0"  "dm_document"  "text"  "3741"  "text"  "cs.xxy.org"  "80"  ""  "/soft/opt/documentum/share/temp/3799691ad29ffd73699c0e85b792ea66"  "./dm_mailwrapper.sh"  " " dmProcess::Exec() returns: 1

 

It worked when I updated the dm_server_config object:

 

update dm_server_config objects set mail_method = 'dm_event_template_sender'

 

EMC confirmed that this is a bug and should be fixed with D2 3.1 P05

Oracle: How to move OMF datafiles in 11g and 12c

Wed, 2014-09-10 13:22

With OMF datafiles, you don't manage the datafile names. But how do you set the destination when you want to move them to another mount point? Let's see how easy (and online) it works in 12c. And how to do it with minimal downtime in 11g.

 

Testcase

Let's create a tablespace with two datafiles. It's OMF and goes into /u01:

 

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=memory;
System altered.

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata

SQL> create tablespace DEMO_OMF datafile size 5M;
Tablespace created.

SQL> alter tablespace DEMO_OMF add datafile size 5M;
Tablespace altered.

 

And I want to move those files in /u02.

 

12c online move

Here is how I generate my MOVE commands for all datafiles in /u01:

 

set echo off linesize 1000 trimspool on pagesize 0 feedback off
spool _move_omf.rcv
prompt set echo on;;
prompt report schema;;
prompt alter session set db_create_file_dest='/u02/app/oracle/oradata';;
select 'alter database move datafile '||file_id||';' from dba_data_files where file_name like '/u01/%' 
/
prompt report schema;;
spool off

 

which generates the following:

 

set echo on;
report schema;
alter session set db_create_file_dest='/u02/app/oracle/oradata';
alter database move datafile 7;
alter database move datafile 2;
report schema;

 

This works straightforward and online. That is the right solution if you are in 12c Enterprise Edition. The OMF destination is set at session level here. The move is done online, without any lock. The only overhead is that writes occured twice during the move operation. And in 12c we can run any SQL statement from RMAN, which is great.

 

11g backup as copy

How do you manage that in 11g? I like to do it with RMAN COPY. If you're in ARCHIVELOG then you can copy the datafiles one by one: backup it as copy, offline it, recover it, switch to it, online it. This is the fastest way. You can avoid the recovery step by putting the tablespace offline but:

  • you will have to wait that the earliest transaction finishes.
  • your downtime includes the whole copy. When activity is low the recovery is probably faster.

 

Here is how I generate my RMAN commands for all datafiles in /u01:

 

set echo off linesize 1000 trimspool on pagesize 0 feedback off
spool _move_omf.rcv
prompt set echo on;;
prompt report schema;;
with files as (
 select file_id , file_name , bytes from dba_data_files where file_name like '/u01/%' and online_status ='ONLINE' 
)
select stmt from (
select 00,bytes,file_id,'# '||to_char(bytes/1024/1024,'999999999')||'M '||file_name||';' stmt from files
union all
select 10,bytes,file_id,'backup as copy datafile '||file_id||' to destination''/u02/app/oracle/oradata'';' stmt from files
union all
select 20,bytes,file_id,'sql "alter database datafile '||file_id||' offline";' from files
union all
select 30,bytes,file_id,'switch datafile '||file_id||' to copy;' from files
union all
select 40,bytes,file_id,'recover datafile '||file_id||' ;' from files
union all
select 50,bytes,file_id,'sql "alter database datafile '||file_id||' online";' from files
union all
select 60,bytes,file_id,'delete copy of datafile '||file_id||';' from files
union all
select 90,bytes,file_id,'report schema;' from files
union all
select 91,bytes,file_id,'' from files
order by 2,3,1
)
/

 

which generates the following:

 

set echo on;
report schema;
#          5M /u01/app/oracle/oradata/DEMO/datafile/o1_mf_demo_omf_b0vg07m8_.dbf;
backup as copy datafile 2 to destination'/u02/app/oracle/oradata';
sql "alter database datafile 2 offline";
switch datafile 2 to copy;
recover datafile 2 ;
sql "alter database datafile 2 online";
delete copy of datafile 2;
report schema;

 

(I have reproduced the commands for one datafile only here.)

And I can run it in RMAN. Run it as cmdfile or in a run block so that it stops if an error is encountered. Of course, it's better to run them one by one and check that the datafiles are online at the end. Note that it does not concern SYSTEM tablespace for which the database must be closed.

Online datafile move is my favorite Oracle 12c feature. And it's the first new feature that you will practice if you come at our 12c new features workshop. And in any versions RMAN is my preferred way to manipulate database files.

Resize your Oracle datafiles down to the minimum without ORA-03297

Mon, 2014-09-08 06:03

Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE ... RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent.

But if you try to get lower, you will get:

ORA-03297: file contains used data beyond requested RESIZE value

So, how do you find this minimum value, which is the datafile's high water mark?
You have the brute solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one.

Or there is the smart solution: find the datafile high water mark.

You can query DBA_EXTENTS to know that. But did you try on a database with a lot of datafiles? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don't need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.
Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablepaces. And you have to read all of them.

Here is my query:

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

and here is a sample output:

/* reclaim    3986M from    5169M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf' resize 1183M;
/* reclaim    3275M from   15864M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf' resize 12589M;
/* reclaim    2998M from    3655M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf' resize 657M;
/* reclaim    2066M from    2250M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf' resize 185M;
/* reclaim     896M from    4000M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf' resize 3105M;

You get directly the resize statements, with the reclaimable space in comments.

A few remarks about my query:

  • I generate the resize statements only for datafiles which are autoextensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.
  • When datafile is not autoextensible, or maxsize is not higher than the current size, I only generate a comment.
  • When a datafile has no extents at all I generate a resize to 5MB. I would like to find the minimum possible size (without getting ORA-3214) but my test do not validate yet what is documented in MOS. If anyone has an idea, please share.
  • There is probably a way to get that high water mark in a cheaper way. Because the alter statement gives the ORA-03297 much quicker. Information is probably available in the datafile headers, without going to segment headers, but I don't know if it is exposed in a safe way. If you have an idea, once again, please share.

Note that I'm using that query for quite a long time. I even think that it was my first contribution to Oracle community on the web, about 9 years ago, in the dba-village website. Since then my contribution has grown to forums, blogs, articles, presentations, ... and tweets. Sharing is probably addictive ;)

Documentum upgrade project - sharing experience

Fri, 2014-09-05 06:01

A few weeks ago, we finished a Documentum upgrade at a customer site. Now, after having analyzed and made a status point (we do that after each upgrade project to optimize our procedures and skills) it is time to share our experiences. In this post and in the ones that will follow we will describe which issues we were confronted with, which bugs we discovered, and how we proceed to go forward to have a running environment at the end - and of course, a satisfied and happy customer!

 

Aim of the project

We had to upgrade four environments; development, test, clone, and prod. We had to upgrade or do new installations for the following components: Content Server from 6.5SP3 to 6.7SP2, D2 3.0 to D2 3.1SP1, DTS 6.5SP3 to ADTS 6.7SP2, DA 6.5SP3 to DA6.7SP2, DFS6.7SP2, xPlore 1.4

 

Upgrade plan

We first started with the Dev environment to define our processes. Then, we built a clone of the production repositories to validate the upgrade process, to do the test, and to validate the xPlore high availability installation. The xPlore HA has been covered with the installation of two xPlore instances on different physical servers, and the use of a DSearch URL configured in F5 (master-slave and not in load balancing mode) for the search. With all that, not only has the xPlore installation been validated, but the upgrade process could also be run faster as we had to index only a gap of objects on the day we did the production upgrade. But let's wait for a future blog where this will be described.

Issues/bugs summary

So first of all, I will summarize the issues/bugs we had to deal with. The details will be published in the next blogs.

On the content server side:

- We discovered a bug when we moved to the summer time; for instance a job that had to run at 4:00 AM started indeed at four o'clock but it also started every two minutes until 5 AM.

- D2EventSenderMailMethod does not work anymore since we installed the patch 12 for the Content Server 6.7SP2.

On ADTS:

- Some ADTS processes randomly stopped working using a domain user as the installation owner.

On DA:

- Documents could not be downloaded.

On the D2-Client:

- Facets are configured in xPlore, but date attributes cannot be listed by date.

- We had some difficulties to implement the SSO based on NTLM. As we use D2 3.1SP1 which is in my point of view a 4.x version, we did not know exactly which documentation to use. Even the EMC support had to investigate to find some information.

- The ActiveX for D2 3.1SP1 could not be installed.

- SSO and D2DFS.

- Some icons where missing in D2-Client.

 

So let's see what will be posted in the next days/weeks, which I hope will help people in their upgrade projects.

The title of the related blogs will begin with "Documentum upgrade project".

Enjoy the reading!

What's the consequence of NOLOGGING loads?

Thu, 2014-09-04 09:25

When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you've loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish?
I encountered recently the situation but - probably because of a bug - the result was not exactly what I expected. Of course, before saying that it's a bug I need to clear any doubt about what I think is the normal situation. So I've reproduced the normal situation and I'm sharing it here in case someone wants to see how to handle it.

First, let me emphasize something that is very important. I didn't say that you can loose the data that you've loaded. You loose the data which were in the blocks that have been allocated by your load. It may concern conventional DML happening long time after the nologging load. And anyway, you probably loose the whole table (or partition) because as you will see the proper way to recover from nologging recovery is to truncate the table (or partition).

I'm in 12c so I can run my SQL statements from RMAN. I create a DEMO tablespace and a 1000 rows table in it:

RMAN> echo set on

RMAN> create tablespace DEMO datafile '/tmp/demo.dbf' size 10M;
Statement processed

RMAN> create table DEMO.DEMO pctfree 99 tablespace DEMO nologging as select * from dual connect by level commit;
Statement processed

Imagine that I've a media failure and I have to restore my tablespace:

RMAN> alter tablespace DEMO offline;
Statement processed


RMAN> restore tablespace DEMO;
Starting restore at 04-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK

creating datafile file number=2 name=/tmp/demo.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 04-SEP-14

and recover up to the point of failure:

RMAN> recover tablespace DEMO;
Starting recover at 04-SEP-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 04-SEP-14

RMAN> alter tablespace DEMO online;
Statement processed

Then here is what happen when I want to query the table where I've loaded data without logging:

RMAN> select count(*) from DEMO.DEMO;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/04/2014 16:21:27
ORA-01578: ORACLE data block corrupted (file # 2, block # 131)
ORA-01110: data file 2: '/tmp/demo.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Let's see that:

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     167            974          1280            6324214
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              306

Finished validate at 04-SEP-14

167 blocks have been marked as corrupt.
The solution is to truncate the concerned table.
And if you don't know what are the tables that are concerned then you need to check v$database_block_corruption and dba_extents. So, my advise is that the tables loaded in NOLOGGING should be documented in the recovery plan, with the way to reload the data. Of course, that's not an easy task because NOLOGGING is usually done by developers and recovery is done by the DBA. The other alternative is to prevent any NOLOGGING operation and put the database in FORCE LOGGING. In a Data Guard configuration, you should do that anyway.

So I truncate my table:

RMAN> truncate table DEMO.DEMO;
Statement processed

and if I check my tablespace, I still see the blocks as 'Marked Corrupt':

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     167            974          1280            6324383
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              306

Finished validate at 04-SEP-14

This is the normal behaviour. The blocks are still marked as corrupt until they are formatted again.

I put back my data;

RMAN> insert /*+ append */ into DEMO.DEMO select * from dual connect by level commit;
Statement processed

And check my tablespace again:

RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              974          1280            6324438
  File Name: /tmp/demo.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              167
  Index      0              0
  Other      0              139

Finished validate at 04-SEP-14

The 167 corrupted blocks have been reused, now being safe and containing my newly loaded data.
This is the point I wanted to validate because I've seen a production database where the blocks remained marked as corrupted. The load has allocated exents containing those blocks but, fortunately, has avoided to put rows in it. However, monitoring is still reporting corrupt blocks and we have to fix that as soon as we can move the tables to another tablespace.

Last point. If you want to see if some tablespace had NOLOGGING operations since the last backup, run:

RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
2    full                    /tmp/demo.dbf

This is an indication that you should backup that datafile now. Knowing the objects concerned if a lot more complex...

I'll not open a SR as I can't reproduce the issue I encountered (corrupt flag remaining after reallocating blocks) but if anyone had that kind of issue, please share.

How to remap tablespaces using Oracle Import/Export Tool

Tue, 2014-08-26 02:04

Since Oracle 10g, Oracle provides a great tool to import and export data from databases: Data Pump. This tool offers several helpful options, particularly one that allows to import data in a different tablespace than the source database. This parameter is REMAP_TABLESPACE. However, how can you do the same when you cannot use Data Pump to perform Oracle import and export operations?

I was confronted with this issue recently, and I had to deal with different workarounds to accomplish my import successfully. The main case where you will not be able to use Datapump is when you want to export data from a pre-10g database. And believe me, there are still a lot more of these databases running over the world than we think! The Oracle Import/Export utility does not provide a built-in way to remap tablespace like Datapump. In this blog posting, I will address the different workarounds to import data in a different tablespace with the Oracle Import Export Tool.

I have used an Oracle 11g R2 database for all examples.

My source schema 'MSC' on the Oracle Database DB11G is using the default tablespace USERS. I want to import the data to a different schema 'DBI' using the USER_DATA tablespace.

The objects contained in the source schema are as follows:

 

SQL> select object_type, object_name from dba_objects where owner='MSC';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
SEQUENCE            MSCSEQ
TABLE               USER_DATA
TABLE               USER_DOCUMENTS
INDEX               SYS_IL0000064679C00003$$
LOB                 SYS_LOB0000064679C00003$$
INDEX               PK_DATAID
INDEX               PK_DOCID

 

I will now export the schema MSC using exp:

 

[oracle@srvora01 dpdump]$ exp msc/Passw0rd file=exp_MSC.dmp log=exp_MSC.log consistent=y
Export: Release 11.2.0.3.0 - Production on Tue Aug 12 14:40:44 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MSC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MSC
About to export MSC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MSC's tables via Conventional Path ...
. . exporting table                      USER_DATA      99000 rows exported
. . exporting table                 USER_DOCUMENTS         25 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

 

I tried different ways to accomplish the tablespace remapping with imp. They are summarized below.

 

Revoke quota on USERS tablespace for the destination schema

I have read somewhere that a workaround could be to revoke UNLIMITED TABLESPACE (if granted) and any quota on USERS tablespace for the destination schema, and to grant UNLIMITED QUOTA on the target tablespace only (USER_DATA). This way, imp tool is supposed to import all objects into the schema default tablespace.

Let's try this. I have created the destination schema with the right default tablespace and temporary tablespace and the required privileges:

 

SQL> create user DBI identified by Passw0rd default tablespace USER_DATA temporary tablespace TEMP quota unlimited on USER_DATA;
User created.

 SQL> grant create session, create table to DBI;
Grant succeeded.

 

The privilege UNLIMITED TABLESPACE is not granted to DBI user. Now, I will try to run the import:

 

[oracle@srvora01 dpdump]$ imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 14:53:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
IMP-00017: following statement failed with ORACLE error 1950:
 "CREATE TABLE "USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE" VARCHAR2(25), ""
 "DOC_VALUE" BLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE "
 "AS BASICFILE  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIO"
 "N  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
Import terminated successfully with warnings.

 

The import has worked only for a part of the objects. As we can see, it seems that the table app_documents, containing a BLOB column, has not been imported. All objects associated to this table are not imported:

 

SQL> select object_type, object_name from dba_objects where owner='DBI';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
TABLE               USER_DATA
SEQUENCE            MSCSEQ
INDEX               PK_DATAID

 

With no quota on the source tablespace, imp tool imports data in the target schema default tablespace, but LOBS are not supported.

 

Drop USERS tablespace prior to import data

Another method could be to drop the source tablespace, to be sure that the import tool does not try to import data in the USERS tablespace.

In this example, I will drop the USERS tablespace and try to import data again with the same command. Note that I have previously dropped the MSC and DBI schemas prior to dropping the USERS tablespace.

 

SQL> alter database default tablespace SYSTEM;
Database altered.

 

SQL> drop tablespace USERS including contents and datafiles;
Tablespace dropped.

 

I will now recreate the empty DBI schema, as shown in example 1:

 

SQL> create user DBI identified by Passw0rd default tablespace USER_DATA temporary tablespace TEMP quota unlimited on USER_DATA;
User created. 

 

SQL> grant create session, create table to DBI;
Grant succeeded.

 

Now let's try to import the data again from the dump file:

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 17:03:50 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE" VARCHAR2(25), ""
 "DOC_VALUE" BLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE "
 "AS BASICFILE  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIO"
 "N  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
Import terminated successfully with warnings.

 

You can see that we have still have an error when importing the USER_DOCUMENTS table, but this time the error says "Tablespace USERS does not exist". So, whatever we do, imp tool tries to import LOBS in the same tablespace. But other data is imported in the new DEFAULT tablespace of the schema.

We can say that imp has the same behavior no matter whether we revoke the quota on the source tablespace or whether we drop it. Clearly, LOBs are not supported by this method. But if you have a database with standard data, these two methods would help you to remap the tablespace at import time.

 

Pre-create objects in the new tablespace using the INDEXFILE option

Imp tool provides the option INDEXFILE which corresponds to the METADATA ONLY with expdp. There is one difference: while impdp directly creates object structures with METADATA_ONLY=Y in the database without any data, imp with the INDEXFILE option will just generate an sql file with all CREATE statements (tables, indexes etc.) and you will have to manually run this file with sqlplus to create the empty objects.

As you may expect (or not), this SQL file will allow you to change the tablespace name when objects have to be created, prior to importing data. The inconvenience is that several manual steps are involved in this workaround - the solution is described below.

 

1) Generate the SQL file

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi indexfile=imp_to_DBI.sql
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 18:04:13 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. . skipping table "USER_DATA"
. . skipping table "USER_DOCUMENTS"
Import terminated successfully without warnings.

 

2) Edit the SQL file

 

In this file, you will have two modifications to do. First, remove all REM keywords from the CREATE statements. All rows are created as a comment in the file. Then, change the tablespace name for all objects you want to create on a different tablespace.

This is how my SQL file looks like after the modifications:

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] cat imp_to_DBI.sqlCREATE TABLE "DBI"."USER_DATA" ("DATA_ID" NUMBER, "DATA_VALUE"
VARCHAR2(250)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 16777216 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING
NOCOMPRESS ;
REM  ... 99000 rows
CONNECT DBI;
CREATE UNIQUE INDEX "DBI"."PK_DATAID" ON "USER_DATA" ("DATA_ID" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS
1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"
LOGGING ;
ALTER TABLE "DBI"."USER_DATA" ADD CONSTRAINT "PK_DATAID" PRIMARY KEY
("DATA_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING
ENABLE ;
CREATE TABLE "DBI"."USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE"
VARCHAR2(25), "DOC_VALUE" BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"
LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE AS BASICFILE (TABLESPACE
"USER_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM  ... 25 rows
CREATE UNIQUE INDEX "DBI"."PK_DOCID" ON "USER_DOCUMENTS" ("DOC_ID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"USER_DATA" LOGGING ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ADD CONSTRAINT "PK_DOCID" PRIMARY
KEY ("DOC_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING ENABLE ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ADD CONSTRAINT "FK_DOCID" FOREIGN
KEY ("DOC_ID") REFERENCES "USER_DATA" ("DATA_ID") ENABLE NOVALIDATE ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ENABLE CONSTRAINT "FK_DOCID" ;

 

3) Execute the SQL file with SQL Plus

 

Simply use SQL Plus to execute the SQL file (the user DBI must exist prior to running the script):

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] sqlplus / as sysdba @imp_to_DBI.sql
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 18:13:14 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Table created.

 

Enter password:
Connected.

 

Index created.

 

Table altered.

 

Table created.

 

Index created.

 

Table altered.

 

Table altered.

 

Table altered.

 

SQL> select object_type, object_name from user_objects;
OBJECT_TYPE         OBJECT_NAME
------------------- -----------------------------------
INDEX               PK_DATAID
TABLE               USER_DOCUMENTS
TABLE               USER_DATA
INDEX               SYS_IL0000064697C00003$$
LOB                 SYS_LOB0000064697C00003$$
INDEX               PK_DOCID

 

4) Disable all constraints

 

When importing data with imp or impdp tools, constraints are created and enabled at the end of the import. This allows Oracle to import data without taking into account any referential integrity constrainst. As we already have created empty objects with enabled constraints, we must manually disable the constraints before importing the data.

 

SQL> select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';' "SQL_CMD" from DBA_CONSTRAINTS WHERE OWNER='DBI';
SQL_CMD
-----------------------------------------------------------
ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT FK_DOCID;
ALTER TABLE DBI.USER_DATA DISABLE CONSTRAINT PK_DATAID;
ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT PK_DOCID;

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT FK_DOCID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DATA DISABLE CONSTRAINT PK_DATAID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT PK_DOCID;
Table altered.

 

5) Import the data with the IGNORE=Y option

 

Now we must import the data from the dump file using the IGNORE=Y option to ignore warnings about already existing objects. It will allow the imp tool to load data to the empty tables and indexes. Additionaly, I have set the CONSTRAINTS=N option because imp tried to enable the constraints after the import, which was generating an error...

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi ignore=y constraints=n
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 19:43:59 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
. . importing table               "USER_DOCUMENTS"         25 rows imported
About to enable constraints...
Import terminated successfully without warnings.

 

All objects have been imported successfully:

 

SQL> select object_type, object_name from dba_objects where owner='DBI';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
INDEX               PK_DATAID
TABLE               USER_DATA
TABLE               USER_DOCUMENTS
INDEX               SYS_IL0000064704C00003$$
LOB                 SYS_LOB0000064704C00003$$
INDEX               PK_DOCID
SEQUENCE            MSCSEQ

 

6) Enable constraints after the import

 

Constraints previously disabled must be enabled again to finish the import:

 

SQL> select constraint_name, status from dba_constraints where owner='DBI';CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DOCID                       DISABLED
PK_DOCID                       DISABLED
PK_DATAID                      DISABLED

 

SQL> select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE CONSTRAINT ' || CONSTRAINT_NAME || ';' "SQL_CMD" from DBA_CONSTRAINTS WHERE OWNER='DBI';
SQL_CMD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT FK_DOCID;
ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT PK_DOCID;
ALTER TABLE DBI.USER_DATA ENABLE CONSTRAINT PK_DATAID;

 

Enable PRIMARY KEYS:

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT PK_DOCID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DATA ENABLE CONSTRAINT PK_DATAID;
Table altered.

 

And then FOREIGN KEY:

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT FK_DOCID;
Table altered.

 

Constraints are now enabled:

 

SQL> select constraint_name, status from dba_constraints where owner='DBI';CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DOCID                       ENABLED
PK_DATAID                      ENABLED
PK_DOCID                       ENABLED

 

We do not have to carry on quota on tablespaces here. As you can see, even if I have recreated my USERS tablespace prior to importing the data with the INDEXFILE option, the USERS tablespace contains no segment after the import:

 

SQL> select segment_name from dba_segments where tablespace_name='USERS';
no rows selected

 

Conclusion

Workaround 1 and 2, which are very similar, are simple and fast methods to remap tablespaces for import into a database without any LOB data. But in the presence of LOB data, the workaround 3 is the right method to successfully move every object of the database into the new database. The major constraint of this workaround is that you will have to manually edit an SQL file, which can become very fastidious if you have several hundred or thousand of objects to migrate...

It is also possible to first import the data in the same tablespac, and then use the MOVE statement to move all objects into the new tablespac. However, you may not be able to move ALL objects this way. Workaround 3 seems to be the best and "cleanest" way to do it.

Oracle 12.1.0.2.1 Set to Join Conversion

Thu, 2014-08-21 01:17

Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it's there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that's not a typo!).

Yes, that number looks like the future PSU for the 12c Release 1 Patchset 1 that was available recently and has no PSU yet. Lost in the release numbers? No problem. This is only default values for the _convert_set_to_join paramter but you can also use the hint to get that transformation, which is available in previous versions as well.

So what does this transformation? It transforms an INTERSECT or MINUS into a join. When the tables are large but the result is small, that transformation can bring new access path avoiding full table scans and deduplication for each branch. And thanks to the Partial Join Evaluation the performance is even better in 12c. Let's look at an example.

 


SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 27 22:10:57 2014

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


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

SQL> create table DEMO1(n constraint DEMO1_N primary key) as select rownum n from (select * from dual connect by level  Table created.

SQL> create table DEMO2(n constraint DEMO2_N primary key) as select rownum n from dual connect by level  Table created.

SQL> alter session set statistics_level=all;
Session altered.

 

So I have two tables, one with 100000 rows and one with only 10. And I want the rows from DEMO1 which are not in DEMO2:

 

SQL> alter session set optimizer_features_enable='12.1.0.2.1';
Session altered.

SQL> select * from DEMO1 intersect select * from DEMO2;

         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

 

Let's have a look at the plan:

 

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 9fpg8nyjaqb5f, child number 0
-------------------------------------
select * from DEMO1 intersect select * from DEMO2

Plan hash value: 4278239763

------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows || Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 ||          |
|   1 |  INTERSECTION       |         |      1 |        |     10 ||          |
|   2 |   SORT UNIQUE       |         |      1 |    100K|    100K|| 4078K (0)|
|   3 |    TABLE ACCESS FULL| DEMO1   |      1 |    100K|    100K||          |
|   4 |   SORT UNIQUE NOSORT|         |      1 |     10 |     10 ||          |
|   5 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 ||          |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   3 - SEL$1 / DEMO1@SEL$1
   5 - SEL$2 / DEMO2@SEL$2

 

This is the expected plan. There is an INTERSECTION operation that implements our INTERSECT. But look: each branch had to be deduplicated (SORT UNIQUE). Note that the SORT UNIQUE NOSORT has a funny name - it's just a SORT UNIQUE that doesn't have to sort because its input comes from an index. Each branch had to read all the rows. Look at the big table: we read 100000 rows and use 4MB of memory to sort them in order to deduplicate them. But it's an intersection and we have a small table that has only 10 rows. We know that the result cannot be large. Then a more efficient way would be to read the small table and for each row check if they are in the big one - through an index access. We still have to deduplicate, but we do that at the end, on the small rowset.

And this is exactly what the Set to Join Conversion is doing. Let's force it with a hint:

 

SQL> select /*+ SET_TO_JOIN(@"SET$1") */ * from DEMO1 intersect select * from DEMO2;

         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID        01z69x8w7fmu0, child number 0
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from DEMO1 intersect select *
from DEMO2

Plan hash value: 169945296

------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 |
|   1 |  SORT UNIQUE NOSORT |         |      1 |     10 |     10 |
|   2 |   NESTED LOOPS SEMI |         |      1 |     10 |     10 |
|   3 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 |
|*  4 |    INDEX UNIQUE SCAN| DEMO1_N |     10 |    100K|     10 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEMO1"."N"="DEMO2"."N")

 

The intersect has been transformed to a join thanks to the Set to Join transformation, and the join has been transformed to a semi-join thanks to the Partial Join Evaluation transformation. The result is clear here:

  • No full table scan on the big table because the join is able to access with an index
  • No deduplication which needs a large workarea
  • The join can stop as soon as one row matches thanks to the semi-join
  • Deduplication occurs only on result, which is small. And here it does not even require a workarea because the rows comes sorted from the index.

 

We can see the SET_TO_JOIN and PARTIAL_JOIN hints in the outline:

 

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      INDEX(@"SEL$02B15F54" "DEMO2"@"SEL$2" ("DEMO2"."N"))
      INDEX(@"SEL$02B15F54" "DEMO1"@"SEL$1" ("DEMO1"."N"))
      LEADING(@"SEL$02B15F54" "DEMO2"@"SEL$2" "DEMO1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "DEMO1"@"SEL$1")
      PARTIAL_JOIN(@"SEL$02B15F54" "DEMO1"@"SEL$1")
      END_OUTLINE_DATA
  */

 

So we are in 12.1.0.2 and we need a hint for that. Let's go to 12.1.0.2.1 (which implicitely set _convert_set_to_join=true).

 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID        9fpg8nyjaqb5f, child number 1
-------------------------------------
select * from DEMO1 intersect select * from DEMO2

Plan hash value: 118900122

------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows || Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 ||          |
|   1 |  HASH UNIQUE        |         |      1 |     10 |     10 || 1260K (0)|
|   2 |   NESTED LOOPS SEMI |         |      1 |     10 |     10 ||          |
|   3 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 ||          |
|*  4 |    INDEX UNIQUE SCAN| DEMO1_N |     10 |    100K|     10 ||          |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEMO1"."N"="DEMO2"."N")

Note
-----
   - this is an adaptive plan

 

Ok, we have the Set to Join Conversion here in 12.1.0.2.1

But don't you see another difference?
.
.
.
.
The deduplication needs a workarea here. It is not a NOSORT operation - even if the result comes from the index. It seems that the CBO cannot guarantee that the result comes sorted. The clue is in the execution plan note.

But that's for a future blog post.

How to find the SQL Server Management Pack in the SCOM database

Wed, 2014-08-20 00:05

While working on a customer project, I needed to retrieve the SQL Server SCOM Management Pack version. Unfortunately, the SCOM team took a while to answer and as I am the DBA, I have access to the SCOM databases. So let’s have a look at how we can get this the "hard" way.

Deploying a SQL Server database to Azure

Tue, 2014-08-12 18:46

Deploying an SQL Server database to a Windows Azure virtual machine is a feature introduced with SQL Server 2014. It can be useful for an organization  that wants to reduce its infrastucture management, simplify the deployment, or have a fast virtual machine generation.

 

Concept

This new feature is a wizard which allows either to copy or migrate an On-Premise SQL Server database to a Windows Azure virtual machine.

The following schema explains the main process of the feature:
 PrtScr-capture_20.pngAn existing SQL Server instance is present on an On-Premise machine of an organization, hosting one or several user databases.

Once the new feature is used, a copy of the on-premise database will be available on the SQL Server instance in the Cloud.

 

Prerequisites

Azure Account Creation

Obviously, the first requirement is an Azure account! To create an Azure account, go to the official Azure website.

 

Azure Virtual Machine Creation

Once the Azure account has been set, a Windows Azure virtual machine has to be created.

There are two ways to create a Virtual Machine: by a "Quick Create" or by a "Custom Create". It is recommended to perform a "Custom Create" (from gallery) because it offers more flexibility and control on the creation.

 

PrtScr-capture_20_20140730-120018_1.png

 

This example is done with “FROM GALLERY”. So the creation of the virtual machine will be performed with a wizard in four steps.

 

The first step of the wizard is the selection of the virtual machine type.

Unlike prejudices, Windows Azure offers a large panel of virtual machines, which do not only come from the Microsoft world.

See more details about Virtual Machines on the Microsoft Azure website.


 

PrtScr-capture_20_20140730-120252_1.png

The targeted feature is only available on SQL Server 2014, so a virtual machine including SQL Server 2014 has to be created. This example will be made with a Standard edition to have the more restrictive edition of SQL Server 2014.

The first step of the wizard is configured as follows: 


 PrtScr-capture_20_20140730-120551_1.png

For this example, default settings are used. Indeed the configuration of the virtual machine is not the main topic here, and can change depending on one’s need.

The release date from June is selected, so the SP1 will be included for SQL Server 2014.

The "Standard Tier" is selected so load-balancer and auto-scaling will be included. See more details about Basic and Standard Tier on the Microsoft Azure website.

The virtual machine will run with 2 cores and 3.5 GB memory, which will be enough for this demo. See more details about prices and sizes on the Microsoft Azure website.

The virtual machine is named “dbiservicesvm” and the first (admin) user is “dbiadmin”.

 

The second step of the wizard is configured as follows:

 

PrtScr-capture_20_20140730-121233_1.png

 

The creation of a virtual machine in Azure requires a cloud service, which is a container for virtual machines in Azure. See more details about Cloud Service on the Microsoft Azure website.

Furthermore, a storage account ("dbiservices") and an affinity group ("IT") are also required to store the disk files of the virtual machine. To create a storage account and an affinity group, see the Azure Storage Account Creation part from a previous blog.

 

The third step of the wizard is configured as follows:

 

PrtScr-capture_20_20140730-121705_1.png

 

This screen offers the possibility to install extensions for the virtual machine. Virtual machine extensions simplify the virtual machine management. By default, VM Agent is installed on the virtual machine.

The Azure VM Agent will be responsible for installing, configuring and managing VM extensions on the virtual machine.

For this example, VM extensions are not required at all, so nothing is selected.

 

Security

At the SQL Server level, an On-Premise SQL user with Backup Operator privilege is required on the targeted Azure database. Of course the user must be mapped with a SQL Server login to be able to connect to the SQL Server instance.

If the user does not have the Backup Operator privilege, the process will be blocked at the database backup creation step:

 

PrtScr-capture_20_20140730-122146_1.png

 

Moreover, an Azure account with Administrator Service privilege linked to the subscription containing the Windows Azure virtual machine is also required. Without this account, it is impossible to retrieve the list of Windows Azure virtual machines available on the Azure account, so the SQL Server Windows Azure virtual machine created previously.

Finally, the end point of the Cloud Adapter must be configured to access the virtual machine during the execution of the wizard. If not, the following error message will occur:

 

PrtScr-capture_201.png

 

The Cloud Adapter is a service which allows the On-Premise SQL Server instance to communicate with the SQL Server Windows Azure virtual machine. More details about Cloud Adapter for SQL Server on TechNet.

To configure the Cloud Adapter port, the Azure administrator needs to access to the Endpoints page in the SQL Server Windows Azure virtual machine on the Azure account. Then a new endpoint needs to be created as follows:

 

PrtScr-capture_201_20140730-122732_1.png

 

Now, the SQL Server Windows Azure virtual machine allows to connect to the Cloud Adapter port.

 

The credentials of a Windows administrator user are also required on the Windows Azure virtual machine to connect to the virtual machine in Azure. This administrator also needs a SQL Server login. If these two requirements are not met, the following error message will occur:

 

PrtScr-capture_201_20140730-122929_1.png

 

The login created in the SQL Server instance in Azure must also have the dbcreator server role, otherwise the following error message will occur:

 

PrtScr-capture_201_20140730-123033_1.png

 

 

Deploy a database to the Windows Azure VM wizard

Launch the wizard

The wizard can be found with a right-click on the targeted database, then "Tasks" and finally "Deploy Database to a Windows Azure VM…"

 

PrtScr-capture_201_20140730-132221_1.png

 

In fact, it does not matter from which user databases the wizard is launched, because the wizard will ask to connect to an instance, and then it will ask to select a database.

 

Use the wizard

The first step is an introduction of the wizard, which is quite without interests.

 

The second step needs three fields: the SQL Server instance, the SQL Server database and the folder to store the backup files.

 

PrtScr-capture_201_20140730-132446_1.png

 

The "SQL Server" field is the SQL Server instance hosting the SQL Server database which is planned to be deployed to Azure.

The "Select Database" field must obviously reference the database to deploy.

 

The third step needs two fields: the authentication to the Azure account and the selection of the subscription.

 

PrtScr-capture_201_20140730-132747_1.png

 

After clicking on the Sign in… button, a pop-up will require the Administrator privilege from the Azure account.

As soon as the credentials are entered to connect to the Azure account, a certificate will be generated.

If several subscriptions are linked to the Azure account, select the correct subscription ID. In this example, there is only one subscription linked to the Azure account.

For more information, see what’s the difference between an Azure account and a subscription on TechNet.

 

The fourth step of the wizard is divided in several parts. First, there is the virtual machine selection, as follows:

 

PrtScr-capture_201_20140730-133036_1.png

 

The cloud service ("dbiservicescloud") needs to be selected. Then, the virtual machine ("dbiservicesvm") can be selected.

 

Credentials to connect to the virtual machine must be provided, as follows:

 

PrtScr-capture_201_20140730-133421_1.png

 

The SQL Server instance name and the database name need to be filled, as follows:

 

PrtScr-capture_201_20140730-133756_1.png

 

Finally, all information are filled for the wizard, the deployment can be launched. The deployment finished successfully!

 

After the deployment

First, the On-Premise database used for the deployment is still present on the SQL Server instance.

 

PrtScr-capture_201_20140730-134024_1.png

 

Then, the folder used to store the temporary file is still present. In fact, this is a "bak" file because the mechanism behind the wizard is a simple backup and restore of the database from an On-Premise SQL Server instance to an Azure SQL Server instance.

 

PrtScr-capture_201_20140730-134116_1.png

 

So do not forget to delete the bak file after the deployment because this file uncessarly fills your storage space for big databases.

 

Finally, the deployed database can be found on the Azure SQL Server instance!

 

PrtScr-capture_201_20140730-134220_1.png

Limitations

The first limitation of this new feature is the database size of this operation: it cannot exceed 1 TB.

Moreover, it does not support hosted services that are associated with an Affinity Group.

The new feature does not allow to deploy all versions of SQL Server database. Indeed, only SQL Server 2008 database versions or higher are allowed to be deployed.

 

Similar features

If this new feature, for any reason , does not meet the organization’s need, two similar features exist.

 

SQL database in Azure

This feature introduced with SQL Server 2012 is quite similar to the feature presented in this blog, because the SQL Server database and the Windows Azure virtual machine are hosted on Azure.

However, the management of the infrastructure is much more reduced: no virtual machine management nor sql server management. A good comparison between these two functionalities is available: Choosing between SQL Server in Windows Azure VM & Windows Azure SQL Database.

More details about SQL Database on Azure.

 

SQL Server data files in Azure

This is a new feature introduced with SQL Server 2014, which allows to store data files from the SQL Server database in Azure Blob storage, but the SQL Server instance runs On-Premise.

It simplifies the migration process, reduces the On-Premise space storage and management, and simplifies the High Availability and recovery solutions…

More details about SQL Server data files in Azure on TechNet.

 

Conclusion

With this feature, Microsoft simplifies the SQL Server database deployment process from On-Premise to Azure.

Azure is a rather attractive and interesting tool that is highly promoted by Microsoft.