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
|
One more for today,
Here is the error, this database link should point to production db,
SQL> create database link PRODDB connect to admin identified by admin using 'PROD';
create database link MQDB connect to strmadmin identified by strmadmin using 'PROD'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link proddb;
drop database link proddb
*
ERROR at line 1:
ORA-02024: database link not found
Weird, no rows in dba_db_links
Lets check, whether my global_name set to true and what value it is
Continue reading ORA-02082: a loopback database link must have a connection qualifier
When a large transaction got killed, terminated we all know that smon will do the rollback of the transaction, “smon: enable tx recovery” which can evident in alert log.
Some time due to fast_start_parallel_rollback =high , lot of parallel sessions invoked and may complete hung your database or not permit any other session to work.
I came across similarly with same due to a large import got failed with undo 🙁 (more than 40gb import dump of 50gb table), this is in middle of the business day and hampering the system load peaking to 14,15, 18 of averages respectively.
Error in parallel slave trace:-
Parallel Transaction recovery coordinatorcaught exception 10388
So is there a way to disable the smon transaction recovery for sometime and continue later ? Continue reading SMON Transaction Recovery hanging up your database?
In RAC, you may all know about a TAF services which gives the failover capability to availability instances when the preferred failed. But how does this can work in when you have a Dataguard Environment with Primary and standby.
Well answer is until 10g/11gR1 create a trigger like below and manage the service based upon the instance role.
Creating a service
exec DBMS_SERVICE.CREATE_SERVICE(service_name =>’taf’,network_name =>’taf’,aq_ha_notifications =>true,failover_method =>’BASIC’,failover_type =>’SELECT’,failover_retries =>180,failover_delay => 5);
and have database level trigger to start service in case database role switch happens:
CREATE OR REPLACE TRIGGER set_rc_svc AFTER DB_ROLE_CHANGE ON DATABASE DECLARE
role VARCHAR(30);
Continue reading RAC Services: How do you manage TAF services in dataguard environment, manually? No more with 11gR2
Uh!!,
Yes should start with that Uh.. only. Let get back to one more post.
One of my databases having I/O error, actually its a VM Linux machine with 11.2.0.2, I have removed the Virtual disk forcibly, (renamed) which is having one datafile. Actually, want to test this new hidden parameter “_datafile_write_errors_crash_instance”, which exactly crashed my instances as expected, so thought of writing this post to make you all aware of it.
See below excerpt of my alert log, after simulating the virtual disk failure
Errors in file /u01/app/oracle/admin/test/bdump/test_dbw0_8888.trc:
ORA-01114: IO error writing block to file 6 (block # 4722)
ORA-01110: data file 6: '/u01/test/oradata/users01.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 5: Input/output error
Additional information: 3721
DBW0: terminating instance due to error 1243
Instance terminated by DBW0, pid = 9246
Before to 11.2.0.2 , when any data file got corrupted,or receives
Continue reading 11gR2 continued it surprises: Have you ever seen that data file I/O error cause Instance crash ? 11.2.0.2 does it…
Very recently I have encountered this issue while cataloging the backup pieces in order to sync the standby server.
Well this is all about that database DBID is different from the source (primary) database where you cannot/rman cannot see those backups relevant to this destination database. very obvious thing.
This db in the issue has been built long back and in sync, whilst I was doing a roll forward standby recovery and trying to catalog found this issue.
Now, how to change the DBID to the desired or the original one,
Continue reading RMAN-07518: Reason: Foreign database file DBID: 18023747 Database Name: TEST
We all know that sys password is required and should be same on both the primary and standby databases, and many times during switchover, failover we encountered, ORA-01017: invalid username/password; logon denied, due to password mismatch etc.
From 11g Onwards, a new parameter REDO_TRANSPORT_USER has been introduced to overcome this issue,
Continue reading Dataguard 11g: Log shipping – No more sys password hassles
ADRCI: Automatic diagnostic repository command interface, used to manage the diagnostic, health monitor, or incident packaging solutions for Oracle databases.
This is the small adrci automatic purge script rather to rely on traditional log purging or housekeeping scripts.
This script will check in /etc/oratab and list down all oracle homes and then set those Oracle ADR bases and clean up the traces/incidents/logs etc.
Download Script here:- ADRCI_Purge.sh
Hope this helps
-Thanks
Geek DBA
Housekeeping of logs , traces, diag directories is every DBA’s job and some may write their own , some adopt from others which is common.
Often we found in our environments similar sort of scripts placed, which I found mostly were
Non Pluggable to any new environments
- You cannot port it to others or outside since there is dependency of some other functions etc
- Not easy to manage or scale if you want to add more
- Not easier to understand even.
Trust me I came across at least more than five environments which are having either too big complex scripts or none.
For those, who want a simple way of achieving this , I suggest to read this document and place in your environment, of course with
adrci ( I will add that one too soon)
this is not use any more.
Download:- Housekeeping Shell Script
Hope this helps.
Thanks
Geek DBA
As the data grows in relational databases (new business requirements, managing audit data etc), the cost associated with the disk systems to store that data and the resources required to manage them becomes vital.
Hence it is necessary to adopt techniques like compression, de-duplication etc to reduce the storage costs.
Lets go one by one of compression techniques and usage, I will be discussing the de-duplication in length in another post.
Types of Compression
|
Physical Compression
|
Logical Compression
|
External Compression
|
|
Hardware Assisted
Block/Page Level
|
Segment Level
Row Level
TDE
Binary Compression
|
Use custom algorithm for ex: CLEMCOM etc for mainframes
|
What is compression for Any RDBMS?
Continue reading Oracle Compression: Concepts & Usage
Yesterday My colleague has encountered the following issue, which is interesting.
Environment:-
1) Linux & 10g Database
2) Oracle Block size =8192
3) OS Block/Page size = 4096
Issue:- ORA-01200: actual file size of X is smaller than correct size of Y
Verification:-
1) Database was not opening (non system datafile)
2) DBV says no corruption
Continue reading ORA-01200: actual file size of X is smaller than correct size of Y
|
Oracle 23c New Features for DBA's
Follow Me!!!