Navigation Bar

Sunday, July 23, 2023

To backup your database on a linux machine and restore it on a windows machine

 The below post is a guide to export your database on linux and import it on a windows machine.

SELECT PLATFORM_NAME FROM  V$DATABASE;
Linux x86 64-bit
SELECT PLATFORM_ID || ' | ' ||  PLATFORM_NAME || ' | ' ||  ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX IA%';
10 | Linux IA (32-bit) | Little
11 | Linux IA (64-bit) | Little

SELECT PLATFORM_ID || ' | ' || PLATFORM_NAME || ' | ' || ENDIAN_FORMAT
FROM   V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE '%MICROSOFT WINDOWS%';
7 | Microsoft Windows IA (32-bit) | Little 
8 | Microsoft Windows IA (64-bit) | Little
12 | Microsoft Windows x86 64-bit | Little

Start the database on Linux OS to be backed up in READ ONLY mode

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;
Verify the database is ready for migration
SET  SERVEROUTPUT ON;
DECLARE
      db_ready BOOLEAN;
    BEGIN
      db_ready :=
         SYS.DBMS_TDB.CHECK_DB('Microsoft Windows IA (64-bit)',SYS.DBMS_TDB.SKIP_READONLY);
      if db_ready
          then
             dbms_output.put_line('YES your database can be transported to Windows platform');
      else
         dbms_output.put_line('NO your database can not be transported to WIndows Platform');
      end if;
end;
/
If you get an error such as Database is not open in READ-ONLY mode, open the database in READ-ONLY mode and retry. Now your database can be transported to Windows Platform. Open the database in READ-ONLY mode as shown above. Make a folder on your database server to store all converted datafiles to be restored.
$mkdir /home/oracle/convertdb
Run the CONVERT DATABASE command as follows
CONVERT DATABASE
NEW DATABASE 'UAT'
TRANSPORT SCRIPT '/backup/dbbackup/convertdb/transport.sql'
TO PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '/backup/dbbackup/convertdb/uat'
DB_FILE_NAME_CONVERT '/u01/app/oracle/oradata/UAT/' '/backup/dbbackup/convertdb/';
This command will 
>convert all datafiles to the platform 'Microsoft Windows 64 bit' 
>create a pfile 
>create a transport.sql file which will have the commands to 
 --start the database with a new pfile --create the control file 
 --open the database. 
Edit the pfile to indicate the new path for the control file, db_recovery_file_dest and audit_file_dest. Edit the transport.sql file to indicate the new path for 
> pfile 
> datafiles 
> redolog files 
> TEMP file for temporary tablespace 
On the new server install the database software with the required settings. 
Consider the DB_HOME as
D:\app\Administrator\product\11.2.0\dbhome_1
D:\ordata\orasid
Copy all the datafiles and redolog files to the new path in 'D:\oradata\orasid'. 
Copy the modified pfile to the path 'D:\app\Administrator\product\11.2.0\dbhome_1\database' 
Register the new instance in the database with the below command.
oradim -new -sid orasid
Brief steps from transport.sql
startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\init_orasid.ora'
Execute the create control file with modified paths for redolog files and the datafiles.
alter database open resetlogs;
Add a temp file with the command 'ALTER DATABASE TEMP ADD TEMPFILE ...'
SHUTDOWN IMMEDIATE
startup upgrade pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\init_orasid.ora'
@@?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
startup pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\init_orasid.ora'
@@ ?/rdbms/admin/utlrp.sql
Steps for restoring a cold backup when platform for both the databases is the same.
select file_name from dba_data_files;
select member from v$logfile;
select file_name from dba_temp_files;
Take a copy of the control file to a suitable directory. 
Alter database backup control file to trace as 'C:\temp\orasid_ctl.txt' 
Modify this file to indicate the new locations for the redo log files, datafiles and temp files. 
This script is to be executed on the new db server in no mount mode to create the control file. 
Take a copy of the pfile to be copied to the new database server.
SQL>create pfile ='D:\temp\initSID.ora'
Modify this file to indicate the new directory locations for the control file, diagnostic dest, db name etc. Copy this file to the new destination on the new server. Ensure the database is properly shut down with the shutdown immediate command.
SQL> shutdown immediate;
Copy the datafiles, redolog files and temp files to the new directory locations on the new server on which the database is to be restored. 
On the new database server install the database software with the required settings. 
Register the new instance in the database with the below command.
oradim -new -sid orasid
startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\init_orasid.ora';
Execute the create control file command (created in step above) with the modified paths for redolog files and datafiles.
alter database open;
In this case there is no need to open the database with resetlogs of upgrade option. 

No comments:

Post a Comment