ORACLE导出到EXCEL     示例是每1000条记录spool 到一个 excel 文件中测试数据创建代码: SQL> CREATE TABLE EXP_EXCEL (ID NUMBER, COL VARCHAR2(10)); Table created SQL> INSERT INTO EXP_EXCEL SELECT LEVEL, 'COL_'||TO_CHAR(LEVEL,'FM000000') FROM DUAL CONNECT BY LEVEL<=3000; 3000 rows inserted SQL> COMMIT; Commit complete --------------------------- 创建一个脚本, 比如 名称为spool_xls.sql脚本内容如下:代码: set echo off set linesize 1000 set term off set verify off set feedback off set pagesize 50000 set heading off set trimspool on spool exp_xls.sql select 'SPOOL C:EXP_EXCEL_'||LEVEL||'.XLS'||CHR(10)||        'SELECT * FROM (SELECT t.*, row_number() over(order by ID) as rn FROM exp_excel t) WHERE rn BETWEEN 1000 * ('||level||' -1) + 1 AND 1000 * '||level||';'||chr(10)||     &nb...

OracleRAC管理 之 集群状态&信息查看 参看集群状态[11:34:11oracle@node1 ~]crs_stat -tName Type Target State Host ------------------------------------------------------------ora.dba.db application ONLINE ONLINE node2 ora....l1.inst application ONLINE ONLINE node1 ora....l2.inst application ONLINE ONLINE node2 ora.dba.rac.cs application ONLINE ONLINE node2 ora....ll1.srv application ONLINE ONLINE node1 ora....ll2.srv application ONLINE ONLINE node2 ora....SM1.asm application ONLINE ONLINE node1 ora....E1.lsnr application ONLINE ONLINE node1 ora.node1.gsd application ONLINE ONLINE node1 ora.node1.ons application ONLINE ONLINE node1 ora.node1.vip application ONLINE ONLINE node1 ora....SM2.asm application ONLINE ONLINE node2 ora....E2.lsnr application ONLIN...

Oracle RAC使用频率较高的CRS维护命令总结 与CRS有关的命令均存放在$ORA_CRS_HOME/bin目录下，使用时请多加留意。本文将在日常CRS维护中较常用的命令予以演示，供参考。1.启动CRS如果CRS没有启动在使用crs_stat命令查看集群状态的时候将会收到如下的报错信息。RACDB1@rac1 /home/oracle$ /oracle/app/crs/bin/crs_stat -tCRS-0184: Cannot communicate with the CRS daemon.在RAC环境下启动所有节点上的CRS。[root@rac1 ~]# /oracle/app/crs/bin/crsctl start crsAttempting to start CRS stackThe CRS stack will be started shortly[root@rac2 ~]# /oracle/app/crs/bin/crsctl start crsAttempting to start CRS stackThe CRS stack will be started shortly集群从启动到全部启动完毕的总时间与系统想能和配置有关，一般需要3~10分钟的时间，请耐心等待。注意：为保证集群环境可以正常无误的启动，一定要确保集群环境中各个节点的时钟保持一致。与之对应的停止CRS命令为：“crsctl stop crs”。2.查看CRS资源状态集群资源启动完毕后，以在第一节点查看集群状态为例。RACDB1@rac1 /home/oracle$ /oracle/app/crs/bin/crs_stat -tName      &n...

 Oracle RAC ASM基本操作维护 ASM（自动存储管理）是一个专门为Oracle数据库服务的数据文件存储机制，通过ASM管理数据文件，DBA不用再担心I/O性能问题，也不需要知道文件的名称，同时ASM也提供了文件系统到卷管理器的集成，下面依次介绍。一、 ASM的特点（1）自动调整I/O负载ASM可以在所有可用的磁盘中自动调整I/O负载，不但避免了人工调整I/O的难度，而且也优化了性能，同时，利用ASM可以在线增加数据库的大小，而无需关闭数据库。（2）条带化存储ASM将文件分为多个分配单元（Allocation Units， AU）进行存储，并在所有磁盘间平均分配每个文件的AU。（3）在线自动负载均衡当共享存储设备有变化时，ASM中的数据会自动均匀分配到现有存储设备中。同时，还可以调节数据的负载均衡速度。（4）自动管理数据库文件在ASM存储管理中，Oracle数据文件是ASM自动管理的。ASM创建的任何文件一旦不再需要，就会被自动删除。但是，ASM不管理二进制文件、跟踪文件、预警日志和口令文件。（5）数据冗余ASM通过磁盘组镜像可以实现数据冗余，不需要第三方工具。（6）支持各种Oracle数据文件ASM存储支持Oracle数据文件、日志文件、控制文件、归档日志、RMAN备份集等。二、ASM的...

p4679769_10201_LINUX.ZIP今天在浏览eygle的网站看到一个bug的相关帖子，猛地忽然想起前几天安装过一套rac 中遇到过一个bug，当时没有注意就直接打完bug后就不去做记录了，今日想起略感模糊故记录在此，供自己日后查看，也方便来客查阅。系统环境 redhat 5.5 64.bit  ORACLE 10g 10.2.0.5 raw设备 +lvm在执行root.sh 时出错出错信息如下failed to upgrade oracle cluster registry configuration按照常理应该是执行root后会出现扫描节点完成的信息 如下：WARNING: directory '/opt/oracle/product/10.2' is not owned by rootWARNING: directory '/opt/oracle/product' is not owned by rootWARNING: directory '/opt/oracle' is not owned by rootChecking to see if Oracle CRS stack is already configuredSetting the permissions on OCR backup directorySetting up NS directoriesOracle Cluster Registry configuration upgraded successfullyWARNING: directory '/opt/oracle/product/10.2' is not owned by rootWARNING: directory '/opt/oracle/product' is not owned by rootWARNING: directory '/opt/oracle' is not owned by rootSuccessfully...

Oracle ASM 配置错误--Initializing the Oracle ASMLib driver: [FAILED]其实有些错误如果你注意按照oracle的要求去做的话，是可以避免的，最近又看到了这个错误两次，所以记录一下。[root@rac01 ~]# /etc/init.d/oracleasm configureConfiguring the Oracle ASM library driver.This will configure the on-boot properties of the Oracle ASMlibrarydriver.  The following questions will determinewhether the driver isloaded on boot and what permissions it will have. The current valueswill be shown in brackets ('[]').  Hitting<ENTER> without typing ananswer will keep that current value.  Ctrl-C willabort.Default user to own the driver interface []: gridDefault group to own the driver interface []: asmadminStart Oracle ASM library driver on boot (y/n) [n]: yScan for Oracle ASM disks on boot (y/n) [y]: yWriting Oracle ASM library driver configuration: doneInitializing the Oracle ASMLib driver:[FAILED] 目前知道可能导致这个错误的原因有两个：1.oraclea...

oracle ASM错误Initializing the Oracle ASMLib driver:[FAILED]  Centos 5.8, kernel为：uname -r2.6.18-308.el5安装ASM三个包：rpm -ivh oracleasm-support-2.1.1-1.el4.i386.rpmrpm -ivh oracleasm-2.6.18-128.el5-2.0.5-1.el5.i686.rpmrpm -ivh oracleasmlib-2.0.4-1.el5.i386.rpm这三个包可以在http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html下载到。安装完成之后，进行配置[root@digdeep oracle]# /etc/init.d/oracleasm configureConfiguring the Oracle ASM library driver.This will configure the on-boot properties of the Oracle ASM librarydriver.  The following questions will determine whether the driver isloaded on boot and what permissions it will have.  The current valueswill be shown in brackets ('[]').  Hitting <ENTER> without typing ananswer will keep that current value.  Ctrl-C will abort.Default user to own the driver interface [oracle]:Default group to own the driver interface [dba]:Start Ora...

How to install ASMLIB from Oracle’s public YUM   http://oracleexamples.wordpress.com/2011/08/08/how-to-install-asmlib-from-oracles-public-yum/There are several ways to get ASMLIB, we can download it from the OTN here, or http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html ,or we can download it from from ULN (Unbreakable Linux Network).Also it comes in the the Unbreakable Linux media, but if you are not subscribed to ULN and you are not using Unbreakable Linux then most people go to the OTN, where you have to select your Linux flavor and then navigate to the correct file according to the linux kernel and then download the 3 files one by one.I’m going to explain here how easy is to get it from Oracle’s public YUM repository (how to configure the public YUM repository?)Once you have the public YUM enabled do as root:1yum install o...

Oracle 9i 、 10g 、 11g 软件和补丁的下载地址 Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XPhttp://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk1.ziphttp://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk2.ziphttp://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk3.zipOracle9i Database Release 2 Enterprise/Standard/Personal/Client Edition for Windows XP 2003/Windows Server 2003 (64-bit)http://download.oracle.com/otn/nt/oracle9i/9202/92021Win64_Disk1.ziphttp://download.oracle.com/otn/nt/oracle9i/9202/92021Win64_Disk2.zipOracle9i Database Release 2 Enterprise/Standard Edition for Intel Linuxhttp://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk1.cpio.gzhttp://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk2.cpio.gzhttp://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk3.cpio.gzOracle9i Database Release 2 (9.2.0.4) Enterprise/Standard Edition for Linux x86-64http://download.oracle...

RedHat5.5+Openfiler2.9+Oracle11gR2 RAC+ASM  作者 阿九【转载时请务必以超链接形式标明文章原始出处和作者信息】 一、硬件环境3台 DELL T410塔式服务器 共享存储其中一台安装openfiler系统，作为共享存储使用。二、软件环境操作系统：RedHat 5.5 数据库：Oracle11gR2 存储环境：openfiler2.9三、安装目录规划grid用户：ORACLE_BASE=/u01/app/baseORACLE_HOME=/u01/app/grid oracle用户ORACLE_BASE=/u01/app/oracleORACLE_HOME=$ORACLE_BASE/db11g ASM实例SID：+ASM1 \ +ASM2集群数据库：rac1 \ rac2集群UNIQUE_NAME：rac四、IP地址规划公网IP：IP地址节点1：192.168.4.210节点2：192.168.4.220私有网络： 节点1：172.10.10.10节点2：172.10.10.20VIP： 节点1：192.168.4.101节点2：192.168.4.102SCAN：192.168.4.103共享存储：192.168.4.230 五、配置openfiler共享存储openfiler安装及磁盘配置，请参照文档《51CTO下载-Openfiler2.3.pdf》 六、在linux上配置共享存储6.1、安装iscsi包[root@rac1  ~]# rpm -Uvh iscsi-initiator-utils-6.2.0.871-0.10.el5.x86_64.rpm warning: isc...