Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more details here ).
I am looking at moving the following to the HDD mount point:
Should datafiles like temp or undo also be moved if I want to minimize the growth of the VM on the SSD ?
I will move all pdb data files,undo and create temp file into new directory.You can skip redo log files as they are fixed ,in Oracle Developer Day VM total size of Redo group files is 600M.
To move data files online execute sql command
alter database move datafile 'source' to 'target' reuse;
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 1 11:20:30 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2760656746)
RMAN> report schema
2> ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLCDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3 530 SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6 360 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9 380 ORCL:SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
10 1210 ORCL:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
12 72 ORCL:USERS NO /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
13 2 ORCL:APEX_1581590175460392 NO /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf
14 50 UNDOTBS2 YES /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15 50 ORCL:UNDOTBS2 YES /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 33 TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2 62 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
3 62 ORCL:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf
RMAN> exit
Recovery Manager complete.
[oracle@localhost ~]$ mkdir -p /u02/oradata/pdb/orcl -- creating new directories for pdb files
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba --login into pdb database as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 1 11:26:29 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf' to '/u02/oradata/pdb/orcl/system01.dbf' reuse;
Database altered.
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf' to '/u02/oradata/pdb/orcl/sysaux01.dbf' reuse;
Database altered.
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf' to '/u02/oradata/pdb/orcl/users01.dbf' reuse;
Database altered.
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf' to '/u02/oradata/pdb/orcl/APEX_1581590175460392.dbf' reuse;
Database altered.
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf' TO '/u02/oradata/pdb/orcl/undotbs2.dbf' reuse;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/pdb/orcl/temp01.dbf' SIZE 65011712 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf' drop including datafiles; -- drop tempfile on old directory
Database altered.
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLCDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3 530 SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6 360 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9 380 ORCL:SYSTEM YES /u02/oradata/pdb/orcl/system01.dbf
10 1210 ORCL:SYSAUX NO /u02/oradata/pdb/orcl/sysaux01.dbf
12 72 ORCL:USERS NO /u02/oradata/pdb/orcl/users01.dbf
13 2 ORCL:APEX_1581590175460392 NO /u02/oradata/pdb/orcl/APEX_1581590175460392.dbf
14 50 UNDOTBS2 YES /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15 50 ORCL:UNDOTBS2 YES /u02/oradata/pdb/orcl/undotbs2.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 33 TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2 62 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
4 62 ORCL:TEMP 32767 /u02/oradata/pdb/orcl/temp01.dbf
If you don't want files to grow on an SSD, then the easiest solution is to not put the database on SSD.
You should expect everything under $ORACLE_BASE to grow in size. (This is 99%+ of the database)
But, you'll definitely want the FRA on the cheaper drive. In Production, this would be in a separate disk subsystem from data files and binaries.
Temp and Undo should show higher DB performance on SSD. Read Only tablespace also don't grow in size.
From the database side, you can set a max file size for your data files. You can set quotes for the users. Also, you can adjust the max size of the FRA.
For ease of implementation, I'd move the entire DB to the HDD then move tablespaces to SSD as you find the need/desire to move them.
