ASM: Miscellaneous Queries

Preconditions

Connected as oracle user on the DB host, set the environment to the +ASM instance.

For example:

OS> export ORACLE_HOME=/u00/app/grid/11.2.0.4.0.P0_GI

OS> export ORACLE_SID=+ASM

Logon to the +ASM instance as SYSDBA:

OS> sqlplus / as sysdba

SQL>

Queries

Diskgroup Space Usage Overview

Lists for each diskgroup, the total size, free size, usable size and used size in GB.

• The "usable size" is the amount of free space taking in account the space needed for mirroring.
  It is calculated by subtracting required_missor_free_mb (not displayed here) from free_mb.
  For example, if normal redundancy is used, the files are mirrored. So they use twice their size space.
  In this case, if you have 10 GB free space the "usable size" would show around 5 GB.
  Since no redundancy is used in the output example below (we are using Data Guard for high availability),
  free and usable sizes are equal.

SELECT name, total_mb/1024 AS "Total GB", free_mb/1024 AS "Free GB", usable_file_mb/1024 AS "Usable GB", (total_mb-usable_file_mb)/1024 AS "Used GB"

  FROM v$asm_diskgroup;

Output example:

NAME                             Total GB    Free GB  Usable GB    Used GB

------------------------------ ---------- ---------- ---------- ----------

U01                                  1600 713.609375 713.609375 886.390625

U02                                   800 757.382813 757.382813 42.6171875

Files Overview

Lists files with their type, size, creation date and file number:

COLUMN gname   FORMAT A9           HEADING "Diskgroup|Name"

COLUMN fname   FORMAT A35          HEADING "File|Name"

COLUMN type    FORMAT A20          HEADING "File|Type"

COLUMN sizemb  FORMAT 9,999,999.99 HEADING "File|Size MB"

COLUMN credate FORMAT A16          HEADING "File|Creation Date"

SELECT g.name gname, a.name fname, f.type, f.bytes/(1024*1024) sizemb, TO_CHAR(f.creation_date,'YYYY.MM.DD HH24:MI') credate, f.file_number

  FROM v$asm_file f, v$asm_alias a, v$asm_diskgroup g

 WHERE g.group_number = a.group_number

   AND f.group_number = a.group_number

   AND f.file_number = a.file_number

   AND f.type NOT IN ('FLASHBACK','ARCHIVELOG')

 ORDER BY 2, 1;

Output example:

Diskgroup File                                File                          File File

Name      Name                                Type                       Size MB Creation Date    FILE_NUMBER

--------- ----------------------------------- -------------------- ------------- ---------------- -----------

U01       Current.256.904578613               CONTROLFILE                  32.36 2016.02.23 15:50         256

U02       Current.256.904578613               CONTROLFILE                  32.36 2016.02.23 15:50         256

U01       SYSAUX.261.904578637                DATAFILE                  4,096.01 2016.02.23 15:50         261

U01       SYSTEM.260.904578633                DATAFILE                  1,024.01 2016.02.23 15:50         260

U01       TEMP.263.904578645                  TEMPFILE                  6,144.01 2016.02.23 15:50         263

U01       TOOLS.273.904745439                 DATAFILE                     10.01 2016.02.25 14:10         273

U01       TS_APP01_DATA.306.907928707         DATAFILE                  2,048.01 2016.03.31 10:25         306

U01       TS_APP02_DATA.269.904748123         DATAFILE                    300.01 2016.02.25 14:55         270

U01       TS_APP02_IDX.274.904748203          DATAFILE                    120.01 2016.02.25 14:56         274

U01       TS_APP03_ARCH.275.904745853         DATAFILE                 32,520.01 2016.02.25 14:17         275

U01       TS_APP03_DATA.268.904748393         DATAFILE                 21,504.01 2016.02.25 14:59         268

U01       TS_APP03_IDX.268.904748394          DATAFILE                 21,504.01 2016.02.25 15:01         269

. . .

I/O and Error Statistics per DB-Instance, Disk-Group and Disk

COLUMN instname     FORMAT A10         HEADING "Instance"

COLUMN name         FORMAT A9          HEADING "Diskgroup|Name"

COLUMN group_number FORMAT 999         HEADING "Diskgroup|Number"

COLUMN disk_number  FORMAT 999         HEADING "Disk|Number"

COLUMN reads        FORMAT 999,999,999 HEADING "Total Read|Requests"

COLUMN writes       FORMAT 999,999,999 HEADING "Total Write|Requests"

COLUMN read_time    FORMAT 999,999     HEADING "Total Read|Time [1/100s]"

COLUMN write_time   FORMAT 999,999     HEADING "Total Write|Time [1/100s]"

COLUMN read         FORMAT 999,999,999 HEADING "Total Read|[MB]"

COLUMN written      FORMAT 999,999,999 HEADING "Total Write|[MB]"

SELECT d.instname, g.name, d.group_number, d.disk_number,

       d.reads, d.writes, d.read_errs, d.write_errs,

       d.read_time, d.write_time, d.bytes_read/(1024*1024) read, d.bytes_written/(1024*1024) written

  FROM v$asm_disk_iostat d, v$asm_diskgroup g

 WHERE g.group_number = d.group_number

 ORDER BY 1, 2, 3;

Output example:

           Diskgroup Diskgroup   Disk   Total Read  Total Write Total Read Total Write    Total Read   Total Write   Total Read  Total Write

Instance   Name         Number Number     Requests     Requests     Errors      Errors Time [1/100s] Time [1/100s]         [MB]         [MB]

---------- --------- --------- ------ ------------ ------------ ---------- ----------- ------------- ------------- ------------ ------------

TEST_DB    U01               1      1   16,174,066    8,985,315          0           0       546,514       464,285    3,175,899      837,536

TEST_DB    U01               1      0   16,860,897    9,075,988          0           0       597,154       462,099    3,183,002      836,577

TEST_DB    U02               2      0    1,716,125    3,428,283          0           0        78,300       119,095      721,684    1,708,365

Status and Error Statistics per Disk-Group and Disk

COLUMN name          FORMAT A9          HEADING "Diskgroup|Name"

COLUMN mount_status  FORMAT A12

COLUMN header_status FORMAT A13

COLUMN mode_status   FORMAT A11

SELECT g.name, d.group_number, d.disk_number, d.mount_status, d.header_status, d.mode_status, d.state, d.read_errs, d.write_errs

  FROM v$asm_disk d, v$asm_diskgroup g

 WHERE g.group_number = d.group_number

 ORDER BY 1, 2;

Output example:

Diskgroup Diskgroup   Disk                                                 Total Read Total Write

Name         Number Number MOUNT_STATUS HEADER_STATUS MODE_STATUS STATE        Errors      Errors

--------- --------- ------ ------------ ------------- ----------- -------- ---------- -----------

U01               1      1 CACHED       MEMBER        ONLINE      NORMAL            0           0

U01               1      0 CACHED       MEMBER        ONLINE      NORMAL            0           0

U02               2      0 CACHED       MEMBER        ONLINE      NORMAL            0           0

Tags: Oracle, ASM, Usage, Disk, Error, Space, Publish
August 19, 2011 at 01:37PM

Automatically Enabling Oracle Advanced Queues

After the migration of several schemata with a lot of Oracle DB-Queues to another database, all the queues where disabled.

In order to re-enable these queues, I wrote the following script:

PROMPT ******************************************************

PROMPT ****               Enable Queues                  ****

PROMPT ******************************************************

SET FEEDBACK OFF

SET VERIFY OFF

SET SERVEROUTPUT ON

SET HEAD OFF

SET TIMING OFF

SET TRIMSPOOL ON

SET LINESIZE 200

SET PAGESIZE 999

COLUMN txt FORMAT A100;

SPOOL tmp_EnableQueues.sql

SELECT --owner, name, queue_table, queue_type, enqueue_enabled, dequeue_enabled,

       DECODE(TRIM(enqueue_enabled),'NO','EXEC SYS.DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'''||owner||'.'||name||''',ENQUEUE=>TRUE);', '')||chr(10)||

       DECODE(TRIM(dequeue_enabled),'NO','EXEC SYS.DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'''||owner||'.'||name||''',DEQUEUE=>TRUE);', '')

  FROM dba_queues

 WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN')

   AND queue_type <> 'EXCEPTION_QUEUE'

   AND (TRIM(enqueue_enabled) = 'NO' OR TRIM(dequeue_enabled) = 'NO');

SPOOL OFF

@tmp_EnableQueues.sql

PROMPT ... done

SET HEAD ON

SET FEEDBACK ON

EXIT;

For every queue one statement for each "direction" (enqueue / dequeue) is generated.

The generated statements are written (Spooled) into the file tmp_EnableQueues.sql

Then the file tmp_EnableQueues.sql is executed, enabling each queue.

I hope it helps.

Tags: Oracle, SQL*Plus, SQL, Publish, DB-Queue, Oracle Streams, Advanced Queues
July 27, 2016 at 02:22PM

Redirecting Gargoyles SysLogs to the Synology NAS

Redirecting Gargoyles SysLogs to the Synology NAS

Other Information 

• http://ift.tt/2amTYbL
• Displaying Logs (when logged on in the router: logread -f)

Directing the SysLogs. In this example, the NAS has the IP address 192.168.1.187

uci set system.@system[0].log_ip=192.168.1.187

uci set system.@system[0].conloglevel=7

uci commit

reboot

Opening a firewall port - as described in some posts - was not necessary 

Removing the redirection

uci del system.@system[0].log_ip

uci commit

reboot

or

open /etc/config/system file and just remove the lines from it

root@Gargoyle:~# cat /etc/config/system

config timeserver 'ntp'

        list server '0.pool.ntp.org'

        list server '1.pool.ntp.org'

        list server '2.pool.ntp.org'

        option enabled '1'

        option enable_server '1'

config led 'led_wan'

        option name 'WAN LED (green)'

        option sysfs 'netgear:green:wan'

        option default '0'

config led 'led_usb'

        option name 'USB'

        option sysfs 'netgear:green:usb'

        option trigger 'usbdev'

        option dev '1-1'

        option interval '50'

config button 'reboot_button'

        option button 'wps'

        option action 'released'

        option handler 'logger reboot ; /usr/lib/gargoyle/reboot.sh ;'

        option min '3'

        option max '90'

config button 'reset_button'

        option button 'reset'

        option action 'released'

        option handler 'logger restore defaults ; /sbin/firstboot ; reboot ;'

        option min '3'

        option max '90'

config system 'system'

        option hostname 'Gargoyle'

        option cronloglevel '9'

        option timezone 'CET-1CEST,M3.5.0/2,M10.5.0/3'

        option log_ip '192.168.1.187'

Tags: Netgear, Router, Synology, NAS, Publish, Gargoyle, SysLog
July 21, 2016 at 10:18PM

Sending E-Mails from an Oracle DB without XML-DB

Goal / Problem Description

In order to send E-Mails from an Oracle DB using the Oracle PL/SQL package utl_smtp Access Control Lists (ACLs) must be configured.

This, not only for this package but also for other packages like: utm_mail, utl_http, utl_inaddr and utl_tcp

ACLs needs the XML-DB option. XML-DB is known to raise security issues, so it is often avoided.

It also would be an "overkill" to install XML-DB, only for sending mails.

A simple alternative is:

• create your own PL/SQL Package, Procedure or Funktion in the SYS Schema
• make a public alias
• grant the execution right to the DB-Users needing the functionality

Et voilà !

Implementation Suggestion

Implementation suggestion of a package with a function and a procedure to send e-mails.

I suggest prefixing it with a name that helps recognize, that the it does not belongs to the default SYS Schema (here my_ ).

Also document this in order to avoid forgetting it in case of migration.

CREATE OR REPLACE PACKAGE SYS.my_mail_utl AUTHID DEFINER IS

-- Used by: send_mail() and send_mail_fct()

c_res_success        CONSTANT PLS_INTEGER:= 0;

c_res_inv_operation  CONSTANT PLS_INTEGER:= 1;

c_res_tmp_problem    CONSTANT PLS_INTEGER:= 2;

c_res_perm_error     CONSTANT PLS_INTEGER:= 3;

/*-------------------------------------------------------

USAGE EXAMPLES

-- Test: Procedure

BEGIN

  my_mail_utl.send_mail(

    'my.name@my_domain.ch',  --Sender

    'my.name@my_domain.ch',  --Recipient

    'Test Subject 1',        --Subject

    'Test message text 1'    --Message

  );

END;

-- Test: Function

BEGIN

  DBMS_OUTPUT.PUT_LINE ('ResVal: '||

    my_mail_utl.send_mail_fct(

     'my.name@my_domain.ch', --Sender

     'my.name@my_domain.ch', --Recipient

     'Test Subject 2',       --Subject

     'Test message text 2'   --Message

    ));

END;

-------------------------------------------------------*/

/*-------------------------------------------------------

  RETURN VALUES

    c_res_success       Success

    c_res_inv_operation Invalid Operation in Mail attempt using UTL_SMTP.

    c_res_tmp_problem   Temporary e-mail issue - try again

    c_res_perm_error    Permanent Error Encountered

-------------------------------------------------------*/

FUNCTION send_mail_fct

(

 i_sender     IN VARCHAR2,

 i_recipient  IN VARCHAR2,

 i_subject    IN VARCHAR2,

 i_message    IN LONG

) RETURN PLS_INTEGER;

/*-------------------------------------------------------

See send_mail_fct()

-------------------------------------------------------*/

PROCEDURE send_mail

(

 i_sender     IN VARCHAR2,

 i_recipient  IN VARCHAR2,

 i_subject    IN VARCHAR2,

 i_message    IN LONG

);

END my_mail_utl;

/

--=============================================================================

--  PACKAGE BODY

--=============================================================================

CREATE OR REPLACE PACKAGE BODY SYS.my_mail_utl IS

/*-------------------------------------------------------

See package header

-------------------------------------------------------*/

FUNCTION send_mail_fct

(

 i_sender     IN VARCHAR2,

 i_recipient  IN VARCHAR2,

 i_subject    IN VARCHAR2,

 i_message    IN LONG

) RETURN PLS_INTEGER IS

   c_crlf         CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);

   c_mailhost     CONSTANT VARCHAR2(30):= 'my_mailhost.my_domain.ch'; -- host mail address

   v_mail_conn    utl_smtp.connection;

   v_mesg         long;

BEGIN

   v_mail_conn := utl_smtp.open_connection(c_mailhost, 25);

   v_mesg:= 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss' ) || c_crlf ||

            'From:  <' || i_sender ||'>' || c_crlf ||

            'Subject: ' || i_subject || c_crlf ||

            'To: ' || i_recipient || c_crlf ||

            c_crlf || i_message;

   utl_smtp.helo(v_mail_conn, c_mailhost);

   utl_smtp.mail(v_mail_conn, i_sende);

   utl_smtp.rcpt(v_mail_conn, i_recipient);

   utl_smtp.data(v_mail_conn, v_mesg);

   utl_smtp.quit(v_mail_conn);

   RETURN c_res_success;

EXCEPTION

  WHEN UTL_SMTP.INVALID_OPERATION THEN

    dbms_output.put_line('Invalid Operation in Mail attempt using UTL_SMTP.');

    RETURN c_res_inv_operation;

  WHEN UTL_SMTP.TRANSIENT_ERROR THEN

    dbms_output.put_line( 'Temporary e-mail issue - try again');

    RETURN c_res_tmp_problem;

  WHEN UTL_SMTP.PERMANENT_ERROR THEN

    dbms_output.put_line( 'Permanent Error Encountered.');

    RETURN c_res_perm_error;

END send_mail_fct;

/*-------------------------------------------------------

See package header

-------------------------------------------------------*/

PROCEDURE send_mail_smtp

(

 i_sender     IN VARCHAR2,

 i_recipient  IN VARCHAR2,

 i_subject    IN VARCHAR2,

 i_message    IN LONG

) IS

  v_dummy PLS_INTEGER;

BEGIN

  v_dummy:= send_mail_fct(i_sender, i_recipient, i_subject, i_message);

END send_mail;

END my_mail_utl;

/

Create a public synonym in order to make the location of the package "my_mail_utl" transparent to the "users".

CREATE OR REPLACE PUBLIC SYNONYM my_mail_utl FOR sys.my_mail_utl;

Give the execution right on the package, function or procedure to the DB-users needing to send emails.

GRANT EXECUTE ON my_mail_utl TO <DB-User>;

For testing as SYS user you can change the session context in SQL*Plus without needing to logon as <DB-User> (what is often not possible if you don't know its passord.)

ALTER SESSION SET CURRENT_SCHEMA=<DB-User>;

Testing the procedure (see package header):

-- Test: Procedure

BEGIN

  my_mail_utl.send_mail(

  'my.name@my_domain.ch', -- Sender

  'my.name@my_domain.ch', -- Recipient

  'Test Subject 1',       -- Subject

  'Test message text 1'   -- Message

  );

END;

/

Tags: Oracle, PL/SQL, Mail, Publish
January 10, 2013 at 03:15PM

Posting Blog Entries from Evernote with ifttt

Today I discovered that the automation platform ifttt (If This Than That) enables the automatic publishing of notes from Evernote to Blog platforms like Blogger or WordPress.

So, this is my first blog entry automatically published from Evernote by using the recipe:

Publish a Blogger Post When You Add a Tag to a Note

There are other recipies using Evernote and Blogger:

http://ift.tt/107OrVe

See if it works.

Below some examples of different content types.

Example of a picture:

Example of  SQL code automatically posted:

COL owner       HEADING 'Owner' FORMAT A15

COL object_type HEADING 'Name'  FORMAT A40

COL object_name HEADING 'Type'  FORMAT A10

SELECT owner "Owner", object_type "Name", object_name "Type"

  FROM dba_objects

 WHERE status != 'VALID'

   AND owner not in ('SYS','SYSTEM','DBSNMP','PUBLIC')

 ORDER BY owner, object_type;

On some tests, the automatically posted code did not looked like the code from above.
Sometimes the used font had no fixed width.

In order to get such fixed width font below, I had to edit the generated HTML, surrounding my SQL-code block by <pre> ... </pre>:

COL owner       HEADING 'Owner' FORMAT A15
COL object_type HEADING 'Name'  FORMAT A40
COL object_name HEADING 'Type'  FORMAT A10

SELECT owner "Owner", object_type "Name", object_name "Type"
  FROM dba_objects
 WHERE status != 'VALID'
   AND owner not in ('SYS','SYSTEM','DBSNMP','PUBLIC')
 ORDER BY owner, object_type;

The color (and other properties) can also be changed by adding a CSS entry to your layout template.
For example:

And using it in the post.
That means, in the example above, by surrounding the SQL-code with:
<pre class="code"> . . . </pre>:

Alternatives for automatic Posting

There are also other automation platforms providing such a service:

• zapier
• Postach.io

Tags: ifttt, Blogger, Publish
July 08, 2016 at 01:36PM

Registering an Oracle DB into the RMAN Catalog

PROBLEM

If the Database ID changed (I described this in a previous post) and the RMAN catalog is used, RMAN will output the following error stack:

RMAN-03002: failure of backup command at 07/05/2016 03:56:13
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20001: target database not found in recovery catalog



As the last error line says, the database can not be found in the RMAN catalog, because the DB ID is used in order to identify the catalog records belonging your DB.

SOLUTION

Logon as oracle on your DB host.
Logon with RMAN to your DB and your RMAN catalog (here TNS-Alias RMANCAT):

OS> rman target / catalog /@RMANCAT


Then register your DB into the RMAN catalog:

RMAN> REGISTER DATABASE;

PROBLEM 

If you are trying to register a standby DB and get this error:

RMAN> REGISTER DATABASE;
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog


SOLUTION

First: make sure, that the primary DB is already registered in the RMAN catalog.

Then logon with the standby DB on the RMAN catalog and execute the following statement:

RUN {
  ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
  CROSSCHECK BACKUP;
}


Remove DB from RMAN Catalog

If using the Oracle password wallet, it is also possible to logon to the DB with rman without password.

(For this, before logon with rman, make sure that the environment variable TNS_ADMIN "points" to the Password wallet directory.)

In this example, the TNS-Alias of the RMAN-Catalog DB is "RMANCAT"

OS> rman catalog /@RMANCAT

Get the ID of your DB:

RMAN> list db_unique_name of database 'DB1_SID'; List of Databases DB Key  DB Name  DB ID            Database Role    Db_unique_name ------- ------- ----------------- ---------------  ------------------ 281963  DB1_SID 1738134485        PRIMARY          DB1_SITE1   281963  DB1_SID 1738134485        STANDBY          DB1_SITE2   394582  DB1_SID 5396830648        PRIMARY          DB5_SITE1

Set the DBID of the DB to be removed in the RMAN environment:

RMAN> set dbid 1738134485 executing command: SET DBID database name is "DB1_SID" and DBID is 1738134485

Execute the unregister statement using the SID of your DB:

RMAN> unregister database DB1_SID; database name is "DB1_SID" and DBID is 1738134485 Do you really want to unregister the database (enter YES or NO)? YES database unregistered from the recovery catalog

The unregistered DBs with ID 1738134485 are now gone:

RMAN> list db_unique_name of database 'DB1_SID'; List of Databases DB Key  DB Name  DB ID        Database Role    Db_unique_name ------- ------- ------------- ---------------  ------------------ 394582  DB1_SID 5396830648    PRIMARY          DB5_SITE1

.

.