The following for upgrading a 9.2.0.8 DB to 11.2 version on Solaris
Required packges for installing 11g software: (see equivalent)
--------------------------------------------
unixODBC-devel-2.2.11
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
gcc..
..
etc
Install 11g software in new ORACLE HOME...
/apps/oracle/product/db11gR2 happens to be mine..
Note ID: 429825.1 Database Upgrade steps from 9i to 11g:
--------------------------------------------------------
Step 1:
-------
Log in to the system as the owner of the new 11gR2 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the $HOME/migration in my case:
mkdir $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utlu11*i.sql $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utltzuv2.sql $HOME/migration
Step 2:
-------
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g to make sure you are re-compiling any objects that became INVALID during migration.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege.
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Step 4:
-------
Create script to save DBLINKS create statements:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','PUBLIC ')||'DATABASE LINK ' ||
DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||
'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''
||L.HOST||''''||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#
Step 5:
-------
Convert the 9i database from TIMEZONE version 1 to version 4:
Download this interm patch..Extract..opatch apply => very simple
Then this query must result version 4:
SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
If the above doesn't work (which it wouldn't), do the following steps. Depending on how much data you have, you might have to try different techniques to make sure you don't pass the downtime window.
http://oramadness.blogspot.com/2010/04/9i10g-11g-timezone-issue.htmlStep 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu112i.sql
spool off
This script will give you information about the tablespaces if they need to adjusted according to 11g and also give info about other initialization parameters that need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Remove the stats for the dictionary ( you will be gathering them again when you are fully upgraded)
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for invalid and corrupt objects in the db.
Set verify off space 0 line 120 heading off
Set feedback off pages 1000
spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES'
/
spool off
exit;
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Make sure there is no invalid objects by query'ing the table INVALID_ROWS.
SQL> select * from invalid_rows;
no rows selected
SQL>
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b)Create a new listener in Oracle 11g for this db.
Step 10:
--------
Ensure no files need media recovery or in backup mode:
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status!='NOT ACTIVE';
Step 11:
--------
Resolve any outstanding unresolved distributed transaction:
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 12:
--------
Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Step 13:
--------
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
Step 14:
--------
Check whether database has any externally authenticated SSL users.
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found, go thru the upgrade guide for further instructions.
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade and finishing in the upgrade window.
Step 16:
-------
Note down the location of datafiles, redo logs, control files.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
Step 17:
-------
Take cold backup (after shutting down the db and restarting it again)
or
if you have your database in archivelog mode then you can do this,
$ rman target / notcatalog
RMAN>run
{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/admin/MYDB1/backup/%U';
backup current controlfile;
backup database format '/home/oracle/admin/MYDB1/backup/%U' TAG before_upgrade;
}
Step 18:
--------
Create the SYSAUX tablespace for 11g.
CREATE TABLESPACE SYSAUX
DATAFILE 'sysaux_01.dbf' size 2048M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 19:
-------
Make a backup of the spfile.
Comment out these obsoleted parameters:
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
DDL_WAIT_FOR_LOCKS
Change the following deprecated parameters:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
Set the COMPATIBLE parameter to 10.1.0 if you want to have the option to downgrade. To use all new features of 11g, you need to use
compatible=11.1.0
When done copy the pfile to the new 11g $ORACLE_HOME/dbs
Step 20:
-------
Create .profile11g under oracle user home directory to point to 11g new software and other relevant environment variables.
Step 20:
-------
Update the oratab entry:
/etc/oratab for linux
/var/opt/oracle/oratab for solaris
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .profile11g
Startup the DB in upgrade mode:
------------------------------
cd $HOME/migration
sqlplus '/ as sysdba'
startup UPGRADE
start the upgrade script:
------------------------
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
At the end, the db will be shutdown by catupgrd.sql script.
Restart the Instance NORMALLY to reinitialize the system parameters for normal operation.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
There may be duplicate objects between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
You can use this query i wrote to find those duplicates:
select distinct 'drop ' || b.object_type || ' SYSTEM.'||b.object_name || ';'
from all_objects a,
all_objects b
where a.owner = 'SYS'
and b.owner = 'SYSTEM'
and a.object_name = b.object_name
order by 1
/
drop PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
drop PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
drop SYNONYM SYSTEM.CATALOG;
drop SYNONYM SYSTEM.COL;
drop SYNONYM SYSTEM.PUBLICSYN;
drop SYNONYM SYSTEM.SYSCATALOG;
drop SYNONYM SYSTEM.SYSFILES;
drop SYNONYM SYSTEM.TAB;
drop SYNONYM SYSTEM.TABQUOTAS;
drop TABLE SYSTEM.AQ$_SCHEDULES;
drop TABLE SYSTEM.DEF$_AQCALL;
drop TABLE SYSTEM.DEF$_CALLDEST;
drop TABLE SYSTEM.DEF$_DEFAULTDEST;
drop TABLE SYSTEM.DEF$_ERROR;
drop TABLE SYSTEM.DEF$_LOB;
Post Upgrade Steps:
##################
Step 22:
--------
Check listener.ora for any modifications needed to listen on the upgraded DB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PF11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/db11gR2)
(SID_NAME = PF11)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 24:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shutdown immediate;
startup;
Step 25:
-------
Now you can gather SYS schema stats.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', options => 'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
You have a cooked 11g database...
