【Oracle ERP Note 】Oralce Adadmin / Snapshot 跑不動?

Problem

Oracle ERP 在做 Snapshot 時，發現系統好像 Hang 住，經查 AD_SNAPSHOT_FILES 與 AD_SNAPSHOT_BUGFIXES 的欄位，其中 AD_SNAPSHOT_FILES 表中欄位 SNAPSHOT_ID 有負值。將其以下列指令刪除:

SQL> delete from applsys.ad_snapshot_files where snapshot_id=-1069;

然而，重新測試時，系統仍似乎 Hang 住不動如下:


        Maintain Current View Snapshot Information
    -------------------------------------------

   1.    Update Complete APPL_TOP

   2.    Update JAVA_TOP only

   3.    Update a PRODUCT_TOP

   4.    Return to previous Menu


Enter your choice [4] : 1

Trying to obtain a lock...


   Deleting Temp Global Snapshot files ...Start time:Tue Apr 28 2015 11:59:57

   Deleting Temp Global Snapshot Bugfixes ...Start time:Tue Apr 28 2015 13:19:11


   Updating Global Snapshot files ...Start time:Tue Apr 28 2015 13:19:11
   Total No. of records to be processed = 788340


Study

此 Snapshot 執行時過慢，或似乎有 Hang 住現象，在 Oracle Note. 1447463.1 R12: Adadmin Maintain Snapshot Takes a Long Time To Complete  有紀載，需上 Path 13424445 - ADADMIN - Mainatin Snapshot Information Hangs with Latch Contention

Solution

1. 先檢查 AD_SNAPSHOT_FILES 的表中欄位 SNAPSHOT_ID 皆為正值

SQL> select distinct(snapshot_id) from applsys.ad_snapshot_files;

2. 以 testmgr 登入，shutdown APP Tier
3. apply patch 13424445
4. 重新做 snapshot 測試



Maintain Current View Snapshot Information 
------------------------------------------- 

1. Update Complete APPL_TOP 

2. Update JAVA_TOP only 

3. Update a PRODUCT_TOP 

4. Return to previous Menu 







Enter your choice [4] : 1 

Trying to obtain a lock... 


Deleting Temp Global Snapshot files ...Start time:Tue Apr 28 2015 15:47:52 
Deleting Temp Global Snapshot Bugfixes ...Start time:Tue Apr 28 2015 15:47:52 


Updating Global Snapshot files ...Start time:Tue Apr 28 2015 15:47:53 
Total No. of records to be processed = 788340 

0% Complete. Done updating 2001 rows[Tue Apr 28 2015 15:47:56]. 
0% Complete. Done updating 4001 rows[Tue Apr 28 2015 15:47:58]. 
0% Complete. Done updating 6001 rows[Tue Apr 28 2015 15:47:59]. 
1% Complete. Done updating 8001 rows[Tue Apr 28 2015 15:48:00]. 
1% Complete. Done updating 10001 rows[Tue Apr 28 2015 15:48:01]. 
... 
99% Complete. Done updating 786001 rows[Tue Apr 28 2015 15:51:08]. 
99% Complete. Done updating 788001 rows[Tue Apr 28 2015 15:51:08]. 
100% Complete. Done updating 788340 rows[Tue Apr 28 2015 15:51:08]. 


Done Updating Snapshot files into global snapshot 



Updating Global Snapshot bugs ...Start time:Tue Apr 28 2015 15:51:11 
Total No. of records to be processed = 413202 

0% Complete. Done updating 2001 rows[Tue Apr 28 2015 15:51:12]. 
0% Complete. Done updating 4001 rows[Tue Apr 28 2015 15:51:12]. 
1% Complete. Done updating 6001 rows[Tue Apr 28 2015 15:51:12]. 
... 
99% Complete. Done updating 410001 rows[Tue Apr 28 2015 15:51:40]. 
99% Complete. Done updating 412001 rows[Tue Apr 28 2015 15:51:40]. 
100% Complete. Done updating 413202 rows[Tue Apr 28 2015 15:51:40]. 


Done Updating Snapshot Temp Table 



Deleting Current Global Snapshot files ...Start time:Tue Apr 28 2015 15:51:40 
Deleting Current Global Snapshot Bugfixes ...Start time:Tue Apr 28 2015 15:52:09 
Updating Temp Global Snapshot Files ...Start time:Tue Apr 28 2015 15:52:23 
Done Updating Temp Global Snapshot Files ...End time:Tue Apr 28 2015 15:52:48 
Updating Temp Global Snapshot BugFixes ...Start time:Tue Apr 28 2015 15:52:48 
Done Updating Temp Global Snapshot Bugfixes ...End time:Tue Apr 28 2015 15:53:07 

Gathering statistics for table 'AD_SNAPSHOTS'... 
Done. 

Gathering statistics for table 'AD_SNAPSHOT_BUGFIXES'... 
Done. 

Gathering statistics for table 'AD_SNAPSHOT_FILES'... 
Done. 





Cleaning up temporary files... 

***** File-system snapshot utility completed successfully ****** 


Review the messages above, then press [Return] to continue. 


Backing up restart files, if any......Done.