[Oracle] [DATAGUARD] About Redo_transport_user parameters

As you may already know, in the context of Oracle's Dataguard (in this case, the physical STANDBY), the primary will transmit the generated redo to the STANDBY, which is then applied by the STANDBY-side MRP process, To achieve the same period effect.

First, the meaning of the Redo_transport_user parameter is as follows:

http://docs.oracle.com/database/122/REFRN/REDO_TRANSPORT_USER.htm#REFRN10269

REDO_TRANSPORT_USERSpecifies the name of the user whose password verifier is used when a remote login password file was used for redo Transpor T authentication.

This user must has the SYSDBA or SYSOPER privilege ...

Translation comes to be, the standby end uses the password authentication to guarantee the redo transmission authentication, uses the designated Redo_transport_user to carry on the authentication.

The manual says that redo_transport_user users need to have SYSDBA or have SYSOPER permission.

But through the actual test, only SYSDBA is not possible, must have Sysopen permission to do.

The test uses a 12.2 dataguard environment.

For Redo_transport_user This parameter, you can also look at this MOS document

Troubleshooting ORA-16191 and ora-1017/ora-1031 in data guard Log Transport Services or Data guard Broker (Doc ID 1368170.1)

=============
6. If you had setup the ' Redo_transport_user '-initialization Parameter to a certain USER,
This User must is granted the ' Sysoper '-role and the Setting for this Parameter must is the same on the Primary and all Standby Databases.
=============

The simple test is as follows:

Test case
------------------
Primary:orcl
--create password file (12.2)
CD $ORACLE _home/dbs
Orapwd FILE=ORAPWORCL format=12.2 password=ora_1234 force=y

--create User
Create user Orasys identified by ora_1234;
Create user oraoper identified by ora_1234;
Grant CONNECT,SYSDBA to Orasys;
Grant Connect,sysoper to Oraoper;

--check If the user created can connect to database.
Sqlplus sys/"[email protected] as SYSDBA"
Sqlplus orasys/"[email protected] as SYSDBA"
Sqlplus oraoper/"[email protected] as sysoper"

--check v$pwfile_users
Col USERNAME format A7
Select Username,sysdba,sysoper from v$pwfile_users where USERNAME like ' ora% ';

Usernam Sysdb SYSOP
------- ----- -----
Orasys TRUE FALSE <<<<<orasys user does not has sysoper Role
Oraoper FALSE TRUE <<<<<oraoper user has sysoper Role


Create Dataguard


Standby:orclst
--check v$pwfile_users
Col USERNAME format A7
Select Username,sysdba,sysoper from v$pwfile_users where USERNAME like ' ora% ';

Usernam Sysdb SYSOP
------- ----- -----
Orasys TRUE FALSE
Oraoper FALSE TRUE

--check If the user created can connect to database
Sqlplus sys/"[email protected] as SYSDBA"
Sqlplus orasys/"[email protected] as SYSDBA"
Sqlplus oraoper/"[email protected] as sysoper"



--sys (default) REDO transportation
Primary:orcl
CREATE TABLE Scott.test (ID number);
INSERT into scott.test values (1);
Commit
Alter system archive log current;

Standby:orclst
Select COUNT (*) from scott.test;
= REDO Transportation was fine.


--sysdba[orasys] REDO Transportation
Primary:orcl
Alter system set Redo_transport_user=orasys Scope=spfile;
Shutdown immediate

Standby:orclst
alter system set Redo_transport_user=orasys;

Primary:orcl
Startup
INSERT into scott.test values (1);
Commit
Alter system archive log current;

Standby:orclst
Select COUNT (*) from scott.test;
--REDO transportation failed on ORA-16191
--You can see ORA-16191 from alert log of Primary.


--sysoper[oraoper] REDO Transportation
Primary:orcl
Alter system set Redo_transport_user=oraoper Scope=spfile;
Shutdown immediate

Standby:orclst
alter system set Redo_transport_user=oraoper;

Primary:orcl
Startup

Standby:orclst
Select COUNT (*) from scott.test;
= REDO Transportation was fine.

Primary:orcl
INSERT into scott.test values (1);
Commit
Alter system archive log current;


Standby:orclst
Select COUNT (*) from scott.test;
= REDO Transportation was fine.

[Oracle] [DATAGUARD] About Redo_transport_user parameters