Wednesday, November 26, 2008

Dumping Oracle schema

Here's the link to dump an Oracle schema:
http://www.tek-tips.com/viewthread.cfm?qid=1301609&page=34

Command
(sample format)exp buffer= compress= grants= feedback= consistent= file= log= owner= userid=/@

(example invocation with sample values)exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.TEST.dump log=TEST_Exp.log owner=TEST userid=TEST/TEST@dhunt

"buffer=" : give a large value, which speeds the export"compress=" : "Y" means 'allocate as an initial extent an extent size that accommodates the ENTIRE contents of the table'. "N" means 'use the standard "initial" and "next" parameters for the table. (I use "N" to avoid receiving the Oracle "Unable to allocate initial extent..." error.)"grants=" : "Y" means export the grant on this object; "N" means don't bother exporting grants. Use "N" if the other users that have grants to the source schema's objects in the source database do not exist in the target database."feedback=" : During the export, display to the screen, a dot (".") for every "n" records exported."consistent=" : "Y" means do not allow in-flight transactions to cause contents of tables to become inconsistent during the export."file=" : This is the name you give to the dump file. I usually use the format "..dump"."log=" : This is the name you give to the file that documents the progress (and errors, if applicable) that occur during the export. I usually use the format "_Exp.log"."owner=" : Name of source schema to export."userid=" : connect string for user officiating the export.

To import:
http://www.oracle.com/technology/products/text/x/Samples/Exp_Imp/index.html
Sample:
imp userid=samples/samples file=test.dmp show=n log=test.txt
imp sys/sys file=c:\dmp.dmp fromuser=user1 touser=userA

Sunday, April 27, 2008

starting Struts2

Compared with Struts1, Struts2 seems a little bit simplied regarding configuration files. However, there's something that needs our attention, which is the location of the struts.xml file.
This file should reside in the WEB-INF/classes directory instead of the WEB-INF directory.
It could also be put into a jar file in WEB-INF/lib directory.

Thursday, April 17, 2008

CheatSheet: Installing and Configuring Oracle 10g Database with WebSphere Message Broker (Windows Platform)

By default, webSphere Message Broker uses Derby or DB2 as the broker database; and how to configure Oracle database is not very clear in the documentation. In this cheatsheet, the step-by-step instruction will be given.

1. Create an Oracle table space (TS_WSBKR).
2. Create an Oracle user (WSBKR), and grant unlimited usage on TS_WSBKR to the user.
3. Grant database previlege to the broker database user:
GRANT CREATE SESSION TO WSBKR;
GRANT CREATE TABLE TO WSBKR;

4. Define a DSN for ODBC Connection
4.1 Go to "System DSN" definition tab though "ODBC data source administrator" in windows administrative tools or Oracle menu.
4.2 Create a new datasource using driver:
MQSeries DataDirect Technologies 5.00 32-BIT Sybase Wire Protocol
4.3 Enter the DSN name (DSN_WSBKR), description, and server name (in this case, use orcl, which is a sample Oracle instance), and use 10g client.
4.4 (Optional) In the Advanced tab, Select Enable SQLDescribeParam. Select Procedure Returns Results, which results in the Windows registry a string value called ProcedureRetResults with the value 1 being created.
4.5 Test the connection using WSBKR account and click "OK" button to create the DSN.
4.6 Start Windows regedit, and locate the following value:
HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
DSN_WSBKR

Add a String value to the key DSN_WSBKR, which is called "WorkArounds" with the value 536870912.
5. Create an MQ queue manager for the broker (WSBKR_QM).
6. Create a broker to populate the database. When you create a broker, if the WebSphere MQ queue manager does not already exist, the queue manager is automatically created. The broker database must already exist but the tables in which the broker stores its internal data are created automatically when the first broker to use that database is created. Subsequent brokers that you create specifying the same database and database user ID share these tables. An example is like this: C:\Program Files\IBM\MQSI\6.1>mqsicreatebroker WSMSGBROKER -i yelei -a pppassword -q WSBKR_QM -n DSN_WSBKR -u WSBKR -p pppassword.

Note the default broker creation wizard provided by websphere message broker toolkit only creates broker database. In order to store business data, user database should also be created.

Wednesday, April 16, 2008

resolving problem when starting oracle control console

When installing oracle 10g, oracle console cannot be configured and started properly on a multi-network adapter machine. Even after disabling some network adapters, using static IP address, reconfiguring listener properties, it still doesn't work.
The error looks like this:
Refer to the log file at
D:\Programs\oracle\product\10.2.0\db\cfgtoollogs\emca\orcl\emca_2008-04-16_04-18-02-PM.log
for more details.Apr 16, 2008 4:18:28 PM oracle.sysman.emcp.EMConfig
performCONFIG: Stack Trace: oracle.sysman.emcp.exception.EMConfigException:
Error creating the repository at
oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:194) at
oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:124) at
oracle.sysman.emcp.EMConfig.perform(EMConfig.java:142) at
oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479) at
oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123) at
oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463) at
oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)

Then the following commands in %ORACLE_HOME%/bin might help resolve the problem:
Stop you dbconsole service.
emctl status dbconsole
emctl stop dbconsole

Now start the Oracle EM dbconsole Build Script
emca -repos (re)create
emca -config dbcontrol db

Using Derby database with WebSphere Message Broker 6.1

WebSphere provides built-in commands for the usage of the embedded Derby database.
mqsicreatedb can be used to create database.
The first time a Derby database is created, a Windows service called IBM® MQSeries® Broker DatabaseInstanceMgr6 is created and started. This service is required in order to access Derby databases. This service can be started or stopped by the mqsistart and mqsistop commands, and automatically starts when Windows is started, if necessary. The service is deleted when the last Derby database is deleted. At most one Database Instance manager Windows service exists, even if you install WebSphere® Message Broker more than once on your Windows computer (multiple installed instances).
The database commands affect all the databases created in any installed instance on your Windows computer, regardless of the instance under which they are created. For example, the mqsilist DatabaseInstanceMgr6 command lists all the databases that have been created using the mqsicreatedb command on this Windows computer. Use the mqsichangedbimgr command to change the user name and password under which the Database Instance manager Windows service is run. Run this command only if passwords change or if user names are updated after the initial installation and configuration. For more information, see Using Derby databases on Windows.

Monday, April 14, 2008

resolving Maven download error

"mvn archetype:create ..." is almost the first command for everybody to run when starting to use Maven. Sometimes the following problem will be shown in the console:
...
[INFO] Repository 'central' will be blacklisted
[INFO]
------------------------------------------------------------------------
[ERROR] BUILD ERROR
...


This problem is usually related to the network settings of the local machine.
By adding a settings.xml file in the repository directory, it can resolve the repository downloading issue, and you'll be able to use Maven properly.
The instructions for settings.xml (to setup the proxy) is available in http://maven.apache.org/guides/mini/guide-proxies.html .

Wednesday, March 26, 2008

Starting Oracle database

In order to start Oracle instances hosted in Linux environments. There are 2 things need to be done.
1. Start the database instance
Use sqlplus to login without connecting to any database:
$ ./sqlplus /NOLOG
SQL > connect webm/webm
SQL > startup

2. Start up the database listener
$ ./lsnrctl start

Check the listener status
$./lsnrctl status

Input/Output Redirection and nohup

This article is from Input/Output redirection made simple in Linux .
Linux follows the philosophy that every thing is a file. For example, a keyboard, monitor, mouse, printer .... you name it and it is classified as a file in Linux. Each of these pieces of hardware have got unique file descriptors associated with it. Now this nomenclature has got its own advantages. The main one being you can use all the common command line tools you have in Linux to send, receive or manipulate data with these devices.
For example, my mouse has the file descriptor '/dev/input/mice' associated with it (yours may be different).
So if I want to see the output of the mouse on my screen, I just enter the command :
$ cat /dev/input/mice

... and then move the mouse to get characters on the terminal. Try it out yourselves.
Note: In some cases, running the above command will scramble your terminal display. In such an outcome, you can type the command :
$ reset
... to get it corrected.

Linux provides each program that is run on it access to three important files. They are standard input, standard output and standard error. And each of these special files (standard input, output and error) have got the file descriptors 0, 1 and 2 respectively. In the previous example, the utility 'cat' uses standard output which by default is the screen or the console to display the output.
  • Standard Input - 0
  • Standard Output - 1
  • Standard Error - 2
Redirecting output to other files

You can easily redirect input / output to any file other than the default one. This is achieved in Linux using input and output redirection symbols. These symbols are as follows:
> - Output redirection
< - Input redirection
Using a combination of these symbols and the standard file descriptors you can achieve complex redirection tasks quite easily.

Output Redirection
Suppose, I want to redirect the output of 'ls' to a text file instead of the console. This I achieve using the output redirection symbol as follows:
$ ls -l myfile.txt > test.txt
When you execute the above command, the output is redirected to a file by name test.txt. If the file 'test.txt' does not exist, then it is automatically created and the output of the command 'ls -l' is written to it. This is assuming that there is a file called myfile.txt existing in my current directory.

Now lets see what happens when we execute the same command after deleting the file myfile.txt.
$ rm myfile.txt
$ ls -l myfile.txt > test.txt
ls: myfile.txt: No such file or directory -- ERROR
What happens is that 'ls' does not find the file named myfile.txt
and displays an error on the console or terminal. Now here is the fun
part. You can also redirect the error generated above to another file
instead of displaying on the console by using a combination of error
file descriptor and output file redirection symbol as follows:
$ ls -l myfile.txt 2> test.txt
The thing to note in the above command is '2>' which can be read as - redirect the error (2) to the file test.txt.
When working with the UNIX operating system, there will be times when you will want to run commands that are immune to log outs or unplanned login session terminations. This is especially true for UNIX system administrators. The UNIX command for handling this job is the nohup (no hangup) command.

Normally when you log out, or your session terminates unexpectedly, the system will kill all processes you have started. Starting a command with nohup counters this by arranging for all stopped, running, and background jobs to ignore the SIGHUP signal.

The syntax for nohup is:

nohup command [arguments]
You may optionally add an ampersand to the end of the command line to run the job in the background:

nohup command [arguments] &

If you do not redirect output from a process kicked off with nohup, both standard output (stdout) and standard error (stderr) are sent to a file named nohup.out. This file will be created in $HOME (your home directory) if it cannot be created in the working directory. Real-time monitoring of what is being written to nohup.out can be accomplished with the "tail -f nohup.out" command.

Although the nohup command is extremely valuable to UNIX system administrators, it is also a must-know tool for others who run lengthy or critical processes on UNIX systems.

Thursday, March 20, 2008

view trigger in MS SQL

Try the following statements in Query Analyzer:
use ;
sp_helptext ;
This is used to see the trigger content.
select * from sysobjects where name=;
This is used to see the trigger properties.

Here's a list of trigger status code:
- a value of 1610615808 when an INSERT trigger is disabled
- a value of 1610615040 when a DELETE trigger is disabled
- a value of 1610615296 when an UPDATE trigger is disabled.

If you want to get the trigger name related to a table, you can use entreprise manager to check dependencies for that table.

In order to check if a trigger is disabled, you can use the following statement:
select name, objectproperty(id, 'ExecIsTriggerDisabled') as Disabled from sysobjects where xtype='tr';

In order to enable a trigger, use the following statement:
alter table enable trigger ;
or enable all triggers:
alter table enable trigger all;

Friday, March 07, 2008

Communicate with webSphere MQ using simple Java client

There are pieces of sample code available in the internet about how to communicate with MQ server using Java. However, you'll find out if you use MQ API, it will not work, always complaining about ClassDefNotFound, even after you include com.ibm.mq.jar and connector.jar in your classpath !!!
In order to resolve such a problem, you need to include com.ibm.mq.jar in the JVM bootstrap entries. It can be easily done using Eclipse!
To get more information about Java class loading mechanism. Here are a couple of interesting entries:
Internals of Java Class Loading
Understanding the Java Classloading Mechanism