# 数据库笔记
# oracle表空间相关操作
- oracle存储结构
# 查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
# 查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
# 查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
# 查看控制文件
SELECT NAME FROM v$controlfile;
# 查看日志文件
SELECT MEMBER FROM v$logfile;
# 查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
# 查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
# 查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
# 查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
SELECT file_name, tablespace_name, bytes/(1024*1024) M FROM dba_data_files;
# 修改表空间数据文件的大小
ALTER database datafile '/u01/app/oracle/product/10.1/dbs/users01.dbf' RESIZE 32M;
# 在表空间新增加数据文件
ALTER TABLESPACE users ADD DATAFILE '/oralcda/u03/app/oracle/oradata/prod/users02.dbf' SIZE 10M;
# 重新查询
SELECT file_name, tablespace_name, bytes/(1024*1024) M FROM dba_data_files;
# Oracle中创建用户和授权
# orache创建用户：
CREATE USER USERNAME IDENTIFIED BY PASSWORD
default tablespace ERP
temporary tablespace TEMP;
创建用户名为： USERNAME, 密码为 PASSWORD 的用户，指定默认表空间为ERP，临时表空间为TEMP
# 分配权限：
GRANT CONNECT TO USERNAME; --授权普通用户
GRANT RESOURCE TO USERNAME; --授权开发人员
GRANT DBA TO USERNAME; --授予DBA权限
GRANT UNLIMITED TABLESPACE TO USERNAME;--授予不限制的表空间
GRANT SELECT ANY TABLE TO USERNAME; --授予查询任何表
GRANT SELECT ANY DICTIONARY TO USERNAME;--授予 查询 任何字典
# 常用查询
- 查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
- 查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
- 查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
- 查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
- 查看所有角色：
select * from dba_roles;
- 查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
- 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
- SqlPlus中查看一个用户所拥有权限
SQL>select * from dba_sys_privs where grantee='username';
- Oracle删除指定用户所有表的方法
select 'Drop table '||table_name||';' from all_tables
where owner='要删除的用户名(注意要大写)';
- 删除用户
drop user user_name cascade;
- 查询当前用户下所有的表：
select table_name from user_tables;
- 删除用户下所有的表数据:
select 'truncate table ' || table_name from user_tables;
# Oracle查询锁表和解锁
Oracle数据库操作中，我们有时会用到锁表查询以及解锁和kill进程等操作
- 锁表查询的代码有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;
- 查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
- 查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
- 杀掉对应进程
alter system kill session'1025,41';
其中1025为sid,41为serial#.
# 快速查询和kill
--查锁表进程
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
--杀锁表进程
alter system kill session '1383,52821';
# Oracle dblink应用
# 查看dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
or
select * from dba_db_links;
# 创建dblink
前提: 创建dblink的用户有对应的数据库权限
create public database link 或者create database link ;
grant create public database link,create database link to myAccount;
create public database link dblinkname connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';
如果在create之后不加public,则创建的dblink就不是公共的,就只有创建者可以使用了.
- 如何确定数据库的servicename:
- sqlplus中使用
show parameter[s] service_names;
注意parameter和parameters都可以 或者
select name,value from v$parameter where name='service_names';
# 使用db link
例如,在本机数据库上创建了一个scott_rmthost的public dblink(使用远程主机的scott用户连接),则用sqlplus连接到本机数据库,执行select * from scott.emp@scott_rmthot即可以将远程数据库上的scott用户下的emp表中的数据获取到. 也可以在本地建一个同义词来指向scott.emp@scott_rmthost,这样取值就方便多了.
# 删除dblink
drop public database link dblinkname;
例:
create public database link chengwenit connect to chengweniterp
identified by "123456" using '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.102)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME =CWIT) ) )';
# Oracle循环语句参考示例
--备份错误数据
create table bms_batch_def_noprice0714 as(
select *
from bms_batch_def t
where nvl(t.unitprice, 0) = 0
and t.createfrom = 7);
--修改错误数据
begin
for c1 in (select *
from bms_batch_def t
where nvl(t.unitprice, 0) = 0
and t.createfrom = 7) loop
update bms_batch_def a
set (a.notaxsuprice, a.unitprice) =
(select b.notaxsuprice, b.unitprice
from bms_batch_def b
where b.batchid = c1.oldbatchid)
where a.batchid = c1.batchid;
end loop;
end;
# Oracle job应用
# 创建Oracle Job
var job1 number;
begin
dbms_job.submit(:job1, 'test;', sysdate,'sysdate+1/1440');
commit;
dbms_job.run(:job1);
end;
/
# 常用的定时时间
- 每分钟执行 Interval => TRUNC(sysdate,’mi’) + 1 / (24*60)
- 每天定时执行
例如：每天的凌晨2点执行 Interval => TRUNC(sysdate) + 1 +2 / (24)
- 每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+2/24
- 每月定时执行
例如：每月1日凌晨2点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
- 每季度定时执行 例如每季度的第一天凌晨2点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
- 每半年定时执行
例如：每年7月1日和1月1日凌晨2点 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
- 每年定时执行
例如：每年1月1日凌晨2点执行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
# SQLserver查询锁表和解锁
由于写错了一个过滤条件kettle从用友U8数据库中拉取发票明细数据时数据量过大强制停止后仍然在无法查询，怀疑是被锁表了。
- 查询锁表语句
select request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type = 'OBJECT'
- 强制解锁
declare @spid int
Set @spid = 58 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
# Oracle截取字符串
oracle 截取字符(substr)，检索字符位置(instr) case when then else end语句使用 常用函数：substr和instr
# SUBSTR（string,start_position,[length]） 求子字符串，返回字符串
- string 元字符串
- start_position 开始位置（从0开始）
- length 可选项，子字符串的个数
# 示例
substr("ABCDEFG", 0); -- 返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); -- 返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); -- 返回：ABC，截取从A开始3个字符，字符串排列位置不变。
substr("ABCDEFG", 0, 100); -- 返回：ABCDEFG。
substr("ABCDEFG", -3); -- 返回：EFG，注意参数-3，为负值时表示从尾部开始算起
# INSTR（string,subString,position,ocurrence）查找字符串位置
- string：源字符串
- subString：要查找的子字符串
- position：查找的开始位置
- ocurrence：源字符串中第几次出现的子字符串
# 示例
INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为 14
# Oracle中exp,imp的使用详解
# EXP:
- 完全(需要有特殊权限)：
EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
- 用户模式(用户所有对象被输出到文件)：
EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=chengwenit
- 表模式：
EXP SONIC/SONIC BUFFER=64000 FILE=C:\SONIC.DMP OWNER=CHENGWENIT TABLES=(CHW_USERS)
# IMP:
- 完全：
IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
- 用户模式：
IMP chwit/chwit BUFFER=64000 FILE=C:\chwit.DMP FROMUSER=chw TOUSER=chwit
- 表模式：
imp system/manager file=tank log=tank fromuser=chengwenTABLES=(a,b)
# EXP导出实例
# 将数据库TEST完全导出，用户名system 密码manager 导出到D：\daochu.dmp中
exp system/manager@TEST file=d：\daochu.dmp full=y
# 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d：\daochu.dmp owner=（system，sys）
# 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d：\daochu.dmp tables=（table1，table2）
# 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d：\daochu.dmp tables=（table1） query=\" where filed1 like '00%'\"
# IMP导入实例
# 将D：\daochu.dmp 中的数据导入 TEST数据库中。表已存在忽略则在后面加上 ignore=y 就可以了。
imp system/manager@TEST file=d：\daochu.dmp
# 将d：\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d：\daochu.dmp tables=（table1）
# Oracle ADG主从同步 dataguard(windows to linux )
# 前言
用户目前用的Windows两个Oracle实例500G左右的表空间大小。因性能和安全性方面的考虑，需要在目前基础上新增一个从库读写分离，分担分析报表和外部流向溯源系统、UDI、金税、监管接口等查询的性能压力，以及预防在主库异常(硬件故障/被攻击)时进行故障转移。
从库选择Linux安装Oracle 11g.(相同版本、字符集),如果是出事搭建，推荐使用一致的操作系统版本和一致的表空间数据文件存储路径，搭建更简单。
注意：主从同步不能替离线备份，因为基于日志归档的实时主从同步，在主库误操作无法回滚后，从库也实时做了同样的操作。此时部分数据还需要从离线备份中恢复。
# 环境准备
- 查询主库版本和字符集
-- 查询版本
SELECT * FROM V$VERSION;
-- 查询字符集
select userenv('language') from dual;
--数据库名
select name from v$database;
安装从库，字符集/数据库名，版本一致 参考静默安装Oracle，仅安装数据库，参考
主库和从库HOSTS增加解析
10.11.11.241 erpora0
10.11.11.242 erpora1
- linser和tns配置 1.1 通信准备(监听和tns配置) %ORACLE_HOME%/NETWORK/ADMIN目录下 listener.ora tnsnames.ora
# 这是我主库的 ip和端口，主库取名为 MAIN，要连接的数据库名为 orcl
WINORA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.11.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERP) ) )
# 这是要通信的备库IP 和端口，取名为 BACK，要连接的数据库名为 orcl
LINORA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERP) ) )
- 开启强制归档 shutdown immediate; startup mount; alter database force logging; alter database archivelog;
alter database add standby logfile group 4 ('D:\ERPORA\standby_log04.log') size 50M;
alter database add standby logfile group 5 ('D:\ERPORA\standby_log05.log') size 50M;
alter database add standby logfile group 6 ('D:\ERPORA\standby_log06.log') size 50M;
alter database add standby logfile group 7 ('D:\ERPORA\standby_log07.log') size 50M;
# 主库参数配置
create pfile = 'D:\app\Administrator\pfile' from spfile;
*.db_unique_name='ERPORA' *.fal_client='ERPORA' *.fal_server='ERPORAB' *.log_archive_config='dg_config=(ERPORA,ERPORAB)' *.log_archive_dest_1='location=D:\ARCHIVELOG valid_for=(all_logfiles,all_roles) db_unique_name=ERPORA' *.log_archive_dest_2='service=ERPORAB lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=ERPORAB' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_max_processes=4 *.db_file_name_convert='/db/app/oracle/oradata/ERPORA','D:\ERPORA' *.log_file_name_convert='/db/app/oracle/oradata/ERPORA','D:\ERPORA' *.standby_file_management='auto'
--启动主库 shutdown immediate; startup mount pfile='D:\app\Administrator\pfile';
--nomount启动备库 startup nomount pfile='/db/app/oracle/oradata/pfile';
--配置密码文件 orapwd file=./orapwERPORA password=pass entries=2 force=y
--测试密码 sqlplus sys/pass@ERPORA as sysdba sqlplus sys/pass@ERPORAB as sysdba
登录rman
rman target sys/pass@ERPORA auxiliary sys/pass@ERPORAB
开始复制 duplicate target database for standby from active database nofilenamecheck;
主备库分别创建spfile create spfile from pfile='D:\app\Administrator\pfile'; --主库 create spfile from pfile='/db/app/oracle/oradata/pfile'; --备库
- 配置文件位置
--查询数据文件位置
select name from v$datafile;
-- 如果主库与备库数据文件位置不相同，则需要使用db_file_name_convert来转换。
alter system set db_file_name_convert='/data/oracle/oradata/chwit1','C:\APP\ADMINISTRATOR\ORADATA\SFXT' scope=spfile;
--log_file_name_convert 查看在线日志文件的位置：
select member from v$logfile;
--如果主库与备库在线日志文件位置不相同，则需要使用log_file_name_convert来转换。
alter system set log_file_name_convert='/data/oracle/oradata/chwit1','C:\APP\ADMINISTRATOR\ORADATA\SFXT' scope=spfile;
- 配置 standby_file_management、fal_client、fal_server
alter system set standby_file_management=auto scope=spfile;
alter system set fal_client='tnssfx' scope=both;
alter system set fal_server='tnschwit1' scope=both;
- 创建文件夹
mkdir C:\app\Administrator\product\11.2.0\dbhome_1\archivelog
- 应用新参数文件
--一致性停库
shutdown immediate;
--创建新的spfile
create spfile from pfile;
startup;
- 添加主库和备库的standby日志组
standby日志组个数：配置为redo日志组个数+1，在主库与备库都添加standby日志组。主库可以不添加，如果考虑备库要切成主库就需要一起添加。
--只查询standby日志组：
select * from v$standby_log ;
alter database add standby logfile group 4 ('C:\app\Administrator\oradata\chwit0\stredo04.log') size 50M;
alter database add standby logfile group 5 ('C:\app\Administrator\oradata\chwit0\stredo05.log') size 50M;
alter database add standby logfile group 6 ('C:\app\Administrator\oradata\chwit0\stredo06.log') size 50M;
alter database add standby logfile group 7 ('C:\app\Administrator\oradata\chwit0\stredo07.log') size 50M;
- 主库静态监听配置
/data/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=chwit0)
(SID_NAME = chwit0)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/oracle
- 主库tnsnames.ora文件配置
/data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
tnschwit0 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = chwit0)
)
)
tnschwit1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = chwit0)
)
)
# 备库配置
- 设置一个密码文件
# 指定standby database的ORACLE_SID
export ORACLE_SID=stddb
# 跳转到dbs文件夹下
cd $ORACLE_HOME/dbs
# 创建密码文件
orapwd file=orapwchwit1 password=sys123
- 初始化参数文件
--在主库生成初始化参数文件
SQL> create pfile from spfile;
复制主库的参数文件到备库并重命名后修改备库的参数文件内容
chwit0.__java_pool_size=4194304
chwit0.__large_pool_size=4194304
chwit0.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
chwit0.__pga_aggregate_target=343932928
chwit0.__sga_target=515899392
chwit0.__shared_io_pool_size=0
chwit0.__shared_pool_size=192937984
chwit0.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/chwit1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oracle/oradata/chwit1/control01.ctl','/data/oracle/flash_recovery_area/chwit1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chwit0'
*.db_recovery_file_dest='/data/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chwit0XDB)'
*.local_listener='LISTENER_SFXT'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_unique_name=chwit1
log_archive_config='dg_config=(chwit0,chwit1)'
log_archive_dest_1=''LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chwit1'
log_archive_dest_2='SERVICE=tnschwit0 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chwit0'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\SFXT','/data/oracle/oradata/chwit1'
log_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\SFXT','/data/oracle/oradata/chwit1'
fal_client='tnschwit1'
fal_server='tnschwit0'
standby_file_management='AUTO'
- 新建需要的目录
mkdir -p /data/oracle/admin/chwit1/adump
mkdir -p /data/oracle/oradata/chwit1
mkdir -p /data/oracle/flash_recovery_area/chwit1
- 复制控制文件过来
注意控制文件一般有两个
cp C:\app\Administrator\oradata\chwit0\CONTROL01.CTL
cp C:\app\Administrator\flash_recovery_area\chwit0\CONTROL02.CTL
- 使用pfile文件创建spfile文件
--登陆到idle数据库
sqlplus / as sysdba
--创建spfile
create spfile from pfile
--启动数据库到nomount状态
startup nomount
- 配置静态监听
[oracle@standbynode admin]$ vim listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=chwit0)
(SID_NAME = chwit1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /data/oracle
- 配置tnsnames.ora文件,直接复制主库的
- 测试网络连通性
主库与备库都测试执行确认可以正常访问
tnsping tnschwit0
tnsping tnschwit1
- 在主库测试连接备库
sqlplus sys/sys123@tnschwit0 as sysdba
sqlplus sys/sys123@tnschwit1 as sysdba
# 参数检查
| 参数 | 说明 |
|---|---|
| db_unique_name | 2个节点需要不一样 |
| compatible | 主库与备库兼容性需一致 |
| log_archive_config | 配置主库与备库的db_unique_name |
| log_archive_dest_1 | 归档日志的路径 |
| log_archive_dest_2 | 归档日志的路径 |
| log_archive_dest_state_2 | enable：启用,defer:禁用 |
| db_file_name_convert | 数据文件转换路径 |
| log_file_name_convert | 日志文件转换路径 |
| standby_file_management | 设置为auto |
| log_archive_format | 日志文件格式，两边需一致 |
# 使用duplicate创建物理standby
- 备库端执行
#： 一定要加nocatalog，否则在执行duplicate时会报错
[oracle@primarynode ~]$ rman target sys/sys123@chwit0 auxiliary sys/sys123@chwit1 nocatalog
- 使用duplicate复制数据库
RMAN> duplicate target database for standby from active database nofilenamecheck;
-- 开始同步数据库(备库执行)
alter database open;
--开启实时同步
alter database recover managed standby database using current logfile disconnect from session;
--方式二：开启同步（日志切换时才同步）
alter database recover managed standby database disconnect from session;
# 检查是否执行成功
- 主库状态查看
select open_mode, --数据库打开模式，如果实时同步，则为：read only with apply，取消同步则为：read only
database_role, --数据库角色，是主库还是备库
protection_mode, --保护模式
protection_level --保护级别
from v$database;
# 主库
| OPEN_MODE | DATABASE_ROLE | PROTECTION_MODE | PROTECTION_LEVEL |
|---|---|---|---|
| READ WRITE | PRIMARY | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE |
# 备库
| OPEN_MODE | DATABASE_ROLE | PROTECTION_MODE | PROTECTION_LEVEL |
|---|---|---|---|
| READ ONLY WITH APPLY | PHYSICAL STANDBY | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE |
# Oracle中创建unique唯一约束(单列和多列)
oracle中的unique约束是用来保证表中的某一类，或者表中的某一类组合不重复。
- 如下建表时并将其中的fname和lname组合起来建立了一个唯一约束。
create table unique_test
(id number,
fname varchar2(20),
lname varchar2(20),
address varchar2(100),
email varchar2(40),
constraint name_unique unique(fname,lname))
- 建表后增加约束
alter table unique_test
add constraint email_unique unique(email);
# Oracle自动备份脚本
set backupfile=crm%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp
--数据文件名
set logfile=crm%date:~0,4%-%date:~5,2%-%date:~8,2%.log
--日志文件名
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\expdp coremail2016/123456@orcl directory=WSCDIR schemas=coremail2016 dumpfile=%backupfile% logfile=%logfile%
--备份命令
forfiles /p "D:\databack" /d -10 /c "cmd /c echo deleting @file ... && del /f @path"
--删除10天前备份
# Oracle查找重复记录
SELECT *
FROM t_info a
WHERE ((SELECT COUNT(*)
FROM t_info
WHERE Title = a.Title) > 1)
ORDER BY Title DESC
# 查找重复记录
-- 查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
-- 过滤重复记录(只显示一条)
Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)
-- 注：此处显示ID最大一条记录
-- 查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
# 删除重复记录
-- 删除全部重复记录（慎用,全部删除）
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
-- 重复记录保留一条
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
-- 注：此处保留ID最大一条记录
-- 删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
# Oracle 汉字转换首字母函数
CREATE OR REPLACE FUNCTION FNC_CWIT_GET_PYM
(iv_str IN CHAR)
RETURN VARCHAR2
IS
v_frmstr VARCHAR2(100) := '（）【】：孢呋羟西漯';
v_tostr VARCHAR2(100) := '()[]:包福枪洗罗';
i_pos INTEGER;
lv_ch VARCHAR2(100);
lv_retstr VARCHAR2(1000);
BEGIN
--依次处理v_str中每个字符
FOR i IN 1..LENGTH(iv_str)
LOOP
lv_ch := SUBSTR(iv_str , i , 1);
IF ASCII(lv_ch) < 128 THEN--非汉字
lv_retstr := lv_retstr || lv_ch;
ELSE--是汉字
i_pos := INSTR(v_frmstr, lv_ch);
IF i_pos > 0 THEN
lv_ch := SUBSTR(v_tostr, i_pos, 1);
END IF;
lv_retstr := CASE lv_ch
WHEN GREATEST(lv_ch, '匝') THEN lv_retstr || 'Z'
WHEN GREATEST(lv_ch, '丫') THEN lv_retstr || 'Y'
WHEN GREATEST(lv_ch, '夕') THEN lv_retstr || 'X'
WHEN GREATEST(lv_ch, '哇') THEN lv_retstr || 'W'
WHEN GREATEST(lv_ch, '他') THEN lv_retstr || 'T'
WHEN GREATEST(lv_ch, '撒') THEN lv_retstr || 'S'
WHEN GREATEST(lv_ch, '然') THEN lv_retstr || 'R'
WHEN GREATEST(lv_ch, '七') THEN lv_retstr || 'Q'
WHEN GREATEST(lv_ch, '趴') THEN lv_retstr || 'P'
WHEN GREATEST(lv_ch, '哦') THEN lv_retstr || 'O'
WHEN GREATEST(lv_ch, '拿') THEN lv_retstr || 'N'
WHEN GREATEST(lv_ch, '妈') THEN lv_retstr || 'M'
WHEN GREATEST(lv_ch, '廓') THEN lv_retstr || 'L'
WHEN GREATEST(lv_ch, '咖') THEN lv_retstr || 'K'
WHEN GREATEST(lv_ch, '讥') THEN lv_retstr || 'J'
WHEN GREATEST(lv_ch, '哈') THEN lv_retstr || 'H'
WHEN GREATEST(lv_ch, '嘎') THEN lv_retstr || 'G'
WHEN GREATEST(lv_ch, '发') THEN lv_retstr || 'F'
WHEN GREATEST(lv_ch, '讹') THEN lv_retstr || 'E'
WHEN GREATEST(lv_ch, '搭') THEN lv_retstr || 'D'
WHEN GREATEST(lv_ch, '擦') THEN lv_retstr || 'C'
WHEN GREATEST(lv_ch, '八') THEN lv_retstr || 'B'
WHEN GREATEST(lv_ch, '阿') THEN lv_retstr || 'A'
ELSE lv_retstr || lv_ch
END;
END IF;
END LOOP;
RETURN lv_retstr;
EXCEPTION WHEN OTHERS THEN
RETURN '0';
END FNC_CWIT_GET_PYM;
# Oracle创建自增序列触发器
create table gd_wms_erp_1
(
dtlid varchar(10) not null,
docno varchar(20) ,
credate date,
customerno varchar(20),
goodsno varchar(20),
goodsqty number(10),
goodsunit varchar(10),
goodsprice number(10,6),
lotno varchar(10),
suflag varchar(2),
saflag varchar(2),
propdate date,
valdate date,
primary key (dtlid)
)
create sequence gd_wms_erp_1_seq
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 10;
create or replace trigger gd_wms_erp_1_seq_trigger
before insert on gd_wms_erp_1
for each row
begin
select gd_wms_erp_1_seq.nextval into :new.dtlid from dual;
end ;
# Oracle密码过期处理
-- 修改密码永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
-- 修改密码
alter user xxerp identified by pass1234
# Oracle数据泵(impdp,expdp)导出导入
oracle 10g增加了data pump提供的是一种基于服务器的数据提取和恢复的实用程序，data pump在体系结构和功能上与传统的export和import实用程序相比有了 明显提升。 注意：数据泵文件与传统的exp/imp数据转储文件是不兼容的
- 查看数据库中已创建的directory的两个视图
select * from all_directories;
select * from dba_directories;
- 创建directory
create directory test as '/media/test';
sql> grant read,write on directory test to system;
- 数据泵导出
#单个用户方案导出，用得最多的昌此种方式
expdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=test dumpfile=x.dmp logfile=x.log
#数据库全库导出
expdp [用户名]/[密码]@[主机字符窜] full=y directory=test dumpfile=x.dmp logfile=x.log
#用管理员导入多个用户
expdp [用户名]/[密码]@[主机字符窜] schemas=[用户名],[用户名1],[用户名2]…… directory=test dumpfile=x.dmp logfile=x.log
- 数据泵导入
#单个用户方案导入
impdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=test dumpfile=x.dmp logfile=x.log ignore=y
#数据库全库导入
impdp [用户名]/[密码]@[主机字符窜] full=y directory=test dumpfile=x.dmp logfile=x.log ignore=y
#old用户导入new用户
remap_schema=chwit:chwitnew
# 增加过滤查询条件导出
expdp scott/tiger@back directory=dpdata1 dumpfile=expdp.dmp tables=emp query='where deptno=20';
- 示例：改变表的owner 从scott 用户导入到system 用户下，表空间从 tablespace1 到 tablespace2
impdp system/manager directory=dpdata1 dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system remap_tablespace=tablespace1:tablespace2
# Oracle常用正则表达式
- REGEXP_LIKE ：与LIKE的功能相似
- REGEXP_INSTR ：与INSTR的功能相似
- REGEXP_SUBSTR ：与SUBSTR的功能相似
| 参数 | 释义 |
|---|---|
| source_string | 源串，可以是常量，也可以是某个值类型为串的列 |
| position | 从源串开始搜索的位置。默认为1 |
| occurrence | 指定源串中的第几次出现。默认值1 |
| match_parameter | 文本量，进一步订制搜索 |
- REGEXP_REPLACE ： 与REPLACE的功能相似
# 示例
--替换数据库中所有的非数字的字符
SELECT
REGEXP_REPLACE (tel,'[^0-9]+', '') AS A
FROM
lt_imp_cust_0519b;
--查询value中以1开头60结束的记录并且长度是7位
select * from chwtable where value like '1____60';
select * from chwtable where regexp_like(value,'1....60');
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select * from chwtable where regexp_like(value,'1[0-9]{4}60');
-- 也可以这样实现，使用字符集。
select * from chwtable where regexp_like(value,'1[[:digit:]]{4}60');
-- 查询value中不是纯数字的记录
select * from chwtable where not regexp_like(value,'^[[:digit:]]+$');
-- 查询value中不包含任何数字的记录。
select * from chwtable where regexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select * from chwtable where regexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select * from chwtable where regexp_like(value,'^1[2B]');
-- 查询数据中包含空白的记录。
select * from chwtable where regexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select * from chwtable where regexp_like(value,'^([a-z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select * from chwtable where regexp_like(value,'[[:punct:]]');
# Oracle复制表
-- 复制表结构及其数据：
create table table_name_new as select * from table_name_old
-- 只复制表结构：
create table table_name_new as select * from table_name_old where 1=2;
--
create table table_name_new like table_name_old
# Oracle 常用函数（待补充）
# 时间日期函数
# 字符串函数
- TRIM 去除收尾空格
# 统计函数
# 分组排序判断函数
# 高级函数(over等)
# MySQL事件创建和修改（类似Oracle job)
写了几个事件定期转储日志，因日志内容增加需修改mysql事件
# 更改事件ALTER EVENT语句，如下所示：
-- 查询事件
SHOW EVENTS FROM studymysql;
-- 修改事件
ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
event_body
# 创建事件，每分钟向messages表中插入一条新记录。
CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
INSERT INTO messages(message,created_at)
VALUES('测试ALTER EVENT语句',NOW());
要使事件每2分钟运行一次，请使用以下语句：
ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;
您还可以通过指定新逻辑来更改事件的主体，如下所示：
ALTER EVENT test_event_04
DO
INSERT INTO messages(message,created_at)
VALUES('Message from event',NOW());
# 禁用事件
ALTER EVENT test_event_04
DISABLE;
# oracle系统表v$session、v$sql 查看最近执行了哪些sql语句
数据库 session被一个运行在数据库服务器上或从中间服务器甚至桌面通过 SQL*Net连接到数据库的客户端进程启动，下列各列提供这个客户端的信息
- OSUSER：客户端操作系统用户名
- MACHINE：客户端执行的机器
- TERMINAL：客户端运行的终端
- PROCESS：客户端进程的 ID
- PROGRAM：客户端执行的客户端程序
# session示例：
--查找你的 session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv( 'SESSIONID' );
--当 machine已知的情况下查找 session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1' ;
--查找当前被某个指定 session正在运行的 sql语句。假设 sessionID为 100
select b.sql_text
from v$session a,v$sqlarea b
where a.sql_hashvalue=b. hash_value and a.sid= 00;
或者
select b.sql_text
from v$session a,v$sqlarea b //v$sqlarea 只包括sql的1000个字符，V$SQLTEXT is all
where a.sql_hash_value = b.HASH_VALUE and a.TERMINAL = 'BackHam';//BackHam is my computer name;
# V$SQLTEXT中的常用列
- HASH_VALUE： SQL语句的 Hash值
- ADDRESS： sql语句在 SGA中的地址
- SQL_TEXT： SQL文本。
- PIECE： SQL语句块的序号
# 执行sql查询示例
--查看消耗资源最多的 SQL：
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC ;
--查看某条 SQL 语句的资源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw( 'CBD8E4B0' );
--查找前10条性能差的sql语句
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;
--EXECUTIONS表示同一条SQL语句一共执行了多少次，SORTS表示排序的次数，DISK_READS表示物理读的数量。
v$sql视图包含 cursor级别资料。当试图定位 session或用户以分析 cursor时被使用。PLAN_HASH_VALUE列存储的是数值表示的 cursor执行计划。可被用来对比执行计划。 PLAN_HASH_VALUE让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。
V$SQL中的列说明：
- SQL_TEXT： SQL文本的前 1000个字符
- SHARABLE_MEM：占用的共享内存大小 (单位： byte)
- PERSISTENT_MEM：生命期内的固定内存大小 (单位： byte)
- RUNTIME_MEM：执行期内的固定内存大小
- SORTS：完成的排序数
- LOADED_VERSIONS：显示上下文堆是否载入， 1是 0否
- OPEN_VERSIONS：显示子游标是否被锁， 1是 0否
- USERS_OPENING：执行语句的用户数
- FETCHES： SQL语句的 fetch数。
- EXECUTIONS：自它被载入缓存库后的执行次数
- USERS_EXECUTING：执行语句的用户数
- LOADS：对象被载入过的次数
- FIRST_LOAD_TIME：初次载入时间
- INVALIDATIONS：无效的次数
- PARSE_CALLS：解析调用次数
- DISK_READS：读磁盘次数
- BUFFER_GETS：读缓存区次数
- ROWS_PROCESSED：解析 SQL语句返回的总列数
- COMMAND_TYPE：命令类型代号
- OPTIMIZER_MODE： SQL语句的优化器模型
- OPTIMIZER_COST：优化器给出的本次查询成本
- PARSING_USER_ID：第一个解析的用户 ID
- PARSING_SCHEMA_ID：第一个解析的计划 ID
- KEPT_VERSIONS：指出是否当前子游标被使用 DBMS_SHARED_POOL包标记为常驻内存
- ADDRESS：当前游标父句柄地址
- TYPE_CHK_HEAP：当前堆类型检查说明
- HASH_VALUE：缓存库中父语句的 Hash值
- PLAN_HASH_VALUE：数值表示的执行计划。
- CHILD_NUMBER：子游标数量
- MODULE：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。
- ACTION：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。
- SERIALIZABLE_ABORTS：事务未能序列化次数
- OUTLINE_CATEGORY：如果 outline在解释 cursor期间被应用，那么本列将显示出 outline各类，否则本列为空
- CPU_TIME：解析 /执行 /取得等 CPU使用时间 (单位，毫秒 )
- ELAPSED_TIME：解析 /执行 /取得等消耗时间 (单位，毫秒 )
- OUTLINE_SID： outline session标识
- CHILD_ADDRESS：子游标地址
- SQLTYPE：指出当前语句使用的 SQL语言版本
- REMOTE：指出是否游标是一个远程映象 (Y/N)
- OBJECT_STATUS：对象状态 (VALID or INVALID)
- IS_OBSOLETE：当子游标的数量太多的时候，指出游标是否被废弃 (Y/N)
--查询$sql示例
select
s.sql_text,
b.name,b.position,b.dup_position,
b.value_string
from v$sql s, v$sql_bind_capture b
where s.hash_value=b.hash_value;
# 安装MSDE停到最后几秒不动
# 方法一
不要取消安装，打开任务管理器（CTRL+ALT+DEL），点开进程，找到一个setupre的进程结束掉，就可以继续安装了。
# 方法二
打开window的“任务管理器”（任务栏右键-任务管理器”或alt+ctrl+delete调出），在“任务管理器”进程中找到msiexec.exe进程（一般有3个），找到其中用户名不是system（系统进程）或者内存占用最小的（一般是5000k左右）-右键-结束进程，可快速安装msde数据库
# ms sqlserver sa密码命令行修改
- 点开始--运行，输入cmd确定
- 出现的对话框里输入OSQL -E
- 回车后出现1>后输入EXEC sp_password NULL, 'chengwenit.com', 'sa'
- 回车后出现2>后输入go
# MSDE/sqlserver2000 安装挂起清除
开始-运行窗口输入regedit，打开注册表编辑器，在HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager中找到PendingFileRenameOperations，删除该键值，关闭注册表编辑器。重新安装SQL Server 2000即可
# mysql添加用户和指定IP授权和回收相关操作
-- 创建用户
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'],
[user[IDENTIFIED BY [PASSWORD] 'password']]...
CREATE USER 'chwit'@'%' IDENTIFIED BY 'SXyVYtUkqh0b3rJ';
-- 授权
GRANT ALL ON *.* to itsm@'192.168.168.164' IDENTIFIED BY '*******';
GRANT ALL ON chwdms.* to chwit@'%' IDENTIFIED BY 'SXyVYtUkqh0b3rJ';
--创建数据库指定字符集
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 回收
--下面的代码实现了取消sss用户的所有权限的功能。
revoke all on *.* from sss@localhost ;
-- 重置密码：
use mysql;
update user set password=password("") where user=’root’ and host=’localhost’; flush privileges;
# Linux下Oracle11G的job没有执行-last_date为空
公司Oracle数据库从windows迁移到Linux之后，创建了几次Job每次都没有执行，last_date为空，但是next_date会正常更新
首先保证手动执行 dbms_job.run(job num) ，可以执行成功
然后查一下ORACLE默认的定时任务执行范围,这个值安装后默认是0，所有的Job都不会执行
select value from v$parameter where name like '%job_queue_processes%'
- 设置job执行范围
alter system set job_queue_processes =1000;
# Centos6 安装Mysql
- 查询是否已经有安装
yum list installed | grep mysql
- 如果发现有系统自带mysql，先卸载
yum -y remove mysql-libs.x86_64
- 下载国内 mysql源
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
- 安装国内 mysql源
rpm -ivh mysql-community-release-el6-5.noarch.rpm
- 用yum repolist mysql查一下是否已经有mysql可安装文件
yum repolist all | grep mysql
6.安装mysql 服务器
yum install mysql-community-server
7.启动mysql服务
service mysqld start
8.初始设置root密码
mysql -u root
use mysql;
update user set password=PASSWORD("****") where User='root';
9.创建数据库
create database chwmis;
10.创建一个用户，所有地址都可以访问
grant all privileges on chwmis.* to fsyjx@'%' identified by 'fsyjx+2018';
flush privileges;
11.设置开机自动启动
# chkconfig mysqld on
12.mysql安全设置
mysql_secure_installation
- 为root用户设置密码；
- 删除匿名账号；
- 取消root用户远程登录；
- 删除test库和对test库的访问权限；
- 刷新授权表使修改生效。
# Oracle创建同义词授权用户使用
因公司一个项目需要和第三方WMS供应商做对接，开放数据库给对方访问，因考虑安全问题，给WMS开放的用户仅开放中间表的访问权限，通过同义词实现
- 创建中间表用户
CREATE USER erptowms IDENTIFIED BY erptowms;
GRANT CONNECT TO EPRTOWMS; --允许连接
GRANT CREATE SESSION TO EPRTOWMS; --允许创建会话
GRANT CREATE SYNONYM TO kq2shr; --允许创建同义词
- 在我方数据库用户下创建中间表(例：
create table ZZERP_PDD_W(
OWNERID varchar2(11),-- 货主ID
BILLCODE varchar2(50),-- 单据编号
BILLSN number(10),-- 单据序号
DATES varchar2(10),-- 日期
ONTIME varchar2(10), -- 时间
CAOZY varchar2(11),-- 操作员ID
SALEMAN VARCHAR2(20),-- 销售员
SUMMARIES varchar2(200),-- 摘要
GOODSID varchar2(11),-- 商品ID
ANGLEID varchar2(11),-- 维度ID
NUM NUMBER(14,2),-- 数量
TAXPRICE NUMBER(14,4),-- 含税价
TAXAMOUNT NUMBER(14,2),-- 含税金额
UNIT varchar2(8),-- 单位
BATCHCODE varchar2(50),-- 批号
PRODUCEDATE varchar2(10),-- 生产日期
VALDATE varchar2(10),-- 有效期至
CREATETIME varchar2(19),-- 创建时间
ZT varchar2(10),-- NULL
STORELX VARCHAR2(2),-- 仓库类型
ERPHW VARCHAR2(20),-- 业务维度
DJBS varchar2(3),-- 单据标识
REMARK varchar2(256),-- 备注
DEPT varchar2(20))-- 部门
- 授权，下传WMS表，允许WMS select,update -- erp用户下执行
grant select,update on ZZERP_PDD to erptowms;
- 授权WMS反馈表，允许WMS select,insert -- erp用户下执行
grant select,insert on ZZERP_PDD_W to erptowms;
- 创建同义词 --WMS用户下执行
CREATE OR REPLACE SYNONYM ZZERP_PDD_W FOR GZCW.ZZERP_BSD;
# win10安装MSDE
- 如果是家庭版系统，先启用管理员账户： net user administrator /active:yes
- msde安装目录下sqlunirl.dll 替换 c:windows\syswow64\sqlunirl.dll （无法替换配置文件所属用户组后修改权限再替换）再正常安装即可
# Oracle企业版迁移标准版
因标准版没有 deferred_segment_creation 功能，导入企业版dmp文件会报错 在impdp导入时，增加 version = 10.2.0 参数即可
# 用友U8 sqlserver数据库变可疑状态处理
客户咨询无法登录，提示是sa登录失败，但是在服务器配置里面测试连接sql是正常的。打开数据库发现是UFSystem库变成可疑状态了。一般是数据库运行中直接关机的偶发现象。
# 解决方法：
当发生这种操作故障时，可以按以下操作步骤可解决，打开ssms,新建查询，输入以下的命令运行
--修改数据库为紧急模式
ALTER DATABASE [UFSystem] SET EMERGENCY;
--使数据库修改为单用户模式
ALTER DATABASE [UFSystem] SET SINGLE_USER;
--修复数据库日志重新生成，此命令检查的分配，结构，逻辑完整性和所有数据库中的对象错误。当您指定“REPAIR_ALLOW_DATA_LOSS”作为DBCC CHECKDB命令参数，该程序将检查和修复报告的错误。但是，这些修复可能会导致一些数据丢失。
DBCC CheckDB ([UFSystem], REPAIR_ALLOW_DATA_LOSS)
---使数据库修改为多用户模式
ALTER DATABASE [UFSystem] SET MULTI_USER
被标记为“可疑”的数据库已恢复正常状态。
# SQLSERVER批量备份和恢复数据库
一个代账的客户有800多个客户，在一个数据库实例中有800多个数据库，需要进行批量迁移
- 开启在SQL中执行shell命令配置
USE master
GO
RECONFIGURE --先执行一次刷新，处理上次的配置
GO
EXEC sp_configure 'show advanced options',1 --启用xp_cmdshell的高级配置
GO
RECONFIGURE --刷新配置
GO
EXEC sp_configure 'xp_cmdshell',1 --打开xp_cmdshell,可以调用SQL系统之外的命令
GO
RECONFIGURE
GO
- 备份
DECLARE
@FileName VARCHAR(200),
@CurrentTime VARCHAR(50),
@DBName VARCHAR(100),
@BAKSQL VARCHAR(1000)
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
DECLARE CurDBName CURSOR FOR
SELECT NAME FROM Master..SysDatabases where dbid>4
OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--Execute Backup
SET @FileName = 'E:\BAK\' + @DBName + '_' + @CurrentTime
SET @BAKSQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
EXEC(@BAKSQL)
--Get Next DataBase
FETCH NEXT FROM CurDBName INTO @DBName
END
CLOSE CurDBName
DEALLOCATE CurDBName
- 恢复
declare
@backpath varchar(2000),
@execsql varchar(2000),
@datapath varchar(2000),
@lgname varchar(64),
@lgtype varchar(12),
@lgfilename varchar(64),
@i int
begin
set @backpath='E:\BAK\';
set @execsql='dir /b '+ @backpath +'\*.BAK';
set @datapath='E:\DATA';
begin
if not exists (select * from sysobjects where name='backfiles')
create table backfiles(name varchar(2000));
end
truncate table backfiles
insert into backfiles exec xp_cmdshell @execsql
DELETE backfiles WHERE upper(right(name,3))<>'BAK' OR NAME IS NULL
declare @fileName varchar(100),
@dbName varchar(100)
declare file_cur cursor for select name from backfiles;
open file_cur
fetch next from file_cur into @fileName
while @@fetch_status=0
begin
select @dbName=substring(@fileName,1,charindex('.bak',@filename)-1);
----------------------------
DECLARE @fileListTable TABLE (
[LogicalName] NVARCHAR(128),
[PhysicalName] NVARCHAR(260),
[Type] CHAR(1),
[FileGroupName] NVARCHAR(128),
[Size] NUMERIC(20,0),
[MaxSize] NUMERIC(20,0),
[FileID] BIGINT,
[CreateLSN] NUMERIC(25,0),
[DropLSN] NUMERIC(25,0),
[UniqueID] UNIQUEIDENTIFIER,
[ReadOnlyLSN] NUMERIC(25,0),
[ReadWriteLSN] NUMERIC(25,0),
[BackupSizeInBytes] BIGINT,
[SourceBlockSize] INT,
[FileGroupID] INT,
[LogGroupGUID] UNIQUEIDENTIFIER,
[DifferentialBaseLSN] NUMERIC(25,0),
[DifferentialBaseGUID] UNIQUEIDENTIFIER,
[IsReadOnly] BIT,
[IsPresent] BIT,
[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backpath+@fileName+'''')
set @execsql = '';
set @execsql ='restore database '+'"'+@dbName+'"' + ' from disk='+CHAR(39)+@backpath+@fileName+CHAR(39) +' with ' ;
-----------------
set @i = 1
declare bak_cur cursor for select
[LogicalName]
,reverse(substring(reverse([PhysicalName]),0,charindex('\',reverse([PhysicalName])))) as filen_ame
,[Type]
from @fileListTable
open bak_cur
fetch next from bak_cur into @lgname,@lgfilename,@lgtype
while @@FETCH_STATUS = 0
begin
/*
不考虑文件重名的情况，统一恢复到同一目录下
*/
set @execsql = @execsql + (case when @i = 1 then ' ' else ', ' end) + ' move '+CHAR(39)+@lgname+CHAR(39)+' to '+CHAR(39)+@datapath+'\'+ @lgfilename +CHAR(39)
set @i = @i + 1
fetch next from bak_cur into @lgname,@lgfilename,@lgtype
end
close bak_cur
deallocate bak_cur
----------------------------
print @execsql
exec (@execsql)
delete from @fileListTable
fetch next from file_cur into @fileName
end
close file_cur
deallocate file_cur
end
# mysql本地自动备份脚本
每天自动备份并打包压缩，自动删除7天前备份
#!/bin/bash
# 文件名为数据库名+日期
backupDatabase=chwdmsdb0
fileName=${backupDatabase}`date +%Y%m%d`
# 定义baseDir变量
baseDir=/app/backup/mysql/
# 备份数据库
mysqldump --defaults-extra-file=/etc/my.cnf ${backupDatabase} > ${baseDir}${fileName}.sql
# 压缩
tar -zcPvf ${baseDir}${fileName}.sql.tar.gz ${baseDir}${fileName}.sql
# 删除刚刚备份的sql文件,留下压缩的
rm -f ${baseDir}${fileName}.sql;
# 删除7天前备份的数据，只保留一星期内的数据
find ${baseDir} -mtime +7 -name "${backupDatabase}*.tar.gz" -exec rm -rf {} \;
# 自动备份所有sqlserver数据库脚本
使用msde/sqlserver express版本没有带代理服务可以通过计划任务定时运行
- 备份全部数据库的sql,逻辑是通过SYSDATABASES获取全部数据库名写入游标，循环执行备份到文件SQL脚本
use master
declare @DbName varchar(60)
declare @BackSql varchar(1000)
DECLARE @backupTime DATETIME = CURRENT_TIMESTAMP
declare myCursor cursor for
SELECT [name] FROM SYSDATABASES
where [name] not in ('master','model','msdb','tempdb')
order by [name]
open myCursor
fetch next from myCursor into @DbName
while(@@FETCH_STATUS = 0)
begin
select @BackSql='Backup DATABASE ['+@DbName+'] to disk=''F:\YYBAK\'+@DbName+'-'+CONVERT(VARCHAR(25), @backupTime, 112) +'.bak'' with format'
exec(@BackSql)
fetch next from myCursor into @DbName
end
close myCursor
DEALLOCATE myCursor
- 转成powershell脚本在定时任务中运行
-S后面是数据库服务器地址，非默认实例则加上 \实例名，-P是密码
sqlcmd -S localhost -U sa -P password -i ./bak.sql -o ./bak.log
- 备份/ 打包/ 清除
sqlcmd -S localhost -U sa -P CHWIT+sasa,. -i ./YXKbak.sql -o ./bak.log
set zip=C:\Program Files\7-Zip\7z.exe
set timestamp=%date:~0,10%
set dir=E:\temp\
echo "%zip%"
echo "%dir%"
echo "%timestamp%"
::explorer "%arc%\.."
"%zip%" a E:\chwRecycle\ZHGY_YXK_%timestamp%.7z "%dir%"
forfiles /p "E:\\temp" /s /m *.* /d -2 /c "cmd /c del @path"
forfiles /p "E:\\chwRecycle" /s /m *.* /d -5 /c "cmd /c del @path"
# Oralce删除前备份触发器应用
应用背景: 客户质管部门反馈系统中客户设置了证照的经验范围经常会无故消失，因是接收的维护项目。不了解具体是哪个服务做的删除操作，通过触发器记录删除记录情况
- 按原表结构创建备份表
create table gsp_company_managerage_bak as
select * from gsp_company_managerage where 1=2;
- 备份表增加删除时间列
ALTER TABLE gsp_company_managerage_bak ADD deltime DATE;
- 创建删除后写入备份表触发器
create or replace trigger TRG_GSP_LICENSE_DEL_EVAN
after delete
on gsp_company_managerage
for each row
declare
begin
insert into gsp_company_managerage_bak values(:old.SEQID, :old.COMPANYID,:old.SCOPEDEFID,:old.LICENSEID,:old.MEDICINETYPE,SYSDATE);
end
;
← DAMA数据治理学习笔记 常用工具集 →