Oracle 10 DBA Interview questions and answers

1. Is the following SQL statement syntactically correct? If not, please rewrite it correctly.

SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);

Ans. SQL is incorrect.

Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);



2. What is a more efficient way to write this query, to archive the same set?


Ans: SELECT col1 from tableA minus SELECT col1 from tableB



3.How would you determine that the new query is more efficient than the original query?

Ans: Run explain plan on both query and see the result .



4.How can we find the location of the database trace files from within the data dictionary?

Ans: Generally trace file on the database server machine is located in one of two locations:

1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by

the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the

BACKGROUND_DUMP_DEST parameter.

you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'


5. What is the correct syntax for a UNIX endless WHILE loop?

while :
do
commands
done



6. Write the SQL statement that will return the name and size of the largest datafile in the database.


SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);


7. What are the proper steps to changing the Oracle database block size?

cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.




8. Using awk, write a script to print the 3rd field of every line.

Ans:

awk '{print }'

awk '{print $3}

awk '{print $3}


9.Under what conditions, is a nested loop better than a merge join?

Ans:

Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving

condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value

from outer table.

Optimizer may not use nested loop in case:

1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.


merge join is used to join two independent data sources. They perform better than nested loop when the volume of

data is big in tables
but not as good as hash joins in general.



10.Which database views would you use to ascertain the number of commits a user's session has performed?

Joining V$SESSTAT ,V$STATNAME

select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=



11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?

Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to

interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell

script.




12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages

(ONS, VIP, listener, instances, etc.)?

Ans:

$ocrcheck


13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.

If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.



14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?

using oifcfg command.

se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are

optional depending on the command, are:

*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect

You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node

by executing the iflist keyword as shown in this example:

oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16


You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]

To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet

139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),

you would use the command:

oifcfg setif -global hme0/139.185.141.0:cluster_interconnect


For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0

interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1

and rac2 respectively:

oifcfg setif -global cms0/139.185.142.0:cluster_interconnect


Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific

node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the

command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of

the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command

deletes the global interface named qfe0 for the subnet 204.152.65.0:

oifcfg delif -global qfe0/204.152.65.0


On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:

oifcfg delif -global



15.What is the Purpose of the voting disk in Oracle 10g Clusterware?

Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.


16.What is the purpose of the OCR in Oracle 10g Clusterware?

Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.

The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.



17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived

logs are no longer needed by the capture process?

Ans: V$ARCHIVE_DEST_STATUS

Saturday, September 6, 2008

How to setup RMAN in Oracle 9i

Commands and setup parameters used to configure a typical RMAN environment.

1. Configuring the Backup Database User

The RMAN executable requires a SYSDBA connection to the database. Although it is possible to use the SYS account, I generally create a user dedicated to backing up the database. Before creating the new user, ensure that your database is configured to use a password file.

% echo $ORACLE_SID
ORA920

% sqlplus "/ as sysdba"

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------- ----------- ------------
remote_login_passwordfile string EXCLUSIVE


SQL> CREATE USER backup_admin IDENTIFIED BY backup_admin
2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.


SQL> GRANT sysdba TO backup_admin;

Grant succeeded.

We should now test running RMAN while logging in as the newly created database user:

% rman target backup_admin/backup_admin

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA920 (DBID=6732194167)

RMAN> exit

Recovery Manager complete.

Configuring RMAN Default Parameters

RMAN (Oracle9i and higher) now allows the DBA to perform automated database backup and recovery. This feature is supported by RMAN with its ability to define default values for a number of settings, (i.e. channel configuration). Setting RMAN settings is performed with the configure command.

Before configuring our RMAN environment, lets take a look at the default settings by using the show all command:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default

New RMAN Configuration Parameters

configure retention policy to redundancy 2;
configure default device type to disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/orabackup/rman/ORA920/%F';
configure channel device type disk format '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' maxpiecesize 1024m;

10g RMAN Configuration Parameters

configure controlfile autobackup on;
configure channel device type disk maxpiecesize 2 g;

After running the above configuration commands in RMAN, here is what the show all command displays:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/rman/ORA920/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default


Backing up the Target Database

Now that RMAN is configured, backing up the database (including backing up all archive redo logs), is as easy as:

RMAN Statements to Perform Full (online) Database Backup

% rman target backup_admin/backup_admin nocatalog msglog rman_full_backup_db_online_ORA920.log

RMAN> backup database plus archivelog delete input;
RMAN> delete noprompt force obsolete;
RMAN> exit;


The following table includes all resulting backup sets contained in the control file after performing the above backup routine.

BS
Key Backup
Type Device
Type Controlfile
Included? SPFILE
Included? Piece
Number Handle (Piece) Name Start
Time End
Time Elapsed
Seconds Tag Block
Size
2 Datafile Full DISK 1 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 11/28/04 20:29:02 11/28/04 20:32:44 222 TAG20041128T202902 8,192
2 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 11/28/04 20:32:47 11/28/04 20:34:45 118 TAG20041128T202902 8,192
3 Archived Logs DISK 1 /orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 11/28/04 20:34:53 11/28/04 20:34:53 0 TAG20041128T203452 512
4 Datafile Full DISK YES YES 1 /orabackup/rman/ORA920/c-2542143170-20041128-01 11/28/04 20:34:54 11/28/04 20:34:55 1 8,192


