1.组件信息
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
set line 200
col COMP_NAME for a40
select comp_name,VERSION,STATUS from dba_registry;
exit;
EOF
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ----------------------
OWB 11.2.0.4.0 VALID
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 VALID
Oracle OLAP API 11.2.0.4.0 VALID
18 rows selected.
废弃的组件：EM
从18c开始,Oracle不会自动更新APEX组件，需要手动更新。
OLAP删除
2.查看刷新的物化视图
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
exit;
EOF
升级前确保无物化视图正在刷新
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
3.删除EM组件
source /home/oracle/.bash_profile11g && emctl stop dbconsole &&sqlplus '/ as sysdba'<<EOF
SET ECHO ON;
SET SERVEROUTPUT ON;
@/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/emremove.sql
exit;
EOF
$ cd $ORACLE_HOME
$ rm -rf HOSTNAME_SID
$ rm -rf oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID
结果：
[oracle@19crac1 ~]$ source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF
> @/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/emremove.sql
> exit;
> EOF
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 9 17:31:40 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
4.删除OLAP组件
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
@?/olap/admin/catnoamd.sql;
exit;
EOF
5.手动升级APEX组件
Ø 下载补丁包：http://www.oracle.com/technetwork/developer-tools/apex/all-archives-099381.html
[oracle@19crac1 ~]$ source .bash_profile11g
[oracle@19crac1 ~]$ cd apex
[oracle@19crac1 apex]$ ls -ltr
[oracle@19crac1 apex]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 9 20:16:40 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
...set_appun.sql
PL/SQL procedure successfully completed.
检查APEX版本
source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF
COL COMP_NAME FORMAT A40
COL STATUS FORMAT A12
Set lines 200
SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY WHERE COMP_NAME='Oracle Application Express';
exit;
EOF
结果
COMP_NAME STATUS VERSION
---------------------------------------- ------------ ------------------------------
Oracle Application Express VALID 3.2.1.00.12
Ø 一定要注意归档空间的使用率，有可能归档满了，升级卡住
Ø 详细日志
6.确认兼容性参数
source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF
show parameter compatible;
exit;
EOF
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
注意升级 19c 的 COMPATIBLE 参数的最小值为“11.2.0”, 确保您的 COMPATIBLE 参数设置为11.2.0或更高
7.配置FRA
source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF
show parameter db_recovery_file_dest;
exit;
EOF
8.清理回收站
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
purge dba_recyclebin;
exit;
EOF
9.创建闪回点
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
create restore point syncdb_20200924_19c guarantee flashback database;
select name,guarantee_flashback_database,to_char(scn) from v\$restore_point;
exit;
EOF
结果：
SQL>
Restore point created.
SQL>
NAME
--------------------------------------------------------------------------------
GUA TO_CHAR(SCN)
--- ----------------------------------------
SYNCDB_20200924_19C
YES 994270
10.收集数据字典统计信息
source /home/oracle/.bash_profile11g && sqlplus '/ as sysdba'<<EOF
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
exit;
EOF
11.备份数据库
$ . .bash_profile11g
$ rman target /
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '/tmp/orcl_2_%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT '/tmp/controlfile';
}
12.升级前预检查
拷贝jar到/tmp
19c环境
[root@19crac1 ~]# su - oracle
[oracle@19crac1 ~]$ cp /u01/app/oracle/product/19c/dbhome_1/rdbms/admin/preupgrade.jar /tmp
11g环境下执行
source /home/oracle/.bash_profile11g && mkdir -p /home/oracle/nxtest/log && $ORACLE_HOME/jdk/bin/java -jar /tmp/preupgrade.jar FILE TEXT DIR /home/oracle/nxtest/log
结果：
==================
PREUPGRADE SUMMARY
==================
/home/oracle/nxtest/log/preupgrade.log
/home/oracle/nxtest/log/preupgrade_fixups.sql
/home/oracle/nxtest/log/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/home/oracle/nxtest/log/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/home/oracle/nxtest/log/postupgrade_fixups.sql
Preupgrade complete: 2020-09-24T11:07:15
13.执行升级前修复脚本
[oracle@19crac1 ~]$ source .bash_profile11g
[oracle@19crac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 9 20:56:30 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @/home/oracle/nxtest/log/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: 2020-09-24 11:07:11
For Source Database: ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. min_recovery_area_size NO Manual fixup required.
2. parameter_min_val NO Manual fixup recommended.
3. em_present NO Manual fixup recommended.
4. invalid_objects_exist NO Manual fixup recommended.
5. apex_manual_upgrade NO Manual fixup recommended.
6. trgowner_no_admndbtrg YES None.
7. tablespaces_info NO Informational only.
Further action is optional.
8. exf_rul_exists NO Informational only.
Further action is optional.
9. 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.
14.修改废弃参数（此步骤在前面可以避免）
删除19c废弃或者不兼容的参数
source /home/oracle/.bash_profile11g && sqlplus "/ as sysdba"<<EOF
alter system set sec_case_sensitive_logon=true;
exit;
EOF
15.修改oratab文件
echo "orcl:/u02/app/oracle/product/11.2.0/dbhome_1:N">>/etc/oratab
deep:/u01/app/oracle/product/19.3.0/db_1:N
prod:/u01/app/oracle/product/12.2.0/db_1:N
orcl:/u01/app/oracle/product/11.2.0/db_1:N
16.拷贝spfile至19c的home dbs下，并重启库至upgrade
[oracle@19crac1 nxtest]$ cp /u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl1.ora /u01/app/oracle/product/19c/dbhome_1/dbs/
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1002437904 bytes
Fixed Size 8903952 bytes
Variable Size 276824064 bytes
Database Buffers 708837376 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
17.执行升级脚本（19c目录下）
[oracle@19crac1 nxtest]$ cd $ORACLE_HOME
[oracle@19crac1 dbhome_1]$ cd bin/
[oracle@19crac1 bin]$ ./dbupgrade -n 4 -l /home/oracle/nxtest
结果
Argument list for [/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/nxtest –日志文件存放位置
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4 --和CPU_COUNT参数一样即可
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.4.0.0.0DBRU_LINUX.X64_190626]
/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19c/dbhome_1]
/u01/app/oracle/product/19c/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19c/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/dbhome_1]
Analyzing file /u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/nxtest]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/nxtest/catupgrd_catcon_73530.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/nxtest/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/nxtest/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = orcldg
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [orcldg]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2019_12_11 09:00:06]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcldg] Files:1 Time: 81s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcldg] Files:5 Time: 34s
Restart Phase #:2 [orcldg] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcldg] Files:19 Time: 14s
Restart Phase #:4 [orcldg] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcldg] Files:7 Time: 11s
***************** Catproc Start ****************
Serial Phase #:6 [orcldg] Files:1 Time: 11s
***************** Catproc Types ****************
Serial Phase #:7 [orcldg] Files:2 Time: 8s
Restart Phase #:8 [orcldg] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcldg] Files:67 Time: 23s
Restart Phase #:10 [orcldg] Files:1 Time: 3s
************* Catproc Package Specs ************
Serial Phase #:11 [orcldg] Files:1 Time: 42s
Restart Phase #:12 [orcldg] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [orcldg] Files:94 Time: 6s
Restart Phase #:14 [orcldg] Files:1 Time: 1s
Parallel Phase #:15 [orcldg] Files:121 Time: 9s
Restart Phase #:16 [orcldg] Files:1 Time: 1s
Serial Phase #:17 [orcldg] Files:22 Time: 3s
Restart Phase #:18 [orcldg] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [orcldg] Files:32 Time: 13s
Restart Phase #:20 [orcldg] Files:1 Time: 3s
。。。。。。
*************** Catproc CDB Views **************
Serial Phase #:31 [orcldg] Files:1 Time: 2s
Restart Phase #:32 [orcldg] Files:1 Time: 2s
Serial Phase #:34 [orcldg] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcldg] Files:294 Time: 16s
Serial Phase #:36 [orcldg] Files:1 Time: 0s
Restart Phase #:37 [orcldg] Files:1 Time: 1s
Serial Phase #:38 [orcldg] Files:6 Time: 5s
Restart Phase #:39 [orcldg] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcldg] Files:3 Time: 35s
Restart Phase #:41 [orcldg] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcldg] Files:13 Time: 84s
Restart Phase #:43 [orcldg] Files:1 Time: 2s
Parallel Phase #:44 [orcldg] Files:11 Time: 9s
Restart Phase #:45 [orcldg] Files:1 Time: 3s
Parallel Phase #:46 [orcldg] Files:3 Time: 3s
Restart Phase #:47 [orcldg] Files:1 Time: 2s
************* Final Catproc scripts ************
Serial Phase #:48 [orcldg] Files:1 Time: 7s
Restart Phase #:49 [orcldg] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcldg] Files:1 Time: 12s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcldg] Files:1 Time: 2s
Restart Phase #:52 [orcldg] Files:1 Time: 2s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcldg] Files:2 Time: 283s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcldg] Files:1 Time: 2s
Serial Phase #:56 [orcldg] Files:3 Time: 19s
Serial Phase #:57 [orcldg] Files:3 Time: 5s
。。。。。。
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcldg] Files:1 Time: 2s
Serial Phase #:69 [orcldg] Files:1 Time: 5s
Parallel Phase #:70 [orcldg] Files:2 Time: 29s
。。。。。
***************** Upgrading SDO ****************
Restart Phase #:74 [orcldg] Files:1 Time: 1s
Serial Phase #:76 [orcldg] Files:1 Time: 30s
Serial Phase #:77 [orcldg] Files:2 Time: 5s
。。。。。。
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcldg] Files:1 Time: 11s
Restart Phase #:96 [orcldg] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [orcldg] Files:1 Time: 3s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcldg] Files:1 Time: 274s
******************* Migration ******************
Serial Phase #:99 [orcldg] Files:1 Time: 34s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcldg] Files:1 Time: 2s
Serial Phase #:101 [orcldg] Files:1 Time: 0s
Serial Phase #:102 [orcldg] Files:1 Time: 60s
***************** Post Upgrade *****************
Serial Phase #:103 [orcldg] Files:1 Time: 22s
**************** Summary report ****************
Serial Phase #:104 [orcldg] Files:1 Time: 3s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcldg] Files:1 Time: 2s
Serial Phase #:106 [orcldg] Files:1 Time: 0s
Serial Phase #:107 [orcldg] Files:1 Time: 40s
------------------------------------------------------
Phases [0-107] End Time:[2019_12_11 09:30:19]
------------------------------------------------------
Grand Total Time: 1814s
LOG FILES: (/home/oracle/nxtest/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/nxtest/upg_summary.log
Grand Total Upgrade Time: [0d:0h:30m:14s]
18.启动数据库
SQL> startup ORACLE instance started.
Total System Global Area 1002437904 bytes
Fixed Size 8903952 bytes
Variable Size 276824064 bytes
Database Buffers 708837376 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
19.执行升级后修复脚本
SQL> @/home/oracle/nxtest/log/postupgrade_fixups.sql
结果：
[oracle@19crac1 log]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 11 09:46:37 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
SQL> @/home/oracle/nxtest/log/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-12-10 16:07:18
For Source Database: ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
19. old_time_zones_exist NO Manual fixup recommended.
20. post_dictionary YES None.
21. post_fixed_objects NO Informational only.
Further action is optional.
22. upg_by_std_upgrd YES None.
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 upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_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.
Session altered.
20.数据库状态查询
Col Comp_name Format a22
Col Status Format a12
Select Comp_name, status, Version
From Dba_Registry
Order by Comp_name;
set line 200
col COMP_ID format a10
col COMP_NAME format a35
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)
comp_name,substr(version,1,10) version,status
from dba_registry order by modified;
编译无效对象：
@?/rdbms/admin/utlrp.sql
注意: 之前版本的 utluNNNs.sql 在 19c 上被替换为 utlusts.sql
SQL> @?/rdbms/admin/utlusts.sql
结果：
（1）注意这里的提示：大部分的组件状态都为upgrade，重新编译即可。
（2）标数据库的 time zone 文件版本比较老了，需要手动升级
21.升级time zine文件版本
echo "orcl:/u02/app/oracle/product/11.2.0/dbhome_1:N">>/etc/oratab
19c此升级脚本数据库自带
@?/rdbms/admin/utltz_upg_check.sql ---检查当前版本
@?/rdbms/admin/utltz_upg_apply.sql --更新应用
结果：
SQL> @?/rdbms/admin/utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL> @?/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1002437904 bytes
Fixed Size 8903952 bytes
Variable Size 612368384 bytes
Database Buffers 373293056 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1002437904 bytes
Fixed Size 8903952 bytes
Variable Size 612368384 bytes
Database Buffers 373293056 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ACTIVITY_LOG1$"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ACTIVITY_LOG2$"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_ISSUE_NOTIFICATIONS"
Number of failures: 0
Table list: "APEX_190200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_190200"."WWV_QS_RANDOM_NAMES"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
22.编译失效对象
执行 utlrp.sql (多次) 来使它们生效，直到失效对象的个数不再改变。
@?/rdbms/admin/utlrp.sql
23.再次查询数据库状态
SQL> @?/rdbms/admin/utlusts.sql
Oracle Database Release 19 Post-Upgrade Status Tool 12-11-2019 10:34:0
Database Name: ORCL
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.4.0.0.0 00:11:33
JServer JAVA Virtual Machine VALID 19.4.0.0.0 00:02:42
Oracle XDK VALID 19.4.0.0.0 00:00:26
Oracle Database Java Packages VALID 19.4.0.0.0 00:00:10
OLAP Analytic Workspace VALID 19.4.0.0.0 00:00:08
Oracle Text VALID 19.4.0.0.0 00:00:26
Oracle Workspace Manager VALID 19.4.0.0.0 00:00:45
Oracle Real Application Clusters VALID 19.4.0.0.0 00:00:00
Oracle XML Database VALID 19.4.0.0.0 00:02:10
Oracle Multimedia VALID 19.4.0.0.0 00:00:39
Spatial VALID 19.4.0.0.0 00:03:05
Oracle OLAP API VALID 19.4.0.0.0 00:00:08
Datapatch 00:04:24
Final Actions 00:05:07
Post Upgrade 00:00:19
Post Compile 00:00:02
Total Upgrade Time: 00:28:23
Database time zone version is 32. It meets current release needs.
务必保证各个组件状态都为VALID
24.更新oratab文件
vi /etc/oratab
orcl:/u01/app/oracle/product/19c/dbhome_1:N
25.检查失效对象
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
--------------- ----------------------- ----------
APEX_190200 PACKAGE BODY 3
EXFSYS FUNCTION 8
EXFSYS PACKAGE BODY 5
EXFSYS PROCEDURE 6
EXFSYS TYPE 1
EXFSYS TYPE BODY 2
26.删除闪回点
drop restore point syncdb_20191209_19c;
27.单库配置为RAC
SQL> alter system set cluster_database=true scope=spfile;
SQL> create pfile='/tmp/orcl.ora' from spfile;
echo "orcl2.instance_number=2
orcl1.instance_number=1" >>/tmp/orcl.ora
SQL> create spfile='+datadg/orcldg/spfile.ora' from pfile='/tmp/orcl.ora';
SQL> shutdown immediate
[oracle@19crac1 log]$ srvctl add database -db orcl -dbname orcl -oraclehome /u01/app/oracle/product/19c/dbhome_1 -dbtype RAC -spfile '+datadg/orcldg/spfile.ora' -startoption open -stopoption immediate -diskgroup DATADG,ARCHDG
[oracle@19crac1 log]$ srvctl add instance -db orcl -instance orcl1 -n 19crac1
[oracle@19crac1 log]$ srvctl add instance -db orcl -instance orcl2 -n 19crac2
[oracle@19crac1 log]$ cd $ORACLE_HOME
[oracle@19crac1 dbhome_1]$ cd dbs
[oracle@19crac1 dbs]$ mv spfileorcl1.ora spfileorcl1.ora.bak
[oracle@19crac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 11 11:04:16 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+datadg/orcldg/spfile.ora' from pfile='/tmp/orcl.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 1002437904 bytes
Fixed Size 8903952 bytes
Variable Size 612368384 bytes
Database Buffers 373293056 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
修改service_name
SQL>alter system set service_name='orcl';--与源库保持一致
SQL> exit
节点二创建审计目录
[oracle@19crac2 dbs]cd /u01/app/oracle/admin/
[oracle@19crac2 dbs]mkdir -p orcldg/adump
[oracle@19crac2 dbs]SQL>startup
2个节点全部关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
用srvctl命令启动
[grid@19crac1 ~]$ srvctl start database -d orcl
资源状态：
[grid@19crac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE 19crac1 STABLE
ONLINE ONLINE 19crac2 STABLE
ora.chad
ONLINE ONLINE 19crac1 STABLE
ONLINE ONLINE 19crac2 STABLE
ora.net1.network
ONLINE ONLINE 19crac1 STABLE
ONLINE ONLINE 19crac2 STABLE
ora.ons
ONLINE ONLINE 19crac1 STABLE
ONLINE ONLINE 19crac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE 19crac1 STABLE
OFFLINE OFFLINE 19crac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.19crac1.vip
1 ONLINE ONLINE 19crac1 STABLE
ora.19crac2.vip
1 ONLINE ONLINE 19crac2 STABLE
ora.ARCHDG.dg(ora.asmgroup)
1 ONLINE ONLINE 19crac1 STABLE
2 ONLINE ONLINE 19crac2 STABLE
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE 19crac1 STABLE
2 ONLINE ONLINE 19crac2 STABLE
ora.DATADG.dg(ora.asmgroup)
1 ONLINE ONLINE 19crac1 STABLE
2 ONLINE ONLINE 19crac2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE 19crac1 STABLE
ora.OCRDG.dg(ora.asmgroup)
1 ONLINE ONLINE 19crac1 STABLE
2 ONLINE ONLINE 19crac2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE 19crac1 Started,STABLE
2 ONLINE ONLINE 19crac2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE 19crac1 STABLE
2 ONLINE ONLINE 19crac2 STABLE
ora.cvu
1 ONLINE ONLINE 19crac1 STABLE
ora.orcl.db
1 ONLINE ONLINE 19crac1 Open,HOME=/u01/app/o
racle/product/19c/db
home_1,STABLE
2 ONLINE ONLINE 19crac2 Open,HOME=/u01/app/o
racle/product/19c/db
home_1,STABLE
ora.qosmserver
1 ONLINE ONLINE 19crac1 STABLE
ora.scan1.vip
1 ONLINE ONLINE 19crac1 STABLE
--------------------------------------------------------------------------------
28.调整密码文件位置
echo "orcl:/u02/app/oracle/product/11.2.0/dbhome_1:N">>/etc/oratab
[grid@19crac1 ~]$ asmcmd
ASMCMD> cd datadg
ASMCMD> pwcopy --dbuniquename orcl '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' '+datadg/orcldg/orapworcl'
copying /u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1 -> +datadg/orcldg/orapworcl
ASMCMD-9453: failed to register password file as a CRS resource
29.手动注册crs资源（root用户）
[root@19crac1 ~]# cd /u01/app/19c/grid/bin/
[root@19crac1 bin]# ./srvctl modify database -d orcl -pwfile '+datadg/orcldg/orapworcl'
[root@19crac1 bin]# su - grid
Last login: Wed Dec 11 16:15:14 CST 2019
[grid@19crac1 ~]$ srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19c/dbhome_1
Oracle user: oracle
Spfile: +datadg/orcldg/spfile.ora
Password file: +datadg/orcldg/orapworcl
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATADG,ARCHDG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: orcl1,orcl2
Configured nodes: 19crac1,19crac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[grid@19crac1 ~]$
30.删除11204 db soft
[root@19crac1 bin]# rm -rf /u02
报错处理：https://www.cnblogs.com/djiankuo/p/6653180.html
[oracle@deep bin]$ ./dbupgrade -n 4 -l /home/oracle/nxtest
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = "",
LANG = "en_US.utf-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Argument list for [/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/nxtest
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
扫一扫
1953
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
