Disclaimer All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use.
This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove.
Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only.
This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only.
If you see any issues with Content and copy write issues, I am happy to remove if you notify me.
Contact Geek DBA Team, via geeksinsights@gmail.com
|
From 10g onwards there is a hidden parameter _fix_control that can be used to turn off/on a particular bug fix. (Be sure, this is hidden parameter and as usual note changing hidden parameters must be done after consulting Oracle)
For example, a patch 5483301 has been recently applied to the database using opatch, and after that you have seeing some issues, you want to determine the root cause, but you cannot rollback the patch to test, in such scenarious this hidden parameter can come in handy.
Given the same scenario, First check the patch registered and enabled. (replace your patch number)
SQL> select bugno, value, description, optimizer_feature_enable from v$system_fix_control where bugno=5483301;
BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
5483301 1 Use min repeat count 10.2.0.4
in freq histogram to compute the density
As my patch is listed here, I can disable and determine that the issue happening in the database is because of this patch or not. to do same.
alter system set "_fix_control"='5483301:off' scope=both;
or for the session
alter session set "_fix_control"='5483301:off';
SQL> select bugno, value, description, optimizer_feature_enable from v$system_fix_control where bugno=5483301;
BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
5483301 0 Use min repeat count 10.2.0.4
in freq histogram to compute the density
Now try to reproduce the issue you have. Once you determine the issue is because of this patch , you can let the bug fix off in the database or let it enable back.
alter system set "_fix_control"='5483301:on' scope=both;
or for the session
alter session set "_fix_control"='5483301:on';
Note:- provided only for educational purposes only, not to try in production systems unless you are sure what you are doing.
-Thanks
Geek DBA
Q1. Can we get old statistics restored for an object for example for a index or a table ?
Yes we can get the old statistics restored from Oracle version 10g onwards.However, if you use ANALYZE for statistics collection in 10g, automatic saving is not possible.
Q2. If so, where do the statistics history stored in and what is the retention?
DBA_OPTSTAT_OPERATIONS contain history of statistics operations performed.The retention period default value is 31 days.You would be able to restore the optimizer statistics to any time in last 31 days.
Select * from dba_optstat_operations where target like ‘%’;
DBA_TAB_STATS_HISTORY view contain a history of table statistics modifications.
Q3. Is it possible to restore a index statistics and where can I find the information?
RESTORE_TABLE_STATS procedure restores the statistics of a table along with this it also restores the index statistics associated with the table.However, the index statistics is not restored in case index is rebuild just before issuing restore_table_stats in 10g.This bug 5519322 is fixed in 11g release 2.Instead we can use DBMS_STATS.EXPORT_*_STATS and DBMS_STATS.IMPORT_*_STATS procedures, but before this we need to create a table to hold this statistics "DBMS_STATS.CREATE_STAT_TABLE".
-Thanks
WOW!!!! 12C database roadmap announced, as usual it is bringing us a big change in Oracle database architecture.
Oracle Open world started last sunday and Oracle CEO Larry Ellison announced a complete new paradigm architecture of Oracle Database with 12c, which is going to launched in calendar 2013.
From the architecture perspective, this is called multi tenant database.
Ellison's comments Sunday provided the most detailed look to date at the new edition, 12c, which is several years in the making.
"It is the first multitenant database in the world," Ellison said of the product. Separate memory and processes are allocated to each database, according to a slide he presented.
It incorporates a "fundamentally new architecture," he said. With it, customers can take "one dedicated set of memory, one set of operating system processes and then plug multiple separate private databases into that single container."
He also made the pitch for multitenancy as a way to ease the burden on corporate IT departments.
"Typically companies have hundreds, even thousands of separate databases," he said. "This becomes very expensive. You have to back up these thousands of databases. You have to buy hardware."
The multitenant capabilities in 12c provide "huge efficiency gains," Ellison said.
Oracle database 12c will be released in "calendar year 2013," an announcer stated prior to Ellison's keynote.
So basically a Separation of data dictionary as one database and other databases. Sounds something similar, Yes for me too. The way SQL Server/Sybase works , but lets wait for full details. Clock start ticking, tik, tik, tik.
Sources:-
Oracle CEO Ellison reveals more details about 'multitenant' 12c database
Oracle CEO Ellison reveals most detail to date about 'multitenant' 12c
Larry 'Shared databases are crap' Ellison reveals shared Oracle
Oracle CEO Ellison Reveals Most Detail to Date About 'multitenant
-Thanks
Geek DBA
Modifying the VIP or VIP Hostname of a 10g Oracle Clusterware Node
Reference Notes:
Note 283684.1 How to Change Interconnect/Public Interface IP Subnet in a 10g Cluster
For complete srvctl syntax on 10gR1, Refer to Appendix B of the RAC Admin Guide:
http://download-west.oracle.com/docs/cd/B13789_01/rac.101/b10765/toc.htm
Changing the VIP involves modification of the nodeapps, which includes the Virtual IP address, the GSD, the Listener, and Oracle Notification Services (ONS). The VIP can be modified while the nodeapps are running, however changes will not take effect until the VIP, and hence the nodeapps, are restarted.
Depending on the version of Oracle Clusterware that you are running, other resources on a node, such as database instances and ASM instances, are dependent on the VIP, so stopping the nodeapps may cause other resources to be stopped – therefore, this change should be made during a scheduled outage.
In most cases, changing Data Centers or IP addresses within a Data Center will already incur an outage for other reasons, because changes need to be made at the operating system level, and servers may even need to be moved – so there is most likely a scheduled outage for this type of maintenence already.
Continue reading RAC: Modifying VIP in 10g
Statement/session crashing with Few ora-07445 and a ora-600
(Publishing the statement is irrespective hence skipping)
ORA 600 [rworupo.2]
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382]
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2AD2FD908088], [], [], [], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [smboQbNxt()+103] [SIGSEGV] [ADDR:0x0] [PC:0x8F22023] [SI_KERNEL(general_protection)] []
Well our good luck chuck DBA has access to Metalink and found that similar to following the stack in our trace files too.
... intel_new_memcpy <- qersoSORowP <- qervwRowProcedure <- qeruaRowProcedure <- qerixFetchUniqueIndex <- qerjotRowProc <- qertbFetchByRowID <- qerjotRowProc <- qervwRowProcedure <- qersoFetch <- qervwFetch <- qerjotFetch <- qerjotFetch <- rwsfcd <- qeruaFetch <- qervwFetch <- qersoProcessULS <- qersoFetch ...
and this note:-
Query causes various ORA-07445 and ORA-600 errors [ID 1385197.1]
Apparently the db is upgraded to 11g from 10g and symptoms are matching, now we are on warpath to remove those corruptions.
But what is bad number data? How does it occur? According to my permissible knowledge
1) Numeric columns some how got chars into it?
2) Application interface accepts anything varchar into number column and inserts the same
for example: ‘1 ’, note the space after the digit.?
3) Conversion of columns :(?
4) etc reasons?
and how to resolve it?
1) First identify the rows that marked as corrupted
a) Identify the rows that are not equal to number values
select rowid from schema.table where rowid in (select rowid from schema.table where (numcolumn!=numcolumn/1);
this will list you all the rowid’s for that number column not equal to divisible/1 itself), this another way of checking your number column data.
alternatively you can download script from
b) Download the script (procedure) and execute in your database.
NOTE:428526.1 - Baddata Script To Check Database For Corrupt column data
c) Update the column with same value of the row that is marked as corrupted, but with multiply into one. (Exactly, you heard it correct, 1*1=(always)1, 2*1=2(always)
Recommendation:- Take a backup before you proceed
update schema.table set NUMCOLUMN = NUMCOLUMN * 1 where rowid in (select rowid from schema.table where ("NUMCOLUMN"!="NUMCOLUMN"/1))
Match the count of rows appeared in step a and then
commit;
d) Rerun the statement that is failing earlier, the above should fix the bad number data as of now. Remember this is something about data (type) corruption, not logical nor the physical corruption
-Thanks
Geek DBA
Happy Reading!!!
Hope you already aware of tracing sessions with different methods
11g onwards, you can trace a session (other session) with in the alter system command itself , no worries of finding pid etc etc. for example like below
SQL> alter session set events 'sql_trace {process : pid = <pid>, pname = <pname>, orapid = <orapid>} rest of event specification'
pid: v$process pid
pname: name of process
orapid: V$process spid
Reference Note:- Metalink (MOS) Doc ID 813737.1, “How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump
Further you can trace single SQL ID, look below,
SQL> alter session set events ‘sql_trace [sql:abcdhehe2v9s1]’;
SQL> alter session set events ‘sql_trace [sql:abcdhehe2v9s1] off’;
I am sure this is very useful when we troubleshoot or want to trace particular Sql_id or the process ID.
Continue reading Tracing Sessions: Single SQL_ID, Single Process, New 11g Event Syntax
How to purge a single statement from shared pool?
Additional info. (Thanks to my friend colleague for updating about the version its possible and the event)
For 10g.
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.
10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.
Also with out the following the event never get enabled, hence you need to set the following event before executing purge procedure with DBMS_SHARED_POOL
alter session set events '5614566 trace name context forever';
For 11g,
No event set required.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc739202021a%';
ADDRESS HASH_VALUE
---------------- ----------
000000085FAS77CF0 808728292
exec DBMS_SHARED_POOL.PURGE ('000000085FAS77CF0,808728292, 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc739202021a%';
no rows selected
Which background daemon writes to OCR disk? or the voting disk?
CRSD? Answer No
CRSD just read the OCR to know about its resources.
Its OCSSD under the following conditions , Oracle cluster synchronization service daemon (OCSSD) is responsible for Writing to OCR registry.
a. New Node Addition /Deletion
b. New Service Addition / Deletion
c. Every 4 Hrs while taking OCR backup
d. Under node eviction to update the group / resource member ship.
e. Service status?
If anyone knows any other than the above,please update comment, will add to the list.
Just an excerpt from the Documentation/Note as a ready reference who reads this post.
Oracle Wait events can be divided into two categories , that is idle (IDLE) to wait for events and non-idle (NON-IDLE) .
1) Idle wait event represents ORACLE is waiting on some work at the time of diagnosis and optimize the database, without much attention to this part of the event.
2) non-idle wait event dedicated for ORACLE activities, waiting for those waiting for an event is the time to adjust the database needs attention and study.
In Oracle 10g and 11g to wait for the event in 1116 . By v $ event_name view to see waiting for the event.
You can check v$event_name view field structure:
SQL> desc v $ event_name;
Name is empty ? Type
-------------------------------------------------- --------------
EVENT # NUMBER
EVENT_ID NUMBER
NAME VARCHAR2 (64)
PARAMETER1 VARCHAR2 (64)
Parameter2 VARCHAR2 (64)
Parameter3 VARCHAR2 (64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS # NUMBER
WAIT_CLASS VARCHAR2 (64)
Continue reading Performance Tuning Basics: Oracle Common Wait events
|
Oracle 23c New Features for DBA's
Follow Me!!!