Yann Neuhaus

I am a “new joiner” at dbi services and had the opportunity to attend the IT Pro day at the Microsoft Techdays in Lausanne. Let me give you a few insights on my day there. Being a SharePoint specialist but also ramping up on Microsoft SQL Server I tried to balance between SQL and SharePoint related sessions.

 

BYOD

As usual, the day started with a Keynote focusing on an overview of the new Windows 8.1 and Windows Server 2012 R2 features. 

Well… it is not “just” an update!!! You will find a lot of new features especially around BYOD trend (Bring Your Own Device)

• Workplace Join 

  To remotely register a non-Domain joined device in Active Directory (AD) to gain secure “single sign-on” access to permitted corporate network resources.

• Selective Wipe

  To identify protected files in app that can be revoked when a user is no longer authorized to access an app data.

• Work Folders

  To store and access work files on personal computers and devices

 

SQL Server

I have been to the “SQL Server & PowerShell” session presented by David Barbarin, MCM SQL Server.

SQL Server 2012 supports PowerShell, the scripting shell you will find in Windows. Administrators and developers can automate server administration and application deployment.

The PowerShell language supports more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts (not only for SQL Server as more and more products from the Microsoft stacks are coming with their own set of PowerShell Cmdlets).

The feature was released with SQL Server 2008 with a mini-shell but with 2012, it is definitely more convenient to use as fully integrated with PowerShell (programming, load other modules…).

For more details, see following link on TechNet: http://technet.microsoft.com/en-us/library/hh245198.aspx

 

SharePoint BI & Azure

I did not attend my two dbi services colleagues Stéphane Haby and Stéphane Savorgnano session while they were presenting the “SQL Server 2014: In-Memory OLTP” feature and live demoing with millions rows.  

But I was sitting in the “BI with SharePoint & SQL Server on Azure IaaS: a user case” where Microsoft provided an overview on how to quickly setup a complete SharePoint/SQL Server BI solution on Azure IaaS (Infrastructure as a Service).

Once all the  servers are configured and authentication with Claim to Windows Token Service (C2WTS) and Kerberos is working for the SharePoint & SQL Server BI integration (see: http://technet.microsoft.com/en-us/library/ee806870.aspx#section2) you have different options to connect your corporate workstation: Site-to-Site VPN or Point-to-Site VPN.

Unfortunately, those might not be possible (to implement in a short timeframe or not friendly enough for end-users)

Here come the trick, deploy ForeFront TMG as a reverse proxy on the Azure IaaS side providing a smooth access to the Azure hosted solution from the corporate workstations.

 

That was a interesting day at the TechDays in Lausanne where we all had the opportunity to view the latest trends on Microsoft technologies for IT Pros.

In future blog posts I will try to provide a step by step deployment of SharePoint & SQL Server BI on the Azure IaaS.

In my last article, I presented the first part of setting up a new action for Alfresco Share, which is an archive action. I advise you to read the first part if you have not read it yet since it is a prerequisite for the proper functioning of the action: Developping an archive action for Alfresco Share (1 of 2). The purpose of this part is to extend the user interface with the creation of an AMP for Alfresco Share. I remind you that <AlfrescoAMP> and <AlfrescoShareAMP> references the location of the projects (e.g. C:/AlfrescoEclipseProjects/AlfrescoShareAMP).

1. Extension of Alfresco Share

So let's create a properties file that contains the display name of the action and messages that will be sent to the user at the end of the action:
<AlfrescoShareAMP>/config/alfresco/messages/custom.properties


In cases where Alfresco is used with multiple languages, you can manage this file with i18n. So you can add several files for different languages: custom_en.properties, custom_fr.properties, aso...

As we did for the Java code, we have to tell to Alfresco Share that a new property file was added. For that purpose, create the following file:
<AlfrescoShareAMP>/config/alfresco/web-extension/custom-slingshot-application-context.xml



The next step is to add a line in the list of actions on Alfresco Share interface. To do this, you will need to extend the file you certainly know:
<AlfrescoShareAMP>/config/alfresco/web-extension/share-config-custom.xml



Each action on Alfresco has a small image (16x16) that can be seen next to the name. Alfresco Share can automatically handles the dependency between the image and the action. For that purpose, you must add the following image:
<AlfrescoShareAMP>/source/web/components/documentlibrary/actions/document-archive-16.png

The name of this image must be the id of the action that you define in the file share-config-custom.xml followed by the size of the image (16px): document-archive-16.png

The last thing to do is to create the JavaScript file that will be executed with a click on the action. This JavaScript file must be minified because Alfresco use by default the minify version of a JavaScript file when DEBUG mode is turned off. The build.xml file can do it for you if you have put the two jar files 'yui-compressor-ant-task-0.5.jar' and 'yuicompressor-2.4.2.jar' in <AlfrescoShareAMP>/lib. This JavaScript file manage interaction with the user by offering messages of failure and success. It also allows you to pass parameters to the Web Script which is very useful:
<AlfrescoShareAMP>/source/web/components/documentlibrary/archive.js

2. Deploying AMPs

You now have all that is necessary to add an action in Alfresco Share v4.2. You can easily add other actions by taking the code and customizing it.

Once you have build your two projects, you should normally have two AMP files. Before deploying them the first time, I strongly advise you to make a backup of the original WAR files. You could create a script to deploy AMP files automatically with some of the following command lines:

# cd  <Alfresco_Home>/tomcat/webapps
# rm  -rf  alfresco
# rm  -rf  share
# cd  <Alfresco_Home>
# java  -jar  bin/alfresco-mmt.jar  install  <AlfrescoAMP>/dist/<AlfrescoAmpName>.amp  tomcat/webapps/alfresco.war  -force
# java  -jar  bin/alfresco-mmt.jar  install <AlfrescoShareAMP>/dist/<ShareAmpName>.amp  tomcat/webapps/share.war  -force
# bin/clean_tomcat.sh

<Alfresco_Home> obviously represents the Alfresco installation folder, something that should look like C:/Alfresco or /opt/Alfresco-4.2.c. <AlfrescoAmpName> and <ShareAmpName> represents the name of the two AMPs that was created in your Eclipse Project.

 

Please also note that I join to this blog the zip file containing the archive action AND the file permissionDefinitions.xml which is used to create all groups. Feel free to modify all files:

• alfresco-archive-action.zip

 

Good luck!

The Microsoft Techdays 2013 took place on 12-13 November at the Congress Center in Basel and on 18-19 November at the Beaulieu Congress Center in Lausanne. With more than 400 participants in Lausanne and around 1'000 participants in Basel, the largest technology event Microsoft in Switzerland was a great success. You can follow all the presentations from Lausanne or Basel. In this blog, I will summarize the presentation of SQL Server 2014: les nouveautés by Tien Dung Thoi at the TechDays in Lausanne.

 

In-Memory OLTP

The main new feature in SQL Server 2014 is In-Memory OLTP, which grants you a significant speed improvement. This flagship feature stores all the data in the memory to improve access time - which is now nearly 1000 times faster than that of a hard drive disk. You can also choose to duplicate the data into the disk to make it persistent.

In-Memory OLTP has the following characteristics:

• Only hash and range indexes are available;
• No buffer pool;
• Stream-based storage for durability;
• Full ACID support;
• Core engine uses lock-free algorithms;
• No lock manager, latches or spinlocks;
• Use of T-SQL compiled to machine code, via C code generator (C compiler integrated in SQL Server 2014 engine);
• Invoking procedure is just a DLL entry-point;
• Aggressive optimization at compile-time.

Databases gains the same manageability, administration and development experience with SQL Server Integration:

• Integrated queries and transactions;
• Integrated High Availability and backup/restore.

 For more information, please have a look at Stéphane Savorgnano's posting  SQL Server 2014: In-memory OLTP project “Hekaton”.

 

ColumnStore

The query speed has been significantly improved when using column store indexes, with the use of a new batch mode.

This batch mode improves CPU utilization by:

• Minimizing instructions per row;
• Taking advantage of cache structures;
• Highly efficient algorithms;
• A better parallelism.

 

AlwaysOn

AlwaysOn has been enhanced with two minor changes. The first one is the number of secondaries that has been increased from four to eight, but the number of synchronized secondaries is still two. The second one is that it now allows FCI customers to configure CSV paths for system and user databases.

have a look at Stéphane Haby's posting SQL Server 2014: AlwaysOn-Verfügbarkeitsgruppen – jetzt mit Microsoft Cloud!.

 

Permissions

There are four new permissions in SQL Server 2014:

• CONNECT ANY DATABASE: is a server level permissions which allows to connect all current databases and any new databases that might be created in the future;
• IMPERSONATE ANY LOGIN: is a server level permissions which allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases;
• SELECT ALL USERS SECURABLES: is a server level permission which allows to view data in all databases that the login has the permission to connect to;
• ALTER ANY DATABASE EVENT SESSION: is a database level permission which allows a role to read all metadata.

 

Backup Encryption

You can encrypt your data while you are performing a backup, independently of the destination - whether internal or external. You have to specify the encryption algorithm and the encryptor when creating the backup.

The supported encryptions are:

• AES 128;
• AES 192;
• AES 256;
• Triple DES.

The supported encryptors are:

• A Certificate;
• An Asymmetric Key.

  Microsoft Power BI

Power Query permits to search and access specific data across multiple sites, whether internal or external from your organization.
You can also transform and combine this data for relevant analysis, such as:
• Clean, transform, and shape;
• Merge and combine.

Power Pivot is an Excel feature for faster analytics and flexible modeling of large data sets, with in-memory technology.

Power View allows to see the data in new ways with an interactive virtualization, such as a 3 D geospatial analysis with Power Map.

 

Hybrid Cloud

Cloud backup, manual or automatic, has been simplified with point in time restore at instance level, and measures databases usage patterns to set backup frequency.
Moreover there is a fast disaster recovery with a low Recovery Time Objective, which is easy to deploy and manage.

Furthermore, a new wizard has been delivered to deploy database to SQL Server in Windows Azure Virtual Machine for easier use.

 

SQL Server 2014 CTP2: what is new from the CTP1?

In-Memory OLTP is now present into the SQL Server Engine: it does not require any additional actions to be installed. It allows some in-memory performance benefits: you do not need to rewrite your database application or to refresh your hardware. Moreover CTP2 enhancements include AlwaysOn support, increased T-SQL surface area, and ability to migrate existing objects to In-Memory OLTP.

In-Memory Updateable ColumnStore provides higher compression, richer query support and updateability of the existing ColumnStore for data warehousing workloads. It gives you even faster load speed, query performance, concurrency, and even lower price per terabyte.

SQL Server uses SSDs as an extension to the database buffer pool, allowing more in-memory processing and reducing disk I/O.

For the Resource Gouvernor, Resource pools now support configuration of minimum and maximum IOPS per volume.

 

Conclusion

There is a very strong will on the part of Microsoft to provide BI tools, in order to easily access, analyze and share data from any source.

Moreover, Microsoft has a real desire to strenghten Cloud utilization through Windows Azure and AlwaysOn.

But without a doubt, the flagship product of this new edition of SQL Server is In-Memory OLTP. It improves significantly performance and rapidity of your databases.

For more information have a look at the presentation of SQL Server 2014: In-Memory OLTP by Stéphane Haby & Stéphane Savorgnano.

Today’s first session was a round table session with Andrew Bullock, Director of Application and systems management at Oracle. The main result of this session was that the main people using Enterprise Manager Cloud Control 12c were DBA and not so much system or Weblogic Administrators.

It appears also that not so many DBA have realized in production the upgrade from Grid Control 11g to Cloud Control 12c, because in case of a huge number of targets, the agent upgrade phase could last a very long time. It appeared also that many DBA use Enterprise Manager Cloud 12c in a high availability mode in active/active or active/passive environment.

It was really interesting to share our experiences about agent’s unreachable, problematic agent 12c installation on MS windows, or AIX.

I continued with a rman session resuming some hints to improve our rman scripts.

1-    It is recommended before calling RMAN to export NLS_DATE_FORMAT in a suitable way to have correct date formats in the log files. It is also important to have the total execution time at the end of the log file.

2-    Do not use crosscheck the crosscheck command in your RMAN backup scripts because RMAN will silently ignore the missing files and the recovery might be impossible. The crosscheck command should be a manual activity executed by a DBA to solve an issue.

3-    Backup the control file at the last step of your backup script.

4-    Do not delete archive log based on time only. It is better to use a command like:

rman>delete no prompt archive log all backed {N} times to disk completed before ‘sysdate –x’;

Or for a standby database:

rman> configure archivelog deletion policy to applied on standby;

5-    Do not rely on rman stored configuration. For example if you use the control file auto backup feature, what happens if another DBA switches auto backup to off ? A good advice is to capture the rman settings and to restore it at the end of the RMAN backup script.

6-    Send backup log files to a shared email address. If possible run a log files check in your rman backup script and in case of failure send an alert to the on call DBA. A good advice should also to check the long running backups.

7-    Use the report commands such as :

Rman> report need backup days=2;

Rman> report unrecoverable;

The last session I assisted was again about Enterprise Manager 12c, even after been working on this subject since quite a few years I learnt some hints, I share with you:

1-    An EM 12c infrastructure is considered as little when monitored targets are less than 1000, users less than 10 and the number of management agent are less than 100.

2-    When planning an installation you have to question yourself about the future number of targets, how big they are to define if you decide to install OMS in active / active environment for high availability

3-    In a shared management server storage the recommendation is at least 50 Gb (in fact for the software library)

4-    Concerning the Enterprise Manager Repository one recommendation is to increase the default value of the STREAMS_POOL_SIZE parameter.

5-    The default security settings for logging information in the weblogic server are set at a level that will cause excessive logging data. It is recommended to modify the USER_MEM_ARG –Xmx in the startEMserver.sh at a value of 4 G in a medium environment size.

6-    Of course the repository should be managed as a target.

7-    Don’t forget to use the search box in the Cloud 12c console to find target or knowledge.

8-    Use emcli, not so many DBA use this nice feature which allows creating database, clear stateless incidents, deploying patches for example. This feature is not very well documented but so useful!

Those four days in Manchester UKOUG have been intensive but very interesting. The UKOUG event is really a technical event allowing us to share technical information with other DBA. We impatiently wait for the next year :=))

It's the final day of Tech13, less sessions but still good topics. Mainly about the Oracle's optimizer: looking at the execution plans and a debate about hinting SQL statements. I will focus on the hint topic in this blog.

During this last day I also decided to follow the Middleware stream. I have continued to learn about future Oracle product strategies. In the web tier layer, Oracle will continue to support I planet web server but without adding new added value features inside. They insisted on the fact that OHS (Oracle HTTP Server) is still, and will be the preferred Oracle product as a web server.

OHS is derived from Apache the famous free/open source web server. Answer was also not totally clear, but it seems that one plug-in compiled for standard Apache, will also be executable within OHS.

Other interesting thing is the ongoing development of Oracle Traffic Director. This product is a software base load balancer (and other enhanced features) especially fitted for OHS and Oracle Fusion Middleware products. However, it can also be integrated with other TCP-based services.

In Weblogic 12c, there is a new interesting feature, the Dynamic Cluster management.

In few words, we first have to register a pool of physical/virtual machines, and Weblogic Admin server will be able to dynamically increase the size of a cluster on them. You can define several metrics, where Weblogic will take care about and determine if more resources are needed to handle the flow of requests (Messages, HTTP requests, etc...)

We attended a quick demo, where we have seen the number of managed servers increasing, on the fly, according to the number of requests. However, once the flow finished, newly created managed servers were not deleted, but only shutdown.

Obviously you can set a limit for a maximum of Managed server the Admin server can create, and configuration of the managed are coming from a template.

This feature is still available in Weblogic 12.2, but we also need to configure other properties beside to get it working. Normally in release 12.4, Dynamic Cluster will be configurable easier.

 

Today I attended the Middleware Weblogic stream. It was very interesting to learn a few things about the Oracle view and strategy around Java and Middle-tiers layer.

On the perspective to continue to provide cloud management tools and products, Oracle wants achieve stronger integration between Weblogic and Databases, both in 12c versions about following features:

• Application availability with Transaction Gard
• Multitenant Database integration
• Database Connection Pool
• Global Data Services

Oracle also made a introduction about Oracle Coherence. It is fact It is an intermediate layer between middleware tiers and several kind of data sources. Its main functionalities are:

• Data caching
• Synchronous and asynchronous backups
• Multi-machine loss protection
• Multi data center

Current Weblogic release supports Java EE 6, however support for Java EE 7 will coming during years 2014/2015

Now let me talk about the update and roadmap of Oracle Cloud Services. Goal is to ease Java based applications deployments, by providing Instant accesses and self service into a pre-configured environment.

There are 2 branches:

• Java cloud services: Dedicated platform for Fusion Middleware SaaS extensions
• Java as a service: For every Java EE an legacy applications

In this case Oracle aims to provide hosting services around Weblogic Java application containers. There are three levels of support:

• Basic: Pre-configured, automatically installed Weblogic software.
• Managed: Oracle manages one or more Weblogic domains
• Maximum availability: Managed HA environment, Weblogic cluster integrated with RAC

I asked the question, but answer was not clear if using such services, administrators can keep a direct access into the underlaying Operating System. It seems to be the case for some few uses cases, but obviously, general purpose is to definitely not deal with other software brick than Java and fusion Middleware products. They invited us to have a look at the documentation about this topic.

To ease and speed up benefit interest in their product, Oracle provides a Java Cloud Service SDK plug-in supported by all mainstream Java development platforms (JSeveloper, Eclipse and Netbeans)

Oracle also planned to provide same kind of high level cloud services regarding the development processes. They aimed to integrate development specific tools such as Maven, Hudson, bug tracking, etc.., in a dedicated cloud space set-up for Java application.

To summarize, the impression I had after having attended today's presentations, is that Oracle's strategy for the future is to try inviting the Java actors within the market to develop new applications on their infrastructure. And once finished, also to host the results by their own.

