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.
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.
Very useful info. Nicely explained. Thanks much for Sharing.
ReplyDelete