Oracle update to 19c using DBU

升級關鍵點
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.

1. 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';