Tagged: oracle RSS

  • levin 11:08 pm on February 19, 2011 Permalink | Reply
    Tags: oracle, rman   

    Getting ORA-01180 error during database restoration 

    Why

    Having restore database from rman but got a ORA-01180 error, it happen often because your backup file source directory is different then original backup path.

    Let’s try

    Restore pfile and recreate all necessary directory eg: archive log, admin, a/b/c/udump, datafile… every directory you specified in the pfile

    Copy your backup pfile to %ORACLE_HOME%\dbs\

    sqlplus / as sysdba
sqlplus> startup nomount pfile='%ORACLE_HOME%\dbs\initORAINST.ora'

    Restore controlfile

    rman target /
RMAN> restore controlfile from 'D:\path\to\controlfile.bak'

    Restore database

    RMAN> alter database mount;
RMAN> restore database;

    Then you got a similar message as below, because originally your backup path is somewhere different then your current backup file source path.

    creating datafile fno=1 name=D:\ORADATA\ORAINST\SYSTEM01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/18/2011 16:55:06
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'D:\ORADATA\ORAINST\SYSTEM01.DBF'

    Try this to re-catalog your backup files then re-run the restore

    rman> catalog start with 'D:\backup\source'
     

  • levin 2:48 am on June 12, 2010 Permalink | Reply
    Tags: oracle   

    Connect with local sysdba failed when database start up by CRS 

    What happen?

    A database started up by CRS successfully, but you can’t connect it by local sysdba “sqlplus / as sysdba”, however tnsping or connect with connection string “sqlplus system@prddb” works fine.
    Later, you tried to start the database alone manually, it works fine with both connection method.

    You inspected every crs log and alertlog but it look seems fine.

    Oops, why it inconsistent?

    When clusterware trying to startup your oracle, it’ll use $ORACLE_HOME/bin/racgwrap , so make sure your system environment setting is same as the ORACLE_HOME setting in racgwrap script.

     

  • levin 5:59 pm on April 13, 2010 Permalink | Reply
    Tags: oracle   

    Oracle PRKN-1008 Error 

    PRKH-1010 : Unable to communicate with CRS services.
PRKH-1000 : Unable to load the SRVM HAS shared library
PRKN-1008 : Unable to load the shared library "srvmhas10"

..... Can't find library srvmhas (libsrvmhas10.a or .so) or any library file

    Possibility

    • File is missing in LIBPATH
    • Wrong LD_LIBRARY_PATH being set.
    • The .so , . a file is unreadable by oracle user in CRS_HOME/lib | lib32 or ORACLE_HOME/lib | lib32
    • “ar” command was broken
     

  • levin 2:30 pm on December 31, 2009 Permalink | Reply
    Tags: oracle,   

    How to determine undo usage in Oracle 

    Overview

    Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

    This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

    Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

    With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
    (More …)

     

    • Chandrasekar Sivasamy 9:12 pm on July 3, 2010 Permalink | Reply

      Thank you so much. It’s very useful. I have cleared my doubt.

    • Raj Gopal 10:12 pm on January 20, 2011 Permalink | Reply

      The query to calculate the required undo tablespace size is very helpful.

  • levin 2:10 pm on December 31, 2009 Permalink | Reply
    Tags: oracle,   

    How to check tablespace usage in Oracle 

    Check each schema usage on tablespaces.

    set linesize 300
set pagesize 20
set feedback off

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
from dba_segments a, (select tablespace_name, sum(bytes) total_space
                      from dba_data_files
                      group by tablespace_name) b
where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, a.owner, b.total_space/1024/1024
order by a.tablespace_name, a.owner;

    Check overall tablespaces usage.

    set linesize 300
set pagesize 20
set feedback off

