Quick Tips for Oracle DBAs

PROBLEM #1 : Need to set the environment each time you login to SQLPLUS?

SOLUTION: Edit login.sql or gloging.sql and enter the setting you need.

This will be used each time you login. 

 vi  $ORACLE_HOME/sqlplus/admin/glogin.sql

SET FEEDBACK OFF

SET TERMOUT OFF

COLUMN X NEW_VALUE Y

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " or  SELECT LOWER(USER || '@' || SYS_CONTEXT('userenv', 'instance_name')) X FROM dual;

SET SQLPROMPT '&Y> '

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

SET TERMOUT ON

SET FEEDBACK ON

SET LINESIZE 100

SET TAB OFF

SET TRIM ON

SET TRIMSPOOL ON

PROBLEM #2: Finding the folder sizes in Linux/Solaris

SOLUTION:du -h -s *

2.2G   AUDRPRD

3.6M   AUDRPROD

  11M   Backup

  80G   E1PROD

120K   E1PROD_RESTORE

   0K   lost+found

232M   SOMEPROD

PROBLEM #3: Unix command out is in bytes and it is hard to covert in readable format, try using h as an attribute.

SOLUTION: ls –alt OR ls –alth

ls –alt

drwxr-xr-x   2 oracle   dba        22016 Jul  7 15:27 SOMEPROD

drwxr-xr-x   2 oracle   dba        18944 Jul  7 15:17 AUDRPROD

ls –alth

drwxr-xr-x   2 oracle   dba        22kb Jul  7 15:27 SOMEPROD

Satheesh Shanmugan, Database Administrator
Myriad IT

Filtering on SQL Server Reporting Services datasets

I’ve been building reports in SSRS for about five years and almost always do all filtering in the SQL WHERE clause.  Over the last week I’ve been refining a report and needed an additional filter that would have been challenging within the query and in the process of investigating an alternative approach I came across dataset filters in SSRS.

As mentioned, I normally do all filtering in the SQL WHERE clause.  My rationale is that it reduces the size of the amount of data returned and is therefore more efficient and quicker.  The report I am currently working on returns information about millions of invoice rows from four tables all via LEFT OUTER JOINS (to ensure I capture all invoices).  One of the required filters is on a field not in the left outer most table and needs logic to handle the prospective nulls returned.

Rather than rewriting the query to handle this, and risking the integrity of the dataset, I found Dataset filters in the SSRS report itself.  By right clicking on the dataset and selecting Properties there is a Filter tab:

 

This offers a very flexible way of controlling the returned data much like a SQL where clause.  Some advantages are:

·         It maintains the query’s integrity.  Sometimes I build a query and verify the data with the business.  Then I am then asked to add additional constraints and these additional WHERE clause conditions then break the integrity of the data returned.  Since the filter doesn't alter the underlying query, the integrity should remain in tact

·         The filter is extremely flexible.  Like a WHERE clause it can operate using a report parameter with all the usual SQL operators

·         SSRS expressions can offer additional flexibility similar to a HAVING clause and nested clauses

·         An expression returning a boolean TRUE / FALSE offers similar functionality to an EXISTS (and NOT EXISTS) clause

 

·         It can be implemented very quickly in comparison to rewriting underlying queries and then revalidating data.

I don’t think I’ll stop filtering within the SQL WHERE clause where I can, but it is a great feature and certainly offers flexibility in filtering data in a SSRS report.

 

Struan Hijner – Infrastructure Services Practice Manager
www.myriad-it.com

Use Rights by Microsoft Dynamics CRM 2013 CAL

Deciding which Microsoft CRM license you require based on features?  The following table lists the use rights corresponding to the Client Access Licenses (CALs) that are available in Microsoft Dynamics CRM 2013 and User Subscription Licenses (USLs) available in Microsoft Dynamics CRM Online.

In the  Microsoft Dynamics CRM Online Licensing and Pricing Guide found here.

Regards,

Struan Hijner, Infrastructure Services Practice Manager

Myriad IT

03-8530-8600

CRM Case History not updating accurately in JD Edwards EnterpriseOne 9.0

In JD Edwards EnterpriseOne applications release 9.0 (without ESU JL61235 applied), CRM case history is not time stamped correctly.  The manifestation of the problem is that case history is incorrect and does not accurately reflect when cases were updated, which can be very important if you are tracking cases to SLAs.

