一、rman方法迁移
32位服务器需要如下工作
1、在32位服务器上查看OLAP组件是否使用。
A、查看实例是否支持OLAP组件。
select VALUE from v$OPTION where PARAMETER = 'OLAP';
B、查看OLAP组件是否备使用过。
select name "FEATURE", first_usage_date "FROM", last_usage_date "TO" from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'
C、查看当前定义的Analytic Workspace (AW),如果查询返回Analytic Workspace (AW)为sys用户所属,那么该实例未使用OLAP。
col owner format a10
col aw_name format a20
select owner, aw_name from dba_aws;
select aw_name, aw_number from all_aws;
D、查看OLAP当前状态。
select comp_id, comp_name, version, status from dba_registry where comp_name like '%OLAP%';
或
col comp_name for a40
SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;
2、查看dbid
select dbid from v$database;
3、查看授权用户
select * from v$pwfile_users
4、拷贝RMAN备份及控制文件备份。
5、查看日志scn号
select current_scn from v$database
select sequence#,first_change#,first_time,next_change#,next_time,archived,deleted from v$archived_log
64位服务器需要如下工作
1、拷贝有用的备份到64位机器(数据库备份、控制文件)。
2、拷贝pfile文件从32服务器上到对应位置。
3、启动64服务器数据库实例。
4、连接rman配置
rman连接数据库并设置dbid
$ rman target / nocatalog
RMAN> set dbid=<dbid from 32 server>;
还原控制文件及数据库
RMAN> restore controlfile from 'xxxxxx';
RMAN> alter database mount;
RMAN> restore database;
恢复数据库
Identify the archivelog till you want to do the recovery and check whether you have all the backups.
RMAN> recover database until scn xxxx;
RMAN> exit
5、升级数据库
启动sqlplus执行如下操作
windows平台:
SQL> SPOOL mig32-64.log;
SQL> alter database open resetlogs migrate;
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql;
SQL> SPOOL OFF;
SQL> shutdown immediate;
SQL> startup;
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;
linux平台:
SQL> SPOOL mig32-64.log;
SQL> alter database open resetlogs migrate;
SQL> @?/rdbms/admin/utlirp.sql
SQL> SPOOL OFF;
Now you must restart the database and explicitly invoke utlrp.sql to recompile invalid objects.
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/utlrp.sql
常见报错
Note 1 :If you are changing wordsize for Oracle release10.1.x/10.2.x/11.1/11.2 32-Bit to 64-bit with OLAP enabled for DB then you may likely to see the error
On Console:
<>ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []<>
On alert.log:
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] <>
Or
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+2132] [SIGSEGV]
(on 11g)
Or ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] < style="font-family: arial unicode ms;"> ( Run the Following query to check OLAP component installed bit version of Oracle release 9.2.0.4 and you want to switch to the 64-bit version of Oracle release 9.2.0.4, then you must complete thi
6、报错解决方法
A、移除olap组件
linux平台:
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
windows平台:
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoamd.sql
----> Remove OLAP API
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\olapidrp.plb
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoaps.sql
B、查看无效对象
select owner, object_name, object_type, status from dba_objects where status='INVALID';
或
col owner for a8
col object_name for a40
col object_type for a15
select owner, object_name, object_type from dba_objects where status <> 'VALID';
C、删除OLAP相关无效的组件
spool oldrop.sql
select 'drop public synonym ' || synonym_name || ';'from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';
spool off
7、再次编译无效对象
linux平台:
SQL>@?/rdbms/admin/utlrp.sql
windows平台:
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;
8、更新java对象
SQL> CREATE OR REPLACE JAVA SYSTEM;
/
SQL> shutdown immediate;
SQL> startup
二、冷备方法迁移
1、在32位服务器上查看OLAP组件是否使用。
A、查看实例是否支持OLAP组件。
select VALUE from v$OPTION where PARAMETER = 'OLAP';
B、查看OLAP组件是否备使用过。
select name "FEATURE", first_usage_date "FROM", last_usage_date "TO" from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'
C、查看当前定义的Analytic Workspace (AW),如果查询返回Analytic Workspace (AW)为sys用户所属,那么该实例未使用OLAP。
col owner format a10
col aw_name format a20
select owner, aw_name from dba_aws;
select aw_name, aw_number from all_aws;
D、查看OLAP当前状态。
select comp_id, comp_name, version, status from dba_registry where comp_name like '%OLAP%';
或
col comp_name for a40
SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;
2、shutdown 32位数据库,进行冷备份全库,紧接着是安装64位ORACLE。
注意:32位于64位数据库版本相同。
3、Oracle程序和数据库分别安装完成后,将TNS和实例服务器停掉,重命名数据库文件目录。
4、将32位服务器上的数据文件COPY到64位服务器的数据文件位置。
######################### 64位服务器需要如下工作 ################################
5、执行升级命令、启动数据库
linux平台:
SQL> STARTUP UPGRADE
Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:
SQL> SPOOL mig32-64.log;
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
SQL> SPOOL OFF;
SQL> shutdown immediate;
SQL> startup;
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql;
Windows平台:
SQL> SPOOL mig32-64.log;
SQL>Startup upgrade
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql;
SQL> SPOOL OFF;
SQL> shutdown immediate;
SQL> startup;
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;
常见错误
Note 1 :If you are changing wordsize for Oracle release10.1.x/10.2.x/11.1/11.2 32-Bit to 64-bit with OLAP enabled for DB then you may likely to see the error
On Console:
<>ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []<>
On alert.log:
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] <>
Or
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+2132] [SIGSEGV]
(on 11g)
Or ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] < style="font-family: arial unicode ms;"> ( Run the Following query to check OLAP component installed bit version of Oracle release 9.2.0.4 and you want to switch to the 64-bit version of Oracle release 9.2.0.4, then you must complete thi
6、移除olap组件
A、删除olap组件
linux平台:
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
windows平台:
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoamd.sql
----> Remove OLAP API
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\olapidrp.plb
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoaps.sql
B、查看无效对象
select owner, object_name, object_type, status from dba_objects where status='INVALID';
col owner for a8
col object_name for a40
col object_type for a15
select owner, object_name, object_type from dba_objects where status <> 'VALID';
C、删除OLAP相关无效的组件
spool oldrop.sql
select 'drop public synonym ' || synonym_name || ';'from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';
spool off
7、再次编译无效对象
linux平台:
SQL>@?/rdbms/admin/utlrp.sql
windows平台:
SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;
8、更新java对象
SQL> CREATE OR REPLACE JAVA SYSTEM;
/
9、再次查看数据库组件状态及无效对象。
col comp_name for a40
SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;
或
select owner, object_name, object_type, status from dba_objects where status='INVALID';