如果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/