Oracle upgrades from 10g to 11g detailed steps ____oracle

Oracle upgrades from 10g to 11g detailed stepsold Database version: 10.2.0.4
Database new version: 11.2.0.2
OS Version: Solaris 10
Reference Documentation:Complete Checklist for Manual upgrades to 11GR2 [ID 837570.1]
Part I-Installing 11GR2 software
Here to Software 'sinstallationIt is not explained in detail, you can refer to the appropriate documentation.
Here is the note: If you want to 11gr2 on the latest PSU or CPU, you can first at the software level PSU or CPU, so you do not have to run two times catbundle.sql, reduce downtime.
Part II-Preliminary examination
1. Before upgrading, make sure all components and objects are valid:
Select substr (comp_name,1,40) comp_name, status, substr (version,1,10) version from Dba_registry Order by Comp_name; --For components
Select substr (object_name,1,40) object_name,substr (owner,1,15) Owner,object_type from dba_objects where status= ' INVALID ' ORDER by Owner,object_type; --For objects
If there are invalid objects, run Utlrp.sql recompile the object.
2. Ensure that there are no duplicate objects under SYS and system:
Select Object_name, object_type from dba_objects where object_name| | Object_type in (select Object_name| | Object_type from dba_objects where Wner = ' SYS ') and Wner = ' SYSTEM ';
The above statement can only return the following 4 records:
object_name object_type
---------------------------------------- ---------------
Dbms_repcat_auth PACKAGE Body
Dbms_repcat_auth PACKAGE
Aq$_schedules_primary INDEX
Aq$_schedules TABLE
If there are other records to return, you must delete the duplicate records according to the following document:
Duplicate Objects owned by SYS and SYSTEM Schema [ID 1030426.6]
Part III-Pre-upgrade work
Step 1.
From the 11GR2Copy the following files to a temporary folder under Oracle home:
$ORACLE _home/rdbms/admin/utlu112i.sql

Step 2.
Log in to the database and run:
$ Sqlplus '/as Sysdba '
Sql> Spool Upgrade_info.log
sql> @utlu112i. sql
Sql> Spool Off
Sql>
The content in the generated Upgrade_info.log is important, and subsequent steps will be modified according to the contents of the file, so be sure to keep it.
Step 3.
You can download the script Dbupgdiag.sql from the following document :
Script. To Collect DB upgrade/migrate diagnostic Information (dbupgdiag.sql) [ID 556610.1]
Run this script:
CD <location of the Script>
$ sqlplus/as SYSDBA
Sql> alter session set nls_language= ' American ';
sql> @dbupgdiag. sql
Sql> exit
If the script reports a invalid object, run the following command to recompile the invalid object:
$ cd $ORACLE _home/rdbms/admin
$ sqlplus "/as SYSDBA"
sql> @utlrp. sql
Step 4.
Since 10.2, the Connect role has become less privileged, so if you are upgrading from 10.2 to11g, you will need to grant the missing permissions after the upgrade, but if you upgrade to 11g from 10.2 and beyond, you do not need to reassign the permissions, and this example is upgraded from 10.2.0.4 to 11g, so this step is not required.
Step 5.
Generate a script to rebuild the Dblink in case the database needs to be degraded. As with step 4, this example is upgraded from 10.2.0.4 to 11g, so it is not required.
Step 6.
Check the timezone version, the main reference:
Actions for DST Updates when upgrading to Or applying the 11.2.0.2 patchset [ID 1201253.1] 
Note: The 11g software has its own version 1-14 timezone.
First check the current timezone version:
Sql> Conn/as SYSDBA
Connected.
Sql>select version from V$timezone_file;
According to the current timezone version, it is divided into three different cases:
1 equals 14: This is already the version required for 11g, so there is no need to do anything before and after the upgrade, which is very rare.
2 above 14: The timezone version of the DST patch must be made available to 11g software before upgrading, which is also rare.
3 below 14: Most of this situation, in the upgrade does not need to be in the 11g software layer patching, after the upgrade to the database level will be timezone upgrade to 14, the specific look at the following steps
Step 7.
Check whether the national character set is UTF8 or AL16UTF16:
Select value from nls_database_parameters where parameter = ' nls_nchar_characterset ';
If it is, then there is nothing to do; if not, then you are miserable, follow the long document Step-by-step:
the National Character Set (nls_nchar_characterset) in Oracle 9i, 10g and 11g [ID 276914.1]
Step 8.
Collect statistics to reduce downtime:
$ sqlplus "/as sysdba"
Sql> EXEC dbms_stats. Gather_dictionary_stats;
Step 9.
If you have open vault, then you need to disable the vault under the 11GR2 software, and so on after the upgrade, then enable Vault, otherwise will be in the upgrade process error.
Step 10.
Backup Enterprise Manager Database ControlDataBecause this example does not use EM, so this step is not required.
Step 11.
Configure the network ACL ' s, which in this case does not require configuration.
Step 12.
Use the following statement to produce the script for the Profiling data dictionary (as SYSDBA):
Set Verify off
Set Space 0
Set Line 120
Set heading off
Set Feedback off
Set pages 1000
Spool Analyze.sql
Select ' Analyze cluster ' | | cluster_name| | ' "Validate structure cascade;
From Dba_clusters
WHERE owner= ' SYS '
UNION
SELECT ' Analyze table ' | | table_name| | ' "Validate structure cascade;
From Dba_tables
WHERE owner= ' SYS '
and partitioned= ' NO '
and (iot_type= ' IoT ' OR Iot_type is NULL)
UNION
SELECT ' Analyze table ' | | table_name| | ' "Validate structure cascade into invalid_rows; '
From Dba_tables
WHERE owner= ' SYS '
and partitioned= ' YES ';
Spool off
The generated script name is: Analyze.sql
Now run the script:
$ sqlplus "/as SYSDBA"
sql> @ $ORACLE _home/rdbms/admin/utlvalid.sql
sql> @analyze. sql
Step 13.
Ensure that all snapshot have been successfully refreshed and that the replication has been closed:
SELECT DISTINCT (TRUNC (Last_refresh))
From Dba_snapshot_refresh_times;
Step 14.
Make sure no files are currently required for media Recovery :
SELECT * from V$recover_file;
The above statement does not return the result is correct.
Step 15.
Make sure that no files are currently running inBackupMode:
SELECT * from V$backup WHERE status is!= ' not ACTIVE ';
The above statement does not return the result is correct.
Step 16.
Resolve distributed transactions.
Check to see if there are any more distributed transactions:
Sql> select * from dba_2pc_pending; If there is a return result, then:
Sql> SELECT local_tran_id
From Dba_2pc_pending;
sql> EXECUTE dbms_transaction.purge_lost_db_entry (");
Sql> COMMIT;
Step 17.
Check to see if there are any standby databases:
SELECT SUBSTR (value,instr (value, ' = ', INSTR (UPPER (value), ' SERVICE ')) +1)
From V$parameter
WHERE name like ' log_archive_dest% ' and UPPER (value) like ' service% '; If there is a return result, make sure that the standby and primary are in sync before you upgrade.
Step 18.
Disable all batch and cron jobs
Step 19.
Make sure that the default table space for user sys and system is system:
Sql> SELECT username, default_tablespace
From Dba_users
WHERE username in (' SYS ', ' SYSTEM '); If not, use the following statement to modify to system:
sql> ALTER user SYS default tablespace SYSTEM;
sql> ALTER User system default Tablespace system;
Step 20.
Make sure that the aud$ table is built under the SYS user and under the system tablespace:
Sql> SELECT Owner,tablespace_name
From Dba_tables
WHERE table_name= ' aud$ '; If not, make the appropriate changes.
Step 21.
Check for externally authenticated SSL users:
sql> SELECT name from sys.user$
WHERE Ext_username is not NULL
and password = ' GLOBAL ';
If yes, then remember to do step 34 after the upgrade.
Step 22.
Note the location of the data file, the online log file, and the control file:
sql> SELECT name from V$controlfile;
Sql> SELECT file_name from Dba_data_files;
Sql> SELECT group#, member from V$logfile; and backup Listener.ora, Tnsnames.ora, Sqlnet.ora and other documents.
Step 23.
Stop listener:
$ lsnrctl Stop stops other executable programs, such as Dbconsole, Isqlplus, etc.
$ emctl Stop Dbconsole
$ isqlplusctl Stop
Step 24.
To close the database:
$ sqlplus "/as sysdba"
sql> shutdown immediate; Then make a cold standby for the whole library.
Step 25.
Create a new pfile for 11g using the 10g pfile as a template, and according to the recommendations in the upgrade_info.log generated by step 2.
Step 26.
If the database is originally running in archive mode, it is best to change to noarchive, which can reduce the upgrade downtime, upgrade successfully and then change back to archive mode.
Step 27.
This step is for Windows systems, and this example skips.
Part Four-upgrade
Step 28.
The check step before the upgrade is basically complete, before you run the upgrade script, you need to change the relevant parameters to point to the new 11g software:
$ export Oracle_home=<location of ORACLE 11.2>
$ export path= $ORACLE _home/bin: $PATH
$ export Oracle_base=<oracle_base set during installation>
Then modify the contents in the Oratab to point to the new 11g home directory:
Sample/etc/oratab
#orcl:/opt/oracle/product/10.2/db_1:n
Orcl:/opt/oracle/product/11.2/db_1:n
Step 29.
All of the previous preparations are for this step to be successful, first the database to the upgrade state:
$ cd $ORACLE _home/rdbms/admin
$ sqlplus "/as SYSDBA"
Sql> startup UPGRADE then starts running upgrade script:
Sql> set echo on
Sql> SPOOL Upgrade.log
sql> @catupgrd. sql
Sql> spool off this script lasts about 1.5 hours, and the end of the script automatically closes the database. After the upgrade script runs, run the following script to check the database status:
$ sqlplus "/as sysdba"
Sql> STARTUP
sql> @utlu112s. SQL If the report contains errors, consult the documentation to resolve them until there is no error, and then run the following script:
Sql> @catuppst. The upgrade script in front of SQL is run in upgrade mode, the script is to do some upgrades in open mode, it doesn't take much time.
Then recompile the invalid object:
sql> @utlrp. SQL Finally, run again in step 3Dbupgdiag.sql, make sure the database is good.
Part V-post-upgrade work
Step 30.
Modify the Listener.ora so that listener executes the new 11g home and then restarts listener:
Lsnrctl start

Step 31.
Re-check that the environment variable set in step 28 does point to the new 11g home.
Step 32.
Upgrade at the TIMEZONE database level.
Note: This step is performed in relation to the result of the check in Stage 6, which is required only if the timezone version is less than 14 o'clock.
Main reference:Updating the RDBMS DST version in 11gr2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
1) Pre-timezone upgrade preparation work:
First check the current timezone version:
Conn/as SYSDBA
SELECT version from V$timezone_file;
SELECT property_name, SUBSTR (Property_value, 1) value from Database_properties WHERE property_name like ' dst_% ' order by Property_name; 
A typical output is:
Property_name VALUE
------------------------------ ------------------------------
Dst_primary_tt_version 4
Dst_secondary_tt_version 0
Dst_upgrade_state NONE
Then get ready to work:
alter session Set "_with_subquery" =materialize;
EXEC dbms_dst. Begin_prepare (April);
Then check the readiness status:
SELECT property_name, SUBSTR (Property_value, 1) value
From Database_properties
WHERE property_name like ' dst_% '
Order by Property_name; 
A typical output is:
Property_name VALUE
------------------------------ ------------------------------
Dst_primary_tt_version 4
Dst_secondary_tt_version 14
Dst_upgrade_state PREPARE
--Truncate logging tables if they exist.
TRUNCATE TABLE SYS. dst$trigger_table;
TRUNCATE TABLE Sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table; 
--Log affected data
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;
/
None of the following statements can have return results:
SELECT * from Sys.dst$affected_tables;
SELECT * from sys.dst$error_table; 
SELECT * from sys.dst$error_table where error_number= ' 1883 ';
SELECT * from sys.dst$error_table where error_number= ' 1878 ';
SELECT * from sys.dst$error_table where error_number isn't in (' 1878 ', ' 1883 ');
--End Prepare window, the rows above would stay in those tables.
EXEC DBMS_DST. End_prepare; 
--Check if is ended
SELECT property_name, SUBSTR (Property_value, 1) value
From Database_properties
WHERE property_name like ' dst_% '
Order by Property_name; 
A typical output is:
Property_name VALUE
------------------------------ ------------------------------
Dst_primary_tt_version 4
Dst_secondary_tt_version 0
Dst_upgrade_state NONE
2) really start to upgrade timezone
Conn/as SYSDBA
Shutdown immediate;
startup upgrade;
Set Serveroutput on
Purge Dba_recyclebin;
TRUNCATE TABLE SYS. dst$trigger_table;
TRUNCATE TABLE Sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
Alter session Set "_with_subquery" =materialize;
EXEC DBMS_DST. Begin_upgrade (); 
SELECT property_name, SUBSTR (Property_value, 1) value
From Database_properties
WHERE property_name like ' dst_% '
Order BY Property_name;
A typical output is:
Property_name VALUE
------------------------------ ------------------------------
Dst_primary_tt_version 14
Dst_secondary_tt_version 4
Dst_upgrade_state UPGRADE
The following statement should not return the result:
SELECT OWNER, TABLE_NAME, upgrade_in_progress from All_tstz_tables where upgrade_in_progress= ' YES '; 
To restart the database:
shutdown Immediate
Startup 
To upgrade related table:
Alter session Set "_with_subquery" =materialize;
Set Serveroutput on
VAR numfail Number
BEGIN
Dbms_dst. Upgrade_database (: Numfail,
Parallel => TRUE,
Log_errors => TRUE,
Log_errors_table => ' SYS. Dst$error_table ',
Log_triggers_table => ' SYS. Dst$trigger_table ',
Error_on_overlap_time => FALSE,
Error_on_nonexisting_time => FALSE);
Dbms_output. Put_Line (' Failures: ' | |: Numfail);
End;
/ 
If there are no errors, end the upgrade:
VAR fail number
BEGIN
Dbms_dst. End_upgrade (: fail);
Dbms_output. Put_Line (' Failures: ' | |: fail);
End;
/ 
Last check:
SELECT property_name, SUBSTR (Property_value, 1) value
From Database_properties
WHERE property_name like ' dst_% '
Order BY Property_name;
Typical output is:
Property_name VALUE
------------------------------ ------------------------------
Dst_primary_tt_version 14
Dst_secondary_tt_version 0
Dst_upgrade_state NONE
SELECT * from V$timezone_file;
FILENAME VERSION
-------------------- ----------
Timezlrg_14.dat 14
Step 33.
This step can be omitted.
Step 34.
Upgrade an externally authenticated SSL user.
Because this example is upgraded from 10.2 to 11g, this step can be ignored.
Step 35.
If you closed the vault in step 9, you must re-enable it at this stage.
Note 453903.1-enabling and disabling Oracle Database Vault in UNIX
Step 36.
Ignore
Step 37.
Create SPFile:
Sql> create SPFile from Pfile;
Step 38.
Locked system user, can be ignored.
Step 39.
Upgrade Oracle Text to be ignored.
Step 40.
Upgrading Oracle Clusterware can be ignored.
Step 41.
Configure EM to be ignored.
Finally, remember to modify the compatible parameter:



Sql> ALTER SYSTEM SET compatible = ' 11.2.0 ' scope=spfile;