Oracle migrate from 32 bit to 64 bit using RMAN

Oracle migrate from 32 bit to 64 bit using RMAN

to check database is 32 bit or 64 bit !!!


Migrate a Database From Linux x86 ( 32-bit ) To Linux Itanium 64-bit (IA64) [ID 553868.1]
Migrating OLAP From 32 To 64 Bits [ID 352306.1]


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum<2 ;

WORD_LENGTH
---------------------------------------------
32-bits


[oracle@ora11g2 ~]$ cd $ORACLE_HOME/bin
[oracle@ora11g2 bin]$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

SQL> select metadata from sys.kopm$ ;

METADATA
--------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405
0607080823432323081123081141B0230083036907D0030000000000000000000000000000000000
0000000000000000000000000000000000000000

B023     then 32 bit
B047     then  64 bit



OS
SQL> !uname -m
i686

Start migrate here !!!



Take Rman Back up for 32Bit database ..

RMAN> RUN
 {
 configure controlfile autobackup on;
2> 3> 4> set command id to 'ORCLEEBackupFull';
5> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
6> backup AS COMPRESSED BACKUPSET full database tag ORCLEE_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
7> 8> backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
9> backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
10> 11> }

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1
channel c1: SID=53 device type=DISK

Starting backup at 19-FEB-17
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:09:40
Finished backup at 19-FEB-17

Starting Control File and SPFILE Autobackup at 19-FEB-17
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_02_19/o1_mf_s_936356375_dblynrwv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-17

sql statement: alter system archive log current

Starting backup at 19-FEB-17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=936355565
input archived log thread=1 sequence=7 RECID=1 STAMP=936355549
input archived log thread=1 sequence=8 RECID=3 STAMP=936356381
input archived log thread=1 sequence=9 RECID=4 STAMP=936356381
channel c1: starting piece 1 at 19-FEB-17
channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/Archive/1_6_936352013.dbf RECID=2 STAMP=936355565
archived log file name=/u01/app/oracle/Archive/1_7_936352013.dbf RECID=1 STAMP=936355549
archived log file name=/u01/app/oracle/Archive/1_8_936352013.dbf RECID=3 STAMP=936356381
archived log file name=/u01/app/oracle/Archive/1_9_936352013.dbf RECID=4 STAMP=936356381
Finished backup at 19-FEB-17

Starting backup at 19-FEB-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 19-FEB-17
channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL tag=ORCLEE_CON
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17

Starting Control File and SPFILE Autobackup at 19-FEB-17
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_02_19/o1_m
Finished Control File and SPFILE Autobackup at 19-FEB-17

released channel: c1



SQL> create pfile from spfile ;

File created.


[oracle@ora11g2 dbs]$ cat initorcl.ora
orcl.__db_cache_size=251658240
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=318767104
orcl.__sga_target=478150656
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=209715200
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/Archive'
*.memory_target=793772032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


scp backup from 32 bit server to 64 bit server  ..

=====================

RMAN> startup force nomount ;

Oracle instance started

Total System Global Area     793350144 bytes

Fixed Size                     2217544 bytes
Variable Size                536873400 bytes
Database Buffers             251658240 bytes
Redo Buffers                   2600960 bytes

RMAN> restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL' ;

Starting restore at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-17

RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1


*****************************************************************
If required !!!!

gen_rman_restore_recovery_script.sql

-- Begin gen_rman_restore_recovery_script.sql
accept oldname prompt "Enter source db name: "
accept newname prompt "Enter target db name: "

set pages 0 head off feed off veri off lines 500 pages 2000 echo off termout off trimspool on

column cmd format a140
spool restore_recover_&&newname\.rman

select 'RUN '||CHR(10)||'{' cmd from dual
union all
--Rename the datafiles
select 'SET NEWNAME FOR DATAFILE '||file#||' TO '''||NNAME||''';' cmd
from (SELECT FILE# ,NAME,replace(replace(name,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname')) NNAME FROM V$DATAFILE) df
union all
--Rename the online redo logs
select 'SQL "ALTER DATABASE RENAME FILE '''''||MEMBER||''''' TO '||chr(10)||' '''''||replace(replace(member,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname'))||''''' ";' cmd
from (SELECT GROUP#,MEMBER FROM V$LOGFILE)
union all
--Do a SET UNTIL to prevent recovery of the online logs
select 'SET UNTIL SCN '||MINSCN||';' cmd
from (SELECT MIN(SCN) MINSCN FROM (SELECT MAX(NEXT_CHANGE#) SCN FROM V$ARCHIVED_LOG GROUP BY THREAD#))
union all
--restore the database and switch the datafile names
select  'RESTORE DATABASE CHECK READONLY;' cmd from dual
union all
select 'SWITCH DATAFILE ALL;' cmd from dual
union all
--recover the database
select 'RECOVER DATABASE DELETE ARCHIVELOG;' from dual
union all
select '}'||CHR(10)||'EXIT' from dual
/
spool off
exit
-- End gen_rman_restore_recovery_script.sql

RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' TO  ''/u01/app/oracle/oradata/orcl/redo03.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log'' TO ''/u01/app/oracle/oradata/orcl/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log'' TO  ''/u01/app/oracle/oradata/orcl/redo01.log'' ";
SET UNTIL SCN 838420;
RESTORE DATABASE CHECK READONLY;
SWITCH DATAFILE ALL;
RECOVER DATABASE DELETE ARCHIVELOG;
}

****************************************************



RMAN> catalog start with '/u01/app/oracle/RmanBackup/';

Starting implicit crosscheck backup at 19-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 19-FEB-17

Starting implicit crosscheck copy at 19-FEB-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-FEB-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_02_19/o1_mf_n_936370038_dbmd011j_.bkp

searching for all files that match the pattern /u01/app/oracle/RmanBackup/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL

RMAN> restore database ;

Starting restore at 19-FEB-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 19-FEB-17

RMAN> RESTORE ARCHIVELOG ALL;

Starting restore at 19-FEB-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE tag=ORCLEE_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-FEB-17

RMAN> run {
set until scn 838420;
recover database;
}
2> 3> 4>
executing command: SET until clause

Starting recover at 19-FEB-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/Archive/1_8_936352013.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/Archive/1_9_936352013.dbf
archived log file name=/u01/app/oracle/Archive/1_8_936352013.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/Archive/1_9_936352013.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-17




SQL> select * from dual;

ADDR                   INDX    INST_ID D
---------------- ---------- ---------- -
0000000008ADEC58          0          1 X


SQL>  alter database open resetlogs migrate;

Database altered.



SQL> select * from dual;

D
-
X



Run following script !!!!!

@?/rdbms/admin/utlirp.sql

@?/rdbms/admin/utlrp.sql

SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================

then shutdown the database ... 

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> !hostname
ora.singh.com

SQL> def
DEFINE _DATE           = "19-FEB-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

SQL> startup ;
ORACLE instance started.

Total System Global Area  793350144 bytes
Fixed Size                  2217544 bytes
Variable Size             536873400 bytes
Database Buffers          251658240 bytes
Redo Buffers                2600960 bytes
Database mounted.
Database opened.



SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum<2 ;

WORD_LENGTH
---------------------------------------------
64-bits


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production                        <<<<<<<<<<<<<----- 64 bit 
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(8)   <<<<< in 64bit it should be 8
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)