Powered By Blogger

Monday, October 31, 2011

Transaction Slot on a Block

Transaction Slot on a Oracle Block :
Your process does some DML that's going to update a table, so, Oracle reserves a slot in the transaction header of a rollback segment. This is specified by the rollback number, slot, and wrap. This is what's stored in the transaction slot of the data block that's to be modified. So, now that transaction slot in the data block points to that placeholder in the rollback segment header. When rows in the data block are updated, then in the data block's row directory, the lock byte for that row is set to point to that transaction slot. So, the row directory entry (lock byte) for an updated row points to a specific transaction slot in the data block's transaction header, which in turn points to the rollback transaction slot, which in turn points to where the before images are actually recorded in the rollback segment.

Reference :

Very good one...

http://www.proligence.com/itl_waits_demystified.html


Regards,
Navaneeth

Tuesday, October 18, 2011

Using 10g Logminer on 9.2.0.7 archive files


in 9i production server

SQL> execute   DBMS_LOGMNR_D.build ('dictionary.ora','C:\BCH\VER2.0\EXPORT\MONTHLY',OPTIONs => DBMS_LOGMNR_D.store_in_flat_file);

PL/SQL procedure successfully completed.

in 10g test server using 10g logminer to analyse 9.2.0.7 archive logfiles

execute DBMS_LOGMNR.ADD_LOGFILE('D:\je_rda\JE_logmining\LOG_1_31443.ARC',options => dbms_logmnr.new);


execute DBMS_LOGMNR.START_LOGMNR(dictfilename =>'D:\je_rda\JE_logmining\dictionary.ora');

select * from v$logmnr_logs
select * from v$logmnr_logfile
desc v$logmnr_stats

select SEG_OWNER,SEG_NAME,TABLE_NAME,SEG_TYPE,ROW_ID,SESSION#,SERIAL#,USERNAME,SESSION_INFO,SQL_REDO,SQL_UNDO,AUDIT_SESSIONID from v$logmnr_contents

Reference :
Oracle 10g New Features of LogMiner [ID 249001.1]
LogMiner - Frequently Asked Questions (FAQ) [ID 174504.1]
Oracle9i LogMiner New Features [ID 148616.1]
LogMiner Utility Release 8.1.x - 10g [ID 291686.1];


Regards,
Navaneeth

Thursday, October 13, 2011

Sharing folder from RHEL5 machine to Windows machine

Configuring Samba service on RHEL5 machine

Create a share folder in RHEL5 machine which u want to share with Windows machine.

Check Linux machine for the samba packages whether installed or not.

[root@nav1 ~]# rpm -qa *samba*
sblim-cmpi-samba-0.5.2-31.el5_2.1
sblim-cmpi-samba-test-1-31.el5_2.1
samba-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
sblim-cmpi-samba-devel-1-31.el5_2.1
system-config-samba-1.2.41-3.el5
samba-common-3.0.33-3.7.el5
samba-swat-3.0.33-3.7.el5
You have new mail in /var/spool/mail/root

[root@nav1 ~]# service smb status
smbd (pid 3635 3068 3029) is running...
nmbd (pid 3032) is running...
[root@nav1 ~]#

[root@nav1 ~]# useradd samba -d /home/samba
[root@nav1 ~]# smbpasswd -a samba
New SMB password:
Retype new SMB password:
Added user samba.
[root@nav1 ~]# cat /etc/samba/smb
smb.conf  smbusers 
[root@nav1 ~]# cat /etc/samba/smb.conf

[windows]
        path = /u01/windows
        writeable = yes
;       browseable = yes
        valid users = az, nv, samba
[root@nav1 ~]#

After doing any changes to smb.conf file, please restart smb service to take effect of the changes as below.

[root@nav1 ~]# service smb restart
Shutting down SMB services:                                [  OK  ]
Shutting down NMB services:                                [  OK  ]
Starting SMB services:                                     [  OK  ]
Starting NMB services:                                     [  OK  ]
[root@nav1 ~]#

Now go to Windows machine and try to Map the network drive using the RHEL5 machine IP and share name and Samba username/password.
It should work fine.

Reference:
http://mars.netanya.ac.il/~unesco/cdrom/LDP/Samba/HTML_FORMAT/SMB-HOWTO.html#toc6


Regards,
Navaneeth

Sharing folder from Windows to RHEL5 machine

First share a folder in windows and give permission to everyone to access to that folder.

Go to linux machine and do the below steps

Prerequsties should be , all the packages for running SAMBA service should be installed on the Linux machine. And the SAMABA service should be running on the machine.

Please use below commands for checking about the SAMBA service.

[root@nav1 ~]# rpm -qa *samba*
sblim-cmpi-samba-0.5.2-31.el5_2.1
sblim-cmpi-samba-test-1-31.el5_2.1
samba-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
sblim-cmpi-samba-devel-1-31.el5_2.1
system-config-samba-1.2.41-3.el5
samba-common-3.0.33-3.7.el5
samba-swat-3.0.33-3.7.el5
You have new mail in /var/spool/mail/root

[root@nav1 ~]# service smb status
smbd (pid 3635 3068 3029) is running...
nmbd (pid 3032) is running...
[root@nav1 ~]#
[root@nav1 ~]# smbclient -L WindowsIP -U APAC/WinUsrId
Password:

Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

        Sharename       Type      Comment
        ---------       ----      -------
        IPC$            IPC       Remote IPC
        D$              Disk      Default share
        ADMIN$          Disk      Remote Admin
        C$              Disk      Default share
        linux_share     Disk     
Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]

        Server               Comment
        ---------            -------

        Workgroup            Master
        ---------            -------
[root@nav1 /]# smbclient \\\\WindowsIP\\linux_share -U APAC/WinUsrId
Password:

Domain=[APAC] OS=[Windows 5.1] Server=[Windows 2000 LAN Manager]
smb: \> ls
  .                                   D        0  Thu Oct 13 21:29:35 2011
  ..                                  D        0  Thu Oct 13 21:29:35 2011

                59616 blocks of size 2097152. 37993 blocks available
smb: \> ls
  .                                   D        0  Thu Oct 13 21:41:10 2011
  ..                                  D        0  Thu Oct 13 21:41:10 2011
  sfsdf                               A        0  Thu Oct 13 21:41:07 2011

                59616 blocks of size 2097152. 37993 blocks available
smb: \>

For Configuring through GUI use below option

go to places menu -> connect to server

it will open a connet to server configuration menu

1. select service type as "Windows Share"
2. Enter Windows Server name which u want to share in the server name option
3. Enter share folder name in the folder name option
4. Enter windows username in the username option
5. Enter windows Domain name in domain name option
6. Name to use for connection is optional and u can use whatever name u want

at last give connect it will ask for password for windows user id, give the passwd thats it !! 

it will be connected to the windows share folder and u will be able to do whatever u want.

Reference:

http://mars.netanya.ac.il/~unesco/cdrom/LDP/Samba/HTML_FORMAT/SMB-HOWTO-7.html

Regards,
Navaneeth

Adding Harddisk on RHEL5

[root@localhost ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1           6       48163+  83  Linux
/dev/sda2               7         515     4088542+  83  Linux
/dev/sda3             516         776     2096482+  82  Linux swap / Solaris
/dev/sda4             777        1305     4249192+   5  Extended
/dev/sda5             777        1305     4249161   83  Linux

Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@localhost ~]# 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.

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-913, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-913, default 913):
Using default value 913

Command (m for help): p

Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         913     7333641   83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@localhost ~]# mount -t ext3 /dev/sdb1 /u01

[root@nav1 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             3.8G  1.9G  1.7G  53% /
/dev/sda5             4.0G  137M  3.6G   4% /home
/dev/sda1              46M   11M   33M  24% /boot
tmpfs                 506M     0  506M   0% /dev/shm
/dev/sdb1             6.9G  144M  6.4G   3% /u01

For mounting permanently add the entry in the fstab file in "/etc" location as below

[root@nav1 ~]# cat /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/home             /home                   ext3    defaults        1 2
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda3         swap                    swap    defaults        0 0
/dev/sdb1               /u01                    ext3    defaults        0 0

Regards,
Navaneeth

Wednesday, October 5, 2011

11gr1 Clean up failed cluster installation on linux RHEL5

Cleaned up a failed oracle 11gr1 crs installation on RHEL5

in node1

[root@rac1 ~]# cd /u01/app/oracle/product/11.1.0/crs/
[root@rac1 crs]# ls
bin css install JRE nls oui rdbms srvm
cdata cv install.platform ldap OPatch perl relnotes xdk
cfgtoollogs diagnostics inventory lib opmn plsql root.sh
clone evm jdk log oracore precomp slax
crs has jlib network oraInst.loc racg sqlplus

[root@rac1 crs]# cd install
[root@rac1 install]# ./rootdelete.sh

Getting local node name
NODE = rac1
PRKO-2006 : Invalid node name: rac1
Stopping resources.
This could take several minutes.
Error while stopping resources. Possible cause: CRSD is down.
Stopping Cluster Synchronization Services.
Unable to communicate with the Cluster Synchronization Services daemon.
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in '/etc/oracle/scls_scr'
Cleaning up Network socket directories

[root@rac1 install]# ./rootdeinstall.sh
Verifying existence of ocr.loc file
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.145772 seconds, 71.9 MB/s
[root@rac1 install]#

in node2

[oracle@rac2 bin]$ su -
Password:
[root@rac2 ~]# cd /u01/app/oracle/product/11.1.0/crs/
[root@rac2 crs]# cd install
[root@rac2 install]# ./rootdelete.sh

Getting local node name
NODE = rac2.localdomain
PRKO-2006 : Invalid node name: rac2.localdomain
Oracle Cluster Registry initialization failed with invalid format: PROC-22: The OCR backend has an invalid format
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in '/etc/oracle/scls_scr'
Cleaning up Network socket directories

[root@rac2 install]# ./rootdeinstall.sh
Verifying existence of ocr.loc file
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.126807 seconds, 82.7 MB/s
[root@rac2 install]#

in node1

[oracle@rac1 oui]$ cd bin
[oracle@rac1 bin]$ ./runInstaller
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 2047 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-05_11-51-47AM. Please wait ...[oracle@rac1 bin]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.
/u01/app/oracle/product/11.1.0/crs/bin/crsctl.bin: error while loading shared libraries: /u01/app/oracle/product/11.1.0/crs/lib/libclntsh.so.11.1: file too short
-bash: /u01/app/oracle/product/11.1.0/crs/bin/crsctl: No such file or directory
-bash: /u01/app/oracle/product/11.1.0/crs/bin/crsctl: No such file or directory

You can check Metalink note How to Clean Up After a Failed 10g or 11.1 Oracle Clusterware Installation [ID 239998.1] for more info.

Regards,
Navaneeth

Sunday, October 2, 2011

Adding new hardisk in Solaris10

Adding new hardisk in Solaris10

# format

FORMAT MENU:

disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
fdisk - run the fdisk program
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
! - execute , then return

quit

format>p

partition> p

Current partition table (unnamed):

Total disk cylinders available: 2044 + 2 (reserved cylinders)
Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 - 2043 3.99GB (2044/0/0) 8372224
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0
8 boot wu 0 - 0 2.00MB (1/0/0) 4096
9 unassigned wm 0 0 (0/0/0) 0

partition> 3

Part Tag Flag Cylinders Size Blocks

3 unassigned wm 0 0 (0/0/0) 0
Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]: 1
Enter partition size[0b, 0c, 1e, 0.00mb, 0.00gb]: 2043c

partition> p
 
Current partition table (unnamed):
Total disk cylinders available: 2044 + 2 (reserved cylinders)
Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 - 2043 3.99GB (2044/0/0) 8372224
3 unassigned wm 1 - 2043 3.99GB (2043/0/0) 8368128
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0
8 boot wu 0 - 0 2.00MB (1/0/0) 4096
9 unassigned wm 0 0 (0/0/0) 0

partition> q

FORMAT MENU:

disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
fdisk - run the fdisk program
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
! - execute , then return

quit

format> l

Ready to label disk, continue? y

format> q

# newfs /dev/rdsk/c1t1d0s3

newfs: construct a new file system /dev/rdsk/c1t1d0s3: (y/n)? y

/dev/rdsk/c1t1d0s3: 8368128 sectors in 2043 cylinders of 128 tracks, 32 sectors

4086.0MB in 79 cyl groups (26 c/g, 52.00MB/g, 6400 i/g)

super-block backups (for fsck -F ufs -o b=#) at:

32, 106560, 213088, 319616, 426144, 532672, 639200, 745728, 852256, 958784,

7350464, 7456992, 7563520, 7670048, 7776576, 7883104, 7989632, 8096160,

8202688, 8309216

# mkdir /u01

# mount -f ufs /dev/dsk/c1t1d0s3 /u01

# df -h

Filesystem size used avail capacity Mounted on

/dev/dsk/c1t0d0s0 9.3G 4.4G 4.8G 48% /

/devices 0K 0K 0K 0% /devices

ctfs 0K 0K 0K 0% /system/contract

proc 0K 0K 0K 0% /proc

mnttab 0K 0K 0K 0% /etc/mnttab

swap 938M 924K 937M 1% /etc/svc/volatile

objfs 0K 0K 0K 0% /system/object

sharefs 0K 0K 0K 0% /etc/dfs/sharetab

/usr/lib/libc/libc_hwcap1.so.1

9.3G 4.4G 4.8G 48% /lib/libc.so.1

fd 0K 0K 0K 0% /dev/fd

swap 937M 112K 937M 1% /tmp

swap 937M 36K 937M 1% /var/run

/hgfs 16G 4.0M 16G 1% /hgfs

/tmp/VMwareDnD 0K 0K 0K 0% /var/run/vmblock

/dev/dsk/c1t1d0s3 3.9G 4.0M 3.9G 1% /u01

#

# bash

bash-3.00# mount -p

/dev/dsk/c1t0d0s0 - / ufs - no rw,intr,largefiles,logging,xattr,onerror=panic

/devices - /devices devfs - no

ctfs - /system/contract ctfs - no

proc - /proc proc - no

mnttab - /etc/mnttab mntfs - no

swap - /etc/svc/volatile tmpfs - no xattr

objfs - /system/object objfs - no

sharefs - /etc/dfs/sharetab sharefs - no

/usr/lib/libc/libc_hwcap1.so.1 - /lib/libc.so.1 lofs - no

fd - /dev/fd fd - no rw

swap - /tmp tmpfs - no xattr

swap - /var/run tmpfs - no xattr

/hgfs - /hgfs vmhgfs - no

/tmp/VMwareDnD - /var/run/vmblock vmblock - no

/dev/dsk/c1t1d0s3 - /u01 ufs - no rw,intr,largefiles,logging,xattr,onerror=panic

bash-3.00# man fstype

Regards,
Navaneeth

Thursday, September 29, 2011

Information on Statistics collection on 11gR2

In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object.

Please see the below for the job details.

select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT
 
CLIENT_NAME                    STATUS    CONSUMER_GROUP     
------------                   ---------  --------------
auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP


The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%.

You can check this values by using the below query.

select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual.

To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection.

The values:
   'ALL' - Statistics collected for all objects in system
   'ORACLE' - Statistics collected for all Oracle owned objects
   'AUTO' - Oracle decides on which objects to collect statistics

We can check this by using the below query

select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual;
DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')                                      
-------------------------------------------
AUTO 

To check the percentage of rows modified or inserted or deleted on the tables you can use the below query

select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT'
If you need to check the information about stats collection on your tables, you can use the below query

select * from DBA_TAB_STATS_HISTORY where owner='SCOTT'
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB
where client_name='%stats%';

To enable or disable the Auto stats job please use the below procs.

ENABLE:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/


DISABLE:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/


Other useful queries

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';

SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';

Select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';


Regards,
Navaneeth

Monday, September 26, 2011

Starting windows services starts database automaticaly and maintain services on Windows

Sometimes starting the Windows Oracle services starts the database automatically. You can check this in Windows registry as mentioned below.

Open the registry with regedit.   

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\oracle_home_name.
There will be a key called:
ORA_SID_AUTOSTART.  SID is your database SID.

This key should be set to TRUE.  If you want to start the database automatically once the service is started.  

If you want to manually start the database set ORA_SID_AUTOSTART to FALSE.  The service will start but not the database.  


There is also an ORA_SID_SHUTDOWN which you want to be TRUE so that if the server is shut down the service will shutdown the database automatically.

For Deleting a service

you can use 
> oradim or sc delete
 
Regards,
Navaneeth

Sunday, September 25, 2011

Incremental backup size of 4th week is Huge

This is tested in production environment. There was an issue in the incremental backup of a 10.2.0.3 database server. The every months 4th week cumulative incremental backup size is huge(comes around 400 Gigs).

Database size - 335 GB.


Backup scenario:

> 1st sunday of every month - Level 0 Incremental backup
    script => Backup incremental level = 0 database tag='Complete_Backup';

> Every Sunday of the month - Level 1 Cumulative Incremental backup
   script => Backup incremental level = 1 cumulative database tag='Cumulative_Backup';

Once the backup is completed the backup's will be moved from the local drive to tape drive(ie: once in a week)

The issue was because of the Incremental backups are moved from its original location to tape drive. It was proved by the below query.

select recid, file#, to_char(creation_change#) create#, incremental_level lvl, to_char(incremental_change#) inc#,
to_char(checkpoint_change#) ckp#, datafile_blocks BLKS, block_size blksz, blocks_read READ,
round((blocks_read/datafile_blocks) * 100,2)  READpc,
blocks WRTN, round((blocks/datafile_blocks)*100,2) WRTNpc,
completion_time, used_change_tracking bct
from v$backup_datafile where
completion_time > to_date('17-Jun-11 12:32:00', 'dd-mon-rr hh24:mi:ss ')
and file# > 0;

 
 If we see the query output we can find that for several Datafiles the 'incremental_change#' is 0 or the same as the Datafile Creation SCN. This means that the RMAN Incremental Backup cannot find a corresponding Level 0 Backup and takes a full Backup of these Datafiles. Therefore the Size of the Backup is growing that much.

So to avoid this we have to ensure that Level 0 Backup from Week 1 for all Datafiles is available for all 4 Weeks (until the next Level 0 Backup is taken).Even do not move this Backup outside RMAN - RMAN must be able to access the Level 0 Backup at its cataloged Location.

We tested by not moving the Incremental Level 0 backup until the 4th weeks backup was taken from its original location. After this the 4th week backup was fine and was creating the backup size of normal.

Hope this helps !

Regards,
Navaneeth

Wednesday, September 21, 2011

Rename/Move a datafile in the primary and propagate the changes in the standby database.

Rename a datafile in the primary database and manually propagate the changes to the standby database.

 in PRIMARY

1. Set STANDBY_FILE_MANAGEMENT=MANUAL on both Primary and Standby Database.

SQL> show parameter standby_file_manag

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
-----------------  ---------------
db_unique_name      orcl11g

SQL>
SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL>  show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL
  
in standby

SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> select name,value from v$parameter where name like 'db_unique_name';

NAME                VALUE
------------------ ------------
db_unique_name      11gstdby

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      MANUAL
 
in PRIMARY

2. Take the Tablespace offline on the Primary Database:

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 FDB                            YES NO  YES

6 rows selected.


SQL> alter tablespace users offline;

Tablespace altered.

3. Rename Datafile on Primary Site:

   copy the file using OS command to the new location


4. Rename the Datafile in the Primary Database.

SQL> alter tablespace users rename datafile 'D:\app\nvcb\oradata\orcl11g\USERS01.DBF' TO 'D:\app\nvcb\oradata\orcl11g_1\USERS01.DBF';

Tablespace altered.

5. Bring the Tablespace back online:

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.
in standby

1. Stop Redo Apply on Standby Database.

SQL> recover managed standby database cancel;
Media recovery complete.

2. Shutdown the Standby Database.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

3. Move the Datafile to the new location at the Standby site.

Copy the file using OS command to the new location.

4. Start and mount the Standby Database.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             209715664 bytes
Database Buffers          318767104 bytes
Redo Buffers                5804032 bytes
Database mounted.

5.Rename the Datafile in the Standby Database control file.

SQL> alter database rename file 'D:\app\nvcb\oradata\11gstdby\USERS01.DBF' to 'D:\app\nvcb\oradata\11gstdby_1\USERS01.DBF';

Database altered.

6. On the Standby Database, restart Redo Apply:

SQL> recover managed standby database disconnect from session;
Media recovery complete.


7. Set STANDBY_FILE_MANAGEMENT=AUTO on both Primary and Standby Database.


SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO

SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE#   NAME
----------  -----------------------------------------------
          4 D:\APP\nvcb\ORADATA\11GSTDBY_1\USERS01.DBF

in Primary

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
standby_file_management              string      AUTO


SQL>  select file#,name from v$datafile where name like '%USERS%';

     FILE# NAME
---------- --------------------------------------

         4 D:\APP\NAV6COB\ORADATA\ORCL11G_1\USERS01.DBF

Regards,
Navaneeth

Tuesday, September 20, 2011

Moving DATAFILE(S) with the Database OPEN in 10.2.0.3

A datafile has been created in a wrong location by mistake, so it has to be moved to a valid location without down time. Its a 10.2.0.3 production environment.

Renaming or Moving DATAFILE(S) with the Database OPEN
  
1. Check how many datafiles are associated with the tablespace.

  
SQL>SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TS_BCH_DE';  

FILE_NAME                                  STATUS
----------------------------------------------------------------------------
F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF                                                      AVAILABLE

2. Make sure that all datafiles returned have the status AVAILABLE.

3. Make the tablespace is read only.

     
SQL> ALTER TABLESPACE TS_BCH_DE READ ONLY;

Tablespace altered.


4. Make sure that the tablespace is defined as read only in the data dictionary.
 
SQL>  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TS_BCH_DE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS_BCH_DE                    READ ONLY

SQL>


5. Copy the datafile(s) to the new location using the operating system copy command.Once the datafile(s) have been copied to the new location compare the sizes of the datafiles.Make sure that the sizes match.

6. Once the datafiles have been copied to the new location alter the tablespace offline.


SQL> ALTER TABLESPACE TS_BCH_DE OFFLINE;

Tablespace altered.


7. Once the tablespace is offline you will need to rename the datafile(s) to the new location.This updates the entry for the datafile(s) in the controlfile.

     
SQL> ALTER DATABASE RENAME FILE
  2                   'F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF'
  3                   TO
  4                   'F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF';

Database altered.


8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

      
SQL> ALTER TABLESPACE TS_BCH_DE ONLINE;

Tablespace altered.


9. After you bring the tablespace back online you can make the tablespace read/write again.

      
SQL> ALTER TABLESPACE TS_BCH_DE READ WRITE;

Tablespace altered.

10. You can check the status of the tablespace to make sure it is read/write.You can also verify that the controlfile has been updated by doing the following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

the above command creates the readable format of the content of the controlfile, check whether the datafile location is updated

         or

Optionally, we can query V$DATAFILE, which gets information from the controlfile as well.


SQL> select file#,status,enabled,name from v$datafile where name like '%TS_BCH_DE%';

     FILE# STATUS  ENABLED    NAME
---------- ------- ---------- ---------------------------------------------------------------------
        28 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF
        85 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF
        90 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF



11. Remove the datafile(s) from the old location at the O/S level.

Regards,
Navaneeth

Thursday, September 15, 2011

Grant privilege on V$ tables to other users

SQL> grant select on v$session to scott;
grant select on v$session to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> select owner,object_name,object_type from dba_objects where object_name = 'V$SESSION';
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC                         V$SESSION                      SYNONYM

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = 'V$SESSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$SESSION

SQL> grant select on v_$session to scott;

Grant succeeded.

All the V$ views are synonyms, we can use the above method to query and find the corresponding tables and grant access to other users.

Regards,
Navaneeth

Thursday, September 8, 2011

TNS Entry on 2 node RAC database and Listener Entry

************ Tnsnames.ora ********

INSPT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB2)
    )
  )

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (INSTANCE_NAME = DB1)
    )
  )

LISTENERS_DB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
  )

CLUSTER1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 173.243.1.235)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pedb)
    )
  )

DB_REPORT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Node2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB)
    )
  )




 ************ Listener db1 **************


# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = DB1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Node1)(PORT = 1521))
  )


Wednesday, September 7, 2011

NX configuration on RHEL5 server for Acessing Linux server through GUI

Installing RPM's on linux server for configuring NX :

Nx is used as a GUI interface for acessing Linux servers. 

1. Need to install NX client on Windows machine.
2. Install Nxclient, NXnode and Nxserver RPM's on Linux server on which you need to acess through GUI.


[root@Servername /]# rpm -ivh nxclient-3.5.0-7.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxclient               ########################################### [100%]
Showing file: /usr/NX/share/documents/client/cups-info

 CUPS Printing Backend

 The NX Client set-up procedure detected that your "IPP CUPS" printing
 backend doesn't allow printing from the NX session. In order to have
 printing support in your NX system, you need to set proper permissions
 on the IPP backend. Please execute:

   chmod 755 /usr/lib/cups/backend/ipp
[root@Servername /]# rpm -ivh nxnode-3.5.0-6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxnode                 ########################################### [100%]
NX> 700 Starting: install node operation at: Tue Sep 06 15:45:08 2011.
NX> 700 Autodetected system 'redhat'.
NX> 700 Install log is '/usr/NX/var/log/install'.
NX> 700 Creating configuration in /usr/NX/etc/node.cfg.
NX> 700 Inspecting local CUPS environment.
NX> 700 Generating CUPS entries in: /usr/NX/etc/node.cfg.
NX> 700 Installation of version: 3.5.0-6 completed.
NX> 700 Bye.
[root@Servername /]# rpm -ivh nxserver-3.5.0-8.x86_64.rpm
Preparing...                ########################################### [100%]
   1:nxserver               ########################################### [100%]
NX> 700 Installing: server at: Tue Sep 06 15:45:37 2011.
NX> 700 Autodetected system: redhat.
NX> 700 Install log is: /usr/NX/var/log/install.
NX> 700 WARNING: Found file: /usr/NX/etc/keys/node.localhost.id_dsa.
NX> 700 WARNING: Skipping generation keys for NX users authentication.
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 700 Installation of NX server was completed with warnings.
NX> 700 Please review the install log '/usr/NX/var/log/install'
NX> 700 for further details.
NX> 700 Showing file: /usr/NX/share/documents/server/install-notices

Server keys

The initial login between client and server happens through a DSA key
pair, i.e. a couple of specially generated cryptographic keys, called
the private key and the public key, which allow you to establish a
secure connection, by means of SSL encryption, between NX client and
NX server.

The public part of the key-pair is provided during the installation
of the server, while the private part of the key-pair is distributed
together with the NX Client. This ensures that each NX client is able
to authenticate to the server and to start the procedure for autho-
rizing the user and negotiating the session.

If you want to create a virtual private network (VPN) instead, you
need to generate a new DSA key-pair and distribute the private part
of the key-pair to those NX clients you want authenticated to the NX
server. More information on how to generate and distribute a new DSA
key-pair is available at:

http://www.nomachine.com/ar/view.php?ar_id=AR01C00126

Creating Users

NX is configured to allow access from any system user, as long as
valid credentials are given to the user for the SSH login. NX pro-
vides an alternative authorization method, allowing system admin-
istrators to determine which users are given access to the NX fun-
ctionalities. This works by implementing a separation between the
system password and the NX password, so that, for example, it is
possible to forbid remote access to the system by any other means
except via NX and use the NX tools to implement effective accounting
of the system resources used by the user, or to share NX passwords in
an external database.

To activate the NX user and password DBs, you will have to edit the
NX server configuration file by hand or use the NX Server Manager
Web tool available for download on the NoMachine Web site at:

http://www.nomachine.com/download-manager.php

Session Shadowing and Desktop Sharing

The session shadowing functionality allows you to share NX sessions
running on the node. The desktop sharing functionality instead, gives
access to the native display of the X server as if you were in front
of the monitor. By default you can access sessions in interactive mode
and upon authorization of the session owner. You can modify this beha-
viour by tuning the server configuration according to your needs, for
example by allowing access to sessions in view-only mode, or connecting
to either a suspended session or the local display via the Desktop
Manager login window.

Load Balancing

NX Advanced Server provides support for multi-node capabilities and
load balancing. In its current implementation, NX server can only
manage accounts on the host machine, so to grant access to the node
running remotely, you will need to create the user account directly
on the remote node host by issuing the NX node commands as root user.
You will also need to add the NX Server public DSA Key to the node to
allow this server to connect to the node running on the remote host.

Documentation

For further information on how to manage the configuration of your
NX system, please refer to the System Administrator's Guide available
on the NoMachine Web site at:

http://www.nomachine.com/documentation/admin-guide.php

The NoMachine Team.


NX> 700 Bye.
[root@Servername /]# service nxserver restart
Trying to restart NX server:
NX> 123 Service stopped.
NX> 153 Stopping NX server monitor.
NX> 153 NX server monitor already stopped.
NX> 122 Service started.
NX> 999 Bye.
Trying to restart NX statistics:
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 999 Bye.
[root@Servername /]#

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Uninstall nxservice packages on RHEL5 server

[root@Servername /]# rpm -e nxserver-3.5.0-8.x86_64.rpm
error: package nxserver-3.5.0-8.x86_64.rpm is not installed
[root@Servername /]# rpm -e nxserver-3.5.0-8.x86_64
NX> 702 Pre-uninstalling: server at: Tue Sep 06 15:50:30 2011.
NX> 702 Shutting down.
NX> 702 Shutdown complete.
NX> 702 Cleaning up NX session DB.
NX> 718 Cannot stop NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 702 Cleaned up NX session DB.
NX> 702 Bye.

NX> 702 Uninstalling: server at: Tue Sep 06 15:50:33 2011.
NX> 702 Autodetected system: redhat.
NX> 702 Uninstall log is: /usr/NX/var/log/uninstall.

NX> 702 Deleting user: nx from the system.
NX> 702 Saving configuration file to: /usr/NX/etc/server.cfg.backup.
NX> 702 Saving passwords DB to: /usr/NX/etc/passwords.db.backup.
NX> 702 Saving users DB to: /usr/NX/etc/users.db.backup.
NX> 702 Saving administrators DB to: /usr/NX/etc/administrators.db.backup.
NX> 702 Saving profiles DB to: /usr/NX/etc/profiles.db.backup.
NX> 702 Saving guests DB to: /usr/NX/etc/guests.db.backup.
NX> 702 Running: chkconfig to remove init script.
NX> 702 Verifying that all init scripts have been removed.
NX> 702 Uninstallation of version: 3.5.0-8 completed.
NX> 702 Bye.

[root@Servername /]# rpm -ivh nxclient-3.5.0-7.x86_64
error: open of nxclient-3.5.0-7.x86_64 failed: No such file or directory
[root@Servername /]# rpm -e nxclient-3.5.0-7.x86_64
error: Failed dependencies:
        nxclient >= 3.0.0 is needed by (installed) nxnode-3.5.0-6.x86_64
[root@Servername /]# rpm -e nxnode-3.5.0-6.x86_64

NX> 702 Starting: uninstall operation at: Tue Sep 06 15:51:07 2011.
NX> 702 Autodetected system 'redhat'.
NX> 702 Uninstall log is '/usr/NX/var/log/uninstall'.
NX> 702 Running: chkconfig to remove init script.
NX> 702 Verifying that all init scripts have been removed.
NX> 702 Saving configuration file to: /usr/NX/etc/node.cfg.backup.
NX> 702 Version '3.5.0-6' uninstall completed.
NX> 702 Bye. 
 
[root@Servername /]#

Regards,
Navaneeth