Skip to content

Archive for

Jan 3

3 Comments

How to recover datafile not backed up

Recently i just added one datafile for a tablespace and later, had a media failure occur before I backuped recently datafile.In this case , How do we recover.

We have previous Good Database backup.

Redo files needed upto Datafiles created.

If you have a current control file, we can restore , recover datafile & tablespace or database level.

Now See Below Example :

before I just backuped database with archivelogs.

Now I’m go to Add datafile to a existing tablespace.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS02.dbf' SIZE 50M;

Tablespace altered. 

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Now I moved Recently added DBF file to other location

d:\backup\restorenew>move testtbs02.dbf d:\backup
        1 file(s) moved.

d:\backup\restorenew>

Shutdown Database & Startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: 'D:\BACKUP\RESTORENEW\TESTTBS02.DBF'

Now Recover tablespace using RMAN

RMAN> restore tablespace testtbs;

Starting restore at 03-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK

creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-JAN-11

RMAN> recover tablespace testtbs;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

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

Finished recover at 03-JAN-11

RMAN> alter database open;

database opened

Let me check datafile

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Using A backup Control file

If we’re using a backup control file that has no information about the datafile, then we must restore and recover at the database level.

I just added another datafile also.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS03.dbf' SIZE 50M;

Tablespace altered.

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                331350480 bytes
Database Buffers             197132288 bytes
Redo Buffers                   5804032 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

recovery area destination: C:\app\oracle\mazar\flash_recovery_area
database name (or database unique name) used for search: AZARDB
channel ORA_DISK_1: AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20110103
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
output file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL
Finished restore at 03-JAN-11

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 03-JAN-11
Starting implicit crosscheck backup at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 03-JAN-11

Starting implicit crosscheck copy at 03-JAN-11
using channel ORA_DISK_1
Crosschecked 17 objects
Finished implicit crosscheck copy at 03-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2X3V22_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2XL3M4_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738773797_6KGCCPPK_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738777392_6KGGW2R7_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851503_6KJQ80HG_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851640_6KJY64RH_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_S_738813797_6KHLFP6B_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\BACKUP\ADDME01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\BACKUP\RCRMAN01.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\BACKUP\USERSTBS01.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\BACKUP\UNDOTBS02.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\BACKUP\TESTDB001.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\BACKUP\TESTTB1.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:05
Finished restore at 03-JAN-11

RMAN> recover database;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_
.ARC
archived log for thread 1 with sequence 35 is already on disk as file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG
archived log file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_.ARC thread=1 sequence=34
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=13 name=D:\BACKUP\RESTORENEW\TESTTBS03.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
media recovery complete, elapsed time: 00:00:06
Finished recover at 03-JAN-11

RMAN> alter database open resetlogs;

database opened
SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS02.DBF
D:\BACKUP\RESTORENEW\TESTTBS03.DBF
D:\BACKUP\RESTORENEW\TESTTBS01.DBF

Jan 3

12 Comments

ORA-30012: undo tablespace ‘UNDOTBS02’ does not exist or of wrong type

When i try startup my test db , the following error will be occur.

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type
Process ID: 14328
Session ID: 125 Serial number: 5

Actually what i’am wrong here, I created new undo tablespace for existing tablespace & then I dropped existing undo tablespace. I startup using pfile and worked, later i shutdown database without creating spfile for existing db. SO I just startup using spfile, the above error will be shown.

Solution :

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
20 ADDME YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
9 RCRMAN YES NO YES
10 USERS_TBS YES NO YES
5 UNDOTBS2 YES NO YES
19 TESTTBS YES NO YES
14 TESTTB YES NO YES

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
15 TESTDB YES NO YES

12 rows selected.

SQL> create pfile='d:\backupnew\initazardb.ora' from spfile;

File created.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Edit Pfile UNDO_TABLESPACE PARAMETER
SQL> startup pfile='d:\backupnew\initazardb.ora';
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile;

File created.

SQL> create spfile from pfile='d:\backupnew\initazardb.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>