Feed aggregator

New Performance Analytics Release

Rittman Mead Consulting - 4 hours 46 min ago

Poor performance is one of the most common complaints we hear upon arrival to customer sites, and often for good reason. When users have time to “make the rounds” before the system returns a result (if it returns a result at all), they have the right to be frustrated.

Unfortunately, tracking down the origins of performance issues can be extremely difficult, as the root cause could be one or more problems across several distinct systems. Without a comprehensive view of the complete technology stack, an administrator will effectively be guessing.

But we are all in the business of analytics. We should be taking an analytical approach to drive our decisions, and provide evidence to support them. Performance data should be collected, investigated, and then acted upon. But an analytical approach raises two distinct questions, “What data can we collect that will help us identify performance issues, and once found, what actions can we take”?

Enter Performance Analytics

Performance Analytics is an application that collects data from all layers of the stack in your environment, and makes it readily available for study and investigation. By collecting data from OBIEE Usage Tracking, operating system metrics, database sessions, and a myriad of other sources, our application provides administrators with an all-encompassing view of their system. It has never been easier to identify the causes of poor performance.

However, simply identifying a cause of poor performance isn’t all that’s required. In fact, it’s almost useless without knowing how to resolve the issue. Rittman Mead has expertise ranging from databases and data warehouses to front end browser applications, and everything in between. Our team will help you understand the problems discovered by Performance Analytics and show you how to fix them.

With the newest release of Performance Analytics, improving the performance of your BI ecosystem is easier than ever. No longer will your users sit around waiting for reports, no longer will you need to guess at configuration settings, and no longer will you be left with no leads to investigate when users desire a better experience.

New Features Simple and Lightweight Installation

Performance Analytics is now deployed using Docker, requiring absolutely no software installation on any machine other than the monitoring server.

Upgraded and enhanced software.

The open source technologies included in Performance Analytics have come a long way since its inception, and this release provides their latest and greatest versions, as well as our own improvements that increase performance and reliability.

Alerts and Notifications.

Receive notifications based on failed OBIEE system components, a violated performance threshold, or any other identifiable condition found in your data. Be made aware of adverse performance conditions the instant they occur.
And many more...

Learn More

We will be releasing more exciting news about Performance Analytics over the following weeks, so be sure to subscribe to our blog and newsletter to stay up to date!

Visit our Performance Analytics page here.
To request a demo or ask any questions about Performance Analytics, call or email us:

P (US): (888) 631-1410
E (US): info@rittmanmead.com
P (UK): +44 (0) 1273 911 268
E (UK): info@rittmanmead.com
Categories: BI & Warehousing

I don’t know (yet)

Jonathan Lewis - 6 hours 52 min ago

Here’s a question that came to mind while reading a recent question on the OTN database forum. It’s a question to which I don’t know the answer and, at present, I don’t really want to bother modelling at present – although if I were on a customer site and this looked like a likely explanation for a performance anomaly it’s the sort of thing I would create a model for.

If I have a query that runs parallel and does a “hash join buffered” (see, for example, this URL), it’s possible that the processes creating the build table will manage to create the build table in memory without spilling to disc but then find themselves unable to keep the incoming probe table data in memory and spill it to disc before re-reading it to do the join and forward the results to the parent process.

Here’s the plan from the URL above, showing rowsource execution stats:


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   | Max-Tmp |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |          |        |      |            |  70101 |00:00:00.17 |      12 |      0 |      0 |       |       |          |         |
|   1 |  PX COORDINATOR         |          |      1 |          |        |      |            |  70101 |00:00:00.17 |      12 |      0 |      0 |       |       |          |         |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 |      0 | 00:00:01 |  Q1,02 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |
|*  3 |    HASH JOIN BUFFERED   |          |      2 | 00:00:01 |  Q1,02 | PCWP |            |  70101 |00:00:00.28 |       0 |    310 |    310 |  5952K|  1953K|     2/0/0|    2048 |
|   4 |     PX RECEIVE          |          |      2 | 00:00:01 |  Q1,02 | PCWP |            |  70101 |00:00:00.08 |       0 |      0 |      0 |       |       |          |         |
|   5 |      PX SEND HASH       | :TQ10000 |      0 | 00:00:01 |  Q1,00 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |
|   6 |       PX BLOCK ITERATOR |          |      2 | 00:00:01 |  Q1,00 | PCWC |            |  70101 |00:00:00.05 |    1031 |   1005 |      0 |       |       |          |         |
|*  7 |        TABLE ACCESS FULL| T1       |     26 | 00:00:01 |  Q1,00 | PCWP |            |  70101 |00:00:00.02 |    1031 |   1005 |      0 |       |       |          |         |
|   8 |     PX RECEIVE          |          |      2 | 00:00:01 |  Q1,02 | PCWP |            |  70102 |00:00:00.06 |       0 |      0 |      0 |       |       |          |         |
|   9 |      PX SEND HASH       | :TQ10001 |      0 | 00:00:01 |  Q1,01 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |
|  10 |       PX BLOCK ITERATOR |          |      2 | 00:00:01 |  Q1,01 | PCWC |            |  70102 |00:00:00.04 |    1031 |   1005 |      0 |       |       |          |         |
|* 11 |        TABLE ACCESS FULL| T2       |     26 | 00:00:01 |  Q1,01 | PCWP |            |  70102 |00:00:00.01 |    1031 |   1005 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note that we have a “hash join buffered” at operation 3, which means incoming data from t2 (the probe table) was buffered before being joined; but we can see 310 writes and reads to temporary space due to that operation. That’s actually t2 spilling from the buffer to disc and then being read back from disc after the t2 scan (at operation 11) completes.

If this pattern of activity appears how is it reflected in the statistics?

Because the build table was completely built in memory you could argue for an “optimal workarea execution”; but because the probe table was temporarily spilled to disc you could argue for a “one-pass workarea operation”. In this case I was able to say that the writes and reads for operation 3 were the t2 table spilling to disc  because (a) I understand (mostly) how parallel hash joins work, (b) I had trace files I could check for timing, and (c) the O/1/M column of the output show 2 optimal operations and no one-pass operations.

The question to which I don’t (yet) know the answer is this: is the I/O caused by this spill to disc captured in v$pgastat under the stats:

NAME                                              VALUE UNIT
-------------------------------------------- ---------- ------------
extra bytes read/written                              0 bytes
cache hit percentage                                100 percent

If so, then you end up with questions like the one on OTN:

How come OEM reports my PGA cache Hit Ratio at 76% when I’ve got so much more memory than seems necessary ?

With the follow-up comment:

All my workarea executions were optimal

Footnote

Though I haven’t examined it recently, I remember noting in the past that the buffer allocation for the incoming probe table was typically a small fraction (often 1/8th or 1/16th) of the size of the buffer allocated for the build table. This may help to explain why you could end up doing a lot of I/O without exhausting the available memory.

I’m probably going to resurrect the example from the linked blog note and check the effects before I publish this post.

 


ORA-06502 PLSQL numeric or value error Solution

Complete IT Professional - 8 hours 46 min ago
Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article. ORA-06502 Cause The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things: A value is being assigned to a numeric variable, but the value is […]
Categories: Development

One bit of news in Trump’s speech

DBMS2 - Tue, 2017-02-28 23:26

Donald Trump addressed Congress tonight. As may be seen by the transcript, his speech — while uncharacteristically sober — was largely vacuous.

That said, while Steve Bannon is firmly established as Trump’s puppet master, they don’t agree on quite everything, and one of the documented disagreements had been in their view of skilled, entrepreneurial founder-type immigrants: Bannon opposes them, but Trump has disagreed with his view. And as per the speech, Trump seems to be maintaining his disagreement.

At least, that seems implied by his call for “a merit-based immigration system.”

And by the way — Trump managed to give a whole speech without saying anything overtly racist. Indeed, he specifically decried the murder of an Indian-immigrant engineer. By Trump standards, that counts as a kind of progress.

Categories: Other

Coordination, the underused “C” word

DBMS2 - Tue, 2017-02-28 22:34

I’d like to argue that a single frame can be used to view a lot of the issues that we think about. Specifically, I’m referring to coordination, which I think is a clearer way of characterizing much of what we commonly call communication or collaboration.

It’s easy to argue that computing, to an overwhelming extent, is really about communication. Most obviously:

  • Data is constantly moving around — across wide area networks, across local networks, within individual boxes, or even within particular chips.
  • Many major developments are almost purely about communication. The most important computing device today may be a telephone. The World Wide Web is essentially a publishing platform. Social media are huge. Etc.

Indeed, it’s reasonable to claim:

  • When technology creates new information, it’s either analytics or just raw measurement.
  • Everything else is just moving information around, and that’s communication.

A little less obvious is the much of this communication could be alternatively described as coordination. Some communication has pure consumer value, such as when we talk/email/Facebook/Snapchat/FaceTime with loved ones. But much of the rest is for the purpose of coordinating business or technical processes.

Among the technical categories that boil down to coordination are:

  • Operating systems.
  • Anything to do with distributed computing.
  • Anything to do with system or cluster management.
  • Anything that’s called “collaboration”.

That’s a lot of the value in “platform” IT right there. 

Meanwhile, in pre-internet apps:

  • Some of the early IT wins were in pure accounting and information management. But a lot of the rest were in various forms of coordination, such as logistics and inventory management.
  • The glory days of enterprise apps really started with SAP’s emphasis on “business process'”. (“Business process reengineering” was also a major buzzword back in the day.)

This also all fits with the “route” part of my claim that “historically, application software has existed mainly to record and route information.”

And in the internet era:

  • “Sharing economy” companies, led by Uber and Airbnb, have created a lot more shareholder value than the most successful pure IT startups of the era.
  • Amazon, in e-commerce and cloud computing alike, has run some of the biggest coordination projects of all.

This all ties into one of the key underlying subjects to modern politics and economics, namely the future of work.

  • Globalization is enabled by IT’s ability to coordinate far-flung enterprises.
  • Large enterprises need fewer full-time employees when individual or smaller-enterprise contractors are easier to coordinate. (It’s been 30 years since I drew a paycheck from a company I didn’t own.)
  • And of course, many white collar jobs are being entirely automated away, especially those that can be stereotyped as “paper shuffling”.

By now, I hope it’s clear that “coordination” covers a whole lot of IT. So why do I think using a term with such broad application adds any clarity? I’ve already given some examples above, in that:

  • “Coordination” seems clearer than “communication” when characterizing the essence of distributed computing.
  • “Coordination” seems clearer than “communication” if we’re discussing the functioning of large enterprises or of large-enterprise-substitutes.

Further — even when we focus on the analytic realm, the emphasis on “coordination” has value. A big part of analytic value comes in determining when to do something. Specifically that arises when:

  • Analytics identifies a problem that just occurred, or is about to happen, allowing a timely fix.
  • Business intelligence is using for monitoring, of impending problems or otherwise, as a guide to when action is needed.
  • Logistics of any kind get optimized.

I’d also say that most recommendation/personalization fits into the “coordination” area, but that’s a bit more of a stretch; you’re welcome to disagree.

I do not claim that analytics’ value can be wholly captured by the “coordination” theme. Decisions about whether to do something major — or about what to do — are typically made by small numbers of people; they turn into major coordination exercises only after a project gets its green light. But such cases, while important, are pretty rare. For the most part, analytic results serve as inputs to business processes. And business processes, on the whole, typically have a lot to do with coordination.

Bottom line: Most of what’s valuable in IT relates to communication or coordination. Apparent counterexamples should be viewed with caution.

Related links

Categories: Other

reading files in a directory -- how to get a list of available files.

Tom Kyte - Tue, 2017-02-28 22:26
How do I read files from a certain directory with PL/SQL, without knowing the exact name ? My program must interface with another system which puts files in a directory on the server. UTL_FILE only reads a file when you know the name of the f...
Categories: DBA Blogs

Executing OS commands from PLSQL code

Tom Kyte - Tue, 2017-02-28 22:26
Hi, Is there a native Oracle solution to execute OS commands via PLSQL or SQL? I searched on the web and do not find an easy or straight forward technique. Can you please help? Assuming OS is Linux and the command to execute is any executable ...
Categories: DBA Blogs

returning text from between specific characters

Tom Kyte - Tue, 2017-02-28 22:26
I have some text in a column stored like below. with time/date stamps, worker names, position and team in a long string. "Verified at HH:MMPM on DD/MM/YYYY by FirstName Surname Job Title: Worker, Organisation: Area 1 Team" I would like to retu...
Categories: DBA Blogs

insert data from multiple lines of one table to one line several column of another table.

Tom Kyte - Tue, 2017-02-28 22:26
Hello I am stuck with one issue. I have one table with several lines. I would like to consolidate those data into another table In sum, i want to insert data from multiple lines of one table to one line several column of another table. My ta...
Categories: DBA Blogs

Unable to load the data in external table: KUP-04020: found record longer than buffer size supported, 524288

Tom Kyte - Tue, 2017-02-28 22:26
Hi, I have a scenario, where I have to load the data into External table. I have the file with 677KB size and record delimiter for file is '^row^' When i tried to load the data , i am getting below error. ORA-29913: error in executing ODCIE...
Categories: DBA Blogs

Delete from AUD$

Pete Finnigan - Tue, 2017-02-28 22:26
We have been working on a new audit trail toolkit / product for some time now which is called PFCLATK - The PFCL means "P"ete"F"innigan."C"om "L"imited so most of our tools products end up with a consistent name starting PFCL....[Read More]

Posted by Pete On 28/02/17 At 01:06 PM

Categories: Security Blogs

Goldengate 12c OGG-01117 core dumped

Michael Dinh - Tue, 2017-02-28 17:47

This probably applies to other versions as well.

Don’t you love it when there’s a simple solution?

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (test1) 1> create subdirs

Creating subdirectories under current directory /oracle/12.2.0/ggs01

Parameter files                /oracle/12.2.0/ggs01/dirprm: created
Report files                   /oracle/12.2.0/ggs01/dirrpt: created
Checkpoint files               /oracle/12.2.0/ggs01/dirchk: created
Process status files           /oracle/12.2.0/ggs01/dirpcs: created
SQL script files               /oracle/12.2.0/ggs01/dirsql: created
Database definitions files     /oracle/12.2.0/ggs01/dirdef: created
Extract data files             /oracle/12.2.0/ggs01/dirdat: created
Temporary files                /oracle/12.2.0/ggs01/dirtmp: created
Credential store files         /oracle/12.2.0/ggs01/dircrd: created
Masterkey wallet files         /oracle/12.2.0/ggs01/dirwlt: created
Dump files                     /oracle/12.2.0/ggs01/dirdmp: created


GGSCI (test1) 2> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (test1) 3> add credentialstore

Credential store created in ./dircrd/.

GGSCI (test1) 4> alter credentialstore add user ggs_user alias ggs_user
Password:

Credential store in ./dircrd/ altered.

GGSCI (test1) 5> dblogin useridalias ggs_user

Source Context :
  SourceModule            : [ggapp.util.pcs]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150330/oggcore/OpenSys/src/gglib/ggapp/pcsutl.c]
  SourceFunction          : [AbendHandler]
  SourceLine              : [1036]
  ThreadBacktrace         : [22] elements
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f746dc0066b]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f746dbfa734]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(_MSG_ERR_SIGNAL_RECEIVED(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition)+0x3a) [0x7f746dbde41d]]
                          : [ggsci() [0x52a977]]
                          : [/lib64/libpthread.so.0(+0xf850) [0x7f7469f72850]]
                          : [/lib64/libpthread.so.0(pthread_mutex_lock+0x4) [0x7f7469f6c444]]
                          : [ggsci(ggs::gglib::MultiThreading::Mutex::Lock()+0x9) [0x556bb9]]
                          : [ggsci(CContextItem::operator char const*() const+0x1d) [0x576b03]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::initConnectionLogon(ggs::gglib::ggdbapi::DBAuthParams const&)+0x3d0) [0x5d13b0]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::openDataSource(ggs::gglib::ggdbapi::DBAuthParams const&)+0x28) [0x5d1578]]
                          : [ggsci() [0x57c20c]]
                          : [ggsci(gl_db_login(char const*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)11>&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12>&, short, char (&) [2048])+0x61) [0x57c7a1]]
                          : [ggsci() [0x6437a6]]
                          : [ggsci(GGSCIDB_get_command(char const*, char const*, char const*, short, short, char (&) [2048])+0x1a0) [0x63ef00]]
                          : [ggsci(do_cmd(char*, unsigned long, char*, unsigned long)+0xe05) [0x6632a5]]
                          : [ggsci() [0x66746b]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x5550c0]]
                          : [ggsci(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x555e0d]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x555ef1]]
                          : [ggsci(main+0x3b) [0x667a7b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7f7465cdfc36]]
                          : [ggsci() [0x4f5b89]]

2017-02-28 20:52:47  ERROR   OGG-01117  Received signal: Segmentation violation (11).

2017-02-28 20:52:47  ERROR   OGG-01668  PROCESS ABENDING.
Segmentation fault (core dumped)

==================================================

Opatch Apply Instructions: Ensure that you set the ORACLE_HOME environment variable to the Oracle GoldenGate home.

I had just completed patching Goldengate and forgot to set ORACLE_HOME back to DB_HOME.

oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/12.2.0/ggs01
ACS_HOME=/oracle/acsprod

==================================================

oracle@test1:/opt/oracle/12.2.0/ggs01$ source ~/.orcl
The Oracle base remains unchanged with value /opt/oracle
oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
ACS_HOME=/oracle/acsprod

==================================================

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (test1) 1> dblogin useridalias ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

Updating the Oracle EM12c Agent Configuration Properties in Bulk

Arun Bavera - Tue, 2017-02-28 14:41
 
1. Goto Enterprise->Job ->Activity->Choose from ‘create Job’ list -> Agent Configuration Operation ->Click Go
image
Give job name and Add all the Agent Targets you want to modify
clip_image004
Goto Parameter tab and Add Custom properties or Change the existing listed properties values.
For example:
Name:     Log.log.level
Value:     ERROR
imageThis job does not require any credentials.
Click Submit:









Categories: Development

Oracle 12c – How to Recover lost DataGuard Broker Configuration Files

Yann Neuhaus - Tue, 2017-02-28 07:53

If you are using RMAN to back up your database, you are already doing the right thing. However, RMAN does not take care of everything. e.g. it is not backing up the following things, to mention just a few.

  • Oracle Home
  • Grid Infrastructure Home
  • Data Guard broker files
  • Password File
  • SQL*Net file like ldap.ora, sqlnet.ora, tnsnames.ora and listener.ora
  • /etc/oratab
  • OS audit files
  • Wallets
  • /etc/sysctl.conf and limits.conf
  • OLR and OCR
  • Voting Disks
  • ASM Metadata
  • passwd, shadow, group
  • RMAN scripts itself ksh/rcv (some exceptions when using the RMAN catalog)
  • crontab

There are for sure many more, but in this article I would like to take a closer look at the Data Guard broker configuraiton files. The DataGuard Broker Configuration Files are quite important files. The contain entries that describe the state and the properties of the DataGuard configuration like the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration. The broker configuration files are very small, in my case they have only 12K. Don’t expect them to grow very big. They usually stay at this size.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 09:27 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 09:34 dr2DBIT121_SITE1.dat

You can have a maximum of two different copies which can be configured via the DG_BROKER_CONFIG_FILEn (where n = 1, 2) parameter. Maybe, maximum is not the correct word in this context, because if you don’t specify the broker file location, Oracle still creates one in the default directory which is OS dependent. On Linux they end up in $ORACLE_HOME/dbs/.

However, not like you might expect, the broker configuration files are not a 1:1 mirror like e.g. redo log members or controlfiles. They are individual copies and they maintain the last known good state of the configuration. You can check it quite easily yourself by editing e.g. the FastStartFailoverThreshold and check the time stamps of the broker files afterwards.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
Property "faststartfailoverthreshold" updated

-- Primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:27 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:34 dr2DBIT121_SITE1.dat

-- Standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:34 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 09:27 dr2DBIT121_SITE2.dat

As you can see here, the broker configuration files have two different time stamps. One with the last know good state at 09:27 and one with new state and 09:34. When the broker is started for the first time, it is expected that you see only one configuration file. But don’t panic, the other will be created by the next updates done by the broker.

The broker configuration file is a binary file, however the readable contents of that file can be extracted via the strings command. It gives an idea of some contents of the broker file. e.g. it shows you that the broker config file is brought to you by the fine folks from NEDC, whoever they are. :-) With 12cR1 is looks like the following.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] file dr2DBIT121_SITE1.dat
dr2DBIT121_SITE1.dat: data

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] strings dr2DBIT121_SITE1.dat
}|{z
DBIT121
DBIT121_SITE1
DBIT121_SITE2
DBIT121_SITE2
Brought to you by the fine folks at NEDC.
TRUETRUEdbidg03ALLDBIT121FALSECONTINUE
DBIT121_SITE1DBIT121_SITE1FALSEFALSEFALSESYNCoptionalDISABLEON1,1948637,931101276,0*AUTO0,0,00,0,0AUTODBIT121_SITE2
dbidg01DBIT121DBIT121(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NA
ME=DBIT121_SITE1_DGMGRL)(INSTANCE_NAME=DBIT121)(SERVER=DEDICATED)))USE_DB_RECOVERY_FILE_DEST%t_%s_%r.dbf4
DBIT121_SITE2DBIT121_SITE2FALSEFALSEFALSESYNCoptionalDISABLEON1,1948637,931101276,0*DBIT121AUTO0,0,00,0,0AUTODBIT121_SITE1
dbidg02DBIT121DBIT121(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT121_SITE2_DGMGRL)(INSTANCE_NAME=DBIT121)(SERVER=DEDICATED)))USE_DB_RECOVERY_FILE_DEST%t_%s_%r.dbf

Things are changing with Oracle 12cR2. The broker config files are still binary files, however the readable content via the strings command is very good now. You can get a lot of very useful information out of it. And even with Oracle 12cR2, it looks like that it is still brought to you by the same fine folks from NEDC. :-) Looks like that the Oracle developers have a sort of humor. :-)

oracle@dbidg01:/u01/app/oracle/admin/DBIT122/pfile/ [DBIT122] file dr2DBIT122_SITE1.dat
dr2DBIT122_SITE1.dat: data
oracle@dbidg01:/u01/app/oracle/admin/DBIT122/pfile/ [DBIT122] strings dr2DBIT122_SITE1.dat
}|{z
DBIT122
DBIT122_SITE1
Brought to you by the fine folks at NEDC.
<?xml version="1.0" encoding="UTF-8"?>
<DRC Version="12.2.0.1.0" Name="DBIT122" CurrentPath="True">
  <PlannedState>OFFLINE</PlannedState>
  <Status>
    <Severity>Success</Severity>
    <Error>0</Error>
  </Status>
  <DefaultState>ONLINE</DefaultState>
  <IntendedState>ONLINE</IntendedState>
  <MIV PropertyID="1">0</MIV>
  <PRIMARY_SITE_ID PropertyID="26">513</PRIMARY_SITE_ID>
  <DRC_UNIQUE_ID PropertyID="4">152596437</DRC_UNIQUE_ID>
  <DRC_UNIQUE_ID_SEQUENCE PropertyID="5">18</DRC_UNIQUE_ID_SEQUENCE>
  <EXT_COND PropertyID="29">7</EXT_COND>
  <OVERALL_PROTECTION_MODE PropertyID="30">2</OVERALL_PROTECTION_MODE>
  <FastStartFailoverMode PropertyID="32">0</FastStartFailoverMode>
  <FSFO_MIV PropertyID="33">11</FSFO_MIV>
  <FastStartFailoverOBID1 PropertyID="119">470173189</FastStartFailoverOBID1>
  <FastStartFailoverOBID2 PropertyID="120">470173183</FastStartFailoverOBID2>
  <FastStartFailoverOBID3 PropertyID="121">470173184</FastStartFailoverOBID3>
  <ObserverVersion1 PropertyID="133">0</ObserverVersion1>
  <Configuration_Name PropertyID="31">DBIT122</Configuration_Name>
  <ObserverName1 PropertyID="129">dbidg03</ObserverName1>
  <ConfigurationWideServiceName PropertyID="132">DBIT122_CFG</ConfigurationWideServiceName>
  <RoleChangeHistory PropertyID="106">
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>931098450</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>931098812</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalFailover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932306689</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932307856</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932377455</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932381717</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>932382294</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</NewPrimary>
      <Status>0</Status>
      <Timestamp>932383387</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE1</OldPrimary>
      <NewPrimary>DBIT122_SITE2</NewPrimary>
      <Status>0</Status>
      <Timestamp>934017954</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>DBIT122_SITE2</OldPrimary>
      <NewPrimary>DBIT122_SITE1</New
Primary>
      <Status>0</Status>
      <Timestamp>934018548</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>
  <Member MemberID="1" Name="DBIT122_SITE1" CurrentPath="True" Enabled="True" MultiInstanced="True">
    <PlannedState>STANDBY</PlannedState>
    <StandbyRole>PhysicalStandby</StandbyRole>
    <Status>
      <Severity>Success</Severity>
      <Error>0</Error>
    </Status>
    <DefaultState>PRIMARY</DefaultState>
    <IntendedState>PRIMARY</IntendedState>
    <ResourceType>Database</ResourceType>
    <CurrentState>PRIMARY</CurrentState>
    <Role>
      <ConditionState>PRIMARY</ConditionState>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>READ-WRITE-XPTON</IntendedState>
    </Role>
    <Role>
      <ConditionState>STANDBY</ConditionState>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <DB_Unique_Name PropertyID="23">DBIT122_SITE1</DB_Unique_Name>
    <DGConnectIdentifier PropertyID="6">DBIT122_SITE1</DGConnectIdentifier>
    <DbDomain PropertyID="37"/>
    <ClusterDatabase PropertyID="42">FALSE</ClusterDatabase>
    <DbChangeCritical PropertyID="8">FALSE</DbChangeCritical>
    <DbIsCritical PropertyID="9">FALSE</DbIsCritical>
    <LogXptMode PropertyID="40">SYNC</LogXptMode>
    <IncarnationTable PropertyID="57">6,2568637,932306696,5*5,2514031,931098817,4#4,2513489,931098453,2#2,1396169,929894741,1#1,1,924281211,0#</IncarnationTable>
    <SRLStatus PropertyID="58">0</SRLStatus>
    <ActualApplyInstance PropertyID="7"/>
    <StandbyFileManagement PropertyID="72">AUTO</StandbyFileManagement>
    <ArchiveLagTarget PropertyID="73">0</ArchiveLagTarget>
    <LogArchiveMaxProcesses PropertyID="74">4</LogArchiveMaxProcesses>
    <LogArchiveMinSucceedDest PropertyID="75">1</LogArchiveMinSucceedDest>
    <DataGuardSyncLatency PropertyID="138">0</DataGuardSyncLatency>
    <DbFileNameConvert PropertyID="76"/>
    <LogFileNameConvert PropertyID="77"/>
    <FastStartFailoverTarget PropertyID="38">DBIT122_SITE2</FastStartFailoverTarget>
    <ReinstateContextArray PropertyID="39"/>
    <Instance InstanceID="1" Name="DBIT122" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True">
      <PlannedState>OFFLINE</PlannedState>
      <HostName PropertyID="2" Default="True">dbidg01</HostName>
      <SidName PropertyID="3">DBIT122</SidName>
      <InstanceName PropertyID="36">DBIT122</InstanceName>
      <StaticConnectIdentifier PropertyID="25">(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))</StaticConnectIdentifier>
      <StandbyArchiveLocation PropertyID="96" Default="True">USE_DB_RECOVERY_FILE_DEST</StandbyArchiveLocation>
      <LogArchiveTrace PropertyID="98">0</LogArchiveTrace>
      <LogArchiveFormat PropertyID="99">%t_%s_%r.dbf</LogArchiveFormat>
    </Instance>
  </Member>
  <Member MemberID="2" Name="DBIT122_SITE2" CurrentPath="True" Enabled="True" MultiInstanced="True">
    <PlannedState>STANDBY</PlannedState>
    <StandbyRole>PhysicalStandby</StandbyRole>
    <Status>
      <Severity>Success</Severity>
      <Error>0</Error>
    </Status>
    <DefaultState>STANDBY</DefaultState>
    <IntendedState>STANDBY</IntendedState>
    <ResourceType>Database</ResourceType>
    <CurrentState>STANDBY</CurrentState>
    <Role>
      <ConditionState>PRIMARY</ConditionState>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <Role>
      <ConditionState>STANDBY</ConditionState>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>PHYSICAL-APPLY-ON</IntendedState>
    </Role>
    <DB_Unique_Name PropertyID="23">DBIT122_SITE2</DB_Unique_Name>
    <DGConnectIdentifier PropertyID="6">DBIT122_SITE2</DGConnectIdentifier>
    <DbDomain PropertyID="37"/>
    <ClusterDatabase PropertyID="42">FALSE</ClusterDatabase>
    <DbChangeCritical PropertyID="8">FALSE</DbChangeCritical>
    <DbIsCritical PropertyID="9">FALSE</DbIsC
ritical>
    <LogXptMode PropertyID="40">SYNC</LogXptMode>
    <IncarnationTable PropertyID="57">8,2568637,932306696,4*4,2514031,931098817,3#3,2513489,931098453,2#2,1396169,929894741,1#1,1,924281211,0#</IncarnationTable>
    <SRLStatus PropertyID="58">0</SRLStatus>
    <ActualApplyInstance PropertyID="7">DBIT122</ActualApplyInstance>
    <StandbyFileManagement PropertyID="72">AUTO</StandbyFileManagement>
    <ArchiveLagTarget PropertyID="73">0</ArchiveLagTarget>
    <LogArchiveMaxProcesses PropertyID="74">4</LogArchiveMaxProcesses>
    <LogArchiveMinSucceedDest PropertyID="75">1</LogArchiveMinSucceedDest>
    <DataGuardSyncLatency PropertyID="138">0</DataGuardSyncLatency>
    <DbFileNameConvert PropertyID="76"/>
    <LogFileNameConvert PropertyID="77"/>
    <FastStartFailoverTarget PropertyID="38">DBIT122_SITE1</FastStartFailoverTarget>
    <ReinstateContextArray PropertyID="39"/>
    <Instance InstanceID="1" Name="DBIT122" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True">
      <PlannedState>OFFLINE</PlannedState>
      <HostName PropertyID="2" Default="True">dbidg02</HostName>
      <SidName PropertyID="3">DBIT122</SidName>
      <InstanceName PropertyID="36">DBIT122</InstanceName>
      <StaticConnectIdentifier PropertyID="25">(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))</StaticConnectIdentifier>
      <StandbyArchiveLocation PropertyID="96" Default="True">USE_DB_RECOVERY_FILE_DEST</StandbyArchiveLocation>
      <LogArchiveTrace PropertyID="98">0</LogArchiveTrace>
      <LogArchiveFormat PropertyID="99">%t_%s_%r.dbf</LogArchiveFormat>
    </Instance>
  </Member>
</DRC>

In 12cR2, Oracle put more information into the broker files and that’s why they are getting bigger. With a standard config they have now 16k instead of 12k like they had before with 12cR1. Ok .. it looks like I am drifting away. Let’s get back to the original question, how do I recover the broker configuration files, in case they get lost? Like always … it depends. You could lose the old copy on the standby, you could lose the new copy on the standby, or you could lose both copies on the standby, and you could lose them while the standby is up and running or while it is shutdown. The same applies to the primary. It might get even more complicated in case you have a far sync database in between, or more standby’s.

Not making it too complex, in the end, we might end up with 6 different recovery scenarios.

1.) We lose the old copy of the broker config file on either the primary or the standby
2.) We lose both copies of the broker config file on the standby while it is up and running
3.) We lose both copies of the broker config file on the standby while it is shut down
4.) We lose both copies of the broker config file on the primary while it is up and running
5.) We lose both copies of the broker config file on the primary while it is shut down
6.) We lose both copies of the broker contfig files on the primary and the standby (quite unlikely to happen)

My demos are built on a simple primary/standby setup with 12cR1. The primary is on host dbidg01 and the standby is currently on host dbidg02.

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:03 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr2DBIT121_SITE1.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:03 dr2DBIT121_SITE2.dat
Scenario 1.) We lose the old copy of the broker config file on either the primary or the standby

There is absolutely not need to panic in this case. I am deleting the old copies on the primary and standby at the same time.

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr1DBIT121_SITE1.dat
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr2DBIT121_SITE2.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

The broker does not care at all.

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

The second copy will be created automatically as soon as I am changing a property. e.g. I am setting the FastStartFailoverThreshold to the same value as it had before.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
Property "faststartfailoverthreshold" updated

The broker log has no information about the new created file and also the alert.log does not say anything, but automagically we end up with two broker config files after the update was done. So .. nothing to do here. Oracle handles this itself.

oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] tail -20f drcDBIT121.log
...
02/28/2017 11:10:25
EDIT CONFIGURATION SET PROPERTY faststartfailoverthreshold = 40
FSFO threshold value set to 40 seconds

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -l dr*
-rw-r----- 1 oracle oinstall 12288 Feb 28 11:10 dr1DBIT121_SITE1.dat
-rw-r----- 1 oracle oinstall 12288 Feb 28 11:04 dr2DBIT121_SITE1.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:04 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:10 dr2DBIT121_SITE2.dat

 

2.) We lose both copies of the broker config file on the standby while it is up and running

But what happens if I lose both copies on the standby while it is up and running.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

I am logging in into the standby database and check the configuration. The broker does not care at all if the files are there or not. It seems like nothing has happend.

oracle@dbidg02:/home/oracle/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:17:00.64] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:17:01.72] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:17:01.72] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

The broker is reading the information from memory, maybe because of performance reasons. The current state is also reflected in the x$drc.

SQL> desc x$drc
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 OBJECT_ID                                          NUMBER
 ATTRIBUTE                                          VARCHAR2(30)
 VALUE                                              VARCHAR2(512)
 PARENT_ID                                          VARCHAR2(15)
 STATUS                                             VARCHAR2(30)
 MESSAGE                                            VARCHAR2(256)
 ERRNUM                                             NUMBER
 VALUE_RAW                                          RAW(512)
 ERRTIME                                            NUMBER

So … how do we get the broker files back?  By simply editing any property. You can choose any property you want.

oracle@dbidg02:/home/oracle/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:27:58.23] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:27:59.33] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:27:59.33] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
<DO_CONFIGURE_DRC version="12.1"><EDIT_DRC><PROPERTY name="faststartfailoverthreshold" value="40" context="0"/></EDIT_DRC></DO_CONFIGURE_DRC>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Property "faststartfailoverthreshold" updated

As soon as you have edited your configuration, a new broker file appears on the standby.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:28 dr1DBIT121_SITE2.dat

If you run the same command again, the second one appears as well.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:28 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 11:30 dr2DBIT121_SITE2.dat

 

3.) We lose both copies of the broker config file on the standby while it is shut down

What happens if we lose both copies while the standby is shut down. Does the standby come up correctly? Is there something in the drc.log or alert.log?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*.dat
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

SQL> startup mount
ORACLE instance started.

Total System Global Area 1325400064 bytes
Fixed Size                  2924112 bytes
Variable Size             352321968 bytes
Database Buffers          956301312 bytes
Redo Buffers               13852672 bytes
Database mounted.

The standby comes up and no entries in the alert.log, however the drc.log shows that the broker files are missing.

02/28/2017 11:35:26
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
      dg_broker_config_file2 = "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat"
2017-02-28 11:35:26.313                      DMON: Attach state object
2017-02-28 11:35:26.314                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat", retrying
2017-02-28 11:35:27.334                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:35:27.334                        ORA-27037: unable to obtain file status
2017-02-28 11:35:27.335                        Linux-x86_64 Error: 2: No such file or directory
2017-02-28 11:35:27.335                        Additional information: 3
2017-02-28 11:35:27.335                      DMON: Error opening "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat", error = ORA-16572
2017-02-28 11:35:27.335                      DMON: Establishing "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat" as the more current file
2017-02-28 11:35:27.335                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat", retrying
2017-02-28 11:35:28.355                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE2.dat"
...
2017-02-28 11:35:42.893                      Configuration does not exist, Data Guard broker ready
2017-02-28 11:35:42.894 7fffffff           0 DMON: Entered rfm_release_chief_lock() for CTL_BOOTSTRAP


DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 11:38:50.58] Connecting to database using DBIT121_SITE2.
[W000 02/28 11:38:51.67] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:38:51.68] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL


SQL> select attribute, value from x$drc;
select attribute, value from x$drc
                             *
ERROR at line 1:
ORA-16532: Oracle Data Guard broker configuration does not exist

If we logon to the primary, we can see that the standby DBIT121_SITE2 was disabled.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE1
[W000 02/28 11:45:40.56] Connecting to database using DBIT121_SITE1.
[W000 02/28 11:45:41.68] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 11:45:41.68] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    DBIT121_SITE2 - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 7 seconds ago)

However, after we enable the standby, we can see in the drc.log that the standby receives the metadata from the primary and creates the broker config file.

DGMGRL> enable database 'DBIT121_SITE2';
<DO_CONTROL version="12.1"><DO_COMMAND type="Enable" object_id="33554432"/></DO_CONTROL>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Enabled.
DGMGRL>


drc.log

2017-02-28 11:46:03.352                      DRCX: Start receiving metadata file: "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:46:03.356                      DRCX: Receiving block #1 (containing Seq.MIV = 2.20), 2 blocks
2017-02-28 11:46:03.358                      DRCX: End receiving metadata file: opcode CTL_ENABLE (1.1.631286030)
2017-02-28 11:46:03.360                      DMON: Entered rfm_get_chief_lock() for CTL_ENABLE, reason 1
2017-02-28 11:46:03.360 02001000   631286030 DMON: start task execution: for metadata resynchronization
2017-02-28 11:46:03.360 02001000   631286030 DMON: status from posting standby instances for RESYNCH = ORA-00000
2017-02-28 11:46:03.360                      INSV: Received message for inter-instance publication
2017-02-28 11:46:03.361                            req ID 1.1.631286030, opcode CTL_ENABLE, phase RESYNCH, flags 8005
2017-02-28 11:46:03.361 02001000   631286030 DMON: Metadata available (1.1.631286030), loading from "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE2.dat"
2017-02-28 11:46:03.361 02001000   631286030       Opcode = CTL_ENABLE, Chief Instance I_ID = 1
2017-02-28 11:46:03.364                      DMON Registering service DBIT121_SITE2_DGB with listener(s)
2017-02-28 11:46:03.364                      DMON: Executing SQL [ALTER SYSTEM REGISTER]
2017-02-28 11:46:03.365                      SQL [ALTER SYSTEM REGISTER] Executed successfully
02/28/2017 11:46:06
Creating process RSM0


oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 11:46 dr1DBIT121_SITE2.dat

Again, if you do any property change again, the second broker file will be created.

4.) We lose both copies of the broker config file on the primary while it is up and running

There is not much difference with the scenario we have seen with the standby. The broker just reads from memory and as soon as any update to the config is done, the broker file is created again. There will be no entries in the drc.log or the alert.log. The broker file is just silently recreated.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager
[W000 02/28 12:28:45.07] Connecting to database using .
[W000 02/28 12:28:45.10] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:28:45.10] Oracle database version is '12.1.0.2.0'
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 9 seconds ago)


DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
<DO_CONFIGURE_DRC version="12.1"><EDIT_DRC><PROPERTY name="faststartfailoverthreshold" value="40" context="0"/></EDIT_DRC></DO_CONFIGURE_DRC>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
<RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>
Property "faststartfailoverthreshold" updated


oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 12:30 dr2DBIT121_SITE1.dat

 

5.) We lose both copies of the broker config file on the primary while it is shut down

Let’s do the whole thing again while the primary DB is shutdown.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -lh dr*
ls: cannot access dr*: No such file or directory

In the drc.log log file you can immediately see that there is something wrong.

>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat"
      dg_broker_config_file2 = "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat"
2017-02-28 12:34:45.866                      DMON: Attach state object
2017-02-28 12:34:45.866                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat", retrying
2017-02-28 12:34:46.876                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat"
2017-02-28 12:34:46.880                        ORA-27037: unable to obtain file status
2017-02-28 12:34:46.881                        Linux-x86_64 Error: 2: No such file or directory
2017-02-28 12:34:46.881                        Additional information: 3
2017-02-28 12:34:46.881                      DMON: Error opening "/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat", error = ORA-16572
2017-02-28 12:34:46.881                      DMON: Establishing "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat" as the more current file
2017-02-28 12:34:46.882                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat", retrying
2017-02-28 12:34:47.899                      DMON: cannot open configuration file "/u01/app/oracle/admin/DBIT121/pfile/dr2DBIT121_SITE1.dat"
...
2017-02-28 12:35:02.058 7fffffff           0 DMON: Entered rfm_release_chief_lock() for CTL_BOOTSTRAP
2017-02-28 12:35:02.424                      Fore: Continuing with primary evaluation, rfmsp.drc_status_rfmp = ORA-16532
2017-02-28 12:35:03.507                      Fore: Initiating post-open tasks
2017-02-28 12:35:09.192                      DMON: Initiating post-open tasks
2017-02-28 12:35:22.242 00000000  1934847279 DMON: GET_FSFO will be retired
2017-02-28 12:35:22.242 00000000  1934847279       severity = ORA-16501, status = ORA-16532
2017-02-28 12:35:22.242 00000000  1934847279 DMON: GET_FSFO operation completed
2017-02-28 12:35:52.250 00000000  1934847280 DMON: GET_FSFO will be retired
2017-02-28 12:35:52.250 00000000  1934847280       severity = ORA-16501, status = ORA-16532
2017-02-28 12:35:52.251 00000000  1934847280 DMON: GET_FSFO operation completed

If you take a look at the alert.log, everything is fine.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] dgmgrl -debug -xml
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager@DBIT121_SITE1
[W000 02/28 12:39:01.18] Connecting to database using DBIT121_SITE1.
[W000 02/28 12:39:02.28] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:39:02.28] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

But if connected to the standby, you will see a clear error message: ORA-16532: Oracle Data Guard broker configuration does not exist.

DGMGRL> connect sys/manager@DBIT121_SITE2
[W000 02/28 12:39:18.81] Connecting to database using DBIT121_SITE2.
[W000 02/28 12:39:19.90] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 02/28 12:39:19.90] Oracle database version is '12.1.0.2.0'
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    Error: ORA-16532: Oracle Data Guard broker configuration does not exist

    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 0 seconds ago)

Taking a close look at the error message, it does not give you a hint how to correct the issue.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] oerr ora 16532
16532, 00000, "Oracle Data Guard broker configuration does not exist"
// *Cause:  A broker operation was requested that required a broker
//          configuration to already be created.
// *Action: Create a Data Guard broker configuration prior to performing
//          other broker operations. If only one instance of a RAC
//          database is reporting this error, ensure that the
//          DG_BROKER_CONFIG_FILE[1|2] initialization parameters are
//          set to file locations that are shared by all instances of
//          the RAC database.

Let’s try the same trick, as we have done on the standby by simply enabling the database again.

DGMGRL> enable database 'DBIT121_SITE1';
<DO_CONTROL version="12.1"><DO_COMMAND type="Enable" object_id="16777216"/></DO_CONTROL>
<RESULT ><MESSAGE ><FAILURE  error_num="16532" error_prefix="ORA"><ERROR_TEXT >ORA-16532: Oracle Data Guard broker configuration does not exist
</ERROR_TEXT></FAILURE></MESSAGE><MESSAGE ><FAILURE  error_num="16625" error_prefix="ORA" error_tag1="DBIT121_SITE1"><ERROR_TEXT >ORA-16625: cannot reach database &quot;DBIT121_SITE1&quot;
</ERROR_TEXT></FAILURE></MESSAGE></RESULT>
<RESULT ><MESSAGE ><FAILURE  error_num="16532" error_prefix="ORA"><ERROR_TEXT >ORA-16532: Oracle Data Guard broker configuration does not exist
</ERROR_TEXT></FAILURE></MESSAGE><MESSAGE ><FAILURE  error_num="16625" error_prefix="ORA" error_tag1="DBIT121_SITE1"><ERROR_TEXT >ORA-16625: cannot reach database &quot;DBIT121_SITE1&quot;
</ERROR_TEXT></FAILURE></MESSAGE></RESULT>
Failed.

Hmmmm … does not look good. To recovery from that situation, we need to figure out the latest broker file version on the standby, which is dr2DBIT121_SITE2.dat is my case, and copy it over to the primary. Before doing that, we need to stop the broker on the primary.

SQL> alter system set dg_broker_start=false;

System altered.

Now we can copy the latest version over.

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] ls -rlth dr*
-rw-r----- 1 oracle oinstall 12K Feb 28 12:30 dr1DBIT121_SITE2.dat
-rw-r----- 1 oracle oinstall 12K Feb 28 12:33 dr2DBIT121_SITE2.dat

oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] scp -p dr2DBIT121_SITE2.dat oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/dr1DBIT121_SITE1.dat
dr2DBIT121_SITE2.dat                                                                          100%   12KB  12.0KB/s   00:00

And optionally create the second broker file on the primary.

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] cp -p dr1DBIT121_SITE1.dat dr2DBIT121_SITE1.dat
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

Last but not least, we need to enable the broker again.

SQL> alter system set dg_broker_start=true;

System altered.

DGMGRL> connect sys/manager@DBIT121_SITE1
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)
6.) We lose both copies of the broker config files on the primary and the standby (quite unlikely to happen)

This scenario is quite unlikely to happen, but if it happens it is good to have your Data Guard configuration as a script in place.

-- shutdown primary
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- shutdown standby
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Remove the broker files on the primary and the standby

-- primary
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

-- standby
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121] rm dr*
oracle@dbidg02:/u01/app/oracle/admin/DBIT121/pfile/ [DBIT121]

Now, after we have lost everything, meaning all broker config files, the only chance that we have is to create it from scratch. It is quite cool, if you have the configuration is place, because in a blink of an eye you recreate the whole configuration.

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cat broker.cfg
CONNECT SYS/manager@DBIT121_SITE1
REMOVE CONFIGURATION;
sql "alter system set log_archive_dest_2=''";
CREATE CONFIGURATION 'DBIT121' AS
PRIMARY DATABASE IS 'DBIT121_SITE1'
CONNECT IDENTIFIER IS 'DBIT121_SITE1';
ADD DATABASE 'DBIT121_SITE2' AS
CONNECT IDENTIFIER IS 'DBIT121_SITE2';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY LogXptMode='SYNC';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY LogXptMode='SYNC';
ENABLE CONFIGURATION;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold='40';
EDIT DATABASE 'DBIT121_SITE1' SET PROPERTY FastStartFailoverTarget='DBIT121_SITE2';
EDIT DATABASE 'DBIT121_SITE2' SET PROPERTY FastStartFailoverTarget='DBIT121_SITE1';
sql "alter system archive log current";
sql "alter system register";
SHOW CONFIGURATION;

oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] dgmgrl -debug -xml < broker.cfg > broker.log 2>&1
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121]

DGMGRL> show configuration;

Configuration - DBIT121

  Protection Mode: MaxAvailability
  Members:
  DBIT121_SITE1 - Primary database
    DBIT121_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)
Conclusion

Loosing broker configuration files is not the end of the world. As you have seen in the blog, it makes quite a huge difference if you loose the broker file while the DB is up and running or it is shut down. In case you haven’t lost all of them you can recover them in most of the situations. However, the alert.log is not a good candidate to check if the broker files are missing. So better scan the alert.log and the drc.log with your preferred monitoring tool to get the whole picture. Besides that, it is good practice to adjust your broker.cfg file after you have done some changes because it makes it very easy to rebuild the whole broker config from scratch.

 

Cet article Oracle 12c – How to Recover lost DataGuard Broker Configuration Files est apparu en premier sur Blog dbi services.

OBIEE 11.1.1.7: Is It Still Supported?

Rittman Mead Consulting - Tue, 2017-02-28 07:11
 Is It Still Supported?

Last week, during a quiet working morning, my colleague Robin Moffatt sent me a link that changed my working focus for few hours.

The link was to "OBIEE 11g: Required and Recommended Bundle Patches and Patch Sets (Doc ID 1488475.1)", a document containing the latest bundle patch (patch set) available for every major OBIEE release.

He spotted that for the first time, the new bundle patch released on 17 Jan 2017 was for OBIEE 11.1.1.9 only, with the latest patch for 11.1.1.7 being the one previously released on 18 Oct 2016.
Moreover there is a clear statement in the document saying that is necessary to be at least on the 11.1.1.7.151020 patch-set or upgrade to 11.1.1.9 (or 12c) in order to receive continued Error Correction Support.

Questions became then naturally:

  • Is OBIEE 11.1.1.7 still fully supported?
  • What is Error Correction Support?
  • How does Oracle Support Work?
  • How can I verify if my software is supported and when the support ends?

 Is It Still Supported?

With those questions in mind, I started my wonderful journey in understanding the Oracle support, if you want be my guests then sit down, take a coffee (espresso please) and enjoy the trip.

Understanding OBIEE Oracle Support

Disclaimer: All the information and date mentioned in the post reflect the situation as of the post publication date, Oracle may change them in future. Oracle support documents mentioned in this post should be directly taken as reference. The documents linked below are mostly regarding OBIEE, for other products please visit Oracle's support website.

Understanding Oracle's support requires patience and the right set of documents, with the main two being:

  • Lifetime Support Policy: provides the announcements, dates and exceptions for the end of Premier, Extended and Sustaining Support. The detail of the Lifetime Support Policy is by product e.g OBIEE 11.1.1.*.
  • Error Correction Support Policy: provides details regarding the end of bug correction support per single release (e.g. 11.1.1.7). For OBIEE 12c the Error Correction Support document is merged in the Fusion Middleware document.

The combined information contained in these two sources should be used to determine the status of a specific Oracle's software version.

Oracle's Support Phases

The two documents listed above provide a good starting point, but what do "Premier", "Extended", "Sustaining" and "Error Correction" Support mean?
Doc ID 1664916.1 and Lifetime Support Policy page provide a detailed explanation.

The "Lifetime Support Policy" defines the three main phases for each product (e.g. OBI 11g):

  • Premier Support: is in the first 5 years (however this timeframe could be changed) from General Availability of a product, during this period bundle patches are released providing error correction and new features with certification for most Oracle or third party support.
  • Extended Support: almost all the benefits of Premier Support can be extended by another three years after its end with an additional fee.
  • Sustaining Support: after the end of Premier Support, this option (requiring an additional fee) provides a limited support on existing product bugs but no new developments (e.g. a certified mechanism of connecting OBIEE 10g to Impala); can be extended for an unlimited amount of time.

The first two phases are also considered Error Correction Support since are the only two where new bug/security related patches are provided. After the end of Error Correction Phase no more patches will be released for a certain product.

Applying the Lifetime Support Policy to OBIEE 11.1.1.* based on Oracle's documentation the following is obtained.

 Is It Still Supported?

As mentioned before, this is a guideline for the whole OBIEE 11.1.1.* set of products, so how can I determine the end of support for a certain release?

Usually the answer to this question is defined by

  • The end of the Premier/Extended Support if the release you're checking is the latest available of a product
  • The end of the release's Grace period: if a newer release is available. The grace period, mentioned in Doc ID 944866.1 is the period of time (usually one year) following the release of a patch set in which Oracle provides fixes for both the new and previous patch set. The grace period is calculated based on the availability of the following release. E.g. since OBIEE 11.1.1.1.9 was released in May 2015, the grace period for 11.1.1.7 should be ended in May 2016. There are however exceptions where the grace period and related error correction support is extented, like for the 11.1.1.7 which now is extended until December 2018 as per OBIEE Error Correction Support document.

The following graph shows the error correction support phases for OBIEE 11.1.1.7.

 Is It Still Supported?

What Does This Mean For OBIEE 11.1.1.7?

As stated above OBIEE 11.1.1.7 is still in its grace period meaning that bundle patches and security fixes will still be released when needed until Dec-2018.

via GIPHY


Is OBIEE 11.1.1.7 Still The Right Choice?

If all you need is support for your existing and working environment having the latest bundle patch, then yes, you're fine and you'll be until December 2018.
But we all know how long an upgrade takes from planning to the actual execution and testing. Probably you don't want to wait until the end of next year having to rush for the upgrade because your system is getting out of support.

 Is It Still Supported?

We at Rittman Mead are in favour of a smart upgrade planning and with our fixed cost upgrade process can take care of it while you keep focusing on what matters in your company.

Upgrading

As suggested by Oracle's document there are two options: migrate to the latest 11g release (11.1.1.9) or 12c. Both are valid options - 11.1.1.9 has another few years of premier support, with end of error correction support targeted for Dec-2021 as stated in Oracle's Doc 1664916.1.

If you are opting for the migration, choosing the target release should be based on the features you aim to get and the version stability plan: some options like Visual Analyzer, Advanced Analytics, and Data Mashups are only available on 12c which is the version currently developed, while 11g is on its way out. These points are discussed in more detail below.

Upgrading to OBIEE 11.1.1.9

The migration to 11.1.1.9 might seem easier and it could be done in-place. However it still requires database schema changes, moreover an in-place upgrade has associated risk of having unavailable live environments until all issues are fixed; risk that can be mitigated with an out-of-place upgrade.
You can find a list of 11.1.1.9 new features for end users and system admins in our blog.

Finally, if you migrate to 11.1.1.9, the end of its error correction support in Dec-2021 forces the next milestone in your upgrade path.

Upgrading to OBIEE 12c

On the other hand the migration to OBIEE 12c (with the 12.2.1.2.0 being the latest available as of now) is completely an out-of-place process. There is a requirement on the source 11g version meaning that if you are currently on a pre-11.1.1.7 version, you'll first need to perform a migration to 11.1.1.7 or 11.1.1.9 and then to 12c.

The upgrade process is smooth, with a dedicated upgrade tool taking care of moving the catalog, RPD and part of the security and a Baseline Validation Tool helping in automating the testing.
OBIEE 12c includes several new features with new connectors to big data world that will increase over time and the new options like Visual Analyzer and Data Mashup providing great functionalities directly in the hands of end users.

Check out our 12c migration case study presented at UKOUG!

Rittman Mead Can Help!

Unsure about the support status of your current system and the risks related? With our experience we can help you plan a migration roadmap!
A migration is not a quick process, it needs to be understood and planned correctly, the following are just some examples of our pre-migration activities with clients.

  • Worried about migrating?

    We can advice about best installation/migration processes and the benefits of such upgrades. We can also help you implementing the processes during the migration phase.

  • Unsure about server capacity?

    We can help estimating server sizing based on the number of active users and the enabled features. Our Performance Analytics Service can help understanding and solving bottlenecks.

  • Unsure about impact on BAU activities?

    Our migration approach reduces the Code Freeze time to a minimum; the time to swap between versions in the production environment can also be minimised.

  • Uncertain about timings?

    We can help you estimating effort and plans for a successful upgrade based on our experience.

Once the migration in planned, we can implement it for you or assist and support your team! Let us help you!

Categories: BI & Warehousing

Introducing AlwaysOn availability groups on Linux

Yann Neuhaus - Tue, 2017-02-28 06:13

A couple of days ago, Microsoft has rolled out the SQL Server vNext CTP 1.3 which includes very nice features and the one that interested me in the supportability of AlwaysOn availability groups on Linux. That is definitely a good news because we may benefit from new ways of architecting high-availability. There are a lot of new cool enhancements, which we’ll try to study when writing new blog posts. In this first blog, let’s learn new available configuration options.

First of all, let’s say that configuring availability groups on Linux is not different than configuring them on Windows in workgroup mode (domainless configuration) from SQL Server perspective.

Basically, the same steps remain as it is (please refer to the Microsoft installation documentation):

  • Creating login and users on each replica
  • Creating certificate and grant authorization to the corresponding user on each replica
  • Creating endpoint for data mirroring and grant permission connected to the corresponding certificate

So let’s just set the scene before moving forward on the other installation steps. I used for my demo two virtual machines on Hyper-V which run on Linux CentOS 7 (LINUX02 and LINUX04) . I also installed two SQL Server instances (CTP 1.3) on each machine which will run on the top of the cluster infrastructure with Pacemaker and Corosync.

blog 119 - 00 - ag linux - demo infra

Obviously this time the NFS server is not part of this infrastructure and this time I used a symmetric storage on each virtual machine which includes two mount points and two ext4 partitions (respectively /SQL_DATA and /SQL_LOG to host my AdventureWorks2012 database files).

[mikedavem@linux02 ~]$ cat /etc/fstab

/dev/mapper/cl-root     /                       xfs     defaults        0 0
UUID=d6eb8d27-35c7-4f0f-b0c1-42e380ab2eca /boot                   xfs     defaults        0 0
/dev/mapper/cl-swap     swap                    swap    defaults        0 0
/dev/sdb1       /sql_data       ext4    defaults 0 0
/dev/sdc1       /sql_log        ext4    defaults 0 0

 

SELECT 
	name AS logical_name,
	physical_name
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks2012')

 

blog 119 - 01 - ag linux - adventurworks2012 files

First step to enable the AlwaysOn feature: we have to use the mssql-conf tool (which replaces the famous SQL Server configuration manager) with the new option set hadrenabled 1

Then if we use the Linux firewall on each machine, we have to open the corresponding TCP endpoint port on the Linux firewall on each Linux machine.

[root@linux02 data]firewall-cmd --zone=public --add-port=5022/tcp --permanent
success
[root@linux02 data]firewall-cmd –reload 
success
[root@linux02 data]firewall-cmd --permanent --zone=public --list-all
public
  target: default
  icmp-block-inversion: no
  interfaces:
  sources:
  services: dhcpv6-client high-availability mountd nfs rpc-bind ssh
  ports: 1433/tcp 5022/tcp
  protocols:
  masquerade: no
  forward-ports:
  sourceports:
  icmp-blocks:
  rich rules:

[root@linux04 ~]firewall-cmd --zone=public --add-port=5022/tcp --permanent
success
[root@linux04 ~]firewall-cmd --reload

 

No really new stuff so far … The most interesting part comes now. After installing SQL Server on Linux and achieving endpoint configurations, it’s time to create the availability group. But wait, at this stage we didn’t install any clustering part right? And in fact, we don’t have to do this. We are now able to create an availability group without any cluster dependencies by using a new T-SQL parameter CLUSTER_TYPE = NONE as follows. Very interesting because we may think about new scenarios where only read-scalability capabilities are considered on DR site. In this case we don’t have to setup additional cluster nodes which may lead to manageability overhead in this case.

We may also use the direct seeding mode feature available since SQL Server 2016 to simplify the process of adding a database in the corresponding availability group (AdventureWorks2012 database in my case).

:CONNECT LINUX02 -U sa -P Xxxxx

CREATE AVAILABILITY GROUP [agLinux]
WITH 
(
	DB_FAILOVER = ON, --> Trigger the failover of the entire AG if one DB fails 
	CLUSTER_TYPE = NONE --> SQL Server is not a member of a Windows Server Failover Cluster 
)
FOR REPLICA ON
N'LINUX02' 
WITH 
(
	ENDPOINT_URL = N'tcp://192.168.5.18:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'LINUX04' 
WITH 
( 
	ENDPOINT_URL = N'tcp://192.168.5.20:5022', 
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
	FAILOVER_MODE = AUTOMATIC,
	SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

ALTER AVAILABILITY GROUP [agLinux] 
GRANT CREATE ANY DATABASE;
GO

:CONNECT LINUX04 -U sa -P Xxxxx
ALTER AVAILABILITY GROUP [agLinux] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [agLinux] GRANT CREATE ANY DATABASE;
GO


:CONNECT LINUX02 -U sa -P Xxxxx
ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL;
GO
BACKUP DATABASE [AdventureWorks2012] TO DISK = 'NUL';
GO
-- Add the AdventureWorks2012 database to the agLinux AG
ALTER AVAILABILITY GROUP [agLinux] ADD DATABASE [AdventureWorks2012];
GO

 

After configuring the agLinux availability group let’s have a look at the different DMVs I usually use in this case:

  • sys.dm_hadr_availability_group_states
  • sys.dm_hadr_availability_replica_states
  • sys.dm_hadr_database_replica_states
-- groups info
SELECT 
	g.name as ag_name,
	rgs.primary_replica, 
	rgs.primary_recovery_health_desc as recovery_health, 
	rgs.synchronization_health_desc as sync_health
FROM sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g
				 ON rgs.group_id = g.group_id

-- replicas info
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	rs.is_local,
	rs.role_desc as role,
	rs.operational_state_desc as op_state,
	rs.connected_state_desc as connect_state,
	rs.synchronization_health_desc as sync_state,
	rs.last_connect_error_number,
	rs.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
	ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
	ON g.group_id = r.group_id

-- DB level          
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	DB_NAME(drs.database_id) as [database_name],
	drs.is_local,
	drs.is_primary_replica,
	synchronization_state_desc as sync_state,
	synchronization_health_desc as sync_health,
	database_state_desc as db_state
FROM sys.dm_hadr_database_replica_states AS drs
		 JOIN sys.availability_replicas AS r
		  ON r.replica_id = drs.replica_id
		 JOIN sys.availability_groups AS g
		  ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC;
GO

 

Here the corresponding output:

blog 119 - 1 - ag linux - ag status

Ok everything seems to be ok. We have configured an availability group which includes two replicas and synchronous replication so far. But maybe you have already noticed we didn’t create any listener and the reason is pretty obvious: if we refer to previous versions of SQL Server, creating a listener requires to create a corresponding CAP on the WSFC side and in our context, no cluster exists at this stage. So go ahead and let’s do it. You may refer to the Microsoft documentation to add the pacemaker cluster to the existing infrastructure.

I didn’t expect a big change compared to my last installation for SQL Server FCI on Linux for the basics but for the integration of the AG resource it will probably be another story. Indeed, the declaration of the resource (ocf:mssql:ag) is completely different compared to the SQL Server FCI (ocf::sql:fci). In this case, we have to create a multi-state resource which may allow us to start multiples instances of the AG resource and have them on each concerned replica. Multi-state resources are specialized clone resources which may be in one of two operating modes – master and slave. We run exactly on the same concept with availability groups because we consider to use one primary replica (master) and at least one secondary (slave). According to the Microsoft documentation we have to setup meta-properties which limit the number of resource copies (on replicas) as well as the number of master resources (only one primary replica at time in our case).

[mikedavem@linux02 ~]$ sudo pcs resource create sqllinuxaag ocf:mssql:ag ag_name=agLinux --master meta master-max=1 master-node-max=1 clone-max=2 clone-node-max=1

 

Creating a multi-state resource generates two separate resources regarding their role. In my case, I will get respectively sqllinuxaag-master (for the resource enrolled as Master resource) and sqllinuxaag-slave (for resource(s) enrolled as Slave resource(s)). We have to define monitoring for both as follows:

[mikedavem@linux02 ~]$ sudo pcs resource op sqllinuxaag monitor interval=11s timeout=60s role=Master
[mikedavem@linux02 ~]$ sudo pcs resource op sqllinuxaag monitor interval=12s timeout=60s role=Slave

 

Finally, we have to create and associate a virtual IP address as follows. The virtual IP must run at the same location than the master resource in our case (INFINITY is mandatory here)

[mikedavem@linux02 ~]$ sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.5.30
[mikedavem@linux02 ~]$ sudo pcs constraint colocation add virtualip sqllinuxaag-master INFINITY with-rsc-role=Master

 

Here a recap of the existing constraints which concern my availability group resource:

[mikedavem@linux02 ~]$ sudo pcs constraint
Location Constraints:
Ordering Constraints:
  promote sqllinuxaag-master then start virtualip (kind:Mandatory)
Colocation Constraints:
  virtualip with sqllinuxaag-master (score:INFINITY) (with-rsc-role:Master)
Ticket Constraints:

 

We may notice the global state of the resources and their roles (Master/Slave)

[mikedavem@linux02 ~]$ sudo pcs status
Cluster name: clustlinuxag
Stack: corosync
Current DC: linux04.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum
Last updated: Mon Feb 27 06:16:03 2017          Last change: Mon Feb 27 06:14:11 2017 by root via cibadmin on linux02.dbi-services.test
…

Full list of resources:

 Master/Slave Set: sqllinuxaag-master [sqllinuxaag]
     Masters: [ linux02.dbi-services.test ]
     Slaves: [ linux04.dbi-services.test ]
 virtualip      (ocf::heartbeat:IPaddr2):       Started linux02.dbi-services.test

 

So, now let’s perform some failover tests. I used a basic PowerShell script to connect to my availability group by using the sqllinuxaag resource and return a response (OK and the server name of the concerned replica or KO). But let’s say the resource is not considered as a listener from the availability group. No listener exists at this stage.

  • First test:

The first test consisted in switching manually over the sqllinuxaag (master) to the next available node (LINUX02). At this stage, we can’t use neither the wizard nor T-SQL statement to trigger a failover event. This is a limitation (explained by Microsoft) and I expect to see it to disappear in the future. Keeping the control of such action from SQL Server side will make more sense for DBAs.

[mikedavem@linux04 ~]$ sudo pcs resource move sqllinuxaag-master linux02.dbi-services.test --master

 

During the failover event, no way to reach out the resource but the situation went back into normal as expected.

blog 119 - 2 - ag linux - ag failover manual test

  • Second test

The second test consisted in simulating “soft” failure by changing the state of the new active node (LINUX02) to standby in order to trigger a switch over the next available cluster node (LINUX04). But before going on this way, let’s configure stickiness to avoid unexpected failback of the sqllinuxaag-master resource when the situation will go back to normal.

[mikedavem@linux04 ~]$ sudo pcs resource defaults resource-stickiness=INFINITY
[mikedavem@linux04 ~]$ sudo pcs cluster standby linux02.dbi-services.test

[mikedavem@linux04 ~]$ sudo pcs status
Cluster name: clustlinuxag
Stack: corosync
Current DC: linux02.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum
Last updated: Sun Feb 26 18:55:01 2017          Last change: Sun Feb 26 18:54:42 2017 by root via crm_attribute on linux04.dbi-services.test

2 nodes and 3 resources configured

Node linux02.dbi-services.test: standby
Online: [ linux04.dbi-services.test ]

Full list of resources:

 Master/Slave Set: sqllinuxaag-master [sqllinuxaag]
     Masters: [ linux04.dbi-services.test ]
     Stopped: [ linux02.dbi-services.test ]
 virtualip      (ocf::heartbeat:IPaddr2):       Started linux04.dbi-services.test

 

Same result than previously. During the failover event, the resource was unreachable but after the situation went back to normal, the application was able to connect again.

blog 119 - 3 - ag linux - ag failover with standby test

We may also confirm the state of the availability group by using usual DMVs. The replica role has switched between replicas as expected and the synchronization state remained in healthy state.

blog 119 - 4- ag linux - ag failover with standby test

  • Third test

My third test consisted in simulating a network outage between my two cluster nodes and the infrastructure responded well and performed the necessary tasks to recover the situation.

It was a quick introduction to the new capabilities offered by the SQL Server vNext in terms of HA and availability groups. Other scenarios and tests as well will come soon. In any event, availability groups feature is very popular as well as Linux in my area and getting the way to mix the both will probably be a good argument for customer adoption.   We will see in a near future!

See you

 

Cet article Introducing AlwaysOn availability groups on Linux est apparu en premier sur Blog dbi services.

Cost is Time (again)

Jonathan Lewis - Tue, 2017-02-28 05:19

The hoary old question about lower cost queries running faster or slower that higher cost queries has appeared once again on the OTN database forum. It’s one I’ve addressed numerous times in the past – including on this blog – but the Internet being what it is the signal keeps getting swamped by the noise. This time around a couple of “new” thoughts crossed my mind when reading the question.

There is a Time column on the standard forms of the execution plan output, and the description of this column is available in the manuals and has been for years (here’s a definition from v$sql_plan from 10gR2, for example):

Elapsed time (in seconds) of the operation as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null.

So the first question is this: why are people looking at the cost when they’re asking about the time ? The second question arises from the bit in brackets (parentheses): the time is given in seconds – so how accurate do you think the optimizer’s estimates of ANYTHING are when the best estimate the optimizer will give you for run-time has a granularity of a second ?

Of course there’s a further observation I could make (which only echoes the first question):  I don’t think I’ve ever seen anyone come up with the question: “Will a query with a lower value for Time run faster or slower than a query with a higher value for Time?”

Bottom Line:

Cost is supposed to be a measure of resource usage (per execution of each operation) and should therefore be a measure of time – but the model fails in many ways so when a plan clearly doesn’t meet reasonable expectations for performance you can (often) use the Cost column as an indicator of where the model has failed and this may give you some clues of how to address the problem.

It is unfortunate that before you can recognise when a particular Cost figure is bad you usually need to know something about the data content, the data distribution  pattern, the run-time caching effects, and the way the optimizer does its arithmetic.

 


In-Memory area static pools

Tom Kyte - Tue, 2017-02-28 04:26
Hello team, I have noticed that In-Memory area has static pools with fixed amount of memory allocated to them. Here is an example: <code> SQL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STA ...
Categories: DBA Blogs

calling stored procedure,stored function inside the trigger

Tom Kyte - Tue, 2017-02-28 04:26
hi Tom, 1)How to call stored function inside the trigger? 2)Is it possible to call stored function,stored procedure inside the trigger at a time, please can show with an example ?
Categories: DBA Blogs

Recursive function that uses FETCH cursor BULK COLLECT LIMIT

Tom Kyte - Tue, 2017-02-28 04:26
I am extracting data from complex XML documents into the database. The way that I have approached this is to write functions which handle lists of elements. So, the initial procedure opens the entire XMl document and I extract data from it using a ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator