Monday, April 27, 2015

Change max value for Oracle Sequence

After data refresh/copy from one environment to another, usually developer realizes that they are using sequence and need to change the sequence's max value. To perform this I have developed this handy script:

This script assumes that the executor of this script has DBA (or DBA like privileges). Ability to select from DBA views and alter any sequence.

set serveroutput on

declare
   seq_owner  varchar2(30) := upper('&1');
   seq_name   varchar2(30) := upper('&2');
   desiredVal number := &3;
   oldIncrVal number;
   diff       number;
   newDiff    number;
   sqlStmt    varchar2(4000);
   currSeqVal number;
begin
   select INCREMENT_BY into oldIncrVal from dba_sequences where SEQUENCE_OWNER = seq_owner and SEQUENCE_NAME = seq_name;
   sqlStmt := 'select ' || seq_owner || '.' || seq_name || '.nextval from dual';
   dbms_output.put_line(sqlStmt);
   execute immediate sqlStmt into currSeqVal;
   diff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff;
   execute immediate sqlStmt into currSeqVal;
   newDiff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal;
end;
/


Monday, April 6, 2015

DB_Unique_Name vs. DB_Name

I have used DB_Unique_Name when I create a standby databases. This morning one of my colleague, when duplicating database using the production backup on non-production cluster,  asked me the question why does files in the ASM are being created in folder with the old database name and not with the new database name. I reviewed and found out that DB_Unique_Name parameter was left unchanged which caused this issue. This prompted me to believe that DB_Unique_Name is being used for creating the OMF file structure.  This is not documented clearly in Oracle Documentation, but I found this nice article which explains the difference between DB_Name and DB_Unique_Name.

http://ora12c.blogspot.com/2012/08/difference-between-dbname-dbuniquename.html

SSRS Report Format options

SSRS reports can be exported in different format. Default text format is comma separated values. If we need different format we can do so by updating RSReportServer.config file.

On SSRS server add following entry to file D:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config.

The entry below needs to be added to the <Render> node:

<Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
        <OverrideNames>
            <Name Language="en-US">Text (Pipe Delimited)</Name>
        </OverrideNames>
        <Configuration>
          <DeviceInfo>
            <FieldDelimiter>|</FieldDelimiter>
            <FileExtension>txt</FileExtension>
          </DeviceInfo>
        </Configuration>

</Extension>

Tuesday, March 11, 2014

Database migration using SAN migration

Couple weeks back I posted about Database migration using SAN migration POC. Yesterday, we successfully migrated 13 databases from one RAC to another RAC. 

We followed the steps I described in earlier post with some minor tweaks. Tweaks were mainly due to the fact that earlier setup had some database instance names in uppercase and some in lower case. To bring consistency we changed all instance names to lower case. Similarly, archive log destination as well as RMAN backup directory were not consistent. For better administration purpose we changed all the directory names to lower case as well.


Migration preparation:

  1. Identify databases are being migrated.
  2. Collect the database and related database service configuration.
  3. Prepare commands to recreate the services as well adding databases to the OCR on the target cluster.
  4. Copy password files from existing cluster nodes to target cluster nodes.
  5. Created text initialization files and copied them to the target server.
  6. On the target server we modified the init files:
    1. Archive log destination
    2. Any other directory names (like audit dump destination)
    3. remote_listener parameter
    4. Changed instance names from uppercase to lowercase
  7. Copy init files from the existing cluster nodes to the target cluster nodes.
  8. Create entries in /etc/oratab for the databases to be migrated
On the day of migration:
  1. Application team shutdown the respective application services.
  2. Shutdown database(s) having files on the disk group being migrated.
  3. Unmount disk group - all the nodes of existing cluster. (umount <DiskGroupName>)
    1. Here we shutdown the entire cluster as we are migrating all databases from this cluster
  4. SysAdmin detached SAN LUN from the existing cluster.
  5. SysAdmin presented same LUNs to the new target cluster nodes.
  6. ASM admin/DBA mounted the diskgroup by using asmcmd utility (mount <DiskGroupName>)
  7. Repeat following steps for each database
    1. Start databases using the modified pfile through sqlplus
    2. Create spfile based on the pfile used to startup the database.
    3. Shutdown database
    4. Startup using spfile through sqlplus
    5. Apply catbundle for the PSU version, we installed on target cluster which was not on the source cluster
    6. Validate modified parameters
  8. Add database to the OCR using commands identified in Step#3 [Changed database instance names to lower case].
  9. Start database using srvctl.
  10. Add database service(s), if any identified in Step#2 and Step#3
  11. As a part of system build, we did perform RAC System test. Still here, we performed service fail-over validation. 
  12. We performed control reboot of both the nodes at the same time.
  13. After everything came online we executed script [validate_svc_restart.sh] to ensure that service are running on the preferred nodes.
  14. We also, updated the RMAN configuration to accommodate the change in directory names.
Script validate_svc_restart.sh:

#!/bin/ksh

ASM_SID="`cat $ORATAB |awk -F: '/^\+ASM/{print $1}'`"; export ASM_SID
. /oracle/orabase/admin/common/oraprofile.sh ${ASM_SID} 1>/dev/null 2>/dev/null

for svc in `crsctl stat res -t | grep svc | grep -v preconnect `
do
   echo " * * * * Processing ${svc}"
   export DBN=`echo ${svc}|cut -d. -f2`
   export SRVC=`echo ${svc}|cut -d. -f3,4,5 | sed s/\.svc\$//`
   for PRFRD_SVR in `srvctl config service -d ${DBN} -s ${SRVC} | grep Preferred | cut -d: -f2|tr  , " "`
   do
      echo "      * * * * Validating preferred instance: ${PRFRD_SVR}"
      if [ `srvctl status service -d ${DBN} -s ${SRVC} | grep ${PRFRD_SVR} | wc -l` -lt 1 ]
      then
          echo " Service[${SRVC}] is NOT running on Preferred server: ${PRFRD_SVR}"
          srvctl stop service -d ${DBN} -s ${SRVC}
          srvctl start service -d ${DBN} -s ${SRVC}
          srvctl status service -d ${DBN} -s ${SRVC}
      fi
   done

done



Wednesday, February 26, 2014

Database migration using SAN migration technique

We are planning migrating databases from one RAC to another RAC. For this purpose we are planning to use SAN migration technique.

This is a proof of concept (POC), I will update this BLOG as we make progress.

1. For POC, we will create a new diskgroup [san_poc] on existing RAC.

old01 $ kfod op=disks disks=raw
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
   1:      20473 Mb /dev/oracleasm/disks/ASM_0001            oracle   oinstall
   2:      20473 Mb /dev/oracleasm/disks/ASM_0002            oracle   oinstall
   3:      20473 Mb ORCL:ASM_0001                            <unknown> <unknown>
   4:      20473 Mb ORCL:ASM_0002                            <unknown> <unknown>
old02 $ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     61440    18734                0           18734              0             Y  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576     40960    40455                0           40455              0             N  FRA1/
old01 $ asmcmd -p
ASMCMD [+] > mkdg '<dg name="san_poc" redundancy="external"><dsk string="/dev/oracleasm/disks/ASM_0001"/><dsk string="/dev/oracleasm/disks/ASM_0002"/></dg>'
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     61440    18734                0           18734              0             Y  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576     40960    40455                0           40455              0             N  FRA1/
MOUNTED  EXTERN  N         512   4096  1048576     40946    40894                0           40894              0             N  SAN_POC/
ASMCMD [+] >

Before we create a new database on this new disk group, we need to mount this new diskgroup on another node:

old02 $ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     61440    18734                0           18734              0             Y  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576     40960    40455                0           40455              0             N  FRA1/
ASMCMD [+] > mount san_poc
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     61440    18734                0           18734              0             Y  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576     40960    40455                0           40455              0             N  FRA1/
MOUNTED  EXTERN  N         512   4096  1048576     40946    40851                0           40851              0             N  SAN_POC/
ASMCMD [+] >

2. Create a database: Here I used dbca to create database quickly with all database files - redo log, control files, data files, temp files, etc. are on SAN_POC disk group.

Also, I created a new user and table with couple rows in it. This will help to validate the database status after migration for the end user objects. To keep this post concise, I am omitting details for these trivial tasks. 

For the new database, created a new service using following command:

old01 $ srvctl add service -d poc -s poc_svc -r poc1 -a poc2 -P PRECONNECT -y AUTOMATIC -q TRUE -e select -m basic -z 10 -w 5
old01 $ srvctl status service -d poc
Service poc_svc is not running.
old01 $ srvctl start service -d poc  -s poc_svc
old01 $ srvctl status service -d poc

Service poc_svc is running on instance(s) poc1


3. As a prep work, we can copy database initialization file and password file from ${ORACLE_HOME}/dbs directory to the new cluster. Also, need to create audit dump destination, archive or any other OS directory structure that's used by database.

4. Next step is to shutdown database and un-mount disk group from all the nodes.



srvctl stop database -d poc

ASMCMD [+] > umount san_poc

5. Worked with system admin to detach LUNs from the existing cluster and present them to the new cluster. In this process we identified that there were identical ASM LUN name on both the servers. To resolve this, we renamed LUN using asmlib on one of the existing cluster node. On the other node we used oracleasm scandisks command. DO NOT use "oracleasm deletedisk" command. System admin released LUNs from OS as well as through storage admin console.

LUNs released from the existing cluster were presented to the new cluster. On both the new cluster nodes execute "oracleasm scandisks".

Now, this was the new excitement phase...


6. I logged in to new cluster node. To validate disk is intact, I ran kfed command:

$ kfed dev=/dev/oracleasm/disks/ASM_0005 op=read cnt=1 blknum=0
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  2930213453 ; 0x00c: 0xaea7824d
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr: ORCLDISKASM_0005 ; 0x000: length=16
kfdhdb.driver.reserved[0]:   1598903105 ; 0x008: 0x5f4d5341
kfdhdb.driver.reserved[1]:    892350512 ; 0x00c: 0x35303030
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                168820736 ; 0x020: 0x0a100000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:            SAN_POC_0000 ; 0x028: length=12
kfdhdb.grpname:                 SAN_POC ; 0x048: length=7
kfdhdb.fgname:             SAN_POC_0000 ; 0x068: length=12

Hold my breath.... attempt to mount diskgroup.

mount san_poc

And I was successful. Repeated same step on another node. 

7. Now time to making sure that we can start database[POC] that was residing on this diskgroup. Before starting database, added database to the OCR by issuing following commands:

new01 $ srvctl add database -d poc  -o /oracle/orabase/product/11.2.0.3 -c RAC -y AUTOMATIC -p '+SAN_POC/poc/spfilepoc.ora' -a "SAN_POC"
new01 $ srvctl add instance -d poc -i poc1 -n new01
new01 $ srvctl add instance -d poc -i poc2 -n new02
new01 $ srvctl add service -d poc -s poc_svc -r poc1 -a poc2 -P PRECONNECT -y AUTOMATIC -q TRUE -e select -m basic -z 10 -w 5
new01 $ srvctl start database -d poc

I overjoyed this as database started without any issues!!! [Though, I had to create audit dump directory...]. 

8. "LISTENER" parameter needs to be modified in the database server parameter file.

SQL> alter system set remote_listener='new-scan1:1521' sid='*' scope=spfile;

System altered.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=new02-vip)(PORT=1521))))' sid='poc2' scope=spfile;

System altered.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=new01-vip)(PORT=1521))))' sid='poc1' scope=spfile;

Restarted database and was able to connect to this POC database from the remote client.

Summary:

Assumptions:

  • OS and Oracle software binaries are identical on existing as well as target cluster nodes.
  • ASM disk groups doesn't contain voting/OCR files
  • ASM disk groups - planned to migrate contains only database files.
  • ASM disk groups planned to migrate are self-contained within the disk groups being migrated.

Migration Steps:

  1. Identify databases are being migrated.
  2. Collect the database and related database service configuration.
  3. Prepare commands to recreate the services as well adding databases to the OCR on the target cluster.
  4. Copy password files from existing cluster nodes to target cluster nodes.
  5. Copy init files from the existing cluster nodes to the target cluster nodes.
  6. Create entries in /etc/oratab for the databases to be migrated
  7. Shutdown database(s) having files on the disk group being migrated.
  8. Unmount disk group - all the nodes of existing cluster. (umount <DiskGroupName>)
  9. SysAdmin detach SAN LUN from the existing cluster.
  10. SysAdmin presents same LUNs to the new target cluster nodes.
  11. ASM admin/DBA will mount the diskgroup by using asmcmd utility (mount <DiskGroupName>)
  12. Add database to the OCR using commands identified in Step#3.
  13. Start database using srvctl.
  14. Modify local_listener and remote_listener parameter [Node vip and scan name needs to be updated]
  15. Add database service(s), if any identified in Step#2 and Step#3
  16. Modify asm_diskgroups parameter in spfile for all the ASM instances to ensure that DISKGROUPS migrated through SAN migration gets mounted in case of node reboot.

Thursday, February 20, 2014

Creating ASM diskgroup using "asmcmd"

Created a "TEMP" diskgroup consisting single LUN with external redundancy.

ASMCMD [+] >  mkdg '<dg name="temp" redundancy="external"><dsk string="ORCL:ASM_0004"/></dg>'

If this is a RAC, then this diskgroup needs to be mounted on all other nodes manually. You can do so by starting asmcmd on other nodes:

ASMCMD [+] > mount TEMP
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     20473    20380                0           20380              0             N  TEMP/
MOUNTED  NORMAL  N         512   4096  1048576     61419    60493            20473           20010              0             Y  VOTE_DG/