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
