Record an Oracle 10g data recovery event, oracle10 g

Record an Oracle 10g data recovery event, oracle10 g

Facts have proved that I will not die if I don't die. This Oracle crash took me two days, just because I installed some inexplicable Android simulators and uninstalled them again.

After uninstalling the database, I found that the oracle database could not be used, and the heart was quite cool, because I had stored all the data for the past two years, nearly 70 GB. So hurry into the Net Manager and you cannot enter it. You need to enter the path of the configuration file! This is definitely ORAACLE_HOME... Therefore, an ORACLE_HOME variable is added to the environment variable and the address points to E: \ oracle \ product \ 10.2.0 \ db_1. Again into the Net Manager, no problem, but found the error ora-12514...

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

Search quickly. There are two common online practices: 1. modify the listener file listener. add a piece of sid_desc..global_name = orcl (your instance name) to the ora file. The explanation is that the static listener needs to be set to actively search for this instance; otherwise, it cannot find this instance. So I modified it and reported another error: ora-01034 and ora-27101.

Continue searching on the internet, saying that Oracle does not know which instance to point to due to abnormal exit and other operations. You need to set it in cmd with set ORACLE_SID = orcl. Then you need to restart Oracle. Now the problem comes again. I forgot my sys user! Since it was a long time ago, I couldn't even think of my sys user. That is to say, when I entered sqlplus in cmd, I couldn't conn anymore... Now I am suddenly panic, and I feel a big problem.

The previous Road failed. I decided to change my mind. It was so active. Another way on the Internet is to rebuild the listener. Then we need to recreate it. In cmd, observe the netca side. No exception. Created successfully. Then find the listening service in the OS service and start it (here you can also use lsnrctl start in cmd ). But after the start or report ora-12514, that is to say all efforts are in vain !!!

Calm down and analyze the cause, and found that there are two listening services in the Service: one is the standard OracleOraDb10g_home1TNSListener, and the other is OracleTNSListener, while the latter is started, and the former is not started. I am confused about the differences between searching for them online and having no information.

So I plan to create another listener. After netca was built, we found another listening service: OracleTNSListener1. I seem to understand something... The naming rule for the online search listening service is found to start with Oracle, end with the TNS + listening name, with ORACLE_HOME_NAME in the middle. I instantly understood that the original ORACLE_HOME_NAME configuration was gone! This ORACLE_HOME_NAME is a default option called name when you install the database.

After learning about it, we decided to set ORACLE_HOME_NAME in the environment variable. This information includes that ORACLE_HOME is saved in a configuration file of oracle. When the configuration file is lost, oracle will also use the environment variables. Therefore, oracle can use the environment variables after they are configured. Configure ORACLE_HOME_NAME with the value OraDb10g_home1.

Configuration complete, delete all listeners, and recreate. Then we found that the standard OracleOraDb10g_home1TNSListener appeared and started! The following OracleServiceORCL is started all the time. If both services are started, it means... Well done!

It's still naive. Ora-12514 error still reported.

I calmed down for more than half a day and checked the listening status lsnrstl status by connecting to the database of other machines to check whether the listening was normal. I found that the listening was okay. Since listening is okay, that is to say, all the above efforts are in vain. The root cause of database connection failure is actually the instance problem.

Read the alert_orcl.log file (E: \ oracle \ product \ 10.2.0 \ admin \ orcl \ bdump \), turn to the last part, found some tns-12560 errors. According to some online statements, we finally assume that the Oracle database is broken...

This is a huge tragedy. I don't have a sys user, so I can't mount, mount, or open it. I don't know which part of the error has occurred. It may be nice to restart, but I don't have the sys user, so I don't have the operation permission.

I had to change my mind and decided to reinstall the database!

So I will use the data file for data recovery!

Online Search, many tutorials found that they are only about data file recovery, and I now have data files, control files, and log files, it should be easier. So I decided to test it on another computer first.

I rummaged out my notebook. The online tutorial says that you only need to create an instance with the same name, and the ports are the same. Then copy the above file (mainly the oradata folder) and overwrite it, restart the service. So try it!

But here I found a problem. The notebook is 11 GB of Oracle, which is really a pitfall. But I had to copy the above files to the corresponding folder.

Cmd enters the sys user (the current sys user exists), and starts 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, no error will be reported), and then alter database mount (this stage is the control file to find the data file, the error will be reported if it cannot be found), an error is reported, because the address of the data file stored in the control file is 10 Gb E: \ oracle \ product \ 10.2.0 \ oradata, rather than 11 GB app \... so we copied the data file to the address pointed to by the control file (all of them were pushed to this file --!), Start mount later. No problem!

Suddenly, the whole person is in a hurry! Is this a success?

Then, alter database is open! On the screen, some good information is displayed! Are you sure you want to succeed?

It's still naive. Ora-00704 and ora-39700. Continue searching and find that the data dictionary table reports an error due to version issues! Execute SQL scripts online to update the data dictionary. That's all!

Catupgrd. SQL, catalog. SQL, catproc. SQL, and utlrp. SQL are executed in the @ path in sqlplus of cmd. I found only three of the four scripts here, and the third one didn't, so they were executed in sequence.

Haha, a big wave of error scrolling !!! All cannot be created !!! I feel like I have to skip the building !!!

Calm down for a moment and feel that 10g and 11g are insurmountable gaps, so I decided to uninstall 11g and change it to 10g to continue the above operation.

After installing 10 Gb, overwrite oradata and continue executing it in sqlplus of cmd. Shutdown immediate, start nomount, and alter database mount. alter database open... An error is reported again... I went there and reported that the dbf file is 11 GB instead of 10 Gb! It turns out that dbf has been corrupted by 11 GB! Shame!

Copy the 10 Gb oradata folder again and continue to open...

Finally, it succeeded !!!

I got it for two days !!! Success !!!

Summary:

(1) ora-12514 only two errors, abnormal monitoring, or the instance is not started, you can troubleshoot in these two aspects, instead of blindly follow the method of the Internet to change listener. ora and re-listen, maybe the instance is not working;

(2) If you want to recover data, it is best to have all the files in the oradata folder;

(3) I have been tossing for two days. Maybe I will recover from restarting the database at the very beginning, but the loss of sys users makes all these difficulties;

(4) It may not be suitable for you to learn to analyze calmly and take appropriate measures based on the situations you encounter, instead of simply following the online solution;

(5) A large number of backups. Do not be lazy!

No.