虚拟机扩容+迁移oracle的数据文件实验

Oracle数据迁移与磁盘扩容
最新推荐文章于 2024-05-23 09:51:47 发布
原创最新推荐文章于 2024-05-23 09:51:47 发布 · 277 阅读
0 ·
CC 4.0 BY-SA版权
oracle 专栏收录该内容
36 篇文章
订阅专栏
本文详细介绍了在Oracle数据库环境中，如何通过增加虚拟机磁盘容量并进行分区、格式化，来解决磁盘空间不足的问题。同时，阐述了如何迁移Oracle数据文件至新磁盘空间，包括数据文件的复制、控制文件中数据文件路径的修改，以及数据库重启验证等关键步骤。
说明：在数据库服务器的磁盘空间不足的情况下，需要扩展磁盘空间，并将oracle的数据文件移动到新的空间中，从而达到腾出磁盘空间的目的
--0.在虚拟化管理界面上，为虚拟机添加一块新的磁盘

--1.没有添加磁盘前系统的分区情况
Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000ae4bd

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64         829     6144000   83  Linux
Partition 2 does not end on cylinder boundary.
/dev/sda3             829        1090     2097152   82  Linux swap / Solaris
/dev/sda4            1090        3917    22703104    5  Extended
/dev/sda5            1090        3917    22702080   83  Linux

--2.关机，添加一块磁盘后分区情况，多了/dev/sdb

[root@localhost ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000ae4bd

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64         829     6144000   83  Linux
Partition 2 does not end on cylinder boundary.
/dev/sda3             829        1090     2097152   82  Linux swap / Solaris
/dev/sda4            1090        3917    22703104    5  Extended
/dev/sda5            1090        3917    22702080   83  Linux

Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

--3.将磁盘设备进行分区

[root@localhost ~]# fdisk /dev/sdb 
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xb4eb40e4.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n --新建分区
Command action
   e   extended
   p   primary partition (1-4)
p --创建的分区类型为主分区
Partition number (1-4): 1  --指定分区号
First cylinder (1-783, default 1): --直接回车默认分配扇区从1开始
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-783, default 783): --直接回车表示分区该设备的全部扇区
Using default value 783

Command (m for help): p --查看分区结果

Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xb4eb40e4

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         783     6289416   83  Linux

Command (m for help): w --写入分区信息并保存
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

--4.再次查看分区结果

[root@localhost ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000ae4bd

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64         829     6144000   83  Linux
Partition 2 does not end on cylinder boundary.
/dev/sda3             829        1090     2097152   82  Linux swap / Solaris
/dev/sda4            1090        3917    22703104    5  Extended
/dev/sda5            1090        3917    22702080   83  Linux

Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xb4eb40e4

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         783     6289416   83  Linux --分区后的结果，多了这一条


---5.向分区中写入文件系统格式
[root@localhost ~]# mkfs.ext4 /dev/sdb1
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
393216 inodes, 1572354 blocks
78617 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1610612736
48 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.


--自动挂载配置
[root@localhost ~]# blkid --查看系统中快设备信息
/dev/sda1: UUID="4ebdcb4e-f0e0-4e6a-bab5-695a5f5eab46" TYPE="ext4" 
/dev/sda2: UUID="421acd9e-02e1-461e-bd2a-3c51b25ce9c6" TYPE="ext4" 
/dev/sda3: UUID="fb7a2c9c-d29d-4257-8ec6-238ad8b11f80" TYPE="swap" 
/dev/sda5: UUID="eec74471-f1d8-4cdf-83ca-351460fe2e17" TYPE="ext4" 
/dev/sdb1: UUID="6d7aff89-713a-4b60-8f46-26b4d15500b2" TYPE="ext4" 

--将设备永久挂载
[root@localhost ~]# vim /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Fri Oct 27 23:03:47 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=eec74471-f1d8-4cdf-83ca-351460fe2e17 /                       ext4    defaults        1 1
UUID=4ebdcb4e-f0e0-4e6a-bab5-695a5f5eab46 /boot                   ext4    defaults        1 2
UUID=421acd9e-02e1-461e-bd2a-3c51b25ce9c6 /home                   ext4    defaults        1 2
UUID=fb7a2c9c-d29d-4257-8ec6-238ad8b11f80 swap                    swap    defaults,size=11G        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=11G        0 0
UUID=6d7aff89-713a-4b60-8f46-26b4d15500b2 /oradata                       ext4    defaults        1 1 --复制第一行修改UUID
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0


********************************************磁盘扩容完毕，接下来就是迁移数据文件*************************************

--表空间脱机

--物理cp数据文件

--rname数据文件名称（实际是修改控制文件中数据文件路径）

--表空间联机

--关闭数据库删除、或移动旧的数据文件到指定位置
将/oradata/rzxerp的目录所有则修改一下
[root@localhost ~]#  chown orale:oinstall /oradata -R

--启动实例
本案例中移动的是系统表空间，系统表空间是无法脱机的，因此需要在mount状态下工作
--1.关闭数据库，并启动到mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2228784 bytes
Variable Size		 1090522576 bytes
Database Buffers	  503316480 bytes
Redo Buffers		    7344128 bytes
Database mounted.
--2.复制数据文件到新的分区
[oracle@localhost rzxerp]$ cp/u01/app/oracle/oradata/rzxerp/system01.dbf ./
[oracle@localhost rzxerp]$ cp /u01/app/oracle/oradata/rzxerp/sysaux01.dbf ./
[oracle@localhost rzxerp]$ cp /u01/app/oracle/oradata/rzxerp/undotbs01.dbf ./
[oracle@localhost rzxerp]$ cp /u01/app/oracle/oradata/rzxerp/users01.dbf ./

--3.修改控制文件中数据文件的路径名称
SQL> alter database rename file '/u01/app/oracle/oradata/rzxerp/system01.dbf' to '/oradata/rzxerp/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/rzxerp/sysaux01.dbf' to '/oradata/rzxerp/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/rzxerp/undotbs01.dbf' to '/oradata/rzxerp/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/rzxerp/users01.dbf' to '/oradata/rzxerp/users01.dbf';

Database altered.

--4.关闭数据库，删除或移动旧的数据文件，我这里做实验就直接删除，生产根据时间情况进行备份
[oracle@localhost rzxerp]$ rm /u01/app/oracle/oradata/rzxerp/sysaux01.dbf 
[oracle@localhost rzxerp]$ rm /u01/app/oracle/oradata/rzxerp/undotbs01.dbf 
[oracle@localhost rzxerp]$ rm /u01/app/oracle/oradata/rzxerp/users01.dbf 
--5.启动实例，查询迁移结果
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2228784 bytes
Variable Size		 1090522576 bytes
Database Buffers	  503316480 bytes
Redo Buffers		    7344128 bytes
Database mounted.
Database opened.
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oradata/rzxerp/system01.dbf
/oradata/rzxerp/sysaux01.dbf
/oradata/rzxerp/undotbs01.dbf
/oradata/rzxerp/users01.dbf

SQL> 

--6.查看根分区空间已经腾出了
[root@localhost /]# df -lh
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        22G   17G  3.4G  84% /
tmpfs            11G  1.8G  9.3G  16% /dev/shm
/dev/sda1       477M   35M  418M   8% /boot
/dev/sda2       5.7G   35M  5.4G   1% /home
/dev/sdb1       5.8G  2.2G  3.4G  40% /oradata