DataGuard bug in adding temporary data files

DataGuard bug in adding temporary data files

One environment is Oracle 10gR2, with one master node and two slave nodes. Because the 10 Gb slave database is not active, we will open the corresponding window time when there are some query requirements.

Developers need to make a big query. The data can only be full tables, and there is order by, which will inevitably consume a lot of temp space. In this case, it is better to make full use of the slave database, there is a standby database that has never been used at ordinary times. Today, this standby database is used to complete the query requirements.

However, after a while, the developer said that the query failed. Let me see why.

The logs provided by developers are:

10:48:05, --- exception: ---- StatementCallback; uncategorized SQLException for SQL [select c.cn as cn, c. uin as uin from test_bind c where enabled = 'y' group by c.cn, c. uin having count (c.cn)> 1]; SQL state [99999]; error code [25153]; ORA-25153: Temporary Tablespace is Empty

; Nested exception is java. SQL. SQLException: ORA-25153: Temporary Tablespace is Empty

It seems that this problem is quite embarrassing. The temporary tablespace is empty.

SQL> select file_name, bytes from dba_temp_files;

No rows

Why is the temporary tablespace in the standby database missing?

Check the history and find that the temporary tablespace is cleared after a log application a few days ago.

Tue Nov 17 17:48:23 CST 2015

Media Recovery Log/U01/app/oracle/admin/acctest/arch/1_21281_782846320.dbf

Recovery ing tempfile #3: '/U03/app/oracle/oradata/acctest/temp03.dbf'

Recovery ing tempfile #2: '/U03/app/oracle/oradata/acctest/temp02.dbf'

Recovery ing tempfile #1: '/U03/app/oracle/oradata/acctest/temp01.dbf'

Recovery dropped temporary tablespace 'temp'

Media Recovery Waiting for thread 1 sequence 21282

Because temporary tablespace is a secondary part of the database, the primary and standby databases can be different. Therefore, after simple analysis, we decided to manually add temporary data files.

At this time, check the temporary tablespace and find that it is already temp2.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

---------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

...

TEMP2

9 rows selected.

Since it is empty, add a temporary data file. The basic results are not solid, and the error message is misleading.

SQL> alter tablespace temp2 add datafile/U03/app/oracle/oradata/acctest/temp01.dbf 'size 32 GB;

Alter tablespace temp2 add datafile/U03/app/oracle/oradata/acctest/temp01.dbf 'size 32G

*

ERROR at line 1:

ORA-16000: database open for read-only accesskey

Modify datafile to tempfile to continue

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30 GB;

Alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G

*

ERROR at line 1:

ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'

ORA-27038: created file already exists

Additional information: 1

At this time, the system prompts that the file already exists. Well, the file exists, so I will reuse it.

Using the resue method, the result reports an error with the ORA-27086.

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;

Alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse

*

ERROR at line 1:

ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'

ORA-27086: unable to lock file-already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 19076

This error is confusing. Is there a temporary data file?

SQL> select file_name, bytes from dba_temp_files;

No rows

Check the file status in the file system. It seems that the timestamp is indeed updated, but this file is not in the data dictionary.

$ Ll temp *. dbf

Total 432600976

-Rw-r ----- 1 oracle oinstall 32212262912 Nov 20 temp01.dbf

-Rw-r ----- 1 oracle oinstall 21109940224 Jan 5 2015 temp02.dbf

-Rw-r ----- 1 oracle oinstall 21109940224 Jan 5 2015 temp03.dbf

This problem is still very strange. It can only be thought of as a bug. The result shows that such a bug exists, and all versions are completely consistent.

Bug 15944809-add tempfile at physical standby fails with ORA-1119, ORA-27086 (Doc ID 15944809.8)

There are two workaround for this problem. One is to restart the slave database.

Workaround

A shutdown/startup of the standby databse will clear the DBW0's

Stale file lock state, and then the new tempfile can be created.

Then I will try to add a new data file and keep the database.

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' size 10G;

Tablespace altered.

You can create one, and then resize it.

SQL> alter database tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' resize 30G;

Database altered.

At this time, let the developers complete the query task.

After the query is complete, you can restart the environment after the environment is recovered.

After the restart, add the temporary data file again.

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;

Tablespace altered.

SQL> select name, bytes from v $ tempfile;

NAME BYTES

------------------------------------------------------------

/U03/app/oracle/oradata/acctest/temp04.dbf 3.2212E + 10

/U03/app/oracle/oradata/acctest/temp01.dbf 3.2212E + 10

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby