Saturday, January 14, 2012

Oracle Database Product Licensing..

Good article on understanding the oracle database products licensing...written by
Alan Swahn.

Check the below link.

http://blogs.flexerasoftware.com/elo/oracle-software-licensing/

Monday, January 9, 2012

Convert Single Instance DB to RAC DB

Converting single instance DB to RAC DB.

a) Login to the single instance server, shutdown the db and take the cold backup using rman.


1) Create a rman script as below

$ vi fullbkpofdb.rcv

connect target /
shutdown immediate;
startup mount;
run
{
allocate channel a1 device type disk;
allocate channel a2 device type disk;
backup database format 'datafiles_%t_%d_%U.bkp' tag='Full_Database';
backup current controlfile format 'ctrlfile_%t_%d_%U.bkp' tag='ctrl_file';
backup spfile format 'spfile_%t_%d_%U.bkp' tag='spfilebkp';
release channel a1;
release channel a2;
}
exit;

2) Run the script.

$ rman cmdfile 'fullbkpofdb.rcv' log 'backup.log'


3) Once the backup is completed, copy the backup to the primary node




4) Create the password for the database in both the instance

Execute the below in node1
$ orapwd file=$ORACLE_HOME/dbs/orapwmanzy1 password=****** entries=20


Execute the below in node2
$ orapwd file=$ORACLE_HOME/dbs/orapwmanzy2 password=****** entries=20

5) Startup the instance using rman to restore db, rman will create a dummy pfile and starts the instance inorder to restore the spfile.
Now restore the spfile.



rhelrac1-> export ORACLE_SID=manzy1
rhelrac1-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 19:19:12 2012

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

connected to target database (not started)

RMAN> startup

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initmanzy.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 01/06/2012 19:19:33
ORA-00205: error in identifying control file, check alert log for more info


#### Restore the spfile to an pfile.

RMAN> restore spfile to pfile '/u01/initmanzy1.ora' from '/u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp';

Starting restore at 09-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-JAN-12

RMAN> exit;


##### Now create the directories in asm diskgroup to store your spfile,controlfile, datafiles and onlinelogfiles.

$ export ORACLE_SID=+ASM1
$ asmcmd

ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 3858 675 0 675 0 DATA1/
MOUNTED NORMAL N N 512 4096 1048576 3765 2361 1255 553 0 DATA2/
MOUNTED NORMAL N N 512 4096 1048576 3858 3075 1286 894 0 DATA3/
MOUNTED EXTERN N N 512 4096 1048576 3858 3304 0 3304 0 FLASH/




#### Once the pfile is restored edit the pfile with appropirate RAC parameter as below.


*.cluster_database_instances=2
*.cluster_database=TRUE
*.control_files='+DATA1/manzy/controlfiles/control01.ctl','+DATA1/manzy/controlfiles/control02.ctl'
manzy1.instance_name='manzy1'
manzy2.instance_name='manzy2'
manzy1.instance_number=1
manzy2.instance_number=2
manzy1.thread=1
manzy2.thread=2
manzy1.undo_tablespace='undotbs1'
manzy2.undo_tablespace='undotbs2'

Note : Also change the adump, bdump, cdump, udump directories accordingly.

###### After editing the pfile, create a spfile using the modified pfile.

rhelrac1-> export ORACLE_SID=manzy1
rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 9 19:38:25 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create spfile='+DATA1/manzy/spfiles/spfilemanzy.ora' from pfile = '/u01/initmanzy1.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

###### Now create a pfile in $ORACLE_HOME/dbs location on both the instance pointing to the
same spfile.

Node 1
$ cd $ORACLE_HOME/dbs
$ vi initmanzy1.ora

### add the below line alone in the pfile.
spfile=+DATA1/manzy/spfiles/spfilemanzy.ora

Node 2
$ cd $ORACLE_HOME/dbs
$ vi initmanzy2.ora


### add the below line alone in the pfile.
spfile=+DATA1/manzy/spfiles/spfilemanzy.ora


### Now restore the controlfile in node1.

rhelrac1-> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jan 9 19:43:54 2012

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 444596224 bytes

Fixed Size 1267980 bytes
Variable Size 130025204 bytes
Database Buffers 310378496 bytes
Redo Buffers 2924544 bytes

RMAN> restore controlfile from '/u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp';

Starting restore at 09-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 instance=manzy1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=+DATA1/manzy/controlfiles/control01.ctl
output filename=+DATA1/manzy/controlfiles/control02.ctl
Finished restore at 09-JAN-12

##### Mount the controlfile

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


##### Now remove all the details of backup repository which is stored in this controfile
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/datafiles_771873888_MANZY recid=1 stamp=771873889
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/datafiles_771873913_MANZY recid=2 stamp=771873913

RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /u01/datafiles_771873888_MANZY recid=1 stamp=771873889
2 2 1 1 EXPIRED DISK /u01/datafiles_771873913_MANZY recid=2 stamp=771873913

