Oracle 10g’s brilliant alternative to database point in time recovery is the the Flashback Database feature. With this feature in place you can do almost everything that you can with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily entails.I recently had a first hand opportunity to see the power of this feature, when I ran a scriptfile to drop tables and unwittingly dropped one of the tables containing sensitive information belonging to my employer Creative Infotech. I later recovered the table and was amazed at seeing how easy it had become to get back dropped objects in Oracle 10g, especially Release 2. Below is a simplified version of what I did
Advanced features of Oracle Net include failover and load balancing. These features are actually interrelated in as much as you usually don’t have one without the other. While they are mostly used in a RAC environment, they can be set up in a single instance environment as well.
In the context of Oracle Net, failover refers to the mechanism of switching over to an alternate resource when connection to the primary resource gets terminated due to any reason. Connection failure may be broadly categorized as:
Once upon a time Le Petit Chaperon Rouge (LPCR) decided to change db_files from 200 to 300 on a busy cluster of four nodes.
Soon after the change applications started to block each other, timeout and crash (enq: TX - contention, cursor: pin S wait on X, enq: HW - contention). Those applications which were actively inserting into LOB columns - suffered the most.
Many lock holders were waiting for "DFS lock handle". Thus LPCR learned that DFS stands for "Distributed File System".
As soon as she reversed the changes - everything went back to normal.
Recently my friends at work saw me doing things with indexes they didn't understand. They saw what looked like unneeded columns in some indexes, and funny expressions they didn't know could be done in an index. So I explained to them the benefits of INDEX ONLY ACCESS, and FUNCTION BASED INDEXES. They had not seen these two techniques before so I figured maybe other people would be interested too. Here is it, a short discussion on what are fast becoming my two favorite tuning techniques.
To continue with the shared server series, as mentioned in my last post, I will here be taking up the issues of configuration of memory, and the management of shared servers using available data dictionary views and initialization parameters.
How much memory should you configure for shared servers?
So, to continue with our shared server series, let's begin with how a user connects through a shared server connection:
This is the first of my three-part series dealing with Shared servers: Today an organization can have hundreds or thousands of users connecting to their databases. The traditional dedicated server model maps each user to a dedicated process running on the database server. Each dedicated server process requires memory and as the number of users increase, there is a linear increase in the demand for memory.
The Redo Logfile Size Advisor can be used to determine the least optimal online redo log file size based on the current FAST_START_MTTR_TARGET setting and MTTR statistics.
Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.
The Advisor provides a recommended optimal smallest online redo log file size.
we can then adjust the online redo log file size to the recommended optimal size ,eliminating the need to manually determine an optimal size.
To use the advisor we need to query the V$INSTANCE_RECOVERY VIEW.
If our redo log file size is under sized then the checkpoint process is driven by WRITES_LOGFILE_SIZE i.e. its driven by the smallest redo log file size.
Automatic Work Load Management
Application workloads can be defined as services so that they can be individually managed and controlled.
We can control and monitor which processing resources are allocated to each service during both normal operations and in response to failures.
Performance metrics are tracked by service and thresholds set to automatically generate alerts should these thresholds be crossed.
CPU resource allocations and resource consumption controls are managed for services using Resource Manager.
we deifne rules to automatically allocate processing resources to services. Oracle RAC 10g instances can be allocated to process individual services or multiple services as needed.
When you setup statspack with level 6 you can have information on the sql_plan associated with a sql. (statpack on oracle db version >=9i)