物理DG临时表空间管理

#主库
sys@lycdb_p>col file_name for a80;
sys@lycdb_p>select tablespace_name,file_name from dba_temp_files where tablespace_name='TEMP';
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp01.dbf
#01.为主库临时表空间添加一个tempfile
sys@lycdb_p>alter tablespace temp add tempfile '/oracle/app/oracle/oradata/lycdb/orcl/temp02.dbf' size 10m autoextend off;
sys@lycdb_p>select tablespace_name,file_name from dba_temp_files where tablespace_name='TEMP';
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp01.dbf
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp02.dbf
sys@lycdb_p>alter system switch logfile;
#02.备库并未自动添加tempfile
sys@lycdb_s>select tablespace_name,file_name from dba_temp_files where tablespace_name='TEMP';
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp01.dbf
#03.需手工在备库添加tempfile
sys@lycdb_s>alter tablespace temp add tempfile '/oracle/app/oracle/oradata/lycdb/orcl/temp02.dbf' size 10m autoextend off;

测试案例2：主库创建一个TEMP临时表空间

#01.为主库新创建一个TEMP临时表空间
sys@lycdb_p>create temporary tablespace TEMP2 tempfile '/oracle/app/oracle/oradata/lycdb/orcl/temp201.dbf' size 10M autoextend off;
sys@lycdb_p>select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp01.dbf
TEMP                           /oracle/app/oracle/oradata/lycdb/orcl/temp02.dbf
TEMP2                          /oracle/app/oracle/oradata/lycdb/orcl/temp201.dbf
#02.备库会创建TEMP表空间，但不会创建tempfile
sys@lycdb_s>select name from v$tablespace where name='TEMP2';
NAME
------------------------------
TEMP2
sys@lycdb_s>select file_name from dba_temp_files where tablespace_name='TEMP2';
no rows selected
#03.尝试将PDB重启，后台日志会有相关的信息提示我们TEMP2表空间没有tempfile
sys@lycdb_s>alter pluggable database orcl close immediate;
Pluggable database altered.
sys@lycdb_s>alter pluggable database orcl open;
Pluggable database altered.
#相关日志信息如下：
2020-10-20T22:28:51.165143+08:00
ORCL(3):Endian type of dictionary set to little
ORCL(3):Undo initialization finished serial:0 start:37237865 end:37237865 diff:0 ms (0.0 seconds)
ORCL(3):Database Characterset for ORCL is AL32UTF8
ORCL(3):*********************************************************************
ORCL(3):WARNING: The following temporary tablespaces in container(ORCL)
ORCL(3):         contain no files.
ORCL(3):         This condition can occur when a backup controlfile has
ORCL(3):         been restored.  It may be necessary to add files to these
ORCL(3):         tablespaces.  That can be done using the SQL statement:
ORCL(3): 
ORCL(3):         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
ORCL(3): 
ORCL(3):         Alternatively, if these temporary tablespaces are no longer
ORCL(3):         needed, then they can be dropped.
ORCL(3):           Empty temporary tablespace: TEMP2
ORCL(3):*********************************************************************
ORCL(3):Opening pdb with no Resource Manager plan active
Pluggable database ORCL opened read only
Completed: alter pluggable database orcl open
#04.手工添加回去
sys@lycdb_s>alter tablespace TEMP2 add tempfile '/oracle/app/oracle/oradata/lycdb/orcl/temp201.dbf' size 10M autoextend off;