首页 > 代码库 > 实战：Oracle10.2.0.4异机迁移并升级到Oracle 11.2.0.4
实战：Oracle10.2.0.4异机迁移并升级到Oracle 11.2.0.4
环境:
源库：192.168.8.132 oracle10.2.0.4
目标库：192.168.8.133 oracle11.2.0.4
OS:linux 5.4-64位
将原10G的库迁移到新主机133上面，数据库版本同时升级为11.2.0.4
主要步骤：
一、环境检查(源库)
1、将11g中的utlu112i.sql，cp到源主机上，进行升级检查；
2、通过dbupgdiag.sql脚本来检查源库的一致性，如果有无效对象，@utlrp.sql可以对其进行重编译
3、对源库进行备份
二、迁移到新主机
1、修改相对应的pfile文件，因为10g里面的参数在11g中有些是废弃;
2、启动数据库到nomount下，恢复控制文件，恢复全库。
3、recover database;
4、进行升级
a、alter database open resetlogs upgrade;
b、执行如下：
SQL> spool upgrade.log
SQL> @catupgrd.sql
----这个执行时间比较长，大概1个多小时。
SQL> STARTUP
## run the below utlu112s.sql file for post upgrade checking and utlrp.sql file to compile the invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
执行以上脚本，再次检查数据库状态。
5、如果升级完成后，如果DST version有问题，可以对其进行修改，具体可以通过脚本自动执行，也可以手工执行，具体参考：
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset (Doc ID 1579838.1)
下面是相当记录
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 12 15:54:36 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-12-2014 15:55:42
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.4.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: 981 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 722 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 596 MB
.
**********************************************************************
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.4.0 database timezone version
.... to the latest version which comes with the new release.
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;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),‘ ‘)) FROM sys.v$parameter2
WHERE UPPER(name) =‘EVENT‘ AND isdefault=‘FALSE‘
Trace Events:
SELECT (translate(value,chr(13)||chr(10),‘ ‘)) from sys.v$parameter2
WHERE UPPER(name) = ‘_TRACE_EVENTS‘ AND isdefault=‘FALSE‘
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off;
SQL> create pfile=‘/home/oracle/pfile.ora‘ from spfile;
SQL> alter system set sga_target=600M scope=spfile;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
如果 dbupgdiag.sql 脚本有无效对象，则运行 utlrp.sql
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
使无效对象有效之后，再次在数据库中重新运行 dbupgdiag.sql，然后确保一切正常。
SQL> alter tablespace system add datafile ‘/home/oracle/oradata/orcl/system02.dbf‘ size 650M;
Tablespace altered.
SQL> alter tablespace UNDOTBS1 add datafile ‘/home/oracle/oradata/orcl/undotbs02.dbf‘ size 400m;
Tablespace altered.
SQL> alter tablespace SYSAUX add datafile ‘/home/oracle/oradata/orcl/sysaux02.dbf‘ size 500m;
Tablespace altered.
SQL> alter tablespace temp add tempfile ‘/home/oracle/oradata/orcl/temp02.dbf‘ size 60m;
Tablespace altered.
SQL> alter tablespace EXAMPLE add datafile ‘/home/oracle/oradata/orcl/ex.dbf‘ size 80m;
Tablespace altered.
SQL>
######备份源库：
[oracle@ora1 bak]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 12 16:09:47 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1393903281)
RMAN> backup full database format ‘/home/oracle/bak/full_orcl_%U‘;
Starting backup at 12-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/home/oracle/oradata/orcl/system02.dbf
input datafile fno=00008 name=/home/oracle/oradata/orcl/sysaux02.dbf
input datafile fno=00001 name=/home/oracle/oradata/orcl/system01.dbf
input datafile fno=00007 name=/home/oracle/oradata/orcl/undotbs02.dbf
input datafile fno=00003 name=/home/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/oradata/orcl/example01.dbf
input datafile fno=00009 name=/home/oracle/oradata/orcl/ex.dbf
input datafile fno=00002 name=/home/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/full_orcl_01pq07r8_1_1 tag=TAG20141212T161015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/full_orcl_02pq07sn_1_1 tag=TAG20141212T161015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 12-DEC-14
RMAN> exit
Recovery Manager complete.
[oracle@ora1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 12 17:25:22 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1393903281)
RMAN>
RMAN>
RMAN>
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 593.46M DISK 00:00:38 12-DEC-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141212T161015
Piece Name: /home/oracle/bak/full_orcl_01pq07r8_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/system01.dbf
2 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/undotbs01.dbf
3 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/sysaux01.dbf
4 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/users01.dbf
5 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/example01.dbf
6 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/system02.dbf
7 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/undotbs02.dbf
8 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/sysaux02.dbf
9 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/ex.dbf
RMAN> exit
Recovery Manager complete.
[oracle@ora1 ~]$ pwd
/home/oracle
[oracle@ora1 ~]$ cd bak
[oracle@ora1 bak]$ ls
diag.log full_orcl_01pq07r8_1_1 full_orcl_02pq07sn_1_1
[oracle@ora1 bak]$
MAN> BACKUP ARCHIVELOG ALL format ‘/home/oracle/bak/%U‘;
Starting backup at 12-DEC-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=866131136
input archive log thread=1 sequence=3 recid=2 stamp=866131686
input archive log thread=1 sequence=4 recid=3 stamp=866137581
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/03pq0dg5_1_1 tag=TAG20141212T174638 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:31
Finished backup at 12-DEC-14
[oracle@ora11 bak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 11 17:27:18 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> set DBID=1393903281;
executing command: SET DBID
RMAN> restore controlfile from ‘/home/oracle/bak/full_orcl_02pq07sn_1_1‘;
Starting restore at 11-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/home/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/oradata/orcl/control02.ctl
output file name=/home/oracle/oradata/orcl/control03.ctl
Finished restore at 11-DEC-14
RMAN> sql ‘alter database mount‘;
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 11-DEC-14
Starting implicit crosscheck backup at 11-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 11-DEC-14
Starting implicit crosscheck copy at 11-DEC-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-DEC-14
searching for all files in the recovery area
cataloging files...
no files cataloged
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 00001 to /home/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/orcl/system02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/oradata/orcl/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/oradata/orcl/sysaux02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/oradata/orcl/ex.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/full_orcl_01pq07r8_1_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/full_orcl_01pq07r8_1_1 tag=TAG20141212T161015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:45
Finished restore at 11-DEC-14
RMAN>
alter日志
Completed: alter database mount
Thu Dec 11 17:29:16 2014
Checker run found 10 new persistent data failures
Thu Dec 11 17:32:38 2014
Full restore complete of datafile 6 /home/oracle/oradata/orcl/system02.dbf. Elapsed time: 0:03:21
checkpoint is 645792
Full restore complete of datafile 8 /home/oracle/oradata/orcl/sysaux02.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 7 /home/oracle/oradata/orcl/undotbs02.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 9 /home/oracle/oradata/orcl/ex.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 4 /home/oracle/oradata/orcl/users01.dbf. Elapsed time: 0:00:01
checkpoint is 645792
Thu Dec 11 17:32:49 2014
Full restore complete of datafile 2 /home/oracle/oradata/orcl/undotbs01.dbf. Elapsed time: 0:03:35
checkpoint is 645792
last deallocation scn is 642787
Thu Dec 11 17:33:08 2014
Full restore complete of datafile 5 /home/oracle/oradata/orcl/example01.dbf. Elapsed time: 0:03:50
checkpoint is 645792
last deallocation scn is 617217
Thu Dec 11 17:33:38 2014
Full restore complete of datafile 3 /home/oracle/oradata/orcl/sysaux01.dbf. Elapsed time: 0:04:22
checkpoint is 645792
last deallocation scn is 635554
Thu Dec 11 17:33:55 2014
Full restore complete of datafile 1 /home/oracle/oradata/orcl/system01.dbf. Elapsed time: 0:04:38
checkpoint is 645792
last deallocation scn is 637323
Thu Dec 11 17:36:53 2014
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
RMAN> recover database;
Starting recover at 11-DEC-14
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/11/2014 17:38:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 645792
====需要指定具体的序列号，同时把源库的归档备份注册进来。
RMAN> CATALOG BACKUPPIECE ‘/home/oracle/bak/03pq0dg5_1_1‘;
cataloged backup piece
backup piece handle=/home/oracle/bak/03pq0dg5_1_1 RECID=2 STAMP=866051762
RMAN> run {
set until scn 645792;
recover database;
}2> 3> 4>
executing command: SET until clause
Starting recover at 11-DEC-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 11-DEC-14
RMAN>
RMAN>
RMAN>
===如果有数据库连的话，要提前生成SQL语句，恢复完成后，在新库上重建一下。
SELECT
‘create ‘||DECODE(U.NAME,‘PUBLIC‘,‘public ‘)||‘database link ‘||CHR(10)
||DECODE(U.NAME,‘PUBLIC‘,Null, U.NAME||‘.‘)|| L.NAME||chr(10)
||‘connect to ‘ || L.USERID || ‘ identified by ‘‘‘
||L.PASSWORD||‘‘‘ using ‘‘‘ || L.host || ‘‘‘‘
||chr(10)||‘;‘ TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
SQL> alter database open resetlogs upgrade;
Database altered.
SQL>
SQL> spool upgrade.log
SQL> @catupgrd.sql
=====这个时间比较长，需要耐心等待
#### 完成后
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
运行以下检查再次进行检查
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
####下面是相关DST版本
SQL> l
1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%‘
4* ORDER BY PROPERTY_NAME
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
####具体升级DST，可以分手工和自动，我是通过自动方式升级的，请参考以下文档。
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
下面是相关日志：
[oracle@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 11:38:12 2014
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> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME
------------------------------
VALUE
------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
SQL> set linesize 2000
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> l
1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%‘
4* ORDER BY PROPERTY_NAME
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select VERSION from v$timezone_file;
select TZ_VERSION from registry$database;
VERSION
----------
4
SQL>
TZ_VERSION
----------
4
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL>
SQL>
SQL> @?/rdbms/admin/upg_tzv_check.sql
SP2-0310: unable to open file "/home/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/upg_tzv_check.sql"
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@ora11 ~]$ vi check.sql
[oracle@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 12:21:13 2014
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> @check
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 11.2.0.4 .
INFO: Database RDBMS DST version is DSTv4 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv14 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> SQL>
SQL> !
vi [oracle@ora11 ~]$ vi app.sql
[oracle@ora11 ~]$ exit
exit
SQL> @app.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv14 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/home/oracle/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora‘
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 35 Serial number: 49
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@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 12:25:34 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=‘/home/oracle/bak/pfile.ora‘;
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile=‘/home/oracle/bak/pfile.ora‘;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
SQL> @app.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv14 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "SYSMAN"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_STAGING_DIRECTORIES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_ASSIGNMENT"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv14 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> select substr(comp_id,1,15) comp_id,
substr(comp_name,1,30) comp_name,
substr(version,1,10) version,
status
from dba_registry 2 3 4 5
6 /
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
EM
Oracle Enterprise Manager
11.2.0.4.0 VALID
AMD
OLAP Catalog
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
SDO
Spatial
11.2.0.4.0 VALID
ORDIM
Oracle Multimedia
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
11.2.0.4.0 VALID
XDB
Oracle XML Database
11.2.0.4.0 VALID
CONTEXT
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
Oracle Text
11.2.0.4.0 VALID
ODM
Oracle Data Mining
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
EXF
Oracle Expression Filter
11.2.0.4.0 VALID
RUL
Oracle Rules Manager
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
OWM
Oracle Workspace Manager
11.2.0.4.0 VALID
CATALOG
Oracle Database Catalog Views
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
11.2.0.4.0 VALID
CATPROC
Oracle Database Packages and T
11.2.0.4.0 VALID
JAVAVM
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
JServer JAVA Virtual Machine
11.2.0.4.0 VALID
XML
Oracle XDK
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
CATJAVA
Oracle Database Java Packages
11.2.0.4.0 VALID
APS
OLAP Analytic Workspace
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
XOQ
Oracle OLAP API
11.2.0.4.0 VALID
17 rows selected.
SQL> select * from v$timezone_file;
select * from registry$database; (this shows the old value of the timezone)
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
1 row selected.
SQL> SQL> 2 SQL> SQL> 2 3 4
PROPERTY_NAME
------------------------------
VALUE
------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
3 rows selected.
SQL>
SQL> select * from registry$database;
PLATFORM_ID
-----------
PLATFORM_NAME
--------------------------------------------------------------------------------
EDITION TZ_VERSION
------------------------------ ----------
13
Linux x86 64-bit
14
1 row selected.
SQL> exit
源库：192.168.8.132 oracle10.2.0.4
目标库：192.168.8.133 oracle11.2.0.4
OS:linux 5.4-64位
将原10G的库迁移到新主机133上面，数据库版本同时升级为11.2.0.4
主要步骤：
一、环境检查(源库)
1、将11g中的utlu112i.sql，cp到源主机上，进行升级检查；
2、通过dbupgdiag.sql脚本来检查源库的一致性，如果有无效对象，@utlrp.sql可以对其进行重编译
3、对源库进行备份
二、迁移到新主机
1、修改相对应的pfile文件，因为10g里面的参数在11g中有些是废弃;
2、启动数据库到nomount下，恢复控制文件，恢复全库。
3、recover database;
4、进行升级
a、alter database open resetlogs upgrade;
b、执行如下：
SQL> spool upgrade.log
SQL> @catupgrd.sql
----这个执行时间比较长，大概1个多小时。
SQL> STARTUP
## run the below utlu112s.sql file for post upgrade checking and utlrp.sql file to compile the invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
执行以上脚本，再次检查数据库状态。
5、如果升级完成后，如果DST version有问题，可以对其进行修改，具体可以通过脚本自动执行，也可以手工执行，具体参考：
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset (Doc ID 1579838.1)
下面是相当记录
源库环境检查：
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 12 15:54:36 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-12-2014 15:55:42
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.4.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: 981 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 722 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 596 MB
.
**********************************************************************
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.4.0 database timezone version
.... to the latest version which comes with the new release.
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;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),‘ ‘)) FROM sys.v$parameter2
WHERE UPPER(name) =‘EVENT‘ AND isdefault=‘FALSE‘
Trace Events:
SELECT (translate(value,chr(13)||chr(10),‘ ‘)) from sys.v$parameter2
WHERE UPPER(name) = ‘_TRACE_EVENTS‘ AND isdefault=‘FALSE‘
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off;
SQL> create pfile=‘/home/oracle/pfile.ora‘ from spfile;
SQL> alter system set sga_target=600M scope=spfile;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
===根据以上检查结果，对源库进行调整；
如果 dbupgdiag.sql 脚本有无效对象，则运行 utlrp.sql
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
使无效对象有效之后，再次在数据库中重新运行 dbupgdiag.sql，然后确保一切正常。
SQL> alter tablespace system add datafile ‘/home/oracle/oradata/orcl/system02.dbf‘ size 650M;
Tablespace altered.
SQL> alter tablespace UNDOTBS1 add datafile ‘/home/oracle/oradata/orcl/undotbs02.dbf‘ size 400m;
Tablespace altered.
SQL> alter tablespace SYSAUX add datafile ‘/home/oracle/oradata/orcl/sysaux02.dbf‘ size 500m;
Tablespace altered.
SQL> alter tablespace temp add tempfile ‘/home/oracle/oradata/orcl/temp02.dbf‘ size 60m;
Tablespace altered.
SQL> alter tablespace EXAMPLE add datafile ‘/home/oracle/oradata/orcl/ex.dbf‘ size 80m;
Tablespace altered.
SQL>
######备份源库：
[oracle@ora1 bak]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 12 16:09:47 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1393903281)
RMAN> backup full database format ‘/home/oracle/bak/full_orcl_%U‘;
Starting backup at 12-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/home/oracle/oradata/orcl/system02.dbf
input datafile fno=00008 name=/home/oracle/oradata/orcl/sysaux02.dbf
input datafile fno=00001 name=/home/oracle/oradata/orcl/system01.dbf
input datafile fno=00007 name=/home/oracle/oradata/orcl/undotbs02.dbf
input datafile fno=00003 name=/home/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/oradata/orcl/example01.dbf
input datafile fno=00009 name=/home/oracle/oradata/orcl/ex.dbf
input datafile fno=00002 name=/home/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/full_orcl_01pq07r8_1_1 tag=TAG20141212T161015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/full_orcl_02pq07sn_1_1 tag=TAG20141212T161015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 12-DEC-14
RMAN> exit
Recovery Manager complete.
[oracle@ora1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 12 17:25:22 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1393903281)
RMAN>
RMAN>
RMAN>
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 593.46M DISK 00:00:38 12-DEC-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141212T161015
Piece Name: /home/oracle/bak/full_orcl_01pq07r8_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/system01.dbf
2 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/undotbs01.dbf
3 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/sysaux01.dbf
4 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/users01.dbf
5 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/example01.dbf
6 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/system02.dbf
7 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/undotbs02.dbf
8 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/sysaux02.dbf
9 Full 645792 12-DEC-14 /home/oracle/oradata/orcl/ex.dbf
RMAN> exit
Recovery Manager complete.
[oracle@ora1 ~]$ pwd
/home/oracle
[oracle@ora1 ~]$ cd bak
[oracle@ora1 bak]$ ls
diag.log full_orcl_01pq07r8_1_1 full_orcl_02pq07sn_1_1
[oracle@ora1 bak]$
MAN> BACKUP ARCHIVELOG ALL format ‘/home/oracle/bak/%U‘;
Starting backup at 12-DEC-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=866131136
input archive log thread=1 sequence=3 recid=2 stamp=866131686
input archive log thread=1 sequence=4 recid=3 stamp=866137581
channel ORA_DISK_1: starting piece 1 at 12-DEC-14
channel ORA_DISK_1: finished piece 1 at 12-DEC-14
piece handle=/home/oracle/bak/03pq0dg5_1_1 tag=TAG20141212T174638 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:31
Finished backup at 12-DEC-14
####在目标库主机上通过RMAN恢复源库
把备份集cp到目标主机上面，并在目标主机上面恢复数据库：
修改spfile中的参数：*.diagnostic_dest=‘/home/oracle‘，并把background_dump_dest、core_dump_dest等参数去掉，因为在11G中已经不用这些参数了。[oracle@ora11 bak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 11 17:27:18 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> set DBID=1393903281;
executing command: SET DBID
RMAN> restore controlfile from ‘/home/oracle/bak/full_orcl_02pq07sn_1_1‘;
Starting restore at 11-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/home/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/oradata/orcl/control02.ctl
output file name=/home/oracle/oradata/orcl/control03.ctl
Finished restore at 11-DEC-14
RMAN> sql ‘alter database mount‘;
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 11-DEC-14
Starting implicit crosscheck backup at 11-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 11-DEC-14
Starting implicit crosscheck copy at 11-DEC-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-DEC-14
searching for all files in the recovery area
cataloging files...
no files cataloged
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 00001 to /home/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/orcl/system02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/oradata/orcl/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/oradata/orcl/sysaux02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/oradata/orcl/ex.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/full_orcl_01pq07r8_1_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/full_orcl_01pq07r8_1_1 tag=TAG20141212T161015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:45
Finished restore at 11-DEC-14
RMAN>
alter日志
Completed: alter database mount
Thu Dec 11 17:29:16 2014
Checker run found 10 new persistent data failures
Thu Dec 11 17:32:38 2014
Full restore complete of datafile 6 /home/oracle/oradata/orcl/system02.dbf. Elapsed time: 0:03:21
checkpoint is 645792
Full restore complete of datafile 8 /home/oracle/oradata/orcl/sysaux02.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 7 /home/oracle/oradata/orcl/undotbs02.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 9 /home/oracle/oradata/orcl/ex.dbf. Elapsed time: 0:03:25
checkpoint is 645792
Full restore complete of datafile 4 /home/oracle/oradata/orcl/users01.dbf. Elapsed time: 0:00:01
checkpoint is 645792
Thu Dec 11 17:32:49 2014
Full restore complete of datafile 2 /home/oracle/oradata/orcl/undotbs01.dbf. Elapsed time: 0:03:35
checkpoint is 645792
last deallocation scn is 642787
Thu Dec 11 17:33:08 2014
Full restore complete of datafile 5 /home/oracle/oradata/orcl/example01.dbf. Elapsed time: 0:03:50
checkpoint is 645792
last deallocation scn is 617217
Thu Dec 11 17:33:38 2014
Full restore complete of datafile 3 /home/oracle/oradata/orcl/sysaux01.dbf. Elapsed time: 0:04:22
checkpoint is 645792
last deallocation scn is 635554
Thu Dec 11 17:33:55 2014
Full restore complete of datafile 1 /home/oracle/oradata/orcl/system01.dbf. Elapsed time: 0:04:38
checkpoint is 645792
last deallocation scn is 637323
Thu Dec 11 17:36:53 2014
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
RMAN> recover database;
Starting recover at 11-DEC-14
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/11/2014 17:38:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 645792
====需要指定具体的序列号，同时把源库的归档备份注册进来。
RMAN> CATALOG BACKUPPIECE ‘/home/oracle/bak/03pq0dg5_1_1‘;
cataloged backup piece
backup piece handle=/home/oracle/bak/03pq0dg5_1_1 RECID=2 STAMP=866051762
RMAN> run {
set until scn 645792;
recover database;
}2> 3> 4>
executing command: SET until clause
Starting recover at 11-DEC-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 11-DEC-14
RMAN>
RMAN>
RMAN>
===如果有数据库连的话，要提前生成SQL语句，恢复完成后，在新库上重建一下。
SELECT
‘create ‘||DECODE(U.NAME,‘PUBLIC‘,‘public ‘)||‘database link ‘||CHR(10)
||DECODE(U.NAME,‘PUBLIC‘,Null, U.NAME||‘.‘)|| L.NAME||chr(10)
||‘connect to ‘ || L.USERID || ‘ identified by ‘‘‘
||L.PASSWORD||‘‘‘ using ‘‘‘ || L.host || ‘‘‘‘
||chr(10)||‘;‘ TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
####升级
SQL> alter database open resetlogs upgrade;
Database altered.
SQL>
SQL> spool upgrade.log
SQL> @catupgrd.sql
=====这个时间比较长，需要耐心等待
#### 完成后
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
运行以下检查再次进行检查
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
####下面是相关DST版本
SQL> l
1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%‘
4* ORDER BY PROPERTY_NAME
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
####具体升级DST，可以分手工和自动，我是通过自动方式升级的，请参考以下文档。
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
下面是相关日志：
[oracle@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 11:38:12 2014
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> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME
------------------------------
VALUE
------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
SQL> set linesize 2000
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> l
1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%‘
4* ORDER BY PROPERTY_NAME
SQL> /
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select VERSION from v$timezone_file;
select TZ_VERSION from registry$database;
VERSION
----------
4
SQL>
TZ_VERSION
----------
4
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL>
SQL>
SQL>
SQL> @?/rdbms/admin/upg_tzv_check.sql
SP2-0310: unable to open file "/home/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/upg_tzv_check.sql"
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@ora11 ~]$ vi check.sql
[oracle@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 12:21:13 2014
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> @check
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 11.2.0.4 .
INFO: Database RDBMS DST version is DSTv4 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv14 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> SQL>
SQL> !
vi [oracle@ora11 ~]$ vi app.sql
[oracle@ora11 ~]$ exit
exit
SQL> @app.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv14 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/home/oracle/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora‘
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 35 Serial number: 49
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@ora11 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 12:25:34 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=‘/home/oracle/bak/pfile.ora‘;
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile=‘/home/oracle/bak/pfile.ora‘;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
SQL> @app.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv14 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2255552 bytes
Variable Size 226493760 bytes
Database Buffers 364904448 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "SYSMAN"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_STAGING_DIRECTORIES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROVISION_ASSIGNMENT"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv14 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> select substr(comp_id,1,15) comp_id,
substr(comp_name,1,30) comp_name,
substr(version,1,10) version,
status
from dba_registry 2 3 4 5
6 /
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
EM
Oracle Enterprise Manager
11.2.0.4.0 VALID
AMD
OLAP Catalog
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
SDO
Spatial
11.2.0.4.0 VALID
ORDIM
Oracle Multimedia
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
11.2.0.4.0 VALID
XDB
Oracle XML Database
11.2.0.4.0 VALID
CONTEXT
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
Oracle Text
11.2.0.4.0 VALID
ODM
Oracle Data Mining
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
EXF
Oracle Expression Filter
11.2.0.4.0 VALID
RUL
Oracle Rules Manager
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
OWM
Oracle Workspace Manager
11.2.0.4.0 VALID
CATALOG
Oracle Database Catalog Views
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
11.2.0.4.0 VALID
CATPROC
Oracle Database Packages and T
11.2.0.4.0 VALID
JAVAVM
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
JServer JAVA Virtual Machine
11.2.0.4.0 VALID
XML
Oracle XDK
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
CATJAVA
Oracle Database Java Packages
11.2.0.4.0 VALID
APS
OLAP Analytic Workspace
11.2.0.4.0 VALID
COMP_ID
------------------------------
COMP_NAME
------------------------------------------------------------
VERSION STATUS
-------------------- ----------------------
XOQ
Oracle OLAP API
11.2.0.4.0 VALID
17 rows selected.
SQL> select * from v$timezone_file;
select * from registry$database; (this shows the old value of the timezone)
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%‘
ORDER BY PROPERTY_NAME;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
1 row selected.
SQL> SQL> 2 SQL> SQL> 2 3 4
PROPERTY_NAME
------------------------------
VALUE
------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
3 rows selected.
SQL>
SQL> select * from registry$database;
PLATFORM_ID
-----------
PLATFORM_NAME
--------------------------------------------------------------------------------
EDITION TZ_VERSION
------------------------------ ----------
13
Linux x86 64-bit
14
1 row selected.
SQL> exit
至此，升级完成。
实战：Oracle10.2.0.4异机迁移并升级到Oracle 11.2.0.4
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。