[Oracle] 오픈상태 - 임의데이터

오픈상태 - 임의데이터 - 완전 복구 | Oracle

2006.11.16 11:36:44 | 2006.11.16 11:36:44 | 396 | Aiden

❮ ❯

※ 필수 파일 
1. 닫힌 백업한 파일중 복원시킬 데이터 파일 or 열린 백업한 파일중 복원시킬 데이터 파일
2. 백업이후 생성된 아카이브 로그파일
3. 여기에는 열린 상태에서 복원하지만 데이터베이스를 마운트 상태에 두고 복구한 다음 오픈해도 된다.
=======================================================================================


SQL> create user test
  2  identified by test
  3  default tablespace users
  4  temporary tablespace temp
  5  quota unlimited on users;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> connect test/test
Connected.
SQL> create table test(
  2  t1 varchar2(10),
  3  t2 number
  4  );

Table created.

SQL> insert into test values ('test', 10);

1 row created.

SQL> select * from test;

T1                 T2
---------- ----------
test               10

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

SQL> connect / as sysdba
Connected.
SQL> col table_name format a30
SQL> col tablespace_name format a30
SQL> col owner format a20
SQL> select table_name, tablespace_name, owner from dba_tables where owner = 'TEST';

TABLE_NAME                     TABLESPACE_NAME                OWNER
------------------------------ ------------------------------ --------------------
TEST                           USERS                          TEST

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> !rm /oracle/oradata/ora10g/users01.dbf

SQL> connect test/test
Connected.
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/oradata/ora10g/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> connect / as sysdba
Connected.
SQL> col tablespace_name format a15
SQL> col name format a40
SQL> col error format a20  
SQL> select h.tablespace_name, d.name, h.error
  2  from v$datafile d, v$datafile_header h
  3  where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- --------------------
SYSTEM          /oracle/oradata/ora10g/system01.dbf
UNDOTBS1        /oracle/oradata/ora10g/undotbs01.dbf
SYSAUX          /oracle/oradata/ora10g/sysaux01.dbf
                /oracle/oradata/ora10g/users01.dbf       CANNOT OPEN FILE
EXAMPLE         /oracle/oradata/ora10g/example01.dbf

SQL> select d.file#, d.name, d.status, h.status     
  2  from v$datafile d, v$datafile_header h
  3  where d.file# = h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/ora10g/system01.dbf      SYSTEM  ONLINE
         2 /oracle/oradata/ora10g/undotbs01.dbf     ONLINE  ONLINE
         3 /oracle/oradata/ora10g/sysaux01.dbf      ONLINE  ONLINE
         4 /oracle/oradata/ora10g/users01.dbf       ONLINE  ONLINE
         5 /oracle/oradata/ora10g/example01.dbf     ONLINE  ONLINE

SQL> alter database datafile '/oracle/oradata/ora10g/users01.dbf' offline;

Database altered.

SQL> select d.file#, d.name, d.status, h.status  
  2  from v$datafile d, v$datafile_header h
  3  where d.file# = h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/ora10g/system01.dbf      SYSTEM  ONLINE
         2 /oracle/oradata/ora10g/undotbs01.dbf     ONLINE  ONLINE
         3 /oracle/oradata/ora10g/sysaux01.dbf      ONLINE  ONLINE
         4 /oracle/oradata/ora10g/users01.dbf       RECOVER OFFLINE
         5 /oracle/oradata/ora10g/example01.dbf     ONLINE  ONLINE

SQL> !cp /backup/close/ora10g/users01.dbf /oracle/oradata/ora10g
혹은
SQL> !cp /backup/open/ora10g/system01.dbf /oracle/oradata/ora10g/

SQL> recover datafile '/oracle/oradata/ora10g/users01.dbf'
ORA-00279: change 4194796 generated at 11/01/2006 11:05:15 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/ORA10G/archivelog/2006_11_17/o1_mf_1_121_%u_.arc
ORA-00280: change 4194796 for thread 1 is in sequence #121


ORA-00279: change 4196859 generated at 11/03/2006 17:00:47 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/ORA10G/archivelog/2006_11_17/o1_mf_1_122_%u_.arc
ORA-00280: change 4196859 for thread 1 is in sequence #122
ORA-00278: log file '/oracle/flash_recovery_area/ORA10G/archivelog/2006_11_03/o1_mf_1_121_2noxshkx_.arc' no longer
needed for this recovery

.
.
.

ORA-00279: change 5341444 generated at 11/16/2006 16:34:27 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/ORA10G/archivelog/2006_11_17/o1_mf_1_160_%u_.arc
ORA-00280: change 5341444 for thread 1 is in sequence #160
ORA-00278: log file '/oracle/flash_recovery_area/ORA10G/archivelog/2006_11_16/o1_mf_1_159_2or5447t_.arc' no longer
needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/ora10g/users01.dbf' online;

Database altered.

SQL> select d.file#, d.name, d.status, h.status 
  2  from v$datafile d, v$datafile_header h
  3  where d.file# = h.file#;

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/ora10g/system01.dbf      SYSTEM  ONLINE
         2 /oracle/oradata/ora10g/undotbs01.dbf     ONLINE  ONLINE
         3 /oracle/oradata/ora10g/sysaux01.dbf      ONLINE  ONLINE
         4 /oracle/oradata/ora10g/users01.dbf       ONLINE  ONLINE
         5 /oracle/oradata/ora10g/example01.dbf     ONLINE  ONLINE

SQL> connect test/test
Connected.
SQL> select * from test;

T1                 T2
---------- ----------
test               10

SQL>