Migrating Databases to Oracle Cloud Using RMAN Backup

Migrating Oracle Databases to Cloud is not any different than migrating databases to any other Host. Oracle database cloud instance is like any other host running on your data center with few exceptions like they are elastic and scalable in nature. Still I thought it will be a good idea to write few blogs about different database migration methods and provide some clarity around this subject.

This blog will be focus on RMAN backup and restore method for migrating a 12c release 1 database. This method can also be used for migrating 11g databases. This method is useful when you have reasonable migration window and you are migrating from same database and same operating system. Please use following steps to migrate on-premise database to Oracle cloud.

Backup source Database

You should start by analyzing database size and arrange local storage to hold full database backup. I will strongly recommend using compress backup, it will not only help you with storage requirement on local and cloud machine, and it will also reduce time to copy backups to Oracle Cloud instance.

RMAN> run
{
allocate channel c1 type DISK FORMAT '/backup/rman/testdb1/%d_FULL_C_DISK_%T_%s_%p';

allocate channel c2 type DISK FORMAT '/backup/rman/testdb1/%d_FULL_C_DISK_%T_%s_%p';

allocate channel c3 type DISK FORMAT '/backup/rman/testdb1/%d_FULL_C_DISK_%T_%s_%p';

allocate channel c4 type DISK FORMAT '/backup/rman/testdb1/%d_FULL_C_DISK_%T_%s_%p';

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

backup spfile format '/backup/rman/testdb1/spfile_%d_%s_%T_dbid%I.rman';

backup current controlfile format '/backup/rman/testdb1/Controlfile_%d_%T_dbid%I_s%s_p%p';
}
release channel c1;
release channel c2;
release channel c3;
release channel c4;

}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>

Finished backup at 09-FEB-17

Create cloud instance with same dbname

Next you want to create an Oracle cloud instance using same database name, it will create the directory structure and parameter you need to restore your database. IF you are create a 12c instance, it will create a container database by default, so make sure either you are migrating a container database or disable (enable_pluggable_database) parameter in you parameter file.

[oracle@testdb1 ~]$ df -k

Filesystem           1K-blocks     Used Available Use% Mounted on
/dev/xvdb3            26198448 14273624  10571012  58% /
tmpfs                  3698528      388   3698140   1% /dev/shm
/dev/xvdb1              487652   151198    306758  34% /boot
/dev/xvde1            61795324 10430960  48202304  18% /u01
/dev/mapper/dataVolGroup-lvol0
25667900  2968240  21372764  13% /u02
/dev/mapper/fraVolGroup-lvol0
7089656   214848   6491628   4% /u03
/dev/mapper/redoVolGroup-lvol0
26700028   446384  24874316   2% /u04
[oracle@testdb1 ~]$ ps -ef | grep smon
oracle    6296     1  0 16:50 ?        00:00:00 ora_smon_testdb1
oracle   16095 16046  0 19:00 pts/1    00:00:00 grep smon
[oracle@testdb1 ~]$

Copy backup to Oracle Cloud Instance

You can copy full RMAN backup to using scp or winscp uutility. Make sure to sure private key file, if you ssh is not open for public.

