User Managed Hot Backup and Recovery of lost of data file

Example of User Managed Hot Backup:
-----------------------------------------

1)Determine which data file you need to backup.
SQL> select file_name ,tablespace_name from dba_data_files
2 /

FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata2/7.dbf USERS
/oradata2/data1/dbase/users01.dbf USERS
/oradata2/data1/dbase/sysaux01.dbf SYSAUX
/oradata2/data1/dbase/undotbs01.dbf UNDOTBS1
/oradata2/data1/dbase/system01.dbf SYSTEM
/oradata2/6.dbf USERS
6 rows selected.

2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/shaik

SQL> !cp /oradata2/7.dbf /oradata2/shaik

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/shaik

SQL> ALTER TABLESPACE SYSAUX END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/shaik

SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/shaik

SQL> ALTER TABLESPACE SYSTEM END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/6.dbf /oradata2/shaik

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

3)Take a backup of your control file also.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/shaik/control.ctl';
Database altered.


Now, I have taken backup.With this backup I will work for next recovery purpose. In this example I will show if someone has accidentally drop one datafile what will be my procedure.

Suppose someone accidentally two files.

SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> !rm /oradata2/6.dbf

Whenever you want to do any operation it will raise error like following,

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


User Managed Recovery of Datafile when your database is online (Archive Log Mode)
---------------------------------------------------------------------------
1)Make the affected tablespace offline.
SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Tablespace altered.

2)Copy the lost datafile from backup into the location of the datafile.

SQL> !cp /oradata2/shaik/users01.dbf /oradata2/data1/dbase/
SQL> !cp /oradata2/shaik/6.dbf /oradata2

3)Recover the tablespace.
SQL> RECOVER TABLESPACE USERS;
Media recovery complete.

4)Make the Tablespace online.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.

While database is offline and can't start it will show following message.

SQL> !rm /oradata2/data1/dbase/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'

1)In order to see which files need media recovery issue,

SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
; 2 3 4 5 6

DF# DF_NAME TBSP_NA STATUS ERROR CHANGE#
---- ----------------------------------- ------- ------- ---------- ---------
4 /oradata2/data1/dbase/users01.dbf USERS ONLINE FILE NOT 0
FOUND

2)Copy the lost file from backup to destination.

SQL> !cp /oradata2/shaik/users01.dbf /oradata2/data1/dbase/users01.dbf

3)Recover the file and open the database.

SQL> RECOVER DATAFILE 4;
Media recovery complete.
SQL> ALTER DATABASE OPEN;

Database altered.


Note: If the location is damaged permamnent to make datafile in another location use,
SQL>ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' TO
'/disk2/users01.dbf';

If you do not have a backup of a specific datafile but you have archived log since the datafile creation then you can recover that datafile by creating an empty file by ALTER DATABASE CREATE DATAFILE and then simply perform recovery of the datafile.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more