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 includes as:

• Adding, dropping or renaming datafiles
• Adding or dropping redo log files or group
• Adding or dropping a tablespace 

  You can obtain control file information using these views as follows:

• V$CONTROLFILE
• V$PARAMETER
• V$CONTROLFILE_RECORD_SECTION
• SHOW PARAMETERS CONTROL_FILES

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 be stored in a different physical disk.If a control file is damaged due to a disl failure, the damaged control file can be restored using the copy of the control file from the other disk and instance can be restarted. The steps for multiplexing control file are as follows:

1) Conn as SYSDBA
2) shutdown the database immediate
3) Open the directory look for control file 'control.ctl'
4) Create duplicate copy of 'control.ctl' ,then rename of the duplicate control file 'control01.ctl'
5) Open the initialization paramter file 'initSID.ora', copy the location of duplicate control file and paste it in the control file configuration:

control file '/opt/luck/control.ctl',
             '/opt/luck/control01.ctl'
 

6) Startup database

SQL>startup pfile='/opt/luck/initluck.ora' or you can start with spfile
 

7) check your control file

SQL>select name from v$controlfile;
Name
-------------
/opt/luck/control.ctl
/opt/luck/control01.ctl

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 clause in the CREATE DATABASE statement. The size of the control file is influenced by the following keywords in the create database or create control commands:

• MAXLOGFILES
• MAXLOGMEMBERS
• MAXLOGHISTORY
• MAXDATAFILES
• MAXINSTANCES

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 of database creation
• Current log sequence number
• Checkpoint information
• Tablespace information
• Datafile offline ranges
• Redo log history
• Archived log information
• RMAN information means (database backup taken through RMAN)
• Datafile copy information.

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

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 it when a DML statement is executed.
b It is used to find information about users, schema objects, and storage structures.
c Used by users and DBAs as a read-only reference.
d The data dictionary is a necessary ingredient for the database to function.
 

4) Data dictionary views are static views.
a True
b False
 

5) The information for a Dynamic View is gathered from the control file and data files.
a True
b False
 

6)  Which of the following questions might a Dynamic View answer?
a Is the object online and available?
b What locks are being held?
c Who owns the object?
d What privileges do users have?
e Is the session active?

Read More

Data Dictionary Contents

Data Dictionary Contents

• The 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 object ever created?
• What is the object a part of?
• Who owns the object?
• What privileges do users have?
• What restrictions are on the object?

Data dictionary dynamic Performance Views that answer questions such as:

• Is the object online and available?
• Is the object open?
• What locks are being held?
• Is the session active?

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 before image of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555: snapshot too old error occurs, rollback records needed by a reader for consistent read, are overwritten by other writers. The properties of the UNDO_RETENTION parameter are mentioned below:

• Parameter type – Integer
• Default value – 900
• Range of values – 0 to 232 – 1
• Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

Avoiding the ORA-01555: snapshot too old error

• Do not run discrete transactions while sensitive queries or transactions are running.
• Use a large optimal value on all rollback segments, to delay extent reuse.
• Use a large database block size to maximize the number of slots in the rollback segment transaction tables.
• If necessary, add extra rollback segments (undo logs) to make more transaction slots available.
• Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN.

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 extent has more free blocks? If it has then allocate the next free block within the extent.
• If current extent does not have free blocks then check the next extent within the segment. If the next extent is expired then wrap the next extent and allocate the first block of the next extent.
• If the next extent is not expired then get space from UNDO tablespace.If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
• If there is no free space available then take space from offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
• If there is no offline undo segment then deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
• If it fails then extend the datafile of the undo tablespace.If the file can be extended then add an extent to the current undo segment then return the block.
• If the extend datafile fails then try to reuse extents from its own undo segments. If it is not busy then warp into the next extent.
• If reuse extents from its own undo segment fails i.e they contain uncommitted transaction then take unexpired extents from it's own offline undo segments.
• If it fails then take unexpired extents from it's own online undo segments.

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 ~]$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 30 09:11:10 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area   314572800 bytes
Fixed Size                            1219160 bytes
Variable Size                       96470440 bytes
Database Buffers                213909504 bytes
Redo Buffers                         2973696 bytes
Database mounted.
ORA-01157 :cannot identify/lock datafile 2 - see DBWR trace file
ORA-01110 :datafile 2: '/opt/luck/undotbs.dbf'

we cannot open the database by taking the datafile offline but we need to use 'OFFLINE FOR DROP'.Datafile once marked offline for drop can never be brought online.

SQL>alter database datafile 2 offline for drop;
Database altered.

SQL>alter database open;
Database altered.

SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Now we need to create another undo tablespace for the transactions to use and once the new undo tablespace is created, shutdown the database and edit the init.ora file and change the  parameter undo_tablespace=new undo_tablespace

SQL>create undo tablespace undotbs1 datafile '/opt/luck/undotbs1.dbf' size 20m autoextend on;
Tablespace created.

SQL>shutdown immediate;

In simple steps:
a) startup
b) error ora:01157
    error ora:01110
c) alter system set undo_management = manual scope=spfile
d) shutdown immediate
e) startup 
f ) drop undo tablespace
g) Create UNDO Tablespace UNDOTBS datafile ...
h) alter system set undo_management = auto scope=spfile
i) Shutdown
j) Startup Database

Related Post:- Undo and Rollback segment

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 statement command: 

SQL>create rollback segment rbs tablespace rbsts;

                   

SQL>alter rollback segment rbs online;

You can monitor rollback segments from DBA_ROLLBACK_SEGS

 SQL>select segment_name, status from dba_rollback_segs;

Undo

Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transactions using ROLLBACK statement
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Flashback Query

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

SQL>show parameter undo;

NAME TYPE VALUE

---------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTEST 

SQL>create undo tablespace undotest datafile'/opt/test/undotest.dbf' size 100m 

autoextend on next 10m maxsize unlimited retention noguarantee;

System parameters

SQL>alter system set undo_retention=900 scope=both;

SQL>alter system set undo_tablespace= undotest  scope=both;

SQl>alter system set undo_management= auto scope=spfile;

SQL>shutdown immediate

SQL>startup

UNDO_MANAGEMENT is a static parameter, so database needs to be restarted.

Related Post:- Recover Undo Tablespace

Read More