Oracle Database From  32Bit To 64 Bit migrate

Migrate a Oracle Database From  32Bit To 64 Bit

Metalink Note !!!

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]

To Check 32 Bit or 64 Bit !!!! 

[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

For OS

SQL> !uname -m

i686

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

WORD_LENGTH

---------------------------------------------

32-bits

SQL> select metadata from sys.kopm$ ;

METADATA

--------------------------------------------------------------------------------

0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405

0607080823432323081123081141B0230083036907D0030000000000000000000000000000000000

0000000000000000000000000000000000000000

SQL> select metadata from sys.kopm$ ;

If the output references the string 'B023' then it indicates that the database was created as 32-bit

If the output references the string 'B047' then it indicates that the database was created as 64-bit

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

create pfile from 32bit database 

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'

Take rman backup for 32 bit  !! 

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: starting piece 1 at 19-FEB-17

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

scp all the backup file to 64 Bit 

create spfile and Modify oratab on 64 bit system 

start the database from 64 bit Binary 

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

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

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

For SCN NO 

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

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

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 Scripts ... 

@?/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> desc v$session

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SADDR                                              RAW(8)<<<<<< for 64 Bit Should be 8

 SID                                                NUMBER

 SERIAL#                                            NUMBER

 AUDSID                                             NUMBER

 PADDR                                              RAW(8)

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Release 11.2.0.1.0 - 64bit 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