RAC: migrate standalone database to RAC

16.04.2015
by Kamil Stawiarski
  • Procedure to convert a standalone database to RAC 11gR2

I have a single-instance database and I want to convert it to RAC with minimum downtime.

  1  select *
  2  from v$option
  3* where lower(parameter) like '%real application cluster%'
SQL> 
SQL> /

PARAMETER				 VALUE
---------------------------------------- --------------------
Real Application Clusters		 FALSE

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_blqqtxff_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_blqqtxfz_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_blqqtxg3_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_blqqtxgk_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_blqqwwj4_.dbf

SQL> sho parameters cluster

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cluster_database		     boolean	 FALSE
cluster_database_instances	     integer	 1
cluster_interconnects		     string
SQL> !env | grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

The first step is to enable RAC option by recompiling the binaries
(YOU HAVE TO STOP THE DATABASE FIRST!!!)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lodzio ~]$ cat orcl.env 
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@lodzio ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@lodzio lib]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib
[oracle@lodzio lib]$ make -f ins_rdbms.mk rac_on ioracle
rm -f /u01/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so
cp /u01/app/oracle/product/11.2.0/dbhome_1/lib//libskgxpg.so /u01/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so
 - Use stub SKGXN library
cp /u01/app/oracle/product/11.2.0/dbhome_1/lib/libskgxns.so /u01/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.so
/usr/bin/ar d /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a ksnkcs.o
/usr/bin/ar cr /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/kcsm.o 
chmod 755 /u01/app/oracle/product/11.2.0/dbhome_1/bin

(...)

After this operation I can startup instance and proceed to migration. The database can work in production mode.

Now I will set the location of the db_recovefile_dest parameter to target ASM diskgroup, to which I want migrate my database. I will also set parameters for OMF (for future files).

SQL> alter system 
  2  set db_recovery_file_dest='+DATA';

System altered.

SQL> alter system 
  2  set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system 
  2  set db_create_file_dest='+DATA';

System altered.

SQL> alter system 
  2  set db_create_online_log_dest_1='+DATA';

No I will create the script to make a snapshot copy of database files on the ASM diskgroup. In subsequent executions, the script will recover the copy of database with incremental backups. Exact description of this commands can be found here:

4.4.3 Incrementally Updated Backups: Rolling Forward Image Copy Backups

#!/bin/bash 

rman target / << !
run {
backup for recover of copy with tag 'asm_migr' incremental level 1 database;
recover copy of database with tag 'asm_migr';
}
!

Run the script appropriate number of times, depending on your database size and maintenance window policy.

In the next step I will shutdown the instance and execute final steps to migrate the database to ASM

Shutdown the instance:

[oracle@lodzio ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 16 12:28:47 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Create pfile from spfile after shutting down the instance. We will set appropriate parameters for RAC.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

Remember to export ORACLE_SID to value related to instance name and not just database name – if database name is "orcl" then the instance name on the first node can be "orcl1″ and on the second node "orcl2″

export ORACLE_SID=orcl1
[oracle@lodzio ~]$ env | grep ORA
ORACLE_SID=orcl1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Adjust parameters in spfile and create spfile on ASM. Notice, that I have deleted CONTROL_FILES entry. I will use current controlfiles (not backup of controlfiles but actual controlfiles!!!) to transfer.

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.nls_language='POLISH'
*.nls_territory='POLAND'
*.open_cursors=300
*.pga_aggregate_target=838860800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2516582400
#### NEW PARAMETERS FOR RAC ####
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.thread=1
orcl2.thread=2
*.cluster_database=true

Now we will create new spfile, create audit dest location on the second node and use RMAN to transfer controlfiles to ASM

[oracle@lodzio dbs]$ ssh miodzio "mkdir -p /u01/app/oracle/admin/orcl/adump"
[oracle@lodzio dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 16 12:46:24 2015

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size		    2230952 bytes
Variable Size		  603981144 bytes
Database Buffers	 1879048192 bytes
Redo Buffers		   20078592 bytes

Now we will restore controlfile (remember to use the actual controlfile and not backup of the controlfile) and mount the database

[oracle@lodzio dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 16 13:07:19 2015

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_blqqwp7y_.ctl';

Starting restore at 16-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 instance=orcl1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.275.877180049
Finished restore at 16-APR-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Now I will create spfile in the final destination:

SQL> create pfile from spfile;

File created.

SQL> create spfile='+DATA/orcl/spfileorcl.ora' from pfile;

File created.

SQL> shutdown immediate
ORA-01109: baza danych nie jest otwarta


Database dismounted.
ORACLE instance shut down.

And now let’s create a pointer to spfile on ASM in pfile

[oracle@lodzio dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@lodzio dbs]$ rm spfileorcl1.ora 
[oracle@lodzio dbs]$ vim initorcl1.ora 
[oracle@lodzio dbs]$ cat initorcl1.ora 
spfile='+DATA/orcl/spfileorcl.ora'

Now we have to start the database in mount state, using new spfile:

SQL> startup mount
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size		    2230952 bytes
Variable Size		  620758360 bytes
Database Buffers	 1862270976 bytes
Redo Buffers		   20078592 bytes
Database mounted.
SQL> sho parameters spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/orcl/spfileorcl.ora

and execute RMAN script once again to have a fresh COPY of database.

[oracle@lodzio ~]$ ./migrate_to_asm.sh 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 16 13:31:12 2015

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

connected to target database: ORCL (DBID=1404716982, not open)

RMAN> 2> 3> 4> 
Starting backup at 16-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_blqqtxff_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_blqqtxfz_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_blqqwwj4_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_blqqtxg3_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_blqqtxgk_.dbf
channel ORA_DISK_1: starting piece 1 at 16-APR-15
channel ORA_DISK_1: finished piece 1 at 16-APR-15
piece handle=+DATA/orcl/backupset/2015_04_16/nnndn1_asm_migr_0.277.877181473 tag=ASM_MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-APR-15
channel ORA_DISK_1: finished piece 1 at 16-APR-15
piece handle=+DATA/orcl/backupset/2015_04_16/ncsnn1_asm_migr_0.278.877181479 tag=ASM_MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-APR-15

Starting recover at 16-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=+DATA/orcl/datafile/system.257.877176269
recovering datafile copy file number=00002 name=+DATA/orcl/datafile/sysaux.258.877176277
recovering datafile copy file number=00003 name=+DATA/orcl/datafile/undotbs1.260.877176287
recovering datafile copy file number=00004 name=+DATA/orcl/datafile/users.262.877176291
recovering datafile copy file number=00005 name=+DATA/orcl/datafile/example.259.877176285
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2015_04_16/nnndn1_asm_migr_0.277.877181473
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2015_04_16/nnndn1_asm_migr_0.277.877181473 tag=ASM_MIGR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 16-APR-15

RMAN> 

Recovery Manager complete.

And now we can switch database to fresh COPY and open the database.

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.877176269"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.258.877176277"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.260.877176287"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.262.877176291"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.259.877176285"

RMAN> recover database;

Starting recover at 16-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 instance=orcl1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 16-APR-15

RMAN> alter database open;

database opened

OK. We are almost done 🙂 Now it’s time to "move" redo logs to ASM

SQL> sho parameters db_create_online_log_dest_1

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1	     string	 +DATA

SQL> alter database add logfile group 11 size 100m, 
  2  group 12 size 100m,
  3  group 13 size 100m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1, group 2, group 3;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_11.279.877182399
+DATA/orcl/onlinelog/group_12.280.877182401
+DATA/orcl/onlinelog/group_13.281.877182401

and create additional UNDO and REDO for second instance:

SQL> create undo tablespace UNDOTBS2  
  2  datafile size 256m
  3  autoextend on next 128m
  4  maxsize unlimited;

Tablespace created.

SQL> alter database add logfile thread 2 
  2  group 21 size 100m,
  3  group 22 size 100m,
  4  group 23 size 100m;

Database altered.

SQL> alter database enable public thread 2;

Only few steps to finish 🙂
Now we have to close the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

regsiter database in a cluster

[oracle@lodzio ~]$ srvctl add database -d orcl -p +DATA/orcl/spfileorcl.ora -o /u01/app/oracle/product/11.2.0/dbhome_1
[oracle@lodzio ~]$ srvctl add instance -d orcl -i orcl1 -n lodzio
[oracle@lodzio ~]$ srvctl add instance -d orcl -i orcl2 -n miodzio

and start the database!

[oracle@lodzio ~]$ srvctl start database -d orcl
[oracle@lodzio ~]$ srvctl status database -d orcl
Instance orcl1 is running on node lodzio
Instance orcl2 is running on node miodzio

VOILA!!!

Search:

Category:
choose
All categories 12c Exadata GO internals konferencje Oracle RAC RMAN RUST security tips&tricks tuning

Popular posts

14.06.2016
ORACLE RAC 12c: restore OCR and VOTEDISK

Let’s fuckup the cluster! Of course after this operation, the final state of the processes can look like this: The cssd service will not be able to start, because there are no voting disks: OK, so let’s try to stop the cluster services: Now we will have to start CRS in exclusive mode and start […]

Read more
10.02.2016
How to list files from directory using external table in 12c

I’ve wrote about privilege escalation with external tables in this post: https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/ This time we will try to list all files within directory object, to which we have no EXECUTE privs. This is possible due to great new feature of database 12c that allows to use metacharacters to match multiple files in external table. I […]

Read more
28.05.2017
How to recover a dropped package from archivelogs – based on research for VOODOO engine

Sorry for long time without post, but we are writing with Marcin Rydz a new heterogenous replication product based on archivelogs and it’s consuming a looooooot of our time 🙂 Yes I know it’s nothing new on the market. There are other cool products and I know they’ll be probably be better. But we want […]

Read more
03.05.2015
12c: Spatial Vector Acceleration

There is a new feature in Oracle Spatial 12c, called "Spatial Vector Acceleration". According to Oracle: One of the most notable improvements with Oracle Spatial and Graph 12c can be seen in performance increases in vector data operations. Enabling the Spatial Vector Acceleration feature (available with the Spatial option) dramatically improves the performance of commonly […]

Read more
01.02.2017
back to the basics: ALTER TABLE MOVE vs SHRINK

It’s time for the next article with ODBV visualisation 🙂 This time let’s examine the difference between move and shrink – this is very common question on a lot of trainings. You can find a lot of great articles in the Internet regarding this subject but I think that visualisation really helps to understand what […]

Read more
30.12.2016
back to the basics: truncate table reuse storage vs drop storage

From time to time I get questions on my trainings, what is the difference between TRUNCATE TABLE and TRUNCATE TABLE DROP STORAGE… well, there is no difference because DROP STORAGE is default 😉 DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the […]

Read more
28.10.2017
Visualize your STATSPACK reports with Python Plotly

If you work as a consultant, getting remote access to your customer is sometimes a nightmare! Even when you’ll get one, there a times when you just want to get your job done and finish this nightmare. For example, there are times when it is easier to dump a statspack repo and do offline analyzes. […]

Read more
08.02.2016
Sparc M7 vs Intel and P8

I had a pleasure to work with my colleagues from Oracle – Radosław Kut and Krzysztof Marciniak – at comparing the performance of In-Memory queries between Sparc M7, Intel® Xeon® X5670 and Intel® Xeon® E5-2699. You can find results of our findings in this presentation: https://www.oracle.com/webfolder/s/delivery_production/docs/FY16h1/doc17/DB12c-on-SPARC-M7.pdf Following their methodology I’d like to present appropriate results […]

Read more
29.10.2015
Oracle 12c: PRAGMA UDF – the truth

Let’s check, why PRAGMA UDF makes execution faster, then regular function. C function responsible for calling a PL/SQL code from SQL is called "plsql_run": Now let’s try to create a new HR session and create a simple function which will multiple values by 2: From other terminal I will connect to the HR session with […]

Read more
12.03.2017
ODBV improvements

Thanks to suggestions made by Frits Hoogland, I made some improvements to the ODBV. The new version can be found here: http://ora-600.pl/oinstall/odbv.x86_64 The changes are: Recognition of first, second and third level bitmap block Recognition of pagetable segment header Block number ranges on the left side The blocks will be coloured properly to belonging segment. […]

Read more
03.11.2016
Oracle Database Block Visualizer

Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on… I didn’t find any tool for that so I’ve written my own. It’s core code is based […]

Read more
09.01.2020
The curious case of enq: TX – row lock contention

So we had a doubtful pleasure of migrating a few databases from Oracle Exadata X3-2 to Oracle Exadata X7-2 Cloud at Customer. Why doubtful? Well, this a material for a whole different story with a lot of beer – let me just say, that CC gen 1 was a bit rough around the edges 😉 […]

Read more

Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated