Oracle笔记(10)——redo日志文件

以下摘自Oracle log files : An introduction

Online redo Oracle log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

Redo日志文件由redo record组成,每个redo record(又称作redo entry)又由一组change vector构成。

The online redo log file that Log Writer (LGWR) is actively writing to is called the current online redo log file. Online redo Oracle log files that are required for instance recovery are called active online redo log files. Online redo log files that are not required for instance recovery are called inactive.

参考下面例子:

SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         1          1             100 INACTIVE
         2          1             100 ACTIVE
         3          1             100 INACTIVE
         4          1             300 CURRENT

CURRENTLGWR进程正在写入的文件;ACTIVE是用于recovery的;INACTIVE则不是。

 

Oracle笔记(9)——checkpoint和CKPT进程

以下摘自Oracle 12c For Dummies

The checkpoint process(CKPT) is responsible for initiating check points. A check point is when the system periodically dumps all the dirty buffers to disk. Most commonly, this occurs when the database receives a shutdown command. It also updates the data file headers and the control files with the check point information so the SMON know where to start recovery in the event of a system crash.

CKPT进程是初始化checkpoint,而checkpoint的作用是让系统可以周期性地把“脏数据”更新到磁盘。

 

Oracle笔记(8)——latch

LatchOracle串行的并发访问内存的机制,也可以把latch看成是lock。当一个进程获取一个latch时,它或者成功或者失败。但是失败后,进程不会释放CPU,而是会继续尝试获取latch(具体行为取决于mode)。

参考资料:
Latches and Latch Contention
Latch and Mutex ContentionTroubleshooting in Oracle

 

Oracle笔记(7)——Oracle进程,server进程和background进程

Oracle手册上对于Oracle process的定义:

A unit of execution that runs the Oracle database code. The process execution architecture depends on the operating system. Oracle processes include server processes and background processes.

Oracle process即是运行Oracle数据库的代码,它分成server processbackground process两种。

Server process的定义:

An Oracle process that communicates with a client process and Oracle Database to fulfill user requests. The server processes are associated with a database instance, but are not part of the instance.

Server process可以看做是连接数据库client进程和数据库之间的桥梁,用来处理client的请求。虽然同Instance相关联,但不属于具体的Instance

Background process的定义:

A process that consolidates functions that would otherwise be handled by multiple Oracle programs running for each client process. The background processes asynchronously perform I/O and monitor other Oracle processes.

Background process用来做实际的I/O操作和监控其它Oracle进程。

参考资料:
Glossary

 

Oracle笔记(6)——SGA

Oracle手册上对于SGA(System Global Area)的定义:

The SGA is a read/write memory area that, along with the Oracle background processes, form a database instance. All server processes that execute on behalf of users can read information in the instance SGA. Several processes write to the SGA during database operation.

Note: The server and background processes do not reside within the SGA, but exist in a separate memory space.

Each database instance has its own SGA. Oracle Database automatically allocates memory for an SGA at instance startup and reclaims the memory at instance shutdown.

SGAOracle后台进程一起组成了数据库的Instance。每个Instance拥有自己的SGAInstance启动时会自动分配SGA,退出时会回收SGASGA包含下列组成部分:

✓ Shared pool
✓ Database buffer cache
✓ Redo log buffer
✓ Large pool
✓ Java pool
✓ Streams pool

参考资料:
Oracle 12c For Dummies
Overview of the System Global Area (SGA)

 

Oracle笔记(5)——连接数据库

Oracle的网络协议称之为Oracle NetSQL*NetNet8

连接数据库的两种方式:

(1)本地连接(Local

这种方式client和数据库位于同一台服务器上,数据通信不需要经过listener进程,而是通过Unix pipe方式进行通信。比如以SYS用户连接数据库:

sqlplus as / sysdba

或是利用ORACLE_SID环境变量指定本地数据库,然后使用用户名和密码进行连接:

sqlplus username/password

需要注意的是还有一个TWO_TASK环境变量指定远端数据库,而当这两个变量同时指定时,TWO_TASK优先级更高。

(2)远端连接(remote

这种方式client和数据库位于不同服务器上,连接时需要指定连接的数据库TNS名字:

sqlplus username/password@orcl

这种方式需要利用tnsnames.ora文件来解析数据库主机和端口,此外数据通信也要经过listener进程。

区分“本地连接”和“远端连接”的一个方法就是看连接数据库时是否指定数据库TNS名字:@tns_alia

(3)

Oracle client和数据库之间通信使用TNS(Transparent Network Substrate)协议,可以用wireshark解析:

Capture

 

参考资料:

research oracle tns protocol

Oracle Database Communication Protocol

Oracle 12c For Dummies ;

http://ora-exp.blogspot.com/2007/06/oraclesid-and-twotask-environment_21.html

Re: sqlplus connection from unix with and without the oracle sid

In Oracle, how do you change the ‘default’ database?

Should we consider “sqlplus” as an Oracle client?

SYSDBA password in Oracle DB

 

Oracle笔记(4)——tnsnames.ora文件

tnsnames.ora文件包含着Oracle数据库连接信息,是供client使用的(TNS代表Transport Network Substrate),位于$ORACLE_HOME/network/admin目录。举例如下:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = npar1.xxxxxx.com)(PORT = 9005))
     (CONNECT_DATA =
      (SID = ORCL)
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

client发起连接数据库请求:“"connect system/system@ORCL"”,就需要解析tnsnames.ora来获得Oracle数据库的地址和端口信息。在配置文件中,HOST指定数据库instance运行的主机,PORTlistener进程监听的端口。

参考资料:
LISTENER.ORA and TNSNAMES.ORA
Local Naming Parameters in the tnsnames.ora File

 

Oracle笔记(3)——Net Listener进程

Oracle Net Listener是一个独立的进程,负责处理client的请求,并把这些请求分发给相应的database instance。配置文件是listener.ora,位于$ORACLE_HOME/network/admin目录下。修改这个文件必须重启listener进程。

listener.ora如下所示:

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/lis
tener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = db01)
   (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
   (SID_NAME = db01)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = dev12c)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = dev12c)
  )
 )
LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
  )
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
 )

SID_LIST_LISTENER部分指定listener进程需要处理的数据库连接;而LISTENER部分指定这些数据库所在的主机,以及listener进程需要监听的端口。

在数据库能够服务客户端请求之前,必须要启动listener进程。使用lsnrctl命令可以启动和停止listener进程:

$ lsnrctl start
$ lsnrctl stop

lsnrctl status命令可以用来检查listener的状态。

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-FEB-2016 04:55:25

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=npar1.atc-hp.com)(PORT=9012)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-FEB-2016 01:01:06
Uptime                    12 days 3 hr. 54 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/npar1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=npar1.atc-hp.com)(PORT=9012)))
Services Summary...
Service "ORCL2" has 1 instance(s).
  Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

参考资料:
Configuring and Administering Oracle Net Listener
How to Configure the Database Listener with Listener.ora in Oracle 12c

 

Oracle笔记(2)——Oracle相关的环境变量

安装和使用Oracle时相关的环境变量:

(1)ORACLE_BASE
安装Oracle时的根目录,默认值为/u01/app/oracle

(2)ORACLE_HOME
安装特定版本Oracle时的目录,例如$ORACLE_BASE/product/11.2.0/dbhome_1

(3)ORACLE_SID
标明数据库的System Identifier(SID)SID用来代表某个数据库实例(instance),用来同其它instance相区分。

参考资料:
Administering Oracle Database
http://www.orafaq.com/wiki/ORACLE_SID
Oracle Database – Environment Variables / Registry Values

 

Oracle笔记(1)——Database和Instance的区别

DatabaseInstance的区别如下:

What is a Database?

We already know that a database is a collection of data. And this data is stored in form of tables at logical level, and in the datafiles at the physical level. There are some other files as well like Redo log files, Control files, Initialization files which stores important information about the database.

What is an Instance?

To view or update data stored in tables/datafiles, Oracle must start a set of background processes, and must allocate some memory to be used during database operation. The background processes and memory allocated by Oracle together make up an Instance.

简言之,Database是存放实际数据的所有文件。为了操作这些文件,就需要有Instance:访问文件的进程和内存。

Oracle 12c For Dummies中关于DatabaseInstance二者之间关系的总结:

✓ An instance can exist without a database. Yes, it’s true. You can start an Oracle instance and not have it access any database files. Why would you do this?
• This is how you create a database. There’s no chicken-or-egg debate here. You first must start an Oracle instance; you create the database from within the instance.
• An Oracle feature called Automatic Storage Management uses an instance but isn’t associated with a database.
✓ A database can exist without an instance but would be useless. It’s just a bunch of magnetic blips on the hard drive.
✓ An instance can access only one database. When you start your instance, the next step is to mount that instance to a database. An instance can mount only one database at a time.
✓ You can set up multiple instances to access the same set of files or one database. Clustering is the basis for the Oracle Real Application Clusters feature. Many instances on several servers accessing one central data- base allows for scalability and high availability.

参考资料:

Oracle 12c For Dummies

database vs instances

Difference between Oracle Instance & Database.