Oracle学习笔记一

最新推荐文章于 2021-04-02 23:31:40 发布

wanchengpeng

最新推荐文章于 2021-04-02 23:31:40 发布

阅读量1.8k

点赞数 1

分类专栏：数据库技术文章标签： oracle database table file user system

本文链接：https://blog.csdn.net/wanchengpeng/article/details/4488987

版权

数据库技术专栏收录该内容

2 篇文章 0 订阅

订阅专栏

PART 1: Introduction

1. Oracle Database is composed of the following elements:

1) The Oracle software that you install on your host computer.

2) The database , which is a collection of physical files on one or more disks.

The database contains user data , metadata , and control structures . Metadata, or data about the data, is the collection of information about a disk that permits Oracle software to manage user data. An example of metadata is the data dictionary. Control structures (such as the control file and redo log) ensure the integrity, availability, and recoverability of user data.

3) The Oracle instance , which is composed of the following:

- The background processes, which are the operating system processes or threads that perform the work of accessing, storing, monitoring, and recovering user data, metadata, and control files associated with the database.

- The shared memory areas used by the background processes

4) Server processes that perform work on behalf of connected users and applications, and the memory and temporary storage used by these processes.

Server processes parse and execute SQL statements, and retrieve and return results to the user or application.

5) Oracle Net , which is a software layer that enables client applications and Oracle Database to communicate over a network, and the Oracle Net listener , which is a process that listens for connection requests from the network.

2. Common Oracle DBA Tasks

As an Oracle DBA, you can expect to be involved in the following tasks:

- Installing Oracle software

- Creating Oracle databases

- Performing upgrades of the database and software to new release levels

- Starting up and shutting down the database

- Managing the storage structures of the database

- Managing users and security

- Managing database objects, such as tables, indexes, and views

- Backing up the database and performing recovery operations when necessary

- Monitoring the state of the database and taking preventive or corrective action as required.

- Monitoring and tuning database performance

- Diagnosing and reporting critical errors to Oracle Support Services.’

In a small to medium-sized database environment, you might be the sole person performing these tasks. In large, enterprise environment, the job is often divided among several DBAs, each of whom has a specialty, such as database security or database tuning.

3. Tools for Administering the Database

The following are some of the products, tools, and utilities you can use to achieve your goals as a database administrator:

- Oracle Universal Installer

Oracle Universal Installer (OUI) is a utility that installs your Oracle software and options. It can automatically start Oracle Database Configuration Assistant to install a database.

- Oracle Database Configuration Assistant

Oracle Database Configuration Assistant (DBCA) is a utility that creates a database from templates that are supplied by Oracle, or you can create your own.

It enables you to copy a preconfigured seed database, thus saving the time and effort of generating and customizing a new database.

- Database Upgrade Assistant

The Database Upgrade Assistant is a tool that guides you through the upgrade of your existing database to a new Oracle Database release.

- Net Configuration Assistant

Net Configuration Assistant (NETCA) is a utility that enables you to configure listeners and naming methods, which are critical components of the Oracle Database network.

- Oracle Enterprise Manager Database Control

The primary product for managing your database is Oracle Enterprise Manager Database Control (Database Control), a Web-based interface. After you have installed the Oracle Database software, created or upgraded a database, and configured the network, you can use Database Control to manage your database.

Database Control also provides an interface for performance advisors and for Oracle utilities such as SQL*Loader and Recovery Manager (RMAN).

4. Kinds of storage subsystem

A database includes several files that store the user data, database metadata, and information required to recover from failures. As an administrator, you decide what kind of storage subsystem to use for these files. You can select from the following options:

- File System: This default option creates database files that are managed by the file system of your operating system. You can specify the directory path where database files are to be stored. Oracle Database can create the manage the actual files. If you are not certain about which option to use, select File System (the default).

- Automatic Storage Management: The option enables you to define pools of storage called disk groups, in which Oracle Database automatically manages database file placement and naming. For environments with a large number of disks, this option simplifies database administration and maximizes performance. Automatic Storage Management (ASM) performs software striping and mirroring at the file level for maximum storage flexibility, performance, and availability.

- Raw Devices: This option enables you to manage your storage devices outside of the operating system file system by providing Oracle Database with unformatted physical disk space called raw devices. This method is primarily used in Oracle Real Application Clusters (Oracle RAC) environments. If you select this option, ensure that the raw devices were created with an operating system command, and that they are not being used for any other purpose or by other Oracle database.

PART 2: Getting Started with Database Administration

1. Starting and Stopping the Database Control Console Process

To start or stop, or to view the status of the dbconsole process from the command like:

1) Open a operating system command window and go to your Oracle_home/bin directory.

2) Do one of the following:

n To start the dbconsole process, run the following command:

./emctl start dbconsole

n To stop the dbconsole process, run the following command:

./emctl stop dbconsole

n To view the status of the dbconsole process, run the following command:

./emctl status dbconsole

For no administrative users to have access to Database Control, they must be granted the SELECT_CATALOG_ROLE role.

PART 5 Managing Database Storage Structures

1. About Database Storage Structures

An Oracle database is made up of physical and logical structures. Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on a disk.

Logical structures are created and recognized by Oracle Database and are not know to the operating system. The primary logical structure in a database, a table space, contains physical files.

Figure 6-1 shows the relationships between logical and physical structures. This figure also shows recovery-related structures that are optionally kept in the flash recovery area.

2. About Control File

A control file tracks the physical components of the database. It is the root file that the database uses to find all the other files used by the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed. In other words, the control file should have multiple identical copies. For databases created with Oracle Database Configuration Assistant (DBCA), three copies of the control file area automatically created and kept synchronized with each other.

If any control file fails, then your database becomes unavailable. As long as you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database using the remaining control files.

3. About online redo Log Files

Every Oracle database has a set of two or more online redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.

The redo log stores a copy of the changes made to data. If a failure requires a data file to be restored from backup, then the recent data changes that are missing from the restored data file can be obtained from the redo log, so work is never lost. The redo log is used to recover a database after hardware, software, or media failure. To protect against a failure involving the redo log itself, Oracle Database can multiplex the redo log so that two or more identical copies of the online redo log can be maintained on different disks.

The redo log for a database consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is considered to be a member of that group. Each group is defined by a number, such as Group 1.

Figure 6-3 shows the configuration of a database that has three redo log groups and two members in each group. For each group, the members are stored on separate disks for maximum availability. For example, the members of Group 1 are the redo log files A_LOG1 and B_LOG1.

The database log writer process (LGWR) writes redo records from the memory buffer to a redo log group until the log files in that group reach their storage size limit, or until you request a log switch operation. The LGWR process then writes to the next log group. The LGWR process performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.

4. About Archived Redo Log Files

When you archive your redo log, you copy the redo log files to another location before they are overwritten. These copied files are referred to as archived redo log files. You can archive to multiple locations, including a standby database.

These archived redo log files extend the amount of redo data that can be saved and are used for recovery. Archived redo log files are required to recover a backup of the database from the time of the backup to the current time. Archiving can be either enabled or disabled for the database, but Oracle strongly recommends that you enable archiving. Oracle also recommends that you configure the database to write archived redo log files to the flash recovery area.

5. About Rollback Segments

Rollback segments were database structures used to track undo information for the database in earlier release of Oracle Database. Now, the preferred way of managing undo information is with the undo table space.

6. About Data Files

Data files are the operating system files that store the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. Temp Files are a special class of data files that are associated only with temporary table spaces. Data files can be broken down into the following components:

² Segment:

A Segment contains a specific type of database object. For example, a table is stored in a table segment, and an index is stored in an index segment. A data file can contain many segments.

² Extent

An extent is a contiguous set of data blocks within a segment. Oracle Database allocated space for segments in units of one extent. When the existing extents of a segment are full, the database allocates another extend for that segment.

² Data block

A data block, also called a database block, is the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.

After the database has been created, it is not possible to change the default block size without re-creating the database. It is possible, however, to create a table space with a block size different than the default block size.

7. About Table Space

A database is divided into logical storage units called table spaces, which group together related logical structures (such as tables, views, and other database objects). For example, all application objects can be grouped into a single table space to simplify maintenance operations.

A table space consists of one or more physical data files. Database objects assigned to a table space are stored in the physical data files of that table space.

When you create an Oracle database, some table spaces already exists, such as SYSTEM and USERS.

Table spaces provide a means to physically locate data on storage. When you define the database make up a table space, you specify a storage location for these files. For example, you might specify a data file location for a certain table space as a designated host directory (implying a certain disk volume) or designated Automatic Storage Management disk group. Any schema objects assigned to that table space then get located in the specified storage location. Table spaces also provide a unit of backup and recovery. The backup and recovery feature of Oracle database enable you to backup or recover at the table space level.

You can create new table space to support your user and application data requirements. During table space creation, you set the following parameters:

² Locally Managed Table spaces Compared to Dictionary-Managed Table spaces

Space Managed within a table space involves keeping track of available (free) and used space, so that space is allocated efficiently during data insertion and deletion.

Oracle recommends creating locally managed table spaces rather than dictionary-managed table spaces. Dictionary-managed table space is an older space management technique and not as efficient as locally managed table spaces.

Locally managed table spaces keep the space allocation information within the table space, not in the data dictionary, thus offering better performance. By default, Oracle Database sets all newly created table space to be locally managed with automatic segment management, a feature that further improves performance.

² Table space Type

There are three types of table spaces:

- Permanent: Oracle Database uses permanent table spaces to store permanent data, such as system data. You use permanent table spaces to store your user and application data. Each user is assigned a default permanent table space.

- Undo: A database running in automatic undo management mode transparently creates and manages undo data in the undo table space. Oracle Database uses undo data to roll back transactions, to provide read consistency, to help with database recovery, and to enable features such as Oracle Flashback Query.

Even though you can create more than one undo table space, only one can be active. If you want to switch the undo table space used by the database instance, then you can create a new one and instruct the database to use it instead. The undo table space no longer in use can then be removed from the database (or dropped).

- Temporary: Temporary table spaces are used for storing temporary data, as would be created when SQL statements perform sort operations. An Oracle database gets a temporary table space when the database is created. You would create another temporary table space if you were creating a temporary table space group. Under typical circumstances, you do not need to create additional temporary table spaces. If you have an extremely large database, then you might configure additional temporary table spaces.

The physical files that make up a temporary table space are called temp files, as opposed to data files.

The TEMP table space is typically used as the default temporary table space for users who are not explicitly assigned temporary table space.

² Table space Status

You can set table space status as follows:

- Read Write: Users can read and write to the table space after it is created. This is the default.

- Read Only: If the table space is created Read Only, then the table space cannot be written to until its status is changed to Read Write. It’s unlikely that you would create a Read-Only table space, but you might change it to that status after you have written data to it that you don’t want modified.

- Offline: If the table space is created offline, then no user can access it. It is unlikely that you will create an Offline table space, but later you might change its status to Offline to perform maintenance on its data files.

² Auto-extend Table space

You can set a table space to automatically extend itself by a specific amount when it reaches its size limit. If you don’t enable auto extend, you are alerted when the database reaches its critical or warning threshold size. The critical and warning threshold parameters have default values that you can change at any time. These parameters also cause alerts to be generated for auto extending table spaces that are approaching their specified size limit. You can respond to size alerts by manually increasing the table space size. You do so by increasing the size of one or more of the table space data files or by adding another data file to the table space.

About Other Storage Structures

Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files.

Initialization parameters are used by the Oracle instance at startup to determine the run-time properties and resources for the database.

A database can use a password file to authenticate administrative users with SYSDBA or SYSOPER connection privileges. The privileges enable a DBA to start up and shutdown the database and perform other high-level administrative tasks. This password file is outside of the database itself, thereby enabling the authentication of a DBA when the database is not yet start.

Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure cause loss of data.

PART 6: Administering User Accounts and Security

1. About User Accounts

For users to access your database, you must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a user name and defines the attributes of the user, including the following:

² Authentication method

² Password (encrypted) for database authentication

² Default table space for permanent and temporary data storage

² Table space quotas

² Account status (locked or unlocked)

² Password status (expired or not)

When you create a user account, you must not only assign a user name, a password and default table space for the account, but you must do the following:

² Grant the appropriate system privileges, Object privileges, and roles to the account.

² If the user will be creating database objects, give the user account a space usage quota on each table space in which the objects will be created.

Oracle recommends that you grant each user just enough privileges to perform his job, and no more.

When you create a user account, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

When you delete a user, you must either simultaneously delete all schema objects of that user, or you must have previously deleted the schema objects in separate operations.

In addition to the user accounts that you create, the database includes a number of user accounts that are automatically created upon installation. All databases include the administrative accounts SYS, SYSTEM, SYSMAN, and DBSNMP.

All databases also include internal accounts, which are automatically created so that individual Oracle Database features or components can have their own schemas.

Your database may also include sample schemas, which are a set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks. These schemas also provide a way for you to experiment without endanger production data.

About User Privileges and Roles

User privileges provide a basic level of database security. They are designed to control user access to data and to limit the kinds of SQL statements that users can execute. When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, to create schema objects, and more. There are two main types of user privileges:

n System privileges --- A system privilege gives a user the ability to perform a particular action, or to perform an action to any schema objects of a particular type. For example, the system privilege CREATE TABLE permits a user to create tables in the schema associate with that user, and the system privilege CREATE USER permits a user to create database users.

n Object privileges --- An object privilege gives a user the ability to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privileges to select rows from the EMPLOYEES table or delete rows from the DEPARTMENT table are examples of object privileges.

PART 7: Performing Backup and Recovery

1. Overview of Database Backup and Recovery

