Automatic INDEX statistics

Nice feature in 10g and onward - index creation or rebuild automatically calculates statistics for the index. let's check it practically here.


SQL> conn /as sysdba
Connected.


SQL> create table T as select * from dba_objects;

Table created.


SQL> create index tx on T(object_id);

Index created.


SQL> select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------
        72455



Above you can see statistics is gathered for the index TX created in earlier statement.

Let's now delete the statistics for this index and try to rebuild to see if it gathers the state again automatically by just rebuidling the index.


SQL> analyze index tx delete statistics;

Index analyzed.


SQL> select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------


Note :- You can see statistisc is no longer available for the index.


Now let's rebuild the index now.

SQL> alter index tx rebuild;

Index altered.



SQL>  select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------
        72455



Statistics for the index gathered automatically by rebuilding it.

Automatic INDEX Statistics

Oracle 10g Database Architecture............

Overview of the System Global Area

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:

• Database buffer cache
  
• Redo log buffer
  
• Shared pool
  
• Java pool
  
• Large pool (optional)
  
• Streams pool
  
• Data dictionary cache
  
• Other miscellaneous information
  

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.

The Oracle server architecture
The Oracle server architecture is basically categorized as follows:

• Logical layer
• Physical layer

The logical layer comprises one or more tablespaces and the database schema. The database schema consists of tables, clusters, indexes, views, procedures, triggers, sequences, and so on. The database schema is a collection of schema objects.
The relationship between segments, extents, and data blocks
The hierarchy of Oracle disk-space management is as follows:

• Tablespaces: These are at the highest level of Oracle disk-space management.
• Segments
• Extents
• Data blocks: These are at the lowest level of Oracle disk-space management.

The physical layer comprises the following files:

• The control file: Though it is the smallest file (1MB-5MB) of the database, it is the most crucial and critical file. The criticality of this file is ascribed to the fact that if it is damaged then the database recovery operation becomes tedious. It contains the requisite information to start the database. The names and locations of all the control files of the database can be obtained from the V$CONTROLFILE dynamic performance view.
• Data files: These hold data that is stored in tables of a database. They are usually the largest files in the database, ranging in size from MB, GB, to TB.
• Redo Log files: These files contain information that helps in recovery in the event of system failure. It contains information stored in the Redo Log Buffer. The information in the Redo Log Buffer is written into Redo Log files by the LGWR background process. The Redo Log files are generally multiplexed and copied for recovery purposes. Sets of redo Log files are known as Redo Log groups. Each database has minimum of two Redo Log groups. Redo Log groups are used in a circular fashion

Note:

1. The V$LOGFILE dynamic performance view is used to obtain information about the names and locations of the Redo Log groups and their members.
2. LGWR writes every three seconds to a Redo Log group in the case of the following events:
   • Whenever a user commits a transaction
   • Whenever the Redo Log Buffer is one-third (1/3) full

The Oracle memory structure
The most basic components of Oracle memory are:

• SGA- It stands for System Global Area. SGA is allocated whenever an Oracle instance starts and gets de-allocated when the Oracle instance is shutdown. Components of SGA:
  
  1. The Database Buffer Cache
  2. The Redo Log Buffer
  3. The Shared Pool


• PGA- It stands for Program Global Area. It is a region in memory that contains data and control information for a single process. This can be either a server or a background process. A PGA is allocated whenever an Oracle database user connects to a database and a session is created for him/her.

Oracle processes
There are two categories of processes that run with an Oracle database. They are mentioned below:

• User processes
• System processes

  The first interaction with the Oracle-based application comes from the user computer that creates a user process. The user process then communicates with the server process on the host computer. Here, PGA is used to store session specific information.
Oracle background processes
Oracle has a large number of background processes. The background processes are categorized into:

• Required Oracle background processes
• Optional Oracle background processes

Some of the background processes are:

1. DBWR – It writes data blocks from SGA to data files.
2. LGWR – It writes data from the Log Buffer to the redo log.
3. CKPT – It timestamps all the data files and the control files whenever a checkpoint occurs.
4. PMON – It stands for process monitor. It keeps track of database processes. It also cleans up the process that has died pre-maturely. The result is that all the cache and the resources are freed up. It also restarts those dispatcher processes that might have failed. Some of the main functions of background processes are:
   
   • Communication among Oracle instances
   • Performing computer maintenance
   • Writing the dirty blocks to disk
5. SMON – It stands for system monitor. It performs instance recovery at instance startup.

The Oracle instance
The Oracle instance consists of SGA and all the Oracle background processes. To manage the size of SGA, two initialization parameter files known as PFILE and SPFILE are used. There are a total of 250-initialization parameters. PFILE holds 30 of those 250 initialization parameters. Oracle does not recommend modifying the rest of the 220 initialization parameters.
This article has described the various facts involved in order to understand the underlying Oracle architecture. After reviewing this article, the database user is now well equipped to understand several components of Oracle architecture.

Identifying INVALID objects in Oracle Database

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

SQL>COLUMN object_name FORMAT A30
SQL>SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 DMSYS    PACKAGE BODY    DBMS_DATA_MINING_INTERNAL        INVALID  
 DMSYS    PACKAGE BODY    DBMS_DM_UTIL              INVALID  
 DMSYS    PACKAGE BODY    DM_SEC_SYS               INVALID  
 OLAPSYS   PACKAGE       CWM2_OLAP_AW_AWUTIL           INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_AW_AWUTIL           INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_EXPORT            INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_MANAGER            INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_OLAPAPI_ENABLE        INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_PC_TRANSFORM         INVALID  
 OLAPSYS   PACKAGE BODY    CWM2_OLAP_UTILITY            INVALID  
 OLAPSYS   PACKAGE BODY    DBMS_AW_UTILITIES            INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 OLAPSYS   PACKAGE BODY    DBMS_ODM                INVALID  
 OLAPSYS   PACKAGE BODY    OLAPDIMVIEW               INVALID  
 OLAPSYS   PACKAGE BODY    OLAPFACTVIEW              INVALID  
 SYS     PACKAGE       DBMS_REPCAT_MIGRATION          INVALID  
 SYS     PACKAGE       LTADM                  INVALID  
 SYS     PACKAGE BODY    DBMS_LOGMNR_D              INVALID  
 SYS     PACKAGE BODY    DBMS_REGISTRY              INVALID  
 SYS     PACKAGE BODY    DBMS_REPCAT_MIGRATION          INVALID  
 SYS     PACKAGE BODY    DBMS_STREAMS_ADM_UTL_INVOK       INVALID  
 SYS     PACKAGE BODY    DBMS_STREAMS_AUTH            INVALID  
 SYS     PACKAGE BODY    DBMS_SUMREF_UTIL            INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 SYS     PACKAGE BODY    KUPM$MCP                INVALID  
 SYS     PACKAGE BODY    KUPW$WORKER               INVALID  
 SYS     PACKAGE BODY    LT                   INVALID  
 SYS     PACKAGE BODY    LTADM                  INVALID  
 SYS     PACKAGE BODY    LTDDL                  INVALID  
 SYS     PACKAGE BODY    LTDTRG                 INVALID  
 SYS     PACKAGE BODY    LTRIC                  INVALID  
 SYS     PACKAGE BODY    LTUTIL                 INVALID  
 SYS     PACKAGE BODY    OWM_BULK_LOAD_PKG            INVALID  
 SYS     PACKAGE BODY    OWM_DDL_PKG               INVALID  
 SYS     PACKAGE BODY    OWM_MIG_PKG               INVALID  
 OWNER    OBJECT_TYPE     OBJECT_NAME               STATUS  
 ------------ ------------------- --------------------------------------- ------- ---  
 SYS     PACKAGE BODY    OWM_MP_PKG               INVALID  
 SYS     PACKAGE BODY    UD_TRIGS                INVALID  
 SYS     PACKAGE BODY    UTL_FILE                INVALID  
 SYS     PACKAGE BODY    WM_DDL_UTIL               INVALID  
 XDB     PACKAGE BODY    DBMS_XMLPARSER             INVALID  
 XDB     PACKAGE BODY    DBMS_XSLPROCESSOR            INVALID  
 39 rows selected.  
 SQL>  

How to RECOMPILE INVALID objects in Oracle database?

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SYS');
EXEC UTL_RECOMP.recomp_parallel(4, 'SYS');

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



-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SYS');

SQL> exec utl_recomp.recomp_serial('SYS');

PL/SQL procedure successfully completed.

SQL> exec utl_recomp.recomp_serial('XDB');

PL/SQL procedure successfully completed.

SQL> exce utl_recomp.recomp.serial('OLAPSYS')

PL/SQL procedure successfully completed.

SQL> exce utl_recomp.recomp_serial('DBSYS')

PL/SQL procedure successfully completed.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');

EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

This method is limited to PL/SQL objects, so it is not applicable for views.

utlrp.sql and utlprp.sql

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. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both above scripts must be run as the SYS user, or any other user with SYSDBA role.




Twitter : https://twitter.com/rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/
Facebook : https://www.facebook.com/rkushawaha

Linux Operating System interview questions for Oracle DBA

Below are some of the most important interview questions from Operating system
point of view to a DBA.

1)How can you check the files permission in Unix OS?

Ans: 'ls' command with the below option is used for checking files permission.
$ ls -altr
total 198
drwxr-xr-x 2 root root 4096 Aug 8 2008 srv
drwxr-xr-x 2 root root 4096 Aug 8 2008 mnt
drwxr-xr-x 3 root root 4096 Mar 7 05:10 home
drwxr-xr-x 7 root root 4096 Mar 7 05:47 lib64

2)How can you schedule job in Unix OS?
Ans: 'crontab' command is used for scheduling job in Unix OS.Crontab can be installed and removed as followed:

For commands that need to be executed repeatedly (e.g. hourly, daily or weekly), use crontab, which has the following options:

crontab filename Install filename as your crontab file.
crontab -e Edit your crontab file.
crontab -l Show your crontab file.
crontab -r Remove your crontab file.

Eg: Create a backup script as per your backup strategy and schedule as per strategy

$ vi backupdb.sh

. $HOME/.profile
export ORACLE_SID=
rman nocatalog << EOF connect target # backup controlfile configure controlfile autobackup on; # backup database,archivelogs and delete backed up archivelogs backup database plus archivelog delete input; # delete obsolete delete obsolete; yes EOF :wq =>save and quit

Note: In the line ". $HOME/.profile" there has to be a space between . and $, to execute .profile in current shell.
Now schedule it with crontab(cronjob) command.
crontab -e

30 20 * * 1,3,5 /u01/backupdb.sh 1>/u01/backupdb.log 2>&1

:wq
=>save and quit displays the below message.

crontab: installing new crontab

Meaning: 00 =>seconds 00-59,Here it is 30 seconds;
20=>hours in a day 00-23,Here it is 8 pm;
*=>Day of the month,1-30,Here * signify the value does not apply
*=>Month of the year,1-12,Here * signify the value does not apply
1,3,5 =>Day of the week,0-6,Here 1,3,5 is monday,wednesday,friday.

/u01/backupdb.sh=>our backup script
> =>for overriding the content
/u01/backupdb.log =>for checking the output after the scheduled job is executed
& =>for running in backup

3)Which tool you use to copy files from windows to linux or linux to windows?
Ans: Winscp can be used for copying files from windows to linux.Winscp is easy to install GUI based utility for copying files in binary mode by default.Howerver 'ftp' command line utility can also be used.

4)How can you search file in Unix OS?
Ans: Using 'find' command we can search file in unix OS.

$ find /home/oracle -name "*.vim*" -print
/home/oracle/.viminfo

where: /home/oracle: path of the directory where you are searching file.

5)Which command is used to see the disk space details in Unix OS?
Ans: We use 'df -h' =>Linux OS, 'bdf' =>HP-UX OS,'df -g' =>AIX 0S

]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 5.7G 4.0G 1.5G 73% /
/dev/sda7 4.9G 4.1G 516M 90% /u01
/dev/sda3 6.3G 2.7G 3.3G 46% /u03
/dev/sda2 34G 32G 720M 98% /u02

