Add bare devices (logical volumes) for Oracle tablespace In the AIX Environment)

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

How to expand the tablespace of a database using bare devices in AIX

AIXCLUB's classic article shows that this AIX bare device is almost the same! The bare Device of AIX is a bit different from that of LINUX. After the LV is created, the file with R added before the LV name is generated under/dev/, which is the bare device file of the LV.

Hardware environment: minicomputer IBM P670, storage: ibm shark F-20

Software environment: Operating System AIX5.1 database Oracle9i

Theme: physical volume PV-> volume group VG-> logical Volume LV (type: raw)-> Add tablespace

Procedure:

I. First

# Lsvg-o // view all available volume groups

Datavg03

Datavg02

Datavg01

Datavg00

Rootvg

2. perform the following operations on the volume group used for database preparation:

# Lsvg-l datavg03

Datavg09:

Lv name type LPs PPs PVs LV STATE MOUNT POINT

Lvdata0316 raw 64 64 1 open/syncd N/

Lvdata0317 raw 64 64 1 open/syncd N/

Lvdata0318 raw 64 64 1 closed/syncd N/

Lvdata0319 raw 64 64 1 closed/syncd N/

Lvdata0320 raw 64 64 1 closed/syncd N/

Lvdata0321 raw 64 64 1 closed/syncd N/

Lvdata0322 raw 64 64 1 closed/syncd N/

Lvdata0323 raw 64 64 1 closed/syncd N/

Lvdata0324 raw 64 64 1 closed/syncd N/

Lvdata0325 raw 64 64 1 closed/syncd N/

Lvdata0326 raw 64 64 1 closed/syncd N/

Lvdata0327 raw 64 64 1 closed/syncd N/

Lvdata0328 raw 64 64 1 closed/syncd N/

Lvdata0329 raw 64 64 1 closed/syncd N/

Lvdata0330 raw 64 64 1 closed/syncd N/

As shown in the following figure, 13 logical volumes (raw devices) in the volume group are not used. (if no logical volumes in the closed/syncd status are displayed, go to step 1) but how can we know how large these 13 logical volumes are? You can use the following command:

# Lslv lvdata0315

Logical volume: lvdata0309 volume group: datavg09

Lv identifier: 0037de1d4154c0000000105cd3b6816. 11 PERMISSION: read/write

Vg state: active/complete lv state: opened/syncd

TYPE: raw write verify: off

MAX LPs: 512 pp size: 64 megabyte (s)

COPIES: 1 sched policy: parallel

LPs: 64 PPs: 64

STALE PPs: 0 bb policy: relocatable

INTER-POLICY: minimum RELOCATABLE: yes

INTRA-POLICY: middle upper bound: 32.

Mount point: N/a label: None

Mirror write consistency: on/ACTIVE

Each lp copy on a separate pv? : Yes

We can see that the physical partition size is 64 M. Because it is in the same volume group, we can know that the size of all physical partitions is 64 M. From the display result of lsvg-l datavg09, we can see that PPs: therefore, the size of each logical volume is: LPs × PPSIZE = 64*64 M = 4096 M = 4G. We can see that there are 13 4G logical volumes that are not used.

However, it is not completely guaranteed that these 13 logical volumes can be used to expand the tablespace of the database, because we do not know the owner of these bare devices, so the following steps are required:

# Cd/dev

# Ls-l rlvdata03 * // display file attributes starting with rlvdata03

Crw-rw ---- 1 oracle dba 58, 20 Aug 22 :33 rlvdata0318

Crw-rw ---- 1 oracle dba 58, 21 Aug 22 :35 rlvdata0319

Crw-rw ---- 1 oracle dba 58, 22 Aug 22 :37 rlvdata0320

Crw-rw ---- 1 oracle dba 58, 23 Aug 22 :35 rlvdata0321

Crw-rw ---- 1 oracle dba 58, 24 Aug 22 :37 rlvdata0322

Crw-rw ---- 1 oracle dba 58, 25 Aug 22 :39 rlvdata0323

Crw-rw ---- 1 oracle dba 58, 26 Aug 22 :39 rlvdata0324

Crw-rw ---- 1 oracle dba 58, 27 Aug 19 16:14 rlvdata0325

The query results show that the owner of the bare device is oracle, and oracle can add these bare devices. However, if the owner of the bare device is not oracle but other users, you need

