Installing Sql Server 2005 SP2 Vol 2

SP2 installation progress

Installing Sql Server 2005 SP2 Vol 1

Installing Sql Server 2005 SP2 is easy. Installer package is just downloaded from Microsoft site and run.

Installing SQL Server 2005 - Vol 4

Collation Settings

Installing SQL Server 2005 - Vol 3

After installing prerequisites wizard continues installation and checks system configuration. After these steps wizard begin asking questions about sql server.

Installing SQL Server 2005 - Vol 2

After installing required windows components Sql Server 2005 installation can be started. On the "Start" page click "Server components, tools, Books Online, and samples" under the "Install" caption.

Installing SQL Server 2005 - Vol 1

Before installing Sql Server 2005 we have to install required windows components. To do this go to "Control Panel" then click on "Add Remove Programs" and open "Add Remove Windows Components" wizard. Install "Internet Information Services" under "Application Server".

RHQ a systems management solution

RHQ is a system management application written in java. It has server/agent architecture to implement remote management of server systems. RHQ needs some prerequisites like supported operating system, java environment and database. It runs on windows and linux operating systems that have java 6 jre or jdk installed. Postgres or oracle is supported. Latest release of RHQ is 4.2.0 now. RHQ is a little bit buggy but fixes them regularly. For detailed information about installing, configuring and running RHQ can be obtained from http://rhq-project.org/display/RHQ/Home

Backing up oracle database example

disk_rman.sh script uses disk_rman.txt that includes rman backup commands.

$ more disk_rman.sh 

#!/bin/bash

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

Generate secure download urls with lighttp

with mod_secdownload, lighttp can generate secure download urls for a given time period.
Generated url's format is <uri-prefix>/<token>/<timestamp-in-hex>/<rel-path> and looks like

"videos1.testing.com/vd1/2c2456cb1fa665644938b59950ba8ea5/4ef09a53/somevideo.mpeg"

Mount windows shares on linux

Any windows share can be mounted on linux easily. Cifs network protocol is used for mounting windows shares. smbclient package is also required.

first install samba-client package

# yum install samba-client

Archive logs older than 10 days

This shell command would archive log files that modification times are older than ten days. Find command takes arguments like -maxdepth 1 for maximum level of directories to dig , -type -f for identifying files, -name '*.log' for filtering log files, -mtime +10 for filtering modification time and -exec mv for executing commands for every file that found.

find /ftp/logs/ -maxdepth 1 -type f -name '*.log' -mtime +10 -exec mv {} /ftp/logs/archive/ \;

md5 checksum with linux command line

md5 checksum can be done from linux command line. Just run a command like following example:

# md5sum /tmp/sun-javadb-client-10.6.2-1.1.i386.rpm |cut -f1 -d " "

416abb0b79f48a70ddec5664af72db1f

Java hibernate select query example

A mysql table named servers contains server records. With the following java and xml files, all server are listed. In eclipse, hibernate.cfg.xml, hibernate.hbm.xml and com.test.Server java files can be produced easily.

This is hibernate config xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

java code for sending an email

Java code for sending mails. An smtp server is required to relay mails.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<%@ page import="java.util.*" %>

<%@ page import="java.net.*" %>

<%@page import="sun.net.smtp.SmtpClient"%>

Java file transfer with sftp

JSch is an ssh2 library for java and can be used to connect ssh server and make remote file transfers. Following example is an implementation of jsch remote file transfer. jsch library is is available at the maven repository, http://jsch.sf.net/maven2/

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<%@ page import="com.jcraft.jsch.*" %>

<%@ page import="com.jcraft.jsch.ChannelSftp.*" %>

java url redirection

An example of redirecting to another url with java

<%@ page import="java.util.*" %>

<%
String source_url = "http://abc.blogspot.com/";
String target_url = "http://def.blogspot.com/";

Java Active Directory authentication

Authenticate users member of a specific group with the java naming library.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.naming.*"%>
<%@ page import="javax.naming.ldap.*"%>
<%@ page import="javax.naming.directory.*"%>

Install RHQ agent on linux

Installing RHQ agent is simple. Only thing you have to do is just download plugin jar save it and run the installation command like java -jar <agent-download.jar> --install=<agent directory>. But i want to explain how we can install the agent as a service.

With the following script rhq enterprise agent 4.2.0 can be installed as a service on redhat linux.

Just another file copy program

There are two files in the project. gates.java and gates.properties. gates.java reads from properties file. In the properties file there are some configuration parameters.

gates.properties example:

sDirParam = /source

dDirParam = /destination

dFileExtParam = $G$

fileFilterParam = .srt;.avi;.pdf;

logFileParam = /logs/gates.log

Oracle logical standby monitoring

in this examle i am going to use php for getting oracle logical standby replication status. This connects primary and standby servers with oci, then query them which logs were applied and which logs are being applied currently.

Solr Replication Monitoring

in this examle i am going to use php for getting solr index replication xml and parsing it. There are two urls. One is inicating master solr server's status, second one is indicating slave solr server's status.

