1. Startup Script
I have a fedora core box which needs to run different scripts on startup to provide services to other machines.
I found what appears to be the best solution for me, using ntsysv and init.d. Here's how it's done;
1.) make a new file in the /etc/init.d/ directory
2.) add your script to this file with the following lines at the top;
#!/bin/bash
# chkconfig: 345 85 15
# description: of your file
3.) enter this in the shell;
chkconfig --add startup_filename
4.) type ntsysv (instead of using setup>service>etc) - your startup script should now be in the list, checked and ready for action!
5. Confirm by
# service sesamedms-test start
NB: make sure the file is executable
Sample File
#!/bin/bash
# chkconfig: 345 85 15
# description: starts tomcat server
case "$1" in
start) /opt/tomcat/bin/startup.sh ;;
stop) /opt/tomcat/bin/shutdown.sh ;;
restart) /bin/sh $0 stop
/bin/sh $0 start ;;
*) echo "Usage: $0 {start|stop}"
exit 1 ;;
esac
Wednesday, September 1, 2010
Solaris SMF - processes comprising a service
Another common task is to list the processes comprising a service. In this example, we will look at the Apache2 service.
First, check if the service is running, using the svcs command:
my-system# svcs apache2
STATE STIME FMRI
online 11:25:33 svc:/network/http:apache2
To list the process IDs of this service, use the svcs command with the -p option.
my-system# svcs -p apache2
STATE STIME FMRI
online 11:25:33 svc:/network/http:apache2
11:25:33 2438 httpd
11:25:34 2439 httpd
11:25:34 2440 httpd
11:25:34 2441 httpd
11:25:34 2442 httpd
11:25:34 2443 httpd
Viewing all Service Information
To view all available information about a particular service, use the svcs command with the -l option, specifying the service by name:
my-system# svcs -l apache2
fmri svc:/network/http:apache2
name Apache 2 HTTP server
enabled true
state online
next_state none
state_time Wed Jun 07 11:47:04 2006
logfile /var/svc/log/network-http:apache2.log
restarter svc:/system/svc/restarter:default
contract_id 297
dependency require_all/error svc:/network/loopback:default (online)
dependency optional_all/error svc:/network/physical:default (online)
This option gives a number of useful details about the service. For example, the service is enabled, is online, and has been online since June 7 11:47:04 2006. The logfile, the restarter, and the service dependencies are given as well.
Common Administrative Tasks
This examples starts the Apache2 service and performs some common administrative tasks on the service.
To start the apache2 service, use the svcadm command with the enable option:
my-system# svcadm enable apache2
To display its status, use the svcs command:
my-system# svcs http
STATE STIME FMRI
online 11:26:46 svc:/network/http:apache2
To examine the process IDs associated with the service, use the scvs command with the -p option
my-system# svcs -p http
STATE STIME FMRI
online 11:26:46 svc:/network/http:apache2
11:26:46 2463 httpd
11:26:47 2464 httpd
11:26:47 2465 httpd
11:26:47 2466 httpd
11:26:47 2467 httpd
11:26:47 2468 httpd
To kill the service, use the pkill command. Then check the status again:
my-system# pkill http
my-system# svcs http
STATE STIME FMRI
online 11:28:05 svc:/network/http:apache2
Note that in this example the service did indeed stop, but was restarted automatically. STIME in the two cases are different indicating that the service was restarted. SMF increases the uptime of the service and also makes this information easy to retrieve.
Get more information using the -x option.
my-system# svcs -x http
svc:/network/http:apache2 (Apache 2 HTTP server)
State: online since Tue Jun 06 11:28:05 2006
See: apache2(1M)
See: /var/svc/log/network-http:apache2.log
Impact: None.
With SMF it is easy to get additional information about services using the -x and -v options of the svcs(1) command. This is particularly helpful when you are investigating the reason why a particular service has failed.
Option
Description
-x
Displays explanations for service states
-v
With -x, displays extra information for each explanation
First, check if the service is running, using the svcs command:
my-system# svcs apache2
STATE STIME FMRI
online 11:25:33 svc:/network/http:apache2
To list the process IDs of this service, use the svcs command with the -p option.
my-system# svcs -p apache2
STATE STIME FMRI
online 11:25:33 svc:/network/http:apache2
11:25:33 2438 httpd
11:25:34 2439 httpd
11:25:34 2440 httpd
11:25:34 2441 httpd
11:25:34 2442 httpd
11:25:34 2443 httpd
Viewing all Service Information
To view all available information about a particular service, use the svcs command with the -l option, specifying the service by name:
my-system# svcs -l apache2
fmri svc:/network/http:apache2
name Apache 2 HTTP server
enabled true
state online
next_state none
state_time Wed Jun 07 11:47:04 2006
logfile /var/svc/log/network-http:apache2.log
restarter svc:/system/svc/restarter:default
contract_id 297
dependency require_all/error svc:/network/loopback:default (online)
dependency optional_all/error svc:/network/physical:default (online)
This option gives a number of useful details about the service. For example, the service is enabled, is online, and has been online since June 7 11:47:04 2006. The logfile, the restarter, and the service dependencies are given as well.
Common Administrative Tasks
This examples starts the Apache2 service and performs some common administrative tasks on the service.
To start the apache2 service, use the svcadm command with the enable option:
my-system# svcadm enable apache2
To display its status, use the svcs command:
my-system# svcs http
STATE STIME FMRI
online 11:26:46 svc:/network/http:apache2
To examine the process IDs associated with the service, use the scvs command with the -p option
my-system# svcs -p http
STATE STIME FMRI
online 11:26:46 svc:/network/http:apache2
11:26:46 2463 httpd
11:26:47 2464 httpd
11:26:47 2465 httpd
11:26:47 2466 httpd
11:26:47 2467 httpd
11:26:47 2468 httpd
To kill the service, use the pkill command. Then check the status again:
my-system# pkill http
my-system# svcs http
STATE STIME FMRI
online 11:28:05 svc:/network/http:apache2
Note that in this example the service did indeed stop, but was restarted automatically. STIME in the two cases are different indicating that the service was restarted. SMF increases the uptime of the service and also makes this information easy to retrieve.
Get more information using the -x option.
my-system# svcs -x http
svc:/network/http:apache2 (Apache 2 HTTP server)
State: online since Tue Jun 06 11:28:05 2006
See: apache2(1M)
See: /var/svc/log/network-http:apache2.log
Impact: None.
With SMF it is easy to get additional information about services using the -x and -v options of the svcs(1) command. This is particularly helpful when you are investigating the reason why a particular service has failed.
Option
Description
-x
Displays explanations for service states
-v
With -x, displays extra information for each explanation
Solaris - svc command options
Table 2—Useful options for svcs(1)
Note that a lowercase -d option and the uppercase -D option actually mean different things. The -d option results in a list of services on which the named service depends, while the -D option results in a list of services which depend upon the named service. Think of them as above and below the service on a dependency tree.
In Step 1, you used the -a option to list all services on the system. Now take a look at the list of services on which inetd depends.
To list the dependencies of the inetd service, use the svcs command, specifying the -d option:
my-system# svcs -d inetd
STATE STIME FMRI
disabled Apr_18 svc:/network/inetd-upgrade:default
online Apr_18 svc:/milestone/name-services:default
online Apr_18 svc:/network/loopback:default
online Apr_18 svc:/milestone/network:default
online Apr_18 svc:/system/filesystem/local:default
online Apr_18 svc:/network/rpc/bind:default
online Apr_18 svc:/milestone/sysconfig:default
As you can see, inetd depends upon a number of different services including inetd-upgrade and name-services. The same command can be used to find out if any one of these services depends upon other services. With the information gathered you can sketch out the dependency tree for inetd. Figure 1 shows a partial graph of the dependency tree for inetd.

Figure 1—Partial dependency tree for inetd (enlarge image)
To generate a similar list for Apache2, use the svcs command and the -d option, specifying Apache2 by name:
my-system# svcs -d apache2
STATE STIME FMRI
online Apr_18 svc:/network/loopback:default
online Apr_18 svc:/network/physical:default
Next, use the -D option to identify services which depend upon inetd and Apache.
To discover the services which depend upon inetd, use the svcs command with the -D option, specifying inetd by name:
my-system# svcs -D inetd
STATE STIME FMRI
online Apr_18 svc:/milestone/multi-user:default
In this example you can see that multi-user depends upon inetd.
Next, find the services which, in turn, depend upon multi-user.
To discover the services which depend upon multi-user, use the svcs command with the -D option, specifying multiuser by name:
my-system# svcs -D multi-user
STATE STIME FMRI
disabled Apr_18 svc:/network/dhcp-server:default
online Apr_18 svc:/milestone/multi-user-server:default
Notice that there are two services which depend upon multi-user, dhcp-server and multi-user-server.
Next, examine the dhcp-server.
To discover the services which depend upon dhcp-server, use the svcs command with the -D option, specifying dhcp-server by name. Follow through the whole dependency tree in the same way:
my-system# svcs -D dhcp-server
STATE STIME FMRI
online Apr_18 svc:/milestone/multi-user-server:default
Find the services which depend upon multi-user-server:
my-system# svcs -D multi-user-server
STATE STIME FMRI
online Apr_18 svc:/system/zones:default
Find services which depend upon zones:
my-system# svcs -D zones
STATE STIME FMRI
In this case, there are no services which depend on zones, so this is the end of the dependency tree. See the dependency tree below.
 Listing Service Processes
Note that a lowercase -d option and the uppercase -D option actually mean different things. The -d option results in a list of services on which the named service depends, while the -D option results in a list of services which depend upon the named service. Think of them as above and below the service on a dependency tree.
In Step 1, you used the -a option to list all services on the system. Now take a look at the list of services on which inetd depends.
To list the dependencies of the inetd service, use the svcs command, specifying the -d option:
my-system# svcs -d inetd
STATE STIME FMRI
disabled Apr_18 svc:/network/inetd-upgrade:default
online Apr_18 svc:/milestone/name-services:default
online Apr_18 svc:/network/loopback:default
online Apr_18 svc:/milestone/network:default
online Apr_18 svc:/system/filesystem/local:default
online Apr_18 svc:/network/rpc/bind:default
online Apr_18 svc:/milestone/sysconfig:default
As you can see, inetd depends upon a number of different services including inetd-upgrade and name-services. The same command can be used to find out if any one of these services depends upon other services. With the information gathered you can sketch out the dependency tree for inetd. Figure 1 shows a partial graph of the dependency tree for inetd.

Figure 1—Partial dependency tree for inetd (enlarge image)
To generate a similar list for Apache2, use the svcs command and the -d option, specifying Apache2 by name:
my-system# svcs -d apache2
STATE STIME FMRI
online Apr_18 svc:/network/loopback:default
online Apr_18 svc:/network/physical:default
Next, use the -D option to identify services which depend upon inetd and Apache.
To discover the services which depend upon inetd, use the svcs command with the -D option, specifying inetd by name:
my-system# svcs -D inetd
STATE STIME FMRI
online Apr_18 svc:/milestone/multi-user:default
In this example you can see that multi-user depends upon inetd.
Next, find the services which, in turn, depend upon multi-user.
To discover the services which depend upon multi-user, use the svcs command with the -D option, specifying multiuser by name:
my-system# svcs -D multi-user
STATE STIME FMRI
disabled Apr_18 svc:/network/dhcp-server:default
online Apr_18 svc:/milestone/multi-user-server:default
Notice that there are two services which depend upon multi-user, dhcp-server and multi-user-server.
Next, examine the dhcp-server.
To discover the services which depend upon dhcp-server, use the svcs command with the -D option, specifying dhcp-server by name. Follow through the whole dependency tree in the same way:
my-system# svcs -D dhcp-server
STATE STIME FMRI
online Apr_18 svc:/milestone/multi-user-server:default
Find the services which depend upon multi-user-server:
my-system# svcs -D multi-user-server
STATE STIME FMRI
online Apr_18 svc:/system/zones:default
Find services which depend upon zones:
my-system# svcs -D zones
STATE STIME FMRI
In this case, there are no services which depend on zones, so this is the end of the dependency tree. See the dependency tree below.
 Listing Service Processes
