2016-11-23

Changing the SID of an Oracle Database keeping the DBID unchanged

When I once needed to change a DB SID, I didn't notice that it is possible to perform this, without changing the DBID. As consequence of my mishap, I had to perform an "DATABASE OPEN RESETLOGS" with all its consequences.

So, If you are only changing the DB SID  (leaving the DBID unchanged)  you should use the parameter SETNAME=YES.
For example:
OS> nid TARGET=SYS/<pwd>@<DB_Alias> DBNAME=NewDbSID SETNAME=YES

If you forget to use it, like me in the following example, the DBID will also be changed:
OS> nid TARGET=SYS/<pwd>@<DB_Alias> DBNAME=NewDbSID
. . .
Connected to database OldDbSID (DBID=1075560178)
. . .
Change database ID and database name OldBbSID to NewDbSID? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1075560178 to 1518037673
Changing database name from OldBbSID to NewDbSID
    Control File ... - modified
    Control File ... - modified
...
    Datafile ... - dbid changed, wrote new name
    Datafile ... - dbid changed, wrote new name
...
    Control File ... - dbid changed, wrote new name
    Control File ... - dbid changed, wrote new name
    Instance shut down

Database name changed to NewDbSID.
Modify parameter file and generate a new password file before restarting.
Database ID for database NewDbSID changed to 1518037673.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

... you will need to perform an "OPEN RESETLOGS".
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

If you are using the RMAN catalog, you will then need to register this DB in the RMAN catalog again. In short:
OS> rman target / catalog /@<RmanCatalogAlias>

RMAN> register database;

Perhaps you also want to remove the DB entry with the old DBID from the catalog.

The previous backups and archived logs of the database become unusable.
You should also perform a full backup of the DB as soon as possible.

Tags: SID, DBID, nid
June 27, 2016 at 05:13PM

2016-09-28

ASM: Queries Hanging (v$asm_disk)

Solved Problem When ASM Queries on v$asm_disk Hang / Block 

Wenn trying to display ASM information, Oracle Cloud Control (Enterprise Manager) displays nothing.
Also when trying to perform some queries on ASM, they "hang" and no answer is returned.
(For example: SELECT * FROM v$asm_disk; hangs)

Commands with the ASM tool asmcmdh still work:

oracle@<Host>:~/ [+ASM] asmcmdh
ASMCMD> lsdsk -p
Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path
        1         0  2526484498  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/grid
        2         6  2526484516  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_07
        2         7  2526484517  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_08
        2         8  2526484518  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_09
        2         9  2526484519  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u01_10
        3         0  2526484503  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_01
        3         1  2526484505  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_02
        3         2  2526484504  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_03
        3         3  2526484507  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_04
        3         4  2526484506  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/u02_05


The Metalink Doc ID 8786114.8 suggests to verify / correct the ASM discovery string in order to exclude "invalid devices"
like /dev/kmem or other devices that should not be scanned by the ASM disk discovery.
For example it suggests not to use /dev/* as an ASM disk discovery string.

Querying the DB for the ASM disk discovery string, returns:

SQL> show parameter asm_diskstring
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /dev/ASM

Listing the mount points using the result from above, shows that still some mount points exits (highlighted)
that are not valid any more. (i.e. no storage is mounted on these mount points any more)
(These are not listed be the asmcmdh command from above.)
(This is not directly apparent from the result below. But I knew, that nothing was mounted at these mount points any more.)
oracle@<Host>:~/ [+ASM] ll /dev/ASM*
total 0
drwxr-xr-x  2 root root  360 Jan 24 10:29 .
drwxr-xr-x 12 root root 3660 Mar 26 15:47 ..
lrwxrwxrwx  1 root root    7 Apr 18  2013 grid -> ../xvdf
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_01 -> ../xvdb
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_02 -> ../xvdc
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_03 -> ../xvdd
lrwxrwxrwx  1 root root    7 Apr 18  2013 u01_04 -> ../xvde
lrwxrwxrwx  1 root root    7 Oct  4  2013 u01_05 -> ../xvdl
lrwxrwxrwx  1 root root    7 Oct  4  2013 u01_06 -> ../xvdm
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_07 -> ../xvdn
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_08 -> ../xvdo
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_09 -> ../xvdp
lrwxrwxrwx  1 root root    7 Jan 24 10:29 u01_10 -> ../xvdq
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_01 -> ../xvdg
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_02 -> ../xvdh
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_03 -> ../xvdi
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_04 -> ../xvdj
lrwxrwxrwx  1 root root    7 Apr 18  2013 u02_05 -> ../xvdk

Problem Cause
As described in the Metalink-Article from above, the SQL statement and Cloud Control hang,
because the statement tried to query inexistent LUNs with a very long timeout.

Solution
In my case, the solution was to remove the old (invalid) mount points.
I did not need to change the ASM disk discovery string.


Tags: Oracle, ASM, Problems&Solutions, LUN, Hang, blockiert, Publish
May 06, 2014 at 09:21AM

2016-09-14

ORA: How To Increase the Redolog Size Under ASM

Only for information (is not ready neeeded here) - how to list the redo log members:
SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;

    GROUP# STATUS  TYPE    member
---------- ------- ------- --------------------------------------------------
         1         ONLINE  +U01/a1028t_site1/onlinelog/group_1.257.771684831
         1         ONLINE  +U02/a1028t_site1/onlinelog/group_1.257.771684833
         2         ONLINE  +U01/a1028t_site1/onlinelog/group_2.258.771684833
         2         ONLINE  +U02/a1028t_site1/onlinelog/group_2.258.771684833
         3         ONLINE  +U01/a1028t_site1/onlinelog/group_3.259.771684835
         3         ONLINE  +U02/a1028t_site1/onlinelog/group_3.259.771684835

List the redo log groups and their status:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        250 CURRENT
         2          1        250 ACTIVE
         3          1        250 ACTIVE

Create a new temporary group (here with number 10).
(I think, that this is not realy needed. I think that we only need to perform several "alter system switch logfile;" until the groups that are now ACTIVE become INACTIVE.)
SQL> ALTER DATABASE ADD LOGFILE GROUP 10 SIZE 250m;

Database altered.

Here the new temporary group "10":
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        250 ACTIVE
         2          1        250 CURRENT
         3          1        250 ACTIVE
        10          1        250 UNUSED

Now, switch the logfiles until the now "ACTIVE" groups get the status "INACTIVE". . .
SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        250 ACTIVE
         2          1        250 ACTIVE
         3          1        250 ACTIVE
        10          1        250 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
. . .

Here we see, that our temporary group "10" is the only active one:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        250 INACTIVE
         2          1        250 INACTIVE
         3          1        250 INACTIVE
        10          1        250 CURRENT

Once we have "INACTIVE" groups, we can delete each of these "INACTIVE" groups and recreate them with the new size.

Deleting "INACTIVE" group "1":
SQL> ALTER DATABASE drop LOGFILE GROUP 1;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         2          1        250 INACTIVE
         3          1        250 INACTIVE
        10          1        250 CURRENT

And recreating it with the new size:
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 512m;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        512 UNUSED
         2          1        250 INACTIVE
         3          1        250 INACTIVE
        10          1        250 CURRENT

Also deleting "INACTIVE" group "2" and recreating it with the new site of 512 MB:
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 SIZE 512m;

Database altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        512 UNUSED
         2          1        512 UNUSED
         3          1        250 INACTIVE
        10          1        250 CURRENT

I switched the logfile in order to prove that my new group "1" is OK:
SQL> alter system switch logfile;

System altered.

SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        512 CURRENT
         2          1        512 UNUSED
         3          1        250 INACTIVE
        10          1        250 ACTIVE

And so on for all groups.

Switch the logfile, until the temporary group "10" becomes "INACTIVE":
. . .
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
. . .

Now that the temporary group "10" is "INACTIVE", it can be deleted:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        512 CURRENT
         2          1        512 INACTIVE
         3          1        512 INACTIVE
        10          1        250 INACTIVE

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;

Database altered.

Here is the final result:
SQL> SELECT group#, thread#, bytes/(1024*1024) "MB", status FROM v$log;

    GROUP#    THREAD#         MB STATUS
---------- ---------- ---------- ----------------
         1          1        512 CURRENT
         2          1        512 INACTIVE
         3          1        512 INACTIVE


REMARK: As you can see below, two files have automatically been created for each group:
SQL> select group#, status, type, substr(member,1,50) "member" from v$logfile;

    GROUP# STATUS  TYPE    member
---------- ------- ------- --------------------------------------------------
         1         ONLINE  +U01/a1028t_site1/onlinelog/group_1.257.771762599
         1         ONLINE  +U02/a1028t_site1/onlinelog/group_1.257.771762601
         2         ONLINE  +U01/a1028t_site1/onlinelog/group_2.258.771762649
         2         ONLINE  +U02/a1028t_site1/onlinelog/group_2.258.771762649
         3         ONLINE  +U01/a1028t_site1/onlinelog/group_3.259.771762723
         3         ONLINE  +U02/a1028t_site1/onlinelog/group_3.259.771762725

6 rows selected.

I think that the files were automatically created, because two log destinations have been configured. (I need to verify this):
SQL> show parameter db_create_online_log_dest_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1          string      +U01
db_create_online_log_dest_2          string      +U02
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string




Tags: Oracle, ASM, Redolog, Publish
January 05, 2012 at 10:09AM

2016-08-28

Kubuntu 14.04 black screen after monitor off/on


Whenever I turned my display off and on again after having logged on,  the screen stayed black. After rebooting, the logon screen appeared, but after logon with my user account, the screen got black. Logging with other user accounts had no problem.

This problem occurred using Kubuntu 14.04.  and a Nvidia graphics card connected by a display port cable.

Here is the description how I corrected it.


In short



Change the first false value of your screen definition file 
.kde/share/apps/kscreen/xxxxxxxxxxxxxx
from false to true.
Than make this file read only.



In Detail


Start one of the consoles: Ctrl-Alt-F1
Then enter your OS-User name and password.

Go to the directory .kde/share/apps/kscreen located in your home directory:
cd ~/.kde/share/apps/kscreen

Listing the files in this directory (ls -1), should display at least one file, that looks like the one bellow.
The files name is a mix of number an letters (seems a long hexadecimal number):
ls -1

7134be4579e47853d9bc866485e252fb

If you have/had more than one monitors, several files will be listed.

This is a text file. Its contents can be displayed with the command cat :
cat 7134be4579e47853d9bc866485e252fb

[ { "enabled" : false, "id" : "9a9b7a71150d853203babd861f68d8c5", "metadata" : { "fullname" : "xrandr-Dell Inc.-DELL U2515H-9X2VY61E10XL", "name" : "DP-1" }, "mode" : { "refresh" : 59.9506, "size" : { "height" : 1440, "width" : 2560 } }, "pos" : { "x" : 0, "y" : 0 }, "primary" : true, "rotation" : 1 } ]

Change the false value after "enabled" (from above) to true.

Here I show how to do it using the vi editor, that is available on every Linux system.
vi 7134be4579e47853d9bc866485e252fb

  • Go to the beginning of false by using the arrow keys
  • press the key [DEL] until false is completely deleted
  • press the key [i] in order to enter the insert mode
    (-- INSERT -- will then by displayed at the bottom line)
  • type true
  • press the key [ESC] in order to leave the insert mode
    (-- INSERT -- disappear from the bottom line)
  • press the keys [:] and [x]
    (":" starts the command mode and "x" is the command "exit with saving")

If you display the contents of your file again, the false should now have been replaced by true

Now the Graphical environment can be restarted by entering the command startx.
startx

I could avoid that this problem occurs again I made this file read only


chmod ugo-w 7134be4579e47853d9bc866485e252fb


I hope this helps.



Tags: Problems&Solutions, Kubuntu, Publish, Monitor, Black Screen
August 28, 2016 at 08:52AM

2016-08-06

Sending WakeOnLan (WOL) from Linux (KUbuntu)


In order to have the Synology NAS already been running when I boot up my Ubuntu (KUbuntu) computer, I make my Ubuntu send a WOL packet to the NAS when booting with etherwake.

Here is the command used in the script below (Replace the exemplary MAC address 00:11:22:33:44:55 below by the MAC address of the device you want to be woken up.)
root@Ubuntu:~# etherwake -i eth1 00:11:22:33:44:55


root@Ubuntu:/etc/init.d# cat sendWOLtoNAS
#! /bin/sh
### BEGIN INIT INFO
# Provides: sendWOLtoNAS
# Required-Start:    
# Required-Stop:     
# Default-Start: 2
# Default-Stop:      
# Short-Description: Sends Wake On Lan (WOL) packet to the Synology NAS
# Description: Is used in order to make sure that the NAS will also be started when this computer is starting
### END INIT INFO

MYSELF="/etc/init.d/sendWOLtoNAS"

logger "${MYSELF} Start"
etherwake -i eth1 00:11:22:33:44:55
logger "${MYSELF} WOL sent, sleeping 10s"
sleep 10
logger "${MYSELF} Done"

Making the script be run on startup of Ubuntu
root@Ubuntu:/etc/init.d# update-rc.d sendWOLtoNAS start 20 2 .
 Adding system startup for /etc/init.d/sendWOLtoNAS ...
  /etc/rc2.d/S20sendWOLtoNAS -> ../init.d/sendWOLtoNAS

If you want to undo it:
root@Ubuntu:/etc/init.d# update-rc.d -f sendWOLtoNAS remove 20 2 .
 Removing any system startup links for /etc/init.d/sendWOLtoNAS ...
  /etc/rc2.d/S20sendWOLtoNAS


Tags: ubuntu, Linux, Boot, WOL, Startup, Publish
May 30, 2016 at 09:22PM

2016-08-02

Configure WOL from Internet (WAN) / ASUS Router RT-AC88U

In order to be able to Wake On LAN (WOL) a computer, a NAS (Network Attached Storage) etc. from the Internet (WAN), perform the following configuration steps.

Altough the examples in this article use the ASUS Router RT-AC88U, the configuration in general is not specific to this router. The only specific part is how to make the static ARP rule "survive" a router reboot.

DDNS

Configure a (DDNS) Dynamic Domain Name System in order to address your home network, even with changing ip addresses.
This is not described here. There are plenty of descriptions around. (The ASUS router can configure one for you automatically):
Our exemplary DDNS here is: myDDNS.asuscomm.com

In order to improve security a little (really only "a little"), do not use standard ports for WOL.
For example, we will use the port: 4231

NAS

For example in the NAS configure the Port: 22 for WOL.

ASUS Router

Port Forwarding
On the router configure the mapping of the incoming Port 4321 to the NAS Port 22 and a broadcast LAN ip address 192.168.1.254.

ARP

Setup a ARP (Address Resolution Protocol) entry / rule, that redirects the WOL packets send to destination 192.168.1.254 to be broadcast to all MAC addresses (i.e. to ff:ff:ff:ff:ff:ff)

Login to the router via telnet or ssh as admin (root) user.

Once logged in, use this command in order to add the needed ARP entry:
arp -s 192.168.1.254 ff:ff:ff:ff:ff:ff

On another router (a Netgear router with Gargoyle, a Firmware based on OpenWrt), I used the following statement in order to add this entry:
ip neigh add 192.168.1.254 lladdr ff:ff:ff:ff:ff:ff nud permanent dev br-lan

List the resulting ARP table containing the new highlighted entry:
admin@(none):/jffs/scripts# arp
? (192.168.1.155) at <incomplete>  on br0
? (XXX.XXX.178.1) at XX:XX:XX:XX:fc:81 [ether]  on eth0
? (192.168.1.254) at ff:ff:ff:ff:ff:ff [ether] PERM on br0
? (192.168.1.58) at XX:XX:XX:XX:4c:c4 [ether]  on br0
? (192.168.1.251) at XX:XX:XX:XX:5d:0a [ether]  on br0
? (192.168.1.170) at XX:XX:XX:XX:0e:f3 [ether]  on br0
? (192.168.1.40) at XX:XX:XX:XX:8a:58 [ether]  on br0

Now you can check, if this configuration works with Programs / Apps that can send WOL Packets.
As target you will need to set your chosen DDNS and port. (In our example it would be myDDNS.asuscomm.com and port 4231)
On the iPhone I use the "iNet WOL" app.

Although this ARP entry is marked as permanent (PERM), the configuration will disappear once the router is rebooted.

In order to avoid this, set up a script that executes the ARP statement from above when the router reboots.
This can be achieved by using the ASUSWRT feature that executes a user defined script, every time a USB drive is mounted.

The ASUS router offers a small non volatile "disk storage" under /jffs
So, create a folder in this directory. In this example, the folder "scripts".

This generates the script wan-wol.sh in the current folder:
cat <<EOF > wan-wol.sh
#!/bin/sh

scriptName="/jffs/scripts/\$(basename $0)"
myIp="192.168.1.254"

/usr/bin/logger -t "\${scriptName}" \$$ "arp -s \${myIp} ff:ff:ff:ff:ff:ff"
arp -s \$myIp ff:ff:ff:ff:ff:ff
/usr/bin/logger -t "\${scriptName}" \$$ "Check: \$(arp | grep $myIp)"
EOF


Displaying the content of this script:
OS> cat /jffs/scripts/wan-wol.sh

The resulting script should look like:
#!/bin/sh

scriptName="/jffs/scripts/$(basename $0)"
myIp="192.168.1.254"

/usr/bin/logger -t "${scriptName}" $$ "arp -s $myIp ff:ff:ff:ff:ff:ff"
arp -s $myIp ff:ff:ff:ff:ff:ff
/usr/bin/logger -t "${scriptName}" $$ "Check: $(arp | grep $myIp)"

Only the highlighted lines are really relevant.
The rest in only for logging.

Do not forget to make the script executable:
admin@(none):/jffs/scripts# chmod a+x wan-wol.sh

Make a copy of this script elsewhere, because it can be deleted, if a new firmware version is installed.

NVRAM

With nvram, the parameters of the router can be shown/configured.

Display the actual value of the parameter "script_usbmount" in case you need to set it back.
In the example below, it was not set:
master@(none):/tmp/home/root# nvram show 2>&1 | grep script_usbmount
script_usbmount=

Set the parameter "script_usbmount" in order for your script to be executed when a USB drive is mounted:
master@(none):/jffs/scripts# nvram set script_usbmount="/jffs/scripts/wan-wol.sh"
master@(none):/jffs/scripts# nvram commit

Check the result:
master@(none):/jffs/scripts# nvram show 2>&1 | grep script_usbmount
script_usbmount=/jffs/scripts/wan-wol.sh

After the router is rebooted, the following highlighted lines should appear in the router log (if you implement the logging lines in the script from above):




Tags: Asus, Router, NAS, WOL, ARP, Publish, RT-AC88U
July 29, 2016 at 10:39PM

2016-07-29

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

2016-07-27

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

2016-07-25

Redirecting Gargoyles SysLogs to the Synology NAS

Redirecting Gargoyles SysLogs to the Synology NAS

Other Information 

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