C. Peter Chen – Page 6

May 11, 2009

Crystal Reports Server XI backup and recovery step-by-step

I am running Crystal Reports Server XI with a SQL Server 2000 database. These two components are running on two different servers, but it should not change the procedures below much.

Procedures for Scheduled Daily Backups

On the database server, I set up a batch file that utilizes SQL Server 2000’s osql command to perform backup of the Central Management Server (CMS) database; I scheduled it to run nightly. The content of the batch file is as follows. Note that you should substitute in your server name, user name, password, and file paths. Also, note the copy command makes a copy of the backup file to another server, which is highly recommended; how good is a backup if the system failure is caused by a hard disk burning out and the backup resides on the same hard disk, right?

osql -S [serverHostName] -U [databaseUsername] -P [myPassword] -Q "begin declare @CMSBackupFile varchar(255) set @CMSBackupFile='C:cms.bak' backup database [cmsDatabaseName] to disk = @CMSBackupFile end"
xcopy.exe /c /d /e /h /r /y C:cms.bak \anotherServerbackupscms

On the Crystal Reports Server XI server, there is a folder that stores the actual reports; Crystal Reports Server XI refers to this folder as the Input File Repository. By default, it is located at C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput, which is what the example below will make use. The example is another batch file, scheduled to run nightly, that makes a backup copy of the reports to another server.

xcopy.exe /c /d /e /h /r /y "C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput*.*" \anotherServerbackupsfileStore

Restore Procedures

My plan to deal with the unthinkable is listed below.

For the SQL Server, if needed, install SQL Server and then copy the backup file (“cms.bak” in this example) to the new database server. Next, issue the following two osql commands in the command prompt to create/recreate the CMS database.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "drop database [cmsDatabaseName]"
osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "restore database [cmsDatabaseName] from disk='c:cms.bak'"

Next, we will likely find an orphaned user in the restored database; this can be seen in SQL Server Enterprise Manager’s “Users” section for the CMS database; you will see an user without “Login Name”. To fix this, run the following osql command in the command prompt.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -d [cmsDatabaseName] -Q "exec sp_change_users_login 'Auto_Fix', '[crystalReportsDatabaseUser]', NULL, '[passwordHere]'"

For the Crystal Reports Server XI reports, if needed, they can be copied directly from your backup to the new Input File Repository location.

When you have completed the restore, you may wish to restart the CMS and Input File Repository services in the Central Configuration Manager.

April 30, 2009

Fixing orphaned users after attaching or restoring a SQL Server database

I ran into this issue when I took a backup set from a production database and restored it into a machine in my development environment. I noticed that the recently restored database has an user named “bob”, but when looking at bob’s entry in the newly restored database, the Login Name field is blank. To fix this, I made use of SQL Server’s built-in stored procedure sp_change_users_login. The syntax is below:

Syntax:

sp_change_users_login 'action'
	[, 'user']
	[, 'login']
	[, 'password']

Valid values for 'action':

Report
Update_One
Auto_Fix

The “Report” action displays a report of all possible orphans in the current database and requires no further parameters. Sample usage:

exec sp_change_users_login 'Report'

The “Update_One” action links a specified orphaned user in the current database to an existing SQL Server login. With this action, the “user” and “login” parameters must be provided, and the “password” parameter must be NULL or not provided. Sample usage:

exec sp_change_users_login 'Update_One', 'bob', 'bob'

Finally, the method I chose to perform to fix my issue was the “Auto-Fix” action, which saves me the step of having to manually create the “bob” SQL Server login first because it will automatically create the login if it does not already exist. The “Auto_Fix” action requires the “user” and “password” parameters, and the “login” parameter must be NULL. Sample usage:

exec sp_change_users_login 'Auto_Fix', 'bob', NULL, 'password123'

March 27, 2009

Changing Oracle 11g password sensitivity

When I was testing for the planned Oracle 11g upgrade, I was fairly excited about the possibility of being able to add an additional dimension to password complexity by introducing case sensitivity. Unfortunately, after some investigation, I found several legacy applications that were still in use in my environment that either had many hard-coded all upper-case passwords or have program functions that changed all casing to upper case. While introducing case sensitive passwords was definitely a goal for my environment, I also understood that the developers responsible for the applications noted above would not have enough time to make the changes to meet my database upgrade schedule. My solution was to temporarily disable the case sensitivity feature in Oracle 11g, thus I could still proceed with my upgrade, while providing the developers an extra month or two to make modifications to their code to comply with the new security model.

To disable password case sensitivity in Oracle 11g:

alter system set sec_case_sensitive_logon=false;

Again, you should carefully evaluate your situation before disabling this new feature found in Oracle 11g. Password sensitivity may be a great tool in safeguarding your data from the unauthorized.

March 20, 2009

Adding or subtracting months or years for Oracle dates

I ran into the need to do this because one of my users performed a big data import, and it was not until he finished that he realized somewhere along the way when he was preparing the data, instead of “2009”, some of the years came out to be “1909”. To fix this in the database, I made use of Oracle’s built-in numtoyminterval() function, which stands for “Number to Year/Month Interval”. The syntax is as follows:

numtoyminterval(n, interval_name)

“n” is the quantity, and “interval_name” is either “year” or “month”. The following example illustrates its basic usage.

select sysdate as now,
sysdate + numtoyminterval(1,'month') as plus_1_month,
sysdate + numtoyminterval(3,'month') as plus_3_months,
sysdate + numtoyminterval(12,'month') as plus_12_months,
sysdate + numtoyminterval(1,'year') as plus_1_year
from dual;

NOW       PLUS_1_MO PLUS_3_MO PLUS_12_M PLUS_1_YE
--------- --------- --------- --------- ---------
20-MAR-09 20-APR-09 20-JUN-09 20-MAR-10 20-MAR-10

Armed with this Oracle built-in function, I simply ran the following update statement to correct the bad data that my user had imported today.

update example_table
set date_goes_here = date_goes_here + numtoyminterval(1,'year')
where date_goes_here between '1-jan-1909' and '31-dec-1909'
and trunc(entry_date,'ddd') = trunc(sysdate,'ddd')
and entry_by = 'careless_user';

March 17, 2009

Rollback Oracle transactions in VBScript

The key to allow transactions lies in the command “.BeginTrans” that you should declare immediately after making the database connection, as seen in the example below.

option explicit
dim dbType, dbHost, dbName, dbUser, dbPass

dbHost = "hostname"   ' Hostname of the Oracle database server
dbName = "database"   ' Name of the database/SID
dbUser = "username"   ' Name of the user
dbPass = "password"   ' Password of the above-named user

dim conn
set conn = CreateObject("ADODB.connection")
conn.ConnectionTimeout = 30
conn.CommandTimeout = 30
conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")

conn.BeginTrans

conn.execute("update order set order_total=(select qty * unit_price from order_detail where order_id=123) where order_id=123")

dim checkOrderTotal
set checkOrderTotal = conn.execute("select order_total from order where order_id=123")

if (checkOrderTotal("order_total") < 0) then
	conn.RollbackTrans
	' TODO : Send an email to accounting department to check it out
else
	conn.CommitTrans
end if

conn.close
set conn = nothing

Notice the if-else clause near the bottom. If the order total somehow ended up a negative number, we rollback the transaction (and presumably would notify someone to check it out at this point), we roll back the transaction. Otherwise, as seen in the "else" clause, we proceed with committing the Oracle transaction.

It is important that you must issue either ".RollbackTrans" or ".CommitTrans" before the connection is terminated, otherwise you might get the VBScript ADODB connection error 800A0CAE, "Connection object cannot be explicitly closed while in transaction."

March 13, 2009

Performing MySQL query with case sensitivity

Let us take note of the following example data.

WEBSITE_NAME
dev-notes.com
Dev-Notes.com
DEV-NOTES.com

Note that the first row is in all lower case, the second in proper case, and the third contains all capitalized letters. If we run a normal MySQL select statement to search for “dev-notes” such as the below, the system will return all three records.

/* Standard query; not case sensitive */
select * from mytable where website_name='dev-notes.com';

To get around it, simply use the “binary” keyword before the column name, shown in the example below.

/* Case sensitive query */
select * from mytable where binary website_name='dev-notes.com';

The query above will only return the first record, which is the one that contains all lower case letters.

March 11, 2009

Resolving Oracle large temp tablespace size problem

Temporary tablespaces are used by the system to sort out things that it cannot do in memory. For example, if you join two very large tables that exceed the memory capacity, Oracle will use the temp tablespace to perform the sorting operation in the temp tablespace. Now, imagine the case in which an user performs a Cartesian join by mistake; the temp tablespace may grow to be a very large size in order to cope with the data. Once the operation is completed, Oracle will not automatically reduce the file size, which what I observed with my Oracle 10g database.

To resolve the issue, I performed the following 6 steps. This solution does not require system restart, but it is recommend that you perform this during slower hours if possible. Temp tablespace does not hold any permanent objects, so I do not back up my temp tablespace, but as always, I recommend that you make certain you have a very recent backup set available to you before you start working, just in case something goes wrong.

1. Create a new temp tablespace

create temporary tablespace temp2
tempfile '/filepath/TEMP02.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

2. Make the new temp tablespace the primary one

alter database default temporary tablespace temp2;

3. Drop the runaway tempfile of the original temp tablespace

alter database tempfile '/filepath/TEMP01.DBF' 
drop including datafiles;

4. Create a new tempfile for the original temp tablespace

alter tablespace temp
add tempfile '/filepath/TEMP01.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

5. Make the original temp tablespace the primary one again

alter database default temporary tablespace temp;

6. Drop the new temp tablespace we created in #1 as we no longer need it

drop tablespace temp2 including contents and datafiles;

At this point, I saw that the disk space is recovered, and the tempfile is at the modest size of 1gb. All the file paths and file sizes are for demonstration purposes only; you should adjust the commands according to your needs.

Not sure where your temp files are? Try the following select statement:

select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS#  RFILE# STATUS  ENABLED         BYTES  BLOCKS CREATE_BYTES BLOCK_SIZE NAME
----- ---------------- --------- --- ------- ------- ---------- ---------- ------- ------------ ---------- --------------------
    1                0             3       1 ONLINE  READ WRITE 1048576000  128000   1048576000       8192 /FILEPATH/TEMP01.DBF

March 10, 2009

Logging Windows events with VBScript

The VBScript code to perform the insertion into the event log is shown below. Please note that the “event_type” input variable will only take the following three values, in upper case letters:

“ERROR” – Use when logging a problem
“WARN” – Use when logging a minor issue
“INFO” – Use when logging miscellaneous information

When we run the example code further below, we will find the log as seen here.

Example Code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Description: This sample code demonstrates how to log information 
'       to the Windows application event log
' Author: C. Peter Chen, http://dev-notes.com
'
' Note: Allowable event_type values:
'         "ERROR" - Use when logging a problem
'         "WARN"  - Use when logging a minor issue
'         "INFO"  - Use when logging miscellaneous information
'
' Revision History:
'	1.0	20090310	Original release
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit

sub logWindowsEventViewer(event_type, event_text)
	dim WshShell, tp
	
	select case event_type
	case "ERROR"
		tp = "1"
	case "WARN"
		tp = "2"
	case "INFO"
		tp = "4"
	case else
		tp = "0"
	end select

	set WshShell = CreateObject("WScript.Shell")
	wshshell.Logevent tp, event_text
	set wshshell=nothing
end sub

' Usage example:
dim event_type, event_text
event_type = "ERROR"
event_text = "Application error: GUI crashed!"

call logWindowsEventViewer(event_type, event_text)

March 10, 2009

Searching for file name or file content in Linux

To search for a file by its name, follow the example below.

[username@machine ~]$ find / -name 'myLostFile*.txt'

/home/username/foldername/myLostFile_Feb2009.txt
/home/username/foldername/myLostFile_Mar2009.txt

Note that “find” is the command we wish to run. The “/” denotes where to start looking. We began looking from the root “/” here; if you know a more specific path where the file is, you can narrow it down by providing a path, such as “/home/username/” or “.”. The “-name” specifies we are searching by file name.

By adding “-ls” at the end, we can return more details on the matched files. Example below:

[username@machine ~]$ find . -name 'myLostFile*.txt' -ls

13795398 2310924 -rw-r--r--   1 username  username 2364071936 Feb 19 14:08 ./myLostFile_Feb2009.txt
13795396 59012 -rw-r--r--   1 username  username 60362752 Mar 10 08:29 ./myLostFile_Mar2009.txt

At times, we may need to search through the content of the files as well. To do so, follow the example below:

[username@machine ~]$ find . -type f -exec grep -H "dev-notes.com" {} ;

./myLostFile_Mar2009.txt:To do - write an article on Linux find command for dev-notes.com

We will get the file path and name in the first portion (before the colon symbol) of each output line. The particular line of that matched our search criteria will be displayed in the second portion. If multiple lines within the same file matches and/or if matches are found across multiple files, more than one output line will be displayed.

February 17, 2009

Using canonical tag to avoid duplicate content penalties

Having multiple URLs hosting nearly similar or identical content has many disadvantages. First of all, the search ranking score is being divided between the two URLs. Secondly, search engines may elect to penalize the pages if the content duplication, which is what the search engine will think of the situation, is widespread. There are other disadvantages that are not listed as well.

The canonical URL tag may be helpful in these situations:

A website uses a slightly different GET variable to display the same content with minor variations. For example, http://ww2db.com/person_bio.php?person_id=1 displays Admiral Isoroku Yamamoto’s biography and http://ww2db.com/person_bio.php?person_id=c1 displays the same biography specifically a Japanese officer. The latter may point to the former as its canonical. This is also helpful in the situation where session IDs, login IDs, etc. are being carried in the URL as GET variables.
A website uses a different URL for its mobile content or print-only content. For example, if http://www.example.com/mobile shows the same content as http://www.example.com except for different page layout, then the mobile page may point to the main page as its canonical.

Below is an example of how to establish a canonical. Note it resides in the HEAD section of a web page.


<html>
<head>
	<link rel="canonical" href="http://ww2db.com/person_bio.php?person_id=1" />
</head>
<body>
	<p>This is the biography for the Japanese officer Isoroku Yamamoto.</p>
</body>
</html>

As you can see, in the above example, the biography of Admiral Yamamoto as a Japanese officer points to his regular biography page as its canonical, thus all the search ranking related information for the variant version of the page goes back to the original version of the page.

It works very similar to a 301 redirect, although not as comprehensive and not as absolute. Nevertheless, it may be helpful as it may cause less headaches to the web developers, especially if the website’s size is large.

It is important to note that the canonical URL must reside on the same root domain as the variant. For example, the Japanese officer version of the biography as listed above cannot point to http://www.combinedfleet.com/officers/Isoroku_Yamamoto as its canonical as it is on a different domain.

The canonical URL tag is a hint for search engine crawlers; it is not a directive, thus it may or may not be absolute that search engines will follow it. However, major search engines such as Google, Yahoo, and MSN Live Search had noted that they will respect this hint.