ORA-01642: begin backup not needed for read-only tablespace

ORA-01642: begin backup not needed for read-only tablespace

 

#환경
OS : Red Hat Enterprise Linux Server release 7.8 (Maipo)
DBMS : Oracle 18.0.0.0.0

 

설명:
오라클 OPEN 백업중 특정 테이블스페이스 백업이 완료가 되지 않아 확인후 처리함.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 NOT ACTIVE           77668278 29-SEP-22          0
         2 NOT ACTIVE           77668278 29-SEP-22          0
         3 NOT ACTIVE           77668278 29-SEP-22          0
         5 NOT ACTIVE           77668278 29-SEP-22          0
         7 NOT ACTIVE           77668278 29-SEP-22          0
         8 NOT ACTIVE           77668659 29-SEP-22          0
         9 NOT ACTIVE                  0                    0
        10 NOT ACTIVE           77668278 29-SEP-22          0
        11 NOT ACTIVE           77668278 29-SEP-22          0

해당 파일 FILE# 테이블스페이스 확인

SQL> SELECT TABLESPACE_NAME,STATUS ,ONLINE_STATUS,FILE_ID FROM DBA_DATA_FILES;

TABLESPACE_NAME                STATUS    ONLINE_    FILE_ID
------------------------------ --------- ------- ----------
SYSTEM                         AVAILABLE SYSTEM           1
SYSAUX                         AVAILABLE ONLINE           3
ITSM_DATA                      AVAILABLE ONLINE           5
USERS                          AVAILABLE ONLINE           7
ITSM_IND                       AVAILABLE ONLINE           2
LOB                            AVAILABLE ONLINE           8
USERS_DATA                     AVAILABLE ONLINE           9
USERS_IND                      AVAILABLE ONLINE          10
UNDOTBS2                       AVAILABLE ONLINE          11

 

수동 백업 수행하여 에러 확인

SQL> ALTER TABLESPACE LOB BEGIN BACKUP;
Tablespace altered.

SQL> ALTER TABLESPACE LOB END BACKUP;
Tablespace altered.

SQL> ALTER TABLESPACE USERS_DATA BEGIN BACKUP;
ALTER TABLESPACE USERS_DATA BEGIN BACKUP

ERROR at line 1:
ORA-01642: begin backup not needed for read-only tablespace 'USERS_DATA'

 

테이블스페이스 상태를 조회하여 확인
USERS_DATA 테이블 스페이스의 READ ONLY 상태를 확인한다.

SELECT  T.TS#, T.NAME, RFILE# , ENABLED ,D.NAME
FROM v$TABLESPACE t, v$datafile d
WHERE t.ts# = d.ts#;
 
 
       TS# NAME            RFILE# ENABLED    NAME
---------- ---------------------- ---------- --------------------------------------------------
         1 SYSAUX               3 READ WRITE /app/oracle/oradata/18c/TSORA18/sysaux01.dbf
         0 SYSTEM               1 READ WRITE /app/oracle/oradata/18c/TSORA18/system01.dbf
         4 USERS                7 READ WRITE /app/oracle/oradata/18c/TSORA18/users01.dbf
         6 ITSM_DATA            5 READ WRITE /app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf
         7 ITSM_IND             2 READ WRITE /app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf
         8 LOB                  8 READ WRITE /app/oracle/oradata/18c/TSORA18/LOB01.dbf
         9 USERS_DATA           9 READ ONLY  /app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf
        10 USERS_IND           10 READ WRITE /app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf
         5 UNDOTBS2            11 READ WRITE /app/oracle/oradata/18c/TSORA18/undotbs02.dbf

 

USERS_DATA  테이블스페이스 READ WRITE 변경

SQL> ALTER TABLESPACE USERS_DATA READ WRITE;

Tablespace altered.

SQL>  SELECT  T.TS#, T.NAME, RFILE# , ENABLED ,D.NAME
FROM v$TABLESPACE t, v$datafile d
WHERE t.ts# = d.ts#;  2    3

       TS# NAME                        RFILE# ENABLED    NAME
---------- ----------------------- ---------- ---------- ------------------------------------------------
         1 SYSAUX                           3 READ WRITE /app/oracle/oradata/18c/TSORA18/sysaux01.dbf
         0 SYSTEM                           1 READ WRITE /app/oracle/oradata/18c/TSORA18/system01.dbf
         4 USERS                            7 READ WRITE /app/oracle/oradata/18c/TSORA18/users01.dbf
         6 ITSM_DATA                        5 READ WRITE /app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf
         7 ITSM_IND                         2 READ WRITE /app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf
         8 LOB                              8 READ WRITE /app/oracle/oradata/18c/TSORA18/LOB01.dbf
         9 USERS_DATA                       9 READ WRITE /app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf
        10 USERS_IND                        10 READ WRITE /app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf
         5 UNDOTBS2                        11 READ WRITE /app/oracle/oradata/18c/TSORA18/undotbs02.dbf

 

USERS_DATA 테이블스페이스  BEGIN BACKUP 수행

SQL> ALTER TABLESPACE USERS_DATA BEGIN BACKUP;
Tablespace altered.

SQL> !cp /app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf /backup/

SQL> ALTER TABLESPACE USERS_DATA END BACKUP;
Tablespace altered.




SQL> SELECT * FROM V$BACKUP;

     FILE# STATUS                CHANGE# TIME          CON_ID
---------- ------------------ ---------- --------- ----------
         1 NOT ACTIVE           77668278 29-SEP-22          0
         2 NOT ACTIVE           77668278 29-SEP-22          0
         3 NOT ACTIVE           77668278 29-SEP-22          0
         5 NOT ACTIVE           77668278 29-SEP-22          0
         7 NOT ACTIVE           77668278 29-SEP-22          0
         8 NOT ACTIVE           77668659 29-SEP-22          0
         9 NOT ACTIVE           77669169 29-SEP-22          0
        10 NOT ACTIVE           77668278 29-SEP-22          0
        11 NOT ACTIVE           77668278 29-SEP-22          0