Search

OakieTags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Oakies Blog Aggregator

Oracle Parallel Execution Deep Dive Session

Here is a recording of a session I did a while ago, covering how to understand the essentials of Oracle Parallel Execution and how to read the corresponding execution plans.

Logwriter I/O

If you are on any version of the database past 10.2.0.4, then savvy DBA’s may have noticed the following message popping up occasionally in their trace files


Warning: log write time 540ms, size 444KB

In itself, that is quite a nice little addition – an informational message letting you know that perhaps your log writer performance is worth closer investigation.  MOS Note 601316.1 talks a little more about this message.

So let’s say you have seen this warning, and you are interested in picking up more information.  Well… you could start scanning trace files from time to time, and parsing out the content etc, or do some analysis perhaps using Active Session History, but given that these warnings are (by default) triggered at above 500ms, there’s a chance you might miss them via ASH.

In 12c, this has become a lot simpler – because it looks like the same data is now exposed via a view, v$lgwrio_outlier


sql> select function_name,io_size,file_name,io_latency
  2  from   V$LGWRIO_OUTLIER;

FUNCTION_NAME IO_SIZE FILE_NAME                        IO_LATENCY
------------- ------- ------------------------------- -----------
LGWR               16 /u03/oradata/MYDB/control01.ctl         745
LGWR               16 /u03/oradata/MYDB/control01.ctl         506
LGWR               12 /u02/oradata/MYDB/redo09.dbf            705
LGWR                1 /u02/oradata/MYDB/redo08.dbf            502
LGWR                5 /u02/oradata/MYDB/redo07.dbf            538
LGWR              211 /u02/oradata/MYDB/redo10.dbf           1783
LGWR             1024 /u02/oradata/MYDB/redo10.dbf            597
LGWR                0 /u02/oradata/MYDB/redo10.dbf            597
LGWR               10 /u02/oradata/MYDB/redo10.dbf            597
LGWR                2 /u02/oradata/MYDB/redo10.dbf            775
LGWR                0 /u02/oradata/MYDB/redo07.dbf            683
LGWR                0 /u02/oradata/MYDB/redo09.dbf            704
LGWR                1 /u02/oradata/MYDB/redo10.dbf            504
LGWR                0 /u02/oradata/MYDB/redo10.dbf            690
LGWR               16 /u03/oradata/MYDB/control01.ctl         504
LGWR               58 /u02/oradata/MYDB/redo08.dbf            705 
LGWR                1 /u02/oradata/MYDB/redo08.dbf            704
...

Obviously the trace files are also tagged with the timestamp of occurrence, whereas the view does not expose this infromation.

But let’s just say two things here – (a) where there is a will there is a way, and (b) like most V$ views, it is based on an X$ object, which might contain more information Smile

For other types of I/O in the database, you can also take a look at V$IO_OUTLIER

Oracle’s Safra Catz and Trump’s Transition Team

I receive about 20-30 messages a week from women in the industry.  I take my role in the Oracle community as a role model for women in technology quite seriously and I’ve somehow ended up speaking up a number of times, upon request from different groups.

rosie

Although its not the first time the topics come up, I was asked last week for some recommendations on Oracle’s CEO, Safra Catz and her opportunity to be on President Elect Trump’s transition team.

I wanted to ask your opinion about Safra not taking a leave of absence to help with Trump’s transition team? I think she should take a leave and as one of the top women in IT I think it shows poor judgment. Could the WITs write her a letter? Thoughts?

After some deep thought, I decided the topic required a good, solid answer and a broader audience.  As with anything involving the topic of WIT, the name of the source who asked the question doesn’t matter and anyone who asks you to give names isn’t really interested in education, but persecution.

It took me some time to think through the complexities of the situation.  Everyone will have some natural biases when a topic bridges so many uncomfortable areas of discussion:

  • Women’s Roles
  • Politics
  • Previous Employer

After putting my own bias aside and thinking through the why and what, here’s my thoughts-

No, I don’t think Safra should take a leave of absence. We have significantly few women in c-level positions.  As of April 2016, only 4% of CEO’s for Fortune 500 companies were women, (which Safra is one.)  I have a difficult time believing we’d be asking most men to give up the opportunity to be on a presidential transition team or take a leave of absence.  Some of the most challenging and difficult times in our career are also the most rewarding and this may be one of those times in Safra’s life.  Anyone who’s friends with me, especially on Facebook, would know, I’m not a big fan of Donald Trump, but in no way should we ask Safra to not try to be part of the solution.

No, I don’t think Safra should refrain from being on the transition team.  As much as we discuss the challenges of getting more women in technology, its even a larger challenge in politics.  Women have less than 25% of the seats in Congress and even less at local government levels.  We are over 50% of the workforce and 50% of the US population.  How can we ever have our voices heard if we aren’t participating in our own government?  Having more representation is important, not less and not because my politics don’t mesh with hers.

So what should the discussion really be about if we don’t want Safra to take a leave of absence or remove herself from the transition team?

  1. We want to know that there are clear policies in place to deter from conflict of interest.  We need to know that if improprieties do occur, that accountability will result.
  2. We need to not limit Safra in opportunities or over-scrutinize her the way we do so many women who don’t fit inside the nice, neat little expectations society still has of them.
  3. We shouldn’t hold Safra accountable for what Donald Trump represents, his actions or if we don’t agree with his politics.

We also need to discuss what is really bothering many when a woman or person of color enters into the lions den, aka a situation that is clearly not very welcoming to us due to gender, race or orientation.  It can bring out feelings of betrayal, concerns that the individual is “working for the enemy.”  We want to know that Safra will stand up for our rights as the under-represented.  We want to know that she would tell Donald that she doesn’t condone his behavior or actions towards women, race and culture.

One of the biggest challenges I had to overcome when I started my career, was recognizing that every individual has their own path in this world.  Their path may be very different than mine, but through change comes growth and to expect someone to do what may not be in their capabilities can be just as limiting as not letting them do what they do best.  This wouldn’t be allowing Safra to do what she does best.

I’ve never viewed Safra as a role-model when it comes to the protection and advancement of women’s roles in technology or our world.  She’s never historically represented this, any more than those expecting it from Marissa Mayer.  It’s just not part of their unique paths, no matter how much the media likes to quote either of them, (especially Marissa, which consistently makes me cringe.)  It doesn’t mean they aren’t capable of accomplishing great feats-  just not feats in the battle for equality.  It also doesn’t mean they aren’t a source of representation.  The more women that are in the space, the better.  That’s how we overcome some of the bias we face.

