Shulou(Shulou.com)06/02 Report--

Old version of database: 10.2.0.4

New version of the database: 11.2.0.2

OS version: Solaris 10

Reference document: Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

Part I-install 11gR2 software

Here the installation of the software will not be described in detail, you can refer to the corresponding documentation.

One thing to note here: if you want to type the latest PSU or CPU on 11gr2, you can type PSU or CPU at the software level first, so you don't have to run catbundle.sql twice and reduce downtime.

Part II-preliminary examination

1. Before upgrading, make sure that 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 object

If there is an object for invalid, run utlrp.sql to recompile the object.

two。 Make sure 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 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, duplicate records must be deleted according to the following document:

How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema [ID 1030426.6]

Part III-pre-upgrade work

Step 1.

Copy the following files from Oracle Home in 11gR2 to a temporary folder:

$ORACLE_HOME/rdbms/admin/utlu112i.sql

Step 2.

Log in to the database and run:

Normal 0 false false false EN-US ZH-CN X-NONE spool off

SQL >

The content in the generated upgrade_info.log is very important, and the next steps should be modified according to the contents of the file, so be sure to keep it.

Step 3.

The script dbupgdiag.sql can be downloaded from the following document:

Script. To Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

Run this script:

Cd

$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 recompile the invalid object:

$cd $ORACLE_HOME/rdbms/admin

$sqlplus "/ as sysdba"

SQL > @ utlrp.sql

Step 4.

From 10.2 onwards, the permissions of the CONNECT role become less, so if you are upgrading from 10.2 to 11g before the upgrade, you need to re-grant the missing permissions after the upgrade, but if you upgrade from 10.2 and later to 11g, you do not need to re-assign permissions. In this example, you upgrade from 10.2.0.4 to 11g, so you do not need this step.

Step 5.

Generate a script to rebuild the dblink in case the database needs to be degraded. 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. Main references:

Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset [ID 1201253.1]

Note: versions 1-14 of Timezone are already included in 11g software.

Check the current timezone version first:

SQL > conn / as sysdba

Connected.

SQL > SELECT version FROM v$timezone_file

According to the current version of timezone, there are three more situations:

1) equals 14: this is already the required version of 11g, so it is rare that nothing needs to be done before and after the upgrade.

2) higher than 14: it is also rare that 11g software must be patched with this timezone version of DST before upgrading.

3) less than 14: this is the case in most cases. You don't need to patch 11g software before upgrading, but you need to upgrade Timezone to 14 at database level after upgrade. See 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 so, you don't have to do anything; if not, you're miserable. Follow the long document below to do it 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 enabled Vault, you need to disable Vault in 11gR2 software first, and then enable Vault after the upgrade is over, otherwise an error will be reported during the upgrade process.

Step 10.

Back up the Enterprise Manager Database Control Data, which is not required because EM is not used in this example.

Step 11.

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

Step 12.

Use the following statement to produce a script to analyze the 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 name of the generated script 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 has been successfully refreshed and that replication has been closed:

SELECT DISTINCT (TRUNC (last_refresh))

FROM dba_snapshot_refresh_times

Step 14.

Ensure that there are currently no files that require media recovery:

SELECT * FROM v$recover_file

It is correct that the above statement does not return a result.

Step 15.

Make sure that no files are currently running in backup mode:

SELECT * FROM v$backup WHERE status! = 'NOT ACTIVE'

It is correct that the above statement does not return a result.

Step 16.

Solve distributed transactions.

First query whether there are any distributed transactions:

SQL > select * from dba_2pc_pending;. If a result is returned, 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 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 a result is returned, make sure that Standby and Primary are synchronized before upgrading.

Step 18.

Disable all batch and cron jobs

Step 19.

Ensure that the default tablespace for users SYS and SYSTEM is SYSTEM:

SQL > SELECT username, default_tablespace

FROM dba_users

WHERE username in ('SYS','SYSTEM'); if not, change it to SYSTEM with the following statement:

SQL > ALTER user SYS default tablespace SYSTEM

SQL > ALTER user SYSTEM default tablespace SYSTEM

Step 20.

Ensure that the AUD$ table is built under the SYS user and under the SYSTEM tablespace:

SQL > SELECT owner,tablespace_name

FROM dba_tables

If WHERE table_name='AUD$'; is not, modify it accordingly.

Step 21.

Check if there are externally authenticated SSL users:

SQL > SELECT name FROM sys.user$

WHERE ext_username IS NOT NULL

AND password = 'GLOBAL'

If so, remember to do Step 34 after the upgrade.

Step 22.

Write down the location of the data file, online log file, and control file:

SQL > SELECT name FROM v$controlfile

SQL > SELECT file_name FROM dba_data_files

SQL > SELECT group#, member FROM vault log file; and back up listener.ora, tnsnames.ora, sqlnet.ora and other files.

Step 23.

Stop listener:

Lsnrctl stop stops other executable programs, such as dbconsole, isqlplus, etc.

$emctl stop dbconsole

$isqlplusctl stop

Step 24.

Shut down the database:

$sqlplus "/ as sysdba"

SQL > shutdown immediate; then make a cold standby for the whole repository.

Step 25.

Create a new pfile for 11g using 10g of pfile as a template and following 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 it to noarchive first, so as to reduce the downtime of the upgrade, and then change it back to archive mode after the upgrade is successful.

Step 27.

This step is for Windows systems, which is skipped in this example.

Part IV-upgrade

Step 28.

The check steps before the upgrade are basically completed. Before running the upgrade script, you need to change the relevant parameters to point to the new 11g software:

$export ORACLE_HOME=

$export PATH=$ORACLE_HOME/bin:$PATH

$export ORACLE_BASE=

Then modify the contents of 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 the previous preparations are for this step to be executed successfully, first setting the database to the upgrade state:

$cd $ORACLE_HOME/rdbms/admin

$sqlplus "/ as sysdba"

SQL > startup UPGRADE then starts running the upgrade script:

SQL > set echo on

SQL > SPOOL upgrade.log

SQL > @ catupgrd.sql

The script SQL > spool off lasts about 1.5 hours, and the script automatically shuts down the database at the end. After the upgrade script is finished, run the following script to check the database status:

$sqlplus "/ as sysdba"

SQL > STARTUP

SQL > @ utlu112s.sql if the report contains errors, consult the relevant documentation to resolve them, and then run the following script until there are no errors:

The previous upgrade script SQL > @ catuppst.sql runs in upgrade mode. This script mainly does some upgrade actions in open mode and does not take much time.

Then recompile the invalid object:

SQL > @ utlrp.sql finally, run dbupgdiag.sql in Step 3 to make sure the database is good.

Part V-Post-upgrade work

Step 30.

Modify listener.ora so that listener executes the new 11g Home, and then restart listener:

Lsnrctl start

Step 31.

Check again that the environment variable set in Step 28 does point to the new 11g Home.

Step 32.

Upgrade at the Timezone database level.

Note: whether this step is performed is related to the results of the check in Step 6. This step is required only if the version of Timezone is less than 14:00.

Main reference: Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

1) preparation before Timezone upgrade:

Check the current version of timezone 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 start the preparatory work:

Alter session set "_ with_subquery" = materialize

Exec DBMS_DST.BEGIN_PREPARE (14)

Then check the readiness:

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= '1883'

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878'

SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878')

-- 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) really start upgrading 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 a result:

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES'

Restart the database:

Shutdown immediate

Startup

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,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 externally authenticated SSL users.

Since this example is upgraded from 10.2 to 11g, this step can be ignored.

Step 35.

If you turn off Vault in Step 9, you must re-enable it at this step.

Note 453903.1-Enabling and Disabling Oracle Database Vault in UNIX

Step 36.

ignore

Step 37.

Create a spfile:

SQL > create spfile from pfile

Step 38.

Lock the system user, but ignore.

Step 39.

Upgrade Oracle Text, negligible.

Step 40.

Upgrade Oracle Clusterware, negligible.

Step 41.

Configure EM, which can be ignored.

Finally, remember to modify the compatible parameter:

Normal 0 false false false EN-US ZH-CN X-NONE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.