Quickest Database Copy

This document outlines the steps for creating a copy of a database on the same linux/unix machine or one with the same operating system. The example below is for making a copy on a new server. This is probably the quickest method for duplicating a single instance database.

Backup controlfile

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Check the alert_SID.log in the trace folder ($ORACLE_BASE/diag/sid/SID/trace) for the name of the trace file.

Example of a trace file for a single instance multitenant database.

--STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORAEMG" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u02/oradata/ORAEMG/redo01.log' SIZE 290M BLOCKSIZE 512,

GROUP 2 '/u02/oradata/ORAEMG/redo02.log' SIZE 290M BLOCKSIZE 512,

GROUP 3 '/u02/oradata/ORAEMG/redo03.log' SIZE 290M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u02/oradata/ORAEMG/system01.dbf',

'/u02/oradata/ORAEMG/pdbseed/system01.dbf',

'/u02/oradata/ORAEMG/sysaux01.dbf',

'/u02/oradata/ORAEMG/pdbseed/sysaux01.dbf',

'/u02/oradata/ORAEMG/undotbs01.dbf',

'/u02/oradata/ORAEMG/pdbseed/undotbs01.dbf',

'/u02/oradata/ORAEMG/users01.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/system01.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/sysaux01.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/undotbs01.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/users01.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/mgmt_ecm_depot1.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/mgmt.dbf',

'/u02/oradata/ORAEMG/EMPDBREPOS/mgmt_deepdive.dbf',

'/u03/oradata/ORAEMG/dbtest/system02.dbf',

'/u03/oradata/ORAEMG/dbtest/sysaux02.dbf',

'/u03/oradata/ORAEMG/dbtest/undotbs02.dbf',

'/u03/oradata/ORAEMG/dbtest/users2.dbf'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/ORAEMG/ORAEMG/archivelog/2018_11_09/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Open all the PDBs.

ALTER PLUGGABLE DATABASE ALL OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/ORAEMG/temp01.dbf'

SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = PDB$SEED;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/ORAEMG/pdbseed/temp01.dbf'

SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = EMPDBREPOS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/ORAEMG/EMPDBREPOS/temp01.dbf'

SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = DBTEST;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/ORAEMG/dbtest/temp02.dbf'

SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER SESSION SET CONTAINER = CDB$ROOT;

-- End of tempfile additions.

Change CREATE CONTROLFILE REUSE DATABASE "ORAEMG" RESETLOGS ARCHIVELOG

to CREATE CONTROLFILE REUSE SET DATABASE "NEWSID" RESETLOGS ARCHIVELOG

Update the file locations and save everything to a file. ex. ccf_NEWSID.sql

Alternative is to create the above file manually by quering the database for all the file names.

SQL> spool dup_files.log

SQL> SELECT NAME FROM V$CONTROLFILE;

SQL> SELECT MEMBER FROM V$LOGFILE;

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

SQL> spool off

Make a pfile copy of the spfile

SQL> Create pfile from spfile;

This will create an initSID.ora file in the same location of the spfileSID.ora ($ORACLE_HOME/dbs)

Copy the initSID.ora file to the new location.

Change the file name from initSID.ora to initNEWSID.ora

Edit the contents of the pfile with the new database name and file locations.

Shutdown database

SQL> SHUTDOWN IMMEDIATE;

Copy files

Copy all the identified log files, data files and pfile to the new locations

$ cd /u02/oradata/ORAEMG/system01.dbf

$ scp system01.dbf oracle@server2:/u02/oradata/ORAEMG/system01.dbf

Note: This assumes that the database software has been installed on the new server.

Create new database

On the new server

SQL> STARTUP NOMOUNT PFILE=$ORACLE_HOME/dbs/initNEWSID.ora

SQL> @ccf_NEWSID.sql

SQL> ALTER DATABASE OPEN RESETLOGS;

Create new password file

If necessary create the new password file

$ orapwd file=’$ORACLE_HOME/dbs/orapwNEWSID’

Change Database ID

If the database backups will use a common RMAN repository database, there cannot be two databases with the same DBID. Change the DBID using the DBNEWID utility

SQL> shutdown immediate;

SQL> startup mount;

$ nid TARGET=SYS PDB=ALL

If the utility is successful it will shutdown the database.

SQL> STARTUP MOUNT

SQL> ALTER DATABASE OPEN RESETLOGS;