Script generate all user privileges in one report

• Posted by Vazha Mantua on May 14, 2012
• 0 comments

Good Day All.

Our task is get all privileges granted for user and create report based on this information. As you know privileges gives on user directly or on his role. Privileges divided by 2 part: system and object.

Let see example, create report for user Scott

1.Create repository table for store data:

create table vm_user_privs(privilege varchar2(100),user_name varchar2(100),object_name varchar2(100));

2. Main script

declare
cursor c_user is
select a.username from dba_users a
where a.username='SCOTT';
p_user varchar2(100);

begin
open c_user;
loop
fetch c_user into p_user;
insert into vm_user_privs
select /*+ rule */ a.privilege,p_user,a.table_name from dba_tab_privs a where a.grantee=p_user
union all
select /*+rule */ b.privilege,p_user,b.table_name from dba_tab_privs b where b.grantee in (select b1.granted_role from dba_role_privs b1 where b1.grantee=p_user)
union all
select /*+ rule */ c.privilege,p_user,null from dba_sys_privs c where c.grantee=p_user
union all
select /*+rule */ d.privilege,p_user,null from dba_sys_privs d where d.grantee in (select b2.granted_role from dba_role_privs b2 where b2.grantee=p_user);
commit;
EXIT WHEN c_user%NOTFOUND;
end loop;
close c_user;
end;

3. See result:

select * from vm_user_privs

Restore database to Asm from type backup to different server

• Posted by Vazha Mantua on April 20, 2012
• 2 comments

Today I show you step by step procedure, how we can restore database from UFS file system to ASM file system, if backup was done on type device and in different server. In your example we use VERITAS Netbackup soft.

We should change some parameters in our PFILEor SPFILE  for using ASM .

This parameters are:

control_files='+DG01/orcl1/controlfile/ctl.f' 

db_create_file_dest='+DG01'

You should install VERITAS Netbackup client and configure it for using. Also in your case we use Oracle RDBMS version 11.2. and OS Linux.

Then we should run RMAN script and set appropriate environment for restoring backup set from type.

ORACLE_SID=ORCL1
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NB_ORA_SERV=mastersrv   (This is master server for backuping)
NB_ORA_CLIENT=testmachine (This is source server, from this server we have made backup) 
export ORACLE_SID NLS_LANG NB_ORA_SERV NB_ORA_CLIENT
echo $ORACLE_SID
echo $NLS_LANG
echo $NB_ORA_SERV
echo $NB_ORA_CLIENT

rman target /

---- Startup Database on nomount Stage
run
{  
startup nomount;
}

--- Restore Controlfile from autobackup

SET DBID=295123130 (This is DBID of source database. you can find DBID on v$database view, or on controlfile format in netbackup catalog)

run
{
allocate channel ch1_tape type sbt;
set controlfile autobackup format for device type sbt to '%F';
restore controlfile  from autobackup;
RELEASE CHANNEL ch1_tape;
}

--- Open database with resetlogs clause and recreate temporary tablespace.

ALTER DATABASE OPEN RESETLOGS;

CREATE TEMPORARY TABLESPACE TEMP1;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

In your case we restore database, but not configure it for archiving.

for archiving you should simple set these parameters and then enable it.

alter system set db_recovery_file_dest_size = '400G';
alter system set db_recovery_file_dest = '+DG01' ;

Good Luck!

large number of trace in $ORACLE_BASE/grid/cv/log/

• Posted by Vazha Mantua on April 14, 2012
• 1 comments

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.

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

• Posted by Vazha Mantua on February 21, 2012
• 1 comments

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

• Posted by Vazha Mantua on January 16, 2012
• 2 comments

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

• Posted by Vazha Mantua on January 11, 2012
• 2 comments

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

Configuration Oracle database 11g for kerberos

• Posted by Vazha Mantua on January 9, 2012
• 2 comments

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<

Click the Other Params

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

Configure linux for using Asm 11gR2

• Posted by Vazha Mantua on January 4, 2012
• 24 comments

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

• Posted by Vazha Mantua on December 29, 2011
• 3 comments

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:

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

How to convert LONG column to CLOB

• Posted by Vazha Mantua on December 29, 2011
• 4 comments

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;