It has finally happened! The first public release of the MySQL plug-in for Oracle Enterprise Manager 10g Grid Control is out.
It’s been a while since I first started to work on this, first as part of the Grid Control Extensibility article that I wrote for IOUG SELECT Magazine in 2006 (thanks to John Kanagaraj […]
Today my manager called me to give a news that he is going to Malaysia for a program. He called me up to ask about the place , how one manages there, which are the good hotels and about all other usual stuff that one needs to know when he is visiting at a place for the first […]
Today my manager called me to give a news that he is going to Malaysia for a program. He called me up to ask about the place , how one manages there, which are the good hotels and about all other usual stuff that one needs to know when he is visiting at a place for the first […]
From time to time the Data Guard broker log file shows ORA-04031 errors. The primary site cannot connect to the standby site anymore.
DG 2008-04-09-23:31:44 0 2 0 RSM0: Failed to connect to remote database MYDB_SITE2. Error is ORA-04031
It seems that the available shared_pool is not sufficient on the standby site. Let's have a deeper look.
On the both sides (primary and standby) we have sga_target = sga_max_size = 800M :
SYS@MYDB > show parameter sga_
NAME TYPE VALUE
—————— ———– ————
sga_max_size big integer 800M
sga_target big integer 800M
On the production server the current shared pool size is about 500 MB (automatically tuned by Oracle):
SYS@MYDB > @ssinipar
Wrote file temp.tmp
Enter value for parameter: shared_pool_size
Parameter Session
—————————- ————-
__shared_pool_size 494927872
shared_pool_size 100663296
However, on the standby server the current shared pool site is about 240 MB :
Parameter Session
————————– ————-
__shared_pool_size 239075328
shared_pool_size 100663296
Furthermore, the standby database has less free shared pool than the primary database, it is also quite fragmented.
On standby:
POOL NAME BYTES
———— —————- ———-
shared pool free memory 18534000
On primary:
POOL NAME BYTES
———— —————- ———-
shared pool free memory 68049224
On the primary site a lot of resize operations have been performed by Oracle, it seems that this application has a higher parsing than I/O activity :
COMPONENT CURRENT_SIZE OPER_COUNT LAST_OPER_TIME
——————– ———— ———- ——————-
shared pool 494927872 142 10-APR-008 00:47:23
streams pool 4194304 43 09-APR-008 22:05:46
DEFAULT buffer cache 260046848 185 10-APR-008 00:47:23
The streams pool size can be explained by data pump, which is used to perform the logical backup on the primary side.
The database requires much more shared pool that database block cache. On the standby side only 46 (23+23) SGA resize operations took place, compared to the 370 (142 + 43 + 185) and more on the primary side.
COMPONENT CURRENT_SIZE OPER_COUNT LAST_OPER_TIME
————————- ———— ———- ——————-
shared pool 239075328 23 10-APR-008 15:11:31
DEFAULT buffer cache 520093696 23 10-APR-008 15:11:31
It is also very interesting to observe that the standby database handles much more database blocks than the primary database:
On the standby side:
select count(*) from v$bh;
COUNT(*)
———-
61748
On the primary database:
select count(*) from v$bh;
COUNT(*)
———-
30591
This can be explained by the fact that the standby database only performs recovery. The blocks are built from the standby redo log file, loaded into the buffer cache in order to be applied on the database which is in recovery mode.
However, the Data Guard broker and its processes also need memory in the shared pool to work. Unfortunately, because the standby database allocates much more database block cache memory than shared pool in the SGA, some ORA-4031 can occur.
Example statement on the standby site :
SELECT dest_id FROM v$archive_dest WHERE (target='LOCAL' OR target='PRIMARY') AND destination = '/export/oradata/MYDB/data99/archivelog'
AND (valid_type <> 'ONLINE_LOGFILE') AND (valid_role <> 'PRIMARY_ROLE') AND (dest_id <= 10) AND (dest_name not in ( select alternate from v
$archive_dest))
Several conclusions can be drawn from this analysis:
- After activation, the standby database will have a SGA configuration which may be in conflict with the application requirements (more cache size than shared pool)
- Oracle does not seem to transfer the SGA modifications from the primary to the standby site but sets the parameters to the requirements of each role (more cache size on the standby site)
- It is not a good idea to set a smaller SGA size on the standby side in order to save some memory space, this error will be amplified.
In some cases, flushing the shared pool could help:
SYS@MYDB > alter system flush shared_pool;
System altered.
A solution could be to set the shared_pool_size to a value corresponding to the lowest limit allowing to operate the standby database without (or with less) ORA-4031 errors. This low-limit seems to be at around 300-350 MB.
From time to time the Data Guard broker log file shows ORA-04031 errors. The primary site cannot connect to the standby site anymore.
DG 2008-04-09-23:31:44 0 2 0 RSM0: Failed to connect to remote database MYDB_SITE2. Error is ORA-04031
It seems that the available shared_pool is not sufficient on the standby site. Let's have a deeper look.
On the both sides (primary and standby) we have sga_target = sga_max_size = 800M :
SYS@MYDB > show parameter sga_
NAME TYPE VALUE
—————— ———– ————
sga_max_size big integer 800M
sga_target big integer 800M
On the production server the current shared pool size is about 500 MB (automatically tuned by Oracle):
SYS@MYDB > @ssinipar
Wrote file temp.tmp
Enter value for parameter: shared_pool_size
Parameter Session
—————————- ————-
__shared_pool_size 494927872
shared_pool_size 100663296
However, on the standby server the current shared pool site is about 240 MB :
Parameter Session
————————– ————-
__shared_pool_size 239075328
shared_pool_size 100663296
Furthermore, the standby database has less free shared pool than the primary database, it is also quite fragmented.
On standby:
POOL NAME BYTES
———— —————- ———-
shared pool free memory 18534000
On primary:
POOL NAME BYTES
———— —————- ———-
shared pool free memory 68049224
On the primary site a lot of resize operations have been performed by Oracle, it seems that this application has a higher parsing than I/O activity :
COMPONENT CURRENT_SIZE OPER_COUNT LAST_OPER_TIME
——————– ———— ———- ——————-
shared pool 494927872 142 10-APR-008 00:47:23
streams pool 4194304 43 09-APR-008 22:05:46
DEFAULT buffer cache 260046848 185 10-APR-008 00:47:23
The streams pool size can be explained by data pump, which is used to perform the logical backup on the primary side.
The database requires much more shared pool that database block cache. On the standby side only 46 (23+23) SGA resize operations took place, compared to the 370 (142 + 43 + 185) and more on the primary side.
COMPONENT CURRENT_SIZE OPER_COUNT LAST_OPER_TIME
————————- ———— ———- ——————-
shared pool 239075328 23 10-APR-008 15:11:31
DEFAULT buffer cache 520093696 23 10-APR-008 15:11:31
It is also very interesting to observe that the standby database handles much more database blocks than the primary database:
On the standby side:
select count(*) from v$bh;
COUNT(*)
———-
61748
On the primary database:
select count(*) from v$bh;
COUNT(*)
———-
30591
This can be explained by the fact that the standby database only performs recovery. The blocks are built from the standby redo log file, loaded into the buffer cache in order to be applied on the database which is in recovery mode.
However, the Data Guard broker and its processes also need memory in the shared pool to work. Unfortunately, because the standby database allocates much more database block cache memory than shared pool in the SGA, some ORA-4031 can occur.
Example statement on the standby site :
SELECT dest_id FROM v$archive_dest WHERE (target='LOCAL' OR target='PRIMARY') AND destination = '/export/oradata/MYDB/data99/archivelog'
AND (valid_type <> 'ONLINE_LOGFILE') AND (valid_role <> 'PRIMARY_ROLE') AND (dest_id <= 10) AND (dest_name not in ( select alternate from v
$archive_dest))
Several conclusions can be drawn from this analysis:
- After activation, the standby database will have a SGA configuration which may be in conflict with the application requirements (more cache size than shared pool)
- Oracle does not seem to transfer the SGA modifications from the primary to the standby site but sets the parameters to the requirements of each role (more cache size on the standby site)
- It is not a good idea to set a smaller SGA size on the standby side in order to save some memory space, this error will be amplified.
In some cases, flushing the shared pool could help:
SYS@MYDB > alter system flush shared_pool;
System altered.
A solution could be to set the shared_pool_size to a value corresponding to the lowest limit allowing to operate the standby database without (or with less) ORA-4031 errors. This low-limit seems to be at around 300-350 MB.
I confess I had never heard of Prouvé before I came across this exhibition at London’s Design Museum but the title grabbed me. If I had have known how interesting and relevant Prouvé was I would not have left it to the last minute to go. I think he’s not better known outside of France because he mainly worked on municipal projects.
He never formally trained as an architect; so although he did work on the design of buildings, his is not the name which tends to be associated with them. His most iconic designs are chairs. But these are chairs for university halls of residence, works canteens and classrooms, not the sort of chairs which grace Notting Hill living rooms.
Although he came from an artistic background Prouvé started out as an artisanal blacksmith in 1919. He quickly moved from wrought ironwork into steel and aluminium, but he always remained rooted in the practice of working with materials. He designed through trials and testing of concepts.
“…one should not sketch out utopian projects, because evolution can only result from practical experience.”
This commitment to evolution is demonstrated by a display of Standard Chairs, variations on a theme produced by Prouvé’s workshop over the course of two decades. The basic shape and configuration of Chair No.305 is not markedly different from Chair No.4. There are minor tweaks, and there are variations in material: wood, steel or aluminium, plain or lacquered. The biggest adaptation was the collapsible Standard Chair.
As an artisan and then a factory owner he understood the properties of wood and metal and their appropriate usages. Designers and architects more driven by the need to appear avant garde tended to get carried away with the thrill of new materials and looking modern. Prouvé appreciated that good design had to come from functional success: no matter how striking it looks, a chair is no good if it is not comfortable to sit in. An example is the Solvay table, which is made of wood bolted together with lacquered steel. The engineering of the table is not hidden, it is part of the aesthetic, but neither is it fetishised.
Prouvé was a early adopter of the concept of design patterns. He assembled a dictionary of structures which could be reused in different situations and scales. The crutch - a asymmetric Y shape - which supports the roof of the Pump House at Evian re-appears in the design of an armchair. He devised a roof made of single curved pieces of steel. These shells were light enough for two men to slot them together. At a larger scale this shape could be rested on the ground to form vaulted halls. One favourite shape, a elongated pentagon, appears repeatedly in his work: as the back legs of the Standard Chair, as the legs of various tables, in the cross-section of a table top, even as the handles of a sideboard.
I tend to be wary of attempts to draw parallels between our industry and branches of engineering or architecture, as these strike me as attempts to lend software development a spurious sense of discipline. Just calling it “software engineering” does not make writing a program as rigourous an activity as building a motorway flyover. However, with his commitment to iteration, re-use, modification and adaptation, and his championing of practice over theory it is hard not to regard Jean Prouvé as the Godfather of Extreme Programming.
The other exhibition at the Design Museum featured lots of modern work. One of the most striking exhibits was a chair “sketched” by a Japanese design house called FRONT. Their designers have developed a mechanism for designing furniture through motion capture and then rendering the designs using extruded plastic. Unlike Prouvé’s work you probably wouldn’t want to sit on the chair or rest a cup of coffee on the table but the process is fascinating to watch.
I confess I had never heard of Prouvé before I came across this exhibition at London’s Design Museum but the title grabbed me. If I had have known how interesting and relevant Prouvé was I would not have left it to the last minute to go. I think he’s not better known outside of France because he mainly worked on municipal projects.
He never formally trained as an architect; so although he did work on the design of buildings, his is not the name which tends to be associated with them. His most iconic designs are chairs. But these are chairs for university halls of residence, works canteens and classrooms, not the sort of chairs which grace Notting Hill living rooms.
Although he came from an artistic background Prouvé started out as an artisanal blacksmith in 1919. He quickly moved from wrought ironwork into steel and aluminium, but he always remained rooted in the practice of working with materials. He designed through trials and testing of concepts.
“…one should not sketch out utopian projects, because evolution can only result from practical experience.”
This commitment to evolution is demonstrated by a display of Standard Chairs, variations on a theme produced by Prouvé’s workshop over the course of two decades. The basic shape and configuration of Chair No.305 is not markedly different from Chair No.4. There are minor tweaks, and there are variations in material: wood, steel or aluminium, plain or lacquered. The biggest adaptation was the collapsible Standard Chair.
As an artisan and then a factory owner he understood the properties of wood and metal and their appropriate usages. Designers and architects more driven by the need to appear avant garde tended to get carried away with the thrill of new materials and looking modern. Prouvé appreciated that good design had to come from functional success: no matter how striking it looks, a chair is no good if it is not comfortable to sit in. An example is the Solvay table, which is made of wood bolted together with lacquered steel. The engineering of the table is not hidden, it is part of the aesthetic, but neither is it fetishised.
Prouvé was a early adopter of the concept of design patterns. He assembled a dictionary of structures which could be reused in different situations and scales. The crutch - a asymmetric Y shape - which supports the roof of the Pump House at Evian re-appears in the design of an armchair. He devised a roof made of single curved pieces of steel. These shells were light enough for two men to slot them together. At a larger scale this shape could be rested on the ground to form vaulted halls. One favourite shape, a elongated pentagon, appears repeatedly in his work: as the back legs of the Standard Chair, as the legs of various tables, in the cross-section of a table top, even as the handles of a sideboard.
I tend to be wary of attempts to draw parallels between our industry and branches of engineering or architecture, as these strike me as attempts to lend software development a spurious sense of discipline. Just calling it “software engineering” does not make writing a program as rigourous an activity as building a motorway flyover. However, with his commitment to iteration, re-use, modification and adaptation, and his championing of practice over theory it is hard not to regard Jean Prouvé as the Godfather of Extreme Programming.
The other exhibition at the Design Museum featured lots of modern work. One of the most striking exhibits was a chair “sketched” by a Japanese design house called FRONT. Their designers have developed a mechanism for designing furniture through motion capture and then rendering the designs using extruded plastic. Unlike Prouvé’s work you probably wouldn’t want to sit on the chair or rest a cup of coffee on the table but the process is fascinating to watch.
I finally arrived around 7pm last night, the plan then was to try and meet up with various people but as I’d been traveling for getting on 18 hours, I thought it best to get some sleep and I crashed around 9pm. As usual on the first day over the in USA I was up […]
They have changed the editor again and I am not able to insert a photo here
because a menu window is stuck at the botton of my screen and I cannot reach the insert button.
Wordpress is a drag!
I anybody knows a WAY TO MIGRATE BACK TO BLOGGER
it would be highly appreciated if you could let me […]
Today my blog has reached to the 100′000 hits landmark.
I am very happy that this has become such a successfull forum here.
Thanks for reading
I’ll keep on posting in the future!
© 2008 Blog Aggregator - Amardeep Sidhu | Entries (RSS) and Comments (RSS)
Powered by Wordpress, design by Web4 Sudoku, based on Pinkline by GPS Gazette