복제 DB, Clone

■ 복제 Database
운영 data file, redo log file을 이용해서 DB를 생성

# backup 본 위치 확인 
[oracle1@oracle ~]$ cd backup/arch/cold_20240117
[oracle1@oracle cold_20240117]$ ls
backup.log     data01.dbf               insa_tbs01.dbf  redo09.log    temp01.dbf
backup.sh      example01.dbf            redo07.log      sysaux01.dbf  undo01.dbf
control01.ctl  initora11g_20240117.ora  redo08.log      system01.dbf  users01.dbf
[oracle1@oracle cold_20240117]$ pwd
/home/oracle1/backup/arch/cold_20240117

# 디렉터리 생성 
[oracle1@oracle ~]$ mkdir clone
[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ pwd
/home/oracle1/clone

1) 데이터 파일, 리두로그 파일 복사
[oracle1@oracle clone]$ cp -av /home/oracle1/backup/arch/cold_20240117/*.dbf .

[oracle1@oracle clone]$ cp -av /home/oracle1/backup/arch/cold_20240117/*.log .

#) 필요없는 파일 삭제

[oracle1@oracle clone]$ rm -R backup.log
[oracle1@oracle clone]$ rm -R insa_tbs01.dbf
[oracle1@oracle clone]$ ls

SQL> select * from v$version;

2) 초기 파라미터 파일 생성, pfile  생성 
[oracle1@oracle clone]$ vi $ORACLE_HOME/dbs/initclone.ora

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

3) clone DB 접속
[oracle1@oracle ~]$ echo $ORACLE_HOME

=> 다른 DB 로 접속하기 위한 경로
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
-> 잘못 지정되면 sqlplus 접속 오류
The Oracle base remains unchanged with value /u01/app/oracle

=> clone으로 보이는지 확인
[oracle1@oracle ~]$ env | grep SID

=> sql 접속
[oracle1@oracle ~]$ sqlplus / as sysdba
=> DB nomount까지 올리기
SQL> startup nomount

=> target 확인
SQL> show parameter target

4) control file 생성

=> 없다면 생성할 것!

SQL> alter database backup controlfile to trace as '/home/oracle1/new_control.sql';

# new_control.sql
[oracle1@oracle ~]$ ls
arch1   control_20240119.sql  Documents        p13390677_112040_LINUX_1of7.zip  Templates
arch2   create_control.sql    Downloads        p13390677_112040_LINUX_2of7.zip  userdata
backup  database              Music            Pictures                         Videos
clone   Desktop               new_control.sql  Public
[oracle1@oracle ~]$ vi new_control.sql

SQL> 
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 7 '/home/oracle1/clone/redo07.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 8 '/home/oracle1/clone/redo08.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 9 '/home/oracle1/clone/redo09.log'  SIZE 100M BLOCKSIZE 512
DATAFILE
  '/home/oracle1/clone/system01.dbf',
  '/home/oracle1/clone/sysaux01.dbf',
  '/home/oracle1/clone/users01.dbf',
  '/home/oracle1/clone/example01.dbf',
  '/home/oracle1/clone/undo01.dbf'
CHARACTER SET AL32UTF8
;

=> control file 생성 완료되면  DB는 mount단계까지 올라온다.
SQL> select status from v$instance;

5) recover data file 하기
=> 불안전한 복구처럼 진행
SQL> recover database until cancel using backup controlfile

6) DB resetlogs로 오픈하기
SQL> alter database open resetlogs;

7) DB 이름 확인
SQL> select name from v$database;

SQL> select count(*) from hr.employees;

8) redo log 확인
SQL> SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# data file 확인
SQL> select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

 

#) control file 확인

SQL> select name from v$controlfile;

#1) tempfile 확인
SQL> select * from v$tempfile;

#2) temp file 위치 확인 

SQL> ! ls /home/oracle1/clone/temp01.dbf

#3) temp file 생성 

SQL> alter tablespace temp add tempfile '/home/oracle1/clone/temp01.dbf' reuse;

#5) temp file 확인 

SQL> select * from v$tempfile;

# 환경 변화 # 
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g

=> DB 이름 체크하기!
[oracle1@oracle ~]$ sqlplus / as sysdba
SQL> select name from v$database;

# clone DB 없애는 방법

# clone 접속

[oracle1@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

[oracle1@oracle ~]$ sqlplus / as sysdba

1) DB 이름 확인
SQL> select name from v$database;

2) DB 비정상적인 종료
SQL> shutdown abort

3) clone 디렉터리 없애기
[oracle1@oracle ~]$ ls

[oracle1@oracle ~]$ rm -r clone/
[oracle1@oracle ~]$ ls

4) 초기 파라미터 삭제(initclone.ora)
[oracle1@oracle ~]$ cd $ORACLE_HOME/dbs/
[oracle1@oracle dbs]$ ls

5) DB 이름 확인 

[oracle1@oracle dbs]$ exit
exit

SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 31453
Session ID: 182 Serial number: 15

# 운영 DB 이름 체크하기
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle

[oracle1@oracle ~]$ sqlplus / as sysdba
SQL> select name from v$database;

# redo log 확인 
SQL> SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# 아카이브 확인
SQL> ! ls /home/oracle1/arch1

# 테이블 생성 
SQL> create table hr.insa_emp tablespace users as select * from hr.employees;

SQL> select count(*) from hr.insa_emp;

# 제약조건 확인
SQL> select constraint_name, constraint_type, search_condition, status, index_name
from dba_constraints
where table_name = 'INSA_EMP';

# primary key 제약조건 추가
SQL> alter table hr.insa_emp add constraint insa_emp_id_pk primary key(employee_id);

# 제약 조건 확인 
SQL> select constraint_name, constraint_type, search_condition, status, index_name
from dba_constraints
where table_name = 'INSA_EMP';

# redo log 확인
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# 로그 스위치 발생
SQL> alter system switch logfile;

# redo log 확인 

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# 운영중인 테이블 삭제
=> 장애 발생
SQL> drop table hr.insa_emp purge;

 

# redo log 확인
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# 로그 스위치 발생
SQL> alter system switch logfile;

# redo log 확인
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;
7 1 /u01/app/oracle/oradata/ora11g/redo07.log 100 YES ACTIVE
8 2 /u01/app/oracle/oradata/ora11g/redo08.log 100 YES ACTIVE
9 3 /u01/app/oracle/oradata/ora11g/redo09.log 100 NO CURRENT

SQL> ! ls /home/oracle1/arch1

tip) 텀이 길면 안된다! begin-end;/
SQL> begin
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo09.log', options=>dbms_logmnr.new);
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo08.log', options=>dbms_logmnr.addfile);
    dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora11g/redo07.log', options=>dbms_logmnr.addfile);
end;
/  

# redo log 확인
SQL> select db_name, filename from v$logmnr_logs;

SQL> begin
        dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;
/  

# 확인

SQL>
select to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss'), operation, sql_redo, sql_undo
from v$logmnr_contents
where seg_name = 'INSA_EMP';

# 종료
SQL> exec dbms_logmnr.end_logmnr

# 종료 후 실행하면 오류발생
select to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss'), operation, sql_redo, sql_undo
from v$logmnr_contents
where seg_name = 'TEST_20240122';

 

 

drop table insa_emp 복구하자! 

# DB 복제하기 
#) 디렉터리 생성
1) 복제 데이터베이스 생성시 필요한 데이터파일, 리두로그파일, 아카이브 파일 복사 
[oracle1@oracle ~]$ mkdir clone
[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ pwd
/home/oracle1/clone

[oracle1@oracle clone]$ cd
[oracle1@oracle ~]$ cd backup/arch/cold_20240117/
[oracle1@oracle cold_20240117]$ ls
backup.log     data01.dbf               insa_tbs01.dbf  redo09.log    temp01.dbf
backup.sh      example01.dbf            redo07.log      sysaux01.dbf  undo01.dbf
control01.ctl  initora11g_20240117.ora  redo08.log      system01.dbf  users01.dbf

#1) 필요한 파일만 갖고오자! 

[oracle1@oracle cold_20240117]$ cp -av system01.dbf /home/oracle1/clone
[oracle1@oracle cold_20240117]$ cp -av sysaux01.dbf /home/oracle1/clone
[oracle1@oracle cold_20240117]$ cp -av undo01.dbf /home/oracle1/clone => 필수!
[oracle1@oracle cold_20240117]$ cp -av users01.dbf /home/oracle1/clone => 복구 작업 tablespace
[oracle1@oracle cold_20240117]$ cp -av temp01.dbf /home/oracle1/clone

[oracle1@oracle cold_20240117]$ cp -av *.log /home/oracle1/clone
[oracle1@oracle cold_20240117]$ cd /home/oracle1/clone
[oracle1@oracle clone]$ ls
backup.log  redo08.log  sysaux01.dbf  temp01.dbf  users01.dbf
redo07.log  redo09.log  system01.dbf  undo01.dbf

[oracle1@oracle clone]$ ls
redo07.log  redo09.log    system01.dbf  undo01.dbf
redo08.log  sysaux01.dbf  temp01.dbf    users01.dbf

#) 아카이브 복제 
[oracle1@oracle clone]$ cd /home/oracle1/arch1
[oracle1@oracle arch1]$ ls
arch_1_1_1158524044.arc  arch_1_2_1158631896.arc  arch_1_5_1158524044.arc
arch_1_1_1158631896.arc  arch_1_3_1158524044.arc
arch_1_2_1158524044.arc  arch_1_4_1158524044.arc
[oracle1@oracle arch1]$ cp -av *.* /home/oracle1/clone

#) clone 디렉터리 확인 
[oracle1@oracle ~]$ cd clone/
[oracle1@oracle clone]$ ls
arch_1_1_1158524044.arc  arch_1_3_1158524044.arc  redo08.log    temp01.dbf
arch_1_1_1158631896.arc  arch_1_4_1158524044.arc  redo09.log    undo01.dbf
arch_1_2_1158524044.arc  arch_1_5_1158524044.arc  sysaux01.dbf  users01.dbf
arch_1_2_1158631896.arc  redo07.log               system01.dbf
[oracle1@oracle clone]$ pwd
/home/oracle1/clone

2) 초기 파라미터 파일, pfile 생성
[oracle1@oracle clone]$ vi $ORACLE_HOME/dbs/initclone.ora

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

3) clone DB 접속
[oracle1@oracle ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1

=> 다른 DB 로 접속하기 위한 경로
[oracle1@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? (입력)clone
ORACLE_HOME = [/home/oracle] ? (입력/위치)/u01/app/oracle/product/11.2.0.4/db_1
-> 잘못 지정되면 sqlplus 접속 오류
The Oracle base remains unchanged with value /u01/app/oracle

=> sql 접속
[oracle1@oracle ~]$ sqlplus / as sysdba

=> DB nomount까지 올리기, instance
SQL> startup nomount

4) control file 생성 (=> 없다면 생성!) 
SQL> alter database backup controlfile to trace as '/home/oracle1/new_control.sql';

SQL> CREATE CONTROLFILE ★SET DATABASE ★"CLONE" ★RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE (★위치)
  GROUP 7 '/home/oracle1/clone/redo07.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 8 '/home/oracle1/clone/redo08.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 9 '/home/oracle1/clone/redo09.log'  SIZE 100M BLOCKSIZE 512
DATAFILE (★위치)
  '/home/oracle1/clone/system01.dbf',
  '/home/oracle1/clone/sysaux01.dbf',
  '/home/oracle1/clone/users01.dbf',
  '/home/oracle1/clone/undo01.dbf'
CHARACTER SET AL32UTF8
; 

SQL> select status from v$instance;
=> mount 단계

#) control file 확인
SQL> select name from v$controlfile;

#) data file 확인
SQL> select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 

 

#) ★ 날짜 설정
=> ★ 운영 DB에 하지 않는다! clone DB로 진행한다! 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

############## ############## ##############
2024-01-22 12:14:02 DDL drop table hr.insa_emp purge;
############## ############## ##############

#) 시간으로 복구
SQL> recover database until time '2024-01-22 12:12:02' using backup controlfile
=> '2024-01-22 12:12:02'까지 복구 해줘
ORA-00279: change 858018 generated at 01/22/2024 10:24:39 needed for thread 1
ORA-00289: suggestion : /home/oracle1/clone/arch_1_1_1158631896.arc
ORA-00280: change 858018 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto (입력, 자동)
Log applied.
Media recovery complete.

#) DB resetlogs open
SQL> alter database open resetlogs;

#) DB instance 상태 및 DB  이름 확인
SQL> select status, instance_name from v$instance;

#) drop table  복구되었는지 확인
SQL> select count(*) from hr.insa_emp;

<clone DB>
SQL> !
[oracle1@oracle ~]$ pwd
/home/oracle1

exp hr/hr file=insa_emp.dmp tables=hr.insa_emp statistics=none
또는
exp sys/1234 file=insa_emp.dmp tables=hr.insa_emp statistics=none

=> 오류발생: 비밀번호 오류

#) export 하기 
[oracle1@oracle ~]$ exp hr/hr file=insa_emp.dmp tables=hr.insa_emp statistics=none(통계정보)

#) insa_emp.dmp 생성되었는지 확인

################
<ora11g DB>
SQL> select name from v$database;

SQL> select count(*) from hr.insa_emp;

=> import 하기 전!

SQL> !
[oracle1@oracle ~]$ ls

[oracle1@oracle ~]$ imp hr/hr file=insa_emp.dmp tables=insa_emp

=> import 하기 

SQL> select count(*) from hr.insa_emp;

################