Oracle Faqs, Prepared by MAK

1. What is the total blocks in the Cache size on Linux and Windows?

A). Linux --- 48MB, Windows ---52 MB

2. What is the default shared-pool-size?

For 32 bit 16MB and 64 Bit 64 MB

3. What is the default large-pool-size?

Zero

4. How to calculate the Large-pool-size?

Set PARLLEL_AUTOMATIC_TUNNING = TRUE Parameter in INIT.ORA file....It will Automatically calculate.

5. What is Default Java pool size?

24MB

6.  If the SGA is less than 128 MB in total, Each kranule is 4MB...if the SGA is greater than 128MB in total, each kranule is 16MB.

7. How to check the size of our SGA in InIt.ORA file (DBBC,RBC, SP, LP)?

Select Name, value from V$parameter;

8. How to find out the character set in the DB?

NSL_DATABASE_PARAMETERS , NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETRES

9. How to check the ownership?

$ LS - LRT/CHOWN/CHMOD

10. How to check the Background Process?

$ PS -EF /GREP   $ORACLE_SID

11. How to find out which  shell is running currently.

ECHO $ SHELL

12. Why we get 'PERMISSION Denied' or 'ACCESS Denied' when we try to MOUNT a REMOTE file system?

Our remote NFS server is not exporting or sharing its file system. This can be verified by running the show mount command as follows.

# showmount  -e servername

13. I have a slight dought regarding INSTANCE/CRASH recovers?

We know that Instance recovery requires TWO phases.

 a. Rolling Forward or Cache Recovery

 b. Rolling backward or Transaction recovery.

Cause:- The DB applies any  pending updates in the online redo logs to the online datafiles of our database. The result is that , whenever the DB is restarted after crash , the data files reflect all committed changes upto the mount when they have not said failure occurred.  

Rolling backward phase starts after the DB has been Opened.

Shutdown abort

 Startup

14. My alert log file dropped by mistake what will happen?

Nothing will happen. Oracle will automatically create a new alert log file.

15. If we shouldn't set the bdump, alert log files where will be created?

$ORACLE_HOME/rdbms/trace

16. As we know, when DB suddenly crashes, not all the committed transactions will have been written to the disk. if our DB is large and redo log files are also large...it can take a long time for the roll forward and roll backward process to complete. How will you handle this situation?

By using Oracle's 'Fast start fault recovery   functionality, we can substantially reduce the downtime.

Alter database set FAST_START_MTTR_TARGET = 60;     3600---sec or 60 minutes in V$instance_recovery

17. How to check Linux version?

Uname - r or cat / proc / version

18. What to do if my global inventory is corrupted?

No need to worry , we can re create global Inventory on machine using universal installer and attach already installed oracle home by option.

/ runinstaller_silent_attachhome_invptloc  orainst.loc

19. Can we have multiple Global Inventory on Machine?

Yes, but upgrading or applying patch then change Inventory pointer Orainst.loc

20. What are the types of inventory's we have?

We have three types of inventory's.

Global Inventory: It holds information about oracle product on a machine. Like database, oracle application server, collaboration suite etc.

OraInst. loc

Local Inventory: This Inventory holds information to that oracle_home only

orainventory directory.

Oracle   home Inventory : It's having Oracle home information and other folder information.

$Oracle_home / inventory

21. We are maintained one stage directory in installation.

22. What is semophore ?

Semophores structures that control access to the operation system memory.

Semophore is a machanism of inter process communication. Semophore allow oracle server process to stop and wiat and then notified, when they should resume processing.

23. List few kernel parameters?

shmmax---shared memory max

shmmni---shared memory mini

file_max and shmall

24. How to check the kernel parameters installed or not?

# /sbin/sysctl -p

# /sbin/sysctl -a /grep shm

24. How to check who are all the users attached to group?

cat/etc/group

cat/etc/passwd

25. How to check the user dump?

Show parameter user_dump_dest

26. How to check the all dump locations?

show parameter dump_dest.

27. How to check parameter an SGA files?

select name, value from V$SGA;

desc  V$instance        desc V$database

27. How to find out which background process is running at SQL /OS level?

Operating system level : ps-ef /grep sudha (DB name)

ps -ef/grep $oracle_SID

Sql Level : Select * from V$bgprocess;

28. How to find running database lists?

ps -ef /grep PMON ( it will show only up database names)

29. How to find database, instance names?

At OS level : echo $oracle_SID;

Sql level: Select name from V$database;

30. Describe few SGA related parameter views?

