Showing posts with label Frequently Asked. Show all posts

Friday 10 May 2024

Rediscovering my Top Viewed Blog Posts 😉😍

Hello Readers,

What to do if listener log has grown too big in size

Oracle Datapump Scenarios | Interview Questions

Follow us on Facebook !!

ASM Interview Questions | PDF Download

20 + ASM Interview Questions | Real-time Scenarios

ASM Interview Questions

How to Crack DBA Interviews | Tips

How to prepare for Interview | Points

General Tips for Job Interview | Situations

Frequently Asked Questions for 2+ Years

Interview Questions for 1+ Years Candidates

General DBA Interview Questions | Quicky

30+ Oracle Dataguard Questions | PDF Download

Oracle Performance Tuning Interview Questions - Updating Soon..!!

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here

DBA Job Updates @ My Facebook Page

Friday 25 August 2017

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Many a times, When user try connecting to the database though an application, user gets the error as below.

ORA-12505: TNS: listener does not currently know of SID given in connect descriptor

Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

This is very comman issue, and there are many reasons for this error.

Cause: User was unable to connect to database through connect descriptor that we use to connect to database from remote server or from application.

Solution:

Since listener is running,try to connect the database manually through connect descriptor that is usually stored in tnsnames.ora file. Always make sure that connect descriptor has correct database name, hostname and port name.

Example:

sqlplus usrname/pwd@PRODDB

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 20 04:39:03 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
Descriptor

Ø Then check your database name in connect descriptor available at tnsnames.ora file.

Ø If that is correct then check your services in listener to find whether requested database is being listened by the listener. You can use below command to check the same.

$lsnrctl services listener_name

Ø If database is found, then wait for few seconds and check the connection.

Ø If database is not found in the services, then manually register your database to the listener by using below command.

SQL> alter system register;

Check after few seconds, you will be able to connect to the database using connect descriptor.

If still not working, check the connection string of the database a provide the connection string to the user, to make sure the same string is used by users.

Also, check the mount point of the host. Sometimes the iNodes get occupied on the host, which make the listener state as hung and will not allow users to connect. If so, perform necessary housekeeping on the respective mount-points and check the listener Status again.

Happy Learning :)

Keep Sharing . . .

Wednesday 19 April 2017

20 ASM Realtime | Interview Questions | 3+ years

Download link : https://www.scribd.com/document/461028011/Asm-Interview-Scenarios-Oracle-dba-Help-Blog

Happy Learning ..!

Saturday 17 September 2016

What a Junior DBA will do, When he Faces “Slowness in the Database” ?

Many a times, a DBA gets a call from Client side regarding the slowness of the Database.

Datapump Architecture. What is Master table in Datapump ?

Master Table :

The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks of lots of detailed information.

The Master Table is used to track the detailed progress information of a Data Pump job.

This will store the following information :

· The status of every worker process involved in the operation.

· The current set of dump files involved.

· The job’s user-supplied parameters.

· The state of current job status and restart information.

· The current state of every object exported or imported and their locations in the dump file set.

Note : The Master Table is the key to Data Pump’s restart capability in the event of a planned or unplanned job stoppage.

Behaviour of Master Table :

This table is created at the beginning of a Data Pump operation and is dropped at the end of the successful completion of a Data Pump operation. The Master Table can also be dropped if the job is killed using the kill_job interactive command. If a job is stopped using the stop_job interactive command or if the job is terminated unexpectedly, the Master Table will be retained.

The keep_master parameter can be set to Y to retain the Master Table at the end of a successful job for debugging purposes

The name of the Master Table is the same as the Data Pump job name and has the following columns:

SQL> Desc <job_name> ;

Process in Datapump Architecture

The master control process

· Maintains job state, job description, restart, and dump file set information in the Master Table.

· This process controls the execution and sequencing of a Data Pump job.

· The master process has two main functions

1. To divide the loading and unloading of data and metadata tasks and handle the worker processes;

2. To manage the information in the Master Table and record job activities in the log file.

Worker Process:

· This handles the request assigned by the master control process. This process maintains the current status of the job, like : ‘pending’ or ‘completed’ or ‘failed’.

· The worker process is responsible for loading and unloading data and metadata.

· The number of worker processes needed can be defined by assigning a number to the parallel parameter.

Parallel Query Process:

· This process is used when the Data Pump chooses External Table API as the data access method for loading and unloading data.

· The worker process that uses the External Table API creates multiple parallel query processes for data movement, with the worker process acting as the query coordinator.

Shadow Process :

· This process is created when a client logs into the Oracle server.

· The shadow process creates a job, which primarily consists of creating the Master Table, creating the queues in Advanced Queues (AQ) used for communication among the various processes, and creating the master control process.

· Once a job is running, the shadow process’ main job is to check the job status for the client process. If the client process detaches, the shadow process goes away; however, the remaining Data Pump job processes are still active.

· Another client process can create a new shadow process and attach to the existing job.

Monday 5 September 2016

11g New Features

This is the most frequently asked interview Question in DBA Interviews that What are the 11g New Features ?

Below are the 11g New Features :

· Automatic Memory Tuning

-Automatic PGA tuning was introduced in Oracle 9i.

-Automatic SGA tuning was introduced in Oracle 10g.

-In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.

· Database Capture/replay database workloads

Allows the total database workload to be captured, transferred to a test database created from a backup or standby database then replayed to test the affects of an upgrade or system change.

· Index can be rebuild ‘online’

Online index build with NO pause to DML.

· RMAN By-Pass UNDO

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. So, RMAN can bypass those types of tablespace. Beneficial for exporting a tablespace from backup.:- Source: Laurent

· Tables can be made read only table :-

E.g ;

SQL> alter table <table_name> read only

SQL> alter table <table_name> read write

· Case sensitive password

Init.ora parameter

sec_case_sensitive_logon = (TRUE FALSE)

To Find users who have case sensitive or case insensitive passwords, Use below queries

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS

Implementation in Password file

$ orapwd file=orclpwd password=manager ignorecase=y

· Passwords are expected to also become case sensitive

In addition to limiting the number of failed login attempts to 10 (the default configuration in 10gR2), Oracle 11g beta’s planned default settings provide a feature to expire passwords every 180 days, and limit to seven the number of times a user can login with an expired password before disabling access.

· ADR ( Automatic Diagnostic Repository)

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool. This tool allow user to interact with ADR , check alert log, check health monitor(HM) status. It is just a file based repository of diagnostic data. ADR gives lot flexibility to maintain / handle diagnostic data.

When critical errors(i.e Problem) are detected, they automatically create an “incident” ( one occurrence of problem is Incident i.e relation between problem and incident is one to many).

Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository), all the incident related files are tagged with Incident Number and certain health checks are run automatically. This information can be packaged to be sent to Oracle support

$ adrci

adrci> show alert ( it will open alert in vi editor )

adrci> show alert -tail ( Similar to Unix tail command )

adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )

adrci> show alert -tail -f ( Similar to Unix command tail -f )

RMAN New Features (11g)

· Active Database Duplication

We can create a duplicate or standby database over the network without taking backup or using old backup.

· Configuring backup compression (Compression Algorithm)

In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compression algorithms for RMAN backups.

· Block Change Tracking (BCT) Faster incremental backups on Physical Standby DB’s :

In 11g we can enable block change tracking on a physical standby database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING).This new 11g feature enables faster Incremental backups on a physical standby database than in previous releases. Because RMAN identify the changed blocks since the last incremental backup.

· Parallel backup and restore for Huge size files

RMAN Backups of large sized data files now use multiple parallel server processes to efficiently distribute the workload for each file.

This features improves the performance of backups.

Data Guard Enhancements , "Active Data Guard"

1) Data Protection

* Advanced Compression

* Lost-write protection

* Fast-Start Failover

2) Increase ROI

* Active Data Guard

* Snapshot Standby

3) High Availability

* Faster Redo Apply

* Faster failover & switchover

* Automatic Failover using ASYNC

Learn DBA : A Life Long Learning Experience