关于单实例从32位迁移到64位的说明,参考:oracle convert a 32-bit database to 64-bit database(32位 转到 64位)说明
在这篇文章中演示第一种方法:直接copy datafile进行迁移。
DB:11.2.0.3 32 位
IP:192.168.3.200
ORACLE_HOME:
[oracle@tianlesoftware ~]$ echo$ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
DB:11.2.0.3 64位
OS:Oracle Linux 6.1 64位
IP:192.168.3.201
ORACLE_HOME:
rac1:/home/oracle> echo $ORACLE_HOME
/u02/app/oracle/product/11.2.0/db_1
SQL> oradebug setmypid
Statement processed.
SQL>
Database altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/anqing/anqing/trace/anqing_ora_8874.trc
[oracle@tianlesoftware u01]$ cat /u01/app/oracle/diag/rdbms/anqing/anqing/trace/anqing_ora_8874.trc
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE"ANQING" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
GROUP 1 '/u01/app/oracle/oradata/anqing/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2'/u01/app/oracle/oradata/anqing/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/anqing/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/anqing/system01.dbf',
'/u01/app/oracle/oradata/anqing/sysaux01.dbf',
'/u01/app/oracle/oradata/anqing/undotbs01.dbf',
'/u01/app/oracle/oradata/anqing/users01.dbf'
;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/anqing/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ONNEXT 655360 MAXSIZE 32767M;
Database closed.
Database dismounted.
ORACLE instance shut down.
注意这里的TEMP 表空间,虽然我们可以一起拷贝过去,但是还是建议重建TEMP,已避免其他的问题。
rac1:/u02> cd app/oracle/
rac1:/u02/app/oracle> ls
checkpoints diag product
rac1:/u02/app/oracle> mkdir admin
rac1:/u02/app/oracle> mkdir fast_recovery_area
rac1:/u02/app/oracle/product/11.2.0/db_1/dbs>cd /u02/app/oracle/fast_recovery_area/
rac1:/u02/app/oracle/fast_recovery_area>mkdir anqing
rac1:/u02/app/oracle> mkdir oradata
rac1:/u02/app/oracle> ls
admin checkpoints diag fast_recovery_area oradata product
rac1:/u02/app/oracle> cd oradata
rac1:/u02/app/oracle/oradata> mkdiranqing
rac1:/u02/app/oracle/oradata> cd ../
rac1:/u02/app/oracle> ls
admin checkpoints diag fast_recovery_area oradata product
rac1:/u02/app/oracle> cd admin
rac1:/u02/app/oracle/admin> ls
rac1:/u02/app/oracle/admin> mkdir anqing
rac1:/u02/app/oracle/admin> cd anqing
rac1:/u02/app/oracle/admin/anqing> mkdiradump
rac1:/u02/app/oracle/admin/anqing> mkdirdpdump
rac1:/u02/app/oracle/admin/anqing> mkdirpfile
rac1:/u02/app/oracle/admin/anqing> mkdirscripts
rac1:/u02/app/oracle/admin/anqing> ls
adump dpdump pfile scripts
[oracle@tianlesoftware anqing]$ scp *192.168.3.201:/u02/app/oracle/oradata/anqing
oracle@192.168.3.201's password:
control01.ctl 100% 9520KB 9.3MB/s 00:01
redo01.log 100% 50MB 5.6MB/s 00:09
redo02.log 100% 50MB 6.3MB/s 00:08
redo03.log 100% 50MB 5.0MB/s 00:10
sysaux01.dbf 100% 510MB 4.2MB/s 02:01
system01.dbf 100% 710MB 5.3MB/s 02:13
temp01.dbf 100% 29MB 5.8MB/s 00:05
undotbs01.dbf 100% 75MB 5.0MB/s 00:15
users01.dbf 100%5128KB 5.0MB/s 00:01
SQL> create pfile from spfile;
File created.
[oracle@tianlesoftware dbs]$ scp initanqing.ora 192.168.3.201:/u02/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.3.201's password:
initanqing.ora 100% 1017 1.0KB/s 00:00
修改的内容包括控制文件保存位置,user_dump_dest,background_dump_dest,core_dump_dest 等。
rac1:/u02/app/oracle/product/11.2.0/db_1/dbs>cat initanqing.ora
anqing.__db_cache_size=570425344
anqing.__java_pool_size=16777216
anqing.__large_pool_size=16777216
anqing.__oracle_base='/u02/app/oracle'#ORACLE_BASEset from environment
anqing.__pga_aggregate_target=570425344
anqing.__sga_target=838860800
anqing.__shared_io_pool_size=0
anqing.__shared_pool_size=218103808
anqing.__streams_pool_size=0
*.audit_file_dest='/u02/app/oracle/admin/anqing/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/anqing/control01.ctl','/u02/app/oracle/fast_recovery_area/anqing/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='anqing'
*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4194304000
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=anqingXDB)'
*.log_archive_dest_1='LOCATION=/u02/archivelog'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1393557504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='undotbs1'
在重建控制之前先rm 掉我们从Source 端copy 过来的控制文件,同时修改我们的控制文件代码,使路径使用Target 的路径。
rac1:/u02/app/oracle/oradata/anqing> ls
control01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
redo01.log redo03.log system01.dbf undotbs01.dbf
rac1:/u02/app/oracle/oradata/anqing> rmcontrol01.ctl
rac1:/u02/app/oracle/oradata/anqing> rmtemp01.dbf
rac1:/u02/app/oracle/oradata/anqing> ls
redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
rac1:/u02/app/oracle/oradata/anqing>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onMon Feb 13 21:20:08 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
ORACLE instance started.
Total System Global Area 1402982400 bytes
Fixed Size 2228304 bytes
Variable Size 822087600 bytes
Database Buffers 570425344 bytes
Redo Buffers 8241152 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE"ANQING" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1'/u02/app/oracle/oradata/anqing/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2'/u02/app/oracle/oradata/anqing/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/app/oracle/oradata/anqing/redo03.log' SIZE 50M BLOCKSIZE 512
11 --STANDBY LOGFILE
12 DATAFILE
13 '/u02/app/oracle/oradata/anqing/system01.dbf',
14 '/u02/app/oracle/oradata/anqing/sysaux01.dbf',
15 '/u02/app/oracle/oradata/anqing/undotbs01.dbf',
16 '/u02/app/oracle/oradata/anqing/users01.dbf'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
SQL> alter database open;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE'/u02/app/oracle/oradata/anqing/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE'/u02/app/oracle/oradata/anqing/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
*
ORA-00604: error occurred at recursive SQLlevel 1
ORA-06553: PLS-801: internal error [56327]
SQL> SPOOL mig32-64.log;
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
……
DOC>###############################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>###############################################################
SQL>
SQL> SPOOL OFF;
SQL> select count(*) from all_objectswhere status='INVALID';
COUNT(*)
----------
9768
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1402982400 bytes
Fixed Size 2228304 bytes
Variable Size 822087600 bytes
Database Buffers 570425344 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql;
SQL> select count(*) from all_objectswhere status='INVALID';
COUNT(*)
----------
8054
begin
update obj$ set status=5 where obj#=(select obj# fromobj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
declare
cursor C1 is select
'DROP JAVA DATA "' || u.name||'"."' || o.name || '"'
from obj$ o,user$ u where o.type#=56 andu.user#=o.owner#;
ddl_statement varchar2(200);
iterations number;
previous_iterations number;
loop_count number;
my_err number;
begin
previous_iterations := 10000000;
loop
-- To make sure we eventually stop,pick a maxnumber of iterations
select count(*) into iterations from obj$ wheretype#=56;
exit when iterations=0 or iterations >=previous_iterations;
previous_iterations := iterations;
loop_count := 0;
open C1;
loop
begin
fetch C1 intoddl_statement;
exit when C1%NOTFOUND orloop_count > iterations;
exception when others then
my_err := sqlcode;
if my_err = -1555then --snapshot too old, re-execute fetch query
exit;
else
raise;
end if;
end;
initjvmaux.exec(ddl_statement);
loop_count := loop_count + 1;
end loop;
close C1;
end loop;
end;
commit;
initjvmaux.drp('delete fromjava$policy$shared$table');
update obj$ set status=1 where obj#=(select obj# fromobj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
end;
/
create or replace java system
/
SQL> @?/rdbms/admin/utlrp.sql;
--这里突然想到把第九步的编译无效对象省略,放到这里一起执行,应该可以节省半个小时,不过这里不能测试了。
ORA-07445: exception encountered: core dump[__intel_new_memcpy()+2164] [SIGSEGV] [ADDR:0x7F2F0CBBD2BF] [PC:0x47ED7D4][Address not mapped to object] []
----- Current SQL Statement for thissession (sql_id=2y0pxmcj6k00t) -----
ALTER VIEW"OLAPSYS"."ALL$OLAP2_AW_CATALOGS" COMPILE
--查看组件
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
----------------------------------------------- ------------ ------------------
OWB OWB 11.2.0.3.0 VALID
APEX Oracle Application Express 3.2.1.00.12 VALID
EM Oracle Enterprise Manager 11.2.0.3.0 VALID
AMD OLAP Catalog 11.2.0.3.0 INVALID
SDO Spatial 11.2.0.3.0 INVALID
ORDIM Oracle Multimedia 11.2.0.3.0 INVALID
XDB Oracle XML Database 11.2.0.3.0 INVALID
CONTEXT Oracle Text 11.2.0.3.0 VALID
EXF Oracle Expression Filter 11.2.0.3.0 INVALID
RUL Oracle Rules Manager 11.2.0.3.0 INVALID
OWM Oracle Workspace Manager 11.2.0.3.0 INVALID
COMP_ID COMP_NAME VERSION STATUS
----------------------------------------------- ------------ ------------------
CATALOG Oracle Database Catalog Views 11.2.0.3.0 INVALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 INVALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID
XML Oracle XDK 11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID
XOQ Oracle OLAP API 11.2.0.3.0 VALID
How To Remove Or To Reinstall The OLAPOption To 10g And 11g [ID 332351.1]
备份DB和oraInventory,在关闭ORACLE_HOME上的所有Instance,使用Universal Installer (OUI) 移除ORACLE_HOME的OLAP 选项。在OUI 中选择升级已经存在的db,在selectoptions 选项可以控制是否启用OLAP。如下图:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk olap_off
make -f ins_rdbms.mk ioracle
非注册的OLAP将不在db 的banner 中限制,同时v$option 也会显示为FALSE.
1)在OUI中添加OLAP 组件,参考上图。
2)添加完毕之后执行如下脚本,在db 级别进行添加。
sqlplus /nolog
SQL> conn /as sysdba
SQL> spool add_olap.log
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
ORACLE_HOME=your_oracle_home
ORACLE_SID=your_db_sid
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL> conn / as sysdba
SQL> @?/olap/admin/catnoamd.sql
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/cwm2drop.sql
SQL> @?/rdbms/admin/utlrp.sql
catnoamd.sqlused above will drop the OLAPSYS schema (which is completely OLAP specific).
cwm2drop.sqlneeds to be run only in 11g. In 10g, catnoamd.sql already calls it.
If you connectto the database in SQLPLUS, the banner still shows the OLAP option,this happensbecause the Oracle executable is still aware of OLAP, it is linked with OLAP, andalso the OLAP files are still present in the ORACLE_HOME.
Once running thedeinstall scripts, the utlrp.sql could report invalid objects with SYS andPUBLIC owners. If so, the following SQL can be used to report more details onthat:
SQL> select owner, object_name,object_type, status from dba_objects where status='INVALID';
You could getsome invalid objects under SYS and PUBLIC owner, and they are the old duplicate OLAPSYS objects copied under these schemas when Olap has been installedpreviously.
Generally, theOlap objects are named with context like %OLAP%, %AWM%, or other Olap word keys intheir "object_name" field, however, if it needs a help to recognizethem, then please contact Oracle Support and create a Service Request toget assistance for this question.
Due to the factthat it refers to three scripts which don't get shipped until 11.2 this scriptwill fail.
Besides that itwill error on 7 non-existing synonyms to drop.
Prior 11.2, execute these three dropsynonym statements:
SQL> drop public synonym OlapFactView;
SQL> drop public synonym OlapDimView;
SQL> drop public synonym DBMS_ODM;
If there arefurther invalid OLAP specific objects after the above removal steps, pleaseconsult Note1060023.1, Note565773.1
If you do notwant to install OLAP into a newly created database, then in the DatabaseConfiguration Assistant (dbca), select a custom database template and uncheckOracle OLAP in the Database Components wizard step.
Assuming thatyou created your database manually or via DBCA, add the OLAP option to anexisting Enterprise Edition Database.
Then open aterminal session on the database server machine, set ORACLE_HOME, ORACLE_SIDenvironment variables, and execute some script as user SYS AS SYSDBA, detailsbelow:
ORACLE_HOME=your_oracle_home
ORACLE_SID=your_db_sid
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL> conn /as sysdba
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
我这里按照DATABASE 的级别重建了OLAP。
在我们重建OLAP 之前有很多的无效组件,现在查看,都变成了有效状态,并且也没有了无效对象,如果到这一步还有无效对象,那么继续执行utlrp.sql 来编译。
SQL> select count(*) from all_objectswhere status='INVALID';
COUNT(*)
----------
0
SQL> selectcomp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
----------------------------------------------- ------------ ------------------
AMD OLAP Catalog 11.2.0.3.0 VALID
OWB OWB 11.2.0.3.0 VALID
APEX Oracle Application Express 3.2.1.00.12 VALID
EM Oracle Enterprise Manager 11.2.0.3.0 VALID
ORDIM Oracle Multimedia 11.2.0.3.0 VALID
XDB Oracle XML Database 11.2.0.3.0 VALID
CONTEXT Oracle Text 11.2.0.3.0 VALID
EXF Oracle Expression Filter 11.2.0.3.0 VALID
RUL Oracle Rules Manager 11.2.0.3.0 VALID
OWM Oracle Workspace Manager 11.2.0.3.0 VALID
COMP_ID COMP_NAME VERSION STATUS
----------------------------------------------- ------------ ------------------
CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID
XML Oracle XDK 11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID
APS OLAP Analytic Workspace 11.2.0.3.0 VALID
XOQ Oracle OLAP API 11.2.0.3.0 VALID
18 rows selected.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE'/u02/app/oracle/oradata/anqing/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> create spfile from pfile;
File created.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 -Production
NLSRTL Version 11.2.0.3.0 - Production