RMAN heterogeneous migration

1. Environment Description:
Original machine: 192.168.102.156 Oracle 10.2.0.1
Target: 192.168.102.157 ORACLE 10.2.0.1
2. Create a table for the database.
SQL> create table rmantest (id number (9 ));
Table created.
SQL> insert into rmantest (id) values (88 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
3. Prepare an Instance name for the target database: the Instance name for this instance is the name of the original database. Do not set it at will because the control file contains DB_NAME records. If you set the Instance name at will, you cannot complete the migration.
[Oracle @ GOLDENGATE2 ~] $ Export ORACLE_SID = gg1
[Oracle @ GOLDENGATE2 ~] $ Echo $ ORACLE_SID
Gg1
4. Prepare the parameter file for the target database.
(1) create a PFILE file in the original database.
SQL> create pfile = '/home/oracle/initgg1.ora' from spfile;
File created.
(2) Move the prize parameter file to the target database.
[Oracle @ GOLDENGATE1 ~] $ Scp initgg1.oraoracle@192.168.102.157:/home/oracle
Oracle@192.168.102.157 'spassword:
Initgg1.ora 100% 1051 1.0KB/s
5. Create the directory required in the parameter file of the target database.
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p admin/gg1/adump
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p admin/gg1/bdump
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p admin/gg1/cdump
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p admin/gg1/dpdump
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p admin/gg1/udump
[Oracle @ GOLDENGATE2 ~] $ Mkdir flash_recovery_area
[Oracle @ GOLDENGATE2 ~] $ Mkdir-p oradata/gg1
[Oracle @ GOLDENGATE2 ~] $ Mkdir archivelog
6. Create a SPFILE file in the target database and start the database to NOMOUNT.
[Oracle @ GOLDENGATE2 ~] $ Export ORACLE_SID = gg1
[Oracle @ GOLDENGATE2 ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Fri Sep 21 01:21:51 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile = '/home/oracle/initgg1.ora ';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
 
7. Perform hot backup on the original database. Prerequisites: the database belongs to the archive mode.
[Oracle @ GOLDENGATE1 ~] $ Rman target/
Recovery Manager: Release 10.2.0.1.0-Production on Fri Sep 21 01:21:49 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to target database: GG1 (DBID = 1620494887)
RMAN> run {
Allocate channel a1 type disk;
Allocate channel a2 type disk;
Backup format = '/home/oracle/rman/full _ % d _ % T _ % s' database;
Backup format = '/home/oracle/rman/arch _ % d _ % T _ % s' archivelog all;
Backup format = '/home/oracle/rman/ctl _ % U' current controlfile;
Release channel a2;
Release channel a1;
} 2> 3> 4> 5> 6> 7> 8> 9>
Released channel: ORA_DISK_1
Allocated channel: a1
Channel a1: sid = 158 devtype = DISK
Allocated channel: a2
Channel a2: sid = 141 devtype = DISK
Starting backup at 21-SEP-12
Channel a1: starting full datafile backupset
Channel a1: specifying datafile (s) in backupset
Input datafile fno = 00001 name =/home/oracle/oradata/gg1/system01.dbf
Input datafile fno = 00005 name =/home/oracle/oradata/gg1/example01.dbf
Input datafile fno = 00003 name =/home/oracle/oradata/gg1/sysaux01.dbf
Channel a1: starting piece 1 at 21-SEP-12
Channel a2: starting full datafile backupset
Channel a2: specifying datafile (s) in backupset
Input datafile fno = 00006 name =/home/oracle/oradata/gg1/gg01.dbf
Input datafile fno = 00002 name =/home/oracle/oradata/gg1/undotbs01.dbf
Input datafile fno = 00004 name =/home/oracle/oradata/gg1/users01.dbf
Channel a2: starting piece 1 at 21-SEP-12
Channel a2: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/full_gg1_201209238510 tag = TAG20120921T013222 comment = NONE
Channel a2: backup set complete, elapsed time: 00:00:25
Channel a2: starting full datafile backupset
Channel a2: specifying datafile (s) in backupset
Including current control file in backupset
Channel a2: starting piece 1 at 21-SEP-12
Channel a2: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/full_gg1_201209238511 tag = TAG20120921T013222 comment = NONE
Channel a2: backup set complete, elapsed time: 00:00:03
Channel a2: starting full datafile backupset
Channel a2: specifying datafile (s) in backupset
Including current SPFILE in backupset
Channel a2: starting piece 1 at 21-SEP-12
Channel a2: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/full_gg1_201209231612 tag = TAG20120921T013222 comment = NONE
Channel a2: backup set complete, elapsed time: 00:00:02
Channel a1: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/full_gg1_20120923169 tag = TAG20120921T013222 comment = NONE
Channel a1: backup set complete, elapsed time: 00:00:48
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
Current log archived
Channel a1: starting archive log backupset
Channel a1: specifying archive log (s) in backup set
Input archive log thread = 1 sequence = 4 recid = 1 stamp = 794527523
Channel a1: starting piece 1 at 21-SEP-12
Channel a2: starting archive log backupset
Channel a2: specifying archive log (s) in backup set
Input archive log thread = 1 sequence = 5 recid = 2 stamp = 794538401
Input archive log thread = 1 sequence = 6 recid = 3 stamp = 794539337
Input archive log thread = 1 sequence = 7 recid = 4 stamp = 794539385
Channel a2: starting piece 1 at 21-SEP-12
Channel a2: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/arch_gg1_201209238513 tag = TAG20120921T013312 comment = NONE
Channel a2: backup set complete, elapsed time: 00:00:02
Channel a1: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/arch_gg1_201209238514 tag = TAG20120921T013312 comment = NONE
Channel a1: backup set complete, elapsed time: 00:00:03
Channel a2: starting archive log backupset
Channel a2: specifying archive log (s) in backup set
Input archive log thread = 1 sequence = 8 recid = 5 stamp = 794539992
Channel a2: starting piece 1 at 21-SEP-12
Channel a2: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/arch_gg1_201209238515 tag = TAG20120921T013312 comment = NONE
Channel a2: backup set complete, elapsed time: 00:00:02
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
Channel a1: starting full datafile backupset
Channel a1: specifying datafile (s) in backupset
Including current control file in backupset
Channel a1: starting piece 1 at 21-SEP-12
Channel a1: finished piece 1 at 21-SEP-12
Piece handle =/home/oracle/rman/ctl_0gnlndut_1_1 tag = TAG20120921T013317 comment = NONE
Channel a1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-SEP-12
Released channel: a2
Released channel: a1
8. Move the backup to the target machine.
[Oracle @ GOLDENGATE1 ~] $ Cd rman/
[Oracle @ GOLDENGATE1 rman] $ ls
Arch_GG1_20120921_13 arch_GG1_20120921_15 full_GG1_20120921_10 full_GG1_20120921_12
Arch_GG1_20120921_14 ctl_0gnlndut_1_1 full_GG1_20120921_11 full_gg1_20120923859
[Oracle @ GOLDENGATE1 rman] $ scp * oracle@192.168.102.157:/home/oracle/rman
Oracle@192.168.102.157 'spassword:
Arch_gg1_201209238513 100% 13 MB 12.8 MB/s
Arch_gg1_201209238514 100% 25 MB 12.5 MB/s
Arch_gg1_201209238515 100% 281KB 281.0KB/s
Ctl_0gnlndut_1_1 100% 6944KB 6.8 MB/s
Full_gg1_201209238510 100% 33 MB 8.2 MB/s
Full_gg1_201209238511 100% 6944KB 6.8 MB/s
Full_gg1_201209238512 100% 96KB 96.0KB/s
Full_gg1_20120923859 100% 561 MB 11.9 MB/s
9. Restore the control file on the target machine.
[Oracle @ GOLDENGATE2 rman] $ rman target/
Recovery Manager: Release 10.2.0.1.0-Production on Fri Sep 21 01:36:10 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to target database: gg1 (not mounted)
RMAN> restore controlfile from '/home/oracle/rman/ctl_0gnlndut_1_1 ';
Starting restore at 21-SEP-12
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 156 devtype = DISK
Channel ORA_DISK_1: restoring control file
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Output filename =/home/oracle/oradata/gg1/control01.ctl
Output filename =/home/oracle/oradata/gg1/control02.ctl
Output filename =/home/oracle/oradata/gg1/control03.ctl
Finished restore at 21-SEP-12
10. Start the database to the MOUNT status.
RMAN> alter database mount;
Database mounted
Released channel: ORA_DISK_1
11. Restore the database.
RMAN> restore database;
Starting restore at 21-SEP-12
Starting implicit crosscheck backup at 21-SEP-12
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 156 devtype = DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 21-SEP-12
Starting implicit crosscheck copy at 21-SEP-12
Using channel ORA_DISK_1
Finished implicit crosscheck copy at 21-SEP-12
Searching for all files in the recovery area
Cataloging files...
No files cataloged
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00002 to/home/oracle/oradata/gg1/undotbs01.dbf
Restoring datafile 00004 to/home/oracle/oradata/gg1/users01.dbf
Restoring datafile 00006 to/home/oracle/oradata/gg1/gg01.dbf
Channel ORA_DISK_1: reading from backup piece/home/oracle/rman/full_gg1_201209220.10
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/home/oracle/rman/full_gg1_201209238510 tag = TAG20120921T013222
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00001 to/home/oracle/oradata/gg1/system01.dbf
Restoring datafile 00003 to/home/oracle/oradata/gg1/sysaux01.dbf
Restoring datafile 00005 to/home/oracle/oradata/gg1/example01.dbf
Channel ORA_DISK_1: reading from backup piece/home/oracle/rman/full_gg1_20120923169
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/home/oracle/rman/full_gg1_20120923169 tag = TAG20120921T013222
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-SEP-12
RMAN> recover database;
Starting recover at 21-SEP-12
Using channel ORA_DISK_1
Starting media recovery
Channel ORA_DISK_1: starting archive log restore to default destination
Channel ORA_DISK_1: restoring archive log
Archive log thread = 1 sequence = 8
Channel ORA_DISK_1: reading from backup piece/home/oracle/rman/arch_gg1_201209242615
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/home/oracle/rman/arch_gg1_201209238515 tag = TAG20120921T013312
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Archive log filename =/home/oracle/archivelog/4158_794474474.dbf thread = 1 sequence = 8
Unable to find archive log
Archive log thread = 1 sequence = 9
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure of recover command at 01:38:18
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 529513
View the logs of the original database:
[Oracle @ GOLDENGATE1 archivelog] $ ls
When 4_794474474.dbf then 5_794474474.dbf then 6_794474474.dbf then 7_794474474.dbf then 8_794474474.dbf
[Oracle @ GOLDENGATE1 archivelog] $
Here there is no such log.
Because the original database does not generate a log with SEQ as 9, there is no backup of 9 at all.
Use a SQLPLUS connection for restoration.
SQL> recover database using backup controlfile;
ORA-00279: change 529513 generated at 09/21/2012 01:33:11 needed for thread 1
ORA-00289: suggestion:/home/oracle/archivelog/rj9_794474474.dbf
ORA-00280: change 529513 for thread 1 is in sequence #9
 
Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
Cancel
Media recovery canceled.
12. Open the database.
SQL> alter database open resetlogs;
Alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oradata/gg1/system01.dbf'
Because the control file and data file are inconsistent.
Solution:
Modify the parameter file: Add _ allow_resetlogs_uption = 'ture'
Close the database, rebuild the SPFILE file, and start it to the NOMOUNT state.
Open the database:
SQL> alter database open resetlogs;
Database altered.
13. Check
The REDO log and temporary tablespace files are created based on the records in the control file when the database is opened.
View the tables created in the original database:
SQL> select * from rmantest;
ID
----------
88
Migration completed.