标题: Step by Step Guide on converting a database from Windows to AIX [打印本页] 作者: 郑全 时间: 2020-2-6 14:27 标题: Step by Step Guide on converting a database from Windows to AIX Step by Step Guide on converting a database from Windows to AIX (Doc ID 1373780.1)
In this Document
Goal
Solution
1 Prerequisits
2 Procedure overview
2.1 Prepare and collect information on the Source database
2.1.2 Check if the tablespaces to transport can be made
2.1.2 Generate a script to create the related users
2.2 Export the data
2.2.1 Prepare for the export
2.2.2 Export the data
3 Create the new database on the Destination host
4 Copy the datafile, export dumps and SQL-script to the
5 Convert the datafiles on Destination host
6 Import the dumps on the Destination host
6.1 Prepare for import
6.2 Import the datafiles
6.3 Post import steps
References
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Goal
Step by Step example of converting a database on a Windows host to an AIX host
See also : http://www.oracle.com/technetwor ... ationtts-129269.pdf
"Platform Migration Using Transportable Tablespaces: Oracle Database 11g"
Solution
parfile: expdp_full_norows.par
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
parfile: expdp_tts.par
directory=tts_exp
dumpfile=expdp_tts.dmp
logfile=expdp_tts.log
transport_tablespaces=(USERS, EXAMPLE)
- Destination server location:<path>/<tts_copy>
parfile: impdp_tts.par
directory=tts_imp
dumpfile=EXPDP_TTS.DMP
logfile=impdp_tts.log
transport_datafiles=(/<path>/tts_convert/example01.dbf, /<path>/tts_convert/users01.dbf)
parfile: impdp_full_norows.par
directory=tts_imp
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
1 Prerequisits
When a database needs to be transferred from Windows to AIX or any other platform, the key thing to check, is the endianess of the both hosts.
SQL> col platform_name format a40
select * from v$transportable_platform order by platform_name;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
...
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
SQL> select platform_id, platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------------
7 Microsoft Windows IA (32-bit)
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------------
6 AIX-Based Systems (64-bit)
If the endianess is the same, the CONVERT DATABASE can be used, but as they are different in this case,
you have to use Transporttable Tablespaces and RMAN CONVERT DATAFILE
Documents relate to CONVERT DATABASE are :
Note 1401921.1 Cross-Platform Database Migration (across same endian) using RMAN Transportable Database
Again this can ONLY be used if the Endianess of the related systems are the same. eq. Windows <-> Linux, or AIX <-> Solaris
Due to the difference in endianess, the SYSTEM and UNDO-tablespaces can NOT be converted to the new platform.
So only the none-system tablespaces are involved and need to be transfered and converted.
Special care needs to be take for the SYSAUX tablespace as it cannot be dropped from a database.
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, ...)
This document will describe how to convert a database from Windows to AIX.
2 Procedure overview
The highlevel procedure is :
Prepare and collect the information of the Source (=Windows) database
Create export dumps of metadata, including Transportable Tablespaces
Create an new database on the Destination (=AIX)
Copy the export-dump and datafiles over to the Destination host
Convert the datafiles
Import the dumps into the new database on the Destination host.
2.1 Prepare and collect information on the Source database
The related tablespaces which need to be converted are :
SQL> select tablespace_name
from dba_tablespaces
where tablespace_name not in ('SYSTEM', 'SYSAUX')
and contents not in ('UNDO', 'TEMPORARY');
2.1.2 Check if the tablespaces to transport can be made
A transportable Tablespace can only succeed if the 'recovery set' is selfcontained. Meaning that there can not be objects inside those tablespaces, which are referencing objects outside this set.
eq. An index has been created in the SYSTEM-tablespace instead of the normal index-tablespace. As the SYSTEM-tablespace is NOT involved in this procedure, the Transportable Tablespace will fail.
The index needs to be moved to the correct tablespace.
Precreate the TRANSPORT_SET_VIOLATIONS using the following command. It will report an error, but WILL create the TRANSPORT_SET_VIOLATIONS.
SQL> exec dbms_tts.transport_set_check('non_existing_tablespace',TRUE);
Create and execute a PL/SQL procedure which will check if the tablespaces in the recovery-set is selfcontained.
C:> sqlplus "/ as sysdba"
SQL> set serveroutput on size 1000000
declare
cursor c_TRANSPORT_SET_VIOLATIONS is select violations from TRANSPORT_SET_VIOLATIONS;
tablespace_names varchar2(4096);
type tslist is table of dba_tablespaces.tablespace_name%type;
ts tslist;
type cursor_ref is ref cursor;
ts_cur cursor_ref;
begin
dbms_output.put_line('Starting to check tablespaces as specified');
open ts_cur for 'select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';
fetch ts_cur bulk collect into ts;
close ts_cur;
tablespace_names :='';
for i in ts.first .. ts.last
loop
if ( i = ts.first)
then
tablespace_names := ts(i);
else
tablespace_names := tablespace_names || ', ' || ts(i);
end if;
end loop;
dbms_output.put_line(tablespace_names);
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;
/
2.1.2 Generate a script to create the related users
Generate a script to create the users who own objects in the exported tablespaces
C:> sqlplus "/ as sysdba"
SQL> set serveroutput on size 1000000
declare
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
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))';
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;
/
set feedback off
spool tts_exp_users_create.sql
/
spool off;
Generate a script with the user-default tablespaces
C:> sqlplus "/ as sysdba"
SQL>REM tts_ts_users
set serveroutput on size 1000000
declare
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
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))';
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;
/
set feedback off
spool tts_exp_users_alter.sql
/
spool off
2.2 Export the data
2.2.1 Prepare for the export
Create an RDBMS Directory pointing to an OS-directory, where the export-dumps will be stored, eq C:\TEMP
and get the database characterset.
SQL> conn / as sysdba
create directory tts_exp as '&directory_name';
SQL> column parameter format a30;
column value format a30;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
Set the correct NLS_LANG depending on the previous values :
C:\>set nls
Environment variable nls not defined
C:\>set ORA_NLS10=<oracle_home>\nls\data
set NLS_LANG=".WE8MSWIN1252"
2.2.2 Export the data
Make an export with ROWS=NO, in order to recreate all objects/schemes/grants/.. which are not covered by the TTS export
expdp_full_norows.par:
userid="/ as sysdba"
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only
C:> expdp parfile=expdp_full_norows.par
Put the related tablespaces in READ ONLY mode per requirement for the Transportable Tablespace export
C:> sqlplus "/ as sysdba"
SQL> REM tts_readonly
set serveroutput on size 1000000
declare
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
/*
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 not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';
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';
dbms_output.put_line('Tablespace ' || tslist(i).tablespace_name ||' read only');
end loop;
end;
/
Get the related datafiles for the tablespaces.
SQL> REM tts_show_datafiles
set serveroutput on size 1000000
declare
type datafiletyp is table of dba_data_files%rowtype;
filelist datafiletyp;
type cursor_ref is ref cursor;
c_cur cursor_ref;
begin
open c_cur for 'select * from dba_data_files
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))
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;
/
Export the tablespaces using Transportable Tablespace feature
expdp_tts.par :
userid="/ as sysdba"
directory=tts_exp
dumpfile=expdp_tts.dmp
logfile=expdp_tts.log
transport_full_check=y
transport_tablespaces=(USERS, EXAMPLE)
C:> expdp parfile=expdp_tts.par
3 Create the new database on the Destination host
The new database can be created using SQL*Plus of DBCA. The actual creation of this database is outside of the scope of this document.
The characterset of the database needs to be the same as the Source database or a superset of the 'source' characterset. (step 2.1.2)
The new database only needs a SYSTEM, SYSAUX, UNDO and TEMP-tablespace.
Anyother tablespace might block the import as the imported tablespace cannot exist in the new database
4 Copy the datafile, export dumps and SQL-script to the
Copy datafiles and export dumps to the Destination-server, using an OS-utility like 'ftp', 'sftp', 'scp'
Besure to copy each datafile which is shown in the output of the procedure 'tts_show_datafiles()' from step 2.2.2
Shutdown the database
C:> sqlplus "/ as sysdba"
SQL> shutdown immediate
C:> scp *.DBF <username>@<destination_host>:/<path>/tts_copy
scp \<path>\*.DMP <username>@<destination_host>:/<path>/tts_copy
5 Convert the datafiles on Destination host
Convert the datafiles from the Windows format to the AIX format. Execute this on the Destination host.
Execute this for each datafile which is shown in the output of the procedure 'tts_show_datafiles()' from step 2.2.2
% rman target /
RMAN> RUN {
CONVERT DATAFILE '/<path>/tts_copy/EXAMPLE01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/<path>/tts_convert/example01.dbf';
CONVERT DATAFILE '/<path>/tts_copy/USERS01.DBF'
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/<path>/tts_convert/users01.dbf';
}
6 Import the dumps on the Destination host
6.1 Prepare for import
Precreate the users, using the generated script tts_exp_users_create.sql (step 2.1.2)
script : tts_exp_users_create.sql
SQL> create user <usernamae> identified by <password>;
create user <usernamae> identified by <password>;
create user <usernamae> identified by <password>;
create user <usernamae> identified by <password>;
create user <usernamae> identified by <password>;
create user <usernamae> identified by <password>;
Create an RDBMS Directory where the export dumps are located, as in this exampe '/<path>/tts_copy'
SQL> connect / as sysdba
create directory tts_imp as '&directory_name';
6.2 Import the datafiles
Import the datafile and metadata using the Transportable Tablespace feature.
The datafiles are the converted datafiles
impdp_tts.par :
userid="/ as sysdba"
directory=tts_imp
dumpfile=EXPDP_TTS.DMP
logfile=impdp_tts.log
transport_datafiles=(/<path>/tts_convert/example01.dbf, /<path>/tts_convert/users01.dbf)
% impdp parfile=impdp_tts.par
6.3 Post import steps
Alter the related users, to have the correct default and temporary tablespace again, using the generated script from step 2.1.2.
In this example, the default tablespace for the users is 'USERS' tablespace and default temporary tablespace name is 'TEMP'
script : tts_exp_users_alter.sql
SQL> alter user <username> default tablespace USERS temporary tablespace TEMP;
alter user <username> default tablespace USERS temporary tablespace TEMP;
alter user <username> default tablespace USERS temporary tablespace TEMP;
alter user <username> default tablespace USERS temporary tablespace TEMP;
alter user <username> default tablespace USERS temporary tablespace TEMP;
alter user <username> default tablespace USERS temporary tablespace TEMP;
Put the tablespaces in READ WRITE again :
SQL> set serveroutput on size 1000000
set serveroutput on size 1000000
declare
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
open c_cur for 'select * from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';
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';
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' put in read write mode');
end loop;
end;
/
Import all the related objects which were not imported by the TTS, like grants etc.
impdp_full_norows.par:
userid="/ as sysdba"
directory=tts_imp
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
full=y
content=metadata_only
table_exists_action=skip
% impdp parfile=impdp_full_norows.par
Recompile all invalid objects
SQL> connect / as sysdba
@?/rdbms/admin/utlrp.sql