{"id":478,"date":"2008-05-19T17:03:20","date_gmt":"2008-05-19T21:03:20","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/"},"modified":"2008-05-22T02:46:03","modified_gmt":"2008-05-22T06:46:03","slug":"configugre-oracle-data-guard-part-ii","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/","title":{"rendered":"Configure Oracle Data Guard Part II"},"content":{"rendered":"

This is in continuation of Oracle Dataguard configuration part I<\/a>\u00a0 which\u00a0I posted long back and forgot to update next one.<\/p>\n

\u00a0\u00a0 One of my friend was looking for steps so I thought of finishing this (If you think I missed any post just leave comment and I’ll try to post on that topic)<\/p>\n

Ensure that you have gone through Part I and finished following task
\n1. Primary Database is configured in archivelog mode
\n2. Configure LOG_ARCHIVE_DEST_2 on primary database
\n3. Create TNS alias (PROD_remote1)\u00a0in tnsnames.ora\u00a0on primary site
\n4. Install Oracle Database on Standby Site (Use same directory structure as on primary site)
\n* Dataguard allows you to configure primary & standby database with different directory structure but for simplicity this doc assume they are with same directory structure<\/font><\/p>\n

Here are step by step Oracle dataguard cnfiguration\u00a0in detail\u00a0once again \u00a0<\/p>\n

1.\u00a0Make sure your production database is in force logging.<\/strong><\/p>\n

\u00a0Query from v$database\u00a0 if not set change to force logging by running
\nsqlplus> alter database force logging;
\n<\/strong><\/p>\n

2.\u00a0Set Primary Database in archivelog \u00a0mode
\n<\/strong>If log mode is not “Archive Mode”, and automatic archival is not “Enabled”, you must set these parameters in your init<SID>.ora file:
\nlog_archive_start = TRUE
\nlog_archive_format = <format for database, using %s, %t>
\nlog_archive_dest_1 = ‘LOCATION=<primary DB archive log dest> MANDATORY’
\nlog_archive_dest_state_1 = enable<\/strong><\/p>\n

For these parameters to take effect, you must shut down your database, then re-start it in this manner:<\/p>\n

sqlplus>\u00a0 startup mount
\n<\/strong>sqlplus> alter database archivelog
\n<\/strong>sqlplus> alter database open<\/strong><\/p>\n

3.\u00a0Create standby redo logs
\n<\/strong>Query v$logfile ( If there is no standby redo log,\u00a0create equal number & of same size as of online redo logs )
\nsqlplus> alter database add standby logfile group <> (\u2018<full path for first member>\u2019,\u2019<full path for second member\u2019) size <> ;<\/strong> –
\n<\/strong>4.\u00a0Define log_archive_dest_state_2 in primary database
\nopen init<SID>.ora\u00a0 in primary server & add\u00a0remote_archive_enable = true
\n—log_archive_dest_2 = ‘SERVICE=<standby_service_name> OPTIONAL REOPEN=60 LGWR AFFIRM SYNC’<\/strong>
\n—log_archive_dest_state_2 = enable<\/strong><\/strong>If log_archive_dest is specified in your init.ora file, you must comment out or remove that parameter.<\/strong>5.\u00a0Add entries to your tnsnames.ora and listener.ora files<\/strong> for the standby database.
\nThe tnsnames.ora file is found in $TNS_ADMIN<\/strong><standby_service_name> = (DESCRIPTION=
\n\u00a0\u00a0 (ADDRESS=(PROTOCOL=tcp)
\n\u00a0\u00a0 (PORT=<same port as production>)
\n\u00a0\u00a0 (HOST=<standby host name>))
\n\u00a0\u00a0 (CONNECT_DATA=(SID=<same SID as production>)))<\/strong>The listener.ora file is also found in $TNS_ADMIN
\n\u00a0\u00a0
\n<standby_service_name> =
\n\u00a0\u00a0 (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
\n\u00a0\u00a0 (PORT=<same port as production>)
\n\u00a0\u00a0 (HOST=<standby host name>)))
\n\u00a0\u00a0 SID_LIST_<standby_service_name>=(SID_LIST=
\n\u00a0\u00a0 (SID_DESC=(SID_NAME=<same SID as production>)
\n\u00a0\u00a0 (ORACLE_HOME=<path to database home>)))<\/p>\n

<\/strong>Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files.\u00a0
\n<\/font>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n6.\u00a0Create the secondary database standby control file.
\n<\/strong>From SQL*Plus as sys or system, on the primary site\u00a0environment, create a standby control file:<\/p>\n

SQL> alter system archive log current;
\n<\/strong>SQL> alter database create standby controlfile as ‘<directory for control files>\/stdbyctl.dbf’<\/strong><\/p>\n

Note the standby control file must have a name different from the primary database control\u00a0 file.
\n<\/font><\/p>\n

7.\u00a0Restore the primary database to the database server at the secondary site
\n<\/strong>This step assumes that you have installed database on standby site with same directory structure, you can delete datafiles from database which is going to become standby site (or replace them from primary site as given below)<\/font>. \u00a0<\/p>\n

7.1 Shutdown your primary database
\n7.2 <\/strong>Copy your datafiles & online redologs including standby redologs<\/p>\n

–
\n8.\u00a0Copy the standby control file to the redundant control file directories.<\/strong><\/p>\n

On the standby database server, make redundant copies of the standby control file to the appropriate directories ( standby control file which you created in step 6 above ).\u00a0 Put them in the same directories your redundant production control files are kept.\u00a0 Log in as the database owner and use operating system utilities to make the copies.<\/p>\n

9.\u00a0Adjust the init<SID>.ora file for the standby database.<\/strong><\/p>\n

On the standby database server, edit your init<SID>.ora file to point to the standby control files by changing the CONTROL_FILES entry and set up your standby archive destination:
\nCONTROL_FILES = <directory 1 for control files>\/stdbyctl.dbf, <directory 2 for control files>\/stdbyctl.dbf, etc.
\nlog_archive_start\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = true\u00a0\u00a0\u00a0\u00a0\u00a0 # if you want automatic archiving
\nFAL_SERVER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <PRIMARY_SID>
\nFAL_CLIENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0 <TNS_ALIAS_STANDBY>\u00a0 (Defined above)
\nSTANDBY_ARCHIVE_DEST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/prodarch\/oracle\/prodarch
\nSTANDBY_FILE_MANAGEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = AUTO<\/p>\n

10.<\/strong>\u00a0Add entries to your tnsnames.ora and listener.ora files for the standby database on standby database.<\/strong><\/p>\n

<standby_service_name> = (DESCRIPTION=
\n\u00a0\u00a0 (ADDRESS=(PROTOCOL=tcp)
\n\u00a0\u00a0 (PORT=<same port as production>)
\n\u00a0\u00a0 (HOST=<standby host name>))
\n\u00a0\u00a0 (CONNECT_DATA=(SID=<same SID as production>)))<\/p>\n

The listener.ora file is also found in $TNS_ADMIN
\n<standby_service_name> =
\n\u00a0\u00a0 (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
\n\u00a0\u00a0 (PORT=<same port as production>)
\n\u00a0\u00a0 (HOST=<standby host name>)))
\nSID_LIST_<standby_service_name>=(SID_LIST=
\n\u00a0\u00a0 (SID_DESC=(SID_NAME=<same SID as production>)
\n\u00a0\u00a0 (ORACLE_HOME=<path to database home>)))<\/p>\n

Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files.<\/p>\n

11.\u00a0Start the listener on the secondary database server, for the standby database.
\n<\/strong>On the standby server, as the database owner, run the following operating system command :<\/p>\n

$ lsnrctl start <standby_service_name<\/strong><\/p>\n

–<\/strong>
\n12.\u00a0Mount the standby database
\n<\/strong>On the standby database server, from sqlplus
\nSQLPLUS> startup nomount <\/strong>
\nSQLPLUS > alter database mount standby database;<\/strong><\/p>\n

–<\/strong>
\n13. Place the standby database in managed recovery mode.<\/strong><\/p>\n

SQLPLUS> alter database recover managed standby database disconnect;<\/strong><\/p>\n

–<\/strong><\/p>\n

14. Modify the standby database listener.ora file<\/strong>\u00a0 ( If this parameter is not set )<\/p>\n

Change the value of the HOST<\/strong> parameter in the production service entry of the listener.ora file on the standby database server to point to the secondary database server.<\/p>\n

<production_service_name> =
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
\n\u00a0\u00a0 \u00a0(PORT=<same port as production>)
\n\u00a0\u00a0 \u00a0(HOST=<standby host name><\/strong>)))<\/p>\n

SID_LIST_<production_service_name>=(SID_LIST=
\n\u00a0\u00a0\u00a0 (SID_DESC=(SID_NAME=<same SID as production>)
\n\u00a0\u00a0 \u00a0(ORACLE_HOME=<path to database home>)))<\/p>\n

–
\n15. Check your protection mode .<\/strong><\/p>\n

Check from v$database ( on standby database )
\nIf want to change to maximum availability mode by running<\/p>\n

Sqlplus > alter database set standby database to maximize { PROTECTION | AVAILABILITY | PERFORMANCE }<\/strong><\/p>\n

Once you have set it to maximum availability\u00a0 mode , you can start your primary database .<\/p>\n

Check your alert<SID>.log for any errors
\n<\/strong><\/p>\n

–<\/strong><\/p>\n

Related<\/u><\/p>\n

10g R2 Dataguard Setup Doc from Oracle<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

This is in continuation of Oracle Dataguard configuration part I\u00a0 which\u00a0I posted long back and forgot to update next one. […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[424],"tags":[],"class_list":["post-478","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nConfigure Oracle Data Guard Part II -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Configure Oracle Data Guard Part II -\" \/>\n<meta property=\"og:description\" content=\"This is in continuation of Oracle Dataguard configuration part I\u00a0 which\u00a0I posted long back and forgot to update next one. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/\" \/>\n<meta property=\"article:published_time\" content=\"2008-05-19T21:03:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2008-05-22T06:46:03+00:00\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/\",\"name\":\"Configure Oracle Data Guard Part II -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"datePublished\":\"2008-05-19T21:03:20+00:00\",\"dateModified\":\"2008-05-22T06:46:03+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Configure Oracle Data Guard Part II\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Configure Oracle Data Guard Part II -","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/","og_locale":"en_US","og_type":"article","og_title":"Configure Oracle Data Guard Part II -","og_description":"This is in continuation of Oracle Dataguard configuration part I\u00a0 which\u00a0I posted long back and forgot to update next one. […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/","article_published_time":"2008-05-19T21:03:20+00:00","article_modified_time":"2008-05-22T06:46:03+00:00","author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/","url":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/","name":"Configure Oracle Data Guard Part II -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"datePublished":"2008-05-19T21:03:20+00:00","dateModified":"2008-05-22T06:46:03+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/05\/19\/configugre-oracle-data-guard-part-ii\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"Configure Oracle Data Guard Part II"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/478","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=478"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/478\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}