Clone a Database to Point-in-Time with RMAN

Introduction

Recently we needed to refresh our Development environment with Production data from February 29th. Normally we would use a third-party tool but were having issues and decided to restore the database manually. Here is what we did.

Note, in the example below, our EBS Production CDB/PDB database is PRODCDB/PROD and Development database is DEVLCDB/DEVL. The Production server is ebsprod and the Development server is ebsdevl.

Clone the Database

Step 1: Confirm entries in the oratab file

First, we confirmed we have the following in our /etc/oratab file on our Development server


DEVLCDB:/opt/app/oracle/product/19.3.0/dbhome_1:N
DEVL:/opt/app/oracle/product/19.3.0/dbhome_1:N


Step 2: Create new LISTENER file

We created a new $TNS_ADMIN/listener.ora file on our Development server and added the following


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DEVLCDB)
(ORACLE_HOME = /opt/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = DEVLCDB)
)
)


Step 3: Add entries to TNSNAMES file

We added the following to the $TNS_ADMIN/tnsnames.ora on our Development server


DEVLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=ebsdevl.beejartha.com)(PORT=1521))
(CONNECT_DATA = (SERVICE_NAME=DEVLCDB)(INSTANCE_NAME=DEVLCDB))
)
ebsdevl:1521 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=ebsdevl.beejartha.com)(PORT=1521))
)
DEVLCDB_REMOTE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=ebsdevl.beejartha.com)(PORT=1521))
)


Step 4: Copy the init/password files to the Development server

Next, from our Development server, we copied the init.ora and password files from the Production server


unix> cd $ORACLE_HOME/dbs
unix> scp -p oracle@ebsprod:/opt/app/oracle/product/19.3.0/dbhome_1/dbs/initPRODCDB.ora .
unix> scp -p oracle@ebsprod:/opt/app/oracle/product/19.3.0/dbhome_1/dbs/orapwPRODCDB .


Step 5: Create init.ora for the Development database

We then renamed the initPRODCDB.ora to initDEVLCDB.ora.
We edited the initDEVLCDB.ora file and changed all references of the Production database and server to the Development database and server (ie, PROD to DEVL, ebsprod to ebsdevl).

Step 6: Create directories specified in the init.ora file

We created any directories that are referenced in the initDEVLCDB file. For example, we needed to create the /opt/app/oracle/admin/DEVLCDB/adump directory for the audit file destination parameter.

Step 7: Start Development database
We now start the Development database using the new init.ora file in nomount mode


unix> export ORACLE_SID=DEVLCDB
unix> sqlplus sys as sysdba
SQL> startup nomount pfile='/opt/app/oracle/product/19.3.0/dbhome_1/dbs/initDEVLCDB.ora';
SQL> exit;


Step 8: Start RMAN and connect to the development database


unix> export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
unix> rman target / nocatalog


Step 9: Restore the controlfile


RMAN> run {
allocate channel ch1 type 'sbt_tape' PARMS="...";
restore controlfile from 'c-3061845234-20240202-0a';
}


Step 10: Mount the database

Once the control file is restored successfully, mount the database.


RMAN> alter database mount;


Step 11: Preview restore (optional)

We ran a report to see what backups and archived log files that RMAN will use to restore and recover the February 29th database. Note, this is optional only generates a report. It does not actually restore the database.


RMAN> run {
allocate channel ch1 type 'sbt_tape' PARMS="...";
set UNTIL TIME "to_date('02/02/2024 11:59:59 pm','mm/dd/yyyy hh:mi:ss am')";
restore database preview summary;
}


Step 12: Restore and Recover the database

Now we restored and recover the database to February 29th at 11:59:59pm


RMAN> run {
allocate channel ch1 type 'sbt_tape' PARMS="...";
allocate channel ch2 type 'sbt_tape' PARMS="...";
allocate channel ch3 type 'sbt_tape' PARMS="...";
allocate channel ch4 type 'sbt_tape' PARMS="...";
set UNTIL TIME "to_date('02/29/2024 11:59:59 pm','mm/dd/yyyy hh:mi:ss am')";
restore database;
recover database;
}