Purpose:
The purpose of this document is to detail and elaborate on the steps involved
in migrating databases across heterogenous platform. Oracle databases running
on different flavors of UNIX such as Linux and Solaris operating systems can be
migrated to AIX operating system. There are challenges being faced with respect
to endian format, size and availability of the Oracle database datafiles when
migrating to AIX platform and this document can be used as means to overcome
these challenges. This document also details on how to use the transportable
tablespaces feature of Oracle to upgrade to a higher version of Oracle running
on a different operating system than the source operating system along with the
scripts needed at various stages to perform the same.
Migration
Strategy Matrix
Source
Operating System
|
Source
Endian format
|
Target
Operating System
|
Target
Endian format
|
Action
Needed
|
Solaris
64 bit
|
Big
|
AIX 64
bit
|
Big
|
Endian
conversion not needed. Perform upgrade steps from the doc
|
Linux
64 bit
|
Little
|
AIX 64
bit
|
Big
|
Endian
conversion needed. Use this document fully
|
Database
size consideration
Database
of different sizes can be a challenge in terms of storage speed available for
the operation and the criticality of the database as well. Approach has to be
weighed in adopting the correct strategy to do the migration.
As a
general rule the following can be used.
Size
|
Approach
|
<50Gb
|
Perform
datapump export/import and not use transportable tablespaces
|
>50Gb
|
Ideal
to use transportable tablespaces
|
Migration
and Upgrade Steps
1. Get
the source and the target database platform information by running the
following query
SET
LINESIZE 100
COL
PLATFORM_NAME FOR A40
SELECT
A.platform_id, A.platform_name, B.endian_format
FROM
v$database A, v$transportable_platform B
WHERE
B.platform_id (+) = A.platform_id;
2. Check
the database characterset on source and the target. They should be the same.
select *
from database_properties where property_name like '%CHARACTERSET';
3. Verify the database options
and components
Select *
from v$options
Select *
from dba_registry
4. Create a database link on the
target database
connect
system/password
create
database link ttslink using '<TNS ENTRY of DATABASE NAME>;
Do not
forget to add the tnsnames.ora entry on the target database tnsnames.ora file
5. Create the
impdp/expdp directories on both source and target databases
create
directory impdp_dir as '/bckp/<DATABASE NAME>/impdp';
create
directory expdp_dir as '/bckp/<DATABASE NAME>/expdp';
6. Create all the source
tablespaces exists in the target database with a minimal size of 10m size.
7. Import metadata required for
TTS using database link on the target server.
impdp
system/password DIRECTORY=impdp_dir LOGFILE=dp_userimp.log NETWORK_LINK=ttslink
FULL=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE
8. Set the default tablespaces
to system and not any other tablespace on the target database.
Drop
user tablespaces on target database
select
property_value from database_properties where
property_name=’DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_VALUE
--------------
USERS
alter
database default tablespace system;
9. Drop all tablespaces on
target database except system, sysaux using the following script
set
heading off feedback off trimspool on linesize 500 spool tts_drop_ts.sql
prompt /*
===================== */
prompt /*
Drop user tablespaces */
prompt /*
===================== */
select
‘DROP TABLESPACE ‘ || tablespace_name || ‘ INCLUDING CONTENTS AND DATAFILES;’
from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = ‘PERMANENT’;
spool off
10. On the source database
run the self-containment check and make sure there are no violations
declare
checklist
varchar2(4000);
i number
:= 0;
begin
for ts in
(select
tablespace_name
from
dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = ‘PERMANENT’)
loop
if (i=0)
then
checklist
:= ts.tablespace_name;
else
checklist
:= checklist||’,’||ts.tablespace_name;
end if;
i := 1;
end loop;
dbms_tts.transport_set_check(checklist,TRUE,TRUE);
end;
/
select *
from transport_set_violations;
11. Do a metadata export on
the source database.
expdp
system/password DIRECTORY=ttsdir LOGFILE=dp_fullexp_meta.log
DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY EXCLUDE=USER,ROLE,ROLE_GRANT,PROFILE
12. Make
all the Source database tablespaces readonly
Script to
make tablespaces readonly
set
heading off feedback off trimspool on linesize 500 spool tts_tsro.sql
prompt /*
==================================== */
prompt /*
Make all user tablespaces READ only */
prompt /*
==================================== */
select
'ALTER TABLESPACE ' || tablespace_name || ' READ only;' from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = 'PERMANENT'
/
13. Capture the sequence values
set
heading off feedback off trimspool on escape off
set long
1000 linesize 1000 pagesize 0
col
SEQDDL format A300
spool
tts_create_seq.sql
prompt /*
========================= */
prompt /*
Drop and create sequences */
prompt /*
========================= */
select
regexp_replace(
dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
'^.*(CREATE
SEQUENCE.*CYCLE).*$',
'DROP
SEQUENCE "'||sequence_owner||'"."'||sequence_name
||'";'||chr(10)||'\1;')
SEQDDL
from
dba_sequences
where
sequence_owner not in
(select
name
from
system.logstdby$skip_support
where
action=0)
;
spool off
14. Generate the par file for the
metadata export of user tablespaces in source database using the following
script.
REM
REM
Create TTS Data Pump export and import PAR files
REM
set
feedback off trimspool on
set
serveroutput on size 1000000
REM
REM Data
Pump parameter file for TTS export
REM
spool
dp_ttsexp.par
declare
tsname
varchar(30);
i number
:= 0;
begin
dbms_output.put_line('directory=ttsdir');
dbms_output.put_line('dumpfile=dp_tts.dmp');
dbms_output.put_line('logfile=dp_ttsexp.log');
dbms_output.put_line('transport_full_check=no');
dbms_output.put('transport_tablespaces=');
for ts in
(select
tablespace_name from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = 'PERMANENT'
order by
tablespace_name)
loop
if (i!=0)
then
dbms_output.put_line(tsname||',');
end if;
i := 1;
tsname :=
ts.tablespace_name;
end loop;
dbms_output.put_line(tsname);
dbms_output.put_line('');
end;
/
spool off
REM
REM Data
Pump parameter file for TTS import
REM
spool
dp_ttsimp.par
declare
fname
varchar(513);
i number
:= 0;
begin
dbms_output.put_line('directory=ttsdir');
dbms_output.put_line('dumpfile=dp_tts.dmp');
dbms_output.put_line('logfile=dp_ttsimp.log');
dbms_output.put('transport_datafiles=');
for df in
(select
file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name
= b.tablespace_name
and
a.tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = 'PERMANENT'
order by
a.tablespace_name)
loop
if (i!=0)
then
dbms_output.put_line(''''||fname||''',');
end if;
i := 1;
fname :=
df.file_name;
end loop;
dbms_output.put_line(''''||fname||'''');
dbms_output.put_line('');
end;
/
spool off
15. Export the tablespaces on the
source database using the script generated from the previous step
expdp
system/password PARFILE=dp_ttsexp.par
Contents
of the dp_ttsexp.par files run on <DATABASE NAME> database
directory=expdp_diretory
dumpfile=dp_tts.dmp
logfile=dp_ttsexp.log
transport_full_check=no
transport_tablespaces=IDM_DATA,
IDM_INDEXES,
LCRM9_DATA,
LCRM9_INDEX,
P4SECDB_DATA,
P4SECDB_INDEX,
TOOLS,
USERS.
16. Copy the datapump export
files to the target server
scp
dp_full.dmp dp_tts.dmp target:/tmp
17. The endian format conversion
should be done on the source system using the following rman convert command.
The db_file_name_convert should point to either a new staging directory or an
NFS mountpoint to stage the converted files.
Source
system
convert
tablespace
'TOOLS','USERS','IDM_DATA','IDM_INDEXES','LCRM9_DATA','LCRM9_INDEX','P4SECDB_DATA','P4SECDB_INDEX'
to platform="AIX-Based Systems (64-bit)"
db_file_name_convert='/db01/oradata/DRMLNX/','/bckp/DRMLNX/stage/'
parallelism
4;
18. scp the converted files
from the source system to the stage directory on the target system.
This step
is avoided if we have a NFS mountpoint.
Generate
the Scp commands by logging into the source database system and by running the
following command.
select
'scp '||file_name||' to oracle@sj1asm044:/bckp/<DATABASE
NAME>/aixstage/'||substr(file_name,20,50)
from
dba_data_files
/
The
output can be scripted in a shell script and executed from the source system.
19.
Import the tablespaces metadata to the target by running the following command
on the target system. For this import use the existing files in the NFS
directory to make them part of the database. After they are part of the
database, we will be moving the datafiles to target ASM.
impdp system/password PARFILE=dp_ttsimp.par
20. On
the target database the tablespaces will be read only. Bring the tablespaces
online and then convert them to read write. Finally make them offline to copy
the database files to the destination ASM filesystem
Script to
make tablespaces online
set
heading off feedback off trimspool on linesize 500 spool tts_tson.sql
prompt /*
==================================== */
prompt /*
Make all user tablespaces offline */
prompt /*
==================================== */
select
'ALTER TABLESPACE ' || tablespace_name || ' offline;' from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = 'PERMANENT'
/
Script to
make tablespaces offline
set
heading off feedback off trimspool on linesize 500 spool tts_tsoff.sql
prompt /*
==================================== */
prompt /*
Make all user tablespaces offline */
prompt /*
==================================== */
select
'ALTER TABLESPACE ' || tablespace_name || ' offline;' from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = 'PERMANENT'
/
set
heading off feedback off trimspool on linesize 500 spool tts_tsrw.sql
prompt /*
==================================== */ prompt /* Make all user tablespaces
READ WRITE */
prompt /*
==================================== */ select ‘ALTER TABLESPACE ‘ || tablespace_name
|| ‘ READ WRITE;’ from dba_tablespaces
where
tablespace_name not in ('SYSTEM','SYSAUX')
and
contents = ‘PERMANENT’; spool off
Run the
following scripts in sql prompt.
@tts_tson.sql
@tts_tsrw.sql
@tts_tsoff.sql
21. Copy
the staged files from NFS directory to target ASM using the following command.
All the
rman commands can be generated by logging to the source system and generating a
spool file
select
'copy datafile '||''''||'NFS directory'||substr(file_name,22,50)||''''||' to
'||''''||'+DATADG/oradata/<DATABASE
NAME>/'||substr(file_name,22,50)||''''||';'
from
dba_data_files
where
tablespace not in (‘SYSTEM’,’SYSAUX’0
/
Now, run
the generated commands on the target system
The above
files can be split into multiple files so that they can run parallel.
Example
rman
target /
copy
datafile '/bckp/<DATABASE NAME>/aixstage/ idm_data01.dbf ' to
'+DATADG/oradata/<DATABASE NAME>/ idm_data01.dbf’;
22.
Rename the datafiles on the tablespaces to the target ASM location.
set
heading off feedback off trimspool on linesize 500 spool tts_ren.sql
select
‘alter database rename file ‘||’’’’||file_name||’’’’||’ to
‘||’+DATADG/oradata/<db sid>’||substr(file_name,13,50)||’’’’||’;’ from
dba_data_files where tablespace_name not in (‘SYSTEM’,’SYSAUX’);
spool
off
23.
Make all the tablespaces online. Run the following the sql prompt
@tts_tson.sql
24. Verify the new file path for
all the tablespaces by running the following query.
Select
file_name from dba_data_files where tablespace_name not in (‘SYSTEM’,’SYSAUX’)
/
25. All the remaining metadata
will be imported to the target system using the following command. Double for
the tablespaces to be read write mode.
impdp
system/password DIRECTORY=ttsdir LOGFILE=dp_fullimp.log DUMPFILE=dp_full.dmp
FULL=y
26. Create the sequences on the
target system using the script generated from the source system. Run the
following on the source system to generate the script.
set
heading off feedback off trimspool on escape off
set long
1000 linesize 1000 pagesize 0
col
SEQDDL format A300
spool
tts_create_seq.sql
prompt /*
========================= */
prompt /*
Drop and create sequences */
prompt /*
========================= */
select
regexp_replace(
dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
'^.*(CREATE
SEQUENCE.*CYCLE).*$',
'DROP
SEQUENCE "'||sequence_owner||'"."'||sequence_name
||'";'||chr(10)||'\1;')
SEQDDL
from
dba_sequences
where
sequence_owner not in
(select
name
from
system.logstdby$skip_support
where
action=0)
;
spool off
27.
Compile invalid objects on the target system.
@?/rdbms/admin/utlrp.sql
28.
Verify the object counts between the source and target database systems.
Select
count(*),owner from dba_objects group by owner
If any of
the schemas do not match query the individual schemas for the object_type
counts
Select
count(*),object_type from dba_objects where owner=<owner name> group by
object_type
/
29. Verify
the datafiles on target database for block corruption using the dbv utility.
dbv
FILE=+DATADG/oradata/<DATABASE NAME>/tool01.dbf
------------------------------------------------------------------------------------------------------------------
Migration Of An Oracle Database Across OS Platforms (Generic Platform) (Doc ID 733205.1)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
Comments
Post a Comment