redhat和oracle linux kernel对应关系

Red Hat Version/Update

RHEL – Kernel version/redhat-release string

Oracle Linux – Kernel version/release strings

 Red Hat Enterprise Linux 7

Red Hat Enterprise Linux 7 Update 4

3.10.0-693.el7
redhat-release: Red Hat Enterprise Linux Server release 7.4 (Maipo)

4.1.12-94.3.9.el7uek ^ * (x86_64 only)
3.10.0-693.el7.x86_64 ^ (x86_64 only)
redhat-release:Red Hat Enterprise Linux Server release 7.4 (Maipo)
system-release:Oracle Linux Server release 7.4
oracle-release: Oracle Linux Server release 7.4

Red Hat Enterprise Linux 7 Update 3

3.10.0-514.el7
redhat-release: Red Hat Enterprise Linux Server release 7.3 (Maipo)

4.1.12-61.1.18.el7uek ^ * (x86_64 only)
3.10.0-514.el7.x86_64 ^ (x86_64 only)
redhat-release:Red Hat Enterprise Linux Server release 7.3 (Maipo)
system-release:Oracle Linux Server release 7.3
oracle-release: Oracle Linux Server release 7.3

Red Hat Enterprise Linux 7 Update 2

3.10.0-327.el7
redhat-release: Red Hat Enterprise Linux Server release 7.2 (Maipo)

3.8.13-98.6.1.el7uek ^ * (x86_64 only)
3.10.0-327.el7.x86_64 ^ (x86_64 only)
redhat-release:Red Hat Enterprise Linux Server release 7.2 (Maipo)
system-release:Oracle Linux Server release 7.2
oracle-release: Oracle Linux Server release 7.2

 Red Hat Enterprise Linux 7 Update 1

 3.10.0-229.el7
redhat-release: Red Hat Enterprise Linux Server release 7.1 (Maipo)

 3.8.13-55.1.6.el7uek ^ * (x86_64 only)
3.10.0-229.el7.x86_64 ^ (x86_64 only)
redhat-release:Red Hat Enterprise Linux Server release 7.1 (Maipo)
system-release:Oracle Linux Server release 7.1
oracle-release: Oracle Linux Server release 7.1

 Red Hat Enterprise Linux 7 GA

 3.10.0-123.el7
redhat-release: Red Hat Enterprise Linux Server release 7.0 (Maipo)

 3.8.13-35.3.1.el7uek ^ * (x86_64 only)
3.10.0-123.el7.x86_64 ^ (x86_64 only)
redhat-release:Red Hat Enterprise Linux Server release 7.0 (Maipo)
system-release:Oracle Linux Server release 7.0
oracle-release: Oracle Linux Server release 7.0

 Red Hat Enterprise Linux 6

 Red Hat Enterprise Linux Server 6 Update 9

 2.6.32-696.el6
redhat-release: Red Hat Enterprise Linux Server release 6.9 (Santiago)

 4.1.12-61.1.28.el6uek ^ * (x86_64 only)
  2.6.39-400.294.3.el6uek ^ * (x86 only)
 2.6.32-696.el6 ^ (x86 & x86_64)
 redhat-release: Red Hat Enterprise Linux Server release 6.9 (Santiago)
 system-release: Oracle Linux Server release 6.9
 oracle-release: Oracle Linux Server release 6.9

Red Hat Enterprise Linux Server 6 Update 8

2.6.32-642.el6
redhat-release: Red Hat Enterprise Linux Server release 6.8 (Santiago)

4.1.12-37.3.1.el6uek ^ * (x86_64 only)
2.6.39-400.278.2.el6uek ^ * (x86 only)
2.6.32-642.el6 ^ (x86 & x86_64)
redhat-release: Red Hat Enterprise Linux Server release 6.8 (Santiago)
system-release: Oracle Linux Server release 6.8
oracle-release: Oracle Linux Server release 6.8

Red Hat Enterprise Linux Server 6 Update 7

2.6.32-573.el6
redhat-release: Red Hat Enterprise Linux Server release 6.7 (Santiago)

3.8.13-68.3.4.el6uek ^ * (x86_64 only)
2.6.39-400.250.7.el6uek ^ * (x86 only)
2.6.32-573.el6 ^ (x86 & x86_64)
redhat-release: Red Hat Enterprise Linux Server release 6.7 (Santiago)
system-release: Oracle Linux Server release 6.7
oracle-release: Oracle Linux Server release 6.7

 Red Hat Enterprise Linux Server 6 Update 6

 2.6.32-504.el6
redhat-release: Red Hat Enterprise Linux Server release 6.6 (Santiago)

 3.8.13-44.1.1.el6uek ^ * (x86_64 only)
2.6.39-400.215.10.el6uek ^ * (x86 only)
2.6.32-504.el6 ^ (x86 & x86_64)
redhat-release: Red Hat Enterprise Linux Server release 6.6 (Santiago)
system-release: Oracle Linux Server release 6.6
oracle-release: Oracle Linux Server release 6.6

 Red Hat Enterprise Linux Server 6 Update 5

 2.6.32-431.el6
redhat-release: Red Hat Enterprise Linux Server release 6.5 (Santiago)

 3.8.13-16.121.el6uek ^ * (x86_64 only)
2.6.39-400.211.1.el6uek ^ * (x86 only)
2.6.32-431.el6 ^ (x86 & x86_64)
redhat-release: Red Hat Enterprise Linux Server release 6.5 (Santiago)
system-release: Oracle Linux Server release 6.5
oracle-release: Oracle Linux Server release 6.5

 Red Hat Enterprise Linux Server 6 Update 4

 2.6.32-358.el6
redhat-release: Red Hat Enterprise Linux Server release 6.4 (Santiago)

 2.6.39-400.17.1.el6uek ^ *
2.6.32-358.el6 ^
redhat-release: Red Hat Enterprise Linux Server release 6.4 (Santiago)
system-release: Oracle Linux Server release 6.4
oracle-release: Oracle Linux Server release 6.4

 Red Hat Enterprise Linux Server 6 Update 3

 2.6.32-279.el6
redhat-release: Red Hat Enterprise Linux Server release 6.3 (Santiago)

 2.6.39-200.24.1.el6uek ^ *
2.6.32-279.el6 ^
redhat-release: Red Hat Enterprise Linux Server release 6.3 (Santiago)
system-release: Oracle Linux Server release 6.3
oracle-release: Oracle Linux Server release 6.3

 Red Hat Enterprise Linux Server 6 Update 2

 2.6.32-220.el6
redhat-release: Red Hat Enterprise Linux Server release 6.2 (Santiago)

 2.6.32-100.34.1.el6uek ^ *
2.6.32-220.el6 ^
redhat-release: Red Hat Enterprise Linux Server release 6.2 (Santiago)
system-release: Oracle Linux Server release 6.2
oracle-release: Oracle Linux Server release 6.2

 Red Hat Enterprise Linux Server 6 Update 1

 2.6.32-131.el6
redhat-release: Red Hat Enterprise Linux Server release 6.1 (Santiago)

 2.6.32-100.34.1.el6uek ^ *
2.6.32-131.0.15.el6 ^
redhat-release: Red Hat Enterprise Linux Server release 6.1 (Santiago)
system-release: Oracle Linux Server release 6.1
oracle-release: Oracle Linux Server release 6.1

 Red Hat Enterprise Linux Server 6 GA

 2.6.32-71.el6
redhat-release: Red Hat Enterprise Linux Server release 6.0 (Santiago)

 2.6.32-100.28.5.el6uek ^ *
2.6.32-71.el6 ^
redhat-release: Red Hat Enterprise Linux Server release 6.0 (Santiago)
system-release: Oracle Linux Server release 6.0
oracle-release: Oracle Linux Server release 6.0

 Red Hat Enterprise Linux 5

 Red Hat Enterprise Linux Server 5 Update 11

 2.6.18-398.el5
redhat-release: Red Hat Enterprise Linux Server release 5.11 (Tikanga)

 2.6.39-400.215.1.el5uek ^ *
2.6.18-398.el5 ^
2.6.18-398.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.11 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.11 (Carthage)
oracle-release: Oracle Linux Server release 5.11

 Red Hat Enterprise Linux Server 5 Update 10

 2.6.18-371.el5
redhat-release: Red Hat Enterprise Linux Server release 5.10 (Tikanga)

 2.6.39-400.209.1.el5uek ^ *
2.6.18-371.el5 ^
2.6.18-371.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.10 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.10 (Carthage)
oracle-release: Oracle Linux Server release 5.10

 Red Hat Enterprise Linux Server 5 Update 9

 2.6.18-348.el5
redhat-release: Red Hat Enterprise Linux Server release 5.9 (Tikanga)

 2.6.39-300.26.1.el5uek ^ *
2.6.18-348.el5 ^
2.6.18-348.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.9 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.9 (Carthage)
oracle-release: Oracle Linux Server release 5.9

 Red Hat Enterprise Linux Server 5 Update 8

 2.6.18-308.el5
redhat-release: Red Hat Enterprise Linux Server release 5.8 (Tikanga)

 2.6.32-300.10.1.el5uek ^ *
