Blog

Oracle Database Migration to Exadata – Case Study

Objective:

This report will show the detailed steps that we followed to migrate a real client production database from its current production server to the Exadata machine.

Environment:

  Source Target
Server Name prod-n-214 exa-db-101
Database Name PROD PROD
Oracle version 11.2.0.3 11.2.0.3
O.S Linux OEL 6.5 Exadata OEL 6.8
Character set WE8ISO8859P1 WE8ISO8859P1
Clustered: Single Instance Single Instance

Migration High Level Plan:

  • Migration Preparation.
  • Backup the production database.
  • Duplicate the production database to Exadata.
  • Post Migration steps
  • Validate the migration.
  • Application specific setup.

 

Migration Preparation: Source Database Preparation:

  • Review the current production database PROD’s alert.log file for any issues.
  • Collect detailed information about PROD (Tablespaces, Users, Tables, Segments, Database Links, etc.)
  • Compile invalid objects (if any).
  • Decide the media/location that will be used to hold the Production database backup.{Database will be duplicated from the latest production backup on the EMC storage}

Migration Preparation: Exadata Preparation

  • Ensure that Oracle 11.2.0.3 binaries were already installed and that it uses RDS protocol (Note that in our case 11.2.0.3 is an additional home that was installed after the standard ACS installation).

/u01/app/oracle/product/11.2.0.4/dbhome/bin/skgxpinfo -v

You should get ==> Oracle RDS/IP (generic)

If you get ==>  Oracle RDS/IP (generic) , then you have to relink the home as        follows:

          make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ipc_rds ioracle

  • It is recommended to use RDS on Exadata as it uses IB network which provides greater bandwidth

{There is a bug between 12c GI & 11.2.0.3 with RDS (Bug: 16844086)}

Pre-Migration Preparation:

The following steps should be followed during the Go-Live (not the test migration):

  • <CUSTOMER> should submit a request to the firewall team to open the any required ports (ports: 1521, 8081 & 1648) è This should be done early enough before the Go-Live date
  • Agree with all the stakeholders on the Go-Live Date/Time.
  • <CUSTOMER> to inform all concerned parties with the Go-Live Date/Time (Network, Backup, Security, Application teams).

Migration Approach:

We are going to use a physical migration approach (database duplicate), as the logical migration approach (export/Import) has some limitations with the Oracle XML database. PROD application is heavily using the Oracle XML database.

Backup the Production database

rman target /

shutdown immediate;

startup mount;

sql ‘alter system enable restricted session’;

alter database open;

sql ‘alter system checkpoint’;

sql ‘alter system switch logfile’;

sql ‘alter system archive log current’;

shutdown immediate;

startup mount;

{Leave the database in the mount mode (to prevent any new transactions while migrating the database}

RUN {

ALLOCATE CHANNEL CH1  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH2  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH3  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH4  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

 

BACKUP INCREMENTAL  LEVEL 0

FILESPERSET 10

FORMAT ‘%d_%u_%s_%p

DATABASE

INCLUDE CURRENT CONTROLFILE;

BACKUP filesperset 10 archivelog all;

BACKUP CURRENT CONTROLFILE;

}

 

Duplicate the production database to Exadata

Preparation: Listener Configuration

Add the following static entry to the listener.ora file (so as we can to connect to the database from rman while being in the shutdown mode):

 

Login as “grid” user.

Set the environment for the grid user

. oraenv

+ASM1

As grid user edit the Listener.ora on the new Exadata server:

1

$ lsnrctl reload

$ lsnrctl stat

Preparation: tnsnames.ora Configuration

Add the following TNS entry for both the source database and new database (Edit the  tnsnames.ora on the new Exadata server)

As “oracle” user go to $ORACLE_HOME/network/admin on the target server and edit tnsnames.ora   as follows (PROD214.world is the source entry, PROD.worls is the Exadata entry):

2

Preparation: init.ora

Create a parameter file for the target database under $ORACLE_HOME/dbs with the name initPROD.ora and add the following lines ,save and exit:

*.audit_file_dest=’/u01/app/oracle/admin/PROD/adump’

*.audit_trail=’DB’

*.compatible=’11.2.0.3.0′

*.control_files=’+DATA_ED01/PROD/CONTROLFILE/ctl1.ctl’,’+RECO_ED01/PROD/CONTROLFILE/ctl2.ctl’

*.db_block_size=8192

*.db_create_file_dest=’+DATA_ED01′

*.db_domain=’WORLD’

*.db_name=’PROD’

*.db_recovery_file_dest=’+RECO_ED01′

*.db_recovery_file_dest_size=322122547200

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)’

*.open_cursors=500

*.processes=500

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=10737418240

*.pga_aggregate_target=5368709120

