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: