Analyze vs. DBMS_STATS

The following is a quick overview of the two.

• Analyze
  • The only method available for collecting statistics in Oracle 8.0 and lower.
  • ANALYZE can only run serially.
  • ANALYZE cannot overwrite or delete certain types of statistics that where generated by DBMS_STATS.
  • ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.
    • For partitioned tables and indexes, ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics.
    • For composite partitioning, ANALYZE gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics.
  • ANALYZE can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.
  • No easy way of knowing which tables or how much data within the tables have changed. The DBA would generally re-analyze all of their tables on a semi-regular basis.
• DBMS_STATS
  • Only available for Oracle 8i and higher.
  • Statistics can be generated to a statistics table and can then be imported or exported between databases and re-loaded into the data dictionary at any time. This allows the DBA to experiment with various statistics.
  • DBMS_STATS routines have the option to run via parallel query or operate serially.
  • Can gather statistics for sub-partitions or partitions.
  • Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command.
  • DBMS_STATS does not generate information about chained rows and the structural integrity of segments.
  • The DBA can set a particular table, a whole schema or the entire database to be automatically monitored when a modification occurs. When enabled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the DBA_TAB_MODIFICATIONS view. Oracle 9i introduced a new function in the DBMS_STATS package called: FLUSH_DATABASE_MONITORING_INFO. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.
  • DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.
  • Use of table monitoring in conjunction with DBMS_STATS stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)

Oracle Real time Interview Questions with Answer

                     Oracle Real time questions

1)      How can you see the Current SCN number of the database?

Select current_scn from v$database;

2)      How can you see the Current log sequence number the logwriter is writing in to?

Select * from v$log;

3)      If you are given a database, how will you know how many datafiles each tablespace contain?

Select distinct tablespace_name,file_name from dba_data_files;

4). How will you know which temporaray tablepsace is allocated to which user?

Select temporary_tablespace from dba_users where username=’SCOTT’;

5) If you are given a database,how will you know whether it is locally managed or      dictionary managed?

Select extent_management from dba_tablespaces where tablespace_name=’USERS’;

6) How will you list all the tablespaces and their status in a database?

Select tablespace_name,status from dba_tablespaces;

7) How will you find the system wide 1) default permanent tablespace, 2) default temporary tablespace 3) Database time zone?

Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;

8) How will you find the current users who are using temporary tablespace segments?

V$TEMPSEG_USAGE

9) How will you convert an existing dictionary managed permanent tablespace to temporary tablespace?

Not possible

10) Is media recovery requird if a tablespace is taken offline immediate?

Not required

11) How will you convert dictionary managed tablespace to locally managed tablespace?

Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);

12) If you have given command to make a tablespace offline normal, but its not  happening.it is in transactional read-only mode. How will you find which are the transactions which are preventing theconversion?

By looking at queries using by those SID (u can get script from net). I suspect question is not clear.

13) If you drop a tablespace containing 4 datafiles, how many datafiles will be droped at a time by giving a single drop tablespace command?

All datafiles

14) If database is not in OMF,How will you drop all the datafiles of a tablespace without dropping the tablespace itself?

Alter database datafile ‘PATH’ offline drop;

15) How will you convert the locally managed tablespace to dictionay managed?What are the limitations?

Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);

SYSTEM tablespace should be dictionary

16) Which parameter defines the max number of datafile in database?

Db_files and MAXDATAFILES in control file

17) Can a single datafile be allocated to two tablespaces?Why?

No. because segments cannot space multiple datafiles

18) How will you check if a datafile is Autoextinsible?

Select autoextensible from dba_data_files where file_name=’’;

19) Write command to make all datafiles of a tablespace offline without making the tablspace offline itself?

Alter database datafile ‘PATH’ offline normal;

20) In 10g, How to allocate more than one temporary tablespace as default temporary tablespace to a single user?

By using temporary tablespace group

21) What is the relation between db_files and maxdatafiles parameters?

Both will restrict no of datafiles in the database

22) Is it possible to make tempfiles as read only?

yes

23) What is the common column between dba_tablespaces and dba_datafiles?

Tablespace_name

24) Write a query to display the names of all dynamic performance views?

Select table_name from dictionary where table_name like ‘v$%’;

25) Name the script that needs to be executed to create the data dictionary views after database creation?

