Posts Tagged ‘Oracle’
09
Mar

To use a data source in WLS (e.g. to benefit from the built-in connection pooling, XA transactions etc.) you have to configure it from the WLS admin console.
Prior to this post, we installed the Oracle DB and Oracle WLS.

  1. start the admin console, e.g. http://localhost:7001/console
  2. in the left tree menu named ‘Domain Structure’ go to ‘Services’ -> ‘JDBC’ -> ‘Data Sources’
  3. click ‘New’ and enter your data:
    • name:     mh_JDBC_DS_orcl (just a unique name in WLS to identify your ds)
    • JNDI Name:     jdbc/mh_orcl
    • Database Type:    Oracle
    • Database Driver:     oracle.jdbc.xa.client.OracleXADataSource (use the XA version if your App participates in global/distributed transactions)
    • Database Name:     orcl (the name you specified in the db installation)
    • Host Name:     localhost (or your IP where the db listens)
    • Port:     1521 (port where the db listens)
    • Database User Name:  mh (user to login)
    • Password:     <pwd>
    • under ‘Targets’: check the server, e.g. ‘AdminServer’ where you want to use this ds
  4. You can explore your settings also on the file system:
    • C:\Oracle\Middleware\user_projects\domains\mh_domain\config\jdbc\mh_JDBC_DS_orcl-5723-jdbc.xml, and an entry to this file in
    • C:\Oracle\Middleware\user_projects\domains\mh_domain\config\config.xml

Alternatively, if you need to make multiple installations/configurations, there is the possibility to use the silent installation and WLST (WebLogic Scripting Tool).

Now you can call the ds in your application via JNDI or if you use JPA you need to specify the JNDI name in persistence.xml.

, , , ,

25
Jan

For the use of JSF you only need a servlet container (no full application server).

JSF 1.2, 2.0: Servlet 2.5 / JSP 2.1 container

JSF 1.2 and JSF 2.0 depend only on Servlet 2.5 / JSP 2.1 containers. Note that EL 2.1 is a component of the JSP 2.1 Specification. This is the case in JEE 5 AppServers:

  • Tomcat 6
  • Glassfish 2
  • JBoss AS 5
  • Oracle WebLogic 11g
  • IBM WebSphere 7, etc.

E.g. So you can use JSF 2.0 in Tomcat 6, but not the features of EL 2.2, like call JSF action method with arguments. To tune Tomcat 6 towards Java EE 6 see here.

JSF 2.1, 2.2: Servlet 3.0 / JSP 2.2 container

JSF 2.1 and JSF 2.2 depend on Servlet 3.0 / JSP 2.2 containers. Note that EL 2.2 is a component of the JSP 2.2 Specification. This is the case in JEE 6 AppServers:

  • Tomcat 7
  • Glassfish 3
  • JBoss AS 6
  • Oracle WebLogic 12g
  • IBM WebSphere 8
  • or newer versions

For an overview of JEE 6 compatible servers: http://www.oracle.com/technetwork/java/javaee/overview/compatibility-jsp-136984.html
What’s new in JSF 2.1? It’s mainly a maintenance release:
http://javaserverfaces.java.net/nonav/rlnotes/2.1.0/whatsnew.html
http://it-republik.de/jaxenter/news/Was-ist-neu-in-JSF-2.1-057653.html (German)

, , , , , , , , , , , ,

24
Jan

After installing the database, I’m getting the WebLogic Server 11gR1 (10.3.3). You have to accept again the same “OTN License Agreement”.

Installation of WLS

Download and unzip

1. At the download page of Oracle WebLogic Server  you can choose versions for different platforms (32/64 bit). Although I would prefer the 64 bit version I’m sticking to the 32 bit version, due to the easy Windows Installer (double-click exe, and follow a wizard…) – I don’t want to invest too much time in the installation.
I choose the full package with Oracle Coherence and Oracle Enterprise Pack for Eclipse, although I won’t need Coherence but want to check out the OEPE (there’s also a zip with AppServer only, but the readme reveals no exe-installation with manual configuration – unfortunately WLS installation isn’t as easy as JBoss, Glassfish: unzip, done). So download

  • wls1033_oepe111150_win32.exe (about 0,9 GB)

Optional download: Documentation package E14571_01.zip, if you want offline all the WLS 11gR1 oracle docs in html and pdf version.

exe-Installer

2. Double-click wls1033_oepe111150_win32.exe, and wait for preparation of installation, about 5 min.

3. Follow the wizard:

  • Create a “Middleware Home Directory”, I choose the suggested one C:\Oracle\Middleware (in C:\Oracle also lies the db)
  • choose “Typical” installation (vs. “Custom”)
  • accept the suggested installation directories:
  • WebLogic Server: C:\Oracle\Middleware\wlserver_10.3
  • Oracle Coherence: C:\Oracle\Middleware\coherence_3.5
  • Oracle Enterprise Pack for Eclipse: C:\Oracle\Middleware\oepe_11gR1PS2
  • Create shortcuts in start menu for “all users” as recommended
  • Check installation summary, click “Next” to start the actual installation, wait about 20 min., time for a coffee :-)

4. Check “Run Quickstart” and press “Done” to create and configure a WLS domain:

  • in Quickstart, select “Getting started with WebLogic Server 10.3.3″
  • next select “Create a new WebLogic domain”
  • Select to “Generate a domain configured automatically to support the following products:” and check JAX-RPC- and JAX-WS extensions
  • enter domain name and location: I choose “mh_domain” and C:\Oracle\Middleware\user_projects\domains
  • configure the administrator user name and password: I choose “admin” and some easy to remember pwd (it has to be longer than 8 chars)
  • choose “Development Mode” and Sun SDK for getting the user/pwd out if boot.properties and poll for apps to deploy (auto-deploy)
  • check no further configurations, inspect the summary and click “Next”, installation complete!

Summary

Starting and Stopping WLS

C:\Oracle\Middleware\user_projects\domains\mh_domain\bin\startWebLogic.cmd
C:\Oracle\Middleware\user_projects\domains\mh_domain\bin\stopWebLogic.cmd

Visit the admin server http://localhost:7001/console and log in as the admin as specified before.

, , ,

11
Jan

After installing the database, you may want to create a schema with tables, indexes, etc., to start your project.

Create User / Schema with Privileges

Log on as user SYSTEM (e.g. in sqldeveloper) and execute the script

-- clean SCHEMA (only if created earlier, for dev only!)
DROP USER mh CASCADE;

-- create
CREATE USER mh IDENTIFIED BY pwd
  DEFAULT TABLESPACE users -- if this line is omitted, the database default tablespace will be used; after installation it's 'USERS', to check it: SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE'; (if you want another existing tablespace: SELECT * FROM user_tablespaces;)
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users; -- such that you can insert data, you can also limit this

-- grant priviliges to create/alter/drop objects
GRANT
  CREATE SESSION, -- to log on; equivalent to the role 'CONNECT'
  CREATE TABLE,
  CREATE VIEW,
  CREATE PROCEDURE,
  CREATE TRIGGER,
  CREATE SEQUENCE
TO mh;

-- though different, but shorter (also see remarks below)
-- GRANT CONNECT, RESOURCE TO mh;

Remarks:

  • if you have more users, create a role, and grant the role to the users
  • DROP and ALTER the named objects are implicitly granted (there exist no such privilege DROP TABLE)
  • the role RESOURCE provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.
  • Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

Log off as SYSTEM, and log in as newly created user MH. Now you can create all objects with the user (fast setup for dev).

If you have more than one user, a better approach would be to run all scripts under an install user (SYSTEM would have enough priviliges ;-) ), but then don’t forget to name the objects fully qualified with schema names, e.g., CREATE schema.tablename (col1 numeric(20), …);.

Create Table with Primary Key and Foreign Key

We want to create a table with autoincrement primary key. This can be done in Oracle through a sequence and a “before insert” trigger. We additionally create a realistic environmet with foreign key and an index.

Suppose an already existing schema/user mh.

Note: A sequence does not guarantee a lückenlos Folge, i.e., we can get 1,2,3,7,10,11,…

CREATE TABLE mh.mh_table_2(
  id                 NUMERIC(20) PRIMARY KEY
);

