Troubleshooting with Oracle Database Script

Creating a Database

Now the Oracle server is installed we need to create a database to test the installation.

If you are using Oracle 7.2.x or earlier, please read the troubleshooting section below.

1 Create the Initialisation File

Copy the $ORACLE_HOME/dbs/init.ora to $ORACLE_HOME/dbs/initorcl.ora:



$ cd $ORACLE_HOME/dbs

$ cp init.ora initorcl.ora



Modify it by adding the following lines:



db_name = orcl

COMPATIBLE=7.3.3.0.0



2 Creating the Database Install Script

Create a script file called makedb.sql in the $ORACLE_HOME/dbs directory:



connect internal

startup nomount

set echo on

spool makedb.log

create database orcl

maxinstances 1

maxlogfiles 8

datafile '$ORACLE_HOME/dbs/orcl_syst_01.dbf' size 40M reuse

logfile

'$ORACLE_HOME/dbs/orcl_redo_01.dbf' size 1M reuse,

'$ORACLE_HOME/dbs/orcl_redo_02.dbf' size 1M reuse,

'$ORACLE_HOME/dbs/orcl_redo_03.dbf' size 1M reuse;

@$ORACLE_HOME/rdbms/admin/catalog.sql

create tablespace rollback

datafile '$ORACLE_HOME/dbs/orcl_roll_01.dbf' size 8.5M reuse;

create tablespace temp

datafile '$ORACLE_HOME/dbs/orcl_temp_01.dbf' size 5M reuse

temporary;

create tablespace users

datafile '$ORACLE_HOME/dbs/orcl_user_01.dbf' size 10M reuse;

create rollback segment r1 tablespace rollback

storage ( optimal 5M );

alter rollback segment r1 online;

connect system/manager

@$ORACLE_HOME/rdbms/admin/catdbsyn.sql

connect internal

@$ORACLE_HOME/rdbms/admin/catproc.sql

connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

spool off

exit



3 Running the Database Installation Script

Start svrmgrl and run the script:



$ cd $ORACLE_HOME/dbs

$ svrmgrl



Oracle Server Manager Release 2.3.3.0.0 - Production



Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.



Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production



SVRMGR> connect internal

Connected.

SVRMGR> startup nomount

ORACLE instance started.

Total System Global Area 4313312 bytes

Fixed Size 41876 bytes

Variable Size 4140364 bytes

Database Buffers 122880 bytes

Redo Buffers 8192 bytes

SVRMGR> @makedb

<loads of messages>

SVRMGR> exit

Server Manager complete.



4 Starting the Database

Firstly, we need to bring up the database by hand (we will automate this later on). To startup an Oracle database we need to issue the startup command when connected internally:



$ svrmgrl



Oracle Server Manager Release 2.3.3.0.0 - Production



Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.



Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production



SVRMGR> connect internal

Connected.

SVRMGR> startup

ORACLE instance started.

Total System Global Area 4313316 bytes

Fixed Size 41876 bytes

Variable Size 4140368 bytes

Database Buffers 122880 bytes

Redo Buffers 8192 bytes

Database mounted.

Database opened.

SVRMGR> exit

Server Manager complete.



5 Stopping the Database

It is worth mentioning here that restarting a Linux server without shutting down the Oracle database first there is a high risk of corrupting the database.

So, before we issue the Linux shutdown command it is wise to bring down the database:



$ svrmgrl



Oracle Server Manager Release 2.3.3.0.0 - Production



Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.



Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production



SVRMGR> connect internal

Connected.

SVRMGR> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> exit

Server Manager complete.



6 Create a Default User

The database, as created, has a two special users which are automatically created. These are:



Username Password



SYSTEM MANAGER

SYS change_on_install



These users are typically used to hold the standard data dictionary information for the database. It is a good idea to change the passwords from the defaults as soon as possible.

This can be achieved by:



sqlplus system/manager



SQL*Plus: Release 3.3.3.0.0 - Production on Sat Feb 21 12:43:33 1998



Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.





Connected to:

Oracle7 Server Release 7.3.3.0.0 - Production Release



SQL> alter user system identified by <newpassword>;



User altered.



SQL> alter user sys identified by <newpassword>;



User altered.



SQL> exit;

Disconnected from Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production



Since the user system/manager is similar to using root on a UNIX machine, we need to create a user with less ability to cause damage. (remember to bring up the database before attempting to create a user)

Connect to SQL*Plus and create a user:



$ sqlplus system/manager



SQL*Plus: Release 3.3.3.0.0 - Production on Sat Feb 21 12:43:33 1998



Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.





Connected to:

Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production



SQL> create user <user> identified by <psw>

2 default tablespace users

3 temporary tablespace temp;



User created.



SQL> grant connect, resource to <user>



Grant succeeded.



SQL> exit

Disconnected from Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production