It was very interesting when I have received an activity to ADD a SCAN listener to the existing RAC 11gR2 setup. As an Oracle DBA each one of us might see such activity in the projects with RAC setup. It is not quite very easy to add a SCAN listener when your environment doesn’t use […]
I had to take a datapump transportable tablepsace backup, but there was a scheduled RMAN job which is not noticed. When these two jobs clashed check how did the affect is. Scenario: EXPDP transportable tablespace backup was initiated at 17:30 hoursRMAN backup was scheduled to run at 17:00 hours.By the moment EXPDP transportable tablespace backup […]
I have set of tables to be excluded with the name AA_* but to include one table AA_SYNTAX in the datapump export on Oracle 11g R2 database. I have found something very strange when I tried using the following syntax with EXPDP. I have executed few test cases to check which one will work for […]
Did you guys ever performed RMAN DUPLICATION from a production backup which has block change tracking (BCT) enabled ? I had to duplicate only one application tablespace to an AUXIL instance on test server from the backups of production. I used RMAN script as below: This RMAN DUPLICATION will do a successful restoration and fails […]
1. LOGON trigger to restrict user connections filtering USERNAME and HOST: 2. LOGON trigger to enable session tracing: 3. Trigger to capture DDL statements: 4. Trigger to capture on SERVERERROR: 5. Trigger on server shutdown: Hope these examples will help you to build triggers based on your project requirements
1. UNDO usage on the database might hit the water mark making the job to administer it tedious.Monitor who is using UNDO: 2. Script to estimate the amount of UNDO tablespace size required for the database 3. Estimate the amount of time required for a transaction to use UNDO:
Let us see how to set the listener password step wise. 1. Check the current status of the listener. 2. Security is ON but for Local OS Authentication. Now let us check the listener.ora file: 3. Now Stop the listener and configure password to the listener. 4. Let us verify the listener functionality with password: […]
Oracle strongly recommends that you run your database in automatic undo mode by leavingthe UNDO_MANAGEMENT initialization parameter set to AUTO, which is the default. In addition,set the UNDO_RETENTION initialization parameter to an interval large enough to include theoldest data you anticipate needing. For more information refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTIONinitialization parameters […]
1. Identify who is using TEMP on Oracle database: 2. To monitor temp usage on the database(This query do not include AUTO EXTENDED size):
There are multiple ways to capture blocking sessions in oracle database. 1. A full query to get list of blocking sessions with BLOCKER, WAITER and OBJECT being blocked:It works on Single Instance and RAC set-up as well. 2. A simple query to find out blocking in the database: 3. To get limited information on blocking […]
