"Original" Oracle ORA-01157 ORA-01110 DataGuard Standby temp Table space error

Brief: When querying database data, prompt temporary table space exception, error ORA-01157 ORA-01110, after the data file processing, has resolved this fault.

Environment: Oracle 11g RAC for Linux 6, library for Dataguard

1. Query the data times wrong, as follows:

ERROR:
Ora-01157:cannot identify/lock data file 226-see DBWR trace file
Ora-01110:data file 226: ' +DG_DATA02/RACDB/BLSP_TEMP.DBF '

2. Check Dataguard conversion Parameters

Sql> Show Parameter Db_file_name_convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_file_name_convert string

--Output information has been adjusted display format

+dg_system/racdb /datafile, +dg_system/ RACDBDG /datafile,
+dg_system/racdb ,           +dg_system/RACDBDG ,
+dg_data01/racdb /datafile, +dg_ddata_01/RACDBDG /datafile,
+ Dg_data02/racdb /datafile, +dg_ddata_02/RACDBDG /datafile,
+dg_system/racdb /tempfile, +dg_system/RACDBDG /tempfile,
+dg_data01/racdb /tempfile, +dg_ Ddata_01/RACDBDG /tempfile,
+dg_data02/ RACDB /tempfile, +dg_ddata_02/RACDBDG /tempfile

The conversion parameter format was found to be correct, but the path to the temporary data file was not set

3. Re-query ASM Disk Group, also did not have an error in this temporary data file

4. Workaround:

A. Tuning database parameters

B. Re-creating the data file into an ASM disk group

5. Adjust the conversion parameters, in the main library to increase the temporary table space conversion path, in the repository of the path and the main library, the parameter is a static parameter, need to restart the database, the database is a production library, temporarily do not modify.

The following examples of modifications are provided:

Main Library:

。。。

' +dg_data02/racdb/', ' +dg_data02/racdbdg/'

。。。

Standby Library:

。。。

' +dg_data02/racdbdg/', ' +dg_data02/racdb/'

。。。

6. Adjust the data file path

6.1. Querying data file information in the main library, full path and file size

Sql> Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '% blsp_temp% ';

file_id file_name MB STATUS tablespace_name
---------- ------------------------------- ---------- ------- -----------------
+DG_DATA02/RACDB/BLSP_TEMP.DBF 10240 ONLINE blsp_temp

Sql>

6.2. Remove the Repository log application, modify the file management mode for manual management

ALTER DATABASE recover managed standby database cancel;
Alter system set standby_file_management=manual sid= ' * ';

6.3. Rename the data file to the ASM disk path of the repository, the error is as follows:

sql> ALTER DATABASE rename file ' +dg_data02/racdb/blsp_temp.dbf ' to ' +dg_ddata_02/racdbdg/datafile/blsp_temp.dbf ';
Alter DATABASE rename file ' +dg_data02/racdb/blsp_temp.dbf ' to ' +dg_ddata_02/racdbdg/datafile/blsp_temp.dbf '
*
ERROR at line 1:
Ora-01511:error in renaming log/data files
Ora-01141:error renaming data file 226-new file ' +dg_ddata_02/racdbdg/datafile/blsp_temp.dbf ' not found
Ora-01110:data file 226: ' +DG_DATA02/RACDB/BLSP_TEMP.DBF '
Ora-17503:ksfdopn:2 Failed to open file +dg_ddata_02/racdbdg/datafile/blsp_temp.dbf
Ora-15173:entry ' blsp_temp.dbf ' does not exist in directory ' DataFile '


Sql>

6.4. Add a new data file to the temp table space to successfully

Sql> alter tablespace blsp_temp add tempfile ' +dg_ddata_02/racdbdg/datafile/blsp_temp02.dbf ' size 10240M;

Tablespace altered.

Sql>

Query temporary table space, or error

Sql> Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '% blsp_temp% ';
Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '%blsp_ temp% '
*
ERROR at line 1:
Ora-01157:cannot identify/lock data file 226-see DBWR trace file
Ora-01110:data file 226: ' +DG_DATA02/RACDB/BLSP_TEMP.DBF '


Sql>

Delete the error data file, successfully

sql> alter tablespace blsp_temp drop tempfile ' +dg_data02/racdb/blsp_temp.dbf ';

Tablespace altered.

Sql>

Querying table space information, normal

Sql> Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '% blsp_temp% ';

file_id file_name MB STATUS tablespace_name
---------- ------------------------------------------------------- ---------- ------- ----------------------------- -
+DG_DDATA_02/RACDBDG/DATAFILE/BLSP_TEMP02.DBF 10240 ONLINE blsp_temp

Sql>

6.5. Add back the original error data file name

Sql> alter tablespace blsp_temp add tempfile ' +dg_ddata_02/racdbdg/datafile/blsp_temp.dbf ' size 10240M;

Tablespace altered.

Sql> Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '% blsp_temp% ';

file_id file_name MB STATUS tablespace_name
---------- ------------------------------------------------------- ---------- ------- ----------------------------- -
+DG_DDATA_02/RACDBDG/DATAFILE/BLSP_TEMP.DBF 10240 ONLINE blsp_temp
+DG_DDATA_02/RACDBDG/DATAFILE/BLSP_TEMP02.DBF 10240 ONLINE blsp_temp

Sql>

6.6. Delete the newly added data file (BLSP_TEMP02.DBF)

sql> alter tablespace blsp_temp drop tempfile ' +dg_ddata_02/racdbdg/datafile/blsp_temp02.dbf ';

Tablespace altered.

Sql>
Sql> Select file_id,file_name,bytes/1024/1024 mb,status,tablespace_name from Dba_temp_files where file_name like '% blsp_temp% ';

file_id file_name MB STATUS tablespace_name
---------- ------------------------------------------------------- ---------- ------- ----------------------------- -
+DG_DDATA_02/RACDBDG/DATAFILE/BLSP_TEMP.DBF 10240 ONLINE blsp_temp

Sql>

6.7. Set the file management mode to automatic and enable the log app

Alter system set Standby_file_management=auto sid= ' * ';

ALTER DATABASE recover managed standby database using current logfile disconnect;

"Original" Oracle ORA-01157 ORA-01110 DataGuard Standby temp Table space error