By Mouhamadou Diaw

With Oracle Database 18c, we can now refresh a standby database over the network using one RMAN command, RECOVER STANDBY DATABASE.

The RECOVER STANDBY DATABASE command restarts the standby instance, refreshes the control file from the primary database, and automatically renames data files, temp files, and online logs. It restores new data files that were added to the primary database and recovers the standby database up to the current time.
When you use the RECOVER STANDBY DATABASE command to refresh a standby database, you specify either a FROM SERVICE clause or a NOREDO clause. The FROM SERVICE clause specifies the name of a primary service. The NOREDO clause specifies that backups should be used for the refresh, which allows a standby to be rolled forward to a specific time or SCN.
The MRP must be manually stopped on the standby before any attempt is made to sync with primary database.

In this blog I am doing some tests of standby refresh using the Recover Standby Database command.

From a fine Data Guard let’s set the property StandbyFileManagement to MANUAL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DGMGRL> show configuration;
Configuration - CONT18C_DR
  Protection Mode: MaxPerformance
  Members:
  CONT18C_SITE  - Primary database
    CONT18C_SITE1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 18 seconds ago)
DGMGRL>
DGMGRL> edit database 'CONT18C_SITE' set property StandbyFileManagement=MANUAL;
Property "standbyfilemanagement" updated
DGMGRL> edit database 'CONT18C_SITE1' set property StandbyFileManagement=MANUAL;
Property "standbyfilemanagement" updated
DGMGRL> show  database 'CONT18C_SITE' StandbyFileManagement;
  StandbyFileManagement = 'manual'
DGMGRL> show  database 'CONT18C_SITE1' StandbyFileManagement;
  StandbyFileManagement = 'manual'
DGMGRL>

And Then I create add new tablespace and new table in the primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> create tablespace TBS_2 datafile '/u01/app/oracle/oradata/CONT18C/PDB1/tbs_201.dbf' size 5M ;
Tablespace created.
SQL> create table test (id number) tablespace TBS_2;
Table created.
SQL> insert into test values (1);
1 row created.
SQL> insert into test values (2);
1 row created.
SQL> commit;
Commit complete.
SQL>

As expected the changes are not being replicated as shown in the standby alert logfile and in the broker sonfiguration

1
2
3
4
5
6
7
8
9
(3):File #14 added to control file as 'UNNAMED00014' because
(3):the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
(3):The file should be manually created to continue.
MRP0 (PID:6307): MRP0: Background Media Recovery terminated with error 1274
2018-08-15T13:31:08.343276+02:00
Errors in file /u01/app/oracle/diag/rdbms/cont18c_site1/CONT18C/trace/CONT18C_mrp0_6307.trc:
ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/CONT18C/PDB1/tbs_201.dbf'
MRP0 (PID:6307): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Using the broker

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DGMGRL> show database 'CONT18C_SITE1';
Database - CONT18C_SITE1
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          4 minutes 33 seconds (computed 1 second ago)
  Average Apply Rate: 3.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    CONT18C
  Database Error(s):
    ORA-16766: Redo Apply is stopped
  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
Database Status:
ERROR
DGMGRL>

