Oracle 19c
2Node RAC MultiTenant CDB 내 특정 PDB내에 있는 테이블이 DROP TABLE PURGE 된 경우
CDB/PDB의
백업본을 원격지 서버에서 Single CDB/PDB로 불완전 복구 후 DROP TABE PURGE 된 테이블을 Export한 후
기존
RAC CDB내 PDB에 Import 시키기 절차 정리
결국 소스 RAC의 PDB인 ORA19RP1에서 DROP PURGE된 테이블 -> 타겟 Single PDB인 ORA19RP1에서 복구 후 Export
-> 다시 소스 RAC로 Import 하는 절차를 테스트하는 것임
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
<타켓> -> 특정 테이블이 복구될 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
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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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 서버에 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))
)
)
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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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 <=
1000000;
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(*)
----------
1000000
1 row selected.
Elapsed: 00:00:05.71
--> tuner.tb_cust 테이블 생성 완료함
[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
4217628
1 row selected.
Elapsed: 00:00:00.07
--> tuner.tb_cust 테이블 생성 직후 SCN번호
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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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
{
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;
}
--> 테이블스페이스 별로 백업을 받음!
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
1840800
drwxr-xr-x. 3 oracle oinstall
4096 Aug 23 08:15 .
drwxrwxr-x. 10 oracle
oinstall 4096 Aug 23 07:57 ..
-rw-r-----.
1 oracle asmadmin 8826368 Aug 23 08:15
ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
drwxr-xr-x. 2 oracle
oinstall 70 Aug 23 08:15
autobackup
-rw-r-----. 1 oracle asmadmin 206512128 Aug 23 08:01
CDB_SYSAUX_8j41qr9b_275_1_1.bkp
-rw-r-----. 1 oracle asmadmin 459112448
Aug 23 08:03 CDB_SYSTEM_8k41qrb5_276_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1630208 Aug 23 08:03
CDB_UNDOTBS1_8l41qreq_277_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1679360 Aug 23 08:03
CDB_UNDOTBS2_8m41qrev_278_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Aug 23 08:03
CDB_USERS_8n41qrf4_279_1_1.bkp
-rw-r-----. 1 oracle asmadmin
72581120 Aug 23 08:04 ORA19RP1_SYSAUX_8o41qrfa_280_1_1.bkp
-rw-r-----.
1 oracle asmadmin 266559488 Aug 23 08:05
ORA19RP1_SYSTEM_8p41qrg5_281_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 69222400 Aug 23 08:05
ORA19RP1_TUNER_DATA1_8q41qrii_282_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 14188544 Aug 23 08:06
ORA19RP1_TUNER_IDX1_8r41qrj3_283_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 6922240 Aug 23 08:06
ORA19RP1_UNDO_2_8t41qrjg_285_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 5341184 Aug 23 08:06
ORA19RP1_UNDOTBS1_8s41qrjc_284_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1073152 Aug 23 08:06
ORA19RP1_USERS_8u41qrjl_286_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64692224 Aug 23 08:09 PDB1_SYSAUX_9241qroa_290_1_1.bkp
-rw-r-----. 1
oracle asmadmin 265445376 Aug 23 08:10
PDB1_SYSTEM_9341qrph_291_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1449984 Aug 23 08:10
PDB1_TUNER_DATA1_9441qrrk_292_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1351680 Aug 23 08:10
PDB1_TUNER_IDX1_9541qrs6_293_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1114112 Aug 23 08:10
PDB1_UNDOTBS1_9641qrs9_294_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64561152 Aug 23 08:06 PDBSEED_SYSAUX_8v41qrjo_287_1_1.bkp
-rw-r-----. 1
oracle asmadmin 265166848 Aug 23 08:07
PDBSEED_SYSTEM_9041qrkj_288_1_1.bkp
-rw-r-----. 1 oracle asmadmin
85991424 Aug 23 08:08 PDBSEED_UNDOTBS1_9141qrn2_289_1_1.bkp
-rw-r-----.
1 oracle asmadmin 20299776 Aug 23 08:15 snapcf_CA.f
/home/oracle/ORA19R_BACKUP/autobackup:
total
39876
drwxr-xr-x. 2 oracle oinstall 70
Aug 23 08:15 .
drwxr-xr-x. 3 oracle oinstall 4096 Aug
23 08:15 ..
-rw-r-----. 1 oracle asmadmin 20414464 Aug 23 08:10
c-1831232271-20250823-08
-rw-r-----.
1 oracle asmadmin 20414464 Aug 23 08:15
c-1831232271-20250823-09
-->
'c-1831232271-20250823-09' --> 이게 나중에 백업받은 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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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(*)
----------
1000000
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-23
08:16:35
1 row selected.
Elapsed: 00:00:00.04
[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ SELECT CURRENT_SCN FROM
V$DATABASE;
CURRENT_SCN
-----------
4221978
1 row
selected.
Elapsed:
00:00:01.00
--> 해당 scn번호(4221978)가 복구
목표인 SCN번호임
--> 결국 해당 SCN번호까지만 불완전 복구를 하면 TUNE.TB_CUST 테이블의 내용을 다시 볼수 있는
것임!
[ol7ora19r1][SYS@ORA19R_ORA19RP1]$ drop table tuner.tb_cust purge;
Table dropped.
Elapsed: 00:00:01.98
--> 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
-----------
4222079
--> drop table purge 를 한 이후 시점의
scn번호임
1 row selected.
Elapsed: 00:00:00.01
--> SCN : 4221978 시점에는 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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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_23/
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls
-sl +FRA1/ORA19R/ARCHIVELOG/2025_08_23/
Type Redund Striped
Time
Sys Block_Size Blocks Bytes
Space Name
ARCHIVELOG UNPROT COARSE AUG 23
08:00:00 Y
512 697 356864 4194304
thread_1_seq_101.309.1209889061
ARCHIVELOG UNPROT
COARSE AUG 23 08:00:00
Y
512 6 3072
4194304 thread_2_seq_69.286.1209889059
--> 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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.27.0.0.0
--drop table purge 한 테이블이
존재했었던 scn인 4221978 시점으로 가기 위해서 필요한 아카이브 로그를 확인
--여기서 조회되는 아카이브로그는 scn 4221978
으로 가기 위한 필수 아카이브 로그임
col name for a80
select NAME,
THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# from v$archived_log where
4221978 between FIRST_CHANGE# and NEXT_CHANGE#;
NAME
THREAD# SEQUENCE# FIRST_CHANGE#
NEXT_CHANGE#
--------------------------------------------------------------------------------
---------- ---------- -------------
------------
+FRA1/ORA19R/ARCHIVELOG/2025_08_23/thread_2_seq_69.286.1209889059
2
69 4221845
4222113
+FRA1/ORA19R/ARCHIVELOG/2025_08_23/thread_1_seq_101.309.1209889061
1
101 4221842
4222116
--hread 1의 101 번과
thread 2 의 69 번인 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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (DBID=1831232271)
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
------- ---------- ----------- ------------
-------------------
291
8.42M
DISK 00:00:01
2025-08-23 08:15:35
BP Key:
291 Status: AVAILABLE Compressed: NO Tag:
TAG20250823T081534
Piece Name:
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
List of Archived Logs in backup set
291
Thrd Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1 100
4218352 2025-08-23 08:00:07 4221842
2025-08-23 08:15:32
2
68 4218336 2025-08-23 08:00:07
4221845 2025-08-23 08:15:32
--> 백업된 리스트에 없는 것을 알 수 있음 (결국 drop table purge 시점으로 복구하려면 해당
아카이브를 백업받아서 타켓에 가져가야함!)
--결국 thread 1의 101 번과
thread 2 의 69 번인 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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (DBID=1831232271)
RMAN>
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 101 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-23
08:23:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=279
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=101 RECID=194
STAMP=1209889060
channel ORA_DISK_1: starting piece 1 at 2025-08-23
08:23:43
channel ORA_DISK_1: finished piece 1 at 2025-08-23 08:23:44
piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
tag=TAG20250823T082343 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-08-23
08:23:44
Starting
Control File and SPFILE Autobackup at 2025-08-23 08:23:45
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250823-0a
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-08-23
08:23:48
-->
'c-1831232271-20250823-0a' 해당 컨트롤 파일에는 아카이브를 백업 받은 내용도 같이 저장된 상태임
--> 이부분을
이해하는것이 매우 중요함
RMAN>
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 69 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-23
08:24:40
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=69 RECID=193
STAMP=1209889059
channel ORA_DISK_1: starting piece 1 at 2025-08-23
08:24:42
channel ORA_DISK_1: finished piece 1 at 2025-08-23 08:24:43
piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
tag=TAG20250823T082441 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-08-23
08:24:43
Starting
Control File and SPFILE Autobackup at 2025-08-23 08:24:43
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250823-0b
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-08-23
08:24:46
-->
'c-1831232271-20250823-0b' 해당 컨트롤 파일에는 아카이브를 백업 받은 내용도 같이 저장된 상태임
--> 이부분을
이해하는것이 매우 중요함
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ls -lRa
/home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total 1841164
drwxr-xr-x. 3
oracle oinstall 4096 Aug 23 08:24 .
drwxrwxr-x.
10 oracle oinstall 4096 Aug 23 08:17
..
-rw-r-----. 1 oracle asmadmin 8826368 Aug 23 08:15
ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
-rw-r-----. 1 oracle
asmadmin 359936 Aug 23 08:23
ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
-rw-r-----. 1 oracle
asmadmin 6144 Aug 23 08:24
ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
drwxr-xr-x. 2
oracle oinstall 4096 Aug 23 08:24
autobackup
-rw-r-----. 1 oracle asmadmin 206512128 Aug 23 08:01
CDB_SYSAUX_8j41qr9b_275_1_1.bkp
-rw-r-----. 1 oracle asmadmin 459112448
Aug 23 08:03 CDB_SYSTEM_8k41qrb5_276_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1630208 Aug 23 08:03
CDB_UNDOTBS1_8l41qreq_277_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1679360 Aug 23 08:03
CDB_UNDOTBS2_8m41qrev_278_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Aug 23 08:03
CDB_USERS_8n41qrf4_279_1_1.bkp
-rw-r-----. 1 oracle asmadmin
72581120 Aug 23 08:04 ORA19RP1_SYSAUX_8o41qrfa_280_1_1.bkp
-rw-r-----.
1 oracle asmadmin 266559488 Aug 23 08:05
ORA19RP1_SYSTEM_8p41qrg5_281_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 69222400 Aug 23 08:05
ORA19RP1_TUNER_DATA1_8q41qrii_282_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 14188544 Aug 23 08:06
ORA19RP1_TUNER_IDX1_8r41qrj3_283_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 6922240 Aug 23 08:06
ORA19RP1_UNDO_2_8t41qrjg_285_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 5341184 Aug 23 08:06
ORA19RP1_UNDOTBS1_8s41qrjc_284_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1073152 Aug 23 08:06
ORA19RP1_USERS_8u41qrjl_286_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64692224 Aug 23 08:09 PDB1_SYSAUX_9241qroa_290_1_1.bkp
-rw-r-----. 1
oracle asmadmin 265445376 Aug 23 08:10
PDB1_SYSTEM_9341qrph_291_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1449984 Aug 23 08:10
PDB1_TUNER_DATA1_9441qrrk_292_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1351680 Aug 23 08:10
PDB1_TUNER_IDX1_9541qrs6_293_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1114112 Aug 23 08:10
PDB1_UNDOTBS1_9641qrs9_294_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64561152 Aug 23 08:06 PDBSEED_SYSAUX_8v41qrjo_287_1_1.bkp
-rw-r-----. 1
oracle asmadmin 265166848 Aug 23 08:07
PDBSEED_SYSTEM_9041qrkj_288_1_1.bkp
-rw-r-----. 1 oracle asmadmin
85991424 Aug 23 08:08 PDBSEED_UNDOTBS1_9141qrn2_289_1_1.bkp
-rw-r-----.
1 oracle asmadmin 20299776 Aug 23 08:24
snapcf_CA.f
/home/oracle/ORA19R_BACKUP/autobackup:
total 79752
drwxr-xr-x. 2
oracle oinstall 4096 Aug 23 08:24 .
drwxr-xr-x. 3
oracle oinstall 4096 Aug 23 08:24 ..
-rw-r-----. 1
oracle asmadmin 20414464 Aug 23 08:10 c-1831232271-20250823-08
-rw-r-----. 1
oracle asmadmin 20414464 Aug 23 08:15 c-1831232271-20250823-09
-rw-r-----. 1
oracle asmadmin 20414464 Aug 23 08:23 c-1831232271-20250823-0a
-rw-r-----. 1 oracle asmadmin 20414464 Aug 23
08:24 c-1831232271-20250823-0b
--> 가장 최근에 백업 받은 컨트롤 파일은 c-1831232271-20250823-0b 임!!!!
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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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
[ol7ora19r1][SYS@ORA19R1]$
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -l
/home/oracle/ORA19R_BACKUP/initORA19R.ora
-rw-r--r--. 1 oracle asmadmin 1901 Aug
23 08:28 /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-r-----. 1 grid oinstall 2048 Aug 23
08:38 /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 23
08:38 /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/autobackup’
[ORA19R:oracle@tdb02t][/home/oracle]$ cd /home/oracle/ORA19R_BACKUP
--drop table purge 한 테이블이 속해있는
ORA19RP1 PDB내에 속해있는 TUNER_DATA1, TUNER_IDX1 테이블 스페이스만을 복구하기 위해서 필요한 파일만을 수신 받는
것임
--CDB, PDB$SEED, 아카이브,
컨트롤파일(오토백업), 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/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/
--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/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]$ ls -laR
/home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total
1817564
drwxr-xr-x. 4 oracle oinstall 4096 Aug
23 12:17 .
drwx------. 9 oracle oinstall 4096
Aug 23 12:11 ..
-rw-r-----. 1 oracle oinstall 8826368 Aug 23
08:15 ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
-rw-r-----. 1 oracle
oinstall 359936 Aug 23 08:23
ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
-rw-r-----. 1 oracle
oinstall 6144 Aug 23 08:24
ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
drwxr-xr-x. 2 oracle
oinstall 4096 Aug 23 09:01
autobackup
-rw-r-----. 1 oracle oinstall 206512128 Aug 23 08:01
CDB_SYSAUX_8j41qr9b_275_1_1.bkp
-rw-r-----. 1 oracle oinstall 459112448 Aug
23 08:03 CDB_SYSTEM_8k41qrb5_276_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1630208 Aug 23 08:03
CDB_UNDOTBS1_8l41qreq_277_1_1.bkp
-rw-r-----. 1 oracle oinstall
1679360 Aug 23 08:03 CDB_UNDOTBS2_8m41qrev_278_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1245184 Aug 23 08:03
CDB_USERS_8n41qrf4_279_1_1.bkp
-rw-r--r--. 1 oracle
oinstall 1071 Aug 23 09:11
initORA19R.ora
-rw-r--r--. 1 oracle oinstall
1901 Aug 23 09:10 initORA19R.ora.bak
drwxr-xr-x. 7 oracle
oinstall 87 Aug 23 10:25
ORA19R
-rw-r-----. 1 oracle oinstall 72581120 Aug 23 08:04
ORA19RP1_SYSAUX_8o41qrfa_280_1_1.bkp
-rw-r-----. 1 oracle oinstall 266559488
Aug 23 08:05 ORA19RP1_SYSTEM_8p41qrg5_281_1_1.bkp
-rw-r-----. 1 oracle
oinstall 69222400 Aug 23 08:05
ORA19RP1_TUNER_DATA1_8q41qrii_282_1_1.bkp
-rw-r-----. 1 oracle oinstall
14188544 Aug 23 08:06 ORA19RP1_TUNER_IDX1_8r41qrj3_283_1_1.bkp
-rw-r-----. 1
oracle oinstall 6922240 Aug 23 08:06
ORA19RP1_UNDO_2_8t41qrjg_285_1_1.bkp
-rw-r-----. 1 oracle
oinstall 5341184 Aug 23 08:06
ORA19RP1_UNDOTBS1_8s41qrjc_284_1_1.bkp
-rw-r-----. 1 oracle
oinstall 2048 Aug 23 08:43
orapwORA19R
-rw-r-----. 1 oracle oinstall 64692224 Aug 23 08:09
PDB1_SYSAUX_9241qroa_290_1_1.bkp
-rw-r-----. 1 oracle oinstall 265445376 Aug
23 08:10 PDB1_SYSTEM_9341qrph_291_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1114112 Aug 23 08:10
PDB1_UNDOTBS1_9641qrs9_294_1_1.bkp
-rw-r-----. 1 oracle oinstall
64561152 Aug 23 08:06 PDBSEED_SYSAUX_8v41qrjo_287_1_1.bkp
-rw-r-----. 1
oracle oinstall 265166848 Aug 23 08:07
PDBSEED_SYSTEM_9041qrkj_288_1_1.bkp
-rw-r-----. 1 oracle oinstall
85991424 Aug 23 08:08 PDBSEED_UNDOTBS1_9141qrn2_289_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 79752
drwxr-xr-x. 2
oracle oinstall 4096 Aug 23 09:01 .
drwxr-xr-x. 4
oracle oinstall 4096 Aug 23 12:17 ..
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:10 c-1831232271-20250823-08
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:15 c-1831232271-20250823-09
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:23 c-1831232271-20250823-0a
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:24
c-1831232271-20250823-0b
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
Copyright (c) 1991, 2025, Oracle. All rights reserved.
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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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-23T09:14:53.718037+09:00
MMNL started with pid=33, OS
id=2516
starting up 1 shared server(s)
...
2025-08-23T09:14:53.983258+09:00
Starting background process
TMON
2025-08-23T09:14:54.096298+09:00
TMON started with pid=36, OS
id=2528
ORACLE_BASE from environment =
/u01/app/oracle
2025-08-23T09:14:54.593697+09:00
Using default
pga_aggregate_limit of 2048 MB
--> alert log 모니터링 시작
12. 타켓에서 restore controlfile 한후 mount 모드로 변경 (여기 할 차례 현재 nomount으로 타켓 떠 있는 상황임)
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$
export ORACLE_SID=ORA19R
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$
export NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ alias
rt
alias rt='rman target
/'
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$
rt
Recovery
Manager: Release 19.0.0.0.0 - Production on Wed Aug 20 20:09:33 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (not mounted)
RMAN>
host 'ls -alrt
/home/oracle/ORA19R_BACKUP/autobackup/';
host 'ls -alrt
/home/oracle/ORA19R_BACKUP/autobackup/';
total 79752
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:10 c-1831232271-20250823-08
-rw-r-----. 1
oracle oinstall 20414464 Aug 23 08:15 c-1831232271-20250823-09
-rw-r-----. 1 oracle oinstall
20414464 Aug 23 08:23 c-1831232271-20250823-0a
-rw-r-----. 1 oracle oinstall 20414464 Aug 23 08:24
c-1831232271-20250823-0b
drwxr-xr-x. 2 oracle
oinstall 4096 Aug 23 09:01 .
drwxr-xr-x. 4 oracle oinstall 4096
Aug 23 09:11 ..
host command complete
--> 가장 최신의
컨트롤 파일 백업본(오토백업본)으로 복구함!!(이것이 정말 중요함!)
RMAN> restore
controlfile from
'/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250823-0b';
restore controlfile
from '/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250823-0b';
Starting restore at 2025-08-23 09:17:20
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device
type=DISK
channel ORA_DISK_1:
restoring control file
channel ORA_DISK_1: restore
complete, elapsed time: 00:00:03
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-23 09:17:25
RMAN> alter
database mount;
alter database mount;
released
channel: ORA_DISK_1
Statement processed
--> resotre controlfile을 한
직후에는 컨트롤 파일의 데이터파일 경로 및 파일정보에 맞게 실제 파일이
--> 존재하지 않아도
mount까지 갈 수 있음
13. 소스에서 REDO LOG를 RENAME 하는 스크립트를 생성하는 SQL문
실행
SELECT 'alter
database rename file ' ||''''|| member ||''''|| ' ' ||''|| ' to '|| ''''
|| case when member like '+DATA1%' then
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/'
when member
like '+FRA1%' then '/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/' end
|| substr(member, instr(member, 'group'))||'''' ||';'
AS rename_redo_log
FROM v$logfile
;
<결과>
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_2.283.1209237581' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_2.283.1209237581';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_1.280.1209237351' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_1.280.1209237351';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_3.273.1209236491' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_3.273.1209236491';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_4.281.1209237371' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_4.281.1209237371';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_5.279.1209236513' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_5.279.1209236513';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_6.282.1209237467' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_6.282.1209237467';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_3.297.1209236493' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_3.297.1209236493';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_5.260.1209236513' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_5.260.1209236513';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_1.258.1209237353' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_1.258.1209237353';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_4.302.1209237373' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_4.302.1209237373';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_6.273.1209237469' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_6.273.1209237469';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_2.283.1209237581' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_2.283.1209237581';
14. 위에서 생성한 REDO LOG를 RENAME하는 스크립트를 타켓에서 실행
시킴!
--타켓에서 실행 시킬 때 RMAN 혹은 sqlplus든
모두 가능함!
alter database
rename file '+DATA1/ORA19R/ONLINELOG/group_2.283.1209237581' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_2.283.1209237581';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_1.280.1209237351' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_1.280.1209237351';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_3.273.1209236491' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_3.273.1209236491';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_4.281.1209237371' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_4.281.1209237371';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_5.279.1209236513' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_5.279.1209236513';
alter database rename file
'+DATA1/ORA19R/ONLINELOG/group_6.282.1209237467' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_6.282.1209237467';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_3.297.1209236493' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_3.297.1209236493';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_5.260.1209236513' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_5.260.1209236513';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_1.258.1209237353' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_1.258.1209237353';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_4.302.1209237373' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_4.302.1209237373';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_6.273.1209237469' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_6.273.1209237469';
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_2.283.1209237581' to
'/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_2.283.1209237581';
--타켓에서 REDO LOG RENAME 후 아래의
SQL문으로 상태를 확인!
[ORA19R:oracle@tdb02t][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19R:oracle@tdb02t][/home/oracle]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Aug 20 20:59:51 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[tdb02t>][ORA19R@SYS]$
set linesize 250
col member for a100
select a.group#,
a.thread#, a.bytes/1024/1024 as mb, a.status, b.member from v$log a, v$logfile b
where a.group# = b.group#;
GROUP# THREAD#
MB
STATUS
MEMBER
---------- ---------- ----------
------------------------------------------------
----------------------------------------------------------------------------------------------------
1
1 200
CURRENT
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_1.258.1209237353
1
1 200
CURRENT
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_1.280.1209237351
2
1 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_2.283.1209237581
2
1 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_2.283.1209237581
3
1 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_3.273.1209236491
3
1 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_3.297.1209236493
4
2 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_4.281.1209237371
4
2 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_4.302.1209237373
5
2 200
CURRENT
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_5.260.1209236513
5
2 200
CURRENT
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_5.279.1209236513
6
2 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_2/group_6.273.1209237469
6
2 200
INACTIVE
/home/oracle/ORA19R_BACKUP/ORA19R/redolog_1/group_6.282.1209237467
12 rows selected.
Elapsed: 00:00:00.01
15. 소스에서 set newname 스크립트를 생성하는 sql문 실행 및 skip할 테이블 스페이스를 출력하는 sql문 실행
--현재 소스 서버의 ORA19RP1 PDB 에는 사용자
테이블 스페이스로 TUNER_DATA1, TUNER_IDX1, USERS가 존재하는 상황임
--이상
상황에서 USERS는 복구할 필요가 없으므로 제외 시킬것임
--이러한 작업을 원활하게 하기 위해서
아래의 스크립트를 실행함
--실전에서는 복구대상 테이블 스페이스는 소수가 될것이고, skip할 테이블
스페이스는 매우 많을 것이므로 IN조건으로 복구대상 테이블 스페이스를 지정해줌
SELECT 'set newname for datafile
' || FILE_NO || ' ' || 'to ' || ''''
|| CASE WHEN CON_NAME = 'CDB$ROOT' THEN
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/' || 'cdb' || '/' ||
SUBSTR(DATA_FILE_PATH_NAME, INSTR(DATA_FILE_PATH_NAME, '/', -1) + 1) ||
''''||';'
WHEN
CON_NAME = 'PDB$SEED' THEN '/home/oracle/ORA19R_BACKUP/ORA19R/datafile/' ||
'pdbseed' || '/' || SUBSTR(DATA_FILE_PATH_NAME, INSTR(DATA_FILE_PATH_NAME, '/',
-1) + 1) || ''''||';'
else
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/' || lower(CON_NAME) || '/' ||
SUBSTR(DATA_FILE_PATH_NAME, INSTR(DATA_FILE_PATH_NAME, '/', -1) + 1) ||
''''||';'
end as set_newname
, 'restore datafile ' || FILE_NO || ';' AS
RESTORE_DATAFILE
, 'switch datafile ' || FILE_NO
|| ';' AS SWITCH_DATAFILE
FROM
(
SELECT A.FILE# AS FILE_NO
, A.NAME AS DATA_FILE_PATH_NAME
, B.TABLESPACE_NAME AS TS_NAME
, C.NAME AS CON_NAME
, c.guid as guid
FROM V$DATAFILE A
, CDB_DATA_FILES B
, V$CONTAINERS C
WHERE A.FILE# = B.FILE_ID(+)
AND A.CON_ID = C.CON_ID
ORDER BY FILE#
) A
WHERE (
A.CON_NAME IN ('CDB$ROOT',
'PDB$SEED') --CDB 및 PDBSEED는 무조건 포함
OR
( A.TS_NAME LIKE 'SYSTEM%' --SYSTEM 테이블 스페이스는 무조건 포함
OR A.TS_NAME LIKE 'SYSAUX%'
--SYSAUX 테이블 스페이스는 무조건 포함
OR
A.TS_NAME LIKE 'UNDO%' --UNDO 테이블 스페이스는 무조건 포함
)
)
OR
(
A.CON_NAME = 'ORA19RP1' AND A.TS_NAME IN
('TUNER_DATA1', 'TUNER_IDX1') --복구 대상 PDB 및 해당 PDB내 복구 대상 유저 테이블 스페이스
)
;
--> PDB(ORA19RP1)의 USERS
테이블스페이스는 제외하고 생성 (PDB인 PDB1은 복구 대상이 아니지만 SYSTEM, SYSAUX, UNDO는 복구 대상으로
포함시켜야함)
--> 파라미터 파일(pfile) 설정 시
*.db_create_file_dest='/home/oracle/ORA19R_BACKUP' 로 했기 때문에
--> /home/oracle/ORA19R_BACKUP/ORA19R/datafile/ 위치가
datafile의 위치가 됨
--> db_create_file_dest 위치 아래에
datafile 경로를 추가해서 그 밑에 넣어주게됨
SET_NEWNAME
---------------------------------------------------------------------------------------------------------------------
set newname for datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/system.256.1209229717';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613';
set newname for datafile 6 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613';
set newname for datafile 7 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613';
set newname for datafile 9 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869';
set newname for datafile 10 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643';
set newname for datafile 11 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643';
set newname for datafile 12 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643';
set newname for datafile 13 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675';
set newname for datafile 25 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079';
set newname for datafile 26 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079';
set newname for datafile 27 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079';
set newname for datafile 34 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503';
set newname for datafile 35 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509';
RESTORE_DATAFILE
-----------------------
restore
datafile 1;
restore datafile 3;
restore datafile 4;
restore datafile
5;
restore datafile 6;
restore
datafile 7;
restore datafile 8;
restore datafile 9;
restore datafile
10;
restore datafile 11;
restore
datafile 12;
restore datafile 13;
restore datafile 25;
restore datafile
26;
restore datafile 27;
restore
datafile 34;
restore datafile 35;
SWITCH_DATAFILE
-----------------------
switch
datafile 1;
switch datafile 3;
switch datafile 4;
switch datafile
5;
switch datafile 6;
switch
datafile 7;
switch datafile 8;
switch datafile 9;
switch datafile
10;
switch datafile 11;
switch
datafile 12;
switch datafile 13;
switch datafile 25;
switch datafile
26;
switch datafile 27;
switch
datafile 34;
switch datafile 35;
--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') --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 기재
)
OR
(
C.CON_NAME <>
'ORA19RP1' --복구 대상 PDB가 아니라면
AND ( C.TS_NAME LIKE 'SYSTEM%' -- SYSTEM 테이블스페이스를 복구하기 때문에 스킵안함
OR
C.TS_NAME LIKE 'SYSAUX%' -- SYSAUX 테이블스페이스를 복구하기 때문에 스킵안함
OR C.TS_NAME LIKE
'UNDO%' -- UNDO 테이블스페이스를 복구하기 때문에 스킵안함
)
)
)
;
RMAN_SKIP_CLAUSE
-----------------------------------------------------------------------------------------------------------------
SKIP TABLESPACE
'ORA19RP1:USERS','PDB1:TUNER_DATA1','PDB1:TUNER_IDX1'
--> recover시 사용할 것임
16. 타켓에서 RESTORE DATABASE
--타켓에서 RMAN으로 아래와 같이 RESTORE를
수행함 (현재 타켓 서버에서 ORA19R DB는 mount상태임!)
--mount 시켰던 rman세션을
그대로 써도 되고 아래와 같이 새로 접속해도됨!
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ export
ORACLE_SID=ORA19R
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ export
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ alias
rt
alias rt='rman target /'
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$
rt
Recovery Manager:
Release 19.0.0.0.0 - Production on Sat Aug 23 09:51:29 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (DBID=1831232271, not open)
run
{
allocate channel ch1 device type
disk ;
allocate channel ch2 device type disk
;
set newname for
datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/system.256.1209229717';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613';
set newname for datafile 6 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613';
set newname for datafile 7 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613';
set newname for datafile 9 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869';
set newname for datafile 10 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643';
set newname for datafile 11 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643';
set newname for datafile 12 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643';
set newname for datafile 13 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675';
set newname for datafile 25 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079';
set newname for datafile 26 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079';
set newname for datafile 27 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079';
set newname for datafile 34 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503';
set newname for datafile 35 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509';
restore datafile
1;
restore datafile 3;
restore
datafile 4;
restore datafile 5;
restore datafile 6;
restore datafile
7;
restore datafile 8;
restore
datafile 9;
restore datafile 10;
restore datafile 11;
restore datafile
12;
restore datafile 13;
restore
datafile 25;
restore datafile 26;
restore datafile 27;
restore datafile
34;
restore datafile 35;
switch datafile
1;
switch datafile 3;
switch
datafile 4;
switch datafile 5;
switch datafile 6;
switch datafile
7;
switch datafile 8;
switch
datafile 9;
switch datafile 10;
switch datafile 11;
switch datafile
12;
switch datafile 13;
switch
datafile 25;
switch datafile 26;
switch datafile 27;
switch datafile
34;
switch datafile 35;
release channel
ch1;
release channel ch2;
}
allocated channel:
ch1
channel ch1: SID=133 device
type=DISK
allocated channel:
ch2
channel ch2: SID=386 device
type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-08-23 13:21:21
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00001 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/system.256.1209229717
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_8k41qrb5_276_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_8k41qrb5_276_1_1.bkp
tag=TAG20250823T080140
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:01:26
Finished restore at 2025-08-23
13:22:47
Starting restore at 2025-08-23 13:22:47
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00003 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_8j41qr9b_275_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_8j41qr9b_275_1_1.bkp
tag=TAG20250823T080043
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:56
Finished restore at 2025-08-23
13:23:43
Starting restore at 2025-08-23 13:23:43
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00004 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_8l41qreq_277_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_8l41qreq_277_1_1.bkp
tag=TAG20250823T080337
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:07
Finished restore at 2025-08-23
13:23:50
Starting restore at 2025-08-23 13:23:50
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00005 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_9041qrkj_288_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_9041qrkj_288_1_1.bkp
tag=TAG20250823T080643
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:55
Finished restore at 2025-08-23
13:24:46
Starting restore at 2025-08-23 13:24:46
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00006 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_8v41qrjo_287_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_8v41qrjo_287_1_1.bkp
tag=TAG20250823T080616
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:19
Finished restore at 2025-08-23
13:25:05
Starting restore at 2025-08-23 13:25:05
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00007 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_USERS_8n41qrf4_279_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_USERS_8n41qrf4_279_1_1.bkp
tag=TAG20250823T080348
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:01
Finished restore at 2025-08-23
13:25:06
Starting restore at 2025-08-23 13:25:06
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00008 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_9141qrn2_289_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_9141qrn2_289_1_1.bkp
tag=TAG20250823T080802
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:26
Finished restore at 2025-08-23
13:25:32
Starting restore at 2025-08-23 13:25:32
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00009 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_8m41qrev_278_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_8m41qrev_278_1_1.bkp
tag=TAG20250823T080343
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:01
Finished restore at 2025-08-23
13:25:33
Starting restore at 2025-08-23 13:25:33
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00010 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_8p41qrg5_281_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSTEM_8p41qrg5_281_1_1.bkp
tag=TAG20250823T080421
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:55
Finished restore at 2025-08-23
13:26:28
Starting restore at 2025-08-23 13:26:28
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00011 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_8o41qrfa_280_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_SYSAUX_8o41qrfa_280_1_1.bkp
tag=TAG20250823T080353
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:25
Finished restore at 2025-08-23
13:26:54
Starting restore at 2025-08-23 13:26:54
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00012 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_8s41qrjc_284_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDOTBS1_8s41qrjc_284_1_1.bkp
tag=TAG20250823T080603
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:03
Finished restore at 2025-08-23
13:26:57
Starting restore at 2025-08-23 13:26:57
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00013 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_8t41qrjg_285_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_UNDO_2_8t41qrjg_285_1_1.bkp
tag=TAG20250823T080608
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:03
Finished restore at 2025-08-23
13:27:01
Starting restore at 2025-08-23 13:27:01
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00025 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDB1_SYSTEM_9341qrph_291_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDB1_SYSTEM_9341qrph_291_1_1.bkp
tag=TAG20250823T080920
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:55
Finished restore at 2025-08-23
13:27:56
Starting restore at 2025-08-23 13:27:56
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00026 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDB1_SYSAUX_9241qroa_290_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDB1_SYSAUX_9241qroa_290_1_1.bkp
tag=TAG20250823T080841
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:25
Finished restore at 2025-08-23
13:28:21
Starting restore at 2025-08-23 13:28:22
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00027 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDB1_UNDOTBS1_9641qrs9_294_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/PDB1_UNDOTBS1_9641qrs9_294_1_1.bkp
tag=TAG20250823T081049
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:03
Finished restore at 2025-08-23
13:28:25
Starting restore at 2025-08-23 13:28:25
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00034 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_8q41qrii_282_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_DATA1_8q41qrii_282_1_1.bkp
tag=TAG20250823T080538
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:17
Finished restore at 2025-08-23
13:28:42
Starting restore at 2025-08-23 13:28:42
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel
ch1: restoring datafile 00035 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_8r41qrj3_283_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19RP1_TUNER_IDX1_8r41qrj3_283_1_1.bkp
tag=TAG20250823T080555
channel ch1: restored backup piece
1
channel ch1: restore complete, elapsed time:
00:00:08
Finished restore at 2025-08-23
13:28:50
released channel: ch1
released channel: ch2
--sqlplus에서 실행
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ 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 Sat Aug 23 10:39:25 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[tdb02t>][ORA19R@SYS]$
col name for
a120
SELECT FILE#, STATUS, NAME FROM
V$DATAFILE;
FILE#
STATUS
NAME
---------- ---------------------
------------------------------------------------------------------------------------------------------------------------
1
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/system.256.1209229717
3
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777
5
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613
6
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613
7
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613
9
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869
10
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643
11
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643
12
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643
13
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675
14
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_users_nblhvs44_.dbf
25
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079
26
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079
27
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079
30
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_da_nblhvsjl_.dbf
31
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_id_nblhvvs3_.dbf
34
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503
35
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509
--> 복구 대상이 아닌 datafile에 대해 offline drop을 해야함
17. 소스DB에서 복구 대상이 아닌 테이블 스페이스에 대한 offline drop 문을 생성할 FILE_NO를 구함
SELECT
listagg(FILE_NO, ',') WITHIN GROUP (ORDER BY FILE_NO) as file_no_list
FROM
(
SELECT A.FILE# AS FILE_NO
, A.NAME AS DATA_FILE_PATH_NAME
, B.TABLESPACE_NAME AS TS_NAME
, C.NAME AS CON_NAME
, c.guid as guid
FROM V$DATAFILE A
, CDB_DATA_FILES B
, V$CONTAINERS C
WHERE A.FILE# = B.FILE_ID(+)
AND A.CON_ID = C.CON_ID
ORDER BY FILE#
) c
WHERE
1=1
AND C.CON_NAME NOT IN ('CDB$ROOT',
'PDB$SEED') --CDB 및 PDBSEED는 무조건 복구 대상임 스킵 대상에 포함안함
AND NOT (
( C.CON_NAME = 'ORA19RP1' --복구 대상 PDB
AND C.TS_NAME IN
('SYSTEM','SYSAUX','UNDOTBS1','UNDO_2','TUNER_DATA1','TUNER_IDX1') --복구대상
TABLESPACE 기재
)
OR
(
C.CON_NAME <>
'ORA19RP1' --복구 대상 PDB가 아니라면
AND ( C.TS_NAME LIKE 'SYSTEM%' -- SYSTEM 테이블스페이스를 복구하기 때문에 스킵안함
OR
C.TS_NAME LIKE 'SYSAUX%' -- SYSAUX 테이블스페이스를 복구하기 때문에 스킵안함
OR C.TS_NAME LIKE
'UNDO%' -- UNDO 테이블스페이스를 복구하기 때문에 스킵안함
)
)
)
;
FILE_NO_LIST
-----------------
14,30,31
18. 타켓에서 복구 대상이 아닌 테이블 스페이스 offline
--> 타켓에서
조회
[>][ORA19R@SYS]$
set linesize
300
COL OFFLINE_DROP FOR A200
COL
con_name FOR A20
SELECT 'ALTER SESSION SET CONTAINER=' ||
a.con_name || ';' || chr(10) || 'alter database datafile'
|| '''' ||DATA_FILE_PATH_NAME || ''''
|| ' offline drop;' as
offline_drop
FROM
(
SELECT A.FILE# AS FILE_NO
, A.NAME AS DATA_FILE_PATH_NAME
, C.NAME AS CON_NAME
, c.guid as guid
FROM V$DATAFILE A
, V$CONTAINERS C
WHERE 1=1
AND A.CON_ID = C.CON_ID
ORDER BY FILE#
) A
WHERE A.FILE_NO in (14,30,31)
;
OFFLINE_DROP
--------------------------------------------------------------------------------------------------------------------------------------------------
ALTER SESSION SET CONTAINER=ORA19RP1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_users_nblhvs44_.dbf'
offline drop;
ALTER SESSION SET
CONTAINER=PDB1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_da_nblhvsjl_.dbf'
offline drop;
ALTER SESSION SET
CONTAINER=PDB1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_id_nblhvvs3_.dbf'
offline drop;
--타켓에서 실행
[>][ORA19R@SYS]$
ALTER SESSION SET
CONTAINER=ORA19RP1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_users_nblhvs44_.dbf'
offline drop;
ALTER SESSION SET
CONTAINER=PDB1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_da_nblhvsjl_.dbf'
offline drop;
ALTER SESSION SET
CONTAINER=PDB1;
alter database
datafile'/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_id_nblhvvs3_.dbf'
offline drop;
--> recover 대상이 아닌 테이블스페이스를 OFFLINE해서 RECOVER 시 복구 대상에서 제외 시키는 작업임
[tdb02t>][ORA19R@SYS]$ ALTER SESSION SET CONTAINER=cdb$root;
Session altered.
Elapsed: 00:00:00.00
[tdb02t>][ORA19R@SYS]$
show con_name;
CON_NAME
------------------------------
CDB$ROOT
[tdb02t>][ORA19R@SYS]$
col name
for a120
SELECT FILE#, STATUS, NAME FROM
V$DATAFILE;
FILE#
STATUS
NAME
---------- ---------------------
------------------------------------------------------------------------------------------------------------------------
1
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/system.256.1209229717
3
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777
5
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613
6
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613
7
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613
9
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869
10
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643
11
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643
12
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643
13
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675
14
RECOVER
/home/oracle/ORA19R_BACKUP/ORA19R/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_users_nblhvs44_.dbf
25
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079
26
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079
27
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079
30
RECOVER
/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_da_nblhvsjl_.dbf
31
RECOVER
/home/oracle/ORA19R_BACKUP/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_id_nblhvvs3_.dbf
34
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503
35
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509
20 rows selected.
Elapsed: 00:00:00.02
Elapsed: 00:00:00.00
--> offline 시킨 테이블 스페이가 recover 상태로 되어
있음
19. 타켓에서 필요한 아카이브 로그 파일을 모두 RMAN이 인식하고 있는지
확인
소스 디비에서 조회했을 때 SCN : 4221978
시점에는 ora19rp1 PDB내 TUNER.TB_CUST 테이블이 존재했었던 상황임!
그러므로 해당
SCN번호가 속해있는 아카이브 로그 파일을 전부 인식하고 있는지 확인해야하는 것임!
아까 소스에서 SCN : 4221978 이 속해있는
아카이브 파일을 아래와 같이 백업 받았었음!
RMAN> BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 101 THREAD 1 FILESPERSET 1 FORMAT
'/home/oracle/ORA19R_BACKUP/ARCHIVE_%d_t%h_s%e_set%s_p%p_%T.bkp';
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE
69 THREAD 2 FILESPERSET 1 FORMAT
'/home/oracle/ORA19R_BACKUP/ARCHIVE_%d_t%h_s%e_set%s_p%p_%T.bkp';
소스에서 백업 받았었고, 소스에서 백업 시 그 내용이
컨트롤 파일에도 백업 받은 내용이 저장되었고! 그 (아카이브 백업 내역이 저장된)컨트롤 파일로 복구 했으니
아래에서와 같이 타켓에서 아카이브 로그 백업 상황을 확인해보면 필요한 아카이브 로그가 있다고 나오는
것임
만약 인식 못한다면 CATALOG START WITH로 인식시켜줘야함 (이부분을 이해하는 것이
매우 중요함)
(결론은 목표 시점으로 불완전 복구를 하는데
필요한 아카이브 파일을 타켓으로 잘 가져와야 하고 잘 가져온 그 아카이브 백업본을 rman이 잘 인식하고
있어야함!
RMAN> list backup of archivelog all;
list backup of archivelog all;
List of Backup
Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ -------------------
291
8.42M
DISK 00:00:01
2025-08-23 08:15:35
BP Key:
291 Status: AVAILABLE Compressed: NO Tag:
TAG20250823T081534
Piece Name:
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
List of
Archived Logs in backup set 291
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 100 4218352
2025-08-23 08:00:07 4221842 2025-08-23 08:15:32
2 68
4218336 2025-08-23 08:00:07 4221845
2025-08-23 08:15:32
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
293
351.00K DISK
00:00:01 2025-08-23 08:23:44
BP Key:
293 Status: AVAILABLE Compressed: YES Tag:
TAG20250823T082343
Piece Name:
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
List of Archived Logs in backup set 293
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 101 4221842
2025-08-23 08:15:32 4222116 2025-08-23 08:17:40 -->
존재하는 것을 확인
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
295
5.50K
DISK 00:00:01
2025-08-23 08:24:42
BP Key:
295 Status: AVAILABLE Compressed: YES Tag:
TAG20250823T082441
Piece Name:
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
List of Archived Logs in backup set 295
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
2 69 4221845
2025-08-23 08:15:32 4222113 2025-08-23 08:17:39 -->
존재하는 것을 확인
--> 필요한 아카이브 로그를 모두 제대로
인식하고 있음
--RMAN> catalog start with
'/home/oracle/ORA19R_BACKUP';
--> 이 작업을 할 필요가 없음
--> 만약 직접 아카이브
로그를 restore 시킨다면 START---------------------------
--(recover 할 경우 아카이브를 restore한 후 recover함,
--그런데도 불구하고 아카이브로그를 archive log dest로 restore시켜놓고 싶다면)
RMAN> restore archivelog sequence <시퀀스번호>
thread <스레드번호>;
--> 만약
직접 아카이브 로그를 restore 시킨다면 END-----------------------------
20. 불완전 복구 수행
--skip 할 테이블 스페이스를
출력 (소스에서 실행)
SELECT listagg(CON_NAME||':'||TS_NAME, ',')
WITHIN GROUP(ORDER BY CON_NAME, TS_NAME) as skip_tablespace_name
FROM
(
SELECT
B.TABLESPACE_NAME AS
TS_NAME
, C.NAME AS CON_NAME
FROM CDB_TABLESPACES B
, V$CONTAINERS C
WHERE B.CON_ID = C.CON_ID
AND B.CONTENTS IN ('PERMANENT')
ORDER BY C.CON_ID, B.TABLESPACE_NAME
) C
WHERE 1=1
AND
C.CON_NAME NOT IN ('CDB$ROOT', 'PDB$SEED') --CDB 및 PDBSEED는 무조건 복구 대상임 스킵 대상에
포함안함
AND NOT (
( C.CON_NAME = 'ORA19RP1' --복구 대상 PDB
AND C.TS_NAME IN
('SYSTEM','SYSAUX','UNDOTBS1','UNDO_2','TUNER_DATA1','TUNER_IDX1') --복구대상
TABLESPACE 기재
)
OR
(
C.CON_NAME <>
'ORA19RP1' --복구 대상 PDB가 아니라면
AND ( C.TS_NAME LIKE 'SYSTEM%' -- SYSTEM 테이블스페이스를 복구하기 때문에 스킵안함
OR
C.TS_NAME LIKE 'SYSAUX%' -- SYSAUX 테이블스페이스를 복구하기 때문에 스킵안함
OR C.TS_NAME LIKE
'UNDO%' -- UNDO 테이블스페이스를 복구하기 때문에 스킵안함
)
)
)
;
SKIP_TABLESPACE_NAME
--------------------
ORA19RP1:USERS,PDB1:TUNER_DATA1,PDB1:TUNER_IDX1
RECOVER DATABASE skip forever tablespace ORA19RP1:USERS,PDB1:TUNER_DATA1,PDB1:TUNER_IDX1 UNTIL SCN 4221978;
Starting recover
at 2025-08-23 13:58:16
using channel
ORA_DISK_1
Executing: alter
database datafile 14 offline drop
Executing: alter
database datafile 30, 31 offline drop
starting media
recovery
channel
ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=68
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=100
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250823_9841qs56_s296_p1
tag=TAG20250823T081534
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_100_1209229842.arc thread=1
sequence=100
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_68_1209229842.arc thread=2
sequence=68
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=1
sequence=101
channel ORA_DISK_1: reading from backup
piece
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s101_set298_p1_20250823.bkp
tag=TAG20250823T082343
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/1_101_1209229842.arc thread=1
sequence=101
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=2
sequence=69
channel ORA_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s69_set300_p1_20250823.bkp
tag=TAG20250823T082441
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:02
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/arch/2_69_1209229842.arc thread=2
sequence=69
media recovery complete, elapsed time:
00:00:00
Finished recover at 2025-08-23
13:58:25
[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
MOUNTED
PRIMARY
1920977
4222113
0
-->
컨트롤 파일의 scn은 4222113 임
[tdb02t>][ORA19R@SYS]$
SET
LINESIZE 200
COL
FILE#
FOR 9999
COL
NAME
FOR A80
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/cdb/system.256.1209229717
4221978
2025-08-23 08:16:54 ONLINE
3
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/sysaux.262.1209229751
4221978
2025-08-23 08:16:54 ONLINE
4
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs1.261.1209229777
4221978
2025-08-23 08:16:54 ONLINE
5
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/system.258.1209230613
2387633
2025-08-15 17:39:16 ONLINE
6
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/sysaux.257.1209230613
2387633
2025-08-15 17:39:16 ONLINE
7
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/users.260.1209229777
4221978
2025-08-23 08:16:54 ONLINE
8
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdbseed/undotbs1.272.1209230613
2387633
2025-08-15 17:39:16 ONLINE
9
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/cdb/undotbs2.268.1209230869
4221978
2025-08-23 08:16:54 ONLINE
10
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/system.264.1209231643
4221978
2025-08-23 08:16:54 ONLINE
11
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/sysaux.265.1209231643
4221978
2025-08-23 08:16:54 ONLINE
12
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undotbs1.266.1209231643
4221978
2025-08-23 08:16:54 ONLINE
13
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/undo_2.277.1209231675
4221978
2025-08-23 08:16:54 ONLINE
14
(NULL) 0
(NULL)
OFFLINE
25
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/system.289.1209829079
4221978
2025-08-23 08:16:54 ONLINE
26
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/sysaux.290.1209829079
4221978
2025-08-23 08:16:54 ONLINE
27
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/pdb1/undotbs1.291.1209829079
4221978
2025-08-23 08:16:54 ONLINE
30
(NULL) 0
(NULL)
OFFLINE
31
(NULL) 0
(NULL)
OFFLINE
34
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_data1.270.1209887503
4221978
2025-08-23 08:16:54 ONLINE
35
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ora19rp1/tuner_idx1.274.1209887509
4221978
2025-08-23 08:16:54 ONLINE
15 rows selected.
Elapsed: 00:00:00.03
--> 모든 datafile 들이 scn번호
4221978 까지 제대로 recover됨 (pdbseed는 read only기 때문에 변하지 않은 것을 주의!)
--> 모든 datafile 들의 scn번호가 동일하니 resetlogs 오픈이 가능한
상황!
실제로 RESETLOGS는 해당
시점까지의 REDO를 모두 적용 완료한 후 "이 시점이 새로운 기준!"으로 삼겠다는 의미라,
파일 간
SCN이 다르면 일관성을 보장 할 수 없으니 반드시 맞아야 함.
STATUS도 모두 ONLINE(단,
14, 30, 31번은 OFFLINE 무시해도 됨. OFFLINE DROP 으로 복구 안 해도 되는 파일)
OFFLINE 데이터파일(여기선 FILE# 14, 30, 31)은
OFFLINE이거나 DROPPED라면 일치하지 않아도 RESETLOGS 오픈에 영향 없음(복구 대상
아님).
모든 데이터파일의 체크포인트 SCN이 동일하다 = 데이터베이스가 논리적으로 한 시점까지
recover되었으니, RESETLOGS 오픈이 가능하다
21. 불완전 복구 이므로 당연히 RESETLOGS로 오픈!
4221978 부터 디비가 다시 시작하고! 그때부터 다시 전혀 새로운 디비가 되는 것임!
REDO LOG도 초기화됨!
TEMPFILE도 자동으로 만들어짐!
[tdb02t>][ORA19R@SYS]$ alter database open resetlogs;
[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
MOUNTED (NULL)
4
PDB1
MOUNTED (NULL)
[tdb02t>][ORA19R@SYS]$ alter pluggable database ora19rp1 open;
Pluggable database altered.
Elapsed: 00:00:02.51
[tdb02t>][ORA19R@SYS]$ alter session set
container=ora19rp1;
Session altered.
Elapsed: 00:00:00.05
[tdb02t>][ORA19R@SYS]$ select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
Elapsed: 00:00:00.11
--> 감동의 순간임! drop table purge 했던 데이터를 되살려 낸 것임!
[tdb02t>][ORA19R@SYS]$ alter session set
container=cdb$root;
Session altered.
Elapsed:
00:00:00.01
[tdb02t>][ORA19R@SYS]$ select current_Scn from
v$database;
CURRENT_SCN
-----------
4223526
1 row selected.
Elapsed: 00:00:00.00
22. 타켓 서버에서 복구하는 과정에서 만들어진 모든 파일을
확인해보자!
[ORA19R:oracle@tdb02t][/home/oracle]$ cd
/home/oracle/ORA19R_BACKUP
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ ls
-lRa
23. 리스너를 잘 인식했는지 확인
소스서버에서 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 23-AUG-2025 14:05:36
Copyright (c) 1991, 2025, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
22-AUG-2025 15:23:08
Uptime
0 days 22 hr. 42 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...
Service
"3c64582fc4c307c3e0631500a8c094d4" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s)
for this service...
Service
"3cef724be3d726e1e0631500a8c0ea84" 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...
Service
"pdb1" has 1 instance(s).
Instance "ORA19R",
status READY, has 1 handler(s) for this service...
The
command completed successfully
24. 타켓에서 db 접속을
편하게 하도록 설정 (orapwd, tnsnames.ora, alias 등 설정)
[ORA19R:oracle@tdb02t][/home/oracle]$ vi .bash_profile
[ORA19R:oracle@tdb02t][/home/oracle]$ alias ssp1
alias ssp1='rlwrap sqlplus sys/oracle@ORA19RP1 as sysdba'
[ORA19R:oracle@tdb02t][/home/oracle]$ alias
ssp1t
alias
ssp1t='rlwrap sqlplus tuner/oracle@ORA19RP1'
[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.
ORA19R =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA19R)
)
)
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/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 20
drwxr-xr-x. 2 oracle
oinstall 78 Aug 21 19:46 .
drwxrwxr-x. 76
oracle oinstall 4096 Aug 19 15:40 ..
-rw-rw----. 1
oracle asmadmin 1544 Aug 21 19:24 hc_ORA19R.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015
init.ora
-rw-r-----. 1 oracle asmadmin
24 Aug 20 20:10 lkORA19R
-rw-r-----. 1 oracle
oinstall 2048 Aug 21 19:46
orapwORA19R
[ORA19R:oracle@tdb02t][/home/oracle]$ .
./.bash_profile
[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Aug 23 14:09:47 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[tdb02t>][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 23 14:10:01 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
--클라이언트(PC)의 tnsnames.ora
추가
TDB02T_ORA19R =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA19R)
)
)
TDB02T_ORA19R_ORA19RP1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.0.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19RP1)
)
)
25. 복구한 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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RP1
MOUNTED (NULL)
4
PDB1
MOUNTED (NULL)
[tdb02t>][ORA19R@SYS]$
alter pluggable database ora19rp1 open;
Pluggable database altered.
Elapsed: 00:00:03.46
[tdb02t>][ORA19R@SYS]$
alter pluggable database ora19rp1 save state;
Pluggable database altered.
Elapsed: 00:00:00.16
[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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[tdb02t>][ORA19R_ORA19RP1@SYS]$ create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';
Directory created.
Elapsed: 00:00:00.41
[tdb02t>][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@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 23 14:25:02 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "TUNER"."SYS_EXPORT_TABLE_01":
tuner/********@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"
123.5 MB 1000000 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 23 14:27:45 2025 elapsed 0 00:02:36
[ORA19R:oracle@tdb02t][/home/oracle/ORA19R_BACKUP]$ ls -l
/home/oracle/ORA19R_BACKUP/datapump
total 126708
-rw-r-----. 1 oracle asmadmin 129744896 Aug 21 19:58
tuner_tb_cust.dmp
-rw-r--r--. 1 oracle
asmadmin 1338 Aug 21 19:58
tuner_tb_cust.log
[ORA19R:oracle@tdb02t][/home/oracle]$ ssp1
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Aug 23 14:28:22 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[tdb02t>][ORA19R_ORA19RP1@SYS]$ drop directory tuner_datapump_dir;
Directory dropped.
Elapsed: 00:00:00.45
26. 타켓에서 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% 124MB 9.1MB/s 00:13
tuner_tb_cust.log
100% 1338 106.8KB/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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[ol7ora19r1][SYS@ORA19R_ORA19RP1]$
[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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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"
123.5 MB 1000000 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 23 14:31:27 2025
elapsed 0 00:01:21
27. 최종적으로 소스 서버에서 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
Copyright (c) 1982, 2024, Oracle. All rights reserved.
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(*)
----------
1000000
1 row selected.
Elapsed: 00:00:00.35
[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 된
테이블을 복구해올 수 있음!