Dataguard环境添加tempfile

如果standby_file_management被设置成auto，在primary端添加数据文件会自动创建到standby端。

但是创建tempfile，并不会自动在standby端创建。因为在创建tempfile的时候，并没有redo。

 

1.primary端创建tempfile

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773



ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777

2.在standby端检测，并没有发现新建tempfile

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773

3.standby端创建tempfile

如果是adg环境，standby是read only模式：

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777

如果没有开启adg

--- cancel recovery:
recover managed standby database cancel;


-- Open database

alter database open readonly;

-- Add tempfile

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;

restart the db in mount stage:

shutdown immediate;

startup mount;

---- Start the recovery process:

alter database recovery managed standby database disconnect from session;

4.standby端查看

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777

　　

https://dbaclass.com/article/add-tempfile-primary-database-dataguard/