CREATE TABLE mh.mh_table(
  id                 NUMERIC(20),
  name               VARCHAR2(255) NOT NULL,
  ts                 TIMESTAMP DEFAULT SYSTIMESTAMP,
  id_fk              NUMERIC(20),
  FOREIGN KEY (id_fk) REFERENCES mh.mh_table_2 (id),
  PRIMARY KEY (id)
);

A foreign key is not automatically indexed like a primary key! You have to declarate an index yourself, see below.

Create Sequence, Trigger

A sequence for thread-safely incrementing a value and trigger putting the autoincrement value of the sequence into mh_table.id when inserting row mh.mh_table.id:

CREATE SEQUENCE mh.mh_sequence
  START WITH 1
  MINVALUE 1
  -- MAXVALUE 9999999999999999999999999999 is default
  INCREMENT BY 1
  NOCYCLE -- is default: means after reaching the max, don't restart
  NOORDER -- is default: means requests are not put in order
  CACHE 100; -- is default with 20: number of next values that are hold in cache, for performance

CREATE OR REPLACE TRIGGER mh.mh_trigger
BEFORE INSERT ON mh.mh_table FOR EACH ROW
DECLARE
BEGIN
  -- if condition: when manually id is set, then do nothing (if this makes sense in your context)
  IF :NEW.id IS NULL THEN
    SELECT mh.mh_sequence.NEXTVAL INTO :NEW.id FROM dual;
  END IF;
END;
/

Don’t forget the slash at the end!

Create Index (unique, bitmap, function based)

The foreign key mh_table.id_fk may need an index, as well as mh_table.name may need a function based index (with the funny name FBI) to support performant search on the first letter:

CREATE INDEX mh.idx_mh_table_id_fk ON mh.mh_table (id_fk);
-- or if this column needs a unique constraint
CREATE UNIQUE INDEX ...;
-- or if this column only has a few (say 100) different values (like status ids)
CREATE BITMAP INDEX ...;

CREATE BITMAP INDEX mh.idx_mh_table_name_firstletter ON mh.mh_table (SUBSTR(name, 1,1));

The latter should contain only the 26 letters of the alphabet, hence we choose the BITMAP version (only available in Enterprise Edition).

Gather Table Statsitics

If this table would already have have data, you should gather table statistics (Oracle cost based optimizer (CBO) uses these),

parameter “cascade=>TRUE”, not only stats on columns, but also on indexes (it’s not clear to me, what it does in detail internaly)

exec dbms_stats.gather_table_stats(OWNNAME=>'MH', TABNAME=>'MH_TABLE', cascade=>TRUE);
-- or if you want to update only a certain index:
exec dbms_stats.gather_index_stats(OWNNAME=>'MH', INDNAME=>'IDX_[...]');

, , , ,

26
Nov

Another vm for dev, another installation of Oracle Database 11g Release 2 (11.2.0.1.0) Enterprise Edition, 64-bit version for Microsoft Windows (x64). This short description is not intended for production installation, but for local installation for dev purposes. You can download and use the database for dev purposes; excerpt from the “OTN License Agreement”, which you have to accept:

[...] LICENSE RIGHTS: We grant you a nonexclusive, nontransferable limited license to use the programs only for the purpose of developing, testing, prototyping and demonstrating your application, and not for any other purpose. [...]

Summary: Quite easy with the exe-installer, but you need time: about 2-3 hours if you know what you’re doing (download of the GBs and installation), if not longer :-)

Directions

At first you should check physical requirements: Hard drive 5.92 GB (+5GB for the installation zips and its extraction), RAM 1 GB minimum or 2 GB minimum for Windows 7 (for further details see the documentation)

Download and unzip

1. At the download page of Oracle Database, download the two parts

  • win64_11gR2_database_1of2.zip (about 1,1 GB)
  • win64_11gR2_database_2of2.zip (about 0,9 GB)

