OCP Blog
Super DBA
Script generate all user privileges in one report
by Vazha Mantua Monday, May 14, 2012 12:09 PM

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
by Vazha Mantua Friday, April 20, 2012 12:04 PM

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;
}

--- Mount database
alter database mount;

--- Restore and Recover database with new ASM Storage.

In our database we have only 4 datafile. for your case you should add all datafiles in this script. In ASM  file system datafiles will be created with automatically generated name

run 
{
allocate channel t1 device type 'sbt_tape';
SET  NEWNAME FOR DATAFILE 1 TO NEW;
SET  NEWNAME FOR DATAFILE 2 TO NEW;
SET  NEWNAME FOR DATAFILE 3 TO NEW;
SET  NEWNAME FOR DATAFILE 4 TO NEW;
restore database;
SWITCH DATAFILE ALL;
recover database;
}

---- Rename Redo log files:
On source database redo logs were in location /u4/oradata/ORCL1.

SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo01.log'' TO ''+DG01/ORCL1/ONLINELOG/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo02.log'' TO ''+DG01/ORCL1/ONLINELOG/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo03.log'' TO ''+DG01/ORCL1/ONLINELOG/redo03.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo04.log'' TO ''+DG01/ORCL1/ONLINELOG/redo04.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo05.log'' TO ''+DG01/ORCL1/ONLINELOG/redo05.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo06.log'' TO ''+DG01/ORCL1/ONLINELOG/redo06.log'' ";

 

--- 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!

Installation Guide For Oracle TimesTen 11.2.1 In-Memory Database
by Vazha Mantua Thursday, August 25, 2011 5:38 PM

Good day all,

Today we will announced about a very nice type of database provided by Oracle. it’s Oracle TimesTen in memory database.

While installing a new technology, you can watch small presentation about this product, which I found on YouTube:

 

As you watched :

TimesTen is an in-memory relational database software product from Oracle. TimesTen is a high performance event-processing software component that enables applications to capture, store, use, and distribute information in real-time,
while preserving transactional integrity and continuous availability.

Full Articles about this product you can find on link: http://www.oracle.com/technetwork/database/timesten/overview/index.html

 

On This article we will provide you step by step procedure how install Oracle TimesTen 11.2.1 in Memory Database on Windows Platform 32 bit. Future We are going to publish some articles which deeply describe this product.

 

Let’s Start

 

Step 1: Install Software

First of all you should download installation package from Oracle:

You can download soft form link: http://www.oracle.com/technetwork/database/timesten/downloads/index.html

Run Setup.exe file , You can see in pictures steps , which you should done for installing software.

12

34

56

78

910

1112

13

After Installation software we should connect to DB

Step 2 Configure DSN

A Timesten Database is simply created by connecting to it, A first connection to an already-existing database results in loading the database in memory.Only A instance administrator can create TimesTen Database.

First of all We should configure system DSN for connecting to DB.

1. Go to control panel , then administrative Tools and Data Sources(ODBC)

2. Switch menu on System DSN part

3. Add System Data Source, named is my_ttdb

4. In Data Store Path browse directory in which you will store data

5. In Transaction Log Directory choose directory, where you will store log data.

6. Choose character set of DB, if you planning pin Oracle TimesTen Database on Oracle Database in future , choose character set, which you have an Oracle Database

7. In section First Connection you can set First connection attributes.

 

Step3 Connecting And Creating TimesTen Database

 

1. Enter ttisql at the OS Command prompt to launch ttIsql

2. For connecting to DB run connect “dns=my_ttdb”;

3. When the connect command returns , the database my_ttdb has been created and loaded into memory.

 

Now you can create users and object within database my_ttdb

 

Very useful instrument for TimesTen Database in SQL Developer. it’s very simple to use SQL Developer for TimesTen Database.

 

Status of Service you can get by command ttStatus from OS command prompt .

 

 

 

Tags: , , , , , ,

Once more scenario for library latch contention
by Vazha Mantua Friday, December 17, 2010 10:53 AM

In last posts we describe reason of library latch contention and the main reason was literal sql’s

 

Let’s see another scenario

it’ is version of SQL statements:

if two statements are textually identical but cannot be shared then these are called 'versions' of the same statement. If Oracle matches to a statement with many versions it has to check each version in turn to see if it is truely identical to the statement currently being parsed. Hence high version counts are best avoided by:

  • Standardizing the maximum bind lengths specified by the client
  • Avoid using identical SQL from lots of different schemas which use private objects. Eg: SELECT xx FROM MYTABLE; where each user has their own MYTABLE
  • Setting _SQLEXEC_PROGRESSION_COST to '0' in Oracle 8.1

 

In our case we have non standarting maximum m binds lengths

The most common causes are:
Bind Type mismatch

VARIABLE v1 VARCHAR2(60);
VARIABLE v1 VARCHAR2(30);

Language

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_LANGUAGE = �GERMAN';

SQL Trace

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Environmental variables that affect the optimizer

ALTER SESSION SET optimizer_mode = CHOOSE;
ALTER SESSION SET optimizer_mode = FIRST ROWS;

Note: how find SQL’s with many versions:

 

`

SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
order by version_count desc

Tags: , ,

Filter by APML

Calendar

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

View posts in large calendar

TextBox