What is a controlfile in oracle, how to recreate the controlfile

Controlfile :

Control file is small binary file, it contains physical structure of the oracle database. Each database must contain one or more control files. It contains 

- The database name

- Datafiles and redolog files information of associated database.

- Timestamp of the database creation.

- The current log sequence number

- Check point information.

We can specify the controlfile names using the “control_files” parameter.

Control_files=(/u01/oracle/prod/control01.ctl, /u02/oracle/prod/control02.ctl, /u03/oracle/prod/control03.ctl)

Size of the control file can vary from release to realse. Configuration parameters maxlogfiles, maxlogmembers, maxdatafiles and maxinstances affect the control file size.

How to multiplexing the controlfiles:

1. Shut down the database.

2. Edit the control_file parameter and set the new locations.

For example having only one controlfile at /u01/app/oracle/oradata/control1.ctl, this file we need to copy to new location and mention that new location at parameter.

$cp /u01/app/oracle/oradata/control1.ctl /u02/app/oracle/oradata/control2.ctl

$ cp /u01/app/oracle/oradata/control1.ctl /u03/app/oracle/oradata/control3.ctl

3. Restart the database.

Creation of new control file:  

We can create the new control file in 2 situations.

1. All the available control files corrupted and have no backup of the control file.

2. Rename the database.

Renaming of the Database using controlfile:

We can create the controlfile using create controlfile statement. We can rename the database base using creation control file. We do mention the new name at create controlfile statement.

CREATE CONTROLFILE SET DATABASE prod

LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',

'/u01/oracle/prod/redo01_02.log'),

GROUP 2 ('/u01/oracle/prod/redo02_01.log',

'/u01/oracle/prod/redo02_02.log'),

GROUP 3 ('/u01/oracle/prod/redo03_01.log',

'/u01/oracle/prod/redo03_02.log')

RESETLOGS

DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,

'/u01/oracle/prod/rbs01.dbs' SIZE 5M,

'/u01/oracle/prod/users01.dbs' SIZE 5M,

'/u01/oracle/prod/temp01.dbs' SIZE 5M

MAXLOGFILES 50

MAXLOGMEMBERS 3

MAXLOGHISTORY 400

MAXDATAFILES 200

Read More

AWS for Oracle DBA, RDS and security groups

Overview of Amazon RDS

1. A web service that makes it easier to set up, operate, and scale a relational database in the cloud
2. Amazon RDS manages backups, software patching, automatic failure detection and recovery
3. Amazon RDS doesn't provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges.
4. In addition to the security in your database package, you can help control who can access your RDS databases by using AWS identity and access management (IAM) to define users and permissions.
5. You can also help protect your databases by putting them in a virtual private cloud

DB Instances:

1. Basic building block of Amazon RDS
2. An isolated database environment in the cloud
3. create and modify a DB instance by using the AWS command line inter face, the Amazon RDS, or the AWS management console
4. Amazon RDS currently supports the MySQL, MariaDB, Postgresql, Oracle and Microsoft SQL Server DB engines
5. The computation and memory capacity of DB instance is determined by its DB instance class.

High Availability:

1. Regions and availability zones.
2. Houses in highly available data center facilitates in different areas of the world.
3. Each region contains multiple distinct locations called Availability Zones, or AZs.
4. Each Availability Zone is engineered to be isolated from failures in other availability Zones.
5. By launching instances in separate Availability Zones, you can protect your applications from the failure of a single option called a Multi-AZ deployment.

Security:

1. A security group controls the access to a DB instance.
2. Allowing access to IP address ranges or Amazon EC2 instances that you specify.
3. Amazon RDS uses DB security groups, VPC security groups, and EC2 security groups.
   - A DB security group controls access to EC2-Classic DB instances that are not in a VPC.
   - A VPC security group controls access to the DB instances and EC2 instances inside a VPC..
   - An EC2 security group controls access to an EC2 Instance.

VPC:

- VPC is a logically isolated virtual network, spanning and entire AWS region, where your instances are launched.
- Isolating your AWS resources from other accounts.
- Routing network traffic to and from your instances
- Protecting your instances from network intrusion.

