将10G的数据库库异机还原到11.2.0.4数据库中，并将库升级到11.2.0.4（三、升级）

王亮

在异机恢复完成后，有时临时表空间数据文件并没有恢复，需要手动增加临时表空间的数据文件，否则升级过程会因为临时表空间的问题导致升级失败
--手动增加临时文件
SQL> alter tablespace temp add  tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 500M;

Tablespace altered.

--删除原来临时文件记录
SQL> alter tablespace temp drop tempfile 1;

Tablespace altered.

--创建spfile
SQL> create spfile from pfile='/home/oracle/orcl10.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area  709836800 bytes
Fixed Size                  2256632 bytes
Variable Size             255852808 bytes
Database Buffers          444596224 bytes
Redo Buffers                7131136 bytes
Database mounted.
Database opened.

--调整快速恢复区
SQL> show parameter db_recovery_file_size
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

SQL>

--调整归档存放目录
SQL> alter system set log_archive_dest_1='location=/u01/app/arch_log';

System altered.


--升级数据库

SQL> alter database open resetlogs upgrade;


SQL> spool upgrade.log

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

Oracle Database 11.2 Post-Upgrade Status Tool           09-08-2020 02:58:57
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:06:58
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:05:22
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:18
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:36
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:22
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:15
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:04:08
Oracle XDK
.                                         VALID      11.2.0.4.0  00:01:07
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:24
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:02:28
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:09
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:02:21
Spatial
.                                         VALID      11.2.0.4.0  00:02:41
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Rule Manager
.                                         VALID      11.2.0.4.0  00:00:04
Final Actions
.                                                                00:00:21
Total Upgrade Time: 00:27:48

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;

Commit complete.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above sql script is the final step of the upgrade. Please
DOC>   review any errors in the spool log file. If there are any errors in
DOC>   the spool file, consult the Oracle Database Upgrade Guide for
DOC>   troubleshooting recommendations.
DOC>
DOC>   Next restart for normal operation, and then run utlrp.sql to
DOC>   recompile any invalid application objects.
DOC>
DOC>   If the source database had an older time zone version prior to
DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade
DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC>   with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--执行exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

--重新编译无效对象

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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2020-09-08 03:05:08

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2020-09-08 03:06:44

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>
到此数据库升级就完成了，可以使用sqlplus等工具对数据库进行验证和测试了