Vignesh D – Sharing Real-World Oracle DBA & Cloud Insights

Monday, July 14, 2025

Roles and responsibilities of an Oracle DBA (Database Administrator)

Roles and responsibilities of an Oracle DBA (Database Administrator)

The roles and responsibilities of an Oracle DBA (Database Administrator) are extensive and varied, encompassing a wide range of technical and strategic tasks. Here is a detailed breakdown of their roles and responsibilities:

Installation and Configuration

· Database Software Installation: Installing Oracle database software on various operating systems.

· Configuration: Configuring databases for specific applications and business requirements, including setting up parameters and options.

Database Design and Architecture

· Schema Design: Designing database schemas, tables, indexes, views, sequences, and other database objects.

· Data Modeling: Working with data models and converting logical models into physical database designs.

Performance Tuning

· Query Optimization: Optimizing SQL queries for better performance.

· Resource Management: Managing system resources such as CPU, memory, and disk I/O to ensure optimal database performance.

· Performance Monitoring: Using Oracle tools like AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), and OEM (Oracle Enterprise Manager) to monitor and tune performance.

Backup and Recovery

· Backup Strategies: Developing and implementing backup strategies using RMAN (Recovery Manager) or other tools.

· Recovery Planning: Planning and executing database recovery procedures in case of failures or data corruption.

· Disaster Recovery: Setting up and managing disaster recovery solutions and ensuring data availability.

Security Management

· User Management: Creating and managing database users and roles, setting up permissions and access controls.

· Data Encryption: Implementing data encryption and other security measures to protect sensitive information.

· Compliance: Ensuring database security policies comply with regulatory requirements and standards.

Monitoring and Maintenance

· Routine Maintenance: Performing regular maintenance tasks such as database cleanup, reorganization, and updates.

· Health Checks: Conducting regular health checks to ensure the database is functioning properly.

· Patch Management: Applying patches and updates to the Oracle database software.

Troubleshooting

· Issue Diagnosis: Identifying and resolving database-related issues such as performance bottlenecks, connectivity problems, and data corruption.

· Log Analysis: Analyzing database logs to troubleshoot and diagnose issues.

Data Migration

· Data Transfer: Moving data between different databases or environments, often during upgrades or consolidations.

· Data Transformation: Transforming data as needed during migration processes.

Automation

· Scripting: Writing scripts to automate routine tasks such as backups, monitoring, and reporting.

· Tool Utilization: Utilizing Oracle tools and third-party software to streamline database management tasks.

Compliance and Auditing

· Audit Trails: Setting up and maintaining audit trails to track database activity.

· Regulatory Compliance: Ensuring the database environment meets regulatory and compliance standards.

Collaboration and Communication

· Cross-Functional Collaboration: Working closely with developers, system administrators, network administrators, and other stakeholders.

· Documentation: Maintaining detailed documentation of database configurations, procedures, and standards.

· Training and Support: Providing training and support to other team members and end-users.

Continuous Improvement

· Skill Development: Keeping up-to-date with the latest Oracle technologies and best practices.

· Process Improvement: Continuously seeking ways to improve database performance, reliability, and efficiency.

Oracle DBAs play a critical role in ensuring that an organization’s databases are secure, efficient, and reliable, supporting business applications and data-driven decision-making processes.

User Management in MYSQL

User Management in MYSQL

Introduction:-

Effective user management is a critical aspect of MySQL database administration. Creating users and assigning access in MySQL involves creating, configuring, and maintaining user accounts to control access and permissions within the MySQL database system.

1. Connect to MySQL Server:

Open a command prompt or terminal.

Use the mysql command to connect to the MySQL server:

mysql -u root –p

Replace root with your MySQL username if you're not using the root user.

2. Create a New User:

To create a new user, use the CREATE USER statement:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace 'username' with the desired username, 'localhost' with the hostname or IP address of the user (e.g., '%' for any host), and 'password' with the user's password.

3. Grant Privileges:

To grant privileges to the user, use the GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON testnewone.* TO 'vijay1'@'localhost';

Replace 'database' with the name of the database to grant access to and adjust the list of privileges as needed.

Alternatively, to grant all privileges:

GRANT ALL PRIVILEGES ON testnewone.* TO 'vijay1'@'localhost';

4. Flush Privileges:

After granting privileges, use the FLUSH PRIVILEGES statement to apply the changes:

FLUSH PRIVILEGES;

5. View User Privileges:

To view the privileges assigned to a user, you can query the mysql.user table:

SELECT user, host, authentication_string FROM mysql.user WHERE user = vijay1;

6. Exit MySQL:

After creating the user and assigning privileges, exit the MySQL shell:

EXIT;

Additional Considerations:

Wildcard Host ('%'):

If you want to allow the user to connect from any host, use '%' instead of a specific hostname or IP address in the CREATE USER statement.

Changing Password:

To change the password for a user, use the ALTER USER statement:

ALTER USER 'vijay1'@'localhost' IDENTIFIED BY 'vijay2';

Revoking Privileges:

To revoke privileges, use the REVOKE statement:

REVOKE ALL PRIVILEGES ON testnewone.* FROM 'vijay1'@'localhost';

Deleting User:

To delete a user, use the DROP USER statement:

DROP USER 'vijay1'@'localhost';"

Conclusion:-

User management plays important role in MYSQL database management system.By understanding the principles of creating users, granting appropriate privileges, and managing authentication methods, database administrators can ensure the integrity and security of their MySQL databases.

Oracle Undo Tablespace management

UNDO TABLESPACE

USES:

· Rollback transactions.

· Recover the database.

· Provide read consistency.

How to monitor Undo Tablespace Usage:

In UNDO tablespace once we allocate the size, if that in currently used we cannot use the space in other process

To check the current size of the Undo tablespace:

Find undo tablespace current size in dba_tablespace view check the status as ONLINE to get current usage

SQL> select sum(a.bytes) as 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_SIZE

----------

775946240

To check the free space (unallocated) space within Undo tablespace:

The dba_free_space we will find tablespace free size mention the tablespace name to get free size of the tablesapce.

SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

----------

718.4375

To Check the space available within the allocated Undo tablespace:

Undo tablespace allocated with the database to find that free space available in that use the dba_undo_segments and mention the status as expired to get available space in allocated undo tablesapce

SQL> select tablespace_name, sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;

TABLESPACE_NAME REUSABLE_SPACE

------------------------------ --------------

UNDOTBS1 8.25

To Check the space allocated in the Undo tablespace:

Check currently space can be used by undo tablespace check the extents sattus as active and unexpired to get the used size

SQL> select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;

TABLESPACE_NAME SPACE_IN_USE

------------------------------ ------------

UNDOTBS1 12.3125

Find over all undo tablespace status

Following script used to find overall undo tablesapce size usage and free space

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME SIZEMB USAGEMB FREEMB

------------------------------ ---------- ---------- ----------

UNDOTBS1 740 12.3125 727.6875

SQL>

Check undo usage by User or schema

select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts
from dba_undo_extents u, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;

If undo tablesapce 100 % full ?

Undo tablespace 100 % full we cannot resize the table space size or we cannot add datafile to the tablespace if we add that file size also shown as 100 % full.

We create new tablespace then we assign the tablespace to default undo tablesapce

Once the process will be completed it will release the memory . if we also remove the used tablespace.