4
Creating/Migrating a Database

This chapter describes the process of creating or upgrading a database to run with your Oracle Server release 9.2.0 software. It describes:

creating a database
migrating and upgrading from prior releases
post-upgrade and post-migration activities

Creating a Java enabled database is not part of this chapter. For more information about this topic see chapter "Java in the Database" below in this book.

Creating a Database

You can create a database either automatically or manually. Oracle Corporation recommends you to use the automatic creation procedure outlined in the section "Creating a Database Automatically" below. Instructions on how to create a database manually are given in the section "Creating a Database Manually". Before creating a database, either automatically or manually you first need to carry out the following pre-creation activities.

Pre-creation Activities

Install the Oracle Server release 9.2.0 software under the installation userid. For details on how to do this, please refer to the chapter "Installing/Upgrading Oracle Server Software".
The BS2000 System Administrator needs to create a JOIN entry for the account which will hold the Oracle Server database (the DBA userid).

Required privileges for this account are as follows:
```
	NTL=YES

	EXPRESS=YES

	TTYPL=TP

	CSTMP-MACRO=YES






Note:



The value of ADDRSPACE must be at least 128MB.
```
Oracle Corporation recommends that the BS2000 System Administrator should define a separate job class for the background tasks. This job class should have the following characteristics:
```
	TP-ALLOWED=YES

	NO-CPU-LIMIT=YES

	JOB-TYPE=BATCH 
```

Creating a Database Automatically

LOGON under the DBA userid.

To start the automatic creation procedure, INSTALL.P.SUPER, enter the following:

	/CALL-PROCEDURE $ORACL920.INSTALL.P.SUPER

When invoking INSTALL.P.SUPER you can specify the value of the following keyword parameters (the default values are used, if you choose not to modify the values):

Parameter	Values
BATCH	Enter YES/NO to run the procedure in batch mode. The default is set to YES, so by default the procedure is run in batch mode.
CPULIMIT	Sets the time limit for batch jobs. The default is NO.
PL/SQL	Enter NO to suppress automatic installation of the basic PL/SQL package. The default is YES.
ROLLBACK	Enter NO to suppress automatic creation of a second rollback segment. The default is YES.
VIEWS	Enter NO to suppress automatic installation of the basic views (catalog, import/export, and so on). The default is YES.

Answer the prompts for the following information (if you enter nothing, the default shown on the screen will be used):

Parameter	Values
DBASID	Enter the 1 - 4 character system-id of the database you are installing. This is mandatory.
JOBCLASS	Enter the jobclass to be used for the Oracle9i Release 2 background jobs. This is mandatory.
UPDATE	Enter YES if you have existing files for this sid and you want to update them.
SYSPW	Enter the desired password for the Oracle Server user SYS. Note: By default the SYS user has the password `change_on_install`. For security reasons, Oracle Corporation recommends that you change this password immediately after installation.
SYSTEMPW	Enter the desired password for the Oracle Server user SYSTEM Note: By default the SYSTEM user has the password `manager`. For security reasons, Oracle Corporation recommends that you change this password immediately after installation.
DBSIZE	Enter the size of the database file(s) in bytes, kilobytes or megabytes. The value you enter can have one of the following forms:
	44M for 44megabytes 44000K for 44000 kilobytes 10000000 for 10000000 bytes The default is 120M.
LOGSIZE	Enter the size of the log files in bytes, kilobytes or megabytes. The value you enter can have one of the following forms:
	1M for 1 megabytes 1000K for 1000 kilobytes 1000000 for 100000 bytes The default is 10000K.
CHARSET	Enter the character set with which you want the database to be created (the default is WE8BS2000). For more information see the chapter about National Language Support in Oracle9i User´s Guide for Fujitsu Siemens Computers BS2000/OSD.
JAVA	Enter NO if you don't need a Java enabled database (thus saving memory, CPU and disk space resources). For more information see chapter "Java in the Database" below.

Unless specified otherwise $ORACL920.INSTALL.P.SUPER will generate and enter a batch job which:

calls INSTALL.P.DBA
creates the database file
creates the log files
initializes the database
runs CATALOG.SQL
runs CATPROC.SQL
installs the SQL Help tables
installs the DEMO tables
creates a second rollback segment
changes the system passwords if necessary
calls the verification procedure

When $ORACL920.INSTALL.P.SUPER has completed, you should have an initialized, ready-to-use database, and a running Oracle Server system. The results of the job are listed in the file, L.sid.INSSUP.SYSOUT, where sid is the system ID of the database you have just installed.

Creating a Database Manually

