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

Environment

Current environment 10.2.0.4

OS version of linux5.5

New environment database version: 11.2.0.4.17

OS version: linx6.5

one。 Installation

Install the software on the 192.0.2.12 machine

11.2.0.4 and type 11.2.0.4.170418

Note: type the latest PSU on 11.2.0.4

In this way, you don't have to run catbundle.sql twice and reduce downtime.

Second check

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

COMP_NAME STATUS VERSION

JServer JAVA Virtual Machine VALID 10.2.0.4.0

OLAP Analytic Workspace VALID 10.2.0.4.0

OLAP Catalog VALID 10.2.0.4.0

Oracle Data Mining VALID 10.2.0.4.0

Oracle Database Catalog Views VALID 10.2.0.4.0

Oracle Database Java Packages VALID 10.2.0.4.0

Oracle Database Packages and Types VALID 10.2.0.4.0

Oracle Enterprise Manager VALID 10.2.0.4.0

Oracle Expression Filter VALID 10.2.0.4.0

Oracle interMedia VALID 10.2.0.4.0

Spatial VALID 10.2.0.4.0

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

No rows selected-for object

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

III. Work before upgrading

1.

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

$ORACLE_HOME/rdbms/admin/utlu112i.sql

two。

SQL >! Pwd

/ home/oracle10g

SQL > spool upgrade_info.log

SQL > set time on

18:34:03 SQL > set timing on

18:34:09 SQL > @ / u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlu112i.sql

*

Elapsed: 00:00:04.40

18:34:41 SQL > spool off

Save this file upgrade_info.log

3. Check the script

Set timing on

SQL > set time on

19:35:54 SQL > @ / u03/arch/dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: / u03/arch/

Upload db_upg_diag_erpdb_11_Jul_2017_0736.log from "/ u03/arch/" directory

Elapsed: 00:00:00.00

Compilation failure object

SQL > @ utlrp.sql

Four: off-machine recovery

1. Backup database

The script is as follows:

Run {

Crosscheck archivelog all

Crosscheck backup

Delete noprompt obsolete

Delete noprompt expired backup

Allocate channel d1 type disk

Allocate channel d2 type disk

Backup format'/ u03 tag BK full_data' database% Thousand% dice% slots% p.bk'Universe 'full_data' database

Backup format'/ u03 tag BK full_arc' archivelog all delete all input% Thousand% dice% slots% p.arc'Universe 'full_arc' archivelog all delete all input

Backup format'/ u03 tag BK full_ctl' current controlfile% Trout% dice% slots% p.ctl'Universe 'full_ctl' current controlfile

Release channel d1

Release channel d2

}

two。 Orapwerpdb scp the pfile initerpdb.ora and password files to 192.0.2.12

3. Mount the backup file to 192.0.2.12, saving the trouble of copywasting time.

4. Modify pfile file to add

_ optim_dict_stats_at_db_cr_upg = false- this parameter is in order to run catupgrd.sql slowly

5. At 192.0.2.102

Start the database

Startup nomount

Rman target /

Startup nomount

Restore controlfile from'/ u03According to BK Universe 20170712 ERPDBs 12 December 1.ctl'

Run

{

Set newname for datafile 1 to'/ u01 to'/ u01According to oradata asm can be changed to disk group'+ oradata'if it is oracle.

Set newname for datafile 2 to'/ u01qapplash oracle.oradataUniplicerpdbUniplicatesyu02.bdf'

Set newname for datafile 3 to'/ u01qapplash oracleUnixoradataUniplicateerpdbUniple sy03.bdf'

Set newname for datafile 4 to'/ u01qapplash oracleUnixoradataUniplicateerpdbUniple sy04.bdf'

Restore database

Switch datafile all

Recover database

}

6. Manually cut 3-4 archives in the production warehouse (the operation needs to stop the business of the production warehouse)

If the database is large, back up the archive log

Backup format'/ u03 tag BK full_arc' archivelog all delete all input% Turing% dashing% slots% p.arc' Universe 'BKG;--

And apply the log.

7. Catalog backuppiece'/ u03Universe bkamp 20170712 ERPDBs 17 registered 1.arcades;-- register the backup set

Restore archivelog from sequence 48 until sequence 52; restore

Recover database

8. Temporary tablespace

Select 'alter database rename file''| | NAME | |''to''' | |' / directory / tempfile/' | |''; 'from v$tempfile

Alter database rename file'/ u03 to'/u01/app/oracle/oradata/erpdb/temp01.dbf'

9.redo log

Select 'alter database rename file''| | MEMBER | |''to''' | |' directory / redofile/' | |''; 'from v$logfile

Alter database rename file'/ u03 to'/u01/app/oracle/oradata/erpdb/redo03.log'

Alter database rename file'/ u03 to'/u01/app/oracle/oradata/erpdb/redo02.log'

Alter database rename file'/ u03 to'/u01/app/oracle/oradata/erpdb/redo01.log'

10. Alter database open resetlogs upgrade

Spool upgrade.log

@? / rdbms/admin/catupgrd.sql-this step takes a long time (95 minutes for this test)

Spool off

Create a spfile file

Create spfile from pfile

Startup

ALTER SYSTEM SET compatible='11.2.0.0.0' SCOPE=SPFILE

Exec dbms_stats.gather_dictionary_stats

Compile object

@? / rdbms/admin/utlrp.sql

Application of PSU at database level

SQL > @? / rdbms/admin/catbundle.sql psu apply

Description:

At this point, 10gR2 upgrade to 11.2.0.4 is complete. In fact, when upgrading the production database, the upgrade performance test is more important and will cooperate with the business, and many issues need to be considered, such as:

1. Application middleware jdk

two。 The use of features applied to 10g-11R2.

3. Pressure testing

Performance of 4.sql statements (SPA test)

Reference documentation

Complete checklist for manual upgrade to Oracle Database 11gR2 (ID 1674333.1)

Database upgrade / downgrade compatibility Matrix (document ID 1577660.1)

Script to collect database upgrade / migration diagnostics (dbupgdiag.sql) (document ID 1577288.1)

How to download and run the Oracle Database pre-upgrade Utility (document ID 1577379.1)

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

During A Manual Database Upgrade To 11.2, Gathering DictionaryStatistics Takes Too Long (catupgrd.sql, cmpupend.sql) [ID 1425763.1

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.