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.
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.
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
