I am Database Specialist in India .: October 2013

Tuesday, October 29, 2013

Automatic undo management

After 10G onward ensures automatic undo space management is enabled. Not using automatic undo management can cause unnecessary contention and performance issues in your database. This may include among other issues, contention for the rollback segment header blocks, in the form of buffer busy waits and increased probability of ORA-1555s (Snapshot Too Old).

rmem_max

The setting for the RMEM_MAX (net.core.rmem_max) parameter is required during database installation. Once installation is complete it is still advisable to set the parameter as recommended to avoid impacting database performance and overall functionality, as well as unexpected operating system behavior.

Set the RMEM_MAX (net.core.rmem_max) kernel parameter greater than or equal to the minimum recommended value. RMEM_MAX defines the TCP Window. See Document 169706.1 for more information about the proper setting for the platform and Oracle version. To modify the parameter use any text editor to create or edit the /etc/sysctl.conf file and add or edit a line similar to the following: net.core.rmem_max = 2097152. The new value will take effect after reboot.

UNDO_TABLESPACE should be specified for each Real Application Cluster instance

Oracle automatically manages undo segments within a specific undo tablespace that is assigned to an instance. Only the instance assigned to the undo tablespace can modify the contents of that tablespace. If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in the database is chosen. If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment and could lead to space issues and ora-1555 errors.

The UNDO_TABLESPACE initialization parameter should be set to a non-null value. A unique value is also needed for the UNDO_TABLESPACE on each Real Application Cluster instance. Assign undo tablespaces in the Oracle RAC database by specifying a different value for the UNDO_TABLESPACE parameter for each instance in the SPFILE or individual PFILEs.

The value of the UNDO_TABLESPACE parameter can also be changed by using the alter system command (alter system set <parameter_name> = <value> scope=both).

