tag:blogger.com,1999:blog-4045951770760889252024-03-13T00:00:35.354-07:00Oracle DBADharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-404595177076088925.post-9276744214524103002021-12-10T07:45:00.000-08:002021-12-10T07:45:55.012-08:00OCI Cli<div dir="ltr" style="text-align: left;word-wrap: break-word; white-space: pre-wrap;font-family:monospace; " trbidi="on">
These steps are performed on OEL VMBox
Install python3:
yum install -y python3*
[root@localhost bin]# ls -l python*
lrwxrwxrwx. 1 root root 7 May 31 2019 <span style="color: cyan;">python</span> -> <span style="color: #00af00;">python2</span>
lrwxrwxrwx. 1 root root 9 May 31 2019 <span style="color: cyan;">python2</span> -> <span style="color: #00af00;">python2.7</span>
-rwxr-xr-x. 1 root root 7216 Apr 9 2019 <span style="color: #00af00;">python2.7</span>
lrwxrwxrwx. 1 root root 9 Mar 26 18:00 <span style="color: cyan;">python3</span> -> <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6m</span>
[root@localhost bin]# ln -s -f python3 python
[root@localhost bin]# ls -l python*
lrwxrwxrwx. 1 root root 7 Mar 26 18:01 <span style="color: cyan;">python</span> -> <span style="color: #00af00;">python3</span>
lrwxrwxrwx. 1 root root 9 May 31 2019 <span style="color: cyan;">python2</span> -> <span style="color: #00af00;">python2.7</span>
-rwxr-xr-x. 1 root root 7216 Apr 9 2019 <span style="color: #00af00;">python2.7</span>
lrwxrwxrwx. 1 root root 9 Mar 26 18:00 <span style="color: cyan;">python3</span> -> <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6m</span>
[root@localhost bin]# python -V
Python 3.6.8
This broke my "yum" so I had to revert back:
[root@localhost bin]# ls -l python*
lrwxrwxrwx. 1 root root 7 Mar 26 18:01 <span style="color: cyan;">python</span> -> <span style="color: #00af00;">python3</span>
lrwxrwxrwx. 1 root root 9 May 31 2019 <span style="color: cyan;">python2</span> -> <span style="color: #00af00;">python2.7</span>
-rwxr-xr-x. 1 root root 7216 Apr 9 2019 <span style="color: #00af00;">python2.7</span>
lrwxrwxrwx. 1 root root 9 Mar 26 18:00 <span style="color: cyan;">python3</span> -> <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6</span>
-rwxr-xr-x. 2 root root 11336 Aug 7 2019 <span style="color: #00af00;">python3.6m</span>
[root@localhost bin]# ln -s -f python2 python
[root@localhost bin]# python -V
Python 2.7.5
[oracle@localhost ~]$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 14620 100 14620 0 0 39270 0 --:--:-- --:--:-- --:--:-- 39407
******************************************************************************
You have started the OCI CLI Installer in interactive mode. If you do not wish
to run this in interactive mode, please include the --accept-all-defaults option.
If you have the script locally and would like to know more about
input options for this script, then you can run:
./install.sh -h
If you would like to know more about input options for this script, refer to:
https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst
******************************************************************************
Downloading Oracle Cloud Infrastructure CLI install script from https://raw.githubusercontent.com/oracle/oci-cli/v2.9.3/scripts/install/install.py to /tmp/oci_cli_install_tmp_ipBj.
######################################################################## 100.0%
Running install script.
python3 /tmp/oci_cli_install_tmp_ipBj
-- Verifying Python version.
-- Python version 3.6.8 okay.
===> In what directory would you like to place the install? (leave blank to use '/home/oracle/lib/oracle-cli'):
-- Creating directory '/u01/userhome/oracle/lib/oracle-cli'.
-- We will install at '/u01/userhome/oracle/lib/oracle-cli'.
===> In what directory would you like to place the 'oci' executable? (leave blank to use '/home/oracle/bin'):
-- The executable will be in '/u01/userhome/oracle/bin'.
===> In what directory would you like to place the OCI scripts? (leave blank to use '/home/oracle/bin/oci-cli-scripts'):
-- Creating directory '/u01/userhome/oracle/bin/oci-cli-scripts'.
-- The scripts will be in '/u01/userhome/oracle/bin/oci-cli-scripts'.
===> Currently supported optional packages are: ['db (will install cx_Oracle)']
What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any optional packages)?: db
-- The optional packages installed will be 'db'.
-- Trying to use python3 venv.
-- Executing: ['/usr/bin/python3', '-m', 'venv', '/u01/userhome/oracle/lib/oracle-cli']
-- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--upgrade', 'pip']
Collecting pip
Downloading https://files.pythonhosted.org/packages/54/0c/d01aa759fdc501a58f431eb594a17495f15b88da142ce14b5845662c13f3/pip-20.0.2-py2.py3-none-any.whl (1.4MB)
100% |████████████████████████████████| 1.4MB 1.4MB/s
Installing collected packages: pip
Found existing installation: pip 9.0.3
Uninstalling pip-9.0.3:
Successfully uninstalled pip-9.0.3
Successfully installed pip-20.0.2
-- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpm_j8_sv_', 'wheel', '--upgrade']
Collecting wheel
Downloading wheel-0.34.2-py2.py3-none-any.whl (26 kB)
Installing collected packages: wheel
Successfully installed wheel-0.34.2
-- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpm_j8_sv_', 'oci_cli[db]', '--upgrade']
Collecting oci_cli[db]
Downloading oci_cli-2.9.8-py2.py3-none-any.whl (7.5 MB)
|████████████████████████████████| 7.5 MB 2.4 MB/s
Collecting cryptography==2.8
Downloading cryptography-2.8-cp34-abi3-manylinux2010_x86_64.whl (2.3 MB)
|████████████████████████████████| 2.3 MB 27.4 MB/s
Collecting configparser>=4.0.2
Downloading configparser-5.0.0-py3-none-any.whl (22 kB)
Collecting python-dateutil<3.0.0,>=2.5.3
Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB)
|████████████████████████████████| 227 kB 44.6 MB/s
Collecting pytz>=2016.10
Downloading pytz-2019.3-py2.py3-none-any.whl (509 kB)
|████████████████████████████████| 509 kB 42.4 MB/s
Collecting idna<2.7,>=2.5
Downloading idna-2.6-py2.py3-none-any.whl (56 kB)
|████████████████████████████████| 56 kB 2.3 MB/s
Collecting PyYAML==5.1.2
Downloading PyYAML-5.1.2.tar.gz (265 kB)
|████████████████████████████████| 265 kB 36.8 MB/s
Collecting jmespath==0.9.4
Downloading jmespath-0.9.4-py2.py3-none-any.whl (24 kB)
Collecting oci==2.12.1
Downloading oci-2.12.1-py2.py3-none-any.whl (3.7 MB)
|████████████████████████████████| 3.7 MB 41.7 MB/s
Collecting arrow==0.14.7
Downloading arrow-0.14.7-py2.py3-none-any.whl (39 kB)
Collecting certifi
Downloading certifi-2019.11.28-py2.py3-none-any.whl (156 kB)
|████████████████████████████████| 156 kB 62.2 MB/s
Collecting retrying==1.3.3
Downloading retrying-1.3.3.tar.gz (10 kB)
Collecting click==6.7
Downloading click-6.7-py2.py3-none-any.whl (71 kB)
|████████████████████████████████| 71 kB 6.4 MB/s
Collecting pyOpenSSL==18.0.0
Downloading pyOpenSSL-18.0.0-py2.py3-none-any.whl (53 kB)
|████████████████████████████████| 53 kB 3.7 MB/s
Collecting six==1.14.0
Downloading six-1.14.0-py2.py3-none-any.whl (10 kB)
Collecting terminaltables==3.1.0
Downloading terminaltables-3.1.0.tar.gz (12 kB)
Collecting cx-Oracle==7.0; extra == "db"
Downloading cx_Oracle-7.0.0-cp36-cp36m-manylinux1_x86_64.whl (675 kB)
|████████████████████████████████| 675 kB 54.6 MB/s
Collecting cffi!=1.11.3,>=1.8
Downloading cffi-1.14.0-cp36-cp36m-manylinux1_x86_64.whl (399 kB)
|████████████████████████████████| 399 kB 36.9 MB/s
Collecting pycparser
Downloading pycparser-2.20-py2.py3-none-any.whl (112 kB)
|████████████████████████████████| 112 kB 41.2 MB/s
Building wheels for collected packages: PyYAML, retrying, terminaltables
Building wheel for PyYAML (setup.py) ... done
Created wheel for PyYAML: filename=PyYAML-5.1.2-cp36-cp36m-linux_x86_64.whl size=44104 sha256=8eadcc33892c992b1c071e8f0e7623945acebe5eece35299f4c448571ba05366
Stored in directory: /tmp/tmpm_j8_sv_/wheels/d8/9b/e7/75af463b873c119dd444151fc54a8e190c87993593e1fa194a
Building wheel for retrying (setup.py) ... done
Created wheel for retrying: filename=retrying-1.3.3-py3-none-any.whl size=11430 sha256=72069b956abf995176390aa9a39d594c55523244a38da0942e45a9a787118746
Stored in directory: /tmp/tmpm_j8_sv_/wheels/ac/cb/8a/b27bf6323e2f4c462dcbf77d70b7c5e7868a7fbe12871770cf
Building wheel for terminaltables (setup.py) ... done
Created wheel for terminaltables: filename=terminaltables-3.1.0-py3-none-any.whl size=15354 sha256=3b6260fbdb92de3b45d625b05bca4eace5de000e9b55f36badabb9d90c3a0a8f
Stored in directory: /tmp/tmpm_j8_sv_/wheels/86/1b/58/c23af2fe683acd8edc15d5a1268f0242be1ff2cf827fe34737
Successfully built PyYAML retrying terminaltables
<span style="color: #cc0000;">ERROR: oci 2.12.1 has requirement configparser==4.0.2, but you'll have configparser 5.0.0 which is incompatible.</span>
Installing collected packages: six, pycparser, cffi, cryptography, configparser, python-dateutil, pytz, idna, PyYAML, jmespath, pyOpenSSL, certifi, oci, arrow, retrying, click, terminaltables, cx-Oracle, oci-cli
Successfully installed PyYAML-5.1.2 arrow-0.14.7 certifi-2019.11.28 cffi-1.14.0 click-6.7 configparser-5.0.0 cryptography-2.8 cx-Oracle-7.0.0 idna-2.6 jmespath-0.9.4 oci-2.12.1 oci-cli-2.9.8 pyOpenSSL-18.0.0 pycparser-2.20 python-dateutil-2.8.1 pytz-2019.3 retrying-1.3.3 six-1.14.0 terminaltables-3.1.0
===> Modify profile to update your $PATH and enable shell/tab completion now? (Y/n): Y
===> Enter a path to an rc file to update (leave blank to use '/home/oracle/.bashrc'):
-- Backed up '/u01/userhome/oracle/.bashrc' to '/u01/userhome/oracle/.bashrc.backup'
-- Tab completion set up complete.
-- If tab completion is not activated, verify that '/u01/userhome/oracle/.bashrc' is sourced by your shell.
--
-- ** WARNING: Other 'oci' executables are on your $PATH. **
-- Conflicting paths: /home/oracle/bin/oci, /home/oracle/bin/oci, /home/oracle/bin/oci, /home/oracle/bin/oci
-- You can run this installation of the CLI with '/u01/userhome/oracle/bin/oci'.
--
-- ** Run `exec -l $SHELL` to restart your shell. **
--
-- Installation successful.
-- Run the CLI with /u01/userhome/oracle/bin/oci --help
[oracle@localhost ~]$
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-20831259152642263732020-04-29T14:30:00.000-07:002020-04-29T14:30:59.874-07:00Oracle Cloud Infrastructure 2019 Architect Associate<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
Oracle Cloud Infrastructure Architect Associate<br />
<br />
</div>
<div data-iframe-height="270" data-iframe-width="150" data-share-badge-host="https://www.youracclaim.com" data-share-badge-id="4984e737-9b92-412b-acbd-4e318e6732b3">
</div>
<script async="" src="//cdn.youracclaim.com/assets/utilities/embed.js" type="text/javascript"></script>
<a href="https://www.youracclaim.com/badges/4984e737-9b92-412b-acbd-4e318e6732b3/public_url">Oracle Cloud Infrastructure 2019 Architect Associate</a>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-13026146105652459962019-10-09T12:44:00.001-07:002019-11-14T10:13:59.750-08:00Powerful Excel Utility for DBAs<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 style="text-align: left;">
<span style="font-family: "verdana" , sans-serif;">Excel Utility for DBAs</span></h2>
<br />
<span style="font-family: "verdana" , sans-serif;">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.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">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.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Here is the usage included in the Excel file in the "Version" tab.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<br />
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: bold;">Usage</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal;">:</span><span style="color: black; font-family: "calibri";"> </span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal;">This WorkBook</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;"> is</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal;"> useful for collecting information
from multiple<span style="mso-spacerun: yes;"> </span></span><span style="color: red; font-family: "calibri"; font-style: normal; font-weight: bold;">Oracle<span style="mso-spacerun: yes;"> </span>or<span style="mso-spacerun: yes;"> </span>SQL
Server</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal;">
databases in</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">
Excel WorkSheet. Initialization of macros<span style="mso-spacerun: yes;">
</span>is very dependent on your current cursor (selected cell) position.<span style="mso-spacerun: yes;"> </span>Query you enter in C2 and other cell should<span style="mso-spacerun: yes;"> </span>NOT contain ";" at the end of the
query string. </span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: bold; vertical-align: baseline;">Enhancement
has been done to trim the trailing ";"</span><span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">,
if present. You should always validate that query you are going to execute
against multiple database is syntactically correct.</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
</div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">There are couple macros in this WorkBook
, most of them have shortcuts too. (Ref: List of Macros)</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
</div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">For collectData Macro:</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">You can enter multiple queries , one
query in its own cell. Starting from C2, C3, C4, etc. </span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">You can specify work sheet name for
the<span style="mso-spacerun: yes;"> </span>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.</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">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.</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
</div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">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<span style="mso-spacerun: yes;"> </span>column E and F to
specify<span style="mso-spacerun: yes;"> </span>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.</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
</div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">WorkSheet containing result contains
following additional information:</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">Date and Time in Cell A1.<span style="mso-spacerun: yes;"> </span>Row 2 contains header name and Column A
contains database name. Query executed is being stored as Comments for Cell A2.</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
</div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-style: normal; font-weight: normal; vertical-align: baseline;">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).</span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-size: 11.0pt; font-style: normal; font-weight: normal; vertical-align: baseline;"><br /></span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-size: 11.0pt; font-style: normal; font-weight: normal; vertical-align: baseline;"><br /></span></div>
<div style="language: en-US; margin-bottom: 0pt; margin-left: 0in; margin-top: 0pt; text-indent: 0in;">
<span style="color: black; font-family: "calibri"; font-size: 11.0pt; font-style: normal; font-weight: normal; vertical-align: baseline;"></span></div>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 614px;">
</table>
<br />
<span style="font-family: "verdana" , sans-serif;"></span><br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 614px;"><tbody>
<tr height="18" style="height: 13.5pt;">
<td class="xl77" height="18" style="height: 13.5pt; width: 86pt;" width="115"><span style="font-family: "verdana" , sans-serif;">List of
Macros:</span></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
<td class="xl76" style="width: 64pt;" width="85"></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
<td class="xl76" style="width: 52pt;" width="69"></td>
</tr>
<tr height="17" style="height: 12.75pt;">
<td class="xl89" colspan="2" height="17" style="height: 12.75pt;"><span style="font-family: "verdana" , sans-serif;">MacroName</span></td>
<td class="xl78" style="border-left: none;"><span style="font-family: "verdana" , sans-serif;">ShortCut</span></td>
<td class="xl91" colspan="5" style="border-left: none; border-right: 1.0pt solid black;"><span style="font-family: "verdana" , sans-serif;">Description</span></td>
</tr>
<tr height="17" style="height: 12.75pt;">
<td class="xl81" colspan="2" height="17" style="height: 12.75pt;"><span style="font-family: "verdana" , sans-serif;">collectData</span></td>
<td class="xl79" style="border-left: none; border-top: none;"><span style="font-family: "verdana" , sans-serif;">Ctl+Shift+C</span></td>
<td class="xl83" colspan="5" style="border-left: none; border-right: 1.0pt solid black; width: 260pt;" width="345"><span style="font-family: "verdana" , sans-serif;">Execute queries and stores result in WorkSheets</span></td>
</tr>
<tr height="17" style="height: 12.75pt;">
<td class="xl81" colspan="2" height="17" style="height: 12.75pt;"><span style="font-family: "verdana" , sans-serif;">compareResults</span></td>
<td class="xl79" style="border-left: none; border-top: none;"><span style="font-family: "verdana" , sans-serif;">Ctl+Shift+R</span></td>
<td class="xl83" colspan="5" style="border-left: none; border-right: 1.0pt solid black; width: 260pt;" width="345"><span style="font-family: "verdana" , sans-serif;">Execute queries and stores result SideBySide</span></td>
</tr>
<tr height="32" style="height: 24.0pt; mso-height-source: userset;">
<td class="xl81" colspan="2" height="32" style="height: 24.0pt;"><span style="font-family: "verdana" , sans-serif;">DeleteCNValidWrkSheet</span></td>
<td class="xl79" style="border-left: none; border-top: none;"><span style="font-family: "verdana" , sans-serif;">Ctl+Shift+D</span></td>
<td class="xl83" colspan="5" style="border-left: none; border-right: 1.0pt solid black; width: 260pt;" width="345"><span style="font-family: "verdana" , sans-serif;">Delete CN Valid Worksheets (Autogenerated after
each execution)</span></td>
</tr>
<tr height="38" style="height: 28.5pt; mso-height-source: userset;">
<td class="xl85" colspan="2" height="38" style="height: 28.5pt;"><span style="font-family: "verdana" , sans-serif;">readTNSNamesAndList</span></td>
<td class="xl80" style="border-left: none; border-top: none;"><span style="font-family: "verdana" , sans-serif;">Ctl+Shift+T</span></td>
<td class="xl87" colspan="5" style="border-left: none; border-right: 1.0pt solid black; width: 260pt;" width="345"><span style="font-family: "verdana" , sans-serif;">Reads TNSNames.ora file from the specified
location and list all aliases in a new WorkSheet.</span></td>
</tr>
</tbody></table>
<br />
<div>
<span style="font-family: "verdana" , sans-serif;">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.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">Here is the permanent link to <a href="https://filedn.com/l91Iy3eQ0DAFiWvv1G0Tlou/tech/DB2Excel4DP.xlsm" rel="nofollow" target="_blank">this Excel utility</a></span><span style="font-family: verdana, sans-serif;">.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">If you like this utility please feel free to leave your comments here as well as feel free to leave any suggestions/enhancements.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<br /></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-73475192699506469222018-06-07T14:05:00.000-07:002018-06-07T14:08:24.140-07:00Cleanup /boot partition on Ubuntu<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
First check your kernel version, so you won't delete the in-use kernel image, running:</div>
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">uname -r
</code></pre>
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
Now run this command for a list of installed kernels:</div>
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">dpkg --list 'linux-image*' | grep ^ii
</code></pre>
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
and delete the kernels you don't want/need anymore by running this:</div>
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">sudo apt-get remove linux-image-VERSION
</code></pre>
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
Replace VERSION with the version of the kernel you want to remove.</div>
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
When you're done removing the older kernels, you can run this to remove ever packages you won't need anymore:</div>
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">sudo apt-get autoremove
</code></pre>
<div style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #111111; font-family: Ubuntu, Arial, "libra sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
And finally you can run this to update grub kernel list:</div>
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">sudo update-grub</code></pre>
<span style="color: #111111; font-family: "consolas" , "menlo" , "monaco" , "lucida console" , "liberation mono" , "dejavu sans mono" , "bitstream vera sans mono" , "courier new" , monospace , sans-serif;"><span style="font-size: 13px; white-space: pre;"><b>Following command is derived from the above post and customized for my own needs:</b></span></span><br />
<pre style="background-color: #eff0f1; border: 0px; box-sizing: inherit; color: #111111; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;">dpkg --list 'linux-image*' | grep ^ii | awk '{print "apt-get remove " $2}' | grep -v extra|sort| egrep "[0-9]-generic"|head -n -3 </pre>
<div>
<code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><br /></code></div>
<div>
<code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">Reference: <a href="https://askubuntu.com/questions/345588/what-is-the-safest-way-to-clean-up-boot-partition">https://askubuntu.com/questions/345588/what-is-the-safest-way-to-clean-up-boot-partition</a></code></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-64041415606464803382015-04-27T12:00:00.002-07:002015-04-27T12:00:57.133-07:00Change max value for Oracle Sequence <div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">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:</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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.</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">set serveroutput on</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">declare</span><br />
<span style="font-family: Courier New, Courier, monospace;"> seq_owner varchar2(30) := upper('&1');</span><br />
<span style="font-family: Courier New, Courier, monospace;"> seq_name varchar2(30) := upper('&2');</span><br />
<span style="font-family: Courier New, Courier, monospace;"> desiredVal number := &3;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> oldIncrVal number;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> diff number;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> newDiff number;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> sqlStmt varchar2(4000);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> currSeqVal number;</span><br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> select INCREMENT_BY into oldIncrVal from dba_sequences where SEQUENCE_OWNER = seq_owner and SEQUENCE_NAME = seq_name;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> sqlStmt := 'select ' || seq_owner || '.' || seq_name || '.nextval from dual';</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_output.put_line(sqlStmt);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> execute immediate sqlStmt into currSeqVal;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> diff := desiredVal - currSeqVal;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> execute immediate sqlStmt into currSeqVal;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> newDiff := desiredVal - currSeqVal;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal;</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<div>
<br /></div>
<div>
<br /></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com1tag:blogger.com,1999:blog-404595177076088925.post-77759375897725565922015-04-06T09:30:00.000-07:002015-04-06T09:30:50.833-07:00DB_Unique_Name vs. DB_Name<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">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.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><a href="http://ora12c.blogspot.com/2012/08/difference-between-dbname-dbuniquename.html">http://ora12c.blogspot.com/2012/08/difference-between-dbname-dbuniquename.html</a></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-56028457911189097412015-04-06T09:28:00.000-07:002015-04-06T09:28:28.933-07:00SSRS Report Format options<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="text-align: left;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">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 </span><span style="font-family: Verdana, sans-serif; font-size: x-small;">RSReportServer.config file.</span></div>
<div class="MsoNormal" style="text-align: left;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal" style="text-align: left;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">On SSRS server add following
entry to file D:\Program Files\Microsoft SQL
Server\MSRS11.MSSQLSERVER\Reporting
Services\ReportServer\RSReportServer.config. <o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: left;">
<br /></div>
<div class="MsoNormal" style="text-align: left;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">The entry below needs to be
added to the <b><Render> </b>node:</span></div>
<div class="MsoNormal" style="text-align: left;">
<br /></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<b><span style="font-family: 'Courier New'; font-size: 9pt;"><Extension</span></b><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;"> </span><span style="color: #000066; font-family: "Courier New"; font-size: 9.0pt;">Name</span><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;">=</span><span style="color: red; font-family: "Courier New"; font-size: 9.0pt;">"PIPE"</span><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;"> </span><span style="color: #000066; font-family: "Courier New"; font-size: 9.0pt;">Type</span><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;">=</span><span style="color: red; font-family: "Courier New"; font-size: 9.0pt;">"Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"</span><b><span style="font-family: 'Courier New'; font-size: 9pt;">></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><OverrideNames></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><Name</span></b><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;"> </span><span style="color: #000066; font-family: "Courier New"; font-size: 9.0pt;">Language</span><span style="color: #009900; font-family: "Courier New"; font-size: 9.0pt;">=</span><span style="color: red; font-family: "Courier New"; font-size: 9.0pt;">"en-US"</span><b><span style="font-family: 'Courier New'; font-size: 9pt;">></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">Text (Pipe Delimited)</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></Name></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></OverrideNames></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><Configuration></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><DeviceInfo></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><FieldDelimiter></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">|</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></FieldDelimiter></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"><FileExtension></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">txt</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></FileExtension></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></DeviceInfo></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 12.0pt;">
<span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;">
</span><b><span style="font-family: 'Courier New'; font-size: 9pt;"></Configuration></span></b><span style="color: #222222; font-family: "Courier New"; font-size: 9.0pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<b><span style="font-family: 'Courier New'; font-size: 9pt;"></Extension></span></b><span style="color: #1f497d;"><o:p></o:p></span></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-50060537898140714522014-03-11T09:37:00.002-07:002015-04-06T09:32:32.826-07:00Database migration using SAN migration<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">Couple weeks back I posted about <a href="http://oracle12c.blogspot.com/2014/02/database-migration-using-san-migration.html" target="_blank">Database migration using SAN migration</a> POC. Yesterday, we successfully migrated 13 databases from one RAC to another RAC. </span></div>
<div style="text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">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.</span></div>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<br />
<div style="background-color: white; color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;">
<h4 style="margin: 0px; position: relative;">
<span style="font-family: Verdana, sans-serif;">Migration preparation:</span></h4>
</div>
<div style="background-color: white;">
<ol style="text-align: left;">
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Identify databases are being migrated.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Collect the database and related database service configuration.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Prepare commands to recreate the services as well adding databases to the OCR on the target cluster.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Copy password files from existing cluster nodes to target cluster nodes.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Created text initialization files and copied them to the target server.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;"><span style="font-family: Verdana, sans-serif;">On the target server we modified the init files:</span></li>
<ol>
<li><span style="color: #222222; font-family: Verdana, sans-serif; font-size: x-small;"><span style="line-height: 18.479999542236328px;">Archive log destination</span></span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;"><span style="font-family: Verdana, sans-serif;">Any other directory names (like audit dump destination)</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;"><span style="font-family: Verdana, sans-serif;">remote_listener parameter</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;"><span style="font-family: Verdana, sans-serif;">Changed instance names from uppercase to lowercase</span></li>
</ol>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Copy init files from the existing cluster nodes to the target cluster nodes.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;"><span style="line-height: 18.399999618530273px; text-indent: -24px;">Create entries in /etc/oratab for the databases to be migrated</span></span></li>
</ol>
<span style="color: #222222; font-family: Verdana, sans-serif; font-size: 13px; line-height: 18.479999542236328px;"><b>On the day of migration:</b></span><br />
<ol style="text-align: left;">
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Application team shutdown the respective application services.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Shutdown database(s) having files on the disk group being migrated.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Unmount disk group - all the nodes of existing cluster. (umount <DiskGroupName>)</span></li>
<ol>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Here we shutdown the entire cluster as we are migrating all databases from this cluster</span></li>
</ol>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">SysAdmin detached SAN LUN from the existing cluster.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">SysAdmin presented same LUNs to the new target cluster nodes.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">ASM admin/DBA mounted the diskgroup by using asmcmd utility (mount <DiskGroupName>)</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Repeat following steps for each database</span></li>
<ol style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px;">
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Start databases using the modified pfile through sqlplus</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Create spfile based on the pfile used to startup the database.</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Shutdown database</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Startup using spfile through sqlplus</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Apply catbundle for the PSU version, we installed on target cluster which was not on the source cluster</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Validate modified parameters</span></li>
</ol>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Add database to the OCR using commands identified in Step#3 [Changed database instance names to lower case].</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Start database using srvctl.</span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">Add database service(s), if any identified in Step#2 and Step#3</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="color: #222222; font-family: Verdana, sans-serif; font-size: x-small;"><span style="line-height: 18.479999542236328px;">As a part of system build, we did perform RAC System test. Still here, we performed service fail-over validation. </span></span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;"><span style="font-family: Verdana, sans-serif;">We performed control reboot of both the nodes at the same time.</span></li>
<li style="margin: 0px 0px 0.25em; padding: 0px;"><span style="color: #222222; font-family: Verdana, sans-serif; font-size: x-small;"><span style="line-height: 18.479999542236328px;">After everything came online we executed script [validate_svc_restart.sh] to ensure that service are running on the preferred nodes.</span></span></li>
<li style="color: #222222; font-family: Verdana, Geneva, sans-serif; font-size: 13px; line-height: 18.479999542236328px; margin: 0px 0px 0.25em; padding: 0px;">We also, updated the RMAN configuration to accommodate the change in directory names.</li>
</ol>
</div>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Script <b>validate_svc_restart.sh</b>:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">#!/bin/ksh</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-size: x-small;"><span style="font-family: Courier New, Courier, monospace;">ASM_SID="`cat $ORATAB |awk -F: '/^\+ASM/{print $1}'`"; export ASM_SID</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. /oracle/orabase/admin/common/oraprofile.sh ${ASM_SID} 1>/dev/null 2>/dev/null</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">for svc in `crsctl stat res -t | grep svc | grep -v preconnect `</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">do</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> echo " * * * * Processing ${svc}"</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> export DBN=`echo ${svc}|cut -d. -f2`</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> export SRVC=`echo ${svc}|cut -d. -f3,4,5 | sed s/\.svc\$//`</span><br />
<span style="font-size: x-small;"><span style="font-family: Courier New, Courier, monospace;"> for PRFRD_SVR in `srvctl config service -d ${DBN} -s ${SRVC} | grep Preferred | cut -d: -f2|tr , " "`</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> do</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> echo " * * * * Validating preferred instance: ${PRFRD_SVR}"</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if [ `srvctl status service -d ${DBN} -s ${SRVC} | grep ${PRFRD_SVR} | wc -l` -lt 1 ]</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> then</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> echo " Service[${SRVC}] is NOT running on Preferred server: ${PRFRD_SVR}"</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> srvctl stop service -d ${DBN} -s ${SRVC}</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> srvctl start service -d ${DBN} -s ${SRVC}</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> srvctl status service -d ${DBN} -s ${SRVC}</span><br />
<span style="font-size: x-small;"><span style="font-family: Courier New, Courier, monospace;"> fi</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> done</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">done</span><br />
<div>
<span style="font-size: x-small;"><br /></span></div>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-90417705483565552712014-02-26T19:37:00.000-08:002014-03-11T09:08:05.758-07:00Database migration using SAN migration technique<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">We are planning migrating databases from one RAC to another RAC. For this purpose we are planning to use SAN migration technique.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">This is a proof of concept (POC), I will update this BLOG as we make progress.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">1. For POC, we will create a new diskgroup [san_poc] on existing RAC.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Courier New, Courier, monospace;">old01 $ kfod op=disks disks=raw</span><br />
<span style="font-family: Courier New, Courier, monospace;">--------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"> Disk Size Path User Group</span><br />
<span style="font-family: Courier New, Courier, monospace;">================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 1: 20473 Mb /dev/oracleasm/disks/ASM_0001 oracle oinstall</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2: 20473 Mb /dev/oracleasm/disks/ASM_0002 oracle oinstall</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3: 20473 Mb ORCL:ASM_0001 <unknown> <unknown></span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4: 20473 Mb ORCL:ASM_0002 <unknown> <unknown></span><br />
<span style="font-family: Courier New, Courier, monospace;">old02 $ asmcmd lsdg</span><br />
<span style="font-family: Courier New, Courier, monospace;">State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 61440 18734 0 18734 0 Y DATA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40960 40455 0 40455 0 N FRA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">old01 $ asmcmd -p</span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > <b>mkdg '<dg name="san_poc" redundancy="external"><dsk string="/dev/oracleasm/disks/ASM_0001"/><dsk string="/dev/oracleasm/disks/ASM_0002"/></dg>'</b></span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > lsdg</span><br />
<span style="font-family: Courier New, Courier, monospace;">State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 61440 18734 0 18734 0 Y DATA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40960 40455 0 40455 0 N FRA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40946 40894 0 40894 0 N SAN_POC/</span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] ></span><br />
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Before we create a new database on this new disk group, we need to mount this new diskgroup on another node:</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">old02 $ asmcmd -p</span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > lsdg</span><br />
<span style="font-family: Courier New, Courier, monospace;">State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 61440 18734 0 18734 0 Y DATA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40960 40455 0 40455 0 N FRA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > <b>mount san_poc</b></span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > lsdg</span><br />
<span style="font-family: Courier New, Courier, monospace;">State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 61440 18734 0 18734 0 Y DATA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40960 40455 0 40455 0 N FRA1/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 40946 40851 0 40851 0 N SAN_POC/</span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] ></span></div>
<div>
<br /></div>
<div>
<span style="font-family: Verdana, sans-serif;">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.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">For the new database, created a new service using following command:</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">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</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">old01 $</span><span style="font-family: Courier New, Courier, monospace;"> srvctl status service -d poc</span><br />
<span style="font-family: Courier New, Courier, monospace;">Service poc_svc is not running.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">old01 $</span><span style="font-family: Courier New, Courier, monospace;"> srvctl start service -d poc -s poc_svc</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">old01 $</span><span style="font-family: Courier New, Courier, monospace;"> srvctl status service -d poc</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Service poc_svc is running on instance(s) poc1</span><br />
<div>
<br /></div>
<span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Verdana, sans-serif;">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.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Verdana, sans-serif;">4. Next step is to shutdown database and un-mount disk group from all the nodes.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Courier New, Courier, monospace;">srvctl stop database -d poc</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > umount san_poc</span></div>
</div>
<div>
<br /></div>
<div>
<span style="font-family: Verdana, sans-serif;">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.</span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, sans-serif;">LUNs released from the existing cluster were presented to the new cluster. On both the new cluster nodes execute "oracleasm scandisks".</span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<h4 style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">Now, this was the new excitement phase...</span></h4>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, sans-serif;">6. I logged in to new cluster node. To validate disk is intact, I ran kfed command:</span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$ kfed dev=/dev/oracleasm/disks/ASM_0005 op=read cnt=1 blknum=0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.endian: 1 ; 0x000: 0x01</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.hard: 130 ; 0x001: 0x82</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.datfmt: 1 ; 0x003: 0x01</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.block.blk: 0 ; 0x004: blk=0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.block.obj: 2147483648 ; 0x008: disk=0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.check: 2930213453 ; 0x00c: 0xaea7824d</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.fcn.base: 0 ; 0x010: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.fcn.wrap: 0 ; 0x014: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.spare1: 0 ; 0x018: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfbh.spare2: 0 ; 0x01c: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.provstr: ORCLDISKASM_0005 ; 0x000: length=16</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[0]: 1598903105 ; 0x008: 0x5f4d5341</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[1]: 892350512 ; 0x00c: 0x35303030</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.compat: 168820736 ; 0x020: 0x0a100000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.dsknum: 0 ; 0x024: 0x0000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"><b>kfdhdb.dskname: SAN_POC_0000 ; 0x028: length=12</b></span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"><b>kfdhdb.grpname: SAN_POC ; 0x048: length=7</b></span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"><b>kfdhdb.fgname: SAN_POC_0000 ; 0x068: length=12</b></span></div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
</div>
<div>
<span style="font-family: Verdana, sans-serif;">Hold my breath.... attempt to mount diskgroup.</span></div>
<div>
<br /></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">mount san_poc</span></div>
<div>
<br /></div>
<div>
<span style="font-family: Verdana, sans-serif;">And I was successful. Repeated same step on another node. </span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, sans-serif;">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:</span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">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"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">new01 $ srvctl add instance -d poc -i poc1 -n new01</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">new01 $ srvctl add instance -d poc -i poc2 -n new02</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">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</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">new01 $ srvctl start database -d poc</span></div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
I overjoyed this as database started without any issues!!! [Though, I had to create audit dump directory...]. </div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
8. "LISTENER" parameter needs to be modified in the database server parameter file.</div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">SQL> alter system set remote_listener='new-scan1:1521' sid='*' scope=spfile;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">System altered.</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=new02-vip)(PORT=1521))))' sid='poc2' scope=spfile;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">System altered.</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=new01-vip)(PORT=1521))))' sid='poc1' scope=spfile;</span></div>
</div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
Restarted database and was able to connect to this POC database from the remote client.</div>
</div>
<h3 style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">Summary:</span></h3>
<h4 style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">Assumptions:</span></h4>
<ul style="text-align: left;">
<li><span style="font-family: Verdana, sans-serif;">OS and Oracle software binaries are identical on existing as well as target cluster nodes.</span></li>
<li><span style="font-family: Verdana, sans-serif;">ASM disk groups doesn't contain voting/OCR files</span></li>
<li><span style="font-family: Verdana, sans-serif;">ASM disk groups - planned to migrate contains only database files.</span></li>
<li><span style="font-family: Verdana, sans-serif;">ASM disk groups planned to migrate are self-contained within the disk groups being migrated.</span></li>
</ul>
<div>
<h4 style="text-align: left;">
<span style="font-family: Verdana, sans-serif;">Migration Steps:</span></h4>
</div>
<div>
<ol style="text-align: left;">
<li><span style="font-family: Verdana, sans-serif;">Identify databases are being migrated.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Collect the database and related database service configuration.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Prepare commands to recreate the services as well adding databases to the OCR on the target cluster.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Copy password files from existing cluster nodes to target cluster nodes.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Copy init files from the existing cluster nodes to the target cluster nodes.</span></li>
<li><span style="font-family: Verdana, sans-serif;"><span style="line-height: 18.399999618530273px; text-indent: -24px;">Create entries in /etc/oratab for the databases to be migrated</span></span></li>
<li><span style="font-family: Verdana, sans-serif;">Shutdown database(s) having files on the disk group being migrated.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Unmount disk group - all the nodes of existing cluster. (umount <DiskGroupName>)</span></li>
<li><span style="font-family: Verdana, sans-serif;">SysAdmin detach SAN LUN from the existing cluster.</span></li>
<li><span style="font-family: Verdana, sans-serif;">SysAdmin presents same LUNs to the new target cluster nodes.</span></li>
<li><span style="font-family: Verdana, sans-serif;">ASM admin/DBA will mount the diskgroup by using asmcmd utility (mount <DiskGroupName>)</span></li>
<li><span style="font-family: Verdana, sans-serif;">Add database to the OCR using commands identified in Step#3.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Start database using srvctl.</span></li>
<li><span style="font-family: Verdana, sans-serif;">Modify local_listener and remote_listener parameter [Node vip and scan name needs to be updated]</span></li>
<li><span style="font-family: Verdana, sans-serif;">Add database service(s), if any identified in Step#2 and Step#3</span></li>
<li><span style="font-family: Verdana, sans-serif;"><span style="line-height: 18.399999618530273px; text-indent: -24px;">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.</span></span></li>
</ol>
</div>
</div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-57579487655582015532014-02-20T15:10:00.002-08:002014-02-21T10:03:10.679-08:00Creating ASM diskgroup using "asmcmd"<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
<span style="font-family: Verdana, sans-serif;">Created a "TEMP" diskgroup consisting single LUN with external redundancy.</span></div>
<div>
<br /></div>
<div style="text-align: left;">
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > <b><span style="color: red;">mkdg '<dg name="temp" redundancy="external"><dsk string="ORCL:ASM_0004"/></dg>'</span></b></span></div>
<div>
<br />
<span style="font-family: Verdana, sans-serif;">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:</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > <b><span style="color: red;">mount TEMP</span></b></span><br />
<span style="font-family: Courier New, Courier, monospace;">ASMCMD [+] > lsdg</span><br />
<span style="font-family: Courier New, Courier, monospace;">State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED EXTERN N 512 4096 1048576 20473 20380 0 20380 0 N TEMP/</span><br />
<span style="font-family: Courier New, Courier, monospace;">MOUNTED NORMAL N 512 4096 1048576 61419 60493 20473 20010 0 Y VOTE_DG/</span></div>
<div>
<br /></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-74862951426106797512013-08-12T09:04:00.000-07:002013-08-12T09:05:26.777-07:00Oracle Database 12c Download<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">Now <a href="http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html?ssSourceSiteId=ocomen" rel="nofollow" target="_blank">Oracle Database 12c download</a> is available for Linux, Solaris and also for Windows X64</span><br />
<br />
<br /></div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-40040060611116923892013-08-01T06:43:00.000-07:002013-08-01T09:01:39.165-07:00Oracle Database 12c Architecture Diagrams<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin-bottom: 24px; padding: 0px; vertical-align: baseline;">
<span style="color: #333333;">Oracle Database 12c Architecture</span><br />
<br /><a href="http://www.oracle.com/technetwork/tutorials/posterfiles-1974103.pdf" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; color: #743399; margin: 0px; padding: 0px; vertical-align: baseline;"><img alt="" border="0" src="http://dharmesh96.files.wordpress.com/2013/07/072613_0400_oracledatab1.png?w=640" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: none; margin: 0px; padding: 0px; vertical-align: baseline;" /></a></div>
<div style="background-color: white; border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin-bottom: 24px; padding: 0px; vertical-align: baseline;">
<a href="http://apex.oracle.com/pls/apex/f?p=44785:24:0::::P24_CONTENT_ID,P24_PREV_PAGE:7345,1" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; color: #743399; margin: 0px; padding: 0px; vertical-align: baseline;">Watch overview of Oracle Database 12c Architecture Diagram</a></div>
<div style="background-color: white; border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin-bottom: 24px; padding: 0px; vertical-align: baseline;">
Oracle Database 12c Multitenant Architecture<br />
<br />
<img alt="" src="http://dharmesh96.files.wordpress.com/2013/07/072613_0400_oracledatab2.jpg?w=640" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;" /></div>
<div style="background-color: white; border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin-bottom: 24px; padding: 0px; vertical-align: baseline;">
<a href="http://apex.oracle.com/pls/apex/f?p=44785:24:0::::P24_CONTENT_ID,P24_PREV_PAGE:7352,1" style="background-color: transparent; background-position: initial initial; background-repeat: initial initial; border: 0px; color: #ff4b33; margin: 0px; padding: 0px; vertical-align: baseline;">Watch overview of Oracle Database 12c Multitenant diagram</a></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-86252548983202341752013-06-27T13:22:00.001-07:002013-06-27T13:22:43.506-07:00ORA-04030 PGA Memory Diagnostic Tool<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">There is a new diagnostic tool for 4030 on Oracle Support
site. Please remember this is for 4030 – related to PGA and is different from
4031 – related to SGA.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a href="https://support.oracle.com/epmos/faces/ui/gr/GrHome.jspx?_afrLoop=100935099170745&domainId=ORA4030&_afrWindowMode=0&_adf.ctrl-state=10dmqc2q2_132"><span style="font-family: Courier New, Courier, monospace;">https://support.oracle.com/epmos/faces/ui/gr/GrHome.jspx?_afrLoop=100935099170745&domainId=ORA4030&_afrWindowMode=0&_adf.ctrl-state=10dmqc2q2_132</span></a><span style="font-family: Verdana, sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">There are other diagnostic tools [including 4030, 4031, 600]
as well and they are catalog under Note ID: 559339.1: </span><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=93100892040338&id=559339.1&_adf.ctrl-state=12td3xcye_113"><span style="font-family: Courier New, Courier, monospace;">https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=93100892040338&id=559339.1&_adf.ctrl-state=12td3xcye_113</span></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSlzM8cFXFcMm9HKlNKTCp45Na80c46hlduYoBrSjuv1h7_IpFAqyx8OPgrHYdXXkgKrDmQvxgBPsy_QHMsnl2Miy52dTE50pSaMLLJV5iTxrNbN8r3diIVCPgt8QBv0j1oQCxLyofVIM/s1567/Ora_4030_Diagnostic_Tool.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSlzM8cFXFcMm9HKlNKTCp45Na80c46hlduYoBrSjuv1h7_IpFAqyx8OPgrHYdXXkgKrDmQvxgBPsy_QHMsnl2Miy52dTE50pSaMLLJV5iTxrNbN8r3diIVCPgt8QBv0j1oQCxLyofVIM/s320/Ora_4030_Diagnostic_Tool.png" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-39549789031420785342013-06-26T14:53:00.002-07:002013-06-26T14:53:35.941-07:00Oracle Database 12c<div dir="ltr" style="text-align: left;" trbidi="on">
Oracle Database 12c is released on June 25, 2013<br />
<br />
<div class="MsoNormal">
<a href="http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html">http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html</a><o:p></o:p></div>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0tag:blogger.com,1999:blog-404595177076088925.post-75481661792619616632013-02-12T12:59:00.000-08:002013-02-14T07:08:25.184-08:00Important Links<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">Important links that I came across this week</span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: Verdana, sans-serif;"><a href="http://martincarstenbach.wordpress.com/category/oracle/performance/" target="_blank">Selectivel purging shared pool</a></span></li>
<li><span style="font-family: Verdana, sans-serif;"><a href="http://serdarturgut.blogspot.com/2011/11/how-to-flush-single-sql-from-shared.html" target="_blank">How to flush a single SQL from shared pool</a></span></li>
<li><span class="xq" id="pt1:sd_r1:0:dv_rDoc:0:ol22"><span style="font-family: Verdana, sans-serif;"><a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=810394.1" rel="nofollow" target="_blank">RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) [ID 810394.1]</a></span></span></li>
</ul>
</div>
Dharmeshhttp://www.blogger.com/profile/14228893065028306002noreply@blogger.com0