Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the off-machine upgrade of Oracle database. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Architecture introduction:

Source library: single instance ip:192.168.56.11 ORCLE_SID:orcl db_nme:orcl version: 10.2.0.5.0

Os:Red Hat Enterprise Linux Server release 4 (Tikanga) hostname:rhel

Target library: single instance ip:192.168.56.28 ORACLE_SID:kill version: 11.2.0.4 (only or source library cle software is installed)

Os:Red Hat Enterprise Linux Server release 6.7 (Santiago) hostname:testd Target Library

Steps for upgrading different machines:

First, confirm whether it can be upgraded directly

Second, the target library machine installs 11g software and makes PSU patches.

Third, check the environment variables of the target database.

Fourth, the target database machine to create the required directory

5. Check the original database before upgrading.

Upload utlu112i.sql scripts from the target database machine to the source database machine

5.2 the source server executes utlu112i.sql for pre-upgrade check

5.3 adjust the source database according to the inspection results

6. The source database machine backs up the original database

7. The target database machine recovers the database

7.1 create a password file

7.2 start the instance to nomount status (specify pfile file)

7.3 RMAN restore control files to determine the validity of the backup set

7.4 RMAN recovery database

7.5 Open the database (resetlogs)

8. Target database machine upgrade database

8.1 to prevent insufficient default archive space during upgrade testing, the upgrade first turns on the library to non-archive mode.

8.2 upgrade open database

8.3 execute upgrade script

IX. Operation after upgrade of the target database machine

9.1 create a spfile file

9.2 execute EXECUTE dbms_stats.gather_dictionary_stats to collect data dictionary statistics

9.3 recompile @? / rdbms/admin/utlrp.sql

9.4 View failure objects

9.5 check script @? / rdbms/admin/utlu112s.sql after running the upgrade

9.6 Application of PSU at database level

First, confirm whether it can be upgraded directly

Reference: http://blog.itpub.net/31397003/viewspace-2146129/

Second, the target library machine installs 11g software and makes PSU patches.

The steps of upgrading and installing to oracle software are omitted.

Patch PSU as follows:

[oracle@testdb OPatch] $pwd

/ u01/app/oracle/product/11.2.0/dbhome_1/OPatch

[oracle@testdb OPatch] $

[oracle@testdb OPatch] $. / opatch version

OPatch Version: 11.2.0.3.4

OPatch succeeded.

[oracle@testdb OPatch] $

Upload opatch package and psu package as follows:

[oracle@testdb ~] $ls

Database db_install_oui.rsp p24732075_112040_Linux-x86-64.zip p6880880_112000_Linux-x86-64.zip

[oracle@testdb ~] $pwd

/ home/oracle

Update the opatch tool:

[oracle@testdb dbhome_1] $mv OPatch OPatch.bak

[oracle@testdb dbhome_1] $

[oracle@testdb] $unzip p6880880_112000_Linux-x86-64.zip-d / u01/app/oracle/product/11.2.0/dbhome_1

Omit.

[oracle@testdb ~] $cd-

/ u01/app/oracle/product/11.2.0/dbhome_1

[oracle@testdb dbhome_1] $cd OPatch

[oracle@testdb OPatch] $. / opatch version

OPatch Version: 11.2.0.3.12

OPatch succeeded.

[oracle@testdb OPatch] $

Apply psu:

[oracle@testdb ~] $ls

Database db_install_oui.rsp p24732075_112040_Linux-x86-64.zip p6880880_112000_Linux-x86-64.zip

[oracle@testdb ~] $

[oracle@testdb ~] $mkdir soft

[oracle@testdb ~] $mv p24732075_112040_Linux-x86-64.zip soft/

[oracle@testdb ~] $

[oracle@testdb ~] $cd soft/

[oracle@testdb soft] $ls

P24732075_112040_Linux-x86-64.zip

[oracle@testdb soft] $

[oracle@testdb soft] $unzip p24732075_112040_Linux-x86-64.zip

Omit; omit.

[oracle@testdb soft] $ls

24732075 p24732075_112040_Linux-x86-64.zip PatchSearch.xml

[oracle@testdb soft] $

[oracle@testdb 24732075] $ls

17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075 patchmd.xml README.html README.txt

[oracle@testdb 24732075] $

Patch according to README.html:

Perform collision detection:

[oracle@testdb 24732075] $pwd

/ home/oracle/soft/24732075

[oracle@testdb 24732075] $

[oracle@testdb 24732075] $/ u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail-ph. /

Oracle Interim Patch Installer version 11.2.0.3.12

Copyright (c) 2017, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home: / u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory: / u01/app/oraInventory

From: / u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version: 11.2.0.3.12

OUI version: 11.2.0.4.0

Log file location: / u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27021-51-47PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@testdb 24732075] $

Start applying patches:

[oracle@testdb 24732075] $/ u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply

Oracle Interim Patch Installer version 11.2.0.3.12

Copyright (c) 2017, Oracle Corporation. All rights reserved.

Oracle Home: / u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory: / u01/app/oraInventory

From: / u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version: 11.2.0.3.12

OUI version: 11.2.0.4.0

Log file location: / u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27021-54-33PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075

Do you want to proceed? [y | n]

Y

User Responded with: Y

All checks passed.

Provide your email address to be informed of security issues, install and

Initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y] es, [N] o) [N]: y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home ='/ u01amp appActionoracleUniverse produce 11.2.0Uniplex dbhomeowners 1')

Is the local system ready for patching? [y | n]

Y

User Responded with: Y

Backing up files...

Applying sub-patch '17478514' to OH'/ u01 debase append oracleUniplex productUniverse 11.2.0 Universe dbhomeowners 1'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.sdo, 11.2.0.4.0...

Patching component oracle.sysman.agent, 10.2.0.4.5...

Omit.

Omit.

Patching component oracle.ordim.server, 11.2.0.4.0...

Composite patch 24732075 successfully applied.

Log file location: / u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27021-54-33PM_1.log

OPatch succeeded.

[oracle@testdb 24732075] $

[oracle@testdb OPatch] $. / opatch lspatches

Database Patch Set Update: 11.2.0.4.170418 (24732075)

OPatch succeeded.

[oracle@testdb OPatch] $

Third, check the environment variables of the target database.

[oracle@testdb ~] $cat .bash _ profile

# .bash _ profile

Export ORACLE_SID=kill

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Export NLS_LANG= "american_america.ZHS16GBK"

Export NLS_DATE_FORMAT= "YYYY-MM-DD HH24:Mi:SS"

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Export PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

[oracle@testdb ~] $

IV. Create the required directories for the target library

The target library recommends that you create the following directory first, which is no longer needed for bdump,udump at 11g, as can be seen from the output of the pre-upgrade check script later.

Mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/adump

Mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/dpdump

Mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/cdump

Mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/pfile

[oracle@testdb] $mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/adump

[oracle@testdb] $mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/dpdump

[oracle@testdb] $mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/cdump

[oracle@testdb] $mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/pfile

5. Check the original database before upgrading.

Upload utlu112i.sql scripts from the target database machine to the source database machine

[oracle@testdb admin] $cd $ORACLE_HOME/rdbms/admin/

[oracle@testdb admin] $

[oracle@testdb admin] $ls-lrt utlu112i*

-rw-rw-rw-. 1 oracle oinstall 225754 Feb 23 2017 utlu112i.sql

[oracle@testdb admin] $

[oracle@testdb admin] $scp utlu112i.sql oracle@192.168.56.11:/home/oracle

The authenticity of host '192.168.56.11 (192.168.56.11)' can't be established.

RSA key fingerprint is ed:38:fa:9f:2a:49:b6:c6:22:7a:05:78:3e:ea:c4:28.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.56.11' (RSA) to the list of known hosts.

Oracle@192.168.56.11's password:

Utlu112i.sql 100% 220KB 220.5KB/s 00:00

[oracle@testdb admin] $

5.2 the source server executes utlu112i.sql for pre-upgrade check

[oracle@rhel ~] $ls

Patch soft utlu112i.sql

[oracle@rhel ~] $

[oracle@rhel ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Thu Nov 23 16:32:27 2017

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

SQL > spool upgrade.info

SQL > @ utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 11-23-2017 16:32:53

Script Version: 11.2.0.4.0 Build: 007

.

*

Database:

*

-- > name: ORCL

-- > version: 10.2.0.1.0

-- > compatible: 10.2.0.1.0

-> blocksize: 8192

-- > platform: Linux 64-bit for AMD

-- > timezone file: V2

.

*

Tablespaces: [make adjustments in the current environment]

*

-> SYSTEM tablespace is adequate for the upgrade.

.... Minimum required size: 1113 MB

-> UNDOTBS1 tablespace is adequate for the upgrade.

.... Minimum required size: 400 MB

-> SYSAUX tablespace is adequate for the upgrade.

.... Minimum required size: 714 MB

-> TEMP tablespace is adequate for the upgrade.

.... Minimum required size: 60 MB

-> EXAMPLE tablespace is adequate for the upgrade.

.... Minimum required size: 69 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:

WARNING:-- > "sga_target" needs to be increased to at least 596MB

.

*

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

-> EM Repository [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.1.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 SYS has 1 INVALID objects.

WARNING:-> EM Database Control Repository exists in the database.

.... Direct downgrade of EM Database Control is not supported. Refer to the

.... Upgrade Guide for instructions to save the EM data prior to upgrade.

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.

.

*

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 > spool off

SQL > exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

[oracle@rhel ~] $

[oracle@rhel ~] $ls

Patch soft upgrade.info utlu112i.sql

[oracle@rhel ~] $

5.3 adjust the source database according to the inspection results

5.3.1 create a pfile for the source database and transfer it to the target database:

SQL > show parameter spfile

NAME TYPE VALUE

-

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

.1 / db_1/dbs/spfileorcl.ora

SQL >

SQL >

SQL > create pfile from spfile

File created.

SQL >

SQL > exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

[oracle@rhel ~] $

[oracle@rhel ~] $cd $ORACLE_HOME/dbs

[oracle@rhel dbs] $ls inito*

Initorcl.ora

[oracle@rhel dbs] $scp initorcl.ora oracle@192.168.56.28:/home/oracle

The authenticity of host '192.168.56.28 (192.168.56.28)' can't be established.

RSA key fingerprint is 16:8d:5a:fb:f2:58:e1:ee:4c:98:3d:76:ec:48:bb:46.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.56.28' (RSA) to the list of known hosts.

Oracle@192.168.56.28's password:

Initorcl.ora 100% 994 1.0KB/s 00:00

[oracle@rhel dbs] $

5.3.2 adjust the pfile on the target library:

Note whether the setting of sga accords with the configuration of the destination machine.

* .sga_target=624951296 WARNING:-- > "sga_target" needs to be increased to at least 596MB

Delete * .background _ dump_dest and * .user _ dump_dest

Add * .diagnostic_dest='$ORACLE_BASE'

Note: there is no need to say much about the modification of the parameter file, but we need to pay attention to whether the values of other parameters meet the requirements.

[oracle@testdb ~] $cat initorcl.ora

Kill.__db_cache_size=478150656

Kill.__java_pool_size=4194304

Kill.__large_pool_size=4194304

Kill.__shared_pool_size=109051904

Kill.__streams_pool_size=0

* .audit_file_dest='/u01/app/oracle/admin/kill/adump'

# * .background _ dump_dest='/u01/app/oracle/admin/kill/bdump'

* .compatible='10.2.0.1.0'

* .control_files='/u01/app/oracle/oradata/kill/control01.ctl','/u01/app/oracle/oradata/kill/control02.ctl','/u01/app/oracle/oradata/kill/control03.ctl'

* .core_dump_dest='/u01/app/oracle/admin/kill/cdump'

* .db_block_size=8192

* .db_domain=''

* .db_file_multiblock_read_count=16

* .db_name='orcl'

* .diagnostic_dest='$ORACLE_BASE'

* .db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

* .db_recovery_file_dest_size=2147483648

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'

* .job_queue_processes=10

* .open_cursors=300

* .pga_aggregate_target=199229440

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .sga_target=624951296

* .undo_management='AUTO'

* .undo_tablespace='UNDOTBS1'

# * .user _ dump_dest='/u01/app/oracle/admin/orcl/udump'

[oracle@testdb ~] $

Create the corresponding directory on the target library according to the parameter file

[oracle@testdb] $mkdir-p / u01/app/oracle/oradata/kill/

[oracle@testdb cdump] $mkdir-p / u01/app/oracle/flash_recovery_area

Copy a copy to $ORACLE_HOME/dbs/init$ORACLE_SID.ora after modification

[oracle@testdb ~] $cp initorcl.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora

[oracle@testdb ~] $cd $ORACLE_HOME/dbs/

[oracle@testdb dbs] $ls

Initkill.ora init.ora

[oracle@testdb dbs] $

5.3.3 Source database executes PURGE DBA_RECYCLEBIN

SQL > PURGE DBA_RECYCLEBIN

SQL >

5.3.4 Source Library collects 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.

5.3.5 the source library runs the utlrp.sql script to recompile invalid objects

Select * from registry$nonsys_inv_objs; / / non-sys/system failure object

Failure object of select * from registry$sys_inv_objs; / / sys/system

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

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

SQL > select * from registry$sys_inv_objs

OWNER OBJECT_NAME OBJECT_TYP

SYS CALLING PROCEDURE

SQL > drop procedure calling

SQL >

Note: perform utluiobj.sql after upgrade

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

VI. Source database backup

Create a backup script:

Note: the script will archive the backup. You need to open the archive.

[oracle@rhel ~] $mkdir bak

[oracle@rhel ~] $vi backup_all.sh

#! / bin/bash

# Define variable

Basedir=/home/oracle/bak

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

# Create pfile

Sqlplus / as sysdba startup nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2255792 bytes

Variable Size 230687824 bytes

Database Buffers 385875968 bytes

Redo Buffers 3330048 bytes

SQL >

7.3 RMAN restore control files to determine the validity of the backup set

7.3.1 RMAN restores the control file, starts the database to the mount status, and determines the validity of the backup set

[oracle@testdb 11gbak] $rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Thu Dec 28 02:03:42 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORCL (not mounted)

RMAN > restore controlfile from'/ home/oracle/bak/ctl_ORCL_20171019_8_1'

Starting restore at 2017-12-31 13:46:21

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Channel ORA_DISK_1: restoring control file

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

Output file name=/u01/app/oracle/oradata/kill/control01.ctl

Output file name=/u01/app/oracle/oradata/kill/control02.ctl

Output file name=/u01/app/oracle/oradata/kill/control03.ctl

Finished restore at 2017-12-31 13:46:23

RMAN > alter database mount

RMAN > crosscheck backupset

RMAN > crosscheck backupset

Starting implicit crosscheck backup at 2017-12-31 13:46:59

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Crosschecked 7 objects

Finished implicit crosscheck backup at 2017-12-31 13:46:59

Starting implicit crosscheck copy at 2017-12-31 13:46:59

Using channel ORA_DISK_1

Finished implicit crosscheck copy at 2017-12-31 13:47:00

Searching for all files in the recovery area

Cataloging files...

No files cataloged

Using channel ORA_DISK_1

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 RECID=1 STAMP=957803210

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/full_ORCL_20171019_3_1 RECID=2 STAMP=957803260

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/full_ORCL_20171019_4_1 RECID=3 STAMP=957803264

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 RECID=4 STAMP=957803210

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 RECID=5 STAMP=957803311

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 RECID=6 STAMP=957803311

Crosschecked backup piece: found to be 'AVAILABLE'

Backup piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 RECID=7 STAMP=957803314

Crosschecked 7 objects

RMAN >

Note that if the backup set expires, register first and then delete, as follows: example

Generally speaking, the backup directories of the two hosts are different, and the backup set path of the control file records cannot find the corresponding backup set. The status is' EXPIRED', you should delete these expired backup sets at this time, catalog the new backup set, and confirm the validity of the backup set again.

Rman target / > catalog.log set newname for datafile 3 to'/ u01According to an oracle, an oradata, an oradata, kills, an

> set newname for datafile 4 to'/ u01ActionPlacement oradataUsers01.dbf'

> set newname for datafile 5 to'/ u01 apprentices.oracle.oradataUniple

> restore database

> switch datafile all

Release channel C1

Release channel c2

>}

> EOF

RMAN >

RMAN > [oracle@testdb 11gbak] $

[oracle@testdb 11gbak] $

[oracle@testdb 11gbak] $tail-200f restore.log

Recovery Manager: Release 11.2.0.4.0-Production on Thu Dec 28 02:34:24 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORCL (DBID=1485502468, not open)

RMAN >

Using target database control file instead of recovery catalog

Allocated channel: c1

Channel c1: SID=21 device type=DISK

Allocated channel: c2

Channel c2: SID=24 device type=DISK

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Starting restore at 2017-12-31 13:52:59

Channel c1: starting datafile backup set restore

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

Channel c1: restoring datafile 00001 to / u01/app/oracle/oradata/kill/system01.dbf

Channel c1: restoring datafile 00005 to / u01/app/oracle/oradata/kill/example01.dbf

Channel c1: reading from backup piece / home/oracle/bak/full_ORCL_20171019_2_1

Channel c2: starting datafile backup set restore

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

Channel c2: restoring datafile 00002 to / u01/app/oracle/oradata/kill/undotbs01.dbf

Channel c2: restoring datafile 00003 to / u01/app/oracle/oradata/kill/sysaux01.dbf

Channel c2: restoring datafile 00004 to / u01/app/oracle/oradata/kill/users01.dbf

Channel c2: reading from backup piece / home/oracle/bak/full_ORCL_20171019_1_1

Channel c1: piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 tag=TAG20171019T162649

Channel c1: restored backup piece 1

Channel c1: restore complete, elapsed time: 00:00:55

Channel c2: piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 tag=TAG20171019T162649

Channel c2: restored backup piece 1

Channel c2: restore complete, elapsed time: 00:00:56

Finished restore at 2017-12-31 13:53:56

Datafile 1 switched to datafile copy

Input datafile copy RECID=7 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/system01.dbf

Datafile 2 switched to datafile copy

Input datafile copy RECID=8 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/undotbs01.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=9 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/sysaux01.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=10 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/users01.dbf

Datafile 5 switched to datafile copy

Input datafile copy RECID=11 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/example01.dbf

Released channel: c1

Released channel: c2

RMAN >

Recovery Manager complete.

[oracle@testdb bak] $

7.4.2 recover data File

7.4.2.1 use the recover database command to recover data

RMAN > recover database

RMAN > recover database

Starting recover at 2017-12-31 13:55:19

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=38

Channel ORA_DISK_1: reading from backup piece / home/oracle/bak/arch_ORCL_20171019_5_1

Channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 tag=TAG20171019T162831

Channel ORA_DISK_1: restored backup piece 1

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

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc thread=1 sequence=38

Channel default: deleting archived log (s)

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc RECID=6 STAMP=964187722

Channel ORA_DISK_1: starting archived log restore to default destination

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=39

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=40

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=41

Channel ORA_DISK_1: reading from backup piece / home/oracle/bak/arch_ORCL_20171019_6_1

Channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 tag=TAG20171019T162831

Channel ORA_DISK_1: restored backup piece 1

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

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc thread=1 sequence=39

Channel default: deleting archived log (s)

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc RECID=8 STAMP=964187725

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc thread=1 sequence=40

Channel default: deleting archived log (s)

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc RECID=7 STAMP=964187725

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc thread=1 sequence=41

Channel default: deleting archived log (s)

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc RECID=9 STAMP=964187725

Channel ORA_DISK_1: starting archived log restore to default destination

Channel ORA_DISK_1: restoring archived log

Archived log thread=1 sequence=42

Channel ORA_DISK_1: reading from backup piece / home/oracle/bak/arch_ORCL_20171019_7_1

Channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 tag=TAG20171019T162831

Channel ORA_DISK_1: restored backup piece 1

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

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc thread=1 sequence=42

Channel default: deleting archived log (s)

Archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc RECID=10 STAMP=964187726

Unable to find archived log

Archived log thread=1 sequence=43

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 12/31/2017 13:55:28

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43 and starting SCN of 1015325

RMAN >

7.4.2.2 recover database until scn xxxxxxx

Note: this scn is obtained from the log information in the previous step.

RMAN > recover database until scn 1015325

Starting recover at 2017-12-31 13:55:53

Using channel ORA_DISK_1

Starting media recovery

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

Finished recover at 2017-12-31 13:55:54

RMAN >

7.4.2.3 modify log file path

Note: you can use UE column editing mode to quickly process new redo file names (that is, redoXXX.log is changed to the corresponding actual value)

New_dest=/u01/app/oracle/oradata/kill

Sqlplus / as sysdba > logfile.log EOF

[oracle@testdb ~] $

[oracle@testdb ~] $cat logfile.log

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 13:57:16 2017

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

Connected to:

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

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

SQL > SQL >

'ALTERDATABASERENAMEFILE''' | | MEMBER | |' 'TO''/U01/APP/ORACLE/ORADATA/KILL/REDOXXX.LOG'';'

-

Alter database rename file'/ u01qqappActionoradat'oradata 'redo03.log'log'log'/ u01 to'/ u01 apprenticeship oradata'oradata 'rekillexcxx.log'

Alter database rename file'/ u01qqappActionoradat'oradata 'redo02.log'log'log'/ u01 to'/ u01 apprenticeship oradata

Alter database rename file'/ u01qqappActionoradat'oradata 'redo01.log' to'/ u01 apprenticeship oradata'oradata'

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

[oracle@testdb ~] $

Formal implementation:

SQL > alter database rename file'/ u01 Grease App alter database rename file'/ u01 Grey App Uniqr oracleGrease oradata 'redo03.log' / u01 ApplicationoracleUniplicationoradata 'redo03.log'

Alter database rename file'/ u01qqappActionoradataPlacement oradata 'redo02.log'log' / u01 to'/ u01 apprenticeship oradata'oradataUniplicateoradata'

Alter database rename file'/ u01qqappActionoradataPlacement oradata 'redo01.log' / u01 to'/ u01 apprenticeship oradata'oradataUniplicationoradata

Database altered.

SQL >

Database altered.

SQL >

Database altered.

SQL > select member from v$logfile

MEMBER

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

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

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

7.4.3 modify temporary file path

Note: also deal with the new temp file name (that is, the tempXXX.dbf is changed to the corresponding actual value).

New_dest=/u01/app/oracle/oradata/kill

Sqlplus / as sysdba > tempfile.log EOF

[oracle@testdb 11gbak] $cat tempfile.log

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:50:11 2017

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

Connected to:

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

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

SQL > SQL >

'ALTERDATABASERENAMEFILE''' | | NAME | |' 'TO''/U01/APP/ORACLE/ORADATA/KILL/TEMPXXX.DBF'';'

-

Alter database rename file'/ u01qqappActionoradataPlacement oradataUniplicationtemp01.dbf' to'/ u01qapplemoradataUniplicationoradata'/ u01qqqxx.dbf'

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

[oracle@testdb 11gbak] $

Execute:

SQL > alter database rename file'/ u01qqappActionorActionoradataUnitionorclActiontemp01.dbf'to'/ u01qapplyoracleUniplicationoradataScale oradataUniplicationtemp01.dbf'

Database altered.

SQL > select name from v$tempfile

NAME

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

SQL >

7.4 Open database (resetlogs)

Note: do not attempt to open the database directly when the recovery is complete. Because we are upgrading! Open upgrade is required (described in the next step).

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 6097

Session ID: 22 Serial number: 63

SQL >

8. Target database machine upgrade database

-- start it to mount with pfile:

SQL > startup mount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2255792 bytes

Variable Size 230687824 bytes

Database Buffers 385875968 bytes

Redo Buffers 3330048 bytes

Database mounted.

SQL >

8.1 in order to prevent insufficient default archive space during upgrade testing, the upgrade first turns on the library to non-archive mode.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

SQL >

SQL > alter database noarchivelog

Database altered.

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Current log sequence 1

SQL >

8.2 upgrade open database

Alter database open resetlogs upgrade; for incomplete recovery

Alter database open upgrade; for full recovery

SQL > alter database open resetlogs upgrade

Alter database open resetlogs upgrade

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL > alter database open upgrade

Database altered.

SQL >

8.3 execute upgrade script

Note that this step may take a long time to upgrade

SQL > spool upgrade.log

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

Record omission.

..

SQL >

SQL > SET SERVEROUTPUT OFF

SQL > SET VERIFY ON

SQL > commit

Commit complete.

SQL >

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL >

SQL >

SQL > DOC

DOC > #

DOC > #

DOC >

DOC > The above sql script is the final step of the upgrade. Please

DOC > review any errors in the spool log file. If there are any errors in

DOC > the spool file, consult the Oracle Database Upgrade Guide for

DOC > troubleshooting recommendations.

DOC >

DOC > Next restart for normal operation, and then run utlrp.sql to

DOC > recompile any invalid application objects.

DOC >

DOC > If the source database had an older time zone version prior to

DOC > upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade

DOC > TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC > with Oracle.

DOC >

DOC > #

DOC > #

DOC > #

SQL >

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

Disconnected 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

[oracle@testdb dbs] $

[oracle@testdb dbs] $

After the execution of the script, the database is automatically closed, and after the above operation, the database can be opened.

IX. Operation after upgrading the target library

Open the library normally:

[oracle@testdb dbs] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:23:03 2017

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

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2255792 bytes

Variable Size 230687824 bytes

Database Buffers 385875968 bytes

Redo Buffers 3330048 bytes

Database mounted.

Database opened.

SQL >

SQL > set lines 200

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string

9.1 create a spfile file

SQL > create spfile from pfile

File created.

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2255792 bytes

Variable Size 230687824 bytes

Database Buffers 385875968 bytes

Redo Buffers 3330048 bytes

Database mounted.

Database opened.

SQL >

SQL > set lines 200

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/product/11.2.0

/ dbhome_1/dbs/spfilekill.ora

SQL >

9.2 execute EXECUTE dbms_stats.gather_dictionary_stats to collect data dictionary statistics

SQL > exec dbms_stats.gather_dictionary_stats

PL/SQL procedure successfully completed.

SQL >

9.3 recompile @? / rdbms/admin/utlrp.sql

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

TIMESTAMP

COMP_TIMESTAMP UTLRP_BGN 2017-12-31 15:33:44

DOC > The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC > objects in the database. Recompilation time is proportional to the

DOC > number of invalid objects in the database, so this command may take

DOC > a long time to execute on a database with a large number of invalid

DOC > objects.

DOC >

DOC > Use the following queries to track recompilation progress:

DOC >

DOC > 1. Query returning the number of invalid objects remaining. This

DOC > number should decrease with time.

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

DOC >

DOC > 2. Query returning the number of objects compiled so far. This number

DOC > should increase with time.

DOC > SELECT COUNT (*) FROM UTL_RECOMP_COMPILED

DOC >

DOC > This script automatically chooses serial or parallel recompilation

DOC > based on the number of CPUs available (parameter cpu_count) multiplied

DOC > by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC > On RAC, this number is added across all RAC nodes.

DOC >

DOC > UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC > recompilation. Jobs are created without instance affinity so that they

DOC > can migrate across RAC nodes. Use the following queries to verify

DOC > whether UTL_RECOMP jobs are being created and run correctly:

DOC >

DOC > 1. Query showing jobs created by UTL_RECOMP

DOC > SELECT job_name FROM dba_scheduler_jobs

DOC > WHERE job_name like 'UTL_RECOMP_SLAVE_%'

DOC >

DOC > 2.Query showing UTL_RECOMP jobs that are running

DOC > SELECT job_name FROM dba_scheduler_running_jobs

DOC > WHERE job_name like 'UTL_RECOMP_SLAVE_%'

DOC > #

PL/SQL procedure successfully completed.

TIMESTAMP

-

COMP_TIMESTAMP UTLRP_END 2017-12-31 15:35:35

DOC > The following query reports the number of objects that have compiled

DOC > with errors.

DOC >

DOC > If the number is higher than expected, please examine the error

DOC > messages reported with each object (using SHOW ERRORS) to see if they

DOC > point to system misconfiguration or resource constraints that must be

DOC > fixed before attempting to recompile these objects.

DOC > #

OBJECTS WITH ERRORS

-

0

DOC > The following query reports the number of errors caught during

DOC > recompilation. If this number is non-zero, please query the error

DOC > messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC > are due to misconfiguration or resource constraints that must be

DOC > fixed before objects can compile successfully.

DOC > #

ERRORS DURING RECOMPILATION

-

0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL >

9.4 View failure objects

SELECT count (*) FROM dba_invalid_objects

If the failure object is the failure object before the upgrade, the object is not invalidated during the upgrade process.

SQL > SELECT count (*) FROM dba_invalid_objects

COUNT (*)

-

one

SQL > col owner for A10

SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_invalid_objects

OWNER OBJECT_NAME OBJECT_TYPE STATUS

-

SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID

SQL >

-- Source database query:

SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV'

OWNER OBJECT_NAME OBJECT_TYPE STATUS

SH FWEEK_PSCAT_SALES_MV TABLE VALID

SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW VALID

SQL >

-- Target database query:

SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV'

OWNER OBJECT_NAME OBJECT_TYPE STATUS

-

SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID

SH FWEEK_PSCAT_SALES_MV TABLE VALID

SQL >

-- recompile the target library:

QL > alter MATERIALIZED VIEW sh.FWEEK_PSCAT_SALES_MV compile

Materialized view altered.

SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV'

OWNER OBJECT_NAME OBJECT_TYPE STATUS

-

SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW VALID

SH FWEEK_PSCAT_SALES_MV TABLE VALID

-- execute utluiobj.sql script to list invalid objects

[oracle@testdb admin] $pwd

/ u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin

[oracle@testdb admin] $

[oracle@testdb admin] $ls utluiobj.sql

Utluiobj.sql

[oracle@testdb admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 16:15:49 2017

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

Connected to:

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

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

SQL > @ utluiobj.sql

.

Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 12-31-2017 16:16:00

.

This tool lists post-upgrade invalid objects that were not invalid

Prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

.

Owner Object Name Object Type

.

PL/SQL procedure successfully completed.

SQL >

9.5 check script @? / rdbms/admin/utlu112s.sql after running the upgrade

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

.

Oracle Database 11.2 Post-Upgrade Status Tool 12-31-2017 15:39:37

.

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

.

Oracle Server

. VALID 11.2.0.4.0 00:12:16

JServer JAVA Virtual Machine

. VALID 11.2.0.4.0 00:09:05

Oracle Workspace Manager

. VALID 11.2.0.4.0 00:00:35

OLAP Analytic Workspace

. VALID 11.2.0.4.0 00:01:16

OLAP Catalog

. VALID 11.2.0.4.0 00:00:47

Oracle OLAP API

. VALID 11.2.0.4.0 00:00:33

Oracle Enterprise Manager

. VALID 11.2.0.4.0 00:07:43

Oracle XDK

. VALID 11.2.0.4.0 00:01:31

Oracle Text

. VALID 11.2.0.4.0 00:00:50

Oracle XML Database

. VALID 11.2.0.4.0 00:04:39

Oracle Database Java Packages

. VALID 11.2.0.4.0 00:00:16

Oracle Multimedia

. VALID 11.2.0.4.0 00:04:04

Spatial

. VALID 11.2.0.4.0 00:04:47

Oracle Expression Filter

. VALID 11.2.0.4.0 00:00:13

Oracle Rule Manager

. VALID 11.2.0.4.0 00:00:10

Final Actions

. 00:00:26

Total Upgrade Time: 00:49:24

PL/SQL procedure successfully completed.

SQL >

9.6 Application of PSU at database level

Cd $ORACLE_HOME/rdbms/admin/

SQL > @ catbundle.sql psu apply

[oracle@testdb ~] $cd $ORACLE_HOME/rdbms/admin/

[oracle@testdb admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:40:51 2017

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

Connected to:

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

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

SQL > @ catbundle.sql psu apply

Omit.

..

SQL > COMMIT

Commit complete.

SQL > SPOOL off

SQL > SET echo off

Check the following log file for errors:

/ u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2017Dec31_15_41_00.log

SQL >

-- check the information about the opatch patch:

[oracle@testdb dbhome_1] $cd $ORACLE_HOME/OPatch

[oracle@testdb OPatch] $. / opatch lspatches

Database Patch Set Update: 11.2.0.4.170418 (24732075)

OPatch succeeded.

[oracle@testdb OPatch] $

[oracle@testdb OPatch] $. / opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.12

Copyright (c) 2017, Oracle Corporation. All rights reserved.

Oracle Home: / u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory: / u01/app/oraInventory

From: / u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version: 11.2.0.3.12

OUI version: 11.2.0.4.0

Log file location: / u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-31, 15-44-55PM_1.log

Lsinventory Output file location: / u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-12-31, 15-44-55PM.txt

Local Machine Information::

Hostname: testdb

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0

There are 1 products installed in this Oracle Home.

Interim patches (1):

Patch 24732075: applied on Sun Dec 31 13:10:55 CST 2017

Unique Patch ID: 21176096

Patch description: "Database Patch Set Update: 11.2.0.4.170418 (24732075)"

Created on 22 Feb 2017, 21:40:49 hrs PST8PDT

Sub-patch 24006111; "Database Patch Set Update: 11.2.0.4.161018 (24006111)"

Sub-patch 23054359; "Database Patch Set Update: 11.2.0.4.160719 (23054359)"

Sub-patch 22502456; "Database Patch Set Update: 11.2.0.4.160419 (22502456)"

Sub-patch 21948347; "Database Patch Set Update: 11.2.0.4.160119 (21948347)"

Sub-patch 21352635; "Database Patch Set Update: 11.2.0.4.8 (21352635)"

Sub-patch 20760982; "Database Patch Set Update: 11.2.0.4.7 (20760982)"

Sub-patch 20299013; "Database Patch Set Update: 11.2.0.4.6 (20299013)"

Sub-patch 19769489; "Database Patch Set Update: 11.2.0.4.5 (19769489)"

Sub-patch 19121551; "Database Patch Set Update: 11.2.0.4.4 (19121551)"

Sub-patch 18522509; "Database Patch Set Update: 11.2.0.4.3 (18522509)"

Sub-patch 18031668; "Database Patch Set Update: 11.2.0.4.2 (18031668)"

Sub-patch 17478514; "Database Patch Set Update: 11.2.0.4.1 (17478514)"

Bugs fixed:

17288409, 21051852, 24316947, 17811429, 17205719, 18607546, 20506699

17816865, 17922254, 23330119, 17754782, 16934803, 13364795, 17311728

17284817, 17441661, 24560906, 16992075, 17446237, 14015842, 19972569

21756677, 17375354, 20925795, 21538558, 17449815, 19463897, 13866822

17235750, 17982555, 17478514, 18317531, 14338435, 18235390, 20803583

13944971, 20142975, 17811789, 16929165, 18704244, 20506706, 17546973

20334344, 14054676, 17088068, 17346091, 18264060, 17343514, 21538567

19680952, 18471685, 19211724, 13951456, 21847223, 16315398, 18744139

16850630, 23177648, 19049453, 18673304, 17883081, 19915271, 18641419

18262334, 17006183, 16065166, 18277454, 16833527, 10136473, 18051556

17865671, 17852463, 18554871, 17853498, 18334586, 17551709, 17588480

19827973, 17344412, 17842825, 18828868, 17025461, 11883252, 13609098

17239687, 17602269, 19197175, 18316692, 22195457, 17313525, 12611721

19544839, 18964939, 17600719, 18191164, 19393542, 17571306, 20777150

18482502, 19466309, 22243719, 17040527, 17165204, 18098207, 16785708

17465741, 17174582, 16180763, 12982566, 16777840, 19463893, 22195465

16875449, 12816846, 22148226, 17237521, 6599380, 19358317, 25505394

17811438, 17811447, 17945983, 21983325, 18762750, 16912439, 17184721

18061914, 17282229, 18331850, 18202441, 17082359, 18723434, 21972320

19554106, 25505371, 14034426, 18339044, 19458377, 17752995, 20448824

17891943, 17258090, 17767676, 16668584, 18384391, 17040764, 17381384

15913355, 18356166, 14084247, 20596234, 20506715, 21756661, 13853126

18203837, 14245531, 16043574, 21756699, 22195441, 17848897, 17877323

21453153, 17468141, 20861693, 17786518, 17912217, 17037130, 16956380

18155762, 17478145, 17394950, 18641461, 18189036, 18619917, 17027426

21352646, 16268425, 24476274, 22195492, 19584068, 18436307, 22507210

17265217, 17634921, 13498382, 21526048, 19258504, 20004087, 17443671

22195485, 18000422, 22321756, 20004021, 17571039, 21067387, 22905130

16344544, 18009564, 14354737, 21286665, 18135678, 18614015, 20441797

18362222, 17835048, 16472716, 17936109, 17050888, 14010183, 17325413

18747196, 17761775, 16721594, 17082983, 20067212, 21179898, 17302277

18084625, 15990359, 24842886, 18203835, 17297939, 17811456, 22380919

16731148, 21168487, 14133975, 13829543, 17215560, 17694209, 17385178

18091059, 8322815, 17586955, 17201159, 17655634, 18331812, 19730508

18868646, 17648596, 16220077, 16069901, 17348614, 17393915, 17274537

17957017, 18096714, 17308789, 18436647, 14285317, 19289642, 14764829

17622427, 18328509, 16943711, 22195477, 14368995, 22502493, 17346671

18996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224

17851160, 17546761, 17798953, 18273830, 22092979, 16596890, 19972566

16384983, 17726838, 22296366, 17360606, 22321741, 13645875, 18199537

16542886, 21787056, 17889549, 14565184, 17071721, 17610798, 20299015

21343897, 22893153, 20657441, 17397545, 18230522, 16360112, 19769489

12905058, 18641451, 12747740, 18430495, 17016369, 17042658, 14602788

17551063, 19972568, 21517440, 18508861, 19788842, 14657740, 17332800

13837378, 19972564, 17186905, 18315328, 19699191, 17437634, 22353199

18093615, 19006849, 19013183, 17296856, 18674024, 17232014, 16855292

17762296, 14692762, 21051840, 17705023, 22507234, 19121551, 21330264

19854503, 21868720, 19309466, 18681862, 20558005, 18554763, 17390160

18456514, 16306373, 13955826, 18139690, 17501491, 17752121, 21668627

17299889, 17889583, 18673325, 19721304, 18293054, 17242746, 17951233

18094246, 17649265, 19615136, 17011832, 16870214, 17477958, 18522509

20631274, 16091637, 17323222, 16595641, 16524926, 18228645, 18282562

17596908, 18031668, 17156148, 16494615, 22683225, 17545847, 25093656

17655240, 24528741, 17614134, 13558557, 17341326, 17891946, 17716305

22657942, 18440095, 16392068, 19271443, 21351877, 18092127, 17614227

18440047, 16903536, 14106803, 18973907, 18673342, 25505382, 19032867

17389192, 17612828, 16194160, 17006570, 25369547, 25505407, 17721717

17390431, 17570240, 16863422, 18325460, 19727057, 16422541, 19972570

17267114, 18244962, 21538485, 18765602, 18203838, 16198143, 17246576

14829250, 17835627, 18247991, 14458214, 21051862, 16692232, 17786278

17227277, 24476265, 16042673, 16314254, 16228604, 16837842, 17393683

23536835, 17787259, 20331945, 20074391, 15861775, 16399083, 18018515

22683212, 18260550, 21051858, 17080436, 16613964, 17036973, 16579084

24433711, 18384537, 18280813, 20296213, 16901385, 15979965, 23330124

18441944, 16450169, 9756271, 17892268, 11733603, 16285691, 17587063

21343775, 18180390, 16538760, 18193833, 21387964, 21051833, 17238511

17824637, 16571443, 18306996, 14852021, 17853456, 18674047, 12364061

24411921, 22195448

OPatch succeeded.

[oracle@testdb OPatch] $

-- query dba_registry_history:

DBA_REGISTRY_HISTORY:

DBA_REGISTRY_HISTORY provides information about upgrades, downgrades, and critical patch updates that have been performed on the database.

SQL > col ACTION_TIME for A30

SQL > col ACTION for A20

SQL > col NAMESPACE for A15

SQL > col VERSION for A15

SQL > col BUNDLE_SERIES for A15

SQL > col COMMENTS for A50

SQL > select * from dba_registry_history

ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS

-

19-OCT-17 02.48.11.467752 PM VIEW RECOMPILE 8289601 view recompilation

19-OCT-17 02.48.11.862564 PM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.1.0

31-DEC-17 02.55.55.820329 PM VIEW INVALIDATE 8289601 view invalidation

31-DEC-17 02.56.22.289206 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.5.0

31-DEC-17 03.41.37.543270 PM APPLY SERVER 11.2.0.4 170418 PSU PSU 11.2.0.4.170418

SQL >

At this point, the database offline upgrade operation has been completed!

The above is the editor for you to share the Oracle database upgrade is how, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.