Oracle Database 12.2 -- How to Clone Very Large Databases (VLDB)

 Problem

You need to clone a very large database (VLDB)

Solution

Beginning from Oracle 12.2 you may employ the new RESTORE FROM SERVICE and RECOVER FROM SERVICE options of the RMAN, combining with DataGuard redo transport services:

1. Prepare the target host. 

This step includes installing necessary software, OS setup, Oracle home etc. Ensure an Oracle instance can be created and started

2. Enable FORCE LOGGING mode for the source database:

SQL> alter database force logging;

3. Enable standby file management on the source:

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

4. Copy the password file and the TDE wallets from the source to the target host

5. Create proper TNS entries for the source and target databases at the both source and target:

<source_db_unique_name> =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL= TCP) (HOST=<source_ip>)(PORT=<PORT>))
  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME =<source database service name>)
  )
)

<target_db_unique_name> =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL = TCP) (HOST=<target_ip>)(PORT=<PORT>))
  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME =<standby database service name>)
  )
)

6. At the target system, create a init parameter file with db_name parameter matching the one of the source database, and the db_unique_name parameter with an appropriate value

Start target instance in NOMOUNT with the PFILE created.

Create SPFILE from PFILE and restart the instance with the SPFILE

7. At target, restore the standby control file from the source database, and mount the target database:

$ rman target /

RMAN> restore standby controlfile from service <source_db_unique_name>;

RMAN> alter database mount;

8. Restore datafiles from the source database:

Hint: Suggested to use the screen utility to execute all long running operations as restore or recovery

Determine the number of CPU on the source and target:

$ grep -c ^processor /proc/cpuinfo

Set the required level of parallelism by changing the RMAN configuration:

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8;

Restore all datafiles from the source database:

(The commands assume that ASM or OMF being used)

RMAN> set newname for database to new;
RMAN> restore database from service <source_db_unique_name> using compressed backupset;

If changing the RMAN configuration is not desirable, allocate the necessary number of channels with the RUN block:

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
set newname for database to new;
restore database from service <source_db_unique_name> using compressed backupset;
}

In case the restore operation fails, or you need deliberately stop the operation, you may proceed with the remaining datafiles:

Identify the datafiles which were partially restored and remove them:

select HXFNM from x$kcvfh where FHTYP=11;

Generate the restore command for the remaining datafiles:

select 'restore datafile '||listagg(FILE#,',') within group (order by file#)||' from service <source_db_unique_name> using compressed backupset;' from v$datafile_header where ERROR='FILE NOT FOUND';

9. Catalog the restored datafiles and switch the database:

RMAN> catalog start with '<path>';

RMAN> switch database to copy;

10. Enable archivelog shipping from the source to target:

At target:

SQL> alter system set log_archive_config='dg_config=(<source_db_unique_name>,<target_db_unique_name>)' scope=both;

At source:

SQL> alter system set log_archive_config='dg_config=(<source_db_unique_name>,<target_db_unique_name>)' scope=both;

SQL> alter system set log_archive_dest_3='service=<target_db_unique_name> arch reopen=15 db_unique_name=<target_db_unique_name>' scope=both; 

SQL> alter system switch logfile;

10.a. Alternatively, in case the source database is not very active and the number of archivelogs required for recovery is not high, the archivelogs may be copied directly to target using RMAN. Connect to source database as target, connect to target database as auxiliary:

RMAN> connect target sys/pwd@<source_db_unique_name>
RMAN> connect auxiliary sys/pwd@<target_db_unique_name>
RMAN> run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
allocate channel c3 type disk ;
allocate channel c4 type disk ;
backup as copy archivelog from sequence <n> thread <m> auxiliary format '<format>';
}

11. At target, recover the datafiles from the source database

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database from service <source_db_unique_name> using compressed backupset noredo;
}

12. Recover the database:

SQL> recover standby database until consistent;

This will start recovery of the target database using the shipped archivelog files until all the datafiles are consistent. 

13. Convert target database from standby to primary:

Remove/reset all DataGuard related parameters for the source database:

SQL> alter system reset log_archive_config scope=both;

SQL> alter system reset log_archive_dest_3 scope=both;

Remove/reset all DataGuard related parameters for the target database:

SQL> alter system reset log_archive_config scope=both;

Convert the target to primary:

SQL> alter database activate standby database;
SQL> alter database open;

14. Change DBNAME and DBID of the target database:

Shutdown the target database and open in mount:

SQL> shu immediate

SQL> startup mount:

Run DBNEWID utility:

$ nid target=/ dbname=<target_db_name>

Update db_name in spfile:

SQL> startup nomount:

SQL> alter system set db_name=<target_db_name> scope=spfile;

SQL> startup force mount

SQL> alter database open resetlogs;