Updates from December, 2009 Toggle Comment Threads | Keyboard Shortcuts

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

    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:13 am on November 6, 2009 Permalink | Reply  

    Datafile 1 (ckpscn 1653538474) is orphaned on incarnation#=2 

    Symptom

    Wed Nov 04 01:48:48 2009
    Managed Standby Recovery starting Real Time Apply
    Warning: Recovery target destination is in a sibling branch
    of the controlfile checkpoint. Recovery will only recover
    changes to datafiles.
    Datafile 1 (ckpscn 1653538474) is orphaned on incarnation#=2
    MRP0: Background Media Recovery terminated with error 19909
    Wed Nov 04 01:48:49 2009
    Errors in file d:\oracle\product\10.2.0\admin\dbprd\bdump\dbdr_mrp0_3224.trc:
    ORA-19909: datafile 1 belongs to an orphan incarnation
    ORA-01110: data file 1: ‘Z:\ORADATA\DBPRD\SYSTEM01.DBF’

    Description
    This is another scenario when your dataguard physical standby database incremented the incarnation bu RMAN during recovering state.

    (More …)

     

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

    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
     

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

    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:   

    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 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: ,   

    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:   

    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 …)

     

← Older posts

Newer 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