Home » Server Options » Data Guard » RMAN-06820 on rman backup from standby with OS authentication (Oracle 19c | Windows Server 2019)
|
|
| 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   |
John Watson
Messages: 8964
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Sun, 05 February 2023 09:33Quote: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 |
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 |
John Watson
Messages: 8964
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Thu, 09 February 2023 21:50Quote: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
...
..
.
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
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
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
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.
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 |
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 |
John Watson
Messages: 8964
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 20:47:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
