Oracle ORA-39726 -- compress table drop column

Oracle ORA-39726 -- compress table drop column
--------------------------------------------------
ALTER TABLE <schema>.<table_name> SET UNUSED COLUMN <column_name>;
ALTER TABLE <schema>.<table_name> DROP UNUSED COLUMNS;

Reference:
39726, 00000, "unsupported add/drop column operation on compressed tables"
// *Cause:  An unsupported add/drop column operation for compressed table
//          was attemped.
// *Action: When adding a column, do not specify a default value.
//          DROP column is only supported in the form of SET UNUSED column
//          (meta-data drop column).

2181970 - tp ended with error code 0200 or 0208

在測試區(非QAS)上面要import CR遇到這個問題。

因為測試區的 /usr/sap/trans是獨立的，沒有NFS到DEV上面，所以才會找不到 DEV的 TP_DOMAIN_DEV.PFL，複製過來並且更改權限後就可以運作了。

以下是SAP官網文章僅供參考。

Symptom

STMS does not import the transport requests.

Environment

SAP Netweaver release independent.

Reproducing the Issue

• Schedule the auto import job in STMS, check the Alert view and find the following error:

When double clicking in the message TP_REPORTED_ERROR you have the following information:

Cause

• The error points to a missing "TP_DOMAIN_<sid>.PFL" file in the DIR_TRANS\bin directory.
• This can be caused if the system has more than one server and they do not point to the same transport directory (DIR_TRANS).

Resolution

• The transport directory must be correctly configured and point to the DIR_TRANS\bin where file "TP_DOMAIN_<sid>.PFL" exists.
• Make sure parameter DIR_TRANS defined for each server points to the same transport directory. Check it with your operation system administrator or check it in the SAP systemas follow:
• To check if all servers point to the same transport directory, execute the following steps:

1. Open transaction SM51;
2. Logon to the first server (Ctrl + Shift + F8);
3. Open transaction AL11; 
4. Double click in the transport directory (DIR_TRANS);
5. Open directory tmp (if it is empty, open another directory like log);
6. In a new window repeat steps 1 to 5 for each server available in SM51;
7. Now compare the files you see in each AL11 opened and make sure they all the same to guarantee all the servers point to the same transport directory.

See Also

SAP Note 814639 - Transport requests disappear from the buffer;
SAP Note 1788821 - Cannot view transport logs for system B whilst in system A

Keywords

TMS_TP_IMPORT, tp, 0200, transport control program tp ended with error code 020, XT200, 0208, TK094, Your transport request could not be exported, since all requirements were not fulfilled.

Product

	Product or Product Version
	SAP NetWeaver all versions

This document is referenced by

	Number	Title
	2464165	The values set for DIR_LIBRARY or dbms_type are invalid

Client Copy

Local Client Copy
-----------------------------
1. 使用SCC4 建立target client
2. 確認profile parameter "login/no_automatic_user_sapstar" = 0
使用sap*/pass登入target client
使用SCCL複製資料
3. 指定source client，選定customizing data, application data and user master records
4. 使用背景程式執行client copy

PS:
1.為了確保data consistency, 不要進入target client 操作，最好source client也不要使用
2.如果使用全新client，可以從cloent 000 複製過去
3. 如果要增進效能，可以從 (menu) Edit => Export Settings 排除tables or packages，關於Export Setting，可以參考Note 446485


Remote Client Copy
-------------------------------------
1. 步驟跟local client copy相同，只差在remote client copy是透過RFC傳送資料
2. 遇到問題請參考Note 557132
3. Remote client copy 另外可以參考Note 47502
4. 如果remote client copy複製all data，在過程中source client 的 table結構如果改變，remote client copy將會因錯誤而終止


Parallel Procesing
----------------------------------
1. 使用SCCL (local client copy) 或是 SCC9 (remote cloent copy)，接著在(menu) Goto => Parallel Processes 指定平行處理的數目
2. 平行處理無法用在client transports
3. 如果要定義RFC server group，使用SM59，RFC => RFC Groups，其他資訊可以參考Note 99284


Client Transport
--------------------------------
Client transport的功能跟local client copy and remote client copy相同，但是不使用RFC
client transport export:
1. 登入source client，使用SCC8，使用profile挑選要複製的資料
2. 指定target system，target system要在TMS中有定義的system
3. 因為執行時間可能會很久，需要使用背景程式跑

在export過程中，會call "tp" 非同步的將資料匯出:
RO<number>: 包含client-independent data
RT<number>: 包含client-specific data
RX<number>: 包含SAP script texts

即使是SCC8已經結束，也不代表export 完成，此時不要執行任何client copy的工具，可以使用SE01檢查export的狀態，並顯示request<SID>KT<NO>的log。
在export過程中，使用SCC3也得不到有用的資訊，因為執行R3trans的export 作業已經結束，反而會得到abort的訊息。

client transport import:
根據選取的client ransport profile 資料型態的不同，以下的client copt command file會被加到target system buffer:
<SID>KO<number>: 包含client-independent data
<SID>KT<number>: 包含client-specific data
<SID>KX<number>: 包含SAP script texts

使用STMS過版client transport request，完成後再登入到target client 使用SCC7做post-import activities。

使用SE09顯示client transport logs

在client transport過程中，使用SCC8的RFC system check按鈕可以check repository consistency。


Others
----------------------
如何複製large client可以參考Note 489690
估計client size大小可以執行程式RSTABLESIZE

SRVCTL command

1. For single database in ASM:
# srvctl status database -d <db_unique_name>
# srvctl status listener

