User Managed Recovery - DB Cloning


CLONING DATABASE TO OTHER NAME

Cloning, in the context of computing and technology, refers to the process of creating an exact duplicate or replica of an object, system, or data. Cloning can be applied to various types of entities, including hardware, software, and data.


Steps for Database Cloning using Hot Backup :
  • Backup the parameter file

If SOURCE database is using spfile create pfile or if database is using pfile, use OS command to copy the pfile to a backup location.

  • place the database to backup mode
  • Copy all data files of "Source" database to a clone location.
  • After copying all datafiles, release the database from backup mode.
  • Switch the current log file and note down the oldest log sequence number
  • Take the control file trace backup to the trace path
  • Edit the clone database parameter file and make necessary changes to the clone
  • Startup the cloned database in NOMOUNT mode, using target pfile.
  • Create the control file for the clone database using the trace control file.
  • Create the control file by running trace file from the trace path
  • Recover the database using backup controlfile option.
  • Open the database with resetlogs option

Hot Backup :

Hot Backups can be performed when the database is up and in Archive log mode.

  • First, check the v$database view to see if your database is set in Archive log mode:
	SQL> select name,dbid,open_mode,log_mode from v$database;

	NAME		DBID		OPEN_MODE	LOG_MODE
	------		---------	------------	----------
	DEV		4176617848	READ WRITE	ARCHIVELOG

	SQL> archive log list

	Database log mode	       Archive Mode
	Automatic archival	       Enabled

	Archive destination	       USE_DB_RECOVERY_FILE_DEST
	Oldest online log sequence     1
	Next log sequence to archive   2
	Current log sequence	       2
To Check dev database files 
	SQL> show parameter spfile
	NAME	          TYPE	 VALUE
	--------------- ----------- ----------------------------
	Spfile         string	 /u01/app/oracle/product/12.2.0 /
				dbhome_1/dbs/spfiledev.ora
Check number of controlfile associated with database 
	SQL>  select name from v$controlfile ;
	NAME
	-----------------------------------------------------------
	/u01/app/oracle/oradata/dev/control01.ctl
	/u01/app/oracle/fast_recovery_area/dev/control02.ctl
Check number of tablespaces associated with database 
	SQL> select name from v$datafile;
	NAME
	-----------------------------------------------------------
	/u01/app/oracle/oradata/dev/system01.dbf
	/u01/app/oracle/oradata/dev/sysaux01.dbf
	/u01/app/oracle/oradata/dev/undotbs01.dbf
	/u01/app/oracle/oradata/dev/users01.dbf
Check number of logfile member associated with database: 
	SQL> select member from v$logfile;
	MEMBER
	-----------------------------------------------------------
	/u01/app/oracle/oradata/dev/redo03.log
	/u01/app/oracle/oradata/dev/redo02.log
	/u01/app/oracle/oradata/dev/redo01.log
Check status for log group 
	SQL> select group#,status from v$log;
		GROUP# STATUS
	---------- ----------------
		 1 INACTIVE
		 2 CURRENT
		 3 UNUSED
Ensure the tablespaces are ready for taking hot backup 
	SQL> select * from v$backup;
		 FILE# STATUS		 CHANGE# TIME	       CON_ID
	---------- ------------------ ---------- --------- ----------
		 1 NOT ACTIVE		       0		    0
		 3 NOT ACTIVE		       0		    0
		 4 NOT ACTIVE		       0		    0
		 7 NOT ACTIVE		       0		    0
Put database in hot backup mode 
	SQL> Alter database begin backup;
	Database altered.
	SQL> select * from v$backup;
		 FILE# STATUS		 CHANGE# TIME	       CON_ID
	---------- ------------------ ---------- --------- ----------
		 1 ACTIVE		 1526688 30-OCT-23	    0
		 3 ACTIVE		 1526688 30-OCT-23	    0
		 4 ACTIVE		 1526688 30-OCT-23	    0
		 7 ACTIVE		 1526688 30-OCT-23	    0
Create appropriate directory structure in clone database 
	[oracle@sdbt ~]$ mkdir tesdb_clone
	[oracle@sdbt ~]$ cd tesdb_clone/

	[oracle@sdbt tesdb_clone]$ pwd
	/home/oracle/tesdb_clone

	[oracle@sdbt tesdb_clone]$ mkdir admin oradata

	[oracle@sdbt tesdb_clone]$ cd admin/

	[oracle@sdbt admin]$ pwd
	/home/oracle/tesdb_clone/admin

	[oracle@sdbt admin]$ mkdir adump diag pfile

	[oracle@sdbt admin]$ ll
	drwxr-xr-x. 2 oracle oinstall 6 Oct 30 10:42 adump
	drwxr-xr-x. 2 oracle oinstall 6 Oct 30 10:42 diag
	drwxr-xr-x. 2 oracle oinstall 6 Oct 30 10:42 pfile

	[oracle@sdbt admin]$ cd ../
	[oracle@sdbt tesdb_clone]$ cd oradata/

	[oracle@sdbt oradata]$ ll

	[oracle@sdbt oradata]$ mkdir control data log arch

	[oracle@sdbt oradata]$ cd

	[oracle@sdbt ~]$ cd /u01/app/oracle/fast_recovery_area/

	[oracle@sdbt fast_recovery_area]$ ll

	drwxr-x---. 3 oracle oinstall 38 Mar  2  2022 dev
	[oracle@sdbt fast_recovery_area]$ mkdir tesdb_clone
	[oracle@sdbt fast_recovery_area]$ ll

	drwxr-x---. 3 oracle oinstall 38 Mar  2  2022 dev
	drwxr-xr-x. 2 oracle oinstall  6 Oct 30 10:43 tesdb_clone
	[oracle@sdbt fast_recovery_area]$ cd
	tesdb_clone/
	+-- admin
	*   +-- adump
	*   +-- diag
	*   +-- pfile
	+-- oradata
		+-- arch
		+-- control
		+-- data
		+-- log
Copy the tablespaces from source to target location 
	[oracle@sdbt dev]$ pwd
	/u01/app/oracle/oradata/dev

	[oracle@sdbt dev]$ cp *.dbf /home/oracle/tesdb_clone/oradata/data/
	[oracle@sdbt dev]$ cd /home/oracle/tesdb_clone/oradata/data/

	[oracle@sdbt data]$ ll
	total 1369240
	-rw-r-----. 1 oracle oinstall 482353152 Oct 30 10:45 sysaux01.dbf
	-rw-r-----. 1 oracle oinstall 838868992 Oct 30 10:45 system01.dbf
	-rw-r-----. 1 oracle oinstall  33562624 Oct 30 10:45 temp01.dbf
	-rw-r-----. 1 oracle oinstall  73408512 Oct 30 10:45 undotbs01.dbf
	-rw-r-----. 1 oracle oinstall   5251072 Oct 30 10:45 users01.dbf
* After copying datafiles to backup location,release database from Hot backup mode. 
	SQL>alter database end backup;
	SQL>select * from v$backup;
		 FILE# STATUS		 CHANGE# TIME	       CON_ID
	---------- ------------------ ---------- --------- ----------
		 1 NOT ACTIVE		 1526688 30-OCT-23	    0
		 3 NOT ACTIVE		 1526688 30-OCT-23	    0
		 4 NOT ACTIVE		 1526688 30-OCT-23	    0
		 7 NOT ACTIVE		 1526688 30-OCT-23	    0
* Backup the controlfile as trace. 
	SQL> alter database backup controlfile to trace as
	'/home/oracle/tesdb_clone/oradata/control/clonebkp.sql';
	Database altered.

	[oracle@sdbt pfile]$ cd /home/oracle/tesdb_clone/oradata/control

	[oracle@sdbt control]$ ll
	total 10676
	-rw-r--r--. 1 oracle oinstall      704 Oct 30 10:57 clonebkp.sql
* Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles
Note:
Change the "REUSE" parameter to "SET" and RESETLOGS option. 

	CREATE CONTROLFILE SET DATABASE "TESDB" RESETLOGS  ARCHIVELOG
		MAXLOGFILES 16
		MAXLOGMEMBERS 3
		MAXDATAFILES 100
		MAXINSTANCES 8
		MAXLOGHISTORY 292

	LOGFILE
	  GROUP 1 '/home/oracle/tesdb_clone/oradata/log/redo01.log'
	  SIZE 200M BLOCKSIZE 512,

	  GROUP 2 '/home/oracle/tesdb_clone/oradata/log/redo02.log'
	  SIZE 200M BLOCKSIZE 512,

	  GROUP 3 '/home/oracle/tesdb_clone/oradata/log/redo03.log'
	  SIZE 200M BLOCKSIZE 512

	-- STANDBY LOGFILE
	DATAFILE
	  '/home/oracle/tesdb_clone/oradata/data/system01.dbf',
	  '/home/oracle/tesdb_clone/oradata/data/sysaux01.dbf',
	  '/home/oracle/tesdb_clone/oradata/data/undotbs01.dbf',
	  '/home/oracle/tesdb_clone/oradata/data/users01.dbf'

	CHARACTER SET AL32UTF8;
* Create parameter file 
	SQL> show parameter spfile
	NAME	          TYPE	 VALUE
	--------------- ----------- ------------------------------
	Spfile         string	 /u01/app/oracle/product/12.2.0 /
					dbhome_1/dbs/spfiledev.ora

	SQL> create pfile='/home/oracle/tesdb_clone/
	admin/pfile/inittesdbclone.ora' from spfile;

	File created.
* Edit the clone database parameter file and make necessary changes to the HCLONE database. 
	[oracle@sdbt pfile]$ cat inittesdbclone.ora
	*.audit_file_dest='/home/oracle/tesdb_clone/admin/adump'
	*.audit_trail='db'
	*.compatible='12.2.0'
	*.control_files='/home/oracle/tesdb_clone/oradata/control/
	control01.ctl','/u01/app/oracle/fast_recovery_area/
	tesdb_clone/control02.ctl'

	*.db_block_size=8192
	*.db_name='TESDB'
	*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/
	tesdb_clone'

	*.db_recovery_file_dest_size=8016m
	*.diagnostic_dest='/u01/app/oracle'
	*.dispatchers='(PROTOCOL=TCP) (SERVICE=devXDB)'
	*.log_archive_format='%t_%s_%r.dbf'
	*.sga_target=678m
	*.nls_language='AMERICAN'
	*.nls_territory='AMERICA'
	*.open_cursors=300
	*.processes=300
	*.remote_login_passwordfile='EXCLUSIVE'
	*.undo_tablespace='UNDOTBS1'
Startup database in NOMOUNT stage using target PFILE 
	 [oracle@sdbt control]$ export ORACLE_SID=TESDB
	[oracle@sdbt control]$ sqlplus / as sysdba

	SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 30
	10:58:48 2023
	Copyright (c) 1982, 2016, Oracle.  All rights reserved.
	Connected to an idle instance.
SQL> startup nomount pfile= '/home/oracle/tesdb_clone/admin/pfile/inittesdbclone.ora' 
	ORACLE instance started.
	Total System Global Area  713031680 bytes
	Fixed Size		    8624792 bytes
	Variable Size		  310379880 bytes
	Database Buffers	  385875968 bytes
	Redo Buffers		    8151040 bytes

	SQL> @/home/oracle/tesdb_clone/oradata/control/clonebkp.sql;
	Control file created.

	SQL> select name,dbid,open_mode,log_mode from v$database;

	NAME	DBID		OPEN_MODE	LOG_MODE
	------	------		---------  	------------
	TESDB	4176617848	MOUNTED		ARCHIVELOG
* Recover the database using backup controlfile option 
	SQL> recover database using backup controlfile until cancel;

	ORA-00279: change 1526688 generated at 10/30/2023 10:44:26
	needed for thread 1

	ORA-00289: suggestion :
	/u01/app/oracle/fast_recovery_area/tesdb_clone/TESDB/
	archivelog/2023_10_30/o1_mf_1_2_%u_.arc

	ORA-00280: change 1526688 for thread 1 is in sequence #2

	Specify log: {< RET>=suggested | filename | AUTO | CANCEL}---
	(find the current log file in dev database and mention it here)

	/u01/app/oracle/oradata/dev/redo02.log
	Log applied.

	Media recovery complete.
	SQL> alter database open resetlogs;
	Database altered.

	SQL> create spfile from pfile=
	'/home/oracle/tesdb_clone/admin/pfile/inittesdbclone.ora';

	File created.
	SQL> show parameter spfile;

	NAME		TYPE	VALUE
	-------	-----------	-----------
	spfile		string

	SQL> select name from v$datafile;

	NAME
	----------------------------------------------------
	/home/oracle/tesdb_clone/oradata/data/system01.dbf
	/home/oracle/tesdb_clone/oradata/data/sysaux01.dbf
	/home/oracle/tesdb_clone/oradata/data/undotbs01.dbf
	/home/oracle/tesdb_clone/oradata/data/users01.dbf
	SQL>
* Check the database name and status : 
	SQL> select name,dbid,open_mode,log_mode from v$database;

	NAME		DBID OPEN_MODE		  LOG_MODE
	--------- ---------- -------------------- ------------
	TESDB	  4176617848 READ WRITE 	  ARCHIVELOG


(Logical Recovery - Schema Refresh)