Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

An example of Oracle 10.2.0.5 RMAN Migration and upgrade 11.2.0.4

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. Environmental introduction 1. Source database environment

Operating system version: OEL 5.4x64

Database version: 10.2.0.5 x64

Database sid name: orcl

The installation directory for Oracle 10g 10.2.0.5 (64bit) is as follows:

Database software: / u01/app/oracle/product/10.2.0/db_1

Database file: / u01/app/oracle/oradata/orcl

Archive directory: / u01/archivelog

RMAN directory: / backup/dbbak/orabak

Background: an old Oracle 10g database, RMAN 50G, not big, due to a bad server disk, overinsured, plan to migrate the database and upgrade version.

Description: the source database needs to do some basic environment checks, tablespaces, user names, data files and whether there are any files that need to be recovered by the media.

Sqlplus / as sysdba > dbstatus.log

DBID OPEN_MODE

--

1226188361 READ WRITE

SQL >

FILE_NAME

/ u01/app/oracle/oradata/orcl/users01.dbf

/ u01/app/oracle/oradata/orcl/sysaux01.dbf

/ u01/app/oracle/oradata/orcl/undotbs01.dbf

/ u01/app/oracle/oradata/orcl/system01.dbf

/ u01/app/oracle/oradata/orcl/tjoa

/ u01/app/oracle/oradata/orcl/trswcm_data01.dbf

/ u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf

/ u01/app/oracle/oradata/orcl/wtt.dbf

/ u01/app/oracle/oradata/orcl/user02.dbf

/ u01/app/oracle/oradata/orcl/jtbzoa

/ u01/app/oracle/oradata/orcl/ELINK.dbf

/ u01/app/oracle/oradata/orcl/USER03.dbf

/ u01/app/oracle/oradata/orcl/user04.dbf

/ u01/app/oracle/oradata/orcl/users05.dbf

14 rows selected.

SQL >

FILE_NAME

/ u01/app/oracle/oradata/orcl/temp01.dbf

/ u01/app/oracle/oradata/orcl/trswcm_temp01.dbf

/ u01/app/oracle/oradata/orcl/trswcmvideo_temp01.dbf

SQL >

MEMBER

/ u01/app/oracle/oradata/orcl/redo03.log

/ u01/app/oracle/oradata/orcl/redo02.log

/ u01/app/oracle/oradata/orcl/redo01.log

SQL >

NAME TYPE VALUE

-

Control_file_record_keep_time integer 7

Control_files string / u01/app/oracle/oradata/orcl/c

Ontrol01.ctl, / u01/app/oracle/

Oradata/orcl/control02.ctl, / u

01/app/oracle/oradata/orcl/con

Trol03.ctl

SQL >

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/product/10.2.0

/ db_1/dbs/spfileorcl.ora

SQL > Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

two。 Target database environment

Operating system version: RHEL 6.5x64

Database version: 11.2.0.4 x64

Database sid name: orcl

Originally has the database, uses the DBCA to delete the database.

Oracle 11g 11.2.0.4 (64bit) installation directory is as follows (no database created)

Database software: / u01/app/oracle/product/11.2.0/db_1

Database file: / u01/app/oracle/oradata/orcl

Archive directory: / u01/archivelog

RMAN directory: / backup/dbbak/orabak

3. Upgrade idea

1) only after Oracle 10.2.0.2 can you upgrade to 11g 11.2.0.4.

2) create a directory related to Oracle 11.2.0.4 database.

3) execute the Oracle 11.2.0.4 x64 database utlu112i.sql script on the Oracle 10.2.0.5x64 database.

4) backup the Oracle 10.2.0.5x64 database and transfer it to the 11g database server.

5) restore 10g database to 11g database and upgrade.

Second, RMAN backup source 10g database 1. Execute the utlu112i.sql script on the Oracle 10g library

First, you need to transfer the utlu112i.sql script under 11g $ORACLE_HOME/rdbms/admin to the 10g $ORACLE_HOME/rdbms/admin directory and execute it.

And executed on 10g, this script can check some information before the upgrade, which must be executed, otherwise an error will occur during recovery.

Scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.0.96:/home/oracle/

SQL > spool upgrade.info

SQL > @ / home/oracle/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-18-2017 14:26:54

Script Version: 11.2.0.4.0 Build: 007

.

*

Database:

*

-- > name: ORCL

-> version: 10.2.0.5.0

-> compatible: 10.2.0.3.0

-> blocksize: 8192

-- > platform: Linux x86 64-bit

-- > timezone file: V4

.

*

Tablespaces: [make adjustments in the current environment]

*

-> SYSTEM tablespace is adequate for the upgrade.

.... Minimum required size: 1480 MB

-> UNDOTBS1 tablespace is adequate for the upgrade.

.... Minimum required size: 400 MB

-> SYSAUX tablespace is adequate for the upgrade.

.... Minimum required size: 837 MB

-> TEMP tablespace is adequate for the upgrade.

.... Minimum required size: 60 MB

.

*

Flashback: OFF

*

*

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

*

-- > If Target Oracle is 32-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

.

-- > If Target Oracle is 64-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

.

*

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]

*

-> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

-> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

.

*

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

-> Oracle Text [upgrade] VALID

-> Oracle XML Database [upgrade] VALID

-> Oracle Java Packages [upgrade] VALID

-> Oracle interMedia [upgrade] VALID

-> Spatial [upgrade] VALID

-> Data Mining [upgrade] VALID

-> Expression Filter [upgrade] VALID

-> Rule Manager [upgrade] VALID

-> Oracle OLAP API [upgrade] VALID

.

*

Miscellaneous Warnings

*

WARNING:-> Database is using a timezone file older than version 14.

.... After the release migration, it is recommended that DBMS_DST package

.... Be used to upgrade the 10.2.0.5.0 database timezone version

.... To the latest version which comes with the new release.

WARNING:-> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... Registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... Registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... Objects due to the upgrade.

.... USER FKSOA has 3 INVALID objects.

.... USER ZWPORTAL has 1 INVALID objects.

.... USER XCJ has 1 INVALID objects.

.... USER XCJOANEW has 1 INVALID objects.

.... USER EDTEST has 1 INVALID objects.

.... USER NYTOA has 1 INVALID objects.

WARNING:-> Your recycle bin is turned on and currently contains no objects.

.... Because it is REQUIRED that the recycle bin be empty prior to upgrading

.... And your recycle bin is turned on, you may need to execute the command:

PURGE DBA_RECYCLEBIN

.... Prior to executing your upgrade to confirm the recycle bin is empty.

WARNING:-- > JOB_QUEUE_PROCESS value must be updated

.... Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2, setting

.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and

.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES

.... To 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were

.... Unaffected and would still run. This parameter must be updated to

.... A value greater than 16 (default value is 1000) prior to upgrade.

.... Not doing so will affect the running of utlrp.sql after the upgrade

.

*

Recommendations

*

Oracle recommends gathering dictionary statistics prior to

Upgrading the database.

To gather dictionary statistics execute the following command

While connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats

*

SQL >

SQL > spool off

# No errors during execution

# Note: the application needs to be processed as follows according to the execution.

(1) invalid objects exist in the original library. You need to run utlrp.sql script to recompile invalid objects.

SQL > @? / rdbms/admin/utlrp.sql

Re-execute @ / home/oracle/utlu112i.sql to see if it is recompiled

(2) execute PURGE DBA_RECYCLEBIN

SQL > PURGE DBA_RECYCLEBIN

DBA Recyclebin purged.

(3) collect system information EXECUTE dbms_stats.gather_dictionary_stats

Collect dictionary statistics before upgrading, otherwise the pre-upgrade tool (utlu102i.sql) will take longer

SQL > EXECUTE dbms_stats.gather_dictionary_stats

PL/SQL procedure successfully completed.

two。 Back up the source database

Backup script content

#! / bin/bash

# ScriptName:rmanbakup.sh

# Usage: backup all files in oracle user environment.

# Author: koumm

# Creation: 2017-07-31

# Version: 1.0.0

# Define variable

Basedir=/u01/orabak

Date= `date +% Y% m% d`

# Create pfile

