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.

Post Views: 8,145
Thumbnail [60x60]
by
Oracle Team