Desc V$SGA, Desc V$instance , Desc v$ database, Desc v$ process, v$bgprocess, v$buffer_pool

v$buffer_pool_statistics, v$sgastat

31. Explain different types of dumps?

bdump: bdump contains alertlog file as well as tracefiles, which are generated by the oracle background process.

udump: udump contains user process related trace files. when we take the trace backup of control file, it will be generated in udump.

cdump: cdump contains the tracefiles  which are generated memory leaks.

adump: (audit):

32. Explain  the  data dictionary views?

We have two types of dictionary views.

Static views and Dynamic views.

Static views: Static views starting with dba_, user_ etc

Static views updated only dictionary views. It is used only DB upon running.

Dynamic views: Dynamic views starting with V$. For example: V$database, V$controlfile and V$ instance

Dynamic views updates from control file and memory. It is used DB mount, open and nomount stages.

Startup - shutdown modes:

NO-MOUNT: No mount stage is only used when first time creating of the DB and necessary to re creation of control files.  Startup includes the following:

--> Read the Pfile or SPfile

--> Allocate the SGA

--> Startup the background process

--> Create lert logifle in the bdump and trace file also

--> Read parameter file and start the instance.

For Example: Startup NO MOUNT followed by

--> Cold cloning, Hot cloning, R-Man cloning, Data guard configuration and lose of control file in R-MAN

MOUNT: In Mount stage database mounted but not open.

--> Data base changed No- Archive log to Archive log

--> Renaming DATA files

--> Lose of C/R/D files (Recovery time)

--> Take the trace of control file backup.

Shut down Normal:

--> No new connections are allowed

--> The server waits for all users to disconnect before completing the shut down.

--> Oracle will write all block buffer data and redo buffers to disk.

--> The SGA memory de allocated and back ground process terminate

--> Next start up is not required for instance recovery.

Shut immediate:

--> No new connections are allowed

--> Connected clients are disconnected and sql statements in process are not completed.

--> Oracle rollback active transactions

--> Dismounts the database and closes the database gracefully.

Shut abort:

--> No new connections are allowed.

--> SQL connections are immediately terminated.

--> Connected users are disconnected.

--> Database and Redo buffers are not written to disk.

--> Un committed transactions are not roll back.

--> next start up is recovery for instance recovery.

33. What are difference between shut immediate and shut abort?

Shut Immediate                                                    Shut abort

-Oracle Rollback un committed transactions     - Oracle not rollback un committed transactions

        It means Active transactions                             it means Active Transactions

-Next startup not required for Recovery            -Next startup required for recovery

-SQL statements in process not complete          -SQL statements immediately terminated.

-No new connections are allowed                      - No new connections are allowed

-Connected users are disconnected                    -Connected users are disconnected.

34. Explain about PGA?

PGM means Program Global Area. It is a part of memory allocated that is outside of the oracle instance. The PGA stores data and control information for a single server process or single background process.

PGA is used for sorting purpose, it combines cursor area, stack space, session info and sort area.

sort_area_size = 64k (Default) or hash_area_size.

35. SGA in 10g dynamic why?

The purpose is to speed up query performance and enable high amount of concurrent of database activity.

36. What is your current SGA size?

20GB, it is not permanent, updated daily transactions SGA size increased day to day transactions.

37. How to find out whether oracle installed or not from SQL level and OS level?

OS level: $ORACLE_HOME/opatch is inventory

SQL level: Select * from V$version;

38. How to check the status of instance / database in sql level?

Select status from v$instance;

select status from V$database;

39. How to find out how many Data bases consist in one server?

Up data bases (currently running DB): ps-ef/grep PMON;

All data bases: /etc/oratab

40. What are the memory related parameters?

SGA (System Global area) and PGA (Program global area)

41. How to login data base?

Through Putty

42. How to check the DB Mode?

Select open_mode, name from V$Database;

--From nomount to mount oracle is going to scan the control file for the physical locations of the data files and online redo log files. Oracle will not go the physical locations for the existence of the file.

--From Mount to Open oracle perform sanitary check which is nothing but checking for the synchronization of SCN numbers as well as oracle checks for the  physical existence of the files. The entire secrete of oracle lines on SCN number. Oracle generate SCN numbers for every three seconds. When we shut down and startup the database oracle generate SCN numbers and update in all data file headers.

> Desc V$Datafile_header

43. How to find out the overall DB size?

Select sum(bytes)/1024/1024 from sys.sm$ts_free/avail/used

