Overview of Temp tablespace and Temp File Management in DataGuard Environment

In Oracle tablespaces and data files, Temp tablespaces and files are special. The temporary segment (tempsegment) corresponding to the Temp tablespace is automatically managed by Oracle.

In Oracle tablespaces and data files, Temp tablespaces and files are special. Besides the temporary segment (Temp segment) corresponding to the temp tablespace, it is automatically managed by Oracle.

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;