tag:blogger.com,1999:blog-72030799706090157192024-02-19T15:04:10.384+11:00Oracle DBA blogAlexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.comBlogger71125tag:blogger.com,1999:blog-7203079970609015719.post-56865687878131158802020-07-30T12:03:00.000+10:002020-07-30T12:03:58.439+10:00Find out Oracle home directory of running Oracle instance on Linux<div dir="ltr" style="text-align: left;" trbidi="on">
If /etc/oratab is deleted or corrupted, then<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">ps aux | grep ora_pmon</span><br />
<span style="font-family: Courier New, Courier, monospace;">oracle <b>10937</b> 0.0 0.0 2445960 6732 ? Ss May25 11:01 ora_pmon_CDRUAT</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">strings /proc/<b>10937</b>/environ | grep ORACLE_HOME</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORACLE_HOME=/opt/oracle/product/12.1.0.2/ee_3</span><br />
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-72322869870718513322019-05-28T12:56:00.001+10:002019-05-28T12:56:58.500+10:00Recovering a Database to a Previous Incarnation<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Environment</h4>
Oracle 12.1, RMAN<br />
<h4 style="text-align: left;">
Problem</h4>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time</span></div>
</div>
<h4 style="text-align: left;">
Solution</h4>
Find a previous incarnation that you need.<br />
Option 1: connect to a target database and catalog, run the following command and choose <b>"Inc Key"</b><br />
<span style="font-family: Courier New, Courier, monospace;">RMAN> <span style="color: blue;">LIST INCARNATION OF DATABASE DBPRD1;</span></span><br />
<br />
Option 2: Connect to the recovery catalog, run the following command and choose <b>DBINC_KEYB.</b> Option 2 is more convenient as it provides more information, e.g. PARENT_DBINC_KEY.<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">select * from RC_DATABASE_INCARNATION where name='DBPRD1' order by DBINC_KEY;</span><br />
<br />
Then use the incarnation key in RESET DATABASE, exempli gratia:<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">reset database to incarnation 3587196;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">run {</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">set until time = "to_date('2019-05-27 10:00:00','YYYY-MM-DD HH24:MI:SS')";</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">allocate channel ch01 type 'SBT_TAPE' PARMS '<i>some parameters</i>';</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">restore database;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">recover database;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">alter database open resetlogs;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">}</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
<br />
<div>
<br /></div>
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-66575605520367330802019-02-14T13:46:00.000+11:002019-02-14T13:47:07.483+11:00ORACLE data block corrupted after restoring database (2)<div dir="ltr" style="text-align: left;" trbidi="on">
A test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.<br />
When such object is used, the following errors happen<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'</span><br />
<div>
<br /></div>
<div>
Find the affected segment:<br />
<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">select segment_type, owner, segment_name, </span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">case segment_type WHEN 'INDEX' THEN 'ALTER INDEX '||owner||'.'||segment_name||' UNUSABLE; ALTER INDEX '||owner||'.'||segment_name||' REBUILD;' </span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;"> WHEN 'TABLE' THEN 'TRUNCATE TABLE '||owner||'.'||segment_name||';'</span><br />
<span style="color: blue; font-family: "courier new", courier, monospace;">end as SQLstatement</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;"></span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">from dba_extents</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">where file_id = </span><b><span style="color: blue;">42 </span><span style="background-color: white;"><span style="color: lime;">-- change</span></span></b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">and </span><b><span style="color: blue;">1029253 </span><span style="color: lime;">-- change</span></b></span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">between block_id and (block_id + blocks -1);</span><br />
<br />
If it is an index, we can (make unusable and rebuild) or (drop and recreate).<br />
<br />
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-18609643202887950732018-09-04T16:31:00.000+10:002018-09-04T16:31:29.978+10:00Moving Oracle EM 12c OMR to another host<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=windows-1252"/>
<title></title>
<meta name="generator" content="LibreOffice 6.0.5.2 (Windows)"/>
<meta name="changed" content="2018-09-04T16:29:28.717000000"/>
<style type="text/css">
@page { margin: 1.27cm }
p { margin-bottom: 0.25cm; line-height: 115% }
h2 { margin-top: 0.07cm; margin-bottom: 0cm; color: #2f5496; page-break-inside: avoid }
h2.western { font-family: "Calibri Light", sans-serif; font-size: 13pt }
h2.cjk { font-family: "Times New Roman", serif; font-size: 13pt }
h2.ctl { font-family: "Times New Roman", serif; font-size: 13pt; font-weight: normal }
a:link { so-language: zxx }
</style>
</head>
<body lang="en-AU" dir="ltr">
<h2 class="western" style="margin-bottom: 0.5cm; page-break-before: always">
Environment</h2>
<p style="margin-bottom: 0.28cm; line-height: 107%">Oracle Enterprise
Manager 12c (12.1.0.5, OEM12c), Oracle database 11.2.0.4, Oracle
Linux 5.7</p>
<h2 class="western" style="margin-bottom: 0.5cm">Solution</h2>
<p style="margin-bottom: 0.28cm; line-height: 107%">Moving Oracle
Management Repository to another host.</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">On OMS host, Set
ORACLE_HOME to OMS oracle home, test:</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">which
emctl</font></font></font></p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Check the OMS
repository configuration on the old host</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">emctl
config oms -list_repos_details</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Repository
Connect Descriptor :
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dtooem2)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Repository
User : SYSMAN</font></font></font></p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><br/>
<br/>
</p>
<p style="margin-bottom: 0cm; line-height: 100%">Make sure that no EM
jobs are running.</p>
<p style="margin-bottom: 0cm; line-height: 100%">Stop the OMS
instances using the command <font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">emctl
stop oms</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">emctl
stop oms</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Stopping
WebTier...</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">WebTier
Successfully Stopped</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Stopping
Oracle Management Server...</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server Successfully Stopped</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server is Down</font></font></font></p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><br/>
<br/>
</p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><b>Duplicate
database with RMAN</b></p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Prepare INIT
file. Check parameters with any directories.</p>
<p style="margin-bottom: 0cm; line-height: 100%">Create SPFILE! Or
DUPLICATE will fail:</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">RMAN-11001:
Oracle Error:</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">ORA-32001:
write to SPFILE requested but no SPFILE is in use</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0cm; line-height: 100%">Create password file
for the auxiliary instance as you must connect with a net service
name:</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">RMAN-06217:
not connected to auxiliary database with a net service name</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0cm; line-height: 100%">Check and update
TNSNAMES for BOTH target and auxiliary instances or you get:</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">ORA-17629:
Cannot connect to the remote database server</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">ORA-17627:
ORA-12154: TNS:could not resolve the connect identifier specified</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">RMAN script</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">rman
log=$LOG append << EOF</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">connect
auxiliary SYS/xxx@OEMDB_new</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">connect
target SYS/xxx@OEMDB_old</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">run
{</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">ALLOCATE
CHANNEL tgt10 TYPE DISK;</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">ALLOCATE
AUXILIARY CHANNEL dup1 TYPE DISK;</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">duplicate
target database to '${ORACLE_SID}' from active database
nofilenamecheck</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">logfile</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">group
1 ('/data001/oradata/${ORACLE_SID}/redo01.log') size 256M reuse,</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">group
2 ('/data001/oradata/${ORACLE_SID}/redo02.log') size 256M reuse,</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">group
3 ('/data001/oradata/${ORACLE_SID}/redo03.log') size 256M reuse,</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">group
4 ('/data001/oradata/${ORACLE_SID}/redo04.log') size 256M reuse;</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">}</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">exit;</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">EOF</font></font></font></p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><br/>
<br/>
</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Shutdown the old
OMR database.</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Correct the
connect descriptor for the repository by running the following
command on each OMS.</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">emctl
config oms -store_repos_details -repos_conndesc
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))"
-repos_user SYSMAN</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Enter
Repository User's Password :</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Successfully
updated datasources and stored repository details in Credential
Store.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">If
there are multiple OMSs in this environment, run this
store_repos_details command on all of them.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">And
finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl
start oms'.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">[oracle@dtooem2
~]$ emctl stop oms -all</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Stopping
WebTier...</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">WebTier
Successfully Stopped</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Stopping
Oracle Management Server...</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server Already Stopped</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">AdminServer
Successfully Stopped</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server is Down</font></font></font></p>
<p lang="en-US" style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">[oracle@dtooem2
~]$ emctl start oms</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Starting
Oracle Management Server...</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Starting
WebTier...</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">WebTier
Successfully Started</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server Successfully Started</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Management Server is Up</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#ff0000">Do
not do the next step</font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">[dtooem2:ora]
/home/oracle # emctl config repos -host upooem1 -oh
/opt/oracle/product/11.2.0.4/ee_2 -conn_desc
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))"</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Please
enter repository password:</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">No
valid repository target found! Please enter the repository name:</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">OEMDB
</font></font></font>
</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Enter
password : </font></font></font>
</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Login
successful</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Target
"OEMDB:oracle_database" modified successfully</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Command
completed successfully!</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">And it updated
“Monitoring Configuration” of <font color="#ff0000">the
old database target</font>.</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">The Oracle
Documentation says: “No database on host "B" has been
discovered.”. The opposite worked well for me.</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Discover the new
database target first. Then</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#0070c0"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">emctl
config repos -host upooem1 -oh /opt/oracle/product/11.2.0.4/ee_2
-conn_desc
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))"</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Oracle
Enterprise Manager Cloud Control 12c Release 5</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Copyright
(c) 1996, 2015 Oracle Corporation. All rights reserved.</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Please
enter repository password:</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Enter
password : </font></font></font>
</p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Login
successful</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Target
"OEMDB1:oracle_database" modified successfully</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><font color="#000000"><font face="Courier New, monospace"><font size="2" style="font-size: 10pt">Command
completed successfully!</font></font></font></p>
<p style="margin-bottom: 0cm; line-height: 100%"><br/>
</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Go to “Management
Services and Repository” and update “Target Setup”
> “Monitoring Configuration”</p>
<p style="margin-bottom: 0.28cm; line-height: 107%">Configure backups
for the new OMR database.</p>
<h2 lang="en-US" class="western" style="margin-bottom: 0.5cm">Oracle
Documentation</h2>
<p style="margin-bottom: 0.28cm; line-height: 107%">Enterprise
Manager Cloud Control Advanced Installation and Configuration Guide
-> 19 Backing Up and Recovering Enterprise Manager</p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><a href="https://docs.oracle.com/cd/E24628_01/install.121/e24089/ha_backup_recover.htm#EMADV10745" target="_top"><font color="#0563c1"><u>https://docs.oracle.com/cd/E24628_01/install.121/e24089/ha_backup_recover.htm#EMADV10745</u></font></a></p>
<p style="margin-bottom: 0.28cm; line-height: 107%"><br/>
<br/>
</p>
</body>
</html>Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-15372677930633445982018-09-04T15:43:00.002+10:002020-06-23T10:54:18.241+10:00Creating a file system for Oracle database<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=windows-1252"/>
<title></title>
<meta name="generator" content="LibreOffice 6.0.5.2 (Windows)"/>
<meta name="created" content="2018-08-31T16:51:38.067000000"/>
<meta name="changed" content="2018-09-04T15:40:22.476000000"/>
<style type="text/css">
@page { margin: 2cm }
p { margin-bottom: 0.25cm; line-height: 115% }
p.cmd-blua-10-western { margin-bottom: 0cm; color: #0066b3; font-family: "Courier New", monospace; font-size: 11pt; line-height: 100%; page-break-before: auto; page-break-after: auto }
p.cmd-blua-10-cjk { margin-bottom: 0cm; color: #0066b3; font-size: 10pt; line-height: 100%; page-break-before: auto; page-break-after: auto }
p.cmd-blua-10-ctl { margin-bottom: 0cm; color: #0066b3; line-height: 100%; page-break-before: auto; page-break-after: auto }
a:link { so-language: zxx }
</style>
</head>
<body lang="en-AU" dir="ltr">
<p style="margin-bottom: 0cm"><b>Environment</b>: Oracle Linux 7.5</p>
<p style="margin-bottom: 0cm"><b>Steps:</b></p>
<p style="margin-bottom: 0cm">1. Create Virtual Disk on VM.</p>
<p style="margin-bottom: 0cm">List all disks.</p>
<p class="cmd-blua-10-western">lsblk</p>
<p class="cmd-blua-10-western">lsblk -o
name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype</p>
<p class="cmd-blua-10-western"><br/>
</p>
<p style="margin-bottom: 0cm">2. Create a partition table and a GPT
partition: mkpart <PART-LABEL> <START> <END></p>
<p class="cmd-blua-10-western">parted /dev/xvdc</p>
<p class="cmd-blua-10-western">(parted) mktable gpt</p>
<p class="cmd-blua-10-western">(parted) mkpart OMR 0% 100%</p>
<p class="cmd-blua-10-western">(parted) quit</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><span style="font-weight: normal">3.
Create </span><b>EXT4 </b>file system</p>
<p class="cmd-blua-10-western">mkfs.ext4 -T largefile4 /dev/xvdc1</p>
<p class="cmd-blua-10-western">lsblk -o
name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype</p>
<p class="cmd-blua-10-western"><br/>
</p>
<p style="margin-bottom: 0cm">4. Mount and set permissions</p>
<p class="cmd-blua-10-western">mkdir -p /data001/oradata/OEMDB</p>
<p class="cmd-blua-10-western">vi /etc/fstab</p>
<p style="margin-bottom: 0cm">add the following:</p>
<p class="cmd-blua-10-western">UUID=xxxx /data001/oradata/OEMDB ext4
noatime,nodiratime,nobarrier 1 2</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p class="cmd-blua-10-western">mount /data001/oradata/OEMDB</p>
<p class="cmd-blua-10-western">df -h</p>
<p class="cmd-blua-10-western">chown oracle:oinstall
/data001/oradata/OEMDB</p>
<p class="cmd-blua-10-western">ls -ld /data001/oradata/OEMDB</p>
<p style="margin-bottom: 0cm"><br/>
</p>
</p>
<p style="margin-bottom: 0cm">5. Reducing reserved blocks</p>
<p style="margin-bottom: 0cm">By default 5% of the space to be usable only by root. E.g. You can reduce it to 0.5% by doing:</p>
<p class="cmd-blua-10-western">tune2fs -m 0.5 /dev/xvdc1</p>
<p class="cmd-blua-10-western">tune2fs -l /dev/xvdc1</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><b>Documentation</b></p>
<p lang="en-US" style="margin-bottom: 0cm">Improve I/O Performance On
ext3/ext4 File Systems With The "noatime" Mount Option (Doc
ID 1561740.1)</p>
<p lang="en-US" style="margin-bottom: 0cm">Available Mount Options to
Improve ext4 Filesystem Performance (Doc ID 1476869.1)</p>
<p lang="en-US" style="margin-bottom: 0cm">Supported and Recommended
File Systems on Linux (Doc ID 236826.1)</p>
<p lang="en-US" style="margin-bottom: 0cm">Oracle Database -
Filesystem & I/O Type Supportability on Oracle Linux 6 (Doc ID
1601759.1)</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><b>Create XFS file system</b></p>
<p style="margin-bottom: 0cm">In similar way, create FRA 30GiB, <b>XFS</b>
file system, /data001/oradata/fast_recovery_area</p>
<p class="cmd-blua-10-western">mkfs.xfs /dev/xvde1</p>
<p class="cmd-blua-10-western">mkdir -p
/data001/oradata/fast_recovery_area</p>
<p class="cmd-blua-10-western">lsblk -o
name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype</p>
<p class="cmd-blua-10-western">vi /etc/fstab</p>
<p class="cmd-blua-10-western">UUID=xxxx
/data001/oradata/fast_recovery_area xfs defaults 0 0</p>
<p class="cmd-blua-10-western">mount
/data001/oradata/fast_recovery_area</p>
<p class="cmd-blua-10-western">chown oracle:oinstall
/data001/oradata/fast_recovery_area</p>
<p class="cmd-blua-10-western">ls -ld
/data001/oradata/fast_recovery_area</p>
<p class="cmd-blua-10-western"><br/>
</p>
</body>
</html>Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-42531780969630616872018-08-07T13:15:00.001+10:002018-08-07T13:15:39.801+10:00Memory swapping on Linux<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-size: large;">Environment</span><br />
Oracle Linux 7 with Oracle databases 12.1<br />
<br />
<span style="font-size: large;">Monitoring</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">free -h</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">top</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">vmstat [options] [delay [count]]</span><br />
<br />
monitoring a particular process<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">ps -o "ppid pid vsz rss pmem comm args" --pid <pid></pid></span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">top -p <pid> </pid></span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">pmap -x <pid></pid></span><br />
<div>
<br /></div>
<div>
adding SWAP to <b><i>top </i></b>and changing SORT column</div>
<div>
<div>
a. Run the TOP command:</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># top</span></div>
<div>
b. On your keyboard press the " f " key and scroll down using the <down> arrow key until you have selected "SWAP" then press <space> to select it. This should add a " * " symbol in front of it.</space></down></div>
<div>
c. While still selecting "SWAP" press the <right> arrow key, which highlights the entire SWAP line, and using the <top> arrow key move it up to one of the first options (anywhere above "COMMAND").</top></right></div>
<div>
d. While still having "SWAP" selected, type the " s " key which will configure top to SORT by the currently selected option, in this case SWAP.</div>
<div>
e. Finally " q " to save the configuration changes and view the results.</div>
<div>
f. Perform your review as needed and press "q" again to exit top command.</div>
</div>
<div>
g. If you want to save this <b><i>top </i></b>configuration press <b>W</b> (upper case)</div>
<div>
<br /></div>
<div>
<span style="font-size: large;">Kernel parameters and tuned service</span></div>
<div>
check the current parameters</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">sysctl -a|grep 'dirty\|swap'</span></div>
<div>
<br /></div>
<div>
check status of tuned.service, it overwrites parameters in <span style="color: blue; font-family: Courier New, Courier, monospace;">/etc/sysctl.conf </span>!</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">systemctl status tuned.service</span></div>
<div>
<br /></div>
<div>
Check the active tune profile. </div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">tuned-adm active</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Current active profile: virtual-guest</span></div>
</div>
<div>
<div>
see all profiles</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">cd /usr/lib/tuned</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">ls -lh</span></div>
</div>
<div>
For example, modify the current profile</div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">cd /usr/lib/tuned/virtual-guest</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vi tuned.conf</span></div>
</div>
<div>
<div>
comment existing vm.* parameters and add the following</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># recommended by RedHat. Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7.</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vm.swappiness = 1</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vm.dirty_background_ratio = 3</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vm.dirty_ratio = 80</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vm.dirty_expire_centisecs = 500</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">vm.dirty_writeback_centisecs = 100</span></div>
</div>
<div>
<div>
<br /></div>
<div>
re-activate the changed profile</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">tuned-adm profile virtual-guest</span></div>
</div>
<div>
<div>
show the current kernel parameters</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">sysctl -a|grep 'dirty\|swap'</span></div>
</div>
<div>
<br /></div>
<div>
<span style="font-size: large;">MOSC Documentation</span></div>
<div>
<div>
<div class="MsoNoSpacing">
<span lang="EN-US">Linux
OS, Swapping and Databases (Doc ID 1295478.1)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN-US">How to
Check Swap Usage Live via the 'top' Command on Oracle Linux 5, 6, and 7. (Doc
ID 2422888.1)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
Why is SWAP being used instead of available physical
memory? (Doc ID 2404462.1)<o:p></o:p></div>
<div class="MsoNoSpacing">
<span lang="EN-US">Oracle
Linux 7 - sysctl parameter doesn't take effect after reboot (Doc ID 2195319.1)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span lang="EN-US">Oracle
Linux: Modifying kernel parameters using sysctl (Doc ID 390279.1)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
What are Dirty Pages? (Doc ID 2304722.1)<o:p></o:p></div>
<div class="MsoNoSpacing">
<br /></div>
</div>
</div>
<div>
<span style="font-size: large;">RedHat Documentation</span></div>
<div>
<a href="https://access.redhat.com/documentation/en-us/reference_architectures/2017/html-single/deploying_oracle_database_12c_release_2_on_red_hat_enterprise_linux_7/index" target="_blank">Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7</a></div>
<div>
<a href="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/performance_tuning_guide/chap-red_hat_enterprise_linux-performance_tuning_guide-tuned" target="_blank">Red Hat Enterprise Linux 7 Performance Tuning Guide Chapter 3. Tuned</a></div>
<div>
<a href="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/performance_tuning_guide/sect-red_hat_enterprise_linux-performance_tuning_guide-performance_monitoring_tools-tuned_and_tuned_adm" target="_blank">Red Hat Enterprise Linux 7 Performance Tuning Guide 3.2. Performance Tuning with tuned and tuned-adm</a></div>
<div>
<br /></div>
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-44506254356669683112018-02-20T13:40:00.000+11:002018-02-20T14:24:13.383+11:00Configuring Disk Devices Manually for Oracle ASM (without ASMLib)<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=Generator content="Microsoft Word 15 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Calibri Light";
panose-1:2 15 3 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin-top:0cm;
margin-right:0cm;
margin-bottom:8.0pt;
margin-left:0cm;
line-height:107%;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
h1
{mso-style-link:"Heading 1 Char";
margin-top:12.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:107%;
page-break-after:avoid;
font-size:16.0pt;
font-family:"Calibri Light",sans-serif;
color:#2F5496;
font-weight:normal;}
h2
{mso-style-link:"Heading 2 Char";
margin-top:2.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:107%;
page-break-after:avoid;
font-size:13.0pt;
font-family:"Calibri Light",sans-serif;
color:#2F5496;
font-weight:normal;}
p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing
{mso-style-link:"No Spacing Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.cmdblack, li.cmdblack, div.cmdblack
{mso-style-name:"cmd black";
mso-style-link:"cmd black Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:9.0pt;
font-family:"Courier New";}
span.Heading1Char
{mso-style-name:"Heading 1 Char";
mso-style-link:"Heading 1";
font-family:"Calibri Light",sans-serif;
color:#2F5496;}
span.NoSpacingChar
{mso-style-name:"No Spacing Char";
mso-style-link:"No Spacing";}
span.cmdblackChar
{mso-style-name:"cmd black Char";
mso-style-link:"cmd black";
font-family:"Courier New";}
span.Heading2Char
{mso-style-name:"Heading 2 Char";
mso-style-link:"Heading 2";
font-family:"Calibri Light",sans-serif;
color:#2F5496;}
.MsoChpDefault
{font-family:"Calibri",sans-serif;}
.MsoPapDefault
{margin-bottom:8.0pt;
line-height:107%;}
@page WordSection1
{size:595.3pt 841.9pt;
margin:14.2pt 17.0pt 17.0pt 1.0cm;}
div.WordSection1
{page:WordSection1;}
-->
</style>
</head>
<body lang=EN-AU>
<div class=WordSection1>
<p class=MsoNormal><span class=Heading2Char><span style='font-size:13.0pt;
line-height:107%'>Environment</span></span>: Oracle Linux Server release 7.3 on
Oracle VM 3.4.3</p>
<p class=MsoNormal>In Oracle VM Manager, edit VM and add a new Virtual Disk,
e.g.</p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaj0s1KBOUdpnA9hbTyXemxDGS_8aTZEdGnoUVkGY4GUBLKpivPWXLL1vgPJXzoxNa-5bChmFTNeA-1T4O6kIgUfoiIvrigggV63nGoXlYkqxk9pULs37g6JljgSakjFueIchKXIbV1VM/s1600/blog_ASM1_image1.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaj0s1KBOUdpnA9hbTyXemxDGS_8aTZEdGnoUVkGY4GUBLKpivPWXLL1vgPJXzoxNa-5bChmFTNeA-1T4O6kIgUfoiIvrigggV63nGoXlYkqxk9pULs37g6JljgSakjFueIchKXIbV1VM/s1600/blog_ASM1_image1.png" data-original-width="535" data-original-height="293" /></a>
<p class=MsoNormal> </p>
<p class=MsoNoSpacing>In the VM as root, see the new disk:</p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'> </span></p>
<p class=MsoNoSpacing><b><span lang=EN-US style='font-size:9.0pt;font-family:
"Courier New";color:#4472C4'>lsblk</span></b></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>NAME
MAJ:MIN RM SIZE RO TYPE MOUNTPOINT</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvda
202:0 0 30G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>├─xvda1
202:1 0 512M 0 part /boot</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>├─xvda2
202:2 0 8G 0 part /</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>├─xvda3
202:3 0 4G 0 part /tmp</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>├─xvda4
202:4 0 1K 0 part</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>├─xvda5
202:5 0 1.5G 0 part /home</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvda6
202:6 0 16G 0 part [SWAP]</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdb
202:16 0 50G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdb1
202:17 0 50G 0 part /opt/oracle</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdc
202:32 0 10G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdc1
202:33 0 10G 0 part /opt/netbackup</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdd
202:48 0 800G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdd1
202:49 0 800G 0 part /data001/oradata/CDRDEV</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvde
202:64 0 200G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvde1
202:65 0 200G 0 part /data001/fast_recovery_area</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdf
202:80 0 800G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdf1
202:81 0 800G 0 part /data001/oradata/CDRSIT</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdg
202:96 0 100G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdg1
202:97 0 100G 0 part /data001/archivelogs</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdh
202:112 0 800G 0 disk</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdh1 202:113 0 800G 0 part
/data001/oradata/CDRDEV1</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>xvdi
202:128 0 800G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>└─xvdi1
202:129 0 800G 0 part /data001/oradata/CDRSIT1</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";
background:yellow'>xvdj 202:144 0 800G 0 disk</span></p>
<p class=MsoNoSpacing><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";
background:yellow'>└─xvdj1 202:145 0 800G 0 part</span></p>
<p class=MsoNormal><span lang=EN-US> </span></p>
<p class=MsoNormal><span lang=EN-US>For persistent device naming, we can
configure ASMLIB or set udev rules. We need to use a partition UUID (not a
filesystem UUID) in udev rules. For this, use <b><i>parted</i></b> to create a
GPT partition.</span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>parted /dev/xvdj</span></b></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>mktable gpt</span></b></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=MsoNoSpacing>to create a GPT partition:</p>
<p class=cmdblack><span lang=EN-US>mkpart PART-LABEL START END</span></p>
<p class=MsoNormal><span lang=EN-US>e.g.</span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>mkpart asmpart1 0%
100%</span></b></p>
<p class=cmdblack><span lang=EN-US>(parted) print</span></p>
<p class=cmdblack><span lang=EN-US>Model: Xen Virtual Block Device (xvd)</span></p>
<p class=cmdblack><span lang=EN-US>Disk /dev/xvdj: 859GB</span></p>
<p class=cmdblack><span lang=EN-US>Sector size (logical/physical): 512B/512B</span></p>
<p class=cmdblack><span lang=EN-US>Partition Table: gpt</span></p>
<p class=cmdblack><span lang=EN-US>Disk Flags:</span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><span lang=EN-US>Number Start End Size File system
Name Flags</span></p>
<p class=cmdblack><span lang=EN-US> 1 1049kB 859GB 859GB
asmpart1</span></p>
<p class=MsoNormal><span lang=EN-US> </span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>udevadm info
--query=property /dev/xvdj1</span></b></p>
<p class=cmdblack><span lang=EN-US>DEVLINKS=/dev/disk/by-partlabel/asmpart1
/dev/disk/by-partuuid/48940eb5-f6fa-4558-aeba-b12b81199e27 /dev/oracleasm/disk1</span></p>
<p class=cmdblack><span lang=EN-US>DEVNAME=/dev/xvdj1</span></p>
<p class=cmdblack><span lang=EN-US>DEVPATH=/devices/vbd-51856/block/xvdj/xvdj1</span></p>
<p class=cmdblack><span lang=EN-US>DEVTYPE=partition</span></p>
<p class=cmdblack><span lang=EN-US>ID_FS_TYPE=oracleasm</span></p>
<p class=cmdblack><span lang=EN-US>ID_FS_USAGE=filesystem</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_DISK=202:144</span></p>
<p class=cmdblack><span lang=EN-US style='background:yellow'>ID_PART_ENTRY_NAME=asmpart1</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_NUMBER=1</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_OFFSET=2048</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_SCHEME=gpt</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_SIZE=1677717504</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_ENTRY_TYPE=ebd0a0a2-b9e5-4433-87c0-68b6b72699c7</span></p>
<p class=cmdblack><span lang=EN-US style='background:yellow'>ID_PART_ENTRY_UUID=48940eb5-f6fa-4558-aeba-b12b81199e27</span></p>
<p class=cmdblack><span lang=EN-US>ID_PART_TABLE_TYPE=gpt</span></p>
<p class=cmdblack><span lang=EN-US>MAJOR=202</span></p>
<p class=cmdblack><span lang=EN-US>MINOR=145</span></p>
<p class=cmdblack><span lang=EN-US>SUBSYSTEM=block</span></p>
<p class=cmdblack><span lang=EN-US>TAGS=:systemd:</span></p>
<p class=cmdblack><span lang=EN-US>USEC_INITIALIZED=800372237892</span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>vi /etc/udev/rules.d/99-oracle-asmdevices.rules</span></b></p>
<p class=cmdblack><span lang=EN-US>KERNEL=="xvd??",
ENV{ID_PART_ENTRY_UUID}=="<span style='background:yellow'>48940eb5-f6fa-4558-aeba-b12b81199e27</span>",
SYMLINK+="oracleasm/disk1", OWNER="oracle",
GROUP="dba", MODE="0660"</span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=MsoNoSpacing>To implement the new rules:</p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>udevadm trigger</span></b></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>ls -la /dev/oracleasm/disk1</span></b></p>
<p class=cmdblack><span lang=EN-US>lrwxrwxrwx. 1 root root 8 Feb 15 12:32 <span
style='background:yellow'>/dev/oracleasm/disk1 -> ../xvdj1</span></span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>ls -la /dev/xvd*</span></b></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 0 Feb 15
12:32 /dev/xvda</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 1 Feb 15
12:32 /dev/xvda1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 2 Feb 15
12:32 /dev/xvda2</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 3 Feb 15
12:32 /dev/xvda3</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 4 Feb 15
12:32 /dev/xvda4</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 5 Feb 15
12:32 /dev/xvda5</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 6 Feb 15
12:32 /dev/xvda6</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 16 Feb 15
12:32 /dev/xvdb</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 17 Feb 15
12:32 /dev/xvdb1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 32 Feb 15
12:32 /dev/xvdc</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 33 Feb 15
12:32 /dev/xvdc1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 48 Feb 15
12:32 /dev/xvdd</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 49 Feb 15
12:32 /dev/xvdd1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 64 Feb 15
12:32 /dev/xvde</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 65 Feb 15
12:32 /dev/xvde1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 80 Feb 15
12:32 /dev/xvdf</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 81 Feb 15
12:32 /dev/xvdf1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 96 Feb 15
12:32 /dev/xvdg</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 97 Feb 15
12:32 /dev/xvdg1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 112 Feb 15
12:32 /dev/xvdh</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 113 Feb 15
12:32 /dev/xvdh1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 128 Feb 15
12:32 /dev/xvdi</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 129 Feb 15
12:32 /dev/xvdi1</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 root disk 202, 144 Feb 15
12:32 /dev/xvdj</span></p>
<p class=cmdblack><span lang=EN-US>brw-rw----. 1 <span style='background:yellow'>oracle
dba</span> 202, 145 Feb 15 12:32 <span style='background:yellow'>/dev/xvdj1</span></span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=MsoNoSpacing>to see all disks and partitions:</p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>lsblk -o
name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype</span></b></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'> </span></b></p>
<p class=MsoNoSpacing>Alternatively, you can use a partition label name ID_PART_ENTRY_NAME:</p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>cat /etc/udev/rules.d/99-oracle-asmdevices.rules
</span></b></p>
<p class=cmdblack><span lang=EN-US>KERNEL=="xvd??",
ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27",
SYMLINK+="oracleasm/disk1", OWNER="oracle",
GROUP="dba", MODE="0660"</span></p>
<p class=cmdblack><span lang=EN-US>KERNEL=="xvd??", ENV<span
style='background:yellow'>{ID_PART_ENTRY_NAME}=="asmpart2"</span>,
SYMLINK+="oracleasm/disk2", OWNER="oracle",
GROUP="dba", MODE="0660"</span></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'> </span></b></p>
<p class=cmdblack><b><span lang=EN-US style='color:#4472C4'>lsblk -o
name,partlabel,partuuid</span></b></p>
<p class=cmdblack><span lang=EN-US>NAME PARTLABEL PARTUUID</span></p>
<p class=cmdblack><span lang=EN-US>xvda</span></p>
<p class=cmdblack><span lang=EN-US>├─xvda1</span></p>
<p class=cmdblack><span lang=EN-US>├─xvda2</span></p>
<p class=cmdblack><span lang=EN-US>├─xvda3</span></p>
<p class=cmdblack><span lang=EN-US>├─xvda4</span></p>
<p class=cmdblack><span lang=EN-US>├─xvda5</span></p>
<p class=cmdblack><span lang=EN-US>└─xvda6</span></p>
<p class=cmdblack><span lang=EN-US>xvdb</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdb1</span></p>
<p class=cmdblack><span lang=EN-US>xvdc</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdc1</span></p>
<p class=cmdblack><span lang=EN-US>xvdd</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdd1</span></p>
<p class=cmdblack><span lang=EN-US>xvde</span></p>
<p class=cmdblack><span lang=EN-US>└─xvde1</span></p>
<p class=cmdblack><span lang=EN-US>xvdf</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdf1</span></p>
<p class=cmdblack><span lang=EN-US>xvdg</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdg1</span></p>
<p class=cmdblack><span lang=EN-US>xvdh</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdh1</span></p>
<p class=cmdblack><span lang=EN-US>xvdi</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdi1</span></p>
<p class=cmdblack><span lang=EN-US>xvdj</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdj1 asmpart1
48940eb5-f6fa-4558-aeba-b12b81199e27</span></p>
<p class=cmdblack><span lang=EN-US>xvdk</span></p>
<p class=cmdblack><span lang=EN-US>└─xvdk1 asmpart2
0b8ba20c-a10d-4564-b582-0692fd9657e8</span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<p class=cmdblack><span lang=EN-US> </span></p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgK3-B7Oy6U96qeoCZDCkgOLmbFyzqIlLGX3m_vwbrLULtN7owKhxwHjONcuOOjmrRZCG807FG-_gk_arhAPInb0EwQcyeNXuEB9Ranpt6kT8a84KrsuI0FlEwH1DVpbuHdzb9X_9n-pBg/s1600/blog_ASM1_image2.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgK3-B7Oy6U96qeoCZDCkgOLmbFyzqIlLGX3m_vwbrLULtN7owKhxwHjONcuOOjmrRZCG807FG-_gk_arhAPInb0EwQcyeNXuEB9Ranpt6kT8a84KrsuI0FlEwH1DVpbuHdzb9X_9n-pBg/s1600/blog_ASM1_image2.png" data-original-width="820" data-original-height="407" /></a>
<p class=cmdblack><span lang=EN-US> </span></p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQhlg26f3JZEWFYXzhWebZ3T7ofgS1TSR7-hljh7k-VDIKuzix_UuQDppf6rNa24N_gSvqIKK4J6TcCHWqiCkw4xwkD6dMflPWMwDBaHKpGK45LpQkIB5IeI-nXG2kgt29QYUIg_iAZv4/s1600/blog_ASM1_image3.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQhlg26f3JZEWFYXzhWebZ3T7ofgS1TSR7-hljh7k-VDIKuzix_UuQDppf6rNa24N_gSvqIKK4J6TcCHWqiCkw4xwkD6dMflPWMwDBaHKpGK45LpQkIB5IeI-nXG2kgt29QYUIg_iAZv4/s1600/blog_ASM1_image3.png" data-original-width="375" data-original-height="147" /></a>
<p class=cmdblack><span lang=EN-US> </span></p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDgGJ1RHsepHunW_FtdhSErr21aP3_TZRHQYbXDTQqsZ4bd-uh4QxB_52mxXd8v9_vQ-iO6uJGQgK2uyiPFZswm-c_noQmKEh67psNL3sv-fGfqRnxNHJbMdwzJwGFXKOCS7LQnXElbao/s1600/blog_ASM1_image4.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDgGJ1RHsepHunW_FtdhSErr21aP3_TZRHQYbXDTQqsZ4bd-uh4QxB_52mxXd8v9_vQ-iO6uJGQgK2uyiPFZswm-c_noQmKEh67psNL3sv-fGfqRnxNHJbMdwzJwGFXKOCS7LQnXElbao/s1600/blog_ASM1_image4.png" data-original-width="816" data-original-height="547" /></a>
<p class=cmdblack><span lang=EN-US> </span></p>
</div>
</body>
</html>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-52218666491187971202017-10-16T15:49:00.000+11:002019-09-13T14:50:19.104+10:00Huge Pages on Oracle Linux<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Environment</span></h4>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Oracle Linux Server release 7.3</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Oracle database 11.2, 12.1</span><br />
<h4 style="text-align: left;">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Quick checking</span></h4>
<pre class="screen"><code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;"># cat /proc/sys/vm/nr_hugepages</span></code></pre>
<pre class="screen"><code class="command"><span style="font-family: "courier new" , "courier" , monospace;">2054</span></code></pre>
<pre class="screen"><span style="font-family: "arial" , "helvetica" , sans-serif;">or</span></pre>
<pre class="screen"><code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;"># cat /proc/meminfo | grep Huge</span>
<span style="font-family: "courier new" , "courier" , monospace;">AnonHugePages: 0 kB
HugePages_Total: 2054
HugePages_Free: 546
HugePages_Rsvd: 542
HugePages_Surp: 0
Hugepagesize: 2048 kB</span>
</code></pre>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">checking if transparent huge pages are disabled</span></code></div>
<div>
<code class="command"></code><br />
<div>
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;"># cat /sys/kernel/mm/transparent_hugepage/enabled</span></code></div>
<code class="command">
</code>
<br />
<div>
<code class="command"><span style="font-family: "courier new" , "courier" , monospace;">always madvise [never]</span></code></div>
<code class="command">
</code>
<br />
<div>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Viewing database 12.1 alert log</span></div>
<code class="command">
</code>
<br />
<div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">**********************************************************************</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Dump of system resources acquired for SHARED GLOBAL AREA (SGA)</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> Per process system memlock (soft) limit = 128G</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> Expected per process system memlock (soft) limit to lock</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> SHARED GLOBAL AREA (SGA) into memory: 2050M</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> Available system pagesizes:</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> 4K, 2048K</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> Supported system pagesize(s):</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Mon Oct 16 12:28:54 2017</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> PAGESIZE <b>AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES</b> ERROR(s)</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> 4K Configured 3 3 NONE</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> 2048K </span><b style="color: #274e13; font-family: "Courier New", Courier, monospace;">2054 1025 1025</b><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> NONE</span></code></div>
<div>
<code class="command"><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">**********************************************************************</span></code></div>
</div>
<code class="command">
</code>
<br />
<div>
<code class="command"><span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></code></div>
<code class="command">
</code>
<br />
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">Viewing database 11.2 alert log</span></code></div>
<code class="command">
</code>
<br />
<div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><span style="color: #274e13;">************************ Large Pages Information *******************</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Per process system memlock (soft) limit = 128 GB</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;"><br /></span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Total Shared Global Region in Large Pages = 2050 MB (100%)</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;"><br /></span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Large Pages used by this instance: 1025 (2050 MB)</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Large Pages unused system wide = 4 (8192 KB)</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Large Pages configured system wide = 2054 (4108 MB)</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">Large Page size = 2048 KB</span></code></div>
<div style="font-family: "Courier New", Courier, monospace;">
<code class="command"><span style="color: #274e13;">********************************************************************</span></code></div>
<div style="font-size: small;">
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">If number of Huge pages is less than required, than SGA will use both page types. Database 12.1 aler.log:</span></code></div>
<div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><span style="color: #274e13;"> PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES <b>ALLOCATED_PAGES</b> ERROR(s)</span></code></div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><span style="color: #274e13;"> 4K Configured 3 <b>187704</b> NONE</span></code></div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><span style="color: #274e13;"> 2048K 659 1025 <b>658</b> NONE</span></code></div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><br /></code></div>
<h4 style="text-align: left;">
<code class="command">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Simplified setup</span></code></h4>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><span style="font-family: "arial" , "helvetica" , sans-serif;">1. Have the </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">memlock </span><span style="font-family: "arial" , "helvetica" , sans-serif;">user limit set in </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">/etc/security/limits.conf</span><span style="font-family: "arial" , "helvetica" , sans-serif;"> file. Set the value (in KB) slightly smaller than total RAM (90%) , at least it must be bigger than HugePages size.</span></span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">* soft memlock 14680064</span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">* hard memlock 14680064</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;"></span></code><br />
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><span style="font-family: "arial" , "helvetica" , sans-serif;"><b>Atentu!</b> If you use systemd to auto startup databases, systemd ignores limits.conf. You need to add the following to <a href="https://alexzy.blogspot.com.au/2017/10/automating-oracle-database-shutdown-and.html" target="_blank">a service unit file</a></span></span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">[Service]</span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">LimitMEMLOCK=infinity</span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">LimitNOFILE=65535</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;"></span></code><br />
<code class="command"><span style="color: blue; font-family: "courier new" , "courier" , monospace;"><br /></span>
</code><br />
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">2. Get the required number of pages from alert.log (EXPECTED_PAGES) or by running Oracle script </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">hugepages_settings.sh</span><span style="font-family: "arial" , "helvetica" , sans-serif;"> (Doc ID 401749.1)</span></code></div>
</div>
</div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">3. edit </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">vm.nr_hugepages</span><span style="font-family: "arial" , "helvetica" , sans-serif;"> in </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">/etc/sysctl.conf</span><span style="font-family: "arial" , "helvetica" , sans-serif;"> as root</span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">4. to reload the parameters, reboot Linux or use </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">sysctl -p</span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><b>Proper setup and more info</b></span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">HugePages on Oracle Linux 64-bit (Doc ID 361468.1)</span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)</span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;">Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)</span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif;"><a href="https://docs.oracle.com/database/122/UNXAR/administering-oracle-database-on-linux.htm#UNXAR400" target="_blank">Oracle 12.2 Documentation: Administering Oracle Database on Linux</a></span></code></div>
<div>
<code class="command"><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: x-small;"><br /></span></code></div>
<div style="font-family: "Courier New", Courier, monospace; font-size: small;">
<code class="command"><br /></code></div>
</div>
<code class="command">
</code>
<div>
<code class="command"><br /></code></div>
<code class="command">
</code></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-84689781811535869282017-10-06T12:00:00.000+11:002017-10-17T12:36:34.171+11:00Automating Oracle database Shutdown and Startup by systemd<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Environment</h4>
Oracle Linux Server release 7.3 (or Redhat 7.3)<br />
Oracle database 12.1.0.2<br />
<h4 style="text-align: left;">
Documentation</h4>
MOSC Oracle Doc ID 2229679.1, Doc ID 2049901.1<br />
<a href="https://access.redhat.com/articles/754933">Overview of systemd for RHEL 7</a><br />
<h4 style="text-align: left;">
Automating by systemd</h4>
alternatives are Oracle Restart or SysV init ( /etc/init.d in older Linux).<br />
Service Unit file typically has extension .service and stored in<br />
<span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">/usr/lib/systemd/system</span><br />
<span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">/etc/systemd/system</span><br />
<span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">/usr/lib/systemd/user</span><br />
<span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">/etc/systemd/user</span><br />
For more details read <a href="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/system_administrators_guide/chap-managing_services_with_systemd">Chapter 9. Managing Services with systemd</a><br />
<br />
Create or edit a service unit file:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">cd /etc/systemd/system</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">vi oracle_database.service</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">[Unit]</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">Description=The Oracle Database Service</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">After=network.target</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">[Service]</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">Type=forking</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">RemainAfterExit=yes</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">KillMode=none</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">TimeoutStopSec=10min</span><br />
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"># memlock limit is needed for SGA to use HugePages</span><br />
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">LimitMEMLOCK=infinity</span><br />
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">LimitNOFILE=65535</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">User=oracle</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">Group=oinstall</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;"># </span><span style="color: #274e13; font-family: "courier new" , "courier" , monospace;">Please use absolute path here</span><br />
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"># ExecStart=$ORACLE_HOME/bin/dbstart $ORACLE_HOME &</span><br />
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"># First argument of dbstart is used to bring up Listener</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">ExecStart=/opt/oracle/product/12.1.0/se2_1/bin/dbstart /opt/oracle/product/12.1.0/se2_1 &</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">ExecStop=/opt/oracle/product/12.1.0/se2_1/bin/dbshut /opt/oracle/product/12.1.0/se2_1</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">Restart=no</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">[Install]</span><br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;"># Puts wants directive for the other units in the relationship</span><br />
<br />
<span style="background-color: #eeeeee; color: #274e13; font-family: "courier new" , "courier" , monospace;">WantedBy=default.target</span><br />
<div>
<br /></div>
<div>
First argument of dbstart/dbshut is used to bring up/shutdown Oracle Listener. This script will start all databases listed in the /etc/oratab file whose third field is a "Y". If you use ASM or cluster services, read more in dbstart description.<br />
<br />
<b>Huge Pages</b><br />
systemd ignores /etc/security/limits.conf. If HugePages are configured, you need to use LimitMEMLOCK and LimitNOFILE, otherwise SGA will use small pages and database alert log will show:</div>
<div>
<span style="color: #274e13; font-family: "courier new" , "courier" , monospace;"> Increase per process memlock (soft) limit to at least 2050MB to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory</span><br />
<br /></div>
<b>TimeoutStopSec</b>= Configures the time to wait for stop. If a service is asked to stop, but does not terminate in the specified time, it will be terminated forcibly via SIGTERM, and after another timeout of equal duration with SIGKILL (see KillMode= in systemd.kill(5)). Takes a unit-less value in seconds, or a time span value such as "5min 20s". Pass "infinity" to disable the timeout logic. Defaults to DefaultTimeoutStopSec= from the manager configuration file (see systemd-system.conf(5)).<br />
For more info use<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">man systemd.service</span><br />
<br />
reload systemd and enable the service:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">systemctl daemon-reload</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">systemctl enable oracle_database.service</span><br />
list services:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">systemctl list-unit-files --type service|grep oracle</span><br />
<span style="font-family: inherit;">Start the service and check its status</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">systemctl start oracle_database.service</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">systemctl status oracle_database.service</span><br />
<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com3tag:blogger.com,1999:blog-7203079970609015719.post-42943734811241808482017-08-09T17:08:00.000+10:002017-08-09T17:11:07.636+10:00Migrating Oracle VM from version 2.2 to 3.4<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
Migrating a virtual machine from Oracle VM Server 2.2 to Oracle VM Server 3.4</div>
<h3 style="text-align: left;">
<span style="color: #741b47;">
Environment</span></h3>
<h2>
<o:p></o:p></h2>
<div class="MsoNormal">
Oracle VM server release 2.2.2<o:p></o:p></div>
<div class="MsoNormal">
Oracle VM server release 3.4.3<o:p></o:p></div>
<div class="MsoNormal">
VMs: Redhat/Oracle Linux 5,6,7.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<h3 style="text-align: left;">
<span style="color: #741b47;">
Importing VM2 to VM3 Template</span></h3>
<h2>
<o:p></o:p></h2>
<div class="MsoNormal">
Shutdown VM in v2 environment.<o:p></o:p></div>
<div class="MsoNormal">
Locate <b>System.img</b>
and <b>vm.cfg</b>, e.g.<o:p></o:p></div>
<div class="StyleCMD">
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">pwd</span><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">ll</span><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">total 69206016<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">-rw------- 1 root root
53687091200 Feb 10 2012 OPTORACLE.img<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">-rw------- 1 root root
17179869184 Feb 10 2012 System.img<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">-rw------- 1 root root 778 Jul
5 11:24 vm.cfg<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">-rw------- 1 root root 666 Feb 10 2012 vm.cfg.orig</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Backup vm.cfg<o:p></o:p></div>
<div class="StyleCMD">
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">cp vm.cfg vm.cfg.backup</span><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Modify <b>vm.cfg</b> and remove all disks except System.img. Other
disks will be imported separately, I just don’t want them to be included in a
template, to keep the template small.<o:p></o:p></div>
<div class="StyleCMD">
<span style="font-size: 8.0pt;"><span style="font-family: "courier new" , "courier" , monospace;">disk =
['file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10/System.img,xvda,w',<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: red; font-size: 8.0pt;"><span style="font-family: "courier new" , "courier" , monospace;">'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10/OPTORACLE.img,xvdb,w',<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: red; font-size: 8.0pt;"><span style="font-family: "courier new" , "courier" , monospace;">'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk/PIRUAT.img,xvdc,w!',<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="color: red; font-size: 8.0pt;"><span style="font-family: "courier new" , "courier" , monospace;">'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk/PIRBUAT_ARCHIVE.img,xvdd,w!',<o:p></o:p></span></span></div>
<div class="StyleCMD">
<span style="font-size: 8.0pt;"><span style="font-family: "courier new" , "courier" , monospace;">]</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Check/Kill IPtables<o:p></o:p></div>
<div class="MsoNormal">
Share the current directory<o:p></o:p></div>
<div class="StyleCMD">
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">python -m SimpleHTTPServer 80</span><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">Serving HTTP on 0.0.0.0 port
80 ...</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
Go to VM3 Manager and <b>Import
VM Template</b>. All disks mentioned in vm.cfg must be included in the URLs if
you didn’t delete them from vm.cfg.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuarHLfZiTpytz_nqpkC0s1afhhmnLl3lajnx4QGooa-3JqZGh6rDXIdZXjmmfRjlhS3Qr1JkiqgHV3qwXZ-rhnWuVxsjosgYM7DxTVWUeCtrogyZBkOnk6VBxR4VP_Q2e2LQ75G63H0w/s1600/image001.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="348" data-original-width="741" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuarHLfZiTpytz_nqpkC0s1afhhmnLl3lajnx4QGooa-3JqZGh6rDXIdZXjmmfRjlhS3Qr1JkiqgHV3qwXZ-rhnWuVxsjosgYM7DxTVWUeCtrogyZBkOnk6VBxR4VP_Q2e2LQ75G63H0w/s1600/image001.png" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYN1U-Q-jZMe8_lLKZPKaJ4TG6ciugiHnh0PJFo7mU9e79zhA1jA7rBypjgWHYGwNqwb0UrJDPhlsy2S8VEooFlmYT5lj47QCXQc-6OXu3WxcTmLMHO5f3CDVC12Sm73ZC5nSN3WGj8Xs/s1600/image002.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="358" data-original-width="549" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYN1U-Q-jZMe8_lLKZPKaJ4TG6ciugiHnh0PJFo7mU9e79zhA1jA7rBypjgWHYGwNqwb0UrJDPhlsy2S8VEooFlmYT5lj47QCXQc-6OXu3WxcTmLMHO5f3CDVC12Sm73ZC5nSN3WGj8Xs/s1600/image002.png" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Rename the template from <b>System.img</b> to something like <b>dtoocl10_template_vm2</b>,
if migrating more than one VM otherwise you’ll have multiple templates with the
same name.<o:p></o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Rename the Virtual Disk from <b>System.img</b> to something like <b>template_dtoocl10_System.img<o:p></o:p></b></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipr_4QZjqj_-XIKpCgCG55Rt8Q9ttGDHs1ipsqADVZkEzXMdL7L_ffzTYueopfB3VXRElN_bfDVgHokq6erFTFX-yDZoP0-wr9jBTCEUHSikXjUw2GlTE6SeSWJ3SjNMDZnO09XTOmXLk/s1600/image003.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="130" data-original-width="455" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipr_4QZjqj_-XIKpCgCG55Rt8Q9ttGDHs1ipsqADVZkEzXMdL7L_ffzTYueopfB3VXRElN_bfDVgHokq6erFTFX-yDZoP0-wr9jBTCEUHSikXjUw2GlTE6SeSWJ3SjNMDZnO09XTOmXLk/s1600/image003.png" /></a></div>
<div class="MsoNormal">
On VM2 hypervisor:<o:p></o:p></div>
<div class="MsoNormal">
Kill SimpleHTTPServer<o:p></o:p></div>
<div class="MsoNormal">
Restore <b>vm.cfg</b><o:p></o:p></div>
<div class="StyleCMD">
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">cp vm.cfg.backup vm.cfg</span><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">cp: overwrite `vm.cfg'? y</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Locate other virtual disks, likely in “sharedDisk”, e.g.<o:p></o:p></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Go to that directory and share it<o:p></o:p></div>
<div class="StyleCMD">
<span style="font-family: "courier new" , "courier" , monospace;"><span style="color: blue;">python -m SimpleHTTPServer 80</span><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: windowtext;"><span style="font-family: "courier new" , "courier" , monospace;">Serving HTTP on 0.0.0.0 port
80 ...</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Go to VM3 Manager and <b>Import
Virtual Disk</b>.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghdlnzdk2TF07IQq_VmxxlcPgtBV9fkdjzWVBx8oYtIBgmAG6VQu1uoqMwNu8z2ZAadT9M-AUW9PSez7ZSrmyFJkASCts5BItG7-lU3qIfJn5y1QhxKA2-UdGEH1GWcyltXdQSgPh183s/s1600/image004.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="204" data-original-width="465" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghdlnzdk2TF07IQq_VmxxlcPgtBV9fkdjzWVBx8oYtIBgmAG6VQu1uoqMwNu8z2ZAadT9M-AUW9PSez7ZSrmyFJkASCts5BItG7-lU3qIfJn5y1QhxKA2-UdGEH1GWcyltXdQSgPh183s/s1600/image004.png" /></a></div>
<div class="MsoNormal">
Rename the virtual disk if needed.<o:p></o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Kill SimpleHTTPServer<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<h3 style="text-align: left;">
<span style="color: #741b47;">
Creating VM</span></h3>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivM3IlQS1VaJV0u714T-xeatnUv4vTnH1E5GFHMhAwl-la8iyGEjc-hauCnw_BZvuuh08MefhZrLawJ6oYTjoUUiJ5Kf9yTMmhsXKM2M1lJAnlIV9Tr7a4cev5jspk85wHZfcTcP8OTlE/s1600/image011.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="301" data-original-width="573" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivM3IlQS1VaJV0u714T-xeatnUv4vTnH1E5GFHMhAwl-la8iyGEjc-hauCnw_BZvuuh08MefhZrLawJ6oYTjoUUiJ5Kf9yTMmhsXKM2M1lJAnlIV9Tr7a4cev5jspk85wHZfcTcP8OTlE/s1600/image011.png" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifLQsAVyXZGMIx9ijWWa6sifdh8zVZeFcz_c17fuQFEVeeONiQWVBimk6oDwA79lCRUdhSB_c35yPYPTB3q5c7UpubkhKzLnkHDq_6lB0VSAZAtCVNAS6W6tL2qDLib4eU6_3hOpSEurQ/s1600/image012.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="121" data-original-width="464" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifLQsAVyXZGMIx9ijWWa6sifdh8zVZeFcz_c17fuQFEVeeONiQWVBimk6oDwA79lCRUdhSB_c35yPYPTB3q5c7UpubkhKzLnkHDq_6lB0VSAZAtCVNAS6W6tL2qDLib4eU6_3hOpSEurQ/s1600/image012.png" /></a></div>
<div>
<div class="MsoNormal">
Rename the VM<o:p></o:p></div>
<div class="MsoNormal">
Set Operating System<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4ZaIPWsNDl4HL8M2d1Vd2RNUksphL-FBsedLSP2jsF49wrI2aqwa-4LlNGXwKY_5Tt2SXLJI-A5EqzPX66EEBe2rFdm4uz7Y-kZ-Ouap10mp5_Hali3OYlNtU_LaXZGgCVPRf56tcMLU/s1600/image007.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="543" data-original-width="738" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4ZaIPWsNDl4HL8M2d1Vd2RNUksphL-FBsedLSP2jsF49wrI2aqwa-4LlNGXwKY_5Tt2SXLJI-A5EqzPX66EEBe2rFdm4uz7Y-kZ-Ouap10mp5_Hali3OYlNtU_LaXZGgCVPRf56tcMLU/s1600/image007.png" /></a></div>
<div class="MsoNormal">
Setup network at <b>Networks</b>
tab<o:p></o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Add other virtual disks at <b>Disks</b> tab, e.g. /opt/oracle or DB disk.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4cFgRVO4EfaXsPbQHZ_cZsK0UjLXDvM3tzhJZpTTcl5LwQHhi5CAYNG7ByOGSVCZZ6Hlc6TBUnQWqtTjyX68UGVzozwuG0HYA18mQuXzSW9zf5PyeypjHUdU9EiD-RZ_VpJBTGhaaV6I/s1600/image009.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="216" data-original-width="740" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4cFgRVO4EfaXsPbQHZ_cZsK0UjLXDvM3tzhJZpTTcl5LwQHhi5CAYNG7ByOGSVCZZ6Hlc6TBUnQWqtTjyX68UGVzozwuG0HYA18mQuXzSW9zf5PyeypjHUdU9EiD-RZ_VpJBTGhaaV6I/s1600/image009.png" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBqPIN1KhSFwD7JG857fJyj-VPQ2TsY14y7UEZrSZkdsMv1kEH6IznL23In8btch0REiAZm8AEVgY7LK-t5-9DNfOTodZmt6qufIQC2w1fvYzinWXz-QvFxE79WvwhiXqMeetEt7O6oQg/s1600/image013.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="203" data-original-width="436" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBqPIN1KhSFwD7JG857fJyj-VPQ2TsY14y7UEZrSZkdsMv1kEH6IznL23In8btch0REiAZm8AEVgY7LK-t5-9DNfOTodZmt6qufIQC2w1fvYzinWXz-QvFxE79WvwhiXqMeetEt7O6oQg/s1600/image013.png" /></a></div>
<div class="MsoNormal">
Rename SYSTEM virtual disk, and other disks if needed.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJV-G845R2PftR0qgegx7ft9D4LKsRh71nvmwg_XYVV8nzwuFiYL574PNtNFhZLdTc5SbnvbN570uOWFra-mzrn4d5m5Iwmq_S48dXSKBqX-2zCRGWaJcn2x3lF9FGMW96Oje20IVitiY/s1600/image006.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="227" data-original-width="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJV-G845R2PftR0qgegx7ft9D4LKsRh71nvmwg_XYVV8nzwuFiYL574PNtNFhZLdTc5SbnvbN570uOWFra-mzrn4d5m5Iwmq_S48dXSKBqX-2zCRGWaJcn2x3lF9FGMW96Oje20IVitiY/s1600/image006.png" /></a></div>
<div class="MsoNormal">
Modify <b>vm.cfg</b>
file with <span style="color: #2e74b5; font-family: "courier new"; mso-themecolor: accent1; mso-themeshade: 191;"><b>extra='S'</b></span><span style="color: #2e74b5; mso-themecolor: accent1; mso-themeshade: 191;"> </span>to boot in Single user mode.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8e2E47K-2EADz9vCk7BB1o7EMbMoctygvQMgkqZtxPTEB8GPJdF6RuzBHasDns_1SJvEVHGqCzRo5fcsj0kIG53JaGu2eXO56_wSjFfs8pQfBpZlDP_SE1cryGaShoyS-V5VgWLNKFTs/s1600/image008.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="258" data-original-width="718" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8e2E47K-2EADz9vCk7BB1o7EMbMoctygvQMgkqZtxPTEB8GPJdF6RuzBHasDns_1SJvEVHGqCzRo5fcsj0kIG53JaGu2eXO56_wSjFfs8pQfBpZlDP_SE1cryGaShoyS-V5VgWLNKFTs/s1600/image008.png" /></a></div>
<br /></div>
<div class="MsoNormal">
Start the VM in Single user mode.<o:p></o:p></div>
<div class="MsoNormal">
Launch Console.<o:p></o:p></div>
<div class="MsoNormal">
Check that the disks are mounted<o:p></o:p></div>
<div class="MsoNormal">
Change <b>IP address</b>
in <span class="StyleCMDChar"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">/etc/hosts</span></span><o:p></o:p></div>
<div class="MsoNormal">
Change <b>IP address</b>
and <b>Gateway</b> in <span class="StyleCMDChar"><span style="color: blue; font-family: "courier new" , "courier" , monospace;">/etc/sysconfig/network-scripts/ifcfg-eth0</span> </span>(Oracle
Linux 5 in this case)<o:p></o:p></div>
<div class="MsoNormal">
Shutdown the VM<o:p></o:p></div>
<div class="MsoNormal">
Update DNS entry<o:p></o:p></div>
<div class="MsoNormal">
Remove <b>extra=’S’</b> from vm.cfg<o:p></o:p></div>
<div class="MsoNormal">
Start the VM<o:p></o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
SSH to the VM and check DB and Apps. (You might need <span style="color: #2e74b5; font-family: "courier new"; mso-themecolor: accent1; mso-themeshade: 191;">ipconfig /flushdns</span>)</div>
<div class="MsoNormal">
<br /></div>
<h3 style="text-align: left;">
<span style="color: #741b47;">
Cleaning up</span></h3>
<h2>
<o:p></o:p></h2>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Remove imported templates.<o:p></o:p></div>
<div class="MsoNormal">
<o:p></o:p></div>
</div>
<h2>
<o:p></o:p></h2>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-59102634695718325432017-08-07T16:06:00.000+10:002017-08-17T13:48:17.293+10:00Oracle EM 12c - reporting filesystem usage across all hosts<div dir="ltr" style="text-align: left;" trbidi="on">
Environment:<br />
Oracle Enterprise Manager 12c Cloud Control.<br />
<br />
in SYSMAN schema:<br />
<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">select target_name, mountpoint,</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">round((freeb/1073741824),2) as "Free, GiB",</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">round((sizeb/1073741824),2) as "Size, GiB",</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">round((usedb/1073741824),2) as "Used, GiB",</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">round((((sizeb-freeb)/sizeb)*100),2) as "Used, %"</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">from MGMT$STORAGE_REPORT_LOCALFS</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">where filesystem_type not in ('iso9660','devtmpfs') </span><span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">-- excluding some filesystems</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">order by "Used, %" desc</span><br />
<br />
<span style="font-family: inherit;">Attention!</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;"> (sizeb-freeb)/sizeb</span><span style="font-family: inherit;"> is not the same as</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;"> </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">usedb/</span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">sizeb</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">Use% </span><span style="font-family: inherit;">of </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">df </span><span style="font-family: inherit;">command shows the same as</span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="color: blue; font-family: "courier new" , "courier" , monospace;">(sizeb-freeb)/sizeb</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: inherit;">Creating the report:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf1tNBjkDWyBmuVBAtiyjSFkWXHHSPjHnayQWQ-sJIWAJSfBbnQDAWlzSrvV3OkpaxiBWMX9skUEQQlnPavPs8gob0lE8Gh625DlRwSbB41TDIfWBZnG7fxVXpctGs9PhKUu5s8GW1z3s/s1600/oem_report.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="274" data-original-width="350" height="312" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf1tNBjkDWyBmuVBAtiyjSFkWXHHSPjHnayQWQ-sJIWAJSfBbnQDAWlzSrvV3OkpaxiBWMX9skUEQQlnPavPs8gob0lE8Gh625DlRwSbB41TDIfWBZnG7fxVXpctGs9PhKUu5s8GW1z3s/s400/oem_report.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixBY3_fhoPRU2pjFBAxiINoIoZ7vZPJ1VSZUDOWDIEMkGLxZk_d3GVOF0ZVwppM45FPNYpjNdctg_oMlBCBcKqI8Ls6NTBPxbRYqEYhbjyIQdUvp7si3JJDBQNQ_v9Rpj0Y6uth_isi1c/s1600/oem_report2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="667" data-original-width="654" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixBY3_fhoPRU2pjFBAxiINoIoZ7vZPJ1VSZUDOWDIEMkGLxZk_d3GVOF0ZVwppM45FPNYpjNdctg_oMlBCBcKqI8Ls6NTBPxbRYqEYhbjyIQdUvp7si3JJDBQNQ_v9Rpj0Y6uth_isi1c/s640/oem_report2.JPG" width="625" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhevHDEqeamWMUfUSoWqsWG8o5uyolodqFVbdad-PK77pjkEOTOel6JEM0sjrAmXA9algsCMkTk2sbrjN3YIhMcGTrwtGgVXiMPeVanOf6iHd-86MWAC33k_WvLqBTPcSrTGUDFjmr7QmI/s1600/oem_report3.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="272" data-original-width="440" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhevHDEqeamWMUfUSoWqsWG8o5uyolodqFVbdad-PK77pjkEOTOel6JEM0sjrAmXA9algsCMkTk2sbrjN3YIhMcGTrwtGgVXiMPeVanOf6iHd-86MWAC33k_WvLqBTPcSrTGUDFjmr7QmI/s400/oem_report3.JPG" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiOKjLiSWw0pEuvcbPxqFHJrUYlG3wOJBUfWvl6P55K1EuBYmr3KETk5EIJQrQOtZJ54h2azkN9nenMfGcckZm4Ni9WlL-tiYYMi0uQg29gV4YuKP9wTuUQtV6oZRX-xnj8UAeb5nXFso/s1600/oem_report4.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="524" data-original-width="496" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiOKjLiSWw0pEuvcbPxqFHJrUYlG3wOJBUfWvl6P55K1EuBYmr3KETk5EIJQrQOtZJ54h2azkN9nenMfGcckZm4Ni9WlL-tiYYMi0uQg29gV4YuKP9wTuUQtV6oZRX-xnj8UAeb5nXFso/s400/oem_report4.JPG" width="376" /></a></div>
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-31174249437868887482017-05-15T15:40:00.000+10:002017-08-09T10:21:14.685+10:00redeploying OEM agent after cloning VM<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Environment:</h4>
Oracle Linux 7.3, OEM agent 12.1.0.5.0<br />
<h4 style="text-align: left;">
Solution:</h4>
prepare or re-use (if it was used for deployment) a response file, e.g. agent.rsp.<br />
If reusing the existing file, you have to add OMS_HOST and EM_UPLOAD_PORT to the file.<br />
If the original agent wast deployed by Pull method, you can get EM_UPLOAD_PORT from AgentPull.sh:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">grep -i omsPort= AgentPull.sh</span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">omsPort=4900</span><br />
<br />
so an example of the response file:<br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">OMS_HOST=<i>oms_host_name</i></span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">EM_UPLOAD_PORT=4900</span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">LOGIN_USER=sysman</span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">LOGIN_PASSWORD=<i>pasvorto1</i></span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">PLATFORM="Linux x86-64"</span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">VERSION=12.1.0.5.0</span><br />
<span style="color: #38761d; font-family: "courier new" , "courier" , monospace;">AGENT_REGISTRATION_PASSWORD=<i>pasvorto2</i></span><br />
<div>
<br /></div>
Delete files from AGENT_BASE_DIR/agent_inst<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">cd /opt/oracle/product/agent/agent_inst</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">rm -rf *</span><br />
<br />
and run agentDeploy.sh script to redeloy the agent:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">cd /opt/oracle/product/agent</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">./agentDeploy.sh AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp</span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Validating the OMS_HOST & EM_UPLOAD_PORT</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -prereq</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Validating oms host & port with url: http://dtooem2:4900/empbs/genwallet</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Validating oms host & port with url: https://dtooem2:4900/empbs/genwallet</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Return status:3-oms https port is passed</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Unzipping the agentcoreimage.zip to /opt/oracle/product/agent ....</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">plugin.zip</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command : /opt/oracle/product/agent/unzip -o /opt/oracle/product/agent/plugin.zip -d /opt/oracle/product/agent</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Successfully unzipped /opt/oracle/product/agent/plugin.zip to /opt/oracle/product/agent !</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/xmlparserv2.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/srvm.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/emCfg.jar:/opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/share.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent /opt/oracle/product/agent/agent_inst AGENT_BASE_DIR=/opt/oracle/product/agent</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing agent install prereqs...</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs -prereqchecker -silent -ignoreSysPrereqs -waitForCompletion -prereqlogloc /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy -entryPoint oracle.sysman.top.agent_Complete -detailedExitCodes PREREQ_CONFIG_LOCATION=/opt/oracle/product/agent/core/12.1.0.5.0/prereqs -J-DAGENT_BASE_DIR=/opt/oracle/product/agent</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Agent install prereqs completed successfully</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Cloning the agent home...</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 -responseFile /opt/oracle/product/agent.rsp AGENT_BASE_DIR=/opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -noconfig ORACLE_HOME_NAME=agent12c2 -force b_noUpgrade=true AGENT_PORT=-1 EMCTLCFG_MODE=NONE</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Cloning of agent home completed successfully</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Attaching sbin home...</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs -attachHome -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/sbin </span><span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">ORACLE_HOME_NAME=sbin12c2</span><span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"> -force</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Attach home for sbin home completed successfully.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Updating home dependencies...</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST={/opt/oracle/product/agent/sbin:/opt/oracle/product/agent/core/12.1.0.5.0} -invPtrLoc /opt/oracle/product/agent/core/12.1.0.5.0/oraInst.loc -force</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Update home dependency completed successfully.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runConfig.sh ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 RESPONSE_FILE=/opt/oracle/product/agent/core/12.1.0.5.0/agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml} RERUN=true</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Agent Configuration completed successfully</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">The following configuration scripts need to be executed as the "root" user.</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">#!/bin/sh</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">#Root script to run</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> /opt/oracle/product/agent/core/12.1.0.5.0/root.sh</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">To execute the configuration scripts:</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">1. Open a terminal window</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">2. Log in as "root"</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">3. Run the scripts</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Agent Deployment Successful.</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Agent deployment log location: /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy/agentDeploy_2017-05-12_15-28-29-PM.log</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: xx-small;">Agent deployment completed successfully.</span><br />
<br />
<h4 style="text-align: left;">
Some observations:</h4>
<div>
agentDeploy.sh script runs with <span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">ORACLE_HOME_NAME=sbin12c2</span>, but old default name <span style="background-color: white; color: blue; font-family: "courier new" , "courier" , monospace;">sbin12c1</span> remains in central inventory. You can see it in oraInventory/ContentsXML/inventory.xml or by "<span style="color: blue; font-family: "courier new" , "courier" , monospace;">opatch lsinv -all</span>".</div>
<div>
<br /></div>
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-72274627092517459602017-05-04T15:09:00.001+10:002017-05-04T15:09:20.616+10:00Indexing NULL values<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Environment: </h4>
Oracle database 11.2.0.4<br />
<h4 style="text-align: left;">
Solution:</h4>
Create an index and add a constant to the end of the index so NULL values are stored.<br />
<br />
For the following statement and regular index on PAYMENT_CLASSIFICATION_ID<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID is null;</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Plan hash value: 2353806608</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;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</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;">| 0 | SELECT STATEMENT | | 434 | 38626 | 423K (1)| 01:24:38 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">|* 1 | TABLE ACCESS FULL| F_PAYMENT_ITEM | 434 | 38626 | 423K (1)| 01:24:38 |</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;">Predicate Information (identified by operation id):</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;"> </span><span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> 1 - filter("PAYMENT_CLASSIFICATION_ID" IS NULL)</span><br />
<br />
<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">DROP INDEX CDR.FPI_PAYMNT_CLS_ID;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
<span style="color: blue; font-family: Courier New, Courier, monospace;">CREATE INDEX CDR.FPI_PAYMNT_CLS_ID ON CDR.F_PAYMENT_ITEM</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">(PAYMENT_CLASSIFICATION_ID,</span><span style="color: red; font-family: Courier New, Courier, monospace;"><b>1</b></span><span style="color: blue; font-family: Courier New, Courier, monospace;">);</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
Plan with the new index:<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;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</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;">| 0 | SELECT STATEMENT | | 434 | 38626 | 82 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| 1 | TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM | 434 | 38626 | 82 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">|* 2 | INDEX RANGE SCAN | FPI_PAYMNT_CLS_ID | 434 | | 5 (0)| 00:00:01 |</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;">Predicate Information (identified by operation id):</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;"> 2 - access("PAYMENT_CLASSIFICATION_ID" IS 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;"><br /></span>
Index type of the new index is FUNCTION-BASED NORMAL (from DBA_INDEXES).<br />
One of disadvantages of Function-Based Indexes, according to Database Advanced Application Developer's Guide, chapter 4, Using Indexes in Database Applications:<br />
- The database does not use function-based indexes when doing OR expansion.<br />
<br />
but for this particular index, it works fine:<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID in (122,1);</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<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;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</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;">| 0 | SELECT STATEMENT | | 2756 | 239K| 497 (0)| 00:00:06 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| 1 | INLIST ITERATOR | | | | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| 2 | TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM | 2756 | 239K| 497 (0)| 00:00:06 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">|* 3 | INDEX RANGE SCAN | FPI_PAYMNT_CLS_ID | 2756 | | 11 (0)| 00:00:01 |</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;">Predicate Information (identified by operation id):</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">---------------------------------------------------</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 3 - access("PAYMENT_CLASSIFICATION_ID"=1 OR "PAYMENT_CLASSIFICATION_ID"=122)</span><br />
<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-78458071252512034592017-05-01T15:53:00.000+10:002017-08-09T10:21:14.688+10:00After cloning VM with Oracle database<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;
mso-font-charset:2;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:0 268435456 0 0 -2147483648 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:0;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-536870145 1107305727 0 0 415 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-536870145 1073786111 1 0 415 0;}
@font-face
{font-family:Cambria;
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:0;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-536870145 1073743103 0 0 415 0;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-1593833729 1073750107 16 0 415 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
h1
{mso-style-priority:9;
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-link:"Heading 1 Char";
mso-style-next:Normal;
margin-top:12.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:115%;
mso-pagination:widow-orphan lines-together;
page-break-after:avoid;
mso-outline-level:1;
font-size:16.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#365F91;
mso-themecolor:accent1;
mso-themeshade:191;
mso-font-kerning:0pt;
mso-fareast-language:EN-US;
font-weight:normal;}
h2
{mso-style-priority:9;
mso-style-qformat:yes;
mso-style-link:"Heading 2 Char";
mso-style-next:Normal;
margin-top:2.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:115%;
mso-pagination:widow-orphan lines-together;
page-break-after:avoid;
mso-outline-level:2;
font-size:13.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#365F91;
mso-themecolor:accent1;
mso-themeshade:191;
mso-fareast-language:EN-US;
font-weight:normal;}
h3
{mso-style-priority:9;
mso-style-qformat:yes;
mso-style-link:"Heading 3 Char";
mso-style-next:Normal;
margin-top:2.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:115%;
mso-pagination:widow-orphan lines-together;
page-break-after:avoid;
mso-outline-level:3;
font-size:12.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#243F60;
mso-themecolor:accent1;
mso-themeshade:127;
mso-fareast-language:EN-US;
font-weight:normal;}
p.MsoToc2, li.MsoToc2, div.MsoToc2
{mso-style-update:auto;
mso-style-priority:39;
mso-style-next:Normal;
margin-top:0cm;
margin-right:0cm;
margin-bottom:5.0pt;
margin-left:11.0pt;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
mso-themecolor:hyperlink;
text-decoration:underline;
text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-noshow:yes;
mso-style-priority:99;
color:purple;
mso-themecolor:followedhyperlink;
text-decoration:underline;
text-underline:single;}
pre
{mso-style-noshow:yes;
mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Courier New";
mso-fareast-font-family:"Times New Roman";}
p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing
{mso-style-priority:1;
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"No Spacing Char";
margin:0cm;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
mso-style-unhide:no;
mso-style-qformat:yes;
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:36.0pt;
mso-add-space:auto;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst
{mso-style-priority:34;
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-type:export-only;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
mso-add-space:auto;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle
{mso-style-priority:34;
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-type:export-only;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
mso-add-space:auto;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast
{mso-style-priority:34;
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-type:export-only;
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:36.0pt;
mso-add-space:auto;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
p.MsoTocHeading, li.MsoTocHeading, div.MsoTocHeading
{mso-style-priority:39;
mso-style-qformat:yes;
mso-style-parent:"Heading 1";
mso-style-next:Normal;
margin-top:12.0pt;
margin-right:0cm;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
line-height:107%;
mso-pagination:widow-orphan lines-together;
page-break-after:avoid;
font-size:16.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#365F91;
mso-themecolor:accent1;
mso-themeshade:191;
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
span.Heading2Char
{mso-style-name:"Heading 2 Char";
mso-style-priority:9;
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 2";
mso-ansi-font-size:13.0pt;
mso-bidi-font-size:13.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#365F91;
mso-themecolor:accent1;
mso-themeshade:191;}
span.Heading3Char
{mso-style-name:"Heading 3 Char";
mso-style-priority:9;
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 3";
mso-ansi-font-size:12.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#243F60;
mso-themecolor:accent1;
mso-themeshade:127;}
span.Heading1Char
{mso-style-name:"Heading 1 Char";
mso-style-priority:9;
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 1";
mso-ansi-font-size:16.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Cambria","serif";
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:major-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:major-fareast;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:major-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:major-bidi;
color:#365F91;
mso-themecolor:accent1;
mso-themeshade:191;}
p.myStyle1, li.myStyle1, div.myStyle1
{mso-style-name:myStyle1;
mso-style-unhide:no;
mso-style-parent:"No Spacing";
mso-style-link:"myStyle1 Char";
margin:0cm;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
span.NoSpacingChar
{mso-style-name:"No Spacing Char";
mso-style-priority:1;
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"No Spacing";}
span.myStyle1Char
{mso-style-name:"myStyle1 Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-parent:"No Spacing Char";
mso-style-link:myStyle1;}
p.CMDblua, li.CMDblua, div.CMDblua
{mso-style-name:"CMD blua";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"No Spacing";
mso-style-link:"CMD blua Char";
margin-top:6.0pt;
margin-right:0cm;
margin-bottom:6.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
font-size:9.0pt;
font-family:"Courier New";
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
color:#0070C0;
mso-fareast-language:EN-US;}
p.CMDnigra, li.CMDnigra, div.CMDnigra
{mso-style-name:"CMD nigra";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"CMD blua";
mso-style-link:"CMD nigra Char";
margin:0cm;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:9.0pt;
font-family:"Courier New";
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
color:black;
mso-themecolor:text1;
mso-fareast-language:EN-US;}
span.CMDbluaChar
{mso-style-name:"CMD blua Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-parent:"No Spacing Char";
mso-style-link:"CMD blua";
mso-ansi-font-size:9.0pt;
mso-bidi-font-size:9.0pt;
font-family:"Courier New";
mso-ascii-font-family:"Courier New";
mso-hansi-font-family:"Courier New";
mso-bidi-font-family:"Courier New";
color:#0070C0;}
span.CMDnigraChar
{mso-style-name:"CMD nigra Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-parent:"CMD blua Char";
mso-style-link:"CMD nigra";
mso-ansi-font-size:9.0pt;
mso-bidi-font-size:9.0pt;
font-family:"Courier New";
mso-ascii-font-family:"Courier New";
mso-hansi-font-family:"Courier New";
mso-bidi-font-family:"Courier New";
color:black;
mso-themecolor:text1;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"HTML Preformatted";
mso-ansi-font-size:10.0pt;
mso-bidi-font-size:10.0pt;
font-family:"Courier New";
mso-ascii-font-family:"Courier New";
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:"Courier New";
mso-bidi-font-family:"Courier New";
mso-fareast-language:EN-AU;}
span.SpellE
{mso-style-name:"";
mso-spl-e:yes;}
span.GramE
{mso-style-name:"";
mso-gram-e:yes;}
.MsoChpDefault
{mso-style-type:export-only;
mso-default-props:yes;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
.MsoPapDefault
{mso-style-type:export-only;
margin-bottom:10.0pt;
line-height:115%;}
@page WordSection1
{size:595.3pt 841.9pt;
margin:1.0cm 1.0cm 1.0cm 1.0cm;
mso-header-margin:35.4pt;
mso-footer-margin:35.4pt;
mso-paper-source:0;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:62224254;
mso-list-type:hybrid;
mso-list-template-ids:52975234 201916431 201916441 201916443 201916431 201916441 201916443 201916431 201916441 201916443;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:677079030;
mso-list-type:hybrid;
mso-list-template-ids:1769739182 201916417 201916419 201916421 201916417 201916419 201916421 201916417 201916419 201916421;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l2
{mso-list-id:789590994;
mso-list-type:hybrid;
mso-list-template-ids:-2012588318 201916417 201916419 201916421 201916417 201916419 201916421 201916417 201916419 201916421;}
@list l2:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l2:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l2:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l2:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l2:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l2:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l2:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l2:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l2:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l3
{mso-list-id:1360738296;
mso-list-type:hybrid;
mso-list-template-ids:-942364636 201916431 201916441 201916443 201916431 201916441 201916443 201916431 201916441 201916443;}
@list l3:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l3:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l3:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l3:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l4
{mso-list-id:1679692546;
mso-list-type:hybrid;
mso-list-template-ids:-730139566 201916431 201916441 201916443 201916431 201916441 201916443 201916431 201916441 201916443;}
@list l4:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l4:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l4:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l4:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l5
{mso-list-id:1685547198;
mso-list-type:hybrid;
mso-list-template-ids:-226833950 201916417 201916419 201916421 201916417 201916419 201916421 201916417 201916419 201916421;}
@list l5:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l5:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l5:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l5:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l5:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l5:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l5:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l5:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l5:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l6
{mso-list-id:1916434745;
mso-list-type:hybrid;
mso-list-template-ids:-2146023382 201916417 201916419 201916421 201916417 201916419 201916421 201916417 201916419 201916421;}
@list l6:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l6:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l6:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l6:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l6:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l6:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l6:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l6:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l6:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:EN-US;}
</style>
<![endif]--><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026"/>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1"/>
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-AU link=blue vlink=purple style='tab-interval:36.0pt'>
<div class=WordSection1><w:Sdt SdtDocPart="t" DocPartType="Table of Contents"
DocPartUnique="t" ID="1072395919">
<p class=MsoTocHeading><span lang=EN-US>Contents</span><span style='font-size:
11.0pt;line-height:107%;mso-ascii-font-family:Calibri;mso-ascii-theme-font:
minor-latin;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:
"Times New Roman";mso-bidi-theme-font:minor-bidi;color:windowtext;mso-ansi-language:
EN-AU'><w:sdtPr></w:sdtPr></span></p>
<p class=MsoToc2 style='tab-stops:right dotted 538.1pt'><!--[if supportFields]><span
style='mso-element:field-begin'></span><span
style='mso-spacerun:yes'> </span>TOC \o "1-3" \h \z \u <span
style='mso-element:field-separator'></span><![endif]--><span
class=MsoHyperlink><span style='mso-no-proof:yes'><a href="#_Toc481417531"><span
lang=EN-US style='mso-ansi-language:EN-US'>Environment</span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-tab-count:1 dotted'>. </span></span><!--[if supportFields]><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-element:field-begin'></span></span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'> PAGEREF _Toc481417531 \h </span><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-separator'></span></span><![endif]--><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'>1</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><!--[if gte mso 9]><xml>
<w:data>08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003400380031003400310037003500330031000000</w:data>
</xml><![endif]--></span><!--[if supportFields]><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-end'></span></span><![endif]--></a></span></span><span
style='mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;
mso-fareast-language:EN-AU;mso-no-proof:yes'><o:p></o:p></span></p>
<p class=MsoToc2 style='tab-stops:right dotted 538.1pt'><span
class=MsoHyperlink><span style='mso-no-proof:yes'><a href="#_Toc481417532"><span
lang=EN-US style='mso-ansi-language:EN-US'>Modifying Oracle Home</span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-tab-count:1 dotted'>. </span></span><!--[if supportFields]><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-element:field-begin'></span></span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'> PAGEREF _Toc481417532 \h </span><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-separator'></span></span><![endif]--><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'>1</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><!--[if gte mso 9]><xml>
<w:data>08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003400380031003400310037003500330032000000</w:data>
</xml><![endif]--></span><!--[if supportFields]><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-end'></span></span><![endif]--></a></span></span><span
style='mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;
mso-fareast-language:EN-AU;mso-no-proof:yes'><o:p></o:p></span></p>
<p class=MsoToc2 style='tab-stops:right dotted 538.1pt'><span
class=MsoHyperlink><span style='mso-no-proof:yes'><a href="#_Toc481417533"><span
lang=EN-US style='mso-ansi-language:EN-US'>Changing the DBID and Database Name
with DBNEWID utility</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-tab-count:1 dotted'>. </span></span><!--[if supportFields]><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-element:field-begin'></span></span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'> PAGEREF _Toc481417533 \h </span><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-separator'></span></span><![endif]--><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'>1</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><!--[if gte mso 9]><xml>
<w:data>08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003400380031003400310037003500330033000000</w:data>
</xml><![endif]--></span><!--[if supportFields]><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-end'></span></span><![endif]--></a></span></span><span
style='mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;
mso-fareast-language:EN-AU;mso-no-proof:yes'><o:p></o:p></span></p>
<p class=MsoToc2 style='tab-stops:right dotted 538.1pt'><span
class=MsoHyperlink><span style='mso-no-proof:yes'><a href="#_Toc481417534"><span
lang=EN-US style='mso-ansi-language:EN-US'>Re-creating control files</span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-tab-count:1 dotted'>. </span></span><!--[if supportFields]><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-element:field-begin'></span></span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'> PAGEREF _Toc481417534 \h </span><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-separator'></span></span><![endif]--><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'>1</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><!--[if gte mso 9]><xml>
<w:data>08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003400380031003400310037003500330034000000</w:data>
</xml><![endif]--></span><!--[if supportFields]><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-end'></span></span><![endif]--></a></span></span><span
style='mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;
mso-fareast-language:EN-AU;mso-no-proof:yes'><o:p></o:p></span></p>
<p class=MsoToc2 style='tab-stops:right dotted 538.1pt'><span
class=MsoHyperlink><span style='mso-no-proof:yes'><a href="#_Toc481417535">Changing
the DBID<span style='color:windowtext;display:none;mso-hide:screen;text-decoration:
none;text-underline:none'><span style='mso-tab-count:1 dotted'>.. </span></span><!--[if supportFields]><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'><span style='mso-element:field-begin'></span></span><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'> PAGEREF _Toc481417535 \h </span><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-separator'></span></span><![endif]--><span
style='color:windowtext;display:none;mso-hide:screen;text-decoration:none;
text-underline:none'>1</span><span style='color:windowtext;display:none;
mso-hide:screen;text-decoration:none;text-underline:none'><!--[if gte mso 9]><xml>
<w:data>08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003400380031003400310037003500330035000000</w:data>
</xml><![endif]--></span><!--[if supportFields]><span style='color:windowtext;
display:none;mso-hide:screen;text-decoration:none;text-underline:none'><span
style='mso-element:field-end'></span></span><![endif]--></a></span></span><span
style='mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;
mso-fareast-language:EN-AU;mso-no-proof:yes'><o:p></o:p></span></p>
<p class=MsoNormal><!--[if supportFields]><b><span style='mso-no-proof:yes'><span
style='mso-element:field-end'></span></span></b><![endif]--><o:p> </o:p></p>
</w:Sdt>
<h2><a name="_Toc481417531"><span lang=EN-US style='mso-ansi-language:EN-US'>Environment</span></a><span
lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></h2>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>Oracle
Linux Server release 7.3, Oracle database 12.1.0.2<o:p></o:p></span></p>
<h2><a name="_Toc481417532"><span lang=EN-US style='mso-ansi-language:EN-US'>Modifying
Oracle Home</span></a><span lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></h2>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>Check /<span
class=SpellE>etc</span>/<span class=SpellE>oratab</span>:<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpFirst style='text-indent:-18.0pt;mso-list:l3 level1 lfo2'><![if !supportLists]><span
lang=EN-US style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin;
mso-ansi-language:EN-US'><span style='mso-list:Ignore'>1.<span
style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span
lang=EN-US style='mso-ansi-language:EN-US'>Rename database in /<span
class=SpellE>etc</span>/<span class=SpellE>oratab</span> (after recreating
control file if doing so)<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpLast style='text-indent:-18.0pt;mso-list:l3 level1 lfo2'><![if !supportLists]><span
lang=EN-US style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin;
mso-ansi-language:EN-US'><span style='mso-list:Ignore'>2.<span
style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span
lang=EN-US style='mso-ansi-language:EN-US'>Remove/comment out other databases
in /<span class=SpellE>etc</span>/<span class=SpellE>oratab</span> if needed.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>Check <span
class=SpellE>tnsnames.ora</span>:<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpFirst style='text-indent:-18.0pt;mso-list:l4 level1 lfo1'><![if !supportLists]><span
lang=EN-US style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin;
mso-ansi-language:EN-US'><span style='mso-list:Ignore'>1.<span
style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span
lang=EN-US style='mso-ansi-language:EN-US'>Modify if necessary. It can contain
some Listener entries.<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpLast style='text-indent:-18.0pt;mso-list:l4 level1 lfo1'><![if !supportLists]><span
lang=EN-US style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin;
mso-ansi-language:EN-US'><span style='mso-list:Ignore'>2.<span
style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span
lang=EN-US style='mso-ansi-language:EN-US'>Create an entry for the new database<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>Listener:<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpFirst style='text-indent:-18.0pt;mso-list:l2 level1 lfo3'><![if !supportLists]><span
lang=EN-US style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol;mso-ansi-language:EN-US'><span style='mso-list:Ignore'>·<span
style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]><span lang=EN-US style='mso-ansi-language:EN-US'>Change
HOST in <span class=SpellE>listener.ora</span><o:p></o:p></span></p>
<p class=MsoListParagraphCxSpLast style='text-indent:-18.0pt;mso-list:l2 level1 lfo3'><![if !supportLists]><span
lang=EN-US style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol;mso-ansi-language:EN-US'><span style='mso-list:Ignore'>·<span
style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]><span lang=EN-US style='mso-ansi-language:EN-US'>Startup
listener<o:p></o:p></span></p>
<h2><a name="_Toc481417533"><span lang=EN-US style='mso-ansi-language:EN-US'>Changing
the DBID and Database Name with DBNEWID utility</span></a><span lang=EN-US
style='mso-ansi-language:EN-US'><o:p></o:p></span></h2>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>If you just
need to change DBID and DB name use <span class=SpellE><b style='mso-bidi-font-weight:
normal'><i style='mso-bidi-font-style:normal'>nid</i></b></span>, otherwise
consider recreating control file.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>Changing
the DBID and Database Name with DBNEWID utility (<span class=SpellE>nid</span>):<o:p></o:p></span></p>
<p class=MsoNormal><a
href="https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544"><span
lang=EN-US style='mso-ansi-language:EN-US'>https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544</span></a><span
lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></p>
<p class=CMDblua><span class=SpellE><span class=GramE>sqlplus</span></span> /
as <span class=SpellE>sysdba</span></p>
<p class=CMDblua><span class=GramE>shutdown</span> immediate</p>
<p class=CMDblua><span class=SpellE><span class=GramE>startup</span></span>
mount</p>
<p class=CMDblua><span class=GramE>exit</span></p>
<p class=CMDblua><span style='mso-fareast-language:EN-AU'><o:p> </o:p></span></p>
<p class=CMDblua><span class=SpellE><span class=GramE><span style='mso-fareast-language:
EN-AU'>nid</span></span></span><span style='mso-fareast-language:EN-AU'>
TARGET=SYS DBNAME=<span class=SpellE>new_name</span><o:p></o:p></span></p>
<p class=MsoNormal><o:p> </o:p></p>
<h2><a name="_Toc481417534"><span lang=EN-US style='mso-ansi-language:EN-US'>Re-creating
control files</span></a><span lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></h2>
<p class=MsoNormal><span lang=EN-US style='mso-ansi-language:EN-US'>If you need
to change more than just DB name, e.g. directory name, then it is easier to
recreate the control file.<o:p></o:p></span></p>
<p class=CMDblua><span class=SpellE><span class=GramE>sqlplus</span></span> /
as <span class=SpellE>sysdba</span></p>
<p class=CMDblua><span class=GramE>alter</span> database backup <span
class=SpellE>controlfile</span> to trace;</p>
<p class=MsoNoSpacing><span style='font-size:9.0pt;font-family:"Courier New";
color:#0070C0'><o:p> </o:p></span></p>
<p class=MsoNormal>Go to “trace” directory e.g.:</p>
<p class=CMDblua>/opt/oracle/<span class=SpellE>diag</span>/<span class=SpellE>rdbms</span>/mdrt115/MDRT115/trace</p>
<p class=MsoNormal>Or find where the trace directory is:</p>
<p class=CMDblua><span class=GramE>select</span> * from V$DIAG_INFO;</p>
<p class=MsoNormal>Copy the trace file to SQL file, e.g. <span class=SpellE>recreate_cf.sql</span></p>
<p class=MsoNormal>Modify the SQL script:</p>
<p class=MsoListParagraphCxSpFirst style='text-indent:-18.0pt;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin'><span
style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Remove all the lines <span class=SpellE>upto</span>
the statement “STARTUP NOMOUNT”</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin'><span
style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Change the database name</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin'><span
style='mso-list:Ignore'>3.<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Rename directories for <span class=SpellE>datafiles</span>
and redo files.</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin'><span
style='mso-list:Ignore'>4.<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Add SET after REUSE</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l0 level1 lfo4'><![if !supportLists]><span
style='mso-bidi-font-family:Calibri;mso-bidi-theme-font:minor-latin'><span
style='mso-list:Ignore'>5.<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>RESETLOGS<span style='mso-spacerun:yes'>
</span>NOARCHIVELOG</p>
<p class=MsoListParagraphCxSpMiddle><o:p> </o:p></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>STARTUP NOMOUNT<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>CREATE CONTROLFILE REUSE set
DATABASE "MDRP115" <span class=GramE>RESETLOGS<span
style='mso-spacerun:yes'> </span>NOARCHIVELOG</span><o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span
style='mso-spacerun:yes'> </span>MAXLOGFILES 16<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span
style='mso-spacerun:yes'> </span>MAXLOGMEMBERS 3<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span
style='mso-spacerun:yes'> </span>MAXDATAFILES 100<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span
style='mso-spacerun:yes'> </span>MAXINSTANCES 8<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span
style='mso-spacerun:yes'> </span>MAXLOGHISTORY 292<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>LOGFILE<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>GROUP 1 '/data001/<span class=SpellE>oradata</span>/MDRP115/<span
class=GramE>redo01.log'<span style='mso-spacerun:yes'> </span>SIZE</span> 50M
BLOCKSIZE 512,<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>GROUP 2 '/data001/<span class=SpellE>oradata</span>/MDRP115/<span
class=GramE>redo02.log'<span style='mso-spacerun:yes'> </span>SIZE</span> 50M
BLOCKSIZE 512,<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>GROUP 3 '/data001/<span class=SpellE>oradata</span>/MDRP115/<span
class=GramE>redo03.log'<span style='mso-spacerun:yes'> </span>SIZE</span> 50M
BLOCKSIZE 512<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>-- STANDBY LOGFILE<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>DATAFILE<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/system01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/sysaux01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/undotbs01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/users01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/XMETA001.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/XMETASR001.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/IAUSER001.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/DSODBSPACE01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/QSSRDSPACE01.dbf',<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'><span style='mso-spacerun:yes'>
</span>'/data001/<span class=SpellE>oradata</span>/MDRP115/ESDBSPACE01.dbf'<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>CHARACTER SET AL32UTF8<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><span style='font-size:9.0pt;line-height:
115%;font-family:"Courier New";color:#0070C0'>;<o:p></o:p></span></p>
<p class=MsoListParagraphCxSpMiddle><o:p> </o:p></p>
<p class=MsoListParagraphCxSpLast style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]><span style='font-size:9.0pt;line-height:115%;
font-family:"Verdana","sans-serif";color:#111111;background:white'>Shutdown the
database with immediate option. Do not abort.</span></p>
<p class=CMDblua><span class=GramE>shutdown</span> immediate</p>
<p class=MsoListParagraphCxSpFirst style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>create <span class=SpellE>pfile</span> from <span
class=SpellE>spfile</span>;</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>rename data file directories if needed (and
mount points in /<span class=SpellE>etc</span>/<span class=SpellE>fstab</span>)</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>rename the just created <span class=SpellE>pfile</span></p>
<p class=MsoListParagraphCxSpMiddle><span class=GramE>mv</span> initMDRT115.ora
initMDRP115.ora</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>edit the new <span class=SpellE>pfile</span> initMDRP115.ora
and change <span class=SpellE>dbname</span> and directory names:</p>
<p class=MsoListParagraphCxSpMiddle>.<span class=SpellE>audit_file_dest</span></p>
<p class=MsoListParagraphCxSpMiddle>.<span class=SpellE>control_files</span></p>
<p class=MsoListParagraphCxSpMiddle>.<span class=SpellE>db_name</span></p>
<p class=MsoListParagraphCxSpMiddle>.dispatchers</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Make sure directory for parameter <span
class=SpellE><b style='mso-bidi-font-weight:normal'>audit_file_dest</b></span>
exists.</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Modify /<span class=SpellE>etc</span>/<span
class=SpellE>oratab</span>, create an entry for the new database.</p>
<p class=MsoListParagraphCxSpMiddle style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Set new <span class=SpellE>env</span> variables
(<span class=SpellE>oraenv</span>)</p>
<p class=MsoListParagraphCxSpLast style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Create new <span class=SpellE>spfile</span> from
<span class=SpellE>pfile</span></p>
<p class=CMDblua><span class=GramE>create</span> <span class=SpellE>spfile</span>
from <span class=SpellE>pfile</span>;</p>
<p class=MsoListParagraph style='text-indent:-18.0pt;mso-list:l1 level1 lfo5'><![if !supportLists]><span
style='font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:
Symbol'><span style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Run SQL script to re-create control file</p>
<p class=CMDblua>@<span class=SpellE>recreate_cf.sql</span></p>
<p class=CMDnigra>ORACLE instance started.</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Total System Global Area 1493172224 bytes</p>
<p class=CMDnigra>Fixed Size<span style='mso-spacerun:yes'>
</span>2924592 bytes</p>
<p class=CMDnigra>Variable Size<span style='mso-spacerun:yes'>
</span>536874960 bytes</p>
<p class=CMDnigra>Database Buffers<span style='mso-spacerun:yes'>
</span>939524096 bytes</p>
<p class=CMDnigra>Redo Buffers<span style='mso-spacerun:yes'>
</span>13848576 bytes</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Control file created.</p>
<p class=MsoNoSpacing><o:p> </o:p></p>
<p class=MsoNoSpacing style='margin-left:36.0pt;text-indent:-18.0pt;mso-list:
l5 level1 lfo7'><![if !supportLists]><span style='font-family:Symbol;
mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol'><span
style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Open database</p>
<p class=CMDblua>ALTER DATABASE OPEN RESETLOGS;</p>
<p class=MsoNoSpacing style='margin-left:36.0pt;text-indent:-18.0pt;mso-list:
l5 level1 lfo7'><![if !supportLists]><span style='font-family:Symbol;
mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol'><span
style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Add TEMP</p>
<p class=CMDblua>ALTER TABLESPACE TEMP ADD TEMPFILE '/data001/<span
class=SpellE>oradata</span>/MDRP115/temp01.dbf'<span
style='mso-spacerun:yes'> </span>SIZE <span class=GramE>20971520<span
style='mso-spacerun:yes'> </span>REUSE</span> AUTOEXTEND ON NEXT 655360<span
style='mso-spacerun:yes'> </span>MAXSIZE 32767M;</p>
<p class=MsoNoSpacing style='margin-left:36.0pt;text-indent:-18.0pt;mso-list:
l5 level1 lfo7'><![if !supportLists]><span style='font-family:Symbol;
mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol'><span
style='mso-list:Ignore'>·<span style='font:7.0pt "Times New Roman"'>
</span></span></span><![endif]>Create password file</p>
<p class=MsoNoSpacing style='margin-left:36.0pt'><span class=GramE>cd</span>
$OH/<span class=SpellE>dbs</span></p>
<p class=MsoNoSpacing style='margin-left:36.0pt'><span class=SpellE><span
class=GramE>orapwd</span></span> FILE=orapwMDRP115</p>
<p class=MsoNoSpacing><o:p> </o:p></p>
<p class=MsoNoSpacing><b style='mso-bidi-font-weight:normal'>Re-creating
control file does not change DBID!<o:p></o:p></b></p>
<p class=MsoNoSpacing><o:p> </o:p></p>
<h2><a name="_Toc481417535">Changing the DBID</a></h2>
<p class=CMDblua><span class=SpellE><span class=GramE>sqlplus</span></span> /
as <span class=SpellE>sysdba</span></p>
<p class=CMDblua><span class=GramE>shutdown</span> immediate</p>
<p class=CMDblua><span class=SpellE><span class=GramE>startup</span></span>
mount</p>
<p class=CMDblua><span class=GramE>exit</span></p>
<p class=CMDblua><o:p> </o:p></p>
<p class=CMDblua><span class=SpellE><span class=GramE>nid</span></span>
TARGET=SYS</p>
<p class=MsoNoSpacing><o:p> </o:p></p>
<p class=CMDnigra>DBNEWID: Release 12.1.0.2.0 - Production on Thu Apr 27
13:57:30 2017</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Copyright (c) 1982, 2015, Oracle and/or its affiliates.<span
style='mso-spacerun:yes'> </span>All rights reserved.</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Password:</p>
<p class=CMDnigra>Connected to database MDRP115 (DBID=<b style='mso-bidi-font-weight:
normal'>3410461342</b>)</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Connected to server version 12.1.0</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Control Files in database:</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>/data001/<span
class=SpellE>oradata</span>/MDRP115/control01.ctl</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>/data001/<span
class=SpellE>oradata</span>/MDRP115/control02.ctl</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Change database ID of database MDRP115? (Y<span class=GramE>/[</span>N])
=> <span class=GramE>y</span></p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Proceeding with operation</p>
<p class=CMDnigra>Changing database ID from <b style='mso-bidi-font-weight:
normal'>3410461342 to 356948765</b></p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>Control File
/data001/<span class=SpellE>oradata</span>/MDRP115/control01.ctl - modified</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>Control File
/data001/<span class=SpellE>oradata</span>/MDRP115/control02.ctl - modified</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/system01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/sysaux01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/undotbs01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/users01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/XMETA001.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/XMETASR001.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/IAUSER001.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/DSODBSPACE01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/QSSRDSPACE01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/ESDBSPACE01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span><span class=SpellE>Datafile</span>
/data001/<span class=SpellE>oradata</span>/MDRP115/temp01.db - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>Control File
/data001/<span class=SpellE>oradata</span>/MDRP115/control01.ctl - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>Control File
/data001/<span class=SpellE>oradata</span>/MDRP115/control02.ctl - <span
class=SpellE>dbid</span> changed</p>
<p class=CMDnigra><span style='mso-spacerun:yes'> </span>Instance shut down</p>
<p class=CMDnigra><o:p> </o:p></p>
<p class=CMDnigra>Database ID for database MDRP115 changed to 356948765.</p>
<p class=CMDnigra>All previous backups and archived redo logs for this database
are unusable.</p>
<p class=CMDnigra>Database is not aware of previous backups and archived logs
in Recovery Area.</p>
<p class=CMDnigra>Database has been <span class=SpellE>shutdown</span>, open
database with RESETLOGS option.</p>
<p class=CMDnigra><span class=SpellE>Succesfully</span> changed database ID.</p>
<p class=CMDnigra>DBNEWID - Completed <span class=SpellE>succesfully</span>.</p>
<p class=MsoNoSpacing><o:p> </o:p></p>
<p class=CMDblua><span class=SpellE><span class=GramE>sqlplus</span></span> /
as <span class=SpellE>sysdba</span></p>
<p class=CMDblua><span class=SpellE><span class=GramE>startup</span></span>
mount</p>
<p class=CMDblua>ALTER DATABASE OPEN RESETLOGS;</p>
<p class=myStyle1><o:p> </o:p></p>
</div>
</body>
</html>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-70478371048870618042016-08-10T12:52:00.000+10:002016-08-10T12:53:07.673+10:00QUERY parameter in Oracle datapump import<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
Environment</h3>
Oracle 11.2.0.4<br />
<h3 style="text-align: left;">
Symptoms</h3>
Using parameters<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">TABLES= CDR.F_CLAIM_WORK_CAPACITY</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">QUERY="where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"</span><br />
<br />
output<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production</span><br />
<span style="font-family: Courier New, Courier, monospace;">UDI-00014: invalid value for parameter, 'query'</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I believe Oracle cannot identify a table of the first <span style="color: blue;">claim_ID.</span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: blue;"><br /></span></span>
<h3 style="text-align: left;">
Solutions</h3>
<br />
Syntax<br />
<pre>QUERY = [<span class="italic">schema</span>.][<span class="italic">table_name</span>:] <span class="italic">query_clause</span></pre>
<br />
Using [schema.][table_name:] for the same query<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">TABLES= CDR.F_CLAIM_WORK_CAPACITY</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">QUERY=<b>CDR.F_CLAIM_WORK_CAPACITY:</b>"where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"</span><br />
<br />
that works (the schema was remapped from CDR to Z)<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows</span><br />
<br />
The table alias used by Data Pump for the table being unloaded is <code>KU$</code>. Using KU$.<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">TABLES= CDR.F_CLAIM_WORK_CAPACITY</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">QUERY="where <b>KU$.</b>claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"</span><br />
works<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows</span><br />
<br />
It works for multiple tables too, elapsed 0 00:17:43<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">TABLES= CDR.F_CLAIM_WORK_CAPACITY,CDR.K_CLAIM_KEY_FIGURES</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. . imported "Z"."K_CLAIM_KEY_FIGURES" 4.088 GB 36215408 out of 41843789 rows</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The following two parameters do not work and return the same error:</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">UDI-00014: invalid value for parameter, 'query'</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><span style="color: blue;">QUERY="where exists (</span><b><span style="color: red;">select 1</span></b><span style="color: blue;"> from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"</span></span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><span style="color: blue;">QUERY="where exists (</span><b><span style="color: red;">select claim_ID</span></b><span style="color: blue;"> from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"</span></span><br />
I understand that "select claim_ID" is ambiguously defined, but I expected "select 1" would work.<br />
Anyway the following works fine.<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">QUERY="where exists (select <b>rc.claim_ID</b> from Z.E_ROLLING_CLAIM rc where rc.claim_id=<b>ku$</b>.claim_ID )"</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">. . imported "Z"."K_CLAIM_KEY_FIGURES" 4.088 GB 36215408 out of 41843789 rows</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">and it is a bit faster (elapsed 0 00:12:27) than "where claim_id in ()". Maybe just because of cache :-)</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;"><br /></span>
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-68663037968866773982016-07-04T14:53:00.000+10:002016-07-04T14:53:57.296+10:00Cron jobs are no longer running<div dir="ltr" style="text-align: left;" trbidi="on">
Environment: Red Hat Enterprise Linux Server release 6.6 (Santiago)<br />
<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"># crontab -e</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Authentication token is no longer valid; new one required</span><br />
<span style="font-family: Courier New, Courier, monospace;">You (root) are not allowed to access to (crontab) because of pam configuration.</span><br />
<div>
<br /></div>
<div>
there were no changes in /etc/security/access.conf</div>
<div>
<br /></div>
<div>
the problem is root`s expired password</div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># chage -l root</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Last password change : Mar 20, 2015</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Password expires : Apr 29, 2015</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Password inactive : never</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Account expires : never</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Minimum number of days between password change : 0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Maximum number of days between password change : 40</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Number of days of warning before password expires : 7</span></div>
</div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># passwd root</span></div>
</div>
<div>
<br /></div>
<div>
and check </div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># crontab -l</span></div>
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-4289265040286645012016-06-09T16:24:00.001+10:002016-06-09T16:24:33.562+10:00HTTP 500: Internal Server Error when login to a new cloned environment<div dir="ltr" style="text-align: left;" trbidi="on">
<h3>
Environment</h3>
<div>
Oracle e-Business Suite 12.1.3 (Oracle EBS)</div>
<div>
Oracle Linux 5.7</div>
<h3>
Symptoms<o:p></o:p></h3>
<div class="MsoNormal">
The following error occurs when trying to login to the new
cloned environment.<o:p></o:p></div>
<div class="StyleCMD">
<span style="color: windowtext;">500 Internal Server Error<o:p></o:p></span></div>
<div class="StyleCMD">
<br /></div>
<div class="MsoNormal" style="text-align: left;">
File <span class="StyleCMDChar"><span style="line-height: 115%;"><span style="font-family: Courier New, Courier, monospace;">application.log</span></span></span> in <span class="StyleCMDChar"><span style="line-height: 115%;"><span style="font-family: Courier New, Courier, monospace;">$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1</span></span></span>
shows<o:p></o:p></div>
<div class="StyleCMD">
<span style="color: #c00000;">Caused by: oracle.apps.jtf.base.resources.FrameworkException:
<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: #c00000;">ORA-01578: ORACLE data block
corrupted (file # 12, block # 180784)<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: #c00000;">ORA-01110: data file 12:
'/data/oradata/TEST12/APPS_TS_SUMMARY01.dbf'<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="color: #c00000;">ORA-26040: Data block was loaded
using the NOLOGGING option<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<h3>
Solution<o:p></o:p></h3>
<div class="MsoNormal">
To identify the corrupted object use:<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">select</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> s</span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">.*</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">from</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> dba_extents s<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">where</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> file_id </span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">=</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> </span><span lang="EN-US" style="background: white; color: maroon; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">12</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> </span><i><span lang="EN-US" style="background: white; color: green; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">-- change</span></i><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">and</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> </span><span lang="EN-US" style="background: white; color: maroon; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">180784</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"> </span><i><span lang="EN-US" style="background: white; color: green; font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-highlight: white;">-- change</span></i><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">between</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> block_id </span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">and</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> </span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">(</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">block_id
</span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">+</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> </span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">blocks</span><span lang="EN-US" style="background: white; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> </span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">-</span><span lang="EN-US" style="background: white; color: maroon; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">1</span><span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;">);</span><o:p></o:p></div>
<div class="MsoNormal">
<span lang="EN-US" style="background: white; color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-highlight: white;"><br /></span></div>
<div class="MsoNormal">
It happened to be APPLSYS.WF_LOCAL_USER_ROLES<o:p></o:p></div>
<div class="MsoNormal">
The solution is described in Oracle Support Doc ID <b>781413.1</b><o:p></o:p></div>
<div class="StyleCMD">
<br /></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">sqlplus apps/password<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">set serveroutput on size 100000;</span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;"><o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">declare<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">begin<o:p></o:p></span></div>
<div class="StyleCMD" style="margin-left: 36.0pt;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">WF_LOCAL_SYNCH.BULKSYNCHRONIZATION(<o:p></o:p></span></div>
<div class="StyleCMD" style="margin-left: 36.0pt;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">P_ORIG_SYSTEM => 'ALL',<o:p></o:p></span></div>
<div class="StyleCMD" style="margin-left: 36.0pt;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">P_PARALLEL_PROCESSES => null,<o:p></o:p></span></div>
<div class="StyleCMD" style="margin-left: 36.0pt;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">P_LOGGING => null,<o:p></o:p></span></div>
<div class="StyleCMD" style="margin-left: 36.0pt;">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">P_RAISEERRORS => TRUE);<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">exception<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">when others then<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">dbms_output.put_line('sqlerrm = ' || sqlerrm);<o:p></o:p></span></div>
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">end;<o:p></o:p></span></div>
<br />
<div class="StyleCMD">
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">/</span><o:p></o:p></div>
<h3>
Useful info</h3>
<div style="text-align: left;">
<span class="p_AFHoverTarget xq" id="kmPgTpl:r1:0:ol22"><span style="font-family: inherit;">Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING (Doc ID 433280.1)</span></span></div>
<div style="text-align: left;">
<span class="p_AFHoverTarget xq">Http 500 : Internal Server Error When Login To A New Cloned Environment (Doc ID 781413.1)</span></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com1tag:blogger.com,1999:blog-7203079970609015719.post-65588487846644596612016-04-29T14:22:00.000+10:002016-04-29T15:16:49.492+10:00How to find whether an oracle database patch was applied<div dir="ltr" style="text-align: left;" trbidi="on">
Using SQL:<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">select * from sys.registry$history;</span><br />
It is useful for patch set levels, but not for a particular bug/patch.<br />
<br />
OPatch, in Linux<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">$ORACLE_HOME/OPatch/opatch lsinventory</span><br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">$ORACLE_HOME/OPatch/opatch lsinventory | grep something</span><br />
some useful flags<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">-bugs_fixed</span> Reports bugs fixed by installed patches with bug descriptions and extra info<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">-patch</span> ..order based on installed time<br />
<span style="color: blue; font-family: "courier new" , "courier" , monospace;">-patch_id</span> ..order based on patch numbers<br />
<br />
In OEM 12c<br />
<i>Targets > All Targrets > </i><br />
<i>Targer Type > Others > Oracle Home > click your Target</i><br />
on Oracle Home page, see tab Patches Applied. In the table below, you can find Bugs Fixed, Files and Components.<br />
<br />
For multiple targets, you can create an OEM job to run a SQL script on multiple databases or opatch command on multiple hosts.<br />
<br />
In OEM 12c<br />
<i>Enterprise > Configuration > Inventory and Usage Details</i><br />
Show "<i>Database Installations</i>"<br />
In the table below, column "<i>Patches Applied</i>", click "Yes" (or No, but in this case there is nothing to see)<br />
<br />
<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-10429020053860430992016-02-24T10:20:00.000+11:002016-02-24T10:21:11.283+11:00Compiling public synonyms<div dir="ltr" style="text-align: left;" trbidi="on">
Environment: Oracle database 11.2, Toad for Oracle 12.6.0.53<br />
<br />
There is an invalid public synonym F_GET_PARTY_NAME. The target object exists and valid.<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">select s.* , o.*</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">--'alter public synonym ' || synonym_name || ' compile;'</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">from dba_synonyms s, dba_objects o</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">where s.owner=o.owner and s.synonym_name=O.OBJECT_NAME</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">and o.status ='INVALID' </span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">and s.owner='PUBLIC'</span><br />
;<br />
When connected as SYSTEM or SYS in “TOAD Script runner” or in “TOAD for Oracle 12.6.0.53” (looks like a TOAD’s bug)<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">alter public synonym F_GET_PARTY_NAME compile;</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">alter public synonym</span><br />
<span style="font-family: Courier New, Courier, monospace;">Error at line 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-00995: missing or invalid synonym identifier</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">F_GET_PARTY_NAME compile;</span><br />
<span style="font-family: Courier New, Courier, monospace;">Error at line 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-00900: invalid SQL statement</span><br />
<br />
When connected as SYSTEM in sqlplus<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">alter public synonym F_GET_PARTY_NAME compile;</span><br />
<span style="font-family: Courier New, Courier, monospace;">alter public synonym F_GET_PARTY_NAME compile</span><br />
<span style="font-family: Courier New, Courier, monospace;">*</span><br />
<span style="font-family: Courier New, Courier, monospace;">ERROR at line 1:</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-01031: insufficient privileges</span><br />
<br />
When connected as SYS in sqlplus<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">SQL> alter public synonym F_GET_PARTY_NAME compile;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Synonym altered.</span><br />
<div>
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-36507967132995933412016-01-07T10:45:00.000+11:002016-01-07T11:28:01.944+11:00Validating CSV file with regular expressions / Проверка CSV файла регулярным выражением <div dir="ltr" style="text-align: left;" trbidi="on">
The CSV file does not contain double quote.<br />
14 commas (field delimiter) are expected in each row.<br />
A row delimiter is CRLF (\r\n).<br />
<br />
Searching for a row with 15 commas in Notepad++ :<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">^([^,\r]*,){15}</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">[^,\r]*</span> matches any char except comma and CR<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-5313245000660041522015-01-30T14:57:00.000+11:002015-01-30T14:57:37.407+11:00ORACLE data block corrupted after restoring database<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Environment</b>: Oracle database 11.2, Oracle Linux 5.7.<br />
<br />
Test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.<br />
When object is used, the following errors happen<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'</span><br />
<div>
<br /></div>
<div>
Find affected segment<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">select segment_name, segment_type, owner</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">from dba_extents</span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="color: blue;">where file_id = </span><b><span style="color: blue;">42 </span><span style="background-color: white;"><span style="color: lime;">-- change</span></span></b></span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="color: blue;">and </span><b><span style="color: blue;">1029253 </span><span style="color: lime;">-- change</span></b></span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">between block_id and (block_id + blocks -1);</span><br />
<br />
as in this case it is an index, we can (make unusable and rebuild) or (drop and recreate).<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">ALTER INDEX owner.name UNUSABLE;</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">ALTER INDEX owner.name REBUILD;</span><br />
<br /></div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-34556282036094253682014-11-28T12:52:00.000+11:002014-11-28T12:52:16.983+11:00Modifying collection schedule for OEM metric Tablespace Allocation.<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Environment:</b><br />
Oracle DB 11.2, Standard Edition One with DB console.<br />
Windows 2003 R2, 32bit<br />
<br />
<b>Action:</b><br />
Default metric (Tablespace Allocation) collection interval is "Every 7 Days", which can be found on page "All Metrics" in DB console.<br />
<br />
Go to %ORACLE_HOME%\sysman\admin\default_collection<br />
and edit file <span style="color: blue;">database.xmlp</span><br />
<br />
Find "Category: Tablespace Allocation" and few rows down:<br />
<span style="color: blue;"><span style="font-family: "Courier New",Courier,monospace;"> IntervalSchedule INTERVAL="7" TIME_UNIT="Day"</span></span><br />
<br />
Change the interval number. Time unit can also be Hr or Min. Save the file.<br />
Restart the oracle agent.<br />
In this particular case by restarting windows service OracleDBConsoleSID, which bounces the agent as well.<br />
<br />
"All Metrics" in DB console will still show 7 days interval. Check the real metric collection by the following SQL.<br />
<span style="color: blue;"><span style="font-family: "Courier New",Courier,monospace;">select * from SYSMAN.MGMT$METRIC_DETAILS<br />where METRIC_LABEL in ('Tablespace Allocation') and key_value='SYSTEM'<br />order by collection_timestamp desc;</span></span><br />
<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-74326565985607301402014-09-05T12:23:00.000+10:002014-09-05T13:03:56.272+10:00Configuring Oracle Database 11g Gateway for ODBC MS SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Environment</h4>
Oracle database 11.2<br />
Oracle Linux Server release 5.7<br />
Microsoft® ODBC Driver 11 for SQL Server - RedHat Linux<br />
Microsoft SQL Server 2005,2008R2<br />
<h4 style="text-align: left;">
Installing ODBC driver</h4>
Install the driver according to MS <a href="http://www.microsoft.com/en-us/download/details.aspx?id=36437">http://www.microsoft.com/en-us/download/details.aspx?id=36437</a><br />
<br />
To verify that the ODBC Driver on Linux was registered successfully, execute the following command:<br />
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"<br />
<br />
edit ~/.odbc.ini<br />
and add<br />
<br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">[infraUAT]</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Driver=ODBC Driver 11 for SQL Server</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Description=My Sample ODBC Database Connection</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Trace=Yes</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Server=gtpsql2</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Port=1433</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace; font-size: x-small;">Database=InfraEnt_ITSD_UAT</span><br />
<br />
"<span style="color: blue; font-family: 'Courier New', Courier, monospace; font-size: x-small;">Driver</span>" has to be same as output of <span style="color: blue; font-family: Courier New, Courier, monospace;">odbcinst -q -d</span>, which comes from <span style="color: blue; font-family: Courier New, Courier, monospace;">/etc/odbcinst.ini</span><br />
<br />
Test it<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">odbcinst -q -s</span><br />
<span style="font-family: Courier New, Courier, monospace;">[infraUAT]</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="color: blue; font-family: Courier New, Courier, monospace;">isql -v infraUAT user password</span><br />
<span style="font-family: Courier New, Courier, monospace;">+---------------------------------------+</span><br />
<span style="font-family: Courier New, Courier, monospace;">| Connected! |</span><br />
<span style="font-family: Courier New, Courier, monospace;">| |</span><br />
<span style="font-family: Courier New, Courier, monospace;">| sql-statement |</span><br />
<span style="font-family: Courier New, Courier, monospace;">| help [tablename] |</span><br />
<span style="font-family: Courier New, Courier, monospace;">| quit |</span><br />
<span style="font-family: Courier New, Courier, monospace;">| |</span><br />
<span style="font-family: Courier New, Courier, monospace;">+---------------------------------------+</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL> quit</span><br />
<h4 style="text-align: left;">
Configuring Gateway</h4>
<div>
cd $ORACLE_HOME/hs/admin</div>
<div>
If you use initdg4odbc.ora, then dg4odbc will be SID.</div>
<div>
edit <b>initdg4odbc.ora</b></div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">S_FDS_CONNECT_INFO = infraUAT</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># you need to match HS_LANGUAGE to SQLS</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># you might need the following HS_</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">#HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">#HS_NLS_LENGTH_SEMANTICS=CHAR</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">#nvarchars of a SQL Server are UCS2 character set</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">#HS_NLS_NCHAR=UCS2</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"># ODBC specific environment variables</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">set ODBCINI=/home/oracle/.odbc.ini</span></div>
</div>
<div>
<br /></div>
<div>
<span style="font-family: inherit;">HS_LANGUAGE needs to match code page of SQL Server.</span></div>
<div>
<span style="font-family: inherit;">When Oracle use unicode AL32UTF8, it will fail to connect. See </span>Doc ID 756186.1</div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ERROR at line 1:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ORA-28500: connection from ORACLE to a non-Oracle system returned this message:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">[</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: inherit;">edit <b>listener.ora</b> and add new <b>SID_DESC</b>, where SID matches </span>init<b><span style="color: blue;">dg4odbc</span></b>.ora</div>
<div>
<div style="font-family: 'Courier New', Courier, monospace;">
SID_LIST_LISTENER =</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(SID_LIST =</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(SID_DESC =</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(SID_NAME = dg4odbc)</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(ORACLE_HOME = /opt/oracle/product/se1/11.2.0.3)</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(PROGRAM = dg4odbc)</div>
<div style="font-family: 'Courier New', Courier, monospace;">
(ENVS='LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql/lib64:/opt/oracle/product/se1/11.2.0.3/lib')</div>
<div style="font-family: 'Courier New', Courier, monospace;">
)</div>
<div style="font-family: 'Courier New', Courier, monospace;">
)</div>
<div>
<span style="font-family: inherit;">Restart listener</span></div>
<div>
<span style="font-family: inherit;">configure </span> <b>tnsnames.ora</b></div>
</div>
</div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">HS1 =</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (DESCRIPTION =</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (ADDRESS_LIST =</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (ADDRESS = (PROTOCOL = tcp)(HOST = <hostname gateway="" istener="" of="">)(PORT = 1521))</hostname></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> )</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (CONNECT_DATA =</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (SID = dg4odbc)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> )</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> (HS=OK)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> )</span></div>
</div>
<div>
<br /></div>
<div>
Configure <b>DB Link</b></div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">CREATE PUBLIC DATABASE LINK INFRA</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"> CONNECT TO DSUSER</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"> IDENTIFIED BY password<pwd></pwd></span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"> USING 'hs1';</span></div>
</div>
<h4 style="text-align: left;">
Problem 1 - code page conversion</h4>
<div>
Some symbols are not converted correctly, e.g. Microsoft double quote or single quote. I have not tested non-latin alphabets.</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">SELECT short_problem FROM CL_CALL_LOGGING@infra where call_number=4234;</span></div>
<div>
output</div>
<div>
<span style="font-family: Courier New, Courier, monospace;">The printer on Level 5 is showing <span style="background-color: #ea9999;">‘</span>perform printer maintenanceâ<span style="background-color: #ea9999;">€™.</span> </span></div>
<div>
<b>Solution 1</b></div>
<div>
as Oracle NLS_CHARACTERSET = AL32UTF8 (check view NLS_DATABASE_PARAMETERS), create a view in <b>SQLS</b> to CAST this column to unicode, then you can select from this view from Oracle.</div>
<div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">CREATE VIEW TEST_VIEW1 AS</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">SELECT CALL_NUMBER, CAST(SHORT_PROBLEM AS NVARCHAR(200)) AS SHORT_PROBLEM</span></div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;">FROM DBO.CL_CALL_LOGGING;</span></div>
</div>
<div>
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: inherit;">in Oracle</span></div>
<div>
<div style="color: blue; font-family: 'Courier New', Courier, monospace;">
SELECT CALL_NUMBER, SHORT_PROBLEM FROM TEST_VIEW1@INFRA WHERE CALL_NUMBER=4234;</div>
<div style="color: blue; font-family: 'Courier New', Courier, monospace;">
output</div>
<div style="font-family: 'Courier New', Courier, monospace;">
The printer on Level 5 is showing<span style="background-color: #ea9999;"> ‘</span>perform printer maintenance<span style="background-color: #ea9999;">’</span>.</div>
<h4 style="text-align: left;">
<span style="font-family: inherit;">Problem 2 - multibyte characters</span></h4>
</div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ORA-28500: connection from ORACLE to a non-Oracle system returned this message:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ORA-02063: preceding 2 lines from INFRA</span></div>
</div>
<div>
<br /></div>
<div>
If in SQLS a column is described as VARCHAR(200) and a value length is 200 and it contains some symbols which become multi-byte, then in Oracle it becomes longer than 200 bytes.</div>
<div>
<b>Solution 2</b></div>
<div>
The same as Solution 1, CAST it to unicode on SQLS.</div>
<div>
If you create similar or the same table in Oracle, define length in chars, e.g. <span style="color: blue; font-family: 'Courier New', Courier, monospace;">VARCHAR(200 char)</span></div>
<div>
<span style="font-family: inherit;">It does not have to be NVARCHAR as in my case oracle DB is already UTF8.</span></div>
<h4 style="text-align: left;">
<span style="font-family: inherit;">Problem 3 - varchar(max)</span></h4>
<div>
<span style="font-family: inherit;">When selecting varchar(max) from SQLS:</span></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ORA-28500: connection from ORACLE to a non-Oracle system returned this message:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">ORA-02063: preceding 2 lines from INFRA</span></div>
</div>
<div>
<span style="font-family: inherit;"><b>Solution 3.1</b></span></div>
<div>
<span style="font-family: inherit;">I haven't found a good solution for this. You can CAST(COLNAME AS TEXT) or as NTEXT in SQLS, in thin case you receive LONG type in Oracle (can be saved and converted to CLOB). Which is ok for most values (Latin characters), but it still has wrong conversion for multi-byte/non-latin characters.</span></div>
<div>
<span style="font-family: inherit;"><b>Solution 3.2 - other ODBC drivers</b></span></div>
<div>
<span style="font-family: inherit;">try other drivers, e.g. </span>Easysoft or Datadirect. I haven't tested it.</div>
<div>
<b>Solution 3.3 - non Oracle ETL</b></div>
<div>
IBM Datastage works ok, when selecting as unicode CAST(col as NVARCHAR(max)) and keeping as LongNVarchar in DataStage to avoid Datastage conversion. It inserts into Oracle CLOB and looks ok.</div>
<div>
Microsoft SSIS converts correctly, if destination table in Oracle is defined as NCLOB.</div>
<h4 style="text-align: left;">
Documentation</h4>
<div>
Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)</div>
<div>
How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)</div>
</div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-32384391351602368432014-08-12T12:17:00.000+10:002016-01-07T10:27:47.382+11:00Regular expressions for CSV files<div dir="ltr" style="text-align: left;" trbidi="on">
SQL Server Import had troubles to import csv file, when some fields contained CRLF(\r\n).<br />
Row delimiter needs to be changed.<br />
Notepad++ was used.<br />
<br />
It matches 15 columns (14 comma delimiters). CRLF is end of line. Some text columns contain CRLF(\r\n).<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">((?:(?:"(?:(?:""|[^"])+)"|(?:[^,]*)),){14}.*?)\r\n</span><br />
to add a pipe as row delimiter replace with <span style="color: blue; font-family: Courier New, Courier, monospace;">\1|\r\n</span><br />
<br />
it matches $$ delimiter 7 fields (6 delimiters) and end of line \r MAKE sure . dot matches end of LINE!<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">(?:.*?\$\$){6}.*?\r</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">((?:.*?\$\$){13}.*?)\r\n </span> to add a pipe as row delimiter replace with <span style="color: blue; font-family: Courier New, Courier, monospace;">\1|\r\n </span><br />
<br />
to search for $$ with grep (Linux or Cygwin), use single quotes and escape last dollar<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">grep -e '$\$' filename.csv</span><br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0tag:blogger.com,1999:blog-7203079970609015719.post-92129112543209660472014-08-12T11:52:00.000+10:002014-08-12T11:53:11.448+10:00CPU and system info on Windows<div dir="ltr" style="text-align: left;" trbidi="on">
Just few examples of wmic utility to get CPU and system info<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">wmic cpu get /format:list</span><br />
or<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">wmic cpu list full /format:list</span><br />
<br />
to get some particular field<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">wmic cpu get deviceid,SocketDesignation</span><br />
<br />
system info<br />
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">wmic COMPUTERSYSTEM list full /format:list</span><br />
<span style="background-color: white; color: blue; font-family: Courier New, Courier, monospace;">wmic COMPUTERSYSTEM get name,model,NumberOfProcessors /format:list</span><br />
<br />
help<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">wmic /?</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">wmic cpu /?</span><br />
etc.<br />
<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">systeminfo</span><br />
use /S to connect to remote host<br />
<br /></div>
Alexander Zyhttp://www.blogger.com/profile/09751135249526959652noreply@blogger.com0