读书笔记1 oracle architecture overview

1、仅安装oracle软件。查看dbs 目录，我们会得到一个实例参数文件。

[oracle@Centos01 dbs]$ pwd
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs
[oracle@Centos01 dbs]$ ls
init.ora
[oracle@Centos01 dbs]$ cat init.ora 
... 省略部分
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation as a starting point for
# customizing the Oracle Database installation for your site.    
#
# NOTE: The values that are used in this file are example values only.
# You may want to adjust those values for your specific requirements. 
# You might also consider using the Database Configuration Assistant     
# tool (DBCA) to create a server-side initialization parameter file
# and to size your initial set of tablespaces. See the
# Oracle Database 2 Day DBA guide for more information.
###############################################################################

# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)

db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

2、查看进程和共享内存

[oracle@Centos01 dbs]$ ps -ef|grep oracle
root      6022  5998  0 07:12 pts/0    00:00:00 su - oracle
oracle    6026  6022  0 07:12 pts/0    00:00:00 -bash
oracle    6118  6026  0 07:23 pts/0    00:00:00 ps -ef
oracle    6119  6026  0 07:23 pts/0    00:00:00 grep oracle
[oracle@Centos01 dbs]$ ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages    

实例没有启动，没有任何的oracle进程和共享内存。
mv init.ora init.ora.bak ，然后尝试使用 sqlplus 连接

[oracle@Centos01 dbs]$  sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 23 07:32:41 2016

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

[oracle@Centos01 dbs]$ oerr ORA 12162
12162, 00000, "TNS:net service name is incorrectly specified"
// *Cause:  The connect descriptor corresponding to the net service name in
// TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is
// incorrectly specified.
// *Action: If using local naming make sure there are no syntax errors in
// the corresponding connect descriptor in the TNSNAMES.ORA file. If using
// directory naming check the information provided through the administration
// used for directory naming.

因oracle 找不到环境变量 ORACLE_SID , 从而访问oracle 实例，于是报错
甚至 ORACLE_SID , 然后再此连接，发现连接成功。

[oracle@Centos01 dbs]$ export ORACLE_SID=ora12c
[oracle@Centos01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 23 07:38:14 2016

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

Connected to an idle instance.

SQL> 

此时我们可以发现oracle 产生了一个的服务器进程，但并没有分配共享内存。
尝试启动实例, 报错，说找不到参数文件

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initora12c.ora'

创建参数文件 iniora12c.ora , 并写入 db_name。 再次尝试启动oracle实例，发现通常情况下的所有必须后台进程均已启动并且共享内存和信号量也已创建。不过此时，我们还没有创建数据库，故没有数据库可以mount

[oracle@Centos01 dbs]$ cat initora12c.ora 
db_name=ora12c
[oracle@Centos01 dbs]$ ps -ef|grep oracle
root      6022  5998  0 07:12 pts/0    00:00:00 su - oracle
oracle    6026  6022  0 07:12 pts/0    00:00:00 -bash
oracle    6265  6026  0 07:53 pts/0    00:00:00 ps -ef
oracle    6266  6026  0 07:53 pts/0    00:00:00 grep oracle
[oracle@Centos01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 23 07:53:39 2016

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size                  2922712 bytes
Variable Size             159385384 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes

SQL> ! ps -ef|grep oracle
root      6022  5998  0 07:12 pts/0    00:00:00 su - oracle
oracle    6026  6022  0 07:12 pts/0    00:00:00 -bash
oracle    6268  6026  0 07:53 pts/0    00:00:00 sqlplus   as sysdba
oracle    6273     1  0 07:54 ?        00:00:00 ora_pmon_ora12c
oracle    6275     1  0 07:54 ?        00:00:00 ora_psp0_ora12c
oracle    6277     1  1 07:54 ?        00:00:00 ora_vktm_ora12c
oracle    6281     1  0 07:54 ?        00:00:00 ora_gen0_ora12c
oracle    6283     1  0 07:54 ?        00:00:00 ora_mman_ora12c
oracle    6287     1  0 07:54 ?        00:00:00 ora_diag_ora12c
oracle    6289     1  0 07:54 ?        00:00:00 ora_dbrm_ora12c
oracle    6291     1  0 07:54 ?        00:00:00 ora_vkrm_ora12c
oracle    6293     1  0 07:54 ?        00:00:00 ora_dia0_ora12c
oracle    6295     1  0 07:54 ?        00:00:00 ora_dbw0_ora12c
oracle    6297     1  0 07:54 ?        00:00:00 ora_lgwr_ora12c
oracle    6299     1  0 07:54 ?        00:00:00 ora_ckpt_ora12c
oracle    6301     1  0 07:54 ?        00:00:00 ora_smon_ora12c
oracle    6303     1  0 07:54 ?        00:00:00 ora_reco_ora12c
oracle    6305     1  0 07:54 ?        00:00:00 ora_lreg_ora12c
oracle    6307     1  0 07:54 ?        00:00:00 ora_pxmn_ora12c
oracle    6309     1  0 07:54 ?        00:00:00 ora_mmon_ora12c
oracle    6311     1  0 07:54 ?        00:00:00 ora_mmnl_ora12c
oracle    6312  6268  0 07:54 ?        00:00:00 oracleora12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6325  6268  0 07:55 pts/0    00:00:00 /bin/bash -c  ps -ef|grep oracle
oracle    6326  6325  0 07:55 pts/0    00:00:00 ps -ef
oracle    6327  6325  0 07:55 pts/0    00:00:00 grep oracle

SQL> ! ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 1310720    oracle     640        2924544    38                      
0x00000000 1343489    oracle     640        209715200  19                      
0x00000000 1376258    oracle     640        5464064    19                      
0xeaad1d20 1409027    oracle     640        8192       19                      

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     
0x6fec4500 196610     oracle     640        124       

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages    

创建数据库。

SQL> create database;

Database created.

当没有指明dbname的时候，oracle会自动到参数文件查找db_name的值，用于创建db所需。

SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> select name from v$database;

NAME
---------------------------
ORA12C

SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
ora12c

创建完database后，实例已启动到open状态，数据库可供正常使用。
但数据文件、日志文件和控制文件等都在同一目录里。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dbs1ora12c.dbf
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dbx1ora12c.dbf
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dbu1ora12c.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/log1ora12c.dbf
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/log2ora12c.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/rdbms/u01/app/oracle/product/12.1.0/dbhome_1/dbs/cntrlora12c.dbf

一个数据库不能备同个实例打开多于一次，要想再次打开只能先shutdown再open(单租户、容器数据库)。

SQL> alter database close;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

创建必须的数据字典

In SQL*Plus, connect to your Oracle Database instance with the SYSDBA administrative privilege:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
In SQL*Plus, connect to your Oracle Database instance as SYSTEM user:
@?/sqlplus/admin/pupbld.sql

具体可参考
https://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11083