![Chanel [K]](http://www.dbform.com/wp-content/chanelk.png)
Archive for the ‘oracle9i’ tag
Transporting Oracle9i Tablespaces from Solaris to HP-UX
我们知道跨平台表空间传输是在Oracle10g以后实现的，用RMAN命令可以完成。
那么在Oracle9i数据库中到底跨平台表空间传输能不能够实现呢？Oracle官方文档中提到：
The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
必须要在同一硬件平台上，并且举例无法从Solaris平台传输表空间到Windows平台。
但是实际上，这样的约束仅仅是由于字符序的限制。
SQL> SELECT * FROM v$transportable_platform ORDER BY PLATFORM_NAME; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ---------------------------------------- -------------- 6 AIX-Based Systems (64-bit) Big 16 Apple Mac OS Big 19 HP IA Open VMS Little 15 HP Open VMS Little 5 HP Tru64 UNIX Little 3 HP-UX (64-bit) Big 4 HP-UX IA (64-bit) Big 18 IBM Power Based Linux Big 9 IBM zSeries Based Linux Big 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) Little 13 Linux x86 64-bit Little 7 Microsoft Windows IA (32-bit) Little 8 Microsoft Windows IA (64-bit) Little 12 Microsoft Windows x86 64-bit Little 17 Solaris Operating System (x86) Little 20 Solaris Operating System (x86-64) Little 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big
可以看到所有的Windows平台上Oracle数据库均是小字符序的，而Solaris SPARC平台是大字符序，因此Oracle官方文档笼统地说不支持跨平台传输表空间。
选择同样为大字符序的操作系统Solaris SPARC 64bit和HP-UX IA (64-bit)，做Oracle9i数据库的跨平台表空间传输，实验证明是完全可行的。
Solaris平台配置：Solaris10，Oracle9.2.0.6
HP-UX平台配置：HP-UX11，Oracle9.2.0.8
无论是操作系统平台还是数据库小版本号都是不一样的。
在Solaris端，USERS表空间中有数据文件/dev/md/rdsk/d29（裸设备），并且存在两张表，KAMUS.T1和SYSTEM.T。
SQL> SELECT segment_name,owner FROM dba_segments WHERE tablespace_name='USERS'; SEGMENT_NAME OWNER ------------------------------ ------------------------------ T SYSTEM T1 KAMUS
将USERS表空间设置为READONLY之后，开始传输表空间。
$ exp TRANSPORT_TABLESPACE=y TABLESPACES=(users) TRIGGERS=n CONSTRAINTS=n GRANTS=n FILE=/install/expdat.dmp Export: Release 9.2.0.6.0 - Production on Thu Nov 19 17:25:48 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Username: SYS/oracle AS SYSDBA Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) Note: table data (rows) will not be exported Note: grants on tables/views/sequences/roles will not be exported Note: constraints on tables will not be exported About to export transportable tablespace metadata... For tablespace USERS ... . exporting cluster definitions . exporting table definitions . . exporting table T . . exporting table T1 . end transportable tablespace metadata export Export terminated successfully without warnings.
然后通过dd命令将数据文件以及dmp文件copy到HP-UX机器上，在我的实验环境中，最终是dd到/dev/testvg/rlvol4上。
然后在HP-UX上导入metadata：
imp PARFILE='par.f'
其中par.f文件的内容如下：
TRANSPORT_TABLESPACE=y
FILE=/oracle/oradata/expdat.dmp
DATAFILES=('/dev/testvg/rlvol4')
TABLESPACES=(users)
TTS_OWNERS=(system,kamus)
FROMUSER=(system,kamus)
TOUSER=(system,system)成功导入之后，查看USERS表空间中的数据。
SQL> SELECT segment_name,owner FROM dba_segments WHERE tablespace_name='USERS'; SEGMENT_NAME OWNER ------------------------------ ------------------------------ T SYSTEM T1 SYSTEM
继续在该表空间中做其他操作，比如create table等，也一切正常。
只做了使用裸设备作为数据文件的测试，不确认是不是如果数据文件存储在文件系统上也同样可以成功。如果是AIX平台，要注意一下，在AIX中裸设备头部有4K的OS Reserved Area，dd的时候需要skip掉。
没有做更多平台的测试，但是可以推测，即使是Oracle9i，Linux,Windows这两个平台或者AIX,HP-UX,SOLARIS SPARC这三个平台之间都应该是可以顺利地完成表空间传输的。
Update@2009-12-28
朋友测试的结果，从HP-UX IA (64-bit)平台传输表空间到 AIX-Based Systems (64-bit)平台无法成功。即使dd了文件头也仍然会报错。
How to resolve ORA-01034 when RAC failover
今天在客户处测试Oracle 9.2.0.8 on HP-UX IA64的RAC Failover功能，遇到ORA-01034错误。
表现为：
当关闭RAC环境的某一个实例之后（无论是shutdown abort还是shutdown immediate），再用远程客户端通过tns连接RAC Service都会间歇性报ORA-01034错误。
$ sqlplus system/oracle@prod SQL*Plus: Release 9.2.0.8.0 - Production ON Tue Nov 17 20:52:09 2009 Copyright (c) 1982, 2002, Oracle Corporation. ALL rights reserved. ERROR: ORA-01034: ORACLE NOT available ORA-27101: shared memory realm does NOT exist HPUX-ia64 Error: 2: No such file OR directory
客户端的TNS配置是很常规的客户端failover。
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = VIP2)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC))
)
)纳闷许久，仔细检查服务器端的listener.ora配置，才发现设置了GLOBAL_DBNAME，这是对于还没有往本地监听动态注册服务名功能的Oracle8和Oracle7才需要设置，在Oracle9i之后，如果设置了该参数，将会导致Failover失败。
将listener.ora中的配置从：
SID_LIST_LISTENER_PROD2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=prod)
(ORACLE_HOME = /oracle/product/9.2)
(SID_NAME = prod2)
)
)修改为：
SID_LIST_LISTENER_PROD2 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/9.2)
(SID_NAME = prod2)
)
)再次测试Failover，一切正常。
结论：
1. 对于监听依然存在，然后数据库实例关闭的情况，必须是在监听中动态注册的服务，才可以实现Failover。
2. GLOBAL_DBNAME会影响Failover。
“Alter Database Recover” Vs “Recover Database”
操作系统版本：Solaris 5.8 Sparc 64bit
数据库版本：Oracle 9.2.0.1
一直以为”alter database recover”和”recover database”这两个命令除了前者是SQL命令后者是SQL*PLUS命令之外，其它都是相同的，实际上却有所出入。
在所有需要的归档日志都存在的相同前提下。alter database recover命令无法继续。
SQL> ALTER DATABASE recover USING backup controlfile until cancel; ALTER DATABASE recover USING backup controlfile until cancel * ERROR at line 1: ORA-00279: CHANGE 10402260063 generated at 10/31/2009 01:30:52 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9565_1.arc ORA-00280: CHANGE 10402260063 FOR thread 1 IS IN sequence #9565
但是recover database命令却可以让DBA继续输入log file的名称。
SQL> recover DATABASE USING backup controlfile until cancel; ORA-00279: CHANGE 10402260063 generated at 10/31/2009 01:30:52 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9565_1.arc ORA-00280: CHANGE 10402260063 FOR thread 1 IS IN sequence #9565 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /xf_arch1/log9565_1.arc ORA-00279: CHANGE 10402260063 generated at 10/30/2009 23:24:14 needed FOR thread 2 ORA-00289: suggestion : /xf_arch1/log10763_2.arc ORA-00280: CHANGE 10402260063 FOR thread 2 IS IN sequence #10763 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /xf_arch1/log10763_2.arc ORA-00279: CHANGE 10402782605 generated at 10/31/2009 06:21:30 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9566_1.arc ORA-00280: CHANGE 10402782605 FOR thread 1 IS IN sequence #9566 ORA-00278: log file '/xf_arch1/log9565_1.arc' no longer needed FOR this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL> SQL> ALTER DATABASE open resetlogs; DATABASE altered.
在上例中如果要想alter database recover成功，需要如下的语法：
ALTER DATABASE RECOVER automatic DATABASE until CHANGE 10404040058 USING backup controlfile;
或者：
ALTER DATABASE recover logfile '/xf_arch1/log9565_1.arc' USING backup controlfile;
a) use ALTER DATABASE if you want to do the recoverying step by step, manually — issueing each and every single solitary command to recover the database (apply logfiles).
b) use the SQLPLUS ‘recover’ command to have sqlplus automate the steps in a) for you.
更加详细的描述可以参看AskTOM的解释。
