f RMAN Restore Upgrade from 10.2.0.5 to 12.1.0.2 ~ 迪貝之家

Pages

RMAN Restore Upgrade from 10.2.0.5 to 12.1.0.2

基本上就分成兩個部份,一是在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.1timezone的升級文件請參看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.

大功告成!!