In the mean time, Oracle ensured they will continue to support Java Community, around Netbeans and Glassfish for instance, even if they have stopped the commercial support for the last.

 

Today's first session I assisted was presented by Tom Kyte. He talked about SQL topics we surely do not know. The first topic concerned the possibility of using the plsql_warning clause with the PL/SQL compiler. This feature exists since version 10.1 but is not widely used. The different values you can define for the plsql_warning with a classical alter session command are:

     Severe: code might cause unexpected errors or wrong results

     Performance: code might cause performance issues

     Informational: code is not wrong but this is just bad code

Thus by using this command before compiling the PL/SQL code you will receive messages helping you to improve your code.

The second hint was about implicit conversion. For example the implicit conversion from string to number or to date is probably the first cause of bugs or performance degradation. It could be even worse with the implicit conversion that rely on NLS default settings.

If we had used the PL/SQL warnings, those implicit conversion would have been told out.

One interesting hint was concerning the different levels of the optimizing compiler. You can set the PLSQL_OPTIMIZER level to the following values:

     1: no rearranging code

     2: rearranging code is possible

     3: code is rearranging

He showed us some very interesting examples which showed us that the level 3 might be very aggressive and modify the results.

As an overview, the PL/SQL warning framework is important and very useful; the last level of the PLSQL_OPTIMISER must be used with caution.

My second slot was about Oracle Active Data Guard, the Next Generation Data Protection with Oracle 12c presented by Mr. Larry Carpenter, Master Oracle Product Manager at Oracle USA.

He presented us the following new features available:

The new Data guard fast sync feature is reducing the synchronous transport impact. In maximum availability protection mode, the standby database acknowledges the receipt of redo before writing to standby redo logs. The main benefit is that it reduces the synchronous performance impact on the primary database, and consequently the primary database performances are more predictable.

He also announced that DML on global temporary tables are now supported in active data guard 12c. It is enabled by the new parameter TEMP_UNDO_ENABLED.

The data guard broker has also evaluated in terms of monitoring, robustness, log monitoring and warnings, validating role transition readiness. For example the Transport lag and Apply lag is included now in the show database output. The validate command can be used to assess readiness to a switchover or a failover, it validates each database current status , verifies there is no archive lag gap, detects parameters property inconsistency and also performs a log switch on the primary database to verify the log is applied on all standbys.

The Rolling Upgrade has also been centralized and simplified and uses a dedicated interface by using dbms_rolling package which will greatly simplify the rolling upgrade in comparison to the previous 11g release by using only five phases:

     dbms_rolling.init_plan: generates an upgrade plan

     dbms_rolling.set_parameters: modifies the parameters of the rolling upgrade

     dbms_rolling.start_plan: configures primary and standby database for the upgrade

 dbms_rolling.switchover: swaps roles between the primary and the standby, this is the only downtime

     dbms_rolling.finish_plan: completes upgrade and resynchronizes

Finally I assisted to an EM12c session: Using BI publisher with EM12c for enhanced productivity animated by Mr. Dananjay Papde author of Oracle EM12c administration cookbook.

BI publisher is the oracle’s primary reporting tool for authoring managing and delivering documents, highly formatted and published in various formats sucha as PDF, Word, Excel, Power Point …

To use Bi publisher with EM 12c you have to install and integrate it with EM 12c in two phases:

     1: software only install of BI version 11.1.1.6.0

     2: integrate BI publisher with EM12c

Note that in the next version of OEM 12c (12.1.0.4.0) you won’t be obliged to install and configure BI publisher, it will be integrated directly in OMS.

Before running the configuration it is recommended to take a backup of the repository and the EM domain, then you only have to run the configureBIP script located in the $OMS_HOME/BIP/bin directory.

Creating BI Publisher reports can be done in an easy way, you select the EM repository as data source and you can use the query builder to build your queries from mgmt$ views.

Another interesting point is that you can efficiently manage your BI publisher from the EM 12c console. By accessing the BI Farm summary you have access to a lot of metrics which might help you to correctly administer your BI publisher.

I also participate at an interactive and open questions and answers session about Oracle Enterprise Manager 12c. The session was really interactive, at the beginning Oracle managers asked classical questions about what kind of targets we used to administer with EM12c, if we used other products then EM12c, then the problematic of the licenses has been discussed, in particular the very complicated usage of the different pack licensing methods for the different targets administered by EM12c. It was a pleasure to share this session with different customers and Oracle managers.

Another full day in Manchester, I choose to follow a stream a little bit different in the morning. So it will not be all about the Oracle Optimizer 12c today, but more about O.S and virtualization.

I started the second day ay UKOUG with an Exadata Management Deep dive with Oracle Enterprise Manager Cloud 12c. Through a certain numer of plugins, Oracle Enterprise Manager Cloud 12c offers an integrated view of hardware and software. In Enterprise Management Cloud 12c you have access to the following menus:

Software Cell Management:

     Cell home page and performance page

     Workload distribution by database

     Interprets immediately what’s happening

     Performance workload distribution may help analyze analyses performance problems

Infiniband Network management

     Infiniband network and switches are discovered as part of the database machine target

Other hardware components are checked, such as power supply failure, fan failure or temperature out of range.

OEM Cloud 12c allows displaying the Exadata component monitoring. The common performance issues can concern the network, the disk in the hardware side, the SQL or the database in the software side.

Concerning the network the Cloud 12c console is able to detect if there is a bad port or a loose cable, ports with errors are marked as red and details of the problem can be found in the metric error page. There is also the possibility to perform infiniband administration to disable a bad port or enable ports.

In another way disk failure, over utilized hard disks or under-utilized flash disks are also checked by the administration console. The notion of cell health is defined and there is a description of where the failure is and what the failure is. The Exadata system health is computed using information collected from network, disks and ASM. You can visualize the Exadata health in the database target performance page.

One interesting point is the visualization of the over utilization of the hard disks. The performance page of Exadata storage server target provides real-time and historical utilization information. You can visualize the performance of the database and at what time of the day the I/O bandwidth is exhausted, you can identify the database which causes the increased I/O usage, and what cause the I/O activity.

Another practical point is to have the possibility to ensure I/O bandwidth for all database targets, and to be sure that one database is not running away with all I/O bandwidth. The notion of Inter Database Resource plan is useful when you need to manage I/O priorities across those databases. It offers the possibility to allocate I/O resources across databases by means of resource plans configured on each storage cell.

This Oracle presentation was very interesting and gave a complete and amazing overview of what EM Cloud 12c can administer in an Exadata environment.

Even after having tested the Oracle 12c new features concerning RMAN, I decided to assist at the Andy Colvin’s RMAN 12c new features session. I’m reassured; the new features are the same!

There is a new security role named SYSBACKUP which is recommended for connecting with rman.

RMAN supports Pluggable Database, can backup CDB and it is possible to realize point in time recovery on a specific PDB.

There is the possibility to restore a table which is very useful when you cannot use flashback. It offers the possibility to import the table with another name if needed using data pump syntax. Just to notice a bad issue for the presenter, the restore table demo failed live, but finally he could finalize it!

The active duplicate feature utilizes now backup set and empty space is moved faster to the target database.

The next session was animated by Maria Colgan and Jonathan Lewis concerning the 10 optimizer hints we can’t miss. Mrs. Colgan and Mr. Lewis were talking each at their turn about the optimizer and each one had a different point of view.

For example Mrs. Colgan told us about the auto_sample_size parameter when gathering statistics showing us that this parameter is very efficient since Oracle 11g version. From the other side Mr. Lewis demonstrated that this comportment was not always true in particular when there is a heterogeneous distribution because the optimizer can miss rare values in the histograms.

The problematic about function was also discussed, because the optimizer has no idea how the function affects values in columns. The main advice is to create a function based index with the exact same syntax as in the SQL request or the better solution is to use the opposite function on the right side (not the field from the table).

Some useful hints: GATHER_PLAN_STATISTICS which allows displaying the estimated and the actual rows, OPT_PARAM which allows value for init.ora parameter to be changed in a specific way for example you might use /* _fast_full_scan_enabled, FALSE */

In conclusion a complete representation set of statistics gets the best plan, optimizer hints should be used with caution.

The last session of the day was also animated by Mrs. Colgan concerning the future new Oracle 12.1.0.2 feature: In Memory Database.

The principle is the following; the data is stored classically on disk but now a new format is available to store the table in memory. The table is stored as columns instead of rows in a new dedicated memory area in the SGA. You can decide to move the table data in memory with an easy command such as alter table T inmemory; or the opposite: alter table T no inmemory;

The data is loaded in memory for active tables or partitions on startup or first access. You can exclude columns for a table not to be in memory.

New views will be available in the 12.1.0.2 version and will allow us to list the objects in memory, and to visualize the compression ratio.

The live demo showed us the amazing efficiency of the In Memory Database option. When running in a classical buffer cache way the response time was 4 or 5 times slower than with the in memory database.

Unfortunately we did not have any information about the licensing cost of this option, we have to wait for next year.

This Monday I decided to follow the SOA – JAVA – BPM stream. I attended a presentation showing a high level overview of Oracle SOA and BPM Architectures, Oracle Fusion Middleware, and various product integrations.

They have spoken a few about SOA principles, from legacy Systems and application, to the end Web Services. What we need to understand here, is that SOA is an intermediate / abstraction layer that aims to standardize accesses to more low-level underlaying and specific services. SOA will make available some kinds of services to others, in an easier way.

During this session diagrams where a few to big and sometimes complicated to understand for gents who are not coming from the Oracle world. What I have felt, is that Oracle tried to convince the audience they can handle every kind of business problem and provide a solution.

In the future, they planned to continue an investment effort in the following key areas:

• Mobile computing / development framework
• Cloud computing
• Internet of things
• Big/fast data
• Engineered systems
• Business user empowerment

They also insured they will continue to support a wider Java community, and even if Java is "owned" by them, Java is not dead and Oracle don't and won't hoard it.

I also attended another presentation tried to demonstrate some use cases within Oracle SOA, and witch to use for what service.

First advice is to work on a complete service registry for an organization, in order have a more accurate segmentation and perform a classification with following criterias:

• Functionalism
• Granularity
• Security level
• Action type
• Organization boundaries
• Channel

Regroup them an try to use another dimension, some kind of an abstract position related to the importance in the organization:

• Elementary services: smallest possible, short running
• Composite services: defines single transaction, combination of logic between elementaries services
• Process services: the highest level, longer running and potentially chargeable

Than we have mainly two choices, to use the Oracle Service Bus (OSB) and Business Process Execution Language (BPEL) for simple processes, or the Business Process Management to model and integrate more complex ones (for example those that sometimes need to go back, earlier in the process)

We have seen more concrete pictures of the usage of such Oracle products.

On another side I have attended to another presentation about database visualization, this in order to reduce over-costs and delay during large development projects, around huge databases.

Replicating full databases, that multiplies the storage needs, or provide subsets of the productions database to the developers, that will generate bugs at the end, are not acceptable solutions in some of these cases.

The solution proposed was to only make a single copy of a production database, eventually kept up-to-date regularly, and store only the small portions of data that where changed for each single developer session.

This will at the end give an efficient way to provide full access to the data to all developers at the same time, avoiding big discussions, decisions and procedures about resources allocation and consumption, letting the project going to be achieved faster. This also gives a chance to allow more time to QA and validations instead of staying waiting for the environment to be ready to start.

Tech13 - Day 2: 11 different streams with 3 dedicated to the databases. Many interesting topics and it's sometimes hard to choose to which session attend. I tried to keep an eye on sessions about performances.

Today was the first day of UKOUG Tech13: 4 days about Oracle Technology. This year, 7 streams started today (3 last years). I also had a slot to give my presentation about the Optimizer behavior in 12c.

Today, we participated in two WebLogic presentations - and won the WebLogic Hackathon contest! But first, let's talk about the sessions.

 

The first session was a summarized overview of the Weblogic installation process.

There are two manners to proceed. On the one hand, you can use a GUI based interface, and on the other, you can use the silent mode.

