Oracle 12c threaded_execution and netbackup

If you haven't started migrating your databases to 12c, you better start!  The last patchset of 11g runs out of free extended support at the end of January '16 and 11.2.0.3 on 8/27/2015.  Besides, 12c is a huge step forward for Oracle, if you take advantage of the new features.  You COULD just install the db and run it just like it was version 7.3 and not take advantage of its features...that would be easier, but you'd be doing the company you work for a disservice....besides...new features are what's fun about being a DBA!

If you've spent any time at all looking at the new features in 12c, you've probably come across "threaded_execution."  It essentially makes all connections to the databases (and most background "processes") threads (aka lightweight processes) instead of process in linux.  The advantage is an in-process library call rather than a cpu call when the OS need to switch between them.  Also, memory that didn't used to be "shareable" between processes is now shared.  In my performance tests you could see a measured improvement in performance, but the big advantage I found was in scalability.  There are lots of post talking about its performance and memory benefits.  This is one of the rare simple changes you can make where the user will notice a quicker system.  IMHO its something non-standard that should be a standard...like how hugepages should always be used, for instance.

Usually, when you do a ps -ef|grep ANDY (my instance name is ANDY) you'd see MANY oracle background processes.  When threaded execution is set to true, most of those processes are now threads...so now when you do a ps -ef|grep ANDY you only see these 6:

oracle   48460     1  0 14:08 ?        00:00:00 ora_pmon_ANDY
oracle   48462     1  0 14:08 ?        00:00:01 ora_psp0_ANDY
oracle   48464     1  4 14:08 ?        00:03:03 ora_vktm_ANDY
oracle   48468     1  0 14:08 ?        00:00:12 ora_u004_ANDY
oracle   48482     1  0 14:08 ?        00:00:08 ora_dbw0_ANDY
oracle   50665     1  1 14:10 ?        00:00:57 ora_u005_ANDY

Like the great philosopher Bon Jovi once said, "Every rose has its thorn."

The main problem with the threaded execution architecture in Linux is that OS authentication no longer works.  (ie:no more sqlplus "/as sysdba")  I've been able to take advantage of other new 12c features to compensate for that in our scripting, but its kind of a pain.  Even so, its worth it to get the most out of the system.  Besides, you could argue that no OS Authentication could mean better security.

While doing extensive testing with this feature I hit a problem.  Although Symantec supports 12c in 7.6.0.2+, there was a problem where the environment variables passed in with my allocate channel commands weren't getting to Netbackup.

run
{
  allocate channel t1 DEVICE TYPE SBT parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_1)';
  allocate channel t2 DEVICE TYPE SBT parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_2)';
  allocate channel t3 DEVICE TYPE SBT parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_3)';
  backup filesperset 8 database format '%d_%U';
}

In the script above, NB_ORA_POLICY was being reported as Oracle_Master_1 for each channel in the NB logs and on the NB console.  The other 2 channels would eventually error out and the backup would complete VERY slowly on the one remaining channel.  If I set threaded_execution=false, NB worked fine on all 3 channels as usual.  I searched the internet for a solution and came up with nothing...I created an SR with Oracle...the response was "Contact your MML vendor."  I created a ticket with Symantec who escalated it to their Engineering group and I worked closely with a great on-site Symantec consultant, but we weren't able to get it to work.  The Symantec guys aren't familiar with the new architecture, so as I explained to them how connections come in first to the listener, then a thread is created for the user...*BAM* the solution hit me:

THE FIX:
Make RMAN use processes, make everything else use threads.

For threaded execution to make threaded connections, you have to add a line to your listener.ora file:

DEDICATED_THROUGH_BROKER_[listener_name] = ON

I already had a normal listener for TCP connections...I created a new (2nd) static listener using IPC and set "dedicated_through_broker" to OFF (the default) for that listener, then I created the entry in my db's tnsnames.ora.

In listener.ora:

lsnr-rman =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCRMAN))
      )
    )
  )

SID_LIST_lsnr-rman=
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ANDY)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
    )

  )

DEDICATED_THROUGH_BROKER_lsnr_rman= OFF

...then I started the new listener:

lsnrctl start lsnr-rman

Then I added the alias in tnsnames.ora:

rman =
  (DESCRIPTION=

     (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCANDY))(CONNECT_DATA=(SID=ANDY))(HS=))

The last thing I had to do was change the backup script to use the connect string...usually used to connect to remote nodes on RAC to load balance the RAC backups:

run
{
  allocate channel t1 DEVICE TYPE SBT connect rman_user/changeme@rman parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_1)';
  allocate channel t2 DEVICE TYPE SBT connect rman_user/changeme@rman parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_2)';
  allocate channel t3 DEVICE TYPE SBT connect rman_user/changeme@rman parms 'BLKSIZE=4194304, ENV=(NB_ORA_POLICY=Oracle_Master_3)';
  backup filesperset 1 database format '%d_%U';

}

When I kicked off the backup, I could see the new PROCESSES were created and connected to my threaded_execution db:

oracle   48460     1  0 14:08 ?        00:00:00 ora_pmon_ANDY
oracle   48462     1  0 14:08 ?        00:00:01 ora_psp0_ANDY
oracle   48464     1  4 14:08 ?        00:03:03 ora_vktm_ANDY
oracle   48468     1  0 14:08 ?        00:00:12 ora_u004_ANDY
oracle   48482     1  0 14:08 ?        00:00:08 ora_dbw0_ANDY
oracle   50665     1  1 14:10 ?        00:00:57 ora_u005_ANDY
oracle   56718     1  3 15:18 ?        00:00:00 oracleANDY (LOCAL=NO)
oracle   56720     1  3 15:18 ?        00:00:00 oracleANDY (LOCAL=NO)
oracle   56722     1  1 15:18 ?        00:00:00 oracleANDY (LOCAL=NO)

oracle   56763 36022  0 15:18 pts/2    00:00:00 grep ANDY

...and now the backup is working as expected.  The two downsides I see to this is that:

1. You can't use sysbackup privs to run your backup...to connect this way you seem to need sysdba privs.
2. You have to have the init.ora parameter backup_tape_io_slaves=false...which I always usually set to true to make backups more efficient.

I suspect this isn't a Netbackup issue, but an issue with the way allocate channel commands pass env variables in the new architecture.  After all, they're process env variables and all the allocate channel commands are using the same process (just different threads.)  Whatever you're using to backup your database...if you're using the great new feature threaded_execution, I hope you find this post useful. :)


I recently had a similar issue with the 12c OEM agent which uses 11.1.0.7 thin jdbc connections to the database.  I used the same work around...here are the details.

Constructing an effective IT team

Margy Ross (President of the Kimball Group, founded by the father of DW) wrote a great article called "Risky Project Resources are Risky Business."  She was focused specifically on DW/BI projects, but I think her article applies to all effective teams, and I want to pass it on. 

Risky Project Resources are Risky Business

Over the years, we’ve worked with countless exemplary DW/BI project team members: smart, skilled, dedicated, and motivated, coupled with a healthy dose of mutual trust, respect, and camaraderie with their teammates. Teams with members who possess these characteristics tend to fire on all cylinders, with the resulting whole often greater than the sum of the parts. But we’ve also run into risky project resources; in addition to being individual non-contributors, they can undermine the effectiveness of the entire DW/BI team. Model team members often become short-timers if the team is stacked with unproductive non-performers. We hope your team doesn’t include resources that resemble the following profiles:

• Obstructionist debaters are perpetual naysayers who find fault with everything and get more satisfaction from the process of debating than the process of delivering.

• Heat seekers who are always anxious to try the latest, greatest technical gadgets and gizmos regardless of whether they align with the DW/BI project’s objectives.

• Cookie cutters continue to do exactly what’s worked for them in the past, regardless of their latest assignment’s nuances.

• Weed dwellers lose sight of the forest from the trees, focusing exclusively on the nitty-gritty details without regard to the bigger picture.

• Perpetual students and researchers want to read, read, and then read some more, but are disinclined to ever take action because there’s always more to learn.

• Independent spirits march to their own drummer without regard to rules, standards or accepted best practices.

• Honesty dodgers and problem hiders are always nodding “yes” and saying “no problem,” even when serious issues are lurking just around the corner.

• Dysfunctional incompetents and mental retirees are checked out and unable to perform.

• Self-declared “know it all” experts don’t need to listen because they already have all the answers – just ask them!

• Threatened worriers are so paralyzed with fear about what might happen that they respond by doing nothing at all.

Of course, even with superstar teammates, the right team leadership is also necessary. Hopefully your DW/BI project/program manager fits the following bill:

• Establishes a partnership with the business, including joint ownership for the DW/BI project/program, in part because they’re respected by the business as being user-oriented rather than technology-focused.

• Demonstrates excellent interpersonal and organizational skills since the DW/BI project/program is a political and cultural animal.

• Recruits and retains resources with the talent to deliver, gets them operating cohesively from a common playbook, and understands that adding more mediocre players won’t increase the team’s chances of winning. Conversely, they also spot individuals who are slowing down the effort and proactively counsel them (or at least minimize the risk of project derailment.)

• Listens keenly, plus communicates effectively and honestly, setting appropriate expectations and having the courage to say “no” when necessary.

• Optimally possesses some DW/BI domain expertise, in addition to strong project management skills. At a minimum, they’re staying one chapter ahead of the project team in The Data Warehouse Lifecycle Toolkit…

• Understands that DW/BI success is directly tied to business acceptance. Period.