Oracle Exception ora-00210,ora-00202 Fault handling

Just work in the morning, as usual, supporting the computer support, open the computer, there is a war film inside, back to the position, set up machine guns, ready to meet the enemy of the ritual sense.

Everything is so familiar, in the start test environment, the development of sound broke the office of quiet: "The company XXX system database is not hanging!!!" ”

After listening to the habit of a cold sweat! It's embarrassing!

Quickly open the relevant tools, connected to the server to confirm the situation:

[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 09:11:29 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select open_mode from v$database;  select open_mode from v$database*ERROR at line 1:ORA-00210: cannot open the specified control fileORA-00202: control file:‘/home/oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl‘ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

As expected, the database status is not normal, the prompt cannot open the control file, cannot find the related file path.

DBA is like this, ordinary system normal time, people tend to ignore your existence, but once the system problems, and like to act as a firefighter!!!

Back to the point, the normal process, we need to look at the relevant alarm log, to find the exception information:

Check the ALTER log first:
General Alarm Log storage path can be queried by the following command:

Sql> Show Parameter Background_dump_dest

Pwd
/home/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/alert---Note: Note here the sensitive information, referring to the time, to their own actual circumstances prevail.

<msg time= ' 2017-07-12t18:06:52.299+08:00 ' org_id= ' xxxx ' comp_id= ' xxxx ' type= ' UNKNOWN ' level= ' from ' host_id= ' xxxxDb ' Host_addr= ' Here is the server IP address ' > <txt> nt OS err code:0 </txt></msg><msg time= ' 2017-07-12t18:06:52.299 +08:00 ' org_id= ' xxxx ' comp_id= ' xxxx ' type= ' UNKNOWN ' level= ' + ' host_id= ' xxxxdb ' host_addr= ' Here is the server IP address ' > <txt > Client Address: (address= (PROTOCOL=TCP) (IP address of host= remote client, for example: 192.168.1.111) (port=58310)) </txt></msg ><msg time= ' 2017-07-12t18:06:59.902+08:00 ' org_id= ' xxxx ' comp_id= ' xxxx ' client_id= ' type= ' UNKNOWN ' level= ' 16 ' Host_id= ' xxxxdb ' host_addr= ' Here is the server IP address ' module= ' mmon_slave ' pid= ' 26750 ' > <txt>errors in file/home/oracle/ U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_M000_26750.TRC:ORA-00210: Unable to open the specified control file ORA-00202: Control file: '/home/ Oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl ' ORA-27041: Cannot open file linux-x86_64 error:2: No such file or Directoryadditional Information:3 </txt></msg>

From the above file, it is possible to see the "control file could not find the relevant error message" When it was 18:06 on July 12.
Interestingly, inside there is a client connection to see the IP address, this time also a little excited: Finally found you, is not someone in a malicious attack, and then a look at the IP address, incredibly is the company's IP,
Embarrassed, their own ah, so asked the development, yesterday 6 o'clock in the afternoon when, there is no one to the database operation, get the reply is, no other operation, just a simple query and UPDATE operation!!!

Hey, don't say!!! Thought or, seize the trouble!

Then view the TRC file according to the information mentioned in the log:

/home/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_22241.trc:ORA-00210: 无法打开指定的控制文件ORA-00202: 控制文件: ‘‘/home/oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl‘‘ORA-27041: 无法打开文件

Probably know the problem after the imagination, the first need to resume business as soon as possible, and then look for the cause of the failure!
According to the log error message, you can determine the problem of control files, then, we first look at the database inside the control files are:

SQL> show parameter control_fileNAME     TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time     integer 7control_files     string /home/oracle/u01/app/oracle/oradata/orcl/control01.ctl,                                      /home/oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Found a problem, before remember correctly, the database of two control files are stored in the same directory, namely: ... ORADATA/ORCL, how the second control file placed in the ... fast_recovery_area/orcl directory?
Oddly enough, the database did not turn on the flashback before, why the control file storage path in this place has changed?

Go ahead and check to see if the current environment is turning on the flashback,

SQL> select flashback_on from v$database;select flashback_on from v$database                         *ERROR at line 1:ORA-00210: cannot open the specified control fileORA-00202: control file:‘/home/oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl‘ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

It's over, I can't see!!!

Try to check the flashback related parameters?

Oracle Exception ora-00210,ora-00202 Fault handling