Migrating On-Premises Oracle Databases to Google Cloud Platform: A Windows to Linux Transformation

Syed Raheel August 11, 2024 APPS DBA Leave a comment 16 Views

Step by step Migration of On-Premises Oracle Databases to Google Cloud Platform: A Windows to Linux Transformation

/u01/db/12.1.0/dbs/initprodDB.ora

alter database create standby controlfile as ‘G:\RMAN_BACKUPS\STDBY_11_DEC_2024_EVENING.ctl’;

/u01/proddb/db/data

=========CREATE A TEST TABLE ON SOURCE PROD ======

create table TEST_TABLE2 (name varchar2(30));
insert into TEST_TABLE2 values (’11DEC2024 12:13PM DATA’);
commit;

===============================

MUST CREATE LATEST STANDBY CTL FILE BEFORE STARTING ACTIVITY

alter database create standby controlfile as ‘G:\RMAN_BACKUPS\STDBY_23_DEC_2024_MORNING.ctl’;

sqlplus / as sysdba

startup nomount pfile=’/u01/prodDB/db/12.1.0/dbs/initprodDB .ora’;

rman target /

restore controlfile from ‘/MiscFiles/RMAN_BKP_21DEC24/STDBY_23_DEC_2024_MORNING.CTL’;

alter database mount;

catalog start with ‘/MiscFiles/RMAN_BKP_21DEC24’;

catalog start with ‘/u01/stuff/RMAN_BKP_11DEC_5AM’;

======================

select ‘set newname for datafile ‘||file#||’ to ‘||CHR(39)||replace(name,’G:\APP\ORACLE\ORADATA\prodDB\’,’/u01/prodDB/db/data/’)||CHR(39)||’;’ a
from v$datafile where name like ‘%ORADATA%’
union all
select ‘set newname for datafile ‘||file#||’ to ‘||CHR(39)||replace(name,’G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\’,’/u01/prodDB/db/data/’)||CHR(39)||’;’ a
from v$datafile where name like ‘%DBHOME_1%’;

===================

restore datafile 1;

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
allocate channel c9 device type disk;
allocate channel c10 device type disk;
set newname for datafile 1 to ‘/u01/prodDB/db/data/SYSTEM01.DBF’;
set newname for datafile 3 to ‘/u01/prodDB/db/data/SYSAUX01.DBF’;
set newname for datafile 5 to ‘/u01/prodDB/db/data/UNDOTBS01.DBF’;
set newname for datafile 6 to ‘/u01/prodDB/db/data/USERS01.DBF’;
set newname for datafile 25 to ‘/u01/prodDB/db/data/UNDOTBS02.DBF’;
set newname for datafile 26 to ‘/u01/prodDB/db/data/UNDOTBS03.DBF’;
set newname for datafile 7 to ‘/u01/prodDB/db/data/prodDATA .DBF’;
set newname for datafile 8 to ‘/u01/prodDB/db/data/MAXINDEX.DBF’;
set newname for datafile 9 to ‘/u01/prodDB/db/data/prodDATA 02.DBF’;
set newname for datafile 10 to ‘/u01/prodDB/db/data/prodDATA 03.DBF’;
set newname for datafile 11 to ‘/u01/prodDB/db/data/prodDATA 04.DBF’;
set newname for datafile 12 to ‘/u01/prodDB/db/data/prodDATA 05.DBF’;
set newname for datafile 13 to ‘/u01/prodDB/db/data/prodDATA 06.DBF’;
set newname for datafile 14 to ‘/u01/prodDB/db/data/prodDATA 07.DBF’;
set newname for datafile 15 to ‘/u01/prodDB/db/data/prodINDEX02.DBF’;
set newname for datafile 16 to ‘/u01/prodDB/db/data/prodINDEX03.DBF’;
set newname for datafile 17 to ‘/u01/prodDB/db/data/prodINDEX04.DBF’;
set newname for datafile 18 to ‘/u01/prodDB/db/data/prodINDEX05.DBF’;
set newname for datafile 19 to ‘/u01/prodDB/db/data/prodINDEX06.DBF’;
set newname for datafile 20 to ‘/u01/prodDB/db/data/prodINDEX07.DBF’;
set newname for datafile 21 to ‘/u01/prodDB/db/data/prodDATA 08.DBF’;
set newname for datafile 22 to ‘/u01/prodDB/db/data/prodDATA 09.DBF’;
set newname for datafile 23 to ‘/u01/prodDB/db/data/prodDATA 10.DBF’;
set newname for datafile 24 to ‘/u01/prodDB/db/data/prodDATA 11.DBF’;
set newname for datafile 27 to ‘/u01/prodDB/db/data/prodDATA 12.DBF’;
set newname for datafile 28 to ‘/u01/prodDB/db/data/prodDATA 13.DBF’;
set newname for datafile 29 to ‘/u01/prodDB/db/data/prodDATA 14.DBF’;
set newname for datafile 30 to ‘/u01/prodDB/db/data/prodDATA 15.DBF’;
set newname for datafile 31 to ‘/u01/prodDB/db/data/prodDATA 16.DBF’;
set newname for datafile 32 to ‘/u01/prodDB/db/data/prodDATA 17.DBF’;
set newname for datafile 33 to ‘/u01/prodDB/db/data/prodDATA 18.DBF’;
set newname for datafile 34 to ‘/u01/prodDB/db/data/prodDATA 19.DBF’;
set newname for datafile 35 to ‘/u01/prodDB/db/data/prodDATA 20.DBF’;
set newname for datafile 36 to ‘/u01/prodDB/db/data/prodDATA 21.DBF’;
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
}

============

catalog start with ‘/MiscFiles/ALG_21DECSTART_TILL_23DECMORNING’;

=======================

cd /u01/stuff/prodDB_ARCHIVELOGS

cd /MiscFiles/ALG_21DECSTART_TILL_23DECMORNING

cp -p * /u01/prodDB/db/archives

====================

sqlplus / as sysdba
startup nomount pfile=’/u01/prodDB/db/12.1.0/dbs/initprodDB.ora’;
alter database mount;

============================

sqlplus / as sysdba

recover standby database;

AUTO

FINAL

recover standby database;

CANCEL

================

Let’s try to open the database, but you need to convert the controlfile from standby to primary first.

SQL> alter database activate standby database;

Database altered.

SQL> alter database open;

At this point, please proceed to change all the redo log files and temp files location as I’ve shown previously in this article.

==========================================

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

SELECT # FROM V$LOGFILE;

ALTER DATABASE ADD LOGFILE GROUP 4 (‘/u01/prodDB/db/data/LOG04A.DBF’,’/u01/prodDB/db/data/LOG04B.DBF’) SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 5 (‘/u01/prodDB/db/data/LOG05A.DBF’,’/u01/prodDB/db/data/LOG05B.DBF’) SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 6 (‘/u01/prodDB/db/data/LOG06A.DBF’,’/u01/prodDB/db/data/LOG06B.DBF’) SIZE 512M;

============================
alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system checkpoint;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 3;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

select member from v$logfile;

===========
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

alter system switch logfile;

ALTER DATABASE DROP LOGFILE GROUP 3;

alter system switch logfile;

alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

alter system checkpoint;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

========================

alter system switch logfile;

alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

alter system checkpoint;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

===================================

select tablespace_name from dba_tablespaces;

select name from v$tempfile;

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_TEMP_TABLESPACE’;

===========================

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/prodDB/db/data/TEMP_0001.dbf’ size 4096M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

select name from v$tempfile;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_TEMP_TABLESPACE’;

============================
ALTER TABLESPACE MAXTEMP ADD TEMPFILE ‘/u01/prodDB/db/data/MAXTEMP_001.dbf’ size 4096M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

=======DROP WINDOWS REDO FILE=================
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP02.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP03.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP04.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP05.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\ORADATA\prodDB\TEMP01.DBF’ DROP INCLUDING DATAFILES;

ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\ORADATA\prodDB\TEMP022024.DBF’ DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE ‘G:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DBS\prodTEMP06.DBF’ DROP INCLUDING DATAFILES;

select name from v$tempfile;

================

Shutdown immediate
Startup upgrade

select count() from all_objects where status=’INVALID’; @$ORACLE_HOME/rdbms/admin/utlrp.sql select count() from all_objects where status=’INVALID’;

Shutdown immediate
Startup

select count() from all_objects where status=’INVALID’; @$ORACLE_HOME/rdbms/admin/utlrp.sql select count() from all_objects where status=’INVALID’;

SELECT patch_id, version, status, bundle_id, bundle_series FROM dba_registry_sqlpatch;

======================
select count(*) from all_objects where status=’INVALID’;

42 After utlrp

=================

create spfile from pfile;

alter system set shared_pool_size=512M scope=spfile;
alter system set java_pool_size=512M scope=spfile;

================

— Start of File rmcorejvm.sql
connect / as sysdba
spool rmcorejvm.log
set echo on
set serveroutput on
select owner, status, count(*) from all_objects
where object_type like ‘%JAVA%’ group by owner, status;
execute rmjvm.run(false);
shutdown immediate
set echo off
spool off
exit
— End of File rmcorejvm.sql

====================
.
— Start of File corejvminst.sql
connect / as sysdba
spool corejvminst.log
set serveroutput on
set echo on
startup mount
alter system set “_system_trig_enabled” = false scope=memory;
alter database open;
select owner, status, count(*) from all_objects
where object_type like ‘%JAVA%’ group by owner, status;
create or replace java system
/
shutdown immediate
set echo off
spool off
exit
— End of File corejvminst.sql

==========================================

  1. Restart the database and resolve any invalid java objects
    connect / as sysdba
    startup
    select owner, status, count(*) from all_objects where object_type like ‘%JAVA%’ group by owner, status;

select count() from all_objects where status=’INVALID’; @$ORACLE_HOME/rdbms/admin/utlrp.sql select count() from all_objects where status=’INVALID’;

=============================================

select target || ‘ – ‘ || sofar || ‘ out of ‘ || totalwork from v$session_longops where totalwork !=sofar;

select count(*) from all_objects where status=’INVALID’;

=================================

Followed by a check for Invalid(s):

select owner, status, count(*) from all_objects where object_type like ‘%JAVA%’ group by owner, status;

=========================
cd $ORACLE_HOME/OPatch
./datapatch -verbose

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba

select count() from all_objects where status=’INVALID’; @$ORACLE_HOME/rdbms/admin/utlrp.sql select count() from all_objects where status=’INVALID’;

rman catalog system/system

create catalog;

UPGRADE CATALOG;

recovery catalog owner is SYSTEM
enter UPGRADE CATALOG command again to confirm catalog upgrade

UPGRADE CATALOG;

recovery catalog upgraded to version 19.12.00.00.00
DBMS_RCVMAN package upgraded to version 19.12.00.00
DBMS_RCVCAT package upgraded to version 19.12.00.00.

RMAN>

recovery catalog upgraded to version 19.12.00.00.00
DBMS_RCVMAN package upgraded to version 19.12.00.00
DBMS_RCVCAT package upgraded to version 19.12.00.00.

sqlplus / as sysdba

alter user sys identified by sys;

==========

SHUTDOWN IMMEDIATE
STARTUP MOUNT

alter database archivelog;
startup mount
ALTER DATABASE OPEN RESETLOGS; <<<<<<<<<<< IMPORTANT

=============================================

FINAL CHECKS:

Connect TOAD via system/system

  1. CHECK DB LINKS
  2. CHECK DIRECTORIES UPDATED TO CORRECT PATH

=========================================

=============FINAL LINE OF ACTION START =========

mkdir -p /u01/prodDB/db/12CBASE/EXPDMP
mkdir -p /u01/prodDB/db/12CBASE/dpdump
mkdir -p /u01/prodDB/db/12CBASE/backup

DROP DIRECTORY XSDDIR;
CREATE OR REPLACE DIRECTORY XSDDIR AS ‘/u01/prodDB/db/12.1.0/rdbms/xml/schema’;

DROP DIRECTORY XMLDIR;
CREATE OR REPLACE DIRECTORY XMLDIR AS ‘/u01/prodDB/db/12.1.0/rdbms/xml’;

DROP DIRECTORY OPATCH_SCRIPT_DIR;

CREATE OR REPLACE DIRECTORY OPATCH_SCRIPT_DIR AS ‘/u01/prodDB/db/12.1.0/QOpatch’;

DROP DIRECTORY OPATCH_LOG_DIR;
CREATE OR REPLACE DIRECTORY OPATCH_LOG_DIR AS ‘/u01/prodDB/db/12.1.0/QOpatch’;

DROP DIRECTORY OPATCH_INST_DIR;

CREATE OR REPLACE DIRECTORY OPATCH_INST_DIR AS ‘/u01/prodDB/db/12.1.0/OPatch’;

DROP DIRECTORY EXPDMP;

