Recovery from Loss of datafile without RMAN:
1. Rename datafile:
[root@rh4as-64 BTTEST1]# ls
control01.ctl example01.dbf redo02.log system01.dbf users02.dbf
control02.ctl mytemp_01.tmp redo03.log undotbs01.dbf
control03.ctl redo01.log sysaux01.dbf users01.dbf
[root@rh4as-64 BTTEST1]# mv users02.dbf users02.dbf_old
[root@rh4as-64 BTTEST1]# ls
control01.ctl example01.dbf redo02.log system01.dbf users02.dbf_old
control02.ctl mytemp_01.tmp redo03.log undotbs01.dbf
control03.ctl redo01.log sysaux01.dbf users01.dbf
[root@rh4as-64 BTTEST1]#
2. Startup database to find Oracle error during Mount stage:
sys@BTTEST1> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@BTTEST1> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 230687544 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u02/app/oracle/oradata/BTTEST1/users02.dbf'
3. Plan for recovery—Make sure you have a Backup(COLD)
Restore the Datafile from your backup
[oracle@rh4as-64 ~]$ cd /u02/app/oracle/bkp_worman/BTTEST1
[oracle@rh4as-64 BTTEST1]$ ls
control01.ctl example01.dbf redo02.log system01.dbf users02.dbf
control02.ctl mytemp_01.tmp redo03.log undotbs01.dbf
control03.ctl redo01.log sysaux01.dbf users01.dbf
[oracle@rh4as-64 BTTEST1]$ cp users02.dbf /u02/app/oracle/oradata/BTTEST1/
4. Open Database
sys@BTTEST1> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 230687544 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
sys@BTTEST1>
Recovery from Loss of Datafile(Database online) without RMAN:
1. sys@BTTEST1> select file_name, tablespace_name, status from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/u02/app/oracle/oradata/BTTEST1/system01.dbf
SYSTEM AVAILABLE
/u02/app/oracle/oradata/BTTEST1/undotbs01.dbf
UNDOTBS1 AVAILABLE
/u02/app/oracle/oradata/BTTEST1/sysaux01.dbf
SYSAUX AVAILABLE
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME STATUS
------------------------------ ---------
/u02/app/oracle/oradata/BTTEST1/users01.dbf
USERS AVAILABLE
/u02/app/oracle/oradata/BTTEST1/example01.dbf
EXAMPLE AVAILABLE
/u02/app/oracle/oradata/BTTEST1/users02.dbf
USERS AVAILABLE
6 rows selected.
2. Take USERS tablespace offline
sys@BTTEST1> alter tablespace USERS offline;
Tablespace altered.
3. Copy datafile from backup location
4. Recover tablespace users and bring User tablespace online.
sys@BTTEST1> recover tablespace USERS;
Media recovery complete.
sys@BTTEST1> alter tablespace USERS online;
Tablespace altered.
sys@BTTEST1>
End of Recovery
Recovery from Loss of Datafile( When Database is closed and open during recovery) without RMAN:
1. Rename Datafile.
[oracle@rh4as-64 BTTEST1]$ mv users02.dbf users02.dbf_old
[oracle@rh4as-64 BTTEST1]$ ls
control01.ctl example01.dbf redo02.log system01.dbf users02.dbf_old
control02.ctl mytemp_01.tmp redo03.log undotbs01.dbf
control03.ctl redo01.log sysaux01.dbf users01.dbf
[oracle@rh4as-64 BTTEST1]$
2. Shutdown database and startup
SYS@BTTEST1> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01110: data file 6: '/u02/app/oracle/oradata/BTTEST1/users02.dbf'
SYS@BTTEST1> select name, status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/system01.dbf
SYSTEM
/u02/app/oracle/oradata/BTTEST1/undotbs01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users01.dbf
RECOVER
/u02/app/oracle/oradata/BTTEST1/example01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/users02.dbf
RECOVER
6 rows selected.
SYS@BTTEST1>
3. Restore datafile users2
[oracle@rh4as-64 BTTEST1]$ cp users02.dbf /u02/app/oracle/oradata/BTTEST1/
4. Recover tablespace USERS.
SYS@BTTEST1> recover tablespace USERS;
Media recovery complete.
SYS@BTTEST1> alter tablespace USERS ONLINE;
Tablespace altered.
5. Check status
SYS@BTTEST1> select name, status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/system01.dbf
SYSTEM
/u02/app/oracle/oradata/BTTEST1/undotbs01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/example01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/users02.dbf
ONLINE
6 rows selected.
Restore datafile that was never backed up:
1. List all the datafiles that re in database:
SYS@BTTEST1> select name, status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/system01.dbf
SYSTEM
/u02/app/oracle/oradata/BTTEST1/undotbs01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/example01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/users02.dbf
ONLINE
6 rows selected.
SYS@BTTEST1>
2. Add new datafile to USERS tablespace, the new datafile isn’t included in backup:
[oracle@rh4as-64 BTTEST1]$ pwd
/u02/app/oracle/bkp_worman/BTTEST1
[oracle@rh4as-64 BTTEST1]$ ls
control01.ctl example01.dbf redo02.log system01.dbf users02.dbf
control02.ctl mytemp_01.tmp redo03.log undotbs01.dbf
control03.ctl redo01.log sysaux01.dbf users01.dbf
[oracle@rh4as-64 BTTEST1]$
Create DBF users03.dbf:
SYS@BTTEST1> alter tablespace USERS add datafile '/u02/app/oracle/oradata/BTTEST1/users03.dbf' size 110M;
Tablespace altered.
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users03.dbf
ONLINE
There is no backup of users03.dbf
3. Rename users03.dbf and Put USERS tablespace offline.
SYS@BTTEST1> alter tablespace USERS offline;
alter tablespace USERS offline
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u02/app/oracle/oradata/BTTEST1/users03.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@BTTEST1> alter tablespace USERS offline immediate; Have to use immediate because Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.
Tablespace altered.
SYS@BTTEST1>
4. Check status from v$datafile:
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users01.dbf
RECOVER
/u02/app/oracle/oradata/BTTEST1/example01.dbf
ONLINE
/u02/app/oracle/oradata/BTTEST1/users02.dbf
RECOVER
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users03.dbf
RECOVER
5. Check satus in v$recover_file
SYS@BTTEST1> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 OFFLINE OFFLINE
889545
21-JAN-10
6 OFFLINE OFFLINE
889545
21-JAN-10
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
FILE NOT FOUND 0
SYS@BTTEST1>
6. Re-create datafile using the command below:
SYS@BTTEST1> alter database create datafile '/u02/app/oracle/oradata/BTTEST1/users03.dbf';
Database altered.
SYS@BTTEST1> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 OFFLINE OFFLINE
889545
21-JAN-10
6 OFFLINE OFFLINE
889545
21-JAN-10
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
890175
21-JAN-10
SYS@BTTEST1> recover tablespace USERS;
Media recovery complete.
SYS@BTTEST1> alter tablespace USERS ONLINE;
Tablespace altered.
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/BTTEST1/users03.dbf
ONLINE
LOSS OF ALL CONTROLFILE:
1. Rename all the controlfiles\
[oracle@rh4as-64 BTTEST1]$ mv control01.ctl control01.ctl_old
[oracle@rh4as-64 BTTEST1]$ mv control02.ctl control02.ctl_old
[oracle@rh4as-64 BTTEST1]$ mv control03.ctl control03.ctl_old
[oracle@rh4as-64 BTTEST1]$ ls
control01.ctl_old example01.dbf redo02.log system01.dbf users02.dbf
control02.ctl_old mytemp_01.tmp redo03.log undotbs01.dbf users03.dbf
control03.ctl_old redo01.log sysaux01.dbf users01.dbf
2. startup database
SYS@BTTEST1> startup
Total System Global Area 419430400 bytes
Fixed Size 1219784 bytes
Variable Size 239076152 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@BTTEST1>
3. SYS@BTTEST1> @/tmp/vij.sql
Control file created.
SYS@BTTEST1> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@BTTEST1> alter database open
No comments:
Post a Comment