Category: Database Maintenance

Sep 18 2020

After Revoked Tablespace Quota From User in Oracle

In topic, I will descript how Oracle handle once we revoke tablespace quota from user. Once blocks assigned to a segment, even after revoked the Tablespace quota and deleted records from table, all allocated block will remain mapping with segment until we released block by using Shrink. This is the one good reason to release …

Continue reading

Sep 02 2020

Unused or Drop Column? Which option should use?

Sometimes DBA got request to drop the table’s columns. Oracle has following options to complete this task. Logical Delete. Physical Delete. Claim space for logical delete columns. Claim space for Physical delete column. Conclusion. In this article, I will discuss which option is more appropriate for big size of table. For small table, even physical …

Continue reading

Jun 13 2020

Steps for PSU (Patch Set Update) Patching on RAC with Data Guard

Applying patch is one of the important task for DBA. I will discuss here how to apply PSU patch on your RAC database including DR for 12c and 11g. Patch Set Update: A collection of proactive, stabilizing cumulative patches for a particular product version (base release or patch set). PSUs are cummulative and include all …

Continue reading

Jun 10 2020

Change Archive Log Mode in Oracle Database

In this topic, I will show how to change Archive Log Mode in Oracle Database. Enable Archive Log Mode in RAC: Archive Log Status:

Continue reading

Mar 24 2020

Control System Privileges (Truncate or Drop) in Oracle Database

There are two of privileges are available in Oracle. Object Privileges: Object privileges can be granted as Object Level. Example, You will able to provide delete privileges for particular table. User will able to delete records from that particular table only. System Privileges: System Privileges are very powerful privileges as you can’t grant at object …

Continue reading

Mar 02 2020

Permission changed for all directories including files in Oracle Home (RAC)

Sometime user (os admin or dba) make mistake to change or provide the permission in Oracle Binary. User might requested for changing the particular file or directory permission but admin might run with recursively. Example: To change owner for patch directory (/u01/patch) including all files on patch. But by mistake run below command

Continue reading

Mar 01 2020

Move or Rename Oracle Files

DBA needs to rename or move oracle file from one filesystem or ASM DISKGroup to another. Move Datafiles Using ALTER DATABASE MOVE DATAFILE command – 12c and onward Moving Datafile Using RMAN with offline option – 10g or 11g Prerequisites: Database should be configured archive log mode.

Continue reading

Feb 01 2020

Create Duplicate Or Refresh the Database Using RMAN Backup on RAC

Create duplicate database or refresh the database from production backup for testing purpose is one of the regular activities for DBA. I will discuss and show how to create duplicate database using RMAN backup. Scenario: Production database running on two nodes RAC and need to refresh the database for testing purpose on testing environment. Production …

Continue reading

Jan 06 2020

Move Objects From One Tablespace to Another Tablespace in Oracle

Sometimes need to move objects from one Tablespace to another in Oracle Databases. We can do this multiple ways. Move a single object from Multiple Tablespace (one TBS for DATA, another for Index and LOB) to different set of Tablespace Move all objects of a schema from Multiple Tablespace (one TBS for DATA, another for …

Continue reading

Jan 05 2020

Tips to Avoid Mistake and Be a Safer DBA

There is around 33% disaster occurs on database world for user error/mistake. If you follow couple of rules, you will able to overcome lots of possible mistake.

Continue reading