DB스토리

에러난 파일 찾아주는 스크립트

select name, error

from v$datafile

join v$recover_file

using (file#)

/

online 상태(open 상태) 에서 복구

---1 고장내기

 rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53cr5v92_.dbf

S SYS> create table x(x date) tablespace example;

Table created.

에러 안나고 성공시
S SYS> alter system checkpoint;

S SYS> @errFile

NAME

--------------------------------------------------------------------------------------------------------------

ERROR

-----------------------------------------------------------------

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53cr5v92_.dbf

FILE NOT FOUND

-- 해당 tablespace off 라인으로 바꾸기

S SYS> select ts# from v$datafile where name='/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53cr5v92_.dbf';

       TS#

----------

         6

S SYS> select name from v$tablespace where ts#=6;

NAME

------------------------------

EXAMPLE

S SYS> alter tablespace EXAMPLE offline for recover;

Tablespace altered.

--- 3. 고치기

RMAN> restore tablespace example;

Starting restore at 15-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53cr5v92_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_06_12/o1_mf_nnndf_TAG20090612T161751_534090wh_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_06_12/o1_mf_nnndf_TAG20090612T161751_534090wh_.bkp tag=TAG20090612T161751
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 15-JUN-09

 RMAN> recover tablespace example;

Starting recover at 15-JUN-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 280 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_53cnlws7_.arc
archive log thread 1 sequence 281 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_281_53cp0068_.arc
archive log thread 1 sequence 282 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_282_53cr8978_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_53cnlws7_.arc thread=1 sequence=280
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-JUN-09

S SYS> alter tablespace example online;

Tablespace altered.

 S SYS> alter database open;

Database altered.

--- 4. 확인

 S SYS> create table xx(x date) tablespace example;

Table created.

S SYS> @errFile

no rows selected

control file  손실시 .. 운영을 위해서는 최소 한개가 필요하지만 nomount에서 mount로 가기 위해서는 모든 control file  필요.

 ---1. 고장내기

 S SYS> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl
/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl

S SYS> !rm /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl

S SYS> shutdown abort
ORACLE instance shut down.

--1.5 startup

 S SYS> startup
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218920 bytes
Variable Size             201328280 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

S SYS> show parameter control_file

NAME                                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time                integer     7
control_files                                        string      /u01/app/oracle/oradata/ORCL/c
                                                                      ontrolfile/o1_mf_50sxs92d_.ctl
                                                                      , /u01/app/oracle/flash_recove
                                                                      ry_area/ORCL/controlfile/o1_mf
                                                                      _50sxs97m_.ctl

 --2. copy

 S SYS> !ls -als /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl
ls: /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl: No such file or directory

 S SYS> !ls -als /u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl
6912 -rw-r-----  1 oracle oinstall 7061504 Jun 15 15:15 /u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl

 S SYS> !cp /u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl

 --3. open

 S SYS> alter database mount;

Database altered.

S SYS> alter database open;

Database altered.

UserManaged 방식 Online Recovery

 [oracle@orcl ~]$ rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53crz7h5_.dbf

S SYS> create table xxx(x date) tablespace example;

Table created.

S SYS> alter system checkpoint;

System altered.

S SYS> @errFile

NAME
--------------------------------------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53crz7h5_.dbf
FILE NOT FOUND

S SYS> select name from v$tablespace where ts#=(
  2  select ts# from v$datafile where name='/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53crz7h5_.dbf');

NAME
------------------------------
EXAMPLE

S SYS> alter tablespace example offline for recover;

Tablespace altered.

S SYS> !
[oracle@orcl ~]$ cd /backup
[oracle@orcl backup]$ cat fileCopy.sh  | grep example
 cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf /backup
[oracle@orcl backup]$ cp /backup/o1_mf_example_50sxt0j6_.dbf /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53crz7h5_.dbf
[oracle@orcl backup]$ exit
exit

S SYS> recover tablespace example;
ORA-00279: change 11227833 generated at 06/15/2009 13:22:51 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_%u_.arc
ORA-00280: change 11227833 for thread 1 is in sequence #280

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11250877 generated at 06/15/2009 13:47:24 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_281_%u_.arc
ORA-00280: change 11250877 for thread 1 is in sequence #281
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_53cnlws7_.arc' no longer needed
for this recovery

Log applied.
Media recovery complete.
S SYS> alter tablespace example online;

Tablespace altered.

 System-Criticla Data File

 --1. 고장내기

[oracle@orcl ~]$ rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf

 shutdown abort

 --2 startup

 S SYS> startup
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218920 bytes
Variable Size             201328280 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf'

 S SYS> @errFile

NAME
--------------------------------------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf
FILE NOT FOUND

--3. recover

 S SYS> !
[oracle@orcl ~]$ cd /backup
[oracle@orcl backup]$ cat fileCopy.sh  | grep system
 cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf /backup
[oracle@orcl backup]$ cp /backup/o1_mf_system_50sxqqv0_.dbf /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf
[oracle@orcl backup]$ exit
exit

S SYS> recover tablespace system;
ORA-00279: change 11227833 generated at 06/15/2009 13:22:51 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_%u_.arc
ORA-00280: change 11227833 for thread 1 is in sequence #280

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 11250877 generated at 06/15/2009 13:47:24 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_281_%u_.arc
ORA-00280: change 11250877 for thread 1 is in sequence #281
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_15/o1_mf_1_280_53cnlws7_.arc' no longer needed
for this recovery

Log applied.
Media recovery complete.

--4. instance open

S SYS> alter database open;

Database altered.

--5. 확인

S SYS> @errFile

no rows selected