oracle database migration steps Wednesday, October 15, 2014

Migrate Oracle Database Windows to Linux

Convert Oracle Database Windows to Linux

Open oracle database SQL prompt from the command line for checking the current OS Version

Finding Supported platforms (Need to connect to SQLPLUS)

sqlplus “/ as sysdba”

SQL> select * from v$transportable_platform order by 2;

Checking the Number of Objects in our database

Query is

Select count(*) from dba_objects;

As we can see our source database has 79885 objects

Note: We will compare it After the Migration process completion

Verifying the SOURCE database is ready for migration

Database Name: testdb

Platform Name: Microsoft Windows IA (32-bit)

Note: We will shutdown the database first and open it in read-only mode

Shutdown immediate;

Startup mount;

Alter database open read only;

Now below command will make it ready for migration

Set serveroutput on;

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_db('Linux x86 64-bit');

End;

PL/SQL procedure successfully completed. (See below Window);

Check and See if External objects are present

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_external;

End;

PL/SQL procedure successfully completed.

The following directories exist in the database we will use for this activity:

SYS.DATA_PUMP_DIR

We are not using it here in this exercise (We will Skip this Step)

Open RMAN prompt for Starting Conversion

Commands to Run (Windows CMD)

rman target /

Converting Database or Preparing for our Destination OS

Command

convert database new database 'testdb' transport script 'C:\Users\kamran\testdb\trasnport.sql' db_file_name_convert 'D:\app\oradata\testdb\testdb' 'C:\Users\kamran\testdb';

Now over transport.sql script is ready, so we will move forward to the Target instance and complete the migration process

Now create Pfile from SPfile on Source database Windows Server using below

Command

create pfile='C:\Users\kamran\testdb\init_testdb.ora' from spfile;

Copy Source datafile from c:\users\kamran\testdb to Destination Linux using Winscp

Destination Server Settings (Linux Server 64 Bit)

Name pmstestug

Version el6uek.x86_64

Create following directories

mkdir -p /d02/app/oracle/flash_recovery_area/testdb

mkdir -p /d02/app/oracle/oradata/testdb

mkdir -p /d02/admin/TESTDB/pfile

mkdir -p /d02/admin/TESTDB/adump

mkdir -p /d02/admin/TESTDB/dbdump

Edit init_testdb.ora

Change destination path as per the below image

Executing TRANSPORT.SQL Script on destination

Conclusion

We have successfully migrated our Windows 32 Bit Oracle 11g Database to Linux 64Bit

Thanks for your time and patience.

Natural DBA

script Wednesday, October 15, 2014

11g Cold Backup Step By Step

Create a directory to copy your backup-related files to. In this example, we will be using c:\oracle\orabackup\orcl\cold.

2. Log into your database using SQL*Plus. C:\oracle\orabackup\orcl>sqlplus sys as sysdba

3. Using the DBA_DATA_FILES view, determine the data files that you will need to back up.

SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME

--------------- ----------------------------------------

USERS C:\ORACLE\ORADATA\ORCL\USERS01.DBF

UNDOTBS1 C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

SYSAUX C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

SYSTEM C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

4. Using the V$LOGFILE view, determine the online redo logs that will require a backup.

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER

----------------------------------

C:\ORACLE\ORADATA\ORCL\REDO03.LOG

C:\ORACLE\ORADATA\ORCL\REDO02.LOG

C:\ORACLE\ORADATA\ORCL\REDO01.LOG

5. Using the V$CONTROLFILE view, determine the location of the database control files that will be backed up.

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME

-------------------------------------

C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL

C:\ORACLE\ORADATA\ORCL\CONTROL02.CTL

C:\ORACLE\ORADATA\ORCL\CONTROL03.CTL

6. From the SQL*Plus prompt, shut down the database below SQL

SQL> SHUTDOWN IMMEDIATE;

7. Once the database is shut down, exit SQL*Plus.

SQL> exit

C:\oracle\orabackup\orcl>

8. Using the OS Copy command, copy the database data files, control files, and online redo logs to the backup directory created in step 1. In our example, they are all in the same directory, so this is easy.

C:\oracle\orabackup\orcl>Copy c:\oracle\oradata\orcl\*.*

9. Start SQL*Plus connecting as sys as sysdba. Restart the database with the startup command. You have completed your backup.

C:\oracle\orabackup\orcl> SQLPLUS SYS AS SYSDBA

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Aug 14 19:31:56 2008

Enter password:

Connected to an idle instance.

SQL> STARTUP

ORACLE instance started.

Total System Global Area 397557760 bytes

Fixed Size 1333452 bytes

Variable Size 268437300 bytes

Database Buffers 121634816 bytes