Urls are like:

http://<master server ip>:8080/solr/admin/cores

http://<slave server ip>:8080/solr/admin/cores

SQL Server DB inventory

with the following query db inventory of a sql server can be retrieved:

SELECT

SERVERPROPERTY('ServerName') as 'Server Name',

@@VERSION as 'SQL Version',

SERVERPROPERTY('productversion') as 'SQL Server Versiyon Number',

CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 0 THEN 'Mix' ELSE 'Windows' END as 'Auth Mode',

dbs.name as 'Database Name',

dbs.collation_name as 'Collation',

mf.physical_name as 'Database File'

FROM sys.master_files as mf, sys.databases as dbs
WHERE (mf.database_id = dbs.database_id) and (dbs.name not in ('master', 'model', 'msdb', 'tempdb'));

Linux Tilde Character

Tilde character represents home directory of the user. To go to home folder of the games user:

# cd ~games

# pwd

/usr/games

Checking Lucene Index Status

To check lucene index status, run something similar;

java -ea /opt/jboss/lucene/lucene-core-2.9.0.jar org.apache.lucene.index.CheckIndex /lucene_index

Linux rsync Command

To synchronize data between two local folder in linux, following command can be used:

# rsync -avzp /<source_dir>/ /<destination_dir>

Creating Synonyms of Tables For Another Schema

Following command can be used for generating “create synonym” queries. schema2 is the newly added schema that needs to use synomym. schema1 is the old schema that holds tables.

select 'create synonym <schema2>.' || TABLE_NAME || ' for <schema1>.' || TABLE_NAME || ';' from dba_tables where owner='<schema1>'

Calculating Size of the Specific Folders - Linux Command

To calculate total size of the directories that have specific name under a directory

Following command lists subfolders

find <folder name> -type d

Append this to get size of folders

-exec du -s {} \;

Append this to list only matching folder names

|grep <folder name>

Append this to sum folder sizes

|awk '{ SUM += $1 } END { print SUM/1024/1024 }'

find <root folder> -type d -exec du -s {} \; |grep <subfolder pattern> |awk '{ SUM += $1 } END { print SUM/1024/1024 }'

How to count the number of files in a directory - Linux Shell Command

To count number of files in a directory in linux shell command line just;

ls -lR <directory path> | wc -l

Output of a command in a shell script

When you want to assign output of a shell command to a variable in your shell script, you have to write shell command between special quotation marks ` <command here> `

example: This command shows oldest two backups.

old_backups=`ls -td backup* | awk 'NR>2'`

echo $old_backups

Java Code for Operating System Properties

The following is example of a java code which is getting some basic Operating System properties of a linux server.It gets host name, operating system name, kernel version and CPU architecture values, then writes them on a mysql database. I hope this helps anyone interested in systems management coding in java.

Getting IP Address information in Java

Java.net class must be used in order to learn ip address of the computer. Simply cast all information of the network interfaces  to an enumeration, then pass the enumeration through a collection.

import java.net.*;

Linux Commands in Java

You can use getRuntime method to run linux shell commands from java. Try this example to get memory statistics:

int ch;
String MemStr = "";

try

Find running transactions longer than one hour

to select running transactions longer than one hour

SELECT
   s.osuser, vp.spid as os_pid, S.BLOCKING_SESSION blocker,
   S.SID, S.SERIAL#, S.USERNAME, S.MACHINE,
   Q.SQL_FULLTEXT cur_sql, PQ.SQL_FULLTEXT prev_sql,
   vt.used_urec, vt.start_date

LOGSTDBY_PROCESS

There are number of sql apply process modules. Coordinator, reader, builder, preparer, analyzer, applier modules can be monitored with the following query

SELECT * FROM V$LOGSTDBY_PROCESS;

DBA_LOGSTDBY_LOG and DBA_LOGSTDBY_PROGRESS

using DBA_LOGSTDBY_LOG and DBA_LOGSTDBY_PROGRESS views sql apply process can be monitored

Monitoring Indexes

Index management is always key point of performance tuning in a database. It should be considered that optimum number of indexes are created.Sometimes an index is created and used once then it is forgotten. If large number of insert and update statements are committed, IO waits waits may be seen.

DBMS_LOGSTDBY INSTANTIATE_TABLE

Sometimes i have to recreate a table on the oracle standby database to keep standby consistent with the primary. It can be performed by DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. This procedure uses three parameters. These parameters are “Schema Name”, “Table Name”, “DB Link”.

DBMS_LOGSTDBY SKIP_TRANSACTION

When a transaction prevents sql apply service, it can be found by querying DBA_LOGSTDBY_EVENTS view.

select * from DBA_LOGSTDBY_EVENTS order by EVENT_TIMESTAMP, COMMIT_SCN;

Using this query, transaction identification information is listed.

XIDUSN NUMBER (Transaction ID undo segment number)

XIDSLT NUMBER (Transaction ID slot number)

XIDSQN NUMBER (Transaction ID sequence number)

Restoring Oracle Backup to a New Server

Nobody wants database server to crash, but if it happens most recent backup has to be restored on a newly replaced server. There are some requirements before the restoring operation. First, a server oracle software installed on it. Second, it makes restore operation easy that the same folder structure as the old server. Third, autobackup feature must be enabled at the old server. Last but not least, DBID of the old database.

Datapump TABLE_EXISTS_ACTION

When datapump import job is started with the option TABLE_EXISTS_ACTION there will be some effects on existing tables.

11g AFTER STARTUP Trigger

AFTER STARTUP trigger checks the database role and open mode of the database. Based on the values it starts the appropriate service using DBMS_SERVICE.START_SERVICE procedure.

Do not kill wrong session

Killing sessions are dangerous. So you have to be careful not to accidentally kill wrong sql session. You may follow the procedure below:

An Alternative Method for Oracle Physical Standby

Physical Standby Server can be used to recover data from disaster is one of the best methods. But the installation and management may be a little bit difficult. There is an another way.

Querying Long Running Jobs

Sometimes i want to see output details of a running datapump export job, but datapump’s job output shows only brief explanation and shows each process’s output after the process completed. In that case v$session_longops view helps us.

Some Oracle Questions and Answers

Where are datafiles?

SELECT FILE_NAME, TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;

Is database using pfile or spfile?

How to use Oracle Data Dictionary?

Data Dictionary is the metadata of the oracle database. It contains all informations about the database objects. There are sets of views in the Data Dictionary. They are in the SYSTEM tablespace and have prefixes.

When listener log file grows

When oracle listener.log file grows, it is getting harder to tail logs. The log file must be reopened. There is no need to close listener service to reset the log file. After setting the logging state off just rename the log file, then set the logging state on again. Just three commands and it is done.

rescan fc buses on linux

rescan fc controllers to add new disks

# echo "1" > /sys/class/fc_host/host<controller number>/issue_lip

# echo "1" > /sys/class/fc_host/host<controller number>/issue_lip

GPT partition on multipathed disk

A new gpt partition can be created on multipathed disk with the following command

# parted -s /dev/mapper/mpath1 -mklabel gpt mkpart primary ext3 0 -1

Multipath configuration for IBM SVC disk

make these changes to /etc/multipath.conf file and then restart multipathd daemon

Add new EMC storage disk to RHEL4

at RHEL 4 system new EMC storage disk can be scanned like this

smbpasswd

with smbpasswd a user is allowed to change their smb password

# smbpasswd -a <username>

Apache Basic Authentication

Creating basic authentication for a web application with apache web server

JBOSS Port Redirection

As for the subject to run jboss over port 80 in linux systems, the problem is that linux security model does not allow unprivileged users to use port 80. In my point of view,  best way to achieve this is port forwarding.

Kickstart Post Installation Steps in ks.cfg

Installing linux with kickstart server, post installation script can be used in order to make specific changes or install additional softwares.This can be done by adding commands after %post section in ks.cfg file.

Mysql Hotcopy Backup Script

It is very easy to get mysql hot copy backup with the following script. /mysql_backup is nfs mounted disk.

Oracle Export Cleaner (Example Script)

Regular database exports can be monitored and cleaned regularly while keeping desired number of successful exports with this linux script. There are two files, one is config file that containing schema names and corresponding schema export path, the other one  is export cleaner script.

Redhat Package Manager

rpm  is a useful Package Manager. It helps to install, update and uninstall softwares.

Shell Script (Reading config file)

In a shell script, we can use while loop for reading configurations. Config file is based on value and corresponding parameter.

Creating a Partition Larger Than 2 TB

If you want to create a partition larger than 2 TB on a linux operating system, fdisk utility will not cover your need. Parted is a good partitioning editor and may help you to create a partition larger than 2 TB.

What is Sticky Bit?

Sticky bit is an extra permission option for files and folders in linux. There are two main reasons for using sticky bit.

Ftp Site Synchronization with Wget

I have a task for synchronizing a remote ftp folder to a local folder. At the remote site, that day’s folder is placed in the form of year_month_day and the local folder placed in the form of year/month/day .

Redhat iSCSI Connection Configuration

Making iSCSI connection from Redhat Linux server is easy to implement.

Running java program as a linux service

Recently, I was wondering about how the programs are running as a service in Linux. After a little googling,  i learned it is very simple. All we have to do is just write a script that starts, stops and restarts the program and register this script as a Linux service. Let me tell the scenario.

Find and replace with sed command in Linux

Find and replace feature is always handy. It can turn into a torture when it comes to change or delete a simple constant string in a text file. There is a handy tool in linux for doing these kind of tihngs. Actually sed is not a text editor but it is used outside of the text file to make changes.

Extending a logical volume at LVM

Suppose that we have a volume group named VG-TEST and It contains a logical volume named LV-TEST. After adding new physical disk /dev/sdx , we want to give additional 100G space to the LV-TEST logical volume. Following procedure is applied to the server.

Make Backups using LVM snapshots

With Logical Volume Manager, backups can be taken using snapshots.

in this example the resources are: