[2025-09-07] Oracle 19c RAC CDB환경에서 특정 PDB를 DROP 한 후 백업본으로 부터 복구하는 방법 (Manual 방식) (동일 서버에서 백업본으로 복구)
[실습
환경]
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"'
[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"'
[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';
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 시킴 (OPEN 시켜야 RAC 2번이 사용하는 LOCAL UNDO 테이블 스페이스가
생성됨)
[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.
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 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"'
[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'
[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 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)
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 백업 파일
확인
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -lRa
/home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total
2137500
drwxr-xr-x. 3 oracle oinstall
4096 Sep 6 20:31 .
drwxrwxr-x. 10 oracle
oinstall 4096 Sep 6 20:31
..
-rw-r-----. 1 oracle asmadmin 690176 Sep 6
20:31 ARCHIVE_ORA19R_20250906_jm4314hb_s630_p1
drwxr-xr-x. 2 oracle
oinstall 70 Sep 6 20:31
autobackup
-rw-r-----. 1 oracle asmadmin 229720064 Sep 6 20:27
CDB_SYSAUX_iq431485_602_1_1.bkp
-rw-r-----. 1 oracle asmadmin 460578816
Sep 6 20:27 CDB_SYSTEM_ir43148v_603_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1556480 Sep 6 20:27
CDB_UNDOTBS1_is4314ae_604_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1523712 Sep 6 20:28
CDB_UNDOTBS2_it4314ai_605_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Sep 6 20:28
CDB_USERS_iu4314ak_606_1_1.bkp
-rw-r-----. 1 oracle asmadmin
74211328 Sep 6 20:28
ORA19RP1_SYSAUX_iv4314al_607_1_1.bkp
-rw-r-----. 1 oracle asmadmin
266747904 Sep 6 20:28
ORA19RP1_SYSTEM_j04314au_608_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 8085504 Sep 6 20:28
ORA19RP1_TUNER_DATA1_j14314bo_609_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2793472 Sep 6 20:28
ORA19RP1_TUNER_IDX1_j24314bs_610_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2433024 Sep 6 20:28
ORA19RP1_UNDO_2_j44314bv_612_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 6340608 Sep 6 20:28
ORA19RP1_UNDOTBS1_j34314bt_611_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1073152 Sep 6 20:28
ORA19RP1_USERS_j54314c0_613_1_1.bkp
-rw-r-----. 1 oracle asmadmin
72630272 Sep 6 20:29 PDB1_SYSAUX_j94314dc_617_1_1.bkp
-rw-r-----.
1 oracle asmadmin 266936320 Sep 6 20:30
PDB1_SYSTEM_ja4314dk_618_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1458176 Sep 6 20:30
PDB1_TUNER_DATA1_jb4314ee_619_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1351680 Sep 6 20:30
PDB1_TUNER_IDX1_jc4314em_620_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1130496 Sep 6 20:30
PDB1_UNDO_2_je4314eq_622_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1236992 Sep 6 20:30
PDB1_UNDOTBS1_jd4314eo_621_1_1.bkp
-rw-r-----. 1 oracle asmadmin
64561152 Sep 6 20:28
PDBSEED_SYSAUX_j64314c2_614_1_1.bkp
-rw-r-----. 1 oracle asmadmin
265166848 Sep 6 20:29
PDBSEED_SYSTEM_j74314ca_615_1_1.bkp
-rw-r-----. 1 oracle asmadmin
85991424 Sep 6 20:29
PDBSEED_UNDOTBS1_j84314d4_616_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 20365312 Sep 6 20:31 snapcf_CA.f
-rw-r-----. 1
oracle asmadmin 71811072 Sep 6 20:30
TEST_PDB1_SYSAUX_jf4314eu_623_1_1.bkp
-rw-r-----. 1 oracle asmadmin
266362880 Sep 6 20:30
TEST_PDB1_SYSTEM_jg4314fe_624_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 7888896 Sep 6 20:31
TEST_PDB1_TUNER_DATA1_jh4314g7_625_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2539520 Sep 6 20:31
TEST_PDB1_TUNER_IDX1_ji4314g9_626_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1138688 Sep 6 20:31
TEST_PDB1_UNDO_2_jk4314gf_628_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1220608 Sep 6 20:31
TEST_PDB1_UNDOTBS1_jj4314gd_627_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 39940
drwxr-xr-x. 2
oracle oinstall 70 Sep 6 20:31
.
drwxr-xr-x. 3 oracle oinstall 4096 Sep 6
20:31 ..
-rw-r-----. 1 oracle asmadmin 20414464 Sep 6 20:31
c-1831232271-20250906-08
5. drop pdb하는 사고가 발생함!
--RAC 1번 노드 test_pdb1
내리기
[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 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"'
[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'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
valog
...생략
2025-09-06T20:32:48.922534+09:00
drop pluggable database test_pdb1
including datafiles
2025-09-06T20:32:50.310257+09:00
Deleted Oracle
managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undo_2.301.1211140795
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_idx1.303.1211140785
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_data1.304.1211140781
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undotbs1.302.1211140787
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/sysaux.306.1211140731
Deleted
Oracle managed file
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/system.305.1211140747
2025-09-06T20:32:50.714293+09:00
Stopped
service test_pdb1
Completed: drop pluggable database test_pdb1 including
datafiles
--> 2025-09-06T20:32:48.922534+09:00 이 시간대에는 test_pdb1이 존재했었음
6. PDB 복구 시도
[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 Thu Sep 4 20:53:19 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)
executing command: SET until clause
RMAN>
run {
set until time "to_date('2025-09-06 20:32:48', 'yyyy-mm-dd
hh24:mi:ss')";
restore pluggable database test_pdb1;
recover pluggable
database test_pdb1;
}
Starting
restore at 2025-09-06 20:33:27
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of restore command at 09/06/2025 20:33:28
ORA-01403: no data
found
RMAN Client Diagnostic Trace file :
/u01/app/oracle/diag/clients/user_oracle/RMAN_3308332144_110/trace/ora_rman_4550_2.trc
--복구
실패
RMAN> restore pluggable database test_pdb1;
Starting
restore at 2025-09-06 20:33:39
using channel ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of restore command at 09/06/2025 20:33:39
RMAN-06813: could not
translate pluggable database TEST_PDB1
--restore 작업도 실패
--이미 컨트롤 파일
자체에서 test_pdb1 이 사라진 상태임
--이런 방식으로는 복구 불가인 상태임
--recover
pluggable database test_pdb1 방식으로 하면 됨
--근데 해당 방식은 자동으로 해주는 것임, 이번 실습은 이러한
상황에서 수동으로 복구하는 것임
[ol7ora19r1][SYS@ORA19R1]$
SELECT
TIMESTAMP_TO_SCN(to_date('2025-09-06 20:32:48', 'yyyy-mm-dd hh24:mi:ss')) as
target_scn from dual;
TARGET_SCN
----------
7342707
1 row selected.
Elapsed: 00:00:00.20
[ol7ora19r1][SYS@ORA19R1]$
col name for a80
select NAME, THREAD#,
SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# from v$archived_log where 7342707 between
FIRST_CHANGE# and NEXT_CHANGE#;
no rows selected
Elapsed: 00:00:00.02
--> 아카이브 파일이 없음
[ol7ora19r1][SYS@ORA19R1]$ ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
Elapsed:
00:00:02.55
[ol7ora19r1][SYS@ORA19R1]$
col name for a80
select NAME,
THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# from v$archived_log where
7342707 between FIRST_CHANGE# and NEXT_CHANGE#;
NAME
THREAD# SEQUENCE# FIRST_CHANGE#
NEXT_CHANGE#
--------------------------------------------------------------------------------
---------- ---------- -------------
------------
+FRA1/ORA19R/ARCHIVELOG/2025_09_06/thread_2_seq_4.308.1211142871
2
4 7342198
7343130
+FRA1/ORA19R/ARCHIVELOG/2025_09_06/thread_1_seq_3.305.1211142873
1
3 7342194
7343133
2 rows selected.
Elapsed: 00:00:00.01
--target scn인 7342707 까지 가려면 (불완전 복구하려면) thread 1의 3 번과 thread 2 의 4 번 아카이브 로그가 필요함
RMAN>
LIST ARCHIVELOG FROM SCN 7342707;
List of Archived Log Copies for
database with db_unique_name
ORA19R
=====================================================================
Key Thrd Seq S Low
Time
------- ---- ------- -
-------------------
299 1
3 A 2025-09-06
20:31:37
Name:
+FRA1/ORA19R/ARCHIVELOG/2025_09_06/thread_1_seq_3.305.1211142873
298 2
4 A 2025-09-06
20:31:38
Name:
+FRA1/ORA19R/ARCHIVELOG/2025_09_06/thread_2_seq_4.308.1211142871
--> 7342707 부터 최신까지 완전 복구하려면
thread 1의 3 과 thread 2의 4 번이 필요한 상태임
--> 근데 당연히 최신까지 복구하면 또다시 drop
test_pdb1이 될것임! 이걸 이해하는게 매우 중요함
RMAN>
LIST BACKUP OF ARCHIVELOG FROM SCN 7342707;
specification does not match any backup
in the repository
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 3 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-09-06
20:35:41
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=1 sequence=3 RECID=299
STAMP=1211142872
channel ORA_DISK_1: starting piece 1 at 2025-09-06
20:35:41
channel ORA_DISK_1: finished piece 1 at 2025-09-06 20:35:42
piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t1_s3_set632_p1_20250906.bkp
tag=TAG20250906T203541 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-09-06
20:35:42
Starting
Control File and SPFILE Autobackup at 2025-09-06 20:35:42
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0a
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-09-06
20:35:46
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG SEQUENCE 4 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-09-06 20:36:09
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=4
RECID=298 STAMP=1211142870
channel ORA_DISK_1: starting piece 1 at 2025-09-06
20:36:09
channel ORA_DISK_1: finished piece 1 at 2025-09-06 20:36:10
piece
handle=/home/oracle/ORA19R_BACKUP/ARCHIVE_ORA19R_t2_s4_set634_p1_20250906.bkp
tag=TAG20250906T203609 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-09-06
20:36:10
Starting
Control File and SPFILE Autobackup at 2025-09-06 20:36:10
piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0b
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-09-06
20:36:11
--> 현 시점에서 최신의 컨트롤 파일 백업본은 "/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0b" 임 근데 해당 파일은 drop pdb가 된 이후에 백업받은 것임
--> 그러므로 무턱대고 최신의 콘트롤 파일로 resotre controlfile한다면 drop pdb된 내역을 restore 시키지 못하기 때문에 복구안됨
--> 이걸 이해하는게 중요! 어떤 컨트롤 파일을 써야하는가?
7.
pfile 생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -pv
/home/oracle/AUX
mkdir: created directory
‘/home/oracle/AUX’
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
ORA19R1
[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 Sat Sep 6 18:12:55 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/AUX/initaux.ora' from spfile;
File created.
Elapsed: 00:00:00.52
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cat
/home/oracle/AUX/initaux.ora
ORA19R1.__data_transfer_cache_size=0
ORA19R2.__data_transfer_cache_size=0
ORA19R1.__db_cache_size=2768240640
ORA19R2.__db_cache_size=3472883712
ORA19R1.__inmemory_ext_roarea=0
ORA19R2.__inmemory_ext_roarea=0
ORA19R1.__inmemory_ext_rwarea=0
ORA19R2.__inmemory_ext_rwarea=0
ORA19R1.__java_pool_size=0
ORA19R2.__java_pool_size=0
ORA19R1.__large_pool_size=50331648
ORA19R2.__large_pool_size=50331648
ORA19R1.__oracle_base='/u01/app/oracle'#ORACLE_BASE
set from environment
ORA19R2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set
from
environment
ORA19R1.__pga_aggregate_target=1694498816
ORA19R2.__pga_aggregate_target=1694498816
ORA19R1.__sga_target=5033164800
ORA19R2.__sga_target=5033164800
ORA19R1.__shared_io_pool_size=134217728
ORA19R2.__shared_io_pool_size=134217728
ORA19R1.__shared_pool_size=2030043136
ORA19R2.__shared_pool_size=1358954496
ORA19R1.__streams_pool_size=33554432
ORA19R2.__streams_pool_size=0
ORA19R1.__unified_pga_pool_size=0
ORA19R2.__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.1211140417'#Restore
Controlfile
*.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'
[+ASM1:grid@ol7ora19r1][/home/grid]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-SEP-2025 20:37:50
Copyright (c) 1991, 2025, Oracle. All rights reserved.
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
06-SEP-2025
19:37:58
Uptime
0 days 0 hr. 59 min. 52 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)))
--> aux 인스턴의 local_listener 파라미터를 이걸로 세팅할 것임
Services Summary...
Service "+APX"
has 1 instance(s).
Instance "+APX1", status
READY, has 1 handler(s) for this service...
Service
"+ASM" has 1 instance(s).
Instance "+ASM1",
status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for
this service...
Service "+ASM_DATA1" has 1
instance(s).
Instance "+ASM1", status READY, has
1 handler(s) for this service...
Service "+ASM_FRA1" has
1 instance(s).
Instance "+ASM1", status READY,
has 1 handler(s) for this service...
Service
"3c64582fc4c307c3e0631500a8c094d4" has 1 instance(s).
Instance "ORA19R1", status READY, has 1 handler(s)
for this service...
Service
"3cef724be3d726e1e0631500a8c0ea84" has 1 instance(s).
Instance "ORA19R1", status READY, has 1 handler(s)
for this service...
Service "ORA19R" has 1
instance(s).
Instance "ORA19R1", status READY,
has 1 handler(s) for this service...
Service "ORA19RXDB"
has 1 instance(s).
Instance "ORA19R1", status
READY, has 1 handler(s) for this service...
Service
"ora19rp1" has 1 instance(s).
Instance "ORA19R1",
status READY, has 1 handler(s) for this service...
Service "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]$ cp
/home/oracle/AUX/initaux.ora
/home/oracle/AUX/initaux.ora.bak
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ vi
/home/oracle/AUX/initaux.ora
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ cat
/home/oracle/AUX/initaux.ora
*.audit_file_dest='/u01/app/oracle/admin/AUX/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/AUX/controlfile/controlfile01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/AUX/datafile'
*.db_create_online_log_dest_1='/home/oracle/AUX/onlinelog'
*.db_name='ORA19R'
*.db_unique_name=AUX
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
*.log_archive_dest_1='LOCATION=/home/oracle/AUX/archivedlog'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.24)(PORT=1521)))'
--> audit_file_dest, cluster_database, control_files,
db_create_file_dest, db_create_online_log_dest_1, db_unique_name,
log_archive_dest_1, pga_aggregate_target, sga_target, undo_tablespace,
local_listener
--복구 시 필요한 디렉토리 생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p
/u01/app/oracle/admin/AUX/adump
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p
/home/oracle/AUX/controlfile
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p
/home/oracle/AUX/datafile
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p
/home/oracle/AUX/onlinelog
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ mkdir -p
/home/oracle/AUX/archivedlog
8. nomount로 aux 올림
--ORACLE_SID를 aux로
지정해줌
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
export ORACLE_SID=aux
[aux:oracle@ol7ora19r1][/home/oracle]$ echo $ORACLE_SID
aux
[aux:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[aux:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Thu Sep 4 22:32:50 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
[ol7ora19r1][SYS@aux]$ startup nomount
pfile='/home/oracle/AUX/initaux.ora';
ORACLE instance
started.
Total System
Global Area 805306192 bytes
Fixed
Size
9183056 bytes
Variable
Size
268435456 bytes
Database
Buffers 520093696 bytes
Redo
Buffers
7593984 bytes
[ol7ora19r1][SYS@aux]$ create spfile from
pfile='/home/oracle/AUX/initaux.ora';
File created.
Elapsed: 00:00:00.09
[ol7ora19r1][SYS@aux]$ quit
Disconnected from
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[aux:oracle@ol7ora19r1][/home/oracle]$ ls -l
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
-rw-r-----. 1
oracle asmadmin 2560 Sep 6 20:43
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
echo $ORACLE_SID
aux
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Thu Sep 4 22:41:45 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
--nomount로 다시 올린 후 spfile 을
인식하면서 올라오는지 확인
[ol7ora19r1][SYS@aux]$ startup nomount
force
ORACLE instance
started.
Total System
Global Area 805306192 bytes
Fixed
Size
9183056 bytes
Variable
Size
268435456 bytes
Database
Buffers 520093696 bytes
Redo
Buffers
7593984 bytes
[ol7ora19r1][SYS@aux]$ show parameter spfile;
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
spfile
string
/u01/app/oracle/product/19c/db
_1/dbs/spfileaux.ora
9. skip할 테이블 스페이스 출력 (소스 CDB로 접속해서)
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
ORA19R1
[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 Thu Sep 4 23:09:07 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
--PDB중에서 PDB$SEED 는 복구대상에 포함
시켜야함
set linesize 250
col rman_skip_ts
for a200
select
listagg('"'||p.name||'":"'||t.tablespace_name||'"', ',') as rman_skip_ts
from v$pdbs p, cdb_tablespaces t
where p.con_id=t.con_id
and t.contents <> 'TEMPORARY'
and p.name not in ('PDB$SEED')
;
--> CDB$ROOT와 PDB$SEED를 제외하고 모두 skip하는
것임
--> 우리가 복구하고자 하는 test_pdb1은
이미 drop된 상태이기 때문에 어차피 v$pdbs 에서 안나옴
RMAN_SKIP_TS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"ORA19RP1":"SYSTEM","ORA19RP1":"SYSAUX","ORA19RP1":"UNDOTBS1","ORA19RP1":"UNDO_2","ORA19RP1":"USERS","ORA19RP1":"TUNER_DATA1","ORA19RP1":"TUNER_IDX1","PDB1":"SYSTEM","PDB1":"SYSAUX","PDB1":"UNDOTBS1","PDB1":"TUNER_DATA1","PDB1":"TUNER_IDX1","PDB1":"UNDO_2"
10. 소스 db에서 복구 시점(대상)이 되는 컨트롤 파일 백업 본
찾기
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
ORA19R1
[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 Thu Sep 4 23:17: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)
RMAN> list
backup of controlfile summary;
List of Backups
===============
Key TY LV S Device Type Completion
Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- -------
------- ---------- ---
615
B F A DISK 2025-09-06
20:31:13 1
1
NO TAG20250906T203112
617 B F A
DISK 2025-09-06 20:31:42
1 1
NO TAG20250906T203140
619 B F A
DISK 2025-09-06 20:35:44
1 1
NO TAG20250906T203543
621 B F A
DISK 2025-09-06 20:36:11
1 1
NO
TAG20250906T203610
RMAN>
list backup of controlfile;
list backup of controlfile;
List of Backup
Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
615
Full 19.45M
DISK 00:00:01
2025-09-06 20:31:13
BP Key:
615 Status: AVAILABLE Compressed: NO Tag:
TAG20250906T203112
Piece Name:
/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-08
Control File Included: Ckp SCN:
7342123 Ckp time: 2025-09-06
20:31:12
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
617
Full 19.52M
DISK 00:00:02
2025-09-06 20:31:42
BP Key:
617 Status: AVAILABLE Compressed: NO Tag:
TAG20250906T203140
Piece Name:
/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-09
Control File Included: Ckp SCN:
7342217 Ckp time: 2025-09-06 20:31:40
--> 이
시점은 아직 test_pdb1이 drop되지 않은 상황임!!!! 이거 진짜 이해해야 한다.
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
619
Full 19.52M
DISK 00:00:01
2025-09-06 20:35:44
BP Key:
619 Status: AVAILABLE Compressed: NO Tag:
TAG20250906T203543
Piece Name:
/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0a
Control File Included: Ckp SCN:
7343498 Ckp time: 2025-09-06
20:35:43
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
621
Full 19.52M
DISK 00:00:01
2025-09-06 20:36:11
BP Key:
621 Status: AVAILABLE Compressed: NO Tag:
TAG20250906T203610
Piece Name:
/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0b
Control File Included: Ckp SCN:
7343603 Ckp time: 2025-09-06
20:36:10
-->
/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-0b 이게 가장 최신임
--> 근데 가장 최신의 콘트롤 파일을 이미 test_pdb1이 없다고 나오잖아! 그러니 가장 최신의
컨트롤 파일로 복구하면 안되는 것임
--> drop test_pdb1가 되기 바로
직전 시점이 2025-09-06T20:32:48.922534+09:00 임 그전꺼를 restore 시켜야 test_pdb1을 인식함
--> 이걸 이해하는게 매우 중요함
--> 결국
"2025-09-06 20:31:42" 이 시점에 백업 받은 컨트롤 파일인
"/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-09"
선택함
11. rman으로 test_pdb1을 불완전 복구함
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
ORA19R1
[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 Thu Sep 4 23:13: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]$ select dbid from v$database;
DBID
----------
1831232271
1 row selected.
Elapsed: 00:00:00.69
--ORACLE_SID를 aux로 설정 후 rman
접속
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
echo $ORACLE_SID
ORA19R1
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
export ORACLE_SID=aux
[aux:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ echo
$ORACLE_SID
aux
[aux:oracle@ol7ora19r1][/home/oracle]$ alias
rt
alias rt='rman
target /'
[aux:oracle@ol7ora19r1][/home/oracle]$
rt
Recovery Manager:
Release 19.0.0.0.0 - Production on Thu Sep 4 23:14: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)
--ORA19R DB의 dbid로
세팅함 (어차피 restore controlfile 할 것이기 때문에 필수는 아님, 안해도됨)
RMAN> set dbid 1831232271
set dbid
1831232271
executing command: SET DBID
RMAN>
--test_pdb1 을 drop하기 전 시점으로
복구
--CDB$ROOT, PDB$SEED,
TEST_PDB1만 aux 인스턴스로 복구하는 것임
run {
set until time "to_date('2025-09-06 20:32:48', 'yyyy-mm-dd
hh24:mi:ss')";
set newname for database to new;
restore controlfile from
'/home/oracle/ORA19R_BACKUP/autobackup/c-1831232271-20250906-09';
alter database mount;
restore
database root;
restore pluggable database "PDB$SEED";
restore pluggable database TEST_PDB1;
switch datafile all;
switch
tempfile all;
recover database skip forever tablespace
"ORA19RP1":"SYSTEM","ORA19RP1":"SYSAUX","ORA19RP1":"UNDOTBS1","ORA19RP1":"UNDO_2","ORA19RP1":"USERS","ORA19RP1":"TUNER_DATA1","ORA19RP1":"TUNER_IDX1","PDB1":"SYSTEM","PDB1":"SYSAUX","PDB1":"UNDOTBS1","PDB1":"TUNER_DATA1","PDB1":"TUNER_IDX1","PDB1":"UNDO_2";
}
--> 정말 중요 중요 중요!!!! ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--> 여기서 보면 alter database open resetlogs는 안하고 있음!
--> 복구시킨 컨트롤 파일안에는 현재 ORA19R DB의 redo log의 경로 정보가 저장되어 있음
--> 이 상태에서 alter database open resetlos하면 AUX 인스턴스가 ORA19R DB가 현재 사용중인 redo log를 엎어치게 됨
--> 대형 사고가 발생하는 것임, 이래서 동일 서버에서 복구는 왠만하면 안하는게 좋음(그냥 하지 말자. 원격지로 백업본 가져가서 복구하자.)
executing command: SET until clause
executing command: SET NEWNAME
Starting restore
at 2025-09-06 21:00:36
using target database control
file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel ORA_DISK_1: SID=344 device
type=DISK
--컨트롤 파일 복구
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time:
00:00:01
output file
name=/home/oracle/AUX/controlfile/controlfile01.ctl
Finished restore at 2025-09-06
21:00:38
released channel:
ORA_DISK_1
Statement processed
--백업본에서 데이터파일을 restore 시킴
Starting restore
at 2025-09-06 21:00:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=344 device
type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_iq431485_602_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSAUX_iq431485_602_1_1.bkp
tag=TAG20250906T202645
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:45
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_ir43148v_603_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_SYSTEM_ir43148v_603_1_1.bkp
tag=TAG20250906T202711
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:56
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_is4314ae_604_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS1_is4314ae_604_1_1.bkp
tag=TAG20250906T202758
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:07
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_it4314ai_605_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_UNDOTBS2_it4314ai_605_1_1.bkp
tag=TAG20250906T202802
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/CDB_USERS_iu4314ak_606_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/CDB_USERS_iu4314ak_606_1_1.bkp
tag=TAG20250906T202804
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
Finished restore at 2025-09-06
21:02:40
Starting restore
at 2025-09-06 21:02:41
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_j64314c2_614_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSAUX_j64314c2_614_1_1.bkp
tag=TAG20250906T202850
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:15
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_j74314ca_615_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_SYSTEM_j74314ca_615_1_1.bkp
tag=TAG20250906T202858
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_j84314d4_616_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/PDBSEED_UNDOTBS1_j84314d4_616_1_1.bkp
tag=TAG20250906T202924
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:16
Finished restore at 2025-09-06
21:03:38
Starting restore
at 2025-09-06 21:03:38
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00054 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_jf4314eu_623_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSAUX_jf4314eu_623_1_1.bkp
tag=TAG20250906T203021
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:16
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00053 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_jg4314fe_624_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_SYSTEM_jg4314fe_624_1_1.bkp
tag=TAG20250906T203038
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00056 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_jh4314g7_625_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_DATA1_jh4314g7_625_1_1.bkp
tag=TAG20250906T203103
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00057 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_ji4314g9_626_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_TUNER_IDX1_ji4314g9_626_1_1.bkp
tag=TAG20250906T203105
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:04
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00055 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_jj4314gd_627_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDOTBS1_jj4314gd_627_1_1.bkp
tag=TAG20250906T203109
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00058 to
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_%u_.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_jk4314gf_628_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/TEST_PDB1_UNDO_2_jk4314gf_628_1_1.bkp
tag=TAG20250906T203111
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
Finished restore at 2025-09-06
21:04:34
--> 컨트롤 파일에서 resotre 시킨 데이터 파일의 위치를 바꿈
datafile 1 switched to datafile copy
input datafile copy RECID=72 STAMP=1211144675 file
name=/home/oracle/AUX/datafile/AUX/datafile/o1_mf_system_ncr8nzqg_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=73 STAMP=1211144675 file
name=/home/oracle/AUX/datafile/AUX/datafile/o1_mf_sysaux_ncr8mlcz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=74 STAMP=1211144676 file
name=/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs1_ncr8pqo6_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=75 STAMP=1211144676 file
name=/home/oracle/AUX/datafile/AUX/datafile/o1_mf_users_ncr8pzv4_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=76 STAMP=1211144676 file
name=/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs2_ncr8pysq_.dbf
datafile 5 switched to datafile
copy
--> 여기까지가
CDB$ROOT
input datafile
copy RECID=77 STAMP=1211144676 file
name=/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_ncr8qjwt_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=78 STAMP=1211144676 file
name=/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_ncr8q1tp_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=79 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_ncr8rb41_.dbf
datafile 53 switched to datafile
copy
--> 여기까지가 PDB$SEED
input
datafile copy RECID=80 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_ncr8sdh8_.dbf
datafile 54 switched to datafile copy
input datafile copy RECID=81 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_ncr8rvbv_.dbf
datafile 55 switched to datafile copy
input datafile copy RECID=82 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_ncr8td85_.dbf
datafile 56 switched to datafile copy
input datafile copy RECID=83 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_ncr8t5mv_.dbf
datafile 57 switched to datafile copy
input datafile copy RECID=84 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_ncr8t91n_.dbf
datafile 58 switched to datafile copy
input datafile copy RECID=85 STAMP=1211144677 file
name=/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_ncr8thc7_.dbf
--> 여기까지가 test_pdb1
--컨트롤 파일에서 복구 대상 컨테이너 DB의 tempfile에 대한 경로를 바꿈
renamed tempfile 1
to /home/oracle/AUX/datafile/AUX/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_temp_%u_.tmp
in control file
renamed tempfile 3 to
/home/oracle/AUX/datafile/AUX/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_temp_%u_.tmp
in control file
renamed tempfile 4 to
/home/oracle/AUX/datafile/AUX/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_te_%u_.tmp
in control file
renamed tempfile 5 to
/home/oracle/AUX/datafile/AUX/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_temp_%u_.tmp
in control file
renamed tempfile 6 to
/home/oracle/AUX/datafile/AUX/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_te_%u_.tmp
in control file
Starting recover
at 2025-09-06 21:04:42
using channel
ORA_DISK_1
--> skip한 PDB 들의 datafile은 offline drop됨
Executing: alter database datafile 10, 11, 12, 13, 14, 38,
39 offline drop
Executing: alter database datafile 25,
26, 27, 30, 31, 46 offline drop
--> 목표 시점까지 가는 중 online redo에 해당되는 redo가 있어서 그걸 사용함
starting media recovery
archived log for
thread 1 with sequence 2 is already on disk as file
+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007
archived
log for thread 1 with sequence 3 is already on disk as file
+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007
archived
log for thread 2 with sequence 3 is already on disk as file
+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009
archived
log for thread 2 with sequence 4 is already on disk as file
+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009
archived
log file name=+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007 thread=1
sequence=2
archived log file
name=+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009 thread=2 sequence=3
archived log file
name=+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007 thread=1 sequence=3
archived log file
name=+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:03
Finished recover at 2025-09-06 21:04:52
--> 결국 recover 작업을 하는데 thread 1의 2, 3 과 thread 2의 3, 4 이 사용됨
[aux:oracle@ol7ora19r1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Sep 6 21:07:20 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@aux]$
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 1
+FRA1/ORA19R/ONLINELOG/group_1.302.1211141007
(NULL) ONLINE 200
INACTIVE
2
1 2
+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007
(NULL) ONLINE 200
ACTIVE
3
1 3
+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007
(NULL) ONLINE 200
CURRENT
4
2 4
+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009
(NULL) ONLINE 200
CURRENT
5
2 2
+FRA1/ORA19R/ONLINELOG/group_5.258.1211141009
(NULL) ONLINE 200
INACTIVE
6
2 3
+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009
(NULL) ONLINE 200
ACTIVE
--> 여기서 보면 현재 aux 인스턴스의 온라인
redo log가 실제 ORA19R에서 사용하고 있는 온라인 redo log임
--> 즉
이상태에서 alter database open resetlogs하면 정말 큰일나는 것임 (실제 ORA19R DB의 online redo log을
덮어쓰게 되는 것임)
--> 여기서 해결책은 2가지임, rename redo log를 하거나
컨트롤 파일을 재생성하는 것임
--> 실수 예방을 위해서 컨트롤 파일을 재생성하는 것을 권장함
-----------------------------------------------------------------------------컨트롤
파일 재생성으로 한다면,
시작!-----------------------------------------------------------------------
[aux:oracle@ol7ora19r1][/home/oracle]$ export
ORACLE_SID=aux
[aux:oracle@ol7ora19r1][/home/oracle]$
echo $ORACLE_SID
aux
[aux:oracle@ol7ora19r1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[aux:oracle@ol7ora19r1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Fri Sep 5 07:23:59 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@aux]$ alter database backup controlfile to
trace as '/home/oracle/AUX/create_ctrl_aux.sql' resetlogs;
[ol7ora19r1][SYS@aux]$ show parameter spfile;
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
spfile
string
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
[ol7ora19r1][SYS@aux]$ create
pfile='/home/oracle/AUX/initaux_new.ora' from spfile;
[ol7ora19r1][SYS@aux]$ quit
[aux:oracle@ol7ora19r1][/home/oracle]$ vi
/home/oracle/AUX/initaux_new.ora
[aux:oracle@ol7ora19r1][/home/oracle]$ cat
/home/oracle/AUX/initaux_new.ora
*.audit_file_dest='/u01/app/oracle/admin/AUX/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/AUX/controlfile/controlfile01_new.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/AUX/datafile'
*.db_create_online_log_dest_1='/home/oracle/AUX/onlinelog'
*.db_name='AUX'
*.db_unique_name=AUX
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
*.log_archive_dest_1='LOCATION=/home/oracle/AUX/archivedlog'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.24)(PORT=1521)))'
--> control_files를
/home/oracle/AUX/controlfile/controlfile01_new.ctl 지정
--> db_name을 AUX로 변경
aux:oracle@ol7ora19r1][/home/oracle]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Fri Sep 5 07:29:09 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@aux]$ shutdown immediate;
[ol7ora19r1][SYS@aux]$ startup nomount pfile='/home/oracle/AUX/initaux_new.ora';
ORACLE instance started.
...
[ol7ora19r1][SYS@aux]$ quit
Disconnected from
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
mv spfileaux.ora spfileaux.ora.bak
--> 기존
사용하던 aux 인스턴스의 spfile을 백업함
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Fri Sep 5 07:31:43 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@aux]$ create spfile from pfile='/home/oracle/AUX/initaux_new.ora';
[ol7ora19r1][SYS@aux]$ shutdown immediate;
ORA-01507: database not mounted
...
ORACLE instance shut down.
--> 여기서 nomount로 올린 후
spfile을 먹었는지 확인하자!
[ol7ora19r1][SYS@aux]$ startup nomount
ORACLE instance
started.
...
[ol7ora19r1][SYS@aux]$ show parameter spfile;
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
spfile
string
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
cat /home/oracle/AUX/create_ctrl_aux.sql
-- The following
are current System-scope REDO Log Archival related
--
parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="ORA19RAUX"
--
-- LOG_ARCHIVE_CONFIG='SEND,
RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
--
FAL_SERVER=''
--
--
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19c/db_1/dbs/arch'
-- LOG_ARCHIVE_DEST_1='MANDATORY REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE
NOQUOTA_USED NODB_UNIQUE_NAME'
--
LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file
and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups
will
-- be invalidated. Use this only if online logs are
damaged.
-- WARNING! The current control file needs to
be checked against
-- the datafiles to insure it
contains the correct files. The
-- commands printed here
may be missing log and/or data files.
-- Another report
should be made after the database has been
--
successfully opened.
-- After mounting
the created controlfile, the following SQL
-- statement
will place the database in the appropriate
-- protection
mode:
-- ALTER DATABASE SET STANDBY DATABASE TO
MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA19R" RESETLOGS
ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA1/ORA19R/ONLINELOG/group_1.280.1209237351',
'+FRA1/ORA19R/ONLINELOG/group_1.258.1209237353'
)
SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'+DATA1/ORA19R/ONLINELOG/group_2.283.1209237581',
'+FRA1/ORA19R/ONLINELOG/group_2.283.1209237581'
)
SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'+DATA1/ORA19R/ONLINELOG/group_3.273.1209236491',
'+FRA1/ORA19R/ONLINELOG/group_3.297.1209236493'
)
SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_system_ncopfwf9_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_sysaux_ncopcshr_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs1_ncopj88q_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_ncopktq5_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_ncopk1p3_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_users_ncopjrpv_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_ncopm80k_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs2_ncopjqhc_.dbf',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/system.264.1209231643',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/sysaux.265.1209231643',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undotbs1.266.1209231643',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undo_2.277.1209231675',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/users.276.1209231693',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/system.289.1209829079',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/sysaux.290.1209829079',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undotbs1.291.1209829079',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_data1.287.1209829699',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_idx1.286.1209829717',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_data1.270.1210428391',
'+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_idx1.274.1210428401',
'+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undo_2.299.1210811257',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_ncopnr1n_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_ncopmxkx_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_ncoppdws_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_ncopp68z_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_ncopp9r2_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_ncoppjhv_.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN
configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 1');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','OFF');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR
DEVICE TYPE','DISK TO ''/home/oracle/ORA19R_BACKUP/autobackup/%F''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 1
BACKUP TYPE TO BACKUPSET');
-- Configure RMAN
configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DATAFILE BACKUP COPIES FOR DEVICE TYPE','DISK
TO 1');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG BACKUP COPIES FOR DEVICE
TYPE','DISK TO 1');
-- Configure RMAN configuration
record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO NONE');
-- Configure RMAN configuration record 11
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 7
DAYS');
-- Configure RMAN configuration record 12
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('MAXSETSIZE TO','UNLIMITED');
-- Configure RMAN configuration record 13
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','OFF');
-- Configure RMAN configuration record 14
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION
ALGORITHM','''AES128''');
-- Configure RMAN
configuration record 15
VARIABLE RECNO NUMBER;
EXECUTE :RECNO :=
SYS.DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION ALGORITHM','''BASIC'' AS OF
RELEASE ''DEFAULT'' OPTIMIZE FOR LOAD TRUE');
--
Commands to re-create incarnation table
-- Below log
names MUST be changed to existing filenames on
-- disk.
Any one log file from each branch can be used to
--
re-create incarnation records.
-- ALTER DATABASE
REGISTER LOGFILE
'/u01/app/oracle/product/19c/db_1/dbs/arch1_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/product/19c/db_1/dbs/arch1_1_1209229842.dbf';
-- Recovery is required if any of the datafiles are
restored backups,
-- or if the last shutdown was not
normal or immediate.
RECOVER DATABASE USING BACKUP
CONTROLFILE
-- Create log
files for threads other than thread one.
ALTER DATABASE
ADD LOGFILE THREAD 2
GROUP 4 (
'+DATA1/ORA19R/ONLINELOG/group_4.281.1209237371',
'+FRA1/ORA19R/ONLINELOG/group_4.302.1209237373'
)
SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 5 (
'+DATA1/ORA19R/ONLINELOG/group_5.279.1209236513',
'+FRA1/ORA19R/ONLINELOG/group_5.260.1209236513'
)
SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 6 (
'+DATA1/ORA19R/ONLINELOG/group_6.282.1209237467',
'+FRA1/ORA19R/ONLINELOG/group_6.273.1209237469'
)
SIZE 200M BLOCKSIZE 512 REUSE;
-- Database can
now be opened zeroing the online logs.
ALTER DATABASE
OPEN RESETLOGS;
-- Open all the
PDBs.
ALTER PLUGGABLE DATABASE ALL
OPEN;
-- Commands to add
tempfiles to temporary tablespaces.
-- Online tempfiles
have complete space information.
-- Other tempfiles may
require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_temp_%u_.tmp' REUSE;
ALTER SESSION SET CONTAINER = "PDB$SEED";
ALTER TABLESPACE TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_temp_%u_.tmp'
REUSE;
ALTER SESSION SET CONTAINER = "ORA19RP1";
ALTER TABLESPACE TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_temp_%u_.tmp'
REUSE;
ALTER TABLESPACE TUNER_TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C64582FC4C307C3E0631500A8C094D4/datafile/o1_mf_tuner_te_%u_.tmp'
REUSE;
ALTER SESSION SET CONTAINER = "PDB1";
ALTER TABLESPACE TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_temp_%u_.tmp'
REUSE;
ALTER TABLESPACE TUNER_TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3CEF724BE3D726E1E0631500A8C0EA84/datafile/o1_mf_tuner_te_%u_.tmp'
REUSE;
ALTER SESSION SET CONTAINER = "CDB$ROOT";
-- End of tempfile additions.
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$ vi /home/oracle/AUX/create_ctrl_aux.sql
--여기서 아래와 같이 control file 재생성
스크립트를 준비해두자.
CREATE CONTROLFILE SET
DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/AUX/onlinelog/group_1.log'
) SIZE
200M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/AUX/onlinelog/group_2.log'
) SIZE
200M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/AUX/onlinelog/group_3.log'
) SIZE
200M BLOCKSIZE 512
DATAFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_system_ncopfwf9_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_sysaux_ncopcshr_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs1_ncopj88q_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_ncopktq5_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_ncopk1p3_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_users_ncopjrpv_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_ncopm80k_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs2_ncopjqhc_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_ncopnr1n_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_ncopmxkx_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_ncoppdws_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_ncopp68z_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_ncopp9r2_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_ncoppjhv_.dbf'
CHARACTER SET AL32UTF8
;
-->SET DATABASE "AUX" 로 바꾸고
-->redo log 부분에서 파일 경로를 모두 날려주고 (소스 DB가 사용하고 있기 때문에
엎어치면 대형 사고 나는 것임)
--> AUX onlinelog 위치로 지정해주고
--> 복구대상이 아닌 pdb datafile은 모두 삭제
--> 결국 cdb$root, pdb$seed, test_pdb1만 복구하는 것임
--아래와 같이 컨트롤 파일 스크립트를 실행하여 컨트롤
파일 재생성
[ol7ora19r1][SYS@aux]$
CREATE
CONTROLFILE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/AUX/onlinelog/group_1.log'
) SIZE
200M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/AUX/onlinelog/group_2.log'
) SIZE
200M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/AUX/onlinelog/group_3.log'
) SIZE
200M BLOCKSIZE 512
DATAFILE
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_system_ncopfwf9_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_sysaux_ncopcshr_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs1_ncopj88q_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_ncopktq5_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_ncopk1p3_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_users_ncopjrpv_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_ncopm80k_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/datafile/o1_mf_undotbs2_ncopjqhc_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_ncopnr1n_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_ncopmxkx_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_ncoppdws_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_ncopp68z_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_ncopp9r2_.dbf',
'/home/oracle/ORA19R_BACKUP/AUX/ORA19RAUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_ncoppjhv_.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
Elapsed:
00:00:12.17
[ol7ora19r1][SYS@aux]$ alter database open
resetlogs;
-----------------------------------------------------------------------------컨트롤
파일 재생성으로 한다면,
종료!-----------------------------------------------------------------------
------------------------------------------------------------------------------rename redo log file로 한다면, 시작!----------------------------------------------------------------------
--aux 인스턴스에서 아래의 sQL문 실행 해서
rename file 스크립트를 만듦
SELECT A.GROUP#
, B.THREAD#
, B.SEQUENCE#
, A.MEMBER
, A.STATUS
, A.TYPE
, B.BYTES/1024/1024 AS MB
, B.STATUS
, 'alter database rename file ' ||
''''||a.member||'''' || ' to ' ||''''|| '/home/oracle/AUX/onlinelog/'||
'group_'||a.group# ||''''||';' as redo_rename
FROM V$LOGFILE A
, V$LOG B
WHERE A.GROUP# = B.GROUP#
ORDER BY 1;
REDO_RENAME
-----------------------------------------------------------------------------------------------------------------------
alter database rename file
'+FRA1/ORA19R/ONLINELOG/group_1.302.1211141007' to
'/home/oracle/AUX/onlinelog/group_1.log';
alter
database rename file '+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007' to
'/home/oracle/AUX/onlinelog/group_2.log';
alter
database rename file '+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007' to
'/home/oracle/AUX/onlinelog/group_3.log';
alter
database rename file '+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009' to
'/home/oracle/AUX/onlinelog/group_4.log';
alter
database rename file '+FRA1/ORA19R/ONLINELOG/group_5.258.1211141009' to
'/home/oracle/AUX/onlinelog/group_5.log';
alter
database rename file '+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009' to
'/home/oracle/AUX/onlinelog/group_6.log';
[ol7ora19r1][SYS@aux]$ alter database rename file '+FRA1/ORA19R/ONLINELOG/group_1.302.1211141007' to '/home/oracle/AUX/onlinelog/group_1.log';
Database altered.
Elapsed: 00:00:00.18
[ol7ora19r1][SYS@aux]$
alter database rename file '+FRA1/ORA19R/ONLINELOG/group_2.297.1211141007' to
'/home/oracle/AUX/onlinelog/group_2.log';
Database altered.
Elapsed: 00:00:00.07
[ol7ora19r1][SYS@aux]$
alter database rename file '+FRA1/ORA19R/ONLINELOG/group_3.283.1211141007' to
'/home/oracle/AUX/onlinelog/group_3.log';
Database altered.
Elapsed: 00:00:00.06
[ol7ora19r1][SYS@aux]$
alter database rename file '+FRA1/ORA19R/ONLINELOG/group_4.309.1211141009' to
'/home/oracle/AUX/onlinelog/group_4.log';
Database altered.
Elapsed: 00:00:00.02
[ol7ora19r1][SYS@aux]$
alter database rename file '+FRA1/ORA19R/ONLINELOG/group_5.258.1211141009' to
'/home/oracle/AUX/onlinelog/group_5.log';
Database altered.
Elapsed: 00:00:00.03
[ol7ora19r1][SYS@aux]$
alter database rename file '+FRA1/ORA19R/ONLINELOG/group_6.300.1211141009' to
'/home/oracle/AUX/onlinelog/group_6.log';
Database altered.
Elapsed:
00:00:00.03
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 1
/home/oracle/AUX/onlinelog/group_1.log
(NULL) ONLINE 200
INACTIVE
2
1 2
/home/oracle/AUX/onlinelog/group_2.log
(NULL) ONLINE 200
ACTIVE
3
1 3
/home/oracle/AUX/onlinelog/group_3.log
(NULL) ONLINE 200
CURRENT
4
2 4
/home/oracle/AUX/onlinelog/group_4.log
(NULL) ONLINE 200
CURRENT
5
2 2
/home/oracle/AUX/onlinelog/group_5.log
(NULL) ONLINE 200
INACTIVE
6
2 3
/home/oracle/AUX/onlinelog/group_6.log
(NULL) ONLINE 200
ACTIVE
--> ADG인 경우 v$standby_log도 당연히 봐야한다. 중요! 암튼 redolog 엎어치는 실수가 최악의 실수다. 무섭다. 진짜
--> 진짜로 member중 운영중인 DB의 redo log가 있는지 찾아봐야한다. 진짜 없어야 한다. 가급적 컨트롤 파일 재생성으로 가는게 가장 안전하다.
[ol7ora19r1][SYS@aux]$ ALTER DATABASE OPEN RESETLOGS;
[aux:oracle@ol7ora19r1][/home/oracle]$ echo
$ORACLE_SID
aux
[aux:oracle@ol7ora19r1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[aux:oracle@ol7ora19r1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Sep 6 18:56: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@aux]$ show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol7ora19r1][SYS@aux]$ show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RP1
MOUNTED (NULL)
4
PDB1
MOUNTED (NULL)
5
TEST_PDB1
MOUNTED (NULL)
[ol7ora19r1][SYS@aux]$ col name for a50
[ol7ora19r1][SYS@aux]$ select name from
v$controlfile;
NAME
--------------------------------------------------
/home/oracle/AUX/controlfile/controlfile01.ctl
1 row selected.
Elapsed: 00:00:00.00
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
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_system_ncr8nzqg_.dbf
SYSTEM 1160
1
CDB$ROOT
3
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_sysaux_ncr8mlcz_.dbf
ONLINE 1750
1
CDB$ROOT
4
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs1_ncr8pqo6_.dbf
ONLINE 795
1
CDB$ROOT
7
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_users_ncr8pzv4_.dbf
ONLINE
5
1
CDB$ROOT
9
/home/oracle/AUX/datafile/AUX/datafile/o1_mf_undotbs2_ncr8pysq_.dbf
ONLINE 50
2
PDB$SEED
5
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_system_ncr8qjwt_.dbf
SYSTEM 480
2
PDB$SEED
6
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_sysaux_ncr8q1tp_.dbf
ONLINE 460
2
PDB$SEED
8
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile/o1_mf_undotbs1_ncr8rb41_.dbf
ONLINE 280
3
ORA19RP1 10
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/system.268.1211140551
SYSOFF 490
3
ORA19RP1 11
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/sysaux.283.1211140535
OFFLINE 560
3
ORA19RP1 12
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undotbs1.262.1211140583
OFFLINE 280
3
ORA19RP1 13
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undo_2.256.1211140585
OFFLINE 280
3
ORA19RP1 14
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/users.278.1211140593
OFFLINE 49
3
ORA19RP1 38
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_data1.260.1211140577
OFFLINE 100
3
ORA19RP1 39
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_idx1.261.1211140579
OFFLINE 100
4
PDB1
25
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/system.311.1211140663
SYSOFF 490
4
PDB1
26
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/sysaux.312.1211140649
OFFLINE 530
4
PDB1
27
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undotbs1.308.1211140717
OFFLINE 280
4
PDB1
30
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_data1.310.1211140699
OFFLINE 1124
4
PDB1
31
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/tuner_idx1.309.1211140715
OFFLINE 100
4
PDB1
46
+DATA1/ORA19R/3CEF724BE3D726E1E0631500A8C0EA84/DATAFILE/undo_2.307.1211140725
OFFLINE 280
5
TEST_PDB1 53
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_system_ncr8sdh8_.dbf
SYSTEM 490
5
TEST_PDB1 54
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_sysaux_ncr8rvbv_.dbf
ONLINE 530
5
TEST_PDB1 55
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undotbs1_ncr8td85_.dbf
ONLINE 280
5
TEST_PDB1 56
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_da_ncr8t5mv_.dbf
ONLINE 100
5
TEST_PDB1 57
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_tuner_id_ncr8t91n_.dbf
ONLINE 100
5
TEST_PDB1 58
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile/o1_mf_undo_2_ncr8thc7_.dbf
ONLINE 280
-->
ORA19RP1과 PDB1은 모두 offline임
------------------------------------------------------------------------------rename
redo log file로 한다면,
종료!----------------------------------------------------------------------
[ol7ora19r1][SYS@aux]$ alter pluggable database test_pdb1 open;
Pluggable database altered.
Elapsed: 00:00:02.35
[ol7ora19r1][SYS@aux]$ alter session set container=test_pdb1;
Session altered.
Elapsed: 00:00:00.76
[ol7ora19r1][SYS@aux]$
select count(*) from tuner.tb_cust;
COUNT(*)
----------
100000
1 row selected.
Elapsed: 00:00:01.01
--> test_db1 복구 완료 , aux cdb에 복구시킨 test_pdb1을 원래 CDB인 ORA19R CDB에 꽂으면 모든 작업이 완료되는 것임
12. aux CDB의 test_pdb1를 unplug
[ol7ora19r1][SYS@aux]$ alter session set container=CDB$ROOT;
Session altered.
Elapsed: 00:00:00.34
[ol7ora19r1][SYS@aux]$
show con_name
CON_NAME
------------------------------
CDB$ROOT
[ol7ora19r1][SYS@aux]$
show pdbs
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA19RP1
MOUNTED (NULL)
4
PDB1
MOUNTED (NULL)
5
TEST_PDB1
READ WRITE NO
[ol7ora19r1][SYS@aux]$
alter pluggable database TEST_PDB1 close immediate;
Pluggable database altered.
Elapsed: 00:00:14.78
[ol7ora19r1][SYS@aux]$
alter pluggable database test_pdb1 unplug into
'/home/oracle/AUX/test_pdb1.xml';
Pluggable database altered.
Elapsed: 00:01:20.79
[ol7ora19r1][SYS@aux]$
drop pluggable database test_pdb1 keep datafiles;
Pluggable database dropped.
Elapsed: 00:00:34.51
13. ORA19R CDB에 plugin 하기
[ORA19R:oracle@ol7ora19r1][/home/oracle]$ export
ORACLE_SID=ORA19R1
[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 Sep 5 08:17: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
[ol7ora19r1][SYS@ORA19R1]$ create pluggable database
test_pdb1 using '/home/oracle/AUX/test_pdb1.xml' copy;
Pluggable database
created.
Elapsed: 00:02:28.37
--이렇게 하면 datafile 복제 안함
(공간 절약)-----------------------------------------------------
create pluggable
database test_pdb1 using '/home/oracle/AUX/test_pdb1.xml' nocopy tempfile
reuse;
--이렇게 하면 datafile 복제 안함 (공간 절약)-----------------------------------------------------
[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
MOUNTED (NULL)
[ol7ora19r1][SYS@ORA19R1]$ alter pluggable database
test_pdb1 open;
Pluggable database altered.
Elapsed: 00:03:30.31
[ol7ora19r1][SYS@ORA19R1]$ alter session set
container=test_pdb1;
Session altered.
Elapsed: 00:00:00.75
[ol7ora19r1][SYS@ORA19R1]$ select count(*) from
tuner.tb_cust;
COUNT(*)
----------
100000
1 row selected.
Elapsed: 00:00:03.18
[ol7ora19r1][SYS@ORA19R1]$ alter pluggable database
test_pdb1 save state;
Pluggable database altered.
Elapsed: 00:00:04.61
[ol7ora19r1][SYS@ORA19R1]$ show
pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
5
TEST_PDB1
READ WRITE NO
[ol7ora19r1][SYS@ORA19R1]$ alter session set container=CDB$ROOT;
[ol7ora19r1][SYS@ORA19R1]$
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 1750
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 490
3
ORA19RP1 11
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/sysaux.283.1211140535
ONLINE 560
3
ORA19RP1 12
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undotbs1.262.1211140583
ONLINE 280
3
ORA19RP1 13
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/undo_2.256.1211140585
ONLINE 280
3
ORA19RP1 14
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/users.278.1211140593
ONLINE 49
3
ORA19RP1 38
+DATA1/ORA19R/3C64582FC4C307C3E0631500A8C094D4/DATAFILE/tuner_data1.260.1211140577
ONLINE 100
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 530
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 59
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/system.303.1211146757
SYSTEM 490
5
TEST_PDB1 60
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/sysaux.301.1211146757
ONLINE 530
5
TEST_PDB1 61
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undotbs1.302.1211146757
ONLINE 280
5
TEST_PDB1 62
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_data1.305.1211146755
ONLINE 100
5
TEST_PDB1 63
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/tuner_idx1.306.1211146757
ONLINE 100
5
TEST_PDB1 64
+DATA1/ORA19R/3DBE2F936BC21740E0631500A8C0B778/DATAFILE/undo_2.304.1211146757
ONLINE
280
14. 마지막으로 aux CDB 날리기
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ export
ORACLE_SID=aux
[aux:oracle@ol7ora19r1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Fri Sep 5 08:26:03 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@aux]$ shutdown abort
ORACLE instance shut down.
[ol7ora19r1][SYS@aux]$ quit
Disconnected from
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[aux:oracle@ol7ora19r1][/home/oracle/AUX]$ ls -lslR
/home/oracle/AUX
/home/oracle/AUX:
total 24
0 drwxr-xr-x. 2 oracle oinstall 32
Sep 6 21:19 archivedlog
0 drwxr-xr-x. 2
oracle oinstall 31 Sep 6 21:00 controlfile
0 drwxr-xr-x. 3 oracle oinstall 17
Sep 6 21:00 datafile
4 -rw-r--r--. 1 oracle
asmadmin 850 Sep 6 20:39 initaux.ora
4
-rw-r--r--. 1 oracle oinstall 1923 Sep 6 20:38 initaux.ora.bak
4 drwxr-xr-x. 3 oracle oinstall 4096 Sep 6
21:19 onlinelog
12 -rw-r--r--. 1 oracle asmadmin 9758
Sep 6 21:38 test_pdb1.xml
/home/oracle/AUX/archivedlog:
total
4
4 -rw-r-----. 1 oracle asmadmin 1024 Sep 6 21:19
2_1_1211145550.dbf
/home/oracle/AUX/controlfile:
total
19888
19888 -rw-r-----. 1 oracle asmadmin 20365312
Sep 6 21:42 controlfile01.ctl
/home/oracle/AUX/datafile:
total
4
4 drwxr-x---. 7 oracle asmadmin 4096 Sep 6 21:04
AUX
/home/oracle/AUX/datafile/AUX:
total 4
0 drwxr-x---. 3 oracle
asmadmin 22 Sep 6 21:02 3C641AD3FE4E119BE0631500A8C0DAD4
0 drwxr-x---. 3 oracle asmadmin 22 Sep 6
21:04 3C64582FC4C307C3E0631500A8C094D4
0 drwxr-x---. 3
oracle asmadmin 22 Sep 6 21:04
3CEF724BE3D726E1E0631500A8C0EA84
0 drwxr-x---. 3 oracle
asmadmin 22 Sep 6 21:03 3DBE2F936BC21740E0631500A8C0B778
4 drwxr-x---. 2 oracle asmadmin 4096 Sep 6 21:19
datafile
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4:
total 4
4 drwxr-x---. 2 oracle
asmadmin 4096 Sep 6 21:19 datafile
/home/oracle/AUX/datafile/AUX/3C641AD3FE4E119BE0631500A8C0DAD4/datafile:
total 1250328
471048 -rw-r-----. 1
oracle asmadmin 482353152 Sep 6 21:02 o1_mf_sysaux_ncr8q1tp_.dbf
491528 -rw-r-----. 1 oracle asmadmin 503324672 Sep 6
21:03 o1_mf_system_ncr8qjwt_.dbf
1024 -rw-r-----.
1 oracle asmadmin 37756928 Sep 6 21:19 o1_mf_temp_ncr9po0q_.tmp
286728 -rw-r-----. 1 oracle asmadmin 293609472 Sep 6
21:03 o1_mf_undotbs1_ncr8rb41_.dbf
/home/oracle/AUX/datafile/AUX/3C64582FC4C307C3E0631500A8C094D4:
total 0
0 drwxr-x---. 2 oracle
asmadmin 6 Sep 6 21:04 datafile
/home/oracle/AUX/datafile/AUX/3C64582FC4C307C3E0631500A8C094D4/datafile:
total 0
/home/oracle/AUX/datafile/AUX/3CEF724BE3D726E1E0631500A8C0EA84:
total 0
0 drwxr-x---. 2 oracle
asmadmin 6 Sep 6 21:04 datafile
/home/oracle/AUX/datafile/AUX/3CEF724BE3D726E1E0631500A8C0EA84/datafile:
total 0
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778:
total 4
4 drwxr-x---. 2 oracle
asmadmin 4096 Sep 6 21:04 datafile
/home/oracle/AUX/datafile/AUX/3DBE2F936BC21740E0631500A8C0B778/datafile:
total 1822768
542728 -rw-r-----. 1
oracle asmadmin 555753472 Sep 6 21:38 o1_mf_sysaux_ncr8rvbv_.dbf
501768 -rw-r-----. 1 oracle asmadmin 513810432 Sep 6
21:38 o1_mf_system_ncr8sdh8_.dbf
102408 -rw-r-----. 1
oracle asmadmin 104865792 Sep 6 21:38 o1_mf_tuner_da_ncr8t5mv_.dbf
102408 -rw-r-----. 1 oracle asmadmin 104865792 Sep 6
21:38 o1_mf_tuner_id_ncr8t91n_.dbf
286728 -rw-r-----. 1
oracle asmadmin 293609472 Sep 6 21:38 o1_mf_undo_2_ncr8thc7_.dbf
286728 -rw-r-----. 1 oracle asmadmin 293609472 Sep 6
21:38 o1_mf_undotbs1_ncr8td85_.dbf
/home/oracle/AUX/datafile/AUX/datafile:
total 3851304
1792008 -rw-r-----. 1
oracle asmadmin 1835016192 Sep 6 21:39 o1_mf_sysaux_ncr8mlcz_.dbf
1187848 -rw-r-----. 1 oracle asmadmin 1216356352 Sep
6 21:39 o1_mf_system_ncr8nzqg_.dbf
1024
-rw-r-----. 1 oracle asmadmin 74457088 Sep 6 21:19
o1_mf_temp_ncr9ph66_.tmp
814088 -rw-r-----. 1
oracle asmadmin 833626112 Sep 6 21:39
o1_mf_undotbs1_ncr8pqo6_.dbf
51208 -rw-r-----. 1
oracle asmadmin 52436992 Sep 6 21:19
o1_mf_undotbs2_ncr8pysq_.dbf
5128
-rw-r-----. 1 oracle asmadmin 5251072 Sep 6 21:19
o1_mf_users_ncr8pzv4_.dbf
/home/oracle/AUX/onlinelog:
total
1228824
0 drwxr-x---. 3 oracle
asmadmin 23 Sep 6 21:19 AUX
204804 -rw-r-----. 1 oracle asmadmin 209715712 Sep 6
21:41 group_1.log
204804 -rw-r-----. 1 oracle asmadmin
209715712 Sep 6 21:19 group_2.log
204804
-rw-r-----. 1 oracle asmadmin 209715712 Sep 6 21:19 group_3.log
204804 -rw-r-----. 1 oracle asmadmin 209715712 Sep 6
21:19 group_4.log
204804 -rw-r-----. 1 oracle asmadmin
209715712 Sep 6 21:19 group_5.log
204804
-rw-r-----. 1 oracle asmadmin 209715712 Sep 6 21:19
group_6.log
/home/oracle/AUX/onlinelog/AUX:
total 0
0 drwxr-x---. 2 oracle
asmadmin 6 Sep 6 21:19 onlinelog
/home/oracle/AUX/onlinelog/AUX/onlinelog:
total 0
[aux:oracle@ol7ora19r1][/home/oracle/AUX]$ rm -rf
/home/oracle/AUX
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
ls -l /u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
-rw-r-----. 1
oracle asmadmin 3584 Sep 6 21:34
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora
[aux:oracle@ol7ora19r1][/u01/app/oracle/product/19c/db_1/dbs]$
rm -f
/u01/app/oracle/product/19c/db_1/dbs/spfileaux.ora