Fixing a standby gap has been possible since Oracle 10g using incremental backup, but it had to be done completely manually. This process required determining the SCN number, taking an incremental backup from the primary database, transferring the backup files to the standby server, and so on.
Starting from Oracle 12cR1, some improvements were introduced, and part of this process became automated. However, it still required going through several manual steps:
SQL>startup force mount
RMAN> RECOVER DATABASE FROM SERVICE prim NOREDO ;
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE prim;
SQL>startup force;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
In Oracle 18c, further improvements were made. Now, with just one command, all the above steps are performed automatically:
RMAN> RECOVER STANDBY DATABASE FROM SERVICE tns_fal_server;
Below, we will simulate this scenario.
Step 1: Checking the current status of standby vs. primary
On primary (prim):
SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
————– ———-
128 1
On standby (stb):
SQL> select max(sequence#),thread#,applied from gv$archived_log group by thread#,applied order by thread#;
MAX(SEQUENCE#) THREAD# APPLIED
————– ———- ———
128 1 YES
As shown, the standby is in sync with the primary.
Step 2: Simulating a gap
We shut down the standby:
SQL> shutdown abort
ORACLE instance shut down.
Then, on the primary, we generate and immediately delete an archived log:
alter system switch logfile;
alter system switch logfile;
[oracle@hkm6 ~]$ rm -rf /18c/arch/1_129_972120046.dbf
When the standby is started again, it will wait for archive log 129, which was deleted:
SQL> startup
Database opened.
SQL> alter database recover managed standby database;
PR00 (PID:23943): Media Recovery Waiting for T-1.S-129
PR00 (PID:23943): Fetching gap from T-1.S-129 to T-1.S-129
At this stage, the standby is intentionally placed in a gap situation.
Step 3: Resolving the gap with Oracle 18c
- Cancel managed recovery on standby:
SQL> alter database recover managed standby database cancel;
Database altered.
2. Run the new Oracle 18c command in RMAN:
RMAN> recover standby database from service prim;
Starting recover at 26-APR-18
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 4982831184 bytes
Fixed Size 8906832 bytes
Variable Size 1174405120 bytes
Database Buffers 3791650816 bytes
Redo Buffers 7868416 bytes
contents of Memory Script:
{
restore standby controlfile from service ‘prim’;
alter database mount standby database;
}
executing Memory Script
Starting restore at 26-APR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=743 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/18c/base/oradata/USEFDB18/controlfile/control01.ctl
Finished restore at 26-APR-18
released channel: ORA_DISK_1
Statement processed
contents of Memory Script:
{
recover database from service ‘prim’;
}
executing Memory Script
Starting recover at 26-APR-18
Starting implicit crosscheck backup at 26-APR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=743 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 26-APR-18
Starting implicit crosscheck copy at 26-APR-18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 26-APR-18
searching for all files in the recovery area
cataloging files…
no files cataloged
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 1506388
skipping datafile 6; already restored to SCN 1506388
skipping datafile 8; already restored to SCN 1506388
skipping datafile 57; already restored to SCN 6799373
skipping datafile 58; already restored to SCN 6799373
skipping datafile 59; already restored to SCN 6799373
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
destination for restore of datafile 00001: /18c/base/oradata/USEFDB18/datafile/o1_mf_system_fcvjfc9s_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
destination for restore of datafile 00003: /18c/base/oradata/USEFDB18/datafile/o1_mf_sysaux_fcvjh2k2_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
destination for restore of datafile 00004: /18c/base/oradata/USEFDB18/datafile/o1_mf_undotbs1_fcvjhvp9_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
destination for restore of datafile 00007: /18c/base/oradata/USEFDB18/datafile/o1_mf_users_fcvjhwty_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-APR-18
Finished recover at 26-APR-18
RMAN>
3. Restart managed recovery:
SQL> alter database recover managed standby database;
PR00 (PID:25374): Media Recovery Waiting for T-1.S-131 (in transit)
Step 4: Verifying the result
Now, the standby has applied archive log 130, proving the gap is resolved:
SQL> select max(sequence#),thread#,applied from gv$archived_log where RESETLOGS_ID=972120046 group by thread#,applied order by thread#;
MAX(SEQUENCE#) THREAD# APPLIED
————– ———- ———
130 1 YES
Top comments (0)