2.6.18-308.el5 ^
2.6.18-308.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.8 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.8 (Carthage)
oracle-release: Oracle Linux Server release 5.8

 Red Hat Enterprise Linux Server 5 Update 7

 2.6.18-274.el5
redhat-release: Red Hat Enterprise Linux Server release 5.7 (Tikanga)

 2.6.32-200.13.1.el5uek ^ *
2.6.18-274.el5 ^
2.6.18-274.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.7 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.7 (Carthage)
oracle-release: Oracle Linux Server release 5.7

 Red Hat Enterprise Linux Server 5 Update 6

 2.6.18-238.el5
redhat-release: Red Hat Enterprise Linux Server release 5.6 (Tikanga)

 2.6.32-100.26.2.el5uek ^ *
2.6.18-238.el5 ^
2.6.18-238.0.0.0.1.el5
redhat-release: Red Hat Enterprise Linux Server release 5.6 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.6 (Carthage)
oracle-release: Oracle Linux Server release 5.6

 Red Hat Enterprise Linux Server 5 Update 5

 2.6.18-194.el5
redhat-release: Red Hat Enterprise Linux Server release 5.5 (Tikanga)

 2.6.18-194.el5 ^ *
2.6.32-100.24.1.el5 #
2.6.18-194.0.0.0.3.el5
redhat-release: Red Hat Enterprise Linux Server release 5.5 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
oracle-release: Oracle Linux Server release 5.5

 Red Hat Enterprise Linux Server 5 Update 4

 2.6.18-164.el5
redhat-release: Red Hat Enterprise Linux Server release 5.4 (Tikanga)

 2.6.18-164.el5 ^ *
2.6.18-164.0.0.0.1.el5
UEK unavailable
redhat-release: Red Hat Enterprise Linux Server release 5.4 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

 Red Hat Enterprise Linux Server 5 Update 3

 2.6.18-128.el5
redhat-release: Red Hat Enterprise Linux Server release 5.3 (Tikanga)

 2.6.18-128.el5 ^ *
2.6.18-128.0.0.0.2.el5
UEK unavailable
redhat-release: Red Hat Enterprise Linux Server release 5.3 (Tikanga)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)

 Red Hat Enterprise Linux Server 5 Update 2

 2.6.18-92.el5
redhat-release: Red Hat Enterprise Linux Server release 5.2 (Tikanga)

 2.6.18-92.el5 ^ *
2.6.18-92.0.0.0.1.el5
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux Server release 5.2 (Carthage)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.2 (Carthage)

 Red Hat Enterprise Linux Server 5 Update 1

 2.6.18-53.el5
redhat-release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)

 2.6.18-53.el5 ^ *
2.6.18-53.0.0.0.1.el5
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux Server release 5.1 (Carthage)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5.1 (Carthage)

 Red Hat Enterprise Linux Server 5 GA

 2.6.18-8.el5
redhat-release: Red Hat Enterprise Linux Server release 5 (Tikanga)

 2.6.18-8.el5 ^ *
2.6.18-8.0.0.4.1.el5
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux Server release 5 (Carthage)
enterprise-release: Enterprise Linux Enterprise Linux Server release 5 (Carthage)

 Red Hat Enterprise Linux 4

 Red Hat Enterprise Linux 4 Update 9

 2.6.9-100.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 9)

 2.6.9-100.0.0.0.1.EL ^ *
 2.6.9-100.EL
 UEK unavailable
 redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 9)
 enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 9)

 Red Hat Enterprise Linux 4 Update 8

 2.6.9-89.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

 2.6.9-89.0.0.0.1.EL ^ *
2.6.9-89.EL
UEK unavailable
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 8)

 Red Hat Enterprise Linux 4 Update 7

 2.6.9-78.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 7)

 2.6.9-78.0.0.0.1.EL ^ *
2.6.9-78.EL
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 7)
enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 7)

 Red Hat Enterprise Linux 4 Update 6

 2.6.9-67.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 6)

 2.6.9-67.0.0.0.1.EL ^ *
2.6.9-67.EL
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 6)
enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 6)

 Red Hat Enterprise Linux 4 Update 5

 2.6.9-55.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 5)

 2.6.9-55.0.0.0.2.EL ^ *
2.6.9-55.EL
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 5)
enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 5)

 Red Hat Enterprise Linux 4 Update 4

 2.6.9-42.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 4

 2.6.9-42.0.0.0.1.EL ^ *
2.6.9-42.EL
UEK unavailable
redhat-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 4)
enterprise-release: Enterprise Linux Enterprise Linux AS release 4 (October Update 4)

 Red Hat Enterprise Linux 4 Update 3

 2.6.9-34.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 3)

No corresponding version

 Red Hat Enterprise Linux 4 Update 2

 2.6.9-22.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 2)

No corresponding version

 Red Hat Enterprise Linux 4 Update 1

 2.6.9-11.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant Update 1)

No corresponding version

 Red Hat Enterprise Linux 4 GA

 2.6.9-5.EL
redhat-release: Red Hat Enterprise Linux AS release 4 (Nahant)

No corresponding version

参考:Comparison of Red Hat and Oracle Linux kernel versions and release strings (Doc ID 560992.1)

发表在 Linux | 标签为 , , | 留下评论

asm磁盘组操作不当导致数据文件丢失恢复

最近遇到数据库恢复case,客户是要更换存储,在数据库mount状态把使用omf方式存储数据的asm 磁盘组通过rman copy到新的通过别名方式存储的新的asm 磁盘组的存储中,但是由于操作人员粗心,copy语句中部分目标磁盘组的数据文件别名重复了,最后执行rename file之后,导致部分数据文件彻底丢失.我们通过底层碎片扫描(参考:asm disk header 彻底损坏恢复)对于该用户的数据实现完全恢复.
因为整个过程重现比较麻烦,这里测试从一个data磁盘组中有一个omf方式存储的含有两个数据文件的表空间,通过rman copy 把这个表空间的两个文件拷贝到datanew磁盘组中,但是由于粗心把两个数据文件的别名写成一样,结果导致该表空间的一个数据文件彻底丢失的测试.

创建测试表空间
在datanew磁盘组中创建omf方式管理的xifenfei表空间,含有两个数据文件,file#分别为14和15

SQL> create tablespace xifenfei datafile '+DATA' SIZE 128m;

Tablespace created.

SQL> ALTER TABLESPACE XIFENFEI ADD DATAFILE '+DATA' SIZE 128m AUTOEXTEND ON;

Tablespace altered.

SQL> SELECT FILE_NAME,FILE_ID FROM  DBA_DATA_FILES WHERE TABLESPACE_NAME='XIFENFEI';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID
----------
+DATA/XFF/DATAFILE/xifenfei.276.961143809
        14

+DATA/XFF/DATAFILE/xifenfei.277.961143825
        15

rman copy datafile 14
通过rman copy把datafile 14拷贝到data磁盘组中,目标端为别名方式存储

RMAN> copy datafile 14 to '+datanew/xifenfei.dbf';

Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=+DATA/XFF/DATAFILE/xifenfei.276.961143809
output file name=+DATANEW/xifenfei.dbf tag=TAG20171127T082643 RECID=4 STAMP=961144006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-NOV-17


[grid@localhost ~]$ asmcmd
ASMCMD> cd datanew
ASMCMD> ls
XFF/
xifenfei.dbf
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    XFF/
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  N    xifenfei.dbf => +DATANEW/XFF/DATAFILE/XIFENFEI.256.961144003
ASMCMD> 

这里通过asmcmd的ls命令,可以看到虽然我们存储的为datanew磁盘组的别名文件,实际上是link到asm的omf方式的文件(本质上asm中的文件都是omf方式存储,只是在使用的时候体现asm的客户端程序方式不一样,是直接asm中的omf方式,还是asm中的别名).

rman copy datafile 15
通过rman copy把datafile 15 拷贝到和datafile 14别名一样的文件了

RMAN> copy datafile 15 to '+datanew/xifenfei.dbf';

Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=+DATA/XFF/DATAFILE/xifenfei.277.961143825
output file name=+DATANEW/xifenfei.dbf tag=TAG20171127T082731 RECID=5 STAMP=961144053
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-NOV-17


ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    XFF/
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  N    xifenfei.dbf => +DATANEW/XFF/DATAFILE/XIFENFEI.256.961144003
ASMCMD> cd xff
ASMCMD> ls
DATAFILE/
ASMCMD> cd datafile
ASMCMD> ls
XIFENFEI.256.961144003
ASMCMD> 

这里可以看出来,在data磁盘组中,file 14被file 15覆盖掉了

rename file
把data磁盘组中的数据文件rename 到datanew磁盘组中

SQL> alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf';

Database altered.

SQL> alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+datanew/xifenfei.dbf';
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+datanew/xifenfei.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01523: cannot rename data file to '+data/xifenfei.dbf' - file already part of database

这里我们可以看到,file 14 rename 成功,但是file 15 rename失败,因为在数据库中,已经有了别名的文件(数据文件的路径)

omf自动删除文件
查看原磁盘组datanew中,发现datafile 14被自动删除

ASMCMD> pwd
+DATA/XFF/DATAFILE
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    SYSAUX.257.942061433
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    SYSTEM.256.942061393
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    UNDOTBS1.258.942061449
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    USERS.259.942061449
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    XIFENFEI.277.961143825
ASMCMD> 

alert日志证实数据文件被删除

2017-11-27T09:05:03.054741-05:00
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf'
2017-11-27T09:05:03.114947-05:00
NOTE: Under CF enqueue, no dependency request for disk group DATANEW
Deleted Oracle managed file +DATA/XFF/DATAFILE/xifenfei.276.961143809
Completed: alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf'
2017-11-27T09:05:21.471474-05:00
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+data/xifenfei.dbf'
ORA-1511 signalled during:alter database rename file
      '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to'+datanew/xifenfei.dbf'

这里可以证实,数据文件的omf方式管理,在数据文件执行rename file的时候,会自动删除掉老的数据文件.这里悲剧已经发生,由于rman copy 覆盖了datanew磁盘组中的datafile 14,rename file又导致data磁盘组中的datafile 14被自动删除,从而使得datafile 14这个数据文件在两个磁盘组中都丢失.从常规角度来说,如果没有合适的备份该文件无法恢复.如果遭遇到oracle asm中数据文件丢失或者部分覆盖,请保护现场,联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持:Phone:13429648788    Q Q:107644445    E-Mail:dba@xifenfei.com最大限度抢救您的数据

发表在 Oracle ASM, 非常规恢复 | 标签为 , , , | 留下评论

dbconsole和sysman用户

dbconsole一直是我不怎么推荐的oracle工具,但是总有一些客户使用,这里对于dbconsole中sysman用户状态,密码修改做了一些基本的测试
sysman用户被锁

SQL>  alter user sysman account  lock;

User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log 

sysman-lock


部分错误日志提示(这里可以明显dbconsole无法正常使用是由于sysman被锁)
2

解锁sysman用户

SQL> alter user sysman account unlock;

User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log 

3


sysman解锁之后重启dbconsole恢复正常
修改sysman密码

SQL> alter user sysman identified by xifenfei;

User altered.

SQL> select account_status from dba_users where username='SYSMAN';

ACCOUNT_STATUS
--------------------------------
OPEN

重启dbconsole

[oracle@localhost ~]$  emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 

[oracle@localhost ~]$  emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log 
SQL>  select account_status from dba_users where username='SYSMAN';

ACCOUNT_STATUS
--------------------------------
LOCKED(TIMED)

由于dbconsole的密码存储在配置文件中,修改sysman密码,而配置文件的没有修改,因此使用错误是syman密码登录,超过了profile里面设置的10次,导致用户被锁.

解决sysman密码不对导致异常问题

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 

[oracle@localhost config]$ pwd
/u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/config
[oracle@localhost config]$ ls -l emoms.properties 
-rw-r--r--. 1 oracle oinstall 1669 Nov 25 15:20 emoms.properties

