User Managed Recovery - DB CloningCLONING 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 :
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.
Hot Backup : Hot Backups can be performed when the database is up and 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 2To Check dev database files SQL> show parameter spfile NAME TYPE VALUE --------------- ----------- ---------------------------- Spfile string /u01/app/oracle/product/12.2.0 / dbhome_1/dbs/spfiledev.oraCheck 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.ctlCheck 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.dbfCheck 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.logCheck status for log group SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 CURRENT 3 UNUSEDEnsure 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 0Put 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 0Create 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 +-- logCopy 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 « Previous Next Topic » (Logical Recovery - Schema Refresh) |