tag:blogger.com,1999:blog-60767323198491868012024-02-17T00:32:29.265-08:00Ulfet`s Oracle BlogUnknownnoreply@blogger.comBlogger86125tag:blogger.com,1999:blog-6076732319849186801.post-37023414461754089202021-02-23T20:29:00.005-08:002021-02-23T20:29:44.598-08:00Cannot access dba_directories inside procedure<p> Recently I faced one of familiar Oracle error</p><p><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;">ORA</span><span class="hljs-number" style="border: 0px; box-sizing: inherit; color: var(--highlight-namespace); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">-00942</span><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;">: </span><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">table</span><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;"> </span><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">or</span><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;"> </span><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">view</span><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;"> does </span><span class="hljs-keyword" style="border: 0px; box-sizing: inherit; color: var(--highlight-keyword); font-family: inherit; font-size: 13px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">not</span><span style="color: var(--highlight-color); font-family: inherit; font-size: 13px; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit; white-space: inherit;"> exist</span> </p><p><br /></p><p>I got it in while compiling procedure, because eventually I found it became invalid state.</p><p><br /></p><p>I tried to create new directory and give read/write permission on it and change inside procedure, but it still was INVALID. But instead</p><p><br /></p><p>inside procedure I put hard code path of that directory it was success.</p><p><br /></p><p>Like:<br /><br />create or replace procedure `procedure_name`<br />(<br />`variable` IN varchar2<br />)<br />AS<br />`variable` varchar2(2000) := '/../../dir';</p><p>begin</p><p><span> select ... into `variable` from dba_directories whhere directnory_name = '...';<br /></span> ....<br /> ....</p><p>end;<br />/ <br /><br /><br />But it is not make sense. Then after google I found to overcome that issue, all I need to grant ALL on dba_directories to user not only READ, WRITE.</p><p><br /></p><p>As SYS<br /><br />grant all on dba_diretories to `user`'</p><p>then I was able to compile successfully original procedure, no longer need to manipulate procedure.</p><p>SQL> set linesize 300<br />SQL> col object_name for a50<br />SQL> col owner for a30<br />SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID'; 2</p><p>OWNER<span style="white-space: pre;"> </span> OBJECT_NAME<span style="white-space: pre;"> </span>OBJECT_TYPE<br />---------------------- -------------------------------- -----------------<br />`USER`<span style="white-space: pre;"> </span> `PROCEDURE_NAME`<span style="white-space: pre;"> </span> <span style="white-space: pre;"> </span> PROCEDURE<br /><br /></p><p>SQL> alter procedure `user`.`precedure_name` compile;<br />Procedure altered.</p><p>SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID'; <br />no rows selected</p><p><br /></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-65341812761808438232020-04-24T09:05:00.001-07:002020-04-24T09:05:56.720-07:00How to fix : ORA-03113: end-of-file on communication channel<div dir="ltr" style="text-align: left;" trbidi="on">
Today when I tried to open my db (that db was created and lunched inside container) I saw ORA-03113: end-of-file on communication channel.<br />
<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="overflow-wrap: normal;"> SQL> startup
ORACLE instance started.
Total System Global Area 3.4206E+10 bytes
Fixed Size 2270360 bytes
Variable Size 3422554984 bytes
Database Buffers 3.0736E+10 bytes
Redo Buffers 45649920 bytes
Database mounted.
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11344
Session ID: 380 Serial number: 3 </code></pre>
<br />
<br />
<br />
There is a lot of reason can be, after check it out from the forum all I need is clear unarchivelog.<br />
<br />
First start instance mount mode.<br />
<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="overflow-wrap: normal;"> SQL> startup mount
ORACLE instance started.
Total System Global Area 3.4206E+10 bytes
Fixed Size 2270360 bytes
Variable Size 3422554984 bytes
Database Buffers 3.0736E+10 bytes
Redo Buffers 45649920 bytes
Database mounted.Database mounted. </code></pre>
<br />
<br />
<br />
-check how many group we have:<br />
<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="overflow-wrap: normal;">SQL> set linesize 200
SQL> select group#, members, status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 INACTIVE
3 2 INACTIVE
4 2 INACTIVE
5 2 INACTIVE
6 2 CURRENT
14 2 INACTIVE
8 2 INACTIVE
9 2 INACTIVE
10 2 INACTIVE
11 2 INACTIVE
GROUP# MEMBERS STATUS
---------- ---------- ----------------
12 2 INACTIVE
13 2 INACTIVE
7 2 INACTIVE
14 rows selected.
SQL> </code></pre>
<br />
<br />
Writing this simple PLSQL anonyms block to clear unarchived log files.<br />
<br />
<br />
<br class="Apple-interchange-newline" />
<span style="background-color: #f0f0f0; font-family: monospace; font-size: 12px; white-space: pre;"> SQL> BEGIN
FOR i IN 1 .. 14
LOOP
execute immediate 'alter database clear unarchived logfile group '||i;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> </span><br />
<br />
<br />
--look alertlog<br />
alter database clear unarchived logfile group 13<br />
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN<br />
BEFORE 04/15/2020 12:35:11 (CHANGE 228093) CANNOT BE USED FOR RECOVERY.<br />
Clearing online log 13 of thread 1 sequence number 195<br />
Completed: alter database clear unarchived logfile group 13<br />
alter database clear unarchived logfile group 14<br />
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN<br />
BEFORE 04/15/2020 12:40:12 (CHANGE 228198) CANNOT BE USED FOR RECOVERY.<br />
Clearing online log 14 of thread 1 sequence number 196<br />
Wed Apr 15 15:22:49 2020<br />
Completed: alter database clear unarchived logfile group 14<br />
<br />
<br />
<br />
<br />
you can restart db or just try to switch from mount mode to open<br />
<br />
alter database open;<br />
<br />
or<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="overflow-wrap: normal;"> SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 3.4206E+10 bytes
Fixed Size 2270360 bytes
Variable Size 3422554984 bytes
Database Buffers 3.0736E+10 bytes
Redo Buffers 45649920 bytes
Database mounted.
Database opened.
SQL> </code></pre>
<br />
<br />
<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-41748864177521038642016-12-26T20:57:00.001-08:002017-03-17T14:50:05.940-07:00Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<span lang="RU">While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open.</span></div>
<div class="MsoNormal">
<span lang="RU"><br /></span></div>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select name from v$database;
NAME
---------
TEST</code></pre>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> shut abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6597406720 bytes
Fixed Size 2265664 bytes
Variable Size 3204451776 bytes
Database Buffers 3372220416 bytes
Redo Buffers 18468864 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6552
Session ID: 191 Serial number: 3 </code></pre>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<o:p> What`s wrong? </o:p></div>
<div class="MsoNormal">
<o:p><br /></o:p></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> alter database open resetlogs;
ERROR: </code></pre>
<div class="MsoNormal">
<span style="background-color: #f0f0f0; font-family: "arial"; font-size: 12px;"> ORA-03114: not connected to ORACLE </span> </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
SQL> exit<o:p></o:p></div>
<div class="MsoNormal">
Disconnected from Oracle Database 11g Enterprise Edition
Release 11.2.0.4.0 - 64bit Production<o:p></o:p></div>
<div class="MsoNormal">
With the Partitioning, OLAP, Data Mining and Real
Application Testing options<o:p></o:p></div>
<div class="MsoNormal">
<br />
And instance got down<br />
<br /></div>
<div class="MsoNormal">
$ ps -ef | grep smon<o:p></o:p></div>
<div class="MsoNormal">
oracle 6586 6246 0 17:50
pts/2 00:00:00 grep smon<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> $ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 17:50:35 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6597406720 bytes
Fixed Size 2265664 bytes
Variable Size 3204451776 bytes
Database Buffers 3372220416 bytes
Redo Buffers 18468864 bytes
Database mounted. </code></pre>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Tried to reset logs</div>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery </code></pre>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<o:p>Unsuccessful...</o:p><br />
<o:p>Let`s recover database </o:p></div>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> recover database until cancel;
Media recovery complete. </code></pre>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now, try to open with resetlogs</div>
<div class="MsoNormal">
<br /></div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> alter database open resetlogs;
Database altered. </code></pre>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
Done, now it works</div>
</div>
Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-6076732319849186801.post-59215861528567101102016-11-01T23:27:00.004-07:002016-11-01T23:34:21.197-07:00ORA-29339: tablespace block size 16384 does not match configured block sizes<div dir="ltr" style="text-align: left;" trbidi="on">
While I create non standart db_block_size tablespace I faced ORA-29339: tablespace block size 16384 does not match configured block sizes.<br />
<br />
16k = 16*1024 = 16384<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> CREATE TABLESPACE DATA DATAFILE
'/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G
LOGGING
DEFAULT
NO INMEMORY
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON; </code></pre>
<br />
ORA-29339<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select name, block_size, current_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE
-------------------- ---------- ------------
DEFAULT 8192 132096 </code></pre>
<br />
Check db_*k_cache_size parameters<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> </code></pre>
<br />
Also db_block_size<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> </code></pre>
<br />
<br />
I need to set db_16k_cache_size.<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> alter system set db_16k_cache_size = 128M scope=both; </code></pre>
<br />
<br />
Then try again<br />
<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> CREATE TABLESPACE DATA DATAFILE
'/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G
LOGGING
DEFAULT
NO INMEMORY
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Tablespace created. </code></pre>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6076732319849186801.post-50850100700758365112016-03-29T00:14:00.002-07:002016-05-09T15:57:12.608-07:00Fix: ORA-13639: The current operation was interrupted because it timed out.<div dir="ltr" style="text-align: left;" trbidi="on">
Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value.<br />
<br />
Example result of sql<br />
<br />
select<br />
execution_name, advisor_name,<br />
to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,<br />
to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message<br />
from dba_advisor_executions<br />
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'<br />
order by execution_start;<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTwrEuoPv1mERB-2ayT3weL4eOpZVeIw_3VOh-aUMisZlyrK5IKG6_u0rbxJhgID54yNKVO_bBEhRW9Mjo-_ab8rHyzlb2K3P9OkUnmK0Nfq2SKlYW2KxMpiC83rzypV8lBYqYFYFVTTeM/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="17" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTwrEuoPv1mERB-2ayT3weL4eOpZVeIw_3VOh-aUMisZlyrK5IKG6_u0rbxJhgID54yNKVO_bBEhRW9Mjo-_ab8rHyzlb2K3P9OkUnmK0Nfq2SKlYW2KxMpiC83rzypV8lBYqYFYFVTTeM/s400/1.jpg" width="400" /></a></div>
<br />
<br />
Check value of TIME_LIMIT`s parameter :<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> column parameter_value for A35
SQL> select
parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'
and parameter_name in ('TIME_LIMIT',
'DEFAULT_EXECUTION_TYPE',
'LOCAL_TIME_LIMIT'); </code></pre>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
LOCAL_TIME_LIMIT 1200
TIME_LIMIT 3600
DEFAULT_EXECUTION_TYPE TUNE SQL </code></pre>
<div>
<br /></div>
<div>
In our case it is default value. Let`s increase it</div>
<div>
<br /></div>
<div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> begin
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200);
end;
/ 2 3 4
PL/SQL procedure successfully completed. </code></pre>
</div>
<div>
<br /></div>
<div>
Check again:</div>
<div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> PARAMETER_NAME, PARAMETER_VALUE
LOCAL_TIME_LIMIT, 1200
TIME_LIMIT, 7200
DEFAULT_EXECUTION_TYPE, TUNE SQL </code></pre>
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-36128851345618676362015-12-18T03:29:00.003-08:002015-12-18T03:33:00.599-08:00Fix RMAN-03002<div dir="ltr" style="text-align: left;" trbidi="on">
If you face ORA-19804 error just increase db_recovery_file_dest_size value<br />
<br />
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01<br />
RMAN-00571: ===========================================================<br />
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============<br />
RMAN-00571: ===========================================================<br />
RMAN-03002: failure of backup plus archivelog command at 12/18/2015 15:10:43<br />
<br />
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/18/2015 15:10:40<br />
ORA-19809: limit exceeded for recovery files<br />
ORA-19804: cannot reclaim 52428800 bytes disk space from 214748364800 limit<br />
<div>
<br /></div>
<div>
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> set linesize 120
SQL> col name for a40
\SQL> col type for a40
SQL> show parameter db_re
NAME TYPE VALUE
------------------------- --------- ----------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 200G
db_recycle_cache_size big integer 0
</code></pre>
</div>
<div>
<br />
<br />
<pre style="background: url("https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif") rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> alter system set db_recovery_file_dest_size=600G scope=both;
System altered. </code></pre>
That`s all.</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-10559501015868317882015-11-27T03:04:00.003-08:002015-11-27T03:25:17.803-08:00Fix ORA-16072<div dir="ltr" style="text-align: left;" trbidi="on">
Today after restore and recovery prod database on test environment I faced with strange error:<br />
<br />
ORA-16072: a minimum of one standby database destination is required.<br />
<br />
My database environment : <br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> OS: OEL 6.5
DB: 11.2.0.4 </code></pre>
<br />
After recovery database stays on MOUNT mode and could not open it.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/27/2015 12:55:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9573 and starting SCN of 556583605 </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/27/2015 12:56:08
ORA-03113: end-of-file on communication channel
Process ID: 12064
Session ID: 958 Serial number: 11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/27/2015 12:56:08
ORA-03113: end-of-file on communication channel
Process ID: 12064
Session ID: 958 Serial number: 11
[oracle@TESTDB dbs]$ </code></pre>
<div>
<br /></div>
<div>
<br /></div>
<div>
Checking alertlog:</div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">LGWR: STARTING ARCH PROCESSES
Fri Nov 27 14:44:35 2015
ARC0 started with pid=22, OS id=23775
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
Errors in file /u01/oracle/diag/rdbms/prod/XXXX/trace/XXXX_lgwr_23742.trc:
<b> ORA-16072: a minimum of one standby database destination is required </b></code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
I turned off archivelog mode on my test database and tried to open. It opened successfully. But I need my test database have to be on archivelog mode.</div>
<div>
<br /></div>
<div>
To handle it I checked and corrected some parameters:</div>
<div>
<br /></div>
<div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select open_mode, log_mode from v$database;
OPEN_MODE LOG_MODE
-------------------- ------------
READ WRITE NOARCHIVELOG </code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> set linesize 100
SQL> show parameter fal_
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
fal_client string XXXX
fal_server string XXXX_SB
SQL> alter system set fal_client='' scope=spfile;
System altered.
SQL> alter system set fal_server='' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered
SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;
OPEN_MODE DATABASE_ROLE GUARD_S SWITCHOVER_STATUS
-------------------- ---------------- ------- --------------------
MOUNTED PRIMARY NONE NOT ALLOWED
Database altered. </code></pre>
</div>
<div>
<br /></div>
<div>
<br />
Restart database<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 8584982528 bytes
Fixed Size 2260128 bytes
Variable Size 6341788512 bytes
Database Buffers 2214592512 bytes
Redo Buffers 26341376 bytes
Database mounted.
SQL> alter database open;
Database altered. </code></pre>
</div>
<div>
<br /></div>
<div>
Perfect<br />
<br /></div>
<div>
<div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- ------------------------
XXXX READ WRITE
SQL> </code></pre>
</div>
</div>
</div>
</div>
<br />
<br />
<br />
But in alertlog still error<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> Errors in file /u01/oracle/diag/rdbms/prod/XXXX/trace/XXXX_arct_24125.trc:
ORA-00313: open failed for members of log group 7 of thread 0
ORA-00312: online log 7 thread 0: '/u01/oracle/fast_recovery_area/XXXX/onlinelog/o1_mf_7_c4vcl2pw_.log' </code></pre>
<div>
<br /></div>
<div>
<br /></div>
<div>
There standby redo logs. Let`s drop it</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select group#, bytes from v$standby_log;
GROUP# BYTES
---------- ----------
4 314572800
5 314572800
6 314572800
7 314572800
SQL> set linesize 150
SQL> col member for a80
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 /u01/oracle/oradata/XXX/redo01.log
2 /u01/oracle/oradata/XXX/redo02.log
3 /u01/oracle/oradata/XXX/redo03.log
4 /u01/oracle/fast_recovery_area/XXX/onlinelog/o1_mf_4_c5jf59f8_.log
5 /u01/oracle/fast_recovery_area/XXX/onlinelog/o1_mf_5_c5jf5df9_.log
6 /u01/oracle/fast_recovery_area/XXX/onlinelog/o1_mf_6_c5jf5hfv_.log
7 /u01/oracle/fast_recovery_area/XXX/onlinelog/o1_mf_7_c5jf5lw1_.log
7 rows selected. </code></pre>
</div>
<div>
<br /></div>
<div>
<div>
<br />[oracle@TESTDB onlinelog]$ pwd</div>
<div>
/u01/oracle/fast_recovery_area/XXXX/onlinelog</div>
</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">[oracle@TESTDB onlinelog]$ ls -lrt
total 1228820
-rw-r-----. 1 oracle oinstall 314573312 Nov 27 14:47 o1_mf_4_c5jf59f8_.log
-rw-r-----. 1 oracle oinstall 314573312 Nov 27 14:47 o1_mf_5_c5jf5df9_.log
-rw-r-----. 1 oracle oinstall 314573312 Nov 27 14:47 o1_mf_6_c5jf5hfv_.log
-rw-r-----. 1 oracle oinstall 314573312 Nov 27 14:47 o1_mf_7_c5jf5lw1_.log</code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select status, group#, bytes from v$standby_log;
STATUS GROUP# BYTES
---------- ---------- ----------
UNASSIGNED 4 314572800
UNASSIGNED 5 314572800
UNASSIGNED 6 314572800
UNASSIGNED 7 314572800
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered. </code></pre>
</div>
<div>
<br /></div>
<div>
After restart db check alertlog<br />
<br />
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 8584982528 bytes
Fixed Size 2260128 bytes
Variable Size 6341788512 bytes
Database Buffers 2214592512 bytes
Redo Buffers 26341376 bytes
Database mounted.
Database opened.
SQL> </code></pre>
</div>
</div>
<div>
<br /></div>
<div>
Alertlog is clear<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">Fri Nov 27 15:15:42 2015
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Fri Nov 27 15:15:42 2015 </code></pre>
</div>
<div>
<br /></div>
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-59642740014688696592015-11-22T22:02:00.002-08:002015-11-22T22:02:39.429-08:00Fix ORA-16053<div dir="ltr" style="text-align: left;" trbidi="on">
Today I will show you how to handle ORA-16053 error<br />
<br />
--Check maximum sequence number of archive log<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
36 </code></pre>
<br />
<br />
--Now check parameter<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> col destination for a70
SQL> select dest_id, status, destination, error from v$archive_dest;
DEST_ID STATUS DESTINATION ERROR
---------- --------- ---------------------------------------------------------------------- -----------------------------------------------------------------
1 BAD PARAM ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration
2 VALID PROD
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 INACTIVE
11 VALID /u01/app/oracle/product/11.0.1/db_1/dbs/arch
11 rows selected. </code></pre>
<br />
Here is error which means db_unique_name not setted on parameter.<br />
<br />
<br />
--Also below query provide us bad param if not properly configured.<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select status, dest_id, error from v$archive_dest_status;
STATUS DEST_ID ERROR
--------- ---------- -----------------------------------------------------------------
BAD PARAM 1 ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration
VALID 2
INACTIVE 3
INACTIVE 4
INACTIVE 5
INACTIVE 6
INACTIVE 7
INACTIVE 8
INACTIVE 9
INACTIVE 10
VALID 11
11 rows selected. </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(PROD,STAND)
--set log_archive_config
SQL> alter system set log_archive_config='DG_CONFIG=(STAND,PROD)';
System altered. </code></pre>
<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=PROD
log_archive_dest_10 string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=PROD VALID_FOR=(ONLINE
_LOGFILE,PRIMARY_ROLE) async d
b_unique_name=PROD
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES_ DB_UNIQUE_NAME=STAND' scope=both;
System altered. </code></pre>
<br />
<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select status, dest_id, error from v$archive_dest_status where dest_id=2;
STATUS DEST_ID ERROR
-------------------- ---------- -----------------------------------------------------------------
VALID </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select dest_id, status, destination, error from v$archive_dest;
DEST_ID STATUS DESTINATION ERROR
---------- -------------------- ---------------------------------------------------------------------- -----------------------------------------------------------------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID PROD
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 INACTIVE
11 VALID USE_DB_RECOVERY_FILE_DEST
11 rows selected. </code></pre>
<br />
<br />
--Now check again and you will see logs start to receive and applying.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
45 </code></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-62194580868263339172015-11-16T00:23:00.002-08:002015-11-16T00:23:53.202-08:00Fix INS-32025<div dir="ltr" style="text-align: left;" trbidi="on">
Sometimes we face <b>"[INS-32025] The chosen installation conflicts with software already installed in the given Oracle home"</b> error when we reinstall Oracle home.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiunGlKPF24bhulSSzr54vL8vYZDK9tVURhB9rUoKEfrAwR2Zahn33Jp-FMf-R3jPOVO7XXazPLd8pB_zF-DgoZb-FgTqarH3hJyFsNHb5cz_z21gd3wfh4x6-hC6ZzuPG2XHIQpXukhRyI/s1600/err1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="81" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiunGlKPF24bhulSSzr54vL8vYZDK9tVURhB9rUoKEfrAwR2Zahn33Jp-FMf-R3jPOVO7XXazPLd8pB_zF-DgoZb-FgTqarH3hJyFsNHb5cz_z21gd3wfh4x6-hC6ZzuPG2XHIQpXukhRyI/s320/err1.jpg" width="320" /></a></div>
<br />
<br />
To fix this error, go to oraInventory folder.<br /><br /><br />
cd /u01/oraInventory/ContentsXML --this is my folder<br />
edit inventory.xml any editor<br />
remove below line:<br />
<br />
<HOME NAME="OraDb11g_home1" LOC="/u01/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/><br />
<br />
execute runInstaller again</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-8691116677505786972015-11-05T00:36:00.002-08:002015-11-05T00:36:46.105-08:00List of most used tables<div dir="ltr" style="text-align: left;" trbidi="on">
I requested to gather most used tables list in production database.<br />
After investigation I found some advice and of course metalink note.<br />
<br />
Here is OTN link: <a href="https://forums.oracle.com/forums/thread.jspa?threadID=511661">https://forums.oracle.com/forums/thread.jspa?threadID=511661</a><br />
<br />
<br />
Use below query.<br />
<br />
<div class="MsoNormal">
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> SELECT ROWNUM AS RANK, Seg_Lio.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'LIO' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_r.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Reads' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Writes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO READS Direct' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads direct'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'DB Block changes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'db block changes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20 </code></pre>
</div>
For detail please refer to metalink note: 252597.1<br />
<br />
Also you may select below views to get some necessary information.<br />
<br />
--tables which modificated<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select * from dba_tab_modifications where table_owner='????' </code></pre>
<br />
Starting Oracle 11g if your statistics_level`s value TYPICAL Oracle automatically will gather and monitor your tables.<br />
<br />
--List of monitored tables<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select * from dba_tables where owner='?????' and monitoring='YES' </code></pre>
<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select owner, monitoring, count(1)
from dba_tables
group by owner, monitoring
order by 1, count(1) </code></pre>
<br />
<br />
<br />
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-40851264714232157632015-10-29T23:02:00.002-07:002015-10-30T00:52:43.861-07:00Fix ORA-08104<div dir="ltr" style="text-align: left;" trbidi="on">
While rebuilding index my toad session was hanged and I decided to kill session but it also hanged<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL> alter index xxx.xxxxx rebuild online parallel 8;</code></pre>
<br />
To fix it I connected db with new sesssion and got object_id of mentioned index<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> SQL> select object_id obj#, i.status from dba_indexes i, dba_objects o
where o.object_name=i.index_name and
i.index_name='LOYAL_RES_IDX1';</code></pre>
<br />
<br />
--Note flags<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL> select flags from ind$ where obj#=356001; </code></pre>
<br />
<br />
Using DBMS_REPAIR package clean index<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL> declare
isclean boolean;
begin
isclean :=false;
while isclean=false loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN
(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/ </code></pre>
<br />
<br />
--Note flags<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> SQL> select flags from ind$ where obj#=356001; </code></pre>
<br />
<br />
As you see it was decreased and normal</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6076732319849186801.post-31388239020008280072015-10-15T04:49:00.002-07:002015-10-15T04:52:26.353-07:00Brief practice about shrink extents<div dir="ltr" style="text-align: left;" trbidi="on">
Today after analyzing segments I found out some tables have 0 rows but segment size more than 2 GB. For practice purpose I simulated some actions on test environment. To do that I created a simple user, grant him essential grants, created table, load some data etc.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> create user ulfet identified by ulfet;
grant connect, resource to ulfet; </code></pre>
<br />
--create table with 1 mln rows<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> create table ulfet.btt nologging as
SELECT
LEVEL id,
dbms_random.string ('U', 10) text1,
reverse(dbms_random.string ('U', 10)) text2
FROM dual
CONNECT BY LEVEL <= 1000000; </code></pre>
<br />
--Check segment, block size<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name
and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
37748736,36,51,4608,,, </code></pre>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 501px;"><colgroup><col style="mso-width-alt: 2962; mso-width-source: userset; width: 61pt;" width="81"></col><col span="3" style="width: 48pt;" width="64"></col><col style="mso-width-alt: 2633; mso-width-source: userset; width: 54pt;" width="72"></col><col style="mso-width-alt: 3364; mso-width-source: userset; width: 69pt;" width="92"></col><col style="width: 48pt;" width="64"></col></colgroup><tbody>
<tr height="25" style="height: 18.75pt;"><td align="right" class="xl66" height="25" style="border-top: none; height: 18.75pt;"><br /></td><td align="right" class="xl66" style="border-left: none; border-top: none;"><br /></td><td align="right" class="xl66" style="border-left: none; border-top: none;"><br /></td><td align="right" class="xl66" style="border-left: none; border-top: none;"><br /></td><td class="xl65" style="border-left: none; border-top: none;"><br /></td>
<td class="xl65" style="border-left: none; border-top: none;"></td>
<td class="xl65" style="border-left: none; border-top: none;"></td>
</tr>
</tbody></table>
--compute statistics<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> analyze table ulfet.btt compute statistics; </code></pre>
<br />
--Check again segment, block size<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
37748736,36,51,4608,1000000,14.10.2015 10:03:04,4504 </code></pre>
<br />
--inserted 15 000 000 rows<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> insert into ulfet.btt
select * from ulfet.btt;
insert into ulfet.btt
select * from ulfet.btt;
insert into ulfet.btt
select * from ulfet.btt;
insert into ulfet.btt
select * from ulfet.btt;
select count(1) from ulfet.btt;
--16000000 </code></pre>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">commit;</code></pre>
<br />
Now check segment size<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
587202560,560,141,71680,1000000,14.10.2015 10:03:04,4504 </code></pre>
<br />
--gather table`s stat again for fresh statistics<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> EXEC DBMS_STATS.gather_table_stats('ULFET', 'BTT');</code></pre>
<br />
--Check segment and block size<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
587202560,560,141,71680,16000000,14.10.2015 10:10:15,71377</code></pre>
<br />
Now delete rows<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> delete from ulfet.btt;
commit; </code></pre>
<br />
--Again take statistics<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> EXEC DBMS_STATS.gather_table_stats('ULFET', 'BTT');</code></pre>
<br />
--Check again<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
587202560,<b>560,141</b>,71680,0,14.10.2015 10:17:30,71377 </code></pre>
<br />
As you see extent not changed<br />
<br />
Ulfet user has quota on two tablespaces<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select
tablespace_name,
bytes,
bytes/1024/1024 SIZEMB,
blocks
from dba_ts_quotas
where username='ULFET';</code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> TABLESPACE_NAME,BYTES,SIZEMB,BLOCKS
DATA01,0,0,0
DATA001,624951296,596,76288 </code></pre>
<br />
--Move segment to another tablespace for shrink purpose<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> alter table ulfet.btt move tablespace DATA01;</code></pre>
<br />
Now check again<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> SQL> select
tablespace_name,
bytes,
bytes/1024/1024 SIZEMB,
blocks
from dba_ts_quotas
where username='ULFET'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> TABLESPACE_NAME,BYTES,SIZEMB,BLOCKS
DATA01,65536,0,0625,8
DATA001,37748736,36,4608 </code></pre>
<br />
--Check segment size and extents<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">SQL> select
bytes,
bytes/1024/1024 SIZEMB,
extents,
s.blocks,
t.num_rows,
t.last_analyzed,
t.blocks
from dba_segments s, dba_tables t
where s.segment_name = t.table_name and segment_name='BTT'; </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;"> BYTES,SIZEMB,EXTENTS,BLOCKS,NUM_ROWS,LAST_ANALYZED,BLOCKS_1
65536,<b>0,0625</b>,1,8,0,14.10.2015 10:17:30,71377 </code></pre>
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-23515824396511044032015-09-20T06:10:00.001-07:002015-09-20T06:10:39.597-07:00Activate Standby Database on Oracle 11g<div dir="ltr" style="text-align: left;" trbidi="on">
On Oracle 11g to activate standby database we should do:<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828px;"><code style="word-wrap: normal;">--stop apply process
SQL> alter database recover managed standby database cancel;
--finish recovery managed
SQL> alter database recover managed standby database finish;
Database altered.
--activate database
SQL> alter database activate physical standby database;
Database altered.
--open database
SQL> alter database open;
Database altered.
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD1 READ WRITE
SQL> </code></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-66057607506753592572015-07-07T00:43:00.002-07:002015-07-26T20:49:04.351-07:00Remove Data Guard Configuration Oracle 11g Today I will show you how to remove DG configuration<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">DGMGRL> show configuration;
Configuration
Name: DGMANAGER
Enabled: YES
Protection Mode: MaxAvailability
Databases:
PROD - Primary database
STAND - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DGMANAGER":
SUCCESS </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> DGMGRL> remove configuration;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed. </code></pre>
<br />
I tried to remove database from configuration<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">DGMGRL> remove database "STAND";
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed. </code></pre>
<br />
But result was same. Unsuccess.<br />
<br />
I changed protection mode to MaxPerformance and then retied again<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">DGMGRL> edit configuration set protection mode as MaxPerformance;
Succeeded. </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">DGMGRL> show configuration;
Configuration
Name: DGMANAGER
Enabled: YES
Protection Mode: MaxPerformance
Databases:
PROD - Primary database
STAND - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DGMANAGER":
SUCCESS </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">DGMGRL> remove database "STAND"
Removed database "STAND" from the configuration
DGMGRL> remove configuration;
Removed configuration
DGMGRL> show configuration
Error: ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> </code></pre>
<br />
Dataguard configuration successfully removed. Now we have to disable some parameters.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> set linesize 200
SQL> show parameter dg_broker
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.1.0
/db_1/dbs/dr1PROD.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.1.0
/db_1/dbs/dr2PROD.dat
dg_broker_start boolean TRUE
SQL> </code></pre>
<br />
<br />
Change dg_broker_start`s value to FALSE on both side<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">alter system set dg_broker_start=FALSE;</code></pre>
<br />
At the end<br />
remove dr*.dat files on both side<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> -bash-3.00$ pwd
/u01/app/oracle/product/11.1.0/db_1/dbs
-bash-3.00$ ls -lrt dr*.dat
-rw-r----- 1 oracle oinstall 1024 Jul 07 12:25 dr2PROD.dat
-rw-r----- 1 oracle oinstall 1024 Jul 07 12:25 dr1PROD.dat
-bash-3.00$ rm dr*.dat </code></pre>
<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> -bash-4.2$ pwd
/u01/app/oracle/product/11.0.1/db_1/dbs
-bash-4.2$ ls -lrt dr*.dat
-rw-r----- 1 oracle oinstall 1024 Jul 07 12:24 dr2STAND.dat
-rw-r----- 1 oracle oinstall 1024 Jul 07 12:24 dr1STAND.dat
-bash-4.2$ rm dr*.dat </code></pre>
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-23136888188916456712015-02-08T20:03:00.004-08:002015-07-26T20:50:32.988-07:00WARNING: Subscription for node down event still pendingToday early morning I saw strange error on listener log.<br />
<br />
...<br />
Mon Feb 09 07:53:24 2015<br />
WARNING: Subscription for node down event still pending<br />
09-FEB-2015 07:53:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=????)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599744)) * status * 0<br />
...<br />
<br />
After searching on metalink I found : MOS document with ID 372959.1<br />
<br />
In a nutshell to fix it we should add SUBSCRIBE_FOR_NODE_DOWN_EVENT_YOURLISTENER_NAME=OFF on listener.ora file<br />
<br />
In my case:<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF </code></pre>
<br />
Then restart/reload listener.<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> lsnrctl reload; </code></pre>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-19435248495474670662014-12-17T21:38:00.000-08:002015-07-26T20:51:11.742-07:00ORA-01122: database file 22 failed verification checkToday when I tried to restart my test database I got strange but known error.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2163168 bytes
Variable Size 2298482208 bytes
Database Buffers 3036676096 bytes
Redo Buffers 7409664 bytes
Database mounted.
ORA-01122: database file 22 failed verification check
ORA-01110: data file 22: '/u01/app/oracle/oradata/???/archive_tbs01.dbf'
ORA-01200: actual file size of 65536 is smaller than correct size of 402176 </code></pre>
<br />
<br />
After researching it means inconsistency of controlfile. So, we need recreate or restore it from backup.<br />
<br />
Do one of the following:<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL > alter database backup controlfile to trace;
Database altered.</code></pre>
<br />
or<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/admin/PROD/bdump/controlfile.bkp';
Database altered. </code></pre>
<br />
or<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/admin/PROD/bdump/controlfile.trc';
Database altered. </code></pre>
<br />
shutdown database and start it on nomount state:<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.</code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2163168 bytes
Variable Size 2298482208 bytes
Database Buffers 3036676096 bytes
Redo Buffers 7409664 bytes
SQL></code></pre>
<br />
<br />
edit your trace file and try to create --I created createnewcontrolfile.sql file<br />
<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL > @createnewcontrolfile.sql
CREATE CONTROLFILE REUSE DATABASE "???" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2921
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/???/redo1.log' SIZE 400M,
GROUP 2 '/u01/app/oracle/oradata/???/redo2.log' SIZE 400M,
GROUP 3 '/u01/app/oracle/oradata/???/redo3.log' SIZE 400M,
GROUP 4 '/u01/app/oracle/oradata/???/redo4.log' SIZE 400M,
GROUP 5 '/u01/app/oracle/oradata/???/redo5.log' SIZE 400M,
GROUP 6 '/u01/app/oracle/oradata/???/redo6.log' SIZE 400M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/???/system01.dbf',
...
'/u01/app/oracle/oradata/???/FCJ_DATA_TS_13.dbf'
CHARACTER SET AL32UTF8; </code></pre>
<div>
<br /></div>
<div>
<br /></div>
<div>
At the end recover database using new controlfile</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> recover database using backup controlfile</code></pre>
</div>
<div>
<br /></div>
<div>
Now open the database and enjoy</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> alter database open; </code></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-62202409800314656502014-11-27T04:19:00.000-08:002014-11-27T09:16:28.874-08:00SP2-0618: Cannot find the Session Identifier. Today I could not enabled autorace and reason was SP2-0618.<br />
<br />
After looking for in google I found solution and now want to share it.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> set autotrace traceonly;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report </code></pre>
<br />
To enable autotrace PLUSTRACE role should be exists! If not exists create it.<br />
<br />
Under $ORACLE_HOME\sqlplus\admin\ you may execute by SYS user<br />
<br />
<span style="background-color: #f0f0f0; font-family: arial; font-size: 12px; line-height: 20px;"> SQL> @$ORACLE_HOME\sqlplus\admin\plustrce.sql; </span><br />
<br />
Another way you may just manually create plustrace role and assign select on necessary views;<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded. </code></pre>
<br />
At the end grant plustrace role to needed user;<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> grant plustrace to ulfet;
Grant succeeded. </code></pre>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-68453043252934648342014-10-26T08:12:00.000-07:002014-10-27T22:35:57.124-07:00Enable Archivelog mode on Oracle11g R2 RAC environmentToday I want to show you how to enable archivelog mode on Oracle 11g R2 RAC environment.<br />
<br />
1. Checking log_mode:<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> [oracle@rac1 acfs]$ export ORACLE_SID=RAC1
[oracle@rac1 acfs]$ sqlplus "/as sysdba"
SQL> archive log list
Database log mode <b>No Archive Mode</b>
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 10
SQL> </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@rac2 acfs]$ export ORACLE_SID=RAC2
[oracle@rac2 acfs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 13 18:13:53 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name, open_mode, log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
RAC READ WRITE <b>NOARCHIVELOG</b>
SQL> </code></pre>
<br />
Using SRVCTL utility stop cluster database<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> [oracle@rac1 ~]$ srvctl stop database -d RAC </code></pre>
<div>
<br /></div>
<div>
--checking statuses</div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is not running on node rac1
Instance RAC2 is not running on node rac2 </code></pre>
<br />
Connect to node 1 and startup instance on mount mode, enable archiving and at the end shutdown the instance:<br /><br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> startup mount
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 570429008 bytes
Database Buffers 272629760 bytes
Redo Buffers 5132288 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;
SQL> SHUTDOWN IMMEDIATE; </code></pre>
<br />
As you know we have shared files between instances so, this operation only has to be done from a single node.<br />
<br />
Now start the clustered database<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> [oracle@rac1 ~]$ srvctl start database -d RAC
[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2 </code></pre>
<br />
<br />
--Check again on both nodes<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> select name, open_mode, log_mode, instance_name from v$database, v$instance;
NAME OPEN_MODE LOG_MODE INSTANCE_NAME
--------- -------------------- ------------ ----------------
RAC READ WRITE <b>ARCHIVELOG </b>RAC1
SQL> select name, open_mode, log_mode, instance_name from v$database, v$instance;
NAME OPEN_MODE LOG_MODE INSTANCE_NAME
--------- -------------------- ------------ ----------------
RAC READ WRITE <b>ARCHIVELOG </b>RAC2
SQL> </code></pre>
<div>
<br /></div>
<div>
That`s all.</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-39044763420265527742014-10-15T23:50:00.000-07:002014-10-15T23:50:29.163-07:00Error: ORA-16810: multiple errors or warnings detectedToday after restarting primary and standby database servers I faced with ORA-16810 error<br />
<br />
DGMGRL> show configuration;<br />
<br />
Configuration - DGMANAGER<br />
<br />
Protection Mode: MaxAvailability<br />
Databases:<br />
XXXPROD - Primary database<br />
Error: ORA-16810: multiple errors or warnings detected for the database<br />
<br />
XXXSTND - Physical standby database<br />
Error: ORA-01017: invalid username/password; logon denied<br />
<br />
Fast-Start Failover: DISABLED<br />
<br />
Configuration Status:<br />
ERROR<br />
<br />
I did not understand what and why it happened...<br />
After research I found that it was related password file of standby side.<br />
<br />
<br />
DGMGRL> show database 'XXXPROD' <br />
<br />
Database - XXXPROD<br />
<br />
Role: PRIMARY<br />
Intended State: TRANSPORT-ON<br />
Instance(s):<br />
FCDBPROD<br />
Error: ORA-16737: the redo transport service for standby database "XXXSTND" has an error<br />
<br />
Database Warning(s):<br />
ORA-16629: database reports a different protection level from the protection mode<br />
<br />
Database Status:<br />
ERROR<br />
<br />
--On standby side<br />
DGMGRL> show configuration<br />
<br />
Configuration - DGMANAGER<br />
<br />
Protection Mode: MaxAvailability<br />
Databases:<br />
XXXPROD - Primary database<br />
XXXSTND - Physical standby database<br />
<br />
Fast-Start Failover: DISABLED<br />
<br />
Configuration Status:<br />
ORA-01017: invalid username/password; logon denied<br />
ORA-16625: cannot reach database "XXXPROD"<br />
DGM-17017: unable to determine configuration status<br />
<br />
<br />
But connection between servers were clean. Listeners were up and tnsping success.<br />
<br />
Hm, interesting what will provide us v$archive_dest:<br />
<br />
SQL> select error from v$archive_dest;<br />
<br />
ERROR<br />
-----------------------------------------------------------------<br />
<br />
ORA-16191: Primary log shipping client not logged on standby<br />
<br />
It looks like could not connect to my standby db. Both instance are up and tnsping were OK, Also using<br />
sqlplus user/pass@'connection_stringPRIMARY'<br />
sqlplus user/pass@'connection_stringSTANDBY'<br />
<br />
on both side between were success.<br />
<br />
<br />
On the forum I found that something related passwordfile.<br />
But I did not change any thing...<br />
<br />
On both side remote_login_passwordfile`s value was EXCLUSIVE<br />
<br />
SQL> show parameter remote_login_passwordfile<br />
<br />
NAME<span class="Apple-tab-span" style="white-space: pre;"> </span> TYPE<span class="Apple-tab-span" style="white-space: pre;"> </span> VALUE<br />
------------------------------------ ----------- ------------------------------<br />
remote_login_passwordfile<span class="Apple-tab-span" style="white-space: pre;"> </span> string<span class="Apple-tab-span" style="white-space: pre;"> </span> EXCLUSIVE<br />
SQL><br />
<br />
And<br />
<br />
SQL> select * from v$pwfile_users;<br />
<br />
USERNAME<span class="Apple-tab-span" style="white-space: pre;"> </span> SYSDB SYSOP SYSAS<br />
------------------------------ ----- ----- -----<br />
SYS<span class="Apple-tab-span" style="white-space: pre;"> </span> TRUE TRUE FALSE<br />
<br />
SQL><br />
<br />
I just copied Primary`s password file to standby side and then checked.<br />
before it renamed old password file: mv orapwXXX.old<br />
<br />
scp orapwXXX oracle@XX.XX.XX.X:/u01/app/oracle/product/11.2.0.3/db_1/dbs<br />
--renamed it also for stanby<br />
<br />
<br />
<br />
now everything is OK<br />
<br />
[oracle@fcdbdb dbs]$ dgmgrl /<br />
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production<br />
<br />
Copyright (c) 2000, 2009, Oracle. All rights reserved.<br />
<br />
Welcome to DGMGRL, type "help" for information.<br />
Connected.<br />
DGMGRL> show configuration<br />
<br />
Configuration - DGMANAGER<br />
<br />
Protection Mode: MaxAvailability<br />
Databases:<br />
XXXPROD - Primary database<br />
XXXSTND - Physical standby database<br />
<br />
Fast-Start Failover: DISABLED<br />
<br />
Configuration Status:<br />
SUCCESS<br />
<br />
--primary<br />
SQL> select max(sequence#) from v$archived_log;<br />
<br />
MAX(SEQUENCE#)<br />
--------------<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> 7819<br />
<br />
--standby<br />
SQL> select max(sequence#) from v$archived_log;<br />
<br />
MAX(SEQUENCE#)<br />
--------------<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> 7819<br />
<br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-90916368997192258182014-10-12T21:50:00.002-07:002014-10-12T21:50:11.409-07:00Warning: ORA-16826: apply service state is inconsistent with the DelayMins propertyToday when I tested dataguard configuration I saw ORA-16826 error.<br />
<br />
DGMGRL> show configuration;<br />
<br />
Configuration - DGMANAGER<br />
<br />
Protection Mode: MaxPerformance<br />
Databases:<br />
PROD - Primary database<br />
PRODST - Physical standby database<br />
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property<br />
<br />
Fast-Start Failover: DISABLED<br />
<br />
Configuration Status:<br />
WARNING<br />
<br />
DGMGRL><br />
<br />
<br />
This is because I managed my standby database with NODELAY option.<br />
<br />
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY;<br />
<br />
To fix this cancel recover managed:<br />
<br />
SQL> alter database recover managed standby database cancel;<br />
<br />
Database altered.<br />
<br />
Start with current logfile option:<br />
<br />
SQL> alter database recover managed standby database using current logfile disconnect;<br />
<br />
Database altered.<br />
<br />
Try again<br />
<br />
<br />
DGMGRL> show configuration;<br />
<br />
Configuration - DGMANAGER<br />
<br />
Protection Mode: MaxPerformance<br />
Databases:<br />
PROD - Primary database<br />
PRODST - Physical standby database<br />
<br />
Fast-Start Failover: DISABLED<br />
<br />
Configuration Status:<br />
SUCCESS<br />
<br />
DGMGRL>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-30390169457117847402014-10-08T02:55:00.002-07:002014-10-08T02:55:28.103-07:00Online Patch ApplyHello,<br />
<br />
Today I want to share my experience about online patching.<br />
<br />
Download needed patch : https://support.oracle.com<br />
For tutorial I got p16928674_112030_Generic.zip file.<br />
<br />
<br />
1. Go to $ORACLE_HOME/Opatch dir and call opatch with parameters<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@dbserver OPatch]$ pwd
/u01/app/oracle/product/11.2.0/db_1/OPatch
[oracle@dbserver OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_11-53-07AM.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-10-08_11-53-07AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded. </code></pre>
<br />
--Check version<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> [oracle@dbserver OPatch]$ ./opatch version
Invoking OPatch 11.2.0.1.7
OPatch Version: 11.2.0.1.7
OPatch succeeded. </code></pre>
<div>
<br /></div>
<br />
2. Take backup of OPatch dir but it will be good to take entire ORACLE_HOME`s backup.<br />
[tar, mv, etc]<br />
<br />
3. export path<br />
[oracle@dbserver db_1]$ export PATH=$PATH:$ORACLE_HOME/OPatch<br />
<br />
4. unzip patch file<br />
[oracle@dbserver Desktop]$ unzip p16928674_112030_Generic.zip<br />
<br />
<br />
If you want you can check applicable using CheckApplicable parameter.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@dbserver OPatch]$ ./opatch prereq CheckApplicable -ph /home/oracle/Desktop/16928674
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-13-31PM.log
Invoking prereq "checkapplicable"
Prereq "checkApplicable" for patch 16928674 passed.
OPatch succeeded. </code></pre>
<br />
5. Apply patch<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@dbserver 16928674]$ opatch apply
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log
Applying interim patch '16928674' to OH '/u01/app/oracle/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
Do you want to proceed? [y|n]
<b> y </b>
User Responded with: Y
All checks passed.
Backing up files...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Copying file to "/u01/app/oracle/product/11.2.0/db_1/sqlpatch/16928674/postinstall.sql"
<b> Patch 16928674 successfully applied</b>
<b>Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log </b>
OPatch succeeded. </code></pre>
<br />
To check result you can check log file :<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@dbserver Desktop]$ more /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log
....
[Oct 8, 2014 2:22:21 PM] Files check OK: Files from Patch ID 16928674 are present in Oracle Home.
[Oct 8, 2014 2:22:21 PM] Finish applying patch to local system at Wed Oct 08 14:22:21 AZST 2014
<b> [Oct 8, 2014 2:22:21 PM] Patch 16928674 successfully applied </b>
[Oct 8, 2014 2:22:21 PM] Finishing ApplySession at Wed Oct 08 14:22:21 AZST 2014
[Oct 8, 2014 2:22:21 PM] Total time spent waiting for user-input is 3 seconds. Finish at Wed Oct 08 14:22:21 AZST 2014
.... </code></pre>
<div>
<br /></div>
<div>
<br /></div>
<div>
If everythins are OK, then execute postinstallion script</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> @/home/oracle/Desktop/16928674/postinstall;
Calling rdbms/admin/prvtbpci.plb on 08-OCT-14 02.28.02.958883 PM +05:00
Package body created.
Package body created.
SQL> </code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Now you can check opatch lsinventory again:</div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@dbserver 16928674]$ opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-23-31PM.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-10-08_14-23-31PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
<b> Patch 16928674 : applied on Wed Oct 08 14:22:20 AZST 2014 </b>
Unique Patch ID: 17483843
Created on 2 Apr 2014, 04:20:57 hrs PST8PDT
<b> Bugs fixed:
16928674 </b>
--------------------------------------------------------------------------------
OPatch succeeded. </code></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-40304462390259969942014-09-17T04:06:00.004-07:002014-09-17T04:06:55.788-07:00Forgot sysman password ?Today I could not remember sysman password for database grid control. How carefully may I change sysman password ? Just follow.<br />
<br />
1. Stop OMS (go to OMS dir)<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> [oracle@gridrepo bin]$ ./emctl stop oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down </code></pre>
<div>
<br /></div>
<div>
2. Config oms password with -change_repos_pwd parameter</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@gridrepo bin]$ ./emctl config oms -change_repos_pwd
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
-change_in_db option not specified. Password not changed in backend.
<b>Enter Repository User's New Password : </b>
Updating repository password in Credential Store...
Repository password in Credential Store updated successfully.
Bounce the OMS.
Successfully changed repository password.
If you have multiple OMS's in your environment, run this command on all of them.
[oracle@gridrepo bin]$ </code></pre>
</div>
<div>
<br /></div>
<div>
<div>
Or you can also use "<b>-change_in_db</b>" parameter with above command which is optional.</div>
<div>
<br /></div>
<div>
But be aware of prompt will ask you enter existing sysman password. Change sysman password by using tradional command:</div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> alter user sysman identified by new_pass;</code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6076732319849186801.post-15148049410036397592014-09-17T03:37:00.000-07:002014-09-17T04:23:35.810-07:00Add, enable, disable service using srvctlA brief post about add||enable||disable service using SRVCTL.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@oralab1 dbhome_1]$ srvctl config database
orcl
[oracle@oralab1 dbhome_1]$ srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: orcl
Disk Groups: DATA
<b>Services:</b> </code></pre>
<br />
--There is no any service(s).<br />
<br />
--create new service:<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> begin
2 dbms_service.create_service('NEWORCL','NEWORCL');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select name, enabled from dba_services;
NAME ENA
--------------------------------- ---------------------------
SYS$BACKGROUND NO
SYS$USERS NO
<b> NEWORCL NO </b>
orclXDB NO
orcl NO </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@oralab1 dbhome_1]$ srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: orcl
Disk Groups: DATA
<b> Services: NEWORCL </b>
[oracle@oralab1 dbhome_1]$ srvctl status service -d orcl
<b> Service NEWORCL is not running. </b></code></pre>
<br />
<br />
--service is not started, let`s start<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> exec dbms_service.start_service('NEWORCL');
PL/SQL procedure successfully completed.
SQL></code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">[oracle@oralab1 dbhome_1]$ srvctl status service -d orcl
<b> Service NEWORCL is running </b> </code></pre>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">
[oracle@oralab1 dbhome_1]$ srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: orcl
Disk Groups: DATA
<b> Services: NEWORCL </b>
--disable service
[oracle@oralab1 dbhome_1]$ srvctl disable service -d orcl -s NEWORCL
[oracle@oralab1 dbhome_1]$ srvctl config service -d orcl
<b> Service name: NEWORCL </b>
<b>Service is disabled </b>
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition: </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> --Enable service
[oracle@oralab1 dbhome_1]$ srvctl enable service -d orcl -s NEWORCL
[oracle@oralab1 dbhome_1]$ srvctl config service -d orcl
<b> Service name: NEWORCL</b>
<b> Service is enabled </b>
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition: </code></pre>
<div>
<br />
--You may stop/start services using SRVCTL utility too.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> -- <span class="e5860" id="e5860_2" style="border-bottom-width: 1px !important; border-style: none none solid !important; color: rgb(0, 107, 255) !important; cursor: pointer; display: inline !important; float: none; font-weight: bold; height: 12px; list-style: none; margin: 0px !important; padding: 0px !important; text-decoration: underline !important;">stop</span> service
[oracle@oralab1 dbhome_1]$ srvctl stop service -s NEWORCL -d orcl
[oracle@oralab1 dbhome_1]$ srvctl status service -d orcl
<b> Service NEWORCL is not running.</b> </code></pre>
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">--start service
Services: NEWORCL
[oracle@oralab1 dbhome_1]$ srvctl start service -s NEWORCL -d orcl
[oracle@oralab1 dbhome_1]$ srvctl status service -d orcl
<b> Service NEWORCL is running </b> </code></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6076732319849186801.post-9664310201401114062014-04-30T00:00:00.001-07:002014-09-17T03:56:44.948-07:00Oracle 11g (FGA) Access Control ListAfter got API for sending SMS and developed packages we tried to send URL, but we faced below known error.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1 </code></pre>
<br />
Starting Oracle 11g Oracle introduce FGA (Fine Grained Access) for using UTTL_HTTP, UTL_MAIL etc packages.<br />
<br />
After reading <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#CHDJFJFF">Oracle notes</a>, we need to create ACL and then assgin it.<br />
<br />
Let`s check our db registery.<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> set linesize 400
SQL> col comp_name format a40
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
---------------------------------------- --------------------------------------------
OWB VALID
Oracle Application Express VALID
Oracle Enterprise Manager VALID
OLAP Catalog VALID
Spatial VALID
Oracle Multimedia VALID
Oracle XML Database VALID
Oracle Text VALID
Oracle Expression Filter VALID
Oracle Rules Manager VALID
Oracle Workspace Manager VALID
COMP_NAME STATUS
---------------------------------------- --------------------------------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
18 rows selected. </code></pre>
<br />
<br />
--Creating new ACL<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'smssend_nowsms.xml', --acl
description => 'Permissions to access http://10.10.9.15', --needed host
principal => 'ALARMSENDER', --my user
is_grant => TRUE,
privilege => 'connect');
COMMIT;
EN 2 3 4 5 6 7 8 9 D;
/ 10
PL/SQL procedure successfully completed.
SQL></code></pre>
<br />
<br />
--Assign it<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;"> SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'smssend_nowsms.xml',
host => '10.10.9.15',
lower_port => 8084,
upper_port => 8084);
COMMIT;
END;
/ </code></pre>
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL><br />
<br />
--Checking using select statement<br />
<br />
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.10.9.15'));
COLUMN_VALUE
10.10.9.15
10.10.9.*
10.10.*
10.*
* </code></pre>
<div>
<br /></div>
<div>
<br /></div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> select acl , host , lower_port , upper_port from dba_network_acls;
ACL,HOST,LOWER_PORT,UPPER_PORT
/sys/acls/smssend_nowsms.xml,10.10.9.15,8084,8084
SQL> select acl , principal , privilege , is_grant from dba_network_acl_privileges;
ACL,PRINCIPAL,PRIVILEGE,IS_GRANT
/sys/acls/smssend_nowsms.xml,ALARMSENDER,connect,true </code></pre>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
--Now try again</div>
<div>
<pre style="background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOcDfVCmcIQyDEr4ghohyKvtNtE-oO6lokQUlh4D17oxLB0qjpmmOV0omhOAHxIgCqGd_x8fJEAX_h_dK2JO_oj40wfevtuqcNR6rPqqh3jjaLaC-69RKBfa8ryWinrn71bTHGyWQSazlp/s320/codebg.gif) rgb(240, 240, 240); border: 1px dashed rgb(204, 204, 204); font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; width: 450.828125px;"><code style="word-wrap: normal;">SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;</code></pre>
</div>
<div>
works fine!</div>
Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-6076732319849186801.post-22239870511611753862014-03-27T22:02:00.002-07:002014-03-27T22:02:35.049-07:00Purge trace, alert, incident files using adrciToday suddenly I saw more disk usage. After investigation I found what used more space.<br />
<br />
[oracle@fc-db-tst1 FPREPROD]$ du -sh *<br />
9.4G<span class="Apple-tab-span" style="white-space: pre;"> </span>alert<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>cdump<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>hm<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>incident<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>incpkg<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>ir<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>lck<br />
3.5M<span class="Apple-tab-span" style="white-space: pre;"> </span>metadata<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>metadata_dgif<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>metadata_pv<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>stage<br />
4.0K<span class="Apple-tab-span" style="white-space: pre;"> </span>sweep<br />
7.2G <span class="Apple-tab-span" style="white-space: pre;"> </span>trace<br />
<br />
<br />
<br />
[oracle@fc-db-tst1 alert]$ adrci<br />
<br />
ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 08:17:10 2014<br />
<br />
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.<br />
<br />
ADR base = "/u01/app/oracle"<br />
adrci> show homes<br />
ADR Homes:<br />
diag/rdbms/fpreprod/FPREPROD<br />
adrci> show control<br />
<br />
ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:<br />
*************************************************************************<br />
ADRID <b>SHORTP_POLICY LONGP_POLICY </b> LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME <br />
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------<br />
1753316741 <b>720 8760</b> 2014-01-24 00:26:21.230527 +04:00 2014-03-24 15:02:28.535497 +04:00 1 2 80 1 2014-01-24 00:26:21.230527 +04:00 <br />
1 rows fetched<br />
<br />
<br />
LONGP_POLICY and SHORTP_POLICY set`s via hour.<br />
720 = (720/24 = 30 days)<br />
8760 = (8760/24 = 365 days)<br />
<br />
Let`s set new values.<br />
<br />
adrci> set control (SHORTP_POLICY = 240)<br />
adrci> set control (LONGP_POLICY = 1095)<br />
adrci> show control<br />
<br />
ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:<br />
*************************************************************************<br />
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME <br />
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------<br />
1753316741 <b>240 1095 </b> 2014-03-28 08:19:14.448120 +04:00 2014-03-24 15:02:28.535497 +04:00 1 2 80 1 2014-01-24 00:26:21.230527 +04:00 <br />
1 rows fetched<br />
<br />
<br />
--Purging alert<br />
adrci> purge -age 10080 -type ALERT<br />
<br />
--Purging trace<br />
adrci> purge -age 10080 -type TRACE<br />
<br />
--Purging incident<br />
adrci> purge -age 10080 -type incident<br />
<br />
--Purging all<br />
adrci> purge -age 10080<br />
<br />
<br />
<br />
adrci> show alert<br />
<br />
ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:<br />
*************************************************************************<br />
Output the results to file: /tmp/alert_27908_1397_FPREPROD_1.ado<br />
<br />
2014-02-07 06:24:17.356000 +04:00<br />
Thread 1 advanced to log sequence 347 (LGWR switch)<br />
Current log# 2 seq# 347 mem# 0: /u01/app/oracle/oradata/FPREPROD/redo02.log<br />
2014-02-07 06:30:00.027000 +04:00<br />
adrci><br />
<br />
<br />
--Display last 50 rows<br />
adrci> show alert -TAIL 50<br />
<br />
--Display last 10 rows and output appended data as the file grows<br />
adrci> show alert -TAIL -F<br />
<br />
For more detail please refer to :<br />http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBHHBGBUnknownnoreply@blogger.com0