'recover datafile' 태그의 글 목록

일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

'recover datafile'에 해당되는 글 2건

2010.03.09 Complete Recovery 연습 문제
2010.03.09 Archive log mode Complete Recovery

Complete Recovery 연습 문제

Oracle 10g/10g - 연습문제

posted by I유령I 2010. 3. 9. 12:13

#1 /home/oracle/oradata/testdb/undotbs01.dbf를 삭제하고 복구하기

SQL> !rm -rf /home/oracle/oradata/testdb/undotbs01.dbf

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/home/oracle/oradata/testdb/undotbs01.dbf'

SQL> !cp /data/backup/open/undotbs01.dbf /home/oracle/oradata/testdb/

SQL> recover datafile '/home/oracle/oradata/testdb/undotbs01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME MB FILE_NAME

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

USERS 5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX 240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1 35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM 480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE 100 /home/oracle/oradata/testdb/example01.dbf

TS_A 5 /home/oracle/oradata/testdb/ts_a01.dbf

TS_B 5 /home/oracle/oradata/testdb/ts_b01.dbf

TS_C 5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL>

#2 ts_a, ts_b, ts_c에 각각 test5, test6, test7 Table을 만들고 Data를 입력한 후 각 Data file을 삭제하고 3개의 Tablespace 전부 복구하기

SQL> create table test5 (no number) tablespace ts_a;

Table created.

SQL> insert into test5 values (5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test5;

----------

SQL> create table test6 (no number) tablespace ts_b;

Table created.

SQL> insert into test6 values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test6;

----------

SQL> create table test7 (no number) tablespace ts_c;

Table created.

SQL> insert into test7 values (7);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test7;

----------

SQL> !rm /home/oracle/oradata/testdb/ts_a01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_b01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_c01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*

ls: /home/oracle/oradata/testdb/ts_*: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/home/oracle/oradata/testdb/ts_a01.dbf'

SQL> !cp /data/backup/open/ts_a01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_b01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_c01.dbf /home/oracle/oradata/testdb/

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from test5;

----------

SQL> select * from test6;

----------

SQL> select * from test7;

----------

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - Time Base 연습문제 1 (0)	2010.03.10
Complete Recovery (다른 경로에 응급 복구) (0)	2010.03.10
No Archive log mode Recovery 연습문제 (0)	2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace) (0)	2010.02.28
종합 연습문제 (Controlfile, Redo log file, Tablespace) (0)	2010.02.27

Archive log mode Complete Recovery

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 9. 12:09

Non System Tablespace Recovery

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/arc2

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

SQL> create tablespace ts_a

2 datafile '/home/oracle/oradata/testdb/ts_a01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_b

2 datafile '/home/oracle/oradata/testdb/ts_b01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_c

2 datafile '/home/oracle/oradata/testdb/ts_c01.dbf' size 5m;

Tablespace created.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME MB FILE_NAME

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

USERS 5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX 240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1 35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM 480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE 100 /home/oracle/oradata/testdb/example01.dbf

TS_A 5 /home/oracle/oradata/testdb/ts_a01.dbf

TS_B 5 /home/oracle/oradata/testdb/ts_b01.dbf

TS_C 5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> create table test1 (no number) tablespace ts_c;

Table created.

SQL> insert into test1 values (1);

1 row created.

SQL> insert into test1 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

----------

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace ts_a begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_a01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/open/ts_a01.dbf'

SQL> alter tablespace ts_a end backup;

Tablespace altered.

SQL> alter tablespace ts_b begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_b01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/open/ts_b01.dbf'

SQL> alter tablespace ts_b end backup;

Tablespace altered.

SQL> alter tablespace ts_c begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_c01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/open/ts_c01.dbf'

SQL> alter tablespace ts_c end backup;

Tablespace altered.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> !rm -rf /home/oracle/oradata/testdb/ts_c01.dbf

SQL> alter tablespace ts_c offline;

Tablespace altered.

SQL> alter tablespace ts_c online;

alter tablespace ts_c online

ERROR at line 1:

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: '/home/oracle/oradata/testdb/ts_c01.dbf'

SQL> select * from test1;

select * from test1

ERROR at line 1:

ORA-00376: file 8 cannot be read at this time

ORA-01110: data file 8: '/home/oracle/oradata/testdb/ts_c01.dbf'

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME MB FILE_NAME

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

USERS 5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX 240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1 35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM 480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE 100 /home/oracle/oradata/testdb/example01.dbf

TS_A 5 /home/oracle/oradata/testdb/ts_a01.dbf

TS_B 5 /home/oracle/oradata/testdb/ts_b01.dbf

TS_C /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> !cp /data/backup/open/ts_c01.dbf /home/oracle/oradata/testdb/

SQL> recover tablespace ts_c;

Media recovery complete.

SQL> alter tablespace ts_c online;

Tablespace altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME MB FILE_NAME

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

USERS 5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX 240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1 35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM 480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE 100 /home/oracle/oradata/testdb/example01.dbf

TS_A 5 /home/oracle/oradata/testdb/ts_a01.dbf

TS_B 5 /home/oracle/oradata/testdb/ts_b01.dbf

TS_C 5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select * from test1;

----------

SQL>

System Tablespace Recovery

#1 recover database;

SQL> create table test3 (no number) tablespace system;

Table created.

SQL> insert into test3 values (1);

1 row created.

SQL> insert into test3 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

----------

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

SQL> !ls /home/oracle/oradata/testdb/system01.dbf

ls: /home/oracle/oradata/testdb/system01.dbf: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from test3;

----------

SQL>

#2 recover tablespace tablespace_name;

SQL> truncate table test3;

Table truncated.

SQL> select * from test3;

no rows selected

SQL> insert into test3 values (3);

1 row created.

SQL> insert into test3 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

----------

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

SQL> !ls /home/oracle/oradata/testdb/system01.dbf

ls: /home/oracle/oradata/testdb/system01.dbf: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover tablespace system;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from test3;

----------

SQL>

#3 recover datafile '/home/oracle/oradata/testdb/system01.dbf';

SQL> truncate table test3;

Table truncated.

SQL> insert into test3 values (5);

1 row created.

SQL> insert into test3 values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

----------

SQL> !rm -rf /home/oracle/oradata/testdb/system01.dbf

SQL> !ls /home/oracle/oradata/testdb/system01.dbf

ls: /home/oracle/oradata/testdb/system01.dbf: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

SQL> !cp /data/backup/open/system01.dbf /home/oracle/oradata/testdb/

SQL> recover datafile '/home/oracle/oradata/testdb/system01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from test3;

----------

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile (0)	2010.03.11
Incomplete Recovery - Time Base (0)	2010.03.10
No Archive log mode Recovery (0)	2010.03.08
Cold Backup & Hot Backup (0)	2010.03.08
Archive log mode 설정 및 활성화/비활성화 (0)	2010.03.05

Ghost's Oracle Database !

Category

Notice

Archive

My Link

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'recover datafile'에 해당되는 글 2건

Complete Recovery 연습 문제

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Archive log mode Complete Recovery

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

티스토리툴바

Ghost's Oracle Database !

Category

Notice

Archive

My Link

Search

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'recover datafile'에 해당되는 글 2건

Complete Recovery 연습 문제

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Archive log mode Complete Recovery

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

티스토리툴바