반응형
■ [2025-10-30] NOLOGGING+APPEND 데이터 입력 후 원격지 서버에 DB 복구 후 ORA-1578/ORA-26040 발생 재현

 

[제목]

 

[2025-10-30] NOLOGGING+APPEND 데이터 입력 후 원격지 서버에 DB 복구 후 ORA-1578/ORA-26040 발생 재현
   
[테스트 개요]

 

풀백업 후 NOLOGGING APPEND 로 데이터 입력을 수행(Redo Log가 남지 않음)
그 후 원격지 서버에서 해당 시점의 아카이브를 적용하여 Recover한 Clone DB에서 대상 테이블 조회 시 ORA-1578/26040이 발생하는 것을 재현/확인
우회 방안으로 테이블을 정상화 시킴

 

[테스트 환경]

 

<소스 DB>

 

OS : Oracle Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname -r)
Oracle Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from v\$version where banner like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle Configuration
     DB명 : ORA19RF (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' | sqlplus -s / as sysdba) '
 PDB명 : non-CDB (echo -e "set pages 0 feedback off heading off verify off\nselect 'PDB명 : '||name from v\$pdbs where name <> 'PDB\$SEED';" | sqlplus -s / as sysdba)
  RAC Node 1
   Hostname : ol8ora19rf1 (hostname)
   Public IP : 192.168.240.31 (getent ahostsv4 `hostname` | awk '{print $1; exit}')
   Instance Name : ORA19RF1 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba) '
  RAC Node 2
   Hostname : ol8ora19rf2
   Public IP : 192.168.240.32 (getent ahostsv4 `hostname` | awk '{print $1; exit}')
   Instance Name : ORA19RF2 (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba) '
Patch Info
 Grid (opatch lspatches) (grid os user)
  38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0 (38124772)
  37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
  37962938;ACFS RELEASE UPDATE 19.28.0.0.0 (37962938)
  37960098;Database Release Update : 19.28.0.0.250715 (37960098)
  36758186;DBWLM RELEASE UPDATE 19.0.0.0.0 (36758186)

 Oracle (opatch lspatches) (oracle os user)
  37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
  37960098;Database Release Update : 19.28.0.0.250715 (37960098)
  
<타켓 DB>

 

OS : Oracle Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname -r)
Oracle Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from v\$version where banner like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle Configuration
     DB명 : ORA19RF 
  PDB명 : non-CDB (echo -e "set pages 0 feedback off heading off verify off\nselect 'PDB명 : '||name from v\$pdbs where name <> 'PDB\$SEED';" | sqlplus -s / as sysdba)
  Single DB
   Hostname : ol8ora19rs1
   Public IP : 192.168.240.41
   Instance Name : ORA19RF1
  
Patch Info
 Grid (opatch lspatches) (grid os user)

  38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0 (38124772)
  37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
  37962938;ACFS RELEASE UPDATE 19.28.0.0.0 (37962938)
  37960098;Database Release Update : 19.28.0.0.250715 (37960098)
  36758186;DBWLM RELEASE UPDATE 19.0.0.0.0 (36758186)

 Oracle (opatch lspatches) (oracle os user)
  37962946;OCW RELEASE UPDATE 19.28.0.0.0 (37962946)
  37960098;Database Release Update : 19.28.0.0.250715 (37960098)

 

[내용]

 

1. 테스트 테이블 생성 및 초기 데이터 입력 (소스 DB)

 

1-1. 일반 테이블 (LOGGING 모드로 테이블 생성 후 데이터 입력)

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias st
alias st='rlwrap sqlplus tuner/oracle'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ st

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 30 14:06:21 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu Oct 30 2025 12:24:21 +09:00

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

[ol8ora19rf1]<TUNER@ORA19RF1>$


DROP TABLE TUNER.TB_NOLOGGING_TEST_01 PURGE;

CREATE TABLE TUNER.TB_NOLOGGING_TEST_01
(
  TEST_NO NUMBER(15)
, DATA_GB VARCHAR2(9)
, CONTS VARCHAR2(4000)
, CONSTRAINT PK_TB_NOLOGGING_TEST_01 PRIMARY KEY (TEST_NO)
) LOGGING
;

INSERT INTO TUNER.TB_NOLOGGING_TEST_01
SELECT ROWNUM AS TEST_NO
     , 'NOLOGGING' AS DATA_GB
     , DBMS_RANDOM.STRING('A', 4000) AS CONTS
  FROM DUAL CONNECT BY LEVEL <= 1000
;

COMMIT;

 

SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 10
COLUMN OWNER           FORMAT A10        HEADING 'OWNER'
COLUMN TABLE_NAME      FORMAT A25        HEADING 'TABLE_NAME'
COLUMN TABLESPACE_NAME FORMAT A20        HEADING 'TABLESPACE'
COLUMN "LOGGING"       FORMAT A9         HEADING 'LOGGING'
COLUMN NUM_ROWS        FORMAT 999,999,999 HEADING 'NUM_ROWS'
COLUMN BLOCKS          FORMAT 999,999,999 HEADING 'BLOCKS'
COLUMN AVG_ROW_LEN     FORMAT 999,999    HEADING 'AVG_ROW_LEN'
COLUMN LAST_ANALYZED   FORMAT A19        HEADING 'LAST_ANALYZED'

SELECT OWNER
     , TABLE_NAME
     , TABLESPACE_NAME
     , "LOGGING"
     , NUM_ROWS
     , BLOCKS
     , AVG_ROW_LEN
     , TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
  FROM DBA_TABLES
 WHERE OWNER = 'TUNER'
   AND TABLE_NAME = 'TB_NOLOGGING_TEST_01';
  
OWNER      TABLE_NAME                TABLESPACE           LOGGING       NUM_ROWS       BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ------------------------- -------------------- --------- ------------ ------------ ----------- -------------------
TUNER      TB_NOLOGGING_TEST_01      TUNER_DATA1          YES       (NULL)       (NULL)       (NULL)      (NULL)


BEGIN
    DBMS_STATS.GATHER_TABLE_STATS
    (
      OWNNAME => 'TUNER'
    , TABNAME => 'TB_NOLOGGING_TEST_01'
    , CASCADE => TRUE
    , METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE 1'
    , GRANULARITY => 'ALL'
    , ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
    , DEGREE => 4
    );
END;
/


 

SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 10
COLUMN OWNER           FORMAT A10        HEADING 'OWNER'
COLUMN TABLE_NAME      FORMAT A25        HEADING 'TABLE_NAME'
COLUMN TABLESPACE_NAME FORMAT A20        HEADING 'TABLESPACE'
COLUMN "LOGGING"       FORMAT A9         HEADING 'LOGGING'
COLUMN NUM_ROWS        FORMAT 999,999,999 HEADING 'NUM_ROWS'
COLUMN BLOCKS          FORMAT 999,999,999 HEADING 'BLOCKS'
COLUMN AVG_ROW_LEN     FORMAT 999,999    HEADING 'AVG_ROW_LEN'
COLUMN LAST_ANALYZED   FORMAT A19        HEADING 'LAST_ANALYZED'

SELECT OWNER
     , TABLE_NAME
     , TABLESPACE_NAME
     , "LOGGING"
     , NUM_ROWS
     , BLOCKS
     , AVG_ROW_LEN
     , TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
  FROM DBA_TABLES
 WHERE OWNER = 'TUNER'
   AND TABLE_NAME = 'TB_NOLOGGING_TEST_01';

OWNER      TABLE_NAME                TABLESPACE           LOGGING       NUM_ROWS       BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ------------------------- -------------------- --------- ------------ ------------ ----------- -------------------
TUNER      TB_NOLOGGING_TEST_01      TUNER_DATA1          YES              1,000        1,000       4,015 2025-10-30 21:55:14

 

1-2. clob 칼럼이 존재하는 테이블 (LOGGING 모드로 테이블 생성 후 데이터 입력)

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias st
alias st='rlwrap sqlplus tuner/oracle'

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ st

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 30 14:06:21 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Thu Oct 30 2025 12:24:21 +09:00

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

[ol8ora19rf1]<TUNER@ORA19RF1>$

DROP TABLE TUNER.TB_NOLOGGING_TEST_02 PURGE;

CREATE TABLE TUNER.TB_NOLOGGING_TEST_02
(
  TEST_NO NUMBER(15)
, DATA_GB VARCHAR2(9)
, CONTS_CLOB CLOB
, CONSTRAINT PK_TB_NOLOGGING_TEST_02 PRIMARY KEY (TEST_NO)
) LOGGING
;

ALTER TABLE TUNER.TB_NOLOGGING_TEST_02 MODIFY LOB (CONTS_CLOB) (NOCACHE LOGGING);

 

INSERT INTO TUNER.TB_NOLOGGING_TEST_02
SELECT ROWNUM AS TEST_NO
     , 'NOLOGGING' AS DATA_GB
     , DBMS_RANDOM.STRING('A', 4000) AS CONTS_CLOB
  FROM DUAL CONNECT BY LEVEL <= 1000
;

COMMIT;

 

SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 10
COLUMN OWNER           FORMAT A10        HEADING 'OWNER'
COLUMN TABLE_NAME      FORMAT A25        HEADING 'TABLE_NAME'
COLUMN TABLESPACE_NAME FORMAT A20        HEADING 'TABLESPACE'
COLUMN "LOGGING"       FORMAT A9         HEADING 'LOGGING'
COLUMN NUM_ROWS        FORMAT 999,999,999 HEADING 'NUM_ROWS'
COLUMN BLOCKS          FORMAT 999,999,999 HEADING 'BLOCKS'
COLUMN AVG_ROW_LEN     FORMAT 999,999    HEADING 'AVG_ROW_LEN'
COLUMN LAST_ANALYZED   FORMAT A19        HEADING 'LAST_ANALYZED'

SELECT OWNER
     , TABLE_NAME
     , TABLESPACE_NAME
     , "LOGGING"
     , NUM_ROWS
     , BLOCKS
     , AVG_ROW_LEN
     , TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
  FROM DBA_TABLES
 WHERE OWNER = 'TUNER'
   AND TABLE_NAME = 'TB_NOLOGGING_TEST_02';

OWNER      TABLE_NAME                TABLESPACE           LOGGING       NUM_ROWS       BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ------------------------- -------------------- --------- ------------ ------------ ----------- -------------------
TUNER      TB_NOLOGGING_TEST_02      TUNER_DATA1          YES       (NULL)       (NULL)       (NULL)      (NULL)


BEGIN
    DBMS_STATS.GATHER_TABLE_STATS
    (
      OWNNAME => 'TUNER'
    , TABNAME => 'TB_NOLOGGING_TEST_02'
    , CASCADE => TRUE
    , METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE 1'
    , GRANULARITY => 'ALL'
    , ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
    , DEGREE => 4
    );
END;
/

 

SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 10
COLUMN OWNER           FORMAT A10        HEADING 'OWNER'
COLUMN TABLE_NAME      FORMAT A25        HEADING 'TABLE_NAME'
COLUMN TABLESPACE_NAME FORMAT A20        HEADING 'TABLESPACE'
COLUMN "LOGGING"       FORMAT A9         HEADING 'LOGGING'
COLUMN NUM_ROWS        FORMAT 999,999,999 HEADING 'NUM_ROWS'
COLUMN BLOCKS          FORMAT 999,999,999 HEADING 'BLOCKS'
COLUMN AVG_ROW_LEN     FORMAT 999,999    HEADING 'AVG_ROW_LEN'
COLUMN LAST_ANALYZED   FORMAT A19        HEADING 'LAST_ANALYZED'

SELECT OWNER
     , TABLE_NAME
     , TABLESPACE_NAME
     , "LOGGING"
     , NUM_ROWS
     , BLOCKS
     , AVG_ROW_LEN
     , TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
  FROM DBA_TABLES
 WHERE OWNER = 'TUNER'
   AND TABLE_NAME = 'TB_NOLOGGING_TEST_02';

 

OWNER      TABLE_NAME                TABLESPACE           LOGGING       NUM_ROWS       BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ------------------------- -------------------- --------- ------------ ------------ ----------- -------------------
TUNER      TB_NOLOGGING_TEST_02      TUNER_DATA1          YES              1,000           13         147 2025-10-30 21:56:37


SET LINESIZE 220
SET PAGESIZE 100
SET NUMWIDTH 15

COLUMN TABLE_NAME      FORMAT A25        HEADING 'TABLE_NAME'
COLUMN COLUMN_NAME     FORMAT A20        HEADING 'COLUMN_NAME'
COLUMN LOB_SEGMENT     FORMAT A30        HEADING 'LOB_SEGMENT'
COLUMN LOB_INDEX       FORMAT A30        HEADING 'LOB_INDEX'
COLUMN LOGGING         FORMAT A9         HEADING 'LOGGING'
COLUMN IN_ROW          FORMAT A7         HEADING 'IN_ROW'
COLUMN TABLESPACE_NAME FORMAT A20        HEADING 'TABLESPACE'
COLUMN SEGMENT_TYPE    FORMAT A15        HEADING 'SEGMENT_TYPE'
COLUMN BLOCKS          FORMAT 999,999,999 HEADING 'BLOCKS(8K)'
COLUMN MB              FORMAT 999,999.99  HEADING 'SIZE(MB)'

SELECT A.TABLE_NAME
     , A.COLUMN_NAME
     , A.SEGMENT_NAME AS LOB_SEGMENT
     , A.INDEX_NAME   AS LOB_INDEX
     , A.LOGGING
     , A.IN_ROW
     , B.TABLESPACE_NAME
     , B.SEGMENT_TYPE
     , B.BYTES/8192      AS BLOCKS
     , B.BYTES/1024/1024 AS MB
  FROM DBA_LOBS A
     , DBA_SEGMENTS B
 WHERE A.OWNER = 'TUNER'
   AND A.TABLE_NAME = 'TB_NOLOGGING_TEST_02'
   AND B.SEGMENT_NAME = A.SEGMENT_NAME
   AND B.OWNER = A.OWNER
ORDER BY A.COLUMN_NAME;

TABLE_NAME                COLUMN_NAME          LOB_SEGMENT                    LOB_INDEX                      LOGGING   IN_ROW  TABLESPACE           SEGMENT_TYPE      BLOCKS(8K)    SIZE(MB)
------------------------- -------------------- ------------------------------ ------------------------------ --------- ------- -------------------- --------------- ------------ -----------
TB_NOLOGGING_TEST_02      CONTS_CLOB           SYS_LOB0000076395C00003$$      SYS_IL0000076395C00003$$       YES       YES     TUNER_DATA1          LOBSEGMENT             2,072       16.19


[ol8ora19rf1]<TUNER@ORA19RF1>$ ALTER SYSTEM CHECKPOINT;

System altered.

Elapsed: 00:00:00.02

[ol8ora19rf2]<SYS@ORA19RF2>$ ALTER SYSTEM CHECKPOINT;

System altered.

Elapsed: 00:00:04.86


2. DB 풀 백업 (소스 DB)

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test
mkdir: created directory '/home/oracle/backup_for_nologging_test'


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

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 30 13:43:36 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


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


[ol8ora19rf1]<SYS@ORA19RF1>$ create pfile='/home/oracle/backup_for_nologging_test/initORA19RF.ora' from spfile;

File created.

Elapsed: 00:00:00.01

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_UNQNAME
ORA19RF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_SID
ORA19RF1
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_DBNAME
ORA19RF


--새로운 ssh 창 열어서
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 30 13:45:22 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19RF (DBID=1289426103)

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/backup_for_nologging_test/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/backup_for_nologging_test/snapcf_CA.f';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
#CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; #For ADG
}


RUN {
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
sql 'alter system archive log current';
sql 'alter system checkpoint';
crosscheck backupset;
crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
BACKUP AS COMPRESSED BACKUPSET database FORMAT '/home/oracle/backup_for_nologging_test/ORA19RF_%U.bkp';
backup current controlfile format '/home/oracle/backup_for_nologging_test/ORA19RF_CTL_%U_%T';
delete noprompt obsolete;
delete noprompt expired backup;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
}

 

