升级关键点 1.可以从11.2.0.4、12.1.0.2、12.2.0.1和18c直接升级到19c。 2.兼容参数至少应为11.2.0 3.升级后，Oracle默认帐户（在升级之前尚未重置其密码）将被锁定并设置为NO AUTHENICATE MODE。 4.升级后，由于采用了新的身份验证方法，您可能无法使用密码登录现有用户。 若要解决此问题，需要更新sqlnet.ora文件。 软件准备 数据库软件 数据库升级版本： Oracle Database 19.3.0.0 当前环境详细信息： DATABASE TYPE – single DATABASE NAME – TESTDB DATABASE VESION – 12.1.0.2 CURRENT ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 NEW ORACLE_HOME = /oracle/app/oracle/product/19.0.0.0/dbhome_1
**安装DB**
解压安装包创建安装目录 unzip the binary and run runInstaller.sh mkdir -p /oracle/app/oracle/product/19.0.0.0/dbhome_1
安装数据库软件
升级前检查 运行升级前工具脚本 oracle数据库二进制文件提供了preupgrade.jar工具文件。运行此进行预检查
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
$ORACLE_HOME/jdk/bin/java -jar /oracle/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/preupgrade.jar
================== PREUPGRADE SUMMARY
/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql /oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2019-08-26T13:09:51
运行升级前修正脚本：
SQL> @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2019-08-26 13:09:37
For Source Database: TESTDB Source Database Version: 12.1.0.2.0 For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action
1. invalid_objects_exist NO Manual fixup recommended.
2. exclusive_mode_auth NO Manual fixup recommended.
3. case_insensitive_auth NO Manual fixup recommended.
4. underscore_events NO Informational only.
Further action is optional.
5. dictionary_stats YES None.
6. parameter_deprecated NO Informational only.
Further action is optional.
7. min_archive_dest_size NO Informational only.
Further action is optional.
8. rman_recovery_version 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 that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database is not ready for upgrade. To resolve the outstanding issues, start by reviewing the preupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed.
运行utlrp.sql :(编译无效对象）
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
0
检查数据库组件状态 set pagesize500 set linesize 100 select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME
STATUS VERSION
JServer JAVA Virtual Machine VALID 12.1.0.2.0
Oracle Database Catalog Views VALID 12.1.0.2.0
Oracle Database Java Packages VALID 12.1.0.2.0
Oracle Database Packages and Types VALID 12.1.0.2.0
Oracle Multimedia VALID 12.1.0.2.0
Oracle Text VALID 12.1.0.2.0
Oracle Workspace Manager VALID 12.1.0.2.0
Oracle XDK VALID 12.1.0.2.0
Oracle XML Database VALID 12.1.0.2.0
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
检查时区版本： SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
timezlrg_18.dat 18 0
在备份模式下检查文件：（应返回零行） SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
SQL> SELECT * FROM v$recover_file;
no rows selected
清除回收站 SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
SQL> SELECT * FROM v$recover_file;
no rows selected
升级数据库 Enable the flashback on the database.
- To enable restore , in case of failure, enable flashback option.
alter system set db_recovery_file_dest_size=20G scope=both; alter system set db_recovery_file_dest='/dumparea/FRA/' scope=both; alter database flashback on;
export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1/
cd $ORACLE_HOME/bin
./dbua
Upgrade completed successfully.
升级后检查
SQL> select comp_id,status from dba_registry;
COMP_ID STATUS
CATALOG VALID CATPROC VALID JAVAVM VALID XML VALID CATJAVA VALID RAC OPTION OFF XDB VALID OWM VALID CONTEXT VALID ORDIM VALID
10 rows selected.
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
timezlrg_32.dat 32 0
更新sqlnet.ora文件 Post upgrade, you might not be able to connect to the existing users with the passwords. So to fix this add SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 to sqlnet.ora file export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1 cd $ORACLE_HOME/network/admin cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
一旦确认升级成功并且没有回滚，就可以删除还原点。 select * from v$restore_point;
drop restore point
在升级后更新兼容的参数。 升级成功后，请对数据库进行测试。 测试成功后，您可以更新兼容参数。 但是，一旦更新了兼容参数，就无法降级数据库。 因此，在更新兼容参数之前，请务必进行适当的测试并进行完整备份。
alter system set compatible='19.0.0' scope=spfile; shutdown immediate; startup
SELECT name, value FROM v$parameter WHERE name = 'compatible';
