【Oracle学习13】 Oracle管理工具及实例管理

13.1 使用数据库管理工具

13.1.1 SQL*PLUS

lrwrp: 可以改进sqlplus使用体验。

#配置epel源
vim epel.repo 
[aliyun_epel]   #这是Redhat的扩展包
name=ailiyun_yum_epel
baseurl=https://mirrors.aliyun.com/epel/$releasever/x86_64
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
gpgcheck=0

yum install rlwrap  -y
#切换到oracle用户权限
su - oracle
vim  ~/.bash_profile 
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
alias ggsci="rlwrap ggsci"
#生效
source ~/.bash_profile 
#测试  alias

SQLPLUS使用:

• 帮助

sqlplus [username[/password[@database]]]
SQL> help index
@             COPY         PASSWORD                 SHOW
@@            DEFINE       PAUSE                    SHUTDOWN
/             DEL          PRINT                    SPOOL

#查看表结构
DESC[RIBE] tablename

• 执行sql脚本

$vim test.sql
select * from hr.departments where location_id = 1400;
quit
$sqlplus hr/hr @test.sql

• 执行shell脚本

$cat batch_sqlplus.sh

# Name of this file: batch_sqlplus.sh
# Count employees and give raise.
sqlplus hr/hr <<EOF
select count(*) from employees;
update employees set salary = salary*1.10;
commit;
quit
EOF
$sh batch_sqlplus.sh

• 执行命令

#编辑SQL
SQL> select FIRST_NAME,last_name
  2   from employees
  3  where employee_id=113;
FIRST_NAME LAST_NAME
--------------------------------------------------
Luis Popp

SQL> 3
  3* where employee_id=113
SQL> change /113/100
  3* where employee_id=100

SQL> list
  1  select FIRST_NAME,last_name
  2   from employees
  3* where employee_id=100
SQL>/ 执行
FIRST_NAME LAST_NAME
--------------------------------------------------
Steven King

#保留
SQL> save /home/oracle/temp.sql
Created file /home/oracle/temp.sql
SQL> !ls -l /home/oracle/temp.sql
SQL> !cat /home/oracle/temp.sql
select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=100
/
#读取文件
SQL> get /home/oracle/temp.sql
  1* select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=100
#执行文件
SQL> start temp.sql
EMPLOYEE_ID FIRST_NAME LAST_NAME
--------------------------------------------------
100 Steven King
SQL> @temp.sql    --相同于start temp.sql

#编辑
SQL> edit 
Wrote file afiedt.buf
select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=200
/
qw
SQL>/ 执行

• Spool

SQL> spool spool_op.txt
SQL> select EMPLOYEE_ID,first_name from employees where EMPLOYEE_ID=200;
EMPLOYEE_ID FIRST_NAME
----------- ----------------------------------------
        200 Jennifer

SQL> c /200/100
  1* select EMPLOYEE_ID,first_name from employees where EMPLOYEE_ID=100
SQL> /
EMPLOYEE_ID FIRST_NAME
----------- ----------------------------------------
        100 Steven

SQL> spool off
SQL> !cat spool_op.txt

• AUTOTRACE

语法: SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> SET AUTOTRACE ON
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    11 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=200)

Statistics
----------------------------------------------------------
        173  recursive calls
          0  db block gets
        248  consistent gets
          0  physical reads
          0  redo size
        626  bytes sent via SQL*Net to client

#关闭
SQL> set AUTOTRACE off

• 格式化

SQL> set lines 200  --即set linesize 200
SQL> set pagesize 200
SQL> desc v$parameter;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 DEFAULT_VALUE                                      VARCHAR2(255)
#如下会特别乱
SQL> Select name, type,value,isdefault from v$parameter where name ='db_file_multiblock_read_count'; --会特别乱

# 设置格式
Col name format a30
Col type for 9
Col value for a20
Set line size 150;
SQL>  Select name, type,value,isdefault from v$parameter where name ='db_file_multiblock_read_count';
NAME                           TYPE VALUE                ISDEFAULT
------------------------------ ---- -------------------- ------------------
db_file_multiblock_read_count     3 128                  TRUE

# 取消格式化 
SQL> clear col
columns cleared

13.1.2 SQL Developer

13.1.3 使用 Database Express

EM :(Oracle Enterprise Manager Database Express)管理工具。

开启:

#开启监听
lsnrctl start 
lsnrctl status
SQL> select dbms_xdb_config.gethttpsport() from dual;
DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                             0
SQL>  select dbms_xdb_config.gethttpport() from dual;
SQL> exec dbms_xdb_config.setHTTPSport(8089);

#开启端口
SQL> exec dbms_xdb_config.setHttpport(8080);
PL/SQL procedure successfully completed.
-- exec DBMS_XDB.setHTTPPort(8080)

#关停端口,设置为0就关停了。
exec dbms_xdb_config.sethttpport(0);  