CREATE OR REPLACE DIRECTORY EXPDMP AS ‘/u01/prodDB/db/12CBASE/EXPDMP’;
GRANT READ, WRITE ON DIRECTORY SYS.EXPDMP TO SYSTEM;

DROP DIRECTORY DATA_PUMP_DIR;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS ‘/u01/prodDB/db/12CBASE/dpdump’;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO IMP_FULL_DATABASE;

DROP DIRECTORY BACKUP;
CREATE OR REPLACE DIRECTORY BACKUP AS ‘/u01/prodDB/db/12CBASE/backup’;
GRANT READ, WRITE ON DIRECTORY SYS.BACKUP TO MAXIMO;

=====================

connect system/system

DROP PUBLIC DATABASE LINK “PRODORA.US.ORACLE.COM”;

CREATE PUBLIC DATABASE LINK “PRODORA.US.ORACLE.COM” CONNECT TO PRODORA IDENTIFIED BY prodoraimo USING ‘192.168.1.1:1538/ORA8017’;

DROP DIRECTORY ORACLECLRDIR;
CREATE OR REPLACE DIRECTORY ORACLECLRDIR AS ‘/u01/prodDB/db/12CBASE/clr_state’;

DROP DIRECTORY ORACLE_OCM_CONFIG_DIR2;
CREATE OR REPLACE DIRECTORY ORACLE_OCM_CONFIG_DIR2 AS ‘/u01/prodDB/db/12CBASE/clr_state’;

DROP DIRECTORY ORACLE_OCM_CONFIG_DIR;
CREATE OR REPLACE DIRECTORY ORACLE_OCM_CONFIG_DIR AS ‘/u01/prodDB/db/12CBASE/clr_state’;

=============FINAL LINE OF ACTION ENDED ABOVE =========

run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
backup as compressed backupset incremental level 0 tag hot_db_bk_level0 filesperset 30 format ‘/u01/RMAN_BKP_TEST/DTF_%d_%u.F_bkp’ (database);
sql ‘alter system archive log current’;
crosscheck archivelog all;
backup as compressed backupset format ‘/u01/RMAN_BKP_TEST/ALG_%d_%u.alg’ archivelog all delete input;
backup as copy current controlfile format ‘/u01/RMAN_BKP_TEST/CTL_%d_%u.ctl’;
RELEASE CHANNEL d1;
RELEASE CHANNEL d2;
RELEASE CHANNEL d3;
RELEASE CHANNEL d4;
RELEASE CHANNEL d5;
}

=========== STANDBY DATABASE CREATION =========

orapwd file=/u01/app/oracle/12.1.0.2/db_1/dbs/orapwcdb1 password=Password entries=10

run
{
duplicate target database for standby from active database dorecover NOFILENAMECHECK;
}

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 (‘/u01/prodDB/db/data/standby_redo01.log’) SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8 (‘/u01/prodDB/db/data/standby_redo02.log’) SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 (‘/u01/prodDB/db/data/standby_redo03.log’) SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 (‘/u01/prodDB/db/data/standby_redo04.log’) SIZE 512M;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/MiscFiles/prodDBSBY_IN_LINUX.CTL’;

/u01/prodDB/db/12.1.0/bin/orapwd file=/u01/prodDB/db/12.1.0/dbs/orapwprodDB password=sysrdbms entries=10

rman TARGET sys/sys AUXILIARY sys/sys SBY;

===============================

About Syed Raheel

I have total of 17 years and more than 15 years experience in Oracle ERP E-Business Suite R11i , R12.1.3 , 12.2.4 to 12.2.11 with Oracle 10g/11g/12c and 19c with Red Hat / Oracle Enterprise Linux Environments. I am Working as Oracle EBS APPS Database Consultant in Advanced Operations Technology, A Saudi Arabian IT/ERP Consulting firm, I am responsible for New Technical Implementations, Upgradation, Migrations and Providing 24x7 support for critical ERP Production Application & Databases. Specialties: Oracle Application Oracle E-Business Suite R12 Oracle Applications System Administration Oracle Applications Database Administration

Check Also

AKS_Cover

Restrict Applications Users To Be Signed In

How Can I Restrict Applications Users To Be Signed In Only Once At Any Time …

Leave a Reply

Powered by OracleSolutions | Designed by
© Copyright 2026, https://oraclesolutions.pk All Rights Reserved