Architecture -2

1. What are the types of grants? Difference between GRANT/ADMIN Option?
   a) System Privilege – (Allow or give permission to a user to perform a particular db operation like to create table, views, synonyms, materialized view, sequences). It is the right to perform a particular action
   Object Privilege – (Table level) Giving permission to user like Select,insert,update,delete,alter,index on tales and views and execute the procedures, functions and packages.
   b) Grant Option – We can grant only Object level privilege not system privilege (with grant option), Objects to users,
   Admin Option –Grant with admin Option i.e., DB Access to user.
2. What is password verification function? Steps to implement it.
   a) To examine the Password when they are chosen and accept or reject them based, we can create special password by using PASSWORD_VERIFY_FUNCTION
   b) STEPS TO IMPLEMENT:
   => Login to db as sys
   =>SQL> @?/rdbms/admin/utlpwdmg.sql;
   =>Then Create user with complex password (Ora@123)
   • Make sure the Password and username are different
   • New password differs from previous by 3 characters
   • Should be made up of alphabetic, numerical, and special characters
3. Will discuss about profiles? Try to learn Password Resources
   Profiles are, set of resource limits, used to limit system resources a user can use. It allows us to regulate the number of resources used by each database user by creating and assigning profiles to them.
   Whenever you create a database, one default profile will be created and assigned to all the users you have created. The name of the default profile is DEFAULT.
   Kernel Resources
   sessions_per_user — Maximum concurrent sessions allowed for a user.
   cpu_per_session — Maximum CPU time limit per session, in hundredth of a second.
   cpu_per_call — Maximum CPU time limit per call, in hundredth of a second. Call being parsed, executed and fetched.
   connect_time — Maximum connect time per session, in minutes.
   idle_time — Maximum idle time before the user is disconnected, in minutes.
   logical_reads_per_session — Maximum blocks read per session.
   logical_reads_per_call — Maximum blocks read per call.
   private_sga — Maximum amount of private space in SGA.
   composite_limit — Sum of cpu_per_session, connect_time, logical_reads_per_session and private_sga.
   In order to enforce the above kernel resource limits, init parameter resource_limit must be set to true.
   SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;
4. What is Snapshot too old error?
   While using the same SQL query from the different users, both users had accessed a table, the expected data not getting from undo-tablespace due to user1 updating a data in a table, at that time user2 is fetching the old data that is before updated data by user 1. That time user2 facing SNAPSHOT TOO_OLD_ERROR
5. What is different between lock and deadlock?
   Same table, different user access – lock
   Different table, different user access – deadlock
6. How will you multiplex Control file and logfile? How to increase the size of logfiles and datafiles?
   a) For Control file:
   1. Multiplexing using spfile(Online)
   SQL> alter system set control_files = ‘control01.ctl’,’control02.ctl’,’control03.ctl’ scope = spfile;
   Then Copy old control file to newly created file name then bring uo the db
   2. Multiplexing using pfile(offline)
   In Pfile, add controlfile with path then cp old ctl file to new ctl file
   Then bring up db with startup mount pfile =’PATH’;
   For logfile: Adding the logfile member in a group à alter database add logfile member ‘path’ to group 1;
   b) 1. We can’t increase the size of logfiles, only we need to create a New logfile.
   2. Datafile resize => alter database datafile ‘path’ resize 100m;
7. What is the use of UNDO tablespace? How to change the name of undo tablespace and location?
   a) Use of UNDO TBS:
   • To maintain the old data’s
   • Data consistency
   b) We can’t change the undotbs name, we can create new undo and set as default undotbs, drop the old undotbs.
   SQL> Create undo tablespace undotbs1 datafile ‘/path/location’ size 100m;
   SQL> alter system set undo_tablespace = undotbs1 scope=both;
8. What is the use of temp tablespace? How to change the location of temp tablespace?
   a) • Temp tablespace is used for sorting purpose.
   • It cannot contain any permanent object
   • It is used to manage space for sort operations
   b) SQL> Create temporary tablespace temp2 tempfile ‘path/temp2.dbf’ size 100m;
   SQL> alter database default temporary tablespace temp2;
9. Steps to create dbms scheduler? How to check the failed job / status?
   a) begin
   dbms_scheduler.create_job
   (job_name = ‘bat’,
   job_type = ‘stored_procedure’,
   job_action = ‘p1’,
   start-date = systimestamp,
   repeat_interval = ‘freq=minutely’,
   end_date = null,
   enabled = true);
   end;
   /
   b) desc USER_SCHEDULER_JOB_RUN_DETAILS
   SQL> select job_name, status, owner, error# from USER_SCHEDULER_JOB_RUN_DETAILS;
10. What is use of flashback query /version/transaction/table?
    • Use of Flashback query – Flashback Query allows users to see the view of past data, if in case some data or table is being deleted by the user, then the flashback query provides us an opportunity to view that data again and perform manipulations over it.
    • Use of Flashback version query – You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed. You specify a Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement.
    • Use of Flashback Transaction query – Oracle Flashback Transaction Query – retrieve metadata and historical data for a given transaction, or for all transactions within a given time interval. You can also obtain the SQL code to undo the changes to particular rows affected by a transaction.
    • Use of flashback table – Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.
11. What is UMASK?
    When user creates a file or directory it will be created with permission. The default value is 022.
    If we create a file or directory the file will be created with 755 permissions.
    the calculation is the difference between 777 and the umask value.
    i.e., 777-022=755.
    If umask is 022 then
    Owner will have read, write, execute permissions
    Group will have read, execute permissions
    Others will have read, execute permissions
12. How will find available port?
    $ netstat -plunt
    $ netstat -tunlp |grep portnumber
13. How to delete files that are older than 10 days?
    $ find. –mtime +10 exec rm {} \
14. Explain the logic for monitoring alert.log, filesystem check.
    • The alert log is a chronological log of messages and errors, and includes the following items:
    • All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) that occur
    • Administrative operations, such as some CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements
    • Messages and errors relating to the functions of shared server and dispatcher processes
    • Errors occurring during the automatic refresh of a materialized view
    • The values of all initialization parameters that had nondefault values at the time the database and instance start
    
15. How will you pass arguments to a shell script? How will you call them inside the shell? how to check how many numbers of arguments passed and list out them?
    a) $$ is the pid (process id) of the shell interpreter running your script.
    b) $? is the exit status of the most recently-executed command; by convention, 0 means success and anything else indicates failure.
    c) echo $ # echo command in Linux is used to display line of text/string that are passed as an argument.
16. How to check the previous command that was execute is successful?
    $ echo$?
    if the result is 0(zero) then the command is successfully executed else if the number is from 1 to 255 it is failed.
17. How to check the following?
    => IPaddress – hostname-i
    => Servername – uname
    => OS release – cat /etc/os-release
    => CPU count – cat /proc/cpuinfo (or) lscpu
    => Ram size – cat /proc/meminfo
    => users groupname – id
    => CPU utilization – top (Provides a dynamic real-time view of a running system, including information about system resource usage and a constantly updated list of the processes which are consuming the most resources. Because it is so useful for administration, we will talk quite a bit about top in chapter 8)
    => IO utilization – iostat (Used for monitoring the load on system input/output devices by observing the time the devices are active compared to the average transfer rate of the device)
    => virtual memory utilization – vmstat (Displays information about processes, memory, paging, block IO, and different levels of CPU activity CPU Related Information)
18. Which command is used to find the process that was locking/using the file?
    $ Free -k
19. which command is used to find the user who is using the file?
    $ Free -k (lsof filename)
20. How to change the ownership of a file?
    $ chown -R oracle:oinstall filename