文檔課題：主庫添加temp文件，dg端不會同步增加temp文件的驗(yàn)證.

系統(tǒng)：centos7.9 64位

數(shù)據(jù)庫：oracle11.2.0.4 64位

環(huán)境：rac (雙節(jié)點(diǎn))+ dg

1、測試temp文件

主庫節(jié)點(diǎn)1操作：

SQL>col file_name for a50

SQL>select file_name,bytes/1024/1024,autoextensible,tablespace_name fromdba_temp_files

FILE_NAME BYTES/1024/1024 AUT TABLESPACE_NAME

----------------------------------------------------------------- --- ------------------------------

+DATA/orcl/tempfile/temp.262.1117373987 60 YES TEMP

SQL>alter tablespace temp add tempfile '+data' size 50m autoextend on;

Tablespacealtered.

SQL>select file_name,bytes/1024/1024,autoextensible,tablespace_name from dba_temp_files;

FILE_NAME BYTES/1024/1024 AUT TABLESPACE_NAME

----------------------------------------------------------------- --- ------------------------------

+DATA/orcl/tempfile/temp.262.1117373987 60 YES TEMP

+DATA/orcl/tempfile/temp.276.1117483829 50 YES TEMP

說明：主庫端已成功添加臨時文件temp.276.1117483829.

備庫操作：

SQL>select file_name,autoextensible,bytes/1024/1024,tablespace_name fromdba_temp_files;

FILE_NAME AUTBYTES/1024/1024 TABLESPACE_NAME

----------------------------------------------------- --------------- ------------------------------

/u01/app/oracle/oradata/temp.262.1117373987 YES 60 TEMP

小結(jié)：備庫端并沒有相應(yīng)的增加temp文件.

2、測試非temp文件

主庫節(jié)點(diǎn)1給users表空間添加數(shù)據(jù)文件.

SQL>select file_name,autoextensible,bytes/1024/1024,tablespace_name fromdba_data_files where tablespace_name='USERS';

FILE_NAME AUTBYTES/1024/1024 TABLESPACE_NAME

----------------------------------------------------- --------------- ------------------------------

/u01/app/oracle/oradata/users.264.1117373991 YES 5 USERS

SQL>alter tablespace users add datafile '+data' size 50m autoextend on;

Tablespacealtered.

SQL>select file_name,autoextensible,bytes/1024/1024,tablespace_name fromdba_data_files where tablespace_name='USERS';

FILE_NAME AUTBYTES/1024/1024 TABLESPACE_NAME

----------------------------------------------------- --------------- ------------------------------

/u01/app/oracle/oradata/users.264.1117373991 YES 5 USERS

/u01/app/oracle/oradata/users.277.1117483957 YES 50 USERS

備庫查詢

SQL>select file_name,autoextensible,bytes/1024/1024,tablespace_name fromdba_data_files where tablespace_name='USERS';

FILE_NAME AUTBYTES/1024/1024 TABLESPACE_NAME

----------------------------------------------------- --------------- ------------------------------

/u01/app/oracle/oradata/users.264.1117373991 YES 5 USERS

/u01/app/oracle/oradata/users.277.1117483957 YES 50 USERS

小結(jié)：備庫端相應(yīng)的增加users.277.1117483957文件.

3、備庫日志

2022-10-0717:02:22.300000 +08:00

Completed:alter database recover managed standby database using current logfiledisconnect from session

2022-10-0720:12:37.513000 +08:00

Recoverycreated file /u01/app/oracle/oradata/users.277.1117483957

Successfullyadded datafile 6 to media recovery

Datafile#6: '/u01/app/oracle/oradata/users.277.1117483957'

總結(jié)：主庫添加tempfile，不會產(chǎn)生redo，因此不會同步到dg端.

補(bǔ)充說明：主庫增加tempfile的大小，備庫也不會相應(yīng)的增加.

SQL>select file_name,bytes/1024/1024,autoextensible,tablespace_name fromdba_temp_files;

FILE_NAME BYTES/1024/1024AUT TABLESPACE_NAME

----------------------------------------------------------------- --- ------------------------------

+DATA/orcl/tempfile/temp.262.1117373987 60 YES TEMP

+DATA/orcl/tempfile/temp.276.1117483829 50 YES TEMP

SQL>select file_name,bytes/1024/1024,autoextensible,tablespace_name fromdba_data_files where tablespace_name='USERS';

FILE_NAME BYTES/1024/1024 AUT TABLESPACE_NAME

----------------------------------------------------------------- --- ------------------------------

+DATA/orcl/datafile/users.264.1117373991 5 YES USERS

+DATA/orcl/datafile/users.277.1117483957 50 YES USERS

SQL>alter database tempfile '+DATA/orcl/tempfile/temp.262.1117373987' resize 100m;

Databasealtered.

SQL>select file_name,bytes/1024/1024,autoextensible,tablespace_name fromdba_temp_files;

FILE_NAME BYTES/1024/1024 AUT TABLESPACE_NAME

----------------------------------------------------------------- --- ------------------------------

+DATA/orcl/tempfile/temp.262.1117373987 100 YES TEMP

+DATA/orcl/tempfile/temp.276.1117483829 50 YES TEMP

備庫增加tempfile：

SQL>select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

------------------------------------

PHYSICALSTANDBY READ ONLY WITH APPLY

SQL>alter tablespace temp add tempfile '/u01/app/oracle/oradata/temp01.dbf' size 5mautoextend on;

Tablespacealtered.

SQL>select file_name from dba_temp_files;

FILE_NAME

--------------------------------------------------

/u01/app/oracle/oradata/temp.262.1117373987

/u01/app/oracle/oradata/temp01.dbf