前次帖子说到ASM实例不能发现创建的磁盘组。问题的解决方法。
我的平台是enterprise linux 5 oracle基于RHEL5的一个发行版。内核为2.6.18-8.1.8.0.1。
这个平台对10G支持不是很好。下面专门对ASM不能发现磁盘个人解决办法说说明：
1、安装ASM lib以前的版本，新ASM rpm没有提供，不知道为什么？（oracleasm-2.6.18-8.1.8.0.1)
2、重新运行oracleasm添加磁盘
3、运行oracleasm-discover发现磁盘。以前没有这个命令，也许是安装了早版本的ASM lib的原因吧。
4、运行$ORACLE_HOME/rdbms/lib/kfed read /dev/oracleasm/disks/VOL1以确认磁盘信息。
5、修改pfile,asm-diskstring='ORCL:VOL*'开始创建＋ASM实例时我为了保险，使用了pfile.
6、重新启动＋ASM1实例。select*from v$asm_disk终于找到磁盘了。

 查看全文

关于在RHEL5上安装crs的错误

关于在RHEL5上安装crs的错误
1、在RHEL5上配置安装oracle10g crs时运行到最后一步，需要启动配置vipca时系统抱错。WARNING: directory '/app/oracle/product/10.2.0' is not owned by root
WARNING: directory '/app/oracle/product' is not owned by root
WARNING: directory '/app/oracle' is not owned by root
WARNING: directory '/app' is not owned by root
/app/oracle/product/10.2.0/crs/bin/crsctl.bin: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory
这个问题是新版的glibc和10g的java有不兼容的地方。按照官方文档要在运行root.sh之前修改vipca脚本。这样就可以安装了。即将该脚本中arch=‘’uname -m‘以下4行注释掉
arch=`uname -m`
#if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
#then
# LD_ASSUME_KERNEL=2.4.19
# export LD_ASSUME_KERNEL
#fi
#End workaround
这样就不会出现错误了。

2、另外RHEL5及相应发行版，包括enterprise linux（oracle），中文字体极差。建议安装时将默认会话指定为英文。export=LANG即可

3、另外虽然crs已经安装好了。运行crs_stat也没有问题。但是netca后集群linster无法配置，提示crs_stat丢失文件。而且安装db后无法启动dbca图形界面，java报错。启动netmgr倒是可以，但点击项目时后台也报了一堆java错误。此前只是在安装jdk1.6是在/etc/profile中将java_home指向了新的javahome。没有做其他设置。允许java --version也还是指显示旧版本。为1.4.2。不能启动图形化界面和crs——listener不能配置是什么问题呢？在rhel4及相应版本上还没有这个问题。看来要等待。

利用flashback还原数据库和表

flashback 是oracle 给我们提供的最有价值的工具之一。利用他结合TSPITR可以大大节省DBA的工作量。
flashback 一张表的方法：
1、首先确认用户有flashback any table 的权限，或者 grant flashback any table to user;
2、向一张表中插入数据：
INSERT INTO employee
SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
3、如果确认数据插入错误：在FLASH_TRANSACTION_QUERY表中查询插入信息：
SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid IN
(SELECT versions_xid FROM employee VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');
4、根据表中显示的信息，可以用时间或SCN号进行还原：FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS');

如果表中含有与其它表的约束条件，而造成其它表的数据错误，则建议用以下方式：
1、FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS');
2、EXP 相应的数据；
3、recover database until ...到当前状态
4、IMP相应数据。

错误删除一张表的恢复方法：
1、drop table employee;
2、被drop掉的表会保存在recyclebin中，并且不会自动删除，除非使用purge命令可以完全删除。
3、查询recyclebin中保存的数据：
SQL> SELECT object_name as recycle_name, original_name, object_type
FROM recyclebin;
RECYCLE_NAME ORIGINAL_NAME OBJECT_TYPE
-------------------------------- --------------------- -------------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 EMPLOYEE_DEMO TABLE
BIN$JKS983293M1dsab4gsz/I249==$0 I_EMP_DEMO INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0 LOB_EMP_DEMO LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0 LOB_I_EMP_DEMO LOB INDEX

找到保存的表名，注意此时的表名是以ID形式存在，可以对照别名找到你删除的表。如果不使用ID名还原，可能会导致其它重名，或已经删除的同名表一起被还原。
4、执行恢复：
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

虽然flashback为我们提供了数据库和表级的还原，但是也会有很多限制，比如如果在SQLPLUS下运行只能在本地。远程只能用RMAN；同时不能对表空间和数据文件进行flashback ,当然这里有保持数据一致性的要求。如果涉及表空间的数据恢复，仍然要使用传统方式，对多个数据文件错误或者表的数据错误，个人建议仍然使用 TSPITR，会对数据库影响小些，使用flashback database + exp+recover database+imp方式，对全库影响太大，在生产环境中不太可行，同时如果表中部署有triger ,flashback是不支持的，当然也有解决方法，但考虑到恢复中的复杂性，建议大家还是保守些好。另外flashback 对系统资源占有很大，alter database flashback on 后后，会发现flash_recovery_area默认就达到2G。如果设置周期较长（默认是1天），会更大，会同时记录两份日志。
这里有两张表很重要，要经常查到的V$FLASHBACK_DATABASE_LOG；V$FLASHBACK_DATABASE_STAT，可以查到状态和恢复信息。

还需要关注v$recovery_file_status
防止flash_recovery_area由于空间被占满，而无法归档

关于Oracle10g跨平台传输表空间
1.准备工作:
查询源数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big

查询目标数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little

查询Oracle10g支持的平台转换

代码:--------------------------------------------------------------------------------
SQL> select * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little

2.创建一个独立的自包含表空间

用于测试.

代码:--------------------------------------------------------------------------------
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:04:08 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/eygle/system01.dbf
/opt/oracle/oradata/eygle/undotbs01.dbf
/opt/oracle/oradata/eygle/sysaux01.dbf
/opt/oracle/oradata/eygle/users01.dbf
/data1/oradata/systemfile/eygle01.dbf
/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf

7 rows selected.

SQL> create tablespace trans
2 datafile '/data1/oradata/systemfile/trans01.dbf'
3 size 10M;

Tablespace created.

SQL> create user trans identified by trans
2 default tablespace trans;

User created.

SQL> grant connect,resource to trans;

Grant succeeded.

SQL> connect trans/trans
Connected.

SQL> create table test as select * from user_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> select * from test;

OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S
------------ ------------ ------------------- ------- - - -
TEST
15604 15604 TABLE
27-APR-04 27-APR-04 2004-04-27:14:05:42 VALID N N N

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Dat

3.导出要传输的表空间
$ pwd
/opt/oracle
$ cd dpdata
$ ls
$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_tablespace=trans
LRM-00101: unknown parameter name 'transport_tablespace'

$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans

Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'TRANS' is not read only

Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:08

注意:传输表空间必须置为只读状态

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:08:13 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace trans read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans

Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/dpdata/trans.dmp
Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:09

4.使用rman转换文件格式

$ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: EYGLE (DBID=1337390772)

RMAN> convert tablespace trans
2> to platform 'Microsoft Windows IA (32-bit)'
3> Format '/tmp/%U';

Starting backup at 27-APR-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=/data1/oradata/systemfile/trans01.dbf
converted datafile=/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-APR-04

RMAN> exit

Recovery Manager complete.

5.确认导出文件已生成.

$ ls /tmp/data*
/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ ls -l /tmp/data*
-rw-r----- 1 oracle dba 10493952 Apr 27 14:12 /tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ chmod 777 /tmp/data*
$ chmod 777 /opt/oracle/dpdata/*

6.通过ftp传输文件至目标主机

220 billing-center.hurray.com.cn FTP server (SunOS 5.8) ready.
User (172.16.33.32none)): gqgai
331 Password required for gqgai.
Password:
230 User gqgai logged in.
ftp> bin
200 Type set to I.
ftp> cd /tmp
250 CWD command successful.
ftp> mget data*
200 Type set to I.
mget data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg? y
200 PORT command successful.
150 Binary data connection for data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg (172.16.32.65,2885) (10493952 bytes).
226 Binary Transfer complete.
ftp: 10493952 bytes received in 15.90Seconds 659.87Kbytes/sec.
ftp> cd /opt/oracle/dpdata
250 CWD command successful.
ftp> ls
200 PORT command successful.
150 ASCII data connection for /bin/ls (172.16.32.65,2889) (0 bytes).
export.log
trans.dmp
226 ASCII Transfer complete.
ftp: 23 bytes received in 0.01Seconds 2.30Kbytes/sec.
ftp> bin
200 Type set to I.
ftp> mget trans.dmp
200 Type set to I.
mget trans.dmp? y
200 PORT command successful.
150 Binary data connection for trans.dmp (172.16.32.65,2893) (73728 bytes).
226 Binary Transfer complete.
ftp: 73728 bytes received in 0.03Seconds 2457.60Kbytes/sec.
ftp> bye
221 Goodbye.

7.使用rman在目标数据库转换文件

E:Oracleoradatavilendpdata>rman target /

恢复管理器: 版本10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

连接到目标数据库: VILEN (DBID=1587222708)

RMAN> CONVERT DATAFILE 'E:Oracleoradatavilendpdatadata_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'E:Oracleoradatavilendpdatadata_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg','E:OracleoradatavilenVILENDATAFILEtrans01.dbf';

启动 backup 于 27-4月 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=E:ORACLEORADATAVILENDPDATADATA_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已转换的数据文件 = E:ORACLEORADATAVILENVILENDATAFILETRANS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 于 27-4月 -04

RMAN>

8.在目标数据库plugin数据文件

注意目标数据库中的目标用户必须存在
否则会报错.

E:Oracleoradatavilendpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:Oracleorad
atavilenVILENDATAFILETRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02

Copyright (c) 2003, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:
OracleoradatavilenVILENDATAFILETRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TRANS

作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止

E:Oracleoradatavilendpdata>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user trans identified by trans;

用户已创建。

SQL> grant connect,resource to trans;

授权成功。

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

E:Oracleoradatavilendpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:Oracleorad
atavilenVILENDATAFILETRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03

Copyright (c) 2003, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:
OracleoradatavilenVILENDATAFILETRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成

9.检查数据

E:Oracleoradatavilendpdata>sqlplus trans/trans

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

E:Oracleoradatavilendpdata>

可以选择把表空间更改为读写

SQL> alter tablespace trans read write;

表空间已更改。

10.总结

10g的表空间跨平台迁移，较9i就是增加了一个使用Rman进行的文件格式转换的过程.
实际上也就是转换了数据文件头的格式信息而已.

然而这一简单改进带来的方便之处是显而易见的.

您已经成功注册ITPUB Blog帐号。请进入http://blog.itpub.net/admin.php管理您的Blog站点。建议先修改Blog的名称并建立文档分类，系统已经默认建立了【初始分类】。登陆密码与itpub论坛帐号相同。如有问题请在斑竹区发贴。谢谢。