|
|
"Gail Shaw" <initialsurname@sentech sa dot com> wrote in message
news:471a7343@news.povray.org...
>
> "Orchid XP v7" <voi### [at] devnull> wrote in message
> news:471a5fde@news.povray.org...
>
>>
>> Yeah - and then rebuild all the table structures, indexes, data
>> permissions, etc. :-S Still, it depends on how "vital" the data is. I
>> take it you didn't just loose your entire customer list or anything? ;-)
>
> Not necessary. Do a select * from all the system tables too, or generate
> the
> scripts straight from the system tables Is possible at least in MS SQL. Is
> something that I do fairly often when creting a replica of the prod
> environment
>
> Still that is the last resort. Should always be able to revert back to a
> backup.
Yep it is possible and I may have a go at it. Oracle Enterprise Manager is
much more work to set up than the Recovery Manager. Although I was earlier
testing out the export on RMAN, OEM is more critical to preserve because of
the amount of work redoing it.
RMAN has features that make life easier recovering from this situation:
When a database physical backup happens, as well as storing its backup job
info in the RMAN catalog, RMAN also stores it in the client DB's own
controlfile, which is part of the physical structure of the DB. So the
database contains its own recovery information and backups do still happen
even with the catalog lost, and it does all get sent to tape eventually.
Secondly, Oracle version 10g (three DBs are 10g RMAN clients here) has a
cool, easy feature: In one command, all the physical files in the backup
storage area are scanned, type-identified, and any orphan backups that are
unknown to the catalog can be added into it. It is easy to build a new
empty catalog and then populate it with data from prior backups.
I expect much more trouble with preserving OEM objects. I haven't looked in
detail, but I expect it has a complex schema. It would be best if I use an
AppDev tool to pull all the definitions for all objects, because it would
take much study and work to make a roll-your-own SQL script that reads the
data dictionary and dynamically writes user object build scripts based on
what it finds.
Selecting from tables, their column definitions and table data are only a
small part of getting the data and structure out: I have to get the entire
application out. I would also have to make SQL to recreate:
Table type (heap, index-organised, cluster, partition) Definitions of all
indexes and constraints on each table, foreign-key constraints to other
tables, tablespace and storage info, remembering again to do the same for
each of its indexes.
Triggers on the tables -- PL/SQL code that fires before/after an
insert/update/delete.
Sequences, including the number they are now at. (So new sequences are
created from that starting point instead of starting at one)
Views -- named SQL that can be selected and joined as one uses a table in
most places.
The program code stored in PL/SQL functions, procedures and packages.
Jobs. Ugh, I know the least about these. I'm sure OEM will have job
schedules registered in the database engine.
I already have a script that dynamically builds a bunch of creation SQL
scripts for such things as users (also preserving passwords by copying the
encrypted value), roles, grants, quotas.
Actually, the more I think about it, the more I think that the normal
procedure of building Enterprise Manager and discovering all the target
Oracle DBs, listeners, etc, setting up the administrators, alarm triggers,
notification pagers and emails -- bad as it is, it may be easier and more
reliable in the end. Certainly the final result would have vendor support,
whereas one I made with manually-pulled data wouldn't, and you could never
be sure that what you made works 100% kosher and won't blow up on you later
or let you down in an emergency.
We will have an export of the OEM schema. It is just oldish because it is
before the exports began to fail. I would try loading that before doing a
full rebuild or manual script-magic-blue-smoke anyway.
--
Cheers,
Brian
Post a reply to this message
|
|