[2025-10-30] 멀티태넌트 환경에서 NOLOGGING+APPEND 데이터 입력 후 원격지 서버에 DB 복구 후 ORA-1578_ORA-26040 발생 재현

■ [제목] [2025-10-30] 멀티태넌트 환경에서 NOLOGGING+APPEND 데이터 입력 후 원격지 서버에 DB 복구 후 ORA-1578/ORA-26040 발생 재현

 

[제목]

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

풀백업 후 TABLE 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명 : ORA19RS (echo -e 'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' | sqlplus -s / as sysdba) '
  PDB명 : ORA19RSP1 (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 : ol8ora19rs1 (hostname)
   Public IP : 192.168.240.41 (getent ahostsv4 `hostname` | awk '{print $1; exit}')
   Instance Name : ORA19RS1 (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 : ol8ora19rs2
   Public IP : 192.168.240.42 (getent ahostsv4 `hostname` | awk '{print $1; exit}')
   Instance Name : ORA19RS2 (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명 : ORA19RS
  PDB명 : ORA19RSP1
  Single DB
   Hostname : ol8ora19rf1
   Public IP : 192.168.240.31
   Instance Name : ORA19RS
  
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 모드로 테이블 생성 후 데이터 입력)

 

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 12:19:03 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@ORA19RS1>$ show con_name

CON_NAME
------------------------------
CDB$ROOT

 

[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ WRITE NO

 

--PDB로 접속  
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.01

 

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)

1 row selected.

Elapsed: 00:00:00.00

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-11-02 12:21:24

1 row selected.

Elapsed: 00:00:00.00

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

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 12:24:00 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@ORA19RS1>$ show con_name

CON_NAME
------------------------------
CDB$ROOT

[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ WRITE NO

 

--PDB 접속

[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.00

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)

1 row selected.

Elapsed: 00:00:00.01
  
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-11-02 12:25:24

1 row selected.

Elapsed: 00:00:00.00

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_LOB0000075733C00003$$      SYS_IL0000075733C00003$$       YES       YES     TUNER_DATA1          LOBSEGMENT             2,064       16.13

1 row selected.

Elapsed: 00:00:00.14

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 12:26:40 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@ORA19RS1>$ alter system checkpoint;

System altered.

Elapsed: 00:00:00.02

 

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

[ORA19RS2:oracle@ol8ora19rs2][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 12:27:10 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

 

[ol8ora19rs2]<SYS@ORA19RS2>$ alter system checkpoint;

System altered.

Elapsed: 00:00:00.68

2. DB 풀 백업 (소스 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]$ ls -l /home/oracle/backup_for_nologging_test
total 0
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 12:29:27 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@ORA19RS1>$ create pfile='/home/oracle/backup_for_nologging_test/initORA19RS.ora' from spfile;

File created.

Elapsed: 00:00:00.03

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ echo $ORACLE_UNQNAME
ORA19RS
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ echo $ORACLE_SID
ORA19RS1
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ echo $ORACLE_DBNAME
ORA19RS
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$

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

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 2 12:30:43 2025
Version 19.28.0.0.0

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

connected to target database: ORA19RS (DBID=1936516987)

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/ORA19RS_%U.bkp';
backup current controlfile format '/home/oracle/backup_for_nologging_test/ORA19RS_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
------- ---- -- ---------- ----------- ------------ -------------------
418     Full    1.12G      DISK        00:00:54     2025-11-02 12:32:48
        BP Key: 418   Status: AVAILABLE  Compressed: YES  Tag: TAG20251102T123154
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_d547p65q_421_1_1.bkp
  List of Datafiles in backup set 418
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 8917387    2025-11-02 12:31:54              NO    +DATA1/ORA19RS/DATAFILE/system.257.1214088939
  3       Full 8917387    2025-11-02 12:31:54              NO    +DATA1/ORA19RS/DATAFILE/sysaux.258.1214088965
  4       Full 8917387    2025-11-02 12:31:54              NO    +DATA1/ORA19RS/DATAFILE/undotbs1.259.1214088979
  7       Full 8917387    2025-11-02 12:31:54              NO    +DATA1/ORA19RS/DATAFILE/users.260.1214088981
  9       Full 8917387    2025-11-02 12:31:54              NO    +DATA1/ORA19RS/DATAFILE/undotbs2.270.1214089557

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
419     Full    391.88M    DISK        00:00:19     2025-11-02 12:33:08
        BP Key: 419   Status: AVAILABLE  Compressed: YES  Tag: TAG20251102T123154
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_d647p67h_422_1_1.bkp
  List of Datafiles in backup set 419
  Container ID: 3, PDB Name: ORA19RSP1
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/system.277.1214089911
  11      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/sysaux.276.1214089911
  12      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/undotbs1.275.1214089911
  13      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/undo_2.279.1214089919
  14      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/users.280.1214089923
  15      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/tuner_data1.282.1214210173
  16      Full 8917501    2025-11-02 12:32:49              NO    +DATA1/ORA19RS/40BB61DA40640933E0632AF0A8C05571/DATAFILE/tuner_idx1.283.1214210175

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
420     Full    407.38M    DISK        00:00:18     2025-11-02 12:33:32
        BP Key: 420   Status: AVAILABLE  Compressed: YES  Tag: TAG20251102T123154
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_d747p68a_423_1_1.bkp
  List of Datafiles in backup set 420
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 2463120    2025-10-09 23:10:29              NO    +DATA1/ORA19RS/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.266.1214089353
  6       Full 2463120    2025-10-09 23:10:29              NO    +DATA1/ORA19RS/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.267.1214089353
  8       Full 2463120    2025-10-09 23:10:29              NO    +DATA1/ORA19RS/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.268.1214089353

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
421     Full    1.16M      DISK        00:00:01     2025-11-02 12:33:40
        BP Key: 421   Status: AVAILABLE  Compressed: YES  Tag: TAG20251102T123154
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_d847p693_424_1_1.bkp
  SPFILE Included: Modification time: 2025-11-02 10:32:24
  SPFILE db_unique_name: ORA19RS
  Control File Included: Ckp SCN: 8917585      Ckp time: 2025-11-02 12:33:39

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
422     Full    19.08M     DISK        00:00:01     2025-11-02 12:33:43
        BP Key: 422   Status: AVAILABLE  Compressed: NO  Tag: TAG20251102T123342
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_CTL_d947p696_425_1_1_20251102
  Control File Included: Ckp SCN: 8917613      Ckp time: 2025-11-02 12:33:42

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
423     122.00K    DISK        00:00:00     2025-11-02 12:33:57
        BP Key: 423   Status: AVAILABLE  Compressed: NO  Tag: TAG20251102T123356
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1

  List of Archived Logs in backup set 423
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    130     8917302    2025-11-02 12:31:51 8917691    2025-11-02 12:33:55
  2    96      8917299    2025-11-02 12:31:50 8917695    2025-11-02 12:33:56

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
424     Full    19.11M     DISK        00:00:00     2025-11-02 12:33:58
        BP Key: 424   Status: AVAILABLE  Compressed: NO  Tag: TAG20251102T123358
        Piece Name: /home/oracle/backup_for_nologging_test/c-1936516987-20251102-00
  SPFILE Included: Modification time: 2025-11-02 10:32:24
  SPFILE db_unique_name: ORA19RS
  Control File Included: Ckp SCN: 8917713      Ckp time: 2025-11-02 12:33:58

 

RMAN> quit
quit

Recovery Manager complete.
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ls -l /home/oracle/backup_for_nologging_test/
total 2050164
-rw-r-----. 1 oracle asmadmin   20054016 Nov  2 12:33 c-1936516987-20251102-00
-rw-r--r--. 1 oracle asmadmin       2632 Nov  2 12:29 initORA19RS.ora
-rw-r-----. 1 oracle asmadmin     125440 Nov  2 12:33 ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
-rw-r-----. 1 oracle asmadmin   20021248 Nov  2 12:33 ORA19RS_CTL_d947p696_425_1_1_20251102
-rw-r-----. 1 oracle asmadmin 1199898624 Nov  2 12:32 ORA19RS_d547p65q_421_1_1.bkp
-rw-r-----. 1 oracle asmadmin  410918912 Nov  2 12:33 ORA19RS_d647p67h_422_1_1.bkp
-rw-r-----. 1 oracle asmadmin  427171840 Nov  2 12:33 ORA19RS_d747p68a_423_1_1.bkp
-rw-r-----. 1 oracle asmadmin    1228800 Nov  2 12:33 ORA19RS_d847p693_424_1_1.bkp
-rw-r-----. 1 oracle asmadmin   19939328 Nov  2 12:33 snapcf_CA.f

3. 풀 백업본을 원격지 서버에서 Restore 및 Recover (타켓 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]$ scp 192.168.240.41:/home/oracle/backup_for_nologging_test/* /home/oracle/backup_for_nologging_test
The authenticity of host '192.168.240.41 (192.168.240.41)' can't be established.
ECDSA key fingerprint is SHA256:DfEAl/+/q8kufpA7VjUayjI14hPyegZUQrxl9PQN4Ss.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.240.41' (ECDSA) to the list of known hosts.
oracle@192.168.240.41's password:
c-1936516987-20251102-00                      100%   19MB 243.5MB/s   00:00
initORA19RS.ora                               100% 2632     5.0MB/s   00:00
ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1     100%  123KB 112.8MB/s   00:00
ORA19RS_CTL_d947p696_425_1_1_20251102         100%   19MB 184.4MB/s   00:00
ORA19RS_d547p65q_421_1_1.bkp                  100% 1144MB 211.3MB/s   00:05
ORA19RS_d647p67h_422_1_1.bkp                  100%  392MB 232.3MB/s   00:01
ORA19RS_d747p68a_423_1_1.bkp                  100%  407MB  35.4MB/s   00:11
ORA19RS_d847p693_424_1_1.bkp                  100% 1200KB  85.6MB/s   00:00
snapcf_CA.f                                   100%   19MB 237.5MB/s   00:00

 

--grid os user로 접속한 후 리스너 정보 추출
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
[+ASM1:grid@ol8ora19rf1][/home/grid]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-NOV-2025 12:42:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                01-NOV-2025 21:37:20
Uptime                    0 days 15 hr. 4 min. 43 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/ol8ora19rf1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.31)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.34)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA1" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA1" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORA19RF" has 1 instance(s).
  Instance "ORA19RF1", status READY, has 1 handler(s) for this service...
Service "ORA19RFXDB" has 1 instance(s).
  Instance "ORA19RF1", status READY, has 1 handler(s) for this service...
The command completed successfully

--oracle os user
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/backup_for_nologging_test]$ cp /home/oracle/backup_for_nologging_test/initORA19RS.ora /home/oracle/backup_for_nologging_test/initORA19RS.ora.bak
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/backup_for_nologging_test]$ vi /home/oracle/backup_for_nologging_test/initORA19RS.ora
[ORA19RS:oracle@ol8ora19rf1][/home/oracle/backup_for_nologging_test]$ cat /home/oracle/backup_for_nologging_test/initORA19RS.ora
*.audit_file_dest='/u01/app/oracle/admin/ORA19RS/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/home/oracle/backup_for_nologging_test/ORA19RS/controlfile/controlfile_01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/backup_for_nologging_test/ORA19RS/datafile/'
*.db_create_online_log_dest_1='/home/oracle/backup_for_nologging_test/ORA19RS/redolog_1'
*.db_create_online_log_dest_2='/home/oracle/backup_for_nologging_test/ORA19RS/redolog_2'
*.db_name='ORA19RS'
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19RSXDB)'
*.enable_pluggable_database=true
*.filesystemio_options='SETALL'
*.heat_map='OFF'
family:dw_helper.instance_mode='read-only'
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.240.31)(PORT=1521)))'
*.log_archive_dest_1='LOCATION=/home/oracle/backup_for_nologging_test/ORA19RS/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=256m
*.processes=1432
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan='FORCE:'
*.sga_target=1024m
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.uniform_log_timestamp_format=FALSE
*.use_large_pages='true'

 

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

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /u01/app/oracle/admin/ORA19RS/adump
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RS/controlfile
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RS/archivedlog 
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RS/datafile
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RS/redolog_1
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/backup_for_nologging_test/ORA19RS/redolog_2

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export ORACLE_SID=ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_SID
ORA19RS

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:03:03 2025
Version 19.28.0.0.0

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

Connected to an idle instance.

[ol8ora19rf1]<SYS@ORA19RS>$ startup nomount pfile='/home/oracle/backup_for_nologging_test/initORA19RS.ora';
ORACLE instance started.

Total System Global Area 1073738760 bytes
Fixed Size                  9188360 bytes
Variable Size             427819008 bytes
Database Buffers          629145600 bytes
Redo Buffers                7585792 bytes

--새로운 ssh 접속

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

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 2 13:04:21 2025
Version 19.28.0.0.0

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

connected to target database: ORA19RS (not mounted)

RMAN> host 'ls -l /home/oracle/backup_for_nologging_test';
host 'ls -l /home/oracle/backup_for_nologging_test';
total 2050164
-rw-r-----. 1 oracle oinstall   20054016 Nov  2 12:40 c-1936516987-20251102-00
-rw-r--r--. 1 oracle oinstall       1500 Nov  2 13:01 initORA19RS.ora
-rw-r--r--. 1 oracle oinstall       2632 Nov  2 12:45 initORA19RS.ora.bak
drwxr-xr-x. 7 oracle oinstall         94 Nov  2 12:51 ORA19RS
-rw-r-----. 1 oracle oinstall     125440 Nov  2 12:40 ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
-rw-r-----. 1 oracle oinstall   20021248 Nov  2 12:40 ORA19RS_CTL_d947p696_425_1_1_20251102
-rw-r-----. 1 oracle oinstall 1199898624 Nov  2 12:40 ORA19RS_d547p65q_421_1_1.bkp
-rw-r-----. 1 oracle oinstall  410918912 Nov  2 12:40 ORA19RS_d647p67h_422_1_1.bkp
-rw-r-----. 1 oracle oinstall  427171840 Nov  2 12:40 ORA19RS_d747p68a_423_1_1.bkp
-rw-r-----. 1 oracle oinstall    1228800 Nov  2 12:40 ORA19RS_d847p693_424_1_1.bkp
-rw-r-----. 1 oracle oinstall   19939328 Nov  2 12:40 snapcf_CA.f
host command complete

RMAN> restore controlfile from '/home/oracle/backup_for_nologging_test/ORA19RS_CTL_d947p696_425_1_1_20251102';
restore controlfile from '/home/oracle/backup_for_nologging_test/ORA19RS_CTL_d947p696_425_1_1_20251102';
Starting restore at 2025-11-02 13:04:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=277 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/backup_for_nologging_test/ORA19RS/controlfile/controlfile_01.ctl
Finished restore at 2025-11-02 13:04:51

RMAN> alter database mount;
alter database mount;
released channel: ORA_DISK_1
Statement processed

 

------------------------------------------여기서 잠깐!! 소스DB로 접속해서 아래의 SQL문을 수행 시작----------------------------------------
SELECT
       'SET NEWNAME FOR DATAFILE '||df.file#||
       ' TO '''||
       CASE
         WHEN vc.name = 'PDB$SEED' THEN '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/'
         WHEN vc.name = 'ORA19RSP1'     THEN '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/'
         ELSE '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/'
       END
       ||'%b'';' as set_newname_for_rman
FROM   v$datafile df
JOIN   v$containers vc
  ON   vc.con_id = df.con_id
WHERE  1=1
  and vc.name IN ('CDB$ROOT', 'PDB$SEED', 'ORA19RSP1')
ORDER  BY vc.name, df.file#;

 

<결과>
SET NEWNAME FOR DATAFILE 1 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
SET NEWNAME FOR DATAFILE 3 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
SET NEWNAME FOR DATAFILE 4 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
SET NEWNAME FOR DATAFILE 7 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
SET NEWNAME FOR DATAFILE 9 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
SET NEWNAME FOR DATAFILE 10 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 11 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 12 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 13 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 14 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 15 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 16 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
SET NEWNAME FOR DATAFILE 5 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
SET NEWNAME FOR DATAFILE 6 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
SET NEWNAME FOR DATAFILE 8 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
----------------------------------------여기서 잠깐!! 소스DB로 접속해서 아래의 SQL문을 수행 종료----------------------------------------

RUN {
 SET NEWNAME FOR DATAFILE 1 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
 SET NEWNAME FOR DATAFILE 3 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
 SET NEWNAME FOR DATAFILE 4 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
 SET NEWNAME FOR DATAFILE 7 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
 SET NEWNAME FOR DATAFILE 9 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/%b';
 SET NEWNAME FOR DATAFILE 10 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 11 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 12 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 13 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 14 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 15 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 16 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/%b';
 SET NEWNAME FOR DATAFILE 5 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
 SET NEWNAME FOR DATAFILE 6 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
 SET NEWNAME FOR DATAFILE 8 TO '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/%b';
 RESTORE DATABASE;
 SWITCH DATAFILE ALL;
}
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2025-11-02 13:25:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=277 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/ORA19RS/datafile/system.257.1214088939
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/sysaux.258.1214088965
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs1.259.1214088979
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/users.260.1214088981
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs2.270.1214089557
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RS_d547p65q_421_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RS_d547p65q_421_1_1.bkp tag=TAG20251102T123154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/system.277.1214089911
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/sysaux.276.1214089911
channel ORA_DISK_1: restoring datafile 00012 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undotbs1.275.1214089911
channel ORA_DISK_1: restoring datafile 00013 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undo_2.279.1214089919
channel ORA_DISK_1: restoring datafile 00014 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/users.280.1214089923
channel ORA_DISK_1: restoring datafile 00015 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_data1.282.1214210173
channel ORA_DISK_1: restoring datafile 00016 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_idx1.283.1214210175
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RS_d647p67h_422_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RS_d647p67h_422_1_1.bkp tag=TAG20251102T123154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/system.266.1214089353
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/sysaux.267.1214089353
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/undotbs1.268.1214089353
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RS_d747p68a_423_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RS_d747p68a_423_1_1.bkp tag=TAG20251102T123154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2025-11-02 13:27:54

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/system.257.1214088939
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/sysaux.258.1214088965
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs1.259.1214088979
datafile 5 switched to datafile copy
input datafile copy RECID=22 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/system.266.1214089353
datafile 6 switched to datafile copy
input datafile copy RECID=23 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/sysaux.267.1214089353
datafile 7 switched to datafile copy
input datafile copy RECID=24 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/users.260.1214088981
datafile 8 switched to datafile copy
input datafile copy RECID=25 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/undotbs1.268.1214089353
datafile 9 switched to datafile copy
input datafile copy RECID=26 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs2.270.1214089557
datafile 10 switched to datafile copy
input datafile copy RECID=27 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/system.277.1214089911
datafile 11 switched to datafile copy
input datafile copy RECID=28 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/sysaux.276.1214089911
datafile 12 switched to datafile copy
input datafile copy RECID=29 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undotbs1.275.1214089911
datafile 13 switched to datafile copy
input datafile copy RECID=30 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undo_2.279.1214089919
datafile 14 switched to datafile copy
input datafile copy RECID=31 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/users.280.1214089923
datafile 15 switched to datafile copy
input datafile copy RECID=32 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_data1.282.1214210173
datafile 16 switched to datafile copy
input datafile copy RECID=33 STAMP=1216128474 file name=/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_idx1.283.1214210175

RMAN>

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 125440 Nov  2 12:40 /home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
host command complete

RMAN> list backup of archivelog all;
list backup of archivelog all;
specification does not match any backup in the repository
--> 소스 DB에서 컨트롤 파일 백업 시 아카이브 로그 상황임
--> 소스 DB에서 컨트롤 파일 백업 후 아카이브 로그를 백업했으므로 아카이브 로그의 백업본을 catalog에 등록시켜야함
--> (결국 컨트롤 파일을 백업한 시점에는 백업한 아카이브가 없었던 상황이라 안보이는 것임, 이걸 이해하는 것이 중요함)

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

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_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/ORA19RS_ARCHIVE_20251102_da47p69l_s426_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
------- ---------- ----------- ------------ -------------------
422     122.00K    DISK        00:00:00     2025-11-02 12:33:57
        BP Key: 422   Status: AVAILABLE  Compressed: NO  Tag: TAG20251102T123356
        Piece Name: /home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1

  List of Archived Logs in backup set 422
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    130     8917302    2025-11-02 12:31:51 8917691    2025-11-02 12:33:55
  2    96      8917299    2025-11-02 12:31:50 8917695    2025-11-02 12:33:56

RMAN>

--> 해당 아카이브 로그의 백업본이 catalog 등록됨

RMAN> RECOVER DATABASE;
RECOVER DATABASE;
Starting recover at 2025-11-02 13:29:29
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=2 sequence=96
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=130
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1 tag=TAG20251102T123356
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/ORA19RS/archivedlog/1_130_1214089022.ARC thread=1 sequence=130
archived log file name=/home/oracle/backup_for_nologging_test/ORA19RS/archivedlog/2_96_1214089022.ARC thread=2 sequence=96
unable to find archived log
archived log thread=1 sequence=131
RMAN Command Id : 2025-11-02T13:24:47
RMAN Command Id : 2025-11-02T13:24:47
RMAN Command Id : 2025-11-02T13:24:47
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/02/2025 13:29:31
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 131 and starting SCN of 8917691
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/RMAN_3429632378_110/trace/ora_rman_1140_0.trc
RMAN Server Diagnostic Trace file : /u01/app/oracle/diag/rdbms/ora19rs/ORA19RS/trace/ORA19RS_ora_2027.trc

--데이터 확인을 위해 read only로 open함
RMAN> alter database open read only;
alter database open read only;
Statement processed

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:31:32 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@ORA19RS>$ show con_name

CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rf1]<SYS@ORA19RS>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      MOUNTED    (NULL)
[ol8ora19rf1]<SYS@ORA19RS>$ alter pluggable database ORA19RSP1 open read only;

Pluggable database altered.

Elapsed: 00:00:00.69
[ol8ora19rf1]<SYS@ORA19RS>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ ONLY  NO
[ol8ora19rf1]<SYS@ORA19RS>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.12

[ol8ora19rf1]<SYS@ORA19RS>$ select count(*) from TUNER.TB_NOLOGGING_TEST_01;

  COUNT(*)
----------
      1000

1 row selected.

Elapsed: 00:00:00.06
[ol8ora19rf1]<SYS@ORA19RS>$ select count(*) from TUNER.TB_NOLOGGING_TEST_02;

  COUNT(*)
----------
      1000

1 row selected.

Elapsed: 00:00:00.02

--> 테스트 테이블에 입력한 데이터가 존재하는 상황임  

 

--데이터를 확인했으니 해당 타켓 DB 인스턴스를 내리고 다시 mount 모드로 기동 시킬것임
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_SID
ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:35:14 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@ORA19RS>$ shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
[ol8ora19rf1]<SYS@ORA19RS>$ startup nomount pfile='/home/oracle/backup_for_nologging_test/initORA19RS.ora';
ORACLE instance started.

Total System Global Area 1073738760 bytes
Fixed Size                  9188360 bytes
Variable Size             427819008 bytes
Database Buffers          629145600 bytes
Redo Buffers                7585792 bytes

[ol8ora19rf1]<SYS@ORA19RS>$ alter database mount;

Database altered.

Elapsed: 00:00:04.07

 

[ol8ora19rf1]<SYS@ORA19RS>$
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
------------- ---------- ------------ ------------- ------------------- ------------- ------------- ------------- ------------- --------------- ---
   1936516987 ORA19RS    MOUNTED            1920977 2025-10-09 22:57:02       8917312       8917294       8917691             0 ORA19RS         YES

1 row selected.

Elapsed: 00:00:00.00

--> 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 A95            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/ORA19RS/datafile/system.257.1214088939                         8917691 (NULL)       CDB$ROOT
    3 ONLINE     SYSAUX                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/sysaux.258.1214088965                         8917691 (NULL)       CDB$ROOT
    4 ONLINE     UNDOTBS1                   1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs1.259.1214088979                       8917691 (NULL)       CDB$ROOT
    5 ONLINE     SYSTEM                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/system.266.1214089353                 2463120 (NULL)       PDB$SEED
    6 ONLINE     SYSAUX                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/sysaux.267.1214089353                 2463120 (NULL)       PDB$SEED
    7 ONLINE     USERS                      1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/users.260.1214088981                          8917691 (NULL)       CDB$ROOT
    8 ONLINE     UNDOTBS1                   1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/PDBSEED/undotbs1.268.1214089353               2463120 (NULL)       PDB$SEED
    9 ONLINE     UNDOTBS2                   1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/undotbs2.270.1214089557                       8917691 (NULL)       CDB$ROOT
   10 ONLINE     SYSTEM                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/system.277.1214089911               8917691 (NULL)       ORA19RSP1
   11 ONLINE     SYSAUX                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/sysaux.276.1214089911               8917691 (NULL)       ORA19RSP1
   12 ONLINE     UNDOTBS1                   1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undotbs1.275.1214089911             8917691 (NULL)       ORA19RSP1
   13 ONLINE     UNDO_2                     1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/undo_2.279.1214089919               8917691 (NULL)       ORA19RSP1
   14 ONLINE     USERS                      1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/users.280.1214089923                8917691 (NULL)       ORA19RSP1
   15 ONLINE     TUNER_DATA1                1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_data1.282.1214210173          8917691 (NULL)       ORA19RSP1
   16 ONLINE     TUNER_IDX1                 1920977 /home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_idx1.283.1214210175           8917691 (NULL)       ORA19RSP1

15 rows selected.

Elapsed: 00:00:00.02

--$DATABASE의 CONTROLFILE_CHANGE# = 8917691
--그리고 V$DATAFILE_HEADER의 각 DATAFILE CHECKPOINT_CHANGE# = 8917691 으로 완전히 일치함
--복구가 끝난 직후(RECOVER DATABASE) DB가 완전히 일관된 상태임을 의미

 

--CHECKPOINT_CHANGE# (V$DATABASE) : 8917312 (복구 시작 시점에 데이터파일이 도달해 있던 SCN)
--CONTROLFILE_CHANGE# (V$DATABASE) : 8917691 (복구를 마친 시점에 컨트롤파일의 SCN 까지 도달함)
--V$DATAFILE_HEADER.CHECKPOINT_CHANGE# : 8917691)복구 완료 후, 모든 데이터파일이 컨트롤파일 SCN과 동일하게 갱신됨)
--> 복구 완료 후, 모든 데이터 파일(pdb$seed 제외하고)의 scn이 컨트롤 파일 SCN과 동일하게 갱신됨

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

 

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

 

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

 

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:43:41 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@ORA19RS1>$ show con_name

CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    8939622

1 row selected.

Elapsed: 00:00:00.01
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ show con_name

CON_NAME
------------------------------
ORA19RSP1
[ol8ora19rs1]<SYS@ORA19RS1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    8939651

1 row selected.

Elapsed: 00:00:00.01

[ol8ora19rs1]<SYS@ORA19RS1>$ alter table TUNER.TB_NOLOGGING_TEST_01 nologging;

Table altered.

 

[ol8ora19rs1]<SYS@ORA19RS1>$
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
;

[ol8ora19rs1]<SYS@ORA19RS1>$ commit;

Commit complete.

Elapsed: 00:00:00.01
[ol8ora19rs1]<SYS@ORA19RS1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    8941095

1 row selected.

Elapsed: 00:00:00.01

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

 

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:50:06 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@ORA19RS1>$ show con_name

CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rs1]<SYS@ORA19RS1>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ WRITE NO
[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ora19rsp1;

Session altered.

Elapsed: 00:00:00.00
[ol8ora19rs1]<SYS@ORA19RS1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    8942224

1 row selected.

Elapsed: 00:00:00.01

[ol8ora19rs1]<SYS@ORA19RS1>$
alter table TUNER.TB_NOLOGGING_TEST_02 nologging;
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
;

[ol8ora19rs1]<SYS@ORA19RS1>$ COMMIT;

[ol8ora19rs1]<SYS@ORA19RS1>$ select current_scn from v$database;

CURRENT_SCN
-----------
    8942396 ------------> 타켓 DB가 가야할 목표 SCN임!!

1 row selected.
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:51:50 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@ORA19RS1>$ alter system checkpoint;

System altered.

Elapsed: 00:00:02.23
[ORA19RS2:oracle@ol8ora19rs2][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS2:oracle@ol8ora19rs2][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 13:52:16 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

[ol8ora19rs2]<SYS@ORA19RS2>$ alter system checkpoint;

System altered.

Elapsed: 00:00:01.46

[ol8ora19rs1]<SYS@ORA19RS1>$ ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

Elapsed: 00:00:02.11

[ol8ora19rs2]<SYS@ORA19RS2>$ ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

Elapsed: 00:00:01.29

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# <= 8942396   --타켓 DB가 가야할 지점은 8942396 이므로 FIRST_CHANGE# 이 8942396 보다 작거나 같은 것은 복구 대상임
   AND A.NEXT_CHANGE#  >= 8917692   --타켓 DB은 8917691 까지 적용된 상태이므로 A.NEXT_CHANGE# 이 8917691+1=8917692 보다 같거나 큰 것은 복구대상임
 ORDER BY A.THREAD#, A.SEQUENCE#
;

ARCHIVELOG NAME                                                        THR     SEQ       CHANGE#       CHANGE#       CHANGE# ARCHIVED DELETED
--------------------------------------------------------------------- ---- ------- ------------- ------------- ------------- -------- --------
+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_1_seq_131.316.1216129963      1     131       1920977       8917691       8942493 YES      NO
(NULL)                                                                   2      96       1920977       8917299       8917695 YES      YES
+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_2_seq_97.312.1216129963       2      97       1920977       8917695       8942497 YES      NO

3 rows selected.

Elapsed: 00:00:00.00

--새로운 ssh 창 열어서
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ echo $ORACLE_SID
ORA19RS1
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 2 13:57:21 2025
Version 19.28.0.0.0

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

connected to target database: ORA19RS (DBID=1936516987)

run {
crosscheck archivelog all;
BACKUP ARCHIVELOG FROM SEQUENCE 131 UNTIL SEQUENCE 131 THREAD 1 format '/home/oracle/backup_for_nologging_test/ARCHIVE_%d_%T_%u_s%s_p%p';
BACKUP ARCHIVELOG FROM SEQUENCE 97 UNTIL SEQUENCE 97 THREAD 2 format '/home/oracle/backup_for_nologging_test/ARCHIVE_%d_%T_%u_s%s_p%p';
}
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=563 instance=ORA19RS1 device type=DISK
validation succeeded for archived log
archived log file name=+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_1_seq_131.316.1216129963 RECID=213 STAMP=1216129962
validation succeeded for archived log
archived log file name=+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_1_seq_132.314.1216129965 RECID=216 STAMP=1216129965
validation succeeded for archived log
archived log file name=+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_2_seq_97.312.1216129963 RECID=214 STAMP=1216129962
validation succeeded for archived log
archived log file name=+FRA1/ORA19RS/ARCHIVELOG/2025_11_02/thread_2_seq_98.313.1216129963 RECID=215 STAMP=1216129963
Crosschecked 4 objects

Starting backup at 2025-11-02 13:57:47
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=131 RECID=213 STAMP=1216129962
channel ORA_DISK_1: starting piece 1 at 2025-11-02 13:57:47
channel ORA_DISK_1: finished piece 1 at 2025-11-02 13:57:48
piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_p1 tag=TAG20251102T135747 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-11-02 13:57:48

Starting backup at 2025-11-02 13:57:48
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=97 RECID=214 STAMP=1216129962
channel ORA_DISK_1: starting piece 1 at 2025-11-02 13:57:48
channel ORA_DISK_1: finished piece 1 at 2025-11-02 13:57:49
piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dd47pb6s_s429_p1 tag=TAG20251102T135748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2025-11-02 13:57:49

Starting Control File and SPFILE Autobackup at 2025-11-02 13:57:49
piece handle=/home/oracle/backup_for_nologging_test/c-1936516987-20251102-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-11-02 13:57:50

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ ls -l /home/oracle/backup_for_nologging_test
total 2050164
-rw-r-----. 1 oracle oinstall   20054016 Nov  2 12:40 c-1936516987-20251102-00
-rw-r--r--. 1 oracle oinstall       1500 Nov  2 13:01 initORA19RS.ora
-rw-r--r--. 1 oracle oinstall       2632 Nov  2 12:45 initORA19RS.ora.bak
drwxr-xr-x. 7 oracle oinstall         94 Nov  2 12:51 ORA19RS
-rw-r-----. 1 oracle oinstall     125440 Nov  2 12:40 ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
-rw-r-----. 1 oracle oinstall   20021248 Nov  2 12:40 ORA19RS_CTL_d947p696_425_1_1_20251102
-rw-r-----. 1 oracle oinstall 1199898624 Nov  2 12:40 ORA19RS_d547p65q_421_1_1.bkp
-rw-r-----. 1 oracle oinstall  410918912 Nov  2 12:40 ORA19RS_d647p67h_422_1_1.bkp
-rw-r-----. 1 oracle oinstall  427171840 Nov  2 12:40 ORA19RS_d747p68a_423_1_1.bkp
-rw-r-----. 1 oracle oinstall    1228800 Nov  2 12:40 ORA19RS_d847p693_424_1_1.bkp
-rw-r-----. 1 oracle oinstall   19939328 Nov  2 12:40 snapcf_CA.f
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$

 

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

 

[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export ORACLE_SID=ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ echo $ORACLE_SID
ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 14:02:22 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@ORA19RS>$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ rt

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 2 14:02:31 2025
Version 19.28.0.0.0

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

connected to target database: ORA19RS (DBID=1936516987, not open)

RMAN> host 'ls -l /home/oracle/backup_for_nologging_test';
host 'ls -l /home/oracle/backup_for_nologging_test';
total 2117488
-rw-r-----. 1 oracle oinstall   38248448 Nov  2 14:00 ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_p1
-rw-r-----. 1 oracle oinstall   30688256 Nov  2 14:01 ARCHIVE_ORA19RS_20251102_dd47pb6s_s429_p1
-rw-r-----. 1 oracle oinstall   20054016 Nov  2 12:40 c-1936516987-20251102-00
-rw-r--r--. 1 oracle oinstall       1500 Nov  2 13:01 initORA19RS.ora
-rw-r--r--. 1 oracle oinstall       2632 Nov  2 12:45 initORA19RS.ora.bak
drwxr-xr-x. 7 oracle oinstall         94 Nov  2 12:51 ORA19RS
-rw-r-----. 1 oracle oinstall     125440 Nov  2 12:40 ORA19RS_ARCHIVE_20251102_da47p69l_s426_p1
-rw-r-----. 1 oracle oinstall   20021248 Nov  2 12:40 ORA19RS_CTL_d947p696_425_1_1_20251102
-rw-r-----. 1 oracle oinstall 1199898624 Nov  2 12:40 ORA19RS_d547p65q_421_1_1.bkp
-rw-r-----. 1 oracle oinstall  410918912 Nov  2 12:40 ORA19RS_d647p67h_422_1_1.bkp
-rw-r-----. 1 oracle oinstall  427171840 Nov  2 12:40 ORA19RS_d747p68a_423_1_1.bkp
-rw-r-----. 1 oracle oinstall    1228800 Nov  2 12:40 ORA19RS_d847p693_424_1_1.bkp
-rw-r-----. 1 oracle oinstall   19939328 Nov  2 12:40 snapcf_CA.f
host command complete

RMAN> catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_p1'
catalog start with '/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_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_ORA19RS_20251102_dc47pb6r_s428_p1

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

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

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

RUN {
 SET UNTIL SCN 8942396;
 RECOVER DATABASE;
}

executing command: SET until clause

Starting recover at 2025-11-02 14:03:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1639 device type=DISK

starting media recovery

archived log for thread 2 with sequence 96 is already on disk as file /home/oracle/backup_for_nologging_test/ORA19RS/archivedlog/2_96_1214089022.ARC
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=131
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_p1
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dc47pb6r_s428_p1 tag=TAG20251102T135747
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/ORA19RS/archivedlog/1_131_1214089022.ARC thread=1 sequence=131
archived log file name=/home/oracle/backup_for_nologging_test/ORA19RS/archivedlog/2_96_1214089022.ARC thread=2 sequence=96
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=97
channel ORA_DISK_1: reading from backup piece /home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dd47pb6s_s429_p1
channel ORA_DISK_1: piece handle=/home/oracle/backup_for_nologging_test/ARCHIVE_ORA19RS_20251102_dd47pb6s_s429_p1 tag=TAG20251102T135748
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/ORA19RS/archivedlog/2_97_1214089022.ARC thread=2 sequence=97
media recovery complete, elapsed time: 00:00:01
Finished recover at 2025-11-02 14:03:58

RMAN> alter database open resetlogs;
alter database open resetlogs;
Statement processed

 

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

 

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

[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 14:05:01 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@ORA19RS>$ show con_name

CON_NAME
------------------------------
CDB$ROOT
[ol8ora19rf1]<SYS@ORA19RS>$ show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      MOUNTED    (NULL)
[ol8ora19rf1]<SYS@ORA19RS>$ alter pluggable database ORA19RSP1 open;

Pluggable database altered.

Elapsed: 00:00:00.81
[ol8ora19rf1]<SYS@ORA19RS>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.04

[ol8ora19rf1]<SYS@ORA19RS>$ select * from tuner.TB_NOLOGGING_TEST_01;

...생략
ERROR:
ORA-01578: ORACLE data block corrupted (file # 15, block # 178178)
ORA-01110: data file 15: '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_data1.282.1214210173'
ORA-26040: Data block was loaded using the NOLOGGING option

 

990 rows selected.

Elapsed: 00:00:03.33

[ol8ora19rf1]<SYS@ORA19RS>$
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;
/

 

[ol8ora19rf1]<SYS@ORA19RS>$ select * from tuner.TB_NOLOGGING_TEST_01;

1000 rows selected.

Elapsed: 00:00:02.46

 

[ol8ora19rf1]<SYS@ORA19RS>$ select * from tuner.TB_NOLOGGING_TEST_02;

...생략
ERROR:
ORA-01578: ORACLE data block corrupted (file # 15, block # 175201)
ORA-01110: data file 15: '/home/oracle/backup_for_nologging_test/ORA19RS/datafile/ORA19RSP1/tuner_data1.282.1214210173'
ORA-26040: Data block was loaded using the NOLOGGING option

 

1000 rows selected.

Elapsed: 00:00:02.00

 

[ol8ora19rf1]<SYS@ORA19RS>$
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;
/

 

[ol8ora19rf1]<SYS@ORA19RS>$ 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 정리

 

[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ps -ef | grep -i pmon | grep -i ora19rs
oracle    8480     1  0 14:33 ?        00:00:00 ora_pmon_ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ export ORACLE_SID=ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 14:35:45 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@ORA19RS>$ shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
[ol8ora19rf1]<SYS@ORA19RS>$ quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ ps -ef | grep -i pmon | grep -i ora19rs
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ rm -rf /home/oracle/backup_for_nologging_test
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ rm -rf /u01/app/oracle/admin/ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ rm -rf /u01/app/oracle/audit/ORA19RS
[ORA19RS:oracle@ol8ora19rf1][/home/oracle]$ rm -rf /u01/app/oracle/diag/rdbms/ora19rs

99-2. 소스 DB 정리

 

[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ rm -rf /home/oracle/backup_for_nologging_test
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RS1:oracle@ol8ora19rs1][/home/oracle]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 2 14:38:29 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@ORA19RS1>$ show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA19RSP1                      READ WRITE NO

[ol8ora19rs1]<SYS@ORA19RS1>$ alter session set container=ORA19RSP1;

Session altered.

Elapsed: 00:00:00.01
[ol8ora19rs1]<SYS@ORA19RS1>$ drop table TUNER.TB_NOLOGGING_TEST_01 purge;

Table dropped.

Elapsed: 00:00:00.19
[ol8ora19rs1]<SYS@ORA19RS1>$ drop table TUNER.TB_NOLOGGING_TEST_02 purge;

Table dropped.

Elapsed: 00:00:00.07
[ol8ora19rs1]<SYS@ORA19RS1>$