Catalog.sql

26) Grant to the user SCOTT the RESTRICTED SESSION privilege?

SQL> grant restricted session to scott;

Grant succeeded.

27) How are privileged users being authenticated on the database you are currently working on? Which initialization parameter would give me this information?

Question not clear

28) Which dynamic performance view gives you information about all privileged users who have been granted sysdba or sysoper roles? Query the view?

SQL> desc v$pwfile_users

29) What is the purpose of the DICTIONARY table?

To know data dictionary and dynamic performance view names

30) Write a query to display the file# and the status of all datafiles that are offline?

Select file#,status from v$datafile where status=’OFFLINE’;

31) Write the statement to display the size of the System Global Area (SGA)?

Show parameter sga

Or

Show sga

32) Obtain the information about the current database? What is its name and creation date?

Select name,created from v$database;

33) What is the size of the database buffer cache? Which two initialization Parameters are used to determine this value?

Db_cache_size or db_block_buffers

34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you need to set up Operating System authentication?

exclusive

35)  Which initialization parameter holds this value? What does the shared pool comprise of?

Library cache and data dictionary cache.

Parameter : shared_pool_size

36) Which initialization parameter holds the name of the database?

Db_name

37) Which dynamic performance view displays information about the active transactions in the database? Which view returns session related information?

V$transaction, v$session

38) Which dynamic performance view is useful for killing user sessions? Which columns of the view will you require to kill a user session? Write the statement to kill any of the currently active sessions in your database?

V$session (SID, SERAIL#)

Alter system kill session ‘SID,SERIAL#’;

39) What is the difference between the ALTER SYSTEM and ALTER SESSION commands?

Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session

40) Write down the mandatory steps that a DBA would need to perform before the CREATE DATABASE command may be used to create a database?

Create a pfile or spfile

Create password file

If windows, create instance using ORADIM utility

41) What does the script utlexcpt.sql create? What is this table used for?

It will create EXECEPTIONS table. See below link

42) In which Oracle subdirectory are all the SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc...? Located?

$ORACLE_HOME/rdbms/admin/

43) Which dynamic performance view would you use to display the OPTIMAL size of the rollback segment RBS2. Write a query to retrieve the OPTIMAL size and Rollback segment name?

V$undostat (but many scripts are available in google or even in my blog)

44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?

Extent size

45) How would I start the database if only users with the RESTRICTED SESSION privilege need to access it?

Startup restrict

46) Which data dictionary view would you query to find out information about free extents in your database? Write a query to display a count of the number of free extents in your database?

We can use scripts. Exactly its difficult to know

47) Write a query to display the tablespace name, datafile name and type of extent management (local or dictionary) from the data dictionary?

You need to combine dba_data_files and dba_tablespaces

48) Which two types of tablespace cannot be taken offline or dropped?

SYSTEM and UNDO

49) When a tablespace is offline can it be made read only? Perform the

Required steps to confirm your answer?

Didn’t got the answer

50) Which parameter specifies the percentage of space in each data block that is reserved for future updates?

PCTFREE

51) write down two reasons why automatic extent allocation for an extent may fail?

If the disk space reached max limit

If autoextend reached maxsize limit

52) Query the DBA_CONSTRAINTS view and display the names of all the constraints that are created on the CUSTOMER table?

Select constraint_name from dba_constraints where table_name=’CUSTOMER’;

53) Write a command to display the names of all BITMAP indexes created in the database?

Select index_name from dba_indexes where index_type=’BITMAP’;

54) Write a command to coalesce the extents of any index of your choice?

Alter tablespace <tablespace_name> coalesce;

Don’t know for extents

55) . What happens to a row that is bigger than a single block? What is this called? Which data dictionary view can be queried to obtain information about such blocks?

Row will be chained into multiple blocks. CHAINED_ROWS is the view

56) Write a query to retrieve the employee number and ROWIDs of all rows that belong to the EMP table belonging to user SCOTT?

Select rowid,empno from scott.emp;

57) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?

Repeated question

58) How to compile a view?  How to compile a table?

Alter view <view_name> compile;

Tables cannot be compiled

59) What is the block size of your database and how do you see it?

Db_block_size

60) At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.?

We can recover it from alert log file which contains non-default values

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

By configuring backup retention policy to redundancy 3

RMAN Backup and Recovery Scenarios

RMAN Backup and Recovery Scenarios

 

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie:
cp -p /usr/backup/RMAN/system01.dbf  /usr/oradata/u01/IASDB/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;

Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed,recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
3.   alter database datafile 6 offline; (tablespace cannot be used because the database is not open)
4.   alter database open;
5.   recover datafile 6;
6.   alter tablespace <tablespace_name> online;

Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist.
Pre requisites: All relevant archived logs.
1.   alter tablespace <tablespace_name> offline immediate;
2.   alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’;
3.   recover tablespace <tablespace_name>;
4.   alter tablespace <tablespace_name> online;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    recover tablespace <tablespace_name>;
5.    alter tablespace <tablespace_name> online;

Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.
Pre requisites: A backup of your controlfile and all relevant archived logs.
1.    startup; (you get ora-205, missing controlfile, instance start but database is not mounted)
2.    Use OS commands to restore the missing controlfile to its original location:
cp -p /usr/backup/RMAN/control01.dbf /usr/oradata/u01/IASDB/control01.dbf
cp -p /usr/backup/RMAN/control02.dbf /usr/oradata/u01/IASDB/control02.dbf
3.    alter database mount;
4.    recover automatic database using backup controlfile;
5.    alter database open resetlogs;
6.    make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped.
Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.  If the database is open, shutdown abort
2.  Use OS commands to restore all datafiles to its original locations:
cp -p /usr/backup/RMAN/u01/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u02/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u03/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u04/*.dbf /usr/oradata/u01/IASDB/
etc…
3.  startup mount;
4.  recover automatic database until time ’2004-03-31:14:40:45′;
5.  alter database open resetlogs;
6.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you   may use instead of until time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR
recover database until cancel;

Rman Recovery Scenarios

Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also.
Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database.
Configuration and operation recommendations:
Set the parameter controlfile autobackup to ON to have with each backup a
controlfile backup also:
configure controlfile autobackup on;
set the parameter retention policy to the recovery window you want to have,
ie redundancy 2 will keep the last two backups available, after executing delete obsolete commands:
configure retention policy to redundancy 2;
Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:
backup database plus archivelog;
Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete backup;
To work with Rman and a database based catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rmuser identified by rmuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rmuser
5. exit
6. rman catalog rmuser/rmuser          # connect to rman catalog as the rmuser
7. create catalog                     # create the catalog
8. connect target /                   #

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,so the database can be opened without reseting the redologs.
1.  rman target /
2.  startup mount;
3.  restore database;
4.  recover database;
5.  alter database open;

Complete Open Database Recovery. Non system tablespace is missing,database is up

1.   rman target /
2.   sql ‘alter tablespace <tablespace_name> offline immediate’;
3.   restore datafile 3;
4.   recover datafile 3;
5.   sql ‘alter tablespace <tablespace_name> online’;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1.    sqlplus /nolog
2.    connect / as sysdba
3.    startup mount
4.    alter database datafile ‘<datafile_name>’ offline;
5.    alter database open;
6.    exit;
7.    rman target /
8.    restore datafile ‘<datafile_name>’;
9.    recover datafile ‘<datafile_name>’;
10.   sql ‘alter tablespace <tablespace_name> online’;

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.
1.    sqlplus ‘/ as sysdba’
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf;
4.    exit
5.    rman target /
6.    recover tablespace <tablespace_name>;
7.    sql ‘alter tablespace <tablespace_name> online’;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs, complete cold or hot backup.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    rman target /
5.    recover tablespace <tablespace_name>;
6.    sql ‘alter tablespace <tablespace_name> online’;

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.
Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile.It is the number following the ‘c-’ at the start of the name.
1.   rman target /
2.   set dbid <dbid#>
3.   startup nomount;
4.   restore controlfile from autobackup;
5.   alter database mount;
6.   recover database;
7.   alter database open resetlogs;
8.   make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.
Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the object was dropped.
Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.   If the database is open, shutdown it to perform full restore.
2.   rman target \
3.   startup mount;
4.   restore database;
5.   recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.
6.  alter database open resetlogs;
7.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you may use instead of until sequence, until time, ie: ’2012-01-04:01:01:10′.