Shulou(Shulou.com)06/01 Report--

This article mainly shows you how Oracle makes dbf file move, the content is simple and easy to understand, I hope you can learn, after learning, there will be harvest, let Xiaobian take you to see it.

background

Oracle space is insufficient, found dbf file is not stored according to the design path, linux disk mount space is not used, need to move the dbf file in the wrong location.

check the file system

/home/oracle \>df -h file system capacity used up % mount point/dev/mapper/lv01-root 22G 4.5G 16G 23% /devtmpfs 3.8G 0 3.8G 0% /devtmpfs 3.9G 96K 3.9G 1% /dev/shmtmpfs 3.9G 153M 3.7G 4% /runtmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup/dev/sda1 190M 132M 44M 75% /boot/dev/mapper/lv02-home 20G 19G 0 100% /home/dev/mapper/oradatalv-oradata 30G 18G 9.8G 65% /oradatatmpfs 781M 16K 781M 1% /run/user/42tmpfs 781M 0 781M 0% /run/user/1001tmpfs 781M 0 781M 0% /run/user/0

The space under the home mount point has reached 100%.

Check for large files

/home/oracle/oradata/ora12c \>ls -lh Total usage 8.7G\-rw-r----. 1 oracle oinstall 6.7G May 20 13:54 upbs_data.dbf\-rw-r----. 1 oracle oinstall 2.0G May 20 05:06 upbs_index.dbf

After discovering that the dbf file is too large, prepare to move it. There is still room left under the oradata mount point where database files should be stored for dbf files.

/home/oracle/oradata/ora12c >sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production onWed May 20 14:18:36 2020Copyright (c) 1982, 2014, Oracle. All rights reserved.ERROR:ORA-09817: Write to audit file failed.Linux-x86_64 Error: 28: No space left on deviceAdditional information: 12ORA-09945: Unable to initialize the audit trail fileLinux-x86_64 Error: 28: No space left on device

Oracle sqlplus direct login failed, insufficient display space.

Delete Audit File Log

/home/oracle/audit \>rm \*/home/oracle/audit \>ll Total Usage 0

After deletion, check the disk space and find that there are a few audit files just deleted.

/home/oracle/audit \>df -h file system capacity used up % mount point/dev/mapper/lv01-root 22G 4.5G 16G 23% /devtmpfs 3.8G 0 3.8G 0% /devtmpfs 3.9G 96K 3.9G 1% /dev/shmtmpfs 3.9G 153M 3.7G 4% /runtmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup/dev/sda1 190M 132M 44M 75% /boot/dev/mapper/lv02-home 20G 19G 60K 100% /home/dev/mapper/oradatalv-oradata 30G 18G 9.8G 65% /oradatatmpfs 781M 16K 781M 1% /run/user/42tmpfs 781M 0 781M 0% /run/user/1001tmpfs 781M 0 781M 0% /run/user/0

After deletion, there is 60k space left. At first, there is no problem with the aud file path, which is not the normal aud path.

Find again Clean up audit logs Free up space

/home/oracle/app/oracle/admin/orcl \>find ./ -name \*aud

Clear the first 30 days after the query results

/home/oracle/app/oracle/admin/orcl \>find ./ -name \*aud -mtime +30 \|xargs rm-f

close the database

/home/oracle/audit \>sqlplus / as sysdbaSQL\*Plus: Release 12.1.0.2.0 Production on Wed May 20 14:14:30 2020Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application TestingoptionsSQL\> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

Copy dbf file

/home/oracle/oradata/ora12c \>cp upbs_data.dbf/oradata/orcl/appdata/upbs_data.dbf

Log in to the database modify dbf file path

home/oracle/app/oracle/admin/orcl \>sqlplus / as sysdbaSQL\*Plus: Release 12.1.0.2.0 Production on Wed May 20 14:33:30 2020Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to an idle instance.SQL\> startup mount;ORACLE instance started.Total System Global Area 2466250752 bytesFixed Size 2927384 bytesVariable Size 671089896 bytesDatabase Buffers 1778384896 bytesRedo Buffers 13848576 bytesDatabase mounted.SQL\> alter database rename file '/home/oracle/oradata/ora12c/upbs_data.dbf' to'/oradata/orcl/appdata/upbs_data.dbf';Database altered.

Restart database services

SQL\> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL\> startupORACLE instance started.Total System Global Area 2466250752 bytesFixed Size 2927384 bytesVariable Size 671089896 bytesDatabase Buffers 1778384896 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened.SQL\>

plsql reconnects database successfully, deletes original dbf file to free space

That's how Oracle makes dbf files move. If you have any knowledge or skills, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.