The focus in Oracle database backup and recovery is on the physically backup of database files, which permits you to reconstruct your database. The files protected by the backup and recovery facilities built into Oracle Enterprise Manager Database Control (Database Control) include data files, control files, server parameter files, and archived redo logs. With these files you can reconstruct your database. The backup mechanisms work at the physical level to protect against file damage, such as the accidental deletion of a data file or the failure of a disk drive.

Oracle Recovery Manager (RMAN), a command-line and Enterprise Manager-based tool, is the method preferred by Oracle for efficiently backing up and recovering your Oracle database.

Logical backups, such as exporting database objects such as tables or table spaces, are a useful supplement to physical backups, but cannot protect your whole database. An effective backup strategy must be based on physical backups.

The Oracle Database flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. The flashback features enable you to reverse the effects of unwanted database changes without restoring data files from backup or performing media recovery.

2. Database Backup and Recovery Concepts

To back up database means to make copies of your data files, control files, and archived redo logs (if your database runs in ARCHIVELOG mode). Restoring a database means copying the physical files that make up the database from a backup medium, typically disk or tape, to their original or to new locations. Database recovery is the process of updating database files restored from a backup with the changes made to the database after the backup, typically using redo log files.

Consistent and Inconsistent Backups

A backup is either consistent or inconsistent. To make a consistent backup, your database must have been shut down cleanly and remain closed for the duration of the backup. All committed changes in the redo log are written to the data files, so the data files are in a transaction-consistent state. When you restore your data files from a consistent backup, you can open the database immediate.

If the database is in ARCHIVELOG mode, then you can make inconsistent backups that are recoverable using archived redo log files. Open database backups are inconsistent because the online redo logs contain changes not yet applied to the data files. The redo logs must be archived and then backed up with the data files to ensure recoverability.

Despite the name, an inconsistent backup is as robust a form of backup as a consistent backup. The advantage of making inconsistent backups is that you can backup your database while the database is open for update.

3. Media Recovery

If you restore the archived redo logs and data files, then you must perform media recovery before you can open the database. Any database transaction in the archived redo logs not already reflected in the data files are applied to the data files, bringing them to a transaction-consistent state before the database is opened.

Media recovery requires a control file, data files (typically restored from backup), and online and archived redo logs containing changes since the time the data files were backed up. Media recovery is most often used to recover from media failure, such as the loss of a file or disk, or a user error, such as the deletion of the contents of a table.

Media recovery can be a complete recovery or a point-in-time recovery. In a complete recovery, you restore backup data files and apply all changes from the archived and online redo logs to the data files. The database is returned to its state at the time of failure and can be opened with no loss of data.

In a point-in-time recovery, you return a database to its contents at a user-selected time in the past. You store a backup of data files created before the target time and a complete set of archived redo log files from backup creation through the target time. Recovery applies changes between the backup time and the target time to the data files. All changes after the target time are discarded.

4. Flash Recovery Area

To simplify the management of backup and recovery files, you can create a flash recovery area for your database. The flash recovery area is a Oracle managed dictionary, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. Oracle creates archived logs and flashback logs in the flash recover area. RMAN can store its backup sets and image copies in the flash recovery area, and it uses it when storing files during media recovery. The flash recovery area also acts as a disk cache for tape.

Oracle Database automatically manages this storage, deleting files that are no longer needed. Periodically copying backups to tape and frees space in the flash recovery area for other files. Oracle recommends that you enable a recovery area to simplify backup management.

5. RMAN Repository

RMAN maintains a record of database files and backups for each database on which it performs operations. This metadata is called the RMAN repository.

If you backup a file without using RMAN, such as by copying the file at the host operating system level, then you can add metadata about the copy to the RMAN repository. If you later use a command such as RESTORE DATABASE, then RMAN uses the records in the repository to select backups needed for the recovery.

The primary location for the RMAN repository for a database is its control file. The importance of this metadata for RMAN is one more reason why protecting your control file is a vital part of you backup strategy.

6. About Automatic Storage Management

Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager. You use AMS instead of an operating system file system to store your database files.

ASM is based on the principle that the database should manage store instead of requiring an administrator to do it. ASM eliminates the need for you to manage potentially thousands of database files.

ASM groups the disk in your storage system into one or more disk groups. You manage a small set of disk groups, and ASM automates the placement of the database files within those disk groups.

ASM provides the following benefits:

Striping – ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.

Mirroring – ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extend of the file to help avoid data loss caused by disk failure. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.

ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring where each file extent gets tow mirrored copies.

Online storage reconfiguration and dynamic rebalancing – ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances the disk group by evenly relocating all the file extents from the disk being removed to the other disks in the disk group.

Managed file creation and deletion – ASM further reduces administrative tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns file names when files are created, and automatically deletes files when they are no longer needed.

Oracle recommends that you use ASM for your database file storage, instead of raw devices or the operating system file system.