marrowyung
asked on
how to use export/import for Oracle migration
Dear all,
Right now will do Oracle migration from 10gR2 and 11gR2 using export/import, any step by steps guide on how to execute/implement this ?
Right now will do Oracle migration from 10gR2 and 11gR2 using export/import, any step by steps guide on how to execute/implement this ?
In like manner, one should remember to search the E-E knowledge base for past answers -- saves you points.
ASKER
johnsone,
you mean the section : Moving Data Using Oracle Data Pump ?
as our company decided to use exp/imp to do this, so I better focus on this.
by the way, RMAN can't restore 10gR2 and 11gR2 content to 12c for migration purpose, right?
you mean the section : Moving Data Using Oracle Data Pump ?
as our company decided to use exp/imp to do this, so I better focus on this.
by the way, RMAN can't restore 10gR2 and 11gR2 content to 12c for migration purpose, right?
That section contains instructions for both export/import and expdp/impdp. As you are moving from 10g to 11g, I believe that the documentation is going to recommend expdp/impdp (data pump). They are the newer exp/imp utilities. Different name, same results.
ASKER
" As you are moving from 10g to 11g, I"
no, 11gR2/10gR2 to 12c
but exp/imp need a lot of manual works, which is diff from DBUA, right?
no, 11gR2/10gR2 to 12c
but exp/imp need a lot of manual works, which is diff from DBUA, right?
"but exp/imp ... is diff from DBUA, right?"
Correct. These are different processes. The DBUA process does not use exp/imp at all.
Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime.
The exp/imp approach will require more manual work, and will usually require more downtime. But, this approach can result in a database with significantly better response times than if you use the DBUA approach. This depends on your application and how it has used the database. If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now. Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows. The exp/imp approach will clean up both of those problems. The DBUA approach will not.
Correct. These are different processes. The DBUA process does not use exp/imp at all.
Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime.
The exp/imp approach will require more manual work, and will usually require more downtime. But, this approach can result in a database with significantly better response times than if you use the DBUA approach. This depends on your application and how it has used the database. If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now. Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows. The exp/imp approach will clean up both of those problems. The DBUA approach will not.
For how to use the exp/imp approach:
1. Create the new database using DBCA.
2. Manually create any non-default tablespaces that you need.
3. Manually create the user accounts (schemas) that you plan to import data for.
4. Manually create any other user accounts that you need.
5. Run the export(s) from the old system, and copy the *.dmp files to the new system.
6. In the new system, run an import for each *.dmp file.
1. Create the new database using DBCA.
2. Manually create any non-default tablespaces that you need.
3. Manually create the user accounts (schemas) that you plan to import data for.
4. Manually create any other user accounts that you need.
5. Run the export(s) from the old system, and copy the *.dmp files to the new system.
6. In the new system, run an import for each *.dmp file.
ASKER
markgeer,
"Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime."
yes, this is what i heard before! but the DB going upgrade need to have the version support by DBUA.
" But, this approach can result in a database with significantly better response times than if you use the DBUA approach"
I don't get it, why longest time but singificantly better response time? you mean during this time both old and new DB up and running at the same time but just new DB has very less data at the beginning, but getting more and more DURING exp/imp process, new DB is already serving from the beginning ?
""exp/imp approach will clean up both of those problems. "
just because exp/imp do everythign once instead of keep deleting and updating ?
ok ,which one give less error once the migration is done when no other manual effect is needed?
"Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime."
yes, this is what i heard before! but the DB going upgrade need to have the version support by DBUA.
" But, this approach can result in a database with significantly better response times than if you use the DBUA approach"
I don't get it, why longest time but singificantly better response time? you mean during this time both old and new DB up and running at the same time but just new DB has very less data at the beginning, but getting more and more DURING exp/imp process, new DB is already serving from the beginning ?
""exp/imp approach will clean up both of those problems. "
just because exp/imp do everythign once instead of keep deleting and updating ?
ok ,which one give less error once the migration is done when no other manual effect is needed?
ASKER
any imcremental exp/imp for the data export? or we need to exp ALL data again if we see data in test database is out date ?
ASKER
I am reading this :
http://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD12565
"8.Optionally, you can change the storage parameters from the source database.
You can pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. When items have been pre-created, specify one of the following options:
•TABLE_EXISTS_ACTION=APPEN
•IGNORE=Y for original Import
"
where do I need to input those parameter?
http://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD12565
"8.Optionally, you can change the storage parameters from the source database.
You can pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. When items have been pre-created, specify one of the following options:
•TABLE_EXISTS_ACTION=APPEN
•IGNORE=Y for original Import
"
where do I need to input those parameter?
This seems to be turning into a repeat of https://www.experts-exchange.com/questions/28581597/upgrade-10g-R2-and-11g-R2-to-12c.html
Again, the documentation that I posted will have the steps required for exp/imp or expdp/impdp if that is the method that you choose.
In terms of RMAN restore, you can restore the original instance onto a system with 12 and then use dbua to do the upgrade. If you are changing system architecture or OS, there may be additional steps to convert the data files, but these are outlined in the documentation.
Again, the documentation that I posted will have the steps required for exp/imp or expdp/impdp if that is the method that you choose.
In terms of RMAN restore, you can restore the original instance onto a system with 12 and then use dbua to do the upgrade. If you are changing system architecture or OS, there may be additional steps to convert the data files, but these are outlined in the documentation.
"But, this [exp/imp] approach can result in a database with significantly better response times than if you use the DBUA approach"
"I don't get it, why longest time but singificantly better response time?"
The longest time refers to the amount of application down time that will be required for the actual data move if you use the exp/imp approach. The better response time refers to the performance of the upgraded system *AFTER* the exp/imp or DBUA process is complete. This better response time (every day) may help you for years. The DBUA approach is usually easier and faster (for the upgrade process itself), but it will not give you the long-term performance gains that the exp/imp approach can give you. And, as you are aware, the DBUA approach is limited to just certain DB versions.
Of course, to get faster response times, you can simply buy more or bigger database servers and pay Oracle more license fees every year. But, if your application has caused lots of chained rows or lots of fragmented free space in tables, you may be able to get a large performance gain by using the exp/imp approach. This depends on your database and your application though! Maybe the exp/imp approach won't give you much of a performance improvement at all. You will have to analyze your tables and examine the results to see if you have either of these problems now. And, depending on the size of your database, maybe the exp/imp approach is not realistic for you. If your total DB size is less than 200Gb, I think the exp/imp approach should work very well. If your total DB size is over 5TB, you should try to use DBUA. If your DB size is somewhere between those numbers, then you have to decide what will be best for you.
"I don't get it, why longest time but singificantly better response time?"
The longest time refers to the amount of application down time that will be required for the actual data move if you use the exp/imp approach. The better response time refers to the performance of the upgraded system *AFTER* the exp/imp or DBUA process is complete. This better response time (every day) may help you for years. The DBUA approach is usually easier and faster (for the upgrade process itself), but it will not give you the long-term performance gains that the exp/imp approach can give you. And, as you are aware, the DBUA approach is limited to just certain DB versions.
Of course, to get faster response times, you can simply buy more or bigger database servers and pay Oracle more license fees every year. But, if your application has caused lots of chained rows or lots of fragmented free space in tables, you may be able to get a large performance gain by using the exp/imp approach. This depends on your database and your application though! Maybe the exp/imp approach won't give you much of a performance improvement at all. You will have to analyze your tables and examine the results to see if you have either of these problems now. And, depending on the size of your database, maybe the exp/imp approach is not realistic for you. If your total DB size is less than 200Gb, I think the exp/imp approach should work very well. If your total DB size is over 5TB, you should try to use DBUA. If your DB size is somewhere between those numbers, then you have to decide what will be best for you.
ASKER
johnsone,
"In terms of RMAN restore, you can restore the original instance onto a system with 12 and then use dbua to do the upgrade. "
the RMAN restore only restore data but not oracle system files, right? we are thinking if RMAN works, then why need exp/imp for that, right?
can't understandy why a system with 12 installed already can have the original instance restore to it? or you mean restore the whole oracle 10g/11g instance binary file and data to that new server, and then upgrade that instance to 12c without DBUA OR exp/imp again?
markgeer,
back to the last comment you post.
" If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now. Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows. The exp/imp approach will clean up both of those problems. The DBUA approach will not. "
why then has a lot of chain rows? just because the inserted but deleted also record also goes to the 12c once upgraded?
can't see why exp/imp can't handle this then, it just expert all records and function, right?
yesterday I try on the test server it takes a lot of time for only 2 MB export files !
"but it will not give you the long-term performance gains that the exp/imp approach can give you. And, as you are aware, the DBUA approach is limited to just certain DB versions.
"
you mean DBUA only applicatabile to the version that is in the direct upgrade path list ?
if long -term perfomrance gains by exp/imp is bigger than DBUA , I cna't see why people keep using DBUA more and less on exp/imp.
any why better performance then ? just because "lots of chained rows or lots of fragmented free space in tables" ?
any doc. to compare DBUA and exp/imp on this issue ? I just don't understand why it takes care the chain rows.
"If your total DB size is less than 200Gb, I think the exp/imp approach should work very well. If your total DB size is over 5TB, you should try to use DBUA. If your DB size is somewhere between those numbers, then you have to decide what will be best for you. "
ok, for large size, DBUA ..
"In terms of RMAN restore, you can restore the original instance onto a system with 12 and then use dbua to do the upgrade. "
the RMAN restore only restore data but not oracle system files, right? we are thinking if RMAN works, then why need exp/imp for that, right?
can't understandy why a system with 12 installed already can have the original instance restore to it? or you mean restore the whole oracle 10g/11g instance binary file and data to that new server, and then upgrade that instance to 12c without DBUA OR exp/imp again?
markgeer,
back to the last comment you post.
" If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now. Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows. The exp/imp approach will clean up both of those problems. The DBUA approach will not. "
why then has a lot of chain rows? just because the inserted but deleted also record also goes to the 12c once upgraded?
can't see why exp/imp can't handle this then, it just expert all records and function, right?
yesterday I try on the test server it takes a lot of time for only 2 MB export files !
"but it will not give you the long-term performance gains that the exp/imp approach can give you. And, as you are aware, the DBUA approach is limited to just certain DB versions.
"
you mean DBUA only applicatabile to the version that is in the direct upgrade path list ?
if long -term perfomrance gains by exp/imp is bigger than DBUA , I cna't see why people keep using DBUA more and less on exp/imp.
any why better performance then ? just because "lots of chained rows or lots of fragmented free space in tables" ?
any doc. to compare DBUA and exp/imp on this issue ? I just don't understand why it takes care the chain rows.
"If your total DB size is less than 200Gb, I think the exp/imp approach should work very well. If your total DB size is over 5TB, you should try to use DBUA. If your DB size is somewhere between those numbers, then you have to decide what will be best for you. "
ok, for large size, DBUA ..
RMAN should be backing up everything except the software binaries. You don't need the 10 or 11 software to do the upgrade, on the 12 software. Again, you need to look at the RMAN documentation to be sure additional steps are not required due to differences in machine architecture and/or OS.
This would still be doing the upgrade with dbua. Which it seems like you have already decided you aren't doing.
exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows. This is because you are starting with an empty table and inserting all the rows again. dbua is just upgrading the system catalog and leaving the data files untouched.
Chained rows cannot be fixed by exp/imp or expdp/impdp. By definition those are rows that are larger than a database block and there is no way that they can be changed. What we are talking about are migrated rows (unfortunately called chained rows in a lot of locations). A migrated row is a row that was updated and no longer fits in the block that it currently resides. In this case the row is moved to another block and a pointer is left in its original location to the new location. If you have a large number of these, it can affect performance as two I/O operations are required to retrieve them. Since exp/imp is inserting the rows into an empty table there will no longer be any migrated rows as no updates are occurring.
Again, I am going to suggest that you spend some time with the Concepts manual. You really cannot learn how to be a DBA from a question and answer site.
This would still be doing the upgrade with dbua. Which it seems like you have already decided you aren't doing.
exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows. This is because you are starting with an empty table and inserting all the rows again. dbua is just upgrading the system catalog and leaving the data files untouched.
Chained rows cannot be fixed by exp/imp or expdp/impdp. By definition those are rows that are larger than a database block and there is no way that they can be changed. What we are talking about are migrated rows (unfortunately called chained rows in a lot of locations). A migrated row is a row that was updated and no longer fits in the block that it currently resides. In this case the row is moved to another block and a pointer is left in its original location to the new location. If you have a large number of these, it can affect performance as two I/O operations are required to retrieve them. Since exp/imp is inserting the rows into an empty table there will no longer be any migrated rows as no updates are occurring.
Again, I am going to suggest that you spend some time with the Concepts manual. You really cannot learn how to be a DBA from a question and answer site.
"If long-term performance gains by exp/imp is bigger than DBUA , I can't see why people keep using DBUA more and [use] ... exp/imp less" . That is because DBUA is easier (more automated) and faster (for the actual upgrade process) and because on larger systems, this may be the only practical option.
When I used the term "chained rows" I was only thinking of the ones that johnsone described as "migrated rows", since these are indicated as "chained rows" by Oracle if you analyze your tables with the older SQL "analyze table..." command and explicitly ask for "chained rows" to be counted. Oracle does not count or indicate "migrated rows" separately from "chained rows".
True, any rows that are actually longer than a single database block will remain chained and exp/imp cannot make these unchained (unless you use a larger block size in your new database). But any of the rows that are what johnsone called "migrated rows" *WILL* be fixed by an exp/imp process. Some tables in some Oracle systems have a large number of these rows, and these can add a big performance penalty. (These rows will *NOT* be adjusted by a DBUA process. They will all remain as they were.)
I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!". We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours. We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year.
But, yes, the DBUA approach is only applicable to a version that is in the direct upgrade path.
When I used the term "chained rows" I was only thinking of the ones that johnsone described as "migrated rows", since these are indicated as "chained rows" by Oracle if you analyze your tables with the older SQL "analyze table..." command and explicitly ask for "chained rows" to be counted. Oracle does not count or indicate "migrated rows" separately from "chained rows".
True, any rows that are actually longer than a single database block will remain chained and exp/imp cannot make these unchained (unless you use a larger block size in your new database). But any of the rows that are what johnsone called "migrated rows" *WILL* be fixed by an exp/imp process. Some tables in some Oracle systems have a large number of these rows, and these can add a big performance penalty. (These rows will *NOT* be adjusted by a DBUA process. They will all remain as they were.)
I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!". We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours. We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year.
But, yes, the DBUA approach is only applicable to a version that is in the direct upgrade path.
ASKER
johnsone
"RMAN should be backing up everything except the software binaries. You don't need the 10 or 11 software to do the upgrade, on the 12 software. Again, you need to look at the RMAN documentation to be sure additional steps are not required due to differences in machine architecture and/or OS."
so instlal the oralce 12c on the new server and the RMAN everthing from 10gR2/11gR2 t othe new server and everything should be there and why still need exp/imp?
out team responsible for this upgrade use RMAN to copy everything from 10gR2/11gR2 old server to new 10gR2/11gR2 new server and they said only this method don't give the existing production 10gR2 down time.
I don't see exp/imp give down time too ! correct me if I am wrong.
"exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows. This is because you are starting with an empty table and inserting all the rows again. dbua is just upgrading the system catalog and leaving the data files untouched."
excellent! tks.
system catalog mean all metadata and oralce 12c binary file ?
"Chained rows cannot be fixed by exp/imp or expdp/impdp. By definition those are rows that are larger than a database block and there is no way that they can be changed. "
then why it can exist before migration ?
" In this case the row is moved to another block and a pointer is left in its original location to the new location. "
by using DBUA , right ? so when running DBUA on new oracle 12c server, it create a pointer from old 10gR2 server to point to the new 12c server?
what if we remove the 10fR2 server later on and the point gone ? we can't access the 12c data quickly as index/point gone ?
I don't get it .
"In this case the row is moved to another block and a pointer is left in its original location to the new location."
the pointer and block are on the new server and the block is a new block on the new server which as larger size like from 64K to 256K?
markgeer,
"I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!". We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours. We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year."
I have the felling now.
"RMAN should be backing up everything except the software binaries. You don't need the 10 or 11 software to do the upgrade, on the 12 software. Again, you need to look at the RMAN documentation to be sure additional steps are not required due to differences in machine architecture and/or OS."
so instlal the oralce 12c on the new server and the RMAN everthing from 10gR2/11gR2 t othe new server and everything should be there and why still need exp/imp?
out team responsible for this upgrade use RMAN to copy everything from 10gR2/11gR2 old server to new 10gR2/11gR2 new server and they said only this method don't give the existing production 10gR2 down time.
I don't see exp/imp give down time too ! correct me if I am wrong.
"exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows. This is because you are starting with an empty table and inserting all the rows again. dbua is just upgrading the system catalog and leaving the data files untouched."
excellent! tks.
system catalog mean all metadata and oralce 12c binary file ?
"Chained rows cannot be fixed by exp/imp or expdp/impdp. By definition those are rows that are larger than a database block and there is no way that they can be changed. "
then why it can exist before migration ?
" In this case the row is moved to another block and a pointer is left in its original location to the new location. "
by using DBUA , right ? so when running DBUA on new oracle 12c server, it create a pointer from old 10gR2 server to point to the new 12c server?
what if we remove the 10fR2 server later on and the point gone ? we can't access the 12c data quickly as index/point gone ?
I don't get it .
"In this case the row is moved to another block and a pointer is left in its original location to the new location."
the pointer and block are on the new server and the block is a new block on the new server which as larger size like from 64K to 256K?
markgeer,
"I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!". We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours. We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year."
I have the felling now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
johnsone,
"RMAN will only restore the data files. After you do that, you still need to upgrade either with dbua or exp/imp or expdp/impdp. It isn't magic, it only restores. Downtime is still required."
so in the new machine , I have to install the same version of Oracle instance first and then restore by RMAN, thne ugprade using dbua or exp/imp, right?
the RMAN we backup the existing oracle's data, so downtime should not be here for existing system?
but for the new instance on new hardware, we will install the binary and then restore using RMAN, this one will have down time for sure, right?
"dbua is just upgrading the system catalog and leaving the data files untouched."
so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?
"If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."
so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?
markgeer,
"This downtime window will be shorter if you use RMAN and DBUA than if you use exp/imp."
the point is can RMAN restore 10gR2/11gR2 to 12c, if yes, who bother DBUA and pump exp/imp ?
then all upgrade can be done by only RMAN and oracle shouldn't implement DBUA at all, agree ?
"You may want to plan to rebuild one or a few of these tables regularly (like every quarter, or once a year) if you know that your application regularly causes migrated rows. "
this means table design from time to time chagned and no change management on this and therefore some time a table will then have chained record ?
"RMAN will only restore the data files. After you do that, you still need to upgrade either with dbua or exp/imp or expdp/impdp. It isn't magic, it only restores. Downtime is still required."
so in the new machine , I have to install the same version of Oracle instance first and then restore by RMAN, thne ugprade using dbua or exp/imp, right?
the RMAN we backup the existing oracle's data, so downtime should not be here for existing system?
but for the new instance on new hardware, we will install the binary and then restore using RMAN, this one will have down time for sure, right?
"dbua is just upgrading the system catalog and leaving the data files untouched."
so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?
"If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."
so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?
markgeer,
"This downtime window will be shorter if you use RMAN and DBUA than if you use exp/imp."
the point is can RMAN restore 10gR2/11gR2 to 12c, if yes, who bother DBUA and pump exp/imp ?
then all upgrade can be done by only RMAN and oracle shouldn't implement DBUA at all, agree ?
"You may want to plan to rebuild one or a few of these tables regularly (like every quarter, or once a year) if you know that your application regularly causes migrated rows. "
this means table design from time to time chagned and no change management on this and therefore some time a table will then have chained record ?
If you are using RMAN to move the files from one machine to another, then your upgrade option is dbua. You wouldn't use exp/imp. Downtime would be required on both sides because you would want a cold consistent backup on the source side. You wouldn't need the 10 or 11 software on the new machine, just 12.
RMAN is just a way to get the data files onto the new machine. It does not modify the files and do some magical upgrade.
Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine. Again, these are detailed in the documentation.
RMAN is just a way to get the data files onto the new machine. It does not modify the files and do some magical upgrade.
Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine. Again, these are detailed in the documentation.
ASKER
"If you are using RMAN to move the files from one machine to another, then your upgrade option is dbua. You wouldn't use exp/imp."
what I mean is as RMAN don't back und restore binary data except data, so on the new target machine, I need to instla lthe same version of oracle instance as the backup data version, and then run DBUA on that new Oralce instnace to upgrade it to 12c ?
waht if the oracle instance we use RMAN from do not have an instance under the direct ugprade path of DBUA ? there are some instance like that.
"Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine. Again, these are detailed in the documentation. "
I knew, as I am gooing to read htat.
what I mean is as RMAN don't back und restore binary data except data, so on the new target machine, I need to instla lthe same version of oracle instance as the backup data version, and then run DBUA on that new Oralce instnace to upgrade it to 12c ?
waht if the oracle instance we use RMAN from do not have an instance under the direct ugprade path of DBUA ? there are some instance like that.
"Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine. Again, these are detailed in the documentation. "
I knew, as I am gooing to read htat.
ASKER
please anser this:
"dbua is just upgrading the system catalog and leaving the data files untouched."
so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?
"If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."
so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?
"dbua is just upgrading the system catalog and leaving the data files untouched."
so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?
"If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."
so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?
ASKER
any pre upgrade procedure?
This isn't exactly correct:
"dbua is just upgrading the system catalog and leaving the data files untouched."
It is true that DBUA upgrades the system catalog (basically most objects that are in the SYSTEM tablespace). And, DBUA doesn't do much with the other data files, but it does at least update the header of each data file to make sure they are compatible with the new database version.
An exp/imp process will insert each record individually into the new database, so it will clean up any migrated rows (those that were split across database blocks) as long as they are not larger than an indiviudal data block can accomodate. Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.
One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database. Try to re-validate any/all of these before starting an upgrade.
"dbua is just upgrading the system catalog and leaving the data files untouched."
It is true that DBUA upgrades the system catalog (basically most objects that are in the SYSTEM tablespace). And, DBUA doesn't do much with the other data files, but it does at least update the header of each data file to make sure they are compatible with the new database version.
An exp/imp process will insert each record individually into the new database, so it will clean up any migrated rows (those that were split across database blocks) as long as they are not larger than an indiviudal data block can accomodate. Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.
One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database. Try to re-validate any/all of these before starting an upgrade.
ASKER
is system catalog just like system schema ? which is you mean the owner is the systme itself, which contain version and system table structure ?
"Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.
"
but we discussed that the chained will be solved, right?
"Try to re-validate any/all of these before starting an upgrade. "
how to do this?
"Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.
"
but we discussed that the chained will be solved, right?
"Try to re-validate any/all of these before starting an upgrade. "
how to do this?
Most things in the SYS schema would be considered system catalog. There may be a few things in the SYSTEM schema that could be considered catalog.
No, chained rows will not be corrected by exp/imp. Migrated rows will.
No, chained rows will not be corrected by exp/imp. Migrated rows will.
How to revalidate PL\SQL objects and views?
Here is a script to identify them and give you the SQL commands that you need to run to re-compile them:
select 'alter '||object_type||' '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type in ('FUNCTION','PROCEDURE')
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY'
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE'
and object_name not like 'BIN$%'
union all
select 'alter view '||owner||'.'||object_name
from dba_objects
where object_type = 'VIEW'
and status = 'INVALID'
union all
select 'alter index '||owner||'.'||index_name|
from dba_indexes where status = 'UNUSABLE'
and index_name not like 'BIN$%'
union all
select 'alter index '||index_owner||'.'||index
from dba_ind_partitions where status = 'UNUSABLE'
and index_name not like 'BIN$%';
Here is a script to identify them and give you the SQL commands that you need to run to re-compile them:
select 'alter '||object_type||' '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type in ('FUNCTION','PROCEDURE')
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY'
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE'
and object_name not like 'BIN$%'
union all
select 'alter view '||owner||'.'||object_name
from dba_objects
where object_type = 'VIEW'
and status = 'INVALID'
union all
select 'alter index '||owner||'.'||index_name|
from dba_indexes where status = 'UNUSABLE'
and index_name not like 'BIN$%'
union all
select 'alter index '||index_owner||'.'||index
from dba_ind_partitions where status = 'UNUSABLE'
and index_name not like 'BIN$%';
If you want to just compile invalid object (basically everything markgeer posted without the index rebuilds), a script is supplied to do that.
Log in to the database as "/ as sysdba" and then run this:
@?/rdbms/admin/utlrp
Log in to the database as "/ as sysdba" and then run this:
@?/rdbms/admin/utlrp
ASKER
so revalidate mean recompile ?
why we need that BEFORE the upgrade? not after upgrade you sure ?
why we need that BEFORE the upgrade? not after upgrade you sure ?
Yes, you may need this after the upgrade. But, it is best to at least check for invalid objects before the upgrade and try to compile them before the upgrade. Ideally there are none before the upgrade. But, if you don't check you don't know. If you spot some invalid objects after the upgrade, you won't know if these were caused by the upgrade, or not, if you didn't check first.
ASKER
"Yes, you may need this after the upgrade"
So this is ok if we do it BEFORE or AFTER, right?
"But, it is best to at least check for invalid objects before the upgrade and try to compile them before the upgrade. "
"One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database. Try to re-validate any/all of these before starting an upgrade. "
when shoudl be do it? when using pump export, exp/imp or DBUA ? or any of this situation?
why it become invaid ? so re-vaildate it can make it vaild again ? I am not sure what situtation it can be? file corrupted ?
""If you spot some invalid objects after the upgrade, you won't know if these were caused by the upgrade, or not, if you didn't check first. "
excellent !
So this is ok if we do it BEFORE or AFTER, right?
"But, it is best to at least check for invalid objects before the upgrade and try to compile them before the upgrade. "
"One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database. Try to re-validate any/all of these before starting an upgrade. "
when shoudl be do it? when using pump export, exp/imp or DBUA ? or any of this situation?
why it become invaid ? so re-vaildate it can make it vaild again ? I am not sure what situtation it can be? file corrupted ?
""If you spot some invalid objects after the upgrade, you won't know if these were caused by the upgrade, or not, if you didn't check first. "
excellent !
Checking for and fixing invalid PL\SQL objects or views is independent of a database upgrade (and this is something you should be doing regularly). But, if there are invalid objects, that can sometimes prevent a data base upgrade, or cause additional problems during a database upgrade.
How do these objects become invalid? That can happen when someone applies a database (or possibly application) patch or if any developers or DBAs modify any of the database tables that PL\SQL objects or views use.
How do these objects become invalid? That can happen when someone applies a database (or possibly application) patch or if any developers or DBAs modify any of the database tables that PL\SQL objects or views use.
ASKER
ok, tks.
There is an entire chapter devoted to doing the upgrade with exp/imp or expdp/impdp. The guide also shows you which utilities to used based on the version of the source and destination.