Explain Oracle memory structure.
SGA(system global area) & PGA(program global area) are two memory structures in oracle. SGA::is used to store shared information.It having Buffercache,Redolog buffer,Shared pool,java pool,Larg pool an...
PGA Program global area Contains
Session variables ,Stack space,Cursor status ,Sort Areas
What process writes from data files to buffer cache?
server process
DBWR only writes to the data files. So the answer is Server Process.
Why do you take tablespaces in backup mode.
In case of hot backup,table space is put into backup mode.At the time ts related datafile header is frozen
because for taking backup with out any data loss.And after that any data is coming,that one going to redolog file for recovery.
We need to backup so that it can be used in case if any failure. We restore it from these backup. Now for the backup to be used for restore it has to be valid backup. Some thing like SCN, Timestamp ha...
Oracle catalog and archive log
Explain what are Oracle catalog and Oracle archive log?
Oracle catalog contain the metadata of objects.
RMAN catalog maintain the backup information.
Redo data persistently stored in archive logs,for recovery..
Catalog term is used in 2 ways.Catalog - All tables/views stores information about database and all objects in it.RMAN Catalog - Schema where RMAN executable stores/reads/writes the information about ...
Startup mount and startup nomount
What is the difference between startup mount and startup nomount? When its used?
Start up No mount: instance started with help of parameter file & here control files are located.
Start up mount:database is mounted i.e control files are allocated & data files, redo logs are located.
Open: here allocated all data & redo logs.Now the database is ready to use.
Nomount : Instance started
Mount : control file open for this instance
open : All the files open as described by the control file.
Oracle maximum data files limit
How many max datafiles can there be in an Oracle database.
Database having maximum up to 65536 data files. Each table space having 1022 data files.
You can create maximum 65536 datafiles
Common problems while taking backups
What are the common problems while taking backups? What are the necessary steps to take backup on to tapes and disks in real time?
For a cold backup :shutdown oracle first and then proceed the cold(offline) back up. once you shutdown the oracle database insert the external tape/disk.pls ensure the tape/disk is in good condition. ...
Some Common issues with Backup. If you are taking backup from no-archive log: then your database doesn't bring down due to some processes are running. If you are taking hot backup then may be newly ...
Dear friend, it is not RAP but it is WRAP. Using this utility of Oracle, we can hide our PL/SQL code from user.
Why use materialized view instead of a table
If materialized view contains the actual data and has to be re-created every time the original table data is changed and we intent to refresh the data in the view.Then what is the actual use of materialized view, why can't we create another table by itself by running the same query ?
Materialized view is useful in replication and data warehousing environment where every time data extracting and data loading from another sites,databases. Materialized view also useful for maintaining complex queries because it uses query rewrite feature.
What are the advantages of views
Exactly View is logical identity.Oracle only stores definition of View in metadata. Using view, we can hide some columns of tables from users, we can hide original table name from user. Using view we can perform complex query in single statement.
Assume that the dba is modifying a tables and the same time developer want to use that database a then dba can provide him the view for that tables.
Code
CREATE VIEW VIEW_NAME(SELECT * FROM A);
What are the components of logical database structure of Oracle database
Logical Structure of Oracle is following.
Tablespace =>Schema => Segments => Extent => Oracle Block. (Remember Oracle block is smallest logical unit).
the logical components of database are tablespaces, segments, extents, datablocks. datablock is the smallest unit of oracle I/0.
A user issues the below query, 1. select * from table1 lock table at the same time DBA is trying to modify this table table1 using alter command, will he able to do that or not?
DDL lock are exclusive locks. It shared the data. Means anyone can select data from same table.
there dba can break the lock given to table.
but in real time, no dba has the right to break the lock given by the user
What are key tasks for an Oracle DBA on daily basis?
Daily Tasks of DBA are following. 1. Disk Space checking for all databases, tablespaces, archive log destinations, dump destinations, and backup destinations. 2. All backup successfully finished or n...
1.Connectivity: Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts. 2.Backups: Check database and log backups, archiving an...
Table is used to store the data in the form of rows and columns
a table is used to store the information in form of rows &columns in the database
datafile is the physical storage location of the database.tablespace is logical.for creation of tablespace one or more datafiles needed.we can define the datafile location with the following parameter
db_create_file_dest
Datafiles are physical data location of oracle architecture. Table space contains no.of datafiles.
How do you see specific table space is offline or online ?
Code
SELECT TABLESPACE_NAME, ONLINE_STATUS FROM DBA_DATA_FILES;
Code
SELECT tablespace_name,status FROM dba_tablespaces;
What kind of information stored in DBA_triggers data dictionary view
ABOUT TRIGGERNAME,
OWNER,
TRIGGERTYPE,
TRIGGEREVENT,
TABLEOWNER EXC
information is stored in DBA_TRIGGERS.
info about triggers...like status of the triggers (enabled/disabled),trigger name,object name etc
Tablespace is a logical content of data It is also called collection of datafiles They are of two types Small tablespace Big tablespace For small tablespace we can create many datafile For big tab...
Table space is the space given to any of data file to get exist into the database provided no two same data files that should exist in any table space.
What should you do when you find a stacktrace in the server errorlog?
When the server crashes, it generates core dump. If value of orclsdumpflag is set to 0, then the server will also attempt to leave a stack trace. The location for the stack trace is: ORACLE_INSTANC...
Rollback segment is a database object,containing before image of data written to the database.It is used to..
:Undo changes when the transaction is rolled back.
:Recover the database in case of failures.
:Uncommitted changes made to the database,this will protected by it.
ROLLBACK INFORMATION NOTES: Segment Name - Name of the rollback segment. Owner - Owner of the rollback segment. Tablespace - Name of the tablespace containing the rollback...