Migrate a Oracle Database 12.2 on OCI Compute VM

We have oracle database backup pieces from a PROD Database. We need to restore and make it migrate on OCI Compute VM.

As the DB size(50GB) is small and a custom Database I am using manual method this time.

For this I have setup a Compute VM for DB on OCI.

Steps:

1) The compute instance was launched with Oracle linux 7.9 from OCI console.

2) I have attached a 200 GB block storage to have a file system /u01.

pvcreate /dev/sda

Physical volume "/dev/sda" successfully created.

[root@funebscustdb ~]# pvs

PV VG Fmt Attr PSize PFree

/dev/sda lvm2 --- 200.00g 200.00g

[root@funebscustdb ~]# vgcreate vol_db1 /dev/sda

Volume group "vol_db1" successfully created

[root@funebscustdb ~]# vgs

VG #PV #LV #SN Attr VSize VFree

vol_db1 1 0 0 wz--n- <200.00g <200.00g

[root@funebscustdb ~]# vgs

VG #PV #LV #SN Attr VSize VFree

vol_db1 1 0 0 wz--n- <200.00g <200.00g

[root@funebscustdb ~]# lvcreate -n lvol0 -l 100%FREE vol_db1

Logical volume "lvol0" created.

[root@funebscustdb ~]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

lvol0 vol_db1 -wi-a----- <200.00g

[root@funebscustdb ~]# lvdisplay

--- Logical volume ---

LV Path /dev/vol_db1/lvol0

LV Name lvol0

VG Name vol_db1

LV UUID 1pcORW-c6Lv-k4GT-wtZQ-1yFm-KoBy-OfR4M7

LV Write Access read/write

LV Creation host, time funebscustdb, 2023-02-18 03:35:14 +0000

LV Status available

# open 0

LV Size <200.00 GiB

Current LE 51199

Segments 1

Allocation inherit

Read ahead sectors auto

- currently set to 256

Block device 252:0

3) Make a XFS File system

[root@funebscustdb ~]# mkfs.xfs /dev/vol_db1/lvol0

meta-data=/dev/vol_db1/lvol0 isize=256 agcount=4, agsize=13106944 blks

= sectsz=4096 attr=2, projid32bit=1

= crc=0 finobt=0, sparse=0, rmapbt=0

= reflink=0

data = bsize=4096 blocks=52427776, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0, ftype=1

log =internal log bsize=4096 blocks=25599, version=2

= sectsz=4096 sunit=1 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

Discarding blocks…Done.

4) Add entry in /etc/fstab

##Mount for DB

/dev/vol_db1/lvol0 /u01 xfs defaults 0 0

And Mount it

mount -a

5) Install 12c database prerequisites rpms

wget http://public-yum.oracle.com/public-yum-ol7.repo

yum install oracle-rdbms-server-12cR1-preinstall -y

6) Edit Security limits.conf and enter below values

/etc/security/limits.conf:

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

* hard nofile 65536

* soft nofile 4096

* hard nproc 16384

* soft nproc 2047

* hard stack 16384

* soft stack 10240

7) Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

Once the change is complete, restart the server or run the following command.

# setenforce Permissive

Validate using below

# sestatus

SELINUX=permissive

8) Set your inventory location under /etc/oraInst.loc as

vi /etc/oraInst.loc

inventory_loc=/u01/oraInventory

inst_group=dba

From oracle user create directory

mkdir -p /u01/oraInventory

chmod 777 /u01/oraInventory

9) Change the. Timezone on server as required.

Refer below if required:

https://www.funoracleapps.com/2023/01/how-to-change-timezone-on-oci-compute.html

10) Copy the tar backup of oracle home on OCI compute server and

untar in /u01/oracle/db

You can choose oracle home location as per requirements

tar -zxvf 12c_db_home.tar.gz

Now we will need to clone the Oracle home for new server and register in OraInventory

ORACLE_HOME=/u01/oracle/db/12.2.0

Set below parameter based on the ORACLE_HOME

export ORACLE_BASE=/u01/oracle/db

export ORACLE_HOME=/u01/oracle/db/12.2.0

export PATH=${ORACLE_HOME}/bin:$PATH

Executing the clone.pl

perl ${ORACLE_HOME}/clone/bin/clone.pl -silent ORACLE_BASE=/u01/oracle/db ORACLE_HOME=/u01/oracle/db/12.2.0 ORACLE_HOME_NAME=OraDb122_Home1

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 26712 MB Passed

Checking swap space: must be greater than 500 MB. Actual 8191 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2023-02-20_03-12-01PM. Please wait ...You can find the log of this install session at:

/u01/oraInventory/logs/cloneActions2023-02-20_03-12-01PM.log

.................................................. 5% Done.

.................................................. 10% Done.

.................................................. 15% Done.

.................................................. 20% Done.

.................................................. 25% Done.

.................................................. 30% Done.

.................................................. 35% Done.

.................................................. 40% Done.

.................................................. 45% Done.

.................................................. 50% Done.

.................................................. 55% Done.

.................................................. 60% Done.

.................................................. 65% Done.

.................................................. 70% Done.

.................................................. 75% Done.

.................................................. 80% Done.

.................................................. 85% Done.

..........

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.

The cloning of OraDb122_Home1 was successful.

Please check '/u01/oraInventory/logs/cloneActions2023-02-20_03-12-01PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.

.................................................. 95% Done.

As a root user, execute the following script(s):

1. /u01/oracle/db/12.2.0/root.sh

.................................................. 100% Done.

Run /u01/oracle/db/12.2.0/root.sh from root user

11) Set the environment

export ORACLE_BASE=/u01/oracle/db

export ORACLE_HOME=/u01/oracle/db/12.2.0

export PATH=${ORACLE_HOME}/bin:$PATH

export ORACLE_SID=FPROD

12) Change the listener.ora,sqlnet.ora and tnsnames.ora with the new hostname

13) Create directories based on the init file from source

[orafprod@funebscustdb dbs]$ mkdir -p /u01/oracle/db/admin/adump

[orafprod@funebscustdb dbs]$ mkdir -p /u01/oracle/db/oradata

[orafprod@funebscustdb dbs]$ mkdir -p /u01/oracle/db/fast_recovery_area

[orafprod@funebscustdb dbs]$ mkdir -p /u01/oracle/db/archivelogs

14) Startup no mount and no error should be encountered

[orafprod@funebscustdb db]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 20 15:28:21 2023

Connected to an idle instance.

SQL> startup nomount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4.0534E+10 bytes

Fixed Size 8631144 bytes

Variable Size 6308236440 bytes

Database Buffers 3.4091E+10 bytes

Redo Buffers 125583360 bytes

15) Now we will used the backup for restoring the database

All backup pieces we are kept at /u01/backup/ on server

[orafprod@funebscustdb ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 20 23:41:57 2023

connected to target database: FPROD (not mounted)

RMAN> restore controlfile from '/u01/backup/FPROD_control_ml1gfh06_1_1.ctl';

Starting restore at 20-FEB-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2906 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/oracle/db/oradata/control01.ctl

output file name=/u01/oracle/db/fast_recovery_area/control02.ctl

Finished restore at 20-FEB-23

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

16) Validate if backup is good to restore and has all required files. Run below commands to get the output and validate.

RMAN> report schema;

Catalog the backup pieces

RMAN> catalog start with ‘/u01/backup/';

Preview the restore - this will report the backuppieces required for the restore operation. It will not perform the actual restore.

RMAN> restore database preview summary;

17) Restore the Database:

Now I will restore the database

run {

allocate channel ch1 device type disk;

SET NEWNAME FOR DATABASE to '/u01/oracle/db/oradata/%b';

restore database;

switch datafile all;

switch tempfile all;

release channel ch1;

}

Confirm that all datafiles have been restored to the new location:

RMAN> report schema;

18) Recover database

List backup of the archive log

list backup of archivelog all;

Pick the last sequence and recover the database.

RMAN> run

{

allocate channel ch1 device type disk;

recover database until sequence 9901;

release channel ch1;

}

19) Change the redo log location to new location.

Check the current state of the database.It would be mounted and run below query:

SELECT

a.group#,

a.thread#,

a.sequence#,

a.archived,

a.status,

b.member AS redolog_file_name,

( a.bytes / 1024 / 1024 ) AS "Size in MB"

FROM

v$log a

JOIN v$logfile b ON a.group# = b.group#

ORDER BY a.group#;

Relocate all the online redo logs if required:

SQL> select * from v$logfile;

SQL> alter database rename file '<old redo log path and name>' to '<new redo log path and name>';

SQL> ALTER DATABASE RENAME FILE '/u01/orafprod/app/oracle/oradata/redo01.log', '/u01/orafprod/app/oracle/oradata/redo02.log','/u01/orafprod/app/oracle/oradata/redo03.log'

TO '/u01/oracle/db/oradata/redo01.log', '/u01/oracle/db/oradata/redo02.log','/u01/oracle/db/oradata/redo03.log';

20) Open database with reset logs

If you get below error then please clear the below log file.

SQL> alter database open RESETLOGS;

alter database open RESETLOGS

ERROR at line 1:

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 1 thread 1: '/u01/oracle/db/oradata/redo01.log'

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database open RESETLOGS;

Validate the database

select DBID, NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE from v$database;

20) Add below entry in /etc/oratab file

FPROD:/u01/oracle/db/12.2.0:N

21) Allow firewall to connect to DB port via listener

firewall-cmd --zone=public --permanent --add-port=1521/tcp

firewall-cmd —reload

firewall-cmd --state

running

firewall-cmd --get-default-zone

public

firewall-cmd --list-all

22) Delete any services which is not required.

exec DBMS_SERVICE.DELETE_SERVICE( SERVICE_NAME=>’test’);

Now Our Migration is done. We can perform Post steps like the cron scheduling, backup jobs, Reconnect application with new db details etc.

The same approach can be used to DB migration to new server.

If you like please follow and comment

Migrate a Oracle Database 12.2 on OCI Compute VM