{"id":888,"date":"2018-11-06T11:47:19","date_gmt":"2018-11-06T03:47:19","guid":{"rendered":"http:\/\/www.sulabs.net\/?p=888"},"modified":"2018-11-06T11:47:19","modified_gmt":"2018-11-06T03:47:19","slug":"oracle-11-2-0-4%e5%8d%87%e7%ba%a712-2-0-1%e8%af%a6%e7%bb%86%e6%ad%a5%e9%aa%a4","status":"publish","type":"post","link":"https:\/\/www.sulabs.net\/?p=888","title":{"rendered":"Oracle 11.2.0.4\u5347\u7ea712.2.0.1\u8be6\u7ec6\u6b65\u9aa4"},"content":{"rendered":"

\u672c\u6587\u6d89\u8f6c\u8f7d\u81ea https:\/\/blog.csdn.net\/u010692693\/article\/details\/73826193
\n\u6d4b\u8bd5\u5347\u7ea7\u6b65\u9aa4\u6b63\u786e\u65e0\u8bef\uff0c\u6ce8\u610f\u672c\u6587\u4e2d\u6d89\u53ca\u52302\u4e2a\u811a\u672c\uff0c\u4ec5\u4ed8\u8d39\u7528\u6237\u53ef\u4ee5\u83b7\u53d6.<\/strong>
\n1. dbupgdiag.zip \u4e3a\u5347\u7ea7\u8bca\u65ad\u811a\u672c\uff0c\u68c0\u67e5\u6570\u636e\u5e93\u662f\u5426\u7b26\u5408\u5347\u7ea7\u6807\u51c6.
\n2. DBMS_DST_scriptsV1.9.zip \u4e3a\u65f6\u533a\u5347\u7ea7\u811a\u672c\uff0c\u7528\u4e8e\u6570\u636e\u5e93\u7684\u65f6\u533a\u5347\u7ea7.<\/p>\n

Complete checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1.x.x to 12.2..x.x (\u6587\u6863 ID 2173144.1)
\nComplete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (\u6587\u6863 ID 2173141.1)
\nUpdating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (\u6587\u6863 ID 1509653.1)
\n<\/p>\n

Upgrading Timezone file on Oracle Releases post 12.2.0.1
\nFrom 12.2.0.2 onwards, timezone upgrade scripts are included in the target ORACLE_HOME under rdbms\/admin directory. Customer can use those files to perform time zone upgrade instead of the ones included in this document. Please refer to Oracle Documentation on how to upgrade a time zone file manually:
\nDatabase Globalization Support Guide
\nSection 4.7.3 Steps to Upgrade Time Zone File and Timestamp with Time Zone Data<\/p>\n

http:\/\/docs.oracle.com\/database\/122\/NLSPG\/datetime-data-types-and-time-zone-support.htm#NLSPG261<\/p>\n

1. \u67e5\u770b\u8865\u4e01\u60c5\u51b5<\/p>\n

[oracle@oracle OPatch]$ .\/opatch lsinventory<\/span>
\nOracle Interim Patch Installer version 11.2.0.3.4
\nCopyright (c) 2012, Oracle Corporation. All rights reserved.<\/p>\n

Oracle Home : \/u01\/app\/oracle\/product\/11.2.0\/db_1
\nCentral Inventory : \/u01\/app\/oraInventory
\nfrom : \/u01\/app\/oracle\/product\/11.2.0\/db_1\/oraInst.loc
\nOPatch version : 11.2.0.3.4
\nOUI version : 11.2.0.4.0
\nLog file location : \/u01\/app\/oracle\/product\/11.2.0\/db_1\/cfgtoollogs\/opatch\/opatch2017-06-16_20-42-36PM_1.log<\/p>\n

Lsinventory Output file location : \/u01\/app\/oracle\/product\/11.2.0\/db_1\/cfgtoollogs\/opatch\/lsinv\/lsinventory2017-06-16_20-42-36PM.txt<\/p>\n

-------------------------------------------------------------------------------
\nInstalled Top-level Products (1):<\/p>\n

Oracle Database 11g 11.2.0.4.0
\nThere are 1 products installed in this Oracle Home.<\/p>\n

There are no Interim patches installed in this Oracle Home.<\/p>\n

-------------------------------------------------------------------------------<\/p>\n

OPatch succeeded.<\/p>\n

2. \u6267\u884c\u9884\u68c0\u67e5\u811a\u672c
\nexport ORACLE_HOME=$ORACLE_BASE\/product\/11.2.0\/db_1<\/span>
\nexport ORACLE_SID=orcl11g<\/span><\/p>\n

[oracle@oracle ~]$ cd \/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/<\/span>
\n[oracle@oracle admin]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 16 21:30:55 2017
\nCopyright (c) 1982, 2013, Oracle. All rights reserved.
\nConnected to:
\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options<\/p>\n

SQL> @dbupgdiag.sql<\/span><\/p>\n

Enter location for Spooled output:
\nEnter value for 1: \/tmp<\/span>
\nJAVAVM TESTING
\n---------------
\nfoo
\n===================================
\nOracle Multimedia\/InterMedia status
\n===================================
\n.
\nOracle Multimedia\/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID
\n.
\nChecking for installed Database Schemas...
\nORDSYS user exists.
\nORDPLUGINS user exists.
\nMDSYS user exists.
\nSI_INFORMTN_SCHEMA user exists.
\nORDDATA user exists.
\n.
\nChecking for Prerequisite Components...
\nJAVAVM installed and listed as valid
\nXDK installed and listed as valid
\nXDB installed and listed as valid
\nValidating Oracle Multimedia\/interMedia...(no output if component status is valid)
\nPL\/SQL procedure successfully completed.
\n*** End of LogFile ***<\/p>\n

3. \u68c0\u67e5\u7269\u5316\u89c6\u56fe\u5237\u65b0\u662f\u5426\u5b8c\u6210
\nSQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s<\/span>
\n2 WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;<\/span><\/p>\n

no rows selected<\/p>\n

4. \u68c0\u67e5\u6570\u636e\u6587\u4ef6\u7684\u72b6\u6001
\nSQL> col name format a50<\/span>
\nSQL> select name,status from v$datafile;<\/span><\/p>\n

NAME STATUS
\n-------------------------------------------------- -------
\n\/u01\/app\/oracle\/oradata\/orcl11g\/system01.dbf SYSTEM
\n\/u01\/app\/oracle\/oradata\/orcl11g\/sysaux01.dbf ONLINE
\n\/u01\/app\/oracle\/oradata\/orcl11g\/undotbs01.dbf ONLINE
\n\/u01\/app\/oracle\/oradata\/orcl11g\/users01.dbf ONLINE<\/p>\n

4 rows selected.<\/p>\n

5. \u68c0\u67e5\u662f\u5426\u5b58\u5728\u5907\u4efd
\nSQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';<\/span><\/p>\n

no rows selected<\/p>\n

6. \u68c0\u67e5\u5206\u5e03\u5f0f\u4e8b\u52a1
\nSQL> SELECT * FROM dba_2pc_pending;<\/span><\/p>\n

no rows selected<\/p>\n

\u5982\u9700\u5904\u7406\uff1a<\/p>\n

SQL> SELECT local_tran_id FROM dba_2pc_pending;<\/span>
\nSQL> EXECUTE dbms_transaction.purge_lost_db_entry('');<\/span>
\nSQL> COMMIT;<\/span><\/p>\n

7. \u6e05\u7a7a\u56de\u6536\u7ad9
\nSQL> show recyclebin<\/span>
\nSQL> PURGE DBA_RECYCLEBIN;<\/span><\/p>\n

DBA Recyclebin purged.<\/p>\n

8. \u6536\u96c6\u6570\u636e\u5b57\u5178\u7684\u7edf\u8ba1\u4fe1\u606f
\nSQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;<\/span><\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

9. \u62f7\u8d1dpfile\u523012c\u76ee\u5f55
\nSQL> create pfile from spfile;<\/span><\/p>\n

File created.<\/p>\n

[oracle@oracle admin]$ cd \/u01\/app\/oracle\/product\/11.2.0\/db_1\/dbs\/<\/span>
\n[oracle@oracle dbs]$ ll<\/span>
\ntotal 24
\n-rw-rw----. 1 oracle oinstall 1544 Jun 16 20:31 hc_orcl11g.dat
\n-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
\n-rw-r--r--. 1 oracle oinstall 937 Jun 16 21:49 initorcl11g.ora
\n-rw-r-----. 1 oracle oinstall 24 Jun 16 20:29 lkORCL11G
\n-rw-r-----. 1 oracle oinstall 1536 Jun 16 20:30 orapworcl11g
\n-rw-r-----. 1 oracle oinstall 2560 Jun 16 21:32 spfileorcl11g.ora
\n[oracle@oracle dbs]$ cp initorcl11g.ora \/u01\/app\/oracle\/product\/12.2.0\/db_1\/dbs<\/span><\/p>\n

10. \u505c\u6b62\u539f11g\u6570\u636e\u5e93
\n[oracle@oracle dbs]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 16 21:52:29 2017
\nCopyright (c) 1982, 2013, Oracle. All rights reserved.
\nConnected to:
\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options<\/p>\n

SQL> shutdown immediate<\/span>
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.<\/p>\n

11. \u542f\u52a812c\u6570\u636e\u5e93
\n[oracle@oracle ~]$ export ORACLE_HOME=$ORACLE_BASE\/product\/12.2.0\/db_1<\/span><\/p>\n

[oracle@oracle dbs]$ cat initorcl11g.ora<\/span>
\n*.audit_file_dest='\/u01\/app\/oracle\/admin\/orcl11g\/adump'
\n*.audit_trail='db'
\n*.compatible='11.2.0.4.0'
\n*.control_files='\/u01\/app\/oracle\/oradata\/orcl11g\/control01.ctl','\/u01\/app\/oracle\/oradata\/orcl11g\/control02.ctl'
\n*.db_block_size=8192
\n*.db_domain=''
\n*.db_name='orcl11g'
\n*.diagnostic_dest='\/u01\/app\/oracle'
\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)'
\n*.log_archive_dest_1='LOCATION=\/arch'
\n*.log_archive_format='%t_%s_%r.dbf'
\n*.open_cursors=300
\n*.pga_aggregate_target=209715200
\n*.processes=150
\n*.remote_login_passwordfile='EXCLUSIVE'
\n*.sga_target=629145600
\n*.undo_tablespace='UNDOTBS1'<\/p>\n

[oracle@oracle dbs]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 16 21:58:13 2017
\nCopyright (c) 1982, 2016, Oracle. All rights reserved.
\nConnected to an idle instance.
\nSQL> create spfile from pfile;
\nFile created.<\/p>\n

SQL> startup upgrade<\/span>
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.<\/p>\n

12. \u8fd0\u884c\u5347\u7ea7\u811a\u672c
\n[oracle@oracle dbs]$ cd ..\/rdbms\/admin\/<\/span>
\n[oracle@oracle admin]$ ll catctl.pl<\/span>
\n-rw-r--r--. 1 oracle oinstall 333980 Dec 19 19:12 catctl.pl
\n[oracle@oracle admin]$ ll catupgrd.sql<\/span>
\n-rw-r--r--. 1 oracle oinstall 7357 Dec 21 2015 catupgrd.sql<\/p>\n

\u9ed8\u8ba4\u5e76\u884c\u5ea6\u4e3a4
\n$ORACLE_HOME\/perl\/bin\/perl catctl.pl catupgrd.sql<\/span><\/p>\n

\u6216\u8005\u4f7f\u7528\u4ee5\u524d\u7684\u65b9\u5f0f\uff0c\u4e0d\u5f00\u5e76\u884c
\nSQL> @catupgrd.sql PARALLEL=NO<\/span><\/p>\n

\u6267\u884c\u5347\u7ea7\uff0c\u65f6\u95f4\u53ef\u80fd\u9700\u8981\u6570\u5341\u5206\u949f\u81f3\u51e0\u5c0f\u65f6\u4e0d\u7b49
\n[oracle@oracle admin]$ $ORACLE_HOME\/perl\/bin\/perl catctl.pl catupgrd.sql<\/span><\/p>\n

Argument list for [catctl.pl]
\nRun in c = 0
\nDo not run in C = 0
\nInput Directory d = 0
\nEcho OFF e = 1
\nSimulate E = 0
\nForced cleanup F = 0
\nLog Id i = 0
\nChild Process I = 0
\nLog Dir l = 0
\nPriority List Name L = 0
\nUpgrade Mode active M = 0
\nSQL Process Count n = 0
\nSQL PDB Process Count N = 0
\nOpen Mode Normal o = 0
\nStart Phase p = 0
\nEnd Phase P = 0
\nReverse Order r = 0
\nAutoUpgrade Resume R = 0
\nScript s = 0
\nSerial Run S = 0
\nRO User Tablespaces T = 0
\nDisplay Phases y = 0
\nDebug catcon.pm z = 0
\nDebug catctl.pl Z = 0<\/p>\n

catctl.pl VERSION: [12.2.0.1.0]
\nSTATUS: [production]
\nBUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]<\/p>\n

