========================
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 "CLTEST" RESETLOGS 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.
테이블 복구 완료.
'Oracle > 백업&복구' 카테고리의 다른 글
| Recyclebin사용하여 테이블 복구하기 (0) | 2021.09.01 |
|---|---|
| [EXPDP/IMPDP]REMAP_DATA (0) | 2020.02.04 |
| DB Open & No Archive Mode상태에서 복구 - Current 상태 (0) | 2020.01.09 |
| Redo log 관리 - 리두 로그 그룹 증가 & 멤버 추가 (0) | 2020.01.08 |
댓글