To understand the issue we need to understand how information is held in the F1755.   The primary keys on the table are:

• ZASTAW (status) – is set to 2 for the active record and 1 for historical record
• ZAUPMJ (Date – Updated)
• ZAUPMT (Time – Updated)
• ZADOCO (Document Number)

Data for a particular case is held as follows:

•  When a CRM case is created, a new record is created in the F1755 table with a new document number assigned (ZADOCO), date and time stamped with current time (ZAUPMJ and ZAUPMT) and status (ZASTAW) is set to 2. 

• When the record is updated, the existing record should just be changed to STAW = 1 and write the new line created with ZASTAW = 2 an updated current time in ZAUPMJ and ZAUPMT. 

Simple really, but this is not the case without ESU JL61235 applied.  Prior to this ESU the record updates incorrectly by setting ZASTAW to 1 and the ZAUPMJ / and ZAUPMT to current date / time.  Therefore it looks like the historical record was written today and tracking cases and status changes (ZACLST) is meaningless to the user.

ESU JL61235 fixes the problem with new P90CG50x applications and associated business functions.  There are no Special Instructions listed for the ESU.  When I applied the ESU and tested the functionality it worked perfectly for new cases.  However when updating existing cases I received an error:

CAUSE: ERROR: File can not be accessed.

Solution. . . Change IBM security or authority using the IBM EDTOBJAUT command.

This file can not be accessed with the current IBM authority

for the User executing this request.

This occurs because there are (old) invalid records which cause a primary key violation when updating the case.

For example, if I create a case on January 1 and update it on January 2.  Without the ESU it time stamps the original and new record as January 2 (and identical times) and therefore the active and original records’ primary keys are identical with the exception of ZASTAW for all existing cases in the database with at least one historical record.  When the case is updated (correctly with the applied ESU) JDE tries to change the current record to a historical record (setting ZASTAW to 1 and not re-stamping dates and times) and hence the violation.

To work around this issue I need to adjust the records so that ZASTAW is not the only difference.  But I have no way of knowing what the existing historical record (ZASTAW = 1) should be therefore as a work around I subtract one second from the records (assuming it is not midnight) using the following SQL:

update F1755 o

set o.ZAUPMT = (o.ZAUPMT - 1)

where o.ZASTAW = '1' and exists (select *

from F1755 i

where i.ZADOCO = o.ZADOCO and i.ZAUPMJ = o.ZAUPMJ and i.ZAUPMT = o.ZAUPMT and i.ZASTAW = '2') and o.ZAUPMT != 0

If you are doing this, you probably want to check for records that are at midnight (ZAUPMT = 0) and add a few seconds to the active record rather than worrying about a changing the date as the information is wrong anyway.

Tip: F1755 records are time stamped adjusted for your time zone.  So even if you don’t work 24 hour shifts, the ZAUPMT could be 0 depending on your time zone.

Struan Hijner, Infrastructure Services Practice Lead

Myriad IT

03-8530-8600

Taking the leap into managed services

Oracle Vice President, Mark Hurd recently posted an article on LinkedIn entitled Five Reasons Why CEOs Should Love the Cloud. 

He writes that cloud computing can help CEO's transform their businesses by enabling them to devote more of their IT budgets towards growth opportunities rather than the "maddeningly complex and expensive in-house IT environments that can't keep up with the modern business world".


The reasons Mr Hurd gives are compelling:

1. Simplify IT
2. Re-engineer the economics of IT spending
3. Accelerate and optimize your business processes.
4. Drive innovation
5. Enjoy world class security and compliance.


So what happens once you've decided to take the leap? How do you choose a managed services provider? 

It's an important but daunting task. In this blog post, I'll set out some factors to take into account when choosing a managed IT service provider.

Define your scope -   In order to maximise the benefits of managed services, it is important to clearly define the scope of your requirements. Being clear about what is required minimises costs and prevents problems arising later.

Flexibility - Above all, you need a provider that can respond to your changing needs. Chances are your organisation won't be the same 5 years from now.

Expertise - Do you trust that your provider has the necessary expertise? A good provider should communicate clearly and make it easy for you to understand the service being provided.

