《Oracle 12c升級(jí)檢查問(wèn)題分析》要點(diǎn)：
本文介紹了Oracle 12c升級(jí)檢查問(wèn)題分析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

今天計(jì)劃把一個(gè)測(cè)試環(huán)境升級(jí)到12c,為了練練手,先在備庫(kù)上來(lái)做.數(shù)據(jù)庫(kù)版本是11.2.0.3.0,計(jì)劃升級(jí)到12.1.0.2.0.

為了不影響原有的測(cè)試主庫(kù),我在備庫(kù)上做了Failover,兩個(gè)命令下去就立刻生效了.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

SQL>alter database recover managed standby database finish force;
Database altered.

SQL>alter database commit to switchover to primary;
Database altered.

然后使用克隆安裝12c的數(shù)據(jù)庫(kù)軟件,使用下面的命令即可安裝.

$ORACLE_HOME/clone/bin/perl clone.pl ORACLE_BASE=$ORACLE_BASE ORACLE_HOME=$ORACLE_HOME? ORACLE_HOME_NAME=OraDb12c_home1

查看數(shù)據(jù)庫(kù)已經(jīng)部署了最新的補(bǔ)丁

$ opatch lsinventory
...
Patch? 23054246???? : applied on Mon Oct 17 17:01:16 CST 2016
Unique Patch ID:? 20464632
Patch description:? "Database Patch Set Update : 12.1.0.2.160719 (23054246)"
?? Created on 5 Jul 2016, 07:07:59 hrs PST8PDT

看了下官方文檔,發(fā)現(xiàn)對(duì)于12c的升級(jí)和升級(jí)11g差別不大,手工升級(jí)的步驟很多腳本都是一樣的,思路完全可以復(fù)用.

升級(jí)前的檢查需要跑一個(gè)腳本/preupgrd.sql

本來(lái)想速戰(zhàn)速?zèng)Q,沒(méi)想到檢查的時(shí)候竟然拋出了一個(gè)錯(cuò)誤.

DECLARE
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1003 - see DBWR trace file
ORA-01110: data file 1003: '+DATA'
ORA-06512: at "SYS.DBMS_PREUP", line 2380
ORA-06512: at "SYS.DBMS_PREUP", line 981
ORA-06512: at "SYS.DBMS_PREUP", line 5471
ORA-06512: at line 73? 

這個(gè)錯(cuò)誤看得我有些懵,因?yàn)槲疫@個(gè)備庫(kù)是沒(méi)有使用ASM的,怎么會(huì)拋出和ASM相關(guān)的錯(cuò)誤呢.

查看參數(shù)文件里面,倒是有一行這樣的內(nèi)容

*.db_file_name_convert='+DATA/sgstatdb3/datafile','/U01/app/oracle/oradata/statdb2','+ARCH','/U01/app/oracle/oradata/statdb2','/U01/....

可見(jiàn)原來(lái)的主庫(kù)是使用了ASM,但是在備庫(kù)端壓根沒(méi)有用到,怎么會(huì)拋出這個(gè)錯(cuò)誤呢.

查看alert日志,發(fā)現(xiàn)這個(gè)錯(cuò)誤還挺特別.

Mon Oct 31 22:27:02 2016
WARNING: ASM communication error: op 36 state 0x40 (15077)
ERROR: slave communication error with ASM
Mon Oct 31 22:28:56 2016
WARNING: ASM communication error: op 36 state 0x40 (15077)
ERROR: slave communication error with ASM
Mon Oct 31 22:30:00 2016
Thread 1 advanced to log sequence 3 (LGWR switch)

從錯(cuò)誤日志可以看出,是在和ASM實(shí)例通信的時(shí)候出問(wèn)題了.這個(gè)環(huán)境壓根沒(méi)有用ASM,肯定出問(wèn)題了.

看錯(cuò)誤是文件1003,查看v$datafile,文件號(hào)最大才是800多,怎么會(huì)冒出一個(gè)1003的文件呢.繼續(xù)查看alert日志,發(fā)現(xiàn)1001也有問(wèn)題,看來(lái)有問(wèn)題的還不止一個(gè)文件,但是數(shù)據(jù)庫(kù)Open沒(méi)有任何問(wèn)題.

Dictionary check beginning
Mon Oct 31 22:05:05 2016
Errors in file /U01/app/oracle/diag/rdbms/sstatdb2/statdb2/trace/statdb2_dbw0_27706.trc:
ORA-01186: file 1001 failed verification tests
ORA-01157: cannot identify/lock data file 1001 - see DBWR trace file
ORA-01110: data file 1001: '+DATA'
File 1001 not verified due to error ORA-01157

腦袋里盤(pán)算著,一邊翻找日志,發(fā)現(xiàn)數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候拋出了下面的錯(cuò)誤.

Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Cannot re-create tempfile +DATA, the same name file exists
Cannot re-create tempfile +DATA, the same name file exists
Cannot re-create tempfile +DATA, the same name file exists

如此一來(lái),問(wèn)題就很明顯了,臨時(shí)表空間的文件映射存在問(wèn)題,導(dǎo)致沒(méi)有創(chuàng)建成功,而臨時(shí)文件有無(wú)不會(huì)影響數(shù)據(jù)庫(kù)的啟動(dòng),所以這個(gè)問(wèn)題就這樣暫時(shí)擱置下來(lái)了.

進(jìn)一步驗(yàn)證,可以看到存著多個(gè)臨時(shí)文件

SQL> SELECT FILE#,NAME FROM V$TEMPFILE
???? FILE# NAME
---------- ------------------------------
???????? 3 +DATA
???????? 4 +DATA
???????? 1 +DATA
???????? 2 +DATA
???????? 5 +DATA
???????? 6 +DATA
???????? 7 +DATA

同時(shí)使用dba_temp_files會(huì)直接拋出之前的錯(cuò)誤.

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
????????????????????? *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1003 - see DBWR trace file
ORA-01110: data file 1003: '+DATA'

問(wèn)題的原因找到了,解決起來(lái)就很容易了.我們可以重新創(chuàng)建一個(gè)臨時(shí)表空間,然后刪除原來(lái)的.

SQL> create temporary tablespace temp1 tempfile '/U01/app/oracle/oradata/statdb2/temp01.dbf' size 100M;
Tablespace created.

SQL> alter database default temporary tablespace temp1;
Database altered.

SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.

后臺(tái)會(huì)繼續(xù)檢查+DATA這個(gè)不存在的虛擬存儲(chǔ),然后最終從數(shù)據(jù)字典層面統(tǒng)一這些信息.

再次做升級(jí)前的檢查,就沒(méi)有任何問(wèn)題了.

作者：楊建榮

文章出處：楊建榮的學(xué)習(xí)筆記(訂閱號(hào)ID：jianrong-notes)

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4400.html