Ready to kick off with the first of our four Hands-On-Labs at OOW15 at the Nikko Hotel. Currently it’s calm before the storm …
But just in case you didn’t grab a seat at one of the labs then please come to our talk today (Monday!) at 1:30pm in Moscone South 102 as we’ll show you the core parts of the lab – and you’ll be able to download it later on from the blog and run it by yourself.
- Upgrade and Migrate to Oracle Database 12c:
Live and Uncensored! [CON6777]
Mike Dietrich, Master Product Manager, Oracle
Roy Swonger, Sr Director, Software Development, Oracle
Monday, Oct 26, 1:30 p.m. | Moscone South—102
Download the lab via this link:
I was at a couple of you sessions at OOW15, hope you had a good flight back and are not totally burnt out. In a follow up to what you have
demonstrated to us, I decided to try it out with the HOL provided (12c Upgrade Migration & Consolidation).
In Part 3, we were going to Migrate FTEX database with Full Transportqable export/import into PDB2. When I got to the section to do the
Full transportable export/import with data pump, this completed successfully. ( See pdb2_HOL.log file)
There where a number of ORA-31684 object types already exist errors, but these can be ignored.
By excluding the ORA-31684 errors, we are left with the following:
grep ORA- pdb2_HOL.log |grep ORA-31684 -v
ORA-39083: Object type TABLESPACE:"UNDOTBS1" failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file "/oradata/FTEX/undotbs01.dbf"
ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to load/unload and is being skipped due to error:
ORA-22303: type "SYS"."JDM_STR_VALS" not found
ORA-21700: object does not exist or is marked for delete
Can these safely be ignored?
PS: I cannot upload the file mentioned, but the remaining legitimate errors in the files are the ones listed above
thanks for your feedback – and yes, I had a safe travel back home but I was very tired after OOW 😉
Yes, you can safely ignore the errors. Actually the first one (UNDO) is treated better when you would go 12c=>12c. It happens because of the source being on 11g as far as I remember.
The second one has also to do with the 11g API – I think you wouldn’t see it when going from 12c=>12c.
Thanks and thanks for trying out the lab!!!
I have progressed further by trying out the Full transportable export/import on a real test environment. Whilst I am impressed with the speed of upgrading an 22.214.171.124 database to 12c, I am disappointed in some of the issues it has thrown up.
I noticed that I know have about 161 invalid objects which cannot be compiled and gotten rid of. These are predominately from the APEX schema in the 11g database and as well as the SYSMAN schema. Is there a way to clean up these objects? A new non-CDB 12c database has 27 users, when I imported from 11g it added more users.
Also dbca creates the USERS tablespace in 11g and 12c, so it seems that Full tablespace export/import will automatically fail on import because of this. Are all these bugs or just limitations? it would have been perfect if we could just import the required schemas with full transportable export/import.
Thanks for your detailed comments.
And I agree that the documentation is a bit short on these things right now. We discuss internally at the moment to release a note about best practices for FTEX.
One conflict I could see here:
(a) different APEX versions causing trouble as APEX itself is not meant to be transported this way. Could you please check which APEX versions both of your databases have?
SQL> select COMP_ID, VERSION, STATUS from DBA_REGISTRY where COMP_ID=’APEX’;
(b) SYSMAN is definitely not meant to be transported this way
A remedy could be simply to EXCLUDE=SCHEMA:"=’SYSMAN’" for instance.
Regarding the USERS tablespace that is not data pump’s fault. It’s in your hands to have the USERS tablespace created by DBCA or not. What type of error did you get?
Thanks Mike for taking time to respond.
In our 12c Environment Apex was not installed so not entries for Apex in the dba_registry. But in 11gR it was installed by default (version 3). Likewise the SYSMAN users is present in 11g when a database is created but in 12c, it is not.
Unfortunately Full transportable export/iimport does not allow for any filtering. Hence, I was not able to exclude any users at all. I raised an SR to conform this.
Also the number of Oracle provided users in 11g ae more than what we get in 12c. So during the full transportable export/import all these users got created in the 12c environment. This resulted in having 166 invalid objects left for the APEX_3XXXX user and SYSMAN. These could not be recompiled so I had to drop them and clean up any dependant objects left.
With regards to the USERS tablespace, this gets created as part of creating the database using DBCA. and Oracle also use it as the default tablespace for some of the oracle supplied user accounts. In 12c, it is the same and also oracle store objects in the users tablespace for one of ORACLEs supplied user accounts.
I think the reason it was not picked up in the lab is because you were going from a nonCDB to a CDB. Otherwise the full transportable export/import would have failed on import. `For it to work in my environment, I had to rename the USERS tablespace and datafile in the 11g environment before the export.
I do apologise for the long statement/explanation, but i feel Oracle have missed a trick here. As I have mentioned before Full transportable export/import definitely is a lot faster, but it is too restrictive not allowing to exclude tablespace/objects that are not required if upgrading from 126.96.36.199 to 12c.
As you are quite close to the product development team, I would leave it to you to influence them to write a much detailed white paper which covers a real scenario as we users might want to use this functionality. -:)
In the meantime, we have had to revert to using the old expdp/impdp, doing a schema import to move to 12c.
Have a great weekend
Further more, I see you will be at the ICC Birmingham, i have a couple of colleagues attending. Would it be possible to put a demo together to cover the issue I have raised by upgrading an 188.8.131.52/184.108.40.206 database to 220.127.116.11,5 using Full transportable export/import.
FTEX does allow you to filter. I’ve done it several times because of different reasons (a user schema did exist already and we don’t want to overwrite it etc).
See this example:
impdp system/oracle@pdb2 network_link=sourcedb version=12 full=y transportable=always metrics=y exclude=statistics directory=mydir logfile=pdb2.log exclude=tablespace:"IN (‘USERS’)" transport_datafiles=’/oradata/CDB2/pdb2/mike01.dbf’
Works pretty well. You can combine multiple EXCLUDE command (as you see above) but never INCLUDE and EXCLUDE in the same clause.
Let me further check your other points and I’ll get back to you asap.
I still can’t follow your points with the USERS tablespace.
DBCA creates the USERS tablespace by default unless you click it away – this is correct. So I understand this point.
Did you specify the USERS tablespace as one of the tablespaces to be transported? If YES, then it needs to be dropped in your target before moving on. If NO then I would like to see the errors.
Your assumption that this would have broken the lab is not correct. I’ve done FTEX with an SAP BW already (ok, no APEX inside) but we didn’t move any USERS tablespace and had no issues at all.
So please may I ask for your logs and a description of actions step-by-step you have taken so I can verify and nail down the issues?
And regarding UKOUG:
I’m sorry but first of all I will be able to stay only until Tuesday – and second colleagues have filled my "free time" already with meetings.
Sorry – thanks