Oracle ORM TopLink with Spring

Project: Spring_TopLink3
Description: Put TopLink work together with Spring Framework
IDE: Eclipse
JARs:
class12.jar
toplink.jar
spring.jar
commons-logging-1.1.1.jar
xercesImpl.jar
xml-apis.jar
serializer.jar
xsltc.jar
xalan.jar
xmlparserv2.jar

Spring Configuration


<bean id="toplinkSessionFactoryInitialiser" class="com.somellc.toplink.TopLinkSessionFactoryInitialiser"/><bean id="toplinkSessionCreation" factory-bean="toplinkSessionFactoryInitialiser" factory-method="createSession"> <constructor-arg type="java.lang.String" value="localSession"/></bean><bean id="toplinkSessionBean" class="com.somellc.toplink.TopLinkSessionBean"> <property name="sessionObject" ref="toplinkSessionCreation"/></bean>
<bean id="toplinkSessionObject" factory-bean="toplinkSessionBean" factory-method="getSessionObject"/><bean id="toplinkSessionEventManager" factory-bean="toplinkSessionBean" factory-method="getSessionEventManager"/><bean id="toplinkSessionProject" factory-bean="toplinkSessionBean" factory-method="getSessionProject"/>
<bean id="readAllQuerySimpleExpression" class="com.somellc.spring_toplink3.ReadAllQuerySimpleExpression"> <property name="mySessionObject" ref="toplinkSessionObject"/></bean>

Session.xml

<toplink-sessions version="10g Release 3 (10.1.3.0.0)" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance; <session xsi:type="database-session"> <name>localSession</name> <event-listener-classes/> <logging xsi:type="toplink-log"> <log-level>fine</log-level> </logging> <primary-project xsi:type="xml">EmployeeProject.xml</primary-project> <login xsi:type="database-login"> <platform-class>oracle.toplink.platform.database.oracle.Oracle10Platform</platform-class> <user-name>hr</user-name> <password>hr</password> <sequencing> <default-sequence xsi:type="table-sequence"> <name>Default</name> </default-sequence> </sequencing> <driver-class>oracle.jdbc.OracleDriver</driver-class> <connection-url>jdbc:oracle:thin:@wshi777:1521:orcl</connection-url> </login> </session></toplink-sessions>

IOCManager
public Object getBean(String fn, String beanId) {
Object obj;
try {
filename = fn;
context = new ClassPathXmlApplicationContext(fn);
BeanFactory factory = (BeanFactory) context;
obj = factory.getBean(beanId);
return obj;
}
catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getStackTrace());
return null;
}
}
public ClassPathXmlApplicationContext getContext(String fn) {
try {
filename = fn;
context = new ClassPathXmlApplicationContext(fn);
return context;
}
catch (Exception e) {
return null;
}
}

TopLinkSessionFactoryInitialiser.java
public DatabaseSession createSession(String sessionName) {
return (DatabaseSession)SessionManager.getManager().getSession(sessionName);
}

TopLinkSessionBean.java
public class TopLinkSessionBean {
protected static DatabaseSession sessionObject;
// @Required
public void setSessionObject(DatabaseSession sessionObj) {
this.sessionObject = sessionObj;
}
public DatabaseSession getSessionObject() {
return this.sessionObject;
}
public SessionEventManager getSessionEventManager() {
return (SessionEventManager)this.sessionObject.getEventManager();
}
public Project getSessionProject() {
return (Project)this.sessionObject.getProject();
}
}

ReadAllQuerySimpleExpression.java
// @Required
public void setMySessionObject(DatabaseSession sessionObject) {
this.mySessionObject = sessionObject;
}
/**
* Demonstration code using the Employee Demo classes.
* @param args java.lang.String[]
*/
public static void main(String[] args) {
IOCManager iocManager = IOCManager.getIOCManager();
ReadAllQuerySimpleExpression readAllQuerySimpleExpression = (ReadAllQuerySimpleExpression)iocManager.getBean("toplink3-context.xml", "readAllQuerySimpleExpression");
readAllQuerySimpleExpression.runExample();
}
public void runExample() {
log("\n\n\n###START: " + this.getClass().getName());
try {
Expression exp = new ExpressionBuilder().get("firstName").like("J%");
Vector employees = mySessionObject.readAllObjects(Employee.class, exp);
log("\tRead " + employees.size() + " employees with firstName like J%:");
log(employees);
} catch (Exception exception) {
exception.printStackTrace();
} finally {
mySessionObject.logout();
log("\n###END***: " + this.getClass().getName());
}
}

Oracle ORM - TopLink

Project: Oracle-TopLink
Description: Demo how TopLink can be used as ORM on Oracle 11g
IDE: Eclipse
JARs:
commons-logging-1.1.1.jar
toplink.jar
xmlparserv2.jar
classes12.jar

sessions.xml
<?xml version="1.0" encoding="UTF-8"?><toplink-sessions version="10g Release 3 (10.1.3.0.0)" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt; <session xsi:type="database-session"> <name>TwoTierEmployee</name> <event-listener-classes/> <logging xsi:type="toplink-log"> <log-level>fine</log-level> </logging> <primary-project xsi:type="xml">EmployeeProject.xml</primary-project> <login xsi:type="database-login"> <platform-class>oracle.toplink.platform.database.oracle.Oracle10Platform</platform-class> <user-name>username</user-name> <password>password</password> <sequencing> <default-sequence xsi:type="table-sequence"> <name>Default</name> </default-sequence> </sequencing> <driver-class>oracle.jdbc.OracleDriver</driver-class> <connection-url>jdbc:oracle:thin:@wshi999:1521:orcl</connection-url> </login> </session></toplink-sessions>

ReadAllQuerySimpleExpressionExample.java
/**
* Purpose: To demonstrate some common functions through example.
*/
public class ReadAllQuerySimpleExpressionExample extends Example {
/**
* Example of how all Employee objects can be read using a simple expression.
*/
public void run() {
Expression exp = new ExpressionBuilder().get("firstName").like("J%");
Vector employees = getSession().readAllObjects(Employee.class, exp);
log("\tRead " + employees.size() + " employees with firstName like J%:");
log(employees);
}
/**
* Demonstration code using the Employee Demo classes.
* @param args java.lang.String[]
*/
public static void main(String[] args) {
new ReadAllQuerySimpleExpressionExample().runExample();
}
}

Example.java
public void login() {
databaseSession = (DatabaseSession)SessionManager.getManager().getSession("TwoTierEmployee");
}
public void logout() {
if (databaseSession != null) {
databaseSession.logout();
}
}
public void runExample() {
log("\n\n\n###START: " + this.getClass().getName());
try {
login();
//databaseSession.getIdentityMapAccessor().initializeIdentityMaps();
} catch (DatabaseException loginException) {
System.out.println();
System.out.println("ERROR: Could not log in to database. Make sure your database server is running");
loginException.printStackTrace();
return;
}
try {
run();
} catch (Exception exception) {
exception.printStackTrace();
} finally {
logout();
log("\n###END: " + this.getClass().getName());
}
}

Spring Hibernate Example - Credit Rating

Project: Spring_Credit_Rating
Description: Make Spring and Hibernate work together
IDE: Eclipse
JARs:
commons-logging-1.1.1.jar
spring.jar (2.5.6)
cglib-nodep-2.1.3.jar
asm-attrs-1.5.3.jar
antlr-2.7.6.jar
asm-1.5.3.jar
ojdbc14.jar
commons-collections-2.1.1.jar
dom4j-1.6.1.jar
ehcache-1.2.3.jar
hibernate-3.2.5.ga.jar
jta.jar
xml-apis.jar
serializer.jar
xercesImpl.jar
xalan.jar
xsltc.jar
Spring Configuration

<bean id="createCreditCard" class="com.somellc.CreateCreditCardAccount"> <property name="creditRatingInterface"> <ref bean="creditRating" /> </property> <property name="creditLinkingInterface"> <ref bean="creditLinking" /> </property> <property name="emailInterface"> <ref bean="email" /> </property> </bean> <bean id="creditLinking" class="com.somellc.CreditLinking"> <property name="url"> <value>http://localhost/creditLinkService</value> </property> </bean> <bean id="creditRating" class="com.somellc.CreditRating"> </bean> <bean id="email" class="com.somellc.Email"> <property name="smtpHost"> <value>localhost</value> </property> <property name="fromEmail"> <value>mycompanyadmin@mycompanyadmin.com</value> </property> <property name="userId"> <value>myuserid</value> </property> <property name="password"> <value>mypassword</value> </property> </bean>
Hibernate Configuration
<hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="hibernate.connection.url">jdbc:oracle:thin:@wshi7:1521:orcl</property> <property name="hibernate.connection.username">hr</property> <property name="hibernate.connection.password">hr</property> <property name="default_schema">hr</property> <property name="connection.autocommit">false</property> <property name="show_sql">true</property> <property name="use_outer_join">false</property> <property name="hibernate.cache.use_second_level_cache">false</property> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <property name="current_session_context_class">thread</property> <property name="hibernate.connection.pool_size">10</property> <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property> <property name="hibernate.hbm2ddl.auto">update</property> <!-- Mapping files --> <mapping resource="Customer.hbm.xml"/>
</session-factory></hibernate-configuration>
Customer Table Configuration
<hibernate-mapping> <class name="com.somellc.NewCustomer" table="TAB_CUSTOMER"> <id name="customerId" type="long" column="CUSTOMER_ID" > <generator class="sequence"/> </id>
<property name="customerName" type="java.lang.String" column="CUSTOMER_NAME" update="true" insert="true"></property>
</class></hibernate-mapping>
Product Table Configuration
<hibernate-mapping> <class name="com.onstar.brm.begspring.Product" table="TAB_PROD"> <id name="productId" type="long" column="PROD_ID" > <generator class="sequence"/> </id>
<property name="productName" type="java.lang.String" column="PROD_NM" update="true" insert="true"></property> <property name="productDesc" type="java.lang.String" column="PROD_DESC" update="true" insert="true"></property>
</class></hibernate-mapping>

CreateCreditAccountClient.java
INewCustomer inewcustomer = new NewCustomer();inewcustomer.setCustomerName("Hibernate Example Customer");
IProductDAO iproductDAO1 = new ProductDAO(); iproductDAO1.createSession(); iproductDAO1.getProduct(63); //CreditCardAccountCreateCreditCardAccountInterface creditCardAccount = (CreateCreditCardAccountInterface)appContext.getBean("createCreditCard");creditCardAccount.createCreditCardAccount(icustomer);

