实用数据库实践笔记

版本说明

Oracle Database: 19c Enterprise Edition Release 19.0.0.0.0 - Production
mysql: 5.7.30-linux-glibc2.12-x86_64
rlwrap: 0.36
sqldeveloper: 19.2.1.247.2212
linux: OracleLinux-R7-U4-Server-x86_64

Oracle数据库操作笔记

显示

查看
- show pagesize
- show linesize
设置
- set pagesize 25
- set linesize 120
- alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
修改默认设置

vim $ORACLE_HOME/sqlplus/admin/glogin.sql

set linesize 120
set pagesize 25
set sqlprompt _user"@"_connect_identifier>
define _EDITOR="vim"

SQL

QL(select)
DML(insert,update,deIete,merge)
DDL(create,alter,drop,rename,truncate,comment)
DCL(grant,revoke)
- grant select on emp to hr
- revoke select on emp from hr
TCL(commit,rollback,savepoint)

create database wyd
datafile '/u01/app/eu/oradata/wyd/system01.dbf' size 400m
sysaux datafile '/u01/app/eu/oradata/wyd/sysaux01.dbf' size 400m
default tablespace users datafile '/u01/app/eu/oradata/wyd/users01.dbf' size 100m
default temporary tablespace temp tempfile '/u01/app/eu/oradata/wyd/temp01.dbf' size 20m
undo tablespace undotbs1 datafile '/u01/app/eu/oradata/wyd/undotbs01.dbf' size 50m
logfile
group 1 '/u01/app/eu/oradata/wyd/redo01.log' size 10m,
group 2 '/u01/app/eu/oradata/wyd/redo02.log' size 10m,
group 3 '/u01/app/eu/oradata/wyd/redo03.log' size 10m;

Database created

导出

exp scott/tiger query=\'where sal\>2500\' tables=emp file=emp2500.dmp
expdp scott/tiger directory=expdp_dest file=empdp1.dmp tables=emp
参考15 Oracle Exp Command Examples to Export Database Objects

表空间

查看
- select tablespace_name,status from dba_tablespaces;
- select name from v$tablespace;
创建表空间create tablespace eu datafile '/u01/app/oracle/oradata/DB19C/eu.dbf' size 10m;
创建一张表到特定的表空间：create table scott.tts(a varchar (30)) tablespace eu;
修改为只读alter tablespace test read only;
从源数据库输出元数据：exp \'sys\/admin1#3 as sysdba\' FILE=tts.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=eu
查找表所对应的文件：select file_name from dba_data_files where tablespace_name=(select tablespace_name from dba_tables where table_name='COLD');

数据文件

查看select name from v$datafile;
把元数据输入到目标数据库：imp \'sys\/admin1#3 as sysdba\' FILE=tts.dmp TRANSPORT_TABLESPACE=Y DATAFILES=/u01/app/eu/oradata/eu01.dbf

字符集

参考：[Oracle] oracle修改字符集 | zeven’s blog
查看使用的字符集：select value$ FROM sys.props$ WHERE name='NLS_CHARACTERSET';
修改：

alter system enable restricted session;
alter database character set internal_use AL32UTF8; #US7ASCII
alter system disable restricted session;

外部表

创建外部表

#
create table scott.ex1(empno int,deptno int,ename varchar(20))
ORGANIZATION EXTERNAL
(type oracle_loader default directory expdp_dest
access parameters
(records delimited by newline
fields terminated by ','
missing field values are null
(empno,deptno,ename))
location('employees.dat'));

卸载数据

create table scott.ex2(empno,ename,sal)
ORGANIZATION EXTERNAL
(type oracle_datapump default directory expdp_dest
location('empl .dat'))
as select empno,ename,sal from scott.emp;

加载数据

create table scott.ex(empno int,ename varchar(20),sal number(7,2))
ORGANIZATION EXTERNAL
(type oracle_datapump default directory expdp_dest
location('empl .dat'));

logmnr

archive log list;
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
启用数据库补充日志：alter database add SUPPLEMENTAL LOG DATA;
产生数据字典文件

mkdir -p /u01/demo/dict
create directory DICT as '/u01/demo/dict';
EXECUTE dbms_logmnr_d.build('v816dict.ora','DICT');

找到需要分析的日志文件

select GROUP#,STATUS from  v$log; #看哪个是current
select member from v$logfile where GROUP#=3;

添加需要分析的日志文件 EXECUTE dbms_logmnr.add_logfile(logfilename=>'/u01/app/eu/oradata/DB19C/redo03.log',Options=>dbms_logmnr.new);
启动分析 EXECUTE dbms_logmnr.start_logmnr(DictFilename=>'/u01/demo/dict/v816dict.ora');
查询 desc v$logmnr_contents;
闪回: flashback table emp to timestamp to timestamp('2024-05-21 10:02:23'),'yyyy-mm-dd hh24:mi:ss'
行移动：
- select table_name,row_movement from user_tables;
- alter table emp enable row movement;

归档模式

查看:archive log list;
要将Oracle数据库设置为归档模式，需要执行以下步骤：

关闭数据库：

shutdown immediate;

以挂载状态启动数据库：

startup mount;

将数据库更改为归档模式：

alter database archivelog;

打开数据库：

alter database open;

备份和恢复

recover database;
alter database open;

rman

基础使用

rman
connect target /
backup tablespace users;
restore tablespace users;

advice
repair
全盘备份和恢复
- configure channel device type disk format '/u01/eubak/%d_%u_%T';
- CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/eubak/auto/%F';
- backup database plus archivelog;
- vim /etc/oratab
- restore spfile from '';
- restore controlfile from '';
- alter database mount;
- restore database;

在原有数据库的基础上创建数据库

SYS> create pfile from spfile;
>cd $ORACLE_HOME/dbs
>cp initwyd.ora initoradup.ora
> orapwd file=orapworadup password=admin1#3 #创建口令文件
SYS>select name from v$datafile; #查找数据文件
#/u01/app/eu/oradata/wyd/system01.dbf
#/u01/app/eu/oradata/wyd/sysaux01.dbf
#/u01/app/eu/oradata/wyd/undotbs01.dbf
#/u01/app/eu/oradata/wyd/users01.dbf
#/u01/app/eu/oradata/wyd/wyd01.dbf
SYS>select member from v$logfile; #查找日志文件
#/u01/app/eu/oradata/wyd/redo01.log
#/u01/app/eu/oradata/wyd/redo02.log
#/u01/app/eu/oradata/wyd/redo03.log
>vim initoradup.ora
: 1,$ s/wyd/oradup/g
# 根据刚才查找到的文件位置进行补充
*.DB_FILE_NAME_CONVERT=('/u01/app/eu/oradata/wyd/','/u01/app/eu/oradata/oradup/')
*.LOG_FILE_NAME_CONVERT=('/u01/app/eu/oradata/wyd/','/u01/app/eu/oradata/oradup/')
*.log_archive_dest_1='location=/u01/arch/oradup'
> netmgr #监听,需配置
>lsnrctl stop
>lsnrctl start # 重启
# 讲oradup启动到nomount状态
>export ORACLE_SID=oradup 
>sqlplus / as sysdba
SYS> startup nomount
# 启动rman，同时连接原库和新库
>rman target sys/admin1#3@wyd auxiliary sys/admin1#3@oradup
# 执行duplicate
rman> duplicate target database to oradup from active database;

恢复

数据库基于时间点的恢复

#克隆数据库作为辅助数据库，然后推到特定时间
#基于时间点的恢复
#1. 创建测试表
create table scott.suibian(id int);
insert into scott.suibian values(100);
commit;
alter system archive log current; #归档模式
#整库备份
在RMAN中backup databse;
# 再插入一条记录
insert into scott.suibian values(200);
commit;
#查看时间
!date
#删除表
drop table scott.suibian purge;
#创建表
create table scott.suibian2 as select * from scott.emp;
#启动数据库到mount阶段

#设置环境变量
export NLS_DATE_FORMAT = "yyyy-mm-dd hh24:mi:ss"

#还原数据库
RMAN下 restore database;
#恢复数据库到指定时间点
RMAN下 RECOVER DATABASE UNTIL TIME "TO_DATE('2024-06-04 10:26:40', 'YYYY-MM-DD HH24:MI:SS')";
#打开数据库
alter database open resetlogs;

TSPITR —表空间基于时间点的恢复

