CREATING ASM INSTANCE USING SATA HARD DISK IN LINUX

Applies to: Linux Kernel - Version: 1.0 to 2.0 Linux x86-64 Linux x86

Goal

ASMLib is a support library for the Automatic Storage Management feature of Oracle Database 10g. This document is a set of tips for installing the library and its supporting driver. The complete installation guide is part of the Oracle Database 10g Documentation.

This document describes the steps required to install the Linux specific ASM library and its assocated driver. This library is provide to enable ASM I/O to Linux disks without the limitations of the standard Unix I/O API. The steps below are steps that the system administrator must follow.

Solution

Locating the ASMLib Packages

The ASMLib software is available from the Oracle Technology Network. Go to ASMLib download page and follow the link for your platform.The link is http://www.oracle.com/tecnology/tech/linux/asmlib/index.html
Select a proper version for ur OS.
You will see 4-6 packages for your Linux platform. The oracleasmlib package provides the actual ASM library. The oracleasm-support package provides the utilities used to get the ASM driver up and running. Both of these packages need to be installed.

The remaining packages provide the kernel driver for the ASM library. Each package provides the driver for a different kernel. You must install the appropriate package for the kernel you are running. Use the "uname -r command to determine the version of the kernel. The oracleasm kerel driver package will have that version string in its name. For example, if you were running Red Hat Enterprise Linux 4 AS, and the kernel you were using was the 2.6.9-5.0.5.ELsmp kernel, you would choose the oracleasm-2.6.9-5.0.5-ELsmp package.

Installing the ASMLib Packages

So, to install these packages on RHEL4 on an Intel x86 machine, you might use the command:

rpm -Uvh oracleasm-support-2.0.0-1.i386.rpm \
oracleasm-lib-2.0.0-1.i386.rpm \
oracleasm-2.6.9-5.0.5-ELsmp-2.0.0-1.i686.rpm

If you were on a different machine, the package names would be slightly different, replacing 'i686' with the appropriate architecture. Use the package names relevant for your distribution.

NOTE: Distributions with the Linux 2.4 kernel still use the 1.0 kernel driver, while distributions based on the Linux 2.6 kernel use the 2.0 kernel driver. All distributions use version 2.0 of the support and library packages. See Note 394954.1 for more information.

Once the command completes, ASMLib is now installed on the system.

Making the ASM Driver Available

Now that the ASMLib software is installed, a few steps have to be taken by the system administrator to make the ASM driver available. The ASM driver needs to be loaded, and the driver filesystem needs to be mounted. This is taken care of by the initialization script, /etc/init.d/oracleasm.

Run the /etc/init.d/oracleasm script with the 'configure' option. It will ask for the user and group that default to owning the ASM driver access point. If the database was running as the 'oracle' user and the 'dba' group, the output would look like this:

[root@ca-test1 /]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration [ OK ]
Creating /dev/oracleasm mount point [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks [ OK ]

This should load the oracleasm.o driver module and mount the ASM driver filesystem. By selecting enabled = 'y' during the configuration, the system will always load the module and mount the filesystem on boot.

The automatic start can be enabled or disabled with the 'enable' and 'disable' options to /etc/init.d/oracleasm:

[root@ca-test1 /]# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration [ OK ]
Unmounting ASMlib driver filesystem [ OK ]
Unloading module "oracleasm" [ OK ]

[root@ca-test1 /]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks [ OK ]

Creating/Deleting/Querying/Scanning ASM Disks

The system administrator has one last task. Every disk that ASMLib is going to be accessing needs to be made available. This is accomplished by creating an ASM disk. The /etc/init.d/oracleasm script is again used for this task:

[root@ca-test1 /]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdg1
Creating Oracle ASM disk "VOL1" [ OK ]

Disk names are ASCII capital letters, numbers, and underscores. They must start with a letter.

Disks that are no longer used by ASM can be unmarked as well:

[root@ca-test1 /]# /etc/init.d/oracleasm deletedisk VOL1
Deleting Oracle ASM disk "VOL1" [ OK ]

Any operating system disk can be queried to see if it is used by ASM:

[root@ca-test1 /]# /etc/init.d/oracleasm querydisk /dev/sdg1
Checking if device "/dev/sdg1" is an Oracle ASM disk [ OK ]
[root@ca-test1 /]# /etc/init.d/oracleasm querydisk /dev/sdh1
Checking if device "/dev/sdh1" is an Oracle ASM disk [FAILED]

Existing disks can be listed and queried:

[root@ca-test1 /]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
[root@ca-test1 /]# /etc/init.d/oracleasm querydisk VOL1
Checking for ASM disk "VOL1" [ OK ]

When a disk is added to a RAC setup, the other nodes need to be notified about it. Run the 'createdisk' command on one node, and then run 'scandisks' on every other node:

[root@ca-test1 /]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks [ OK ]

ASMLib uses discovery strings to determine what disks ASM is asking for. The generic Linux ASMLib uses glob strings. The string must be prefixed with "ORCL:". Disks are specified by name. A disk created with the name "VOL1" can be discovered in ASM via the discovery string "ORCL:VOL1". Similarly, all disks that start with the string "VOL" can be queried with the discovery string "ORCL:VOL*".

Disks cannot be discovered with path names in the discovery string. If the prefix is missing, the generic Linux ASMLib will ignore the discovery string completely, expecting that it is intended for a different ASMLib. The only exception is the empty string (""), which is considered a full wildcard. This is precisely equivalent to the discovery string "ORCL:*".

NOTE: Once you mark your disks with Linux ASMLib, Oracle Database 10g R1 (10.1) OUI will not be able to discover your disks. It is recommended that you complete a Software Only install and then use DBCA to create your database (or use the custom install).

3)Docid:249992.1

