Yann Neuhaus

Last February, I performed an operating system rolling upgrade on a four-nodes RAC cluster (11.2.0.2). I then faced a strange problem when restarting the operating system...

The first step of the procedure was to stop all Grid Infrastructure and Database services running on the first node as well as to disable Cluster and ASM autostart. The following command is supposed to prevent Oracle High Availability Service (OHAS) to be run at operating system startup:
 

# crsctl disable crs

 
Then, I powered off the server and asked the storage administrator to disable all LUNs attached to this server, including the one containing Oracle binaries (/u00).

This step was necessary because the storage bay requires specific drivers which are not shipped with the Linux installation media. Without drivers, all mountpoints attached to LUNs and detected during the upgrade process would have displayed errors.

However, when starting the server again to check that all mountpoints were disabled, I saw that the startup procedure was blocked to the OHASD service, preventing the server to finish the startup.

In fact, even if crs autostart is disabled, a deamon called "ohasd" is still running at server startup. Among other things, it checks and indefinitively waits for the presence of CRS binaries. No luck, the LUN attached to the mountpoint containg CRS binaries was disabled...

We can see this check in /etc/init.d/ohasd file:

 

# Wait until it is safe to start CRS daemons
  while [ ! -r $CRSCTL ]
  do
    $LOGMSG "Waiting for filesystem containing $CRSCTL."
    $SLEEP $DEP_CHECK_WAIT
  done

Where $CRSCTL corresponds to /u00/app/11.2.0/grid/bin/crsctl

 

What is crazy is that the loop is performed no matter if autostart is enabled or not. Just after the loop is done, the script checks if autostart is enabled - thanks to the file /etc/oracle/scls_scr/$MY_HOST/root/ohasdstr, which contains "enable" or "disable" depending of the autostart configuration.

Why do not check if autostart is enabled before looking for CRS binaries? A question that Oracle does not seem to have answered in 11.2.0.3, because we can see that, even if the ohasd startup mecanism was updated, ohasd is still waiting for CRS binaries:

 

# Wait until it is safe to start CRS daemons.
  # Wait for 10 minutes for filesystem to mount
  # Print message to syslog and console
  works=true
  for minutes in 10 9 8 7 6 5 4 3 2 1
  do
    if [ ! -r $CRSCTL ]
    then
      works=false
      log_console "Waiting $minutes minutes for filesystem containing $CRSCTL."
      $SLEEP $DEP_CHECK_WAIT
    else
      works=true
      break
    fi
  done

 

As you can see, in 11.2.0.3, the server will now finish to start, but will be waiting 10 minutes. A message is displayed in the log startup:

 

Apr 30 15:31:29 rac1 logger: Waiting 10 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:32:29 rac1 logger: Waiting 9 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:33:29 rac1 logger: Waiting 8 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
Apr 30 15:34:29 rac1 logger: Waiting 7 minutes for filesystem containing /u00/app/11.2.0/grid/bin/crsctl.
[...]

 

At this time, the workaround I found to allow the server to start immediatly is to prevent "ohasd" service to run by renaming or moving the /etc/init.d/ohasd file, or to comment the loop section in order to skip the infinite loop.

Hopefully, SSH deamon (if enabled) runs before OHAS deamon. It is possible, if the startup procedure is blocked, to access the machine through an SSH session in order to apply this workaround and to restart the server.

I finally added this pre-requisite to the upgrade procedure for the remaining nodes of the cluster.

OpenERP release 7.0, which just came out, shows some real advantages in terms of accessibility. By refining the website, OpenERP wants to prove that the new release is not merely an update but a new product with a new mind-set.

 

The new features

Indeed, the ERP’s web interface is now purified and simplified to match the fashions. Today’s user interfaces are intended to be as simple and as designed as possible. The new web interface displays only the needed information, it loads fast, it navigates fast and it only encompasses the modules that you have chosen. If you want a new functionality, you just have to install the right module by pressing one button.

OpenERP was built as a container and is extensible thanks to the modules. It now states this desire by providing a real Apps container. Modules are now considered as applications that you can plug to your ERP to extend its behaviour and functionalities. Some new applications are emerging such as Social Network, which facilitates the internal communications by providing a messaging tool for chatting and allowing user authentication thanks to Google and Facebook accounts.

But unlike these questionable utility applications, some of them can become business critical such as the redesigned Point of Sale module that provides a new useful interface. A lot of partner projects are emerging around this feature; I guess this is the most wanted module for this release.

 

It is meant to facilitate and speed up business processes. In the same registry, the contract management has been enhanced to facilitate usage and to improve functionalities and possibilities:

• Manage the validity of the contract (duration, maximum number of hour/tickets, terms and conditions)
• Handle the invoicing spread in time (fixed price, on time/materials basis, invoice by phases)
• Cover the contract’s price (price by hour by user, conditions according to quantities)
• Forecast of invoices, define budgets and analyse costs and revenues
• Link contracts to analytic accounts (costs and revenues) and projects (manage related tasks, timesheets or issues)
• Follow up (once a week, alerts for contracts renewals are sent automatically to a salesperson with the indication of what to do for each contract)

Some other interesting applications are explained in the release note available on the website.

 

Migrating to the new release

As told before, the new release is not just a minor update - a lot of things have changed: from the user interface point of view and from developers point of view.

First, the traditional web server is merged to the main server; you no longer have two servers to start - and that’s good news.

Secondly, the internal model is changing. For example partners are now considered as contacts. And users have a contact profile created automatically. In addition, internal objects may change (such as osv.osv becoming osv.Model) and behaviour too (such as the need to store a calculated field if you want it to be shown in a graph).

So the migration of a module from version 6.x to 7.0 might be difficult; as such OpenERP proposes to handle your migration for fees, but note that an audit is made to ensure the migration is possible.

The support on version 6.x is coming to the end, obliging companies to migrate. OpenERP wants to show that the new release is a new departure and we are excited to work with this kind of software. But I hope new major updates will not come too fast since we can’t rebuild modules every month!

This way, OpenERP shows that they want to provide a stable and professional open source ERP to make it accessible for companies with a small infrastructure and to provide an alternative to huge, abstract and costly ERPs.

In this bog posting, I will present a boot sequence that works for all Linux-based operating systems - from switching on the power to the login screen.

 

7 steps

There are 7 steps for Linux startup:

 

Power on

Obviously, first of all, you have to switch on your computer, then:

• Each sub process will execute the following one
• It is the same process for all Linux distributions
• Close to BSD and Unix style from which it derives
• Same as Microsoft's procedure until MBR stage

BIOS (Basic Inpout Output System)

• First firmware executed at startup
• Inits motherboard hardware components
• Performs some system integrity checks (i. e. memory)
• Abstraction layer between hardware and software
• Searches and executes the boot loader program from MBR disk

MBR (Master Boot Record), bootlader stage 1

• Located on the first sector of booted disk (512 bytes)
• Contains location and data about 2nd bootloader stage, inside bootloader code section

• Partition table (MS DOS limited to 4 primary partitions)
• Signature helps BIOS to identify from which disk it executes the bootloader code

Bootloader, stage 2

• Located on a disk partition
• Loads operating system kernel and options

 You can choose several bootloader software:

• Grub, GRand Universal Bootloader
• Lilo, LInux LOader
• ...

Grub configuration (< v1.0) :

Grub version < v1.0 uses a singe configuration file, in which all operating systems and kernel options have to be written by hand.

[root@oel-test ~]# cat /boot/grub/menu.lst

default=0

timeout=5

splashimage=(hd0,0)/grub/splash.xpm.gz

hiddenmenu

title Oracle Linux Server-uek (2.6.39-200.24.1.el6uek.i686)

root (hd0,0)

kernel /vmlinuz-2.6.39-200.24.1.el6uek.i686

...

...

Grub 2 configuration (> v1.0) :

Grub version > v1.0 (Grub 2) allows to use automatic search and configuration mechanism that updates the menu.lst file:

[root@oel-test ~]# grub-mkconfig

[root@oel-test ~]# upgrade-grub2

Moreover, if needed, we can add customization during these processes:

[root@oel-test ~]# vim /etc/default/grub # common configuration file for all Operating systems

