[2025-08-30] Oracle 19c 2Node RAC CDB 환경에서 특정 PDB의 DROP TABLE PURGE 된 테이블을 원격지 Single 서버의 CDB_PDB로 RMAN Duplicate로 불완전 복구 후 Export·Import하여 원래 RAC PDB로 복원하는 절차

kyoungoh.lee 2025. 8. 30. 09:28

2025. 8. 30. 09:28

■ [2025-08-30] Oracle 19c 2Node RAC CDB 환경에서 특정 PDB의 DROP TABLE PURGE 된 테이블을 원격지 Single 서버의 CDB_PDB로 RMAN Duplicate로 불완전 복구 후 Export·Import하여 원래 RAC PDB로 복원하는 절차 (블로그)

RMAN Duplicate로 복구할 필요가 없는 PDB는 복구하지 않을 것임
RMAN Duplicate로 복구 대상 PDB의 복구할 필요가 없는 테이블 스페이스는 복구하지 않을 것임
RMAN Duplicate로 복구 시 CDB$ROOT 및 PDB$SEED는 반드시 가져와야함

1. 실습 환경

<소스> -> 특정 테이블이 DROP TABLE PURGE가 된 DB
OS Version : Oracle Linux Server 7.9 (Linux rdb01d 5.4.17-2102.201.3.el7uek.x86_64)
DB Versionn : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (Version 19.27.0.0.0)
RAC1 : ol7ora19r1(192.168.0.21) : ORA19R1(인스턴스명)
RAC2 : ol7ora19r2(192.168.0.22) : ORA19R2(인스턴스명)
DB Info
   CDB : ORA19R
   PDB : ORA19RP1, PDB1
   DROP TABLE PURGE가 ORA19RP1에서 발생

<타켓> -> 특정 테이블이 복구될 DB
OS Version : Oracle Linux Server 7.9 (Linux rdb01d 5.4.17-2102.201.3.el7uek.x86_64)
DB Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (Version 19.27.0.0.0)
SINGLE : tdb02t(192.168.0.61) : ORA19R(인스턴스명)
DB Info
   CDB : ORA19R
   PDB : ORA19RP1, PDB1
   ORA19RP1 PDB를 복구

2. 소스에서 pdb 현황 확인

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 22 16:35:15 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R1]$ show con_name;

CON_NAME
------------------------------
CDB$ROOT

[ol7ora19r1][SYS@ORA19R1]$ show pdbs;

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 ORA19RP1                       READ WRITE NO
         4 PDB1                           READ WRITE NO
--> 현재 ORA19R CDB내에는 ORA19R1 과 PDB1 이라는 PDB가 존재하는 상황임
--> 이번 테스트에서는 ORA19R1 PDB내에 테이블을 생성 한 후 백업 받은 후 DROP TABLE PURGE를 한 후
--> 백업 받은 백업본을 원격지 Single 서버에 RMAN Duplicate로
--> Restore/Recover (시점 복구) 하여 DROP된 데이터를 export한 후
--> 다시 소스에 Import 시킬 것임

--정리하자면,
--소스 RAC의 PDB인 ORA19RP1에서 DROP PURGE된 테이블 → 타겟 Single PDB인 ORA19RP1에서 복구 후 Export → 다시 소스 RAC로 Import

--tnsnames.ora 확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias cdt
alias cdt='cd $ORACLE_HOME/network/admin'

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cdt
[ORA19R1:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/network/admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA19R =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.25)(PORT = 1521))
          (LOAD_BALANCE = OFF)
          (FAILOVER = ON)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19R)
          (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
    )
)

ORA19R_ORA19RP1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.25)(PORT = 1521))
          (LOAD_BALANCE = OFF)
          (FAILOVER = ON)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19RP1)
          (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
    )
)

ORA19R_PDB1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.25)(PORT = 1521))
          (LOAD_BALANCE = OFF)
          (FAILOVER = ON)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
          (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
    )
)

T_ORA19R =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19R)
    )
)

T_ORA19R_ORA19RP1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19RP1)
    )
)

--tnsping으로 소스에서 타켓으로 통신 가능한지 확인

--CDB용 확인
[ORA19R1:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/network/admin]$ tnsping T_ORA19R

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-AUG-2025 13:46:38

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19R)))
OK (10 msec)

--PDB용 확인
[ORA19R1:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/network/admin]$ tnsping T_ORA19R_ORA19RP1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-AUG-2025 13:46:48

Used parameter files:

3. 특정 pdb에 접속하여 신규 유저 및 테이블 생성

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ssp1
alias ssp='rlwrap sqlplus sys/oracle@ORA19R_ORA19RP1 as sysdba'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ssp1

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 18 22:41:41 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$
DROP USER TUNER CASCADE;
CREATE USER TUNER IDENTIFIED BY "oracle";
GRANT RESOURCE, DBA, CONNECT TO TUNER;
ALTER USER TUNER ACCOUNT UNLOCK;

DROP TABLESPACE TUNER_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TUNER_DATA1
DATAFILE '+DATA1' SIZE 100M
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;

DROP TABLESPACE TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TUNER_IDX1
DATAFILE '+DATA1' SIZE 100M
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;

DROP TABLESPACE TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1' SIZE 64M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;

ALTER USER TUNER DEFAULT TABLESPACE TUNER_DATA1;
ALTER USER TUNER TEMPORARY TABLESPACE TUNER_TEMP;

DROP TABLE TUNER.TB_CUST PURGE;

CREATE TABLE TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;

COMMENT ON COLUMN TUNER.TB_CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_ID IS '고객아이디';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NM IS '고객명';
COMMENT ON COLUMN TUNER.TB_CUST.BRTHDY IS '생년월일';
COMMENT ON COLUMN TUNER.TB_CUST.SEX_CD IS '성별코드';
COMMENT ON COLUMN TUNER.TB_CUST.JOIN_DT IS '가입일시';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT ON COLUMN TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN TUNER.TB_CUST.INPUT_DT IS '입력일시';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_CUST IS '고객';

ALTER TABLE TUNER.TB_CUST NOLOGGING;

--데이터 입력
INSERT /*+ APPEND */
INTO TUNER.TB_CUST
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS CUST_NO
     , DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
     , DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
     , TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS BRTHDY
     , 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS SEX_CD
     , TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS JOIN_DT
     , 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS CUST_STS_CD
     , DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
     , TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
     , NULL AS UPDT_ID
     , NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <= 100000;

COMMIT;

CREATE UNIQUE INDEX TUNER.PK_TB_CUST ON TUNER.TB_CUST(CUST_NO) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_CUST PARALLEL 1;
ALTER INDEX TUNER.PK_TB_CUST LOGGING;
ALTER TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST PRIMARY KEY (CUST_NO);

CREATE INDEX TUNER.IDX_TB_CUST_01 ON TUNER.TB_CUST(CUST_STS_CD) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_01 PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_01 LOGGING;

CREATE INDEX TUNER.IDX_TB_CUST_02 ON TUNER.TB_CUST(BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_02 PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_02 LOGGING;

CREATE INDEX TUNER.IDX_TB_CUST_03 ON TUNER.TB_CUST(CUST_STS_CD, BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03 PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_03 LOGGING;

--다시 LOGGING으로 설정 (중요!)
ALTER TABLE TUNER.TB_CUST LOGGING;

--통계 정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST', method_opt => 'for all indexed columns' , cascade => true, DEGREE=> 4);

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ select count(*) from tuner.tb_cust;

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

1 row selected.

Elapsed: 00:00:00.03
--> tuner.tb_cust 테이블 생성 완료함

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
5084624

1 row selected.

Elapsed: 00:00:00.07
--> tuner.tb_cust 테이블 생성 직후 SCN번호

SELECT TO_CHAR(SCN_TO_TIMESTAMP(5084624), 'YYYY-MM-DD HH24:MI:SS') AS TS FROM DUAL;

TS
-------------------
2025-08-29 14:19:48
--> tuner.tb_cust 테이블 생성 직후 SCN번호에 대한 timestamp

4. 소스에서 풀 백업 작업 수행

--소스에서 RMAN BACKUP RUN 스크립트를 생성하는 SQL문 실행
SELECT 'RUN {
sql ''alter system archive log current'';
sql ''alter system checkpoint'';
crosscheck backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog all;' AS BACKUP_SCRIPT FROM DUAL
UNION ALL
SELECT 'BACKUP AS COMPRESSED BACKUPSET TABLESPACE '
     || CASE WHEN CON_NM = 'CDB$ROOT' THEN TS_NM
             WHEN CON_NM = 'PDB$SEED' THEN '"'||CON_NM||'"'||':'||TS_NM
       ELSE CON_NM||':'||TS_NM
     END
     || ' FORMAT '
|| ''''|| '/home/oracle/ORA19R_BACKUP/'
|| CASE WHEN CON_NM = 'CDB$ROOT' THEN 'CDB' || '_'
             WHEN CON_NM = 'PDB$SEED' THEN 'PDBSEED' ||'_'
       ELSE CON_NM || '_'
     END
||TS_NM||'_%U.bkp' || '''' || ';' AS BACKUP_SCRIPT
FROM
     (
SELECT (SELECT L.NAME FROM V$CONTAINERS L WHERE L.CON_ID = A.CON_ID) AS CON_NM
    , A.TABLESPACE_NAME AS TS_NM
    FROM CDB_TABLESPACES A
   WHERE A.CONTENTS IN ('PERMANENT', 'UNDO')
UNION ALL
   SELECT 'PDB$SEED' AS CON_NM, 'SYSTEM'   AS TS_NM FROM DUAL UNION ALL
   SELECT 'PDB$SEED' AS CON_NM, 'SYSAUX'   AS TS_NM FROM DUAL UNION ALL
   SELECT 'PDB$SEED' AS CON_NM, 'UNDOTBS1' AS TS_NM FROM DUAL
ORDER BY CON_NM, TS_NM
     ) A
UNION ALL
SELECT 'delete noprompt obsolete;
delete noprompt expired backup;
}' AS BACKUP_SCRIPT
FROM DUAL
;

<결과>

RUN {
sql 'alter system archive log current';
sql 'alter system checkpoint';
crosscheck backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
BACKUP AS COMPRESSED BACKUPSET TABLESPACE SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE UNDOTBS2 FORMAT '/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE USERS FORMAT '/home/oracle/ORA19R_BACKUP/CDB_USERS_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:TUNER_DATA1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:TUNER_IDX1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:UNDO_2 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:USERS FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_USERS_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:TUNER_DATA1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:TUNER_IDX1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_TUNER_IDX1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_UNDOTBS1_%U.bkp';
delete noprompt obsolete;
delete noprompt expired backup;
}
--> 이걸로 테이블 스페이스 단위로 백업을 할 것임

--소스에서 백업받을 디렉토리 생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p /home/oracle/ORA19R_BACKUP/autobackup
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -l /home/oracle/ORA19R_BACKUP/autobackup
total 0

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cd /home/oracle/ORA19R_BACKUP
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias rt
alias rt='rman target /'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Aug 18 23:01:48 2025
Version 19.27.0.0.0

connected to target database: ORA19R (DBID=1831232271)

RMAN>

run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/ORA19R_BACKUP/autobackup/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/ORA19R_BACKUP/snapcf_CA.f';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
#CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; #For ADG
}

RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
sql 'alter system archive log current';
sql 'alter system checkpoint';
crosscheck backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
BACKUP AS COMPRESSED BACKUPSET TABLESPACE SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE UNDOTBS2 FORMAT '/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE USERS FORMAT '/home/oracle/ORA19R_BACKUP/CDB_USERS_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:TUNER_DATA1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:TUNER_IDX1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:UNDO_2 FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE ORA19RP1:USERS FORMAT '/home/oracle/ORA19R_BACKUP/ORA19RP1_USERS_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE "PDB$SEED":UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:SYSAUX FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:TUNER_DATA1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:TUNER_IDX1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_TUNER_IDX1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET TABLESPACE PDB1:UNDOTBS1 FORMAT '/home/oracle/ORA19R_BACKUP/PDB1_UNDOTBS1_%U.bkp';
delete noprompt obsolete;
delete noprompt expired backup;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
--> 테이블스페이스 별로 백업을 받음!

run {
crosscheck archivelog all;
backup archivelog all format '/home/oracle/ORA19R_BACKUP/ARCHIVE_%d_%T_%u_s%s_p%p' delete input;
delete backup of archivelog all completed before 'SYSDATE-14';
}

RMAN> quit
quit

Recovery Manager complete.

--RMAN 백업 파일 확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ls -lRa /home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total 1763236
drwxr-xr-x. 3 oracle oinstall      4096 Aug 29 14:53 .
drwxrwxr-x. 10 oracle oinstall      4096 Aug 29 13:32 ..
-rw-r-----. 1 oracle asmadmin    136704 Aug 29 14:53 ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1
drwxr-xr-x. 2 oracle oinstall        70 Aug 29 14:54 autobackup
-rw-r-----. 1 oracle asmadmin 201998336 Aug 29 14:48 CDB_SYSAUX_bp42bdcs_377_1_1.bkp
-rw-r-----. 1 oracle asmadmin 459513856 Aug 29 14:49 CDB_SYSTEM_bq42bddm_378_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1122304 Aug 29 14:49 CDB_UNDOTBS1_br42bdf6_379_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1114112 Aug 29 14:49 CDB_UNDOTBS2_bs42bdfb_380_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1245184 Aug 29 14:49 CDB_USERS_bt42bdfg_381_1_1.bkp
-rw-r-----. 1 oracle asmadmin 72982528 Aug 29 14:49 ORA19RP1_SYSAUX_bu42bdfi_382_1_1.bkp
-rw-r-----. 1 oracle asmadmin 266625024 Aug 29 14:50 ORA19RP1_SYSTEM_bv42bdg3_383_1_1.bkp
-rw-r-----. 1 oracle asmadmin   7872512 Aug 29 14:50 ORA19RP1_TUNER_DATA1_c042bdha_384_1_1.bkp
-rw-r-----. 1 oracle asmadmin   2498560 Aug 29 14:50 ORA19RP1_TUNER_IDX1_c142bdhb_385_1_1.bkp
-rw-r-----. 1 oracle asmadmin   5865472 Aug 29 14:50 ORA19RP1_UNDO_2_c342bdhi_387_1_1.bkp
-rw-r-----. 1 oracle asmadmin   5406720 Aug 29 14:50 ORA19RP1_UNDOTBS1_c242bdhf_386_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1073152 Aug 29 14:50 ORA19RP1_USERS_c442bdhn_388_1_1.bkp
-rw-r-----. 1 oracle asmadmin 71483392 Aug 29 14:51 PDB1_SYSAUX_c842bdji_392_1_1.bkp
-rw-r-----. 1 oracle asmadmin 266674176 Aug 29 14:52 PDB1_SYSTEM_c942bdk3_393_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1458176 Aug 29 14:52 PDB1_TUNER_DATA1_ca42bdl7_394_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1351680 Aug 29 14:52 PDB1_TUNER_IDX1_cb42bdlh_395_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1097728 Aug 29 14:52 PDB1_UNDOTBS1_cc42bdlk_396_1_1.bkp
-rw-r-----. 1 oracle asmadmin 64561152 Aug 29 14:50 PDBSEED_SYSAUX_c542bdhp_389_1_1.bkp
-rw-r-----. 1 oracle asmadmin 265166848 Aug 29 14:51 PDBSEED_SYSTEM_c642bdi4_390_1_1.bkp
-rw-r-----. 1 oracle asmadmin 85991424 Aug 29 14:51 PDBSEED_UNDOTBS1_c742bdj2_391_1_1.bkp
-rw-r-----. 1 oracle asmadmin 20299776 Aug 29 14:54 snapcf_CA.f

/home/oracle/ORA19R_BACKUP/autobackup:
total 39876
drwxr-xr-x. 2 oracle oinstall 70 Aug 29 14:54 .
drwxr-xr-x. 3 oracle oinstall 4096 Aug 29 14:53 ..
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 14:52 c-1831232271-20250829-02
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 14:54 c-1831232271-20250829-03
--> 'c-1831232271-20250829-03' --> 이게 나중에 백업받은 control file 임

5. 소스에서 DROP TABLE PURGE 가 되는 사고가 발생함!

[ORA19R1:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/network/admin]$ alias ssp1
alias ssp1='rlwrap sqlplus sys/oracle@ORA19R_ORA19RP1 as sysdba'

[ORA19R1:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/network/admin]$ ssp1
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 18 23:14:00 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ show con_name;

CON_NAME
------------------------------
ORA19RP1

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORA19RP1 READ WRITE NO

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ select count(*) from tuner.tb_cust;

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

1 row selected.

Elapsed: 00:00:00.05

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CUR_DATE FROM DUAL;

CUR_DATE
-------------------
2025-08-29 14:57:07

1 row selected.

Elapsed: 00:00:00.04

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
5090500

1 row selected.

Elapsed: 00:00:01.00

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT TO_CHAR(SCN_TO_TIMESTAMP(5090500), 'YYYY-MM-DD HH24:MI:SS') AS TS FROM DUAL;

TS
-------------------
2025-08-29 14:57:20

1 row selected.

Elapsed: 00:00:00.01
--> 해당 scn번호(5090500)가 복구 목표인 SCN번호임
--> 결국 해당 SCN번호까지만 불완전 복구를 하면 TUNE.TB_CUST 테이블의 내용을 다시 볼수 있는 것임!

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ drop table tuner.tb_cust purge;

Table dropped.

Elapsed: 00:00:04.47
--> drop table purge를 하는 사고가 발생함!!!!

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ select * from tuner.tb_cust;
select * from tuner.tb_cust
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
5090750
--> drop table purge 를 한 이후 시점의 scn번호임

1 row selected.

Elapsed: 00:00:00.01

--> SCN : 5090500 시점에는 TUNER.TB_CUST 테이블이 존재했었던 상황임!
--> 결국 해당 시점까지 불완전 복구를 해야하는 상황임!

6. 소스에서 아카이브를 발생 시킴

--grid os user
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl +FRA1/ORA19R/ARCHIVELOG
ASMCMD-8002: entry 'ARCHIVELOG' does not exist in directory '+FRA1/ORA19R/'
--> 아카이브 로그가 없음 (백업 시 백업받고 불필요한 아카이브를 삭제한 상태인 것임!)

--oracle os user
--아카이브를 발생 시키려면 CDB로 접속해야함

[ORA19R1:oracle@ORA19R1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19R1:oracle@ORA19R1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 18 23:24:26 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R1]$ ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.
Elapsed: 00:00:03.39
--> RAC 1, 2에서 각각 아카이브 로그가 생성됨

-- grid os user
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl +FRA1/ORA19R/ARCHIVELOG
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
                             Y                                      2025_08_29/
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl +FRA1/ORA19R/ARCHIVELOG/2025_08_29
Type        Redund Striped Time             Sys Block_Size Blocks   Bytes    Space Name
ARCHIVELOG UNPROT COARSE   AUG 29 15:00:00 Y           512     542 277504 4194304 thread_1_seq_119.265.1210431631
ARCHIVELOG UNPROT COARSE   AUG 29 15:00:00 Y           512      55   28160 4194304 thread_2_seq_87.312.1210431633
--> RAC 1, 2에서 각각 아카이브 로그가 생성됨

--oracle os user
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 23 00:29:36 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

--drop table purge 한 테이블이 존재했었던 scn인 5090500 시점으로 가기 위해서 필요한 아카이브 로그를 확인
--여기서 조회되는 아카이브로그는 scn 5090500 으로 가기 위한 필수 아카이브 로그임
col name for a80
select NAME, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# from v$archived_log where 5090500 between FIRST_CHANGE# and NEXT_CHANGE#;

NAME                                                                                THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------------------------------------------------------------------------------- ---------- ---------- ------------- ------------
+FRA1/ORA19R/ARCHIVELOG/2025_08_29/thread_1_seq_119.265.1210431631                        1          119       5090214      5090842
+FRA1/ORA19R/ARCHIVELOG/2025_08_29/thread_2_seq_87.312.1210431633                         2           87       5090211      5090846
--> thread 1의 119 번과 thread 2 의 87 번인 2개의 아카이브 로그는 rman으로 추가적으로 백업을 진행해야함

--rman에서 아카이브 로그 백업 정보 확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 23 08:20:57 2025
Version 19.27.0.0.0

connected to target database: ORA19R (DBID=1831232271)

--scn 5090500이 걸쳐있는 아카이브 로그 확인
RMAN> LIST ARCHIVELOG FROM SCN 5090500;
LIST ARCHIVELOG FROM SCN 5090500;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORA19R
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
229     1    119     A 2025-08-29 14:53:55
        Name: +FRA1/ORA19R/ARCHIVELOG/2025_08_29/thread_1_seq_119.265.1210431631

230 2 87 A 2025-08-29 14:53:54
Name: +FRA1/ORA19R/ARCHIVELOG/2025_08_29/thread_2_seq_87.312.1210431633

--scn 5090500 까지 가는데 필요한 아카이브로그이 백업본이 있는 확인
RMAN> LIST BACKUP OF ARCHIVELOG FROM SCN 5090500;
LIST BACKUP OF ARCHIVELOG FROM SCN 5090500;
specification does not match any backup in the repository

RMAN> LIST BACKUP OF ARCHIVELOG ALL;
LIST BACKUP OF ARCHIVELOG ALL;
using target database control file instead of recovery catalog

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

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
390     133.00K    DISK        00:00:00     2025-08-29 14:53:58
        BP Key: 390   Status: AVAILABLE Compressed: NO Tag: TAG20250829T145357
        Piece Name: /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1

List of Archived Logs in backup set 390
Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
---- ------- ---------- ------------------- ---------- ---------
1    118     5089142    2025-08-29 14:47:27 5090214    2025-08-29 14:53:55
2    86      5089146    2025-08-29 14:47:28 5090211    2025-08-29 14:53:54
--> 백업된 리스트에 없는 것을 알 수 있음 (결국 drop table purge 시점으로 복구하려면 해당 아카이브를 백업받아서 타켓에 가져가야함!)

--결국 thread 1의 119 번과 thread 2 의 87 번인 2개의 아카이브 로그는 rman으로 추가적으로 백업을 진행해야함

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Aug 18 23:30:57 2025
Version 19.27.0.0.0

connected to target database: ORA19R (DBID=1831232271)

RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 119 THREAD 1 FILESPERSET 1 FORMAT '/home/oracle/ORA19R_BACKUP/ARCHIVE_%d_t%h_s%e_set%s_p%p_%T.bkp';
Starting backup at 2025-08-29 15:08:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 instance=ORA19R1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=119 RECID=229 STAMP=1210431631
channel ORA_DISK_1: starting piece 1 at 2025-08-29 15:08:14
channel ORA_DISK_1: finished piece 1 at 2025-08-29 15:08:16
piece handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp tag=TAG20250829T150814 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2025-08-29 15:08:16

Starting Control File and SPFILE Autobackup at 2025-08-29 15:08:16
piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250829-04 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-08-29 15:08:19

--> 'c-1831232271-20250829-04' 해당 컨트롤 파일에는 아카이브를 백업 받은 내용도 같이 저장된 상태임
--> 이부분을 이해하는것이 매우 중요함

RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 87 THREAD 2 FILESPERSET 1 FORMAT '/home/oracle/ORA19R_BACKUP/ARCHIVE_%d_t%h_s%e_set%s_p%p_%T.bkp';
Starting backup at 2025-08-29 15:08:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=87 RECID=230 STAMP=1210431633
channel ORA_DISK_1: starting piece 1 at 2025-08-29 15:08:54
channel ORA_DISK_1: finished piece 1 at 2025-08-29 15:08:55
piece handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp tag=TAG20250829T150854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-08-29 15:08:55

Starting Control File and SPFILE Autobackup at 2025-08-29 15:08:55
piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250829-05 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-08-29 15:08:58

--> 'c-1831232271-20250829-05' 해당 컨트롤 파일에는 아카이브를 백업 받은 내용도 같이 저장된 상태임
--> 이부분을 이해하는것이 매우 중요함

RMAN> LIST BACKUP OF ARCHIVELOG FROM SCN 5090500;
LIST BACKUP OF ARCHIVELOG FROM SCN 5090500;
using target database control file instead of recovery catalog

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

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
392     273.50K    DISK        00:00:01     2025-08-29 15:08:15
        BP Key: 392   Status: AVAILABLE Compressed: YES Tag: TAG20250829T150814
        Piece Name: /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp

List of Archived Logs in backup set 392
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 119 5090214 2025-08-29 14:53:55 5090842 2025-08-29 15:00:31

BS Key Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
394     30.00K     DISK        00:00:00     2025-08-29 15:08:54
        BP Key: 394   Status: AVAILABLE Compressed: YES Tag: TAG20250829T150854
        Piece Name: /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp

List of Archived Logs in backup set 394
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
2 87 5090211 2025-08-29 14:53:54 5090846 2025-08-29 15:00:32
--> scn 5090500으로 가기 위한 아카이브 백업본이 존재하는 상황임!

[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ls -lRa /home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total 1763548
drwxr-xr-x. 3 oracle oinstall      4096 Aug 29 15:08 .
drwxrwxr-x. 10 oracle oinstall      4096 Aug 29 13:32 ..
-rw-r-----. 1 oracle asmadmin    136704 Aug 29 14:53 ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1
-rw-r-----. 1 oracle asmadmin    280576 Aug 29 15:08 ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp
-rw-r-----. 1 oracle asmadmin     31232 Aug 29 15:08 ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp
drwxr-xr-x. 2 oracle oinstall      4096 Aug 29 15:08 autobackup
-rw-r-----. 1 oracle asmadmin 201998336 Aug 29 14:48 CDB_SYSAUX_bp42bdcs_377_1_1.bkp
-rw-r-----. 1 oracle asmadmin 459513856 Aug 29 14:49 CDB_SYSTEM_bq42bddm_378_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1122304 Aug 29 14:49 CDB_UNDOTBS1_br42bdf6_379_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1114112 Aug 29 14:49 CDB_UNDOTBS2_bs42bdfb_380_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1245184 Aug 29 14:49 CDB_USERS_bt42bdfg_381_1_1.bkp
-rw-r-----. 1 oracle asmadmin 72982528 Aug 29 14:49 ORA19RP1_SYSAUX_bu42bdfi_382_1_1.bkp
-rw-r-----. 1 oracle asmadmin 266625024 Aug 29 14:50 ORA19RP1_SYSTEM_bv42bdg3_383_1_1.bkp
-rw-r-----. 1 oracle asmadmin   7872512 Aug 29 14:50 ORA19RP1_TUNER_DATA1_c042bdha_384_1_1.bkp
-rw-r-----. 1 oracle asmadmin   2498560 Aug 29 14:50 ORA19RP1_TUNER_IDX1_c142bdhb_385_1_1.bkp
-rw-r-----. 1 oracle asmadmin   5865472 Aug 29 14:50 ORA19RP1_UNDO_2_c342bdhi_387_1_1.bkp
-rw-r-----. 1 oracle asmadmin   5406720 Aug 29 14:50 ORA19RP1_UNDOTBS1_c242bdhf_386_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1073152 Aug 29 14:50 ORA19RP1_USERS_c442bdhn_388_1_1.bkp
-rw-r-----. 1 oracle asmadmin 71483392 Aug 29 14:51 PDB1_SYSAUX_c842bdji_392_1_1.bkp
-rw-r-----. 1 oracle asmadmin 266674176 Aug 29 14:52 PDB1_SYSTEM_c942bdk3_393_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1458176 Aug 29 14:52 PDB1_TUNER_DATA1_ca42bdl7_394_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1351680 Aug 29 14:52 PDB1_TUNER_IDX1_cb42bdlh_395_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1097728 Aug 29 14:52 PDB1_UNDOTBS1_cc42bdlk_396_1_1.bkp
-rw-r-----. 1 oracle asmadmin 64561152 Aug 29 14:50 PDBSEED_SYSAUX_c542bdhp_389_1_1.bkp
-rw-r-----. 1 oracle asmadmin 265166848 Aug 29 14:51 PDBSEED_SYSTEM_c642bdi4_390_1_1.bkp
-rw-r-----. 1 oracle asmadmin 85991424 Aug 29 14:51 PDBSEED_UNDOTBS1_c742bdj2_391_1_1.bkp
-rw-r-----. 1 oracle asmadmin 20299776 Aug 29 15:08 snapcf_CA.f

/home/oracle/ORA19R_BACKUP/autobackup:
total 79752
drwxr-xr-x. 2 oracle oinstall 4096 Aug 29 15:08 .
drwxr-xr-x. 3 oracle oinstall 4096 Aug 29 15:08 ..
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 14:52 c-1831232271-20250829-02
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 14:54 c-1831232271-20250829-03
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 15:08 c-1831232271-20250829-04
-rw-r-----. 1 oracle asmadmin 20414464 Aug 29 15:08 c-1831232271-20250829-05
--> 가장 최근에 백업 받은 컨트롤 파일은 c-1831232271-20250829-05 임!!!!

7. 소스에서 pfile 생성

--소스에서 /home/oracle/ORA19R_BACKUP/ 위치에 pfile을 생성
--pfile을 생성하기 위해서 pfile로 접속
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 22 18:05:13 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R1]$ create pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora' from spfile;

File created.

Elapsed: 00:00:00.15

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -l /home/oracle/ORA19R_BACKUP/initORA19R.ora
-rw-r--r--. 1 oracle asmadmin 1895 Aug 29 15:16 /home/oracle/ORA19R_BACKUP/initORA19R.ora

8. orapw 파일 copy

--소스에서 orapw 파일의 사본을 /home/oracle/ORA19R_BACKUP/ 위치에 저장함
--grid os user

[+ASM1:grid@ol7ora19r1][/home/grid]$ srvctl config database -db ora19r
Database unique name: ORA19R
Database name: ORA19R
Oracle home: /u01/app/oracle/product/19c/db_1
Oracle user: oracle
Spfile: +DATA1/ORA19R/PARAMETERFILE/spfile.267.1209231527
Password file: +DATA1/ORA19R/PASSWORD/pwdora19r.263.1209229673
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA1,DATA1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORA19R1,ORA19R2
Configured nodes: ol7ora19r1,ol7ora19r2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd
ASMCMD [+] > cd +DATA1/ORA19R/PASSWORD/

ASMCMD [+DATA1/ORA19R/PASSWORD] > ls -sl
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
PASSWORD UNPROT COARSE AUG 15 17:00:00 Y 512 4 2048 0 pwdora19r.263.1209229673

ASMCMD [+DATA1/ORA19R/PASSWORD] > pwcopy pwdora19r.263.1209229673 /tmp/orapwORA19R
copying +DATA1/ORA19R/PASSWORD/pwdora19r.263.1209229673 -> /tmp/orapwORA19R

[+ASM1:grid@ol7ora19r1][/home/grid]$ ls -l /tmp/orapwORA19R
-rw-rw----. 1 grid oinstall 2048 Aug 29 15:17 /tmp/orapwORA19R

[+ASM1:grid@ol7ora19r1][/home/grid]$ chmod 660 /tmp/orapwORA19R

[+ASM1:grid@ol7ora19r1][/home/grid]$ ls -l /tmp/orapwORA19R
-rw-rw----. 1 grid oinstall 2048 Aug 29 15:17 /tmp/orapwORA19R

--oracle os user
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cp /tmp/orapwORA19R /home/oracle/ORA19R_BACKUP/