\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/orahome = [\/u01\/app\/oracle\/product\/12.2.0\/db_1]
\n\/u01\/app\/oracle\/product\/12.2.0\/db_1\/bin\/orabasehome = [\/u01\/app\/oracle\/product\/12.2.0\/db_1]
\ncatctlGetOrabase = [\/u01\/app\/oracle\/product\/12.2.0\/db_1]<\/p>\n

Analyzing file \/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/catupgrd.sql<\/p>\n

Log file directory = [\/tmp\/cfgtoollogs\/upgrade20170616220804]<\/p>\n

catcon: ALL catcon-related output will be written to [\/tmp\/cfgtoollogs\/upgrade20170616220804\/catupgrd_catcon_11069.lst]
\ncatcon: See [\/tmp\/cfgtoollogs\/upgrade20170616220804\/catupgrd*.log] files for output generated by scripts
\ncatcon: See [\/tmp\/cfgtoollogs\/upgrade20170616220804\/catupgrd_*.lst] files for spool files, if any<\/p>\n

Number of Cpus = 1
\nDatabase Name = orcl11g
\nDataBase Version = 11.2.0.4.0
\ncatcon: ALL catcon-related output will be written to [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/catupgrd_catcon_11069.lst]
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/catupgrd*.log] files for output generated by scripts
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/catupgrd_*.lst] files for spool files, if any<\/p>\n

Log file directory = [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805]<\/p>\n

Parallel SQL Process Count = 4
\nComponents in [orcl11g]
\nInstalled [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
\nNot Installed [DV MGW ODM OLS RAC WK]<\/p>\n

------------------------------------------------------
\nPhases [0-115] Start Time:[2017_06_16 22:08:05]
\n------------------------------------------------------
\n*********** Executing Change Scripts ***********
\nSerial Phase #:0 [orcl11g] Files:1 Time: 86s
\n*************** Catalog Core SQL ***************
\nSerial Phase #:1 [orcl11g] Files:5 Time: 44s
\nRestart Phase #:2 [orcl11g] Files:1 Time: 1s
\n*********** Catalog Tables and Views ***********
\nParallel Phase #:3 [orcl11g] Files:19 Time: 17s
\nRestart Phase #:4 [orcl11g] Files:1 Time: 0s
\n************* Catalog Final Scripts ************
\nSerial Phase #:5 [orcl11g] Files:6 Time: 15s
\n***************** Catproc Start ****************
\nSerial Phase #:6 [orcl11g] Files:1 Time: 11s
\n***************** Catproc Types ****************
\nSerial Phase #:7 [orcl11g] Files:2 Time: 11s
\nRestart Phase #:8 [orcl11g] Files:1 Time: 0s
\n**************** Catproc Tables ****************
\nParallel Phase #:9 [orcl11g] Files:69 Time: 43s
\nRestart Phase #:10 [orcl11g] Files:1 Time: 1s
\n************* Catproc Package Specs ************
\nSerial Phase #:11 [orcl11g] Files:1 Time: 34s
\nRestart Phase #:12 [orcl11g] Files:1 Time: 1s
\n************** Catproc Procedures **************
\nParallel Phase #:13 [orcl11g] Files:97 Time: 16s
\nRestart Phase #:14 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:15 [orcl11g] Files:118 Time: 18s
\nRestart Phase #:16 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:17 [orcl11g] Files:13 Time: 2s
\nRestart Phase #:18 [orcl11g] Files:1 Time: 0s
\n***************** Catproc Views ****************
\nParallel Phase #:19 [orcl11g] Files:33 Time: 33s
\nRestart Phase #:20 [orcl11g] Files:1 Time: 1s
\nSerial Phase #:21 [orcl11g] Files:3 Time: 5s
\nRestart Phase #:22 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:23 [orcl11g] Files:24 Time: 116s
\nRestart Phase #:24 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:25 [orcl11g] Files:11 Time: 38s
\nRestart Phase #:26 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:27 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:28 [orcl11g] Files:3 Time: 2s
\nSerial Phase #:29 [orcl11g] Files:1 Time: 0s
\nRestart Phase #:30 [orcl11g] Files:1 Time: 1s
\n*************** Catproc CDB Views **************
\nSerial Phase #:31 [orcl11g] Files:1 Time: 0s
\nRestart Phase #:32 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:34 [orcl11g] Files:1 Time: 0s
\n***************** Catproc PLBs *****************
\nSerial Phase #:35 [orcl11g] Files:283 Time: 15s
\nSerial Phase #:36 [orcl11g] Files:1 Time: 0s
\nRestart Phase #:37 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:38 [orcl11g] Files:1 Time: 3s
\nRestart Phase #:39 [orcl11g] Files:1 Time: 0s
\n*************** Catproc DataPump ***************
\nSerial Phase #:40 [orcl11g] Files:3 Time: 39s
\nRestart Phase #:41 [orcl11g] Files:1 Time: 0s
\n****************** Catproc SQL *****************
\nParallel Phase #:42 [orcl11g] Files:13 Time: 52s
\nRestart Phase #:43 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:44 [orcl11g] Files:12 Time: 16s
\nRestart Phase #:45 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:46 [orcl11g] Files:2 Time: 1s
\nRestart Phase #:47 [orcl11g] Files:1 Time: 0s
\n************* Final Catproc scripts ************
\nSerial Phase #:48 [orcl11g] Files:1 Time: 5s
\nRestart Phase #:49 [orcl11g] Files:1 Time: 0s
\n************** Final RDBMS scripts *************
\nSerial Phase #:50 [orcl11g] Files:1 Time: 19s
\n************ Upgrade Component Start ***********
\nSerial Phase #:51 [orcl11g] Files:1 Time: 1s
\nRestart Phase #:52 [orcl11g] Files:1 Time: 0s
\n**************** Upgrading Java ****************
\nSerial Phase #:53 [orcl11g] Files:1 Time: 235s
\nRestart Phase #:54 [orcl11g] Files:1 Time: 0s
\n***************** Upgrading XDK ****************
\nSerial Phase #:55 [orcl11g] Files:1 Time: 28s
\nRestart Phase #:56 [orcl11g] Files:1 Time: 0s
\n********* Upgrading APS,OLS,DV,CONTEXT *********
\nSerial Phase #:57 [orcl11g] Files:1 Time: 52s
\n***************** Upgrading XDB ****************
\nRestart Phase #:58 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:60 [orcl11g] Files:3 Time: 18s
\nSerial Phase #:61 [orcl11g] Files:3 Time: 4s
\nParallel Phase #:62 [orcl11g] Files:9 Time: 2s
\nParallel Phase #:63 [orcl11g] Files:24 Time: 4s
\nSerial Phase #:64 [orcl11g] Files:4 Time: 5s
\nSerial Phase #:65 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:66 [orcl11g] Files:30 Time: 3s
\nSerial Phase #:67 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:68 [orcl11g] Files:6 Time: 2s
\nSerial Phase #:69 [orcl11g] Files:2 Time: 12s
\nSerial Phase #:70 [orcl11g] Files:3 Time: 51s
\nRestart Phase #:71 [orcl11g] Files:1 Time: 0s
\n********* Upgrading CATJAVA,OWM,MGW,RAC ********
\nSerial Phase #:72 [orcl11g] Files:1 Time: 78s
\n**************** Upgrading ORDIM ***************
\nRestart Phase #:73 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:75 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:76 [orcl11g] Files:2 Time: 58s
\nSerial Phase #:77 [orcl11g] Files:1 Time: 49s
\nRestart Phase #:78 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:79 [orcl11g] Files:2 Time: 10s
\nSerial Phase #:80 [orcl11g] Files:2 Time: 0s
\n***************** Upgrading SDO ****************
\nRestart Phase #:81 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:83 [orcl11g] Files:1 Time: 37s
\nSerial Phase #:84 [orcl11g] Files:1 Time: 1s
\nRestart Phase #:85 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:86 [orcl11g] Files:1 Time: 17s
\nRestart Phase #:87 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:88 [orcl11g] Files:3 Time: 98s
\nRestart Phase #:89 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:90 [orcl11g] Files:1 Time: 3s
\nRestart Phase #:91 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:92 [orcl11g] Files:1 Time: 2s
\nRestart Phase #:93 [orcl11g] Files:1 Time: 0s
\nParallel Phase #:94 [orcl11g] Files:4 Time: 65s
\nRestart Phase #:95 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:96 [orcl11g] Files:1 Time: 1s
\nRestart Phase #:97 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:98 [orcl11g] Files:2 Time: 28s
\nRestart Phase #:99 [orcl11g] Files:1 Time: 1s
\nSerial Phase #:100 [orcl11g] Files:1 Time: 0s
\nRestart Phase #:101 [orcl11g] Files:1 Time: 0s
\n*********** Upgrading Misc. ODM, OLAP **********
\nSerial Phase #:102 [orcl11g] Files:1 Time: 21s
\n**************** Upgrading APEX ****************
\nRestart Phase #:103 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:104 [orcl11g] Files:1 Time: 450s
\nRestart Phase #:105 [orcl11g] Files:1 Time: 1s
\n*********** Final Component scripts ***********
\nSerial Phase #:106 [orcl11g] Files:1 Time: 0s
\n************* Final Upgrade scripts ************
\nSerial Phase #:107 [orcl11g] Files:1 Time: 130s
\n********** End PDB Application Upgrade *********
\nSerial Phase #:108 [orcl11g] Files:1 Time: 0s
\n******************* Migration ******************
\nSerial Phase #:109 [orcl11g] Files:1 Time: 36s
\nSerial Phase #:110 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:111 [orcl11g] Files:1 Time: 43s
\n***************** Post Upgrade *****************
\nSerial Phase #:112 [orcl11g] Files:1 Time: 439s
\n**************** Summary report ****************
\nSerial Phase #:113 [orcl11g] Files:1 Time: 1s
\nSerial Phase #:114 [orcl11g] Files:1 Time: 0s
\nSerial Phase #:115 [orcl11g] Files:1 Time: 29s<\/p>\n

------------------------------------------------------
\nPhases [0-115] End Time:[2017_06_16 22:52:26]
\n------------------------------------------------------<\/p>\n

Grand Total Time: 2665s<\/p>\n

LOG FILES: (\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/catupgrd*.log)<\/p>\n

Upgrade Summary Report Located in:
\n\/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/upg_summary.log<\/p>\n

Grand Total Upgrade Time: [0d:0h:44m:25s]<\/p>\n

13. \u91cd\u542f\u6570\u636e\u5e93
\n[oracle@oracle admin]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 16 22:54:49 2017
\nCopyright (c) 1982, 2016, Oracle. All rights reserved.
\nConnected to an idle instance.<\/p>\n

SQL> startup<\/span>
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.<\/p>\n

14. \u8865\u5145\u811a\u672c
\n[oracle@oracle admin]$ $ORACLE_HOME\/perl\/bin\/perl catcon.pl -n 1 -e -b catuppst -d '''.''' catuppst.sql<\/span>
\ncatcon: ALL catcon-related output will be written to [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/catuppst_catcon_16736.lst]
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/catuppst*.log] files for output generated by scripts
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/catuppst_*.lst] files for spool files, if any
\ncatcon.pl: completed successfully<\/p>\n

15.\u7f16\u8bd1\u5bf9\u8c61\u811a\u672c
\n[oracle@oracle admin]$ $ORACLE_HOME\/perl\/bin\/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql<\/span>
\ncatcon: ALL catcon-related output will be written to [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/utlrp_catcon_16773.lst]
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/utlrp*.log] files for output generated by scripts
\ncatcon: See [\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/utlrp_*.lst] files for spool files, if any
\ncatcon.pl: completed successfully<\/p>\n

16. \u9884\u68c0\u67e5\u4fee\u590d\u811a\u672c
\nHow to Download and Run Oracle's Database Pre-Upgrade Utility (\u6587\u6863 ID 884522.1)<\/p>\n

[root@oracle opt]# unzip preupgrade_12201_cbuild_005.zip<\/span>
\nArchive: preupgrade_12201_cbuild_005.zip
\ninflating: dbms_registry_basic.sql
\ninflating: dbms_registry_extended.sql
\ninflating: preupgrade_driver.sql
\ninflating: preupgrade.jar
\ninflating: preupgrade_messages.properties
\ninflating: preupgrade_package.sql<\/p>\n

[root@oracle opt]# chown oracle:oinstall dbms_registry_* preupgrade*<\/span><\/p>\n

[root@oracle opt]# cp -rp dbms_registry_* preupgrade*<\/span> \/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/dbms_registry_basic.sql'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/dbms_registry_extended.sql'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/preupgrade_12201_cbuild_005.zip'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/preupgrade_driver.sql'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/preupgrade.jar'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/preupgrade_messages.properties'? y
\ncp: overwrite `\/u01\/app\/oracle\/product\/12.2.0\/db_1\/rdbms\/admin\/preupgrade_package.sql'? y<\/p>\n

[oracle@oracle db_1]$ $ORACLE_HOME\/jdk\/bin\/java -jar $ORACLE_HOME\/rdbms\/admin\/preupgrade.jar<\/span>
\nPreupgrade generated files:
\n\/u01\/app\/oracle\/cfgtoollogs\/orcl11g\/preupgrade\/preupgrade.log
\n\/u01\/app\/oracle\/cfgtoollogs\/orcl11g\/preupgrade\/preupgrade_fixups.sql
\n\/u01\/app\/oracle\/cfgtoollogs\/orcl11g\/preupgrade\/postupgrade_fixups.sql<\/p>\n

[oracle@oracle db_1]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 17 07:53:25 2017
\nCopyright (c) 1982, 2016, Oracle. All rights reserved.
\nConnected to:
\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production<\/p>\n

SQL> @\/u01\/app\/oracle\/cfgtoollogs\/orcl11g\/preupgrade\/postupgrade_fixups.sql<\/span>
\nSession altered.
\nPL\/SQL procedure successfully completed.
\nPL\/SQL procedure successfully completed.
\nPackage created.
\nNo errors.
\nPackage body created.
\nNo errors.
\nPackage created.
\nNo errors.
\nPackage body created.
\nNo errors.
\nExecuting Oracle POST-Upgrade Fixup Script
\nAuto-Generated by: Oracle Preupgrade Script
\nVersion: 12.2.0.1.0 Build: 5
\nGenerated on: 2017-06-17 07:51:12
\nFor Source Database: ORCL11G
\nSource Database Version: 12.2.0.1.0
\nFor Upgrade to Version: 12.2.0.1.0
\nFixup
\nCheck Name Status Further DBA Action
\n---------- ------ ------------------
\nold_time_zones_exist Failed Manual fixup recommended.
\npost_dictionary Passed None
\nupg_by_std_upgrd Failed Manual fixup recommended.
\nPL\/SQL procedure successfully completed.
\nSession altered.<\/p>\n

17. \u68c0\u67e5\u811a\u672c
\nSQL> @?\/rdbms\/admin\/utlu122s.sql<\/span><\/p>\n

Oracle Database 12.2 Post-Upgrade Status Tool 06-17-2017 07:57:00<\/p>\n

Component Current Version Elapsed Time
\nName Status Number HH:MM:SS<\/p>\n

Oracle Server VALID 12.2.0.1.0 00:10:42
\nJServer JAVA Virtual Machine VALID 12.2.0.1.0 00:03:53
\nOracle Workspace Manager VALID 12.2.0.1.0 00:01:07
\nOLAP Analytic Workspace VALID 12.2.0.1.0 00:00:19
\nOLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
\nOracle OLAP API VALID 12.2.0.1.0 00:00:13
\nOracle XDK VALID 12.2.0.1.0 00:00:27
\nOracle Text VALID 12.2.0.1.0 00:00:31
\nOracle XML Database VALID 12.2.0.1.0 00:01:40
\nOracle Database Java Packages VALID 12.2.0.1.0 00:00:09
\nOracle Multimedia VALID 12.2.0.1.0 00:01:57
\nSpatial VALID 12.2.0.1.0 00:04:11
\nOracle Application Express VALID 5.0.4.00.12 00:07:29
\nFinal Actions 00:02:46
\nPost Upgrade 00:00:01
\nPost Compile 00:06:36<\/p>\n

Total Upgrade Time: 00:42:23<\/p>\n

Database time zone version is 14. It is older than current release time
\nzone version 26. Time zone upgrade is needed using the DBMS_DST package.<\/p>\n

Summary Report File = \/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/orcl11g\/upgrade20170616220805\/upg_summary.log<\/p>\n

18. \u68c0\u67e5\u7ec4\u4ef6\u7684\u72b6\u6001
\nSQL> set line 200<\/span>
\nSQL> col COMP_ID format a10<\/span>
\nSQL> col COMP_NAME format a35<\/span>
\nSQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)<\/span>
\n2 comp_name,substr(version,1,10) version,status<\/span>
\n3 from dba_registry order by modified;<\/span><\/p>\n

COMP_ID COMP_NAME VERSION STATUS
\n---------- ----------------------------------- -------------------- -----------
\nAMD OLAP Catalog 11.2.0.4.0 OPTION OFF
\nCATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
\nCATPROC Oracle Database Packages and T 12.2.0.1.0 VALID
\nOWM Oracle Workspace Manager 12.2.0.1.0 VALID
\nCATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID
\nJAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
\nXML Oracle XDK 12.2.0.1.0 VALID
\nCONTEXT Oracle Text 12.2.0.1.0 VALID
\nXDB Oracle XML Database 12.2.0.1.0 VALID
\nORDIM Oracle Multimedia 12.2.0.1.0 VALID
\nAPS OLAP Analytic Workspace 12.2.0.1.0 VALID
\nXOQ Oracle OLAP API 12.2.0.1.0 VALID
\nSDO Spatial 12.2.0.1.0 VALID
\nAPEX Oracle Application Express 5.0.4.00.1 VALID<\/p>\n

14 rows selected.<\/p>\n

19. \u68c0\u67e5OPatch\u7684\u60c5\u51b5
\n[oracle@oracle db_1]$ cd OPatch\/<\/span>
\n[oracle@oracle OPatch]$ .\/opatch lsinventory<\/span>
\nOracle Interim Patch Installer version 12.2.0.1.6
\nCopyright (c) 2017, Oracle Corporation. All rights reserved.<\/p>\n

Oracle Home : \/u01\/app\/oracle\/product\/12.2.0\/db_1
\nCentral Inventory : \/u01\/app\/oraInventory
\nfrom : \/u01\/app\/oracle\/product\/12.2.0\/db_1\/oraInst.loc
\nOPatch version : 12.2.0.1.6
\nOUI version : 12.2.0.1.4
\nLog file location : \/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/opatch\/opatch2017-06-17_08-08-09AM_1.log<\/p>\n

Lsinventory Output file location : \/u01\/app\/oracle\/product\/12.2.0\/db_1\/cfgtoollogs\/opatch\/lsinv\/lsinventory2017-06-17_08-08-09AM.txt<\/p>\n

-------------------------------------------------------------------------------
\nLocal Machine Information::
\nHostname: oracle
\nARU platform id: 226
\nARU platform description:: Linux x86-64<\/p>\n

Installed Top-level Products (1):<\/p>\n

Oracle Database 12c 12.2.0.1.0
\nThere are 1 products installed in this Oracle Home.<\/p>\n

There are no Interim patches installed in this Oracle Home.
\n-------------------------------------------------------------------------------
\nOPatch succeeded.<\/p>\n

20. \u4fee\u6539ORACLE_HOME\uff0cPATH
\n[oracle@oracle ~]$ cat .bash_profile<\/span>
\n# .bash_profile<\/p>\n

# Get the aliases and functions
\nif [ -f ~\/.bashrc ]; then
\n. ~\/.bashrc
\nfi<\/p>\n

# User specific environment and startup programs<\/p>\n

PATH=$PATH:$HOME\/bin<\/p>\n

export PATH<\/p>\n

export ORACLE_BASE=\/u01\/app\/oracle
\n#export ORACLE_HOME=$ORACLE_BASE\/product\/11.2.0\/db_1
\nexport ORACLE_HOME=$ORACLE_BASE\/product\/12.2.0\/db_1
\nexport PATH=$ORACLE_HOME\/bin:$PATH
\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/bin:$LD_LIBRARY_PATH
\nexport ORACLE_SID=orcl11g
\n#export ORACLE_SID=orcl12c
\numask 022<\/p>\n

21. \u68c0\u67e5\/etc\/oratab
\n[root@oracle opt]# vi \/etc\/oratab<\/span>
\n#orcl11g:\/u01\/app\/oracle\/product\/11.2.0\/db_1:N
\norcl12c:\/u01\/app\/oracle\/product\/12.2.0\/db_1:N<\/p>\n

22. \u4fee\u6539\u652f\u6301\u6269\u5c55\u6570\u636e\u7c7b\u578b
\nSQL> show parameter COMPATIBLE<\/span><\/p>\n

NAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\ncompatible string 11.2.0.4.0
\nnoncdb_compatible boolean FALSE
\nSQL> show parameter max_string_size<\/span><\/p>\n

NAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nmax_string_size string STANDARD<\/p>\n

SQL> alter system set compatible='12.2.0.1.0' scope=spfile;<\/span>
\nSystem altered.<\/p>\n

SQL> alter system set MAX_STRING_SIZE = EXTENDED scope=spfile;<\/span>
\nSystem altered.<\/p>\n

SQL> shutdown immediate<\/span>
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.
\nSQL> startup<\/span>
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nORA-00603: ORACLE server session terminated by fatal error
\nORA-01092: ORACLE instance terminated. Disconnection forced
\nORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
\nProcess ID: 24368
\nSession ID: 32 Serial number: 24866<\/p>\n

SQL> startup upgrade<\/span>
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.<\/p>\n

SQL> alter system set MAX_STRING_SIZE = EXTENDED;<\/span><\/p>\n

System altered.<\/p>\n

SQL> @?\/rdbms\/admin\/utl32k.sql<\/span>
\nSP2-0042: unknown command \"aRem\" - rest of line ignored.
\nSession altered.
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC> The following statement will cause an \"ORA-01722: invalid number\"
\nDOC> error if the database has not been opened for UPGRADE.
\nDOC>
\nDOC> Perform a \"SHUTDOWN ABORT\" and
\nDOC> restart using UPGRADE.
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC>#
\nno rows selected
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC> The following statement will cause an \"ORA-01722: invalid number\"
\nDOC> error if the database does not have compatible >= 12.0.0
\nDOC>
\nDOC> Set compatible >= 12.0.0 and retry.
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC>#
\nPL\/SQL procedure successfully completed.
\nSession altered.
\n1988 rows updated.
\nCommit complete.
\nSystem altered.
\nPL\/SQL procedure successfully completed.
\nCommit complete.
\nSystem altered.
\nSession altered.
\nSession altered.
\nTable created.
\nTable created.
\nTable created.
\nTable truncated.
\n0 rows created.
\nPL\/SQL procedure successfully completed.
\nSTARTTIME
\n-------------------------------------------------------------------------------
\n06\/17\/2017 08:17:22.920635000
\nPL\/SQL procedure successfully completed.
\nNo errors.
\nPL\/SQL procedure successfully completed.
\nSession altered.
\nSession altered.
\n0 rows created.
\nno rows selected
\nno rows selected
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC> The following statement will cause an \"ORA-01722: invalid number\"
\nDOC> error if we encountered an error while modifying a column to
\nDOC> account for data type length change as a result of enabling or
\nDOC> disabling 32k types.
\nDOC>
\nDOC> Contact Oracle support for assistance.
\nDOC>#######################################################################
\nDOC>#######################################################################
\nDOC>#
\nPL\/SQL procedure successfully completed.
\nPL\/SQL procedure successfully completed.
\nCommit complete.
\nPackage altered.
\nPackage altered.
\nSQL> shutdown immediate<\/span>
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.
\nSQL> startup<\/span>
\nORACLE instance started.
\nTotal System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.
\nSQL> show parameter COMPATIBLE<\/span><\/p>\n

NAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\ncompatible string 12.2.0.1.0
\nnoncdb_compatible boolean FALSE
\nSQL> show parameter max_string_size<\/p>\n

NAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nmax_string_size string EXTENDED
\nSQL><\/p>\n

23. \u5347\u7ea7catalog
\nRMAN\u5ba2\u6237\u7aef\u7248\u672c\u4f4e\u7684\u60c5\u51b5\u4e0b\uff0c\u9700\u8981\u5347\u7ea7
\nUpgrade catalog<\/p>\n

24. \u5347\u7ea7timezone<\/p>\n

11g\u672c\u8eab\u7684timezone\u662f14<\/p>\n

SQL> select * from v$timezone_file;<\/span><\/p>\n

FILENAME VERSION CON_ID
\n-------------------- ---------- ----------
\ntimezlrg_14.dat 14 0<\/p>\n

SQL> set line 200<\/span>
\nSQL> col PROPERTY_NAME format a30<\/span>
\nSQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value<\/span>
\n2 FROM DATABASE_PROPERTIES<\/span>
\n3 WHERE PROPERTY_NAME LIKE 'DST_%'<\/span>
\n4 ORDER BY PROPERTY_NAME;<\/span><\/p>\n

PROPERTY_NAME VALUE
\n------------------------------ ------------------------------------------------
\nDST_PRIMARY_TT_VERSION 14
\nDST_SECONDARY_TT_VERSION 0
\nDST_UPGRADE_STATE NONE<\/p>\n

[root@oracle opt]# mkdir timezone<\/span>
\n[root@oracle opt]# mv DBMS_DST_scriptsV1.9.zip timezone\/<\/span>
\n[root@oracle timezone]# unzip DBMS_DST_scriptsV1.9.zip<\/span>
\nArchive: DBMS_DST_scriptsV1.9.zip
\ncreating: DBMS_DST_scriptsV1.9\/
\ninflating: DBMS_DST_scriptsV1.9\/countstarTSTZ.sql
\ninflating: DBMS_DST_scriptsV1.9\/countstatsTSTZ.sql
\ninflating: DBMS_DST_scriptsV1.9\/upg_tzv_apply.sql
\ninflating: DBMS_DST_scriptsV1.9\/upg_tzv_check.sql<\/p>\n

[root@oracle opt]# chown oracle:oinstall -R timezone\/<\/span>
\n[root@oracle DBMS_DST_scriptsV1.9]# ll<\/span>
\ntotal 68
\n-rw-r--r--. 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql
\n-rw-r--r--. 1 oracle oinstall 6909 Jan 8 2015 countstatsTSTZ.sql
\n-rw-r--r--. 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql
\n-rw-r--r--. 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql<\/p>\n

[oracle@oracle DBMS_DST_scriptsV1.9]$ pwd<\/span>
\n\/opt\/timezone\/DBMS_DST_scriptsV1.9
\n[oracle@oracle DBMS_DST_scriptsV1.9]$ sqlplus \/ as sysdba<\/span>
\nSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 17 09:38:03 2017
\nCopyright (c) 1982, 2016, Oracle. All rights reserved.
\nConnected to:
\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production<\/p>\n

SQL> @countstatsTSTZ.sql<\/span>
\n.
\nAmount of TSTZ data using num_rows stats info in DBA_TABLES.
\n.
\nFor SYS tables first...
\nNote: empty tables are not listed.
\nStat date - Owner.Tablename.Columnname - num_rows
\n24\/08\/2013 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
\n24\/08\/2013 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
\n24\/08\/2013 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
\n24\/08\/2013 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
\n24\/08\/2013 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
\n24\/08\/2013 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
\n17\/06\/2017 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
\n17\/06\/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
\n17\/06\/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
\n17\/06\/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
\n17\/06\/2017 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
\n17\/06\/2017 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
\n17\/06\/2017 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
\n17\/06\/2017 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
\n17\/06\/2017 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
\n17\/06\/2017 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
\n17\/06\/2017 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
\n17\/06\/2017 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
\n17\/06\/2017 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3
\n17\/06\/2017 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3
\n17\/06\/2017 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 39
\n17\/06\/2017 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 39
\n17\/06\/2017 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 5566
\n17\/06\/2017 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 35
\n17\/06\/2017 - SYS.RADM_FPTM$.TSWTZ_COL - 1
\n17\/06\/2017 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
\n17\/06\/2017 - SYS.REG$.REG_TIME - 2
\n17\/06\/2017 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 296
\n17\/06\/2017 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.END_DATE - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB.START_DATE - 28
\n17\/06\/2017 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 190
\n17\/06\/2017 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 190
\n17\/06\/2017 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 190
\n17\/06\/2017 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
\n17\/06\/2017 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
\n17\/06\/2017 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
\n17\/06\/2017 - SYS.STATS_TARGET$.END_TIME - 1698
\n17\/06\/2017 - SYS.STATS_TARGET$.START_TIME - 1698
\n17\/06\/2017 - SYS.TAB_STATS$.SPARE6 - 1117
\n17\/06\/2017 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 23
\n17\/06\/2017 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 23
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 42762
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 42762
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 56148
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 56148
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 3686
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 3686
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 134
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 134
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 134
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 7422
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 7422
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 7422
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 3705
\n17\/06\/2017 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 3705
\n17\/06\/2017 - SYS.XS$PRIN.END_DATE - 15
\n17\/06\/2017 - SYS.XS$PRIN.START_DATE - 15
\nTotal numrow of SYS TSTZ columns is : 246735
\nThere are in total 154 non-SYS TSTZ columns.
\n.
\nFor non-SYS tables ...
\nNote: empty tables are not listed.
\nStat date - Owner.Tablename.Columnname - num_rows
\n17\/06\/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
\n17\/06\/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
\n17\/06\/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
\n1
\n17\/06\/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
\n17\/06\/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
\n17\/06\/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
\n17\/06\/2017 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
\n17\/06\/2017 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
\nTotal numrow of non-SYS TSTZ columns is : 8
\nThere are in total 24 non-SYS TSTZ columns.
\nTotal Minutes elapsed : 0<\/p>\n

SQL> @upg_tzv_check.sql<\/span>
\nINFO: Starting with RDBMS DST update preparation.
\nINFO: NO actual RDBMS DST update will be done by this script.
\nINFO: If an ERROR occurs the script will EXIT sqlplus.
\nINFO: Doing checks for known issues ...
\nINFO: Database version is 12.2.0.1 .
\nINFO: Database RDBMS DST version is DSTv14 .
\nINFO: No known issues detected.
\nINFO: Now detecting new RDBMS DST version.
\nA prepare window has been successfully started.
\nINFO: Newest RDBMS DST version detected is DSTv26 .
\nINFO: Next step is checking all TSTZ data.
\nINFO: It might take a while before any further output is seen ...
\nA prepare window has been successfully ended.
\nINFO: A newer RDBMS DST version than the one currently used is found.
\nINFO: Note that NO DST update was yet done.
\nINFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
\nINFO: Note that the upg_tzv_apply.sql script will
\nINFO: restart the database 2 times WITHOUT any confirmation or prompt.<\/p>\n

SQL> @upg_tzv_apply.sql<\/span>
\nINFO: If an ERROR occurs the script will EXIT sqlplus.
\nINFO: The database RDBMS DST version will be updated to DSTv26 .
\nWARNING: This script will restart the database 2 times
\nWARNING: WITHOUT asking ANY confirmation.
\nWARNING: Hit control-c NOW if this is not intended.
\nINFO: Restarting the database in UPGRADE mode to start the DST upgrade.
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.
\nINFO: Starting the RDBMS DST upgrade.
\nINFO: Upgrading all SYS owned TSTZ data.
\nINFO: It might take time before any further output is seen ...
\nAn upgrade window has been successfully started.
\nINFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.
\nORACLE instance started.<\/p>\n

Total System Global Area 629145600 bytes
\nFixed Size 8623832 bytes
\nVariable Size 234883368 bytes
\nDatabase Buffers 381681664 bytes
\nRedo Buffers 3956736 bytes
\nDatabase mounted.
\nDatabase opened.
\nINFO: Upgrading all non-SYS TSTZ data.
\nINFO: It might take time before any further output is seen ...
\nINFO: Do NOT start any application yet that uses TSTZ data!
\nINFO: Next is a list of all upgraded tables:
\nTable list: \"GSMADMIN_INTERNAL\".\"AQ$_CHANGE_LOG_QUEUE_TABLE_L\"
\nNumber of failures: 0
\nTable list: \"GSMADMIN_INTERNAL\".\"AQ$_CHANGE_LOG_QUEUE_TABLE_S\"
\nNumber of failures: 0
\nTable list: \"APEX_050000\".\"WWV_FLOW_DEBUG_MESSAGES\"
\nNumber of failures: 0
\nTable list: \"APEX_050000\".\"WWV_FLOW_DEBUG_MESSAGES2\"
\nNumber of failures: 0
\nTable list: \"APEX_050000\".\"WWV_FLOW_FEEDBACK\"
\nNumber of failures: 0
\nTable list: \"APEX_050000\".\"WWV_FLOW_FEEDBACK_FOLLOWUP\"
\nNumber of failures: 0
\nTable list: \"APEX_050000\".\"WWV_FLOW_WORKSHEET_NOTIFY\"
\nNumber of failures: 0
\nINFO: Total failures during update of TSTZ data: 0 .
\nAn upgrade window has been successfully ended.
\nINFO: Your new Server RDBMS DST version is DSTv26 .
\nINFO: The RDBMS DST update is successfully finished.
\nINFO: Make sure to exit this sqlplus session.
\nINFO: Do not use it for timezone related selects.<\/p>\n

SQL> select * from v$timezone_file;<\/span><\/p>\n

FILENAME VERSION CON_ID
\n-------------------- ---------- ----------
\ntimezlrg_26.dat 26 0<\/p>\n

SQL> set line 200<\/span>
\nSQL> col PROPERTY_NAME format a30<\/span>
\nSQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value<\/span>
\n2 FROM DATABASE_PROPERTIES<\/span>
\n3 WHERE PROPERTY_NAME LIKE 'DST_%'<\/span>
\n4 ORDER BY PROPERTY_NAME;<\/span><\/p>\n

PROPERTY_NAME VALUE
\n------------------------------ ------------------------------------------------
\nDST_PRIMARY_TT_VERSION 26
\nDST_SECONDARY_TT_VERSION 0
\nDST_UPGRADE_STATE NONE
\n---------------------<\/p>\n","protected":false},"excerpt":{"rendered":"

\u672c\u6587\u6d89\u8f6c\u8f7d\u81ea https:\/\/blog.csdn.net\/u010692693\/ …<\/p>\n

\u7ee7\u7eed\u9605\u8bfb »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33],"tags":[57],"class_list":["post-888","post","type-post","status-publish","format-standard","hentry","category-db","tag-oracle"],"_links":{"self":[{"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/posts\/888","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=888"}],"version-history":[{"count":1,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/posts\/888\/revisions"}],"predecessor-version":[{"id":889,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=\/wp\/v2\/posts\/888\/revisions\/889"}],"wp:attachment":[{"href":"https:\/\/www.sulabs.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sulabs.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}