其他相关文章Redhat 7.x 从11.2.0.4升级到18.0.0(DBUA) 从11.2.0.4升级到18.0.0 non-CDB(手动) 从11.2.0.4升级到18.0.0 non-CDB(手动) 说明

本节通过手动执行脚本方式升级，因为使用DBUA升级方式过于简单，参考前面章节即可。过程也非常简单，首先安装新版本的数据库软件并创建数据库(CDB模式)，然后将non-CDB插入到CDB中。

1.1.1. 源库准备工作

1) 数据库版本

数据库版本是12.2.0.1，支持直接升级到18c.

SQL> select * from v$version where rownum=1;BANNER CON_ID----------------------------------------------------------------------------------------------------- ------------------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

2) 是否是CDB

为non-CDB模式

SQL> select name,cdb from v$database;NAME CDB--------- --------CNDBA NO

3) 全库备份

升级前建议进行备份，以防万一。

rman "target / nocatalog"RUN{ALLOCATE CHANNEL chan_name TYPE DISK;BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';}

4) 执行检查脚本

这里不再具体说明，请参考其他升级文章：从11.2.0.4升级到18.0.0 non-CDB(手动)。

5) 开启归档和闪回

查看是否开启闪回和归档

SQL> select log_mode,flashback_on from v$database;LOG_MODE FLASHBACK_ON------------ ------------------ARCHIVELOG YES

没有则手动开启归档和闪回。

shutdown immediate;startup mount;alter database archivelog;alter database flashback on;

6) (可选)如果是RAC，则修改CLUSTER_DATABASE为FALSE

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;

7) 关闭数据库

shutdown immediate

1) 安装18c数据库软件

具体步骤略，请参考：从11.2.0.4升级到18.0.0 non-CDB(手动)。

2) oratab文件是否指向新的ORACLE_HOME

[oracle@cndba ~]$ tail -1 /etc/orataborcl:/u01/app/oracle/product/18.1.0/db_1:N

3) 将环境变量修改为新版本数据库的目录

例如：ORACLE_HOME，ORACLE_BAES，PATH等

[oracle@12cr1 ~]$ env|grep ORACLE_HOMEORACLE_HOME=/u01/app/oracle/product/18.1.0/db_1

4) 将源库的spfile拷贝目标库

cp /u01/app/oracle/product/12.2.0/db_1/dbs/spfilecndba.ora /u01/app/oracle/product/18.1.0/db_1/dbs/

5) 以UPGRADE方式打开数据库

SQL> startup upgrade;ORACLE instance started.Total System Global Area 1560280224 bytesFixed Size 8658080 bytesVariable Size 1006632960 bytesDatabase Buffers 536870912 bytesRedo Buffers 8118272 bytesDatabase mounted.Database opened.

1) 退出所有SQL*PLUS，执行升级脚本

[oracle@12cr1 ~]$ cd /u01/app/oracle/product/18.1.0/db_1/bin/[oracle@12cr1 bin]$ ./dbupgrade -n 4 -d /u01/app/oracle/product/18.1.0/db_1/rdbms/admin/ -l /tmp/Argument list for [/u01/app/oracle/product/18.1.0/db_1/rdbms/admin/catctl.pl]Run in c = 0Do not run in C = 0Input Directory d = /u01/app/oracle/product/18.1.0/db_1/rdbms/admin/Echo OFF e = 1Simulate E = 0Forced cleanup F = 0Log Id i = 0Child Process I = 0Log Dir l = /tmp/Priority List Name L = 0Upgrade Mode active M = 0SQL Process Count n = 4SQL PDB Process Count N = 0Open Mode Normal o = 0Start Phase p = 0End Phase P = 0Reverse Order r = 0AutoUpgrade Resume R = 0Script s = 0Serial Run S = 0RO User Tablespaces T = 0Display Phases y = 0Debug catcon.pm z = 0Debug catctl.pl Z = 0catctl.pl VERSION: [18.0.0.0.0]STATUS: [Production]BUILD: [RDBMS_18.1CLOUD_LINUX.X64_180131.2]/u01/app/oracle/product/18.1.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.1.0/db_1]/u01/app/oracle/product/18.1.0/db_1/bin/orabasehome = [/u01/app/oracle/product/18.1.0/db_1]catctlGetOrabase = [/u01/app/oracle/product/18.1.0/db_1]Analyzing file /u01/app/oracle/product/18.1.0/db_1/rdbms/admin/catupgrd.sqlLog file directory = [/tmp/]catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/catupgrd_catcon_30640.lst]catcon::set_log_file_base_path: catcon: See [/tmp/catupgrd*.log] files for output generated by scriptscatcon::set_log_file_base_path: catcon: See [/tmp/catupgrd_*.lst] files for spool files, if anyNumber of Cpus = 1Database Name = cndbaDataBase Version = 12.2.0.1.0Parallel SQL Process Count = 4Components in [cndba]Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]Not Installed [APEX EM MGW ODM RAC WK]------------------------------------------------------Phases [0-108] Start Time:[2018_07_12 12:17:41]------------------------------------------------------*********** Executing Change Scripts ***********Serial Phase #:0 [cndba] Files:1略….Serial Phase #:104 [cndba] Files:1 Time: 16s**************** Summary report ****************Serial Phase #:105 [cndba] Files:1 Time: 2s*** End PDB Application Upgrade Post-Shutdown **Serial Phase #:106 [cndba] Files:1 Time: 0sSerial Phase #:107 [cndba] Files:1 Time: 0sSerial Phase #:108 [cndba] Files:1 Time: 24s------------------------------------------------------Phases [0-108] End Time:[2018_07_12 13:09:40]------------------------------------------------------Grand Total Time: 3120sLOG FILES: (/tmp//catupgrd*.log)Upgrade Summary Report Located in:/tmp//upg_summary.logGrand Total Upgrade Time: [0d:0h:52m:0s]