Default VPC:

- Designed to make it easy for AWS users to set up networking

Security Group Scenario:

- Create a VPC security group(for example, sg-appsrv1) and defined in bounded rules, that use the IP address of the client application as the source. this security group allows your client application to connect to EC2 instances in a VPC that uses this security group.
- Create an EC2 instance for the application and add the EC2 instance to the VPC security group (sg-appserv1) that we created in the previous step. The Ec2 instance in the VPC shares the VPC security group with the DB instance.
- Create a second VPC security group (for example, sg-dbserv1) and create a new rule by specifying the VPC security group that you created in step 1 (sg-appsrvl) as the source.
- Create new DB instance and add the DB instance to the VPC security group (sg-dbserv1) that we created in the previous step. When you create instance, use the some part of number as the one specified for the VPC security group (sg-dbsrv1) rule that we created in step 3.

- From the Amazon RDS console, you can monitor the following items for resources.

- The number of connections to the DB instance.
- The amount of read write operation to a DB instance
- The amount of storage that a DB instance is currently utilizing.
- The amount of memory and CPU being utilized for a DB instance.
- The amount of network traffic to and from DB instance.

- In addition, Amazon RDS integrates with Amazon cloud watch for additional monitoring capabilities.

- Amazon cloud watch metrics
- Amazon cloud watch alarms
- Amazon cloud watch logs.

Backup and Recovery:

- Amazon RDS creates and saves automated backups of your DB instance.
- Amazon RDS creates storage volume snapshot of your DB instance.
- Saves automated backups of DB instance according to the backup retention period that you specify during database creation.
- You can also backup your DB instance manually, by manually creating a DB snapshot.
- You can create a new DB instance by restoring from this DB snapshot.
- You cannot restore from a DB snapshot to an existing DB instance.

Read More

Undo tablespace, rollback segments in oracle

Undo Tablespace:

Oracle Database maintains the undo tablespace for maintaining the information of data of transactions and changes on database before committed.

Undo records are used to:

1. Roll back the transaction when a rollback statement issued.

2. Recover the database.

3. Provide read consistency by maintaining the before image of the data.

4. Used for oracle flashback query and flashback features.

Without undo tablespace is oracle database starts?

Yes, Oracle database will start without undo tablespace. At that time undo records will stores in the system tablespace. But it is not preferable, we can notice the warning in the alert log file as system is running without undo tablespace.

We can manage the undo tablespace with the parameters

Undo_tablespace=undotbs

Undo_management=auto

Undo_retention=900

Undo_tablespace parameter:

Once oracle database starts with assigned undo tablespace.

Undo_Management=Auto:

By default, oracle manages the automatic undo management. Server automatically manages the segments and space among the various active sessions.

Undo_retention=900

Undo retention value is by default 900 seconds (15 mins), oracle database retains the data in undo specified or minimum amount of time. Undo data is used for rollback the data and transactions. Committed data is no longer needed, Older information that is older than the current undo retention is said to be expired. Old undo information that is less than the current undo retention period is unexpired.

We can find the undo data/blocks expired / unexpired using below query.

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;

Read More

What is Oracle Instance? What is Oracle Database?

Oracle instance is containing memory areas and background processes. Instance is used to communicate with the database files.

When an Oracle Instance started, oracle database allocates memory areas and background process.
The memory areas contains the information of

• Program code.
• Information about each connected session even it is in inactive.
• Information needed during program execution, for example current state of the query from which rows are being fetched.
• Information such as lock data that is shared and communicated among the processes.

Memory Structures
Database Buffer Cache
Shared Pool
Redo Buffers
Java pool
Large Pool
Streams Pool
Background process: Background process divided into 2 types

Mandatory Background Process
Optional Background Process

What is the database?
Database is the set of physical datafiles on disk which are created by create database statement.
What are the oracle Database memory structures?
Once started the oracle instance, Oracle Database allocates the memory areas and set of background process. These memory areas store’s the information of
1. Each session information (Both active and inactive)

Read More