2024년 1월 26일 1교시 RMAN을 이용해서 복제 DB 만들기

 

★ RMAN 을 이용해서 클론 DB 만들기

 

1. 시작하기 전에 백업 먼저 받기

 

1-1. RMAN 접속해서 schema 확인

 

RMAN> report schema;

현재 ora11g 에 생성되어 있는 테이블스페이스와, 그 각각의 테이블스페이스에 속한 데이터파일 및 그 크기 보기

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORA11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/ora11g/system01.dbf
2    720      SYSAUX               ***     /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4    8        USERS                ***     /u01/app/oracle/oradata/ora11g/users01.dbf
5    338      EXAMPLE              ***     /u01/app/oracle/oradata/ora11g/example01.dbf
6    40       UNDOTBS              ***     /u01/app/oracle/oradata/ora11g/undotbs01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ora11g/temp01.dbf

 

1-2. 백업정보 확인

 

RMAN> list backup;

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
36      Full    1.28G      DISK        00:00:32     25-JAN-24     
        BP Key: 36   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T184557
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T184557_lv4cgong_.bkp
  List of Datafiles in backup set 36
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2108788    25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2108788    25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2108788    25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2108788    25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  6       Full 2108788    25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37      Full    9.73M      DISK        00:00:00     25-JAN-24     
        BP Key: 37   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T184633
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159209993_lv4chsfp_.bkp
  SPFILE Included: Modification time: 25-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2108824      Ckp time: 25-JAN-24

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    1.28G      DISK        00:00:33     25-JAN-24     
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T191821
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T191821_lv4fcfnr_.bkp
  List of Datafiles in backup set 38
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  6       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39      Full    9.73M      DISK        00:00:00     25-JAN-24     
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T191856
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159211936_lv4fdjsz_.bkp
  SPFILE Included: Modification time: 25-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2110657      Ckp time: 25-JAN-24

 

1-3. 있어야 할 위치에 진짜로 백업 정보가 있는지 확인

 

RMAN> crosscheck backup;

RMAN> crosscheck backup;

 

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=183 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T184557_lv4cgong_.bkp RECID=36 STAMP=1159209957
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159209993_lv4chsfp_.bkp RECID=37 STAMP=1159209993
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T191821_lv4fcfnr_.bkp RECID=38 STAMP=1159211901
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159211936_lv4fdjsz_.bkp RECID=39 STAMP=1159211936
Crosschecked 4 objects

 

1-4. 문제되는 것들이 있는지 확인해보기

 

RMAN> list expired backup;
specification does not match any backup in the repository

 

지금은 문제되는 것들이 하나도 없다고 나오는데

만약 문제되는 것들이 있다면 : delete expired backup; 으로 문제되는 데이터들 지우기

 

★ 백업

 

RMAN> backup database;

RMAN> backup database;

Starting backup at 26-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-JAN-24
channel ORA_DISK_1: finished piece 1 at 26-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T195435_lv73vct6_.bkp tag=TAG20240126T195435 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 26-JAN-24

Starting Control File and SPFILE Autobackup at 26-JAN-24
piece handle=/home/oracle/backup/rman/c-256148007-20240126-09 comment=NONE
Finished Control File and SPFILE Autobackup at 26-JAN-24

 

1-5. 백업정보 보기

 

RMAN> list backup;

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37      Full    9.73M      DISK        00:00:00     25-JAN-24     
        BP Key: 37   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T184633
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159209993_lv4chsfp_.bkp
  SPFILE Included: Modification time: 25-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2108824      Ckp time: 25-JAN-24

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    1.28G      DISK        00:00:33     25-JAN-24     
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T191821
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T191821_lv4fcfnr_.bkp
  List of Datafiles in backup set 38
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  6       Full 2110639    25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39      Full    9.73M      DISK        00:00:00     25-JAN-24     
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: TAG20240125T191856
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159211936_lv4fdjsz_.bkp
  SPFILE Included: Modification time: 25-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2110657      Ckp time: 25-JAN-24

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    1.26G      DISK        00:00:34     26-JAN-24     
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20240126T095142
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T095142_lv60jyvf_.bkp
  List of Datafiles in backup set 40
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  6       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    9.73M      DISK        00:00:01     26-JAN-24     
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20240126T095217
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159264337_lv60l23s_.bkp
  SPFILE Included: Modification time: 26-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2124051      Ckp time: 26-JAN-24

 

1-6. 필요없는 백업본 지우기

 

RMAN> delete backupset 37;

RMAN> delete backupset 38;

(진짜로 지우겠느냐고 물어보면 yes 하기)

백업본 두 개를 지웠으므로 이제 저 사이에 있는 아카이브들도 필요없게 된다.

 

1-7. 정책상 필요없는 아카이브들도 지우기

 

RMAN> report obsolete;

RMAN> delete obsolete;

(진짜로 지우겠느냐고 물어보면 yes 하기)

 

1-8. 물리적 아카이브에서도 지워졌는지 확인해보기

 

SYS@ora11g> ! ls /home/oracle/arch1
arch_1_28_1158423268.arc

깔끔하다.

 

1-9. 현재 리두도 다시한번 확인해보기

 

select a.group#, b.sequence#, a.member, b.bytes/1024/1024MB, b.archived, b.status, b.first_change#, b.next_change#
from v$logfile a, v$log b
where a.group# = b.group#
order by 1;

 

 

2. 여러 가지 준비작업

 

2-1. 로그 스위치 발생시키기

 

SYS@ora11g> alter system switch logfile;
System altered.

 

/

/

 

아래는 로그 스위치가 발생한 시간 (alert log file) Fri Jan 26 10:02:27 2024

Fri Jan 26 10:02:27 2024
Completed checkpoint up to RBA [0x2.2.10], SCN: 2124835
Beginning log switch checkpoint up to RBA [0x4.2.10], SCN: 2124852
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Fri Jan 26 10:02:27 2024
Archived Log entry 135 added for thread 1 sequence 3 ID 0xf8627fd dest 1:

 

2-2. 테이블 만들기

 

SYS@ora11g> create table hr.emp_temp as select * from hr.employees;
Table created.

 

SYS@ora11g> select count(*) from hr.emp_temp;

  COUNT(*)
----------
       107

 

2-3. 현재 시각 확인

 

SYS@ora11g> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-24 10.05.25.299913 AM +09:00

 

이 시간까지는 테이블이 남아있는 것

 

2-4. 테이블 드롭하기

 

SYS@ora11g> drop table hr.emp_temp purge;
Table dropped.

 

2-5. 로그 스위치 발생시키기

 

SYS@ora11g> alter system switch logfile;
System altered.

 

/

/

 

2-6. 리두 정보 확인

 

select a.group#, b.sequence#, a.member, b.bytes/1024/1024MB, b.archived, b.status, b.first_change#, b.next_change#
from v$logfile a, v$log b
where a.group# = b.group#
order by 1;

 

 

2-7. 아카이브 정보도 확인

 

SYS@ora11g> ! ls /home/oracle/arch1
arch_1_1_1159207111.arc   arch_1_4_1159207111.arc
arch_1_2_1159207111.arc   arch_1_5_1159207111.arc
arch_1_28_1158423268.arc  arch_1_6_1159207111.arc
arch_1_3_1159207111.arc

 

2-8. 테이블 삭제한 걸 모르고 테이블을 조회해보면: 없어져 있다.

 

SYS@ora11g> select count(*) from hr.emp_temp;
select count(*) from hr.emp_temp
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

 

3. 테이블 없애기 이전 시간의 복제 DB 만들기

그러려면 테이블을 없애기 이전의 백업본(과 아카이브)가 있어야 한다.

 

- 초기 파라미터 파일 만들기

- 백업본 찾기

- 아카이브 찾기

 

3-1. 초기 파라미터 파일 만들기

 

[oracle@oracle ~]$ vi $ORACLE_HOME/dbs/initclone.ora

 

*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle/clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='undotbs'

 

붙여넣고 저장

 

3-2. 디렉토리 생성하기

 

[oracle@oracle ~]$ mkdir clone

[oracle@oracle ~]$ cd clone

[oracle@oracle clone]$ cd
[oracle@oracle ~]$

 

3-3. 아카이브를 모두 클론 디렉토리에 카피

 

[oracle@oracle ~]$ cp -av /home/oracle/arch1/*.* /home/oracle/clone

 

카피가 잘 되었는지 체크하기

 

[oracle@oracle ~]$ cd clone

[oracle@oracle clone]$ ls
arch_1_1_1159207111.arc   arch_1_4_1159207111.arc
arch_1_2_1159207111.arc   arch_1_5_1159207111.arc
arch_1_28_1158423268.arc  arch_1_6_1159207111.arc
arch_1_3_1159207111.arc

 

3-4. 백업본 찾기

 

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    1.26G      DISK        00:00:34     26-JAN-24     
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20240126T095142
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T095142_lv60jyvf_.bkp
  List of Datafiles in backup set 40
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  4       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
  6       Full 2124028    26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    9.73M      DISK        00:00:01     26-JAN-24     
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20240126T095217
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159264337_lv60l23s_.bkp
  SPFILE Included: Modification time: 26-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 2124051      Ckp time: 26-JAN-24

 

 

3-5. 백업셋과 컨트롤파일의 물리적인 위치 복사해서 clone 디렉토리에 카피하기

 

[oracle@oracle ~]$

cp -av /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T095142_lv60jyvf_.bkp /home/oracle/clone

 

[oracle@oracle ~]$

cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159264337_lv60l23s_.bkp /home/oracle/clone

 

★ 복제 DB 만들기

 

[oracle@oracle ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1

 

[oracle@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle

 

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 10:27:41 2024
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

 

여기서 선생님 접속 안되고 오류 나심...

잠시 쉬어가는 시간...