본문 바로가기
Oracle/백업&복구

[시나리오]CloneDB 생성으로 Drop Table 복구

by 취미툰 2020. 3. 24.
반응형

========================

ORACLE_SID = ysbae
OS = Linux ysbae2 4.1.12-112.16.4.el7uek.x86_64 #2 SMP Mon Mar 12 23:57:12 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
OEL 7.6
DB = 12.2.0.1

=========================

 

1.현재 상태 확인

SYS@ysbae> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
FILE_NAME
-----------------------------------------------
SYSTEM                                      
/app/oracle/oradata/ysbae/system01.dbf      
                                            
SYSAUX                                      
/app/oracle/oradata/ysbae/sysaux01.dbf      
                                            
UNDOTBS1                                    
/app/oracle/oradata/ysbae/undotbs01.dbf     
                                            
USERS                                       
/app/oracle/oradata/ysbae/users01.dbf       
                                            
EXAMPLE                                     
/app/oracle/oradata/ysbae/example01.dbf     
                                            
                                            
5 rows selected.             

 

 

2.테스트 유저 생성
SYS@ysbae> create tablespace cltest datafile '/app/oracle/oradata/ysbae/cltest01.dbf' size 300M;

Tablespace created.


Elapsed: 00:00:00.08
SYS@ysbae> create user cltest identified by cltest account unlock default tablespace cltest;

User created.

Elapsed: 00:00:00.11
SYS@ysbae> grant connect,resource,unlimited tablespace to cltest;

Grant succeeded.
               

3.테스트 데이터 생성
CLTEST@ysbae> create table tb01 (lvl number,name varchar2(10));

Table created

CLTEST@ysbae> insert into tb01
  2  select level as lvl,dbms_random.string('A',5) as name
from  dual connect by level <= 100000;  3

100000 rows created.

CLTEST@ysbae> select count(*) from tb01;

  COUNT(*)
----------
    100000

1 row selected.

 

4.백업 시작

노아카이브로 hot backup 시 아래와 같은 에러 발생
ORA-01123: cannot start online backup; media recovery not enabled

 

SYS@ysbae> select * from v$backup;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 NOT ACTIVE                  0 (null)             0
         2 NOT ACTIVE                  0 (null)             0
         3 NOT ACTIVE                  0 (null)             0
         4 NOT ACTIVE                  0 (null)             0
         5 NOT ACTIVE                  0 (null)             0
         6 NOT ACTIVE                  0 (null)             0

6 rows selected.

Elapsed: 00:00:00.01
SYS@ysbae> alter database begin backup;

Database altered.

Elapsed: 00:00:01.09
SYS@ysbae> select * from v$backup;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 ACTIVE               48157230 23-MAR-20          0
         2 ACTIVE               48157230 23-MAR-20          0
         3 ACTIVE               48157230 23-MAR-20          0
         4 ACTIVE               48157230 23-MAR-20          0
         5 ACTIVE               48157230 23-MAR-20          0
         6 ACTIVE               48157230 23-MAR-20          0

6 rows selected.

5.물리적 파일 복사
[oracle@ysbae2 ~]$ mkdir clonetest
[oracle@ysbae2 ysbae]$ cp -av *.dbf /home/oracle/clonetest/
‘cltest01.dbf’ -> ‘/home/oracle/clonetest/cltest01.dbf’
‘example01.dbf’ -> ‘/home/oracle/clonetest/example01.dbf’
‘sysaux01.dbf’ -> ‘/home/oracle/clonetest/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/clonetest/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/clonetest/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/clonetest/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/clonetest/users01.dbf’

 

 

6.컨트롤파일 백업

SYS@ysbae> alter database backup controlfile to trace as '/home/oracle/clonetest/ctrl01.sql';

Database altered.

 

7.백업 종료

SYS@ysbae> alter database end backup;

Database altered.

 

