찐빵의 IT World

SELECT a.group#,a.member,b.bytes/1024/1024 MB,b.sequence# "SEQ#",b.status,b.archived"ARC" FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1,2 

GROUP# MEMBER                                          MB SEQ# STATUS   ARC
------ --------------------------------------------- ---- ---- -------- -----
     1 /data/temp/redo01_a.rdo                         10    3 INACTIVE YES
     1 /data/temp/redo01_b.rdo                         10    3 INACTIVE YES
     1 /data/temp/redo01_c.rdo                         10    3 INACTIVE YES
     2 /data/temp/redo02_a.rdo                         10    4 CURRENT  NO
     2 /data/temp/redo02_b.rdo                         10    4 CURRENT  NO
     2 /data/temp/redo02_c.rdo                         10    4 CURRENT  NO

6 rows selected.

set line 200

col tablespace_name for a10

col file_name for a50

select a.name "TS_NAME", b.name "FILE_NAME", b.bytes/1024/1024 MB, b.status
from v$tablespace a, v$datafile b
where a.ts#=b.ts#

TS_NAME    FILE_NAME                                             MB STATUS
---------- -------------------------------------------------- ----- --------
SYSTEM     /data/temp2/system01.dbf                            2010 SYSTEM
SYSAUX     /data/temp2/sysaux01.dbf                             520 ONLINE
UNDOTBS1   /data/temp2/undotbs01.dbf                             90 ONLINE
USERS      /data/temp2/users01.dbf                                5 ONLINE
EXAMPLE    /data/temp2/example01.dbf                            346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf                              100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf                           10 OFFLINE

7 rows selected.

SYS> @df

TS_NAME    FILE_NAME                                             MB STATUS
---------- -------------------------------------------------- ----- --------
SYSTEM     /data/temp/system01.dbf                             2010 SYSTEM
SYSAUX     /data/temp/sysaux01.dbf                              520 ONLINE
UNDOTBS1   /data/temp/undotbs01.dbf                              90 ONLINE
USERS       /data/temp/users01.dbf                                 5 ONLINE
EXAMPLE    /data/temp/example01.dbf                             346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf                              100 ONLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf                           10 OFFLINE

7 rows selected.

SYS> alter database datafile '/data/temp/ts_web_idx01.dbf' online;
alter database datafile '/data/temp/ts_web_idx01.dbf' online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/data/temp/ts_web_idx01.dbf'

SQL> recover datafile '/data/temp/ts_web_idx01.dbf' 

ORA-00279: change 1242623 generated at 07/04/2013 05:17:03 needed for thread 1
ORA-00289: suggestion : /data/arc1/3_1_819794020.arc
ORA-00280: change 1242623 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

어쩌구저쩌구 많이 나옴

Log applied.
Media recovery complete.

SYS> alter database datafile '/data/temp/ts_web_idx01.dbf' online;

Database altered.

정상적으로 온라인 되었다.