4 Creating and Upgrading a Database

4.1.1 Pre-Creation Activities

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

1. Install Oracle Database 11g Release 2 software under the installation user ID. For details on how to do this, refer to Chapter 3, " Oracle Database Installation and Deinstallation".

2. The BS2000 System Administrator must create a JOIN entry for the account, which holds 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 512 MB.

3. 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
   

4.1.2 Creating a Database Automatically

Complete the following steps to create a database automatically:

1. Log in using the DBA user ID.

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

   /CALL-PROCEDURE $ORAC1120.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.
   PLSQL	Enter NO to suppress automatic installation of the basic PL/SQL package. 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.

   
   

3. 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 Oracle Database 11g 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 files 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.
   UNDOSIZE	Enter the size of the undo tablespace file 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 100M.
   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, then 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 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, $ORAC1120.INSTALL.P.SUPER generates and enter a batch job which:

• Calls INSTALL.P.DBA

• Creates the system and sysaux tablespace

• Creates the default permanent tablespace, temporary tablespace, and undo tablespace

• Creates the log files

• Initializes the database

• Runs CATALOG.SQL

• Runs CATPROC.SQL

• Installs the SQL Help tables

• Installs the DEMO tables

• Changes the system passwords if necessary

• Calls the verification procedure

When $ORAC1120.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.

4.1.3 Creating a Database Manually

Oracle recommends that you use the automatic creation procedure outlined in "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 of the following steps you want to perform and which you want to expand, omit, or save for another time.

• Copying the DBA Procedures

• Creating the Database

• Installing Data Dictionary Views

• Installing Data Dictionary Views for PL/SQL

• Installing Online Help Messages

• Installing the Demo Tables

• Installing the Sample Schemas

• Verifying Successful Creation of the Database

• Installing Oracle Text

• Installing Java

/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]

/START-PROGRAM $ORAC1120.SQLPLUS
* /NOLOG
SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT [PFILE=filename]

SQL> CREATE DATABASE...;

SQL> SPOOL filename
SQL> SET TERMOUT OFF
SQL> @$ORAC1120.RDBMS.ADMIN.CATALOG.SQL

  SQL>@$ORAC1120.RDBMS.ADMIN.CATPROC.SQL

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

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

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

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

*SCOTT'S TABLE EMP IS INSTALLED

4.2.1 Upgrade from Version 9

For upgrading from version 9 refer to "Upgrading to the New Release of Oracle Database" in Oracle Database Upgrade Guide.

4.2.2 Upgrade from Version 10

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

We further assume your Oracle Database 10g is set up and the Oracle Database 11g Release 2 software is properly installed as explained in Chapter 3, " Oracle Database Installation and Deinstallation". Thereafter, complete the following steps:

1. Run the Pre-Upgrade Information Tool utlu112i.sql from Oracle Database 11g installation id with SQL*Plus in your Oracle Database 10g environment to analyze required parameters as follows (assuming the database is running):

   /START-PROGRAM $ORACL1020.sqlplus
   * /nolog
   SQL> connect / as sysdba
   SQL> SPOOL info.log
   SQL> @$ORAC1120.rdbms.admin.utlu112i.sql;
   SQL> SPOOL off
   

2. Check the sections in the spoolfile for Logfiles, Tablespaces, and Rollback Segments, and change the appropriate values of your database.

3. Shutdown the database and exit SQL*Plus

   SQL> SHUTDOWN IMMEDIATE
   SQL> exit 
   

4. Enter the following command to create an Oracle Database 11g Release 2 init.ora and an Oracle Database 11g Release 2 ORAENV file and save the original files under the suffix .OLD.

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

5. Modify the newly created files according to your special requirements (for instance PROCESSES, DB_CACHE_SIZE, and so on) and set the parameters in the INIT.ORA file as recommended in the spoolfile. Ensure that the COMPATIBLE initialization parameter is properly set for Oracle Database 10g Release 2 (10.2 or higher).

6. Set the Oracle environment variables using the ORAENV file:

   /CALL-PROCEDURE sid.p.oraenv

   To avoid being prompted for many overflow acknowledgements on your screen, set the overflow to No:

   /tchng oflow=no
   

7. Start the instance in upgrade mode:

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

8. Start the upgrade script:

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

9. Shut down and restart the instance to re-initialize the system parameters for normal operation:

   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP
   

10. Run the postupgrade script utlu112s.sql to display the results of the upgrade and run catuppst.sql to finish the upgrade. Finally run utlrp.sql to recompile any remaining stored PL/SQL and Java code and verify that all expected packages and classes are valid:

    SQL> SPOOL postupgrade.log;
    SQL> @$ORAC1120.rdbms.admin.UTLU112S.SQL;
    SQL> SPOOL off
    SQL> @$ORA11202.RDBMS.ADMIN.CATUPPST.SQL
    SQL> @$ORAC1120.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 Oracle Database 11g Release 2 database. For troubleshooting, refer to "Upgrading to the New Oracle Database 10g Release" in Oracle Database Upgrade Guide.

4.2.3 Postupgrade Activities

This section describes the tasks you must complete after upgrading your database to Oracle Database 11g Release 2.

Re-compilation of C and COBOL Programmatic Interface Programs

All C and COBOL programs developed prior to release 11.2 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

openUTM applications must be rebuilt. Refer to the chapters on openUTM in this manual and in Oracle Database User's Guide for Fujitsu 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.