Cognizant interview questions

Hi,

Q 1)What are the difference between RAID 0 ,RAID 1 ,RAID 0+1,RAID 1+0,RAID 5 & RAID 10?(Cognizant)
Ans: Please refer my post for RAID types


Q 2)HOW YOU TAKE BACKUP IN ASM?(Cognizant)

Ans: We have to use RMAN for taking ASM backup.By using backup as copy command we can do this.
Eg:
TEST USING TRANSPORTABLE TABLESPACES, STEP BY STEP
1. Create tablespace accounts
2. Create table accounts on tablespace accounts, insert some records on it
3. Create a Directory for Data Pump and grant privileges on it to Public
4. Check that the tablespace can be part of a transportable tablespace set
5. Flush online logs to disk
6. Set tablespace accounts read only
7. Export tablespace accounts using data pump
8. Set tablespace accounts offline
9. Backup the tablespace using Rman backup as copy set keep until sysdate + 2 years
10. Drop tablespace accounts including datafiles
11. Confirm that you cannot access the table on the dropped tablespace
Restore tablespace steps
12. Restore the datafile backup to ASM
13. Import tablespace accounts back into the database using data pump
14. Set tablespace accounts online and check the control table

1. Create tablespace accounts
SQL> create tablespace accounts;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='ACCOUNTS';
FILE_NAME
-----------------------------------------------

2. Create table accounts on tablespace accounts, insert some records on it
SQL> conn avargas/oracle
Connected.
SQL> create table accounts tablespace accounts as select * from dba_users;
Table created.
SQL> insert into accounts select * from accounts;
15 rows created.
SQL> /
30 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from accounts;
COUNT(*)
----------
60
3. Create a Directory for Data Pump and grant privileges on it to Public
SQL> create or replace directory XTTS as '/oradisk/app01/oracle/scripts/av';

Directory created.
SQL> grant read, write on directory XTTS to public;
Grant succeeded.
4. Check that the tablespace can be part of a transportable tablespace set
SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('accounts',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
5. Flush online logs to disk
SQL> alter system archive log current;
System altered.
6. Set tablespace accounts read only
SQL> alter tablespace accounts read only;
Tablespace altered.
7. Export tablespace accounts using data pump

{oracle} /oradisk/app01/oracle/scripts/av [pollux.com] > expdp system/oracle
dumpfile=xtts.dmp directory=XTTS transport_tablespaces=accounts
Export: Release 10.2.0.3.0 - Production on Thursday, 03 January, 2008 21:34:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

8. Set tablespace accounts offline
SQL> alter tablespace accounts offline;
Tablespace altered.
9. Backup the tablespace using Rman backup as copy
Note that in order to be able to setup the keep time for a period longer than the setup of the Flash Recovery Area
we must make this backup outside the Flash Recovery Area
RMAN> backup as copy datafile '+DB1DATADG/redx/datafile/accounts.287.642998077
2> tag accounts_DF_CP
3> format '/oradisk/app01/oracle/scripts/av/%U'
4> keep until time='sysdate + 730' nologs;
Starting backup at 03/01/2008 21:44:33
using channel ORA_DISK_1
backup will be obsolete on

10. Drop tablespace accounts
SQL> drop tablespace accounts including contents and datafiles;
Tablespace dropped.
11. Confirm that you cannot access the table on the dropped tablespace
SQL> select count(*) from accounts;
select count(*) from accounts
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select tablespace_name from dba_tablespaces where tablespace_name='ACCOUNTS';

12. Restore the datafile backup to ASM
On this step we need to use the Rman copy command instead of restore. Restore do require that the tablespace
exist, and we did drop to it as part of the TTS procedure.
Take note of the output filename given by Rman; you will need to use this name on the next step
RMAN> copy datafilecopy '/oradisk/app01/oracle/scripts/av/data_D-REDX_I-
2305343033_TS-ACCOUNTS_FNO-6_0pj58r62' to '+DB1DATADG';
Starting backup at 03/01/2008 22:15:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00006: /oradisk/app01/oracle/scripts/av/data_DREDX_
I-2305343033_TS-ACCOUNTS_FNO-6_0pj58r62
output filename=+DB1DATADG/redx/datafile/accounts.287.643068939
tag=ACCOUNTS_DF_CP recid=21 stamp=643068957
channel ORA_DISK_1: datafile copy complete,

13. Import tablespace accounts back into the database using data pump
[pollux.com] > impdp system/oracle dumpfile=xtts.dmp directory=XTTS
transport_datafiles='+DB1DATADG/redx/datafile/accounts.287.643068939'
Import: Release 10.2.0.3.0 - Production on Thursday, 03 January, 2008 22:18:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.

14. Set tablespace accounts online and check the control table
On the previous check our current log sequence was 27, now we got to sequence 29. This check is made to be
sure that despite the database advanced its scn we will not require to do recover of the offline tablespace when
seting it online again.
SQL> alter tablespace accounts online;
Tablespace altered.
SQL> select count(*) from avargas.accounts

Q 4)WHAT IS CLIENT SIDE AND SERVER SIDE LOAD BALANCING IN RAC?
EXPLAIN BRIEFLY IMPLEMENTATION?(Cognizant)
Ans:Client Side Load Balancing is by definition “evenly spreads new connection requests across all listeners”, and “Oracle Database randomly selects an address in the address list and connects to that node’s listener. This provides a balancing of client connections across the available listeners in the cluster.” [source]. Another definition, “whereby if more than one listener services a single database, a client can randomly choose between the listeners for its connect requests. This randomization enables all listeners to share the burden of servicing incoming connect requests.” [source].
Client Side Load Balancing is configured by adding LOAD_BALANCE=ON in tnsnames.ora file. The Net Services Reference tells us how and were to embed this parameter – “embed this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.” [source]. Note however, whether you place LOAD_BALANCE under ADDRESS_LIST or outside, will make a different in terms of load balancing ADDRESS_LISTs or load balancing ADDRESSES.