run {
crosscheck archivelog all;
backup archivelog all format '/home/oracle/backup_for_nologging_test/%d_ARCHIVE_%T_%u_s%s_p%p' delete input;
}

 

RMAN> list backup;
list backup;

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
43      Full    877.51M    DISK        00:00:39     2025-10-30 22:06:03
        BP Key: 43   Status: AVAILABLE  Compressed: YES  Tag: TAG20251030T220524
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_1d47ial4_45_1_1.bkp
  List of Datafiles in backup set 43
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/system.257.1213140455
  2       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/tuner_data1.272.1213465041
  3       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/sysaux.258.1213140481
  4       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/undotbs1.259.1213140495
  5       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/undotbs2.266.1213140849
  7       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/users.260.1213140497
  8       Full 6040730    2025-10-30 22:05:24              NO    +DATA1/ORA19RF/DATAFILE/tuner_idx1.273.1213465053

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
44      Full    1.06M      DISK        00:00:01     2025-10-30 22:06:10
        BP Key: 44   Status: AVAILABLE  Compressed: YES  Tag: TAG20251030T220524
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_1e47iamh_46_1_1.bkp
  SPFILE Included: Modification time: 2025-10-30 12:15:34
  SPFILE db_unique_name: ORA19RF
  Control File Included: Ckp SCN: 6040879      Ckp time: 2025-10-30 22:06:09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
45      Full    18.92M     DISK        00:00:01     2025-10-30 22:06:12
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20251030T220611
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_CTL_1f47iamj_47_1_1_20251030
  Control File Included: Ckp SCN: 6040896      Ckp time: 2025-10-30 22:06:11

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
46      154.50K    DISK        00:00:00     2025-10-30 22:06:50
        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: TAG20251030T220649
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1

  List of Archived Logs in backup set 46
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    62      6040625    2025-10-30 22:05:20 6041124    2025-10-30 22:06:49
  2    49      6040636    2025-10-30 22:05:22 6041127    2025-10-30 22:06:49

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
47      Full    18.95M     DISK        00:00:00     2025-10-30 22:06:51
        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20251030T220651
        Piece Name: /home/oracle/backup_for_nologging_test/c-1289426103-20251030-02
  SPFILE Included: Modification time: 2025-10-30 12:15:34
  SPFILE db_unique_name: ORA19RF
  Control File Included: Ckp SCN: 6041152      Ckp time: 2025-10-30 22:06:51

RMAN> quit
quit


Recovery Manager complete.

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/]$ ls -l /home/oracle/backup_for_nologging_test
total 957968
-rw-r-----. 1 oracle asmadmin  19890176 Oct 30 22:06 c-1289426103-20251030-02
-rw-r--r--. 1 oracle asmadmin      2364 Oct 30 22:04 initORA19RF.ora
-rw-r-----. 1 oracle asmadmin 920141824 Oct 30 22:06 ORA19RF_1d47ial4_45_1_1.bkp
-rw-r-----. 1 oracle asmadmin   1130496 Oct 30 22:06 ORA19RF_1e47iamh_46_1_1.bkp
-rw-r-----. 1 oracle asmadmin    158720 Oct 30 22:06 ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1
-rw-r-----. 1 oracle asmadmin  19857408 Oct 30 22:06 ORA19RF_CTL_1f47iamj_47_1_1_20251030
-rw-r-----. 1 oracle asmadmin  19775488 Oct 30 22:06 snapcf_CA.f


