How to rename a 500GB schema in 17 minutes.....



One of the long needed pieces of functionality in oracle is the ability to just rename a schema - I've needed this loads of times and what would seem to be a relatively simple dictionary update for oracle to implement (update user$ anyone?) is still not available and invariable you end up having to do some kind of logical unload/load to acheive it. When this is a very large schema it can take a huge amount of time.

Here is a 'quick' way of acheiving it - it's all supported and it's nothing 'dodgy' but it does feel a little nerve wracking at some points in the process.... note it makes use of TTS so the restrictions around that all have to be adhered to.

Anyway here is how you do it - hold on to your hats....

For this example i have got a schema called 'DAILY' but it actually needs to be called 'APPSCHEMA'

The first thing we need to make the tablespace read only


alter tablespace DAILY read only;

We then do a datapump export of the tablespaces related to that user - in this case my tablespace is the same name as the application schema

expdp / dumpfile=stage1.dmp transport_tablespaces=DAILY directory=gold

Export: Release 11.2.0.3.0 - Production on Fri Apr 4 17:10:49 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01":  /******** dumpfile=stage1.dmp transport_tablespaces=DAILY
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/AUDIT_OBJ
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_TRANSPORTABLE_01 is:
  /oracle/11.2.0.3.0.DB/rdbms/log/stage1.dmp
******************************************************************************
Datafiles required for transportable tablespace DAILY:
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b72sn_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b8t85_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b8y1c_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b91q3_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b958m_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b991n_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b9dkd_.dbf
  /oracle/DB/oradata/DATABASE/DATABASE/datafile/o1_mf_endur_ma_8s7b9j3d_.dbf
Job "OPS$ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:12:49



 This creates a small dumpfile (stage1.dmp) that contains metadata relating to the contents of the DAILY tablespace - this is only the physical components though we also need to extract the logical components

 to extract all the components that do not physically exist in that tablespace but belong to this user we need to do an additional export

 expdp / parfile=stage2.par

stage2.par contains

schemas=DAILY
include=TABLE:"IN (SELECT table_name from dba_tables where owner='DAILY' and temporary='Y')"
include=FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,SYSTEM_GRANT
dumpfile=stage2.dmp


Export: Release 11.2.0.3.0 - Production on Fri Apr 4 17:20:04 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08":  /******** parfile=stage2.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39168: Object path SYNONYM was not found.
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_08 is:
  /oracle/11.2.0.3.0.DB/rdbms/log/stage2.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_08" completed with 1 error(s) at 17:20:55


So in this case i'm extracting all the objects the schema owns and any rights it requires - note that temporary table definitions also have to be extracted here - they too do not physically exist. Note that this is not an exhaustive list - it's just the objects that my schema has

Again this creates a fairly small file (stage2.dmp) that contains all the metadata to recreate the schema and all its objects.

So now i have all the components i need to do an entire TTS which will include a schema rename - i have the 2 dumpfiles - and importantly the huge datafiles containing the tables and indexes.

Now comes the nervous bit..... now we take the files offline to avoid them being changed

alter tablespace daily offline;

And being totally paranoid at this point - i move the files out of the way temporarily





[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE# cd datafile
 [oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile# ls *daily*
o1_mf_daily_9m0fpdqo_.dbf  o1_mf_daily_9m5qo5xz_.dbf  o1_mf_daily_9m5qomdp_.dbf  o1_mf_daily_9m5qozkz_.dbf  o1_mf_daily_9m5qpq1y_.dbf  o1_mf_daily_9mldo4hn_.dbf
o1_mf_daily_9m5qntwj_.dbf  o1_mf_daily_9m5qof0m_.dbf  o1_mf_daily_9m5qoqs2_.dbf  o1_mf_daily_9m5qp85t_.dbf  o1_mf_daily_9m5qqcql_.dbf
[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile# mkdir rich
[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile# mv *daily* ./rich


Now even more nervously we run this......the keep datafiles should mean the step i did previously wasn't needed but i was too scared to do that....



SYS@DATABASE>drop tablespace daily including contents keep datafiles;

Tablespace dropped.



Now we put the files back as they were before i renamed them

[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile# cd rich
[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile/rich# ls
o1_mf_daily_9m0fpdqo_.dbf  o1_mf_daily_9m5qo5xz_.dbf  o1_mf_daily_9m5qomdp_.dbf  o1_mf_daily_9m5qozkz_.dbf  o1_mf_daily_9m5qpq1y_.dbf  o1_mf_daily_9mldo4hn_.dbf
o1_mf_daily_9m5qntwj_.dbf  o1_mf_daily_9m5qof0m_.dbf  o1_mf_daily_9m5qoqs2_.dbf  o1_mf_daily_9m5qp85t_.dbf  o1_mf_daily_9m5qqcql_.dbf
[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile/rich# mv * ..
[oracle@oracle-server]:DATABASE:/oracle/DATABASE/oradata/DATABASE/datafile/rich#


Now the files are back where they were originally - so now we have to put everything back and give it a new name.

First up we have to load in the stage 2 file so the user will exist when we come to do the tts import. Note the remap_schema to give us the schema name we want - the remap_tablespace is a temporary thing as we can;t remap it to the final tablespace we want as that has not been loaded yet - we will change this afterwards.

impdp / remap_schema=DAILY:APPSCHEMA  dumpfile=stage2.dmp remap_tablespace=DAILY:USERS

That runs away (some output removed) - a load of stuff ends up invalid as no tables exist yet - but we ignore that for now.


Import: Release 11.2.0.3.0 - Production on Wed Apr 2 17:27:01 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning option

Master table "ORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "ORACLE"."SYS_IMPORT_FULL_01":  /******** remap_schema=DAILY:APPSCHEMA dumpfile=stage2.dmp remap_tablespace=DAILY:USERS

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT

ORA-39082: Object type VIEW:"APPSCHEMA"."ACS_BALANCE_ALT_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."ACS_BALANCE_ACCOUNT_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."ACS_BALANCE_SUMMARY_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."CREDIT_EXPDEF_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."RISK_EXPDEF_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."CREDIT_EXPOSURE_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."RISK_EXPOSURE_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."CREDIT_FACILITY_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."RISK_FACILITY_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."IDX_DEF_VALIDATED_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."TRADE_SNAPSHOT_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."TWB_OLDB_VIEW_64BIT1" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."TWB_OLDB_VIEW_64BIT2" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."COMM_SCHEDULE_DETAIL_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."COMM_SCHEDULE_HEADER_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."COMM_FINANCIAL_PARAM_VIEW" created with compilation warnings

ORA-39082: Object type VIEW:"APPSCHEMA"."COMM_DELIVERY_EVENT_VIEW" created with compilation warnings

Job "ORACLE"."SYS_IMPORT_FULL_01" completed with 6707 error(s) at 17:31:33




So now the schema we want exists as do all of the logical objects - now we need to get the tables and indexes back - so we TTS that lot in

 impdp / parfile=tts.par

tts.par contains

directory=gold dumpfile=stage1.dmp transport_datafiles=

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m0fpdqo_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qntwj_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qo5xz_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qof0m_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qomdp_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qoqs2_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qozkz_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qp85t_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qpq1y_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9m5qqcql_.dbf,

  /oracle/DATABASE/oradata/DATABASE/datafile/o1_mf_daily_9mldo4hn_.dbf

remap_schema=DAILY:APPSCHEMA remap_tablespace=DAILY:APPSCHEMA



Lets run that in

[oracle@oracle-server]:DATABASE:/oracle/export/GOLD# impdp / parfile=tts.par

Import: Release 11.2.0.3.0 - Production on Wed Apr 2 17:32:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning option

Master table "ORACLE"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "ORACLE"."SYS_IMPORT_TRANSPORTABLE_01":  /******** parfile=tts.par

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/AUDIT_OBJ

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "ORACLE"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:41:52

Now a little tidy up - we need to make the tablespace read write and switch appschema to have the tts tablespace as its default
alter tablespace APPSCHEMA read write;
alter user APPSCHEMA quota unlimited on APPSCHEMA;
alter user APPSCHEMA default tablespace APPSCHEMA;



Now recompile all the stuff that was invalid from the logical import - which will now be OK as the tables exist

@?/rdbms/admin/utlrp

There you go - schema renamed in a 0.5TB database in a matter of minutes! What was called DAILY is now called APPSCHEMA.....

Comments