2) 打开数据库，编译PL/SQL和java代码

SQL> startupORACLE instance started.Total System Global Area 1560280224 bytesFixed Size 8658080 bytesVariable Size 1006632960 bytesDatabase Buffers 536870912 bytesRedo Buffers 8118272 bytesDatabase mounted.Database opened.[oracle@12cr1 db_1]$ cd $ORACLE_HOME/rdbms/admin[oracle@12cr1 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sqlcatcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/18.1.0/db_1/rdbms/admin/utlrp_catcon_7595.lst]catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/18.1.0/db_1/rdbms/admin/utlrp*.log] files for output generated by scriptscatcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/18.1.0/db_1/rdbms/admin/utlrp_*.lst] files for spool files, if anycatcon.pl: completed successfully[oracle@12cr1 admin]$

3) 执行修复脚本

对于没有修复成功的问题，需要手动进行处理，处理方法参考前一节。

SQL> @/u01/app/oracle/cfgtoollogs/cndba/preupgrade/postupgrade_fixups.sqlAuto-Generated by: Oracle Preupgrade ScriptVersion: 18.0.0.0.0 Build: 3Generated on: 2018-07-12 10:43:02For Source Database: CNDBASource Database Version: 12.2.0.1.0For Upgrade to Version: 18.0.0.0.0Preup PreupgradeAction Issue IsNumber Preupgrade Check Name Remedied Further DBA Action------ ------------------------ ---------- --------------------------------4. old_time_zones_exist YES None.5. post_dictionary YES None.6. post_fixed_objects NO Informational only.Further action is optional.The fixup scripts have been run and resolved what they can. However,there are still issues originally identified by the preupgrade thathave not been remedied and are still present in the database.Depending on the severity of the specific issue, and the nature ofthe issue itself, that could mean that your database upgrade is notfully complete. To resolve the outstanding issues, start by reviewingthe postupgrade_fixups.sql and searching it for the name ofthe failed CHECK NAME or Preupgrade Action Number listed above.There you will find the original corresponding diagnostic messagefrom the preupgrade which explains in more detail what still needsto be done.PL/SQL procedure successfully completed.Session altered.

4) 查看组件是否正常

执行脚本utlu122s.sql验证所有组件是否工作正常。

SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sqlOracle Database Release 18 Post-Upgrade Status Tool 07-12-2018 14:11:2Component Current Full Elapsed TimeName Status Version HH:MM:SSOracle Server VALID 18.1.0.0.0 00:30:14JServer JAVA Virtual Machine VALID 18.1.0.0.0 00:03:06Oracle XDK VALID 18.1.0.0.0 00:00:46Oracle Database Java Packages VALID 18.1.0.0.0 00:00:22OLAP Analytic Workspace VALID 18.1.0.0.0 00:00:45Oracle Label Security VALID 18.1.0.0.0 00:00:17Oracle Database Vault VALID 18.1.0.0.0 00:00:39Oracle Text VALID 18.1.0.0.0 00:01:04Oracle Workspace Manager VALID 18.1.0.0.0 00:00:54Oracle Real Application Clusters OPTION OFF 18.1.0.0.0 00:00:00Oracle XML Database VALID 18.1.0.0.0 00:01:58Oracle Multimedia VALID 18.1.0.0.0 00:02:19Spatial VALID 18.1.0.0.0 00:06:10Oracle OLAP API VALID 18.1.0.0.0 00:00:27Upgrade Datapatch 00:00:15Final Actions 00:00:31Post Upgrade 00:00:16Post Upgrade Datapatch 00:00:11Post Compile 00:10:29Total Upgrade Time: 01:00:47Database time zone version is 26. It is older than current release timezone version 31. Time zone upgrade is needed using the DBMS_DST package.Summary Report File = /tmp//upg_summary.log

可以看到都正常。

5) 查看无效对象

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;no rows selected

6) 修改COMPATITLE参数

在所有测试完成后且结果都正常，那么就可以将COMPATITLE参数设置为18.0.0。

l 查看COMPATITLE

SQL> show parameter compatibleNAME TYPE VALUE------------------------------------ ----------- ------------------------------compatible string 12.2.0noncdb_compatible boolean FALSE

l 修改COMPATITLE

SQL> alter system set compatible='18.0.0' scope=spfile;System altered.

l 重启之前要删除所有的还原点

SQL> col name for a30SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;SCN GUA NAME------------ --------- ------------------------------1493536 YES GRP_15312104323351 row selected.SQL> drop restore point GRP_1531210432335;Restore point dropped.

l 重启数据库

SQL> shutdown immediateSQL> startup

l 再次查看COMPATITLE

COMPATITLE参数一旦修改了，就无法降级数据库到原来的版本了。

SQL> show parameter compatibleNAME TYPE VALUE------------------------------------ ----------- ------------------------------compatible string 18.0.0noncdb_compatible boolean FALSE