At the end of the presentation, we installed a new Weblogic domain in a virtual machine configured by the presentation speakers.

They managed to automatically setup the networking environment over a private dedicated network for the room, and to share everything we need to download in order to achieve our installation.

Afterwards, we installed an Admin Server, followed by a Managed Server. All installation procedures where done in silent mode, as no X server was installed in the virtual machine.

The interesting thing here for us is that we now have a fully automated script-based manner for the installation of a complete Weblogic domain, in silent mode, and using the node manager.

 

On the second presentation, the speakers talked about how to manage configuration files on big heterogeneous environments, including Weblogic, and potentially many more IT products.

In order to help administrators and architects, you can deploy one of the following products: Puppet and/or Chef.

Their main particularity advantages are:

• Avoiding of repetitive tasks

• OS independency

• Keep the servers in sync, with always up-to-date configurations

• Configuration version control

To summarize, such a "king of products" can pull a configuration file on servers all over an organization network, in a scheduled period of time. Both are equivalent in terms of features and quality. Both run using a central server that communicates with an agent installed on the client servers. It seems that Puppet is more broadly used, as we already have seen it at one of our customers.

 

Weblogic Hackathon contest

And now to the most interesting: The Weblogic Hackathon contest!

After the second presentation, we split into groups of 3-4 persons and the challenge was to be able to install either a Puppet or Chef Master/Agent in two new virtual machines. The team who did that first, won the right to discuss on-line with the OTN presentation manager.

dbi services was one of the team leaders, as all installation operations were performed on our laptop. We configured a Puppet Master server and an Agent and were able to automatically install a Java Virtual Machine and another Weblogic domain on the client virtual machine (Admin and Managed servers).

We did it! Another trophy won by dbi services, and it will surely not be the last one!

On the first presentation we attended a summarized overview of Weblogic installation.

There are two main manner to proceed. One using a GUI based interface, and the other in silent mode.

At the end of the presentation, we have installed a new Weblogic domain in a virtual machine per-configured by the presentation speakers.

They have managed to automatically set-up the networking environment over a private dedicated network for the room, to share everything we need to download in order to achieve our installation.

Afterwards, we have installed an Admin Server, followed by a Managed Server. All installation procedures where done in silent mode, as no X server was installed in the virtual machine.

The interesting thing here for us, is that we now can have a full automated script-based manner to install a complete Weblogic domain, in silent mode, and using the node manager.

 

On the second presentation, speakers have talked about how to manage configurations files over big heterogeneous environment, including Weblogic, and potentially much more IT products.

The way to help administrators and architects, is to deploy one of the following products: Puppet and/or Chef.

Their main particularity advantages are:

• Avoiding of repetitive tasks

• OS independent

• Keep the servers in sync, always with up-to-date configurations

• Configuration version control

To summarize, such king of products can pull configuration file into servers all over an organization network, in a scheduled period of time. Both are equivalent in terms of features and quality. Both run using a central server that communicates with an agent installed over the client servers. It seems that Puppet is more broadly used, as we already have seen it by one of our customers.

 

Now the most interesting: the Weblogic Hackathon contest.

After the second presentation, we made groups of 3-4 persons and the challenge was to be able to install either a Puppet or Chef Master/Agent in two new virtual machines. The team who did that first, won the right to discuss on-line with the OTN presentation manager.

dbi services was one of the team leaders, as all installation operations were performed on its laptop ! We have configured a Puppet Master server and Agent, and we were able to automatically install a Java Virtual Machine and another Weblogic domain on the client virtual machine (Admin and Managed servers).

 

We did it! Another dematerialized trophy gained by dbi services, and it will not be the last one !

Today was the first day of the United Kingdom Oracle User Group (UKOUG) - usually called the Super Sunday! I already participated in today’s first session three weeks ago in Geneva, but my colleague Nicolas Jardot was talking about Solving Critical Issues with the 12c Optimizer, covering histogram collection, temporary table statistics and adaptive query optimization.

There were around thirty people at his session, and a lot of UKOUG speakers were very interested and asked a lot of questions with a typical British accent that did not destabilize Nicolas at all. All my congratulations to Nicolas!

The next session, preseented by Tony Hasler, was about the Cost Based Optimizer: Why does the CBO goes wrong?

Mr. Hasler explained that the expectations around the CBO are unreasonably high.

He explained us a lot of issues around the following points:

• Cardinality errors (correlated columns, functions and expressions, stale statistics or daft data types)
• Partition level statistics (which might mask the need for extended statistics)
• Badly written code
• Missing information
• Transitive closure (you write the statement in another way and the plan is different)

Other issues were discussed as possible reasons of changing execution plans:

• Caching effects
• Contention
• Bad physical design

His recommendations were the following:

• Proactively tune the critical statements
• Use optimizer hints cautiously
• Carefully consider the statistics gathering strategy (i. e.: gather statistics less frequently)

The last session of the afternoon was a live demo: Andy Colvin applied the latest Exadata patches to one of his company Exadata racks.

We assisted the live demo of the Exadata patching process with instructions and best practices on how to apply patches on an Exadata environment.

He used a utility named dbnodeupdate.sh which helps in the process of updating the nodes in an Exadata environment. Oracle last year introduced yum updates in Exadata compute nodes with Oracle 11.2.3.1.0.

Before that time, each Exadata server patch had a pack including a shell script that forced new RPMs on the new compute node. But if the users began to install their own packages, the installation had a lot of chances to fail.

The dbnodeupdate can run the bootstrap phase to directly update from 11.2.2.4.2 version to 11.2.3.2.1 in two reboots and running three commands. It analyses the free space on the compute node volume groups to back up the root volume, providing a way to rollback. It also has the possibility to disable the CRS on reboot and relink the Oracle Homes and enable CRS on startup.

It really was a "Super Sunday" afternoon, I will have to impatiently wait until tomorrow to discover some features like "Exadata Management deep dive with OEM 12c" or "10 optimizer hints you can't miss"!

A few weeks ago when I was working on setting up an Alfresco Community Edition v4.2.c, I came across a problem that took me quite some time. What I will talk about today is the customization of the actions provided by the interface of Alfresco Share. This action was tested on all sub-versions of Alfresco Community Edition 4.2 (a, b, c and d).

