Control file Backup

It is very important that you back up your control file, Bacause the control file records the physical structure of the database,you should make a backup of your control file after making changes to the physical structure of the database. You can create backup of control file by using this statement: Alter database backup controlfile '/opt/nit/conback.ctl'You can also backup of a control file to trace file.Alter database backup controlfile to trace You backup a control file after any change the physical structure of your database. Such...

Read More

Multiplexing Control File

Control file is a small binary file which includes information like database name, name and location of data files and redo log files, timestamp of database creation,log sequence number and RMAN backup information if we use RMAN. Control file include these all information so thats "Why we need multiplexing of control file". When control file is damaged ther is no way you can bring it back unless you have the backup.So, oracle database should have atleast two control files. You should have backup of control file and backup of control files must...

Read More

Create Control File

when you issue the create database statement at the time of the database creation at that time control file of an oracle database is created. The name of the control file is defined by the CONTROL_FILES parameter in the pfile initialization parameter file. Example of a control file initialization parameter is as follows: Control_files '/opt/luck/con01.ctl',              '/opt/luck/con02.ctl' If files names currently exist at the time of database creation, you must specify the Control Reuse...

Read More

What is Control file

The control file is a small binary file necessary for the database to start and operate successfully. Control file records the physical structure of the database.Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use.The information in the control file can be modified only by the Oracle server.The information in the control file includes:Database name Names and locations of associated datafiles and online redo log files The timestamp...

Read More

Data Dictionary Question

1) Which of the following statements are true about the data dictionary? a The data dictionary describes the database and its objects. b The data dictionary includes two types of objects: base tables, data dictionary views. c The data dictionary is a set of read-only tables. d The data dictionary records and verifies information about its associated database. 2) Base tables are created using the catalog.sql script. a True b False 3) Which of the following statements are true about how the data dictionary is used?  a The Oracle server modifies...

Read More

Data Dictionary Contents

Data Dictionary ContentsThe definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers) How much space has been allocated for, and is currently used by, the schema objects Default values for columns Integrity constraint information The names of Oracle users Privileges and roles each user has been granted Auditing information, such as who has accessed or updated various schema objects  Data dictionary static views that answer questions such as: Was the...

Read More

Undo_Retention or ORA-01555:snapshot too old

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter. If any overwritten undo information is required for consistent read in a current long-running query, the query could fail with ORA-01555 snapshot too old error message. The ORA-01555 is caused by Oracle read consistency mechanism.Oracles does this by reading the...

Read More

Undo Space Allocation

Whenever a new transaction needs undo space:First , oracle tries to find a undo segment which has no active transaction and allocate the extent within the undo segment. If no such undo segment found then oracle tries to make online of and off-line undo segment and use it. If no such off-line undo segment found then create a new undo segment and use it. If there is no sufficient space to create a new undo segment then it try to reuse an expired extent from the existing undo segment. Whenever a running transaction needs more undo space:If current...

Read More

Recover Undo Tablespace

Undo tablespace is used to store the post modification data of every transaction for rollback or read consistency or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. So, recovery of undo tablespace is more important in case of undo tablespace lost. There are few steps to recover undo tablespace: [oracle10@localhost ~]$export_SID=luck [oracle10@localhost...

Read More

Undo and Rollback Segment

Rollback  Rollback segments are used to store the previous values temporarily when you updates some data in the database. Rollback segments have traditionally stored undo information used by several functions of Oracle. During database recovery, after all changes recorded in the redo log have been applied, Oracle uses rollback segment information to undo any uncommitted transactions. Because rollback segments are stored in the database buffers, this important recovery information is automatically protected by the redo log. Create rollback...

Read More