Powered By Blogger

Wednesday, June 13, 2012

Oracle 9.2.0.6 to 11.2.0.2 upgrade steps

The below steps followed for upgrading a production database which is of size 400GB.

Preupgrade steps :
--------------------------------------

9206 oracle home = /usr/oracle/ora_9_2_0_5

> do the count on the PG022 server on 9.2.0.6 database before going for upgrade.

step 1: Shut down the existing database STRASPRD in old 9.2.0.6 home.

>shutdown immediate

step 2: copy the datafile from old filesystem to new filesystem

> script copy_DF_strasprd.sh

step 3a: Backup the spfile with new name.

> copy spfilestrasprd.ora spfilestrasprd20120519.ora.bkp

step 3b : change the controlfile locations in the pfile/spfile in 9.2.0.6 home with new database

step 4: connect sqlplus and startup nomount

> sqlplus /nolog
> startup nomount

step 4: Recreate the controlfile.

> script control_file_creation_script

step 5: Mount the database

>alter database mount

step 6: Recover using backup controlfile and open resetlogs

> recover database using backup controlfile until cancel;

> alter database open resetlogs;

step 7: create a new temporary tablepsace - remove the copy in DF copy script file.


create temporary tablespace TEMP tempfile '/prd/HK/strauss/undo/temp_01.dbf' size 1000M autoextend on next 1000M maxsize 8000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

step 8: check the datafiles count and logfiles, controlfile locations as per the new filesystem.

select name from v$datafile;
select * from v$controlfile;
select * from v$logfile;
select * from v$reocver_file;

step 9: check the count of invalid obejcts and database components versions and status before going for upgrade.

select owner,count(*),object_type from dba_objects where status !='VALID' group by owner,object_type;
15:21:37 SQL> select owner,count(*),object_type from dba_objects where status !='VALID' group by owner,object_type;

OWNER                            COUNT(*) OBJECT_TYPE
------------------------------ ---------- ------------------
SYS                                     1 TRIGGER
SYS                                     1 PROCEDURE
SYS                                     8 PACKAGE BODY
SYSTEM                                371 PACKAGE
SYSTEM                                394 PACKAGE BODY
IBROKER                                17 PACKAGE
IBROKER                                21 PACKAGE BODY

7 rows selected.

select  COMP_NAME,version,status from dba_registry;
15:24:01 SQL> select  COMP_NAME,version,status from dba_registry;

COMP_NAME                                                                                                                                                                                                                                                       VERSION                        STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------
Oracle9i Catalog Views                                                                                                                                                                                                                                          9.2.0.6.0                      VALID
Oracle9i Packages and Types                                                                                                                                                                                                                                     9.2.0.6.0                      VALID


step 10: Shutdown the database for 9.2.0.8 upgrade.

> shutdown immediate



Upgrade to 9.2.0.8 steps :
-------------------------------------------------------------------

step 1: set the environment variables

export ORACLE_HOME=/prd/strauss/oradump/9.2.0.8
export PATH=/prd/strauss/oradump/9.2.0.8/bin:$PATH
export LD_LIBRARY_PATH=/prd/strauss/oradump/9.2.0.8/lib32
export LD_LIBRARY_PATH_64=/prd/strauss/oradump/9.2.0.8/lib


step 2: copy the spfile to the new 9.2.0.8 oracle home

cp /usr/oracle/ora_9_2_0_5/dbs/spfilestrasprd.ora  /prd/strauss/oradump/9.2.0.8/dbs/spfilestrasprd.ora

step 3b: start the database using 9.2.0.8 home

export ORACLE_SID=strasprd
sqlplus -v
sqlplus /nolog
sqlplus / as sysdba

step 4: startup the database and check parameters for shared pool and java pool - refer document.

> startup

SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE

 If the system is using a server parameter file:
 If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:

 SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

 If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
 SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

step 6: shutdown immediate and startup in migrate mode

SQL> STARTUP MIGRATE
SQL> SPOOL 9208_cpatch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

Review the 9208_patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catpatch.sql script after correcting any problems.

step 7: restart the database

> SHUTDOWN
> STARTUP

step 8: Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
This step is optional but recommended.

> @?/rdbms/admin/utlrp.sql

step 9: check the count of invalid obejcts and database components versions and status before going for upgrade.

select owner,count(*) from dba_objects where status !='VALID' group by owner;
select * from dba_registry;

rerun the utlrp.sql script if needed.


11.2.0.2 Preupgrade steps
---------------------------------------------------------------------------
>>>>>>> Note: we can execute the below steps from the 9.2.0.8 oracle home. <<<<<<<<<<<<<

Step 1:  Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] -- refer metalink

Log in as oracle and copy the file utlu112i_2.sql from the 11gR2
ORACLE_HOME/rdbms/admin directory to a directory outside of the
Oracle Home.
Execute utlu112i_2.sql and spool the output to a file. Examine the output
of the upgrade information tool.

SQL> @utlu112i_2

check the recommendations and follow.
From this step it differs for Manual upgrade and upgrade using DBUA.

For manual upgrade follow the below steps.

Step 2: Mandatory steps to be followed before upgrade

CREATE TABLESPACE sysaux DATAFILE '/prd/HK/strauss/admin/sysaux01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Step 3: drop the perfstat and rbs tablespace which is not needed for upgrade.

> drop tablespace perfstat including contents and datafiles;
> drop tablespace rbs including contents and datafiles;
select * from v$tablespace;

Step 4: Convert any dictionary managed tablespace to localy managed tablespace - refer metalink document -> How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace? [ID 735978.1]

SQL>  select tablespace_name,extent_management from dba_tablespaces;
SQL>  SELECT tablespace_name, status FROM dba_tablespaces;

SQL>SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

SQL>SELECT s.segment_name, s.tablespace_name, t.extent_management FROM   dba_segments s, dba_tablespaces t WHERE  s.tablespace_name = t.tablespace_name
AND  s.segment_type = 'ROLLBACK'

SQL>SELECT tablespace_name, status FROM  dba_tablespaces WHERE  contents = 'UNDO';

 >>  Make all the tablespaces readonly before doing conversion of system tablespace aprat from temp.

ALTER TABLESPACE ITRADE_DAT READ ONLY;
ALTER TABLESPACE ITRADE_IDX READ ONLY;
ALTER TABLESPACE IBROKER_DAT READ ONLY;
ALTER TABLESPACE IBROKER_IDX READ ONLY;
ALTER TABLESPACE SYSAUX  READ ONLY;

SELECT logins  FROM   v$instance

ALTER SYSTEM ENABLE RESTRICTED SESSION;

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

SELECT extent_management FROM   dba_tablespaces WHERE  tablespace_name = 'SYSTEM';

ALTER SYSTEM DISABLE RESTRICTED SESSION;

ALTER TABLESPACE ITRADE_DAT READ WRITE;
ALTER TABLESPACE ITRADE_IDX READ WRITE;
ALTER TABLESPACE IBROKER_DAT READ WRITE;
ALTER TABLESPACE IBROKER_IDX READ WRITE;
ALTER TABLESPACE SYSAUX  READ WRITE;

for time zone check the values and update as per the recomendation -- refer metalink

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- optional

Step 5: Do all the recomendations provided by @utlu112i_2 script. -- mandatory

Step 6: connect roles is obsolete in 11g please use below query and grant privilege as needed

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', 'OLAPDBA', 'OLAPSVR','OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN',
'MDDATA','SI_INFORMTN_SCHEMA','XDB', 'ODM');

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT';

>> The below query generate grant statements for users who has connect role

select 'grant create view,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE,CREATE DATABASE LINK 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', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA', 'XDB', 'ODM');


>>> check all the necessary recomendations are followed and make sure there is nor issues.

Step 7: gather stats for all schemas which has stale statistics

>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);

Step 8: Copy the parameter file to the 11.2.0.2 location and change the necessary parameters.

cp /prd/strauss/oradump/9.2.0.8/dbs/spfilestrasprd.ora  /prd/strauss/oracle/11.2.0/dbs/initstrasprd.ora

check - > parameters to be added/changed in 11.2.0.2
arhcivelog location
controlfile locaton
--------------------------------------------------------
Note :
set shared pool size to at least 448M
set COMPATIBLE parameter to 10.1.0 until after the upgrade has been completed successfully
----------------------------------------------------------------
*.compatible='11.2.0'
*.DB_RECOVERY_FILE_DEST_SIZE=1024M
*.db_recovery_file_dest='/usr/oracle/upgrade11G/fast_recovery_area'   - need to change ,check the current environment
*.diagnostic_dest='/prd/stss/strasprd/data01/diag'
*.log_archive_format='strasprd_T%T_S%S%r.arc'
*.memory_max_target=13G
*.memory_target=13G
*.optimizer_mode='FIRST_ROWS_1000'
*.optimizer_secure_view_merging=FALSE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=600
*.undo_tablespace='UNDOTBS'
*.WORKAREA_SIZE_POLICY='auto'
*.job_queue_processes=1


Step 9:Shutdown the database and listener in 9.2.0.8 

shutdown immediate
lsnrctl stop LISTENER_STRASPRD


11g Upgrade steps:
------------------------------------------------------------

Step 1: set the environment variables for 11.2.0.2

export ORACLE_HOME=/prd/strauss/oracle/11.2.0.2
export PATH=/prd/strauss/oracle/11.2.0.2/bin:$PATH
export LD_LIBRARY_PATH=/prd/strauss/oracle/11.2.0.2/lib -  optional try if errors
export LD_LIBRARY_PATH_64=/prd/strauss/oracle/11.2.0.2/lib -  optional try if errors

cd $ORACLE_HOME
export ORACLE_SID=strasprd

