Use bare devices to expand database tablespaces

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 × pp size = 64*64 m = 4096 M = 4G. We can see that there are 13 4G logical volumes, 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: dab 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.

Note: ① here, the author of the original article uses "error-prone ". In fact, it is not prone to errors, but not allowed to use all of them, because two Oracle blocks should be reserved for the logic volume header for control and use. (this should be an OS block and an oracle block, generally, the Oracle block size is an integer multiple of the OS block size, so the size of two Oracle blocks is retained ).