My confrontations with oracle

November 4, 2008

Index

Filed under: oracle — John Jacob @ 11:43 pm

June 29, 2009

April 6, 2009

Recovery of UNDO tablespace in a Non Archive Log mode Database

Every transaction stores the post modification data in the undo tablespace for a rollback or read consistency or flash back query or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. If the database is in Archivelog mode, the recovery of an Undo tablespace is same as that of any Non-System tablespace,but how to recover if the database is in Non Archivelog Mode ?

While shutdown
++++++++++++++
This will work only if the database is shutdown properly(shutdown immediate).
If the database was aborted, oracle would do the ‘instance recovery’ whilst next startup and would terminate if the undo tablespace isn’t present.

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/oradata/burp/undotbs01.dbf'

Since the database in Non Archive Log mode, we cannot open the database by taking the datafile offline using ‘alter database datafile 2 offline’,but we need to use ‘…OFFLINE FOR DROP‘. This will mark the datafile for subsequent dropping.A datafile once marked ‘OFFLINE FOR DROP’ can never be brought online.

SQL>alter database datafile 2 offline for drop;
SQL>alter database open;

Now we need to create another undo tablespace for the transactions to use. Once the new undo tablespace is created,shutdown the database and edit the init.ora file and change the parameter undo_tablepace=<new_undo_tablespace>

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;
SQL>shutdown immediate;

edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;

Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

While database is OPEN
+++++++++++++++++++

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;
SQL>shutdown immediate;

edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;

Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

January 28, 2009

No quota on USERS tablespace? Then no EXPDP.

Filed under: oracle, troubleshooting — John Jacob @ 3:23 pm

When i tried to export objects from a schema using EXPDP using the userid and passwd as that of the schema name, it failed.

my [DR1]$ expdp SCHEMAS=dprd PARFILE=exp_include.par
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 28 January, 2009 14:47:15
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: DPRD
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "DPRD.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'

Well, the error message clearly says “no privileges on tablespace ‘USERS’“.

Yes, oracle will create some temporary tables whilst the expdp.So, no quota on users? then no expdp. Then i granted some quota on tablespace USERS to user DPRD.

SQL>alter user DPRD quota 10M on USERS;

User altered.

While the expdp was in process, i found the below objects created on tablespace USERS

SQL>select segment_name,segment_type,bytes from dba_segments where owner='DBKPRD' and tablespace_name='USERS';
SEGMENT_NAME                   SEGMENT_TYPE                        BYTES
------------------------------ ------------------------------ ----------
SYS_LOB0000154588C00039$$      LOBSEGMENT                          65536
SYS_MTABLE_000025BDC_IND_1     INDEX                              131072
SYS_C00119124                  INDEX                               65536
SYS_IL0000154588C00039$$       LOBINDEX                            65536
SYS_EXPORT_SCHEMA_01           TABLE                              131072
SQL>

