Oracle 11g Migration to a New Server

Migration Plan

• Old Oracle server, A: 192.168.221.23 (RHEL 6 x86_64)
• New Oracle server, B: 192.168.221.66 (RHEL 7 x86_64 with better hardware, disk subsystem and processor)
• Database name/Oracle SID: dbmas

The migration plan is basically to copy the database from A to the new server B and take over the old IP address from A. Using this method, we will reduce downtime on the application with zero changes on the application side (the only downtime is to unplug A and bring up the older IP address on B). It also provides the fastest rollback time in case if something goes wrong (just plug in A back to the switch and remove the old IP on B). The steps mentioned below does not include the Oracle 11g installation on B, where you can find a lot of resources online for that particular part.

Preparation (1 hour before migration)

1) A: Check crontab for user oracle and root. Identify all scripts that required to be transferred to B:

$ crontab -l

2) B: Create new IP address interface but do not activate it:

$ vim /etc/sysconfig/network-scripts/ifcfg-eth0:1

3) B: Create new entry of the new IP and comment it inside /etc/hosts:

$ vim /etc/hosts        # make sure shortname first, long name later

4) A & B: Double check all DB paths to be identical on both servers.

5) A: Check /home/oracle directory. Sync all the scripts.

$ ls /home/oracle
$ scp /home/oracle/* 192.168.221.66:/home/oracle/

6) A: Check RMAN script job via Enterprise Manager, bring it to the new server’s Enterprise Manager.

7) A: Create archivelog backup:

$ rman target /
rman> list archivelog all;
rman> backup as compressed backupset tag 'archivelog-103-144' format '/orabak/oradata/dbmas/migration/Archive_%T_%U' archivelog from sequence 103 until sequence 144;

8) A: SCP the archivelog backup to the new server, B:

$ scp /orabak/oradata/dbmas/migration/Archive* 192.168.221.66:/orabak/oradata/dbmas/migration/

9) B: Catalog rman:

rman> catalog start with '/orabak/oradata/dbmas/';

10) B: Recover database:

rman> run
{
allocate channel ch1 device type disk;
recover database until sequence 144;
release channel ch1;
}

11) Compare the archivelog sequence number between A & B:

rman> list archivelog all;

Actual migration (downtime 1 hour)

1) Declare the start of maintenance.

2) A: Stop listener:

$ export ORACLE_SID=dbmas
$ lsnrctl stop

3) A: Clean up all running sessions:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;

4) A: Create migration table for verification called sys.aa_migration_final:

SQL> create table sys.aa_migration_final (description varchar(20),xdate datetime default sysdate not null);
SQL> insert into sys.aa_migration_final (description) values ('migration started');
SQL> select * from sys.aa_migration_final;

5) A & B: Shutdown database:

$ export ORACLE_SID=dbmas
$ sqlplus / as sysdba
SQL> shutdown immediate;

6) A: Copy 2 controlfiles and redo logs to the new server, B:

$ cd /ora02/oradata/dbmas
$ scp control* redo* 192.168.221.66:/ora02/oradata/dbmas/
$ cd /orafra/oradata/dbmas
$ scp control* 192.168.221.66:/orafra/oradata/dbmas/

7) B: Mount database:

SQL> startup mount;

8) B: Recover database:

SQL> recover database;

9) B: Open database:

SQL> alter database open;

10) B: Tune processes, target_memory on the new server:

SQL> alter system set processes=500 scope=spfile;
SQL> alter system set sessions=555 scope=spfile;
SQL> alter system set transactions=610 scope=spfile;
SQL> show parameter memory_target;
SQL> alter system set memory_target = 80G;
SQL> alter system set memory_max_target = 100G scope=spfile;

11) B: Restart database to load the new changes:

SQL> shutdown immediate;
SQL> startup;

12) Unplug old server. This is when the actual downtime starts.

13) B: Set iptables:

iptables -I INPUT -p tcp -s 127.0.0.1 --dport 1521 -m comment --comment "Allow local to Oracle" -j ACCEPT
iptables -I INPUT -p tcp -s 192.168.221.0/24 --dport 1521 -m comment --comment "Allow server's network to Oracle" -j ACCEPT
iptables -I INPUT -p tcp -s 130.2.88.174 --dport 1521 -m comment --comment "Allow PC A to Oracle" -j ACCEPT
iptables -I INPUT -p tcp -s 130.2.51.206 --dport 1521 -m comment --comment "Allow PC B to Oracle" -j ACCEPT
iptables -I INPUT -p tcp -s 0.0.0.0/0 --dport 1521 -m comment --comment "Drop others to Oracle" -j DROP

14) B: Bring up new IP address:

$ nmtui
$ systemctl restart network

15) Try ping and telnet to 1521 from another server/PC just to make sure the old IP 192.168.221.23 is up on B:

$ ping 192.168.221.23
$ telnet 192.168.221.23 1521

16) B: Uncomment/comment new IP address inside /etc/hosts:

$ vim /etc/hosts

17) B: Reboot the server.

$ reboot

18) B: Start listener:

$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status

19) Declare server B is up for testing.

20) B: Perform app testing

21) B: Try to access Enterprise Manager, otherwise, re-configure Enterprise Manager:

Create the EM database user:

alter user DBSNMP identified by ora08928hjpklmn account unlock;
alter user SYS identified by ora08927nncvshgf account unlock;

Delete the old EM path:

rm -rf <ORACLE_HOME>/dbmas
rm -rf <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_oracledb1.myserver.com_dbmas

Inside SQLplus, run the following procedure:

exec sysman.emd_maintenance.remove_em_dbms_jobs;
exec sysman.setEMUserContext('',5);
revoke dba from sysman;
commit;
declare
cursor c1 is
select owner, synonym_name name
from dba_synonyms
where table_owner='SYSMAN'
;
begin
for r1 in c1 loop
if r1.owner='PUBLIC' then
execute immediate 'DROP PUBLIC SYNONYM '||r1.name;
else
execute immediate 'DROP SYNONYM '||r1.owner||'.'||r1.name;
end if;
end loop;
end;
/
drop user mgmt_view cascade;
drop role mgmt_user;
drop user sysman cascade;
commit;
/

Recreate repository and start the DB console:

$ emca -config dbcontrol db -repos create
$ emctl status dbconsole
$ emctl status agent

Try accessing Enterprise Manager from the browser. Use the details generated after the above installation has completed.

22) Declare migration completion.

Post migration checks (est: 1 hour)

1) B: Configure RMAN back up settings:

RMAN> SHOW ALL;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabak/oradata/dbmas/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orabak/oradata/dbmas/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora00/oracle/product/112/dbhome/dbs/snapcf_dbmas.f'; # default

2) B: Crosscheck archivelog:

$ rman target /
rman> crosscheck archivelog all;
rman> delete expired archivelog all;

3) B: Perform a new backup:

rman> backup database plus archivelog;

4) B: Login to Enterprise Manager and check if everything is okay.

5) B: Perform UAT.

At this point, the migration is completed and application can start performing tests.