4 Creating and Upgrading a Database

This chapter describes the process of creating or upgrading a database to run with the Oracle Database 10g release 2 software. It contains the following topics:

Creating a Database
Upgrading a Database

Note:

Creating a Java enabled database is not part of this chapter. For more information about this topic refer to Chapter 12, "Java in the Database" in this book.

Creating a Database

You can create a database either automatically or manually. Oracle recommends you to use the automatic creation procedure outlined in the "Creating a Database Automatically" section. Instructions on how to create a database manually are given in the "Creating a Database Manually".

Pre-Creation Activities

Before creating a database, you first need to carry out the following pre-creation activities:

Install the Oracle Database 10g release 2 software under the installation user ID. For details on how to do this, refer to Chapter 3, " Installing Oracle Database Software".
The BS2000 System Administrator must create a JOIN entry for the account, which will hold the Oracle Database (the DBA user ID).

The 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 512MB.
Oracle 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

Complete the following steps to create a database automatically:

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

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

When running INSTALL.P.SUPER procedure, 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 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 are used):

Parameter	Value
DBASID	Enter the 1 - 4 character system-id of the database you are installing. This is a mandatory parameter.
JOBCLASS	Enter the jobclass to be used for the Oracle Database 10g 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 Database user `SYS`. Note: By default the `SYS` user has the password `change_on_install`. For security reasons, Oracle recommends that you change this password immediately after installation.
SYSTEMPW	Enter the desired password for the Oracle Database user `SYSTEM`. Note: By default the `SYSTEM` user has the password manager. For security reasons, Oracle recommends that you change this password immediately after installation.
JAVA	Enter NO if you do not need a Java enabled database (thus saving memory, CPU and disk space resources). For more information refer to Chapter 12, "Java in the Database".
DBSIZE	Enter the size of the system tablespace file(s) in bytes, kilobytes or megabytes. The value you enter can have one of the following forms: 44M for 44 megabytes 44000K for 44000 kilobytes 10000000 for 10000000 bytes The default is 250M.
AUXSIZE	Enter the size of the sysaux tablespace 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 200M.
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 100000 for 100000 bytes The default is 20000K.
LOCAL	Enter NO if you do not require a locally managed system tablespace. The default is `YES`. If you choose a locally managed system tablespace, Oracle automatically creates a default temporary tablespace.
DEFTS	Enter NO if you don't want to create a default permanent tablespace. The default is `YES`.
TEMPTS	This prompt only appears if you don't want a locally managed system tablespace. Enter NO if you don't want a default temporary tablespace. The default is `YES`.
CHARSET	Enter the character set with which you want the database to be created (the default is `WE8BS2000`). For more information refer to the chapter about Globalization Support in Oracle Database User's Guide for Fujitsu Siemens BS2000/OSD .
NCHARSET	Enter the national character set used to store data in columns specifically defined as `NCHAR`, `NCLOB`, or `NVARCHAR2`. Valid values are `AL16UTF16` and `UTF8`. The default is `AL16UTF16`.

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

Calls INSTALL.P.DBA
Creates the system and sysaux tablespace
Creates the default permanent tablespace and temporary tablespace
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 $ORAC1020.INSTALL.P.SUPER has completed, you should have an initialized, ready-to-use database, and a running Oracle Database 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

Oracle recommends that you use the automatic creation procedure outlined in the "Creating a Database Automatically" section. 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 files from $ORAC1020 as shown below:

Call the Oracle Database install procedure. This procedure copies the DBA files from $ORAC1020 to the DBA User ID account. When the procedure begins you are prompted to supply a 1 to 4 character Oracle Database ID for the database you are installing.

To install the DBA files, enter the following command:

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

This procedure prompts you for the following information:

Parameter	Value
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	Value
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 $ORAC1020.INSTALL.P.DBA procedure copies the following files into the DBA User ID account:

sid.P.ORAENV: Oracle Database environment definition file
sid.DBS.INIT.ORA: Oracle Database 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, another Public Volume Set (PVS), or a specific private volume.

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

/FILE sid.DBS.DATABASE1.DBF,SPACE=filesize -
/[,DEVICE=device,VOLUME=volser]
/FILE sid.DBS.SYSAUX.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 file size 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 Database block used as an extra header. The size of this block is 1 to 16 PAM pages depending on the Oracle Database block size given in the init.ora parameter DB_BLOCK_SIZE, refer to Appendix C, "Initialization Parameters and the Parameter File".

For example, if you want to create a 2MB database file, then you need to specify 1024 plus 1 PAM pages extra Oracle Database block as the value of file size 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 preceding examples are the default database and log file names. 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 the database). The SGA parameters may need to be adjusted to reflect memory limitations and the maximum number of users who can access the Oracle Database system at one time. Make the modifications using a BS2000 editor.

Modifying the ORAENV File

Modify the environment definition file, sid.P.ORAENV, according to the 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 command:

START-PROGRAM $ORAC1020.SQLPLUS
* /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 Database 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 previous command.

SQL> CREATE DATABASE...;

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

Unless you have 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 file size is 1 Oracle Database block larger than you specified. This extra block is automatically added by SQL*Plus and contains header information, which is used by the Oracle Database. For example, if you select a file size of 5120 2K blocks by specifying a file size of 10 Mb in SQL*Plus, you get a file of 5121 PAM pages (5120 Oracle Database blocks for 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.

Installing Data Dictionary Views

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

SQL> SPOOL filename
SQL> SET TERMOUT OFF
SQL> @$ORAC1020.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.
Run 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>@$ORAC1020.RDBMS.ADMIN.CATPROC.SQL
```
To install the PL/SQL demonstration tables, run SQL*Plus under the SYSTEM username 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 $ORAC1020.SQLPLUS
        * SYS/password
        SQL> @EXAMPBLD
        SQL> @EXAMPLOD
```

Installing Online HELP Messages

To install the online Help facility, enter the following command:

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

Installing the SQL*Plus Demonstration Database

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

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

Installing the Sample Schemas

To install the Sample Schemas, enter the following:

/CALL-PROCEDURE $ORACL1020.INSTALL.P.SAMPLES,(sid -
/[,SYSTEMPW=systempw] [,SYSPW=syspw])

The procedure INSTALL.P.SAMPLES installs the sample schemas human resources (HR), order entry (OE), info exchange (IX) and sales history (SH) with the default passwords. product media (PM) is not supported.

Note:

By default, the SYSTEM user has the password manager and the SYS user has the password change_on_install. For security reasons, Oracle recommends that you change these passwords and the sample schema passwords immediately after installation.

Verifying Successful Creation of the Database

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

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

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

*SCOTT'S TABLE EMP IS INSTALLED

Installing Oracle Text

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

Installing Java

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

Upgrading a Database

This section contains information about upgrading your Oracle database.

Upgrade from Version 8

If you are using NCHAR data types in a version 8 database, Oracle recommends that you analyze your SQL NCHAR data before migration. For upgrading from version 8 refer to Chapter 3: Upgrading to the New Oracle Database 10g Release in the Oracle Database Upgrade Guide.

Upgrade from Version 9

This section explains the BS2000 specific steps of the upgrade path. We assume the reader to be familiar with the Oracle Database 10g release 2 upgrade documentation ( Oracle Database Upgrade Guide.) about upgrade preparation, space and backup requirements, release differences, and so on. Oracle also recommends reading the appropriate section in the generic documentation, especially when you are using TIMESTAMP WITH TIME ZONE data type.

We further assume your Oracle9i database is set up and the Oracle Database 10g release 2 software is properly installed as explained in Chapter 3 of this manual. Then follow these steps:

Run the Pre-Upgrade Information Tool utlu102i.sql from Oracle Database 10g installation id with SQLPLUS in your Oracle9i environment to analyze required parameters as follows (assuming the database is running):
```
/START-PROGRAM $ORACL920.sqlplus
* /nolog
SQL> connect / as sysdba
SQL> SPOOL info.log
SQL> @$ORAC1020.rdbms.admin.utlu102i.sql;
SQL> SPOOL off
```
Check the sections in the spoolfile for Logfiles, Tablespaces, and Rollback Segments, and change the appropriate values of your database.
Shutdown the database and exit SQLPLUS
```
SQL> SHUTDOWN IMMEDIATE
SQL> exit 
```
Enter the following command to create an Oracle Database 10g release 2 init.ora and an Oracle Database 10g release 2 oraenv file and save the original files under the suffix .OLD.
```
/CALL-PROCEDURE $ORAC1020.INSTALL.P.DBA, (sid, jobclass, UPDATE=YES) 
```
Modify the newly created files according to your special requirements (for instance PROCESSES, DB_BLOCK_BUFFERS, and so on.) and set the parameters in INIT.ORA file as recommended in the spoolfile. Make sure the COMPATIBLE initialization parameter is properly set for the new Oracle Database 10g release (9.2.0 or higher).
/CALL-PROCEDURE sid.p.oraenv

To avoid being prompted for many overflow acknowledgements on your screen set
```
/tchng oflow=no
```

Invoke SQLPLUS with the following scripts:

/START-PROGRAM $ORAC1020.sqlplus
* /nolog
SQL> connect / as sysdba
SQL> STARTUP UPGRADE

Create a SYSAUX tablespace. The Pre-Upgrade Information Tool estimated the minimum required size in the SYSAUX Tablespace section of the spoolfile:

SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf' 
     SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT 
     SPACE MANAGEMENT AUTO ONLINE;

Start the Upgrade script and run utlu102s.sql to display the results of the upgrade:

SQL> SPOOL upgrade.log;
SQL> @$ORAC1020.rdbms.admin.catupgrd.sql;
SQL> @$ORAC1020.rdbms.admin.utlu102s.sql;
SQL> SPOOL off

Shut down and restart the instance to re-initialize the system parameters for normal operation:
```
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
```
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code and verify that all expected packages and classes are valid:
```
SQL> @$ORAC1020.rdbms.admin.utlrp.sql;
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';
```
Now you should have an upgraded Oracle10g release 2 database. For troubleshooting, refer to Chapter3 in the Oracle Database Upgrade Guide.

Post-Upgrade activities

Re-compilation of C and COBOL Programmatic Interface Programs

All C and COBOL programs developed prior to release 10.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 Database libraries.

Rebuilding Oracle Database (openUTM) Applications

As Oracle Database 10g on BS2000 exclusively supports the XA interface and the interfaces have changed significantly, you have to rebuild your openUTM application. Refer to the chapters on openUTM in this manual and in the Oracle Database User's Guide for Fujitsu Siemens BS2000/OSD for more information.

Updating ORAENV Files

Your ORAUID environment variable must reference the correct Oracle Database installation user ID. Check your ORAENV files, and if necessary, amend the values of the ORAUID and NLS_LANG environment variables. Remember that you do not need the file link for ORAUTM anymore.