Oracle 排障配置与调整 – 第12页

恢复被删除(损坏、丢失)的goldengate trailfile

1.1恢复目标被删除(损坏、丢失)的trailfile

原理：根据目标端trailfile最后应用的scn来去源端找到准确位置后重传数据

测试方法:手动删除目标端的最后一个trail file后进行测试(并不是完全恢复这个文件,只是将删除文件中未应用的数据找出,产生包含丢失数据的文件而已)
关于trailfile存在源、目标方式:
1、源端、目标拥有独立的trailfile.extract抽取后经过datapump传输到目标端
2、源端、目标共享trailfile.extract抽取被replicat进程直接读取.这种不需要配置datapump,这种情形使用不如第一种情形多.

测试场景:
采用第一种,源端和目标端都是拥有独立的trailfile.是否想过源、目的trailfile文件大小或文件个数能否对应上,恢复直接从源端复制过来就可以使用?

具体如下.
Ex开头是源端,dp是目标端.初步对比来看个数以及文件大小都存在差异，初步设想不成功.

操作步骤:
1、先停止目标端的replicat进程.
2、源端：对一个空表插入100条数据.然后手动rename目标端未应用的trailfile一个.
验证源端数据:

SQL> select count(*) from leo_rows;
COUNT(*)
----------
100

验证目标端数据:

SQL> select count(*) from leo_rows;
COUNT(*)
----------
0

通过这个信息对比,发现2边数据不同步.
3、rename目标端未应用的trailfile。例如dp000014变成dp000014.old
mv dp000014 dp000014.old
4、根据目标端分析上次成功checkpoint信息位置找出目标端实际应用位置.
从checkpoint信息中查出实际应用到位置是extseqno=14、extrba=8751283.
但是此时dp000014文件已经丢失(只是rename而已).10g以后版本trailfile记录csn信息，不需要通过@genenv(“ORATRANSACTION”,”SCN”)强制写个token到trailfile.SCN是指Oracle transaction id的对应数值.可以通过Oracle v$transaction视图查询.查看进程的checkpoint里面记录了

CSN stateinformation:
CRC: 55-A2-8E-E4
Latest CSN: 1963641—这个就是对应Oraclescn.
Latest TXN: 10.23.846 –这个是transactionid
Latest CSN of finished TXNs: 1963641:这里面提示已经完成.不如此事务是不完整的,需要从重新同步这个事物.
Completed TXNs: 10.23.846
GGSCI (ludatou) 4> inforepleo showch
REPLICAT REPLEO Last Started 2014-04-22 20:27 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:09:17 ago)
Log Read Checkpoint File ./dirdat/dp000014
2014-04-22 20:32:05.801351 RBA 8751283
Current Checkpoint Detail:
Read Checkpoint #1
GGS Log Trail
Startup Checkpoint (starting position in thedata source):
Sequence #: 14
RBA: 8748614
Timestamp: 2014-04-22 13:01:47.962664
Extract Trail: ./dirdat/dp
Current Checkpoint (position of last recordread in the data source):
Sequence #: 14
RBA: 8751283
Timestamp: 2014-04-22 20:32:05.801351
Extract Trail: ./dirdat/dp
CSN state information:
CRC: 55-A2-8E-E4
Latest CSN: 1963641
Latest TXN: 10.23.846
Latest CSN of finished TXNs: 1963641
Completed TXNs: 10.23.846
Header:
Version = 2
Record Source = A
Type = 1
# Input Checkpoints = 1
# Output Checkpoints = 0
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 0
Transaction Integrity = -1
Task Type = 0
Database Checkpoint:
Checkpoint table = goldengate.ogg_checkpoint
Key = 296275922 (0x11a8cfd2)
Create Time = 2014-04-22 07:56:50
Status:
Start Time = 2014-04-22 20:27:58
Last Update Time = 2014-04-22 20:37:41
Stop Status = G
Last Result = 400

从上述看已经最后被应用的事务信息包括如下:
1、 csn=1963641
2、 TXN=10.23.846
根据如上信息登陆源端系统找到对应trailfile的rba信息.然后根据这个位置，重新修改datapump从这个位置传输trailfile到目标端.

开始恢复数据:
登陆源端主机:

[ludatou ~]$ cd $OGG
[ludatou112101]$cd dirdat
[ludatoudirdat]$ ls -lrt
total 126000
-rw-rw-rw- 1oracle oinstall 15198 Apr 27 12:24ex000000
-rw-rw-rw- 1oracle oinstall 9999850 Apr 27 12:28 ex000001
-rw-rw-rw- 1oracle oinstall 9999928 Apr 27 12:28 ex000002
-rw-rw-rw- 1oracle oinstall 9999824 Apr 27 12:33 ex000003
-rw-rw-rw- 1oracle oinstall 9999838 Apr 27 12:33 ex000005
-rw-rw-rw- 1oracle oinstall 9999495 Apr 27 12:33 ex000004
-rw-rw-rw- 1oracle oinstall 9999904 Apr 27 12:33 ex000007
-rw-rw-rw- 1oracle oinstall 9999928 Apr 27 12:33 ex000006
-rw-rw-rw- 1oracle oinstall 9999863 Apr 27 12:41 ex000010
-rw-rw-rw- 1oracle oinstall 9999714 Apr 27 12:41 ex000009
-rw-rw-rw- 1oracle oinstall 9999792 Apr 27 12:41 ex000008
-rw-rw-rw- 1oracle oinstall 9999867 Apr 27 12:44 ex000012
-rw-rw-rw- 1oracle oinstall 9999804 Apr 27 12:44 ex000011
-rw-rw-rw- 1oracle oinstall 8762807 Apr 27 21:01 ex000013

从根据目标段的时间来源端寻找时机最靠近的trailfile.即ex000013是第一选择,如果对应csn信息不在000013,则相前反向推找.

[ludatou dirdat]$ cd $OGG
[ludatou 112101]$./logdump –使用logdump对trailfile进行操作
Logdump 236>open ./dirdat/ex000013
Current LogTrailis /goldengate/112101/dirdat/ex000013
Logdump 237>ghdr on
Logdump 238>detail data
Logdump 239>usertoken detail
Logdump 240>ggstoken detail
Logdump 241>filter include csn = 1963641 --来自目标端的csn
Logdump 242>n --查看满足条件的数据
Scanned 10000 records, RBA 3160036, 2014/04/27 12:44:51.000.000
Scanned 20000 records, RBA 6320505, 2014/04/27 12:44:51.000.000
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 1204 (x04b4) IO Time : 2014/04/27 20:53:21.000.000
IOType : 160 (xa0) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2014/04/2720:53:21.000.000 DDLOP Len 1204 RBA 8749251
Name:
After Image: Partition 0 G s
2c43 353d 2732 3533 3127 2c2c 4237 3d27 32353331 | ,C5='2531',,B7='2531
272c 2c42 323d 2727 2c2c 4233 3d27 4c45 4f272c2c | ',,B2='',,B3='LEO',,
4234 3d27 4c45 4f5f 524f 5753 272c 2c43 31323d27 | B4='LEO_ROWS',,C12='
272c 2c43 3133 3d27 272c 2c42 353d 2754 41424c45 | ',,C13='',,B5='TABLE
272c 2c42 363d 2743 5245 4154 4527 2c2c 42383d27 | ',,B6='CREATE',,B8='
474f 4c44 454e 4741 5445 2e47 4753 5f44 444c5f48 | GOLDENGATE.GGS_DDL_H
4953 5427 2c2c 4239 3d27 4c45 4f27 2c2c 43373d27 | IST',,B9='LEO',,C7='
GGS tokens:
TokenID x52 'R'ORAROWID Info x00 Length 20
4141 4156 5849 4141 4541 4141 4146 6741 41460001 | AAAVXIAAEAAAAFgAAF..
TokenID x44 'D'DDL Info x00 Length 36
4c45 4f00 4c45 4f5f 524f 5753 0031 312e 322e302e | LEO.LEO_ROWS.11.2.0.
342e 3000 3131 2e32 2e30 2e34 2e30 004e | 4.0.11.2.0.4.0.N
TokenID x4c 'L'LOGCSN Info x00 Length 7
3139 3633 3634 31 | 1963641 –与目标csn符合
TokenID x36 '6'TRANID Info x00 Length 9
3130 2e32 332e 3834 36 | 10.23.846 –与目标tranid符合
Filteringsuppressed 27661 records

根据上述信息已经找到目标端应用的数据信息.
Datapump需要从满足条件下一条记录开始传输.所以继续输入N捕获下一条数据信息的rba
但是输入N却发现没有任何数据返回,是到达文件结尾了吗?根据判断这个属于中间数据.经过分析发现，原来是filter include csn = 1963641搞的鬼.现在清楚这个过滤条件.根据下面输出可以获得正确rba信息是:

Logdump 243 >n
Filteringsuppressed 100 records
Logdump 244>filter clear
Logdump 246>pos 8749251 –来自上面的rba信息,是目标端已经应用的数据.
Reading forwardfrom RBA 8749251
Logdump 247>n –表示输出这个数据的详细信息.下n才是我们需要的数据
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 1204 (x04b4) IO Time : 2014/04/27 20:53:21.000.000
IOType : 160 (xa0) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)
2014/04/2720:53:21.000.000 DDLOP Len 1204 RBA 8749251
Name:
After Image: Partition 0 G s
2c43 353d 2732 3533 3127 2c2c 4237 3d27 32353331 | ,C5='2531',,B7='2531
272c 2c42 323d 2727 2c2c 4233 3d27 4c45 4f272c2c | ',,B2='',,B3='LEO',,
4234 3d27 4c45 4f5f 524f 5753 272c 2c43 31323d27 | B4='LEO_ROWS',,C12='
272c 2c43 3133 3d27 272c 2c42 353d 2754 41424c45 | ',,C13='',,B5='TABLE
272c 2c42 363d 2743 5245 4154 4527 2c2c 4238 3d27| ',,B6='CREATE',,B8='
474f 4c44 454e 4741 5445 2e47 4753 5f44 444c5f48 | GOLDENGATE.GGS_DDL_H
4953 5427 2c2c 4239 3d27 4c45 4f27 2c2c 43373d27 | IST',,B9='LEO',,C7='
GGS tokens:
TokenID x52 'R'ORAROWID Info x00 Length 20
4141 4156 5849 4141 4541 4141 4146 6741 41460001 | AAAVXIAAEAAAAFgAAF..
TokenID x44 'D'DDL Info x00 Length 36
4c45 4f00 4c45 4f5f 524f 5753 0031 312e 322e302e | LEO.LEO_ROWS.11.2.0.
342e 3000 3131 2e32 2e30 2e34 2e30 004e | 4.0.11.2.0.4.0.N
TokenID x4c 'L'LOGCSN Info x00 Length 7
3139 3633 3634 31 | 1963641
TokenID x36 '6'TRANID Info x00 Length 9
3130 2e32 332e 3834 36 | 10.23.846

Logdump 248>n --这里是正确的rba信息,同时判断这个条记录是否开始位置.根据transind后面数字x00表示是开始位置，同时也确认我们找的rba是正确的.否则就是错误的.
TransInd : . (x00) 表示记录开始位置
TransInd : . (x01) 表示记录中间位置
TransInd : . (x02) 表示记录结尾位置
TransInd : . (x03) 表示这个事务只有1条记录
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 31 (x001f) IO Time : 2014/04/27 21:01:11.000.000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 161 AuditPos : 11343376
Continued : N (x00) RecCount : 1 (x01)
2014/04/2721:01:11.000.000 Insert Len 31 RBA 8750598
NameEO.LEO_ROWS
After Image: Partition 4 G b
0000 0005 0000 0001 3100 0100 0700 0000 036c656f | ........1........leo
0002 0007 0000 0003 3130 30 | ........100
Column 0 (x0000), Len 5 (x0005)
0000 0001 31 | ....1
Column 1 (x0001), Len 7 (x0007)
0000 0003 6c65 6f | ....leo
Column 2(x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
GGS tokens:
TokenID x52 'R'ORAROWID Info x00 Length 20
4141 4156 5976 4141 4541 4141 4146 3141 41410001 | AAAVYvAAEAAAAF1AAA..
TokenID x4c 'L'LOGCSN Info x00 Length 7
3139 3635 3636 33 | 1965663
TokenID x36 '6'TRANID Info x00 Length 8
382e 342e 3130 3638 | 8.4.1068

停止源端的pump，修改配置

Logdump 249>exit
[ludatou112101]$ ./ggsci
OracleGoldenGate Command Interpreter for Oracle
Version11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64,64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C)1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ludatou)1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPLEO 00:00:00 00:00:06
EXTRACT RUNNING EXTLEO 00:00:03 00:00:02
GGSCI (cifpay02)2> stop DPLEO
Sending STOPrequest to EXTRACT DPLEO ...
Requestprocessed.
GGSCI (ludatou)3> alter extract dpleo,extrba 8750598
EXTRACT altered.
GGSCI (cifpay02)4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPLEO 00:00:00 00:00:01
EXTRACT RUNNING EXTLEO 00:00:02 00:00:07
GGSCI (cifpay02)5> start dpleo
Sending STARTrequest to MANAGER ...
EXTRACT DPLEOstarting
GGSCI (ludatou)14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED DPLEO 00:00:00 00:02:38
EXTRACT RUNNING EXTLEO 00:00:02 00:00:07

从上面看到dumpabend，主要目标端dp000014被手动删除，导致dump传输数据无法lock trailfile，当目标端达到设置大小时候才会切换到下一个或者我们手动切换到下一个.下面手动切换dump从新文件开始写,即dp000015.其实dump指定写目标端extseqno.

GGSCI (cifpay02)15> alter DPLEO,etrollover
2014-04-2803:43:52 INFO OGG-01520 Rollover performed. For eachaffected output trail of Version 10 or higher format, after starting the sourceextract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT orREPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.

目标端确认配置

GGSCI (ludatou)1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPLEO 00:00:00 06:56:12

因为dp000014已经不存在了，修改从dp000015开始读取.

GGSCI (cifpay01)2> shell ls -lrt ./dirdat/*
-rw-rw-rw- 1oracle oinstall 13135 May 2 10:08 ./dirdat/dp000000
-rw-rw-rw- 1oracle oinstall 3120 May 2 12:03 ./dirdat/dp000001
-rw-rw-rw- 1oracle oinstall 9999708 May 2 12:07./dirdat/dp000002
-rw-rw-rw- 1oracle oinstall 9999967 May 2 12:07./dirdat/dp000003
-rw-rw-rw- 1oracle oinstall 9999491 May 2 12:13./dirdat/dp000005
-rw-rw-rw- 1oracle oinstall 9999677 May 2 12:13./dirdat/dp000004
-rw-rw-rw- 1oracle oinstall 9999879 May 2 12:13./dirdat/dp000006
-rw-rw-rw- 1oracle oinstall 9999975 May 2 12:13./dirdat/dp000007
-rw-rw-rw- 1oracle oinstall 9999943 May 2 12:13./dirdat/dp000008
-rw-rw-rw- 1oracle oinstall 9999749 May 2 12:21./dirdat/dp000009
-rw-rw-rw- 1oracle oinstall 9999908 May 2 12:21./dirdat/dp000011
-rw-rw-rw- 1oracle oinstall 9999743 May 2 12:21./dirdat/dp000010
-rw-rw-rw- 1oracle oinstall 9999841 May 2 12:24./dirdat/dp000012
-rw-rw-rw- 1oracle oinstall 9999908 May 2 12:24./dirdat/dp000013
-rw-rw-rw- 1 oracleoinstall 8763492 May 2 20:40./dirdat/dp000014.old
-rw-rw-rw- 1oracle oinstall 13269 May 3 03:23 ./dirdat/dp000015
GGSCI (ludatou)4> alter REPLEO,extseqno 15,extrba 0
REPLICATaltered.
GGSCI (ludatou)5> start repleo
Sending STARTrequest to MANAGER ...
REPLICAT REPLEOstarting
GGSCI (ludatou)6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPLEO 00:00:00 00:00:09

目标端验证数据是否同步
经过查看源端leo_rows表中100条数据已经成功同步到目标端.由此可以确认恢复被删除的trailfile成功.

GGSCI (ludatou)8> stats REPLEO
Sending STATSrequest to REPLICAT REPLEO ...
Start ofStatistics at 2014-05-03 03:36:13.
Replicating fromLEO.LEO_ROWS to LEO.LEO_ROWS:
*** Totalstatistics since 2014-05-03 03:35:23 ***
Total inserts 100.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 100.00
*** Dailystatistics since 2014-05-03 03:35:23 ***
Total inserts 100.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 100.00
*** Hourlystatistics since 2014-05-03 03:35:23 ***
Total inserts 100.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 100.00
*** Lateststatistics since 2014-05-03 03:35:23 ***
Total inserts 100.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 100.00
End ofStatistics.

至此目标端被删除的trailfile中数据已经恢复成功.

Goldengate Oracle Oracle 排障配置与调整数据技术 | Guang Cai Li | 2014-04-30

遭遇ora-600[kjblpkeydrmqscchk:pkey]

平台为 linux x86_64
db版本 11.2.0.3

现象节点2,3,4自动重启.有session挂住集中出现等待事件gc buffer busy acquire.
诊断为bug 14409183

具体如下：

Fixed:

The fix for 14409183 is first included in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
11.2.0.3.4 Database Patch Set Update
11.2.0.3 Bundle Patch 10 for Exadata Database
11.2.0.3 Patch 10 on Windows Platforms

Interim patches may be available for earlier versions - click here to check.
Symptoms:

Related To:

Instance May Crash
Process May Dump (ORA-7445) / Abend / Abort
Hang (Process Hang)
Internal Error May Occur (ORA-600)
ORA-600 [kjblpkeydrmqscchk:pkey]
ORA-600 [kjbldrmrpst:pkey]
ORA-600 [kjbrchkpkeywait:timeout]
ORA-600 [kclcls_8]
ORA-600 [kjbmmchkinteg:from]
ORA-600 [kclantilock_5]
ORA-600 [kclchkinteg_8]
ORA-600 [kclexpand_9]
Waits for "gc buffer busy acquire"
RAC (Real Application Clusters) / OPS
_gc_read_mostly_locking
_gc_read_mostly_locking
Description

This bug is only relevant when using Real Application Clusters (RAC)
The fix for bug 13397104 addressed some potential ORA-600 issues in RAC
*BUT* the fix was faulty and can cause a hang scenario in RAC with sessions waiting on
"gc buffer busy acquire" READING buffer's pin is not cleaned up properly during a
previous exception(ORA-8103).

This fix supersedes the 13397104 and should be used instead of that fix
for interim patches. It addresses both the symptoms of bug 13397104 and the
follow on hang issue.

Rediscovery Notes:
If you have the fix for bug 13397104 present and see the following symptoms then
this bug should be suspected:
1. Fix 13397104 is present on the system,
2. RAC env,
3. Sessions wait for a READING buffer with a dangling pin.

If you do not have the fix for bug 13397104 but see the symptoms of that bug#
then this fix should be used. Symptoms of that issue include:
- Various ora-600's such as:
ora-600 [kjblpkeydrmqscchk:pkey]
ora-600 [kjbldrmrpst:pkey]
ora-600 [kjbrchkpkeywait:timeout]
in 11.2.0.3 where fix for bug 10170926 is present
- the LE flag includes KCLL_F_ANTI (0x080)
ie. flags: 0xa1
- ORA-600 [kclcls_8] due to a mismatch from a guess-dba

Workaround
The original problem in bug 13397104 can be avoided by
disabling read mostly locking via: _gc_read_mostly_locking=false

Getting a Fix
Use one of the "Fixed" versions listed above
(for Patch Sets / bundles use the latest version available as
contents are cumulative - the "Fixed" version listed above is
the first version where the fix is included)
or
Click here for suggestions on how to get a fix for this issue

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

关于索引重建是否需要更新统计信息

今天有一个dba问我关于索引重建是否需要更新统计信息的问题，其实这个问题有点儿经验的dba都会冒出，真正的答案的是不需要更新，包括创建索引.
具体见以下sql reference的说明：


COMPUTE STATISTICS Clause

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

Index Maintenance Oracle performance tuning | Guang Cai Li | 2014-04-25 | COMPUTE STATISTICS

浅谈如何了解一个B-tree索引的变化过程

索引在我们日常维护数据库的性能中占用非常大的比例,如何维护好你的索引很大程度上关系着数据库的性能,当然了索引只是性能调整中数据model的一部分,本文只是大头根据对索引的理解结合脚本来浅浅的对b-tree索引的变化做一个描述,权当饭后谈资,有错误和需要弥补的地方还要靠大家指正.

b-tree索引是啥我就不解释了,不懂的朋友参考oracle concept或者摸下度娘.很多dba都知道,维护索引无外乎如下几种情况：

1.索引有没有
2.索引设计合不合理
3.索引碎片太多了
4.索引过大了，虚胖
5.索引聚簇因不同步

郁闷碰到故障了··rac disgroup 起不来~先去trouble shooting了.

后续补上.

Data access tuning Index Maintenance Oracle partition tab 浅谈oracle技术系列 | Guang Cai Li | 2014-04-22

Oracle Database Upgrade Path Reference List:升级数据库跨版本升级对应的bundle编号

References
Applies to:

Oracle Database – Enterprise Edition – Version 8.1.7.0 and later
Oracle Database – Standard Edition – Version 8.1.7.0 and later
Information in this document applies to any platform.
Goal

This article provides an upgrade reference list for most available Oracle Database versions, including download information, patch numbers, and links to My Oracle Support articles with detailed information.

Solution：

8.1.7.X.X

8.1.7.X.X TO 8.1.7.4.0

1. Install the 8.1.7.4.0 patchset on top of the existing 8.1.7.X.X ORACLE_HOME
Patchset number is :Patch 2376472

2. Finish the post installation steps as per the patchset README (patch_note.htm).
8.1.7.X.X TO 9.0.1.X

1. Install the 9.0.1.X software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
For installation requirements, refer to the following note :
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.0.1.X patchset on top of the existing 9.0.1.X ORACLE_HOME
Patchset numbers are :
9.0.1.1 Patch 1968474
9.0.1.2 Patch 2072050
9.0.1.3 Patch 2271678
9.0.1.4 Patch 2517300
9.0.1.5 Patch 3301544

3. Upgrade the database to 9.0.1.X
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 200838.1 : Upgrading 8.1.7.x Directly to a 9.0.1 Patch Set

REFERENCE:
8.1.7.X.X/9.0.1.X TO 9.2.0.1.0

1. Install the 9.2.0.1.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
How to Request Physical Shipment or FTP download of Software Media
For installation requirements, refer to the following note :
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Upgrade the database to 9.2.0.1.0.
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.1 Note 353912.1
8.1.7.X.X/9.0.1.X TO 9.2.0.2.0

For installation requirements refer to the following note :
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.2.0.2.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 2632931

3. Upgrade the database to 9.2.0.2.0.
Refer the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.2 Note 246010.1
List of fixes added in 9.2.0.2 Note 246017.1
8.1.7.X.X/9.0.1.X TO 9.2.0.3.0

1. Install 9.2.0.1.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
For installation requirements refer to the following note :
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.2.0.3.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 2761332

3. Upgrade the database to 9.2.0.3.0.
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.3Note 246001.1
List of fixes added in 9.2.0.3 Note 245939.1
8.1.7.X.X/9.0.1.X TO 9.2.0.4.0

1.Install 9.2.0.1.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
For installation requirements refer to the following note :
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.2.0.4.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 3095277

3. Upgrade the database to 9.2.0.4.0.
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.4 Note 245984.1
List of fixes added in 9.2.0.4 Note 245758.1
8.1.7.X.X/9.0.1.X TO 9.2.0.5.0

2. Install the 9.2.0.5.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 3501955

3. Upgrade the database to 9.2.0.5.0.
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.5 Note 263792.1
List of fixes added in 9.2.0.5 Note 263791.1
8.1.7.X.X/9.0.1.X TO 9.2.0.6.0

2. Install the 9.2.0.6.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 3948480

3. Upgrade the database to 9.2.0.6.0.
Refer to the following note for different upgrade methods :
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.6 Note 283899.1
List of fixes added in 9.2.0.6 Note 283897.1

8.1.7.X.X/9.0.1.X TO 9.2.0.7.0

1. Install 9.2.0.1.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.2.0.7.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 4163445

3. Upgrade the database to 9.2.0.7.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.7 Note 308895.1
List of fixes added in 9.2.0.7 Note 308894.1
8.1.7.X.X/9.0.1.X TO 9.2.0.8.0

1. Install 9.2.0.1.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 9.2.0.8.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 4547809

3. Upgrade the database to 9.2.0.8.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 159657.1 : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.8 Note 388281.1
List if fixes added in 9.2.0.8 Note 358776.1
8.1.7.X.X/9.0.1.X TO 10.1.0.2.0

1. Install 10.1.0.2.0 software
The software can be downloaded either from Oracle Software Delivery Cloud
or it will require a shipping SR if not available on Oracle Software Delivery Cloud.
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) .

2. Upgrade the database to 10.1.0.2.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 263809.1 : Complete checklist for manual upgrades to 10gR1 (10.1.0.x)

REFERENCE:
Known issues and alerts affecting 10.1.0.2 Note 263729.1
8.1.7.X.X/9.0.1.X TO 10.1.0.3.0

2. Install the 10.1.0.3.0 patchset on top of the existing 10.1.0.2.0 ORACLE_HOME
Patchset number is : Patch 3761843

3. Upgrade the database to 10.1.0.3.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 263809.1 : Complete checklist for manual upgrades to 10gR1 (10.1.0.x)

REFERENCE:
Known issues and alerts affecting 10.1.0.3 Note 280554.1
List of fixes added in 10.1.0.3 Note 280552.1
8.1.7.X.X/9.0.1.X TO 10.1.0.4.0

2. Install the 10.1.0.4.0 patchset on top of 10.1.0.2.0 oracle Home
Patchset number is : Patch 4163362

3. Upgrade the database to 10.1.0.4.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 263809.1 : Complete checklist for manual upgrades to 10gR1 (10.1.0.x)

REFERENCE:
Known issues and alerts affecting 10.1.0.4 Note 302663.1
List of fixes added in 10.1.0.4 Note 295763.1
8.1.7.X.X/9.0.1.X TO 10.1.0.5.0

2. Install the 10.1.0.5.0 patchset on top of the existing 10.1.0.2.0 ORACLE_HOME
Patchset number is : Patch 4505133

3. Upgrade the database to 10.1.0.5.0.
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 263809.1 : Complete checklist for manual upgrades to 10gR1 (10.1.0.x)

REFERENCE:
Known issues and alerts affecting 10.1.0.5 Note 356201.1
List of fixes added in 10.1.0.5 Note 335869.1
8.1.7.X.X/9.0.1.X TO 10.2.0.1.0

1. Install 10.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/database/index.html
If not available then a shipping SR is required
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 :
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

If your oracle database is of a lower version than 8.1.7.4.0 , then upgrade the database to 8.1.7.4.0 first.

2. Upgrade the database to 10.2.0.1.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 316889.1 : Complete checklist for manual upgrades to 10gR2

REFERENCE:
Known issues and alerts affecting 10.2.0.1 Note 316901.1
8.1.7.X.X/9.0.1.X TO 10.2.0.2.0

2. Install the 10.2.0.2.0 patchset on top of the existing10.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 4547817

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 :
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

If your oracle database is of a lower version than 8.1.7.4.0 , then upgrade the database to 8.1.7.4.0 first.t.

3. Upgrade the database to 10.2.0.2.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 316889.1 : Complete checklist for manual upgrades to 10gR2

REFERENCE:
List of fixes included in 10.2.0.2 Note 358749.1
Known issues and alerts affecting 10.2.0.2 Note 359415.1
8.1.7.X.X/9.0.1.X TO 10.2.0.3.0

1. Install 10.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/database/index.html
If not available then a shipping SR is required
Note 1071023.1> How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

2. Install the 10.2.0.3.0 patchset on top of the existing10.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 5337014

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 :
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

If your oracle database is of a lower version than 8.1.7.4.0 , then upgrade the database to 8.1.7.4.0 first.

3. Upgrade the database to 10.2.0.3.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 316889.1 Complete checklist for manual upgrades to 10gR2

REFERENCE:
List of fixes included in 10.2.0.3 Note 391116.1
Known issues and alerts affecting 10.2.0.3 Note 401435.1
8.1.7.X.X/9.0.1.X TO 10.2.0.4.0

2. Install the 10.2.0.4.0 patchset on top of the existing 10.2.0.1.0 ORACLE_HOME
Patchset number is : Patch 6810189

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 :
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

If your oracle database is of a lower version than 8.1.7.4.0 , then upgrade the database to 8.1.7.4.0 first.

3. Upgrade the database to 10.2.0.4.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 316889.1 : Complete checklist for manual upgrades to 10gR2

REFERENCE:
List of fixes included in 10.2.0.4 Note 401436.1
Known issues and alerts affecting 10.2.0.4 Note 555579.1

8.1.7.X.X/9.0.1.X TO 10.2.0.5.0

2. Install the 10.2.0.5.0 patchset on top of the existing 10.2.0.1.0 ORACLE_HOME
Patchset number is : 8202632
Patch 8202632

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 :
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

If your oracle database is of a lower version than 8.1.7.4.0 , then upgrade the database to 8.1.7.4.0 first.

3. Upgrade the database to 10.2.0.5.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 316889.1 : Complete checklist for manual upgrades to 10gR2

REFERENCE:
List of fixes included in 10.2.0.5 Note 1088172.1
Known issues and alerts affecting 10.2.0.5 Note 1087991.1
8.1.7.X.X/9.0.1.X TO 11.1.0.6.0

1. Install 11.1.0.6.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/database/index.html
If not available then a shipping SR is required
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

The minimum version required to upgrade to 11.1.0.X.X is 9.2.0.4.0.
Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix
If your oracle database is of a lower version than 9.2.0.4.0, then first upgrade the database to 9.2.0.4.0(or higher)
Oracle recommends to install the latest 9.2 patchset ie 9.2.0.8.0

2. Upgrade the database to 11.1.0.6.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 429825.1 Complete Checklist for Manual Upgrades to 11gR1

REFERENCE:
Availability and Known issues for 11.1.0.6 Note 454506.1
8.1.7.X.X/9.0.1.X TO 11.1.0.7.0

2. Install the 11.1.0.7.0 patchset on top of the existing 11.1.0.6.0 ORACLE_HOME
Patchset number is : Patch 6890831

The minimum version required to upgrade to 11.1.0.X.X is 9.2.0.4.0.
Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix
If your oracle database is of a lower version than 9.2.0.4.0 , then first upgrade the database to 9.2.0.4.0(or higher)
Oracle recommends to install the latest 9.2 patchset ie 9.2.0.8.0

3. Upgrade the database to 11.1.0.7.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 429825.1 Complete Checklist for Manual Upgrades to 11gR1

REFERENCE:
Availability and Known issues for 11.1.0.7 Note 738538.1
8.1.7.X.X/9.0.1.X TO 11.2.0.1.0

1. Install 11.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/database/index.html
If not available then a shipping SR is required
Note 1071023.1 How to Request Physical Shipment or FTP download of Software Media
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

The minimum version required to upgrade to 11.2.0.X.X is 9.2.0.8.0.

You can also first upgrade from 8.1.7.4 to 10.2.0.2(or any higher 10gr2 version), then upgrade to 11.2.0.1

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

2. Upgrade the database to 11.2.0.1.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 837570.1 : Complete Checklist for Manual Upgrades to 11gR2
Note.870814.1 : Complete checklist to upgrade the database to 11g R2 using DBUA

REFERENCE:
Availability and Known issues for 11.2.0.1 Note:880707.1
8.1.7.X.X/9.0.1.X TO 11.2.0.2.0

1. Install 11.2.0.2.0 rdbms software
11.2.0.2 patch set is a full release. There is no need to install 11.2.0.1 software
See Note 1189783.1 : Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2
The software can be downloaded from My Oracle support: Patch:10098816

Please select your platform before downloading (Click on “Platform or Language” drop down menu)

if the platform name contains (32-bit) then it’s 32-bit 11.2.0.2 client version

To install 11.2.0.2, you must download both p10098816_112020__1of7.zip
and p10098816_112020__2of7.zip

= your platform( ie for linux x86, download p10098816_112020_LINUX_1of7.zip and
p10098816_112020_LINUX_2of7.zip )

Note 1194734.1 : Where do I find that on My Oracle Support (MOS) [Video]
Note.549617.1 : How To Verify The Integrity Of A Patch/Software Download? [Video]
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

The minimum version required to upgrade to 11.2.0.X.X is 9.2.0.8.0.

You can also first upgrade from 8.1.7.4 to 10.2.0.2(or any higher 10gr2 version), then upgrade to 11.2.0.2

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

2. Upgrade the database to 11.2.0.2.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 837570.1 : Complete Checklist for Manual Upgrades to 11gR2
Note.870814.1 : Complete checklist to upgrade the database to 11gR2 using DBUA

REFERENCE:
Availability and Known issues for 11.2.0.2 Note:1179474.1
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset Note 1201253.1
8.1.7.X.X/9.0.1.X TO 11.2.0.3.0

1. Install 11.2.0.3.0 rdbms software
11.2.0.3 patch set is a full release. There is no need to install 11.2.0.1 software
See Note 1189783.1 : Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2

The software can be downloaded from My Oracle support: Patch:10404530

Please select your platform before downloading (Click on “Platform or Language” drop down menu)

if the platform name contains (32-bit) then it’s 32-bit 11.2.0.3 client version

To install 11.2.0.3, you must download both p10404530_112030__1of7.zip
and p10404530_112030__2of7.zip

= your platform( ie for linux x86, download p10404530_112030_LINUX_1of7.zip and
p10404530_112030_LINUX_2of7.zip )

The minimum version required to upgrade to 11.2.0.X.X is 9.2.0.8.0.

You can also first upgrade from 8.1.7.4 to 10.2.0.2 (or any higher 10gr2 version), then upgrade to 11.2.0.3

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

2. Upgrade the database to 11.2.0.3.0
Note 419550.1 : Different Upgrade Methods For Upgrading Your Database
Note 837570.1 : Complete Checklist for Manual Upgrades to 11gR2
Note.870814.1 : Complete checklist to upgrade the database to 11gR2 using DBUA

REFERENCE:
Availability and Known issues for 11.2.0.3 Note:1348336.1
11.2.0.3 Patch Set – List of Bug Fixes by Problem Type Note 1348303.1
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset Note 1358166.1

8.1.7.X.X/9.0.1.X TO 11.2.0.4.0

1. Install 11.2.0.4.0 rdbms software
11.2.0.4 patch set is a full release. There is no need to install 11.2.0.1 software
See Note 1189783.1 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2

The software can be downloaded from My Oracle support: Patch:13390677

Please select your platform before downloading (Click on “Platform or Language” drop down menu)

if the platform name contains (32-bit) then it’s 32-bit 11.2.0.4 client version

To install 11.2.0.4, you must download both p13390677_112040__1of7.zip
and p13390677_112040__2of7.zip

= your platform( ie for linux x86, download p13390677_112040_LINUX_1of7.zip and
p13390677_112040_LINUX_2of7.zip )

Note 1194734.1 : Where do I find that on My Oracle Support (MOS) [Video]
Note 549617.1 : How To Verify The Integrity Of A Patch/Software Download? [Video]
Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)