Optional downloads:

  • Documentation package E11882_01.zip, if you want offline all the db 11gR2 oracle docs in html and pdf version.
  • Interactive quick reference db11g-interactivequickref-187977.zip, for which you need a Flash Player. This gives a nice overview of dba views, architecture and background processes.
  • Examples package win64_11gR2_examples.zip
  • Client package win64_11gR2_client.zip
  • there are more downloads, e.g. Oracle Grid Infrastructure (necessary e.g. for Oracle Real Application Clusters (RAC)), or Web Tier Utilities (for HTTP access to the database via the Apache HTTP Server), etc.
  • sqldeveloper for sql-Development: sqldeveloper64-3.0.04.34-no-jre.zip

2. Unzip both files at the same directory level: I created C:\Oracle\db_11gR2\, put both zips there, and chose “Extract here” with 7-zip. This will produce one directory of name database.

exe-Installer

3. Double-click setup.exe to start the Installer

4. Follow the installation wizard, which will guide you straightforward through the installation.

  • Choose the desired installation: I choose database with configuration for a desktop (not server) for a fast setup, and the Enterprise Edition.
  • I change the Oracle base directory and accept the other suggestions:
    • Oracle Base directory: C:\Oracle
    • software directory: C:\Oracle\product\11.2.0\dbhome_1
    • directory of db file: C:\Oracle\oradata
    • global database name: e.g. orcl.[domain name]
    • administrative password for user SYS: for dev purposes you don’t have to follow the guidelines, it’s enough if it’s not empty and no user name.
  • You get a summary, which you can save as a file (good to have for later reference…). Check it, and hit “Finish” to begin the installation, which takes about 20-30 min., time to have a cup of coffee ;-)

5. After Completion you get a report:

  • Log files are under [Oracle base dir]\cfgtoollogs\dbca\orcl
  • global database name : orcl.[domain name]
  • SID (system id): orcl
  • name of server parameter file: [software dir]\database\spfileorcl.ora
  • db control URL (Enterprise Manager): https://localhost:1158/em

Database Users / Schemas

Admin Users/Schemas

6. After installation only the admin-users SYS, SYSTEM, DBSNMP, SYSMAN are not locked. Still in the installer or later (via OEM, sqlplus, or sqldeveloper), you can lock/unlock users and change the default passwords. In my dev environment I changed the one for SYSTEM to the same as for SYS. Later on I also change the password of SYSMAN to the same: Logon via sqlplus or sqldeveloper as a dba user, and issue the command: alter sysman identified by [pwd];

  • SYS: You have already changed the default pwd change_on_install for SYS with the Installer. It possesses the DBA role. All of the base tables and views for the database data dictionary are stored in the schema SYS. To maintain the integrity of the data dictionary, tables in the SYS schema should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS.
  • SYSTEM: default pwd is manager. It possesses the DBA role. SYSTEM is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.
  • SYSMAN: It is used to perform OEM administration tasks.
  • DBSNMP: default pwd is the same as its name. The management agent of OEM uses this account to monitor and manage the database.

Sample Users/Schemas

7. All other users are locked: Sample schema accounts (e.g. SCOTT, HR, OE, PM, SH, IX, BI, etc.); I unlock those, just to already have a few schemas and tables to experiment. There are scripts for resetting those under [software dir]\demo\schema, if you messed them up ;-)

Internal Users/Schemas

8. Internal accounts (individual Oracle Database features or components can have their own schemas); I don’t change anything.

Exploring OEM (Oracle Enterprise Manager)

. Now you can logon to OEM with an admin account https://localhost:1158/em

Windows Services

9. As you notice in the Windows task manager, some Windows Services are created. I’ll explain the three most important, to start up and shut down the db (alternatively you can use the Enterprise Manager):

  • OracleService[SID], which is the Oracle database instance.
  • Oracle[ORACLE_HOME]TNSListener, which is your listener. The listener is required for clients (e.g. sqlplus, sqldeveloper) to connect to your database.
  • OracleDBConsole[SID], which enables clients to connect to Database Control.

In our context SID=orcl and ORACLE_HOME=OraDb11g_home1, i.e., we have the services OracleServiceORCL, OracleOraDb11g_home1TNSListener, OracleDBConsoleorcl. Start or stop all three services through Start->Control Panel->Administrative Tools->Services: start/stop. You can deactivate the other services for dev purposes.

, , ,