Tagged: oracle RSS

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

    Remote Archival to Standby Site Fails with ORA-01031 

    Archiver process at  primary database fails with following errors in Alert.log:

    Fri Jan 27 22:56:57 2006
    Error 1031 received logging on to the standby
    Fri Jan 27 22:56:57 2006
    Errors in file /home/oracle/product/10.2.0/db_1/admin/ORA10G2/bdump/ora10g2_arc1_7527.trc:
    ORA-01031: insufficient privileges
    PING[ARC1]: Heartbeat failed to connect to standby ‘standby’. Error is 1031.
    Fri Jan 27 23:01:10 2006
    (More …)

     
  • levin 10:38 pm on October 27, 2009 Permalink | Reply
    Tags: oracle   

    Resize undo tablespace 10gR2 RAC 

    On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.

    For each instance :

    – Create new undo tablespace with smaller size.

    SQL> CREATE undo tablespace UNDOTBS3 datafile '/oradata/undotbs3.dbf' SIZE 1G;

    – Set new tablespace as undo_tablespace

    SQL> ALTER system SET undo_tablespace=UNDOTBS3 sid=RAC1;

    – Drop the old tablespace.

    SQL> DROP tablespace UNDOTBS1 including contents.

    Repeat those steps if you want preserve and reuse UNDOTBS1 as the name.

    NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

     
  • levin 1:21 pm on October 16, 2009 Permalink | Reply
    Tags: oracle,   

    oracle grant a user to another user’s objects 

    BEGIN
      FOR x IN (SELECT object_name, object_type FROM all_objects WHERE owner='SOURCE_USER' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE')) LOOP
        IF x.object_type IN ('TABLE') THEN
          EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
        END IF;
        IF x.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
          EXECUTE IMMEDIATE'GRANT EXECUTE ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
        END IF;
        IF x.object_type IN ('SEQUENCE') THEN
          EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
        END IF;
      END LOOP;
    END;
     
  • levin 2:21 am on October 15, 2009 Permalink | Reply
    Tags: oracle,   

    show table size in oracle 

    exec it, enter desired schema name and go!

    (More …)

     
  • levin 1:05 am on September 30, 2009 Permalink | Reply
    Tags: oracle   

    RMAN restore of database fails with ORA-01180: Cannot create datafile 1 

    This problem can occur on any platform.
    Symptoms

    After restoring the controlfile, rman restore of database fails with :

    RMAN-03002: failure of restore command at 09/14/2006 01:39:09
    ORA-01180: cannot create datafile 1
    ORA-01110: data file 1: ‘/ /system01.dbf’
    Cause
    (More …)

     
  • levin 3:53 pm on September 28, 2009 Permalink | Reply
    Tags: oracle   

    ORA-27191: sbtinfo2 returned error 

    Try to run “sbttest SBT_TAPE” over and over until the command pass the validation, it’s 99% due to file/directory permssion issues, make sure oracle have sufficient right to access backup library.

     
  • levin 5:17 pm on September 2, 2009 Permalink | Reply
    Tags: oracle   

    MRP0: Background Media Recovery terminated with error 19909 

    How to solve ORA-19909 error when re-sync dataguard, when the standby database shutdown ungracefully, sometime you may found an error when you put the standby database back online, in this scenario, I try to active the standby database and flash back to standby mode in order to let standby database re-apply archive logs again.

    Error message as below

    MRP0: Background Media Recovery terminated with error 19909

    ORA-19909: datafile 1 belongs to an orphan incarnation

    (More …)

     
    • Givre 10:02 pm on August 18, 2010 Permalink | Reply

      Hi !
      Thank you for this informations.

      I had the same problem on my Physical stanbdy , and now all it’s ok :)
      I dont have Gapped archive log and the process MRP is now running correctly

      I dont understand why with this senario, the database re-apply archive logs again..
      Witch archive logs ? since what time ?

      Can you givre me more explication about this scenario ?

      Thanks a lot.

      Regards,
      Benoit

    • levin 9:53 am on August 19, 2010 Permalink | Reply

      Hi Benoit,

      Thanks for your comment, I got this error twice after power outage in the DR site , and it only affect on Windows 2003 platform too. At that moment I couldn’t found any resolution from metalink, so I just try to resolve the problem by reactivate the standby db and put it back to physical standby. Luckily the problem disappeared, this is just a workaround and may not be a standard resolution.

      According to the gapped log, for my case, I found this is log 2221 is missing from standby database archive directory, and the archive date is 2 days ago. 19909 error happens on 1 day after this log.

      When I try to apply the workaround and re-establish the sync process, the primary db appeared this 2221 log was already shipped to standby db. However, no such log can be found. Since the archive log retention is set to 7 day, So i can able to copy this log from primary db to standby db in order to recover the gap manually.

    • Givre 3:37 pm on August 19, 2010 Permalink | Reply

      Hi,
      This morning, the problem is still here :-(

      I’m on Unix Machine (AIX) and i have got the same error like you

      MRP0: Background Media Recovery terminated with error 19909
      ORA-19909: datafile 1 belongs to an orphan incarnation
      ORA-01110: data file 1: ‘/xxxx/xxxx/sapdata1/system_1/system.data1′

      I think i will copy the datafile from Primary node.

      Have nice day.
      Benoit

    • levin 3:49 am on August 20, 2010 Permalink | Reply

      Do your standby db backup with RMAN? if yes, take a look on incarnation. Try to reset it to earlier incarnation and then start the standby db again.

  • levin 3:25 pm on August 24, 2009 Permalink | Reply
    Tags: oracle   

    How to Perform. a Healthcheck on the Database 

    Table of Contents
    —————–

    1. Introduction
    2. Parameter file
    3. Controlfiles
    4. Redolog files
    5. Archiving
    6. Datafiles
    6.1 Autoextend
    6.2 Location

    (More …)

     
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