RMAN CONVERT entire database from a source
platform to a destination platform, the endian format of both databases should
be the same..
Convertions can be done on datafile, tablespace and database level Cross-Platform.
- To convert the database from one platform to another, the endian format of both databases should be the same.
set lines 150 pages
300
col PLATFORM_NAME for a35
select * from V$TRANSPORTABLE_PLATFORM order by PLATFORM_ID;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
col PLATFORM_NAME for a35
select * from V$TRANSPORTABLE_PLATFORM order by PLATFORM_ID;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
Checking the source server
platform_name
select platform_id,platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- -------------------------------------
13 Linux x86 64-bit
Checking the source Target platform_name
select platform_id,platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- -------------------------------------
12 Microsoft Windows x86 64-bit
select platform_id,platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- -------------------------------------
13 Linux x86 64-bit
Checking the source Target platform_name
select platform_id,platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- -------------------------------------
12 Microsoft Windows x86 64-bit
Check that
endian format of the target platform i.e. "Microsoft Windows x86 64-bit" is also little.
Hence
database can be transported
Database should be in read only
mode to convert
SQL>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2215064 bytes
Variable Size 171967336 bytes
Database Buffers 356515840 bytes
Redo Buffers 3764224 bytes
Database mounted.
SQL>alter database open read only;
Database altered.
Verifying the database can be transportable or not with the below script:-
- If nothing was returned, then it means that the database is ready to be transported to the destination platform.
SQL> set serveroutput on
declare
v_return boolean;
begin
v_return:=dbms_tdb.check_db('Microsoft Windows x86 64-bit');
end;
/
PL/SQL procedure successfully completed.
To verify the objects will not be created on the transported database.
SQL> declare
v_return boolean;
begin
v_return:=dbms_tdb.check_external;
end;
/
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR
PL/SQL procedure successfully completed.
Note: SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR
These objects cannot be created on the transported database
Details of the RMAN script
- convert database new database 'TTSDB' ➤ Specify any name for the new database name
- transport script '/u02/oracle/ttsdb/transport.sql' ➤ This script is used to create the database automatically on the target server //
- to platform 'Microsoft Windows x86 64-bit' ➤ Specifying the target platfom name
- db_file_name_convert ➤ In the transport.sql script it will give with the target locations.
Run the RMAN convert database command:-
Converting the whole database to the Windows platform.
RMAN> convert database new database 'TTSDB'
transport script '/u02/oracle/ttsdb/transport.sql'
to platform 'Microsoft Windows x86 64-bit'
FORMAT '/u02/oracle/ttsdb/ttsdb'
db_file_name_convert '/u01/app/oracle/oradata/veena/' '/u02/oracle/ttsdb/';
Starting conversion at source at 13-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.XMLDIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u01/app/oracle/oradata/sai/system01.dbf
converted datafile=/u02/oracle/ttsdb/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/u01/app/oracle/oradata/sai/sysaux01.dbf
converted datafile=/u02/oracle/ttsdb/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/u01/app/oracle/oradata/sai/undotbs01.dbf
converted datafile=/u02/oracle/ttsdb/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/u01/app/oracle/oradata/sai/users01.dbf
converted datafile=/u02/oracle/ttsdb/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /u02/oracle/ttsdb/init_ttsdb.ora. This PFILE will be used to create the database on the target platform
Run SQL script /u02/oracle/ttsdb/transport.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 13-JAN-18
These are below Files which are created By the above RMAN script in the target location
[oracle@sainath ttsdb]$ ls -rlt
total 1203248
-rw-r-----. 1 oracle oinstall 702554112 Jan 13 20:34 system01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jan 13 20:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Jan 13 20:34 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 13 20:34 users01.dbf
-rw-r--r--. 1 oracle oinstall 1339 Jan 13 20:34 init_ttsdb.ora
-rw-r--r--. 1 oracle oinstall 2381 Jan 13 20:34 transport.sql
[oracle@sainath ttsdb]$
Please edit the init_ttsdb.ora parameter path values as per target server directories.
vi init_ttsdb.ora
# Please change the values of the following parameters:
control_files = "E:\win_12c\oracle\oradata\ttsdb\control01.ctl"
db_recovery_file_dest = "E:\win_12c\oracle\flash_recovery_area"
db_recovery_file_dest_size= 4070572032
audit_file_dest = "E:\win_12c\oracle\admin\ttsdb\adump"
db_name = "TTSDB"
Please edit the transport.sql path values as per target server directories.
vi transport.sql
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT PFILE='E:\win_12c\oracle\12.1.0\dbhome_1\dbs\init_ttsdb.ora'
CREATE CONTROLFILE REUSE SET DATABASE "TTSDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512,
GROUP 2 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512,
GROUP 3 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512
DATAFILE
'E:\win_12c\oracle\oradata\ttsdb\system01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\sysaux01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\undotbs01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\win_12c\oracle\oradata\ttsdb\temp01.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='E:\win_12c\oracle\12.1.0\dbhome_1\dbs\init_ttsdb.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='E:\win_12c\oracle\oradata\ttsdb\init_ttsdb.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
Make sure to create the necessary Directories in the target Windows Server.
mkdir E:\win_12c\oracle\oradata\ttsdb
mkdir E:\win_12c\oracle\flash_recovery_area
mkdir E:\win_12c\oracle\admin\ttsdb\adump
Copy below Files to the target Windows Server
[oracle@sainath ttsdb]$ ls -rlt
total 1203248
-rw-r-----. 1 oracle oinstall 702554112 Jan 13 20:34 system01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jan 13 20:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Jan 13 20:34 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 13 20:34 users01.dbf
-rw-r--r--. 1 oracle oinstall 1339 Jan 13 20:34 init_ttsdb.ora
-rw-r--r--. 1 oracle oinstall 2381 Jan 13 20:34 transport.sql
[oracle@sainath ttsdb]$
SQL>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2215064 bytes
Variable Size 171967336 bytes
Database Buffers 356515840 bytes
Redo Buffers 3764224 bytes
Database mounted.
SQL>alter database open read only;
Database altered.
Verifying the database can be transportable or not with the below script:-
- If nothing was returned, then it means that the database is ready to be transported to the destination platform.
SQL> set serveroutput on
declare
v_return boolean;
begin
v_return:=dbms_tdb.check_db('Microsoft Windows x86 64-bit');
end;
/
PL/SQL procedure successfully completed.
To verify the objects will not be created on the transported database.
SQL> declare
v_return boolean;
begin
v_return:=dbms_tdb.check_external;
end;
/
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR
PL/SQL procedure successfully completed.
Note: SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR
These objects cannot be created on the transported database
Details of the RMAN script
- convert database new database 'TTSDB' ➤ Specify any name for the new database name
- transport script '/u02/oracle/ttsdb/transport.sql' ➤ This script is used to create the database automatically on the target server //
- to platform 'Microsoft Windows x86 64-bit' ➤ Specifying the target platfom name
- db_file_name_convert ➤ In the transport.sql script it will give with the target locations.
Run the RMAN convert database command:-
Converting the whole database to the Windows platform.
RMAN> convert database new database 'TTSDB'
transport script '/u02/oracle/ttsdb/transport.sql'
to platform 'Microsoft Windows x86 64-bit'
FORMAT '/u02/oracle/ttsdb/ttsdb'
db_file_name_convert '/u01/app/oracle/oradata/veena/' '/u02/oracle/ttsdb/';
Starting conversion at source at 13-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.XMLDIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u01/app/oracle/oradata/sai/system01.dbf
converted datafile=/u02/oracle/ttsdb/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/u01/app/oracle/oradata/sai/sysaux01.dbf
converted datafile=/u02/oracle/ttsdb/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/u01/app/oracle/oradata/sai/undotbs01.dbf
converted datafile=/u02/oracle/ttsdb/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/u01/app/oracle/oradata/sai/users01.dbf
converted datafile=/u02/oracle/ttsdb/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /u02/oracle/ttsdb/init_ttsdb.ora. This PFILE will be used to create the database on the target platform
Run SQL script /u02/oracle/ttsdb/transport.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 13-JAN-18
These are below Files which are created By the above RMAN script in the target location
[oracle@sainath ttsdb]$ ls -rlt
total 1203248
-rw-r-----. 1 oracle oinstall 702554112 Jan 13 20:34 system01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jan 13 20:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Jan 13 20:34 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 13 20:34 users01.dbf
-rw-r--r--. 1 oracle oinstall 1339 Jan 13 20:34 init_ttsdb.ora
-rw-r--r--. 1 oracle oinstall 2381 Jan 13 20:34 transport.sql
[oracle@sainath ttsdb]$
Please edit the init_ttsdb.ora parameter path values as per target server directories.
vi init_ttsdb.ora
# Please change the values of the following parameters:
control_files = "E:\win_12c\oracle\oradata\ttsdb\control01.ctl"
db_recovery_file_dest = "E:\win_12c\oracle\flash_recovery_area"
db_recovery_file_dest_size= 4070572032
audit_file_dest = "E:\win_12c\oracle\admin\ttsdb\adump"
db_name = "TTSDB"
Please edit the transport.sql path values as per target server directories.
vi transport.sql
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT PFILE='E:\win_12c\oracle\12.1.0\dbhome_1\dbs\init_ttsdb.ora'
CREATE CONTROLFILE REUSE SET DATABASE "TTSDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512,
GROUP 2 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512,
GROUP 3 'E:\win_12c\oracle\oradata\ttsdb' SIZE 50M BLOCKSIZE 512
DATAFILE
'E:\win_12c\oracle\oradata\ttsdb\system01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\sysaux01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\undotbs01.dbf',
'E:\win_12c\oracle\oradata\ttsdb\users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\win_12c\oracle\oradata\ttsdb\temp01.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='E:\win_12c\oracle\12.1.0\dbhome_1\dbs\init_ttsdb.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='E:\win_12c\oracle\oradata\ttsdb\init_ttsdb.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
Make sure to create the necessary Directories in the target Windows Server.
mkdir E:\win_12c\oracle\oradata\ttsdb
mkdir E:\win_12c\oracle\flash_recovery_area
mkdir E:\win_12c\oracle\admin\ttsdb\adump
Copy below Files to the target Windows Server
[oracle@sainath ttsdb]$ ls -rlt
total 1203248
-rw-r-----. 1 oracle oinstall 702554112 Jan 13 20:34 system01.dbf
-rw-r-----. 1 oracle oinstall 492838912 Jan 13 20:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 31465472 Jan 13 20:34 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 13 20:34 users01.dbf
-rw-r--r--. 1 oracle oinstall 1339 Jan 13 20:34 init_ttsdb.ora
-rw-r--r--. 1 oracle oinstall 2381 Jan 13 20:34 transport.sql
[oracle@sainath ttsdb]$
Copying the Pfile in $ORACLE_HOME/dbs location
C:\Users\Admin>copy E:\win_12c\oracle\oradata\ttsdb\init_ttsdb.ora E:\win_12c\oracle\12.1.0\dbhome_1\dbs
1 file(s) copied.
Target Windows Server :—
Create a Newdb service
c:\>oradim -new -sid ttsdb
Run the Transport Script:
c:\>set oracle_sid=ttsdb
sqlplus / as sysdba
SQL>@E:\win_12c\oracle\oradata\ttsdb\transport.sql
Verify the database has been transported —
SQL>select * from dba_tables;
Done:——
C:\Users\Admin>copy E:\win_12c\oracle\oradata\ttsdb\init_ttsdb.ora E:\win_12c\oracle\12.1.0\dbhome_1\dbs
1 file(s) copied.
Target Windows Server :—
Create a Newdb service
c:\>oradim -new -sid ttsdb
Run the Transport Script:
c:\>set oracle_sid=ttsdb
sqlplus / as sysdba
SQL>@E:\win_12c\oracle\oradata\ttsdb\transport.sql
Verify the database has been transported —
SQL>select * from dba_tables;
Done:——
No comments:
Post a Comment