[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=e3299b5db6162f5d
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE


[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=xifenfei
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
SQL>  alter user sysman account unlock;

User altered.
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log 

通过人工修改$ORACLE_HOME/localhost_sid/sysman/config/emoms.properties文件中的oracle.sysman.eml.mntr.emdRepPwd=密码,oracle.sysman.eml.mntr.emdRepPwdEncrypted=false实现修改密码

[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=aa537afcbab41e05
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE

dbconsole重启之后,密码又重新被修改为加密方式,在11.2之前修改sysman密码,注意要同步修改emoms.properties中相关密码信息

sysman修改密码( 11.2 and higher)

[oracle@localhost config]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 
SQL> alter user sysman identified by xifenfei123;

User altered.
[oracle@localhost ~]$ emctl setpasswd dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Please enter new repository password: 
Repository password successfully updated.

[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log 
发表在 Oracle | 标签为 , , , , | 留下评论

move lob导致index失效

在一次数据库升级过程中,因为blug需要move lob,导致表相关index失效,这里通过实验重现
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建用户

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.

创建测试表和插入数据

SQL> conn xff/oracle
Connected.

SQL> create table t_xff (id int primary key not null,name varchar2(20),c_lob clob);

Table created.

SQL> insert into t_xff values(1,'www.xifenfei.com','http://www.xifenfei.com');

1 row created.

SQL>  insert into t_xff values(2,'www.xifenfei.com','http://www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

move lob

SQL> ALTER TABLE t_xff MOVE LOB (c_lob) store as (tablespace users);

Table altered.

SQL>  select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   UNUSABLE

SQL>  insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com');
 insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com')
*
ERROR at line 1:
ORA-01502: index 'XFF.SYS_C0011148' or partition of such index is in unusable
state

这里很明显,当我们move lob之后,表的index变为无效,插入操作无法进行

rebuind index

SQL> alter index xff.SYS_C0011148 rebuild;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

SQL> insert into t_xff values(3,'www.xifenfei.com','http://www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

这个测试就是告诫自己,做oracle 不要想当然,move lob之后,表相关的index 都会失效,需要rebuild。具体参见:
Bug 6525073 : STBH: INDEX IS IN UNUSABLE STATE AFTER A LOB COLUMN IS MOVED
ALTER TABLE MOVE LOB makes indexes unusable on the parent table (Doc ID 1228324.1)

发表在 Oracle | 标签为 , , | 留下评论

XTTS实战分享

迁移目标
源库:hp-unix RAC 裸设备 10.2.0.5
目标库:Linux RAC ASM 11.2.0.4
停机时间:8小时
数据量:16T

迁移方法
使用基于rman备份的xtts实现(因为使用裸设备,无法使用dbms_file_transfer方法实现)

迁移步骤
1)迁移之前检查

--查询无效对象
select owner, object_name, object_type from dba_objects 
where status !='VALID' order by owner, object_type, object_name;

--检查无效index
select owner, index_name, status from dba_indexes 
where status='UNUSABLE' order by 1,2;

select i.owner, i.index_name, p.partition_name, p.status 
from dba_ind_partitions p,dba_indexes i 
where p.index_name=i.index_name and p.status='UNUSABLE'
order by 1,2,3;

select i.owner,i.index_name,s.subpartition_name,s.status from 
dba_ind_subpartitions s,dba_indexes i where 
s.index_name=i.index_name and s.status='UNUSABLE'
order by 1,2,3;

--compatible Advanced Queues检查
select owner,queue_table,recipients,compatible from dba_queue_tables
where recipients='MULTIPLE' and compatible like '%8.0%';

--基于XMLSchema的XMLType对象检查
SELECT distinct OWNER FROM DBA_XML_SCHEMAS;

select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
      t.tablespace_name=p.tablespace_name and
      x.owner=u.username;

--SPATIAL空间组件对象检查
select owner,index_name from dba_indexes
where ityp_name = 'SPATIAL_INDEX';


select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;

--外部表检查
select distinct owner from DBA_EXTERNAL_TABLES;

--IOT表检查
select distinct owner from dba_tables where IOT_TYPE is not null;

--检查临时表
SELECT owner,table_name FROM DBA_TABLES WHERE 
TEMPORARY='Y' AND OWNER IN(用户列表);

--物化视图检查
select owner,count(*) from dba_mviews group by owner;

--检查永久表空间
select t.TABLESPACE_NAME TABLESPACE_NAME,count(f.FILE_ID),
sum(f.bytes/1024/1024/1024) GB
FROM dba_tablespaces t, dba_data_files f
where t.TABLESPACE_NAME=f.TABLESPACE_NAME
	and t.tablespace_name not in (根据需求排除)
	and t.contents = 'PERMANENT'
group by t.TABLESPACE_NAME
order by 2;


--检查命令进行自包含检查
EXEC sys.dbms_tts.transport_set_check('需要迁移的表空间列表',TRUE);

select * from transport_set_violations;

--回收站检查
select count(*) from dba_recyclebin;
Purge dba_recyclebin;

--检查是否存在应用户使用TSTZ 字段
select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
    || c.data_type || ' ' col
  from dba_tab_cols c, dba_objects o
 where c.data_type like '%WITH TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE' 
order by col;

--检查表空间是否加密
select tablespace_name,ENCRYPTED from dba_tablespaces;

--检查是否存在加密字段
select * from DBA_ENCRYPTED_COLUMNS;

--检查Opaque Types类型字段
select distinct owner ,DATA_TYPE from dba_tab_columns where
 owner in (需要迁移用户列表);

--检查表空间和数据文件状态
select tablespace_name,status from dba_tablespaces;

select STATUS,ONLINE_STATUS,count(*) from dba_data_files 
group by STATUS,ONLINE_STATUS;

--比对新旧环境profile是否一致
select distinct(t.pro) from
(select s.profile pro, l.profile pro2
from dba_profiles@XTTS_DBLINK s, dba_profiles l
where s.profile = l.profile(+)) t where t.pro2 is null
order by t.pro;

2)迁移之前新库创建
其他类似profile,不需要xtts迁移的东西,均可以创建

--在新环境中比对并创建角色
select 'create role '||role ||';' from dba_roles@XTTS_DBLINK 
minus
select 'create role '||role ||';' from dba_roles;

--在新环境中比对并创建用户 
select 'create user "'||a.username ||'" identified by values '''||b.password||
 ''' default tablespace USERS '|| 'temporary tablespace '||
 a.TEMPORARY_TABLESPACE||';'
from dba_users@XTTS_DBLINK a,sys.user$@XTTS_DBLINK b,
dba_users c,sys.user$   d
where a.username=b.name  and
a.username=d.name(+)
and a.username = c.username(+) and c.username is null
order by a.username;

3)停业务之前xtts迁移操作
源库
备注:由于源库的perl的版本不能满足需求,下载新的perl版本

--xtt.properties文件编辑
tablespaces=需要迁移表空间列表
platformid=4
dfcopydir=/backup/temp1/rmanback
backupformat=/backup/temp1/rmanback
stageondest=/temp1/rmanback
storageondest=+DATA/xifenfei/datafile
backupondest=/temp3/covntemp
parallel=16
rollparallel=16
getfileparallel=16

--xtts全备发起
cat /home/oracle/xttsscript/full_backup.sh

export TMPDIR=/home/oracle/tmpxtts
cd /home/oracle/xtts
export ORACLE_SID=xifenfei2
/home/oracle/perl/bin/perl xttdriver.pl -p -d

nohup /home/oracle/xttsscript/full_backup.sh > /home/oracle/xttsscript/full_backup.log &

--完成之后拷贝rmanconvert.cmd到共享目录
cp  /home/oracle/tmpxtts/rmanconvert.cmd  /backup/temp1/tempfile/

目标库

--拷贝源库的rmanconvert.cmd到目标库
cp /temp1/tempfile/rmanconvert.cmd /home/oracle/tmpxtts/

--目标库启动到mount
shutdown immediate;
startup mount

--xtts转换数据文件
cd /home/oracle/xttsscript/
cat > /home/oracle/xttsscript/full_restore.sh

export TMPDIR=/home/oracle/tmpxtts
export ORACLE_SID=xifenfei2
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -c -d

chmod +x /home/oracle/xttsscript/full_restore.sh
nohup /home/oracle/xttsscript/full_restore.sh > /home/oracle/xttsscript/full_restore.log &

源库第一次增量备份

cd /home/oracle/xttsscript
cat /home/oracle/xttsscript/incre_backup.sh

export XTTDEBUG=1
export TMPDIR=/home/oracle/tmpxtts
cd /home/oracle/xtts
export ORACLE_SID=xifenfei2
/home/oracle/perl/bin/perl xttdriver.pl -i -d

nohup /home/oracle/xttsscript/incre_backup.sh > /home/oracle/xttsscript/incre_backup1.log &

--拷贝文件到共享目录
cp /home/oracle/tmpxtts/xttplan.txt /backup/temp1/tempfile/
cp /home/oracle/tmpxtts/tsbkupmap.txt /backup/temp1/tempfile/

目标库第一次增量恢复

--拷贝共享目录文件
cp /temp1/tempfile/xttplan.txt /home/oracle/tmpxtts/xttplan.txt
cp /temp1/tempfile/tsbkupmap.txt /home/oracle/tmpxtts/tsbkupmap.txt

--xtts增量还原
cd /home/oracle/xttsscript
cat  /home/oracle/xttsscript/ince_restore.sh

export XTTDEBUG=1
export TMPDIR=/home/oracle/tmpxtts
export ORACLE_SID=xifenfei1
cd /home/oracle/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r -d

nohup /home/oracle/xttsscript/ince_restore.sh > /home/oracle/xttsscript/ince_restore1.log &

源端更新scn

cd /home/oracle/xttsscript/
cat /home/oracle/xttsscript/getscn.sh

export XTTDEBUG=1
export TMPDIR=/home/oracle/tmpxtts
cd /home/oracle/xtts
export ORACLE_SID=xifenfei2
date
/home/oracle/perl/bin/perl xttdriver.pl -s -d

nohup /home/oracle/xttsscript/getscn.sh >> /home/oracle/xttsscript/getscn.log &

在停业务之前,依次进行上面三步的增量备份,恢复,更新scn,一直持续到最后停业务,表空间只读。
4)正式停业务后xtts操作
原库

--设置表空间只读
select 'alter tablespace '||t.TABLESPACE_NAME||' read only;'
FROM dba_tablespaces t, dba_data_files f
where t.TABLESPACE_NAME=f.TABLESPACE_NAME
	and t.tablespace_name not in (选择排除的表空间)
	and t.contents = 'PERMANENT'
group by t.TABLESPACE_NAME
order by count(f.FILE_ID);

--xtts增量备份
cd /home/oracle/xttsscript
nohup /home/oracle/xttsscript/incre_backup.sh > /home/oracle/xttsscript/incre_backup_end.log &

--拷贝文件到共享目录
cp /home/oracle/tmpxtts/xttplan.txt /backup/temp1/tempfile/
cp /home/oracle/tmpxtts/tsbkupmap.txt /backup/temp1/tempfile/

目标库

--拷贝共享目录文件
cp /temp1/tempfile/xttplan.txt /home/oracle/tmpxtts/xttplan.txt
cp /temp1/tempfile/tsbkupmap.txt /home/oracle/tmpxtts/tsbkupmap.txt

cd /home/oracle/xttsscript
nohup /home/oracle/xttsscript/ince_restore.sh > /home/oracle/xttsscript/ince_restore_end.log &

5)元数据迁移

--源库(导出表空间和用户元数据)
nohup expdp "'/ as sysdba'" parfile=/archive2/dmpdir/expdp_xtts_tbs_metadata.par &

nohup expdp "'/ as sysdba'" parfile=/archive2/dmpdir/expdp_xtts_users_metadata.par &

--目标库(导入表空间和用户元数据)
nohup impdp "'/ as sysdba'"  parfile=/temp3/dmpdir/impdp_xtts_tbs_metadata.par &

nohup impdp "'/ as sysdba'" parfile=/temp3/dmpdir/impdp_xtts_users_metadata.par &

6)后续操作

--设置表空间读写
select 'alter tablespace '||t.TABLESPACE_NAME||' read write;'
FROM dba_tablespaces t, dba_data_files f
where t.TABLESPACE_NAME=f.TABLESPACE_NAME
	and t.tablespace_name not in (排除表空间列表)
	and t.contents = 'PERMANENT'
group by t.TABLESPACE_NAME
order by count(f.FILE_ID);

--编译无效对象
@?/rdbms/admin/utlprp.sql 32

--对比无效对象
select r.owner, r.object_type, r.remote_cnt Source_Cnt, l.local_cnt Target_Cnt
from ( select owner, object_type, count(owner) remote_cnt
from dba_objects@XTTS_DBLINK
where owner not in
(select name
from system.logstdby$skip_support
where action=0) group by owner, object_type ) r
, ( select owner, object_type, count(owner) local_cnt
from dba_objects
where owner not in
(select name
from system.logstdby$skip_support
where action=0) group by owner, object_type ) l
where l.owner (+) = r.owner
and l.object_type (+) = r.object_type 
and nvl(l.local_cnt,-1) != r.remote_cnt
order by 1, 3 desc;

--对比sequence大小
select * from(
select a.SEQUENCE_OWNER,a.sequence_name,a.last_number prod_number, 
b.last_number dr_number,(b.last_number - a.last_number) gap_than_zero
 from dba_sequences@XTTS_DBLINK a,dba_sequences b
  where a.sequence_owner not in 
 ('SYS','SYSTEM','XDB','WMSYS','TSMSYS','SYSMAN','PRECISE1',
'ORDSYS','OUTLN','OLAPSYS','ORDPLUGINS','MDSYS','EXFSYS',
'DMSYS','DSG','DBSNMP','PRECISE2','SI_INFORMTN_SCHEMA','SPA','TSMSYS','PUBLIC','GOLDENGATE',
'ORDDATA','PRECISE4','PRECISE3','CTXSYS','SCOTT','PERFSTAT')
and a.sequence_owner=b.sequence_owner (+)
and a.sequence_name=b.sequence_name(+)
 order by 5,1,2 desc) where gap_than_zero < 0 ;

--检查无效index
select owner, index_name, status from dba_indexes 
where status='UNUSABLE' order by 1,2;

select i.owner, i.index_name, p.partition_name, p.status 
from dba_ind_partitions p,dba_indexes i 
where p.index_name=i.index_name and p.status='UNUSABLE'
order by 1,2,3;

select i.owner,i.index_name,s.subpartition_name,s.status from 
dba_ind_subpartitions s,dba_indexes i where 
s.index_name=i.index_name and s.status='UNUSABLE'
order by 1,2,3;

--创建迁移之前删除的物化视图,index,临时表等

由于xtts迁移是一种物理方法结合逻辑方法的迁移,本身是一个复杂的过程,这里只是列举出来了主要的操作过程和步骤,可能涉及很多细节工作需要考虑比如public对象,统计信息,dblink,权限等所有逻辑迁移中需要注意的问题,在这里易于需要注意,另外还要关注xtts本身物理转换所带来的各种bug.
特别感谢Lunar,Oracle的Gary Zhou,存储的黄长老等所有朋友的帮助和支持

发表在 Oracle | 标签为 , , | 留下评论