Veritas disk group configuration records
Veritas uses disk group configuration records to store subdisk, plex, volume, and device configuration data. The configuration records get written to the private region of specific devices in each disk group, and are described in the vxinfo(1m) manual page:
A disk group configuration is a small database
that contains all volume, plex, subdisk, and disk
media records. These configurations are repli-
cated onto some or all disks in the disk group,
usually with one copy on each disk. Because these
databases are stored within disk groups, record
associations cannot span disk groups. Thus, a
subdisk defined on a disk in one disk group cannot
be associated with a volume in another disk group.
If multiple devices are present in a disk group, Veritas will replicate the configuration records to multiple devices for redundancy. You can see which devices contain disk configuration records by invoking vxdg(1m) with the “list” option:
$ vxdg list oof
Group: oof
dgid: 1120604922.22.tigger
import-id: 1024.10
flags: cds
version: 120
alignment: 8192 (bytes)
ssb: on
detach-policy: global
dg-fail-policy: dgdisable
copies: nconfig=2 nlog=default
config: seqno=0.17098 permlen=1280 free=1223 templen=27 loglen=192
config disk c1t1d0s2 copy 1 len=1280 state=clean online
config disk c1t2d0s2 copy 1 len=1280 state=clean online
config disk c1t3d0s2 copy 1 len=1280 disabled
config disk c1t4d0s2 copy 1 len=1280 disabled
config disk c1t5d0s2 copy 1 len=1280 disabled
config disk c1t6d0s2 copy 1 len=1280 disabled
log disk c1t1d0s2 copy 1 len=192
log disk c1t2d0s2 copy 1 len=192
log disk c1t3d0s2 copy 1 len=192
log disk c1t4d0s2 copy 1 len=192
log disk c1t5d0s2 copy 1 len=192
This example shows that targets 1 and 2 contain configuration records. If you are a paranoid person, you will probably want to replicate the configuration records to several devices in the disk group. This can be accomplished with the vxedit(1m) utility:
$ vxedit -g oof set nconfig=6 oof
$ vxdg list oof | grep ^config
config: seqno=0.17137 permlen=1280 free=1265 templen=27 loglen=192
config disk c1t1d0s2 copy 1 len=1280 state=clean online
config disk c1t2d0s2 copy 1 len=1280 state=clean online
config disk c1t3d0s2 copy 1 len=1280 state=clean online
config disk c1t4d0s2 copy 1 len=1280 state=clean online
config disk c1t5d0s2 copy 1 len=1280 state=clean online
config disk c1t6d0s2 copy 1 len=1280 state=clean online
Since the configuration records need to be updated periodically, it is poor practice to replicate configuration records to all devices in large disk groups. Veritas will use the correct number of configuration records by default, so creating additional configuration records is seldom required. For further details on configuration records, take a look at the Veritas Volume Manager administrators guide and vxintro(1m) manual page.
вторник, 24 марта 2009 г.
VxVM Default Disk Groups
Default Disk Groups
Veritas Volume Manager comes with a wide variety of command line utilities, which can be used to create, delete and maintain Veritas objects. When operations are performed with the CLI and no disk group is passed to the “-g” (disk group to use) option, the command will default to using the value assigned to defaultdg. This value of defaultdg is stored in the /etc/vx/volboot file:
$ grep defaultdg /etc/vx/volboot
defaultdg oradg
If you would like to change the default disk group, you can use vxdctl(1m)’s “defaultdg” option:
$ vxdctl defaultdg oof
To verify that the value was changed, you can run vxdg(1m) with the “defaultdg” option:
$ vxdg defaultdg
oof
This can save a lot of typing when creating new Veritas objects!
Veritas Volume Manager comes with a wide variety of command line utilities, which can be used to create, delete and maintain Veritas objects. When operations are performed with the CLI and no disk group is passed to the “-g” (disk group to use) option, the command will default to using the value assigned to defaultdg. This value of defaultdg is stored in the /etc/vx/volboot file:
$ grep defaultdg /etc/vx/volboot
defaultdg oradg
If you would like to change the default disk group, you can use vxdctl(1m)’s “defaultdg” option:
$ vxdctl defaultdg oof
To verify that the value was changed, you can run vxdg(1m) with the “defaultdg” option:
$ vxdg defaultdg
oof
This can save a lot of typing when creating new Veritas objects!
VxVM Veritas Tasks
Starting, Stopping and Aborting Veritas Tasks
When mirrors and RAID5 devices are created with Veritas Volume Manager, the volume contents need to be synchronized to an initial consistent state (e.g., parity and mirrors need to be synchronized). Veritas assigns a unique task id to each operation, and allows tasks to be monitored and displayed with the vxtask(1m) utility:
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/R 05.57% 0/209698816/11689984 PLXATT oravol04 oravol04-02 oradg
Since synchronization can be extremely I/O intensive, you might want to pause a synchronization task until a later time. This is easily accomplished by passing the task id to vxtask(1m)’s pause option:
$ vxtask pause 172
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/P 05.76% 0/209698816/12077056 PLXATT oravol04 oravol04-02 oradg
Once you are ready to resume the task, you can use vxtask(1m)’s resume option:
$ vxtask resume 172
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/R 06.02% 0/209698816/12613632 PLXATT oravol04 oravol04-02 oradg
This is super useful, and it looks to have caught on.
When mirrors and RAID5 devices are created with Veritas Volume Manager, the volume contents need to be synchronized to an initial consistent state (e.g., parity and mirrors need to be synchronized). Veritas assigns a unique task id to each operation, and allows tasks to be monitored and displayed with the vxtask(1m) utility:
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/R 05.57% 0/209698816/11689984 PLXATT oravol04 oravol04-02 oradg
Since synchronization can be extremely I/O intensive, you might want to pause a synchronization task until a later time. This is easily accomplished by passing the task id to vxtask(1m)’s pause option:
$ vxtask pause 172
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/P 05.76% 0/209698816/12077056 PLXATT oravol04 oravol04-02 oradg
Once you are ready to resume the task, you can use vxtask(1m)’s resume option:
$ vxtask resume 172
$ vxtask list
TASKID PTID TYPE/STATE PCT PROGRESS
172 ATCOPY/R 06.02% 0/209698816/12613632 PLXATT oravol04 oravol04-02 oradg
This is super useful, and it looks to have caught on.
VxVM Enabling and Disabling I/O paths
Enabling and Disabling I/O paths with Veritas
Veritas Volume Manager (VxVM) comes with DMP (Dynamic MultiPathing) support. DMP allows VxVM to load-balance IOs across multiple controllers, offline paths when failures are detected, and dynamically disable paths when performing maintenance on a specific controller or path (e.g., ugprading a SAN switch or hot swapping an HBA). The process of manually disabling a path is easily accomplished by invoking VxVM’s vxdmpadm(1m) utility with the “disable” keyword, and the controller to disable:
$ vxdmpadm disable ctlr=c2
Once the controller is disabled you can monitor the number of IOs per path with vxdmpadm(1m)’s iostat utility:
$ vxdmpadm iostat show dmpnodename=c2t20d1s2 interval=5
cpu usage = 636us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 1800 0 230400 0 0.016102 0.000000
c3t21d1s2 1799 0 230272 0 0.016858 0.000000
cpu usage = 663us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 337 0 43136 0 0.014257 0.000000
c3t21d1s2 3007 0 384896 0 0.018631 0.000000
cpu usage = 657us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 0 0 0 0 0.000000 0.000000
c3t21d1s2 3357 0 429696 0 0.018183 0.000000
Once maintenance is performed, the controller can be brought back online with vxdmpadm(1m)’s “enable” option, and the controller to enable:
$ vxdmpadm enable ctlr=c2
Once the controller is back up and operational, VxVM will start sending IOs through the controller:
cpu usage = 904us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 0 0 0 0 0.000000 0.000000
c3t21d1s2 4064 0 520192 0 0.014160 0.000000
cpu usage = 719us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 1454 0 186112 0 0.013288 0.000000
c3t21d1s2 2720 1 348160 8 0.013732 0.250000
cpu usage = 697us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 2110 0 270080 0 0.013485 0.000000
c3t21d1s2 2112 0 270336 0 0.013361 0.000000
VxVM is awesome, and makes managing tons of storage a breeze!
Veritas Volume Manager (VxVM) comes with DMP (Dynamic MultiPathing) support. DMP allows VxVM to load-balance IOs across multiple controllers, offline paths when failures are detected, and dynamically disable paths when performing maintenance on a specific controller or path (e.g., ugprading a SAN switch or hot swapping an HBA). The process of manually disabling a path is easily accomplished by invoking VxVM’s vxdmpadm(1m) utility with the “disable” keyword, and the controller to disable:
$ vxdmpadm disable ctlr=c2
Once the controller is disabled you can monitor the number of IOs per path with vxdmpadm(1m)’s iostat utility:
$ vxdmpadm iostat show dmpnodename=c2t20d1s2 interval=5
cpu usage = 636us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 1800 0 230400 0 0.016102 0.000000
c3t21d1s2 1799 0 230272 0 0.016858 0.000000
cpu usage = 663us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 337 0 43136 0 0.014257 0.000000
c3t21d1s2 3007 0 384896 0 0.018631 0.000000
cpu usage = 657us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 0 0 0 0 0.000000 0.000000
c3t21d1s2 3357 0 429696 0 0.018183 0.000000
Once maintenance is performed, the controller can be brought back online with vxdmpadm(1m)’s “enable” option, and the controller to enable:
$ vxdmpadm enable ctlr=c2
Once the controller is back up and operational, VxVM will start sending IOs through the controller:
cpu usage = 904us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 0 0 0 0 0.000000 0.000000
c3t21d1s2 4064 0 520192 0 0.014160 0.000000
cpu usage = 719us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 1454 0 186112 0 0.013288 0.000000
c3t21d1s2 2720 1 348160 8 0.013732 0.250000
cpu usage = 697us per cpu memory = 32768b
OPERATIONS KBYTES AVG TIME(ms)
PATHNAME READS WRITES READS WRITES READS WRITES
c2t20d1s2 2110 0 270080 0 0.013485 0.000000
c3t21d1s2 2112 0 270336 0 0.013361 0.000000
VxVM is awesome, and makes managing tons of storage a breeze!
VxVM Monitoring
Monitoring VxVM Usage
While poking around /var/adm/vx this week, I noticed that VxVM (Veritas Volume Manager) 4.x logs all commands that have been executed to /var/adm/vx/cmdlog:
$ tail -6 /var/adm/vx/cmdlog
$ /usr/sbin/vxdg list -o alldgs
$ /usr/sbin/vxdisk -o alldgs list
$ /usr/sbin/vxprint -G -q -n -A
This is super cool, and will be extremely valuable for troubleshooting storage related problems.
While poking around /var/adm/vx this week, I noticed that VxVM (Veritas Volume Manager) 4.x logs all commands that have been executed to /var/adm/vx/cmdlog:
$ tail -6 /var/adm/vx/cmdlog
$ /usr/sbin/vxdg list -o alldgs
$ /usr/sbin/vxdisk -o alldgs list
$ /usr/sbin/vxprint -G -q -n -A
This is super cool, and will be extremely valuable for troubleshooting storage related problems.
VxVM Veritas controller information
Displaying Veritas controller information
When utilizing Veritas’s DMP utilities, a controller, enclosure or DMP nodename is required when performing most actions.
To list the controllers on a server, the vxdmpadm(1m) utility can be invoked with the “listctlr” option:
$ vxdmpadm listctlr all
CTLR-NAME ENCLR-TYPE STATE ENCLR-NAME
=====================================================
c3 EMC ENABLED EMC0
c2 EMC ENABLED EMC0
c0 Disk ENABLED Disk
The vxdmpadm(1m) utility also has a “getctlr” option to display the physical device path associated with a controller:
$ vxdmpadm getctlr c2
LNAME PNAME
===============
c2 /pci@80,2000/lpfc@1
When utilizing Veritas’s DMP utilities, a controller, enclosure or DMP nodename is required when performing most actions.
To list the controllers on a server, the vxdmpadm(1m) utility can be invoked with the “listctlr” option:
$ vxdmpadm listctlr all
CTLR-NAME ENCLR-TYPE STATE ENCLR-NAME
=====================================================
c3 EMC ENABLED EMC0
c2 EMC ENABLED EMC0
c0 Disk ENABLED Disk
The vxdmpadm(1m) utility also has a “getctlr” option to display the physical device path associated with a controller:
$ vxdmpadm getctlr c2
LNAME PNAME
===============
c2 /pci@80,2000/lpfc@1
VxVM Install
Important Notes for Installing VERITAS Volume Manager (VxVM)
* Check what VERITAS packages are currently running:
# pkginfo | grep –i VRTS
* Make sure the boot disk has at least two free partitions with 2048 contiguous sectors (512 bytes) aviable.
# prtvtoc /dev/rdsk/c0t0d0
* Make sure to save the boot disk information by using the “prtvtoc” command.
# prtvtoc /dev/rdsk/c0t0d0 > /etc/my_boot_disk_information
* Make sure to have a backup copy of the /etc/system and /etc/vfstab files.
* Add packages to your system.
# cd 2location_of_your_packages
# pkgadd –d . VRTSvxvm VRTSvmman VRTSvmdoc
* Add the license key by using vxlicinst.
# vxlicinst
* Then run the Volume Manager Installation program.
# vxinstall
* Check the .profile file to ensure the following paths:
# PATH=$PATH:/usr/lib/vxvm/bin:/opt/VRTSobgui/bin:/usr/sbin:/opt/VRTSob/bin
# MANPATH=$MANPATH:/opt/VRTS/man
# export PATH MANPATH
The VERITAS Enterprise Administrator (VEA) provides a Java-based graphical user interface for managing Veritas Volume Manager (VxVM).
Important Notes for how to set up VEA:
* Install the VEA software.
# cd 2location_of_your_packages
# pkgadd –a ../scripts/VRTSobadmin –d . VRTSob VRTSobgui VRTSvmpro VRTSfspro
* Start the VEA server if not, running.
# vxsvc –m (Check or monitor the VEA server is running)
# vxsvc (Start the VEA server)
* Start the Volume Manager User interface.
# vea &
The Most handy Volume Manager commands:
* # vxdiskadm
* # vxdctl enable (Force the VxVM configuration to rescan for the disks. See devfsadm)
* # vxassist (Assist to create a VxVM volume.)
* # vxdisk list rootdisk (Displays information about the header contents of the root disk.)
* # vxdg list rootdg (Displays information about the content of the rootdg disk group.)
* # vxprint –g rootdg –thf | more (Displays information about volumes in rootdg.)
In order to create VERITAS Volume Manager, you may use the following three methods:
(This article emphases on the CLI method.)
* VEA
* Command Line Interface (CLI)
* vxdiskadm
Steps to create a disk group:
* # vxdg init accountingdg disk01=c1t12d0
Steps to add a disk to a disk group:
* View the status of the disk.
# vxdisk list --or-- # vxdisk –s list
* Add one un-initialized disk to the free disk pool.
# vxdisksetup –i c1t8d0
* Add the disk to a disk group called accoutingdg.
# vxdg init accountingdg disk01=c1t8d0
# vxdg –g accountingdg adddisk disk02=c2t8d0
Steps to split objects between disk groups:
* # vxdg split sourcedg targetdg object …
Steps to join disk groups:
* # vxdg join sourcedg targetdg
Steps to remove a disk from a disk group:
* Remove the “disk01” disk from the “accountingdg” diskgroup.
# vxdg –g accountingdg rmdisk=disk01
Steps to remove a device from the free disk pool:
* Remove the c1t8d0 device from the free disk pool.
# vxdiskunsetup c2t8d0
Steps to manage disk group:
* To deport and import the “accountingdg” disk group.
# vxdg deport accountingdg
# vxdg –C import accountingdg
# vxdg –h other_hostname deport accountingdg
* To destroy the “accountingdg” disk group.
# vxdg destroy accountingdg
Steps to create a VOLUME:
* # vxassist –g accountingdg make payroll_vol 500m
* # vxassist –g accountingdg make gl_vol 1500m
Steps to mount a VOLUME:
If using ufs:
* # newfs /dev/vx/rdsk/accountingdg/payroll_vol
* # mkdir /payroll
* # mount –F ufs /dev/vx/dsk/accountingdg/payroll_vol /payroll
If using VxFS:
* # mkfs –f vxfs /dev/vx/rdsk/accountingdg/payroll_vol
* # mkdir /payroll
* # mount –F vxfs /dev/vx/dsk/accountingdg/payroll_vol /payroll
Steps to resize a VOLUME:
* # vxresize –g accountingdg payroll_vol 700m
Steps to remove a VOLUME:
* # vxedit –g accountingdg –rf rm payroll_vol
Steps to create a two striped and a mirror VOLUME:
* # vxassist –g accounting make ac_vol 500m layout=stripe,mirror
Steps to create a raid5 VOLUME:
* # vxassist –g accounting make ac_vol 500m layout=raid5 ncol=5 disk01 …
Display the VOLUME layout:
* # vxprint –rth
Add or remove a mirror to an existing VOLUME:
* # vxassist –g accountingdg mirror payroll_vol
* # vxplex –g accounitngdg –o rm dis payroll_plex01
Add a dirty region log to an existing VOLUME and specify the disk to use for the drl:
* # vxassist –g accountingdg addlog payroll_vol logtype=drl disk04
Move an existing VOLUME from its disk group to another disk group:
* # vxdg move accountingdg new_accountingdg payroll_vol
To start a VOLUME:
* #vxvol start
Steps to encapsulate and Root Disk Mirroring
* Use “vxdiskadm” to place another disk in rootdg with the same size or greater.
* Set the eeprom variable to enable VxVM to create a device alias in the openboot program.
# eeprom use-nvramrc?=true
* Use “vxdiskadm” to mirror the root volumes. (Option 6)
* Test you can reboot from mirror disk.
# vxmend off rootvol-01 (disable the boot disk)
# init 6
OK> devalias (check available boot disk aliases)
OK> boot vx-disk01
Write a script to use the “for” statement to do some work.
# for i in 0 1 2 3 4
>do
>cp –r /usr/sbin /mydir${i}
>mkfile 5m /mydir${i}
>dd if=/mydir/my_input_file of=/myother_dir/my_output_file &
>done
* Check what VERITAS packages are currently running:
# pkginfo | grep –i VRTS
* Make sure the boot disk has at least two free partitions with 2048 contiguous sectors (512 bytes) aviable.
# prtvtoc /dev/rdsk/c0t0d0
* Make sure to save the boot disk information by using the “prtvtoc” command.
# prtvtoc /dev/rdsk/c0t0d0 > /etc/my_boot_disk_information
* Make sure to have a backup copy of the /etc/system and /etc/vfstab files.
* Add packages to your system.
# cd 2location_of_your_packages
# pkgadd –d . VRTSvxvm VRTSvmman VRTSvmdoc
* Add the license key by using vxlicinst.
# vxlicinst
* Then run the Volume Manager Installation program.
# vxinstall
* Check the .profile file to ensure the following paths:
# PATH=$PATH:/usr/lib/vxvm/bin:/opt/VRTSobgui/bin:/usr/sbin:/opt/VRTSob/bin
# MANPATH=$MANPATH:/opt/VRTS/man
# export PATH MANPATH
The VERITAS Enterprise Administrator (VEA) provides a Java-based graphical user interface for managing Veritas Volume Manager (VxVM).
Important Notes for how to set up VEA:
* Install the VEA software.
# cd 2location_of_your_packages
# pkgadd –a ../scripts/VRTSobadmin –d . VRTSob VRTSobgui VRTSvmpro VRTSfspro
* Start the VEA server if not, running.
# vxsvc –m (Check or monitor the VEA server is running)
# vxsvc (Start the VEA server)
* Start the Volume Manager User interface.
# vea &
The Most handy Volume Manager commands:
* # vxdiskadm
* # vxdctl enable (Force the VxVM configuration to rescan for the disks. See devfsadm)
* # vxassist (Assist to create a VxVM volume.)
* # vxdisk list rootdisk (Displays information about the header contents of the root disk.)
* # vxdg list rootdg (Displays information about the content of the rootdg disk group.)
* # vxprint –g rootdg –thf | more (Displays information about volumes in rootdg.)
In order to create VERITAS Volume Manager, you may use the following three methods:
(This article emphases on the CLI method.)
* VEA
* Command Line Interface (CLI)
* vxdiskadm
Steps to create a disk group:
* # vxdg init accountingdg disk01=c1t12d0
Steps to add a disk to a disk group:
* View the status of the disk.
# vxdisk list --or-- # vxdisk –s list
* Add one un-initialized disk to the free disk pool.
# vxdisksetup –i c1t8d0
* Add the disk to a disk group called accoutingdg.
# vxdg init accountingdg disk01=c1t8d0
# vxdg –g accountingdg adddisk disk02=c2t8d0
Steps to split objects between disk groups:
* # vxdg split sourcedg targetdg object …
Steps to join disk groups:
* # vxdg join sourcedg targetdg
Steps to remove a disk from a disk group:
* Remove the “disk01” disk from the “accountingdg” diskgroup.
# vxdg –g accountingdg rmdisk=disk01
Steps to remove a device from the free disk pool:
* Remove the c1t8d0 device from the free disk pool.
# vxdiskunsetup c2t8d0
Steps to manage disk group:
* To deport and import the “accountingdg” disk group.
# vxdg deport accountingdg
# vxdg –C import accountingdg
# vxdg –h other_hostname deport accountingdg
* To destroy the “accountingdg” disk group.
# vxdg destroy accountingdg
Steps to create a VOLUME:
* # vxassist –g accountingdg make payroll_vol 500m
* # vxassist –g accountingdg make gl_vol 1500m
Steps to mount a VOLUME:
If using ufs:
* # newfs /dev/vx/rdsk/accountingdg/payroll_vol
* # mkdir /payroll
* # mount –F ufs /dev/vx/dsk/accountingdg/payroll_vol /payroll
If using VxFS:
* # mkfs –f vxfs /dev/vx/rdsk/accountingdg/payroll_vol
* # mkdir /payroll
* # mount –F vxfs /dev/vx/dsk/accountingdg/payroll_vol /payroll
Steps to resize a VOLUME:
* # vxresize –g accountingdg payroll_vol 700m
Steps to remove a VOLUME:
* # vxedit –g accountingdg –rf rm payroll_vol
Steps to create a two striped and a mirror VOLUME:
* # vxassist –g accounting make ac_vol 500m layout=stripe,mirror
Steps to create a raid5 VOLUME:
* # vxassist –g accounting make ac_vol 500m layout=raid5 ncol=5 disk01 …
Display the VOLUME layout:
* # vxprint –rth
Add or remove a mirror to an existing VOLUME:
* # vxassist –g accountingdg mirror payroll_vol
* # vxplex –g accounitngdg –o rm dis payroll_plex01
Add a dirty region log to an existing VOLUME and specify the disk to use for the drl:
* # vxassist –g accountingdg addlog payroll_vol logtype=drl disk04
Move an existing VOLUME from its disk group to another disk group:
* # vxdg move accountingdg new_accountingdg payroll_vol
To start a VOLUME:
* #vxvol start
Steps to encapsulate and Root Disk Mirroring
* Use “vxdiskadm” to place another disk in rootdg with the same size or greater.
* Set the eeprom variable to enable VxVM to create a device alias in the openboot program.
# eeprom use-nvramrc?=true
* Use “vxdiskadm” to mirror the root volumes. (Option 6)
* Test you can reboot from mirror disk.
# vxmend off rootvol-01 (disable the boot disk)
# init 6
OK> devalias (check available boot disk aliases)
OK> boot vx-disk01
Write a script to use the “for” statement to do some work.
# for i in 0 1 2 3 4
>do
>cp –r /usr/sbin /mydir${i}
>mkfile 5m /mydir${i}
>dd if=/mydir/my_input_file of=/myother_dir/my_output_file &
>done
VxVM CLI
Volume Manager CLI examples:
---------------------------
Initialize a disk
vxdisksetup -i device
List disks owned by local and remote hosts
vxdisk –o alldgs list
List disk header
vxdisk list diskname|device
Evacuate a disk
vxevac -g diskgroup from_disk to_disk
Rename a disk
vxedit -g diskgroup rename oldname newname
Set spare, no hot relocation,or reserved space on a disk
vxedit -g diskgroup set {spare|nohotuse|reserve}=on|off diskname
Unrelocate a disk
vxunreloc -g diskgroup original_diskname
Create a disk group
vxdg init diskgroup diskname=device
Add a disk to disk group
vxdg -g diskgroup adddisk diskname=device
Remove a disk from disk group
vxdg –g diskgroup rmdisk diskname
Deport, import or destroy a disk group
vxdg {deport|import|destroy} diskgroup
List disk groups
vxdg list [diskgroup]
Show free/spare space pool
vxdg –g diskgroup {free|spare}
Upgrade disk group
version vxdg [-T version] upgrade diskgroup
Rewrite disk headers, configuration copies, and kernel log copies in a disk
group.
vxdg flush diskgroup targetdg object...
or
vxdctl enable (for all disk groups)
Move an object between disk groups
vxdg move sourcedg targetdg object...
Split objects between disk groups
vxdg split sourcedg targetdg object...
Join disk groups
vxdg join sourcedg targetdg
List objects affected by a disk group move operation
vxdg listmove sourcedg targetdg object...
Create a volume
vxassist -g diskgroup make vol_name size layout=format diskname
Remove a volume
vxedit -g diskgroup -rf rm vol_name
or
vxassist -g diskgroup remove volume vol_name
Display a volume
vxprint -g diskgroup -vt vol_name
vxprint -g diskgroup –l vol_name
Change volume read policy
vxvol -g diskgroup rdpol round vol_name
vxvol -g diskgroup rdpol prefer vol_name preferrred_plex_name
vxvol -g diskgroup rdpol select vol_name
Mirror an existing plex
vxassist -g diskgroup mirror vol_name
Create a snapshot volume
vxassist –g diskgroup -b snapstart vol_name
vxassist –g diskgroup snapshot vol_name new_volume
Abort a snapshot
vxassist -g diskgroup snapabort orig_vol_name
Reassociate a snapshot
vxassist -g diskgroup snapback snapshot_vol
Dissociate a snapshot
vxassist -g diskgroup snapclear snapshot_vol
Print snapshot information
vxassist -g diskgroup snapprint vol_name
Relayout a volume
vxassist -g diskgroup relayout vol_name layout=new_layout [attributes...]
Convert to or from a layered layout
vxassist -g diskgroup convert vol_name layout=new_layout [attributes...]
Add a log to a volume
vxassist –g diskgroup addlog vol_name
Create a subdisk
vxmake -g diskgroup sd subdisk_name diskname offset length
Remove a subdisk
vxedit -g diskgroup rm subdisk_name
Display subdisk information
vxprint –st
vxprint -l subdisk_name
Associate a subdisk to a plex
vxsd assoc plex_name subdisk_name
Dissociate a subdisk
vxsd dis subdisk_name
Create a plex
vxmake -g diskgroup plex plex_name sd=subdisk_name,…
Associate a plex (to a volume)
vxplex –g diskgroup att vol_name plex_name
Unmirror a volume (remove a plex)
vxplex –o rm dis plex_name
Start/stop volumes
vxvol {start|stop} vol_name
Start/stop all volumes
vxvol {startall|stopall}
Recover a volume
vxrecover -sn vol_name
Detach a plex
vxplex –g diskgroup det plex_name
Attach a plex
vxplex –g diskgroup att vol_name plex_name
Change state flags on plex
vxmend fix {active|clean|stale} plex_name
Turn plex online/offline
vxmend {on|off} plex_name
Set FastResync flag on a
volume vxvol set fastresync=on vol_name
Count and size of VxVM disk I/Os completed per sample
time slice to a volume:
vxstat -g diskgroup [-i interval] [-c count] –d vol_name
VxVM I/O trace information—dump to file and read from file
vxtrace -g diskgroup [–t duration] –d [filename] –o dev,disk vol_name;
vxtrace –l –f /tmp/tracedata | pg
Sample I/O load with statistics—sequential
vxbench –w {read|write} –i iosize=size,iocount=count filename
Sample I/O load with statistics—random
vxbench –w {rand_read|rand_write} –i iosize=size,iocount=count,maxfilesize=size filename
View currently set VxVM kernel parameters
Example: View the current setting for the kernel
parameter vol_max_vol:
# echo ‘vol_max_vol/D’ | adb –k
Change VxVM kernel parameters
Example: Change the VxVM kernel parameter
vol_max_vol from the current value to a new
value of 2048 by adding the parameter to the
/etc/system file:
# set vxio: vol_max_vol=2048
Then, reboot the system.
######################################################
display disk listings:
# vxdisk list
display volume manager object listings
# vxprint -ht
display free space in a disk group
# vxdg -g free
list all volume manager tasks currently running on the system
# vxtask list
add a disk to Volume Manager (devicename = cXtXdX) (prompt driven)
# vxdiskadd
designate/remove a disk as a hot-relocation spare
# vxedit set spare=on
# vxedit set spare=off
rename a disk
# vxedit rename
reserve/unreserve a disk (space won't be allocated unless specifically mentioned in vxassist)
# vxedit set reserve=on
# vxedit set reserve=off
take a disk offline (first remove the disk from its disk group) (devicename=cXtXdXs2)
#vxdisk offline
remove a disk
(first, stop any applications associated with the volume,
unmount the volume, stop the volume,
if you need the data on the disk, move the volumes to another disk or back up volume)
--removing the disk from its disk group:
# vxdg -g rmdisk
--remove the disk from volume manager control (devicename=cXtXdXs2)
# vxdisk rm
display multipath information
# vxdisk list
create a disk group
(see 'add a disk to volume manager - if the group does not exist, you will be prompted)
upgrade a disk group
--list disk group version
# vxdg list
--upgrade disk group to highest version currently running
# vxdg upgrade
destroy a disk group
# vxdg destroy
display disk group information
# vxdg list
# vxdg list
move a disk group
(stop any applications associated with all volumes in the disk group,
unmount and stop all volumes in the disk group:
--deport (disable local access) the disk group to be moved on first system
# vxdg deport
--import (enable local access) the disk group and its disks from the second system
# vxdg import
--start all volumes in the disk group on the second system
# vxrecover -g -sb
or # vxvol -g startall
estimating maximum volume size (any_valid_type = raid5, stripe, mirror)
# vxassist -g maxsize layout=
create a concatenated volume (length examples = 15g, 15m)
# vxassist -g make
create a striped volume (length examples = 15g, 15m)
# vxassist -g make layout=stripe \
create a raid5 volume (without logging) (length examples = 15g, 15m)
# vxassist -g make layout=raid5,nolog \
create a raid5 volume (with logging) (length examples = 15g, 15m)
# vxassist -g make layout=raid5,log \
create a mirrored volume (without DRL) (length examples = 15g, 15m)
# vxassist -g make layout=mirror,nolog \
create a mirrored volume (with DRL) (length examples = 15g, 15m)
# vxassist -g make layout=mirror,log \
mirror an existing volume
# vxassist mirror
mirror all volumes within a disk group
# vxmirror -g -a
mirror the root (boot) disk
EEPROM variable "use-nvramrc?" must be set to true
# vxrootmir -v
remove a mirror (use 'vxprint -g -ht' to get plexname)
# vxplex -o rm dis
add a log to an existing volume
# vxassist addlog
remove a log from an existing volume
# vxassist remove log
create a raid 0+1 volume (without DRL)
# vxassist -g make layout=mirror-stripe,nolog \
nmirror=# nstripe=#
create a raid 1+0 volume (without DRL)
# vxassist -g make layout=stripe-mirror,nolog \
nmirror=# nstripe=#
resize a volume
# vxassist -g growto
# vxassist -g growby
# vxassist -g shrinkto
# vxassist -g shrinkby
estimate how much a volume can grow
# vxassist -g maxgrow
remove a volume
(stop all applications associated with the volume,
unmount volume (remove /etc/vfstab entry))
--stop volume
# vxvol stop
-- remove volume (for raid 1+0, use 'rf-rm')
# vxedit -r rm
or # vxassist remove volume
change the volume read policy
# vxvol rdpol
# vxvol rdpol prefer
change volume attributes
# vxedit set=
resize a filesystem (ufs cannot be shrunk, only grown)
# vxresize -g +
# vxresize -g -
join subdisks (must be in the order of offset on disks)
# vxsd join
---------------------------
Initialize a disk
vxdisksetup -i device
List disks owned by local and remote hosts
vxdisk –o alldgs list
List disk header
vxdisk list diskname|device
Evacuate a disk
vxevac -g diskgroup from_disk to_disk
Rename a disk
vxedit -g diskgroup rename oldname newname
Set spare, no hot relocation,or reserved space on a disk
vxedit -g diskgroup set {spare|nohotuse|reserve}=on|off diskname
Unrelocate a disk
vxunreloc -g diskgroup original_diskname
Create a disk group
vxdg init diskgroup diskname=device
Add a disk to disk group
vxdg -g diskgroup adddisk diskname=device
Remove a disk from disk group
vxdg –g diskgroup rmdisk diskname
Deport, import or destroy a disk group
vxdg {deport|import|destroy} diskgroup
List disk groups
vxdg list [diskgroup]
Show free/spare space pool
vxdg –g diskgroup {free|spare}
Upgrade disk group
version vxdg [-T version] upgrade diskgroup
Rewrite disk headers, configuration copies, and kernel log copies in a disk
group.
vxdg flush diskgroup targetdg object...
or
vxdctl enable (for all disk groups)
Move an object between disk groups
vxdg move sourcedg targetdg object...
Split objects between disk groups
vxdg split sourcedg targetdg object...
Join disk groups
vxdg join sourcedg targetdg
List objects affected by a disk group move operation
vxdg listmove sourcedg targetdg object...
Create a volume
vxassist -g diskgroup make vol_name size layout=format diskname
Remove a volume
vxedit -g diskgroup -rf rm vol_name
or
vxassist -g diskgroup remove volume vol_name
Display a volume
vxprint -g diskgroup -vt vol_name
vxprint -g diskgroup –l vol_name
Change volume read policy
vxvol -g diskgroup rdpol round vol_name
vxvol -g diskgroup rdpol prefer vol_name preferrred_plex_name
vxvol -g diskgroup rdpol select vol_name
Mirror an existing plex
vxassist -g diskgroup mirror vol_name
Create a snapshot volume
vxassist –g diskgroup -b snapstart vol_name
vxassist –g diskgroup snapshot vol_name new_volume
Abort a snapshot
vxassist -g diskgroup snapabort orig_vol_name
Reassociate a snapshot
vxassist -g diskgroup snapback snapshot_vol
Dissociate a snapshot
vxassist -g diskgroup snapclear snapshot_vol
Print snapshot information
vxassist -g diskgroup snapprint vol_name
Relayout a volume
vxassist -g diskgroup relayout vol_name layout=new_layout [attributes...]
Convert to or from a layered layout
vxassist -g diskgroup convert vol_name layout=new_layout [attributes...]
Add a log to a volume
vxassist –g diskgroup addlog vol_name
Create a subdisk
vxmake -g diskgroup sd subdisk_name diskname offset length
Remove a subdisk
vxedit -g diskgroup rm subdisk_name
Display subdisk information
vxprint –st
vxprint -l subdisk_name
Associate a subdisk to a plex
vxsd assoc plex_name subdisk_name
Dissociate a subdisk
vxsd dis subdisk_name
Create a plex
vxmake -g diskgroup plex plex_name sd=subdisk_name,…
Associate a plex (to a volume)
vxplex –g diskgroup att vol_name plex_name
Unmirror a volume (remove a plex)
vxplex –o rm dis plex_name
Start/stop volumes
vxvol {start|stop} vol_name
Start/stop all volumes
vxvol {startall|stopall}
Recover a volume
vxrecover -sn vol_name
Detach a plex
vxplex –g diskgroup det plex_name
Attach a plex
vxplex –g diskgroup att vol_name plex_name
Change state flags on plex
vxmend fix {active|clean|stale} plex_name
Turn plex online/offline
vxmend {on|off} plex_name
Set FastResync flag on a
volume vxvol set fastresync=on vol_name
Count and size of VxVM disk I/Os completed per sample
time slice to a volume:
vxstat -g diskgroup [-i interval] [-c count] –d vol_name
VxVM I/O trace information—dump to file and read from file
vxtrace -g diskgroup [–t duration] –d [filename] –o dev,disk vol_name;
vxtrace –l –f /tmp/tracedata | pg
Sample I/O load with statistics—sequential
vxbench –w {read|write} –i iosize=size,iocount=count filename
Sample I/O load with statistics—random
vxbench –w {rand_read|rand_write} –i iosize=size,iocount=count,maxfilesize=size filename
View currently set VxVM kernel parameters
Example: View the current setting for the kernel
parameter vol_max_vol:
# echo ‘vol_max_vol/D’ | adb –k
Change VxVM kernel parameters
Example: Change the VxVM kernel parameter
vol_max_vol from the current value to a new
value of 2048 by adding the parameter to the
/etc/system file:
# set vxio: vol_max_vol=2048
Then, reboot the system.
######################################################
display disk listings:
# vxdisk list
display volume manager object listings
# vxprint -ht
display free space in a disk group
# vxdg -g
list all volume manager tasks currently running on the system
# vxtask list
add a disk to Volume Manager (devicename = cXtXdX) (prompt driven)
# vxdiskadd
designate/remove a disk as a hot-relocation spare
# vxedit set spare=on
# vxedit set spare=off
rename a disk
# vxedit rename
reserve/unreserve a disk (space won't be allocated unless specifically mentioned in vxassist)
# vxedit set reserve=on
# vxedit set reserve=off
take a disk offline (first remove the disk from its disk group) (devicename=cXtXdXs2)
#vxdisk offline
remove a disk
(first, stop any applications associated with the volume,
unmount the volume, stop the volume,
if you need the data on the disk, move the volumes to another disk or back up volume)
--removing the disk from its disk group:
# vxdg -g
--remove the disk from volume manager control (devicename=cXtXdXs2)
# vxdisk rm
display multipath information
# vxdisk list
create a disk group
(see 'add a disk to volume manager - if the group does not exist, you will be prompted)
upgrade a disk group
--list disk group version
# vxdg list
--upgrade disk group to highest version currently running
# vxdg upgrade
destroy a disk group
# vxdg destroy
display disk group information
# vxdg list
# vxdg list
move a disk group
(stop any applications associated with all volumes in the disk group,
unmount and stop all volumes in the disk group:
--deport (disable local access) the disk group to be moved on first system
# vxdg deport
--import (enable local access) the disk group and its disks from the second system
# vxdg import
--start all volumes in the disk group on the second system
# vxrecover -g
or # vxvol -g
estimating maximum volume size (any_valid_type = raid5, stripe, mirror)
# vxassist -g
create a concatenated volume (length examples = 15g, 15m)
# vxassist -g
create a striped volume (length examples = 15g, 15m)
# vxassist -g
create a raid5 volume (without logging) (length examples = 15g, 15m)
# vxassist -g
create a raid5 volume (with logging) (length examples = 15g, 15m)
# vxassist -g
create a mirrored volume (without DRL) (length examples = 15g, 15m)
# vxassist -g
create a mirrored volume (with DRL) (length examples = 15g, 15m)
# vxassist -g
mirror an existing volume
# vxassist mirror
mirror all volumes within a disk group
# vxmirror -g
mirror the root (boot) disk
EEPROM variable "use-nvramrc?" must be set to true
# vxrootmir -v
remove a mirror (use 'vxprint -g
# vxplex -o rm dis
add a log to an existing volume
# vxassist addlog
remove a log from an existing volume
# vxassist remove log
create a raid 0+1 volume (without DRL)
# vxassist -g
nmirror=# nstripe=#
create a raid 1+0 volume (without DRL)
# vxassist -g
nmirror=# nstripe=#
resize a volume
# vxassist -g
# vxassist -g
# vxassist -g
# vxassist -g
estimate how much a volume can grow
# vxassist -g
remove a volume
(stop all applications associated with the volume,
unmount volume (remove /etc/vfstab entry))
--stop volume
# vxvol stop
-- remove volume (for raid 1+0, use 'rf-rm')
# vxedit -r rm
or # vxassist remove volume
change the volume read policy
# vxvol rdpol
# vxvol rdpol prefer
change volume attributes
# vxedit set
resize a filesystem (ufs cannot be shrunk, only grown)
# vxresize -g
# vxresize -g
join subdisks (must be in the order of offset on disks)
# vxsd join
Oracle opatch
opatch lsinventory -detail:
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
export PATH=/app/oracle/product/10.2.0/db_1/bin:/usr/bin:/usr/sbin
export ORACLE_HOSTNAME=dbtest.msk.ru
export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
1. Check the OPatch already installed or not
cd $ORACLE_HOME/OPatch
$opatch -lsinventory
This is command will show existing patch
Then Export the following environmet file
$ export ORACLE_HOME=/PROD10gR2/app/oracle
$ export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
$ export OBJECT_MODE=32_64
$ cd patch/
Extract the Following OPatch.
p5246372_10203_LINUX.zip
p5965763_10203_LINUX.zip
5246372
5965763
Then Go to patch Directory and apply the Opatch.
$ cd 5965763/
$ opatch apply
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.1.0.6.6
There are 1 products installed in this Oracle Home.
Installed Products (10):
Agent Required Support Files 11.1.0.6.6
Assistant Common Files 11.1.0.6.6
Bali Share 1.1.18.0.0
Buildtools Common Files 11.1.0.6.6
Character Set Migration Utility 11.1.0.6.6
Database Configuration and Upgrade Assistants 11.1.0.6.6
Database SQL Scripts 11.1.0.6.6
Database Workspace Manager 11.1.0.6.6
DBJAVA Required Support Files 11.1.0.6.6
Enterprise Edition Options 11.1.0.6.6
There are 10 products installed in this Oracle Home.
Intermin patches (1) :
Patch 111000 : applied on Mon May 23 19:44:08 IST 2008
Created on 27 Jul 2007, 05:43:46 hrs PST8PDT
Bugs fixed: 111000
Files Touched:
/qmtest.o --> ORACLE_HOME/lib/libserver11.a
libmapsym.so --> ORACLE_HOME/lib/libmapsym.so
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
/oracle/xml/jaxb/orajaxb.class --> ORACLE_HOME/lib/xml.jar
Patch Location in Inventory:
/home/oracle_TEST/product/11.1.0/db_1/inventory/oneoffs/111000
Patch Location in Storage area:
/home/oracle_TEST/product/11.1.0/db_1/.patch_storage/111000_Jul_27_2007_05_43_46
--------------------------------------------------------------------------------
OPatch succeeded.
-bugs_fixed Option Example
The following example shows the output of opatch lsinventory -bugs_fixed asc:
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 11g 11.1.0.6.6
Oracle Database 11g Release 2 Patch Set 2 11.1.0.6.6
There are 2 products installed in this Oracle Home.
List of Bugs fixed by Installed Patches:
Bug Fixed by Installed at Description
Patch
--- -------- ------------ -----------
1000000 6079591 Mon Oct 13 02:03:42 PDT 2008 test bug
6079591 6079591 Mon Oct 13 02:03:42 PDT 2008 MLR BUG FOR 10.2.0:.3 FOR CPU:JUL2:007
300500 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
300501 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
300502 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
6121268 6121268 Tue Aug 19 23:32:33 PDT 2008 DB-10.2.0.3-MOLECULE-007-CPUJUL2007
6121266 6121266 Tue Aug 19 23:32:27 PDT 2008 DB-10.2.0.3-MOLECULE-018-CPUJUL2007
6121264 6121264 Tue Aug 19 23:32:22 PDT 2008 DB-10.2.0.3-MOLECULE-017-CPUJUL2007
6121263 6121263 Tue Aug 19 23:32:14 PDT 2008 DB-10.2.0.3-MOLECULE-016-CPUJUL2007
.....
.....
(Middle section of report is intentionally excluded.)
.....
.....
6121248 6650096 Tue Feb 12 05:50:48 PST 2008 DB-10.2.0.3-MOLECULE-015-CPUJUL2007
6650096 6650096 Tue Feb 12 05:50:48 PST 2008 DB-10.2.0.3-MOLECULE-036-CPUJAN2008
6121247 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-006-CPUAPR2007
6397946 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-031-CPUOCT2007
6650095 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-035-CPUJAN2008
6650081 6650081 Tue Feb 12 05:50:35 PST 2008 DB-10.2.0.3-MOLECULE-034-CPUJAN2008
6646853 6646853 Tue Feb 12 05:50:28 PST 2008 MLR BUG FOR 10.2.0.3 FOR CPUJAN2008
6452863 6452863 Tue Feb 12 05:50:12 PST 2008 TRACKING BUG FOR CPUJUL2007
--------------------------------------------------------------------------------
OPatch succeeded.
-patch desc Option Example
The following example shows the output of opatch lsinventory -patch desc:
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Interim patches (39) :
Patch 6079591 : applied on Mon Oct 13 02:03:42 PDT 2008
Created on 21 Jun 2008, 03:42:18 hrs PST8PDT
Bugs fixed:
6079591, 1000000
Patch 300500 : applied on Fri Sep 05 02:25:34 PDT 2008
Created on 07 Nov 2007, 04:57:14 hrs US/Eastern
Bugs fixed:
300500, 300501, 300502
--------------------------------------------------------------------------------
OPatch succeeded.
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
export PATH=/app/oracle/product/10.2.0/db_1/bin:/usr/bin:/usr/sbin
export ORACLE_HOSTNAME=dbtest.msk.ru
export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
1. Check the OPatch already installed or not
cd $ORACLE_HOME/OPatch
$opatch -lsinventory
This is command will show existing patch
Then Export the following environmet file
$ export ORACLE_HOME=/PROD10gR2/app/oracle
$ export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
$ export OBJECT_MODE=32_64
$ cd patch/
Extract the Following OPatch.
p5246372_10203_LINUX.zip
p5965763_10203_LINUX.zip
5246372
5965763
Then Go to patch Directory and apply the Opatch.
$ cd 5965763/
$ opatch apply
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.1.0.6.6
There are 1 products installed in this Oracle Home.
Installed Products (10):
Agent Required Support Files 11.1.0.6.6
Assistant Common Files 11.1.0.6.6
Bali Share 1.1.18.0.0
Buildtools Common Files 11.1.0.6.6
Character Set Migration Utility 11.1.0.6.6
Database Configuration and Upgrade Assistants 11.1.0.6.6
Database SQL Scripts 11.1.0.6.6
Database Workspace Manager 11.1.0.6.6
DBJAVA Required Support Files 11.1.0.6.6
Enterprise Edition Options 11.1.0.6.6
There are 10 products installed in this Oracle Home.
Intermin patches (1) :
Patch 111000 : applied on Mon May 23 19:44:08 IST 2008
Created on 27 Jul 2007, 05:43:46 hrs PST8PDT
Bugs fixed: 111000
Files Touched:
/qmtest.o --> ORACLE_HOME/lib/libserver11.a
libmapsym.so --> ORACLE_HOME/lib/libmapsym.so
ins_rdbms.mk --> ORACLE_HOME/rdbms/lib/ioracle
/oracle/xml/jaxb/orajaxb.class --> ORACLE_HOME/lib/xml.jar
Patch Location in Inventory:
/home/oracle_TEST/product/11.1.0/db_1/inventory/oneoffs/111000
Patch Location in Storage area:
/home/oracle_TEST/product/11.1.0/db_1/.patch_storage/111000_Jul_27_2007_05_43_46
--------------------------------------------------------------------------------
OPatch succeeded.
-bugs_fixed Option Example
The following example shows the output of opatch lsinventory -bugs_fixed asc:
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 11g 11.1.0.6.6
Oracle Database 11g Release 2 Patch Set 2 11.1.0.6.6
There are 2 products installed in this Oracle Home.
List of Bugs fixed by Installed Patches:
Bug Fixed by Installed at Description
Patch
--- -------- ------------ -----------
1000000 6079591 Mon Oct 13 02:03:42 PDT 2008 test bug
6079591 6079591 Mon Oct 13 02:03:42 PDT 2008 MLR BUG FOR 10.2.0:.3 FOR CPU:JUL2:007
300500 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
300501 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
300502 300500 Fri Sep 05 02:25:34 PDT 2008 Demo bug for patching files
6121268 6121268 Tue Aug 19 23:32:33 PDT 2008 DB-10.2.0.3-MOLECULE-007-CPUJUL2007
6121266 6121266 Tue Aug 19 23:32:27 PDT 2008 DB-10.2.0.3-MOLECULE-018-CPUJUL2007
6121264 6121264 Tue Aug 19 23:32:22 PDT 2008 DB-10.2.0.3-MOLECULE-017-CPUJUL2007
6121263 6121263 Tue Aug 19 23:32:14 PDT 2008 DB-10.2.0.3-MOLECULE-016-CPUJUL2007
.....
.....
(Middle section of report is intentionally excluded.)
.....
.....
6121248 6650096 Tue Feb 12 05:50:48 PST 2008 DB-10.2.0.3-MOLECULE-015-CPUJUL2007
6650096 6650096 Tue Feb 12 05:50:48 PST 2008 DB-10.2.0.3-MOLECULE-036-CPUJAN2008
6121247 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-006-CPUAPR2007
6397946 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-031-CPUOCT2007
6650095 6650095 Tue Feb 12 05:50:41 PST 2008 DB-10.2.0.3-MOLECULE-035-CPUJAN2008
6650081 6650081 Tue Feb 12 05:50:35 PST 2008 DB-10.2.0.3-MOLECULE-034-CPUJAN2008
6646853 6646853 Tue Feb 12 05:50:28 PST 2008 MLR BUG FOR 10.2.0.3 FOR CPUJAN2008
6452863 6452863 Tue Feb 12 05:50:12 PST 2008 TRACKING BUG FOR CPUJUL2007
--------------------------------------------------------------------------------
OPatch succeeded.
-patch desc Option Example
The following example shows the output of opatch lsinventory -patch desc:
Oracle interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle_TEST/product/11.1.0/db_1
Central Inventory : /home/OUIHome_Opatch
from : /home/oracle_TEST/product/11.1.0/db_1/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.1.0.6.6
OUI location : /home/oracle_TEST/product/11.1.0/db_1/oui
Log file location : /home/oracle_
TEST/product/11.1.0/db1/cfgtoollogs/opatch/opatch-2008_May_25_11-09-34-IST_Wed.log
Patch history file: /scratch/userid/newDB/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /home/oracle_TEST/product/11.1.0/db_
1/cfgtoollogs/opatch/lsinv/lsinventory-2008_May_25_11-09-34-IST_Wed.txt
--------------------------------------------------------------------------------
Interim patches (39) :
Patch 6079591 : applied on Mon Oct 13 02:03:42 PDT 2008
Created on 21 Jun 2008, 03:42:18 hrs PST8PDT
Bugs fixed:
6079591, 1000000
Patch 300500 : applied on Fri Sep 05 02:25:34 PDT 2008
Created on 07 Nov 2007, 04:57:14 hrs US/Eastern
Bugs fixed:
300500, 300501, 300502
--------------------------------------------------------------------------------
OPatch succeeded.
Аналитические функции
SET LINESIZE 32767
SET PAGESIZE 50000
alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;
DROP TABLE EMP;
CREATE TABLE EMP(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
select ename, deptno, sal from emp
order by deptno, ename;
ENAME DEPTNO SAL
---------- ---------- ----------
CLARK 10 2450
KING 10 5000
MILLER 10 1300
ADAMS 20 1100
FORD 20 3000
JONES 20 2975
SCOTT 20 3000
SMITH 20 800
ALLEN 30 1600
BLAKE 30 2850
JAMES 30 950
MARTIN 30 1250
TURNER 30 1500
WARD 30 1250
14 rows selected.
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 10 5000 7450
MILLER 10 1300 8750
ADAMS 20 1100 9850
FORD 20 3000 12850
JONES 20 2975 15825
SCOTT 20 3000 18825
SMITH 20 800 19625
ALLEN 30 1600 21225
BLAKE 30 2850 24075
JAMES 30 950 25025
MARTIN 30 1250 26275
TURNER 30 1500 27775
WARD 30 1250 29025
14 rows selected.
Разделим группы отступами (пустыми строками)
break on deptno skip 1
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 5000 7450
MILLER 1300 8750
ADAMS 20 1100 9850
FORD 3000 12850
JONES 2975 15825
SCOTT 3000 18825
SMITH 800 19625
ALLEN 30 1600 21225
BLAKE 2850 24075
JAMES 950 25025
MARTIN 1250 26275
TURNER 1500 27775
WARD 1250 29025
14 rows selected.
Разобъём все результирующее множество на группы по полю DEPTNO
Теперь функция SUM() будет вычислять кумулятивные суммы зарплат отдельно для каждой группы
(в нашем случае для каждого номера отдела)
Видим суммы зарплат по каждому департаменту:
select ename, deptno, sal,
sum(sal) over
(partition by deptno order by ename) dep_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL DEP_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 5000 7450
MILLER 1300 8750
ADAMS 20 1100 1100
FORD 3000 4100
JONES 2975 7075
SCOTT 3000 10075
SMITH 800 10875
ALLEN 30 1600 1600
BLAKE 2850 4450
JAMES 950 5400
MARTIN 1250 6650
TURNER 1500 8150
WARD 1250 9400
14 rows selected.
В следующем запросе укажем конструкцию,
которая задает условие разбиения результирующего множества на группы
по полю DEPTNO.
Для последовательной нумерации строк в каждой группе,
в соответствии с критериями упорядочения, используется функция
ROW_NUMBER()
В результате видим, что SCOTT - четвертый по списку сотрудник
в отделе 20 при упорядочении по фамилии ENAME.
select ename, deptno, sal,
row_number() over
(partition by deptno order by ename) seq
from emp
order by deptno, ename;
ENAME DEPTNO SAL SEQ
---------- ---------- ---------- ----------
CLARK 10 2450 1
KING 5000 2
MILLER 1300 3
ADAMS 20 1100 1
FORD 3000 2
JONES 2975 3
SCOTT 3000 4
SMITH 800 5
ALLEN 30 1600 1
BLAKE 2850 2
JAMES 950 3
MARTIN 1250 4
TURNER 1500 5
WARD 1250 6
14 rows selected.
Все в одном запросе:
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot,
sum(sal) over
(order by deptno, ename) run_tot,
row_number() over
(partition by deptno order by ename) seq
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT RUN_TOT SEQ
---------- ---------- ---------- ---------- ---------- ----------
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 9850 1
FORD 3000 12850 12850 2
JONES 2975 15825 15825 3
SCOTT 3000 18825 18825 4
SMITH 800 19625 19625 5
ALLEN 30 1600 21225 21225 1
BLAKE 2850 24075 24075 2
JAMES 950 25025 25025 3
MARTIN 1250 26275 26275 4
TURNER 1500 27775 27775 5
WARD 1250 29025 29025 6
14 rows selected.
SET PAGESIZE 50000
alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;
DROP TABLE EMP;
CREATE TABLE EMP(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
Вывод сотрудников, номеров отделов и зарплат.
select ename, deptno, sal from emp
order by deptno, ename;
ENAME DEPTNO SAL
---------- ---------- ----------
CLARK 10 2450
KING 10 5000
MILLER 10 1300
ADAMS 20 1100
FORD 20 3000
JONES 20 2975
SCOTT 20 3000
SMITH 20 800
ALLEN 30 1600
BLAKE 30 2850
JAMES 30 950
MARTIN 30 1250
TURNER 30 1500
WARD 30 1250
14 rows selected.
Отсортируем результирующее множество по deptno и ename (внутри deptno).
Вычислим кумулятивную сумму зарплат по всему результирующему множеству.
Видим итоговую сумму зарплат
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 10 5000 7450
MILLER 10 1300 8750
ADAMS 20 1100 9850
FORD 20 3000 12850
JONES 20 2975 15825
SCOTT 20 3000 18825
SMITH 20 800 19625
ALLEN 30 1600 21225
BLAKE 30 2850 24075
JAMES 30 950 25025
MARTIN 30 1250 26275
TURNER 30 1500 27775
WARD 30 1250 29025
14 rows selected.
Разделим группы отступами (пустыми строками)
break on deptno skip 1
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 5000 7450
MILLER 1300 8750
ADAMS 20 1100 9850
FORD 3000 12850
JONES 2975 15825
SCOTT 3000 18825
SMITH 800 19625
ALLEN 30 1600 21225
BLAKE 2850 24075
JAMES 950 25025
MARTIN 1250 26275
TURNER 1500 27775
WARD 1250 29025
14 rows selected.
Разобъём все результирующее множество на группы по полю DEPTNO
Теперь функция SUM() будет вычислять кумулятивные суммы зарплат отдельно для каждой группы
(в нашем случае для каждого номера отдела)
Видим суммы зарплат по каждому департаменту:
select ename, deptno, sal,
sum(sal) over
(partition by deptno order by ename) dep_tot
from emp
order by deptno, ename;
ENAME DEPTNO SAL DEP_TOT
---------- ---------- ---------- ----------
CLARK 10 2450 2450
KING 5000 7450
MILLER 1300 8750
ADAMS 20 1100 1100
FORD 3000 4100
JONES 2975 7075
SCOTT 3000 10075
SMITH 800 10875
ALLEN 30 1600 1600
BLAKE 2850 4450
JAMES 950 5400
MARTIN 1250 6650
TURNER 1500 8150
WARD 1250 9400
14 rows selected.
В следующем запросе укажем конструкцию,
которая задает условие разбиения результирующего множества на группы
по полю DEPTNO.
Для последовательной нумерации строк в каждой группе,
в соответствии с критериями упорядочения, используется функция
ROW_NUMBER()
В результате видим, что SCOTT - четвертый по списку сотрудник
в отделе 20 при упорядочении по фамилии ENAME.
select ename, deptno, sal,
row_number() over
(partition by deptno order by ename) seq
from emp
order by deptno, ename;
ENAME DEPTNO SAL SEQ
---------- ---------- ---------- ----------
CLARK 10 2450 1
KING 5000 2
MILLER 1300 3
ADAMS 20 1100 1
FORD 3000 2
JONES 2975 3
SCOTT 3000 4
SMITH 800 5
ALLEN 30 1600 1
BLAKE 2850 2
JAMES 950 3
MARTIN 1250 4
TURNER 1500 5
WARD 1250 6
14 rows selected.
Все в одном запросе:
select ename, deptno, sal,
sum(sal) over
(order by deptno, ename) run_tot,
sum(sal) over
(order by deptno, ename) run_tot,
row_number() over
(partition by deptno order by ename) seq
from emp
order by deptno, ename;
ENAME DEPTNO SAL RUN_TOT RUN_TOT SEQ
---------- ---------- ---------- ---------- ---------- ----------
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 9850 1
FORD 3000 12850 12850 2
JONES 2975 15825 15825 3
SCOTT 3000 18825 18825 4
SMITH 800 19625 19625 5
ALLEN 30 1600 21225 21225 1
BLAKE 2850 24075 24075 2
JAMES 950 25025 25025 3
MARTIN 1250 26275 26275 4
TURNER 1500 27775 27775 5
WARD 1250 29025 29025 6
14 rows selected.
Oracle Distributed transactions
1. Identify the id of the transaction:
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;
2. Purge the transaction:
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
SQL> COMMIT;
3. Confirm that the transaction has been purged:
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;
Step 2:
=====
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386'); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
Fix:
===
This problem is logged as
Bug.2191458 (unpublished) UNABLE TO EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY WITH AUTO UNDO MANAGEMENT and is worked by development.
Use the following workaround:
You have to use local_tran_id.....
Issue commit before alter system set "_smu_debug_mode" = 4;
Follow the steps,
SQL>commit;
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
Step 3:
=====
When executing the following procedure(dbms_transaction.purge_lost_db_entry)
to delete entries from
dba_2pc_pending one encounters the following error:
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); ==>For example..
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94
ORA-06512: at line 1
Fix:
===
The transaction to be deleted is in the prepared state and has to be either
force committed or rolled back
SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared
SQL> rollback force '37.16.108'; ==>For example
Rollback complete.
SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback
SQL>Commit;
SQL>alter system set "_smu_debug_mode" = 4;
Rollback complete.
SQL> exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example
SQL> sho parameter distributed_l
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout integer 60
SQL> select value from v$parameter where name='distributed_lock_timeout';
VALUE
--------------------------------------------------------------------------------
60
select addr, kaddr, sid, type, id1 from v$lock where type = 'DX';
select
username
,osuser
,status
,sid
,serial#
,machine
,process
,terminal
,program
from
v$session
where saddr in ( select k2gtdses from sys.x$k2gte );
select
s.username
,s.osuser
,s.status
,s.sid
,s.serial#
,s.machine
,s.process
,s.terminal
,s.program
from
v$session s
where s.saddr in ( select x.k2gtdses from sys.x$k2gte x )
and not exists ( select
l.sid
from v$lock l
where l.type = 'DX'
and l.sid = s.sid
)
;
Сначала смотрим список транзакций и удостоверяемся, что их можно грохнуть:
SELECT P.LOCAL_TRAN_ID, P.STATE, P.HOST, N.DBUSER_OWNER
FROM DBA_2PC_PENDING P, DBA_2PC_NEIGHBORS N
WHERE P.LOCAL_TRAN_ID=N.LOCAL_TRAN_ID
ORDER BY DBUSER_OWNER
/
Потом, загоняем в спул результаты этого скрипта:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('’);
SELECT ‘COMMIT FORCE ‘'’ || LOCAL_TRAN_ID || ‘'’;’
FROM DBA_2PC_PENDING
/
Запускаем контент спула. И после того как все транзакции помечены force committed запускаем это:
DECLARE
CURSOR C_TRANSACTIONS IS
SELECT LOCAL_TRAN_ID, STATE
FROM DBA_2PC_PENDING;
TRAN_ID VARCHAR2(22);
STATE VARCHAR2(16);
BEGIN
OPEN C_TRANSACTIONS;
LOOP
FETCH C_TRANSACTIONS INTO TRAN_ID, STATE;
EXIT WHEN C_TRANSACTIONS%NOTFOUND;
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(TRAN_ID);
COMMIT;
END LOOP;
CLOSE C_TRANSACTIONS;
END;
/
SELECT s.current_queue_duration, s.sql_address, t.name,
t.start_time, t.status AS t_status, s.status AS s_status, s.sid
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr
select * from v$session;
select status, osuser, username, process, machine, program, module, logon_time sid, serial# from v$session
Убиваем 7--sid 19--serial#
alter system kill session '7,19'
alter system disconnect session '8,66' immediate
--либо
alter system disconnect session '8,66' post_transaction
ну orakill как раз и прибивает процесс:
SELECT 'orakill ' || i.instance_name || ' ' || p.spid as Kill_cmd
FROM v$process p, v$session s, v$instance i
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
Purging dba_2pc_pending
Years ago, when I worked more with Oracle Replication than I do now, I knew more or less every command I needed to handle in-doubt transactions (as a result of failed two phase commit). On one of our development server something went wrong with distributed transactions between Oracle10g and Transparent Gateway for MS SQL Server.
First, I tried to force rollback of transaction listed in dba_2pc_pending:
SQL> rollback force '19.27.91915';
where 19.27.91915 is LOCAL_TRAN_ID from dba_2pc_pending.
I guess the problem was with TG at MS SQL Server side, because Oracle occasionally tried unsuccessfully to execute rollback on MS SQL Server. At the end, developers simply turned off Oracle TG Listener. As a result I got plenty of error messages in alert.log like this:
Fri Sep 26 12:12:38 2008
Errors in file F:\oracle\admin\oraDB\bdump\orcl_reco_1736.trc:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: Message 2063 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [ lines] [TGATEWAY] []
I knew that Oracle has a package procedure that can permanently purge such transactions...., here it is: if you know that database is permanently lost and you can not rollback or commit, then you can use dbms_transaction.purge_lost_db_entry to remove failed transaction.
SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;
LOCAL_TRAN_ID STATE TRAN_COMMENT
---------------------- ---------------- ------------------------------
19.27.91915 forced rollback
SQL> connect / as sysdba
S
QL> execute dbms_transaction.purge_lost_db_entry('19.27.91915');
SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;
no rows selected
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;
2. Purge the transaction:
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
SQL> COMMIT;
3. Confirm that the transaction has been purged:
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;
Step 2:
=====
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386'); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
Fix:
===
This problem is logged as
Bug.2191458 (unpublished) UNABLE TO EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY WITH AUTO UNDO MANAGEMENT and is worked by development.
Use the following workaround:
You have to use local_tran_id.....
Issue commit before alter system set "_smu_debug_mode" = 4;
Follow the steps,
SQL>commit;
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
Step 3:
=====
When executing the following procedure(dbms_transaction.purge_lost_db_entry)
to delete entries from
dba_2pc_pending one encounters the following error:
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); ==>For example..
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94
ORA-06512: at line 1
Fix:
===
The transaction to be deleted is in the prepared state and has to be either
force committed or rolled back
SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared
SQL> rollback force '37.16.108'; ==>For example
Rollback complete.
SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback
SQL>Commit;
SQL>alter system set "_smu_debug_mode" = 4;
Rollback complete.
SQL> exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example
SQL> sho parameter distributed_l
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout integer 60
SQL> select value from v$parameter where name='distributed_lock_timeout';
VALUE
--------------------------------------------------------------------------------
60
select addr, kaddr, sid, type, id1 from v$lock where type = 'DX';
select
username
,osuser
,status
,sid
,serial#
,machine
,process
,terminal
,program
from
v$session
where saddr in ( select k2gtdses from sys.x$k2gte );
select
s.username
,s.osuser
,s.status
,s.sid
,s.serial#
,s.machine
,s.process
,s.terminal
,s.program
from
v$session s
where s.saddr in ( select x.k2gtdses from sys.x$k2gte x )
and not exists ( select
l.sid
from v$lock l
where l.type = 'DX'
and l.sid = s.sid
)
;
Сначала смотрим список транзакций и удостоверяемся, что их можно грохнуть:
SELECT P.LOCAL_TRAN_ID, P.STATE, P.HOST, N.DBUSER_OWNER
FROM DBA_2PC_PENDING P, DBA_2PC_NEIGHBORS N
WHERE P.LOCAL_TRAN_ID=N.LOCAL_TRAN_ID
ORDER BY DBUSER_OWNER
/
Потом, загоняем в спул результаты этого скрипта:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('’);
SELECT ‘COMMIT FORCE ‘'’ || LOCAL_TRAN_ID || ‘'’;’
FROM DBA_2PC_PENDING
/
Запускаем контент спула. И после того как все транзакции помечены force committed запускаем это:
DECLARE
CURSOR C_TRANSACTIONS IS
SELECT LOCAL_TRAN_ID, STATE
FROM DBA_2PC_PENDING;
TRAN_ID VARCHAR2(22);
STATE VARCHAR2(16);
BEGIN
OPEN C_TRANSACTIONS;
LOOP
FETCH C_TRANSACTIONS INTO TRAN_ID, STATE;
EXIT WHEN C_TRANSACTIONS%NOTFOUND;
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(TRAN_ID);
COMMIT;
END LOOP;
CLOSE C_TRANSACTIONS;
END;
/
SELECT s.current_queue_duration, s.sql_address, t.name,
t.start_time, t.status AS t_status, s.status AS s_status, s.sid
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr
select * from v$session;
select status, osuser, username, process, machine, program, module, logon_time sid, serial# from v$session
Убиваем 7--sid 19--serial#
alter system kill session '7,19'
alter system disconnect session '8,66' immediate
--либо
alter system disconnect session '8,66' post_transaction
ну orakill как раз и прибивает процесс:
SELECT 'orakill ' || i.instance_name || ' ' || p.spid as Kill_cmd
FROM v$process p, v$session s, v$instance i
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
Oracle Purging dba_2pc_pending
Years ago, when I worked more with Oracle Replication than I do now, I knew more or less every command I needed to handle in-doubt transactions (as a result of failed two phase commit). On one of our development server something went wrong with distributed transactions between Oracle10g and Transparent Gateway for MS SQL Server.
First, I tried to force rollback of transaction listed in dba_2pc_pending:
SQL> rollback force '19.27.91915';
where 19.27.91915 is LOCAL_TRAN_ID from dba_2pc_pending.
I guess the problem was with TG at MS SQL Server side, because Oracle occasionally tried unsuccessfully to execute rollback on MS SQL Server. At the end, developers simply turned off Oracle TG Listener. As a result I got plenty of error messages in alert.log like this:
Fri Sep 26 12:12:38 2008
Errors in file F:\oracle\admin\oraDB\bdump\orcl_reco_1736.trc:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: Message 2063 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [ lines] [TGATEWAY] []
I knew that Oracle has a package procedure that can permanently purge such transactions...., here it is: if you know that database is permanently lost and you can not rollback or commit, then you can use dbms_transaction.purge_lost_db_entry to remove failed transaction.
SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;
LOCAL_TRAN_ID STATE TRAN_COMMENT
---------------------- ---------------- ------------------------------
19.27.91915 forced rollback
SQL> connect / as sysdba
S
QL> execute dbms_transaction.purge_lost_db_entry('19.27.91915');
SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;
no rows selected
MAXDATAFILES, DB_FILES (ORA-01118, ORA-00059)
ORA-01118: cannot add any more database files: limit of XXX exceededORA-00059: maximum number of DB_FILES exceeded
MAXDATAFILES
select type, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';
TYPE RECORDS_TOTAL RECORDS_USED
--------------------------------------
DATAFILE 100 4
DB_FILES
show parameter db_files
NAME TYPE VALUE
-------- ------- -----
db_files integer 200
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'c:\temp\crctl.sql' REUSE NORESETLOGS;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCVRL_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCYST_.LOG'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD1RB_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD4TN_.LOG'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJD8BP_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJDCO7_.LOG'
) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEMP_FRHJFBC0_.TMP'
SIZE 65011712 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
select
tablespace_name,
file_name,
bytes/1024/1024 as Size_Mb
from dba_data_files
order by tablespace_name, file_name;
TABLESPACE_NAME FILE_NAME SIZE_MB
------------------------------------------------------------------------------------------------------
SYSAUX C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF 1540
SYSTEM C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF 930
UNDOTBS1 C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF 985
USERS C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF 210
select
tablespace_name,
count(file_name)
from dba_data_files
group by tablespace_name
order by tablespace_name;
TABLESPACE_NAME COUNT(FILE_NAME)
------------------------------------
SYSAUX 1
SYSTEM 1
UNDOTBS1 1
USERS 1
Сейчас всего 4 файла данных в базе.
Добавим ещё 103 файла:
create tablespace TEST
datafile size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited,
size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace TEST created.
BEGIN
FOR i IN 1 .. 100 LOOP
execute immediate 'alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited';
END LOOP;
END;
PL/SQL procedure successfully completed.
select
tablespace_name,
count(file_name)
from dba_data_files
group by tablespace_name
order by tablespace_name;
TABLESPACE_NAME COUNT(FILE_NAME)
------------------------------------
SYSAUX 1
SYSTEM 1
TEST 103
UNDOTBS1 1
USERS 1
Смотрим что произошло с параметром MAXDATAFILES:
select type, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';
TYPE RECORDS_TOTAL RECORDS_USED
-----------------------------------------
DATAFILE 200 107
Как видим значение параметра MAXDATAFILES увеличилось до 200,
т.е. до значения db_files:
show parameter db_files
NAME TYPE VALUE
-------- ------- -----
db_files integer 200
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'c:\temp\crctl.sql' REUSE NORESETLOGS;
Database altered.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCVRL_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FRHJCYST_.LOG'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD1RB_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_2_FRHJD4TN_.LOG'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJD8BP_.LOG',
'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_3_FRHJDCO7_.LOG'
) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FRHJDJVM_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FRHJDXSS_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FRHJF6QN_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FRHJFRGR_.DBF',
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEST_FVDGKGM7_.DBF',
.......................................................................
'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEST_FVDHYR81_.DBF'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_TEMP_FRHJFBC0_.TMP'
SIZE 65011712 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Начиная с версии 8i, не нужно пересоздавать controlfile при возникновении ошибки ORA-01118, а просто нужно увеличить параметр DB_FILES как и при ORA-00059.
Значение параметра MAXDATAFILES будет автоматически выровнено со значением
DB_FILES.
alter system set db_files=2048 scope=spfile sid='*';
alter system set processes=1024 scope=spfile sid='*';
alter system set control_file_record_keep_time=30 scope=spfile sid='*';
alter system set cursor_sharing='EXACT' scope=spfile sid='*';
alter system set filesystemio_options='SETALL' scope=spfile sid='*';
Solaris Swap
create the swap partition - can be datadg or rootdg
# vxassist -g datadgorrootdg make swap_v 500m layout=mirror A1L1 A1R1
or mkfile 300m /export/home/swap
add the swap partition
# /usr/sbin/swap -a /dev/vx/dsk/datadg/swap_v
create the entry in the /etc/vfstab but do not mount
/dev/vx/dsk/datadg/swap_v /dev/vx/rdsk/datadg/swap_v - swap - no -
Permanently create the swap partition
# vi /etc/rc2.d/S99addswap
#!/bin/ksh
#
# Add extra partition to swap
#
/usr/sbin/swap -a /dev/vx/dsk/datadg/swap_v
# chmod u+x /etc/rc2.d/S99addswap
-------------------------------------------------------
SWAPFILE - 2Gb created swapfile in /tmp
# cat S100swap
#!/sbin/sh
#
# Temporary measure to add swapspace 17/11/05 jc 64148
#
/usr/sbin/mkfile 2048m /tmp/swapleave
/usr/sbin/swap -a /tmp/swapleave
exit 0
Swap monitoring Script
FREE=`/usr/sbin/swap -s |awk '{print $11}'|cut -f1 -d 'k' `
USED=`/usr/sbin/swap -s |awk '{print $9}'|cut -f1 -d 'k' `
TOTAL=`expr $FREE + $USED`
LEFT=`expr $TOTAL - $USED`
RATIO=`expr $USED \* 100 / $TOTAL`
echo `date` > /tmp/swap.log
echo "Space left : $LEFT " >>/tmp/swap.log
echo "Total space : $TOTAL " >>/tmp/swap.log
echo "" >> /tmp/swap.log
echo "$RATIO% of the available swap space is utilized" >> /tmp/swap.log
echo "" >> /tmp/swap.log
ps -e -o user,pid,ppid,vsz,rss,time,comm | sort -k 4rn | head -15 >> /tmp/swap.log
echo "" >> /tmp/swap.log
/opt/freeware/bin/top >> /tmp/swap.log
#change the threshold here:
if [ $RATIO -ge 40 ]
then mailx -s "Swap issue on `hostname`" aaa@mail.ru < /tmp/swap.log
fi
rm /tmp/swap.log
# vxassist -g datadgorrootdg make swap_v 500m layout=mirror A1L1 A1R1
or mkfile 300m /export/home/swap
add the swap partition
# /usr/sbin/swap -a /dev/vx/dsk/datadg/swap_v
create the entry in the /etc/vfstab but do not mount
/dev/vx/dsk/datadg/swap_v /dev/vx/rdsk/datadg/swap_v - swap - no -
Permanently create the swap partition
# vi /etc/rc2.d/S99addswap
#!/bin/ksh
#
# Add extra partition to swap
#
/usr/sbin/swap -a /dev/vx/dsk/datadg/swap_v
# chmod u+x /etc/rc2.d/S99addswap
-------------------------------------------------------
SWAPFILE - 2Gb created swapfile in /tmp
# cat S100swap
#!/sbin/sh
#
# Temporary measure to add swapspace 17/11/05 jc 64148
#
/usr/sbin/mkfile 2048m /tmp/swapleave
/usr/sbin/swap -a /tmp/swapleave
exit 0
Swap monitoring Script
FREE=`/usr/sbin/swap -s |awk '{print $11}'|cut -f1 -d 'k' `
USED=`/usr/sbin/swap -s |awk '{print $9}'|cut -f1 -d 'k' `
TOTAL=`expr $FREE + $USED`
LEFT=`expr $TOTAL - $USED`
RATIO=`expr $USED \* 100 / $TOTAL`
echo `date` > /tmp/swap.log
echo "Space left : $LEFT " >>/tmp/swap.log
echo "Total space : $TOTAL " >>/tmp/swap.log
echo "" >> /tmp/swap.log
echo "$RATIO% of the available swap space is utilized" >> /tmp/swap.log
echo "" >> /tmp/swap.log
ps -e -o user,pid,ppid,vsz,rss,time,comm | sort -k 4rn | head -15 >> /tmp/swap.log
echo "" >> /tmp/swap.log
/opt/freeware/bin/top >> /tmp/swap.log
#change the threshold here:
if [ $RATIO -ge 40 ]
then mailx -s "Swap issue on `hostname`" aaa@mail.ru < /tmp/swap.log
fi
rm /tmp/swap.log
Подписаться на:
Сообщения (Atom)
