Posts

ORACLE DBA INTERVIEW QUESTIONS (L3)

By -
How good you are at performance tuning? A user has reported that they are facing slowness for a certain sql, what will be your approach to identify the issue? You see that plan hash value of a sql plan has changed and you have already a good plan available in AWR, how will you use that plan? What do you look in AWR report to identify the issue? As a DBA, when and how you reach out to conclusion that a certain issue could be related to network and not database. What is buffer busy wait event? What is log file sync wait event? What other wait event you have observed in your environment which was problematic, can you please describe the situation and remedy that you performed to resolve that issue? What is direct path read event in database, in what scenario this occurs? What is difference between logical and physical reads? What is DB file scattered read and parallel read? If you have to read 100G of data from database using one sql and it has to run 100 times, what method you will prefe
Post a Comment
Read more

ORACLE DBA INTERVIEW QUESTIONS (L2)

By -
## ############ORACLE DBA INTERVIEW QUESTIONS (L2)##################### How Will you create RAC DB from Standalone DB. How you create DR DB from scratch. How will you proceed to migrate your database from FS to ASM. What is Voting Disk, why its required? what is the minimum and maximum number of voting disk that you can have for voting disk. What processes are required in RAC? What does LNS,RFS process do? What is load balancing and its type? How will you perform refresh on RAC Database. What is  raw disk and ASM . How do you approach to stop and start services in RAC env. Why do we need public, private and Virtual IP's? What is ACFS? How do you perform switchover and failover? How to make DB in sync with standby database if its not in sync. what could be the causes? In case of performance issue, how you approach to resolve it. How will you record the information of your current session? How can you use the information collected in trace file from your session? Why you do Index reb
Post a Comment
Read more
By -
How to kill IMPDP/EXPDP job: nohup impdp \"/ as sysdba\" directory=PUMP dumpfile=FULL_EXPORT.dmp logfile=FULL_IMPORT.log & check import process from database: sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 21 10:01:05 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select JOB_NAME,state from dba_datapump_jobs; JOB_NAME STATE ------------------------------ ------------------------------ SYS_IMPORT_FULL_02 EXECUTING SYS_IMPORT_SCHEMA_02 NOT RUNNING SYS_IMPORT_SCHEMA_01 NOT RUNNING SYS_IMPORT_FULL_01 NOT RUNNING SYS_IMPORT_SCHEMA_03 NOT RUNNING exit You can see that IMPORT process is run with SYS_IMPORT_FULL_02 EXECUTING name like above. We can attach or connect to this process like following: impdp \"/ as sysdba\" attach=SYS_IMPORT_FULL_02 check st
Post a Comment
Read more

ORA-28040: No matching authentication protocol

By -
Receiving error: Application is getting below error post up gradation while trying to connect to DB ORA-28040: No matching authentication protocol Cause: It might want you to upgrade the JDBC version. Resolution: You may  align one parameter mentioned below in sqlnet.ora file:   sqlnet.allowed_logon_version_server=8   (A setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, 11g, and 12c.) OR you update your JDBC version and try to connect again. Thanks & Regards Nikhil Kumar.
Post a Comment
Read more

ORA-39083 ORA-02298 ORA-02374 ORA-1289 parent keys not found and value too large for column DESCRIPTION

By -
Issue: While importing from 11.2.0.4 to 12.2.0.1 DB: ORA-39083: Object type REF_CONSTRAINT:"TEST"."TK_COD_TYPE" failed to create with error: ORA-02298: cannot validate (TEST.TK_COD_TYPE) - parent keys not found Failing sql is: ALTER TABLE "TEST"."CODE" ADD CONSTRAINT "TK_COD_TYPE" FOREIGN KEY ("TYPE") REFERENCES "TEST"."CODEAREA" ("CODEAREA") ENABLE ORA-39083: Object type REF_CONSTRAINT:"TEST"."TK_PDE_RPP_ID" failed to create with error: ORA-02298: cannot validate (TEST.TK_PDE_RPP_ID) - parent keys not found Failing sql is: ALTER TABLE "TEST"."PDE_RPP_RPT_PARAM" ADD CONSTRAINT "TK_PDE_RPP_ID" FOREIGN KEY ("RPT_ID") REFERENCES "TEST"."RPT_REPORT" ("RPT_ID") ENABLE ORA-39083: Object type REF_CONSTRAINT:"TEST"."TK_COD_TYPE" failed to create with error: ORA-02298: cannot vali
Post a Comment
Read more

change account status from expired to open

By -
change account status from expired to open: SQL> select username,account_status from dba_users where account_status='EXPIRED'; USERNAME       ACCOUNT_STATUS ------------- -----------------   AUTO_CATT8     EXPIRED SQL> set long 9999999 SQL> set pages 333 lines 222 SQL> select dbms_metadata.get_ddl('USER','AUTO_CATT8') from dual; DBMS_METADATA.GET_DDL('USER','AUTO_CATT8') -------------------------------------------------------------------------------- CREATE USER "AUTO_CATT8" IDENTIFIED BY VALUES 'S:54C1468D29B1759BEF943066549E 4A6583425459428A27280143EEA3872266D9;T:BEC38C394DD6D61E87D51FD0E9C1685C949C8B9CA2F32 DB3626C7F16DE927C91D95FBB0F358AEA2100B5E8251F60C331B6173EAC91530BF3BF0EE84ECF9AB C3486A6D17F81BF2848F33AA7FE97BB0704'       DEFAULT TABLESPACE "AUTOMATION"       TEMPORARY TABLESPACE "TEMP"       PASSWORD EXPIRE SQL> alter USER "AUTO_CATT8" IDENTIFIED BY
Post a Comment
Read more

Database link

By -
Create database link: CREATE  DATABASE LINK TESTROD_AUTO  CONNECT TO USERNAME IDENTIFIED BY PASSWORD   USING {Either service name from tns entry or lsitener address description}; Thanks & Regards Nikhil Kumar.
Post a Comment
Read more