$netstat -lntp |grep 8080
tcp        0      0 :::8080                     :::*                        LISTEN      2230/tnslsnr  
$ lsnrctl status --可以看到8080端口已监听
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=5500))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db01)(PORT=8080))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
--访问em
http://localhost:8080/em  账号sys/oracle 以sysdba登录

使用em:

http://localhost:8080/em 账号sys/oracle 以sysdba登录

13.1.4 Enterprise Manager Cloud Control

EMCC(Enterprise Manager Cloud Control):常安装在独立的服务器。

13.2 理解初始化参数文件

13.2.1 静态和动态参数文件

• 静态参数文件 : pfile或叫初始文件init.ora。
• 动态参数文件: spfile ,二进制文件，不可手工编辑。

启动时依次查找如下文件： spfile.ora,spfile.ora。

-- 动态spfile<SID>.ora,spfile.ora
[oracle@db01 ~]$ ll $ORACLE_HOME/dbs/*.ora
-rw-r--r--. 1 oracle oinstall 3079 May 15  2015 /u01/app/oracle/product/12.2.0/db_1/dbs/init.ora
-rw-r-----. 1 oracle oinstall 3584 Feb 12 01:05 /u01/app/oracle/product/12.2.0/db_1/dbs/spfileorcl.ora
--静态init<SID>.ora
less /u01/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora

创建参数文件:

-- 建静态文件 
SQL> create pfile='/home/oracle/initorcl.ora'  from spfile;
SQL> create pfile from spfile;
File created.
--建动态文件
SQL> create spfile ='/home/oracle/spfile.ora' from pfile='/home/oracle/initorcl.ora';
$file spfile.ora
spfile.ora: data
--用string查看内容
$strings spfile.ora > /home/oracle/initorcl.ora

13.2.2 静态和动态参数及初始化参数文件

#查看
SQL> show parameter sga
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
allow_group_access_to_sga            boolean                FALSE
lock_sga                             boolean                FALSE
pre_page_sga                         boolean                TRUE
sga_max_size                         big integer            1536M
sga_min_size                         big integer            0
sga_target                           big integer            0
unified_audit_sga_queue_size         integer                1048576
SQL> 
SQL> spool /home/oracle/parame.txt
SQL> show prarameters      
SQL> spool off

#基本参数
select name,value from v$parameter where isbasic='TRUE'  order by name;
select name,value from v$parameter order by name;
select name,value from v$spparameter order by name;

可以用EM来修改参数。

13.2.3 基本参数

select name,value from v$parameter where isbasic='TRUE'  order by name;
SQL> Col NAME for a30 
SQL> Col value for a50 
SQL> select s.name,s.value from v$spparameter s join v$parameter p on s.name=p.name where p.isbasic='TRUE' order by name;

NAME                                                         VALUE
----------------------------- ------------------------------------------------------------
cluster_database
compatible                                                   12.2.0
control_files                                                /u01/app/oracle/oradata/orcl/control01.ctl
control_files                                                /u01/app/oracle/oradata/orcl/control02.ctl
db_block_size                                                8192

13.2.4 更改参数

alter system set name = value scope = memory | spfile | both;

#alter system set name = value scope = memory | spfile | both;
alter session set name = value ;

SQL>  select p.name,p.value in_effect,s.value in_file from v$spparameter s join v$parameter p on s.name=p.name 
  2   where p.name='db_file_multiblock_read_count';
NAME                           IN_EFFECT                      IN_FILE
------------------------------ ------------------------------ ------------------------------
db_file_multiblock_read_count  128

SQL> alter system set db_file_multiblock_read_count = 32 scope=memory;
SQL> alter system set db_file_multiblock_read_count = 64 scope=spfile;

SQL>  select p.name,p.value in_effect,s.value in_file from v$spparameter s join v$parameter p on s.name=p.name 
  2   where p.name='db_file_multiblock_read_count';
NAME                           IN_EFFECT                      IN_FILE
------------------------------ ------------------------------ ------------------------------
db_file_multiblock_read_count  32                             64
SQL> alter system set db_file_multiblock_read_count = 128;

-- 
show parameter optimizer_mode;
alter session set optimizer_mode= first_rows;

示例:

SQL> alter system set log_buffer=16M;
ORA-02095: specified initialization parameter cannot be modified
SQL>  alter system set log_buffer=16M scope=spfile;
System altered.
SQL>select name,value,isdefault from  v$parameter where isbasic='TRUE' order by name;
--session
alter session set nls_language='SIMPLIFIED CHINESE';
select name,value,isdefault from  v$parameter where name = 'nls_language';

13.3 启动和关闭Oracle实例

13.3.1 启动侦听器

lsnrctl start
lsnrctl status
[oracle@db01 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-FEB-2020 13:53:17
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.103)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                11-FEB-2020 22:56:59
Uptime                    0 days 14 hr. 56 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db01)(PORT=8080))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

13.3.2 启动和关闭数据库

1.连接数据库

SQL> show user
USER is "SYS"
SQL> conn  / as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
SQL> conn sys/oracle@orcl as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select count(*) from all_tables;
  COUNT(*)
----------
      2165

SQL> conn hr/hr
SQL> select count(*) from all_tables;

  COUNT(*)
----------
       140

2.启动

数据库状态:

• SHUTDOWN : 关闭和数据库相关所有文件，实例不存在。
• NOMOUNT : 有实例，但没有控制文件。
• MOUNT: 读控制文件，数据库文件不可用。
• OPEN: 数据库文件打开，可以正常使用oracle。

语法：startup [nomount | mount | open | force] startup命令其实是包括了startup nomount;alter database mount;alter datbase open; 3个操作。

#spfile参数错误,无法oracle启动处理
$strings spfile.ora > /home/oracle/initorcl.ora
vim /home/oracle/initorcl.ora
SQL>startup nomount pfile='/home/oracle/initorcl.ora';
SQL>create spfile from memory; --下次就可以正常了。
SQL>create spfile ='/home/oracle/spfile.ora' from pfile='/home/oracle/initorcl.ora';
SQL>shutdown 
--SQL>startup 
SQL> startup nomount  ;
SQL> select name from v$datafile; --出错
SQL> alter database mount; 

SQL> select name from v$datafile;
NAME
----------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> select * from dual;

ADDR                   INDX    INST_ID     CON_ID DU
---------------- ---------- ---------- ---------- --
00000000125F8C9C          0          1          0 X

SQL> alter database open;
Database altered.

SQL> select * from dual;
DU
--
X

3.关闭

语法： shutdown [normal | transactional | immediate | abort ]

• normal :
• transactional:
• immediate
• abort

关闭数据库:

alter system checkpoint;
alter system archive log current;
shutdown normal; 
shutdown immediate;
-- 也可以如下手动操作但无太价值，不如shutdown命令.
alter database close;
alter database dismount;

13.4 查看警报日志，访问动态性能视图

13.4.1 警报日志

• 警报日志是应用于实例和数据库的关键操作的连续记录。
• 位置: diagnostic_dest/diag/rdbms/dbname/sid/alert

SQL> select name,value from  v$parameter where name in ('diagnostic_dest','db_name','instance_name');
NAME                           VALUE
------------------------------ --------------------------------------------------
instance_name                  orcl
db_name                        orcl
diagnostic_dest                /u01/app/oracle

-- 位置
ls -l /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
ll /u01/app/oracle/diag/rdbms/orcl/orcl/alert/log.xml 

tail  /u01/app/oracle/diag/rdbms/orcl/orcl/alert/log.xml  
<msg time='2020-02-12T13:42:23.013+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='db01'
 host_addr='192.168.56.103' module='sqlplus@db01 (TNS V1-V3)' pid='9878'>
 <txt>ALTER SYSTEM SET log_buffer=16M SCOPE=SPFILE;
 </txt>
</msg>

13.4.2 DDL日志

• DDL记录DDL命令，需要DBA开启ENABLE_DDL_LOGGING。
• 位置： $diagnostic_dest/diag/rdbms/dbname/sid/log

ls -l   /u01/app/oracle/diag/rdbms/orcl/orcl/log/ddl/   

13.4.3 动态性能视图

动态性能视图用v$前缀。事实上他们不是视图，是视图的同义词SYNONYM。

SQL> select owner,object_name,object_type from dba_objects where object_name like 'V%INSTANCE';
OWNER                          OBJECT_NAME                              OBJECT_TYPE
------------------------------ ---------------------------------------- --------------------
SYS                            V_$INSTANCE                              VIEW
SYS                            V_$TEMPFILE_INFO_INSTANCE                VIEW
PUBLIC                         V$INSTANCE                               SYNONYM
PUBLIC                         V$TEMPFILE_INFO_INSTANCE                 SYNONYM

SQL> desc v$instance;
...

练习:

SQL> select name,value from  v$parameter where name in ('diagnostic_dest','db_name','instance_name');
--从视图或表中查询数据库信息
SQL>select t.name,d.name,d.bytes from v$tablespace t join v$datafile d on t.ts#=d.ts# order by t.name;
select t.tablespace_name,d.file_name,d.bytes from dba_tablespaces t join dba_data_files d 
on t.tablespace_name = d.tablespace_name order by tablespace_name;
--
SQL> select name,value from  v$parameter where name ='control_files';
NAME                           VALUE
------------------------------ --------------------------------------------------
control_files                  /u01/app/oracle/oradata/orcl/control01.ctl, /u01/a
                               pp/oracle/oradata/orcl/control02.ctl
SQL> select * from v$controlfile;
STATUS         NAME                           IS_REC BLOCK_SIZE FILE_SIZE_BLKS     CON_ID
-------------- ------------------------------ ------ ---------- -------------- ----------
               /u01/app/oracle/oradata/orcl/c NO          16384            646          0
               ontrol01.ctl

               /u01/app/oracle/oradata/orcl/c NO          16384            646          0
               ontrol02.ctl

13.5 总结

#