Oracle Cloud Infrastructure 2019 Architect Associate

Oracle Cloud Infrastructure Architect Associate

Oracle Cloud Infrastructure 2019 Architect Associate

Powerful Excel Utility for DBAs

Excel Utility for DBAs

This Excel utility has been very useful to all my colleagues at different places as well to my other DBA friends. Based on popular demand I am pleased to share this utility with a broader community of DBA team by sharing this to everyone.

This utility is pretty self explanatory. Due to privacy and security reason I have included sample database names. To begin validating this utility, you need to replace sample database names with your database name(s), which you can do by running a macro(ctrl+shift+T) which will read a TNSNames.ora file you provide as input.

Here is the usage included in the Excel file in the "Version" tab.

Usage: This WorkBook is useful for collecting information from multiple  Oracle  or  SQL Server databases in Excel WorkSheet. Initialization of macros  is very dependent on your current cursor (selected cell) position.  Query you enter in C2 and other cell should  NOT contain ";" at the end of the query string. Enhancement has been done to trim the trailing ";", if present. You should always validate that query you are going to execute against multiple database is syntactically correct.

There are couple macros in this WorkBook , most of them have shortcuts too. (Ref: List of Macros)

For collectData Macro:

You can enter multiple queries , one query in its own cell. Starting from C2, C3, C4, etc.

You can specify work sheet name for the  query results, if you omit it will autogenerate one for you. WorkSheetName must be specified in B2, B3, B4, etc. corresponding cells for the queries.

Queries must be followed by header line and then followed by Sr.No and Database Aliases (from your local TNSNAMES.ora file). Please refer to the "Sample" WorkSheet in this WorkBook.

Once ready with this setup press "Ctl+Shift+C" and will display Login Window for the common user name and password across all the databases. If you have separate username and/or password, use  column E and F to specify  username and password respectively. Select environment type; N => for Non Production, P => For production, A => For all environment. Once entered all this information this macro executes all the listed queries in the current WorkSheet for all the databases listed in the current WorkSheet only. All database must be consecutoive without any Blank lies and/or Sr.No.

WorkSheet containing result contains following additional information:

Date and Time in Cell A1.  Row 2 contains header name and Column A contains database name. Query executed is being stored as Comments for Cell A2.

Also, there will be text file generated in the directory where this Excel file with Macro is being stored, (e.g. DB2Excel4DP.xlsm.txt)  which can be used to watch the progress of the execution. It updates this file once it  completes execution for the databse (success/failure).

List of Macros:
MacroName		ShortCut	Description
collectData		Ctl+Shift+C	Execute queries and stores result in WorkSheets
compareResults		Ctl+Shift+R	Execute queries and stores result SideBySide
DeleteCNValidWrkSheet		Ctl+Shift+D	Delete CN Valid Worksheets (Autogenerated after each execution)
readTNSNamesAndList		Ctl+Shift+T	Reads TNSNames.ora file from the specified location and list all aliases in a new WorkSheet.

In order to get this file, as of now please feel free to send me email (patel.RiDham at GMail.com) I will share this file with you at the earliest feasible time.

Here is the permanent link to this Excel utility.

If you like this utility please feel free to leave your comments here as well as feel free to leave any suggestions/enhancements.

Cleanup /boot partition on Ubuntu

First check your kernel version, so you won't delete the in-use kernel image, running:

uname -r

Now run this command for a list of installed kernels:

dpkg --list 'linux-image*' | grep ^ii

and delete the kernels you don't want/need anymore by running this:

sudo apt-get remove linux-image-VERSION

Replace VERSION with the version of the kernel you want to remove.

When you're done removing the older kernels, you can run this to remove ever packages you won't need anymore:

sudo apt-get autoremove

And finally you can run this to update grub kernel list:

sudo update-grub

Following command is derived from the above post and customized for my own needs:

dpkg --list 'linux-image*' | grep ^ii | awk '{print "apt-get remove " $2}' | grep -v extra|sort| egrep "[0-9]-generic"|head -n -3 




Reference: https://askubuntu.com/questions/345588/what-is-the-safest-way-to-clean-up-boot-partition

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;
/

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>

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