Java Stored Procedure in Oracle Database

Tags

DB, Java, STORED PROCEDURE

Oracle database comes with its own Java Virtual Machine and since it has JVM we can run our Java Classes in the DB Server itself. Why would some one need to run java on DB server ? The best answer would be to have stored procedure in Java which can use JDBC to do some operation on database.

For more details on this subject please refer to http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm

In this blog I am taking to very very simple example to show how can we create a Java Stored Procedure and Run them.

So we start with a simple Java class ‘HelloWorld’ and also have a method which is public static [Required to make public static]

public class HelloWorld{        
public static void sayHello()
{
System.out.println("Hello World");
}
}

We are going to treat this method as our stored procedure. This is where we would like to put our jdbc code to manipulate DB as shown in the example  http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm

So now we have the Java Class ready to uploaded in Oracle Database. In $ORACLE_HOME/bin there loadjava tool, using which we can upload our java class into DB server.

[me@myhost]$ ./loadjava -u sys@myhost:1521:orcl-v -r -t HelloWorld.java
Password:
********
arguments: '-u' 'me@myhost:1521:orcl' '-v' '-r' '-t' 'HelloWorld.java'
creating : source HelloWorld
loading  : source HelloWorld
resolving: source HelloWorld
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

Now lets create package def and package body for our Java Stored Procedure so that it can be called as normal PL/SQL.

create or replace
PACKAGE hello as
PROCEDURE sayHello;
END hello;

PACKAGE HELLO compiled

create or replace
PACKAGE BODY hello AS
PROCEDURE sayHello as LANGUAGE JAVA
NAME 'HelloWorld.sayHello()';
end hello;

PACKAGE BODY HELLO compiled

And its time to call our store procedure and test.

SQL> SET SERVEROUTPUT ON;
SQL> CALL dbms_java.set_output(2000);
Call completed.
SQL> CALL hello.sayHello();
Hello World
Call completed.

Oracle Database Backup Simplified

You want to backup your Oracle Database and wandering how ? Here is a simple way to backup database. But before we start we need to decide if we want to backup database in Online mode of backup in Offline Mode.

Online Backup : Advantage of online backup is that database keeps running and backup can be done in parallel. But since database is running in parallel we also need to backup the archive logs and apply them while restoring the backup. Also Database needs to be in archive log mode before starting the backup, steps to put database in archive log mode.

Offline Backup : In offline backup database needs to be shutdown before we start the backup. Also we don’t need to backup archive logs since the database is not running and no additionally media recovery step while restoring database.

Database Backup Steps Online Mode
1. Create RMAN sql file

run{
allocate channel ch1 device type disk format '<your backup location>/%U';
backup database plus archivelog;
backup as copy current controlfile format '<your backup location>/cntrl01.ctl';
release channel ch1;
}

Allocating more channels increases the parallelism in backup process. So we can allocate more channels like

allocate channel ch1 device type disk format ‘<your backup location>/%U’
allocate channel ch2 device type disk format ‘<your backup location>/%U’
.
.
backup commands 
.
.
release channel ch1;
release channel ch2;

we can add a tag name to backup with

backup tag '<tagname>' database plus archivelog;

Also if we are backing up database in offline mode we can skip ‘plus archivelog’, and just say
backup tag ‘<tagname>’ database;

In case backup need to be compressed we can use ‘compressed backupset’  like following

backup tag 'mybkp' as compressed backupset database plus archivelog;

%U is specified to let rman decide the backup filenames. There are some more file name formats which can be specified. You can check oracle rman website for more on filenames. Also by specifying the location we are overriding the default backup location.

The next line is to backup the control file. It is good to copy control file specially if we are planning to restore database in a new environment. Same as database backup we add tag name to control file by following

backup tag '<control-file tag name>' as copy current controlfile format '<your backup location>/cntrl01.ctl';

So we have created following sql file /home/user1/MyBackup.sql

run{
allocate channel ch1 device type disk format '/u01/backup/mydb/%U';
backup tag database plus archivelog;
backup as copy current controlfile format '/u01/backup/mydb/cntrl01.ctl';
release channel ch1;
}

Now lets us run the sql file we have created.

1. Set env vars

$export ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome_1
$export ORACLE_SID=mydb

2. Connect target using rman client

$ORACLE_HOME/bin/rman target 
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jun 4 15:57:33 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MYDB (DBID=XXXXX)
/

This command will open rman prompt and connect the database whose SID is set in the ORACLE_SID.
Since we not specified the username and password, the current user need to be part of dba group.
Alternatively we can use username and password

$ORACLE_HOME/rman target sys/welcome@mydb

3. Run Backup SQL file

RMAN>/home/user1/MyBackup.sql

OUTPUT

RMAN> run{
2> allocate channel ch1 device type disk format '/u01/backup/mydb/%U';
3> backup database plus archivelog;
4> backup as copy current controlfile format '/u01/backup/mydb/cntrl01.ctl';
5> release channel ch1;
6> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=48 device type=DISK


Starting backup at 04-JUN-12
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set

channel ch1: starting piece 1 at 04-JUN-12
channel ch1: finished piece 1 at 04-JUN-12
piece handle=/u01/backup/mydb/06ncmve9_1_1 tag=TAG20120604T155903 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
Finished backup at 04-JUN-12

Starting backup at 04-JUN-12
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATADB/mydb/system01.dbf
input datafile file number=00002 name=+DATADB/mydb/sysaux01.dbf
input datafile file number=00003 name=+DATADB/mydb/undotbs01.dbf
input datafile file number=00004 name=+DATADB/mydb/users01.dbf
channel ch1: starting piece 1 at 04-JUN-12
channel ch1: finished piece 1 at 04-JUN-12
piece handle=/u01/backup/mydb/07ncmvgm_1_1 tag=TAG20120604T160022 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 04-JUN-12
channel ch1: finished piece 1 at 04-JUN-12
piece handle=/u01/backup/mydb/08ncmvhp_1_1 tag=TAG20120604T160022 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUN-12

Starting backup at 04-JUN-12
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 RECID=28 STAMP=785088060
channel ch1: starting piece 1 at 04-JUN-12
channel ch1: finished piece 1 at 04-JUN-12
piece handle=/u01/backup/mydb/09ncmvhs_1_1 tag=TAG20120604T160100 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JUN-12

Starting backup at 04-JUN-12
channel ch1: starting datafile copy
copying current control file
output file name=/u01/backup/mydb/cntrl01.ctl tag=TAG20120604T160101 RECID=2 STAMP=785088062
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-JUN-12

released channel: ch1

RMAN> **end-of-file**

Running this command will store backup in /u01/backup/mydb..as we can see here

$ls -l /u01/backup/mydb/
total 2176972
-rw-r----- 1 dbuser dba 1013575680 Jun  4 16:00 06ncmve9_1_1
-rw-r----- 1 dbuser dba 1193861120 Jun  4 16:00 07ncmvgm_1_1
-rw-r----- 1 dbuser dba    9830400 Jun  4 16:00 08ncmvhp_1_1
-rw-r----- 1 dbuser dba       4096 Jun  4 16:01 09ncmvhs_1_1
-rw-r----- 1 dbuser dba    9748480 Jun  4 16:01 cntrl01.ctl

How to put database in ARCHIVELOG mode

Tags

oracle home

Lets understand the Archive log and no archive log mode first. The database has a set of redo log which it keep on filling data as on transactions are taking place. Once these redo logs are filled, they are overwritten. If the database is in

ARCHIVE LOG MODE : The database will archive the redo logs before overwriting them. This help database to recover from unexpected failures.

NO ARCHIVE LOG MODE : The database will not bother about archiving redo logs and overwrite them. In this case even if the database was backed up, it can be restored but can not be recovered.

By default the database runs in NOARCHIVELOG mode. Following are the steps to change it to ARCHIVELOG mode.

1. Set ORACLE_HOME and ORACLE_SID environment vars

$setenv ORACLE_HOME /u01/app/product/11.2.0/dbhome_1

$setenv ORACLE_SID orcl

2. Connect to sqlplus as sysdba

$sqlplus / as sysdba

3. Shutdown the database

SQL>shutdown immediate;

4. Start database in mount mode

SQL>startup mount;

5. Change to archive log mode

SQL>alter database archivelog;

6. Open database

SQL>alter database open;

After this following statement can be used to check the archive mode of database:

SQL> select log_mode from v$database;

 LOG_MODE
 ------------
ARCHIVELOG

Delete Database Using DBCA

We often want to remove a database and create it again or make a way for new database. DBA often do this task, for creating new DB for Development team. Here is a simple way to delete a database using dbca.

1.Create Response File
Add following lines in response file

RESPONSEFILE_VERSION="11.1.0"
OPERATION_TYPE="deleteDatabase"
[DELETEDATABASE]
SOURCEDB="DEV_DB"
SYSDBAUSERNAME="sys"
SYSDBAPASSWORD="welcome"

Let’s say we saved this file as /mylocation/deleteDB.rsp

Response file version is version of you Database.
Operation Type is ‘deleteDatabase’ since we want to delete o
Source DB is name of the database which we want to delete
Sys user name is required to delete database
And last the syspassword

2.Set ORACLE_HOME

setenv ORACLE_HOME u02/app/product/11.2.0/dbhome_1

3.Set ORACLE_SID

setenv ORACLE_SID DEV_DB

4.Run the dbca command

$ORACLE_HOME/bin/dbca -deleteDatabase -silent -responseFile /mylocation/deleteDB.rsp

Running the command will show following logs :

Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/u02/app/cfgtoollogs/dbca/DEV_DB.log" for further details.

Notes:
This is tested with database 11.2.0.2
In case you dont want to keep password in response file, you can pass it on command line using -sysDBAPassword
E.g.

$ORACLE_HOME/bin/dbca -deleteDatabase -silent -responseFile /mylocation/deleteDB.rsp -sysDBAPassword mypassword

Oracle Single Instance Vs RAC Vs RAC One Node

Planning a new database installation for your Dev/Test/Prod environment ?

There are three types of deployment options you have :

• Oracle Single Instance (SI)
• Oracle Real Application Cluster (RAC)
• Oracle RAC One Node

Let me give you a overview of these different views of databases :

Single Instance(SI)  : SI is a simple and basic option of deploying oracle database. In single instance database there is only one instance of database running on one node. SI is simple to install and maintain. This could be a good choice for small production and development environments.

Oracle RAC Database : Oracle RAC database is a clustered database. It can have more than one instance running on one or more hosts. To make is database RAC , there is another software needs to be installed called Oracle Grid infrastructure which takes care of multinode sync, ASM, Oracle restart etc. Oracle GI is necessary as a base of Oracle RAC. In Oracle RAC, the load is balanced across multiple instance of databases running on different nodes. This is best for Production database. Failure of any instance is abstracted from user and forwarded to another instance from the database server pool. No of instances can be controlled using Policy Managed or Admin Managed.

Oracle RAC One Node : Some time we need to have RAC databases but donot need many instances, why? Simple answers to it is leverage the high availability of RAC. The Oracle RAC One node is a RAC DB runs on one host. The advantage is it saves your resource, can to converted to full RAC any time, Cluster fail over i.e. the Database instance is moved to new instance in case of any failure on host known as Instance Relocation,easy to upgrade etc. This type of database is well suited for small production and dev/test environments.