Shulou(Shulou.com)06/01 Report--

Last night, the production library had to be upgraded from 11.2.0.3 to 11.2.0.4, but the database could not be startup upgrade because of the ORA-01157 ORA-01110 error.

Environment: HP-UX B.11.31 "11.2.0.3 +" device, database size nearly 8T

As it has been done once before, and there are ready-made documents that are familiar with it, 11.2.0.4 software and patches have been written in advance and upgrades are started before the business is stopped.

It went well at the beginning of the check until the RMAN backup was completed. Due to the large amount of database data, the backup time is reduced by setting all business tablespaces to read only state and only backing up system-related tablespaces (SYSTEM/SYSAUX/UNDOTBS1).

When the backup completes and records the current SCN number, stop the database, cut to the new environment variable to start startup upgrade, and upgrade the data dictionary.

But the example is wrong in the status from MOUNT to OPEN

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 bytesDatabase 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'

There are also a large number of errors in ALERT logs.

ERROR: clonedb parameter not set. Make sure clonedb=TRUE is setErrors in file / oracle11g/app/oracle/diag/rdbms/db1/db1/trace/db1_dbw0_20898.trc:ORA-01157: / 2 -? DBWR ORA-01110: 2:'/ dev/vgdb1ora8/rlvorasysaux'ORA-17503: ksfdopn: 1 / dev/vgdb1ora8/rlvorasysauxORA-17515:? Clonedb?.

So go to MOS to check for related errors, and there is really one similar to our current situation: ORA-01157 Cannot Identify Lock On Datafile Error During Upgrade (document ID 1917635.1). However, from the description of the document, it is said that there is a bad block in the device, but it is obvious that the shutdown immediate shut down the database cleanly a few minutes ago, so how can there be a bad block? moreover, there is no error in RMAN backup.

So close the current instance, switch the environment variable back to 11.2.0.3, start the database, and the magic happens, and the database starts normally.

SYS@db1 > startupORACLE instance started.Total System Global Area 6.8413E+10 bytesFixed Size 2199712 bytesVariable Size 1.5569E+10 bytesDatabase Buffers 5.2748E+10 bytesRedo Buffers 93655040 bytesDatabase mounted.Database opened.

Now the situation has become more complicated, the original environment variables can OPEN the database, the new environment variables can not OPEN the database.

So close the old instance, cut to the new environment variable, check the pfile file, and find compatible=11.2.0.3, is that the problem? change this parameter to 11.2.0.4, restart the new instance, and still report an error.

Open the boss in the group to help look, check that the various states of the vg are normal, and there is no abnormal storage. Remount the storage vg and restart the server, all of which have no effect.

So it is said to cut to the old environment to see if it can still OPEN, as a result, not even MOUNT, the following is the error message.

SYS@db1 > startupORACLE instance started.Total System Global Area 6.8413E+10 bytesFixed Size 2199712 bytesVariable Size 1.5569E+10 bytesDatabase 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'

When I saw the error message, I immediately sensed that I had fallen into the hole I had dug. It was caused by the previous operation changed to compatible=11.2.0.4. I regretted it at that time. This parameter cannot be modified until the upgrade is complete. Otherwise, it will cause trouble for the fallback, just like me.

The time has come to more than 1: 00 in the morning, and the business has to deploy new features online. There is not much time left for the database. There is no way to restore the backup. Fortunately, the backup is more important than everything else.

One more thing to say here, during the whole process, there was also a search on baidu based on ORA-01157 ORA-01110, and the solution found was to offline drop the wrong data file. At that time, I was thinking that if someone really did this in production, the next day should be the day when he packed up 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

The restoration was complete, the old environment OPEN was successful, and a stone in my heart was on the ground (at least business could be resumed). It was 01:30 in the morning at this time. Then there is at most an hour and a half to communicate with the business database, and then the boss says that if you don't want to try to upgrade again, it's not too late to fall back. So shutdown the old environment and start the new environment (first change the cpmpatible in pfile to 11.2.0.3). A miracle happened, and the database OPEN was successful.

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 bytesDatabase Buffers 6.3351E+10 bytesRedo Buffers 93634560 bytesDatabase mounted.Database opened.

So began to upgrade the data dictionary, do the follow-up upgrade work, to 2: 00 in the morning 11.2.0.4 upgrade completed.

So the question now is why it will be all right after recovering the data. Is there really a bad block in the channel? Or some other reason, we don't know. This is reserved for asking the engineer of the original factory to see if there is a good explanation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.