Thursday, October 21, 2010

RAC to Single Instance Active Database Duplication

11g allows a database to be duplicated via Oracle Net without the use of backups. The source database must be open and in archive log mode or mounted after a clean shutdown.

This blog is about duplicating a RAC source database to a single instance in a different host. RAC is a 11.2 with two nodes
olsnodes -n
rac4 1
rac5 2
and two diskgroups for data (+DATA) and flash recovery (+FLASH).

1. Install Oracle software (Enterprise or Standard, same as source edition) on the host where duplicate database will be created or clone from an existing single instance Oracle Home. Also create locations for audit trail (adump directory) and location to store the control files.

2. Create a static listener entry on the listener.ora in duplicate database Oracle Home.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdaux)
(SID_NAME = stdaux)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/ent)
)
)
stdaux is single instance name.

3. Create a pfile that only contains the db_name entry
db_name='stdaux'
4. Create a password file in the duplicate Oracle Home with the same sys password as the source database sys password
orapwd file=orapwstdaux password=password ignorecase=y
5. Start the duplicate instance in nomount state
startup nomount;
6. Create a TNS entry in the tnsnames.ora file in the source database Oracle Home. Adding this to only one node's tnsnames.ora file is suffice, provided duplicate command is run from this node.
STDAUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate-hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdaux)
)
)
7. Connect to the target (source database) as well as the auxiliary (duplicate database) using rman
rman target / auxiliary sys/password@stdaux
Duplication will fail if connection to the auxiliary instance is not through Oracle Net.

8. The command used will copy the spfile from source to duplicate while overriding parameter values set using various clauses (set and parameter_value_convert). In 11gR2 RAC remote listener is set to scan listener and local listener is set to VIP TNS entry, which is something not required in a single instance. These parameters will be reset.
Since db_create_file_dest and db_recovery_file_dest will be used in command data files will be created as OMF in these locations. But control file location doesn't get created automatically, so it is necessary to create them manually as mentioned on step 1.
During this step if the duplication process terminates due to an error, delete the spfile on the duplicate Oracle Home (if it was created by the duplicate process) and shutdown and start up the instance in nomount state. Correct the error and try again.
RMAN> DUPLICATE TARGET DATABASE
2> TO stdaux
3> FROM ACTIVE DATABASE
4> SPFILE PARAMETER_VALUE_CONVERT 'rac11g2', 'stdaux', '+DATA', '/data/oradata', '+FLASH','/data/flash_recovery'
5> SET cluster_database='false'
6> reset REMOTE_LISTENER
7> reset local_listener
8> SET db_create_file_dest = '/data/oradata'
9> set db_recovery_file_dest = '/data/flash_recovery';

Starting Duplicate Db at 21-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/rac11g2/spfilerac11g2.ora' auxiliary format
'/opt/app/oracle/product/11.2.0/ent/dbs/spfilestdaux.ora' ;
sql clone "alter system set spfile= ''/opt/app/oracle/product/11.2.0/ent/dbs/spfilestdaux.ora''";
}
executing Memory Script
..
..
..
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_sysaux_12lr0eq9_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_undotbs1_13lr0es0_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_users_15lr0esj_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_undotbs2_14lr0esf_.dbf

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

database opened
Finished Duplicate Db at 21-OCT-10
rac11g2 is the RAC database name which will appear on various dump file paths and will be replaced by stdaux. Any reference paths to ASM diskgroup will be replaces with directory paths.

9. At the end of the successful execution of the command, duplicate process is complete. Single instance would have chosen one of the undo tablespace (UNDOTBS1) as the system undo tablespace, while other one (UNDOTBS2) will also be there as a tablespace. If needed some tablespaces could be skipped during the duplicate process but trying to skip undo tablespaces could result in the following error
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/21/2010 16:46:01
RMAN-05514: Tablespace UNDOTBS2 has undo information, cannot skip
10. Though the duplicate instance get created the spfile will still have parameters that were set at instance level.
rac11g21.__db_cache_size=369098752
rac11g22.__db_cache_size=339738624

stdaux.__db_cache_size=436207616
rac11g21.__java_pool_size=4194304
rac11g22.__java_pool_size=25165824

stdaux.__java_pool_size=4194304
rac11g21.__large_pool_size=8388608
rac11g22.__large_pool_size=8388608

stdaux.__large_pool_size=4194304
rac11g21.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g22.__oracle_base='/opt/app/orac

..
..
rac11g22.instance_number=2
rac11g21.instance_number=1

*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=631242752
rac11g22.thread=2
rac11g21.thread=1
rac11g22.undo_tablespace='UNDOTBS2'
rac11g21.undo_tablespace='UNDOTBS1'
These instance level settings could be reset on the duplicate instance or create a pfile and delete the unwanted entries and create a spfile out of the modified pfile.

The way to create a duplicate instance without the unwanted parameter entries is to create a pfile with all the necessary parameter entries and use that to start the duplicate in nomount state. Then when running the duplicate command omit the spfile clause.

Replace the pfile created on step 3 with a pfile with following entries (or more if needed)
*.audit_file_dest='/opt/app/oracle/admin/stdaux/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/stdaux/controlfile/o1_mf_62n35l5c_.ctl','/data/flash_recovery/stdaux/controlfile/o1_mf_62n35lcp_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain='domain.net'
*.db_name='stdaux'
*.db_recovery_file_dest='/data/flash_recovery'
*.db_recovery_file_dest_size=40705720320
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdauxXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1326448640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3707764736
*.undo_tablespace='UNDOTBS1'
Start the duplicate instance in nomount state and follow the rest of the steps unitl the execution of duplicate command.
Replace the above given duplicate command with
RMAN> DUPLICATE TARGET DATABASE
2> TO stdaux
3> FROM ACTIVE DATABASE;

Starting Duplicate Db at 21-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

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
..
..
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 21-OCT-10
The duplicate instance's spfile will not contain any RAC instance specific entries, but unlike a spfile of a database created from the scratch it will show all the hidden Oracle parameters.

Related Post
Duplicate Database Without Target Connection or Catalog Connection

Monday, October 4, 2010

10gR2 RAC on RHEL 4 using VirtualBox

This blog is mainly about the pre-installation tasks (highlights) that is needed on VirtualBox to create 10gR2 RAC on RHEL 4. The oracle-base site has two articles on creating 11gR2 RAC using VirtualBox and VMware. These articles uses RHEL 5 on the virtual servers could be refered for installing a 11gR2 RAC.

1. Download Virtualbox rpm (VirtualBox-3.2-3.2.8_64453_rhel5-1.x86_64.rpm or later, chose the correct kernel architecture and host OS version). This version of virtualbox allows the creation of shared disks, which is a key requirment to create a RAC.

2. When installing RHEL 4 with a mixture of IDE controllers (for CD, iso image) and SATA controllers (for loacl hard disk) following error could be seen. (This is not seen when installing RHEL 5) Linux installation bootup seem to hang at this error but continues after a long wait but it wasn't possible to restart the virtual server after the Linux OS installation.

3. To fix this problem all the local disks were created using the IDE controller which seem to solve this issue.

4. Once Linux OS is installed in one virtual server, clone it with
VBoxManage clonehd /vm/rac1.vdi /vm/rac2.vdi --remember
When creating the second virtual server select "use existing harddisk" option to make use of the cloned disk. Once the clone (second rac node) is started change the hostname,IP and etc to make clone different from the source.
If the vdi file is copied instead of cloning generate a new uid with (tested on VirtualBox 4.1)
VBoxManage internalcommands sethduuid rac2.vdi
UUID changed to: ee7c49ae-2871-463b-a75c-a88135b57bde
5. Using IDE controllers only 3 local disks could be used at one time in the server (considering fourth IDE slot would be for CD). Create disks with shareable option
VBoxManage createhd --filename /vm/asm1.vdi --size 10240 --format VDI --variant Fixed --type shareable --remember

VBoxManage createhd --filename /vm/asm2.vdi --size 10240 --format VDI --variant Fixed --type shareable --remember 
and attach them to each virtual server
VBoxManage storageattach rac1 --storagectl "IDE Controller" --port 0 --device 1 --type hdd --medium /vm/asm1.vdi
VBoxManage storageattach rac1 --storagectl "IDE Controller" --port 1 --device 1 --type hdd --medium /vm/asm2.vdi

VBoxManage storageattach rac2 --storagectl "IDE Controller" --port 0 --device 1 --type hdd --medium /vm/asm1.vdi
VBoxManage storageattach rac2 --storagectl "IDE Controller" --port 1 --device 1 --type hdd --medium /vm/asm2.vdi
May need to assign IDE slots (primary salve/master, secondary slave/master) as appropriately.
6. These disks would appear as /dev/hdb and /dev/hdc (similar names) when listed with fdisk -l from the virtual server. To store the ocr,vote disks and asm spfile craeted partitons in one of them and use the reset of the space to create a ASM disk using oracleasm.
Disk /dev/hdb: 10.7 GB, 10737418240 bytes
16 heads, 63 sectors/track, 20805 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/hdb1               1         214      107824+  83  Linux
/dev/hdb2             215         273       29736   83  Linux
/dev/hdb3             274         293       10080   83  Linux
/dev/hdb4             294       20805    10338048    5  Extended
/dev/hdb5             294       20805    10338016+  83  Linux

Disk /dev/hdc: 10.7 GB, 10737418240 bytes
16 heads, 63 sectors/track, 20805 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/hdc1               1       20805    10485688+  83  Linux
Here hdb1 would be bound to a raw device and will be used as storage location for OCR, hdb2 for vote and hdb3 for asm spfile. Rest of it will be used for ASM (data). hdc1 would be used for another ASM disk (flash).

7. Provided other requriement are satisfied (two network cards) this is all that is required to get started with the RAC installation. (RAC related pre-installation ie. kernel parameters, required rpms and etc are needed, not covered here)

8. Use the following when starting virtual servers remotely and having a GUI interface is not important.
VBoxManage startvm rac1 --type headless
VBoxManage startvm rac2 --type headless