Configuration
I have a 2-node 10g RAC cluster, and for load balancing, I have the following entry in my tnsnames.ora file.
MODDB=
(description=
(load_balance=on)
(address=(protocol=tcp)(host=moddb1-vip)(port=2521))
(address=(protocol=tcp)(host=moddb2-vip)(port=2521))
(connect_data=
(service_name=MODDB)
)
)
I want to trace the Client Side Load Balancing behaviour from the client side, so I enable Oracle net tracing by adding the following lines in sqlnet.ora [source], and I should fine trace files for each physical connection made under $ORACLE_HOME/network/trace.
TRACE_LEVEL_CLIENT = USER
TRACE_FILE_CLIENT = SQLTRC
Testing a connection
We can first test one connection…
$ sqlplus -s tester/tester@MODDB

exit
Now take a look at the trace file produced, under $ORACLE_HOME/network/trace. An inspection of of the trace file shows two entries that are of interest (it is possible to observe server-side load balancing as well, but that’s a topic for another post):
view source

print?
1 niotns: Calling address: (description=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb1-vip)(port=2521))(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))
2 nsc2addr: (DESCRIPTION=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))
Unfortunately, I couldn’t find much documentation on how to read a trace file, I can only guess…
* niotns entry shows a lookup of tns information based on the service name I supplied “MODDB”.
* nsc2addr entry shows Oracle Net connects to listener on moddb2-vip.
It’s difficult to observe load balancing with just one connection, so I wrote a script to run 1000 connections, using Bash Shell scripting:
#!/bin/bash

for a in {1..1000}
do
echo $a
sqlplus -s tester/tester@MODDB<EOF
done

exit 0
Examining the traces
Extending the idea above, we can use a combination of grep, and wc to see the result of load balancing. For some reason, each connection produced 2 trace files, so we have 2000 trace files for 1000 connections.
$ ls -l *.trc |wc -l
2000

$ grep nsc2addr *.trc | grep load_balance |grep moddb1-vip |wc -l
498

$ grep nsc2addr *.trc | grep load_balance |grep moddb2-vip |wc -l
502
We can see over 1000 connections,498 were made to moddb1-vip listener, whilst 502 were made to moddb2-vip listener. It is a fairly even distribution. But just how random is it? I have used the output of the trace files, and plot the first 100 connections on a scattered chart. The first note one can observe is that, these connections are not in a round-robin fashion. (1 = moddb1-vip; 2 = moddb2-vip)
LOAD BALANCING: The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.