select t1.tsp_name, sysdate,
       round(t1.total_space/1024/1024,2) "Total (MB)",
       round((t1.total_space-t2.free_bytes)/1024/1024,2) "Used (MB)",
       round(t2.free_bytes/1024/1024,2) "Free (MB)",
       round(t2.free_bytes/t1.total_space*100,2) "% Free",
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,'*',' ') D,
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,'*',' ') E
from (select tablespace_name tsp_name, sum(bytes) total_space
      from sys.dba_data_files
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t1,
     (select tablespace_name tsp_name, sum(bytes) free_bytes
      from sys.dba_free_space
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t2
where t1.tsp_name = t2.tsp_name
order by tsp_name;
     

  • levin 11:21 am on December 31, 2009 Permalink | Reply
    Tags: oracle,   

    How to find active SQL statements in Oracle 

    Everyone has a favorite script to find the active SQL statements. Here is one of mine, it’s RAC friendly too..

    
     set serverout on size 999999
     declare
     begin
     dbms_output.put_line(' ');
     dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************');
     for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> 'BACKGROUND'
     and vsw.event NOT IN ('rdbms ipc message'
     ,'smon timer'
     ,'pmon timer'
     ,'SQL*Net message from client'
     ,'lock manager wait for remote message'
     ,'ges remote message'
     ,'gcs remote message'
     ,'gcs for action'
     ,'client message'
     ,'pipe get'
     ,'Null event'
     ,'PX Idle Wait'
     ,'single-task message'
     ,'PX Deq: Execution Msg'
     ,'KXFQ: kxfqdeq - normal deqeue'
     ,'listen endpoint status'
     ,'slave wait'
     ,'wakeup time manager'))
     loop
     begin
     dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
     dbms_output.put_line('************************* ******************** ****** *********** ********** *************');
     dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
     '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(' SQLText ');
     dbms_output.put_line('****************************************************************');
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************');
     dbms_output.put_line(' ');
     end;
     /

    http://www.oracle.com/technology/oramag/code/tips2004/062104.html

     

  • levin 3:06 am on December 24, 2009 Permalink | Reply
    Tags: oracle   

    Recovering Through an Added Datafile with a Backup Control File: Scenario 

    Background:

    Restore a production database with 1 week ago tape backup media and recover / roll forward archived log files until up-to-date. However a new datafile just added to a tablespace in yesterday. As a result, media recovery stopped when applying the archive log files which contains add tablespace operation.

    Scenario:

    1. Restored a database with backup control file from tape.
    2. Roll forward recover database with archived log files.
    3. Archive log files contain “Add datafile to tablespace information”
    (More …)

     

    • Natalie White 9:09 am on May 26, 2010 Permalink | Reply

      Data Recovery is a very costly option that is why you should always check your storage media for any signs of wear and tear..;`

    • Allison Hill 10:51 pm on July 15, 2010 Permalink | Reply

      data recovery is a very costly option, always make a regular backups.:.’

    • Abigail Clark 12:00 am on August 31, 2010 Permalink | Reply

      data recovery is very very expensive so do make regular backups of important files,-:

    • Vai 1:20 am on October 7, 2010 Permalink | Reply

      Hello Levin,
      Thank you for the detailed solution provided. We had the scenario like this – During annual Disaster Recovery Drill, we restored the latest successful online backup on a server and started applying logs. There had been tablespace extension activity on the next day of the successful backup. Due to this, some of the redo logs had that information. During recovery, we got error that File# added to control file as ‘unnamedNNN’. This was strange as the structure was available for the newly added data files. Though we had error same as shown in scenario 1, only renaming did not help as recovery gave error = ORA-19909: datafile NNN belongs to an orphan incarnation. We created the files as mentioned in other solution and it worked. Thank you for your help.

      • levin 3:03 am on October 7, 2010 Permalink | Reply

        Hi Vai, sounds you had this error during rman restore operation right?

    • Metallic Sandals : 12:23 am on October 23, 2010 Permalink | Reply

      i had my 1TB hard drive crashed and data recovery was horrendously expensive,.”

    • Mavis Brindamour 5:47 am on October 26, 2010 Permalink | Reply

      Hello. See my site

    • Summer Camps 10:17 am on October 30, 2010 Permalink | Reply

      Maybe you should make changes to the post name Recovering Through an Added Datafile with a Backup Control File: Scenario | what the blog? to something more suited for your blog post you write. I liked the post nevertheless.

      • Aplink 10:30 am on October 30, 2010 Permalink | Reply

        what’s your suggestion?

    • RFID Reader 12:55 am on December 13, 2010 Permalink | Reply

      of course data entry services are very expensive that is why always make a backup of your files -,”

  • levin 1:22 am on November 4, 2009 Permalink | Reply
    Tags: oracle   

    Oracle 10.2.0.4 Startup fail by RAC on AIX 

    If you just upgrade Oracle database to 10.2.0.4 in your RAC clusterware environment on AIX 5L , you may instantly encountered  your oracle database cannot startup by crs but manual startup was fine.

    chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE root
     

← Older posts

c
compose new post
j
next post/next comment
k
previous post/previous comment
r
reply
e
edit
o
show/hide comments
t
go to top
l
go to login
h
show/hide help
esc
cancel