3. 풀 백업본을 원격지 서버에서 Restore 및 Recover (타켓 DB)

 

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test
mkdir: created directory '/home/oracle/backup_for_nologging_test'

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ scp 192.168.240.31:/home/oracle/backup_for_nologging_test/* /home/oracle/backup_for_nologging_test
oracle@192.168.240.31's password:
'
c-1289426103-20251030-02                                                                                                                                                  100%   19MB 261.4MB/s   00:00
initORA19RF.ora                                                                                                                                                           100% 2364     4.4MB/s   00:00
ORA19RF_1d47ial4_45_1_1.bkp                                                                                                                                               100%  878MB 278.4MB/s   00:03
ORA19RF_1e47iamh_46_1_1.bkp                                                                                                                                               100% 1104KB 193.3MB/s   00:00
ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1                                                                                                                                  100%  155KB 156.0MB/s   00:00
ORA19RF_CTL_1f47iamj_47_1_1_20251030                                                                                                                                      100%   19MB 270.8MB/s   00:00
snapcf_CA.f                                                                                                                                                               100%   19MB 253.1MB/s   00:00

 

--grid os user로 접속한 후 리스너 정보 추출
[+ASM1:grid@ol8ora19rs1][/home/grid]$ lsnrctl status | grep PORT
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.41)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.44)(PORT=1521)))

 

--oracle os user
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ cp /home/oracle/backup_for_nologging_test/initORA19RF.ora /home/oracle/backup_for_nologging_test/initORA19RF.ora.bak
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ cat > /home/oracle/backup_for_nologging_test/initORA19RF.ora << 'EOF'
*.audit_file_dest='/u01/app/oracle/admin/ORA19RF/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.compatible='19.0.0'
*.control_files='/home/oracle/backup_for_nologging_test/ORA19RF/controlfile/controlfile_01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/backup_for_nologging_test/ORA19RF/'
*.db_create_online_log_dest_1='/home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_01'
*.db_create_online_log_dest_2='/home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_02'
*.db_name='ORA19RF'
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RFXDB)'
*.filesystemio_options='SETALL'
*.heat_map='OFF'
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.41)(PORT=1521)))'
*.log_archive_dest_1='LOCATION=/home/oracle/backup_for_nologging_test/ORA19RF/archivedlog/'
*.log_archive_format='%t_%s_%r.ARC'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=TRUE
*.optimizer_dynamic_sampling=0
*.parallel_force_local=TRUE
*.parallel_min_servers=0
*.pga_aggregate_limit=0m
*.pga_aggregate_target=268m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=768m
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.uniform_log_timestamp_format=FALSE
*.use_large_pages='true'
EOF

 

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ cat /home/oracle/backup_for_nologging_test/initORA19RF.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA19RF/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.compatible='19.0.0'
*.control_files='/home/oracle/backup_for_nologging_test/ORA19RF/controlfile/controlfile_01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/backup_for_nologging_test/ORA19RF/'
*.db_create_online_log_dest_1='/home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_01'
*.db_create_online_log_dest_2='/home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_02'
*.db_name='ORA19RF'
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RFXDB)'
*.filesystemio_options='SETALL'
*.heat_map='OFF'
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.41)(PORT=1521)))'
*.log_archive_dest_1='LOCATION=/home/oracle/backup_for_nologging_test/ORA19RF/archivedlog/'
*.log_archive_format='%t_%s_%r.ARC'
*.nls_language='AMERICAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.optimizer_adaptive_plans=FALSE
*.optimizer_adaptive_reporting_only=TRUE
*.optimizer_dynamic_sampling=0
*.parallel_force_local=TRUE
*.parallel_min_servers=0
*.pga_aggregate_limit=0m
*.pga_aggregate_target=268m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=768m
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.uniform_log_timestamp_format=FALSE
*.use_large_pages='true'

 

--필수 디렉토리 생성
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /u01/app/oracle/admin/ORA19RF/adump
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RF/controlfile
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RF/archivedlog 
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RF/datafile
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_01
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RF/onlinelog_02

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ export ORACLE_SID=ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ echo $ORACLE_SID
ORA19RF1

 

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

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 30 14:59:43 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connected to an idle instance.

[ol8ora19rs1]<SYS@ORA19RF1>$ startup nomount pfile='/home/oracle/backup_for_nologging_test/initORA19RF.ora';
ORACLE instance started.

Total System Global Area  805305464 bytes
Fixed Size                  8944760 bytes
Variable Size             423624704 bytes
Database Buffers          364904448 bytes
Redo Buffers                7831552 bytes

 

--새로운 ssh 접속
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ export ORACLE_SID=ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ echo $ORACLE_SID
ORA19RF1

 

[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ alias rt
alias rt='rman target /'

 

[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 30 15:00:42 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19RF (not mounted)

 

RMAN> host 'ls -l /home/oracle/backup_for_nologging_test';
host 'ls -l /home/oracle/backup_for_nologging_test';
total 957972
-rw-r-----. 1 oracle oinstall  19890176 Oct 30 22:16 c-1289426103-20251030-02
-rw-r--r--. 1 oracle oinstall      1439 Oct 30 22:26 initORA19RF.ora
-rw-r--r--. 1 oracle oinstall      2364 Oct 30 22:20 initORA19RF.ora.bak
drwxr-xr-x. 7 oracle oinstall       100 Oct 30 22:28 ORA19RF
-rw-r-----. 1 oracle oinstall 920141824 Oct 30 22:16 ORA19RF_1d47ial4_45_1_1.bkp
-rw-r-----. 1 oracle oinstall   1130496 Oct 30 22:16 ORA19RF_1e47iamh_46_1_1.bkp
-rw-r-----. 1 oracle oinstall    158720 Oct 30 22:16 ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1
-rw-r-----. 1 oracle oinstall  19857408 Oct 30 22:16 ORA19RF_CTL_1f47iamj_47_1_1_20251030
-rw-r-----. 1 oracle oinstall  19775488 Oct 30 22:16 snapcf_CA.f
host command complete

 

--컨트롤 파일 복구

RMAN> restore controlfile from '/home/oracle/backup_for_nologging_test/ORA19RF_CTL_1f47iamj_47_1_1_20251030';

 

--마운트 시킴

RMAN> alter database mount;

 

--set newname으로 복구할 디렉토리 위치를 지정하고 %b 옵션으로 원래 파일명은 유지함

RUN {
  SET NEWNAME FOR DATABASE TO '/home/oracle/backup_for_nologging_test/ORA19RF/datafile/%b';
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
}

 

executing command: SET NEWNAME

Starting restore at 2025-10-30 22:32:52
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/system.257.1213140455
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_data1.272.1213465041
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/sysaux.258.1213140481
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs1.259.1213140495
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs2.266.1213140849
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/users.260.1213140497
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_idx1.273.1213465053
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RF_1d47ial4_45_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RF_1d47ial4_45_1_1.bkp tag=TAG20251030T220524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 2025-10-30 22:35:22

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/system.257.1213140455
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_data1.272.1213465041
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/sysaux.258.1213140481
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs1.259.1213140495
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs2.266.1213140849
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/users.260.1213140497
datafile 8 switched to datafile copy
input datafile copy RECID=14 STAMP=1215902122 file name=/home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_idx1.273.1213465053


RMAN> host 'ls -l /home/oracle/backup_for_nologging_test/*ARCHIVE*';
host 'ls -l /home/oracle/backup_for_nologging_test/*ARCHIVE*';
-rw-r-----. 1 oracle oinstall 158720 Oct 30 22:16 /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1
host command complete

RMAN> list backup of archivelog all;
list backup of archivelog all;

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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
40      146.00K    DISK        00:00:00     2025-10-30 15:05:54
        BP Key: 40   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T150554
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1a47hi2i_s42_p1

  List of Archived Logs in backup set 40
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    57      5900414    2025-10-30 15:05:01 5900751    2025-10-30 15:05:54
  2    45      5900423    2025-10-30 15:05:03 5900748    2025-10-30 15:05:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
41      34.20M     DISK        00:00:00     2025-10-30 16:15:50
        BP Key: 41   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T161550
        Piece Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1b47hm5m_s43_p1

  List of Archived Logs in backup set 41
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    58      5900751    2025-10-30 15:05:54 5922164    2025-10-30 16:11:48

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
42      32.15M     DISK        00:00:00     2025-10-30 16:15:52
        BP Key: 42   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T161551
        Piece Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1c47hm5o_s44_p1

  List of Archived Logs in backup set 42
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    46      5900748    2025-10-30 15:05:54 5922167    2025-10-30 16:11:48
--> 소스 DB에서 컨트롤 파일 백업 시 아카이브 로그 상황임

--> 소스 DB에서 컨트롤 파일 백업 후 아카이브 로그를 백업했으므로 아카이브 로그의 백업본을 catalog에 등록시켜야함

(결국 컨트롤 파일을 백업한 시점에는 백업한 아카이브가 없었던 상황이라 안보이는 것임, 이걸 이해하는 것이 중요함)

RMAN> catalog start with '/home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1';

catalog start with '/home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1';
searching for all files that match the pattern /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_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/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_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
------- ---------- ----------- ------------ -------------------
40      146.00K    DISK        00:00:00     2025-10-30 15:05:54
        BP Key: 40   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T150554
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1a47hi2i_s42_p1

  List of Archived Logs in backup set 40
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    57      5900414    2025-10-30 15:05:01 5900751    2025-10-30 15:05:54
  2    45      5900423    2025-10-30 15:05:03 5900748    2025-10-30 15:05:54

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
41      34.20M     DISK        00:00:00     2025-10-30 16:15:50
        BP Key: 41   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T161550
        Piece Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1b47hm5m_s43_p1

  List of Archived Logs in backup set 41
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    58      5900751    2025-10-30 15:05:54 5922164    2025-10-30 16:11:48

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
42      32.15M     DISK        00:00:00     2025-10-30 16:15:52
        BP Key: 42   Status: EXPIRED  Compressed: NO  Tag: TAG20251030T161551
        Piece Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1c47hm5o_s44_p1

  List of Archived Logs in backup set 42
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    46      5900748    2025-10-30 15:05:54 5922167    2025-10-30 16:11:48

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
45      154.50K    DISK        00:00:00     2025-10-30 22:06:50
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20251030T220649
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1

  List of Archived Logs in backup set 45
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    62      6040625    2025-10-30 22:05:20 6041124    2025-10-30 22:06:49
  2    49      6040636    2025-10-30 22:05:22 6041127    2025-10-30 22:06:49
--> 해당 아카이브 로그의 백업본이 catalog 등록됨


RMAN> RECOVER DATABASE;
RECOVER DATABASE;
Starting recover at 2025-10-30 22:44:08
using channel ORA_DISK_1

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=62
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=49
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1 tag=TAG20251030T220649
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/backup_for_nologging_test/ORA19RF/archivedlog/1_62_1213140537.ARC thread=1 sequence=62
archived log file name=/home/oracle/backup_for_nologging_test/ORA19RF/archivedlog/2_49_1213140537.ARC thread=2 sequence=49
unable to find archived log
archived log thread=1 sequence=63
RMAN Command Id : 2025-10-30T22:31:54
RMAN Command Id : 2025-10-30T22:31:54
RMAN Command Id : 2025-10-30T22:31:54
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/30/2025 22:44:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 63 and starting SCN of 6041124
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/RMAN_2520668267_110/trace/ora_rman_1005_0.trc
RMAN Server Diagnostic Trace file : /u01/app/oracle/diag/rdbms/ora19rf/ORA19RF1/trace/ORA19RF1_ora_3380.trc

 

--데이터 확인을 위해 read only로 open함

RMAN> alter database open read only;
alter database open read only;
Statement processed

RMAN> select count(*) from TUNER.TB_NOLOGGING_TEST_01;
select count(*) from TUNER.TB_NOLOGGING_TEST_01;
  COUNT(*)
----------
      1000

RMAN> select * from TUNER.TB_NOLOGGING_TEST_01;
...생략
1000 rows selected

 

RMAN> select count(*) from TUNER.TB_NOLOGGING_TEST_02;
select count(*) from TUNER.TB_NOLOGGING_TEST_02;
  COUNT(*)
----------
      1000

RMAN> select * from TUNER.TB_NOLOGGING_TEST_02;
...생략
1000 rows selected
--> 테스트 테이블에 입력한 데이터가 존재하는 상황임

 

--데이터를 확인했으니 해당 타켓 DB 인스턴스를 내리고 다시 mount 모드로 기동 시킬것임

RMAN> shutdown immediate;
shutdown immediate;
database closed
database dismounted
Oracle instance shut down

 

--새로운 ssh 창 열어서
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ echo $ORACLE_SID
ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 30 15:37:02 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connected to an idle instance.

[ol8ora19rs1]<SYS@ORA19RF1>$ startup nomount pfile='/home/oracle/backup_for_nologging_test/initORA19RF.ora';
ORACLE instance started.

Total System Global Area  805305464 bytes
Fixed Size                  8944760 bytes
Variable Size             423624704 bytes
Database Buffers          364904448 bytes
Redo Buffers                7831552 bytes
[ol8ora19rs1]<SYS@ORA19RF1>$ alter database mount;

Database altered.

Elapsed: 00:00:04.07

[ol8ora19rs1]<SYS@ORA19RF1>$
SET LINESIZE 220
SET PAGESIZE 100
SET NUMWIDTH 20
COLUMN DBID                FORMAT 999999999999 HEADING 'DBID'
COLUMN NAME                FORMAT A10           HEADING 'DB_NAME'
COLUMN OPEN_MODE           FORMAT A12           HEADING 'OPEN_MODE'
COLUMN DB_UNIQUE_NAME      FORMAT A15           HEADING 'DB_UNIQUE_NAME'
COLUMN RESETLOGS_CHANGE#   FORMAT 999999999999 HEADING 'RESETLOGS|CHANGE#'
COLUMN RESETLOGS_TIME      FORMAT A19           HEADING 'RESETLOGS_TIME'
COLUMN CHECKPOINT_CHANGE#  FORMAT 999999999999 HEADING 'CHECKPOINT|CHANGE#'
COLUMN ARCHIVE_CHANGE#     FORMAT 999999999999 HEADING 'ARCHIVE|CHANGE#'
COLUMN CONTROLFILE_CHANGE# FORMAT 999999999999 HEADING 'CONTROLFILE|CHANGE#'
COLUMN CURRENT_SCN         FORMAT 999999999999 HEADING 'CURRENT|SCN'
COLUMN CDB                 FORMAT A3            HEADING 'CDB'

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT
     DBID
    , NAME
    , OPEN_MODE
    , RESETLOGS_CHANGE#
    , RESETLOGS_TIME
    , CHECKPOINT_CHANGE#
    , ARCHIVE_CHANGE#
    , CONTROLFILE_CHANGE#
    , CURRENT_SCN
    , DB_UNIQUE_NAME
    , CDB
FROM
    V$DATABASE;

                                          RESETLOGS                        CHECKPOINT       ARCHIVE   CONTROLFILE       CURRENT
         DBID DB_NAME    OPEN_MODE          CHANGE# RESETLOGS_TIME            CHANGE#       CHANGE#       CHANGE#           SCN DB_UNIQUE_NAME  CDB
------------- ---------- ------------ ------------- ------------------- ------------- ------------- ------------- ------------- --------------- ---
   1289426103 ORA19RF    MOUNTED            1920977 2025-09-29 23:28:57       6040650       6040621       6041124             0 ORA19RF         NO
--> CHECKPOINT_CHANGE# : 데이터파일이 일관되게 커밋된 시점(SCN)
--> CONTROLFILE_CHANGE# : 컨트롤파일에 기록된 마지막 변경 시점의 SCN


SET LINESIZE 250
SET PAGESIZE 100
SET NUMWIDTH 20
COLUMN FILE#              FORMAT 9999           HEADING 'FILE#'
COLUMN STATUS             FORMAT A10            HEADING 'STATUS'
COLUMN TABLESPACE_NAME    FORMAT A20            HEADING 'TABLESPACE_NAME'
COLUMN RESETLOGS_CHANGE#  FORMAT 999999999999   HEADING 'RESETLOGS|CHANGE#'
COLUMN NAME               FORMAT A90            HEADING 'DATAFILE_NAME'
COLUMN CHECKPOINT_CHANGE# FORMAT 999999999999   HEADING 'CHECKPOINT|CHANGE#'
COLUMN ERROR              FORMAT A12            HEADING 'ERROR'
COLUMN CON_NAME           FORMAT A20            HEADING 'CON_NAME'

SELECT
     a.FILE#
    , a.STATUS
    , a.TABLESPACE_NAME
    , a.RESETLOGS_CHANGE#
    , a.NAME
    , a.CHECKPOINT_CHANGE#
    , a.ERROR
    , (SELECT l.name
         FROM v$containers l
        WHERE l.con_id = a.con_id) AS con_name
FROM
    v$datafile_header a
ORDER BY
    a.FILE#;

                                          RESETLOGS                                                                                               CHECKPOINT
FILE# STATUS     TABLESPACE_NAME            CHANGE# DATAFILE_NAME                                                                                    CHANGE# ERROR        CON_NAME
----- ---------- -------------------- ------------- ------------------------------------------------------------------------------------------ ------------- ------------ --------------------
    1 ONLINE     SYSTEM                     1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/system.257.1213140455                    6041124 (NULL)       ORA19RF
    2 ONLINE     TUNER_DATA1                1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_data1.272.1213465041               6041124 (NULL)       ORA19RF
    3 ONLINE     SYSAUX                     1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/sysaux.258.1213140481                    6041124 (NULL)       ORA19RF
    4 ONLINE     UNDOTBS1                   1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs1.259.1213140495                  6041124 (NULL)       ORA19RF
    5 ONLINE     UNDOTBS2                   1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/undotbs2.266.1213140849                  6041124 (NULL)       ORA19RF
    7 ONLINE     USERS                      1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/users.260.1213140497                     6041124 (NULL)       ORA19RF
    8 ONLINE     TUNER_IDX1                 1920977 /home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_idx1.273.1213465053                6041124 (NULL)       ORA19RF

7 rows selected.

Elapsed: 00:00:00.01

$DATABASE의 CONTROLFILE_CHANGE# = 6041124 인 상황에서 V$DATAFILE_HEADER의 각 DATAFILE CHECKPOINT_CHANGE# = 6041124 으로 완전히 일치함
복구가 끝난 직후(RECOVER DATABASE) DB가 완전히 일관된 상태임을 의미

 

 

CHECKPOINT_CHANGE# (V$DATABASE) : 6040650 (복구 시작 시점에 데이터파일이 도달해 있던 SCN)

CONTROLFILE_CHANGE# : 6041124 (복구를 마친 시점에 컨트롤파일이 갱신된 SCN)

V$DATAFILE_HEADER.CHECKPOINT_CHANGE# : 6041124 (복구 완료 후, 모든 데이터파일이 컨트롤파일 SCN과 동일하게 갱신됨)

 

4. 테스트 테이블을 nologging 모드로 설정 후 append로 데이터 insert (소스 DB)

 

Using Oracle7 UNRECOVERABLE and Oracle8 NOLOGGING Option (문서 ID 147474.1) 참고
어떤 상황에서 아카이브 로그로 복구를 못하는 데이터가 만들어지는에 대해 설명한 문서임

 

----------------------------------------------문서 내용 시작-----------------------------------------

무엇을 말하나? (한줄 요약)

UNRECOVERABLE/NOLOGGING 옵션은 특정 작업에서 “데이터 블록”에 대한 redo 생성(로그 기록)을 줄여서 성능을 올리는 대신,

그 작업으로 만들어진 데이터는 사고 시 일반 복구(media/instance recovery)로는 되살릴 수 없게 만든다는 뜻이에요. 작업 직후 반드시 백업이 필요합니다.

 

왜 이런 옵션이 있나?
Redo 로그 생성은 래치/CPU와 redo 파일 I/O가 수반되어 비용이 큰 작업입니다.

그래서 일부 작업에 한해서만 redo 생성을 생략(또는 최소화)하는 모드를 제공해 성능을 끌어올리고 리두(아카이브) 생성 최소화가 목적입니다.

정확히 어떻게 동작하나?
데이터딕셔너리용 DDL 정보는 여전히 redo에 기록됩니다. (객체 생성/구조 변경 사실은 남아야 하므로)
하지만 대량 데이터 적재에서 생성되는 “실제 행 데이터(블록)”는 redo에 기록하지 않도록 할 수 있습니다.
예: CREATE TABLE ... AS SELECT 중에 들어간 데이터 자체는 로그를 거의 남기지 않음.

 

어디에 설정할 수 있나?
객체/공간 단위 속성으로 NOLOGGING을 걸 수 있습니다: 테이블, 파티션, 인덱스, 테이블스페이스 등.
단, 이 속성을 걸었다고 해서 모든 작업이 무조건 NOLOGGING이 되는 건 아님. “지원되는 작업”에서만 실제 효과가 납니다.

 

어떤 작업이 NOLOGGING을 쓸 수 있나? (문서에 명시된 대상)
Direct Path 로드: SQL*Loader Direct Load
Direct-Load INSERT (예: INSERT /*+ APPEND */ ...)
CTAS: CREATE TABLE ... AS SELECT
인덱스 작업: CREATE INDEX, ALTER INDEX ... REBUILD, REBUILD PARTITION, SPLIT PARTITION
파티션 이동/분할: ALTER TABLE ... MOVE PARTITION, SPLIT PARTITION
LOB 예외: out-of-line LOB이면서 NOCACHE NOLOGGING일 때의 INSERT/UPDATE/DELETE
In-line LOB : 데이터가 작으면 테이블 로우 안에 함께 저장됨 (일반 컬럼처럼 redo 처리됨)

