[실습1] ASM 쪽에 있는 SYSTEM 관련 데이터 파일을 /home/oracle에 옮기기
💡 system 관련 데이터 파일
1. system01.dbf
2. sysaux01.dbf
3. undotbs01.dbf
--> system 관련 파일들은 db 내리고 옮겨줘야 됨
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users01.dbf
UNDOTBS1 +DATA/orcl/datafile/undotbs1.258.796857625
SYSAUX +DATA/orcl/datafile/sysaux.257.796857623
SYSTEM +DATA/orcl/datafile/system.256.796857621
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
6 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL>
SQL>
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[orcl:~]$
[orcl:~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[+ASM:~]$ asmcmd
ASMCMD>
ASMCMD> cp +DATA/orcl/datafile/system.256.796857621 /home/oracle/system01.dbf
copying +DATA/orcl/datafile/system.256.796857621 -> /home/oracle/system01.dbf
ASMCMD>
ASMCMD> exit
[+ASM:~]$
[+ASM:~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[orcl:~]$ sys
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 17 15:59:35 2025
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 306186232 bytes
Database Buffers 167772160 bytes
Redo Buffers 5963776 bytes
Database mounted.
SQL> SQL> SQL>
SQL>
SQL> alter database rename file '+DATA/orcl/datafile/system.256.796857621'
2 to '/home/oracle/system01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users01.dbf
UNDOTBS1 +DATA/orcl/datafile/undotbs1.258.796857625
SYSAUX +DATA/orcl/datafile/sysaux.257.796857623
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
6 rows selected.
SQL>문제1. undotbs 테이블 스페이스의 데이터 파일을 /home/oracle 밑으로 옮기시오
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users01.dbf
UNDOTBS1 +DATA/orcl/datafile/undotbs1.258.796857625
SYSAUX +DATA/orcl/datafile/sysaux.257.796857623
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
6 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[orcl:~]$
[orcl:~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[+ASM:~]$ asmcmd
ASMCMD>
ASMCMD> cp +DATA/orcl/datafile/undotbs1.258.796857625 /home/oracle/undotbs01.dbf
copying +DATA/orcl/datafile/undotbs1.258.796857625 -> /home/oracle/undotbs01.dbf
ASMCMD> exit
[+ASM:~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[orcl:~]$ sys
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 17 16:07:09 2025
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
SQL> alter database rename file '+DATA/orcl/datafile/undotbs1.258.796857625' to '/home/oracle/undotbs01.dbf';
SQL> alter database open;
Database altered.
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users01.dbf
UNDOTBS1 /home/oracle/undotbs01.dbf
SYSAUX +DATA/orcl/datafile/sysaux.257.796857623
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
6 rows selected.
SQL>[실습2] control file 을 다른 곳으로 이동 시키기
alter system set control_files='/home/oracle/control01.ctl', '/home/oracle/control02.ctl' scope=spfile;
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.260.796857737, +FRA/orcl/controlfile/current.256.796857739
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @i
INSTANCE_NAME
----------------
orcl
SQL>
SQL> alter system set control_files='/home/oracle/control01.ctl','/home/oracle/control02.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[orcl:~]$
[orcl:~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[+ASM:~]$ asmcmd
ASMCMD>
ASMCMD> cp +DATA/orcl/controlfile/current.260.796857737 /home/oracle/control01.ctl
copying +DATA/orcl/controlfile/current.260.796857737 -> /home/oracle/control01.ctl
ASMCMD>
ASMCMD> cp +FRA/orcl/controlfile/current.256.796857739 /home/oracle/control02.ctl
copying +FRA/orcl/controlfile/current.256.796857739 -> /home/oracle/control02.ctl
ASMCMD>
ASMCMD> exit
[+ASM:~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[orcl:~]$ sys
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 17 16:21:54 2025
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 306186232 bytes
Database Buffers 167772160 bytes
Redo Buffers 5963776 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/oracle/control01.ctl, /home/oracle/control02.ctl🔸 문제2. data file 중 asm 에 남아있던 파일들 전부 /home/oracle 밑으로 이동시키기
1. system에 있는 SYSAUX 먼저 옮김
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users.dbf
UNDOTBS1 /home/oracle/undotbs01.dbf
SYSAUX +DATA/orcl/datafile/sysaux.257.796857623
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
TS02 +DATA/orcl/datafile/ts02.268.1212062921
7 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ASMCMD> cp +DATA/orcl/datafile/sysaux.257.796857623 /home/oracle/sysaux01.dbf
copying +DATA/orcl/datafile/sysaux.257.796857623 -> /home/oracle/sysaux01.dbf
SQL> startup mount
ORACLE instance started.
Total System Global Area 481259520 bytes
Fixed Size 1337352 bytes
Variable Size 306186232 bytes
Database Buffers 167772160 bytes
Redo Buffers 5963776 bytes
Database mounted.
SQL> alter database rename file '+DATA/orcl/datafile/sysaux.257.796857623'
2 to '/home/oracle/sysaux01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users.dbf
UNDOTBS1 /home/oracle/undotbs01.dbf
SYSAUX /home/oracle/sysaux01.dbf
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
TS02 +DATA/orcl/datafile/ts02.268.1212062921
7 rows selected.2. 나머지 파일들(EXAMPLE, TS02) 옮기기
SQL> select tablespace_name, status
2 from dba_tablespaces;
TABLESPACE STATUS
---------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS01 ONLINE
TS02 ONLINE
8 rows selected.
SQL> alter tablespace ts02 offline;
Tablespace altered.
SQL> alter tablespace EXAMPLE offline;
Tablespace altered.
SQL> select tablespace_name, status
2 from dba_tablespaces;
TABLESPACE STATUS
---------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE OFFLINE
TS01 ONLINE
TS02 OFFLINE
8 rows selected.
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users.dbf
UNDOTBS1 /home/oracle/undotbs01.dbf
SYSAUX /home/oracle/sysaux01.dbf
SYSTEM /home/oracle/system01.dbf
EXAMPLE +DATA/orcl/datafile/example.265.796857803
TS01 /home/oracle/ts01.dbf
TS02 +DATA/orcl/datafile/ts02.268.1212062921
7 rows selected.
ASMCMD> cp +DATA/orcl/datafile/ts02.268.1212062921 /home/oracle/ts02.dbf
copying +DATA/orcl/datafile/ts02.268.1212062921 -> /home/oracle/ts02.dbf
ASMCMD> cp +DATA/orcl/datafile/example.265.796857803 /home/oracle/example01.dbf
copying +DATA/orcl/datafile/example.265.796857803 -> /home/oracle/example01.dbf
SQL> alter database rename file '+DATA/orcl/datafile/ts02.268.1212062921'
2 to '/home/oracle/ts02.dbf';
Database altered.
SQL> alter database rename file '+DATA/orcl/datafile/example.265.796857803'
2 to '/home/oracle/example01.dbf';
Database altered.
SQL> alter tablespace ts02 online;
Tablespace altered.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select tablespace_name, status
2 from dba_tablespaces;
TABLESPACE STATUS
---------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS01 ONLINE
TS02 ONLINE
8 rows selected.
SQL> @tablespace
TABLESPACE FILE_NAME
---------- -------------------------------------------------------
USERS /home/oracle/users.dbf
UNDOTBS1 /home/oracle/undotbs01.dbf
SYSAUX /home/oracle/sysaux01.dbf
SYSTEM /home/oracle/system01.dbf
EXAMPLE /home/oracle/example01.dbf
TS01 /home/oracle/ts01.dbf
TS02 /home/oracle/ts02.dbf
7 rows selected.
SQL>