Search This Blog

Oracle DBA (12c/19c) Articles and Scripts

This blog is intended to share my knowledge on Oracle various scripts that I use as an Oracle DBA

Oracle Database Startup Stages

Get link
Facebook
X
Pinterest
Email
Other Apps

- September 15, 2019

check the current status of the database instance by querying the v$instance view:

select instance_name,status from v$instance;

Get link
Facebook
X
Pinterest
Email
Other Apps

Comments

Register Archive log file manually in standby Database

- December 30, 2020

Some time, the archive logs are not shipping to standby location due to network issue. There may be several archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100. In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step 2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc'; Step 3 Verify th...

Step by Step: How to troubleshoot a slow running query in Oracle

- September 17, 2019

This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the ...

Using RMAN Effectively In A Dataguard Environment

- December 21, 2020

Data Guard enables and automates the management of a disaster recovery solution for Oracle databases located on the same campus or across the continent. Data Guard consists of a production database (also known as the primary database) and one or more standby database(s), which are transactionally consistent copies of the production database. RMAN is a tool integrated with the Oracle Database that satisfies the demands of high performance, manageable backup and recovery. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and compression, and operates with leading backup software systems via the supplied Media Management Library (MML) API. RMAN brings rich functionality such as online backups, incremental backups, block media recovery, automation of backup management tasks, and integration with 3rd party media management systems int...