203
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:UCASNEWUCASNEW , H:UCASNEWORADATA_2 | G:UCASNEWORADATA_1 , H:UCASNEWORADATA_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:Usersjkhan>
C:Usersjkhan>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_Backupbackup_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:UCASNEWORADATA_2UCAS_01.DBF
input datafile file number=00003 name=G:UCASNEWUCASNEWUNDOTBS01.DBF
input datafile file number=00012 name=H:UCASNEWORADATA_2UCAS_INDEX_02.DBF
input datafile file number=00007 name=H:UCASNEWORADATA_2UCAS_INDEX_01.DBF
input datafile file number=00009 name=H:UCASNEWORADATA_2TELEX_01.DBF
input datafile file number=00001 name=G:UCASNEWUCASNEWSYSTEM01.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:UCASNEWORADATA_2UCAS_02.DBF
input datafile file number=00006 name=H:UCASNEWORADATA_2UCAS_DATA_01.DBF
input datafile file number=00011 name=H:UCASNEWORADATA_2UCAS_DATA_02.DBF
input datafile file number=00002 name=G:UCASNEWUCASNEWSYSAUX01.DBF
input datafile file number=00008 name=H:UCASNEWORADATA_2GGS_DATA_01.DBF
input datafile file number=00004 name=G:UCASNEWUCASNEWUSERS01.DBF
channel ch2: starting piece 1 at 26-APR-22
channel ch1: finished piece 1 at 26-APR-22
piece handle=F:UCAS_BACKUPBACKUP_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_BACKUPBACKUP_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_BACKUPBACKUP_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_BACKUPBACKUP_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_Backupbackup_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_BACKUPBACKUP_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_Backupbackup_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_BACKUPBACKUP_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>
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:UCASNEWUCASNEW','/data01/oradata_2/','H:UCASNEWORADATA_2','/data02/oradata_2/'
*.log_file_name_convert='G:UCASNEWORADATA_1','/data01/oradata_1/','H:UCASNEWORADATA_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:UCASNEWUCASNEWTEMP01.DBF SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- H:UCASNEWORADATA_1STDBY_REDO08.LOG H:UCASNEWORADATA_1STDBY_REDO09.LOG G:UCASNEWORADATA_1STDBY_REDO10.LOG H:UCASNEWORADATA_1REDO04.LOG H:UCASNEWORADATA_1REDO05.LOG G:UCASNEWORADATA_1REDO06.LOG G:UCASNEWORADATA_1REDO07.LOG G:UCASNEWORADATA_1STDBY_REDO11.LOG G:UCASNEWORADATA_1STDBY_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!