CreateCreditCardAccount.java
public void createCreditCardAccount(ICustomer icustomer) throws Exception{
boolean crediRating = getCreditRatingInterface().getUserCreditHistoryInformation(icustomer);
icustomer.setCreditRating(crediRating);
//Good Rating
if(crediRating){
getCreditLinkingInterface().linkCreditBankAccount(icustomer);
}
getEmailInterface().sendEmail(icustomer);
}

CreateCreditCardAccountInterface.java
public interface CreateCreditCardAccountInterface {
public CreditLinkingInterface getCreditLinkingInterface();
public void setCreditLinkingInterface(
CreditLinkingInterface creditLinkingInterface);
public CreditRatingInterface getCreditRatingInterface();
public void setCreditRatingInterface(
CreditRatingInterface creditRatingInterface);
public EmailInterface getEmailInterface();
public void setEmailInterface(EmailInterface emailInterface);
public void createCreditCardAccount(ICustomer icustomer) throws Exception;
}

CreditLinking.java
public class CreditLinking implements CreditLinkingInterface {
private String url;
public void linkCreditBankAccount(ICustomer icustomer) throws Exception {
//Connect to URL
System.out.println("url to connect is" + url);
System.out.println("credit card linked for customer id " +icustomer.getCustomerId());
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
}

CustomerDAO.java
public class CustomerDAO implements ICustomerDAO{
private Session session = null;
public void createSession(){
Configuration cfg = new Configuration()
.configure("hibernate.cfg.xml");
SessionFactory sf = cfg.buildSessionFactory();
this.session = sf.getCurrentSession();
}
public INewCustomer create(INewCustomer inewCustomer) {
Transaction transaction = session.beginTransaction();
session.save(inewCustomer);
transaction.commit();
return inewCustomer;
}
}

Customer.java
public class Customer implements ICustomer {
// Existing Customer of bank
private String customerId;
// Customer Information
private String ssnId;
private String firstName;
private String lastName;
private String emailAddress;
private boolean creditRating;
// Address Information
private IAddress iAddress;
public String getCustomerId() {
return customerId;
}
public void setCustomerId(String customerId) {
this.customerId = customerId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public IAddress getIAddress() {
return iAddress;
}
public void setIAddress(IAddress address) {
iAddress = address;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getSsnId() {
return ssnId;
}
public void setSsnId(String ssnId) {
this.ssnId = ssnId;
}
public String getEmailAddress() {
return emailAddress;
}
public void setEmailAddress(String emailAddress) {
this.emailAddress = emailAddress;
}
public boolean isCreditRating() {
return creditRating;
}
public void setCreditRating(boolean creditRating) {
this.creditRating = creditRating;
}
}

Sending and Receiving Message Using Oracle AQ JMS

Environment:
Eclipse

aqapi.jar
javax.jms.jar
jta.jar
ojdbc14.jar

Project: OracleAQ_JMS2

I Sending Message
1. Get QueueConnectionFactory
- AQjmsFactory.getQueueConnectionFactory
2. Start QueueConnectionFactory
- qc.start();
3. Create QueurSession
- qc.createQueueSession
4. Get Destination Queue
- (AQjmsSession)m_queueSess).getQueue
5. Create Queue Sender
- m_queueSess.createSender
6. Construct Message
- m_queueSess.createTextMessage
7. Send Message
- m_sender.send
8. Queue Connection Stop and Close
-qc.stop();
-qc.close();

II Receiving Message
1. Get QueueConnectionFactory
- AQjmsFactory.getQueueConnectionFactory
2. Start QueueConnectionFactory
- qc.start();
3. Create QueurSession
- qc.createQueueSession
4. Get Source Queue
- (AQjmsSession)m_queueSess).getQueue
5. Create Queue Receiver
- m_queueSess.createReceiver
6. Receive Message
- m_receiver.receive
7. Queue Connection Stop and Close
-qc.stop(); -qc.close();

Oracle AS 10g

Solution Areas

- J2EE and Web Services

- Portals

- Wireless

- Business Intelligence

J2EE and Web Services Components

- Oracle HTTP Server (OHS)

- OracleAS Containers for J2EE (OC4J)

- OraxleAS Web Services

OHS added Modules

- mod_plsql

- mod_perl

- mod_fastcgi

- mod_oc4j (load-balancing support and transmits requests between OHS and OC4J)

- mod_oradav (file-distributed and database-distributed authoring and versioning)

- mod_ossl (SSL security with server and cryptography)

- mod_osso (Integrates OHS with Single Sign-On server)

Oracle AS Portal

Oracle AS Wireless

Business Intelligence

- OracleAS Reports Services

- OracleAS Forms Services

- OracleAS Discoverer (querying and reporting for data warehouses, data marts, and OLTP User Type:[Discoverer Viewer, Discoverer Plus])

- OracleAS Personalization (OP)

- Caching (Web Cache)

- Management & Security

1. Enterprise Manager (Web interface for managing all aspects of instances, farms, and clusters)

2. DCMCTL (Distributed Configuration Mgmt: command-line to facilitate configuration mgmt)

3. OPMNCTL (Oracle Process mgmt and Notification: monitor OracleAS processes. OPMN restarts those processes when necessary. Command-line interface for process management)

OracleAS Infrastructure (clustering) Categories

1. Identity Management Components

- Single Sign-On (SSO)

- Oracle Internet Directory (OID is LDAP service. Levels: anonymous, password-based, certification-based)

- Delegated Administration Service (DAS queries OID and returns the response to the OracleAS component)

- Oracle Certificate Authority (OCA - PKI solution)

2. Metadata Repository (an Oracle DB centralizes product, identity mgmt, and configuration metadata)

- Product (Contains components for OracleAS)

- Identity Management (components associated: OID, SSO, DAS, and OCA)

- Configuration Management (schemas for OracleAS instance configuration)

Products

- Oracle Application Server

- OracleAS Infrastructure

- OracleAS Developer Kits

Oracle Application Server Installation Types

- J2EE and Web Cache (smallest. OHS, OracleAS Web Cache, OC4J and Oracle Enterprise Manager Application Server Control)

- Portal and Wireless (OracleAS Portal and OracleAS Wireless, and all components from J2EE and Web Cache installation. OracleAS Infrastructure is required)

- Business Intelligence (OracleAS Personalization, OracleAS Discoverer, OracleAS Forms, OracleAS Reports, OracleAS Infrastructure is required)

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.

Install and Configure the Oracle 10g ODBC driver on Windows 2003 Server

Verify MDAC version 2.8 (use comcheck.exe).
If less than 2.8, install MDAC 2.8 by using Windows 2003 Server CDs.

Run CC_Pkg.exe
Install it in E:\Program Files\CompChecker
Run Component Checker
Make sure MDAC has version 2.8 and up
Setup Oracle ODBC and Provider for OLE DB drivers. Install or update TNSNAMES.ORA in E:\Program Files\\Network\Admin folder.

Install Oracle Client including ODBC and Provider for OLEDB
Open 10g_win32_client.zip

A. Run Setup.exe.

B. Oracle Universal installer screen.
Name: OraClient10g_hisrep
Path: E:\Oracle\Product\10.1.0\Client_hisrep
Chose “Administrator” option
Click ‘Next”

1. At Welcome Screen
-Leave ‘Typical’ button unchecked.

2. Select Naming Methods screen.
- Select ‘Local Naming’ option and > to ‘Selected Naming’ side.
- Click ‘Next’

3. Service Name screen.
- Enter sidname: hisrep
- Click ‘Next’

4. Select Protocols screen.
- Select TCP
-Click ‘Next”

5. TCP/IP Protocol screen.
- Host Name: hisreppldb.iweb.gm.com
- Keep ‘Use standard prot number of 1521.
- Click ‘Next”

6. Test screen.
- Keep ‘Yes, perform a test checked.
- Click ‘Next’

7. Net Service Name screen.
- Net Service Name: hisrep
- Click ‘Next’

8. Connect Test/Logon screen.
- Username: Medgate_v60_app
- Password: medgate60
- Click ‘OK’

9. Another Net Service Name? screen.
- Leave ‘No’ checked.
- Click ‘Next”

10. Net Service configuration Done screen.
- Click ‘Next’

11. Naming Methods configuration Done screen.
- Click ‘Next’

12. Done ‘OracleNet Configuration Complete’ screen.
- Click ‘Finish’

13. End of Installation screen.
- Click ‘Exit’
- Do you Really want to exit? prompt
- Click ‘Yes’

Verify ODBC driver.
Create new DSN source.
Go to Start->Administrative Tools->Data Source (ODBC)
Click “System DSN” Tab
Click “Add” button
Select the driver: “Oracle in OraDB10g_xxx”
Click “Finish”
Configure the Oracle ODBC Driver:
Data source name = <your_data_source>
Description = Oracle database
TNS Service name = <your_service_name>
UserID = <user_id>

TEST the database connection by click “Test Connection” button

Make sure it can establish connection. The connection will be used for other install components.

Oracle 8.1.6 Recovery Manager (RMAN)

Overview
•Command-line and Enterprise Manager-based tool
•Optimize performance & space consumption
•Integrate with Oracle Secure Backup
•Free dependency on OS and SQL*Plus

Functional Components
•Target DB to be backed up
•RMAN client
•A flash recovery area (disk location)

Backup and restore on Windows (32bit)
•Run run_rman.bat (can be added to schedule tasks list)
•Log File (C:\Database_Migration\RMAN\ORCL81_.log

RMAN – Files
•Run_rman.bat – main batch script
•run_sql.txt: - sql script for backup controlfile to trace
•rman_cmf.txt: rman script for backup database and old archived logs
•run_bak_ctl.txt: sql script for backup controlfile
•restore_steps.txt:steps for restore whole database from backup files

Steps to run scripts
•Step1. delete old backup files.
•Step2. create controlfile trace file and then copy it to the backup folder
•Step3. rum rman backup scripts
•Step4. using winrar compress the backup files (if winrar is available)

Parameters to be modified
•paths in run_rman.bat, rman_cmf.txt, run_bak_ctl.txt
•NowDate, Month, Day definition in run_rman.bat according to your windows system.
•winrar in run_rman.bat. You may need add the path of winrar to system path or use absolute path

If Error ORA-19602 is encountered
•ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
•C:\>SVRMGRL
•SVRMGR>connect internal
•SVRMGR>shutdown immediate
•SVRMGR>startup (if error, try ‘mount’)
•SVRMGR>alter database archivelog;
•
•SVRMGR>startup

Recover database
•Launch CMD screen (db is mount)
•C:>set oracle_sid=GEMSDB81
•C:>sqlplus /nolog
•SQL>connect / as sysdba

Oracle Application Server 10g Corporate Portal

Overview
•Browser-based environment for building, deploying, and maintaining enterprise portals
•Secure and manageable framework
•Organized and personalized views
•Self-service Web publishing
•Manageable deployment architecture

Oracle Application Server Components
•Oracle Internet Directory (OID)
•OracleAS Portal (OC4J)
•OracleAS Wireless
•OracleAS Web Cache
•OracleAS Personalization
•OracleAS Integration

Grid Computing

Architecture that pools large numbers of servers and storage into a less expensive, flexible, on-demand computing resource for all enterprise needs

- Standardize low cost components

- Consolidate shared resources

- Automate management operations

OracleAS 10g portal Solution
•Content management (Classify content, Navigate and access content; Route content for review and approval)
•Content display (Create, organize, and manage pages; Build and customize dynamic portlets
•Content integration (integrate applications and disparate data by using built-in functionality, including Web Clipplig, OmmiPortlet, and Portlet Builder

Major User Roles in OracleAS Portal

- Page designers

- Content contributors and content managers
- Portlet developers

- Portal administrator

Portal Page Modes

•Page group – Root page; Subpages
•View mode; Graphical mode; Layout mode; List mode

Adding Content to Portal
Item
•An item is a basic unit of content on a portal page.
•Two kinds –
•Content item type
•Navigation item type

Content item types
•File and Simple File
•Simple Image
•Image and Simple Image Map
•PL/SQL and Simple PL/SQL
•Prge Link and Simple Page Link
•Text and Simple Text
•URL and Simple URL
•Zip File

Item-Related Features
•Versioning
•Item-level security
•Document control
•Publishing dates
•Expiry dates
•Approvals

Adding Items

Accessing the Document Library by Using a WebDAV Client

With a WebDAV client, you can:

- Move content, files, and folders between your desktop and the document library

- Open, edit, and save file type items "in place" by using desktop application

Content Metadata
•Data about the content in the document library
•Set explicitly or implicitly
•Made up of three main components (Attributes; Categories; Perspectives)

Classifying Content in OracleAS Portal
•Category – A predefined attribute that is used to group or classify pages, items, and portlets

Creating Category

•Perspective – A cross-category grouping of items and pages (attributes)

- Further classify content across categories

- Enable users to view related content classified in different categories

Creating Perspectives

Implementing Custom Types

Custom Types
•Custom types are unique types you create to extende the standard type definitions provided by OracleAS Protal.
•Custom attributes – User-defined attributes based on predefined data types created to store additional info about an item. (used in definition of custom item types and page types)
•Custom item types
•Custom page types
•[Item Type]n-n[Attribute]n-n[page type]

Creating Custom Item Types

Approval Process

A Series of one or more approval routing steps

- Each step must have one or more approvers

- Routing to approvers can be in serial or in parallel

Portal Page

•OracleAS Protal object that contains portlets and items.
•A portal page is the face of the portal – that which the user interacts with to access informatipn and applications. The layout of a portal page is defined through regions
•A portal page combines the features of a directory folder and a browser page.Like a folder, a page can exist within a hierarchy of pages and can contain content.

Page Group

•A page group is a hierarchical collection of pages for which common attributes and mechanisms can be established to govern the behavior of the pages it contains.
•Consider – (Administering page groups; Managing content metadata; managing content presentation; Copying and moving content)
•Region – rectanglar area on a page used to define the page layout (Types: Item; Portlet; Sub-Page Links; Undefined)

Shared Objects

•Layout and appearance (Styles; Templates; Navigation pages)
•Content attribution (Custom page types; Custom item types; Custom attributes; Perspectives; Categories

Style

•Set of values and parameters that controls the colors and fonts that are used by pages and regions within a page

Page template
•An object that enforces an standard layout and appearance for multiple pages

Navigation Items

•Portal Smart Link

•Login/Logout Link

•Basic Search Box

•List of Objects

•Portal Smart Text

•Object Map Link

•Page Path

•Page Function

Page parameters

•Synchronize portlets residing on a page

•Enable the reuse of portlets on multiple pages with no additional coding

•Provide users the means to customize pages based on their input values

Portlet parameters

•Enable the portlet developer to declare a public data input interface for the page designer to use

•Give the page designer control over the input data to the portlet

Integrating Page and Portlet Parameters

Controlling Access to Page Groups

Controlling Access to Pages

Item-Level Privileges

•Manage

•Edit

•View

Accessing Portal Objects by Using Direct Access URLs

Web Clippling

A piece of existing Web content that can be repurposed in other Web pages, particularly portals.

OmniPortlet

A feature of Oracle AS Portal that enables you to quickly and easily publish data from various data sources and render the result in a variety of formats.

Supported data sources

•Spreadsheet

•SQL

•XML

•Web Service

•Web Page

Supported Data formates

•Tabular

•Chart

•News layout

•Bulleted list

•Form

Data-Driven Portlets (DB, SQL etc)

OracleAS Portal Forms

•Forms based on tables and views

•Master-detail forms based on two tables or views

•Forms based on stored procedures

OracleAS Poprtal Reports

Publishing Business Intelligence on a Portal Page

OrcleAS Discoverer provides two types of portlets

•List of Database Workbooks portlet: Contains the names and links to Discoverer workbooks

•Worksheets portlet: Enables you to place actual worksheetcontent on the portal page

Privileges

- Global privileges

- Objectr-level Privileges

- The corresponding global privilege overrides an object-level privilege

Install Oracle 8.1.6

Run Oracle 8.1.6 Enterprise Edition CD
- Select “Oracle8i Enterprise Edition 8.1.6.0.0
- Select “Typical (1001MB)”
- Database/SID (myDB/myDB)
- Message: “Database Creation Completed”
- Exit (log: C:\Program Files\Oracle\Inventory\Logs\InstallActions.log

Running Oracle 8.1.6 the first time
- Start->Programs->Oracle-orahome81->Application Development->SQL Plus
- User Name/Password (system/manager)
- Leave “Host string” (blank)
- What users are setup: select username from dba_users;
- CREATE USER &userid IDENTIFIED BY &password;
- GRANT CONNECT, RESOURCE TO &userid
- Log onto new userid: CONNECT &userid
- Alter user identified by

After restarting, if you get an ORA-01034: Oracle not Available error, it is because Oracle instance is not running. Do manual start as following
C:\>svrmgrl
SVRMGR>connect internal
SVRMGR>startup (shutdown immediate)
Change system, sys and ctxsys password:
SQL>alter user system/sys/ctxsys identified by password

Create first Oracle 8.1.6 Database
- Start->Programs->Oracle-orahome81-> Database Administration->Database Configuration Assistant
- "Create a database." ->"Custom“->"Multipurpose“->“10" concurrent users->"Dedicated Server Mode“->
- Global Database Name/SID (GEMSDB81/GEMSDB81)->Next
- Temporary/Rollback Tab (Size:150)
- Redo log size - 5000
- Checkpoint Interval – 10000 (default)
- Process (50)

If you encounter ora-01102 error
C:\>set oracle_sid=s1
C:\>svrmgrl
C:\>connect internal
C:\>shutdown immediate
C:\>exit

Net8 Assistant
- Start form menu
- Local->Service Naming->”+”
- Net Service Name: “GEMS816”->TCP/IP->Host Name “myDB”, Port Number: 1521->Service Name: “myDB”->Finish

Add listener
- Start from menu
- Select “Listener configuration”->Select “Add”->Listener Name: “myDB”->”TCP” selected->Use the standard port number of 1521->”No”

Delete a database
- Oracle Database Configuration Assessant->”Delete a database
- “OracleServicemyDB”

Start Oracle Listener
- Cmd Window
- C:>LSNRCTL
- LSNRCTL>start (stop)

Oracle 8.1.6 Exp / Imp (multiple dump files)

The table export is split into multiple dump files because there are too many records.

Export data from SOURCE DB
Make sure machine can be connected to both databases ( SOURCE and LOCAL)
Start SQL*PLUS session for SOURCE
Run:
SQL>@C:\Exp_Data\Exp_script.sql

Where Exp_script.sql is
---------------------------
/*
How to run under SQL*Plus:

SQL>@C:\Exp_Data\Exp_script.sql

**separated by "rec_creation_date"**

*/


host del C:\Exp_Data\ExpSpool.log
SPOOL C:\Exp_Data\\ExpSpool.log

--Cleanup existing dump files
host del C:\Exp_Data\record_in_year_2001.dmp
host del C:\Exp_Data\record_in_year_2002.dmp
host del C:\Exp_Data\record_in_year_2003.dmp
host del C:\Exp_Data\record_in_year_2004.dmp

--Cleanup existing log files
host del C:\Exp_Data\record_in_year_2001.log
host del C:\Exp_Data\record_in_year_2002.log
host del C:\Exp_Data\record_in_year_2003.log
host del C:\Exp_Data\record_in_year_2004.log


--create 2001 dump file
drop table .record_in_year_2001;
create table .record_in_year_2001 as select * from .table_name where rec_creation_date < to_date('01-jan-2002');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2001.dat
drop table .record_in_year_2001;


--create 2002 dump file
drop table .record_in_year_2002;
create table .record_in_year_2002 as select * from .table_name where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2002.dat
drop table .record_in_year_2002;

--create 2003 dump file
drop table .record_in_year_2003;
create table .record_in_year_2003 as select * from .table_name where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
host username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2003.dat
drop table .record_in_year_2003;

--create 2004 dump file
drop table .record_in_year_2004;
create table .record_in_year_2004 as select * from .table_name where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2004.dat
drop table .record_in_year_2004;


SPOOL OFF
---------------------------------
dat file example:
------------------------
CONSTRAINTS=N
DIRECT=Y
FEEDBACK=0
FILE=C:\Exp_Data\record_in_year_2001.dmp
GRANTS=N
INDEXES=N
LOG=C:\Exp_Data\record_in_year_2001.log
TRIGGERS=N
ROWS=Y
TABLES=.table2001
--------------------------

Import data into LOCAL database
Run “cmd” to get command prompt

Run following commands sequentially:
imp username/password file=record_in_year_2001.dmp full=yes
imp username/password file=record_in_year_2002.dmp full=yes
imp username/password file=record_in_year_2003.dmp full=yes
imp username/password file=record_in_year_2004.dmp full=yes

Note: If “ORACLE error 12560 encountered”, try to add the tnsname:
imp username/password@SOURCE file=<***>.dmp full=yes


Consolidate data into one table

Start SQL*PLUS session for local database (GEMSORCL)
Run following scripts:
create table .record_in_year_ as select * from .record_in_year_2001;
insert into .record_in_year_ select * from .record_in_year_2002;
insert into .record_in_year_ select * from .record_in_year_2003;
insert into .record_in_year_ select * from .record_in_year_2004;

drop table .record_in_year_2001;
drop table .record_in_year_2002;
drop table .record_in_year_2003;
drop table .record_in_year_2004;


Verify restored data against SOURCE DB
Start SQL*PLUS session for both databases (SOURCE and LOCAL)
Run following scripts for PE0047:
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
select count(*) from .record_in_year_ where rec_creation_date is NULL;
select count(*) from .record_in_year_;

Run following scripts for GEMSORCL:
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
select count(*) from .record_in_year_ where rec_creation_date is NULL;
select count(*) from .record_in_year_;

All counts should match accordingly.

Upgrade Oracle 10.2.0.1 to 10.2.0.4 (with january patch) for a single Instance Installation

Install 10.2.0.4 package

1. Download file p6810189_10204_Win32.zip. Unzip the package, all files are under “Disk1” directory. Read the patch_note.htm. Refer to patch_note.htm for detail steps.
2. patch_note.htm: Section 7.6.1 Stopping All Services for a Single Instance Installation
3. patch_note.htm: Section 8.1 Installing the Oracle Database 10g Patch Set Interactively
4. patch_note.htm: Section 9.3.2 Manually Upgrading a Release 10.2 Database

Install opatch
1. Download p6880880_102000_WINNT.zip and unzip it
2. Read opatch’s readme.txt file and follow the instruction


Install Oracle January critical Patch
1. Download p7584866_10204_Win32.zip
2. Unzip the file and all files are under folder 7584866
3. Follow Readme.html Please read through the whole document and make sure that you understand the process
4. Readme.html Section 3.3.2 Patch Installation Instructions for Single Instance
5. Readme.html Section 3.3.7.2 Recompiling Views in the Database

Oracle 10.2.0.4 Cold Backup and Restore

The cold backup is the easiest backup that could be performed.

It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.

It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.

Perform a cold backup

A cold back requires a clean shutdown of the database.

shutdown immediate or normal are the only shutdown allowed.

A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.

The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).

When the clean shutdown is done, copy the oradata folders to a new location.

After the files are copied, the database can be started up.

Recover from a cold backup

Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.

1) If the service doesn’t exist yet, issue the oradim command:

oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword

If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.

2) Copy the oradata folders to the exact same location from primary to the server to recover.

If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.

3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.

In the case a data file has been moved to new location, the database must be started in mount mode:

C:\set oracle_sid= mySID

C:\sqlplus / as sysdba

SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)

and a rename command must be issued for each files relocated:

SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;

SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;

SQL>alter database open;

Using Microsoft Access as Oracle Client

To connect to Oracle as you described above, you'll have to first create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.















Set up your ODBC connection.

In this example, we've setup the Data Source with a name of AAAA, with a user name of BBBB, and an Oracle server called CCCC. You'll need to configure the ODBC connection with your own settings.










Next, open your Access database, click on the Modules tab and create a new Module.

Paste in the following code:

Function OracleConnect() As Boolean

Dim ws As Workspace
Dim db As Database
Dim LConnect As String

On Error GoTo Err_Execute

'Use {Microsoft ODBC for Oracle} ODBC connection
LConnect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

'Point to the current workspace
Set ws = DBEngine.Workspaces(0)

'Connect to Oracle
Set db = ws.OpenDatabase("", False, True, LConnect)

db.Close

OracleConnect = True

Exit Function

Err_Execute:
MsgBox "Connecting to Oracle failed."
OracleConnect = False

End Function



Please note that you'll need to customize the following line of code:

LConnect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

So that:

AAAA is the name of the ODBC Data Source that you set up.
BBBB is the user name that you will use to log into Oracle.
CCCC is the name of your Oracle server.
DDDD is the password that you will use to log into Oracle



If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.

Which Oracle version 32bit or 64bit?

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

If you need to know if Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.

Rename or Move Oracle Tablespace Datafiles to another Location

Oracle database does not provide an easy user interface to rename a datafile of tablespace, nor database administrator can easily move or relocate the datafile to another location or directory that different from original location on creation of database. The rename or move place task has to be performed via Oracle SQLPlus command line interface. However, if the operation is performed when the tablespace which owns the datefile is online, error will occur.

The error message may include the following:

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file - file is in use or recovery
ORA-01110: data file : ‘datafile.dbf’

To properly move the datafile around or rename the datafile, follow this guide:

Login to SQLPlus.
Connect as SYS DBA with CONNECT / AS SYSDBA command.
Shutdown the database instance with SHUTDOWN command.
Rename or/and move the datafiles at operating system level.
Start Oracle database in mount state with STARTUP MOUNT command.
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘’ TO ‘’;

Open Oracle database instance completely with ALTER DATABASE OPEN command.
If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.

Login to SQLPlus.
Connect as SYS DBA with CONNECT / AS SYSDBA command.
Make offline the affected tablespace with ALTER TABLESPACE OFFLINE; command.
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER TABLESPACE RENAME DATAFILE ‘’ TO ‘’;

Bring the tablespace online again with ALTER TABLESPACE alter tablespace ONLINE; command.

Oracle 10.2.0.4 Enterprise Manager - Java.lang:exception error

Command: (Windows XP) Settings->Control Panel->Administrative Tools->Services

The Following services are started:

OracleDBConsolePE0047
OracleOraDB10g_homeiTNSListenerLISTENER2
OracleServicePE0047

Start Oracle Enterprise Manager and login
Error displays on the top:
java.lang.Exception: Exception in sending Request::null

First Try:
1. Stop OracleDBConsolePE0047
2. Open a Command window
3. C:\>set oracle_sid=PE0047
4. C:\>emctl start dbconsole
5. Run Oracle Enterprise Manager
Problem Still There

Second Try (from google search):
Maybe it is caused by agentTZtime?
1- Open the command prompt and typeset oracle_sid=(your DB name)
2- Then Type this commandemctl resetTZ agent
3- You will see this message that will tell you what to do :Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0Copyright � 1996, 2005 Oracle Corporation. All rights reserved.Updating D:\oracle\product\10.2.0\db_1/osama-pc_orcl/sysman/config/emd.properties...Time zone set to Africa/Cairo.
To complete this process, you must either:connect to the database served by this DBConsole as user 'sysman', and execute:SQL> exec mgmt_target.set_agent_tzrgn('osama-pc:3938','Africa/Cairo')-- or --connect to the database served by this DBConsole as user 'sys', and execute:SQL> alter session set current_schema = SYSMAN;SQL> exec mgmt_target.set_agent_tzrgn('osama-pc:3938','Africa/Cairo
4- You must restart the DBCONSOLE Service Or reboot your machine and you will find that the problem has gone.

ERROR:
While running mgmt_target.set_agent_tzrgn() through sysman account,I am getting error:
ERROR at line 1:ORA-20233: Invalid agent name sun220r.spiceindia.com:3872
ORA-06512: at "SYSMAN.MGMT_TARGET", line 3814
ORA-06512: at line 1

PROBLEM IS STILL THERE!!!


Analysis:
Commands used:
emctl status agent

Troubleshooting Agent Time Zone
This case happened to me on a Windows XP Professional in Mexican Spanish after a timezone change. I notice a similar error shows up on databases affected by the automatic time zone changes after synchronization with the time.windows.com, which recently has included two time zones for Mexico - one named old and the other named new-

Troubleshooting
0. Consider these files when performing the troubleshooting phase:
emd.properties
supportedtzs.lst

1. Stop the console
\bin\emctl stop dbconsole

2. Backup the file located at ORACLE_HOME/hostname_SID/sysman/config/emd.properties

3. Edit the emd.properties file and look for the agentTZRegion (generally appears at the end of the file)

At the file emd.properties, located at the ORACLE_HOME/hostname_instanceName/sysman/config

change the value

agentTZRegion=GMT To agentTZRegion=America/Mexico_City

Evidently, this is for the particular case of Mexico City, but similar issues could apply to other time zones.
For other timezones affected, such as the one corresponding to Egypt, change the value of this parameter from GMT+2 to Egypt which is included in the supportedtzs.lst file.
So the parameter will be agentTZRegion=Egypt

4. When agent is unable to find a proper time zone it will adopt GMT, so it could be the value registered so far. Change this value by the value corresponding to the OS time zone, this time zone should be one listed at the ORACLE_HOME/sysman/admin/nsupportedtzs.lst file.

5. Execute this command
emctl resetTZ agent
emctl config agent getTZ

After issuing the resetTZ command a similar issue like this one may appear:
To complete this process, you must either:

connect to the database served by this DBConsole as user 'sysman', and execute:

SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

-- or --

connect to the database served by this DBConsole as user 'sys', and execute:

SQL> alter session set current_schema = SYSMAN;
SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

In order for these commands to be successful, you are required the agent to have registered some values at the EM repository. Check this query, there should be similar information displayed when connected as sysman:
SQL> select target_name, target_type from mgmt_targets;

TARGET_NAME TARGET_TYPE
------------------------------------
pc06.oracle.com host
orcl.oracle.com oracle_database
pc06.oracle.com:3938 oracle_emd
Management Services and Repository oracle_emrep
LISTENER_pc06.oracle.com oracle_listener

6. The previous command will ask to perform some actions at the sysman repository level. Execute the reset at the repository level by:
SQL> alter session set current_schema = SYSMAN;

SQL> exec mgmt_target.set_agent_tzrgn('hostname:3938','TimeZone');

This command should be successful, otherwise it could be because the agent hasn't ever started and it has never registered, even with the wrong TZ at the repository.

In order for you to verify this has ever run and the agent is properly registered, issue this query as sysman:

SQL> select target_name, target_type from mgmt_targets;
The target with the default port 3938 is the target we are looking for.

The target related to the port 3938 should be listed. Otherwise try to start the agent so it can register this target. If agent is not starting, please veriy at the logs what could be a reason that prevents agent from starting. Most of the times it is because of a wrong specified

6. Try to login to the dbconsole and check if the error still exists.

7. Now start the EM Console, the problem should be fixed by now.


Notes.
The value of the time zone is a valid value listed at ORACLE_HOME/sysman/admin/nsupportedtzs.lst Make sure the time zone matches that of the host computer.

if when executing the emdctl command an error related to java appears, it is because the right java version is not being invoked. Make sure the path environment variable properly includes the jdk environment provided at the same Oracle Home where the database is related to.

path=%PATH%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin;C:\Oracle\product\10.2.0\db_1\jdk\bin
path=%path%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin\client



References.

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1 --> This note has particularly helped me to solve my particular issue.

Problem: Startup Agent: EM Agent will not start due to Timezone mismatch (Daylight Savings changes for Australia)
Doc ID: Note:362888.1 --> some useful ideas were taken from this note

Problem: Startup Agent: Agent Fails to Start due to Incorrect Timezone File Used as Pointed by ORA_TZFILE
Doc ID: Note:409121.1

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1