Oracle Database

Oracle Forms And Html:
A short comment the differences between Oracle Forms and Oracle Html DB, which are both used with Oracle 10g.

Oracle Forms is a rapid development tool as is HTML DB, however from a reporting side, HTML DB allows users with limited programming skills to create complex and graphical reports using wizards which are available through HTML DB (some setup may still be required from a development side before this ability is available to the user). Oracle Forms offers you the ability to have more control over the application you are developing. You can create the layouts you want for screens, with HTML DB you use pre-defined templates for the screen layouts, but these still allow you to be flexible with the report layouts.

Conclusion
Oracle HTML DB a feature of Oracle Database 10g, is a rapid application development tool. Thanks to built-in features such as design themes, navigational controls, form handlers, and flexible reports, Oracle HTML DB accelerates the application development process.
Using only a web browser, you can develop and deploy professional looking applications that are both fast and secure, which allows concurrent updates by multiple users as well as providing real time access to a single source of information.

New applications can be launched and updated without the need to distribute software, everyone has access to the latest information from any computer, and users jump almost seamlessly from one application to another, as long as they have a web browser on their desktop.

The Oracle database is centrally deployed and managed, with the ability to host many users on a single instance of the database. This can be accessed through HTML DB, anywhere via a browser.

For Oracle HTML DB to be effective, all the data in the database needs to be up-to-date. As this data will be shared with other users, reports will become inaccurate, if data is not updated by end users.
Oracle 10g Database Creation Steps
DATABASE CREATION STEPS in Linux: After Performing the oracle software only Installation.The below are the steps for creating a database .

Password file creation
======================

[oracle@Nalanta dbs]$ orapwd file=orapwtritya password=specialone entries=5

Directory creations
===================

[oracle@Nalanta dbs]$ mkdir -p /usr1/oracle/oradata/controlfiles/
[oracle@Nalanta dbs]$ mkdir -p /usr1/oracle/oradata/datafiles/
[oracle@Nalanta dbs]$ mkdir -p /usr1/oracle/oradata/logfiles/
[oracle@Nalanta dbs]$ mkdir -p /usr1/oracle/oradata/admin/{bdump,cdump,udump}

Init file for the tritya database
==================================

[oracle@Nalanta dbs]$ cat inittritya.ora
*.control_files=’/usr1/oracle/oradata/controlfiles/control01.ctl’,'/usr1/oracle/oradata/controlfiles/control02.ctl’,'/usr1/oracle/oradata/controlfiles/control03.ctl’
*.core_dump_dest=’/usr1/oracle/oradata/admin/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’tritya’
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile=EXCLUSIVE
*.log_archive_format=’tritya_arch_%t_%s_%r.arc’
log_Archive_dest=’/usr1/oracle/tritya/arch’
*.shared_pool_size=83886080
*.sort_area_size=65536
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’
*.user_dump_dest=’/usr1/oracle/oradata/admin/udump’
[oracle@tritya dbs]$

[oracle@tritya script]$ cat createdb.sql
connect / as sysdba;
startup nomount;
CREATE DATABASE “tritya”
DATAFILE
‘/usr1/oracle/oradata/datafiles/system01.dbf’ size 500M
SYSAUX DATAFILE
‘/usr1/oracle/oradata/datafiles/sysaux01.dbf’ size 500M
UNDO TABLESPACE undotbs DATAFILE
‘/usr1/oracle/oradata/datafiles/undo01.dbf’ size 250M
LOGFILE
‘/usr1/oracle/oradata/logfiles/redo01.log’ size 10M,
‘/usr1/oracle/oradata/logfiles/redo02.log’ size 10M
CHARACTER SET “WE8ISO8859P1″
NATIONAL CHARACTER SET “UTF8″
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE mytemp TEMPFILE
‘/usr1/oracle/oradata/datafiles/temp01.dbf’ size 500m
ARCHIVELOG
MAXDATAFILES 1000
MAXLOGFILES 10;

SQL> @createdb
Connected to an idle instance.
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 1218340 bytes
Variable Size 142608604 bytes
Database Buffers 25165824 bytes
Redo Buffers 2973696 bytes

Database created.

SQL> !cat postdb.sql
@/home/oracle/product/10.2.0.1/rdbms/admin/catalog.sql
@/home/oracle/product/10.2.0.1/rdbms/admin/catproc.sql

SQL>@postdb.sql

………..
…………

Completed

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 1218340 bytes
Variable Size 142608604 bytes
Database Buffers 25165824 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 tritya
tritya
10.2.0.1.0 18-MAY-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /usr1/oracle/tritya/arch
Oldest online log sequence 41
Next log sequence to archive 42
Current log sequence 42
SQL> show sga

Total System Global Area 171966464 bytes
Fixed Size 1218340 bytes
Variable Size 142608604 bytes
Database Buffers 25165824 bytes
Redo Buffers 2973696 bytes
SQL>
Mannual Creation Of Oracle Database
Manual Creation of database in windows with oracle 9i (Step-by-Step)
(Name of the database=db18)
(Note: all commands are in bold letters)
1. Open the command prompt and execute the command sqlplus/nolog
2. Connect to the default database as sysdba in sql prompt SQL>conn sys/oracle as
sysdba you can see the name of that database by executing select name from
v$database;
3. Now open another command prompt and set oracle SID as set oracle_sid=db18
4. Start a windows service with internal password oradim –new –sid
intpwd is the syntax. In this case I use ceylonlinux_suranga as password
to create db18 service like, oradim –new –sid db18 –intpwd ceylonlinux_suranga
5. Create a directory called db18. In my case I created it in d:\ drive (Note: all my
parameter files and .sql file that are going to discuss following are based on my
location, you can change the location according to yours)
6. Here is my initdb18.ora that I saved it in d:\db18 folder. This is the static parameter
file that I used in my database creation (Note: If you are creating a database with a
different name and in a different location make sure to edit the relevant fields in this
file)
###########################################################################
###
# Copyright © 1991, 2001, 2002 by Oracle Corporation
###########################################################################
###
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=db18
###########################################
# Diagnostics and Statistics
###########################################
2
background_dump_dest=d:\db18
core_dump_dest=d:\db18
timed_statistics=TRUE
user_dump_dest=d:\db18
###########################################
# File Configuration
###########################################
control_files=("d:\db18\control01.ctl", "d:\db18\CONTROL02.ctl",
"d:\db18\CONTROL03.ctl")
###########################################
# Instance Identification
###########################################
instance_name=db18
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orcl1XDB)"
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Pools
###########################################
java_pool_size=33554432
large_pool_size=8388608
shared_pool_size=50331648
###########################################
# Processes and Sessions
###########################################
processes=150
3
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=1
undo_tablespace=UNDOTBS
7. Now type following in your current command prompt sqlplus/nolog and in sql
prompt type conn sys/ceylonlinux_suranga as sysdba then you should see that you
are connected to an idle instance
8. Now start the instance in nomount mode as, startup nomount
pfile=d:\db18\initdb18.ora why are you starting the database in nomount mode ?
The reason is still we are not created control files. “An instance would be started in
the NOMOUNT stage only during database creation or the re-creation of control files.
9. This step is to create the database using dbca.sql script that I saved in d:\db18 folder
appears follows
CREATE DATABASE db18
LOGFILE GROUP 1('d:\db18\redo01.log') SIZE 100M,
GROUP 2('d:\db18\redo02.log') SIZE 100M,
GROUP 3('d:\db18\redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'd:\db18\system01.dbf' SIZE 325M
UNDO TABLESPACE UNDOTBS
DATAFILE 'd:\db18\UNDOTBS.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
4
10. Run the script in the SQL prompt as this, SQL>@d:\db18\dbca.sql once you run this
script you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created in d:\db18 folder.
11. Now you can shutdown the database using shutdown command.
12. Once the database shutdown reboot your PC
13. Connect again as sysdba to default database sqlplus/nolog, SQL>conn sys/oracle as
sysdba check which database you are in.
14. If it is not db18 set oracle sid as we did before in another command prompt as set
oracle_sid=db18
15. If db18 windows service is not started start it manually or execute this oradim –
STARTUP –sid db18 –intpwd ceylonlinux_suranga
16. Now connect to the database sqlplus “sys/ceylonlinux_suranga as sysdba”
17. startup pfile=d:\db18\initdb18.ora (Note: Here we don’t need to start the database
in nomount mode because we have already created control files)
18. Execute catalog.sql SQL>@d:\ORANT\rdbms\admin\catalog.sql
19. Execute catproc.sql SQL>@d:\ORANT\rdbms\admin\catproc.sql
Note: if the password file is corrupted or if you get an error in authentication you can recreate
the password file as follows, but make sure to delete the existing password file.
C:>orapwd file=d:\ORANT\database \PWDdb18.ORA password=ceylonlinux_suranga
This is what you need to do every time when you start your database…..
C:\Documents and Settings\qq>set oracle_sid=db18
C:\Documents and Settings\qq>oradim -STARTUP -sid db18 -intpwd
ceylonlinux_suranga
ORA-01078: failure in processing system parameters
LRM-00109: could no t open parameter file 'D:\ORANT\DATABASE\INITDB18.ORA'
C:\Documents and Settings\qq>sqlplus "sys/ceylonlinux_suranga as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Dec 6 21:22:02 2004
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=d:\db18\initdb18.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
5
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
Now you need to edit the following files
· D:\ORANT\network\admin\tnsnames.ora
· D:\ORANT\network\admin\listener.ora
Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:\ORANT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SURANGA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = suranga)
)
)
db18 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db18)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
6
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
# LISTENER.ORA Network Configuration File: D:\ORANT\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ORANT)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = suranga)
(ORACLE_HOME = D:\ORANT)
(SID_NAME = suranga)
)
(SID_DESC =
(GLOBAL_DBNAME = db18)
(ORACLE_HOME = D:\db18)
(SID_NAME = db18)
)
)
Now start OEM console and click “Add Database To Tree” under “Navigator” menu item.
From the window select second radio button saying “Add selected databases from your local
tnsnames.ora file”
From there select db18. Once you select it you should see OEM console as follows