Personal Oracle  
  Real Application Clusters  
Create Oracle 10g Grid Computing at Home

By Edward Stoever


 Oracle RAC Home  Website Home  Contact 
Password:
 
The password for this
website is listed on
page 16 of the book.
Purchase the Book
Oracle RAC Errors
Photos
FAQ
Links to Downloads
Articles

Upgrading a 9i Database to 10g
and then Adding Enterprise Manager Repository

By Edward Stoever


This article will demonstrate upgrading a 9i (9.2.0.6.0) single-instance database to 10g (10.2.0.2.0). Then it will demonstrate adding the Enterprise Manager Repository. This task is actually a bit tricky because the upgrade process leaves you with a functioning 10g database that is not quite ready for an Enterprise Manager Repository.

To begin, create a simple 9i database. Next, create two variables to represent the different Oracle Homes like so:
[oracle@x7000 oracle]$ . oraenv
ORACLE_SID = [oracle] ? *
[oracle@x7000 oracle]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
[oracle@x7000 oracle]$ export db10g=$ORACLE_HOME
[oracle@x7000 oracle]$ . oraenv
ORACLE_SID = [*] ? qtest
[oracle@x7000 oracle]$ echo $ORACLE_HOME
/u01/app/oracle/product/9.2.0
[oracle@x7000 oracle]$ export db9i=$ORACLE_HOME
[oracle@x7000 oracle]$
In this example, the database to be upgraded is called qtest. Run the upgrade check script as shown here, then review the results.
[oracle@x7000 oracle]$ grep DESCR $db10g/rdbms/admin/utlu102i.sql -A2
Rem    DESCRIPTION
Rem      This script provides information about databases to be
Rem      upgraded to 10.2.
[oracle@x7000 oracle]$
[oracle@x7000 oracle]$ sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 20 16:38:16 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> spool upgrade_qtest_diagnostic.log
SQL> @db10g/rdbms/admin/utlu102i.sql
SP2-0310: unable to open file "db10g/rdbms/admin/utlu102i.sql"
SQL> @$db10g/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    06-20-2006 16:39:48
.
**********************************************************************
Database:
**********************************************************************
--> name:       QTEST
--> version:    9.2.0.6.0
--> compatible: 9.2.0.0.0
--> blocksize:  8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 439 MB
.... AUTOEXTEND additional space required: 109 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 18 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 5 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 49 MB
.... AUTOEXTEND additional space required: 4 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 
                                                        177861837
WARNING: --> "java_pool_size" needs to be increased to at least 
                                                        67108864
WARNING: --> "streams_pool_size" is not currently defined and needs a 
              value of at least 
                                                        50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 
                                                        20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora 
                                                           or spfile]
**********************************************************************
--> "hash_join_enabled"
.
**********************************************************************
Components: [The following database components will be upgraded 
                                                         or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial                      [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   CTXSYS
....   XDB
....   WMSYS
....   ORDSYS
....   MDSYS
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

SQL> spool off
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
[oracle@x7000 oracle]$
Under most circumstances, at this point, the database would be backed up. This demonstration will skip over that part of the upgrade.

Next, we create a PFILE from our SPFILE that we can make changes to:
SQL> create pfile from spfile;

File created.

SQL> exit

[oracle@x7000 dbs]$ vi /u01/app/oracle/product/9.2.0/dbs/initqtest.ora

Edit the pfile using this the diagnostic output as a guide. Here is my file:
*.background_dump_dest='/u01/app/oracle/admin/qtest/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u02/oradata/qtest/control01.ctl',
                '/u02/oradata/qtest/control02.ctl',
                '/u02/oradata/qtest/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/qtest/cdump'
*.db_block_size=8192
*.db_cache_size=117440512
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='qtest'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=qtestXDB)'
*.fast_start_mttr_target=300
#*.hash_join_enabled=TRUE
*.instance_name='qtest'
#*.java_pool_size=33554432
*.java_pool_size=67108864
*.large_pool_size=28311552
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_max_open_files=20
#*.shared_pool_size=38797312
*.shared_pool_size=177861837
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/qtest/udump'

Now, login as sysdba, shutdown, then create an SPFILE from the PFILE as shown:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL>

Now copy the new spfile over to the 10g Oracle Home:
[oracle@x7000 dbs]$ cp $db9i/dbs/spfileqtest.ora \
> $db10g/dbs/spfileqtest.ora
[oracle@x7000 dbs]$

Switch to the root user, edit the /etc/oratab file so that the database to be upgraded is associated with the 10g Oracle Home:
#qtest:/u01/app/oracle/product/9.2.0:N
qtest:/u01/app/oracle/product/10.2.0/db_1:N

Next, we startup with the upgrade option (note the use of the 10g SQL*Plus!):
[oracle@x7000 dbs]$ . oraenv
ORACLE_SID = [qtest] ? qtest
[oracle@x7000 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
[oracle@x7000 dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jun 21 

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter password:
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  398458880 bytes
Fixed Size                  1261068 bytes
Variable Size             276824564 bytes
Database Buffers          117440512 bytes
Redo Buffers                2932736 bytes
Database mounted.
Database opened.
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
qtest

SQL> CREATE TABLESPACE sysaux
  2  DATAFILE '/u02/oradata/qtest/sysaux01.dbf'
  3  SIZE 300M REUSE
  4  EXTENT MANAGEMENT LOCAL
  5  SEGMENT SPACE MANAGEMENT AUTO
  6  ONLINE;

Tablespace created.

SQL> host whoami
oracle

SQL> spool /home/oracle/upgrade_qtest.log
SQL> @$db10g/rdbms/admin/catupgrd.sql
That will run a very long script to upgrade the database.
When the script is finished, you will see output like so:
Oracle Database 10.2 Upgrade Status Utility  06-21-2006
.
Component                       Status       Version  HH:MM:SS
Oracle Database Server           VALID    10.2.0.2.0  00:26:35
JServer JAVA Virtual Machine     VALID    10.2.0.2.0  00:03:59
Oracle XDK                       VALID    10.2.0.2.0  00:03:10
Oracle Database Java Packages    VALID    10.2.0.2.0  00:01:17
Oracle Text                      VALID    10.2.0.2.0  00:01:37
Oracle XML Database              VALID    10.2.0.2.0  00:02:51
Oracle Workspace Manager         VALID    10.2.0.1.0  00:01:54
Oracle interMedia                VALID    10.2.0.2.0  00:09:33
Spatial                          VALID    10.2.0.2.0  00:06:52
.
Total Upgrade Time: 01:01:03

Now, we create a streams pool, which will require a restart of the database:
SQL> show parameter streams

NAME                         TYPE        VALUE
---------------------------- ----------- --------------------
streams_pool_size            big integer 0

SQL> alter system set streams_pool_size=50331648 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


Start the database back up and compile all of the INVALID objects:
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
       488

1 row selected.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp

...

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

1 row selected.

SQL>
The database is upgraded, but it is still missing two important elements:
  • tnsname.ora entry in the new Oracle Home (I assume you know how)
  • password file (requires the database to be shutdown)
Here I will create a new password file:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g 
Enterprise Edition Release 10.2.0.2.0
With the Partitioning, OLAP and Data Mining options
[oracle@x7000 dbs]$ cd $ORACLE_HOME/dbs
[oracle@x7000 dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@x7000 dbs]$ orapwd file=orapwqtest password=password \
> entries=5
[oracle@x7000 dbs]$ ls orapwqtest
orapwqtest
[oracle@x7000 dbs]$



That finishes the process of upgrading. The database is clean and the appropriate entries and files are in the 10g Oracle Home subdirectories. Now, we can add in the Enterprise Manager Repository.

There are two possible methods for adding in Enterprise Manager. You can use the dbca, or you can use a command line tool called emca. I will be using emca, but before I do, I will show you some common errors you will want to avoid as seen through dbca.

The first error to look at is here:
Enterprise Manager Configuration failed due to the following error - job_queue_processes must be greater than or equal to 1.

You would think that with a GUI tool like dbca, Oracle would check for that condition before running the scripts to add in the repository, and either fail first or make the necessary change for you.

Rather than doing that, the error comes at the very end, so the install is incomplete. A mess! Let's avoid this error from SQL*Plus like so:
SQL> show parameter job_queue_p

NAME                         TYPE        VALUE
---------------------------- ----------- --------------------
job_queue_processes          integer     0
SQL> alter system set job_queue_processes=1 scope=both;

System altered.

SQL>


The next common error looks like this:
Enterprise Manager Configuration failed due to the following error - failed to unlock all EM-related accounts.

This error can be caused by having the DEFAULT profile inappropriately configured or the MONITORING_PROFILE profile inappropriately configuured. Here is how to get that configuration straight:
SQL> col profile for a10 trun
SQL> col resource_name for a25 trun
SQL> col limit for a10 trun
SQL> SELECT   PROFILE, resource_name, LIMIT
  2    FROM dba_profiles
  3   WHERE resource_type = 'PASSWORD'
  4   ORDER BY 1, 2;

PROFILE    RESOURCE_NAME             LIMIT
---------- ------------------------- ----------
DEFAULT    FAILED_LOGIN_ATTEMPTS     10
DEFAULT    PASSWORD_GRACE_TIME       UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME        UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME        UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX        UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME       UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL

7 rows selected.

SQL> -- Default profile is OK, we need a monitoring_profile.
SQL> -- use ALTER PROFILE if the 
SQL> -- monitoring_profile already exists!
SQL> CREATE PROFILE monitoring_profile
  2  LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED
  3  PASSWORD_GRACE_TIME UNLIMITED
  4  PASSWORD_LIFE_TIME UNLIMITED
  5  PASSWORD_LOCK_TIME UNLIMITED
  6  PASSWORD_REUSE_MAX UNLIMITED
  7  PASSWORD_REUSE_TIME UNLIMITED
  8  PASSWORD_VERIFY_FUNCTION NULL;

Profile created.

SQL> -- make sure the user dbsnmp is assigned to 
SQL> -- the monitoring_profile:
SQL> ALTER USER dbsnmp PROFILE monitoring_profile;

User altered.

SQL> col profile for a19 trun
SQL> SELECT   PROFILE, resource_name, LIMIT
  2    FROM dba_profiles
  3   WHERE resource_type = 'PASSWORD'
  4   ORDER BY 1, 2;

PROFILE             RESOURCE_NAME             LIMIT
------------------- ------------------------- ----------
DEFAULT             FAILED_LOGIN_ATTEMPTS     10
DEFAULT             PASSWORD_GRACE_TIME       UNLIMITED
DEFAULT             PASSWORD_LIFE_TIME        UNLIMITED
DEFAULT             PASSWORD_LOCK_TIME        UNLIMITED
DEFAULT             PASSWORD_REUSE_MAX        UNLIMITED
DEFAULT             PASSWORD_REUSE_TIME       UNLIMITED
DEFAULT             PASSWORD_VERIFY_FUNCTION  NULL
MONITORING_PROFILE  FAILED_LOGIN_ATTEMPTS     UNLIMITED
MONITORING_PROFILE  PASSWORD_GRACE_TIME       UNLIMITED
MONITORING_PROFILE  PASSWORD_LIFE_TIME        UNLIMITED
MONITORING_PROFILE  PASSWORD_LOCK_TIME        UNLIMITED
MONITORING_PROFILE  PASSWORD_REUSE_MAX        UNLIMITED
MONITORING_PROFILE  PASSWORD_REUSE_TIME       UNLIMITED
MONITORING_PROFILE  PASSWORD_VERIFY_FUNCTION  NULL

14 rows selected.

SQL>
SQL> select PROFILE
  2  FROM dba_users WHERE username = 'DBSNMP';

PROFILE
-------------------
MONITORING_PROFILE

SQL> -- that is the correct configuration.



Make one final check that you can login using SYS AS SYSDBA using a network connection (using the "@" symbol to go through TNS):
[oracle@x7000 admin]$ sqlplus sys@qtest as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 22 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter password: ********

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 
With the Partitioning, OLAP and Data Mining options

SQL> -- a successful login for sys@qtest as sysdba verifies both 
SQL> -- the tnsnames.ora entry and the password file!



We are now ready to create the Enterprise Manager Repository. You can use dbca to do this, but there is also a command line tool called emca that can be used. This tool is used from the bash shell, not from SQL*Plus. I will use emca for this demonstration. Emca has many different commands. Type emca without any parameters to view a "help" listing.
### Possible commands include:

# Drop Enterprise Manager repository:
emca -deconfig dbcontrol db -repos drop 

# Create Enterprise Manager repository:
emca -config dbcontrol db -repos create

# Recreate Enterprise Manager repository:
emca -config dbcontrol db -repos recreate

Now we try the Enterprise Manager Repository Install:
[oracle@x7000 admin]$ emca -config dbcontrol db -repos create

STARTED EMCA at Jun 22, 2006 10:11:26 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: qtest
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications 
                                  (optional): dept.fuller.edu
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME 
    ................ /u01/app/oracle/product/10.2.0/db_1
Database hostname  ................ x7000.fuller.edu
Listener port number ................ 1521
Database SID ................ qtest
Email address for notifications 
    ............... 
Outgoing Mail (SMTP) server for notifications 
    ............... dept.fuller.edu

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jun 22, 2006 10:12:13 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
$ORACLE_HOME/cfgtoollogs/emca/qtest/emca_2006-06-22_10-11-26-AM.log
Jun 22, 2006 10:12:25 AM 
   oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jun 22, 2006 10:18:45 AM 
   oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jun 22, 2006 10:19:04 AM 
   oracle.sysman.emcp.ParamsManager getLocalListener
WARNING: Error retrieving listener for x7000.fuller.edu
Jun 22, 2006 10:19:29 AM 
   oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jun 22, 2006 10:21:57 AM 
   oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 22, 2006 10:21:57 AM 
   oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: 
>>>> The DB Control URL is http://x7000.fuller.edu:5500/em <<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 22, 2006 10:21:57 AM
[oracle@x7000 admin]$

Now, you should be able to visit the control webpages for the database as shown here:

Configuration files for the Enterprise Manager are found in AGENT_HOME/sysman/config where AGENT_HOME is $ORACLE_HOME/server_name.domain_SID. So for this install, the configuration files are found here:
$ORACLE_HOME/c1000.fuller.edu_qtest/sysman/config

Happy Upgrading!
- Edward

Related Material:
OracleŽ Enterprise Manager Advanced Configuration 10g Release 1 (10.1)
Part No. B12013-01
©2010 Edward Stoever