Tuesday, May 17, 2016

REDO_TRANSPORT_USER and Recovery Appliance (ZDLRA)

“REDO_TRANSPORT_USER” was an Oracle Database Parameter that was introduced in Oracle release 11.1 to help transporting redo from a primary to a standby by using a user designated for log transport , The default configuration assumes the user “SYS” is performing the transport.
This distinction is very important since the user “SYS” is available on every Oracle database and as such most data guard environment when created with default settings are created with “SYS” being the used for Log Transport services.
The Zero Data Loss Recovery Appliance (ZDLRA) adds an interesting twist to this configuration. In order for Real-TIme redo to work on a ZDLRA, the “REDO_TRANSPORT_USER” needs to be set to the Virtual Private Catalog (VPC) user of the ZDLRA. For database that are not participating in the Data Guard configuration , this is not an issue and a user does not be created on the Protected Database i.e the database being backed up to the ZDLRA. The important distinction comes into play if you already have a standby configured to receive redo, that process will break since we have switched the “REDO_TRANSPORT_USER” to a user that doesn’t exist on the protected database. In order to avoid this issue if you already have a Data Guard , you will need to create the VPC user as a user in the primary database with the "create session” and “sysoper" with an optional “sysdg” (12c) .
An example configuration is detailed below.
SQL> select * from v$pwfile_users;

SQL> select * from v$pwfile_users;

USERNAMESYSDBSYSOPSYSASSYSBASYSDGSYSKMCON_ID
SYSTRUETRUEFALSEFALSEFALSEFALSE 0
SYSDGFALSEFALSEFALSEFALSETRUEFALSE 0
SYSBACKUPFALSEFALSEFALSETRUEFALSEFALSE 0
SYSKMFALSEFALSEFALSEFALSEFALSETRUE 0

SQL> create user ravpc1 identified by ratest;
User created.

SQL> grant sysoper,create session to ravpc1;
Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAMESYSDBSYSOPSYSASSYSBASYSDGSYSKMCON_ID
SYSTRUETRUEFALSEFALSEFALSEFALSE 0
SYSDGFALSEFALSEFALSEFALSETRUEFALSE 0
SYSBACKUPFALSEFALSEFALSETRUEFALSEFALSE 0
SYSKMFALSEFALSEFALSEFALSEFALSETRUE 0
RAVPC1FALSETRUEFALSEFALSEFALSEFALSE 0

SQL> spool off


Once you have ensure that the password file has the entries , copy the password file to the standby node(s) and then ensure that the destination state on the primary to the standby is reset by deferring and then reenabling the destination state

SQL> alter system set log_archive_dest_state_X=defer scope=both sid='*'
SQL> alter system set log_archive_dest_state_X=enable scope=both sid='*'

This will ensure that you have redo transport working to the Data Guard standby and the ZDLRA


References

Data Guard Standby Database log shipping failing reporting ORA-01031 and Error 1017 when using Redo Transport User (Doc ID 1542132.1)
MAA White Paper - Deploying a Recovery Appliance in a Data Guard environment
REDO_TRANSPORT_USER Reference
Redo Transport Services
Real-Time Redo for Recovery Appliance

Google Search