Last week I needed to validate an issue with an archive backup on a standby database. And I run into a “problem” which seems to be a change of behavior starting from 11.2.0.4. which I wasn’t aware of.

First lets me show the error message before I explain the change in behavior. When I connect to the standby database and the rman catalog to execute a backup of the archive logs the following error is throng.

$ rman target / catalog rman@rmn


Recovery Manager: Release 11.2.0.4.0 - Production
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved


connected to target database: DBA1 (DBID=3148055959, not open)
recovery catalog database Password:
connected to recovery catalog database


RMAN> backup archivelog all;


Starting backup


RMAN-06820: WARNING: failed to archive current log at primary database

ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist


allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=324 instance=DBA13 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
skipping archived logs of thread 1 from sequence 28687 to 28690; already backed up
skipping archived logs of thread 2 from sequence 25989 to 25992; already backed up
Finished backup

I needed to validate what was causing this error. The ora-00942 was of course a site effect of the first error the ORA-17629. My first question what is going wrong? The easiest way to check this is to enable rman tracing and see what is going on.


$ rman target / catalog rman@rmn debug log=/tmp/check.log

Looking in the created logfile I search for the ORA-17629 and see the connect string used.


DBGSQL: TARGET> declare null_retVal varchar2(1); begin null_retVal := sys.dbms_backup_restore.remoteSQLExecute( source_dbuname=> :primary_dbuname, source_cs => :lprimary_db_cs, stmt => 'alter system archive log current'); end;
DBGSQL: sqlcode = 17629
DBGSQL: B :primary_dbuname = DBA1_02
DBGSQL: B :lprimary_db_cs = (DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=prod01-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBA1_02)))
DBGSQL: error: ORA-17629: Cannot connect to the remote database server (krmkosqlerr)
DBGSQL: ORA-17627: ORA-00942: table or view does not exist (krmkosqlerr)
DBGSQL: ORA-06512: a

But this is a connection to the primary database. Interesting! Why is a connection to the primary database required to back up my archives on the standby database?

Is there something wrong with the connection? Using the connect string from the logfile I am able to connect. Below the proof:

$ sqlplus myuser@'(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=prod01-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBA1_02)))'

SQL*Plus: Release 11.2.0.4.0 Production
Copyright (c) 1982, 2013, Oracle. All rights reserved.


Enter password:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> exit
Ok, so when I try to connect it is working, but which user/password is Oracle using? Within RMAN and also data guard environment the sys user and sysdba is required in 11g starting from 12.1.0.1. also the sysbackup and/or sysdg is introduce. But the fact I use rman in 11.2.0.4 I need sysdba.So I use OS authentication when connecting to the target (my standby). How is the remote session able to use the password belonging to the sys user? Would it read the password file? Checking the created logfile I don’t see any call to a password file. Instead of using rman target / lets change this to see what will happen when the sys user is provided.


$ rman target sys@'(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=prod01-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SRVDBA)))(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=prod02-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBA1_03))))' catalog rman@srvirmn1rmn


Recovery Manager: Release 11.2.0.4.0 - Production


Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


target database Password:
connected to target database: DBA1 (DBID=3148055959)
recovery catalog database Password:
connected to recovery catalog database

RMAN> backup archivelog all;
Starting backup
current log archived ==== Look here a current log is archived from the primary database!


allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=247 instance=DBA12 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
skipping archived log of thread 1 with sequence 28688; already backed up
skipping archived log of thread 2 with sequence 25990; already backed up
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=25991 RECID=33523 STAMP=895915044
input archived log thread=1 sequence=28689 RECID=33525 STAMP=895915045


…..


Finished Control File and SPFILE Autobackup


RMAN> exit

Conclusion: In a data guard environment backup of archivelog files will result in backup of the current standby redo logfile, which in that case will result in an required online redo log switch on the primary before it can backup the current standby redo logfile.

Due to a change of behavior sqlnet (passwordfile) authentication is required to have a successful redo log switch at the primary site. So starting from 11.2.0.4 start changing rman target / into rman target sys@<your tnsnames>.

 

Checking MOS I found doc id: 1616074.1, with bug 8740124 which confirms the above change of behaviour.