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.

Data Model Analysis Styles

Data Models
- Conceptual: The highest-level beginning place
- Logical: The most important model for developing a good physical design
- Physical: The most detailed model that’s the source of the DDL to generate database objects
- Reverse engineered: A Physical model that shows the As-Is of a deployed database

Top-Down Modeling
A top-down approach to the modeling of a system will begin with a Conceptual model and work down through a Logical model to a Physical model for implementation. This process documents details regarding the conceptual entities that you begin with, breaking out attributes and relationships as you progress toward a Physical model.

Bottom-up Modeling
Sometimes you have to work with a bottom-up approach, working backward from an existing system, in order to develop a Logical model (and sometimes further back to a Conceptual model). This type of approach is reverse-engineering analysis.

Levels of detail
At each of these stages of analysis, you can choose the level of detail you display in the model you develop.

- Entity level: This is a high level of detail used for scope and planning or a simplified review at the beginning of a project. It shows only entities and attributes.
- Key-based: This is an intermediate level of detail, which includes entities and primary key attributes (usually natural keys if possible).
- Fully attributed: This is the most detailed level of analysis. All attributes are listed, including all foreign keys migrating from the parent table.

It’s important to use the appropriate type of model and level of detail and to tailor the model to the needs of your particular situation. You’ll consider the scope of projects in the next chapter.

The Right Model
Data models go through similar maturing and iteration processes as a full project. At the height of a project you’ll be moving rapidly between different models, from Conceptual to Logical to Physical and back to Logical, as you find out that there are exceptions to the business rules that the clients didn’t mention earlier.

Project Type
The type of project will help you narrow down the analysis level of the models. Enterprise-level projects, for instance, almost never need Physical models, but transactional OLTP projects could use all three analysis levels.

Model Deliverables for Different Project Types
Enterprise
Conceptual

Transactional
Conceptual, Logical, Physical

Dimensional or data warehouse
Conceptual, Physical

Model Goal
Your task helps you decide analysis level as well. If you want a high-level overview of the data system, or to play with new ideas or ways of organizing things, then you’ll need to build a Conceptual model. On the other hand, if you want to analyze business rules and analyze data elements, then you’ll need to build a Logical model. If you already know everything there is to know about the rules of the data elements and need to design tables, then you’ll want to build a Physical model.

Model Deliverables Expected by Model Goal
Abstract
Conceptual

Analysis
Conceptual, Logical

Physical design
Physical

Customer Need
Now you need to determine who will be your customer in order to decide what level of definition you need. If your customer is someone who wants a general rundown of the data involved in the project, then you want an entity-level model. If you’re working with someone who is concentrating on integration opportunities or key design choices, then you need a key-based view of the model. If the customer is someone who needs to see all the gory details, then you want a fully attributed model.

Model Level of Detail Deliverable Expectation by Customer Need
Overview
Entity

Focused identifier/uniqueness
Key based

All the details
Fully attributed

Choose one option from these three ways to narrow the definition of your product, and you’ll see what model you need to build. The following maps the combinations of ideas that focus the product definition of a model.

Deliverable Needs Combined to Focus on What Model Should Be Built
(Project Type, Model Goal, Customer View Need, Analysis Level, Level of Definition)
Enterprise
Abstract
Overview
Conceptual
Entity level

Transactional
Abstract
Overview
Conceptual
Entity level

Transactional
Abstract
Overview
Conceptual
Fully attributed

Transactional
Analysis
Overview
Logical
Entity level

Transactional
Data element analysis
Focused identifier/uniqueness
Logical
Key based

Transactional
Data element analysis
All the details
Logical
Fully attributed

Transactional
Physical design
Focused identifier/uniqueness
Physical
Key based

Transactional
Physical design
All the details
Physical
Fully attributed

Data warehouse/enterprise reporting
Abstract
Overview
Conceptual
Entity level

Data warehouse/enterprise reporting
Data element analysis
All the details
Logical
Fully attributed

Data warehouse/enterprise reporting
Physical design: normalized or dimensional
Focused identifier/uniqueness
Physical
Key based

Data warehouse/enterprise reporting
Physical design: normalized or dimensional
All the details
Physical
Fully attributed

Note: Remember that if the model doesn’t communicate what you’re trying to get across to your audience, then it’s useless.
These are still just high-level definitions of what’s on a model and the details that show up. You have an almost unlimited set of choices, especially if you’re using one of the modeling software packages. Making these choices just helps you narrow down what kind of model you’re building and therefore what your checklist of information is. You still need to be aware of the level of understanding (rather than what they want to know) of your model customer, the needs of the project, and any methodology or external standard.

Model Tips
The following is a list of things you should keep in mind when modeling:
- Picking the level of analysis and detail of a model depends on many factors.
- Staying within scope is a challenge.
- Being wrong on a model can be a good thing. It stimulates discussion.
- There is no “one right” model. The models serve different purposes. Not all of them are schematics for databases.
- Sometimes the “correct” solution isn’t the best solution for the enterprise.
- Models are products and have customers. Be customer oriented.
- Stay objective. Feedback for designs or analysis is meant to enhance, not criticize.
- Finding a way to keep the team in sync with each other at every model iteration requires a creative publish and deploy mechanism.
- Every model you build helps you build the next one.
- You’ll need a second pair of eyes to look for your mistakes. You can be too close to see them.
The primary goal of your work is to create a model that your audience can understand. The “perfectly designed” model may not be consumable by your customers. Also, work with your audience to encourage them to think of better ways of doing things; sometimes resistance isn’t because of a failure of design but because of the comfort level of your customers.

Building a Conceptual Model
Defining the Objectives – 20 – 80 rule
What are you trying to accomplish in building a Conceptual model? In this example, you’re going to draw a picture that portrays the data scope of a business process. You’re also going to refine and clarify all the individual descriptions of a process until it’s distilled into a single truth (or as close as you can get to it). To do that, you need to do the following:
- You should become informed enough to explain and defend the discoveries you make.
- You should learn the vocabulary of the process and its surroundings as the business sees it.
- You should identify the concepts or data elements, and determine the rules that govern them.
- You should validate the scope.
- You should build a Conceptual ER model.

Defining the Scope
Defining the Approach
- Top-Down Approach
- Bottom-Up Approach

Documenting the Process

Building the Conceptual Model

Building a Logical Model

Understanding OLAP Database Basics
The main difference between a data mart and a data warehouse is this: a data mart is intended to store a single snapshot of the business data, and the data warehouse will contain more than just a single point-in-time snapshot. A data mart is usually intended to be exposed to query tools or decision support applications and therefore usually follows a Dimensional model design. A data warehouse may have a Third Normal Form (3NF) design or a Dimensional model design, depending on its purpose within the enterprise. If a data warehouse has a 3NF design, it’s likely to feed dependent data marts and therefore is infrequently accessed. If the data warehouse has a Dimensional model design, it may be supporting an OLAP query tool or application directly. It’s important to note that the terms data mart and data warehouse generally describe a database’s use rather than imply a Physical model design.

Data marts generally have the following characteristics:
- They have a Dimensional model design.
- They’re accessed by OLAP (business intelligence) tools or an OLAP application.
- They contain a single point-in-time snapshot of data.
- They’re periodically refreshed from a source system (usually a data warehouse).
- They don’t have other dependent OLAP databases.

Data warehouses generally have the following characteristics:
- They have either a Dimensional model design or a normalized design.
They’re infrequently accessed by any process that isn’t an extraction, transformation, and loading (ETL) process, unless they have a Dimensional model design.
- They contain a time variant and historical view of the data.
- They’re periodically refreshed from a source system (usually source OLTP systems).
- They have dependent data marts.

Dimensional Modeling
It’s a methodology for modeling data that starts from a set of base measurement events and constructs a table called the fact table, generally with one record for each discrete measurement. This fact table is then surrounded by a set of dimension tables, describing precisely what’s known in the context of each measurement record. Because of the characteristic structure of a Dimensional model, it’s often called a star schema. Dimensional models are the logical foundation of all OLAP systems.
The key to a Dimensional model is in determining the relevant context (or face). A Dimensional model that’s designed to find trends in sales information will likely treat each sale as a fact. Any supporting information important to that analysis, such as the country of sale or the month of the year, will be a dimension.

Database Normalization

Normalization is a data modeling technique, the goal of which is to organize data elements in such a way that they’re stored in one place and one place only (with the exception of foreign keys, which are shared).

Universal properties
No duplicate members of the set.
Record order unimportant (top to bottom).
Attributes order unimportant (left to right).
All attribute values are atomic. No single attribute is allowed to hold more than one value at one time.

First Normal Form (1NF)
The appropriateness of the primary key.
Note: 1NF demands that every member of the set depends on the key, and no repeating groups are allowed.
(Car 1,2,3:Serial Number, Color Names, Make Name…) in one table; extract then to “Car Order” table

Second Normal Form (2NF)
The dependence of all attributes on all aspects of the primary key.
Note: 2NF demands that every aspect of every member of the set depends on the whole key.
In “Car Order” table (Order Number (FK), Car Serial Number, Car Color Name, Car Make Name etc.) it should be identified by one primary key.

Third Normal Form (3NF)
The dependence of any attribute on any attribute other than the primary key.
Note: 3NF demands that every data element of every member of the set depends on nothing but the key.
(Look at the Car data set. Can a Car Model Name of Camry have a Car Make Name of GM? No, only Toyota makes Camry. Can a Camry have a Car Year Number of 1975? No, the Toyota Camry wasn’t offered until 1983. Dependencies exist between the values of Make, Model, and Year of a Car. These dependencies create a set of true facts about cars that can be used repeatedly by many different members of the set of cars.)

Boyce-Codd Normal Form (BCNF)
Verifies that all data sets are identified and segregated.
Note: BCNF demands that every data element must be a fact about the data set whose members are identified by the key and that all keys are identified.

Fourth Normal Form (4NF)
Verifies that all attributes are single valued for a member of the set.

Fifth Normal Form (5NF)
Verifies that if the constituent parts of a data set were divided, they couldn’t be reconstructed.

Domain Key Normal Form (DKNF)
Verifies that all constraints are the logical consequence of the definition of the keys and the domains (data value rules).

Denormalization
Denormalization is performed after weighing the benefit it will bring to your application’s performance against a loss of relational integrity and ease of data value management.