In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Monday, October 25, 2010

RMAN-03002 : ORA-01119: error in creating database file ORA-27038:

this is one of the interview question
your database corrupted/lost after adding a datafile to database you are only  having the backup of database before adding the datafile, the newly added datafile is not available in the backupset , what will happen how will you recover? Is the new added datafile will be available after the restore backup command?

the answer is yes the control contains the information about the newly added datafile so RMAN creates a new datafile with the same name and location and applies logs to the datafile to recover. it is similar to sql statement alter database create datafile statement.
here is an demo.
SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf


[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [cdbs1] ? rev1
[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 25 20:48:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: REV1 (DBID=1886963043)

RMAN> backup full database plus archivelog;


Starting backup at 25-OCT-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=109 stamp=733321071
input archive log thread=1 sequence=19 recid=110 stamp=733321246
input archive log thread=1 sequence=20 recid=111 stamp=733321261
input archive log thread=1 sequence=21 recid=112 stamp=733351761
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0slrc3qs_1_1 tag=TAG20101025T204929 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 25-OCT-10

Starting backup at 25-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/rev1/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/rev1/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/rev1/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0tlrc3r5_1_1 tag=TAG20101025T204939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-OCT-10

Starting backup at 25-OCT-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=22 recid=113 stamp=733351877
channel ORA_DISK_1: starting piece 1 at 25-OCT-10
channel ORA_DISK_1: finished piece 1 at 25-OCT-10
piece handle=/u01/app/oracle/backup/0ulrc3u7_1_1 tag=TAG20101025T205118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-OCT-10

Starting Control File and SPFILE Autobackup at 25-OCT-10
piece handle=/u01/app/oracle/flash_recovery_area/REV1/autobackup/2010_10_25/o1_mf_s_733351882_6dc83mcy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-OCT-10

RMAN>

SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/rev1/users02.dbf' SIZE 2m;

Tablespace altered.

SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf
/u01/app/oracle/oradata/rev1/users02.dbf

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 25 21:03:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: REV1 (DBID=1886963043)

RMAN> restore database;

Starting restore at 25-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

creating datafile fno=5 name=/u01/app/oracle/oradata/rev1/users02.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/25/2010 21:14:13
ORA-01119: error in creating database file '/u01/app/oracle/oradata/rev1/users02.dbf'
ORA-27038: created file already exists
Additional information: 1


SQL> ! mv /u01/app/oracle/oradata/rev1/users02.dbf /u01/app/oracle/user02.dbf

RMAN> restore database;

Starting restore at 25-OCT-10
using channel ORA_DISK_1

creating datafile fno=5 name=/u01/app/oracle/oradata/rev1/users02.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/rev1/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/rev1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/rev1/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/rev1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/0tlrc3r5_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/0tlrc3r5_1_1 tag=TAG20101025T204939
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 25-OCT-10

RMAN> recover database;

Starting recover at 25-OCT-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 25-OCT-10

RMAN>

RMAN> sql 'alter database open';

sql statement: alter database open
RMAN> exit


Recovery Manager complete.
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf
/u01/app/oracle/oradata/rev1/users02.dbf


hope, this will helps you.

reference:
http://forums.oracle.com/forums/thread.jspa?messageID=3687911

No comments:

 
Share/Bookmark