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”
Continue reading “Recovering Through an Added Datafile with a Backup Control File: Scenario”

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.

Continue reading “Datafile 1 (ckpscn 1653538474) is orphaned on incarnation#=2”

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

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
Continue reading “Remote Archival to Standby Site Fails with ORA-01031”

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.

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;