테이블 nologging / append insert 복구 불가(ORA-26040)

DBMS : oracle 19.3

 

- RMAN 백업 -> nologging 테이블에 append 힌트사용하여 데이터 적재 -> 백업데이터 restore / recover 작업 후 해당 테이블 조회에러 발생(ORA-26040) - nologging append 상태의 데이터는 복구불가지만  해당 테이블 정상화 시키는 방법

 

테이블 생성

SQL> CREATE TABLE tab1 tablespace ts01 AS SELECT * FROM dba_objects ;

Table created.

SQL> SELECT count(*) FROM tab1 ;

  COUNT(*)
----------
     73067

SQL> ALTER TABLE tab1 nologging ;

Table altered.

SQL> SELECT table_name, LOGGING, tablespace_name
FROM DBA_TABLES
WHERE table_name = 'TAB1'
;

TABLE_NAME			 LOGGING			TABLESPACE_NAME
-------------------------------- ------------------------------ ------------------------------
TAB1				 NO				TS01

 rman 백업

[oracle@svr1 ~]$ rman target /

RMAN> backup format '/home/oracle/bak/%T_TS01.dat' tablespace ts01 ; 

Starting backup at 05-JUL-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORA19C/ts01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUL-23
channel ORA_DISK_1: finished piece 1 at 05-JUL-23
piece handle=/home/oracle/bak/20230705_TS01.dat tag=TAG20230705T213417 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUL-23

Starting Control File and SPFILE Autobackup at 05-JUL-23
piece handle=/home/oracle/reco/ORA19C/autobackup/2023_07_05/o1_mf_s_1141421658_lbbrtbo1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUL-23

RMAN>

 APPEND INSERT 

[oracle@svr1 ~]$ sqlplus / as sysdba

SQL> INSERT /*+ append */INTO  tab1 SELECT * FROM dba_objects ;

73068 rows created.

SQL> commit ;

Commit complete.

SQL> SELECT count(*) FROM tab1 ;

  COUNT(*)
----------
    146135

SQL> alter system switch logfile;

System altered.

SQL> ALTER tablespace ts01 offline ;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, STATUS 
FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME = 'TS01'
;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
TS01			       OFFLINE

 백업 데이터 restore & recover

[oracle@svr1 ~]$ rman target /

RMAN>  restore tablespace ts01;

Starting restore at 05-JUL-23
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /ORA19/app/oracle/oradata/ORA19C/ts01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/20230705_TS01.dat
channel ORA_DISK_1: piece handle=/home/oracle/bak/20230705_TS01.dat tag=TAG20230705T213417
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-JUL-23

RMAN> recover tablespace ts01;

Starting recover at 05-JUL-23
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-JUL-23

 테이블 조회 시 에러 발생

SQL> ALTER tablespace ts01 online ;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, STATUS 
FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME = 'TS01'
;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
TS01			       ONLINE

SQL>  SELECT count(*) FROM tab1 ;
 SELECT count(*) FROM tab1
        *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 139)
ORA-01110: data file 5: '/ORA19/app/oracle/oradata/ORA19C/ts01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

 복구방법

1. currupt block skip
SQL> exec dbms_repair.skip_corrupt_blocks (schema_name => '&schema_name', object_name => '&object_name', object_type => dbms_repair.table_object, flags => dbms_repair.SKIP_FLAG);
Enter value for schema_name: SYS
Enter value for object_name: TAB1

PL/SQL procedure successfully completed.


2. 테이블 move 진행(동일 테이블스페이스 또는 원하는 테이블스페이스로 move)
SQL> alter table tab1 move;

Table altered.

-- 다른 테이블스페이스에 move 작업 시 명령어
alter table table_name move tablespace tablespace_name;

3. currupt block no skip
SQL> exec dbms_repair.skip_corrupt_blocks (schema_name => '&schema_name', object_name => '&object_name', object_type => dbms_repair.table_object, flags => dbms_repair.NOSKIP_FLAG);
Enter value for schema_name: SYS
Enter value for object_name: TAB1

PL/SQL procedure successfully completed.

SQL> SELECT count(*) FROM tab1 ;

  COUNT(*)
----------
     73067

SQL>