基本上就分成兩個部份,一是在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. 大功告成!! |