9. 타켓에서 백업본을 저장할 디렉토리 생성 및 백업본을 가져옴 (scp)

--타켓 서버에 접속해서 복구 작업에 사용할 디렉토리를 생성하고 소스 서버의 백업본 및 PFILE을 SCP로 가져오는 것임!
[ORA19R:oracle@tdb02t][/home/oracle]$ mkdir -pv /home/oracle/ORA19R_BACKUP/autobackup
mkdir: created directory ‘/home/oracle/ORA19R_BACKUP’
mkdir: created directory ‘/home/oracle/ORA19R_BACKUP/autobackup’

[ORA19R:oracle@tdb02t][/home/oracle]$ cd /home/oracle/ORA19R_BACKUP

--drop table purge 한 테이블이 속해있는 ORA19RP1 PDB내에 속해있는 TUNER_DATA1, TUNER_IDX1 테이블 스페이스만을
--복구하기 위해서 필요한 파일만을 수신 받는 것임

--CDB, pdbseed, 아카이브, 컨트롤파일(오토백업), pfile, orapw file을 수신받는 것임!

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/CDB* /home/oracle/ORA19R_BACKUP/

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ARCHIVE* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/autobackup/c* /home/oracle/ORA19R_BACKUP/autobackup
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/initORA19R.ora /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/orapwORA19R /home/oracle/ORA19R_BACKUP/

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/PDBSEED* /home/oracle/ORA19R_BACKUP/

--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/PDB1_SYSAUX* /home/oracle/ORA19R_BACKUP/
--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/PDB1_SYSTEM* /home/oracle/ORA19R_BACKUP/
--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/PDB1_UNDOTBS1* /home/oracle/ORA19R_BACKUP/

--> PDB1은 불필요 전송하지 않음

--ORA19RP1 PDB내에서 USERS 테이블 스페이스의 데이터파일을 수신안하는 것을 주목해야함!
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1* /home/oracle/ORA19R_BACKUP/
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1* /home/oracle/ORA19R_BACKUP/

--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ scp -p 192.168.0.21:/home/oracle/ORA19R_BACKUP/ORA19RP1_USERS* /home/oracle/ORA19R_BACKUP/

--> ORA19RP1의 USERS 테이블스페이스는 불필요 전송하지 않음

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ ls -laR /home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total 1002656
drwxr-xr-x. 3 oracle oinstall      4096 Aug 29 15:32 .
drwx------. 9 oracle oinstall      4096 Aug 29 15:19 ..
-rw-r-----. 1 oracle oinstall    136704 Aug 29 14:53 ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1
-rw-r-----. 1 oracle oinstall    280576 Aug 29 15:08 ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp
-rw-r-----. 1 oracle oinstall     31232 Aug 29 15:08 ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp
drwxr-xr-x. 2 oracle oinstall      4096 Aug 29 15:30 autobackup
-rw-r-----. 1 oracle oinstall 201998336 Aug 29 14:48 CDB_SYSAUX_bp42bdcs_377_1_1.bkp
-rw-r-----. 1 oracle oinstall 459513856 Aug 29 14:49 CDB_SYSTEM_bq42bddm_378_1_1.bkp
-rw-r-----. 1 oracle oinstall   1122304 Aug 29 14:49 CDB_UNDOTBS1_br42bdf6_379_1_1.bkp
-rw-r-----. 1 oracle oinstall   1114112 Aug 29 14:49 CDB_UNDOTBS2_bs42bdfb_380_1_1.bkp
-rw-r-----. 1 oracle oinstall   1245184 Aug 29 14:49 CDB_USERS_bt42bdfg_381_1_1.bkp
-rw-r--r--. 1 oracle oinstall      1895 Aug 29 15:16 initORA19R.ora
-rw-r-----. 1 oracle oinstall 72982528 Aug 29 14:49 ORA19RP1_SYSAUX_bu42bdfi_382_1_1.bkp
-rw-r-----. 1 oracle oinstall 266625024 Aug 29 14:50 ORA19RP1_SYSTEM_bv42bdg3_383_1_1.bkp
-rw-r-----. 1 oracle oinstall   7872512 Aug 29 14:50 ORA19RP1_TUNER_DATA1_c042bdha_384_1_1.bkp
-rw-r-----. 1 oracle oinstall   2498560 Aug 29 14:50 ORA19RP1_TUNER_IDX1_c142bdhb_385_1_1.bkp
-rw-r-----. 1 oracle oinstall   5865472 Aug 29 14:50 ORA19RP1_UNDO_2_c342bdhi_387_1_1.bkp
-rw-r-----. 1 oracle oinstall   5406720 Aug 29 14:50 ORA19RP1_UNDOTBS1_c242bdhf_386_1_1.bkp
-rw-r-----. 1 oracle oinstall      2048 Aug 29 15:18 orapwORA19R

/home/oracle/ORA19R_BACKUP/autobackup:
total 79752
drwxr-xr-x. 2 oracle oinstall 4096 Aug 29 15:30 .
drwxr-xr-x. 3 oracle oinstall 4096 Aug 29 15:32 ..
-rw-r-----. 1 oracle oinstall 20414464 Aug 29 14:52 c-1831232271-20250829-02
-rw-r-----. 1 oracle oinstall 20414464 Aug 29 14:54 c-1831232271-20250829-03
-rw-r-----. 1 oracle oinstall 20414464 Aug 29 15:08 c-1831232271-20250829-04
-rw-r-----. 1 oracle oinstall 20414464 Aug 29 15:08 c-1831232271-20250829-05

--소스에서 tnsnames.ora도 가져오기
[ORA19R:oracle@tdb02t][/home/oracle]$ scp -p 192.168.0.21:/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora

[ORA19R:oracle@tdb02t][/home/oracle]$ cat /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

T_ORA19R =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19R)
    )
)

T_ORA19R_ORA19RP1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19RP1)
    )
)

[ORA19R:oracle@tdb02t][/home/oracle]$ tnsping ORA19R

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-AUG-2025 15:37:50

Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.25)(PORT = 1521)) (LOAD_BALANCE = OFF) (FAILOVER = ON)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19R) (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))))
OK (60 msec)

10. 타켓에서 pfile 수정

--타켓에서 사용할 리스너 정보 조회
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ csrt
--------------------------------------------------------------------------------
Name           Target State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
               ONLINE ONLINE       tdb02t                   STABLE
ora.FRA1.dg
               ONLINE ONLINE       tdb02t                   STABLE
ora.LISTENER.lsnr
               ONLINE ONLINE       tdb02t                   STABLE
ora.asm
               ONLINE ONLINE       tdb02t                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      tdb02t                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE ONLINE       tdb02t                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE ONLINE       tdb02t                   STABLE
--------------------------------------------------------------------------------

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-AUG-2025 23:36:53

Connectingto(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                19-AUG-2025 22:55:25
Uptime                    0 days 0 hr. 41 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/tdb02t/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.61)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA1" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA1" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
-->(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.61)(PORT=1521)))
--> 타켓 db의 local_listener 파라미터의 값으로 할 것임

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ cat /home/oracle/ORA19R_BACKUP/initORA19R.ora
ORA19R2.__data_transfer_cache_size=0
ORA19R1.__data_transfer_cache_size=0
ORA19R2.__db_cache_size=3959422976
ORA19R1.__db_cache_size=3657433088
ORA19R2.__inmemory_ext_roarea=0
ORA19R1.__inmemory_ext_roarea=0
ORA19R2.__inmemory_ext_rwarea=0
ORA19R1.__inmemory_ext_rwarea=0
ORA19R2.__java_pool_size=0
ORA19R1.__java_pool_size=0
ORA19R2.__large_pool_size=16777216
ORA19R1.__large_pool_size=16777216
ORA19R1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA19R2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA19R2.__pga_aggregate_target=1694498816
ORA19R1.__pga_aggregate_target=1694498816
ORA19R2.__sga_target=5033164800
ORA19R1.__sga_target=5033164800
ORA19R2.__shared_io_pool_size=50331648
ORA19R1.__shared_io_pool_size=134217728
ORA19R2.__shared_pool_size=989855744
ORA19R1.__shared_pool_size=1174405120
ORA19R2.__streams_pool_size=0
ORA19R1.__streams_pool_size=33554432
ORA19R2.__unified_pga_pool_size=0
ORA19R1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+FRA1/ORA19R/CONTROLFILE/current.282.1209229839'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_create_online_log_dest_1='+FRA1'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
ORA19R2.instance_number=2
ORA19R1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='LOCATION=+FRA1'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1601m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=4800m
ORA19R2.thread=2
ORA19R1.thread=1
ORA19R1.undo_tablespace='UNDOTBS1'
ORA19R2.undo_tablespace='UNDOTBS2'
*.use_large_pages='ONLY'

-- 파라미터 파일 백업
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ cp /home/oracle/ORA19R_BACKUP/initORA19R.ora /home/oracle/ORA19R_BACKUP/initORA19R.ora.bak

--> 위의 내용을 아래와 같이 변경한후 저장함
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ vi /home/oracle/ORA19R_BACKUP/initORA19R.ora
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ cat /home/oracle/ORA19R_BACKUP/initORA19R.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl','/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/ORA19R_BACKUP'
*.db_create_online_log_dest_1='/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1'
*.db_create_online_log_dest_2='/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.61)(PORT=1521)))'
*.log_archive_dest_1='LOCATION=/home/oracle/ORA19R_BACKUP/ORA19R/arch'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'

--수정한 pfile을 기반으로 타켓서버에서 필요한 디렉토리를 미리 생성해둠
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /u01/app/oracle/admin/ORA19R/adump
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/arch
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb
--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed
--[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1

11. 타켓에서 nomount 모드로 인스턴스 시작

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ export ORACLE_SID=ORA19R
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ echo $ORACLE_SID
ORA19R

[ORA19R:oracle@tdb02t][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 19 23:47:31 2025
Version 19.27.0.0.0

Connected to an idle instance.

[][ORA19R@SYS]$ startup nomount pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora'

ORACLE instance started.

Total System Global Area 1073740632 bytes
Fixed Size                  9186136 bytes
Variable Size             276824064 bytes
Database Buffers          780140544 bytes
Redo Buffers                7589888 bytes

--타켓 서버에서 또다른 ssh창을 하나 더 열어서 oracle user로 접속한 다음 alert log를 모니터링! (항상 alert log를 모니터링 하자!)
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ tail -f /u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace/alert_ORA19R.log
2025-08-30T05:59:01.767948+09:00
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
2025-08-30T05:59:01.862725+09:00
Starting background process TMON
2025-08-30T05:59:01.893748+09:00
TMON started with pid=36, OS id=3439
ORACLE_BASE from environment = /u01/app/oracle
2025-08-30T05:59:04.098323+09:00
Using default pga_aggregate_limit of 2048 MB
--> alert log 모니터링 시작

12. skip할 테이블 스페이스를 출력하는 sql문 실행

--현재 소스 서버의 ORA19RP1 PDB 에는 사용자 테이블 스페이스로 TUNER_DATA1, TUNER_IDX1, USERS가 존재하는 상황임
--이상 상황에서 USERS는 복구할 필요가 없으므로 제외 시킬것임
--이러한 작업을 원활하게 하기 위해서 아래의 스크립트를 실행함
--실전에서는 복구대상 테이블 스페이스는 소수가 될것이고, skip할 테이블 스페이스는 매우 많을 것이므로 IN조건으로 복구대상 테이블 스페이스를 지정해줌

--skip 할 테이블 스페이스를 출력
WITH C AS
(
SELECT B.NAME AS CON_NAME
   , A.TABLESPACE_NAME AS TS_NAME
   , A.CONTENTS AS CONTENTS
   FROM CDB_TABLESPACES A JOIN V$CONTAINERS B ON B.CON_ID = A.CON_ID
WHERE B.NAME NOT IN ('CDB$ROOT','PDB$SEED', 'PDB1') --CDB 및 PDBSEED는 무조건 복구하기 때문에 스킵할 것이 없음
    AND A.CONTENTS IN ('PERMANENT','UNDO')
)
SELECT
'SKIP TABLESPACE '
|| LISTAGG('' || C.CON_NAME || ':' || C.TS_NAME || '', ',')
     WITHIN GROUP (ORDER BY C.CON_NAME, C.TS_NAME) AS RMAN_SKIP_CLAUSE
FROM C
WHERE 1=1
AND NOT (
            (     C.CON_NAME = 'ORA19RP1' --복구 대상 PDB
              AND C.TS_NAME IN ('SYSTEM','SYSAUX','UNDOTBS1','UNDO_2','TUNER_DATA1','TUNER_IDX1') --복구대상 TABLESPACE 기재
            )
    )
;

RMAN_SKIP_CLAUSE
------------------------------
SKIP TABLESPACE ORA19RP1:USERS
--> rman duplicate시 사용할 것임

13. 타켓에서 duplicate

[ORA19R:oracle@tdb02t][/home/oracle]$ export ORACLE_SID=ORA19R
[ORA19R:oracle@tdb02t][/home/oracle]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R:oracle@tdb02t][/home/oracle]$ rman target sys/oracle@ORA19R auxiliary /
--> target에는 소스db로 붙고, auxiliary는 타켓db(현재 nomount로 띄운)로 붙는 것임
--> 이렇게 접속해야 until scn으로 복구가 가능함
--> until time으로 복구한다면 (SET UNTIL TIME "TO_DATE('2025-08-28 22:19:43', 'YYYY-MM-DD HH24:MI:SS')";)
--> rman auxiliary /
--> 이렇게만 접속해도 됨

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 30 06:28:11 2025
Version 19.27.0.0.0

connected to target database: ORA19R (DBID=1831232271)
connected to auxiliary database: ORA19R (not mounted)

RMAN>
RUN {
ALLOCATE AUXILIARY CHANNEL dupe1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dupe2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dupe3 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dupe4 TYPE DISK;

SET UNTIL SCN 5090500;

DUPLICATE DATABASE TO ORA19R
PLUGGABLE DATABASE ora19rp1, ROOT
BACKUP LOCATION '/home/oracle/ORA19R_BACKUP'
SKIP TABLESPACE ORA19RP1:USERS
NOFILENAMECHECK;
}

--> 원격지 서버로 복구하는 것이기 때문에 NOFILENAMECHECK 옵션 사용

--> NOFILENAMECHECK 는 소스와 동일한 파일명/경로를 그대로 쓸 수도 있는 상황에서,

--> RMAN이 “소스 파일을 덮어쓰지 않겠지?” 같은 파일 중복 체크를 생략하게됨.

using target database control file instead of recovery catalog
allocated channel: dupe1
channel dupe1: SID=343 device type=DISK

allocated channel: dupe2
channel dupe2: SID=427 device type=DISK

allocated channel: dupe3
channel dupe3: SID=7 device type=DISK

allocated channel: dupe4
channel dupe4: SID=93 device type=DISK

executing command: SET until clause

Starting Duplicate Db at 2025-08-30 06:52:23

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1073740632 bytes

Fixed Size                     9186136 bytes
Variable Size                289406976 bytes
Database Buffers             767557632 bytes
Redo Buffers                   7589888 bytes
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   set until scn 5090500;
   sql clone "alter system set db_name =
''ORA19R'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set db_unique_name =
''ORA19R'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ''ORA19R'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ORA19R'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1073740632 bytes

Fixed Size                     9186136 bytes
Variable Size                289406976 bytes
Database Buffers             767557632 bytes
Redo Buffers                   7589888 bytes
allocated channel: dupe1
channel dupe1: SID=176 device type=DISK
allocated channel: dupe2
channel dupe2: SID=258 device type=DISK
allocated channel: dupe3
channel dupe3: SID=343 device type=DISK
allocated channel: dupe4
channel dupe4: SID=427 device type=DISK

Starting restore at 2025-08-30 06:53:18

channel dupe1: starting datafile backup set restore
channel dupe1: restoring control file
channel dupe1: reading from backup piece /home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250829-03
channel dupe1: piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250829-03 tag=TAG20250829T145400
channel dupe1: restored backup piece 1
channel dupe1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl
output file name=/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl
Finished restore at 2025-08-30 06:53:19

database mounted
Skipping pluggable database PDB1
Checking that duplicated tablespaces are self-contained
Automatically adding tablespace PDB$SEED:SYSTEM
Automatically adding tablespace PDB$SEED:SYSAUX
Skipping tablespace ORA19RP1:USERS

contents of Memory Script:
{
   set until scn 5090500;
   set newname for clone datafile 1 to new;
   set newname for clone datafile 3 to new;
   set newname for clone datafile 4 to new;
   set newname for clone datafile 5 to new;
   set newname for clone datafile 6 to new;
   set newname for clone datafile 7 to new;
   set newname for clone datafile 8 to new;
   set newname for clone datafile 9 to new;
   set newname for clone datafile 10 to new;
   set newname for clone datafile 11 to new;
   set newname for clone datafile 12 to new;
   set newname for clone datafile 13 to new;
   set newname for clone datafile 38 to new;
   set newname for clone datafile 39 to new;
   restore
   clone database
   skip forever tablespace "ORA19RP1":"USERS",
"PDB1":"UNDOTBS1",
"PDB1":"TUNER_IDX1",
"PDB1":"TUNER_DATA1",
"PDB1":"SYSTEM",
"PDB1":"SYSAUX"   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2025-08-30 06:53:53

channel dupe1: starting datafile backup set restore
channel dupe1: specifying datafile(s) to restore from backup set
channel dupe1: restoring datafile 00003 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_%u_.dbf
channel dupe1: reading from backup piece /home/oracle/ORA19R_BACKUP/CDB_SYSAUX_bp42bdcs_377_1_1.bkp
channel dupe2: starting datafile backup set restore
channel dupe2: specifying datafile(s) to restore from backup set
channel dupe2: restoring datafile 00001 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_system_%u_.dbf
channel dupe2: reading from backup piece /home/oracle/ORA19R_BACKUP/CDB_SYSTEM_bq42bddm_378_1_1.bkp
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00004 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_br42bdf6_379_1_1.bkp
channel dupe4: starting datafile backup set restore
channel dupe4: specifying datafile(s) to restore from backup set
channel dupe4: restoring datafile 00009 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_%u_.dbf
channel dupe4: reading from backup piece /home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_bs42bdfb_380_1_1.bkp
channel dupe4: piece handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_bs42bdfb_380_1_1.bkp tag=TAG20250829T144915
channel dupe4: restored backup piece 1
channel dupe4: restore complete, elapsed time: 00:00:01
channel dupe4: starting datafile backup set restore
channel dupe4: specifying datafile(s) to restore from backup set
channel dupe4: restoring datafile 00007 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_%u_.dbf
channel dupe4: reading from backup piece /home/oracle/ORA19R_BACKUP/CDB_USERS_bt42bdfg_381_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_br42bdf6_379_1_1.bkp tag=TAG20250829T144910
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:02
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00011 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_bu42bdfi_382_1_1.bkp
channel dupe4: piece handle=/home/oracle/ORA19R_BACKUP/CDB_USERS_bt42bdfg_381_1_1.bkp tag=TAG20250829T144920
channel dupe4: restored backup piece 1
channel dupe4: restore complete, elapsed time: 00:00:01
channel dupe4: starting datafile backup set restore
channel dupe4: specifying datafile(s) to restore from backup set
channel dupe4: restoring datafile 00010 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_system_%u_.dbf
channel dupe4: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_bv42bdg3_383_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_bu42bdfi_382_1_1.bkp tag=TAG20250829T144922
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:08
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00038 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_c042bdha_384_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_c042bdha_384_1_1.bkp tag=TAG20250829T145017
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:01
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00039 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_c142bdhb_385_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_c142bdhb_385_1_1.bkp tag=TAG20250829T145019
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:02
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00012 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_c242bdhf_386_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_c242bdhf_386_1_1.bkp tag=TAG20250829T145023
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:02
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00013 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_c342bdhi_387_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_c342bdhi_387_1_1.bkp tag=TAG20250829T145026
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:01
channel dupe3: starting datafile backup set restore
channel dupe3: specifying datafile(s) to restore from backup set
channel dupe3: restoring datafile 00006 to /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_%u_.dbf
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_c542bdhp_389_1_1.bkp
channel dupe4: piece handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_bv42bdg3_383_1_1.bkp tag=TAG20250829T144939
channel dupe4: restored backup piece 1
channel dupe4: restore complete, elapsed time: 00:00:18
channel dupe4: starting datafile backup set restore
channel dupe4: specifying datafile(s) to restore from backup set
channel dupe4: restoring datafile 00005 to /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_%u_.dbf
channel dupe4: reading from backup piece /home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_c642bdi4_390_1_1.bkp
channel dupe1: piece handle=/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_bp42bdcs_377_1_1.bkp tag=TAG20250829T144756
channel dupe1: restored backup piece 1
channel dupe1: restore complete, elapsed time: 00:00:24
channel dupe1: starting datafile backup set restore
channel dupe1: specifying datafile(s) to restore from backup set
channel dupe1: restoring datafile 00008 to /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_%u_.dbf
channel dupe1: reading from backup piece /home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_c742bdj2_391_1_1.bkp
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_c542bdhp_389_1_1.bkp tag=TAG20250829T145033
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:12
channel dupe1: piece handle=/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_c742bdj2_391_1_1.bkp tag=TAG20250829T145113
channel dupe1: restored backup piece 1
channel dupe1: restore complete, elapsed time: 00:00:09
channel dupe2: piece handle=/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_bq42bddm_378_1_1.bkp tag=TAG20250829T144822
channel dupe2: restored backup piece 1
channel dupe2: restore complete, elapsed time: 00:00:41
channel dupe4: piece handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_c642bdi4_390_1_1.bkp tag=TAG20250829T145044
channel dupe4: restored backup piece 1
channel dupe4: restore complete, elapsed time: 00:00:21
Finished restore at 2025-08-30 06:54:39

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=1210488879 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_system_nc48co20_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=19 STAMP=1210488879 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=1210488879 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=1210488879 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_nc48d9yk_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=22 STAMP=1210488879 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=23 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=24 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=25 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=26 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_system_nc48cqy1_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=27 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=28 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=29 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf
datafile 38 switched to datafile copy
input datafile copy RECID=30 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf
datafile 39 switched to datafile copy
input datafile copy RECID=31 STAMP=1210488880 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf

contents of Memory Script:
{
   set until scn 5090500;
   recover
   clone database
   skip forever tablespace "ORA19RP1":"USERS",
"PDB1":"UNDOTBS1",
"PDB1":"TUNER_IDX1",
"PDB1":"TUNER_DATA1",
"PDB1":"SYSTEM",
"PDB1":"SYSAUX"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2025-08-30 06:54:41

Executing: alter database datafile 14 offline drop
Executing: alter database datafile 25, 26, 27, 30, 31 offline drop
starting media recovery

channel dupe1: starting archived log restore to default destination
channel dupe1: restoring archived log
archived log thread=1 sequence=118
channel dupe1: restoring archived log
archived log thread=2 sequence=86
channel dupe1: reading from backup piece /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1
channel dupe2: starting archived log restore to default destination
channel dupe2: restoring archived log
archived log thread=2 sequence=87
channel dupe2: reading from backup piece /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp
channel dupe3: starting archived log restore to default destination
channel dupe3: restoring archived log
archived log thread=1 sequence=119
channel dupe3: reading from backup piece /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp
channel dupe1: piece handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250829_ce42bdo6_s398_p1 tag=TAG20250829T145357
channel dupe1: restored backup piece 1
channel dupe1: restore complete, elapsed time: 00:00:00
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_118_1209229842.arc thread=1 sequence=118
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_86_1209229842.arc thread=2 sequence=86
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_86_1209229842.arc RECID=230 STAMP=1210488893
channel dupe2: piece handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s87_set402_p1_20250829.bkp tag=TAG20250829T150854
channel dupe2: restored backup piece 1
channel dupe2: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_87_1209229842.arc thread=2 sequence=87
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_118_1209229842.arc RECID=229 STAMP=1210488893
channel dupe3: piece handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s119_set400_p1_20250829.bkp tag=TAG20250829T150814
channel dupe3: restored backup piece 1
channel dupe3: restore complete, elapsed time: 00:00:02
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_119_1209229842.arc thread=1 sequence=119
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_119_1209229842.arc RECID=232 STAMP=1210488893
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_87_1209229842.arc RECID=231 STAMP=1210488893
media recovery complete, elapsed time: 00:00:00
Finished recover at 2025-08-30 06:54:55
released channel: dupe1
released channel: dupe2
released channel: dupe3
released channel: dupe4
Oracle instance started

Total System Global Area 1073740632 bytes

Fixed Size                     9186136 bytes
Variable Size                289406976 bytes
Database Buffers             767557632 bytes
Redo Buffers                   7589888 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''ORA19R'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set db_name = ''ORA19R'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1073740632 bytes

Fixed Size                     9186136 bytes
Variable Size                289406976 bytes
Database Buffers             767557632 bytes
Redo Buffers                   7589888 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA19R" RESETLOGS ARCHIVELOG
MAXLOGFILES    192
MAXLOGMEMBERS      3
MAXDATAFILES     1024
MAXINSTANCES    32
MAXLOGHISTORY      292
LOGFILE
GROUP     1 SIZE 200 M ,
GROUP     2 SIZE 200 M ,
GROUP     3 SIZE 200 M
DATAFILE
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_system_nc48co20_.dbf',
'/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_nc48d9yk_.dbf',
'/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_system_nc48cqy1_.dbf'
CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE 'i2'
GROUP     4 SIZE 200 M ,
GROUP     5 SIZE 200 M ,
GROUP     6 SIZE 200 M

contents of Memory Script:
{
   set newname for clone tempfile 1 to new;
   set newname for clone tempfile 2 to new;
   set newname for clone tempfile 3 to new;
   set newname for clone tempfile 4 to new;
   switch clone tempfile all;
   catalog clone datafilecopy "/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf",
"/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_te_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf RECID=1 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf RECID=2 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf RECID=3 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf RECID=4 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf RECID=5 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf RECID=6 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf RECID=7 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf RECID=8 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf RECID=9 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf RECID=10 STAMP=1210488933
cataloged datafile copy
datafile copy file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf RECID=11 STAMP=1210488933

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=6 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=7 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=8 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=9 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf
datafile 38 switched to datafile copy
input datafile copy RECID=10 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf
datafile 39 switched to datafile copy
input datafile copy RECID=11 STAMP=1210488933 file name=/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf

contents of Memory Script:
{
   sql clone 'alter system set "_system_trig_enabled"=FALSE';
   Alter clone database open resetlogs;
   sql clone 'alter system reset "_system_trig_enabled"';
}
executing Memory Script

sql statement: alter system set "_system_trig_enabled"=FALSE

database opened

sql statement: alter system reset "_system_trig_enabled"
Executing: drop pluggable database "PDB1" including datafiles

contents of Memory Script:
{
sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Dropping offline and skipped tablespaces
Executing: alter database default tablespace system
Executing: drop tablespace "USERS" including contents cascade constraints
Cannot remove created server parameter file
Finished Duplicate Db at 2025-08-30 06:55:54

14. duplicate 완료 후 타켓 DB 상황 체크

[ORA19R:oracle@tdb02t][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'

[ORA19R:oracle@tdb02t][/home/oracle]$ echo $ORACLE_SID
ORA19R
[ORA19R:oracle@tdb02t][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 30 07:06:34 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][ORA19R@SYS]$ SHOW PARAMETER db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORA19R

[tdb02t][ORA19R@SYS]$ SHOW PARAMETER db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORA19R

COL name FOR a20
SELECT name, open_mode, database_role FROM v$database;

NAME OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
ORA19R READ WRITE PRIMARY

1 row selected.

Elapsed: 00:00:00.01

[tdb02t][ORA19R@SYS]$
COL name FOR a20
SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;
    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 ORA19RP1             READ WRITE

2 rows selected.

Elapsed: 00:00:00.01

[tdb02t][ORA19R@SYS]$ show con_name;

CON_NAME
------------------------------
CDB$ROOT

[tdb02t][ORA19R@SYS]$ show pdbs;

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 ORA19RP1                       READ WRITE NO

[tdb02t][ORA19R@SYS]$

SET LINESIZE 300
COL NAME                FOR A10
COL OPEN_MODE           FOR A15
COL DATABASE_ROLE       FOR A20
COL RESETLOGS_CHANGE#   FOR 999999999999999
COL CHECKPOINT_CHANGE# FOR 999999999999999
COL CURRENT_SCN         FOR 999999999999999

SELECT
     NAME
   , OPEN_MODE
   , DATABASE_ROLE
   , RESETLOGS_CHANGE#
   , CHECKPOINT_CHANGE#
   , CURRENT_SCN
FROM
     V$DATABASE;

NAME OPEN_MODE DATABASE_ROLE RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN
---------- --------------- -------------------- ----------------- ------------------ ----------------
ORA19R READ WRITE PRIMARY 5090501 5090504 5115857
--> 우리의 불완전 복구 목표 scn은 5090500 이었고, RESETLOGS_CHANGE# 그 보다 큰 1이 증가한 5090501 임을 알 수있음
--> CHECKPOINT_CHANGE# 은 5090504 이므로 해당 지점까지 datafile에 적용된 상태임
--> 데이터베이스의 현재 SCN은 5115857 임

COL name FOR A150
SELECT file#, checkpoint_change#, name
FROM v$datafile
ORDER BY file#;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
         1            5090504 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_system_nc48co20_.dbf
         3            5090504 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf
         4            5090504 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf
         5            2387633 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_nc48d9yk_.dbf
         6            2387633 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf
         7            5090504 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf
         8            2387633 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf
         9            5090504 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf
        10            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_system_nc48cqy1_.dbf
        11            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf
        12            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf
        13            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf
        38            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf
        39            5092567 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf

14 rows selected.

Elapsed: 00:00:00.00

[tdb02t][ORA19R@SYS]$
SET LINESIZE 300
COL FILE#               FOR 9999
COL NAME                FOR A110
COL CHECKPOINT_CHANGE# FOR 999999999999999
COL CHECKPOINT_TIME     FOR A20
COL STATUS              FOR A10

SELECT
     FILE#
   , NAME
   , CHECKPOINT_CHANGE#
   , TO_CHAR(CHECKPOINT_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CHECKPOINT_TIME
   , STATUS
FROM
     V$DATAFILE_HEADER
ORDER BY
     FILE#;

FILE# NAME                                                                                                           CHECKPOINT_CHANGE# CHECKPOINT_TIME      STATUS
----- -------------------------------------------------------------------------------------------------------------- ------------------ -------------------- ----------
    1 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_system_nc48co20_.dbf                                                     5090504 2025-08-30 06:55:42 ONLINE
    3 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_sysaux_nc48co15_.dbf                                                     5090504 2025-08-30 06:55:42 ONLINE
    4 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs1_nc48co2o_.dbf                                                   5090504 2025-08-30 06:55:42 ONLINE
    5 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_nc48d9yk_.dbf                    2387633 2025-08-15 17:39:16 ONLINE
    6 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_nc48d61g_.dbf                    2387633 2025-08-15 17:39:16 ONLINE
    7 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_users_nc48cpyk_.dbf                                                      5090504 2025-08-30 06:55:42 ONLINE
    8 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_nc48dfxg_.dbf                  2387633 2025-08-15 17:39:16 ONLINE
    9 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_undotbs2_nc48co35_.dbf                                                   5090504 2025-08-30 06:55:42 ONLINE
   10 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_system_nc48cqy1_.dbf                    5092567 2025-08-30 06:55:52 ONLINE
   11 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_sysaux_nc48cq2n_.dbf                    5092567 2025-08-30 06:55:52 ONLINE
   12 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undotbs1_nc48d2yf_.dbf                  5092567 2025-08-30 06:55:52 ONLINE
   13 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_undo_2_nc48d4mq_.dbf                    5092567 2025-08-30 06:55:52 ONLINE
   38 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_da_nc48czvf_.dbf                  5092567 2025-08-30 06:55:52 ONLINE
   39 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_id_nc48d0wt_.dbf                  5092567 2025-08-30 06:55:52 ONLINE

COL name FOR A150
SELECT CREATION_CHANGE#, file#, name
FROM v$tempfile;

CREATION_CHANGE#      FILE# NAME
---------------- ---------- ----------------------------------------------------------------------------------------------------------
         1921153          1 /home/oracle/ORA19R_BACKUP/ORA19R/datafile/o1_mf_temp_nc48gz0n_.tmp
         2156151          2 /home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_temp_nc48h0b9_.tmp
         2392722          3 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_temp_nc48h1jv_.tmp
         5078551          4 /home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_te_nc48h1mf_.tmp

COL status FOR A10
SELECT incarnation#, resetlogs_change#, resetlogs_time, status FROM v$database_incarnation ORDER BY incarnation#;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS
------------ ----------------- ------------------- ----------
           1           1920977 2025-08-15:17:10:42 PARENT
           2           5090501 2025-08-30:06:55:36 CURRENT

2 rows selected.

Elapsed: 00:00:00.01

--현재까지의 체크 상황 정리
      (이전 incarnation #1)
   ────────────────────────────────┐
   SCN 1920977 (2025-08-15 RESETLOGS)
                                    │
                                    ▼
                    ... DB 운영 중 ...
                                    │
                                    │
   ────────────────────────────────┐
   목표 SCN = 5090500 (PITR UNTIL SCN)
                                    │
   RESETLOGS 시점 = 5090501 (incarnation #2 시작)
                                    │
   CHECKPOINT_CHANGE# = 5090504 (데이터파일 헤더 flush)
                                                         │
   일부 PDB 파일 SCN = 5092567 (PDB open 과정에서 advance)
                                    │
   CURRENT_SCN = 5115857 (DB 열고 몇 초 만에 증가)
                                    │
                                    ▼
       (새 incarnation #2, CURRENT)

[tdb02t][ORA19R@SYS]$ select name from v$controlfile;

NAME
---------------------------------------------------------------
/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl
/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl

2 rows selected.

Elapsed: 00:00:00.00

[tdb02t][ORA19R@SYS]$ SELECT group#, bytes/1024/1024 AS size_mb, members, status FROM v$log ORDER BY group#;

    GROUP#    SIZE_MB    MEMBERS STATUS
---------- ---------- ---------- ----------
         1        200          2 CURRENT
         2        200          2 UNUSED
         3        200          2 UNUSED
         4        200          2 UNUSED
         5        200          2 UNUSED
         6        200          2 UNUSED

[tdb02t][ORA19R@SYS]$

col member for a120
select GROUP#, TYPE, MEMBER from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- -----------------------------------------------------------------------------------
         3 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_3_nc48gr8d_.log
         3 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_3_nc48gs04_.log
         2 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_2_nc48gr7t_.log
         2 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_2_nc48gs8c_.log
         1 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_1_nc48gr6k_.log
         1 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_1_nc48gs6z_.log
         4 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_4_nc48gg10_.log
         4 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_4_nc48gg30_.log
         5 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_5_nc48ggrx_.log
         5 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_5_nc48ghm9_.log
         6 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog/o1_mf_6_nc48gjfn_.log
         6 ONLINE /home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog/o1_mf_6_nc48gk2h_.log

15. 타켓 db에서 drop purge된 테이블 확인

[tdb02t][ORA19R@SYS]$ show pdbs;

[tdb02t][ORA19R@SYS]$ alter session set container=ora19rp1;

Session altered.

Elapsed: 00:00:00.02

[tdb02t][ORA19R@SYS]$ select count(*) from tuner.tb_cust;

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

1 row selected.

Elapsed: 00:00:00.02
--> 감동의 순간임! drop table purge 했던 데이터를 되살려 낸 것임!

16. 타켓 서버에서 복구하는 과정에서 만들어진 모든 파일을 확인해보자!

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ ls -lRa /home/oracle/ORA19R_BACKUP/ORA19R
/home/oracle/ORA19R_BACKUP/ORA19R:
total 12
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 .
drwxr-xr-x. 4 oracle oinstall 4096 Aug 30 06:50 ..
drwxr-x---. 3 oracle asmadmin   22 Aug 30 06:53 3C641AD3FE4E119BE0631500A8C0DAD4
drwxr-x---. 3 oracle asmadmin   22 Aug 30 06:53 3C64582FC4C307C3E0631500A8C094D4
drwxr-xr-x. 2 oracle oinstall    6 Aug 30 06:54 arch
drwxr-x---. 2 oracle asmadmin   56 Aug 30 06:53 controlfile
drwxr-xr-x. 4 oracle oinstall 4096 Aug 30 06:55 datafile
drwxr-xr-x. 3 oracle oinstall   20 Aug 30 06:55 redolog_1
drwxr-xr-x. 3 oracle oinstall   20 Aug 30 06:55 redolog_2

/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4:
total 8
drwxr-x---. 3 oracle asmadmin 22 Aug 30 06:53 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 datafile

/home/oracle/ORA19R_BACKUP/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/datafile:
total 1250332
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 .
drwxr-x---. 3 oracle asmadmin 22 Aug 30 06:53 ..
-rw-r-----. 1 oracle asmadmin 482353152 Aug 30 06:55 o1_mf_sysaux_nc48d61g_.dbf
-rw-r-----. 1 oracle asmadmin 503324672 Aug 30 06:55 o1_mf_system_nc48d9yk_.dbf
-rw-r-----. 1 oracle asmadmin 210771968 Aug 30 06:55 o1_mf_temp_nc48h0b9_.tmp
-rw-r-----. 1 oracle asmadmin 293609472 Aug 30 06:55 o1_mf_undotbs1_nc48dfxg_.dbf

/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4:
total 8
drwxr-x---. 3 oracle asmadmin 22 Aug 30 06:53 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 datafile

/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile:
total 1845300
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 .
drwxr-x---. 3 oracle asmadmin 22 Aug 30 06:53 ..
-rw-r-----. 1 oracle asmadmin 576724992 Aug 30 07:15 o1_mf_sysaux_nc48cq2n_.dbf
-rw-r-----. 1 oracle asmadmin 513810432 Aug 30 07:30 o1_mf_system_nc48cqy1_.dbf
-rw-r-----. 1 oracle asmadmin 210771968 Aug 30 06:56 o1_mf_temp_nc48h1jv_.tmp
-rw-r-----. 1 oracle asmadmin 104865792 Aug 30 06:55 o1_mf_tuner_da_nc48czvf_.dbf
-rw-r-----. 1 oracle asmadmin 104865792 Aug 30 06:55 o1_mf_tuner_id_nc48d0wt_.dbf
-rw-r-----. 1 oracle asmadmin 67117056 Aug 30 06:55 o1_mf_tuner_te_nc48h1mf_.tmp
-rw-r-----. 1 oracle asmadmin 293609472 Aug 30 06:55 o1_mf_undo_2_nc48d4mq_.dbf
-rw-r-----. 1 oracle asmadmin 293609472 Aug 30 07:30 o1_mf_undotbs1_nc48d2yf_.dbf

/home/oracle/ORA19R_BACKUP/ORA19R/arch:
total 4
drwxr-xr-x. 2 oracle oinstall 6 Aug 30 06:54 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..

/home/oracle/ORA19R_BACKUP/ORA19R/controlfile:
total 39140
drwxr-x---. 2 oracle asmadmin 56 Aug 30 06:53 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..
-rw-r-----. 1 oracle asmadmin 20037632 Aug 30 07:33 controlfile01.ctl
-rw-r-----. 1 oracle asmadmin 20037632 Aug 30 07:33 controlfile02.ctl

/home/oracle/ORA19R_BACKUP/ORA19R/datafile:
total 3698008
drwxr-xr-x. 4 oracle oinstall       4096 Aug 30 06:55 .
drwxr-xr-x. 9 oracle oinstall       4096 Aug 30 06:53 ..
drwxr-xr-x. 2 oracle oinstall          6 Aug 29 16:18 cdb
-rw-r-----. 1 oracle asmadmin 1677729792 Aug 30 07:31 o1_mf_sysaux_nc48co15_.dbf
-rw-r-----. 1 oracle asmadmin 1216356352 Aug 30 07:31 o1_mf_system_nc48co20_.dbf
-rw-r-----. 1 oracle asmadmin 247472128 Aug 30 06:56 o1_mf_temp_nc48gz0n_.tmp
-rw-r-----. 1 oracle asmadmin 833626112 Aug 30 07:30 o1_mf_undotbs1_nc48co2o_.dbf
-rw-r-----. 1 oracle asmadmin   52436992 Aug 30 06:55 o1_mf_undotbs2_nc48co35_.dbf
-rw-r-----. 1 oracle asmadmin    5251072 Aug 30 06:55 o1_mf_users_nc48cpyk_.dbf
drwxr-xr-x. 2 oracle oinstall          6 Aug 29 16:18 ora19rp1

/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb:
total 4
drwxr-xr-x. 2 oracle oinstall 6 Aug 29 16:18 .
drwxr-xr-x. 4 oracle oinstall 4096 Aug 30 06:55 ..

/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1:
total 4
drwxr-xr-x. 2 oracle oinstall 6 Aug 29 16:18 .
drwxr-xr-x. 4 oracle oinstall 4096 Aug 30 06:55 ..

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1:
total 4
drwxr-xr-x. 3 oracle oinstall 20 Aug 30 06:55 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 ORA19R

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R:
total 4
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 .
drwxr-xr-x. 3 oracle oinstall 20 Aug 30 06:55 ..
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 onlinelog

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/ORA19R/onlinelog:
total 1228828
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 .
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 ..
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 07:33 o1_mf_1_nc48gr6k_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_2_nc48gr7t_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_3_nc48gr8d_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_4_nc48gg10_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_5_nc48ggrx_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_6_nc48gjfn_.log

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2:
total 4
drwxr-xr-x. 3 oracle oinstall 20 Aug 30 06:55 .
drwxr-xr-x. 9 oracle oinstall 4096 Aug 30 06:53 ..
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 ORA19R

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R:
total 4
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 .
drwxr-xr-x. 3 oracle oinstall 20 Aug 30 06:55 ..
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 onlinelog

/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/ORA19R/onlinelog:
total 1228828
drwxr-x---. 2 oracle asmadmin 4096 Aug 30 06:55 .
drwxr-x---. 3 oracle asmadmin 23 Aug 30 06:55 ..
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 07:33 o1_mf_1_nc48gs6z_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_2_nc48gs8c_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_3_nc48gs04_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_4_nc48gg30_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_5_nc48ghm9_.log
-rw-r-----. 1 oracle asmadmin 209715712 Aug 30 06:55 o1_mf_6_nc48gk2h_.log

17. 리스너를 잘 인식했는지 확인

소스서버에서 PFILE을 가져온 후 그 PFILE을 타켓 DB용으로 수정 시 LOCAL_LISTENER 파라미터를 수정했으므로

타켓 서버의 LISTENER_ORA19R 리스너스 타켓 서버 ORA19R DB를 인식하게됨!
만약 인식 못하면 타켓 cDB에서 (ALTER SYSTEM REGISTER; 명령 한번 쳐볼 것)

[+ASM:grid@tdb02t][/home/grid]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-AUG-2025 07:34:29

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-AUG-2025 05:56:53
Uptime                    0 days 1 hr. 37 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19c/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/tdb02t/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.61)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA1" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA1" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "3c64582fc4c307c3e0631500a8c094d4" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s) for this service...
Service "ORA19R" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s) for this service...
Service "ORA19RXDB" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s) for this service...
Service "ora19rp1" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s) for this service...
The command completed successfully

18. 타켓에서 db 접속을 편하게 하도록 설정 (orapwd, alias 등 설정)

--tnsnames.ora 내용 확인
[ORA19R:oracle@tdb02t][/home/oracle]$ alias cdt
alias cdt='cd $ORACLE_HOME/network/admin'
[ORA19R:oracle@tdb02t][/home/oracle]$ cdt
[ORA19R:oracle@tdb02t][/u01/app/oracle/product/19c/db_1/network/admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

T_ORA19R =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19R)
    )
)

T_ORA19R_ORA19RP1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19RP1)
    )
)
--T_ORA19R은 타켓 db의 CDB이고, T_ORA19R_ORA19RP1 은 타켓 DB의 PDB(ORA19RP1)임

[ORA19R:oracle@tdb02t][/home/oracle]$ vi .bash_profile
[ORA19R:oracle@tdb02t][/home/oracle]$ . ./.bash_profile

[ORA19R:oracle@tdb02t][/home/oracle]$ alias ssp1
alias ssp1='rlwrap sqlplus sys/oracle@T_ORA19R_ORA19RP1 as sysdba'

[ORA19R:oracle@tdb02t][/home/oracle]$ alias ssp1t
alias ssp1t='rlwrap sqlplus tuner/oracle@T_ORA19R_ORA19RP1'

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ cp /home/oracle/ORA19R_BACKUP/orapwORA19R /u01/app/oracle/product/19c/db_1/dbs

ORA19R:oracle@tdb02t][/home/oracle]$ cdod
[ORA19R:oracle@tdb02t][/u01/app/oracle/product/19c/db_1/dbs]$ ls -al
total 137688
drwxr-xr-x. 2 oracle oinstall     4096 Aug 30 07:05 .
drwxrwxr-x. 76 oracle oinstall     4096 Aug 19 15:40 ..
-rw-r-----. 1 oracle asmadmin 20152320 Aug 30 06:55 c-1832491357-20250830-00 --> 컨트롤 파일 오토 백업 된 것임
-rw-r-----. 1 oracle asmadmin 20152320 Aug 30 07:05 c-1832491357-20250830-01 --> 컨트롤 파일 오토 백업 된 것임
-rw-rw----. 1 oracle asmadmin     1544 Aug 30 06:55 hc_ORA19R.dat
-rw-r--r--. 1 oracle oinstall     3079 May 14 2015 init.ora
-rw-r-----. 1 oracle asmadmin       24 Aug 23 09:17 lkORA19R
-rw-r-----. 1 oracle oinstall     2048 Aug 30 07:35 orapwORA19R
-rw-r-----. 1 oracle asmadmin 20037632 Aug 30 07:05 snapcf_ORA19R.f
-rw-r-----. 1 oracle asmadmin     9728 Aug 30 06:56 spfileORA19R.ora

[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 30 07:48:32 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][T_ORA19R_ORA19RP1@SYS]$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1t

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 30 07:48:56 2025
Version 19.27.0.0.0

Last Successful login time: Sat Aug 30 2025 07:44:36 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][T_ORA19R_ORA19RP1@TUNER]$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

--클라이언트(PC)의 tnsnames.ora 추가
T_ORA19R =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19R)
    )
)

T_ORA19R_ORA19RP1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19RP1)
    )
)

19. 복구한 tuner.tb_cust 테이블 export하기

[ORA19R:oracle@tdb02t][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 23 14:23:24 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][ORA19R@SYS]$ show pdbs;

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[tdb02t][ORA19R@SYS]$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 23 14:24:01 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][T_ORA19R_ORA19RP1@SYS]$ create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';

Directory created.

Elapsed: 00:00:00.41

[tdb02t][T_ORA19R_ORA19RP1@SYS]$ grant read, write on directory tuner_datapump_dir to tuner;

Grant succeeded.

Elapsed: 00:00:00.07
[tdb02t][ORA19RP1@SYS]$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ expdp tuner/oracle@T_ORA19R_ORA19RP1 tables='TB_CUST' directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log

Export: Release 19.0.0.0.0 - Production on Sat Aug 30 07:49:50 2025
Version 19.27.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "TUNER"."SYS_EXPORT_TABLE_01": tuner/********@T_ORA19R_ORA19RP1 tables=TB_CUST directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log
*/
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TUNER"."TB_CUST" 12.36 MB 100000 rows
Master table "TUNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TUNER.SYS_EXPORT_TABLE_01 is:
/home/oracle/ORA19R_BACKUP/datapump/tuner_tb_cust.dmp
Job "TUNER"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 30 07:50:17 2025 elapsed 0 00:00:25

[ORA19R:oracle@tdb02t][/home/oracle]$ ls -l /home/oracle/ORA19R_BACKUP/datapump
total 12884
-rw-r-----. 1 oracle asmadmin 13189120 Aug 30 07:50 tuner_tb_cust.dmp
-rw-r--r--. 1 oracle asmadmin 1347 Aug 30 07:50 tuner_tb_cust.log

[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 30 07:51:01 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[tdb02t][T_ORA19R_ORA19RP1@SYS]$ drop directory tuner_datapump_dir;

Directory dropped.

Elapsed: 00:00:00.06

20. 타켓에서 export 한 tuner.tb_cust dmp파일을 소스에서 import 하기

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ scp 192.168.0.61:/home/oracle/ORA19R_BACKUP/datapump/* /home/oracle/ORA19R_BACKUP/datapump
oracle@192.168.0.61's password:
tuner_tb_cust.dmp 100% 13MB 7.2MB/s 00:01
tuner_tb_cust.log 100% 1347 14.2KB/s 00:00
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ssp1
alias ssp1='rlwrap sqlplus sys/oracle@ORA19R_ORA19RP1 as sysdba'

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ssp1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 23 14:29:13 2025
Version 19.27.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';

Directory created.

Elapsed: 00:00:00.46
[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ grant read, write on directory tuner_datapump_dir to tuner;

Grant succeeded.

Elapsed: 00:00:00.05

[ol7ora19r1][SYS@ORA19RP1]$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ impdp tuner/oracle@ORA19R_ORA19RP1 tables='TB_CUST' directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust_import.log
Import: Release 19.0.0.0.0 - Production on Sat Aug 23 14:29:59 2025
Version 19.27.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "TUNER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TUNER"."SYS_IMPORT_TABLE_01": tuner/********@ORA19R_ORA19RP1 tables=TB_CUST directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust_import.log

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TUNER"."TB_CUST" 12.36 MB 100000 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TUNER"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 30 07:55:00 2025 elapsed 0 00:02:18

21. 최종적으로 소스 서버에서 TUNER.TB_CUST 테이블이 보이는 지 확인!

[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ssp1t
alias ssp1t='rlwrap sqlplus tuner/oracle@ORA19R_ORA19RP1'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ssp1t

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 23 14:32:43 2025
Version 19.27.0.0.0

Last Successful login time: Sat Aug 23 2025 14:30:00 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

[ol7ora19r1][TUNER@ORA19R_ORA19RP1]$ select count(*) from tuner.tb_cust;

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

1 row selected.

Elapsed: 00:00:00.67

[ol7ora19r1][TUNER@ORA19R_ORA19RP1]$

--> tuner.tb_cust 테이블 복구 성공

[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ drop directory tuner_datapump_dir;

Directory dropped.

Elapsed: 00:00:00.24

소스 서버에서 TUNER.TB_CUST 테이블이 DROP TABLE PURGE 됐을 때 당황하지 말고 해당 절차대로 수행하면 충분히 DROP PURGE 된 테이블을 복구해올 수 있음!

'Oracle > 백업&복구' 카테고리의 다른 글

[2025-09-08] Oracle 19c RAC CDB환경에서 특정 PDB를 DROP 한 후 백업본으로 부터 복구하는 방법 (자동 방식) (동일 서버에서 백업본으로 복구) (0)	2025.09.08
[2025-09-07] Oracle 19c RAC CDB환경에서 특정 PDB를 DROP 한 후 백업본으로 부터 복구하는 방법 (Manual 방식) (동일 서버에서 백업본으로 복구) (0)	2025.09.07
[2025-06-20] RMAN 복제 DB 구성 Active Database 기능 실습 (12cR2에서 테스트) (1)	2025.08.28
[2025-08-22] Oracle 19c 2Node RAC CDB 환경에서 특정 PDB의 DROP TABLE PURGE 된 테이블을 원격지 Single 서버의 CDB_PDB로 불완전 복구 후 Export·Import하여 원래 RAC PDB로 복원하는 절차 (1)	2025.08.24
[2025-06-14] RAC환경에서 특정 테이블 Drop table purge 후 원격지 Single DB로 복구 (컨트롤 파일 재생성 방식) (19c) (0)	2025.08.08

db-expert

[2025-08-30] Oracle 19c 2Node RAC CDB 환경에서 특정 PDB의 DROP TABLE PURGE 된 테이블을 원격지 Single 서버의 CDB_PDB로 RMAN Duplicate로 불완전 복구 후 Export·Import하여 원래 RAC PDB로 복원하는 절차

'Oracle > 백업&복구' 카테고리의 다른 글

+ Recent posts

티스토리툴바