TTS transfer on DataGuard

Oracle TTS(Transportable tablespace) allows us to copy datafiles between databases. TTS was introducted in Oracle 8i.

Lets create a tablespace, schema, create table and insert some data on the table. 

-- Create tablespace to test transportable tablespace

Create tablespace tts_test
datafile '/u01/app/oracle/oradata/orcl/tts_test01.dbf'
size 2m
autoextend on 
next 2m
maxsize unlimited;

-- Create user schema 

Create user tts_user
identified by oracle
default tablespace tts_test;

-- Grant required privileges

Grant connect, resource to tts_user;

-- Connect as user tts_user and create table

Conn tts_user/oracle
Create table tbl_test(
sn number,
name varchar2(200)
);

-- Insert huge data using PL/SQL below

declare
begin
for i in 1..50000000
loop
insert into tbl_test values(i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');                   
commit;
end loop;
end;
/

Verify the number of rows on the table.

Select count(*) from tbl_test;

Connect as user sys, create directory and grant read, write privileges on directory to public.

-- Connect as user sys

Conn / as sysdba

-- Create directory

Create directory dump_dir
as '/home/oracle';

-- Grant read, write privileges on directory to public.

Grant read,write on directory dump_dir to public;

Alter tablespace to read only state.

ALTER TABLESPACE tts_test READ ONLY;

Export tablespace with transport_tablespaces option. 

expdp dumpfile=tts_test_$(date +%Y_%m_%d).dmp logfile=exp_tts_test_$(date +%Y_%m_%d).log directory=dump_dir transport_tablespaces=tts_test

Copy datafile to oracle home.

In destination database with DataGuard configured. Execute impdp on Primary Database.

Copy datafiles to default datafile location.

cp ~oracle/tts_test01.dbf /u01/app/oracle/oradata/orcl/tts_test01.dbf

Create user tts_user and grant required privileges.

Create user tts_user
identified by oracle;
grant connect, resource to tts_user;

Now start import on destination database

impdp dumpfile=tts_test_$(date +%Y_%m_%d).dmp logfile=imp_tts_test_$(date +%Y_%m_%d).log directory=dump_dir transport_datafiles='/u01/app/oracle/oradata/orcl/tts_test01.dbf'

Now modify user and set default tablespace and imported tablespace

alter user tts_user default tablespace tts_test;

Set imported tablespace to read write.

alter tablespace tts_test read write;

Verify table count.

select count(*) from tbl_test;

Now insert rows on primary database so that log switch will occur and we can check recovery state on Standby database

insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;
insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;
insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;

Execute following command on the Standby database to verify the status of recovery.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Check the name of the database we have imported with impdp. we can notice datafile with the name UNNAMED. Actually the file doesnot exists. So we need to copy the datafile that has been shipped with expdp and rename it.

select name from v$datafile;

Set standby_file_management database parameter to manual.

Alter system set standby_file_management=manual;

Now rename UNNAMED with the datafile we have copied.

alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005' to '/u01/app/oracle/oradata/orcl/tts_test01.dbf';

Revert back standby_file_management database parameter to Auto.

Alter system set standby_file_management=auto;

Now start standby recover mode.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now we can noticed that archivelog has been applied on standby database. Check alert<SID>.log for details.