In a DataGuard environment, by default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. But for security reasons you might not want to use such a high privileged user only for the redo transmission. To overcome this issue, Oracle has implemented the REDO_TRANSPORT_USER initialization parameter.<\/p>\n
The REDO_TRANSPORT_USER specifies the name of the user whose password verifier is used when a remote login password file is used for redo transport authentication.<\/p>\n
But take care, the password must be the same at both databases to create a redo transport session, and the value of this parameter is case sensitive and must exactly match the value of the USERNAME column in the V$PWFILE_USERS view.<\/p>\n
Besides that, this user must have the SYSDBA or SYSOPER privilege. However, we don’t want to grant the SYSDBA privilege. For administrative ease, Oracle recommends that the REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination.<\/p>\n
Ok. Let’s give it a try. I am creating an user called ‘DBIDG’ which will be used for redo transmission between my primary and standby.<\/p>\n
SQL> create user DBIDG identified by manager;\n\nUser created.\n\nSQL> grant connect to DBIDG;\n\nGrant succeeded.\n\nSQL> grant sysoper to DBIDG;\n\nGrant succeeded.<\/pre>\nOnce done, I check the v$pwfile_users to see if my new user ‘DBIDG’ exist.<\/p>\n
-- On Primary\n\nSQL> col username format a22\nSQL> select USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM from V$PWFILE_USERS\n 2 where USERNAME = 'DBIDG';\n\nUSERNAME SYSDB SYSOP SYSBA SYSDG SYSKM\n---------------------- ----- ----- ----- ----- -----\nDBIDG FALSE TRUE FALSE FALSE FALSE\n\n\n-- On Standby\nSQL> col username format a22\nSQL> select USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM from V$PWFILE_USERS\n 2 where USERNAME = 'DBIDG';\n\nno rows selected<\/pre>\nOk. Like in previous versions of Oracle, I have to copy the password myself to the destination host to make it work.<\/p>\n
oracle@dbidg01:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] scp -p orapwDBIT122 oracle@dbidg02:$PWD\n\nSQL> select USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM from V$PWFILE_USERS\n 2 where USERNAME = 'DBIDG';\n\nUSERNAME SYSDB SYSOP SYSBA SYSDG SYSKM\n---------------------- ----- ----- ----- ----- -----\nDBIDG FALSE TRUE FALSE FALSE FALSE<\/pre>\n<\/p>\n
By connecting with the ‘DBIDG’ user, you almost can’t do anything. Not even selecting from the dba_tablespaces view e.g. From the security perspective, this user is much less of a concern.<\/p>\n
oracle@dbidg01:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] sqlplus dbidg\/Manager1@DBIT122_SITE1 as sysoper\n\nSQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 13 11:08:00 2016\n\nCopyright (c) 1982, 2016, Oracle. All rights reserved.\n\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL> desc dba_tablespaces\nERROR:\nORA-04043: object \"SYS\".\"DBA_TABLESPACES\" does not exist<\/pre>\nNevertheless, the ‘DBIDG’ user is completely sufficient for my use case. Now, as I got my ‘DBIDG’ redo transport user in both password files (primary and standby), I can activate the redo_transport_user feature on (primary and standby) and check if everything works, by doing a switch over and switch back.<\/p>\n
-- On Primary and Standby\n\nSQL> alter system set redo_transport_user='DBIDG';\n\nSystem altered.\n\n\nDGMGRL> show configuration;\n\nConfiguration - DBIT122\n\n Protection Mode: MaxAvailability\n Members:\n DBIT122_SITE1 - Primary database\n DBIT122_SITE2 - Physical standby database\n\nFast-Start Failover: DISABLED\n\nConfiguration Status:\nSUCCESS (status updated 33 seconds ago)\n\nDGMGRL> SWITCHOVER TO 'DBIT122_SITE2' WAIT 5;\nStopping services and waiting up to 5 seconds for sessions to drain...\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"DBIT122_SITE2\"\nConnecting ...\nConnected to \"DBIT122_SITE2\"\nConnected as SYSDBA.\nNew primary database \"DBIT122_SITE2\" is opening...\nOperation requires start up of instance \"DBIT122\" on database \"DBIT122_SITE1\"\nStarting instance \"DBIT122\"...\nORACLE instance started.\nDatabase mounted.\nConnected to \"DBIT122_SITE1\"\nSwitchover succeeded, new primary is \"DBIT122_SITE2\"\n\nDGMGRL> show configuration;\n\nConfiguration - DBIT122\n\n Protection Mode: MaxAvailability\n Members:\n DBIT122_SITE2 - Primary database\n DBIT122_SITE1 - Physical standby database\n\nFast-Start Failover: DISABLED\n\nConfiguration Status:\nSUCCESS (status updated 71 seconds ago)\n\n\nDGMGRL> SWITCHOVER TO 'DBIT122_SITE1' WAIT 5;\nStopping services and waiting up to 5 seconds for sessions to drain...\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"DBIT122_SITE1\"\nConnecting ...\nConnected to \"DBIT122_SITE1\"\nConnected as SYSDBA.\nNew primary database \"DBIT122_SITE1\" is opening...\nOperation requires start up of instance \"DBIT122\" on database \"DBIT122_SITE2\"\nStarting instance \"DBIT122\"...\nORACLE instance started.\nDatabase mounted.\nConnected to \"DBIT122_SITE2\"\nSwitchover succeeded, new primary is \"DBIT122_SITE1\"<\/pre>\nLooks very good so far. But what happens if I have to change the password of the ‘DBIDG’ user?<\/p>\n
-- On Primary\n\nSQL> alter user dbidg identified by Manager1;\n\nUser altered.\n\n-- On Primary\noracle@dbidg01:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] ls -l orapwDBIT122\n-rw-r----- 1 oracle oinstall 4096 Dec 13 10:30 orapwDBIT122\n\noracle@dbidg01:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] md5sum orapwDBIT122\n3b7b2787943a07641b8af9f9e5284389 orapwDBIT122\n\n\n-- On Standby\noracle@dbidg02:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] ls -l orapwDBIT122\n-rw-r----- 1 oracle oinstall 4096 Dec 13 10:30 orapwDBIT122\n\noracle@dbidg02:\/u01\/app\/oracle\/admin\/DBIT122\/pfile\/ [DBIT122] md5sum orapwDBIT122\n3b7b2787943a07641b8af9f9e5284389 orapwDBIT122<\/pre>\nThat’s cool. Passwords on both sites have been updated successfully. They have the same time stamps and even the MD5 checksums are exactly the same. This is because of the new “Automatic Password Propagation to Standby” feature of 12cR2.<\/p>\n
Conclusion<\/h3>\n
REDO_TRANSPORT_USER and “Automatic Password Propagation to Standby” are nice little features from Oracle. \u00a0The\u00a0REDO_TRANSPORT_USER exists for quite a while now, at least since 11gR2, however, the\u00a0“Automatic Password Propagation to Standby” is new with 12cR2.<\/p>\n","protected":false},"excerpt":{"rendered":"
By William Sescu In a DataGuard environment, by default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. But for security reasons you might not want to use such a high privileged user only for the redo transmission. To overcome this issue, Oracle has implemented […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[999,988,209],"type_dbi":[],"class_list":["post-9545","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-dataguard","tag-oracle-12-2","tag-oracle-12c"],"acf":[],"yoast_head":"\n
Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER\" \/>\n<meta property=\"og:description\" content=\"By William Sescu In a DataGuard environment, by default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. But for security reasons you might not want to use such a high privileged user only for the redo transmission. To overcome this issue, Oracle has implemented […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-14T06:18:35+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12cR2 – DataGuard and the REDO_TRANSPORT_USER\",\"datePublished\":\"2016-12-14T06:18:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\"},\"wordCount\":440,\"commentCount\":0,\"keywords\":[\"DataGuard\",\"Oracle 12.2\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\",\"name\":\"Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-12-14T06:18:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12cR2 – DataGuard and the REDO_TRANSPORT_USER\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER - dbi Blog","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:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER","og_description":"By William Sescu In a DataGuard environment, by default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. But for security reasons you might not want to use such a high privileged user only for the redo transmission. To overcome this issue, Oracle has implemented […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/","og_site_name":"dbi Blog","article_published_time":"2016-12-14T06:18:35+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12cR2 – DataGuard and the REDO_TRANSPORT_USER","datePublished":"2016-12-14T06:18:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/"},"wordCount":440,"commentCount":0,"keywords":["DataGuard","Oracle 12.2","Oracle 12c"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/","name":"Oracle 12cR2 - DataGuard and the REDO_TRANSPORT_USER - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-12-14T06:18:35+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-dataguard-and-the-redo_transport_user\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12cR2 – DataGuard and the REDO_TRANSPORT_USER"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9545"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9545\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9545"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}