Oracle internals, debugging and undocumented features
Category Archives: New Features
February 6, 2012Posted by on
By Granville Bonyata
Sometimes it’s useful to have a query that always returns a certain number of rows, such as a report that returns one row for every day of the month. Using DUAL and CONNECT BY allows you to return as many rows as needed. For instance, this query returns one row for every day of the month:
SELECT TO_DATE(level||’-‘||TO_CHAR(SYSDATE,’MON-YYYY’)) each_day from dual connect by level <= TO_CHAR(LAST_DAY(SYSDATE),’DD’) ORDER BY 1;
EACH_DAY ——— 01-JAN-12 02-JAN-12 03-JAN-12 04-JAN-12 05-JAN-12 06-JAN-12 07-JAN-12 08-JAN-12 09-JAN-12 10-JAN-12 11-JAN-12 12-JAN-12 13-JAN-12 14-JAN-12 15-JAN-12 16-JAN-12 17-JAN-12 18-JAN-12 19-JAN-12 20-JAN-12 21-JAN-12 22-JAN-12 23-JAN-12 24-JAN-12 25-JAN-12 26-JAN-12 27-JAN-12 28-JAN-12 29-JAN-12 30-JAN-12 31-JAN-12
31 rows selected.
November 13, 2010Posted by on
Mr Trace speaks for itself.
It’s called Method R Trace .When you run your PL/SQL code, it automatically turns tracing on and off at the right times, fetches the trace file for you, and presents it in a list right there on the screen.
You can find demo with Cary .
and more info at Cary’s blog:
November 11, 2010Posted by on
DBCA needs to include sizes in G Bytes for Fast Recovery Area.Mb are too small these days..
If you pick Automatic Memory Management DBCA will stop you from using more memory than it is size of tmpfs /dev/shm.That is a good feature but
although I specified correct amount of memory database creation failed.After looking logs at cfgtools/dbca I found that pfile was set to use for about few gigabyte higher. It seems that dbca will add 2 Gb extra.
Also DBCA will not let you to create more than two controlfiles. I even tried using custom template.Same result.
After install I noticed that in 11.2 GSD is disabled by default and the service will show as target:offline, status:offline.
The crsctl command has cluster option so you can stop / start all cluster using crsctl command e.g. crsctl stop cluster -all
October 21, 2010Posted by on
Oracle Grid Infrastructure 220.127.116.11 introduces new feature called Redundant Interconnect
allowing for Oracle Supplied redundancy for the cluster interconnect.Redundant Interconnect Usage
enables load-balancing and high availability across multiple (up to 4) private networks (also known as interconnects).Redundant Interconnect Usage creates a redundant interconnect when you identify more than one interface as private.
With this new feature, multicast network communication on the private interconnect network is utilized on bootstrap to establish communication with peer nodes in the cluster, once communication is established network communication is then switched to unicast.
This mulitcast communication utilizes the 18.104.22.168 address (port 42424) on the private interconnect network.Therefore multicast on the private interconnect network must be enabled and properly
functioning on all cluster nodes for the mulitcast address 22.214.171.124 (port 42424).
Should multicast communication fail, the end result will be the inability for the node to join the cluster.
Starting from 126.96.36.199 onwards an I/O write error to a datafile will now crash the instance.
Before this fix I/O errors to datafiles not in the system tablespace
offline the respective datafiles when the database is in archivelog mode.
Oracle indroduced new hidden parameter _datafile_write_errors_crash_instance to control instance crash on I/O error.
Default is true .
So if you decide to keep old setting set this parameter to false.