[root@oel-test ~]# ls /etc/grub.d/* # search and configuration scripts location

[root@oel-test ~]# upgrade-grub2

#> upgrade-grub2
will overvrite /boot/grub/menu.lst

 

Kernel

• Located on a disk partition
• Contains drivers for hardware support
• Lowest Operating System software layer
• Enables multi-task support (scheduler)
• Mounts root file system
• Executes init program

• Usually, an “initrd”, INITial RamDisk (filesystem mounted temporarely in RAM memory), is needed

Init process

The Init process comes first in the operating system startup and defines the running state (Runlevel):

0 – Halt
1 – Single user mode
2 – Multiuser, without NFS
3 – Full multiuser mode
4 – Unused
5 – Full multiuser and graphical mode
6 – Reboot

Default configuration file location:

[root@oel-test ~]# cat /etc/inittab

id:5:initdefault:

Runlevel service script organization:

Runlevel scripts are organized acoording to a strict naming convention, allowing their execution order and purpose:

It is possible to invoke a manual action related to a service:

[root@oel-test ~]# /etc/init.d/service_name start|stop|restart|status|reload

Starting service : [ OK ]

[root@oel-test ~]# service service_name start|stop|restart|status|reload

Runlevel service script configuration

It is possible to manage service execution scripts using more high-level commands:

chkconfig [--level levels ] service_nqme on|off|reset

[root@oel-test ~]# chkconfig --level 2345 ntpd on

[root@oel-test ~]# chkconfig --list ntpd

ntpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off

It is also possible to use a graphical tool:

[root@oel-test ~]# ntsysv --level 5

That's it!

Vor einigen Wochen hatte ich im Rahmen der Frankfurter Datenbanktage Gelegenheit zu einem Interview mit Andrea Held, der fachlichen Leiterin des Events und Geschäftsführerin der Firma Held Informatik.

Das Ergebnis des Gesprächs ist dieses Video, in dem Sie mehr über dbi services, das schnelle Wachstum unserer jungen Firma und unser Dienstleistungsportfolio erfahren. Gucken Sie doch mal rein, falls Sie wissen wollen, wie dbi services "tickt" .

Nochmals vielen Dank an Andrea Held für das interessante und anregende Gespräch! Und natürlich auch für die super organisierten Frankfurter Datenbanktage, an denen dbi services auch 2014 wieder als Partner auftreten wird.

In January, I promised to show you the next step of a procedure to manage templates with Virtual Box. Don't worry, I have not forgotten but I was very busy! This step, the final one, consists on customizing of a virtual machine configuration at the start. VMware does it with Templates on ESX, using for example Sysprep for MS Windows guests. I am going to show you how to do it with one simple text file.

 

Sorry for Windows adepts, my example is based on an Oracle Linux 6. But the scenario is the same for both environments, there are just different commands and syntaxes on Windows.

 

Last time, we looked at the startup step. You have one or more cloned virtual machines, all identical: same hostname, same IP, etc. The purpose here is to customize each one in order to have machines based on a single template, but with proper network configuration, without causing for instance any Oracle issues.

 

When you use the script to clone the template virtual machine, a parameter file (conf.ini) is generated in a dedicated shared folder for each new virtual machine, containing a new hostname, IP and netmask, and an init flag. This file will allow the virtual machine to be reset at its first startup. The init flag will prevent the script to be run at each startup:

• 0: script is not executed
• 1: script is executed.

 

Let's see how it works. Here is an example of the parameter file:

 

vmtest01:192.168.1.101:255.255.255.0:1

 

You have to create a new script to read information on this file. The script will:

 

• Create a mountpoint to the shared folder created at previous steps

 

mount -t vboxsf vmform /mnt/vmform

 

• Parse the file conf.ini in the mount point

 

CONF=`cat /mnt/vmform/conf.ini`
NEW_HOSTNAME=`echo $CONF | cut -d":" -f1`
NEW_IP=`echo $CONF | cut -d":" -f2`
NEW_MASK=`echo $CONF | cut -d":" -f3`
FLAG=`echo $CONF | cut -d":" -f4`

 

• Change the hostname by editing /etc/sysconfig/network file

 

cat /etc/sysconfig/network | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Update the /etc/hosts file

 

cat /etc/hosts | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Update Oracle configuration (tnsnames.ora and listener.ora files)

 

cat $TNS_ADMIN/listener.ora | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"
cat $TNS_ADMIN/tnsnames.ora | sed -e "s/$HOSTNAME/$NEW_HOSTNAME/I"

 

• Change the current IP and Netmask

 

IP=`ifconfig eth0 | awk '/^ *inet / {print substr($2, 6)}'`
MASK=`ifconfig eth0 | awk '/^ *inet / {print substr($3, 6)}'`
cat /etc/sysconfig/network-scripts/ifcfg-eth0 | sed -e "s/$IP/$NEW_IP/g"
cat /etc/sysconfig/network-scripts/ifcfg-eth0 | sed -e "s/$MASK/$NEW_MASK/g"

 

• Delete the /etc/udev/rules.d/70-persistent-net.rules file, in order to reinitialize the network interface allias

 

rm -f /etc/udev/rules.d/70-persistent-net.rules

 

Remark: To avoid any problem, delete the HWADDR parameter from scripts of any network interfaces (/etc/sysconfig/network-scripts/ifcfg-ethX).

 

• Reset the init flag to a null value, to avoid the script to be run at each startup

 

cat /mnt/vmform/conf.ini | sed -e "s/1$/0/"

 

• Restart the virtual machine

 

 reboot

 

Of course, I only give you the main commands here. You have to adapt the script to check errors, make copies before editing files, create the temporary files required by the said command, etc.

 

The script can be copied after creating cloned virtual machines, but it can be fastidious if you have to create a lot of VMs. My advice is to copy it on the template before the clonage. This way, the machines will automatically be customized at the first startup and restarted when script is finished.

 

It will be completely automatic once your template is ready. When I created a new lab for our dbi InSite workshops, I clicked on the main script (duplicate_script.cmd) and was able to go drink a coffee afterwards - a big coffee because the process is not that fast :-) When I came back, all of my virtual machines were running and completely ready for work. I was able to delete the virtual machines after the training sessions and recreate them very, very easily!

 

Contact me if you need help or if you have any remarks on my tool.

My last blog was about installing and configuring Microsoft SQL Server 2012 Distributed Replay. This time, I will show you how you can create a trace and replay it with Distributed Replay Clients on a specific SQL Server target.

This is my Distributed Replay configuration:

 

Creation of the trace

I will use the two Distributed Replay clients to generate a workload against the target server with two PowerShell Windows which will execute diverse SQL scripts. To capture the trace, I will use the SQL Server profiler tool which is known in SQL Server to create, watch, store, and replay a trace.

Go to Microsoft SQL Server Management Studio, Tools and Open SQL Server Profiler:

 

Connect to the target and a screen for trace properties opens:

 

Add a name to your trace, save it to a file, change the property "Maximum file size" to avoid the split of your trace file. You should also enable a stop time for your trace which will save the workload during 20 minutes.

When the trace is done, you have to copy it on your Distributed Replay controller (vmtestsqlwin01) to perform the preprocess task.

 

Preprocess the trace

To preprocess the trace we have first to open a command prompt windows and start the Distributed Replay controller services:

Now that the service is started, we have the possibility to edit the preprocess configuration file to add System Sessions during our replay. This configuration file (DReplay.Exe.Preprocess.config) is an XML file which looks like this:

 

The syntax of the prepross command is:

 

dreplay preprocess[-m controller] -i input_trace_file     -d controller_working_dir[-c config_file][-f status_interval] 

To preprocess your trace, you should go back to your command prompt windows, execute 'cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" ' and run the preprocess command:

 

This process will create two files on c:\distributedreplay which will be necessary to perform the replay. Those two files are ReplayEvents.irf and TraceStats.xml.

You can see the complete preprocess below:

 

Replay the trace

Now that the trace is in preprocess, your Distributed Replay environment is ready to replay the trace on the target SQL Server vmtestsqlwin03.

First, you have to start the Distributed Replay client services on each client(vmtestsqlwin01 and vmtestsqlwin02) via the net start command on a command prompt window:

 