Reliability - A good managed services provider will work actively with your IT to minimise disruption to your business during the transition. Clear roles should be established and your service provider should implement and comply with service-level agreements (SLA's) tailored to meet your organisation's needs.

Quality & Support - Does the service provider use a well established data centre? Are you able to visit it to see for yourself? Does the provider offer a premium service and proactive support?

Customised - Once you have established the above, you should understand whether the provider can customise its service to meet your organisation's specific needs. The flexibility to customise means you only pay for what you need.

Location - For many companies the physical location of data is important for compliance reasons so it is important to understand where will the data be physically stored (e.g. within Australia vs outside of Australia).

Key takeaway:

There are many business benefits and savings that can be generated by managed services. The most successful models involve open communication between the company and a trusted provider to ensure the services provide not only meet your business needs but can also evolve as your business does.

Myriad IT
03-8530-8600

Backing up & restoring SharePoint

Last week we had the unenviable task of restoring a production SharePoint due to the corruption of a number of libraries.  We didn’t want to restore the entire site as the restore point was 17 hours from the previous backup.  The decision was made to restore to an alternate location, compare the files and manually copy the required files across.

The first point to note is that this is much easier if you have a full application level backup of SharePoint – possible through backup utilities like Microsoft DPM (Data Protection Manager) or Dell’s AppAssure and herein lies the real moral of the story.  However, we only had a backup of a full server image and a backup of the SharePoint database.

Restoring the site from this point, back in to the same SharePoint Farm for comparison, had its challenges and we found no reliable, single point of reference on the web to give us guidance.  We’ve documented the process for reference and we will certainly be backing up the SharePoint application from now on.

Restore SharePoint content into the same SharePoint Farm

The only way to do this is to use export import using one of the following SharePoint commands:

Backup-SPSite -Identity SiteCollectionURLHere -Path BackupFilePathHere [-Force] [-NoSiteLock] [-UseSqlSnapshot] [-Verbose]

Restore-SPSite -Identity SiteCollectionURLHere -Path BackupFilePathHere [-DatabaseServer DatabaseServerNameHere] [-DatabaseName ContentDatabaseNameHere] [-HostHeader HostHeaderHere] [-Force] [-GradualDelete] [-Verbose]

Or:

stsadm -o backup "webappurl" -filename "yoursitecolleciton.bak"

stsadm -o backup "restore" -filename "yoursitecolleciton.bak"

The above commands are native SharePoint command for backup and restore. They will work well provided the original site content that you want to backup is still good (so you can take the SharePoint backup from this site and restore it to the other site in the same farms).

If the original SharePoint content has broken and you need to restore content from backup without deleting the old content then it is going to be a challenge as you can’t restore SharePoint content into the same farm. This is because one site only can have one application GUID, so when you import the database it will always shows that you have 0 sites.

There is a work around to this:

Step I

Create New Web App:

1. Go to SharePoint Central Administration -> Application Management -> Manage Web Application -> Click the ‘New’ icon in the top left.

2. Fill out all the required details, make sure it is running port 80, specify the new database name, it will create the new one (e.g.: WSS_Content_Restore)

Step 2

Attach the new database, the database must be attached in the same instance where the SharePoint is installed

1. In the SQL server management studio, right click databases and choose attach

2. Click add and point it to the restored database (.MDF) and log (.LDF) files

3. Make sure that we change attach as the new name  (e.g.: WSS_Content_Restore_1)

Step 3

Attach the database to the SharePoint:

1. Open SharePoint 2010 Management Shell as administrator

2. Execute this command: Mount-SPContentDatabase “WSS_Content_name” –WebApplication restoreurl -AssignNewDatabaseID
e.g: Mount-SPContentDatabase "WSS_Content_MynetRestore" -WebApplication http://mynetrestore.myriad-it.com/ -Assignnewdatabaseid

(Note that the CurrentSiteCount is 0 although we have 5 sites in that database) – Database attach from the web interface will not work because one SharePoint farm can only have one database ID so we need to assign the new database ID to the imported database.


3. Go to Central Administration- > Application Management -> Select the correct Web Application -> Manage Content Database and remove all the original database, make it the restore database as the only database.

a. Change the Database status to Offline

b. Tick remove content database

c. Click OK

4. Make sure only one database listed

Notice that it only has 0 number of site collection

Step 4

Create the new site in the new WebApp, the site name must be the same with the original SharePoint.

1. Go to SharePoint Central Administration -> Application Management -> Create Site Collection -> Change the web application that we created on the step 1

 2. Title and Description can be anything, url for the new sites must be the same with the original site, if the original site is /sites/clients then we also have to create /sites/clients

3. Template must match the original template, for this case is Team site.

4. Put yourself as primary site collection Administrator

Step 5

Match the application GUID with the original site.

When you create the new site it will create the new application ID, you need to change this to match the original site application ID. This is the tricky part.

1. Go to the database server and open SQL Management Studio

2. Execute the following query:

use WSS_Content_MynetRestore_AV

select ID, TimeCreated from dbo.Sites

3. Take note of the new site ID, when we look at the date, it should be the recent time and date as we just created it:

4. Execute the following query to see the ID of the old site

use sp2010_Config

select ID, Path from dbo.SiteMap where path = '/sites/clients'

5. Take note of the original site application ID:

(if there is two ID like above, need to find the correct clients ID which one is belong to the original ID), in this example the correct site ID is: 6E76FF13-6C70-4BFE-A112-74D29C553EC9

6. Go to Sharepoint_Config, expand and go to -> Tables -> dbo.SiteMap, right click and choose Edit Top 200 Rows

7. Find the original site ID and replace the most end character with any character, and copy the original application ID to the new site. The reason we do this because one sharepoint farm only can have one application ID.


8. Do the IISreset

9. After iisreset, the original site will be down showing 400 Bad Request, to make the original site up need to put back the correct value to the original site

10. Change the last character of ID on the new site’s application ID to anything, but must remember the original value.

11. Go to the new site URL,  if the restore is successful, it should represent the same layout with the original site

There was some error showing on the page, that because of publictokenkey that we need to save it on the web.config of the sharepoint file, should ignore the error if just need to get the file, if need to get the site running must put this line in the web.config located in IIS virtual directory, e.g. C:\inetpub\wwwroot\wss\VirtualDirectories\80:

<SafeControls>
<SafeControl Assembly=”DateTimeWebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=2bfd9e5fd3b67b15″ Namespace=”DateTimeWebPart.DateTimeWebPart” TypeName=”*” Safe=”True” />
</SafeControls>

12. Then map either map or explore the sharepoint site using explorer, we should be able to see all the files:

Note: There is a grace period between wrong ApplicationID that being mapped to the sharepoint site, on the next database refresh one of the site will fail so need to re-do step 10 and 11 to make sure both sites are working.

Key takeaway:

There are a number of challenges in restoring SharePoint that the above steps can help address. The most important thing to remember is that this would have been much easier with a full application level backup of SharePoint – possible through backup utilities like Microsoft DPM (Data Protection Manager) or Dell’s AppAssure.

- Nicolas Prasetyo, Systems Engineer

End of Windows 2003 support – opportunity or threat?

April 8 2014 was a well published milestone with Microsoft no longer supporting Windows XP.  The next big date is July 14 2015 when Windows 2003 support will reach end of life and I think this is a much more daunting proposition. 

At a recent vendor conference, someone threw out a number 10-12 million servers worldwide still running production applications on Windows 2003. It’s unclear if this estimate is accurate but I imagine there are a lot of Windows 2003 servers out there still running production applications. 

Whilst the number of servers out there is much smaller than the number of PCs, the task of migrating is much more complicated and the risks greater.  Most Windows 2003 servers shipped between 2002 and 2007 and given normal server lifecycles and corporate asset replacement cycles many will already have been retired.  However, I still see Windows 2003 servers at sites and usually this is due to the legacy applications that are not supported on newer versions of Windows.

We all know if it was easy to replace an application or the ROI to do so is compelling it probably would have been done already.  Establishing business cases, finding suitable replacement applications and testing new versions all take time and I sense that, even though the date is over 14 months away, the urgency is building. 

It doesn’t need to be all doom and gloom though.  This presents a great opportunity to look for modern applications delivered in alternative ways.  Not only is it an opportunity to move applications into the cloud but also to consume them differently. 

The subscription models now available also change the financial impetus as the monthly expense is smaller and more immediate.  Paying for applications as they are consumed removes the requirement for developing business cases for capital expenditure, saving time and effort.

Instead of thinking about the task of replacing a server and an application I think this is a great opportunity to deploy modern applications, without any infrastructure requirement, and to move away from traditional “shift and lift” upgrades permanently.