Dataguard环境添加tempfile

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

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

 

1.primary端创建tempfile

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

?

1 2 3 4 5 6

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模式：

?

1 2 3 4 5 6 7 8 9 10

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

--- 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端查看

?

1 2 3 4 5 6 7

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/