[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 在线移动数据文件名的情况.