Steps to Clone Oracle Database from Windows to Oracle Linux

In the previous blog, we saw Steps to create a Heterogeneous Dataguard between Windows and Linux Click here to read more. In this blog, we are going to perform a clone of Windows Oracle DB by taking RMAN backup and restoring it to Oracle Linux Server.

To view, the full log the below demo Click here.

Below are the high-level steps we will follow to configure Clone
1. Take Source DB backup from Window Server
2. Transfer backups to Linux Server
3. Prepare Oracle Instance in Linux
4. Initiate restore command in Linux server
5. Verify Clone DB
6. Re-create controlfile
7. Perform manual recovery

Below are setup details and the same will be used in this demonstration.

OS	Hostname	Datafile Location	Logfile Location	Role	DB Name
1	test-machine01	G:\UCASNEW\UCASNEW\ , H:\UCASNEW\ORADATA_2\	G:\UCASNEW\ORADATA_1\ , H:\UCASNEW\ORADATA_1\	Source	ucas
2	test-machine02	/data01/oradata_2/ , /data02/oradata_2/	/data01/oradata_1/ , /data02/oradata_1/	Clone	ucas

Step 1. Take Source DB backup from Window Server: Take RMAN Full backup of source windows database including datafiles, archivelog files, and controlfiles.

C:\Users\jkhan>
C:\Users\jkhan>rman target sys/ucas123

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 27 10:12:28 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: UCAS (DBID=2026517590)

RMAN>

RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> backup
5> tag 'BACKUP'
6> format 'F:\UCAS_Backup\backup_db_%d_S_%s_P_%p_T_%t'
7> as compressed backupset database;
8> release channel ch1;
9> release channel ch2;
10> sql "alter system switch logfile";
11> sql "alter system switch logfile";
12> sql "alter system switch logfile";
13> }

allocated channel: ch1
channel ch1: SID=162 device type=DISK

allocated channel: ch2
channel ch2: SID=204 device type=DISK

Starting backup at 26-APR-22
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00005 name=H:\UCASNEW\ORADATA_2\UCAS_01.DBF
input datafile file number=00003 name=G:\UCASNEW\UCASNEW\UNDOTBS01.DBF
input datafile file number=00012 name=H:\UCASNEW\ORADATA_2\UCAS_INDEX_02.DBF
input datafile file number=00007 name=H:\UCASNEW\ORADATA_2\UCAS_INDEX_01.DBF
input datafile file number=00009 name=H:\UCASNEW\ORADATA_2\TELEX_01.DBF
input datafile file number=00001 name=G:\UCASNEW\UCASNEW\SYSTEM01.DBF
channel ch1: starting piece 1 at 26-APR-22
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00010 name=H:\UCASNEW\ORADATA_2\UCAS_02.DBF
input datafile file number=00006 name=H:\UCASNEW\ORADATA_2\UCAS_DATA_01.DBF
input datafile file number=00011 name=H:\UCASNEW\ORADATA_2\UCAS_DATA_02.DBF
input datafile file number=00002 name=G:\UCASNEW\UCASNEW\SYSAUX01.DBF
input datafile file number=00008 name=H:\UCASNEW\ORADATA_2\GGS_DATA_01.DBF
input datafile file number=00004 name=G:\UCASNEW\UCASNEW\USERS01.DBF
channel ch2: starting piece 1 at 26-APR-22
channel ch1: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_DB_UCAS_S_8869_P_1_T_1103037787 tag=BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:04:25
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 26-APR-22
channel ch1: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_DB_UCAS_S_8871_P_1_T_1103038052 tag=BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 26-APR-22
channel ch1: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_DB_UCAS_S_8872_P_1_T_1103038055 tag=BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_DB_UCAS_S_8870_P_1_T_1103037787 tag=BACKUP comment=NONE
channel ch2: backup set complete, elapsed time: 00:07:03
Finished backup at 26-APR-22

released channel: ch1

released channel: ch2

sql statement: alter system switch logfile

sql statement: alter system switch logfile

sql statement: alter system switch logfile

RMAN>

RMAN> backup tag 'ARCHIVE BACKUP' as compressed backupset archivelog from sequence 11879 until sequence 11882 format 'F:\UCAS_Backup\backup_archive_db_%d_S_%
s_P_%p_T_%t' ;

Starting backup at 26-APR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11879 RECID=24439 STAMP=1103038211
input archived log thread=1 sequence=11880 RECID=24440 STAMP=1103038211
input archived log thread=1 sequence=11881 RECID=24442 STAMP=1103038212
channel ORA_DISK_1: starting piece 1 at 26-APR-22
channel ORA_DISK_1: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_ARCHIVE_DB_UCAS_S_8873_P_1_T_1103038440 tag=ARCHIVE BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-APR-22

RMAN> backup tag 'CONTROL FILE BACKUP' as compressed backupset  current controlfile  format 'F:\UCAS_Backup\backup_db_%d_S_%s_P_%p_T_%t';

Starting backup at 26-APR-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-APR-22
channel ORA_DISK_1: finished piece 1 at 26-APR-22
piece handle=F:\UCAS_BACKUP\BACKUP_DB_UCAS_S_8874_P_1_T_1103038450 tag=CONTROL FILE BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-APR-22

RMAN>
RMAN>

Step 2. Transfer backups to Linux Server: Once all backups are done. Transfer all backups to Linux Server. We are using here pscp.exe tool to transfer backup files from Windows to Linux Server. Please note we are transferring DB backups to the Linux Server location: /u01/db_pump.

F:\UCAS_Backup>
F:\UCAS_Backup>
F:\UCAS_Backup>dir
 Volume in drive F is DB GG Soft
 Volume Serial Number is 089F-8206

 Directory of F:\UCAS_Backup

04/26/2022  03:34 PM        25,405,952 BACKUP_ARCHIVE_DB_UCAS_S_8873_P_1_T_1103038440
04/26/2022  03:27 PM     2,159,509,504 BACKUP_DB_UCAS_S_8869_P_1_T_1103037787
04/26/2022  03:30 PM     3,949,846,528 BACKUP_DB_UCAS_S_8870_P_1_T_1103037787
04/26/2022  03:27 PM         1,490,944 BACKUP_DB_UCAS_S_8871_P_1_T_1103038052
04/26/2022  03:27 PM            98,304 BACKUP_DB_UCAS_S_8872_P_1_T_1103038055
04/26/2022  03:34 PM         1,490,944 BACKUP_DB_UCAS_S_8874_P_1_T_1103038450
01/14/2019  06:40 PM           626,744 pscp.exe
              13 File(s)  6,138,484,330 bytes
               2 Dir(s)  181,670,490,112 bytes free

F:\UCAS_Backup>
F:\UCAS_Backup>pscp.exe BACKUP* oracle@test-machine02:/u01/db_pump
oracle@meducas2db1's password:
BACKUP_ARCHIVE_DB_UCAS_S_ | 478181 kB | 469.3 kB/s | ETA: 00:00:00 | 100%
BACKUP_ARCHIVE_DB_UCAS_S_ | 504663 kB | 452.2 kB/s | ETA: 00:00:00 | 100%
BACKUP_ARCHIVE_DB_UCAS_S_ | 386400 kB | 517.3 kB/s | ETA: 00:00:00 | 100%
BACKUP_ARCHIVE_DB_UCAS_S_ | 179536 kB | 473.7 kB/s | ETA: 00:00:00 | 100%
BACKUP_ARCHIVE_DB_UCAS_S_ | 30 kB |  30.5 kB/s | ETA: 00:00:00 | 100%
ARCHIVE_DB_UCAS_S_ | 2338552 kB | 450.5 kB/s | ETA: 00:00:00 | 100%
F:\UCAS_Backup>

Click here to get more on Step by Step Oracle 19c Binary Installation On Linux.

Step 3. Prepare Oracle Instance in Linux: Create clone DB init parameter file, password file, and static listener configuration. When you are going to use RMAN duplicate command to perform a clone. RMAN duplicate command requires static listener configuration in a “listener.ora” file. Make sure you set proper log_file_name_convert & db_file_name_convert parameter.

[oracle@test-machine02 dbs]$ 
[oracle@test-machine02 dbs]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@test-machine02 dbs]$ cat initucas.ora
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/ucas/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=27
*.control_files='/data01/oradata_1/control01.ctl','/data02/oradata_1/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='G:\UCASNEW\UCASNEW\','/data01/oradata_2/','H:\UCASNEW\ORADATA_2\','/data02/oradata_2/'
*.log_file_name_convert='G:\UCASNEW\ORADATA_1\','/data01/oradata_1/','H:\UCASNEW\ORADATA_1\','/data02/oradata_1/'
*.db_flashback_retention_target=10800
*.db_name='ucas'
*.db_recovery_file_dest='/backup/fast_recovery_area'
*.db_recovery_file_dest_size=171798691840
*.db_unique_name='ucasdr2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ucasdrXDB)'
*.enable_goldengate_replication=TRUE
*.fast_start_mttr_target=180
*.log_archive_config='DG_CONFIG=(ucas,ucasdr)'
*.log_archive_dest_1='LOCATION=/backup/fast_recovery_area/ucas/archivelog'
*.DB_UNIQUE_NAME=ucas'
*.log_archive_format='log_%s_%t_%r.arc'
*.log_archive_max_processes=5
*.log_archive_min_succeed_dest=1
*.log_checkpoints_to_alert=TRUE
*.open_cursors=500
*.optimizer_dynamic_sampling=2
*.pga_aggregate_target=4194304000
*.processes=500
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=0
*.result_cache_mode='MANUAL'
*.session_cached_cursors=100
*.sessions=500
*.sga_max_size=10485760000
*.sga_target=10502537216
*.standby_file_management='AUTO'
*.streams_pool_size=536870912
*.undo_retention=86400
*.undo_tablespace='UNDOTBS1'
#event="10294 trace name context forever, level 1"
#event="10298 trace name context forever, level 3"
#event="logon trace name KRB_TRACE level 15"
[oracle@test-machine02 dbs]$

[oracle@test-machine02 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@test-machine02 admin]$
[oracle@test-machine02 admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (SID_NAME = ucas)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
   )
 )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
[oracle@test-machine02 admin]$

[oracle@test-machine02 ~]$ cat /etc/oratab
ucas:/u01/app/oracle/product/11.2.0.4/db_1:Y
[oracle@test-machine02 ~]$

[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ cd $ORACLE_HOME/dbs
[oracle@test-machine02 dbs]$
[oracle@test-machine02 dbs]$ orapwd file=orapwucas password=pass123 entries=5 ignorecase=Y
[oracle@test-machine02 dbs]$

[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ . oraenv
ORACLE_SID = [oracle] ? ucas
The Oracle base has been set to /u01/app/oracle
[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 31 14:56:28 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            2248149664 bytes
Database Buffers         8187281408 bytes
Redo Buffers               17948672 bytes
SQL>

Step 4. Initiate restore command in Linux server: Connect to clone instance as auxiliary and initiate duplicate database command as below. Please note all your backup files are located in ‘/u01/db_pump‘.

[oracle@test-machine02 ~]$
[oracle@test-machine02 ~]$ rman auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 26 17:27:45 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: UCAS (not mounted)

RMAN> 
RMAN> run{
allocate auxiliary channel prmy1 type disk;
allocate auxiliary channel prmy2 type disk;
allocate auxiliary channel prmy3 type disk;
DUPLICATE DATABASE TO UCAS BACKUP LOCATION '/u01/db_pump' NOFILENAMECHECK;
}2> 3> 4> 5> 6>

allocated channel: prmy1
channel prmy1: SID=197 device type=DISK

allocated channel: prmy2
channel prmy2: SID=391 device type=DISK

allocated channel: prmy3
channel prmy3: SID=586 device type=DISK

Starting Duplicate Db at 26-APR-22

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   10455642112 bytes

Fixed Size                     2262368 bytes
Variable Size               2281704096 bytes
Database Buffers            8153726976 bytes
Redo Buffers                  17948672 bytes
allocated channel: prmy1
channel prmy1: SID=197 device type=DISK
allocated channel: prmy2
channel prmy2: SID=391 device type=DISK
allocated channel: prmy3
channel prmy3: SID=585 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''UCAS'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/db_pump/BACKUP_DB_UCAS_S_8874_P_1_T_1103038450';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''UCAS'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area   10455642112 bytes

Fixed Size                     2262368 bytes
Variable Size               2281704096 bytes
Database Buffers            8153726976 bytes
Redo Buffers                  17948672 bytes
allocated channel: prmy1
channel prmy1: SID=197 device type=DISK
allocated channel: prmy2
channel prmy2: SID=391 device type=DISK
allocated channel: prmy3
channel prmy3: SID=585 device type=DISK

Starting restore at 26-APR-22

channel prmy2: skipped, AUTOBACKUP already found
channel prmy3: skipped, AUTOBACKUP already found
channel prmy1: restoring control file
channel prmy1: restore complete, elapsed time: 00:00:08
output file name=/data01/oradata_1/control01.ctl
output file name=/data02/oradata_1/control02.ctl
Finished restore at 26-APR-22

database mounted

contents of Memory Script:
{
   set until scn  15011266961;
   set newname for datafile  1 to
 "/data01/oradata_2/SYSTEM01.DBF";
   set newname for datafile  2 to
 "/data01/oradata_2/SYSAUX01.DBF";
   set newname for datafile  3 to
 "/data01/oradata_2/UNDOTBS01.DBF";
   set newname for datafile  4 to
 "/data01/oradata_2/USERS01.DBF";
   set newname for datafile  5 to
 "/data02/oradata_2/UCAS_01.DBF";
   set newname for datafile  6 to
 "/data02/oradata_2/UCAS_DATA_01.DBF";
   set newname for datafile  7 to
 "/data02/oradata_2/UCAS_INDEX_01.DBF";
   set newname for datafile  8 to
 "/data02/oradata_2/GGS_DATA_01.DBF";
   set newname for datafile  9 to
 "/data02/oradata_2/TELEX_01.DBF";
   set newname for datafile  10 to
 "/data02/oradata_2/UCAS_02.DBF";
   set newname for datafile  11 to
 "/data02/oradata_2/UCAS_DATA_02.DBF";
   set newname for datafile  12 to
 "/data02/oradata_2/UCAS_INDEX_02.DBF";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-APR-22

channel prmy1: starting datafile backup set restore
channel prmy1: specifying datafile(s) to restore from backup set
channel prmy1: restoring datafile 00001 to /data01/oradata_2/SYSTEM01.DBF
channel prmy1: restoring datafile 00003 to /data01/oradata_2/UNDOTBS01.DBF
channel prmy1: restoring datafile 00005 to /data02/oradata_2/UCAS_01.DBF
channel prmy1: restoring datafile 00007 to /data02/oradata_2/UCAS_INDEX_01.DBF
channel prmy1: restoring datafile 00009 to /data02/oradata_2/TELEX_01.DBF
channel prmy1: restoring datafile 00012 to /data02/oradata_2/UCAS_INDEX_02.DBF
channel prmy1: reading from backup piece /u01/db_pump/BACKUP_DB_UCAS_S_8869_P_1_T_1103037787
channel prmy2: starting datafile backup set restore
channel prmy2: specifying datafile(s) to restore from backup set
channel prmy2: restoring datafile 00002 to /data01/oradata_2/SYSAUX01.DBF
channel prmy2: restoring datafile 00004 to /data01/oradata_2/USERS01.DBF
channel prmy2: restoring datafile 00006 to /data02/oradata_2/UCAS_DATA_01.DBF
channel prmy2: restoring datafile 00008 to /data02/oradata_2/GGS_DATA_01.DBF
channel prmy2: restoring datafile 00010 to /data02/oradata_2/UCAS_02.DBF
channel prmy2: restoring datafile 00011 to /data02/oradata_2/UCAS_DATA_02.DBF
channel prmy2: reading from backup piece /u01/db_pump/BACKUP_DB_UCAS_S_8870_P_1_T_1103037787

channel prmy1: piece handle=/u01/db_pump/BACKUP_DB_UCAS_S_8869_P_1_T_1103037787 tag=BACKUP
channel prmy1: restored backup piece 1
channel prmy1: restore complete, elapsed time: 00:06:45

channel prmy2: piece handle=/u01/db_pump/BACKUP_DB_UCAS_S_8870_P_1_T_1103037787 tag=BACKUP
channel prmy2: restored backup piece 1
channel prmy2: restore complete, elapsed time: 00:10:45
Finished restore at 26-APR-22

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=1103046028 file name=/data01/oradata_2/SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1103046028 file name=/data01/oradata_2/SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1103046028 file name=/data01/oradata_2/UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1103046028 file name=/data01/oradata_2/USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1103046028 file name=/data02/oradata_2/UCAS_01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=1103046028 file name=/data02/oradata_2/UCAS_DATA_01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=1103046029 file name=/data02/oradata_2/UCAS_INDEX_01.DBF
datafile 8 switched to datafile copy
input datafile copy RECID=20 STAMP=1103046029 file name=/data02/oradata_2/GGS_DATA_01.DBF
datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=1103046029 file name=/data02/oradata_2/TELEX_01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=22 STAMP=1103046029 file name=/data02/oradata_2/UCAS_02.DBF
datafile 11 switched to datafile copy
input datafile copy RECID=23 STAMP=1103046029 file name=/data02/oradata_2/UCAS_DATA_02.DBF
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=1103046029 file name=/data02/oradata_2/UCAS_INDEX_02.DBF

contents of Memory Script:
{
   set until scn  15011266961;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-APR-22

starting media recovery

channel prmy1: starting archived log restore to default destination
channel prmy1: restoring archived log
archived log thread=1 sequence=11879
channel prmy1: restoring archived log
archived log thread=1 sequence=11880
channel prmy1: restoring archived log
archived log thread=1 sequence=11881
channel prmy1: reading from backup piece /u01/db_pump/ARCHIVE_DB_UCAS_S_8873_P_1_T_1103038440
channel prmy1: piece handle=/u01/db_pump/ARCHIVE_DB_UCAS_S_8873_P_1_T_1103038440 tag=ARCHIVE BACKUP
channel prmy1: restored backup piece 1
channel prmy1: restore complete, elapsed time: 00:00:07
archived log file name=/backup/fast_recovery_area/ucas/archivelog/log_11879_1_1019388628.arc thread=1 sequence=11879
released channel: prmy1
released channel: prmy2
released channel: prmy3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/26/2022 17:40:38
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/backup/fast_recovery_area/ucas/archivelog/log_11879_1_1019388628.arc'
ORA-10562: Error occurred while applying redo to data block (file# 11, block# 620515)
ORA-10564: tablespace UCAS_DATA
ORA-01110: data file 11: '/data02/oradata_2/UCAS_DATA_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 167162
ORA-00600: internal error code, arguments: [ktbrcl:CDLC not in CR], [44], [], [], [], [], [], [], [], [], [], []

RMAN>
RMAN>
RMAN> exit

RMAN command failed at recovery with ORA-00600. From the above output you will notice restore was successful but during the recovery, the process job failed. Below is Oracle Support Note.

Redo application is not supported between Linux and Windows except with a standby database. This means that the backup must be a cold (consistent) backup, which requires no redo application. If redo apply is required to recover the database on the new platform it will fail. Using consistent (cold) backup method should be used for duplicating cross-platform.

Step 5. Verify Clone DB: At this stage, we are unable to open DB with resetlogs option due to the error “ORA-19838: Cannot use this control file to open database” as controlfile type is BACKUP.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19838: Cannot use this control file to open database

SQL>
SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
BACKUP


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata_2/SYSTEM01.DBF
/data01/oradata_2/SYSAUX01.DBF
/data01/oradata_2/UNDOTBS01.DBF
/data01/oradata_2/USERS01.DBF
/data02/oradata_2/UCAS_01.DBF
/data02/oradata_2/UCAS_DATA_01.DBF
/data02/oradata_2/UCAS_INDEX_01.DBF
/data02/oradata_2/GGS_DATA_01.DBF
/data02/oradata_2/TELEX_01.DBF
/data02/oradata_2/UCAS_02.DBF
/data02/oradata_2/UCAS_DATA_02.DBF

NAME
--------------------------------------------------------------------------------
/data02/oradata_2/UCAS_INDEX_02.DBF

12 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
G:\UCASNEW\UCASNEW\TEMP01.DBF

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
H:\UCASNEW\ORADATA_1\STDBY_REDO08.LOG
H:\UCASNEW\ORADATA_1\STDBY_REDO09.LOG
G:\UCASNEW\ORADATA_1\STDBY_REDO10.LOG
H:\UCASNEW\ORADATA_1\REDO04.LOG
H:\UCASNEW\ORADATA_1\REDO05.LOG
G:\UCASNEW\ORADATA_1\REDO06.LOG
G:\UCASNEW\ORADATA_1\REDO07.LOG
G:\UCASNEW\ORADATA_1\STDBY_REDO11.LOG
G:\UCASNEW\ORADATA_1\STDBY_REDO12.LOG

9 rows selected.
SQL>

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-19838: Cannot use this control file to open database


SQL>

Step 6. Recreate Control File: Recreate controlfile with the below command and update redofile location. After controlfile is created verify contorlfile type.

SQL>
SQL> alter database backup controlfile to trace as '/u01/db_pump/ucas_ctl.trc';

Database altered.

SQL>

[oracle@test-machine02 db_pump]$ 
[oracle@test-machine02 db_pump]$ vi ucas_ctl_main.trc
:wq!
[oracle@test-machine02 db_pump]$
[oracle@test-machine02 db_pump]$ cat ucas_ctl_main.trc
CREATE CONTROLFILE REUSE DATABASE "UCAS" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 4 '/data01/oradata_1/redo04.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 5 '/data01/oradata_1/redo05.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 6 '/data02/oradata_1/redo06.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 7 '/data02/oradata_1/redo07.log'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data01/oradata_2/SYSTEM01.DBF',
  '/data01/oradata_2/SYSAUX01.DBF',
  '/data01/oradata_2/UNDOTBS01.DBF',
  '/data01/oradata_2/USERS01.DBF',
  '/data02/oradata_2/UCAS_01.DBF',
  '/data02/oradata_2/UCAS_DATA_01.DBF',
  '/data02/oradata_2/UCAS_INDEX_01.DBF',
  '/data02/oradata_2/GGS_DATA_01.DBF',
  '/data02/oradata_2/TELEX_01.DBF',
  '/data02/oradata_2/UCAS_02.DBF',
  '/data02/oradata_2/UCAS_DATA_02.DBF',
  '/data02/oradata_2/UCAS_INDEX_02.DBF'
CHARACTER SET AL32UTF8
;
[oracle@meducas2db1 db_pump]$


SQL>
SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            2248149664 bytes
Database Buffers         8187281408 bytes
Redo Buffers               17948672 bytes
SQL>
SQL> @/u01/db_pump/ucas_ctl_main.trc

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CREATED

SQL>

Step 7. Perform Manual Recovery: Even manual recovery is failing with same ORA-00600 error. Since there is no way to perform recovery we will use Oracle undocumented parameter “_allow_resetlogs_corruption” to open the database. To use this parameter we need to set undo_management parameter to manual.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 10 needs more recovery to be consistent
ORA-01110: data file 10: '/data02/oradata_2/UCAS_02.DBF'
SQL>

SQL> recover database using backup controlfile;
ORA-00279: change 15011266413 generated at  needed for thread 1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/backup/fast_recovery_area/ucas/archivelog/log_11879_1_1019388628.arc
ORA-10562: Error occurred while applying redo to data block (file# 11, block#
606042)
ORA-10564: tablespace UCAS_DATA
ORA-01110: data file 11: '/data02/oradata_2/UCAS_DATA_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 88003
ORA-00600: internal error code, arguments: [4502], [0], [], [], [], [], [], [],
[], [], [], []


ORA-01112: media recovery not started

SQL>  alter system set undo_management=manual scope=spfile;
 alter system set undo_management=manual scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use


SQL> show parameter spfile;

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


SQL>
SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> create spfile from pfile;

File created.

SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            2248149664 bytes
Database Buffers         8187281408 bytes
Redo Buffers               17948672 bytes
Database mounted.
SQL>  alter system set undo_management=manual scope=spfile;

System altered.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

System altered.

SQL>


SQL>  ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
 ALTER DATABASE RECOVER DATABASE UNTIL CANCEL
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL>  ALTER DATABASE RECOVER CANCEL;
 ALTER DATABASE RECOVER CANCEL
*
ERROR at line 1:
ORA-01112: media recovery not started


SQL>


SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup force;
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            2248149664 bytes
Database Buffers         8187281408 bytes
Redo Buffers               17948672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;


Database altered.

SQL> SQL>
SQL>

Once DB is open revert the below parameters to default.

SQL>
SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management                      string                           MANUAL
undo_retention                       integer                          86400
undo_tablespace                      string                           UNDOTBS1
SQL>
SQL>
SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> show parameter "_allow_resetlogs_corruption"

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_allow_resetlogs_corruption          boolean                          TRUE
SQL>
SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= FALSE SCOPE = SPFILE;

System altered.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1.0456E+10 bytes
Fixed Size                  2262368 bytes
Variable Size            2248149664 bytes
Database Buffers         8187281408 bytes
Redo Buffers               17948672 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management                      string                           AUTO
undo_retention                       integer                          86400
undo_tablespace                      string                           UNDOTBS1
SQL> show parameter "_allow_resetlogs_corruption"

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
_allow_resetlogs_corruption          boolean                          FALSE
SQL>

This document is just for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

CLONE, DATABASE, MIGRATION, ORA-ERRORS, ORACLE, ORACLE 19C

DBsGuru