*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS1′

Preparation: dump directories

mkdir –p /u01/app/oracle/admin/PROD/adump

Preparation: password file

Under directory ($ORACLE_HOME\dbs) on target Exadata server , create password file via orapwd utility (or you may copy the source password file):

Login to the source server prod-n-214

cd $ORACLE_HOME/dbs

scp orapwPROD oracle@exa-db-101:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs

Preparation: Startup the new Database

Startup the new database in nomount state on target Exadata server (101):

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

export ORACLE_SID=PROD

export PATH=$PATH:$ORACLE_HOME/bin

sqlplus / as sysdba

sql> startup nomount;

3

Database Duplication

Run RMAN connecting to both source database (PROD214) as a target and new database (PROD) as auxiliary

export NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”

rman target orabkup/xxxx@PROD214.world auxiliary orabkup/xxxx@PROD.world

4

On the source system (mounted database):

sqlplus / as sysdba

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select * from (

select NEXT_CHANGE#,FIRST_TIME,NEXT_TIME from v$archived_log order by 1 desc )

where rownum <2;

On the Exadata server, run the duplicate command as follows and set until time clause in case you need to set duplicate to a specific point in time (Make sure no spaces in parms syntax):

run {

set until scn 423041993;

allocate auxiliary channel ch1 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch2 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch3 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch4 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

duplicate target database to PROD pfile ‘/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initPROD.oranofilenamecheck; }

Make sure RMAN finished the Duplicate command with no errors

Once RMAN finished, the new database PROD should be up and running.

Post Migration Steps

Create spfile

Exit RMAN and create spfile for the duplicated database:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=PROD
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus / as sysdba
sql> create spfile='+DATA_ED01' from pfile;

This will create spfile at:
+DATA_ED01/PROD/PARAMETERFILE/spfile.527.938200795

now :
cd $ORACLE_HOME/dbs
mv initPROD.ora initPROD.ora.org
vi initPROD.ora
   spfile='+DATA_ED02/DTSDEV/PARAMETERFILE/spfile.527.938200795'

Register database with the cluster

On the terminal issue the following to add database to Oracle Restart

$ srvctl add database -d PROD -n PROD -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -c SINGLE -a DATA_ED01,RECO_ED01 -p '+DATA_ED01/PROD/PARAMETERFILE/spfile.527.938200795' -i PROD -x exa-dbadm-101
$ srvctl stop database -d PROD
$ srvctl start database -d PROD
$ srvctl status database -d PROD
$ srvctl config database -d PROD

Full Database Backup

Ask the backup team to take a full cold backup for the newly migrated database before going live.

Distribute the new client TNS Entry

Below is the TNS entry that should be used by the clients:
PROD.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exa-dbvip-101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.WORLD)
    )
  )

 

RMAN Backup Setup

Modify/Change any relevant RMAN settings:

RMAN> Show all;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

SQL> alter system set control_file_record_keep_time=31 scope=both;

 

Migration Validation

  • Ensure that there is no errors reported out from the “Duplicate” operation.
  • Make sure new Database become registered with Listener (lsnrctl status).
  • Ensure that you can connect to the database and it is in the “Read Write” mode:

56

 

  • Ensure that all datafiles are online:

Select name, status from v$datafile;

  • Review the new database alert.log file for any errors:

  /u01/app/oracle/diag/rdbms/PROD/PROD/trace/alert_PROD.log

  • Check Invalid objects
  • Restart the database
  • Test database connection from server and from clients

 

Appendix A: Duplicate Command Output

[oracle@exa-dbadm-101 dbs]$ rman target sys/xxxx@PROD202.world auxiliary sys/xxxx@PROD.world

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 9 18:53:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=3964826197, not open)
connected to auxiliary database: PROD (not mounted)

RMAN> run {
set until scn 423041993;
allocate auxiliary channel ch1 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch2 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch3 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch4 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
duplicate target database to PROD pfile '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initPROD.ora' nofilenamecheck;
}

2> 3> 4> 5> 6> 7> 8>

executing command: SET until clause
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=685 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=703 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=721 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=739 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

Starting Duplicate Db at 09-MAR-17
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:

{
   shutdown clone immediate;
   startup clone nomount;
}

executing Memory Script

Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1644169904 bytes
Database Buffers            9026142208 bytes
Redo Buffers                  16924672 bytes

allocated channel: ch1
channel ch1: SID=667 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=685 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=703 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=721 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

contents of Memory Script:
{
   set until scn  423041993;
   sql clone "alter system set  db_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down
Oracle instance started

Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1644169904 bytes
Database Buffers            9026142208 bytes
Redo Buffers                  16924672 bytes

allocated channel: ch1
channel ch1: SID=667 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=685 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=703 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=721 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

Starting restore at 09-MAR-17
channel ch1: starting datafile backup set restore
channel ch1: restoring control file
channel ch1: reading from backup piece PROD_05runeku_5_1
channel ch1: piece handle=PROD_05runeku_5_1 tag=TAG20170309T174708
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
output file name=+DATA_ED02/PROD/controlfile/ctl1.ctl
output file name=+RECO_ED02/PROD/controlfile/ctl2.ctl
Finished restore at 09-MAR-17

database mounted

contents of Memory Script:
{
   set until scn  423041993;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;

   restore
   clone database
   ;
}

executing Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 09-MAR-17
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to +DATA_ED02
channel ch1: restoring datafile 00006 to +DATA_ED02
channel ch1: restoring datafile 00012 to +DATA_ED02
channel ch1: restoring datafile 00016 to +DATA_ED02
channel ch1: reading from backup piece PROD_02rune8s_2_1
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00001 to +DATA_ED02
channel ch2: restoring datafile 00009 to +DATA_ED02
channel ch2: restoring datafile 00011 to +DATA_ED02
channel ch2: restoring datafile 00014 to +DATA_ED02
channel ch2: reading from backup piece PROD_03rune8s_3_1
channel ch3: starting datafile backup set restore
channel ch3: specifying datafile(s) to restore from backup set
channel ch3: restoring datafile 00002 to +DATA_ED02
channel ch3: restoring datafile 00007 to +DATA_ED02
channel ch3: restoring datafile 00008 to +DATA_ED02
channel ch3: restoring datafile 00013 to +DATA_ED02
channel ch3: reading from backup piece PROD_01rune8s_1_1
channel ch4: starting datafile backup set restore
channel ch4: specifying datafile(s) to restore from backup set
channel ch4: restoring datafile 00004 to +DATA_ED02
channel ch4: restoring datafile 00005 to +DATA_ED02
channel ch4: restoring datafile 00010 to +DATA_ED02
channel ch4: restoring datafile 00015 to +DATA_ED02
channel ch4: reading from backup piece PROD_04rune8s_4_1
channel ch1: piece handle=PROD_02rune8s_2_1 tag=TAG20170309T174708
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:08:05
channel ch3: piece handle=PROD_01rune8s_1_1 tag=TAG20170309T174708
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:10:25
channel ch2: piece handle=PROD_03rune8s_3_1 tag=TAG20170309T174708
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:11:15
channel ch4: piece handle=PROD_04rune8s_4_1 tag=TAG20170309T174708
channel ch4: restored backup piece 1
channel ch4: restore complete, elapsed time: 00:12:00
Finished restore at 09-MAR-17

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=32 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/system.534.938199899
datafile 2 switched to datafile copy
input datafile copy RECID=33 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843
datafile 3 switched to datafile copy
input datafile copy RECID=34 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707
datafile 4 switched to datafile copy
input datafile copy RECID=35 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/users.533.938199945
datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.546.938199239
datafile 6 switched to datafile copy
input datafile copy RECID=37 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.548.938199239
datafile 7 switched to datafile copy
input datafile copy RECID=38 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.547.938199239
datafile 8 switched to datafile copy
input datafile copy RECID=39 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.544.938199441
datafile 9 switched to datafile copy
input datafile copy RECID=40 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.542.938199499
datafile 10 switched to datafile copy
input datafile copy RECID=41 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.541.938199529
datafile 11 switched to datafile copy
input datafile copy RECID=42 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.545.938199239
datafile 12 switched to datafile copy
input datafile copy RECID=43 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.543.938199449
datafile 13 switched to datafile copy
input datafile copy RECID=44 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.539.938199641
datafile 14 switched to datafile copy
input datafile copy RECID=45 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.537.938199711
datafile 15 switched to datafile copy
input datafile copy RECID=46 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.536.938199759
datafile 16 switched to datafile copy
input datafile copy RECID=47 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.540.938199639

contents of Memory Script:
{
   set until scn  423041993;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause

Starting recover at 09-MAR-17
starting media recovery
archived log for thread 1 with sequence 131 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_131.23318.938197719
archived log for thread 1 with sequence 132 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_132.23319.938197719
archived log for thread 1 with sequence 133 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_133.23320.938197719
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_131.23318.938197719 thread=1 sequence=131
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_132.23319.938197719 thread=1 sequence=132
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_133.23320.938197719 thread=1 sequence=133
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-MAR-17

Oracle instance started
Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1610615472 bytes
Database Buffers            9059696640 bytes
Redo Buffers                  16924672 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     4674
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  '+DATA_ED02/PROD/datafile/system.534.938199899'
 CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA_ED02/PROD/datafile/undotbs1.535.938199843",
 "+DATA_ED02/PROD/datafile/sysaux.538.938199707",
 "+DATA_ED02/PROD/datafile/users.533.938199945",
 "+DATA_ED02/PROD/datafile/proddata.546.938199239",
 "+DATA_ED02/PROD/datafile/proddata.548.938199239",
 "+DATA_ED02/PROD/datafile/proddata.547.938199239",
 "+DATA_ED02/PROD/datafile/proddata.544.938199441",
 "+DATA_ED02/PROD/datafile/proddata.542.938199499",
 "+DATA_ED02/PROD/datafile/proddata.541.938199529",
 "+DATA_ED02/PROD/datafile/proddata.545.938199239",
 "+DATA_ED02/PROD/datafile/proddata.543.938199449",
 "+DATA_ED02/PROD/datafile/proddata.539.938199641",
 "+DATA_ED02/PROD/datafile/proddata.537.938199711",
 "+DATA_ED02/PROD/datafile/proddata.536.938199759",
 "+DATA_ED02/PROD/datafile/proddata.540.938199639";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_ED02 in control file
renamed tempfile 2 to +DATA_ED02 in control file

cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843 RECID=1 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707 RECID=2 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/users.533.938199945 RECID=3 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.546.938199239 RECID=4 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.548.938199239 RECID=5 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.547.938199239 RECID=6 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.544.938199441 RECID=7 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.542.938199499 RECID=8 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.541.938199529 RECID=9 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.545.938199239 RECID=10 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.543.938199449 RECID=11 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.539.938199641 RECID=12 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.537.938199711 RECID=13 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.536.938199759 RECID=14 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.540.938199639 RECID=15 STAMP=938199972

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/users.533.938199945
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.546.938199239
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.548.938199239
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.547.938199239
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.544.938199441
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.542.938199499
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.541.938199529
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.545.938199239
datafile 12 switched to datafile copy
input datafile copy RECID=11 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.543.938199449
datafile 13 switched to datafile copy
input datafile copy RECID=12 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.539.938199641
datafile 14 switched to datafile copy
input datafile copy RECID=13 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.537.938199711
datafile 15 switched to datafile copy
input datafile copy RECID=14 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.536.938199759
datafile 16 switched to datafile copy
input datafile copy RECID=15 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.540.938199639

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 09-MAR-17



 

How to change the Apex Application from LTR to RTL?

If you are developing Arabic application using Oracle Apex and want to change the the direction of the whole application from LTR(Left-To_Right) to RTL (Right-To-Left) , heer are the quick steps:

 

1- Select the application Select Shared Components.

1

then select “Globalization”

23

Then set the “Document Direction” attribute to “Right-To-Left”

That is all!

Ransomware in Arabic فيروس دفع الفدية بالعربي

بالنسبة للناس إللي إتصابت فعلا بفيروس الكومبيوتر اللي منتشر اليومين دول, يا تري هل هناك أى فرصة لإسترجاع ملفاتهم؟

الإجابة نعم قد تكون هناك فرصة للبعض (وليس للكل).

عشان نفهم الإجابة لازم نفهم الأول هو الفيروس ده بيعمل إيه?

الفيروس ده إسمه  Ransomeware (WannaCry)  ومعناه بالعربى دفع الفدية.

الفيروس بيقوم بتشفيرملفاتك الشخصية وبيطلب منك دفع مبلغ 300 دولار لفك التشفير.

اذا لم تقم بالدفغ خلال 3 أيام بيقوم برفع المبلغ الى 600 دولار واذا لم تدفع بعد 7 أيام  فلا يمكن إستعادة ملفاتك بعد كده أبدأ لأنهم ببساطة بيقوموا بمسح “مفتاح التشفير” من عندهم..

الفيروس ده أصاب عشرات الألاف من الأجهزة سواء الشخصية أو الشركات على مستوى العالم كله.

النقطة المهمة اللي لازم نفهمها فى طريقة عمل الفيروس ده هى أنه بيعمل نسخة جديدة مشفرة من ملفاتك ويقوم بمسح النسخة الأصلية. بالنسبة للنسخة المشفرة فهذه لا يوجد طريقة لإسترجاعها (حتي  الأن). وعلي فكرة حتي لو دفعت مش هترجع لأن هناك مشكلة فنية فى عملية إسترجاع البيانات بعد الدفع.

طيب بالنسبة للنسخة الأصلية اللي تم مسحها هل يمكن إسترجاعها؟ الإجابة نعم فى بعض الحالات:

بالنسبة للملفات اللي موجودة علي فلاشة أو ديسك خارجي أو موجودة فى ال Desktop  أو Documents  فاللأسف لا يمكن إسترجاعها لأنه بيتم مسحها بطريقة “عميقة”.

بالنسبة للملفات الأصلية الممسوحة فى الأماكن الأخرى (الديسكات الداخلية) فده بيتم مسحها بطريقة عادية (أمر delete) وبالتالي فهناك فرصة كبيرة جدا لإسترجاعها بأى برنامج مجاني بيعمل undelete

طبعا الوقاية خير من العلاج, ودى بعض النصائح العامة:

إحتفظ بنسخ إضافية من ملفاتك المهمة علي ديسكات خارج جهازك أو علي الأنترنت.

إستعمل برنامج مقاوم فيروسات Antivirus

نزل أخر تحديثات للويندوز.

لو تقدر تسيبك من الويندو خالص و تشتغل علي اللينكس يبقى أحسن بكتير!.

Ransomware

sqlcl Multi-Line Paste Issue

What is the issue?

I noticed that sometimes when I copy SQL statement from web and paste it on sqlcl , it is copied on many lines and generating errors.

For example, when I copied the following statement:

1

And pasted into sqlcl running on the windows command window, it was pasted as follows:

2

So, it was copied as three separate statements instead of one statement, which led to the errors shows above.

 

If you paste the same above statement on the sql*plus running on the same windows command window, it will accept it as one statement, like:

3

So, what is reason behind that?

After a lot of research and after discussing it with Jeff Smith and getting inputs from “TR Rudkin”:

http://www.thatjeffsmith.com/ask-a-question/comment-page-3/#comment-131653

I found the following interesting remarks:

If I compares the statement with the above issue with another statement that is working fine, I found that both are having a different end of lines special characters.

For the statement that is working without the above issue, the end of line is: “CRLF” which is Carriage Return + Line Feed (Hexa  codes: 0D0A).

4

For the statement that is having the above paste issue, the end of line is: “LF” which is Line Feed (Hexa  codes: 0A).

5

I guess the reason behind this issue may be the source of the statement. Some statements may be formatted with Unix end of line format while others are formatted for Widnows.

It worth mentioning that sqlplus absorbs this difference without issue, but sqlcl error out with lines that end up with LF only.

Also, it worth mentioning that if you just copy/paste the code that has this issue into a text editor like “Notepad++’, it will automatically adjust the end of lines to CRLF, then if you copied that code from Notepad++, it will work fine.

Oracle tnsnames.ora Distribution Management System!

What?

Does tnsnames.ora file need a Distribution Management System?

In our case, the answer is yes. It is a headache to implement our client requirements without some sort of automation.

What is tnsnames.ora file?

tnsnames.ora is a text file that resides on the ORACLE_HOME/network/admin and contains some simple information about our databases connection information, it is like:

1

What is problem that we need to fix?

Our client has two production Exadata machines (total database nodes =5= two separate clusters), plus a normal linux server for DR, so we have total of 6 database nodes.

Following is the high level architecture:

2

There are many databases consolidated in these servers (about 40 databases), and these databases have many database links referring to other non-Exadata databases.

In each database server, our client has 3 database homes for: 12c,11.2.0.4 & 11.2.0.3.

Each database home contains its own tnsnames.ora file related to the databases resides on this home. So, we have 18 tnsnames.ora file across all the database servers/homes.

Due to the huge number of connection strings (about 150 tns entries and keep increasing each day with consolidation,the tnsnames.ora file is currently about 1500 line) and the difficulty of mapping each TNS entry to which database/home/node/cluster, so, our client decided to create only one master tnsnames.ora file that contains all connection strings across all database nodes/database homes and maintain only this file.

Now, the main points we want to address are:

  • How will we create the consolidated master tnsnames.ora?
  • How will we validate the consolidated tnsnames.ora has no typo errors?
  • Where we will keep this master tnsnames.ora file?
  • If there is a change in the master tnsnames.ora file (e.g adding/deleting/modifying new entries), then how will we distribute the new file across all nodes/homes without much manual intervention?

The solution that I will present below is my solution with my colleague “Moustafa Hassan”, but I’m quite sure that there is another better solutions/options.

1-     Creating the Consolidated Master tnsnames.ora file

Actually, this step is a little bit manual. We simply copied the tnsnames .ora file for each database home from all nodes/homes into one big master file, then manually we started to search about any repetition and remove it.

This is simply done using some powerful editors like “Notepad ++ or sublime or even vi”.

Please note that in the current situation, each database home has its own tnsnames.ora file which may be different or partially similar to that of other homes.

Now, we have one consolidated file contains all TNS entries from all local tnsnames.ora files at all nodes/homes.

2-     Validating the Consolidated Master tnsnames.ora file

Because we were afraid of having any typo errors while preparing the master tnsnames.ora file, so, we thought of a way to validate this file. The best option is to use the “tnsping” utility to ping each entry in this file and ensure a successful ping. Doing this manually for about 150 entry takes time, so, we wrote the following shell script command to do it for us:

for t in `egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1`

echo “=========”

do echo $t;

echo “=========”

tnsping $t

done | tee tnsping.out

Simply this shell command/script is parsing the tnsnames.ora file for lines that are not starting with space (i.e lines that contain the connect string names).

Let us take the following tnsnames.ora as an example:

3a

So, the first line in our script will return the names of the connection strings, like:

egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1

4

Then it will pass this set of tns entries to the “tnsping” utility and then spool the output to a file, while seeing it on the screen also.

This will produce the following output:

=========

XPS

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS)))

OK (20 msec)

=========

XPS10

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS10)))

OK (0 msec)

=========

XPS20

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS20)))

OK (0 msec)

=========

RAC

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-cluster-scan.rac.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS.rac.com)))

TNS-12535: TNS:operation timed out

=========

mcwb

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.97.58.45)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mcwb)))

TNS-12543: TNS:destination host unreachable

=========

SPROD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = insightproddb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = SPROD)))

TNS-12545: Connect failed because target host or object does not exist

=========

PROD.WORLD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:52

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = afattah-lap)(PORT = 1524))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))

TNS-12545: Connect failed because target host or object does not exist

Doing that, it will be very simple to catch any wrong tns entries and fix them before going forward. Also, this command enable you to find out any outdated entries that reference severs or databases that are no longer exist.

 

3-     Location of the Consolidated Master tnsnames.ora file

As we have three different environment. Two Exaxdata machines and another standalone DR server, the question was where we should locate our master file?.

Some options:

  • For each machine put the master tnsnames.ora file in any location and then adjust the TNS_ADMIN for all oracle homes to point to that location.
  • Another option which is similar to the above one, but with using a symbolic link under the oracle home to point to the master file.
  • One option was to create a NFS mount point that is seen from all servers and putting the master file there and adjusting the TNS_ADMIN also.
  • The last option that I chose was to keep the master file in one server offline, and when you need to update it, just update it in that server then run a script to distribute it to all servers/homes (you need to validate it before distribution as we did above). Also, the script will backup the existent local tnsnames.ora files under each ORACL_HOME before replacing them.

 

4-     Maintaining the Consolidated Master tnsnames.ora file

 

Now, assume that we have the master tnsnames.ora file at server1 (db_node1) with name “Master_tnsnames_Exadata_v1.ora” and we want to modify it and add new entries, the procedure will be as follows:
1- Copy the Master_tnsnames_Exadata_v1.ora to Master_tnsnames_Exadata_v2.ora
2- Edit the new file “Master_tnsnames_Exadata_v2.ora”
3- Add the new entries
4- Run the following script distribute_tnsnames.sh to complete the mission:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The above script will deploy the new master file across all servers and database homes.

Note that you will need to setup a trust ssh link between the server that contains the master tnsnames.ora file “db_node1) and all the other servers, so, you will not be prompted to enter the password for each server.

This is simply done by copying the entry in the “id_rsa.pub” file in the master server, to the “authorized_keys” file in all other servers. Please note that these files exist under /home/oracle/.ssh directory.

5-     Distributing the Consolidated Master tnsnames.ora file

The following shell script “distribute_tnsnames.sh” will take care of backing up the current local tnsnames.ora file files before replacing them with the new master tnsnames.ora file.

This script accepts only one parameter which is the name of the new master file.

Usage should be as follows:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The source code of this script is listed below:

#!/bin/bash

script_dir=`dirname $0`

script_name=`basename $0`

if [ $# -ne 1 ]; then

echo “Usage: $script_name {master_tns_file_nama}”

exit 1

fi

master_file=$1

if [ ! -f $master_file ]; then

echo “Error: master file: $script_dir/$master_file not found”

exit 2

fi

for node in `cat $script_dir/db_nodes | egrep -v “^#”`; do

if [ $node != “db_node1″ ]; then

scp -p /home/oracle/master_tns/$master_file $node:/home/oracle/master_tns/

fi

done

OH3=”/u01/app/oracle/product/11.2.0.3/dbhome_1″

OH4=”/u01/app/oracle/product/11.2.0.4/dbhome_1″

OH12=”/u01/app/oracle/product/12.1.0/dbhome_1″

TNS3=$OH3″/network/admin”

TNS4=$OH4″/network/admin”

TNS12=$OH12″/network/admin”

# Backup current tnsnames.ora

# ===========================

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS3/tnsnames.ora $TNS3/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS4/tnsnames.ora $TNS4/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS12/tnsnames.ora $TNS12/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

# Distribute the new tnsnames.ora

# ===============================

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS3/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS4/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS12/tnsnames.ora”

We are using the “scp” command to copy the new master tnsnames.ora file from the first node to a specific directory “/home/oracle/master_tns/” across all servers.

The dcli is a powerful utility available with Exadata, that enables you to run the same command across all nodes (or subset of nodes).

We are simply using the “dcli” utility to do two things:

  • Backup the current local tnsnames.ora file under each oracle home at all servers.
  • Replace the current local tnsnames.ora file under each oracle home at all servers with the new master consolidated tnsnames.ora file.

The db_nodes file is a simple test file that contains the names of the database servers, it is like:

5

Also, if you want to partially distribute the new master file to subset of the above nodes, you can simply comment the un-wanted servers, like:

6

Caution:

Don’t use this script before validating the new master tnsnames.ora!

Use this procedure at your own risk!.

Oracle Database 32-bit Funny Migration to 64-bit

Objective:

Migrating one 11.1.0.7 32-bit database that resides on an old Windows 2008 (32-bit) (4GB memory) to Windows 2008 64-bit (no upgrade) on a brand new server with 64GB memory.

{At 2017 and still there is 11.1.0.7 database ==> yes there is!}

 

Introduction and Background

I have a client with the following very old environment:

O.S.:                      Windows 2008 32-bit

Oracle:                  11.1.0.7 32-bit

This environment was initially installed at 2002 (with Oracle 901) and then it went with a couple of upgrades until they reached 11.1.0.7, then they stopped upgrade, why?

This was actually because of the application vendor closed completely and disappeared !!

Since that date, client decided to stop any further database upgrades to avoid any issues with the application (as there is no application support going forward).

In parallel, he started to develop a new application with a new vendor with the latest technologies, but they didn’t finish yet.

Until the new application is completed, he wants to keep the old system running as it is!.

But, given the increased workload and data volumes and the 32-bit memory limitations ( < 4GB), he started to suffer heavily from the memory shortage.

So, to work around the memory issue, he decided to move the database from 32-bit platform to 64-bit platform without upgrade to minimize any impact on the old application.

So, it looks like a simple migration, let us see!

 

Which Windows Version?:

Ok, as we are going to move to another new powerful server, so, let us move to the latest Windows version available. Let us go with Windows 2012 R2. This is fine, but wait, as Oracle 11.1.0.7 is not certified with Windows 2012, actually there was no Windows 2012 at that time!.

Ok, no problem so let us go with Windows 2008 R2 64-bit.

  • Unfortunately, Oracle 11.1.0.7 is not certified with this version, it is certified only with Windows 2008 64-bit (without R2)!.

Ok, go ahead and install Windows 2008 (without R2) on the new server and start the migration.

No, wait please, the new hardware is not compatible with the Windows 2008 , it is only compatible with Windows 2012 !!!!

Ok, no problem, let us install Windows 2012 R2 Enterprise Edition (64-bit) on the new server and then create a Virtual Machine on top of it with Windows 2008 64-bit , as a workaround.

So, I used the Windows Hyper-V virtualization technology to implement this and created a new Windows 2008 64-bit virtual machine.

So far so good, let us move on.!

Where is the Database Software?

Client has only 11.1.0.6 (+ 11.2.0.7 patchset) 32-bit, he doesn’t have the 64-bit version.

Ok, no problem, go ahead and download it from OTN or edelivery ==>  unfortunately, Oracle already removed these old versions from its sites!.

The only way to get them, is to send Oracle a request and they will send you a physical DVD with the required software. The other option is to ask one of your Oracle friends to download it for you (on friendly basis) from Oracle Internal sites {ok thanks for my friend!]

For Windows 2008 64-bit, we are lucky, as we have 11.1.0.7 full version that can be installed directly. For any other Windows 64-bit platform, you have to install 11.1.0.6 first and then patch it to 11.1.0.7.

Install Hyper-V

123456789101112

 

Create new virtual Machine

131415161718192021222324252627282930

Disable Firewall.

Assign  static IP

Oracle 11.1.0.7 64-bit Installation

313233343536373839

 

Database Migration

Assumptions:

  • Same data file directory structure
  • Same characterset
  • Physical – as it is – migration from 11.1.0.7 32-bit (Windows 2003 32-bit) to 11.1.0.7 64-bit (Windows 2008 Enterprise Edition 64-bit).

Note that the new server version OS version is Windows 2008 Enterprise Edition 64-bit, not Windows 2008 Enterprise Edition 64-bit R2 ( as Oracle 11.1.0.7 is not certified with R2)

Migration Procedure:

  • Shutdown the source PIS database (take it offline in case of Fail Safe environment)
  • Take Full database offline backup (Database size is small , about 20 GB)
  • Copy the production database full backup to the new server  ( it takes about 5 minutes over network).
  • Copy initXXX.ora & Password file
  • Optionally, copy the tnsnames.ora, listener.ora & sqlnet.ora
  • Edit initXXX.ora file:

Remove all _ parameters

Create all the referenced directories

Enlarge the SGA  and configure ASMM

 

  • Copy the datafiles, redo files, control files to the same directories as that on the production

 

  • Configure default listener using the “Net Configuration Assistant”
  • Create the Oracle service on the 64bit system, using the ORADIM command

        oradim -new -sid XXX -startmode auto -SRVCSTART SYSTEM -intpwd ****** –   pfile M:\app\Administrator\product\11.1.0\db_1\database\initXXX.ora

You may need to write the above command manually, not copy/paste!                                                Instance created and started

 

Set environment

      set ORACLE_HOME=M:\app\Administrator\product\11.1.0\db_1         

      set PATH=%ORACLE_HOME%\bin;%PATH%                          

      set ORACLE_SID=XXX

 

If the service started the database in the “READ WRITE” mode then:

Leave the Windows service running

From command window:

                                sqlplus / as sysdba

shutdown immediate

startup mount

 

 

To avoid the issue of FRA being 100% utilized:

                                 rman target /

crosscheck archivelog all;

crosscheck backupset;

delete expired backupset;

shutdown immediate

 

OLAP:

If you have OLAP installed, then you may encounter many errors [ORA-07445/ORA-03113] later on. So, to avoid these errors, we will remove the OLAP and then add it again:

{Please refer to:

DB CONVERSION: 32 bit –>64 Bit Broke OLAP OPTION [ID 386990.1]

http://www.orafaq.com/forum/t/170415/

 

                                sqlplus / as sysdba

startup upgrade

 

Remove OLAP

                                                @?/olap/admin/catnoamd.sql

@?/olap/admin/catnoaps.sql

@?/olap/admin/catnoxoq.sql

@?/olap/admin/olapidrp.plb

 

Add OLAP  ( if it is required) or add it later after migration using :                                                            @?/olap/admin/olap.sql SYSAUX TEMP;

 

{{If the utlrp script hang, this is mainly due to OLAP being installed}}

 

        Migrate:

@?\rdbms\admin\utlip.sql

@?\rdbms\admin\utlrp.sql                         ==> 5 minutes

39a

All of the above 70 invalid objects are related to the OLAP, so , don’t worry!

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

shutdown immediate

startup

select name,platform_name,open_mode from v$database;

Congratulation ,  Migration completed !

 

If you want to add the OLAP option:

@?/olap/admin/olap.sql SYSAUX TEMP;                               ==> 3 minutes

@?\rdbms\admin\utlrp.sql                                         è zero invalid object

{But note that adding OLAP will produce many core dumps and trace files, it is better to keep OLAP deleted unless you really need it}

 

Post Migration

 

Configure EM DB Control on the New Server

 

Ensure that the listener is up and running

emca -deconfig dbcontrol db -repos drop

40

emca -config dbcontrol db -repos create

4142

Emctl status dbconsole

42a

43

 

Windows Services:

44

Done!

Oracle Data Pump Import Log File Analysis

In most (or all) cases , when you fully import the database using oracle data pump or the old “imp” import utility , you will get some errors. Some of them are expected and could be ignored and some need close attention to investigate and fix.

For big databases, the import log file may be huge and it may take a lot of time to review. In this blog I’ll show you a quick way to analyze the errors exist in the import log file.

For Unix Platforms:

Execute the following two commands:

tail -20 import.log

This will report the last 20 lines in the import log file, with the number of errors, if any.

For example:

1

 

If you executed the following command:

awk ‘($1 ~ /ORA-/)’ TMS_full_import.log|awk ‘{print $1}’|sort|uniq -c|sort –fr

you will get a summary report of all errors in the import log file and how many times they occurred. For example:

2

From here, you can drill down and investigate why each category of the above errors occurred.

For example, for the erros “ORA-31681” which was reported 32 times during the import:

ORA-31684: Object type ROLE:”AQ_ADMINISTRATOR_ROLE” already exists

So, we are importing an object that is already exist in the target database è could be ignored safely.

 

For Windows Platforms:

I found a similar commands developed for Windows Power Shell by “Marco Mischke”.

So, you may move import log file to the windows and run the following two commands from the power shell:

Get-Content import.log -tail 20

3

Get-Content import.log | %{$_.split(‘:’)[0]} | Select-String -pattern ^ORA- | Group-Object | Format-Table count, name

 

4

You will get a similar results.