# srvctl stop database -d <db_unique_name> -o immediate
# srvctl stop listener
# srvctl start listener
# srvctl start database -d <db_unique_name>

# srvctl status database -d <db_unique_name>
# srvctl status listener

2. For RAC database in Filesystem or ASM
If service name is PROD, instance name is PROD1 (hostname is HOST1) and PROD2 (hostname is HOST2)

# srvctl status database -d PROD
# srvctl status listener

# srvctl stop database -d PROD -o immediate
or
# srvctl stop instance -d PROD -i PROD1,PROD2 -o immediate

# srvctl stop listener
or
# srvctl stop listener -n HOST1

# srvctl start listener
or
# srvctl start listener -n HOST1

# srvctl start database -d PROD
or
# srvctl start instance -d PROD -i PROD1,PROD2

# srvctl status database -d <db_unique_name>
# srvctl status listener

Reference:
https://docs.oracle.com/html/E25494_01/restart005.htm#srvstartdb1

如何刪除HPUX的未使用裝置

例如找出 Tape 未連接裝置
# ioscan -fnC tape
Class     I  H/W Path       Driver   S/W State   H/W Type     Description
==================================================
tape      1  0/4/1/0/4/0.2.0  stape    NO_HW       DEVICE       IBM     DDS Gen5
         /dev/rmt/1m            /dev/rmt/1mn           /dev/rmt/c2t2d0BEST    /dev/rmt/c2t2d0BESTn
         /dev/rmt/1mb           /dev/rmt/1mnb          /dev/rmt/c2t2d0BESTb   /dev/rmt/c2t2d0BESTnb
tape      0  0/4/1/0/4/1.0.0  stape    CLAIMED     DEVICE       HP      C7438A
         /dev/rmt/0m            /dev/rmt/0mnb          /dev/rmt/c3t0d0BESTn   /dev/rmt/c3t0d0DDSb
         /dev/rmt/0mb           /dev/rmt/c3t0d0BEST    /dev/rmt/c3t0d0BESTnb  /dev/rmt/c3t0d0DDSn
         /dev/rmt/0mn           /dev/rmt/c3t0d0BESTb   /dev/rmt/c3t0d0DDS     /dev/rmt/c3t0d0DDSnb

找出顯示 NO_HW 的 H/W Path，再使用 rmsf 指令刪除
# rmsf -H 0/4/1/0/4/0.2.0

或是直接執行以下指令：
# ioscan -nfC tape|grep NO_HW|awk '{print $3}'|xargs -i -t rmsf -H {}

或是重新 reboot 也可以

最後再重新搜尋一次
# ioscan -fnC tape
Class     I  H/W Path       Driver   S/W State   H/W Type     Description
==================================================
tape      0  0/4/1/0/4/1.0.0  stape    CLAIMED     DEVICE       HP      C7438A
         /dev/rmt/0m            /dev/rmt/0mnb          /dev/rmt/c3t0d0BESTn   /dev/rmt/c3t0d0DDSb
         /dev/rmt/0mb           /dev/rmt/c3t0d0BEST    /dev/rmt/c3t0d0BESTnb  /dev/rmt/c3t0d0DDSn
         /dev/rmt/0mn           /dev/rmt/c3t0d0BESTb   /dev/rmt/c3t0d0DDS     /dev/rmt/c3t0d0DDSnb

Oracle ERP Daily Check

-- DB
invalid object
blocking lock
unusable index
enable but invalid trigger
top 5 event
long running session
session count
tablespace free status
tablespace space compare with the previous day
segment space compare with the previous day
-- AP
concurrent program enable run alone
concurrent program enable trace
diable User Has Pending concurrent request
concurrent request running but completed
concurrent request running
concurrent manager queue status

SAP Basis Daily Check

Skip to end of metadata參考網址

Reference:

http://wiki.scn.sap.com/wiki/display/Basis/SAP+Basis+Daily+Tasks?original_fqdn=wiki.sdn.sap.com

• SAP System R/3 System Status Check:

Logon Test

The availability of the SAP system is a pre-requisite for using the SAP system. If  you to establish connection to the SAP system the system must be up and running.

如果logon沒問題，表示系統是正常的

• Backup Management:

DB12

It is recommended that backup of the SAP system daily. Success or failure of the backup run has to be monitored daily. Ensure that backups are done properly so that you can recover the system state when it’s required. When a backup run fails, you should immediately resolve the problem and possibly perform an “emergency” backup.

養成習慣，每天確認備份是否成功

• Application Servers Status Check:

SM51

Application servers used for load balancing, hence, the need for their availability. The application server represents the runtime environment the SAP system. Use transaction SM51 to display the status of the instances of your SAP system.

• Work processes Status Check:

SM51

Work processes are essential for the effective functioning of the SAP system. It is important to ensure that all configured work processes possess their correct status at any point in time. The SAP administrator should be able to know when to add or redistribute work process based on usage analysis.

確認CI and DI 每個節點的WP都是正常運作

• Failed Updates Monitoring:

SM13

Failed updates are transaction that is not committed in the database. As administrator you needs to critical review such updates. Examine the reason and reprocess the failed update if required.

檢查是否有update error，也可以說是lock

• System Log Review:

SM21

The SAP system has its own system log. The system log contains error, warning and problem messages. The application server records events and problems in the system log and has a log that contains the messages output.

檢察系統 log

• Jobs Monitoring:

SM37/SM35

In order to optimize resources and increase performance of SAP systems, some operations are performed at the background, defined or standard jobs. Background job as it were, is supposed to perform assigned task. Review the status of jobs for failure or success.

檢查系統管理相關的 job 是否正常執行