[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 線上移動資料檔名的情況.

[20181102]資料檔案改名與awr.txt

相關文章