[20181102]数据文件改名与awr.txt

来源：这里教程网时间：2026-03-03 12:12:25 作者：

[20181102]数据文件改名与awr.txt --//当数据文件改名时,awr报表可能并不真实反应对应数据文件,通过例子说明问题. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> select * from v$dbfile; FILE# NAME ------------ ---------------------------------------- 4 /mnt/ramdisk/book/users01.dbf 3 /mnt/ramdisk/book/undotbs01.dbf 2 /mnt/ramdisk/book/sysaux01.dbf 1 /mnt/ramdisk/book/system01.dbf 5 /mnt/ramdisk/book/example01.dbf 6 /mnt/ramdisk/book/tea01.dbf 6 rows selected. --//file#=6,name=/mnt/ramdisk/book/tea01.dbf SCOTT@book> select * from DBA_HIST_DATAFILE; DBID FILE# CREATION_CHANGE# FILENAME TS# TSNAME BLOCK_SIZE ------------ ------------ ---------------- ------------------------------- --- -------- ---------- 1337401710 1 7 /mnt/ramdisk/book/system01.dbf 0 SYSTEM 8192 1337401710 2 1834 /mnt/ramdisk/book/sysaux01.dbf 1 SYSAUX 8192 1337401710 3 923328 /mnt/ramdisk/book/undotbs01.dbf 2 UNDOTBS1 8192 1337401710 4 16143 /mnt/ramdisk/book/users01.dbf 4 USERS 8192 1337401710 5 952916 /mnt/ramdisk/book/example01.dbf 6 EXAMPLE 8192 1337401710 6 13276257767 /mnt/ramdisk/book/tea01.dbf 7 TEA 8192 6 rows selected. 2.改名数据文件看看. SCOTT@book> alter database datafile 6 offline ; Database altered. SCOTT@book> recover datafile 6; Media recovery complete. $ mv /mnt/ramdisk/book/tea01.dbf /mnt/ramdisk/book/tea02.dbf SCOTT@book> alter database rename file '/mnt/ramdisk/book/tea01.dbf' to '/mnt/ramdisk/book/tea02.dbf'; Database altered. SCOTT@book> alter database datafile 6 online ; Database altered. --//改名成功.并且online. 3.建立awr的snapshot: SCOTT@book> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SCOTT@book> select * from DBA_HIST_DATAFILE; DBID FILE# CREATION_CHANGE# FILENAME TS# TSNAME BLOCK_SIZE ------------ ------------ ---------------- ------------------------------- --- -------- ---------- 1337401710 1 7 /mnt/ramdisk/book/system01.dbf 0 SYSTEM 8192 1337401710 2 1834 /mnt/ramdisk/book/sysaux01.dbf 1 SYSAUX 8192 1337401710 3 923328 /mnt/ramdisk/book/undotbs01.dbf 2 UNDOTBS1 8192 1337401710 4 16143 /mnt/ramdisk/book/users01.dbf 4 USERS 8192 1337401710 5 952916 /mnt/ramdisk/book/example01.dbf 6 EXAMPLE 8192 1337401710 6 13276257767 /mnt/ramdisk/book/tea01.dbf 7 TEA 8192 6 rows selected. --//依旧是/mnt/ramdisk/book/tea01.dbf. SCOTT@book> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SCOTT@book> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 and snap_id>=1230; SNAP_ID FILE# FILENAME ------------ ------------ ---------------------------------------- 1230 6 /mnt/ramdisk/book/tea01.dbf 1231 6 /mnt/ramdisk/book/tea01.dbf 1232 6 /mnt/ramdisk/book/tea01.dbf 1233 6 /mnt/ramdisk/book/tea01.dbf --//你可以发现file#=6,在awr报表中看到的还是/mnt/ramdisk/book/tea01.dbf. 4.建立awr报表看看: --//业务太少,看不到对该表空间的IO操作. SCOTT@book> create table tt tablespace tea as select * from all_objects; Table created. SCOTT@book> update tt set owner=lower(owner), OBJECT_NAME=lower(OBJECT_NAME); 84761 rows updated. SCOTT@book> commit ; Commit complete. SCOTT@book> update tt set owner=lower(owner), OBJECT_NAME=lower(OBJECT_NAME); 84761 rows updated. SCOTT@book> commit ; Commit complete. SCOTT@book> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. --//再次建立awr报表: File IO Stats DB/Inst: BOOK/book Snaps: 1233-1234 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk Writes Buffer Av Buf Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Writes avg/s Waits Wt(ms) ------- ------- ------- ------- ------- ------- ------- ------- -------- ------- SYSAUX /mnt/ramdisk/book/sysaux01.dbf 56 0 0.0 1.4 0 0.0 345 1 0 0.0 SYSTEM /mnt/ramdisk/book/system01.dbf 382 1 0.0 1.0 1 0.0 11 0 1 0.0 TEA /mnt/ramdisk/book/tea01.dbf 114 0 0.0 10.6 0 0.0 38 0 0 0.0 UNDOTBS1 /mnt/ramdisk/book/undotbs01.dbf 0 0 N/A N/A 0 N/A 25 0 0 0.0 ------------------------------------------------------ --//可以发现记录的数据文件还是/mnt/ramdisk/book/tea01.dbf. 5.问题原因: --//问题在于oracle并不是每次生成awr snapshot时更新DBA_HIST_DATAFILE视图信息. SCOTT@book> select * from DBA_HIST_DATAFILE where file#=6; DBID FILE# CREATION_CHANGE# FILENAME TS# TSNAME BLOCK_SIZE ------------ ------------ ---------------- --------------------------- --- ------ ---------- 1337401710 6 13276257767 /mnt/ramdisk/book/tea01.dbf 7 TEA 8192 --//如果查看包DBMS_WORKLOAD_REPOSITORY,可以发现如下信息: -- update_datafile_info() -- This routine updates WRH$_DATAFILE rows for the datafile name and -- tablespace name. Whenever this procedure runs, it will update these -- values with the current information in the database. -- -- This routine is useful when a datafile/tablespace has been moved or -- renamed. As this change is generally not always captured in the next -- snapshot in AWR. This change will be captured at max after some -- (generally 50) snapshots. So the AWR and AWR report may be wrong with -- respect to data file name or tablespace name for that duration. -- -- To fix this problem, we can use this procedure to sync the table -- WRH$_DATAFILE with the current information in database. -- PROCEDURE update_datafile_info; --//也就是一般在50个snapshot后,才会自动更新.可以手工调用这个包更新反应真实的情况: SCOTT@book> exec dbms_workload_repository.update_datafile_info; PL/SQL procedure successfully completed. SCOTT@book> select * from DBA_HIST_DATAFILE where file#=6; DBID FILE# CREATION_CHANGE# FILENAME TS# TSNAME BLOCK_SIZE ------------ ------------ ---------------- --------------------------- --- ------ ---------- 1337401710 6 13276257767 /mnt/ramdisk/book/tea02.dbf 7 TEA 8192 SCOTT@book> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 and snap_id>=1230; SNAP_ID FILE# FILENAME ------------ ------------ ---------------------------------------- 1230 6 /mnt/ramdisk/book/tea02.dbf 1231 6 /mnt/ramdisk/book/tea02.dbf 1232 6 /mnt/ramdisk/book/tea02.dbf 1233 6 /mnt/ramdisk/book/tea02.dbf 1234 6 /mnt/ramdisk/book/tea02.dbf 1235 6 /mnt/ramdisk/book/tea02.dbf 1236 6 /mnt/ramdisk/book/tea02.dbf 7 rows selected. --//OK,现在更新过来的.这样的情况也发生在12c 在线移动数据文件名的情况.