There are two methods of load balancing in broad
1.Client load balancing
2.Server load balancing

Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.

Configure Client-side connect-time load balancing by setting LOAD_BALANCE=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.

Configure Server-side connect-time load balancing feature by setting REMOTE_LISTENERS initialization parameter of each instance to a TNS name that describes list of all available listeners.

TESTRAC_LISTENERS =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))
)

Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.

Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.

FAILOVER:

The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.

There are two methods of failover
1. Connection Failover
2. Transparent Application Failover (TAF)

Connection Failover - If a connection failure occurs at connect time,the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.

Enable client-side connect-time Failover by setting FAILOVER=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.

Transparent application Failover (TAF) – If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover(i.e SELECT FAILOVER can be saved in RAC and No DML failover are saved).

TESTRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb.oracleracexpert.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)

Q 6)SPECIFY THE ORDER FOR STOPPING & STARTING FOR CLUSTERWARE ,NODES AND DATABASE(SPECIFYING APPS)(Cognizant)

Ans: In Oracle 10g rel2, First we have to have to,Stop Database than ASM,than NODE Application if we are stopping Cluster component wise,As we know clusterware is the base for Database and ASM,We can stop all the component at once using 'crsctl stop crs' command.In the below example we are having OCFS2 cluster filesystem and ASM is not used.

The ORDER IS AS FOLLOWS:

Steps : For Stopping the things:

Step 1:First the applications should be shutdown

The corresponding service should be down in order to make specific application down.

Step 2:Database should be shutdown

# status
srvctl status database -d AUCD

# stop
srvctl stop database -d AUCD

Step 3:Nodes should be shutdown

# status
srvctl status nodeapps -n dbcl1n1

# stop
srvctl stop nodeapps -n dbcl1n1

# status
# Run as root or you should have SUDO permissing in /etc/sudoers file.

ssh dbcl1n1 /home/app/crs/bin/crsctl check crs

Details below:

([oracle@dbperf01 AUCTX1 ~]$ cd $ORA_CRS_HOME/bin
[oracle@dbperf01 AUCTX1 bin]$ pwd
/home/app/crs/bin

[oracle@dbperf01 AUCTX1 bin]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

(0R)
Step II):The whole Clusterware COMPONENT can be shutdown:
$ sudo /home/app/crs/bin/crsctl stop crs =>Stops all the clusterware components i.e
services,Instances,Node Applications will be stop

Because This command should be run as root user as we do not have/use root user password(see the email Below) But , oracle accout has the "sudo" privelge, so just add "sudo" keyword in front of that command

The ORDER for Starting clusterware component IS AS FOLLOWS:
For Starting things:

Step I) Start Clusterwareware Components:

Note:When any host in the cluster boots,Clusterware should be started.

The scripts listed below help make this happen:
/etc/inittab
/etc/rc5.d/S96init.crs
/etc/init.d/init.crs
/etc/init.d/init.crsd
/etc/init.d/init.cssd
/etc/init.d/init.evmd
Be aware of this for 2 reasons:
1. It may be unneccesary for you to start Clusterware. It might already be started.
2. The script, /etc/init.d/init.crs, can be used to start Clusterware if you want to use it.
To start Clusterware manually, maybe use this script instead of init.crs:
To start Clusterware manually, maybe use this script instead of init.crs:
#!/bin/sh
# Run as root
# start
ssh dbcl1n1 /home/app/crs/bin/crsctl start crs
or
$ sudo /home/app/crs/bin/crsctl start crs =>Starts all the Clusterware components,
i.e Sevices,Instances and Node Applications will be started

(OR)

Step II
Step 1: Start nodeapps

After you start Clusterware, you may start nodeapps.
Use this script to start nodeapps:
#! /bin/sh
# start
srvctl start nodeapps -n dbcl1n1

Step 2:Start Database

After you start the nodeapps, you may start the Databases.
Use this script to start the databases:
#! /bin/sh
# ps
ssh dbcl1n1 ps -ef | grep dbw

# status
srvctl status database -d AUCD

# start
srvctl start database -d AUCD

Step 4:Start the application after starting the Database

Hope this questions help everyone.

Happy INTERVIEWS

Best regards,

Rafi.