This post is dedicated to Pieter Van Puymbroeck.
When using a physical standby database, you can use RMAN to backup the standby database instead of the primary. You can backup both primary and standby of course, but you don’t have to (see below). The fact that the primary and standby are completely identical (at the files binary level), means you can backup the standby database and if needed, restore the datafiles to the primary. You can, of course, restore the entire database as well without a problem (you’ll just have to “activate” it after the restore as it will be a standby database).
I actually do this for one of my clients. We have a standby database, we backup it instead of the primary, and we have a script that restores the database and activates it. We use this quite often for restore tests and it works perfectly.
In some cases, however, the primary and standby databases are very remote. This means that in case of a crash, you’ll have to copy the backup files over the network which might be slow. In this case, some people prefer to backup both sides (primary and standby) and restore locally if needed.
So far so good. But there is one thing that not all people know. When you backup the database, RMAN performs a logfile switch in order to backup the active logfile. When you backup the primary, that’s not a problem, but when you backup the standby, you can’t perform a log switch, this is something that must be performed on the primary. RMAN knows that, and it will perform the log switch on the primary, but for that it needs to connect to the primary, and that’s the catch.
Most of us using RMAN and connect to the database using “connect target /”, as we connect locally with the oracle OS user. This is fine when backing up the primary, but for the standby, in order to perform the log switch on the primary, RMAN has to have the password, so you have to connect with “connect target sys/<pass>” when backing up the standby. Using this password, RMAN will connect to the primary to perform log switch, and will wait until this log is applied to the standby before continuing with the backup.
This is very important. If you don’t connect with the password, RMAN won’t be able to perform a log switch on the primary, and you might not be able to restore your standby database from backup.
This is how it looks when you DO NOT provide the password:
[oracle@ora19sec ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 21 17:20:24 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST19 (DBID=4106941138, not open)
RMAN> backup incremental level 0 database plus archivelog delete input;
Starting backup at 21-JUL-21
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=418 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=39 STAMP=1078053213
input archived log thread=1 sequence=11 RECID=42 STAMP=1078054662
This is how it looks when you DO provide the password:
[oracle@ora19sec ~]$ rman target sys/syspwd
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 21 17:22:11 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST19 (DBID=4106941138, not open)
RMAN> backup incremental level 0 database plus archivelog delete input;
Starting backup at 21-JUL-21
using target database control file instead of recovery catalog
current log archived at primary database
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=412 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=44 STAMP=1078507344
Nice. I wonder if this would be a great time to use a Secure External Password Store wallet and set up a TNS connection that would have the password in it so that you don’t have to provide the password on the command line. In theory, you could then just do rman target /@standby-with-seps.
Wonderful article ,thanks
Thank you for sharing Liron but there are certain restrictions which I encountered when trying to backup a standby database which uses bigfile tablespaces, you cannot exclude tablespaces. This feature works perfectly well on the primary where you can exclude all your non-bigfile tablespaces , then backup in section sizes all your bigfile tablespaces
Ravin,
I was not aware of this. I actually do backup standby databases with bigfile tablespaces, but never tried to exclude any.
Thanks for the comment.
Liron