Unfortunately, Alfresco Share does not provide function to archive content. To meet this need, I was led to develop an action using the Java-Backed Web Script. This technology is very similar to Web Scripts except that the desired action is written in Java.

This article contains the changes made to the core of Alfresco for the implementation of this action. In an article that will be available soon, I will discuss the changes to do to Alfresco Share to add this new action and make it work. Please also note that in this second article, I will join a zip file containing all the files needed to create the two AMPs and therefore the action.

1. Description of the action

We Assume that your repository is structured as follows:

• Company Home
  • 00_FirstLevel
    • 001_SecondLevel
  • 01_FirstLevel
  • ...
  • 99_Archives   

The only requirement is to have a folder named 99_Archives. You can change the name of this folder in the Java code.

With this structure, the purpose of the archive action will be to move an element (a file or a folder containing other elements) to the folder named 99_Archives.

In order to do something useful, we can also add other features such as:

• Each time a user will execute the archive action, a folder named YYYYMMDD (current date) will be created if it does not exist 
• In order to maintain the integrity of the repository, each archived item must keep their own rights
• All the elements entering into the 99_Archives folder should no longer be editable

 

2. Operation

The use of Java-Backed Web Script for the creation of actions in Alfresco Share is very useful because it allows access to the entire Java API provided by Alfresco. This allows you to easily develop actions that will be very powerful.

When a user clicks the archive action from the interface of Alfresco Share, a JavaScript file is called. This file is used to record the action that has been requested and tries to process the request. It also uses a generic module provided by Alfresco that allows the execution of a Web Script from its URL. This module then starts the execution of the Web Script which is, in our case, a Java code. Depending on the success or failure of the Java code, the JavaScript file will send to the user a message with the state of the action.

3. Extension of Alfresco

For the development of the archive action, start from the assumption that you already have an Eclipse project (or other IDE) which allow you to create some AMPs (Alfresco Module Packages) for Alfresco and Alfresco Share. It means that you have two project with the following files: build.xml, file-mapping.properties, module.properties and jars to compress JavaScript. If I get a few requests, I could make a new blog about setting up an Eclipse project to completely customize Alfresco or Alfresco Share.

Note these two projects as follows: AlfrescoAMP and AlfrescoShareAMP. <AlfrescoAMP> and <AlfrescoShareAMP> references the location of the projects (for example C:/AlfrescoEclipseProjects/AlfrescoAMP).

The first thing to do is to write the Java code. To do this, just created the file:
<AlfrescoAMP>/source/java/org/alfresco/module/<YourModuleName>/ArchiveWebscript.java


Caution:

• Notice that in the code, there are several methods "setXXX" that will be called automatically by Alfresco to initialize variables that are defined and have the same name
• As you can see in the Java code, I've previously been led to create customized groups ("dbi_read", "dbi_write", etc...). It is important to note that to prevent edition of elements in the folder 99_Archives, I created other groups completely equivalent except that I remove the terms "AddChildren", "Write" and "CheckOut". All of these groups have been created in <AlfrescoAMP>/config/alfresco/model/permissionDefinitions.xml. There are other ways to do this but this is the one that best suited our needs.

Then you need to tell Spring that we have a new Java class:
<AlfrescoAMP>/config/alfresco/module/<YourModuleName>/module-context.xml


This file allows you to set several other context files (e.g. one for each action) in order to separate actions but this is not mandatory.


The definition of the action is in the following file:
<AlfrescoAMP>/config/alfresco/module/<YourModuleName>/context/service-context.xml


Note that each variable that needs to be initialized by Alfresco at the execution of the archive action must be declared in this file.


The last step in the extension of the core of Alfresco is the declaration of the Java-Backed Web Script. As I stated previously, this is done in the same way as the declaration of a classic Web Script:
<AlfrescoAMP>/config/alfresco/extension/templates/webscripts/<YourModuleName>/archive.get.desc.xml

This concludes the first part of this blog about the modification to bring to the core of Alfresco. The other part of this post will be available soon!

Le propos des trois posts PostgreSQL qui vont suivre, est de présenter:

• la mise en place d'un outil d'aide à la migration d'une base Oracle, au sens du schéma, vers une base PostgreSQL
• L'utilisation de cet outil, nommé ora2pg
• Les difficultés qu'un projet de migration doit affronter

Le premier post porte sur l'installation d'ora2pg, un des outils disponibles sur internet.

I was surprised to see that Oracle WebTier 12c release has been available from OTN since June 2013. It comes with big changes. I guess that marketing Oracle Database 12c encouraged the outcome of newer versions of Oracle Middleware products such as Oracle WebLogic Server 12.1.2, Oracle Coherence 12.1.2, and Oracle WebTier 12.1.2.

Oracle WebTier 12.1.2 includes the following features: 

• Oracle HTTP Server (OHS)
• Oracle WebLogic Web Server Plug-In

In this blog posting, will concentrate on the new OHS features. The most important change is the integration via the WebLogic Management Framework instead of Oracle Process Monitor and Notification (OPMN).

Where to get it?

You can download it from the OTN. It is called Oracle WebTier Utilities 12c Installation.

These are the Oracle WebTier Utilities 12.1.2 features and components

• Oracle HTTP Server (OHS)
• Oracle WebLogic Management Framework
• Oracle WebLogic Web Server Proxy Plug-in
• Oracle WebGate Plug-in
• Fusion Middleware Upgrade Assistant
• Remote Diagnostic Agent
• Configuration Manager

You can download it here.

The main changes are the provisioning and management capabilities. Provisioning

The provisioning of OHS 12c can be performed in two different topologies.

1. Administration of OHS 12c through the WebLogic Domain

This kind of topology requires a valid installation of Oracle Fusion Middleware Infrastructure 12c.

It is called “Standard Installation Topology for OHS 12c in WebLogic Domain”.

2. Administration of OHS 12c through the standalone Domain

The provisioning requires no additional dependency. The administration is executed through a command line API available within the WebLogic Management Framework, precisely WLST. The major advantage is that it can be administrated remotely through its Domain. 

This kind of topology is called “Standard Installation Topology for OHS 12c in Standalone Domain”.

A third topology exists, which is the HA implementation. It consists of the OHS installation (typo 1 or 2) on two separate hosts.

Management

The integration of the WebLogic Management Framework is done through WebLogic NodeManager. It enables to manage the OHS lifecycle.

In the two different topologies defined above, each configured instance will be stored in the user_projects directory which contains the OHS Domain. Note that the “Domain” notion will be omnipresent in the Oracle Middleware stacks. 

Each instance is linked to the WebLogic NodeManager during its configuration.

A Node Manager can be configured per domain (the configuration is located in domain directory) or for multiple domains (with some or all domains from a machine).

Main Node Manager capabilities

I refer to WebLogic server instances such as the Admin Server, Managed Servers (Java components). Since now, Node Manager also manage system components such as OHS.

• Starting and stopping remote server instances
• Monitoring the self-reported health of server instances and automatically killing server instances whose health state has "failed"
• Automatically restarting server instances that have a "failed" health state, or have shut down unexpectedly due to a system crash or reboot

Yes cool but what are the new features? And which ones are not available anymore?

Removed features

The integration with Oracle web cache is no longer included and has been removed. In my opinion, this features has been removed to make people use Oracle Coherence.

Some other modules are not included anymore such as mod_oradav and mod_osso, as Oracle Webgate is the recommended replacement. SSO plug-ins for third-party Web Servers (IIS and iPlanet) have also been removed.

New features

I will explore the most interesting new feature, introduced with the 12.1.2 release, which is the integration in the WebLogic Management Framework.  

Oracle Documentation defines it as follows:

A set of tools that leverage Oracle WebLogic 12c (12.1.2) interfaces to provide a simple, consistent, and distributed framework for managing Oracle.

The major changes of this new framework

• A new configuration wizard for the creation of OHS instances
• A remote management of OHS instances
• The configuration files are part of a WLS domain in standalone, cohabiting with WLS Server.
• The framework is completely managed within WLST
• The opmn.xml specific configuration is now configured in ohs.plugins.nodemanager.properties

A point that surprised me concerns the OHS MBeans: These are subject to change without notice and are not supported for any use other than the Oracle management tools.

Conclusion

The integration in the WebLogic Management Framework is a major step and it is difficult to grasp it without sound knowledge of Java-based WebLogic Server.

One of the best new functionalities of Microsoft SQL Server 2014 is the In-Memory part, which gives you the possibility to load tables and also Stored Procedures in-memory for a very fast response time. The Community Technology Preview 2 from Microsoft SQL Server 2014 went out some days ago with two new Advisors. These Advisors will help you manage the transformation of your disk tables to Memory Optimized Tables as well as your Stored Procedures to Natively Compiled Stored Procedures.

Let see how this work!

Memory Optimization Advisor

Go to Management Studio and select a table you would like to migrate. Right click this table and choose Memory Optimization Advisor.

The Advisor tool will be launched with an explain page that you can skip.

Click Next to begin the script generation process. It will check if your table is able to be migrated as a Memory Optimized Table.

Here, all is green, it means that your table has no blocking features which could avoid a migration process. You have the possibility to Generate a report for this analyze, for the time being it is just an HTML file.

Click Next.

On this page a warning appears. In fact for memory optimized table, only BIN2 collation are available for indexes. My instance has French_CI_AS, I will need to change my index collation later in the migration process.

Click Next to continue.

On this page we have important informations:

• Memory-Optimized Filegroup, you can have just one per instance
• Logical file name that you can enter
• Path where you will save your logical file can be changed
• The estimated current memory cost for this table(if you move the data from the disk table to the new table)
• You have the possibility to select, if you want, to copy Data from the disk table to the new memory optimized table during the migration process
• You are also able to change the durability of the table: by default Schema and Data(schema_and_data) but you could also choose just Schema(schema_only), in this case data will be lost after each SQL Server service restart.

Click Next.

The primary key has to be converted, you can choose between:

• new nonclustered Hash index, which gives best performance for point lookups, needs also bucket_count which should be twice the expected number of rows
• nonclustered index, which gives best performance for range predicates

Click Next.

You have the same choice as for the primary key.

Click Next.

For an index with Char Data Type, we have to select a BIN2 collation because other collations are not accepted with memory optimized tables.

Click Next.

Here you have a resume of all operations that will be performed to migrate your disk table to a new memory optimized table. You have the option to script those operations by clicking the Script button.

Click Migrate to start the migration process.

The migration process succeeded. Now our table is In-Memory!

We can check the new table's properties.

The table is memory-optimized and the durability is schema and data. At this step, we have migrates our disk table to a memory-optimized table.

Why not also move the Stored Procedure which fills this table to a new natively compiled Stored Procedure. Let's also try this!

Native Compilation Advisor

In Management Studio, navigate through your database's objects and select the Stored Procedure you would like to migrate to Natively Compiled Stored Procedure. Right click it and select Native Compilation Advisor.

A welcome screen appears.

This screen explains that this advisor will help us to discover and evaluate Transact-SQL elements in the Stored Procedure that are not supported in Native Compilation...

Click Next.

Here, the Stored Procedure is valid and can become a native one without modification.

My first idea was to click Next to let the advisor migrate my Stored Procedure as for a table, but the Next button is disabled...

In fact, this Advisor just helps you to discover problems that could make your migration impossible, but doesn't do anything else... You have to migrate your Stored Procedure on your own.

On the other hand, when a part of your Transact-SQL is not cimpliant for Natively Compiled Stored Procedures, this tool advises you:

And when you click the Next button:

Now, you have a list of elements which are not supported for Natively Compiled Stored Procedures.

You also have the possibility to create a report for these unsupported elements.

 

Conclusion

As we have seen, the Advisor for tables is more interesting than the Advisor for native compilation stored procedures. In both case, it is definitively a good help to improve codes prior to a migration.

To evaluate if In-Memory OLTP will improve you database application's performance you can use the AMR (Analysis, Migrate and Report) tool. This tool will be covered in a future blog.