Restore Standby Database from Standby Backups

This post shows the steps for full restoring (controlfile + data files) for a standby database using standby database backups. The same could be achieved using restore from service (2283978.1). However, this method is useful when the database size is large and high network latencies are invovled.

1. As the first step disable log apply and transport.

DGMGRL> edit database fsfodr set state='apply-off';
Succeeded.
DGMGRL> edit database fsfopr set state='transport-off';
Succeeded.
DGMGRL>

2. Start the standby database in nomount mode and restore the standby controlfile.

$ rman target /
RMAN > startup nomount

RMAN> restore standby controlfile from '/opt/backup/fsfodr/full_c-1245564449-20230913-02.ctl';

Starting restore at 13-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2836 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/FSFODR/CONTROLFILE/current.341.1147435431
output file name=+FRA/FSFODR/CONTROLFILE/current.573.1147435431
Finished restore at 13-SEP-23

3. Mount the database.

RMAN> alter database mount;

4. Since the standby controfile was restored from a backup taken on standby database no need to catalog backup file location. The controlfile is aware of the backup locations. Run a restore and recover statements.

RMAN> run {
2> restore database;
3> recover database;
4> }

5. Clear the online logfiles on the standby database.

SQL> begin
for log_cur in ( select group# group_no from v$log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                 2635375 13-SEP-23      2635378 13-SEP-23          0
         2          1          0  104857600        512          2 YES UNUSED                 2635378 13-SEP-23      2636371 13-SEP-23          0
         5          1          0  104857600        512          2 YES UNUSED                 2636825 13-SEP-23   9.2954E+18                    0
         4          1          0  104857600        512          2 YES UNUSED                 2636470 13-SEP-23      2636825 13-SEP-23          0
         3          1          0  104857600        512          2 YES UNUSED                 2636371 13-SEP-23      2636470 13-SEP-23          0

6. Clear the standby logfiles

begin
for log_cur in ( select group# group_no from v$standby_log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/


PL/SQL procedure successfully completed.

SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME     CON_ID
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ --------- ----------
         6 UNASSIGNED                                        1          0  104857600        512          0 NO  UNASSIGNED                                                                                0
         7 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
         8 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
         9 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
        10 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0
        11 UNASSIGNED                                        1          0  104857600        512          0 YES UNASSIGNED                                                                                0

6 rows selected.

7. Enable log transport and redo apply

DGMGRL>  edit database fsfopr set state='transport-on';
Succeeded.
DGMGRL>  edit database fsfodr set state='apply-on';
Succeeded.

8. Check data guard configuration status and valdiate the standby database

DGMGRL> show configuration

Configuration - fsfo_dg

  Protection Mode: MaxAvailability
  Members:
  fsfopr - Primary database
    fsfodr - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 25 seconds ago)

DGMGRL> validate database fsfodr;

  Database Role:     Physical standby database
  Primary Database:  fsfopr

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    fsfopr:  YES
    fsfodr:  YES

Useful metalink notes
Creating a Physical Standby database using RMAN restore database from service [ID 2283978.1]