Jaume Bernadó Blog

Migrate users with DBMS_METADATA

2008-04-05T21:10:00.006+02:00

Do you need to migrate users with all system and object grants ?. The DBMS_METADATA package helps us on Oracle 9i or higher. This script collects all information that you need:

-- DIR_OUTPUT: directory where create the output script file cr_users.sql.

create procedure EXTRACT_DDL_FOR_USERS as

no_grant exception;

pragma exception_init( no_grant, -31608 );
-- to skip the ORA-31608 when no data for the object requested.

cursor get_username is
select username
from dba_users;

file_handle utl_file.file_type;
stmt clob;

begin
file_handle:=utl_file.fopen('DIR_OUTPUT','cr_users.sql','w',32767);

for l_user in get_username loop

-- USERS
stmt := DBMS_METADATA.GET_DDL('USER', l_user.username);
utl_file.put(file_handle, stmt);

-- SYSTEM GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no system grants';
end;
utl_file.put(file_handle,stmt);

-- OBJECT GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no object grants';
end;
utl_file.put(file_handle,stmt);

-- ROLE GRANTS
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', l_user.username);
exception
when no_grant then stmt := '-- no role grants';
end;
utl_file.put(file_handle, stmt);

-- TABLESPACE QUOTAS
begin
stmt:=DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',l_user.username);
exception
when no_grant then stmt := '-- no tablespace quota';
end;
utl_file.put(file_handle, stmt);

-- DEFAULT ROLES
begin
stmt := DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', l_user.username);
exception
when no_grant then stmt := '-- no default role';
end;
utl_file.put(file_handle, stmt);

end loop;
utl_file.fclose(file_handle);
end;
/

Then you can execute it with:

set long 100000
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
extract_ddl_for_users;
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
end;
/

I use the SET_TRANSFORM_PARAM to add the terminator character statement (;).

Statistics on Dictionary tables

2008-03-29T20:41:00.003+01:00

Gathering statistics on dictionary tables (owned by 'sys') is a recommended 10g best practice. The automatic statistics gathering job performs this task, but if we use a customized statistics gathering job and switch off the default automatic job then the recommended strategy is don't switch off the automatic job and configure the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the procedure DBMS_STATS.SET_PARAM:

BEGIN
DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','ORACLE');
END;
/

with this configuration we can use our customized job and let the automatic job that runs during the maintenance window gather statistics on dictionary tables.

RMAN. To be or not to be about Oracle Backup

2007-12-09T17:53:00.000+01:00

I have found a lot of sites still using the user-managed backup method. We're in the RMAN century and I believe it's necessary migrate to RMAN for Oracle Backup&Recovery.

RMAN has a lot of useful functions that free the DBA of awful rutinary tasks. RMAN takes care of our backups and with a simple command you can backup your database without operating system scripts. RMAN provides block level corruption detection during backup and restore, optimize performance and space consumption with backup set compression. With 10g we have the new feature Flash Recovery Area, disk destination space for RMAN backup's that Oracle manage and it's not necessary to be worried about managing free space of our destination backup area. We have to dimension the Flash Recovery Area with the space needed for our backup policy retention.

There is a lot of information about RMAN but all that you must know is in "Backup and Recovery Advanced User’s Guide" that you can download of OTN

Oracle CPU Costing. More information for the optimizer.

2007-10-14T16:21:00.000+02:00

We collect object statistics, so the optimizer has information about the data distribution to calculate execution plans. But, how the optimizer can get information about our system load ? The answer is CPU costing gathering system statistics in the desired time interval.

Since Oracle 9i we can collect system statistics manually but it's in 10g where there are two different system statistics options:

No workload (default option): when we don't collect system statistics, the optimizer use CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED from AUX_STATS$ to calculate multiblock read time and single block read time.

Workload: when we collect system statistics, the AUX_STATS$ will have information of the system load to calculate multiblock read time, single block read time that reflects the system load.

We can see all of this with a simple example:

No workload: the AUX_STATS$ has these values stored in my system (10.2.0.3):

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 524.368
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

SQL> create table t1 as select * from all_objects;

Table created.

SQL> insert into t1 select * from all_objects;

41205 rows created.

SQL> R
1* insert into t1 select * from all_objects

41205 rows created.

SQL> R
1* insert into t1 select * from all_objects

41205 rows created.

SQL> R
1* insert into t1 select * from all_objects

41205 rows created.

SQL> R
1* insert into t1 select * from all_objects

41205 rows created.

SQL> commit;

Commit complete.

set autotrace traceonly explain statitics

SQL> select * from t1 where object_type = 'SYNONYM';

121176 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 123K| 11M| 795 (3)| 00:00:10 |
|* 1 | TABLE ACCESS FULL| T1 | 123K| 11M| 795 (3)| 00:00:10 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SYNONYM')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11442 consistent gets
0 physical reads
0 redo size
5584938 bytes sent via SQL*Net to client
89254 bytes received via SQL*Net from client
8080 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
121176 rows processed

Workload: gathering system statistics in the time interval when we want to collect, the AUX_STATS$ has in my system these values stored:

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 524.368
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 23.19
MREADTIM 39.333
CPUSPEED 2812
MBRC 14
MAXTHR 19456
SLAVETHR

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL>

SQL> select * from t1 where object_type = 'SYNONYM';

121176 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 123K| 11M| 432 (1)| 00:00:11 |
|* 1 | TABLE ACCESS FULL| T1 | 123K| 11M| 432 (1)| 00:00:11 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SYNONYM')

Statistics
----------------------------------------------------------
591 recursive calls
0 db block gets
11551 consistent gets
3467 physical reads
0 redo size
5584938 bytes sent via SQL*Net to client
89254 bytes received via SQL*Net from client
8080 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
121176 rows processed

When we have collected system statistics in the time interval that we consider like representative of the our system load:

exec dbms_stats.gather_system_stats('START');

..... interval time

exec dbms_stats.gather_system_stats('STOP');

The optimizer has more information about the system load that it uses to calculate a more realistic cost on our system.

The recommendation is to use system statistics with workload mode to supply the optimizer with this strategic information to find the more appropiate execution plan.

I have done more testing about this topic following the comment of David Pujol. System statistics can change execution plans, we can see it in this testcase.

I have collected system statistics in a representative workload with:

exec DBMS_STATS.CREATE_STAT_TABLE ('SYS','STAT_TAB','USERS');
exec dbms_stats.gather_system_stats('START', NULL, 'STAT_TAB');

.... period of time of a typical workload

exec dbms_stats.gather_system_stats('STOP', NULL, 'STAT_TAB');

SQL> create table t9 as select * from all_objects;

Table created.

SQL> insert into t9 select * from all_objects;

41205 rows created.

SQL> R
1* insert into t9 select * from all_objects

41205 rows created.

SQL> R
1* insert into t9 select * from all_objects

41205 rows created.

SQL> R
1* insert into t9 select * from all_objects

41205 rows created.

SQL> R
1* insert into t9 select * from all_objects

41205 rows created.
...

SQL> commit;

Commit complete.

create index i1_t9 on t1(object_type) nologging;

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>USER, tabname=> 'T9',estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'for columns size 20 OBJECT_TYPE',cascade=>true, granularity=>'all');

SQL> SELECT pname, pval1 FROM aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 803.781
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

SQL> select owner, object_name, object_type from t9;

Execution Plan
----------------------------------------------------------
Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3225K| 123M| 9861 (2)| 00:01:59 |
| 1 | TABLE ACCESS FULL| T9 | 3225K| 123M| 9861 (2)| 00:01:59 |
--------------------------------------------------------------------------

SQL> select owner, object_name, object_type from t9 where object_type='SCHEDULE';

Execution Plan
----------------------------------------------------------
Plan hash value: 533680601

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124K| 4846K| 7158 (1)| 00:01:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| T9 | 124K| 4846K| 7158 (1)| 00:01:26 |
|* 2 | INDEX RANGE SCAN | I1_T9 | 124K| | 348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='SCHEDULE')

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS('STAT_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT pname, pval1 FROM aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 803.781
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 3.423
MREADTIM 4.243
CPUSPEED 1421
MBRC 14
MAXTHR 13312
SLAVETHR

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select owner, object_name, object_type from t9;

Execution Plan
----------------------------------------------------------
Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3225K| 123M| 4176 (6)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T9 | 3225K| 123M| 4176 (6)| 00:00:15 |
--------------------------------------------------------------------------

SQL> select owner, object_name, object_type from t9 where object_type='SCHEDULE';

Execution Plan
----------------------------------------------------------
Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124K| 4846K| 4209 (7)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T9 | 124K| 4846K| 4209 (7)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SCHEDULE')

When we have system statistics in action (workload), the second query has changed the execution plan from INDEX RANGE SCAN to FULL SCAN. The system has more information about our system and can choose a more convenient execution plan.

Installing Oracle Database Vault 10.2.0.3

2007-09-21T20:13:00.000+02:00

Oracle Database Vault is an add-on to enterprise database software that places restrictions on what privileged users can do. It protects against insider security threats with separation of tasks between Database Administrator and Security Administrator. It's possible to meet regulatory compliance requirements too and all of these features are in the database software. It's not necessary to make complex developments to comply with these required security features.

I'm describing the installation process of Oracle Database Vault on RHEL4. If you are using ASM as the database storage option then you must install ASM in another Oracle Home Directory.

1.- Install Oracle ASM binaries in the ASM Home Directory. (e.g. /u01/app/oracle/product/10.2.0/ASM_1 )
2.- Install 10g Release 2 (10.2.0.3) Patch Set 2 in ASM Home Directory.
3.- Install Oracle Database binaries in the Database Home Directory. (e.g. /u01/app/oracle/product/10.2.0/db_1 )
4.- Install 10g Release 2 (10.2.0.3) Patch Set 2 in Database Home Directory.
5.- Create the ASM instance with dbca from the ASM Home Directory.
6.- Create the database with dbca from the Database Home Directory.
7.- Install Oracle Database Vault 10g Release 2 (10.2.0.3.0) in Database Home Directory.

When the Database Vault configuration assistant run I have got the following error messages in $ORACLE_HOME/cfgtoollogs/dvca_install.log file:

RULE_SYNC:java.sql.SQLException: ORA-24141: rule set DVSYS.DV$1 does not exist
ORA-06512: at "SYS.DBMS_RULE_ADM", line 188
ORA-06512: at "DVSYS.DBMS_MACADM", line 2701
ORA-06512: at line 1

It was solved setting the following variable prior to the installer being run:
$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII

The Security Administrator can define the enterprise security policy from the Oracle Database Vault Administrator utility (http::/dva).

You can see the product functionality with the "Oracle by Example" at the http://www.oracle.com/technology/deploy/security/database-security/database-vault/index.html, but then you need the Database Sample schemas. You can install the Database Sample Schemas with the Companion CD.

Installing Oracle 10gR2 on Oracle Enterprise Linux 5

2007-09-01T20:54:00.000+02:00

I have installed Oracle 10.2.0.3 on Oracle Enterprise Linux 5 with some issues that I'm describing below.

When I try to execute runInstaller, I have got the error message:

"libXp.so.6: cannot open shared object ..."

To solve this issue it's necessary to install libXp RPM from CD2.

To pass the system prerequisites step, I have followed the Metalink Note 456634.1.

To create an ASM Disk Group, I have modified the ASM_DISKSTRING parameter with the value
ASM_DISKSTRING= '/dev/oracleasm/disks/VOL*'

where VOL is the prefix of the ASM disks created with oracleasm utility.