#1. 创建测试表空间
create tablespace tbs1 datafile '/u01/app/oracle/oradata/sales/tbs101.dbf' size 10m;
create tablespace tbs2 datafile '/u01/app/oracle/oradata/sales/tbs201.dbf' size 10m;

#2. 创建测试表
create table scott.t1 tablespace tbs1 as select * from scott.emp;
create index scott.idx_ename on scott.t1(ename) tablespace tbs2;
alter system checkpoint;

#3. 备份数据库
RMAN>backup database plus archivelog;
RMAN>list backup;

#4.误操作
select systimestamp from dual;   (28-MAY-24 09.55.49)
truncate table scott.t1;

#5.之后再创建一张表
create table scott.t2 tablespace users as select * from scott.dept;
create table scott.t3 tablespace tbs1 as select * from scott.emp;
select * from scott.t1;
select * from scott.t2;
select * from scott.t3;

#6.验证表空间的依赖性
conn / as sysdba
execute DBMS_TTS.TRANSPORT_SET_CHECK('TBS1', TRUE,TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
execute DBMS_TTS.TRANSPORT_SET_CHECK('TBS1,TBS2', TRUE,TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

#可以看到，如果只恢复表空间TBS1，会有T1表的索引依赖表空间TBS2。
#我们这里同时恢复表空间TBS1,TBS2，这样就解决了依赖关系。

#7.确定执行TSPITR后会丢失的对象
select owner,name,tablespace_name,to_char(creation_time,'yyyy-mm-dd hh24:mi:ss') creation_time
from TS_PITR_OBJECTS_TO_BE_DROPPED
where tablespace_name in ('TBS1','TBS2')
and creation_time>to_date('2024-05-28 09:55:49','yyyy-mm-dd hh24:mi:ss');

# 这里没有查出结果，如果有结果，最好先expdp导出这些对象的备份，待恢复表空间后，再导入这些对象。
# 当然如果确定这些对象是没有用的，可以直接忽略。

# 8.执行TSPITR
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
RMAN>recover tablespace TBS1,TBS2 until time '2024-05-28 09:55:49' auxiliary destination '/u01/aux';

# 9.检查
select tablespace_name,status from dba_tablespaces;
alter tablespace tbs1 online;
alter tablespace tbs2 online;
select * from scott.t1;
select * from scott.t2;
select * from scott.t3;

imp 
# 10.删除测试表空间和表
drop tablespace tbs1 including contents and datafiles;
drop tablespace tbs2 including contents and datafiles;
drop table scott.t2 purge;
drop table scott.t3 purge;

闪回

闪回数据库

前置条件
- 归档模式
- 启用闪回
  - 查看是否启用:select FLASHBACK_ON from v$database;
  - 启用alter database FLASHBACK ON;
相关参数
- show parameter db_recovery_file_dest
- show parameter db_recovery_file_dest_size
- show parameter db_flashback_retention_target
select OLDEST_FLASHBACK_TIME from v$flashback_database_log;
查看scn： select current_scn from v$database;
闪回需要在mount阶段执行
- startup mount;
- flashback database to scn 666;
- alter database open RESETLOGS;

闪回表

前提：激活表的行移动特性
- select table name.ROW MOVEMENT from user tables;
- 激活 alter table emp enable ROW MOVEMENT;
flashback table scott.emp to timestamp to_timestamp('2024-05-28 11:31:05','yyyy-mm-dd hh24:mi:ss');

闪回删除

前提：启用回收站：show parameter recyclebin;
flashback table <table_name> to before drop;

闪回查询

时间点
- select * from emp as of timestamp to_timestamp ('2024-05-28 11:31:22','yyyy-mm-dd hh24:mi:ss') where deptno=30;
- insert into emp select * from emp as of timestamp to_timestamp ('2024-05-28 11:31:22','yyyy-mm-dd hh24:mi:ss') where deptno=30;
版本
- select sal from emp versions between scn minvalue and maxvalue where empno=1234;

闪回事务查询

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
alter database add SUPPLEMENTAL LOG DATA;
select versions_xid,empno,ename,versions_operation from my_emp versions between scn minvalue and maxvalue; #050011003E020000
select undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=‘050011003E020000’;

闪回事务

select distinct xid,commit_scn
  from flashback_transaction_query
  where table_owner='HR'and 
  table_name='EMPLOYEES' and  
  commit_timestamp > systimestamp - interval '10' minute
  order by commit_scn;
# 得到
XID              COMMIT_SCN
---------------- ----------
07001400DA030000    2611571
0100070003040000    2611623

declare
  xids sys.xid_array;
  begin
    xids := sys.xid_array('07001400DA030000');
    dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
  end;
/

最终实验

mkdir -p /u01/app/eu/admin/seuwyd29/adump
mkdir -p /u01/app/eu/fast_recovery_area/seuwyd29
mkdir -p /u01/app/seuwyd29
mkdir -p /u01/app/eu/oradata/seuwyd29

export ORACLE_SID=seuwyd29
sqlplus / as sysdba
SYS@seuwyd29>startup nomount

create database seuwyd29
  datafile '/u01/app/eu/oradata/seuwyd29/system01.dbf' size 400m
  sysaux datafile '/u01/app/eu/oradata/seuwyd29/sysaux01.dbf' size 400m
  default tablespace users datafile '/u01/app/eu/oradata/seuwyd29/users01.dbf' size 100m
  default temporary tablespace temp tempfile '/u01/app/eu/oradata/seuwyd29/temp01.dbf' size 20m
  undo tablespace undotbs1 datafile '/u01/app/eu/oradata/seuwyd29/undotbs01.dbf' size 50m
  logfile
  group 1 '/u01/app/eu/oradata/seuwyd29/redo01.log' size 10m,
  group 2 '/u01/app/eu/oradata/seuwyd29/redo02.log' size 10m,
  group 3 '/u01/app/eu/oradata/seuwyd29/redo03.log' size 10m;

SELECT LAST_NAME, YEARS, MONTHS
FROM (
    SELECT 
        LAST_NAME,
        FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) AS YEARS,
        TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIRE_DATE), 12)) AS MONTHS,
        ROW_NUMBER() OVER (ORDER BY FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) DESC, 
                           TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIRE_DATE), 12)) DESC) AS RANK
    FROM 
        hr.employees
)
WHERE RANK BETWEEN 6 AND 20;

#logmnr 
mkdir -p /u01/demo/eudict
create directory EUDICT as '/u01/demo/eudict';
EXECUTE dbms_logmnr_d.build('v816dict.ora','EUDICT');
EXECUTE dbms_logmnr.add_logfile(logfilename=>'/u01/app/eu/oradata/seuwyd29/redo02.log',Options=>dbms_logmnr.new);
EXECUTE dbms_logmnr.start_logmnr(DictFilename=>'/u01/demo/eudict/v816dict.ora');

# 闪回事务
create table scott.my_emp as select * from scott.emp;
delete from scott.my_emp where empno=7900;
delete from scott.my_emp where empno=7844;
commit;

实用数据库实践笔记

版本说明

Oracle数据库操作笔记

登录

显示

SQL

目录

导出

表空间

数据文件

字符集

外部表

logmnr

归档模式

备份和恢复

rman

在原有数据库的基础上创建数据库

恢复

数据库基于时间点的恢复

TSPITR —表空间基于时间点的恢复

闪回

闪回数据库

闪回表

闪回删除

闪回查询

闪回事务查询

闪回事务

最终实验

参考

版本说明¶

Oracle数据库操作笔记¶

登录¶

显示¶

SQL¶

目录¶

导出¶

表空间¶

数据文件¶

字符集¶

外部表¶

logmnr¶

归档模式¶

备份和恢复¶

rman¶

在原有数据库的基础上创建数据库¶

恢复¶

数据库基于时间点的恢复¶

TSPITR —表空间基于时间点的恢复¶

闪回¶

闪回数据库¶

闪回表¶

闪回删除¶

闪回查询¶

闪回事务查询¶

闪回事务¶

最终实验¶

参考¶

版本说明

Oracle数据库操作笔记

登录

显示

SQL

目录

导出

表空间

数据文件

字符集

外部表

logmnr

归档模式

备份和恢复

rman

在原有数据库的基础上创建数据库

恢复

数据库基于时间点的恢复

TSPITR —表空间基于时间点的恢复

闪回

闪回数据库

闪回表

闪回删除

闪回查询

闪回事务查询

闪回事务

最终实验

参考