Duplicate Database Cluster Oracle Environment Using From Active Database

Publicado em por Jone Jarouj

In this article, I will demonstrate how to perform data cloning using the duplicate database method without using a backup, or as some may prefer to call, via active database, on different servers.

This method of database cloning without a backup is a feature introduced in version 11g and in previous versions, cloning was only possible if a full backup had been performed before the operation.

To perform the duplicate database from active database, we need to connect to both the target/source database and the auxiliary/destination database via the listener. This is why the initial adjustment in the listener is necessary.

We will clone a RAC environment using this method. The clone will be created as a single-node environment and then converted to a cluster, as per the original environment.

Below are some details in the table:

Source DatabaseORDB
Source Node 1oraordb01
Source Node 2oraordb02
Destination DatabaseORCL
Destination Node 1oraorcl01
Destination Node 2oraorcl02

To begin the duplicate process, let’s first to configure the listener.ora and tnsnames.ora files on source and destination server as well.

In the tnsnames.ora file, we must configure the connection alias identically on both source and destination servers, as shown below:

[oracle@oraordb01 ]$  vi /u01/app/oracle/product/19.0.0.0/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
ORDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraordb01)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ordb01)
    )
  )
 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraorcl01)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl01)
    )
    (UR=A)
  )

Don’t forget to set UR=A parameter. Without this, nothing will connect.

In the listener.ora file, we must set up the service as shown below and reload listener to startup this new service:

[oracle@oraordb01 ]$ vi /u01/app/grid/19.0.0.0/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/grid/19.0.0.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl01)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0)
      (GLOBAL_DBNAME = orcl01)
    )
)

[oracle@oraordb01 ]$  lsnrctl reload
[oracle@oraordb01 ]$  lsnrctl status

With the strings already configured in the tnsnames.ora files on the destination environment nodes, we will begin the procedure by creating an init (pfile) from the source environment, so that all parameters can be the same on both the source and the destination, with only a few specific parameters being changed at the end of the process.

In the source environment, connect to sqlplus and then create the init file:

[oracle@oraordb01 ]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 20 07:43:15 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> create pfile='/tmp/initorcl01.ora' from spfile;

Then, move the file to the $ORACLE_HOME/dbs directory of the destination RAC.

[oracle@oraordb01 ]$ scp /tmp/initorcl01.ora oraorcl01:/u01/app/oracle/product/19.0.0.0/dbs/initorcl01.ora

We will modify the necessary parameters in the init file to perform the duplicate in single-node mode:

  • Replace all entries referring to node 1 (ordb01) with * (asterisk).
  • Remove all entries for node 2 (ordb02).
  • Remove all entries starting with the following parameters:
cluster
instance_number 
thread

As shown in the examples below:

Delete or comment out:

*.cluster_database_instances=2
*.cluster_database=true

ordb01.cluster_interconnects='10.0.0.1'
ordb02.cluster_interconnects='10.0.0.2'

ordb01.instance_number=1
ordb02.instance_number=2

*.remote_listener='racordb-scan:2569'

ordb02.thread=2
ordb01.thread=1

Modify:

audit_file_dest='/u01/app/oracle/admin/ordb/adump'
to
audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.db_name='ORDB'
to 
*.db_name='ORCL'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORDBXDB)'
to
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

*.control_files='+DATA/ordb/controlfile/current.123456789','+RECO/ordb/controlfile/current.123456789' 
to 
*.control_files='+DATA/orcl/controlfile/controlorcl01.ora','+RECO/orcl/controlfile/controlorcl02.ora'

*.wallet_root='/u01/app/oracle/WALLETS/ORCL'

*.db_create_file_dest='+DATA'

*.db_create_online_log_dest_1='+DATA'

ordb01.undo_tablespace='UNDOTBS1'
to
*.undo_tablespace='UNDOTBS1'

Note: Make sure to verify the exact names of the UNDOs from the source environment to modify this parameter, as errors may occur at the end of the duplicate process.

To check, run the following select on the source database:

[oracle@oraordb01 ]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 20 07:43:15 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';

Add the following parameters to move the data files from one location to another:

Use the parameter log_file_name_convert in the init file. If not specified, RMAN will create the files with the same name as the target (which should be on a different host), but NOFILENAMECHECK must be used.

The parameter is structured as follows: ‘SOURCE’, ‘DESTINATION’, ‘SOURCE’, ‘DESTINATION’

*.log_file_name_convert='+DATA/ORDB/','+DATA/ORCL/','+RECO/ORDB/','+RECO/ORCL/'

For the datafiles, use the set newname and set auxname commands in the duplicate command. However, you must use the pfile instead of the spfile during the duplicate, or you will encounter an error.

Instead of using the set commands, you can use the db_file_name_convert parameter in the init file. If not specified, RMAN will use the same name as the target, requiring NOFILENAMECHECK, and it must be on a different host.

The parameter is structured as follows: ‘SOURCE’, ‘DESTINATION’, ‘SOURCE’, ‘DESTINATION’

*.db_file_name_convert='+DATA/ORDB/','+DATA/ORCL/'

We will create the directories on the source, preparing both nodes of the destination RAC to receive the new database.

For both nodes, create the following dump directories:

[oracle@oraorcl01 ]$ cd /u01/app/oracle/admin/
[oracle@oraorcl01 ]$ mkdir orcl
[oracle@oraorcl01 ]$ cd orcl
[oracle@oraorcl01 ]$ mkdir adump dpdump hdump

Create the wallet directories for TDE, as well as for ZDL:

  • TDE
[oracle@oraorcl01 ]$ cd /u01/app/oracle/WALLETS/
[oracle@oraorcl01 ]$ mkdir -p ORCL/tde
[oracle@oraorcl01 ]$ cd ORCL/tde
[oracle@oraorcl01 ]$ scp -p oraordb01:/u01/app/oracle/WALLETS/ORCL/tde/*.* .
[oracle@oraorcl01 ]$ scp -p oraordb02:/u01/app/oracle/WALLETS/ORCL/tde/*.* .

Create the cluster services on each destination node:

[oracle@oraorcl01 ]$ srvctl add database -d orcl -o /u01/app/oracle/product/19.0.0.0
[oracle@oraorcl01 ]$ srvctl add instance -d orcl -i orcl01 -n oraorcl01
[oracle@oraorcl01 ]$ srvctl add instance -d orcl -i orcl02 -n oraorcl02

Note: Do not add a slash at the end of ORACLE_HOME, as this will prevent connection errors at the end of the process.

Create the orapw file inside ASM and enter the password when prompted:

[oracle@oraorcl01 ]$ asmcmd
ASMCMD> pwcreate --dbuniquename ORCL +DATA/orapworcl

We will connect to the database using sqlplus, then start the database with the init file we created earlier, and finally create an spfile:

[oracle@oraorcl01 ]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 20 07:43:15 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> startup force nomount pfile=/u01/app/oracle/product/19.0.0.0/dbs/initorcl01.ora
SQL> create spfile from pfile;
SQL> shutdown immediate;

Send the spfile outside of ASM to avoid an error where the spfile is used when RMAN modifies parameters in the file during the clone process.

First, we need to check the alert log of the destination database to determine where the spfile was created:

[oracle@oraorcl01 ]$ cd $ORACLE_BASE/diag/rdbms/orcl/orcl01/trace
[oracle@oraorcl01 ]$ vi alert_orcl01.log

Inside the vi editor for the file, search for the spfile created inside ASM.

After this, use asmcmd to copy the file outside:

[oracle@oraorcl01 ]$ asmcmd
ASMCMD> cp +DATA/spfileorcl01.ora /u01/app/oracle/product/19.0.0.0/dbs/spfileorcl01.ora

Modify the database configuration to use the spfile that was moved outside of ASM:

[oracle@oraorcl01 ]$ srvctl modify database -d orcl -spfile /u01/app/oracle/product/19.0.0.0/dbs/spfileorcl01.ora

Start the database in nomount mode:

[oracle@oraorcl01 ]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 20 07:42:34 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup force nomount

ORACLE instance started.
Total System Global Area  943669248 bytes
Fixed Size                  2234000 bytes
Variable Size             276826480 bytes
Database Buffers          658505728 bytes
Redo Buffers                6103040 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      orcl

SQL>

Set environment variables:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
export ORACLE_SID=orcl01

Connect to the target ORDB database and the auxiliary ORCL database, then start the duplicate process.

[oracle@oraorcl01 ]$ rman target sys/oracle@ordb auxiliary sys/oracle@orcl

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 20 07:54:24 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORDB (DBID=678058935)
connected to auxiliary database: ORCL (not mounted)

RMAN> run {
    allocate channel x1 type disk;
    allocate channel x2 type disk;
    allocate channel x3 type disk;
    allocate auxiliary channel x1 type disk;
    allocate auxiliary channel x2 type disk;
    allocate auxiliary channel x3 type disk;
    duplicate target database to orcl from active database
    NOFILENAMECHECK;
}

Finally, we will configure the environment for a 2-node cluster.

Connected to sqlplus in the source environment, change the following parameters:

[oracle@oraorcl01 ]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 20 07:43:15 2025
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> alter system set cluster_database_instances=2 scope=spfile sid='*';
SQL> alter system set cluster_database=true scope=spfile sid='*';

Configure the parameters that were previously removed or commented out, for the cluster configurations:

SQL> alter system set cluster_interconnects='10.0.0.1' scope=spfile sid='orcl01';
SQL> alter system set cluster_interconnects='10.0.0.2' scope=spfile sid='orcl02';

SQL> alter system set instance_number=1 scope=spfile sid='orcl01';
SQL> alter system set instance_number=2 scope=spfile sid='orcl02';

SQL> alter system set thread=1 scope=spfile sid='orcl01';
SQL> alter system set thread=2 scope=spfile sid='orcl02';

SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='orcl02';

Note: Make sure to verify the exact name of the UNDO in the source environment to modify this parameter, as mentioned at the beginning of this article.

At the operating system prompt:

[oracle@oraorcl01 ]$ srvctl stop database -d orcl

Connect to asmcmd and move the spfile parameter file back into ASM, then modify the cluster’s initialization configuration. Finally, start the database:

[oracle@oraorcl01 ]$ asmcmd
ASMCMD> cp /u01/app/oracle/product/19.0.0.0/dbs/spfileorcl01.ora +DGAUX/spfileorcl01.ora

Then

[oracle@oraorcl01 ]$ srvctl modify database -d orcl -spfile +DATA/spfileorcl01.ora
[oracle@oraorcl01 ]$ srvctl start database -d orcl

Adjust block change tracking if necessary.

Deixe um comentário