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> |
Shayad
01.08.2023Hi I tried with multiple channels in run block as well as rman parameter setting level but it takes only one channel results in slow refresh.
can you suggest how to use parallelism in this method.?