[2025-09-08] Oracle 19c RAC CDB환경에서 특정 PDB를 DROP 한 후 백업본으로 부터 복구하는 방법 (자동 방식) (동일 서버에서 백업본으로 복구)
recover pluggable database <pdb명> 기능으로 drop 된 pdb를 복구하는 절차
[실습
환경]
OS : Oracle Linux Server 7.9 (Linux rdb01d
5.4.17-2102.201.3.el7uek.x86_64)
DB : 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
1. 현재 DB 상황 확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
ORA19R1
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 1 21:56:23
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
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd lsdg
State
Type Rebal Sector Logical_Sector
Block AU Total_MB Free_MB
Req_mir_free_MB Usable_file_MB Offline_disks
Voting_files Name
MOUNTED NORMAL
N
512
512 4096 4194304
6132
5096
2044
1526
0
Y CRS/
MOUNTED EXTERN
N
512
512 4096 4194304 131068
119312
0
119312
0
N DATA1/
MOUNTED EXTERN
N
512
512 4096 4194304 131068
129004
0
129004
0
N FRA1/
2. 새로운 PDB 생성
--alert log
모니터링
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ tail -f
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 1 22:06: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
[ol7ora19r1][SYS@ORA19R1]$ CREATE PLUGGABLE DATABASE test_pdb1 admin user pdbadmin IDENTIFIED BY oracle CREATE_FILE_DEST = '+DATA1';< /STRONG >
Pluggable database created.
Elapsed: 00:02:05.72
[ol7ora19r1][SYS@ORA19R1]$ alter pluggable
database test_pdb1 open;
Pluggable database altered.
Elapsed: 00:03:35.42
[ol7ora19r1][SYS@ORA19R1]$ alter pluggable
database test_pdb1 save state;
Pluggable database altered.
Elapsed: 00:00:02.54
[ol7ora19r1][SYS@ORA19R1]$ alter session set
container=test_pdb1;
Session altered.
Elapsed: 00:00:00.69
[ol7ora19r1][SYS@ORA19R1]$ GRANT DBA TO
PDBADMIN;
Grant succeeded.
Elapsed: 00:00:03.64
[ol7ora19r1][SYS@ORA19R1]$
quit
Disconnected from
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version
19.27.0.0.0
--rac2번에서도 신규로 생성한 test_pdb1을
open 시킴
[ORA19R2:oracle@ol7ora19r2][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 3 00:21:21
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
[ol7ora19r2][SYS@ORA19R2]$ alter
pluggable database TEST_PDB1 open;
Pluggable database altered.
Elapsed: 00:02:32.49
[ol7ora19r2][SYS@ORA19R2]$ alter pluggable
database TEST_PDB1 save state;
Pluggable
database altered.
--> RAC2번 노드에도 신규 생성한 PDB를 OPEN해줘야 2번노드를 위한 Local Undo
Tablespace가 생성됨
3. 신규로 생성한 pdb에 접속한 후 테이블 스페이스 생성 및 테이블
생성 후 데이터 입력
[+ASM1:grid@ol7ora19r1][/home/grid]$
lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-SEP-2025 22:27:38
Copyright (c) 1991, 2025, Oracle. All rights reserved.
<P>Connectingto(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
01-SEP-2025
21:37:53
Uptime
0 days 0 hr. 49 min. 45 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/ol7ora19r1/listener/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.24)(PORT=1521)))
Services
Summary...
...중간 생략
Service "test_pdb1" has 1 instance(s).
Instance "ORA19R1", status READY, has 1 handler(s) for this service...
The command completed
successfully
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cat
/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# tnsnames.ora
Network Configuration File:
/u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration
tools.
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_TEST_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 =
TEST_PDB1)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
)
)
...생략
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ vi
.bash_profile
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ .
./.bash_profile
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
tp1a
alias
tp1a='rlwrap sqlplus < /STRONG> pdbadmin/oracle@ORA19R_TEST_PDB1'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ tp1a
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 1 22:32:35
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][PDBADMIN@ORA19R_TEST_PDB1]$ show con_name
CON_NAME
------------------------------
TEST_PDB1
--DROP USER TUNER CASCADE;
CREATE USER TUNER IDENTIFIED BY
"oracle";
GRANT RESOURCE, DBA, CONNECT TO TUNER;
ALTER USER TUNER
ACCOUNT UNLOCK;
--DROP TABLESPACE TUNER_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE TUNER_DATA1
DATAFILE '+DATA1' SIZE 100M
AUTOEXTEND ON NEXT 512M
MAXSIZE UNLIMITED
;
--DROP TABLESPACE TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE TUNER_IDX1
DATAFILE '+DATA1' SIZE 100M
AUTOEXTEND ON NEXT 512M
MAXSIZE UNLIMITED
;
--DROP TABLESPACE TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE
TEMPORARY TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1' SIZE 64M
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED
;
ALTER USER TUNER DEFAULT TABLESPACE TUNER_DATA1;
ALTER USER TUNER
TEMPORARY TABLESPACE TUNER_TEMP;
--DROP TABLE TUNER.TB_CUST PURGE;
CREATE TABLE
TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID
VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_ID IS '고객아이디';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NM IS
'고객명';
COMMENT ON COLUMN TUNER.TB_CUST.BRTHDY IS '생년월일';
COMMENT ON COLUMN
TUNER.TB_CUST.SEX_CD IS '성별코드';
COMMENT ON COLUMN TUNER.TB_CUST.JOIN_DT IS
'가입일시';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT
ON COLUMN TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_DT IS '입력일시';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_ID IS
'수정아이디';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON TABLE
TUNER.TB_CUST IS '고객';
ALTER TABLE TUNER.TB_CUST NOLOGGING;
--데이터 입력
INSERT /*+ APPEND */
INTO TUNER.TB_CUST
SELECT 'C'
|| LPAD(ROWNUM, 9, '0') AS CUST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80),
'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2),
4, '0') AS SEX_CD
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <= 100000;
COMMIT;
CREATE UNIQUE INDEX TUNER.PK_TB_CUST ON TUNER.TB_CUST(CUST_NO) TABLESPACE
TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_CUST PARALLEL
1;
ALTER INDEX TUNER.PK_TB_CUST LOGGING;
ALTER TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST PRIMARY
KEY (CUST_NO);
CREATE INDEX TUNER.IDX_TB_CUST_01 ON TUNER.TB_CUST(CUST_STS_CD) TABLESPACE
TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_01 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_CUST_01 LOGGING;
CREATE INDEX TUNER.IDX_TB_CUST_02 ON TUNER.TB_CUST(BRTHDY) TABLESPACE
TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_02 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_CUST_02 LOGGING;
CREATE INDEX TUNER.IDX_TB_CUST_03 ON TUNER.TB_CUST(CUST_STS_CD, BRTHDY)
TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03
PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_CUST_03 LOGGING;
--다시 LOGGING으로 설정
(중요!)
ALTER TABLE TUNER.TB_CUST
LOGGING;
--통계 정보 생성
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
[ol7ora19r1][PDBADMIN@ORA19R_TEST_PDB1]$ select count(*) from
tuner.tb_cust;
COUNT(*)
----------
100000
1 row selected.
Elapsed: 00:00:00.01
4. 풀 백업 작업 수행
--소스에서 RMAN BACKUP RUN 스크립트를 생성하는 SQL문 실행
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 1 22:58: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
[ol7ora19r1][SYS@ORA19R1]$
col backup_script for a150
set linesize
200
SELECT 'RUN {' || CHR(10) ||
'sql ''alter system archive log
current'';' || CHR(10) ||
'sql ''alter system checkpoint'';' || CHR(10)
||
'crosscheck backupset;' || CHR(10) ||
'crosscheck backup;' || CHR(10)
||
'crosscheck copy;' || CHR(10) ||
'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;' || CHR(10) ||
'delete noprompt
expired backup;' || CHR(10) ||
'}' AS BACKUP_SCRIPT
FROM DUAL
;
--결과
BACKUP_SCRIPT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE PDB1:UNDO_2 FORMAT
'/home/oracle/ORA19R_BACKUP/PDB1_UNDO_2_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:SYSAUX FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:SYSTEM FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:TUNER_DATA1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE TEST_PDB1:TUNER_IDX1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE TEST_PDB1:UNDOTBS1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:UNDO_2 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_%U.bkp';
delete noprompt
obsolete;
delete noprompt expired backup;
}
Elapsed: 00:00:00.17
--> 이걸로 테이블 스페이스 단위로 백업을 할 것임
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -pv
/home/oracle/ORA19R_BACKUP/autobackup
mkdir: created directory
‘/home/oracle/ORA19R_BACKUP’
mkdir: created directory
‘/home/oracle/ORA19R_BACKUP/autobackup’
[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'<
/STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias
rt
alias rt='rman target
/'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
rt
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 2 23:59:16
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)< /STRONG>
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';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE PDB1:UNDO_2 FORMAT
'/home/oracle/ORA19R_BACKUP/PDB1_UNDO_2_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:SYSAUX FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:SYSTEM FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:TUNER_DATA1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE TEST_PDB1:TUNER_IDX1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE TEST_PDB1:UNDOTBS1 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TEST_PDB1:UNDO_2 FORMAT
'/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_%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 백업
파일 확인
RMAN> host 'ls -lRa
/home/oracle/ORA19R_BACKUP/';
host 'ls -lRa
/home/oracle/ORA19R_BACKUP/';
/home/oracle/ORA19R_BACKUP/:
total
2272580
drwxr-xr-x. 3 oracle oinstall
4096 Sep 7 21:42 .
drwxrwxr-x. 10 oracle
oinstall 4096 Sep 7 21:32
..
-rw-r-----. 1 oracle asmadmin 539136 Sep 7
21:42 ARCHIVE_ORA19R_20250907_kq433t25_s666_p1
drwxr-xr-x. 2 oracle
oinstall 70 Sep 7 21:42
autobackup
-rw-r-----. 1 oracle asmadmin 246300672 Sep 7 21:34
CDB_SYSAUX_ju433sik_638_1_1.bkp
-rw-r-----. 1 oracle asmadmin 460931072
Sep 7 21:35 CDB_SYSTEM_jv433sjp_639_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1179648 Sep 7 21:35
CDB_UNDOTBS1_k0433slh_640_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1196032 Sep 7 21:35
CDB_UNDOTBS2_k1433slj_641_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Sep 7 21:35
CDB_USERS_k2433sll_642_1_1.bkp
-rw-r-----. 1 oracle asmadmin
87474176 Sep 7 21:36
ORA19RP1_SYSAUX_k3433sln_643_1_1.bkp
-rw-r-----. 1 oracle asmadmin
266870784 Sep 7 21:36
ORA19RP1_SYSTEM_k4433smc_644_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 18735104 Sep 7 21:36
ORA19RP1_TUNER_DATA1_k5433snh_645_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2793472 Sep 7 21:36
ORA19RP1_TUNER_IDX1_k6433snl_646_1_1.bkp
-rw-r-----. 1 oracle asmadmin
103940096 Sep 7 21:37
ORA19RP1_UNDO_2_k8433snr_648_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1253376 Sep 7 21:36
ORA19RP1_UNDOTBS1_k7433snq_647_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1073152 Sep 7 21:37
ORA19RP1_USERS_k9433soc_649_1_1.bkp
-rw-r-----. 1 oracle asmadmin
72900608 Sep 7 21:38 PDB1_SYSAUX_kd433sqd_653_1_1.bkp
-rw-r-----.
1 oracle asmadmin 266952704 Sep 7 21:39
PDB1_SYSTEM_ke433sqt_654_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1449984 Sep 7 21:39
PDB1_TUNER_DATA1_kf433ss1_655_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1351680 Sep 7 21:39
PDB1_TUNER_IDX1_kg433ssa_656_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1368064 Sep 7 21:39
PDB1_UNDO_2_ki433ssi_658_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2424832 Sep 7 21:39
PDB1_UNDOTBS1_kh433ssd_657_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64561152 Sep 7 21:37
PDBSEED_SYSAUX_ka433soe_650_1_1.bkp
-rw-r-----. 1 oracle asmadmin
265166848 Sep 7 21:37
PDBSEED_SYSTEM_kb433son_651_1_1.bkp
-rw-r-----. 1 oracle asmadmin
85991424 Sep 7 21:38
PDBSEED_UNDOTBS1_kc433sps_652_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 20365312 Sep 7 21:42 snapcf_CA.f
-rw-r-----. 1
oracle asmadmin 71942144 Sep 7 21:39
TEST_PDB1_SYSAUX_kj433ssj_659_1_1.bkp
-rw-r-----. 1 oracle asmadmin
266469376 Sep 7 21:40
TEST_PDB1_SYSTEM_kk433st3_660_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 7888896 Sep 7 21:40
TEST_PDB1_TUNER_DATA1_kl433su7_661_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2539520 Sep 7 21:40
TEST_PDB1_TUNER_IDX1_km433suc_662_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1105920 Sep 7 21:40
TEST_PDB1_UNDO_2_ko433sui_664_1_1.bkp
-rw-r-----. 1 oracle asmadmin 1097728
Sep 7 21:40 TEST_PDB1_UNDOTBS1_kn433sug_663_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 40004
drwxr-xr-x. 2 oracle
oinstall 70 Sep 7 21:42
.
drwxr-xr-x. 3 oracle oinstall 4096 Sep 7
21:42 ..
-rw-r-----. 1 oracle asmadmin 20480000 Sep 7 21:40
c-1831232271-20250907-00
-rw-r-----. 1 oracle asmadmin 20480000 Sep 7
21:42 c-1831232271-20250907-01
host command
complete
5. drop pdb하는 사고가 발생함!
--RAC 1번
노드 test_pdb1 내리기
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 4 20:43: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]$ 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
5
TEST_PDB1
READ WRITE NO
[ol7ora19r1][SYS@ORA19R1]$ alter pluggable
database test_pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:28.40
--RAC 2번
노드 test_pdb1 내리기
[ORA19R2:oracle@ol7ora19r2][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'< /STRONG>
[ORA19R2:oracle@ol7ora19r2][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 4 20:44:18
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
[ol7ora19r2][SYS@ORA19R2]$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol7ora19r2][SYS@ORA19R2]$ 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
5
TEST_PDB1
READ WRITE NO
[ol7ora19r2][SYS@ORA19R2]$ alter pluggable
database test_pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:36.32
--RAC1번
노드에서 drop pluggable database
[ol7ora19r1][SYS@ORA19R1]$ drop pluggable database test_pdb1 including
datafiles;
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias valog
alias valog='vi -R
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'<
/STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ valog
2025-09-07T21:54:50.306072+09:00
drop pluggable database test_pdb1
including datafiles
2025-09-07T21:54:52.844887+09:00
Deleted Oracle
managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undo_2.304.1211146757
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_idx1.306.1211146757
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_data1.305.1211146755
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undotbs1.302.1211146757
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/sysaux.301.1211146757
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/system.303.1211146757
2025-09-07T21:54:53.736518+09:00
Stopped
service test_pdb1
2025-09-07T21:54:54.092771+09:00
Completed: drop pluggable database test_pdb1 including
datafiles
--2025-09-07T21:54:50.306072+09:00 이 시간대에는 test_pdb1이 존재했었음
6. PDB 복구 (recover pluggable database 기능으로 자동 복구)
[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
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -pv
/home/oracle/AUX
mkdir: created directory
‘/home/oracle/AUX’
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ export
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias rt
alias rt='rman target /'< /STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ rt
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 7 21:58:05
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)< /STRONG>
RMAN> report
schema;
report schema;
Report of database schema for database with db_unique_name
ORA19R
List of Permanent Datafiles
===========================
File Size(MB)
Tablespace RB segs
Datafile Name
---- -------- -------------------- -------
------------------------
1 1160
SYSTEM
YES
+DATA1/ORA19R/DATAFILE/system.279.1211140473
3
1820
SYSAUX
NO
+DATA1/ORA19R/DATAFILE/sysaux.259.1211140427
4
795
UNDOTBS1
YES
+DATA1/ORA19R/DATAFILE/undotbs1.280.1211140519
5
480 PDB$SEED:SYSTEM
NO
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/system.257.1211140609
6
460 PDB$SEED:SYSAUX
NO
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/sysaux.272.1211140593
7
5
USERS
NO
+DATA1/ORA19R/DATAFILE/users.282.1211140535
8
280 PDB$SEED:UNDOTBS1
NO
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/undotbs1.258.1211140633
9
50
UNDOTBS2
YES
+DATA1/ORA19R/DATAFILE/undotbs2.281.1211140533
10
490 ORA19RP1:SYSTEM
YES
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/system.268.1211140551
11
690 ORA19RP1:SYSAUX
NO
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/sysaux.283.1211140535
12
280 ORA19RP1:UNDOTBS1
YES
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undotbs1.262.1211140583
13
680 ORA19RP1:UNDO_2
YES
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undo_2.256.1211140585
14
48
ORA19RP1:USERS
NO
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/users.278.1211140593
25
490
PDB1:SYSTEM
YES
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/system.311.1211140663
26
540
PDB1:SYSAUX
NO
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/sysaux.312.1211140649
27
280
PDB1:UNDOTBS1
YES
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undotbs1.308.1211140717
30
1124 PDB1:TUNER_DATA1
NO
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_data1.310.1211140699
31
100 PDB1:TUNER_IDX1
NO
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_idx1.309.1211140715
38
612 ORA19RP1:TUNER_DATA1
NO
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_data1.260.1211140577
39
100 ORA19RP1:TUNER_IDX1
NO
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_idx1.261.1211140579
46 280
PDB1:UNDO_2
YES
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undo_2.307.1211140725
List of Temporary Files
=======================
File Size(MB)
Tablespace
Maxsize(MB) Tempfile Name
---- -------- -------------------- -----------
--------------------
1
71
TEMP
32767
+DATA1/ORA19R/TEMPFILE/temp.299.1211141021
2
36
PDB$SEED:TEMP
32767
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/TEMPFILE/temp.286.1211141031
3
201
ORA19RP1:TEMP
32767
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/TEMPFILE/temp.290.1211141045
4
64 ORA19RP1:TUNER_TEMP
32767
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/TEMPFILE/tuner_temp.291.1211141047
5
201
PDB1:TEMP
32767
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/TEMPFILE/temp.287.1211141041
6 100
PDB1:TUNER_TEMP
32767
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/TEMPFILE/tuner_temp.289.1211141043
RMAN> recover pluggable database
test_pdb1 until time "to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/home/oracle/AUX/';
recover pluggable database test_pdb1 until time
"to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')" auxiliary destination
'/home/oracle/AUX/';
Starting recover at 2025-09-07 22:01:39
current log
archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=105
instance=ORA19R1 device type=DISK
RMAN-05026: warning: presuming following set
of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace
SYSTEM
Tablespace UNDOTBS1
Tablespace
UNDOTBS2
Creating automatic instance, with SID='ohEx'< /STRONG>
initialization parameters used for automatic
instance:
db_name=ORA19R
db_unique_name=ohEx_pitr_test_pdb1_ORA19R
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=4800M
processes=200
db_create_file_dest=/home/oracle/AUX/
log_archive_dest_1='location=/home/oracle/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No
auxiliary parameter file used
starting up automatic instance
ORA19R
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of recover command at 09/07/2025 22:03:53
RMAN-04014: startup failed:
ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No
space left on device
Additional information: 4659
Additional information:
5016387584
RMAN Client Diagnostic Trace file :
/u01/app/oracle/diag/clients/user_oracle/RMAN_3308332144_110/trace/ora_rman_2670_0.trc
--기본적으로 sga_target을 4.8기가를 잡게 되어 있음
--현재 DB의 메모리가 부족할 경우 실패하게 됨
--아래와
같이 파라미터 파일을 생성해 놓음
[ORA19R1:oracle@ol7ora19r1][/home/oracle/AUX]$
cat /home/oracle/AUX/initAUX.ora
db_name=ORA19R
db_unique_name=ohEx_pitr_test_pdb1_ORA19R
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=768M
processes=200
db_create_file_dest=/home/oracle/AUX/
log_archive_dest_1='location=/home/oracle/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
--> SGA_TARGET을 768M으로만 했음
--돌리기전에
AUX 인스턴스 관련 파일 생기는거 모니터링
[ORA19R1:oracle@ol7ora19r1][/home/oracle/AUX]$ while true; do ls -lRart
/home/oracle/AUX; sleep 3; echo ""; echo
"--------------------------------------------------"; done
--> 파일이 생기는 현황을 모니터링
할 수 있다.
--다시 시도
RMAN>
run {
SET AUXILIARY
INSTANCE PARAMETER FILE TO '/home/oracle/AUX/initAUX.ora';
recover pluggable database test_pdb1 until
time "to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')" auxiliary
destination '/home/oracle/AUX/';
}
executing command: SET auxiliary parameter file
Starting recover at 2025-09-07 22:18:02
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace
SYSTEM
Tablespace UNDOTBS1
Tablespace
UNDOTBS2
Creating automatic instance, with SID='iald'
using contents of file
/home/oracle/AUX/initAUX.ora
initialization parameters used for automatic
instance:
db_name=ORA19R
db_unique_name=iald_pitr_test_pdb1_ORA19R
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=4800M
processes=200
db_create_file_dest=/home/oracle/AUX/
log_archive_dest_1='location=/home/oracle/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
ifile=/home/oracle/AUX/initAUX.ora
starting up automatic instance
ORA19R
Oracle instance started
Total System Global Area 805305952 bytes
Fixed
Size
9182816 bytes
Variable
Size
251658240 bytes
Database
Buffers
536870912 bytes
Redo
Buffers
7593984 bytes
Automatic instance
created
contents of Memory Script:
{
# set requested point in time
set until
time "to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')";
#
restore the controlfile
restore clone
controlfile;
# mount the controlfile
sql clone 'alter
database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2025-09-07 22:18:48
allocated channel:
ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=225 device
type=DISK < /STRONG>
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250907-01
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250907-01
tag=TAG20250907T214231
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file
name=/home/oracle/AUX/ORA19R/controlfile/o1_mf_ncv1kt2y_.ctl<
/FONT>
Finished restore at 2025-09-07 22:18:51
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until
time "to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')";
#
set destinations for recovery set and auxiliary set datafiles
set newname for
clone datafile 1 to new;
set newname for clone datafile 4 to
new;
set newname for clone datafile 9 to new;
set newname for clone
datafile 3 to new;
set newname for clone datafile 7 to
new;
set newname for datafile 59 to new;
set newname for
datafile 60 to new;
set newname for datafile 61 to new;
set
newname for datafile 62 to new;
set newname for datafile 63 to
new;
set newname for datafile 64 to new;
# restore the tablespaces
in the recovery set and the auxiliary set
restore clone datafile 1, 4, 9, 3, 7,
59, 60, 61, 62, 63, 64;
switch clone datafile all;
}
executing Memory
Script
executing command: SET until clause
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-09-07 22:19:08
using channel
ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00003 to
/home/oracle/AUX/ORA19R/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1:
reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_ju433sik_638_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_ju433sik_638_1_1.bkp
tag=TAG20250907T213412
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00001 to
/home/oracle/AUX/ORA19R/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1:
reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_jv433sjp_639_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_jv433sjp_639_1_1.bkp
tag=TAG20250907T213448
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00004 to
/home/oracle/AUX/ORA19R/datafile/o1_mf_undotbs1_%u_.dbf
channel
ORA_AUX_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_k0433slh_640_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_k0433slh_640_1_1.bkp
tag=TAG20250907T213545
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00009 to
/home/oracle/AUX/ORA19R/datafile/o1_mf_undotbs2_%u_.dbf
channel
ORA_AUX_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_k1433slj_641_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_k1433slj_641_1_1.bkp
tag=TAG20250907T213547
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00007 to
/home/oracle/AUX/ORA19R/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1:
reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_USERS_k2433sll_642_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_USERS_k2433sll_642_1_1.bkp
tag=TAG20250907T213548
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00060 to +DATA1
channel ORA_AUX_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_kj433ssj_659_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_kj433ssj_659_1_1.bkp
tag=TAG20250907T213931
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00059 to +DATA1
channel ORA_AUX_DISK_1: reading from
backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_kk433st3_660_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_kk433st3_660_1_1.bkp
tag=TAG20250907T213947
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00062 to +DATA1
channel ORA_AUX_DISK_1: reading from
backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_kl433su7_661_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_kl433su7_661_1_1.bkp
tag=TAG20250907T214023
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00063 to +DATA1
channel ORA_AUX_DISK_1: reading from
backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_km433suc_662_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_km433suc_662_1_1.bkp
tag=TAG20250907T214028
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00061 to +DATA1
channel ORA_AUX_DISK_1: reading from
backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_kn433sug_663_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_kn433sug_663_1_1.bkp
tag=TAG20250907T214032
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1:
restoring datafile 00064 to +DATA1
channel ORA_AUX_DISK_1: reading from
backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_ko433sui_664_1_1.bkp
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_ko433sui_664_1_1.bkp
tag=TAG20250907T214034
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:07
Finished restore at 2025-09-07
22:23:46
datafile 59 switched to datafile copy
input datafile copy RECID=69
STAMP=1211235827 file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/system.301.1211235755
datafile
60 switched to datafile copy
input datafile copy RECID=70 STAMP=1211235827
file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/sysaux.303.1211235731
datafile
61 switched to datafile copy
input datafile copy RECID=71 STAMP=1211235827
file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undotbs1.306.1211235811
datafile
62 switched to datafile copy
input datafile copy RECID=72 STAMP=1211235827
file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_data1.302.1211235801
datafile
63 switched to datafile copy
input datafile copy RECID=73 STAMP=1211235827
file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_idx1.305.1211235807
datafile
64 switched to datafile copy
input datafile copy RECID=74 STAMP=1211235827
file
name=+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undo_2.304.1211235819
datafile
1 switched to datafile copy
input datafile copy RECID=75 STAMP=1211235828
file
name=/home/oracle/AUX/ORA19R/datafile/o1_mf_system_ncv1og3z_.dbf
datafile 4
switched to datafile copy
input datafile copy RECID=76 STAMP=1211235828 file
name=/home/oracle/AUX/ORA19R/datafile/o1_mf_undotbs1_ncv1qj1k_.dbf
datafile 9
switched to datafile copy
input datafile copy RECID=77 STAMP=1211235828 file
name=/home/oracle/AUX/ORA19R/datafile/o1_mf_undotbs2_ncv1qzw6_.dbf
datafile 3
switched to datafile copy
input datafile copy RECID=78 STAMP=1211235828 file
name=/home/oracle/AUX/ORA19R/datafile/o1_mf_sysaux_ncv1lrky_.dbf
datafile 7
switched to datafile copy
input datafile copy RECID=79 STAMP= 1211235828file name=
/home/oracle/AUX/ORA19R/datafile/o1_mf_users_ncv1r0wj_.dbf</FONT> <
/STRONG>
contents of Memory Script:
{
# set requested point in time
set
until time "to_date('2025-09-07 21:54:50','YYYY-MM-DD HH24:MI:SS')";
#
online the datafiles restored or switched
sql clone "alter database
datafile 1 online";
sql clone "alter database datafile 4
online";
sql clone "alter database datafile 9 online";
sql clone
"alter database datafile 3 online";
sql clone "alter database
datafile 7 online";
sql clone 'TEST_PDB1' "alter database
datafile
59 online";
sql clone 'TEST_PDB1' "alter database
datafile
60 online";
sql clone 'TEST_PDB1' "alter database
datafile
61 online";
sql clone 'TEST_PDB1' "alter database
datafile
62 online";
sql clone 'TEST_PDB1' "alter database
datafile
63 online";
sql clone 'TEST_PDB1' "alter database
datafile
64 online";
#recover pdb
recover clone database
tablespace "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX", "USERS" pluggable
database
'TEST_PDB1' delete archivelog;
#open in read write
mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into
temp file
sql clone "alter pluggable database TEST_PDB1 unplug into
''
/u01/app/oracle/product/19c/db_1/dbs/_rm_pdb_pitr_1_iald.xml''";
#create
pdb using temp file of recovered pdb
sql "create pluggable database TEST_PDB1
using ''
/u01/app/oracle/product/19c/db_1/dbs/_rm_pdb_pitr_1_iald.xml''
nocopy tempfile reuse";
alter pluggable database TEST_PDB1
open;
}
executing
Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 7 online
sql statement: alter database datafile 59 online
sql statement: alter database datafile 60 online
sql statement: alter database datafile 61 online
sql statement: alter database datafile 62 online
sql statement: alter database datafile 63 online
sql statement: alter database datafile 64 online
Starting recover at 2025-09-07 22:23:54
using channel
ORA_AUX_DISK_1
Executing: alter database datafile 5, 6, 8 offline
Executing: alter
database datafile 10, 11, 12, 13, 14, 38, 39 offline
Executing: alter
database datafile 25, 26, 27, 30, 31, 46 offline
starting media
recovery
archived log for thread 1 with sequence 17 is already on disk as file
+FRA1/ORA19R/ARCHIVELOG/2025_09_07/thread_1_seq_17.263.1211234511
archived
log for thread 2 with sequence 11 is already on disk as file
+FRA1/ORA19R/ARCHIVELOG/2025_09_07/thread_2_seq_11.267.1211234511
channel
ORA_AUX_DISK_1: starting archived log restore to default destination
channel
ORA_AUX_DISK_1: restoring archived log
archived log thread=1
sequence=16
channel ORA_AUX_DISK_1: restoring archived log
archived log
thread=2 sequence=10
channel ORA_AUX_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250907_kq433t25_s666_p1
channel
ORA_AUX_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_20250907_kq433t25_s666_p1
tag=TAG20250907T214229
channel ORA_AUX_DISK_1: restored backup piece
1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:01
archived log file name=/home/oracle/AUX/1_16_1211141006.dbf thread=1
sequence=16
archived log file name=/home/oracle/AUX/2_10_1211141006.dbf
thread=2 sequence=10
channel clone_default: deleting archived
log(s)
archived log file name=/home/oracle/AUX/1_16_1211141006.dbf RECID=320
STAMP=1211235862
archived log file
name=+FRA1/ORA19R/ARCHIVELOG/2025_09_07/thread_1_seq_17.263.1211234511 thread=1
sequence=17
channel clone_default: deleting archived log(s)
archived log
file name=/home/oracle/AUX/2_10_1211141006.dbf RECID=319
STAMP=1211235862
archived log file
name=+FRA1/ORA19R/ARCHIVELOG/2025_09_07/thread_2_seq_11.267.1211234511 thread=2
sequence=11
media recovery complete, elapsed time: 00:00:06
Finished recover at 2025-09-07
22:24:29
sql statement: alter database open resetlogs
sql statement: alter pluggable database TEST_PDB1 unplug into ''/u01/app/oracle/product/19c/db_1/dbs/_rm_pdb_pitr_1_iald.xml''
sql statement: create pluggable database TEST_PDB1 using ''/u01/app/oracle/product/19c/db_1/dbs/_rm_pdb_pitr_1_iald.xml'' nocopy tempfile reuse
Statement processed
Removing automatic instance
shutting down automatic instance
Oracle
instance shut down
Automatic instance removed
auxiliary instance file
/u01/app/oracle/product/19c/db_1/dbs/_rm_pdb_pitr_1_iald.xml
deleted
auxiliary instance file
/home/oracle/AUX/ORA19R/datafile/o1_mf_sysaux_ncv1lrky_.dbf deleted
auxiliary
instance file /home/oracle/AUX/ORA19R/controlfile/o1_mf_ncv1kt2y_.ctl
deleted
Finished recover at 2025-09-07 22:26:54
7. 복구 완료 후 확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 7 22:31:54
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 pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RP1
READ WRITE NO
4
PDB1
READ WRITE NO
5
TEST_PDB1
READ WRITE NO
[ol7ora19r1][SYS@ORA19R1]$ alter
session set container=test_pdb1;< /STRONG>
Session altered.
Elapsed: 00:00:00.13
[ol7ora19r1][SYS@ORA19R1]$ select count(*)
from tuner.tb_cust;
COUNT(*)
----------
100000
1 row selected.
Elapsed: 00:00:00.21
[ol7ora19r1][SYS@ORA19R1]$
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]$
alias ss
alias ss='rlwrap sqlplus "/as
sysdba"'<
/STRONG>
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 7 22:35:44
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]$
col group# for 999
col thread# for 999
col member for a50
SELECT
A.GROUP#
, B.THREAD#
,
B.SEQUENCE#
, A.MEMBER
,
A.STATUS
, A.TYPE
,
B.BYTES/1024/1024 AS MB
, B.STATUS
FROM
V$LOGFILE A
, V$LOG B
WHERE A.GROUP# =
B.GROUP#
ORDER BY 1
;
GROUP# THREAD# SEQUENCE#
MEMBER
STATUS
TYPE MB
STATUS
------ ------- ----------
-------------------------------------------------- ------- ------- ----------
----------------
1
1 16
+FRA1/ORA19R/ONLINELOG/group_1.302.1211141007
(NULL) ONLINE 200
INACTIVE
2
1 17
+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007
(NULL) ONLINE 200
INACTIVE
3
1 18
+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007
(NULL) ONLINE 200
CURRENT
4
2 10
+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009
(NULL) ONLINE 200
INACTIVE
5
2 11
+FRA1/ORA19R/ONLINELOG/group_5.258.1211141009
(NULL) ONLINE 200
INACTIVE
6
2 12
+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009
(NULL) ONLINE 200
CURRENT
COL CON_ID FOR 999
COL
CON_NAME FOR A15
COL
FILE# FOR 99999
COL
NAME FOR A100
COL
STATUS FOR A10
COL
MB FOR
99999999
SELECT DF.CON_ID
, C.NAME AS
CON_NAME
, DF.FILE#
,
DF.NAME
, DF.STATUS
, DF.BYTES/1024/1024
AS MB
FROM V$DATAFILE DF JOIN V$CONTAINERS C ON DF.CON_ID =
C.CON_ID
ORDER BY DF.CON_ID,
DF.FILE#;
CON_ID CON_NAME FILE#
NAME
STATUS
MB
------ --------------- ------
----------------------------------------------------------------------------------------------------
---------- ---------
1
CDB$ROOT
1
+DATA1/ORA19R/DATAFILE/system.279.1211140473
SYSTEM
1160
1
CDB$ROOT
3
+DATA1/ORA19R/DATAFILE/sysaux.259.1211140427
ONLINE
1830
1
CDB$ROOT
4
+DATA1/ORA19R/DATAFILE/undotbs1.280.1211140519
ONLINE
795
1
CDB$ROOT
7
+DATA1/ORA19R/DATAFILE/users.282.1211140535
ONLINE
5
1
CDB$ROOT
9
+DATA1/ORA19R/DATAFILE/undotbs2.281.1211140533
ONLINE
50
2
PDB$SEED
5
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/system.257.1211140609
SYSTEM
480
2
PDB$SEED
6
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/sysaux.272.1211140593
ONLINE
460
2
PDB$SEED
8
+DATA1/ORA19R/3C641AD3FE4E119BE0631500A8C0DAD4/DATAFILE/undotbs1.258.1211140633
ONLINE
280
3
ORA19RP1 10
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/system.268.1211140551
SYSTEM
500
3
ORA19RP1 11
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/sysaux.283.1211140535
ONLINE
690
3
ORA19RP1 12
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undotbs1.262.1211140583
ONLINE
280
3
ORA19RP1 13
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undo_2.256.1211140585
ONLINE
680
3
ORA19RP1 14
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/users.278.1211140593
ONLINE
49
3
ORA19RP1 38
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_data1.260.1211140577
ONLINE
612
3
ORA19RP1 39
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_idx1.261.1211140579
ONLINE
100
4
PDB1
25
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/system.311.1211140663
SYSTEM
490
4
PDB1
26
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/sysaux.312.1211140649
ONLINE
540
4
PDB1
27
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undotbs1.308.1211140717
ONLINE
280
4
PDB1
30
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_data1.310.1211140699
ONLINE
1124
4
PDB1
31
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_idx1.309.1211140715
ONLINE
100
4
PDB1
46
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undo_2.307.1211140725
ONLINE
280
5
TEST_PDB1 65
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/system.301.1211235755
SYSTEM
490
5
TEST_PDB1 66
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/sysaux.303.1211235731
ONLINE
530
5
TEST_PDB1 67
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undotbs1.306.1211235811
ONLINE
280
5
TEST_PDB1 68
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_data1.302.1211235801
ONLINE
100
5
TEST_PDB1 69
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_idx1.305.1211235807
ONLINE
100
5
TEST_PDB1 70
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undo_2.304.1211235819
ONLINE 280
--> /home/oracle/AUX에 있었던
test_pdb1에 대한 datafile을 모두 ASM영역으로 move 해옴 (정말 제대로
복구해주네!)
8. 마지막으로 aux 관련 파일
날리기
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -lsRt
/home/oracle/AUX
/home/oracle/AUX:
total 4
0 drwxr-x---. 5 oracle
asmadmin 58 Sep 7 22:23 ORA19R
4 -rw-r--r--.
1 oracle oinstall 374 Sep 7 22:16 initAUX.ora
/home/oracle/AUX/ORA19R:
total 8
0 drwxr-x---. 2 oracle
asmadmin 6 Sep 7 22:26 controlfile
4 drwxr-x---. 2
oracle asmadmin 4096 Sep 7 22:26 datafile
4 drwxr-x---. 2 oracle asmadmin 4096 Sep
7 22:24 onlinelog
/home/oracle/AUX/ORA19R/controlfile:
total 0
/home/oracle/AUX/ORA19R/datafile:
total 2058272
51208
-rw-r-----. 1 oracle asmadmin 52436992 Sep 7 22:24
o1_mf_undotbs2_ncv1qzw6_.dbf
5128 -rw-r-----. 1 oracle
asmadmin 5251072 Sep 7 22:24
o1_mf_users_ncv1r0wj_.dbf
814088 -rw-r-----. 1 oracle asmadmin
833626112 Sep 7 22:24 o1_mf_undotbs1_ncv1qj1k_.dbf
1187848 -rw-r-----. 1
oracle asmadmin 1216356352 Sep 7 22:24
o1_mf_system_ncv1og3z_.dbf
/home/oracle/AUX/ORA19R/onlinelog:
total 1228824
204804 -rw-r-----. 1
oracle asmadmin 209715712 Sep 7 22:26 o1_mf_1_ncv1whhm_.log
204804
-rw-r-----. 1 oracle asmadmin 209715712 Sep 7 22:24
o1_mf_3_ncv1wj1d_.log
204804 -rw-r-----. 1 oracle asmadmin 209715712
Sep 7 22:24 o1_mf_2_ncv1whwx_.log
204804 -rw-r-----. 1 oracle asmadmin
209715712 Sep 7 22:24 o1_mf_6_ncv1woor_.log
204804 -rw-r-----. 1 oracle
asmadmin 209715712 Sep 7 22:24 o1_mf_5_ncv1wonl_.log
204804 -rw-r-----. 1 oracle asmadmin 209715712 Sep 7
22:24 o1_mf_4_ncv1wnqv_.log
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ rm -rf /home/oracle/AUX