Breaking News

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

Syed Raheel August 11, 2024 APPS DBA Leave a comment 6 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

HTTP Service Issue After Sudden Power Shutdow

Fixing HTTP Service Issue After Sudden Power Shutdown of Oracle EBS 12.2 Application Server   …

Leave a Reply

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