Tuesday, May 17, 2016

Calculate UNDO_RETENTION for given UNDO Tabespace

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention = 
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC
Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Top 99 Responsibilities of a DBA

Database Architecture Duties


1. Planning for the database's future storage requirements
2. Defining database availability and fault management architecture
3. Defining and creating environments for development and new release installation
4. Creating physical database storage structures after developers have designed an application
5. Constructing the database
6. Determining and setting the size and physical locations of datafiles
7. Evaluating new hardware and software purchase
8. Researching, testing, and recommending tools for Oracle development, modeling, database administration, and backup and recovery implementation, as well as planning for the future
9. Providing database design and implementation
10. Understanding and employing the optimal flexible architecture to ease administration, allow flexibility in managing I/O, and to increase the capability to scale the system
11. Working with application developers to determine and define proper partitioning


Backup and Recovery


12. Determining and implementing the backup/recovery plan for each database while in development and as the application moves through test and onto production
13. Establishing and maintaining sound backup and recovery policies and procedures
14. Having knowledge and practice of Oracle backup and recovery scenarios
15. Performing Oracle cold backups when the database is shut down to ensure consistency of the data
16. Performing Oracle hot backups while the database is operational
17. Performing Oracle import/export as a method of recovering data or individual objects
18. Providing retention of data to satisfy legal responsibilities of the company
19. Restoring database services for disaster recovery
20. Recovering the database in the event of a hardware or software failure
21. Using partitioning and transportable tablespaces to reduce downtime, when appropriate

Maintenance and Daily Tasks


22. Providing adjustment and configuration management of INIT.ORA
23. Adjusting extent size of rapidly growing tables and indexes
24. Administering database-management software and related utilities
25. Automating database startup and shutdown
26. Automating repetitive operations
27. Determining and setting critical thresholds for disk, tablespaces, extents, and fragmentation
28. Enrolling new users while maintaining system security
29. Filtering database alarm and alert information
30. Installing, configuring, and upgrading Oracle server software and related products installation
31. Logging Technical Action Reports (TARs); applying patches
32. Maintaining the "Database Administrator's Handbook"
33. Maintaining an ongoing configuration for database links to other databases
34. Maintaining archived Oracle data
35. Managing contractual agreements with providers of database-management software
36. Managing service level agreements with Oracle consultants or vendors
37. Monitoring and advising management on licensing issues while ensuring compliance with Oracle license agreements
38. Monitoring and coordinating the update of the database recovery plan with the site's disaster recovery plan
39. Monitoring and optimizing the performance of the database
40. Monitoring rollback segment and temporary tablespace use
41. Monitoring the status of database instances
42. Performing housekeeping tasks as required; purging old files from the Oracle database
43. Performing database troubleshooting
44. Performing modifications of the database structure from information provided by application developers
45. Performing monthly and annual performance reports for trend analysis and capacity planning
46. Installing new and maintaining existing client configurations
47. Performing ongoing configuration management
48. Performing ongoing Oracle security management
49. Performing routine audits of user and developer accounts
50. Performing translation of developer modeled designs for managing data into physical implementation
51. Performing correlation of database errors, alerts, and events
52. Planning and coordinating the testing of the new database, software, and application releases
53. Providing a focal point on calls to Oracle for technical support
54. Working as part of a team and providing 24x7 support when required


Methodology and Business Process


55. Coordinating and executing database upgrades
56. Coordinating upgrades of system software products to resolve any Oracle/operating system issues/conflicts
57. Creating error and alert processes and procedures
58. Creating standard entry formats for SQLNet files
59. Creating processes and procedures for functional and stress testing of database applications
60. Creating processes and procedures of application transport from DEV, to TEST, to PROD
61. Defining and maintaining database standards for the organization to ensure consistency in database creation
62. Defining database standards and procedures to cover the instance parameters, object sizing, storage, and naming. The procedures define the process for install/upgrade, corporate database requirements, security, backup/recovery, applications environment, source code control, change control, naming conventions, and table/index creation.
63. Defining the database service levels necessary for application availability
64. Defining methodology tasks for database software integration
65. Defining a methodology for developing and improving business applications
66. Creating a process to determine whether a new release is "stable" enough to be placed on the development system
67. Developing data-conversion processes for customization, testing, and production
68. Developing database test plans
69. Developing database administration procedures and responsibilities for production systems
70. Developing production migration procedures
71. Establishing and providing schema definitions, as well as tablespace, table, constraint, trigger, package, procedure, and index naming conventions
72. Facilitating design sessions for requirements gathering and defining system requirements
73. Providing database problem reporting, management, and resolution
74. Providing final approval for all technical architecture components that manage and exchange data, including database management software, serve hardware, data distribution management software, server hardware, data distribution management software, transaction processing monitors, and connecting client applications software
75. Providing processes for the setup of new database environments
76. Providing risk and impact analysis of maintenance or new releases of code
77. Providing standards and methods for database software purchasing
78. Providing standards and naming conventions
79. Handling multiple projects and deadlines

Education and Training


80. Attending training classes and user group conferences
81. Evaluating Oracle features and Oracle-related products
82. Understanding the Oracle database, related utilities, and tools
83. Understanding the underlying operating system as well as the design of the physical database
84. Understanding Oracle data integrity
85. Knowing the organization's applications and how they map to the business requirements
86. Knowing how Oracle acquires and manages resources
87. Knowing enough about the Oracle tool's normal functional behavior to be able to determine whether a problem lies with the tool or the database
88. Processing sound knowledge in database and system performance tuning
89. Providing in-house technical consulting and training
90. Staying abreast of the most current release of Oracle software and compatibility issues
91. Subscribing to database trade journals and web sources


Communication


92. Interfacing with vendors
93. Disseminating Oracle information to the developers, users, and staff
94. Training application developers to understand and use Oracle concepts, techniques, and tools that model and access managed data
95. Assisting developers with database design issues and problem resolutions, including how to run and understand the output from both TKProf and the Explain Plan utilities
96. Training interim DBAs and junior-level DBAs


Documentation


97. Creating and maintaining a database operations handbook for frequently performed tasks
98. Defining standards for database documentation
99. Creating documentation of the database environment

Source - http://appsdbaportal.blogspot.com/2009/04/top-99-responsibilities-of-dba.html

Meaning of Oracle “I” “G” and “C”

Meaning of “I” and “G” in Oracle and the Release format number of version

Meaning of I in Oracle:

The Oracle version starting of I. The starting in 1999 with version 6i, 8i and 9i, I signify “Internet” means stands for “Internet” and Oracle added the “I” to the version name to reflect support for the Internet with its built-in Java Virtual Machine (JVM). Oracle 9i added more support for XML in 2001.

Meaning of G in Oracle:

The starting in 2003 with version 10g and 11g, G signifies “Grid Computing” with the release of Oracle10g in 2003. Oracle 10g was introduced with emphasis on the “g” for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. Upgrade Enterprise Manager 10g Grid Control Release 4 (10.2.0.4.0) or higher to Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0).

Meaning of C in Oracle:

In the Oracle Database 12c the "c" stands for "cloud". In addition to many new features, this new version of the Oracle Database implements a multitenant architecture, which enables the creation of pluggable databases (PDBs) in a multitenant container database (CDB). The Oracle Database 12c is a high-performance, enterprise-class database. Oracle released Oracle Database 12c into general availability July 1, 2013. According to Oracle, this is "the first database designed for the cloud." Oracle Database 12c also introduces 500 new features to the database, most notably pluggable databases and multitenant architecture. The Oracle Database 12c release 12.0.1.2 also features the Oracle Database 12c In-Memory, an optional add-on that provides in-memory capabilities. The in-memory option makes Oracle Database 12c the first Oracle database to offer real-time analytics.

Thursday, April 28, 2016

How to find failed login attempts??

Enable auditing for failed login attempts
-- # 1 - Modifiy initialization parameters
alter system set audit_trail=db scope=spfile;
-- # 2 - Restart the database 
shutdown immediate
startup
-- # 3 - Enable auditing for failed login attempts
audit create session whenever not successful;
-- # 4 - Query the dba_audit_trail view to see failed login attempts 
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Monday, April 25, 2016

ORA-00020: maximum number of processes (5000) exceeded

Symptom
After an ORA-00020 "maximum number of processes (%s) exceeded" occurs ... no new connections (even SYSDBA or SYSOPER) may be made to the instance.

Solution
An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter 
There are a couple of 'workarounds' for this issue
WORKAROUNDS:
UNIX: kill -9 one or more of the client connections 
WINDOWS : using ORAKILL ... kill one or more of the client connections 
* Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and 
         alter system kill session 'SID, SERAL#';
In my case I don't have any active sessions with sufficient privileges
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to generate traces.
You can shut abort and then restart your database, instead of rebooting your server.
This is the ultimate chance before reboot. Before doing this, consider disconnecting or killing some user session to get a regular sqlplus / as sysdba.

Wednesday, April 13, 2016

How to find all child tables of a table in Oracle??

Sometimes it’s useful to find out all the dependent/child tables for a particular table. The SQL statement below will work for an Oracle database:

SELECT
a.owner,a.table_name, a.constraint_name
FROM
sys.all_constraints a,
(SELECT owner,constraint_name from sys.all_constraints
WHERE
owner = 'OWNER' and
table_name = 'TABLE_NAME' and
constraint_type in ('P','U')
) b
WHERE
a.constraint_type = 'R' and
a.r_constraint_name = b.constraint_name and
a.r_owner = b.owner

Where  OWNER is the owner/schema to which the table belongs andTABLE_NAME is the table to be reported on.

Sunday, April 3, 2016

HUNG IN AUTO SQL TUNING TASK

Issue-
In 11.2.0.3 database , we get alert in EM related to metric “Generic Operational Error” or "Generic Operational Error Status ". Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:
Error-
These messages indicate that an auto kill of a "hung"/long running tuning task has taken place.

This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects a the system from harm caused by such over-running.

Since this is an expected activity to prevent over-running there is no fix as such.  
Instead as a workaround, you could:
FIX 1 :Give the task more time to complete (the following example would set the per statement timeout to 6 hours (21600 seconds)):
BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600);
 END;

/
Note
If you increase the per-statement time limit (LOCAL_TIME_LIMIT) then you need to stay within the bounds of the time limit for the entire task (TIME_LIMIT). The duration of the TIME_LIMIT parameter must be at least equal or greater than the LOCAL_TIME_LIMIT. When the maintenance window closes the SQL Tuning Advisor is stopped.
FIX 2 :
Disable the automatic tuning process and the messages will not appear anymore (though obviously the auto tuning will also no longer occur - you could manually execute the job as desired later). 
To disable the job:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable in future:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

/
Source : 
How to Avoid or Prevent "Process 0x%p appears to be hung in Auto SQL Tuning task" Messages (Doc ID 1344499.1)

Thursday, March 31, 2016

How to get PASSWORD changed time for an Oracle user??

SELECT name, ctime, ptime
FROM sys.user$
WHERE name = 'TEST_SUH';

How to check if your Oracle client is 32 bit or 64 bit?

SOLUTION 1:
If logged into SQL Plus, the banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit (even though it does not explicitly say so).
SOLUTION 2:
You can specifically query the info by running the following:
SELECT * FROM V$VERSION;
The banner will tell you 64-bit if the 64-bit version is installed. If it does not specify 64-bit then it is 32-bit.
SOLUTION 3:
Run the query to check the following:
select distinct address from v$sql where rownum<2;
If the address returned is 16 characters long, it is 64 bit.
If it is 32 bit you will get an 8 character address.
SOLUTION 4:
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit. If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
*Headsup: In newer versions of the client, the library is not included and this directory may not exist.

How to find total number of Oracle schema objects and its size??

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

How to run SQL Tuning Adviser manually??

Step 1: Replace SQL_ID and TASK_NAME (Unique):
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'XXXXX',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1800,
                          task_name   => 'XXXXX_A',
                          description => 'Tuning task for statement 8p9qh5xwgc3fx_B in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Step 2: Validate if SQL Tuning Task is created or not (Status should be INITIAL):
select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;
Step 3: Run the Advisor
EXEC DBMS_SQLTUNE.execute_tuning_task('XXXXX_A');
Step 4: Check the status in other session using command below: (It should show EXECUTING)
select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;
Step 5: Once the Advisor task is completed. Generate report using command below:
SELECT DBMS_SQLTUNE.report_tuning_task(‘XXXXX_A') AS recommendations FROM dual;
Step 6: Analyze the report and implement the solution if necessary.
Source: Thanks Arbind!!!

How to configure RMAN??

Here i am using ORCL as primary database and CATDB as catalog database.
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.
Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?
Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.
Step # 4: Startup the database in mount state.
 Step # 5: Configure database in ARCHIVELOG mode.
Step # 6: Alter database to open state.
Step # 7: Ensure ARCHIVELOG destination.




Step # 8: Connect to RMAN prompt with target database.
Step # 9: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.

Step # 10: To enable backup optimization run the following command, by default backup optimization has been configured OFF.
Step # 11: Configure retention policy for backup.
Step # 12: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects. Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.
Step # 13: Connect to RMAN on target and recovery catalog database. Create catalog by issuing the following command in RMAN prompt. And then register database with recovery catalog
Step # 14: Check whether registration was successful.



Friday, March 18, 2016

Oracle Support: Coverage for Oracle Database Releases

Extended Support Fee for Oracle 11.2.0.4 waived until May 31, 2017 - Extended Support until Dec 2020




Saturday, March 12, 2016

How to Prevent a User to Drop Own Objects???

CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'VIEW' 
AND ora_dict_obj_owner = 'QO112_1616' 
AND ora_login_user = 'QO112_1616' 
AND ora_dict_obj_name='SAMPLE_NEW'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP SAMPLE_NEW VIEW!');
END IF;
END;

We will get an erorr message when trying to delete object in my case VIEW

Friday, March 11, 2016

Error Starting WebLogic Admin Server - Could not reserve enough space for object heap

The first time that I ran may WebLogic admin Server, I encountered the following error:
Could not reserve enough space for object heap.
I resolved this by creating a new environment variable called EXTRA_JAVA_PROPERTIES with the "-Xms512m -Xmx251m"
Try starting WebLogic server, it should start!!!!

Tuesday, March 8, 2016

How to create ODI Domain - Windows???

Configuration - Create ODI Domain
Execute config.cmd as shown below
 Select "Create a new domain" and choose the location, Click "Next"
Choose following templates from available templates, Click "Next"

  •  Basic WebLogic Server Domain – 12.2.1 [wlserver]*
  • Oracle Enterprise Manager Plugin for ODI – 12.2.1 [em]
  • Oracle Enterprise Manager – 12.2.1 [em]
  • Oracle Data Integrator – Agent – 12.2.1 [odi]
  • Oracle Data Integrator – Agent Libraries – 12.2.1 [odi]
  • Oracle Data Integrator SDK Shared Library Template – 12.2.1 [odi]
  • Oracle Data Integrator – Console – 12.2.1 [odi]
  • Oracle JRF – 12.2.1 [oracle_common]
  • WebLogic Coherence Cluster Extension – 12.2.1 [wlserver], Click "Next"
Enter Application Location,  Click "Next"
 Create Administrator Account, Click "Next"
Choose "Production Mode", Click "Next"
Provide database connection information for schema <Prefix>_STB (Configured via Repository Creation Utility - RCU), Click "Get RCU Configuration" you should see result log as below, Click "Next"
Check database connection information (Schema, password, database service, listener port and hostname) - You should see this information while creating repository using RCU, Click "Next"
You should see next screen as below, Click "Next"
Give SUPERVISOR username and password (you should get this while creating Repository using RCU), Click "Next"

Configure Admin Server, Click "Next"
Configure Node Manager, Click "Next"
Configure ODI Manager Server, Click "Next"
 Click "Next", Configuration without Cluster
Default value and Click "Next"
Click "Add" and Configure Unix Machine, Click "Next"
 Add AdminServer and ODI_Server1 to the Unix machine, Click "Next"
Check Configuration Summary and Click "Create" 

Domain Creation is completed!!!