You can create a database either automatically or manually. Oracle Corporation recommends you to use the automatic creation procedure outlined in the previous section "Creating a Database Automatically". The following manual creation procedure performs the same steps as the automatic creation procedure. However, because you enter the individual steps manually, you can perform the installation at your own pace, and determine which of the optional steps you want to perform and which you want to expand upon, omit or save for another time.

Copying the DBA Procedures

Copy the DBA procedures from $ORACL920 as shown below:

LOGON under the DBA userid.

Call the Oracle Server database install procedure. This procedure copies the DBA files from $ORACL920 to $DBA. When the procedure begins you will be prompted to supply a 1 to 4 character Oracle Server database ID for the database you are installing.

To install the DBA files, enter the following:

    /CALL-PROCEDURE $ORACL920.INSTALL.P.DBA

This procedure prompts you for the following information:

Parameter	Values
DBASID	Enter the 1 - 4 character system ID of the database you are installing.
JOBCLASS	Enter the BS2000 jobclass to be used for background and server tasks.

You can also modify the following keyword parameters when invoking this procedure:

Parameter	Values
LOG	Enter WRITE-TEXT (the BS2000 command name) if you want to have install actions listed.
UPDATE	Enter YES/NO to indicate whether existing files are to be updated. The default is NO.

The $ORACL920.INSTALL.P.DBA procedure copies the following files into the $DBA account:

sid.P.ORAENV: Oracle Server environment definition file
sid.DBS.INIT.ORA: Oracle Server initialization file

where sid is the database ID for the database being installed.

Creating the Database

After installing the DBA procedures, you must create the database. This section describes the procedure for creating the database, and for allocating the database file and the log files.

Pre-allocating the Files

You can place the database file and the log files on the default volume set for the DBA account, or on another Public Volume Set (PVS), or on a specific private volume.

To create a database file or a log file on a private disk, you must first pre-allocate the file using the BS2000 /FILE command as shown below:

/FILE sid.DBS.DATABASE1.DBF,SPACE=filesize -

/[,DEVICE=device,VOLUME=volser]

/FILE sid.DBS.LOG1.DBF,SPACE=filesize -

/[,DEVICE=device, VOLUME=volser]

/FILE sid.DBS.LOG2.DBF,SPACE=filesize -

/[,DEVICE=device, VOLUME=volser]

where:

sid
identifies the database that you are installing.

filesize
is the size of the file in PAM blocks. The filesize specified in the /FILE command must match the size specified to SQL*Plus in the CREATE DATABASE statement when creating the database, plus 1 extra Oracle Server block used as an extra header. The size of this block is 1 to 16 PAM pages depending on the Oracle Server block size given in the init.ora parameter DB_BLOCK_SIZE, see Appendix C, "Additional Notes on Initialization Parameters".
For example, if you want to create a 2MB database file, then you need to specify 1024 plus 1 PAM pages extra Oracle Server block as the value of filesize in the FILE command.

device
specifies the device to be used to store the file.

volser
specifies the volume to be used to store the file.

The names used in the above examples are the default database and log filenames. If you wish to use other names, remember to use these names in the SQL CREATE DATABASE statement, when creating the database.

Modifying the Initialization File

Determine what changes, if any, you wish to make to parameters in the distributed initialization file, sid.DBS.INIT.ORA (where sid is the database ID for your database). The SGA parameters may need to be adjusted to reflect memory limitations and the maximum number of users who can access your Oracle Server system at one time. Make the modifications using a BS2000 editor.

Refer to the Oracle9i Reference manual for an explanation of initialization parameters.

Modifying the ORAENV File

Modify the environment definition file, sid.P.ORAENV, according to your specific requirements. Remember that a number of variables are evaluated during startup only. If you modify the ORAENV file later on, you may have to wait for the next startup for the changes to become effective.

The character set in the ORAENV variable NLS_LANG however must not be changed when you run some of the delivered SQL scripts.

Using SQL*Plus to Create the Database

Remember that you must call the applicable sid.P.ORAENV procedure before calling SQL*Plus. To execute SQL*Plus, enter the following:

/START-PROGRAM $ORACL920.SQLPLUS

Then the following dialog should occur:

* /NOLOG
SQL> CONNECT / AS SYSDBA 
SQL> STARTUP NOMOUNT [PFILE=filename]

/NOLOG omits being prompted for username/password. CONNECT gives you a connection to an idle instance. The last statement starts the Oracle Server instance. If you want to use your own copy of the initialization file (sid.DBS.INIT.ORA), use the PFILE=filename option, as illustrated in the above command.

SQL> CREATE DATABASE...;

This statement creates database and log files. Note when you enter the statement:

unless you have pre-allocated the database file and the log files and specified these files in the CREATE DATABASE statement, the files will be created by SQL*Plus on the default public volume set.

when you create the files using SQL*Plus, the actual filesize will be 1 Oracle Server block larger than you specified. This extra block is automatically added by SQL*Plus and contains header information which is used by the Oracle Server. For example, if you select a filesize of 5120 2K blocks by specifying a filesize of 10 Mb in SQL*Plus, you will get a file of 5121 PAM pages (5120 Oracle Server blocks for your use, plus the header block).

Note:

If you get an error before the first SQL> prompt, it may be caused by either a missing ORAENV file (or ORASID not set in the ORAENV), or sometimes by an address space conflict, for example, the address range you assigned to the kernel memory pool (KNL_BASE) could be occupied by a subsystem.

Installating Data Dictionary Views

Data dictionary views provide easy access to dictionary information. If you wish to use dictionary views, you must install them by invoking SQL*Plus, issuing the CONNECT / AS SYSDBA command and entering the following commands:

SQL> SPOOL filename

SQL> SET TERMOUT OFF

SQL> @$ORACL920.RDBMS.ADMIN.CATALOG.SQL

Data dictionary views required for Export/Import are also installed in this sequence.

Installing Data Dictionary Views for PL/SQL

If you chose not to install automatically the basic PL/SQL package when running INSTALL.P.SUPER, to make PL/SQL available for use now you must first perform a STARTUP and then:

Include the following line in the ORAENV file:
```
	SQLPATH=&ORAUID..RDBMS.ADMIN;  &ORAUID..PLSQL.DEMO; <other prefixes>;
```
where <other prefixes> specifies the necessary prefix for scripts called by the PL/SQL demo scripts.
Invoke SQL*Plus, issue the CONNECT / AS SYSDBA command, and run the SQL script RDBMS.ADMIN.CATPROC.SQL to install the PL/SQL dictionary tables:
```
	SQL>@$ORACL920.RDBMS.ADMIN.CATPROC.SQL
```
To install the PL/SQL demonstration tables, invoke SQL*Plus under the username SYSTEM and run the scripts EXAMPBLD.SQL (which creates the demo tables) and EXAMPLOD.SQL (which loads the demo data into the tables):
```
	SQL> START-PROGRAM $ORACL920.SQLPLUS

	* SYS/<password>

	SQL> @EXAMPBLD

	SQL> @EXAMPLOD
```

Installing Online HELP Messages

To install the online Help facility, enter the following:

/CALL-PROCEDURE $ORACL920.INSTALL.P.HELP,(sid [,SYSTEMPW=systempw])

Installing the SQL*Plus Demonstration Database

To install the SQL*Plus demonstration database, enter the following:

/CALL-PROCEDURE $ORACL920.INSTALL.P.DEMO,(sid [,SYSTEMPW=systempw])

Verifying Successful Creation of the Database

To verify that the demonstration database was correctly created, enter the following:

/CALL-PROCEDURE $ORACL920.INSTALL.P.VERIFY,(sid [,SYSTEMPW=systempw])

If the demonstration database was correctly created, you see messages like the following one displayed on your screen:

*SCOTT'S TABLE EMP IS INSTALLED

Installing Oracle Text

Usage and installation of Oracle Text is summarized in the chapter "Oracle Text" below in this book.

Installing Java

Using and installing of Java is summarized in chapter "Java in the Database" below in this book.

Migrating from Version 8

Transferring an Oracle version 8 database into an Oracle9i database does not need the Migrate Utility. It is actually done by applying some upgrade scripts to the version 8 database, see below in this chapter. Therefore Migrate Utilitiy of Oracle9i has not been ported to BS2000/OSD. As a consequence when you try to migrate an Oracle 7 database to an Oracle9i database you must follow this path:

Migrate the Oracle7 database to an Oracle8i database
Upgrade the Oracle8i database to an Oracle9i database

Upgrade from Version 8

For differences between migration and upgrading see Oracle9i Migration.

This section explains the BS2000 specific steps of the upgrade path. We assume the reader to be familiar with the Oracle9i Release 2 upgrade documentation (see Oracle9i Migration) about upgrade preparation, space and backup requirements, release differences etc.

We further assume your Oracle8 database is shutdown normal and the Oracle9i Release 2 software is properly installed as explained in ch.3 of this manual.

Then follow these steps:

/CALL-PROCEDURE $ORACL920.INSTALL.P.DBA, (sid, jobclass, UPDATE=YES)