select sum(bytes)/1024/1024 from dba_data_files;

--when we create the database , run on DB crt script ....first created by:

                                -Control file

                                -Redo log file

                                -System table space

                                -Undo table space

                                -user Table space

                                -Sysaux Table space

--Temporary Table space size --6GB

--Sysaux Table space size --3GB

--System table space size--4GB

45. What backup servers are you used?

TSM (Tivoli Storage manager), veritas backup

46. What is Error-16055?

This error occurred when a archive gaps and mountpoints are full. Please view the parameter log_archive_dest and the problem might me archived log is not available for some reason, so the process cannot see the file and cannot get it across to the standby site

47.  What is error-01653?

http://www.opssys.com/InstantKB/article.aspx?id=10794

CAUSE:Typically occurs when writing a new record to oracle database, but there is not enough space to write it. The data file in oracle tablespace called OPSMASTERSTORAGE should be set to AUTOEXTEND. If it is not, Oracle will not auto-increase the size of the datafile, and will raise the above error.

SOLUTION: WARNING: This procedure should be performed by your ORACLE Database Administrator.
The SYSTEM user should have required privledges to perform this task.  Enable Autoextend on the OPSMASTERSTORAGE tablespace by executing the following Oracle PL/SQL command:

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORA817\OPSMASTERSTORAGE.ORA' AUTOEXTEND ON NEXT 1024K

48. What is $free -m ?

Memory total used, free, shared and buffer cache information is displays.

49. What is the use of TOP command?

It will provide all information like users, load average, tasks, running process, CPU % memory total used and free memory information and swap: ok used free.

50.  What is the use of # init O command?

We can use this command to shut down the instance.

51. What is the use of # init g?

We can use this command to restart the instance.

52. Can we create a database without sysaux/Undo table space?

Yes, we can create in the 9i, but 10g on words it's not possible because of both are mandatory.  

53. Shall we create a data base without log files?

Yes, we can create, oracle is created by default.

54. How to find out server name / IP address?

cat /etc/hots 0r #ipconfig -a , hostname -i

55. How to find out only directories in Linux?

ls -d */

56. How to set the password to logon to the OS to SQL in database?

Set one parameter $ ORACLE_HOME/network/admin/sqlnet.ora

sqlnet.authentication_services = (none)

---ps x ---it will display all back ground process

57. How to find the shared memory management?

]$ipcs

                                ---- shared memory segments ------

key    shmid    owner    perms    bytes    nattch   status

65532 oracle

66332 oracle

77321 oracle

--This is the shared memory segments, if need some oracle used shmid_delete some shmid (65532) , consist of oracle

]$ ipcrm - m 65532

58. What is LOG_CHECKPOINT_INTERVAL ?

Is set to number of operating system blocks that are used before the log switch occurs. For most operating systems , the size of the blocks is 512 bytes.

LOG_CHECKPOINT_INTERNAL = 1MB /512

LOG_CHECKPOINT_TIMEOUT = 600

The parameter LOG_CHECKPOINT_TIMEOUT specify a time interval in seconds in which the checkpoint will occur. This will automatically run the checkpoint process at this interval.

59. What happens when we fire SQL statement in Oracle?

When we fetch the query the user process request passed to the server process, the server process take request from user process between user process server process session will be established. Then it will connect to the instance, in instance first it will check the syntax and semantics in library cache, after that it will create execution plan. The server process checks to see if the desired data in the buffer cache of SGA, if the data in the buffer cache, it retrieved and return to the user. if data not in the buffer cache, the server process reads out of datafiles and put in to the buffer cache, then returned to the user.

60. How to drop database?

--Startup restrict mount

--check DBname > select name from v$database

--drop database

61. What is Row Migration and Row Chaining?

Row Migration: Oracle will try to shift the entire row from current block to another block. It will store the new block row ID into the old block. These is because of oracle's internal mechanism. That's why oracle has to maintain two row id's , one is because of internal mechanism and one is for the current location of the data.

Row Changing: Row changing is storage of data in a chain of blocks. This will mainly occur in the LOB,CLOB,BLOB or BIG varchar 2 data types.

   ---LOB--large Object

  ---BLOB--Binary large object

 ---CLOB--Character large object

62. how to find Row migration / Row changing ?

Oracle has provided the following three methods to create view the statistics of tables / indexes.

 --- Analyze command

>Analyze table <tablename> list chained rows; By executing : $ ORACLE_HOME/rdbms/utlchain.sql

---Dynamic Views

---Report.txt method.  

63.  How to avoid / eliminate Row migration / Row changing?

For avoiding row migration we can use a higher PCTFREE value.

---Temporary Solution:

                                --Analyze the table....to get the row id

                                -- Copy those row to temporary table

                                --Delete the row from the Original table

                                --Insert the row from temporary table to Original table.

64. Explain about the Table space Management?

A Table space is database object and logical storage unit within an oracle database.

                --Collection of datafiles are called Table space

                --Collection of segments are called Table space

 -Types of Table space

                                --Locally managed table space (by Default in 10g)

                                --Dictionary managed table space (Optional in 10g)

-Locally managed table space: Locally managed tablespace that record extent allocation in the tablespace header.

 --Locally managed tablespace made up of 128kb

--Locally managed tablespace is maintained by bitmaps

                ---Coalescing is not required

                ---Each bit corresponds to a block.

-Benefits of LMTS : -Dictionary contention is reduced

                                      -Coalescing is not required (Wastage space removed)

                                      - No rollback is generated

                                      - Fragmentation is reduced

--Two types of LMTS:

                --Auto: Where all the sizes of extents are controlled by Oracle

                --Uniform: All segments are same

--Dictionary managed Tablespace: 

                                -DMTS maintained by the oracle ( like system.dbf)

                                - Record extent allocation in the dictionary are called Dictionary managed tablespace

                                -Coalescing is required (Alter tablespace <TN> coalesce;)

65. What is the ORA-30036 error?

No space in the table space...need to increase the tablespace size

66. Explain the difference between LMTS and DMTS?

                                LMTS                                                   DMTS

-LMTS is maintained by bitmaps                                    - DMTS is maintained by Oracle

-Coalescing is not required                                             -Coalescing is required

-Dictionary contention reduced                                     -Extents are managed in the dictionary

-No Rollback is generated                                               Rollback is generated

-Fragmentation reduced

66. How to find out tablespace free space?

Desc dba_free_space

Desc dba_segments

67. How to rename tablespace?

alter tablespace <TN> rename to <New name>

68. How many types segments we have?

                -Table segments (data segments)

                -Index segments

                -Rollback segments

                -Undo segments

                -Temporary segments

69. How to assign one table space in to default database?

Alter database default tablespace <TN>;

70. How many tablespaces are used in your environment?

Around  40 tablespace

                -Each table space minimum data files 2,5,10,20,40

71. How many Index tables are maintained?

Two index tables , each table 10gb

72. How to find out used space within the database size ?

select (bytes)/1024/1024 'meg' from dba_segments ; (or dba_extents)

73. Where we will see deleted tables?

Show recyclebin or dba_recyclebin

74. Explain about tablespace de-fragmentation / re-organization?

We have two types of re-organization methods for Tablespace

Method 1:

                -Create new tablespace

                -Move all tables to new tablespace (Alter table <TN> move <table space>;)

                -Rebuild all indexes to new tablespace (alter index <TN> rebuild tablespace <TN>;

Method 2:

                - Select distinct , owner from dba_tables where tablespace _name = 'username'; (This query will be find how many users under particular tablespace)

                -Export every schema

                -Create new tablespace with different name (Select dbms_metadata.get_ddl ('Tablespace':Tname) from dual; --In this query we will find complete DDL for new tablespace same like old TS.

                -Make new tablespace to default tablespace for database;

                -Import all exported schemas

                -Rename new tablespace to old tablespace name.

75. Describe about the Segments, Extents and Data blocks?

 -Segments: A segments are major organizational structure of the database objects.  Collection of extents are called a segments.

-Extents: Extents are database objects, An extent is a specific number of continues allocation of the data blocks.

-Data block: Oracle databases data is stored in the data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

76. What is dynamic space allocation?

One extent is full, then oracle automatically allocates another extent for this segment..which is known as dynamic space allocation.

77. What is the minimum /maximum database block size?

Minimum - 2kb, Maximum --32 kb. By default --8kb

78. How many max/min control files?

Min- 1 and Max-8

79.  What is the database file size?

It depends on operating system, limited by maximum OS file.

Size is typically 2^22 or 4MB block

80. What is Redo log file size?

Min- 50kb

Max - Operating system limit, typically 2gb

81. What is big file tablespace?

Big file tablespace consist of only one datafile or one tempfile upto 32TB. The big file table space allows you to create one large data file that is 4 billion block size. The big file table space is designed to be used in RAID systems that support large datafiles or ASM.

82. How to resize the SGA?

alter system set SGA_target = <value>

83. How many redo logs?

Min- 2

Max- 255

84. Explain about the temporary table space?

A temporary table space is used to manage space for sort operations. Sort operations generate segments, some time large segments.

                - Used for sort operations

                -Can't contain any permanent objects

                -Locally managed extents are recommended

                -Temp files are also in 'Nologging' mode. they also can't be made as read only or be renamed.

Limitations: A default temporary tablespace can't be dropped unless a replacement is created.

                - We can't take a default TTS offline

85. Can we create temporary table space with 'segment space management auto'?

No, We can't create temporary tablespace with 'segment space management auto'

86. What is the minimum size of TTS?

1041k

--A normal table space can consists of up to 1024 datafiles. The bigfile table space allows us to create one large datafile that is 4 billion blocks size. The bigfile TS is designed to be used with RAID systems that support large datafiles or with ASM.

87. What is the extent - management value for the temporary tablespace created in 10G?

Local.

REDOLOG FILE Management

-Every change that was made to the database initially will be registered in the online redolog file.

-Online redo log file contains committed as well as un committed data

-Oracle writes the data into online redolog file in cyclic manner

- Redolog files used for recovery purpose.

                --Instance recovery (After a system crash )

                --Media Recovery (In Complete recovery , like Lost of C/R/D files)

                --Stand by database processing

-Redologfile Modes

                -Current

                -Active

                -InActive

                -Unused

-Current: If a LGWR is pointing to  a particular online redolog file the status of the online redolog file will be 'Current'

-Active: Online redo log file which is required for recovery will exists in 'active' state

-Unused/InActive: Online reolog file which is not requires for recovery may exits either in inactive / unused state.

---Redolog file is full, LGWR will writes to next log group by performing a log switch

- Adding online redolog groups:

                Add group:

                >alter database add logfile group2 '/u001/madhu/oradara/redolog3.log' size 4m;

                Add member:

                >Alter database add logfile member '/u001/madhu/oradata/redolog03.log' to group 2;

                Drop group:

                >Alter database drop logfile group2;

                Drop member:

                >alter database drop logfile member '/u001/madhu/oradata/redolog03.log to group 2;

88. What is the default redolog file size in 10G?

4MB

89. Tell me few views of the redolog files?

V$log, V$Logfile and V$Loghist etc

Archive Log Mode

-Archive log files re content copies of online redolog files, but they are not image copies

-once we configure the DB in archive log mode a new background process 'Archive' will be started

-Archiver background process takes the responsibility of coping the content of online redolog files into archive destination specified with the parameter 'log-arch_dest'

90 What are the Configuration steps of archive log mode in DB?

-Inform to end users and shutdown the database (Proper shutdown)

                >Shut immediate

-Open parameter file and set the parameters

                -log_archive_dest_format = redo_%S_%t_%r.log

                -log_archive_dest = '/u001/madhu/admin/arch'

-Startup the database in mount mode

                >startup mount

-Alter database archivelog

-alter database open

-Finally check archive log list

                >archive log list;

Note: At the end of every hot backup DBA switches the logfile manually because of switch we are calling it as a content copy rather than a image copy

                >alter system switch logfile;

    - It is responsibility of DBA to monitor the availability of space in archive log destination. if the destination is full database goes to hung state and no new connections will be allowed

91 My archive log destination is full, what can you do?

When archive log destination is full, the database goes to hung state

                -Move the archivelog files

                                -archive destination to backup location by using MV command

                                -Move archive log files current mount point to another mount point

                                                Ex: mv archivelogfiles '/u002/arch'

                -after database running normally

                                - change archivelog destination temporarily

Note1: In No archive log mode , not able to recovery to any point in time recovery

Note2: In archive log mode we can recover point in time recoveries

92. What are the difference between Reset logs and No Reset logs?

Reset logs:

Once reset log is used then the redo log files cannot be used and any completed transactions in those redo logs are lost.

--before using the resetlogs option take an offline backup of the DB.

No Reset logs:

The No reset log option does not clear the redo log files, running startup and the online redo logs to be used for recovery.

-only used in scenario where manual recovery is started, cancel is used and then recover database is started.

93. What does alter database open resetlogs?

 --The current online redo logs are archived

--The log sequence number is reset to 1

--New database incarnation is created and online redo logs are given a new time stamp and SCN

                Few views of the archive logs

                v$archive, v$archive_dest_status, v$archived_log, v$archive_process and v$archive_gap etc

USER MANAGEMENT

SCHEMA: Schema is  a named collection of objects of a user

                - A user is created and corresponding schema is created

                - A user can be associated only with one schema

HEADER: A header contains the general information about the data

                - block address

                -Type of segments like tables, indexes

Sequence: A sequence generates a serial list of unique numbers for numerical columns of a database table

Synonym : Synonym is nothing but to hide the original name of the owner, table and view. It provide the security by hiding the identity of the component and it will not hold any data.

A synonym is an alias for a table , view and sequences etc

                - synonym have 2 different types

                                - Private synonym

                                - Public synonym

                -Private synonym : Only it's owner can access a private synonym

                -Public synonym: Any data base user can access a public synonym

CLUSTER: Clusters are group of one or more tables physically stores together to share common columns and are often used together

94. What are difference between Profile, Privilege and Role?

Profile                                                   Privilege                                                               Role

-Profile is a named set of              -Privilege is a permission to perform       - Set of privileges is called roles

password and resource limit         transactions against the database

like: passwrd, account lock          -privileges are two types

idle-time,failed-login-attemts                    - system privilege

                                                                                -Object privilege

                                                                -System privilege: It performs DDL

                                                                (C,A,D) operations

                                                                - Object privilege: It performs DML

                                                                (I,U,D) operations

95. How to check the all users in one database?

select * from all_users;

Desc dba_users;

96. How to given quotas to users?

create user <UN> identified by <UP> default tablespace <TN> quota 5m on <TSN>;

alter user <UN> quota 5m on <TSN>

97. How to assign existing user to another TS?

alter user <UN> default tablespace <TN>;

98. How to find the particular user connected to the TS?

Desc DBA_users;

99. How much space allocated to specified user, how to find out?

Desc dba_ts_quotas;

100. How to given sysdba permissions?

> show user

>grant create table to user;

grant create view to user;

Desc dba_sys_privs

101. How to grant privilege with admin options?

> show user

>grant create table to user with admin option;

102. How to revoke privileges?

> revoke create table from user;

>revoke all on user1 order_details from user2;

103. How to change the status of user account?

>alter user <UN> account lock;

104. How to unlock user account?

>alter user<UN>account unlock; error 28000

105. How to release expired password?

> alter user<UN>identified by <UP> expire; error 28001

106. How to change user password?

>alter user <UN> identified by <NPW>;

107. Here old password overwrite, But user wants to old password , how to set?

- before changing user password find the password binary value (encrypted)

                >desc dba_users

-Copy the binary password in notepad

-Change new password

- Next revoke the new password to old password

> alter user <UN> identified by values <binary value>;

107. How to find out how many user are connected to database?

>select username,sid,serial# from v$session;

108. How to find out how much tablespace used?

> desc dba_segments or desc dba_extents

109. Tel me few views of user management?

v$pwfiles_users

V$resource

v$resource_limt

v$rollstat

V$rollname etc

109 Describe drop user command?

 > drop user <UN>;

-dropping a user causes the user and user schema to be immediately deleted from database

-any object consist by user, necessary to use the cascade option in order to drop the user

---We can't drop a user directly

                - Must first terminate the users session through V$session

                >alter system kill sessionid, serial# then rop the user

>Drop user ,UN> cascade;

                - it will be dropped with objects (if any objects to user)

Note: Oracle database stories user password in the datadictionary in an encrypted format to prevent unauthorized alteration

110. How to find out how many users are created in one database?

desc dba_users;

111. How to find out how many users are connected to database?

desc v$session

112. How to find out all DB information?

select * from database_propertices;

113. How to kill session from OS/SQL level?

SQL level : v$ session;

OS Level: #ps -ef/grep sqlplus

                #kill -g 42742

> alter system kill sessionid,serial#;

                - When a session is kill , the PMON background process will rollback the users. Current transaction and release all table and row locks held and free al resources reserved for the user.

114. From which view user can see his privileges ?

session_privs;

115. Which privilege is necessary for a normal user to change his password?

create session

116. DBA created one profile and assigned to users for applicable for that which parameter we need to set in init.ora?

resource_limit = true

UNDO Management

-Undo management and undo tablespace is maintained undo data. Undo TS purpose is transaction rollback. Transaction recovery and instance recovery

-Undo TS is nothing but like rollback. It stories before image of any DML(I,U,D) stateents. It will support only DML operations

Note: When we create one Undo TS oracle will be create 10 undo segments by default with naming convention.

>create undo TS <TN> datafile '/uoo1/madhu/oradata/datfile01.dbf' reuse autoextent on;

-We have two types of undo management.

                - Auto and Manual

Auto: It's available from 10g

Manual: For transactional roll back segments (RBS)

--By default undo retention is 900 sec(15mints)

1. In your environment undo retention period how much?

4800 sec(80 min)

2. What is your undo TS size?

20GB

3. What happen without undo segments /Rollback segments?

We can't perform any DML operations

-Required use of UNDO:  Undo used to store undo segments, can't contain any other objects, extents are locally managed, can only used datafiles and extent management clause

Note:- More than one undo tablespace can exists, but only one can be active at a time

4. What is unexpired blocks?

The blocks which are in retention period, these segments are needed for read consistency even after a transaction is commit

5. What is Expired blocks?

The blocks which are in out of retention period , which ready for new transactions are called expired blocks

--These segments are store undo data that has been committed and undo has been reached

6. What is active blocks?

The blocks which contain ongoing transactions means not committed

7. What is Free blocks?

At the time of creation all are empty/free blocks

8. How to resolve snap shot too old error (ORA-1555)?

-Properly sizes the rollback segment which will avoid overwrite rollback information

-Reduce the number of commits

-Add additional rollback segments

-Increase the size of undo-retention

9. What is the difference between offline and offline immediate?

Offline : When we will keep the TS/datafile in offline mode, at the same time checkpoint is occurred and SCN numbers are generated. Oracle update SCN numbers in datafile headers and control file

-When we release the offline to online, the SCN numbers doesn't  match in control file. This is a risk method

Offline immediate: When we will keep the tablespace/datafile in offline immediate mode, SCN numbers will not generate, Next release the offline immediate to online no problem to us

10. What is difference between Rollback segments and undo segments?

                                Rollback segments                                                                          Undo Segments

-One rollback segments are full, DBA created by       -One undo segment is full, oracle starts transferring 

new rollback segments by owning                                  extents from one segment to another segment,                                                                                                              which belongs to done transactions

-Monitoring is required by DBA                                      -No more monitoring is required by DBA

                                                                                                     -Oracle responsibility for taking core of extents sizes

--Sometimes we will want to start up the DB, but restrict connection to users with the restricted session privilege, so that we can perform certain maintenance activity   such as exporting or importing part of the DB

                > Startup restrict pfile = '    ';

--We may also begin media recovery when a DB starts where our system has suffered a disk crash

                > startup recover pfile = '             ';

Note: Undo TS have rollback segments, when we drop the undo table space, it will not drop roll back segments, at that time we have to set one parameter in init.ora file

                _offline_rollback_segment = (sysmu1,_syssmu2,_syssmu3----)

11. What are the difference between PFILE and SPFILE?

                                PFILE                                                                                     SPFILE

-Pfile is a text file(ASCII)                                                                               - SPfile is a binary file

-We can't change parameters dynamically                            -We can change parameters dynamically

-When we want to set parameters in init.ora file                               -Here no need to shutdown the DB, we can

must we shutdown the DB and set parameters and         change dynamically by using alter system set

startup DB                                                                                           command

-Pfile is maintained by slandered OS editor                          -Spfile is maintained by oracle server

-We can edit directly                                                                      - We can't edit directly

-Pfile create from SPfile                                                                                -Spfile create from Pfile

Advantages of SPfile:

                - We can take spfile backup by using RMAN

                -Reduce human errors

                -SPfile is maintained by server

                - Eliminate configuration problems

12. How to startup pfile in nomount/mount/open, when DB is running in SPFILE?

                > startup nomount pfile = $oracle_home/dbs/initmadhu.ora

                >startup mount pfile = $oracle_home/dbs/initmadhu.ora

                > start pfile = $oracle_home/dbs/initmadhu.ora

Control Files

What is multiplexing the control files?

                >Shutdown the database

                >Copy the existing control file to new name and new location

                $cp /u001/madhu/oradata/control01.ctl               /u002/madhu/oradata/control02.ctl

                >Add new control file location in parameter file

                > startup database

How to Re-Creating control file?

                We have Three methods

 - Method1:

                -We have backup that time we can recover from yesterdays backup

                  -Use proper shutdown (shut immediate / shut abort)

                                > shut abort

                 -Restore yesterdays backup to current backup location

                                cold]$cp  *.*    /u002/madhu/oradata

                 -startup database in mount mode

                                >startup mount

                 -Alter database recover automatic using backup control file until cancel;

                alter database open reset logs;

-Method 2:

                -We have maintained one trace backup of control file

                 -Open trace backup and delete junk file and change 'reuse' to 'set'

                   >create controlfile set database 'madhu' noresetlogs archivelog;

                 -Shutdown database

                                > shut down immediate

                 -Startup no mount

                                > start up nomount

                 -run the trace backup of control file

                                > /u001/madhu/admin/scripts/control.sql

                 - Alter database open resetlogs;

-Method3:

                -If we have no backup create controlfile manually

                -Startup database nomount

                -Create controlfile

                -Startup Mount

                -Open database

                                                                                DATABASE Name Change

Steps to change DB name

                -Take the full database backup

                -Startup database in mount mode

                -$NID target = system/manager db_name = newdbname

                -It will be changed control file and all and give suggesions

                -It will shutdown automatically

                -startup database in mount mode

                -alter database open resetlogs

                -after change db_name in envfile

                                                                                NETWORKING

-We can configure network in two methods

                1. Manual

                2. Netca (Network Configuration assistance)

Location of the network file

                $Oracle_home/network/admin

1. What are the difference between Server and Client?

                                Server                                                                                   Client

-Listner                                                                                                 -tns names.ora

-lsnrctl status                                                                                     -tnsping

-lsnrctl stop

-lsnrctl start

--We are connected through tnsnames.ora to lsnrctl

2. How to find out server IP address?

-#ipconfig -a

/etc/hosts

3. What is default port number?

1521

4. How to check the network connection status?

ps -ef/grep tns

lsnrctl status

5. How many network parameters are there?

                Listner.ora, tnsnames.ora and sqlnet.ora

6. How you are connecting your DB?

Through Putty (putty is a CUI, character user interface)

7. What is 12162 TNS error?

Net service name is incorrectly specified

Solution: Export Oracle_SID = madhu

                                                                                DATA Base Links

--We have two types of database links

                --Private DB links

                --Public DB links

--Private DB links: Private database links created by an user for his own

--Public DB links: Created by DBA

Init.ora:

                global _names = true/false

True: Public DB links

False: Private DB links

1. How to create a DB link?

create database link <link name> connect to <U> identified by <UP> using  'tnsaliasname';

 -grant create database link to <UN>;

2. How to drop the DB link?

>Drop database link <link name>;

3. How to close the database link?

alter session close database link <link name>;

4. What are data base link monitoring tools?

                > select * from user_db-links;

                >select * from dba_db_links

                >select * from all_db_links;

5. If user wants to create db links what privileges he is need?

-Create database link privileges

-Create public database link

6. What type of operations we can do by using db links?

We can perform only DML (I,U,D) operations

7. Where will be stored db link related links?

$ORACLE_home/rdbms/dmin/caths.sql

8. How to findout which user using which db link?

>select db_link,username,password,host,created from user_db_links;

>select name,value from v$parameter where name = dblink

BACKUP AND RECOVERYS

1. Explain difference between traditional backups and data pumps?

                                EXP                                          ExpDP

-Traditional logical backups are a process              -Data pumps are a job

 -Export is a byte mode                                  -Data pump is  block mode

 -Exp technology is a client based        -Data pumps are purely server based . All dump files,log files and other files are created on the server by default

 -No such flexibility                              -It will provide high speed, parallel , bulked and meta data

                                                               

-It is slow                                               -Data pumps are fastest and improve performance                                                                                      compare to traditional logical backups

- Not able to estimate size                     - One more main advantages is we can stop the job in                                                                                middle for performance issues and attach the existing                                                                              job and reuse the job

                                                           - We can estimate the sizes. like, dump file size and                                                                                                                export file size  

                                                             -Ability to restart the job

                                                             -Network mode operations we can perform

                                                          - The data pumps use two components

                                                              - DBMS_Datapump                                                                                                                                        -DBMS_metadata
                                                                 RMAN

1. How to improve RMAN performance ?
A. By using parallelism

     Files for set param ( Actually it will take backup based on this param, when it was specified. For example if we set as 'Files for set param as 10' then backup will take at a time 10 files. so that performance will be increased.)

2. How to improve incremental back up performance in RMAN?
A. We have to increase the performance to  Enable 'Block change tracking.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

> alter database enable block change tracking using file 'u001/oradata/product/11.2.0/madhu';
This command creates a binary file called /rman-bkup/Chanel 
>alter database disable block change tracking;
        v$block_change_tracking
> select filename,status from v$block_change_tracking