[oracle@oraclenode1]$ scp -i /backup/lunix_rsa_cloudkey  =/backup/rman/testdb1/* oracle@129.144.9.60:/u04/rman/testdb1/

Enter passphrase for key '/backup/lunix_rsa_cloudkey':

[oracle@testdb1 testdb1]$ pwd
/u04/rman/testdb1

[oracle@testdb1 testdb1]$ ls -ltr
total 371880
-rw-r--r-- 1 oracle oinstall   1278464 Feb 20 15:54 TESTDB1_FULL_C_DISK_20170220_1_1
-rw-r--r-- 1 oracle oinstall   1097728 Feb 20 15:54 TESTDB1_FULL_C_DISK_20170220_5_1
-rw-r--r-- 1 oracle oinstall     98304 Feb 20 15:54 TESTDB1_FULL_C_DISK_20170220_6_1
-rw-r--r-- 1 oracle oinstall  50708480 Feb 20 15:55 TESTDB1_FULL_C_DISK_20170220_2_1
-rw-r--r-- 1 oracle oinstall 106348544 Feb 20 15:56 TESTDB1_FULL_C_DISK_20170220_3_1
-rw-r--r-- 1 oracle oinstall 211050496 Feb 20 15:56 TESTDB1_FULL_C_DISK_20170220_4_1
-rw-r--r-- 1 oracle oinstall     26112 Feb 20 15:56 TESTDB1_FULL_C_DISK_20170220_7_1
-rw-r--r-- 1 oracle oinstall     98304 Feb 20 15:56 spfile_TESTDB1_8_20170220_dbid688186828.rman
-rw-r--r-- 1 oracle oinstall  10092544 Feb 20 15:56 Controlfile_TESTDB1_20170220_dbid688186828_s9_p1

Backup pfile for recovery

It is important to backup existing parameter file using pfile, you need this file for restore purposes. You can use parameter file from source database but then you will need to make changes according to new cloud environment.

SQL> create pfile='/tmp/inittestdb1.ora' from spfile;

File created.

SQL> exit

Delete testdb1 from cloud Database instance

You can manually delete database or use DBCA to delete database. Please note that it might drop audit file directory and spfile too.

[oracle@testdb1 testdb1]$ ps -ef | grep smon

oracle    6296     1  0 16:50 ?        00:00:00 ora_smon_testdb1
oracle   17876 16046  0 19:05 pts/1    00:00:00 grep smon

[oracle@testdb1 testdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 20 19:05:59 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>  startup mount exclusive restrict

ORACLE instance started.
Total System Global Area 2667577344 bytes
Fixed Size                  2927960 bytes
Variable Size             687866536 bytes
Database Buffers         1962934272 bytes
Redo Buffers               13848576 bytes
Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exit

[oracle@testdb1 testdb1]$ ps -ef | grep smon
oracle   19166 16046  0 19:09 pts/1    00:00:00 grep smon

Startup database in nomount mode

As I mentioned it earlier that dropping database will delete audit file directory too. You need to create audit file directory and restore init.ora file before you can startup you instance for recovery purposes.

[oracle@testdb1 dbs]$ mkdir -p /u01/app/oracle/admin/testdb1/adump
[oracle@testdb1 dbs]$ clear
[oracle@testdb1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 20 16:33:57 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 2667577344 bytes
Fixed Size                  2927960 bytes
Variable Size             687866536 bytes
Database Buffers         1962934272 bytes
Redo Buffers               13848576 bytes

SQL>

Restore control file and mount database

Restore control file using RMAN backup and mount database.

[oracle@testdb1 testdb1]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 20 16:50:26 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB1 (not mounted)

RMAN> RESTORE CONTROLFILE from '/u04/rman/testdb1/Controlfile_TESTDB1_20170220_dbid688186828_s9_p1';


Starting restore at 20-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/app/oracle/oradata/testdb1/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/testdb1/control02.ctl
Finished restore at 20-FEB-17

RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

RMAN>

Catalog backup if location is different

If you have copied backup to different storage structure than source system, you need to either catalog the RMAN backup or create soft link, so controlfile can locate backup pieces.

RMAN> CATALOG START WITH '/u04/rman/testdb1/';

Starting implicit crosscheck backup at 20-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 20-FEB-17
Starting implicit crosscheck copy at 20-FEB-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-FEB-17
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u04/rman/testdb1/
List of Files Unknown to the Database
=====================================
File Name: /u04/rman/testdb1/spfile_TESTDB1_8_20170220_dbid688186828.rman

File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_7_1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_6_1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_4_1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_5_1
File Name: /u04/rman/testdb1/Controlfile_TESTDB1_20170220_dbid688186828_s9_p1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_3_1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_1_1
File Name: /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_2_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

Restore database

Once you have catalog the backup , you can go ahead and restore database files. You will need to use set newname parameter to directory RMAN restore new datafiles location.

RMAN> RUN {
SET NEWNAME FOR DATABASE TO '/u02/app/oracle/oradata/testdb1/%U';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
2> 3> 4> 5>


executing command: SET NEWNAME
Starting restore at 20-FEB-17
using channel ORA_DISK_1
.
.
.
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-FEB-17

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=936464700 file name=/u02/app/oracle/oradata/testdb1/data_D-TESTDB1_TS-SYSTEM_FNO-1
datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=936464700 file name=/u02/app/oracle/oradata/testdb1/data_D-TESTDB1_TS-SYSAUX_FNO-3
datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=936464700 file name=/u02/app/oracle/oradata/testdb1/data_D-TESTDB1_TS-UNDOTBS1_FNO-4
datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=936464700 file name=/u02/app/oracle/oradata/testdb1/data_D-TESTDB1_TS-SALES_FNO-5
datafile 6 switched to datafile copy

input datafile copy RECID=10 STAMP=936464701 file name=/u02/app/oracle/oradata/testdb1/data_D-TESTDB1_TS-USERS_FNO-6

RMAN>

Rename redo file location

Now go ahead and rename redo log file to new storage location, so you can recover your database.

SQL> select member from v$logfile;

MEMBER
-----------------------------------------------------------------------
/oradata/TESTDB1/onlinelog/o1_mf_3_dbb7nobl_.log
/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_3_dbb7nofb_.log
/oradata/TESTDB1/onlinelog/o1_mf_2_dbb7njhw_.log
/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_2_dbb7njnc_.log
/oradata/TESTDB1/onlinelog/o1_mf_1_dbb7ndky_.log
/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_1_dbb7ndpo_.log

SQL> ALTER DATABASE RENAME FILE '/oradata/TESTDB1/onlinelog/o1_mf_3_dbb7nobl_.log' to '/u04/app/oracle/redo/o1_mf_3_dbb7nobl_.log';

ALTER DATABASE RENAME FILE '/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_3_dbb7nofb_.log' to '/u04/app/oracle/redo/o1_mf_3_dbb7nofb_.log';

ALTER DATABASE RENAME FILE '/oradata/TESTDB1/onlinelog/o1_mf_2_dbb7njhw_.log' to '/u04/app/oracle/redo/o1_mf_2_dbb7njhw_.log';

ALTER DATABASE RENAME FILE '/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_2_dbb7njnc_.log' to '/u04/app/oracle/redo/o1_mf_2_dbb7njnc_.log';

ALTER DATABASE RENAME FILE '/oradata/TESTDB1/onlinelog/o1_mf_1_dbb7ndky_.log' to '/u04/app/oracle/redo/o1_mf_1_dbb7ndky_.log';

ALTER DATABASE RENAME FILE '/oradata/fast_recovery_area/TESTDB1/onlinelog/o1_mf_1_dbb7ndpo_.log' to '/u04/app/oracle/redo/o1_mf_1_dbb7ndpo_.log';


Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.

SQL> select member from v$logfile;

MEMBER

-----------------------------------------------------------------------
/u04/app/oracle/redo/o1_mf_3_dbb7nobl_.log
/u04/app/oracle/redo/o1_mf_3_dbb7nofb_.log
/u04/app/oracle/redo/o1_mf_2_dbb7njhw_.log
/u04/app/oracle/redo/o1_mf_2_dbb7njnc_.log
/u04/app/oracle/redo/o1_mf_1_dbb7ndky_.log
/u04/app/oracle/redo/o1_mf_1_dbb7ndpo_.log

6 rows selected.

Recover database and open it with resetlogs

Recover database and once completed, you can open your database with resetlogs.

RMAN> recover database;

Starting recover at 20-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: reading from backup piece /u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_7_1

channel ORA_DISK_1: piece handle=/u04/rman/testdb1/TESTDB1_FULL_C_DISK_20170220_7_1 tag=TAG20170220T155636

channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB1/archivelog/2017_02_20/o1_mf_1_40_dbp8z3ot_.arc thread=1 sequence=40

channel default: deleting archived log(s)

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB1/archivelog/2017_02_20/o1_mf_1_40_dbp8z3ot_.arc RECID=3 STAMP=936465251
unable to find archived log

archived log thread=1 sequence=41
RMAN-03002: failure of recover command at 02/20/2017 17:14:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 41 and starting SCN of 2795088

RMAN> alter database open resetlogs;

Statement processed

Check database instance

Verify your database instance is up and running.

[oracle@testdb1 testdb1]$ ps -ef | grep smon

oracle    6296     1  0 16:50 ?        00:00:00 ora_smon_testdb1
oracle   17876 16046  0 19:05 pts/1    00:00:00 grep smon

[oracle@testdb1 testdb1]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 20 19:05:59 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options