Cloning to Single Instance From A RAC Cluster…Or, “Make It A Double!”

Although there a numerous blogs out there, and adding a new one seems like a repeating exercise, I found it hard to find a good one suited to my situation..

Which is? Why am I so unique? Well..beside being really awesome, I have an RAC Cluster which once in a while needs to be cloned to a Single Instance database. This happens just enough so I know what I’m doing, but not enough to keep remembering every pesky detail, so it’s taking way longer than I know it could..Hence: another step by step write-down, with the caveats everyone else forgets to mention.

Source Database

The source database is a RAC.
RAC Name: dbSource-scan
SID Node 01: dbSource01
SID Node xx: dbSourcexx (We just need one, and this is applicable for all kinds of multinode RAC Clusters)
Host: server01

Target Database

The target is a single instance database.
SID: EXP05
Host: server02

* The intention is to make a clone of the RAC database to a single instance database on a different host where the RAC database is located.
* The clone will be done using the active method from RMAN, and not from a backup.
* ASM is in use, both at the source and target, but this doesn’t really matter for the steps involved.

The checklist:

– Configure the Listener on server01 and server02
– Configure the TNS NAMES on server01 and server02
– Create ORAPWD file
– Create target instance pfile/spfile
– Test connectivity
– Start the duplicate from active database.

Configure the Listener
This needs to be done on: server02

Edit the listener.ora file in the $ORACLE_HOME/network/admin location and add the static info

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    )
)

ADR_BASE_LISTENER = /oracle/base

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=dbSource-scan)
          (SID_NAME=dbSource01)
          (ORACLE_HOME=/oracle/base/db/dbhome1/)
        )
   (SID_DESC=
          (GLOBAL_DBNAME=EXP05)
          (SID_NAME=EXP05)
          (ORACLE_HOME=/oracle/base/db/dbhome1/)
        )
      )

Caution: pay attention to where to use the RAC SCAN name and where to use the specific instance name!

Save the file and stop/start the listener.
Check for the output to see the status of the instance, it should be listed as  UNKNOWN.

$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
                                
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2014 09:35:50
Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server02)(PORT=1521)))
STATUS of the LISTENER          
------------------------        
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-SEP-2014 09:35:43
Uptime                    0 days 0 hr. 0 min. 8 sec
Trace Level               off   
Security                  ON: Local OS Authentication
SNMP                      OFF   
Listener Parameter File   /oracle/grid/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/server02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1522)))
Services Summary...
Service "dbSource-scan" has 1 instance(s).
  Instance "dbSource01", status UNKNOWN, has 1 handler(s) for this service...
Service "EXP05" has 1 instance(s).
  Instance "EXP05", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configuring the TNSNAMES.ora
This needs to be done on: server01,server02

Most, if not all, of the actions will need to be done on the server02 (where the final database will be located) but one step, and an important one, needs to be done on the source server (nodeXX, or in this case server01): the tnsnames needs to be amended with the name of the target in order to be able to create a connection between the source and target. This is an important and easy to miss step!

On server01 (the node of the RAC where the instance we are duplicating from resides):

dbSource01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbSource-scan)
      (instance_name = dbSource01)
    )
  )

EXP05 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = exp05)
      (UR = A)
    )
  )

Most likely the information about the node is already there, it is referenced here to have a complete idea of what we need. The important part is the entry EXP05.

Also make note of the UR=A parameter, this makes sure the SYS user can login from a remote location to an idle instance.

Save the file.
Test the new entry FROM SERVER01:

$ tnsping exp05

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2014 09:48:15
Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/oracle/base/db/dbhome1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = server02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = exp05) (UR = A)))
OK (180 msec)

Although there is no instance whatsoever, yet, the resolving is in place.

On the SERVER02 we need to do this exact same thing:
Edit the tnsnames.ora:

dbSource01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbSource-scan)
      (instance_name = dbSource01)
      (UR = A)
    )
  )

EXP05 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = EXP05)
      (UR=A)
    )
  )

Save the file. And test connectivity to BOTH instances.

$ tnsping exp05

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2014 10:02:31
Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/oracle/base/db/dbhome1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = server02)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = EXP05) (UR=A)))
OK (0 msec)

$ tnsping dbSource01

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 17-SEP-2014 10:02:37
Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/oracle/base/db/dbhome1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
 (CONNECT_DATA = (SERVER = DEDICATED)
 (SERVICE_NAME = dbSource-scan) (instance_name = dbSource01) (UR = A)))
OK (100 msec)

Create ORAPWD file
This needs to be done on: server02

(Again, important not to miss this step!)

Navigate to the $ORACLE_HOME/dbs location.
Create a password file for the target database:

$ orapwd file=orapwEXP05 password=secret

Create target instance pfile/spfile
This needs to be done on: server02

Navigate to the $ORACLE_HOME/dbs location.

Create an init file: initEXP05.ora

*.db_name='EXP05'
*.audit_file_dest='/oracle/base/EXP05/admin/EXP05/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_cache_size=2752M
*.db_create_file_dest='/oracle/oradata/EXP05'
*.db_domain=''
*.db_name='EXP05'
*.db_recovery_file_dest='/oracle/base/fast_recovery_area/'
*.db_recovery_file_dest_size=773094113280
*.diagnostic_dest='/oracle/base/EXP05'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server02)(PORT=1521)
)))'
*.memory_max_target=5G
*.memory_target=4G
*.open_cursors=4000
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=680
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'

Save the file.

Start the instance in nomount mode.

$ sqlplus sys/secret@exp05 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 17 10:48:33 2014
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 5344731136 bytes
Fixed Size                  2237776 bytes
Variable Size            2449476272 bytes
Database Buffers         2885681152 bytes
Redo Buffers                7335936 bytes
SQL>

Test Connectivity
This needs to be done on: server02

$ sqlplus sys/secret@exp05 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 17 10:13:22 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> Disconnected

$ sqlplus sys/secret@dbSource01 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 17 10:13:30 2014
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>

If these steps do not succeed, continuing will not have any use, since RMAN later on will bail out on us. Double, triple check all the entries made when this happens, even an missing comma can throw a wrench in the procedure..

Duplicate from active database.
This needs to be done on: server02

$ rlwrap rman target sys/secret@dbSource01 auxiliary sys/secret@exp05

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 17 10:14:40 2014

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

connected to target database: dbSource-scan (DBID=5558547333)
connected to auxiliary database EXP05 (not mounted)

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database to EXP05 FROM ACTIVE DATABASE nofilenamecheck;
}

This command will run for a period of time, depending on network speed/disk speed etc.

<SNIP>
input datafile copy RECID=30 STAMP=858513312 
file name=/oracle/oradata/EXP05/EXP05/datafile/o1_mf_fr_indx_96pinnnm_.dbf
datafile 32 switched to datafile copy
input datafile copy RECID=31 STAMP=858513312 
file name=/oracle/oradata/EXP05/EXP05/datafile/o1_mf_fr_data_9cpinnpb_.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database enable block change tracking

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

database opened
Finished Duplicate Db at 17-SEP-14
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby

RMAN> 

And we’re done! The database is open and ready to use.

Some steps I personally take to make sure I can bounce the database without surprises such as a wrong control file:

$ sqlplus / as sysdba
SQL> create pfile from memory;
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup

 

Success!

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 and tagged , , . Bookmark the permalink.

2 Responses to Cloning to Single Instance From A RAC Cluster…Or, “Make It A Double!”

  1. mczimm says:

    Hey, just stumbled upon your post. Unfortunately you’ve got an error. You wrote “ASM is in use, both at the source and target, but this doesn’t really matter for the steps involved.” Actually it’s very matter! Because if you will duplicate RAC database with ASM to non RAC database without ASM you will get the error “ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage”

    • Indeed, the situation is a bit different when moving out of ASM. This issue can easily be circumvented when you create a (s)pfile to the $ORACLE_HOME/dbs location (provided this is not in ASM either). So you are correct, and I should be more careful with my statements whether it matters or not!

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