[실습 환경]
<소스> -> 특정 테이블이 Drop
Table Purge가 된 DB
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
<타켓> -> 특정 테이블이 복구된
DB
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.26.0.0.0)
SINGLE : tdb01t(192.168.0.51) : ORA19R 로 복구
0. 소스에서 신규
유저 및 테이블 생성
[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 Wed Jun 11 21:25:46 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
[2025-06-15:09:05:19][ol7ora19r1]<SYS@ORA19R1>
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_NEW' SIZE 100M
AUTOEXTEND ON NEXT 512M
MAXSIZE UNLIMITED
;
DROP TABLESPACE
TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
TUNER_IDX1
DATAFILE '+DATA1_NEW' SIZE 100M
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP TABLESPACE
TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY
TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1_NEW' SIZE 64M
AUTOEXTEND ON NEXT
100M MAXSIZE UNLIMITED
;
ALTER USER TUNER
DEFAULT TABLESPACE TUNER_DATA1;
ALTER USER TUNER TEMPORARY TABLESPACE
TUNER_TEMP;
DROP TABLE TUNER.TB_CUST PURGE;
CREATE TABLE
TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID
VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_ID IS
'고객아이디';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NM IS '고객명';
COMMENT ON
COLUMN TUNER.TB_CUST.BRTHDY IS '생년월일';
COMMENT ON COLUMN TUNER.TB_CUST.SEX_CD
IS '성별코드';
COMMENT ON COLUMN TUNER.TB_CUST.JOIN_DT IS '가입일시';
COMMENT ON
COLUMN TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN TUNER.TB_CUST.INPUT_DT
IS '입력일시';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_ID IS '수정아이디';
COMMENT ON
COLUMN TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_CUST IS
'고객';
ALTER TABLE TUNER.TB_CUST NOLOGGING;
INSERT /*+ APPEND
*/
INTO TUNER.TB_CUST
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
INPUT_ID
, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS
INPUT_DT
, NULL AS
UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
1000000;
COMMIT;
CREATE UNIQUE INDEX
TUNER.PK_TB_CUST ON TUNER.TB_CUST(CUST_NO) TABLESPACE TUNER_IDX1 PARALLEL 4
NOLOGGING;
ALTER INDEX
TUNER.PK_TB_CUST PARALLEL 1;
ALTER INDEX TUNER.PK_TB_CUST LOGGING;
ALTER
TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST PRIMARY KEY (CUST_NO);
CREATE INDEX
TUNER.IDX_TB_CUST_01 ON TUNER.TB_CUST(CUST_STS_CD) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_01 PARALLEL 1;
ALTER
INDEX TUNER.IDX_TB_CUST_01 LOGGING;
CREATE INDEX
TUNER.IDX_TB_CUST_02 ON TUNER.TB_CUST(BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4
NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_02 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_CUST_02 LOGGING;
CREATE INDEX
TUNER.IDX_TB_CUST_03 ON TUNER.TB_CUST(CUST_STS_CD, BRTHDY) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03 PARALLEL 1;
ALTER
INDEX TUNER.IDX_TB_CUST_03 LOGGING;
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);
[2025-06-14:19:48:49][ol7ora19r1]<SYS@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
--> tuner.tb_cust 테이블 생성
완료함
SELECT
CURRENT_SCN
FROM V$DATABASE;
CURRENT_SCN
-----------
6558618
--> 현재의 SCN 번호 확인
1.
소스에서 백업받을 디렉토리 생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
mkdir -p
/home/oracle/ORA19R_BACKUP/autobackup
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$
ls -l /home/oracle/ORA19R_BACKUP/autobackup
total 0
2. 소스에서
백업 수행
[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 Sun Jun 15 09:11:56 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=1824822448)
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 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
MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE
ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF
RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
}
old RMAN
configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new
RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY
1;
new RMAN configuration parameters are successfully
stored
old RMAN
configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN
configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN
configuration parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN
configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN
configuration parameters are successfully stored
released channel:
ORA_DISK_1
old RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN
configuration parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/home/oracle/ORA19R_BACKUP/autobackup/%F';
new RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/home/oracle/ORA19R_BACKUP/autobackup/%F';
new RMAN
configuration parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP
TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE
TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration
parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE
DISK TO 1;
new RMAN configuration parameters:
CONFIGURE DATAFILE BACKUP
COPIES FOR DEVICE TYPE DISK TO 1;
new RMAN configuration parameters are
successfully stored
old RMAN
configuration parameters:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
DISK TO 1;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG BACKUP
COPIES FOR DEVICE TYPE DISK TO 1;
new RMAN configuration parameters are
successfully stored
old RMAN
configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO
NONE;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION
POLICY TO NONE;
new RMAN configuration parameters are successfully
stored
old RMAN
configuration parameters:
CONFIGURE MAXSETSIZE TO UNLIMITED;
new RMAN
configuration parameters:
CONFIGURE MAXSETSIZE TO UNLIMITED;
new RMAN
configuration parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE OFF;
new RMAN
configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE OFF;
new RMAN
configuration parameters are successfully stored
old RMAN
configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
new
RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM
'AES128';
new RMAN configuration parameters are successfully
stored
old RMAN
configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF
RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration
parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'
OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully
stored
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
SYSTEM FORMAT '/home/oracle/ORA19R_BACKUP/SYSTEM_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE SYSAUX FORMAT
'/home/oracle/ORA19R_BACKUP/SYSAUX_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE UNDOTBS1 FORMAT
'/home/oracle/ORA19R_BACKUP/UNDOTBS1_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE USERS FORMAT '/home/oracle/ORA19R_BACKUP/USERS_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET TABLESPACE UNDOTBS2 FORMAT
'/home/oracle/ORA19R_BACKUP/UNDOTBS2_%U.bkp';
BACKUP AS COMPRESSED BACKUPSET
TABLESPACE TUNER_DATA1 FORMAT
'/home/oracle/ORA19R_BACKUP/TUNER_DATA1_%U.bkp';
BACKUP AS COMPRESSED
BACKUPSET TABLESPACE TUNER_IDX1 FORMAT
'/home/oracle/ORA19R_BACKUP/TUNER_IDX1_%U.bkp';
delete noprompt
obsolete;
delete noprompt expired backup;
}
sql statement: alter system archive log current
sql statement: alter system checkpoint
allocated
channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 instance=ORA19R1 device
type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_DATA1_3i3rpo22_114_1_1.bkp
RECID=103 STAMP=1203560514
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_IDX1_3j3rpo25_115_1_1.bkp RECID=104
STAMP=1203560517
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/rman_backup/ORA19R/TUNER_DATA1_4l3rv0t7_149_1_1.bkp
RECID=137 STAMP=1203733416
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_IDX1_4m3rv0tb_150_1_1.bkp RECID=138
STAMP=1203733420
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/SYSTEM_5k3s2dcn_180_1_1.bkp RECID=168
STAMP=1203844503
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/SYSAUX_5l3s2de5_181_1_1.bkp RECID=169
STAMP=1203844549
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_5m3s2df9_182_1_1.bkp RECID=170
STAMP=1203844586
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/USERS_5n3s2dfc_183_1_1.bkp RECID=171
STAMP=1203844588
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_5o3s2dfd_184_1_1.bkp RECID=172
STAMP=1203844589
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_5p3s2dff_185_1_1.bkp
RECID=173 STAMP=1203844591
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_5q3s2dfn_186_1_1.bkp RECID=174
STAMP=1203844599
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-03
RECID=175 STAMP=1203844607
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_5s3s2dg8_s188_p1
RECID=176 STAMP=1203844616
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-04 RECID=177
STAMP=1203844618
Crosschecked 14 objects
using
channel ORA_DISK_1
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_DATA1_3i3rpo22_114_1_1.bkp
RECID=103 STAMP=1203560514
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_IDX1_3j3rpo25_115_1_1.bkp RECID=104
STAMP=1203560517
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/rman_backup/ORA19R/TUNER_DATA1_4l3rv0t7_149_1_1.bkp
RECID=137 STAMP=1203733416
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/rman_backup/ORA19R/TUNER_IDX1_4m3rv0tb_150_1_1.bkp RECID=138
STAMP=1203733420
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/SYSTEM_5k3s2dcn_180_1_1.bkp RECID=168
STAMP=1203844503
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/SYSAUX_5l3s2de5_181_1_1.bkp RECID=169
STAMP=1203844549
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_5m3s2df9_182_1_1.bkp RECID=170
STAMP=1203844586
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/USERS_5n3s2dfc_183_1_1.bkp RECID=171
STAMP=1203844588
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_5o3s2dfd_184_1_1.bkp RECID=172
STAMP=1203844589
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_5p3s2dff_185_1_1.bkp
RECID=173 STAMP=1203844591
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_5q3s2dfn_186_1_1.bkp RECID=174
STAMP=1203844599
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-03
RECID=175 STAMP=1203844607
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_5s3s2dg8_s188_p1
RECID=176 STAMP=1203844616
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-04 RECID=177
STAMP=1203844618
Crosschecked 14 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: SID=572 instance=ORA19R1 device type=DISK
specification does not
match any datafile copy in the repository
specification does not match any
control file copy in the repository
validation succeeded for archived
log
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_166.314.1203844669
RECID=246 STAMP=1203844669
validation succeeded for archived log
archived
log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_91.297.1203844671
RECID=247 STAMP=1203844670
Crosschecked 2 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: SID=572 instance=ORA19R1 device type=DISK
validation succeeded
for archived log
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_166.314.1203844669
RECID=246 STAMP=1203844669
validation succeeded for archived log
archived
log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_91.297.1203844671
RECID=247 STAMP=1203844670
Crosschecked 2 objects
Starting backup at 2025-06-15 09:17:59
using channel
ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup
set
channel ORA_DISK_1: specifying datafile(s) in backup set
input
datafile file number=00001
name=+DATA1_NEW/ORA19R/DATAFILE/system.264.1202825797
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:17:59
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:18:34
piece
handle=/home/oracle/ORA19R_BACKUP/SYSTEM_5u3s2di7_190_1_1.bkp
tag=TAG20250615T091759 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:35
Finished backup at 2025-06-15
09:18:34
Starting
backup at 2025-06-15 09:18:34
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00003
name=+DATA1_NEW/ORA19R/DATAFILE/sysaux.265.1202825873
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:18:35
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:19:00
piece
handle=/home/oracle/ORA19R_BACKUP/SYSAUX_5v3s2dja_191_1_1.bkp
tag=TAG20250615T091834 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:25
Finished backup at 2025-06-15
09:19:00
Starting
backup at 2025-06-15 09:19:00
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00004
name=+DATA1_NEW/ORA19R/DATAFILE/undotbs1.266.1202826029
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:19:01
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:19:04
piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_603s2dk5_192_1_1.bkp
tag=TAG20250615T091900 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:03
Finished backup at 2025-06-15
09:19:04
Starting
backup at 2025-06-15 09:19:04
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00007
name=+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:19:05
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:19:06
piece
handle=/home/oracle/ORA19R_BACKUP/USERS_613s2dk8_193_1_1.bkp
tag=TAG20250615T091904 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-06-15
09:19:06
Starting
backup at 2025-06-15 09:19:06
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00005
name=+DATA1_NEW/ORA19R/DATAFILE/undotbs2.267.1202826057
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:19:06
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:19:07
piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_623s2dka_194_1_1.bkp
tag=TAG20250615T091906 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-06-15
09:19:07
Starting
backup at 2025-06-15 09:19:07
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00002
name=+DATA1_NEW/ORA19R/DATAFILE/tuner_data1.271.1203843947
channel
ORA_DISK_1: starting piece 1 at 2025-06-15 09:19:08
channel ORA_DISK_1:
finished piece 1 at 2025-06-15 09:19:15
piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_633s2dkc_195_1_1.bkp
tag=TAG20250615T091907 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:07
Finished backup at 2025-06-15
09:19:15
Starting
backup at 2025-06-15 09:19:16
using channel ORA_DISK_1
channel ORA_DISK_1:
starting compressed full datafile backup set
channel ORA_DISK_1: specifying
datafile(s) in backup set
input datafile file number=00008
name=+DATA1_NEW/ORA19R/DATAFILE/tuner_idx1.272.1203843949
channel ORA_DISK_1:
starting piece 1 at 2025-06-15 09:19:17
channel ORA_DISK_1: finished piece 1
at 2025-06-15 09:19:20
piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_643s2dkl_196_1_1.bkp
tag=TAG20250615T091916 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:03
Finished backup at 2025-06-15
09:19:20
Starting
Control File and SPFILE Autobackup at 2025-06-15 09:19:20
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-05
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-15
09:19:21
RMAN
retention policy will be applied to the command
RMAN retention policy is set
to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete
backups and
copies:
Type
Key Completion Time
Filename/Handle
-------------------- ------ ------------------
--------------------
Backup
Set
168 2025-06-15 09:15:41
Backup
Piece 168 2025-06-15
09:15:41 /home/oracle/ORA19R_BACKUP/SYSTEM_5k3s2dcn_180_1_1.bkp
Backup
Set
169 2025-06-15 09:16:14
Backup
Piece 169 2025-06-15
09:16:14 /home/oracle/ORA19R_BACKUP/SYSAUX_5l3s2de5_181_1_1.bkp
Backup
Set
170 2025-06-15 09:16:27
Backup
Piece 170 2025-06-15
09:16:27 /home/oracle/ORA19R_BACKUP/UNDOTBS1_5m3s2df9_182_1_1.bkp
Backup
Set
171 2025-06-15 09:16:28
Backup
Piece 171 2025-06-15
09:16:28 /home/oracle/ORA19R_BACKUP/USERS_5n3s2dfc_183_1_1.bkp
Backup
Set
172 2025-06-15 09:16:30
Backup
Piece 172 2025-06-15
09:16:30 /home/oracle/ORA19R_BACKUP/UNDOTBS2_5o3s2dfd_184_1_1.bkp
Backup
Set
173 2025-06-15 09:16:34
Backup
Piece 173 2025-06-15
09:16:34 /home/oracle/ORA19R_BACKUP/TUNER_DATA1_5p3s2dff_185_1_1.bkp
Backup
Set
174 2025-06-15 09:16:42
Backup
Piece 174 2025-06-15
09:16:42 /home/oracle/ORA19R_BACKUP/TUNER_IDX1_5q3s2dfn_186_1_1.bkp
Backup
Set
175 2025-06-15 09:16:47
Backup
Piece 175 2025-06-15
09:16:47
/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-03
Backup
Set
176 2025-06-15 09:16:56
Backup
Piece 176 2025-06-15
09:16:56
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_5s3s2dg8_s188_p1
Archive
Log 246
2025-06-15 09:17:49
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_166.314.1203844669
Backup
Set
177 2025-06-15 09:16:58
Backup
Piece 177 2025-06-15
09:16:58
/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-04
Archive
Log 247
2025-06-15 09:17:50
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_91.297.1203844671
deleted
backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/SYSTEM_5k3s2dcn_180_1_1.bkp RECID=168
STAMP=1203844503
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/SYSAUX_5l3s2de5_181_1_1.bkp RECID=169
STAMP=1203844549
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_5m3s2df9_182_1_1.bkp RECID=170
STAMP=1203844586
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/USERS_5n3s2dfc_183_1_1.bkp RECID=171
STAMP=1203844588
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_5o3s2dfd_184_1_1.bkp RECID=172
STAMP=1203844589
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_5p3s2dff_185_1_1.bkp RECID=173
STAMP=1203844591
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_5q3s2dfn_186_1_1.bkp RECID=174
STAMP=1203844599
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-03 RECID=175
STAMP=1203844607
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_5s3s2dg8_s188_p1
RECID=176 STAMP=1203844616
deleted archived log
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_166.314.1203844669
RECID=246 STAMP=1203844669
deleted backup piece
backup piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-04 RECID=177
STAMP=1203844618
deleted archived log
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_91.297.1203844671
RECID=247 STAMP=1203844670
Deleted 12 objects
using
channel ORA_DISK_1
specification does not match any backup in the
repository
run
{
crosscheck archivelog all;
backup archivelog all format
'/home/oracle/ORA19R_BACKUP/%d_ARCHIVE_%T_%u_s%s_p%p' delete input;
delete
backup of archivelog all completed before 'SYSDATE-14';
}
released
channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1:
SID=572 instance=ORA19R1 device type=DISK
specification does not match any
archived log in the repository
Starting
backup at 2025-06-15 09:20:48
current log archived
using channel
ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel
ORA_DISK_1: specifying archived log(s) in backup set
input archived log
thread=1 sequence=167 RECID=248 STAMP=1203844849
input archived log thread=2
sequence=92 RECID=249 STAMP=1203844851
channel ORA_DISK_1: starting piece 1
at 2025-06-15 09:20:51
channel ORA_DISK_1: finished piece 1 at 2025-06-15
09:20:52
piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
tag=TAG20250615T092051 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived
log(s)
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_167.297.1203844849
RECID=248 STAMP=1203844849
archived log file
name=+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_92.314.1203844851
RECID=249 STAMP=1203844851
Finished backup at 2025-06-15
09:20:52
Starting
Control File and SPFILE Autobackup at 2025-06-15 09:20:52
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-06
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-15
09:20:55
using
channel ORA_DISK_1
specification does not match any backup in the
repository
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -lRa
/home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total
784672
drwxr-xr-x. 3 oracle oinstall 4096 Jun
15 09:20 .
drwx------. 8 oracle oinstall 4096
Jun 15 09:11 ..
drwxr-xr-x. 2 oracle
oinstall 70 Jun 15 09:20
autobackup
-rw-r-----. 1 oracle asmadmin 497664 Jun 15
09:20 ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
-rw-r-----. 1 oracle asmadmin
253968384 Jun 15 09:18 SYSAUX_5v3s2dja_191_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 457981952 Jun 15 09:18 SYSTEM_5u3s2di7_190_1_1.bkp
-rw-r-----. 1
oracle asmadmin 68919296 Jun 15 09:19
TUNER_DATA1_633s2dkc_195_1_1.bkp
-rw-r-----. 1 oracle asmadmin 13737984
Jun 15 09:19 TUNER_IDX1_643s2dkl_196_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2293760 Jun 15 09:19
UNDOTBS1_603s2dk5_192_1_1.bkp
-rw-r-----. 1 oracle asmadmin
4849664 Jun 15 09:19 UNDOTBS2_623s2dka_194_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Jun 15 09:19
USERS_613s2dk8_193_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total
39364
drwxr-xr-x. 2 oracle oinstall 70
Jun 15 09:20 .
drwxr-xr-x. 3 oracle oinstall 4096 Jun
15 09:20 ..
-rw-r-----. 1 oracle asmadmin 20152320 Jun 15 09:19
c-1824822448-20250615-05
-rw-r-----. 1 oracle asmadmin 20152320 Jun 15 09:20
c-1824822448-20250615-06
--> 이게 나중에 백업받은 control file
임
3.
소스에서 drop table purge가 되는 사고가 발생함!
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
st
alias st='rlwrap sqlplus tuner/oracle'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ st
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Jun 15 09:45:16 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sun Jun 15 2025 09:45:07 +09:00
Connected
to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.27.0.0.0
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
Elapsed:
00:00:00.03
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CUR_DATE FROM DUAL;
CUR_DATE
---------------------------------------------------------
2025-06-15
09:45:28
1 row selected.
Elapsed:
00:00:00.00
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
6565835
--> 해당 SCN번호로 불완전 복구를 한다면 아래에서 DROP TABLE PURGE하는 테이블을 복구할 수 있게
되는 것임!
1 row selected.
Elapsed:
00:00:00.03
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> drop table tuner.tb_cust purge;
Table dropped.
Elapsed:
00:00:00.33
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> select * from
tuner.tb_cust;
select * from
tuner.tb_cust
*
ERROR at line 1:
ORA-00942: table or view does not
exist
Elapsed: 00:00:00.01
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
6565905
--> 해당 SCN번호는 결국 TUNER.TB_CUST 테이블이 DROP PURGE된 상태임
1 row selected.
Elapsed:
00:00:00.00
[2025-06-15:09:45:16][ol7ora19r1]<TUNER@ORA19R1>
4. 소스에서 아카이브를 발생 시킴 (아카이브 발생 시 check point도 자동으로 하게됨)
--아카이브 발생 시키기 전 아카이브 로그를 확인
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl
+FRA1_NEW/ORA19R/ARCHIVELOG
[+ASM1:grid@ol7ora19r1][/home/grid]$
--> 아카이브 로그가 없음
[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 Sun Jun 15 09:47:01 2025
Version
19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.27.0.0.0
[2025-06-15:09:47:01][ol7ora19r1]<SYS@ORA19R1> ALTER SYSTEM ARCHIVE LOG CURRENT;
--> RAC 각 노드에서 아카이브 로그를 발생 시킴
System altered.
Elapsed: 00:00:02.06
[2025-06-15:09:47:01][ol7ora19r1]<SYS@ORA19R1>
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls -sl
+FRA1_NEW/ORA19R/ARCHIVELOG
Type Redund Striped
Time Sys Block_Size Blocks Bytes Space
Name
Y
2025_06_15/
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd ls
-sl
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/
Type
Redund Striped
Time
Sys Block_Size Blocks Bytes
Space Name
ARCHIVELOG UNPROT COARSE JUN 15
09:00:00 Y
512 1671 855552 4194304
thread_1_seq_168.314.1203846429
ARCHIVELOG UNPROT
COARSE JUN 15 09:00:00
Y
512 212 108544 4194304
thread_2_seq_93.297.1203846429
[+ASM1:grid@ol7ora19r1][/home/grid]$
--> RAC 1, 2에서 각각 아카이브 로그가 생성됨
[2025-06-14:20:02:16][ol7ora19r1]<SYS@ORA19R1>
col name for a80
select NAME, THREAD#, SEQUENCE#,
FIRST_CHANGE#, NEXT_CHANGE# from v$archived_log where 6565835 between FIRST_CHANGE# and
NEXT_CHANGE#;
NAME
THREAD# SEQUENCE# FIRST_CHANGE#
NEXT_CHANGE#
--------------------------------------------------------------------------------
---------- ---------- -------------
------------
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_1_seq_168.314.1203846429
1
168 6563579
6565981
+FRA1_NEW/ORA19R/ARCHIVELOG/2025_06_15/thread_2_seq_93.297.1203846429
2
93 6563583
6565985
--> 복구 목표 시점인 scn 6565835 은 위의 아카이브 로그에
걸쳐 있음
--아래와 같이 RMAN에서 해당 아카이브 로그를
백업받아야함 (백업본 restore 후 불완전 복구 시 필요함)
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ alias
rt
alias rt='rman target /'
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ rt
Recovery
Manager: Release 19.0.0.0.0 - Production on Sat Jun 14 20:27: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 (DBID=1824822448)
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 93 THREAD 2 FORMAT '/home/oracle/ORA19R_BACKUP/arch_t2_s93_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 93 THREAD 2 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t2_s93_%U.bkp';
Starting backup at
2025-06-15 09:53:41
current log archived
using target database control
file instead of recovery catalog
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: SID=32 instance=ORA19R1 device type=DISK
channel ORA_DISK_1:
starting compressed archived log backup set
channel ORA_DISK_1: specifying
archived log(s) in backup set
input archived log thread=2 sequence=93
RECID=251 STAMP=1203846428
input archived log thread=2 sequence=94 RECID=253
STAMP=1203846823
channel ORA_DISK_1: starting piece 1 at 2025-06-15
09:53:47
channel ORA_DISK_1: finished piece 1 at 2025-06-15 09:53:48
piece
handle=/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
tag=TAG20250615T095346 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-06-15
09:53:48
Starting
Control File and SPFILE Autobackup at 2025-06-15 09:53:48
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-07
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-15
09:53:49
RMAN> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 168 THREAD 1 FORMAT '/home/oracle/ORA19R_BACKUP/arch_t1_s168_%U.bkp';
BACKUP AS
COMPRESSED BACKUPSET ARCHIVELOG FROM SEQUENCE 168 THREAD 1 FORMAT
'/home/oracle/ORA19R_BACKUP/arch_t1_s168_%U.bkp';
Starting backup at
2025-06-15 09:54:17
current log archived
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=168 RECID=250 STAMP=1203846428
input archived
log thread=1 sequence=169 RECID=252 STAMP=1203846823
input archived log
thread=1 sequence=170 RECID=254 STAMP=1203846859
channel ORA_DISK_1: starting
piece 1 at 2025-06-15 09:54:19
channel ORA_DISK_1: finished piece 1 at
2025-06-15 09:54:20
piece
handle=/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
tag=TAG20250615T095419 comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 2025-06-15
09:54:20
Starting
Control File and SPFILE Autobackup at 2025-06-15 09:54:20
piece
handle=/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-08
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-06-15
09:54:22
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ ls -lR
/home/oracle/ORA19R_BACKUP/
/home/oracle/ORA19R_BACKUP/:
total 785672
-rw-r-----. 1 oracle asmadmin 892928 Jun
15 09:54 arch_t1_s168_6a3s2fmb_202_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 132096 Jun 15 09:53
arch_t2_s93_683s2flb_200_1_1.bkp
--> 백업 받은 아카이브 로그 확인
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 09:54
autobackup
-rw-r-----. 1 oracle asmadmin 497664 Jun 15
09:20 ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
-rw-r-----. 1 oracle asmadmin
253968384 Jun 15 09:18 SYSAUX_5v3s2dja_191_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 457981952 Jun 15 09:18 SYSTEM_5u3s2di7_190_1_1.bkp
-rw-r-----. 1
oracle asmadmin 68919296 Jun 15 09:19
TUNER_DATA1_633s2dkc_195_1_1.bkp
-rw-r-----. 1 oracle asmadmin 13737984
Jun 15 09:19 TUNER_IDX1_643s2dkl_196_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 2293760 Jun 15 09:19
UNDOTBS1_603s2dk5_192_1_1.bkp
-rw-r-----. 1 oracle asmadmin
4849664 Jun 15 09:19 UNDOTBS2_623s2dka_194_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 1245184 Jun 15 09:19
USERS_613s2dk8_193_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 78720
-rw-r-----. 1
oracle asmadmin 20152320 Jun 15 09:19 c-1824822448-20250615-05
-rw-r-----. 1
oracle asmadmin 20152320 Jun 15 09:20 c-1824822448-20250615-06
-rw-r-----. 1
oracle asmadmin 20152320 Jun 15 09:53 c-1824822448-20250615-07
-rw-r-----. 1 oracle asmadmin 20152320 Jun 15
09:54 c-1824822448-20250615-08 --> 이것이 가장 최신의 컨트롤 파일 백업 본임
5.
소스에서 pfile 생성 및 control file 생성하는 스크립트를 생성
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus:
Release 19.0.0.0.0 - Production on Sun Jun 15 09:58:48 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
[2025-06-15:09:58:48][ol7ora19r1]<SYS@ORA19R1> create pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora' from spfile;
File created.
Elapsed: 00:00:00.02
[2025-06-15:09:58:48][ol7ora19r1]<SYS@ORA19R1> alter database backup controlfile to trace as
'/home/oracle/ORA19R_BACKUP/ORA19R_CREATE_CONTROLFILE.sql';
Database altered.
Elapsed: 00:00:00.02
6. 타켓에서
백업본을 저장할 디렉토리 생성 및 소스에서 백업본을 가져옴 (scp)
[ORCL:oracle@tdb01t][/home/oracle]$ mkdir -pv
/home/oracle/ORA19R_BACKUP/
mkdir: created directory
‘/home/oracle/ORA19R_BACKUP’
[ORCL:oracle@tdb01t][/home/oracle]$ cd /home/oracle/ORA19R_BACKUP
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ scp -r
192.168.0.21:/home/oracle/ORA19R_BACKUP/*
/home/oracle/ORA19R_BACKUP/
*/
oracle@192.168.0.21''s
password:
arch_t1_s168_6a3s2fmb_202_1_1.bkp
100% 872KB 24.5MB/s
00:00
arch_t2_s93_683s2flb_200_1_1.bkp
100% 129KB 17.2MB/s
00:00
c-1824822448-20250615-05
100% 19MB 36.3MB/s
00:00
c-1824822448-20250615-06
100% 19MB 48.7MB/s
00:00
c-1824822448-20250615-07
100% 19MB 53.9MB/s
00:00
c-1824822448-20250615-08
100% 19MB 32.8MB/s
00:00
initORA19R.ora
100% 1958 375.4KB/s
00:00
ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1 100% 486KB
26.1MB/s
00:00
ORA19R_CREATE_CONTROLFILE.sql
100% 13KB 5.7MB/s
00:00
SYSAUX_5v3s2dja_191_1_1.bkp
100% 242MB 30.4MB/s
00:07
SYSTEM_5u3s2di7_190_1_1.bkp
100% 437MB 81.7MB/s
00:05
TUNER_DATA1_633s2dkc_195_1_1.bkp
100% 66MB 11.9MB/s
00:05
TUNER_IDX1_643s2dkl_196_1_1.bkp
100% 13MB 50.7MB/s
00:00
UNDOTBS1_603s2dk5_192_1_1.bkp
100% 2240KB 45.8MB/s
00:00
UNDOTBS2_623s2dka_194_1_1.bkp
100% 4736KB 57.7MB/s
00:00
USERS_613s2dk8_193_1_1.bkp
100% 1216KB 47.8MB/s 00:00
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ ls -laR
/home/oracle/ORA19R_BACKUP
/home/oracle/ORA19R_BACKUP:
total
785700
drwxr-xr-x. 3 oracle oinstall 4096
Jun 15 10:00 .
drwx------. 11 oracle oinstall
4096 Jun 15 10:00 ..
-rw-r-----. 1 oracle oinstall
892928 Jun 15 10:00 arch_t1_s168_6a3s2fmb_202_1_1.bkp
-rw-r-----. 1
oracle oinstall 132096 Jun 15 10:00
arch_t2_s93_683s2flb_200_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 10:00
autobackup
-rw-r--r--. 1 oracle oinstall
1958 Jun 15 10:00 initORA19R.ora
-rw-r-----. 1 oracle
oinstall 497664 Jun 15 10:00
ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
-rw-r--r--. 1 oracle
oinstall 13136 Jun 15 10:00
ORA19R_CREATE_CONTROLFILE.sql
-rw-r-----. 1 oracle oinstall 253968384
Jun 15 10:00 SYSAUX_5v3s2dja_191_1_1.bkp
-rw-r-----. 1 oracle oinstall
457981952 Jun 15 10:00 SYSTEM_5u3s2di7_190_1_1.bkp
-rw-r-----. 1 oracle
oinstall 68919296 Jun 15 10:00
TUNER_DATA1_633s2dkc_195_1_1.bkp
-rw-r-----. 1 oracle oinstall
13737984 Jun 15 10:00 TUNER_IDX1_643s2dkl_196_1_1.bkp
-rw-r-----. 1
oracle oinstall 2293760 Jun 15 10:00
UNDOTBS1_603s2dk5_192_1_1.bkp
-rw-r-----. 1 oracle oinstall
4849664 Jun 15 10:00 UNDOTBS2_623s2dka_194_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1245184 Jun 15 10:00
USERS_613s2dk8_193_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup:
total 78728
drwxr-xr-x. 2
oracle oinstall 4096 Jun 15 10:00 .
drwxr-xr-x. 3
oracle oinstall 4096 Jun 15 10:00 ..
-rw-r-----. 1
oracle oinstall 20152320 Jun 15 10:00 c-1824822448-20250615-05
-rw-r-----. 1
oracle oinstall 20152320 Jun 15 10:00 c-1824822448-20250615-06
-rw-r-----. 1
oracle oinstall 20152320 Jun 15 10:00 c-1824822448-20250615-07
-rw-r-----. 1
oracle oinstall 20152320 Jun 15 10:00
c-1824822448-20250615-08
7.
타켓에서 pfile 수정 및 컨트롤 파일 생성 스크립트 확인
--타켓에서 신규 리스너 생성
[+ASM:grid@tdb01t][/home/grid]$ srvctl add listener -listener
LISTENER_ORA19R -oraclehome /u01/app/19c/grid -endpoints
TCP:1525
[+ASM:grid@tdb01t][/home/grid]$ srvctl start listener -listener
LISTENER_ORA19R
[+ASM:grid@tdb01t][/home/grid]$ lsnrctl status
LISTENER_ORA19R
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JUN-2025 10:03:06
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA19R)))
STATUS of the
LISTENER
------------------------
Alias
LISTENER_ORA19R
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
15-JUN-2025
10:03:03
Uptime
0 days 0 hr. 0 min. 2 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/tdb01t/listener_ora19r/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA19R)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))
The
listener supports no services
The command completed
successfully
--> "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))"
를 타켓 db의 local_listener 파라미터의 값으로 할 것임
--> 소스에서 백업받은 PFILE을 타켓에서 확인
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ cat
/home/oracle/ORA19R_BACKUP/initORA19R.ora
ORA19R2.__data_transfer_cache_size=0
ORA19R1.__data_transfer_cache_size=0
ORA19R1.__db_cache_size=3741319168
ORA19R2.__db_cache_size=3875536896
ORA19R2.__inmemory_ext_roarea=0
ORA19R1.__inmemory_ext_roarea=0
ORA19R2.__inmemory_ext_rwarea=0
ORA19R1.__inmemory_ext_rwarea=0
ORA19R2.__java_pool_size=0
ORA19R1.__java_pool_size=0
ORA19R2.__large_pool_size=16777216
ORA19R1.__large_pool_size=16777216
ORA19R1.__oracle_base='/u01/app/oracle'#ORACLE_BASE
set from environment
ORA19R2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set
from
environment
ORA19R2.__pga_aggregate_target=1728053248
ORA19R1.__pga_aggregate_target=1728053248
ORA19R2.__sga_target=5167382528
ORA19R1.__sga_target=5167382528
ORA19R2.__shared_io_pool_size=50331648
ORA19R1.__shared_io_pool_size=134217728
ORA19R1.__shared_pool_size=1258291200
ORA19R2.__shared_pool_size=1207959552
ORA19R2.__streams_pool_size=0
ORA19R1.__streams_pool_size=0
ORA19R2.__unified_pga_pool_size=0
ORA19R1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA1_NEW/ORA19R/CONTROLFILE/current.256.1202820017','+FRA1_NEW/ORA19R/CONTROLFILE/current.261.1202820017'
*.db_block_size=8192
*.db_create_file_dest='+FRA1_NEW'
*.db_create_online_log_dest_1='+DATA1_NEW'
*.db_create_online_log_dest_2='+FRA1_NEW'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=ORA19RXDB)'
family:dw_helper.instance_mode='read-only'
ORA19R1.instance_number=1
ORA19R2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='LOCATION=+FRA1_NEW'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1638m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.sga_target=4915m
ORA19R2.thread=2
ORA19R1.thread=1
ORA19R2.undo_tablespace='UNDOTBS2'
ORA19R1.undo_tablespace='UNDOTBS1'
-- 아래와 같이 PFILE을 수정함
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ vi
/home/oracle/ORA19R_BACKUP/initORA19R.ora
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
cat /home/oracle/ORA19R_BACKUP/initORA19R.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA19R/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl','/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/ORA19R_BACKUP'
*.db_create_online_log_dest_1='/home/oracle/ORA19R_BACKUP/redolog_1'
*.db_create_online_log_dest_2='/home/oracle/ORA19R_BACKUP/redolog_2'
*.db_name='ORA19R'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=ORA19RXDB)'
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))'
*.log_archive_dest_1='LOCATION=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1638m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.sga_target=4915m
*.undo_tablespace='UNDOTBS1'
--> 수정한 pfile을 기준으로 필요한 디렉토리
생성
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/u01/app/oracle/admin/ORA19R/adump
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
mkdir -p
/home/oracle/ORA19R_BACKUP/ORA19R/controlfile
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
mkdir -p
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
mkdir -p
/home/oracle/ORA19R_BACKUP/ORA19R/ARCH
[ORCL:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
mkdir -p /home/oracle/ORA19R_BACKUP/ORA19R/datafile
[ORA19R:oracle@tdb01t][/home/oracle]$ cat
/home/oracle/ORA19R_BACKUP/ORA19R_CREATE_CONTROLFILE.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="ORA19R"
--
--
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=+FRA1_NEW'
-- LOG_ARCHIVE_DEST_1='OPTIONAL
REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
--
LOG_ARCHIVE_DEST_1='REGISTER'
-- 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
--
--
Below are two sets of SQL statements, each of which creates a new
-- control
file and uses it to open the database. The first set opens
-- the database
with the NORESETLOGS option and should be used only if
-- the current
versions of all online logs are available. The second
-- set opens the
database with the RESETLOGS option and should be used
-- if online logs are
unavailable.
-- The appropriate set of statements can be copied from the
trace into
-- a script file, edited as necessary, and executed when there is
a
-- need to re-create the control file.
--
--
Set #1. NORESETLOGS case
--
-- The following commands will create a new
control file and use it
-- to open the database.
-- Data used by Recovery
Manager will be lost.
-- Additional logs may be required for media recovery
of offline
-- Use this only if the current versions of all online logs
are
-- available.
-- 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"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_1.258.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 4 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_4.260.1202820853',
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853'
) SIZE 200M BLOCKSIZE 512,
GROUP 5 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855',
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855'
) SIZE 200M BLOCKSIZE 512,
GROUP 6 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857',
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857'
) SIZE 200M BLOCKSIZE 512
--
STANDBY LOGFILE
DATAFILE
'+DATA1_NEW/ORA19R/DATAFILE/system.264.1202825797',
'+DATA1_NEW/ORA19R/DATAFILE/tuner_data1.271.1203843947',
'+DATA1_NEW/ORA19R/DATAFILE/sysaux.265.1202825873',
'+DATA1_NEW/ORA19R/DATAFILE/undotbs1.266.1202826029',
'+DATA1_NEW/ORA19R/DATAFILE/undotbs2.267.1202826057',
'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071',
'+DATA1_NEW/ORA19R/DATAFILE/tuner_idx1.272.1203843949'
CHARACTER SET KO16MSWIN949
;
-- Configure
snapshot controlfile filename
EXECUTE
SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+DATA1_NEW/ORA19R/CONTROLFILE/snapcf_ca.f');
-- 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 6
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO
''+DATA1_NEW/ORA19R/CONTROLFILE/snapcf_CA.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 '+FRA1_NEW';
-- ALTER DATABASE REGISTER
LOGFILE '+FRA1_NEW';
-- Recovery is required if any of
the datafiles are restored backups,
-- or if the last
shutdown was not normal or immediate.
RECOVER
DATABASE
-- All logs need
archiving and a log switch is needed.
ALTER SYSTEM
ARCHIVE LOG ALL;
-- Database can
now be opened normally.
ALTER DATABASE
OPEN;
-- Commands to add
tempfiles to temporary tablespaces.
-- Online tempfiles
have complete space information.
-- Other tempfiles may
require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'+FRA1_NEW/ORA19R/TEMPFILE/temp.265.1202820029'
SIZE 247463936 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER
TABLESPACE TUNER_TEMP ADD TEMPFILE
'+DATA1_NEW/ORA19R/TEMPFILE/tuner_temp.273.1203843951'
SIZE 67108864 REUSE
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;
--
End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- 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.
-- 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_NEW/ORA19R/ONLINELOG/group_1.258.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019',
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021'
) SIZE 200M BLOCKSIZE 512
--
STANDBY LOGFILE
DATAFILE
'+DATA1_NEW/ORA19R/DATAFILE/system.264.1202825797',
'+DATA1_NEW/ORA19R/DATAFILE/tuner_data1.271.1203843947',
'+DATA1_NEW/ORA19R/DATAFILE/sysaux.265.1202825873',
'+DATA1_NEW/ORA19R/DATAFILE/undotbs1.266.1202826029',
'+DATA1_NEW/ORA19R/DATAFILE/undotbs2.267.1202826057',
'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071',
'+DATA1_NEW/ORA19R/DATAFILE/tuner_idx1.272.1203843949'
CHARACTER SET KO16MSWIN949
;
-- Configure
snapshot controlfile filename
EXECUTE
SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+DATA1_NEW/ORA19R/CONTROLFILE/snapcf_ca.f');
-- 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 6
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO
''+DATA1_NEW/ORA19R/CONTROLFILE/snapcf_CA.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 '+FRA1_NEW';
-- ALTER DATABASE REGISTER
LOGFILE '+FRA1_NEW';
-- 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_NEW/ORA19R/ONLINELOG/group_4.260.1202820853',
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853'
) SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 5 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855',
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855'
) SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 6 (
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857',
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857'
) SIZE 200M BLOCKSIZE 512 REUSE;
-- Database can
now be opened zeroing the online logs.
ALTER DATABASE
OPEN RESETLOGS;
-- Commands to add
tempfiles to temporary tablespaces.
-- Online tempfiles
have complete space information.
-- Other tempfiles may
require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
'+FRA1_NEW/ORA19R/TEMPFILE/temp.265.1202820029'
SIZE 247463936 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER
TABLESPACE TUNER_TEMP ADD TEMPFILE
'+DATA1_NEW/ORA19R/TEMPFILE/tuner_temp.273.1203843951'
SIZE 67108864 REUSE
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;
--
End of tempfile additions.
--
[ORA19R:oracle@tdb01t][/home/oracle]$
8. 타켓에서 nomount로 인스턴스 시작
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ export
ORACLE_SID=ORA19R
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ alias
ss
alias ss='rlwrap sqlplus "/as
sysdba"'
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sun Jun 15 10:05:19 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
[2025-06-11
22:59:07][]<ORA19R@SYS> startup nomount
pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora'
ORACLE instance
started.
Total System
Global Area 5167379008 bytes
Fixed
Size
8950336 bytes
Variable
Size
922746880 bytes
Database
Buffers 4227858432 bytes
Redo
Buffers
7823360 bytes
[ORA19R:oracle@tdb01t][/u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace]$
tail -f
/u01/app/oracle/diag/rdbms/ora19r/ORA19R/trace/alert_ORA19R.log
SMMNL started with pid=33, OS id=1424
2025-06-15T10:05:54.978891+09:00
starting up 1 dispatcher(s) for network address
'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1
shared server(s) ...
Starting background process TMON
2025-06-15T10:05:55.035517+09:00
TMON started with pid=36, OS id=1433
ORACLE_BASE from environment = /u01/app/oracle
2025-06-15T10:05:56.739249+09:00
Using default pga_aggregate_limit of 4296 MB
-->
alert log 모니터링 시작
9. 타켓에서 restore
controlfile
[ORA19R:oracle@tdb01t][/home/oracle]$ export
ORACLE_SID=ORA19R
[ORA19R:oracle@tdb01t][/home/oracle]$
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[ORA19R:oracle@tdb01t][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19R:oracle@tdb01t][/home/oracle]$ rt
Recovery Manager:
Release 19.0.0.0.0 - Production on Sun Jun 15 10:09:24 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19R (not mounted)
RMAN> host 'ls -l
/home/oracle/ORA19R_BACKUP/autobackup/';
host 'ls -l
/home/oracle/ORA19R_BACKUP/autobackup/';
total 78720
-rw-r-----. 1 oracle oinstall 20152320 Jun 15 10:00
c-1824822448-20250615-05
-rw-r-----. 1 oracle oinstall
20152320 Jun 15 10:00 c-1824822448-20250615-06
-rw-r-----. 1 oracle oinstall 20152320 Jun 15 10:00
c-1824822448-20250615-07
-rw-r-----. 1 oracle oinstall
20152320 Jun 15 10:00 c-1824822448-20250615-08 --> 이게
가장 최신에 백업받은 컨트롤 파일임
host command
complete
RMAN> restore controlfile from
'/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-08';
restore
controlfile from '/home/oracle/ORA19R_BACKUP/autobackup/c-1824822448-20250615-08';
Starting restore at 2025-06-15 10:10:18
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=553 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/ORA19R_BACKUP/ORA19R/controlfile/controlfile01.ctl
output file name=/home/oracle/ORA19R_BACKUP/ORA19R/controlfile/controlfile02.ctl
Finished restore at 2025-06-15 10:10:20
RMAN> alter database mount;
alter database
mount;
released channel: ORA_DISK_1
Statement processed
10. 소스에서 rename
redo log 스크립트를 생성하는 sql문 돌리기
SELECT 'alter
database rename file ' ||''''|| member ||''''|| ' ' ||''|| ' to '|| ''''
|| case when member like '+DATA1_NEW%'
then '/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/'
when
member like '+FRA1_NEW%' then
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/' end
|| substr(member, instr(member, 'group'))||'''' ||';'
AS rename_redo_log
FROM v$logfile
;
alter database
rename file '+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_3.259.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_3.264.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_2.257.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_2.262.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_1.258.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_1.258.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_1.263.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_4.260.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_4.260.1202820853';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_4.267.1202820853';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_5.261.1202820855';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_5.268.1202820855';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_6.262.1202820857';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_6.269.1202820857';
--> 이걸 타켓에 가져가서 돌릴 것임
11. 타켓에서 rename redo log 수행
[2025-06-15
10:05:21][]<ORA19R@SYS>
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_3.259.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_3.259.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_3.264.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_3.264.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_2.257.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_2.257.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_2.262.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_2.262.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_1.258.1202820019' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_1.258.1202820019';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_1.263.1202820021' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_1.263.1202820021';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_4.260.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_4.260.1202820853';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_4.267.1202820853' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_4.267.1202820853';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_5.261.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_5.261.1202820855';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_5.268.1202820855' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_5.268.1202820855';
alter database rename file
'+DATA1_NEW/ORA19R/ONLINELOG/group_6.262.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_6.262.1202820857';
alter database rename file
'+FRA1_NEW/ORA19R/ONLINELOG/group_6.269.1202820857' to
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_6.269.1202820857';
[2025-06-15
10:05:21][]<ORA19R@SYS>
col member for
a80
select a.group#, a.thread#, a.bytes/1024/1024 as mb,
a.status, b.member from v$log a, v$logfile b where a.group# =
b.group#;
GROUP# THREAD#
MB
STATUS
MEMBER
---------- ---------- ----------
------------------------------------------------
--------------------------------------------------------------------------------
1
1 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_1.263.1202820021
1
1 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_1.258.1202820019
2
1 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_2.257.1202820019
2
1 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_2.262.1202820021
3
1 200
CURRENT
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_3.264.1202820021
3
1 200
CURRENT
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_3.259.1202820019
4
2 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_4.260.1202820853
4
2 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_4.267.1202820853
5
2 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_5.261.1202820855
5
2 200
ACTIVE
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_5.268.1202820855
6
2 200
CURRENT
/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_6.262.1202820857
6
2 200
CURRENT
/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_6.269.1202820857
12 rows selected.
12. 소스에서 set
newname 스크립트를 생성하는 sql문 및 skip할 테이블 스페이스를 출력하는 sql문 실행
select 'set newname for datafile ' || file_id || ' ' || 'to
' || ''''
||
replace(file_name, '+DATA1_NEW/ORA19R/DATAFILE/',
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/') || '''' || ';' as set_newname
from dba_data_files
where tablespace_name in ('SYSTEM', 'SYSAUX',
'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1');
--> USERS
테이블스페이스는 제외하고 생성
-->
파라미터 파일(pfile) 설정 시 *.db_create_file_dest='/home/oracle/ORA19R_BACKUP' 로 했기 때문에
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/ 위치가 datafile의 위치가 됨
--> db_create_file_dest 위치
아래에 datafile 경로를 추가해서 그 밑에 넣어주게됨
set newname for
datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057';
set newname for datafile 2 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949';
--skip 할 테이블 스페이스를 출력
[2025-06-14:20:28:32][ol7ora19r1]<SYS@ORA19R1> col SKIP_TABLESPACE_NAME for a20
select
listagg(tablespace_name, ',') WITHIN GROUP(ORDER BY TABLESPACE_NAME) as
skip_tablespace_name
from DBA_TABLESPACES
where tablespace_name not in ('SYSTEM', 'SYSAUX',
'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1')
and CONTENTS in
('PERMANENT');
SKIP_TABLESPACE_NAME
--------------------
USERS
13. 타켓에서 resotre database
run
{
allocate channel ch1 device type
disk ;
allocate channel ch2 device type disk
;
set newname for
datafile 1 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797';
set newname for datafile 3 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873';
set newname for datafile 5 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057';
set newname for datafile 2 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947';
set newname for datafile 4 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029';
set newname for datafile 8 to
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949';
restore datafile 1,3,5,2,4,8;
switch datafile 1;
switch datafile 3;
switch
datafile 5;
switch datafile 2;
switch datafile 4;
switch datafile
8;
release channel ch1;
release
channel ch2;
}
--switch datafile all;
---> 이걸로 한번에 다 switch datafile 할 수 있음
allocated channel:
ch1
channel ch1: SID=554 device
type=DISK
allocated channel:
ch2
channel ch2: SID=1098 device
type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-06-15 10:21:56
channel ch1:
starting datafile backup set restore
channel ch1:
specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/SYSTEM_5u3s2di7_190_1_1.bkp
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup
set
channel ch2: restoring datafile 00003 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/SYSAUX_5v3s2dja_191_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/SYSTEM_5u3s2di7_190_1_1.bkp
tag=TAG20250615T091759
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:38
channel ch1: starting datafile backup set
restore
channel ch1: specifying datafile(s) to restore
from backup set
channel ch1: restoring datafile 00004 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/UNDOTBS1_603s2dk5_192_1_1.bkp
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/SYSAUX_5v3s2dja_191_1_1.bkp
tag=TAG20250615T091834
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:38
channel ch2: starting datafile backup set
restore
channel ch2: specifying datafile(s) to restore
from backup set
channel ch2: restoring datafile 00005 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/UNDOTBS2_623s2dka_194_1_1.bkp
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS2_623s2dka_194_1_1.bkp
tag=TAG20250615T091906
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:01
channel ch2: starting datafile backup set
restore
channel ch2: specifying datafile(s) to restore
from backup set
channel ch2: restoring datafile 00002 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
channel ch2: reading from backup piece
/home/oracle/ORA19R_BACKUP/TUNER_DATA1_633s2dkc_195_1_1.bkp
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/UNDOTBS1_603s2dk5_192_1_1.bkp
tag=TAG20250615T091900
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:08
channel ch1: starting datafile backup set
restore
channel ch1: specifying datafile(s) to restore
from backup set
channel ch1: restoring datafile 00008 to
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
channel ch1: reading from backup piece
/home/oracle/ORA19R_BACKUP/TUNER_IDX1_643s2dkl_196_1_1.bkp
channel ch2: piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_DATA1_633s2dkc_195_1_1.bkp
tag=TAG20250615T091907
channel ch2: restored backup
piece 1
channel ch2: restore complete, elapsed time:
00:00:15
channel ch1: piece
handle=/home/oracle/ORA19R_BACKUP/TUNER_IDX1_643s2dkl_196_1_1.bkp
tag=TAG20250615T091916
channel ch1: restored backup
piece 1
channel ch1: restore complete, elapsed time:
00:00:15
Finished restore at 2025-06-15
10:23:02
datafile 1
switched to datafile copy
input datafile copy RECID=8
STAMP=1203848582 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
datafile 3
switched to datafile copy
input datafile copy RECID=9
STAMP=1203848582 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
datafile 5
switched to datafile copy
input datafile copy RECID=10
STAMP=1203848583 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
datafile 2
switched to datafile copy
input datafile copy RECID=11
STAMP=1203848583 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
datafile 4
switched to datafile copy
input datafile copy RECID=12
STAMP=1203848583 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
datafile 8
switched to datafile copy
input datafile copy RECID=13
STAMP=1203848583 file
name=/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
released channel: ch1
released channel: ch2
[2025-06-15 10:05:21][]<ORA19R@SYS>
col name for a80
SELECT FILE#,
STATUS, NAME FROM V$DATAFILE;
FILE#
STATUS
NAME
---------- ---------------------
--------------------------------------------------------------------------------
1
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
2
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
3
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
5
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
7
ONLINE
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
7 rows selected.
-->
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071 이게 아직 살아 있음 이걸 offline 시켜야함
14. 소스에서 복구 대상이 아닌
테이블 스페이스에 대한 offline drop 문을 작성하는 스크립트 실행
select 'alter database datafile' || '''' ||FILE_NAME || '''' || ' offline drop;' as offline_drop from dba_data_files where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'UNDOTBS2', 'TUNER_DATA1', 'TUNER_IDX1');
OFFLINE_DROP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database
datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline
drop;
15. 타켓에서 복구 대상이 아닌 테이블 스페이스 offline
RMAN> alter
database datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline
drop;
alter database
datafile'+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071' offline drop;
Statement processed
[2025-06-15 10:05:21][]<ORA19R@SYS>
col name for a80
SELECT FILE#, STATUS, NAME FROM
V$DATAFILE;
FILE#
STATUS
NAME
---------- ---------------------
--------------------------------------------------------------------------------
1
SYSTEM
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
2
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
3
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
4
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
5
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
7
RECOVER
+DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071
8
ONLINE
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
7 rows selected.
Elapsed: 00:00:00.01
--> +DATA1_NEW/ORA19R/DATAFILE/users.268.1202826071 의 상태가 recover로 되어 있음
7 rows selected.
16. 타켓에서 catalog
start with 수행 후 아카이브 로그를 복원함
RMAN> list
backup of archivelog all;
list backup of archivelog all;
List of Backup
Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ -------------------
186 485.50K
DISK 00:00:00
2025-06-15 09:20:51
BP Key:
186 Status: AVAILABLE Compressed: NO Tag:
TAG20250615T092051
Piece Name:
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
List of
Archived Logs in backup set 186
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 167 6562051
2025-06-15 09:17:49 6563579 2025-06-15 09:20:49
2 92
6562055 2025-06-15 09:17:50 6563583
2025-06-15 09:20:50
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
188
128.50K DISK
00:00:00 2025-06-15 09:53:47
BP Key:
188 Status: AVAILABLE Compressed: YES Tag:
TAG20250615T095346
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
List of
Archived Logs in backup set 188
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
2 93 6563583
2025-06-15 09:20:50 6565985 2025-06-15 09:47:08
2 94
6565985 2025-06-15 09:47:08 6566318
2025-06-15 09:53:42
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
190
871.50K DISK
00:00:00 2025-06-15 09:54:19
BP Key:
190 Status: AVAILABLE Compressed: YES Tag:
TAG20250615T095419
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
List of
Archived Logs in backup set 190
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 168 6563579
2025-06-15 09:20:49 6565981 2025-06-15 09:47:07
1 169
6565981 2025-06-15 09:47:07 6566315
2025-06-15 09:53:42
1
170 6566315 2025-06-15 09:53:42
6566384 2025-06-15 09:54:19
--> 필요한 아카이브 로그를 모두 제대로
인식하고 있음
RMAN> catalog
start with '/home/oracle/ORA19R_BACKUP';
--> 이 작업을 할 필요가 없음 (모두 제대로 인식하고 있으니 할 필요가 없다는
것임)
--> 만약 직접 아카이브 로그를 restore
시킨다면
START------------------------------------------------------------------------------------
--(recover 할 경우
아카이브를 restore한 후 recover함, 그럼에도 불구하고 아카이브로그를 archive log dest로 restore 시켜놓고
싶다면)
RMAN> restore
archivelog sequence <시퀀스번호> thread <스레드번호>;
--> 만약 직접 아카이브 로그를 restore 시킨다면
END------------------------------------------------------------------------------------
17. 불완전 복구 수행 (RECOVER 수행)
--컨트롤 파일 재생성 방식으로 recover 할 것임
RMAN> shutdown
immediate;
shutdown immediate;
database
dismounted
Oracle instance shut down
--소스에서 아래의 sql문 실행
SELECT group#
, listagg(''''|| case when member
like '+DATA1_NEW%' then '/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/'
when member like '+FRA1_NEW%' then
'/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/' end ||substr(member,
instr(member, 'group'))||'''', ',') as redo_log_controlfile
FROM v$logfile
group by group#
order
by group#
;
SELECT
listagg('''' || replace(file_name, '+DATA1_NEW/ORA19R/DATAFILE/',
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/') || '''' || chr(10), ',') AS
set_newname
FROM dba_data_files
WHERE tablespace_name IN ('SYSTEM'
, 'SYSAUX'
, 'UNDOTBS1'
, 'UNDOTBS2'
, 'TUNER_DATA1'
, 'TUNER_IDX1');
--> 컨트롤 파일 생성 스크립트를
위한 SQL문임
--타켓에서 nomount 로 올림
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sun Jun 15 11:51:12 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
[2025-06-15 11:51:13][]<ORA19R@SYS> startup nomount
pfile='/home/oracle/ORA19R_BACKUP/initORA19R.ora';
ORACLE instance
started.
Total System
Global Area 5167379008 bytes
Fixed
Size
8950336 bytes
Variable
Size
922746880 bytes
Database
Buffers 4227858432 bytes
Redo
Buffers
7823360 bytes
CREATE CONTROLFILE
REUSE DATABASE "ORA19R" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_1.258.1202820019','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_1.263.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_2.257.1202820019','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_2.262.1202820021'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_3.259.1202820019','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_3.264.1202820021'
) SIZE 200M BLOCKSIZE 512
DATAFILE
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797'
,'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873'
,'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057'
,'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947'
,'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029'
,'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949'
CHARACTER SET KO16MSWIN949
;
ALTER DATABASE ADD
LOGFILE THREAD 2
GROUP 4 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_4.260.1202820853','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_4.267.1202820853'
) SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 5 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_5.261.1202820855','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_5.268.1202820855'
) SIZE 200M BLOCKSIZE 512 REUSE,
GROUP 6 (
'/home/oracle/ORA19R_BACKUP/redolog_1/onlinelog/group_6.262.1202820857','/home/oracle/ORA19R_BACKUP/redolog_2/onlinelog/group_6.269.1202820857'
) SIZE 200M BLOCKSIZE 512 REUSE;
[ORA19R:oracle@tdb01t][/home/oracle]$
rt
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ for f
in *; do echo "$(pwd)/$f"; done
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
/home/oracle/ORA19R_BACKUP/autobackup
/home/oracle/ORA19R_BACKUP/initORA19R.ora
/home/oracle/ORA19R_BACKUP/ORA19R
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
/home/oracle/ORA19R_BACKUP/ORA19R_CREATE_CONTROLFILE.sql
/home/oracle/ORA19R_BACKUP/redolog_1
/home/oracle/ORA19R_BACKUP/redolog_2
/home/oracle/ORA19R_BACKUP/SYSAUX_5v3s2dja_191_1_1.bkp
/home/oracle/ORA19R_BACKUP/SYSTEM_5u3s2di7_190_1_1.bkp
/home/oracle/ORA19R_BACKUP/TUNER_DATA1_633s2dkc_195_1_1.bkp
/home/oracle/ORA19R_BACKUP/TUNER_IDX1_643s2dkl_196_1_1.bkp
/home/oracle/ORA19R_BACKUP/UNDOTBS1_603s2dk5_192_1_1.bkp
/home/oracle/ORA19R_BACKUP/UNDOTBS2_623s2dka_194_1_1.bkp
/home/oracle/ORA19R_BACKUP/USERS_613s2dk8_193_1_1.bkp
RMAN> catalog
start with
'/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp';
catalog start with
'/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp';
searching for all files that match the pattern
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
List of Files
Unknown to the Database
=====================================
File Name:
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
Do you really want
to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging
done
List of Cataloged
Files
=======================
File Name:
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
RMAN> catalog start with
'/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp';
catalog start with
'/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp';
searching for all files that match the pattern
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
List of Files
Unknown to the Database
=====================================
File Name:
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
Do you really want
to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging
done
List of Cataloged
Files
=======================
File Name:
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
RMAN> catalog start with
'/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1';
catalog start with
'/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1';
searching for all files that match the pattern
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
List of Files
Unknown to the Database
=====================================
File Name:
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
Do you really want
to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging
done
List of Cataloged
Files
=======================
File Name:
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
RMAN> list backup of archivelog all;
list backup of
archivelog all;
List of Backup
Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ -------------------
1
871.50K DISK
00:00:00 2025-06-15 09:54:19
BP Key:
1 Status: AVAILABLE Compressed: YES Tag:
TAG20250615T095419
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
List of
Archived Logs in backup set 1
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 168 6563579
2025-06-15 09:20:49 6565981 2025-06-15 09:47:07
1 169
6565981 2025-06-15 09:47:07 6566315
2025-06-15 09:53:42
1
170 6566315 2025-06-15 09:53:42
6566384 2025-06-15 09:54:19
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
2
128.50K DISK
00:00:00 2025-06-15 09:53:47
BP Key:
2 Status: AVAILABLE Compressed: YES Tag:
TAG20250615T095346
Piece Name:
/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
List of
Archived Logs in backup set 2
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
2 93 6563583
2025-06-15 09:20:50 6565985 2025-06-15 09:47:08
2 94
6565985 2025-06-15 09:47:08 6566318
2025-06-15 09:53:42
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
3
485.50K DISK
00:00:00 2025-06-15 09:20:51
BP Key:
3 Status: AVAILABLE Compressed: NO Tag:
TAG20250615T092051
Piece Name:
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
List of
Archived Logs in backup set 3
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 167 6562051
2025-06-15 09:17:49 6563579 2025-06-15 09:20:49
2 92
6562055 2025-06-15 09:17:50 6563583
2025-06-15 09:20:50
[2025-06-15 11:51:13][]<ORA19R@SYS>
SET LINESIZE
300
COL
NAME
FOR A10
COL
OPEN_MODE FOR A15
COL DATABASE_ROLE FOR
A20
COL RESETLOGS_CHANGE# FOR
999999999999999
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CURRENT_SCN FOR 999999999999999
SELECT
NAME
, OPEN_MODE
, DATABASE_ROLE
, RESETLOGS_CHANGE#
, CHECKPOINT_CHANGE#
, CURRENT_SCN
FROM
V$DATABASE;
NAME
OPEN_MODE
DATABASE_ROLE RESETLOGS_CHANGE#
CHECKPOINT_CHANGE# CURRENT_SCN
---------- --------------- --------------------
----------------- ------------------ ----------------
ORA19R
MOUNTED
PRIMARY
1920977
0
0
1 row selected.
Elapsed: 00:00:00.00
--> 컨트롤 파일 재생성한 상태이기 때문에
CHECKPOINT_CHANGE# 과 CURRENT_SCN이 모두 0임
[2025-06-15 11:51:13][]<ORA19R@SYS>
SET LINESIZE
200
COL
FILE#
FOR 9999
COL
NAME
FOR A80
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CHECKPOINT_TIME FOR A20
COL
STATUS
FOR A10
SELECT
FILE#
, NAME
,
CHECKPOINT_CHANGE#
,
TO_CHAR(CHECKPOINT_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CHECKPOINT_TIME
, STATUS
FROM
V$DATAFILE_HEADER
ORDER BY
FILE#;
FILE#
NAME
CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-----
--------------------------------------------------------------------------------
------------------ -------------------- ----------
1
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
6562138 2025-06-15 09:17:59 ONLINE
2
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
6563182 2025-06-15 09:19:08 ONLINE
3
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
6562952 2025-06-15 09:18:35 ONLINE
4
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
6563142 2025-06-15 09:19:01 ONLINE
5
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
6563169 2025-06-15 09:19:06 ONLINE
8
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
6563199 2025-06-15 09:19:17 ONLINE
--> 불완전 복구 목표 SCN인 6565835까지 만들어야 하는 것임
--드디어 RECOVER 작업 시작
(TUNER.TB_CUST 테이블 PURGE 직전으로 복구)
RMAN> recover
database until scn = 6565835;
Starting recover
at 2025-06-15 12:05:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device
type=DISK
starting media recovery
channel
ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=167
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=92
channel ORA_DISK_1: reading from backup piece
/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
tag=TAG20250615T092051
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/1_167_1202820019.dbf thread=1
sequence=167
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/2_92_1202820019.dbf thread=2
sequence=92
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=1
sequence=168
channel ORA_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/arch_t1_s168_6a3s2fmb_202_1_1.bkp
tag=TAG20250615T095419
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:02
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/1_168_1202820019.dbf thread=1
sequence=168
channel ORA_DISK_1: starting archived log
restore to default destination
channel ORA_DISK_1:
restoring archived log
archived log thread=2
sequence=93
channel ORA_DISK_1: reading from backup
piece /home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/ORA19R_BACKUP/arch_t2_s93_683s2flb_200_1_1.bkp
tag=TAG20250615T095346
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
archived log file
name=/home/oracle/ORA19R_BACKUP/ORA19R/ARCH/2_93_1202820019.dbf thread=2
sequence=93
media recovery complete, elapsed time:
00:00:01
Finished recover at 2025-06-15
12:05:55
[2025-06-15 11:51:13][]<ORA19R@SYS>
SET LINESIZE
300
COL
NAME
FOR A10
COL
OPEN_MODE FOR A15
COL DATABASE_ROLE FOR
A20
COL RESETLOGS_CHANGE# FOR
999999999999999
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CURRENT_SCN FOR 999999999999999
SELECT
NAME
, OPEN_MODE
, DATABASE_ROLE
, RESETLOGS_CHANGE#
, CHECKPOINT_CHANGE#
, CURRENT_SCN
FROM
V$DATABASE;
NAME
OPEN_MODE
DATABASE_ROLE RESETLOGS_CHANGE#
CHECKPOINT_CHANGE# CURRENT_SCN
---------- --------------- --------------------
----------------- ------------------ ----------------
ORA19R
MOUNTED
PRIMARY
1920977
0
0
[2025-06-15 11:51:13][]<ORA19R@SYS>
SET LINESIZE 200
COL
FILE#
FOR 9999
COL
NAME
FOR A80
COL CHECKPOINT_CHANGE# FOR
999999999999999
COL
CHECKPOINT_TIME FOR A20
COL
STATUS
FOR A10
SELECT
FILE#
, NAME
,
CHECKPOINT_CHANGE#
,
TO_CHAR(CHECKPOINT_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CHECKPOINT_TIME
, STATUS
FROM
V$DATAFILE_HEADER
ORDER BY
FILE#;
FILE#
NAME
CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-----
--------------------------------------------------------------------------------
------------------ -------------------- ----------
1
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
6565835 2025-06-15 09:45:40 ONLINE
2
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
6565835 2025-06-15 09:45:40 ONLINE
3
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
6565835 2025-06-15 09:45:40 ONLINE
4
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
6565835 2025-06-15 09:45:40 ONLINE
5
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
6565835 2025-06-15 09:45:40 ONLINE
8
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
6565835 2025-06-15 09:45:40 ONLINE
--> 모든 datafile이 scn번호 6565835 까지 제대로 recover됨
6 rows selected.
Elapsed: 00:00:00.01
RMAN> alter database open resetlogs;
alter database
open resetlogs;
Statement processed
RMAN> select
count(*) from tuner.tb_cust;
select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
[2025-06-14 21:31:52][]<ORA19R@SYS> select current_Scn from v$database;
CURRENT_SCN
----------------
6566719
1 row selected.
Elapsed: 00:00:00.00
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ ls
-lRa
.:
total 785700
drwxr-xr-x. 6
oracle oinstall 4096 Jun 15 10:04 .
drwx------. 11 oracle
oinstall 4096 Jun 15 10:13 ..
-rw-r-----. 1 oracle oinstall
892928 Jun 15 10:00 arch_t1_s168_6a3s2fmb_202_1_1.bkp
-rw-r-----. 1 oracle oinstall
132096 Jun 15 10:00 arch_t2_s93_683s2flb_200_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 10:00 autobackup
-rw-r--r--. 1 oracle
oinstall 1026 Jun 15 10:04 initORA19R.ora
drwxr-xr-x. 5 oracle
oinstall 53 Jun 15 10:10 ORA19R
-rw-r-----. 1 oracle oinstall
497664 Jun 15 10:00 ORA19R_ARCHIVE_20250615_663s2dnj_s198_p1
-rw-r--r--. 1 oracle oinstall
13136 Jun 15 10:00 ORA19R_CREATE_CONTROLFILE.sql
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 redolog_1
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 redolog_2
-rw-r-----. 1 oracle oinstall 253968384 Jun 15 10:00
SYSAUX_5v3s2dja_191_1_1.bkp
-rw-r-----. 1 oracle
oinstall 457981952 Jun 15 10:00 SYSTEM_5u3s2di7_190_1_1.bkp
-rw-r-----. 1 oracle oinstall 68919296 Jun 15
10:00 TUNER_DATA1_633s2dkc_195_1_1.bkp
-rw-r-----.
1 oracle oinstall 13737984 Jun 15 10:00 TUNER_IDX1_643s2dkl_196_1_1.bkp
-rw-r-----. 1 oracle oinstall 2293760 Jun
15 10:00 UNDOTBS1_603s2dk5_192_1_1.bkp
-rw-r-----.
1 oracle oinstall 4849664 Jun 15 10:00
UNDOTBS2_623s2dka_194_1_1.bkp
-rw-r-----. 1 oracle
oinstall 1245184 Jun 15 10:00
USERS_613s2dk8_193_1_1.bkp
./autobackup:
total 78728
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 10:00 .
drwxr-xr-x. 6 oracle oinstall 4096
Jun 15 10:04 ..
-rw-r-----. 1 oracle oinstall 20152320
Jun 15 10:00 c-1824822448-20250615-05
-rw-r-----. 1
oracle oinstall 20152320 Jun 15 10:00 c-1824822448-20250615-06
-rw-r-----. 1 oracle oinstall 20152320 Jun 15 10:00
c-1824822448-20250615-07
-rw-r-----. 1 oracle oinstall
20152320 Jun 15 10:00 c-1824822448-20250615-08
./ORA19R:
total 12
drwxr-xr-x. 5 oracle
oinstall 53 Jun 15 10:10 .
drwxr-xr-x. 6
oracle oinstall 4096 Jun 15 10:04 ..
drwxr-xr-x. 2
oracle oinstall 4096 Jun 15 12:08 ARCH
drwxr-x---. 2
oracle asmadmin 56 Jun 15 10:10 controlfile
drwxr-xr-x. 2 oracle oinstall 4096 Jun 15 10:22
datafile
./ORA19R/ARCH:
total 1440
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 12:08 .
drwxr-xr-x. 5
oracle oinstall 53 Jun 15 10:10 ..
-rw-r-----. 1 oracle asmadmin 70144 Jun 15 12:05
1_167_1202820019.dbf
-rw-r-----. 1 oracle asmadmin
855552 Jun 15 12:05 1_168_1202820019.dbf
-rw-r-----. 1
oracle asmadmin 1024 Jun 15 12:08 2_1_1203854880.dbf
-rw-r-----. 1 oracle asmadmin 423936 Jun 15 12:05
2_92_1202820019.dbf
-rw-r-----. 1 oracle asmadmin 108544
Jun 15 12:05 2_93_1202820019.dbf
./ORA19R/controlfile:
total
39136
drwxr-x---. 2 oracle
asmadmin 56 Jun 15 10:10 .
drwxr-xr-x. 5 oracle
oinstall 53 Jun 15 10:10 ..
-rw-r-----. 1 oracle asmadmin 20037632 Jun 15 12:09
controlfile01.ctl
-rw-r-----. 1 oracle asmadmin 20037632
Jun 15 12:09 controlfile02.ctl
./ORA19R/datafile:
total 5298228
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 10:22 .
drwxr-xr-x. 5 oracle
oinstall 53 Jun 15 10:10 ..
-rw-r-----. 1 oracle asmadmin 1981816832 Jun 15 12:08
sysaux.265.1202825873
-rw-r-----. 1 oracle asmadmin
1216356352 Jun 15 12:08 system.264.1202825797
-rw-r-----. 1 oracle asmadmin 641736704 Jun 15 12:08
tuner_data1.271.1203843947
-rw-r-----. 1 oracle
asmadmin 641736704 Jun 15 12:08 tuner_idx1.272.1203843949
-rw-r-----. 1 oracle asmadmin 865083392 Jun 15 12:08
undotbs1.266.1202826029
-rw-r-----. 1 oracle
asmadmin 78651392 Jun 15 12:08
undotbs2.267.1202826057
./redolog_1:
total 8
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 .
drwxr-xr-x. 6
oracle oinstall 4096 Jun 15 10:04 ..
drwxr-xr-x. 2
oracle oinstall 4096 Jun 15 12:08 onlinelog
drwxr-x---.
3 oracle asmadmin 23 Jun 15 12:08 ORA19R
./redolog_1/onlinelog:
total
1228828
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 12:08 .
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 ..
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_1.258.1202820019
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_2.257.1202820019
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_3.259.1202820019
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_4.260.1202820853
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_5.261.1202820855
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_6.262.1202820857
./redolog_1/ORA19R:
total 0
drwxr-x---. 3 oracle asmadmin 23 Jun 15 12:08 .
drwxr-xr-x. 4 oracle oinstall 37 Jun 15 12:08 ..
drwxr-x---. 2 oracle asmadmin 64 Jun 15 12:08
onlinelog
./redolog_1/ORA19R/onlinelog:
total
204808
drwxr-x---. 2 oracle
asmadmin 64 Jun 15 12:08 .
drwxr-x---. 3 oracle
asmadmin 23 Jun 15 12:08 ..
-rw-r-----. 1 oracle asmadmin 104858112 Jun 15 12:08
o1_mf_7_n4wg8yf3_.log
-rw-r-----. 1 oracle asmadmin
104858112 Jun 15 12:08 o1_mf_8_n4wg8yqj_.log
./redolog_2:
total 8
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 .
drwxr-xr-x. 6
oracle oinstall 4096 Jun 15 10:04 ..
drwxr-xr-x. 2
oracle oinstall 4096 Jun 15 12:08 onlinelog
drwxr-x---.
3 oracle asmadmin 23 Jun 15 12:08 ORA19R
./redolog_2/onlinelog:
total
1228828
drwxr-xr-x. 2 oracle
oinstall 4096 Jun 15 12:08 .
drwxr-xr-x. 4 oracle
oinstall 37 Jun 15 12:08 ..
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_1.263.1202820021
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_2.262.1202820021
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_3.264.1202820021
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_4.267.1202820853
-rw-r-----. 1 oracle asmadmin 209715712 Jun 15 12:08
group_5.268.1202820855
-rw-r-----. 1 oracle asmadmin
209715712 Jun 15 12:08 group_6.269.1202820857
./redolog_2/ORA19R:
total 0
drwxr-x---. 3 oracle asmadmin 23 Jun 15 12:08 .
drwxr-xr-x. 4 oracle oinstall 37 Jun 15 12:08 ..
drwxr-x---. 2 oracle asmadmin 64 Jun 15 12:08
onlinelog
./redolog_2/ORA19R/onlinelog:
total
204808
drwxr-x---. 2 oracle
asmadmin 64 Jun 15 12:08 .
drwxr-x---. 3 oracle
asmadmin 23 Jun 15 12:08 ..
-rw-r-----. 1 oracle asmadmin 104858112 Jun 15 12:08
o1_mf_7_n4wg8yk5_.log
-rw-r-----. 1 oracle asmadmin
104858112 Jun 15 12:08 o1_mf_8_n4wg8yxw_.log
[+ASM:grid@tdb01t][/home/grid]$ lsnrctl status
LISTENER_ORA19R
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 14-JUN-2025 23:59:05
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA19R)))
STATUS of the LISTENER
------------------------
Alias
LISTENER_ORA19R
Version
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start
Date
14-JUN-2025 21:08:13
Uptime
0 days 2 hr. 50 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/tdb01t/listener_ora19r/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA19R)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1525)))
Services Summary...
Service
"ORA19R" has 1 instance(s).
Instance "ORA19R",
status READY, has 1 handler(s) for this service...
Service "ORA19RXDB" has 1 instance(s).
Instance "ORA19R", status READY, has 1 handler(s)
for this service...
The command completed
successfully
--client에서 아래의 tnsname 정보 추가
TDB01T_ORA19R =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.51)(PORT = 1525))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA19R)
)
)
--소스에서 orapw 파일이 어딨는지
조회 한 후 타켓으로 복사 시킴
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ srvctl config
database -db ora19r
Database unique name: ORA19R
Database name:
Oracle home:
/u01/app/oracle/product/19c/db_1
Oracle user: oracle
Spfile:
+DATA1_NEW/ORA19R/PARAMETERFILE/spfile.270.1203101939
Password file:
+DATA1_NEW/ORA19R/PASSWORD/orapwora19r
Domain:
Start options: open
Stop options:
immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server
pools:
Disk Groups: DATA1_NEW,FRA1_NEW
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group:
dba
OSOPER group: oper
Database
instances: ORA19R1,ORA19R2
Configured nodes:
ol7ora19r1,ol7ora19r2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network
number for database services:
Database is administrator
managed
[+ASM1:grid@ol7ora19r1][/home/grid]$ asmcmd cp
+DATA1_NEW/ORA19R/PASSWORD/orapwora19r /tmp/orapwora19r
copying
+DATA1_NEW/ORA19R/PASSWORD/orapwora19r -> /tmp/orapwora19r
[+ASM1:grid@ol7ora19r1][/home/grid]$ chmod 775
/tmp/orapwora19r
[ORA19R1:oracle@ol7ora19r1][/home/oracle]$ scp
/tmp/orapwora19r
192.168.0.51:/u01/app/oracle/product/19c/db_1/dbs/orapwORA19R
oracle@192.168.0.51's password:
'
orapwora19r 100%
6144 1.3MB/s
00:00
--> 오렌지 등의 툴에서 syssdba로 붙는지 확인
-- 템프 테이블 스페이스 추가
ALTER TABLESPACE
TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/temp.265.1202820029'
SIZE 247463936 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER
TABLESPACE TUNER_TEMP ADD TEMPFILE
'/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_temp.273.1203636437'
SIZE 67108864 REUSE
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M;
[2025-06-15:12:19:50][tdb01t]<ORA19R@SYS>
col name for a80
select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/temp.265.1202820029
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_temp.273.1203636437
2 rows selected.
[2025-06-15:12:19:50][tdb01t]<ORA19R@SYS>
col name for a80
select name from
v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/system.264.1202825797
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_data1.271.1203843947
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/sysaux.265.1202825873
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs1.266.1202826029
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/undotbs2.267.1202826057
/u01/app/oracle/product/19c/db_1/dbs/MISSING00007
/home/oracle/ORA19R_BACKUP/ORA19R/datafile/tuner_idx1.272.1203843949
7 rows selected.
--> 스킵한 테이블 스페이스는 MISSING으로 남아있음
18. 복구한
tuner.tb_cust 테이블 export하기
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS> create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';
Directory created.
Elapsed: 00:00:00.03
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS> grant read, write on directory tuner_datapump_dir to tuner;
Grant succeeded.
Elapsed: 00:00:00.01
[2025-06-14:22:48:29][tdb01t]<ORA19R@SYS> quit
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$ expdp tuner/oracle tables='TB_CUST' directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log
Export: Release
19.0.0.0.0 - Production on Sun Jun 15 12:38:40 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "TUNER"."SYS_EXPORT_TABLE_01":
tuner/******** tables=TB_CUST directory=tuner_datapump_dir
dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object
type TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/COMMENT
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported
"TUNER"."TB_CUST"
123.5 MB 1000000 rows
Master table
"TUNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TUNER.SYS_EXPORT_TABLE_01 is:
/home/oracle/ORA19R_BACKUP/datapump/tuner_tb_cust.dmp
Job "TUNER"."SYS_EXPORT_TABLE_01" successfully completed at
Sun Jun 15 12:38:54 2025 elapsed 0 00:00:12
*/
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP/datapump]$
ls -l /home/oracle/ORA19R_BACKUP/datapump
total 126700
-rw-r-----. 1 oracle asmadmin 129736704 Jun 15 12:38
tuner_tb_cust.dmp
-rw-r--r--. 1 oracle
asmadmin 1329 Jun 15 12:38
tuner_tb_cust.log
[ORA19R:oracle@tdb01t][/home/oracle/ORA19R_BACKUP]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 22:51:29 2025
Version 19.26.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.26.0.0.0
[2025-06-15:12:40:39][tdb01t]<ORA19R@SYS> drop directory tuner_datapump_dir;
Directory dropped.
Elapsed: 00:00:00.09
19. 타켓에서 export 한
tuner.tb_cust dmp 파일을 소스에서 import 하기
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
mkdir -p /home/oracle/ORA19R_BACKUP/datapump
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
scp 192.168.0.51:/home/oracle/ORA19R_BACKUP/datapump/tuner_tb_cust.dmp
/home/oracle/ORA19R_BACKUP/datapump/tuner_tb_cust.dmp
oracle@192.168.0.51's password:
tuner_tb_cust.dmp 100% 124MB
23.8MB/s 00:05
'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$ ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Jun 14 22:58: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
[2025-06-15:12:42:37][ol7ora19r1]<SYS@ORA19R1> create directory tuner_datapump_dir as '/home/oracle/ORA19R_BACKUP/datapump';
Directory created.
Elapsed: 00:00:00.03
[2025-06-15:12:42:37][ol7ora19r1]<SYS@ORA19R1> grant read, write on directory tuner_datapump_dir to
tuner;
Grant succeeded.
Elapsed: 00:00:00.03
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP]$
impdp tuner/oracle tables='TB_CUST' directory=tuner_datapump_dir
dumpfile=tuner_tb_cust.dmp logfile=tuner_tb_cust_import.log
Import: Release
19.0.0.0.0 - Production on Sun Jun 15 12:43:00 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "TUNER"."SYS_IMPORT_TABLE_01" successfully
loaded/unloaded
Starting
"TUNER"."SYS_IMPORT_TABLE_01": tuner/******** tables=TB_CUST
directory=tuner_datapump_dir dumpfile=tuner_tb_cust.dmp
logfile=tuner_tb_cust_import.log
Processing object type
TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/TABLE_DATA
. . imported
"TUNER"."TB_CUST"
123.5 MB 1000000 rows
Processing object type
TABLE_EXPORT/TABLE/COMMENT
Processing object type
TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing
object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job
"TUNER"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jun 15 12:43:40 2025
elapsed 0 00:00:37
*/
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP/datapump]$
alias st
alias st='rlwrap sqlplus
tuner/oracle'
[ORA19R1:oracle@ol7ora19r1][/home/oracle/ORA19R_BACKUP/datapump]$
st
SQL*Plus: Release
19.0.0.0.0 - Production on Sun Jun 15 12:44:08 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sun Jun 15 2025 12:43:00 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.27.0.0.0
[2025-06-15:12:44:08][ol7ora19r1]<TUNER@ORA19R1> select count(*) from tuner.tb_cust;
COUNT(*)
----------
1000000
1 row selected.
Elapsed: 00:00:01.12
--> tuner.tb_cust 테이블 복구 성공
[2025-06-15:12:44:08][ol7ora19r1]<TUNER@ORA19R1> drop directory tuner_datapump_dir;
Directory dropped.
Elapsed:
00:00:00.08