2010. 3. 8. 12:49
No Archive log mode Recovery #1
SQL> !rm -rf /home/oracle/oradata/testdb/users01.dbf
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'
SQL> select tablespace_name, status, contents from dba_tablespaces;
TABLESPACE STATUS CONTENTS
---------- --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS OFFLINE PERMANENT
EXAMPLE ONLINE PERMANENT
6 rows selected.
SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
2 from v$datafile a, v$backup b
3 where a.file# = b.file#;
FILE# NAME STATUS TIME
---------- -------------------------------------------------- ------------------ -------------------
1 /home/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2010-03-08:11:10:04
2 /home/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2010-03-08:11:11:07
3 /home/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2010-03-08:11:10:38
5 /home/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2010-03-08:11:12:16
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /data/backup/close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'
SQL> !cp -av /data/backup/close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'
SQL> !cp -av /data/backup/close/*.log /home/oracle/oradata/testdb/
`/data/backup/close/redo01.log' -> `/home/oracle/oradata/testdb/redo01.log'
`/data/backup/close/redo02.log' -> `/home/oracle/oradata/testdb/redo02.log'
`/data/backup/close/redo03.log' -> `/home/oracle/oradata/testdb/redo03.log'
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> select tablespace_name, status, contents from dba_tablespaces;
TABLESPACE STATUS CONTENTS
---------- --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
6 rows selected.
SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
2 from v$datafile a, v$backup b
3 where a.file# = b.file#;
FILE# NAME STATUS TIME
---------- -------------------------------------------------- ------------------ -------------------
1 /home/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2010-03-08:10:54:39
2 /home/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2010-03-08:10:55:09
3 /home/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2010-03-08:10:54:58
4 /home/oracle/oradata/testdb/users01.dbf NOT ACTIVE 2010-03-08:10:55:11
5 /home/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2010-03-08:10:55:11
SQL>
No Archive log mode Recovery #2 (해당 Tablespace가 지워져도 괜찮을 경우에 한하여 사용한다.)
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.
Database opened.
SQL> col name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
6 rows selected.
SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
2 from v$datafile a, v$backup b
3 where a.file# = b.file#;
FILE# NAME STATUS TIME
---------- -------------------------------------------------- ------------------ -------------------
1 /home/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2010-03-08:10:54:39
2 /home/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2010-03-08:10:55:09
3 /home/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2010-03-08:10:54:58
4 /home/oracle/oradata/testdb/users01.dbf NOT ACTIVE 2010-03-08:10:55:11
5 /home/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2010-03-08:10:55:11
SQL> !rm /home/oracle/oradata/testdb/users01.dbf
SQL> shutdown abort;
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 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'
SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select tablespace_name, status, contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
6 rows selected.
SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
2 from v$datafile a, v$backup b
3 where a.file# = b.file#;
FILE# NAME STATUS TIME
---------- -------------------------------------------------- ------------------ -------------------
1 /home/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2010-03-08:10:54:39
2 /home/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2010-03-08:10:55:09
3 /home/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2010-03-08:10:54:58
5 /home/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2010-03-08:10:55:11
SQL>
No Archive log mode Recovery #3
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.
Database opened.
SQL> !rm -rf /home/oracle/oradata/testdb/*
SQL> !ls /home/oracle/oradata/testdb/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /data/temp
SQL> !cp -av /data/close/*.dbf /data/temp/
cp: cannot stat `/data/close/*.dbf': 그런 파일이나 디렉토리가 없음
SQL> !cp -av /data/backup/close/*.dbf /data/temp/
`/data/backup/close/example01.dbf' -> `/data/temp/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/data/temp/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/data/temp/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/data/temp/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/data/temp/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/data/temp/users01.dbf'
SQL> !cp -av /data/backup/close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'
SQL> !cp -av /data/backup/close/*.log /home/oracle/oradata/testdb/
`/data/backup/close/redo01.log' -> `/home/oracle/oradata/testdb/redo01.log'
`/data/backup/close/redo02.log' -> `/home/oracle/oradata/testdb/redo02.log'
`/data/backup/close/redo03.log' -> `/home/oracle/oradata/testdb/redo03.log'
SQL> startup mount;
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.
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
SQL> alter database rename
2 file '/home/oracle/oradata/testdb/system01.dbf'
3 to '/data/temp/system01.dbf';
Database altered.
SQL> alter database rename
2 file '/home/oracle/oradata/testdb/sysaux01.dbf'
3 to '/data/temp/sysaux01.dbf';
Database altered.
SQL> alter database rename
2 file '/home/oracle/oradata/testdb/undotbs01.dbf'
3 to '/data/temp/undotbs01.dbf';
Database altered.
SQL> alter database rename
2 file '/home/oracle/oradata/testdb/users01.dbf'
3 to '/data/temp/users01.dbf';
Database altered.
SQL> alter database rename
2 file '/home/oracle/oradata/testdb/example01.dbf'
3 to '/data/temp/example01.dbf';
Database altered.
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 /data/temp/users01.dbf
SYSAUX 240 /data/temp/sysaux01.dbf
UNDOTBS1 35 /data/temp/undotbs01.dbf
SYSTEM 480 /data/temp/system01.dbf
EXAMPLE 100 /data/temp/example01.dbf
SQL>
'Oracle 10g > 10g - 실습' 카테고리의 다른 글
| Incomplete Recovery - Time Base (0) | 2010.03.10 |
|---|---|
| Archive log mode Complete Recovery (0) | 2010.03.09 |
| Cold Backup & Hot Backup (0) | 2010.03.08 |
| Archive log mode 설정 및 활성화/비활성화 (0) | 2010.03.05 |
| Privileges(권한) 관리 (0) | 2010.03.05 |