http://cafe.naver.com/ocmkorea/4491 이현룡 교수님 카페 참조
========================================================================
Backup control file을 이용한 불완전복구(time based)
========================================================================
SYS@orcl>create table insa(id number, name varchar2(10), hdate date)
tablespace users;
Table created.
SYS@orcl>select tablespace_name, table_name from dba_tables
where table_name='INSA';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
USERS INSA
[oracle@lnx04 ~]$ vi dd.sql
col file_name for a50
col tablespace_name for a15
set linesize 120
select tablespace_name, file_name, bytes/1024/1024 from dba_data_files
SYS@orcl>@dd.sql
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
--------------- -------------------------------------------------- ---------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 105
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 700
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100
RMAN_TBS /u01/app/oracle/oradata/orcl/rman_tbs.dbf 100
6 rows selected.
SYS@orcl>alter system checkpoint;
System altered.
SYS@orcl>alter system switch logfile;
System altered.
SYS@orcl>drop tablespace users including contents and datafiles cascade constraints;
drop tablespace users including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
SYS@orcl>create tablespace users02
datafile '/u01/app/oracle/oradata/orcl/user02.dbf' size 100m
autoextend on maxsize 1g
permanent;
Tablespace created.
SYS@orcl>alter database default tablespace users02;
Database altered.
SYS@orcl>drop tablespace users including contents and datafiles cascade constraints;
Tablespace dropped.
SYS@orcl>SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
12-OCT-15 01.06.53.926744 PM +09:00
SYS@orcl>insert into insa values(10,'angel',sysdate);
insert into insa values(10,'angel',sysdate)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@orcl>alter system checkpoint;
System altered.
SYS@orcl>alter system switch logfile;
System altered.
SYS@orcl>show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/orcl/trace
alert log
Completed: alter database default tablespace users02
drop tablespace users including contents and datafiles cascade constraints
Deleted file /u01/app/oracle/oradata/orcl/users01.dbf
Completed: drop tablespace users including contents and datafiles cascade constraints
SYS@orcl>shutdown immediate;
SYS@orcl>@dd.sql
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
--------------- -------------------------------------------------- ---------------
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 105
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 700
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100
RMAN_TBS /u01/app/oracle/oradata/orcl/rman_tbs.dbf 100
USERS02 /u01/app/oracle/oradata/orcl/user02.dbf 100
6 rows selected.
[oracle@lnx04 close]$ cp * $ORACLE_BASE/oradata/orcl/
[oracle@lnx04 ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 12 13:10:06 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS@orcl>startup mount
ORACLE instance started.
Total System Global Area 1707446272 bytes
Fixed Size 1336988 bytes
Variable Size 1006635364 bytes
Database Buffers 687865856 bytes
Redo Buffers 11608064 bytes
Database mounted.
SYS@orcl>recover database until time'2015-10-12:13:05:05' using backup controlfile;
ORA-00279: change 946020 generated at 10/12/2015 12:58:49 needed for thread 1
ORA-00289: suggestion : /ARC/ORCL_892658623_1_47.arc
ORA-00280: change 946020 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 946439 generated at 10/12/2015 13:00:56 needed for thread 1
ORA-00289: suggestion : /ARC/ORCL_892658623_1_48.arc
ORA-00280: change 946439 for thread 1 is in sequence #48
ORA-00278: log file '/ARC/ORCL_892658623_1_47.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 946519 generated at 10/12/2015 13:01:16 needed for thread 1
ORA-00289: suggestion : /ARC/ORCL_892658623_1_49.arc
ORA-00280: change 946519 for thread 1 is in sequence #49
ORA-00278: log file '/ARC/ORCL_892658623_1_48.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 946757 generated at 10/12/2015 13:03:22 needed for thread 1
ORA-00289: suggestion : /ARC/ORCL_892658623_1_50.arc
ORA-00280: change 946757 for thread 1 is in sequence #50
ORA-00278: log file '/ARC/ORCL_892658623_1_49.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SYS@orcl>alter database open resetlogs;
Database altered.
SYS@orcl>@dd.sql
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
--------------- -------------------------------------------------- ---------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf 5
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 105
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 510
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 700
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100
RMAN_TBS /u01/app/oracle/oradata/orcl/rman_tbs.dbf 100
6 rows selected.
SYS@orcl>select tablespace_name, table_name from dba_tables
2 where table_name='INSA';
TABLESPACE_NAME TABLE_NAME
--------------- ------------------------------
USERS INSA
======================================END===========================================
'스터디북' 카테고리의 다른 글
| [10/12] RMAN 복구 실습 - 4 (0) | 2015.10.12 |
|---|---|
| [10/12] RMAN 복구 실습 - 3 (0) | 2015.10.12 |
| [10/12] 정리 그리고 실습 - 1 (0) | 2015.10.12 |
| [10/11] 정리 (0) | 2015.10.11 |
| [10/10] 정리 (0) | 2015.10.10 |