sqlplus -v
sqlplus /nolog
sqlplus / as sysdba

sql>startup upgrade
SQL> @/prd/strauss/oracle/11.2.0/rdbms/admin/catupgrd.sql

sql> shutdown immediate
sql> startup

11g post upgrade steps :
------------------------------------------

07:31:35 SQL> alter system set optimizer_mode='FIRST_ROWS_1000' scope=spfile;

System altered.

Elapsed: 00:00:00.01
07:32:03 SQL> alter system set optimizer_secure_view_merging=FALSE scope=spfile;

System altered.

Elapsed: 00:00:00.00
07:32:08 SQL> alter system set compatible='11.2.0' scope=spfile;

System altered.

Elapsed: 00:00:00.01
07:32:17 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:32:26   2  07:32:26   3  07:32:26   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:32:31 SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         1

Elapsed: 00:00:00.01
07:32:40 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
07:32:45 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:32:50 SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         1

Elapsed: 00:00:00.00
07:35:59 SQL> exec DBMS_DST.BEGIN_PREPARE(11);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
07:37:30 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:37:37   2  07:37:37   3  07:37:37   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

Elapsed: 00:00:00.00
07:37:38 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:38:28   2  07:38:28   3  07:38:28   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              PREPARE

Elapsed: 00:00:00.00
07:38:30 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.09
07:38:43 SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.01
07:38:57 SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.02
07:39:07 SQL> set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/07:39:25 SQL> 07:39:25   2  07:39:25   3  07:39:25   4  07:39:25   5  07:39:25   6  07:39:25   7

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.66
07:39:28 SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

Elapsed: 00:00:00.00
07:39:44 SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.00
07:40:01 SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
07:44:20 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:44:29   2  07:44:29   3  07:44:29   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:44:30 SQL> shutdown immediate
Database closed.
Database dismounted.
07:46:18 SQL> set serveroutput on
07:46:19 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
07:46:26   2  07:46:26   3  07:46:26   4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         1
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.02
07:46:26 SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:00:00.19
07:46:43 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.03
07:46:52 SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.02
07:46:59 SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.01
07:47:07 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.01
07:47:17 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:47:26 SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
07:47:42 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:48:03   2  07:48:03   3  07:48:03   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       1
DST_UPGRADE_STATE              UPGRADE

Elapsed: 00:00:00.01
07:48:04 SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

Elapsed: 00:00:00.36
07:48:14 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
07:48:55 SQL> startup
ORACLE instance started.

Total System Global Area 1.2831E+10 bytes
Fixed Size                  2160072 bytes
Variable Size            6375344696 bytes
Database Buffers         6442450944 bytes
Redo Buffers               11247616 bytes
Database mounted.
Database opened.
07:49:20 SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
Session altered.

Elapsed: 00:00:00.02
07:49:21 SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;alter session set "_with_subquery"=materialize
                                             *
ERROR at line 1:
ORA-00911: invalid character


Elapsed: 00:00:00.00
07:49:26 SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
07:49:33 SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
07:49:37 SQL> set serveroutput on
VAR numfail number
07:49:46 SQL> 07:49:46 SQL> BEGIN
07:49:46   2  DBMS_DST.UPGRADE_DATABASE(:numfail,
07:49:46   3  parallel => TRUE,
07:49:46   4  log_errors => TRUE,
07:49:46   5  log_errors_table => 'SYS.DST$ERROR_TABLE',
07:49:46   6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
07:49:46   7  error_on_overlap_time => FALSE,
07:49:46   8  error_on_nonexisting_time => FALSE);
07:49:46   9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
07:49:46  10  END;
07:49:46  11  /
Failures:0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
07:49:48 SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/07:50:15 SQL> 07:50:15   2  07:50:15   3  07:50:15   4  07:50:15   5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
07:50:17 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;07:50:31   2  07:50:31   3  07:50:31   4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
07:50:32 SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

Elapsed: 00:00:00.01
07:50:41 SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
         1

Elapsed: 00:00:00.00
07:50:54 SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;
1 row updated.

Elapsed: 00:00:00.01
07:51:12 SQL>

Commit complete.

sql> @/prd/strauss/oracle/11.2.0.2/rdbms/admin/utlrp.sql


For Upgrade using DBUA follow the below steps.

1. start the database and listener in 9.2.0.8 home, the database should be in openmode in 9.2.0.8.

2. Add the entry in the /var/opt/oracle/oratab file with the 9.2.0.8 home.

3.Now set the oracle home for New oracle 11g

4.start the DBUA.

5. The DBUA should recognize the 9208 database and show the database for upgrading.

6. Proceed with next as per the requirment.

Note: For upgrade using DBUA you no need to do any preupgrade steps like creating SYSAUX and changing parameters or Database timzone upadte, DBUA automaticaly does all these while upgrading.

You need to take care of the connect privileges since its deprecated in 11g.


Regards,
Navaneeth