| Goal |
|---|
| Solution |
|---|
| References |
|---|
Applies to:
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Information in this document applies to any platform.
* Reviewed 5-Feb-2016 *
Goal
HowTo recreate/rebuild a database using TTS (Transportable TableSpace):
In a number of cases there is a need to recreate the database (dictionary corruption for instance) and not being able to restore/recover the database.
The option left in such case would be exp{dp}/create dbs/imp{dp}. Drawback of this approach is the time needed to recreate the database and thus the downtime involved (original database can only be used in READONLY mode while the recreation runs).
A quicker solution might be the usage of TTS, the difference in time needed is due to the fact that TTS:
- only exports the metadata of the objects present in the tablespace and not the physical data (rows)
- no creation of indexes
The following restrictions do apply for this Note:
- the original + new database are on the same OS + Oracle version
- actions as documented do apply for original/new database only, so it might be that additional actions are required for other (remote) databases/sqlnet configuration/… due to relocation/rename of database
- during the recreation of the database it has to be ensured that there will no conflict arise by doing actions/modifications from the original + new database (replication/database jobs/..)
- the original database is kept available till it has been proven that all is working fine using the new database
- no data will be exported out of objects residing in SYSAUX, which might result in creation of empty objects in new tablespace (these objects are exported as part of step full export norows, excluding of SYSAUX only is not possible). A check should be done to get clear which schema's/applications do have segments in SYSAUX after which a plan can be made if data needs to be extracted out of SYSAUX and if true how (for example: APEX has it's own exporting utility, RMAN could be done by a user export, …)
- check note:1264715.1 to check of known issues of expdp/impdp (datapump) which might apply in your case/setup/…
…
- not all scenario's have been tested
In case the actions are to be performed on the same system you should be very careful not to overwrite/damage/use/… a file of the original database.
In case XMLSchema's are being used I do advice to use exp/imp instead of expdp/impdp. Expdp/impdp do have some restrictions/errors related to XMLSchema's which I have not observed with exp/imp.
(tested versions: 9.2.0.8.0 + 10.2.0.4.0 + 11.1.0.6.0)
A thing to keep in mind is that the application in question might have specific requirements/steps as needed as part of recreate of database (direct grants on SYS objects for instance/…). Check if applicable with the application vendor if there are additional steps to perform as part of exp/create dbs/imp.
Example for APP's R12: Note:741818.1 Export/import process for R12 using 11gR1
Solution
In order to be able to use TTS to recreate the database a number of checks/actions are needed:
Checks:
- check if set of tablespaces can be exported and is self contained
- check if metadata of database can be exported (all other objects/schemas/grants/…)
- NLS settings of database, needed for exp/create database/….
- basic check that new database contains as many objects as old database
- check that applications do work
Actions:
- full export of database with parameter rows=n/metadata only
- export of all tablespaces which needs to be transported
- creation of new database using dbca, nls settings conform old database
- (pre)creation of all schemes having objects in tablespaces being exported
- all tablespaces to be exported in readonly mode
- export of TTS
- transferring all datafiles to new location/name
- import of TTS
- import of full export (which will set the original passwords for the (pre)created schemes)
- recompile of all invalid objects
The original database has to be kept alive/existing till the moment in time it has been proven that the new database is an exact copy of production.
During the creation of the new database all TTS tablespaces will be in readonly mode in the original database, thus restricting the usage of the original database to query only.
ActionPlan for recreation of database using TTS:
- all actions in the database are done connected "/ as sysdba"
(unless other schema has been explicit mentioned)
(OLD refers to action in original database, NEW refers to action in new database)
- OLD: Check if the tablespaces to transport can be made
- OLD: Check the NLS-Settings of the current database in order to use in the export/database creation
- OLD: check if a full export can be made in order to recreate all objects/schemes/grants/.. which are not covered by the TTS export
Status of checks
- the tablespaces to be exported are a self contained set
- full export can be made in order to recreate all other objects/schemes/grants
==> so a GO status
- NEW: create a new database using DBCA
- Pre-create the users having objects in the TTS in the new database
OLD: get all create user statements
NEW: Run create user statements in order to precreate users of TTS objects
- OLD: Put all tablespaces to be exported in readonly mode
- Make a TTS export of the tablespaces in question
- OLD: make a full export with rows=n
(In case NO DDL has been used in the database there is no need for a new export and can the export be used as made in step 3))
- OLD+NEW: transfer all datafiles of TTS to new location
OLD: make an overview of all involved datafiles:
NEW: use above overview to transfer all involved datafiles to the proper location of new database
- NEW: import the TTS export
- NEW: put all TTS tablespaces as imported in read write mode
- OLD+NEW: change the default/temporary tablespace of TTS users to original values
OLD:
NEW: run above alter user statements in new database
- NEW: import the full export rows=n
- NEW: recompile all invalid objects
- OLD+NEW: Checking of objects per schema
- OLD: stop the database, cleanup after a week/month/… after all is working fine.