Upgrading Oracle Database Server from 10.2.0.4 to 11.2.0.2 by dbua

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SP2 32bit, Oracle 10.2.0.4(was upgraded from Oracle 9.2)
Objective: upgrade it to Oracle standard edition 11.2.0.2.


Steps:

Part I - Upgrading Oracle database

1. backup before doing anything.
a. rman backup
Sign on to RMAN:
 rman "target / nocatalog"
 Issue the following RMAN commands:
 RUN
 {
  ALLOCATE CHANNEL chan_name TYPE DISK;
  BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
  BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
  or
  BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
 }

or

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

b. database full export backup (expdp)
set ORACLE_SID=ORCL
exp backup_admin/password file=d:\oracle\oradata\orcl\backup\full_orcl.dmp log=d:\oracle\oradata\orcl\backup\exp_full_orcl.log consistent=y full=y

c. binary backup (cold copy)
Backup c:\program files\oracle directory and d:\oracle data directory.
If you have enough space to backup, no matter how big the temporary tablespace is, just copy them.

After doing copy and before running dbua, check temporary tablespace datafile size, shrink it if it's huge as follows:

shutdown listener and make sure nobody is using database (bounce db if required)

SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';

----------
TEMP
D:\ORACLE\ORADATA\orcl\TEMP01.DBF
1.7826E+10

SQL> ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\orcl\TEMP01.DBF' DROP INCLUDING DATAFILES;
Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'D:\ORACLE\ORADATA\orcl\TEMP01.DBF' SIZE 50m
AUTOEXTEND ON NEXT 20m MAXSIZE 17000M [EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576];
Tablespace altered.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';


d. Windows OS backup
shutdown Windows, use RIP CD or system rescue cd to backup the whole C drive(c:\program files\oracle) and d:\oracle (where data exists) by ntfsclone program.



boot from RIP CD
a. backup partition table
sfdisk -d /dev/cciss/c0d0 | ssh remoteserver 'cat > /path/to/c0d0_sfdisk-d'
for restore, use
ssh remoteserver 'cat /path/to/c0d0_sfdisk-d' | sfdisk /dev/cciss/c0d0

b. backup MBR
dd if=/dev/cciss/c0d0 count=1 bs=512 | ssh remoteserver 'dd of=/path/to/mbr_dd'
for restore, use
ssh remoteserver 'dd if=/path/to/mbr_dd' | dd of=/dev/cciss/c0d0

c. backup ntfs partition /dev/cciss/c0d0p1
ntfsclone -s -o - /dev/cciss/c0d0p1 |gzip -c | ssh remoteserver 'cat > /path/to/c0d0p1_ntfsclone.gz'
for restore, use
ssh remoteserver 'gzip -dc /path/to/c0d0p1_ntfsclone.gz' | ntfsclone -r - -O /dev/cciss/c0d0p1



e. backup OS related files c:\program files\oracle
backup c:\program files\oracle


2. Software Installation (11.2.0.2)
 a. check before software installation
check the existing database charactset

 sql> select * from nls_database_parameters;

backup the existing spfile to pfile [optional]
sql> create pfile from spfile

b. Unzip both 1/6 and 2/6 zip files into same directory, then perform installation (must unzip and put them into the same directory)
c. Execute runInstaller to install ‘software only’ without creating database

3. Pre-upgrading preparation

a.[optional] Shrink temporary tablespace if required as above, otherwise, during installation, 11g will try to use cold backup method to copy all control,redo and data files to d:\oracle\admin\backup directory, including huge temporary tablespace files.

note: for all methods to shrink temporary tablespace under 10g, refer to support article - Resizing (or Recreating) the Temporary Tablespace [ID 409183.1]

Make sure nobody is using the database (stop listener service and bounce database if needed),  if someone is accessing database, refer to above support ID for another way to shrink temporary tablespace.


b. Confirm you have done backup for your database as step 1
During upgrade process, 11g will shutdown current database to do cold backup automatically before it performs the upgrade procedure if we enable backup option. 11g dbua will also create a batch file D:\oracle\product\10.2.0\admin\orcl\backup\SID_restore.bat for restoring our backup in case of upgrade failure.


c. After installing 11g db, Run pre-upgrade information tool
Once 11g software is installed, such as d:\oracle\11.2.0.2\db_1, copy %ORACLE_HOME%\rdbms\admin\utlu112i.sql to somewhere, now let's login to 10g db as sysdba and startup database.

Sql> spool \path\to\upgrade.sql
Sql> @utlu112i.sql or
@$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql
Sql> spool off


d. Adjust parameters if necessary before running ‘dbua’
Sql> purge dba_recyclebin;
Sql> alter system set sga_target=596M scope=spfile  [might be adviced by pre-upgrade script,optional]
Sql> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Sql> SELECT * FROM v$recover_file;  should be no media files to recover

Resolving Outstanding Distributed Transactions
Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID';
For example:
"OWNER"    "OBJECT_NAME"    "OBJECT_TYPE"
"MDSYS"    "SDO_INDEX_METHOD_9I"    "TYPE BODY"
"WKSYS"    "WK_UTIL"    "PACKAGE BODY"
3 rows selected.

Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is 10.0.0.0.0.

Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
SQL> ALTER SYSTEM SET COMPATIBLE = '10.0.0' SCOPE=SPFILE;

For invalid objects or invalid components, Oracle recommends running the the utlrp.sql before starting the upgrade as a means to minimize the number of invalid objects and components marked with WARNING.

Get characterset from the current database
sql> select * from nls_database_parameters;
record down nls_characterset and nls_nchar_characterset.

4. Run dbua to upgrade database

Inform the support team we are going to upgrade, ask them not to touch server.

a. actual upgrade by dbua
Cd $ORACL_HOME ( for Windows, start- program-choose 11g, database upgrade assistant )
Cd  bin
./dbua &

Do Not Move Database Files as Part of Upgrade
Choose ‘backup database’ and upgrade timezone version to version 14

Upgrade log is at C:\oracle\cfgtoollogs\dbua\orcl\upgrade2

b. Identifying Invalid Objects
After running pre-upgrade information tool, any invalid SYS/SYSTEM objects found before upgrading the database are stored in the table named registry$sys_inv_objs.
Any invalid non-SYS/SYSTEM objects found before upgrading the database are stored in registry$nonsys_inv_objs.

(sqlplus / as sysdba;
select * from sys.registry$sys_inv_objs; 
select * from sys.registry$nonsys_inv_objs;)

To identify any new invalid objects due to the upgrade
run @%ORACLE_HOME%\rdbms\admin\utluiobj.sql after finishing upgrade.
   
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';
should see the same number of invalid objects as sys.registry$sys_inv_objs and sys.registry$nonsys_inv_objs

c. Check the flush recovery area settings and confirm archive logging mode is enabled.
sql> show parameter db_recovery_file_dest;
Sql> archive log list;

d. Configure Enterprise Manager if required
If you are not yet using Oracle Enterprise Manager to manage your database, then install and configure Enterprise Manager Database Control.
If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:
emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]
You must run this from the Oracle home of the new Oracle Database 11g release. When prompted, provide the Oracle home from which the configuration is being upgraded.
You can also configure Enterprise Manager using DBCA. Select the Configure Database Options option, and then select the Enterprise Manager Repository option.

5. Rollback plan
a. Use the script that generated by dbua to restore to old 10g database
C:\oracle\admin\sid\backup\sid_restore.bat

b. use our own rman backup to restore back

New features and changes in 11.2
Automatic Diagnostic Repository
The locations of alert logs and trace files are no longer set by the initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST. They are now kept in the Automatic Diagnostic Repository (ADR), whose location is set the by the initialization parameter DIAGNOSTIC_DEST.

JOB_QUEUE_PROCESSES Parameter
Beginning with Oracle Database 11g Release 1 (11.1), the JOB_QUEUE_PROCESSES parameter is changed from a basic to a non-basic initialization parameter. Most databases only need to have basic parameters set in order to run properly and efficiently. The default value is also changed from 0 to 1000.
Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.


6. FAQ

1.    When installing 10.2.0.4 over 10.2.0.1 oracle, it prompts the following error message:
Cannot write to c:\oracle\product\10.2.0\db_1\bin\msvcr71.dll,
When using ‘process explorer’ and found process msdtc is holding it, which is ‘microsoft distributed coordinator’, stop that services from services.msc, if necessary, install ‘process explorer’ to find handle or dll for msvcr71.dll

2.    Services, ‘oracleserviceORCL’ previously point to 10.2, after upgrade, will point to 11.2,
OracleOraDb11g_home1TNSListener (C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR ) service will be created during upgrading (dbua), at the time start doing enterprise manager repository data migrate, it will prompt you to backup EM data for downgrade, after you say ‘yes’ to continue, it will start a 11g listener.

3.    EM create repository, got error SEVERE: 'job_queue_processes' must be greater than or equal to 1.
Sql> Alter system set job_queue_processes=2; can be changed dynamically.


Part II - actual upgrade notes

1. upgrading Oracle
reboot server into Windows,  follow the Oracle upgrade guide, install Oracle database 11.2.0.2 32bit for Windows in a separated directory

2. cleanup the database before upgrading
a.  sql>exec dbms_stats.gather_dictionary_stats;
b. sql>purge dba_recyclebin;
c. sql> select * from v$recover_file; should be no media file to recover

d. Sql> SELECT * FROM dba_2pc_pending;
If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

e. Ensure no files are no backup mode:
Sql> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

f. Check the current flush recovery area settings:
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\flash_recovery_area
db_recovery_file_dest_size           big integer 5000M

g. Record down the number of invalid objects before running dbua.
Sql> select owner,object_name,object_type from dba_objects where status='INVALID'
For devora01, the following is the output:
"OWNER"    "OBJECT_NAME"    "OBJECT_TYPE"
"MDSYS"    "SDO_INDEX_METHOD_9I"    "TYPE BODY"
"WKSYS"    "WK_UTIL"    "PACKAGE BODY"
3 rows selected.

h. Check compatibility parameter before running ‘dbua’, the minimum value required by 11.2 is 10.0.0.0.0.
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

i. run downloaded script dbupgdiag.sql from Oracle support to check the number of invalid objects detail
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

after that,Validating invalid objects first
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

j. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
SQL> @$ORACLE_HOME\rdbms\admin\utlrp.sql
Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';


k.Note down the location of datafiles, redo logs, control files. Also take a backup of all configuration files like listener.ora,tnsnames.ora (C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN) ,etc., from $ORACLE_HOME

SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;

Check services.msc to confirm
OracleServiceORCL is started
OracleOraDb10g_home1TNSListener is started
c:\windows\system32\drivers\etc\hosts has a line for EM to work
ipaddress   name_with_domain name


run select * from nls_database_parameters
to get nls_characterset WE8MSWIN1252
and nls_NCHAR_CHARACTERSET as AL16UTF16


backup
backup c:\program files\oracle to f:\jephe\
bakcup d:\oracle to f:\jephe
record down the number of files and folders for d:\oracle
104927 files and 12986 folders around 30G
after done backup, compare the number of files and folders to make sure they are same.





stop 10g listener
sqlplus / as sysdba
shutdown immediate;
go to services.msc to stop OracleServiceORCL service.


sqlplus / as sysdba
sql> spool d:\dba\upgrade.sql
sql> @......\utlu112i.sql
sql> spool off

change compatible to 10.1.0
execute dbms_stats.gather_dictionary_stats;



3. startup 'database upgrade assistance -dbua' from start- program files - oracle 11g to upgrade the 10g database,
follow the GUI to finish upgrading.




dbua from 11gR2
significant changes before dbua
a. compatible=10.1.0
b. tempfile resize

do not move database file
backup database
upgrade timezone to 14


8. checking after upgrade

a. it should be in archive log mode
sql> archive log list;
sq> SELECT name, value FROM v$parameter WHERE name = 'compatible';
compatible parameter should be in 10.1.0.
b. check if Enterprise Manager is able to start up.
c. query invalid objects generated by upgrading process

start upgrade at 3pm.
14% to 34% to finish 'upgrading Oracle server' part.
34%-38% for upgrading Jserver Java virtual machine
upgrading EM repository takes time - 48%-71%
upgrading Oracle XML database takes long time
post upgrading database also takes time

9. Reconfigure EM

Although inside windows services.msc dbconsole is not starting, but port 5500 might be already running and you can access https://hostname:5500/em/

If you need to recreate EM, do this:
a.    Drivers\etc\hosts to add ip and hostname
b.    Make sure sysman account Is there and you can login as sysman
c.    Check if can login as sysman and dbsnmp

disable EM SSL , just use http
emctl unsecure dbconsole

10. Abandon the Upgrade
a. To cancel the upgrade by restoring the previous backup

Log in to the system as the owner of the Oracle home directory of the previous release.
Sign on to RMAN:


sql> rman "target / nocatalog"


    STARTUP NOMOUNT
    RUN
    {
        RESTORE CONTROLFILE FROM 'save_controlfile_location';
        ALTER DATABASE MOUNT;
        RESTORE DATABASE FROM TAG before_upgrade
        ALTER DATABASE OPEN RESETLOGS;
    }

b. or use dbua generated sid_restore.bat script to restore the cold backup back.


11. References:

a. http://www.diybl.com/course/7_databases/oracle/Oracleshl/20110207/553475.html
b. How to create oracle Windows service - http://psoug.org/reference/oradim.html
c. Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
d. Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]

12. configuration files location

a. Dbconsole log file is at c:\oracle\product\11.2.0\dbhome_2\xxx_orcl\sysman\log
b. db console config file at C:\oracle\product\11.2.0\dbhome_2\xxx_orcl/sysman/config/emd.properties
c. listener.ora and tnsname.ora is at C:\oracle\product\11.2.0\dbhome_2\NETWORK\ADMIN by default
d. 1/6 and 2/6 zip file for 11.2 contains 2824files, 906 folders and totally around 2.18G after unzip.

13. check scheduled task scripts, rewrite Oracle backup script if necessary