In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.