By Mouhamadou Diaw
Generally we do active duplicate from primary database, but I think it should be fine to see what happen if we do active duplicate a database from a standby database. So, I do some tests and I write the results in this blog
Below the actual configuration of my Data Guard environment
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DGMGRL> show configuration Configuration - db21 Protection Mode: MaxPerformance Members: DB21_SITE1 - Primary database DB21_SITE2 - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 35 seconds ago) DGMGRL> |
The tests are done with Oracle 21c but I guess it should work for 19c
Standby in Read Only Mode
The first tests I do is with the standby opened in read only mode
If you do not have Active Data Duard license stop the apply process before opening the database in Read Only mode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
oracle@oraadserver2: /home/oracle/ [DB21 (CDB$ROOT)] DB21 ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DB21_SITE2 OPEN_MODE : READ ONLY LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 21.0.0.0.0 CDB Enabled : YES List PDB(s) MOUNTED : PDB1, PDB2, PDB3 List PDB(s) READ ONLY : PDB$SEED ************************************* oracle@oraadserver2: /home/oracle/ [DB21 (CDB$ROOT)] |
The goal is to duplicate the standby database DB21_SITE2 to DB21CLNE to a remote server. I assume that all network files are already configured. Password file, spfile are also already configured.
From the source server
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
|
oracle@oraadserver2: /home/oracle/ [DB21 (CDB$ROOT)] tnsping db21_site2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:29:59 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet .ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL))) OK (0 msec) oracle@oraadserver2: /home/oracle/ [DB21 (CDB$ROOT)] tnsping db21clne TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:30:05 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet .ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE))) OK (0 msec) oracle@oraadserver2: /home/oracle/ [DB21 (CDB$ROOT)] |
From the target server
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
|
oracle@oraadserver4: /home/oracle/ [DB21CLNE (CDB$ROOT)] tnsping db21_site2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:12 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet .ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21_SITE2_DGMGRL))) OK (0 msec) oracle@oraadserver4: /home/oracle/ [DB21CLNE (CDB$ROOT)] tnsping db21clne TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 06-APR-2022 13:33:16 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet .ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB21CLNE))) OK (10 msec) oracle@oraadserver4: /home/oracle/ [DB21CLNE (CDB$ROOT)] |
I then create a simple duplicate rman script
1
2
3
4
5
6
|
oracle@oraadserver4: [DB21CLNE (CDB$ROOT)] cat duplicate_from_standby.rcv connect target sys/******@db21_site2 connect auxiliary sys/****@db21clne run { duplicate target database to DB21CLNE from active database nofilenamecheck; } |
After starting the target database DB21CLNE in NOMOUNT state, I run the script to create DB21CLNE
1
|
oracle@oraadserver4: [DB21CLNE (CDB$ROOT)] nohup rman cmdfile=duplicate_from_standby.rcv log=duplicate_standby_` date + "%Y-%m-%d_%H%M%S" `.log & |
A few minutes later the duplicate went fine (output 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
|
Recovery Manager: Release 21.0.0.0.0 - Production on Wed Apr 6 14:51:56 2022 Version 21.1.0.0.0 Copyright (c) 1982, 2020, Oracle and / or its affiliates. All rights reserved. RMAN> connect target * 2> connect auxiliary * 3> run { 4> duplicate target database to DB21CLNE from active database nofilenamecheck; 5> } 6> connected to target database : DB21 (DBID=1137202071) connected to auxiliary database : DB21CLNE ( not mounted) Starting Duplicate Db at 06-APR-2022 14:51:57 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=25 device type=DISK current log archived at primary database current log archived at primary database duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl'' comment= ''Set by RMAN'' scope=spfile" ; sql clone "alter system set db_name = ''DB21'' comment= ''Modified by RMAN duplicate'' scope=spfile" ; sql clone "alter system set db_unique_name = ''DB21CLNE'' comment= ''Modified by RMAN duplicate'' scope=spfile" ; shutdown clone immediate; startup clone force nomount restore clone from service 'db21_site2' primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set control_files = '' /u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl '' , '' /u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl '' comment= '' Set by RMAN '' scope=spfile sql statement: alter system set db_name = '' DB21 '' comment= '' Modified by RMAN duplicate '' scope=spfile sql statement: alter system set db_unique_name = '' DB21CLNE '' comment= '' Modified by RMAN duplicate '' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 1577057624 bytes Fixed Size 9686360 bytes Variable Size 385875968 bytes Database Buffers 889192448 bytes Redo Buffers 292302848 bytes Starting restore at 06-APR-2022 14:56:46 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=333 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service db21_site2 channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time : 00:00:01 output file name =/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v3hqr1_.ctl output file name =/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v3hqr7_.ctl Finished restore at 06-APR-2022 14:56:49 database mounted … … Executing: alter database force logging contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Executing: alter database flashback on contents of Memory Script: { sql clone "alter pluggable database all open" ; } executing Memory Script sql statement: alter pluggable database all open Finished Duplicate Db at 06-APR-2022 15:02:41 Recovery Manager complete. |
And DB21CLNE is created and opened in READ WRITE and ready to be used
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
oracle@oraadserver4:/u01/app/oracle/admin/DB21CLNE/ create / [DB21CLNE (CDB$ROOT)] DB21CLNE ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DB21CLNE OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : YES FORCE_LOGGING : YES VERSION : 21.0.0.0.0 CDB Enabled : YES List PDB(s) READ ONLY : PDB$SEED List PDB(s) READ WRITE : PDB1, PDB2, PDB3 ************************************* |
Standby in MOUNT Mode
I decide to do the same tests , but with the source standby database in MOUNT state
1
2
3
4
5
6
7
8
9
10
11
12
13
|
oracle@oraadserver2:/home/oracle/ [DB21 (CDB$ROOT)] DB21 ********* dbi services Ltd. ********* STATUS : MOUNTED DB_UNIQUE_NAME : DB21_SITE2 OPEN_MODE : MOUNTED LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : YES FORCE_LOGGING : YES CDB Enabled : YES List PDB(s) MOUNTED : PDB$SEED, PDB1, PDB2, PDB3 ************************************* oracle@oraadserver2:/home/oracle/ [DB21 (CDB$ROOT)] |
In this case the starts fine, but the recovery process was asking for a archived and was not able to finish. There was no error returned, but the duplicate is asking, asking the missing archived
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
|
RMAN> connect target * 2> connect auxiliary * 3> run { 4> duplicate target database to DB21CLNE from active database nofilenamecheck; 5> } 6> connected to target database : DB21 (DBID=1137202071, not open ) connected to auxiliary database : DB21CLNE ( not mounted) Starting Duplicate Db at 06-APR-2022 14:12:28 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=23 device type=DISK current log archived at primary database current log archived at primary database duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/DB21CLNE/controlfile/o1_mf_k4v15kz7_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB21CLNE/controlfile/o1_mf_k4v15l04_.ctl'' comment= ''Set by RMAN'' scope=spfile" ; sql clone "alter system set db_name = ''DB21'' comment= ''Modified by RMAN duplicate'' scope=spfile" ; sql clone "alter system set db_unique_name = ''DB21CLNE'' comment= ''Modified by RMAN duplicate'' scope=spfile" ; shutdown clone immediate; startup clone force nomount restore clone from service 'db21_site2' primary controlfile; alter clone database mount; } executing Memory Script … … starting media recovery archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 ... ... archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 archived log file name =/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06/o1_mf_1_35_k4v156k8_.arc thread=1 sequence =35 …. |
As a workaround from the standby server I manually copy the requested archive to the remote source server
1
|
oracle@oraadserver2 ] scp o1_mf_1_35_k4v156k8_.arc oraadserver4:/u01/app/oracle/fast_recovery_area/DB21_SITE2/archivelog/2022_04_06 |
And the duplicate process went fine
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
… … contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Executing: alter database flashback on contents of Memory Script: { sql clone "alter pluggable database all open" ; } executing Memory Script sql statement: alter pluggable database all open Finished Duplicate Db at 06-APR-2022 14:34:39 Recovery Manager complete. |
So we can see that duplicate can be done from a standby database like a primary one. Just when the standby is mounted, you may have to manually copy some archived.
Note that I repeat the duplicate a second time with the database mount and I had the same behavior, I had to manually copy a missing archived log.
Alex
01.11.2023I get the error with the absent archived log file even when the source standby database is opened (in read only mode of course)