René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Creating an Oracle 8i database on NT from the command line only

A newer document covering Oracle 10g can be found here.
  • decide on your instance identifier
  • Create the initialization initSID.ora parameter file.
  • do the oradim thing:
    oradim -new -sid ADPDB -intpwd password -startmode manual [-pfile c:\oracle\ora81\database\initADPDB.ora]
    This command will create a service on NT named OracleServiceADPDB. The internal password will be set to "password". The startmode argument determines whether the OracleServiceADPDB service will startup automatically upon startup of the NT server. manual means that the service must be manually started. you can alternatively use auto instead of manual. The pfile argument should point to the init.ora file for the new instance.
    -sid is used to specify the name of the instance
    Follow this link if you want to know what oradim is.

    the -intpwd specifies the password for the INTERNAL account. The -INTPWD option is not required. If you do not specify it, operating system authentication is used, and no password is required. See "Automatically Enabling Operating System Authentication during Installation" for a description of features.
    -pfile is optional, if specified it creates a key in the registry named ora_adpdb_pfile

  • set ORACLE_SID
    c:\foo\bar SET ORACLE_SID=ADPDB
    or
    create a key/value pair in the registry. (key=ORACLE_SID, value=ADPDB)
  • start SQL*Plus and connect to your Oracle instance as SYSDBA.
        SQLPLUS /NOLOG
        CONNECT internal/password as SYSDBA
        
  • start an instance
        STARTUP PFILE=c:\oracle\ora81\admin\ADPDB\pfile\initADPDB.ora NOMOUNT
        
  • Create the database. Use the sql command CREATE DATABASE with its parameters. find a sample create database
  • run ?\rdbms\admin\catalog.sql, ?\rdbms\admin\catproc.sql, ?\rdbms\admin\caths.sql. SYSTEM should run ?\sqlplus\admin\pupbld.sql. catalog.sql creates the data dictionary view, catproc.sql creates oracle provided stored procedures like DBMS_OUTPUT. caths.sql (?) and pupbld.sql creates a table allowing you from block users from using sqlplus.
    This can be done using svrmgrl:
    c:\foo> svrmgrl
    svrmgrl> @%oracle_home%\rdbms\admin\catalog
    svrmgrl> @%oracle_home%\rdbms\admin\catproc

sample initSID.ora

db_name = ADPDB

db_files = 1020

control_files = (C:\ORANTdatabase\ctl1ADPDB.ora, C:\ORANT\database\ctl2ADPDB.ora)

db_file_multiblock_read_count = 16

# Number of buffers in the buffer cache.
db_block_buffers = 550

# Size in bytes of the shared pool
shared_pool_size = 9000000

#Name of another parameter file included for startup.
#ifile

log_checkpoint_interval = 8000

processes = 100

dml_locks = 200

log_buffer = 1M

sequence_cache_entries = 30

sequence_cache_hash_buckets = 23

#audit_trail = true
#timed_statistics = true

background_dump_dest = C:\ORANT\rdbms80\trace

user_dump_dest = C:\ORANT\rdbms80\trace

#size in bytes of oracle database blocks
db_block_size = 2048

compatible = 8.0.3.0.0

sort_area_size = 65536

log_checkpoint_timeout = 0

#Enable or disable automatic archiving if the database is in ARCHIVELOG mode.
#log_archive_start

#Default filename format used for archived logs. 
#LOG_ARCHIVE_FORMAT 

#Location of archived redo log files.
#log_archive_dest

remote_login_passwordfile = shared

#Maximum size in OS blocks of the trace files
max_dump_file_size = 10240

#Rollback segments allocated to this instance. Refer to the Oracle8 tuning
#manual for information and guidelines on determining the number and size of
#rollback segments based on the anticipated number of concurrent transactions.
#rollback_segments

sample create database

CREATE DATABASE ADPDB
    CONTROLFILE REUSE
    LOGFILE  'c:\oracle\ora81\admin\ADPDB\redo01.log'   SIZE 1M REUSE,
             'c:\oracle\ora81\admin\ADPDB\redo02.log'   SIZE 1M REUSE,
             'c:\oracle\ora81\admin\ADPDB\redo03.log'   SIZE 1M REUSE,
             'c:\oracle\ora81\admin\ADPDB\redo04.log'   SIZE 1M REUSE
    DATAFILE 'c:\oracle\ora81\admin\ADPDB\system01.dbf' SIZE 10M REUSE 
      AUTOEXTEND ON
      NEXT 10M MAXSIZE 200M 
    ARCHIVELOG
    CHARACTER SET WE8ISO8859P1
    NATIONAL CHARACTER SET UTF8;

-- Create another (temporary) rollback segment in the system talbespace
CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);

-- Alter temporary rollback segment online before proceding
ALTER ROLLBACK SEGMENT rb_temp ONLINE;

-- Create additional tablespaces ...
-- RBS: For rollback segments
-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
CREATE TABLESPACE rbs
    DATAFILE 'c:\oracle\ora81\admin\ADPDB\rbs01.dbf' SIZE 5M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE users
    DATAFILE 'c:\oracle\ora81\admin\ADPDB\users01.dbf' SIZE 3M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE temp
    DATAFILE 'c:\oracle\ora81\admin\ADPDB\temp01.dbf' SIZE 2M REUSE AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

-- Create rollback segments.  
CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;

-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;

ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;
Newly added users should get a quota on the tablespace users and rbs:
alter user rene quota unlimited on users
alter user rene quata unlimited on rbs
Or, alternativly, grant resource to the user
grant resource to rene

oradim

ORADIM is a command line tool that is only available with the Oracle8i database.
You only need to use ORADIM if you are manually creating, deleting, or modifying databases.
It
  • Can create, start, stop, modify, and delete instances (and not any associated database files) at the MS-DOS command prompt.
  • Can be used to modify an instance. You can modify an existing instance to change such values as the instance name, the password, the startup mode, or the shutdown mode.
  • Only creates the password file and the related service. The database (that is, the database files) is not created.