Note: In IBM-AIX OS we have to use 'df -g' ; In HP-UX OS we have to use 'bdf' for checking the Disk Space details .

6) Which comand is used to see files usage details in Unix OS?
Ans:'du' command is used to see directories and file usage details in Unix OS.

$ cd /u01
[oracle@node1.u01]$ du -csh *
16K lost+found
756K oracle
4.1G oracle11g

7)Which command is used to schedule job without user intervention and backend?
Ans: 'nohup' command is used to run a command without user intervention.If we use '&' symbol than the command will run in backend.'nohup' command is my favourite command for running export and import jobs.I use this command very often.

Eg: nohup sh file_name.sh >file_output.out &

8)What are frequently used OS command for DBA's?
Ans: mkdir =>for creating directories in Unix OS; cd =>For changing the directory;
rm =>For removing files;rmdir =>For removing directories;grep =>For searching characters in a file; man =>User manual for all commands; useradd=>for creating OS user; chmod=For granting permission to files and Directories;chown=>For changing ownership of Directories.

Eg: mkdir test =>Create test directory
cd /u01/test =>Will change the path of Directory
man grep =>user manual for all command man has it own advantage.
chmod -R 775 /u01/test =>Grants permissions as read,write,execute to owner,groups
useradd -d oracle =>For Creating Operating system user as Oracle.

9)Which command is used to copy or synchronizing two Directories in a secure way on any Unix environment?
Ans : 'rsync' command can be used to copy or synchronize two directories.It is very important command and very handy tool for copying files fast for a DBA,Ofcourse
'scp' can also be use.But I really like using 'rsync' .Below is the example:

Eg:
----
[oracle@node1 HEALTH_CHECK_DB]$ rsync -av /u04/HEALTH_CHECK_DB/ /u05/SCRIPTS_DBA_TASKS/
building file list ... done
./
Database_status.sh
archive_gen.sql
c:&FileName._TESTDB1_20110509.html
c:&FileName._TESTDB2_20110509.html
db_monitor.sql
health_check.sql

sent 4225122 bytes received 158 bytes 8450560.00 bytes/sec
total size is 4224061 speedup is 1.00
[oracle@node1 HEALTH_CHECK_DB]$ cd ..
[oracle@node1 u04]$ cd /u05/SCRIPTS_DBA_TASKS/
[oracle@node1 SCRIPTS_DBA_TASKS]$ ls -altr
total 4176
-rwxrwxrwx 1 oracle dba 1815 Mar 27 23:53 archive_gen.sql
-rwxrwxrwx 1 oracle dba 2724 Mar 27 23:59 db_monitor.sql
-rw-r--r-- 1 oracle dba 1233385 May 9 11:40 health_check.sql
-rw-r--r-- 1 oracle dba 1421433 May 9 11:41 c:&FileName._TESTDB1_20110509.html
-rw-r--r-- 1 oracle dba 1564704 May 9 11:43 c:&FileName._TESTDB2_20110509.html
-rw-r--r-- 1 oracle dba 0 May 18 12:24 Database_status.sh
drwxrwxrwx 2 oracle dba 4096 May 18 12:24 .
drwxrwxr-x 9 oracle oinstall 4096 May 26 12:37 ..
-rwxrwxr-x 1 oracle dba 486 May 26 12:39 SCHEMA_CREATE_SCRIPT_TESTDB1_TESTDB2.sql
-rwxrwxr-x 1 oracle dba 846 May 26 14:06 profile_create.sql
-rwxrwxr-x 1 oracle dba 1252 May 27 20:44 crontab.oracle
-rwxrwxr-x 1 oracle dba 143 May 30 17:12 tablespace_create_script.sql

If you want to copy files from one linux(unix server) to other again it can be very handy,below is the syntax:

rsync -avc my_stuff2/ user@remotehost:~/mystuff3/

rsync copies only those files which are not copyied and this is definetly
useful in syncronizing source and destination directories.Hence it is very fast.

10)Which command is useful to see line by line display in Unix environment?
Ans: 'less' command is used to see line-by-line display('more' for page-by-page display).I find less more useful specially for seeing log files and to find the errors or Warnings in the log files.