Solaris - Service Management Primer
SMF Commands
SMF has a limited yet powerful set of commands. Each command has several options which cover the tasks required to manage Solaris systems. The following table lists the SMF commands.
Command
Description
svcs
Reports service status
svcadm
Used for service management: e.g., starting, stopping and restoring services
svccfg
Used to list properties of a service
svcprop
Used to list properties of a service
inetadm
Used to manage inetd services
Table 1—SMF commands
This guide focuses on gathering information about the services running on a Solaris system and troubleshooting a failed service. These tasks are accomplished with the svcs and svcadm commands.
The other commands enable system administrators to manage, modify, and display service manifests. You can read more about them on docs.sun.com under the Solaris 10 Basic Administration Guide.
An Example
This example begins by looking at all the services currently running on your Solaris system and then examining a few of the services for more details. These details include the services upon which they depend and the services which depend upon them.
Displaying System Services Information
To display all services on your Solaris system with their state information, use the svcs command along with the -a option:
my-system# svcs -a
STATE STIME FMRI
legacy_run Apr_18 lrc:/etc/rcS_d/S51installupdates
legacy_run Apr_18 lrc:/etc/rc2_d/S47pppd
legacy_run Apr_18 lrc:/etc/rc2_d/S99audit
legacy_run Apr_18 lrc:/etc/rc3_d/S76snmpdx
legacy_run Apr_18 lrc:/etc/rc3_d/S90samba
disabled Apr_18 svc:/network/ipfilter:default
disabled Apr_18 svc:/network/rpc/keyserv:default
disabled Apr_18 svc:/network/rpc/nisplus:default
disabled Apr_18 svc:/application/print/server:default
disabled Apr_18 svc:/network/dhcp-server:default
disabled Apr_18 svc:/network/http:apache2
online Apr_18 svc:/system/svc/restarter:default
online Apr_18 svc:/network/pfil:default
online Apr_18 svc:/network/physical:default
online Apr_18 svc:/system/identity:domain
online Apr_18 svc:/system/cryptosvc:default
online Apr_18 svc:/network/inetd:default
online Apr_18 svc:/network/telnet:default
online Apr_18 svc:/network/ssh:default
online Apr_18 svc:/system/zones:default
online Apr_18 svc:/network/nfs/nlockmgr:default
offline Apr_18 svc:/application/print/ipp-listener:default
offline Apr_18 svc:/application/print/rfc1179:default
[Note: This is a truncated list] Displaying Individual Services
You can look at individual services as well. This is especially useful during troubleshooting or when examining what is going on with a particular service.
To display information about the inetd service, use the svcs command specifying the service by name:
my-system# svcs inetd
STATE STIME FMRI
online Apr_18 svc:/network/inetd:default
To display information about the Samba service, use the svcs command specifying the service by name:
my-system# svcs S90samba
STATE STIME FMRI
legacy_run Apr_18 lrc:/etc/rc3_d/S90samba
To display information about the Apache service, use the svcs command specifying the service by name:
my-system# svcs apache2
STATE STIME FMRI
disabled Apr_18 svc:/network/http:apache2 Retrieving Dependency Tree Information
SMF permits you to identify all the service dependencies for a given service. That is, the services upon which a given service depends, as well as the services that depend upon that service. The following options are used to provide additional detail on the services.
Option
Description
-a
Displays all services, including those which have been disabled
-d
Lists a service's dependencies
-D
Lists a service's dependents
-l
Displays all available information about the service
-p
Lists all processes (PID) associated with a service
SMF has a limited yet powerful set of commands. Each command has several options which cover the tasks required to manage Solaris systems. The following table lists the SMF commands.
Command
Description
svcs
Reports service status
svcadm
Used for service management: e.g., starting, stopping and restoring services
svccfg
Used to list properties of a service
svcprop
Used to list properties of a service
inetadm
Used to manage inetd services
Table 1—SMF commands
This guide focuses on gathering information about the services running on a Solaris system and troubleshooting a failed service. These tasks are accomplished with the svcs and svcadm commands.
The other commands enable system administrators to manage, modify, and display service manifests. You can read more about them on docs.sun.com under the Solaris 10 Basic Administration Guide.
An Example
This example begins by looking at all the services currently running on your Solaris system and then examining a few of the services for more details. These details include the services upon which they depend and the services which depend upon them.
Displaying System Services Information
To display all services on your Solaris system with their state information, use the svcs command along with the -a option:
my-system# svcs -a
STATE STIME FMRI
legacy_run Apr_18 lrc:/etc/rcS_d/S51installupdates
legacy_run Apr_18 lrc:/etc/rc2_d/S47pppd
legacy_run Apr_18 lrc:/etc/rc2_d/S99audit
legacy_run Apr_18 lrc:/etc/rc3_d/S76snmpdx
legacy_run Apr_18 lrc:/etc/rc3_d/S90samba
disabled Apr_18 svc:/network/ipfilter:default
disabled Apr_18 svc:/network/rpc/keyserv:default
disabled Apr_18 svc:/network/rpc/nisplus:default
disabled Apr_18 svc:/application/print/server:default
disabled Apr_18 svc:/network/dhcp-server:default
disabled Apr_18 svc:/network/http:apache2
online Apr_18 svc:/system/svc/restarter:default
online Apr_18 svc:/network/pfil:default
online Apr_18 svc:/network/physical:default
online Apr_18 svc:/system/identity:domain
online Apr_18 svc:/system/cryptosvc:default
online Apr_18 svc:/network/inetd:default
online Apr_18 svc:/network/telnet:default
online Apr_18 svc:/network/ssh:default
online Apr_18 svc:/system/zones:default
online Apr_18 svc:/network/nfs/nlockmgr:default
offline Apr_18 svc:/application/print/ipp-listener:default
offline Apr_18 svc:/application/print/rfc1179:default
[Note: This is a truncated list] Displaying Individual Services
You can look at individual services as well. This is especially useful during troubleshooting or when examining what is going on with a particular service.
To display information about the inetd service, use the svcs command specifying the service by name:
my-system# svcs inetd
STATE STIME FMRI
online Apr_18 svc:/network/inetd:default
To display information about the Samba service, use the svcs command specifying the service by name:
my-system# svcs S90samba
STATE STIME FMRI
legacy_run Apr_18 lrc:/etc/rc3_d/S90samba
To display information about the Apache service, use the svcs command specifying the service by name:
my-system# svcs apache2
STATE STIME FMRI
disabled Apr_18 svc:/network/http:apache2 Retrieving Dependency Tree Information
SMF permits you to identify all the service dependencies for a given service. That is, the services upon which a given service depends, as well as the services that depend upon that service. The following options are used to provide additional detail on the services.
Option
Description
-a
Displays all services, including those which have been disabled
-d
Lists a service's dependencies
-D
Lists a service's dependents
-l
Displays all available information about the service
-p
Lists all processes (PID) associated with a service
Solaris - Network Interfaces
1. Ip Address basics
checking the config of a particular (configured) interface
- ifconfig e1000g0
Modifying
- ifconfig e1000g0 inet 192.168.1.1 netmask 255.255.255.0 broadcast 192.168.1.255
2. Network Interfaces
a. check configured (plumbed ??) interfaces
# ifconfig -a
b. to check all connected physical nic ports
# dladm show-dev
c. to mount(?) an interface (eg e1000g1) (before u can give it an ip)
# efconfig e1000g1 plumb
d. to give it an ip
#ifconfig e1000g1 inet 192.168.56.103 netmask 255.255.255.0 broadcast 192.168.56.255
e. to bring it up (make it usable)
#ifconfig e1000g1 up
checking the config of a particular (configured) interface
- ifconfig e1000g0
Modifying
- ifconfig e1000g0 inet 192.168.1.1 netmask 255.255.255.0 broadcast 192.168.1.255
2. Network Interfaces
a. check configured (plumbed ??) interfaces
# ifconfig -a
b. to check all connected physical nic ports
# dladm show-dev
c. to mount(?) an interface (eg e1000g1) (before u can give it an ip)
# efconfig e1000g1 plumb
d. to give it an ip
#ifconfig e1000g1 inet 192.168.56.103 netmask 255.255.255.0 broadcast 192.168.56.255
e. to bring it up (make it usable)
#ifconfig e1000g1 up
PostgreSQL - Dump and Restore
#pg_dump -Fc imlu > imlu.sql
1. My Dump - explicit column names
#pg_dump -c -D -v sacco>dump.sacco.sql
-c drops db objects b4 recreating them
-C starts with command to create the db and reconnect to it
-D dump with explicit column names (slow)
2. Simplest dump
# pg_dump -d sacco > simplestdump.sacco.sql
-d Dump data as INSERT commands
3. single table dump
#pg_dump -d -t periods sacco >dump.periods.sacco.sql
RESTORE
#createdb sacco (after dropping) - or createdb -T template0 sacco
#psql -f dump.sacco.sql sacco - or psql dbname < infile
23.1.2. Using pg_dumpall
The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as users and groups. The basic usage of this command is:
pg_dumpall > outfile
The resulting dump can be restored with psql:
psql -f infile postgres
(Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then postgres should generally be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the user and group information.
List All Databases
\l
Alternatively, you can list the databases from the command line using:
psql -l
1. My Dump - explicit column names
#pg_dump -c -D -v sacco>dump.sacco.sql
-c drops db objects b4 recreating them
-C starts with command to create the db and reconnect to it
-D dump with explicit column names (slow)
2. Simplest dump
# pg_dump -d sacco > simplestdump.sacco.sql
-d Dump data as INSERT commands
3. single table dump
#pg_dump -d -t periods sacco >dump.periods.sacco.sql
RESTORE
#createdb sacco (after dropping) - or createdb -T template0 sacco
#psql -f dump.sacco.sql sacco - or psql dbname < infile
23.1.2. Using pg_dumpall
The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as users and groups. The basic usage of this command is:
pg_dumpall > outfile
The resulting dump can be restored with psql:
psql -f infile postgres
(Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then postgres should generally be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the user and group information.
List All Databases
\l
Alternatively, you can list the databases from the command line using:
psql -l
Oracle - Date Functions
Examples
1. Get the date of the next/following Saturday from today
SELECT NEXT_DAY(SYSDATE, 'SAT') FROM dual;
2. Number of months between two dates
SELECT MONTHS_BETWEEN(actiondate, SYSDATE) FROM clientlicenses;
3. Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') as NEW_YEAR FROM dual;
1. Get the date of the next/following Saturday from today
SELECT NEXT_DAY(SYSDATE, 'SAT') FROM dual;
2. Number of months between two dates
SELECT MONTHS_BETWEEN(actiondate, SYSDATE) FROM clientlicenses;
3. Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') as NEW_YEAR FROM dual;
Oracle - Syntax
1. Decode
This works as if-then-else flow control
Here i want to display the relevant application form depending on the licensename
SELECT
decode(vwclientlicenses.licensename,'Land Mobile Service','RF 1','Aircraft Station','RF 14','Maritime Station','RF 14B','Port Operations(Coast) Radio','RF 14B','Amateur Band Radio','RF 2','Citizen Band Radio','RF 1B','Terrestrial Point to Point Fixed Links','RF 3','Fixed Wireless Access Network','RF 3','Terrestrial Point to Multipoint Fixed Links','RF 3','Cellular Network','RF 3','Broadcasting (Radio) - Commercial Free To Air','RF 4','Broadcasting (TV) - Commercial Free To Air','RF 5','RF 1') as applicationform
FROM vwclientlicenses;
2. Coalesce
This allows us to trap NULL values and handle them accordingly
Example
SELECT
correspondenceaction.correspondenceactionid,
coalesce(esc.fullname,us.fullname) as forwardedby,
('' || correspondence.cckreference || '
' || correspondence.correspondencesource || '
' || correspondence.subject || '') as corrrespondencedetails
FROM correspondenceaction
INNER JOIN correspondence ON correspondenceaction.correspondenceid=correspondence.correspondenceid
INNER JOIN users ac ON correspondenceaction.actorid = ac.userid
LEFT JOIN users esc ON correspondenceaction.escalatedby = esc.userid
LEFT JOIN users us ON correspondenceaction.userid = us.userid;
3. Database links
CREATE DATABASE LINK "ERP_LINK.CCK"
CONNECT TO "OPENBR" IDENTIFIED BY VALUES '058C24497EA0E9171D374EA0A3A6D411E09B5CA906AFC989CA' --hash of password
USING '172.100.3.22:1530/imiserp';
This works as if-then-else flow control
Here i want to display the relevant application form depending on the licensename
SELECT
decode(vwclientlicenses.licensename,'Land Mobile Service','RF 1','Aircraft Station','RF 14','Maritime Station','RF 14B','Port Operations(Coast) Radio','RF 14B','Amateur Band Radio','RF 2','Citizen Band Radio','RF 1B','Terrestrial Point to Point Fixed Links','RF 3','Fixed Wireless Access Network','RF 3','Terrestrial Point to Multipoint Fixed Links','RF 3','Cellular Network','RF 3','Broadcasting (Radio) - Commercial Free To Air','RF 4','Broadcasting (TV) - Commercial Free To Air','RF 5','RF 1') as applicationform
FROM vwclientlicenses;
2. Coalesce
This allows us to trap NULL values and handle them accordingly
Example
SELECT
correspondenceaction.correspondenceactionid,
coalesce(esc.fullname,us.fullname) as forwardedby,
('' || correspondence.cckreference || '
' || correspondence.correspondencesource || '
' || correspondence.subject || '') as corrrespondencedetails
FROM correspondenceaction
INNER JOIN correspondence ON correspondenceaction.correspondenceid=correspondence.correspondenceid
INNER JOIN users ac ON correspondenceaction.actorid = ac.userid
LEFT JOIN users esc ON correspondenceaction.escalatedby = esc.userid
LEFT JOIN users us ON correspondenceaction.userid = us.userid;
3. Database links
CREATE DATABASE LINK "ERP_LINK.CCK"
CONNECT TO "OPENBR" IDENTIFIED BY VALUES '058C24497EA0E9171D374EA0A3A6D411E09B5CA906AFC989CA' --hash of password
USING '172.100.3.22:1530/imiserp';
Oracle - Resetting a sequence
For some reason you may want to reset a sequence during operation.
Scenario
I had a situation where the reference number of a correspondence has to start from 1 in every month.
Code Snippet:
--if its not equal to this month then this is a new entry of this month
IF (lastaction != mm) THEN
--we attempt to reset the sequence
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual;
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by ' || -nxt || 'minvalue 0';
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual; --move to 0 (counter-intuive)
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by 1 minvalue 0';
--by now the next value should be 1
END IF;
Scenario
I had a situation where the reference number of a correspondence has to start from 1 in every month.
Code Snippet:
--if its not equal to this month then this is a new entry of this month
IF (lastaction != mm) THEN
--we attempt to reset the sequence
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual;
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by ' || -nxt || 'minvalue 0';
SELECT ref_sequence.NEXTVAL INTO nxt FROM dual; --move to 0 (counter-intuive)
EXECUTE IMMEDIATE 'alter sequence ref_sequence increment by 1 minvalue 0';
--by now the next value should be 1
END IF;
Oracle 10g - Configure Email
UTL Mail Package for sending mail
1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql
2. GRANT
GRANT execute ON utl_mail TO;
(or)
GRANT execute ON utl_mail TO PUBLIC;
3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;
I prefer setting this inside the procedure/function sending the mail
4. Restart the DBMS
>shutdown immediate
>startup
SENDING EMAIL
Showing only the relevant part of the function :
IF(email = '1')THEN
--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;
1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql
2. GRANT
GRANT execute ON utl_mail TO
(or)
GRANT execute ON utl_mail TO PUBLIC;
3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;
I prefer setting this inside the procedure/function sending the mail
4. Restart the DBMS
>shutdown immediate
>startup
SENDING EMAIL
Showing only the relevant part of the function :
IF(email = '1')THEN
--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;
Oracle 10g - Command Line
NB: Linux Server Assummed
1. Manual Startup
#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //connect to sqlplus as sysdba
SQL>startup //start up the db
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //start Enterprise Manager
NB: shutting down = shutdown immediate (from withing sqlplus)
2. Related (run as user oracle)
#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm
Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)
#netca //configure listener
1. Manual Startup
#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //connect to sqlplus as sysdba
SQL>startup //start up the db
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //start Enterprise Manager
NB: shutting down = shutdown immediate (from withing sqlplus)
2. Related (run as user oracle)
#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm
Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)
#netca //configure listener
Virtual Box - Teleportation
Since Virtualbox v3.1
Move a running VM from one Physical Machine to another (for load balancing or fault tolerance)
Move a running VM from one Physical Machine to another (for load balancing or fault tolerance)
Virtual Box - Networking
1. Host Only Network
This is a network between the hosts and guests (configured and running)
Host IP
vboxnet0
Link encap:Ethernet HWaddr 0A:00:27:00:00:00
inet addr:192.168.56.1 Bcast:192.168.56.255 Mask:255.255.255.0
inet6 addr: fe80::800:27ff:fe00:0/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:25 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:5697 (5.5 KiB)
Guests
192.168.56.x
2. Internal Network
This is a Virtual LAN between guests.
3. Bridged Network
This uses the host as the network to allow your guests to join the corporate LAN (and obtain IPs in the same range as the host).
4. NAT
Network Address Translation.
The following example will give u an overview of what NATing can achieve.
The following commands will forward TCP traffic that originates from port 2222 on your host OS to port 22 on your guest OS:
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/Protocol" TCP
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/GuestPort" 22
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/HostPort" 2222
This is a network between the hosts and guests (configured and running)
Host IP
vboxnet0
Link encap:Ethernet HWaddr 0A:00:27:00:00:00
inet addr:192.168.56.1 Bcast:192.168.56.255 Mask:255.255.255.0
inet6 addr: fe80::800:27ff:fe00:0/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:25 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:5697 (5.5 KiB)
Guests
192.168.56.x
2. Internal Network
This is a Virtual LAN between guests.
3. Bridged Network
This uses the host as the network to allow your guests to join the corporate LAN (and obtain IPs in the same range as the host).
4. NAT
Network Address Translation.
The following example will give u an overview of what NATing can achieve.
The following commands will forward TCP traffic that originates from port 2222 on your host OS to port 22 on your guest OS:
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/Protocol" TCP
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/GuestPort" 22
$ VBoxManage setextradata "Windows Guest" \
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestssh/HostPort" 2222
Tomcat Virtual Hosts
Assumptions
1. One development host with two host names, devhost and prodhost each for development and production (configure dns accordingly or client's host file). Host name can be any valid hostnames including development.com, intranet.cit.go.ke, amongh others
2. One instance of Tomcat running,
so $CATALINA_HOME refers to wherever it's installed, perhaps /opt/tomcat.
3. Unix-style path separators and commands; if you're on Windows modify accordingly.
A. Configure Engine
File:server.xml at $CATALINA_HOME/conf
At the simplest, edit the Engine portion of your server.xml file to look like this:
B. Configure Webapps Directories
Create directories for each of the virtual hosts:
mkdir $CATALINA_HOME/devapps
mkdir $CATALINA_HOME/prodapps
Ah ha
I also normaly copy the host-manager/ and manager/ directories to corresponding webapps folder for each virtual host.
I dont know why but i just find myself doing it everytime i create virtualhosts (it may have something to do with manager.xml file in host-manager folder)
1. One development host with two host names, devhost and prodhost each for development and production (configure dns accordingly or client's host file). Host name can be any valid hostnames including development.com, intranet.cit.go.ke, amongh others
2. One instance of Tomcat running,
so $CATALINA_HOME refers to wherever it's installed, perhaps /opt/tomcat.
3. Unix-style path separators and commands; if you're on Windows modify accordingly.
A. Configure Engine
File:server.xml at $CATALINA_HOME/conf
At the simplest, edit the Engine portion of your server.xml file to look like this:
B. Configure Webapps Directories
Create directories for each of the virtual hosts:
mkdir $CATALINA_HOME/devapps
mkdir $CATALINA_HOME/prodapps
Ah ha
I also normaly copy the host-manager/ and manager/ directories to corresponding webapps folder for each virtual host.
I dont know why but i just find myself doing it everytime i create virtualhosts (it may have something to do with manager.xml file in host-manager folder)
Fixing Tomcat PermGem Space Error
Tomcat PermGem Space error:
How to fix it ?
1) Find where is Cataline.sh located. We need to make some changes in “catalina.sh” file.
P.S Cataline.sh is located at /tomcat folder/bin/catalina.sh
2) Assign following line to JAVA_OPTS variable and add it into catalina.sh file.
JAVA_OPTS="-Djava.awt.headless=true -Dfile.encoding=UTF-8
-server -Xms1536m -Xmx1536m
-XX:NewSize=256m -XX:MaxNewSize=256m -XX:PermSize=256m
-XX:MaxPermSize=256m -XX:+DisableExplicitGC"
Java Opts
-Xms
initial java heap size
-Xmx
maximum java heap size
-Xmn
the size of the heap for the young generation
-server starts Tomcat with the Server JVM
How to fix it ?
1) Find where is Cataline.sh located. We need to make some changes in “catalina.sh” file.
P.S Cataline.sh is located at /tomcat folder/bin/catalina.sh
2) Assign following line to JAVA_OPTS variable and add it into catalina.sh file.
JAVA_OPTS="-Djava.awt.headless=true -Dfile.encoding=UTF-8
-server -Xms1536m -Xmx1536m
-XX:NewSize=256m -XX:MaxNewSize=256m -XX:PermSize=256m
-XX:MaxPermSize=256m -XX:+DisableExplicitGC"
Java Opts
-Xms
initial java heap size
-Xmx
maximum java heap size
-Xmn
the size of the heap for the young generation
-server starts Tomcat with the Server JVM
Subscribe to:
Posts (Atom)