How To Recreate A Database Using TTS (Transportable TableSpace) [ID 733824.1]

rongshiyuan發表於2013-01-24

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Reviewed 11-Jan-2013.

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:
1) the original + new database are on the same OS + Oracle version
2) 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
3) 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/..)
4) the original database is kept available till it has been proven that all is working fine using the new database
5) 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, ...)
6) check note:1264715.1 to check of known issues of expdp/impdp (datapump) which might apply in your case/setup/...

...
99) 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

Fix

Note: Only expdp and impdp are supported for 11.2 and higher.

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:
-------------------------------------------------
0) 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)

1) OLD: Check if the tablespaces to transport can be made

REM Pre-create table TRANSPORT_SET_VIOLATIONS
REM --------------------------------------------------------------------------
REM In case TTS has not been used yet there will be the need to pre-create
REM table TRANSPORT_SET_VIOLATIONS
REM below will raise an error but will create TRANSPORT_SET_VIOLATIONS

exec dbms_tts.transport_set_check('non_existing_tablespace',TRUE);

REM tts_check
REM --------------------------------------------------------------------------
REM Below is a check if the set of TTS is possible, in other words:
REM Checks if a set of tablespaces (to be transported) is self-contained
REM in case errors are raised you have to check and resolve them

create or replace procedure tts_check(tablespace_names in varchar2)
is
cursor c_TRANSPORT_SET_VIOLATIONS is
select violations from TRANSPORT_SET_VIOLATIONS;
begin
dbms_output.put_line('Starting to check tablespaces as specified');
DBMS_TTS.TRANSPORT_SET_CHECK(tablespace_names, TRUE, TRUE);
for c_cur in c_TRANSPORT_SET_VIOLATIONS loop
dbms_output.put_line(c_cur.violations);
end loop;
dbms_output.put_line('In case there are no line(s) after '||chr(39)||'Starting to check ...'||chr(39));
dbms_output.put_line('It does imply that the check went fine and there are no issues to resolve.');
end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_check('USERS,TOOLS,MY_DATA1,MY_INDEX1');
exec tts_check('&tablespace_names');

2) OLD: Check the NLS-Settings of the current database in order to use in the export/database creation

column parameter format a30;
column value format a30;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';

3) 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

4) NEW: create a new database using DBCA

$> export DISPLAY=....
$> dbca &

The quickest way will be to use the template database as present in ORACLE_HOME. As mentioned before you have to use the same NLS settings of the original database. For the rest of the parameters you can use a copy of the one of the original database, which can be done after database creation

*In case of versions < 10.2.0.x you might need to run the upgrade script. in order to upgrade datadictionary to correct patch level

5) Pre-create the users having objects in the TTS in the new database
OLD: get all create user statements

REM tts_exp_users
REM --------------------------------------------------------------------------
REM Below will show all users who do have objects in the tablespaces
REM to be transported. The output will be spooled to screen and will consist
REM of create user statements. The correct password/privileges/... will
REM be imported/set by the full export/import

create or replace procedure tts_exp_users(tablespace_names in varchar2)
is

string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

dbms_output.put_line(string);
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in ('||string||')';
fetch c_cur bulk collect into users;
close c_cur;
for i in users.first .. users.last
loop
dbms_output.put_line('create user '||users(i)||' identified by '||users(i)||';');
end loop;
end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_exp_users('USERS,TOOLS,MY_DATA1,MY_INDEX1');
spool tts_exp_users_create.sql
exec tts_exp_users('&tablespace_names');
spool off;

NEW: Run create user statements in order to precreate users of TTS objects

@tts_exp_users_create.sql

6) OLD: Put all tablespaces to be exported in readonly mode

REM tts_readonly
REM --------------------------------------------------------------------------
REM tts_readonly is a procedure to put all tablespaces as present in string
REM in read only mode.
create or replace procedure tts_readonly(tablespace_names in varchar2)
is

string varchar2(4096);
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

/*
1st check if one of the tablespaces is already in read only mode, if true procedure will fail
due to fact that we cannot discriminate if the read only was a failure of an earlier run of this script
OR that it is really a read only tablespace.
*/

open c_cur for 'select * from dba_tablespaces where tablespace_name in ('||string||')';
fetch c_cur bulk collect into tslist;
close c_cur;

for i in tslist.first .. tslist.last
loop
if tslist(i).status!='ONLINE'
then
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' can NOT be put in read only mode, current status '||
tslist(i).status);
ts_fail:=ts_fail+1;
end if;
end loop;

if ts_fail!=0
then
dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read only mode');
return;
end if;

for i in tslist.first .. tslist.last
loop
execute immediate 'alter tablespace '||tslist(i).tablespace_name||' read only';
end loop;

end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_readonly('USERS,TOOLS,MY_DATA1,MY_INDEX1');
exec tts_readonly('&tablespace_names');

7) Make a TTS export of the tablespaces in question

8) 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))

9) OLD+NEW: transfer all datafiles of TTS to new location
OLD: make an overview of all involved datafiles:

REM tts_show_datafiles
REM --------------------------------------------------------------------------
REM tts_show_datafilesis a procedure to show all datafiles beloning to the
REM TTS tablespaces being exported
create or replace procedure tts_show_datafiles(tablespace_names in varchar2)
is

string varchar2(4096);

type datafiletyp is table of dba_data_files%rowtype;
filelist datafiletyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

open c_cur for 'select * from dba_data_files where tablespace_name in ('||string||') order by tablespace_name,file_id';
fetch c_cur bulk collect into filelist;
close c_cur;

for i in filelist.first .. filelist.last
loop
dbms_output.put_line('Tablespace: '||filelist(i).tablespace_name||' File: '||filelist(i).file_name);
end loop;
end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_show_datafiles('USERS,TOOLS,MY_DATA1,MY_INDEX1');
exec tts_show_datafiles('&tablespace_names');

NEW: use above overview to transfer all involved datafiles to the proper location of new database

Use OS utilities to copy/ftp/... the files to the new location
(leaving the OLD database as is)

10) NEW: import the TTS export

Set the correct NLS-characterset:
$> export NLS_LANG='.'

Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x: export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

Imp example:
------------
imp_tts.par:
userid=system
file=exp_tts.dmp
log=imp_tts.log
transport_tablespace=y
tablespaces=(USERS,TOOLS,MY_DATA1,MY_INDEX1)
datafiles=(...,...,...,...)

$> imp parfile=imp_tts.par

Impdp example:
--------------
connect / as sysdba
create directory tts_imp as '&directory_name';

impdp_tts.par:
userid=system
directory=tts_imp
dumpfile=expdp_tts.dmp
logfile=impdp_tts.log
transport_datafiles=('...','...','...','...')
==> use the list as made in step: 9. transfer all datafiles of TTS ...

$> impdp parfile=impdp_tts.par

*) A number of errors which might arise during TTS import will be resolved during importing exp{dp}_full_norows, examples:
- ORA-39082: Object type TRIGGER ... created with compilation warnings
- context indexes (privileges are not granted yet)
ORA-39083: Object type INDEX failed to create with error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtabc.post_transport:TC
ORA-01031: insufficient privileges
Failing sql is:
CREATE INDEX ... INDEXTYPE IS "CTXSYS"."CTXCAT" PARALLEL 1
- ...

Ensure to check that all errors as reported during TTS import are resolved after import of exp{dp}_full_norows + ?/rdbms/admin/utlrp:
- triggers valid
- {context} indexes created
- ...

11) NEW: put all TTS tablespaces as imported in read write mode

REM tts_readwrite
REM --------------------------------------------------------------------------
REM tts_readwrite is a procedure to put all tablespaces as present in string
REM in read only mode (only valid if status is read only)
create or replace procedure tts_readwrite(tablespace_names in varchar2)
is

string varchar2(4096);
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

open c_cur for 'select * from dba_tablespaces where tablespace_name in ('||string||')';
fetch c_cur bulk collect into tslist;
close c_cur;

for i in tslist.first .. tslist.last
loop
if tslist(i).status!='READ ONLY'
then
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' can NOT be put in read write mode, current status '||tslist(i).status);
ts_fail:=ts_fail+1;
end if;
end loop;

if ts_fail!=0
then
dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read write mode');
return;
end if;

for i in tslist.first .. tslist.last
loop
execute immediate 'alter tablespace '||tslist(i).tablespace_name||' read write';
end loop;

end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_readwrite('USERS,TOOLS,MY_DATA1,MY_INDEX1');
exec tts_readwrite('&tablespace_names');

12) OLD+NEW: change the default/temporary tablespace of TTS users to original values
OLD:

REM tts_ts_users
REM --------------------------------------------------------------------------
REM tts_ts_users is a procedure extract the alter user statements in order
REM to correct the default + temporary tablespace of users of TTS
create or replace procedure tts_ts_users(tablespace_names in varchar2)
is

string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

def_ts dba_users.default_tablespace%type;
temp_ts dba_users.temporary_tablespace%type;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

dbms_output.put_line(string);
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in ('||string||')';
fetch c_cur bulk collect into users;
close c_cur;
for i in users.first .. users.last
loop
select default_tablespace,temporary_tablespace into def_ts,temp_ts
from dba_users where username=users(i);
dbms_output.put_line('alter user '||users(i)||' default tablespace '||def_ts||' temporary tablespace '||temp_ts||';');
end loop;
end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_ts_users('USERS,TOOLS,MY_DATA1,MY_INDEX1');
spool tts_exp_users_alter.sql
exec tts_ts_users('&tablespace_names');
spool off

