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:
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:
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
|
Oracle RAC Home