Sqlplus / as sysdba shutdown immediate

Oracle instance shut down

There are DBID records in the original backup log backup _ all_20170801.log.

RMAN > set DBID=1226188361

Executing command: SET DBID

RMAN > startup mount

Connected to target database (not started)

Oracle instance started

Database mounted

Total System Global Area 1219260416 bytes

Fixed Size 2252744 bytes

Variable Size 704643128 bytes

Database Buffers 503316480 bytes

Redo Buffers 9048064 bytes

RMAN >

RMAN > delete noprompt expired backupset

RMAN > crosscheck backupset

Using channel ORA_DISK_1

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1 RECID=11 STAMP=955031770

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1 RECID=12 STAMP=955031784

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1 RECID=13 STAMP=955032406

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_15_1 RECID=14 STAMP=955035803

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_16_1 RECID=15 STAMP=955035849

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1 RECID=16 STAMP=955035323

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1 RECID=17 STAMP=955036896

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_17_1 RECID=18 STAMP=955036896

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1 RECID=19 STAMP=955036900

Crosschecked 9 objects

RMAN > restore database

Starting restore at 18-SEP-17

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00005 to / u01/app/oracle/oradata/orcl/tjoa

Channel ORA_DISK_1: restoring datafile 00012 to / u01/app/oracle/oradata/orcl/USER03.dbf

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/full_ORCL_20170918_12_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1 tag=TAG20170918T143606

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:35

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/orcl/sysaux01.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/orcl/users01.dbf

Channel ORA_DISK_1: restoring datafile 00010 to / u01/app/oracle/oradata/orcl/jtbzoa

Channel ORA_DISK_1: restoring datafile 00014 to / u01/app/oracle/oradata/orcl/users05.dbf

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/full_ORCL_20170918_11_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1 tag=TAG20170918T143606

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:02:16

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/orcl/system01.dbf

Channel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/orcl/undotbs01.dbf

Channel ORA_DISK_1: restoring datafile 00008 to / u01/app/oracle/oradata/orcl/wtt.dbf

Channel ORA_DISK_1: restoring datafile 00013 to / u01/app/oracle/oradata/orcl/user04.dbf

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/full_ORCL_20170918_13_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1 tag=TAG20170918T143606

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:45

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00006 to / u01/app/oracle/oradata/orcl/trswcm_data01.dbf

Channel ORA_DISK_1: restoring datafile 00007 to / u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf

Channel ORA_DISK_1: restoring datafile 00009 to / u01/app/oracle/oradata/orcl/user02.dbf

Channel ORA_DISK_1: restoring datafile 00011 to / u01/app/oracle/oradata/orcl/ELINK.dbf

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/full_ORCL_20170918_14_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1 tag=TAG20170918T143606

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:56

Finished restore at 18-SEP-17

RMAN >

RMAN > list backup of archivelog all

List of Backup Sets

=

BS Key Size Device Type Elapsed Time Completion Time

--

17 12.70M DISK 00:00:02 18-SEP-17

BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20170918T160134

Piece Name: / backup/dbbak/orabak/arch_ORCL_20170918_18_1

List of Archived Logs in backup set 17

Thrd Seq Low SCN Low Time Next SCN Next Time

-

1 30547 1904894964 18-SEP-17 1904899341 18-SEP-17

1 30548 1904899341 18-SEP-17 1904899351 18-SEP-17

1 30549 1904899351 18-SEP-17 1904899359 18-SEP-17

BS Key Size Device Type Elapsed Time Completion Time

--

18 41.45M DISK 00:00:06 18-SEP-17

BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20170918T160134

Piece Name: / backup/dbbak/orabak/arch_ORCL_20170918_17_1

List of Archived Logs in backup set 18

Thrd Seq Low SCN Low Time Next SCN Next Time

-

1 30546 1904882200 18-SEP-17 1904894964 18-SEP-17

BS Key Size Device Type Elapsed Time Completion Time

--

19 2.50K DISK 00:00:01 18-SEP-17

BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20170918T160134

Piece Name: / backup/dbbak/orabak/arch_ORCL_20170918_19_1

List of Archived Logs in backup set 19

Thrd Seq Low SCN Low Time Next SCN Next Time

-

1 30550 1904899359 18-SEP-17 1904899365 18-SEP-17

1 30551 1904899365 18-SEP-17 1904899370 18-SEP-17

Check the SCN of the source database after the backup.

RMAN > recover database until scn 1904899370

Starting recover at 18-SEP-17

Using channel ORA_DISK_1

Starting media recovery

Channel ORA_DISK_1: starting archived log restore to default destination

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=30547

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=30548

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=30549

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/arch_ORCL_20170918_18_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1 tag=TAG20170918T160134

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Archived log file name=/u01/archivelog1_30547_698407628.dbf thread=1 sequence=30547

Archived log file name=/u01/archivelog1_30548_698407628.dbf thread=1 sequence=30548

Archived log file name=/u01/archivelog1_30549_698407628.dbf thread=1 sequence=30549

Channel ORA_DISK_1: starting archived log restore to default destination

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=30550

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=30551

Channel ORA_DISK_1: reading from backup piece / backup/dbbak/orabak/arch_ORCL_20170918_19_1

Channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1 tag=TAG20170918T160134

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Archived log file name=/u01/archivelog1_30550_698407628.dbf thread=1 sequence=30550

Archived log file name=/u01/archivelog1_30551_698407628.dbf thread=1 sequence=30551

Media recovery complete, elapsed time: 00:00:01

Finished recover at 18-SEP-17

RMAN >

5. Upgrade mode upgrade database 1) upgrade mode resetlogs startup database

[oracle@mholddb ~] $sqlplus / nolog

SQL > conn / as sysdba

SQL > alter database open resetlogs upgrade

Database altered.

SQL >

2) rebuild the temp temporary tablespace data file

SQL > alter tablespace temp add tempfile'/ u01According to an maxsize maxsize 100m reuse autoextend on next 10m maxsize 1000m

Tablespace altered.

6. Run the script in upgrade mode: catupgrd.sql

SQL > @? / rdbms/admin/catupgrd.sql

... It took about 25 minutes to execute.

SQL > Rem Set errorlogging off

SQL > SET ERRORLOGGING OFF

SQL >

SQL > REM END OF CATUPGRD.SQL

SQL >

SQL > REM bug 12337546-Exit current sqlplus session at end of catupgrd.sql.

SQL > REM This forces user to start a new sqlplus session in order

SQL > REM to connect to the upgraded db.

SQL > exit

From Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options disconnect

7. Execute utlu112s.sql script

This script displays a summary of the upgrade process. You don't need to be in upgrade mode.

