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/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


Copyright (c) 1982, 2016, Oracle.  All rights reserved.


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


Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


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