Eg: less test1.log =>Will display test1.log file

11)Which command can be used to view file in readonly mode?
Ans:'view' command can be used to view file in readonly mode.A very good option to see cronjob file specially because at any this file should not get modified by mistake as all your daily jobs will be scheduled using cronjob.

Eg: view crontab.oracle

12)How we can delete the files which are few days(N days) old?
Ans:To save the disk space you might be deleting the old files or backup which are 1 week(2 weeks) old or depending on your Disk Space and other requirement.We should automate these tasks as a DBA.We can do this as follows:

For Unix environment:
-----------------------------------
Eg: If I want to delete files from a path which are 7 Days old:
Write one shell script as given below:

#remove_files_back.sh
#Removing 7 days old dump files
find /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup -mtime +6 -exec rm {} \;

Where: find =>Finding the file; /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup =>path;
-mtime=>Modified time,Here I'm giving 7 days(>6);-exec rm =>execute removing for files. Now as per your convenience schedule cronjob for doing this task,For example every sunday at 9 pm than:

00 21 * * 0 /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup/remove_files_back.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/TESTDB_BACK_cron.log

Note: For Windows Environment you can create a .bat file as follows:

--remove_file_back.bat
forfiles /p "D:\dbbackup\testdb\daily" /s /d -7 /c "cmd /c del @file : date >=7 days >NUL"

Where:All files which are 7 days old are removed;D:\dbbackup\testdb\daily =>path.
However Please make a note Please don't use the above command for your /(root) directory or any software specified and confirm and test in test environment before using on the actual system.

What is Cache Fusion?

Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects.

The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cachce fusion.

What is GRD?

GRD stands for Global Resource Directory.

The GES and GCS maintains records of the statuses of each datafile and each cached block using global resource directory.This process is referred to as cache fusion and helps in data integrity.

Mention the Oracle RAC software components

Oracle RAC is composed of two or more database instances.
They are composed of Memory structures and background processes same as the single instance database.

Oracle RAC instances use two processes:
1- GES(Global Enqueue Service)
2- GCS(Global Cache Service) that enable cache fusion.

Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor

What is RAC?

RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.

Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT)

Locally Managed Tablespaces: A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information). When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.


Dictionary Managed Tablespaces: In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.


 Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT): 

1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required Converting DMT to LMT:

 SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local(‘ts1′);
PL/SQL procedure successfully completed. Converting LMT to DMT:

 SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘ts2′);
PL/SQL procedure successfully completed.



 Important Points:

1. LMTs can be created as

a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.

2. One cannot create a locally managed SYSTEM tablespace in 8i.

3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.

4. Locally managed temporary tablespaces can not be of type “permanent”.

How to find CPU usage for a session

Below is the query to find High CPU utilizing Database sessions and their state.

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid               FORMAT 9999             HEADING 'SID'

COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'

COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right

COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program   FORMAT a20              HEADING 'Session Program' TRUNC

COLUMN session_machine   FORMAT a14              HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN cpu_value         FORMAT 999,999,999,999  HEADING 'CPU'

prompt

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by CPU                       |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,14)   session_machine

  , sstat.value          cpu_value

FROM

    v$process  p

  , v$session  s

  , v$sesstat  sstat

  , v$statname statname

WHERE

      p.addr (+)          = s.paddr

  AND s.sid               = sstat.sid

  AND statname.statistic# = sstat.statistic#

  AND statname.name       = 'CPU used by this session'

ORDER BY cpu_value DESC

/

Controlfile is Missing

Database failed and you have restored it with the previous days backup and after restoring you noticed that control file is missing what do you do.


1. If one or more not all control file/s got deleted/corrupted you can replace it by the good one from another location. It is only hit and trial basis.


2. If you have backup of the control file you can restore it from the backup.

3. If none of these then create new control file.

How to export a limited number of rows from a table

By using DBMS_DATAPUMP.metadata_filter, DBMS_DATAPUMP.data_filter we can export a limited number of rows from a table.

What are the views available to see the information about export.

DBA_DATAPUMP_JOBS:

This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.



DBA_DATAPUMP_SESSIONS:
This view gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.


V$SESSION_LONGOPS:
This view helps determine how well a Data Pump export is doing. Basically gives you a progress indicator through the MESSAGE column.

How do you see how many memory segments are acquired by Oracle Instances?

Check on x$bh it show you what objects are in the buffer.

 Try this query:

 SELECT /*+ ordered use_hash(o) use_hash(bh) */ bp.NAME BUFFER_POOL u.NAME owner so.object_type segment_type o.NAME segment_name COUNT (*) cached_blocks FROM v$buffer_pool bp SYS.seg$ s SYS.sys_objects so SYS.obj$ o SYS.user$ u x$bh bh WHERE bh.obj o.dataobj# AND o.obj# so.object_id AND o.type# so.object_type_id AND o.owner# u.user# AND so.segment_type_id s.type# AND so.ts_number s.ts# AND so.header_file s.file# AND so.header_block s.block# AND DECODE (s.cachehint 0 3 s.cachehint) bp.ID GROUP BY u.NAME so.object_type o.NAME bp.NAME ORDER BY bp.NAME cached_blocks DESC;

Command to check how much hard disk space is free in Linux?

# df -h
The output will be Filesystem Size Used Avail Use% Mounted on /dev/hda3 75G 17G 54G 24% / /dev/hda1 99M 17M 78M 18% /boot none 505M 0 505M 0% /dev/shm /tmp 243M 7.2M 223M 4% /tmp Here the size is represented by megabytes and gigabytes.

Now let’s create an executable file to show the disk sizes:

#!/bin/sh DISC=$1 PARTITION=`df -h |grep $DISC |awk ‘{print $1}’` SIZE=`df -h|grep $DISC|awk ‘{print $2}’` USED=`df -h|grep $DISC|awk ‘{print $3}’` FREE=`df -h|grep $DISC|awk ‘{print $4}’` echo “Partition: $PARTITION” echo “Total size: $SIZE” echo “Used space: $USED” echo “Free space: $FREE”

Simply copy & paste this script into for example into a file named info.sh(create it with VI or JOE or even PICO).

Next, you’ll need to make it executable.

To do this, use the following command: # chmod +x info.sh

Now, to execute the file, you need to run it, and pass it the correct argument.

For our example, we are going to use hda3. So, to execute the file, type in the command as below….

# ./info.sh hda3

Can you Redefine a table Online?

Yes. We can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.

You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

CONFIGURE RETENTION POLICY TO REDUNDANCY 3

The current logfile gets damaged. What you can do now?

Once current redolog file is damaged, instance is aborted and it needs recovery upto undamaged part. Only undamaged part can be recovered. Here DBA must apply time based recovery, means it can be a point in time or specified by SCN. It leads to incomplete recovery

You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

Restore it from Full backup and recover that data file using archive log files which are after Full backup to till date

You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

You have lost datafiles and you dont have backup and database is not running in archive log mode…… So we need to recreate the datafiles which will be empty….. you can not recover, becuase you dont have backup and database not in archive log mode

Which is more efficient Incremental Backups using RMAN or Incremental Export?

RMAN

How do you see how many instances are running?

For single instance Oracle Database Server, you can find the number of instances running using below commands.

In Linux, Unix the command:
$ ps -ef | grep pmon


In Windows:
services.msc

How to recover if we delete a data file at OS level no backup for that?

Bring the database to the mount state

SQL>alter database create datafile ;

SQL>recover datafile ;

SQL>alter database open;

DBA Interview Questions