Now let’s try to sync the standby database using the RECOVER command. First let’s stop the recovery process.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DGMGRL> edit database 'CONT18C_SITE1' set state ='APPLY-OFF';
Succeeded.
DGMGRL> show database 'CONT18C_SITE1';
Database - CONT18C_SITE1
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          26 minutes 28 seconds (computed 0 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    CONT18C
Database Status:
SUCCESS
DGMGRL>

After let’s connect with Rman as the target to the standby and let’s run the command
If we try to run the command while connecting to the primary as target we will get following error

1
2
3
4
5
6
7
8
9
10
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CONT18c_SITE;
Starting recover at 15-AUG-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/15/2018 14:00:15
RMAN-05146: must be connected to standby database to issue RECOVER STANDBY DATABASE
RMAN>

So from the standby as target. Note that outputs are truncated

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
[oracle@primaserver admin]$ rman target sys/root@cont18c_site1
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 15 14:03:55 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CONT18C (DBID=4292751651)
RMAN>  RECOVER STANDBY DATABASE FROM SERVICE CONT18c_SITE;
Starting recover at 15-AUG-18
using target database control file instead of recovery catalog
Executing: alter database flashback off
Oracle instance started
Total System Global Area     956299440 bytes
Fixed Size                     8902832 bytes
Variable Size                348127232 bytes
Database Buffers             595591168 bytes
Redo Buffers                   3678208 bytes
contents of Memory Script:
{
   restore standby controlfile from service  'CONT18c_SITE';
   alter database mount standby database;
}
executing Memory Script
Starting restore at 15-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CONT18c_SITE
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/CONT18C/control01.ctl
output file name=/u01/app/oracle/oradata/CONT18C/control02.ctl
Finished restore at 15-AUG-18
released channel: ORA_DISK_1
Statement processed
contents of Memory Script:
{
set newname for datafile  14 to
 "/u01/app/oracle/oradata/CONT18C/PDB1/tbs_201.dbf";
   restore from service  'CONT18c_SITE' datafile
    14;
   catalog datafilecopy  "/u01/app/oracle/oradata/CONT18C/PDB1/tbs_201.dbf";
   switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
Starting restore at 15-AUG-18
Starting implicit crosscheck backup at 15-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 15-AUG-18
Starting implicit crosscheck copy at 15-AUG-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-AUG-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/CONT18C/CONT18C_SITE1/archivelog/2018_08_15/o1_mf_1_47_fq7q5ls5_.arc
File Name: /u01/app/oracle/fast_recovery_area/CONT18C/CONT18C_SITE1/archivelog/2018_08_15/o1_mf_1_48_fq7qn5s3_.arc
File Name: /u01/app/oracle/fast_recovery_area/CONT18C/CONT18C_SITE1/archivelog/2018_08_15/o1_mf_1_49_fq7r0715_.arc
File Name:
…
…
contents of Memory Script:
{
  recover database from service  'CONT18c_SITE';
}
executing Memory Script
Starting recover at 15-AUG-18
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 1550044
skipping datafile 6; already restored to SCN 1550044
skipping datafile 8; already restored to SCN 1550044
skipping datafile 14; already restored to SCN 2112213
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CONT18c_SITE
destination for restore of datafile 00001: /u01/app/oracle/oradata/CONT18C/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CONT18c_SITE
destination for restore of datafile 00003:
…
…
destination for restore of datafile 00012: /u01/app/oracle/oradata/CONT18C/PDB1/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 CONT18c_SITE
destination for restore of datafile 00013: /u01/app/oracle/oradata/CONT18C/PDB1/tbs_nolog01.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 15-AUG-18
flashback needs to be reenabled on standby open
Finished recover at 15-AUG-18
RMAN>

And we can verify that the configuration is now sync

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DGMGRL> edit database 'CONT18C_SITE1' set state ='APPLY-ON';
Succeeded.
DGMGRL> show configuration;
Configuration - CONT18C_DR
  Protection Mode: MaxPerformance
  Members:
  CONT18C_SITE  - Primary database
    CONT18C_SITE1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 37 seconds ago)
DGMGRL>

After opening the standby on read only mode we can verify that everything is now fine

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SQL> alter session set container=pdb1;
Session altered.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_NOLOG
TBS_2
7 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CONT18C/PDB1/system01.dbf
/u01/app/oracle/oradata/CONT18C/PDB1/sysaux01.dbf
/u01/app/oracle/oradata/CONT18C/PDB1/undotbs01.dbf
/u01/app/oracle/oradata/CONT18C/PDB1/users01.dbf
/u01/app/oracle/oradata/CONT18C/PDB1/tbs_nolog01.dbf
/u01/app/oracle/oradata/CONT18C/PDB1/tbs_201.dbf
6 rows selected.
SQL> select * from test;
        ID
----------
         1
         2
SQL>
Post Views: 6,271
Thumbnail [60x60]
by
Oracle Team