ORA-01555 on Active Physical standby and patchset issue without Central Inventory

Posted on by admin

今天在生产库解决一个ORA-01555 on Active Physical standby问题：
Applies to:

Oracle Server – Enterprise Edition – Version: 11.2.0.2.0 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Primary and Standby databases at 11.2.0.2
Symptoms

After upgrading to 11.2.0.2 we see this ORA-1555 on the Standby Database. It is not possible to logon with system, dbsnmp or other db-users. Only sys as sysdba is working.
It’s like we are running on system’s undo Tablespace, – and not the one which has been restored..

查看系统alert日志发现:
ORA-01555 caused by SQL statement below (SQL ID: 1f9apznp767fc, Query Duration=0 sec, SCN: 0x0006.62ab3687):
select object_name synonym_name, object_name, owner object_owner, status, object_type
from sys.all_objects o
where o.owner = object_owner
and o.object_type in (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘SEQUENCE’, ‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE’, ‘TYPE’)
and o.object_name not like ‘BIN$%’

order by o.object_type, o.object_name

一个简单的查询 都导致了ora-01555 undo回滚段出现严重问题，同时出现如下问题：

ORA-00313: open failed for members of log group 15 of thread 1
ORA-00312: online log 15 thread 1: ‘/data/oracle/oradata/edw1/redo15.log’
ORA-27037: unable to obtain file status

客户端报如下错误：

同时发现mutex 等待 以及大量library cache lock 等待

通过查找metalink 发现与以下描述极其相似

This problem is introduced in 11.2.0.2 by the fix for bug 9214531

A self-hang / spin can occur when getting a library cache lock
leading to a wider hang scenario with other sessions waiting
for the blocked lock.

Rediscovery Notes:
A process spins in kglLock(),
other processes waiting for the spinning session
ORA-1555 on Active data Guard

解决方案：通过打p10018789_112020_Linux-x86-64解决此问题

打patch 过程过遇到很多问题，下面一一阐述：

opatch lsinventory raise error code 104

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.
OPatch failed with error code 104

通过了解发现此RDBMS是通过tar包过来直接relink的，缺少全局的 Inventory 所以我们需要手动创建Inventory
首先要编辑一个oraInst.loc文件，使之指向我们要创建全局inventory的目录

inventory_loc=/data/oracle/oraInventory
inst_group=oinstall

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”” ORACLE_HOME_NAME=””

我们这里采用的是 ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /data/oracle/oraInventory

‘AttachHome’ was successful.

针对rac的Inventory重建 我们需要指定crs_home 和oracle_home

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Ora_Crs_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2 CRS=true

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”<10g Oracle_Home Path>” ORACLE_HOME_NAME=”” LOCAL_NODE=’node1′ CLUSTER_NODES=node1,node2

下面的过程就显得很简单了：

解压缩补丁文件

unzip p10018789_112020_Linux-x86-64.zip

重新生成oraInventory

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/data/oracle/product/11.2.0/db1″ ORACLE_HOME_NAME=”oracle11g”

设置环境变量

export PATH=$ORACLE_HOME/OPatch:$PATH

停止redo apply

alter database recover managed standby database cancel;

关闭数据库

shutdownn immediate

打补丁

cd 10018789

opatch apply

打开数据库并开始redo应用

startup mount

alter database open read only

alter database recover managed standby database disconnect from session using current logfile;

观察日志 发现问题解决

2 thoughts on “ORA-01555 on Active Physical standby and patchset issue without Central Inventory”

  1. Cloning An Existing Oracle10g Release 1 (10.1.0.x) RDBMS Installation Using OUI [ID 559301.1]
    Modified 12-OCT-2010 Type BULLETIN Status PUBLISHED
    In this Document
    Purpose
    Scope and Application
    Cloning An Existing Oracle10g Release 1 (10.1.0.x) RDBMS Installation Using OUI
    References

    Applies to:

    Oracle Universal Installer – Version: 10.1.0.2 to 10.1.0.5 – Release: 10.1 to 10.1
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.1.0.5 [Release: 10.1 to 10.1]
    Information in this document applies to any platform.
    ***Checked for relevance on 23-07-2010***
    Purpose

    The purpose of this bulletin is to describe how to clone an existing Oracle10g Release 1 (10.1.0.x) RDBMS installation using Oracle Universal Installer (OUI).

    NOTE: This note should only be used for cloning single-instance (non-RAC) homes. It should not be used for cloning RAC, CRS or ASM homes.

    Scope and Application

    This bulletin is intended for anyone who is responsible for installing Oracle software onto a server or cloning an existing Oracle software installation.

    Please note that Perl 5.6 or higher is required when cloning Oracle10g Release 1

    NOTE: When cloning an installation to a new/separate server, it is mandatory to check that the server meets all of the prerequisite requirements. See Note:169706.1 or the Installation Guide for a list of these.
    Cloning An Existing Oracle10g Release 1 (10.1.0.x) RDBMS Installation Using OUI

    Step 1
    Install the software you want to clone into the “source” home.

    For Oracle10g Release 1, install the base software (10.1.0.2 for most platforms) then any required products from the Companion CD and any required patchsets and/or patches

    Step 2
    Make a copy of the existing (source) Oracle RDBMS installation. If the clone (target) installation is on the same server then the “cp -Rp” command could be used.

    Before copying the source installation you should shutdown any databases, listeners, agents etc. that are running from the source home (to ensure the integrity of the copy).

    For example:

    cp -Rp /u01/app/oracle/product/10.1.0 /u01/app/oracle/product/10.1.0_clone

    NOTE: run this command as the ‘root’ user (the ‘p’ option will make sure that the ownership of the files is preserved correctly)

    Alternatively, the source installation could be packed up using the “tar” command:

    cd /u01/app/oracle/product/10.1.0
    tar -cvf /tmp/source.tar .

    and then moved to the target area (on the same server, or another server) and unpacked:

    cd /u01/app/oracle/product/10.1.0_clone
    tar -xvf /tmp/source.tar

    NOTE: run this command as the ‘root’ user (to make sure that the ownership of the files is preserved correctly)

    IMPORTANT NOTES:

    Before going to step 3, it is important to spend some time checking that the file permissions on files/directories in the target home match those in the source home. Please also check that the validity of any symbolic links has been preserved.

    If you are cloning on AIX, make sure that the rootpre.sh script has been executed on the target server. This can be found on the Oracle10g Release 1 media. If Oracle10g Release 2 or Oracle11g Release 1 is already installed on the target server then rootpre.sh does not need to be executed again.

    Step 3
    Run the following command to clone the installation with the Oracle Universal Installer (OUI)

    ./runInstaller -clone -silent -ignorePreReq ORACLE_HOME=”” ORACLE_HOME_NAME=””

    NOTE: if the home you are trying to clone has products from the Companion CD installed, you must use the -ignorePreReq option as a workaround for a known bug.

    You can also specify –ignoreSysPrereqs if required (for example if you are cloning Oracle10g Release 1 on Solaris 10, RHEL 5.0, HPUX PA-RISC 11.31 or AIX5L 5.3)

    If the server has more than one Perl version installed then it may be necessary to specify the PERL5LIB environment variable so that the versions of the Perl modules match with the Perl version used.

    Note that the full path for the target ORACLE_HOME should be provided (for example, /u01/app/oracle/product/10.1.0_clone)

    Also note that the home name must be unique (that is, it must not already exist in the central inventory file /oraInventory/ContentsXML/inventory.xml).

    If no central inventory exists on the target server, OUI will create one

    Step 4
    On Unix/Linux installations, you will now need to run root.sh (as root) from the target home

    On Unix/Linux platforms, you may (optionally) run $ORACLE_HOME/install/changePerm.sh

    Notes

    For Windows, use setup.exe (instead of runInstaller) to start OUI
    Log files are created in the central inventory (/oraInventory/logs) and in the target home ($ORACLE_HOME/clone/logs)

  2. How To Clone An Existing RDBMS Installation Using OUI [ID 300062.1]
    Modified 24-OCT-2011 Type HOWTO Status PUBLISHED
    In this Document
    Goal
    Solution
    References

    Applies to:

    Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.1 – Release: 9.2 to 11.2
    Oracle Universal Installer – Version: 10.1.0.2 to 11.2.0.1 [Release: 10.1 to 11.2]
    Information in this document applies to any platform.
    Goal

    The goal of this note is to provide links to various Metalink notes regarding how to clone Oracle RDBMS installations using the Oracle Universal Installer (OUI)

    NOTE: These notes should only be used for cloning single-instance (non-RAC) homes. They should not be used for cloning RAC, CRS or ASM homes.
    Solution

    Currently, the following general notes exist regarding cloning Oracle RDBMS installations using OUI:

    Note:559301.1 “Cloning An Existing Oracle10g Release 1 (10.1.0.x) RDBMS Installation Using OUI”

    Note:559304.1 “Cloning An Existing Oracle10g Release 2 (10.2.0.x) RDBMS Installation Using OUI”

    Note:559305.1 “Cloning An Existing Oracle11g Release 1 (11.1.0.x) RDBMS Installation Using OUI”

    Note:1221705.1 “Cloning An Existing Oracle11g Release 1 (11.2.0.x) RDBMS Installation Using OUI”
    The following additional cloning notes exist:

    Note:407086.1 “Using Cloning In CRS/RAC Windows Environments To Add A Node ”

    Note:549268.1 “How To Clone An Existing RDBMS Installation Using EMGC”

    Note:558478.1 “Cloning A Database Home And Changing The User/Group That Owns It”

    Note:559863.1 “An Example Of How To Clone An Existing Oracle9i Release 2 (9.2.0.x) RDBMS Installation Using OUI”

    Note:565009.1 “FAQs on RDBMS Oracle Home Cloning Using OUI”

    Note:578805.1 “The “root.sh” Script Generated While Cloning A 9.2 Home Contains Duplicate Actions”

    Note:1061788.1 “Connect as SYSDBA on 11.2 Cloned Home Gives “ORA-1031: Insufficient Privileges” Error”

    References

    NOTE:1061788.1 – Connect as SYSDBA on 11.2 Cloned Home Gives “ORA-1031: Insufficient Privileges” Error
    NOTE:1221705.1 – Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI
    NOTE:407086.1 – USING CLONING IN CRS/RAC WINDOWS ENVIRONMENTS TO ADD A NODE
    NOTE:549268.1 – How To Clone An Existing RDBMS Installation Using EMGC
    NOTE:558478.1 – Cloning A Database Home And Changing The User/Group That Owns It
    NOTE:559301.1 – Cloning An Existing Oracle10g Release 1 (10.1.0.x) RDBMS Installation Using OUI
    NOTE:559304.1 – Cloning An Existing Oracle10g Release 2 (10.2.0.x) RDBMS Installation Using OUI
    NOTE:559305.1 – Cloning An Existing Oracle11g Release 1 (11.1.0.x) RDBMS Installation Using OUI
    NOTE:559863.1 – An Example Of How To Clone An Existing Oracle9i Release 2 (9.2.0.x) RDBMS Installation
    NOTE:565009.1 – FAQs on RDBMS Oracle Home Cloning Using OUI
    NOTE:578805.1 – The “root.sh” Script Generated While Cloning A 9.2 Home Contains Duplicate Actions

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top