This is the 26th day of my participation in the August Text Challenge.More challenges in August

preface

  • The name DG GAP means: the DG is out of sync. A GAP occurs when the standby database cannot receive archive log files from one or more primary databases.

So, if encounter GAP how to repair it? Let me tell you more

Introduce a,

  • DG GAP is mainly divided into two categories:
  • The primary Archive Log exists, and the Fetch Archive Log(FAL) parameter can be configured to resolve the archiving GAP automatically.
  • Primary archive logs are missing and need human intervention to fix:
  • The GAP fix varies from Oracle to Oracle:
  • 11G processing steps:

A. Create a control file for the standby database on the active database. B. Perform incremental backup on the active database using the current SCN number of the standby database as the starting point. Copy the incremental backup to the standby database d. Use the new control file to start the standby database to the mount state e. Register the incremental backup to RMAN’s catalog, cancel the backup recovery application, and restore the incremental backup F. Example Start the recovery process of the standby database

  • New 12C features (RECOVER… FROM the SERVICE)
  • RECOVER STANDBY DATABASE FROM SERVICE

With the upgrade of Oracle, the steps are gradually reduced and encapsulated. After 18C, it can be described as a one-click refresh to restore DG synchronization.

Second, the actual combat

Here we demonstrate how to fix it through experiments:

  • 11G routine repair
  • 12C New features (RECOVER… FROM SERVICE)
  • 18C New feature (RECOVER STANDBY DATABASE FROM SERVICE) fixed

Test environment database installation:

  • 11G:./ oracleshellinstall. sh -i 10.211.55.100
  • 12C:./ oracleshellinstall. sh -i 10.211.55.101
  • 18C:./ oracleshellinstall. sh -i 10.211.55.102

For more details on how scripts are used, subscribe to the column:Oracle install scripts with one click.

How to obtain scripts:

  • GitHub Keep updating at 🔥
  • Gitee Keep updating at 🔥

ADG construction can be referred to:

  • Build Oracle ADG for DBCA
  • ADG RAC to Single DATABASE 11G RAC to Single DATABASE
  • ADG single instance series setup (RMAN backup recovery)
  • ADG Single Instance Build series (DBCA)
  • Active Database Duplicate Using Image Copies

The above experimental environment has been set up.

Three, 11G routine repair

First, simulate the standby database power outage, the master database cut several latest archives, and then manually delete, restart DG synchronization.

  • The standby database stops the DG synchronization process
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
Copy the code
  • The master library switches archives multiple times
sqlplus / as sysdba
alter system switch logfile;
Copy the code
  • The master library deletes the most recent archive logs
rm 1_34_1070147137.arc 
rm 1_33_1070147137.arc
Copy the code
  • The synchronization process has been started for the standby database
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • See the GAP
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
 1		   32 			 34

SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)-- -- -- -- -- -- -- -- -- -- -- -- -- -- 31Copy the code

Note: GAP exists in the current DG database. The GAP log is 32– 34.

A. Create a control file for the standby database on the primary database

alter database create standby controlfile as '/tmp/standby.ctl';
Copy the code

B. Use the current SCN number of the standby database as the starting point to perform incremental backup on the active database

  • Example Query the current SCN number of the standby database
sqlplus / as sysdba
select  to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
1086639
Copy the code
  • Check whether data files are added during active/standby GAP
sqlplus / as sysdba
select file# from v$datafile where creation_change# > =1086639;
Copy the code
  • The primary database performs incremental backup based on the SCN number of the standby database
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup INCREMENTAL from scn 1086639 database format '/tmp/incre_%U';
release channel c1;
release channel c2;
}
Copy the code

Note: If new data files exist, restore the newly added data files before restoring the standby database.

C. Copy the incremental backup and control files to the standby database

  • The master copy incremental backup and control files to your standby
scp incre_0* oracle@orcl_stby:/home/oracle
scp standby.ctl oracle@orcl_stby:/home/oracle
Copy the code

Note: Ensure that the disk space of the standby database is sufficient for storage.

D. Use the new control file to start the standby database to the mount state

  • The standby database closes the database instance and switches it to the nomount state
sqlplus / as sysdba
shutdown immediate
startup nomount
Copy the code
  • The standby database restores the new control file
rman target /
restore controlfile from '/home/oracle/standby.ctl';
Copy the code
  • The standby database is in the mount state. Procedure
alter database mount;
Copy the code

E. Register incremental backup with RMAN’s catalog, cancel the log application, and restore incremental backup

  • Ensure that the DG synchronization process has been stopped for the standby database
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Copy the code
  • Backup rman registers incremental backup files
rman target /
catalog start with '/home/oracle/';
YES
Copy the code
  • Restore incremental backup is enabled for the standby database
recover database noredo;
Copy the code

F. Start the recovery process for the standby database

  • The log synchronization process is enabled for the standby database
sqlplus / as sysdba
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • The primary library reactivates synchronization
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;
Copy the code
  • Check whether the GAP exists and check whether the master and slave are synchronized
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
SELECT max(sequence#) from v$archived_log where applied='YES';
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
Copy the code

So far, DG GAP has been repaired. The above repair method is common to all versions.

New 12C feature fixes

First, simulate the standby database power outage, the master database cut several latest archives, and then manually delete, restart DG synchronization.

  • The standby database stops the DG synchronization process:
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
Copy the code
  • The master library switches archives multiple times
sqlplus / as sysdba
alter system switch logfile;
Copy the code
  • Delete the most recent archive logs:
rm 1_30_1070147137.arc 
rm 1_31_1070147137.arc
Copy the code
  • Start the synchronization process for the standby database:
sqlplus / as sysdba
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • See the GAP
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
 1		   30			 31

SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)-- -- -- -- -- -- -- -- -- -- -- -- -- -- 31Copy the code
  • During GAP simulation, there are data files added:
Add data files to the main library
alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;
Copy the code

Note: GAP exists in the current DG database. The GAP log is 30– 31.

A. Record the SCN number of the standby database

  • The standby database records the current SCN number
sqlplus / as sysdba
SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2600487
Copy the code

B. Run recover standby using service to recover

  • With rMAN’s new features,recover standby using serviceRMAN connects to target standby database, and then executes restore standby command using service of primary database.

Grammar:

RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;
Copy the code

Note: Ensure that the TNS of the PRIMARY database is configured, where < PRIMARY DB SERVICE NAME > is TNSNAME.

C. Start the standby database to the Nomount state and restore the control file

  • The standby database is in the Nomount state. Procedure
sqlplus / as sysdba
shutdown immediate
startup nomount
Copy the code
  • The standby database restores control files using the from service command
rman target /
restore standby controlfile from service orcl;
Copy the code
  • The standby database is in the mount state. Procedure
sqlplus / as sysdba
alter database mount;
Copy the code

D. Backup database recovery and GAP repair

  • Check whether data files are added during the active/standby GAP
sqlplus / as sysdba
select file# from v$datafile where creation_change# > =2600487;

FILE#-- -- -- -- -- -- -- -- -- -- 13Copy the code
  • Restore Indicates the newly added data file
rman target /
run
{
SET NEWNAME FOR DATABASE TO '/oradata/ORCL_STBY/%f_%U';
RESTORE DATAFILE 13 FROM SERVICE orcl;
}
Copy the code
  • Because the data file directories of the master and slave are inconsistent, the data file location in the ControlFile must be changed
rman target /
catalog start with '/oradata/ORCL_STBY';
YES
SWITCH DATABASE TO COPY;
Copy the code
  • Example Change the backup file management mode to manual
sqlplus / as sysdba
alter system set standby_file_management=MANUAL;
Copy the code
  • Rename tempFile && logFile
sqlplus / as sysdba
##logfile
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database rename file '/oradata/ORCL/redo03.log' to '/oradata/ORCL_STBY/redo03.log';
alter database rename file '/oradata/ORCL/redo02.log' to '/oradata/ORCL_STBY/redo02.log';
alter database rename file '/oradata/ORCL/redo01.log' to '/oradata/ORCL_STBY/redo01.log';
alter database rename file '/oradata/ORCL/standby_redo04.log' to '/oradata/ORCL_STBY/standby_redo04.log';
alter database rename file '/oradata/ORCL/standby_redo05.log' to '/oradata/ORCL_STBY/standby_redo05.log';
alter database rename file '/oradata/ORCL/standby_redo06.log' to '/oradata/ORCL_STBY/standby_redo06.log';
alter database rename file '/oradata/ORCL/standby_redo07.log' to '/oradata/ORCL_STBY/standby_redo07.log';
##tempfile
alter database rename file '/oradata/ORCL/temp01.dbf' to '/oradata/ORCL_STBY/temp01.dbf';
alter database rename file '/oradata/ORCL/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf' to '/oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf';
alter database rename file '/oradata/ORCL/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf' to '/oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf';
Copy the code
  • Rename standby database to automatic
