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 06. 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]
