Database Manual Creation. 1

[ofsouto]

How can I create an Oracle 8i release 8.1.7 Database on NT manually?
Thanks in advance.

 

[bdraper]

I have included scripts called crdb1.sql and crdb2.sql. These scripts should allow you to create a base database, which you can then login to and start adding new tablespace and data. You will need to modify to suite your own environment.

You will need to modify your listener.ora and tnsnames.ora for the new database.
At a dos prompt make sure that your ORACLE_HOME is set.

set ORACLE_SID=DBTEST

Create the Oracle database service by using the oracle command at the dos prompt.
oradim -new -sid DBTEST -intpwd password -startmode auto -pfile E:\apps\oracle\product\ORA81\Database\initDBTEST.ora

To delete the service, if you need to later use
oradim -delete -sid DBTEST

Then use svrmgrl to create the database
svrmgrl
connect internal
password

Then call the following file by @crdb1.sql at the svrmgrl prompt

REM NAME
REM crdb1.sql
REM
REM FUNCTION
REM Create the DBTEST Database on the Server X
REM
REM HISTORY Billy Draper 09/10/1998 Created.
REM Billy Draper 07/23/2001 Modified.
REM Oracle 7.3.4 to Oracle 8.1.7
REM

REM Set terminal output and command echoing on.
REM Log output of this script.
SET TERMOUT ON
SET ECHO ON

SPOOL crdb1.log

CONNECT INTERNAL/password

startup nomount

CREATE DATABASE DBTEST
MAXINSTANCES 8
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXLOGHISTORY 1600
MAXDATAFILES 1022
CHARACTER SET WE8ISO8859P1
ARCHIVELOG
LOGFILE
GROUP 1
(
'G:\oradata\DBTEST\redo01a.log'
, 'H:\oradata\DBTEST\redo01b.log'
) SIZE 10M
,GROUP 2
(
'I:\oradata\DBTEST\redo02a.log'
, 'J:\oradata\DBTEST\redo02b.log'
) SIZE 10M
,GROUP 3
(
'H:\oradata\DBTEST\redo03a.log'
, 'J:\oradata\DBTEST\redo03b.log'
) SIZE 10M
DATAFILE
'J:\oradata\DBTEST\system01.dbf' SIZE 150M
;

REM End Spooling to Logfile.
REM Disconnect.
SPOOL OFF
DISCONNECT

@crdb2


REM NAME
REM crdb2.sql
REM
REM FUNCTION
REM Create Basic Tablespaces for the DBTEST Database
REM and Create the Data Dictionary
REM
REM HISTORY Billy Draper 09/10/1998 Created.
REM Billy Draper 07/23/2001 Modified.
REM Oracle 7.3.4 to Oracle 8.1.7
REM

REM Set terminal output and command echoing on.
REM Begin spooling to logfile.

SET TERMOUT ON
SET ECHO ON

SPOOL crdb2.log

REM Change Storage for the System Tablespace.

CONNECT INTERNAL/password

ALTER TABLESPACE system
DEFAULT STORAGE
(
NEXT 128K
PCTINCREASE 0
)
;

REM Create RB00 Rollback Segment in SYSTEM before Creating Tablespaces.

CREATE ROLLBACK SEGMENT rb00 TABLESPACE system
STORAGE
(
INITIAL 16K
NEXT 16K
MINEXTENTS 2
MAXEXTENTS 20
)
;

REM Put R0 ONLINE.

ALTER ROLLBACK SEGMENT rb00 ONLINE
;

REM Create a Tablespace for Rollback Segments
REM only to be used by SYSTEM when Creating/Tuning Rollback Segments.

REM Create a Tablespace for Rollback Segments
REM to be used for Normal Operations.

CREATE TABLESPACE rbs DATAFILE 'G:\oradata\DBTEST\rbs01a.dbf' SIZE 100M
DEFAULT STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 20 MAXEXTENTS 505 PCTINCREASE 1)
/
ALTER TABLESPACE rbs ADD DATAFILE 'H:\oradata\DBTEST\rbs01b.dbf' SIZE 100M
/
ALTER TABLESPACE rbs ADD DATAFILE 'I:\oradata\DBTEST\rbs01c.dbf' SIZE 100M
/
ALTER TABLESPACE rbs ADD DATAFILE 'J:\oradata\DBTEST\rbs01d.dbf' SIZE 100M
/

REM Create Rollback Segments.

CREATE ROLLBACK SEGMENT rb01 TABLESPACE rbs
STORAGE (MINEXTENTS 20 OPTIMAL 20480K)
;

CREATE ROLLBACK SEGMENT rb02 TABLESPACE rbs
STORAGE (MINEXTENTS 20 OPTIMAL 20480K)
;

CREATE ROLLBACK SEGMENT rb03 TABLESPACE rbs
STORAGE (MINEXTENTS 20 OPTIMAL 20480K)
;

CREATE ROLLBACK SEGMENT rb04 TABLESPACE rbs
STORAGE (MINEXTENTS 20 OPTIMAL 20480K)
;

REM Put the Small Rollback Segments ONLINE.

ALTER ROLLBACK SEGMENT rb01 ONLINE
;

ALTER ROLLBACK SEGMENT rb02 ONLINE
;

ALTER ROLLBACK SEGMENT rb03 ONLINE
;

ALTER ROLLBACK SEGMENT rb04 ONLINE
;


REM Put the RB00 Rollback Segment in SYSTEM OFFLINE.
REM Since it is best not to use objects
REM in the SYSTEM Tablespace.

ALTER ROLLBACK SEGMENT rb00 OFFLINE
;

REM Create a Tablespace for Temporary Segments.
REM Set INITIAL and NEXT to a multiple (20 times)
REM of SORT_AREA_SIZE (250K) plus DB_BLOCK_SIZE.

CREATE TABLESPACE temp DATAFILE 'G:\oradata\DBTEST\temp01a.dbf' SIZE 100M
DEFAULT STORAGE (INITIAL 5008K NEXT 5008K MAXEXTENTS 1000 PCTINCREASE 1)
/
ALTER TABLESPACE temp ADD DATAFILE 'H:\oradata\DBTEST\temp01b.dbf' SIZE 100M
/
ALTER TABLESPACE temp ADD DATAFILE 'I:\oradata\DBTEST\temp01c.dbf' SIZE 100M
/
ALTER TABLESPACE temp ADD DATAFILE 'J:\oradata\DBTEST\temp01d.dbf' SIZE 100M
/

ALTER TABLESPACE "TEMP" TEMPORARY;

REM Create Tablespace USERS.

CREATE TABLESPACE users
DATAFILE 'H:\oradata\DBTEST\users01a.dbf' SIZE 2M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
DEFAULT STORAGE ( INITIAL 16K NEXT 128K MAXEXTENTS 1000 PCTINCREASE 1);

REM Create Tablespace TOOLS.

CREATE TABLESPACE tools
DATAFILE 'G:\oradata\DBTEST\tools01a.dbf' SIZE 1M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
DEFAULT STORAGE ( INITIAL 16K NEXT 128K MAXEXTENTS 1000 PCTINCREASE 1);

CREATE USER TESTUSER
IDENTIFIED BY PASSWORD
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS
;

GRANT DBA TO TESTUSER WITH ADMIN OPTION;

ALTER USER "TESTUSER" PASSWORD EXPIRE;

REM End Spooling to Logfile.
SPOOL OFF

REM Set terminal output and echoing off..
REM Install data dictionary views.
REM Install PL/SQL Packages.
REM Run Profile Build.

SET TERMOUT OFF
SET ECHO OFF
CONNECT INTERNAL/password

@%ORACLE_HOME%\rdbms\admin\catalog.sql
@%ORACLE_HOME%\rdbms\admin\catproc.sql
@%ORACLE_HOME%\rdbms\admin\catblock.sql
@%ORACLE_HOME%\rdbms\admin\catparr.sql
@%ORACLE_HOME%\rdbms\admin\caths.sql

CONNECT system/manager

@%ORACLE_HOME%\sqlplus\admin\pupbld.sql

ALTER USER "SYS" IDENTIFIED BY "NEWPASSWORD"
;
ALTER USER "SYSTEM" IDENTIFIED BY "NEWPASSWORD"
;
ALTER USER "DBSNMP" DEFAULT TABLESPACE "TOOLS" TEMPORARY TABLESPACE "TEMP"
;
ALTER USER "SYS" TEMPORARY TABLESPACE "TEMP"
;
ALTER USER "SYSTEM" DEFAULT TABLESPACE "TOOLS" TEMPORARY TABLESPACE "TEMP"
;

SET ECHO ON
SET TERMOUT ON

REM Disconnect.

DISCONNECT

 

[lfoata]

You can use dbassist and save the creation script to a file. Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA