I've recently had cause to try and install the sample schema(OE,HR and the like). I can;t remember actually the last time i ever did this, in fact i don't think i ever did - these sample schemas came late to the game from my point of view - I'm familiar with them though from seeing blogs and talks that involve using (which is actually why i wanted to look at them).
Anyway the process to be able to load them is a bit of saga and I've no idea why it's been set up this way. Making the sample schemas easy to load surely should be a priority as a lot of the people doing this will be new to Oracle and making it complicated gives the wrong impression.
I first decided to just try and figure out how to do this without reading any documentation - this should be possible surely the process should be obvious, right?
So i go and take a look in the $ORACLE_HOME/demo directory of my 11.2.x install (actually it's 11.2.0.4 from the otn developer vm that covers 12c upgrades). In there is a schema directory which contains some promising looking directories but a closer inspection reveals seemingly no master script to run these in (i had assumed it would be very simple sql scripts) only a script called mkplug which looks very 'clever' and like it might do the job but it's not what i expected at all.
Hmm - before i start then lets have a quick read of the docs....
First thing that jumps out is that the sample stuff is on the companion cd and not part of the standard install - so what is this stuff i have in the demo directory then?
Checking then for the installs for oe etc then in the schema by schema instructions and the script referenced (oe_main.sql) does not exist in my demo directory - so maybe i need the companion cd after all - whats going on here - it's like it's designed to confuse. If it's on the companion cd - don't include half of the stuff with the normal database software!
Anyway, like most other people i guess i don't want to download the companion software, i just want to use the stuff. In looking around the directory i discovered the mkplug.sql script, this you might think is something to do with pdb's etc in 12c but this is 11.2 it can't be. In fact it turns out to be a scipt that attached a prebuilt template database file to an existing database (using TTS) and then adds all the logical metadata using the sql scripts. I think this is what is used when you add the schemas to an existing database via dbca. I decide to try and manually run this to get the sample schemas.
I eventually got it to work (I won't bore you with all the steps- I'll just highlight some of the major fixes/changes/features to be aware of).
1. The script takes loads of parameters - the password ones are obvious - some of the later ones are not - here are the fiddly ones with explanation
a) metadata import file - file name only of tts dumpfile (example.dmp in 11.2) - no full path!
b) database backup file - file name only of tts datafile (example01.dfb) - no full path! -also note dfb is not a typo!
c) output database file - full path of database file name when plugged into your database
d) output log directory - full path ending with a / of where logs are created
e) output dump file - full path to where metadata import file and backup file are located - in my case $ORACLE_HOME/assistants/dbca/templates/
2) make sure all the various options needed are installed - in my case mdsys/olapsys were missing and i had to add them - check the prereqs in the schema install docs
3) The mkplug.sql script has a bug the line that reads " SELECT MAX(file_id)+1 INTO data_file_id FROM dba_data_files;" is wrong - the fix i did was to comment out this line completely and then change the line in the declaration section to this
data_file_id number := 5;
After all that messing about i finally have workable demo schemas in a new tablespace called example.
I've no idea why this has been made so complex.
Surely it could just be a simple script that did an impdp of some dumpfile included with the software distribution - this could be run manually or by dbca. I really don't know why it has been set up this way - this file would be pretty small and the process surely a lot simpler than the current one
Had a quick look in 12c by the way and the bug with mkplug seems to be fixed and some of the missing scripts seem to have appeared now without the companion cd.....
Comments
Post a Comment