A new trend in IT Shared Services Organizations

A client of ours recently asked this question: “Are you aware of the trend of companies establishing shared IT services organizations with other companies – not cloud or outsourcing with managed providers but two companies deciding to share data centers or certain applications as an example.“  So, we asked our IT manager to look more into it and provide some more information.  We would like to share that with our readers:

One thing to understand is that IT Shared Services Organization (or IT-SSO as it is called sometimes) could be an external organization that services the needs of multiple organizations OR two (or more) companies can decide to form a cost center by combining their IT organizations…these companies can also decide to have separate IT organizations but they share the load, act as each other’s backup or collaborate on projects and establish DR/HA practices.  The first one where a company does the managed services for several companies has been around for years (example: IBM managed services in their data centers), what he was interested in here is the second trend where a couple of companies decide to form a cost center by forming an IT Shared Services Organization.

Before we dive into the details of it, first thing to understand is why is there even a need for IT-SSO for the second trend that we are seeing in the industry.  Why would any corporation even consider looking into it?

In order to understand the need, consider this scenario – In a city, how many companies and within those companies, how many departments have their own dedicated network and cabling, each with their own:

-              Internal Data Centers or Server Rooms,
-              Desktops
-              Telecommunications
-              Infrastructure security layers
-              Network management
-              Help Desk management
-              Application Hosting
-              Content Management
-              Hardware infrastructure,
-              Their own patch management standards
-              Same kind of software and hardware licenses
-              IT Staff
-              Energy consumption
-              Under utilized hardware and storage resources

About 15-20% of the costs of any SMB organization goes towards IT.  For Tier-1 Organizations, this number is even higher.  Many of these organizations do not go with the managed services from large players like IBM etc. because of cost and more importantly because they want their own teams doing this work to do proper justice to the business and the turn-around times.  Having a team of employees v/s an outside company doing it for you has differences in terms of turnaround time on projects, accountability and protecting your IP for any of the critical business applications.

If many of these companies start using an IT-SSO model, each of them would stand to gain from it.  Here are some of the benefits:

-                      Single Service Organization with better manpower utilization
-                      Cost benefits in terms of resources, hardware and software licenses
-                      Common Patch management and standards
-                      Companies can focus on their core business rather than IT infrastructure
-                      Shared Storage
-                      Cost savings in terms of energy consumption
-                      Cost savings in terms of contracts with external vendors & partners
-                      Uniform practices and standards – for different companies, these might be at varying degrees of maturity depending upon their adoption.
-                      Better scalability because of common pool of resources.
-                      Better insight into & adoption of new technologies so strategic advise and guidance can be obtained.
-                      Organizations can save huge costs on their Production and DR sites as they have their DR at their partner’s primary site and vice versa.
-                      Leveraged resources. With private cloud, the entire infrastructure can be considered as one pool and can be used by other organization during their spike traffic if the other organization doesn’t have much traffic during the same time.
-                      Leveraged knowledge – Both organizations can share their expertise on managing their infrastructure.
-                      Energy cost savings and better scalability due to consolidation – one can make use of VMWare’s VI3 infrastructure and blade servers

So, having said that, what kind of infrastructure and applications are we really talking about over here and do some companies fit this model better than others?  Ideally speaking, every company can fit this model – one main concern could be around security access to the data, their IP and compliance.  Before we delve into that, here are some of the applications and IT infrastructure needs that I think stand to gain from IT-SSO:

-                      Internal Desktop and Server IT infrastructure and support
-                      Internal Applications like:

• Accounting packages (Great Plains, SAP Business One etc.),
• HR software applications,
• MS Office (either through virtual desktop infrastructure or Online),
• Content management systems like MS Sharepoint
• Development and QA environments and software like VS2008, QC, Load Runner etc.
• Source code control systems like Sourcesafe, Subversion, TFS etc.
• Database Servers – Oracle, SQL Server, DB2 UDB etc.

-                      Phone Systems
-                      Major software systems like PeopleSoft, SAP, Oracle where a lot of differences between 2 organizations is merely in configuration.  The hardware and software requirements are nearly similar.
-                      Operating Systems & their patch management
-                      IM tools like internal facebook/twitter/yammer or MS Communicator
-                      Networking Systems

And what are the hurdles to this approach?  Before jumping on the bandwagon, it is important for the companies to understand how a shared services model fits successfully into the overall business strategy of the firm.  We think that the major changes involved when transitioning to shared services are process and communication related. Aligning the team members and gaining their commitment are necessary for success.  In addition, one clearly needs to understand their security and compliance needs and proper SLAs need to be in place.

So, here are some of the cons or rather hurdles to this approach:

-                      Requires proper buy in from the CxO levels as well as the IT & Development teams.  Easier said than done.  Even after the buy in, proper governance needs to be defined (more on this below).
-                      Defining SLAs would be a bit complex – the more the number of companies involved, the more complex it becomes.
-                      Needs a very strong project management regime/discipline to drive the collaboration activities.  This collaboration must generate business value and each and every step needs to be measurable.  Parties need to agree on the common goals and maintain a clear mode of communication involving the IT-SSO.
-                      Compliance issues – Both organizations need to make sure their information is secure and managed according to compliance regulations – depending upon the different business verticals, this can be a major point so it might make more sense for corporations in the same business domain to do this.  Example: A heathcare software company co-sharing the data center with say an online accounting software company would have totally different compliance requirements.
-                      Companies would have concern about their IP rights which can become a serious issue if the companies are in the same business space.

So, why now?  Because the advancements in technology (VMWare, Blade Servers, Geo Clusters) makes it easier to implement such solutions.  Not only that, the companies are realizing that benefits in terms of cost and time are to be gained if they consolidate and have a shared IT services organization with other companies.

The first movers that we have seen or read about who are adopting this are either educational institutes, universities, small practices/hospitals and Federal Agencies.  As far as private companies go, we think that those that are either funded by the same venture group or those who have some of the same folks on their board, will be the next to move towards this.  Here is an article on computerworld that you would find interesting: http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9029160

We think that if proper governance is there, then moving towards IT-SSO is an achievable goal which has several benefits.  What do we mean by that?  By governance, in this case I mean that three questions need to be answered properly – these three questions were cited by Accenture in their study (Driving High Performance in Government: Maximizing the value of Public-Sector Shared Services, Accenture, Feb. 2005.) and also by Nancy Desormeau:

• Who decides what?  ==>  The decision-making roles and authorities for strategic direction and for ongoing operations.
• Who does what?  ==> The roles and responsibilities for achieving results, the form and composition of the decision-making bodies.
• Who answers for results? ==>  Accountability and recourse.

One needs to define the governance accountabilities at three tiers: Executive, Strategic Partnerships and Operational.  So, this can be done and is being done by different organizations in order to save on costs and also provide a more efficient and scalable solution to their organizations.  Whether it is a right solution for your organization, only you can tell.

Resources:

• ComputerWorld article referenced above.
• Accenture Study referenced above.
• Presentation by Nancy Desormeau – here.

Companies establishing shared IT services organizations with other companies – not cloud or outsourcing but two companies deciding to share data centers or certain applications as an example.

Happy 4th of July

We wish all of our readers in the USA a very Happy 4th of July.  Happy Independence Day.

map : Permission Denied after client upgrade to 10.2.0.4

Recently at one of our client site we ran into issue after we upgraded oracle client from 10.2.0.2 to 10.2.0.4.  After update to 10.2.0.4 when we invoked SQL*Plus connection, we ran into following error. Even after displaying the error, it still connects to SQL*Plus. This error occurs only when osuser is not part of “dba” group on unix box.

sqlplus inter/inter@orcl
1634460 : map : Permission Denied
1634460 : map : Permission Denied
1634460 : map : Permission Denied

SQL>

Clearly this is a permission issue, even after running all the required scripts after upgrade was done.  After checking with Oracle, they suggested to run changeperm.sh script from install directory  under $ORACLE_HOME (Unix environment variable where oracle software is installed). This script relaxes the permission on the directories.  Here is the script followed by its output.

sh $ORACLE_HOME/install/changePerm.sh

——————————————————————————-
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation.  Running this script
should be done only after considering all security ramifications.
——————————————————————————-

-n Do you wish to continue (y/n) [n]:
y
Spooling the error log /tmp/changePerm_err.log…
Finished running the script successfully

After running the script, we tried to connect using SQL*Plus and it worked fine.

sqlplus inter/inter@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 11 22:10:20 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

SQL>

You may or may not run into this error, but it is always good to know in case you run into such scenario, there is a solution available.

Resources:

• Orafaq – here.
• Database-Diva Blog – here.

Changing the database to be Read/Write

A few days back, we had written a post on how to go about changing a database to be in a read only mode and the scenarios when you would do that.  You can read more on that here.  A reader asked how to change it back to be a read/write DB.  It’s pretty simple and you can use the same set of commands as we had shown in that post – just change the READ_ONLY to be READ_WRITE.  Here is an example:

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Mark the database as Read Write*/
ALTER DATABASE DECIPHERTEST SET READ_WRITE
GO
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
GO

Exporting Windows Event Viewer data for compliance

The companies that are subjected to regulatory compliance are often required to store and archive the logs from various part of their infrastructure such as applications, firewalls, VPN and servers. Most of the network devices support Syslog and if you have any syslog server in your environment you should be able to view, collect and archive the syslog data. Kiwi Syslog server is one of the best tools available in the market.

Windows servers do not have a syslog client by default and usually all the system related warnings, alerts and information are stored and displayed in the Windows Event Viewer. Event viewer allows exporting of data locally in different formats for review. However, in an enterprise environment, there is no tool exists to automate the collection of event viewer from a centralized location.

One great solution for this is using software called ‘winlogd’. Winlogd converts the windows event viewer logs into syslog and send it to the syslog server. Winlogd installs itself as a windows service and requires a registry edit to specify the syslog server IP.
It can be easily pushed to all the servers in an enterprise environment using a .reg file.
Once the syslog server can receive the data from servers, it can be viewed and archived for compliance purposes.

One limitation of Winlogd is it doesn’t allow filtering the window event viewer logs. So, all the data that is going to Windows Event Viewer (including ‘information’) will be sent to syslog server. If you have many chatty servers that would cause lot of informational event logs, it may generate tons of syslog data and network traffic. I’m hoping that winlogd community will fix this in their next release. Nevertheless winlogd is a great tool!

More information on ‘winlogd’ can be found here:  http://edoceo.com/creo/winlogd

Guide to SQL Server Consolidation

One of the most common questions in our consulting engagements surround consolidation of the SQL Server environments.  There are a couple of ways to go about it and it varies client to client depending upon the type of applications that the SQL Server is being used for, versions & their OS’s, existing and proposed hardware infrastructure, the proper IT team in place to support it etc.

Here is an excellent whitepaper from Microsoft MVP’s that you can download in order to learn more about the different consolidation techniques – here.

Combining multiple trace files into single file using trcsesse

We are all aware that tkprof and trace facility are two most basic and important aspects of performance tuning in oracle database. We can generate trace for the entire database or for a specific session. We have covered about them in our previous blog post – here. We have also covered event level tracing here.

Problem occurs when tracing spans across multiple trace files. How we can collect data from multiple trace files? Oracle provides the utility called ‘trcsess’, which can combine multiple trace files into single trace file. Once all the files are combined into one file, we can run tkprof or trace analyzer on the file to collect more meaningful data. Basic syntax for trcsess utility is as follows.

Trcsess utility provides different criteria on which we can combine information from all the trace files into single trace file. Following is the basic syntax for trcsess utility.

trcsess [output=output_file_name]

[session=session_id]

[clientid=client_id]

[service=service_name]

[action=action_name]

[module=module_name]

[trace_files]

Output specifies the output file name and rest of them are criteria on which one can combine the different trace files into single trace file. We can combine multiple trace files based on either session or client id or service (instance name) or specific action or module name. All these information will be available in trace file itself. After that we have to specify all the trace files we would like to combine. So here is the example of it.

trcsess output=ora_jun23_lvl12.trc service=ORCL orcl_ora_1024490_TEST.trc orcl_ora_1024492_TEST.trc

In our example, we are combining two trace files based on the service and combined output is created in ora_jun23_lvl12.trc file. As mentioned earlier, we can run tkprof output on this single file to get all the information for the traced session.

Resources:

• Oracle 10g Performance Tuning guide – here

Mutating table/trigger error and how to resolve it

Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other during the development process.  In this blog post, we will cover why this error occurs and how we can resolve it using different methodology.

Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error. Let us take a simple scenario in which we have to know total number of invalid objects after any object status is updated to ‘INVALID’. We will see it with an example. First let us create a table and then trigger.

SQL> CREATE TABLE TEST
2  AS SELECT * FROM USER_OBJECTS;

Table created.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(’Total Invalid Objects are ‘ || v_count);
END;
/

Now if we try to change the status of any object to ‘INVALID’, we will run into mutating error as we are trying to update the record and trigger is trying to select total number of records in ‘INVALID’ status from the same table.

SQL> update test
2  set status = 'INVALID'
3  where object_name = 'TEST1';
update test
*
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it

Having said that there are different ways we can handle mutating table errors. Let us start taking one by one scenario.

First one is to create statement level trigger instead of row level. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
DECLARE
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(’Total Invalid Objects are ‘ || v_count);
END;

Now let us fire the same update statement again.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 6

1 row updated.

When we defined statement level trigger, update went through fine and it displayed the total number of invalid objects.

Why this is a problem when we are using ‘FOR EACH ROW’ clause? As per Oracle documentation, the session, which issues a triggering statement on the table, cannot query the same table so that trigger cannot see inconsistent data. This restriction applies to all the row level triggers and hence we run into mutating table error.

Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(’Total Invalid Objects are ‘ || v_count);
END;

Now let is issue the update statement again and observe the results.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 5

1 row updated.

If you closely look at the output, you will see only 5 objects shown in invalid status while statement level trigger showed 6 objects in invalid status. Let us try to update multiple objects at the same time.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name IN ('T1','T2');

Total Invalid Objects are 6
Total Invalid Objects are 6

2 rows updated.

By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.

In version 11g, Oracle made it much easier with introduction of compound triggers. We have covered compound triggers in a previous blog post. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:

CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR UPDATE
ON TEST
COMPOUND TRIGGER

/* Declaration Section*/
v_count NUMBER;

AFTER EACH ROW IS
BEGIN

dbms_output.put_line(’Update is done’);

END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(’Total Invalid Objects are ‘ || v_count);

END AFTER STATEMENT;

END TEST_TRIG_COMPOUND;
/

Now let us check how many objects are invalid in the test table.

SQL> select count(*) from test where status = 'INVALID';

COUNT(*)
———-
6

Here is the update statement followed by an output.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'T2';

Update is done
Total Invalid Objects are 7

1 row updated.

Here we get correct result without getting mutating table error. This is also one very good advantage of compound triggers. There are other ways also to resolve mutating table error using temporary tables but we have discussed common ones in this blog post.

Resources:

• Tom Kyte’s article: http://asktom.oracle.com/tkyte/Mutate/index.html
• Don Burleson’s article: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
• Oracle 11g PL/SQL language reference: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm

Google Fusion Tables

Google introduced their Fusion Tables approach towards data management in the cloud.  You can read more on this from their Research Blog here:

http://googleresearch.blogspot.com/2009/06/google-fusion-tables.html

And here is the infoworld article on the same topic:

http://www.infoworld.com/d/data-management/google-tests-revolutionary-cloud-based-database-290?source=rss_infoworld_news

Making a database Read Only

We were in the process of migrating the data from a legacy system to a newly architected system for a client of ours.  For the duration of the migration, they needed the data set to be available for reporting purposes but of course no data should be created in that legacy system during the time of the migration.  So, one of the obvious choices were to make the database as a read only database.  In SQL Server, there is an option in the “ALTER DATABASE” command to achieve that.  In order to do so, one first has to mark the database in a single user mode first, then make the change to mark the database as a read only database and then change it back to the multi-user mode.

Here is a sample script:

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Mark the database as Read Only*/
ALTER DATABASE DECIPHERTEST SET READ_ONLY
GO
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
GO

References:

• BOL entry for the ALTER DATABASE command – here.