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 ...
The dbms_advisor package has a procedure called dbms_advisor.quick_tune that allows the DBA to quickly tune a single SQL statement with a single procedure call
Hi , I just reviewed your answer. Anyway thanks for your effort to give us response.But I have a concern .Suppose a user just reported us that his query running slow and we checked the execution plan,...
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.
What is SCN number in Oracle? Plz any one give me the explanation for SCN
SCN is system change number.it will generate every time when log switch occurred & user commits the transaction.Main thing is scn will generated with every transaction &(or) every one seconds.
SCN-SYSTEM Change number, it will generate automatically while u perform commit.
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
What is difference between spfile and init.Ora file???
Using spfile,change the parameters dynamically.But in pfile,after change the parameters u must restart the database..
spfile is a binary file n it will automatically created with dbca utility(at db creation time).Pfile is a text file n it will edit by any text editor.
Spfile server parameter file it is an substitute for pfile with the benefit of "make the changes in this spfile dynamically means if we made the changes in the spfile there is no need to restart of da...
What is the use of storage option in materialized view ?
Materialized views are useful for fast REPLICATION purpose..
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 ...
How will you move a table from one schema to other ?
We can use dbms_redefinition package to achieve this
By creating a dump file of one schema and then exporting it to other schema
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.
Which process writes data from data files to database buffer cache?
Data always copied in buffer cache from data file by only Server Process not DBWR.
Database writer writes data from Buffer to Disk But Server process loads data from Disk to Buffer
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;
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...