sqlplus / as sysdba
alter system set standby_file_management=AUTO;
Copy the code
  • Restore the active/standby GAP
recover database from service orcl noredo using compressed backupset;
Copy the code

Notes: If the directories of the primary and secondary libraries are different, you need to switch the path in the catalog control file. Otherwise, an error is reported:

E. Enable the log application of the standby database to check the synchronization

  • Check whether the active and standby SCNS are consistent
sqlplus / as sysdba
col HXFNM for a100
setLine222 select HXFIL File_num,substr(HXFNM,1,40) HXFNM, FHSCN from x$kcvfh;
Copy the code
  • Master Coetzee filed several times
sqlplus / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SWITCH LOGFILE;
Copy the code
  • Enable application logs of the standby database
sqlplus / as sysdba
alter database open;
alter pluggable database all open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • Check whether the synchronization of the standby database is normal
sqlplus / as sysdba
set line222
col member for a60
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;
Copy the code
  • The primary library inserts data
sqlplus test/test@pdb01
insert into test values (999);
commit;
Copy the code
  • Query whether real-time synchronization is performed on the standby database
alter session set container=pdb01;
select * from test.test;
ID
----------
1
2
999
Copy the code

At this point, GAP has been repaired, it can be found that this new feature, 12C, some steps are omitted and encapsulated, further reduce our operating steps, but the internal principle is still the same.

Five, 18C new features restore

  • What’s new in 18C is that the RECOVER STANDBY DATABASE command is used in conjunction with the FROM SERVICE clause to refresh the physical STANDBY DATABASE through changes made to the primary DATABASE. The standby database can be refreshed directly in the enabled state.

Grammar:

RECOVER STANDBY DATABASE FROM SERVICE primary_db;
Copy the code

First, simulate the standby database power outage, the master database cut several latest archives, and then manually delete, restart DG synchronization.

  • The standby database stops the DG synchronization process
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
Copy the code
  • The master library switches archives multiple times
sqlplus / as sysdba
alter system switch logfile;
Copy the code
  • Delete the most recent archive logs
rm 1_69_1070147137.arc 
rm 1_70_1070147137.arc
Copy the code
  • The synchronization process has been started for the standby database
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • See the GAP
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
 1		   70			 70

SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)-- -- -- -- -- -- -- -- -- -- -- -- -- -- 69Copy the code
  • During the simulation GAP, there are data files added
Add data files to the main library
alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;
Copy the code

Note: GAP exists in the current DG database. The GAP log is 69– 70.

A. Run RECOVER STANDBY DATABASE FROM SERVICE to refresh the STANDBY DATABASE

Here’s how to fix DG GAP online with a single command:

  • Disable the log application for the standby database:
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Copy the code
  • The standby database executes the repair command and starts to refresh the standby database online:
rman target /
RMAN> RECOVER STANDBY DATABASE FROM SERVICE orcl;

Starting recover at 19-APR-21
using target database control file instead of recovery catalog
Oracle instance started

Total System Global Area3355441944 bytes

Fixed Size 9141016 bytes
Variable Size671088640 bytes
Database Buffers2667577344 bytes
Redo Buffers   7634944 bytes

contents of Memory Script:
{
   restore standby controlfile from service  'orcl';
   alter database mount standby database;
}
executing Memory Script

Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=502 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/oradata/ORCL_STBY/control01.ctl
output file name=/oradata/ORCL_STBY/control02.ctl
Finished restore at 19-APR-21

released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual

contents of Memory Script:
{
set newname for tempfile  1 to 
 "/oradata/ORCL_STBY/temp01.dbf";
set newname for tempfile  2 to 
 "/oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf";
set newname for tempfile  3 to 
 "/oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf";
   switch tempfile all;
set newname for datafile  1 to 
 "/oradata/ORCL_STBY/system01.dbf";
set newname for datafile  3 to 
 "/oradata/ORCL_STBY/sysaux01.dbf";
set newname for datafile  4 to 
 "/oradata/ORCL_STBY/undotbs01.dbf";
set newname for datafile  5 to 
 "/oradata/ORCL_STBY/pdbseed/system01.dbf";
set newname for datafile  6 to 
 "/oradata/ORCL_STBY/pdbseed/sysaux01.dbf";
set newname for datafile  7 to 
 "/oradata/ORCL_STBY/users01.dbf";
set newname for datafile  8 to 
 "/oradata/ORCL_STBY/pdbseed/undotbs01.dbf";
set newname for datafile  9 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf";
set newname for datafile  10 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf";
set newname for datafile  11 to 
 "/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf";
set newname for datafile  12 to 
 "/oradata/ORCL_STBY/test01.dbf";
set newname for datafile  14 to 
 "/oradata/ORCL/test02.dbf";
   restore from service  'orcl' datafile
14;
   catalog datafilecopy  "/oradata/ORCL_STBY/system01.dbf"."/oradata/ORCL_STBY/sysaux01.dbf"."/oradata/ORCL_STBY/undotbs01.dbf"."/oradata/ORCL_STBY/pdbseed/system01.dbf"."/oradata/ORCL_STBY/pdbseed/sysaux01.dbf"."/oradata/ORCL_STBY/users01.dbf"."/oradata/ORCL_STBY/pdbseed/undotbs01.dbf"."/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf"."/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf"."/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf"."/oradata/ORCL_STBY/test01.dbf"."/oradata/ORCL/test02.dbf";
   switch datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/ORCL_STBY/temp01.dbf in control file
renamed tempfile 2 to /oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf in control file
renamed tempfile 3 to /oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=504 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00014 to /oradata/ORCL/test02.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 19-APR-21 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/system01.dbf RECID=4 STAMP=1070263316 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/sysaux01.dbf RECID=5 STAMP=1070263317 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/undotbs01.dbf RECID=6 STAMP=1070263317 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/pdbseed/system01.dbf RECID=7 STAMP=1070263317 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/pdbseed/sysaux01.dbf RECID=8 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/users01.dbf RECID=9 STAMP=1070263318  cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/pdbseed/undotbs01.dbf RECID=10 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf RECID=11 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf RECID=12 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf RECID=13 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL_STBY/test01.dbf RECID=14 STAMP=1070263318 cataloged datafile copy datafile copy file name=/oradata/ORCL/test02.dbf RECID=15 STAMP=1070263318 datafile 14 switched to datafile copy input datafile copy RECID=15 STAMP=1070263318 file name=/oradata/ORCL/test02.dbf datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=1070263316 file name=/oradata/ORCL_STBY/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1070263317 file name=/oradata/ORCL_STBY/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1070263317 file name=/oradata/ORCL_STBY/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=1070263317 file name=/oradata/ORCL_STBY/pdbseed/system01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=1070263318 file name=/oradata/ORCL_STBY/pdbseed/sysaux01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=1070263318 file name=/oradata/ORCL_STBY/users01.dbf datafile  8 switched to datafile copy input datafile copy RECID=10 STAMP=1070263318 file name=/oradata/ORCL_STBY/pdbseed/undotbs01.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.dbf datafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=1070263318 file name=/oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf datafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=1070263318 file name=/oradata/ORCL_STBY/test01.dbf Executing: alter database rename file'/oradata/ORCL/redo01.log' to '/oradata/ORCL_STBY/redo01.log'
Executing: alter database rename file '/oradata/ORCL/redo02.log' to '/oradata/ORCL_STBY/redo02.log'
Executing: alter database rename file '/oradata/ORCL/redo03.log' to '/oradata/ORCL_STBY/redo03.log'

contents of Memory Script:
{
  recover database from service  'orcl';
}
executing Memory Script

Starting recover at 19-APR-21
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2155383
skipping datafile 6; already restored to SCN 2155383
skipping datafile 8; already restored to SCN 2155383
skipping datafile 14; already restored to SCN 2658548
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00001: /oradata/ORCL_STBY/system01.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 orcl
destination for restore of datafile 00003: /oradata/ORCL_STBY/sysaux01.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 orcl
destination for restore of datafile 00004: /oradata/ORCL_STBY/undotbs01.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 orcl
destination for restore of datafile 00007: /oradata/ORCL_STBY/users01.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 orcl
destination for restore of datafile 00009: /oradata/ORCL_STBY/PDB01/o1_mf_system_j749f5d5_.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 orcl
destination for restore of datafile 00010: /oradata/ORCL_STBY/PDB01/o1_mf_sysaux_j749f5fw_.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 orcl
destination for restore of datafile 00011: /oradata/ORCL_STBY/PDB01/o1_mf_undotbs1_j749f5fx_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
destination for restore of datafile 00012: /oradata/ORCL_STBY/test01.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 19-APR-21
Executing: alter system set standby_file_management=auto
Finished recover at 19-APR-21
Copy the code

It is convenient for everyone to view, so record the whole process of recovery, through the above execution process, you can see:

  • The RECOVER STANDBY DATABASE command restarts the STANDBY instance.
  • Flushes control files from the primary database and automatically renames data files, temporary files, and online logs.
  • It restores new data files added to the primary database and to the standby database at the current time.

B. Change the standby log path for the standby database

The redo log path of the standby database is changed, but the standby log path is not. Therefore, you need to manually change the log path.

  • Example Query the log file path of the standby database
sqlplus / as sysdba
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/ORCL_STBY/redo03.log
/oradata/ORCL_STBY/redo02.log
/oradata/ORCL_STBY/redo01.log
/oradata/ORCL/standby_redo04.log
/oradata/ORCL/standby_redo05.log
/oradata/ORCL/standby_redo06.log
/oradata/ORCL/standby_redo07.log
Copy the code
  • Disable automatic backup file management
sqlplus / as sysdba
alter system set standby_file_management=MANUAL;
Copy the code
  • Clean up the standby log
sqlplus / as sysdba
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
Copy the code
  • Example Change the standby log path
sqlplus / as sysdba
alter database rename file '/oradata/ORCL/standby_redo04.log' to '/oradata/ORCL_STBY/standby_redo04.log';
alter database rename file '/oradata/ORCL/standby_redo05.log' to '/oradata/ORCL_STBY/standby_redo05.log';
alter database rename file '/oradata/ORCL/standby_redo06.log' to '/oradata/ORCL_STBY/standby_redo06.log';
alter database rename file '/oradata/ORCL/standby_redo07.log' to '/oradata/ORCL_STBY/standby_redo07.log';
Copy the code
  • After the modification, enable automatic file management in the standby database
sqlplus / as sysdba
alter system set standby_file_management=AUTO;
Copy the code

C. Log cutting is enabled for the active database and log application is enabled for the standby database

  • Check whether the active and standby SCNS are consistent
sqlplus / as sysdba
col HXFNM for a100
setLine222 select HXFIL File_num,substr(HXFNM,1,40) HXFNM, FHSCN from x$kcvfh;
Copy the code
  • Master Coetzee filed several times
sqlplus / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SWITCH LOGFILE;
Copy the code
  • Enable application logs of the standby database
sqlplus / as sysdba
alter database open;
alter pluggable database all open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Copy the code
  • Check whether the synchronization of the standby database is normal
sqlplus / as sysdba
set line222
col member for a60
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;
Copy the code
  • The primary library inserts data
sqlplus test/test@pdb01
insert into test values (999);
commit;
Copy the code
  • Query whether real-time synchronization is performed on the standby database
sqlplus / as sysdba
alter session set container=pdb01;
select * from test.test;
ID
----------
1
2
999
Copy the code

So far, the GAP of 18C has been fixed, and Oracle is becoming more and more automated as the version is upgraded, which means the future of operation and maintenance automation.

Reference Documents:

  • RESTORE/Recover from Service
  • Restoring and Recovering Files Over the Network (DG)
  • Restoring and Recovering Files Over the Network (RMAN)
  • Rolling Forward a Standby With One Command 18C

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.