Shulou(Shulou.com)06/01 Report--

Environment: centos6.8 11.2.0.3.10 (64bit)

There was no such problem, but due to some recent adjustments to the read-only library, it was found that the bug was triggered after a restart. This bug has appeared before, and it was solved by restarting the read-only library and resynchronizing, but this reboot found that I still could not evade the bug. I did not have a mos account and could not download the patch. After many tests and comparing the settings before and after the adjustment, it is found that the recovery parallelism is different. In the past, the default parallelism was used for rebooting. This time, parallel recovery was specially turned off, and the change problem was temporarily avoided after the parallelism was set to 2. Record here. In addition, some people on asktom say that adding hint (this / * + inline * /) can also be solved temporarily. For changing the solution, their reply is that hint does not take effect every time, so errors will unexpectedly occur again.

The following is the way to solve the problem by patching and upgrading reprinted:

Http://blog.itpub.net/30820196/viewspace-2132271/

I. Environment

The database environment is DG one master and three backups (maximum available mode), the operating system is CentOS release version 6.5, and the database version is 11.2.0.3.15.

2. Description

In the afternoon of December 26th, 2016, the business staff suddenly said that the system could not be used and that some web pages reported frequent errors and could not find out the data. finally, combined with the developer, the corresponding sql was extracted for analysis, and it was found that all sql had with syntax and queried standby databae. Through the metalink search, it is found that this is related to a bug. Occurs when the with statement is used in the ADG environment and the system automatically generates temporary tables

ORA-30927: Unable to complete execution due to failure in temporary table transformation reported an error. (will appear in standby database)

The description on METALINK is:

On ADG, queries that use a cursor-duration temporary table may fail with ORA-30927 errors.

Such queries use Star with Temp Transformation and subquery factoring (WITH clause).

(Bug 14143632-ora-30927 on active data guard (document ID 14143632.8))

At this time, the online database PSU has reached 11.2.0.3.15 but does not include a patch for this bug. Patch number (14143632)

For example: execute the following code, the table in with is used twice, and oracle will automatically generate a temporary table to store the table in with.

With an as (

Select object_ id aa, object_name na from dba_objects

)

Select * from a where aa in (select a .aa from a)

Temporary tables will not be generated if the tables in with are used only once.

With an as (

Select object_id aa, object_name na from dba_objects

)

Select * from a

Third, solve the problem

Download the appropriate patch at METALINK and upload it to the standby database server

View the patch as an online through opatch.

And need 19769496 this patch, after looking at the previously installed patch, we found that this patch already exists. Patches can be made directly online now.

1. Check that you can process online [oracle@oracle-test 14143632] $opatch query-all onlineOracle intermediate patch installer version 11.2.0.3.6 copyright (c) 2013, Oracle Corporation. All rights reserved. Oracle Home: / home/app/oracle/product/11.2.0/dbhome_1Central Inventory: / home/app/oraInventoryfrom: / home/app/oracle/product/11.2.0/dbhome_1/oraInst.locOPatch version: 11.2.0.3.6OUI version: 11.2.0.3.0Log file location: / home/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3016-12-27: 15-36-57 PM _ 1. Log----Patch created on 5 Jun 2015 23:20:09 hrs PST8PDTNeed to shutdown Oracle instances: falsePatch is roll-backable: truePatch is a "Patchset Update": falsePatch is a rolling patch: truePatch has sql related actions: falsePatch is an online patch: truePatch is a portal patch: falsePatch is an "auto-enabled" patch: falsePatch is translatable: falseList of platforms supported:226: Linux x86-64List of prereq patches:19769496List of overlay patches:19769496List of bugs to be fixed:14143632: QUERIES MAY FAIL WITH ORA-30927 ERRORS ON AN ADG STANDBY DATABASEThis patch is a "singleton" patch.This patch belongs to the " Db "product familyThis patch supports the patching model as" one-off "This patch supports the language" en "List of executables affected:ORACLE_HOME/bin/oracleORACLE_HOME/bin/renamedgORACLE_HOME/lib/libclntsh.so.11.1List of optional components:oracle.rdbms: 11.2.0.3.0List of optional actions:Patch the Database instances with Online Patch hpatch/bug14143632.pchPossible XML representation of the patch:14143632-- -OPatch succeeded.2. Patch online

Check for existing patches

[oracle@newfhldb1 OPatch] $opatch lsinventory

Patch 14143632

[oracle@newfhldb1 ~] $cd 14143632 / [oracle@newfhldb1 14143632] $lsetc files online README.txt** here sid username password corresponds to the sid,username of the current database and password had better make the user with dba authority * * [oracle@newfhldb1 14143632] $opatch apply online-connectString: Oracle intermediate patch installer version 11.2.0.3.6 copyright (c) 2013, Oracle Corporation. All rights reserved. Oracle Home: / usr/app/oracle/110203/v01Central Inventory: / usr/app/oracle/oraInventoryfrom: / usr/app/oracle/110203/v01/oraInst.locOPatch version: 11.2.0.3.6OUI version: 11.2.0.3.0Log file location: / usr/app/oracle/110203/v01/cfgtoollogs/opatch/14143632_Dec_27_2016_12_57_10/apply2016-12-27 hours 12-57-10 PM _ 1.log patch should only be found in the Apply / fallback in'- all_nodes' mode. Convert RAC mode to'- all_nodes' mode. Applying interim patch '14143632' to OH'/ usr/app/oracle/110203/v01'Verifying environment and performing prerequisite checks...All checks passed. Provide an email address to receive notifications about security issues, install Oracle Configuration Manager, and start it. If you use an My OracleSupport e-mail address / user name, the operation will be easier. For more information, visit http://www.oracle.com/support/policies.html. Email address / user name: an email address has not been provided to receive notifications about security issues. Whether you do not want to be notified about security issues (yes [Y], no [N]) [N]: yBacking up files... Patching component oracle.rdbms, 11.2.0.3.0. The online patch 'bug14143632.pch' is being installed and enabled on database' fhlsys'. Verifying the update...Patch 14143632 successfully appliedLog file location: / usr/app/oracle/110203/v01/cfgtoollogs/opatch/14143632_Dec_27_2016_12_57_10/apply2016-12-27-12-57-10 afternoon _ 1.logOPatch succeeded.

A total of three standby databases are installed in turn.

The patch was installed successfully, and the corresponding sql statement with with and generated temporary table was executed successfully.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.