DBA Blogs

After I make kernel 2.6.35.4 on Oracle Enterprise Linux 5.4 support Btrfs.
# cd /usr/src
# tar jxvf linux-2.6.35.4.tar.bz2
# cd linux-2.6.35.4
# cp /boot/config-2.6.18-164.el5 .config
# make && make modules_install headers_install install
# mkinitrd /boot/initrd-2.6.35.4 2.6.35.4
# rebootthen installed btrfs-progs-0.19 program.
# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

# rpm -q enterprise-release
enterprise-release-5-0.0.20

# uname -a
Linux oel 2.6.35.4 #1 SMP Wed Sep 1 20:37:04 ICT 2010 x86_64 x86_64 x86_64 GNU/Linux
and then tested to convert ext3 to btrfs (read):# mount | grep /dev/sda6
/dev/sda6 on /data type ext3 (rw)

# ls /data
linux-2.6.35.4.tar.bz2

# umount /data

# fsck.ext3 /dev/sda6
e2fsck 1.39 (29-May-2006)
/data: ...

# btrfs-convert /dev/sda6
creating btrfs metadata.
creating ext2fs image file.
cleaning up system chunk.
conversion complete.

# mount -t btrfs /dev/sda6 /data

# mount | grep /dev/sda6
/dev/sda6 on /data type btrfs (rw)

# ls /data
ext2_saved linux-2.6.35.4.tar.bz2we will see ext3/4 snapshot (ext2_saved). we can mount loopback for image in snapshot:
# mount -t ext3 -o loop,ro /data/ext2_saved/image /mnt

# ls /mnt
linux-2.6.35.4.tar.bz2check some command-line:
# btrfs-show
Label: /data uuid: 7721003c-adcb-4706-8238-68946a5e2547
Total devices 1 FS bytes used 8.73GB
devid 1 size 128.79GB used 128.79GB path /dev/sda6

Btrfs Btrfs v0.19However, if we need to roll back the conversion(we should backup). we can:
# umount /mnt
# umount /data
# btrfs-convert -r /dev/sda6Written By: Surachart Opun http://surachartopun.com

There was a discussion on the OTN General database forum, in which the OP asked creating a table with just one row and restricting that table to just one row. Here is my attempt at it.

I created a table with two columns, and the second column is a virtual column and contains a constant. I created a unique index on this column. On every insertion, this second column always evaluates to 1, and unique index (which become the function based index on virtual column) ensures that only one row remains in the table.

oracle@test # sqlplus /nolog
 
SQL*Plus: Release 11.1.0.7.0
 - Production on Sat Aug 28 19:09:16 2010
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
idle> conn test/test
Connected.
test@test> create table t1
 (c1 number, c2 generated always as (1) virtual);
 
Table created.
 
test@test> create unique index idx1 on t1(c2);
 
Index created.
 
test@test> insert into t1(c1) values (1);
 
1 row created.
 
test@test> commit;
 
Commit complete.
 
test@test> insert into t1(c1) values (1);
insert into t1(c1) values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated
 
 
test@test> insert into t1(c1) values (2);
insert into t1(c1) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated

Hello folks,

If it happens and you are in and around the Sydney area 1-2 weeks from now then you might be interested in taking part in the following events:

The first event is organized by Oracle Community in Sydney (sponsored by Pythian), during the next Sydney Oracle Meetup. The good news is that you can have free beer and pizza to recharge your batteries after a working day and have a good discussion with Oracle professionals in friendly environment.
- a – RAC, Grid, Cloud or on the way to Oracle Cloud
Wednesday 8 September 2010 17:30 p.m. to 19:30 p.m.

Usually, there are not many full day Oracle-related events happening in Sydney. More good news is that another one is coming soon. Attend and hear about new products and features from Oracle themselves and win a Car Navigator (as one of the lucky Pythian’s employees did at last Oracle event in Sydney recently :)
- b – Oracle’s Next Generation Data Centre Summit 2010 – Sydney
Thursday 16 September 2010 8:30 a.m. to 6:00 p.m.

See you around,
Yury – a DBA from down under

Hello Everyone,

This is one of my fist posts under Pythian’s blog. I will try to keep those short and simple at the beginning.

Recently I was troubleshooting a new GNS (Grid Name Services) functionality.

For more information please see here: Oracle Clusterware Network Configuration Concepts.

I have noticed that there is a-trace-level parameter in the GNS process string.
# ps -ef | grep gns
root 26790 1 2 14:41 ? 00:00:00 /u01/app/11.2.0/grid/bin/gnsd.bin -trace-level 0 -ip-address 10.10.193.201 -startup-endpoint ipc://GNS_hostrac01_23867_408c49e351f1f6a8
root 26825 17210 0 14:41 pts/1 00:00:00 grep gns


Unfortunately there is no description as of now in the documentation or MOS on how to change it to generate invaluable diagnostic information.
NOTE: I am sure the documentation will be updated in Database 12c version (c for Cloud ;)

For a time being the following should work for you:

# /u01/app/11.2.0/grid/bin/crsctl modify resource ora.gns -attr "TRACE_LEVEL=6"
# /u01/app/11.2.0/grid/bin/srvctl stop gns
# /u01/app/11.2.0/grid/bin/srvctl start gns


I hope that this advice will help you to diagnose your GNS issue.

I will blog about the way I resolved future GNS-related issues later on.

It looks like I have said too much for my very first post already.

See you around,

Yury

Just another DBA from down under

I had a chance to test about dbForge Data Compare for Oracle from Devart (@DevartSoftware). This is a good software for Oracle data comparison and synchronization and free.
Key Features
- Wide support of Oracle versions: 11g, 10g, XE, 9i, 8i, 7.3
- High-speed comparison
- Comparison of all schema tables at once
- Comparing data with different structures
- Custom mapping of tables
- Full or partial Oracle data synchronization
- Generating SQL*Plus-compatible synchronization script
- Saving data comparison settings as comparison projects for the next usage
- Start page with the latest comparisons
- Convenient UI

We can watch "dbForge Data Compare for Oracle Tutorial" for this tool.Written By: Surachart Opun http://surachartopun.com

It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.

So… all of you Oracle bloggers attending Oracle Open World 2010…
… you are invited to attend this Oracle Bloggers Meetup during OOW 2010 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.

When: Wed, 22-Sep-2010, 5:30pm

Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.


View Larger Map

Street view:

View Larger Map

See the “Lower Dining Room” on the floor plan below and ask where is the “Bloggers Meetup” booked under my name — Alex Gorbachev. These are the keywords to find us easily.

The plan is to gather at 5:30pm on Wednesday after three (or four for those of us starting on Sunday) days of intense learning. This year, you won’t need to find where to kill few hours in between of the OOW sessions and customer appreciation event at the Treasure Island — the best place to be this year is our bloggers meetup — the place where all the “cool kids” are.

As usual, thanks to Oracle Technology Network and Pythian for sponsoring the venue and drinks. HP is planning to establish a prize again this year for something fun… yes, we will again do something fun.

Last year, we were collecting signatures on our Bloggers Meetup T-Shirts so feel free to wear them this year to show your seniority at the event. ;-) This year’s activity is a surprise but if you have something cool in mind — let me know privately {last_name} at pythian.com.

For those of you who don’t know the history… The Bloggers Meetups during the Oracle Open World were started by Mark Rittman and continued by Eddie Awad and then I picked up the flag. They have been great success so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and last year’s meetup blog post update from myself.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us make sure we have the attendance numbers right. I will maintain the list here. Make sure you provide your blog URL with your comment — it’s a Bloggers Meetup in the end! Make sure you comment here if you are attending so that we have enough room, food and (most important) drinks.

Of course, do not under any circumstances forget to blog and tweet about this year’s bloggers meetup.

Looking forward to seeing all of you again this year!

Who is coming:
1. Alex Gorbachev
2. Vanessa Simmons
3. Marc Fielding
4. Arup Nanda
5. Kevin Closson
6. John Piwowar
7. Asif Momen
8. Arjen Visser
9. Kamran Agayev
10. Rob van Wijk
11. Karl Arao
12. Markus Eisele
13. Riyaj Shamsudeen
14. Mohan Dutt aka OCP Advisor
15. Jacco Landlust
16. Marco Gralike
17. Chet Justice
18. Eric Jenkinson
19. Fuad Arshad
20. Meg Bear
21. Jake Kuramoto
22. Rich (AppsLab)
23. Anthony (AppsLab)
24. Michael Aldrich
25. Sheeri K. Cabral
26. Iggy Fernandez
27. Martin Nash
28. Richard Foote
29. Espen Barroso-Gomez

[yanick@enkidu shuck]$ perl -V:news

Do you regularly scuba dive in a motley sea of other peeps’ codebase, trying to bring on surgical changes without doing too much collateral b0rking on the code formatting? If so, Steffen Mueller has a nifty trick to share with you. Using Text::FindIndent, he shows how to configure Vim such that it can magically adapts to any indentation policy.

Talking of Vim, Andy Lester let us know that Vim 7.3 is out, with a bunch of upgrade to its Perl-related support files (and that includes brand-new support for Perl 6).

CPAN is great, CPAN is awesome. But, as we all know, the leviathanesque amount of distributions it contains is sometime daunting. Which module should I use to perform $random_task? Jesse Thompson proposes to look at how many other modules are dependent on a distribution as a metric, and provides a greasemonkey script to retrieve that information straight on the CPAN search page.

This year we’ve seen the rise of a lot of über-cool cpan* and perl* utilities. The latest, announced by Cornelius, is a little speed-demon called cpansearch. Written in C (which gives it mongoose-like response time) it is a module searching tool. Already cool on its own

$ time cpans XPath | head
Source list from: http://cpan.nctu.edu.tw/modules/02packages.details.txt.gz
Apache::AxKit::Language::XPathScript     - 0.05 (M/MS/MSERGEANT/AxKit-1.6.2.tar.gz)
Apache::XPointer::XPath                  - 1.1 (A/AS/ASCOPE/Apache-XPointer-1.1.tar.gz)
AxKit2::Transformer::XPathScript         - 0 (M/MS/MSERGEANT/AxKit2-1.1.tar.gz)
B::XPath                                 - 0.01 (C/CH/CHROMATIC/B-XPath-0.01.tar.gz)
Cindy::XPathContext                      - 0 (J/JZ/JZOBEL/Cindy-0.15.tar.gz)
Class::XPath                             - 1.4 (S/SA/SAMTREGAR/Class-XPath-1.4.tar.gz)
Config::XPath                            - 0.16 (P/PE/PEVANS/Config-XPath-0.16.tar.gz)
Config::XPath::Reloadable                - 0.16 (P/PE/PEVANS/Config-XPath-0.16.tar.gz)
Email::MIME::XPath                       - 0.005 (H/HD/HDP/Email-MIME-XPath-0.005.tar.gz)

real    0m0.168s
user    0m0.016s
sys     0m0.020s

it can yet achieve higher levels of radness if combined with other Perl tools like cpanm:

# install all that is tiny
$ cpans -n Tiny | cpanm

YAPC::NA and YAPC::Europe came and went, but Karen Pauley reminds us that there’s still YAPC::Asia happening in Tokyo in October, and that the tickets are now on sale.

What? Didn’t attend any YAPC::* yet this year? Oh well, at least Matt S Trout points us where we can download videos of some of their talks.

Have you noticed that you can’t use the 5.10 features (like the smart match, say, given / when) under the Perl debugger? Pablo Marin-Garcia did, and dug to find out why. Also check the comments for a dirty way to force the debugger into a more modern attitude.

Moose is a mighty beast, but it’s not the fastest ungulate you’ll ever meet. But thanks to Dave Rolsky, it now compiles 10% faster than it used to. w00t!

Alberto Simões reports that the Perl Foundation accepted grants for 2010Q3 are in. From the look of it, lots of documentation — game development with SDL, Perlbal, Perl 6, Parrot — is coming our way.

Does anyone remember Mazinger Z? Each time we thought that giant robot achieved the peek of ultimateness, it would interface with a new ship/contraption/coffee machine and become even awesomer. Moose, with its MooseX cohorts, is a little bit like that. But with antlers. Florian Ragwitz shows us how the raw power of parameterized traits given by MooseX::Role::Parameterized can now be harnessed by MooseX::Declare.

use MooseX::Declare;
use 5.10.0;

role Gizmo ( Str :$codename ) {

    has 'upgraded' => ( is => 'rw' );

    my %gizmo_ability = (
        'wingy_thingy'  => 'fly like a butterfly',
        'smash_o_tron'  => 'squish things',
        'expresso_core' => 'make darn good coffee',
    );

    method "summon_$codename" {
        say "Giant robot summons its $codename";
        $self->upgraded(1);
    }

    method unleash_power {
        say $self->upgraded
            ? "Giant robot can now $gizmo_ability{ $codename }"
            : "No gizmo? No super-power for you"
            ;
    }
}

class GiantRobot::Omega {
    with Gizmo => { codename => 'expresso_core' };
}

my $robot = GiantRobot::Omega->new;

$robot->unleash_power;         # No gizmo? No super-power for you

$robot->summon_expresso_core;  # expresso core, to me!

$robot->unleash_power;         # *mouahaha*

[yanick@enkidu shuck]$ perl -E'sleep 2 * 7 * 24 * 60 * 60 # see y'all in 2 weeks!'

Log Buffer is the DBA community’s Carnival of the Vanities-style blog of blogs about the database world. It is the source DBAs and others interested in databases count on for weekly news of industry happenings, and what their colleagues around the world are working on.

This week’s edition, Log Buffer #201 has been compiled and published by Craig Mullins on his Data and Technology Today blog. Craig has contributed to Log Buffer since the early days, and we’re happy to have him involved yet again. Enjoy!

It’s been more than 2 years and a half since I last blogged on Oracle performance. I had the feeling I could not carry on this extra time activity when I started managing a 20 people DBA team and 4 technologies (including 2  Oracle competitors- Microsoft and Sybase which has ASE and IQ). It would [...]

I submitted an abstract to the Oracle OpenWorld 2010 that was found to be worthwhile enough to be accepted. Here are the session details: Speaker(s) Christian BILIEN, BNP PARIBAS Corporate Investment Banking, Head of the DBAs Monday, September 20, 2:00PM | Moscone South, Rm 236 60 min. Session ID: S314649 Title: Large-Scale Oracle RAC (and [...]

Here is a scenario where a CREATE CONTROLFILE was used to recover a database but one of the datafiles was (inadvertently) excluded from the CREATE CONTROLFILE statement.
The Database when, OPENed, sets the file as a MISSING file. This is because it is present in the Data Dictionary but not in the Controlfile.
The datafile can be "added" back (if it is really available on disk or has been restored earlier) and "re-recovered".

SQL> REM ########### The controlfile was created without the "example01.dbf" datafile
SQL> REM #### An INCOMPLETE RECOVERy was performed
SQL> REM #### The database was OPENed with RESETLOGS
SQL> REM ##  The datafile has been added back with an ALTER DATABASE RENAME FILE 'MISSING05.DBF' TO 'example01.dbf'

SQL> @create_cntrlfile
SQL> spool create_cntrlfile
SQL> 
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORT24FS" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oracle_fs/Databases/ORT24FS/redo01.dbf'  SIZE 50M,
  9    GROUP 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf'  SIZE 50M,
 10    GROUP 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf'  SIZE 50M,
 11    GROUP 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf'  SIZE 50M,
 12    GROUP 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf'  SIZE 50M
 13  -- STANDBY LOGFILE
 14  DATAFILE
 15    '/oracle_fs/Databases/ORT24FS/system01.dbf',
 16    '/oracle_fs/Databases/ORT24FS/sysaux01.dbf',
 17    '/oracle_fs/Databases/ORT24FS/users01.dbf',
 18    '/oracle_fs/Databases/ORT24FS/undotbs.dbf'
 19  CHARACTER SET WE8ISO8859P1
 20  ;

Control file created.

SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','OFF');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 8 DAYS');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 4
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');

PL/SQL procedure successfully completed.

SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 4325510 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325510 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01119: error in creating database file
'/oracle_fs/Databases/ORT24FS/temp01.dbf'
ORA-17610: file '/oracle_fs/Databases/ORT24FS/temp01.dbf' does not exist and no
size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' size 100M autoextend on next 100M maxsize 8000M;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle_fs/Databases/ORT24FS/undotbs.dbf
/oracle_fs/Databases/ORT24FS/users01.dbf
/oracle_fs/Databases/ORT24FS/sysaux01.dbf
/oracle_fs/Databases/ORT24FS/system01.dbf
/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005

SQL> alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
  2  as '/oracle_fs/Databases/ORT24FS/example01.dbf';
alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
*
ERROR at line 1:
ORA-19723: Cannot recreate plugged in read-only datafile 5
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'


SQL> alter database rename file '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
  2  to '/oracle_fs/Databases/ORT24FS/example01.dbf'; 

Database altered.

SQL> recover datafile 5;
ORA-00279: change 4325511 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325511 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 5 online;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1           4034914 23-JUL-10                 3632263 04-JUN-10
PARENT     725069822                  0 NO

           2           4325718 25-AUG-10                 4034914 23-JUL-10
CURRENT    727983988                  1 NO


SQL> exit 

The example01.dbf file was not included in the CREATE CONTROLFILE statement although it was present on disk. The OPEN RESETLOGS after the Incomplete Recovery created a new Incarnation of the database. Yet, I was able to "add" the datafile back by RENAMEing the "MISSING" file.

.
.
.

There is a little know fact that Oracle table compression doesn’t work with tables that have more than 255. It’s has been reported here and compression limitations in general here.

As a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I should test whether HCC works with tables that have more than 255 columns.

And it does. Here’s my test case.

Setup:

create table ctest (
col_1  varchar2(30) default 'JUSTSOMEDATA',
col_2  varchar2(30) default 'JUSTSOMEDATA',
...
col_255  varchar2(30) default 'JUSTSOMEDATA'
);
insert into ctest (col_1) select 'OTHERDATA' from dba_objects;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
insert /*+APPEND*/ into ctest select * from ctest;
commit;

Build various scenarios (NOTE: second create adds the 256th column):

/*REGULAR*/
create table ctest_cmp compress as select * from ctest;
create table ctest_cmp1 compress as select t.*, 'NEW' col_256 from ctest t;

/*HCC QUERY*/
create table ctest_hcc compress for query low as select * from ctest;
create table ctest_hcc1 compress for query low  as select t.*, 'NEW' col_256 from ctest t;

/*HCC ARCHIVE*/
create table ctest_hccA compress for archive low as select * from ctest;
create table ctest_hccA1 compress for archive low  as select t.*, 'NEW' col_256 from ctest t;

And the results:

SQL> select round((bytes)/1024/1024) as mb ,  segment_name from user_segments t where segment_name like 'CTEST%' order by segment_name;

        MB SEGMENT_NAME
---------- ----------------------------
       824 CTEST
         3 CTEST_CMP
       824 CTEST_CMP1
         1 CTEST_HCC
         1 CTEST_HCC1
         1 CTEST_HCCA
         1 CTEST_HCCA1

Michigan OakTable Symposium (MOTS) is a unique event taking place just before Oracle OpenWorld — 16-17 September. Why unique? This is the first conference (is it not?) where all presenters are members of OakTable Network, a group that gathers number of like-minded IT professionals with scientific approach to Oracle database technology and to the life in general.

It happens in Ann Arbor, Michigan — a place I wanted to visit for a very long time. For those of you who are cost conscious and don’t have much in their education budget, this conference is a great value priced at a third of the Oracle OpenWorld pass and I can assure you that quality of presentations is on par with or higher then the top Oracle OpenWorld sessions. The conference is organized by volunteers from OakTable Network and few good friends as a non-commercial event so you will hear no marketing crap whatsoever.

Unlike, huge OpenWorld crowd, which has always intimidated me a little bit, MOTS is going to be a cozy event with strictly limited cap of only 300 participants – ideal size to support peer networking and opportunity to mingle with some of the folks you’ve been dying to talk to.

I especially enjoyed this video promotion:

My immediate reaction on the OakTable list was:

Am I hallucinating or I just saw the image of me right next to Cary, Mogens and Jonathan? As much as I’d be proud, I’d never qualify myself as deserving such comparison.

And Mogens clarified it:

The order in which we appear is based on TAOACDOEL (Total Amount Of Alcohol Consumed During Ones Entire Lifetime – pronounced “tao-AC-dole”). You’re still young, so you only made 4th position.

As to Cary’s and Jonathan’s TAOACDOEL, it is based on the premise that they have lived several prior lives in mining areas as gold diggers and drunkards. Otherwise they’d never make the list.

This is my first life, by the way.

I’m doing three presentation there:

• Battle Against Any Guess
• Oracle ASM 11g – the Evolution
• Under the Hood of Oracle Clusterware 2.0 – 11gR2 Grid Infrastructure

My colleague Chen Shapira does two sessions as well:

• Everything a DBA needs to know about TCP/IP Networks
• NoSQL Deep Dive

There is one more Pythian colleague that will be speaking at the conference but I’ll leave it for a surprise later. The full agenda is available but I believe it will be updated shortly.

Now that you checked the list of speakers and abstracts, I’m sure you’d be dying to get there for the two exciting days.

How to compile new oci8 in PHP source?
oci8(PHP) is extension for Oracle Database allows you to access Oracle databases. It can be built with PHP 4.3.9 to 5.x. It can be linked with Oracle 9.2, 10.2, 11.1, or 11.2 client libraries.When we find new version (oci8) and want to upgrade in PHP, How?
If we want to use oci8 1.4.3. we may wait PHP version 6.0.0 or older (but OCI8 1.4.2 is included in PHP 5.3.3).
So, download oci8 1.4.3 and compile in php 5.3.3:
- check PHP source path.
# pwd
/SRC/php-5.3.3- check old configuration.
# cat config.nice

#! /bin/sh
#
# Created by configure

'./configure' \
'--with-apxs2=/usr/local/apache/bin/apxs' \
'--with-oci8=instantclient,/u01/app/instantclient_11_2' \
"$@"
- move old oci8 and use new oci8 (1.4.3)
# mv ext/oci8 ext/oci8-org
# ls ../oci8-1.4.3.tgz
../oci8-1.4.3.tgz
# tar zxvf ../oci8-1.4.3.tgz -C ext/
# mv ext/oci8-1.4.3 ext/oci8
# make clean && make
# /usr/local/apache/bin/apachectl stop
# make install
# /usr/local/apache/bin/apachectl start- check again.
we will see new version(oci8).Written By: Surachart Opun http://surachartopun.com

As of this afternoon, version 11.2.1.3.1 of Oracle’s Exadata storage server software, is out in the wild. This is the first publicly available version of the 11.2.1.3 branch, a major release including a full OS image with an update to Oracle Enterprise Linux 5.5. A number of bugs causing cell server crashes and hangs have been fixed, including 9472035, 9870117, and 9722560.

Both storage server and database portions of this patch can be applied in a rolling fashion, avoiding entire database downtime. The minimum prerequisite version is 11.2.1.2.3, with an exception made for Exadata V1 devices at 11.1.3.3.0. If you’re running an earlier version, apply the 11.2.1.2.3 patch first.

This topic seems to be some kind of “Evergreen”, since it comes up regularly in my courses and also in the OTN Discussion Forum. I decided therefore to cover it briefly here in order to be able to point to this post in the future. Short answer: Probably not If the intention of the reorganizing [...]

Continuing the previous posts (1) and (2), in both of which I ran PLSQL procedures, I now run a test with a simple SQL select that retrieves close to 27million rows from the database server to the client (the SQLPLUS program).

The first sesion runs a SELECT with termout off but spooling to a file :

select object_id from obj_list; 

Monitoring shows :

SQL> l
1  select to_char(sysdate,'DD-MON HH24:MI:SS') Collection_datestamp, x.last_call_et, x.status, x.sql_id,
2  y.sql_text
3  from v$session x, v$sqlarea y
4  where x.sql_id = y.sql_id(+)
5* and x.sid = &session_id
SQL> /
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 15:51:52            0 INACTIVE bxya78ra815js select object_id from obj_list

SQL> /
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 15:51:57            0 ACTIVE   bxya78ra815js select object_id from obj_list

SQL> /
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 15:52:04            0 ACTIVE   bxya78ra815js select object_id from obj_list

SQL> /
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 15:52:13            0 INACTIVE bxya78ra815js select object_id from obj_list

SQL>
SQL> /
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 15:53:32            0 INACTIVE bxya78ra815js select object_id from obj_list

SQL>

SQL> select to_char(sysdate,'DD-MON HH24:MI:SS') Collection_Datestamp, executions, buffer_gets, rows_processed
2  from v$sqlstats where sql_id = 'bxya78ra815js';

COLLECTION_DATE EXECUTIONS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ----------- --------------
22-AUG 15:55:32          1      441971       13190847

SQL>
SQL> /

COLLECTION_DATE EXECUTIONS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ----------- --------------
22-AUG 15:56:04          1      505590       15090747

SQL>
SQL> /

COLLECTION_DATE EXECUTIONS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ----------- --------------
22-AUG 15:57:22          1      646127       19291147

SQL>
SQL> /

COLLECTION_DATE EXECUTIONS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ----------- --------------
22-AUG 15:58:27          1      760275       22702847

SQL>
SQL>
SQL> @sess_sql_statement
Enter value for session_id: 141
old   5: and x.sid = &session_id
new   5: and x.sid = 141

COLLECTION_DATE LAST_CALL_ET STATUS   SQL_ID        SQL_TEXT
--------------- ------------ -------- ------------- ----------------------------------------------------------------
22-AUG 16:00:16           27 INACTIVE

SQL>  

Thus, the LAST_CALL_ET was getting reset at every new FETCH call by the client and STATUS kept switching between 'ACTIVE' and 'INACTIVE'.

The tkprof of the trace shows :

select object_id
from
obj_list


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   538612     58.38      74.70     363739     900989          0    26930545
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   538614     58.38      74.70     363739     900989          0    26930545

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
26930545  TABLE ACCESS FULL OBJ_LIST (cr=900989 pr=363739 pw=0 time=107722594 us) 

A total of 538,612 FETCH calls from the client (SQLPLUS) to the server. Each FETCH call is a new call to the database server process.

.
.
.

Hello, and welcome to Log Buffer, a weekly blog about happenings in the database industry. Today is a celebration of the 200th edition of Log Buffer, and we’d like to give a special shout out to all of our guests hosts over the past 4 plus years. Their contributions have been invaluable in helping to grow Log Buffer into the community source of favorite database-related blog posts that it is today.

We’ve come a long way since our inaugural issue, Log Buffer #1, published July 14, 2006. This week enjoy mix of contributed links and the latest posts from past contributors.

Mart Rittman (Log Buffer #11) reminds everyone that OBIEE 11gR1 is available for download. His colleague, Venkatakrishnan J follows up with posts on a few new and important features including reporting on CLOBs – Lookups, map views – integration with Mapviewer, handling double columns – ID/description interoperability, lookup tables – sparse and dense lookups and vertical clustering.

Adam Machanic, of SQLblog.com and host of Log Buffer #21 is reflecting on 6 plus years of blogging in the database world, and wants to know who his readers are.

Lewis Cunningham (Log Buffer #32) notes that the call for abstracts is open for ODTUG/Kaleidoscope 2011. Submission deadline is October 26, 2010. He also talks about a problem with success and offers good advice to make sure you’re not getting “crusty”.

Coskan Gundgobar (Log Buffer #49) responds to a slow system waiting on library cache lock and credits Tanel Poder’s scripts as the solution.

Jeremy Schneider (Log Buffer #55) points out that DBCA is missing from the 11gR2 ASM/grid installation.

Hubert Depesz Lubaczewski (Log Buffer #57) writes about OMNIPITR – hot-backups on slave – they really work.

Edie Awad (Log Buffer #73) references 5 interesting posts in his Monday, August 16th roundup including working with long columns, and database performance for developers.

Robert Treat (Log Buffer #127) shares on his personal opinion on what people should do following the announcement that OpenSolaris is cancelled and is to be replaced with Solaris 11 Express.

Gary Myers (Log Buffer #181) has a little fun with passwords on Sydney Oracle Lab.

Kent Milligan suggests to process your DB2 for i indexes in parallel and thus improve the overall performance of your database server.

Steve Karam, the Oracle Alchemist reports strange behaviour with MEMORY_TARGET.

Chris Presley shares Paul White’s post on viewing another session’s temporary table.

While a few have fallen off the map, it’s nice to see many members of our database blogging community still going strong.

I already knew about git-achievements (and partake in the fun). But earlier this week Dean showed me Unit Testing Achievements.

My first thought after seeing it was: “cool, I want to port that to Smolder!”. My second thought was: “hey, if we can do it for Git, and for testing, why not for Perl itself?” Perl::Critic and Perl::Tidy already use PPI for their magic, surely it wouldn’t be too hard to harness its power for a little bit of fun?

And, indeed, it wasn’t:

[yanick@enkidu ~]$ cat foo.pl
print "Hello world", $/;

[yanick@enkidu ~]$ alias perl="perl-achievements -r --"

[yanick@enkidu ~]$ perl foo.pl
Congrats! You have unlocked 1 new achievement

************************************************************
*** Cryptomancer
*** Level 1

Used Perl magic variables.

Variables used: $/
************************************************************
Hello world

The code for this new Perl::Achievements is on Github. For now I was only aiming at churning out a prototype, so the code’s a mess, there’s no documentation whatsoever, and there are only two achievements to unlock. Buuut if I have time, and if it amuses peoples, it would be easy to make it grow into something a little more… interesting.