[oracle@mholddb ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 21:03:52 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 570428576 bytes

Database Buffers 1023410176 bytes

Redo Buffers 7319552 bytes

Database mounted.

Database opened.

SQL > @? / rdbms/admin/utlu112s.sql

.

Oracle Database 11.2 Post-Upgrade Status Tool 09-18-2017 21:04:53

.

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

.

Oracle Server

. VALID 11.2.0.4.0 00:06:07

JServer JAVA Virtual Machine

. VALID 11.2.0.4.0 00:03:40

Oracle Workspace Manager

. VALID 11.2.0.4.0 00:00:19

OLAP Analytic Workspace

. VALID 11.2.0.4.0 00:01:07

OLAP Catalog

. VALID 11.2.0.4.0 00:00:22

Oracle OLAP API

. VALID 11.2.0.4.0 00:00:12

Oracle XDK

. VALID 11.2.0.4.0 00:01:11

Oracle Text

. VALID 11.2.0.4.0 00:00:23

Oracle XML Database

. VALID 11.2.0.4.0 00:02:06

Oracle Database Java Packages

. VALID 11.2.0.4.0 00:00:06

Oracle Multimedia

. VALID 11.2.0.4.0 00:01:50

Spatial

. VALID 11.2.0.4.0 00:03:08

Oracle Expression Filter

. VALID 11.2.0.4.0 00:00:05

Oracle Rule Manager

. VALID 11.2.0.4.0 00:00:04

Final Actions

. 00:00:14

Total Upgrade Time: 00:21:01

PL/SQL procedure successfully completed.

SQL >

The PL/SQL process completed successfully.

SQL >

8. Compile invalid object

# View the number of invalid objects

SQL > select count (*) from dba_invalid_objects

COUNT (*)

-

1275

# compile invalid objects, and you don't have to worry about them if they are the same as the previous ones.

SQL > @? / rdbms/admin/utlrp.sql

Execution complete.

# View invalid objects

SQL > select count (*) from dba_invalid_objects

No rows selected

SQL > SELECT COUNT (*) FROM obj$ WHERE status IN (4, 5, 6)

COUNT (*)

-

0

9. Check database status (1) check component status

SQL > select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS

-

Oracle Enterprise Manager 11.2.0.4.0 VALID

OLAP Catalog 11.2.0.4.0 VALID

Spatial 11.2.0.4.0 VALID

Oracle Multimedia 11.2.0.4.0 VALID

Oracle XML Database 11.2.0.4.0 VALID

Oracle Text 11.2.0.4.0 VALID

Oracle Data Mining 11.2.0.4.0 VALID

Oracle Expression Filter 11.2.0.4.0 VALID

Oracle Rules Manager 11.2.0.4.0 VALID

Oracle Workspace Manager 11.2.0.4.0 VALID

Oracle Database Catalog Views 11.2.0.4.0 VALID

Oracle Database Packages and Types 11.2.0.4.0 VALID

JServer JAVA Virtual Machine 11.2.0.4.0 VALID

Oracle XDK 11.2.0.4.0 VALID

Oracle Database Java Packages 11.2.0.4.0 VALID

OLAP Analytic Workspace 11.2.0.4.0 VALID

Oracle OLAP API 11.2.0.4.0 VALID

(2) check the patch status

Set line 150

Col ACTION_TIME for a30

Col ACTION for a15

Col NAMESPACE for a8

Col VERSION for a10

Col BUNDLE_SERIES for a5

Col COMMENTS for a30

Select * from dba_registry_history

ACTION_TIME ACTION NAMESPAC VERSION ID COMMENTS

25-APR-12 09.56.48.615853 AM VIEW RECOMPILE 8289601 view recompilation

25-APR-12 09.56.48.651566 AM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.4.0

18-SEP-17 09.00.00.659377 PM VIEW INVALIDATE 8289601 view invalidation

18-SEP-17 09.00.14.866796 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.5.0

SQL >

10, other generated parameter files

Create a spfile file through pfile.

SQL > create spfile from pfile

File created.

SQL >

11, generate password file

$orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' password=oracle entries=10 force=y

12. Modify compatible parameters:

SQL > ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE=SPFILE

13, Timezone database level upgrade, the following is the manual implementation, as well as the script upgrade method, briefly.

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]

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.

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

10.2.0.4, 5 timezone is 4. 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

Execute the script:

-- 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')

Execute the script:

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

No rows selected

Restart the database:

Shutdown immediate

Startup

Upgrade related table: execute the script:

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

/

Failures:0

PL/SQL procedure successfully completed.

If there are no errors, end the upgrade:

VAR fail number

BEGIN

DBMS_DST.END_UPGRADE (: fail)

DBMS_OUTPUT.PUT_LINE ('Failures:' | |: fail)

END

/

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

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

14. If you encounter problems during the upgrade process, you can re-execute the upgrade script (parameters)

1) Shut down the database as follows:

SQL > SHUTDOWN IMMEDIATE

2) Restart thedatabasein UPGRADE mode:

SQL > STARTUPUPGRADE

3) Rerun catupgrd.sql:-- upgrade DB

SQL > @ catupgrd.sql

4) Rerun utlu112s.sql:-- report a summary of the upgrade process

SQL > @ utlu112s.sql

5) migrate 10g Baseline to 11g

SQL > @? / rdbms/admin/catuppst.sql

6) compile invalid objects:

SQL > @? / rdbms/admin/utlrp.sql

Note: after the upgrade, utluiobj.sql is performed to list the comparative information of the failed objects.

SQL > @? / rdbms/admin/utluiobj.sql

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report