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 是否正常執行

"先 outer join 再 filter " 的結果不一定等於 "先 filter 再 outer join "

這是個很弔詭的問題，直覺上我們會覺得先join 再filter 跟先filter 再 join 的結果是相同的，的確，這情況會發生在 inner join 身上，所以想當然爾，我們也會認為outer join 的結果也會相同，但是事實卻不是如此。

舉例來說，如果是先 left outer join 再 filter 的話，right table會因為 filter 的欄位而缺少配對的數值，換個角度看，因為缺少配對的數值，所以應該要存在的值就變成是 NULL，所以這也是為何加上 ..... OR column is NULL  會變成正確的原因。
老實說，這很難理解，但是我們如果先 filter 再做 left outer join 的話，問題就會變的簡單多了
先 filter column = 'Y' 是我們想要的第一層結果，再做 left outer join 是我們想要的第二層結果，讓 left table 全數呈現。

結論是，今後遇到 outer join 加上 filter 的問題，先使用 sub-query 做 filter ，再做 outer join。

How To Start OSWatcher Black Box (OSWBB) Every System Boot

APPLIES TO:

Reference from Oracle Doc ID 580513.1
Linux OS - Version Oracle Linux 4.6 to Oracle Linux 6.3 [Release OL4U6 to OL6U3]
Oracle VM - Version 2.1 to 3.1.1 [Release OVM21 to OVM31]
Information in this document applies to any platform.

GOAL

Oracle support often recommends that the OSWatcher Black Box(*) tool be run for an extended period.  Should the system reboot during this time, the system administrator must manually restart the OSWatche Black Boxr, and allow it to run until the necessary data have been collected.

* NOTE:

Depending on the version you have, the OSWatcher Black Box tool may also be referred to as simply OSWatcher; the nomenclature has been changed.

To automate this procedure, a simple shell script can be used.  Care must be taken to avoid accidentally overwriting the log data upon a restart.   The script must also ensure that the OSWatcher Black Box tool be run using the correct user privileges.

The osw-service RPM package does not include the OSWatcher Black Box  tool.  OSWatcher Black Box must be downloaded separately.

SOLUTION

The osw-service RPM package provides a script to run the OSWatcher at system boot, and to stop it down gracefully at system shutdown.  It provides an "osw" service that can be controlled using the standard Linux init(1) script controls:

# /sbin/chkconfig oswbb on
# /sbin/service oswbb start

The osw-service RPM package is available as an attachment to this note.  Download and install it as any other RPM package.  A source RPM is provided for completeness.

Before starting the service, first change the settings in the /etc/oswbb.conf configuration file to fit your situation:

# Set OSW_HOME to the directory where your OSWatcher tools are installed
OSW_HOME='/opt/osw'
# Set OSW_INTERVAL to the number of seconds between collections
OSW_INTERVAL='60'
# Set OSW_RETENTION to the number of hours logs are to be retained
OSW_RETENTION='48'
# Set OSW_USER to the owner of the OSWHOME directory
OSW_USER='root'
# Set OSW_COMPRESSION to the desired compression scheme
OSW_COMPRESSION='bzip2'
# Set OSW_ARCHIVE to the location to generate the logs
OSW_ARCHIVE='archive'

Once this is done, the command:

# /sbin/service oswbb start

will start the OSWatcher tool upon every boot.

The OSWatcher logs will be stored in ${OSW_ARCHIVE); the default value is the archive/ subdirectory as in earlier versions

How To Collect an Sosreport on Oracle Linux

APPLIES TO:

Reference from Oracle Doc ID 1500235.1
Linux OS - Version Oracle Linux 4.4 and later
Oracle VM - Version 3.2.7 to 3.2.7 [Release OVM32]
Information in this document applies to any platform.

GOAL

The "sosreport" is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.

Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.

Why support needs sosreport

The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.

Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.

The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.

The sosreport helps the support to identify configuration errors and make proactive recommendations too.

SOLUTION

Main Content

To run sosreport, the package "sos" must be installed. This is usually installed by default, unless the system was installed with a custom package set. If it is not installed, it can be installed from ULN with up2date, the public Oracle Linux repository or from installation media. It is also a good idea to make sure it is up to date.

To create the sosreport can be as simple as running the command in a terminal, without arguments, as root:

# sosreport

It will ask for some information related to a support case:

# sosreport

sosreport (version 1.7)

This utility will collect some detailed  information about the
hardware and  setup of your  Enterprise Linux  system.
The information is collected and an archive is  packaged under
/tmp, which you can send to a support representative.
This information will be used for diagnostic purposes ONLY
and it will be considered confidential information.

This process may take a while to complete.
No changes will be made to your system.

Press ENTER to continue, or CTRL-C to quit.
Please enter your first initial and last name [testsystem-2]: jdoe
Please enter the case number that you are generating this report for: 3-1234567890

 and will then show a progress bar with estimated time:

 Progress [#                  3%                  ][00:33/19:54]

 On completion, a bzip2-compressed tarball will be created in /tmp, along with a file containing the md5sum so that the file's integrity can be verified by the support representative. The filename will be printed to the terminal:

Progress [###################100%##################][03:15/03:15]

Creating compressed archive...

Your sosreport has been generated and saved in:
  /tmp/sosreport-jdoe.3123456789-182843-6a3d9a.tar.bz2

The md5sum is: 17be8be4266b06efd9726825ff6a3d9a

Please send this file to your support representative.

It is possible to have the sosreport created somewhere other than /tmp by setting the TMPDIR environment variable when running the sosreport command:

# TMPDIR=/home/jdoe sosreport

Your sosreport has been generated and saved in:
  /home/jdoe/sosreport-jdoe.31234567890-904562-30011c.tar.bz2

Additional options:

To list available plugins:

# sosreport -l

sosreport (version 1.7)

The following plugins are currently enabled:

 apache          Apache related information
 autofs          autofs server-related information
 bootloader      Bootloader information
 devicemapper    device-mapper related information (dm, lvm, multipath)
 emc             EMC related information (PowerPath, Solutions Enabler CLI and Navisphere CLI)
 filesys         information on filesystems
 general         basic system information
 hardware        hardware related information
 ipsec           ipsec related information
 kernel          kernel related information
 ldap            LDAP related information
 libraries       information on shared libraries
 mail            mail server related information
 memory          memory usage information
 networking      network related information
 nfsserver       NFS server-related information
 pam             PAM related information
 printing        printing related information (cups)
 process         process information
 rpm             RPM information
 samba           Samba related information
 sendmail        sendmail information
 ssh             ssh-related information
 startup         startup information
 system          core system related information
 systemtap       SystemTap pre-requisites information
 x11             X related information
 xen             Xen related information
 yum             yum information

The following plugins are currently disabled:

 amd               Amd automounter information
 cluster           cluster suite and GFS related information
 ftp               FTP server related information
 initrd            initrd related information
 named             named related information
 openswan          ipsec related information
 radius            radius related information
 rhn               RHN Satellite related information
 s390              s390 related information
 selinux           selinux related information
 squid             squid related information
 xinetd            xinetd information

The following plugin options are available:

 devicemapper.lvmdump  off collect raw metadata from PVs
 general.syslogsize    15 max size (MiB) to collect per syslog file
 kernel.modinfo        on gathers module information on all modules
 kernel.sysrq          off trigger sysrq+[m,p,t] dumps
 networking.traceroute off collects a traceroute to linux.oracle.com
 rpm.rpmq              on queries for package information via rpm -q
 rpm.rpmva             on runs a verify on all packages
 yum.yumlist           off list repositories and packages

If the system has a lot of packages installed, and sosreport takes a long time to complete, support may request that you disable the rpm database verification (verifies all packaged files on the filesystem against the rpm database)

# sosreport -k rpm.rpmva=off

In older versions of sosreport, the kernel.sysrq option was enabled by default. On heavily loaded systems, this can take a long time and can cause problems; particularly on clustered systems where it may cause a node eviction. It may be wise to check if this option is disabled by default on your system, or to manually disable it when running sosreport:

# sosreport -k kernel.sysrq=off

 For further information, see the manual page man sosreport or the help text sosreport --help

Known issues

1. In earlier versions, sosreport could fail with:

#  /usr/sbin/sosreport -k rpm.rpmva=off -n kernel.sysrq
Traceback (most recent call last):
File "/usr/sbin/sosreport", line 31, in <module>
 import sos.policyredhat
ImportError: No module named sos.policyredhat

This usually occurs when a 3rd-party version of python is installed and has been fixed in version 1.7-6 and newer

2. Run sosreport may change the /usr/java/latest symbolic link

sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release (Doc ID 1597124.1)

REFERENCES

NOTE:1597124.1 - sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release

ORA-02070: database does not support in this context

現象：
insert into remote_table@dblink(columns.....)
select columns..... from local_view ;
==> ORA-02070: database  does not support  in this context

解決方式：
1. 將 select columns..... from local_view 換成 select columns..... from local_table.....，不要使用view，改成table
2. 將procedure 由 local DB執行改為 remote DB執行，例如
    insert into local_table(columns...) select columns... from remote_view@dblink ;
3. 將 procedure 改成 cursor 運作

參考來源：
ORA-2070 during an remote Insert over Database link while selecting from local dictionary view (Doc ID 744219.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 7.2.3.0 to 11.2.0.2 [Release 7.2.3 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

ORA-2070 Error occurs when inserting into remote Table with Select from local Dictionary Views.
For Example :"Ora10g" is Local Database and "Dblink" is name of Database link that connects to Remote Database where Table T2 exists..T2 has same DDL structure as V$instance.
On executing below Remote Insert Statement, ORA-02070 Error occurs

insert into t2@ora10g(select *from v$instance)
*
ERROR at line 1:
ORA-02070: database does not support in this context

CHANGES

CAUSE

02070, 00000, "database %s%s does not support %s in this context"
// *Cause: The remote database does not support the named capability in
// the context in which it is used.
// *Action: Simplify the SQL statement.

//
The operation is unsupported which is clear from Bug 469264 "INSERT INTO REMOTE TABLE SELECT FROM LOCAL DICTIONARY VIEW GIVES 2070"
"The error is not a bug, but I've changed the message to print the name ofthe operation (in this case we will print "USERENV" instead of "operator 105") to make diagnosing possible."

SOLUTION

Workaround

You can use any one of 2 Solutions as shown below to resolve the issue:

1) Use Insert Statement in Remote Database and use Database links in Select statements .
   

insert into t2 (select * from v$instance@Dblink);
commit;

   Here Dblink is the name of Database Link that connects to Database where  information of table V$instance are taken.

2) Use a PL/Sql cursor in local Database to fetch Data from Local Database and then Insert into remote Database as shown in below Example :

declare
cursor c1 is select * from V$instance;


begin
for i in c1 loop
insert into T2@Dblink values(i.INSTANCE_NUMBER ,
i.INSTANCE_NAME ,
i.HOST_NAME,
i.VERSION ,
i.STARTUP_TIME ,
i.STATUS ,
i.PARALLEL ,
i.THREAD# ,
i.ARCHIVER ,
i.LOG_SWITCH_WAIT ,
i.LOGINS ,
i.SHUTDOWN_PENDING,
i.DATABASE_STATUS ,
i.INSTANCE_ROLE ,
i.ACTIVE_STATE ,
i.BLOCKED ) ;
end loop;
end;
/
commit;

Here "Dblink" is the name of Database link that connects
from Local database (where information on V$instance is taken)
to Remote database(where table T2 exists).

SAP Note: 88346 - Changing the RFC destination if IP changes are made

Symptom

After you change the IP addresses of a host, the RFC destinations for which this host is defined as the target host no longer work.

Other Terms

RFC, destination, IP, DNS, host name, target host

Reason and Prerequisites

If you enter the host name (for example hs0311) as the target host when you maintain the RFC destination, the host name is stored internally in the database table RFCDES (for the destination administration) as its corresponding IP address. Among other things, this should reduce the time required to set up the RFC connection. As a result, the conversion of the target host name into its IP address is no longer required. Furthermore, the host name (as a Domain Name Server, among other things) can assume any length. This is not suitable for storage in the database table RFCDES.

This will create problems if the IP addresses of the target hosts are changed. After this action has been carried out, you must therefore amend the affected RFC destinations that use the IP addresses accordingly in transaction SM59.

Note:

• If you want to prevent the name of the target host from being stored as its respective IP address in the database table RFCDES, you must enter the host name as a SAP router name (/H/hs0311 instead of hs0311). In this case, the host name is stored as such in the database table RFCDES.

SAP Note: 403708 - Changing an IP address

Symptom

You want to change an IP address.

Other Terms

IP address, IP configuration, network, TCP/IP ,ipconfig

Reason and Prerequisites

This note provides information about configuring the operating system.

Solution

Internally, SAP systems use host names to communicate. As a result, you should experience no problems when you change an IP address.

Theoretically, you only need to make changes at operating system level:
- /etc/hosts
- /etc/services
- DNS entries
- Routing tables

However, you should err on the side of caution and check the following in the SAP system:
- Transaction SM59, table RFCDES, also see
  Note 88346 for additional information
- Transaction SM55, if you are using IP addresses here, you must adjust them
- Transaction SM51, check for consistent host names
- Transaction SMLG, check whether the entries for the server groups have been created with IP addresses

Also ensure that changes are not only made in the affected system but in all SAP systems, clients, and external programs that communicate with the affected system in any way.

Outside the SAP system:
- saprouter, adjust the saprouttab to the changes
* If the saprouter receives a new address:
  You should inform us of this change (for sapserv3).
  Open a message on the 'xx-ser-net-chg' component
  and inform us of the address change.
- Adjust the IP addresses so that the service connection continues to function
- If you use an APO system, see Note
  154997

Depending on your system landscape and configuration, additional
relevant points may be added. This note does not claim to be complete.

RMAN Backup and Recovery (Oracle 10g on AIX)

• Install Oracle 10g software for RMAN catalog DB

Environment:
OS Level = AIX 5.3
Oracle DB Version = 10.2.0.3
Oracle Catalog DB SID = CATDB
Oracle RAC DB SID = PROD
Oracle RAC DB SID1 = PROD1
Oracle RAC DB SID2 = PROD2
TDPO node name = dbsrv_oracle


1. Install 10.2.0.1 software DB

login as root

cd /source/Disc1/cdrom/database/rootpre

./rootpre.sh

login as oracle

vi .profile

export TMP=/oracle/tmp

export TMPDIR=/oracle/tmp

export ORACLE_HOME=/oracle/10.2.0/db

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_SID=catdb

cd /source/Disc1/cdrom/database

export DISPLAY=catdbsrv:1.0

mkdir /oracle/tmp

./runInstaller

install software only

2. Apply 10.2.0.2 patchset

cd /source/patch/Disk1

./runInstaller

3. Create catalog database

dbca (SID=catdb)

4. Create Listener

netca

5. Create alias for PROD db

netca

• Configure Catalog Database

1. Creating tablespace to store backup information

SQL> create tablespace rmancat datafile

'/rman/catdb/rmancat01.dbf' size 4g;

2. Creating the Recovery Catalog Owner

SQL> CREATE USER rman IDENTIFIED BY cat

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE rmancat

QUOTA UNLIMITED ON rmancat;

SQL> grant CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER to rman;

3. Creating the Recovery Catalog
oracle> rman catalog rman/cat

RMAN> CREATE CATALOG;

4. Verify the user

sqlplus rman/cat

SQL> SELECT TABLE_NAME FROM USER_TABLES;

• Configure Production Database

1. Login to db server using oracle account

2. Create tnsnames alias for catdb

netca

3. Link TSM Data Protection for Oracle

ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a

4. Define Data Protection for Oracle options

login root

vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt

dsmi_orc_config     /usr/tivoli/tsm/client/oracle/bin64/dsm.opt

dsmi_log     /usr/tivoli/tsm/client/oracle/bin64

tdpo_node     dbsrv_oracle

tdpo_pswdpath     /usr/tivoli/tsm/client/oracle/bin64

vi /usr/tivoli/tsm/client/oracle/bin64/dsm.opt
SErvername tdpo

vi /usr/tivoli/tsm/client/ba/bin/dsm.sys

SErvername     tdpo

CHANGINGRETRIES     0

NODENAME     dbsrv_oracle

PASSWORDACCESS     prompt

COMMMethod     TCPip

TCPPort     1600

TCPServeraddress     xx.xx.xx.xx

cd /usr/tivoli/tsm/client/api/bin64

ln -s /usr/tivoli/tsm/client/ba/bin/dsm.sys  dsm.sys

login root

dsmadmc

REG NODE   dbsrv_oracle   oracleprod maxnummp=2   passexp=0

cd /usr/tivoli/tsm/client/oracle/bin64

tdpoconf password (enter <password> three times)

tdpoconf   showenvironment   (confirm the configuration)

rm /usr/tivoli/tsm/client/oracle/bin64/tdpoerror.log

• Managing Target Database in the Recovery Catalog

1. Registering a Database in the Recovery Catalog

login oracle

rman TARGET / CATALOG rman/cat@catdb

RMAN> REGISTER DATABASE;

RMAN> REPORT SCHEMA;

2. Configure Default RMAN Setting

RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE sbt 

CONNECT 'SYS/<pwd>@PROD1' 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE sbt 

CONNECT 'SYS/<pwd>@PROD2' 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> crosscheck archivelog all;

• PROD Database Backup Scripts

1. Level 0 Backup

rman target / catalog rman/cat@catdb

RMAN> backup incremental level 0

filesperset 50 

format "PROD_df_level0_%t_%s_%p" database

plus archivelog format "PROD_arch_%t_%s_%h"

delete all input;

RMAN> delete noprompt obosolete;

(The script is create under $ORACLE_HOME/scripts/rman_bck_level0.sh)

2. Level 1 Backup

rman target / catalog rman/cat@catdb

RMAN> backup incremental level 1 cumulative

filesperset 50

format "PROD_df_level1_%t_%s_%p" database

plus archivelog format "PROD_arch_%t_%s_%h”

delete all input;

(The script is create under $ORACLE_HOME/scripts/rman_bck_level1.sh)

3. Archive Log Files Backup

rman target / catalog rman/cat@catdb

RMAN> backup

filesperset 10

format "PROD_arch_%t_%s_%h" archivelog all delete all input;

• Catalog Database Backup Scripts(Use DataPump)

1. Create Directory Definition for DataPump

sqlplus “/ as sysdba”

SQL> create directory dpump_dir as ‘/rman/bck_catdb’;

SQL> grant READ, WRITE ON DIRECTORY dpump_dir TO rman;

2. Use DataPump Export to backup the catalog

expdp rman/cat DUMPFILE=dpump_dir:exp_rman.dmp \

logfile=dpump_dir:exp_rman.log

• Restore & Recovery Scripts

1. Loss all database files, but control files, and online redo log files 

are not loss

rman target / catalog rman/cat@catdb

RMAN> run {

startup mount;

restore database;

recover database;

alter database open;

}

2. Loss all database files, control files, and online redo log files

For this situation, you need to perform incomplete recovery. Please try to 

find the latest archived log file information in the alert file, and check the 

timing information

rman target / catalog rman/cat@catdb

RMAN> run {

startup nomount;

ALLOCATE CHANNEL T1 DEVICE TYPE sbt 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; 

ALLOCATE CHANNEL T2 DEVICE TYPE sbt 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

restore controlfile from autobackup;

alter database mount;

set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;

restore database;

recover database;

alter database open resetlogs;

}

If the above recovery failed due to no latest archivelog file to be applied, then perform the following statements to open the database.

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> run { alter database open resetlogs; }

Remove all 2PC pending transactions

Generate the scripts first if there are many 2PC pending transactions, then edit the scripts 

to add ‘commit;’ statement for every DBMS_TRANSACTION statement

sqlplus “/ as sysdba”

set linesize 120

set pages 24

set head off

spool remove_2pc_pending.sql

select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||

LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;

spool off

Remove all expired archivelogs

$ cd /prodarch

remove restored archivelogs

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

3. All the machines crash, need to find a new machine to startup the database

Install Oracle 10g Software on the new machine

Setup TSM and Data Protection for Oracle

Please refer the “Configure Production Database” Section

Create initial parameter initPROD1.ora with the following setting

background_dump_dest =/oracle/10.2.0/db/admin/PROD1/bdump

core_dump_dest =/oracle/10.2.0/db/admin/PROD1/cdump

user_dump_dest =/oracle/10.2.0/db/admin/PROD1/udump

db_name ="PROD"

instance_name =PROD1

control_files =('/prodora/control01.ctl','/proddata1/control02.ctl','/prodindx1/control03.ctl')

#Set the below to the same as the production target

undo_management =AUTO

undo_tablespace =APPS_UNDOTBS1

db_block_size = 8192

log_archive_format=%t_%s_%r.arc

log_archive_dest_1=’location=/prodarch/’

log_archive_format=%t_%s_%r.arc

sga_target=8G

db_cache_size=4G

shared_pool_size=2G

pga_aggregate_target=16G

compatible = 10.2.0.

# To clone RAC db to non-RAC db need to add this parameter

_no_recovery_through_resetlogs=TRUE

Perform the Recovery

rman target / catalog rman/cat@catdb

RMAN> run {

startup nomount;

ALLOCATE CHANNEL T1 DEVICE TYPE sbt 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

ALLOCATE CHANNEL T2 DEVICE TYPE sbt 

PARMS='ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

restore controlfile from autobackup;

alter database mount;

set until time = “to_date(‘<TIME_INFO>’,’YYYY/MM/DD HH24:MI:SS’)”;

restore database;

recover database;

alter database open resetlogs;

}

If the above recovery failed due to no latest archivelog file to be applied, then perform the 

following statements to open the database.

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> run { alter database open resetlogs; }

Remove all 2PC pending transactions

Generate the scripts first if there are many 2PC pending transactions, then edit the scripts 

to add ‘commit;’ statement for every DBMS_TRANSACTION statement

sqlplus “/ as sysdba”

set linesize 120

set pages 24

set head off

spool remove_2pc_pending.sql

select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''||

LOCAL_TRAN_ID||''');' from DBA_2PC_PENDING;

spool off

Remove all expired archivelogs

$ cd /prodarch

remove restored archivelogs

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

• Duplicate Database

1. create an alias entry in tnsnames.ora file for PROD and catdb(optional)

2. confirm the connection to PROD and catdb(optional) working

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)(HOST = db1srv_vip.domain)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = PROD)

(INSTANCE_NAME = PROD1)

)

)

CATDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = catdbsrv.domain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = catdb)

)

)

sqlplus system/<pwd>@prod

sqlplus rman/cat@catdb

3. determine the files location and space to be restored

this information is used to set ‘db_file_name_convert’ parameter in 

init<SID>.ora file

4. create the init<SID>.ora parameter file and administration directories 

for the duplicate database, the parameter file should have the following 

setting:

background_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/bdump

core_dump_dest =/newprod/10.2.0/db/adminNEWPROD_otsrv/cdump

user_dump_dest =/newprod/10.2.0/db/admin/NEWPROD_otsrv/udump

db_name ="NEWPROD"

instance_name =NEWPROD

control_files =('/newprod/data/control01.ctl','/newprod/data /control02.ctl','/newprod/data /control03.ctl')

db_file_name_convert =("/proddata1/","/newprod/data/", "/prodindx1/","/newprod/data/", "/prodindx1/","/

newprod/data/", "/prodora/","/newprod/data/")

log_file_name_convert =("/prodora/PROD/", "/newprod/data/",”/proddata1/”,”/newprod/data/”)

#Set the below to the same as the production target

undo_management =AUTO

undo_tablespace =APPS_UNDOTBS1

db_block_size = 8192

log_archive_format=%t_%s_%r.arc

log_archive_dest_1=’location=/arch/’

compatible = 10.2.0.

# To clone RAC db to non-RAC db need to add this parameter

_no_recovery_through_resetlogs=TRUE

5. Startup the duplicate database instance

Sqlplus “/ as sysdba”

SQL> startup nomount;

6. Prepare RMAN duplicate script

run {

allocate auxiliary channel t1 type sbt

parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";

allocate auxiliary channel t2 type sbt

parms="ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)";

set until time "to_date('<the_time_within_RMAN_backup>','YYYY/MM/DD 

HH24:MI:SS')";

duplicate target database to NEWPROD;

}

7. run RMAN to duplicate the database

rman target sys/<pwd>@prod nocatalog auxiliary /

RMAN> @duplicate.script