NET START "SQL Server Distributed Replay Client"

 

When, done you can go to the log file to see if both clients are connected to the controller (I explained how to do that in my first blog on this topic: SQL Server 2012: Installing and configuring Distributed Replay)

Before running the replay, you have the possibility to edit the replay configuration file to change parameters. This configuration file (DReplay.Exe.Replay.config) is an XML file which looks as follow:

 

The syntax of the replay command is:

 

dreplay replay[-m controller] -d controller_working_dir [-o]     [-s target_server] -w clients [-c config_file]     [-f status_interval]

• c is mandatory is you change the Replay configuration file or if you store it in a different place as the normal one
• o is to capture  the clients' replay activity and saves it to a result trace file in the path specified by the "resultdirectory" element in the client configuration file
• f is to specifie the frequency (in seconds) at which to display the status, by default each thirty secondes

 

Go to "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" and execute the following replay command:

 

DReplay replay -s “vmtestsqlwin03" -d "c:\distributedreplay" -w “vmtestsqlwin01,vmtestsqlwin02“ -o -f 15

This command will execute a replay of the trace from the working directory c:\distributedreplay where the preprocessed files are stored to the target SQL Server vmtestsqlwin03 via the two clients vmtestsqlwin01 and vmtestsqlwin02.

An output file will be created on each client in the Result Directory and the screen will be updated each fifteen secondes during the replay:

 

During the replay process, you can follow the status on the screen. When the replay is finished, you have the possibility to check the replay trace result on the ResultDir directory of each client.

 

Conclusion

Microsoft SQL Server Distributed Replay is in my opinion the best way to simulate a mission critical workload with the availability to run the trace up to sixteen clients with different modes like stress mode or synchronisation mode.

The drawback of this solution is that the implementation is more complex than with SQL Server profiler and also needs more resources (one controller, several clients and a target).

Three weeks ago, Microsoft published the third Cumulative Update (CU3) for Microsoft SQL Server 2012 Service Pack one. As usual, if this Cumulative Update solves an issue you are facing, install it quickly - otherwise, do not install it.

Which issues are covered by this CU3?

It contains 38 Hotfixes:

• 27 for the Engine
• 8 for SSAS
• 3 for SSRS

To have more details or information about these hotfixes, click here.

 

Historic of SQL Server 2012

The build number of this Third Cumulative Update is 11.00.3349.

Let's resume the different versions of SQL Server 2012 we have for the moment:

 

Date SQL Server 2012 version Build

November 2010

Community Technology Preview 1 (CTP1)

11.00.1103

July 2011

Community Technology Preview 3 (CTP3)

11.00.1440 

November 2011

Release Candidate 0 (RC0)

11.00.1750 

December 2011

Release Candidate 1 (RC1)

11.00.1913 

March 2012

RTM

11.00.2100

April 2012

Cumulative Update 1 (CU1)

11.00.2316

June 2012

Cumulative Update 2 (CU2)

11.00.2325

August 2012

Cumulative Update 3 (CU3)

11.00.2332

October 2012

Microsoft Security Bulletin MS12-070

11.00.2376

October 2012

Cumulative Update 4 (CU4)

11.00.2383

November 2012

Service Pack 1 (SP1)

11.00.3000

November 2012

Cumulative Update 1 (CU1)

11.00.3321

January 2012

First Hotfix for SP1

11.00.3335

January 2013

Cumulative Update 2 (CU2)

11.00.3339

March 2013

Cumulative update 3 (CU3)

11.00.3349

 

On the same date, a Cumulative Update has been also published by Microsoft for SQL Server 2008 SP3. It is the 10th (CU10). You can have a look at it here.

A few weeks ago, I posted an article about Oracle Database Appliance, focusing on ODA version 2.5.0.0.0. However, a new update of ODA, version 2.5.5.0.0, was released by Oracle on 5 March. This posting will focus on the new ODA release, which leads to a new hardware architecture.

 

So, what's new with this new ODA version? According to the Release Notes, ODA now supports the new X3-2 architecture which allows additional hardware and storage capabilities.

 

Physical interconnection

Remember, the previous ODA version was shipped in the form of two 2U nodes. Each one was hosting multiple storage devices, and the nodes were already linked to each other. The front was composed of disks and the host was accessible from the rear. The cluster interconnect was included inside the rack. Users just had to plug in power as well as network and start the rack.

With the X3-2 architecture, ODA still comes in a 4U form, but each node is now independant and 1U sized. You can access the host by the front side, and each one is attached to a 2U storage shelf. This means that the user has to plug in network and power, but the interconnect and the storage also has to be configured by the user. The connection becomes a little bit more difficult!

 

Evolution perspective

When I reviewed ODA last week, I pointed out a major drawback: the hardware limitation. The new architecture now is evolutive: the storage shelf shipped with ODA can be upgraded with a second 2U storage shelf to get more storage capabilities. Other hardware components are still limited, but have been strongly upgraded with X3-2.

 

Hardware updates

The hardware is clearly more powerful. Here are the new hardware specifications of ODA X3-2 for each node - in comparison to the old configuration:

Item Old configuration New configuration CPU 2 x X5675 2 x E5-2690 Cores per CPU 6 8 Memory 96 GB 256 GB SSD Disks for REDO Logs 4 x 73 GB 4 x 200 GB SAS Disks for data files 20 x 600 GB 15k rpm 20 x 900 GB 10k rpm SATA disks for operating system 2 x 500 GB 2 x 600 GB Cluster interconnect 2 x 1 GbE fibre 2 x 10 GbE copper

 

We can see that Oracle has put more powerful components on its ODA: more CPU, more Memory, and more disk space to host more databases.

 

To conclude

ODA virtualization support is still limited to the local storage of the host (2x600GB SATA disks) - no update on this point. This ODA version does not provide new features. In fact, this new release of ODA is "just a boost" of the old ODA architecture, with twice more CPU power and four times more storage capabilities.

Distributed Replay is a new fonctionality of Microsoft SQL Server 2012. It helps you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL Server tunings by replaying a trace captured from a productive SQL Server environment to a new SQL Server test environment.

This new functionality is similar to SQL Server Profiler, but with more possibilities: e. g. replaying the trace from multiple clients (up to sixteen), use a stress or  synchronization mode, configure options like think time, or connect time etc.

 

Distributed Replay installation and configurations

To use Distributed Replay, we have to install a Distributed Replay Controller and 1 to 16 Distributed Replay Clients. In this exemple, I will use a Distributed Replay controller and a Distributed Client on server VMTESTSQLWIN01, a second Distributed Replay Client on server VMTESTSQLWIN02 and a target instance with SQL Server 2008 R2 on WMTESTSQLWIN03.

Do not fortget to also install Management Tools which provides Distributed Replay administration tools!

First, we have to create two Active Directory accounts.

1) One for the Distributed Replay Controller: MSTESTDR_controller:

2) And another for the Distributed Replay Client: MSTESTDR_client:

 

Distributed Replay installation

We now have to install Distributed Replay Controller, Distributed Replay Client, and Management Tools on our VMTESTSQLWIN01 server which will be the Controller of our Distributed Replay and also the Client:

On the Server Configuration, add the two Actice Directory accounts created for Distributed Replay Controller and Distributed Replay Client:

On the Distributed Replay Controller Page, add the Active Directory account previously created for the Controller. On the Distributed Replay Client page, add the Controller name:

Continue the installation until the Complete Screen appears:

Now, our VMTESTSQLWIN01 has Distributed Replay installed.

We have to install the second Distributed Replay Client on VMTESTSQLWIN02:

On the Server Configuration, add the Actice Directory account created for Distributed Replay Client:

Configure the Distributed Replay Controller for the client:

Finish the installation:

 

Windows Firewall configurations

To allow Distributed Replay Clients to connect and register to the Distributed Replay controller, inbound connection for DReplayClient.exe application has to be allowed on each Distributed Replay Client through the Windows firewall.

Go to Windows firewall, Inbound Rules, and add a new rule for a program:

Browse to the DReplayClient.exe location:

Allow the connection:

Give a name to this new rule and click Finish:

Now, configure the Windows firewall on the Distributed Replay Controller to allow inbound connection for DReplayController.exe application using the same way.

