Converting Single Instance to RAC..OR..”Just add water..”

We had the need to clone a RAC for upgrading purposes, and although the steps are quite straight forward, there are always some caveats.

The steps are:

– Clone the RAC (or basically one node) to a single instance.
– RAC -> “RAC01” and “RAC02” for each node respectively.
– Clone -> “Clone” for the single instance, “Clone01” and “Clone02” for each instance.
– Expand/Convert the single instance to a multi node RAC.

The first step is documented earlier in this blog, just search for “clone”, and take a pick: either from backup, or from active duplication. The result should be the same: a single instance clone of the source RAC.

This blog continues where the other two end: just at the end of the clone, where the database is up and ready.

First some housekeeping:

Add the new database to the /etc/oratab file:

on Node 1:

+ASM1:/oracle/11.2.0.4/grid:N           # line added by Agent
RAC:/oracle/11.2.0.4/base/db/dbhome_1:N            # line added by Agent
CLONE:/oracle/11.2.0.4/base/db/dbhome_1:N

On Node 2:

+ASM2:/oracle/11.2.0.4/grid:N           # line added by Agent
RAC:/oracle/11.2.0.4/base/db/dbhome_1:N            # line added by Agent
CLONE:/oracle/11.2.0.4/base/db/dbhome_1:N

Note: the entries contain the RAC db name, not the instance names!

Now for the good part:

Create a new pfile from the running clone:

sql> create pfile from spfile

Edit the pfile:

clone.__db_cache_size=30534533120
clone.__java_pool_size=268435456
clone.__large_pool_size=67108864
clone.__oracle_base='/oracle/base'#ORACLE_BASE set from environment
clone.__pga_aggregate_target=6912212992
clone.__sga_target=34359738368
clone.__shared_io_pool_size=0
clone.__shared_pool_size=3154116608
clone.__streams_pool_size=134217728
*.audit_file_dest='/oracle/11.2.0.4/base/admin/clone/adump'
*.audit_trail='DB'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATACLONEDG/clone/controlfile/current.263.884363099','+FRACLONEDG/clone/controlfile/current.263.884363099'#Set by RMAN
*.core_dump_dest='/oracle/11.2.0.4/base/diag/rdbms/clone/clone/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATACLONEDG'
*.db_domain=''
*.db_name='clone'#Reset to original value by RMAN
*.db_recovery_file_dest='+FRACLONEDG'
*.db_recovery_file_dest_size=858937884672
*.diagnostic_dest='/oracle/11.2.0.4/base'
*.event=''
*.log_archive_max_processes=30
*.open_cursors=20000
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_mode='FIRST_ROWS'
*.optimizer_use_sql_plan_baselines=FALSE
clone1.pga_aggregate_target=6442450944
*.plsql_code_type='NATIVE'
*.processes=680
*.recyclebin='OFF'
*.remote_listener='erpemea-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1056
*.sga_max_size=32G
*.sga_target=32G
*.standby_file_management='AUTO'
*.statistics_level='ALL'
*.streams_pool_size=0
*.undo_retention=3600
clone.undo_tablespace='UNDOTBS1'

Remove:

clone.__db_cache_size=30534533120
clone.__java_pool_size=268435456
clone.__large_pool_size=67108864
clone.__oracle_base='/oracle/base'#ORACLE_BASE set from environment
clone.__pga_aggregate_target=6912212992
clone.__sga_target=34359738368
clone.__shared_io_pool_size=0
clone.__shared_pool_size=3154116608
clone.__streams_pool_size=134217728

Change

*.cluster_database=false

to

*.cluster_database=true

Change

*.pga_aggregate_target=6442450944

to

clone1.pga_aggregate_target=6442450944

Change the line:

clone.undo_tablespace='UNDOTBS1'

to

clone1.undo_tablespace='UNDOTBS1'
clone2.undo_tablespace='UNDOTBS2'

Add instance_numbers and threads:

clone1.thread=1
clone2.thread=2
clone1.instance_number=1
clone2.instance_number=2

We use large pages, but I prefer to battle with this as the last step..for now add:

*.use_large_pages='false'

Save the file WITH A NEW NAME:

old: initclone.ora
new: initclone1.ora

IMPORTANT STEP:

Shutdown the single instance db NOW, otherwise the first  instance will not start:

sql> shutdown immediate

Export the ORACLE_SID to:

$ export ORACLE_SID=clone1

Start the database:

$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 12:55:13 2015

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

Connected to an idle instance.

SQL>startup

ORACLE instance started.

Total System Global Area 3.4206E+10 bytes
Fixed Size                  2270360 bytes
Variable Size            3556772712 bytes
Database Buffers         3.0602E+10 bytes
Redo Buffers               45649920 bytes
Database mounted.
Database opened.
SQL>

Copy the file to the 2nd node, and rename to:

old: initclone1.ora
new: initclone2.ora

Shutdown the database and start from new pfile:

sql> shutdown immediate;
sql> create spfile from pfile;
sql> startup;

Check the status of the logfiles:

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 28 2 YES INACTIVE
2 1 29 2 NO CURRENT
3 2 1 2 YES INACTIVE
4 2 2 2 NO CURRENT
5 1 27 2 YES INACTIVE
6 2 0 2 YES UNUSED
SELECT thread#, first_change#,
TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'),
next_change#
FROM v$log_history;

THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE#
---------- ------------- ---------------- ------------
2    6075442179 07-06-15 05:51PM   6075442362
1    6075442179 07-06-15 05:51PM   6075474791
1    6075474791 07-06-15 08:00PM   6075483706
1    6075483706 07-06-15 10:00PM   6075510297
1    6075510297 07-06-15 10:15PM   6075522265
1    6075522265 07-06-15 10:19PM   6075533711
1    6075533711 07-06-15 10:32PM   6075545799
1    6075545799 07-06-15 10:43PM   6075557616
1    6075557616 07-06-15 10:47PM   6075568423
1    6075568423 07-06-15 10:51PM   6075578835
1    6075578835 07-06-15 10:55PM   6075590686

THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE#
---------- ------------- ---------------- ------------
1    6075590686 07-06-15 10:58PM   6075598506
1    6075598506 07-06-15 11:00PM   6075608432
1    6075608432 07-06-15 11:05PM   6075621076
1    6075621076 07-06-15 11:19PM   6075631591
1    6075631591 07-06-15 11:23PM   6075642165
1    6075642165 07-06-15 11:29PM   6075652911
1    6075652911 07-06-15 11:37PM   6075844416
1    6075844416 07-07-15 12:36AM   6076295209
1    6076295209 07-07-15 12:54AM   6076298824
1    6076298824 07-07-15 12:55AM   6076302252
1    6076302252 07-07-15 12:55AM   6076305515

THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE#
---------- ------------- ---------------- ------------
1    6076305515 07-07-15 12:56AM   6076308984
1    6076308984 07-07-15 12:56AM   6076312298
1    6076312298 07-07-15 12:56AM   6076316540
1    6076316540 07-07-15 12:57AM   6076319662
1    6076319662 07-07-15 01:15AM   6076323738
1    6076323738 07-07-15 02:00AM   6076338862
1    6076338862 07-07-15 05:00AM   6076378793

29 rows selected.
SQL>

Since we already cloned from a RAC, we don’t need to add additional logfiles to thread 2, it’s already available as shown above.

The two instances are now capable to start, let’s tie them together with a service:

$ srvctl add database -d clone -o /oracle/11.2.0.4/base/db/dbhome_1
$ srvctl add instance -d clone -i clone1 -n lnx01
$ srvctl add instance -d clone -i clone2 -n lnx02

$ srvctl add service -d clone -s clone_tst_01 -r clone1,clone2 -P basic -e session

Connect to the database (instance doesn’t matter):

sql> alter system set service_names='clone_tst_01';
sql> alter system register;

Check with:

$ lsnrctl services
or
$ lsnrctl services|grep -i clone_tst_01

Check the database status

$ srvctl config database -d clone

Database unique name: clone
Database name:
Oracle home: /oracle/11.2.0.4/base/db/dbhome_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clone
Database instances: clone1,clone2
Disk Groups: DATA1DG,FRA1DG
Mount point paths:
Services: clone_tst_01
Type: RAC
Database is administrator managed
$

The ultimate check would be to connect from a remote server to this server with the following entry on the client:

clonedb =
(DESCRIPTION =
(LOAD_BALANCE = YES)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.133.128.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = clone_tst_01)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)

As mentioned before, we are using large pages (Huge Mem), so as a final step we alter the pfile on each instance, and change the line:

*.use_large_pages='false'

to

*.use_large_pages='true'

Bounce the instance, and they should be using Large Pages. More information about this subject can also be found in this blog. Just search for “huge mem”.

And this should cover all the bases.

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, RAC, Technical Stuff and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s