Regarding those that do support women in more ways that just representing the overall count of women in technology and politics, I’d rather put my time into Sheryl Sandberg, Grace Hopper, Meg Whitman and others who have the passion to head up equality issues.  I both welcome and am thankful for the discussion surrounding writing the letter and applaud the woman who asked me about the topic-  it’s a difficult one.

For those of you who are still learning about why equality is so important, here’s a few historical references of great women who’ve advanced our rights.  We wouldn’t be where we are today without them.

#008000;">Thank you to everyone for the great beginning to 2017 and thank you for continuing to trust me to lead so many of these initiatives.  I hope I can continue to educate and help the women in our technical community prosper.

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Oracle's Safra Catz and Trump's Transition Team], All Right Reserved. 2017.

The post Oracle’s Safra Catz and Trump’s Transition Team appeared first on DBA Kevlar.

Fast-Start Failover for Maximum Protection in #Oracle 12c

Fast-Start Failover is supported with Maximum Protection in 12cR2. Also Multiple Observers can now monitor the same Data Guard Configuration simultaneously. I will show both in this article. Starting with a (Multitenant) Primary in Maximum Protection mode with two Standby Databases. It is still not recommended to have the highest protection mode configured with only one standby. So this is my starting point:

DGMGRL> show configuration;

Configuration - myconf

Protection Mode: MaxProtection
Members:
cdb1 - Primary database
cdb1sb - Physical standby database
cdb1sb2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 57 seconds ago)

All three databases have flashback turned on. I want to have a setup like this in the end:

FSFO with Max Protection and 2 Observershttps://uhesse.files.wordpress.com/2017/01/fsfo_2observer.png?w=1240&h=698 1240w, https://uhesse.files.wordpress.com/2017/01/fsfo_2observer.png?w=150&h=84 150w, https://uhesse.files.wordpress.com/2017/01/fsfo_2observer.png?w=300&h=169 300w, https://uhesse.files.wordpress.com/2017/01/fsfo_2observer.png?w=768&h=432 768w, https://uhesse.files.wordpress.com/2017/01/fsfo_2observer.png?w=1024&h=576 1024w" sizes="(max-width: 620px) 100vw, 620px" />

FSFO with Max Protection and 2 Observers

This is how it’s been configured:

DGMGRL> edit database cdb1 set property faststartfailovertarget='cdb1sb,cdb1sb2';
Property "faststartfailovertarget" updated
DGMGRL> edit database cdb1sb set property faststartfailovertarget='cdb1,cdb1sb2';
Property "faststartfailovertarget" updated
DGMGRL> edit database cdb1sb2 set property faststartfailovertarget='cdb1,cdb1sb';
Property "faststartfailovertarget" updated
DGMGRL> enable fast_start failover;
Enabled.

On host uhesse4:

[oracle@uhesse4 ~]$ dgmgrl sys/oracle@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jan 13 17:20:52 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> start observer number_one;
[W000 01/13 17:21:04.85] FSFO target standby is cdb1sb
[W000 01/13 17:21:07.05] Observer trace level is set to USER
[W000 01/13 17:21:07.05] Try to connect to the primary.
[W000 01/13 17:21:07.05] Try to connect to the primary cdb1.
[W000 01/13 17:21:07.05] The standby cdb1sb is ready to be a FSFO target
[W000 01/13 17:21:09.06] Connection to the primary restored!
[W000 01/13 17:21:13.07] Disconnecting from database cdb1.

On host uhesse3:

[oracle@uhesse3 ~]$ dgmgrl sys/oracle@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jan 13 17:22:16 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> start observer number_two;
[W000 01/13 17:22:32.68] FSFO target standby is cdb1sb
[W000 01/13 17:22:34.85] Observer trace level is set to USER
[W000 01/13 17:22:34.85] Try to connect to the primary.
[W000 01/13 17:22:34.85] Try to connect to the primary cdb1.
[W000 01/13 17:22:34.85] The standby cdb1sb is ready to be a FSFO target
[W000 01/13 17:22:36.86] Connection to the primary restored!
[W000 01/13 17:22:40.86] Disconnecting from database cdb1.

This is now the state of the configuration:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    cdb1sb  - (*) Physical standby database 
    cdb1sb2 - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 33 seconds ago)

DGMGRL> show fast_start failover;

Fast-Start Failover: ENABLED

  Threshold:          15 seconds
  Target:             cdb1sb
  Candidate Targets:  cdb1sb,cdb1sb2
  Observers:      (*) number_two
                      number_one
  Lag Limit:          30 seconds (not in use)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

That protects against the failure of any two components in the configuration with automatic failover and zero data loss! For example the first standby may fail and then the primary. We failover to the second standby that becomes the new fast-start failover target:

[oracle@uhesse2 ~]$ ps -ef | grep smon
oracle   15087     1  0 17:40 ?        00:00:00 ora_smon_cdb1sb
oracle   15338  9765  0 17:49 pts/2    00:00:00 grep --color=auto smon
[oracle@uhesse2 ~]$ kill -9 15087

Above crashed the first standby. This is what the Observers report:

[W000 01/13 17:49:34.24] Failed to ping the standby.
[W000 01/13 17:49:37.25] Failed to ping the standby.
[W000 01/13 17:49:40.25] Failed to ping the standby.
[W000 01/13 17:49:43.25] Failed to ping the standby.
[W000 01/13 17:49:46.26] Failed to ping the standby.
[W000 01/13 17:49:46.26] Standby database has changed to cdb1sb2.
[W000 01/13 17:49:47.26] Try to connect to the primary.
[W000 01/13 17:49:47.26] Try to connect to the primary cdb1.
[W000 01/13 17:49:48.34] The standby cdb1sb2 is ready to be a FSFO target
[W000 01/13 17:49:53.35] Connection to the primary restored!
[W000 01/13 17:49:57.35] Disconnecting from database cdb1.

This is the state of the configuration now:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    Error: ORA-16778: redo transport error for one or more members

    cdb1sb2 - (*) Physical standby database 
    cdb1sb  - Physical standby database 
      Error: ORA-1034: ORACLE not available

Fast-Start Failover: ENABLED

Configuration Status:
ERROR   (status updated 14 seconds ago)

Notice that the Fast-Start Failover indicator (*) now points to cdb1sb2. Now the primary fails:

[oracle@uhesse1 ~]$ ps -ef | grep smon
oracle   21334     1  0 17:41 ?        00:00:00 ora_smon_cdb1
oracle   22077  5043  0 17:52 pts/0    00:00:00 grep --color=auto smon
[oracle@uhesse1 ~]$ kill -9 21334

This is what the Observers report:

[W000 01/13 17:52:54.04] Primary database cannot be reached.
[W000 01/13 17:52:54.04] Fast-Start Failover threshold has not exceeded. Retry for the next 15 seconds
[W000 01/13 17:52:55.05] Try to connect to the primary.
[W000 01/13 17:52:57.13] Primary database cannot be reached.
[W000 01/13 17:52:58.13] Try to connect to the primary.
[W000 01/13 17:53:06.38] Primary database cannot be reached.
[W000 01/13 17:53:06.38] Fast-Start Failover threshold has not exceeded. Retry for the next 3 seconds
[W000 01/13 17:53:07.39] Try to connect to the primary.
[W000 01/13 17:53:09.46] Primary database cannot be reached.
[W000 01/13 17:53:09.46] Fast-Start Failover threshold has expired.
[W000 01/13 17:53:09.46] Try to connect to the standby.
[W000 01/13 17:53:09.46] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 01/13 17:53:09.46] Check if the standby is ready for failover.
[S019 01/13 17:53:09.47] Fast-Start Failover started...

17:53:09.47  Friday, January 13, 2017
Initiating Fast-Start Failover to database "cdb1sb2"...
[S019 01/13 17:53:09.47] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1sb2"
17:53:23.68  Friday, January 13, 2017

After having restarted the two crashed databases, they become automatically reinstated and the configuration then looks like this:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1sb2 - Primary database
    cdb1    - (*) Physical standby database 
    cdb1sb  - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

DGMGRL> show fast_start failover;

Fast-Start Failover: ENABLED

  Threshold:          15 seconds
  Target:             cdb1
  Candidate Targets:  cdb1,cdb1sb
  Observers:      (*) number_two
                      number_one
  Lag Limit:          30 seconds (not in use)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

Switching back to make cdb1 primary – this is of course optional:

DGMGRL> switchover to cdb1;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb1"
Connecting ...
Connected to "cdb1"
Connected as SYSDBA.
New primary database "cdb1" is opening...
Operation requires start up of instance "cdb1sb2" on database "cdb1sb2"
Starting instance "cdb1sb2"...
ORACLE instance started.
Database mounted.
Connected to "cdb1sb2"
Switchover succeeded, new primary is "cdb1"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    cdb1sb  - (*) Physical standby database 
    cdb1sb2 - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

I think this enhancement is really a big deal!

Tagged: 12c New Features, Data Guard

Python for PL/SQL Developers Series

How hard it is to learn a new language? It depends on the learner, of course; but everyone will agree that it is easier to get your feet wet if you learn the corresponding structures of a language you are already proficient in. That's the principle behind my new article series on Oracle Technology Network: Python for PL/SQL Developers. If you are at least an intermediate level in PL/SQL, you will jumpstart your learning Python by learning the corresponding syntax elements, which is almost always a guaranteed way to learn the meat of the language fairly quickly. I learned most of the languages that way, and this is an experiment to do the same here as well.

Designed as a five part series, it starts off with the basics such as variables and assignments. then it gradually progresses to conditions, loops, functions, modules, etc. Finally, it explains how to use publicly available packages for data manipulation, including the ones to connect to the Oracle database. The idea is to have you up and running in Python by committing about an hour every day for a week.

Too busy to read? I have developed a video for each part. Watch it and go at it. Each part also has a quiz to test your knowledge gained from that part.

Here it is: http://bit.ly/python4plsql

As always, I will be successful only if you (and only you) like it. I will appreciate your honest feedback on this series. How you liked it (or, not); what are the strengths; what areas could have been improved and so on. Post as comments here, tweet @ArupNanda, post at www.FaceBook.com/ArupKNanda or just email me at arup@proligence.com.

An introduction to PCP / Performance Co Pilot on Oracle Linux

I was investigating gathering performance data on (oracle) linux servers recently and came across Performance Co-Pilot (PCP). I have come across this product regularly in the past, but it seemed somewhat abstract to me, and I never ran into any actual usage. And we got sar for linux performance data and for the Oracle database we got oswatcher (and it’s exadata cousin exawatcher) and TFA right? How wrong I was.

First let me explain a few things.

There is no magic involved in performance measurement and tuning. What this aims to say is that most performance gathering frameworks use the same performance data sources as regular tools use. The are tools that can go beyond the regular data sources, like systemtap, which actually can be used as a source for PCP (but is beyond the scope of this article) and perf to name a few. This means that whatever tool you use, like: mpstat, iostat, top or sar, they all display the same cpu information. It might disguised in a different way, but really it’s all the same information.

This means that’s it’s not about the information itself, but about how you can interact with historical data. The majority of the performance tools simply do not have a facility to store measurements. That’s perfectly fine, and that wasn’t ever a design criterion when they were written. This is true for tools like mpstat, iostat, top to name a few. However in a lot of cases having volatile data is a real pain. Of course you can save the output to a file, but this very quickly becomes a challenge once you have a few files of sar, iostat, etc.

This is where sar comes in. Sar is installed on linux using the sysstat rpm package, and actually does store historical data! Sar stores it’s data in /var/log/sa, and stores sar archives historically based on the HISTORY setting in /etc/sysconfig/sysstat. In the past I seen this been set to 8, a check on a current oracle linux 6 machine shows HISTORY been set to 28. Also in the past the maximal retention time was 31, because the day of the month number would overwrite the same day of the previous month. This issue too seems to be solved, the comments in the /etc/sysconfig/sysstat file says that if the value is set higher than 28, files are kept in month-based directories.

The historical data sar stores are measurements with a ten minute interval. The reason for this is how sa1 (a helper utility for storing sar data) is invoked. This is done via a cron setting in /etc/cron.d/sysstat:

# Run system activity accounting tool every 10 minutes
*/10 * * * * root /usr/lib64/sa/sa1 1 1
# 0 * * * * root /usr/lib64/sa/sa1 600 6 &
# Generate a daily summary of process accounting at 23:53
53 23 * * * root /usr/lib64/sa/sa2 -A

If you speak cron, you see that the second row instructs cron to run sa1 taking one sample every ten minutes (which is put in /var/log/saNN, and on line 5 to run sa2, which creates a (text)report called /var/log/sarNN, and does maintenance based on the HISTORY setting.

This is brilliant when coming from having no data at all. In fact, I have used this in the past and am using this with success to look into measurements looking for anomalies. However, it’s not very granular, it’s per ten minutes. Also, getting data is by extracting data from the /var/log/sa/saNN file (sar -f /var/log/sa/saNN -s hh:mm:ss -e hh:mm:ss – (switches for data sources, like cpu% (u), swapping in/out (W), etc)). More frequently I just ask for the /var/log/sa/sarNN (text) report, which contains all sar measurements.

So far we are all happy. Performance data can be queried ad-hoc, and there’s a history with per 10 minute samples. But how about when you do test things on your system? In most of the cases I am involved with benchmarking and proof of concepts, we run things and actually want data which is more granular than 10 minutes. In fact, a single test might not even take 10 minutes and not be visible in the sar history at all! There is a solution for that too: setup sar, iostat, mpstat, etc. each with output into a file in the background, and then run whatever you want to run, and kill the performance collectors afterwards. Sounds great, but if you’ve ever tried that, it becomes a bit complex quite soon.

This is where performance co-pilot (PCP) comes in. PCP is an open-source tool that is available in the ‘latest’ repository of oracle linux 6 and 7, which means it can simply be installed using yum. Once installed, you can extract the very same data earlier mentioned tools get from a linux system using the pcp tools:

CPU information:

[opc@deploy1 ~]$ pmrep :sar-u
               %user       %nice        %sys     %iowait      %steal       %idle
13:00:41         N/A         N/A         N/A         N/A         N/A         N/A
13:00:42        0.47        0.00        0.11        0.00        0.00       99.41
13:00:43        0.96        0.00        0.12        0.00        0.00       98.89
13:00:44        0.21        0.00        0.06        0.00        0.00       99.72
13:00:45        1.15        0.00        0.17        0.00        0.00       98.65
13:00:46        0.03        0.00        0.01        0.00        0.00       99.96
13:00:47        1.22        0.00        0.14        0.00        0.00       98.58
13:00:48        0.58        0.00        0.18        0.00        0.00       99.23
13:00:49        0.98        0.00        0.12        0.00        0.00       98.85
13:00:50        0.18        0.00        0.04        0.00        0.00       99.77
13:00:51        1.15        0.00        0.18        0.00        0.00       98.65
13:00:52        0.07        0.00        0.01        0.00        0.00       99.91
13:00:53        1.19        0.00        0.15        0.00        0.00       98.63

Memory information:

[opc@deploy1 ~]$ pmrep :mem
              free(mb)   bufhead(mb)  pagecache(mb  kernelstack(      slab(mb)  pagetables(m    mapped(mb)      anon(mb)       shm(mb)   swapuse(mb)  hugefree(mb)  hugeused(mb)
13:08:33           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A
13:08:34        237199           320         17056            16          1113            23            62          1048            27             0             0             0
13:08:35        237258           320         17056            16          1113            24            62           991            27             0             0             0
13:08:36        237200           320         17056            16          1113            24            62          1046            27             0             0             0
13:08:37        237255           320         17056            16          1113            24            62           991            27             0             0             0
13:08:38        237212           320         17056            16          1113            24            62          1034            27             0             0             0

Network:

[opc@deploy1 ~]$ pmrep :nw-pkt
          pck/in  pck/in  pck/in  err/in  err/in  err/in  drp/in  drp/in  drp/in  pck/out  pck/out  pck/out  err/out  err/out  err/out  drp/out  drp/out  drp/out
            eth0    eth1      lo    eth0    eth1      lo    eth0    eth1      lo     eth0     eth1       lo     eth0     eth1       lo     eth0     eth1       lo
13:08:58     N/A     N/A     N/A     N/A     N/A     N/A     N/A     N/A     N/A      N/A      N/A      N/A      N/A      N/A      N/A      N/A      N/A      N/A
13:08:59  10.972   0.000  29.924   0.000   0.000   0.000   0.000   0.000   0.000   16.957    0.000   29.924    0.000    0.000    0.000    0.000    0.000    0.000
13:09:00   0.996   0.000  103.63   0.000   0.000   0.000   0.000   0.000   0.000    0.996    0.000  103.627    0.000    0.000    0.000    0.000    0.000    0.000
13:09:01   0.996   0.000  29.892   0.000   0.000   0.000   0.000   0.000   0.000    0.996    0.000   29.892    0.000    0.000    0.000    0.000    0.000    0.000
13:09:02   0.996   0.000  94.653   0.000   0.000   0.000   0.000   0.000   0.000    0.996    0.000   94.653    0.000    0.000    0.000    0.000    0.000    0.000

Disk:

[opc@deploy1 ~]$ pmrep :disk-iops
          riops  wiops  iops
            sda    sda   sda
13:09:23    N/A    N/A   N/A
13:09:24      0      0     0
13:09:25      0      0     0
13:09:26      0      0     0
13:09:27      0      3     3
13:09:28      0      0     0

As you can see, all these statistics where provided using a utility called ‘pmrep’ (reporter utility). They were fetched on runtime. I can go back in time when I use the archive that the pcp logger creates and use -S (starttime):

[opc@deploy1 ~]$ pmrep -a /var/log/pcp/pmlogger/deploy1/20170113.00.10 -S @-1min :sar-u
               %user       %nice        %sys     %iowait      %steal       %idle
13:22:36         N/A         N/A         N/A         N/A         N/A         N/A
13:22:37        0.65        0.00        0.11        0.00        0.00       99.22
13:22:38        0.65        0.00        0.11        0.00        0.00       99.22
13:22:39        0.65        0.00        0.11        0.00        0.00       99.22
13:22:40        0.65        0.00        0.11        0.00        0.00       99.22
13:22:41        0.65        0.00        0.11        0.00        0.00       99.22
13:22:42        0.65        0.00        0.11        0.00        0.00       99.22
13:22:43        0.65        0.00        0.11        0.00        0.00       99.22
13:22:44        0.65        0.00        0.11        0.00        0.00       99.22
13:22:45        0.65        0.00        0.11        0.00        0.00       99.22
13:22:46        0.65        0.00        0.11        0.00        0.00       99.22
13:22:47        0.65        0.00        0.11        0.00        0.00       99.22
13:22:48        0.65        0.00        0.11        0.00        0.00       99.22

The archive is stored in /var/log/pcp/pmlogger/hostname/YYYYMMDD.00.10 for the current day, the older archives are also stored as YYYYMMDD, but without ‘.00.10’ added to it. Above the syntax for using an archive and specifying a relative time is shown (-S for start, then a number and a time specification like sec, min, hour). Of course you can specify absolute times too, like ‘-S @13:22 -T @13:25’. -T is the until time.

Did you notice the measurements read from the archive above are per second?? The metrics are actually measured per minute (this specified in /var/lib/pcp/config/pmlogger/config.default), the pmrep tool extrapolates the values per minute. I am not really happy about that, but you can use ‘-u’ to disable per second extrapolation.

When doing investigation, you often want all data from a certain point in time because quite often you don’t know upfront what exact you are going to encounter. This is also true for troubleshooting, proof of concept testing, performance analysis, benchmarking, etc. You want to be able to do multidimensional analysis after the investigated event, probably even offline. This is a point where PCP truly shines. It easy to create an archive of only the relevant time using the ‘pmlogextract’ utility:

[opc@deploy1 ~]$ pmlogextract -S 12:30 -T 12:40 /var/log/pcp/pmlogger/deploy1/20170113.00.10 this

The above pmlogextract command reads the (current, there’s .00.10 attached) archive /var/log/pcp/pmlogger/deploy1/20170113.00.10 and creates the archive ‘this’ with all measurements between 12:30 and 12:40. A PCP archive consists of three files:

[opc@deploy1 ~]$ ls -ls this*
236 -rw-rw-r--. 1 opc opc 238212 Jan 13 14:05 this.0
  4 -rw-rw-r--. 1 opc opc    192 Jan 13 14:05 this.index
 20 -rw-rw-r--. 1 opc opc  18456 Jan 13 14:05 this.meta

This archive is endian-independent, and can be transferred to your laptop for analysis (provided you got the PCP executables on it of course). Please mind an archive contains a subset of all available performance metrics, in order to save space. My default installation has 1620 metrics available, there are 280 stored in the archive.

But it doesn’t stop here. PCP not only installs a logger daemon (pmlogger) and a couple of agents to extract data (pmdaroot, pmdaproc, pmdaxfs, pmdalinux), but also the ‘pmcd’ daemon (pcp collector deamon) listening on port 44321. This means you can fetch metrics over the network:

[opc@deploy1 ~]$ pmrep -h oracleserver.local :mem
              free(mb)   bufhead(mb)  pagecache(mb  kernelstack(      slab(mb)  pagetables(m    mapped(mb)      anon(mb)       shm(mb)   swapuse(mb)  hugefree(mb)  hugeused(mb)
15:25:18           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A           N/A
15:25:19        507244           165          5436            19           499           165          1267           817          3015             0             0             0
15:25:20        507244           165          5436            19           499           165          1267           817          3015             0             0             0
15:25:21        507244           165          5436            19           499           165          1267           817          3015             0             0             0
15:25:22        507244           165          5436            19           499           165          1267           817          3015             0             0             0
15:25:23        507244           165          5436            19           499           165          1267           817          3015             0             0             0
15:25:24        507244           165          5436            19           499           165          1267           817          3015             0             0             0

This is running my memory overview report fetching current data from the server ‘oracleserver.local’! Sadly, currently is fetching historical data using pmrep via the ‘pmcd’ daemon not supported.

The last point I wanted to focus the attention on is that gathering PCP collected performance data on a central host is built-in in PCP using the logger functionality. Provided the collection host can access the remote host on tcp/44321, it’s really simple (example for above mentioned oracleserver.local host). This is all that needs to be done on the collection host. Nothing needs to be done on the host that needs to provide the data:

[root@deploy1 ~]# echo 'oracleserver.local 	n   n	PCP_LOG_DIR/pmlogger/oracleserver.local	-r -T24h10m -c config.oracleserver.local' >> /etc/pcp/pmlogger/control
[root@deploy1 ~]# service pmlogger restart

The installation of PCP via the oracle linux 6 or 7 repository is really simple (provided you enabled the ‘latest’ repository):

[root@deploy1 ~]# yum install pcp pcp-system-tools
[root@deploy1 ~]# chkconfig pmcd on
[root@deploy1 ~]# chkconfig pmlogger on
[root@deploy1 ~]# service pmcd start
[root@deploy1 ~]# service pmlogger start

ps. I gotten a message ‘Error: Failed to create avahi client: Daemon not running’ when executing ‘service pmcd start’, but it does not seem to impact using PCP.

PS. the above mentioned way appears to still be working on OL7, but OL7 in reality is using systemd (actually, it is doing the systemctl commands under the hood). The correct way to enable and start the pmcd and pmlogger daemons is:

# systemctl enable pmcd
# systemctl enable pmlogger
# systemctl start pmcd pmlogger

A final remark: the pmrep reports (:sar-u, :mem, :nw-pkt, disk-iops) mentioned are NOT reports that are in the default installation of PCP. Create a file in the current working directory of the process executing pmrep named pmrep.conf, and use the following gist to fill the file with my report definitions. Actually, it’s really easy to create a report, and there plenty of examples with the ones I created. If the pmrep.conf file is not in the current working directory, it needs to be specific using -c /path/to/pmrep.conf.

Creating a RAC 12.1 Data Guard Physical Standby environment (2)

In the first part of this mini-series you saw me define the environment as well as creating a primary database. With that out of the way it’s time to think about the standby. Before the standby can be created, a few preparations are necessary both on the primary as well as the standby cluster.

NOTE: As always, this is just a demonstration using VMs in my lab, based on my notes. Your system is most likely different, so in real-life you might take a different approach. The techniques I am using here were suitable for me, and my own small scale testing. I tried to make sure they are valid, but you may want to allocate more resources in your environment. Test, test, test on your own environment on test kit first!

Preparing the Creation of the Standby Database

It’s easier to follow the discussion if you keep in mind that my primary database is NCDBA and the standby is NCDBB.

Step 1: Listener Configuration

The first step to be completed is the network setup. I am planning on using an active database duplication which requires my node listeners on the cluster to “know” about my database instances. Thankfully since RAC 12.1 we don’t need to register the *_DGMGRL service (used for switchover operations by the Broker) anymore, making life a bit easier. The static registration of databases may not be necessary if you are using a different RMAN duplicate approach.

By default Oracle does not statically register databases, so it’s up to you to do so. On rac12sec1 I added a SID_LIST_LISTENER section (note: only the node listener!) to /u01/app/12.1.0.2/grid/network/admin/listener.ora. Remember that the node listeners reside in the GRID_HOME.

...
SID_LIST_LISTENER =
  (SID_LIST =
...
    (SID_DESC =
      (GLOBAL_DBNAME = NCDBB)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = NCDBB1)
    )
...
  )
...

Likewise, I added a static registration to rac12sec2 – identical to the previous example except for the ORACLE_SID, which is NCDBB2 of course. After which I reloaded the listeners on both nodes.

Step 2: Service Name Configuration

The next file to change is the RDBMS home’s tnsnames.ora. To make sure I cover all eventualities I created the following tnsnames.ora on all cluster nodes, primary and standby RAC

[oracle@rac12pri1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NCDBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac12pri-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NCDBA)
    )
  )

NCDBB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac12sec-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = NCDBB)
    )
  )

# only used for RMAN duplicate ... from active database
NCDBA_DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac12pri1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac12pri2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = NCDBA)
    )
  )

NCDBB_DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac12sec1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac12sec2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = NCDBB)
    )
  )

The first two entires are self-explanatory. The second pair requires a little more explanation. Since I have statically registered my standby database with the node listeners only (eg not with the SCAN listeners-I never tried and don’t like the thought of that) the duplicate would fail trying to restart the auxiliary instance. I haven’t captured the output of the failed RMAN duplicate but it’s possible to show you the effect of using the “wrong” tnsname with a simple test (the database I want to connect to is of course down)

[oracle@rac12pri1 ~]$ sqlplus sys@NCDBB as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 14 14:36:49 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 
[oracle@rac12pri1 ~]$ sqlplus sys@NCDBB_DUP as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 14 14:36:59 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: 
Connected to an idle instance.

SQL> 

So it seems to be better to use the net service name where I can be sure the database can be controlled from a remote session :)

NB: this test required a password file for the standby database which doesn’t exist yet and will be created in part 3.

Step 3: Modify the oratab file

You also should change the oratab and add NCDBB on both standby hosts, as shown here for and rac12sec2.

[oracle@rac12sec2 ~]$ grep NCDBB /etc/oratab
NCDBB:/u01/app/oracle/product/12.1.0.2/dbhome_1:N

Step 4: Prepare the Primary Database: Archivelog mode, Force Logging, and maybe even Flashback Database

There are a number of tasks to be completed on the primary database before I can start duplicating it for a standby environment. I need to make sure it’s in Archivelog mode, and that force logging is enabled. If I can afford to take the overhead, I enable Flashback Database as well to help reinstating a failed primary should that need arise. Of course, you don’t just enable Flashback Database, because it can have a negative impact on performance, and the Flashback logs will use space. Your FRA space usage calculation can be derailed if you are not careful. Thorough testing is needed to assess whether it is ok to operate with Flashback Database enabled.

The first task in my scenario is to enable Archivelog mode and to enforce logging.

[oracle@rac12pri1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 14 09:52:11 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Current log sequence           16
SQL> exit

...

[oracle@rac12pri1 ~]$ srvctl stop database -d NCDBA
[oracle@rac12pri1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 14 10:00:34 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size             838863984 bytes
Database Buffers         2365587456 bytes
Redo Buffers               13844480 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL>  alter database open;

Database altered.

SQL> exit

It is easy to check if that was a success:

[oracle@rac12pri1 ~]$ srvctl start instance -db NCDBA -node rac12pri2

[oracle@rac12pri1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 14 15:04:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   16
Current log sequence           16

Step 5: Add Standby Redo Logs

In order to use Real Time Apply you need Standby Redo Logs. If you create them now (eg before the RMAN duplicate) you are saving time. First of all you don’t need to create Standby Redo Logs (SRL) post switchover when NCDBA operates as a standby database. Secondly, if there are SRLs in place, they are automatically created on the standby database as part of the RMAN duplicate.

You need n+1 SRLs per thread. You can work the number out by looking at v$log. My tiny database has 2 threads with 2 members each – ok for the lab, almost certainly not ok for anything serious. Helps me keep storage footprint in the lab low though.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         15   52428800        512          2 YES INACTIVE               1621242 14-DEC-16      1621618 14-DEC-16          0
         2          1         16   52428800        512          2 NO  CURRENT                1621618 14-DEC-16   2.8147E+14                    0
         3          2          1   52428800        512          2 YES INACTIVE               1620677 14-DEC-16      1628674 14-DEC-16          0
         4          2          2   52428800        512          2 NO  CURRENT                1629454 14-DEC-16   2.8147E+14 14-DEC-16          0

SQL> 

With that information I can create 3 SRLs per thread. Did I mention that I love Oracle Managed Files?

SQL> alter database add standby logfile thread 1 size 52428800;

Database altered.

SQL> /

Database altered.

SQL> /

Database altered.

SQL> alter database add standby logfile thread 2 size 52428800;

Database altered.

SQL> /

Database altered.

SQL> /

Database altered.

SQL> select group#,thread#,sequence#,bytes,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------
         5          1          0   52428800 UNASSIGNED
         6          1          0   52428800 UNASSIGNED
         7          1          0   52428800 UNASSIGNED
         8          2          0   52428800 UNASSIGNED
         9          2          0   52428800 UNASSIGNED
        10          2          0   52428800 UNASSIGNED

6 rows selected.

The primary database should be in relatively good shape now – Flashback Database is off to simulate the case where my fictional load is considered to impact performance too much.

SQL> select name,db_unique_name,database_role,log_mode,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME  DATABASE_ROLE    LOG_MODE     FORCE_LOGGING  FLASHBACK_ON
--------- --------------- ---------------- ------------ -------------- ------------------
NCDBA     NCDBA           PRIMARY          ARCHIVELOG   YES            NO

SQL>

Summary

This concludes part 2. In this blog post I shared my notes about configuration the network for RMAN duplicate … from active database. I also configured the primary database in preparation for the initial duplicate command.

Note that I’m not setting any initialisation parameters related to Data Guard: over time I have become a great fan of the Data Guard Broker and plan on using that instead of a manual configuration.

use_nl hint

In response to a recent lamentation from Richard Foote about the degree of ignorance regarding the clustering_factor of indexes I commented on the similar level of understanding of a specific hint syntax, namely use_nl(a b) pointing out that this does not mean “do a nested loop from a to b”. My comment was underscored by a fairly prompt response asking what the hint did mean.

Surprisingly, although I’ve explained it many times over the last couple of decades (here’s one from 10 years ago), I couldn’t find an explanation on my blog though I did find a blog note where I’d made a passing comment about the equivalent misunderstanding of the use_hash(a b) syntax.

The misunderstanding is not entirely surprising since for many years the Oracle manuals seemed to suggest (in their examples) that the hint did have a multi-table meaning and it wasn’t until 10g that the manual gave an explicit statement of the single-table nature of the hint. The hint /*+ use_nl(a b) */ is a short-hand for the pair of hints /*+ use_nl(a)  use_nl(b) */ it doesn’t say anything about whether a and b should be joined, or in what order. If you want to guarantee that a and b will be joined in that order by a nested loop you will have to work a lot harder with your hints – and almost certainly need to make use of the /+ leading() */ hint.

Consider the following query (I’ll put the table creation code at the end of the article if you want to experiment):

select
	/*+ use_nl(a b) */
	a.v1, b.v1, c.v1, d.v1
from
	a, b, c, d
where
	d.n100 = 0
and	a.n100 = d.id
and	b.n100= a.n2
and	c.id = a.id
;

Only one of the tables a and b can be the first table in the final execution plan so one of them will be “the next table in the join order” at some point, so this hint will guarantee that one of the tables will be the inner table of a nested loop join. Here’s the plan I happened to get with my data, indexing, version (11.2.0.4), etc.:

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      | 20000 |  1347K| 30125   (1)| 00:00:02 |
|   1 |  HASH JOIN                     |      | 20000 |  1347K| 30125   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL            | C    | 10000 |   146K|    26   (4)| 00:00:01 |
|   3 |   HASH JOIN                    |      | 20000 |  1054K| 30098   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL           | D    |   100 |  1800 |    26   (4)| 00:00:01 |
|   5 |    NESTED LOOPS                |      | 20000 |   703K| 30072   (1)| 00:00:02 |
|   6 |     NESTED LOOPS               |      | 20000 |   703K| 30072   (1)| 00:00:02 |
|   7 |      TABLE ACCESS FULL         | B    | 10000 |   136K|    26   (4)| 00:00:01 |
|   8 |      INDEX RANGE SCAN          | A_I2 |     2 |       |     1   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID| A    |     2 |    44 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

In this case it’s table a that ends up in a position to be the inner table of a nested loop join.

You may be wondering why there seems to be a hash join into b when we’ve hinted a nested loop join – but the join order that Oracle is using is B -> A -> D -> C with a swap_join_inputs(d) swap_join_inputs(d), so b is never “the next table in the join order”.

If you want an even more confusing (at first sight) plan here’s the plan I got if I changed the one hint to /*+ use_nl(a) */


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 20000 |  1347K|   105   (5)| 00:00:01 |
|   1 |  HASH JOIN           |      | 20000 |  1347K|   105   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | B    | 10000 |   136K|    26   (4)| 00:00:01 |
|   3 |   HASH JOIN          |      | 10000 |   537K|    78   (4)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | C    | 10000 |   146K|    26   (4)| 00:00:01 |
|   5 |    HASH JOIN         |      | 10000 |   390K|    52   (4)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| D    |   100 |  1800 |    26   (4)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| A    | 10000 |   214K|    26   (4)| 00:00:01 |
-----------------------------------------------------------------------------

This plan really looks as if Oracle should have done a nested loop into a but didn’t. Again appearanced are deceptive thanks to the effects of swap_join_inputs(): the join order here is A -> D -> C -> B (note that we don’t have a use_nl(b) hint in this example).

If you want a plan where the optimizer produces a nested loop join between a and b you’ll need to put in a leading() hint which places b immediately after a somewhere in the list of tables with just use_nl(b) being sufficient to enforce the join method. Here, for example, is the plan with hints /*+ leading(d a b c) use_nl(b) */ for my data set:


----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        | 20000 |  1347K| 30164   (1)| 00:00:02 |
|   1 |  HASH JOIN                    |        | 20000 |  1347K| 30164   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL           | C      | 10000 |   146K|    26   (4)| 00:00:01 |
|   3 |   NESTED LOOPS                |        | 20000 |  1054K| 30137   (1)| 00:00:02 |
|   4 |    NESTED LOOPS               |        |  1000K|  1054K| 30137   (1)| 00:00:02 |
|   5 |     HASH JOIN                 |        | 10000 |   390K|    52   (4)| 00:00:01 |
|   6 |      TABLE ACCESS FULL        | D      |   100 |  1800 |    26   (4)| 00:00:01 |
|   7 |      TABLE ACCESS FULL        | A      | 10000 |   214K|    26   (4)| 00:00:01 |
|   8 |     INDEX RANGE SCAN          | B_I100 |   100 |       |     1   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID| B      |     2 |    28 |   101   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Notice, yet again, Oracle has done hash join to c with a swap_join_inputs().

Creation Script:


create table a
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4
)
select
	rownum				id,
	mod(rownum,5000)		n2,
	mod(rownum,100)			n100,
	lpad(rownum,10,'0')		v1,
	lpad('x',100,'x')		padding
from
        generator       v1
;

create table b nologging as select * from a;
create table c nologging as select * from a;
create table d nologging as select * from a;

alter table a add constraint a_pk primary key(id);
alter table b add constraint b_pk primary key(id);
alter table c add constraint c_pk primary key(id);
alter table d add constraint d_pk primary key(id);

create index a_i2 on a(n2) nologging;
create index b_i2 on b(n2) nologging;
create index c_i2 on c(n2) nologging;
create index d_i2 on d(n2) nologging;

create index a_i100 on a(n100) nologging;
create index b_i100 on b(n100) nologging;
create index c_i100 on c(n100) nologging;
create index d_i100 on d(n100) nologging;
begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'A',
		method_opt	 => 'for all columns size 1'
	);
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'B',
		method_opt	 => 'for all columns size 1'
	);
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'C',
		method_opt	 => 'for all columns size 1'
	);
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'D',
		method_opt	 => 'for all columns size 1'
	);
end;
/

Extending Flashback Data Archive in 12c

Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.

Create a tablespace to hold your history, and create a flashback archive using that space allocation.



SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB11\ARCH_SPACE.DBF' 
  3  size 100M;

Tablespace created.



SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created.

Then add your tables to the archive. Some tables will be created automatically to support being able to record all of the changes to that table for as long as the archive duration specifies


SQL> ALTER TABLE EMP FLASHBACK ARCHIVE LONGTERM;

Table altered.

SQL> select table_name
  2  from   user_tables
  3  /

TABLE_NAME
---------------------------
SYS_FBA_HIST_71036
SYS_FBA_TCRV_71036
SYS_FBA_DDL_COLMAP_71036
EMP

And then voila! We can track changes to our table over time, well beyond the typical undo_retention setting for the database.


SQL> select empno, ename, job, sal, comm,
  2      nvl(VERSIONS_STARTTIME,LAST_MOD) TS
  3     ,nvl(VERSIONS_OPERATION,'I') op
  4  from EMP
  5  versions between timestamp
  6    timestamp '2014-02-11 20:12:00' and
  7    systimestamp
  8  order by empno;


     EMPNO ENAME      JOB              SAL       COMM TS           O
---------- ---------- --------- ---------- ---------- ------------ -
      7369 SMITH      CLERK            806            08.10.51 PM  I
      7369 SMITH      SALES           8060       1000 08.12.10 PM  U
      7499 ALLEN      SALESMAN        1606  300000000 08.10.51 PM  I
      7521 WARD       SALESMAN        1256  500000000 08.10.51 PM  I
      7566 JONES      MANAGER         2981            08.10.51 PM  I
      ...
      7900 JAMES      CLERK            956            08.10.51 PM  I
      7902 FORD       ANALYST         3006            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.12.10 PM  D

But sometimes we want to know more about the history. We want to know who did the change, what machine were they on, etc etc. Auditors tend to be a little snippy about those kind of things Smile

  • You: “I can confirm that the SALARY field was updated at 9:01am on January 25th”
  • Auditor: “And who did that change?”
  • You: “Er….um…..someone”

But it might not be practical to augment all of your tables with WHO, WHEN, WHERE etc columns to capture that information. That might involve a lot of application changes and testing.

If you’re on 12c, you might be in luck. Flashback Data Archive can capture additional information, assuming your application is taking advantage of the myriad of instrumentation facilities offered by the database.

We simply turn on context tracking, which can capture information on a per transaction basis


SQL> begin
  2    dbms_flashback_archive.set_context_level(
  3        level=> 'ALL');
  4  end;

PL/SQL procedure successfully completed.

Now when we perform changes to the table, we can look at the transaction ID (XID) for that change.


SQL> update EMP
  2  set    sal = sal*10
  3  where  empno = 7499;

1 row updated.

SQL> commit;


SQL> select XID from SYS_FBA_HIST_510592;

XID
----------------
09000B00C7080000

And using that XID, we can pick up the context information about that transaction


SQL> set serverout on
SQL> begin
  2    dbms_output.put_line(
  3      dbms_flashback_archive.get_sys_context (
  4         '09000B00C7080000', 'USERENV', 'SESSION_USER'));
  5
  6    dbms_output.put_line(
  7      dbms_flashback_archive.get_sys_context (
  8         '09000B00C7080000', 'USERENV', 'HOST'));
  9
 10    dbms_output.put_line(
 11      dbms_flashback_archive.get_sys_context (
 12         '09000B00C7080000', 'USERENV', 'MODULE'));
 13  end;
 14  /
SCOTT
WORKGROUP\XPS
SQL*Plus

How much more ? Well, quite a lot actually !


SQL> desc SYS.SYS_FBA_CONTEXT_AUD

 Name                      Null?    Type
 ------------------------- -------- ------------------
 XID                                RAW(8)
 ACTION                             VARCHAR2(256)
 AUTHENTICATED_IDENTITY             VARCHAR2(256)
 CLIENT_IDENTIFIER                  VARCHAR2(256)
 CLIENT_INFO                        VARCHAR2(256)
 CURRENT_EDITION_NAME               VARCHAR2(256)
 CURRENT_SCHEMA                     VARCHAR2(256)
 CURRENT_USER                       VARCHAR2(256)
 DATABASE_ROLE                      VARCHAR2(256)
 DB_NAME                            VARCHAR2(256)
 GLOBAL_UID                         VARCHAR2(256)
 HOST                               VARCHAR2(256)
 IDENTIFICATION_TYPE                VARCHAR2(256)
 INSTANCE_NAME                      VARCHAR2(256)
 IP_ADDRESS                         VARCHAR2(256)
 MODULE                             VARCHAR2(256)
 OS_USER                            VARCHAR2(256)
 SERVER_HOST                        VARCHAR2(256)
 SERVICE_NAME                       VARCHAR2(256)
 SESSION_EDITION_NAME               VARCHAR2(256)
 SESSION_USER                       VARCHAR2(256)
 SESSION_USERID                     VARCHAR2(256)
 SESSIONID                          VARCHAR2(256)
 TERMINAL                           VARCHAR2(256)
 SPARE                              VARCHAR2(256)

And since we can use our flashback query syntax to pick up all of the transaction ID’s


SQL> select empno, ename, job, sal, comm,
  2      VERSIONS_XID
  3  from EMP
  4  versions between timestamp
  5    timestamp '2014-02-11 20:12:00' and
  6    systimestamp
  7  order by empno;

     EMPNO ENAME      JOB              SAL       COMM VERSIONS_XID
---------- ---------- --------- ---------- ---------- ----------------
      7369 SMITH      CLERK            806
      7369 SMITH      SALES           8060       1000 09001C00E04A0000
      7499 ALLEN      CLERK          16060  300000000 0A000A0024080000
      7499 ALLEN      SALESMAN       16060  300000000 09000B00C7080000
      7499 ALLEN      SALESMAN        1606  300000000
      7521 WARD       SALESMAN        1256  500000000

we can also take advantage of that new dictionary view SYS.SYS_FBA_CONTEXT_AUD to get all the context information for each transaction.


SQL> select ...
  2  from
  3  (  select
  4         empno, ename, job,
  5         sal, comm, VERSIONS_XID
  6     from EMP
  7     versions between timestamp
  8        timestamp '2014-02-11 20:12:00' and
  9        systimestamp
 10  ) e, SYS.SYS_FBA_CONTEXT_AUD  a
 11  where e.VERSIONS_XID = a.XID;

Flashback Data Archive is pretty nifty in 12c.

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01011

Two New Oracle Security Public Class Dates

I will be teaching two of my Oracle Security classes with Oracle University soon. The first is my class "Securing and Locking Down Oracle Databases". This class will be taught on the 24th January on-line via the Oracle LVC platform....[Read More]

Posted by Pete On 12/01/17 At 02:47 PM