Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

12.2 New Feature: the FLEX ASM disk group part 4

Flex Disk Group Properties

In the previous 3 parts I shared my investigation into ASM Flex Disk Groups, Quota Groups, File Groups, and how Quota Groups actually enforce space limits. What I haven’t discussed yet was changing properties of a File Group and the effects thereof. Properties I have in mind are related to the protection level, as discussed in the official documentation-Automatic Storage Management Administrator’s Guide, Administering Oracle ASM Disk Groups. There are of course other properties as well (and you’ll find a link to all of the modifiable properties later in this post), but they are out of scope for this investigation.

A disk group with Flex Redundancy can be set up with all protection levels (3-way mirror, 2-way mirror, unprotected), and by default uses 2-way mirroring. Unlike other types of disk groups, you can change the protection levels for individual (pluggable) databases within the Flex Disk group. This is best shown with an example. Continuing the story from my previous blog posts, here’s the setup again for your convenience.

SQL> select filegroup_number,name,guid from v$asm_filegroup

FILEGROUP_NUMBER NAME                 GUID
---------------- -------------------- --------------------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         4700A987085A3DFAE05387E5E50A8C7B
               2 CDB_PDB$SEED         536DF51E8E28221BE0534764A8C0FD81
               3 PDB1                 537B677EF8DA0F1AE0534764A8C05729
               4 ORCL_CDB$ROOT        4700A987085A3DFAE05387E5E50A8C7B
               5 ORCL_PDB$SEED        537E63B952183748E0534764A8C09A7F
               6 PDB1_0001            537EB5B87E62586EE0534764A8C05530

7 rows selected. 

The above listing shows all my File Groups in my ASM instance. This post is about changing attributes of filegroup number 6, PDB1_0001. It will be important to understand which files pertain to the filegroup later; here’s the list:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;
  
FILE_NUMBER  BYTES      SPACE      TYPE      REDUNDANCY  REDUNDANCY_LOWERED  STRIPED  REMIRROR
309          104865792  218103808  DATAFILE  MIRROR      U                   COARSE   N
310          262152192  541065216  DATAFILE  MIRROR      U                   COARSE   N
311          419438592  859832320  DATAFILE  MIRROR      U                   COARSE   N
312          67117056   142606336  TEMPFILE  MIRROR      U                   COARSE   N

The documentation is correct: the default redundancy for the datafiles and tempfile is “mirror”.

Filegroup properties

The ability to change redundancy and other properties within the disk group hinges on the fact that you have File Groups. Properties that belong to a File Group can be listed either via the SQL interface, or asmcmd. The latter is shown first:

ASMCMD> lsfg -G flex --filegroup PDB1_0001
File Group  Disk Group  Property    Value   File Type                  
PDB1_0001   FLEX        PRIORITY    MEDIUM                             
PDB1_0001   FLEX        STRIPING    COARSE  CONTAINER                  
PDB1_0001   FLEX        STRIPING    FINE    CONTROLFILE                
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DATAFILE                   
PDB1_0001   FLEX        STRIPING    COARSE  DATAFILE                   
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ONLINELOG                  
PDB1_0001   FLEX        STRIPING    COARSE  ONLINELOG                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ARCHIVELOG                 
PDB1_0001   FLEX        STRIPING    COARSE  ARCHIVELOG                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  TEMPFILE                   
PDB1_0001   FLEX        STRIPING    COARSE  TEMPFILE                   
PDB1_0001   FLEX        REDUNDANCY  MIRROR  BACKUPSET                  
PDB1_0001   FLEX        STRIPING    COARSE  BACKUPSET                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  PARAMETERFILE              
PDB1_0001   FLEX        STRIPING    COARSE  PARAMETERFILE              
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DATAGUARDCONFIG            
PDB1_0001   FLEX        STRIPING    COARSE  DATAGUARDCONFIG            
PDB1_0001   FLEX        REDUNDANCY  MIRROR  CHANGETRACKING             
PDB1_0001   FLEX        STRIPING    COARSE  CHANGETRACKING             
PDB1_0001   FLEX        REDUNDANCY  MIRROR  FLASHBACK                  
PDB1_0001   FLEX        STRIPING    COARSE  FLASHBACK                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DUMPSET                    
PDB1_0001   FLEX        STRIPING    COARSE  DUMPSET                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUTOBACKUP                 
PDB1_0001   FLEX        STRIPING    COARSE  AUTOBACKUP                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  VOTINGFILE                 
PDB1_0001   FLEX        STRIPING    COARSE  VOTINGFILE                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  OCRFILE                    
PDB1_0001   FLEX        STRIPING    COARSE  OCRFILE                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ASMVOL                     
PDB1_0001   FLEX        STRIPING    COARSE  ASMVOL                     
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ASMVDRL                    
PDB1_0001   FLEX        STRIPING    COARSE  ASMVDRL                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  OCRBACKUP                  
PDB1_0001   FLEX        STRIPING    COARSE  OCRBACKUP                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  FLASHFILE                  
PDB1_0001   FLEX        STRIPING    COARSE  FLASHFILE                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  XTRANSPORT BACKUPSET       
PDB1_0001   FLEX        STRIPING    COARSE  XTRANSPORT BACKUPSET       
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUDIT_SPILLFILES           
PDB1_0001   FLEX        STRIPING    COARSE  AUDIT_SPILLFILES           
PDB1_0001   FLEX        REDUNDANCY  MIRROR  INCR XTRANSPORT BACKUPSET  
PDB1_0001   FLEX        STRIPING    COARSE  INCR XTRANSPORT BACKUPSET  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  KEY_STORE                  
PDB1_0001   FLEX        STRIPING    COARSE  KEY_STORE                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUTOLOGIN_KEY_STORE        
PDB1_0001   FLEX        STRIPING    COARSE  AUTOLOGIN_KEY_STORE        
PDB1_0001   FLEX        REDUNDANCY  MIRROR  CONTAINER                  
PDB1_0001   FLEX        REDUNDANCY  HIGH    CONTROLFILE                
ASMCMD> 

This output shows 2 properties per ASM-supported file type: redundancy and striping. I really only care about redundancy, and I haven’t ever touched the striping property. Quoting the ASM Administrator’s guide, chapter Administering Oracle ASM Disk Groups, section Managing Oracle ASM Flex Disk Groups about the STRIPING property:

This is a file type property, and is set for each file type. Usually the default value for each file type is sufficient and is not changed

I’m happy to go with that.

The SQL interface can provide the same information, and here is the equivalent output:

SQL> select file_type, name, value from v$asm_filegroup_property where filegroup_number = 6;

FILE_TYPE                      NAME                           VALUE
------------------------------ ------------------------------ ------------------------------
                               PRIORITY                       MEDIUM
CONTROLFILE                    REDUNDANCY                     HIGH
CONTROLFILE                    STRIPING                       FINE
DATAFILE                       REDUNDANCY                     MIRROR
DATAFILE                       STRIPING                       COARSE
ONLINELOG                      REDUNDANCY                     MIRROR
ONLINELOG                      STRIPING                       COARSE
ARCHIVELOG                     REDUNDANCY                     MIRROR
ARCHIVELOG                     STRIPING                       COARSE
TEMPFILE                       REDUNDANCY                     MIRROR
TEMPFILE                       STRIPING                       COARSE
BACKUPSET                      REDUNDANCY                     MIRROR
BACKUPSET                      STRIPING                       COARSE
PARAMETERFILE                  REDUNDANCY                     MIRROR
PARAMETERFILE                  STRIPING                       COARSE
DATAGUARDCONFIG                REDUNDANCY                     MIRROR
DATAGUARDCONFIG                STRIPING                       COARSE
CHANGETRACKING                 REDUNDANCY                     MIRROR
CHANGETRACKING                 STRIPING                       COARSE
FLASHBACK                      REDUNDANCY                     MIRROR
FLASHBACK                      STRIPING                       COARSE
DUMPSET                        REDUNDANCY                     MIRROR
DUMPSET                        STRIPING                       COARSE
AUTOBACKUP                     REDUNDANCY                     MIRROR
AUTOBACKUP                     STRIPING                       COARSE
VOTINGFILE                     REDUNDANCY                     MIRROR
VOTINGFILE                     STRIPING                       COARSE
OCRFILE                        REDUNDANCY                     MIRROR
OCRFILE                        STRIPING                       COARSE
ASMVOL                         REDUNDANCY                     MIRROR
ASMVOL                         STRIPING                       COARSE
ASMVDRL                        REDUNDANCY                     MIRROR
ASMVDRL                        STRIPING                       COARSE
OCRBACKUP                      REDUNDANCY                     MIRROR
OCRBACKUP                      STRIPING                       COARSE
FLASHFILE                      REDUNDANCY                     MIRROR
FLASHFILE                      STRIPING                       COARSE
XTRANSPORT BACKUPSET           REDUNDANCY                     MIRROR
XTRANSPORT BACKUPSET           STRIPING                       COARSE
AUDIT_SPILLFILES               REDUNDANCY                     MIRROR
AUDIT_SPILLFILES               STRIPING                       COARSE
INCR XTRANSPORT BACKUPSET      REDUNDANCY                     MIRROR
INCR XTRANSPORT BACKUPSET      STRIPING                       COARSE
KEY_STORE                      REDUNDANCY                     MIRROR
KEY_STORE                      STRIPING                       COARSE
AUTOLOGIN_KEY_STORE            REDUNDANCY                     MIRROR
AUTOLOGIN_KEY_STORE            STRIPING                       COARSE
CONTAINER                      REDUNDANCY                     MIRROR
CONTAINER                      STRIPING                       COARSE

49 rows selected.

In addition to v$asm_file you can also query the new view v$asm_filegroup_file for information about files in File Groups:

SQL> select filegroup_number, file_number, incarnation
  2  from v$asm_filegroup_file
  3  where filegroup_number = 6
  4  order by file_number;

FILEGROUP_NUMBER FILE_NUMBER INCARNATION
---------------- ----------- -----------
               6         309   948464269
               6         310   948464269
               6         311   948464269
               6         312   948464283

SQL> 

FILE_NUMBER and INCARNATION can be used to link back to v$asm_file by the way.

Back to the blog post: I wanted to increase the redundancy level from normal redundancy to high redundancy, but only within filegroup 6. There are a number of useful attributes in v$asm_file that provide information about the size, type, current redundancy, stripe levels and whether a re-mirror operation is taking place.

Before making any changes, this is what is looks like:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  218103808 DATAFILE             MIRROR U COARSE N
        310  262152192  541065216 DATAFILE             MIRROR U COARSE N
        311  419438592  859832320 DATAFILE             MIRROR U COARSE N
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

SQL>

Files 309 through 312 use a redundancy of MIRROR, which is 2-way mirroring aka normal redundancy. Let’s try and change this and see what happens.

Altering the datafile.redundancy

This is where the action starts for real. All the filegroup properties that can be changed are documented in the Automatic Storage Management Administrator’s Guide, Administering Oracle ASM Disk Groups chapter, section Managing Oracle ASM Flex Disk Groups. Using the documented example, I can change the redundancy of my data files in filegroup 6:

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high';

Diskgroup altered.

This command, like all others that change the properties of storage, must be executed from the ASM instance as SYSASM, or else it fails as shown here when I tried to execute if from the RDBMS instance.

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy' = 'high';

Error starting at line : 1 in command -
alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy' = 'high'
Error report -
ORA-15000: command disallowed by current instance type
15000. 00000 -  "command disallowed by current instance type"
*Cause:    The user has issued a command to a conventional RDBMS instance
           that is only appropriate for an ASM instance. Alternatively, the
           user has issued a command to an ASM instance that is only
           appropriate for an RDBMS instance.
*Action:   Connect to the correct instance type and re-issue the command.
SQL> 

Immediately after this command completes data files in filegroup 6 are re-mirrored. While the re-mirror operation is ongoing (see flag remirror=’Y’) the redundancy is still set to a value of MIRROR. Only when it has completed (remirror = ‘N’) is the redundancy changed to HIGH. Note that file 312 in the filegroup still is set to NORMAL redundancy. On second look you’ll notice it’s ok because it is a tempfile, not a data file. And I only asked for datafiles to be protected by high redundancy.

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  339738624 DATAFILE             MIRROR U COARSE Y
        310  262152192  805306368 DATAFILE             MIRROR U COARSE Y
        311  419438592 1283457024 DATAFILE             MIRROR U COARSE Y
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  339738624 DATAFILE             HIGH   U COARSE N
        310  262152192  805306368 DATAFILE             HIGH   U COARSE N
        311  419438592 1283457024 DATAFILE             HIGH   U COARSE N
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

To complete the post I’ll list the ASM instance’s alert.log as it’s quite verbose and I find it interesting to see what activities ASM performs.

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high'
NOTE: updated format of group 5 file 311 for 3-way mirroring
NOTE: updated redundancy of group 5 file 311 to 3-way mirrored (remirror 0x4)
NOTE: updated format of group 5 file 310 for 3-way mirroring
NOTE: updated redundancy of group 5 file 310 to 3-way mirrored (remirror 0x4)
NOTE: updated format of group 5 file 309 for 3-way mirroring
NOTE: updated redundancy of group 5 file 309 to 3-way mirrored (remirror 0x4)
NOTE: GroupBlock outside rolling migration privileged region
NOTE: client +ASM1:+ASM:rac122pri no longer has group 5 (FLEX) mounted
NOTE: client +ASM1:+ASM:rac122pri no longer has group 3 (DATA) mounted
NOTE: client +ASM1:+ASM:rac122pri no longer has group 2 (MGMT) mounted
NOTE: requesting all-instance membership refresh for group=5
NOTE: membership refresh pending for group 5/0x4718f00c (FLEX)
GMON querying group 5 at 835 for pid 25, osid 11576
SUCCESS: refreshed membership for 5/0x4718f00c (FLEX)
SUCCESS: alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high'
2017-07-06 13:17:47.169000 +01:00
NOTE: Attempting voting file refresh on diskgroup FLEX
NOTE: Refresh completed on diskgroup FLEX. No voting file found.
NOTE: starting rebalance of group 5/0x4718f00c (FLEX) at power 1
NOTE: starting process ARBA
Starting background process ARBA
ARBA started with pid=33, OS id=9904
NOTE: starting process ARB0
Starting background process ARB0
ARB0 started with pid=48, OS id=9906
NOTE: assigning ARBA to group 5/0x4718f00c (FLEX) to compute estimates
NOTE: assigning ARB0 to group 5/0x4718f00c (FLEX) with 1 parallel I/O
2017-07-06 13:18:29.554000 +01:00
NOTE: Starting expel slave for group 5/0x4718f00c (FLEX)
NOTE: stopping process ARB0
NOTE: stopping process ARBA
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=5
SUCCESS: rebalance completed for group 5/0x4718f00c (FLEX)
NOTE: membership refresh pending for group 5/0x4718f00c (FLEX)
GMON querying group 5 at 836 for pid 25, osid 11576
SUCCESS: refreshed membership for 5/0x4718f00c (FLEX)
2017-07-06 13:18:32.568000 +01:00
NOTE: Attempting voting file refresh on diskgroup FLEX
NOTE: Refresh completed on diskgroup FLEX. No voting file found.

The re-mirroring operation is reported as a (mini) rebalance operation in the ASM instance’s alert.log. Thinking about it this makes perfect sense.

What about new files?

The change in meta-data is also visible in v$asm_filegroup_properties. Every new data file created in my PDB will from now on be created with high redundancy.

SQL> select file_type, name, value from v$asm_filegroup_property
  2  where filegroup_number = 6 and file_type = 'DATAFILE';

FILE_TYPE       NAME                 VALUE
--------------- -------------------- --------------------
DATAFILE        REDUNDANCY           HIGH
DATAFILE        STRIPING             COARSE

And indeed: after adding a USERS-tablespace the new properties are enforced:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6
  3  order by file_number;

FILE_NUMBER      BYTES      SPACE TYPE            REDUND R STRIPE R
----------- ---------- ---------- --------------- ------ - ------ -
        309  167780352  528482304 DATAFILE        HIGH   U COARSE N
        310  272637952  843055104 DATAFILE        HIGH   U COARSE N
        311  471867392 1434451968 DATAFILE        HIGH   U COARSE N
        312   67117056  142606336 TEMPFILE        MIRROR U COARSE N
        313 2147491840 6467616768 DATAFILE        HIGH   U COARSE N

Summary

Flex ASM Disk Groups continue to amaze me. Using the File Group properties I can now define entities (NCDB, CDB, PDB, …) and provide flexible, highly granular settings for data protection within the disk group. In previous releases I could do similar things, but that required multiple disk groups and was more complex to do so.

@martinberx posted an interesting comment about Flex ASM Disk Groups and redundancy levels on twitter, comparing Flex ASM Disk Groups with ASM File Templates. Using ASM File Templates one could define redundancy levels of files within an ASM disk group amongst other things, a feature which has been with ASM for a long time. Technically it is possible to define a template for data files mandating 3-way mirroring even if the file was stored on a normal redundancy disk group.

My colleague Alex Fatkulin has blogged about high redundancy files in normal redundancy disk groups and shows why ASM File Templates ultimately don’t offer better availability. I’ll try and get a test case together for my Flex ASM Disk Group to see if it fares better. According to the documentation, a Flex ASM Disk Group with 5 or more failgroups should be able to survive the failure of 2 failgroups (just like a disk group with High Redundancy).

How Oracle stores numbers internally

Before you proceed, please check out this short article written by Tanel Poder:
http://blog.tanelpoder.com/2010/09/02/which-number-takes-more-space-in-an-oracle-row/

In the documentation, you can find the following explanation about the internal numeric format:

Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

So how to decode a number from hex value, that can be found in a datafile or archivelog?
Let’s take a value c3020102 which is 10001.

The first byte (c3) is exponent… but wait? c3 = 195. And 10^195 would be a little higher than 10001 </p />
</p></div>

    	  	<div class=

How Oracle stores numbers internally

Before you proceed, please check out this short article written by Tanel Poder:
http://blog.tanelpoder.com/2010/09/02/which-number-takes-more-space-in-an-oracle-row/

In the documentation, you can find the following explanation about the internal numeric format:

Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

So how to decode a number from hex value, that can be found in a datafile or archivelog?
Let’s take a value c3020102 which is 10001.

The first byte (c3) is exponent… but wait? c3 = 195. And 10^195 would be a little higher than 10001 </p />
</p></div>

    	  	<div class=

Get trace file from server to client

The old way to get a user dump trace file, for sql_trace (10046), Optimizer compilation trace (10053), lock trace (10704), Optimizer execution trace (10507),… is to go to the server trace directory. But if you don’t have access to the server (as in the ☁) the modern (12cR2) way is to select from V$DIAG_TRACE_FILE_CONTENTS. Before everybody is on 12.2 I’m sharing here a sqlplus script that I use for a long time to get the trace file to the client.

This is a script to run with sqlplus passing as a parameter the name of the trace file you want to write to (because the original name on the server is not very meaningful). I close all cursors to get all info such as STAT lines. I get the trace file name from v$diag_info. If a directory exists I use it. If not I create one, named UDUMP, which I’ll remove at the end. Then the file is read by utl_file and spooled on client though dbms_output. At the end, I remove the trace file from the server.

Here is the script – comments welcome.

set serveroutput on feedback off verify off termout off linesize 1024 trimspool on echo off
spool &1..trc
declare
fd utl_file.file_type;
line varchar2(1024);
l_tracename varchar2(512);
l_directory_path all_directories.directory_path%TYPE;
l_directory_name all_directories.directory_name%TYPE;
l_directory_created boolean;
procedure t (msg in varchar2) is begin dbms_output.put_line(msg); end;
begin
/* use old parameter _cached_open_cursors to close all open cursors */
for r in (select 1 from v$session_cursor_cache where count>0) loop
dbms_session.set_close_cached_open_cursors(true);
rollback;
commit;
dbms_session.set_close_cached_open_cursors(false);
end loop;
/* get trace directory and trace file name */
select value into l_directory_path from v$diag_info where name='Diag Trace';
select substr(replace(value,l_directory_path,''),2) into l_tracename from v$diag_info where name='Default Trace File';
/* get directory name for it, or try to create it */
l_directory_created:=false;
begin
select directory_name into l_directory_name from all_directories where directory_path = l_directory_path and rownum=1;
exception
when no_data_found then
begin
l_directory_name:='UDUMP';
execute immediate 'create directory '||l_directory_name||' as '''||l_directory_path||'''';
l_directory_created:=true;
exception when others then
raise_application_error(-20000,'You must CREATE DIRECTORY '||l_directory_name||' as ''' ||l_directory_path||'''; or be granted CREATE DIRECTORY.');
end;
end;
/* opens the trace file */
begin
fd:=utl_file.fopen(l_directory_name,l_tracename,'R');
exception when others then
raise_application_error(-20001,'Impossible to open file: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
/* read the trace file and prints it */
begin
loop
begin
utl_file.get_line(fd,line);
dbms_output.put_line(line);
exception
when no_data_found then exit;
when others then
dbms_output.put_line('!!! error while reading file '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
end loop;
/* close and remove the file from the server */
utl_file.fclose(fd);
utl_file.fremove(l_directory_name,l_tracename);
exception
when others then
raise_application_error(-20002,'Impossible to remove: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
begin
/* drop directory if created */
if l_directory_created then execute immediate 'drop directory '||l_directory_name; end if;
exception
when others then
raise_application_error(-20002,'Impossible to remove directory: '||l_directory_name||' ( '||l_directory_path||' )'||sqlerrm);
end;
end;
/
spool off

 

Cet article Get trace file from server to client est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths VIII – Index Scan and Filter

In the previous post we have seen a nice optimization to lower the consequences of bad correlation between the index and the table physical order: a bitmap, which may include false positives and then requires a ‘recheck’ of the condition, but with the goal to read each page only once. Now we are back to the well-clustered table where we have seen two possible access paths: IndexOnlyScan when all columns we need are in the index, and IndexScan when we select additional columns. Here is a case in the middle: the index does not have all the columns required by the select, but can eliminate all rows.

The table created is:

create table demo1 as select generate_series n , 1 a , lpad('x',1000,'x') x from generate_series(1,10000);
SELECT 10000
create unique index demo1_n on demo1(n);
CREATE INDEX
vacuum demo1;
VACUUM

Index Only Scan and Filter

I use only one column (N), which is indexed, in the SELECT clause and the WHERE clause. And this WHERE clause is silly: in addition to the n<=1000 I've used in previous post to focus on 10% of rows, I add a condition which is always false: mod(n,100)=1000

explain (analyze,verbose,costs,buffers) select n from demo1 where n<=1000 and mod(n,100)=1000 ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..38.78 rows=5 width=4) (actual time=0.276..0.276 rows=0 loops=1)
Output: n
Index Cond: (demo1.n <= 1000)
Filter: (mod(demo1.n, 100) = 1000)
Rows Removed by Filter: 1000
Heap Fetches: 0
Buffers: shared hit=5
Planning time: 0.454 ms
Execution time: 0.291 ms

Index Only Scan is used here because no other columns are used. The n<=1000 is the access condition (Index Cond.) doing a range scan on the index structure. The mod(n,100)=1000 is a filter predicate which is applied to the result of the index access (Filter) and we have additional information that the 1000 rows selected by the access predicate have been filtered out (Rows Removed by Filter). During the execution, 5 index buffers have been read for the range scan (branches + leaves). Because I vacuumed any changes, the visibility map knows that all rows can be displayed and there are no blocks to read from the table (Heap Fetches).

Now I’ll select another column in order to see an Index Scan. We have seen in the previous post that the huge cost of index access is the access to the table. Filtering most of the rows from the index entries is the most common recommendation to optimize a query. And my example here is running the extreme case: a predicate on the indexed column removes all rows.

Index Scan and Filter

I’ve just changed the ‘select n’ to ‘select a':


explain (analyze,verbose,costs,buffers) select a from demo1 where n<=1000 and mod(n,100)=1000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using demo1_n on public.demo1 (cost=0.29..184.78 rows=5 width=4) (actual time=0.427..0.427 rows=0 loops=1)
Output: a
Index Cond: (demo1.n <= 1000)
Filter: (mod(demo1.n, 100) = 1000)
Rows Removed by Filter: 1000
Buffers: shared hit=147
Planning time: 0.434 ms
Execution time: 0.440 ms

I can understand that the cost is higher. The optimizer may not know that mod(n,100) will never be equal to 1000. Estimating 5 rows, as in the previous case, is ok for me. We see different Output (different SELECT clause) but same information about Index Cond, Filter, and Rows Removed (same WHERE clause). The estimation part looks good.

However, there’s something that I can’t understand. At execution, we know that all rows can be removed before going to the table. We go to the table to get the value from A but all rows were filtered out from the index. At least it was the case with the Index Only Scan, and we know that the filter condition has all values from the index.

However, 147 blocks were read here. We have seen that the index scan reads 5 index pages, and then we can guess that 142 table pages have been read, exactly 10% of the pages from my correlated table. It seems that all rows have been read from the table before being filtered out. The Index Scan being one operation, the filter occurs at the end only. This is only my guess and I hope to get comments about that.

Oracle

With Oracle, the first query, selecting only indexed columns is an INDEX RANGE SCAN similar to the Postgres one.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fj36y2vph9u8f, child number 0
-------------------------------------
select /*+ */ n from demo1 where n<=1000 and mod(n,100)=1000
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 0 |00:00:00.01 | 3 |
|* 1 | INDEX RANGE SCAN| DEMO1_N | 1 | 10 | 4 (0)| 0 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"<=1000)
filter(MOD("N",100)=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Oracle does not know either that the filter predicate mod(n,100)=1000 eliminates all rows and estimates this kind of predicate to 10% of rows (a generic value) after the access predicate returning 10% (this one is calculated from statistics). 3 blocks were read: index branch + leaves.

Reading an additional table from the column does not change this INDEX RANGE SCAN operation but just adds one step to go to the table:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1rpmvq3jj8hgq, child number 0
-------------------------------------
select /*+ */ a from demo1 where n<=1000 and mod(n,100)=1000
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (100)| 0 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO1 | 1 | 10 | 6 (0)| 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | DEMO1_N | 1 | 10 | 4 (0)| 0 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"<=1000)
filter(MOD("N",100)=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22]
2 - "DEMO1".ROWID[ROWID,10]

Having two operations, the filter removes the rows on the output of the index range scan on line 2 and then has to go to the table only for rows that remain. No additional buffer reads on this step 1 when there are no rows. With Oracle, we build indexes to optimize the access predicates and we add columns to optimize the filter predicate. We can go further by adding all projections and avoid completely the access to the table, but that is not always needed. If we can apply all where clause filters on the indexed columns, then the access to the table remains proportional to the result. And the end-user usually accept longer response time for long results. And index access response time is proportional to the result.

The decomposition in two operations is also convenient to see which columns projection is done for the index result or the table result. Here the only output of the index range scan at line 2 is the ROWID and the output from the table access at line 1 is the column we select. So, we have two operations here. We have seen that INDEX RANGE SCAN can run alone. And we will see in the next post that the TABLE ACCESS BY INDEX ROWID can also run alone.

So what?

I hope that Postgres experts will comment about the need to read the table pages even when we can filter all rows from the index scan. We can do something similar by re-writing the query where we can see that the access to the table is never executed:

explain (analyze,verbose,costs,buffers) select a from demo1 where n in (select n from demo1 where n<=1000 and mod(n,100)=1000 ) ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.57..76.35 rows=5 width=4) (actual time=0.285..0.285 rows=0 loops=1)
Output: demo1.a
Buffers: shared hit=5
-> Index Only Scan using demo1_n on public.demo1 demo1_1 (cost=0.29..34.78 rows=5 width=4) (actual time=0.284..0.284 rows=0 loops=1)
Output: demo1_1.n
Index Cond: (demo1_1.n <= 1000)
Filter: (mod(demo1_1.n, 100) = 1000)
Rows Removed by Filter: 1000
Heap Fetches: 0
Buffers: shared hit=5
-> Index Scan using demo1_n on public.demo1 (cost=0.29..8.30 rows=1 width=8) (never executed)
Output: demo1.n, demo1.a, demo1.x
Index Cond: (demo1.n = demo1_1.n)

But this involves a join, and join methods will deserve another series of blog posts. The next one on access paths will show the TABLE ACCESS BY INDEX ROWID equivalent, Tid Scan. Then I’ll have covered all access paths.

 

Cet article Postgres vs. Oracle access paths VIII – Index Scan and Filter est apparu en premier sur Blog dbi services.

Combining Resource Consumer Groups with Application Modules in #Oracle

https://uhesse.files.wordpress.com/2015/10/helps.png?w=600&h=558 600w, https://uhesse.files.wordpress.com/2015/10/helps.png?w=150&h=140 150w" sizes="(max-width: 300px) 100vw, 300px" />

This article contains a complete working example for the Resource Manager on the command line for those of you who can’t use the Enterprise Manager fort it. Believe me, I feel your pain </p />
</p></div>

    	  	<div class=

Choosing a password scheme for the database

In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators.  This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:


SQL> select text
  2   from dba_source
  3  where name in (
  4    'ORA12C_STRONG_VERIFY_FUNCTION'
  5    'ORA12C_VERIFY_FUNCTION',
  6    'ORA_COMPLEXITY_CHECK',
  7    'ORA_STRING_DISTANCE')
  8  order by name, line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
function ora12c_strong_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
return boolean IS
   differ integer;
begin
   if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
                           digit => 2, special => 2) then
      return(false);
   end if;

   -- Check if the password differs from the previous password by at least
   -- 4 characters
   if old_password is not null then
      differ := ora_string_distance(old_password, password);
      if differ < 4 then
         raise_application_error(-20032, 'Password should differ from previous '
                                 || 'password by at least 4 characters');
      end if;
   end if;

   return(true);
end;

FUNCTION ora12c_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   db_name varchar2(40);
   i integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
BEGIN
   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
                               special => 1) THEN
      RETURN(FALSE);
   END IF;

   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;

   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   END LOOP;
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20003, 'Password contains the username ' ||
                                     'reversed');
   END IF;

   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      raise_application_error(-20004, 'Password contains the server name');
   END IF;

   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
        raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password differs from the previous password by at least
   -- 3 characters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 3 THEN
        raise_application_error(-20010, 'Password should differ from the '
                                || 'old password by at least 3 characters');
     END IF;
   END IF ;

   RETURN(TRUE);
END;

function ora_complexity_check
(password varchar2,
 chars integer := null,
 letter integer := null,
 upper integer := null,
 lower integer := null,
 digit integer := null,
 special integer := null)
return boolean is
   digit_array varchar2(10) := '0123456789';
   alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
   cnt_letter integer := 0;
   cnt_upper integer := 0;
   cnt_lower integer := 0;
   cnt_digit integer := 0;
   cnt_special integer := 0;
   delimiter boolean := false;
   len integer := nvl (length(password), 0);
   i integer ;
   ch char(1);
begin
   -- Check that the password length does not exceed 2 * (max DB pwd len)
   -- The maximum length of any DB User password is 128 bytes.
   -- This limit improves the performance of the Edit Distance calculation
   -- between old and new passwords.
   if len > 256 then
      raise_application_error(-20020, 'Password length more than 256 characters');
   end if;

   -- Classify each character in the password.
   for i in 1..len loop
      ch := substr(password, i, 1);
      if ch = '"' then
         delimiter := true;
      elsif instr(digit_array, ch) > 0 then
         cnt_digit := cnt_digit + 1;
      elsif instr(alpha_array, nls_lower(ch)) > 0 then
         cnt_letter := cnt_letter + 1;
         if ch = nls_lower(ch) then
            cnt_lower := cnt_lower + 1;
         else
            cnt_upper := cnt_upper + 1;
         end if;
      else
         cnt_special := cnt_special + 1;
      end if;
   end loop;

   if delimiter = true then
      raise_application_error(-20012, 'password must NOT contain a '
                               || 'double-quotation mark which is '
                               || 'reserved as a password delimiter');
   end if;
   if chars is not null and len < chars then
      raise_application_error(-20001, 'Password length less than ' ||
                              chars);
   end if;

   if letter is not null and cnt_letter < letter then
      raise_application_error(-20022, 'Password must contain at least ' ||
                                      letter || ' letter(s)');
   end if;
   if upper is not null and cnt_upper < upper then
      raise_application_error(-20023, 'Password must contain at least ' ||
                                      upper || ' uppercase character(s)');
   end if;
   if lower is not null and cnt_lower < lower then
      raise_application_error(-20024, 'Password must contain at least ' ||
                                      lower || ' lowercase character(s)');
   end if;
   if digit is not null and cnt_digit < digit then
      raise_application_error(-20025, 'Password must contain at least ' ||
                                      digit || ' digit(s)');
   end if;
   if special is not null and cnt_special < special then
      raise_application_error(-20026, 'Password must contain at least ' ||
                                      special || ' special character(s)');
   end if;

   return(true);
end;

function ora_string_distance
(s varchar2,
 t varchar2)
return integer is
   s_len    integer := nvl (length(s), 0);
   t_len    integer := nvl (length(t), 0);
   type arr_type is table of number index by binary_integer;
   d_col    arr_type ;
   dist     integer := 0;
begin
   if s_len = 0 then
      dist := t_len;
   elsif t_len = 0 then
      dist := s_len;
   -- Bug 18237713 : If source or target length exceeds max DB password length
   -- that is 128 bytes, then raise exception.
   elsif t_len > 128 or s_len > 128 then
     raise_application_error(-20027,'Password length more than 128 bytes');
   elsif s = t then
     return(0);
   else
      for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
          d_col(j) := 0 ;
      end loop;
      for i in 0 .. s_len loop
          d_col(i) := i;
      end loop;
      for j IN 1 .. t_len loop
          d_col(j * (s_len + 1)) := j;
      end loop;

      for i in 1.. s_len loop
        for j IN 1 .. t_len loop
          if substr(s, i, 1) = substr(t, j, 1)
          then
             d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
          else
             d_col(j * (s_len + 1) + i) := LEAST (
                       d_col( j * (s_len+1) + (i-1)) + 1,      -- Deletion
                       d_col((j-1) * (s_len+1) + i) + 1,       -- Insertion
                       d_col((j-1) * (s_len+1) + i-1) + 1 ) ;  -- Substitution
          end if ;
        end loop;
      end loop;
      dist :=  d_col(t_len * (s_len+1) + s_len);
   end if;

   return (dist);
end;

The good thing about this is that:

  • the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and 
  • if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.

Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.

New Video of Oracle Security Vulnerability Scanning

I have just made a new video of a sample session using PFCLScan our vulnerability / security scanner for the Oracle database. In the video I show how easy it is to get started with PFCLScan and scan an Oracle....[Read More]

Posted by Pete On 17/08/17 At 01:50 PM

SQL Server 2012 and Changes to the Backup Operator Permissions

style="display:inline-block;width:320px;height:100px"
data-ad-client="ca-pub-5103295461547706"
data-ad-slot="5182487270">

I’m off to Columbus, Ohio tomorrow for a full day of sessions on Friday for the Ohio Oracle User Group.  The wonderful Mary E. Brown and her group has set up a great venue and a fantastic schedule.  Next week, I’m off to SQL Saturday Vancouver to present on DevOps for the DBA to a lovely group of SQL Server attendees.  It’s my first time to Vancouver, British Columbia and as it’s one of the cities on our list of potential future locations to live, I’m very excited to visit.

Speaking of SQL Server-  Delphix‘s own SQL Server COE, (Center of Excellence) meets twice a month to discuss various topics surrounding our much-loved Microsoft offering.  This week, one of the topics discussed a previous change made to permissions to the Backup Operator role from SQL Server 2008R2 to SQL Server 2012. This feature, referred to as “File Share Scoping” was unique to 2008R2 clusters and no longer exists.

Now many may say, “but this is such an old version.  We’ve got SQL Server 2017, right?”  The challenge is, there are folks out there with 2008 instances and it’s good to know about these little changes that can make big impacts to your dependent products.  This change impacted products with shared backups file systems and as we know, having access to a backup can offload a lot of potential load on a system.

Now, for my product, Delphix, we are dependent on read access to backup files for the initial creation of our “golden copy” that we source everything from.  The change in SQL Server 2012 from the previous File Share Scoping in 2008R2 was only made to Microsoft Failover Clusters, to then offering access to only those with Administrator, where previously, anyone with Backup Operator role could attain access, too.

Our documentation clearly states during configuration of a Delphix engine for the validated sync, (creation of the golden copy) the customer must grant read access for the backup shares to the Delphix OS user and doesn’t state to grant Backup Operator.  As with everything, routine can spell failure, as the Backup Operator role previously offered this access with 2008R2 and it was easy to assume the configuration complete upon database level role grants.

Using Powershell from the command line, note that you can’t view the root of the shared drive with the file server role, Backup Operator in the newer release.

PS C:\Users\user> Get-SmbShareAccess -name "E$" | ft -AutoSize

Name ScopeName AccountName AccessControlType AccessRight
---- --------- ----------- ----------------- -----------
E$ USER1-SHARE BUILTIN\Administrators Allow Full
E$ * BUILTIN\Administrators Allow Full
E$ * BUILTIN\Backup Operators Allow Full
E$ * NT AUTHORITY\INTERACTIVE Allow Full

If you’d like to read more details on backup and recovery changes from SQL Server 2008R2 to 2012, check out the documentation from Microsoft here.

 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [SQL Server 2012 and Changes to the Backup Operator Permissions], All Right Reserved. 2017.

The post SQL Server 2012 and Changes to the Backup Operator Permissions appeared first on DBA Kevlar.

AskTOM–more experts to help you!

I’m thrilled to announce the “formal” addition of globalization and characterset guru Sergiusz Wolicki to the AskTOM team. I say “formal” addition because the team was already getting guidance from Sergiusz whenever we had tough question on charactersets, but just like his enthusiasm to help customers on the forums, Sergiusz was keen to help our AskTOM visitors as well.

Sergiusz is a 20+ year veteran of Oracle Corporation, with over half that time specializing in globalization, internationalization of Oracle products. It only takes a quick glance at the community space for Globalization to gauge the contribution he makes there !

image

 

And it didn’t take long before his knowledge came to good use on AskTOM !

 

image

Welcome Sergiusz !