In the target Server VMTESTSQLWIN03, we also have to configure the Windows firewall for inbound on port TCP 1433 to let the Distributed Replay Client write on the target. Select the port:

Protocol TCP and port number 1433:

Allow the connection:

Apply this rule for Domain, Private, and Public:

Specify a name for this new inbound and click Finish to save it:

Now the firewall is configured on each server!

 

Start Services and check client registrations

You first have to start the Controller service on the Distributed Replay Controller.
Go to VMTESTSQLWIN01, open a command prompt screen and enter:

 

NET STOP "SQL Server Distributed Replay Controller"
NET START "SQL Server Distributed Replay Controller"

 

You should see the result of this command in the log folder of Distributed Replay Controller under C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\log.

You have now to start Distributed Replay Client service on your Distributed Replay Clients VMTESTSQLWIN01 and VMTESTSQLWIN02. Verify that they are correctly synchronised with your Distributed Replay Controller.

On both servers, open a command prompt screen and run:

 

NET STOP "SQL Server Distributed Replay Client"
NET START "SQL Server Distributed Replay Client"

 

To see if the Client is successfully registred with the controller, open the last log file in the location C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\log and check the last line of the file. This could be something like that:

If this was successul for the two clients, the configuration between the controller and clients is finished and the Distributed Replay is ready to work.

If you have a log file like this...

...it means the registration failed, so:

• check if the controller name is right in the DReplayClient.config file
• check if, for each client, there is an inbound rule to allow connections for the DReplayClient.exe application
• check if, for the controller, there is an inbound rule to allow connections for the DReplayController.exe application

  Conclusion

At this point, our Distributed Replay application is ready to use, clients and controller are registred together.

You now have to create a trace, preprocess it and replay it from our clients to the target SQL Server.

This will be the content of my next blog posting.

Last february, I had the chance to attend an Oracle presentation about ODA: Oracle Database Appliance. I found it interessant to share what I have learned about this technology. Which, I confess, convinced me.

 

Summary

Before developing the software and hardware components, here is a small overview of ODA.

ODA is an all in one solution providing Hardware, Operating System, and database components. It comes in a 4U rack server form, composed of two distinct nodes linked each other by the rear of the rack.

Oracle called it “Database Appliance”, but I remember the first thing I said to myself was: Why not call this technology Oracle Cluster Appliance? Indeed, ODA is a high availability database solution and provides two nodes, with Automatic Storage Management (ASM) and Grid Infrastructure, to implement Oracle Real Application Cluster (RAC) or RAC One Node with Enterprise Edition databases.

 

ODA offers the complete stack for databases hosting:

 

 

The solution is shipped with Operating System and an Appliance Manager pre-installed. The ASM storage is also pre-configured. So, the user just has to plug in power and network, and to configure the system through a wizard (install clusterware, create databases, etc.).

Another positive point is the ability to enable or disable CPU cores from ODA, thus to limit the licensing impact. It is the only Oracle offer allowing to acquire processor licensing in relation to the need, just by limiting processor activation in the BIOS. Even if CPU cores are disabled, all memory and storage remain available, without restriction.

To resume, ODA saves time and money by simplifying deployment, maintenance, and support of high-availability database solutions.

 

Hardware

ODA has the following specifications:

 

Item Per node Total CPU 2 x Intel Xeon x5675 4 Cores 12 (6 per CPU) 24 Memory 96 GB 192 GB Storage 2 x 250 GB 7.2 rpm SATA Raid 1 1 TB 2 x 73 GB SSD for Redo 292 GB 10 x 600 GB 15k rpm SAS2 for Datas 12 TB

 

No network storage system can be attached to ODA, except Sun ZFS Storage Appliance. But the rack has a high level of built in redundancy to compensate:

 

• Storage
• Network
• Power
• Cooling
• Server

 

Note that the interconnect, for the cluster, is also built in.

  

Software

ODA comes with following software:

 

• Oracle Enterprise Linux 5.8 UEK
• Appliance Manager 2.x
• Grid Infrastructure 11g R2
• Oracle Rdbms 11g R2

 

What is Appliance Manager?

Appliance Manager is responsible of Provisioning, Storage Management, Patching and Validation and Diagnostics.

 

Provisioning

The OS is deployed in factory. Devices, partitions and file systems are in place, and OS best practices are configured.

To deploy the database, just download latest version of Grid Infrastructure and RDBMS, install and configure them through Appliance Manager wizard.

All is fully tested and validated by Oracle.

 

Storage Management

The user has no storage to create or configure. ASM is already configured, and 3 diskgroups are created per default: DATA for datas, RECO for archive logs, and REDO for redo logs.

Storage errors and problems are monitored by Appliance Manager. If a disk fails, just change it and ODA makes the rest.

 

Patching

Patches all the components: firmware, OS, Appliance Manager itself, Grid Infrastructure, and Database.

To patch ODA, just download the bundle patch and select what to patch (in reality, this is a little bit more difficult because patching is run via command line...).

The patch bundle is fully tested by Oracle.

 

Validation and Diagnostics

Auto Service Request (ASR) can automatically open Service Requests.

Some command tools allow to easily see best practices violations or components failure.

 

A word about virtualization

The latest version of ODA now offers a virtualization platform. This is a good point, because it allows to use your performant environment (ODA) to do something else than database activities. For example, host an application server.

The feature is based on Oracle VM and can use CPU cores that are not licenced for database.

But the first aim of ODA is to provide database services. So, in any case, priority is given to databases. Virtualization support will not benefit of SSD disks or 12 TB of storage, which are all dedicated to database.

Templates and VM can only use the local storage of the node and there is no redundancy of the virtual machines: nodes are standalone hypervisors.

Also note that there is no GUI but only command line to manage virtual machines.

 

Advantages & drawbacks

Advantages

• One provider for all the stack
• Fully tested and compatible
• Reduced costs compared to a complete deployment
• Simplicity of deployment and administration
• Performance
• Virtualization support since last release
• Possibility to disable CPU cores to reduce licencing

 

Drawbacks

• Not expandable to larger solutions. In that case, you can have a look at the Oracle Exadata machines.
• Limited virtualization storage and performances
• Virtualization administration limited to command line

 

Conclusion

ODA is quite a performant solution and I would certainly talk about it to some of my customers. It also offers a virtualization support, which allows to share performances with other services than databases.

But I think that this type of technology is dedicated to small companies or small environments, because of its limitations:

• Memory or storage cannot be upgraded
• No network storage system can be attached
• Limited to two nodes

It is a good alternative when you do not need a big installation and want to maximize deployment and administration simplicity without sacrificing performance. Otherwise, I would highly recommend to take a look at the Oracle Exadata machines.

Ganz klar: Unser dbi services Goldhase war das inoffizielle Maskottchen der Frankfurter Datenbanktage! Der Nachfrage nach zu urteilen war es sowohl ein Augen- als auch ein Gaumenschmaus ... wir haben auch schon eines "geschlachtet" und brüderlich geteilt.

Wir waren in 3 Sessions aktiv und hatten die Freude, viele interessierte Teilnehmer an unserem Stand zu begrüssen!

Stéphane Haby hat in seiner Session ein neues SQL Server Monitoring-Tool für Microsoft DBAs vorgestellt: "SQL Server Management Datawarehouse". Die Teilnehmer wurden von Stéphane regelmässig durch ein paar Fragen "wach gehalten" und für richtige Antworten mit unserem dbi Swiss Army Knife und Goldhasen belohnt.

Hervé Schweizer stellte in seiner Session "Dbvisit Replicate: kostengünstige Migration von Oracle Datenbanken" eine nahezu Zero-Downtime-Migration vor.

Ich selbst durfte das Produkt "Dbvisit Standby" vorstellen. In der Session habe ich die verschiedenen Schritte aufgezeigt, die für den Aufbau einer kostengünstigen hochverfügbaren Umgebung für Oracle Standard Edition erforderlich sind.

Sie werden in Kürze auf unserer Website unsere Präsentationen herunterladen können: http://dbi-services.com/ffdb

Und natürlich auch auf der offiziellen Seite der Frankfurter Datenbanktage:

http://www.frankfurter-datenbanktage.de/

Wir wurden von unserem Marketingleiter Michael Teiwes und unserem CEO Yann Neuhaus begleitet und möchten uns bei beiden ganz doll bedanken!

Ein herzlichen Dank an das gesamte Organisationsteam um Susanne Benning und Andrea Held für das rundum gelungene Event und die tolle Location!

See you next year!

Hervé Schweizer, Stéphane Haby, Jérôme Witt

I have read some blog postings concerning the new perfmon counters, but I have not seen really extensive overviews yet. In this post, I would like to present not only what is new, but also what has changed (or not) and how to find it. The first step is to select all objects in SQL2008R2 and SQL2012 from the view sys.dm_os_performance_counters.

 

Remarks: All object or counter definitions from this post come from msdn.

 

Objects comparison

The query to do a comparison is simple:

select disctinct object_name from sys.dm_os_performance_counters

 

The result is 27 objects for SQL Server 2008 R2 and 33 for SQL Server 2012.
Additionally, I found that 1 object disappeared (Buffer Partition) and 7 appeared (Query Execution, FileTable, Batch Resp Statistics, Memory Broker Clerks, Memory Node, Database Replica and Availability Replica).

 

The Buffer Partition object provides counters to monitor how SQL Server uses free pages (Free list empty/sec, Free list requests/sec and Free page)

 

Objects without changes between 2008R2 and 2012 

In SQL Server 2012, 19 objects out of the 27 from SQL Server 2008R2 have not changed (counter added or deleted):

• MSSQL$...:Transactions
• MSSQL$...:Wait Statistics
• MSSQL$...:User Settable
• MSSQL$...:Workload Group Stats
• MSSQL$...:Cursor Manager by
• MSSQL$...:Resource Pool Stats
• MSSQL$...:Broker/DBM Transport
• MSSQL$...:Latches
• MSSQL$...:SQL Statistics
• MSSQL$...:Exec Statistics
• MSSQL$...:CLR
• MSSQL$...:General Statistics
• MSSQL$...:Cursor Manager Total
• MSSQL$...:SQL Errors
• MSSQL$...:Broker Statistics
• MSSQL$...:Plan Cache
• MSSQL$...:Broker TO Statistics
• MSSQL$...:Broker Activation
• MSSQL$...:Catalog Metadata

 

I have used this query to find them:

select distinct 'select COUNT(*) as '''+RTRIM(object_name)+'''from sys.dm_os_performance_counters where object_name='''+ RTRIM(object_name)+'''' from sys.dm_os_performance_counters

 

Objects with changes

There are 19 objects without changes and 7 new in SQL Server 2012, so I have calculated that 7 objects have changed

• from SQL Server 2012: 33-19-7=7
• from SQL Server 2008R2: 27-19-1(deleted object)=7

I have created a query to get all counters by object for these 7 objects with changes:

 

select distinct 'select counter_name, instance_name from sys.dm_os_performance_counters where object_name='''+ RTRIM(object_name)+''' ORDER BY instance_name DESC, counter_name DESC' from sys.dm_os_performance_counters

 

MSSQL$...:Buffer Node

This provides counters that complement counters provided by the Buffer Manager object.

 

Is deleted:

• Target pages: Ideal number of pages in the buffer pool on this node
• Stolen pages: Number of pages used for miscellaneous server purposes (stolen from the buffer pool) on this node
• Free pages: Total number of free pages on this node
• Foreign pages: Number of pages that come from a different NUMA node

Is added:

• Nothing

 

MSSQL$...:Buffer Manager

The Buffer Manager object provides counters to monitor how SQL Server uses

• Memory to store data pages, internal data structures, and the procedure cache
• Counters to monitor the physical I/O as SQL Server reads and writes database pages

Is deleted:

• Total pages: Number of pages in the buffer pool (includes database, free, and stolen pages)
• Stolen pages: Number of pages used for miscellaneous server purposes (including procedure cache)
• Reserved pages: Number of buffer pool reserved pages
• Free pages: Total number of pages on all free lists
• AWE write maps/sec: Number of times per second that it is necessary to map in a dirty buffer so it can be written to disk
• AWE unmap pages/sec: Number of SQL Server buffers that are unmapped per second
• AWE unmap calls/sec: Number of calls to unmap buffers per second
• AWE stolen maps/sec: Number of times per second that a buffer was taken from the free list and mapped
• AWE lookup maps/sec: Number of times per second that a database page was requested by the server, found in the buffer pool,and mapped

Is added:

• Integral Controller Slope: Not documented!
• Background writer pages/sec: Not documented!

  

MSSQL$...:Locks

The Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types.

Is deleted:

• Nothing

Is added:

• A new instance: OibTrackTbl Not documented !

 

MSSQL$...:Memory Manager

The Memory Manager object in Microsoft SQL Server provides counters to monitor overall server memory usage.

Is deleted:

• Nothing

Is added:

• Stolen Server Memory (KB): Specifies the amount of memory the server is using for purposes other than database pages
• Reserved Server Memory (KB): Indicates the amount of memory the server has reserved for future usage
• Log Pool Memory (KB): Not documented!
• Free Memory (KB): Specifies the amount of committed memory currently not used by the server
• External benefit of memory: Not documented!
• Database Cache Memory (KB):Specifies the amount of memory the server is currently using for the database pages cache

 

MSSQL$...:Access Methods

The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed

Is deleted:

• Nothing

Is added:

• InSysXact waits/sec: Not documented!

 

MSSQL$...:Databases

The Databases object in SQL Server provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities

Is deleted:

• Nothing

Is added:

• Log Pool Requests/sec: The number of log-block requests processed by the log pool
• Log Pool Disk Reads/sec: Number of disk reads that the log pool issued to fetch log blocks
• Log Pool Cache Misses/sec: Number of requests for which the log block was not available in the log pool
• Log Flush Write Time (ms): Time in milliseconds for performing writes of log flushes that were completed in the last second

 

MSSQL$...:Deprecated Features

The Deprecated Features object in SQL Server provides a counter to monitor the features designated as deprecated.

Is deleted:

• sp_dropalias
• sp_dboption
• SET DISABLE_DEF_CNST_CHK
• RESTORE DATABASE or LOG WITH DBO_ONLY
• Oldstyle RAISERROR
• Non-ANSI *= or =* outer join operators
• FASTFIRSTROW
• database_principal_aliases
• CREATE TRIGGER WITH APPEND
• COMPUTE [BY]
• BACKUP DATABASE or LOG WITH
• BACKUP DATABASE or LOG WITH MEDIAPASSWORD

Is added:

• sp_trace_setstatus
• sp_trace_setfilter
• sp_trace_setevent
• sp_trace_getdata
• sp_trace_create
• sp_dropsrvrolemember
• sp_droprolemember
• sp_db_increased_partitions
• sp_configure 'default trace'
• sp_configure 'c2 audit mode'
• sp_configure 'affinity64 mask'
• sp_configure 'affinity mask'
• sp_changedbowner
• sp_addsrvrolemember
• sp_addrolemember
• SET ERRLVL
• SET FMTONLY ON
• Old NEAR Syntax
• objidupdate
• fn_trace_gettable
• fn_trace_getinfo
• fn_trace_getfilterinfo
• fn_trace_geteventinfo
• DBCC_IND
• DBCC_EXTENTINFO
• Database Mirroring

The deleted counters are for SQL 2000 compatibility and in SQL Server 2012, SQL 2000 is not supported anymore as compatibility mode. The added counters are the deprecated features for SQL server 2008 and 2008R2.

And to finish, the 7 new objects…

New Objects

MSSQL$...:Query Execution

Not documented!

New counters:

• Remote resend requests/sec
• Remote requests/sec
• Remote activations/sec
• Rem Req Cache Hit Ratio Base
• Rem Req Cache Hit Ratio
• Local data access/sec

MSSQL$...:FileTable

Not documented! But easy to find that is for measuring performance of the new feature FileTable.

New counters:

• Time update FileTable item BASE
• Time to get FileTable item BASE
• Time rename FileTable item BASE
• Time per file I/O response BASE
• Time per file I/O request BASE
• Time move FileTable item BASE
• Time FileTable handle kill BASE
• Time FileTable enumeration BASE
• Time delete FileTable item BASE
• FileTable table operations/sec
• FileTable kill handle ops/sec
• FileTable item update reqs/sec
• FileTable item rename reqs/sec
• FileTable item move reqs/sec
• FileTable item get requests/sec
• FileTable item delete reqs/sec
• FileTable file I/O response/sec
• FileTable file I/O requests/sec
• FileTable enumeration reqs/sec
• FileTable db operations/sec
• Avg time update FileTable item
• Avg time to get FileTable item
• Avg time rename FileTable item
• Avg time per file I/O response
• Avg time per file I/O request
• Avg time move FileTable item
• Avg time FileTable handle kill
• Avg time FileTable enumeration
• Avg time delete FileTable item

 

MSSQL$...:Memory Node

The Memory Node object in Microsoft SQL Server provides counters to monitor server memory usage on NUMA nodes.

New counters:

• Database Node Memory (KB): Specifies the amount of memory the server is currently using on this node for database pages
• Free Node Memory (KB): Specifies the amount of memory the server is not using on this node
• Foreign Node Memory (KB): Specifies the amount of non NUMA-local memory on this node
• Stolen Memory Node (KB): Specifies the amount of memory the server is using on this node for purposes other than database pages
• Target Node Memory: Specifies the ideal amount of memory for this node
• Total Node Memory: Indicates the total amount of memory the server has committed on this node

 

MSSQL$...:Database Replica

The Database Replica performance object contains performance counters that report information about the secondary databases of an AlwaysOn availability group in SQL Server 2012.

New counters:

• File Bytes Received/sec: Amount of FILESTREAM data received by the secondary replica for the secondary database in the last second
• Log Bytes Received/sec: Amount of log records received by the secondary replica for the database in the last second
• Log remaining for undo: The amount of log in kilobytes remaining to complete the undo phase
• Log Send Queue: Amount of log records in the log files of the primary database, in kilobytes, that has not yet been sent to the secondary replica
• Mirrored Write Transaction/sec: Number of transactions that wrote to the mirrored database and waited for the log to be sent to the mirror in order to commit, in the last second
• Recovery Queue: Amount of log records in the log files of the secondary replica that has not yet been redone
• Redo Bytes Remaining: The amount of log in kilobytes remaining to be redone to finish the reverting phase
• Redone Bytes/sec: Amount of log records redone on the secondary database in the last second
• Total Log requiring undo: Total kilobytes of log that must be undone
• Transaction Delay: Delay in waiting for unterminated commit acknowledgement, in milliseconds

 

MSSQL$...:Availability Replica

The SQLServer:Availability Replica performance object contains performance counters that report information about the availability replicas in AlwaysOn availability groups in SQL Server 2012.

New counters:

• Bytes Received from Replica/sec: Number of bytes received from the availability replica per second
• Bytes Sent to Replica/sec: Number of bytes sent to the remote availability replica per second
• Bytes Sent to Transport/sec: Actual number of bytes sent per second over the network to the remote availability replica
• Flow Control Time (ms/sec): Time in milliseconds that log stream messages waited for send flow control, in the last second
• Flow Control/sec: Number of times flow-control initiated in the last second
• Receives from Replica/sec: Number of AlwaysOn messages received from the replica per second
• Resent Messages/sec: Number of AlwaysOn messages resent in the last second
• Sends to Replica/sec: Number of AlwaysOn messages sent to this availability replica per second
• Sends to Transport/sec: Actual number of AlwaysOn messages sent per second over the network to the remote availability replica

 

MSSQL$...:Memory Broker Clerks

Not documented!

2 instances: Column store object pool and Buffer Pool

Counters per instance:

• Simulation size
• Simulation benefit
• Pressure evictions (pages/sec)
• Periodic evictions (pages)
• Memory broker clerk size
• Internal benefit

 

MSSQL$...:Batch Resp Statistics

Not documented!

4 instances:Elapsed Time:Total(ms), Elapsed Time:Requests, CPU Time:Total(ms) and CPU Time:Requests

Counters per instance:

• Batches >=100000ms
• Batches >="050000ms" &
• Batches >="020000ms" &
• Batches >="010000ms" &
• Batches >="005000ms" &
• Batches >="002000ms" &
• Batches >="001000ms" &
• Batches >="000500ms" &
• Batches >="000200ms" &
• Batches >="000100ms" &
• Batches >="000050ms" &
• Batches >="000020ms" &
• Batches >="000010ms" &
• Batches >="000005ms" &
• Batches >="000002ms" &
• Batches >="000001ms" &
• Batches >="000000ms" &

  Conclusion

This overview demonstrates all performance counters available for SQL Server 2012.
It may be noticed that the majority of new metrics are not documented...
I hope that will change in an nearly future!

The lastest version of Performance Analysis of Logs (PAL) Tool to analyse the result file of a monitoring, haven't included these new counters for SQL Server 2012 yet.

Some references:

• msdn reference for performance monitor for SQL Server here
• Performance Analysis of Logs (PAL) Tool web site here

On 26 February I went to the University of Bern with my colleague Arnaud Berbier to follow an introductory conference on Ceylon, a new language inspired from Java. Unfortunately not presented by Gavin King, the leading developer of Ceylon at Red Hat, the project still was well introduced by Stéphane Epardaud. He is a long-time open source user and contributor, who is now working on the Ceylon compiler:

“Because if you’re not writing a compiler you’re not doing proper programming.”

Well, the show started and in less than one minute we knew we were in an open source project: “From scratch”, “fast programming”, “more readable than Java that you don’t have to comment it”, oh wait…

After our first heart attacks he began to explain what Ceylon is and why they wanted to develop a new language. Ceylon is a language running on a JVM, on Node.js or in a web browser. It has been promoted as a “Java killer” and directly disclaimed by Gavin King:

“First, I never billed this as a Java killer or the next generation of the Java language. Not my words. Ceylon isn't Java, it's a new language that's deeply influenced by Java, designed by people who are unapologetic fans of Java. Java's not dying anytime soon, so nothing's killing it.”

Gavin King is leading the development of Ceylon at Red Hat. He is the creator of Hibernate (persistence solution for Java) and has also contributed to the Java Community Process standards as Red Hat representative for the EJB and JPA specifications and as spec lead of the CDI specification. So Ceylon may be promoted by Red Hat and this why it may have a destiny (maybe like Sun?).

But why building a new language which is like Java? Stéphane Epardaud explained that most of the developers contributing to the Ceylon project were designing and building frameworks and libraries for java and they were frustrated by the limitations they encountered. Not only from the language itself but from the JVM, too. Ceylon is built with an eye to the strength and weaknesses of Java and with the goal to make it easy to learn, read, develop, and maintain.

Then Stéphane Epardaud displayed a first piece of code. There were instructions in the class’ body, parameters in the class signature, no getters/setters, no constructor, no overloading, and the possibility to inherit from several classes at the same time. It was totally different than Java and that was what we were looking for.

The program is structured in modules which are built of packages, then source files. Here is a sample of code:

 

 

We will not detail all available features of the syntax of this new language here. You can easily find it there: http://ceylon-lang.org. The presentation rapidly showed some of the features and brought us some misunderstanding and doubts about the readability of a huge application built upon Ceylon. But with one hour length, the conference cannot provide as much information as the website which is well done for an open source project. You can find explanations of each features and how to use them with examples. In addition, you can figure out why they did it like this.

Programs run in the JVM for the time being and they can be written in command line, thanks to an Eclipse plugin which opens a dedicated perspective or in a web browser IDE which is being developed. The project is still a "baby" and it is coming to the 0.5 version (Milestone 5). A lot of work has still to be done; a compiler is working but the syntax error explanations are still ambiguous. Lots of projects around Ceylon are started and a community is growing; open source communities are famous for fast development and this language deserves to grow. We strongly hope that Ceydevs (Ceylon’s contributors) will not just build frameworks or APIs as fastly as possible to reduce the gap with Java. But we hope they will build strong modules with clear and limpid documentations in order to lay the foundation of a strong alternative Java language.

Sources:

Gavin King - http://in.relation.to/user/gavin
Ceylon - http://ceylon-lang.org

As everybody knows, Linux regroups several operating system families. The most popular Linux OS are Debian, Red Hat, or Suse. But each family has many derived Linux distributions. In November 2006, Oracle released the first version of its operating system, totally based on Red Hat Enterprise Linux: Enterprise Linux. 

 

Up to release 5, the system came with two kernels:

• A customized kernel with bug fixes, based on Red Hat kernel, installed and started by default
• An unmodified Red Hat kernel for manual installation, identical to the one released on Red Hat Enterprise Linux systems

 

From Enterprise Linux 5 to Enterprise Linux 5 Update 4, the unmodified Red Hat kernel was installed by default and the kernel with bug fixes had to be installed manually.

In Oracle Linux 5 Update 5, Oracle introduced a new Kernel: the UEK (for Unbreakable Enterprise Kernel). This kernel consists in an upgraded kernel, with specific features or enhancements for Oracle software. In Oracle Linux 5.5, the Red Hat kernel was still installed and started by default, the UEK and the kernel with bug fixes had both to be installed manually.

Since Oracle Linux 5 Update 6, the Oracle UEK has replaced the Red Hat kernel. It is installed and started by default, and the Red Hat kernel is just installed, but not enabled. The kernel with bug fixes remains uninstalled but still available for a manual install.

 

The following table lists all Oracle Linux releases, associated to the corresponding kernel(s) and release date (dates are approximations from information displayed on oss.oracle.com and wikipedia.org):

 

Release name Release date Default kernel Optionnal kernel Enterprise Linux 4.4 10/2006 Kernel with bug fixes 2.6.9-42.0.0.0.1.EL n/c

Enterprise Linux 4.5

05/2007 Kernel with buf fixes 2.6.9-55.0.0.0.2.EL n/c

Enterprise Linux 4.6

04/2008 Kernel with bug fixes 2.6.9-67.0.0.0.1.EL n/c

Enterprise Linux 4.7

07/2008 Kernel with bug fixes 2.6.18-78.0.0.0.1.EL 2.6.18-78 (manually)

Enterprise Linux 4.8

05/2009 Kernel with bug fixes 2.6.9-89.0.0.0.1.EL 2.6.9-89 (manually)

Enterprise Linux 4.9

02/2011 Kernel with bug fixes 2.6.9-100.0.0.0.1.EL 2.6.9-100.EL

Enterprise Linux 5

06/2007 2.6.18-8.el5 Kernel with bug fixes 2.6.18-8.0.0.4.1.el5 (manually)

Enterprise Linux 5.1

04/2008 2.6.18-53.el5 Kernel with bug fixes 2.6.18-53.0.0.0.1.el5 (manually)

Enterprise Linux 5.2

06/2008 2.6.18-92.el5 Kernel with big fixes 2.6.18-92.0.0.0.1.el5 (manually) Oracle Enterprise Linux 5.3 (OEL) 01/2009 2.6.18-128.el5 Kernel with bug fixes 2.6.18-128.0.0.0.1.el5 (manually) Oracle Enterprise Linux 5.4 (OEL) 09/2009 2.6.18-164.el5 Kernel with bug fixes 2.6.18-164.0.0.0.1.el5 (manually) Oracle Linux 5.5 09/2010 2.6.18-194.el5

Kernel with bug fixes 2.6.18-194.0.0.0.3.el5 (manually)

UEK 2.6.32-100.0.19.el5 (via uln)

Oracle Linux 5.6 06/2011 UEK 2.6.32-100.26.2.el5

2.6.18-238.el5 (disabled)

Kernel with bug fixes 2.6.18-238.0.0.0.1.el5 (manually)

Oracle Linux 5.7 07/2011 UEK 2.6.32-200.13.1.el5uek

2.6.18-274.el5 (disabled)

Kernel with bug fixes 2.6.18-274.0.0.0.1.el5 (manually)

Oracle Linux 5.8 02/2012 UEK 2.6.32-300.10.1.el5uek

2.6.18-308.el5 (disabled)

Kernel with bug fixes 2.6.18-308.0.0.0.1.el5 (manually)

Oracle Linux 5.9 01/2013 UEKR2 QU2 2.6.39-300.26.1.el5uek

2.6.18-348.el5 (disabled)

Kernel with bug fixes 2.6.18-348.0.0.0.1.el5 (manually)

Oracle Linux 6 02/2011 UEK 2.6.32-100.28.5.el6 2.6.32-71.el6 (disabled) Oracle Linux 6.1 06/2011 UEK 2.6.32-100.34.1.el6 2.6.32-131.0.15.el6 (disabled) Oracle Linux 6.2 12/2011 UEK 2.6.32-300.3.1.el6uek 2.6.32-220.el6 (disabled) Oracle Linux 6.3 06/2012 UEK R2 2.6.39-200.24.1.el6uek 2.6.32-279.el6 (disabled)    Oracle Linux 6.4 02/2013 UEK R2 QU3
2.6.39-400.17.1.el6uek 2.6.32-358.el6 (disabled)

 

I will regulary update this table with new releases of Oracle Linux.

 

UPDATE

In february 28th, Oracle released Oracle Linux 6 Update 4 with the third Quarterly Update of the UEK R2.

You can find the release note on the Oracle website:

https://oss.oracle.com/ol6/docs/RELEASE-NOTES-U4-en.html

Changing the default TCP port 1433 for SQL Server is an important step for securing your SQL Server. I have written a script in PowerShell to modify the port number that helps me perform this task. This blog posting will show you how to do it. Please note that the code in this article is for SQL Server 2012 on Windows Server 2012.

 

Initialization

Run SQLPS to launch the SQL PowerShell in a command prompt. 

First you have to initialize the Microsoft.SqlServer.Management.Smo namespace that contains the classes representing the core SQL Server database engine objects:

 

$smo = 'Microsoft.SqlServer.Management.Smo.'


 

Then, you have to set the ManagedComputer object that represents a Windows Management Instrumentation (WMI) installation on an instance of Microsoft SQL Server.

 

$wmi = new-object ($smo + 'Wmi.ManagedComputer')

 

Let me show you my instance information with $wmi (sample):

 

 

You will notice that in ClientProtocols, I have the tcp protocol.

 

Searching the port value

The next step is to find the Tcp Protocol settings:

 

$uri = "ManagedComputer[@Name='']/ ServerInstance[@Name='']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)

 

This is my $tcp variable (sample):

 

Please not that IsEnabled is set to true. If you have IsEnabled on false, you can set it up to true with this command:

 

$Tcp.IsEnabled = $true

 

And do not forget to validate this change with an Alter:

 

$Tcp.Alter()

 

To check the port, you need to go to the IPAll characteristics:

 

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties

 

Like in the SQL Server configuration manager, two sections will appear: One is for the TcpDymanicPorts and the second for the fixed TcpPort (sample):

 

 

Changing the TCP port

To change the value, it’s very simple, the field is simply named Value:

 

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="xxxxx"

 

 

If you look at the characteristics with your precedent command...

 

($wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties)

 

...the port has changed, but in the Sql Server Configuration Manager, the port still is 1433:

 

 

But why??? 

 

Validating the change

Yes, of course... you need to validate the change with the Alter command!

 

 

And now, if you look at the SQL Server Configuration Manager, the change is applied:

 

 

To finish, do not forget to restart your services to activate the port change.

Diese Frage habe ich in den letzten Jahren öfters gehört. Zumindest seitdem die DBAs Oracle Datapump verwenden anstatt des alten Export/Import Tools. Angeblich war es früher mit dem Import-Parameter-Tool „INDEXFILE“ viel einfacher. Tja, die Wahrheit ist: Mit Datapump geht es noch leichter!

Dies möchte ich anhand eines ganz normalen Datapump-Export–Files verdeutlichen. In folgendem Beispiel handelt es sich um einen einfachen Schema-Export, der wie folgt erzeugt wurde:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] cat expdp_SalesApp.par
SCHEMAS=sh
DIRECTORY=data_pump_dir
DUMPFILE=expdp_SH_2013-02-17.dmp
LOGFILE=expdp_SH_2013-02-17.log
PARALLEL=4

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] expdp parfile=expdp_SalesApp.par

Export: Release 11.2.0.3.0 - Production on Sun Feb 17 16:08:27 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

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
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
. . exported "SH"."CUSTOMERS"                            9.853 MB   55500 rows

... [Aus Formatierungsgründen wurde dieser Teil herausgefiltert]


. . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/admin/DB112/dpdump/expdp_SH_2013-02-17.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:09:00

 

Zuerst sollten die gesamten Daten und alle anderen Objekte, ausser die Indexes importiert werden. In diesem Fall ist der Parameter EXCLUDE der Schlüssel zum Erfolg:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 exclude=INDEX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 16:15:42 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

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
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 exclude=INDEXProcessing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

... (Aus Formatierungs-Gründ wurde dieser Teil herausgefiltert)

 

In einem zweiten Schritt müssen die Indexes importiert werden. Der INCLUDE Parameter enthält den INDEX-Schlüssel, dieser umfasst die Index-Statistiken. Die Tablespace-Änderung erfolgt durch den REMAP_TABLESPACE Parameter:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX remap_tablespace=SH_DATA:SH_IDX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 16:23:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

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
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX remap_tablespace=USERS:SH_IDX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"SH_V2"."PROMO_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."CUSTOMERS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."PRODUCTS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."TIMES_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."CHANNELS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."COUNTRIES_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."TIMES_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."PRODUCTS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."CHANNELS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."PROMO_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."CUSTOMERS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."COUNTRIES_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 12 error(s) at 16:24:05

 

Die Primär Schlüssen Indexes sind durch den EXLUDE Paramater wegen dem "Constraint" nicht beeinflusst. Deswegen muss ebenso jeweils in EXCLUDE und INCLUDE der Schlüssel CONSTRAINT mitangegeben werden:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX,CONSTRAINT remap_tablespace=SH_DATA:SH_IDX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 17:08:50 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

...

 

Folgendes SQL-Statement stellt die Objekttrennung dar:

 

SQL> SELECT segment_type,tablespace_name FROM dba_segments
WHERE owner='SH_V2' AND segment_type IN ('TABLE','INDEX') GROUP BY segment_type,tablespace_name;SEGMENT_TYPE       TABLESPACE_NAME
------------------ ------------------------------
TABLE           SH_DATA
INDEX           SH_IDX

Et voila, das wars! Übrigens mit anderen Data-Pump-Parametern wie zum Beispiel TABLE_EXISTS_ACTION kann man vieles mehr erreichen. Ein Blick in der Oracle Dokumentation „Oracle Database Utilities“ lohnt sich hier sehr.

Bei Anmerkungen oder Fragen freue ich mich auf einen Kommentar.

Viel Spass mit Oracle Data Pump!

Jérôme

During one of my last MySQL mission, I encountered a "security error" with mysqldump. The mission was about migrating a MySQL database from one box to another MySQL version and from Windows to Linux with different users. In order to do that, I used mysqldump. During the import of the data, I got the following warning:

 

ERROR 1449 (HY000) at line 1860: The user specified as a definer ('cdadmin'@'%') does not exist

 

Following the migration, I did the backup strategy. During the first mysql dump, I got the following error message:

 

mysql@mysql001: [mysqld2] mysqldump test
Running mysqldump for instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23, please wait...
MySQL dump of instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23 has failed.
mysqldump: Couldn't execute 'show table status like 'cdr\_app\_browser\_v'': SELECT command denied to user ''@'%' for column 'APPLICATION' in table 'cd_app_browser' (1143)

 

Msqldump exited without finishing to dump the mysql database. Obvisously, both errors had the same root issue.

My initial dump file contained a DEFINER clause and this was the reason of these error messages. As specified in the MySQL documentation, the DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINERcharacteristics. When you dump the data structure for views, triggers, and stored routines, you also dump the permissions related to such objects, with the DEFINER clause.

The MySQL Bug 50594 (http://bugs.mysql.com/bug.php?id=50594) is closely related to this issue. In order to remove the DEFINER clause from the dump file - which can be time consuming - some tools such as MySQL dump filter may help you. For more information regarding this tool, please have a look at this blog posting: http://datacharmer.blogspot.ch/2009/12/filtering-mysqldump-output.html

A couple of days ago, Microsoft published the second Cumulative Update (CU2) for Microsoft SQL Server 2012 SP1. If this CU covers an issue you are experiencing, you should install it quickly. Otherwise, it is not mandatory.

Which issues are covered by this CU2?

This CU2 contains 51 Hotfixes:

• 35 for the Engine
• 6 for SSAS
• 3 for SSIS
• 7 for SSRS

If you need more information and/or details on these hotfixes, click here.

Historic of SQL Server 2012

The build number of this second Cumulative Update is 11.00.3339.

Let's resume the different versions of SQL Server 2012 we have for the moment:

Date SQL Server 2012 version Build

November 2010

Community Technology Preview 1 (CTP1)

11.00.1103

July 2011

Community Technology Preview 3 (CTP3)

11.00.1440 

November 2011

Release Candidate 0 (RC0)

11.00.1750 

December 2011

Release Candidate 1 (RC1)

11.00.1913 

March 2012

RTM

11.00.2100

April 2012

Cumulative Update 1 (CU1)

11.00.2316

June 2012

Cumulative Update 2 (CU2)

11.00.2325

August 2012

Cumulative Update 3 (CU3)

11.00.2332

October 2012

Microsoft Security Bulletin MS12-070

11.00.2376

October 2012

Cumulative Update 4 (CU4)

11.00.2383

November 2012

Service Pack 1 (SP1)

11.00.3000

November 2012

Cumulative Update 1 (CU1)

11.00.3321

January 2012

First Hotfix for SP1

11.00.3335

January 2013

Cumulative Update 2 (CU2)

11.00.3339

Don't forget that a hotfix has been released in the beginning of January! More information on this hotfix are available here.

I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.

Here is my View in SQL Server 2012:

During the import process a warning says  my view doesn't have any primary key (apparently it's mandatory in LightSwitch) and that a key has been inferred...

When I check my view in my LightSwitch data source...

...one of my field(rating_id_session) has been changed to a key which means that I will not be able anymore to view all lines of my views in a grid but just one record per session id, which is just impossible for me...

To avoid this issue, we have to find a workaround which needs two steps:

The first one is to avoid that our field rating_id_session becomes a key during the import. For that we will force SQL Server to mark it as "nullable" by converting it.

If you now try to re-import your view, you will have the following error:

All fields are nullable now, so there is no way to create an inferred key: the view is also simply not imported.

We now have to create a dummy primary key to be able to import our view and to see all records.

The best way to create this primary key - which could be unique for each record - is to use NewID().

I will also use the function ISNULL to add my new field to the inferred key.

With this new key, my view is now imported and I will be able to use it correctly.

To resume

When you want to take out a field from an inferred key, you can use CAST or CONVERT functions.

To add a field to an inferred key, use the ISNULL function with 0 for integer fields and N'' for char fields.

And if you don't have one or more fields to use for keys, you have the possibility to use NEWID() to create one.

Hope this will help you ;-)

In march 2011, my colleague Gregory Steulet published an article about Simulating and testing I/O performances with Orion. In this article, he wrote about a bug concerning Orion on Oracle 11.2, reported in Oracle bug number 9104898: "ORION FAILS WITH ORA-27061: WAITING FOR ASYNC I/OS FAILED".

 

Trying to compare performances of storage devices on a virtual machine between a classical and a paravirtual storage adapter, I experienced the same error when using large IOs. I was using a Red Hat 5 based environment, which seems to be especially concerned by this problem: 

 

Error completing
IO(storax_aiowait)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 14:
Bad addressAdditional information: -1
Additional information: 1048576
Test aborted due to errors.

 

If you have already faced the problem too, there is good news! Oracle finally published a patch in November 2012 to fix this problem, reported in January ... 2009!!! The patch is available for Oracle RDBMS 11.2.0.2 and 11.2.0.3.  You can download it at My Oracle Support website, searching for Patch 9104898 or Patch ID 15597001.

 

It works fine for me now.

 

Don't forget to download the last version of OPatch (6880880 in My Oracle Support) before applying this patch, as recommanded by Oracle.