| 基本上就分成兩個部份，一是在source 端執行preupgrade 工具程式，處理報表內出現的ERROR，其他可以不用理會，等完成restore之後，在target端再來處理，因為沒有人想在源頭系統上進行變更，風險太大。另一則是restore作業及升級的步驟。 |
| preupgrade 工具的來源有二，一是安裝12.1.0.2 Oracle Binary 之後，到$ORACLE_HOME/rdbms/admin，把preupgrd.sql及utluppkg.sql拷貝到10.2.0.5的環境執行，一則是你到metalink網站下載，去找文件884522.1，各版本都有。你也許會懷疑，在既有的環境跑preupgrade工具程式會不會有問題，因為這也當初猶豫了好久的點，我可以告訴你，就放一百個心去run 該工具。 |
| 在source 端該做的動作，我覺得沒有必要談太多，我僅提供我所碰到的該修正的兩個ERROR，第一個是報表提到系統用的表格空間不夠，就是擴空間囉；第二就是要求關掉或者purge recyclebin，照做就是了，因為我的環境需要進行OGG 抄寫，在OGG建置過程中就會要求關掉recyclebin，所以我後來的測試就乾脆直接關掉。最後就是進行controlfile 的備份，指令如下，記得switch logfile。 BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.dbf'; |
| 把檔案傳到restore主機，db instance開在mount mode |
| 接著當然就是進行restore，我的rman script如下 |
| run { |
| set archivelog destination to '/ora_data/arch'; |
| sql "alter session set nls_date_format=''YYYY-MM-DD HH24:MI:SS'' "; |
| set until scn 9390628700021; |
| allocate channel ch00 type 'sbt_tape'; |
| SET NEWNAME FOR DATABASE TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 1 TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 2 TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 3 TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 4 TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 5 TO '/ora_data/%b'; |
| SET NEWNAME FOR tempfile 6 TO '/ora_data/%b'; |
| restore database; |
| switch datafile all; |
| switch tempfile all; |
| recover database ; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/redo01.log'' TO ''/ora_data/redo01.log'' "; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/redo02.log'' TO ''/ora_data/redo02.log'' "; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/redo03.log'' TO ''/ora_data/redo03.log'' "; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/sbredo01.log'' TO ''/ora_data/sbredo01.log'' "; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/sbredo02.log'' TO ''/ora_data/sbredo02.log'' "; |
| SQL "ALTER DATABASE RENAME FILE ''/oradata/redo/sbredo03.log'' TO ''/ora_data/sbredo03.log'' "; |
| SQL "ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1 "; |
| SQL "ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 "; |
| SQL "ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3 "; |
| SQL "alter database disable block change tracking "; |
| SQL "alter database noarchivelog "; |
| SQL "alter database open resetlogs upgrade "; |
| } |
| script內容裡我覺得要解釋的是 |
| 1.redo log一定得透過alter database rename file 來進行，不然open resetlogs時，依舊會建在原路徑。 |
| 2.until scn 9390628700021 是因為這份資料是做為OGG Initial Load，怎麼取得SCN，請參照Oracle Restore 時出現ORA-01547 及 ORA-01152一文內所給的語法。 |
| 準備好你的rman script後，當然就是啟動rman放著讓它跑，跑的過程中，就是去傳檔archive log，傳完後就透過catalog 指令把archivelog 位置寫入control file裡，然後就是等rman 做完它該做的事情。 |
| 緊接著就是要進行升級datase internal catalog 等相關物件，既然是rman restore，archive log mode 一定會啟動，我的rman script裡頭就已經下了關掉archive log mode的指令，所以跑升級用catupgrd.sql檔案前，記得關掉資料庫先，然後startup upgrade，沒跑完catupgrd.sql是沒辦法把資料庫正常開啟的，指令如下。 |
| cd $ORACLE_HOME/rdbms/admin |
| $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql |
| internal catalog 升級完之後，catupgrd.sql會shutdown 資料庫instance，此時進行startup指令就能順利開啟資料庫，照升級的正規的步驟，緊接著要執行@utlu121s.sql看升級的報表，看是否有問題。但因為我的環境要透過OGG 同步DDL，開啟了DB Level的trigger，所以得透過下列幾個指令把OGG的schema砍掉，不然只要執行procedure就會出錯。 |
| sqlplus / as sysdba |
| SQL> @ddl_disable |
| SQL> @ddl_remove |
| SQL> @marker_remove |
| SQL> drop user ggadmin cascade; |
| 移除掉OGG之後，升級報表程式utlu121s.sql的執行才會正常，我貼個output給大家看 |
| Oracle Database 12.1 Post-Upgrade Status Tool 05-08-2019 12:42:46 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:07:34 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:08 Oracle Workspace Manager VALID 12.1.0.2.0 00:00:28 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:16 OLAP Catalog OPTION OFF 10.2.0.5.0 00:00:00 Oracle OLAP API VALID 12.1.0.2.0 00:00:18 Oracle XDK VALID 12.1.0.2.0 00:01:22 Oracle Text VALID 12.1.0.2.0 00:00:31 Oracle XML Database VALID 12.1.0.2.0 00:01:40 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:10 Oracle Multimedia VALID 12.1.0.2.0 00:02:12 Spatial UPGRADED 12.1.0.2.0 00:02:58 Final Actions 00:00:39 Post Upgrade 00:02:06 Total Upgrade Time: 00:22:24 PL/SQL procedure successfully completed. |
| 最後就是要升級timezone的版本，做到這個步驟的時候，我就覺得Oracle有點奸詐，因為執行升級的script一定得從metalink下載，我不知道沒做升級會不會在運作上出問題，因為眼前正在做的資料庫只是做為upgrade到18c的一個中繼資料庫，我後續還要透過datapump移轉資料到18c，但如果你是為了營運用，那我還是建議你把timezone版本升上去，不然就是透過datapump 直接把資料從10g轉到12.1。timezone的升級文件請參看metalink文件"Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)"，我把執行結果貼出來供大家參考 |
| SQL> exec DBMS_STATS.PURGE_STATS(systimestamp); |
| PL/SQL procedure successfully completed. |
| SQL> exec dbms_stats.alter_stats_history_retention(31); |
| PL/SQL procedure successfully completed. |
| SQL> spool upg_tzv_check.log |
| SQL> @upg_tzv_check.sql |
| 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 12.1.0.2 . |
| INFO: Database RDBMS DST version is DSTv4 . |
| 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 DSTv18 . |
| 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 upg_tzv_apply.sql to do the actual RDBMS DST update. |
| INFO: Note that the upg_tzv_apply.sql script will |
| INFO: restart the database 2 times WITHOUT any confirmation or prompt. |
| SQL> @upg_tzv_apply.sql |
| INFO: If an ERROR occurs the script will EXIT sqlplus. |
| INFO: The database RDBMS DST version will be updated to DSTv18 . |
| 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. |
| Fixed Size 2934168 bytes |
| Variable Size 1543506536 bytes |
| Database Buffers 8086618112 bytes |
| Redo Buffers 30617600 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 9663676416 bytes |
| Fixed Size 2934168 bytes |
| Variable Size 1543506536 bytes |
| Database Buffers 8086618112 bytes |
| Redo Buffers 30617600 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: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" |
| Number of failures: 0 |
| Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_WRI$_OPTSTAT_AUX_HISTORY" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_SQL_STATEMENT" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_OPTSTAT_USER_PREFS$" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_TAB_STATS_VERSIONS" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_SCHEDULER_JOBS" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_OPTSTAT_OPERATIONS" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_IND_STATS_VERSIONS" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_HISTGRM_STATS_VERSN" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_COL_STATS_VERSIONS" |
| Number of failures: 0 |
| Table list: "SQLTXPLAIN"."SQLT$_DBA_AUTOTASK_CLIENT_HST" |
| 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 DSTv18 . |
| INFO: The RDBMS DST update is successfully finished. |
| INFO: Make sure to exit this sqlplus session. |
| INFO: Do not use it for timezone related selects. |
| 我們來看看版本 |
| SQL> SELECT version FROM v$timezone_file; |
| VERSION |
| ---------- |
| 18 |
| 1 row selected. 大功告成!! |