After this before running dbca to create database intance use these commands

You have two disk say "/dev/sdf" and "/dev/sdg"

Determine what those devices are bound as raw:

$ /usr/bin/raw -qa

If not:

Include devices in diskgroups by editing /etc/sysconfig/rawdevices :

eg /dev/raw/raw1 /dev/sdf

/dev/raw/raw2 /dev/sdg

Set owner, group and permission on device file for each raw device:

$ chown oracle:dba /dev/raw/rawn, chmod 660 /dev/raw/rawn

Bind disk devices to raw devices:

$ service rawdevices restart

So from DBCA you can see the device "raw1" and "raw2".

4)Then before running or if run dbca it will ask u to install cluster software it can be done by running script localconfig add in $ORACLE_HOME/bin by

./localconfig add

crsctl check crs - checks the viability of the CRS stack

crsctl check cssd - checks the viability of CSS

crsctl check crsd - checks the viability of CRS

crsctl check evmd - checks the viability of EVM

$asmcmd-to manage asm files through OS.

5)Then run dbca if u get the error ,cant copy /tmp/otatab to /etc/oratab then better recreate oratab by running root.sh

Other useful sites for rac,asm

http://www.oracledba.org/10g/asm/

Getting a Create statement of Object, Tablespace & User

for Object: -
set heading off;
set echo off;
set pages 999;
set long 90000;
spool ddl_file_name.sql
select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','USER_NAME') from dual;
spool off
/

for Tablespace: -
set heading off;
set echo off;
set pages 999;
set long 90000;
spool ddl_file_name.sql
select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;
spool off
/

for User: -
set heading off;
set echo off;
set pages 999;
set long 90000;
spool ddl_file_name.sql
select dbms_metadata.get_ddl('USER','USER_NAME') from dual;
spool off
/

for Database link: -

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;

(or)

set heading off;
set echo off;
set pages 999;
set long 90000;
spool db_link_name.sql
select dbms_metadata.get_ddl('DB_LINK','DB_LINK_NAME','USER_NAME') from dual;
spool off
/

*get the db_like name from the below v$dblink view.


Get the privileges from the below views: -
dba_sys_privs
dba_role_privs
dba_tab_privs
dba_ts_quotas

&

set pages 500
set lines 200
set long 100000
select dbms_metadata.get_ddl('USER','USER_NAME') from dual
UNION ALL
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','USER_NAME') from dual
UNION ALL
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','USER_NAME') from dual
UNION ALL
select dbms_metadata.get_granted_ddl('ROLE_GRANT','USER_NAME') from dual;

DBMS_SQLTUNE

ADVISOR privilege should be granted to user to use DBMS_SQLTUNE package

SQL>grant ADVISOR to test_user,

Creating Tuning Task

DECLARE
test_task_name VARCHAR2 (30);
test_sqltext CLOB;
BEGIN
test_sqltext := 'SELECT e.last_name, d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = :bnd';
test_task_name := dbms_sqltune.create_tuning_task (sql_text=> test_sqltext,
bind_list => sql_binds (anydata.convertnumber (9)),
user_name => 'TEST_USER',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_tuning_task',
description => 'Tuning Task'
);
END;
/


Executing SQL Tuning Task

BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'test_tuning_task');
END;
/


Checking Status of SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_tuning_task';


Retrieving results of SQL tuning task

SET LONG 1000
SET LONGCHUtestSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'test_tuning_task')
FROM DUAL;

Queries on Indexes

Tables which is not having Indexes: -

select OWNER,TABLE_NAME
from (select OWNER,TABLE_NAME from dba_tables minus
select TABLE_OWNER, TABLE_NAME from dba_indexes)
orasnap_noindex
where OWNER NOT IN ('SYS','SYSTEM')
order by OWNER,TABLE_NAME;

Tables which is having more then 5 Indexes: -

select OWNER,TABLE_NAME,COUNT(*) index_count
from dba_indexes
where OWNER NOT IN ('SYS','SYSTEM')
group by OWNER, TABLE_NAME
having COUNT(*) > 5
order by COUNT(*) desc, OWNER, TABLE_NAME;

Columns which is having more then one Index: -

select TABLE_OWNER,TABLE_NAME,COLUMN_NAME
from dba_ind_columns
where COLUMN_POSITION=1
and TABLE_OWNER not in ('SYS','SYSTEM')
group by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having count(*) > 1 ;

Columns which is having Primary Key: -

select OWNER, TABLE_NAME from dba_tables dt
where not exists
(select 'TRUE' from dba_constraints dc
where dc.TABLE_NAME = dt.TABLE_NAME and dc.CONSTRAINT_TYPE='P')
and OWNER NOT IN ('SYS','SYSTEM')
order by OWNER, TABLE_NAME;