Expandmenu Shrunk


  • Category Archives oracle troubleshooting
  • RMAN不准确报错

    1.现象,RMAN备份报错没有文件或者路径
    RMAN>  backup incremental level 0 database format ‘/backup/inc_0.bak’;

    Starting backup at 12-07-05 00:20:38
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 0 datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/system.dbf
    input datafile fno=00004 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/users01.dbf
    input datafile fno=00002 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/undotbs01.dbf
    input datafile fno=00003 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/sysaux01.dbf
    input datafile fno=00007 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/ats001.dbf
    input datafile fno=00005 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak.dbf
    input datafile fno=00006 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak1.dbf
    channel ORA_DISK_1: starting piece 1 at 12-07-05 00:20:38
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/05/2012 00:21:33
    ORA-19502: write error on file “/backup/inc_0.bak”, blockno 42625 (blocksize=8192)
    ORA-27072: File I/O error
    Linux Error: 2: No such file or directory
    Additional information: 4
    Additional information: 42625
    Additional information: 901120
    2.查看信息
    RMAN> report schema;

    Report of database schema

    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    —- ——– ——————– ——- ————————
    1    340      SYSTEM               ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/system.dbf
    2    85       UNDOTBS1             ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/undotbs01.dbf
    3    80       SYSAUX               ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/sysaux01.dbf
    4    210      USERS                ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/users01.dbf
    5    2        HOT_BAK              ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak.dbf
    6    1        HOT_BAK              ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak1.dbf
    7    10       ATS001               ***     /opt/ora_data/FSXYBAK/datafile/add_disk/test/test/ats001.dbf
    可以看到system有340MB,加上其他的估计至少超过当前/目录的337M空间
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda3             7.3G  6.6G  337M  96% /
    /dev/sda1              99M   12M   82M  13% /boot
    none                  119M     0  119M   0% /dev/shm
    /dev/sdb1             3.0G  2.4G  494M  83% /opt/ora_data/FSXYBAK/datafile/add_disk
    空间只剩337M,猜测可能是空间不足
    3.验证备份的合理性
    RMAN> backup validate database;

    Starting backup at 12-07-05 00:31:33
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=144 devtype=DISK
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/system.dbf
    input datafile fno=00004 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/users01.dbf
    input datafile fno=00002 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/undotbs01.dbf
    input datafile fno=00003 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/sysaux01.dbf
    input datafile fno=00007 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/ats001.dbf
    input datafile fno=00005 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak.dbf
    input datafile fno=00006 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak1.dbf
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    Finished backup at 12-07-05 00:32:12               —-/验证说明备份没有问题

    4.尝试增加硬盘空间处理

    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda3             7.3G  6.4G  534M  93% /
    /dev/sda1              99M   12M   82M  13% /boot
    none                  119M     0  119M   0% /dev/shm
    /dev/sdb1             3.0G  2.4G  494M  83% /opt/ora_data/FSXYBAK/datafile/add_disk
    问题解决
    RMAN> run{
    2> backup incremental level 0 database format ‘/backup/inc_0_%U.bak’;}

    Starting backup at 12-07-05 00:34:37
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 0 datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/system.dbf
    input datafile fno=00004 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/users01.dbf
    input datafile fno=00002 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/undotbs01.dbf
    input datafile fno=00003 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/sysaux01.dbf
    input datafile fno=00007 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/ats001.dbf
    input datafile fno=00005 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak.dbf
    input datafile fno=00006 name=/opt/ora_data/FSXYBAK/datafile/add_disk/test/test/hot_bak1.dbf
    channel ORA_DISK_1: starting piece 1 at 12-07-05 00:34:37
    channel ORA_DISK_1: finished piece 1 at 12-07-05 00:35:22
    piece handle=/backup/inc_0_0gnf9l8t_1_1.bak tag=TAG20120705T003437 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
    channel ORA_DISK_1: starting incremental level 0 datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 12-07-05 00:35:24
    channel ORA_DISK_1: finished piece 1 at 12-07-05 00:35:25
    piece handle=/backup/inc_0_0hnf9lab_1_1.bak tag=TAG20120705T003437 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    Finished backup at 12-07-05 00:35:25

     

    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda3             7.3G  6.8G   56M 100% /
    /dev/sda1              99M   12M   82M  13% /boot
    none                  119M     0  119M   0% /dev/shm

    可以看出0级别的增量备份大约有500M的大小

    小结:

    1)这个案例正常来讲RMAN应该报Space not enough这个错误,即空间不足,而实际上是没有文件或者路径,实际上是有的,这样会产生一种误解

    2)可以看出数据库表空间的总和大小,基本和RMAN全备(0 level)差不多小[表空间使用率接近100%]

    3)诊断数据库不单纯的从错误信息入手,更重要的需要结合os,network(rac)以及存储等多方面入手,而感觉就来自于对知识面的广度和深度,这个’语感’是需要累积的长期过程。

     



  • Oracle不能正常连接处理

    [oracle@MSPDBServer ~]$ export ORACLE_SID=MSP
    [oracle@MSPDBServer ~]$ sqlplus / as sysdba

    SQL*Plus: Release 10.2.0.4.0 – Production on Mon Nov 14 16:30:16 2011

    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

    Connected to an idle instance.

    SQL>
    环境变量正确,但是登陆不入数据库

     

    [oracle@MSPDBServer ~]$ ps -ef|  grep oracle | grep -v LOCAL=NO
    oracle    3957 30080  0 May23 ?        03:05:20 /home/oracle/product/10.2.0/MSP/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/home/oracle/product/10.2.0/MSP -Doracle.home=/home/oracle/product/10.2.0/MSP/oc4j -Doracle.oc4j.localhome=/home/oracle/product/10.2.0/MSP/MSPDBServer_MSP/sysman -DEMSTATE=/home/oracle/product/10.2.0/MSP/MSPDBServer_MSP -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/home/oracle/product/10.2.0/MSP/oc4j/j2ee/OC4J_DBConsole_MSPDBServer_MSP/config/jazn.xml -Djava.security.policy=/home/oracle/product/10.2.0/MSP/oc4j/j2ee/OC4J_DBConsole_MSPDBServer_MSP/config/java2.policy -Djava.security.properties=/home/oracle/product/10.2.0/MSP/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/home/oracle/product/10.2.0/MSP/MSPDBServer_MSP -Dsysman.md5password=true -Drepapi.oracle.home=/home/oracle/product/10.2.0/MSP -Ddisable.checkForUpdate=true -Djava.awt.headless=true -jar /home/oracle/product/10.2.0/MSP/oc4j/j2ee/home/oc4j.jar -config /home/oracle/product/10.2.0/MSP/oc4j/j2ee/OC4J_DBConsole_MSPDBServer_MSP/config/server.xml
    root     10548 10503  0 14:23 pts/1    00:00:00 su – oracle
    oracle   10549 10548  0 14:23 pts/1    00:00:00 -bash
    oracle   10647 30080  0  2010 ?        13:12:10 /home/oracle/product/10.2.0/MSP/bin/emagent
    root     12347 12020  0 15:37 pts/3    00:00:00 su – oracle
    oracle   12348 12347  0 15:37 pts/3    00:00:00 -bash
    root     13222 12748  0 16:04 pts/5    00:00:00 su – oracle
    oracle   13223 13222  0 16:04 pts/5    00:00:00 -bash
    oracle   13743     1  0 16:17 ?        00:00:00 /usr/libexec/gconfd-2 13
    root     13810 27852  0 16:18 ?        00:00:00 sshd: oracle [priv]
    oracle   13821 13810  0 16:18 ?        00:00:04 sshd: oracle@notty
    oracle   13822 13821  0 16:18 ?        00:00:00 /usr/bin/gnome-session –display localhost:11.0
    oracle   13850     1  0 16:18 ?        00:00:00 /usr/bin/gnome-keyring-daemon
    oracle   13852     1  0 16:18 ?        00:00:00 /usr/libexec/bonobo-activation-server –ac-activate –ior-output-fd=19
    oracle   13858     1  0 16:18 ?        00:00:00 /usr/libexec/gnome-settings-daemon –oaf-activate-iid=OAFIID:GNOME_SettingsDaemon –oaf-ior-fd=23
    oracle   13862     1  0 16:18 ?        00:00:00 /usr/libexec/gam_server
    oracle   13879     1  0 16:18 ?        00:00:00 gnome-panel –sm-client-id default2
    oracle   13881     1  0 16:18 ?        00:00:00 nautilus –no-default-window –sm-client-id default3
    oracle   13883     1  0 16:18 ?        00:00:00 gnome-volume-manager –sm-client-id default6
    oracle   13890     1  0 16:18 ?        00:00:00 /usr/libexec/gnome-vfs-daemon –oaf-activate-iid=OAFIID:GNOME_VFS_Daemon_Factory –oaf-ior-fd=29
    oracle   13897     1  0 16:18 ?        00:00:00 /usr/libexec/mapping-daemon
    root     13906 27852  0 16:19 ?        00:00:00 sshd: oracle [priv]
    oracle   13909 13906  0 16:19 ?        00:00:00 sshd: oracle@pts/4
    oracle   13910 13909  0 16:19 pts/4    00:00:00 -bash
    oracle   14025     1  0 16:20 ?        00:00:00 pam-panel-icon –sm-client-id default0
    oracle   14027     1  0 16:20 ?        00:00:01 /usr/bin/python /usr/bin/rhn-applet-gui –sm-client-id default4
    oracle   14208 26556  0 16:27 ?        00:00:00 sleep 59
    oracle   14216 12348  0 16:28 pts/3    00:00:00 ps -ef
    oracle   14217 12348  0 16:28 pts/3    00:00:00 grep oracle
    oracle   15937     1  0 Oct14 ?        00:01:11 ora_q001_MSP
    oracle   22109     1  6 Oct13 ?        2-00:09:02 ora_j000_MSP
    oracle   22517     1  6 Oct13 ?        1-23:54:55 ora_j001_MSP
    oracle   22519     1  5 Oct13 ?        1-21:47:05 ora_j002_MSP
    oracle   22521     1  5 Oct13 ?        1-20:09:08 ora_j003_MSP
    oracle   22523     1  6 Oct13 ?        1-23:29:54 ora_j004_MSP
    oracle   22581     1  5 Oct13 ?        1-21:11:45 ora_j005_MSP
    oracle   22583     1  5 Oct13 ?        1-22:23:23 ora_j006_MSP
    oracle   22585     1  6 Oct13 ?        1-23:28:49 ora_j007_MSP
    oracle   22587     1  6 Oct13 ?        1-23:18:27 ora_j008_MSP
    oracle   22589     1  6 Oct13 ?        2-00:32:12 ora_j009_MSP
    oracle   26556     1  0 Sep05 ?        00:03:26 /bin/ksh ./jgetvmstat.sh
    oracle   29339     1  0  2010 ?        00:40:32 /home/oracle//product/10.2.0/MSP//bin/tnslsnr LISTENER -inherit
    注意监监听目录多了个//

    因此链接不了,环境变量故障
    SQL> select instance_name,to_char(startup_time,’yyyy-mm-dd hh24:mi:ss’) from v$instance;

    INSTANCE_NAME    TO_CHAR(STARTUP_TIM
    —————- ——————-
    MSP              2010-12-24 11:13:32

     

     

    [oracle@MSPDBServer /]$ ps -eo pid,tty,user,comm,stime,etime,stat,cmd,comm,command | grep lsn
    15292 pts/3    oracle   grep             17:06       00:00 S+   grep lsn         grep             grep lsn
    29339 ?        oracle   tnslsnr           2010 325-14:27:29 Ssl /home/oracle//pr tnslsnr          /home/oracle//product/10.2.0/MSP//bin/tnslsnr LISTENER -inh

    时间是2010 第325天的14:27:29秒
    2010-11月21号

    比实例早启动

     

     

     

     

     
    To see every process on the system using standard syntax:
       ps -e
       ps -ef
       ps -eF
       ps -ely

    To see every process on the system using BSD syntax:
       ps ax
       ps axu

    To print a process tree:
       ps -ejH
       ps axjf

    To get info about threads:
       ps -eLf
       ps axms

    To get security info:
       ps -eo euser,ruser,suser,fuser,f,comm,label
       ps axZ
       ps -eM

    To see every process except those running as root (real & effective ID)
       ps -U root -u root -N

    To see every process with a user-defined format:
       ps -eo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan:14,comm
       ps axo stat,euid,ruid,tty,tpgid,sess,pgrp,ppid,pid,pcpu,comm
       ps -eopid,tt,user,fname,tmout,f,wchan

     

     

     



  • Ora-12720

    平台AIX
    Oracle10g
    raw
    由于raw文件过小,动态调节,导致某个online logfile所在的lv为close/sync但是能切换,重启数据报告找不到文件
    原因
    rrac_undotb1_4g为调节为16g同时名称更改为rrac_undotbs1_16g,因此需要重建控制文件(成本较高),或者重命名数据文件

    1.重建

    SQL>startup restrict nomount;
    ORA-12720
    ERROR at line 1:
    ORA-01501: CREATE DATABASE failed
    ORA-12720: operation requires database is in EXCLUSIVE mode

     
    you need to set cluster database = false

    alter system set cluster_database=false;

    2.重命名文件
    1)关闭集群的instance
    srvctl stop database -d peppdb
    2)登陆一个节点peppdb1
    sqlplus / as sysdba
    startup mount;
    3)重命名文件(由于是raw设备,不需要move或者cp数据文件镜像,直接命名即可)
    alter database rename file ‘/dev/rrac_undotbs1_4g’ to ‘/dev/rrac_undotb1_16g’;
    4)开始实例(测试正常)
    alter database open;
    shutdown immediate;
    5)重启集群
    srvctl start database -d peppdb

     




香港马会开奖记录|香港马会开奖资料|香港马会开奖现场|香港马会走势图|香港马会开奖结果直播|香港马会n730|