Refer to Oracle Real Application Clusters Administration and Deployment Guide 11g Release 2 (11.2) (http://download.oracle.com/docs/cd/E11882_01/rac.112/e16795/storage.htm#i3915041).

Sunday, October 27, 2013

Server pool in Oracle 11gR2

The following steps are performed to create a server pool via the command line:

1. Log in as grid user

2. Run the srvctl command from the Grid Home bin to see the default configuration. For example:

$GRID_HOME/bin/srvctl config srvpool

3. Add a server pool by running the following command:

$ GRID_HOME/bin/srvctl add srvpool -g oracle -l 2 -u 4 -i 2 -n node1, node2, node3, node4

where -g is the Server pool name, -l is the minimum number of nodes, -u is the maximum number of nodes, -i is the importance given to the server pool, -n is the node names which are part of the server pool. In this example,the server pool name is oracle the maximum number of nodes are four, the minimum number of nodes are two, the relative priority of the server pool is two, and names of nodes in the pool are node 1, node 2, node 3,and node 4.

4. Verify the server pool configuration by running the following command:

$ GRID_HOME/bin/srvctl config srvpool

A server pool is the logical division of the cluster into pools of servers for effective resource utilization. Server pools are used to create Policy Managed Databases.

A Policy-Managed database is dynamic with instances managed automatically based on resource requirement for expected workload where as Admin-Managed databases result in instances tied to specific servers.

Policy-Managed database facilitates easy allocation of resources to specific workload and simplifies the consolidation of Databases and Applications on Clusters.

There are 2 default pools after a Grid Infrastructure Installation or Upgrade:

• Free – any unassigned nodes go there and

• Generic – for administrator-managed databases

A server pool is defined by 3 attributes:

• Min- The minimum number of servers that should run in the server pool (default 0)

• Max– The maximum number of servers that can run in the server pool (default 0 or -1)

• Importance – Importance specifies the relative importance between server pools. 0 (least important) to 1000

Highly Available Internet Protocol (HAIP) --Private interconnect

Something Interesting to read

The grid infrastructure of Oracle 11gR2 (11.2.0.3) supports IP failover natively using a new feature introduced known as Redundant Interconnect. Oracle uses its ora.cluster_interconnect.haip resource to communicate with Oracle RAC, Oracle ASM, and other related services. The Highly Available Internet Protocol (HAIP) has the ability to activate a maximum of four private interconnect connections. These private network adapters can be configured during the initial install process of Oracle Grid or after the installation process using the oifcfg utility.

Oracle Grid currently creates an alias IP (as known as virtual private IP) on your private network adapters using the 169.254.*.* subnet for the HAIP. If subnet range is already in use, Oracle Grid will not attempt to use it. The purpose of HAIP is to load balance across all active interconnect interfaces, and failover to other available interfaces if one of the existing private adapters becomes unresponsive.

Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip (Doc ID 1210883.1)

EXADATA HEALTH CHECK

Oracle has provided Exachk to do HealthCheck on Oracle Exadata Database Machine.

Oracle Exadata Database Machine exachk collect data regarding key software, hardware, firmware, and configurations. The exachk output assists customers to review and cross reference current collected data against supported version levels and recommended Oracle Exadata best practices.

Its a good idea to run Exadata Health Check Periodically (Monthly)
Both the Oracle Exadata Database Machine exachk and HealthCheck can be executed as desired and should be executed regularly as part of the maintenance program for an Oracle Exadata Database Machine.

Pleaser refer Oracle Support Note 1070954.1 for more information

Monday, October 21, 2013

wrong datafile addition in ASM Env

SQL> alter tablespace SYSAUX add datafile '   +DATA' size 100m;

Tablespace altered.

-----above i have given space before diskgroup name. this way datafile will create inside '$ORACLE_HOME/dba'

SQL> select NAME,FILE# from v$datafile where FILE#='43';

NAME
--------------------------------------------------------------------------------
     FILE#
----------
/XXX/oracle/database/11.2.0/dbs/ +DATA
        43

------- Above we can see new datafile name is '/XXX/oracle/database/11.2.0/dbs/ +DATA' now we are sure it's abnormal behavior .
-- check some more detail using Praveen sql -
SQL> select d.file#,d.name "file name",to_char(d.CREATION_TIME,'DAYDD-MON-YY HH:MI:ss'),d.STATUS,t.NAME "tablespace name" from v$datafile d,v$tablespace t where d.FILE#=43 and d.TS#=t.TS#;

     FILE#
----------
file name
--------------------------------------------------------------------------------
TO_CHAR(D.CREATION_TIME,'DAYDD-MON-YYHH:MI:SS')     STATUS
--------------------------------------------------- -------
tablespace name
------------------------------
        43
/XXX/oracle/database/11.2.0/dbs/ +DATA
MONDAY   21-OCT-13 01:57:20                         ONLINE
SYSAUX
---now i can sure we have same situation as corp_bsm2 have . let fix this now ,

Makeing 43 datafile ID offline.
SQL> alter database datafile 43 offline;

Database altered.

SQL> select d.file#,d.name "file name",to_char(d.CREATION_TIME,'DAYDD-MON-YY HH:MI:ss'),d.STATUS,t.NAME "tablespace name" from v$datafile d,v$tablespace t where d.FILE#=43 and d.TS#=t.TS#;

     FILE#
----------
file name
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TO_CHAR(D.CREATION_TIME,'DAYDD-MON-YYHH:MI:SS')     STATUS tablespace name
--------------------------------------------------- ------- ------------------------------
        43
/XXX/oracle/database/11.2.0/dbs/ +DATA
MONDAY   21-OCT-13 01:57:20                         RECOVER SYSAUX

-- After keeping data file offline it went RECOVER mode,

SQL>
----Connect to target database using RMAN , (Here catalog connection not required) Then take a copy of that datafile into '+DATA' DG.

RMAN> backup as copy datafile 43 format '+DATA';

Starting backup at 21-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=105 instance=XXX2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=123 instance=XXX2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00043 name=/XXX/oracle/database/11.2.0/dbs/ +DATA
output file name=+DATA/XXX/datafile/sysaux.831.829361111 tag=TAG20131021T020510 RECID=1 STAMP=829361111
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-OCT-13

---Now wrongly created datafile got plugged into ASM storage, lets update controlfile with new datafile location.

RMAN> switch datafile 43 to copy;

datafile 43 switched to datafile copy "+DATA/XXX/datafile/sysaux.831.829361111"

Ctl file also updated .

SQL> select name from v$datafile where file#=43;

NAME
--------------------------------------------------------------------------------
+DATA/XXX/datafile/sysaux.831.829361111

SQL>

SQL> select d.file#,d.name "file name",to_char(d.CREATION_TIME,'DAYDD-MON-YY HH:MI:ss'),d.STATUS,t.NAME "tablespace name" from v$datafile d,v$tablespace t where d.FILE#=43 and d.TS#=t.TS#;

     FILE#
----------
file name
--------------------------------------------------------------------------------
TO_CHAR(D.CREATION_TIME,'DAYDD-MON-YYHH:MI:SS')     STATUS
--------------------------------------------------- -------
tablespace name
------------------------------
        43
+DATA/XXX/datafile/sysaux.831.829361111
MONDAY   21-OCT-13 01:57:20                         RECOVER
SYSAUX

-- now recover datafile ,
SQL> recover datafile 43;
Media recovery complete.
-- keep again datafile to online/normal mode
SQL> alter database datafile 43 online;

Database altered.

-- At last check dafile status .

SQL> select d.file#,d.name "file name",to_char(d.CREATION_TIME,'DAYDD-MON-YY HH:MI:ss'),d.STATUS,t.NAME "tablespace name" from v$datafile d,v$tablespace t where d.FILE#=43 and d.TS#=t.TS#;

     FILE#
----------
file name
--------------------------------------------------------------------------------
TO_CHAR(D.CREATION_TIME,'DAYDD-MON-YYHH:MI:SS')     STATUS
--------------------------------------------------- -------
tablespace name
------------------------------
        43
+DATA/XXX/datafile/sysaux.831.829361111
MONDAY   21-OCT-13 01:57:20                         ONLINE
SYSAUX

SQL>

Now we can manually remove that old datafile located in $ORACLE_HOME/dbs directory.

Sunday, October 20, 2013

ORA-00060

Master Note: Optimizer Statistics (Doc ID 1369591.1)
How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)

Wednesday, October 16, 2013

MySQL Basic Administration

Using Yum install mysql -

[root@joshi ~]# yum install mysql mysql-client mysql-server
Loaded plugins: fastestmirror
Determining fastest mirrors
CentOS | 1.1 kB 00:00
Setting up Install Process
No package mysql-client available.
Resolving Dependencies
--> Running transaction check
---> Package mysql.i386 0:5.0.77-4.el5_4.2 set to be updated
--> Processing Dependency: perl(DBI) for package: mysql
---> Package mysql.x86_64 0:5.0.77-4.el5_4.2 set to be updated
---> Package mysql-server.x86_64 0:5.0.77-4.el5_4.2 set to be updated
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated
---> Package perl-DBI.x86_64 0:1.52-2.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================
Installing:
mysql i386 5.0.77-4.el5_4.2 CentOS 4.8 M
mysql x86_64 5.0.77-4.el5_4.2 CentOS 4.8 M
mysql-server x86_64 5.0.77-4.el5_4.2 CentOS 9.8 M
Installing for dependencies:
perl-DBD-MySQL x86_64 3.0007-2.el5 CentOS 148 k
perl-DBI x86_64 1.52-2.el5 CentOS 600 k

Transaction Summary
===================================================================================================================================================================================
Install 5 Package(s)
Upgrade 0 Package(s)

Total download size: 20 M
Is this ok [y/N]: y
Downloading Packages:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 334 MB/s | 20 MB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : perl-DBI 1/5
Installing : mysql 2/5
Installing : perl-DBD-MySQL 3/5
Installing : mysql 4/5
Installing : mysql-server 5/5

Installed:
mysql.i386 0:5.0.77-4.el5_4.2 mysql.x86_64 0:5.0.77-4.el5_4.2 mysql-server.x86_64 0:5.0.77-4.el5_4.2

Dependency Installed:
perl-DBD-MySQL.x86_64 0:3.0007-2.el5 perl-DBI.x86_64 0:1.52-2.el5

Complete!

Start MySQL database service as:

[root@joshi ~]# service mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h joshi.tenongroove.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]

Create password for mysql -

[root@joshi ~]# /usr/bin/mysqladmin -u root password root

Login mysql db using root user.

[root@joshi ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create a new database uttrakhand

mysql> create database uttrakhand
-> ;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| uttrakhand |
+--------------------+
4 rows in set (0.00 sec)

use database for doing task.

mysql> use uttrakhand;
Database changed

Create Table.

mysql> CREATE TABLE Delhi (
-> Emp_id Int(3),
-> first_name Varchar (19),
-> email Varchar(15) ) ;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_uttrakhand |
+----------------------+
| Delhi |
+----------------------+
1 row in set (0.00 sec)

mysql> show columns from Delhi;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Emp_id | int(3) | YES | | NULL | |
| first_name | varchar(19) | YES | | NULL | |
| email | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Monday, October 7, 2013

mtime, ctime and atime are not confusing ?.

ctime

ctime is the inode or file change time. The ctime gets updated when the file attributes are changed, like changing the owner, changing the permission or moving the file to an other filesystem but will also be updated when you modify a file.

mtime

mtime is the file modify time. The mtime gets updated when you modify a file. Whenever you update content of a file or save a file the mtime gets updated.

Most of the times ctime and mtime will be the same, unless only the file attributes are updated. In that case only the ctime gets updated.

atime

atime is the file access time. The atime gets updated when you open a file but also when a file is used for other operations like grep, sort, cat, head, tail and so on.

ALTER SYSTEM KILL Session Marked for Killed Forever

We have a many session that have killed, but still they are present into db,

SQL> alter system kill session 'sid, serial#';

SQL> select status, username from v$session;

status killed

username username

I have issued this several times and it seems it still is marked as killed.

In order to determine which process to kill:

a) On a Unix platform:

SQL> SELECT spid

FROM v$process

WHERE NOT EXISTS ( SELECT 1

FROM v$session

WHERE paddr = addr);

SQL> SELECT inst_id, status, event, state, blocking_session, sid, serial#, program

FROM gv$session

WHERE status = 'KILLED';

% kill <spid>

After doing some googling i got my answer.

The simplest (and probably most common) reason the session stays around is because the process is still around. The reason the

process is still around is because it is waiting on "SQLNet message from client".  If it does ever get a message, it will

then respond with an ORA-28 "Your session has been killed"error number. At that point the session should go away. The

dedicated server process may remain alive until the client disconnects or exits.

In same case it has to rollback or rook forward for this session data so  until rollback or rook forward is not completed it will show as marked kill.

PMON may take ownership of the session while it is cleaning up any resources held by the session at the time it was killed.

If it cannot clean everything up immediately it will leave the session under the PSEUDO process while performing other tasks.

Sunday, October 6, 2013

Convert Datafile from Raw Device to normal File System Or ASM

Use RMAN to move datafiles from raw devices to file system./ASM file system.

1. Connect to the database:

$ sqlplus system/manager@orcl

2. Put the tablespace with the datafile, which should be converted, offline:

SQL> alter tablespace test_ts offline;

3. Start rman and connect it to the database:

$ rman nocatalog target rman/rman@orcl

4. a-Move the datafile to file system:

RMAN> run {
2> allocate channel c1 type disk;
3> copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf';
4> }

4. b -Move the datafile to ASM:
RMAN> run {
2> allocate channel c1 type disk;
3> copy datafile '/dev/raw1' to '+DATA';
4> }

5. Rename the moved datafile:

SQL> alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf';

6. Put the tablespace back online:

SQL> alter tablespace test_ts online;

Silent Install with the 11.2 Oracle Client Installer

Here are the steps to create the response file:

1. Launch the 11.2 client setup.exe file that comes with the Oracle Client software download.

2. The "Select Installation Type" screen will come up when the installer is running. Click on
     Custom for the installation type.

3. Click Next.

4. On the "Select Product Languages" screen select the language you want to install in by
     highlighting the language and use the arrow keys to select the language.

5. Click Next.

6. On the "Specify Installation Location" screen browse to the location of where you would like the
     Oracle Base and Software location to be located.

7. Click Next.

8. On the "Available Products Components" screen select all the products you want to include in
     the install. In this case the following products will be selected:

        SQL*Plus
        Oracle Net
        Oracle ODBC Driver
        Oracle Provider for OLE DB
        Oracle Data Provider for .NET
        Oracle Providers for ASP.NET

9.   Click next.

10. Click next on the Perform Prerequisite screen once the prerequisite checks have completed.

11. On the "Summary" screen click on "Save Response File" and provide a file name and location
      to save to.

12. If you click on "Finish" this will complete the custom install installation or exit the installer if you
      do not wish to install the product at this time.

To Install using the response file:

1. Before using the saved response file on another system, edit the file in notepad.exe and make
    any required changes to the directory location for ORACLE_BASE and ORACLE_HOME.

2. Open a command prompt and navigate to the location where your setup.exe is that comes with
    the client software is and pass the following to start the install.

Example:
D:\soft\win32_11gR2_client\client>setup -silent -responseFile D:\soft\oracle/11gR2_client\client\client_custom.rsp

Thursday, October 3, 2013

Verify the hard disk information in linux

hard disk information -

[root@joshi_test-VM-Linux ~]# fdisk -l
Disk /dev/sda: 11.8 GB, 11811160064 bytes
255 heads, 63 sectors/track, 1435 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 102400 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 14 1436 11427840 8e Linux LVM
Partition 2 does not end on cylinder boundary.
Disk /dev/sdb: 42.9 GB, 42949672960 bytes <== New disk can be seen
255 heads, 63 sectors/track, 5221 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 2610 20964761 83 Linux <== Currently having single partition of Linux

2. Verify the existing LV information

root@joshi_test -VM-Linux ~]# lvdisplay
— Logical volume —
LV Name /dev/datavg/applocalvol
VG Name datavg
LV UUID SFchwv-BPVy-MuVR-rA2p-MaOC-P8oq-YGPT7B
LV Write Access read/write
LV Status available
# open 1
LV Size 19.99 GB ß Only 20G seen
Current LE 5118
Segments 1
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 253:2

The additional disk space is already visible through fdisk (20G), so we just needed to create an additional partition (/dev/sdb2) from that space: