Daily Activities of an Oracle DBA

1.         Usually We can check on call mailbox for any alerts/issues like following

·         Mount point full

·         Tablespace alert

·         Listener down

·         Server down

·         Backups fail

2.              Alert log error checking.

3.              Shift handover report, it contains pending request.

4.              Daily resolving the tickets by using Remedy ticketing tool.

5.              Verify all instances are online.

6.              Deploying the application code to production servers.

7.              Schema level refreshing by using exp/imp, expdp/impdp.

8.              Explaining the plans for particular table to tune the databases.

9.              Taking backups by using RMAN on daily and weekly basis.

10.          Supporting to TAF (Transparent Application Failover).

11.          Check tablespaces should not be used more that 95%

12.          Monitoring the RAC instances by using Cron Tab.

13.          Check all last night backups were successful.

14.          Monitoring the databases by using Cron Tab.

15.          Check all database archiving are done.

See Also

 General Tips to Prepare for an Oracle DBA Job Interview  *HOT*

Database Block Corruption

How To Recompile Invalid Objects in Oracle

Recompiling Invalid Schema Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query:


SQL> COLUMN object_name FORMAT A30
SQL> SELECT substr(owner,1,18) owner, object_type, substr(object_name,1,30) object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

There are FOUR ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL

1-      DBMS_DDL.ALTER_COMPILE

Syntax
SQL> EXEC dbms_ddl.alter_compile ( type , schema, name);

Example
SQL> EXEC dbms_ddl.alter_compile ('PROCEDURE' , 'JOHN ' , 'TEST');

2-      DBMS_UTILITY.COMPILE_SCHEMA

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema.

Example
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'JOHN');

3-      UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

The RECOMP_SERIAL procedure recompiles all the invalid objects one at a time.

The RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.  

Example :

-- Schema level.
SQL> EXEC UTL_RECOMP.recomp_serial('JOHN');
SQL> EXEC UTL_RECOMP.recomp_parallel(4, 'JOHN');

-- Database level.
SQL> EXEC UTL_RECOMP.recomp_serial();
SQL> EXEC UTL_RECOMP.recomp_parallel(4);

Restrictions associated with the use of this package :

• Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
• The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA privileges.
• Running DDL operations at the same time as this package may result in deadlocks.

4-     utlrp.sql and utlprp.sql  - - SCRIPTS

·         The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database.

·         They are typically run after major database changes such as upgrades or patches.

·         Located in the $ORACLE_HOME/rdbms/admin directory.

·         The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0".

0 - The level of parallelism is derived based on the CPU_COUNT parameter.

·         The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

Note : Both scripts must be run as the SYS user or another user with SYSDBA privilege , to work correctly.

Interview Questions on Architecture for 1+ Experienced

What are 11g New Features 

1) Automatic Diagnostic Repository [ADR]

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Performance Analyzer (SPA) New

13) DBMS_STATS Enhancements

14) The Result Cache

15) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

11G Background Processes?

The following process are added in 11g as new background processes.

1 dbrm DB resource manager

2 dia0 Diagnosability process

3 fbda Flashback data archiver process

4 vktm Virtual Timekeeper

5 w000 Space Management Co-ordination process

6 smc0 Space Manager process

NOTE : The above six are mandatory processes.

DBWn writes when __ ?

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

What are the Responsibilities of System Monitor (SMON) ?

• Instance recovery

– Rolls forward changes in redo logs

– Opens database for user access

– Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

Why should we use separate ASM home?

ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How many ASM instances should one have?

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

What is ASM Rebalancing?

The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing

Interview Qustions for 2+ Year experienced Candidates

 Hii , Today I will Give you some Questions that you may face in DBA Interview :

How do you find the number of rows in a Table ?

A bad answer is : count them (SELECT COUNT(*) FROM table_name)

 A good answer is : 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLES or ALL_TABLES).

The best answer is : to refer to the utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

Why do you set kernel parameters?

Ans: Oracle recommends that you set shared memory segment attributes as well as semaphores to the following values.

If not set, database instance creation will fail. I added the following lines to /etc/sysctl.conf file.

Every OS process needs  semaphore where It waits on for the resources.

For more on semaphore, please read the UNIX os documents.

How you Identify the Corruption (rman)

An alternative method to identify block corruption in an Oracle data file is to use the RMAN validate function. The following example is taken from Windows:

RMAN> connect target sys/****

connected to target database: TAXPROD (DBID=3492187718)

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;

Once the validate completes, the V$DATABASE_BLOCK_CORRUPTION can be used to check for corruption:

Q. Common Listener Issues You Faced ?

It is not very common to find listener-related problems in Oracle, and most connectivity-related problems are generally associated with database server problems, such as shortage of memory.

Here are some common errors:

    ORA-12500 TNS:listener failed to start a dedicated server process  - This is generally associated with memory shortages or permission problems in UNIX.

    ORA-3113 end-of-file on communication channel – This is generally a network failure

    TNS-12547 TNS:lost contact – This can be corrected by increasing the connect_timeout_listener parameter.

    TNS-12224 TNS:no listener – Clients will get this message if the listener process is not running.

    ORA-12570 TNS:packet reader failure – This is a network problem.

    ORA-12571 TNS:packet writer failure – This is a network problem

Note that in many cases, a failure of the listener will not be logged on the database server, but will instead be presented as a message on the client workstation.  Hence, the vast majority of listener errors are reported by end-users.

Q. How we see that our listener log is located in ?? 

 

 cat `lsnrctl stat|grep Log|awk '{print $4 }'`|grep ORA-

/ora7/home/dba/product/7.3.4/network/log/listener.log.