11.2.0.3 upgrade to 11.2.0.4 Error ORA-01157 ORA-01110

Last night production library to do upgrade, from the 11.2.0.3 upgrade to 11.2.0.4, but encountered ORA-01157 ORA-01110 error, the database can not be startup upgrade.

Environment: HP-UX b.11.31+11.2.0.3+ naked device, database size near 8T

As done before, there are ready-made documents are pro, 11.2.0.4 software and patches have been in advance, to stop the business before the upgrade started.

The first check-up was smooth, until the Rman backup was complete. Due to the large amount of database data, the backup time is reduced by simply backing up the system-related tablespace (SYSTEM/SYSAUX/UNDOTBS1) by placing all business table spaces in the read only state.

Backup complete record current SCN number, stop database, cut to new environment variable start startup upgrade, upgrade data dictionary

But the instance is wrong from mount to open state times

sql> startup upgrade pfile= '/home/oracle/update/initdb1.ora '; Oracle instance started. Total System Global area 6.8413E+10 bytesfixed size 2222664 bytesvariable size 4966057400 byte Sdatabase buffers 6.3351E+10 Bytesredo buffers 93634560 bytesdatabase mounted. Ora-01157:cannot identify/lock data file 2-see DBWR trace fileora-01110:data file 2: '/dev/vgdb1ora8/rlvorasysaux '

Alert logs also have a large number of errors

Error:clonedb parameter not set. Make sure clonedb=true are seterrors in File/oracle11g/app/oracle/diag/rdbms/db1/db1/trace/db1_dbw0_20898.trc:o RA-01157:???? /?????? 2-??? DBWR???? ORA-01110:???? 2: '/dev/vgdb1ora8/rlvorasysaux ' ora-17503:ksfdopn:1?????? /dev/vgdb1ora8/rlvorasysauxora-17515:???????? Clonedb??? ......

So to MOS-related errors, there is really an article similar to our present situation:ORA-01157 cannot Identify Lock on datafile error During Upgrade (document ID 1917635.1). but from the document description, said the naked device has a bad block, but obviously a few minutes ago shutdown immediate clean shut down the database, how there will be bad block, and Rman backup did not error.

So close the current instance, the environment variable cut back to 11.2.0.3, start the database, the magical scene happened, the database actually started normally

[Email protected]> startuporacle instance started. Total System Global area 6.8413E+10 bytesfixed size 2199712 bytesvariable size 1.5569E+10 byte Sdatabase buffers 5.2748E+10 Bytesredo buffers 93655040 bytesdatabase mounted. Database opened.

Now the situation becomes complicated, the original environment variable, can open the database, the new environment variable cannot open the database.

So close the old instance, cut to the new environment variable, check the Pfile file, found compatible=11.2.0.3, that will not be the problem, change this parameter to 11.2.0.4, restart the new instance, the error is still.

Open the group in the boss help look, check the VG various states are normal, storage and no abnormal situation. Re-mount the storage VG and restart the server, no effect.

Then said cut to the old environment to see if it can open, the results even mount are not, the following is an error message.

[Email protected]> startuporacle instance started. Total System Global area 6.8413E+10 bytesfixed size 2199712 bytesvariable size 1.5569E+10 byte Sdatabase buffers 5.2748E+10 Bytesredo buffers 93655040 bytesora-00201:control file version 11.2.0. 4.0 incompatible with ORACLE version 11.2.0.3.0ora-00202:control file: '/dev/vgdb1ora8/rlvoracontrol01 '

See the error message immediately aware of himself fell into the pit of their own digging, the front operation to change compatible=11.2.0.4 caused, then that regret ah ... This parameter cannot be modified until the upgrade is complete. Otherwise it will bring back trouble, just like me.

Time has come to more than 1 o'clock in the morning, the business also to deploy new features on-line, left the database time is not much, no way can only restore backup, fortunately do a backup, back to everything AH!

Here to say a word, the whole process also has in Baidu according to ORA-01157 ORA-01110 search, found the solution is to the error data file offline drop, at that time the heart is thinking, if someone really in production, The next day should be the day when he packed his things and left.

The recovery process is relatively smooth

Restore Controlfile from/home/oracle/backup/bak_control_20161227;alter Database Mount;restore tablespace system, Sysaux,undotbs1;recover database until SCN xxxxxxxx;alter database open resetlogs;

Restore completed, the old environment open success, the heart of a stone is landed (at least can resume business), at this time is 1:30 A.M.. Then with the business Communication database up to 1.5 hours, and then the boss said to no longer try to upgrade, if not back also time. Then shutdown the old environment, start a new environment (first pfile in the cpmpatible to 11.2.0.3), the miracle happened, the database actually open successfully.

sql> startup upgrade pfile= '/home/oracle/update/initdb1.ora '; Oracle instance started. Total System Global area 6.8413E+10 bytesfixed size 2222664 bytesvariable size 4966057400 byte Sdatabase buffers 6.3351E+10 Bytesredo buffers 93634560 bytesdatabase mounted. Database opened.

Then started to upgrade the data dictionary, do the following upgrade work, to 2 o'clock in the morning 11.2.0.4 upgrade completed.

So now the question is, why is it that the data is restored, and the channel really has a bad block? or any other reason, it's unclear. The engineer who keeps asking the factory see if there is any good explanation.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1887085

11.2.0.3 upgrade to 11.2.0.4 Error ORA-01157 ORA-01110