User Tools

Site Tools


software:oracle

A collection of tips for installing and working with Oracle databases.

Installation

Windows

At the time of writing, the most recent edition of Oracle database is 11g. It can be downloaded from here. Because downloading anything from Oracle's website is quite a pain, requiring account creation, logging on and prepetual acceptance of their licencing terms, the installer package for the 64bit Windows edition is available on our network drive (incoming\Appz\Database\Oracle).

Just unpack the zip files and run the installer script. The installation procedure is quite painless. I have chosen the “Standard Edition” of the database and accepted most of the default options. Do not forget the administrative password. It can be used for logging on as SYS, SYSTEM, SYSMAN and DBSNMP users.

Linux

A solid tutorial can be found here. Haven't completed it all the way through yet.

Configuration

Note: If you just care about accessing the database locally (from your own machine), you can skip this step.

If you want your database to be accessible to clients on other machines, you will need to configure the tsnames.ora and listener.ora files. These files are located in your <database installation folder>/network/admin. On my Windows installation, this is:

f:\Oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN

Here's what my files look like.

# tnsnames.ora Network Configuration File: F:\Oracle\product\11.2.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =

#  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.28.1)(PORT = 1521))

ORCL =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.28.1)(PORT = 1521))
#    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )  
# listener.ora Network Configuration File: F:\Oracle\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools. 

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.28.1)(PORT = 1521))
#      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = F:\Oracle

To check if everythig works, do a tnsping of your database from the command line:

$ tnsping orcl

Usage

Connecting with TOAD

Toad for Oracle is the tool used by people who know something about Oracle, so I guess using it is the right thing to do.

If you have binary compatible TOAD and Oracle 11g versions (i.e. both 32 bit), you should be ready to go. Simply start Toad and select your database from the TNS drop-down menu on the startup screen. If you don't see your database there, you'll need to install the Oracle Instant Client (also available on our network disk). On first connection, log on as the user SYSTEM, in order to create your regular user. Here's my Toad logon screen for initial logon:

Creating a new user

While logged on as SYSTEM, open the Editor window, enter the following script (of couse, feel free to use the username and password of your liking):

CREATE USER username IDENTIFIED BY password;
GRANT DBA TO username WITH ADMIN OPTION;
GRANT RESOURCE TO username WITH ADMIN OPTION;
GRANT CONNECT TO username WITH ADMIN OPTION;
ALTER USER username DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO username WITH ADMIN OPTION;

and execute it as a script through the “Editor→Execute as script” menu entry, by simply pressing F5. This will crate a rather powerful user, capable of creating new tables etc.

Once you have created the new user, disconnect the SYSTEM user and connect again as your newly created user.

Command-line connection from local machine

Changing the system password:

$ cd <database dir>/BIN
$ sqlplus / as sysdba
SQL> show user
SQL> passw system
  • [damjan] Migrate Oracle database
software/oracle.txt · Last modified: 2017/05/12 09:29 (external edit)