NEW: run above alter user statements in new database

@tts_exp_users_alter.sql

13) NEW: import the full export rows=n

14) NEW: recompile all invalid objects

connect / as sysdba

15) OLD+NEW: Checking of objects per schema

Same code being used on OLD and NEW:

set pagesize 9999
spool check_objects_&old_new

column owner format a20;

select owner,object_type,count(*) from dba_objects
group by owner,object_type order by owner,object_type;

*) keep in mind that objects/grants/... made in SYS account are not exported
*) remember the procedures TTS_... will show a difference

$> stty cols 160
$> sdiff check_objects_old.lst check_objects_new.lst|more

16) OLD: stop the database, cleanup after a week/month/... after all is working fine.

References

NOTE:77442.1 - ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.
NOTE:741818.1 - Export/import process for R12 using 11gR1 or 11gR2
NOTE:1264715.1 - Master Note for Data Pump

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-753219/，如需轉載，請註明出處，否則將追究法律責任。

How to Move a Database Using Transportable Tablespaces (文件 ID 1493809.1)
2014-08-05
Database
Transportable Tablespaces (TTS) for Oracle Database [ID 1461278.2]
2013-01-24
TTSOracleDatabase
using dbms_file_transfer transportable tablespace between asm
2016-12-14
ASM
How to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN [ID 371556.1]
2011-10-23
ROSPlatform
How to Move or Copy a Tablespace to Another Database (61)
2007-10-15
Database
How to Recreate the OraInventory on UNIX Systems [ID 472854.1]
2012-12-25
AI
How To Drop, Create And Recreate DB Control In A 10g Database
2010-04-07
Database
How to Restore the Database Using AMDU after Diskgroup Corruption
2014-02-19
RESTDatabase
How to check whether the current database in using Oracle options
2014-10-08
DatabaseOracle
VMS: ALTER TABLESPACE/DATABASE TO RENAME FILES on VMS [ID 9560.1]
2013-01-25
Database
Master Note for Transportable Tablespaces (TTS) -- Common Questions_1166564.1
2014-09-19
ASTTTS
How to move Oracle Spatial objects from SYSAUX tablespace to a user defined tablespace [ID 1119758.1
2011-07-19
OracleObjectUX
聊聊Oracle可傳輸表空間（Transportable Tablespace）（上）
2017-09-15
Oracle
聊聊Oracle可傳輸表空間（Transportable Tablespace）（中）
2017-09-15
Oracle
聊聊Oracle可傳輸表空間（Transportable Tablespace）（下）
2017-09-15
Oracle
MySQL Transportable Tablespace(傳輸表空間) 使用詳解
2017-02-21
MySql
Oracle OCP IZ0-053 Q694(Transportable Tablespace)
2016-03-15
Oracle
How to Quiesce a Database
2009-09-18
UIDatabase
How to Duplicate a Database in NOARCHIVELOG mode (Doc ID 275480.1)
2014-09-04
DatabaseHive
How to Resolve Invalid Objects in a Database [ID 158185.1]
2012-12-20
ObjectDatabase
How to Find Out How Much Space an Index is Using
2008-05-07
Index
How to Move/Recreate GI Management Repository to Different Shared Storage
2015-06-14
Oracle OCP 1Z0 053 Q427(Transportable Tablespace)
2016-04-03
Oracle
Oracle OCP IZ0-053 Q425( transportable tablespace set)
2016-03-21
Oracle
How To Move The DB Audit Trails To A New Tablespace
2016-12-15
AI
How to Rename Tablespace In Oracle10g
2010-11-19
Oracle
How to Perform a Healthcheck on the Database
2009-02-04
ORMDatabase
How to enable the flashback database:
2009-02-10
Database
How a Database Is Mounted (293)
2007-12-01
Database
How To Size the Database Smart Flash Cache (Doc ID 1317950.1)
2013-07-26
Database
How To List All The Named Events Set For A Database [ID 436036.1]
2011-06-02
Database
Oracle OCP 1Z0 053 Q426(transportable tablespace)
2016-04-04
Oracle
Oracle OCP 1Z0 053 Q633(Transportable Tablespace)
2016-03-31
Oracle
Step By Step Guide To Create Physical Standby Database Using RMAN [ID 469493.1]
2011-10-23
GUIIDEDatabase
Database Testing: How to Regression Test a Relational Database
2012-12-18
Database
How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]
2010-12-23
IDEBloCDatabase
How to Perform a Health Check on the Database
2016-12-27
ORMDatabase
[原創] How to Quiesce a Database
2008-08-22
UIDatabase

How To Recreate A Database Using TTS (Transportable TableSpace) [ID 733824.1]

Applies to:

Goal

Fix

References

相關文章