Thursday 30 August 2012

SQL Interview Questions and Answers Partt4

                                                     
                                                                    
                                                                    
                                            
1) backup database incremental level 0, what are the files will be part of the backupset?
  Datafiles and control files.
  

2) What does crosscheck command does?
Updates the catalog whether the piece does exist or not.

3) steps to clone a db using Rman? also suppose source and target directory structures are different? how would you proceed further
  
 a) take a full backup of the database
 b) move the backups to the dev server (Make sure you move to the same file-system as in Source database)
 c) create the auxiliary instance by specifying the required parameter values.
 d) Then run the below duplicate command by connecting to target as well as auxiliary database:
           rman nocatalog target monitor@TFSSG_SGDC01APUX001-TFSPROD1 auxiliary/
           run{
           set until time "TO_DATE('11/06/09 06:05','MM/DD/YY HH24:MI')";
           duplicate target database to tfsprod1 nofilenamecheck; }

    If there are different directory structure, then use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT accordingly........
          
 

4) Improving the performance of an Rman backup

  a) Use multiple channels
  b) use block change tracking feature
  c) enable the backups during non bussiness hours

5) What is the purpose of VIP

   VIp (Virtual IP address) which gets switched over to another node during the failure of active node. The concept of VIP comes in the clustered servers (Eg : VCS)
   The advantage is user gets connected to the passive node with less down time.

6) 10 Db's on a RAC server, want to find what db's are running. What commands do u use

  $CRS_HOME/bin/srvctl status database -d <name>

7) Steps to set up a data guard

   a) Take a backup of the database
   b) backup of the control file and pfile too
   c) Move the backups to the DR server.
   d) configure the following parameters db_file_name_convert, log_file_name_convert, standby_file_management, standby_archive_dest, fal_server and fal_client
   e) Startup nomount and alter database mount standby database
   f) Enabled managed recovery -----> alter database recover managed standby database cancel;
   g) To ensure that the DR is in managed recovery fire this command ----> select process,sequence#,status,blocks,block# from v$managed_standby;
   i) To ensure the DR is in sync with the primary fire the below query
   
    Get the current log sequence of the primary and at the standby site execute this command select max(sequence#),thread# from v$log_history group by thread#; 


8) U want to refersh a dev database with a production db, due to file-system naming mismatch between prod and dev, how would you move the backup pieces and
    recover the db?

   1 method:

   a) Take the backup of the database.  
   b) Move the backup pieces to the develeopment server by creating soft links, so that the location between the prod and develeopment server looks similar
   c) startup the db in nomount stage
   d) duplicate the database as specified in question 3

  2 method

   a) Take the backup of the database
   b) move the backup pieces to some location on the development server
   c) startup the db in nomount stage
   d) At the Rman prompt create the control file -------> restore controlfile from 'location of the piece containing the control file';
   e) bring the db to mount state -------> alter database mount;
   f) Again at the Rman prompt catalog the backup pieces in the control file -------> catalog start with 'location of the backup pieces';
   g) after cataloging fire ----> restore database and recover database.... use the below command

      rman > run { restore database ; recover database ; }

11) Apps team require a tablespace to be created OF a non standard block size, hoe would you configure

Define a parameneter db_nk_block_size used to store the blocks of non default size in the buffer cache..... after configuring this parameter create a tablespace of the specified block size.....


13) If a data file is added at the primary site, how the change will be reflected at the DR site

If the standby_file_management parameter is set to AUTO then the datafile gets created automatically at the DR site else we need to create manually....

14) Index is being rebuilt at the primary site with no logging option, whatz the effect at the DR site.

Cause a logical corruption at the DR site.... hence we need to rebuild the DR database.......

15) How do you identify and resolve gap sequences at the DR site

Use v$archive_gap and v$archived_log and v$log_history to see the archive gap..... if the archive logs are missing at the primary site then try to restore the archive logs so that the DR will catch up with those archive logs.......

16) What is FAL_SERVER and FAL_CLIENT parameter. What is the advantage of setting the same parameter at the primary site

FAL_SERVER ---> specifies the location of the primary server
FAL_CLIENT ----> specifies the location of the DR site...... basically these parameters are configures to resolve the gaps automatically......

The adavantage of setting this parameter at the primary site is for the ease of siwtchover and switch back....... nothing more.......

17) Difference between Datapump and conventional export/import

    a) Run the export in parallel.... can also increase the parallel threads on the fly....
    b) stop the export and imporrt at will and then restart the job.
    c) The staus of the expdp/impdp is transparent to the user...... just attach the job and we will get to know the status of the job
    d) Filter of the objects is much user friendly..
    e) server side utility.
    f) Over the db link we can import the data without actually creating the export dump ----> network_link is the parameter that is used
    g) without actaully performing the export we can get to know the size of the dump....

18) How do you identify whether the DB is primary or a standby database?

select controlffile_type from v$database;

If primary then it is a primary database
If standby then it is a standby database

19) Standby database is lagging by 100 logs, how do you fix to get the DR in sync with the primary

check if we can restore the archivelogs at the primary site...... unfortunately if the archivelogs are not in the backup, then we need to apply the incremental backups to the standby database to get the DR in sync with the primary.....

Applying the incremental backup's is a new feature of 10g.... the main advantage of this feature is we can get the DR in sync pretty fast rather than rebuilding the entire database which is a time consuming task....

20) What is Ora-01555

Snapshot too old....... meaning -----> when the read consistent data is being over written by another active session, then is such situations the user who is getting a read consistent data will be prompted with the error message ----> ORA-01555

Remedy----- > increase the undo tablespace as well as undo_retention parameter


21) How do you remove the shared memory segment from the operating system

ipcs -rm

22) What is FRA feature

Flash recovery area used to store the Rman backup's as well as falsh back logs so that we can flash back the database to a particular time....

PArameters configured to enable FRA is --> db_recovery_file_dest and  db_recovery_file_dest_size and db_recovery_retention_period......

23) What is Flash back database

Flash back database is a kinda rolling back the database using Flash back logs...... New feature in 10G

24) How do you check the max sequence# applied at the DR site

    a) select max(sequence#),thread# from v$log_history group by thread#;
    b) check the alert log file

25) Parameters configured at the Primary/DR site in a Dataguard Environment

PRIMARY SITE ----> log_archive_dest_2, db_unique_name
DR SITE -----> FAl-SERVER, FAL_CLIENT, STANDBY_FILE_MANAGEMENT, STANDBY_ARCHIVE_DEST, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT

26) How do you check the load on a server

use the below OS commands
   top ----> In Linux
   /usr/ucb/ps aux | more ----> In Sun OS
   prstat  ---- > In Sun OS


  
27) Archive dest is configured in the FRA, how do u clear the same when the destination is full

  a) increase the FRA size at first instance to avoid outage of the database...
  b) If the archivelogs are backed up and also if the same has been applied in the DR (If configured), then remove the old archivelogs using RMAN

 Rman ;> delete archivelog all completed before (sysdate -1);

28) What is TAF

Transparent application fail over.... where the session gets failed over to the available instance in case of RAC

29) U wanna do some maintanence on a RAC cluster database... what are the steps to bring down the CRS resources

  $CRS_HOME/bin/srvctl crs_stat -t to see the services that are currently running.....

    a) $CRS_HOME/bin/srvctl stop database -d <db_name>
    b) $CRS_HOME/bin/srvctl stop service - d <db_name>
    c) $CRS_HOME/bin/srvctl stop asm -n <node_name>  (To get the node names in cluster fire this command $CRS_HOME/bin/srvctl olsnodes)
    d) $CRS_HOME/bin/srvctl stop nodeapps -n <node_name>
    e) As root user stop the CRS stack -----> crsctl stop crs

30) FRA is 100%, how wuld u fix the issue

Increse the db_recovery_file_dest_Size parameter

31) At the OS level how wuld u find the CPU usage

   To see the CPU idleness ------>   sar 10 10

32) How would u check whether the standby database is in managed recovery mode

select process,sequence#,status,blocks,block# from v$managed_stqandby;

33) Datafile is corupted, how to fix it

Try to recover the corrupted block throo block media recovery..... at the worst case try to restore the datafile and recover it.....

34) Steps to upgrade a DB from 9i to 10G

35) what is a nodeapps

No comments:

Post a Comment