Now, let's talk about the actual steps that were performed by RMAN after running the full (online) database backup statements. I attempted to annotate the RMAN log file (below) with the following steps. Note that the first 6 steps were all part of the backup database plus archivelog delete input; statement. The 7th step includes the delete noprompt force obsolete; statement

1. The first action performed by RMAN is a log switch (using the alter system archive log current command).

2. Next, all existing archived redo logs (including the one just archived) are backed up to a Archive Log backup set. Note that at the end of the RMAN statements in our example, this archive log backup set (backup_db_ORA920_S_4_P_1_T_543443326) will be removed as it will no longer be needed once the database files are backed up.

3. RMAN then starts the backup of the actual database files. For this backup set, (BS Key - 2), RMAN creates two backup pieces: backup_db_ORA920_S_5_P_1_T_543443342 and backup_db_ORA920_S_5_P_2_T_543443342.

4. After the full database backup, RMAN performs another archive redo log switch (using the alter system archive log current command).

5. After the archive redo log switch completes, RMAN creates an Archived Log backup set (BS Key - 3) of the remaining archived redo logs (using the backup archivelog all command). This is backup piece backup_db_ORA920_S_6_P_1_T_543443693. Keep in mind that this archived redo log backup set includes the redo logs that occured during the full database backup.

6. Finally, the autobackup of the control file and SPFILE occurs and creates another datafile backup set (BS Key - 4). This is contained in backup piece c-2542143170-20041128-01. Since a full database backup will always include datafile 1, which belongs to the SYSTEM tablespace, there will always be a backup of the control file and the SPFILE. At this point, all tasks for the backup database plus archivelog delete input; statement have been completed.

7. The delete noprompt force obsolete; statement is then run to apply the retention policy we have configured. In our example, the first Archive Log backup set (that was performed at the begining on this example) will be removed. This was piece backup_db_ORA920_S_4_P_1_T_543443326.


RMAN Full (online) Database Backup Log File

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA920 (DBID=2542143170)
using target database controlfile instead of recovery catalog

RMAN>

Starting backup at 28-NOV-04
current log archived (Step 1)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting archive log backupset (Step 2)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=89 recid=1 stamp=543441301
input archive log thread=1 sequence=90 recid=2 stamp=543443042
input archive log thread=1 sequence=91 recid=3 stamp=543443132
input archive log thread=1 sequence=92 recid=4 stamp=543443258
input archive log thread=1 sequence=93 recid=5 stamp=543443325
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s89.dbf recid=1 stamp=543441301
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s90.dbf recid=2 stamp=543443042
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s91.dbf recid=3 stamp=543443132
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s92.dbf recid=4 stamp=543443258
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s93.dbf recid=5 stamp=543443325
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset (Step 3)
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u06/app/oradata/ORA920/system01.dbf
input datafile fno=00010 name=/u06/app/oradata/ORA920/users01.dbf
input datafile fno=00002 name=/u06/app/oradata/ORA920/undotbs01.dbf
input datafile fno=00011 name=/u06/app/oradata/ORA920/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/ORA920/example01.dbf
input datafile fno=00003 name=/u06/app/oradata/ORA920/cwmlite01.dbf
input datafile fno=00004 name=/u06/app/oradata/ORA920/drsys01.dbf
input datafile fno=00005 name=/u06/app/oradata/ORA920/odm01.dbf
input datafile fno=00006 name=/u06/app/oradata/ORA920/xdb01.dbf
input datafile fno=00008 name=/u06/app/oradata/ORA920/indx01.dbf
input datafile fno=00009 name=/u06/app/oradata/ORA920/tools01.dbf
input datafile fno=00012 name=/u06/app/oradata/ORA920/users02.dbf
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 comment=NONE
channel ORA_DISK_1: starting piece 2 at 28-NOV-04
channel ORA_DISK_1: finished piece 2 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:50
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
current log archived (Step 4)
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset (Step 5)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=94 recid=6 stamp=543443692
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s94.dbf recid=6 stamp=543443692
Finished backup at 28-NOV-04

Starting Control File and SPFILE Autobackup at 28-NOV-04 (Step 6)
piece handle=/orabackup/rman/ORA920/c-2542143170-20041128-01 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-04

RMAN>
RMAN retention policy will be applied to the command (Step 7)
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 28-NOV-04
Backup Piece 1 28-NOV-04 /orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326
deleted backup piece
backup piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 recid=1 stamp=543443326
Deleted 1 objects


RMAN>

Recovery Manager complete.


UNIX RMAN Backup Script

The following is a short UNIX script that can be used to perform the RMAN backup example we used earlier in this article.

Simple RMAN Full Database (online) Backup Script

#!/bin/ksh

rman target backup_admin/backup_admin nocatalog msglog rman_full_backup_db_online_ORA920.log EOF
backup database plus archivelog delete input;
delete noprompt force obsolete;
exit;
EOF

exit
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Blog Archive