The following is a real scenario for upgrading a 9.2.0.8 DB to 11.1.0.6.0 version on Redhat 4.7 in 28 Step
Required packges for installing 11g software: This for RHCL 4.7
--------------------------------------------
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
unixODBC-devel-2.2.11
Install 11g software on new ORACLE Home...
Note ID: 429825.1
####### ########
Database upgrade steps from 9i to 11g:
#####################################
Step 1:
-------
Log in to the system as the owner of the new 11gR1 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 /tmp directory on your system:
$ORACLE_HOME/rdbms/admin/utlu111i.sql
$ORACLE_HOME/rdbms/admin/utltzuv2.sql
Step 2:
-------
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade like:
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
set linesize 170
set pages 100
SELECT 'grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to '||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', 'OLAPDBA', 'OLAPSVR',
'OLAP_USER','OLAPSYS', '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 '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' 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
Step 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu111i.sql
spool off
This script will give you infomation about tablespaces if they need to adjusted according to 11g and also give info about parameters need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Gather statistics for SYS schema:
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for corruption in the dictionary:
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set 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
This creates a script called analyze.sql.
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b) backup listener and tnsnames then Delete the 9i listener using netca from 9i ORACLE_HOME.
c) Create 11g listener using netca frin 11g ORACLE_HOME.
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 then Step 31 has to be followed after the upgrade
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade time.
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 group#, member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
Step 17:
-------
Take cold backup.
Step 18:
-------
Make a backup of the spfile.
=Comment out obsoleted parameters:
DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
=Change all 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
=set the parameter CLUSTER_DATABASE=FALSE
When done copy the pfile to 11g ORACLE_HOME/dbs
Step 19:
-------
Create .11g_profile under oracle user home directory to point to 11g new binaries.
Step 20:
-------
Update the oratab entry:
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .11g_profile
Startup the DB in upgrade mode:
------------------------------
startup UPGRADE
Create the SYSAUX tablespace:
----------------------------
CREATE TABLESPACE SYSAUX
DATAFILE '/fiber_ocfs_ORCL_data_1/oracle/ORCL/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
start the upgrade script:
------------------------
cd $ORACLE_HOME/rdbms/admin
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
Database will be shutdown by catupgrd.sql script. Restart the Instance to reinitialize the system parameters for normal operation.
If you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script:
--------------------------------------------------------------------------------
included in required_11g_packages/oracle
@dbupgdiag.sql
During the test I found duplicate object between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
###############################################
BE SURE THAT FLASHBACK OPTION IS RUNNING ......
###############################################
drop TABLE system.AQ$_SCHEDULES;
drop INDEX system.AQ$_SCHEDULES_PRIMARy;
drop PACKAGE system.DBMS_REPCAT_AUTH;
drop PACKAGE system.BODYDBMS_REPCAT_AUTH;
drop PACKAGE system.DBMS_SHARED_POOL;
drop PACKAGE system.BODYDBMS_SHARED_POOL;
drop TABLE system.UTL_RECOMP_INVALID ;
drop TABLE system.UTL_RECOMP_SORTED;
drop TABLE system.UTL_RECOMP_COMPILED;
drop TABLE system.UTL_RECOMP_BACKUP_JOBS;
drop TABLE system.UTL_RECOMP_LOG;
drop PACKAGE system.UTL_RECOMP;
drop PACKAGE system.BODYUTL_RECOMP;
drop TABLE system.CHAINED_ROWS;
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 = pc-ora-test)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/ora11g)
(SID_NAME = ORCL)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Edit init.ora:
CLUSTER_DATABASE=true
create spfile from pfile;
Step 24:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 25: -Only in case of RAC environment- < From both nodes>
--------
Upgrade the OCR:
From 11g Oracle_Home::
srvconfig -upgrade -dbname ORCL -orahome /u01/oracle/ora9i
From 9i Oracle_Home:
srvctl remove database -d ORCL
From 11g Oracle_Home::
srvctl add database -d ORCL -o /u01/oracle/ora11g
srvctl add instance -d ORCL -i ORCL1 -n pc-ora-nod1
srvctl add instance -d ORCL -i ORCL2 -n pc-ora-nod2
Step 26:
--------
Configure Enterprise Manager:
First be sure to open the port 1158 which the default port for EM...
#emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]
Create the repostory:
--------------------
run this command from VNC:
-------------------------
emca -repos create
Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
feed it back with SYSMAN passwd you want because EM will create it.
Y
Repostory will be created within 10min...
Configure the EM with this command:
----------------------------------
emca -config dbcontrol db
STARTED EMCA at Sep 18, 2008 7:39:10 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: ORCL
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Y
Will be done within 5min check the connection string to use in the output.
https://pc-ora-test:1158/em
Step 27:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shu immediate;
startup;
No comments:
Post a Comment