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!