{"id":86,"date":"2010-12-31T15:46:30","date_gmt":"2010-12-31T13:46:30","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=86"},"modified":"2020-08-18T16:59:46","modified_gmt":"2020-08-18T14:59:46","slug":"dataguard-check-script-for-real-application-clusters-maa","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/dataguard-check-script-for-real-application-clusters-maa\/","title":{"rendered":"Dataguard check script for Real Application Clusters (MAA)"},"content":{"rendered":"

Two years after my posts:
\nQuick Oracle Dataguard check script<\/a> and More about Dataguard and how to check it<\/a> I faced a whole new Dataguard <\/strong> between two Oracle Real Application Clusters<\/strong>, aka Oracle Maximum Availability Architecture (MAA)<\/em>.<\/p>\n

This enviromnent is relying on Windows OS. Don’t know how this could be called “availability<\/em>” but here we are. I revisited my scripts in a quick and very dirty way. Please consider that I did copy and paste to check the alignment once per thread, but it should be improved with some kind of iteration to check each thread in a more structured fashion.<\/p>\n

\r\n#!D:\\oracle\\product\\10.2.0\\db_1\\perl\\5.8.3\\bin\\MSWin32-x86-multi-thread\\perl.exe -w\r\nuse DBI;\r\nuse DBD::Oracle qw(:ora_session_modes);\r\n# DB connection #\r\nmy $prod\u00a0 = \"prod\";\r\nmy $stby = \"stby\";\r\nmy $prodh;\r\nunless ($prodh = DBI->connect('dbi:Oracle:'.$prod, \r\n    'sys', 'strongpwd', \r\n    {PrintError=>0, AutoCommit => 0,\r\n    ora_session_mode => ORA_SYSDBA}))\u00a0 {\r\nprint \"Error connecting to DB: $DBI::errstr\\n\";\r\nexit(1);\r\n}\r\n$prodh->{RaiseError}=1;\r\n\r\nmy $stbyh;\r\nunless ($stbyh = DBI->connect('dbi:Oracle:'.$stby,\r\n    'sys', 'strongpwd',\r\n    {PrintError=>0, AutoCommit => 0,\r\n    ora_session_mode => ORA_SYSDBA}))\u00a0 {\r\nprint \"Error connecting to DB: $DBI::errstr\\n\";\r\n$prodh->disconnect;\r\nexit(1);\r\n}\r\n$stbyh->{RaiseError}=1;\r\n\r\nmy $sth;\r\n### query stdby MRP0\r\n$sth = $stbyh->prepare( <<EOSQL );\r\nselect thread#, SEQUENCE#, BLOCK#\r\n    from gv\\$managed_standby\u00a0\r\n    where process='MRP0'\r\nEOSQL\r\n$sth->execute();\r\nmy ($mrpthread, $mrpsequence, $mrpblock) = $sth->fetchrow_array();\r\n$sth->finish();\r\n\r\n### query stdby RFS\r\n$sth = $stbyh->prepare( <<EOSQL );\r\nselect thread#, SEQUENCE#, BLOCK#\r\n    from gv\\$managed_standby\u00a0\r\n    where process='RFS' and client_process='LGWR' order by thread#\r\nEOSQL\r\n$sth->execute();\r\nmy ($rfsthread1, $rfssequence1, $rfsblock1) = $sth->fetchrow_array();\r\nmy ($rfsthread2, $rfssequence2, $rfsblock2) = $sth->fetchrow_array();\r\n$sth->finish();\r\n\r\n### query prod\r\n$sth = $prodh->prepare( <<EOSQL );\r\nselect thread#, SEQUENCE#, BLOCK#\r\n    from gv\\$managed_standby\r\n    where process='LNS' order by thread#\r\nEOSQL\r\n$sth->execute();\r\nmy ($pthread1, $psequence1, $pblock1) = $sth->fetchrow_array();\r\nmy ($pthread2, $psequence2, $pblock2) = $sth->fetchrow_array();\r\n$sth->finish();\r\n\r\n\r\nprintf (\"ENVIRONM\u00a0 Thread Sequence\u00a0\u00a0 Block\\n\");\r\nprintf (\"--------- ------ ---------- ----------\\n\");\r\nprintf (\"PROD\u00a0\u00a0\u00a0\u00a0 LNS1\u00a0\u00a01 %10d %10d\\n\", $psequence1, $pblock1);\r\nprintf (\"STANDBY\u00a0 RFS1\u00a0\u00a01 %10d %10d\\n\", $rfssequence1, $rfsblock1);\r\nprintf (\"PROD\u00a0\u00a0\u00a0\u00a0 LSN2\u00a0\u00a02 %10d %10d\\n\", $psequence2, $pblock2);\r\nprintf (\"STANDBY\u00a0 RFS2\u00a0 2 %10d %10d\\n\", $rfssequence2, $rfsblock2);\r\nprintf (\"STANDBY\u00a0 MRP0\u00a0\u00a0%d %10d %10d\\n\", $mrpthread, $mrpsequence, $mrpblock);\r\n\r\nmy $psequence;\r\nmy $pblock;\r\nif ( $mrpthread == 1 ) {\r\n$psequence=$psequence1;\r\n$pblock=$pblock1;\r\n} else {\r\n$psequence=$psequence2;\r\n$pblock=$pblock2;\r\n}\r\n\r\n$sth = $stbyh->prepare( <<EOSQL );\r\nselect nvl(sum(blocks),0)\r\n+ $pblock - $mrpblock as BLOCK_GAP\r\nfrom gv\\$archived_log\r\nwhere thread#=$mrpthread and sequence#\r\nbetween $mrpsequence and $psequence\r\nEOSQL\r\n$sth->execute();\r\nmy ($mrpblockgap) = $sth->fetchrow_array();\r\n$sth->finish();\r\n\r\n$sth = $stbyh->prepare( <<EOSQL );\r\nselect nvl(sum(blocks),0)\r\n+ $pblock1 - $rfsblock1 as BLOCK_GAP\r\nfrom gv\\$archived_log\r\nwhere thread#=1 and sequence#\r\nbetween $rfssequence1 and $psequence1\r\nEOSQL\r\n$sth->execute();\r\nmy ($rfsblockgap1) = $sth->fetchrow_array();\r\n$sth->finish();\r\n\r\n$sth = $stbyh->prepare( <<EOSQL );\r\nselect nvl(sum(blocks),0)\r\n+ $pblock2 - $rfsblock2 as BLOCK_GAP\r\nfrom gv\\$archived_log\r\nwhere thread#=2 and sequence#\r\nbetween $rfssequence2 and $psequence2\r\nEOSQL\r\n$sth->execute();\r\nmy ($rfsblockgap2) = $sth->fetchrow_array();\r\n$sth->finish();\r\nprintf (\"\\n\\n%-10d blocks gap in TRANSMISSION\\n\", $rfsblockgap1+$rfsblockgap2);\r\nprintf (\"%-10d blocks gap in APPLY (MRP0)\\n\", $mrpblockgap);\r\n\r\n$stbyh->disconnect;\r\n$prodh->disconnect;\r\n<\/pre>\n
Please foreward me every improvement you implement over my code: it would be nice to post it here.<\/p>\n","protected":false},"excerpt":{"rendered":"
Two years after my posts: Quick Oracle Dataguard check script and More about Dataguard and how to check it I faced a whole new Dataguard between two Oracle Real Application Clusters, aka Oracle Maximum Availability Architecture (MAA). This enviromnent is … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[327,326,329,149,1],"tags":[19,36,9,22,29,37,38,23,33,31],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-oracle-maa","category-oracle","category-oracle-dg","category-oracle-rac","category-uncategorized","tag-cluster","tag-maximum-availability-architecture","tag-oracle","tag-oracle-database","tag-oracle-dataguard","tag-oracle-maa","tag-perl-script","tag-rac","tag-real-time-apply","tag-standby"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":9,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"predecessor-version":[{"id":95,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/86\/revisions\/95"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}