Home » Server Options » Data Guard » RMAN-06820 on rman backup from standby with OS authentication (Oracle 19c | Windows Server 2019)
RMAN-06820 on rman backup from standby with OS authentication [message #686935] Sat, 04 February 2023 15:43 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi DBAs,

I am trying to backup my database from the Standby instance ( to avoid the read/write overhead on the Primary ).

I do "BACKUP DATABASE PLUS ARCHIVELOG"

When I do, I get the error "WARNING: failed to archive current log at primary database"

I go and dig up some information in different sources such as:

https://doyensys.com/blogs/rman-06820-warning-failed-to-archive/
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=573376425708183&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&i d=2723796.1&_afrWindowMode=0&_adf.ctrl-state=fuhuiyuil_4]https:// support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=573376425708183&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=272379 6.1&_afrWindowMode=0&_adf.ctrl-state=fuhuiyuil_4

I see that this is happening because of a bug in connecting to the primary instance and performing the current archive log switch,
when authenticated without a password. And that the solution suggested is to use the credentials for authentication during backup.

I have questions:

1. What options are there to allow a complete backup with no errors from standby, without plain-texting my SYS password ?
2. Why would it be an issue with a RMAN error, if it basically means that the backup is good to roll forward until latest point in time excluding current archive log file on primary ?
Or did I misunderstand something ?

Many thanks in advance,
Andrey

[Updated on: Sat, 04 February 2023 15:45]

Report message to a moderator

Re: RMAN-06820 on rman backup from standby with OS authentication [message #686936 is a reply to message #686935] Sun, 05 February 2023 01:33 Go to previous messageGo to next message
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I see that this is happening because of a bug in connecting to the primary instance and performing the current archive log switch,
It isn't a bug, it is a natural result of using OS authentication rather than password file autentication.

It doesn't matter: the backup will succeed. However, the datafile backup will not be recoverable because you will be missing the redo generated while the backup was in progress. So you need to be sure to log switch on the primary reasonably frequently (I set archive_lag_target=900) and backup the archive logfiles on the standby with similar frequency.
Re: RMAN-06820 on rman backup from standby with OS authentication [message #686968 is a reply to message #686936] Wed, 08 February 2023 15:25 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Sun, 05 February 2023 09:33
Quote:
I see that this is happening because of a bug in connecting to the primary instance and performing the current archive log switch,
It isn't a bug, it is a natural result of using OS authentication rather than password file autentication.

It doesn't matter: the backup will succeed. However, the datafile backup will not be recoverable because you will be missing the redo generated while the backup was in progress. So you need to be sure to log switch on the primary reasonably frequently (I set archive_lag_target=900) and backup the archive logfiles on the standby with similar frequency.
Thank you for the reply.

Why would we want to backup the archive logs every 15 minutes on standby, wouldn't it be enough to backup the archivelogs, say 20 minutes after the backup ( with the retention you specified ) just once ??

Also, what would be a sensible in this case to perform a full database backup from data guard without plain-texting credentials ?

Andrey
Re: RMAN-06820 on rman backup from standby with OS authentication [message #686970 is a reply to message #686936] Wed, 08 February 2023 15:32 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
In fact, isn't it true that this backup will allow recovery to any point in time except for the time interval, transactions of which are included in the single archive log that was current during the backup ?

Assuming log switches and archive transports are every 15 minutes, it means that the database is recoverable to any point in time for 1425 out of 1440 minutes of the daytime ?

Regards,
Andrey
Re: RMAN-06820 on rman backup from standby with OS authentication [message #686981 is a reply to message #686970] Thu, 09 February 2023 13:50 Go to previous messageGo to next message
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
Senior Member
Quote:
In fact, isn't it true that this backup will allow recovery to any point in time except for the time interval, transactions of which are included in the single archive log that was current during the backup ?
If you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
Re: RMAN-06820 on rman backup from standby with OS authentication [message #686992 is a reply to message #686981] Sat, 11 February 2023 11:31 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Thu, 09 February 2023 21:50
Quote:
In fact, isn't it true that this backup will allow recovery to any point in time except for the time interval, transactions of which are included in the single archive log that was current during the backup ?
If you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:

- Install Oracle 19c EE, no PDB, on Windows Server 2019 - hostname ORCL-PRIMARY
- ORACLE_HOME is "D:\oracle\product\19.0.0\dbhome_1\", recovery_area is E:\oracle\recovery_area
- Duplicate the VM to hostname ORCL-STANDBY
- Configured Archivelog, Flashback Database, Data Guard according to instructions in link https://oracle-base.com/articles/19c/data-guard-setup-using-broker-19c#read_only_active_data_guard
- Verified that the redo transport and apply work, Data Guard is working.

DGMGRL> show configuration;

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl      - Primary database
    orcl_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 53 seconds ago)

DGMGRL>

Then I logged in to RMAN on the standby instance (*without credentials*) and performed


C:\Users\Administrator>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Feb 11 15:57:23 2023
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1656235672, not open)

RMAN> spool log to 'f:\rman_backup_on_standby.log';
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'F:\ORACLE_BACKUP\FULL_%d_%u_%s_%T.bkp';
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL not backed up 1 times FORMAT 'F:\ORACLE_BACKUP\Archivelogs_%d_%u_%s_%T.bkp';
RMAN> BACKUP CURRENT CONTROLFILE FORMAT 'F:\ORACLE_BACKUP\CURRENT_CONTROLFILE%d_%u_%s_%T.bkp';
RMAN> BACKUP SPFILE FORMAT 'F:\ORACLE_BACKUP\SPFILE_%T.bkp' ;
RMAN>
RMAN> quit

So now I have the following in my backup folder:

C:\Users\Administrator>dir F:\ORACLE_BACKUP
 Volume in drive F is Backup
 Volume Serial Number is 70EC-AF5B

 Directory of F:\ORACLE_BACKUP

02/11/2023  04:54 PM    <DIR>          .
02/11/2023  04:54 PM    <DIR>          ..
02/11/2023  04:54 PM         2,403,840 ARCHIVELOGS_ORCL_3L1K81AD_117_20230211.BKP
02/11/2023  04:54 PM        11,304,960 CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1A_20230211.ORA
02/11/2023  04:54 PM        11,304,960 CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1B_20230211.ORA
02/11/2023  04:54 PM        11,304,960 CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1C_20230211.ORA
02/11/2023  04:54 PM        11,304,960 CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1D_20230211.ORA
02/11/2023  04:54 PM        11,272,192 CURRENT_CONTROLFILEORCL_3N1K81AG_119_20230211.BKP
02/11/2023  04:54 PM       297,754,624 FULL_ORCL_3J1K8196_115_20230211.BKP
02/11/2023  04:54 PM           114,688 SPFILE_20230211.BKP
               8 File(s)    356,765,184 bytes
               2 Dir(s)  10,341,388,288 bytes free

C:\Users\Administrator>

And indeed in the logfile I got the error:


RMAN> 
..
...
Starting backup at 11-FEB-23
RMAN-06820: warning: failed to archive current log at primary database
cannot connect to remote database
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 6 to 8; already backed up
skipping archived logs of thread 1 from sequence 1 to 2; already backed up
skipping archived logs of thread 1 from sequence 1 to 2; already backed up
...
..
.
So after lets say 30 minutes the log switches on primary. I imitated it with


SQL> alter system archive log current;

System altered.

SQL>



Then I backed up only the archivelogs on standby

RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL not backed up 1 times FORMAT 'F:\ORACLE_BACKUP\Archivelogs_%d_%u_%s_%T.bkp';

Starting backup at 11-FEB-23
using target database control file instead of recovery catalog
RMAN-06820: warning: failed to archive current log at primary database
cannot connect to remote database
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=625 device type=DISK
skipping archived logs of thread 1 from sequence 6 to 8; already backed up
skipping archived logs of thread 1 from sequence 1 to 2; already backed up
skipping archived logs of thread 1 from sequence 1 to 2; already backed up
skipping archived logs of thread 1 from sequence 1 to 4; already backed up
skipping archived logs of thread 1 from sequence 9 to 11; already backed up
skipping archived log of thread 1 with sequence 12; already backed up
skipping archived logs of thread 1 from sequence 13 to 39; already backed up
skipping archived log of thread 1 with sequence 1; already backed up
skipping archived log of thread 1 with sequence 1; already backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=40 RECID=48 STAMP=1128532021
channel ORA_DISK_1: starting piece 1 at 11-FEB-23
channel ORA_DISK_1: finished piece 1 at 11-FEB-23
piece handle=F:\ORACLE_BACKUP\ARCHIVELOGS_ORCL_3R1K8260_123_20230211.BKP tag=TAG20230211T170920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-FEB-23

Starting Control File and SPFILE Autobackup at 11-FEB-23
piece handle=F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1E_20230211.ORA comment=NONE
Finished Control File and SPFILE Autobackup at 11-FEB-23

RMAN>


Now, the moment of truth:

I Shut down Oracle on the both instances


Then did the following on the ORCL-STANDBY OS/Oracle:

- Removed file(s) D:\oracle\product\19.0.0\dbhome_1\database\SPFILEORCL.ORA, INITORCL.ORA
- Removed file(s) E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL, E:\ORACLE\RECOVERY_AREA\ORCL\CONTROL02.CTL
- Removed all the .DBF files in E:\oracle\oradata\ORCL
- Removed all the REDO LOG files in E:\oracle\oradata\ORCL



To recover I performed:

- create file D:\ORACLE\PRODUCT\19.0.0\DBHOME_1\DATABASE\INITORCL.ORA with single entry "*.db_name='orcl'" ( Just for enabling nomount )

SQL> startup nomount pfile='D:\ORACLE\PRODUCT\19.0.0\DBHOME_1\DATABASE\INITORCL.ORA';
ORACLE instance started.

Total System Global Area  310374472 bytes
Fixed Size                  9027656 bytes
Variable Size             243269632 bytes
Database Buffers           50331648 bytes
Redo Buffers                7745536 bytes
With RMAN

RMAN> set dbid=1656235672

executing command: SET DBID

RMAN> restore spfile from 'F:\ORACLE_BACKUP\SPFILE_20230211.BKP';

Starting restore at 11-FEB-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP F:\ORACLE_BACKUP\SPFILE_20230211.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-FEB-23
- shutdown, startup in nomount, verify the new SPFILE created and used

SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0201E+10 bytes
Fixed Size                 12577856 bytes
Variable Size            1577058304 bytes
Database Buffers         8589934592 bytes
Redo Buffers               20975616 bytes
SQL>
SQL> show parameter spf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\19.0.0\DBHOM
                                                 E_1\DATABASE\SPFILEORCL.ORA
SQL>


Restore the latest controlfile With RMAN

C:\Users\Administrator>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Feb 11 17:23:32 2023
Version 19.3.0.0.0

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from 'F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1E_20230211.ORA';

Starting restore at 11-FEB-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=621 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
output file name=E:\ORACLE\RECOVERY_AREA\ORCL\CONTROL02.CTL
Finished restore at 11-FEB-23

RMAN>


And then

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> catalog start with 'F:\ORACLE_BACKUP';

searching for all files that match the pattern F:\ORACLE_BACKUP

List of Files Unknown to the Database
=====================================
File Name: F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1E_20230211.ORA

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_C-1656235672-20230211-1E_20230211.ORA

RMAN> RESTORE DATABASE;

Starting restore at 11-FEB-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00004 to E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to E:\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece F:\ORACLE_BACKUP\FULL_ORCL_3J1K8196_115_20230211.BKP
channel ORA_DISK_1: piece handle=F:\ORACLE_BACKUP\FULL_ORCL_3J1K8196_115_20230211.BKP tag=TAG20230211T165357
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 11-FEB-23

RMAN> RECOVER DATABASE;

Starting recover at 11-FEB-23
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 37 is already on disk as file E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_37_KYHKWJPY_.ARC
archived log for thread 1 with sequence 38 is already on disk as file E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_38_KYHKWKGN_.ARC
archived log for thread 1 with sequence 39 is already on disk as file E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_39_KYHKWPC7_.ARC
archived log for thread 1 with sequence 40 is already on disk as file E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_40_KYHLSNYS_.ARC
archived log file name=E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_37_KYHKWJPY_.ARC thread=1 sequence=37
archived log file name=E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_38_KYHKWKGN_.ARC thread=1 sequence=38
archived log file name=E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_39_KYHKWPC7_.ARC thread=1 sequence=39
archived log file name=E:\ORACLE\RECOVERY_AREA\ORCL_STBY\ARCHIVELOG\2023_02_11\O1_MF_1_40_KYHLSNYS_.ARC thread=1 sequence=40
media recovery complete, elapsed time: 00:00:02
Finished recover at 11-FEB-23


Trying to open it with resetlogs I got the ORA-01666



C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 11 17:26:15 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database

So then as it was configured still as standby, I had to manually failover with:



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.
I was then able to open it as primary

SQL> alter database open;

Database altered.

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCL      READ WRITE           PRIMARY






Re: RMAN-06820 on rman backup from standby with OS authentication [message #686993 is a reply to message #686992] Sat, 11 February 2023 11:55 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Observations so far, and please correct me if I'm wrong:


- The ARCHIVELOGS_ORCL_3R1K8260_123_20230211.BKP was not used for the recovery. The archive logs that were transported from Primary have, and were enough to do the job.
- As long as the REDO TRANSPORT process works from primary to standby - the RMAN backup on STANDBY is good enough in spite of the "hole" in the REDO that is current on Primary during the backup



I did not check what happens if redo transportation stops and recovery is needed to the exact time right after the backup that lacks the current archive - I assume it will fail ? ( I may check on that next week )

But even if so, it means that basically the rman backup on Standby, in spite of the behavior ( I still think it's a bug, and also the MOS note calls it so ) ,
Is providing the ability to recover to almost any PIT, and a one time effort such as a archivelog additional backup 20 minutes after, would ensure recoverability to any PIT except for 20 minutes after the backup.

I would say it's a tradeoff between plain texting the credentials in a rman script file( which is awful ) and a risk of losing a few minutes of data recency in recovery, in very low percentage of the possible scenarios
( archive logs on primary not available + archive log was not transported + recovery needed to the exact 15 minute interval right after backup starts and before it switches and transports on primary .. )

What other options are for no-password access to perform backup from standby ?

CMU Active Directory authorization ( if that at all will work for rman backup..) ?
Kerberos authentication ?
Other solutions ?

Regards,
Andrey
Re: RMAN-06820 on rman backup from standby with OS authentication [message #686994 is a reply to message #686993] Mon, 13 February 2023 04:01 Go to previous messageGo to next message
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
Senior Member
What a superb demonstration. I don't want to sound patronizing, I really mean it when I say that is very nice to see such a good problem solving exercise: How to determine the truth by applying the Scientific Method.
Quote:
- The ARCHIVELOGS_ORCL_3R1K8260_123_20230211.BKP was not used for the recovery. The archive logs that were transported from Primary have, and were enough to do the job.
- As long as the REDO TRANSPORT process works from primary to standby - the RMAN backup on STANDBY is good enough in spite of the "hole" in the REDO that is current on Primary during the backup
With regard to these statements, I would say they are correct: as long as you have the necessary redo in the standby logfiles, it will succeed.

Remember that in normal running, archive logfiles are NEVER shipped. The redo is shipped as it is generated, the standby applies it and writes it to its standby logfiles. The standby logfiles are then archived by the standby whenever there is a log switch on the primary.
Re: RMAN-06820 on rman backup from standby with OS authentication [message #687312 is a reply to message #686994] Sun, 19 February 2023 04:21 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Thank you John.

Any ideas about the questions, which approaches can be helpful for allowing errorless backups ( from STANDBY ) without plain texting passwords ?
Should I maybe post this questions separately in the security forum ?

Regards,
Andrey
Previous Topic: Query-Only non-SYS user for Mounted instance
Next Topic: PROS and CONS of cross platform dataguard(Linux and windows)
Goto Forum:
  


Current Time: Sat Apr 13 03:09:41 CDT 2024