Overview of Temp tablespace and Temp File Management in Data Guard

In Oracle tablespaces and data files, Temp tablespaces and files are special. In addition to the temporary segment (Temp segment) corresponding to the temp tablespace, it is automatically managed by Oracle. The sparse file feature is also an important aspect to note.
 
In general, we create a data file. Even if we use the OMF feature, we also need to specify the initial file size. After a data file is created, the disk space is clearly occupied. We can see the space at the operating system level. However, for temporary tablespace files, the size of the newly created file may only be visible at the operating system level, but it does not occupy space. We call this feature a "sparse file ".
 
At the implementation level, sparse files mean fewer redo logs are generated. So what is the difference between the features of Temp files and common files in the DG Environment? Next we will prove it through a series of experiments.

-------------------------------------- Split line --------------------------------------

References:

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

-------------------------------------- Split line --------------------------------------

1. experiment environment Introduction

We perform a test in Dataguard in Oracle 11gR2 environment. The specific version is 11.2.0.4. The current Primary condition is as follows:

-- Primary name ora11g

SQL> select DATABASE_ROLE, open_mode from v $ database;

DATABASE_ROLE OPEN_MODE

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

PRIMARY READ WRITE

The current database has only one temporary file, corresponding to the tablespace TEMP.

 

SQL> select file_name, tablespace_name from dba_temp_files;

 

FILE_NAME TABLESPACE_NAME

------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11G/datafile/o1_mf_temp_9mnjxpk4 _. tmp TEMP

 

For javasuard, the most important file management parameter is standby_file_management. If the value is AUTO, the data file can be created simultaneously.

 

SQL> show parameter standby_file

 

NAME TYPE VALUE

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

Standby_file_management string AUTO

 

Standby is in the mount state. File Creation management.

 

 

SQL> select DATABASE_ROLE, open_mode from v $ database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

 

 

SQL> select name, file # from v $ tempfile;

 

Name file #
 
------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11GSY/datafile/o1_mf_temp_9pcqbdd6 _. tmp 1
 
 

SQL> show parameter standby_file

NAME TYPE VALUE

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

Standby_file_management string AUTO

 

2. Temporary tablespace operations on the Primary end

 

First, perform table space operations on the Primary side.

 

(Primary)

SQL> alter tablespace temp add tempfile size 100 m autoextend off;

Tablespace altered

 

SQL> select file_name, tablespace_name from dba_temp_files;

 

FILE_NAME TABLESPACE_NAME

------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11G/datafile/o1_mf_temp_9mnjxpk4 _. tmp TEMP

/U01/app/oradata/ORA11G/datafile/o1_mf_temp_9pm3ct62 _. tmp TEMP

 

SQL> alter system switch logfile;

System altered

After switching, the normal redo log information should have been passed to the standby end. Start the redo apply process on the Standby side to check whether temporary files are created.

SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered

 

SQL> select name, file # from v $ tempfile;

 

Name file #
 
------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11GSY/datafile/o1_mf_temp_9pcqbdd6 _. tmp 1
 
After the redo apply is started, the standby end still has only one temporary file, that is, the temporary file is not transferred together. At this point, the structure of the Primary and Standby files is different.
 
Can I add files directly on the standby end? In the mount status, it is rejected.

SQL> alter tablespace temp add tempfile size 100 m autoextend off;

Altertablespace temp add tempfile size 100 m autoextend off

ORA-01109: the database is not open

If we create a temporary tablespace corresponding to the data file at the Primary end, will it be created together?

(Master database)

SQL> create temporary tablespace temp1 tempfile size 10 m autoextend off

2 extent management local uniform size 1 m;

 

Tablespace created

 

SQL> select file_name, tablespace_name from dba_temp_files;

 

FILE_NAME TABLESPACE_NAME

------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11G/datafile/o1_mf_temp_9mnjxpk4 _. tmp TEMP

/U01/app/oradata/ORA11G/datafile/o1_mf_temp_9pm3ct62 _. tmp TEMP

/U01/app/oradata/ORA11G/datafile/o1_mf_temp1_9pm3mv8b _. tmp TEMP1

At this time, observe the Standby end, And the TEMP1 tablespace is not connected to the standby end.

SQL> select name, file # from v $ tempfile;

Name file #
 
------------------------------------------------------------------------------------------
 
/U01/app/oradata/ORA11GSY/datafile/o1_mf_temp_9pcqbdd6 _. tmp 1
 
Conclusion: When the Standby end is in the mount state (Read Only is the same), the temporary tablespace creation and maintenance and temporary file creation operations involved in the Primary end are not transmitted to the standby end. The standby end in the mount status cannot create files manually.

For more details, please continue to read the highlights on the next page:

1 2 Next Page