1.1物理备份还原

1.全库备份

backup database backupset '/home/dmdba/dmdbms/dmbak';

2.指定时间还原

restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/dmbak/'
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/dmarch' until time '2022-11-07 20:43:00'
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic;

2.基于归档还原，归档目录为/data/dmarch

restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/dmbak/';
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/dmarch';
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic;

1.2 逻辑备份还原

1.验证是否支持跨模式导入

./dexp SYSDBA/SYSDBA SCHEMAS=DMHR FILE=/home/dmdba/exp.dmp
将dmp文件导入模式test
./dimp SYSDBA/SYSDBA REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE FILE=/home/dmdba/exp.dmp

2.导入部分表

./dimp SYSDBA/SYSDBA FILE=/home/dmdba/exp.dmp tables=DMHR.CITY, DMHR.JOB REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE

3.验证是否支持指定时间范围的数据通过DMP迁移

./dexp SYSDBA/SYSDBA tables=DMHR.EMPLOYEE QUERY="where HIRE_DATE> '2010-11-27'" FILE=/home/dmdba/exp.dmp
导入数据
/dimp SYSDBA/SYSDBA tables=DMHR.EMPLOYEE REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE FILE=/home/dmdba/exp.dmp

2.性能监控常用的sql

2.1 慢sql的执行时间查询

SELECT
SESS_ID ,
EXEC_TIME ,
FINISH_TIME,
N_RUNS ,
TRX_ID ,
substr(sql_text,0,20)
FROM
V$SYSTEM_LONG_EXEC_SQLS
ORDER BY
EXEC_TIME DESC,
N_RUNS DESC;

2.2 执行会话时执行时间查询

select datediff(s, last_recv_time, sysdate) exectime,
dbms_lob.SUBSTR(sf_get_session_sql(sess_id)) sql_txt,
CUR_SQLSTR,
sess_id,
state,
user_name,
clnt_ip,
clnt_ver
from v$sessions
where state = 'ACTIVE'
order by exectime desc

2.3 阻塞查询

with
lock_wait as(
SELECT l.addr,o.name as 阻塞对象, l.LMODE, l.trx_id, l.tid
FROM v$lock l, sysobjects o
WHERE l.table_id = o.id AND blocked = 1),
waiting as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'被阻塞的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.trx_id = b.trx_id),
waiting_for as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'待提交的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.tid = b.trx_id)
select * from waiting_for union all
select * from waiting;

2.4 查询事务等待

select * from v$trxwait;
select * from v$lock t where t.blocked =1;
select a.sess_id, a.sql_text, a.state, b.* from v$sessions a, v$trxwait b
where a.trx_id = b.id;

2.5 查询未提交事务

select b.object_name, c.sess_id, c.thrd_id, c.state, c.sql_text, a.*
from v$lock a, dba_objects b, v$sessions c
where a.table_id = b.object_id
and a.ltype = 'OBJECT'
and a.trx_id = c.trx_id
and a.ign_flag=0;