Steps for upgrading oracle from 10 Gb to 11 GB

Steps for upgrading oracle from 10 Gb to 11 GB Database Old Version: 10.2.0.4
New database version: 11.2.0.2
OS: Solaris 10
Reference:Complete checklist for manual upgrades to 11gr2 [ID 837570.1]
Part 1-install 11gr2 Software
Here Software Of Install For more information, see the relevant documentation.
Note: If you want to add the latest PSU or CPU on 11gr2, you can add PSU or CPU on the software level first, so that you do not need to run catbundle twice. SQL to reduce the downtime.
Part 2-Preliminary check
1. Before the upgrade, make sure that all components and objects are valid:
Select substr (comp_name,) comp_name, status, substr (version,) version from dba_registry order by comp_name; -- for components
Select substr (object_name,) object_name, substr (owner,) Owner, object_type from dba_objects where status = 'invalid' order by owner, object_type; -- target object
If there is an invalid object, run utlrp. SQL to re-compile the object.
2. Make sure that there are no repeated objects in 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 preceding statement can only return the following four records:
Object_name object_type
-------------------------------------------------------
Dbms_repcat_auth package body
Dbms_repcat_auth package
AQ $ _ schedules_primary Index
AQ $ _ schedules Table
If other records are returned, you must delete the duplicate records according to the following document:
How to clean up duplicate objects owned by sys and system schema [ID 1030426.6]
Part 3-Pre-Upgrade
Step 1.
From 11gr2 Oracle Copy the following files to a temporary folder in home:
$ ORACLE_HOME/rdbms/admin/utluw. I. SQL

Step 2.
Log on 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 very important. The subsequent steps should be modified based on the content of the file, so it must be retained.

Step 3.
You can download the script from the following document.Dbupgdiag. SQL:
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 an invalid object, run the following command to re-compile the invalid object: $ CD $ ORACLE_HOME/rdbms/admin
$ Sqlplus "/As sysdba"
SQL> @ utlrp. SQL

Step 4.
Since 10.2, the connect role has fewer permissions, So if you upgrade from 10.211gAfter the upgrade, you need to re-grant the missing permissions. However, if the upgrade is from 10.2 or later to 11 GB, you do not need to re-grant permissions, this example is upgraded from 10.2.0.4 to 11g, so this step is not required.

Step 5.
Generate the script for rebuilding dblink, in case the database needs to be downgraded. Like step 4, this example is upgraded from 10.2.0.4 to 11g, so this step is not required.

Step 6.
Check the timezone version. For details, refer:
Actions for DST updates when upgrading to or applying the 11.2.0.2 patchset [ID 1201253.1]
Note: The 11g software has a timezone of version 1-14.
Check the current timezone version first:
SQL> Conn/As sysdba
Connected.
SQL> select version from V $ timezone_file;
According to the current timezone version, there are three more situations:
1) equal to 14: This version is already required for the 11g version, so there is no need to do anything before and after the upgrade. This situation is rare.
2) higher than 14: prior to the upgrade, the 11g software must be installed with the DST patch of the timezone version, which is rare.
3) less than 14: this is the case in most cases. You do not need to patch the 11g software before the upgrade. After the upgrade, You need to upgrade the timezone to 14 at the database level. For details, refer to the subsequent 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 yes, you do not need to do anything. If not, you will be miserable. Follow the long article below to do it step by step:
The national character set (nls_nchar_characterset) in Oracle 9i,
10gAnd 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 enabled the vault, you must disable the vault under the 11gr2 software and enable the vault after the upgrade is complete. Otherwise, an error is reported during the upgrade.

Step 10.
Back up Enterprise Manager Database ControlDataBecause em is not used in this example, this step is not required.

Step 11.
Configure the network ACL's, which is not required in this example.

Step 12.
Use the following statement to generate a script for data dictionary analysis (as sysdba): Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Sets 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
Run the Script: $ sqlplus "/As sysdba"
SQL> @ $ ORACLE_HOME/rdbms/admin/utlvalid. SQL
SQL> @ analyze. SQL

Step 13.
Make sure that all snapshots have been successfully refreshed and replication has been disabled: Select distinct (trunc (last_refresh ))
From dba_snapshot_refresh_times;

Step 14.
Make sure that no file requires media.Restore:
Select * from V $ recover_file;
The preceding statement does not return the correct result.

Step 15.
Make sure that no files are running onBackupMode:
Select * from V $ backup where status! = 'Not active ';
The preceding statement does not return the correct result.

Step 16.
Solve distributed transactions.
First, check whether there are distributed transactions: SQL> select * From dba_2pc_pending;

If any result is returned, SQL> select local_tran_id
From dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry ('');
SQL> commit;

Step 17.
Check whether a standby database exists: 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 any result is returned, ensure that standby and primary are in the synchronous state before the upgrade.

Step 18.
Disable all batch and cron jobs

Step 19.
Make sure that the default tablespaces of SYS and system are system: SQL> select username, default_tablespace
From dba_users
Where username in ('sys ', 'system ');

If not, use the following statement to change it 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 created under the Sys user and the system tablespace: SQL> select owner, tablespace_name
From dba_tables
Where table_name = 'aud $ ';

If not, modify the settings accordingly.

Step 21.
Check whether an SSL user with external authentication exists:
SQL> select name from SYS. User $
Where ext_username is not null
And Password = 'global ';
If yes, perform step 34 after the upgrade.

Step 22.
Write down the location of data files, online log files, and control files: SQL> select name from V $ controlfile;
SQL> select file_name from dba_data_files;
SQL> select group #, member from V $ logfile;

Back up files such as listener. ora, tnsnames. ora, and sqlnet. ora.

Step 23.
Stop listener: $ LSNRCTL stop

Stop other executable tasksProgram, Such as dbconsole and ISQLPLUS $ emctl stop dbconsole
$ Isqlplusctl stop

Step 24.
Close Database: $ sqlplus "/As sysdba"
SQL> shutdown immediate;

Then perform a cold backup for the entire database.

Step 25.
Use a 10g pfile as the template, and create a new pfile for 11G according to the suggestions in upgrade_info.log generated by step 2.

Step 26.
If the database is originally running in archive mode, it is recommended to change it to noarchive first, which can reduce the upgrade downtime and then re-Change it back to archive mode after the upgrade is successful.

Step 27.
This step is for Windows systems. In this example, It is skipped.

Part 4-Upgrade

Step 28.
The pre-upgrade check steps are basically completed. Before running the upgrade script, you need to change the relevant parameters 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>

Modify the content in oratab to point it 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 the preparations above are for this step to be successfully executed. First, the database goes to the upgrade status: $ CD $ ORACLE_HOME/rdbms/admin
$ Sqlplus "/As sysdba"
SQL> startup upgrade

Run the upgrade Script: SQL> set echo on.
SQL> spool upgrade. Log
SQL> @ catupgrd. SQL
SQL> spool off

This script lasts for about 1.5 hours, and the database is automatically closed at the end of the script. After the upgrade script is run, run the following script to check the database status: $ sqlplus "/As sysdba"
SQL> startup
SQL> @ utlu112s. SQL

If the report contains errors, please refer to the relevant documentation to solve the problem and run the following Script: SQL> @ catuppst. SQL

The preceding upgrade script runs in Upgrade mode. It mainly performs upgrade in open mode and does not take much time.
Then re-compile the invalid object: SQL> @ utlrp. SQL

Finally, runDbupgdiag. SQL ensures that the database is good.

Part 5-post-Upgrade

Step 30.
Modify listener. ora to enable listener to execute the new 11g home, and then restart listener: LSNRCTL start.

Step 31.
Check again that the environment variables set in step 28 point to the new 11g home.

Step 32.
Timezone Database Upgrade.
Note: whether to perform this step is related to the check result in Step 6. This step is required only when the timezone version is earlier than 14.
Main reference: Updating the rdbms dst version in 11gr2 (11.2.0.1 and up) using dbms_dst [ID 977512.1]
1) preparations before timezone upgrade:
Check the current timezone version first:
Conn/As sysdba
Select version from V $ timezone_file;
Select property_name, substr (property_value, 1, 30) 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 begin the preparation:
Alter session set "_ with_subquery" = materialize;
Exec dbms_dst.begin_prepare (14) ;
Then check the preparation status:
Select property_name, substr (property_value, 1, 30) 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 return results:
Select * From SYS. dst $ affected_tables;
Select * From SYS. dst $ error_table;
Select * From SYS. dst $ error_table where error_number = '000000 ';
Select * From SYS. dst $ error_table where error_number = '000000 ';
Select * From SYS. dst $ error_table where error_number not in ('20140901', '20160901 ');
-- End prepare window, the rows above will stay in those tables.
Exec dbms_dst.end_prepare;
-- Check if this is ended
Select property_name, substr (property_value, 1, 30) 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) 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 (14 );
Select property_name, substr (property_value, 1, 30) 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 results:
Select owner, table_name, upgrade_in_progress from all_tstz_tables where upgrade_in_progress = 'yes ';
Restart the database:
Shutdown immediate
Startup
Update 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 is no error, the upgrade is completed:
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, 30) 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 external SSL users.
This step is ignored because it is upgraded from 10.2 to 11 GB.

Step 35.
If you disable vault in Step 9, you must re-enable it in this step.
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.
You can ignore this vulnerability when locking the System user.

Step 39.
Upgrade Oracle Text, which can be ignored.

Step 40.
Upgrade Oracle clusterware, which can be ignored.

Step 41.
Configure em, which can be ignored.

Finally, remember to modify the compatible parameter:

SQL> alter system set compatible = '11. 2.0 'scope = spfile;