Showing posts with label 11gR2. Show all posts
Showing posts with label 11gR2. Show all posts

Sunday, August 24, 2014

Crossplatform transportable tablespaces - part 2

It took some time since I wrote a first post about TTS migration but I finished that project literally hours before my summer break. Now after couple of days while I enjoyed thermal waters and good wine of Hungary it's time to write next post.

As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC but my goal was to minimize downtime of production database.

Source database datasheet:
- version 10.2.0.4 
- OS - HP-UX 
- existing backup using data files copy 
- there is a one backup set per data file 
- daily incremental backups are recovered into data files and keep in FRA

On target server a new version of Oracle 12.1.0.1 has been installed and configured with ASM. New database with same character set as source database has been created as well.


Target database datasheet:
- version 12.1.0.1
- OS -Linux 64 bit
- storage - ASM

Transportable tablespaces (TTS) allow us to migrate data between databases but it is DBA responsibility to migrate rest of objects like views and PL/SQL code using for example DataPump. Before I have started a work on TTS I did the following preparation steps:
  1. On source database identify list of tablespaces and it's datafiles to move to new server
  2. On source database identify owners of objects included in TTS
    select distinct owner from dba_tables where tablespace_name like ('LIST','OF','TABLESPACES','TO','MIGRATE');
    
  3. On source database verify that tablespaces are self contained
    begin
       SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'LIST,OF,TABLESPACES,TO,MIGRATE', full_check => TRUE);
    end;
    /
    
    select * from SYS.TRANSPORT_SET_VIOLATIONS;
    
  4. On target database create owners for all objects included in TTS 

This is list of steps I performed to achieve my goal.
  1. Copy existing data files copies into new server - if other location is used on new server change script in point 2
  2. Create a script to convert data file from data file copy into data file in new location
    select 'convert datafile ''' || b.name || ''' format ''+DATA/POCDB/TTS/' || REGEXP_REPLACE(f.name,'(/disk\d/oradata/XXX/)','') || ''' from platform ''HP-UX IA (64-bit)'';' from V$BACKUP_COPY_DETAILS b, v$datafile f where f.file# = b.file#;
    
  3. Convert file using script from point 2. Example output
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_9x3xjcon_.dbf' format '+DATA/POCDB/TTS/reports01.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_aas24412_.dbf' format '+DATA/POCDB/TTS/reports02.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_22ee1445_.dbf' format '+DATA/POCDB/TTS/reports03.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_34ddr545_.dbf' format '+DATA/POCDB/TTS/reports04.dbf' from platform 'HP-UX IA (64-bit)';
    
  4. Copy daily incremental backupsets into new server - if other location is used on new server change script in point 5
  5. Create a script to apply incremental backupset into new files
    set linesize 600 pagesize 999 feedback off head off trimspool on
    select 'recover from platform ''HP-UX IA (64-bit)'' foreign datafilecopy ''' || name || ''' from backupset ''' || handle || ''';'
    from V$BACKUP_DATAFILE bd, v$datafile d, V$BACKUP_PIECE bp where bd.file# = d.file#
    and bp.set_count = bd.set_count and handle is not null
    and bp.COMPLETION_TIME > sysdate -1
    order by bp.set_count;
    
  6. Recover data files copies
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports01.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkp6w_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports02.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkxg5_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports03.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppk4w9_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports04.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkbws_.bkp';
    
  7. Run steps 4 to 6 until cut over date
  8. Run incremental backup on source
  9. Switch all required tablespace into read only mode
  10. Export transportable tablespaces using DataPump  using parameter file like this
    directory=XXX_DPDUMP
    dumpfile=tts_aws1.dmp
    logfile=tts.log
    TRANSPORT_TABLESPACES='TABLESPACES', 'TO', 'EXPORT'
    TRANSPORT_FULL_CHECK=y
    
    EXPDP command
    expdp parfile=tts.par
    
  11. Run incremental backup on source
  12. Copy backupsets from point 8 and 11 into new server
  13. Create a script to apply incremental backupset into new files (like in point 5)
  14. Import transportable tablespaces using dump file from point 9 and all converted files. In my case first attempt took very long as I didn't excluded stats and Oracle was gathering stats during importing process. This operation can be postponed to next phase using EXCLUDE option. Example IMPDP parameter file
    directory=AWS
    dumpfile=tts_aws1.dmp
    logfile=aws_tts_import1.log
    exclude=TABLE_STATISTICS,INDEX_STATISTICS
    TRANSPORT_DATAFILES=+DATA/POCDB/TTS/reports01.dbf,
    +DATA/POCDB/TTS/reports02.dbf,
    +DATA/POCDB/TTS/reports03.dbf,
    +DATA/POCDB/TTS/reports04.dbf
    
    Run IMPDP command
    impdp parfile=imp.par
    
  15. Export source database code and users
    expdp directory=DPDUMP dumpfile=code.dmp exclude=TABLE_DATA full=y
    
  16. Import PL/SQL code - quick, dirty approach - but it was enough fot that case
    impdp directory=AWS TABLE_EXISTS_ACTION=SKIP dumpfile=code.dmp log=code_import.log full=y
    
  17. Perform backup of new database and gather new statistics

 
Performing all steps above allow me to migrate 1 TB database from HP-UX into Linux with 30 min downtime on source database. As it was POC I left source database working as main production database. For real migration time it's necessary to add time to recover last incremental backup and import TTS on new platform and also resolve issue with time necessary to gather statistics on new platform. Probably copy existing stats using PL/SQL will be solution there but it has to be check in next phase.

This post is long enough so I leave lesson learned to the next one.

regards,
Marcin



Tuesday, July 1, 2014

Don't delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> select status from v$instance;

STATUS
------------------------------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

OK open database doesn't work. So what happen when I disable a flashback logging
SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Well still doesn't work - but what is a flashback state ?
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY

SQL> select * from v$restore_point;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 33 seq 476 thread 1:
"/u01/app/oracle/fast_recovery_area/DEV/flashback/o1_mf_9nq1wbon_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
OK at least there is some information about root cause - it is looking for missing flashback files. Information about flashback database is keep inside control file so let's try to recreate control file using trace file
SQL> alter database backup controlfile to trace as '/tmp/control.ctl';

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size                  2253024 bytes
Variable Size             822087456 bytes
Database Buffers          503316480 bytes
Redo Buffers                8519680 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Backup of control file has been created in trace file and edited as follow
[oracle@dev-6 tmp]$ vi control.ctl
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/dev/system01.dbf',
  '/u01/app/oracle/oradata/dev/sysaux01.dbf',
  '/u01/app/oracle/oradata/dev/undotbs01.dbf',
  '/u01/app/oracle/oradata/dev/users01.dbf',
  '/u01/app/oracle/oradata/dev/USER.dbf',
  '/u01/app/oracle/oradata/dev/DATA.dbf',
  '/u01/app/oracle/oradata/dev/DATA_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/ARCHIVE.dbf',
  '/u01/app/oracle/oradata/dev/apex01.dbf'
CHARACTER SET AL32UTF8
;

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/nim_backup/backup/oracle/db/dev/%F''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');

RECOVER DATABASE;

-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/app/oracle/oradata/dev/bct_01.log' REUSE;

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dev/temp01.dbf' REUSE;
Let's try to create new control files and open database
[oracle@dev-6 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:43:45 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> @control.ctl

Control file created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Media recovery complete.

Database altered.


System altered.


Database altered.


Tablespace altered.


SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
YES

SQL>
Database has been started and opened - all done. 

I blogged about that cause I have to solve this problem several time when due to space restriction flashback logs has been deleted by other DBA manually instead of disabling and enabling flashback on database. Just keep in mind if you need to release space in FRA don't delete flashback logs manually but turn off flashback on the database 

regards, 
Marcin

Sunday, January 27, 2013

How dNFS database clone works – part 1

There is new feature in Oracle 11.2.0.2 called dNFS clone. It has been described by Kevin Closson on his blog post - Oracle Database 11g Direct NFS Clonedb Feature  and very good configuration description has been posted by Tim Hall on his blog - Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2). I have played with it just after I found both blogs but never think how Oracle implemented that feature. I came back to it when my colleague asked me if I ever use that in production environment and what are performance implications of using it. 

Oracle is using Direct NFS option as Oracle Disk Manager 

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
(is there any other still in use ? I remember one – Veritas Quick I/O module for Veritas filesystem / volume manager). As far as I understand ODM is a library which is providing IO functionality for database. As Oracle can be used on different hardware and Oracle kernel can call ODM functions to read or write data from storage and from Oracle kernel perspective it doesn’t really matter how ODM fulfill IO request.

When I started thinking how Oracle implemented that feature, two ideas came into my mind:
  • Oracle keep in-memory map of changed blocks and know where to go to find latest block – actually I never believe in it – this too costly solution 
  • Oracle is just doing read from both sources (backup and NFS) and it is comparing which block is newer if necessary
But guessing work is against BAAG and I want to check what database is actually doing when we are using dnfs clone. I have started with two scenarios – read data which have been changed after clone has been established and read unchanged data. Thanks to Oracle instrumentation (trace 10046), strace and wireshark I see much more details now but still there is long way to have whole picture. Let me share my findings with you.

Read changed data in clone 

I added one row to scott.bonus table which was empty when I cloned database and I executed simple query – select * from scott.bonus – below is a mixture output from all three monitoring sources
EXEC #140195143601328:c=0,e=42,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=953918076,tim=2013-01-17 14:29:43:108500
WAIT #140195143601328: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=13891 tim=2013-01-17 14:29:43:108660
WAIT #140195143601328: nam='Disk file operations I/O' ela= 5 FileOperation=2 fileno=4 filetype=2 obj#=75337 tim=2013-01-17 14:29:43:108800
First WAIT line with disk OS CALL (thanks to @CaryMillsap Method R training I started to thinking about WAIT lines as I should before) has no strace nor wireshark activity and call itself is very short – 5us –so I just ignored it for time being.
statfs("/u01/app/oracle/backup/data_data_D-TESTSE_I-4169104998_TS-USERS_FNO-4_10nvidnv", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=5158559, f_bfree=2226212, f_bavail=1964171, f_files=1310720, f_ffree=1268869, f_fsid={1982750833, 2010996514}, f_namelen=255, f_frsize=4096})
open("/u01/app/oracle/backup/data_data_D-TESTSE_I-4169104998_TS-USERS_FNO-4_10nvidnv", O_RDWR|O_DSYNC) = 11
fcntl(11, F_DUPFD, 256) = 257
close(11)               = 0
fcntl(257, F_SETFD, FD_CLOEXEC) = 0
fstatfs(257, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=5158559, f_bfree=2226212, f_bavail=1964171, f_files=1310720, f_ffree=1268869, f_fsid={1982750833, 2010996514}, f_namelen=255, f_frsize=4096}) = 0
WAIT #140195143601328: nam='Disk file operations I/O' ela= 182 FileOperation=2 fileno=4 filetype=2 obj#=75337 tim=2013-01-17 14:29:43:109050
WAIT #140195143601328: nam='Disk file operations I/O' ela= 160 FileOperation=2 fileno=4 filetype=2 obj#=75337 tim=2013-01-17 14:29:43:109150
There are next two i/o related calls and above we can see that backup file has been opened , handler has been duplicated and fstatfs call has been executed. There is another mystery here I have seen couple of system calls and then two entries from Oracle trace files – not sure why it look like this – as we are dealing here on microsecond level it is hard to match tim / ela from Oracle trace file with actuall OS calls from strace – but I will try to do it later - in next post probably. Right now we now that Oracle process opened backup file and it is ready to read from it. 
Query is using full scan on table so first of all Oracle need to read segment header
sendmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\0\214\252\16\236\235\0\0\0\0\0\0\0\2\0\1\206\243\0\0\0\3\0\0\0\6\0\0\0\1\0\0\0000\253\315\357\253\0\0\0\24oraclese.localdomain\0\0\3241\0\0\3241\0\0\0\2\0\0\3241\0\0\3242\0\0\0\0\0\0\0\0\0\0\0$\1\0\7\1\1\240\202\3\0\0\0\0\222\364,

V3 READ Call (Reply In 44853), FH:0x8f3a5fe4 Offset:256131072 Len:8192,44846,2013-01-17 14:29:43.109264,192.168.1.60,192.168.1.20,NFS,210

poll([{fd=32, events=POLLIN}], 1, 500) = 1 ([{fd=32, revents=POLLIN}])
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0 \200\252\16\236\235\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\0 \0\0\0\0\0\0004\24\0\0\0\0\0\0!z\0\1\1\0\0\0 z\0\1!z\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\0\0\0\0I&\1\0\0\0\0\20 z\0\1\10\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\

V3 READ Reply (Call In 44846) Len:8192,44853,2013-01-17 14:29:43.144922,192.168.1.20,192.168.1.60,NFS,1150

recvmsg(32, 0x7fffe701bc20, MSG_DONTWAIT|MSG_NOSIGNAL) = -1 EAGAIN (Resource temporarily unavailable)

WAIT #140195143601328: nam='db file sequential read' ela= 36202 file#=4 block#=31266 blocks=1 obj#=75337 tim=2013-01-17 14:29:43:145410
We can see that process is using sendmsg function sending NFS READ call (captured by wireshark) and it is waiting for answer. Due to my NFS and network configuration respond has been split into two recvmsg system calls but it is one NFS READ Reply – one database block ( 8kB) has been read and this action has been registered in trace file as db file sequential read. Now process is going to execute full scan and read blocks from table
sendmsg(32, {msg_name(0)=NULL, msg_iov(2)=[{"\200\0\0\214\252\16\236\237\0\0\0\0\0\0\0\2\0\1\206\243\0\0\0\3\0\0\0\6\0\0\0\1\0\0\0000\253\315\357\253\0\0\0\24oraclese.localdomain\0\0\3241\0\0\3241\0\0\0\2\0\0\3241\0\0\3242\0\0\0\0\0\0\0\0\0\0\0$\1\0\7\1\1\240\202\3\0\0\0\0\222\364,V3 READ Call (Reply In 44888), FH:0x8f3a5fe4 Offset:256139264 Len:32768  ; 
V3 READ Call (Reply In 44888), FH:0x8f3a5fe4 Offset:256139264 Len:32768
V3 READ Call (Reply In 44862), FH:0x8f3a5fe4 Offset:256172032 Len:8192
poll([{fd=32, events=POLLIN}], 1, 500) = 1 ([{fd=32, revents=POLLIN}])
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0 \200\252\16\236\240\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0,
V3 READ Reply (Call In 44855) Len:8192,44862,2013-01-17 14:29:43.158250,192.168.1.20,192.168.1.60,NFS,1150,,,,
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\200\200\252\16\236\237\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0
V3 READ Reply (Call In 44855) Len:32768,44888,2013-01-17 14:29:43.159262,192.168.1.20,192.168.1.60,NFS,1110,,,,
recvmsg(32, 0x7fffe7016aa0, MSG_DONTWAIT|MSG_NOSIGNAL) = -1 EAGAIN (Resource temporarily unavailable)
WAIT #140195143601328: nam='db file scattered read' ela= 13870 file#=4 block#=31267 blocks=5 obj#=75337 tim=2013-01-17 14:29:43:159580
There are two NFS READ calls and two NFS READ Reply and 5 blocks has been read – it exactly match Oracle trace file entry – db file scattered read – with blocks equal to 5. So this scenario is straight forward – Oracle is going directly to NFS to read changed blocks – but still I can’t be sure how it decide to go there. Let’s check now what will happen when we try to read unchanged blocks.

Read unchanged blocks.  

Oracle is executing query – select * from scott.salgrade – and here is a trace / strace / wireshark output. Process is executing table full scan so it need to read segment header first
sendmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\0\214\252\16\236\245\0\0\0\0\0\0\0\2\0\1\206\243\0\0\0\3\0\0\0\6\0\0\0\1\0\0\0000\253\315\357\253\0\0\0\24oraclese.localdomain\0\0\3241\0\0\3241\0\0\0\2\0\0\3241\0\0\3242\0\0\0\0\0\0\0\0\0\0\0$\1\0\7\1\1\240\202\3\0\0\0\0\222\364\16\216\312.\252\227\0\0\0\0\0\0\0\0\25`\ 
V3 READ Call (Reply In 44938), FH:0x8f3a5fe4 Offset:1327104 Len:8192,44930,2013-01-17 14:29:55.162780,192.168.1.60,192.168.1.20,NFS,210    
sendmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\0X\252\16\236\246\0\0\0\0\0\0\0\2\0\1\206\243\0\0\0\3\0\0\0\0\0\0\0\1\0\0\0000\253\315\357\253\0\0\0\24oraclese.localdomain\0\0\3241\0\0\3241\0\0\0\2\0\0\3241\0\0\3242\0\0\0\0\0\0\0\0", 92}], msg_controllen=0, msg_flags=0}, MSG_DONTWAIT|MSG_NOSIGNAL) = 92
poll([{fd=32, events=POLLIN}], 1, 500) = 1 ([{fd=32, revents=POLLIN}])
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0 \200\252\16\236\245\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\0P\370\nZ\0\0\0\0P\370\nZP\366\241\37\0\0
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 7996}], msg_con
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\0\30\252\16\236\246\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\0P\370\nZ\0\0\0\0P\370\nZP\366\241\37\0\0
V3 READ Reply (Call In 44930) Len:8192 44938 2013-01-17 14:29:55.163827,192.168.1.20,192.168.1.60,NFS,1178
recvmsg(32, 0x7fffe701bc20, MSG_DONTWAIT|MSG_NOSIGNAL) = -1 EAGAIN (Resource temporarily unavailable)         
WAIT #140195143601328: nam='db file sequential read' ela= 1323 file#=4 block#=162 blocks=1 obj#=75338 tim=2013-01-17 14:29:55:164020
At first sight nothing has been changed – Oracle is doing NFS READ call and got a response. But if you will take a look on returned data – database block is full of zeros. It isn’t a surprise as NFS files are sparse one and it should contain only changed blocks. That read activity has been recorded in trace file as db file sequential read but we still have no data - so what is going next
pread(257, "#\242\0\0\242\0\0\1\250\34\17\0\0\0\1\4\317\215\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\10\0\0\0\234\n\0\0\0\0\0\0\10\0\0\0\10\0\0\0\250\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\10\0\0\0\10\0\0\0\250\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\5\0\0\0\240\0\0\1"..., 8192, 1327104) = 8192
WAIT #140195143601328: nam='Disk file operations I/O' ela= 49 FileOperation=9 fileno=4 filetype=2 obj#=75338 tim=2013-01-17 14:29:55:164170
Disk read is performed on file with handler 257 and as we can check in first strace output this file is a backup data file. Oracle is reading segment data from here and recorded that operation in trace file as ‘Disk file operations I/O’. Based on that behaviour I think there is no map of changed blocks but process is reading NFS first and if NFS will return zeroed block it is reading backup data file. Let see rest of full scan operation.
sendmsg(32, {msg_name(0)=NULL, msg_iov(2)=[{"\200\0\0\214\252\16\236\250\0\0\0\0\0\0\0\2\0\1\206\243\0\0\0\3\0\0\0\6\0\0\0\1\0\0\0000\253\315\357\253\0\0\0\24oraclese.localdomain\0\0\3241\0\0\3241\0\0\0\2\0\0\3241\0\0\3242\0\0\0\0\0\0\0\0\0\0\0$\1\0\7\1\1\240\202\3\0\0\0\0\222\364\16\216\312.\252\227\0\0\0\0\0\0\0\0\25`\ 
V3 READ Call (Reply In 44972), FH:0x8f3a5fe4 Offset:1335296 Len:32768  ; V3 READ Call (Reply In 44978), FH:0x8f3a5fe4 Offset:1368064 Len:8192
poll([{fd=32, events=POLLIN}], 1, 500) = 1 ([{fd=32, revents=POLLIN}])
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0\200\200\252\16\236\250\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\0P\370\nZ\0\0\0\0P\370\nZP\366\241\37\
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 32572}], msg_co
V3 READ Reply (Call In 44940) Len:32768 44972,2013-01-17 14:29:55.168776,192.168.1.20,192.168.1.60,NFS,1514    
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\200\0 \200\252\16\236\251\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0\1\0\0\201\240\0\0\0\1\0\0\3241\0\0\3241\0\0\0\0\21X \0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\227\252.\312\216\16\364\222\0\0\0\0\4\207`\25P\370\n[\0\0\0\0P\370\nZ\0\0\0\0P\370\nZP\366\241\37\0\0
recvmsg(32, {msg_name(0)=NULL, msg_iov(1)=[{"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 7996}], msg_con
V3 READ Reply (Call In 44940) Len:8192,44978,2013-01-17 14:29:55.168790,192.168.1.20,192.168.1.60,NFS,746    
recvmsg(32, 0x7fffe7016aa0, MSG_DONTWAIT|MSG_NOSIGNAL) = -1 EAGAIN (Resource temporarily unavailable)           
WAIT #140195143601328: nam='db file scattered read' ela= 4762 file#=4 block#=163 blocks=5 obj#=75338 tim=2013-01-17 14:29:55:169040
Once again process send to NFS READ calls (even if segement header wasn’t on NFS) and again only zeroed blocks has been send back. Whole operation is recorded as db file scattered read but actually process still has no data.
pread(257, "\6\242\0\0\243\0\0\1\250\34\17\0\0\0\1\4\202\0\0\0\1\0\0\0J&\1\0\247\34\17\0\0\0\0\0\2\0002\0\240\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\377\377\16\0\230\37\212\37\212\37\0\0\0\0@\0\6\0\0\0`\37\0\0"..., 40960, 1335296) = 40 
WAIT #140195143601328: nam='Disk file operations I/O' ela= 6883 FileOperation=9 fileno=4 filetype=2 obj#=75338 tim=2013-01-17 14:29:55:176020      
Again process is reading file 257 (backup data file) and it is recording that activity as "Disk file operations I/O". Based on that observation I can say that Oracle is looking for data first on NFS and then in backup file. 

But it there any caching / learning algorithm in place ? I don’t think so cause when I run this query select * from dba_source where line like 'marcin' twice with flushing buffer cache between runs and execution pattern looks similar – there was NFS activity fallowed by reading backup disk file. 

This is end of part one and I will try to publish next post about same topic soon.

regards,
Marcin 

Friday, April 27, 2012

HCC on non-Exadata - How Oracle is detecting storage type

This is next part of HCC compression series on non-Exadata. When 11.2.0.3 has been released Oracle announced that HCC compression will be possbile on ZFS Appliance and Axiom Pillar storage and patch 13041324 has been released as well. I have blogged about it and was able to run HCC on ZFS Appliance simulator and on default Linux NFS as well. After some time Oracle raised bug "Bug 13362079  HCC compression should not be allowed on dNFS with non ZFS or Pillar" and it has been fixed in PSU 11.2.0.3.1. (patch 13343438). After applying that PSU I was unable to create HCC compressed table anymore.

I was wondering how Oracle is checking storage type as far as I know NFS doesn't have that functionality. I have compared wireshark network dumps from old and new Oracle version and there was no difference. At that point I was thinking that maybe new firmware upgrade for ZFS Appliance is required but unfortunately I couldn't download it and apply to my simulator. After discussion on Twitter with @GregRahn @alexgorbachev @AlexFatkulin @kevinclosson I have been told that Oracle can use SNMP to check storage type. That's a new idea and I recalled that I have seen SNMP related error in trace files but I have ignored it as is appear for 11.2.0.3 with patch and for 11.2.0.3.1 as well.

This time I decided to dig it out. So this is a first error I have seen from DBWR trace:
test_dbw0_11626.trc: [1332587314469093] skgnfs_setup_snmp:250: dlopen errno = 0, errstr = libnetsnmp.so: cannot open shared object file: No such file or directory
So there is no shared library in system (server is running OEL 5.6) or some symlinks are missing. Let's try with new symlink
ln -s /usr/lib64/libnetsnmp.so.10 /usr/lib64/libnetsnmp.so
Let's check DBWR trace now - looks better library has been found but target host is not responding.
test_dbw0_3575.trc: [1334661892496086] skgnfs_query_snmp:1831: Timeout error 2 for server 10.10.10.60
I have connected to my ZFS Appliance simulator and I have enabled SNMP using network=0.0.0.0/0 as network filter. I have restarted Oracle and there was no SNMP errors in DBWR trace anymore. I have enabled wireshark again and this what have been captured.
12:44:14.276823 IP 10.10.10.51.20671 > 10.10.10.60.snmp:  GetRequest(33)  E:sun.2.225.1.4.2.0
12:44:14.289691 IP 10.10.10.60.snmp > 10.10.10.51.20671:  GetResponse(59)  E:sun.2.225.1.4.2.0=[|snmp]
Now we can use snmpwalk to check what ZFS Appliance simulator is responding to SNMP request.
[root@dg1 mibs]# snmpwalk -O n -v 1 -c public 10.10.10.60 .1 | grep 225
.1.3.6.1.4.1.42.2.225.1.4.1.0 = STRING: "sunstore"
.1.3.6.1.4.1.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage VirtualBox"
.1.3.6.1.4.1.42.2.225.1.4.3.0 = STRING: "2011.04.24.1.0,1-1.8"
.1.3.6.1.4.1.42.2.225.1.4.4.0 = Timeticks: (938601700) 108 days, 15:13:37.00
.1.3.6.1.4.1.42.2.225.1.4.5.0 = Timeticks: (938601700) 108 days, 15:13:37.00
.1.3.6.1.4.1.42.2.225.1.4.6.0 = Timeticks: (329800) 0:54:58.00
.1.3.6.1.4.1.42.2.225.1.4.7.0 = STRING: "f2513e14-f8c2-6d7e-fc29-bbd8078aad24"
.1.3.6.1.4.1.42.2.225.1.4.8.0 = STRING: "unknown"
.1.3.6.1.4.1.42.2.225.1.4.9.0 = STRING: "Oracle 000-0000"
.1.3.6.1.4.1.42.2.225.1.5.1.0 = STRING: "AKCS_UNCONFIGURED"
.1.3.6.1.4.1.42.2.225.1.6.1.2.1 = STRING: "zfspool/default/zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.3.1 = STRING: "zfspool"
.1.3.6.1.4.1.42.2.225.1.6.1.4.1 = STRING: "default"
.1.3.6.1.4.1.42.2.225.1.6.1.5.1 = STRING: "zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.6.1 = STRING: "/export/zfstest"
.1.3.6.1.4.1.42.2.225.1.6.1.7.1 = Counter32: 7
.1.3.6.1.4.1.42.2.225.1.6.1.8.1 = Counter32: 0
.1.3.6.1.4.1.42.2.225.1.6.1.9.1 = Counter32: 7
Oracle is looking for sun.2.225.1.4.2.0 and this id .1.3.6.1.4.1.42.2.225.1.4.2.0. is matching all numbers. It is returning "Sun ZFS Storage VirtualBox" value and this is a type of NFS server. I think that word "VirtualBox" in name is a non matching one. To confirm that I have googled for screen shots and I found this link. On page 11 I found a information from physical ZFS Appliance which I was looking for and name looks like "Sun ZFS Storage 7xxx".
I hope Oracle will include simulator name as proper name for HCC. In my opinion it can be used to HCC evaluation like dbms_compression package but in different scale. From businesses perspective no one will use simulator for production workload.

But is there any way to test it now ? ZFS Appliance type is not configurable during simulator installation process but there are two options:
- libnetsnm.so is a open source library so it can be amended to return proper value but this is a hard way
- Other possibility to realize that Sun ZFS Appliance simulator is just Solaris box. And there is a chance that as many of other parameters in UNIX type name is saved in text file and if you are able to access this file you can change it.After a few tries and was able to change it and now my simulator is returning following values. I did it only for education purpose and I'm not sure if I can share all steps I did.
At the end SNMP is returning different name
[root@dg1 ~]# snmpwalk -O n -v 1 -c public 10.10.10.60 .1 | grep 225
.1.3.6.1.4.1.42.2.225.1.4.1.0 = STRING: "sunstore"
.1.3.6.1.4.1.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage 7420"
Now it is time to try HCC in version 11.2.0.3.1 on new configured simulator ZFS and YES it is working again.

Disclaimer:
According to Oracle license Hybrid Column Compression can be run on ZFS Appliance and Axiom Pillar storage only. This post is for eduction purposes only to understand how DB is detecting storage type and how to enable it if you have proper hardware.


regards,
Marcin

Sunday, September 12, 2010

Oracle patchest 11.2.0.2

Hi, 

Some people tweet about new Oracle patch set 11.2.0.2. I decided to check it too but I can't find it on MOS any more. Not only me as Tim Hall  (@oraclebase) tweet about that situation too.
Anyway there is one important note on MOS - 1189783.1 - starting with patch set 11.2.0.2 new Oracle patch sets will be full installations of Oracle Software and patching out-of-place will be preferred one or even mandatory one in case of Grid Infrastructure.
I hope this note doesn't disappear from MOS like patch set and will be still valid after a few days.
Now I'm waiting for 11.2.0.2 to appear once again.

regards,
Marcin

Saturday, March 13, 2010

Oracle ACFS on CentOS

Hi,

This is short instruction how to install Oracle ACFS on CentOS. CentOS is free version of RedHat and it is binary compatible. It is not officially supported by Oracle but all solutions related for RedHat should work on CentOS too.
I have found Laurent blog post about it and I used it as a background of my investigation. When I have used Laurent's steps it was fine until I want to register ACFS in Oracle Cluster Registry. I couldn't do it as not all cluster types has been registered during Grid Infrastructure configuration process.
Here is a simple solution how to fix it for new installations (still working on solution for existing one).
  1. Perform GUI part of Grid Control installation
  2. Before executing root.sh on both nodes edit following file on both nodes : $ORACLE_HOME/lib/osds_acfslib.pm

    line 299:    
    else   {     
       # we don't know what this is     
       $supported = 1;     
       $vers = "EL5";   
    } 
    
  3. Execute a root.sh on both nodes
Now ACFS will be installed and configured and all necessary tools will be copied.


Monday, December 14, 2009

Oracle RAC One Node – part 2

In this post I want to share with you some scenarios about RAC One Node and client behaviour. In that part I’m using SQL*Plus from 10g so there is no FAN notification, next part will be with FAN enabled client. The main difference between using and not using FAN is that FAN client has a feedback from Oracle Cluster (formally Grid Infrastructure in 11R2 or Oracle ClusterWare in previous releases) and has information about current cluster status. Non FAN client has to wait for TCP/IP time-out for current session before switching to other node. More information about client failover and cluster configuration can be found here.

My server and client configuration has been presented in part 1 (entry from TNSNAMES.ORA) so this is only short recall:
  • Two nodes – RAC1 and RAC2 with SCAN configured
  • SQL*Plus 10g Client


I want to present 3 scenarios:


  1. One node in cluster is running

  2. Two nodes are running – current instance is crashing



  3. Fixing configuration





One node in cluster is running

Due to failure or maintenance only one node in cluster is running. RAC One node service is starting on that node and it is opened for all sessions. Irrespective of which node was current previously instance is open on node which is running now. Current status can be checked via raconestatus

[oracle@rac1 ~]$ raconestatus

RAC One Node databases on this cluster:


Database UP Fix Required Current Server Candidate Server Names
======== == ============ ============================== ========================================
testone Y N rac1 rac1 rac2


Available Free Servers:

As we can see there is no available server to move our instance on. If we start Omotion process we will hit error.


[oracle@rac1 ~]$ Omotion

RAC One Node databases on this cluster:

# Database Server Fix Required
=== ======== ============================== ============
[1] testone rac1 N

Enter number of the database to migrate [1]:

Specify maximum time in minutes for migration to complete (max 30) [30]: 2

Available Target Server(s) :
# Server Available
=== ================== =========
ERROR: Cannot find any available servers in this cluster.

Exiting...

[oracle@rac1 ~]$

In case of shutdown node RAC1 all session will be terminated as in single node configuration. Whatever server will be started after both nodes are down will become a current one.

Two nodes are running – current instance is crashing
Both servers are up – instance is running on RAC1 server. Current cluster configuration can be checked via crsctl command



[oracle@rac1 grid]$ $GRID_HOME/bin/crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.eons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.oc4j
1 OFFLINE OFFLINE
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.scan1.vip
1 ONLINE ONLINE rac1
ora.testone.db
1 ONLINE ONLINE rac1 Open
ora.testone.serviceone.svc
1 ONLINE ONLINE rac1
[oracle@rac2 grid]$


Session is connected to RAC1 server


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from test;

ID
----------
1
2
3
4
5
6

6 rows selected.

SQL> insert into test values (7);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values (8);

1 row created.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_2 rac1

SQL>

Active Query
Now first scenario when I’m repeating a select command in SQL*Plus and in that same time I’m turning off RAC1 server. In that case a active statement is cancelled and client is trying to reconnect at that time.


SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
09:55

SQL> select to_char(sysdate,'hh24:mi') from dual;
select to_char(sysdate,'hh24:mi') from dual
*
ERROR at line 1:
ORA-03135: connection lost contact


SQL> select to_char(sysdate,'hh24:mi') from dual;
ERROR:
ORA-03114: not connected to ORACLE

As you can see command has been cancelled and session has been terminated. There is no other instance up and running yet to there is no target to failover a session. In client session network trace file you can find following order of errors:

  • ORA -12547 – TNS Lost Contact

  • ORA- 12151 – Bad packages

  • ORA-03115 – connection lost contact


After that client is going to reconnect session to other nodes like in normal RAC configuration. Client is calling SCAN address to get another VIP address but cluster is still in that same state – a reconfiguration has been just started – and Oracle Cluster is returning a rac1-vip once again as it is only one VIP configured for database service.

Keep in mind that current, not committed transaction in that case will be rollback.


Idle session
When a session is in an idle state a failover will take place when a first SQL query will be executed.
See example

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
15:30


SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_2 rac1


SQL> select to_char(sysdate,'hh24:mi') from dual;

TO_CH
-----
15:38

SQL>
SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_1 rac2

SQL>

As you can see a connection has been migrated between servers without any error. This is because a before a last query a new instance was up and running. If we take a look into client network trace file we will see following errors
  • ORA- 12151
  • ORA- 3113
After that client is going to reconnect session to other nodes. Client is calling SCAN address to get another VIP address and cluster in returning a new VIP (rac2-vip) as a database service has been successfully migrated to other node.
Keep in mind that current, not committed transaction has to be rollback after session migration.


Fixing configuration

After unexpected crash it is possible that RAC One Node configuration need to be fixed.
I have figure out two possible scenarios:

  • After node crash and migration of instance and service the crashed node has been rebooted and there are two running instances

    [oracle@rac1 grid]$ raconestatus

    RAC One Node databases on this cluster:


    Database UP Fix Required Current Server Candidate Server Names
    ======== == ============ ============================== ========================================
    testone Y Y rac1 rac2 rac1 rac2


    Available Free Servers:

    [oracle@rac1 grid]$

    Fixing of configuration will close one of instance (on rebooted node) and will clean a configuration.

    [oracle@rac1 rac1]$ raconefix

    RAC One Node databases on this cluster:

    # Database Server Fix Required
    === ======== ============================== ============
    [1] testone rac1 Y

    Enter number of the database to fix [1]:

    If database is up, it will be checked and cleaned after a previous fail over.


  • Very similar situation like in previous point but only one instance is working and there is a “mess” in RAC service configuration. Oracle RAC One Node fix will clean up a configuration.


At the end I want to mention that I have hit some strange behavior related to migration of instance between servers and starting and stopping servers. I’m not sure if it a bug or not but after whole cluster restart instances have been migrated across.
First configuration was

  • RAC1 – instance name : testone_2
  • RAC2 – instance name : testone_1
Yes I know it was my inconsequence in naming so Oracle decided to fix it. No, not at the beginning but after a week of testing and restarts. And now it looks like:

  • RAC1 – instance name : testone_1
  • RAC2 – instance name : testone_2

Unfortunately after that I was unable to play with RAC One Node and I have to clear a whole configuration and create it from scratch.

Next part will be about FAN enabled clients and more about transactions.

Monday, December 7, 2009

Oracle RAC One Node – part 1

This post is about a installation and configuration of Oracle RAC 11gR2 in One Node configuration on VMWare. At the beginning I want describe a RAC One Node a little bit more – this is a new possible configuration with special licence and price which is very similar to fail over cluster configuration. During a normal work only one instance and up and running – like in failover cluster, a difference is in migration process. The following steps are performed during a migration:
  1. Second instance in started on target node
  2. All session are migrated to target instance – TAF has to be enabled on client configuration
  3. Source instance in shutdown in transaction mode
  4. After timeout a source instance in shutdown in abort mode
Installation and configuration in based on Linux CentOS 5.3 and Oracle 11g 11.2.0.1

If you want to skip Grid Infrastructure configuration tips click here

Oracle Grid Infrastructure
Oracle Grid Infrastructure has to be installed and configured on all nodes belongs to Oracle RAC (this is requirement for both RAC configuration – typical RAC and One Node RAC). In previous releases Oracle Grid Infrastructure was called an Oracle ClusterWare. In 11g R2 a name has been change to Grid Infrastructure and a lot of changes have been made. The most important are:
  • Oracle ASM is a part of Grid Infrastructure and not a part of Oracle Database
  • Vote and cluster configuration can use ASM disk or cluster file system
  • Raw or block devices for vote and cluster configuration are not supported during a installation time
  • More RAM is required ?

Last change made me sad as I have only 4 GB RAM on my laptop so I can allocate about 3 GB for two VM. But why do not try. After a few tests I have found a working configuration for both nodes.

RAC1 – node number 1
  • 1.5 GB of RAM allocated for VM
  • 2.0 GB of swap
  • 1 CPU
  • 10 GB of free space for Oracle Homes (both infrastructure and database)

RAC2 – node number 2
  • 1.0 GB of RAM allocated for VM
  • 2.0 GB of swap
  • 1 CPU
  • 10 GB of free space for Oracle Homes (both infrastructure and database)

Yes there is a difference between memory sizes in nodes – start OUI on node number 1 with more allocated memory.

Common configuration:

/etc/hosts
10.10.20.129    rac1.localdomain rac1
192.168.226.10 rac1-priv.localdomain rac1-priv
10.10.20.130 rac2.localdomain rac2
192.168.226.20 rac2-priv.localdomain rac2-priv
10.10.20.200 rac-cluster
10.10.20.210 rac1-vip.localdomain rac1-vip
10.10.20.220 rac2-vip.localdomain rac2-vip
RAC1-VIP and RAC2-VIP has to be assigned to public network and not configured during an installation. There is an additional entry for rac-cluster which is SCAN interface and has to be in same network as both VIP interfaces and not configured during a configuration time.
SCAN interface is a single entry point for cluster – for more information you can see RAC documentation.

Now is time to start OUI and install a Grid Control infrastructure. Below some installation tips:
  • Installation option – choose Install and configure Grid Infrastructure for Cluster
  • Installation type – Choose Typical installation
  • SCAN name – type rac-cluster
  • Add both nodes – RAC1 and RAC2 with proper VIP – note there is no need to private name anymore
  • Test SSH connectivity between nodes and click Setup there are any problems
  • Choose 10.10.20.x subnet as public and 192.168 as private
  • Specify a Oracle Base and Software location (which is a Oracle Home for Grid Infrastructure) - note that Oracle Home for Grid has to be in different location that Oracle Base
  • Select a disk for ASM group – it will be used for vote and configuration file too – if required change a Discovery Path to correct value (ex. /dev/sdc* for block devices or ORCL:* if you are using ASMLib)
  • If there are any problem with kernel setting or missing packages solve it before installation start – you can ignore memory, swap and NTP – but you have to have at least memory size specified above.
After about 15 minutes there is a time for a last step – execute a root.sh to configure and start cluster infrastructure. Run root.sh on node with more memory first.


Oracle Database Oracle Home

Just perform a standard installation of Oracle 11gR2 binaries without database creation.

RAC One Node patch

This is a best time to install a patch which adds One Node support to our database Oracle Home. Why ? Because it has to be installed when DB is down, so before creation of database we don’t need any other actions. Patch number is RACONENODE_p9004119_112010_LINUX.zip and can be found on Oracle Support Pages.

Database creation

The most important thing is to create a database only on one node. On the first screen a RAC database has to be chosen and then only one node (ex. rac1) has to be selected.
The next important thing is storage for a database. In our example all database files will be placed in ASM disk group. This same used to keep Grid Infrastructure cluster configuration.
All other configuration settings don’t have any impact on RAC One Node configuration.

Service configuration

A new service has to be added to support a RAC One Node configuration. This service will be used in our client configuration and will be entry point to our database.

srvctl add service -d testone -s serviceone -r testone1
where
  • testone – is a database name
  • serviceone – is a service name
  • testone1 – is a instance name created in previous point

RAC One node configuration

When database and service are up there is a time to start a RAC One Node configuration.
To do that a raconeinit has to be started.


Candidate Databases on this cluster:
# Database RAC One Node Fix Required
=== ======== ============ ============
[1] testone NO N/A
Enter the database to initialize [1]:
Database testone is now running on server rac1
Candidate servers that may be used for this DB: rac2

Enter the names of additional candidate servers where this DB may run (space delimited): rac2

Please wait, this may take a few minutes to finish.
Database configuration modified.

After that command a new configuration should be in place. Current status can be checked with following command:raconestatus


RAC One Node databases on this cluster:

Database UP Fix Required Current Server Candidate Server Names
======== == ============ ============================== ========================================
testone Y N rac1 rac1 rac2

Available Free Servers:

RAC One node operations

Main RAC One Node operation is moving an instance between nodes. That operation can be done using Omotion tool. Here is a example of Omotion execution

RAC One Node databases on this cluster:

# Database Server Fix Required
=== ======== ============================== ============
[1] testone rac1 N
Enter number of the database to migrate [1]:
Specify maximum time in minutes for migration to complete (max 30) [30]: 5
Available Target Server(s) :
# Server Available
=== ================== =========
[1] rac2 Y
Enter number of the target node [1]:

Omotion Started...
Starting target instance on rac2...
Migrating sessions...
Stopping source instance on rac1...
Omotion Completed...

=== Current Status ===
Database testone is running on node rac2

In that example database instance has been moved from node rac1 to node rac2. Instance on rac1 has been closed in transaction mode. In that scenario there was no remaining session on rac1 instance. When there are outstanding sessions/transactions on source node (in that case rac1) Omotion will shutdown that instance in transaction mode and then after time out will shutdown in abort mode – see example below.

RAC One Node databases on this cluster:

# Database Server Fix Required
=== ======== ============================== ============
[1] testone rac1 N

Enter number of the database to migrate [1]:
Specify maximum time in minutes for migration to complete (max 30) [30]: 5
Available Target Server(s) :
# Server Available
=== ================== =========
[1] rac2 Y

Enter number of the target node [1]:
Omotion Started...
Starting target instance on rac2...
Migrating sessions...
Stopping source instance on rac1...
Timeout exceeded, aborting instance...
Omotion Completed...

=== Current Status ===
Database testone is running on node rac2
Client configuration
How is look like from client perspective?
  1. TAF not configured – session has to be reconnected after instance migration
  2. TAF configured in client TNS – only current transaction has to be rollback.
TAF example:
tnsnames.ora

testone =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testone)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))
)
)

Take a look on address - there is no VIP any more - now SCAN name has to be entered
in TNS alias and resolved via DNA or hosts as well as all RAC VIP (rac1-vip and rac2-vip).
SQL*Plus test


sqlplus system@testone
SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_1 rac2


SQL> select * from test;

ID
----------
1
2
3
4
5
6
7

7 rows selected.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_1 rac2


Omotion has been started.

SQL> select instance_name, host_name from v$instance;
select instance_name, host_name from v$instance
*
ERROR at line 1:
ORA-25402: transaction must roll back


SQL> rollback;

Rollback complete.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testone_2 rac1


SQL> select * from test;

ID
----------
1
2
3
4
5
6

6 rows selected.

SQL>

After migration there was two changes - instance name and host name has been changed - it's not like in typical failover clustre
where instance is migrated from one host to other. In RAC One Node a new instance

is started on second node and during a migration time this configuration is working
as typical RAC.

This is end of part one - next part with more test and operations soon.