1

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:

  1. USERS tablespace
  2. Redo log files

Should datafiles like temp or undo also be moved if I want to minimize the growth of the VM on the SSD ?

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.