Duplication of databases from a physical standby
by Venkata Gogineni, Oracle Applications Database Administrator, Rackspace Technology
Introduction
This blog describes how to duplicate a database from a physical standby database. Database duplication copies the target database over the network to the auxiliary destination and then creates the duplicate database. You do not need pre-existing RMAN backups and copies.
Duplicate database from a physical standby is supported from Oracle® 11g release 2 and onwards.
To perform RMAN active duplication from physical standby you need the
following:
- Databases: Oracle 11g to the latest version
- Platform: Linux® 7
- Standby DB name: `PROD_DR`
- Target DB name: `TEST`
Open the standby database in read-only mode
Stop the recovery at the physical standby database and then open the
database in read-only mode to allow duplication.
Use the following command to check the status of the standby database:
PROD_DR> select name, open_mode, log_mode from v$database;
You should see output similar to the following:
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
PROD_DR MOUNTED ARCHIVELOG
Use the following command to stop the recovery of the standby database:
SQL> alter database recover managed standby database cancel;
Database altered.
Then, open the database in read-only mode:
SQL> alter database open read only;
Database altered.
Check the status of the database again to see that it is in read-only instead
of mounted:
PROD_DR> select name, open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
PROD_DR READ ONLY ARCHIVELOG
Prepare the target server
In this section, we'll copy the entire standby Oracle home binaries to the target instance server, prepare the pfile from the source database, and make changes according to the new database name.
First, prepare the pfile by using the following commands:
cd $ORACLE_HOME/dbs
initTEST.ora
*.control_files='+DATA/cntrl01.dbf', '+DATA/cntrl02.dbf'
*.db_create_file_dest='+DATA'
*.db_file_name_convert='+PROD_DR_DATA','+DATA'
*.db_name='TEST'
*.db_unique_name='TEST'
*.diagnostic_dest='/u01/app/diag'
*.log_file_name_convert='+PROD_DR_DATA','+DATA'
Copy the existing standby password file and rename it to match the SID used for the TEST database, or you can create a new password file on the target server with the same source password.
cd $ORACLE_HOME/dbs
cp orapwPROD_DR orapwTEST
Configure static listener
Use the following steps to configure the static listener for the test
database:
cd $ORACLE_HOME/network/admin
cat listener.ora
ADR_BASE_LISTENER_LOCAL = /u01/app/oracle
LISTENER_LOCAL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TEST.ras.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_LOCAL = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DGNEER) (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1) (SID_NAME = TEST)))
Start the listener by using the following command:
lsnrctl status listener_local
You should see output similar to the following example:
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2020 02:57:35
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= TEST.ras.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias listener_local
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 04-JAN-2020 04:53:15
Uptime 156 days 21 hr. 4 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/12.1.0.2/dbhome_1/admin/diag/tnslsnr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= TEST.ras.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "TEST" has 2 instance(s).
Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
Instance "TEST", status READY, has 1 handler(s) for this service...
The command completed successfully
Create an Oracle Net alias
Next, you need to create an Oracle Net alias to reach the standby database:
PROD_DR=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= standbydb.ras.com)(PORT=1523))
(CONNECT_DATA=
(SID=PROD_DR)
)
)
Start the target database
Then you need to start the target database in a `nomount` state by using the
following commands:
sqlplus "/ as sysdba"
startup nomount
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_name string TEST
SQL> show parameter uniq
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_unique_name string TEST
Use RMAN to test the connection
After the target database is running in `nomount` state, use RMAN to test
the connection:
[oracle@TEST.ras.com dbs]$ rman target sys@PROD_DR auxiliary sys@TEST
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 09 03:25:22 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: PROD_DR (DBID=4252464621)
auxiliary database Password:
connected to an auxiliary database: TEST (not mounted)
Restore and duplicate the database
RMAN> target sys@PROD_DR auxiliary sys@TEST
RMAN> duplicate target database to TEST from active database nofilenamecheck;
After the duplicate command has been completed, you need to restart the standby
database and re-enable recovery by using the following commands:
PROD_DR> shut immediate
PROD_DR> startup mount;
PROD_DR> alter database recover managed standby database disconnect from session;
Check the target database status
Use the following commands to check the status of the target database:
[oracle@TEST.ras.com dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 9 05:15:53 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name, open_mode, log_mode , database_role from v$database;
NAME OPEN_MODE LOG_MODE DATABASE_ROLE
--------- -------------------- ------------ ----------------
TEST READ WRITE ARCHIVELOG PRIMARY
Conclusion
In this blog, we duplicated a database from a physical standby without taking any physical backups and we restored the target database to the latest archive supplied in the standby database.
Recent Posts
Patrones de redes híbridas de Google Cloud - Parte 2
Octubre 16th, 2024
Patrones de redes híbridas de Google Cloud - Parte 2
Octubre 15th, 2024
Cómo aprovecha Rackspace AWS Systems Manager
Octubre 9th, 2024
Windows Server impide la sincronización horaria con Rackspace NTP
Octubre 3rd, 2024