Upgrading 11GR1 to 11GR2, PFB steps :-
Preupgrade steps :- Run the utlu112i.sql in 11GR1 oracle home.
PFB Output of utlu112i.sql script
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 6 04:54:58 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-06-2012 04:55:38
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 11.1.0.6.0
--> compatible: 11.1.0.0.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1049 MB
.... AUTOEXTEND additional space required: 349 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 909 MB
.... AUTOEXTEND additional space required: 308 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 462 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Oracle Ultra Search [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.1.0.6.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... SYSMAN
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 33 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
. PL/SQL procedure successfully completed.
Purge recyclebin of database.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
Gather Schema and Database statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
PL/SQL procedure successfully completed.
SQL> exit
Remove Oracle EM database control.
[cognos@rac1 ~]$ export ORACLE_SID=orcl
[cognos@rac1 ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 7, 2012 12:20:43 PM
EM Configuration Assistant, Version 11.1.0.5.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 SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 7, 2012 12:20:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at E:\app\svijay7\cfgtoollogs\emca\test\emca_2012_03_07_12_20_43.log.
Mar 7, 2012 12:20:55 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 7, 2012 12:21:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 7, 2012 12:24:23 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 7, 2012 12:24:36 PM
Oracle EM successfully removed.
Shut Down your Oracle 11GR1 Database.
Copy your init.ora file of 11GR1 database to 11GR2 Oracle home dbs folder
Edit the Pfile by increasing java pool size to 64 MB, audit_file_dest, diagnostic_dest and db_recovery_file_dest parameters according to the new 11GR2 oracle home.
Upgrading Database to 11GR2
Invoke sqlplus from 11GR2 Oracle Home
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/u04/app/cognos/dbs/initpfile.ora';
ORACLE instance started.
Total System Global Area 602619904 bytes
Fixed Size 1334828 bytes
Variable Size 163578324 bytes
Database Buffers 432013312 bytes
Redo Buffers 5693440 bytes
Database mounted.
Database opened.
(If you are upgrading this in windows server then you will get below mentioned error)
SQL> spool F:\11gr2_upgrade.log (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "<procedure/function name>", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Steps to recover from this error in windows server
a. Stop all Oracle Services
C:\Documents and Settings\amit>NET STOP OracleServiceorcl
The OracleServiceorcl service is stopping.
The OracleServiceorcl service was stopped successfully.
b. Delete Oracle service using ORADIM binary from which the database is upgraded
C:\Documents and Settings\amit>oradim -delete -SID orcl
Instance deleted.
C:\Documents and Settings\amit>ORADIM -NEW -SID orcl -INTPWD sys -STARTMODE manual
Instance created.
After this again run the below upgrade script :-
@\u02\app\cognos\product\11.2.0\dbhome_2\rdbms\admin\catupgrd.sql
If you are upgrading this in Unix server then you will get below mentioned Output
SQL> spool F:\11gr2_upgrade.log (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql
It will take more than an hour to finish the upgrade process and database will be in a shutdown stage.
Post Upgrade steps:-
Start the database and run the post upgrade utility
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u04/initorcl.ora'
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 06-10-2012 10:40:44
.
Component Status Version HH:MM:SS
.
Oracle Server . VALID 11.2.0.1.0 00:17:39
JServer JAVA Virtual Machine . VALID 11.2.0.1.0 00:06:28
Oracle Workspace Manager . VALID 11.2.0.1.0 00:00:45
OLAP Analytic Workspace . VALID 11.2.0.1.0 00:01:53
OLAP Catalog . VALID 11.2.0.1.0 00:01:58
Oracle OLAP API . VALID 11.2.0.1.0 00:00:29
Oracle XDK . VALID 11.2.0.1.0 00:01:11
Oracle Text . VALID 11.1.0.6.0 00:00:00
Oracle XML Database . VALID 11.2.0.1.0 00:03:52
Oracle Database Java Packages . VALID 11.2.0.1.0 00:00:47
Oracle Multimedia . VALID 11.2.0.1.0 00:09:02
Spatial . VALID 11.2.0.1.0 00:05:45
Oracle Expression Filter . VALID 11.2.0.1.0 00:00:13
Oracle Rule Manager . VALID 11.2.0.1.0 00:00:10
Gathering Statistics. 00:04:29
Total Upgrade Time: 01:34:54
PL/SQL procedure successfully completed.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Run catuppst.sql to perform post-upgrade actions.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catuppst.sql
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_BGN 2012-10-06 12:40:50
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
0 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
0 rows updated.
Table dropped.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows created.
Commit complete.
0 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_END 2012-10-06 12:41:03
Run the utlrp.sql script to recompile any invalid objects.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2012-10-06 12:43:14
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2012-10-06 12:46:49
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Recreate the Listener service
Recreate the listener service from new oracle home.
Check the Current version of the Oracle time Zone definitions in Upgraded database
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------
------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
(if this is LOWER than 11 (for 11.2.0.1 ) or 14 (for 11.2.0.2 and 11.2.0.3) then it is *recommended* to upgrade the timezone version)
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(11);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[cognos@rac1 ~]$
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:03:59 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Update the Database DST version
SQL> startup upgrade pfile='/u04/initorcl.ora'
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> create spfile='+AMIT_R1' from pfile='/u04/initorcl.ora';
File created.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
BEGIN DBMS_DST.BEGIN_UPGRADE(11); END;
*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1054
ORA-06512: at line 1
SQL> EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE
-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property.
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_CONFIG_ACTIVITIES YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_OPERATION YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_ASSIGNMENT YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
IX AQ$_STREAMS_QUEUE_TABLE_S YES
IX AQ$_STREAMS_QUEUE_TABLE_L YES
IX AQ$_ORDERS_QUEUETABLE_S YES
IX AQ$_ORDERS_QUEUETABLE_L YES
19 rows selected.
These 19 tables need to be upgraded
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[cognos@rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:24:53 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.SQL> startup
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
Previeous 19 tables will be upgraded now
SQL>
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
-- if there where no failures then end the upgrade.
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_11.dat 11
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
######################### Upgrade to 11gR2 is complete ##########################
I hope this article helped you.
Regards,
Amit Rath
- Install 11gR2 software in a new Oracle home.
- Run the pre-upgrade steps in current Oracle (11gR1) version and make the necessary modification as suggested by pre upgrade utility.
- Upgrade Oracle version to 11gR2 using manual upgrade method.
- After upgrade process has completed, run the post-upgrade checks.
- Recreate the listener using the new Oracle version.
- Update the time zone to the appropriate version.
Preupgrade steps :- Run the utlu112i.sql in 11GR1 oracle home.
PFB Output of utlu112i.sql script
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 6 04:54:58 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-06-2012 04:55:38
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 11.1.0.6.0
--> compatible: 11.1.0.0.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1049 MB
.... AUTOEXTEND additional space required: 349 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 909 MB
.... AUTOEXTEND additional space required: 308 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 462 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Oracle Ultra Search [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.1.0.6.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... SYSMAN
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 33 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
. PL/SQL procedure successfully completed.
Purge recyclebin of database.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
Gather Schema and Database statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
PL/SQL procedure successfully completed.
SQL> exit
Remove Oracle EM database control.
[cognos@rac1 ~]$ export ORACLE_SID=orcl
[cognos@rac1 ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 7, 2012 12:20:43 PM
EM Configuration Assistant, Version 11.1.0.5.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 SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 7, 2012 12:20:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at E:\app\svijay7\cfgtoollogs\emca\test\emca_2012_03_07_12_20_43.log.
Mar 7, 2012 12:20:55 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 7, 2012 12:21:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 7, 2012 12:24:23 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 7, 2012 12:24:36 PM
Oracle EM successfully removed.
Shut Down your Oracle 11GR1 Database.
Copy your init.ora file of 11GR1 database to 11GR2 Oracle home dbs folder
Edit the Pfile by increasing java pool size to 64 MB, audit_file_dest, diagnostic_dest and db_recovery_file_dest parameters according to the new 11GR2 oracle home.
Upgrading Database to 11GR2
Invoke sqlplus from 11GR2 Oracle Home
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/u04/app/cognos/dbs/initpfile.ora';
ORACLE instance started.
Total System Global Area 602619904 bytes
Fixed Size 1334828 bytes
Variable Size 163578324 bytes
Database Buffers 432013312 bytes
Redo Buffers 5693440 bytes
Database mounted.
Database opened.
(If you are upgrading this in windows server then you will get below mentioned error)
SQL> spool F:\11gr2_upgrade.log (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "<procedure/function name>", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Steps to recover from this error in windows server
a. Stop all Oracle Services
C:\Documents and Settings\amit>NET STOP OracleServiceorcl
The OracleServiceorcl service is stopping.
The OracleServiceorcl service was stopped successfully.
b. Delete Oracle service using ORADIM binary from which the database is upgraded
C:\Documents and Settings\amit>oradim -delete -SID orcl
Instance deleted.
C:\Documents and Settings\amit>ORADIM -NEW -SID orcl -INTPWD sys -STARTMODE manual
Instance created.
After this again run the below upgrade script :-
@\u02\app\cognos\product\11.2.0\dbhome_2\rdbms\admin\catupgrd.sql
If you are upgrading this in Unix server then you will get below mentioned Output
SQL> spool F:\11gr2_upgrade.log (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql
It will take more than an hour to finish the upgrade process and database will be in a shutdown stage.
Post Upgrade steps:-
Start the database and run the post upgrade utility
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u04/initorcl.ora'
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 06-10-2012 10:40:44
.
Component Status Version HH:MM:SS
.
Oracle Server . VALID 11.2.0.1.0 00:17:39
JServer JAVA Virtual Machine . VALID 11.2.0.1.0 00:06:28
Oracle Workspace Manager . VALID 11.2.0.1.0 00:00:45
OLAP Analytic Workspace . VALID 11.2.0.1.0 00:01:53
OLAP Catalog . VALID 11.2.0.1.0 00:01:58
Oracle OLAP API . VALID 11.2.0.1.0 00:00:29
Oracle XDK . VALID 11.2.0.1.0 00:01:11
Oracle Text . VALID 11.1.0.6.0 00:00:00
Oracle XML Database . VALID 11.2.0.1.0 00:03:52
Oracle Database Java Packages . VALID 11.2.0.1.0 00:00:47
Oracle Multimedia . VALID 11.2.0.1.0 00:09:02
Spatial . VALID 11.2.0.1.0 00:05:45
Oracle Expression Filter . VALID 11.2.0.1.0 00:00:13
Oracle Rule Manager . VALID 11.2.0.1.0 00:00:10
Gathering Statistics. 00:04:29
Total Upgrade Time: 01:34:54
PL/SQL procedure successfully completed.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Run catuppst.sql to perform post-upgrade actions.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catuppst.sql
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_BGN 2012-10-06 12:40:50
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
0 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
0 rows updated.
Table dropped.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows created.
Commit complete.
0 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_END 2012-10-06 12:41:03
Run the utlrp.sql script to recompile any invalid objects.
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2012-10-06 12:43:14
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2012-10-06 12:46:49
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Recreate the Listener service
Recreate the listener service from new oracle home.
Check the Current version of the Oracle time Zone definitions in Upgraded database
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------
------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
(if this is LOWER than 11 (for 11.2.0.1 ) or 14 (for 11.2.0.2 and 11.2.0.3) then it is *recommended* to upgrade the timezone version)
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(11);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[cognos@rac1 ~]$
[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:03:59 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Update the Database DST version
SQL> startup upgrade pfile='/u04/initorcl.ora'
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> create spfile='+AMIT_R1' from pfile='/u04/initorcl.ora';
File created.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 11
DST_UPGRADE_STATE PREPARE
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
BEGIN DBMS_DST.BEGIN_UPGRADE(11); END;
*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1054
ORA-06512: at line 1
SQL> EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE
-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property.
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_CONFIG_ACTIVITIES YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_OPERATION YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_ASSIGNMENT YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
IX AQ$_STREAMS_QUEUE_TABLE_S YES
IX AQ$_STREAMS_QUEUE_TABLE_L YES
IX AQ$_ORDERS_QUEUETABLE_S YES
IX AQ$_ORDERS_QUEUETABLE_L YES
19 rows selected.
These 19 tables need to be upgraded
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[cognos@rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:24:53 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.SQL> startup
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.
Database opened.
Previeous 19 tables will be upgraded now
SQL>
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
-- if there where no failures then end the upgrade.
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_11.dat 11
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
######################### Upgrade to 11gR2 is complete ##########################
I hope this article helped you.
Regards,
Amit Rath
Greate pieces. Keep writing such kind of info on your blog.
ReplyDeleteIm really impressed by your site.
Hey there, You have performed an excellent job. I'll definitely digg it and for my part suggest to my friends.
I am sure they will be benefited from this web site.
My weblog ... Reed
very good article
ReplyDelete