Using full transportable export/import, we can upgrade or migrate the source Oracle Database 11g Release 2 (11.2.0.3) or higher to Oracle Database 12c pluggable database in a single operation. Tags: Oracle. Written by Anju Garg. Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Oracle Database 10g Migration to Automatic Storage Management EXECUTIVE OVERVIEW In Oracle Database 10g, storage management and provisioning for the database has becomes much more simplified with a new feature called Automatic Storage Management (ASM). ASM provides filesystem and volume manager capabilities built into the Oracle database kernel.
You could see it go slower, you could see it go faster.Lets say you are using a unix buffered file system. Your system does more physical IO then it should due to poorly tuned applications. You want to make things go faster. You've heard that 'raw is faster' so you move your files to raw. The next day it is REALLY REALLY slow. What happened?Well, your system was double buffered.
There was the Oracle cache, and the OS file system cache. Maybe 90% of your 'physical io' when using the cooked (non-raw) file system was satisfied via the unix buffer cache. So, only 10% of your physical IO really went to physical disk.
When you moved it to raw - you took that cache away - now 100% of your physical IO is TRUE physical IO, really goes to disk. You just killed performance (because you tried a quick fix to fix your problem instead of fixing the problem - the way to fix IO related issues is generally to DECREASE your IO;)Conversely, you are using a unix buffered file system.
Your system does more physical IO then it should due to poorly tuned applications. You want to make things go faster.
You've heard that 'raw is faster' so you move your files to raw. The next day it is marginally.faster. What happened?Well, you apparently didn't make much use of the unix buffer cache, perhaps your SGA was so large and the PGA's just crowded the unix buffer cache out so 90% of your physical IO was true physical IO. You are seeing the nominal increase in performance of RAW disk IO over cooked.Conversely, you are using a unix buffered file system. Your system does more physical IO then it should due to poorly tuned applications. You want to make things go faster. You've heard that 'raw is faster' so you move your files to raw.
The next day it is really really fast. What happened?Perhaps you removed an IO bottleneck.In short - what you need to do iso determine cause of poor performanceo develop a custom plan to remove that causeJust moving files - well, maybe you get lucky once out of one hundred times but more typically it makes it worse, makes is marginally better or does nothing whatsoever.and you rated our response. FollowupFebruary 20, 2003 - 7:05 pm UTCyou missed my point. Here the effect of double buffering is HIDING the terrible effect of too much physical IO. The OS we buffering the data that Oracle believed was a PIO.So you look at your system and see 'oh my, 100,000 pios an hour.
That is terrible. I've heard RAW is faster at reads - let's switch'. Bummer - you really only did 10,000 PIO's an hour cause 90k of them were buffered by the OS for you - now you are REALLY doing 100k PIO'sSo, beware - DIAGNOSE YOUR PROBLEM first and then apply logical corrections.I always say, if you watch the LIO's the PIO's take care of themselves. Reduce the amount of LIO's and you'll most likely reduce the PIO's and then you don't need to remove the double buffering (which mostly affects WRITES, not READS anyway so if you are READ bound not WRITE bound - kaio isn't going to do you very much good)My point is - beware the silver bullet syndrome. There aren't any.
Whilst I agree with Tom on not converting to (or from) raw just for the sake of doing so, I am an ardent fan when putting a system together to always go for raw. The reasons being:a) a PIO as reported as Oracle is very likely to be a real PIO, so you get a true picture of whats going onb) the buffering is controlled by Oracle so as a DBA I have more control over it, and Oracle should yield smarter buffering, since it knows thing like extent and segment boundariesc) you typically get some benefits from the OS layer (kernelised aio etc)d) you do.not. get autoextend. Yep, I see this as a benefit because it forces you to do some decent sizing and planning up front (sadly lacking in many databases)The historical arguments against raw were always management, admin etc, but nowadays, every OS as a simple gui or equivalent to resize them, move them, create/drop them as if they were file systems anyway. FollowupFebruary 21, 2003 - 7:38 am UTCAll valid and correct points.I do see tons of people 'afraid of raw' - like it is 'harder'. Perhaps that is because the DBA needs SA help to set things up (needs root everynow and again) and they feel a loss of control. Or, they really don't understand what RAW means.
A major 'fear' for OPS/RAC adopters has always been 'oh, i have to use raw'. We had to create a cluster file system just to work around that!To me, /dev/.
Looks no different then /d01/oradata, they are just files after all. I have a story about using the raw device:We have an application running very slow. We don't know the reason. Our application team cannot find any clue in solving this problem. The application involves many components such as appliction server, Powerbuilder, 9i Database. Our DBA find that the disk IO of the redo log is very busy. So he said, 'maybe we move the redo log files to the raw device and see.'
Later, he moves the redo log files to the raw device, and the performance seem improve (but still not acceptable). And then he said, 'ok, the performance is improve now, maybe we move the whole database to the raw device and see.' What is your comments about this DBA? Hello,I am using RAW on 10g 10.1.0.2 with ASM for almost 1 month and the performance is very good but my DB hangs once or twice in a week, I dont know where the problem was Oracle and HP both involved in this problem many things done but the problem remain same, the last solution for me to do is remove ASM and install custom database on file system. Today is the first day after new installation i hope the system will never hang again but i worried with the performance issue, my application is well tunedEnvironment isOracle DB 10g 10.1.0.2.0HP-UX 11i 11.23 on Itaniumusing SANIf anybody face the same problem let me know the action please.Syed. FollowupSeptember 06, 2005 - 4:29 pm UTCa short excerpt from my forthcoming book:A Brief Review of File System MechanismsThere are four file system mechanisms in which to store your data in Oracle.
By your data, I mean your data dictionary, redo, undo, tables, indexes, LOBs, and so on-the data you personally care about at the end of the day. Briefly, they are. 'Cooked' operating system (OS) file systems: These are files that appear in the file system just like your word processing documents do. You can see them in Windows Explorer; you can see them in UNIX as the result of an ls command.
You can use simple OS utilities such as xcopy on Windows or cp on UNIX to move them around. Cooked OS files are historically the 'most popular' method for storing data in Oracle, but I personally expect to see that change with the introduction of ASM (more on that in a moment). Cooked file systems are typically buffered as well, meaning that the OS will cache information for you as you read and, in some cases, write to disk. Raw partitions: These are not files-these are raw disks. You do not ls them; you do not review their contents in Windows Explorer. They are just big sections of disk without any sort of file system on them. The entire raw partition appears to Oracle as a single large file.
This is in contrast to a cooked file system, where you might have many dozens or even hundreds of database data files; a raw partition will appear to Oracle to be a single large data file. Currently, only a small percentage of Oracle installations use raw partitions due to their perceived administrative overhead.
Raw partitions are not buffered devices-all I/O performed on them is a direct I/O, without any OS buffering of data (which, for a database, is generally a positive attribute). Automatic Storage Management (ASM): This is a new feature of Oracle 10g Release 1 (for both Standard and Enterprise editions). ASM is a file system designed exclusively for use by the database. An easy way to think about it is as a database file system.
You won't store your shopping list in a text file on this file system-you'll store only database-related information here: your tables, indexes, backups, control files, parameter files, redo logs, archives, and more. But even in ASM, the equivalent of a data file exists; conceptually, data is still stored in files, but the file system is ASM.
ASM is designed to work in either a single machine or clustered environment. Clustered file system: This is specifically for a RAC (clustered) environment and provides for the appearance of a cooked file system that is shared by many nodes (computers) in a clustered environment.
A traditional cooked file system is usable by only one computer is a clustered environment. So, while it is true that you could NFS mount or Samba share (a method of sharing disks in a Windows/UNIX environment similar to NFS) a cooked file system among many nodes in a cluster, it represents a single point of failure. In the event that the node owning the file system and performing the sharing was to fail, then that file system would be unavailable. The Oracle Cluster File System (OCFS) is Oracle's offering in this area and is currently available for Windows and Linux only. Other third-party vendors do provide certified clustered file systems that work with Oracle as well. The clustered file system brings the comfort of a cooked file system to a clustered environment.The interesting thing is that a database might consist of files from any and all of the preceding file systems-you don't need to pick just one.
You could have a database whereby portions of the data were stored in conventional cooked file systems, some on raw partitions, others in ASM, and yet other components in a clustered file system. This makes it rather easy to move from technology to technology, or to just get your feet wet in a new file system type without moving the entire database into it. Now, since a full discussion of file systems and all of their detailed attributes is beyond the scope of this particular book, we'll dive back into the Oracle file types.
Regardless of whether the file is stored on cooked file systems, in raw partitions, within ASM, or on a clustered file system, the following concepts always apply. That is what the challenge is -to make the product that is worth use.and the one that will sell.Its failure provided opportunity to improve and then bounce back.and the cycle continues. But whatis baffling that the approach was simply abandoned.IF the approach chosen had the flaw then there is always scope for the improvement, or a change of course is also possible.Might be it does not make business sense, but an opportunity with lot of scope of innovation, enhancement, and improvement was lost. So people (.users and one working with oracle.) are satisfied with current OS flavors.or they feel that certain features are still missing which could have led to more efficient and effective performance of oracle database. Do they yearn for 'Dream OS for Oracle' to get max out from the resources in place?to put it in the other way.Sometimes it happens that things are found wanting from the end of software (.
In this case ORACLE db server. ) that runs on OS and sometimes it is the otherway round.So what is the current scenario? Tom,I'm experiencing slower I/O performance on raw devices when compared to cooked files.Here are some numbers.On cooked files:1 select a.file#, b.filename, a.singleblkrds, a.singleblkrdtim, a.singleblkrdtim/a.singleblkrds averagewait2 from v$filestat a, dbadatafiles b3 where a.file# = b.fileid4 and a.singleblkrds 05. order by averagewaitFILE# FILENAME SINGLEBLKRDS SINGLEBLKRDTIM AVERAGEWAIT-2 /var/opt/apps/oracle/HKR/hkrs/data/undotbs01.dbf 387. /var/opt/apps/oracle/HKR/hkrs/data/FESMLIDX0101.DBF 1. /var/opt/apps/oracle/HKR/hkrs/data/HRMSLRGDAT0101.DBF 1383480.3 /var/opt/apps/oracle/HKR/hkrs/data/FELRGIDX0101.DBF.
/var/opt/apps/oracle/dflinks/hkrp/rdf 2. /var/opt/apps/oracle/dflinks/hkrp/rdf 2. /var/opt/apps/oracle/dflinks/hkrp/rdf1 2.976249242 /var/opt/apps/oracle/dflinks/hkrp/rdf4 191511 2.989836724 /var/opt/apps/oracle/dflinks/hkrp/rdf4 3. /var/opt/apps/oracle/dflinks/hkrp/rdf41 471125 3. /var/opt/apps/oracle/dflinks/hkrp/rdf500m08 1319 412019Any clues as to what could be causing the big average wait difference?Thanks.
This may be slightly off-topic.Would you say, 'raw-iron' (database appliance) to Teradata is a correct comparison? (not that I am going to run the test)Just asking since I have heard 'Teradata' comes with its own box and in-built OS and thats why it conquered DW market.Its still kinda funny for me to think ' Database not performing fine.Lets create a TAR and get the yellow cable inside replaced';-) For this reason, I would really like to see how Teradata DBAs work and diagnose the problems. Hi Tom,Sincerely apologies if I have hurted / offended you. I heard that / believe that in the US and the Europe, people loved to be heared with the short names.Sir, it would be all pleasure of mine if you can call me either SN or Star Nirav. I dont have any problem as far as I am clearing my doubts here and making my concepts clear.Even i hope that I am not offending you by calling TK (because I believe TK means Technically King)-As you asked. How would I decrease i/oSir, If my one raw volume is having 2 tablespaces with 500 objects and simultaneously needs to be accessed then I would go for moving my some objects to another raw partitions. Correct?If it is correct then pls tell me how would i move from one to another raw partitions, as I heard that only RMAN can do this thing.
Normal CP command (AIX) will not copy the files from raw device. Not sure about dd and cpio so please describe the other workarounds.ThanksStar NiravPS: However we are not in the one organisation but I strongly believed you as my mentor. FollowupOctober 20, 2006 - 5:54 pm UTCWhy would you call 'u' Tom?
Would not the person named 'u' get upset by that?If you move stuff you read from DISK A to DISK B, how will that reduce the number of times Oracle must perform physical IO?Think about this - if you did 100 IO's against the file on the raw partition 'A', and you move the data from raw partition A to raw partition B, you will do.100 IO's against B.Now, unless 'A' is a logical disk and the raw partition was just a small slice of it - and there was much CONTENTION on the logical disk that underlies 'A' and by moving to 'B' you can reduce contention - you might DECREASE IO TIMES, but you won't. Decrease IO.Many things can move raw partitoins, including the unix command dd. However before doing so, you will get someone qualified (eg: has experience with this stuff) to do it WITH YOU. FollowupOctober 23, 2006 - 5:32 pm UTCif you were doing 500 physical IO's yesterday on disks A and Band you move data from A to CI would hazzard a guess that you will be doing 500 physical IO's tomorrow.think about it, how can MOVING data from 'A' to 'C' reduce the number of times Oracle must READ that data????????
It will just have to read it from somewhere else!so, maybe you are confusing some terms or something, but you will not convince me that by moving data from A to C that you will reduce IO's - move them perhaps, but not reduce them. Star Nirav (from india) wrote: So if i move some tables and indexes to another tablespace, will i get the benefit or not???Benefit of what?I/O is the result of a need for data. For example, the need for data when UserA says 'I want all rows from EMP with surname SMITH'.Now you move the table containing that data from volume 1 to volume 10 (be that a cooked or raw volume).Have you now stopped UserA from wanting that data?No, UserA still wants that data - unless you have hit UserA over the head with the trusty old lead pipe in the meantime.And this is what Tom has been trying to tell you, seemingly in vain, all along - MOVING data does NOT reduce I/O.Hitting users with a lead pipe, however can reduce I/O. (but then you need to be a card carrying member of the Scorched Earth Party for this to be legal)Moving data simple spreads I/O.
It moves I/O from one disk, raw device, file system, volume, slice, whatever, to another.Moving data therefore does not reduce I/O, it speads I/O.Yes, spreading I/O can alleviate I/O bottlenecks caused by an I/O channel/disk/device being overloaded due to processes asking for more I/O per second that what it can service.In which case something like ASM is an excellent answer as this perform automatic I/O load balancing for you with the database instance up and running.
ALTER DATABASE DROP LOGFILEMEMBER '/u01/data/learndb/redo01.log';ALTER SYSTEM SWITCH LOGFILE;ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';ALTER SYSTEM SWITCH LOGFILE;ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';4) You can check the logfiles created now in ASMGROUP#MEMBERSTATUS- -1+DATA/learndb/onlinelog/group1.293 CURRENT2+DATA/learndb/onlinelog/group2.205 INACTIVE3+DATA/learndb/onlinelog/group3.217 INACTIVENow your database is migrated to ASM.