[20151201]Manual Remastering of Objects

[20151201]Manual Remastering of Objects.txt

--rac下物件存在master問題，如果一個例項下經常訪問這個物件，oracle會讓這個例項重新remaster 這個物件。
--簡單的測試如何實現：

1.測試環境建立：

SCOTT@xxxx1> @ &r/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@xxxx1> select * from dba_objects where owner='SCOTT' and object_name='EMP';
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- --------------
SCOTT  EMP                                  87108          87108 TABLE               2013-08-24 12:04:21 2013-08-24 12:04:21 2013-08-24:12:04:21 VALID   N N N          1

SCOTT@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=87108;
no rows selected

--現在沒有人訪問過這張表。
--執行如下語句多次 select * from scott.emp;

SCOTT@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=87108;
no rows selected
--估計要等1會。
--實際上我透過指令碼執行了上千次，透過那個檢視來觀察現在是那個例項remaster呢？

2.使用oradebug執行Manual Remastering of Objects。

SYS@xxxx1> oradebug setmypid
Statement processed.
SYS@xxxx1> oradebug lkdebug -m pkey 87108
Statement processed.

--注意這裡使用的是data_object_id.

SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=87108;
   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         1          0          87108 Affinity                 0           32767            1
         2          0          87108 Affinity                 0           32767            1

--CURRENT_MASTER=0 表示是例項1.先前是PREVIOUS_MASTER=32767，表示沒有master。

3.現在手工切到例項2看看：

SYS@xxxx2>oradebug setmypid
SYS@xxxx2>oradebug lkdebug -m pkey 87108
SYS@xxxx2> select * from GV$GCSPFMASTER_INFO where data_object_id=87108;

   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         2          0          87108 Affinity                 1               0            2
         1          0          87108 Affinity                 1               0            2

--可以發現CURRENT_MASTER=1,表示是例項2.並且REMASTER_CNT從1=>2.

4.補充1點:
SYS@xxxx1> ALTER TABLE SCOTT.T_SQL_PROFILE MOVE TABLESPACE USERS;
Table altered.

SYS@xxxx1> select * from dba_objects where owner='SCOTT' and object_name='T_SQL_PROFILE';
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT  T_SQL_PROFILE                       112023         112029 TABLE               2015-12-01 08:59:41 2015-12-01 10:07:08 2015-12-01:08:59:41 VALID   N N N          1

--注意現在OBJECT_ID,DATA_OBJECT_ID不等。OBJECT_ID=112023, DATA_OBJECT_ID=112029.

SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=112023;
   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         1          0         112023 Affinity                 0               1            5
         2          0         112023 Affinity                 0               1            5

SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=112029;
no rows selected
--以上是我前面做的多次。

SYS@xxxx1> oradebug lkdebug -m pkey 112029
Statement processed.
SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=112029;
   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         1          0         112029 Affinity             32767           32767            0
         2          0         112029 Affinity             32767           32767            0

--再重複1次：
SYS@xxxx1> oradebug lkdebug -m pkey 112029
Statement processed.
SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where data_object_id=112029;
   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         1          0         112029 Affinity                 0           32767            1
         2          0         112029 Affinity                 0           32767            1

--再在例項2執行：

SYS@xxxx2> oradebug lkdebug -m pkey 112029
Statement processed.

SYS@xxxx2> select * from GV$GCSPFMASTER_INFO where data_object_id=112029;
   INST_ID    FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
         2          0         112029 Affinity                 1               0            2
         1          0         112029 Affinity                 1               0            2