out-of-line LOB : 큰 데이터(BLOB, CLOB 등)는 별도의 LOB segment에 저장됨

위 나열된 이런 류의 작업에서만 NOLOGGING로 redo 생성을 대폭 줄일 수 있습니다.

out-of line LOB 예시
CREATE TABLE t_lob (
  id NUMBER,
  data CLOB
)
LOB (data) STORE AS SECUREFILE (
  NOCACHE
  NOLOGGING
);
NOCACHE: LOB 데이터를 버퍼 캐시에 적재하지 않음
NOLOGGING: LOB 데이터를 redo 로그에 기록하지 않음.
이 LOB 컬럼에 대해 INSERT / UPDATE / DELETE 작업을 해도, 그 LOB 데이터 자체(즉, 실제 CLOB/BLOB 내용)는 redo에 거의 남지 않습니다.


결론 : 테이블이 노로깅이어도 일반적인 dml(update, delete, insert, merge)은 모두 REDO로그가 남음

       테이블이 노로깅이어도 append insert 를 제외한 update, delete, merge는 모두 REDO로그가 남음

       테이블이 노로깅인데 APPEND힌트를 기재안해도 PARALLEL DML로 DIRECT PATH LOAD가 유도되면 REDO로그가 최소화됨

       하지만 out-of-line LOB은 nocache nologging일 때 일반적인 dml이라도 redo에 안남음

 

성능 이점 vs. 복구(리스크)
이점: redo가 거의 안 생기므로 생성·쓰기 부담이 줄어 성능 향상(CPU/래치/redo I/O 감소).
리스크/제약:
해당 작업으로 적재/작성된 데이터는 미디어 복구로 복원 불가합니다.
인스턴스/DB 장애가 나면 그 작업을 “처음부터 다시” 해야 할 수도 있음(redo가 없으니 재생할 수 없음).
따라서 작업 완료 직후 백업이 필수입니다. 이 백업이 있어야 이후 장애 시 해당 시점으로 복구가 가능해요.

 

흔한 오해 정리
NOLOGGING 속성을 걸어도 **일반 DML(일반 INSERT/UPDATE/DELETE)**은 보통 redo를 만듭니다.
Direct Path가 아니면 효과가 없다고 보면 됩니다. (LOB의 특별 케이스 제외)
스키마 변경 사실(DDL 메타데이터)은 항상 redo에 기록됩니다. 데이터만 최소화되는 겁니다.

 

실무 사용 예시

CTAS 고속 적재
CREATE TABLE sales_nologging NOLOGGING
AS
SELECT * FROM sales_source;
적재 끝나면 즉시 백업.

 

대용량 Append INSERT
ALTER TABLE fact_sales NOLOGGING;

INSERT /*+ APPEND */
INTO fact_sales
SELECT ... FROM staging_data;

Direct Path로 들어가며 redo가 크게 줄어듦. 끝나면 백업 필수.

인덱스 재구성
ALTER INDEX idx_orders REBUILD NOLOGGING;
리빌드가 빠름. 이후 백업.
----------------------------------------------문서 내용 종료-----------------------------------------

4-1. 일반 테이블 (NOLOGGING 모드로 테이블 속성 변경 후 데이터 입력)

[ol8ora19rf1]<SYS@ORA19RF1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    6065883

1 row selected.

Elapsed: 00:00:00.01

 

[ol8ora19rf1]<SYS@ORA19RF1>$

 

--테이블을 노로깅으로 설정
alter table TUNER.TB_NOLOGGING_TEST_01 nologging;

 

--append 힌트주고 insert (row단위 redo 로그 안남음)

insert /*+ append */ into TUNER.TB_NOLOGGING_TEST_01
WITH TMP_1 AS
(
    SELECT /*+ MATERILAIZE */
           MAX(TEST_NO) MAX_TEST_NO
      FROM TUNER.TB_NOLOGGING_TEST_01
)
SELECT MAX_TEST_NO+LEVEL AS TEST_NO
     , 'NOLOGGING' AS DATA_GB
     , DBMS_RANDOM.STRING('A', 4000) AS CONTS_CLOB
  FROM TMP_1 CONNECT BY LEVEL <= 1000
;

 

COMMIT;

 

[ol8ora19rf1]<SYS@ORA19RF1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    6066033

1 row selected.

Elapsed: 00:00:00.00

 

4-2. clob 칼럼이 존재하는 테이블 (NOLOGGING 모드로 테이블 및 CLOB 칼럼의 속성 변경 후 데이터 입력)

 

CURRENT_SCN
-----------
    6066092

1 row selected.

Elapsed: 00:00:00.00

 

--테이블을 노로깅으로 설정

alter table TUNER.TB_NOLOGGING_TEST_02 nologging;

 

--lob 칼럼 단위로도 노로깅 설정

ALTER TABLE TUNER.TB_NOLOGGING_TEST_02 MODIFY LOB (CONTS_CLOB) (NOCACHE nologging);

insert /*+ append */ into TUNER.TB_NOLOGGING_TEST_02
WITH TMP_1 AS
(
    SELECT /*+ MATERILAIZE */
           MAX(TEST_NO) MAX_TEST_NO
      FROM TUNER.TB_NOLOGGING_TEST_02
)
SELECT MAX_TEST_NO+LEVEL AS TEST_NO
     , 'NOLOGGING' AS DATA_GB
     , DBMS_RANDOM.STRING('A', 4000) AS CONTS_CLOB
  FROM TMP_1 CONNECT BY LEVEL <= 1000
;

COMMIT;

 

select current_scn from v$database;

CURRENT_SCN
-----------
    6066811

1 row selected.

Elapsed: 00:00:00.00

 

--체크 포인트 및 아카이브 로그 생성 (각각의 노드에서 실행)

[ol8ora19rf1]<SYS@ORA19RF1>$ alter system checkpoint;
[ol8ora19rf2]<SYS@ORA19RF2>$ alter system checkpoint;

[ol8ora19rf1]<SYS@ORA19RF1>$ ALTER SYSTEM ARCHIVE LOG CURRENT;
[ol8ora19rf2]<SYS@ORA19RF2>$ ALTER SYSTEM ARCHIVE LOG CURRENT;


SET LINESIZE 220
SET PAGESIZE 200
SET NUMWIDTH 20
COLUMN THREAD#         FORMAT 999      HEADING 'THR'
COLUMN SEQUENCE#       FORMAT 999999   HEADING 'SEQ'
COLUMN RESETLOGS_CHANGE# FORMAT 999999999999 HEADING 'RESETLOGS|CHANGE#'
COLUMN FIRST_CHANGE#   FORMAT 999999999999 HEADING 'FIRST|CHANGE#'
COLUMN NEXT_CHANGE#    FORMAT 999999999999 HEADING 'NEXT|CHANGE#'
COLUMN ARCHIVED        FORMAT A8       HEADING 'ARCHIVED'
COLUMN DELETED         FORMAT A8       HEADING 'DELETED'
COLUMN NAME            FORMAT A100     HEADING 'ARCHIVELOG NAME'

SELECT
       A.NAME
     , A.THREAD#
     , A.SEQUENCE#
     , A.RESETLOGS_CHANGE#
     , A.FIRST_CHANGE#
     , A.NEXT_CHANGE#
     , A.ARCHIVED
     , A.DELETED
  FROM V$ARCHIVED_LOG A
 WHERE 1=1
   AND A.FIRST_CHANGE# <= 6066811   --타켓 DB가 가야할 지점은 6066811 이므로 FIRST_CHANGE# 이 6066811 보다 작거나 같은 것은 복구 대상임
   AND A.NEXT_CHANGE#  >= 6041125   --타켓 DB은 6041124 까지 적용된 상태이므로 A.NEXT_CHANGE# 이 6041124+1=6041125 보다 같거나 큰 것은 복구대상임
 ORDER BY A.THREAD#, A.SEQUENCE#
;

ARCHIVELOG NAME                                                                                       THR     SEQ       CHANGE#       CHANGE#       CHANGE# ARCHIVED DELETED
---------------------------------------------------------------------------------------------------- ---- ------- ------------- ------------- ------------- -------- --------
(NULL)                                                                                                  1      62       1920977       6040625       6041124 YES      YES
+FRA1/ORA19RF/ARCHIVELOG/2025_10_30/thread_1_seq_63.292.1215905289                                      1      63       1920977       6041124       6067001 YES      NO
(NULL)                                                                                                  2      49       1920977       6040636       6041127 YES      YES
+FRA1/ORA19RF/ARCHIVELOG/2025_10_30/thread_2_seq_50.293.1215905289                                      2      50       1920977       6041127       6066998 YES      NO

4 rows selected.

Elapsed: 00:00:00.01

 

--새로운 ssh 창 열어서

--소스DB에서 아카이브를 백업 받음
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export ORACLE_SID=ORA19RF1
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_SID
ORA19RF1
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 30 23:33:49 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19RF (DBID=1289426103)

RMAN>
run {
crosscheck archivelog all;
BACKUP ARCHIVELOG FROM SEQUENCE 63 UNTIL SEQUENCE 63 THREAD 1 format '/home/oracle/backup_for_nologging_test/ARCHIVE_%d_%T_%u_s%s_p%p';
BACKUP ARCHIVELOG FROM SEQUENCE 50 UNTIL SEQUENCE 50 THREAD 2 format '/home/oracle/backup_for_nologging_test/ARCHIVE_%d_%T_%u_s%s_p%p';
}

Starting backup at 2025-10-30 23:33:56
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=63 RECID=103 STAMP=1215905288
channel ORA_DISK_1: starting piece 1 at 2025-10-30 23:33:56
channel ORA_DISK_1: finished piece 1 at 2025-10-30 23:33:57
piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1 tag=TAG20251030T233356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-10-30 23:33:57

Starting backup at 2025-10-30 23:33:57
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=2 sequence=50 RECID=102 STAMP=1215905288
channel ORA_DISK_1: starting piece 1 at 2025-10-30 23:33:57
channel ORA_DISK_1: finished piece 1 at 2025-10-30 23:33:58
piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1 tag=TAG20251030T233357 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-10-30 23:33:58

Starting Control File and SPFILE Autobackup at 2025-10-30 23:33:58
piece handle=/home/oracle/backup_for_nologging_test/c-1289426103-20251030-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-10-30 23:33:59


5. 타켓 DB에서 소스DB에서 백업받은 아카이브 로그를 가져와서 Recover (타켓 DB)

 

[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ scp 192.168.240.31:/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1 /home/oracle/backup_for_nologging_test
oracle@192.168.240.31's password:
'
ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1                                                                                                                               100%   40MB 333.0MB/s   00:00

[ORA19RF1:oracle@ol8ora19rs1][/home/oracle/backup_for_nologging_test]$ scp 192.168.240.31:/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1 /home/oracle/backup_for_nologging_test
oracle@192.168.240.31's password:
'
ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1                                                                                                                               100%   37MB 344.7MB/s   00:00

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ls -alrt /home/oracle/backup_for_nologging_test
total 1036996
-rw-r-----.  1 oracle oinstall  19890176 Oct 30 22:16 c-1289426103-20251030-02
-rw-r-----.  1 oracle oinstall 920141824 Oct 30 22:16 ORA19RF_1d47ial4_45_1_1.bkp
-rw-r-----.  1 oracle oinstall   1130496 Oct 30 22:16 ORA19RF_1e47iamh_46_1_1.bkp
-rw-r-----.  1 oracle oinstall    158720 Oct 30 22:16 ORA19RF_ARCHIVE_20251030_1g47ianq_s48_p1
-rw-r-----.  1 oracle oinstall  19857408 Oct 30 22:16 ORA19RF_CTL_1f47iamj_47_1_1_20251030
-rw-r-----.  1 oracle oinstall  19775488 Oct 30 22:16 snapcf_CA.f
-rw-r--r--.  1 oracle oinstall      2364 Oct 30 22:20 initORA19RF.ora.bak
-rw-r--r--.  1 oracle oinstall      1439 Oct 30 22:26 initORA19RF.ora
drwxr-xr-x.  8 oracle oinstall       115 Oct 30 22:44 ORA19RF
drwx------. 13 oracle oinstall      4096 Oct 30 23:35 ..
-rw-r-----.  1 oracle oinstall  42316800 Oct 30 23:35 ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1
drwxr-xr-x.  3 oracle oinstall      4096 Oct 30 23:36 .
-rw-r-----.  1 oracle oinstall  38590464 Oct 30 23:36 ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1

 

[ORA19RFS1:oracle@ol8ora19rs1][/home/oracle]$ export ORACLE_SID=ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ echo $ORACLE_SID
ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ alias rt
alias rt='rman target /'
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 30 23:39:18 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19RF (DBID=1289426103, not open)

RMAN> catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1';
catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_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/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1i47ifr4_s50_p1


RMAN> catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1';
catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1';
searching for all files that match the pattern /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_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/backup_for_nologging_test/ARCHIVE_ORA19RF_20251030_1j47ifr5_s51_p1

 

--복구 목표 SCN 6066811 까지 복구
RUN {
 SET UNTIL SCN 6066811;
 RECOVER DATABASE;
}

RMAN> alter database open resetlogs;

 

6. 테스트 테이블 조회 (타켓 DB)

 

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (문서 ID 794505.1) 문서 참고할 것

----------------------------------------------문서 내용 시작-----------------------------------------

Doc ID 794505.1 ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

 

이론 요약
NOLOGGING 또는 UNRECOVERABLE 작업(또는 impdp DISABLE_ARCHIVE_LOGGING=Y)으로 만들어진 블록은 복구 시 무효화되어 이후 읽을 때 ORA-1578과 ORA-26040이 발생한다.
이런 블록의 데이터는 복구 불가능하며, NOLOGGING 작업 이후의 백업이 있을 때만 정상 데이터로 복원할 수 있다.

RMAN DUPLICATE나 RESTORE 이후 오류 발생 시
원본 DB에서 FORCE LOGGING 설정
ALTER DATABASE FORCE LOGGING;
새로운 백업을 다시 수행한 후 DUPLICATE 또는 RESTORE를 다시 진행한다.

물리적 스탠바이 환경에서 오류가 발생한 경우
Primary DB에 문제가 없다면 해당 파일을 Primary에서 복원하여 교체한다.
Doc ID 958181.1 절차(RMAN Incremental Backup으로 NOLOGGING 수정) 사용 가능하다.
12c 이상에서는 RMAN 명령으로 복구 가능하다.
RECOVER DATABASE NONLOGGED BLOCK;
재발 방지를 위해 Primary DB에서 FORCE LOGGING을 설정한다.
Primary와 Standby 간 NOLOGGING 블록 위치가 다를 경우 event 10231 또는 DBMS_REPAIR로 스킵 설정 후 파일 교체가 필요하다.

영향받은 세그먼트나 객체 식별
RMAN VALIDATE 명령으로 탐지 가능하다.
12c 이상은 V$NONLOGGED_BLOCK 뷰를 확인하고
10g~11g는 V$DATABASE_BLOCK_CORRUPTION 뷰에서 CORRUPTION_TYPE=NOLOGGING을 확인한다.
DBVERIFY(DBV) 도구로도 확인 가능하다.

세그먼트 유형별 조치

FREE 블록의 경우
DBA_FREE_SPACE에 속한 FREE 블록이라면 재사용 시 자동으로 재포맷되어 복구된다.
필요 시 Doc ID 336133.1의 재포맷 절차를 참조한다.

인덱스의 경우
인덱스를 드롭 후 재생성한다.

테이블의 경우
DBMS_REPAIR로 손상 블록을 스킵하도록 설정한다.
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
SCHEMA_NAME => '<스키마>',
OBJECT_NAME => '<테이블>',
OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
FLAGS => DBMS_REPAIR.SKIP_FLAG);
END;
/
설정이 적용되었는지 확인한다.
SELECT SKIP_CORRUPT FROM DBA_TABLES WHERE OWNER='<스키마>' AND TABLE_NAME='<테이블>';
테이블을 이동하거나 재생성한다.
ALTER TABLE <테이블> MOVE;
또는 export/import, CTAS로 새 테이블을 생성한 후 원본 테이블을 삭제한다.

LOB의 경우
Doc ID 293515.1 절차를 적용한다.

세그먼트를 드롭하면 블록은 FREE로 표시되고 이후 재사용 시 자동 재포맷된다.
RMAN VALIDATE를 실행해 V$DATABASE_BLOCK_CORRUPTION 또는 V$NONLOGGED_BLOCK의 잔여 표시를 제거한다.

모니터링 및 사전 탐지
RMAN REPORT UNRECOVERABLE 명령으로 NOLOGGING 이후 백업되지 않은 파일을 확인한다.
RMAN VALIDATE DATABASE NONLOGGED BLOCK 명령으로 비로그 블록을 검증한다.
V$DATAFILE의 관련 컬럼은 다음과 같다.
UNRECOVERABLE_CHANGE#
UNRECOVERABLE_TIME
FIRST_NONLOGGED_SCN
FIRST_NONLOGGED_TIME
스탠바이 환경에서는 event 16490 level 1을 설정하면 MRP가 alert log에 invalidated block 메시지를 남긴다.

NOLOGGING 블록 생성 시점 확인
Trace 파일의 블록 SCN 또는 V$DATABASE_BLOCK_CORRUPTION.CORRUPTION_CHANGE# 값을 사용한다.
12c 이상은 V$NONLOGGED_BLOCK.NONLOGGED_START_CHANGE# 값을 사용한다.
SELECT SCN_TO_TIMESTAMP(:SCN) FROM DUAL;
GV$ARCHIVED_LOG나 GV$LOG_HISTORY로 SCN 구간을 매핑하여 시점을 확인한다.

특수 케이스
11.1.0.6부터 11.2.0.1 버전까지 NOARCHIVELOG + Direct Path 작업 시 FORCE LOGGING이어도 AWR 또는 EM(SYSAUX)에서 ORA-1578과 ORA-26040이 발생할 수 있다.
Doc ID 1071869.1을 참조한다.
11.2.0.2 이상에서는 이 문제가 해결되었다.
Exadata 환경에서는 ORA-26040 대신 ORA-27616이 발생할 수 있다.
암호화된 블록에서는 ORA-28304와 ORA-26040이 함께 보고될 수 있다.

점검 및 조치 순서 요약
NOLOGGING 이후 백업이 있는지 확인한다.
스탠바이 여부를 확인하고 Primary와 비교한다.
RMAN VALIDATE 또는 DBV로 손상 블록을 확인한다.
세그먼트 종류에 맞는 복구 절차를 수행한다.
RMAN VALIDATE로 상태를 정리한다.
Primary에 FORCE LOGGING을 설정한다.
REPORT UNRECOVERABLE로 사전 모니터링을 수행한다.

출처 Oracle Support Doc ID 794505.1

----------------------------------------------문서 내용 종료-----------------------------------------

 

select * from tuner.TB_NOLOGGING_TEST_01;

...생략
999 rows selected
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/30/2025 23:42:53
ORA-01578: ORACLE data block corrupted (file # 2, block # 178178)
ORA-01110: data file 2: '/home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_data1.272.1213465041'
ORA-26040: Data block was loaded using the NOLOGGING option
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/RMAN_2520668267_110/trace/ora_rman_681_0.trc


BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
      SCHEMA_NAME => 'TUNER'                
    , OBJECT_NAME => 'TB_NOLOGGING_TEST_01' 
    , OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT
    , FLAGS       => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

 

select * from tuner.TB_NOLOGGING_TEST_01;

1000 rows selected.

Elapsed: 00:00:02.46

 

select * from tuner.TB_NOLOGGING_TEST_02;

...생략
1000 rows selected
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/30/2025 23:43:21
ORA-01578: ORACLE data block corrupted (file # 2, block # 175209)
ORA-01110: data file 2: '/home/oracle/backup_for_nologging_test/ORA19RF/datafile/tuner_data1.272.1213465041'
ORA-26040: Data block was loaded using the NOLOGGING option

 

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
      SCHEMA_NAME => 'TUNER'                
    , OBJECT_NAME => 'TB_NOLOGGING_TEST_02' 
    , OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT
    , FLAGS       => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

 

select * from tuner.TB_NOLOGGING_TEST_02;

1000 rows selected.

Elapsed: 00:00:01.25


--DBMS_REPAIR.SKIP_CORRUPT_BLOCKS 를 사용하면 해당 테이블 조회 시 에러 메시지는 사라짐
--하지만 결국 데이터는 유실된 상태임
--소스 DB에서 데이터를 DB Link로 가져오거나, datapump로 exporing해서 import 시켜야함


99. 작업 후 정리

 

99-1. 타켓 DB 정리

 

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ps -ef | grep -i pmon | grep -i ora19rf
oracle    1868     1  0 Oct30 ?        00:00:09 ora_pmon_ORA19RF1

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ export ORACLE_SID=ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 1 16:04:31 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


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

[ol8ora19rs1]<SYS@ORA19RF1>$ shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[ol8ora19rs1]<SYS@ORA19RF1>$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ ps -ef | grep -i pmon | grep -i ora19rf
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ rm -rf /home/oracle/backup_for_nologging_test
[ORA19RF1:oracle@ol8ora19rs1][/u01/app/oracle/admin]$ rm -rf /u01/app/oracle/admin/ORA19RF
[ORA19RF1:oracle@ol8ora19rs1][/u01/app/oracle/audit]$ rm -rf /u01/app/oracle/audit/ORA19RF1
[ORA19RF1:oracle@ol8ora19rs1][/home/oracle]$ rm -rf /u01/app/oracle/diag/rdbms/ora19rf

 

99-2. 소스 DB 정리

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/backup_for_nologging_test]$ rm -rf /home/oracle/backup_for_nologging_test

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 1 16:14:09 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


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

[ol8ora19rf1]<SYS@ORA19RF1>$

drop table TUNER.TB_NOLOGGING_TEST_01 purge;
drop table TUNER.TB_NOLOGGING_TEST_02 purge;

 

반응형

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

[2025-11-13] 12cR1의 신기능인 MAX_STRING_SIZE를 extended로 설정 후 Flashback database로 다시 Standard로 돌아올수 있는지 테스트  (0) 2025.11.13
[2025-10-30] 멀티태넌트 환경에서 NOLOGGING+APPEND 데이터 입력 후 원격지 서버에 DB 복구 후 ORA-1578_ORA-26040 발생 재현  (0) 2025.11.08
[2025-10-19] 멀티태넌트 환경에서 RAC to Single 로 동일 서버에서 크론 디비 생성하여 아카이브를 적용하는 실습 (19c에서 테스트)  (0) 2025.10.20
[2025-10-13] Oracle 19c 2 Node RAC 환경에서 PDB 단위 Flashback Database 실습  (0) 2025.10.13
[2025-03-31] FlashBack Database가 ON인 상태에서 DROP USER CASCADE 작업 실수 발생 시 FlashBack Database로 유저 및 데이터가 존재했던 시점으로 돌아가서 exp로 데이터 덤프 후 복구하는 테스트 (12cR2)  (0) 2025.10.12

+ Recent posts

티스토리툴바