1) Primary database scn is 22 and standby SCN is 24 how you will resolve this issue.

 2) Archive log got deleted before shipped to standby location how you will resolve this issue.

 3) 3 users running same select but one of them response time is 1hr and for other few minutes how u will diagnose this.

 4) What is advantage of ASM over os storage and raw storage.

 5) What is remastering in RAC

 6) How you can see database version at OS level.

 7) A job is running since long it was working fine earlier how you will check that How you will suggest joins to developer

 9) 3 databases sizes 10gb, 100gb, 1TB how which database will take more time.

 10) You lost control file database is using RMAN backup with nocatalog. If you loose whole db as well same time how you recover database.

 11) what is sync, and async in standby how it works.

 12) Shut immediate is taking too much time how you diagnose what is the issue.

 13) How you take backup of OCR.

 14) What will happen if voting disk is down.

 15) What will happen if virtual IP is down in RAC.

 VIP – Virtual IP address in RAC VIP is mainly used for fast connection in failover. Until 9i RAC faileover we used physical IP address of another server. When the connection request come from a client to server, then failure of first server listener then RAC redirect the connection request to second available server using physical IP address. Hence it is physical IP address rediretion to second physical IP address is possible only after we get timeout error from First Physical IP address. So connection should wait a while for getting TCP connection timeout. From RAC 10g we can use the VIP to save connection timeout wait, Because ONS (Oracle Notification Service) maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node. This Process will not wait for TCP/IP timeout event. So new connection will be faster even one listener/node failed.

RMAN Interview Questions

RMAN Interview Questions

1) If one archive deleted before applying on standby and you do not have backup how u resolve this issue?. Database size is in TB so recreating Standby is not good idea.

2) If block change tacking is enabled on production database will it be enabled on cloned database automatically?

3) You have assigned 3-4 channels in RMAN backup how you will identify whether all are utilized or not?

4) How you do performance tuning for RMANbackups?

5) Can we register manual cold backup in RMAN?

6) Can we recreate oracle inventory if we do not have any backup?

7) What is difference between image copy & normal backup in terms of performance?

8) 10K blocks in your database got corrupted how you will resolve this?

9) What is the benefit of Recovery Catalog and would you create one?

10) Can you register multiple target databases in a single RMAN Catalog?

11) When you try to register a target database in RMAN catalog then you get error that Database is already registered? What could be the reason of it and how would you overcome from this situation?

12) How would you take archivelog log backups?

13) What is the benefits of taking RMAN backups over other backup strategies?

Oracle Clusterware Processes on Linux

What are Oracle Clusterware processes for 10g on Unix and Linux:

Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.

 Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource’s configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

 Event manager daemon (evmd) —A background process that publishes events that crs creates. 

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing.

OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then
OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.

 RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

Data Guard Background processes

Data Guard Background processes

DMON – The Data Guard Broker process.

SNP – The snapshot process.

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

ORBn – performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB – is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS – Remote File Server process – In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN – Queue Monitor Process

(QMNn) – Used to manage Oracle Streams Advanced Queuing.

Oracle Jobs Interview Questions and Answers

1. What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
2. What is On-line Redo Log? - The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
3. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? - All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
4. What are the steps involved in Database Startup? - Start an instance, Mount the Database and Open the Database.
5. What are the steps involved in Instance Recovery? - Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
6. Can Full Consistent Backup be performed when the database is open? - No.
7. What are the different modes of mounting a Database with the Parallel Server? - Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
8. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
9. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
10. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
11. What is Restricted Mode of Instance Startup? - An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
12. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
13. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
14. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.
15. Can a View based on another View? - Yes.
16. Can a Tablespace hold objects from different Schemes? - Yes.
17. Can objects of the same Schema reside in different tablespaces? - Yes.
18. What is the use of Control File? - When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
19. Do View contain Data? - Views do not contain or store data.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
21. What are the type of Synonyms? - There are two types of Synonyms Private and Public
22. What is a Redo Log? - The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
23. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
24. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
25. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
26. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
27. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
28. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
29. What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
30. What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
31. What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
32. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
33. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
34. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
35. What is schema? - A schema is collection of database objects of a User.
36. Describe Referential Integrity? - A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
37. What is Hash Cluster? - A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
38. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
39. What is Database Link? - A database link is a named object that describes a “path” from one database to another.
40. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together
41. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
42. What are the Characteristics of Data Files? - A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
43. How to define Data Block size? - A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.
44. What does a Control file Contain? - A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.
45. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.
46. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? - What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
47. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? - The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
48. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
49. What is Execution Plan? - The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
50. What are the different approaches used by Optimizer in choosing an execution plan? - Rule-based and Cost-based.