Shulou(Shulou.com)05/31 Report--

Editor to share with you about the oracle dg library data file creation failure ORA-01111 how to do, I hope you will gain something after reading this article, let's discuss it together!

The specific error reports are as follows

SQL > alter database open read only

Alter database open read only

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01157: cannot identify/lock data file 24-see DBWR trace file

ORA-01111: name for data file 24 is unknown-rename to correct file

ORA-01110: data file 24:

'/ data/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00024'

Just added a data file to the main library:

SQL > alter tablespace MODEL_BH add datafile'/ data2/oradata/datafile/bapp/MODEL_BH010.dbf' size 31g

Then check and find that the data file No. 24 is the file you just added:

SQL > set line 1000

SQL > set pagesize 1000

SQL > col name for A60

SQL > col status for A20

SQL > select FILE#,name from v$datafile where file#=24

FILE# NAME

24 / data2/oradata/datafile/bapp/MODEL_BH010.dbf

That is to say, the file that came out of the library was not synchronized to the slave library, and it occurred to me that today the directory of this file was placed on a new mount point / data2 (due to insufficient space, the newly added one is on the point). Broken because the db_file_name_convert file of the slave library does not have a path to configure the subpoena / data2/oradata/datafile/bapp/, as shown below:

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string / data/app/oracle/oradata/dataf

Ile/bapp/, / data/u01/app/dataf

Ile/

Then the datagurd I build generally configures the parameter STANDBY_FILE_MANAGEMENT of the slave database to auto, as shown below:

SQL > show parameter STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE

-

Standby_file_management string AUTO

1) in the case of STANDBY_FILE_MANAGEMENT=AUTO, if the db_file_name_convert parameter of the slave database is set correctly, the data file created by the master database will be automatically created when the log is applied to the backup side, and the slave database will create the corresponding data file in the slave database according to the convert parameter.

2) in the case of STANDBY_FILE_MANAGEMENT=AUTO, but if the master library is created in a data file in another path, that is, if the db_file_name_convert parameter of the slave library is not set correctly (as I mentioned earlier, the path under / data2 of the master library is not set to the corresponding conversion path, then the slave library will be created under $ORACLE_HOME/dbs (note that only the file information that starts with UNNAMED is in the control file). But it won't really be created, and if the primary library is drop, the standby library will also drop.

My case is STANDBY_FILE_MANAGEMENT=AUTO, and then the db_file_name_convert parameter of the slave library is not set correctly

When viewing a file with a status of recover from the library, the slave library was not created successfully

SQL > select file#,name, status from v$datafile where status='RECOVER'

FILE# NAME status

--

24 / data/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00024 RECOVER

The repair process is shown below:

1. Modifying the parameter db_file_name_convert from the library

Add the conversion path of / data2/oradata/datafile/bapp/ as shown below

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string / data/app/oracle/oradata/dataf

Ile/bapp/, / data/u01/app/dataf

Ile/, / data2/oradata/datafile/

Bapp/, / data/u01/app/datafile/

two。 Restart the slave library

SQL > shu immediate

SQL > startup mount

3. Modify the parameter standby_file_management=manual of the slave library

SQL > alter system set standby_file_management='manual'

System altered.

Note: standby_file_management is manual, so when files are added to the master library, the slave library needs to be created manually, and if standby_file_management=auto, the standby library is not allowed to create files by hand!

4. Create that file on the repository, as shown below!

SQL > alter database create datafile'/ data/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00024' as'/ data/u01/app/datafile/MODEL_BH010.dbf'

At this time, I looked at this document No. 24 and found that the name had changed back.

SQL > select name, file# from v$datafile where file#=24

NAME

FILE#

-

/ data/u01/app/datafile/MODEL_BH010.dbf

twenty-four

But at this time, check the status of recover:

SQL > select file#,name, status from v$datafile where status='RECOVER'

FILE# NAME status

--

24 / data/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00024 RECOVER

Need to open the mrp process, apply the log, and restore the slave library! Be careful not to read only open first!

Alter database recover managed standby database using current logfile disconnect from session

Check this file again and find that it is no longer in the recover state, but in the online state. This is when the recovery is really successful!

SQL > select name, status from v$datafile where file#=24

NAME STATUS

/ data/u01/app/datafile/MODEL_BH010.dbf ONLINE

7. Open the database in read only mode

You need to close the mrp process before you can open the data in read only mode, otherwise an error will be reported!

SQL > alter database open read only

Alter database open read only

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in

Progress

Close the mrp process:

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database altered.

Then open the database.

SQL > alter database open read only

Database altered.

Start the mrp process again

Alter database recover managed standby database using current logfile disconnect from session

8. Modify the parameter standby_file_management=auto of the slave library

SQL > alter system set standby_file_management='auto'

Data synchronization has been verified to be normal.

9. Finally, you'd better restart the standby library! Then don't forget to start the mrp process!

After reading this article, I believe you have a certain understanding of "oracle dg database data file creation failure ORA-01111". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.