作者 | JiekeXu
来源 | JiekeXu之路（ID: JiekeXu_IT）
转载请联系授权 | (微信ID：JiekeXu_DBA)
大家好，我是 JiekeXu,分开这么久，很高兴又和大家见面了，祝大家新年快乐，牛气冲天发大财，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
本文 SQL 及相关命令均是在运维工作中总结整理而成的，对于运维 DBA 来说可提高很大的工作效率，值得收藏。当然如果你全部能够背下来那就很牛逼了，如果不能，还是建议收藏下来慢慢看，每条 SQL 的使用频率都很高，肯定能够帮助到你。
当然，由于本编辑器原因以下 SQL 可能出现格式错误不能执行，导致出错，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号后台回复【SQL大全二】 即可获取，这里也有 2020 年的精华文章汇总，如有需要可点击此处查看【精华文章】。
1、查看数据文件信息：
-
col file_name for a55
-
select tablespace_name,file_name,
bytes/
1024/
1024/
1024 gb,AUTOEXTENSIBLE
-
from dba_data_files
where tablespace_name=
'AM_DATA';
-
alter
tablespace AM_DATA
add
datafile
'+DATA'
size
30G;
2、查看 ASM 磁盘组信息：
-
su - grid
-
sqlplus / as sysasm
-
select group_number,
name,total_mb/
1024 total_gb,free_mb/
1024 free_gb,
TYPE
-
from v$asm_diskgroup;
-
---调整ASM磁盘均衡级别
-
ALTER
DISKGROUP
DATA REBALANCE
POWER
10;
-
---查看ASM磁盘均衡时间：
-
select *
from v$asm_operation;
3、查看 ASM 磁盘组磁盘的信息
-
set lin
1000 pagesize
999
-
col
PATH
for a33
-
col
NAME
for a15
-
col FAILGROUP
for a15
-
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/
1024,FREE_MB/
1024,
NAME,FAILGROUP,
PATH,FAILGROUP_TYPE
-
from v$asm_disk
where GROUP_NUMBER=
'1';
4、查看表空间大小：
-
SELECT a.tablespace_name,
round(total/
1024/
1024/
1024)
"Total g",
-
round(free/
1024/
1024/
1024)
"Free g",
ROUND((total-free)/total,
4)*
100
"USED%"
-
FROM (
SELECT tablespace_name,
SUM(
bytes) free
FROM
-
DBA_FREE_SPACE
-
GROUP
BY tablespace_name ) a,
-
(
SELECT tablespace_name,
SUM(
bytes) total
FROM DBA_DATA_FILES
-
GROUP
BY tablespace_name) b
-
WHERE a.tablespace_name=b.tablespace_name
-
ORDER
BY
4;
5、查某个表空间内的前五个大表
-
col TABLE_NAME for a30
-
set pagesize
200
-
set linesize
200
-
col TABLE_NAME
for a30
-
set linesize
200
-
select *
from (
select TABLESPACE_NAME,OWNER,SEGMENT_NAME
"TABLE_NAME",to_number(
decode(
substr(
BYTES/
1024/
1024,
1,
1),
'.',
'0'||
BYTES/
1024/
1024,
BYTES/
1024/
1024)) total_MB
-
from dba_segments
where TABLESPACE_NAME =
'&tablespacename'
and SEGMENT_TYPE=
'TABLE'
order
by total_MB
desc )
where
rownum<=
5;
-
-
-
SELECT *
FROM (
SELECT OWNER,SEGMENT_NAME,
SUM(
BYTES) /
1024 /
1024 MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME =
'SYSTEM'
GROUP
BY SEGMENT_NAME,OWNER
ORDER
BY
3
DESC)
WHERE
ROWNUM <=
5;
-
OWNER SEGMENT_NAME MB
-
------------------------------ --------------------------------------------------------------------------------- ----------
-
SYS XX_RECNAME_RB_IX 11136
-
SYS XXXXX_ORIGINNOTE_IX 9152
-
SYS XXXXX_ABSTRACT_IX 6388
-
SYS XXX_PAYDATE_NU_NC 5490
-
-
-
SELECT OWNER,SEGMENT_NAME ,segment_type
FROM DBA_SEGMENTS
WHERE segment_name
in (
'PAYMENTS_RECNAME_RB_IX',
'XXXXX_ORIGINNOTE_IX',
'XXXXX_ABSTRACT_IX',
'XXX_PAYDATE_NU_NC');
-
-
-
OWNER SEGMENT_NAME SEGMENT_TYPE
-
------------------------------ --------------------------------------------------------------------------------- ------------------
-
SYS XXX_PAYDATE_NU_NC INDEX
-
SYS XXXXX_ORIGINNOTE_IX INDEX
-
SYS XXXXX_ABSTRACT_IX INDEX
-
SYS XXXMENTS_RECNAME_RB_IX INDEX
6、查看 shared_pool 的大小
-
select
sum(
bytes)/
1024/
1024/
1024
from v$sgastat
where pool=
'shared pool';
-
--查看空闲的：
-
select *
from v$sgastat
where
name =
'free memory'
and pool =
'shared pool';
-
--统计活动的undo
-
select
sum(
bytes /(
1024*
1024*
1024))
from dba_undo_extents
where
status=
'ACTIVE';
7、查看占用内存 100k 的 sql 语句：
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem;
8、查看字符集
-
select userenv(
'language')
from dual;
-
select *
from nls_database_parameters;
9、Oracle 查询 temp 表空间的名字和位置
-
select tablespace_name,file_name
from dba_temp_files;
-
col FILE_NAME for a55
-
select TABLESPACE_NAME,FILE_NAME,
BYTES/
1024/
1024/
1024 total_gb,USER_BYTES/
1021/
1024/
1024 gb
from dba_temp_files;
-
-
-
create
temporary
tablespace temp1 tempfile
'/rhzxdata/tempdata/pbc/temp01.dbf'
size
20G
autoextend
on
maxsize
30G
-
-
-
Oracle查询 temp 表空间的使用率
-
select tablespace_name,
round(free_space/
1024/
1024/
1024,
2)
"free(GB)",
round(tablespace_size/
1024/
1024/
1024,
2)
"total(GB)",
round(nvl(free_space,
0)*
100/tablespace_size,
3)
"Free percent"
-
from dba_temp_free_space;
-
-
-
注意：Primary 端涉及到的临时表空间创建维护、临时文件创建的操作是不会传导到 standby 端的。
10、查看数据库版本
-
set line
150
-
col ACTION_TIME
for a30
-
col
ACTION
for a8
-
col NAMESPACE
for a8
-
col
VERSION
for a10
-
col BUNDLE_SERIES
for a5
-
col COMMENTS
for a20
-
select *
from dba_registry_history;
======================================================
本文 SQL 命令由于微信编辑器原因可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号【JiekeXu DBA之路】扫描最下方二维码后台回复 【SQL大全二】即可获取。
=======================================================
11、查看补丁版本：
JIEKED2:/app/product/11.2.0/db/OPatch$opatch lsinventory
12、查看锁表
-
SELECT l.session_id
sid, s.serial
#, l.locked_mode,l.oracle_username,
-
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
-
FROM v$locked_object l, all_objects o, v$
session s
-
WHERE l.object_id = o.object_id
-
AND l.session_id = s.sid
-
ORDER
BY
sid, s.serial
# ;
-
-
-
查出锁定表的
session 的
sid,
serial
#,os_user_name, machine name, terminal 和执行的语句：
-
-
-
SELECT l.session_id
sid, s.serial
#, l.locked_mode, l.oracle_username, s.user#,
-
l.os_user_name,s.machine, o.object_name,s.terminal,a.sql_text, a.action
-
FROM v$sqlarea a,v$
session s, v$locked_object l,all_objects o
-
WHERE l.session_id = s.sid
-
AND s.prev_sql_addr = a.address
-
ORDER
BY
sid, s.serial
#;
13、查看视图对应的表：
select * from dba_dependencies where NAME='视图名' and TYPE='VIEW';
14、杀会话命令
alter system kill session 'sid,serial#';
15、查看表大小
-
select TABLESPACE_NAME,OWNER,SEGMENT_NAME,
sum(
BYTES)/
1024/
1024 total_mb
-
from dba_segments
where TABLESPACE_NAME=
'NEWCAR02'
-
group
by TABLESPACE_NAME,OWNER,SEGMENT_NAME;
16、查看 GI 兼容版本
select name,compatibility,database_compatibility from v$asm_diskgroup;
17、查看aix操作系统的资源情况
-
prtconf|more
-
lparstat
-i
-
ortconf
-
AIX
磁盘扫描命令
-
root
执行
-
cfgmgr
-v
-
-
-
-
-
--查看那个进程占用磁盘空间
-
fuser
-dV /app
-
-
-
-
-
AIX
查看错误日志并输出到 messages
-
JIekeXuY1:
/#errpt -dH
-
IDENTIFIER
TIMESTAMP T C RESOURCE_NAME DESCRIPTION
-
DE3B8540
1012214419 P H hdisk18 PATH HAS FAILED
-
4B436A3D
1012214119 T H fscsi0 LINK ERROR
-
4B436A3D
1012214119 T H fscsi0 LINK ERROR
-
4B436A3D
1012214119 T H fscsi0 LINK ERROR
-
-
-
/bin/errpt
-a > messages.out
-
-
-
AIX查看LUNID信息
-
lscfg
-vpl hdisk187 输出结果里的 serial number就是存储要的lunid
-
# lscfg -vpl hdisk11
-
hdisk11 U9080.MME.680A6E8-V7-C2-T1-W500507680C25ADBA-LB000000000000 MPIO FC 2145
-
Manufacturer................IBM
-
Machine
Type and Model......2145
-
ROS
Level and ID............0000
-
Device
Specific.(Z0)........0000063268181002
-
Device
Specific.(Z1)........0203202
-
Serial Number...............600507680C808570080000000000042B
-
PLATFORM
SPECIFIC
-
-
-
Name:
disk
-
Node:
disk
-
Device Type:
block
-
--- 检查磁盘大小（单位M）
-
bootinfo -s hdisk12
-
--- 查看磁盘的详细信息
-
lsattr -El hdisk12
-
HP：machinfo
-
（diskinfo /dev/rdisk/disk* 查看磁盘大小）
-
WIN：msinfo32
-
SUSE：cat /proc/cpuinfo （model name ）
-
---查看资源使用情况：
-
HP：glance/top
-
AIX：nmon/topas
-
-
-
--AIX查看版本号
-
oslevel
-
JIEKEXuR2:/app/product/11.2.0/grid/network/log$oslevel
-
6.1.0.0
-
---查看内存大小：
-
HP：/usr/contrib/bin/machinfo | grep -i Memory
-
AIX: /usr/sbin/lsattr -E -l sys0 -a realmem
-
---查看swap分区：
-
HP：/usr/sbin/swapinfo -a
-
AIX：/usr/sbin/lsps -s
18、用户相关操作
-
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
from dba_users;
-
create
user agriproduct
identified
by product
default
tablespace
users;
-
grant conncet,
resource,
create
view
to product;
-
--解锁用户
-
set linesize
500
-
col USERNAME
for a15
-
col ACCOUNT_STATUS
for a15
-
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE
from dba_users
where USERNAME =
'SQLMON';
-
alter
user SQLMON
identified
by
"SQLMON"
account
unlock;
-
alter
user SQLMON
account
unlock;
-
alter
user SQLMON
identified
by
"SQLMON";
-
conn SQLMON/SQLMON
-
#查看用户具有的权限
-
1、查询用户有哪些角色：
-
select *
from dba_role_privs
where grantee=
'&username';
-
2、查询角色包含哪些权限：
-
select *
from role_sys_privs
where
role=
'&role';
-
3、查询用户系统权限：
-
select *
from dba_sys_privs
where grantee=
'&username';
-
select *
from dba_tab_privs
where grantee=
'&username';
-
--创建 dblink 权限
-
grant
create
database
link
to
user ;
-
grant
create
public
database
link
to
user ;
-
-
-
删除用户：
drop
user XXX
cascade;
-
解锁用户
-
alter
user muar_rb
account
unlock
identified
by &
PASSWORD;
-
-
-
查看表空间下的用户
-
select
distinct s.owner
from dba_segments s
where s.tablespace_name =
'&TBSNAME'
19、查看数据量：
select sum(bytes)/1024/1024/1024 Gb from dba_segments;
20、查看 REDOLOG 大小
select group#,members,bytes/1024/1024,status from v$log;
21、清理垃圾文件
-
cd
&DIR
-
find
./ -ctime +3 |xargs rm
-
查看大于1024M的文件
-
find
/ -type f -size +1024M -print0 | xargs -0 du -h | sort -nr
22、AIX 操作系统下 ASM 磁盘相关操作
-
--- 检查磁盘大小（单位M）
-
bootinfo -s hdisk0
-
-
-
--- 查看磁盘的详细信息
-
lsattr -El hdisk0
-
-
-
JIEKEXu:/dev#lsattr -El /dev/hdisk322
-
lsattr: 0514-519 The following device was not found in the customized
-
device configuration database:
-
/dev/hdisk322
-
JIEKEXu:/dev#su - grid
-
JIEKEXu:/home/grid$kfed read /dev/rhdisk322
-
kfbh.endian: 0 ; 0x000: 0x00
-
kfbh.hard: 130 ; 0x001: 0x82
-
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
-
kfbh.datfmt: 1 ; 0x003: 0x01
-
kfbh.block.blk: 0 ; 0x004: blk=0
-
kfbh.block.obj: 2147483953 ; 0x008: disk=305
-
kfbh.check: 3956950460 ; 0x00c: 0xebda45bc
-
kfbh.fcn.base: 2632 ; 0x010: 0x00000a48
-
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
-
-
-
--- 检查权限 hdisk是块设备，而rhdisk是字符设备。
-
ls -l /dev/hdisk*
-
【排序查看ls -ltr /dev |grep rhdisk】
-
-
-
运行命令lsattr -E -a rw_timeout -F value -l Name，查看rw_timeout的值。
-
JIEKEY1:/app/grid/diag/asm/+asm/+ASM1/trace$lsattr -E -a rw_timeout -F value -l hdisk16
-
60
-
--- 检查PVID
-
lspv | grep hdiskn
-
--- 检查保留策略
-
lsattr -E -l hdisk5 | grep reserve_policy
-
--- 查看磁盘是否为共享磁盘
-
lsattr -El hdisk0
-
比对两个主机对应的磁盘号是否一致：unique_id
-
--- 查看磁盘是否可用
-
lspv
-
看PVID是否为none，若为none则数据库可用，再查看数据库当前有没有使用，若没有则可用来扩容ASM磁盘组
23、AIX 下按用户查看占用多少内存
-
svmon
-U grid -w |more
-
svmon
-U oracle -w | more
24、AIX 下查看用户下有多少进程
-
svmon
-PO unit=GB |grep aioserver |wc
-
svmon
-PO unit=GB |grep oracle |wc
-
-
-
lsvg
|lsvg -i -p
-
lsdev
-c disk
25、查看服务器底层用的存储类型：
lscfg -vpl hdisk40
26、生成AWR报告
-
-----某个节点的 AWR
-
@?/rdbms/admin/awrrpti.sql
-
-----AWR报告
-
@?/rdbms/admin/awrrpt.sql
-
-- AWR 两个时间段的比较报告
-
@?/rdbms/admin/awrddrpt.sql
-
-----ASH报告
-
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
-
-----细粒度的 ASH （Enter value for slot_width:1）slot 槽为 1 的 ASH
-
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
-
-
-
关于physical reads ，db block gets 和 consistent gets 这三个参数之间有一个换算公式：
-
数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
-
用以下语句可以查看数据缓冲区的命中率：
-
SELECT
name,
value
FROM v$sysstat
WHERE
name
IN (
'db block gets',
'consistent gets',
'physical reads');
-
-
-
查询出来的结果 Buffer
Cache 的命中率应该在
90％ 以上，否则需要增加数据缓冲区的大小。
27、查看ASM磁盘挂载时间：
-
set
lines
500 pages
2000
-
col g_name
format a10
-
col g_n
format
99
-
col d_n
format
999
-
col m_status
format a7
-
col mo_status
format a7
-
col h_status
format a11
-
col
name
format a20
-
col
path
format a20
-
col failgroup
format a15
-
select g.group_number g_n,
-
g.disk_number d_n,
-
g.name
name,
-
g.failgroup,
-
g.mount_status m_status,
-
g.header_status h_status,
-
g.mode_status mo_status,
-
g.path ,
-
to_char(g.mount_date,
'YYYY/MM/DD HH24:MI:SS') m_date
-
from v$asm_disk g
-
order
by g_n, d_n
28、查看某个用户所拥有的角色
select * from dba_role_privs where grantee='JKX_NEW_QRY ';
29、查看某个角色所拥有的权限
-
select *
from dba_sys_privs
where grantee=
'SKDATA';
-
select *
from DBA_TAB_PRIVS
where grantee=
'SKDATA';
30、修改 LINUX 操作系统 /dev/shm 文件系统大小
-
开机自启动：
-
tmpfs
/dev/shm tmpfs defaults,size=20G 0 0
-
立即生效：
-
mount
-t tmpfs shmfs -o size=20g /dev/shm
31、trace 追踪监听文件
-
$ ps aux |sort -rnk3 |more
-
侦听器进程的Pstack / Truss / Strace / Tusc 注意110888.1如何跟踪Unix系统调用
-
Pstack
<PID_of_listener>
-
-
-
Sun：truss -aefdDo /tmp/truss-lsnr.log -p
<PID_of_listener>
-
Linux：strace -frT -o /tmp/strace-lsnr.log -p
<PID_of_listener>
-
HP：tusc -aef -o /tmp/tusc-lsnr.log -T“％H：％M：％S” -p
<PID_of_listener>
32、删除当前目录下十天前 *.dmp 文件
find ./ -name '*.dmp' -mtime +10 -exec rm -f {}
33、dds 同步软件
-
su
- ddsdt 用户，vshmt -c 可以看到同步情况
-
/ddssoftware/ddt/ddsdt/bin/dds_pput
-x
-
DDS同步软件
类似于OGG的同步软件
-
10.10.19X.XXX
localhost
-
10.10.19X.XX
JiekeADG
-
JiekeADG:
/app/oracle/diag/rdbms/picccash/picccash/trace$
-
hostname:
/#su - ddsdt
-
JiekeADG:
/ddssoftware/ddt/ddsdt/dt$vshmt -c
-
SHM:
-
SHM-VER :
Ver2.13.009
-
type :
T
-
MULTI_SOURCE :
1
-
size :
34675744
-
SystemStat :
Normal
-
OracleLoginStr :
ddsdt/ddsdt
-
DB-Charset :
852
-
DB-N-Charset :
2000
-
Conv-Charset :
0
-
AuthStr :
ddsdt/ddsdt
-
DDS_DATA :
/ddssoftware/ddt/ddsdt/dt
-
Audit :
No
-
QueueCount :
2
-
comm_param :
TCP:10.10.19X.XX:7915
-
Source :
TCP:10.10.19X.XXX:7910
-
SourceWebport :
8303
-
SizeLogFile :
104857600
-
ReserveTableCNT:
50
-
put_retry_cnt :
3
-
alarm_wait_dtf :
300
-
has_split_rids :
0
-
large_table_blo:
1000
-
lob_piece_len :
8388608
-
RM_Blk_Count :
128
-
PutData :
InQueue: 0:0 PutOK: 12510654:945115120084
-
HisData :
InQueue: 0:0 PutOK: 8893:87198847301
-
ChkData :
InQueue: 0:0 PutOK: 0:0
-
AudData :
InQueue: 0:0 PutOK: 0:0
-
dbfInASM :
1
-
DELAY :
0
-
DML_MODE :
RowID
-
PackBak :
5
-
Modules :
0x87
-
DDS_RB_HOURS :
0
-
MachineTime :
20190904182630
-
inode-log :
294
-
inode-err :
281
-
DISK-REMAIN(MB):
512
-
restart_mem(MB):
256
-
DDS_LOBDIR :
-
sem_count :
121
-
sem_pput :
8
-
sem_pput_h :
40
-
sem_pput_v :
72
-
sem_pput_cmt :
89
-
max_num_pput :
32
-
max_num_pput_h :
32
-
max_num_pput_v :
16
-
max_num_pput_a :
1
-
adjust_op :
0
-
index_retry_cou:
0
-
index_retry_del:
60
-
SyncTime0 :
2018/07/26 00:35:05
-
SCN-PUT00 :
SCN-0x83d659c71 SUB:0 Time:2019/09/04 10:29:57
-
SCN-PUT01 :
SCN-0x83d659c71 SUB:0 Time:2019/09/04 10:29:57
-
StopPutIncremen:
Normal, Load Increments
-
StopCreateIndex:
Normal, Load Indexes
-
Total :
IRP-160361545 URP-1100746241 DRP-130759 DDL-7 TXN-593096960
-
DelayTime :
MIN-28591 MAX-1353324 AVG-229
-
PUT-NO-CUR :
136533
-
put0 :
6296809/6296809
-
put1 :
6296809/6296809
-
his0 :
12033/12033 ATS001
-
WaitCommit :
0-N 1-N
-
SRC#0 :
SOURCE 10.10.19X.XXX:7910:8303
-
JiekeADG:
/ddssoftware/ddt/ddsdt/dt$
=======================================================
关于 SQL 命令还有很多，由于篇幅就先写这么多，有机会在分享。上述 SQL 命令由于微信编辑器原因断句不明显，可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号【ID 为 JiekeXu_IT】后台回复 【SQL大全二】 即可获取。也可添加微信 ID：JiekeXu_DBA 一起学习数据库。
=======================================================
34、根据归档号从带库恢复归档日志
-
rman target /
-
run
-
{
-
Allocate channel t1 type
'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
-
restore archivelog sequence between
63145 and
63149 thread
1;
-
restore archivelog sequence between
62697 and
62700 thread
2;
-
Release channel t1;
-
}
35、JDBC 连接串
jdbc:Oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=10.1XX.XXX.XXX)(PROTOCOL=TCP)(PORT=1521))(ADDRESS=(HOST=10.1XX.XXX.XXX)(PROTOCOL=TCP)(PORT=1521)))(LOAD_BALANCE=yes)(FAILOVER=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Jieker)))
36、数据泵导入
-
impdp user/password dumpfile=temp.dmp directory=dump remap_tablespace=[old_table_space]:[new_table_space]
-
old是指server1机器上的tablespace_name，
new是指server2上的
37、切换日志打检查点，关闭实例时使用
-
alter
system
switch
logfile;
-
alter
system
switch
logfile;
-
alter
system checkpoint;
38、修改内存
-
alter
system
set memory_max_target=
0
scope=
spfile;
-
alter
system
set memory_target =
0
scope=
spfile;
-
alter
system
set sga_max_size=
13G
scope=
spfile;
-
alter
system
set sga_target=
13G
scope=
spfile;
-
alter
system
set pga_aggregate_target=
6G
scope=
spfile;
###########################################################
安装数据库报错：
/bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1
$GRID_HOME/crs/install/roothas.pl -deconfig -force -verbose //GI回退 root.sh 脚本
###########################################################
39、停 oem 杀会话
-
停 oem 服务再停数据库
-
/app/oms/bin/emctl start/stop oms
-
Ps -ef |
grep -i
local=
no |
grep -v
grep | awk
'{print $2}'| xargs
kill -
9
40、ASM 掉盘重新挂盘重启 crsd 进程
-
su
- grid
-
crsctl
query crs softwareversion --查看集群版本
-
asmcmd
-
lsdg
-
mount
ocr
-
exit
-
crsctl
start res ora.crsd -init
41、Linux 6 下查看数据库有哪些 IP 连接进来
-
netstat -anop | grep ESTABLISHED | awk
'$4 ~/:1521/'
-
-
-
--解压
10g 的 cpio 类型安装软件
-
cpio -idmvc <
*.cpio ----cpio解压
42、清理垃圾文件
-
cd
&DIR
-
find
./ -ctime +3 |xargs rm
-
查看大于20M的文件
-
find
/ -type f -size +20M -print0 | xargs -0 du -h | sort -nr
43、查看备库进程：
-
set pages
9999
-
set
lines
200
-
select process,client_process,
sequence
#,thread#,status from v$managed_standby;
-
---mrp0当前正在应用的日志序列
-
select process,
status,
sequence
# from v$managed_standby;
-
---查询dg应用情况
-
set linesize
150;
-
set pagesize
20;
-
column name format a13;
-
column value format a20;
-
column unit format a30;
-
column TIME_COMPUTED format a30;
-
select
name,
value,unit,time_computed
from v$dataguard_stats
where
name
in (
'transport lag',
'apply lag');
44、查询数据库负载 dbtime
-
set pages
9999
-
set
lines
200
-
alter
session
set nls_date_format=
'yyyy-mm-dd hh24:mi:ss';
-
SELECT *
-
FROM (
SELECT A.INSTANCE_NUMBER,
-
A.SNAP_ID,
-
B.BEGIN_INTERVAL_TIME +
0 BEGIN_TIME,
-
B.END_INTERVAL_TIME +
0 END_TIME,
-
ROUND(
VALUE - LAG(
VALUE,
1 ,
'0')
-
OVER(
ORDER
BY A.INSTANCE_NUMBER, A.SNAP_ID))
"DB TIME"
-
FROM (
SELECT B.SNAP_ID,
-
INSTANCE_NUMBER,
-
SUM(
VALUE ) /
1000000 /
60
VALUE
-
FROM DBA_HIST_SYS_TIME_MODEL B
-
WHERE B.DBID = (
SELECT DBID
FROM V$
DATABASE)
-
AND
UPPER (B.STAT_NAME)
IN
UPPER((
'DB TIME' ))
-
GROUP
BY B.SNAP_ID, INSTANCE_NUMBER) A,
-
DBA_HIST_SNAPSHOT B
-
WHERE A.SNAP_ID = B.SNAP_ID
-
AND B.DBID = (
SELECT DBID
FROM V$
DATABASE)
-
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
-
WHERE TO_CHAR(BEGIN_TIME,
'YYYY-MM-DD') = TO_CHAR(
SYSDATE ,
'YYYY-MM-DD')
-
ORDER
BY BEGIN_TIME;
=======================================================
本文 SQL 命令由于微信编辑器原因可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号后台回复 【SQL大全二】即可获取,点击下方公众号即可回复。
=======================================================
45、连接数相关查看
-
show parameter process
-
--查看最大连接数
-
select
value
from v$parameter
where
name =
'processes';
-
--查两个节点连接数
-
select INST_ID,
count(*)
from gv$
session
group
by inst_id;
-
--查看起库以来最大连接数
-
select resource_name,MAX_UTILIZATION,LIMIT_VALUE
from v$resource_limit
where resource_name
in (
'processes',
'sessions');
-
--查看并发连接数
-
Select INST_ID,
count(*)
from gv$
session
where
status=
'ACTIVE'
group
by inst_id;
-
--查看不同用户的连接数
-
select username,
count(username)
from v$
session
where username
is
not
null
group
by username;
46、查当前的等待事件
-
col wait_class for a20
-
set
lines
200 pages
200
-
col
event
for a60
-
select
event,
count(*),wait_class
from v$session_wait
group
by
event,wait_class
order
by
3;
47、查看归档是否有错误
select dest_name,error from v$archive_dest;
48、查看数据库运行时间
-
select to_char(startup_time,
'DD-MON-YYYY HH24:MI:SS') 启动时间,
-
TRUNC(
sysdate-(startup_time))||
'天'||TRUNC(
24*((
sysdate-startup_time)-TRUNC(
sysdate-startup_time)))
-
||
'小时'||
MOD(TRUNC(
1440*((
SYSDATE-startup_time)-
-
TRUNC(
sysdate-startup_time))),
60)
-
||
'分'||
MOD(TRUNC(
86400*((
SYSDATE-STARTUP_TIME)-
-
TRUNC(
SYSDATE-startup_time))),
60)
-
||
'秒' 运行时间
-
from gv$
instance;
49、查询dg同步延迟
-
select
ceil((
sysdate-next_time)*
24*
60)
"M"
-
from v$archived_log
-
where applied=
'YES'
-
AND
SEQUENCE
#=
-
(
SELECT
MAX(
SEQUENCE
#) FROM V$ARCHIVED_LOG WHERE applied='YES');
50、查询数据库安装时间
-
select a.name,to_char(a.created,
'yyyy.mm.dd'),b.instance_name
-
from gv$
database a,gv$
instance b
-
where a.inst_id=b.inst_id;
51、查看 sys 用户使用情况
-
select b.username,h.machine,to_char(h.sample_time,
'yyyymmdd hh24:mi:ss')
time,
count(*)
-
from dba_hist_active_sess_history h ,dba_users b
-
where h.user_id=b.user_id
and b.username
in(
'SYSTEM',
'SYS')
and h.MACHINE
not
in (
select host_name
from gv$
instance)
-
and h.sample_time>
to_date(
'20210208',
'yyyymmdd')
-
group
by b.username,h.machine,to_char(h.sample_time,
'yyyymmdd hh24:mi:ss');
52、查询 redo 每天切换次数
-
select trunc(FIRST_TIME,
'dd'),
count(
1)
-
from v$log_history
-
where trunc(FIRST_TIME,
'dd')>
sysdate
-10
-
group
by trunc(FIRST_TIME,
'dd')
-
order
by
1;
53、查看每天归档产生数据量 归档量
-
--按天计算
-
select trunc(FIRST_TIME,
'dd')
Time,
sum(BLOCKS*BLOCK_SIZE)/
1024/
1024/
1024 Total_GB
from v$archived_log
-
group
by trunc(FIRST_TIME,
'dd')
order
by
1;
-
--按小时计算
-
select trunc(FIRST_TIME,
'hh')
Time,
sum(BLOCKS*BLOCK_SIZE)/
1024/
1024/
1024 Total_GB
from v$archived_log
-
group
by trunc(FIRST_TIME,
'hh')
order
by
1;
54、redo 日志产生频率
-
select a.recid,to_char(a.first_time,
'yyyy-mm-dd hh24:mi:ss') begin_time,
-
b.recid,to_char(b.first_time,
'yyyy-mm-dd hh24:mi:ss') end_time,
-
round((b.first_time-a.first_time)*
24*
60,
2)
minutes
-
from v$log_history a,v$log_history b
-
where b.recid = a.recid+
1
and to_char(a.first_time,
'yyyy-mm-dd hh24:mi:ss') >=to_char(
sysdate
-10,
'yyyy-mm-dd hh24:mi:ss') ;
-
desc dba_hist_active_sess_history;
----可以查询执行过的sql的主机
55、AIX 查看僵尸进程
ps -ef | grep defunct
56、清理 LOCAL=NO 连接
ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |xargs kill -9
57、AIX 系统查看错误
errpt | head -20
58、rman 查看归档备份
-
rman target /
-
list
backup
of
archivelog
all;
59、查看 rman 备份进度
-
set line
200 pages
1000
-
col MESSAGE
for a60
-
col TARGET
for a20
-
select
sid,
SERIAL
#,TARGET,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,MESSAGE,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
-
from v$session_longops
-
where
1=
1
--and sid=2983 and SERIAL#=5
-
and TIME_REMAINING>
0 ;
60、查询创建用户的日期
-
select t.username, t.account_status, t.created, t.default_tablespace
from DBA_USERS t
-
where username=
'ABASE'
order
by t.created
desc;
61、rman全库备份
-
run {
-
allocate channel c1 type disk ;
-
allocate channel c2 type disk ;
-
allocate channel c3 type disk ;
-
allocate channel c4 type disk ;
-
backup as compressed backupset database format
'/backup/%d_%I_%s_%p.bak';
-
backup as compressed backupset archivelog all format
'/backup/%d_%I_%s_%p.arc';
-
backup current controlfile for standby format
'/backup/%d_%I_%s_%p.ctl';
-
release channel c1;
-
release channel c2;
-
release channel c3;
-
release channel c4;
-
}
-
--压缩全库备份
-
backup database
format=
'/u01/app/backup/rman/eweaverstb_%s.bak' filesperset
4;
-
run{
-
backup as compressed backupset database
format=
'/u01/app/backup/ewverstb_%s.bak' plus archivelog
delete input skip inaccessible;
-
}
62、数据泵导入导出相关命令
-
--expdp导入导出
-
select
* from dba_directories where directory_name ='DATA_PUMP_DIR';
-
如果目录过小或者不存在,则
-
create
directory expdp_dir as '/u01/app/backup/expdp_dir';
-
grant
read,write on directory expdp_dir to public;
-
expdp
\'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;
-
-
-
impdp
\'/ as sysdba\' directory=expdp_dir dumpfile=full_bak.dmp full=y;
-
-
-
impdb
\'/ as sysdba\' DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
-
-
-
--使用
par 文件全库导出
-
more
full_expdp_query.par
-
directory=
exp_dir1
-
logfile=
full_new_exclude.log
-
dumpfile=
full_new_exclude_%U.dmp
-
exclude=
table:"in('LOG2017','LOG20210115')"
-
query=
GO.POLICYATTACHMENTS:"where createtime>=to_date('20200901','YYYYMMDD')"
-
exclude=
statistics
-
full=
y
-
parallel=
8
-
cluster=
N
-
-
-
nohup expdp \"/ as sysdba\" parfile=
full_expdp_query.par &
-
-
-
--导入
-
more
full_impdp_query.par
-
directory=
imp_dir1
-
logfile=
impdp_full_query.log
-
dumpfile=
full_expdp_query_%U.dmp
-
CLUSTER=
N
-
parallel=
8
-
-
-
nohup impdp \"/ as sysdba\" parfile=
full_impdp_query.par &
-
expdp
\'/ as sysdba\' directory=expdp_dir dumpfile=tbs_bak.dmp tables=(XXX,XXX)
-
expdp \'/ as sysdba\' directory=
expdp_dir dumpfile=tbs_bak.dmp tables=\(PROD.SU_UALCOMPEMRESPONINFO,PROD.SU_BASICINFO,PROD.SU_SENDXML,PROD.SU_SENDXMLBATCH\) fromuser=xxx touser=xxx log=tab.log
-
expdp \'/ as sysdba\' directory=
TIF_DP dumpfile=ATSSIX_20190808.DMP logfile=PICCATSSIX_20190809.log fromuser=ATSSIX touser=ATS
-
expdp
\'/ as sysdba\' directory=expdp_dir dumpfile=bbruser.dmp logfile=bbruser.log fromuser=bbruser touser=bbinfo
-
-
-
--只导出全库索引
-
expdp
\' / as sysdba \' directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=INDEX full=y
-
Export:
Release 11.2.0.3.0 - Production on Wed Feb 3 14:45:50 2021
-
-
-
Copyright
(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
;;;
-
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With
the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
-
Data
Mining and Real Application Testing options
-
FLASHBACK
automatically enabled to preserve database integrity.
-
Starting
"SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=exp_dir dumpfile=index.dmp logfile=out_index.log cluster=n include=INDEX full=y
-
Estimate
in progress using BLOCKS method...
-
Total
estimation using BLOCKS method: 0 KB
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
-
Master
table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
-
******************************************************************************
-
Dump
file set for SYS.SYS_EXPORT_FULL_01 is:
-
/oracle/soft/index.dmp
-
Job
"SYS"."SYS_EXPORT_FULL_01" successfully completed at 14:47:59
-
-
-
SQLFILE参数
-
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为
-
SQLFILE=
[directory_object:]file_name
-
注意事项:
-
1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略
-
2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中
-
3.SQLFILE和QUERY参数冲突,不能同时使用
-
-
-
--获取创建索引的
SQL
-
impdp
\' / as sysdba \' directory=exp_dir sqlfile=cre_index.sql dumpfile=index.dmp logfile=in_index.log cluster=n include=INDEX
-
Import:
Release 11.2.0.3.0 - Production on Wed Feb 3 15:06:30 2021
-
-
-
Copyright
(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
;;;
-
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With
the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
-
Data
Mining and Real Application Testing options
-
Master
table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
-
Starting "SYS"."SYS_SQL_FILE_FULL_01":
"/******** AS SYSDBA" directory=exp_dir dumpfile=index.dmp logfile=index.log cluster=n sqlfile=cre_index.sql include=INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
-
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
-
Job
"SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 15:07:22
-
-
-
--获取创建索引的
SQL 语句
-
set
pages 9999
-
set
line 9999
-
set
long 9999
-
SELECT
dbms_lob.substr(dbms_metadata.get_ddl('INDEX',INDEX_NAME,'SCOTT'))||';'
-
from
dba_indexes
-
where
owner='SCOTT';
-
-
--导出序列
-
expdp
\' / as sysdba \' directory=exp_dir dumpfile=sequence.dmp logfile=out_sequence.log cluster=n include=sequence full=y
-
-
-
--删除序列在导入
-
set
line 120 pages 9999
-
spool
drop_sequence.sql
-
SELECT
'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';' FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='SCOTT';
-
spool
off;
-
@drop_sequnece.sql
-
impdp
\' / as sysdba \' network_link=db_seq include=sequence
-
-
-
--按条件导出
-
expdp
\'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_polic_%U.dmp TABLES=ECARGO.POLICYATTACHMENTS query=\"where createtime \>= to_date\(\'20200901\',\'YYYYMMDD\'\)\"
-
-
-
impdp
bbrinfo/8t4V~p5=Y DIRECTORY=expdp_dir dumpfile=bbruser.dmp REMAP_SCHEMA=bbruser:bbrinfo
-
Import:
Release 10.2.0.1.0 - 64bit Production on Friday, 09 August, 2019 14:43:03
-
Copyright
(C) 2003, 2005, Oracle.? All rights reserved.
-
Connected
to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
-
With
the Partitioning, OLAP and Data Mining options
-
ORA-39001:
invalid argument value
-
ORA-39000:
bad dump file specification
-
ORA-39142:
incompatible version number 3.1 in dump file "/home/oracle/PICCATSSIX_20190808.DMP"
-
-
-
expdp
\'/ as sysdba\' directory=exp_dir1 dumpfile=full_query2021_%U.dmp full=y logfile=full2021.log qurey= \"where policyattachments.createtime>= to_date('20200901','YYYYMMDD')\"
-
-
-
impdp
scott/tiger ?network_link=test1? TABLES=scott.a? directory=backup? REMAP_TABLE=a:a1 Query="'Where HIREDATE<= to_date(''31-05-1981'', ''dd-mm-yyyy'') and HIREDATE>to_date(''31-01-1981'', ''dd-mm-yyyy'') '"? logfile=test.log ;
-
-
-
query=
bi_dw.DW_MID_CONTRACT:"where data_date in(to_date('2013-11-13','yyyy-mm-dd'),to_date('2013-11-14','yyyy-mm-dd'),to_date('2013-8-13','yyyy-mm-dd'),to_date('2013-8-14','yyyy-mm-dd'))"
-
-
-
expdp
\'/ as sysdba\' directory=exp_dir1 logfile=full_query2021.log dumpfile=full_query_%U.dmp TABLES=ECARGO.POLICYATTACHMENT query=\"where createtime>= to_date('20200901','YYYYMMDD') \" parallel=8 cluster=N
-
-
-
源端数据库版本11.2.0.4.0,目标端数据库版本10.2.0.4.0，源端数据库版本高于目标端数据库版本，源端导出数据时加上目标端version：
-
set
lines 300
-
col
OWNER_NAME for a10
-
col
OPERATION for a15
-
col
JOB_MODE for a20
-
col
STATE for a15
-
select
* from dba_datapump_jobs;
-
-
-
expdp
wxlun/wxlun123 tables=wxlun.WXLUN_TAB01,wxlun.WXLUN_TAB02 directory=expdp dumpfile=wxlun1212.dmp logfile=wxlun1212.log version=10.2.0.4.0
-
impdp
scott/tiger directory=expdp dumpfile=wxlun1212.dmp logfile=wxlun1212.log remap_schema=wxlun:scott REMAP_TABLESPACE=wxlun:USERS
-
--cp冷备数据文件
控制文件 参数文件 v$datafile,v$controlfile;
63、查看历史备份
-
set line
200
-
col START_TIME
for a30
-
col END_TIME
for a30
-
col
status
for a10
-
select SESSION_KEY,
-
INPUT_TYPE,
-
STATUS,
-
to_char(START_TIME,
'yyyy-mm-dd hh24:mi') START_TIME,
-
to_char(END_TIME,
'yyyy-mm-dd hh24:mi') END_TIME,
-
ELAPSED_SECONDS /
3600
-
from v$rman_backup_job_details
-
where to_char(START_TIME,
'yyyy-mm-dd hh24:mi') > to_char(
sysdate
-12,
'yyyy-mm-dd hh24:mi')
-
order
by SESSION_KEY;
64、查询数据库增量增长
-
select
sum(TABLESPACE_USEDSIZE)*
8/
1024/
1024,
substr(rtime,
0,
10)
-
from DBA_HIST_TBSPC_SPACE_USAGE
-
where snap_id
in (
select
max(snap_id)
from DBA_HIST_TBSPC_SPACE_USAGE
group
by
substr(rtime,
0,
10) )
--and TABLESPACE_ID=7
-
group
by
substr(rtime,
0,
10)
order
by
1 ;
-
--清空SHARED_POOL缓冲
-
ALTER
SYSTEM
FLUSH
SHARED_POOL ;
-
--删除已备份归档
-
rman target /
-
delete
archivelog
all backed up
1 times
to device
type
'sbt_tape';
-
关于密码错误验证延迟特性：
-
密码错误验证延迟，可以通过设置EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"
-
alter
system
set
events=
'28401 trace name context forever, level 1';
---不需要重启
65、数据库开启归档
-
su - oracle
-
sqlplus / as sysdba
-
archive log list;
-
-
-
1、归档路径
-
①如果没有归档存放存储，需要操作系统挂载存储再继续操作
-
②如果归档存储存在，但并非所想存放的实际路径，可在相关存储下创建目录
-
mkdir -p /app/oracle/arch
-
修改归档目录权限
-
chown -R oracle:oinstall /app/oracle/arch
-
-
-
③如果归档存储存在，且路径正确，权限正确则可以直接使用
-
-
-
2、备份spfile文件
-
create pfile
from
spfile;
-
3、修改归档路径到想存放的位置，如查询出的归档路径已经是正确的位置，则不需要修改。
-
su - oracle
-
sqlplus / as sysdba
-
alter
system
set log_archive_dest_1=
'location=/app/oracle/arch'
scope=
spfile
sid=
'*';
-
4、重启数据库，开启/关闭归档模式（rac两个节点均关闭，使用一个节点修改，修改完成后启动另一个节点）
-
shutdown immediate;
-
startup mount;
-
5、归档模式修改：
-
开启归档模式：
-
alter
database
archivelog;
-
关闭归档模式：
-
alter
database
noarchivelog;
-
alter
database
open;
-
如果为集群：需要停止两个节点，在一个节点操作完成后，拉起另一个节点，操作步骤如上，
-
rac集群启动另一个节点：startup
-
-
-
6、查看数据库归档路径
-
su - oracle
-
sqlplus / as sysdba
-
archive log list;
-
切换日志，查看归档目录下产生归档文件
-
alter
system
switch
logfile；
-
7、关闭归档
-
shutdown
immediate;
-
startup mount;
-
--开启归档模式：
-
--alter database archivelog;
-
关闭归档模式：
-
alter
database
noarchivelog;
66、 RMAN 全备脚本部署
-
①RMAN全备脚本部署
-
vi /home/oracle/full_backup.sh
-
ORACLE_BASE=/app/oracle
-
ORACLE_HOME=/app/product/11.2.0/db
-
ORACLE_SID=JiekeXu1
-
db_name=JiekeXu
-
PATH=$PATH:$ORACLE_HOME/bin/
-
-
-
v_date=`date +%Y%m%d`
-
[ -d /
backup/$db_name/$v_date ] || mkdir -p /app/
backup/$db_name/$v_date
-
-
-
rman target /<<EOF > /app/
backup/$db_name/$v_date/full_$v_date.log
-
run{
-
allocate channel c1 device
type disk;
-
backup
database
format
'/app/backup/%d/%T/full_%d_%T_%s_%p.bak';
-
sql '
alter
system
archive
log
current
';
-
sql '
alter
system
archive
log
current
';
-
sql '
alter
system
archive
log
current
';
-
backup archivelog all delete input format '/app/
backup/%d/%T/full_arch_%d_%T_%s_%p.bak
';
-
backup current controlfile format '/app/
backup/%d/%T/full_controlfile_%U
';
-
backup spfile format '/app/
backup/%d/%T/full_spfile_%d_%T_%s_%p.bak
';
-
release channel c1;
-
}
-
EOF
-
-
-
②赋权
-
chmod +x full_backup.sh
-
关于 SQL 命令还有很多，由于篇幅就先写这么多，66 条六六大顺，剩余的有机会在分享。上述 SQL 命令由于微信编辑器原因可能出现格式错误不能执行，这里将其保存至文本文件中方便复制粘贴执行，如有需要的小伙伴们在本公众号后台回复 【SQL大全二】 即可获取。
未完待续！！！
以下地址均可找到我：
————————————————————————————
公众号：JiekeXu之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
Oracle 12c 及以上版本补丁更新说明及下载方法（收藏版）
一线运维 DBA 五年经验常用 SQL 大全（一）
Oracle 21C 新特性：数据泵相关新特性汇总
使用数据泵导出时遇到 ORA-27054 错误解决办法
案例|RAC 添加表空间误将数据文件放本地处理办法
11g RAC 在线存储迁移实现 OCR 磁盘组完美替换
震惊：Oracle 11gR2 RAC ADG 并没有高可用
如何通过 Shell 监控异常等待事件和活跃会话
我的 OCM 之路|书写无悔青春追梦永不止步
Oracle 19c 之多租户 PDB 连接与访问（三）
Oracle 12C 最新补丁下载与安装操作指北
DBA 常用的软件工具有哪些（分享篇）？
深入了解 Oracle Flex ASM 及其优点
Oracle 11g 临时表空间管理
Oracle 每日一题系列合集
转载：https://blog.csdn.net/JiekeXu/article/details/113764968
查看评论