Friday, January 27, 2006

Creating an Oracle (XE) database manually on XP

There are two ways for creating an Oracle database, one is using Oracle Database Configuration Assistant and the other is manually, I'll provide a small step by step guide for the latter on windows XP.

This are the main steps:

1. Create the directory structure
2. Declare an oracle SID name.
3. Create a windows service and password file
4. Create the init.ora file
5. Start the instance in nomount mode
6. Use the Create database command
7. Create Data Dictionary

1. Create the directory structure

Under the admin
directory (C:\oraclexe\app\oracle\admin\) we have to create the structure for the new database (this is called OFA Oracle Flexible Arquitecture), the directory is called oraXE which will be the name of the database, and the directories adump, bdump, cdump, dpdump, pfile, udump are created in it:

2. Declare an oracle SID name.

Using the this command we declare the variable for oracle SID. Since could be more than one oracle instance running, oracle uses the SID to difference them.


3. Create a windows service and password file

Every instance on windows requires a windows service which can be created using oradim tool, the created service can be checked in the services list: type services.msc in the console or a link can be found in the control panel -> admin tools -> Services. Also a password file is created under database directory (ORACLE_HOME\database) with the SID embedded in the name (PWDoraXE.ora), this password file is used to authenticate the DBA before starting an instance.

oradim -new -sid %ORACLE_SID% -intpwd passwordhere -startmode M

Important : Remember to stop the default service for the "XE" database which is called "OracleServiceXE" and start the service just created "OracleServiceoraXE" for our new database in the services console.

4. Create the init.ora file
Open a text editor, add the lines below and save it as
initoraXE.ora in C:\oraclexe\app\oracle\admin\oraXE\pfile\. This are just some basic initial parameters, the ones that are not specified are taken by default.

control_files = (C:\oraclexe\oradata\oraXE\control01.ctl,
undo_management = auto
db_name = oraXE
db_block_size = 8192

control_files : Here we define where will be our control files, those are used for store management information of the database like location of the datafiles, timestamp of the creation of the database,etc. In this case there are three files copies, its recommended to store each copy on different physical disk which is called multiplexing.

undo_management: this parameter is used for choosing between automatic undo management (AUTO) or manually undo management (MANUAL, this is the default) in the latter is needed to create , size and monitor closely undo (rollback) segments, it's recommended to be set to AUTO.

db_name : The database name must be the same used in the create statement.

db_block_size : The default or standard database block size, this is the mininal logical unit of storage in oracle this should be a multiple of the operating system block size.

5. Start the instance in nomount mode

Before you try to connect to start the instance check that you have the right ORACLE_SID:


You can now authenticate through the operating system (no login or password) to start up the database, like this:

sqlplus /nolog

sql>connect / as sysdba

sql>startup nomount pfile=C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora

ORACLE instance started.

Total System Global Area 113246208 bytes
Fixed Size 1246556 bytes
Variable Size 58722980 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes

sqlplus /nolog : start the sqlplus client , the "/nolog" is to not be prompted for username/password.

connect / as sysdba : authenticate through the operating system with sysdba privileges. If you don't have the right ORACLE_SID ("oraXE") you may get an "ORA-12560: TNS:protocol adapter error".

startup nomount... : we must startup the db in nomount mode because at this point there a no database (physical files) to mount. The pfile that created in step 4 (C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora) must be specified otherwise it will look for the default location. If you get a ORA-01081 error is because probably the "XE" database is already started and you can't have two databases mounted at the same time, so first shutdown the running database using the shutdown command.

6. Use the Create database command

With this command we create the database:

sql> create database oraXE
logfile group 1 ('C:\oraclexe\oradata\oraXE\redo1.log') size 10M,
group 2 ('C:\oraclexe\oradata\oraXE\redo2.log') size 10M,
group 3 ('C:\oraclexe\oradata\oraXE\redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile 'C:\oraclexe\oradata\oraXE\system.dbf'
size 50M
autoextend on
next 10M maxsize unlimited
extent management local
sysaux datafile 'C:\oraclexe\oradata\oraXE\sysaux.dbf'
size 10M
autoextend on
next 10M
maxsize unlimited
undo tablespace undo
datafile 'C:\oraclexe\oradata\oraXE\undo.dbf'
size 10M
autoextend on
default temporary tablespace temp
tempfile 'C:\oraclexe\oradata\oraXE\temp.dbf'
size 10M
autoextend on;

Database created.

7. Create Data Dictionary

Finally, the scripts for creating the data dictionary are under ORACLE_HOME\rdbms\admin.


sql> @?\rdbms\admin\catalog.sql
sql> @?\rdbms\admin\catproc.sql

? this will be replaced by the value of $ORACLE_HOME
catalog.sql creates the data dictionary views.
catproc.sql creates the dictionary items necessary for PL/SQL functionality.

Wednesday, January 18, 2006

Firefox extension: IE Tab

For those of you who wants to open sites using internet explorer embedded in a firefox tab, this is the rigth extension. This is a relative of the IE view extension but the difference is that IE view extension opens new internet explorer windows for each selected site.

You have to be on windows to use it :S ...

Here is the link

Monday, January 02, 2006

Download Oracle XE with wget

I needed to download Oracle XE using wget mainly because of the limit rate option, but also because i can run it in the background and files can be resumed at any time. The limit rate option allows me to not use almost all the available Internet bandwidth, so i tried to download it but i noticed that you can't get the file directly from (this link is for windows platforms) without beeing authenticated, you must have a cookie if you already signed in, otherwise you will be forwarded to, where there is a form asking for username and password that post to sending this and other additional variables for authentication and finally redirection to the url that we came from (as you can se below in the redirectUrl variable) that in this case was the file to download.

If we look at the talk between browser and server when sending the form we can see the POST variables :

POST /jsp/reg/loginHandler.jsp HTTP/1.1
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
rv:1.7.12) Gecko/20050915 Firefox/1.0.7
Accept: text/xml,application/xml,application/xhtml+xml,
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
OracleXE.exeCookie: JSESSIONID=8d920d0fce90b20ff91eaec4cc2aa
OXgnkrDqRfMmlfDpQSxn6jAmljGr5XDqQLvpAe_;s_cc=true; s_sq=orac
Content-Type: application/x-www-form-urlencoded
Content-Length: 324


with that POST variables and URL i compose the following
wget command:

wget -v -c --limit-rate=3K --no-check-certificate --post-data="remoteIp=63.XXX.XX.XX&globalId=&redirectUrl=" -o oraxe.log &

-c , is used when you are resuming the download
--limit-rate=3K, limit to 3 Kbytes/sec
--no-check-certificate, it doesnt work verifying the server
-o oraxe.log, for output
& to run on background

At the end of the POST variables you can find "...username=camafla&password=xxxxx..." , you have to change
that with your personal information (also remoteIp=63.XXX.XX.XX).

The redirectUrl= variable may change but you just have to check with a browser whats the actual url.