Datafile 이동

데이터파일 이동 작업

1. 데이터 파일 경로 조회

현재 데이터파일은 /app/oracle/oradata/18c/TSORA18 경로에 모여있다.

SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/app/oracle/oradata/18c/TSORA18/system01.dbf       SYSTEM
/app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf     ONLINE
/app/oracle/oradata/18c/TSORA18/sysaux01.dbf       ONLINE
/app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf    ONLINE
/app/oracle/oradata/18c/TSORA18/users01.dbf        ONLINE
/app/oracle/oradata/18c/TSORA18/LOB01.dbf          ONLINE
/app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf   ONLINE
/app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf    ONLINE
/app/oracle/oradata/18c/TSORA18/undotbs02.dbf      ONLINE

9 rows selected.

 

데이터 파일이 있는 경로에 데이터파일 확인

[oracle@ts-oracle1 datafile]$ cd /app/oracle/oradata/18c/TSORA18/
[oracle@ts-oracle1 TSORA18]$ ls -alrt
total 50547212
drwxrwxr-x 4 oracle oinstall          32 Oct 26  2020 ..
-rw-r--r-- 1 oracle oinstall        6870 Sep 30 10:15 contrl.sql
drwxr-x--- 2 oracle oinstall        4096 Sep 30 10:44 .
-rw-r----- 1 oracle oinstall  1924145152 Nov 20 09:00 temp01.dbf
-rw-r----- 1 oracle oinstall   209715712 Jan  3 10:14 redo01.log
-rw-r----- 1 oracle oinstall   209715712 Jan  3 10:14 redo02.log
-rw-r----- 1 oracle oinstall  3160285184 Jan  3 10:15 ITSM_IND01.dbf
-rw-r----- 1 oracle oinstall 10737426432 Jan  3 10:15 ITSM_DATA01.dbf
-rw-r----- 1 oracle oinstall    58990592 Jan  3 10:15 users01.dbf
-rw-r----- 1 oracle oinstall  3221233664 Jan  3 10:15 LOB01.dbf
-rw-r----- 1 oracle oinstall   524296192 Jan  3 10:15 USERS_IND01.dbf
-rw-r----- 1 oracle oinstall  1216356352 Jan  3 14:35 system01.dbf
-rw-r----- 1 oracle oinstall  1073750016 Jan  3 14:35 USERS_DATA01.dbf
-rw-r----- 1 oracle oinstall 29087506432 Jan  3 14:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall   104865792 Jan  3 14:35 undotbs02.dbf
-rw-r----- 1 oracle oinstall   209715712 Jan  3 14:36 redo03.log
-rw-r----- 1 oracle oinstall    11124736 Jan  3 14:37 control01.ctl
-rw-r----- 1 oracle oinstall    11124736 Jan  3 14:37 control02.ctl

 

2. DB shutdown

세션을 하나 더 열어 alert log를 함께 확인한다.

[oracle@ts-oracle1 trace]$ cd $ORACLE_BASE/diag/rdbms/tsora18/TSORA18/trace
[oracle@ts-oracle1 trace]$ tail -f alert_TSORA18.log

 

DB shutdown

SQL> shutdown immediate;

3. 데이터파일 경로 변경
cp /원래경로 /이동할경로

cp /app/oracle/oradata/18c/TSORA18/system01.dbf /oradata/datafile/system01.dbf
cp /app/oracle/oradata/18c/TSORA18/sysaux01.dbf /oradata/datafile/sysaux01.dbf
cp /app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf /oradata/datafile/ITSM_DATA01.dbf
cp /app/oracle/oradata/18c/TSORA18/users01.dbf /oradata/datafile/users01.dbf
cp /app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf /oradata/datafile/ITSM_IND01.dbf
cp /app/oracle/oradata/18c/TSORA18/LOB01.dbf /oradata/datafile/LOB01.dbf
cp /app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf /oradata/datafile/USERS_DATA01.dbf
cp /app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf /oradata/datafile/USERS_IND01.dbf
cp /app/oracle/oradata/18c/TSORA18/undotbs02.dbf /oradata/datafile/undotbs02.dbf

 

복사된 파일 확인

[oracle@ts-oracle1 ~]$ cd /oradata/datafile/
[oracle@ts-oracle1 datafile]$ ls -alrt
total 48031972
drwxr-xr-x 5 oracle oinstall        4096 Jan  2 10:55 ..
-rw-r----- 1 oracle oinstall  1216356352 Jan  3 16:52 system01.dbf
-rw-r----- 1 oracle oinstall 29087506432 Jan  3 16:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 10737426432 Jan  3 16:54 ITSM_DATA01.dbf
-rw-r----- 1 oracle oinstall    58990592 Jan  3 16:54 users01.dbf
-rw-r----- 1 oracle oinstall  3160285184 Jan  3 16:54 ITSM_IND01.dbf
-rw-r----- 1 oracle oinstall  3221233664 Jan  3 16:54 LOB01.dbf
-rw-r----- 1 oracle oinstall  1073750016 Jan  3 16:54 USERS_DATA01.dbf
-rw-r----- 1 oracle oinstall   524296192 Jan  3 16:54 USERS_IND01.dbf
drwxr-xr-x 2 oracle oinstall        4096 Jan  3 16:54 .
-rw-r----- 1 oracle oinstall   104865792 Jan  3 16:54 undotbs02.dbf

 

4. DB mount 단계까지 open

SQL> startup nomount;
SQL> alter database mount;

 

5. 변경된 데이터파일 경로 DB에 등록
SQL> alter database rename file '/[원래경로파일]' to '/[이동할경로파일]';

SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/system01.dbf' to '/oradata/datafile/system01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/sysaux01.dbf' to '/oradata/datafile/sysaux01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf' to '/oradata/datafile/ITSM_DATA01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/users01.dbf' to '/oradata/datafile/users01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf' to '/oradata/datafile/ITSM_IND01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/LOB01.dbf' to '/oradata/datafile/LOB01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf' to '/oradata/datafile/USERS_DATA01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf' to '/oradata/datafile/USERS_IND01.dbf';
SQL> alter database rename file '/app/oracle/oradata/18c/TSORA18/undotbs02.dbf' to '/oradata/datafile/undotbs02.dbf';

 

6. DB open

SQL> alter database open;

 

7. 데이터 파일 확인

SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/oradata/datafile/system01.dbf                     SYSTEM
/oradata/datafile/ITSM_IND01.dbf                   ONLINE
/oradata/datafile/sysaux01.dbf                     ONLINE
/oradata/datafile/ITSM_DATA01.dbf                  ONLINE
/oradata/datafile/users01.dbf                      ONLINE
/oradata/datafile/LOB01.dbf                        ONLINE
/oradata/datafile/USERS_DATA01.dbf                 ONLINE
/oradata/datafile/USERS_IND01.dbf                  ONLINE
/oradata/datafile/undotbs02.dbf                    ONLINE

9 rows selected.

 

8. 기존 데이터파일 삭제
rm -rf [삭제할 파일]

[oracle@ts-oracle1 ~]$ cd /app/oracle/oradata/18c/TSORA18/
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/system01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/sysaux01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/ITSM_DATA01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/users01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/ITSM_IND01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/LOB01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/USERS_DATA01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/USERS_IND01.dbf
[oracle@ts-oracle1 TSORA18]$ rm -rf /app/oracle/oradata/18c/TSORA18/undotbs02.dbf

 

---

Job 삭제

+) 데이터 파일 이동 후 아래와 같은 에러 확인

현재 snapshot을 찍는 같은 job이 2개 돌고있는데 이게 같은 시간에 돌면서 PK(snap_id) 충돌이 나 에러가 떨어진 것으로 유추됨 

2023-01-03T17:00:08.132008+09:00
Errors in file /app/oracle/diag/rdbms/tsora18/TSORA18/trace/TSORA18_j001_9137.trc:
ORA-12012: error on auto execute of job 61
ORA-00001: unique constraint (PERFSTAT.STATS$DATABASE_INSTANCE_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 4936
ORA-06512: at "PERFSTAT.STATSPACK", line 105
ORA-06512: at line 1

 

job 확인 후 삭제할 job number 확인

SQL> select * from dba_jobs

 

job 삭제 방법 
EXEC DBMS_JOB.REMOVE( [job number] );

commit를 해줘야 job이 삭제된다.

SQL> EXEC DBMS_JOB.REMOVE(61);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

 

job을 다시 조회하면 해당 job이 삭제된 것을 확인할 수 있다.