Do we have any control on the tablespace name ?
yet to find it out :(

December 25, 2008

Setting up Oracle VM using iSCSI storage

Virtualization Technology isn’t new to the technical magazines, but the frevour at which companies bring out their virtualization products shows how HOT it has become at the data centers at this point when the companies vie each other to go GREEN.

Oracle has brought out the Oracle Virtualization Product using the Xen hypervisor,  an Open Source s/w and widely used in all Linux Distributions for virtualization. This product of Oracle comes with an Oracle VM Server and an Oracle VM Manager, a Java based interface to manage the Virtualization.

I have created my TEST environment using 4 desktops and below is how i went about.

ora_vm_arch

As shown in the above picture i used 4 machines.
Machine Details
————————
Manager
——-
CPU        -Intel P4 3Ghz [1 core]
RAM      -500 MB
HDD       -40 GB [SCSI]
OS           -Oracle Enterprise Linux 5.2

Storage
——-
CPU        -Intel P4 3Ghz [1 core]
RAM      -1 GB
HDD       -80 GB [SCSI]
OS           -Openfiler 2.1

VMS1
——-
CPU        -Intel P4 3Ghz [2 core]
RAM      -2 GB
HDD       -80 GB [SCSI]
OS[Dom]-Oracle VM Server 2.1.2

VMS2
——-
CPU        -Intel P4 3Ghz [1 core]
RAM        -2 GB
HDD        -80 GB [SCSI]
OS[Dom0]-Oracle VM Server 2.1.2

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Files to download
——————
——–
Oracle Enterprise Linux 5.2(32bit)         – http://edelivery.oracle.com/linux
Oracle VM Server 2.1.2(32bit)                  – http://edelivery.oracle.com/linux
Oracle VM Manger 2.1.2(32bit)                – http://edelivery.oracle.com/linux
Oracle EL 5.2 Template(32bit)                  – http://edelivery.oracle.com/linux
Oracle RDBMS 10g(Linux 32bit)              – http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html
VNC Viewer Plugin for VM Manager(32bit)    – http://oss.oracle.com/oraclevm/manager/RPMS/

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Setting up Manager
——————
———-
Install OEL 5.2

-Firewall    =No
-Selinux    =Disabled
-IP Address    =192.168.20.99/255.255.255.0
-Hostname    =Manger
-Root Pswd    =redhat

Install Oracle VM Manager 2.1.2
http://download.oracle.com/docs/cd/E11081_01/doc/doc.21/e10902/ovmig.htm#BHCDACIH

Install the downloaded VNC viewer plugin. This plugin is required if we want to get the console of guests’ from VM Manager.
[Manager:root]$rpm -ivh ovm-console-1.0.0-2.i386.rpm

Stop the iptables and ip6tables services

[Manager:root]$service iptables stop
[Manager:root]$service ip6tables stop

Make sure that it doesn’t start in the next reboot

[Manager:root]$chkconfig –levels 345 iptables off
[Manager:root]$chkconfig –levels 345 ip6tables off

Add the below to the /etc/hosts
192.168.20.98    storage
192.168.20.99    manager
192.168.20.100    vms1
192.168.20.101  vms2
192.168.20.102  guest1

Setting up Storage
————————-

Install Openfiler 2.1

-Select manual partition
-Delete all existing partitions(if they do exist)
- /                =20 GB*
SWAP        =2 GB
Keep the rest as Free Space, this free space can be used as the shared iSCSI Storage.
-Firewall    =No
-Selinux     =No
-IP address    =192.68.20.98/255.255.255.0
-Hostname    =Storage

* Even 1 Gb would do for “/” [it used only 500 MB for my installation] and less for SWAP

Setting up VMS1
—————–
——
Install Oracle VM Server 2.1.2

[Follow the documentation http://download.oracle.com/docs/cd/E11081_01/doc/doc.21/e10899.pdf]
-Go for the default options
-IP Address        =192.168.20.100/255.255.255.0
-Hostname          =VMS1
-Agent Password    =vmagent
-Root Password      =redhat

Stop the iptables and ip6tables

[VMS1:root]$service iptables stop
[VMS1:root]$service ip6tables stop

Make sure that it doesn’t start in the next reboot

[VMS1:root]$chkconfig –levels 345 iptables off
[VMS1:root]$chkconfig –levels 345 ip6tables off

Setting up VMS2
—————–
——-
Install Oracle VM Server 2.1.2

[Follow the documentation http://download.oracle.com/docs/cd/E11081_01/doc/doc.21/e10899.pdf]
-Go for the default options
-IP Address        =192.168.20.101/255.255.255.0
-Hostname         =VMS2
-Agent Password    =vmagent
-Root Password      =redhat

Stop the iptables and ip6tables

[VMS2:root]$service iptables stop
[VMS2:root]$service ip6tables stop

Make sure that it doesn’t start in the next reboot

[VMS2:root]$chkconfig –levels 345 iptables off
[VMS2:root]$chkconfig –levels 345 ip6tables off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Configure iSCSI Storage
———————–
———–
[Check Jeffery Hunter's RAC with iSCSI for more detailed explanation on setting up shared iSCSI storage and FAQ's and debugging]

From the browser in Manager:
https://192.168.20.98:446/
Userid          =openfiler
Password    =password

Go to SERVICES/ENABLE-DISABLE tab

op2

Click “Enable” for iSCSI target and the status would change from Disabled to Enabled.

On Storage
[STORAGE]$service iscsi-target status
=>it should be running

Go to GENERAL/LOCAL NETWORKS tab
op4

Add the network, whose machines would mount the storage
Name                  = Network 20
Network/Host= 192.168.20.0    => All our machines are in this network
Netmask            = 255.255.255.0
Type                    = Share
[UPDATE]

Go to VOLUMES/PHYSICAL STORAGE MGMT tab
op6

Here we can see the disk /dev/sda of 74.50 Gb with 2 partitions
[click VIEW]    /dev/sda1 for “/” and /dev/sda2 for “SWAP

Click /dev/sda under “Edit Disk” and Scroll Down to “Create a partition in /dev/sda” with the FREE SPACE in the disk
Cylinders 1 to 2107 of /dev/sda are used by / and SWAP of STORAGE

Mode                       =Primary
Partition Type     =Physical Volume
Startig Cylinder  =<let it be the default>
Ending Cylinder  =<let it be the default>

[CREATE]

op7

op8

:

Go to GENERAL tab

:

Go to VOLUMES/VOLUME GROUP MGMT tab
op9

Enter the Volume Group Name            =data
Select the physical volumes to add     =/dev/sda3

[ADD VOLUME GROUP]

:

Go to VOLUMES/CREATE NEW VOLUME
op10

Select the volume group    =data
Create Volume in data
Volume name        =data
Volume Desc         =data
Reqd Space            =<drag the slider to end>
Filesystem Type  =iSCSI

[CREATE]

Go to VOLUMES/LIST EXISTING VOLUMES

op11

Click “EDIT” for the volume “data” and
Scroll down to ‘iSCSI host access configuration for volume “data”
and select “Allow” for Access
[UPDATE]

op12

Make iSCSI clients available to clients
[STORAGE:root]$service iscsi-target restart

Make sure that the service ‘iscsi-target’ is started after restart
[STORAGE:root]$chkconfig –levels 345 iscsi-target on
[STORAGE:root]$chkconfig –list iscsi-target

Discovering Shared iSCSI on VMS1 and VMS2
——————————————–
——————–
[do it from both VMS1 and VMS2]
Discover the shared iSCSI using the ‘iscsiadm‘ utility that comes along with VM Server.
[http://download.oracle.com/docs/cd/E11081_01/doc/doc.21/e10898.pdf]

$iscsiadm -m discovery -t sendtargets -p <iscsi server>

[VMS1:root]$iscsiadm -m discovery -t sendtargets -p 192.168.20.98
192.168.20.98:3260,1 iqn.2006-01.com.openfiler:data.data

The discovered partitions will appear only after restarting the iscsi service
$cat /proc/partitions
$service iscsi restart
$cat /proc/partitions

[root@vms1 ~]# cat /proc/partitions
major minor  #blocks  name
8     0   78125000 sda
8     1     104391 sda1
8     2   23663745 sda2
8     3   50154930 sda3
8     4          1 sda4
8     5    3148708 sda5
8     6    1052226 sda6
[root@vms1 ~]# service iscsi restart
Stopping iSCSI daemon: /etc/init.d/iscsi: line 33:  3006 Killed         /etc/init.d/iscsid stop
iscsid dead but pid file exists                            [  OK  ]
Turning off network shutdown. Starting iSCSI daemon:       [  OK  ]
[  OK  ]
Setting up iSCSI targets: Login session [192.168.20.98:3260 iqn.2006-01.com.openfiler:data.data]
[  OK  ]
[root@vms1 ~]#
[root@vms1 ~]# cat /proc/partitions
major minor  #blocks  name
8     0   78125000 sda
8     1     104391 sda1
8     2   23663745 sda2
8     3   50154930 sda3
8     4          1 sda4
8     5    3148708 sda5
8     6    1052226 sda6
8    16   61177856 sdb               ===> the shared disk is discovered    
[root@vms1 ~]#

Create and Format the discoverd iSCSI device using OCFS2 file system
———————————————————————
——————————
[do it only from either VMS1 or VMS2]

Create partition on the shared iSCSI disk
[VMS1:root]$fdisk /dev/sdb
n -new partition
p -primary partition
w -write and quit

[root@vms1 ~]# fdisk /dev/sdb
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.The number of cylinders for this disk is set to 59744.
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)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): p
Disk /dev/sdb: 62.6 GB, 62646124544 bytes
64 heads, 32 sectors/track, 59744 cylinders
Units = cylinders of 2048 * 512 = 1048576 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-59744, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-59744, default 59744):
Using default value 59744

Command (m for help): p
Disk /dev/sdb: 62.6 GB, 62646124544 bytes
64 heads, 32 sectors/track, 59744 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       59744    61177840   83  Linux

Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@vms1 ~]#
[root@vms1 ~]# fdisk -l
Disk /dev/sda: 80.0 GB, 80000000000 bytes
255 heads, 63 sectors/track, 9726 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2            6781        9726    23663745   83  Linux
/dev/sda3              14        6257    50154930   83  Linux
/dev/sda4            6258        6780     4200997+   5  Extended
/dev/sda5            6258        6649     3148708+  83  Linux
/dev/sda6            6650        6780     1052226   82  Linux swap / Solaris
Partition table entries are not in disk order

Disk /dev/sdb: 62.6 GB, 62646124544 bytes
64 heads, 32 sectors/track, 59744 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       59744    61177840   83  Linux
[root@vms1 ~]#

Create the OCFS2 file system on the partition using mkfs utility
[VMS1:root]$mkfs.ocfs2 -b 4K -C 32K -N 4 -L data /dev/sdb1
[VMS1:root]$partprobe

[root@vms1 ocfs2]# mkfs.ocfs2 -b 4K -C 32K -N 4 -L data /dev/sdb1
mkfs.ocfs2 1.2.7
Overwriting existing ocfs2 partition.
Proceed (y/N): y
Filesystem label=data
Block size=4096 (bits=12)
Cluster size=32768 (bits=15)
Volume size=62646091776 (1911807 clusters) (15294456 blocks)
60 cluster groups (tail covers 8703 clusters, rest cover 32256 clusters)
Journal size=268435456
Initial number of node slots: 4
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Writing backup superblock: 3 block(s)
Formatting Journals: done
Writing lost+found: done
mkfs.ocfs2 successful
[root@vms1 ~]#

Configure the shared partition on VMS1 and VMS2
————————————————————————-
[do it on VMS1 and copy the file to VMS2]

Create the cluster.conf file under /etc/ocfs2
This cluster.conf file will have the details of the nodes that will be participating in a Cluster.
So in our case, the cluster name will be OCFS2 and the number of nodes will be 2 (VMS1 and VMS2). Later, should a new VM Server be added to this cluster, the details of it should be added to this cluster.conf.
All the machines(nodes) participating in a cluster should have the same cluster.conf entries.

[VMS1:root]$mkdir /etc/ocfs2
[VMS1:root]$cd /etc/ocfs2
[VMS1:root]$touch cluster.conf
<I had the below in my cluster.conf>

[root@vms1 ocfs2]# cat cluster.conf
node:
     ip_port         =7777
     ip_address      =192.168.20.100
     number          =1
     name            =vms1
     cluster         =ocfs2
node:
     ip_port         =7777
     ip_address      =192.168.20.101
     number          =2
     name            =vms2
     cluster         =ocfs2
cluster:
     node_count      =2
     name            =ocfs2
[root@vms1 ocfs2]#

Create /etc/ocfs2 dir on VMS2 and copy the cluster.conf from VMS1 to VMS2 .

Mounting the shared disk
—————————-
———
[do below on both VMS1 and VMS2]

[VMS1:root]$service o2cb status
[VMS1:root]$service o2cb load
[VMS1:root]$service o2cb online
[VMS1:root]$service o2cb configure =>go for the defaults>
[VMS1:root]$service o2cb start

<if any typo err in the cluster.conf it will throw error, in that case correct the typo error and offline and unload o2cb >
[VMS1:root]$sevice o2cb offline
[VMS1:root]$sevice o2cb unload
:
:

Make sure the service O2CB is started after reboot
[VMS1:root]$chkconfig –levels 345 o2cb on
[VMS1:root]$chkconfig –list o2cb

Try to mount the shared iSCSI disk manually to check:
$mount /dev/sdb1 /OVS -t ocfs2
Unmount it
$umount /dev/sdb1

For the High Availability to work,
The shared disk must be mounted under /OVS and should be in /etc/ovs/repositories instead of mounting by adding details in fstab.
Make entries in the /etc/ovs/repositories using the /usr/lib/ovs-makerepo command
[http://download.oracle.com/docs/cd/E11081_01/doc/doc.21/e10898/ha.htm#CHDEFABI]

$/usr/lib/ovs/ovs-makerepo /dev/sdb1 C “cluster root”
$/usr/lib/ovs/ovs-cluster-check –alter-fstab

<this should be done on all the vm servers(vms1 and vms2)>

[root@vms1 OVS]# /usr/lib/ovs/ovs-makerepo /dev/sdb1 C "cluster root"
Initializing NEW repository /dev/sdb1
Updating local repository list.
ovs-makerepo complete
[root@vms1 OVS]#
[root@vms1 OVS]# cat /etc/ovs/repositories
# This configuration file was generated by ovs-makerepo
# DO NOT EDIT
@211419C38BD34CE4A0B9083A47F7AEBF /dev/sdb1
[root@vms1 OVS]#
[root@vms1 OVS]# /usr/lib/ovs/ovs-cluster-check
Need to remove /OVS mount from /etc/fstab, but --alter-fstab not specified.
[root@vms1 OVS]# /usr/lib/ovs/ovs-cluster-check --master --alter-fstab
Backing up original /etc/fstab to /tmp/fstab.BJZxu20292
Removing /OVS mounts in /etc/fstab
O2CB cluster ocfs2 already online
Cluster setup complete.
[root@vms1 OVS]#

REBOOT both vms1 and vms2 and confirm that the shared disk is mounted under /OVS on both using df -h command.

Now we have,
Storage ready with iSCSI
Manager with VM Manager
VMS1 with VM Server and shared iSCSI storage mounted under /OVS
VMS2 with VM Server and shared iSCSI storage mounted under /OVS

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Create/Add Server pool and Servers from Oracle VM Manager
———————————————————-—————————–
On MANAGER:

Server Pools -> Create Pool ->
Create the pool Pool1 with VMS1 as Server Master, Utility Server (provide root/<root_passwd>) and VM Server
Enable HA

Servers -> Add Server
Select pool ‘Pool1′ and add the server VMS2 as VM Server.

create_server_pool_2

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Creating the Guest OS
——————————–

Create VM Guest is divided into 4 sections:
1]Download and register the template
2]Create the VM guest OS
3]Access VM guest OS
4]Configure Hostname and IP address for Guest OS


1] Download and register the template

Download “Oracle Enterprise Linux 5 Update 2 template -PV Small x86(32 bit).zip” from http://edelivery.oracle.com/linux
Two templates are available for download.
Small – 4Gb
Large – 10gb
The default root password is ovsroot.

Oracle VM Manger can download/register the template from HTTP/FTP source or from a Machine in the Server Pool or Linux P2V Import.

We will download the file and copy it to either VMS1 or VMS2, which are members of the pool ‘Pool1‘.

Create the seed_pool directory to hold the templates so that the VM Manger can detect and register the template. The seed_pool directory should be directly under /OVS. Unzip and untar the file in seed_pool and then import and register the template from VM Manager.

[VMS1:root]mkdir /OVS/seed_pool
[VMS1:root]mv “Oracle Enterprise Linux 5 Update 2 template -PV Small x86(32 bit).zip” /OVS/seed_pool/
[VMS1:root]unzip “Oracle Enterprise Linux 5 Update 2 template -PV Small x86(32 bit).zip”
[VMS1:root]tar -xzvf OVM_EL5U2_X86_PVM_10GB.tgz
[VMS1:root]

Once the OVM_EL5U2_X86_PVM_4GB.tgz is untarred, the template will be available in OVM_EL5U2_X86_PVM_4GB directory. After extracting the tar file the size of the OVM_EL5U2_X86_PVM_4GB.tgz will be around 6.5GB, so make sure that you have enough space in seed_pool before extracting the tar file.

To Discover and Register the template:

VM Manager -> Resources -> Virtual Machine Templates -> Select from Server Pool -> [Next]

template_registration_1

[APPROVE]

2]Create the VM

VM Manger -> Virtual Machines -> [Create Virtual Machine]

create_vm_1_1

[NEXT]

The Preferred Server = Auto/Manual . I chose MANUAL and selected VMS1 as the preferred server

create_vm_2_2

[NEXT]

Select the OEL 5.2 template. Select the one we have registered now (4Gb).

create_vm_3_3

[NEXT]

Enter the Virtual Machine Name and the Console password and enable HA too. The console password is needed to access the guest OS through vncviewer or VM Manger Console option. This password will be in vm.cfg. (Console Password= oracle)

create_vm_4_4

[NEXT]

create_vm_5_5

[CONFIRM]

create_vm_6_6

[CREATE VIRTUAL MACHINE]

After the creation the GUEST2 will be in ‘Powered OFF’ state.
There will be a direcotry created under /OVS/running_pool/ for this Paravitualized Guest.
cd /OVS/running_pool/52_guest2/

Actually what happens is that the manager will make a copy of the template in the seed_pool to running_pool. So the drive should have a minimum of around 6.5 GB of space, since we use the ’small’ template. So you can monitor this activity from the ovs_operation.log of the utility server, in our case its VMS1.

[VMS1:root]$tail -f /var/log/ovs_agent/ovs_operation.log

Also to monitor the ‘cp’ status.
[VMS1:root]$ cd /OVS/running_pool
[VMS1:root]$du -sh */
and monitor the disk space used

create_vm_7_7

As mentioned after the creation the Virtual Machine will be in “Powered OFF” state.
The directory /OVS/running_pool/<vm name>/, will contain the system.img file and the vm.cfg for this Guest2. The vm.cfg should be modified for the guest2.

I modified my vm.cfg to

[VMS1:root]$cd /OVS/running_pool/52_guest2/
[VMS1:root]$cat vm.cfg
bootloader = '/usr/bin/pygrub'
disk = ['file:/OVS/running_pool/52_guest2/system.img,hda,w']
maxmem = 600
memory = 500
name = '52_guest2'
on_crash = 'restart'
on_reboot = 'restart'
uuid = 'cfd16fd1-1c7f-691a-51b9-2c5cbcc714a9'
vcpus = 1
vfb = ['type=vnc,vncunused=1,vnclisten=0.0.0.0,vncpasswd=oracle']
vif = ['bridge=xenbr0,mac=00:16:3E:5C:1C:F6,type=netfront']
vif_other_config = []

VM Manager-> Select Guest2 and “Power On”

create_vm_8_8

The status of the guest os ’start’ operation can be checked from the log file (check /var/log/ovs-agent/ovs_operation.log for success in starting or not ) on the VM server where the guest is started.

"2008-12-18 10:59:18" INFO=> xen_start_vm: success. vm('/OVS/running_pool/12_guest1')
"2008-12-18 10:59:18" INFO=> start_vm: vm('/OVS/running_pool/12_guest1') on srv('192.168.20.100') => success
"2008-12-18 10:59:18" INFO=> start_vm: success. vm('/OVS/running_pool/12_guest1') ip=''

3]Access VM Guest OS

Once the Guest2 is running, we can connect to it using the

(i)The ‘console’ option from VM Manager provided ovm-console-1.0.0-2.i386.rpm is installed on the Manager server.
Select the Guest2 -> Console -> ‘provide the vncpassword’
If you have forgot the vncpassword, its mentioned in /OVS/running_pool/<guest_name>/vm.cfg

(ii)vncviewer utility
[Manager:root]$vncviewer <vnc_server>:<port>

[Manager:root]$vncviewer 192.168.20.100:5900

The vncserver is the virtual server(VMS1 or VMS2) hosting the guestOS.
The first guest will use port 5900, the second will use 5901 and so on. Provide the VNC_Password when asked.

(iii)xm console <domain_name>
[VMS1:root]$xm console 52_guest2
Login: root
Passwd: ovsroot
=> the default root password for root in OEL Template is ovsroot

4] Configure Hostname and IP address for Guest OS

By deault the guestOS from template doesn’t have any IP address or hostname. We need to edit 3 files to set it.

(i)/etc/sysconfig/network
HOSTNAME=guest2
NETWORKING=yes
..

(ii)/etc/hosts
<add hostname and ip address>
192.168.20.101        guest2

(iii)/etc/sysconfig/network-scripts/ifcfg-eth0
HWADDR should be the same as the MAC in vm.cfg of VMSERVER(in /OVS/running_pool/<vmserver_name>/vm.cfg)
IPADDR should be the same as in the /etc/hosts file

[GUEST2:root]$cat /etc/sysconfig/network-scripts/ifcfg-eth0
# Intel Corporation 82540EM Gigabit Ethernet Controller
DEVICE=eth0
BOOTPROTO=none
BROADCAST=192.168.20.255
HWADDR=00:08:74:FC:6F:06
IPADDR=192.168.20.99
NETMASK=255.255.255.0
NETWORK=192.168.20.0
ONBOOT=yes
TYPE=Ethernet
USERCTL=no
IPV6INIT=no
PEERDNS=yes

Finally restart the network service

$service network restart

Log files
————-
VM Agent
/opt/ovs-agent-2.2/logs/ovs_trace.log
service ovs-agent status/start/stop/restart
VM Server
/var/log/xen/
var/log/ovs-agent/ovs_operation.log (on utility server)

Troubleshooting
————————

(i) If for some reason the shared disk is not mounted:

1]check for o2cb status (unload,load and online)
2]cat /etc/partitions        => check whether the partition is discovered ?
3]service iscsi restart
4]cat /etc/partitions        => check whether the partition is discovered ?
5]/usr/lib/ovs-cluster-check => OK ?
6]/etc/init.d/ovsrepositories start

(ii) Installing Oracle

The most of the kernel values are already configured in the template. The template OS doesn’t have X server for the GUI installation of Oracle RDBMS.

To install oracle using GUI connect using ssh with X flag

[MANAGER:root]ssh -X oracle@guest1
./runInstaller.sh

(iii)Mounting CD ROM in Guest OS

Check this site http://www.option-c.com/xwiki/Xen_CDROM_Support

(iv)Uninstall VM Manager

/opt/ovs-manager-2.1/bin/runInstaller
<all files pertaining to ovm manager will be removed including the XE database>

(v)Registering existing VM guests in VM manager

Resources-> Virtual Machine Images-> Import -> Select from server pool(discover and register)
The system.img and vm.cfg file are supposed to be under /OVS/running_pool/<guest_name>/

References
http://download.oracle.com/docs/cd/E11081_01/welcome.html
http://forums.oracle.com
http://www.oracle.com/technology/pub/articles/hunter_rac10gr2_iscsi.html

December 4, 2008

How to identify the type(32 or 64bit) of software installed ?

Filed under: oracle — John Jacob @ 11:25 am
Tags: ,

One of the first thing that we should consider before any patching is to identify the type of software installed.Upgrading a 32bit intallation using a patch for 64bit would make your day hell.

Well, for Oracle RDBMS one could identify it from the messages while executing sqlplus uid/passwd from the oracle db home.

[]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 4 11:02:28 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

But how about Oracle Agent, Oracle HTTP Server, Oracle Cluster etc.
The software type is mentioned in every ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml. I believe this file is available in almost all oracle products(i have checked for rdbms,cluster,agent,client,http server,Grid Control(oms)).

For 64 bit software installations on Solaris SPARC 64bit OS it will be

 <ARU_PLATFORM_INFO>
      <ARU_ID>23</ARU_ID>
      <ARU_ID_DESCRIPTION>Solaris Operating System (SPARC 64-bit)
                </ARU_ID_DESCRIPTION>
   </ARU_PLATFORM_INFO>

For 32 bit software installations on Solaris SPARC 64bit OS it will be

   <ARU_PLATFORM_INFO>
                <ARU_ID>453</ARU_ID>
                <ARU_ID_DESCRIPTION>Solaris Operating System (SPARC 32-bit)
                </ARU_ID_DESCRIPTION>
    </ARU_PLATFORM_INFO>

For 32 bit installations on Win2k 32bit OS it will be

 <ARU_PLATFORM_INFO>
               <ARU_ID>912</ARU_ID>
               <ARU_ID_DESCRIPTION>Microsoft Windows (32-bit)
               </ARU_ID_DESCRIPTION>
  </ARU_PLATFORM_INFO>

For 32 bit installations on CentOS 32bit OS it will be

 <ARU_PLATFORM_INFO>
              <ARU_ID>46</ARU_ID>
              <ARU_ID_DESCRIPTION>Linux x86
              </ARU_ID_DESCRIPTION>
  </ARU_PLATFORM_INFO>

On Solaris SPARC environments ARU_ID =23 is for 64bit and 453 is for 32bit software installations.
I ‘am not sure of other environments, may be some one can update me :D .

Recover from loss of Active redo log file

Filed under: Oracle Backup and Recovery, oracle — John Jacob @ 11:15 am
Tags: , , ,

LGWR,logwriter writes the redo data from the log buffer cache to the redo log files when,
1] A transaction is commited
2] Every 3 Seconds
3] When redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be  unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it.
Oracle complains with error ORA-00313,ORA-00312,ORA-27037 once all members of the log file group goes missing.
The database halts unable to switch the log file.

1] Shutdown the database
2] Restore the datafiles and proceed for an incomplete recovery until the missing log sequence.
2] Mount the database
startup mount;
3] Check the status of the missing log file group from v$log view.
select group#,sequence#,bytes,first_change#,to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim,status from v$log;
5] If STATUS = ACTIVE and ARCHIVED = NO
recover database until time ‘yyyy-mm-dd:hh24:mi:ss’;
* ’ss’ in the seconds should be 1 second less than the first_time of the missing redo log file.
6] Open the database with resetlogs option
7] Take a solid backup

===ARENA===

Mon Oct 27 15:04:18 2008
Errors in file /u01/app/oracle/admin/burp/bdump/burp_arc0_5826.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

I did shutdown the DB and restored the *.dbf files.

cp -rp /u01/burp/backup u01/burp/oradata/

Startup mount;

SQL> select group#,sequence#,bytes,first_change#,to_char(first_time,'DD-MM-YY hh24:mi:ss') tim,status from v$log;
    GROUP#  SEQUENCE#      BYTES FIRST_CHANGE# TIM               STATUS
---------- ---------- ---------- ------------- ----------------- ----------------
         1        148    5242880        206241 27-10-08 15:02:14 INACTIVE
         3        147    5242880        206196 27-10-08 15:02:13 INACTIVE
         2        149    5242880        206336 27-10-08 15:04:18 CURRENT
SQL> recover database until time '2008-10-27:15:02:13';
:
:
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>

All data after 27-10-08 15:02:13 will be lost.

November 4, 2008

Recovery from the loss of Inactive redo log file

Filed under: Oracle Backup and Recovery, oracle — John Jacob @ 11:33 pm
Tags: , , ,

LGWR,logwriter writes the redo data from the log buffer cache to the redo log files when,
1] A transaction is commited
2] Every 3 Seconds
3] When redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be  unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it.
Oracle complains with error ORA-00313,ORA-00312,ORA-27037 once all members of the log file group goes missing.
The database halts unable to switch the log file.

1] Shutdown the database
2] Mount the database
startup mount;
3] Check the status of the missing log file group from v$log view.
select * from v$log;
4]
a] If STATUS = INACTIVE and ARCHIVED = YES
alter database clear logfile group <grp number>;
b] If STATUS = INACTIVE and ARCHIVED = NO
STATUS = INACTIVE and ARCHIVED = NO
alter database clear UNARCHIVED logfile group <grp number>;
Since the unarchived log file is cleared, there will NOT be an archive log of that sequence.
Means, if log of seq#10 is cleared. Then archive log seq#10 will be not be created.
It will be like seq#8,seq#9,seq#11,seq#12. So, rollforward using archive logs will not be possible.
Also, all uncommitted transactions will be lost.
5] Open the database
alter database open;

6] Take a good backup

*The step 4(CLEAR) command will create the missing log files at the OS level.

===ARENA===

Below is the error in alert.log

Tue Oct 21 13:39:49 2008
Errors in file /u01/app/oracle/admin/burp/bdump/burp_arc0_23510.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Oct 21 13:39:49 2008

and i SHUTDOWN the Database.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'

The database cannot open as the online log file group 1 is missing. Now, we will check the status of the missing redo log group 1.


SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        145    5242880 NO  INACTIVE                206100 21-10-08 13:34:50
         2          1        146    5242880 NO  INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48
SQL>

The redo log group 1 is INACTIVE and NOT ARCHIVED, so we will follow step 4b.

SQL> alter database clear unarchived logfile group 1;

Database altered.

The above CLEAR command have created the file /u01/burp/oradata/redo01.log, which was missing.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1          0    5242880 YES UNUSED                  206100 21-10-08 13:34:50
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48

SQL> alter database open;

Database altered.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        148    5242880 NO  CURRENT                 206670 21-10-08 13:45:09
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 YES INACTIVE                206304 21-10-08 13:38:48

SQL> /

Excerpt from alert.log

===============

Tue Oct 21 13:44:12 2008
alter database clear unarchived logfile group 1
Tue Oct 21 13:44:12 2008
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 10/21/2008 13:36:59 (CHANGE 206188) CANNOT BE USED FOR RECOVERY.
Clearing online log 1 of thread 1 sequence number 145
Tue Oct 21 13:44:12 2008
Errors in file /u01/app/oracle/admin/burp/udump/burp_ora_23573.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Completed: alter database clear unarchived logfile group 1
Tue Oct 21 13:44:32 2008
Archiver process freed from errors. No longer stopped
Tue Oct 21 13:45:09 2008
alter database open
Tue Oct 21 13:45:09 2008
Thread 1 advanced to log sequence 148
Thread 1 opened at log sequence 148
  Current log# 1 seq# 148 mem# 0: /u01/burp/oradata/redo01.log
Successful open of redo thread 1

Since we have cleared the seq# 145, there will not be an archive log with sequence# 145. So complete rollforward using archive logs cannot be performed on backups taken prior to seq#145.

[oracle@spade burp]$ ls -ltr arch/
total 36104
-rw-r-----  1 oracle oinstall 5171712 Oct 21 13:30 1_140_667494933.arch
-rw-r-----  1 oracle oinstall 5237760 Oct 21 13:30 1_139_667494933.arch
-rw-r-----  1 oracle oinstall 5168128 Oct 21 13:30 1_141_667494933.arch
-rw-r-----  1 oracle oinstall 5168640 Oct 21 13:30 1_142_667494933.arch
-rw-r-----  1 oracle oinstall 5173248 Oct 21 13:30 1_143_667494933.arch
-rw-r-----  1 oracle oinstall 5169664 Oct 21 13:34 1_144_667494933.arch
-rw-r-----  1 oracle oinstall 5174784 Oct 21 13:44 1_146_667494933.arch
-rw-r-----  1 oracle oinstall  606208 Oct 21 13:45 1_147_667494933.arch
[oracle@spade burp]$
[oracle@spade burp]$

October 14, 2008

Recovery of non system datafile [ without backup ]

Filed under: Oracle Backup and Recovery — John Jacob @ 11:29 pm
Tags: , ,

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.

A non-system datafile can be recovered even if no backups are available for that datafile, provided all the archive logs since the creation of the datafile are available and the datafile is added after the control file is created. Datafiles created before the creation of the controlfile cannot be recoverd by this method.

[oracle@spade ~]$ sqlplus scott/tiger

SQL> insert into j_emp select * from j_emp;
insert into j_emp select * from j_emp
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/burp/oradata/booms.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

When checked the datafile booms.dbf is missing. But there is not any backup for this datafile
to perform open recovery

Steps for Database in OPEN mode
===============================
1] Take tablespace offline with immediate option;
2] SQL> alter database create datafile ‘<datafile_name>’;
3] Recover the tablespace
4] Online the tablespace

Steps for Database in SHUTDOWN
===============================
1] Mount the database
2] Offline the datafile [SQL>alter database datafile 5 offline;]
3] Open the database
4] SQL> alter database create datafile ‘<datafile_name>’;
5] Recover the tablespace
6] Online the tablespace
To restore the datafile to a different location:

SQL>alter database create datafile '<datafile_name_old>' as '<datafile_name_new>';

**examples down are for the database in ‘open’ mode.

===ARENA===

SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      READ WRITE             208496          206897

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
                                                                            0
SQL>

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5                                                   0          5 0                                              0          0
SQL>

Since the datafile isn’t available we need to take the tablespace offline with IMMEDIATE option

SQL> alter tablespace booms offline immediate;

Tablespace altered.

Now we will create the datafile

SQL> alter database create datafile '/u01/burp/oradata/booms.dbf';

Database altered

The datafile is created but the checkpoint is lagging behind other datafiles.

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 205403           BOOMS                         0        139

SQL> recover tablespace booms;

Excerpt from alert.log
==============

ALTER DATABASE RECOVER  tablespace booms
Wed Oct  8 11:38:39 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  tablespace booms  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_139_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
:
:
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_144_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_145_667494933.arch
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 146 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo02.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 147 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo03.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 148 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo01.log
Wed Oct  8 11:38:58 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

==============================

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208695           BOOMS                         0          0

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
/u01/burp/oradata/booms.dbf                        NO  NO              208695
SQL>

The archive logs until 148 were applied and booms.dbf has become consistent and can be ONLINED

SQL> alter tablespace booms online;

Tablespace altered.

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208973           BOOMS                         4        148

SQL>

October 10, 2008

Recovery of Non System datafile [ while the database is closed ]

Filed under: Oracle Backup and Recovery — John Jacob @ 12:20 am
Tags: , , ,

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.
2]A solid cold backup is available

We will be greeted with the ORA-01157 and ORA-01110 messages by Oracle, if any of the datafiles are missing.

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'

Steps
=====
1] Mount the database
2] Offline the datafile.
3] Open the database.
4] Restore the missing file the backup.
5] Recover the datafile.
6] Online the datafile.

ARENA
=======
Mount the database

SQL> startup mount;

Now, let’s fetch some details.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 ONLINE  ONLINE  FILE NOT FOUND                                                             0
SQL>
SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      MOUNTED                508539          495261

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
                                                                            0
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4                                                   0          4 0                                         0        0
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19

Since the database is in mounted state, we cannot ‘offline’ the tablespace, only offlining of datafile is possible.

SQL> alter database datafile 4 offline;

Database altered.

SQL>

Restore the missing datafile from the latest backup.

[spade]$cp -rp /u01/oradata/backup/users01.dbf /u01/oradata/burp/

Now, we will query to see the checkpoint of the restored file.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 OFFLINE OFFLINE                                                                       494404 20-SEP-08
SQL>

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
/u01/oradata/burp/users01.dbf                      YES NO              494404
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4 /u01/oradata/burp/users01.dbf                     3          0 494404           USERS                    0       12
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19

As per the above query, we need to apply archive logs(or redo logs) from sequence 12 to 19 to make it consistent.


SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- --------------------------------------------------
         1         12 20-SEP-08 /u01/oradata/arch/1_12_665715452.dbf
         1         13 01-OCT-08 /u01/oradata/arch/1_13_665715452.dbf
         1         14 01-OCT-08 /u01/oradata/arch/1_14_665715452.dbf
         1         15 01-OCT-08 /u01/oradata/arch/1_15_665715452.dbf
         1         16 01-OCT-08 /u01/oradata/arch/1_16_665715452.dbf

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         17          1 YES INACTIVE                495261
         3          1         19          1 NO  CURRENT                 508504
         2          1         18          1 YES INACTIVE                495416

Logs 12 to 16 are archived and 17,18 and 19 are still with the redo logs.


SQL> recover datafile 4;

Excerpt from alert.log
=================================================================
Tue Oct  7 01:21:11 2008
ALTER DATABASE RECOVER  datafile 4
Tue Oct  7 01:21:11 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 4  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_13_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_14_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:15 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:15 2008
Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Tue Oct  7 01:21:18 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Oct  7 01:21:18 2008
Media Recovery Log /u01/oradata/arch/1_16_665715452.dbf
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo01.log
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo02.log
Tue Oct  7 01:21:19 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 19 Reading mem 0
  Mem# 0 errs 0: /u01/oradata/burp/redo03.log
Tue Oct  7 01:21:19 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
===================================================

SQL> select * from v$recover_file;

no rows selected

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf                     NO  NO              508539
/u01/oradata/burp/undotbs01.dbf                    NO  NO              508539
/u01/oradata/burp/sysaux01.dbf                     NO  NO              508539
/u01/oradata/burp/users01.dbf                      NO  NO              508538
/u01/oradata/burp/example01.dbf                    NO  NO              508539

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;  2    3

FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME     STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
       1 /u01/oradata/burp/system01.dbf                    3          0 508539           SYSTEM                8192       19
       2 /u01/oradata/burp/undotbs01.dbf                   3          0 508539           UNDOTBS1                 0       19
       3 /u01/oradata/burp/sysaux01.dbf                    3          0 508539           SYSAUX                   0       19
       4 /u01/oradata/burp/users01.dbf                     3          0 508538           USERS                    0       19
       5 /u01/oradata/burp/example01.dbf                   3          0 508539           EXAMPLE                  0       19
SQL>

Since we have recovered the lost datafile completely, we can ONLINE the datafile and the users should be able to access data in that.


SQL>  select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 OFFLINE
         5 ONLINE

SQL> alter database datafile 4 online;

Database altered.

SQL>  select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
SQL>
Next Page »

Blog at WordPress.com.