Procedure to add a swap file
You need to use dd command to create swapfile. Next you need to use mkswap command to set up a Linux swap area on a device or in a file.
a) Login as the root user
b) Type following command to create 512MB swap file (1024 * 512MB = 524288 block size):
$ dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
c) Set up a Linux swap area:
$ mkswap /swapfile1
d) Activate /swapfile1 swap space immediately:
$ swapon /swapfile1
e) To activate /swapfile1 after Linux system reboot, add entry to /etc/fstab file. Open this file using text editor such as vi:
$ vi /etc/fstab
Append following line to the above file:
/swapfile1 swap swap defaults 0 0
So next time Linux comes up after reboot, it enables the new swap file for you automatically.
g) How do I verify swap is activated or not?
Simply use free command:
$ free -m
Thursday, December 15, 2011
Sunday, December 11, 2011
Creating LVM in Linux
Creating Lvm in linux:-
======================
a) I have a disk named /dev/sdb which is of 25GB, using this We will
be creating a LVM. First we need to create physical volume, then
volume group then the logical volumes.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
b. First will be creating 3 partition using the device /dev/sdb
[root@rhel ~]# fdisk /dev/sdb
The number of cylinders for this disk is set to 3133.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3133, default 1): 1
Last cylinder or +size or +sizeM or +sizeK (1-3133, default 3133): +10204M
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 83 Linux
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L
0 Empty 1e Hidden W95 FAT1 75 PC/IX be Solaris boot
1 FAT12 24 NEC DOS 80 Old Minix bf Solaris
2 XENIX root 39 Plan 9 81 Minix / old Lin c1 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 82 Linux swap c4 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 83 Linux c6 DRDOS/sec (FAT-
5 Extended 41 PPC PReP Boot 84 OS/2 hidden C: c7 Syrinx
6 FAT16 42 SFS 85 Linux extended da Non-FS data
7 HPFS/NTFS 4d QNX4.x 86 NTFS volume set db CP/M / CTOS / .
8 AIX 4e QNX4.x 2nd part 87 NTFS volume set de Dell Utility
9 AIX bootable 4f QNX4.x 3rd part 8e Linux LVM df BootIt
a OS/2 Boot Manag 50 OnTrack DM 93 Amoeba e1 DOS access
b W95 FAT32 51 OnTrack DM6 Aux 94 Amoeba BBT e3 DOS R/O
c W95 FAT32 (LBA) 52 CP/M 9f BSD/OS e4 SpeedStor
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a0 IBM Thinkpad hi eb BeOS fs
f W95 Ext'd (LBA) 54 OnTrackDM6 a5 FreeBSD ee EFI GPT
10 OPUS 55 EZ-Drive a6 OpenBSD ef EFI (FAT-12/16/
11 Hidden FAT12 56 Golden Bow a7 NeXTSTEP f0 Linux/PA-RISC b
12 Compaq diagnost 5c Priam Edisk a8 Darwin UFS f1 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor a9 NetBSD f4 SpeedStor
16 Hidden FAT16 63 GNU HURD or Sys ab Darwin boot f2 DOS secondary
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fd Linux raid auto
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fe LANstep
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid ff BBT
1c Hidden W95 FAT3
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (1243-3133, default 1243):
Using default value 1243
Last cylinder or +size or +sizeM or +sizeK (1243-3133, default 3133): +10204M
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 83 Linux
Command (m for help): t
Partition number (1-4): 2
Hex code (type L to list codes): 8e
Changed system type of partition 2 to 8e (Linux LVM)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 3
First cylinder (2485-3133, default 2485):
Using default value 2485
Last cylinder or +size or +sizeM or +sizeK (2485-3133, default 3133):
Using default value 3133
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 83 Linux
Command (m for help): t
Partition number (1-4): 3
Hex code (type L to list codes): 8e
Changed system type of partition 3 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
c) Now we have created 3 Partition of type Linux LVM, then execute the partproble cmd
to inform the changes made on the partition table.
[root@rhel ~]# partprobe
d) Create the physical volume using the 3 partition which we have created now
[root@rhel ~]# pvcreate /dev/sdb1 /dev/sdb2 /dev/sdb3
Physical volume "/dev/sdb1" successfully created
Physical volume "/dev/sdb2" successfully created
Physical volume "/dev/sdb3" successfully created
e) Check the list of physical volumes.
pvs - report information about physical volumes
[root@rhel ~]# pvs
PV VG Fmt Attr PSize PFree
/dev/sdb1 lvm2 -- 9.51G 9.51G
/dev/sdb2 lvm2 -- 9.51G 9.51G
/dev/sdb3 lvm2 -- 4.97G 4.97G
f) Then create a volumne gruoup using these physical volumes, the attribute -p defines the maximum no.
of physical volumes can be added in this volume group, the /dev/orahome defines the name of the
volumes group.
[root@rhel ~]# vgcreate -s 32 -p 100 /dev/orahome /dev/sdb1 /dev/sdb2 /dev/sdb3
Volume group "orahome" successfully created
e) Display and check the properties of the volume group which we have created.
[root@rhel ~]# vgdisplay -v orahome
Using volume group(s) on command line
Finding volume group "orahome"
--- Volume group ---
VG Name orahome
System ID
Format lvm2
Metadata Areas 3
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 100
Cur PV 3
Act PV 3
VG Size 23.97 GB
PE Size 32.00 MB
Total PE 767
Alloc PE / Size 0 / 0
Free PE / Size 767 / 23.97 GB
VG UUID zMQhxU-3Epq-UV54-QKkU-YpkI-mNIj-6UGxI1
--- Physical volumes ---
PV Name /dev/sdb1
PV UUID itUG6n-E9Ug-AW27-s0JI-iinC-Z8BZ-f3XzDG
PV Status allocatable
Total PE / Free PE 304 / 304
PV Name /dev/sdb2
PV UUID 3Dr36J-QLNT-PkIL-iIHE-3wKM-wcFs-3ntguB
PV Status allocatable
Total PE / Free PE 304 / 304
PV Name /dev/sdb3
PV UUID ukv2D3-hfkO-nKYX-7bk6-gu2T-OEHO-uiiTgH
PV Status allocatable
Total PE / Free PE 159 / 159
f) Create the logical volumes using the volume group.
[root@rhel ~]# lvcreate -L 23G -n orabin /dev/orahome
Logical volume "orabin" created
g) Check the properties of the lvm.
[root@rhel ~]# lvs
LV VG Attr LSize Origin Snap% Move Log Copy%
orabin orahome -wi-a- 23.00G
h) Now make the file system using the logical volume.
[root@rhel ~]# mke2fs -j /dev/orahome/orabin
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3014656 inodes, 6029312 blocks
301465 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
184 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
i) Create an directory and mount the lvm to the directory
[root@rhel /]# mkdir optware
[root@rhel /]# mount /dev/orahome/orabin /optware
[root@rhel /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.0G 626M 87% /
/dev/sda1 494M 17M 453M 4% /boot
none 506M 0 506M 0% /dev/shm
/dev/sda3 2.0G 36M 1.9G 2% /software
/dev/sda6 494M 11M 458M 3% /tmp
/dev/sda7 15G 70M 15G 1% /orafiles
/dev/mapper/orahome-orabin
23G 77M 22G 1% /optware
j) Add the entry in fstab system file.
[root@rhel /]# vi /etc/fstab
/dev/orahome/orabin /optware ext3 defaults 0 0
h) un mount the mount point and check whether it is mounting properly.
[root@rhel /]# umount /optware
[root@rhel /]# mount -a
[root@rhel /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.0G 626M 87% /
/dev/sda1 494M 17M 453M 4% /boot
none 506M 0 506M 0% /dev/shm
/dev/sda3 2.0G 36M 1.9G 2% /software
/dev/sda6 494M 11M 458M 3% /tmp
/dev/sda7 15G 70M 15G 1% /orafiles
/dev/mapper/orahome-orabin
23G 77M 22G 1% /optware
i) Now the lvm is ready to use
II) Steps to increase the size of the logical volume.
a) As per the above the size of the /optware mount point is 23G, we will
add another 2 GB to the mount point.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn't contain a valid partition table
b) We have device /dev/sdc with 2 GB , we need to add this to the existing
VG inorder to increase the Logical volume.
c) Create partition on the /dev/sdc.
[root@rhel ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L
0 Empty 1e Hidden W95 FAT1 75 PC/IX be Solaris boot
1 FAT12 24 NEC DOS 80 Old Minix bf Solaris
2 XENIX root 39 Plan 9 81 Minix / old Lin c1 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 82 Linux swap c4 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 83 Linux c6 DRDOS/sec (FAT-
5 Extended 41 PPC PReP Boot 84 OS/2 hidden C: c7 Syrinx
6 FAT16 42 SFS 85 Linux extended da Non-FS data
7 HPFS/NTFS 4d QNX4.x 86 NTFS volume set db CP/M / CTOS / .
8 AIX 4e QNX4.x 2nd part 87 NTFS volume set de Dell Utility
9 AIX bootable 4f QNX4.x 3rd part 8e Linux LVM df BootIt
a OS/2 Boot Manag 50 OnTrack DM 93 Amoeba e1 DOS access
b W95 FAT32 51 OnTrack DM6 Aux 94 Amoeba BBT e3 DOS R/O
c W95 FAT32 (LBA) 52 CP/M 9f BSD/OS e4 SpeedStor
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a0 IBM Thinkpad hi eb BeOS fs
f W95 Ext'd (LBA) 54 OnTrackDM6 a5 FreeBSD ee EFI GPT
10 OPUS 55 EZ-Drive a6 OpenBSD ef EFI (FAT-12/16/
11 Hidden FAT12 56 Golden Bow a7 NeXTSTEP f0 Linux/PA-RISC b
12 Compaq diagnost 5c Priam Edisk a8 Darwin UFS f1 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor a9 NetBSD f4 SpeedStor
16 Hidden FAT16 63 GNU HURD or Sys ab Darwin boot f2 DOS secondary
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fd Linux raid auto
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fe LANstep
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid ff BBT
1c Hidden W95 FAT3
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 261 2096451 8e Linux LVM
c) Run the partprobe to inform the kernel about the modified partition.
d) Create a physical volume.
[root@rhel ~]# pvcreate /dev/sdc1
Physical volume "/dev/sdc1" successfully created
[root@rhel ~]# pvs
PV VG Fmt Attr PSize PFree
/dev/sdb1 orahome lvm2 a- 9.50G 0
/dev/sdb2 orahome lvm2 a- 9.50G 0
/dev/sdb3 orahome lvm2 a- 4.97G 992.00M
/dev/sdc1 lvm2 -- 2.00G 2.00G
e) Increase the VG using the vgextend cmd.
[root@rhel ~]# vgextend orahome /dev/sdc1
Volume group "orahome" successfully extended
[root@rhel ~]# vgdisplay -v orahome
--- Volume group ---
VG Name orahome
System ID
Format lvm2
Metadata Areas 4
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 100
Cur PV 4
Act PV 4
VG Size 25.94 GB
PE Size 32.00 MB
Total PE 830
Alloc PE / Size 736 / 23.00 GB
Free PE / Size 94 / 2.94 GB
VG UUID zMQhxU-3Epq-UV54-QKkU-YpkI-mNIj-6UGxI1
f) As per the above the total size of the VG is 25.94 GB , and the allocated
size is 23 GB and the free size is 2.94 GB.
g) Now increase the lvm using the lvextend cmd.
[root@rhel ~]# lvextend -L 25G /dev/orahome/orabin
Extending logical volume orabin to 25.00 GB
Logical volume orabin successfully resized
h) Now resize the mount point. You can try to resize online , if it failures
the un mount the file sysem then resize and then mount again.
[root@rhel ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.5G 93M 99% /
/dev/sda1 494M 17M 453M 4% /boot
none 760M 0 760M 0% /dev/shm
/dev/sda3 2.0G 1.3G 637M 67% /software
/dev/sda6 494M 13M 457M 3% /tmp
/dev/sda7 15G 6.5G 7.7G 46% /orafiles
/dev/mapper/orahome-orabin
23G 8.3G 14G 39% /optware
[root@rhel ~]# umount /optware
[root@rhel ~]# resize2fs /dev/orahome/orabin
resize2fs 1.35 (28-Feb-2004)
Please run 'e2fsck -f /dev/orahome/orabin' first.
[root@rhel ~]# e2fsck -f /dev/orahome/orabin
e2fsck 1.35 (28-Feb-2004)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/orahome/orabin: 58274/3014656 files (1.7% non-contiguous), 2259610/6029312 blocks
[root@rhel ~]# resize2fs /dev/orahome/orabin
resize2fs 1.35 (28-Feb-2004)
Resizing the filesystem on /dev/orahome/orabin to 6553600 (4k) blocks.
The filesystem on /dev/orahome/orabin is now 6553600 blocks long.
[root@rhel ~]# mount /dev/orahome/orabin /optware/
[root@rhel ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.5G 93M 99% /
/dev/sda1 494M 17M 453M 4% /boot
none 760M 0 760M 0% /dev/shm
/dev/sda3 2.0G 1.3G 637M 67% /software
/dev/sda6 494M 13M 457M 3% /tmp
/dev/sda7 15G 6.5G 7.7G 46% /orafiles
/dev/mapper/orahome-orabin
25G 8.3G 16G 35% /optware
Now the /optware shows 25G.
======================
a) I have a disk named /dev/sdb which is of 25GB, using this We will
be creating a LVM. First we need to create physical volume, then
volume group then the logical volumes.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
b. First will be creating 3 partition using the device /dev/sdb
[root@rhel ~]# fdisk /dev/sdb
The number of cylinders for this disk is set to 3133.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3133, default 1): 1
Last cylinder or +size or +sizeM or +sizeK (1-3133, default 3133): +10204M
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 83 Linux
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L
0 Empty 1e Hidden W95 FAT1 75 PC/IX be Solaris boot
1 FAT12 24 NEC DOS 80 Old Minix bf Solaris
2 XENIX root 39 Plan 9 81 Minix / old Lin c1 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 82 Linux swap c4 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 83 Linux c6 DRDOS/sec (FAT-
5 Extended 41 PPC PReP Boot 84 OS/2 hidden C: c7 Syrinx
6 FAT16 42 SFS 85 Linux extended da Non-FS data
7 HPFS/NTFS 4d QNX4.x 86 NTFS volume set db CP/M / CTOS / .
8 AIX 4e QNX4.x 2nd part 87 NTFS volume set de Dell Utility
9 AIX bootable 4f QNX4.x 3rd part 8e Linux LVM df BootIt
a OS/2 Boot Manag 50 OnTrack DM 93 Amoeba e1 DOS access
b W95 FAT32 51 OnTrack DM6 Aux 94 Amoeba BBT e3 DOS R/O
c W95 FAT32 (LBA) 52 CP/M 9f BSD/OS e4 SpeedStor
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a0 IBM Thinkpad hi eb BeOS fs
f W95 Ext'd (LBA) 54 OnTrackDM6 a5 FreeBSD ee EFI GPT
10 OPUS 55 EZ-Drive a6 OpenBSD ef EFI (FAT-12/16/
11 Hidden FAT12 56 Golden Bow a7 NeXTSTEP f0 Linux/PA-RISC b
12 Compaq diagnost 5c Priam Edisk a8 Darwin UFS f1 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor a9 NetBSD f4 SpeedStor
16 Hidden FAT16 63 GNU HURD or Sys ab Darwin boot f2 DOS secondary
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fd Linux raid auto
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fe LANstep
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid ff BBT
1c Hidden W95 FAT3
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (1243-3133, default 1243):
Using default value 1243
Last cylinder or +size or +sizeM or +sizeK (1243-3133, default 3133): +10204M
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 83 Linux
Command (m for help): t
Partition number (1-4): 2
Hex code (type L to list codes): 8e
Changed system type of partition 2 to 8e (Linux LVM)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 3
First cylinder (2485-3133, default 2485):
Using default value 2485
Last cylinder or +size or +sizeM or +sizeK (2485-3133, default 3133):
Using default value 3133
Command (m for help): p
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 83 Linux
Command (m for help): t
Partition number (1-4): 3
Hex code (type L to list codes): 8e
Changed system type of partition 3 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
c) Now we have created 3 Partition of type Linux LVM, then execute the partproble cmd
to inform the changes made on the partition table.
[root@rhel ~]# partprobe
d) Create the physical volume using the 3 partition which we have created now
[root@rhel ~]# pvcreate /dev/sdb1 /dev/sdb2 /dev/sdb3
Physical volume "/dev/sdb1" successfully created
Physical volume "/dev/sdb2" successfully created
Physical volume "/dev/sdb3" successfully created
e) Check the list of physical volumes.
pvs - report information about physical volumes
[root@rhel ~]# pvs
PV VG Fmt Attr PSize PFree
/dev/sdb1 lvm2 -- 9.51G 9.51G
/dev/sdb2 lvm2 -- 9.51G 9.51G
/dev/sdb3 lvm2 -- 4.97G 4.97G
f) Then create a volumne gruoup using these physical volumes, the attribute -p defines the maximum no.
of physical volumes can be added in this volume group, the /dev/orahome defines the name of the
volumes group.
[root@rhel ~]# vgcreate -s 32 -p 100 /dev/orahome /dev/sdb1 /dev/sdb2 /dev/sdb3
Volume group "orahome" successfully created
e) Display and check the properties of the volume group which we have created.
[root@rhel ~]# vgdisplay -v orahome
Using volume group(s) on command line
Finding volume group "orahome"
--- Volume group ---
VG Name orahome
System ID
Format lvm2
Metadata Areas 3
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 100
Cur PV 3
Act PV 3
VG Size 23.97 GB
PE Size 32.00 MB
Total PE 767
Alloc PE / Size 0 / 0
Free PE / Size 767 / 23.97 GB
VG UUID zMQhxU-3Epq-UV54-QKkU-YpkI-mNIj-6UGxI1
--- Physical volumes ---
PV Name /dev/sdb1
PV UUID itUG6n-E9Ug-AW27-s0JI-iinC-Z8BZ-f3XzDG
PV Status allocatable
Total PE / Free PE 304 / 304
PV Name /dev/sdb2
PV UUID 3Dr36J-QLNT-PkIL-iIHE-3wKM-wcFs-3ntguB
PV Status allocatable
Total PE / Free PE 304 / 304
PV Name /dev/sdb3
PV UUID ukv2D3-hfkO-nKYX-7bk6-gu2T-OEHO-uiiTgH
PV Status allocatable
Total PE / Free PE 159 / 159
f) Create the logical volumes using the volume group.
[root@rhel ~]# lvcreate -L 23G -n orabin /dev/orahome
Logical volume "orabin" created
g) Check the properties of the lvm.
[root@rhel ~]# lvs
LV VG Attr LSize Origin Snap% Move Log Copy%
orabin orahome -wi-a- 23.00G
h) Now make the file system using the logical volume.
[root@rhel ~]# mke2fs -j /dev/orahome/orabin
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3014656 inodes, 6029312 blocks
301465 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
184 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
i) Create an directory and mount the lvm to the directory
[root@rhel /]# mkdir optware
[root@rhel /]# mount /dev/orahome/orabin /optware
[root@rhel /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.0G 626M 87% /
/dev/sda1 494M 17M 453M 4% /boot
none 506M 0 506M 0% /dev/shm
/dev/sda3 2.0G 36M 1.9G 2% /software
/dev/sda6 494M 11M 458M 3% /tmp
/dev/sda7 15G 70M 15G 1% /orafiles
/dev/mapper/orahome-orabin
23G 77M 22G 1% /optware
j) Add the entry in fstab system file.
[root@rhel /]# vi /etc/fstab
/dev/orahome/orabin /optware ext3 defaults 0 0
h) un mount the mount point and check whether it is mounting properly.
[root@rhel /]# umount /optware
[root@rhel /]# mount -a
[root@rhel /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.0G 626M 87% /
/dev/sda1 494M 17M 453M 4% /boot
none 506M 0 506M 0% /dev/shm
/dev/sda3 2.0G 36M 1.9G 2% /software
/dev/sda6 494M 11M 458M 3% /tmp
/dev/sda7 15G 70M 15G 1% /orafiles
/dev/mapper/orahome-orabin
23G 77M 22G 1% /optware
i) Now the lvm is ready to use
II) Steps to increase the size of the logical volume.
a) As per the above the size of the /optware mount point is 23G, we will
add another 2 GB to the mount point.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdc doesn't contain a valid partition table
b) We have device /dev/sdc with 2 GB , we need to add this to the existing
VG inorder to increase the Logical volume.
c) Create partition on the /dev/sdc.
[root@rhel ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): L
0 Empty 1e Hidden W95 FAT1 75 PC/IX be Solaris boot
1 FAT12 24 NEC DOS 80 Old Minix bf Solaris
2 XENIX root 39 Plan 9 81 Minix / old Lin c1 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 82 Linux swap c4 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 83 Linux c6 DRDOS/sec (FAT-
5 Extended 41 PPC PReP Boot 84 OS/2 hidden C: c7 Syrinx
6 FAT16 42 SFS 85 Linux extended da Non-FS data
7 HPFS/NTFS 4d QNX4.x 86 NTFS volume set db CP/M / CTOS / .
8 AIX 4e QNX4.x 2nd part 87 NTFS volume set de Dell Utility
9 AIX bootable 4f QNX4.x 3rd part 8e Linux LVM df BootIt
a OS/2 Boot Manag 50 OnTrack DM 93 Amoeba e1 DOS access
b W95 FAT32 51 OnTrack DM6 Aux 94 Amoeba BBT e3 DOS R/O
c W95 FAT32 (LBA) 52 CP/M 9f BSD/OS e4 SpeedStor
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a0 IBM Thinkpad hi eb BeOS fs
f W95 Ext'd (LBA) 54 OnTrackDM6 a5 FreeBSD ee EFI GPT
10 OPUS 55 EZ-Drive a6 OpenBSD ef EFI (FAT-12/16/
11 Hidden FAT12 56 Golden Bow a7 NeXTSTEP f0 Linux/PA-RISC b
12 Compaq diagnost 5c Priam Edisk a8 Darwin UFS f1 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor a9 NetBSD f4 SpeedStor
16 Hidden FAT16 63 GNU HURD or Sys ab Darwin boot f2 DOS secondary
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fd Linux raid auto
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fe LANstep
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid ff BBT
1c Hidden W95 FAT3
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rhel ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 65 522081 83 Linux
/dev/sda2 66 702 5116702+ 83 Linux
/dev/sda3 703 963 2096482+ 83 Linux
/dev/sda4 964 3263 18474750 5 Extended
/dev/sda5 964 1224 2096451 82 Linux swap
/dev/sda6 1225 1289 522081 83 Linux
/dev/sda7 1290 3263 15856123+ 83 Linux
Disk /dev/sdb: 25.7 GB, 25769803776 bytes
255 heads, 63 sectors/track, 3133 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1242 9976333+ 8e Linux LVM
/dev/sdb2 1243 2484 9976365 8e Linux LVM
/dev/sdb3 2485 3133 5213092+ 8e Linux LVM
Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 261 2096451 8e Linux LVM
c) Run the partprobe to inform the kernel about the modified partition.
d) Create a physical volume.
[root@rhel ~]# pvcreate /dev/sdc1
Physical volume "/dev/sdc1" successfully created
[root@rhel ~]# pvs
PV VG Fmt Attr PSize PFree
/dev/sdb1 orahome lvm2 a- 9.50G 0
/dev/sdb2 orahome lvm2 a- 9.50G 0
/dev/sdb3 orahome lvm2 a- 4.97G 992.00M
/dev/sdc1 lvm2 -- 2.00G 2.00G
e) Increase the VG using the vgextend cmd.
[root@rhel ~]# vgextend orahome /dev/sdc1
Volume group "orahome" successfully extended
[root@rhel ~]# vgdisplay -v orahome
--- Volume group ---
VG Name orahome
System ID
Format lvm2
Metadata Areas 4
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 100
Cur PV 4
Act PV 4
VG Size 25.94 GB
PE Size 32.00 MB
Total PE 830
Alloc PE / Size 736 / 23.00 GB
Free PE / Size 94 / 2.94 GB
VG UUID zMQhxU-3Epq-UV54-QKkU-YpkI-mNIj-6UGxI1
f) As per the above the total size of the VG is 25.94 GB , and the allocated
size is 23 GB and the free size is 2.94 GB.
g) Now increase the lvm using the lvextend cmd.
[root@rhel ~]# lvextend -L 25G /dev/orahome/orabin
Extending logical volume orabin to 25.00 GB
Logical volume orabin successfully resized
h) Now resize the mount point. You can try to resize online , if it failures
the un mount the file sysem then resize and then mount again.
[root@rhel ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.5G 93M 99% /
/dev/sda1 494M 17M 453M 4% /boot
none 760M 0 760M 0% /dev/shm
/dev/sda3 2.0G 1.3G 637M 67% /software
/dev/sda6 494M 13M 457M 3% /tmp
/dev/sda7 15G 6.5G 7.7G 46% /orafiles
/dev/mapper/orahome-orabin
23G 8.3G 14G 39% /optware
[root@rhel ~]# umount /optware
[root@rhel ~]# resize2fs /dev/orahome/orabin
resize2fs 1.35 (28-Feb-2004)
Please run 'e2fsck -f /dev/orahome/orabin' first.
[root@rhel ~]# e2fsck -f /dev/orahome/orabin
e2fsck 1.35 (28-Feb-2004)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/orahome/orabin: 58274/3014656 files (1.7% non-contiguous), 2259610/6029312 blocks
[root@rhel ~]# resize2fs /dev/orahome/orabin
resize2fs 1.35 (28-Feb-2004)
Resizing the filesystem on /dev/orahome/orabin to 6553600 (4k) blocks.
The filesystem on /dev/orahome/orabin is now 6553600 blocks long.
[root@rhel ~]# mount /dev/orahome/orabin /optware/
[root@rhel ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 4.9G 4.5G 93M 99% /
/dev/sda1 494M 17M 453M 4% /boot
none 760M 0 760M 0% /dev/shm
/dev/sda3 2.0G 1.3G 637M 67% /software
/dev/sda6 494M 13M 457M 3% /tmp
/dev/sda7 15G 6.5G 7.7G 46% /orafiles
/dev/mapper/orahome-orabin
25G 8.3G 16G 35% /optware
Now the /optware shows 25G.
Friday, October 21, 2011
Patch Set upgrade from 10.2.0.1 to 10.2.0.4 (Physical standby Database)
Patch set Upgrade from 10.2.0.1. to 10.2.0.4 ( Physical Standby database)
You can apply the Oracle Database release 10.2.0.4 patch set to the following Oracle Database 10g release 2 installations:
Oracle Database
Oracle Real Application Clusters
Oracle Database Client
Oracle Database Companion CD
Oracle Clusterware
Oracle Database Vault
Steps:
1. Download the Patch Set p6810189_10204_Linux-x86.zip from oracle support.
2. Inorder to upgrade to 10.2.0.4 our timezone file version should be 4, if it is less than/greater than 4 then
take action as per metalink note 553812.1
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
Process Overview:
1. In the primary disable log shipping to the standby site.
SQL> alter system set log_archive_dest_state_2 = 'DEFER';
System altered.
2. Shutdown the standby site and apply interim patchsets to the RDBMS binaries as per the README.
This includes Patchset/Patchset Update(PSU)/Critical Patch Update (CPU). You will not be able to and do not need to run
the scripts(catpatch.sql etc) against the standby rdbms itself.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit;
Set the Environmane Variables oracle_home and oracle_sid
$ lsnrctl stop
$ emctl stop dbconsole
-Unzip the Downloaded Patch file.
$ unzip p6810189_10204_Linux-x86.zip -d /u01/patch
$ cd /u01/patch/disk1
7. If your doing a silent installation then modify the parameter in the response file
and start the runInstaller in silent mode.
$ cd /u01/patch/Disk1/response
$ vi patchset.rsp
Modify the values in the patchset.rsp file accordinly.
$ cd /u01/patch/Disk1
$ ./runInstaller -silent -responseFile /u01/patch/Disk1/response/patchset.rsp
- If Installing through GUI mode and not installing the Patch on the local machine
then enable the X forward on your desktop
In your desktop you need to have a software to forward the X Server Screens. eg. cygwin.
Here we have installed cygwin tool.
Double click the cygwin tool from you desktop and enter startxwin.
$ startxwin
It will open a xterminal , there enter the host name where your perfoming the
patch installation.
$ xhost + 192.168.1.5
192.168.1.5 being added to access control list
In your server set the display variable to where you want to forward your
GUI Screen, here to the desktop.
Bourne, Bash, or Korn shell:
$ export DISPLAY=192.168.1.2:0.0
C shell:
% setenv DISPLAY l192.168.1.2:0.0
- Start the runInstaller.
$ cd /u01/patch/Disk1
$ ./runInstaller
- Once the installation is complete it will as for to run the root.sh script
run this script as root user.
# sh /u02/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Startup the listener process and the databse:-
$ lsnrctl start
[oracle@rhel11gstdby Disk1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 22 09:44:22 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 130025204 bytes
Database Buffers 310378496 bytes
Redo Buffers 2924544 bytes
Database mounted.
Note :- Do not run the upgrade scripts in the standby site.
3. Shutdown the primary site, apply the Patchset/PSU/CPU patch to the RDBMS binaries and patch the RDBMS itself using the
instructions in the README (run catpatch/catbundle/catcpu etc).
$ lsnrctl stop
$ emctl stop dbconsole
$ isqlplusctl stop
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Now install the patch set (10.2.0.4) as per the instruction said above.
Upgrade the database from 10.2.0.1 to 10.2.0.4
$ sqlplus / as sysdba
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 159385332 bytes
Database Buffers 281018368 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catupgrd.sql;
---
---
---
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-10-22 10:40:34
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 10:40:34
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 197134068 bytes
Database Buffers 243269632 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-10-22 10:43:03
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2011-10-22 10:44:36
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 10:44:59
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
4. Start the primary site, re-enable log shipping to the standby.
SQL> alter system set log_archive_dest_state_2 = 'ENABLE';
System altered.
5. At the standby site restart redo apply/managed recovery and the RDBMS changes implemented in the Primary Site through
catpatch/catbundle/catcpu will also be applied to the standby.
NOTE: Step 5. should be done immediately after upgrading the Database Binaries on the Standby Database. It is to
ensure the Data Dictionary (CATPROC)-Version matches the Version of the Database Binaries. If this does not
match (eg. when you upgrade the Standby Database Binaries first and perform a Role Change on the
Standby before you upgrade the Primary) you may run into severe Problems. Having different Patchlevels in a
Data Guard Physical Standby Database Environment is not supported anyway, see
Mixed Oracle Version support with Data Guard Redo Transport Services (Doc ID 785347.1)
for further Details and Reference.
6. Checks to perform to ensure the patch has been applied successfully at the primary and standby sites.
Check whether all the logs has been applied in the standby database, wailt until all the logs been
applied in the standby database. Once all the logs been applied successully the standby database
been upgraded successfully.
You can also verify it by opening the database in read only mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 130025204 bytes
Database Buffers 310378496 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 11:22:54
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
PL/SQL procedure successfully completed.
Reference:-
For information about physical standby databases, review document 278641.1.
For information about logical standby databases, review document 278643.1.
You can apply the Oracle Database release 10.2.0.4 patch set to the following Oracle Database 10g release 2 installations:
Oracle Database
Oracle Real Application Clusters
Oracle Database Client
Oracle Database Companion CD
Oracle Clusterware
Oracle Database Vault
Steps:
1. Download the Patch Set p6810189_10204_Linux-x86.zip from oracle support.
2. Inorder to upgrade to 10.2.0.4 our timezone file version should be 4, if it is less than/greater than 4 then
take action as per metalink note 553812.1
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
Process Overview:
1. In the primary disable log shipping to the standby site.
SQL> alter system set log_archive_dest_state_2 = 'DEFER';
System altered.
2. Shutdown the standby site and apply interim patchsets to the RDBMS binaries as per the README.
This includes Patchset/Patchset Update(PSU)/Critical Patch Update (CPU). You will not be able to and do not need to run
the scripts(catpatch.sql etc) against the standby rdbms itself.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit;
Set the Environmane Variables oracle_home and oracle_sid
$ lsnrctl stop
$ emctl stop dbconsole
-Unzip the Downloaded Patch file.
$ unzip p6810189_10204_Linux-x86.zip -d /u01/patch
$ cd /u01/patch/disk1
7. If your doing a silent installation then modify the parameter in the response file
and start the runInstaller in silent mode.
$ cd /u01/patch/Disk1/response
$ vi patchset.rsp
Modify the values in the patchset.rsp file accordinly.
$ cd /u01/patch/Disk1
$ ./runInstaller -silent -responseFile /u01/patch/Disk1/response/patchset.rsp
- If Installing through GUI mode and not installing the Patch on the local machine
then enable the X forward on your desktop
In your desktop you need to have a software to forward the X Server Screens. eg. cygwin.
Here we have installed cygwin tool.
Double click the cygwin tool from you desktop and enter startxwin.
$ startxwin
It will open a xterminal , there enter the host name where your perfoming the
patch installation.
$ xhost + 192.168.1.5
192.168.1.5 being added to access control list
In your server set the display variable to where you want to forward your
GUI Screen, here to the desktop.
Bourne, Bash, or Korn shell:
$ export DISPLAY=192.168.1.2:0.0
C shell:
% setenv DISPLAY l192.168.1.2:0.0
- Start the runInstaller.
$ cd /u01/patch/Disk1
$ ./runInstaller
- Once the installation is complete it will as for to run the root.sh script
run this script as root user.
# sh /u02/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Startup the listener process and the databse:-
$ lsnrctl start
[oracle@rhel11gstdby Disk1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 22 09:44:22 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 130025204 bytes
Database Buffers 310378496 bytes
Redo Buffers 2924544 bytes
Database mounted.
Note :- Do not run the upgrade scripts in the standby site.
3. Shutdown the primary site, apply the Patchset/PSU/CPU patch to the RDBMS binaries and patch the RDBMS itself using the
instructions in the README (run catpatch/catbundle/catcpu etc).
$ lsnrctl stop
$ emctl stop dbconsole
$ isqlplusctl stop
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Now install the patch set (10.2.0.4) as per the instruction said above.
Upgrade the database from 10.2.0.1 to 10.2.0.4
$ sqlplus / as sysdba
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 159385332 bytes
Database Buffers 281018368 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catupgrd.sql;
---
---
---
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-10-22 10:40:34
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 10:40:34
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 197134068 bytes
Database Buffers 243269632 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-10-22 10:43:03
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2011-10-22 10:44:36
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 10:44:59
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
4. Start the primary site, re-enable log shipping to the standby.
SQL> alter system set log_archive_dest_state_2 = 'ENABLE';
System altered.
5. At the standby site restart redo apply/managed recovery and the RDBMS changes implemented in the Primary Site through
catpatch/catbundle/catcpu will also be applied to the standby.
NOTE: Step 5. should be done immediately after upgrading the Database Binaries on the Standby Database. It is to
ensure the Data Dictionary (CATPROC)-Version matches the Version of the Database Binaries. If this does not
match (eg. when you upgrade the Standby Database Binaries first and perform a Role Change on the
Standby before you upgrade the Primary) you may run into severe Problems. Having different Patchlevels in a
Data Guard Physical Standby Database Environment is not supported anyway, see
Mixed Oracle Version support with Data Guard Redo Transport Services (Doc ID 785347.1)
for further Details and Reference.
6. Checks to perform to ensure the patch has been applied successfully at the primary and standby sites.
Check whether all the logs has been applied in the standby database, wailt until all the logs been
applied in the standby database. Once all the logs been applied successully the standby database
been upgraded successfully.
You can also verify it by opening the database in read only mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 130025204 bytes
Database Buffers 310378496 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-22-2011 11:22:54
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:10:37
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:29
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:26
Oracle Text VALID 10.2.0.4.0 00:00:36
Oracle XML Database VALID 10.2.0.4.0 00:02:38
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:17
Oracle Data Mining VALID 10.2.0.4.0 00:00:51
OLAP Analytic Workspace VALID 10.2.0.4.0 00:01:40
OLAP Catalog VALID 10.2.0.4.0 00:01:48
Oracle OLAP API VALID 10.2.0.4.0 00:01:34
Oracle interMedia VALID 10.2.0.4.0 00:05:43
Spatial VALID 10.2.0.4.0 00:02:05
Oracle Expression Filter VALID 10.2.0.4.0 00:00:11
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:44
Oracle Rule Manager VALID 10.2.0.4.0 00:00:11
.
Total Upgrade Time: 00:35:30
PL/SQL procedure successfully completed.
Reference:-
For information about physical standby databases, review document 278641.1.
For information about logical standby databases, review document 278643.1.
Wednesday, October 19, 2011
Patch Set upgrade from 10.2.0.1 to 10.2.0.4
Upgrade Oracle from 10.2.0.1 to 10.2.0.4 using Patch set (Single Instance)
You can apply the Oracle Database release 10.2.0.4 patch set to the following Oracle Database 10g release 2 installations:
Oracle Database
Oracle Real Application Clusters
Oracle Database Client
Oracle Database Companion CD
Oracle Clusterware
Oracle Database Vault
Steps:
1. Download the Patch Set p6810189_10204_Linux-x86.zip from oracle support.
2. Inorder to upgrade to 10.2.0.4 our timezone file version should be 4, if it is less than/greater than 4 then
take action as per metalink note 553812.1
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
3. Set the Environment Varibales:-
Bourne, Bash, or Korn shell:
$ ORACLE_HOME=Oracle_home
$ ORACLE_SID=sid
$ export ORACLE_HOME ORACLE_SID
C shell:
% setenv ORACLE_HOME Oracle_home
% setenv ORACLE_SID sid
3. Stop all the process:
a. listener - $ lsnrctl stop
b. Enterprise Manager - $ emctl stop dbconsole
c. isqlplus - $ isqlplusctl stop
4. Take the backup of the following components.
1. Oracle Inventory,
$ cp -r oraInventory /u01/bkpof10gbeforepatch/
2. Oracle 10g home
$ cd /u02/app/oracle/product/10.2.0
$ tar -cvf /u01/bkpof10gbeforepatch/10.2.0.1_home_binaries.tar db_1
Here first change the directory to the previous one to the oracle home and then
take the tape archive (tar) bakup of the db_1 directory which contains all
the binaries of the current 10.2.0.1 version and it will store the
backup in the /u01/bkpof10gbeforepatch folder with name as 10.2.0.1_home_binaries.tar
3. Oracle 10g Database
sql> create pfile = '/u01/bkpof10gbeforepatch/initfirstdb.ora' from spfile;
sql> shut immediate;
Take the backup of datafile, redologs and controlfiles.
$ cp -r firstdb /u01/bkpof10gbeforepatch/
Take the backup of Password file.
$ cd $ORACLE_HOME
$ cp orapwfirstdb /u01/bkpof10gbeforepatch
5. Unzip the Downloaded Patch file.
$ unzip p6810189_10204_Linux-x86.zip -d /u01/patch
$ cd /u01/patch/disk1
7. If your doing a silent installation then modify the parameter in the response file
and start the runInstaller in silent mode.
$ cd /u01/patch/Disk1/response
$ vi patchset.rsp
Modify the values in the patchset.rsp file accordinly.
$ cd /u01/patch/Disk1
$ ./runInstaller -silent -responseFile /u01/patch/Disk1/response/patchset.rsp
6. If Installing through GUI mode and not installing the Patch on the local machine
then enable the X forward on your desktop
In your desktop you need to have a software to forward the X Server Screens. eg. cygwin.
Here we have installed cygwin tool.
Double click the cygwin tool from you desktop and enter startxwin.
$ startxwin
It will open a xterminal , there enter the host name where your perfoming the
patch installation.
$ xhost + 192.168.1.4
192.168.1.4 being added to access control list
In your server set the display variable to where you want to forward your
GUI Screen, here to the desktop.
Bourne, Bash, or Korn shell:
$ export DISPLAY=192.168.1.2:0.0
C shell:
% setenv DISPLAY l192.168.1.2:0.0
8. Start the runInstaller.
$ cd /u01/patch/Disk1
$ ./runInstaller
9. Once the installation is complete it will as for to run the root.sh script
run this script as root user.
# sh /u02/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
10. Now upgrade the database from 10.2.0.1 to 10.2.0.4
[oracle@rhel11g Disk1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 20 10:20:04 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 159385332 bytes
Database Buffers 281018368 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
--Run the preupgrade tool and action accordingly.
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-20-2011 10:24:36
.
**********************************************************************
Database:
**********************************************************************
--> name: FIRSTDB
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 486 MB
.... AUTOEXTEND additional space required: 6 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 403 MB
.... AUTOEXTEND additional space required: 378 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 247 MB
.... AUTOEXTEND additional space required: 7 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
PL/SQL procedure successfully completed.
SQL> alter database datafile '/u02/app/oracle/firstdb/system01.dbf' resize 500m;
Database altered.
SQL> alter database datafile '/u02/app/oracle/firstdb/undotbs01.dbf' resize 450m;
Database altered.
SQL> alter database datafile '/u02/app/oracle/firstdb/sysaux01.dbf' resize 250m;
Database altered.
SQL> alter database tempfile '/u02/app/oracle/firstdb/temp01.dbf' resize 100m;
Database altered.
-- Run the catalog upgrade script
SQL>@?/rdbms/admin/catupgrd.sql
---
---
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-10-20 11:05:47
.
Oracle Database 10.2 Upgrade Status Utility 10-20-2011 11:05:47
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:08:15
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:37
Oracle XDK VALID 10.2.0.4.0 00:00:27
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:23
Oracle Text VALID 10.2.0.4.0 00:00:21
Oracle XML Database VALID 10.2.0.4.0 00:01:17
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:29
Oracle Data Mining VALID 10.2.0.4.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16
OLAP Catalog VALID 10.2.0.4.0 00:00:41
Oracle OLAP API VALID 10.2.0.4.0 00:00:38
Oracle interMedia VALID 10.2.0.4.0 00:03:22
Spatial VALID 10.2.0.4.0 00:01:26
Oracle Expression Filter VALID 10.2.0.4.0 00:00:08
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:10
Oracle Rule Manager VALID 10.2.0.4.0 00:00:06
.
Total Upgrade Time: 00:22:00
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
SQL> shutdown immediate;
SQL> EXIT;
$ sqlplus / as sysdba
sql> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 192939764 bytes
Database Buffers 247463936 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
--Now run the ultrp.sql script to recompile all the invalid objects.
SQL> @?/rdbms/admin/utlrp.sql;
PL/SQL procedure successfully completed.
SQL> col comp_id form a10;
SQL> col comp_name form a30;
SQL> col version form a10;
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
COMP_ID COMP_NAME VERSION STATUS MODIFIED
---------- ------------------------------ ---------- ----------- --------------------
CATALOG Oracle Database Catalog Views 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CATPROC Oracle Database Packages and T 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ypes
OWM Oracle Workspace Manager 10.2.0.4.3 VALID 20-OCT-2011 11:11:53
JAVAVM JServer JAVA Virtual Machine 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
XML Oracle XDK 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CATJAVA Oracle Database Java Packages 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
EXF Oracle Expression Filter 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ODM Oracle Data Mining 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CONTEXT Oracle Text 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
XDB Oracle XML Database 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
RUL Oracle Rule Manager 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ORDIM Oracle interMedia 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
APS OLAP Analytic Workspace 10.2.0.4.0 VALID 20-OCT-2011 11:11:54
XOQ Oracle OLAP API 10.2.0.4.0 VALID 20-OCT-2011 11:11:54
AMD OLAP Catalog 10.2.0.4.0 VALID 20-OCT-2011 11:11:56
SDO Spatial 10.2.0.4.0 VALID 20-OCT-2011 11:11:56
EM Oracle Enterprise Manager 10.2.0.4.0 VALID 20-OCT-2011 11:05:40
SQL> select CNAME,VERSION,STATUS,MODIFIED,DATE_UPGRADED,ORG_VERSION,PRV_VERSION from registry$;
CNAME VERSION STATUS MODIFIED DATE_UPGR ORG_VERSION PRV_VERSION
---------------------------------------- ---------- ---------- --------- --------- ------------------------------ ------------------------------
Oracle Database Catalog Views 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Database Packages and Types 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Workspace Manager 10.2.0.4.3 1 20-OCT-11 20-OCT-11 10.2.0.1.0
JServer JAVA Virtual Machine 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle XDK 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Database Java Packages 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Expression Filter 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Data Mining 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0
Oracle Text 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle XML Database 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Rule Manager 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle interMedia 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
OLAP Analytic Workspace 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle OLAP API 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
OLAP Catalog 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Spatial 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Enterprise Manager 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0
Run the post upgrade script and check the status.
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-20-2011 11:25:45
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:08:15
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:37
Oracle XDK VALID 10.2.0.4.0 00:00:27
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:23
Oracle Text VALID 10.2.0.4.0 00:00:21
Oracle XML Database VALID 10.2.0.4.0 00:01:17
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:29
Oracle Data Mining VALID 10.2.0.4.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16
OLAP Catalog VALID 10.2.0.4.0 00:00:41
Oracle OLAP API VALID 10.2.0.4.0 00:00:38
Oracle interMedia VALID 10.2.0.4.0 00:03:22
Spatial VALID 10.2.0.4.0 00:01:26
Oracle Expression Filter VALID 10.2.0.4.0 00:00:08
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:10
Oracle Rule Manager VALID 10.2.0.4.0 00:00:06
.
Total Upgrade Time: 00:22:00
PL/SQL procedure successfully completed.
-Upgrade Process Completed---
Running changePerm.sh Script on an Oracle Database Server Home
Important:
Oracle recommends using the most restrictive file permissions possible for the given implementation.
Perform these optional steps only after considering all security ramifications and only if you need to share this installation.
During patch set installation, all new files and directories are created with restricted access, by default. Users or third party
applications with a different group identifier from that of the database, which try to access client-side utilities or libraries in the database home,
will see permission errors when trying to access these files or directories. Perform the following steps to change the permissions:
Change to the install directory by using the following command:
$ cd $ORACLE_HOME/install
Run changePerm.sh and specify the patched server Oracle home location, before accessing client-side utilities or libraries in the database home.
===Revert back the change using the backup if any problem occurs=========
Remove the Oracle inventory
$ cd /u01/app
$ rm -r oraInventory
Remove the oracle home
$ cd /u02/app/oracle/product/10.2.0
$ rm -r db_1
Remove the databasefile, controlfiles and redo files
$ cd /u02/app/oracle
$ rm -r firstdb
Restore the oracle inventory from backup
$ cd /u01/bkpof10gbeforepatch
$ cp -r oraInventory /u01/app
Restore the oracle home from backup
$ cd /u02/app/oracle/product/10.2.0/
$ tar -xvf /u01/bkpof10gbeforepatch/10.2.0.1_home.tar
Restore the database from backup
$ cp -r /u01/bkpof10gbeforepatch/firstdb /u02/app/oracle/
Start the Database:-
[oracle@rhel11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 20 21:51:12 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 159384256 bytes
Database Buffers 281018368 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
sql> select COMP_NAME, VERSION, status from dba_registry;
COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
Oracle Expression Filter 10.2.0.1.0 VALID
Oracle Data Mining 10.2.0.1.0 VALID
Oracle Text 10.2.0.1.0 VALID
Oracle XML Database 10.2.0.1.0 VALID
Oracle Rules Manager 10.2.0.1.0 VALID
Oracle interMedia 10.2.0.1.0 VALID
OLAP Analytic Workspace 10.2.0.1.0 VALID
Oracle OLAP API 10.2.0.1.0 VALID
OLAP Catalog 10.2.0.1.0 VALID
Spatial 10.2.0.1.0 VALID
Oracle Enterprise Manager 10.2.0.1.0 VALID
--------------------------------------
The change has been reverted successfully.
You can apply the Oracle Database release 10.2.0.4 patch set to the following Oracle Database 10g release 2 installations:
Oracle Database
Oracle Real Application Clusters
Oracle Database Client
Oracle Database Companion CD
Oracle Clusterware
Oracle Database Vault
Steps:
1. Download the Patch Set p6810189_10204_Linux-x86.zip from oracle support.
2. Inorder to upgrade to 10.2.0.4 our timezone file version should be 4, if it is less than/greater than 4 then
take action as per metalink note 553812.1
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
3. Set the Environment Varibales:-
Bourne, Bash, or Korn shell:
$ ORACLE_HOME=Oracle_home
$ ORACLE_SID=sid
$ export ORACLE_HOME ORACLE_SID
C shell:
% setenv ORACLE_HOME Oracle_home
% setenv ORACLE_SID sid
3. Stop all the process:
a. listener - $ lsnrctl stop
b. Enterprise Manager - $ emctl stop dbconsole
c. isqlplus - $ isqlplusctl stop
4. Take the backup of the following components.
1. Oracle Inventory,
$ cp -r oraInventory /u01/bkpof10gbeforepatch/
2. Oracle 10g home
$ cd /u02/app/oracle/product/10.2.0
$ tar -cvf /u01/bkpof10gbeforepatch/10.2.0.1_home_binaries.tar db_1
Here first change the directory to the previous one to the oracle home and then
take the tape archive (tar) bakup of the db_1 directory which contains all
the binaries of the current 10.2.0.1 version and it will store the
backup in the /u01/bkpof10gbeforepatch folder with name as 10.2.0.1_home_binaries.tar
3. Oracle 10g Database
sql> create pfile = '/u01/bkpof10gbeforepatch/initfirstdb.ora' from spfile;
sql> shut immediate;
Take the backup of datafile, redologs and controlfiles.
$ cp -r firstdb /u01/bkpof10gbeforepatch/
Take the backup of Password file.
$ cd $ORACLE_HOME
$ cp orapwfirstdb /u01/bkpof10gbeforepatch
5. Unzip the Downloaded Patch file.
$ unzip p6810189_10204_Linux-x86.zip -d /u01/patch
$ cd /u01/patch/disk1
7. If your doing a silent installation then modify the parameter in the response file
and start the runInstaller in silent mode.
$ cd /u01/patch/Disk1/response
$ vi patchset.rsp
Modify the values in the patchset.rsp file accordinly.
$ cd /u01/patch/Disk1
$ ./runInstaller -silent -responseFile /u01/patch/Disk1/response/patchset.rsp
6. If Installing through GUI mode and not installing the Patch on the local machine
then enable the X forward on your desktop
In your desktop you need to have a software to forward the X Server Screens. eg. cygwin.
Here we have installed cygwin tool.
Double click the cygwin tool from you desktop and enter startxwin.
$ startxwin
It will open a xterminal , there enter the host name where your perfoming the
patch installation.
$ xhost + 192.168.1.4
192.168.1.4 being added to access control list
In your server set the display variable to where you want to forward your
GUI Screen, here to the desktop.
Bourne, Bash, or Korn shell:
$ export DISPLAY=192.168.1.2:0.0
C shell:
% setenv DISPLAY l192.168.1.2:0.0
8. Start the runInstaller.
$ cd /u01/patch/Disk1
$ ./runInstaller
9. Once the installation is complete it will as for to run the root.sh script
run this script as root user.
# sh /u02/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
10. Now upgrade the database from 10.2.0.1 to 10.2.0.4
[oracle@rhel11g Disk1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 20 10:20:04 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 159385332 bytes
Database Buffers 281018368 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
--Run the preupgrade tool and action accordingly.
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-20-2011 10:24:36
.
**********************************************************************
Database:
**********************************************************************
--> name: FIRSTDB
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 486 MB
.... AUTOEXTEND additional space required: 6 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 403 MB
.... AUTOEXTEND additional space required: 378 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 247 MB
.... AUTOEXTEND additional space required: 7 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
PL/SQL procedure successfully completed.
SQL> alter database datafile '/u02/app/oracle/firstdb/system01.dbf' resize 500m;
Database altered.
SQL> alter database datafile '/u02/app/oracle/firstdb/undotbs01.dbf' resize 450m;
Database altered.
SQL> alter database datafile '/u02/app/oracle/firstdb/sysaux01.dbf' resize 250m;
Database altered.
SQL> alter database tempfile '/u02/app/oracle/firstdb/temp01.dbf' resize 100m;
Database altered.
-- Run the catalog upgrade script
SQL>@?/rdbms/admin/catupgrd.sql
---
---
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-10-20 11:05:47
.
Oracle Database 10.2 Upgrade Status Utility 10-20-2011 11:05:47
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:08:15
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:37
Oracle XDK VALID 10.2.0.4.0 00:00:27
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:23
Oracle Text VALID 10.2.0.4.0 00:00:21
Oracle XML Database VALID 10.2.0.4.0 00:01:17
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:29
Oracle Data Mining VALID 10.2.0.4.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16
OLAP Catalog VALID 10.2.0.4.0 00:00:41
Oracle OLAP API VALID 10.2.0.4.0 00:00:38
Oracle interMedia VALID 10.2.0.4.0 00:03:22
Spatial VALID 10.2.0.4.0 00:01:26
Oracle Expression Filter VALID 10.2.0.4.0 00:00:08
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:10
Oracle Rule Manager VALID 10.2.0.4.0 00:00:06
.
Total Upgrade Time: 00:22:00
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
SQL> shutdown immediate;
SQL> EXIT;
$ sqlplus / as sysdba
sql> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1267980 bytes
Variable Size 192939764 bytes
Database Buffers 247463936 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
--Now run the ultrp.sql script to recompile all the invalid objects.
SQL> @?/rdbms/admin/utlrp.sql;
PL/SQL procedure successfully completed.
SQL> col comp_id form a10;
SQL> col comp_name form a30;
SQL> col version form a10;
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
COMP_ID COMP_NAME VERSION STATUS MODIFIED
---------- ------------------------------ ---------- ----------- --------------------
CATALOG Oracle Database Catalog Views 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CATPROC Oracle Database Packages and T 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ypes
OWM Oracle Workspace Manager 10.2.0.4.3 VALID 20-OCT-2011 11:11:53
JAVAVM JServer JAVA Virtual Machine 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
XML Oracle XDK 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CATJAVA Oracle Database Java Packages 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
EXF Oracle Expression Filter 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ODM Oracle Data Mining 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
CONTEXT Oracle Text 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
XDB Oracle XML Database 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
RUL Oracle Rule Manager 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
ORDIM Oracle interMedia 10.2.0.4.0 VALID 20-OCT-2011 11:11:53
APS OLAP Analytic Workspace 10.2.0.4.0 VALID 20-OCT-2011 11:11:54
XOQ Oracle OLAP API 10.2.0.4.0 VALID 20-OCT-2011 11:11:54
AMD OLAP Catalog 10.2.0.4.0 VALID 20-OCT-2011 11:11:56
SDO Spatial 10.2.0.4.0 VALID 20-OCT-2011 11:11:56
EM Oracle Enterprise Manager 10.2.0.4.0 VALID 20-OCT-2011 11:05:40
SQL> select CNAME,VERSION,STATUS,MODIFIED,DATE_UPGRADED,ORG_VERSION,PRV_VERSION from registry$;
CNAME VERSION STATUS MODIFIED DATE_UPGR ORG_VERSION PRV_VERSION
---------------------------------------- ---------- ---------- --------- --------- ------------------------------ ------------------------------
Oracle Database Catalog Views 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Database Packages and Types 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Workspace Manager 10.2.0.4.3 1 20-OCT-11 20-OCT-11 10.2.0.1.0
JServer JAVA Virtual Machine 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle XDK 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Database Java Packages 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Expression Filter 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Data Mining 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0
Oracle Text 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle XML Database 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Rule Manager 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle interMedia 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
OLAP Analytic Workspace 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle OLAP API 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
OLAP Catalog 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Spatial 10.2.0.4.0 1 20-OCT-11 10.2.0.1.0 10.2.0.1.0
Oracle Enterprise Manager 10.2.0.4.0 1 20-OCT-11 20-OCT-11 10.2.0.1.0
Run the post upgrade script and check the status.
SQL> @?/rdbms/admin/utlu102s.sql;
.
Oracle Database 10.2 Upgrade Status Utility 10-20-2011 11:25:45
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:08:15
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:37
Oracle XDK VALID 10.2.0.4.0 00:00:27
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:23
Oracle Text VALID 10.2.0.4.0 00:00:21
Oracle XML Database VALID 10.2.0.4.0 00:01:17
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:29
Oracle Data Mining VALID 10.2.0.4.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16
OLAP Catalog VALID 10.2.0.4.0 00:00:41
Oracle OLAP API VALID 10.2.0.4.0 00:00:38
Oracle interMedia VALID 10.2.0.4.0 00:03:22
Spatial VALID 10.2.0.4.0 00:01:26
Oracle Expression Filter VALID 10.2.0.4.0 00:00:08
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:10
Oracle Rule Manager VALID 10.2.0.4.0 00:00:06
.
Total Upgrade Time: 00:22:00
PL/SQL procedure successfully completed.
-Upgrade Process Completed---
Running changePerm.sh Script on an Oracle Database Server Home
Important:
Oracle recommends using the most restrictive file permissions possible for the given implementation.
Perform these optional steps only after considering all security ramifications and only if you need to share this installation.
During patch set installation, all new files and directories are created with restricted access, by default. Users or third party
applications with a different group identifier from that of the database, which try to access client-side utilities or libraries in the database home,
will see permission errors when trying to access these files or directories. Perform the following steps to change the permissions:
Change to the install directory by using the following command:
$ cd $ORACLE_HOME/install
Run changePerm.sh and specify the patched server Oracle home location, before accessing client-side utilities or libraries in the database home.
===Revert back the change using the backup if any problem occurs=========
Remove the Oracle inventory
$ cd /u01/app
$ rm -r oraInventory
Remove the oracle home
$ cd /u02/app/oracle/product/10.2.0
$ rm -r db_1
Remove the databasefile, controlfiles and redo files
$ cd /u02/app/oracle
$ rm -r firstdb
Restore the oracle inventory from backup
$ cd /u01/bkpof10gbeforepatch
$ cp -r oraInventory /u01/app
Restore the oracle home from backup
$ cd /u02/app/oracle/product/10.2.0/
$ tar -xvf /u01/bkpof10gbeforepatch/10.2.0.1_home.tar
Restore the database from backup
$ cp -r /u01/bkpof10gbeforepatch/firstdb /u02/app/oracle/
Start the Database:-
[oracle@rhel11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 20 21:51:12 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 159384256 bytes
Database Buffers 281018368 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
sql> select COMP_NAME, VERSION, status from dba_registry;
COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
Oracle Expression Filter 10.2.0.1.0 VALID
Oracle Data Mining 10.2.0.1.0 VALID
Oracle Text 10.2.0.1.0 VALID
Oracle XML Database 10.2.0.1.0 VALID
Oracle Rules Manager 10.2.0.1.0 VALID
Oracle interMedia 10.2.0.1.0 VALID
OLAP Analytic Workspace 10.2.0.1.0 VALID
Oracle OLAP API 10.2.0.1.0 VALID
OLAP Catalog 10.2.0.1.0 VALID
Spatial 10.2.0.1.0 VALID
Oracle Enterprise Manager 10.2.0.1.0 VALID
--------------------------------------
The change has been reverted successfully.
Database Upgrade/Downgrade Compatibility Matrix
Source Database Downgrade Possible to
10.2.x - 9.2.0.6.0 (or higher)
10.2.x - 10.1.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.1.x
Source Database Downgrade Possible to
10.1.x 9.2.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 9.2.x
Source Database Downgrade Possible to
9.2.x 9.0.1.3.0 (or higher)
9.2.x 8.1.7.3.0 (or higher)
UPGRADE
Upgrade Compatibility Matrix for Upgrading to 11.2.x
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2
Source Database Destination Database
9.2.0.8 (or higher) 11.2.x
10.1.0.5 (or higher) 11.2.x
10.2.0.2 (or higher) 11.2.x
11.1.0.6 (or higher) 11.2.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4.0 => 9.2.0.8 => 11.2.x
8.0.5 (or lower) => 8.0.6.x => 9.2.0.8 => 11.2.x
8.1.7 (or lower) => 8.1.7.4 => 10.2.0.4 => 11.2.x
9.0.1.3 (or lower) => 9.0.1.4 => 10.2.0.4 => 11.2.x
9.2.0.7 (or lower) => 9.2.0.8 => 11.2.x
Upgrade Compatibility Matrix for Upgrading to 11.1.x
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1
Source Database Destination Database
9.2.0.4 (or higher) 11.1.x
10.1.0.2 (or higher) 11.1.x
10.2.0.1 (or higher) 11.1.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4.0 => 9.2.0.8 => 11.1.x
8.0.5 (or lower) => 8.0.6.x => 9.2.0.8 => 11.1.x
8.1.7 (or lower) => 8.1.7.4 => 9.2.0.8 => 11.1.x
9.0.1.3 (or lower) => 9.0.1.4 => 9.2.0.8 => 11.1.x
9.2.0.3 (or lower) => 9.2.0.4.0 => 11.1.x
Upgrade Compatibility Matrix for Upgrading to 10.2.x
Minimum Version of the database that can be directly upgraded to Oracle 10.2.x
Source Database Destination Database
8.1.7.4 (or higher) 10.2.x
9.0.1.4 (or higher) 10.2.x
9.2.0.4 (or higher) 10.2.x
10.1.0.2 (or higher) 10.2.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4 => 8.1.7 =>8.1.7.4 => 10.2.x
7.3.4 (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
8.0.n (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
8.1.n (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
Upgrade Compatibility Matrix for Upgrading to 10.1.x
Minimum Version of the database that can be directly upgraded to Oracle 10.1.x
Source Database Destination Database
8.0.6 (or higher) 10.1.x
8.1.7 (or higher) 10.1.x
9.0.1 (or higher) 10.1.x
9.2.0 (or higher) 10.1.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.4 (or lower) => 8.0.6 => 10.1.x
8.0.5 (or lower) => 8.0.6 => 10.1.x
8.1.6 (or lower) => 8.1.7 => 10.1.x
Upgrade Compatibility Matrix for Upgrading to 9.2.x
Minimum Version of the database that can be directly upgraded to Oracle 9.2.x
Source Database Destination Database
7.3.4 (or higher) 9.2.x
8.0.6 (or higher) 9.2.x
8.1.7 (or higher) 9.2.x
9.0.1 (or higher) 9.2.x
The following database version will require an indirect upgrade path
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4 => 9.2.x
8.0.5 (or lower) => 8.0.6 => 9.2.x
8.1.6 (or lower) => 8.1.7 => 9.2.x
DOWNGRADE
Downgrade Compatibility Matrix for Downgrading from 11.2.x
Source Database Downgrade Possible to
11.2.x 10.1.0.5.0 (or higher)
11.2.x 10.2.0.3.0 (or higher)
11.2.x 11.1.0.6.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 11.1.x
Source Database Downgrade Possible to
11.1.x 10.1.0.5.0 (or higher)
11.1.x 10.2.0.3.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.2.x
Source Database Downgrade Possible to
10.2.x 9.2.0.6.0 (or higher)
10.2.x 10.1.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.1.x
Source Database Downgrade Possible to
10.1.x 9.2.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 9.2.x
Source Database Downgrade Possible to
9.2.x 9.0.1.3.0 (or higher)
9.2.x 8.1.7.3.0 (or higher)
NOTE
1 : If you have applied a patchset since upgrading then it will not be possible to downgrade.
For example: If you upgraded from 9.2.0.6 to 10.2.0.1 and then applied the 10.2.0.3 patchset, then you cannot downgrade from 10.2.0.3 to 9.2.0.6. (You can only downgrade from 10.2.0.3 to 9.2.0.6 if you upgraded from 9.2.0.6 to 10.2.0.3)
2 : You can only downgrade to a version if you previously upgraded from that version.
For example: You can downgrade an 11.1.0.6 database to 10.2.0.3 only if you upgraded the database from 10.2.3. If you created a new 11.1.0.6 database, then you cannot downgrade the database to 10.2.0.3. Also, if you upgraded directly from 9.2.0.8 to 11.1.0.6, then you cannot downgrade to 10.2.0.3 since you did not upgrade from 10.2.0.3.
3 : You cannot downgrade if the database parameter COMPATIBLE was ever set higher than the version you upgraded from.
For example: If you upgraded from 10.2.0.3 to 11.1.0.6, then after the upgrade set COMPATIBLE to 11.1.0.6, then no downgrade is possible. Also, if you upgraded directly from 9.2.0.8 to 11.1.0.6, then after upgrade set COMPATIBLE to 10.2.0.1, then
no downgrade is possible.
4 : You cannot upgrade database binaries from one major release to another.
For example : You cannot upgrade the binaries from 10.1.0.2.0 to 10.2.0.1.0. Major releases must be installed in a separate oracle home. You cannot install 10.2.0.x on any other major release to upgrade the oracle home.
Refer Metalink note : 551141.1
10.2.x - 9.2.0.6.0 (or higher)
10.2.x - 10.1.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.1.x
Source Database Downgrade Possible to
10.1.x 9.2.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 9.2.x
Source Database Downgrade Possible to
9.2.x 9.0.1.3.0 (or higher)
9.2.x 8.1.7.3.0 (or higher)
UPGRADE
Upgrade Compatibility Matrix for Upgrading to 11.2.x
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2
Source Database Destination Database
9.2.0.8 (or higher) 11.2.x
10.1.0.5 (or higher) 11.2.x
10.2.0.2 (or higher) 11.2.x
11.1.0.6 (or higher) 11.2.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4.0 => 9.2.0.8 => 11.2.x
8.0.5 (or lower) => 8.0.6.x => 9.2.0.8 => 11.2.x
8.1.7 (or lower) => 8.1.7.4 => 10.2.0.4 => 11.2.x
9.0.1.3 (or lower) => 9.0.1.4 => 10.2.0.4 => 11.2.x
9.2.0.7 (or lower) => 9.2.0.8 => 11.2.x
Upgrade Compatibility Matrix for Upgrading to 11.1.x
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1
Source Database Destination Database
9.2.0.4 (or higher) 11.1.x
10.1.0.2 (or higher) 11.1.x
10.2.0.1 (or higher) 11.1.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4.0 => 9.2.0.8 => 11.1.x
8.0.5 (or lower) => 8.0.6.x => 9.2.0.8 => 11.1.x
8.1.7 (or lower) => 8.1.7.4 => 9.2.0.8 => 11.1.x
9.0.1.3 (or lower) => 9.0.1.4 => 9.2.0.8 => 11.1.x
9.2.0.3 (or lower) => 9.2.0.4.0 => 11.1.x
Upgrade Compatibility Matrix for Upgrading to 10.2.x
Minimum Version of the database that can be directly upgraded to Oracle 10.2.x
Source Database Destination Database
8.1.7.4 (or higher) 10.2.x
9.0.1.4 (or higher) 10.2.x
9.2.0.4 (or higher) 10.2.x
10.1.0.2 (or higher) 10.2.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4 => 8.1.7 =>8.1.7.4 => 10.2.x
7.3.4 (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
8.0.n (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
8.1.n (or lower) =>8.1.7 => 8.1.7.4 => 10.2.x
Upgrade Compatibility Matrix for Upgrading to 10.1.x
Minimum Version of the database that can be directly upgraded to Oracle 10.1.x
Source Database Destination Database
8.0.6 (or higher) 10.1.x
8.1.7 (or higher) 10.1.x
9.0.1 (or higher) 10.1.x
9.2.0 (or higher) 10.1.x
The following database version will require an indirect upgrade path.
Source Database Upgrade Path Destination Database
7.3.4 (or lower) => 8.0.6 => 10.1.x
8.0.5 (or lower) => 8.0.6 => 10.1.x
8.1.6 (or lower) => 8.1.7 => 10.1.x
Upgrade Compatibility Matrix for Upgrading to 9.2.x
Minimum Version of the database that can be directly upgraded to Oracle 9.2.x
Source Database Destination Database
7.3.4 (or higher) 9.2.x
8.0.6 (or higher) 9.2.x
8.1.7 (or higher) 9.2.x
9.0.1 (or higher) 9.2.x
The following database version will require an indirect upgrade path
Source Database Upgrade Path Destination Database
7.3.3 (or lower) => 7.3.4 => 9.2.x
8.0.5 (or lower) => 8.0.6 => 9.2.x
8.1.6 (or lower) => 8.1.7 => 9.2.x
DOWNGRADE
Downgrade Compatibility Matrix for Downgrading from 11.2.x
Source Database Downgrade Possible to
11.2.x 10.1.0.5.0 (or higher)
11.2.x 10.2.0.3.0 (or higher)
11.2.x 11.1.0.6.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 11.1.x
Source Database Downgrade Possible to
11.1.x 10.1.0.5.0 (or higher)
11.1.x 10.2.0.3.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.2.x
Source Database Downgrade Possible to
10.2.x 9.2.0.6.0 (or higher)
10.2.x 10.1.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 10.1.x
Source Database Downgrade Possible to
10.1.x 9.2.0.4.0 (or higher)
Downgrade Compatibility Matrix for Downgrading from 9.2.x
Source Database Downgrade Possible to
9.2.x 9.0.1.3.0 (or higher)
9.2.x 8.1.7.3.0 (or higher)
NOTE
1 : If you have applied a patchset since upgrading then it will not be possible to downgrade.
For example: If you upgraded from 9.2.0.6 to 10.2.0.1 and then applied the 10.2.0.3 patchset, then you cannot downgrade from 10.2.0.3 to 9.2.0.6. (You can only downgrade from 10.2.0.3 to 9.2.0.6 if you upgraded from 9.2.0.6 to 10.2.0.3)
2 : You can only downgrade to a version if you previously upgraded from that version.
For example: You can downgrade an 11.1.0.6 database to 10.2.0.3 only if you upgraded the database from 10.2.3. If you created a new 11.1.0.6 database, then you cannot downgrade the database to 10.2.0.3. Also, if you upgraded directly from 9.2.0.8 to 11.1.0.6, then you cannot downgrade to 10.2.0.3 since you did not upgrade from 10.2.0.3.
3 : You cannot downgrade if the database parameter COMPATIBLE was ever set higher than the version you upgraded from.
For example: If you upgraded from 10.2.0.3 to 11.1.0.6, then after the upgrade set COMPATIBLE to 11.1.0.6, then no downgrade is possible. Also, if you upgraded directly from 9.2.0.8 to 11.1.0.6, then after upgrade set COMPATIBLE to 10.2.0.1, then
no downgrade is possible.
4 : You cannot upgrade database binaries from one major release to another.
For example : You cannot upgrade the binaries from 10.1.0.2.0 to 10.2.0.1.0. Major releases must be installed in a separate oracle home. You cannot install 10.2.0.x on any other major release to upgrade the oracle home.
Refer Metalink note : 551141.1
Tuesday, September 13, 2011
PL/SQL Easy Reference
PL/SQL Easy reference:-
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language
that offers the following advantages:
■ Tight Integration with SQL
■ Better Performance
■ Higher Productivity
■ Full Portability
■ Tight Security
■ Access to Pre-defined Packages
■ Support for Object-Oriented Programming
■ Support for Developing Web Applications and Pages
Block Structure:-
[DECLARE
-- declarations]
BEGIN
-- statements
[EXCEPTION
-- handlers]
END;
1) Declaring Variables:-
part_no NUMBER(6);
part_name VARCHAR2(20);
in_stock BOOLEAN;
part_price NUMBER(6,2);
raise_app_err exception;
emp_rec1 employees%ROWTYPE; -- The datatype of all columns of employees table.
eg:-
DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id FROM employees
WHERE employee_id = 120;
employee_rec c1%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
CLOSE C1;
END;
/
emp_rec2 employees.ename%TYPE; -- The datatype of ename column of employee table.
name varchar2(30);
anname name%type -- (anname datatype will be as per name datatype)
Collections:- (Similar to arrarys)
TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; -- Used for storing array values
comm_tab commissions;
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list ;
begin
staff := staff_list(100,200,300,400,500);
FOR i IN staff.FIRST..staff.LAST LOOP
SELECT last_name, first_name INTO lname, fname FROM employees
WHERE employees.employee_id = staff(i);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );
END LOOP;
END;
/
Record:- (Records are composite data structures whose fields can have different datatypes.)
TYPE meetin_typ IS RECORD(
date_held DATE,
duration timerec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));
met_ty meetin_typ
Different methods to assign arrays:-
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE varray_type IS VARRAY(5) OF INTEGER;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Shipping','Sales','Finance','Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 10; -- Just start assigning to elements
v3(7) := 100; -- Subscripts can be any integer values
v4(42) := 'Smith'; -- Just start assigning to elements
v4(54) := 'Jones'; -- Subscripts can be any integer values
v5('Canada') := 'North America'; -- Just start assigning to elements
v5('Greece') := 'Europe'; -- Subscripts can be string values
END;
/
2) Assigning values to variables:-
hourly_salary NUMBER := 22.50;
v_ename_first varchar2(30) := 'MANZOOR';
v_ename_last varchar2(30);
in_stock BOOLEAN := TRUE;
done BOOLEAN;
begin
comm_tab(5) := 20000 * 0.15;
select ename_last into v_ename_last from emp where empid = 10;
done := (counter > 100);
end;
blood_type char := 'O'; (OR) blood_type char default 'O';
cnt number not null := 100;
3) Declaring Constants
cons constant number := 10;
4) Processsing Queries with pl/sql
BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
',Last name = ' || someone.last_name);
END LOOP;
END;
/
5) IF Statement
Syntax:-
if condition then
stmt;
elseif condition then
stmt;
else
stmt;
end if;
6) Case Statement
Syntax:-
Case when condition then
stmt;
when condition then
stmt;
else
stmt;
end case;
7) Loop Statements:-
Syntax:-
Loop
stmt;
exit when condition;
End loop;
For Loop
Syntax:-
for i in 1..n loop
stmt;
end loop;
for i in (select * from emp) loop
stmt;
end loop;
While loop
Syntax:-
while condition loop
Stmt;
end loop;
8) Goto Statement (unconditional control transfer):-
Syntax:-
<>
eg:
i:=0;
<>
i := 1+1;
if i > 10 then
goto gothere;
else
goto comehere;
end if;
<>
end;
9) Using a Block Label for Name Resolution
<>
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
-- deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
-- OK, column and variable have different names
DELETE FROM employees2 WHERE last_name = main.last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
END;
/
9) Creating a Stored procedures:-
create or replace procedure get_emp_name (v_emp in number) as
ss varchar2(30);
begin
select ename into ss from emp where empno = v_emp;
dbms_output.put_line ('Name of the Employee is :' || ss);
exception
when no_data_found then
dbms_output.put_line ('There is no employ with emp id :' || v_emp);
end;
/
To Exec (1st method):-
declare
emp_id number;
begin
emp_id := 100;
get_emp_name(emp_id);
end;
/
To Exec (2nd method):-
SQL> var emp_id number;
SQL> exec :emp_id := 100;
SQL> call get_emp_name(:emp_id);
Name of the Employee is :Ahamed
Call completed.
10) Creating Packages:-
I) Create Package Specification:-
CREATE OR REPLACE PACKAGE emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
FUNCTION num_above_salary (emp_id NUMBER) RETURN VARCHAR2;
END emp_actions;
/
II) Create Package Body:-
CREATE OR REPLACE PACKAGE body emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER) is
begin
insert into emp (empno) values (employee_id);
commit;
end;
PROCEDURE fire_employee (emp_id NUMBER) is
begin
delete from emp where empno = emp_id;
commit;
end;
FUNCTION num_above_salary (emp_id NUMBER) RETURN varchar2 is
emp_sal varchar2(30);
BEGIN
SELECT ename INTO emp_sal FROM emp WHERE empno = emp_id;
RETURN emp_sal;
END num_above_salary;
END emp_actions;
/
To Exec:-
a) SQL> exec emp_actions.hire_employee(100);
b) SQL> exec emp_actions.fire_employee(100);
C) SQL> select emp_actions.num_above_salary(10) from dual;
EMP_ACTIONS.NUM_ABOVE_SALARY(10)
--------------------------------
Ahamed
D) CALL emp_actions.hire_employee(300);
E)
BEGIN
DBMS_OUTPUT.PUT_LINE( 'NAME of employees whose emp id 120 is: ' ||
TO_CHAR(emp_actions.num_above_salary(120)));
emp_actions.fire_employee(300);
END;
/
Creation of triggers:-
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/
===== Still to add more===================================
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language
that offers the following advantages:
■ Tight Integration with SQL
■ Better Performance
■ Higher Productivity
■ Full Portability
■ Tight Security
■ Access to Pre-defined Packages
■ Support for Object-Oriented Programming
■ Support for Developing Web Applications and Pages
Block Structure:-
[DECLARE
-- declarations]
BEGIN
-- statements
[EXCEPTION
-- handlers]
END;
1) Declaring Variables:-
part_no NUMBER(6);
part_name VARCHAR2(20);
in_stock BOOLEAN;
part_price NUMBER(6,2);
raise_app_err exception;
emp_rec1 employees%ROWTYPE; -- The datatype of all columns of employees table.
eg:-
DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id FROM employees
WHERE employee_id = 120;
employee_rec c1%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
CLOSE C1;
END;
/
emp_rec2 employees.ename%TYPE; -- The datatype of ename column of employee table.
name varchar2(30);
anname name%type -- (anname datatype will be as per name datatype)
Collections:- (Similar to arrarys)
TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; -- Used for storing array values
comm_tab commissions;
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list ;
begin
staff := staff_list(100,200,300,400,500);
FOR i IN staff.FIRST..staff.LAST LOOP
SELECT last_name, first_name INTO lname, fname FROM employees
WHERE employees.employee_id = staff(i);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );
END LOOP;
END;
/
Record:- (Records are composite data structures whose fields can have different datatypes.)
TYPE meetin_typ IS RECORD(
date_held DATE,
duration timerec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));
met_ty meetin_typ
Different methods to assign arrays:-
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE varray_type IS VARRAY(5) OF INTEGER;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Shipping','Sales','Finance','Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 10; -- Just start assigning to elements
v3(7) := 100; -- Subscripts can be any integer values
v4(42) := 'Smith'; -- Just start assigning to elements
v4(54) := 'Jones'; -- Subscripts can be any integer values
v5('Canada') := 'North America'; -- Just start assigning to elements
v5('Greece') := 'Europe'; -- Subscripts can be string values
END;
/
2) Assigning values to variables:-
hourly_salary NUMBER := 22.50;
v_ename_first varchar2(30) := 'MANZOOR';
v_ename_last varchar2(30);
in_stock BOOLEAN := TRUE;
done BOOLEAN;
begin
comm_tab(5) := 20000 * 0.15;
select ename_last into v_ename_last from emp where empid = 10;
done := (counter > 100);
end;
blood_type char := 'O'; (OR) blood_type char default 'O';
cnt number not null := 100;
3) Declaring Constants
cons constant number := 10;
4) Processsing Queries with pl/sql
BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
',Last name = ' || someone.last_name);
END LOOP;
END;
/
5) IF Statement
Syntax:-
if condition then
stmt;
elseif condition then
stmt;
else
stmt;
end if;
6) Case Statement
Syntax:-
Case when condition then
stmt;
when condition then
stmt;
else
stmt;
end case;
7) Loop Statements:-
Syntax:-
Loop
stmt;
exit when condition;
End loop;
For Loop
Syntax:-
for i in 1..n loop
stmt;
end loop;
for i in (select * from emp) loop
stmt;
end loop;
While loop
Syntax:-
while condition loop
Stmt;
end loop;
8) Goto Statement (unconditional control transfer):-
Syntax:-
<
eg:
i:=0;
<
i := 1+1;
if i > 10 then
goto gothere;
else
goto comehere;
end if;
<
end;
9) Using a Block Label for Name Resolution
<
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
-- deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
-- OK, column and variable have different names
DELETE FROM employees2 WHERE last_name = main.last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
END;
/
9) Creating a Stored procedures:-
create or replace procedure get_emp_name (v_emp in number) as
ss varchar2(30);
begin
select ename into ss from emp where empno = v_emp;
dbms_output.put_line ('Name of the Employee is :' || ss);
exception
when no_data_found then
dbms_output.put_line ('There is no employ with emp id :' || v_emp);
end;
/
To Exec (1st method):-
declare
emp_id number;
begin
emp_id := 100;
get_emp_name(emp_id);
end;
/
To Exec (2nd method):-
SQL> var emp_id number;
SQL> exec :emp_id := 100;
SQL> call get_emp_name(:emp_id);
Name of the Employee is :Ahamed
Call completed.
10) Creating Packages:-
I) Create Package Specification:-
CREATE OR REPLACE PACKAGE emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
FUNCTION num_above_salary (emp_id NUMBER) RETURN VARCHAR2;
END emp_actions;
/
II) Create Package Body:-
CREATE OR REPLACE PACKAGE body emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER) is
begin
insert into emp (empno) values (employee_id);
commit;
end;
PROCEDURE fire_employee (emp_id NUMBER) is
begin
delete from emp where empno = emp_id;
commit;
end;
FUNCTION num_above_salary (emp_id NUMBER) RETURN varchar2 is
emp_sal varchar2(30);
BEGIN
SELECT ename INTO emp_sal FROM emp WHERE empno = emp_id;
RETURN emp_sal;
END num_above_salary;
END emp_actions;
/
To Exec:-
a) SQL> exec emp_actions.hire_employee(100);
b) SQL> exec emp_actions.fire_employee(100);
C) SQL> select emp_actions.num_above_salary(10) from dual;
EMP_ACTIONS.NUM_ABOVE_SALARY(10)
--------------------------------
Ahamed
D) CALL emp_actions.hire_employee(300);
E)
BEGIN
DBMS_OUTPUT.PUT_LINE( 'NAME of employees whose emp id 120 is: ' ||
TO_CHAR(emp_actions.num_above_salary(120)));
emp_actions.fire_employee(300);
END;
/
Creation of triggers:-
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/
===== Still to add more===================================
Sunday, September 11, 2011
11g Dataguard Features
11g Dataguard Features:-
A. Setup dataguard using active database.
Normally when setting up datagurad using rman we need to copy the backup files to the
destination server and then we need to start using the duplicate command, but in
11g we dont need to copy the backup files to the destination server when creating
the standby databse using active database method.
Steps:-
1. Configure th listner.ora parameter to do a static registration of the
Standby database.
Eg:-
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gstdby.manzoor.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orclstdb )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
2. Configure the tnsnames.ora file in target and the destination server accordingly.
3. Take the backup of the init parameter file from the target and modify it accordinly and transfer it to the destination server and modify it accordingly as per the destinaton database requirements.
4. Create the password file with same password as the target password.
5. startup the instance in nomount state in the destination server and exit the session.
6. In target connect to the target and the auxiliary database and execute the duplicate command.
[oracle@rhel11g u01]$ rman target sys/admin@orcl11g auxiliary sys/admin@orclstdb
Recovery Manager: Release 11.1.0.6.0 - Production on Mon Sep 12 09:45:07 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL11G (DBID=867589313)
connected to auxiliary database: ORCL11G (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 12-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=170 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=154 device type=DISK
contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl11g' auxiliary format
'/u01/app/oracle/product/10.2.0/db_1/dbs/orapworclstdb' ;
}
executing Memory Script
Starting backup at 12-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=134 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=130 device type=DISK
Finished backup at 12-SEP-11
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control02.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control03.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl11g.f tag=TAG20110912T094518 RECID=2 STAMP=761651119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-SEP-11
Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11
Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orclstdb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orclstdb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orclstdb/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orclstdb/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orclstdb/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orclstdb/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orclstdb/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orclstdb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl11g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl11g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl11g/example01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/example01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl11g/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl11g/users01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/users01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:08:16
output file name=/u01/app/oracle/oradata/orclstdb/system01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:26
Finished backup at 12-SEP-11
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/example01.dbf
Finished Duplicate Db at 12-SEP-11
RMAN> exit;
7. Now do a log switch in the primary and check whether the logs are applied correctly.
B) Active Datagurad:-
Prior to 11g the logs will not be applied in physical standby database if it is open in readonly mode, but from 11g the logs will get applied even when the standby database is in read only mode.
Eg:-
SQL> select sequence#, archived, applied from V$archived_log;
SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES
22 rows selected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Now do some logswith in the priarmy database....and the check whether the logs are applied in the standby databse.
SQL> select sequence#, archived, applied from V$archived_log;
SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES
30 YES YES
31 YES YES
24 rows selected.
We can see that the logs are getting applied even when the physical standby database
in the read only mode.
C) Snap shot standby:-
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
stop the mrp process
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database convert to snapshot standby;
Database altered.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
SQL> select flashback_on from V$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
--- Do any transaction as per requirement.........
SQL> create user layana identified by fathima;
User created.
---
---
once the transactions are completed shut down the database and convert that to
physical standby database, once you shutdown the database all the transaction
will be lost.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
--Once the above commands executes the database will be automatically dismounted and instance
will be in start state.
SQL> select status from V$instance;
STATUS
------------
STARTED
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
A. Setup dataguard using active database.
Normally when setting up datagurad using rman we need to copy the backup files to the
destination server and then we need to start using the duplicate command, but in
11g we dont need to copy the backup files to the destination server when creating
the standby databse using active database method.
Steps:-
1. Configure th listner.ora parameter to do a static registration of the
Standby database.
Eg:-
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gstdby.manzoor.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orclstdb )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
2. Configure the tnsnames.ora file in target and the destination server accordingly.
3. Take the backup of the init parameter file from the target and modify it accordinly and transfer it to the destination server and modify it accordingly as per the destinaton database requirements.
4. Create the password file with same password as the target password.
5. startup the instance in nomount state in the destination server and exit the session.
6. In target connect to the target and the auxiliary database and execute the duplicate command.
[oracle@rhel11g u01]$ rman target sys/admin@orcl11g auxiliary sys/admin@orclstdb
Recovery Manager: Release 11.1.0.6.0 - Production on Mon Sep 12 09:45:07 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL11G (DBID=867589313)
connected to auxiliary database: ORCL11G (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 12-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=170 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=154 device type=DISK
contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl11g' auxiliary format
'/u01/app/oracle/product/10.2.0/db_1/dbs/orapworclstdb' ;
}
executing Memory Script
Starting backup at 12-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=134 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=130 device type=DISK
Finished backup at 12-SEP-11
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control02.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/orclstdb/control03.ctl' from
'/u01/app/oracle/oradata/orclstdb/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl11g.f tag=TAG20110912T094518 RECID=2 STAMP=761651119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-SEP-11
Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11
Starting restore at 12-SEP-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_DISK_2: no AUTOBACKUP in 7 days found
channel ORA_DISK_3: no AUTOBACKUP in 7 days found
channel clone_default: copied control file copy
Finished restore at 12-SEP-11
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orclstdb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orclstdb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orclstdb/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/orclstdb/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/orclstdb/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/orclstdb/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/orclstdb/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/orclstdb/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/orclstdb/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orclstdb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl11g/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl11g/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl11g/example01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/example01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl11g/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl11g/users01.dbf
output file name=/u01/app/oracle/oradata/orclstdb/users01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:08:16
output file name=/u01/app/oracle/oradata/orclstdb/system01.dbf tag=TAG20110912T094549 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:26
Finished backup at 12-SEP-11
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=761651617 file name=/u01/app/oracle/oradata/orclstdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=761651618 file name=/u01/app/oracle/oradata/orclstdb/example01.dbf
Finished Duplicate Db at 12-SEP-11
RMAN> exit;
7. Now do a log switch in the primary and check whether the logs are applied correctly.
B) Active Datagurad:-
Prior to 11g the logs will not be applied in physical standby database if it is open in readonly mode, but from 11g the logs will get applied even when the standby database is in read only mode.
Eg:-
SQL> select sequence#, archived, applied from V$archived_log;
SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES
22 rows selected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Now do some logswith in the priarmy database....and the check whether the logs are applied in the standby databse.
SQL> select sequence#, archived, applied from V$archived_log;
SEQUENCE# ARC APP
---------- --- ---
12 YES YES
8 YES YES
11 YES YES
9 YES YES
10 YES YES
13 YES YES
14 YES YES
15 YES YES
18 YES YES
17 YES YES
16 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
24 YES YES
25 YES YES
26 YES YES
27 YES YES
28 YES YES
29 YES YES
30 YES YES
31 YES YES
24 rows selected.
We can see that the logs are getting applied even when the physical standby database
in the read only mode.
C) Snap shot standby:-
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
stop the mrp process
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database convert to snapshot standby;
Database altered.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
SQL> select flashback_on from V$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
--- Do any transaction as per requirement.........
SQL> create user layana identified by fathima;
User created.
---
---
once the transactions are completed shut down the database and convert that to
physical standby database, once you shutdown the database all the transaction
will be lost.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
--Once the above commands executes the database will be automatically dismounted and instance
will be in start state.
SQL> select status from V$instance;
STATUS
------------
STARTED
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 360712320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Monday, August 29, 2011
Built in Functions in Oracle
Error
SQLCODE
SQLERRM
Number
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
REMAIN
DER
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC
Character
ASCII
ASCIISTR
CHR
COMPOSE
CONCAT
DECOMPOSE
INITCAP
INSTR
INSTR2
INSTR4
INSTRB
INSTRC
LENGTH
LENGTH2
LENGTH4
LENGTHB
LENGTHC
LOWER
LPAD
LTRIM
NCHR
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTR2
SUBSTR4
SUBSTRB
SUBSTRC
TRANSLATE
TRIM
UNISTR
UPPER
Conversion
CHARTOROWID
CONVERT
HEXTORAW
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
TO_BINARY_DOUBLE
TO_BLOB
TO_BINARY_FLOAT
TO_CHAR
TO_CLOB
TO_DATE
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
Date
ADD_MONTHS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND
SCN_TO_TIMESTAMP
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TIMESTAMP_TO_SCN
TO_DSINTERVAL
TO_TIME
TO_TIME_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC
TZ_OFFSET
Obj Ref
DEREF
REF
TREAT
VALUE
Misc
BFILENAME
COALESCE
DECODE
DUMP
EMPTY_BLOB
EMPTY_CLOB
GREATEST
LEAST
NANVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
SYS_CONTEXT
SYS_GUID
UID
USER
USERENV
VSIZE
SQLCODE
SQLERRM
Number
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
REMAIN
DER
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC
Character
ASCII
ASCIISTR
CHR
COMPOSE
CONCAT
DECOMPOSE
INITCAP
INSTR
INSTR2
INSTR4
INSTRB
INSTRC
LENGTH
LENGTH2
LENGTH4
LENGTHB
LENGTHC
LOWER
LPAD
LTRIM
NCHR
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTR2
SUBSTR4
SUBSTRB
SUBSTRC
TRANSLATE
TRIM
UNISTR
UPPER
Conversion
CHARTOROWID
CONVERT
HEXTORAW
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
TO_BINARY_DOUBLE
TO_BLOB
TO_BINARY_FLOAT
TO_CHAR
TO_CLOB
TO_DATE
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
Date
ADD_MONTHS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND
SCN_TO_TIMESTAMP
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TIMESTAMP_TO_SCN
TO_DSINTERVAL
TO_TIME
TO_TIME_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC
TZ_OFFSET
Obj Ref
DEREF
REF
TREAT
VALUE
Misc
BFILENAME
COALESCE
DECODE
DUMP
EMPTY_BLOB
EMPTY_CLOB
GREATEST
LEAST
NANVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
SYS_CONTEXT
SYS_GUID
UID
USER
USERENV
VSIZE
Subscribe to:
Posts (Atom)