# Chown oracle: dba rlvdata03 * // be careful when modifying the data according to actual conditions

3. You can log on to the database by adding a tablespace. you can log on to the database by using a user with the permission to create or modify the tablespace (there are multiple methods). Take sqlplus as an example:

# Su-oracle

$ Sqlplus/nolog

SQL * Plus: Release 9.2.0.5.0-Production on Mon Aug 22 12:49:55 2005

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

SQL> conn/as sysdba

Connected.

SQL> alter tablespace ts_index add datafile

2 '/dev/rlvdata0318' size 4090; // The size is 4090 instead of 4096. If 4096 is all used, it is prone to errors.

SQL> Tablespace altered

You can perform the preceding operations to add other bare devices to expand the tablespace.

However, if the above 13 logical volumes cannot meet the expansion requirements, continue with the following steps:

Iv. # lsvg datavg09 // view the volume group information and usage to check whether there is sufficient space.

Volume group: datavg09 vg identifier: 0037de1d00004c000000010

5cd3b6816

Vg state: active pp size: 64 megabyte (s)

Vg permission: read/write TOTAL PPs: 2605 (166720 megabytes)

MAX LVs: 256 FREE PPs: 557 (35648 megabytes)

LVs: 32 USED PPs: 2048 (131072 megabytes)

OPEN LVs: 16 QUORUM: 3

TOTAL PVs: 5 vg descriptors: 5

STALE PVs: 0 STALE PPs: 0

ACTIVE PVs: 5 auto on: no

MAX PPs per PV: 1016 MAX PVs: 32

LTG size: 128 kilobyte (s) auto sync: no

Hot spare: no

The displayed information shows that the volume group currently has a space of 35648M. You can perform the following operations:

# Mklv-y 'lvdata0331 '-t 'raw' datavg09 64

Note: On the volume group datavg09, create the logical volume lvdata0331. The logical volume type is raw, and the logical volume lps is 64.

If the logical volume is too large, the database performance will be affected. Therefore, it is not recommended to create a large bare device.

Then proceed to the '3' section above

However, if all the current volume groups are used up and there is no volume group that can be used to add bare devices, you must create a volume group.

5. Before creating a volume group, let's first check whether there are available physical volumes.

# Lspv

Vpath53 000b273dbe31ff50 datavg03

Vpath54 000b273dbe320138 datavg03

Vpath55 000b273dbe320303 datavg03

Vpath56 000b273dbe320795 None

Vpath57 000b273dbe320a46 None

Vpath58 000b273dbe320c29 None

We can see that vpath56, vpath57, and vpath58 are not used by the backup volume group. Because the storage in the current environment is provided as a disk array, the vpath specifies the size on the array. The so-called "physical volume" recognized by the Minicomputer System (in fact, the real physical volume, it should be

Hdisk ).

# Mkvg-f-y 'datavg04 '-S' 64'-n' vpath56 vpath57 vpath58

Note: vpath56 vpath57 vpath58 is used to create the volume group datavg04 and the physical partition size is 64 MB.

After the volume group is created, repeat the previous steps to complete the process.

Refer:

A bare device, also called a raw partition, is a special character device that is not formatted and not read by Unix through a file system. This article collects 20 questions and answers from bare devices and Oracle.

1. What is a bare device?

A bare device, also called a raw partition, is a special character device that is not formatted and not read by Unix through a file system. It is read and written by the application. It is not buffered by the file system.

2. How to identify bare devices?

In the Unix/dev directory, there are many files, including two categories: character device files and block device files. Special files of character devices perform I/O operations without passing through the buffer zone of the operating system, while special files of Block devices are used to transmit packets of fixed length with peripherals. Only one character is transmitted at a time when special character files and peripherals perform I/o operations. For special files on a block device, the cache mechanism is used to transmit a whole piece of data at a time between the peripherals and the memory. Bare devices use special character files. In the/dev directory, you can see many such files.

3. Benefits of using bare Devices

Because the bare device does not pass through the Unix operating system, data is transmitted directly from Disk to Oracle. Therefore, the bare device is used for database applications with frequent reads and writes, it can greatly improve the performance of the database system. Of course, this is true when the disk I/O is very large and the disk I/O is already known as a system bottleneck. If the disk read/write operation is so frequent that the disk read/write operation becomes the bottleneck of the system, the use of bare devices can greatly improve the performance, up to or even up to 40%, which is very obvious. In addition, because the original partition is used and the file system management method is not used, the overhead for Unix file system maintenance is also gone, for example, I-node maintenance is not required, idle blocks, which can also improve the performance.

4. How can I determine whether to use a bare device?

To determine whether a bare device is used, consider the following: first, the database system itself needs to be well optimized. Optimization is a topic of some technologies, which is hard to be described simply. Second, use Unix commands to identify whether there is a disk read/write bottleneck. For example, commands such as Unix vmstat and sar can be well identified. If you decide to use bare devices, you need to have free partitions on the disk. Otherwise, you need to add a new disk or re-plan the original system.

5. What systems must use bare devices?

If you use the Oracle Parallel Server option, you must use bare devices to store all data files, control files, and redo log files. Only by placing these files on bare devices can all Oracle instances read the files in the database. This is determined by the characteristics of Unix operating systems. Another case is that if you want to use asynchronous I/O, you must also use bare devices on Some Unix systems. For more information about Unix, see.

6. Can I use the first partition of a disk as a bare device?

Yes, but not recommended. The old version of Unix is a bank. The first partition of a disk often contains some information about the disk and some control information about the logical volume. If these parts are overwritten by bare devices, the disk will become unrecognizable and cause system crash. This is not the case for newer Unix versions because they use more complex technologies to manage disk and logical volume information. However, unless you are sure not to use the first partition of the disk as a bare device.

7. Can I use the entire bare device as an Oracle data file?

No. The data file size must be slightly smaller than the actual size of the bare device. At least two oracle blocks must be empty.

8. Which user should the bare device belong?

The root user should create bare devices and distribute them to Oracle users for use. At the same time, it should be included in the group where the Oracle user is located (generally DBA ).

9. How do I specify a bare device when creating a data file?

There is no big difference with a common file. In the same way, you can write the detailed path of the bare device in single quotes. For example, to create a tablespace, use two bare devices, each of which is 30 mb and the Oracle block is 4 kb. Run the following command: create tablespace RAW_TS DATAFILE '/dev/raw1 'size 30712 k datafile'/dev/raw2' size 30712 k;

10. What is the relationship between the size of Oracle blocks and bare devices?

Oracle must be a multiple of the physical block size on the bare device.

11. How to back up data on bare devices?

On bare devices, you cannot use Unix utilities for backup. The only way is to use the most basic Unix Command: DD for backup. For example, dd if =/dev/raw1of =/dev/rmt0bs = 16 k. For detailed dd syntax, refer to the unix manual or the online help. You can also back up the data files on the bare device to the disk with dd, and then use the Unix utility for further processing. Row server option,

12. Can I use a file system for some data files in the database and a bare device for the other part?

Yes. However, this will make the backup process more complex.

13. Should I put online redo log files on bare devices?

This is an excellent choice. Online redo log files are frequently written and suitable for storing on bare devices. If you use the parallel server option, the online redo log file must be placed on the bare device.

14. Can I put the archive log file on a bare device?

No. Archive log files must be stored on the conventional Unix file system or directly on the tape.

15. Can I place multiple data files on a bare device?

No. Therefore, you must be very careful when setting bare devices. If it is too small, the space will soon be used up. If it is too large, the space will be wasted.

16. Should I put several bare devices on the same physical disk?

This is not good. Because the bare device is used to increase the disk read/write speed. However, placing multiple bare devices on the same physical disk will lead to read/write competition, which is not conducive to improving the I/O speed. Try to distribute bare devices to different physical disks, preferably to different disk controllers. This is the best choice.

17. Do I need to define all bare devices as the same size?

This is not mandatory, but dividing it into the same size is advantageous for database management.

18. Do I need to change the Unix core parameters to use bare devices on Unix? No. However, you can reduce the buffer size. If no other application runs on the same Unix machine. Since bare devices are used, Unix system buffers are no longer used.

19. Is there any way to improve the read/write speed at the operating system level?

RAID (cheap redundant disk array) is also a very effective method, especially for systems with very frequent reads and writes.

20. After considering all the above aspects, is there any way to improve the performance?

This requires optimizing Oracle and purchasing more disks and disk controllers to distribute I/O