will create an Oracle9i Release 2 init.ora and an Oracle9i Release 2 oraenv file and save the original files under the suffix .OLD.
Modify the newly created files according to your special requirements (for instance PROCESSES, DB_BLOCK_BUFFERS etc.) and set
```
_SYSTEM_TRIG_ENABLED = FALSE 
```
in INIT.ORA file.
/CALL-PROCEDURE sid.p.oraenv

To avoid being prompted for many overflow acknowledgements on your screen set
```
/tchng oflow=no
```
This step depends of the version you try to upgrade and is different for 8.1.7, 8.1.5 and 8.0.4.

Note:
The following sequences contain scripts for upgrade of JavaVM and Oracle Text. Don't call these scripts when your existing database is not Java resp. Text enabled.

When you try to upgrade from version 8.1.7 use the following scripts:

/START-PROGRAM $ORACL920.sqlplus

SQL> connect / as sysdba
SQL> startup migrate
SQL> spool my-catout.log	
SQL> @$ORACL920.rdbms.admin.u0801070.sql;
SQL> @$ORACL920.javavm.admin.jvmdbmig.sql;    (for Java VM upgrade only)
SQL> @$ORACL920.javavm.admin.catjava.sql;     (for Java VM upgrade only)
SQL> spool off
SQL> shutdown immediate
SQL> startup restrict                         (for Oracle Text only)
SQL> spool text-upgrade.log                   (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0900010.sql;       (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                   (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0900010.sql;       (for Oracle Text only)
SQL> connect / as sysdba                      (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0902000.sql;       (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                   (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0902000.sql;       (for Oracle Text only)
SQL> connect / as sysdba                      (for Oracle Text only)
SQL> spool off                                (for Oracle Text only)
SQL> shutdown immediate                       (for Oracle Text only) 
SQL> startup
SQL> $ORACL920.rdbms.admin.utlrp.sql;
SQL> SELECT comp_id, version, status FROM dba_registry; 
                                              (verify correct versions)

When you try to upgrade from version 8.1.5 use the following scripts:

/START-PROGRAM $ORACL920.sqlplus

SQL> connect / as sysdba
SQL> startup migrate
SQL> spool my-catout.log	
SQL> @$ORACL920.rdbms.admin.u0801050.sql;
SQL> spool off
SQL> shutdown immediate
SQL> startup restrict                          (for Oracle Text only)
SQL> spool text-upgrade.log                    (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0801060.sql;        (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                    (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0801060.sql;        (for Oracle Text only)
SQL> connect / as sysdba                       (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0801070.sql;        (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                    (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0801070.sql;        (for Oracle Text only)
SQL> connect / as sysdba                       (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0900010.sql;        (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                    (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0900010.sql;        (for Oracle Text only)
SQL> connect / as sysdba                       (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.s0902000.sql;        (for Oracle Text only)
SQL> connect ctxsys/ctxsys;                    (for Oracle Text only)
SQL> @$ORACL920.ctx.admin.u0902000.sql;        (for Oracle Text only)
SQL> connect / as sysdba                       (for Oracle Text only)
SQL> spool off                                 (for Oracle Text only)
SQL> shutdown immediate                        (for Oracle Text only) 
SQL> startup
SQL> $ORACL920.rdbms.admin.utlrp.sql;
SQL> SELECT comp_id, version, status FROM dba_registry; 
                                               (verify correct versions)

When you try to upgrade from version 8.0.4 use the following scripts


/START-PROGRAM $ORACL920.sqlplus

SQL> connect / as sysdba
SQL> startup migrate
SQL> spool my-catout.log	
SQL> @$ORACL920.rdbms.admin.u0800040.sql;
SQL> spool off
SQL> shutdown immediate
SQL> startup
SQL> $ORACL920.rdbms.admin.utlrp.sql;
SQL> SELECT comp_id, version, status FROM dba_registry; 
                                               (verify correct versions)

Now you should have an upgraded Oracle9i Release 2 database. For troubleshooting see Oracle9i Migration ch.7.

Post-Upgrade activities

Re-compilation of C and COBOL Programmatic Interface Programs

All C and COBOL programs developed prior to release 9.2.0 must be precompiled using the new version of the precompilers and recompiled.

Re-Linking Programmatic Interface Programs

All user-written precompiler or Oracle Call Interface applications must be re-linked using the new Oracle Server libraries.

Rebuilding Oracle Server (UTM) Applications

UTM applications that use any older version of Oracle Server need to be preprocessed and built again, as the interfaces have changed significantly. See the chapters on UTM in this manual and in the User's Guide for more information.

Updating ORAENV Files

Your ORAUID environment variable must reference the correct Oracle Server installation userid. Check your ORAENV files and, if necessary, amend the values of the ORAUID and NLS_LANG environment variables.

4 Creating/Migrating a Database