288 lines
8.7 KiB
Plaintext
Executable File
288 lines
8.7 KiB
Plaintext
Executable File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
~~ Walkthrough for:
|
|
~~ V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
This procedure apply on 11.2.0.4 or higher soure database.
|
|
The target database can be in higher version than source database (upgrade)
|
|
|
|
~~~~~~~~~~~~~~~~~~~~~~~~
|
|
~~ Source database setup
|
|
~~~~~~~~~~~~~~~~~~~~~~~~
|
|
initGREEDOPRD.ora:
|
|
|
|
db_name=GREEDO
|
|
instance_name=GREEDOPRD
|
|
db_unique_name=GREEDOPRD
|
|
compatible=11.2.0.0
|
|
control_files=(/data/GREEDOPRD/control01.ctl)
|
|
db_create_file_dest=/data
|
|
db_create_online_log_dest_1=/data
|
|
db_recovery_file_dest_size=4G
|
|
db_recovery_file_dest=/fra
|
|
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
|
|
log_archive_format=%t_%s_%r.arc
|
|
db_block_size=8192
|
|
open_cursors=300
|
|
diagnostic_dest=/app/oracle/base/admin/GREEDOPRD
|
|
sga_max_size=3G
|
|
sga_target=3G
|
|
pga_aggregate_target=512M
|
|
processes=350
|
|
audit_file_dest=/app/oracle/base/admin/GREEDOPRD/adump
|
|
audit_trail=db
|
|
remote_login_passwordfile=exclusive
|
|
undo_tablespace=UNDOTBS
|
|
|
|
|
|
-- tablespace setup
|
|
create tablespace TS1 datafile size 16M autoextend ON next 16M;
|
|
create tablespace TS2 datafile size 16M autoextend ON next 16M;
|
|
create tablespace TS3 datafile size 16M autoextend ON next 16M;
|
|
|
|
alter tablespace TS1 add datafile size 16M autoextend ON next 16M;
|
|
alter tablespace TS1 add datafile size 16M autoextend ON next 16M;
|
|
alter tablespace TS2 add datafile size 16M autoextend ON next 16M;
|
|
alter tablespace TS2 add datafile size 16M autoextend ON next 16M;
|
|
alter tablespace TS2 add datafile size 16M autoextend ON next 16M;
|
|
|
|
-- schema setup
|
|
grant connect, resource, unlimited tablespace to user1 identified by user1;
|
|
grant connect, resource, unlimited tablespace to user2 identified by user2;
|
|
|
|
grant create view to user1;
|
|
grant create view to user2;
|
|
|
|
create profile STANDARD_USER limit
|
|
SESSIONS_PER_USER 10
|
|
CONNECT_TIME 30;
|
|
|
|
create profile VIP_USER limit
|
|
SESSIONS_PER_USER 20
|
|
CONNECT_TIME 60;
|
|
|
|
alter user user1 profile STANDARD_USER;
|
|
alter user user2 profile VIP_USER;
|
|
|
|
-- schema contents setup
|
|
create table user1.tab1 as select * from dba_extents;
|
|
alter table user1.tab1 move tablespace TS1;
|
|
insert into user1.tab1 select * from user1.tab1;
|
|
insert into user1.tab1 select * from user1.tab1;
|
|
insert into user1.tab1 select * from user1.tab1;
|
|
commit;
|
|
insert into user1.tab1 select * from user1.tab1;
|
|
insert into user1.tab1 select * from user1.tab1;
|
|
commit;
|
|
|
|
create table user2.tab2 as select * from user1.tab1;
|
|
insert into user2.tab2 select * from user2.tab2;
|
|
commit;
|
|
insert into user2.tab2 select * from user2.tab2;
|
|
commit;
|
|
|
|
alter table user1.tab1 move tablespace TS2;
|
|
|
|
create index user1.ind1 on user1.tab1(blocks) tablespace TS3;
|
|
create index user2.ind2 on user2.tab2(blocks) tablespace TS3;
|
|
|
|
alter table user2.tab2 move tablespace TS2;
|
|
alter index user2.ind2 rebuild tablespace TS3;
|
|
|
|
|
|
|
|
create table user1.message(m varchar2(30), d date) tablespace TS3;
|
|
insert into user1.message values('Setup',sysdate);
|
|
commit;
|
|
|
|
|
|
grant select on v_$session to user1;
|
|
grant select on v_$tablespace to user2;
|
|
|
|
connect user1/user1
|
|
create view sess as select * from v$session;
|
|
|
|
|
|
connect user2/user2
|
|
|
|
create or replace procedure TSLIST
|
|
is
|
|
cursor c_ts is select * from v$tablespace;
|
|
begin
|
|
for r_ts in c_ts
|
|
loop
|
|
dbms_output.put_line( 'Tablespace: ' ||r_ts.name );
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
|
|
-- check if the tablespaces are self_contained
|
|
SQL> exec sys.dbms_tts.transport_set_check(ts_list => 'TS1,TS2,TS3', incl_constraints => true);
|
|
SQL> Select * from transport_set_violations;
|
|
|
|
PL/SQL procedure successfully completed.
|
|
|
|
-- backup source database
|
|
run
|
|
{
|
|
set nocfau;
|
|
allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/GREEDO/%d_%U_%s_%t.bck';
|
|
allocate channel ch02 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/GREEDO/%d_%U_%s_%t.bck';
|
|
allocate channel ch03 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/GREEDO/%d_%U_%s_%t.bck';
|
|
allocate channel ch04 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/GREEDO/%d_%U_%s_%t.bck';
|
|
backup as compressed backupset incremental level 0 database include current controlfile plus archivelog delete input;
|
|
}
|
|
|
|
|
|
~~~~~~~~~~~~~~~~~~~~~~~~
|
|
~~ Target database setup
|
|
~~~~~~~~~~~~~~~~~~~~~~~~
|
|
initWEDGEPRD.ora:
|
|
|
|
db_name=WEDGE
|
|
instance_name=WEDGEPRD
|
|
db_unique_name=WEDGEPRD
|
|
compatible=19.0.0.0.0
|
|
control_files=(/data/WEDGEPRD/control01.ctl)
|
|
db_create_file_dest=/data
|
|
db_create_online_log_dest_1=/data
|
|
db_recovery_file_dest_size=4G
|
|
db_recovery_file_dest=/fra
|
|
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
|
|
log_archive_format=%t_%s_%r.arc
|
|
db_block_size=8192
|
|
open_cursors=300
|
|
diagnostic_dest=/app/oracle/base/admin/WEDGEPRD
|
|
sga_max_size=3G
|
|
sga_target=3G
|
|
pga_aggregate_target=512M
|
|
pga_aggregate_limit=2G
|
|
processes=350
|
|
audit_file_dest=/app/oracle/base/admin/WEDGEPRD/adump
|
|
audit_trail=db
|
|
remote_login_passwordfile=exclusive
|
|
undo_tablespace=TBS_UNDO
|
|
|
|
|
|
-- backup target database
|
|
run
|
|
{
|
|
set nocfau;
|
|
allocate channel ch01 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/WEDGE/%d_%U_%s_%t.bck';
|
|
allocate channel ch02 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/WEDGE/%d_%U_%s_%t.bck';
|
|
allocate channel ch03 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/WEDGE/%d_%U_%s_%t.bck';
|
|
allocate channel ch04 device type disk format '/mnt/yavin4/tmp/_oracle_/orabackup/WEDGE/%d_%U_%s_%t.bck';
|
|
backup as compressed backupset incremental level 0 database include current controlfile plus archivelog delete input;
|
|
}
|
|
|
|
|
|
~~
|
|
|
|
-- downnload scripts (attached to note, currently: rman_xttconvert_VER4.3.zip) to source machine
|
|
-- unzip to a temporary location
|
|
-- edit xtt.properties file at least with mandatory filds:
|
|
|
|
tablespaces
|
|
platformid
|
|
src_scratch_location
|
|
dest_scratch_location
|
|
dest_datafile_location
|
|
(if using 12c) -- usermantransport=1
|
|
|
|
|
|
-- get PLATFORM_ID for SOURCE and DESTINATION databases
|
|
SQL> select PLATFORM_ID from V$DATABASE;
|
|
|
|
-- once xtt.properties OK on source, copy to dest in $TEMPDIR
|
|
|
|
-- set TEMPDIR environement variable for BOTH machines:
|
|
export TMPDIR=/mnt/yavin4/tmp/_oracle_/tmp/TEMP_SOURCE_XTTCONVERT
|
|
export TMPDIR=/mnt/yavin4/tmp/_oracle_/tmp/TEMP_DEST_XTTCONVERT
|
|
|
|
|
|
-- Run the backup on the source system
|
|
cd $TMPDIR
|
|
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
|
|
|
|
|
|
-- Restore the datafiles on the destination system
|
|
cd $TMPDIR
|
|
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
|
|
|
|
|
|
-- Roll Forward Phase
|
|
-- as long as necessary perform backup/restore (incremental!) using previous commands
|
|
|
|
-- in order to trace, we add a new datafile and some data
|
|
|
|
insert into user1.message values('Roll Forward Phase',sysdate);
|
|
commit;
|
|
|
|
alter tablespace TS2 add datafile size 8M autoextend ON next 8M;
|
|
|
|
|
|
|
|
|
|
-- Phase final Incremental Backup
|
|
-- If you are running 12c, this step can be replaced by Phase 4 in Note 2005729.1
|
|
|
|
insert into user1.message values('Just before RO tablespaces',sysdate);
|
|
|
|
alter tablespace TS1 read only;
|
|
alter tablespace TS2 read only;
|
|
alter tablespace TS3 read only;
|
|
|
|
-- take final incremental backup ignoring errors like:
|
|
ORA-20001: TABLESPACE(S) IS READONLY OR,
|
|
OFFLINE JUST CONVERT, COPY
|
|
ORA-06512: at line 284
|
|
|
|
cd $TMPDIR
|
|
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
|
|
|
|
-- restore final incremental backup on target database
|
|
cd $TMPDIR
|
|
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
|
|
|
|
|
|
-- on source
|
|
------------
|
|
mkdir -p /mnt/yavin4/tmp/_oracle_/tmp/DATAPUMP_SOURCE_XTTCONVERT
|
|
SQL> create directory DPUMP_TTS as '/mnt/yavin4/tmp/_oracle_/tmp/DATAPUMP_SOURCE_XTTCONVERT';
|
|
|
|
cd /mnt/yavin4/tmp/_oracle_/tmp/DATAPUMP_SOURCE_XTTCONVERT
|
|
|
|
-- export metadata
|
|
expdp userid=system/secret directory=DPUMP_TTS LOGFILE=metadata.log FULL=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE dumpfile=metadata.dmp CONTENT=METADATA_ONLY
|
|
|
|
-- parfile exp.par:
|
|
dumpfile=xttdump.dmp
|
|
directory=DPUMP_TTS
|
|
statistics=NONE
|
|
transport_tablespaces=TS1,TS2,TS3
|
|
transport_full_check=y
|
|
logfile=tts_export.log
|
|
|
|
-- expdp en mode "transportable tablespace"
|
|
expdp userid=system/***** parfile=exp.par
|
|
|
|
-- copy dumpfiles from source to destination
|
|
cp /mnt/yavin4/tmp/_oracle_/tmp/DATAPUMP_SOURCE_XTTCONVERT/xttdump.dmp /mnt/yavin4/tmp/_oracle_/tmp/DATAPUMP_DEST_XTTCONVERT/
|
|
|
|
-- on target
|
|
------------
|
|
-- import metadata
|
|
impdp userid=system/secret directory=DPUMP_TTS dumpfile=metadata.dmp logfile=import_metadata.log remap_tablespace=TEMP:TMS_TEMP
|
|
-- import "transportable tablespace"
|
|
impdp userid=system/secret parfile=imp.par
|
|
|
|
|
|
~~~~~~~~~~~~~~
|
|
~~ Other links
|
|
~~~~~~~~~~~~~~
|
|
-- https://dbavivekdhiman.wordpress.com/2015/05/31/cross-platform-migration-from-aix-oracle-11-2-0-3-to-linux11-2-0-3/
|
|
-- 11G - Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 1389592.1)
|
|
|
|
|