deleted backup piece
backup piece handle=/u01/datafiles_771873888_MANZY recid=1 stamp=771873889 recid=15 stamp=771874538
deleted backup piece
backup piece handle=/u01/datafiles_771873913_MANZY recid=2 stamp=771873913 recid=16 stamp=771874527
Deleted 2 EXPIRED objects


RMAN> list backup;


RMAN>

#### Now catalog the backup files which we have taken from the single instance.


RMAN> catalog start with '/u01/backupofmanzy/';


searching for all files that match the pattern /u01/backupofmanzy/

List of Files Unknown to the Database
=====================================
File Name: /u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874533_MANZY_0hn03nn5_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp
File Name: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874538_MANZY_0in03nna_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874533_MANZY_0hn03nn5_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp
File Name: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp
File Name: /u01/backupofmanzy/datafiles_771874538_MANZY_0in03nna_1_1.bkp


##### Now restore the datafiles to the asm diskgroup use the set newname command to rename the datafiles.

RMAN> run
2> {
3> set newname for datafile '/optware/oracle/oradata/manzy/system01.dbf' to '+DATA2/manzy/datafiles/system01.dbf';
4> set newname for datafile '/optware/oracle/oradata/manzy/undotbs01.dbf' to '+DATA2/manzy/datafiles/undotbs01.dbf';
5> set newname for datafile '/optware/oracle/oradata/manzy/sysaux01.dbf' to '+DATA3/manzy/datafiles/sysaux01.dbf';
6> set newname for datafile '/optware/oracle/oradata/manzy/users01.dbf' to '+DATA3/manzy/datafiles/users01.dbf';
7> restore database;
8> switch datafile all;
9> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA2/manzy/datafiles/undotbs01.dbf
restoring datafile 00003 to +DATA3/manzy/datafiles/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp tag=FULL_DB
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA2/manzy/datafiles/system01.dbf
restoring datafile 00004 to +DATA3/manzy/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp tag=FULL_DB
channel ORA_DISK_1: restore complete, elapsed time: 00:00:47
Finished restore at 09-JAN-12

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=772142636 filename=+DATA2/manzy/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=772142636 filename=+DATA2/manzy/datafiles/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=772142637 filename=+DATA3/manzy/datafiles/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=772142637 filename=+DATA3/manzy/datafiles/users01.dbf

RMAN> exit;

### Now we have restored the database to the new location i.e. asm diskgroup, now we need to rename the
online logfiles for that connect to the sqlplus and rename the files.



rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 9 20:04:55 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> select status from V$instance;

STATUS
------------
MOUNTED

SQL> select member from V$logfile;

MEMBER
--------------------------------------------------------------------------------
/optware/oracle/oradata/manzy/redo03.log
/optware/oracle/oradata/manzy/redo02.log
/optware/oracle/oradata/manzy/redo01.log


#### Rename the logfiles to point to the asm diskgroups.

SQL> alter database rename file '/optware/oracle/oradata/manzy/redo01.log' to '+FLASH/manzy/onlinelogfiles/redo01.log';

Database altered.

SQL> alter database rename file '/optware/oracle/oradata/manzy/redo02.log' to '+FLASH/manzy/onlinelogfiles/redo02.log';

Database altered.

SQL> alter database rename file '/optware/oracle/oradata/manzy/redo03.log' to '+FLASH/manzy/onlinelogfiles/redo03.log';

Database altered.


#### Now add the logfiles which will be used by the instance 2.


SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 NO CURRENT 464631 06-JAN-12
3 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0

SQL> alter database add logfile thread 2 group 4 '+FLASH/manzy/onlinelogfiles/redo04.log' size 52428800;

Database altered.

SQL> alter database add logfile thread 2 group 5 '+FLASH/manzy/onlinelogfiles/redo05.log' size 52428800;

Database altered.

SQL> alter database add logfile thread 2 group 6 '+FLASH/manzy/onlinelogfiles/redo06.log' size 52428800;

Database altered.

#### Open the database with resetlogs option.

SQL> alter database open resetlogs;

Database altered.


#### Recreate the temporary tablespace

SQL> create temporary tablespace temp2 tempfile '+DATA3/manzy/datafiles/temp02.dbf' size 50m;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.


### Enable the thread for the instance 2

SQL> alter database enable thread 2;

Database altered.


### Create the undo tablespace for the instance 2

SQL> create undo tablespace undotbs2 datafile '+DATA3/manzy/datafiles/undotbs02.dbf' size 50m;

Tablespace created.

SQL> exit;

##### Now add the database to the cluster registry (OCR) using the srvctl command.

$ srvctl add database -d manzy -o /u01/app/oracle/product/10.2.0/db_1 -p +DATA1/manzy/spfiles/spfilemanzy.ora
$ srvctl add instance -d manzy -i manzy1 -n rhelrac1
$ srvctl add instance -d manzy -i manzy2 -n rhelrac2

### Shutdown the database and start it using srvctl

$ srvctl stop database -d manzy
$ srvctl start database -d manzy


### Check the status of the instance.

$ srvctl status database -d manzy
Instance manzy1 is running on node rhelrac1
Instance manzy2 is running on node rhelrac2

#### Configure the tnsnames and listerners.



##### End of converting single instance to RAC Instance.