Remember Oracle 10g data recovery Events once

It turns out that it's not going to die without dying, this time the oracle crashed and it took me two days to unload it just because I installed some inexplicable Android simulator.

After uninstalling, found that the Oracle database can not be used, the heart of a cold, because they have stored in two years of data in the inside, nearly 70 g. So hurriedly into net Manager, can't get in, need to enter the path of the configuration file! This is definitely oraacle_home. A oracle_home variable is added to the environment variable, and the address points to E:\oracle\product\10.2.0\db_1. Again into net Manager, no problem, but found an error ora-12514 ...

This is the most common error, meaning it is easy to solve or difficult to solve.

Hurriedly search, on-line generally two kinds of practice: 1. Modify the Listening file Listener.ora file, add a sid_desc in it ... Global_name= ORCL (own instance name), explained because static listening needs to be set to actively look for the instance, otherwise it cannot find this instance. So I changed and then reported another error: ora-01034 and ora-27101.

Continue to search the Internet, said that it is due to abnormal exit and other operations so that ORACLE does not know which instance name to point to, need to use the set ORACLE_SID=ORCL in CMD settings, so set. Then you need to restart Oracle. Then the problem came again, I unexpectedly forget their SYS user! Because of the time, I can not think of their own sys user, that is, when I entered sqlplus in CMD, can not conn ... I suddenly distressed, feeling a lot of trouble.

The last road is not working, I decided to change the idea, is so active. On the internet there is another way to say is to re-monitor. Then rebuild it. NETCA-side observation in CMD. Basically no exception. Build success. Then in the OS service to find the monitoring service, start (here can also be in cmd lsnrctl start). But after the launch is still reported ora-12514, that is, all efforts are in vain!!!

Calm down to analyze the reason, unexpectedly found that there are two monitoring services: one is the standard Oracleoradb10g_home1tnslistener, the other is Oracletnslistener, and the latter is started, the former does not start. Internet search Their differences, no information, in the mind very confused.

So I'm going to build another monitor to see. NETCA went on to build and found another monitoring service:OracleTNSListener1. I seem to understand what ... Internet search monitoring Service naming rules, found that the beginning of Oracle,tns+ listener name End , the middle plus oracle_home_name composition. Instantly understand, the original oracle_home_name this configuration is gone! This oracle_home_name is actually the default option when you install the database, called the name.

After you understand, you decide to set oracle_home_name in the environment variable . This information includes the above oracle_home, which are actually stored in one of Oracle's configuration files. When the configuration file is missing, Oracle is also looking through the environment variables, so it can be configured in the environment variable so that Oracle is ready to use it. Configure Oracle_home_name with a value of oradb10g_home1.

Configuration complete, delete all monitoring, rebuild. Later found that the standard Oracleoradb10g_home1tnslistener appeared, and started up! The following ORACLESERVICEORCL is always started, both services are started, which means ... It's done!

Oh, still too naïve. Still reported ora-12514 error.

I'm cool again. Most of the day, by connecting the database of other machines and viewing the monitoring status of the Lsnrstl status to check whether the monitoring is normal, found that monitoring is not a problem. Since the monitoring is not a problem, that is, the above efforts are all wasted, the database is not connected to the root cause is actually an example of the problem.

Also consult the Alert_orcl.log file (E:\oracle\product\10.2.0\admin\orcl\bdump\), turn to the last section and find some tns-12560 errors. Combined with some online statements, the final presumption: The Oracle database is broken ...

It was a huge tragedy. I don't have sys user, I can't nomount, mount, open, I don't know what kind of mistake happened in the specific part. Maybe a reboot is OK, but I don't have sys user, there is no permission to operate.

Can only change the idea, determined to reinstall the database!

So, I'm going to use the data file for data recovery!

Internet search, a lot of tutorials, found that they are all talking about only the recovery of data files, and I now data files, control files, log files are in, the feeling should be more simple. So I decided to test it on another computer first.

I turned out my own notebook, the online tutorial said that as long as the establishment of the same name instance, port and so on, then the above file (mainly oradata this folder) copy into the cover, and then restart the service on the line. So try it!

But here I find a problem, the notebook is 11g Oracle, is really a pit father. Helpless had to bite the bullet to copy the above files to the corresponding folder.

CMD into the SYS user (the SYS user exists), start trying to start: shutdown immediate, start Nomount no problem (Nomount is the parameter file to find the control file, as long as the control file path is accurate, will not error), and then alter Database mount (This stage is the control file to find data files, can not find the error), error, because the data file in the control file stored in the address is 10g E:\oracle\product\10.2.0\oradata, rather than 11g app\ ... The data file is then copied to the address that the control file points to (it's all pushed to the--! ), then start mount, no problem!

All of a sudden, the whole person is manic! Is this going to be successful?

Then ALTER DATABASE open AH! Pop up on the screen and bounce off some good information! Are you going to be successful?

Oh, still too naïve. ora-00704 and ora-39700. Continue to search, found that the data dictionary table because of the version of the problem and error Ah! Online says Execute SQL Script update data dictionary. Then it's all right!

Catupgrd.sql, Catalog.sql, Catproc.sql, utlrp.sql four scripts, executed in CMD's Sqlplus @ path. These four scripts only found three in my case, the third one did not, and then executed in turn.

Oh, a big wave error roll screen hit Ah!!! Can not create Ah!!! Feel like jumping off a jump!!!

Cool a moment, feel 10g and 11g is insurmountable gap, so decided to uninstall 11g, instead 10g continue above the operation.

10g installation is complete, Oradata will be overwritten, continue to execute in the sqlplus of CMD. shutdown immediate, start Nomount,ALTER DATABASE Mount no problem,ALTER DATABASE open ... Again the error ... I went, unexpectedly reported that the DBF file is 11g instead of 10g! The original DBF was 11g so that a mess was defiled Ah! It's so shy!

Re-copy the 10g Oradata folder, continue to open ...

Finally succeeded!!!

I've been doing this for two days!!! It worked!!!

To summarize:

(1) ora-12514 only two aspects of error, monitoring abnormal, or the instance does not start, can be in these two aspects of the investigation, and do not blindly follow the online method to change the Listener.ora and reconstruction monitoring, perhaps the case is not?

(2) For data recovery, it is best to have oradata all files under this folder, so emboldened;

(3) I toss two days, perhaps in the beginning to restart the database will return to normal, but the SYS user lost, making it all difficult;

(4) To learn to calm analysis, combined with their own situation to take appropriate measures, and can not blindly follow the online solution to implement, it may not be suitable for you;

(5) Lots of backup, don't be lazy!

It's gone.

Remember Oracle 10g data recovery Events once