Oracle数据库异机升级

echo $ORACLE_BASE
echo $ORACLE_HOME
echo $ORACLE_SID
echo $PATH

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 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts

$ scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.xx.xx:/home/oracle          

[oracle@oracle10g oracle]$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 26 06:46:46 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
SQL> @/home/oracle/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-28-2015 10:02:57
Script Version: 11.2.0.4.0 Build: 007
.
**********************************************************************
Database:
**********************************************************************
--> name:          VAS
--> 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: 991 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 790 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
--> 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: --> 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 CRNOPHQOA has 2 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.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2 (11.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 32  (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> spool off

select file_name,tablespace_name,maxbytes/1024/1024 from dba_data_files
where tablespace_name in('SYSTEM','UNDOTBS1','SYSAUX')
union all
select file_name,tablespace_name,maxbytes/1024/1024 from dba_temp_files;

SQL> create pfile='/home/oracle/orcl10.pfile' from spfile;

$ scp /home/oracle/orcl10.pfile 192.168.xx.xx:/home/oracle

注意下sga和pga的设定是否符合目的机器的配置
*.sga_target=629145600
*.pga_aggregate_target=25165824
将*.background_dump_dest和*.user_dump_dest删除
加上 *.diagnostic_dest='$ORACLE_BASE'
注：参数文件的修改不用多说，但需要注意其他各参数的值是否符合需求。

$ cp /home/oracle/orcl10.pfile $ORACLE_HOME/dbs/init$ORACLE_SID.ora

记录无效对象：
col OWNER for a10;
col OBJECT_NAME for a30;
col OBJECT_TYPE for a10;
select * from registry$nonsys_inv_objs; //非sys/system的失效对象

select * from registry$sys_inv_objs;   //sys/system的失效对象
 
SQL> @?/rdbms/admin/utlrp.sql;
重新执行 @/home/oracle/utlu112i.sql，查看是否重新编译,

SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 
PL/SQL procedure successfully completed.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

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     27
Current log sequence           29

SQL> alter database open resetlogs upgrade;

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

SQL> startup pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
SQL> create spfile from pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';

SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.

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

SQL> SELECT count(*) FROM dba_invalid_objects;

SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           08-31-2015 16:13:55
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:07:44
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:05:02
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:21
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:20
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:30
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:15
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:05:23
Oracle XDK
.                                         VALID      11.2.0.4.0  00:01:48
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:30
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:02:42
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:08
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:02:21
Spatial
.                                         VALID      11.2.0.4.0  00:03:32
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:05
Final Actions
.                                                                00:00:25
Total Upgrade Time: 00:31:21

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

cd $ORACLE_HOME/rdbms/admin/
SQL> @catbundle.sql psu apply

./opatch lspatches
./opatch lsinventory

SQL> select * from dba_registry_history;