OCP Blog
Super DBA
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: , , ,

Load XML file to oracle database in varchar2 type
by Vazha Mantua Wednesday, December 21, 2011 3:30 PM
    • Good Day all,
    • Today we show example of possibility of loading xml file to oracle10g database to text.
    • This method is very easy of getting some data from XML file to oracle database.
    • declare
    •   v_req   utl_http.req;
    •   v_resp  utl_http.resp;
    •   v_value VARCHAR2(1024);
    •   v_url varchar2(100);
  • begin
    • ---
    •   v_url:='http://192.0.0.16/test.xml';
    •   v_req := utl_http.begin_request(v_url);
    •   v_resp := utl_http.get_response(v_req);
    •   utl_http.read_text(v_resp, v_value);
    •   dbms_output.put_line(v_value);
    •   utl_http.end_response(v_resp);
    • ---
    • end;

Let see packages which we used on this example:

utl_http.begin_request:

Begins a new HTTP request. When the function returns, the UTL_HTTP  package has established the network connection to the target Web server, or the proxy server if a proxy server is to be used, and has sent the HTTP request line. The PL/SQL program should continue the request by calling some other API to complete the request.

utl_http.get_response:

Reads the HTTP response and processes the status line and HTTP response headers. The status code, reason phrase and the HTTP protocol version are stored in the response record.

utl_http.read_text:

Reads the HTTP response body in text form and returns the output in the caller-supplied buffer. The end_of_body exception will be raised if the end of the HTTP response body is reached. Text data is automatically converted from the response body character set to the database character set.

utl_http.end_response:

Ends the HTTP response completing the HTTP request and response. Unless a HTTP 1.1 persistent  connection is used in this request, the network connection is closed.

 

Tags: , ,

ORA-06544: PL/SQL: internal error, arguments: [55916]
by Vazha Mantua Saturday, December 17, 2011 12:10 PM

Good Day All,

Today we will discuss about error which appeared during import some data to oracle database release 11gR2 via impdp utility

when session try compile package we have got message like this

ORA-39083: Object type PACKAGE_BODY failed to create with error:
ORA-04052: error occurred when looking up remote object test_procedure
@TESTDB.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from TESTDB.WORLD

 

 

As you see error appear when oracle 11gR2 compile package which contain remote object dependence

Database version where is located test_procedure is 10.2.0.1, after upgrading it to 10.2.0.2 problem resolved. I think it’s a bug of version 11.2.0.1.

Tags: , ,

Oracle Database not started via srvctl with error CRS-2674, ORA-01034 if you installed Grid infrastructure with different user
by Vazha Mantua Monday, November 28, 2011 3:59 PM

if you try start database(version 11R2) with srvctl you should got error:

 

We got this case, when we created database with Database scripts in postDBCreation.sql

 

PRCR-1079 : Failed to start resource ora.test.db

CRS-5011: Check of resource "TEST" failed: details at "(:CLSN00007:)" in "/u0/app/grid/product/11.2.0/log/testmachine/agent/ohasd/oraagent_grid/oraagent_grid.log"

CRS-2674: Start of 'ora.test.db' on 'testmachine' failed

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

 

Cause of this error is that grid agent is running with difference user. During the instance startup using srvctl, agent tries to modify the files under $ORACLE_HOME/dbs and $ORACLE_HOME/srvm/admin,

But Unfortunately Grid user doesn't have write permissions to these directories, startup could fail.

Workaround:

You can add permission to write on directories for Grid user with commands:

chmod 775 /u0/app/oracle/product/11.2.0/dbs

chmod 775 /u0/app/oracle/product/11.2.0/srvm/admin

Tags: , , ,

ORA-15027: active use of diskgroup precludes its dismount
by Vazha Mantua Thursday, November 24, 2011 10:33 PM

Good Day,

Today We discuss about Error:ORA-15027: active use of diskgroup precludes its dismount.This error occurred  we I tried to  drop disk group on ASM instance (version 11GR2).

Cause of this error is that ASM spfile is located on Diskgroup

Solution:

In that case you need to create a pfile from the spfile and then startup the ASM instance using the pfile.

1.Find location of AMS spfile with command SQL> show parameter spfile

2.Create pfile from spfile

3.Start ASM instance with pfile, for Example SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora

4.Drop Diskgroup

Good Luck!

Tags:

Filter by APML

Calendar

<<  October 2017  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

TextBox