【笔记】oracle基础

oracle笔记

oracle4大文件

参数文件

规定了软件需要什么位置，安装时会检查主机是否符合。参数文件的作用是规定oracle数据库运行规则，国家管理机制，国，省，市，区
参数文件位置：/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoltp.ora

[root@oracle dbs]# cat spfileoltp.ora
C"rj▒qH▒DCC"Huoltp.__db_cache_size=520093696
oltp.__java_pool_size=16777216
oltp.__large_pool_size=16777216
oltp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oltp.__pga_aggregate_target=671088640
oltp.__sga_target=989855744
oltp.__shared_io_pool_size=0
oltp.__shared_pool_size=419430400
oltp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oltp/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oltp/control01.ctl','/u01/appCC")i/oracle/flash_recovery_area/oltp/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oltp'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oltpXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
CC"GeCC"FeC[root@oracle dbs]# pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[root@oracle dbs]#

控制文件

规定数据文件、日志、归档等都放在哪里，表空间和表的对应关系等
存放位置：/u01/app/oracle/oradata/oltp/control01.ctl或/u01/app/oracle/flash_recovery_area/oltp/control02.ctl
控制文件是有主备的，01为主，02为备

查询控制文件位置命令：select NAME from v$controlfile;

数据文件

存储写入数据的位置，数据库最重要的角色。
位置：/u01/app/oracle/oradata/oltp/system01.dbf

查询数据文件位置命令：select NAME from v$datafile;

[root@oracle oltp]# ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[root@oracle oltp]# pwd
/u01/app/oracle/oradata/oltp
[root@oracle oltp]#

日志文件

记录操作/u01/app/oracle/oradata/oltp/redo01.log

查询日志文件位置命令：select * from v$logfile;

[root@oracle oltp]# ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[root@oracle oltp]# pwd
/u01/app/oracle/oradata/oltp
[root@oracle oltp]#

日志文件可以用来向前滚。
日志文件写满之后会变成归档文件，3个redo.log文件之间是镜像管理，联想存储划LUN，分在不同的LUN上；它们其实是3个日志小组，小组内可以加多个成员。
在客户端向数据库写入数据文件时，会先存放到LOG文件中，再落向数据文件里。

数据库写入数据过程

当设备某一时刻故障后，做了快照回滚，是会把所有数据都回滚。比如，在t1时刻打了快照，而在t2时刻设备故障，回滚快照后，DB数据文件回滚至t1时刻，t1至t2时间段内新增数据就会丢失。

而日志的作用是，当备份回滚后，读取log日志，将数据文件恢复到故障前的某个瞬间。

举个例子：日志像史官，皇上走在路上，皇宫里面有一个存档点，存档，华清池去找杨贵妃，经过小土坡，一道闪电，把皇帝电焦香了；启动数据回滚，滚到了存档点，史官跳到了小土坡，避开闪电劈中的地方

数据库结构

mysql数据库，单体结构
一个IO只有一个节点处理，这个IO的完成由一个节点的CPU、内存、硬盘

oracle rac（real application cluster）

王二狗，张红，王二狗5个亿，王二狗开个张卡，存5W，张红副卡，5W，张红买个LV，4.5W，王二狗开车撞人了，8000千，5.3W，IO被分到1号节点和2号节点，IO1进来（王二狗），到1号节点，告诉其他节点，改表格中的某个数据，其他不能改，IO2（张红），到2号节点，等待1号修改完，再允许2号IO修改。
三个节点都能处理数据，磁盘是共享，所以叫share-disk
如果节点越多，会有更多的人访问存储，存储的业务业务压力就会比较大，所以可以购买多台存储或者多个lun均衡到不同的存储控制上或者扩控
表和存储空间的关系是什么？数据库的逻辑存储空间和物理存储控的关系

什么都不共享，PQ，Gaussdb，分布式数据库

每个节点都是独立的，每个节点都可以处理数据，数据是分散，没有重复
怎么保证数据的冗余？数据主备，Node1disk1会把数据复制到Node2disk1上。
分布式数据库前端一定会有一个逻辑角色，这个角色像快递转运中心（可以单独部署，也可以和数据库数据节点在一起，华为成为CN也叫Coordinator），将IO分散到所属节点上

在10TB数据量计算，对与share-disk结构，一个IO只能由一个节点承载，对于share-nothing，可以由3个节点同时计算。
在小IO，多，随机的场景下share-disk会更好，因为直接，share-disk直接分节点计算，share-nothing，想这个数据在那个节点上（转运中心），再分节点计算。
人大金仓、OceanBase、GuassDB。

oracle4种模式

4个模式
1. shutdown：关闭状态，数据库未运行
2. nomount：检查数据库与主机的指标。是否可以运行数据库，读取参数，实例开始分配内存、运行进程
3. mount：读取控制文件。记录了数据文件、日志文件
4. open：读取数据文件日志文件，是否开启归档模式；ok之后可以运行数据库
状态控制
1. shutdown：一定要等所有的会话结束，才会结束数据库
2. shutdown immediate：主动结束会话。然后关闭
3. shutdown abort：中断会话

查询数据库当前的状态：select status from v$instance;

starup;               #启动到open
startup nomount;      #读取参数文件
startup mount;        #读取控制文件
startup open;         #启动到open

shutdown–>nomount读取参数文件

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


[root@oracle oltp]# ps -ef |grep ora
root       3250   3239  0 17:18 pts/1    00:00:00 su - oracle
oracle     3251   3250  0 17:18 pts/1    00:00:00 -bash
oracle    38177      1  0 17:59 ?        00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
root      39804  39744  0 19:05 pts/0    00:00:00 su - oracle
oracle    39805  39804  0 19:05 pts/0    00:00:00 -bash
oracle    40335  39805  0 19:38 pts/0    00:00:00 sqlplus   as sysdba
oracle    40336  40335  0 19:38 ?        00:00:00 oracleoltp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      40354  39999  0 19:44 pts/3    00:00:00 grep ora

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             939526256 bytes
Database Buffers          654311424 bytes
Redo Buffers                7360512 bytes

[root@oracle oltp]# ps -ef |grep ora
root       3250   3239  0 17:18 pts/1    00:00:00 su - oracle
oracle     3251   3250  0 17:18 pts/1    00:00:00 -bash
oracle    38177      1  0 17:59 ?        00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
root      39804  39744  0 19:05 pts/0    00:00:00 su - oracle
oracle    39805  39804  0 19:05 pts/0    00:00:00 -bash
oracle    40335  39805  0 19:38 pts/0    00:00:00 sqlplus   as sysdba
oracle    40380      1  0 19:44 ?        00:00:00 ora_pmon_oltp
oracle    40382      1  0 19:44 ?        00:00:00 ora_vktm_oltp
oracle    40386      1  0 19:44 ?        00:00:00 ora_gen0_oltp
oracle    40388      1  0 19:44 ?        00:00:00 ora_diag_oltp
oracle    40390      1  0 19:44 ?        00:00:00 ora_dbrm_oltp
oracle    40392      1  0 19:44 ?        00:00:00 ora_psp0_oltp
oracle    40394      1  0 19:44 ?        00:00:00 ora_dia0_oltp
oracle    40396      1  4 19:44 ?        00:00:00 ora_mman_oltp
oracle    40398      1  0 19:44 ?        00:00:00 ora_dbw0_oltp
oracle    40400      1  0 19:44 ?        00:00:00 ora_lgwr_oltp
oracle    40402      1  0 19:44 ?        00:00:00 ora_ckpt_oltp
oracle    40404      1  0 19:44 ?        00:00:00 ora_smon_oltp
oracle    40406      1  0 19:44 ?        00:00:00 ora_reco_oltp
oracle    40408      1  0 19:44 ?        00:00:00 ora_mmon_oltp
oracle    40410      1  0 19:44 ?        00:00:00 ora_mmnl_oltp
oracle    40412      1  0 19:44 ?        00:00:00 ora_d000_oltp
oracle    40414      1  0 19:44 ?        00:00:00 ora_s000_oltp


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

[root@oracle oltp]# cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
[root@oracle dbs]# mv spfileoltp.ora /root/

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

[root@oracle dbs]# mv /root/spfileoltp.ora  .

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             939526256 bytes
Database Buffers          654311424 bytes
Redo Buffers                7360512 bytes

参数文件有两种：二进制和文本，所以有些修改参数可以直接修改文本文件再覆盖二进制文件

SQL> create pfile from spfile;

pfile就是文本文件，spfile是二进制文件
[root@oracle dbs]# cat initoltp.ora
oltp.__db_cache_size=654311424
oltp.__java_pool_size=16777216
oltp.__large_pool_size=16777216
oltp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oltp.__pga_aggregate_target=654311424
oltp.__sga_target=956301312
oltp.__shared_io_pool_size=0
oltp.__shared_pool_size=251658240
oltp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oltp/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oltp/control01.ctl','/u01/app/oracle/flash_recovery_area/oltp/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oltp'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oltpXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1600126976
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[root@oracle dbs]# ls
hc_DBUA0.dat  hc_oltp.dat  initoltp.ora  init.ora  lkOLTP  orapwoltp  spfileoltp.ora


如果想修改文本之后覆盖二进制
[root@oracle dbs]#rm -f spfileoltp.ora

SQL> create spfile from pfile;

nomount–>mount读取控制文件

当控制文件缺失，可以正常进入nomount状态，但是无法进入mount状态。

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.

[root@oracle oltp]# pwd
/u01/app/oracle/oradata/oltp

[root@oracle oltp]# mv control01.ctl /root/datafile/.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             939526256 bytes
Database Buffers          654311424 bytes
Redo Buffers                7360512 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


[root@oracle oltp]# mv /root/datafile/control01.ctl  .

SQL> alter database mount;

Database altered.


怎么知道去哪里读控制文件？
[root@oracle oltp]# cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoltp.ora
C"▒▒▒▒ϒECC"M}oltp.__db_cache_size=654311424
oltp.__java_pool_size=16777216
oltp.__large_pool_size=16777216
oltp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oltp.__pga_aggregate_target=654311424
oltp.__sga_target=956301312
oltp.__shared_io_pool_size=0
oltp.__shared_pool_size=251658240
oltp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oltp/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oltp/control01.ctl','/u01/appCC"*g/oracle/flash_recovery_area/oltp/control02.ctl'

mount–>open

open状态的意义是什么？

SQL> create table HCIE123 (id number);
create table HCIE123 (id number)
*
ERROR at line 1:
ORA-01109: database not open


SQL> select * from HCIE;
select * from HCIE
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 oltp
oracle
11.2.0.1.0        25-APR-24 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

数据库没有open，那么就不能创建、查询、插入数据库，除了数据库的状态查询、SCN查询等
数据文件损坏也无法启动数据库

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             939526256 bytes
Database Buffers          654311424 bytes
Redo Buffers                7360512 bytes
Database mounted.

[root@oracle oltp]# mv users01.dbf /root/datafile/

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/oltp/users01.dbf'

[root@oracle oltp]# mv /root/datafile/users01.dbf .

SQL> alter database open;

Database altered.

安装过程tips

SGA&PGA

参考表与表空间与.dbf文件章节

日志文件&日志小组

注意有日志小组和小组成员的概念。小组与小组之间为轮换的关系，小组内的成员为镜像关系。
默认会有3个小组（如下图左侧树形图），日志会先写第一个小组，当第一个小组的数据写满后，开始归档文件，新的日志写入第二个小组，第二个小组写满，归档，切换至第三个小组，小组三归档，切换至小组一……
小组内的成员（如下图右侧）可以自行添加并关联数据的位置。
为了防止存放日志文件的物理设备挂掉，可以结合日志小组和小组成员隔离放置，例如有LUN1~~3，LUN1放小组1~~3的第一个成员，LUN2放小组1~~3的第二个成员，LUN3放小组1~~3的第三个成员

闪回区

Flash Recovery Area，FRA区，闪回区，备份数据的地方以及日志归档的地方
开启归档模式，redo日志写满之后会将日志归档，原redo内容清楚
不开启归档模式，redo日志写满之后会将日志内容直接覆盖，例如写满了，从日志最开头开始覆盖

Oracle数据库组件

组件	功能
database	数据存取、计算、事务处理
RAC	高可用性集群
ASM	集群卷管理、只能由数据库使用的简单文件系统
ACFS	可挂载的集群文件系统
RMAN	备份恢复
dataguard	远程复制

软件包	包含组件
database	database、RMAN、dataguard
grid	RAC、ASM、ACFS

基本使用

使用Oracle用户登录Linux虚拟机，sqlplus / as sysdba进入oracle环境

tips：当进入数据库后，使用删除键时回显“^H”，可以回退至Linux命令行执行stty erase ^H（临时）
查询数据库当前状态：select status from v$instance;
如果状态报错，lsnrctl statusLinux系统下查看数据库是否开启了监听，没有的话使用lsnrctl start打开监听后，进入数据库startup;开启数据库
查表：desc [table_name];
创建表：create table 数据库表名 (字段1 字段类型1，字段2 字段类型2，……)
插入一条数据到表中：insert into [table_name] values(data1,data2,data3,……);

SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size             939526256 bytes
Database Buffers          654311424 bytes
Redo Buffers                7360512 bytes
Database mounted.
Database opened.
SQL> create table HCIE(name varchar(30),id number(11),attend_date date);

Table created.

SQL> insert into HCIE values('gaozhikang','15871420440',to_date('2024-04-25','YYYY-MM-DD'));

1 row created.

SQL> select * from HCIE;

NAME                                   ID ATTEND_DA
------------------------------ ---------- ---------
gaozhikang                     1.5871E+10 25-APR-24

SQL> insert into HCIE values('gzk','15871420440',to_date('2024-04-25','YYYY-MM-DD'));

1 row created.

SQL>  select * from HCIE;

NAME                                   ID ATTEND_DA
------------------------------ ---------- ---------
gaozhikang                     1.5871E+10 25-APR-24
gzk                            1.5871E+10 25-APR-24

SQL> select name from HCIE;

NAME
------------------------------
gaozhikang
gzk

SQL> select name from HCIE where ID='15871420440';

NAME
------------------------------
gaozhikang
gzk


SQL> select id from HCIE where name='gaozhikang';

        ID
----------
1.5871E+10

SQL>

表与表空间与.dbf文件

表空间是oracle数据逻辑存储空间
表→表空间→文件→硬盘

Q：上图中各组件的关系？
A：从下往上看，可以把“块”和“分区”看作底层存储，数据段是一条条的数据，组成了“表”，多个表存放在表空间里，表空间关联“.dbf”文件，“.dbf”文件存放在/u01/app/oracle/oradata/实例名/下

查看现有表属于哪个表空间：select table_name,tablespace_name from user_tables where table_name='HCIE';
默认不同的用户会分配不一样的表空间
创建表空间并关联新的dbf文件：create tablespace jiggly datafile '/u01/app/oracle/oradata/oltp/jiggly.dbf' size 5M;
为表空间扩展数据文件：alter tablespace jiggly datafile '/u01/app/oracle/oradata/oltp/jiggly02.dbf' size 5M;
带dbf文件一起删除表空间：drop tablespace jiggly including contents and datafiles;
查询所有的表空间状态：select tablespace_name,status from dba_tablespaces;
查询表空间的所有的用户状态：select username from dba_users;
创建新的用户：create user [user_name] identified by [password];
新建用户后需要授权才可以连接/登录等，授权：grant connect,resource,dba to [user_name];
新建用户默认存在于“user”的表空间，更改其到指定表空间下：alter user [user_name] default tablespace [tablespace_name];

查询当前用户属于哪个表空间：select username,defalut_tablespace from user_users;

SQL> create tablespace gzk_ts datafile '/u01/app/oracle/oradata/oltp/daxiong.dbf' size 10M autoextend on;

Tablespace created.

SQL> drop table HCIE;

Table dropped.

SQL> create table HCIE(name varchar(30),id number(11),attend_date date) tablespace gzk_ts;

Table created.

SQL> insert into HCIE values('gaozhikang','15871420440',to_date('2024-04-25','YYYY-MM-DD'));

1 row created.

SQL> insert into HCIE values('gzk','15871420440',to_date('2024-04-25','YYYY-MM-DD'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from HCIE;

NAME                                   ID ATTEND_DA
------------------------------ ---------- ---------
gaozhikang                     1.5871E+10 25-APR-24
gzk                            1.5871E+10 25-APR-24

SQL> create table  HCIE1(name varchar(30),id number(11),attend_date date) tablespace gzk_ts;

Table created.

SQL> insert into HCIE1 values('gaozhikang','15871420440',to_date('2024-04-25','YYYY-MM-DD'));

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace gzk_ts add datafile '/u01/app/oracle/oradata/oltp/daxiong01.dbf' size 10M autoextend on;

Tablespace altered.

多个文件可以组成一个表空间
一个表空间可以有多张表
为了增加性能，多个划多个lun，放多个datafile，组成一个表空间。

1
2

create tablespace gzk_ts datafile '/u01/app/oracle/oradata/oltp/daxiong.dbf' size 10M autoextend on;
alter tablespace gzk_ts add datafile '/u01/app/oracle/oradata/oltp1/daxiong01.dbf' size 10M autoextend on;

oltp目录属于lun1，oltp1目录属于lun2。
控制文件会记录datafile和表空间以及表的对应情况。

用户可以和表空间关联，通过用户登录到数据库的时候，那就可以只修改查看某个表空间

SQL> create user xiaokang identified by Huawei$123 default tablespace gzk_ts;

User created.

SQL> select * from user_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR
----------- ---------- ------------ ---------- --------- --------- --------- ---
EXTENT_MAN ALLOCATIO SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR
---------- --------- ------ -------- ----------- --- ------- --- ------------
SYSTEM                               8192          65536                       1
 2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO
LOCAL      SYSTEM    MANUAL DISABLED NOT APPLY   NO  HOST    NO

SYSAUX                               8192          65536                       1
 2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO
LOCAL      SYSTEM    AUTO   DISABLED NOT APPLY   NO  HOST    NO

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR
----------- ---------- ------------ ---------- --------- --------- --------- ---
EXTENT_MAN ALLOCATIO SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR
---------- --------- ------ -------- ----------- --- ------- --- ------------

UNDOTBS1                             8192          65536                       1
 2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO
LOCAL      SYSTEM    MANUAL DISABLED NOGUARANTEE NO  HOST    NO

TEMP                                 8192        1048576     1048576           1
            2147483645            0    1048576 ONLINE    TEMPORARY NOLOGGING NO

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR
----------- ---------- ------------ ---------- --------- --------- --------- ---
EXTENT_MAN ALLOCATIO SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR
---------- --------- ------ -------- ----------- --- ------- --- ------------
LOCAL      UNIFORM   MANUAL DISABLED NOT APPLY   NO  HOST    NO

USERS                                8192          65536                       1
 2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO
LOCAL      SYSTEM    AUTO   DISABLED NOT APPLY   NO  HOST    NO

GZK_TS                               8192          65536                       1

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR
----------- ---------- ------------ ---------- --------- --------- --------- ---
EXTENT_MAN ALLOCATIO SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR
---------- --------- ------ -------- ----------- --- ------- --- ------------
 2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO
LOCAL      SYSTEM    AUTO   DISABLED NOT APPLY   NO  HOST    NO


6 rows selected.

SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
XIAOKANG
OUTLN
MGMT_VIEW
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
DBSNMP
WMSYS

USERNAME
------------------------------
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA

USERNAME
------------------------------
OLAPSYS
SCOTT
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

31 rows selected.

RMAN备份

Oracle自带的备份功能
记录DBID
oracle用户下，进入RMAN：rman target /

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 25 20:37:19 2024

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OLTP (DBID=1747046616)

RMAN下备份命令：RMAN> backup database;
可以看到命令回车后，备份了数据文件、控制问价和参数文件。但是参考下图，仅仅只是备份了这三个文件，倘若有新的数据没有落到数据文件中，只是在缓存里，并且日志文件有记录下来，但是又没有备份日志文件，这会儿如果数据库宕了，新数据是恢复不了的。

RMAN> backup archivelog #归档日志
RMAN> backup controlfilecopy
RMAN> backup datafile #数据文件
RMAN> backup spfile #参数文件
RMAN> backup tablespace #表空间
RMAN> backup database #数据库，包含了数据文件，参数文件，控制文件，日志文件可选


RMAN> backup database;

Starting backup at 25-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1  ###分配备份的通道，
channel ORA_DISK_1: SID=19 device type=DISK #备份到硬盘上，还可以备份磁带库等
channel ORA_DISK_1: starting full datafile backup set #开始全备
channel ORA_DISK_1: specifying datafile(s) in backup set #指定备份数据文件
input datafile file number=00001 name=/u01/app/oracle/oradata/oltp/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oltp/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oltp/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oltp/daxiong1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oltp/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-24  #生成一个备份片
channel ORA_DISK_1: finished piece 1 at 25-APR-24  #完成片的打包
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_25/o1_mf_nnndf_TAG20240425T204152_m2nmwj4x_.bkp tag=TAG20240425T204152 comment=NONE #把备份片放到了那里去？
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 #备份完成，花了多长时间
channel ORA_DISK_1: starting full datafile backup set  #备份参数文件和控制文件
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 25-APR-24
channel ORA_DISK_1: finished piece 1 at 25-APR-24
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_25/o1_mf_ncsnf_TAG20240425T204152_m2nmx065_.bkp tag=TAG20240425T204152 comment=NONE#把备份片放到了那里去
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-24



备份的时候默认参数
RMAN> show all;

RMAN configuration parameters for database with db_unique_name OLTP are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default   #默认用磁盘备份
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_oltp.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


RMAN> list backup;

那如何让整个数据库完全被备份下来呢？
对四大文件都备份：RMAN> backup database plus archivelog;

为什么备份过程中会备份2次归档日志呢？
在备份的过程中，可能会有新数据的写入，所以为保证数据的完整性，最后又执行了一遍。
备份文件存放在：/u01/app/oracle/flash_recovery_area/OLTP/backupset/下
查看备份：RMAN> list backup;
恢复备份：restore datafile [num];
数据文件关联数据库：recove datafile [num];

破坏数据文件恢复

创建表空间和表文件

SQL> create tablespace gzk_ts datafile '/u01/app/oracle/oradata/oltp/daxiong.dbf' size 10M autoextend on;

Tablespace created.

SQL> create table HCIE (NAME varchar(20),tel number) tablespace gzk_ts;

Table created.

SQL> insert into HCIE values ('gaozhikang','15871420440');

SQL> commit;

破坏数据文件

备份数据和日志

1	`rman target /`

RMAN> backup database plus archivelog;

Starting backup at 24-APR-24
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1167150402
input archived log thread=1 sequence=4 RECID=2 STAMP=1167151059
channel ORA_DISK_1: starting piece 1 at 24-APR-24
channel ORA_DISK_1: finished piece 1 at 24-APR-24
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_annnn_TAG20240424T163739_m2kk6mhj_.bkp tag=TAG20240424T163739 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-24

Starting backup at 24-APR-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oltp/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oltp/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oltp/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oltp/daxiong.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oltp/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-APR-24
channel ORA_DISK_1: finished piece 1 at 24-APR-24
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp tag=TAG20240424T163740 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-APR-24
channel ORA_DISK_1: finished piece 1 at 24-APR-24
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_ncsnf_TAG20240424T163740_m2kk7gxp_.bkp tag=TAG20240424T163740 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-24

Starting backup at 24-APR-24
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=3 STAMP=1167151087
channel ORA_DISK_1: starting piece 1 at 24-APR-24
channel ORA_DISK_1: finished piece 1 at 24-APR-24
piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_annnn_TAG20240424T163807_m2kk7j0h_.bkp tag=TAG20240424T163807 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-24

查看备份集

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       3.90M      DISK        00:00:00     24-APR-24
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20240424T163739
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_annnn_TAG20240424T163739_m2kk6mhj_.bkp

  List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    3       956077     22-APR-24 978175     24-APR-24
  1    4       978175     24-APR-24 979441     24-APR-24

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    946.37M    DISK        00:00:17     24-APR-24
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20240424T163740
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 979450     24-APR-24 /u01/app/oracle/oradata/oltp/system01.dbf
  2       Full 979450     24-APR-24 /u01/app/oracle/oradata/oltp/sysaux01.dbf
  3       Full 979450     24-APR-24 /u01/app/oracle/oradata/oltp/undotbs01.dbf
  4       Full 979450     24-APR-24 /u01/app/oracle/oradata/oltp/users01.dbf
  5       Full 979450     24-APR-24 /u01/app/oracle/oradata/oltp/daxiong.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:02     24-APR-24
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20240424T163740
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_ncsnf_TAG20240424T163740_m2kk7gxp_.bkp
  SPFILE Included: Modification time: 24-APR-24
  SPFILE db_unique_name: OLTP
  Control File Included: Ckp SCN: 979458       Ckp time: 24-APR-24

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4       16.50K     DISK        00:00:00     24-APR-24
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20240424T163807
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_annnn_TAG20240424T163807_m2kk7j0h_.bkp

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       979441     24-APR-24 979463     24-APR-24

破坏

1
2
3

[root@oracle oltp]# ls
control01.ctl  daxiong.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[root@oracle oltp]# rm -f daxiong.dbf

插入数据（演示日志的作用）

SQL> insert into HCIE values ('gzk','158');

1 row created.

SQL> commit;

Commit complete.
SQL> select * from HCIE;

NAME                        TEL
-------------------- ----------
gaozhikang           1.5871E+10
gzk                         158
SQL> shutdown;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/oltp/daxiong.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 oltp
oracle
11.2.0.1.0        24-APR-24 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> shutdown abort;
ORACLE instance shut down.
SQL> select * from v$instance;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 3826
Session ID: 96 Serial number: 15

恢复数据文件（这里有可能可以启动，因为数据文件offline）

SQL> startup
ORACLE instance started.

Total System Global Area 6680915968 bytes
Fixed Size                  2213936 bytes
Variable Size            3556771792 bytes
Database Buffers         3087007744 bytes
Redo Buffers               34922496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/oltp/daxiong.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 24 16:46:54 2024

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OLTP (DBID=1746859480, not open)

RMAN> restore datafile 5;

Starting restore at 24-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oltp/daxiong.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp tag=TAG20240424T163740
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-APR-24

打开数据库，发现还是不能查询，应该是没有recover

RMAN> exit


Recovery Manager complete.
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 24 16:47:33 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/oltp/daxiong.dbf'

进行recover

关闭数据库再到mount下

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 24 16:48:22 2024

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OLTP (DBID=1746859480, not open)

RMAN> recover datafile 5;

Starting recover at 24-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-APR-24

RMAN> exit


Recovery Manager complete.
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 24 16:48:42 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

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

recover发现还是不能读取，可能是因为数据文件被offine（有可能不会出现）

进行online（有可能不会出现）

不完整恢复

破坏数据文件

1
2
3

[root@oracle oltp]# ls
control01.ctl  daxiong.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[root@oracle oltp]# rm -f daxiong.dbf

插入数据

SQL> insert into HCIE values('test1','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from HCIE;

NAME                        TEL
-------------------- ----------
gaozhikang           1.5871E+10
gzk                         158
test1                       123

关闭数据库尝试启动

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6680915968 bytes
Fixed Size                  2213936 bytes
Variable Size            3556771792 bytes
Database Buffers         3087007744 bytes
Redo Buffers               34922496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/oltp/daxiong.dbf'

恢复数据文件

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 24 16:56:31 2024

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OLTP (DBID=1746859480, not open)

RMAN> restore datafile 5;

Starting restore at 24-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oltp/daxiong.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/OLTP/backupset/2024_04_24/o1_mf_nnndf_TAG20240424T163740_m2kk6nn8_.bkp tag=TAG20240424T163740
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-APR-24

select current_scn from v$database;
CONFIGURE CONTROLFILE AUTOBACKUP on;

note

#oracle

【笔记】Linux LVM使用和迁移上一篇

【笔记】修复Linux引导文件下一篇

【笔记】oracle基础

oracle4大文件

参数文件

控制文件

数据文件

日志文件

数据库结构

share-everything

share-disk

share-nothing

oracle4种模式

shutdown–>nomount读取参数文件

nomount–>mount读取控制文件

mount–>open

安装过程tips

SGA&PGA

日志文件&日志小组

闪回区

Oracle数据库组件

基本使用

表与表空间与.dbf文件

RMAN备份

破坏数据文件恢复

创建表空间和表文件

破坏数据文件

备份数据和日志

查看备份集

破坏

插入数据（演示日志的作用）

恢复数据文件（这里有可能可以启动，因为数据文件offline）

打开数据库，发现还是不能查询，应该是没有recover

进行recover

recover发现还是不能读取，可能是因为数据文件被offine（有可能不会出现）

进行online（有可能不会出现）

不完整恢复

破坏数据文件

插入数据

关闭数据库尝试启动

恢复数据文件