Oracle DBA学习杂记（二）

How to Creating a DB Manually:

   Choose a unique instance and database name (SID)

   Choose a database character set

   Set operating system variables (必设的是ORACLE_SID和ORACLE_HOME两个)

   Create the initialization parameter file

   Start the instance in NOMOUNT stage

   Create and execute the CREATE DATABASE command

   Run scripts to generate the data dictionary and accomplish post-creation steps

   Create additional table-spaces as needed

 

手工创建数据库过程中创建数据字典必须执行的三个脚本：

$ORACLE_HOME/rdbms/admin/catalog.sql

$ORACLE_HOME/rdbms/admin/catproc.sql

$ORACLE_HOME/sqlplus/admin/pupbld.sql

 

Build-In Database Objects包括Data dictionary、Performance tables、PL/SQL packages、Database event triggers

数据字典的作用就是描述数据库和数据库的对象，它只属于SYSTEM表空间，Contains read-only tables and views，Owned by the user SYS。

基表(Base Table)的脚本：sql.bsq

 

The data dictionary provides information about: Logical and physical database、Privileges、Users、Roles、Auditing、Definitions and space allocations of objects, Oracle Server modifies it when a DDL statement is executed.

 

DD static view按照范围大小依次划分为三大类：DBA、ALL、USER

 

Dynamic Performance Tables are listed in V$FIXED_TABLE，Owned by SYS user, synonyms begin with V$

 

Control file is a small binary file, defines current state of physical database, required at MOUNT state during database startup, if loss may require recovery.

 

The control file is a fairly small file (it can grow up to 64MB or so in extreme cases). The parameter file tells the instance where the control files are, and the control files tell the instance where the database and online redo log files are. The control file also tell Oracle other things, such as information about checkpoints that have taken place, the name of the database, an archive redo log history, RMAN information and so on.

 

Every Oracle Database should have at least two control files, each stored on a different physical disk.

The database reads only the first file listed in the CONTROL_FILES parameter during database operation.

 

对Oracle数据库来说最重要的两种文件是数据文件和联机重做日志文件，只要这两个文件不丢，其他的文件丢失了都能够通过各种办法恢复

 

Oracle在把数据写到数据文件里去之前一定会先写联机重做日志文件

一个Oracle数据库至少需要两个Redo log file group

LGWR后台进程会同步的向同一Redo log file group的所有成员里写入同样的信息(所以每个成员都是相同的大小)，在Redo log file group里的每一个成员都拥有一个Log sequence numbers

 

When a online redo log file is full, LGWR will move to the next log group, called a log switch, checkpoint operation also occurs, information written to the control file. (Log switch引发DBWn执行检查点)

 

强制执行LS： ALTER SYSTEM SWITCH LOGFILE

强制执行检查点： ALTER SYSTEM CHECKPOINT

参数FAST_START_MTTR_TARGET表示每隔number of seconds，DBWn必须去写一次Data file

 

You can drop a redo log member only if it is not part of an active or current group. When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structutre.

Drop命令：ALTER DATABASE DROP LOGFILE MEMBER ‘***’

 

The redo log for each database instance is also referred to as a redo thread. In typical configurations, only one database instance accesses an Oracle Database, so only one thread is present, but in RAC two or more instance concurrently access a single database and each instance has its own thread of redo.

 

By default, the database is created in NOARCHIVELOG mode.

Archived Redo Log files can be accomplished automatically by ARCn or SQL statement.

 

Table-space只是一个逻辑概念，物理上是由许多Data File组成的一个集合，Data File只能属于一个Table-space和一个数据库，一个数据库可以由一个或多个表空间组成

Oracle的表空间分成两大类：SYSTEM和Non-SYSTEM，SYSTEM表空间包括SYSTEM Undo Segment, 从种类上来分又可以分成undo、permanent和temporary三种

 

要查看表空间的详细情况，可以从dba_data_files表里看到信息

表空间的管理方式：Locally managed table-space和Dictionary managed table-space

在DD方式中，Free extents are managed by the data dictionary, 而在Local方式中，Free extents are managed in table-space and bitmap is used to record free extents. (Bit value indicate free or used)

 

When the SYSTEM table-space is locally managed, you must define at least one default temporary table-space when creating a database.

Segment的几大种类：Table、Table partition、Cluster、Index、Index-organized table、Undo segment、Index partition、Temporary segment、LOB segment、Nested table、Bootstrap segment

 

DB_CACHE_SIZE specifies the size of the DEFAULT buffer cache for standard block size, minimum size = one granule (4MB or 16MB), default value = 48MB

 

All partitions of a partitioned object must reside in table-spaces of the same block size

All temporary table-spaces, including the permanent ones that are being used as default temporary table-spaces, must be of standard block size.

 

INITRANS and MAXTRANS specify initial and the maximum number of transaction slots that are created in an index or a data block. The transaction slots are used to store information about transactions that are making changes to the block at a point time. A transaction uses only one transaction slot, even if it is changing more than one row or index entry.

 

INITRANS guarantees a minimum level of concurrency. It defaults to 1 for a data segment and 2 for an index segment, guarantees a minimum level of concurrency. If necessary, additional transaction slots can be allocated from the free space in the block to permit more concurrent transaction to modify rows in the block.

 

MAXTRANS default value is 255, sets the limit for the number of concurrent transactions that can make changes to a data or an index block. When set, this value restricts use of space for transaction slots and therefore guarantees that there is sufficient space in the block for use by row or index data.

 

Both PCTFREE and PCTUSED are calculated as percentages of available data space, that is, the block space that remains after deducting the header space from the total block size. PCTFREE is reserved for growth resulting from updates to rows in the block. A block is put back on the free list when its used space falls below PCTUSED.

 

Delete操作不会把Water Mark置位，但Trunc操作会将Water Mark归位

Undo segment的作用：Transaction recovery、Transaction rollback和Read Consistency

Oracle在执行查询的时候会给SELECT分配一个SCN号，只增不减的一个序列号，为防止读到脏数据只会去读SCN号更小的数据块

 

Oracle在执行100万条记录插入的提交和1条记录的插入提交几乎花费的时间是一样的，因为Redo Log的写磁盘操作每隔3秒钟就会发生一次，最后Commit的时候其实只有少量的Redo Log数据需要写入磁盘，而数据一直都在缓存中除非到了检查点，所以Redo信息才是真正消耗时间的

 

Undo table-space可以互相切换，数据库任何时候只能有一个Undo table-space出于使用状态

UNDO_RETENTION parameter controls the amount of undo data to retain for consistent read.

UNDO_SUPPRESS_ERRORS parameter suppresses errors while attempting to execute manual operations in AUTO mode.

 

Determining a size for the UNDO table-space requires 3 pieces of information: UNDO_RETENTION in seconds、Number of undo data blocks generated per second、Overhead varies based on extent and file size

可以从以下动态视图中查到undo块统计信息：V$ROLLNAME、V$ROLLSTAT、V$UNDOSTA等

 

Oracle will internally store a row with more than 254 columns in separate row pieces that point to each other and must be reassembled to produce the entire row image. A table can have up to 1000 columns. Typically, a table-space can have at most 1022 files.

 

ROWID的格式(Base64编码)：【Data object number】【Relative file number】【Block number】【Row number】

ROWID provides the fastest means of accessing a row in a table

Because a segment can only reside in one table-space, by using the data object number, the Oracle server can determine the table-space that contains a row.

 

Columns for a row are generally stored in the order in which they are defined and any trailing NULL columns are not stored. A single byte for column length is required for non trailing NULL columns.
Each row in the block has a slot in row directory. The directory slot points to the beginning of the row.

 

FREELISTS: MSSM only, every table manages the blocks it has allocated in the heap on a free-list..

PCTFREE: Both ASSM and MSSM, a measure of how full a block can be is made during the INSERT process.

PCTUSED: MSSM only, a measure of how empty a block must become before it can be a candidate for insertion.

 

Use locally managed table-space can avoid fragmentation.

Use few standard extent sizes for tables to reduce table-space fragmentation.

 

Temporary tables are used to hold intermediate result sets, for the duration of either a transaction or a session.

Temporary tables will allocate storage from the currently logged-in user’s temporary table-space, or if they are accessed from a definer rights procedure, the temporary table-space of the owner of that procedure will be used.

 

The act of creating a temporary table involves no storage allocation, no INITIAL extent is allocated, as it would be for a regular table. (临时表只是一个逻辑定义，创建的时候实际存储并不存在, 直到用户要插入记录才会临时从临时表空间里分配物理存储)

 

If you only INSERT and SELECT from temporary tables, the amount of redo generated will not be noticeable, only if DELETE or UPDATE a temporary table heavily will you see large amounts of redo generated.

 

Compute PCTFREE: (Average Row Size – Initial Row Size) * 100 / Average Row Size

 

Row Migration: if PCTFREE is set to a low value, there may be insufficient space in a block to accommodate a row that grows as a result of an update. When this happens, the Oracle Server will move the entire row to a new block, and leave a pointer from the original block to the new location. (I/O performance decrease)

 

Row Chaining: it occurs when a row is too large to fit into any block. This might occur when the row contains columns that are very long. In this case, the Oracle Server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with the necessary pointers to retrieve and assemble the entire row.

 

When a non-partitioned table is reorganized, its structure is kept, but not its contents. It is used to move a table to a different table-space or reorganize extents.

 

查看表的元数据信息执行如下PL/SQL包：select dbms_metadata.get_ddl(‘TABLE’,’table_name’) from dual;

 

Truncating a table deletes all rows in a table and release used space.

No undo data is generated and command commits implicitly because TRUNCATE TABLE is a DDL command

A table that is being referenced by a foreign key cannot be truncated.

 

When a table is dropped, the extents used by the table are released. If they are contiguous, they may be coalesced either automatically or manually at a later stage. The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

如果要删除的表非常大，则可以加上例如Checkpoint 1000表示每删除1000行就执行一个检查点，以防止删除操作把整个Undo空间占满

 

Renaming a Column: ALTER TABLE table_name RENAME COLUMN old TO new (如果此列存在Join Index则无法直接修改)。Instead of removing a column from a table, the column can be marked as unused and then removed later, unused columns act as if they are not part of the table.