OCP Blog
Super DBA
Welcome to OCP Blog
by vazha mantua Friday, April 20, 2012 10:00 AM

If you see this post it means that BlogEngine.NET 2.6 is running and the hard part of creating your own blog is done. There is only a few things left to do.

Tags: ,

large number of trace in $ORACLE_BASE/grid/cv/log/
by Vazha Mantua Saturday, April 14, 2012 11:50 AM

Good Day All,

Today searching large directories in Grid home I found a lot of trace files in directory $ORACLE_BASE/grid/cv/log/, cause of creating these files is cluvfy utility tracing, which by default is enabled in 11.2 version.it creates a new cvutrace.log everytime it gets executed.

But when it gets executed? Answer is: EM Grid Control agent collects metrics and needs to verify the status of the cluster, agent run command like this: $ORA_CRS_HOME/bin/cluvfy comp crs -display_status

The workaround is to modify the script, runcluvfy.sh or cluvfy script located in stage location of grid infrastructure to include SRVM_TRACE="false" at top of script, also you simple can delete these files.

 

Tags: , , , , ,

ORA-20222: ORA-24247: network access denied by access control list (ACL)
by Vazha Mantua Tuesday, February 21, 2012 1:14 PM

Good day all,

Let see a issue which appear after migration database from 10g to 11gR2

When executing DBMS_LDAP, UTL_INADDR (or) UTL_HTTP (or) any UTL packages after upgrading to 11gR1, fails with following error.

ORA-20222: ORA-24247: network access denied by access control list (ACL)

Cause of error is new security measure is introduced in Oracle 11g  for the following network-related PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL,  UTL_INADDR, DBMS_LDAP.By default, the ports are blocked.

We find Solution:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'http_service.xml',
description => 'HTTP ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'http_service.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'http_service.xml',
host => '*');
COMMIT;
END;

In our case user SCOTT has a privileges use http_service. If you want to open port for specific host on assign_acl part use direct IP instead of ‘*”

ORA-01078 ORA-29701 error during startup ASM
by Vazha Mantua Monday, January 16, 2012 11:48 AM

Good Day,

Today we resolve problem with error ORA-01078 and ORA-29701 while starting ASM , Oracle release 11gR2.

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 11:22:15 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

 

1.First of all check services by command

crs_stat –t

Than try start services manually by command:

crsctl start resource ora.cssd

-bash-3.2$ crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'Test'
CRS-2679: Attempting to clean 'ora.diskmon' on 'Test'
CRS-2681: Clean of 'ora.diskmon' on 'Test' succeeded
CRS-2672: Attempting to start 'ora.diskmon' on 'Test'
CRS-2676: Start of 'ora.diskmon' on 'Test' succeeded
CRS-2676: Start of 'ora.cssd' on 'Test' succeeded

2.Now try to start ASM instance

-bash-3.2$ export ORACLE_SID=+ASM
-bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 11:26:08 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size 2212656 bytes
Variable Size 256552144 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

3.Finally mount disk group : Find problematic diskgroup with statement:

select name, state, type from v$asm_diskgroup

SQL> alter diskgroup DATA mount;

Master process DM00 violated startup protocol. Master error: ORA-6533: Subscript beyond count
by Vazha Mantua Wednesday, January 11, 2012 10:49 AM

Today we will resolve problem which we had during export data from 10.2.04 database with expdp utility

We was trying epxort data with this command :

expdp scott/scott  DIRECTORY=scott  DUMPFILE=scott.dmp LOGFILE=scott.log SHEMAS=SCOTT

But unfortunately we got a message like this

drop ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SCOTT
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-6512: at "SYS.KUPV$FT", line 1201
ORA-39062: error creating master process DM00
ORA-39107: Master process DM00 violated startup protocol. Master error:
ORA-6533: Subscript beyond count

Resolve of problem

Cause of problem is public synonym with the same name as the user schema name performing the export

Find problematic synonym with command and drop it if it’s possible

select s.synonym_name
from dba_synonyms s
where s.owner = 'PUBLIC'
and s.synonym_name = SCOTT

 

Tags: , , , , , , , , , ,

Configuration Oracle database 11g for kerberos
by Vazha Mantua Sunday, January 8, 2012 11:26 PM

In this case we show plan which you should do on client and servers machines for configuration Kerberos,There are not shown task which you should do on Kerberos server, like install it, create principals,create service key table, etc.

------------YOU SHOULD DO ALL OF THESE STEPS ON DATABASE MACHINE AND ON CLIENT MACHINE ------------

1. Install Oracle Advanced security and net services. It will be done by oracle universal installer.

2. Configure Kerberos Authentication

Use Oracle Net Manager to perform the following steps to configure Kerberos authentication service parameters on the client and on the database server.

On windows run Net Manager, or Unix machine $ORACLE_HOME/bin/netmgr

2.1 Click the Authentication tab <From the Available Methods list, select KERBEROS5 <Move KERBEROS5 to the Selected Methods list<

kerb0001


Click the Other Params

kerb0002

2.2 Than You should create directory /krb5(with all permission)  and create file krb.conf .On client machine, if you use windows location of krb file is C:\Windows\krb.ini

Example of krb file:

[libdefaults]
    default_realm = DOMAIN.GE
    dns_lookup_realm = true
    dns_lookup_kdc = true
[realms] 
    DOMAIN.GE= {
        kdc = KERBEROS.DOMAIN.GE:88
    }
[domain_realm]
    .domain.ge = DOMAIN.GE

[logging]
        default = FILE:/tmp/krb5-kdc.log
        kdc = FILE:/tmp/krb5-kdc.log


2.3 update sqlnet.ora file

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=kservice
SQLNET.KERBEROS5_CONF_MIT = TRUE


2.4 Set the Initialization Parameter  OS_AUTHENT_PREFIX=""

3. Create an Externally Authenticated Oracle User

CREATE USER SCOTT IDENTIFIED EXTERNALLY AS 'scott@DOMAIN.GE';  

4. Copy Kerberos principal/secret key mapping file  

From system admin get this file and copy to on location /etc/v5srvtab

5.Get an Initial Ticket for the Kerberos/Oracle User

% okinit scott@DOMAIN.GE

sqlplus /@kservice  

As you see we get ticket form Kerberos and without entering password login in DB with username scott

Tags:

Configure linux for using Asm 11gR2
by Vazha Mantua Wednesday, January 4, 2012 12:42 PM

Good Day All.

Today we will discuss about configuration Linux for using ASM realize 11gR2.

Main difference between old and new versions of ASM is that now we need new groups and privileges on OS level for creating and managing ASM instances. Also Installation process is difference. For new realize we should install Grid infrastructure, but now we discuss about preparing work for using ASM in Linux.

Let start:

1.Create users,Groups,directories for Grid Infrastructure(grid user) and Oracle Software(oracle user)

groupadd –g 1000 oinstall
groupadd –g 1200 asmadmin
groupadd -g 1300 asmdba
groupadd -g 1301 asmoper

mkdir -p /u0/app/oracle
mkdir -p /u0/app/grid

useradd –s /bin/bash –d /u0/app/oracle –g oinstall –G asmadmin,asmdba,asmoper oracle
useradd –s /bin/bash –d /u0/app/grid –g oinstall –G asmadmin,asmdba,asmoper grid

chown –R oracle:oinstall /u0/app/oracle
chown –R grid:oinstall /u0/app/grid
chmod –R 775 /u0/app/oracle
chmod –R 775 /u0/app/grid
passwd oracle
passwd grid

2.Install ASM library driver

First of all you should get version of your OS with command uname –a

Than download and install packages

oracleasm-support-version.arch.rpm

oracleasm-kernel-version.arch.rpm

oracleasmlib-version.arch.rpm

Which you can find for your version of Linux in link: http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

3. Configure disk for ASM

we use multipathing feature, for using this feature for ASM you should:

Add to file /etc/sysconfig/oracleasm

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning

ORACLEASM_SCANORDER=”mapper/*”

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan

ORACLEASM_SCANEXCLUDE=”sd

For configure the on-boot properties of the Oracle ASM library driver:

/usr/sbin/oracleasm configure –I (Enter values: grid, asmadmin ,Y ,Y)

Load the kernel module using the following command:

/usr/sbin/oracleasm init

List of suitable disks you can find /sbin/fdisk –l

In your case we enable multipathing in Linux level,that’s why we have disk with name “mapper”.

/usr/sbin/oracleasm createdisk DATA11 /dev/mapper/mpath5

/usr/sbin/oracleasm createdisk DATA21 /dev/mapper/mpath6

We create 2 disk’s candidate for member of ASM instance,named DATA11 and DATA21,which are created.

command to refresh the ASM disk configuration: /usr/sbin/oracleasm scandisks

Invisible index in oracle 11g, parameter OPTIMIZER_USE_INVISIBLE_INDEXES
by Vazha Mantua Thursday, December 29, 2011 12:37 PM

Good Day All, Today we will discuss about very interesting feature which is added in oracle 11g.

This is invisible indexes. Invisible indexes like any other indexes, but they ignored by the optimizer unless explicitly specified.
If the value of parameter optimizer_use_invisible_indexes is true on session level,
for this session optimizer ignored status of visibility index and use it in SQL_PLAN.

Let See Example:


First of all we create table:

create table NY_TABLE (person varchar2(50), department varchar2(20),salary number )
--------

Than we insert into table 1 row.

Insert into NY_TABLE values ('JONH SMITH','IT', 20000)
Commit;

--------
After this we insert 10000 test rows:

BEGIN
FOR i IN 1.. 10000 LOOP
  INSERT INTO NY_TABLE VALUES ('TEST','TEST',1);
END LOOP;
COMMIT;
END;
--------
Now we are gathering table statistic:

BEGIN
dbms_stats.gather_table_stats(SCOTT,'NY_TABLE',cascade => true,degree => 4);
END;

--------
We create index with invisible clause with command

create index NY_TABLE_I1 on NY_TABLE(DEPARTMENT) invisible

--------
Let see SQL_PLAN:

select * from NY_TABLE where department='IT'

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NY_TABLE | 1 | 3 | 6 (0)| 00:00:01 |

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

As you see optimizer ignored index. Now we change status of index from invisible to visible

alter index NY_TABLE_I1 visible;
select * from NY_TABLE where department='IT'

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NY_TABLE_I1 | 1 | 3 | 2 (0)| 00:00:01 |

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


And we see, that in this case index is used in SQL_PLAN
Let see example when OPTIMIZER_USE_INVISIBLE_INDEXES is set true.
---------

alter index NY_TABLE_I1 invisible;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select * from NY_TABLE where department='IT'

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| NY_TABLE_I1 | 1 | 3 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

In result we see that index is invisible, but it used by optimizer, reason of this is parameter SET OPTIMIZER_USE_INVISIBLE_INDEXES.

---------
Status of visibility of index you can find with command :select visibility from dba_indexes

Tags: , , , ,

How to convert LONG column to CLOB
by Vazha Mantua Wednesday, December 28, 2011 4:24 PM

Today We will show 2 methods how convert long column to clob.

METHOD I: Simple method using TO_LOB procedure:

CREATE TABLE NBA_LONG (ID NUMBER, TEXT_L LONG);
CREATE TABLE NBA_CLOB (ID NUMBER, TEXT_C CLOB);

INSERT INTO NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
INSERT INTO NBA_CLOB SELECT ID, TO_LOB(TEXT_L) FROM NBA_LONG;
COMMIT;

As you see in table NBA_CLOB column text_c contain information of column text_l from table NBA_LONG.

----


METHOD II: With using DBMS_REDEFINITION package


CREATE TABLE SCOTT.NBA_LONG (ID NUMBER PRIMARY KEY, TEXT_L LONG);
CREATE TABLE SCOTT.NBA_CLOB (ID NUMBER, TEXT_C CLOB);
INSERT INTO SCOTT.NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
COMMIT;

 

DECLARE
VM BINARY_INTEGER;
BEGIN
--VERIFY THAT THE TABLE NBA_LONG IS A CANDIDATE FOR ON LINE REDEFINITION
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','NBA_LONG');
--START THE REDEFINITION PROCESS
DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','NBA_LONG','NBA_CLOB','ID ID,TO_LOB(TEXT_L) TEXT_C', DBMS_REDEFINITION.CONS_USE_PK);
--AUTOMATICALLY CREATE ANY TRIGGERS, INDEXES AND CONSTRAINTS ON NBA_CLOB
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','NBA_LONG','NBA_CLOB', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, VM);
--OPTIONALLY FOR SYNCHRONIZE
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
-- COMPLETE THE REDEFINITION
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
END;

Tags: , , , , , , ,

virtual column in oracle 11g database
by Vazha Mantua Monday, December 26, 2011 9:04 PM

Good Day All,

In this article we show you example of virtual indexes in oracle 11g database. When queried table virtual columns appear to be normal table columns, but their values are not stored in disk, They are generated at runtime. but of course, any indexes that create on virtual columns will require storage space.

Let see example

create table vm_table_with_vc
(id number,
person_name varchar2(30),
age number,
age_virtual NUMBER GENERATED ALWAYS AS (age*2) virtual
);

 

As you see column age_virtual is virtual column, which multiply value of “age” column in 2 times.

Now we can insert into table and see results:

insert into vm_table_with_vc(id,person_name,age) values (1,'Lionel Messi',24);
commit;

select * from vm_table_with_vc and result is:

ID PERSON_NAME AGE AGE_VIRTUAL
1 Lionel Messi 24 48

As you see in column “age_virtual” is automatically shown value=24*2=48

We can simple create index on virtual columns and this index will be like as function-based indexes.

create index vm_table_with_vc_I1 on vm_table_with_vc(age_virtual);

If we select dba_segments view ,we will check that index is stored in database.

 

Tags: , , ,

Filter by APML

Calendar

<<  December 2014  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox