This post gives us hands on steps on how to migrate a database from Windows platform to Linux platform with an upgrade from 12.1.0.2 to 18.5.
Mostly we get to do this kind of exercise in a Lab setup or a setup that was initially used as test setup and then one client wants this live and can afford a downtime.
Source -
OS - Windows (Microsoft Windows x86 64-bit)
Oracle Database version - 12.1.0.2
Target -
OS - Linux (Linux x86 64-bit)
Oracle Database version - 18.5.0.0
List of Steps Needed to Complete the Migration:-
1. Check platform compatibility between source and target OS
2. Start the database in read only mode
3. Check database readiness for transport from Windows to Linux using DBMS_TDB.CHECK_DB
4. Check if there are any external objects
5. Execute the Rman Convert database command
6. Copy converted datafiles, generated Transport Script and Parameter File to Linux
7. Edit the init.ora for the new database
8. Edit the Transport Script and Parameter File changing the windows paths to Linux Paths
9. Execute the Transport Script
10.Change the Database ID
11.Check database integrity
Let's get into hands on now :-
Check the Endian format
-------------------------------------------------------------
col platform_name format a40;
set pages 100
select * from v$transportable_platform order by 2;
Windows and Linux use same Endian format - Little Endian
Check the platform of existing database
-------------------------------------------------------------
select platform_id,platform_name from v$database;
On source database
-------------------------------------------------------------
shutdown immediate;
startup nomount;
alter database open read only;
Verify whether the database can be migrated and whether the current state is correct
-------------------------------------------------------------
Fails means database couldn't be transported
If it doesn't return resolved, it means the state of the database is correct
set serveroutput on
declare
v_return boolean;
begin
v_return: dbms_tdb.check_db('Linux x86 64-bit '); --- Add the
end;
/
It should return PL/SQL procedure successfully completed.
Verify whether the database has external tables directories or bfiles;
-------------------------------------------------------------
declare
v_return boolean;
begin
v_return:= dbms_tdb.check_external;
end;
/
--- The following directories exist in the database:
Check the INVALID object count
-------------------------------------------------------------
select owner, count(*) from dba_objects where status='INVALID' group by owner;
Source Server - Create a folder for the converted data files
-------------------------------------------------------------
New Folder -> test
c:\test
Use RMAN convert database to transport the entire database for destination platform
-------------------------------------------------------------
C:\rman target /
convert database new database 'newdb' --- Enter the required new name for the database
transport script 'c:\test\transport.sql'
db_file_name_convert 'C:\<Location_of_directory_where_all_data_files_are_placed\' 'c:\test' --- folder which will contain the data files
to platform 'Linux x86 64-bit';
--- This will start converting the datafiles
Now, create a parameter file
-------------------------------------------------------------
create pfile 'C:\test\pfile.ora' from spfile;
Check in the directory to see if the files are created
-------------------------------------------------------------
You will have all the .dbf files, pfile and transport.sql files
Move the files to the target - Linux machine
-------------------------------------------------------------
Target directory - /tmp/test
Create required directories you will mention in pfile:-
mkdir admin
mkdir dbname --- Enter database name that is created
mkdir adump bdump cdump udump
mkdir flash_recovery_area
mkdir -p oradata/db_name --- Enter database name that is created, let's say lindb
Move all the data files to the oradata location
mv /tmp/test/*.DBF $ORACLE_HOME/oradata/lindb
Open the pfile and edit the changes corresponding to the directory structure
-------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle
*.audit_file_dest='C:\app\oracle\admin\windb\adump'
*.audit_file_dest='/u02/app/oracle/admin/windb/adump'
mkdir /u02/app/oracle/admin/windb/adump
*.control_files='C:\app\oracle\oradata\windb\control01.ctl','C:\app\oracle\fast_recovery_area\windb\control02.ctl'
*.control_files='/u02/app/oracle/oradata/windb/control01.ctl','/u02/app/oracle/oradata/windb/control02.ctl'
mkdir /u02/app/oracle/oradata/windb/
*.db_name='windb'
*.db_name='windb'
*.db_recovery_file_dest='C:\app\oracle\fast_recovery_area\windb'
*.db_recovery_file_dest='/u02/app/oracle/oradata/windb/fast_recovery_area'
mkdir -p /u02/app/oracle/oradata/lindb/fast_recovery_area
*.diagnostic_dest='C:\app\oracle'
*.diagnostic_dest='/u02/app/oracle/oradata/windb/diag'
mkdir -p /u02/app/oracle/oradata/windb/diag
*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'
*.local_listener='LISTENER_windb'
*.local_listener='LISTENER'
Open the TRANSPORT.SQL and edit the changes corresponding to the directory structure
-------------------------------------------------------------
Mention the pfile location
new redo log files
datafile location
temp file locations
pfile location
Export the new database ORACLE_SID
-------------------------------------------------------------
You will observe the database is an idle instance:-
You can run
@TRANSPORT.SQL
or execute commands step by step
All the database files will be restored as in the source database.
Now run the following scripts
catupgrd.sql
utlirp.sql
utlrp.sql
The complete exercise, right from Installing software to migrating the complete database can be viewed at https://youtu.be/HpOn1rE_d-c
Feel free to comment and correct me if any mistakes.
Mostly we get to do this kind of exercise in a Lab setup or a setup that was initially used as test setup and then one client wants this live and can afford a downtime.
Source -
OS - Windows (Microsoft Windows x86 64-bit)
Oracle Database version - 12.1.0.2
Target -
OS - Linux (Linux x86 64-bit)
Oracle Database version - 18.5.0.0
List of Steps Needed to Complete the Migration:-
1. Check platform compatibility between source and target OS
2. Start the database in read only mode
3. Check database readiness for transport from Windows to Linux using DBMS_TDB.CHECK_DB
4. Check if there are any external objects
5. Execute the Rman Convert database command
6. Copy converted datafiles, generated Transport Script and Parameter File to Linux
7. Edit the init.ora for the new database
8. Edit the Transport Script and Parameter File changing the windows paths to Linux Paths
9. Execute the Transport Script
10.Change the Database ID
11.Check database integrity
Let's get into hands on now :-
Check the Endian format
-------------------------------------------------------------
col platform_name format a40;
set pages 100
select * from v$transportable_platform order by 2;
Windows and Linux use same Endian format - Little Endian
Check the platform of existing database
-------------------------------------------------------------
select platform_id,platform_name from v$database;
On source database
-------------------------------------------------------------
shutdown immediate;
startup nomount;
alter database open read only;
Verify whether the database can be migrated and whether the current state is correct
-------------------------------------------------------------
Fails means database couldn't be transported
If it doesn't return resolved, it means the state of the database is correct
set serveroutput on
declare
v_return boolean;
begin
v_return: dbms_tdb.check_db('Linux x86 64-bit '); --- Add the
end;
/
It should return PL/SQL procedure successfully completed.
Verify whether the database has external tables directories or bfiles;
-------------------------------------------------------------
declare
v_return boolean;
begin
v_return:= dbms_tdb.check_external;
end;
/
--- The following directories exist in the database:
Check the INVALID object count
-------------------------------------------------------------
select owner, count(*) from dba_objects where status='INVALID' group by owner;
Source Server - Create a folder for the converted data files
-------------------------------------------------------------
New Folder -> test
c:\test
Use RMAN convert database to transport the entire database for destination platform
-------------------------------------------------------------
C:\rman target /
convert database new database 'newdb' --- Enter the required new name for the database
transport script 'c:\test\transport.sql'
db_file_name_convert 'C:\<Location_of_directory_where_all_data_files_are_placed\' 'c:\test' --- folder which will contain the data files
to platform 'Linux x86 64-bit';
--- This will start converting the datafiles
Now, create a parameter file
-------------------------------------------------------------
create pfile 'C:\test\pfile.ora' from spfile;
Check in the directory to see if the files are created
-------------------------------------------------------------
You will have all the .dbf files, pfile and transport.sql files
Move the files to the target - Linux machine
-------------------------------------------------------------
Target directory - /tmp/test
Create required directories you will mention in pfile:-
mkdir admin
mkdir dbname --- Enter database name that is created
mkdir adump bdump cdump udump
mkdir flash_recovery_area
mkdir -p oradata/db_name --- Enter database name that is created, let's say lindb
Move all the data files to the oradata location
mv /tmp/test/*.DBF $ORACLE_HOME/oradata/lindb
Open the pfile and edit the changes corresponding to the directory structure
-------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle
*.audit_file_dest='C:\app\oracle\admin\windb\adump'
*.audit_file_dest='/u02/app/oracle/admin/windb/adump'
mkdir /u02/app/oracle/admin/windb/adump
*.control_files='C:\app\oracle\oradata\windb\control01.ctl','C:\app\oracle\fast_recovery_area\windb\control02.ctl'
*.control_files='/u02/app/oracle/oradata/windb/control01.ctl','/u02/app/oracle/oradata/windb/control02.ctl'
mkdir /u02/app/oracle/oradata/windb/
*.db_name='windb'
*.db_name='windb'
*.db_recovery_file_dest='C:\app\oracle\fast_recovery_area\windb'
*.db_recovery_file_dest='/u02/app/oracle/oradata/windb/fast_recovery_area'
mkdir -p /u02/app/oracle/oradata/lindb/fast_recovery_area
*.diagnostic_dest='C:\app\oracle'
*.diagnostic_dest='/u02/app/oracle/oradata/windb/diag'
mkdir -p /u02/app/oracle/oradata/windb/diag
*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=windbXDB)'
*.local_listener='LISTENER_windb'
*.local_listener='LISTENER'
Open the TRANSPORT.SQL and edit the changes corresponding to the directory structure
-------------------------------------------------------------
Mention the pfile location
new redo log files
datafile location
temp file locations
pfile location
Export the new database ORACLE_SID
-------------------------------------------------------------
You will observe the database is an idle instance:-
You can run
@TRANSPORT.SQL
or execute commands step by step
All the database files will be restored as in the source database.
Now run the following scripts
catupgrd.sql
utlirp.sql
utlrp.sql
The complete exercise, right from Installing software to migrating the complete database can be viewed at https://youtu.be/HpOn1rE_d-c
Feel free to comment and correct me if any mistakes.
No comments:
Post a Comment