SYS@ysbae> select * from v$backup;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 NOT ACTIVE           48157230 23-MAR-20          0
         2 NOT ACTIVE           48157230 23-MAR-20          0
         3 NOT ACTIVE           48157230 23-MAR-20          0
         4 NOT ACTIVE           48157230 23-MAR-20          0
         5 NOT ACTIVE           48157230 23-MAR-20          0
         6 NOT ACTIVE           48157230 23-MAR-20          0

6 rows selected.

 

8.아카이브로그 생성
SYS@ysbae> alter system switch logfile;

System altered.

Elapsed: 00:00:00.04
SYS@ysbae> /

System altered.

Elapsed: 00:00:00.03
SYS@ysbae> /

System altered.

Elapsed: 00:00:01.65
SYS@ysbae> /

System altered.

 

9.장애발생

CLTEST@ysbae> drop table tb01 purge;

Table dropped.

Elapsed: 00:00:01.59
CLTEST@ysbae> select * from tb01;
select * from tb01
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00
CLTEST@ysbae> select * from tab;

no rows selected

 

10.CloneDB 생성

빨간색으로 색칠된 부분 수정 후 나머지부분은 삭제합니다.

[oracle@ysbae2 clonetest]$ cp ctrl01.sql ctrl02.sql

[oracle@ysbae2 clonetest]$ vi ctrl02.sql

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLTESTRESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/home/oracle/clonetest/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clonetest/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clonetest/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/clonetest/system01.dbf',
  '/home/oracle/clonetest/sysaux01.dbf',
  '/home/oracle/clonetest/undotbs01.dbf',
  '/home/oracle/clonetest/cltest01.dbf'
CHARACTER SET KO16MSWIN949
;

 

11.init파일 수정

[oracle@ysbae2 dbs]$ cp initysbae.ora initcltest.ora
[oracle@ysbae2 dbs]$ vi initcltest.ora

 

ysbae.__db_cache_size=738197504
ysbae.__inmemory_ext_roarea=0
ysbae.__inmemory_ext_rwarea=0
ysbae.__java_pool_size=16777216
ysbae.__large_pool_size=33554432
ysbae.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
ysbae.__pga_aggregate_target=570425344
ysbae.__sga_target=1090519040
ysbae.__shared_io_pool_size=0
ysbae.__shared_pool_size=285212672
ysbae.__streams_pool_size=0
*.allow_group_access_to_sga=TRUE
*.audit_file_dest='/app/oracle/admin/ysbae/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/clonetest/control01.ctl'  ---수정
*.db_block_size=8192
*.db_domain=''
*.db_name='cltest' ---수정
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest='/app/oracle/fast_recovery_area'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ysbaeXDB)'
*.local_listener='LISTENER_YSBAE'
*.memory_target=1660944384
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=900
*.undo_tablespace='UNDOTBS1'

 

12. cltest CloneDB Open

[oracle@ysbae2 ~]$ export ORACLE_SID=cltest

 

[oracle@ysbae2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 24 14:58:52 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@cltest> @ctrl02.sql
ORACLE instance started.

Total System Global Area  662700032 bytes
Fixed Size                  8624216 bytes
Variable Size             557844392 bytes
Database Buffers           92274688 bytes
Redo Buffers                3956736 bytes

Control file created.

SYS@cltest> select status from v$instance;

STATUS
------------
MOUNTED

1 row selected.

 

13.아카이브 파일 복사

기존 ysbae에서 만들었던 아카이브로그를 CLTEST 아카이브로그 디렉토리로 복사

[oracle@ysbae2 2020_03_23]$ cp * /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23

 

14. 복구

SYS@cltest> recover database using backup controlfile until cancel
ORA-00279: change 48157230 generated at 03/23/2020 11:24:42 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1118_h7jf2gkr_.arc
ORA-00280: change 48157230 for thread 1 is in sequence #1118


Specify log: {=suggested | filename | AUTO | CANCEL}
[
ORA-00279: change 48161994 generated at 03/23/2020 13:00:14 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1119_h7jgtp6l_.arc
ORA-00280: change 48161994 for thread 1 is in sequence #1119
ORA-00278: log file '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1118_h7jf2gkr_.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 48163644 generated at 03/23/2020 13:30:14 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1120_h7jgtpr4_.arc
ORA-00280: change 48163644 for thread 1 is in sequence #1120
ORA-00278: log file '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1119_h7jgtp6l_.arc' no longer needed for this recovery


ORA-00279: change 48163647 generated at 03/23/2020 13:30:14 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1121_h7jgtrxw_.arc
ORA-00280: change 48163647 for thread 1 is in sequence #1121
ORA-00278: log file '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1120_h7jgtpr4_.arc' no longer needed for this recovery


ORA-00279: change 48163656 generated at 03/23/2020 13:30:16 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1122_h7jgttop_.arc
ORA-00280: change 48163656 for thread 1 is in sequence #1122
ORA-00278: log file '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1121_h7jgtrxw_.arc' no longer needed for this recovery


ORA-00279: change 48163660 generated at 03/23/2020 13:30:18 needed for thread 1
ORA-00289: suggestion : /app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1123_%u_.arc
ORA-00280: change 48163660 for thread 1 is in sequence #1123
ORA-00278: log file '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1122_h7jgttop_.arc' no longer needed for this recovery


ORA-00308: cannot open archived log '/app/oracle/fast_recovery_area/CLTEST/archivelog/2020_03_23/o1_mf_1_1123_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

 

14. Resetlog로 Open

SYS@cltest> alter database open resetlogs;

Database altered.

 

15.삭제된 테이블 있는지 확인

SYS@cltest> conn cltest/cltest
Connected.
CLTEST@cltest> select * from tab;

TNAME                                                                                                               TABTYPE  CLUSTERID
------------------------------------------------------------------------------------------------------ ------- ----------
TB01                                                                                                                TABLE    (null)

1 row selected.

Elapsed: 00:00:00.26
CLTEST@cltest> select count(*) from tb01;

  COUNT(*)
----------
    100000

1 row selected.


CloneDB에는 Table이 생성되어있는것을 확인할 수 있습니다.

 

16. exp/imp로 복구

[oracle@ysbae2 clonetest]$ exp system/oracle tables=cltest.tb01 file=/home/oracle/clonetest/tb01.dmp

Export: Release 12.2.0.1.0 - Production on Mon Mar 23 14:10:56 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


EXP-00056: ORACLE error 28002 encountered
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16MSWIN949 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to CLTEST
. . exporting table                           TB01     100000 rows exported
Export terminated successfully without warnings.

[oracle@ysbae2 clonetest]$ imp system/oracle tables=tb01 fromuser=cltest touser=cltest file=/home/oracle/clonetest/tb01.dmp

Import: Release 12.2.0.1.0 - Production on Mon Mar 23 14:13:20 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


IMP-00058: ORACLE error 28002 encountered
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses KO16MSWIN949 character set (possible charset conversion)
IMP-00403:

Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.

. importing CLTEST's objects into CLTEST
. . importing table                         "TB01"     100000 rows imported
Import terminated successfully with warnings.

exp/imp 완료


확인
[oracle@ysbae2 clonetest]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 23 14:13:33 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@ysbae> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ysbae            OPEN

1 row selected.

Elapsed: 00:00:00.01
SYS@ysbae> conn cltest/cltest
Connected.
CLTEST@ysbae> select * from tab;

TNAME                                                                                                                            TABTYPE  CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------- ----------
TB01                                                                                                                             TABLE   (null)

1 row selected.

Elapsed: 00:00:00.06
CLTEST@ysbae> select count(*) from tb01;

  COUNT(*)
----------
    100000

1 row selected.

 

테이블 복구 완료.

반응형

댓글