# 요약
  ■ Log Miner
■ 복제 데이터베이스 : Clone DB
<< 시나리오 >> 운영중인 테이블 Drop + Purge
■ Export & Import
1. Export  : Table Level ... tables=hr.emp
2-1. Import ... fromuser=hr
2-2. Import ... data_only=y
2-3. Import ... touser
3. Export : User Level ... owner
4. Import





■ Log Miner
- Redo Log File에 있는 내용을 추출해서 SQL문장으로 변환해주는 유틸리티

# Log Miner 사용 설정
  SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES

# 데이터 예시
  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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          1 /u01/app/oracle/oradata/ora11g/redo01.log             50 YES       ACTIVE
         2          2 /u01/app/oracle/oradata/ora11g/redo02.log             50 NO        CURRENT
         3          0 /u01/app/oracle/oradata/ora11g/redo03.log             50 YES       UNUSED


CREATE TABLE hr.test_20240122 (id number, name varchar2(30));


INSERT INTO hr.test_20240122 (id, name) VALUES (1, 'oracle');

INSERT INTO hr.test_20240122 (id, name) VALUES (2, 'overwatch');

COMMIT;


UPDATE hr.test_20240122 SET name = 'itwill' WHERE id = 1;

DELETE FROM hr.test_20240122 WHERE id = 2;

COMMIT;


SELECT * FROM hr.test_20240122;
        ID NAME
---------- -----------------------------------------------
         1 itwill


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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          1 /u01/app/oracle/oradata/ora11g/redo01.log             50 YES       INACTIVE
         2          2 /u01/app/oracle/oradata/ora11g/redo02.log             50 NO        CURRENT
         3          0 /u01/app/oracle/oradata/ora11g/redo03.log             50 YES       UNUSED
# Log Miner 목록 생성
  BEGIN
    dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/ora11g/redo02.log', 
                            options => dbms_logmnr.new);

    dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/ora11g/redo01.log', 
                            options => dbms_logmnr.addfile);
END;
/

- new     : Log가 시작하는 최근파일
- addfile : 이전 Log 파일 추가


SELECT db_name, filename FROM v$logmnr_logs;
DB_NAME  FILENAME
-------- --------------------------------------------------
ORA11G   /u01/app/oracle/oradata/ora11g/redo01.log
ORA11G   /u01/app/oracle/oradata/ora11g/redo02.log
# Log Miner 시작
  BEGIN
    dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
END;
/

- current 상태의 Log File을 확인할 경우 데이터가 들어오지 않게 START ~ END 동안에 WRITE 작업을 중지해야한다.
# Log Miner Contents : start_logmnr를 수행한 session에서만 확인 가능하다
  SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, operation, sql_redo, sql_undo
FROM v$logmnr_contents
WHERE seg_name = 'TEST_20240122';

TIMESTAMP           OPERATION
------------------- --------------------------------
SQL_REDO
---------------------------------------------------------------------------------------------------------------
SQL_UNDO
---------------------------------------------------------------------------------------------------------------
2024-01-22 09:49:50 DDL
create table hr.test_20240122 (id number, name varchar2(30));

2024-01-22 09:51:16 INSERT
insert into "HR"."TEST_20240122"("ID","NAME") values ('1','oracle');
delete from "HR"."TEST_20240122" where "ID" = '1' and "NAME" = 'oracle' and ROWID = 'AAAVnMAAEAAAAIXAAA';

2024-01-22 09:51:20 INSERT
insert into "HR"."TEST_20240122"("ID","NAME") values ('2','overwatch');
delete from "HR"."TEST_20240122" where "ID" = '2' and "NAME" = 'overwatch' and ROWID = 'AAAVnMAAEAAAAIXAAB';

2024-01-22 09:52:45 UPDATE
update "HR"."TEST_20240122" set "NAME" = 'itwill' where "NAME" = 'oracle' and ROWID = 'AAAVnMAAEAAAAIXAAA';
update "HR"."TEST_20240122" set "NAME" = 'oracle' where "NAME" = 'itwill' and ROWID = 'AAAVnMAAEAAAAIXAAA';

2024-01-22 09:52:48 DELETE
delete from "HR"."TEST_20240122" where "ID" = '2' and "NAME" = 'overwatch' and ROWID = 'AAAVnMAAEAAAAIXAAB';
insert into "HR"."TEST_20240122"("ID","NAME") values ('2','overwatch');
# Log Miner 종료
  SQL> EXEC dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.


SQL> SELECT db_name, filename FROM v$logmnr_logs;
no rows selected





■ 복제 데이터베이스
- 운영 데이터 파일, 리두로그 파일을 이용해서 데이터베이스를 생성

1. COPY : Data File, Redo Log File
  # Cold Backup File
[oracle@oracle ~]$ /home/oracle/backup/arch/cold_20240117

[oracle@oracle cold_20240117]$ ls
control01.ctl  example01.dbf  initora11g_20240117.ora  redo01.log  redo02.log  
redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf


[oracle@oracle ~]$ mkdir clone/

cp -av /home/oracle/backup/arch/cold_20240117/*.dbf /home/oracle/clone/
cp -av /home/oracle/backup/arch/cold_20240117/*.log /home/oracle/clone/

> 필수 CP : SYSTEM, SYSAUX, UNDOTBS, 복구가 필요한 User Data File, Redo log File
> 옵션 CP : Archive Log File
> Control File을 복제하면 현재 DB를 그대로 사용한다는 것을 복구가 불가하다.


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

[oracle@oracle clone]$ ls
example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  
temp01.dbf  undotbs01.dbf  users01.dbf
2. pfile 생성
  # 복구가 필요한 DB의 버전 확인
SQL> SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


# Undo Tablespace 정보
SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS


# pfile 생성 : init[SID명].ora
[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'

:wq


# Control To Trace 수행 후 코드 복사
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/new_control.sql';

> 파일 내용에서 Set #2. RESETLOGS case에 해당하는 코드 복사
3. CLONE DB 접속
  # sqlplus 프로그램 위치 확인
[oracle@oracle ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1


# 접속할 DB 변경
[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 Instance 확인
[oracle@oracle ~]$ env | grep SID
ORACLE_SID=clone


[oracle@oracle ~]$ sqlplus / as sysdba
Connected to an idle instance.


SQL> STARTUP NOMOUNT
ORACLE instance started.


SQL> SELECT status, instance_name FROM v$instance;
STATUS          INSTANCE_NAME
--------------- ----------------
STARTED         clone

4. Control File 생성
  CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/undotbs01.dbf',
  '/home/oracle/clone/users01.dbf',
  '/home/oracle/clone/example01.dbf'
CHARACTER SET AL32UTF8
;

> 처음 생성 시 꼭 SET, RESETLOGS 사용한다.
> 중간에 공백줄 있으면 안된다.


SQL> SELECT status, instance_name FROM v$instance;
STATUS          INSTANCE_NAME
--------------- ----------------
MOUNTED          clone


> CONTROL FILE 생성 > 불안전한 복구 > RECOVER > RESETLOGS
5. Recovery
  # Control File을 기준으로 복구
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
ORA-00279: change 1166664 generated at 01/17/2024 16:57:54 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_1_1158511351.arc
ORA-00280: change 1166664 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL

Media recovery cancelled.
6. Open Resetlog
  SQL> ALTER DATABASE OPEN RESETLOGS;


SQL> SELECT name FROM v$database;
NAME
-----------------------------------------------
CLONE


SQL> SELECT count(*) FROM hr.employees;

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


SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
     FILE# FILE_NAME                                     TBS_NAME   STATUS          CHECKPOINT_CHANGE#
---------- --------------------------------------------- ---------- --------------- ------------------
         1 /home/oracle/clone/system01.dbf               SYSTEM     SYSTEM                     1166668
         2 /home/oracle/clone/sysaux01.dbf               SYSAUX     ONLINE                     1166668
         3 /home/oracle/clone/undotbs01.dbf              UNDOTBS    ONLINE                     1166668
         4 /home/oracle/clone/users01.dbf                USERS      ONLINE                     1166668
         5 /home/oracle/clone/example01.dbf              EXAMPLE    ONLINE                     1166668


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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          1 /home/oracle/clone/redo01.log                         50 NO        CURRENT
         2          0 /home/oracle/clone/redo02.log                         50 YES       UNUSED
         3          0 /home/oracle/clone/redo03.log                         50 YES       UNUSED


SQL> SELECT name FROM v$controlfile;
NAME                                       IS_RECOVERY_DEST_FILE  BLOCK_SIZE FILE_SIZE_BLKS
------------------------------------------ ---------------------- ---------- --------------
/home/oracle/clone/control01.ctl           NO                          16384            614


# Temp File은 필요 시 생성
SQL> SELECT * FROM v$tempfile;
no rows selected
7. DB 변경
  # CLONE DB -> ORA11G
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oracle ~]$ sqlplus / as sysdba

SQL> SELECT name FROM v$database;
NAME
-----------------------------------------------
ORA11G


# ORA11G  -> CLONE DB
[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> SELECT name FROM v$database;
NAME
-----------------------------------------------
CLONE
8. Clone DB 삭제
  SQL> SHUTDOWN ABORT
ORACLE instance shut down.


# Data File 및 PFile 삭제
[oracle@oracle ~]$ rm -r /home/oracle/clone/

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


[oracle@oracle ~]$ sqlplus / as sysdba
Connected to an idle instance.


SQL> STARTUP NOMOUNT
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora'


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


[oracle@oracle ~]$ sqlplus / as sysdba


SQL> SELECT name FROM v$database;

NAME
-----------------------------------------------
ORA11G


!!! DB 이름 항상 체크하자 !!!





<< 시나리오 >> 운영중인 테이블 Drop + Purge

# DB 운영 중
  SQL> CREATE TABLE hr.insa_emp TABLESPACE USERS AS SELECT * FROM hr.employees;

SQL> SELECT COUNT(*) FROM hr.insa_emp;
  COUNT(*)
----------
       107


SQL> ALTER TABLE hr.insa_emp ADD CONSTRAINT inda_id_pk PRIMARY KEY(employee_id);


SELECT constraint_name, constraint_type, search_condition, status, index_name
FROM dba_constraints
WHERE table_name = 'INSA_EMP';
CONSTRAINT_NAME                C SEARCH_CONDITION               STATUS          INDEX_NAME
------------------------------ - ------------------------------ --------------- -----------
SYS_C0011452                   C "JOB_ID" IS NOT NULL           ENABLED
SYS_C0011451                   C "HIRE_DATE" IS NOT NULL        ENABLED
SYS_C0011450                   C "EMAIL" IS NOT NULL            ENABLED
SYS_C0011449                   C "LAST_NAME" IS NOT NULL        ENABLED
INDA_ID_PK                     P                                ENABLED         INDA_ID_PK


SQL> ALTER SYSTEM SWITCH LOGFILE;
--Thread 1 advanced to log sequence 4 (LGWR switch)
--  Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          4 /u01/app/oracle/oradata/ora11g/redo01.log             50 NO        CURRENT
         2          2 /u01/app/oracle/oradata/ora11g/redo02.log             50 YES       INACTIVE
         3          3 /u01/app/oracle/oradata/ora11g/redo03.log             50 YES       ACTIVE



# 작업 오류
SQL> DROP TABLE hr.insa_emp PURGE;



SQL> ALTER SYSTEM SWITCH LOGFILE;
--Thread 1 advanced to log sequence 5 (LGWR switch)
--  Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          4 /u01/app/oracle/oradata/ora11g/redo01.log             50 YES       ACTIVE
         2          5 /u01/app/oracle/oradata/ora11g/redo02.log             50 NO        CURRENT
         3          3 /u01/app/oracle/oradata/ora11g/redo03.log             50 YES       ACTIVE


SQL> ! ls /home/oracle/arch1
arch_1_1_1158511351.arc  arch_1_2_1158511351.arc  arch_1_3_1158511351.arc  arch_1_4_1158511351.arc


>> 운영 중 Table Level 오작업으로 Time Base Recovery 시도
1. Log Miner
  # add_logfile : 시점 확인을 안하여 파일 다량 등록

BEGIN
    dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/ora11g/redo02.log', 
                            options => dbms_logmnr.new);
    dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/ora11g/redo01.log', 
                            options => dbms_logmnr.addfile);
    dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/ora11g/redo03.log', 
                            options => dbms_logmnr.addfile);
    dbms_logmnr.add_logfile(logfilename => '/home/oracle/arch1/arch_1_2_1158511351.arc', 
                            options => dbms_logmnr.addfile);
END;
/


# v$logmnr_logs : 대상 목록 확인
SELECT db_name, filename FROM v$logmnr_logs;
DB_NAME  FILENAME
-------- --------------------------------------------------
ORA11G   /home/oracle/arch1/arch_1_2_1158511351.arc
ORA11G   /u01/app/oracle/oradata/ora11g/redo03.log
ORA11G   /u01/app/oracle/oradata/ora11g/redo01.log
ORA11G   /u01/app/oracle/oradata/ora11g/redo02.log


# start_logmnr : 해당 세션에서 로그조회 시작

BEGIN
    dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
END;
/


# v$logmnr_contents : 작업 시간 체크
SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, operation, sql_redo, sql_undo
FROM v$logmnr_contents
WHERE seg_name = 'INSA_EMP';

TIMESTAMP           OPERATION
------------------- --------------------------------
SQL_REDO
---------------------------------------------------------------------------------------------------------------
SQL_UNDO
---------------------------------------------------------------------------------------------------------------
2024-01-22 11:56:50 DDL
create table hr.insa_emp tablespace users as select * from hr.employees;

2024-01-22 12:09:50 DDL
alter table hr.insa_emp add constraint inda_id_pk primary key(employee_id);

2024-01-22 12:13:52 DDL
drop table hr.insa_emp purge;


# end_logmnr : 로그조회 종료
SQL> EXEC dbms_logmnr.end_logmnr;
2. DB 복제
  [oracle@oracle ~]$ mkdir clone

/home/oracle/backup/arch/cold_20240117
[oracle@oracle cold_20240117]$ ls
control01.ctl  data01.dbf  example01.dbf  initora11g_20240119.ora  insa_tbs01.dbf  
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

/home/oracle/arch1
[oracle@oracle arch1]$ ls
arch_1_1_1158511351.arc  arch_1_2_1158511351.arc  arch_1_3_1158511351.arc  arch_1_4_1158511351.arc


# CP Data File, Redo Log File, Archive File
cp -av /home/oracle/backup/arch/cold_20240117/system01.dbf /home/oracle/clone/
cp -av /home/oracle/backup/arch/cold_20240117/sysaux01.dbf /home/oracle/clone/
cp -av /home/oracle/backup/arch/cold_20240117/undotbs01.dbf /home/oracle/clone/
cp -av /home/oracle/backup/arch/cold_20240117/users01.dbf /home/oracle/clone/
cp -av /home/oracle/backup/arch/cold_20240117/*.log /home/oracle/clone/
cp -av /home/oracle/arch1/*.* /home/oracle/clone/


/home/oracle/clone
[oracle@oracle clone]$ ls
arch_1_1_1158511351.arc  arch_1_2_1158511351.arc  arch_1_3_1158511351.arc  arch_1_4_1158511351.arc  
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf



# PFile 생성
[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'

:wq


[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 ~]$ env | grep SID
ORACLE_SID=clone


[oracle@oracle ~]$ sqlplus / as sysdba
Connected to an idle instance.


SQL> STARTUP NOMOUNT
ORACLE instance started.


# Control File 생성
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/clone/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/clone/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/clone/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/undotbs01.dbf',
  '/home/oracle/clone/users01.dbf'
CHARACTER SET AL32UTF8
;


SELECT status FROM v$instance;
STATUS
---------------
MOUNTED


SELECT name FROM v$controlfile;
NAME
-----------------------------------------------
/home/oracle/clone/control01.ctl


# 시간 작업하기 편하게 포맷 변경
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';


--2024-01-22 12:13:52 DDL
--drop table hr.insa_emp purge;

# Control File 정보를 기준으로 Time Base Recovery
SQL> RECOVER DATABASE UNTIL TIME '2024-01-22 12:13:00' USING BACKUP CONTROLFILE
ORA-00279: change 1167872 generated at 01/19/2024 18:34:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/arch_1_2_1158511351.arc
ORA-00280: change 1167872 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO

ORA-00326: log begins at change 1171376, need earlier change 1167872
ORA-00334: archived log: '/home/oracle/clone/arch_1_2_1158511351.arc'


SQL> ALTER DATABASE OPEN RESETLOGS;


SELECT status, instance_name FROM v$instance;
STATUS          INSTANCE_NAME
--------------- ----------------
OPEN            clone


SELECT count(*) FROM hr.insa_emp;
  COUNT(*)
----------
       107
# Export : cloneDB에서 내보내기
  [oracle@oracle ~]$ pwd
/home/oracle

[oracle@oracle ~]$ env | grep SID
ORACLE_SID=clone

[oracle@oracle ~]$ exp hr/hr file=insa_emp.dmp tables=hr.insa_emp
> 또는
    exp hr/hr file=insa_emp.dmp tables=insa_emp
    exp hr/hr file=insa_emp.dmp tables=hr.insa_emp statistics=none  -- 통계정보 제외한다
    exp system/oracle file=insa_emp.dmp tables=hr.insa_emp          -- 시스템 계정 사용가능

Export: Release 11.2.0.4.0 - Production on Mon Jan 22 14:16:54 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                       INSA_EMP        107 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.


[oracle@oracle ~]$ ls /home/oracle
arch1  backup  clone  database  insa_emp.dmp  new_control.sql  
p13390677_112040_LINUX_1of7.zip  p13390677_112040_LINUX_2of7.zip
# Import  : 기존 ora11gDB로 가져오기
  << ora11g : NEW SESSION >>

[oracle@oracle ~]$ sqlplus / as sysdba

SQL> SELECT status, instance_name FROM v$instance;
STATUS          INSTANCE_NAME
--------------- ----------------
OPEN            ora11g


SQL> SELECT count(*) FROM hr.insa_emp;
ORA-00942: table or view does not exist


[oracle@oracle ~]$ ls /home/oracle
arch1  backup  clone  database  insa_emp.dmp  new_control.sql  
p13390677_112040_LINUX_1of7.zip  p13390677_112040_LINUX_2of7.zip


[oracle@oracle ~]$ imp hr/hr file=insa_emp.dmp tables=hr.insa_emp
...
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00029: cannot qualify table name by owner (hr.insa_emp), use FROMUSER parameter
IMP-00000: Import terminated unsuccessfully

>> 접속계정 hr/hr 유저가 있기 때문에 tables에서는 소유자 제외하기
>> Export 시 exp hr/hr -> imp hr/hr 계정정보 유지


[oracle@oracle ~]$ imp hr/hr file=insa_emp.dmp tables=insa_emp
...
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table                     "INSA_EMP"        107 rows imported
Import terminated successfully without warnings.


SQL> SELECT count(*) FROM hr.insa_emp;

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


SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
     FILE# FILE_NAME                                     TBS_NAME   STATUS          CHECKPOINT_CHANGE#
---------- --------------------------------------------- ---------- --------------- ------------------
         1 /u01/app/oracle/oradata/ora11g/system01.dbf   SYSTEM     SYSTEM                     1180556
         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf   SYSAUX     ONLINE                     1180556
         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf  UNDOTBS    ONLINE                     1180556
         4 /u01/app/oracle/oradata/ora11g/users01.dbf    USERS      ONLINE                     1180556
         5 /u01/app/oracle/oradata/ora11g/example01.dbf  EXAMPLE    ONLINE                     1180556


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;
    GROUP#  SEQUENCE# MEMBER                                                MB ARCHIVED  STATUS
---------- ---------- --------------------------------------------- ---------- --------- ---------------
         1          4 /u01/app/oracle/oradata/ora11g/redo01.log             50 YES       INACTIVE
         2          5 /u01/app/oracle/oradata/ora11g/redo02.log             50 NO        CURRENT
         3          3 /u01/app/oracle/oradata/ora11g/redo03.log             50 YES       INACTIVE
# Clone DB 삭제
  # Data File, Redo Log File, Archive File 삭제
[oracle@oracle ~]$ rm -r /home/oracle/clone

# PFile 삭제
[oracle@oracle ~]$ rm -r $ORACLE_HOME/dbs/*clone*





■ Export & Import
- export는 오라클 데이터베이스가 서로 다른 하드웨어 및 소프트웨어 구성을 사용하는 플랫폼에서 데이터베이스 간 데이터 객체를 이동할 수 있는 방법
- import는 export 덤프 파일에서 데이터베이스에 로드하는 가능을 제공한다.
- Export : exp system/oracle -> Import : imp system/oracle >> 작업수행에 사용한 계정 정보가 동일해야한다.

# DB 운영 중
  SQL> DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

SQL> CREATE TABLE hr.emp AS SELECT * FROM hr.employees;

SQL> SELECT COUNT(*) FROM hr.emp;
  COUNT(*)
----------
       107


SQL> ALTER TABLE hr.emp ADD CONSTRAINT empid_pk PRIMARY KEY(employee_id);


SELECT constraint_name, constraint_type, search_condition, status, index_name
FROM dba_constraints
WHERE table_name = 'EMP';
CONSTRAINT_NAME                C SEARCH_CONDITION               STATUS          INDEX_NAME
------------------------------ - ------------------------------ --------------- ------------
SYS_C0011479                   C "JOB_ID" IS NOT NULL           ENABLED
SYS_C0011478                   C "HIRE_DATE" IS NOT NULL        ENABLED
SYS_C0011477                   C "EMAIL" IS NOT NULL            ENABLED
SYS_C0011476                   C "LAST_NAME" IS NOT NULL        ENABLED
FK_DEPTNO                      R                                ENABLED
PK_EMP                         P                                ENABLED         PK_EMP
EMPID_PK                       P                                ENABLED         EMPID_PK
1. Export  : Table Level ... tables=hr.emp
  [oracle@oracle ~]$ exp userid=system/oracle tables=hr.emp file=hr_emp.dmp statistics=none
...
About to export specified tables via Conventional Path ...
Current user changed to HR
. . exporting table                            EMP        107 rows exported
Export terminated successfully without warnings.

- sys
- system : db on/off 불가


[oracle@oracle ~]$ cd /home/oracle
[oracle@oracle ~]$ ls
backup  database    insa_emp.dmp     p13390677_112040_LINUX_1of7.zip
arch1   hr_emp.dmp  new_control.sql  p13390677_112040_LINUX_2of7.zip

2-1. Import ... fromuser=hr : hr 유저한테 받은거니 hr유저한테 import 한다.
  # 작업 오류 발생 Drop Table + Purge
SQL> DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;


# Import
[oracle@oracle ~]$ imp userid=system/oracle file=hr_emp.dmp
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

>> 소유자 지정 필요


[oracle@oracle ~]$ imp userid=system/oracle file=hr_emp.dmp fromuser=hr
...
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing HR's objects into HR
. . importing table                          "EMP"        107 rows imported
Import terminated successfully without warnings.


SQL> SELECT count(*) FROM hr.emp;

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


SELECT constraint_name, constraint_type, search_condition, status, index_name
FROM dba_constraints
WHERE table_name = 'EMP';
CONSTRAINT_NAME                C SEARCH_CONDITION               STATUS          INDEX_NAME
------------------------------ - ------------------------------ --------------- ------------------------
SYS_C0011484                   C "JOB_ID" IS NOT NULL           ENABLED
SYS_C0011483                   C "HIRE_DATE" IS NOT NULL        ENABLED
SYS_C0011482                   C "EMAIL" IS NOT NULL            ENABLED
SYS_C0011481                   C "LAST_NAME" IS NOT NULL        ENABLED
FK_DEPTNO                      R                                ENABLED
PK_EMP                         P                                ENABLED         PK_EMP
EMPID_PK                       P                                ENABLED         EMPID_PK
2-2. Import ... data_only=y : 데이터만 가져오기.
  # 작업 오류 발생 : Truncate Table
SQL> TRUNCATE TABLE hr.emp;


SQL> SELECT count(*) FROM hr.emp;
  COUNT(*)
----------
         0

> export 덤프파일 있다면 import
> cloneDB + Time Base Recovery : DB에 따라 시간소요 주의


[oracle@oracle ~]$ imp system/oracle file=hr_emp.dmp fromuser=hr tables=emp data_only=y
...
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing HR's objects into HR
. . importing table                          "EMP"        107 rows imported
Import terminated successfully without warnings.


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

  COUNT(*)
----------
       107
2-3. Import ... touser : 소유자를 변경하여 가져오기.
  # 유저 생성
SQL> CREATE USER michael IDENTIFIED BY ORACLE DEFAULT TABLESPACE USERS QUOTA 10m ON USERS;

SQL> GRANT CREATE SESSION TO michael;


[oracle@oracle ~]$ imp system/oracle file=hr_emp.dmp fromuser=hr touser=michael
...
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into MICHAEL
. . importing table                          "EMP"        107 rows imported
Import terminated successfully without warnings.



<< MICHAEL SESSION >>

conn michael/oracle


SQL> SELECT * FROM tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE


SQL> SELECT count(*) FROM emp;
SQL> SELECT count(*) FROM michael.emp;
  COUNT(*)
----------
       107
3. Export : User Level ... owner
  << SYS >>

# Michael이 소유하고 있는 모든 객체를 내보낸다
[oracle@oracle ~]$ exp system/oracle owner=michael file=michael_owner.dmp
...
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MICHAEL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MICHAEL
About to export MICHAEL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MICHAEL's tables via Conventional Path ...
. . exporting table                            EMP        107 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.


# hr이 소유하고 있는 모든 객체를 내보낸다
[oracle@oracle ~]$ exp system/oracle owner=hr file=hr_owner.dmp
...
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                       COPY_EMP        107 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      COUNTRIES         25 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    DEPARTMENTS         27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP        107 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      EMPLOYEES        107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       INSA_EMP        107 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           JOBS         19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    JOB_HISTORY         10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      LOCATIONS         23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        REGIONS          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        TEST_01        107 rows exported
. . exporting table                        TEST_02        107 rows exported
. . exporting table                  TEST_20240122          1 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
4. Import
  << SYS >>

# 작업오류 : michael 유저가 가지고 있는 객체와 함께 지워버렸다.
SQL> DROP USER michael CASCADE;


> Import 시 USER 정보는 직접 재생성 해야한다.

CREATE USER michael IDENTIFIED BY ORACLE DEFAULT TABLESPACE USERS QUOTA 10m ON USERS;

GRANT CREATE SESSION TO michael;


[oracle@oracle ~]$ imp system/oracle fromuser=michael file=michael_owner.dmp
...
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing MICHAEL's objects into MICHAEL
. . importing table                          "EMP"        107 rows imported
Import terminated successfully without warnings.



<< MICHAEL >>

SQL> conn michael/oracle
Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

 

저작자표시

'Oracle 11g > Backup & Recovery' 카테고리의 다른 글

72 Backup&Recovery, RMAN  (0) 2024.01.24
71 Backup&Recovery, Export & Import, Data Pump  (0) 2024.01.23
69 Backup&Recovery, Archive Log Mode  (0) 2024.01.19
68 Backup&Recovery, Archive Log Mode, control file 손상  (0) 2024.01.18
67 Backup&Recovery, Archive Log Mode,  (0) 2024.01.17
'Oracle 11g/Backup & Recovery'의 다른글
  • 현재글70 Backup&Recovery, Log Miner, Clone DB, Export & Import

티스토리툴바