数据库基础运维笔记

renle0216

已于 2023-02-01 16:45:32 修改

阅读量1.1k

点赞数

文章标签：数据库运维 oracle

于 2022-09-16 14:57:27 首次发布

本文链接：https://blog.csdn.net/u012132164/article/details/126890620

版权

数据库基础

1. 环境配置

1. 配置方向键、删除键

# 安装readline-devel或epel-release
yum install -y epel-release

# 安装rlwrap
yum install rlwrap

# 修改配置文件
vim /home/oracle/.bash_profile

# 添加
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
stty erase ^H

或 执行stty erase ^H

# 使改动生效
source ./.bash_profile


# 设置行容量
SQL> set linesize 500

# 设置页容量
SQL> set pagesize 50

2. 理论

1. 进程

#查看进程
ps -ef|grep ora_

数据写进程（dbwr）：负责将更改的数据从数据库缓冲区高速缓存写入数据文件 
日志写进程（lgwr）：将重做日志缓冲区中的更改写入在线重做日志文件 
系统监控（smon） ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复 
进程监控（pmon） ：负责在一个Oracle 进程失败时清理资源 
检查点进程（ckpt）：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时，更新控制文件和数据文件中的数据库状态信息。 
归档进程（arcn） ：在每次日志切换时把已满的日志组进行备份或归档 
作业调度器（cjq） ：负责将调度与执行系统中已定义好的job，完成一些预定义的工作. 
恢复进程（reco） ：保证分布式事务的一致性，在分布式事务中，要么同时commit，要么同时rollback;

2. sql分类

# DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。
        SELECT：用于检索数据；
        INSERT：用于增加数据到数据库；
        UPDATE：用于从数据库中修改现存的数据 
        DELETE：用于从数据库中删除数据。

# DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。
        DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令：
        CREATE TABLE：创建表
        ALTER TABLE
        DROP TABLE：删除表
        CREATE INDEX
        DROP INDEX

# DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。
        ALTER
        GRANT 
        REVOKE 
        CREATE

3. SGA与PGA

# Memory_Target =SGA_TARGET+PGA_AGGREGATE_TARGET ，大小和memory_max_size一致
SQL> alter system set memory_target=4096M scope=spfile;

1. SGA
# SGA是一组共享内存结构，被所有的服务和后台进程所共享。当数据库实例启动时，系统全局区内存被自动分配。当数据库实例关闭时，SGA内存被回收。SGA是占用内存最大的一个区域，同时也是影响性能的重要因素。

# 查询SGA区的情况
SQL> show SGA
SQL> select * from v$sga;

# 参数调优
（1）修改SGA大小
SQL> alter system set sga_target=4096M scope=spfile;


（2）共享池 Shared Pool：
# 查看共享池大小Sql代码：
SQL> show parameter shared_pool_size;

# 修改共享池大小的Sql代码：
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;


（3）缓冲区高速缓存 Database Buffer Cache：
# 查看其大小的Sql代码：
SQL> show parameter db_cache_size;

# 修改缓冲区高速缓存大小
SQL> alter system set db_cache_size=4096M scope=spfile;


（4）日志缓冲区
# 查看日志缓冲区使用情况的Sql代码：
SQL> SELECT name, value  FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');

 
（5）大型池
# 指定Large Pool的大小：
SQL> ALTER SYSTEM SET LARGE_POOL_SIZE=64M;



2. PGA
# PGA包含单个服务器进程或单个后台进程的数据和控制信息，PGA 是为每个连接到Oracle database的用户进程保留的内存，PGA 在创建进程时分配,在终止进程时回收。

（1）PGA_AGGREGATE_TARGET初始化设置

SQL> alter system set workarea_size_policy=auto scope=both;  

SQL> alter system set pga_aggregate_target=512m scope=both;  

SQL> show parameter workarea  

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

workarea_size_policy                 string      AUTO --这个设置成AUTO

SQL> show parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 536870912

3. 表操作（sql）

1. 基础操作

# 创建表
create table student("id" char(6),"name" varchar2(10),"sex" char(1),"age" integer,"phone" varchar2(12)) tablespace test_db;

# 创建一个表，指定passwd为不可见字段
SQL>create table invisible_t(id int,name varchar2(20),passwd varchar2(20) invisible);
# 向表中插入数据
SQL>insert into invisible_t values(1,'andy',1);
ERROR at line 1:
ORA-00913: too many values
SQL> insert into invisible_t(id,name,passwd) values(1,'andy',1);

# 查看表
# 在Oracle中查看所有的表:
select * from tab/dba_tables/dba_objects/cat;

# 看用户建立的表 :
select table_name from user_tables;  //当前用户的表

select table_name from all_tables;  //所有用户的表

select table_name from dba_tables;  //包括系统表

select * from user_indexes //可以查询出所有的用户表索引


# 删除表
drop table tabname;

# 重命名表
alter table tablename rename to newtablename;

# 增加字段
alter table tablename add (ID varchar2(30) default '空' not null);


# 修改字段
alter table tablename modify (ID number(4));

# 重名字段
alter table tablename rename column ID to newID;

# 删除字段
alter table tablename drop column ID;

# 添加主键
alter table tabname add primary key(col)

# 删除主键
alter table tabname drop primary key(col)

# 创建索引
create [unique] index idxname on tabname(col….)

# 创建位图索引
create bitmap index ssex_bitmap_index on zhou.student(ssex);

# 删除索引
注：索引是不可更改的，想更改必须删除重新建。 
drop index idxname

# 创建视图
create view viewname as select statement

# 删除视图
drop view viewname

2. 分区表

分区类型

# 使用id进行分区
create table p_test(id number(7), type varchar(1), op_time date) 
PARTITION BY RANGE(id) 
(partition t_operate_log_1 values less than (100), 
partition t_operate_log_2 values less than (200), 
partition t_operate_log_3 values less than (300));

# 添加分区
ALTER TABLE p_test ADD PARTITION p4 VALUES LESS THAN(400); 

# 删除分区
ALTER TABLE p_test DROP PARTITION p4; 

# 插入数据
SQL> insert into p_test values(100, '1');

1 row created.

# 插入的数据无法匹配分区范围时
SQL> insert into p_test values(200, '1');
insert into p_test values(200, '1')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


# 查询数据
SQL> select * from p_test;

	ID T
---------- -
	 1 1
       100 1

SQL> select * from p_test partition(p1);

	ID T
---------- -
	 1 1

SQL> select * from p_test partition(p2);

	ID T
---------- -
       100 1

# 基于时间分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(op_time) 
( 
 partition t_operate_log_1 values less than (to_date('2019-01-17','yyyy-MM-dd')), 
 partition t_operate_log_2 values less than (to_date('2020-01-17','yyyy-MM-dd')), 
 partition t_operate_log_3 values less than (maxvalue) 
)


# 基于列表分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add values('add'), 
 partition t_operate_log_delete values('delete'), 
 partition t_operate_log_edit values('edit'), 
 partition t_operate_log_query values('query') 
) 

# 给列表分区增加元素
ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query ADD VALUES('select'); 
1.
# 给列表分区删除元素
ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query DROP VALUES('select'); 


# HASH分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY hash(id) 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3, 
 partition t_operate_log_4 
)

给现有表创建分区

1.创建表
create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 

2.创建一个分区表，只有1个分区
create table t_operate_log_p 
 
( 
 
id number(7), 
 
type varchar(1), 
 
op_time date 
 
) 
 
PARTITION BY RANGE(id) 
 
( 
 
partition t_operate_log_1 values less than (101) 
 
) 

3.把原表数据抽取到分区表
ALTER TABLE t_operate_log_p 
  EXCHANGE PARTITION t_operate_log_1 
  WITH TABLE t_operate_log 
  WITHOUT VALIDATION; 

4.删除原有表，把新的表改名为原来的表
DROP TABLE t_operate_log; 
RENAME t_operate_log_p TO t_operate_log; 

5.把新表拆分成多个分区表
ALTER TABLE t_operate_log SPLIT PARTITION t_operate_log_1 AT (id) 
INTO (PARTITION t_operate_log_2, 
      PARTITION t_operate_log_3);

3. 约束

# 查看约束信息
SELECT * FROM DBA_CONSTRAINTS;

SELECT * FROM ALL_CONSTRAINTS;

SELECT * FROM USER_CONSTRAINTS;


SELECT * FROM DBA_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;

SELECT * FROM USER_CONS_COLUMNS;



# 示例
一、非空约束（只能在列级设置，不能在表级设置）
1、在创建表时设置非空约束
CREATE TABLE table_name(column_name datatype NOT NULL);

2、在修改表时添加非空约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
 
3、在修改表时去除非空约束
ALTER TABLE table_name MODIFY column_name datatype NULL;
 

二、主键约束
1、在创建表时设置主键约束
在列级设置主键约束

CREATE TABLE table_name(column_name datatype PRIMARY KEY);
 

在表级设置主键约束

CONSTRAINT constraint_name PRIMARY KEY(column_name1)
 

使用desc user_constraints可以查看约束的名字等信息

2、在修改表时添加主键约束
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1);
 

3、更改约束的名称
RENAME CONSTRAINT old_name TO new_name;
 

4、删除主键约束
禁用主键约束：

DISABLE|ENABLE CONSTRAINT constraint_name;
 

彻底删除主键约束：

DROP CONSTRAINT constraint_name;
 

DROP PRIMARY KEY[CASCADE]
 

三、外键约束（涉及两个表之间的关系）
1、在创建表时设置外键约束
设置外键约束时，主表的字段必须是主键
主从表中相应的字段必须是同一个数据类型
从表中外键字段的值必须来自主表中的相应字段的值，或者为null值
CREATE TABLE table1
(column_name datatype REFERENCES
table2(column_name),……);
table1位从表，table2位主表

 

在表级设置外键约束
CONSTRAINT constraint_name FOREIGN
KEY(column_name)REFERENCES
table_name(column_name)[ON DELETE CASCADE];
 

2、在修改表时添加外键约束
ADD CONSTRAINT constraint_name FOREIGN
KEY(column_name) REFERENCES 
table_name(column_name)[ON DELETE CASCADE];
 

3、删除外键约束
禁用外键约束：
DISABLE|ENABLE CONSTRAINT constraint_name;
 
彻底删除外键约束：

DROP CONSTRAINT constraint_name;
 

四、唯一约束
唯一约束和主键约束的区别：

主键字段值必须是非空的，唯一约束允许有一个空值
主键在每张表中只能有一个，唯一约束在每张表中可以有多个
唯一约束要求每个字段不能有重复值，可以有空值，但是空值只能有一个
1、在创建表时设置唯一约束
在列级设置唯一约束

CREATE TABLE table_name
(column_name datatype QUNIQUE,……);
 

在表级设置唯一约束

CONSTRAINT constraint_name UNIQUE(column_name);
 

2、在修改表时添加唯一约束
ADD CONSTRAINT constraint_name
UNIQUE(column_name);
 

3、删除唯一约束 
DISABLE|ENABLE CONSTRAINT constraint_name;
禁用唯一约束

彻底删除唯一约束

DROP CONSTRAINT constraint_name;
 

五、检查约束
1、在创建表时设置检查约束
在列级设置检查约束：

CREATE TABLE table_name(
column_name datatype CHECK(expressions),……);
 

表级设置检查约束：

CONSTRAINT constraint_name CHECK(expressions);
 

2、在修改表时添加检查约束
ADD CONSTRAINT constraint_name
CHECK(expressions);
 

3、删除检查约束
禁用检查约束：

DISABLE|ENABLE CONSTRAINT constraint_name;
 

彻底删除检查约束：

DROP CONSTRAINT constraint_name;

4. 序列

# 创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下：

　　CREATE SEQUENCE 序列名

　　[INCREMENT BY n]  # INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

　　[START WITH n]  # START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]  # MAXVALUE/MINVALUE定义序列生成器能产生的最大/最小值。

　　[{CYCLE|NOCYCLE}]   # CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

　　[{CACHE n| NOCACHE}]; # CACHE(缓冲)定义存放序列的内存块的大小，默认为20。改善性能


# 创建，指定初始和步长
SQL> create sequence t1_seq increment by 1 start with 1;

SYS@orclasm > create sequence t1_seq increment by -1 start with  -5;



# 获取序列当前与下一个值，第一次使用nextval为当前值
SYS@orclasm > select t1_seq.currval,t1_seq.nextval from dual;


   CURRVAL    NEXTVAL
---------- ----------
        -5         -5
        
# 将序列与表的字段结合
SQL> create table t1(id number,qq number,ww number);

SQL> insert into t1 values(t1_seq.nextval,1,1);

# 修改序列
SQL> alter sequence t1_seq increment by 2 maxvalue 40 cycle;

# 查询序列
SQL> select sequence_name,increment_by,cycle_flag,cache_size from user_sequences;

3. 表数据操作（sql）

# 数据查询
SQL> select tablespace_name,SEGMENT_NAME,EXTENTS,BLOCKS,BYTES from user_segments where SEGMENT_NAME IN ('TEST1','INX_V1','V1');

# 插入数据
insert into 表名 values(所有列的值);
insert into test values(1,'zhangsan',20);

insert into 表名(列) values(对应的值);
insert into test(id,name) values(2,'lisi');

# 更新数据
update test set name='zhangsan2' where name='zhangsan'

update test set age =20; # 更新该列所有数据

# 删除数据
delete from test where id = 1; 
delete from test -- 删除所有 
commit; -- 提交数据
rollback; -- 回滚数据

# 清空表数据
truncate table 表名

# 表数据复制
insert into test2 (select * from dba_objects);

# 复制表结构
create table test2 as select * from dba_objects where 1>1;

# 复制表结构和数据
create table table1 as select * from table2;

# 复制指定字段
create table table1 as select id, name from table2 where 1>1;

数据库管理

1. 基础

1. 启动、关闭、登录

# 数据库的正常启动步骤
# 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户
[root@kevin ~]# su - oracle 

# 以DBA的身份登录数据库(oracle用户下执行)
[oracle@kevin ~]$ sqlplus / as sysdba

# 执行启动数据库命令
SQL> startup
# 分步启动
SQL> startup nomount
SQL> alter database mount
SQL> alter database open


# 数据库的正常关闭步骤 
# 执行数据库关闭命令
SQL> shutdown immediate;

# 几种关闭数据库方法对比
shutdown有四个参数：normal、transactional、immediate、abort。缺省不带任何参数时表示是normal。
shutdown normal：   不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复，这种方法往往不能关闭数据库或等待很长时间。
shutdown transactional：  不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
shutdown immediate：  不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。最常用的方法。
shutdown abort：  不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。一般不推荐采用，只有在数据库无法关闭时使用，可能造成数据库的不一致。

2. 用户管理

1. 基础管理

# 查看当前用户：
SQL> show user

# 查看当前用户的详细信息
SQL> SELECT * FROM user_users;

# 查看当前用户拥有的角色权限信息
SQL> select * from role_sys_privs;

# 查看当前用户的角色信息
SQL> select * from user_role_privs;

# 查看数据库用户
SQL> select * from dba_users;  

# 创建用户
SQL> CREATE USER test IDENTIFIED BY test123 DEFAULT TABLESPACE test TEMPORARY TABLESPACE test_temp QUOTA 10M ON test;

# 修改用户表空间
SQL> Alter user test default tablespace tbs_test1;              
SQL> Alter user test temporary tablespace temp_tbs_test1;  

# 修改配额空间，否则无法插入数据
SQL> alter user test QUOTA 4M ON test;

# 删除用户
SQL> drop user test cascade;

2. 密码管理

Password_parameter部分：
       Failed_login_attempts：指定在帐户被锁定之前所允许尝试登陆的的最大次数。
       Password_life_time：指定同一密码所允许使用的天数。如果同时指定了password_grace_time参数，如果在grace period内没有改变密码，则密码会失效，连接数据库被拒绝。如果没有设置password_grace_time参数，默认值unlimited将引发一个数据库警告，但是允许用户继续连接。
       Password_reuse_time和password_reuse_max：这两个参数必须互相关联设置，password_reuse_time指定了密码不能重用前的天数，而password_reuse_max则指定了当前密码被重用之前密码改变的次数。两个参数都必须被设置为整数。
       1．如果为这两个参数指定了整数，则用户不能重用密码直到密码被改变了password_reuse_max指定的次数以后在password_reuse_time指定的时间内。
       如：password_reuse_time=30，password_reuse_max=10，用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
       2．如果指定了其中的一个为整数，而另一个为unlimited，则用户永远不能重用一个密码。
       3．如果指定了其中的一个为default，Oracle数据库使用定义在profile中的默认值，默认情况下，所有的参数在profile中都被设置为unlimited，如果没有改变profile默认值，数据库对该值总是默认为unlimited。
       4．如果两个参数都设置为unlimited，则数据库忽略他们。
       Password_lock_time：指定登陆尝试失败次数到达后帐户的锁定时间，以天为单位。
       Password_grace_time：指定宽限天数，数据库发出警告到登陆失效前的天数。如果数据库密码在这中间没有被修改，则过期会失效。
       Password_verify_function：该字段允许将复杂的PL/SQL密码验证脚本做为参数传递到create profile语句。Oracle数据库提供了一个默认的脚本，但是自己可以创建自己的验证规则或使用第三方软件验证。 对Function名称，指定的是密码验证规则的名称，指定为Null则意味着不使用密码验证功能。如果为密码参数指定表达式，则该表达式可以是任意格式，除了数据库标量子查询。
       
# 查看指定概要文件（如default）的密码有效期设置：
sql> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
 
# 将密码有效期由默认的180天修改成“无限制/天数”：
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED/天数; 

# 设置最大失败次数
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 30;

# 设置无限失败次数
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

# 设置登录失败超过限制次数锁定的天数
SQL> alter profile default limit  password_lock_time 3;

# 锁定用户
alter user test account lock;

# 用户解锁
SQL> alter user test account unlock;

# 修改当前登录用户密码：
SQL> password

# 修改某个用户的密码：
SQL> alter user [username] identified by [password];



# 查看用户的proifle是那个，一般是default： 
sql> SELECT PROFILE FROM dba_users WHERE USERNAME='test'; 


# 创建用户profile角色文件
 CREATE PROFILE my_profile LIMIT
    SESSIONS_PER_USER          UNLIMITED  #对用户的并发连接会话数不做限制
    CPU_PER_SESSION            UNLIMITED  #对于连接到用户的每一个session的CPU时间的使用不做限制
    CPU_PER_CALL               6000       #一次调用消耗的CPU时间不能超过60秒（不超过一分钟）
    CONNECT_TIME               60         #连接到用户的每次会话时间不能超过60分钟（不超过一个小时）
    LOGICAL_READS_PER_SESSION  DEFAULT    #一次会话使用的物理读与逻辑读数据块总量与DEFAULT profile中定义保持一致
    LOGICAL_READS_PER_CALL     60000      #一次调用使用的物理读与逻辑读数据块总量不超过60000个数据块
    COMPOSITE_LIMIT            6000000    #一次会话总的资源消耗不超过6000000个服务单元（service units）
    FAILED_LOGIN_ATTEMPTS      10         #帐户被锁定之前允许10次的错误尝试
    PASSWORD_LIFE_TIME         UNLIMITED  #密码不过期
    PASSWORD_REUSE_TIME        UNLIMITED  #密码重置时间不限制
    PASSWORD_LOCK_TIME         1/24       #超过错误尝试次数后，用户将被锁定1小时
    PASSWORD_GRACE_TIME        10         #当密码过期之后原密码还可以使用10天
    PASSWORD_VERIFY_FUNCTION   verify_function #使用密码复杂度校验函数verify_function对密码做检查


# 查看创建的profile
SQL> select * from dba_profiles;

# 将profile文件分配给用户
SQL> alter user scott profile my_profile;
SQL> select USERNAME,PROFILE from dba_users where USERNAME = 'scott';
SQL> alter user scott profile default;
SQL> select USERNAME,PROFILE from dba_users where USERNAME = 'scott';

3. 权限、角色管理

# 赋予/收回权限
SQL> grant CONNECT,CREATE TABLE,CREATE ANY VIEW  TO test;

SQL> revoke resource from test;

# 查看当前用户权限
SQL> SELECT * FROM role_sys_privs;

3. 实例管理

1. 控制文件多路径

# 查询现有控制文件
# 未开机，查看spfile文件
strings $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
# 已经开机
SELECT name FROM v$controlfile;  

# 修改参数
ALTER SYSTEM SET control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl' scope=spfile;

# 干净关机 
SQL> shutdown immediate;

# 复制控制文件至新添加的路径
cp -a /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl
# 正常开机
startup

2. 参数管理

# 查看内存中参数
select name ,value from v$parameter where name= 'processes';
show parameter processes 

# 查看参数文件里面的值
select name,value from v$spparameter where name='processes';


# 动态参数：修改后时时生效
alter system set resource_limit =true;

#静态参数：只记录在参数文件中下次重启生效
alter system set processes=300 scope=spfile;

SCOPE = SPFILE  修改静态参数，重启后生效
SCOPE = MEMORY  修改动态参数，立即生效。
SCOPE = BOTH  修改动态参数，立即生效，更改将应用于服务器参数文件和内存中（默认）

# session级别修改参数(只影响当前会话，不影响系统)：
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

# pfile/spfile格式转换：
create pfile='/home/oracle/initorcl.ora' from spfile;
create spfile from pfile='/home/oracle/initorcl.ora';

# 指定参数文件启动数据库：
SQL> startup pfile='/home/oracle/initorcl.ora'
# 还原修改 
SQL>ALTER SYSTEM RESET ... SCOPE=SPFILE

3. 重要参数

# UTL_FILE_DIR参数定义
UTL_FILE_DIR是Oracle中的一个“静态参数”，可以设置一个或多个路径。用于在PL/SQL中进行文件I/O操作（可以用utl_file包）时指定路径。UTL_FILE_DIR是Oracle中的一个“静态参数”，可以设置一个或多个路径。用于在PL/SQL中进行文件I/O操作（可以用utl_file包）时限定路径，utl_file包只能在指定路径下创建，读取文件。utl_file_dir为空时，则不限定路径。

alter system set utl_file_dir='/u01/app/oracle','/oracle/oradata/orcl' scope=spfile;

# 设置UNDO查询时间
alter system set utl_file_dir='/u01/app/oracle','/oracle/oradata/orcl' scope=spfile;

# 配置严重错误事件日志信息存储到ORACLE_HOME 目录
alter system set diagnostic_dest='/u01/app/oracle/product/19.3.0/dbhome_1';

# 开启ddl日志记录功能
Alter system|session set enable_ddl_logging=ture

# 限制pga大小
alter system set pga_aggregate_limit=3g;

4. 收集统计信息

1. 支持同时在多个表上收集统计信息
　　在之前的版本中，当执行DBMS_STATS收集统计信息时，Oracle习惯于一次一个表进行收集统计数据，如果表很大，那么推荐采用并行方式。在12c R1中，oracle可以同时在多个表、分区以及子分区上收集。

-- 使用之前必须对数据库进行以下设置以开启此功能：
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DEFAULT_MAIN';
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;
-- 开始收集
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');

# 收集用户统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

数据库逻辑结构

1. 表空间tablespace

1. 理论

表空间分类	名称	描述
EXAMPLE	示例表空间	示例表空间，存放示例数据库的方案对象信息及其培训资料
SYSAUX	辅助系统表空间	辅助系统表空间，用于减少system表空间的负荷，提高系统的工作效率。是oracle 10g新增加的表空间
SYSTEM	系统表空间	系统表空间，存放关于表空间名称、控制文件、数据文件等管理信息，存放着方案对象（如表，索引，同义词，序列）的定义信息，存放着所有pl/sql 程序（如过程，函数，包，触发器）的源代码，是oracle数据库中最重要的表空间。它属于SYS和SYSTEM方案，仅被SYS和SYSTEM或其他具有足够权限的用户使用。即使是SYS 和SYSTEM用户也不能删除或重命名该空间。他是用户的默认表空间，即当用户在创建一个对象时，如果没有指定特定的表空间，该对象的数据也会被保存在SYSTEM表空间中。
TEMP	临时表空间	存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。
UNDO	UNDO表空间	保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

# 查看system用户的默认表空间和临时表空间：
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';


# 默认表空间
用户在登陆后创建数据库对象时，如果没有指定表空间，那么这些数据就会存储到默认表空间。

# 查看默认的永久表空间
注意：如果创建用户时，不指定其永久表空间，则会使用默认的表空间。
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES;

# 设置默认表空间
针对某个用户的:
SQL> alter user user_name default tablespace tbs_name;

设置数据库的默认临时表空间:
SQL> alter database default tablespace temp_tbs_name;

# 查看默认的TEMP表空间
系统管理员：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

# 普通用户
SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

# 设置默认临时表空间
针对系统
SQL> alter database default temporary tablespace temp_grp;
针对用户
SQL> Alter user test temporary tablespace temp1;

# 查看UNDO表空间
SQL> show parameter undo;

2. 表空间/临时表空间操作

# 预先设置文件存储位置
SQL> alter system set DB_CREATE_FILE_DEST='/oracle/oradata' scope=both;

# 完整的创建表空间语句
create tablespace mytablespace datafile '/home/oracle/oradata/mytablespace.dbf' size 200m autoextend on next 512k maxsize unlimited

logging

online

extent management local autoallocate

blocksize 8k

segment space management manual

flashback on;

//创建一个小表空间，表空间名称为mytablespace，表空间的数据文件名为/home/oracle/oradata/mytablespace.dbf，表空间文件的初始大小是200m，文件自动扩展，文件块的大小是8k，表空间文件最大是32g。

// logging会创建重做日志，方便在数据丢失后找回数据

//online 表空间的状态设置成online，允许外部对表空间的对象进行访问；如果设置 成offline，则不允许外部对表空间的对象进行访问。

// extent是“区间”的意思,在oracle数据库中：extent management 有两种方式 extent management local（本地管理）; extent management dictionary（数据字典管理），默认的是local，本地管理表空间与字典管理表空间相比大大提高了管理效率和数据库性能。
// segment space management manual  手动管理，oracle使用自由列表（free list）管理段中的块，自由列表列出允许进行插入操作的数据块;自动管理，段的手工管理使用自由列表管理段中数据块的使用，自动管理使用位图来管理数据快，当块发生变化时，oracle会更新位图，以反映这个块是否允许insert操作，位图使oracle自动管理自由空间。 

// flashback on将以前被修改,删除了的数据,通过开启flashback 模式,回到我们需要回到的时间来查看数据,



# 创建表空间(多个数据文件)
CREATE TABLESPACE test_db  DATAFILE '/oracle/oradata/orcl/test_db1.dbf' SIZE 10m  AUTOEXTEND ON NEXT 10M   MAXSIZE UNLIMITED, '/oracle/oradata/orcl/test_db2.dbf' size 10M autoextend on next 100M maxsize 100m;


# 创建表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  maxsize unlimited  extent management local uniform size 2m segment space management manual;
段空间的手工管理（Manual Segment Space Management）

# 创建表空间（指定数据块大小），DB_nK_CACHE_SIZE (其中n = 2,4，8,16，32) 指定nK缓冲区的缓存大小。仅当DB_BLOCK_SIZE具有nK以外的值时，才可以设置此参数。例如，如果DB_BLOCK_SIZE = 4096，则指定参数DB_4K_CACHE_SIZE是非法的 (因为DB_CACHE_SIZE已经指定了4 KB块缓存的大小)。

SQL> show parameter  block  # 首先查看默认数据块大小

SQL> show parameter db_16k_cache_size # 查看16k块大小

SQL> alter system set db_16k_cache_size=100m; # 设置16k块

SQL> create tablespace ts16k datafile '/oracle/data/ts16k.dbf' size 5m blocksize 16k;

# 


# 创建表空间超大文件
CREATE bigfile TABLESPACE test_db  DATAFILE '/oracle/oradata/orcl/test_db1.dbf' SIZE 10m  AUTOEXTEND ON NEXT 10M   MAXSIZE 4T;

# 修改数据文件属性
SQL> alter database datafile '/oracle/oradata/orcl/test_db1.dbf' resize 20M;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on next 20M;

SQL> alter database datafile '/oracle/oradata/orcl/tbs1.dbf' autoextend on next 20M 
maxsize 20G;

# 查看数据文件及所属表空间
SQL> select file_name,tablespace_name from dba_data_files;

# 给表空间添加数据文件
SQL> alter tablespace test_db add datafile '/oracle/oradata/orcl/test_db-2.dbf' size 10M autoextend on next 20M maxsize 20G;

# 删除表空间数据文件
alter tablespace test_db drop datafile '/oracle/oradata/orcl/test_db2.dbf';

# 创建临时表空间
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/oracle/oradata/orcl/test_temp.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

# 创建临时表空间，临时空间属于某个组
create temporary tablespace temp1 tempfile '$ORACLE_BASE/oradata/PROD1/temp02.dbf' size 50m tablespace group temp_grp;

SQL>alter database default temporary tablespace tempgrp;

# 临时表空间添加数据文件
ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;

# 查看表空间信息
SELECT * FROM v$tablespace;

# 删除表空间/临时表空间
drop tablespace test_db including contents and datafiles;

# 查看表空间及数据文件：
SQL> SELECT FILE_NAME,TABLESPACE_NAME from DBA_DATA_FILES;

3. UNDO表空间

命令

（1）创建undo表空间
使用create undo tablespace来创建undo tablespace。undo tablespace用来保存事务的回退信息，用户不能在其中创建数据库对象。

# 查看UNDO表空间
show parameter undo;

# 创建UNDO表空间
create undo tablespace test_undo datafile '/oracle/oradata/orcl/test_undo.dbf' size 2m;
 

(2)修改undo表空间

可以使用alter tablespace修改undo表空间，允许对undo表空间进行如下操作：

  --添加undo表空间的数据文件；

  --重命名undo表空间的数据文件；

  --将undo表空间的数据文件联机或脱机；

  --启用或禁用保护回退信息在回退段中的保留时间；

# 为undo tablespace添加新的数据文件
alter tablespace undotbs1 add datafile '/home/app/oracle/oradata/orcl/untbs02.dbf' size 50M;

# 将undo tablespace里面的untbs02.dbf文件扩充为100M
alter database datafile '/home/app/oracle/oradata/orcl/untbs02.dbf' resize 100M;
 

（3）删除undo表空间

drop tablespace test_db including contents and datafiles;


（4）切换undo表空间，直接修改即可，无需重启实例
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_name

（5）参数 UNDO_RETENTION 设置撤销保留时间的大小
前提：
# 使用ALTER DATABASE命令保证数据库中的撤销保留。

SQL> alter tablespace UNDOTBS1 retention guarantee;

关闭撤销信息的保证保留

SQL> alter tablespace UNDOTBS1 retention noguarantee;

SQL> alter system set UNDO_RETENTION=1800       设置保留时间为30分钟（1800秒）。

UNDO_RETENTION参数默认设置为900秒。

2. 段segment

1. 理论

段类型	名称	描述
表	table	段名 = 表名
表分区	table partition	段名 = 表名 and 分区名不为空
表子分区	table subpartition	段名 = 表名 and 分区名不为空且不同
索引	index	段名 = 索引名
索引分区	index partition	段名 = 索引名 and 分区名不为空
索引子分区	index subpartition	段名 = 索引名 and 分区名不为空且不同
聚簇	cluster
lob 段	lobsegment	每个 lob 字段都会对应一个 lob 段，可分区
lob 分区	lob partition
lob 子分区	lob subpartition
lob 索引	lobindex
回滚段	rollback	8i及以前，名称 system
撤销段	type2 undo	9i及以后
嵌套表	nested table

2. 实操

# 查看表空间、段、区信息
SQL> select * from dba_tablespaces;    
SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;

# 查询段，权限由大至小
select t.segment_name, t.* from dba_segments t;
select t.segment_name, t.* from user_segments t;

# 查找表
select t.table_name, t.* from dba_tab_comments t;
select t.table_name, t.* from all_tab_comments t;
select t.table_name, t.* from user_tab_comments t;

# 查找 lob 字段
select * from dba_lobs;
select * from dba_lob_partitions;
select * from dba_lob_subpartitions;

# 创建表空间（所有参数，包括段、区参数）
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  MAXSIZE UNLIMITED  extent management local uniform size 2m segment space management manual;

3. 区extend

1. 理论

#  查询 区 extend
-- 段空间使用完后，自动为该段分配一个新的区（扩展）
select * from dba_extents;
select * from user_extents;

# 查看表空间、段、区信息
SQL> select * from dba_tablespaces;    
SQL> select segment_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments;

# 创建表空间（所有参数，包括段、区参数）
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m autoextend ON NEXT 10M  MAXSIZE UNLIMITED  extent management local uniform size 2m segment space management manual;

4. 数据块block

1. 理论

1. 数据块 和 操作系统块
   (1) 数据块   : '最小的逻辑单元'
   (2) 操作系统块: 读取或写入磁盘的最小单元（'I/O'）
   (3) 数据快 和 操作系统快分离的意义：'屏蔽了不同操作系统的差异'
       -- 也就是说，数据库在 Windows 或 Linux 等一样使用
   
2. 数据块的大小
   (1) 数据库创建时设置，之后无法更改，除非重新创建数据库。
   (2) 一般默认大小 '8kb'
       show parameters db_block_size -- (8192/1024 = 8 kb)

3. 行数据格式（Row Data）
   (1) 可通过伪列 rowid 查看，共计 18 位
	   select rowid, -- 18 位
		      -- 数据对象id（前 6 位）
			  dbms_rowid.rowid_object(rowid) object_id, 
		      -- 相对文件id（3 位）
		      dbms_rowid.rowid_relative_fno(rowid) relative_fno, 
		      -- 数据块id（6 位）
		      dbms_rowid.rowid_block_number(rowid) block_number, 
		      -- 行id（后 3 位）
		      dbms_rowid.rowid_row_number(rowid) row_number
	     from scott.emp;

数据库物理结构

1. 参数文件

1. 参数类型

查看参数最好是直接show parameter 参数，参数可以进行模糊匹配


# 作用：记录oracle参数信息，类似mysql的my.cnf,在启动阶段读取初始化参数文件（init parameter files)。该文件管理实例相关启动参数。

# 参数类型大致以下几种：

基本初始化参数	大约 10-20 个左右（见联机文档）
初始化参数	300 个左右
隐含参数	Oracle 不推荐使用
总的来说也就是普通参数和隐含参数

# 查看普通参数：

select * from v$parameter;


# 动态参数和静态参数，及文件

动态参数：可以直接在内存中修改，并对当前 instance 立即生效

静态参数：必须修改参数文件，下次启动后生效

# 区分是静态参数还是动态参数：根据 ISSYS_MODIFIABLE 的值判断

SQL>  select distinct issys_modifiable from v$parameter;

ISSYS_MOD
---------
IMMEDIATE		#动态参数
DEFERRED		#静态参数
FALSE			#延迟参数，下次session连接有效

#实例
SQL> select name,value,ISSYS_MODIFIABLE from v$parameter where name ='undo_retention';

NAME            VALUE            ISSYS_MODIFIABLE
--------------- -----------------------------------
undo_retention   900             IMMEDIATE

2. 参数文件

# 参数文件可以分为文本参数文件（pfile）和 二进制参数文件(spfile)

# 静态参数文件
记录Oracle参数文本文件（默认已经不使用），默认的名称为“init+实例名.ora”，这是一个文本文件，可以用任何文本编辑工具打开。
# 位置
$ORACLE_HOME/dbs/init.ora

# 动态参数文件：记录Oracle参数二进制文件（默认使用的参数文件）

spfile 默认的名称为“spfile+例程名.ora”这是一个二进制文本形式存在，不能用vi编辑器对其中参数进行修改。

#  查看当前数据库使用的参数文件
SQL> show parameter spfile

# 两种参数文件:pfile和spfile

1. pfile（parameter file）

特点：

①必须通过文本编辑器修改参数，便于一次修改多个参数。

②缺省的路径及命名方式：$ORACLE_HOME/dbs/initSID.ora

2. spfile（system parameter file）

特点：

①二进制文件，不可以通过编辑器修改。通过 Linux 命令 strings 可以查看内容。

②路径及命名方式: $ORACLE_HOME/dbs/spfileSID.ora

# 参数的修改

1. 修改spfile的方法：

alter system set 参数=值 [scope=memory|spfile|both]
①scope=memory 参数修改立刻生效，但不修改 spfile 文件。

②scope=spfile 修改了 spfile 文件，重启后生效。

③scope=both 前两种都要满足。要求 spfile 参数文件存在

④不写 scope 限定词，缺省both。但不如③严格，即 spfile 如果不存在，仅仅修改内存中参数。

# 示例
SQL> select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sql_trace';

NAME         ISSES_MODIFIABLE ISSYS_MODIFIABLE
------------ ---------------- ----------------
sql_trace    TRUE             IMMEDIATE

#这个结果表示 sql_trace 参数在 session 级别可以改，在 system 级也可以 both 修改（动态参数）。

alter system set sga_target=400M ; ---动态参数
alter system set memory_max_target=600M scope=spfile; --静态参数
alter system set sga_target=400M  scope=memory;    
ALTER SYSTEM SET parameter_name=parameter_value scope=[memory|spfile|both] 


# 参数文件的生成和相互转换

读取顺序：优先 spfile，其次 pfile

pfile 和 spfile 可以相互生成：

SQL>create pfile from spfile
SQL>create spfile from pfile
SQL>create pfile from memory;
SQL>create spfile from memory;


# 示例

#动态参数文件生成静态参数文件:
create pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initweisi.ora' from spfile;
#静态参数文件生动态态参数文件:
create spfile from pfile ='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/pfile.ora';


注意：使用 spfile 启动后不能重写 spfile

尽可能使用 spfile，pfile 一般留做备用，特殊情况也可以使用 pfile 启动:

SQL> startup pfile=$ORACLE_HOME/dbs/initprod.ora
---如果 pfile 不是缺省命名或放在其他路径，则指定命令路径和文件名即可。
SQL> startup pfile=/home/oracle/mypfile

---怎样知道实例是 spfile 启动还是 pfile 启动的
SQL> show parameter spfile
NAME     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile   string   /u01/oracle/dbs/spfile.ora
---如果 value 有值，说明数据库启动时读的是 spfile

---另一个办法是看 v$spparameter（spfile 参数视图）中的参数 memory_target 的 isspecified 字段值，如果是 TRUE 说明是 spfile 启动的
SQL> select name,value,isspecified from v$spparameter where name like 'memory_target';

NAME             VALUE         ISSPECIFIED
--------------- -----------------------------
memory_target    1528823808    TRUE

2. 控制文件

控制文件是数据库中最重要的文件，它是一个二进制文件，它记录了数据库名、实例信息、数据文件和日志文件的名字位置、建库日期等。一般由系统安装时自动创建。

实例启动时，先加载参数文件，然后分配sga内存，这时实例处于安装状态，然后访问控制文件，读出数据文件和日志文件信息，再打开数据文件和日志文件供用户访问。

# 存放路径
控制文件存放的路径由参数文件SPFILEsid.ora和crontrol参数值来确定。

# 查看控制文件信息可以在v$controlfile数据字典
col name for mat a60;
select name from v$controlfile;

3. 数据文件

数据文件是用与保存应用程序数据和系统内部数据的文件，在操作系统中是普通文件。oracle逻辑上由表空间组成，每个表空间可以包含一个或多个数据文件，一个数据文件只能属于一个表空间。

当修改数据时，也不会立即写入数据文件，而是先保存在内存的数据缓冲区，由检查点机制去触发后台进程DBWR去写入到相应数据文件。也就是脏数据落盘，是checkpoint激活时会触发数据库写进程(DBWR)，将数据缓冲区里的脏数据块写到数据文件中。通过这样的方式，减少磁盘IO，提供系统性能。

# 查看数据文件信息，或者在操作系统目录查看，可以通过dba_data_files或v$datafile数据字典
SQL> col file_name for a50
SQL> set linesize 100;
SQL> select file_name,tablespace_name from dba_data_files;

SQL> select FILE#,name from v$datafile; 
	
# 四种类型数据文件：

 系统数据文件（system01.dbf、sysaux01.dbf）

 回滚数据文件（undotbs01.dbf）

 用户数据文件（users01.dbf）

 临时数据文件（temp01.dbf）

系统数据用来管理用户数据和Oralce本身的数据，如用户建立表的名字、列的名字、字段类型等，这些数据被自动存放在系统表空间对应的system01.dbf数据文件中。oracle系统内部的数据字典、系统表等所存储的数据属于oracle系统内部数据，也存储在系统表空间对应的数据文件中。

回滚数据文件（撤销数据文件）用于临时存储修改前的旧数据

用户数据文件用于存储用户应用系统的数据，包括与应用系统相关的所有相关信息

临时数据文件用于存放和排序相关的数据，临时表数据文件和一般数据文件被Oracle做了区分，不能在dba_data_files中查询到。

# 临时数据文件查看，可以通过dba_temp_files或者v$tempfile数据字典查询
SQL> col file_name format a50
SQL> col tablespace_name format a20
SQL> select file_name,tablespace_name from dba_temp_files;

SQL> select name from v$tempfile;

4. 日志文件

日志文件主要功能是记录对数据所做的修改，出现问题时可以通过日志文件得到原始数据，从而不丢失操作成果。

日志文件主要分为两种：

重做日志文件（redo log file）

归档日志文件（archive log file）

对应着非归档模式和归档模式：

非归档模式：系统运行期间，所产生的日志信息不断记录到日志文件组中，所有重做日志组被写满后，又从第一个日志组开始覆写；

归档模式：各个日志文件都被写满而即将覆盖前，先由归档进程（ARCH）将即将覆盖的日志文件中的日志信息读出，然后写入归档日志文件中，这个过程被称为归档操作。

1. 重做日志文件

# 重做日志文件,也叫联机重做日志文件（online redo log file），用来记录数据库发生过的更改信息（修改、添加、删除）以及oracle内部行为（创建表、索引等）而引起的数据库变化信息。

在数据库恢复时，可以从该日志读取原始记录。每次用户commit时，数据库都先将原始记录通过数据库自己的方法记录在日志文件中，只有写入日志文件成功时，才会将新纪录传回给用户。所以可以通过日志文件读取原始记录或恢复数据。

当对表或表空间设置nologging属性时，对其的dml、ddl操作，不生成日志。

# 查看日志文件信息
SQL> col member for a50;

# 查看当前运行情况
select  GROUP#, SEQUENCE#, MEMBERS, STATUS, ARCHIVED from v$log;

    GROUP#  SEQUENCE#	 MEMBERS STATUS 	  ARC
---------- ---------- ---------- ---------------- ---
	 1	   10	       1 INACTIVE	  YES
	 2	   11	       1 INACTIVE	  YES
	 3	   12	       1 CURRENT	  NO

# 查看文件路径
SQL> select group#,status,type,member from v$logfile;
    
    GROUP# MEMBER
---------- --------------------------------------------------
         3 /u01/app/oracle/oradata/ORCL/redo03.log
         2 /u01/app/oracle/oradata/ORCL/redo02.log
         1 /u01/app/oracle/oradata/ORCL/redo01.log

# oracle示例运行中产生的日志信息，首先被临时的存放在sga的重做日志缓冲区中（redo log buffer，sga中循环使用的一段内存区域），当发出commit命令（或日志缓冲区满）或是达到一些出触发条件时,例如：

用户提交

重做日志缓冲区满1/3时

有大于1M的重做日志缓冲区未被写入磁盘（不同版本可能不同，隐含参数_LOG_IO_SIZE控制）

每隔3 秒钟

DBWR 需要写入的数据的SCN大于LGWR记录的SCN，DBWR 触发LGWR写入。

LGWR进程将日志信息从重做日志缓冲区中读出，写入日志组中序列较小的文件里，在一个日志组写满后接着写入另一个日志组。在LGWR将所有能用的日志文件都使用过后，将会覆写第一个日志组。

2. 重做日志文件管理

1. 重做日志组管理

# 添加
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo01_2.log','/u01/app/oracle/oradata/orcl/redo01_2.log') size 500M;

SQL> alter database add logfile ('/u01/app/oracle/oradata/orcl/redo01_2.log','/u01/app/oracle/oradata/orcl/redo01_2.log') size 500M;

# 验证是否添加成功
select * from v$logfile;

# 删除联机重做日志组
SQL> alter database drop logfile group 4,group 5;

2. 重做日志成员及维护

# 向重做日志组1、2、3添加一个重做日志成员
alter database add logfile member
'/oradata01/mlbdb1/redo03_c.log' to group 3,
'/oradata01/mlbdb1/redo02_c.log' to group 2,
'/oradata01/mlbdb1/redo01_c.log' to group 1;

# 删除日志组中的一个日志成员
alter database drop logfile member '/oradata01/mlbdb1/redo01_c.log';

# 验证是否删除成功
SQL> select * from v$logfile where group#=1;

3. 重建重做日志组1并修改重做日志文件的大小

# 修改大小
alter database add logfile group 1
(
   '/oradata01/mlbdb1/redo01_a.log',
   '/oradata01/mlbdb1/redo01_b.log'
) size 1024m;

# 验证是否添加成功，以及日志成员的大小
select group#,sequence#,bytes,members,status from v$log;

4. 清除、检查点

# 清除日志
alter database clear logfile group n;

# 日志切换
alter system switch logfile; 

# 发生检查点
alter system checkpoint;

3. 归档日志文件

LOG_ARCHIVE_DEST：指定归档文件存放的路径，该路径只能是本地磁盘，默认为’’。

LOG_ARCHIVE_DEST_n：默认值为’’。Oracle最多支持把日志文件归档到10个地方，n从1到10。归档地址可以为本地磁盘，或者网络设备。

DB_RECOVERY_FILE_DEST：指定闪回恢复区路径。

三者关系：

1、 如果设置了DB_RECOVERY_FILE_DEST，就不能设置LOG_ARCHIVE_DEST，默认的归档日志存放于DB_RECOVERY_FILE_DEST指定的闪回恢复区中。可以设置LOG_ARCHIVE_DEST_n，如果这样，那么归档日志不再存放于DB_RECOVERY_FILE_DEST中，而是存放于LOG_ARCHIVE_DEST_n设置的目录中。如果想要归档日志继续存放在DB_RECOVERY_FILE_DEST中，可以通过如下命令：alter system set log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST’;

2、 如果设置了LOG_ARCHIVE_DEST，就不能设置LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST。如果设置了LOG_ARCHIVE_DEST_n，就不能设置LOG_ARCHIVE_DEST。也就是说，LOG_ARCHIVE_DEST参数和DB_RECOVERY_FILE_DEST、LOG_ARCHIVE_DEST_n都不共存。而DB_RECOVERY_FILE_DEST和LOG_ARCHIVE_DEST_n可以共存。

3、 LOG_ARCHIVE_DEST只能与LOG_ARCHIVE_DUPLEX_DEST共存。这样可以设置两个归档路径。LOG_ARCHIVE_DEST设置一个主归档路径，LOG_ARCHIVE_DUPLEX_DEST设置一个从归档路径。所有归档路径必须是本地的。

4、 如果LOG_ARCHIVE_DEST_n设置的路径不正确，那么Oracle会在设置的上一级目录归档。比如设置LOG_ARCHIVE_DEST_1=’location=C:\archive1’，而OS中并没有archive1这个目录，那么Oracle会在C盘归档。




# 由于所有的日志文件被写入一遍后，LGWR将覆写第一个日志组，从而导致一部分较早的日志信息被覆盖。所以数据库开启归档后，这些将被覆盖的日志将被保存在归档日志中。

在归档操作过程中，LGWR进程需要等待ARCH进程结束才能开始覆写日志文件，这也就延迟了系统响应时间，同时归档也占用了大量磁盘空间

# 查看是否开启归档
SQL> col name format a30;
SQL> select dbid,name,log_mode from v$database;
--或着
SQL> archive log list;

# 查看归档路径
SQL> archive log list;
--或者
SQL> set pagesize 30;
SQL> show parameter log_archive_dest;

# 修改为归档模式
(1)关闭数据库
shutdown immediate;

(2)启动到mount
startup mount;

(3)修改归档
alter database archivelog;

(4)将数据库打开
alter database open;

# 手动归档
alter system swicth logfile;
alter system archive log current;

(5)归档默认存储位置（闪回区），尽量不使用此路径，使用log_archive_dest_n
SQL> show parameter db_recovery_file_dest;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 2000M

(7)修改归档默认存储位置
SQL> alter system set log_archive_dest_1='location=/archlog/mlbdb1' scope=spfile;
SQL> alter system set log_archive_format='mlbdb1_%t_%s_%r.dbf' scope=spfile;

5. 密码文件、告警文件、跟踪文件

1. 跟踪文件

# 跟踪文件包括下面一些文件

 1）用户进程跟踪文件（Udump）

 用于记载与用户进程相关的信息，主要跟踪sql语句

 _ora_14778.trm，例如：orcl_ora_14778.trc

 2）后台进程的跟踪文件（Bdump）

 用于后台进程的警告和错误信息

 _processname_PID.trc 如：_orcl_lgwr_15941.trc

 每个后台进程都有对应的后台进程跟踪文件,而系统会将着错误的用户和后台进程的trc文件，分别复制一份到user_dump_dest和background_dump_dest,所以并不是网上说的background_dump_dest和user_dump_dest来决定，这可能和版本有关系。

另外增加.trm（trace map)文件，记录 trc 文件的结构信息

# 每个后台进程都有对应的后台进程跟踪文件

# 查看跟踪文件路径

SQL> select name,value from v$diag_info where name='Diag Trace';

NAME                                                             VALUE
---------------------------------------------------------------- --------------------------------------------------------------------------------
Diag Trace                                                       /u01/app/oracle/diag/rdbms/orcl/orcl/trace

2. 口令(密码)文件

# 密码文件有时也被称为口令文件，是oracle系统用于验证具有sysdba系统权限的用户远程登陆的认证，它是一个二进制文件。

以19c为例，默认存储在dbs目录下，密码格式为orapw

# 创建密码文件语法

orapwd file=<filename> password=<password>

#由于版本不同，越老的版本语法会有差异,查看帮助即可
orapwd --help
#可能需要的参数
format=12	#默认12.2,使用12可以忽略新特性高要求的密码复杂度
force=y		#覆盖
例子：

#删除后无法远程登陆
rm -f orapworcl

#重建
orapwd file=/u01/app/oracle/product/19.3/dbhome_1/dbs/orapworcl password=Oracle123 format=12

它受参数remote_login_passwordfile影响

SQL> show parameter remote_login_passwordfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile  
它的值对应的效果：

EXCLUSIVE：（默认值）独占模式使用密码文件，官档中提到了“only one instance of one database”使用exclusive方式，在数据库中是可以执行对于sysdba用户的增加，修改，删除动作的，同样也可以修改sysdba用户的密码，这些更改会记录到密码文件中去。

1.查看被授予sysdba权限的用户

SELECT USERNAME FROM V$PWFILE_USERS WHERE SYSDBA='TRUE';
2.口令文件中添加/删除sysdba权限用户

REVOKE SYSDBA FROM non-SYS-user;
GRANT SYSDBA TO non-SYS-user;
NONE：禁用口令文件验证

设置成none将直接禁用口令文件验证，sysdba用户只能通过操作系统认证登录数据库。此时的登录将会收到报错：

# Oracle寻找口令文件的顺序：orapw$ORACLE_SID --> orapw --> Failure

3. 告警文件

# 告警文件（告警日志文件）是一个存储在oracle系统目录下的文本文件，名字为alert_.log，它用来记录Oracle运行信息和错误信息。运行信息包括实例的启动和关闭，建立表空间、增加数据文件等以及有关数据库物理结构变化的信息；错误信息包括实例启动失败，扩展空间失败和一些内部错误信息等。

# 告警文件位置：

background_dump_dest和user_dump_dest是带错误的确定告警文件和跟踪文件的归档

在 oracle 11g 中，故障诊断及跟踪的文件路径改变较大，告警文件分别 xml 的文件格式和普通文本格式两种形式存在。这两份文件的位置分别是 V$DIAG_INFO 中的 Diag Alert 和 Diag Trace 对应的目录中取来的。

# 跟踪文件和文本格式的告警文件放在同一文件夹

SQL> select * from V$DIAG_INFO;

XML格式的就存放在Diag Alert中，文本格式的告警文件就在Diag Trace中


# 其他
show parameter dump_dest;
show parameter diagnostic; //查看ADR base
select name,value from v$diag_info;   //查看告警文件存储位置



alter system set diagnostic_dest = '/u02/app/oracle'; //修改诊断（错误）日志文件位置
ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;  // 开启DDL日志记录

逻辑备份

逻辑备份是指使用工具exp或expdp将数据库对象的结构和数据导出到二进制文件的过程。当数据库对象被误操作而损坏后就可以使用工具imp或impdp利用备份的文件把数据对象导入到数据库中进行恢复。逻辑备份是物理备份方式的一种补充，多用于数据迁移。

1. 命令

1. 导出数据

# 创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create or replace directory dump1 as '/u01/app/oracle/dump1';
# 查看管理理员目录（同时查看目录是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;

# 给导出数据用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read,write on directory dpdata1 to scott;


（1）按用户导出

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1

（2）并行进程PARALLEL

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

（3）按表名导出

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1

（4）按查询条件导出

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'

（5）按表空间导出

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example

（6）导出整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y

2. 导入数据

（1）导入指定用户下

impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott

（2）改变表的OWNER

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system

（3）导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example

（4）导入数据库

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

（5）追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=app

2. 示例

# 创建用户
create user test1 identified by test1 default tablespace test1;

# 创建表
create table EMP20
(
  employee_id    NUMBER(6),
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) not null,
  email          VARCHAR2(25) not null,
  phone_number   VARCHAR2(20),
  hire_date      DATE not null,
  job_id         VARCHAR2(10) not null,
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
);

# 创建导出目录(之后手动创建目录)
create or replace directory dump1 as '/u01/app/oracle/dump1';

# 授予test1操作权限
grant read,write on directory dump1 to test1;

# 导出test1用户数据
expdp system/oracle directory=dump1 dumpfile=test1.dmp schemas=test1;

# 将test1所有数据导入到另一个用户test2（要先创建）
impdp system/oracle directory=dump1 dumpfile=test1.dmp remap_schema=test1:test2

物理备份

1. RMAN Advisor

# 恢复指导需要全备和归档才能使用，归档是必须的

list failure——列出故障原因

advise failure——恢复建议

repair failure——按advice来恢复

change failure——改变故障状态

# 步骤
1. list failure
#例如删除数据文件后，list failure(要有相关备份)
rm -f test1.dbf

RMAN> list failure;


2. advise failure
RMAN> advise failure;

3. repair failure
#如果产生了automatic repairs，就可以使用repair failure恢复

#恢复预览
RMAN> repair failure preview;

#恢复
RMAN> repair failure;

SQL> select open_mode from v$database;

2. 归档模式

# 检查服务器是否为归档模式
SQL> SELECT log_mode FROM v$database;

# 查看归档信息
SQL> archive log list;

# 设置归档模式步骤
# 设置参数，修改日志地址（首先创建文件夹） 
alter system set log_archive_dest_1='location=/home/oracle/log_archive/arch01';
alter system set log_archive_dest_2='location=/home/oracle/log_archive/arch02';

# 设置状态
SQL> shutdown immediate; //立即关闭数据库
SQL> startup mount //启动实例并加载数据库，但不打开
SQL> alter database archivelog; //更改数据库为归档模式
SQL> alter database open; //打开数据库
# 查看归档信息
SQL> archive log list;

# 其他
SQL> alter system archive log start; //启用自动归档
SQL> alter system switch logfile; //手动切换一下日志文件存档，使修改生效
SQL> alter system archive log current; //手动归档

3. 备份操作

1. 参数配置

# 事先创建备份文件所在文件夹并授权
mkdir /orabackup
chown -R oracle.oinstall /orabackup

# 查看默认备份参数
SQL> show parameter recover 

# 修改备份参数
# 先修改备份空间大小（快速恢复区 fast_recovery_area，闪回恢复区 flash recovery area）
SQL> alter system set db_recovery_file_dest_size=10G; 

# 修改备份空间（快速恢复区 fast_recovery_area，闪回恢复区 flash recovery area）
SQL> alter system set db_recovery_file_dest='/home/oracle/fast_recovery_area' scope=both;

# 查看备份保留时间
SQL> show parameter control_file_record_keep_time;  

# 修改备份保留时间
RMAN> configure retention policy to recovery window of 7 days;

# RMAN操作
# 查看配置
RMAN> show all;

# 打开控制文件与服务器参数文件的自动备份
RMAN> configure controlfile autobackup on;

# 设置控制文件与服务器参数文件自动备份的文件格式
RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/oracle_bak/ctl_%F'; 

# 设置备份的并行级别，通道数1，内容为备份集（backupset）
RMAN> configure device type disk parallelism 1 BACKUP TYPE TO BACKUPSET;
# 设置备份内容为copy（与上一条命令对应）
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COPY;
主：back up as copy制作数据库文件的简单副本(与Linux cp命令相同),而backupset则是将片段备份为数据文件的表空间的逻辑实体.备份片段采用RMAN特定的二进制格式.



# 设置默认的备份设备为磁盘（或'SBT_TAPE'）
RMAN> configure default device type to disk;

# 设置备份的文件格式，只适用于磁盘设备 （设置前要创建文件夹，channel参数可选）
RMAN> configure channel 1 device type disk format '/home/oracle/oracle_bak/bakup_%U'; 

# 备份整个数据库并指定名称位置
RMAN> backup database format '/orabackup/ora_%d_%U.bak';



# 参数一：
备份保留策略
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; # 备份保留天数
CONFIGURE RETENTION POLICY TO REDUNDANCY 3; # 备份冗余数
CONFIGURE RETENTION POLICY TO NONE;
Note：
可以按时间策略进行保留，设置7天的窗口，7天后就会被标记为obsolete。
可以按冗余数进行保留，设置3份，超过3份就会被标记为obsolete。
report obsolete; 可以查看

# 参数二：
备份优化器开关
CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE BACKUP OPTIMIZATION OFF;

Note：rman自动采用优化算法进行备份，判断哪些需要备份，哪些可以跳过，防止备份冗余，节省空间。

# 参数三：
默认的备份设备类型
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;
Note：
指定默认备份的设备类型，可以是磁盘或者磁带，可以是第三方的SBT库。
Oracle自带SBT库位置SBT_LIBRARY=oracle.disksbt。

# 参数四：
控制文件自动备份开关
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Note：
当数据库发起备份，或者数据库结构发生变化，将会自动备份控制文件。
12c之后默认为开启状态。

# 参数五：
控制文件自动备份的路径和格式
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

Note：
配置控制文件自动备份的位置和格式，可以是磁盘或者磁带。

# 参数六：
配置备份通道的并行度
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

Note：
配置默认的通道数量，可以是磁盘的通道或者磁带的通道。

# 参数七：
备份副本数
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

# 参数八：
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK clear;

Note：
数据文件和归档的默认备份副本数。

# 参数九：
备份集的最大限制
CONFIGURE MAXSETSIZE TO UNLIMITED; # default

Note：
默认是无限制大小。

# 参数十：
备份加密开关
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

# 参数十一：
备份加密的算法
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

Note：
对备份集进行加密，当然进行还原备份集的时候，需要解密。
可以通过v$rman_encryption_algorithms查看加密算法种类，
加密算法有AES128、AES192、AES256

设置口令：
set encryption on identified by ‘oracle’ only;
解密：
set decryption identified by ‘oracle’;
关闭：
set encryption off;

# 参数十二：
备份集压缩算法
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

Note：
配置默认的压缩算法。
自带4种压缩算法：
BASIC：基本的压缩算法，
LOW：对备份吞吐量的影响最小，适用于CPU资源是限制因素的环境
MEDIUM：适用大部分环境，压缩比和速度最优组合
HIGH：最高压缩，适合网络慢的场景
V$RMAN_COMPRESSION_ALGORITHM视图有详细描述。

# 参数十三：
归档删数策略
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Note：
配置归档可自动删除的策略，可以配置备库应用完自动删除。
还有被备份的次数等。
需要归档存在在FRA区域才能自动删除。

# 参数十四：
控制文件的快照保存位置和格式
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_pretty.f’; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/WLDB/snapcf_wldb.f';

Note：
配置控制文件快照的保存位置，RAC需要配置在ASM磁盘组上。
快照控制文件是为了保障控制文件的一致性，
每次备份会刷新快照文件。

# 参数十五：
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
RMAN输出结果的保留天数。
V$RMAN_OUTPUT视图的记录内容。

2. 备份命令

# 备份整个数据库并指定名称位置
RMAN> backup database format '/orabackup/ora_%d_%U.bak';


# 备份数据库
RMAN> backup database plus archivelog;  # 备份所有数据文件、UNDO文件，包括归档日志文件，临时文件不备份，另外RMAN会自动备份Control File and SPFILE 

# 备份整个数据库并压缩备份集
RMAN> backup as compressed backupset database format '/u01/app/oracle/rmanbak/whole_%d_%U';  

# 备份数据文件
select FILE# from v$datafile; //使用文件号备份可以先查看
RMAN> backup datafile 8,10,11;
RMAN> backup as copy datafile 8,10,11;
RMAN> backup as compressed backupset datafile 8,10,11; //备份时压缩
RMAN> backup datafile '/oracle/oradata/orcl/users01.dbf','/oracle/oradata/orcl/tbs4.dbf';

# 备份参数文件
RMAN> backup spfile;

# 备份控制文件
RMAN> backup current controlfile;

# 备份表空间
RMAN> backup tablespace tbs1;
RMAN> backup tablespace tbs1,tbs2;

# 备份归档日志文件
RMAN> backup format '/u01/app/oracle/rmanbak/lf_%d_%U' archivelog all delete input;   
--delete input 删除所有已经备份过的归档日志

RMAN>  backup archivelog sequence between 50 and 120 thread 1 delete input;

RMAN> backup archivelog from time "sysdate-15" until time "sysdate-7";


# 备份在备份操作期间产生的新的归档日志
RMAN> backup database plus archivelog format  '/u01/app/oracle/rmanbak/lg_%d_%U' delete input;

# 备份闪回区
RMAN> backup recovery area;

# 检查备份操作
RMAN> report obsolete; //报告过期备份
RMAN> delete obsolete; //删除过期备份
RMAN> list backup;  //列出所有备份
RMAN> list backupset;  //列出所有备份
RMAN> report need backup; //查看所需要的备份文件
RMAN> list expired backup;  //列出无效备份
RMAN> list expired backupset; 列出无效backupset

3. 增量备份

# 0级增量
# 开启块跟踪 
mkdir /home/oracle/tracking
chown -R oracle.oinstall /home/oracle/tracking
SQL> ALTER DATABASE enable block change tracking using file '/home/oracle/tracking/orcl.trc';

# 增量与差异备份
RMAN> backup incremental level 0 database; 0级增量
RMAN> backup incremental level 1 database; 差异备份
RMAN> backup incremental level 1 cumulative database; 累积备份

4. 数据恢复

数据文件datafile

Restore 使用备份的数据进行还原
Recover 使用日志进行恢复

1. 非关键型数据文件损坏的服务器离线处理方法
SQL> startup mount
SQL> alter database datafile 16 offline;
SQL> alter database open;
RMAN> restore datafile 16;
RMAN> recover datafile 16;
SQL> alter database datafile 16 online;


2. 非关键型数据文件损坏的服务器在线处理方法
SQL> alter database datafile 16 offline;
RMAN> restore datafile 16;
RMAN> recover datafile 16;
SQL> alter database datafile 16 online;

--或

RMAN> run {
sql 'alter database datafile 16 offline';
restore datafile 16;
recover datafile 16;
sql 'alter database datafile 16 online';
}

3.临时文件不备份
# 破环临时文件
rm -f /oracle/oradata/orcl/temp01.dbf
rm -f /oracle/oradata/orcl/temp1.dbf

# 修复
1.添加新临时文件
alter tablespace temp add tempfile '/oracle/oradata/orcl/temp001.dbf' size 100M autoextend on;
2 .删除旧新临时文件
alter tablespace temp drop tempfile '/oracle/oradata/orcl/temp1.dbf';

控制文件controlfile

# 破坏控制文件
rm -f /oracle/oradata/orcl/control01.ctl
rm -f /oracle/oradata/orcl/control03.ctl

# 启动
SQL> shutdown abort;

SQL> startup
提示：
ORACLE instance started.

Total System Global Area  943718400 bytes
Fixed Size		    8627440 bytes
Variable Size		  805309200 bytes
Database Buffers	  121634816 bytes
Redo Buffers		    8146944 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter system set control_files='/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> shutdown abort

SQL> startup

日志文件组

# 破坏
rm -f /oracle/oradata/orcl/redo01.log  
rm -f /oracle/oradata/orcl/redo02.log  
rm -f /oracle/oradata/orcl/redo03a.log 
SQL> select a.GROUP#,a.SEQUENCE#,b.MEMBER,a.STATUS from v$log a, v$logfile b where a.GROUP#=b.GROUP# order by 1;

    GROUP#  SEQUENCE# MEMBER				  STATUS
---------- ---------- ----------------------------------- ----------------
	 1	   19 /oracle/oradata/orcl/redo01.log	  INACTIVE
	 1	   19 /oracle/oradata/orcl/redo01a.log	  INACTIVE
	 2	   20 /oracle/oradata/orcl/redo02a.log	  INACTIVE
	 2	   20 /oracle/oradata/orcl/redo02.log	  INACTIVE
	 3	   21 /oracle/oradata/orcl/redo03a.log	  CURRENT
	 3	   21 /oracle/oradata/orcl/redo03.log	  CURRENT

# 修复方法：从库中清除损坏的文件
alter database drop logfile member '/oracle/oradata/orcl/redo01.log';
alter database drop logfile member '/oracle/oradata/orcl/redo02.log';
alter database drop logfile member '/oracle/oradata/orcl/redo03a.log';
如果该文件为current 切换日志 alter system switch logfile; 再清除


5.2 一组成员都损坏 ---数据库不完全恢复 重置日志 
    查看归档日志 sequence 26
	基于sequence 不完全恢复
	
run {
startup mount;
set until sequence=27;
restore database;
recover database;
alter database open resetlogs;
}


# 一旦发生重置日志 立刻备份
RMAN> backup incremental level 0 database;

参数文件spfile

# 平时收集信息 
SQL> select dbid from v$database;

# 模拟
mv spfileorcl.ora /tmp

关机状态
RMAN> startup nomount
RMAN> set DBID=1469582717    
RMAN> restore spfile from '/oracle/flash_recovery_area/ORCL/autobackup/2022_09_04/o1_mf_s_1114527962_kk8mltyd_.bkp'; 此处为最新的控制文件自动备份 控制文件自动备份里含有参数文件的数据


SQL> startup force  重启服务器

数据库闪回

1. 闪回开启

1、闪回开启
a.开启归档
# mount状态：
alter database archivelog;

b.设置闪回区
db_recovery_file_dest：指定闪回恢复区的位置
db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟(1天),实际取决于闪回区大小

SQL> alter system set db_recovery_file_dest_size=60G scope=both;

SQL> alter system set db_recovery_file_dest='/oracle/data/fast_recovery_area' scope=both;

SQL> alter system set db_flashback_retention_target=4320 scope=both;

c.开启flashback (10g在mount开启)
SQL> alter database flashback on;

(4)确定闪回开启
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES    

(5)关闭闪回
SQL> alter database flashback off;

2. 闪回drop

# 开启回收站
ALTER SESSION SET recyclebin = ON; //当前会话开启
 
ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE; //永久开启

SELECT * FROM RECYCLEBIN WHERE TYPE='TABLE'; //查看回收站内容


# 注意 
1.对system表空间存储的数据无效
2.删除时使用purge 则无效
3.索引(drop index)无效 

# 具体步骤：
(1)、查询这个“回收站”或者查询user_table视图来查找已被删除的表:

show recyclebin;

select table_name,dropped from user_tables;

select object_name,original_name,type,droptime from user_recyclebin;

以上表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名。

#  a、如果还能记住表名，则可以用下面语句直接恢复：

  flashback table 原表名 to before drop;

#  b、如果记不住了，也可以直接使用回收站的表名进行恢复，然后再重命名，参照以下语句：

  flashback table "Bin$DSbdfd4rdfdfdfegdfsf==$0" to before drop rename to 新表名;


# 3、闪回整个数据库

使用数据库闪回功能，可以使数据库回到过去某一状态, 语法如下：

alter database flashback on;
flashback database to scn tmp_db1;
flashback database to timestamp to_timestamp('2017-6-28 11:30','yyyy-mm-dd hh24:mi:ss');



# 示例
SQL> create table student("id" char(6),"name" varchar2(10),"sex" char(1),"age" integer,"phone" varchar2(12)) tablespace test_db;

SQL> drop table student;
SQL> drop table student1 purge;
SQL> purge table TR02;

# 恢复操作
create table test tablespace users as select * from "BIN$59YTYxa8dv3gU45YHazhyQ==$0";
SQL> flashback table student to before drop;
SQL> flashback table "BIN$59YTYxbCdv3gU45YHazhyQ==$0" to before drop;

3. 闪回delete

# 注意：删除操作需要commit才可以成功。
# 具体实例及步骤：

(1) 确定删除数据的时间（在删除数据之前的时间就行，最好是删除数据的时间点）

# 查看oracle时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

# 找到删除的记录：

select * from test as of timestamp to_timestamp('2017-06-28 11:04','yyyy-mm-dd hh24:mi:ss');

# 把删除的数据重新插入原表：

insert into test ( select * from test as of timestamp to_timestamp('2017-06-28 11:04','yyyy-mm-dd hh24:mi:ss'));

commit;


(2) 如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据，表闪回要求用户必须要有flash any table权限。

# 具体步骤：

# 开启行移动功能

alter table test enable row movement;

# 恢复表数据
flashback table test to timestamp to_timestamp('2022-09-23 17:17:28','YYYY-MM-DD HH24:MI:SS');

# 关闭行移动功能(此操作千万要记住)
alter table test disable row movement;

4. 闪回truncate

# 准备工作
# 查看oracle时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> truncate table scott.EMP;

# 重启到mount
SQL> shutdown immediate;

SQL> startup mount;

# 闪回数据库
SQL> flashback database to timestamp to_timestamp('2017-12-14 14:12:46','yyyy-mm-dd HH24:MI:SS');

# 开启数据库，重建日志逻辑
SQL> alter database open resetlogs;


SQL> select * from scott.emp;

5. 常用查询语句

1、相关数据字典
V$FLASHBACK_DATABASE_LOG ##查看数据库可闪回的时间点/SCN等信息
V$flashback_database_stat ##查看闪回日志空间记录信息

2、常用查询语句
(1)查看数据库状态

SQL> select NAME,OPEN_MODE ,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from v$database;

NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON
------------- -------------------- ---------------- ----------- ------------------
TESTDB READ WRITE PRIMARY 16812246 YES

(2)获取当前数据库的系统时间和SCN
SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt, dbms_flashback.get_system_change_number scn from dual;

(3)查看数据库可恢复的时间点

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
16801523 2017-12-14 11:35:05 4320 104857600 244113408

(4)查看闪回日志空间情况

SQL> select * from V$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2017-12-14 14:34:53 2017-12-14 14:56:43 1703936 9977856 1487872 0    

(5)SCN和timestamp装换关系查询

select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time;

(6)查看闪回restore_point

select scn, STORAGE_SIZE ,to_char(time,'yyyy-mm-dd hh24:mi:ss') time,NAME from v$restore_point;

(7)闪回语句

a.闪回数据库
　　FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd HH24:MI:SS');;
　　flashback database to scn 16813234;
b.闪回DROP
其中table_name可以是删除表名称，也可以是别名
　　flashback table table_name to before drop;
　　flashback table table_name to before drop rename to table_name_new;
c.闪回表
　　flashback table table_name to scn scn_number;
　　flashback table table_name to timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');
d.闪回查询
　　select * from table_name as of timestamp to_timestamp('2017-12-14 14:28:33','yyyy-mm-dd hh24:mi:ss');
　　select * from scott.dept as of scn 16801523;

数据库网络(监听)

1. 理解监听器

# 启动和关闭监听(oracle用户下执行)
[oracle@kevin ~]$ lsnrctl start
[oracle@kevin ~]$ lsnrctl stop

# 几种连接数据库用到的命令形式：
1)sqlplus "/ as sysdba" 这是典型的操作系统认证，不需要listener进程，数据库即使不可用也可以登录。
2)sqlplus username/password 不需要listener进程，登录本机数据库，数据库实例启动即可。
3)sqlplus username/password@tnsname需要listener进程，最常见的远程登录模式，需要启动数据库实例和listener进程。


1、实例以 service 的形式对外提供客户端访问支持，监听器负责注册 service  。

2、Service_name 对外屏蔽了实例和数据库的复杂描述。 

3、客户端 tnsnames.ora 中 SERVICE_NAME 与服务器端 listener.ora 的静态监听中 GLOBAL_DBNAME 等同。

4、listener.ora 中监听器的 SID_NAME 与实例的 SID 等同，对应 instance_name。

5、一个 linstener.ora  可以描述多个监听器。 

6、一个 tnsnames.ora  可以描述多个网络连接符。  

7、一个 listener.ora 可以为多个数据库描述监听器。  

8、一个实例可以对应多个 service_name。

2. 对应关系

1. 相关命令

# 启动图形化界面
netca
netmgr
# 注意
配置监听时，tns的service_name 与 listener的global_name对应，服务端的tnsnames.ora也要配置

# 查看监听配置文件
vim $ORACLE_HOME/network/admin/listener.ora

# 查看sid
select instance_name from V$instance;

# 查看service_names和instance_name
SQL> show parameter instance_name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
instance_name			     string	 orcl

SQL> show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 orcl.example.com

2. 客户端连接Oracle服务器流程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A7nGehri-1675241084763)(D:\运行培训\笔记\数据库\监听.png)]

1. 客户端使用sqlplus system/oracle@ORCL

# tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.132)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
根据tnsnames.ora的配置，这里ORCL就是orcl这个SERVICE_NAME所对应的NET SERVICE NAME,只有用sqlplus system/oracle@ORCL才行,NET SERVICE NAME可以随意设置，但是当源数据库GLOBAL_NAME=TRUE时，link_name必须与远程数据库的全局数据库名global_name相同；否则，可以任意命名

2. NET SERVICE NAME（ORCL）映射到Oracle服务器监听文件listener.ora

# listerner.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )     
         
  )       
         
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.132)(PORT = 1521))
  )   

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora与listerner.ora的连接（对应）基于参数SERVICE_NAME，通常情况下，我们会设置SERVICE_NAME和INSTANCE_NAME（SID)一致。这里还要注意的是，SERVICE_NAME虽然可以脱离INSTANCE_NAME搞别名，但是必须对应listener.ora里配置的GLOBAL_DBNAME，否则就算配好了静态监听，也是注册不了服务的。

# SERVICE_NAME和GLOBAL_DBNAME：
这两个参数之所以放在一起讲，是因为他们往往是成对出现的。SERVICE_NAME出现在tn snames.ora文件中，是客户端要请求的服务名。GLOBAL_DBNAME出现在listener.ora文件中，是服务器提供的服务名。

# 查看
show paramerer service_names
# 修改
alter systemset service_name='servicename' scope=both

二者对应，实现了listerner.ora/tnsnames.ora的重要功能——监听、请求与验证。


4. listener.ora 基于SID_NAME指定连接实例，就是数据库实例名，在Linux环境大小写敏感
SID_NAME 就是SID和INSTANCE_NAME

# ORACLE_SID：（ORACLE SYSTEM IDENTIFIER）

以环境变量的形式出现的。Oracle实例是由SGA和一组后台进程组成的，实例的创建和启动需要一个参数文件，而参数文件的名称就是由ORACLE_SID决定的。对于init文件，缺省的文件名称是init<ORACLE_SID>.ora，对于spfile文件，缺省的文件名称是spfile<ORACLE_SID>.ora

设置不同的ORACLE_SID值，就可以默认使用不同的参数文件启动不同的数据库实例。另外，ORACLE_SID的作用远远不是作为一个实例入口这么简单的，在实例启动后，实例名称INSTANCE_NAME也是从ORACLE_SID得到的。

# INSTANCE_NAME:

实例名称，这是Oracle实例的名字，用来区分不通的实例。在Oracle9i之前，该名字存储在两个地方：参数文件和数据库的内部试图（V$INSTANCE），而在Oracle10g之后的版本中，该名字不再出现在参数文件中，而是动态从系统中获得，默认是取自ORACLE_SID。INSTANCE_NAME的作用除了区别不同实例之外，在监听器动态注册时，还会用于向监听器注册。比如instance_name=zlm,监听中将动态注册Instance "zlm",status READY信息。

# 查看以上两个参数
# 查看sid
select instance_name from V$instance;
# 查看instance_name
SQL> show parameter instance_name

3. 涉及配置文件

1. tnsnames.ora

# 用在oracle client端，用户配置连接数据库的别名参数,就像系统中的hosts文件一样。提供了客户端连接某个数据库的详细信息，主机地址，端口，数据库实例名等。

# 示例：
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
PROTOCOL参数一般是TCP，可以根据服务器情况选择一种配置方式。
HOST 一般是ip地址，如果是主机名，则应该在客户端系统的host文件上配好主机名和ip地址的映射关系。

PORT 标准是1521，根据服务器端的监听端口而定。

2. listener.ora

tnslsnr进程是监听、并接受远程连接数据库请求的监听进程。listener.ora是tnslsnr进程的配置文件，监听的参数都是从该配置文件中读取，该文件位于服务端。如果你只需要在本地连接数据库，不接受远程连接，那么也不需要启动tnslsnr进程，也不需要去维护listener.ora文件。
启动监听进程的命令，在命令模式下，执行lsnrctl start命令就启动了监听进程tnslsnr。

# 示例：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (ORACLE_HOME = /oracle/product/10.2.0/db_1/)
      (SID_NAME = eas)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

LISTENER部分包含协议地址列表，每个实例一个入口。

SID_LIST_LISTENER部分标识全局数据库名称、标识监听程序正在服务的每个实例的Oracle软件主目录以及实例或SID。

当你执行命令sqlplus username/password@tnsname时，一般处理过程如下：
1） 查询sqlnet.ora看看名称的解析方式，由参数NAMES.DIRECTORY_PATH控制，常用参数值TNSNAME和hostname，不指定参数则默认使用TNSNAME。
2） 如果使用TNSNAME，则查询tnsnames.ora文件，从里边找tnsname的记录，并且找到主机名，端口和service_name。
3） 如果listener进程没有问题的话，建立与listener进程的连接。
4） 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。
5） 这时候网络连接已经建立，listener进程的历史使命也就完成了。

4. 动态注册

动态注册是在实例启动的时候，或使用命令Alter system register，或每隔一分钟，PMON进程会向监听进行动态注册，PMON进程根据init.ora中的instance_name，service_names两个参数将实例和服务动态注册到listener中。
如果init.ora中的instance_name，service_names两个参数没有指定值，那么系统将取默认值，注册到监听器中的实例名将取init.ora文件中的db_name值，服务名将取init.ora文件中的db_name+db_domain值。可以在service_names参数中指定多个服务值，值之间用逗号隔开，这对于共享服务器配置很有用。

# 前提
动态注册是默认启动的，要想立马刷新动态注册内容，可以使用Alter system register


# 查看动态注册
# 采用动态注册时，不需要有listener.ora，需要先配置一下local_listener才可以使用

1. 首先将listener.ora文件重命名。

[oracle@strong admin]$ mv listener.ora listener.ora.bak
[oracle@strong admin]$ ls
listener.ora.bak  samples  shrept.lst  tnsnames.ora


2. 启动监听器，并查看其状态。

[oracle@strong ~]$ lsnrctl start
 
[oracle@strong ~]$ lsnrctl status

Services Summary...
Service "orcl" has 1 instance(s).
  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

# 说明：Instance状态为READY，说明采用动态注册监听器。


# 配置非默认端口动态注册
动态注册默认只注册到默认的监听器上（名称为LISTENER，端口为1521，协议为TCP），因为PMON只会动态注册端口为1521的监听，否则不能动态注册，如果使用非默认端口注册，则需要配置local_listener参数。


1. 配置listener.ora参数，新增LISTENER2，监听端口为1522

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = c12.example.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )


2. 修改local_listener参数，配置监听地址
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.202.132)(PORT=1522))','(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.202.132)(PORT=1521))';

或：

(1) 在Oracle服务器端建立$ORACLE_HOME/network/admin/tnsnames.ora解析文件,并填入如下内容

test =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS=(PROTOCOL=TCP)(HOST = 192.168.202.132)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

(2) 设置参数，指定通过tnsnames.ora内的信息指定监听参数
SQL>alter system set local_listener=test;

(3) 连接
sqlplus test/test@test # @test为配置的tns名称

# 接上
SQL> alter system register;
 
SQL> show parameter local_listener

5. 静态注册

静态注册就是实例启动时读取listener.ora文件的配置，将实例和服务名注册到监听器中。静态注册时，listener.ora中的GLOBAL_DBNAME提供服务名，listener.ora中的SID_NAME提供注册的实例名。

# 配置静态注册监听

1. listener.ora文件中配置静态注册
添加
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =orcl)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(SID_NAME =orcl)
)
(SID_DESC =
(GLOBAL_DBNAME =orcl1)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(SID_NAME =orcl)
)
)
# 解释
listener.ora文件两大模块：

LISTENER模块：监听名字、连接协议、监听主机、监听端口等基本配置信息

SID_LIST_LISTENER模块：配置监听的静态注册特性，包含数据库服务名、ORACLE_HOME、实例名等信息。

注意：SID_NAME，就是数据库实例名，在Linux环境大小写敏感

GLOBAL_DBNAME就是数据库服务名,可以省略，默认和SID_NAME保持一致，也可以不一致。

ORACLE_HOME，默认和$ORACLE_HOME环境变量保持一致。Windows，该参数无效，取自注册表。
该文件表明数据库是单实例的，实例名为orcl，向外提供了两个服务：orcl和orcl1

2.重启监听
lsnrctl stop
lsnrctl start

6. 配置连接串

# 修改tnsnames.ora文件
提供tnsname到主机名或者ip的对应(也是客户端监听器配置文件)

orclstatic =
　(DESCRIPTION =
   　　(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))   #对应的SOCKET信息
     　(CONNECT_DATA =                  
         　　(SERVER = DEDICATED)            #使用专用服务器模式去连接（server=shared共享模式）
           　(SERVICE_NAME = orcl)           #这里填入对应 service_name，
                                           
　 )

7. 配置DBLINK

创建dblink是在客户端创建，基于客户端tnsnames.ora建立的监听配置创建服务端数据库的dblink
# 授权
SQL> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to test;

Grant succeeded.

# 基于tnsnames.ora建立的监听创建连接
SQL> create public database link test_link connect to test identified by test123 using 'ORCL2';

Database link created.

# 或者直接创建
create database link link_name
connect to username identified by password
using ' (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.13.178)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rzzqnew)
    )
  )';

# 查看是否创建成功
SQL> select * from dual@test_link;

D
-
X

# 使用
SQL> desc p_test@test_link;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER(7)
 TYPE						    VARCHAR2(1)

mysql

启动数据库

cp support-files/mysql.server /etc/init.d/mysql.service

service mysql.service start

启动成功

Starting MySQL.Logging to '/data/mysql/data/mysql.server.err'.
 SUCCESS!

报错日志将在该文件中找到：/data/mysql/data/mysql.server.err

查看 MySQL 运行状态

[root@localhost mysql]# service mysql.service status

修改 root 用户密码

[root@mysql ~]# /data/mysql/bin/mysql -u root -p
Enter password: # 这里输入旧密码
mysql> set password for root@localhost = password('123456Aa.');
mysql> flush privileges;

授权 root 用户远程登录

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456Aa.';
mysql> flush privileges;

创建一个以自己名字命名的数据库

# mysql> CREATE DATABASE <改成自己的名字> DEFAULT CHARSET utf8mb4;
mysql> CREATE DATABASE liuli DEFAULT CHARSET utf8mb4;
mysql> SHOW DATABASES;

远程连接

C:/>mysql -h 192.168.5.116 -P 3306 -u root -p123456

mysql主从

1、开启 bin-log 配置

Master

修改 master 服务器的配置，开启 bin-log

vi /data/mysql/my.cnf

添加如下内容

server-id=6
log-bin=/data/mysql/data/binlog

重启数据库生效

service mysql restart

Slave

修改 Slave 服务器的配置，开启 relay-log

vi /etc/my.cnf

server-id=7
relay-log=/var/lib/mysql/relaylog

重启数据库生效

systemctl restart mysqld

2、配置主从同步

Master

Master 数据库赋予 Slave 有读取日志的权限

mysql> grant FILE on *.* to 'root'@'192.168.93.131' identified by '123456Aa.';
mysql> grant replication slave on *.* to 'root'@'192.168.93.131' 
        identified by '123456Aa.';
mysql> flush privileges;

查看二进制文件的名称及位置

mysql> show master status;

Slave

Slave 配置 Master 数据库的相关信息

master_host：主机的IP地址
master_user：主机的user账号
master_password：主机的user账号密码
master_port：主机MySQL的端口号
master_log_file：二进制日志文件名称
master_log_pos：二进制日志文件位置

mysql> CHANGE MASTER TO master_host = '192.168.93.169',
master_user = 'root',
master_password = '123456Aa.',
master_port = 3306,
master_log_file = 'binlog.000001',
master_log_pos = 885;

启动slave数据同步

mysql> start slave;
mysql> show slave status\G

看到这两项配置显示为 yes 说明成功

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0
        # slave 节点与 master 节点的时延，保证为0，确保数据一致。

mysql数据库常用命令

用户与权限管理

用户创建与管理

# 创建
create user '用户名'@'被允许链接的主机ip' identified by '用户的密码';
# 查看
select user,host from mysql.user;
# 删除
drop user '用户名'@'被允许链接的主机ip';

# 修改 Jack 的远程登陆主机为 % 所有主机
rename user 'Jack'@'192.168.1.100' to 'Jack'@'%';

用户权限

# 查看权限
select Host,User,Grant_priv,Super_priv from mysql.user;

# 设置 `Grant_priv` 为 `Y`
update mysql.user set Grant_priv='Y',Super_priv='Y'
where user = 'root' and host = '%';

# 授权
grant 权限1，权限2，权限3 on 库.表 to '用户'@'主机';
# 刷新
flush privileges;

数据库操作

创建数据库

# 如果不存在则创建 test_db3 库并指定默认字符集
create database if not exists test_db3 default charset utf8mb4;

# 查看 test_db3 的创建语句
show create database test_db3;

修改数据库

# 修改 test_db1 的默认字符编码为 gbk
alter database test_db1 default charset gbk;

删除数据库

# 删除 test_db 数据库
drop database test_db;

数据表操作

创建数据表

# 创建 tb_admin 表，包含字段 id、username、password
CREATE TABLE tb_admin 
( id INT, NAME VARCHAR ( 20 ), age INT, phone VARCHAR ( 20 ));

显示数据表

# 查看 tb_admin 的创建语句
show create table tb_admin;

# 显示表字段
desc tb_admin;

修改数据表

# 添加 phone 字段 
ALTER TABLE tb_admin ADD addr VARCHAR ( 30 );

# 修改字段 phone 类型
ALTER TABLE tb_admin MODIFY phone INT;

# 修改 password 字段名称为 passwd
ALTER TABLE tb_admin CHANGE NAME username VARCHAR ( 32 );

# 删除 phone 字段
ALTER TABLE tb_admin DROP phone;

# 修改 tb_admin 名称为 tb_user
RENAME TABLE tb_admin TO tb_user;

删除数据表

# 删除 tb_user 表
DROP TABLE tb_admin;

数据操作

增加数据

# 指定字段插入数据
INSERT INTO tb_user ( id, username, age, phone, addr )
VALUES
	( 1, 'liuli', 27, 110, '南京' );

修改数据

# 将 id 为 1 的 username 改为 tester
UPDATE tb_user 
SET username = 'tester' 
WHERE
	id = 1;

删除数据

# 删除表数据
delete from tb_admin;

# 直接清空表数据
truncate tb_admin;

自增与主键约束

自增（auto_increment）、主键（primary key）

# 将 id 字段进行自增编号，并设置为主键
CREATE TABLE tb_user2 (
	id INT NOT NULL auto_increment PRIMARY KEY,
	name VARCHAR ( 20 ),
phone VARCHAR ( 11 ) 
)

高级sql

排序（ORDER BY）

SELECT * FROM EMPLOYEES ORDER BY SALARY ASC;

条件查询（WHERE）

# 查询 EMPLOYEE_ID 为 100 的行
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
# 查询名以 P 开头的员工
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'P%';
# 查询姓以 e 结尾的员工
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%e';
# 查询名中包含 an 的员工
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%an%';
# 查询名为四个单词的员工
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '____';
# 查询出现的名
SELECT DISTINCT(FIRST_NAME) FROM EMPLOYEES;

统计记录数（COUNT）

# 查询一共多少行
SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;
# 统计薪水大于 20000 的员工有多少个
SELECT COUNT(SALARY) FROM EMPLOYEES WHERE SALARY > 20000;
# 统计薪水在 15000 ~ 20000 之间的员工有多少个
SELECT COUNT(SALARY) FROM EMPLOYEES WHERE SALARY > 15000 and SALARY < 20000;
SELECT COUNT(SALARY) FROM EMPLOYEES WHERE SALARY BETWEEN 15000 AND 20000;
# 统计薪水小于 2500 或者大于 20000 的员工有多少个
SELECT COUNT(SALARY) FROM EMPLOYEES WHERE SALARY < 2500 OR SALARY > 20000;

查出前三条记录

SELECT * FROM EMPLOYEES WHERE ROWNUM < 4;

查出后三条记录

SELECT * FROM EMPLOYEES WHERE ROWNUM < 4 ORDER BY EMPLOYEE_ID DESC;

关于 rownum，它是 oracle 系统顺序分配为从查询返回的行的编号，
返回的第一行分配1、第二行是2、以此类推，这个伪字段可以限制查询返回的总行数。

聚合函数（count、max、min、sum、avg）

# 查看薪水最高的员工
SELECT MAX(SALARY) FROM EMPLOYEES;
SELECT * FROM EMPLOYEES WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES);
# 查看薪水最少的员工
SELECT * FROM EMPLOYEES WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES);
# 统计薪水总数
SELECT SUM(SALARY) FROM EMPLOYEES;
# 统计薪水的平均数
SELECT AVG(SALARY) FROM EMPLOYEES;
SELECT ROUND(AVG(SALARY), 2)  FROM EMPLOYEES;

嵌套查询

# 找出比员工号为108的员工薪水高的人
SELECT * FROM EMPLOYEES WHERE SALARY > (SELECT SALARY FROM	
EMPLOYEES WHERE	EMPLOYEE_ID = 108);

SELECT * FROM EMPLOYEES WHERE SALARY IN (SELECT SALARY FROM	
EMPLOYEES WHERE	SALARY > 15000);
SELECT * FROM EMPLOYEES WHERE SALARY NOT IN (SELECT SALARY FROM	
EMPLOYEES WHERE	SALARY > 15000);

多表联合查询

笛卡尔积

# 列之和，行之积，笛卡尔积本身无意义，特殊需求可用 where 过滤
SELECT * FROM COUNTRIES,REGIONS;

内联接（inner join）

SELECT * FROM COUNTRIES INNER JOIN REGIONS ON COUNTRIES.REGION_ID = REGIONS.REGION_ID;

外联接

外连接 (OUTER JOIN)分为三种

左外连接 (LEFT OUTER JOIN 或 LEFT JOIN）：左表的记录将会全部表示出来，
而右表只会显示符合搜索条件的记录，右表记录不足的地方均为 NULL
右外连接 (RIGHT OUTER JOIN 或 RIGHT JOIN)：与左(外)连接相反，右(外)连接，
右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录，左表记录不足的地方均为NULL
全外连接 (FULL OUTER JOIN 或 FULL JOIN)：左表和右表都不做限制，所有的记录都显示，
两表不足的地方用 null 填充

SELECT * FROM JOB_HISTORY LEFT JOIN EMPLOYEES 
ON JOB_HISTORY.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID;

SELECT * FROM JOB_HISTORY RIGHT JOIN EMPLOYEES 
ON JOB_HISTORY.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID;

SELECT * FROM JOB_HISTORY FULL JOIN EMPLOYEES 
ON JOB_HISTORY.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID;

分组查询

# 统计每个岗位员工的数据量
SELECT JOB_ID,COUNT(JOB_ID) AS num FROM EMPLOYEES GROUP BY JOB_ID ;
# 每个岗位薪水大于 5000 岗位数
SELECT JOB_ID,COUNT(JOB_ID) AS num FROM EMPLOYEES 
WHERE SALARY> 5000 GROUP BY JOB_ID ;

逻辑备份

1. 表级
```bash
# 备份 test-db 库中的 t_disease 表
/data/mysql/bin/mysqldump -h127.0.0.1 -uroot -P3306 -p123456Aa. 
--databases liuli  > /root/liuli.sql 
# 还原
/data/mysql/bin/mysql -h127.0.0.1 -uroot -P3306 -p123456Aa. 
liuli  < /root/liuli.sq

库级

# 备份整个 test-db 库
mysqldump -h192.168.1.11 -uroot -P3306 -p123456Aa.
--databases test-db > /root/test-db.sql
# 还原
mysql -h192.168.1.11 -uroot -P3306 -p123456Aa. < /root/test-db.sql

全库

注意点

必须开启 bin-log 日志
–master-data：将二进制日志位置和文件名写入到备份文件，=1：不注释该行，=2：注释该行，默认 0
–single-transaction：适用于innoDB引擎，保证一致性，服务可用性

# 备份整个 MySQL 服务的所有库
mysqldump -h192.168.1.11 -uroot -P3306 -p123456Aa. 
--all-databases --master-data --single-transaction > all.sql
# 还原
mysql -h192.168.1.11 -uroot -P3306 -p123456Aa. < /root/all.sql

全库 + 增量

需要搭配 bin-log 日志恢复

# 备份整个 MySQL 服务的所有库
mysqldump -h192.168.1.11 -uroot -P3306 -p123456Aa. 
--all-databases --master-data --single-transaction > all.sql
# 还原
mysql -h192.168.1.11 -uroot -P3306 -p123456Aa. < /root/all.sql
# 使用 bin-log 还原 mysql 指定位置：4 ~ 750 之间的操作数据
mysqlbinlog --start-position=4 --stop-position=750 
/root/mysql-bin.000001 | -h192.168.1.11 -uroot -P3306 -p123456Aa.