Oracle Database 发布计划

发表在 未分类 | Oracle Database 发布计划已关闭评论

HPUX安装Python、cx_Oracle、mysql和MySQLdb

1. 安装依赖包

expat-2.2.3-ia64_64-11.31.depot.gz
下载地址:http://hpux.connect.org.uk/hppd/hpux/Development/Tools/expat-2.2.3/

# gunzip expat-2.2.3-ia64_64-11.31.depot.gz
# swinstall -s /tmp/expat-2.2.3-ia64_64-11.31.depot expat

=======  03/26/18 14:37:32 EAT  BEGIN swinstall SESSION
        (non-interactive) (jobid=bcbsswx-0096)

      * Session started for user "root@bcbsswx".
        
      * Beginning Selection
      * Target connection succeeded for "bcbsswx:/".
      * Source:                /tmp/expat-2.2.3-ia64_64-11.31.depot
      * Targets:                bcbsswx:/
      * Software selections:
            expat.expat-RUN,r=2.2.3,a=HP-UX_B.11.31_64
      * Selection succeeded.
        
        
      * Beginning Analysis and Execution
      * Session selections have been saved in the file
        "/.sw/sessions/swinstall.last".
      * The analysis phase succeeded for "bcbsswx:/".
      * The execution phase succeeded for "bcbsswx:/".
      * Analysis and Execution succeeded.
        
        
NOTE:    More information may be found in the agent logfile using the
        command "swjob -a log bcbsswx-0096 @ bcbsswx:/".

=======  03/26/18 14:37:37 EAT  END swinstall SESSION (non-interactive)
        (jobid=bcbsswx-0096)

2. 安装过程

下载地址:http://hpux.connect.org.uk/hppd/hpux/Languages/python-2.7.13/
下载文件:python-2.7.13-ia64_64-11.31.depot.gz

# gunzip python-2.7.13-ia64_64-11.31.depot.gz
# swinstall -s /tmp/python-2.7.13-ia64_64-11.31.depot python

=======  03/26/18 14:28:23 EAT  BEGIN swinstall SESSION
        (non-interactive) (jobid=bcbsswx-0094)

      * Session started for user "root@bcbsswx".
        
      * Beginning Selection
      * Target connection succeeded for "bcbsswx:/".
      * Source:                /tmp/python-2.7.13-ia64_64-11.31.depot
      * Targets:                bcbsswx:/
      * Software selections:
            python.python-INC,r=2.7.13,a=HP-UX_B.11.31_64
            python.python-RUN,r=2.7.13,a=HP-UX_B.11.31_64
      * Selection succeeded.
        
        
      * Beginning Analysis and Execution
      * Session selections have been saved in the file
        "/.sw/sessions/swinstall.last".
      * The analysis phase succeeded for "bcbsswx:/".
      * The execution phase succeeded for "bcbsswx:/".
      * Analysis and Execution succeeded.
        
        
NOTE:    More information may be found in the agent logfile using the
        command "swjob -a log bcbsswx-0094 @ bcbsswx:/".

=======  03/26/18 14:28:43 EAT  END swinstall SESSION (non-interactive)
        (jobid=bcbsswx-0094)

# python
Python 2.7.13 (default, Aug 22 2017, 12:47:12) [C] on hp-ux11
Type "help", "copyright", "credits" or "license" for more information.
>>>

3.安装cx_Oracle

包:cx_Oracle-6.2.1.tar.gz

Oracle客户端必须要为11.2及以上

# gunzip cx_Oracle-6.2.1.tar.gz
# tar xvf cx_Oracle-6.2.1.tar
# cd cx_Oracle-6.2.1
# export ORACLE_BASE=/oracle/app/oracle
# export ORACLE_HOME=/oracle/app/oracle/product/10.2/db_1
# export SHLIB_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
# python setup.py install 

查看模块

>>> help('modules')

Please wait a moment while I gather a list of all available modules...

BaseHTTPServer      asynchat            ihooks              sgmllib
Bastion            asyncore            imaplib            sha
CGIHTTPServer      atexit              imghdr              shelve
Canvas              audiodev            imp                shlex
ConfigParser        audioop            importlib          shutil
Cookie              base64              imputil            signal
Dialog              bdb                inspect            site
DocXMLRPCServer    binascii            io                  smtpd
FileDialog          binhex              itertools          smtplib
FixTk              bisect              json                sndhdr
HTMLParser          bsddb              keyword            socket
IN                  bz2                lib2to3            sqlite3
MimeWriter          cPickle            linecache          sre
Queue              cProfile            locale              sre_compile
ScrolledText        cStringIO          logging            sre_constants
SimpleDialog        calendar            macpath            sre_parse
SimpleHTTPServer    cgi                macurl2path        ssl
SimpleXMLRPCServer  cgitb              mailbox            stat
SocketServer        chunk              mailcap            statvfs
StringIO            cmath              markupbase          string
Tix                cmd                marshal            stringold
Tkconstants        code                math                stringprep
Tkdnd              codecs              md5                strop
Tkinter            codeop              mhlib              struct
UserDict            collections        mimetools          subprocess
UserList            colorsys            mimetypes          sunau
UserString          commands            mimify              sunaudio
_LWPCookieJar      compileall          mmap                symbol
_MozillaCookieJar  compiler            modulefinder        symtable
__builtin__        contextlib          multifile          sys
__future__          cookielib          multiprocessing    sysconfig
_abcoll            copy                mutex              syslog
_ast                copy_reg            netrc              tabnanny
_bisect            crypt              new                tarfile
_codecs            csv                nis                telnetlib
_codecs_cn          ctypes              nntplib            tempfile
_codecs_hk          curses              ntpath              termios
_codecs_iso2022    cx_Oracle          nturl2path          test
_codecs_jp          datetime            numbers            textwrap
_codecs_kr          dbhash              opcode              this
_codecs_tw          decimal            operator            thread
_collections        difflib            optparse            threading
_csv                dircache            os                  time
_ctypes            dis                os2emxpath          timeit
_ctypes_test        distutils          parser              tkColorChooser
_curses            doctest            pdb                tkCommonDialog
_curses_panel      dumbdbm            pickle              tkFileDialog
_elementtree        dummy_thread        pickletools        tkFont
_functools          dummy_threading    pipes              tkMessageBox
_hashlib            email              pkgutil            tkSimpleDialog
_heapq              encodings          platform            toaiff
_hotshot            ensurepip          plistlib            token
_io                errno              popen2              tokenize
_json              exceptions          poplib              trace
_locale            fcntl              posix              traceback
_lsprof            filecmp            posixfile          ttk
_multibytecodec    fileinput          posixpath          tty
_multiprocessing    fnmatch            pprint              turtle
_osx_support        formatter          profile            types
_pyio              fpformat            pstats              unicodedata
_random            fractions          pty                unittest
_socket            ftplib              pwd                urllib
_sqlite3            functools          py_compile          urllib2
_sre                future_builtins    pyclbr              urlparse
_ssl                gc                  pydoc              user
_strptime          gdbm                pydoc_data          uu
_struct            genericpath        pyexpat            uuid
_symtable          getopt              quopri              warnings
_sysconfigdata      getpass            random              wave
_testcapi          gettext            re                  weakref
_threading_local    glob                readline            webbrowser
_warnings          grp                repr                whichdb
_weakref            gzip                resource            wsgiref
_weakrefset        hashlib            rexec              xdrlib
abc                heapq              rfc822              xml
aifc                hmac                rlcompleter        xmllib
antigravity        hotshot            robotparser        xmlrpclib
anydbm              htmlentitydefs      runpy              xxsubtype
argparse            htmllib            sched              zipfile
array              httplib            select              zipimport
ast                idlelib            sets                zlib

# 4. 安装MySQLdb模块

安装zlib
下载地址:http://hpux.connect.org.uk/hppd/hpux/Misc/zlib-1.2.8/

# gunzip zlib-1.2.11-ia64_64-11.31.depot.gz
# swinstall -s /tmp/zlib-1.2.11-ia64_64-11.31.depot zlib

安装libffi
下载地址:http://hpux.connect.org.uk/hppd/hpux/Development/Libraries/libffi-3.2.1/

# gunzip libffi-3.2.1-ia64_64-11.31.depot.gz
# swinstall -s /tmp/libffi-3.2.1-ia64_64-11.31.depot libffi

安装依赖模块 setuptools
下载地址:https://pypi.python.org/pypi/setuptools/39.0.1

# unzip setuptools-39.0.1.zip
# python setup.py install

安装MySQL模块
下载地址:http://hpux.connect.org.uk/hppd/hpux/Development/Languages/mysql-5.1.73/
安装MySQL的依赖包

# gunzip openssl-1.0.2n-ia64_64-11.31.depot.gz
# swinstall -s /tmp/openssl-1.0.2n-ia64_64-11.31.depot openssl
# gunzip ncurses-6.1-ia64_64-11.31.depot.gz
# swinstall -s /tmp/ncurses-6.1-ia64_64-11.31.depot ncurses

安装MySQL

# gunzip mysql-5.1.73-ia64_64-11.31.depot.gz
# swinstall -s /tmp/mysql-5.1.73-ia64_64-11.31.depot mysql

测试连接

# export SHLIB_PATH=/usr/local/lib/hpux64:/usr/local/mysql/lib/mysql
# export PATH=$PATH:/usr/local/mysql/bin
# mysql -uroot -pxxxxx -h192.168.139.13

安装MySQLdb模块

# export PATH=$PATH:/usr/local/mysql/bin
# unzip MySQL-python-1.2.5.zip
# cd MySQL-python-1.2.5
# python setup.py build
# python setup.py install
# 
发表在 Python | 标签为 | HPUX安装Python、cx_Oracle、mysql和MySQLdb已关闭评论

如何解决Oracle补丁冲突

最近实施11gR2的ADG,为了解决备库RAC的应用节点异常宕掉后,不自动选用另一个节点作为应用节点这个问题,根据 1613719.1文档要求,在11.2.0.4版本上需要打两个补丁,并设置_adg_instance_recovery这个隐含参数为true。

  • Bug fix for 18331944.
  • Bug fix for 19516448.
  • 下载好对应PSU版本的这两个补丁,发现打补丁时,这两个补丁冲突,情况如下:

    $ORACLE_HOME/OPatch/opatch apply 
    Oracle Interim Patch Installer version 11.2.0.3.12
    Copyright (c) 2018, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home      : /oracle/app/oracle/product/11.2.0/db
    Central Inventory : /oracle/app/oraInventory
      from          : /oracle/app/oracle/product/11.2.0/db/oraInst.loc
    OPatch version    : 11.2.0.3.12
    OUI version      : 11.2.0.4.0
    Log file location : /oracle/app/oracle/product/11.2.0/db/cfgtoollogs/opatch/19516448_Jan_29_2018_19_04_48/apply2018-01-29_19-04-48PM_1.log
    
    Verifying environment and performing prerequisite checks...
    
    Conflicts/Supersets for each patch are:
    
    Patch : 19516448
    
            Conflict with 18331944
            Conflict details:
            /oracle/app/oracle/product/11.2.0/db/lib/libserver11.a:kcb.o
            /oracle/app/oracle/product/11.2.0/db/lib/libserver11.a:krd.o
            /oracle/app/oracle/product/11.2.0/db/lib/libserver11.a:kcbz.o
    
    
    Following patches have conflicts: [  18331944  19516448 ]
    Refer to My Oracle Support Note 1299688.1 for instructions on resolving patch conflicts.
    
    UtilSession failed: Inter-conflict checking failed in apply incoming patches
    Log file location: /oracle/app/oracle/product/11.2.0/db/cfgtoollogs/opatch/19516448_Jan_29_2018_19_04_48/apply2018-01-29_19-04-48PM_1.log
    
    OPatch failed with error code 73
    

    那如何解决这两个问题呢,根据1299688.1这篇文档,找到的解决办法。在MOS上先搜索18331944这个补丁

    选择对应的PSU版本,然后”使用OPatch进行分析”

    然后添加补丁程序

    选择对应的PSU版本,然后“使用OPatch进行分析”

    确认这两个补丁在列表里,然后“分析冲突”

    然后“请求补丁程序”

    然后在下载补丁程序里,就得到了这两个冲突补丁的合并补丁,安装这个补丁即可

    打完补丁后的情况

    $ $ORACLE_BASE/OPatch/opatch lsinventory
    -bash: /oracle/app/oracle/OPatch/opatch: No such file or directory
    [oracle@cpctdb11 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
    Oracle Interim Patch Installer version 11.2.0.3.12
    Copyright (c) 2018, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home      : /oracle/app/oracle/product/11.2.0/db
    Central Inventory : /oracle/app/oraInventory
      from          : /oracle/app/oracle/product/11.2.0/db/oraInst.loc
    OPatch version    : 11.2.0.3.12
    OUI version      : 11.2.0.4.0
    Log file location : /oracle/app/oracle/product/11.2.0/db/cfgtoollogs/opatch/opatch2018-01-31_08-26-58AM_1.log
    
    Lsinventory Output file location : /oracle/app/oracle/product/11.2.0/db/cfgtoollogs/opatch/lsinv/lsinventory2018-01-31_08-26-58AM.txt
    
    --------------------------------------------------------------------------------
    Local Machine Information::
    Hostname: cpctdb11
    ARU platform id: 226
    ARU platform description:: Linux x86-64
    
    Installed Top-level Products (1): 
    
    Oracle Database 11g                                                  11.2.0.4.0
    There are 1 products installed in this Oracle Home.
    
    
    Interim patches (3) :
    
    Patch  27293201    : applied on Mon Jan 29 19:39:07 CST 2018
    Unique Patch ID:  21813166
      Created on 23 Dec 2017, 13:34:37 hrs PST8PDT
      Bugs fixed:
        19516448, 18331944
      This patch overlays patches:
        26925576
      This patch needs patches:
        26925576
      as prerequisites
    
    发表在 故障处理 | 标签为 | 如何解决Oracle补丁冲突已关闭评论

    12c口令文件存储在ASM中对DataGuard的影响

    1. 说明

    默认情况下,Oracle 12c RAC,会将口令文件存储在ASM中,这达到了所有Oracle文件由ASM统一管理,由于ASMFD的存在,避免了误删除,但是有时侯口令文件存储在ASM中,确带来了问题,以下就是这么一种情况。

    2. 业务报障

    有一天,业务报告无法连接一套Oracle 12c RAC库了,报ORA-00257,归档错误,只允许SYSDBA登录,这套Oracle 12c RAC库,前面实施了ADG,但是还没有上备份,所有归档都是靠脚本删除,具体删除脚本就不列了。

    3. 故障诊断

    3.1 检查alert日志

    2018-01-12T08:00:28.184006+08:00
    TT03: Standby redo logfile selected for thread 1 sequence 49349 for destination LOG_ARCHIVE_DEST_2
    2018-01-12T09:11:27.521632+08:00
    Non critical error ORA-48113 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_gen0_20458.trc"
    Error message: 
    Writing to the above trace file is disabled for now on...
    2018-01-12T09:11:37.330519+08:00
    Non critical error ORA-48113 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_lmhb_20514.trc"
    Error message: 
    Writing to the above trace file is disabled for now on...
    2018-01-12T09:11:40.266490+08:00
    Non critical error ORA-48113 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_mmon_20574.trc"
    Error message: 
    Writing to the above trace file is disabled for now on...
    2018-01-12T09:12:38.430984+08:00
    Non critical error ORA-48113 caught while writing to trace metadata file (.trm)
    Trace file name: "/oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_ora_32481.trc"
    Error message: 
    Writing to trace metadata is disabled for now on...
    Non critical error ORA-48113 caught while writing to trac2018-01-12T11:18:35.686499+08:00
    Errors in file /oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_tt00_20785.trc:
    ORA-01017: invalid username/password; logon denied
    2018-01-12T11:18:38.803014+08:00
    Error 1017 received logging on to the standby
    ------------------------------------------------------------
    Check that the primary and standby are using a password file
    and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
    and that the SYS password is same in the password files.
          returning error ORA-16191
    ------------------------------------------------------------
    2018-01-12T11:23:38.875352+08:00
    Errors in file /oracle/app/oracle/diag/rdbms/zhyydb/zhyydb1/trace/zhyydb1_tt00_20785.trc:
    ORA-01017: invalid username/password; logon denied
    2018-01-12T11:23:41.989872+08:00
    Error 1017 received logging on to the standby
    ------------------------------------------------------------
    Check that the primary and standby are using a password file
    and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
    and that the SYS password is same in the password files.
          returning error ORA-16191
    ------------------------------------------------------------
    2018-01-12T11:24:37.571472+08:00
    krsd_check_stuck_arch: stuck archiver condition cleared
    

    写trace文件被禁止,检查了下/oracle文件系统,发现是/oracle文件系统满了,将/oracle文件系统清理后,发现故障依旧,日志还是无法切换。

    3.2 恢复业务

    检查ASM,归档空间未满,当前数据库是ADG的主库,暂时未找到解决办法,先临时禁用DataGuard的日志传输恢复业务。

    SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH SID='*';
    

    设置log_archive_dest_state_2为defer后,发现日志可以切换了,业务恢复

    3.3 分析ORA-16191和ORA-01017

    这两个报错解释如下:

    $ oerr ora 16191
    16191, 0000, "Primary log shipping client not logged on standby"
    // *Cause:  An attempt to ship redo to standby without logging on 
    // to standby or with invalid user credentials. 
    // *Action: Check that primary and standby are using password files and that 
    //          both primary and standby have the same SYS password. 
    //          Restart primary and/or standby after ensuring that 
    //          password file is accessible and REMOTE_LOGIN_PASSWORDFILE
    //          initialization parameter is set to SHARED or EXCLUSIVE.
    
    $ oerr ora 1017
    01017, 00000, "invalid username/password; logon denied"
    // *Cause:
    // *Action:
    

    从这里可以显示,DataGuard无法传输日志到备库是因为口令不正确,难道有人修改了SYS口令,另外这套DataGuard的日志是通过DGDBA用户去传输的,难道没有生效。

    SQL> show parameter redo_transport_user
    
    NAME                                TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    redo_transport_user                  string      DGDBA
    

    测试SYS用户登录,发现通过正确的口令无法登录zhyydb1节点,但是可以登录zhyydb2节点

    $ sqlplus "sys/xxxxxx@zhyydb1 as sysdba"
    
    SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 12 11:24:49 2018
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Enter user-name:
    

    所以应该是这个原因,造成了DataGuard的问题。在分析ORA-01017过程中,发现节点1上的v$pwfile_users视图为空,检查$ORACLE_HOME/dbs中,没有发现口令文件,所以口令文件应该是存在ASM里的,查看database的配置信息,发现无法获取信息:

    $ srvctl config database -d zhyydb
    PRCD-1027 : Failed to retrieve database zhyydb
    PRCR-1070 : Failed to check if resource ora.zhyydb.db is registered
    CRS-0184 : Cannot communicate with the CRS daemon.
    

    检查grid的CRS信息,发现也是无法获取

    $ crsctl status res -t
    CRS-4535: Cannot communicate with Cluster Ready Services
    CRS-4000: Command Status failed, or completed with errors.
    

    检查CRS的后台资源,发现ora.crsd资源是offline状态

    $ crsctl status res -t -init
    ---------------------------------------------------------------
    Name          Target  State    Server      State details      
    ---------------------------------------------------------------
    Cluster Resources
    ---------------------------------------------------------------
    ......
    ora.crsd
          1        ONLINE  OFFLINE              STABLE
    ora.cssd
          1        ONLINE  ONLINE  zhyydb01    STABLE
    ......
    ---------------------------------------------------------------
    将ora.crsd这个资源拉起来后,集群状态正常
    
    $ crsctl start resource ora.crsd -init
    CRS-2672: Attempting to start 'ora.crsd' on 'zhyydb01'
    CRS-2676: Start of 'ora.crsd' on 'zhyydb01' succeeded
    

    检查数据库中的v$pwfile_users,已经可以查看到信息。再次通过口令测试SYS用户的登录,可以正常登录。

    3.4 恢复DataGuard

    恢复DataGuard的日志传输

    ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
    

    主库自动将备库缺失的归档传输过去,DataGuard恢复正常

    4. 总结

    故障的主要原因是,由于没有监控,节点1的/oracle目录满了造成ora.crsd资源异常offline。
    由于口令文件存在ASM中,数据库实例无法读取到ASM中的口令文件,造成v$pwfile_users无任何特权用户信息,备库无法登录到主库,造成主库在进行日志切换时,无法将日志传输到备库,造成主库夯住。重启ora.crsd资源后,恢复v$pwfile_users的信息,解决用户登录问题后,主库到备库的日志传输问题恢复。

    发表在 Oracle故障诊断 | 标签为 | 12c口令文件存储在ASM中对DataGuard的影响已关闭评论

    OGG集成模式下归档无法删除的特殊场景

    1. 说明

    在OGG 12.3.0.1版本上,OGG基本上是配置成集成模式了,OGG配置过程我就不再多说了,extract添加完成后,为了让RMAN删除归档时保留OGG抽取进程需要的归档,我们需要向数据库注册一下OGG的抽取进程,脚本如下:

    GGSCI> register extract ex_tt, DATABASE
    

    这其实就向以前Oracle Stream用的DBA_CAPTURE视图里添加一行记录,OGG抽取进程时时向数据库更新当前抽取的SCN位置,RMAN在删除归档时会参考DBA_CAPTURE视图里的SCN信息,OGG未抽取的归档会保留不删除。
    原理就到这里了,今天测一个场景时,碰到了个问题,OGG一般默认情况下会开BR特性,BR以前碰到过默名其妙的问题,需要START EXTNAME, BRRESET重置才会恢复,今天想着将BR禁用下,看看RMAN删除归档会有什么问题,试了这个场景,还真碰到问题了。

    2. 禁用BR过程

    未关闭BR前,查看OGG抽取进程的检查点:

    GGSCI (it12ctest01) 13> info ex_tt, showch
    
    EXTRACT    EX_TT    Last Started 2017-10-18 16:15  Status STOPPED
    Checkpoint Lag      00:00:04 (updated 00:03:54 ago)
    Log Read Checkpoint  Oracle Integrated Redo Logs
                        2017-10-19 09:27:47
                        SCN 0.44818438 (44818438)
    
    
    Current Checkpoint Detail:
    
    Read Checkpoint #1
    
      Oracle Integrated Redo Log
    
      Startup Checkpoint (starting position in the data source):
        Timestamp: 2017-09-16 11:54:25.000000
        SCN: Not available
    
      Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
        Timestamp: 2017-10-19 09:27:47.000000
        SCN: 0.44818438 (44818438)
    
      Current Checkpoint (position of last record read in the data source):
        Timestamp: 2017-10-19 09:27:47.000000
        SCN: 0.44818438 (44818438)
    
      BR Previous Recovery Checkpoint:                                 ------------------------------> BR INFO BEGIN
        Timestamp: 2017-10-18 16:15:49.043216
        SCN: Not available
    
      BR Begin Recovery Checkpoint:
        Timestamp: 2017-10-19 08:17:26.000000
        SCN: 0.44804031 (44804031)
    
      BR End Recovery Checkpoint:
        Timestamp: 2017-10-19 08:17:26.000000
        SCN: 0.44804031 (44804031)                                     -------------------------------> BR INFO END
    
    Write Checkpoint #1
    
      GGS Log Trail
    
      Current Checkpoint (current write position):
        Sequence #: 8
        RBA: 83494
        Timestamp: 2017-10-19 09:27:51.994829
        Extract Trail: ./dirdat/tt
        Seqno Length: 9
        Flip Seqno Length: No
        Trail Type: EXTTRAIL
    
    Header:
      Version = 2
      Record Source = A
      Type = 18
      # Input Checkpoints = 1
      # Output Checkpoints = 1
    
    File Information:
      Block Size = 2048
      Max Blocks = 100
      Record Length = 2048
      Current Offset = 0
    
    Configuration:
      Data Source = 3
      Transaction Integrity = 1
      Task Type = 0
    
    Status:
      Start Time = 2017-10-18 16:15:52
      Last Update Time = 2017-10-19 09:27:51
      Stop Status = G
      Last Result = 520
    

    数据库的归档信息如下:

    SQL> select name, thread#, sequence#, first_change#, first_time from v$archived_log where dest_id = 1 order by first_time desc;
    
    NAME                                                                                THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIME
    -------------------------------------------------------------------------------- ---------- ---------- ------------- -----------
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_16.626.957779199                          2        16      44822108 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_20.635.957779197                          1        20      44822091 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_15.642.957779193                          2        15      44818700 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_19.644.957779189                          1        19      44818689 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_14.652.957778145                          2        14      44818611 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_18.651.957778141                          1        18      44818576 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_17.649.957778095                          1        17      44810921 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_13.650.957778109                          2        13      44810912 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_12.647.957776121                          2        12      44804181 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_16.648.957776123                          1        16      44697435 2017/10/19
    +GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_11.641.957773897                          2        11      44662121 2017/10/19
    ......
    

    将OGG的BR给禁用,禁用过程如下:
    停掉抽取进程ex_tt
    然后在ex_tt的参数文件中添加:
    BR, BROFF
    再启动抽取进程ex_tt
    查看ex_tt的检查点,已经没有BR检查点信息了

    GGSCI (it12ctest01) 34> info ex_tt,showch
    
    EXTRACT    EX_TT    Last Started 2017-10-19 09:44  Status STOPPED
    Checkpoint Lag      00:00:00 (updated 00:00:58 ago)
    Log Read Checkpoint  Oracle Integrated Redo Logs
                        2017-10-19 09:45:20
                        SCN 0.44821859 (44821859)
    
    
    Current Checkpoint Detail:
    
    Read Checkpoint #1
    
      Oracle Integrated Redo Log
    
      Startup Checkpoint (starting position in the data source):
        Timestamp: 2017-09-16 11:54:25.000000
        SCN: Not available
    
      Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
        Timestamp: 2017-10-19 09:45:20.000000
        SCN: 0.44821859 (44821859)
    
      Current Checkpoint (position of last record read in the data source):
        Timestamp: 2017-10-19 09:45:20.000000
        SCN: 0.44821859 (44821859)
    
    Write Checkpoint #1
    
      GGS Log Trail
    
      Current Checkpoint (current write position):
        Sequence #: 9
        RBA: 1446
        Timestamp: 2017-10-19 09:45:26.394022
        Extract Trail: ./dirdat/tt
        Seqno Length: 9
        Flip Seqno Length: No
        Trail Type: EXTTRAIL
    
    Header:
      Version = 2
      Record Source = A
      Type = 18
      # Input Checkpoints = 1
      # Output Checkpoints = 1
    
    File Information:
      Block Size = 2048
      Max Blocks = 100
      Record Length = 2048
      Current Offset = 0
    
    Configuration:
      Data Source = 3
      Transaction Integrity = 1
      Task Type = 0
    
    Status:
      Start Time = 2017-10-19 09:44:47
      Last Update Time = 2017-10-19 09:45:26
      Stop Status = G
      Last Result = 400
    

    3. 测试RMAN删除归档

    禁用OGG的BR后,RMAN删除归档时,发现归档无法删除了

    RMAN> delete noprompt archivelog until SCN 44821859;
    
    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=2654 instance=tdb1 device type=DISK
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_16.648.957776123 thread=1 sequence=16
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_17.649.957778095 thread=1 sequence=17
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_18.651.957778141 thread=1 sequence=18
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_19.644.957779189 thread=1 sequence=19
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_11.641.957773897 thread=2 sequence=11
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_12.647.957776121 thread=2 sequence=12
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_13.650.957778109 thread=2 sequence=13
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_14.652.957778145 thread=2 sequence=14
    RMAN-08120: warning: archived log not deleted, not yet applied by standby
    archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_15.642.957779193 thread=2 sequence=15
    

    thread_1_seq_16.648.957776123、thread_2_seq_11.641.957773897这两个归档就包含了禁用BR前的SCN:44804031
    我切换了多次归档,发现从这最旧的这两个归档开始,RMAN就一直无法删除了,而检查OGG的检查点,Recovery Checkpoint是一直往下走的,
    所以这里我感觉是OGG的BR检查点信息,保留在DBA_CAPTURE里了,并且一直不更新,导致了RMAN无法将归档删除。
    用以下SQL查询:

    SQL> SELECT CAPTURE_NAME,
      2        CAPTURE_TYPE,
      3        STATUS,
      4        to_char(REQUIRED_CHECKPOINT_SCN, '999999999999999') as REQ_SCN,
      5        to_char(OLDEST_SCN, '999999999999999') as OLDEST_SCN
      6    FROM DBA_CAPTURE;
    
    CAPTURE_NAME            CAPTURE_TYPE STATUS  REQ_SCN          OLDEST_SCN
    ------------------------ ------------ -------- ---------------- ----------------
    OGG$CAP_EX_TT            LOCAL        DISABLED        44821858         44821858
    

    这个DBA_CAPTURE中的OGG检查点,一直不走了,RMAN删除归档就考虑这两个信息的,看下这两个字段的描述

    为了验证这个猜测,我多次切换了归档日志,上面检查DBA_CAPTURE的脚本多次执行,发现这两个SCN是一直不动的,重启抽取进程也无效,RMAN归档一直无法删除。
    然后我找了找,发现了如下OGG参数:

    TRANLOGOPTIONS INTEGRATEDPARAMS(_CKPT_RETENTION_CHECK_FREQ 300)
    

    将这个参数添加到OGG的抽取进程中,重启抽取进程后,发现DBA_CAPTURE里的这两个SCN信息终于变化了,RMAN也可以正常删除归档。

    4. 其它

    当然,最后说一下,将这个OGG的注册信息从DBA_CAPTURE中删除,也是可以解决问题的。

    发表在 GoldenGate | 标签为 | OGG集成模式下归档无法删除的特殊场景已关闭评论